import { eq, and } from "drizzle-orm";
import { database } from "~/database/context";
import * as schema from "~/database/schema";
import { findDraftableSportsSeasonBySportId } from "~/models/sports-season";
import { logger } from "~/lib/logger";

export type SeasonSport = typeof schema.seasonSports.$inferSelect;
export type NewSeasonSport = typeof schema.seasonSports.$inferInsert;

export async function linkSportToSeason(
  seasonId: string,
  sportsSeasonId: string
): Promise<SeasonSport> {
  const db = database();
  const [link] = await db
    .insert(schema.seasonSports)
    .values({
      seasonId,
      sportsSeasonId,
    })
    .returning();
  return link;
}

export async function linkMultipleSportsToSeason(
  data: NewSeasonSport[]
): Promise<SeasonSport[]> {
  const db = database();
  return await db
    .insert(schema.seasonSports)
    .values(data)
    .returning();
}

export async function unlinkSportFromSeason(
  seasonId: string,
  sportsSeasonId: string
): Promise<void> {
  const db = database();
  await db
    .delete(schema.seasonSports)
    .where(
      and(
        eq(schema.seasonSports.seasonId, seasonId),
        eq(schema.seasonSports.sportsSeasonId, sportsSeasonId)
      )
    );
}

export async function findSeasonSportsBySeasonId(
  seasonId: string
): Promise<SeasonSport[]> {
  const db = database();
  return await db.query.seasonSports.findMany({
    where: eq(schema.seasonSports.seasonId, seasonId),
    with: {
      sportsSeason: {
        with: {
          sport: true,
        },
      },
    },
  });
}

export async function findSeasonSportsBySportsSeasonId(
  sportsSeasonId: string
): Promise<SeasonSport[]> {
  const db = database();
  return await db.query.seasonSports.findMany({
    where: eq(schema.seasonSports.sportsSeasonId, sportsSeasonId),
    with: {
      season: {
        with: {
          league: true,
        },
      },
    },
  });
}


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

export async function applySportsFromTemplate(
  seasonId: string,
  templateId: string
): Promise<SeasonSport[]> {
  const db = database();

  const templateSports = await db.query.seasonTemplateSports.findMany({
    where: eq(schema.seasonTemplateSports.templateId, templateId),
  });

  if (templateSports.length === 0) {
    return [];
  }

  const resolved = await Promise.all(
    templateSports.map(async (ts) => {
      const season = await findDraftableSportsSeasonBySportId(ts.sportId);
      if (!season) {
        logger.warn(`No draftable season found for sport ${ts.sportId} in template ${templateId}`);
      }
      return season ?? null;
    })
  );

  const sportsSeasonIds = resolved
    .filter((s): s is NonNullable<typeof s> => s !== null)
    .map((s) => s.id);

  if (sportsSeasonIds.length === 0) {
    return [];
  }

  return await linkMultipleSportsToSeason(
    sportsSeasonIds.map((sportsSeasonId) => ({ seasonId, sportsSeasonId }))
  );
}

/**
 * Get sports for a season in a simple format for eligibility calculations
 */
export async function getSeasonSportsSimple(seasonId: string, providedDb?: ReturnType<typeof database>) {
  const db = providedDb || database();

  const seasonSports = await db.query.seasonSports.findMany({
    where: eq(schema.seasonSports.seasonId, seasonId),
    with: {
      sportsSeason: {
        with: {
          sport: true,
        },
      },
    },
  });

  return seasonSports.map((ss) => ({
    id: ss.sportsSeason.sport.id,
    name: ss.sportsSeason.sport.name,
  }));
}