import catalog from "../src/game/generated/dungeonLeaderboardCatalog.generated.json" with { type: "json" };
import { defaultBiteSizedBoardScope, normalizeBiteSizedSelection } from "../shared/bite-sized-layouts.mjs";

const dungeons = new Map(catalog.dungeons.map((entry) => [entry.id, entry]));
const classes = new Set(catalog.classes.map((entry) => entry.id));

function storedFinalBossId(dungeonId, difficultyId, killedBossIds) {
  const difficulty = dungeons.get(dungeonId)?.difficulties.find((entry) => entry.id === difficultyId);
  if (!difficulty) return null;
  if (!difficulty.finalBossIds?.length) return difficulty.finalBossId || null;
  const defeated = difficulty.finalBossIds.filter((id) => killedBossIds.includes(id));
  // The earlier Deadmines ship cut defeated Greenskin before VanCleef.
  if (dungeonId === "bite-sized-deadmines-ship" && defeated.length > 1 && defeated.includes("creature-79336")) {
    return "creature-79336";
  }
  return defeated.length === 1 ? defeated[0] : null;
}

export function createDungeonLeaderboardRepository(database, { ErrorType, biteSizedLayouts }) {
  database.exec(`
    CREATE TABLE IF NOT EXISTS dungeon_runs (
      id TEXT PRIMARY KEY,
      dungeon_id TEXT NOT NULL,
      difficulty_id TEXT NOT NULL,
      duration_ms INTEGER NOT NULL CHECK (duration_ms > 0),
      full_clear INTEGER NOT NULL CHECK (full_clear IN (0, 1)),
      finished_at INTEGER NOT NULL,
      party_json TEXT NOT NULL,
      bosses_json TEXT NOT NULL,
      submitted_by_account_id TEXT REFERENCES accounts(id) ON DELETE SET NULL,
      received_at INTEGER NOT NULL
    ) STRICT;
    CREATE INDEX IF NOT EXISTS dungeon_runs_times_idx
      ON dungeon_runs(dungeon_id, difficulty_id, duration_ms, finished_at, id);
    CREATE INDEX IF NOT EXISTS dungeon_runs_full_times_idx
      ON dungeon_runs(dungeon_id, difficulty_id, full_clear, duration_ms);
  `);
  // Zero archives pre-slider runs so their old boss balance cannot win the new Easy board.
  if (!database.prepare("PRAGMA table_info(dungeon_runs)").all().some((column) => column.name === "boss_difficulty")) {
    database.exec("ALTER TABLE dungeon_runs ADD COLUMN boss_difficulty INTEGER NOT NULL DEFAULT 0 CHECK (boss_difficulty BETWEEN 0 AND 10)");
  }
  if (!database.prepare("PRAGMA table_info(dungeon_runs)").all().some((column) => column.name === "final_boss_id")) {
    database.exec("ALTER TABLE dungeon_runs ADD COLUMN final_boss_id TEXT");
  }
  const updateFinalBoss = database.prepare("UPDATE dungeon_runs SET final_boss_id = ? WHERE id = ?");
  for (const row of database.prepare("SELECT id, dungeon_id, difficulty_id, bosses_json FROM dungeon_runs WHERE final_boss_id IS NULL").all()) {
    const bossId = storedFinalBossId(row.dungeon_id, row.difficulty_id, JSON.parse(row.bosses_json));
    if (bossId) updateFinalBoss.run(bossId, row.id);
  }
  database.exec(`CREATE INDEX IF NOT EXISTS dungeon_runs_boss_difficulty_idx
    ON dungeon_runs(dungeon_id, difficulty_id, boss_difficulty, full_clear, duration_ms, finished_at, id)`);
  database.exec(`CREATE INDEX IF NOT EXISTS dungeon_runs_final_boss_idx
    ON dungeon_runs(dungeon_id, difficulty_id, boss_difficulty, final_boss_id, full_clear, duration_ms, finished_at, id)`);
  const byId = database.prepare("SELECT * FROM dungeon_runs WHERE id = ?");
  for (const [name, type] of [["spot_id", "TEXT"], ["spot_revision", "INTEGER"], ["selection_json", "TEXT"]]) {
    if (!database.prepare("PRAGMA table_info(dungeon_runs)").all().some((c) => c.name === name)) database.exec(`ALTER TABLE dungeon_runs ADD COLUMN ${name} ${type}`);
  }
  for (const row of database.prepare("SELECT id, dungeon_id FROM dungeon_runs WHERE spot_id IS NULL").all()) {
    const scope = defaultBiteSizedBoardScope(row.dungeon_id);
    if (scope.spotId) database.prepare("UPDATE dungeon_runs SET spot_id = ?, spot_revision = ? WHERE id = ?").run(scope.spotId, scope.spotRevision, row.id);
  }
  database.exec("CREATE INDEX IF NOT EXISTS dungeon_runs_spot_times_idx ON dungeon_runs(dungeon_id, spot_id, spot_revision, difficulty_id, boss_difficulty, final_boss_id, duration_ms)");
  const insert = database.prepare(`INSERT INTO dungeon_runs
    (id, dungeon_id, difficulty_id, duration_ms, full_clear, finished_at, party_json, bosses_json, submitted_by_account_id, received_at, boss_difficulty, final_boss_id, spot_id, spot_revision, selection_json)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`);
  const fail = (message) => { throw new ErrorType(message, 400, "invalid_dungeon_run"); };
  const record = (row) => ({ id: row.id, dungeonId: row.dungeon_id, difficultyId: row.difficulty_id,
    ...(row.selection_json ? { biteSizedSelection: JSON.parse(row.selection_json) } : {}),
    ...(row.boss_difficulty > 0 ? { bossDifficulty: Number(row.boss_difficulty) } : {}),
    durationMs: Number(row.duration_ms), finishedAt: Number(row.finished_at), fullClear: row.full_clear === 1,
    party: JSON.parse(row.party_json), killedBossIds: JSON.parse(row.bosses_json) });
  function selection(input) {
    const dungeon = dungeons.get(input.dungeonId);
    if (!dungeon) fail("Choose a known dungeon.");
    const difficultyId = input.difficultyId || dungeon.defaultDifficultyId;
    if (!dungeon.difficulties.some((entry) => entry.id === difficultyId)) fail("Choose a supported dungeon difficulty.");
    const difficulty = dungeon.difficulties.find((entry) => entry.id === difficultyId);
    const bossDifficulty = input.bossDifficulty ?? 0;
    if (!Number.isInteger(bossDifficulty) || bossDifficulty < 0 || bossDifficulty > 10)
      fail("Choose a boss difficulty from 1 to 10.");
    const defaults = defaultBiteSizedBoardScope(dungeon.id);
    const spotId = input.spotId || defaults.spotId;
    const spotRevision = input.spotRevision == null ? defaults.spotRevision : Number(input.spotRevision);
    if (spotId && (!Number.isSafeInteger(spotRevision) || spotRevision < 0)) fail("Choose a valid spot revision.");
    if (spotId && biteSizedLayouts) {
      const spot = biteSizedLayouts.getBiteSizedSpotRevision(spotId, spotRevision);
      if (!dungeon.id.startsWith("randomized-bite-sized-") && spot.dungeonId !== dungeon.id) fail("Choose a spot in this dungeon.");
    }
    return { dungeon, difficultyId, difficulty, bossDifficulty, spotId, spotRevision };
  }
  function boardBossId(difficulty, requestedBossId) {
    const finalBossIds = difficulty.finalBossIds ?? [difficulty.finalBossId];
    if (requestedBossId && !finalBossIds.includes(requestedBossId)) fail("Choose a final boss from this dungeon.");
    return difficulty.finalBossIds?.length ? requestedBossId || finalBossIds[0] : null;
  }
  function placement(run, selectedBossId = storedFinalBossId(run.dungeonId, run.difficultyId, run.killedBossIds)) {
    const scope = run.biteSizedSelection ? { spotId: run.biteSizedSelection.spot.id, spotRevision: run.biteSizedSelection.spot.revision } : defaultBiteSizedBoardScope(run.dungeonId);
    const spotCondition = scope.spotId ? "AND spot_id = ? AND spot_revision = ?" : "";
    const variant = dungeons.get(run.dungeonId)?.difficulties.find((entry) => entry.id === run.difficultyId)?.finalBossIds?.length;
    const bossCondition = variant ? "AND final_boss_id = ?" : "";
    const counts = database.prepare(`SELECT COUNT(*) AS total,
      COALESCE(SUM(CASE WHEN duration_ms < ? THEN 1 ELSE 0 END), 0) AS faster,
      COALESCE(SUM(full_clear), 0) AS full_total,
      COALESCE(SUM(CASE WHEN full_clear = 1 AND duration_ms < ? THEN 1 ELSE 0 END), 0) AS full_faster
      FROM dungeon_runs WHERE dungeon_id = ? AND difficulty_id = ? AND boss_difficulty = ? ${bossCondition} ${spotCondition}`)
      .get(run.durationMs, run.durationMs, run.dungeonId, run.difficultyId, run.bossDifficulty ?? 0,
        ...(variant ? [selectedBossId] : []), ...(scope.spotId ? [scope.spotId, scope.spotRevision] : []));
    return { finalRank: Number(counts.faster) + 1, finalTotal: Number(counts.total),
      fullRank: run.fullClear ? Number(counts.full_faster) + 1 : null, fullTotal: Number(counts.full_total) };
  }
  return {
    submitDungeonRun(account, input) {
      if (!input || typeof input !== "object" || Array.isArray(input)) fail("Provide a completed dungeon run.");
      const { dungeon, difficultyId, difficulty, bossDifficulty } = selection(input);
      let biteSizedSelection;
      if (input.biteSizedSelection !== undefined) {
        try { biteSizedSelection = biteSizedLayouts ? biteSizedLayouts.validateBiteSizedRunSelection(input.biteSizedSelection, dungeon.id) : normalizeBiteSizedSelection(input.biteSizedSelection); }
        catch (error) { fail(error.message); }
        if (biteSizedSelection.dungeonId !== dungeon.id || input.killedBossIds?.length !== 1 || input.killedBossIds[0] !== biteSizedSelection.boss.objectiveId) fail("Defeat the selected spot's boss.");
      }
      if (input.bossDifficulty !== undefined && bossDifficulty === 0) fail("Choose a boss difficulty from 1 to 10.");
      if (!difficulty.finalBossId) fail("This dungeon does not have a configured final boss yet.");
      if (typeof input.id !== "string" || !/^[A-Za-z0-9_-]{8,100}$/.test(input.id)) fail("Provide a valid run id.");
      if (!Number.isSafeInteger(input.durationMs) || input.durationMs < 1 || input.durationMs > 7 * 24 * 60 * 60 * 1000)
        fail("Provide a valid completion time.");
      if (!Number.isSafeInteger(input.finishedAt) || input.finishedAt < 0 || input.finishedAt > Date.now() + 60_000)
        fail("Provide a valid finish timestamp.");
      if (!Array.isArray(input.killedBossIds) || input.killedBossIds.length > 100
        || input.killedBossIds.some((id) => !difficulty.bossIds.includes(id))) fail("Provide bosses from this dungeon and difficulty.");
      const killedBossIds = [...new Set(input.killedBossIds)].sort();
      const finalBossIds = difficulty.finalBossIds ?? [difficulty.finalBossId];
      if (!finalBossIds.some((id) => killedBossIds.includes(id))) fail("The final boss must be defeated before recording a run.");
      if (difficulty.finalBossIds && finalBossIds.filter((id) => killedBossIds.includes(id)).length !== 1)
        fail("A randomized run must defeat exactly one selected final boss.");
      if (!Array.isArray(input.party) || input.party.length < 1 || input.party.length > 5) fail("Provide the run's party.");
      const party = input.party.map((member) => {
        if (!member || typeof member !== "object" || typeof member.name !== "string" || !member.name.trim()
          || member.name.length > 40 || !classes.has(member.classId)
          || !Number.isInteger(member.level) || member.level < 1 || member.level > 80
          || !["tank", "healer", "damage"].includes(member.role) || typeof member.npc !== "boolean"
          || (member.secondaryClassId !== undefined && !classes.has(member.secondaryClassId))) fail("Provide valid party classes and levels.");
        return { name: member.name.trim(), classId: member.classId, level: member.level,
          role: member.role, npc: member.npc,
          ...(member.secondaryClassId ? { secondaryClassId: member.secondaryClassId } : {}) };
      });
      const fullClear = difficulty.finalBossIds
        ? difficulty.finalBossIds.some((id) => killedBossIds.includes(id))
        : difficulty.bossIds.every((id) => killedBossIds.includes(id));
      const run = { id: input.id, dungeonId: dungeon.id, difficultyId, durationMs: input.durationMs,
        ...(biteSizedSelection ? { biteSizedSelection } : {}),
        ...(bossDifficulty > 0 ? { bossDifficulty } : {}),
        finishedAt: input.finishedAt, killedBossIds, party, fullClear };
      const existing = byId.get(run.id);
      if (existing) {
        if (JSON.stringify(record(existing)) !== JSON.stringify({ id: run.id, dungeonId: run.dungeonId,
          difficultyId: run.difficultyId, ...(biteSizedSelection ? { biteSizedSelection } : {}), ...(bossDifficulty > 0 ? { bossDifficulty } : {}),
          durationMs: run.durationMs, finishedAt: run.finishedAt,
          fullClear: run.fullClear, party: run.party, killedBossIds: run.killedBossIds }))
          throw new ErrorType("That run id already belongs to a different result.", 409, "run_conflict");
      } else {
        const scope = biteSizedSelection ? { spotId: biteSizedSelection.spot.id, spotRevision: biteSizedSelection.spot.revision } : defaultBiteSizedBoardScope(run.dungeonId);
        insert.run(run.id, run.dungeonId, run.difficultyId, run.durationMs, fullClear ? 1 : 0,
        run.finishedAt, JSON.stringify(party), JSON.stringify(killedBossIds), account?.id ?? null, Date.now(), bossDifficulty,
        storedFinalBossId(run.dungeonId, run.difficultyId, run.killedBossIds), scope.spotId, scope.spotRevision, biteSizedSelection ? JSON.stringify(biteSizedSelection) : null);
      }
      return { run, placement: placement(run, existing?.final_boss_id ?? storedFinalBossId(run.dungeonId, run.difficultyId, run.killedBossIds)) };
    },
    getDungeonRun(id) {
      const row = byId.get(id);
      if (!row) throw new ErrorType("That dungeon run is not recorded yet.", 404, "run_not_found");
      const run = record(row);
      return { run, placement: placement(run, row.final_boss_id) };
    },
    listDungeonLeaderboard(input) {
      const { dungeon, difficultyId, difficulty, bossDifficulty, spotId, spotRevision } = selection(input);
      const filter = input.clear || "final";
      if (!["full", "final"].includes(filter)) fail("Choose full dungeon clears or final boss clears.");
      const offset = Number(input.offset ?? 0);
      const limit = Number(input.limit ?? 20);
      if (!Number.isSafeInteger(offset) || offset < 0 || offset > 1_000_000
        || !Number.isSafeInteger(limit) || limit < 1 || limit > 100) fail("Choose a valid leaderboard page.");
      const condition = filter === "full" ? "AND full_clear = 1" : "";
      const bossId = boardBossId(difficulty, input.bossId);
      const bossCondition = bossId ? "AND final_boss_id = ?" : "";
      const spotCondition = spotId ? "AND spot_id = ? AND spot_revision = ?" : "";
      const args = [dungeon.id, difficultyId, bossDifficulty, ...(bossId ? [bossId] : []), ...(spotId ? [spotId, spotRevision] : [])];
      const rows = database.prepare(`SELECT *, RANK() OVER (ORDER BY duration_ms) AS place FROM dungeon_runs
        WHERE dungeon_id = ? AND difficulty_id = ? AND boss_difficulty = ? ${condition} ${bossCondition} ${spotCondition}
        ORDER BY duration_ms, finished_at, id LIMIT ? OFFSET ?`).all(...args, limit, offset);
      const total = database.prepare(`SELECT COUNT(*) AS total FROM dungeon_runs
        WHERE dungeon_id = ? AND difficulty_id = ? AND boss_difficulty = ? ${condition} ${bossCondition} ${spotCondition}`).get(...args).total;
      return { entries: rows.map((row) => ({ ...record(row), rank: Number(row.place) })), total: Number(total) };
    },
  };
}