import { createRequire } from "node:module";
import {
  existsSync,
  mkdirSync,
  readFileSync,
  writeFileSync
} from "node:fs";
import { dirname, join } from "node:path";
import type initSqlJsType from "sql.js";
import type {
  Database as SqlJsDatabase,
  ParamsObject,
  SqlJsStatic,
  SqlValue,
  Statement
} from "sql.js";
import type {
  AiContentType,
  AiGeneratedArtifact,
  AiGenerationMode,
  AiPromptPack,
  AiPromptTemplate,
  CoachSummary,
  ConferenceSummary,
  GamePlayerStatsSummary,
  GameSummary,
  GameTeamStatsSummary,
  ImportProbeResult,
  PlayerSummary,
  PlayHighlightRecord,
  ScreenshotExtractionPayload,
  ScreenshotExtractionRecord,
  ScreenshotExtractionStatus,
  ScreenshotParsedRow,
  ScreenshotRecord,
  ScreenshotStatRecord,
  ScreenshotType,
  SourcePointer,
  StatPayload,
  TeamSummary
} from "../../shared/src";
import {
  AI_PROMPT_PACKS,
  AI_PROMPT_TEMPLATE_SEEDS
} from "../../ai/src";
import {
  SCREENSHOT_TEMPLATE_VERSION,
  getScreenshotCropTemplate
} from "../../shared/src";

const require = createRequire(import.meta.url);
const initSqlJs = require("sql.js") as typeof initSqlJsType;

export type StoredSnapshotRef = {
  snapshotId: number;
  dynastyId: string;
  sourceHash: string;
};

export class DynastyDatabase {
  readonly name: string;
  private readonly db: SqlJsDatabase;
  private closed = false;

  constructor(databasePath: string, db: SqlJsDatabase) {
    this.name = databasePath;
    this.db = db;
  }

  exec(sql: string): void {
    this.assertOpen();
    this.db.exec(sql);
  }

  run(sql: string, params: ParamsObject = {}): void {
    this.assertOpen();
    this.db.run(sql, normalizeParams(params));
  }

  get<T>(sql: string, params: ParamsObject = {}): T | undefined {
    this.assertOpen();
    const statement = this.db.prepare(sql);
    try {
      statement.bind(normalizeParams(params));
      if (!statement.step()) return undefined;
      return statement.getAsObject() as T;
    } finally {
      statement.free();
    }
  }

  all<T>(sql: string, params: ParamsObject = {}): T[] {
    this.assertOpen();
    const statement = this.db.prepare(sql);
    const rows: T[] = [];
    try {
      statement.bind(normalizeParams(params));
      while (statement.step()) {
        rows.push(statement.getAsObject() as T);
      }
      return rows;
    } finally {
      statement.free();
    }
  }

  prepare(sql: string): Statement {
    this.assertOpen();
    return this.db.prepare(sql);
  }

  transaction<T>(work: () => T): T {
    this.exec("BEGIN IMMEDIATE");
    try {
      const result = work();
      this.exec("COMMIT");
      this.persist();
      return result;
    } catch (error) {
      this.exec("ROLLBACK");
      throw error;
    }
  }

  lastInsertRowid(): number {
    const row = this.get<{ id: number }>("SELECT last_insert_rowid() AS id");
    return Number(row?.id ?? 0);
  }

  persist(): void {
    this.assertOpen();
    mkdirSync(dirname(this.name), { recursive: true });
    writeFileSync(this.name, Buffer.from(this.db.export()));
  }

  close(): void {
    if (this.closed) return;
    this.persist();
    this.db.close();
    this.closed = true;
  }

  private assertOpen(): void {
    if (this.closed) throw new Error("Dynasty database is already closed.");
  }
}

type SnapshotRow = {
  id: number;
  dynasty_id: string;
  source_file_name: string;
  source_hash: string;
  archive_path: string;
  imported_at: string;
  opened_at: string;
  season_year: number | null;
  dynasty_year: number | null;
  week: number | null;
  week_type: string | null;
  stage: string | null;
  offseason_stage: number | null;
  offseason_advances: number | null;
  schema_game_year: number | null;
  schema_game_type: string | null;
  schema_major: number | null;
  schema_minor: number | null;
  table_count: number;
  user_team_row: number | null;
  user_coach_row: number | null;
  validation_json: string;
  diagnostics_json: string;
};

type ConferenceRow = {
  game_row: number;
  name: string;
  enum_name: string | null;
  team_rows_json: string;
  source_json: string;
};

type TeamRow = {
  game_row: number;
  team_index: number | null;
  display_name: string;
  short_name: string | null;
  nick_name: string | null;
  conference_row: number | null;
  conference_name: string | null;
  rank: number | null;
  media_rank: number | null;
  coaches_rank: number | null;
  cfp_rank: number | null;
  prestige: number | null;
  prestige_rank: number | null;
  colors_json: string;
  rival_team_rows_json: string;
  source_json: string;
};

type CoachRow = {
  game_row: number;
  name: string | null;
  first_name: string | null;
  last_name: string | null;
  role: string | null;
  team_index: number | null;
  team_row: number | null;
  level: number | null;
  prestige: string | null;
  prestige_score: number | null;
  contract_status: string | null;
  job_security: string | null;
  is_user_controlled: number;
  source_json: string;
};

type PlayerRow = {
  game_row: number;
  first_name: string | null;
  last_name: string | null;
  display_name: string;
  position: string | null;
  jersey_number: number | null;
  school_year: string | null;
  overall: number | null;
  team_index: number | null;
  team_row: number | null;
  roster_team_row: number | null;
  source_json: string;
};

type GameRow = {
  game_row: number;
  season_year: number | null;
  week: number | null;
  week_type: string | null;
  game_number: number | null;
  status: string | null;
  home_team_row: number | null;
  away_team_row: number | null;
  home_score: number | null;
  away_score: number | null;
  quarter_scores_json: string;
  is_simmed: number | null;
  is_game_of_week: number | null;
  is_major_game: number;
  major_game_reasons_json: string;
  attendance: number | null;
  weather: string | null;
  source_json: string;
};

type GameTeamStatsRow = {
  game_row: number;
  team_row: number | null;
  side: "home" | "away";
  stats_json: string;
  source_json: string;
};

type GamePlayerStatsRow = {
  game_row: number;
  player_row: number;
  player_name: string;
  team_row: number | null;
  opponent_team_row: number | null;
  stat_group: string;
  stats_json: string;
  source_json: string;
};

type ScreenshotRow = {
  id: number;
  dynasty_id: string | null;
  snapshot_id: number | null;
  original_file_name: string;
  stored_path: string;
  file_hash: string;
  screenshot_type: string;
  imported_at: string;
};

type ScreenshotExtractionRow = {
  id: number;
  screenshot_id: number;
  screenshot_type: string;
  status: string;
  template_version: number;
  template_json: string;
  extracted_json: string;
  error_message: string | null;
  created_at: string;
  completed_at: string | null;
};

type PlayHighlightRow = {
  id: number;
  dynasty_id: string | null;
  snapshot_id: number | null;
  game_row: number;
  screenshot_id: number;
  extraction_id: number;
  sequence: number;
  period: string | null;
  clock: string | null;
  team: string | null;
  summary: string;
  score: string | null;
  raw_text: string;
  dedupe_key: string;
  approved_at: string;
};

type ScreenshotStatRow = {
  id: number;
  dynasty_id: string | null;
  snapshot_id: number | null;
  game_row: number;
  screenshot_id: number;
  extraction_id: number;
  row_type: string;
  player_name: string;
  team: string | null;
  raw_row_json: string;
  corrected_row_json: string;
  dedupe_key: string;
  approved_at: string;
};

type AiPromptPackRow = {
  id: string;
  name: string;
  description: string;
  personalities_json: string;
};

type AiPromptTemplateRow = {
  id: number;
  dynasty_id: string | null;
  pack_id: string;
  pack_name: string;
  content_type: string;
  mode: string;
  name: string;
  template: string;
  is_custom: number;
  source_template_id: number | null;
  updated_at: string;
};

type AiGeneratedArtifactRow = {
  id: number;
  dynasty_id: string;
  snapshot_id: number;
  game_row: number;
  content_type: string;
  mode: string;
  prompt_template_id: number;
  provider_base_url: string;
  model: string;
  rendered_prompt: string;
  evidence_json: string;
  content_json: string;
  created_at: string;
  updated_at: string;
};

let sqlModulePromise: Promise<SqlJsStatic> | null = null;

export async function openDynastyDatabase(
  databasePath: string
): Promise<DynastyDatabase> {
  mkdirSync(dirname(databasePath), { recursive: true });
  const SQL = await getSqlModule();
  const existingBytes = existsSync(databasePath) ? readFileSync(databasePath) : null;
  const db = new DynastyDatabase(
    databasePath,
    existingBytes ? new SQL.Database(existingBytes) : new SQL.Database()
  );
  migrate(db);
  seedAiDefaults(db);
  db.persist();
  return db;
}

export function findSnapshotByHash(
  db: DynastyDatabase,
  sourceHash: string
): StoredSnapshotRef | null {
  const row = db.get<StoredSnapshotRef>(
    "SELECT id AS snapshotId, dynasty_id AS dynastyId, source_hash AS sourceHash FROM snapshot WHERE source_hash = @sourceHash",
    { sourceHash }
  );
  return row ?? null;
}

export function saveParsedSnapshot(
  db: DynastyDatabase,
  result: ImportProbeResult
): StoredSnapshotRef {
  if (!result.dynastyId) {
    throw new Error("Cannot persist snapshot without a detected dynasty ID.");
  }
  const dynastyId = result.dynastyId;

  const existing = findSnapshotByHash(db, result.sourceHash);
  if (existing) return existing;

  const now = new Date().toISOString();
  return db.transaction(() => {
    db.run(
      `
      INSERT INTO dynasty (
        id, league_id, display_name, game_version, user_team_row, user_coach_row, created_at, updated_at
      )
      VALUES (@id, @leagueId, @displayName, @gameVersion, @userTeamRow, @userCoachRow, @now, @now)
      ON CONFLICT(id) DO UPDATE SET
        display_name = excluded.display_name,
        game_version = excluded.game_version,
        user_team_row = excluded.user_team_row,
        user_coach_row = excluded.user_coach_row,
        updated_at = excluded.updated_at
      `,
      {
        id: dynastyId,
        leagueId: leagueIdFromDynastyId(dynastyId),
        displayName: result.userTeam?.team?.displayName ?? dynastyId,
        gameVersion: `${result.schema.gameType ?? "unknown"} ${result.schema.major ?? "?"}.${result.schema.minor ?? "?"}`,
        userTeamRow: result.userTeam?.team?.row ?? null,
        userCoachRow: result.userTeam?.coach?.row ?? null,
        now
      }
    );

    db.run(
      `
      INSERT INTO snapshot (
        dynasty_id, source_file_name, source_hash, archive_path, imported_at, opened_at,
        season_year, dynasty_year, week, week_type, stage, offseason_stage, offseason_advances,
        schema_game_year, schema_game_type, schema_major, schema_minor, table_count,
        user_team_row, user_coach_row, validation_json, diagnostics_json
      )
      VALUES (
        @dynastyId, @sourceFileName, @sourceHash, @archivePath, @importedAt, @openedAt,
        @seasonYear, @dynastyYear, @week, @weekType, @stage, @offseasonStage, @offseasonAdvances,
        @schemaGameYear, @schemaGameType, @schemaMajor, @schemaMinor, @tableCount,
        @userTeamRow, @userCoachRow, @validationJson, @diagnosticsJson
      )
      `,
      {
        dynastyId,
        sourceFileName: result.sourceFileName,
        sourceHash: result.sourceHash,
        archivePath: result.archivePath,
        importedAt: now,
        openedAt: result.openedAt,
        seasonYear: result.calendar?.seasonYear ?? null,
        dynastyYear: result.calendar?.dynastyYear ?? null,
        week: result.calendar?.week ?? null,
        weekType: result.calendar?.weekType ?? null,
        stage: result.calendar?.stage ?? null,
        offseasonStage: result.calendar?.offseasonStage ?? null,
        offseasonAdvances: result.calendar?.offseasonAdvances ?? null,
        schemaGameYear: result.schema.gameYear,
        schemaGameType: result.schema.gameType,
        schemaMajor: result.schema.major,
        schemaMinor: result.schema.minor,
        tableCount: result.tableCount,
        userTeamRow: result.userTeam?.team?.row ?? null,
        userCoachRow: result.userTeam?.coach?.row ?? null,
        validationJson: stringifyJson(result.validation),
        diagnosticsJson: stringifyJson(result.diagnostics)
      }
    );
    const snapshotId = db.lastInsertRowid();

    insertConferences(db, snapshotId, result.conferences);
    insertTeams(db, snapshotId, result.teams);
    insertCoaches(db, snapshotId, result.coaches);
    insertPlayers(db, snapshotId, result.players);
    insertGames(db, snapshotId, result.games);
    insertTeamGameStats(db, snapshotId, result.teamGameStats);
    insertPlayerGameStats(db, snapshotId, result.playerGameStats);

    return {
      snapshotId,
      dynastyId,
      sourceHash: result.sourceHash
    };
  });
}

export function loadLatestSnapshot(
  db: DynastyDatabase,
  dynastyId: string | null = null
): ImportProbeResult | null {
  const row = dynastyId
    ? db.get<SnapshotRow>(
        "SELECT * FROM snapshot WHERE dynasty_id = @dynastyId ORDER BY imported_at DESC, id DESC LIMIT 1",
        { dynastyId }
      )
    : db.get<SnapshotRow>(
        "SELECT * FROM snapshot ORDER BY imported_at DESC, id DESC LIMIT 1"
      );

  return row ? loadSnapshotById(db, row.id) : null;
}

export function loadSnapshotById(
  db: DynastyDatabase,
  snapshotId: number
): ImportProbeResult | null {
  const snapshot = db.get<SnapshotRow>("SELECT * FROM snapshot WHERE id = @snapshotId", {
    snapshotId
  });
  if (!snapshot) return null;

  const conferences = db
    .all<ConferenceRow>(
      "SELECT * FROM conference WHERE snapshot_id = @snapshotId ORDER BY name, game_row",
      { snapshotId }
    )
    .map(rowToConference);
  const teams = db
    .all<TeamRow>("SELECT * FROM team WHERE snapshot_id = @snapshotId ORDER BY game_row", {
      snapshotId
    })
    .map(rowToTeam);
  const teamsByRow = new Map(teams.map((team) => [team.row, team]));
  const coaches = db
    .all<CoachRow>(
      "SELECT * FROM coach WHERE snapshot_id = @snapshotId ORDER BY game_row",
      { snapshotId }
    )
    .map(rowToCoach);
  const players = db
    .all<PlayerRow>(
      "SELECT * FROM player WHERE snapshot_id = @snapshotId ORDER BY COALESCE(team_row, 999999), position, display_name, game_row",
      { snapshotId }
    )
    .map(rowToPlayer);
  const games = db
    .all<GameRow>(
      `
      SELECT * FROM game
      WHERE snapshot_id = @snapshotId
      ORDER BY COALESCE(season_year, 999999), COALESCE(week, 999999), week_type, COALESCE(game_number, 999999), game_row
      `,
      { snapshotId }
    )
    .map((row) => rowToGame(row, teamsByRow));
  const teamGameStats = db
    .all<GameTeamStatsRow>(
      "SELECT * FROM game_team_stats WHERE snapshot_id = @snapshotId ORDER BY game_row, side",
      { snapshotId }
    )
    .map(rowToGameTeamStats);
  const playerGameStats = db
    .all<GamePlayerStatsRow>(
      "SELECT * FROM game_player_stats WHERE snapshot_id = @snapshotId ORDER BY game_row, COALESCE(team_row, 999999), player_name, stat_group, player_row",
      { snapshotId }
    )
    .map(rowToGamePlayerStats);
  const userTeam = teamsByRow.get(snapshot.user_team_row ?? -1) ?? null;
  const userCoach =
    coaches.find((coach) => coach.row === snapshot.user_coach_row) ?? null;

  return {
    sourceFileName: snapshot.source_file_name,
    sourceHash: snapshot.source_hash,
    archivePath: snapshot.archive_path,
    openedAt: snapshot.opened_at,
    dynastyId: snapshot.dynasty_id,
    schema: {
      gameYear: snapshot.schema_game_year,
      gameType: snapshot.schema_game_type,
      major: snapshot.schema_major,
      minor: snapshot.schema_minor
    },
    tableCount: snapshot.table_count,
    calendar: {
      stage: snapshot.stage,
      weekType: snapshot.week_type,
      week: snapshot.week,
      seasonYear: snapshot.season_year,
      dynastyYear: snapshot.dynasty_year,
      offseasonStage: snapshot.offseason_stage,
      offseasonAdvances: snapshot.offseason_advances
    },
    userTeam: {
      team: userTeam,
      coach: userCoach
    },
    conferences,
    coaches,
    teams,
    players,
    games,
    teamGameStats,
    playerGameStats,
    validation: parseJson<ImportProbeResult["validation"]>(
      snapshot.validation_json,
      {}
    ),
    diagnostics: parseJson<string[]>(snapshot.diagnostics_json, []),
    importStatus: {
      snapshotId: snapshot.id,
      imported: false,
      duplicate: false,
      databasePath: db.name
    }
  };
}

export function findScreenshotByHash(
  db: DynastyDatabase,
  fileHash: string
): ScreenshotRecord | null {
  const row = db.get<ScreenshotRow>(
    "SELECT * FROM screenshot WHERE file_hash = @fileHash",
    { fileHash }
  );
  return row ? rowToScreenshot(row) : null;
}

export function loadScreenshotById(
  db: DynastyDatabase,
  id: number
): ScreenshotRecord | null {
  const row = db.get<ScreenshotRow>("SELECT * FROM screenshot WHERE id = @id", {
    id
  });
  return row ? rowToScreenshot(row) : null;
}

export function saveScreenshotRecord(
  db: DynastyDatabase,
  value: {
    dynastyId: string | null;
    snapshotId: number | null;
    originalFileName: string;
    storedPath: string;
    fileHash: string;
    screenshotType?: ScreenshotType;
    importedAt?: string;
  }
): ScreenshotRecord {
  const existing = findScreenshotByHash(db, value.fileHash);
  if (existing) return existing;

  const importedAt = value.importedAt ?? new Date().toISOString();
  return db.transaction(() => {
    db.run(
      `
      INSERT INTO screenshot (
        dynasty_id, snapshot_id, original_file_name, stored_path, file_hash, screenshot_type, imported_at
      )
      VALUES (
        @dynastyId, @snapshotId, @originalFileName, @storedPath, @fileHash, @screenshotType, @importedAt
      )
      `,
      {
        dynastyId: value.dynastyId,
        snapshotId: value.snapshotId,
        originalFileName: value.originalFileName,
        storedPath: value.storedPath,
        fileHash: value.fileHash,
        screenshotType: value.screenshotType ?? "unknown",
        importedAt
      }
    );
    const id = db.lastInsertRowid();
    return {
      id,
      dynastyId: value.dynastyId,
      snapshotId: value.snapshotId,
      originalFileName: value.originalFileName,
      storedPath: value.storedPath,
      fileHash: value.fileHash,
      screenshotType: value.screenshotType ?? "unknown",
      importedAt
    };
  });
}

export function loadScreenshots(
  db: DynastyDatabase,
  dynastyId: string | null = null
): ScreenshotRecord[] {
  const rows = dynastyId
    ? db.all<ScreenshotRow>(
        "SELECT * FROM screenshot WHERE dynasty_id = @dynastyId ORDER BY imported_at DESC, id DESC",
        { dynastyId }
      )
    : db.all<ScreenshotRow>(
        "SELECT * FROM screenshot ORDER BY imported_at DESC, id DESC"
      );
  return rows.map(rowToScreenshot);
}

export function updateScreenshotType(
  db: DynastyDatabase,
  id: number,
  screenshotType: ScreenshotType
): ScreenshotRecord {
  return db.transaction(() => {
    db.run(
      "UPDATE screenshot SET screenshot_type = @screenshotType WHERE id = @id",
      { id, screenshotType }
    );
    const row = db.get<ScreenshotRow>("SELECT * FROM screenshot WHERE id = @id", {
      id
    });
    if (!row) throw new Error(`Screenshot ${id} was not found.`);
    return rowToScreenshot(row);
  });
}

export function loadScreenshotExtractions(
  db: DynastyDatabase,
  dynastyId: string | null = null
): ScreenshotExtractionRecord[] {
  const rows = dynastyId
    ? db.all<ScreenshotExtractionRow>(
        `
        SELECT screenshot_extraction.*
        FROM screenshot_extraction
        INNER JOIN screenshot ON screenshot.id = screenshot_extraction.screenshot_id
        WHERE screenshot.dynasty_id = @dynastyId
        ORDER BY screenshot_extraction.created_at DESC, screenshot_extraction.id DESC
        `,
        { dynastyId }
      )
    : db.all<ScreenshotExtractionRow>(
        "SELECT * FROM screenshot_extraction ORDER BY created_at DESC, id DESC"
      );
  return rows.map(rowToScreenshotExtraction);
}

export function loadScreenshotExtractionById(
  db: DynastyDatabase,
  id: number
): ScreenshotExtractionRecord | null {
  const row = db.get<ScreenshotExtractionRow>(
    "SELECT * FROM screenshot_extraction WHERE id = @id",
    { id }
  );
  return row ? rowToScreenshotExtraction(row) : null;
}

export function createScreenshotExtraction(
  db: DynastyDatabase,
  screenshotId: number,
  value: {
    status?: ScreenshotExtractionStatus;
    extractedJson?: ScreenshotExtractionPayload;
    errorMessage?: string | null;
    completedAt?: string | null;
  } = {}
): ScreenshotExtractionRecord {
  return db.transaction(() => {
    const screenshot = db.get<ScreenshotRow>(
      "SELECT * FROM screenshot WHERE id = @screenshotId",
      { screenshotId }
    );
    if (!screenshot) throw new Error(`Screenshot ${screenshotId} was not found.`);

    const screenshotType = normalizeScreenshotType(screenshot.screenshot_type);
    const template = getScreenshotCropTemplate(screenshotType);
    if (!template) {
      throw new Error(
        `Screenshot ${screenshotId} needs a known type before extraction.`
      );
    }

    const createdAt = new Date().toISOString();
    db.run(
      `
      INSERT INTO screenshot_extraction (
        screenshot_id, screenshot_type, status, template_version, template_json, extracted_json, error_message, created_at, completed_at
      )
      VALUES (
        @screenshotId, @screenshotType, @status, @templateVersion, @templateJson, @extractedJson, @errorMessage, @createdAt, @completedAt
      )
      `,
      {
        screenshotId,
        screenshotType,
        status: value.status ?? "pending",
        templateVersion: SCREENSHOT_TEMPLATE_VERSION,
        templateJson: stringifyJson(template),
        extractedJson: stringifyJson(value.extractedJson ?? {}),
        errorMessage: value.errorMessage ?? null,
        completedAt: value.completedAt ?? null,
        createdAt
      }
    );

    const row = db.get<ScreenshotExtractionRow>(
      "SELECT * FROM screenshot_extraction WHERE id = @id",
      { id: db.lastInsertRowid() }
    );
    if (!row) throw new Error("Screenshot extraction could not be loaded.");
    return rowToScreenshotExtraction(row);
  });
}

export function updateScreenshotExtractionResult(
  db: DynastyDatabase,
  id: number,
  value: {
    status: ScreenshotExtractionStatus;
    extractedJson?: ScreenshotExtractionPayload;
    errorMessage?: string | null;
    completedAt?: string | null;
  }
): ScreenshotExtractionRecord {
  return db.transaction(() => {
    const existing = db.get<ScreenshotExtractionRow>(
      "SELECT * FROM screenshot_extraction WHERE id = @id",
      { id }
    );
    if (!existing) throw new Error(`Screenshot extraction ${id} was not found.`);

    const extractedJson = parseJson<ScreenshotExtractionPayload>(
      existing.extracted_json,
      {}
    );
    db.run(
      `
      UPDATE screenshot_extraction
      SET status = @status,
          extracted_json = @extractedJson,
          error_message = @errorMessage,
          completed_at = @completedAt
      WHERE id = @id
      `,
      {
        id,
        status: value.status,
        extractedJson: stringifyJson({
          ...extractedJson,
          ...(value.extractedJson ?? {})
        }),
        errorMessage:
          value.errorMessage === undefined
            ? existing.error_message
            : value.errorMessage,
        completedAt:
          value.completedAt === undefined ? existing.completed_at : value.completedAt
      }
    );

    const row = db.get<ScreenshotExtractionRow>(
      "SELECT * FROM screenshot_extraction WHERE id = @id",
      { id }
    );
    if (!row) throw new Error(`Screenshot extraction ${id} could not be loaded.`);
    return rowToScreenshotExtraction(row);
  });
}

export function updateScreenshotExtractionText(
  db: DynastyDatabase,
  id: number,
  text: string
): ScreenshotExtractionRecord {
  return db.transaction(() => {
    const existing = db.get<ScreenshotExtractionRow>(
      "SELECT * FROM screenshot_extraction WHERE id = @id",
      { id }
    );
    if (!existing) throw new Error(`Screenshot extraction ${id} was not found.`);

    const extractedJson = parseJson<ScreenshotExtractionPayload>(
      existing.extracted_json,
      {}
    );
    db.run(
      "UPDATE screenshot_extraction SET extracted_json = @extractedJson WHERE id = @id",
      {
        id,
        extractedJson: stringifyJson({
          ...extractedJson,
          text,
          rawRows: undefined,
          rows: undefined,
          approval: null
        })
      }
    );

    const row = db.get<ScreenshotExtractionRow>(
      "SELECT * FROM screenshot_extraction WHERE id = @id",
      { id }
    );
    if (!row) throw new Error(`Screenshot extraction ${id} could not be loaded.`);
    return rowToScreenshotExtraction(row);
  });
}

export function updateScreenshotExtractionRows(
  db: DynastyDatabase,
  id: number,
  rows: ScreenshotParsedRow[]
): ScreenshotExtractionRecord {
  return db.transaction(() => {
    const existing = db.get<ScreenshotExtractionRow>(
      "SELECT * FROM screenshot_extraction WHERE id = @id",
      { id }
    );
    if (!existing) throw new Error(`Screenshot extraction ${id} was not found.`);

    const extractedJson = parseJson<ScreenshotExtractionPayload>(
      existing.extracted_json,
      {}
    );
    db.run(
      "UPDATE screenshot_extraction SET extracted_json = @extractedJson WHERE id = @id",
      {
        id,
        extractedJson: stringifyJson({
          ...extractedJson,
          rows,
          approval: null
        })
      }
    );

    const row = db.get<ScreenshotExtractionRow>(
      "SELECT * FROM screenshot_extraction WHERE id = @id",
      { id }
    );
    if (!row) throw new Error(`Screenshot extraction ${id} could not be loaded.`);
    return rowToScreenshotExtraction(row);
  });
}

export function approveScreenshotHighlights(
  db: DynastyDatabase,
  extractionId: number,
  gameRow: number,
  snapshotId: number | null
): PlayHighlightRecord[] {
  return db.transaction(() => {
    const extraction = db.get<ScreenshotExtractionRow>(
      "SELECT * FROM screenshot_extraction WHERE id = @extractionId",
      { extractionId }
    );
    if (!extraction) {
      throw new Error(`Screenshot extraction ${extractionId} was not found.`);
    }

    const screenshot = db.get<ScreenshotRow>(
      "SELECT * FROM screenshot WHERE id = @screenshotId",
      { screenshotId: extraction.screenshot_id }
    );
    if (!screenshot) {
      throw new Error(`Screenshot ${extraction.screenshot_id} was not found.`);
    }

    const payload = parseJson<ScreenshotExtractionPayload>(
      extraction.extracted_json,
      {}
    );
    const rows = (payload.rows ?? []).filter(
      (row) => row.rowType === "highlight"
    );
    const approvedAt = new Date().toISOString();

    db.run(
      "DELETE FROM play_highlight WHERE extraction_id = @extractionId AND game_row = @gameRow",
      { extractionId, gameRow }
    );

    for (const [index, row] of rows.entries()) {
      const summary = rowString(row, "summary") ?? rowString(row, "rawText");
      if (!summary) continue;
      const rawText = rowString(row, "rawText") ?? summary;
      const sequence = rowNumber(row, "sequence") ?? index + 1;
      const dedupeKey = highlightDedupeKey({
        snapshotId,
        gameRow,
        period: rowString(row, "period"),
        clock: rowString(row, "clock"),
        score: rowString(row, "score"),
        summary
      });

      db.run(
        `
        INSERT OR IGNORE INTO play_highlight (
          dynasty_id, snapshot_id, game_row, screenshot_id, extraction_id, sequence,
          period, clock, team, summary, score, raw_text, dedupe_key, approved_at
        )
        VALUES (
          @dynastyId, @snapshotId, @gameRow, @screenshotId, @extractionId, @sequence,
          @period, @clock, @team, @summary, @score, @rawText, @dedupeKey, @approvedAt
        )
        `,
        {
          dynastyId: screenshot.dynasty_id,
          snapshotId,
          gameRow,
          screenshotId: screenshot.id,
          extractionId,
          sequence,
          period: rowString(row, "period"),
          clock: rowString(row, "clock"),
          team: rowString(row, "team"),
          summary,
          score: rowString(row, "score"),
          rawText,
          dedupeKey,
          approvedAt
        }
      );
    }

    saveExtractionApproval(db, extractionId, payload, {
      kind: "highlights",
      gameRow,
      rowCount: rows.length,
      approvedAt
    });

    return loadPlayHighlights(db, snapshotId, gameRow);
  });
}

export function approveScreenshotStats(
  db: DynastyDatabase,
  extractionId: number,
  gameRow: number,
  snapshotId: number | null
): ScreenshotStatRecord[] {
  return db.transaction(() => {
    const extraction = db.get<ScreenshotExtractionRow>(
      "SELECT * FROM screenshot_extraction WHERE id = @extractionId",
      { extractionId }
    );
    if (!extraction) {
      throw new Error(`Screenshot extraction ${extractionId} was not found.`);
    }

    const screenshot = db.get<ScreenshotRow>(
      "SELECT * FROM screenshot WHERE id = @screenshotId",
      { screenshotId: extraction.screenshot_id }
    );
    if (!screenshot) {
      throw new Error(`Screenshot ${extraction.screenshot_id} was not found.`);
    }

    const payload = parseJson<ScreenshotExtractionPayload>(
      extraction.extracted_json,
      {}
    );
    const rows = (payload.rows ?? []).filter(isScreenshotStatRow);
    const rawRows = payload.rawRows ?? [];
    const approvedAt = new Date().toISOString();

    db.run(
      "DELETE FROM screenshot_stat_row WHERE extraction_id = @extractionId AND game_row = @gameRow",
      { extractionId, gameRow }
    );

    for (const [index, row] of rows.entries()) {
      const rowType = rowString(row, "rowType");
      const playerName = rowString(row, "player");
      if (!rowType || !playerName) continue;
      const team = rowString(row, "team");
      const rawRow =
        rawRows[index]?.rowType === row.rowType ? rawRows[index] : row;
      const dedupeKey = screenshotStatDedupeKey({
        snapshotId,
        gameRow,
        rowType,
        playerName,
        team
      });

      db.run(
        `
        INSERT INTO screenshot_stat_row (
          dynasty_id, snapshot_id, game_row, screenshot_id, extraction_id,
          row_type, player_name, team, raw_row_json, corrected_row_json,
          dedupe_key, approved_at
        )
        VALUES (
          @dynastyId, @snapshotId, @gameRow, @screenshotId, @extractionId,
          @rowType, @playerName, @team, @rawRowJson, @correctedRowJson,
          @dedupeKey, @approvedAt
        )
        ON CONFLICT(dedupe_key) DO UPDATE SET
          screenshot_id = excluded.screenshot_id,
          extraction_id = excluded.extraction_id,
          raw_row_json = excluded.raw_row_json,
          corrected_row_json = excluded.corrected_row_json,
          approved_at = excluded.approved_at
        `,
        {
          dynastyId: screenshot.dynasty_id,
          snapshotId,
          gameRow,
          screenshotId: screenshot.id,
          extractionId,
          rowType,
          playerName,
          team,
          rawRowJson: stringifyJson(rawRow),
          correctedRowJson: stringifyJson(row),
          dedupeKey,
          approvedAt
        }
      );
    }

    saveExtractionApproval(db, extractionId, payload, {
      kind: "stats",
      gameRow,
      rowCount: rows.length,
      approvedAt
    });

    return loadScreenshotStats(db, snapshotId, gameRow);
  });
}

export function loadPlayHighlights(
  db: DynastyDatabase,
  snapshotId: number | null,
  gameRow: number | null = null
): PlayHighlightRecord[] {
  const rows =
    gameRow !== null
      ? db.all<PlayHighlightRow>(
          `
          SELECT * FROM play_highlight
          WHERE snapshot_id IS @snapshotId AND game_row = @gameRow
          ORDER BY sequence, id
          `,
          { snapshotId, gameRow }
        )
      : db.all<PlayHighlightRow>(
          `
          SELECT * FROM play_highlight
          WHERE snapshot_id IS @snapshotId
          ORDER BY game_row, sequence, id
          `,
          { snapshotId }
        );
  return rows.map(rowToPlayHighlight);
}

export function loadScreenshotStats(
  db: DynastyDatabase,
  snapshotId: number | null,
  gameRow: number | null = null
): ScreenshotStatRecord[] {
  const rows =
    gameRow !== null
      ? db.all<ScreenshotStatRow>(
          `
          SELECT * FROM screenshot_stat_row
          WHERE snapshot_id IS @snapshotId AND game_row = @gameRow
          ORDER BY row_type, team, player_name, id
          `,
          { snapshotId, gameRow }
        )
      : db.all<ScreenshotStatRow>(
          `
          SELECT * FROM screenshot_stat_row
          WHERE snapshot_id IS @snapshotId
          ORDER BY game_row, row_type, team, player_name, id
          `,
          { snapshotId }
        );
  return rows.map(rowToScreenshotStat);
}

export function loadAiPromptPacks(db: DynastyDatabase): AiPromptPack[] {
  return db
    .all<AiPromptPackRow>("SELECT * FROM ai_prompt_pack ORDER BY sort_order, name")
    .map((row) => ({
      id: row.id,
      name: row.name,
      description: row.description,
      personalities: parseJson<AiPromptPack["personalities"]>(
        row.personalities_json,
        []
      )
    }));
}

export function loadAiPromptTemplates(
  db: DynastyDatabase,
  dynastyId: string | null
): AiPromptTemplate[] {
  return db
    .all<AiPromptTemplateRow>(
      `
      SELECT ai_prompt_template.*, ai_prompt_pack.name AS pack_name
      FROM ai_prompt_template
      INNER JOIN ai_prompt_pack ON ai_prompt_pack.id = ai_prompt_template.pack_id
      WHERE ai_prompt_template.dynasty_id IS NULL
         OR ai_prompt_template.dynasty_id IS @dynastyId
      ORDER BY ai_prompt_pack.sort_order, ai_prompt_template.content_type,
               ai_prompt_template.mode, ai_prompt_template.is_custom,
               ai_prompt_template.id
      `,
      { dynastyId }
    )
    .map(rowToAiPromptTemplate);
}

export function loadAiPromptTemplateById(
  db: DynastyDatabase,
  id: number
): AiPromptTemplate | null {
  const row = db.get<AiPromptTemplateRow>(
    `
    SELECT ai_prompt_template.*, ai_prompt_pack.name AS pack_name
    FROM ai_prompt_template
    INNER JOIN ai_prompt_pack ON ai_prompt_pack.id = ai_prompt_template.pack_id
    WHERE ai_prompt_template.id = @id
    `,
    { id }
  );
  return row ? rowToAiPromptTemplate(row) : null;
}

export function saveAiPromptTemplate(
  db: DynastyDatabase,
  id: number,
  dynastyId: string,
  template: string
): AiPromptTemplate {
  const normalizedTemplate = template.trim();
  if (!normalizedTemplate) throw new Error("Prompt template cannot be empty.");

  return db.transaction(() => {
    const existing = loadAiPromptTemplateById(db, id);
    if (!existing) throw new Error(`Prompt template ${id} was not found.`);
    const updatedAt = new Date().toISOString();

    if (existing.isCustom) {
      if (existing.dynastyId !== dynastyId) {
        throw new Error("Prompt template belongs to another dynasty.");
      }
      db.run(
        "UPDATE ai_prompt_template SET template = @template, updated_at = @updatedAt WHERE id = @id",
        { id, template: normalizedTemplate, updatedAt }
      );
      const updated = loadAiPromptTemplateById(db, id);
      if (!updated) throw new Error(`Prompt template ${id} could not be loaded.`);
      return updated;
    }

    const priorCustom = db.get<{ id: number }>(
      `
      SELECT id FROM ai_prompt_template
      WHERE dynasty_id = @dynastyId AND source_template_id = @sourceTemplateId
      LIMIT 1
      `,
      { dynastyId, sourceTemplateId: id }
    );
    if (priorCustom) {
      db.run(
        "UPDATE ai_prompt_template SET template = @template, updated_at = @updatedAt WHERE id = @id",
        { id: priorCustom.id, template: normalizedTemplate, updatedAt }
      );
      const updated = loadAiPromptTemplateById(db, priorCustom.id);
      if (!updated) {
        throw new Error(`Custom prompt template ${priorCustom.id} could not be loaded.`);
      }
      return updated;
    }

    db.run(
      `
      INSERT INTO ai_prompt_template (
        dynasty_id, pack_id, content_type, mode, name, template,
        is_custom, source_template_id, created_at, updated_at
      )
      VALUES (
        @dynastyId, 'custom', @contentType, @mode, @name, @template,
        1, @sourceTemplateId, @updatedAt, @updatedAt
      )
      `,
      {
        dynastyId,
        contentType: existing.contentType,
        mode: existing.mode,
        name: `${existing.packName}: ${existing.name}`,
        template: normalizedTemplate,
        sourceTemplateId: id,
        updatedAt
      }
    );
    const created = loadAiPromptTemplateById(db, db.lastInsertRowid());
    if (!created) throw new Error("Custom prompt template could not be loaded.");
    return created;
  });
}

export function resolveAiPromptTemplate(
  db: DynastyDatabase,
  value: {
    dynastyId: string;
    packId: string;
    contentType: AiContentType;
    mode: AiGenerationMode;
    requestedId?: number | null;
  }
): AiPromptTemplate {
  if (value.requestedId) {
    const requested = loadAiPromptTemplateById(db, value.requestedId);
    if (
      !requested ||
      (requested.dynastyId !== null && requested.dynastyId !== value.dynastyId) ||
      requested.contentType !== value.contentType ||
      requested.mode !== value.mode
    ) {
      throw new Error("Selected prompt template does not match this generation.");
    }
    return requested;
  }

  const custom = db.get<AiPromptTemplateRow>(
    `
    SELECT custom.*, custom_pack.name AS pack_name
    FROM ai_prompt_template AS custom
    INNER JOIN ai_prompt_pack AS custom_pack ON custom_pack.id = custom.pack_id
    INNER JOIN ai_prompt_template AS source ON source.id = custom.source_template_id
    WHERE custom.dynasty_id = @dynastyId
      AND source.pack_id = @packId
      AND custom.content_type = @contentType
      AND custom.mode = @mode
    ORDER BY custom.updated_at DESC, custom.id DESC
    LIMIT 1
    `,
    value
  );
  if (custom) return rowToAiPromptTemplate(custom);

  const seeded = db.get<AiPromptTemplateRow>(
    `
    SELECT ai_prompt_template.*, ai_prompt_pack.name AS pack_name
    FROM ai_prompt_template
    INNER JOIN ai_prompt_pack ON ai_prompt_pack.id = ai_prompt_template.pack_id
    WHERE ai_prompt_template.dynasty_id IS NULL
      AND ai_prompt_template.pack_id = @packId
      AND ai_prompt_template.content_type = @contentType
      AND ai_prompt_template.mode = @mode
    LIMIT 1
    `,
    value
  );
  if (!seeded) {
    throw new Error(
      `No ${value.mode} ${value.contentType} prompt exists in pack ${value.packId}.`
    );
  }
  return rowToAiPromptTemplate(seeded);
}

export function saveAiGeneratedArtifact(
  db: DynastyDatabase,
  value: Omit<AiGeneratedArtifact, "id" | "createdAt" | "updatedAt">
): AiGeneratedArtifact {
  return db.transaction(() => {
    const now = new Date().toISOString();
    db.run(
      `
      INSERT INTO ai_generated_artifact (
        dynasty_id, snapshot_id, game_row, content_type, mode,
        prompt_template_id, provider_base_url, model, rendered_prompt,
        evidence_json, content_json, created_at, updated_at
      )
      VALUES (
        @dynastyId, @snapshotId, @gameRow, @contentType, @mode,
        @promptTemplateId, @providerBaseUrl, @model, @renderedPrompt,
        @evidenceJson, @contentJson, @now, @now
      )
      `,
      {
        dynastyId: value.dynastyId,
        snapshotId: value.snapshotId,
        gameRow: value.gameRow,
        contentType: value.contentType,
        mode: value.mode,
        promptTemplateId: value.promptTemplateId,
        providerBaseUrl: value.providerBaseUrl,
        model: value.model,
        renderedPrompt: value.renderedPrompt,
        evidenceJson: stringifyJson(value.evidence),
        contentJson: stringifyJson(value.content),
        now
      }
    );
    const artifact = loadAiGeneratedArtifactById(db, db.lastInsertRowid());
    if (!artifact) throw new Error("Generated artifact could not be loaded.");
    return artifact;
  });
}

export function loadAiGeneratedArtifacts(
  db: DynastyDatabase,
  snapshotId: number,
  gameRow: number
): AiGeneratedArtifact[] {
  return db
    .all<AiGeneratedArtifactRow>(
      `
      SELECT * FROM ai_generated_artifact
      WHERE snapshot_id = @snapshotId AND game_row = @gameRow
      ORDER BY created_at DESC, id DESC
      `,
      { snapshotId, gameRow }
    )
    .map(rowToAiGeneratedArtifact);
}

export function updateAiGeneratedArtifact(
  db: DynastyDatabase,
  id: number,
  content: Record<string, unknown>
): AiGeneratedArtifact {
  return db.transaction(() => {
    db.run(
      `
      UPDATE ai_generated_artifact
      SET content_json = @contentJson, updated_at = @updatedAt
      WHERE id = @id
      `,
      {
        id,
        contentJson: stringifyJson(content),
        updatedAt: new Date().toISOString()
      }
    );
    const artifact = loadAiGeneratedArtifactById(db, id);
    if (!artifact) throw new Error(`Generated artifact ${id} was not found.`);
    return artifact;
  });
}

export function loadAiGeneratedArtifactById(
  db: DynastyDatabase,
  id: number
): AiGeneratedArtifact | null {
  const row = db.get<AiGeneratedArtifactRow>(
    "SELECT * FROM ai_generated_artifact WHERE id = @id",
    { id }
  );
  return row ? rowToAiGeneratedArtifact(row) : null;
}

export function getDatabaseCounts(db: DynastyDatabase): Record<string, number> {
  const tables = [
    "dynasty",
    "snapshot",
    "conference",
    "team",
    "coach",
    "player",
    "game",
    "game_team_stats",
    "game_player_stats",
    "screenshot",
    "screenshot_extraction",
    "play_highlight",
    "screenshot_stat_row",
    "ai_prompt_pack",
    "ai_prompt_template",
    "ai_generated_artifact"
  ];
  return Object.fromEntries(
    tables.map((table) => {
      const row = db.get<{ count: number }>(`SELECT COUNT(*) AS count FROM ${table}`);
      return [table, Number(row?.count ?? 0)];
    })
  );
}

function migrate(db: DynastyDatabase): void {
  db.exec(`
    PRAGMA foreign_keys = ON;

    CREATE TABLE IF NOT EXISTS dynasty (
      id TEXT PRIMARY KEY,
      league_id TEXT,
      display_name TEXT NOT NULL,
      game_version TEXT,
      user_team_row INTEGER,
      user_coach_row INTEGER,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS snapshot (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      dynasty_id TEXT NOT NULL REFERENCES dynasty(id) ON DELETE CASCADE,
      source_file_name TEXT NOT NULL,
      source_hash TEXT NOT NULL UNIQUE,
      archive_path TEXT NOT NULL,
      imported_at TEXT NOT NULL,
      opened_at TEXT NOT NULL,
      season_year INTEGER,
      dynasty_year INTEGER,
      week INTEGER,
      week_type TEXT,
      stage TEXT,
      offseason_stage INTEGER,
      offseason_advances INTEGER,
      schema_game_year INTEGER,
      schema_game_type TEXT,
      schema_major INTEGER,
      schema_minor INTEGER,
      table_count INTEGER NOT NULL,
      user_team_row INTEGER,
      user_coach_row INTEGER,
      validation_json TEXT NOT NULL,
      diagnostics_json TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS conference (
      snapshot_id INTEGER NOT NULL REFERENCES snapshot(id) ON DELETE CASCADE,
      game_row INTEGER NOT NULL,
      name TEXT NOT NULL,
      enum_name TEXT,
      team_rows_json TEXT NOT NULL,
      source_json TEXT NOT NULL,
      PRIMARY KEY (snapshot_id, game_row)
    );

    CREATE TABLE IF NOT EXISTS team (
      snapshot_id INTEGER NOT NULL REFERENCES snapshot(id) ON DELETE CASCADE,
      game_row INTEGER NOT NULL,
      team_index INTEGER,
      display_name TEXT NOT NULL,
      short_name TEXT,
      nick_name TEXT,
      conference_row INTEGER,
      conference_name TEXT,
      rank INTEGER,
      media_rank INTEGER,
      coaches_rank INTEGER,
      cfp_rank INTEGER,
      prestige INTEGER,
      prestige_rank INTEGER,
      colors_json TEXT NOT NULL,
      rival_team_rows_json TEXT NOT NULL,
      source_json TEXT NOT NULL,
      PRIMARY KEY (snapshot_id, game_row)
    );

    CREATE TABLE IF NOT EXISTS coach (
      snapshot_id INTEGER NOT NULL REFERENCES snapshot(id) ON DELETE CASCADE,
      game_row INTEGER NOT NULL,
      name TEXT,
      first_name TEXT,
      last_name TEXT,
      role TEXT,
      team_index INTEGER,
      team_row INTEGER,
      level INTEGER,
      prestige TEXT,
      prestige_score INTEGER,
      contract_status TEXT,
      job_security TEXT,
      is_user_controlled INTEGER NOT NULL,
      source_json TEXT NOT NULL,
      PRIMARY KEY (snapshot_id, game_row)
    );

    CREATE TABLE IF NOT EXISTS player (
      snapshot_id INTEGER NOT NULL REFERENCES snapshot(id) ON DELETE CASCADE,
      game_row INTEGER NOT NULL,
      first_name TEXT,
      last_name TEXT,
      display_name TEXT NOT NULL,
      position TEXT,
      jersey_number INTEGER,
      school_year TEXT,
      overall INTEGER,
      team_index INTEGER,
      team_row INTEGER,
      roster_team_row INTEGER,
      source_json TEXT NOT NULL,
      PRIMARY KEY (snapshot_id, game_row)
    );

    CREATE TABLE IF NOT EXISTS game (
      snapshot_id INTEGER NOT NULL REFERENCES snapshot(id) ON DELETE CASCADE,
      game_row INTEGER NOT NULL,
      season_year INTEGER,
      week INTEGER,
      week_type TEXT,
      game_number INTEGER,
      status TEXT,
      home_team_row INTEGER,
      away_team_row INTEGER,
      home_score INTEGER,
      away_score INTEGER,
      quarter_scores_json TEXT NOT NULL,
      is_simmed INTEGER,
      is_game_of_week INTEGER,
      is_major_game INTEGER NOT NULL,
      major_game_reasons_json TEXT NOT NULL,
      attendance INTEGER,
      weather TEXT,
      source_json TEXT NOT NULL,
      PRIMARY KEY (snapshot_id, game_row)
    );

    CREATE TABLE IF NOT EXISTS game_team_stats (
      snapshot_id INTEGER NOT NULL REFERENCES snapshot(id) ON DELETE CASCADE,
      game_row INTEGER NOT NULL,
      team_row INTEGER,
      side TEXT NOT NULL CHECK (side IN ('home', 'away')),
      stats_json TEXT NOT NULL,
      source_json TEXT NOT NULL,
      PRIMARY KEY (snapshot_id, game_row, side)
    );

    CREATE TABLE IF NOT EXISTS game_player_stats (
      snapshot_id INTEGER NOT NULL REFERENCES snapshot(id) ON DELETE CASCADE,
      game_row INTEGER NOT NULL,
      player_row INTEGER NOT NULL,
      player_name TEXT NOT NULL,
      team_row INTEGER,
      opponent_team_row INTEGER,
      stat_group TEXT NOT NULL,
      stats_json TEXT NOT NULL,
      source_json TEXT NOT NULL,
      PRIMARY KEY (snapshot_id, game_row, player_row, stat_group, source_json)
    );

    CREATE TABLE IF NOT EXISTS screenshot (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      dynasty_id TEXT REFERENCES dynasty(id) ON DELETE SET NULL,
      snapshot_id INTEGER REFERENCES snapshot(id) ON DELETE SET NULL,
      original_file_name TEXT NOT NULL,
      stored_path TEXT NOT NULL,
      file_hash TEXT NOT NULL UNIQUE,
      screenshot_type TEXT NOT NULL,
      imported_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS screenshot_extraction (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      screenshot_id INTEGER NOT NULL REFERENCES screenshot(id) ON DELETE CASCADE,
      screenshot_type TEXT NOT NULL,
      status TEXT NOT NULL,
      template_version INTEGER NOT NULL,
      template_json TEXT NOT NULL,
      extracted_json TEXT NOT NULL,
      error_message TEXT,
      created_at TEXT NOT NULL,
      completed_at TEXT
    );

    CREATE TABLE IF NOT EXISTS play_highlight (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      dynasty_id TEXT REFERENCES dynasty(id) ON DELETE SET NULL,
      snapshot_id INTEGER REFERENCES snapshot(id) ON DELETE CASCADE,
      game_row INTEGER NOT NULL,
      screenshot_id INTEGER NOT NULL REFERENCES screenshot(id) ON DELETE CASCADE,
      extraction_id INTEGER NOT NULL REFERENCES screenshot_extraction(id) ON DELETE CASCADE,
      sequence INTEGER NOT NULL,
      period TEXT,
      clock TEXT,
      team TEXT,
      summary TEXT NOT NULL,
      score TEXT,
      raw_text TEXT NOT NULL,
      dedupe_key TEXT NOT NULL UNIQUE,
      approved_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS screenshot_stat_row (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      dynasty_id TEXT REFERENCES dynasty(id) ON DELETE SET NULL,
      snapshot_id INTEGER REFERENCES snapshot(id) ON DELETE CASCADE,
      game_row INTEGER NOT NULL,
      screenshot_id INTEGER NOT NULL REFERENCES screenshot(id) ON DELETE CASCADE,
      extraction_id INTEGER NOT NULL REFERENCES screenshot_extraction(id) ON DELETE CASCADE,
      row_type TEXT NOT NULL CHECK (row_type IN ('passing', 'rushing', 'receiving')),
      player_name TEXT NOT NULL,
      team TEXT,
      raw_row_json TEXT NOT NULL,
      corrected_row_json TEXT NOT NULL,
      dedupe_key TEXT NOT NULL UNIQUE,
      approved_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS ai_prompt_pack (
      id TEXT PRIMARY KEY,
      name TEXT NOT NULL,
      description TEXT NOT NULL,
      personalities_json TEXT NOT NULL,
      sort_order INTEGER NOT NULL
    );

    CREATE TABLE IF NOT EXISTS ai_prompt_template (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      dynasty_id TEXT REFERENCES dynasty(id) ON DELETE CASCADE,
      pack_id TEXT NOT NULL REFERENCES ai_prompt_pack(id),
      content_type TEXT NOT NULL,
      mode TEXT NOT NULL CHECK (mode IN ('save_only', 'screenshot_enriched')),
      name TEXT NOT NULL,
      template TEXT NOT NULL,
      is_custom INTEGER NOT NULL,
      source_template_id INTEGER REFERENCES ai_prompt_template(id) ON DELETE SET NULL,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS ai_generated_artifact (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      dynasty_id TEXT NOT NULL REFERENCES dynasty(id) ON DELETE CASCADE,
      snapshot_id INTEGER NOT NULL REFERENCES snapshot(id) ON DELETE CASCADE,
      game_row INTEGER NOT NULL,
      content_type TEXT NOT NULL,
      mode TEXT NOT NULL CHECK (mode IN ('save_only', 'screenshot_enriched')),
      prompt_template_id INTEGER NOT NULL REFERENCES ai_prompt_template(id),
      provider_base_url TEXT NOT NULL,
      model TEXT NOT NULL,
      rendered_prompt TEXT NOT NULL,
      evidence_json TEXT NOT NULL,
      content_json TEXT NOT NULL,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );

    CREATE INDEX IF NOT EXISTS idx_snapshot_dynasty_latest ON snapshot(dynasty_id, imported_at DESC, id DESC);
    CREATE INDEX IF NOT EXISTS idx_player_snapshot_team ON player(snapshot_id, team_row);
    CREATE INDEX IF NOT EXISTS idx_game_snapshot_week ON game(snapshot_id, season_year, week, game_number);
    CREATE INDEX IF NOT EXISTS idx_game_player_stats_game ON game_player_stats(snapshot_id, game_row);
    CREATE INDEX IF NOT EXISTS idx_screenshot_dynasty_latest ON screenshot(dynasty_id, imported_at DESC, id DESC);
    CREATE INDEX IF NOT EXISTS idx_screenshot_extraction_latest ON screenshot_extraction(screenshot_id, created_at DESC, id DESC);
    CREATE INDEX IF NOT EXISTS idx_play_highlight_game ON play_highlight(snapshot_id, game_row, sequence);
    CREATE INDEX IF NOT EXISTS idx_screenshot_stat_game ON screenshot_stat_row(snapshot_id, game_row, row_type);
    CREATE UNIQUE INDEX IF NOT EXISTS idx_ai_prompt_custom_source ON ai_prompt_template(dynasty_id, source_template_id) WHERE dynasty_id IS NOT NULL AND source_template_id IS NOT NULL;
    CREATE INDEX IF NOT EXISTS idx_ai_prompt_lookup ON ai_prompt_template(pack_id, content_type, mode, dynasty_id);
    CREATE INDEX IF NOT EXISTS idx_ai_artifact_game ON ai_generated_artifact(snapshot_id, game_row, created_at DESC);
  `);
}

function seedAiDefaults(db: DynastyDatabase): void {
  db.transaction(() => {
    const packs = [
      ...AI_PROMPT_PACKS,
      {
        id: "custom",
        name: "Custom",
        description: "Dynasty-specific copies of edited default prompts.",
        personalities: []
      }
    ];
    for (const [sortOrder, pack] of packs.entries()) {
      db.run(
        `
        INSERT INTO ai_prompt_pack (
          id, name, description, personalities_json, sort_order
        )
        VALUES (@id, @name, @description, @personalitiesJson, @sortOrder)
        ON CONFLICT(id) DO UPDATE SET
          name = excluded.name,
          description = excluded.description,
          personalities_json = excluded.personalities_json,
          sort_order = excluded.sort_order
        `,
        {
          id: pack.id,
          name: pack.name,
          description: pack.description,
          personalitiesJson: stringifyJson(pack.personalities),
          sortOrder
        }
      );
    }

    const now = new Date().toISOString();
    for (const seed of AI_PROMPT_TEMPLATE_SEEDS) {
      const existing = db.get<{ id: number }>(
        `
        SELECT id FROM ai_prompt_template
        WHERE dynasty_id IS NULL
          AND pack_id = @packId
          AND content_type = @contentType
          AND mode = @mode
        LIMIT 1
        `,
        seed
      );
      if (existing) continue;
      db.run(
        `
        INSERT INTO ai_prompt_template (
          dynasty_id, pack_id, content_type, mode, name, template,
          is_custom, source_template_id, created_at, updated_at
        )
        VALUES (
          NULL, @packId, @contentType, @mode, @name, @template,
          0, NULL, @now, @now
        )
        `,
        { ...seed, now }
      );
    }
  });
}

function insertConferences(
  db: DynastyDatabase,
  snapshotId: number,
  values: ConferenceSummary[]
): void {
  const statement = db.prepare(`
    INSERT INTO conference (snapshot_id, game_row, name, enum_name, team_rows_json, source_json)
    VALUES (@snapshotId, @row, @name, @enumName, @teamRowsJson, @sourceJson)
  `);
  try {
    for (const value of values) {
      statement.run(
        normalizeParams({
          snapshotId,
          row: value.row,
          name: value.name,
          enumName: value.enumName,
          teamRowsJson: stringifyJson(value.teamRows),
          sourceJson: stringifyJson(value.source)
        })
      );
    }
  } finally {
    statement.free();
  }
}

function insertTeams(
  db: DynastyDatabase,
  snapshotId: number,
  values: TeamSummary[]
): void {
  const statement = db.prepare(`
    INSERT INTO team (
      snapshot_id, game_row, team_index, display_name, short_name, nick_name,
      conference_row, conference_name, rank, media_rank, coaches_rank, cfp_rank,
      prestige, prestige_rank, colors_json, rival_team_rows_json, source_json
    )
    VALUES (
      @snapshotId, @row, @teamIndex, @displayName, @shortName, @nickName,
      @conferenceRow, @conferenceName, @rank, @mediaRank, @coachesRank, @cfpRank,
      @prestige, @prestigeRank, @colorsJson, @rivalTeamRowsJson, @sourceJson
    )
  `);
  try {
    for (const value of values) {
      statement.run(
        normalizeParams({
          snapshotId,
          row: value.row,
          teamIndex: value.teamIndex,
          displayName: value.displayName,
          shortName: value.shortName,
          nickName: value.nickName,
          conferenceRow: value.conferenceRow,
          conferenceName: value.conferenceName,
          rank: value.rank,
          mediaRank: value.mediaRank,
          coachesRank: value.coachesRank,
          cfpRank: value.cfpRank,
          prestige: value.prestige,
          prestigeRank: value.prestigeRank,
          colorsJson: stringifyJson(value.colors),
          rivalTeamRowsJson: stringifyJson(value.rivalTeamRows),
          sourceJson: stringifyJson(value.source)
        })
      );
    }
  } finally {
    statement.free();
  }
}

function insertCoaches(
  db: DynastyDatabase,
  snapshotId: number,
  values: CoachSummary[]
): void {
  const statement = db.prepare(`
    INSERT INTO coach (
      snapshot_id, game_row, name, first_name, last_name, role, team_index, team_row,
      level, prestige, prestige_score, contract_status, job_security, is_user_controlled, source_json
    )
    VALUES (
      @snapshotId, @row, @name, @firstName, @lastName, @role, @teamIndex, @teamRow,
      @level, @prestige, @prestigeScore, @contractStatus, @jobSecurity, @isUserControlled, @sourceJson
    )
  `);
  try {
    for (const value of values) {
      statement.run(
        normalizeParams({
          snapshotId,
          row: value.row,
          name: value.name,
          firstName: value.firstName,
          lastName: value.lastName,
          role: value.role,
          teamIndex: value.teamIndex,
          teamRow: value.teamRow,
          level: value.level,
          prestige: value.prestige,
          prestigeScore: value.prestigeScore,
          contractStatus: value.contractStatus,
          jobSecurity: value.jobSecurity,
          isUserControlled: value.isUserControlled ? 1 : 0,
          sourceJson: stringifyJson(value.source)
        })
      );
    }
  } finally {
    statement.free();
  }
}

function insertPlayers(
  db: DynastyDatabase,
  snapshotId: number,
  values: PlayerSummary[]
): void {
  const statement = db.prepare(`
    INSERT INTO player (
      snapshot_id, game_row, first_name, last_name, display_name, position, jersey_number,
      school_year, overall, team_index, team_row, roster_team_row, source_json
    )
    VALUES (
      @snapshotId, @row, @firstName, @lastName, @displayName, @position, @jerseyNumber,
      @schoolYear, @overall, @teamIndex, @teamRow, @rosterTeamRow, @sourceJson
    )
  `);
  try {
    for (const value of values) {
      statement.run(
        normalizeParams({
          snapshotId,
          row: value.row,
          firstName: value.firstName,
          lastName: value.lastName,
          displayName: value.displayName,
          position: value.position,
          jerseyNumber: value.jerseyNumber,
          schoolYear: value.schoolYear,
          overall: value.overall,
          teamIndex: value.teamIndex,
          teamRow: value.teamRow,
          rosterTeamRow: value.rosterTeamRow,
          sourceJson: stringifyJson(value.source)
        })
      );
    }
  } finally {
    statement.free();
  }
}

function insertGames(
  db: DynastyDatabase,
  snapshotId: number,
  values: GameSummary[]
): void {
  const statement = db.prepare(`
    INSERT INTO game (
      snapshot_id, game_row, season_year, week, week_type, game_number, status,
      home_team_row, away_team_row, home_score, away_score, quarter_scores_json,
      is_simmed, is_game_of_week, is_major_game, major_game_reasons_json,
      attendance, weather, source_json
    )
    VALUES (
      @snapshotId, @row, @seasonYear, @week, @weekType, @gameNumber, @status,
      @homeTeamRow, @awayTeamRow, @homeScore, @awayScore, @quarterScoresJson,
      @isSimmed, @isGameOfWeek, @isMajorGame, @majorGameReasonsJson,
      @attendance, @weather, @sourceJson
    )
  `);
  try {
    for (const value of values) {
      statement.run(
        normalizeParams({
          snapshotId,
          row: value.row,
          seasonYear: value.seasonYear,
          week: value.week,
          weekType: value.weekType,
          gameNumber: value.gameNumber,
          status: value.status,
          homeTeamRow: value.homeTeam?.row ?? null,
          awayTeamRow: value.awayTeam?.row ?? null,
          homeScore: value.homeScore,
          awayScore: value.awayScore,
          quarterScoresJson: stringifyJson(value.quarterScores),
          isSimmed: nullableBoolToInt(value.isSimmed),
          isGameOfWeek: nullableBoolToInt(value.isGameOfTheWeek),
          isMajorGame: value.isMajorGame ? 1 : 0,
          majorGameReasonsJson: stringifyJson(value.majorGameReasons),
          attendance: value.attendance,
          weather: value.weather,
          sourceJson: stringifyJson(value.source)
        })
      );
    }
  } finally {
    statement.free();
  }
}

function insertTeamGameStats(
  db: DynastyDatabase,
  snapshotId: number,
  values: GameTeamStatsSummary[]
): void {
  const statement = db.prepare(`
    INSERT INTO game_team_stats (snapshot_id, game_row, team_row, side, stats_json, source_json)
    VALUES (@snapshotId, @gameRow, @teamRow, @side, @statsJson, @sourceJson)
  `);
  try {
    for (const value of values) {
      statement.run(
        normalizeParams({
          snapshotId,
          gameRow: value.gameRow,
          teamRow: value.teamRow,
          side: value.side,
          statsJson: stringifyJson(value.stats),
          sourceJson: stringifyJson(value.source)
        })
      );
    }
  } finally {
    statement.free();
  }
}

function insertPlayerGameStats(
  db: DynastyDatabase,
  snapshotId: number,
  values: GamePlayerStatsSummary[]
): void {
  const statement = db.prepare(`
    INSERT INTO game_player_stats (
      snapshot_id, game_row, player_row, player_name, team_row, opponent_team_row,
      stat_group, stats_json, source_json
    )
    VALUES (
      @snapshotId, @gameRow, @playerRow, @playerName, @teamRow, @opponentTeamRow,
      @statGroup, @statsJson, @sourceJson
    )
  `);
  try {
    for (const value of values) {
      statement.run(
        normalizeParams({
          snapshotId,
          gameRow: value.gameRow,
          playerRow: value.playerRow,
          playerName: value.playerName,
          teamRow: value.teamRow,
          opponentTeamRow: value.opponentTeamRow,
          statGroup: value.statGroup,
          statsJson: stringifyJson(value.stats),
          sourceJson: stringifyJson(value.source)
        })
      );
    }
  } finally {
    statement.free();
  }
}

function rowToConference(row: ConferenceRow): ConferenceSummary {
  return {
    row: row.game_row,
    name: row.name,
    enumName: row.enum_name,
    teamRows: parseJson<number[]>(row.team_rows_json, []),
    source: parseJson<SourcePointer>(row.source_json, emptySource())
  };
}

function rowToTeam(row: TeamRow): TeamSummary {
  return {
    row: row.game_row,
    teamIndex: row.team_index,
    displayName: row.display_name,
    shortName: row.short_name,
    nickName: row.nick_name,
    conferenceRow: row.conference_row,
    conferenceName: row.conference_name,
    rank: row.rank,
    mediaRank: row.media_rank,
    coachesRank: row.coaches_rank,
    cfpRank: row.cfp_rank,
    prestige: row.prestige,
    prestigeRank: row.prestige_rank,
    colors: parseJson<TeamSummary["colors"]>(row.colors_json, {
      primary: null,
      secondary: null
    }),
    rivalTeamRows: parseJson<number[]>(row.rival_team_rows_json, []),
    source: parseJson<SourcePointer>(row.source_json, emptySource())
  };
}

function rowToCoach(row: CoachRow): CoachSummary {
  return {
    row: row.game_row,
    name: row.name,
    firstName: row.first_name,
    lastName: row.last_name,
    role: row.role,
    teamIndex: row.team_index,
    teamRow: row.team_row,
    level: row.level,
    prestige: row.prestige,
    prestigeScore: row.prestige_score,
    contractStatus: row.contract_status,
    jobSecurity: row.job_security,
    isUserControlled: row.is_user_controlled === 1,
    source: parseJson<SourcePointer>(row.source_json, emptySource())
  };
}

function rowToPlayer(row: PlayerRow): PlayerSummary {
  return {
    row: row.game_row,
    firstName: row.first_name,
    lastName: row.last_name,
    displayName: row.display_name,
    position: row.position,
    jerseyNumber: row.jersey_number,
    schoolYear: row.school_year,
    overall: row.overall,
    teamIndex: row.team_index,
    teamRow: row.team_row,
    rosterTeamRow: row.roster_team_row,
    source: parseJson<SourcePointer>(row.source_json, emptySource())
  };
}

function rowToGame(row: GameRow, teamsByRow: Map<number, TeamSummary>): GameSummary {
  return {
    row: row.game_row,
    seasonYear: row.season_year,
    week: row.week,
    weekType: row.week_type,
    gameNumber: row.game_number,
    status: row.status,
    homeTeam: row.home_team_row !== null ? teamsByRow.get(row.home_team_row) ?? null : null,
    awayTeam: row.away_team_row !== null ? teamsByRow.get(row.away_team_row) ?? null : null,
    homeScore: row.home_score,
    awayScore: row.away_score,
    quarterScores: parseJson<GameSummary["quarterScores"]>(row.quarter_scores_json, {
      home: [null, null, null, null],
      away: [null, null, null, null],
      homeOvertime: null,
      awayOvertime: null
    }),
    isSimmed: nullableIntToBool(row.is_simmed),
    isGameOfTheWeek: nullableIntToBool(row.is_game_of_week),
    isMajorGame: row.is_major_game === 1,
    majorGameReasons: parseJson<string[]>(row.major_game_reasons_json, []),
    attendance: row.attendance,
    weather: row.weather,
    source: parseJson<SourcePointer>(row.source_json, emptySource())
  };
}

function rowToGameTeamStats(row: GameTeamStatsRow): GameTeamStatsSummary {
  return {
    gameRow: row.game_row,
    teamRow: row.team_row,
    side: row.side,
    stats: parseJson<StatPayload>(row.stats_json, {}),
    source: parseJson<SourcePointer>(row.source_json, emptySource())
  };
}

function rowToGamePlayerStats(row: GamePlayerStatsRow): GamePlayerStatsSummary {
  return {
    gameRow: row.game_row,
    playerRow: row.player_row,
    playerName: row.player_name,
    teamRow: row.team_row,
    opponentTeamRow: row.opponent_team_row,
    statGroup: row.stat_group,
    stats: parseJson<StatPayload>(row.stats_json, {}),
    source: parseJson<SourcePointer>(row.source_json, emptySource())
  };
}

function rowToScreenshot(row: ScreenshotRow): ScreenshotRecord {
  return {
    id: row.id,
    dynastyId: row.dynasty_id,
    snapshotId: row.snapshot_id,
    originalFileName: row.original_file_name,
    storedPath: row.stored_path,
    fileHash: row.file_hash,
    screenshotType: normalizeScreenshotType(row.screenshot_type),
    importedAt: row.imported_at
  };
}

function rowToScreenshotExtraction(
  row: ScreenshotExtractionRow
): ScreenshotExtractionRecord {
  return {
    id: row.id,
    screenshotId: row.screenshot_id,
    screenshotType: normalizeScreenshotType(row.screenshot_type),
    status: normalizeExtractionStatus(row.status),
    templateVersion: row.template_version,
    templateJson: parseJson<Record<string, unknown>>(row.template_json, {}),
    extractedJson: parseJson<ScreenshotExtractionPayload>(row.extracted_json, {}),
    errorMessage: row.error_message,
    createdAt: row.created_at,
    completedAt: row.completed_at
  };
}

function rowToPlayHighlight(row: PlayHighlightRow): PlayHighlightRecord {
  return {
    id: row.id,
    dynastyId: row.dynasty_id,
    snapshotId: row.snapshot_id,
    gameRow: row.game_row,
    screenshotId: row.screenshot_id,
    extractionId: row.extraction_id,
    sequence: row.sequence,
    period: row.period,
    clock: row.clock,
    team: row.team,
    summary: row.summary,
    score: row.score,
    rawText: row.raw_text,
    approvedAt: row.approved_at
  };
}

function rowToScreenshotStat(row: ScreenshotStatRow): ScreenshotStatRecord {
  const rowType =
    row.row_type === "rushing" || row.row_type === "receiving"
      ? row.row_type
      : "passing";
  return {
    id: row.id,
    dynastyId: row.dynasty_id,
    snapshotId: row.snapshot_id,
    gameRow: row.game_row,
    screenshotId: row.screenshot_id,
    extractionId: row.extraction_id,
    rowType,
    playerName: row.player_name,
    team: row.team,
    rawRow: parseJson<ScreenshotParsedRow>(row.raw_row_json, {}),
    correctedRow: parseJson<ScreenshotParsedRow>(row.corrected_row_json, {}),
    approvedAt: row.approved_at
  };
}

function rowToAiPromptTemplate(row: AiPromptTemplateRow): AiPromptTemplate {
  return {
    id: row.id,
    dynastyId: row.dynasty_id,
    packId: row.pack_id,
    packName: row.pack_name,
    contentType: normalizeAiContentType(row.content_type),
    mode: normalizeAiGenerationMode(row.mode),
    name: row.name,
    template: row.template,
    isCustom: row.is_custom === 1,
    sourceTemplateId: row.source_template_id,
    updatedAt: row.updated_at
  };
}

function rowToAiGeneratedArtifact(
  row: AiGeneratedArtifactRow
): AiGeneratedArtifact {
  return {
    id: row.id,
    dynastyId: row.dynasty_id,
    snapshotId: row.snapshot_id,
    gameRow: row.game_row,
    contentType: normalizeAiContentType(row.content_type),
    mode: normalizeAiGenerationMode(row.mode),
    promptTemplateId: row.prompt_template_id,
    providerBaseUrl: row.provider_base_url,
    model: row.model,
    renderedPrompt: row.rendered_prompt,
    evidence: parseJson<Record<string, unknown>>(row.evidence_json, {}),
    content: parseJson<Record<string, unknown>>(row.content_json, {}),
    createdAt: row.created_at,
    updatedAt: row.updated_at
  };
}

function normalizeAiContentType(value: string): AiContentType {
  switch (value) {
    case "recap":
    case "headline":
    case "social":
    case "grade_explanation":
      return value;
    default:
      return "article";
  }
}

function normalizeAiGenerationMode(value: string): AiGenerationMode {
  return value === "screenshot_enriched"
    ? "screenshot_enriched"
    : "save_only";
}

function saveExtractionApproval(
  db: DynastyDatabase,
  extractionId: number,
  payload: ScreenshotExtractionPayload,
  approval: NonNullable<ScreenshotExtractionPayload["approval"]>
): void {
  db.run(
    "UPDATE screenshot_extraction SET extracted_json = @extractedJson WHERE id = @extractionId",
    {
      extractionId,
      extractedJson: stringifyJson({
        ...payload,
        approval
      })
    }
  );
}

function isScreenshotStatRow(row: ScreenshotParsedRow): boolean {
  return (
    row.rowType === "passing" ||
    row.rowType === "rushing" ||
    row.rowType === "receiving"
  );
}

function rowString(row: ScreenshotParsedRow, key: string): string | null {
  const value = row[key];
  if (typeof value === "string") return value.trim() || null;
  if (typeof value === "number" && Number.isFinite(value)) return String(value);
  return null;
}

function rowNumber(row: ScreenshotParsedRow, key: string): number | null {
  const value = row[key];
  if (typeof value === "number" && Number.isFinite(value)) return value;
  if (typeof value === "string" && value.trim() && Number.isFinite(Number(value))) {
    return Number(value);
  }
  return null;
}

function screenshotStatDedupeKey(value: {
  snapshotId: number | null;
  gameRow: number;
  rowType: string;
  playerName: string;
  team: string | null;
}): string {
  return [
    value.snapshotId ?? "none",
    value.gameRow,
    value.rowType.toLowerCase(),
    normalizeHighlightSummary(value.team ?? ""),
    normalizeHighlightSummary(value.playerName)
  ].join("|");
}

function highlightDedupeKey(value: {
  snapshotId: number | null;
  gameRow: number;
  period: string | null;
  clock: string | null;
  score: string | null;
  summary: string;
}): string {
  return [
    value.snapshotId ?? "none",
    value.gameRow,
    value.period ?? "",
    value.clock ?? "",
    value.score ?? "",
    normalizeHighlightSummary(value.summary)
  ].join("|");
}

function normalizeHighlightSummary(value: string): string {
  return value.toLowerCase().replace(/[^a-z0-9]+/g, " ").trim();
}

function normalizeScreenshotType(value: string): ScreenshotType {
  switch (value) {
    case "highlight_list":
    case "passing_stats":
    case "rushing_stats":
    case "receiving_stats":
      return value;
    default:
      return "unknown";
  }
}

function normalizeExtractionStatus(value: string): ScreenshotExtractionStatus {
  switch (value) {
    case "completed":
    case "failed":
    case "pending":
      return value;
    default:
      return "pending";
  }
}

async function getSqlModule(): Promise<SqlJsStatic> {
  sqlModulePromise ??= initSqlJs({
    locateFile: (fileName: string) => {
      const distRoot = dirname(require.resolve("sql.js"));
      return join(distRoot, fileName);
    }
  });
  return sqlModulePromise;
}

function normalizeParams(params: ParamsObject): ParamsObject {
  const normalized: ParamsObject = {};
  for (const [key, value] of Object.entries(params)) {
    const sqlValue = normalizeValue(value);
    normalized[key] = sqlValue;
    if (!key.startsWith("@")) normalized[`@${key}`] = sqlValue;
    if (!key.startsWith(":")) normalized[`:${key}`] = sqlValue;
    if (!key.startsWith("$")) normalized[`$${key}`] = sqlValue;
  }
  return normalized;
}

function normalizeValue(value: unknown): SqlValue {
  if (value === null || value === undefined) return null;
  if (typeof value === "boolean") return value ? 1 : 0;
  if (typeof value === "number" || typeof value === "string" || value instanceof Uint8Array) {
    return value;
  }
  return String(value);
}

function stringifyJson(value: unknown): string {
  return JSON.stringify(value);
}

function parseJson<T>(value: string | null, fallback: T): T {
  if (!value) return fallback;
  try {
    return JSON.parse(value) as T;
  } catch {
    return fallback;
  }
}

function nullableBoolToInt(value: boolean | null): number | null {
  return value === null ? null : value ? 1 : 0;
}

function nullableIntToBool(value: number | null): boolean | null {
  return value === null ? null : value === 1;
}

function leagueIdFromDynastyId(dynastyId: string): string | null {
  return dynastyId.startsWith("league-") ? dynastyId.slice("league-".length) : null;
}

function emptySource(): SourcePointer {
  return {
    table: "unknown",
    uniqueId: 0,
    tableId: 0,
    row: 0
  };
}