import type { SupabaseClient } from "@supabase/supabase-js";

export async function listPlayers(supabase: SupabaseClient) {
  const { data, error } = await supabase
    .from("players")
    .select("*")
    .order("display_name");
  if (error) throw new Error(error.message);
  return data;
}

export async function createPlayer(
  supabase: SupabaseClient,
  userId: string,
  input: { display_name: string; external_id?: string }
) {
  const { data, error } = await supabase
    .from("players")
    .insert({
      display_name: input.display_name,
      external_id: input.external_id || null,
      created_by: userId,
    })
    .select()
    .single();
  if (error) throw new Error(error.message);
  return data;
}

export async function updatePlayerStatus(
  supabase: SupabaseClient,
  playerId: string,
  status: "active" | "inactive"
) {
  const { error } = await supabase
    .from("players")
    .update({ status })
    .eq("id", playerId);
  if (error) throw new Error(error.message);
}

export async function addToRoster(
  supabase: SupabaseClient,
  userId: string,
  input: { teamId: string; competitionId: string; playerId: string }
) {
  const { error: rosterError } = await supabase.from("team_roster").insert({
    team_id: input.teamId,
    player_id: input.playerId,
    competition_id: input.competitionId,
  });
  if (rosterError) throw new Error(rosterError.message);

  await supabase.from("roster_events").insert({
    team_id: input.teamId,
    player_id: input.playerId,
    competition_id: input.competitionId,
    event_type: "add",
    registered_by: userId,
  });
}

export async function registerTransfer(
  supabase: SupabaseClient,
  userId: string,
  input: {
    competitionId: string;
    playerId: string;
    fromTeamId: string;
    toTeamId: string;
  }
) {
  const { error: tError } = await supabase.from("transfers").insert({
    competition_id: input.competitionId,
    player_id: input.playerId,
    from_team_id: input.fromTeamId,
    to_team_id: input.toTeamId,
    registered_by: userId,
    status: "completed",
  });
  if (tError) throw new Error(tError.message);

  await supabase
    .from("team_roster")
    .update({ left_at: new Date().toISOString() })
    .eq("team_id", input.fromTeamId)
    .eq("player_id", input.playerId)
    .is("left_at", null);

  const { error: rError } = await supabase.from("team_roster").insert({
    team_id: input.toTeamId,
    player_id: input.playerId,
    competition_id: input.competitionId,
  });
  if (rError) throw new Error(rError.message);
}

export async function listTransfers(
  supabase: SupabaseClient,
  competitionId: string
) {
  const { data, error } = await supabase
    .from("transfers")
    .select(
      `*, player:players(display_name), from_team:from_team_id(name), to_team:to_team_id(name)`
    )
    .eq("competition_id", competitionId)
    .order("created_at", { ascending: false })
    .limit(50);
  if (error) throw new Error(error.message);
  return data;
}