import { database } from "~/database/context";
import * as schema from "~/database/schema";
import { and, desc, inArray, isNotNull } from "drizzle-orm";

/**
 * Resolve which bracket template a sports season's placements should be scored against.
 *
 * A sports season can own several scoring events — a bracket plus schedule events, or a
 * re-created bracket alongside a stale one — and only some of them carry a
 * bracketTemplateId. Picking an arbitrary row is not harmless: calculateBracketPoints
 * falls back to the standard single 5th–8th tier when the template id is null, which
 * silently collapses the two-tier templates (llws_20, afl_10) so a team locked into
 * 5th–6th and one locked into 7th–8th both score the flat 5–8 average. The 3rd/4th
 * distinction that llws_20 and fifa_48 have goes the same way.
 *
 * So: only events that actually carry a template are considered, most recent first —
 * matching the "a re-created event wins over a stale one" rule the LLWS simulator uses
 * when it picks its bracket event.
 *
 * Every requested season gets an entry, null when it has no bracket event, so callers
 * can cache the negative result too.
 */
export async function getBracketTemplateIdsForSportsSeasons(
  sportsSeasonIds: string[],
  providedDb?: ReturnType<typeof database>
): Promise<Map<string, string | null>> {
  const resolved = new Map<string, string | null>(
    sportsSeasonIds.map((id) => [id, null])
  );
  if (sportsSeasonIds.length === 0) return resolved;

  const db = providedDb || database();

  const events = await db.query.scoringEvents.findMany({
    where: and(
      inArray(schema.scoringEvents.sportsSeasonId, sportsSeasonIds),
      isNotNull(schema.scoringEvents.bracketTemplateId)
    ),
    columns: { sportsSeasonId: true, bracketTemplateId: true },
    // createdAt can tie when a bracket is generated in the same transaction as a
    // sibling event, so id breaks the tie and keeps the choice deterministic.
    orderBy: [desc(schema.scoringEvents.createdAt), desc(schema.scoringEvents.id)],
  });

  for (const event of events) {
    // Ordered newest-first, so the first row seen for a season is the one to keep.
    // The isNotNull filter means bracketTemplateId is set, but a mocked or partial row
    // could still carry null — skip those rather than caching a null as a real answer.
    if (resolved.get(event.sportsSeasonId) === null && event.bracketTemplateId) {
      resolved.set(event.sportsSeasonId, event.bracketTemplateId);
    }
  }

  return resolved;
}

/**
 * Single-season form of getBracketTemplateIdsForSportsSeasons.
 */
export async function getBracketTemplateIdForSportsSeason(
  sportsSeasonId: string,
  providedDb?: ReturnType<typeof database>
): Promise<string | null> {
  const resolved = await getBracketTemplateIdsForSportsSeasons([sportsSeasonId], providedDb);
  return resolved.get(sportsSeasonId) ?? null;
}