import { eq, and } from "drizzle-orm";
import { database } from "~/database/context";
import * as schema from "~/database/schema";
import type { SeasonStatus } from "~/models/season";

export type League = typeof schema.leagues.$inferSelect;
export type NewLeague = typeof schema.leagues.$inferInsert;

export async function createLeague(data: NewLeague): Promise<League> {
  const db = database();
  const [league] = await db
    .insert(schema.leagues)
    .values(data)
    .returning();
  return league;
}

export async function findLeagueById(id: string): Promise<League | undefined> {
  const db = database();
  return await db.query.leagues.findFirst({
    where: eq(schema.leagues.id, id),
  });
}

export async function findLeaguesByCreator(userId: string): Promise<League[]> {
  const db = database();
  return await db.query.leagues.findMany({
    where: eq(schema.leagues.createdBy, userId),
    orderBy: (leagues, { desc }) => [desc(leagues.createdAt)],
  });
}

export async function updateLeague(
  id: string,
  data: Partial<NewLeague>
): Promise<League> {
  const db = database();
  const [league] = await db
    .update(schema.leagues)
    .set({ ...data, updatedAt: new Date() })
    .where(eq(schema.leagues.id, id))
    .returning();
  return league;
}

export async function deleteLeague(id: string): Promise<void> {
  const db = database();
  await db.delete(schema.leagues).where(eq(schema.leagues.id, id));
}

export async function setCurrentSeason(
  leagueId: string,
  seasonId: string | null
): Promise<League> {
  return await updateLeague(leagueId, { currentSeasonId: seasonId });
}

export type LeagueWithStatus = League & {
  currentSeasonStatus: SeasonStatus | null;
  currentSeasonYear: number | null;
};

export async function listLeaguesWithStatus(): Promise<LeagueWithStatus[]> {
  const db = database();
  const rows = await db
    .select({
      id: schema.leagues.id,
      name: schema.leagues.name,
      createdBy: schema.leagues.createdBy,
      currentSeasonId: schema.leagues.currentSeasonId,
      isPublicDraftBoard: schema.leagues.isPublicDraftBoard,
      discordWebhookUrl: schema.leagues.discordWebhookUrl,
      discordPicksAnnouncementEnabled: schema.leagues.discordPicksAnnouncementEnabled,
      createdAt: schema.leagues.createdAt,
      updatedAt: schema.leagues.updatedAt,
      currentSeasonStatus: schema.seasons.status,
      currentSeasonYear: schema.seasons.year,
    })
    .from(schema.leagues)
    .leftJoin(schema.seasons, eq(schema.seasons.id, schema.leagues.currentSeasonId))
    .orderBy(schema.leagues.createdAt);

  return rows.map((r) => ({
    ...r,
    currentSeasonStatus: (r.currentSeasonStatus as SeasonStatus) ?? null,
    currentSeasonYear: r.currentSeasonYear ?? null,
  }));
}

export async function listLeagues(options?: {
  limit?: number;
  offset?: number;
}): Promise<League[]> {
  const db = database();
  return await db.query.leagues.findMany({
    limit: options?.limit,
    offset: options?.offset,
    orderBy: (leagues, { desc }) => [desc(leagues.createdAt)],
  });
}

export async function findLeaguesWithActiveSeasonsByUserId(
  userId: string
): Promise<League[]> {
  const db = database();

  const leagueFields = {
    id: schema.leagues.id,
    name: schema.leagues.name,
    createdBy: schema.leagues.createdBy,
    currentSeasonId: schema.leagues.currentSeasonId,
    isPublicDraftBoard: schema.leagues.isPublicDraftBoard,
    discordWebhookUrl: schema.leagues.discordWebhookUrl,
    discordPicksAnnouncementEnabled: schema.leagues.discordPicksAnnouncementEnabled,
    createdAt: schema.leagues.createdAt,
    updatedAt: schema.leagues.updatedAt,
  };

  // Leagues where user has a team in the current season
  const leaguesWithTeam = await db
    .select(leagueFields)
    .from(schema.leagues)
    .innerJoin(schema.teams, eq(schema.teams.seasonId, schema.leagues.currentSeasonId))
    .where(eq(schema.teams.ownerId, userId));

  // Leagues where user is a commissioner (with or without a team)
  const leaguesAsCommissioner = await db
    .select(leagueFields)
    .from(schema.leagues)
    .innerJoin(schema.commissioners, eq(schema.commissioners.leagueId, schema.leagues.id))
    .where(eq(schema.commissioners.userId, userId));

  // Deduplicate by id in case user is both a commissioner and has a team, then sort
  const leagueMap = new Map<string, League>();
  for (const league of [...leaguesWithTeam, ...leaguesAsCommissioner]) {
    leagueMap.set(league.id, league);
  }
  return [...leagueMap.values()].toSorted(
    (a, b) => b.createdAt.getTime() - a.createdAt.getTime()
  );
}

/**
 * Check if a user is a member of a league (has a team in current season)
 */
export async function isUserLeagueMember(
  leagueId: string,
  userId: string
): Promise<boolean> {
  const db = database();
  
  const league = await db.query.leagues.findFirst({
    where: eq(schema.leagues.id, leagueId),
  });
  
  if (!league || !league.currentSeasonId) {
    return false;
  }
  
  const team = await db.query.teams.findFirst({
    where: and(
      eq(schema.teams.seasonId, league.currentSeasonId),
      eq(schema.teams.ownerId, userId)
    ),
  });
  
  return !!team;
}