import dotenv from "dotenv";
import fs from "node:fs";
import path from "node:path";
import type {
  Pool,
  PoolConnection,
  ResultSetHeader,
  RowDataPacket,
} from "mysql2/promise";
import { loadConfig } from "../config.js";
import { closeDb, initDb } from "../db.js";
import { ensureSchema } from "../server/db-schema.js";
import { DIFFICULTIES, type DifficultyKey } from "../server/constants.js";

type CliOptions = {
  yes: boolean;
  dryRun: boolean;
  dumpPath: string;
};

type DumpLevelRow = {
  difficulty: DifficultyKey;
  levelIndex: number;
  payload: Record<string, unknown>;
};

type DumpLeaderboardRow = {
  userId: number;
  difficulty: DifficultyKey;
  levelIndex: number;
  timeMs: number;
  moves: number;
  createdAt: string;
};

type RestoreSummary = {
  dumpPath: string;
  dryRun: boolean;
  levels: {
    totalOld: number;
    totalCurrent: number;
    unchanged: number;
    changedMoveLimitOnly: number;
    changedStructural: number;
    comparable: number;
  };
  leaderboard: {
    totalDumpEntries: number;
    eligibleEntries: number;
    skippedStructural: number;
    skippedUnknownLevel: number;
    skippedMissingUser: number;
    upserted: number;
    beforeCount: number;
    afterCount: number;
  };
};

const parseArgs = (argv: string[]): CliOptions => {
  const options: CliOptions = {
    yes: false,
    dryRun: false,
    dumpPath: "../bdd/RollerLogic.sql",
  };

  for (let index = 0; index < argv.length; index += 1) {
    const token = argv[index];
    if (token === "--yes") {
      options.yes = true;
      continue;
    }
    if (token === "--dry-run") {
      options.dryRun = true;
      continue;
    }
    if (token === "--dump") {
      const value = argv[index + 1];
      if (!value || value.startsWith("--")) {
        throw new Error("Option --dump attend un chemin de fichier.");
      }
      options.dumpPath = value.trim();
      index += 1;
      continue;
    }
    throw new Error(`Option inconnue: ${token}`);
  }

  return options;
};

const printUsage = () => {
  console.log(
    [
      "Usage:",
      "  node dist/scripts/restore-compatible-leaderboard.js --yes [--dump ../bdd/RollerLogic.sql] [--dry-run]",
      "",
      "Options:",
      "  --yes             Confirme l'écriture en base.",
      "  --dump <path>     Dump SQL source (ancien leaderboard + ancien levels_catalog).",
      "  --dry-run         N'écrit rien, affiche seulement le plan de restauration.",
    ].join("\n"),
  );
};

const decodeSqlJsonString = (value: string): string => {
  return value
    .replace(/\\\\/g, "\\")
    .replace(/\\"/g, '"')
    .replace(/\\'/g, "'");
};

const parseDifficulty = (value: string): DifficultyKey | null => {
  if (DIFFICULTIES.includes(value as DifficultyKey)) {
    return value as DifficultyKey;
  }
  return null;
};

const parseDump = (
  dumpPath: string,
): { levels: DumpLevelRow[]; leaderboard: DumpLeaderboardRow[] } => {
  const text = fs.readFileSync(dumpPath, "utf8");
  const lines = text.split(/\r?\n/);
  const levels: DumpLevelRow[] = [];
  const leaderboard: DumpLeaderboardRow[] = [];
  let currentTable: string | null = null;

  for (const rawLine of lines) {
    const line = rawLine.trim();
    const insertStart = line.match(/^INSERT INTO `([^`]+)` .* VALUES$/);
    if (insertStart) {
      currentTable = insertStart[1];
      continue;
    }
    if (!currentTable) {
      continue;
    }
    if (!line.startsWith("(")) {
      if (line.endsWith(";")) {
        currentTable = null;
      }
      continue;
    }

    if (currentTable === "levels_catalog") {
      const tuple = line.match(
        /^\((\d+), '([^']+)', (\d+), (\d+), (\d+), '([^']*)', '(.*)', '([^']+)'\),?;?$/,
      );
      if (tuple) {
        const difficulty = parseDifficulty(tuple[2]);
        if (difficulty) {
          const payloadString = decodeSqlJsonString(tuple[7]);
          const payload = JSON.parse(payloadString) as Record<string, unknown>;
          levels.push({
            difficulty,
            levelIndex: Number(tuple[3]),
            payload,
          });
        }
      }
    } else if (currentTable === "leaderboard_entries") {
      const tuple = line.match(
        /^\((\d+), (\d+), '([^']+)', (\d+), (\d+), (\d+), '([^']+)'\),?;?$/,
      );
      if (tuple) {
        const difficulty = parseDifficulty(tuple[3]);
        if (difficulty) {
          leaderboard.push({
            userId: Number(tuple[2]),
            difficulty,
            levelIndex: Number(tuple[4]),
            timeMs: Number(tuple[5]),
            moves: Number(tuple[6]),
            createdAt: tuple[7],
          });
        }
      }
    }

    if (line.endsWith(";")) {
      currentTable = null;
    }
  }

  return { levels, leaderboard };
};

const sortObjectDeep = (value: unknown): unknown => {
  if (Array.isArray(value)) {
    return value.map(sortObjectDeep);
  }
  if (!value || typeof value !== "object") {
    return value;
  }
  const entries = Object.entries(value as Record<string, unknown>)
    .sort(([a], [b]) => a.localeCompare(b))
    .map(([key, nested]) => [key, sortObjectDeep(nested)]);
  return Object.fromEntries(entries);
};

const normalizeLevelPayload = (payload: Record<string, unknown>): string => {
  const topLevel = { ...payload };
  delete topLevel.moveLimit;
  return JSON.stringify(sortObjectDeep(topLevel));
};

const levelKey = (difficulty: DifficultyKey, levelIndex: number): string =>
  `${difficulty}:${levelIndex}`;

const computeScore = (timeMs: number, moves: number): number => {
  return Math.max(100, 10000 - Math.floor(timeMs / 1000) * 100 - moves * 10);
};

const dedupeLeaderboardEntries = (
  entries: DumpLeaderboardRow[],
): DumpLeaderboardRow[] => {
  const map = new Map<string, DumpLeaderboardRow>();
  for (const entry of entries) {
    const key = `${entry.userId}:${entry.difficulty}:${entry.levelIndex}`;
    const current = map.get(key);
    if (!current) {
      map.set(key, entry);
      continue;
    }
    const currentScore = computeScore(current.timeMs, current.moves);
    const nextScore = computeScore(entry.timeMs, entry.moves);
    if (
      nextScore > currentScore ||
      (nextScore === currentScore && entry.timeMs < current.timeMs)
    ) {
      map.set(key, entry);
    }
  }
  return Array.from(map.values());
};

const fetchLeaderboardCount = async (db: Pool): Promise<number> => {
  const [rows] = await db.query<RowDataPacket[]>(
    "SELECT COUNT(*) as total FROM leaderboard_entries",
  );
  return Number(rows[0]?.total ?? 0);
};

const fetchCurrentLevels = async (db: Pool): Promise<Map<string, string>> => {
  const [rows] = await db.query<RowDataPacket[]>(
    "SELECT difficulty, level_index as levelIndex, payload FROM levels_catalog",
  );
  const map = new Map<string, string>();
  for (const row of rows) {
    const difficulty = parseDifficulty(String(row.difficulty));
    if (!difficulty) {
      continue;
    }
    const payloadRaw = row.payload;
    let payload: Record<string, unknown>;
    if (typeof payloadRaw === "string") {
      payload = JSON.parse(payloadRaw) as Record<string, unknown>;
    } else if (payloadRaw && typeof payloadRaw === "object") {
      payload = payloadRaw as Record<string, unknown>;
    } else {
      continue;
    }
    const key = levelKey(difficulty, Number(row.levelIndex));
    map.set(key, normalizeLevelPayload(payload));
  }
  return map;
};

const fetchExistingUserIds = async (
  db: Pool,
  userIds: number[],
): Promise<Set<number>> => {
  if (userIds.length === 0) {
    return new Set<number>();
  }
  const placeholders = userIds.map(() => "?").join(",");
  const [rows] = await db.query<RowDataPacket[]>(
    `SELECT id FROM users WHERE id IN (${placeholders})`,
    userIds,
  );
  return new Set(rows.map((row) => Number(row.id)));
};

const upsertLeaderboardEntries = async (
  connection: PoolConnection,
  entries: DumpLeaderboardRow[],
): Promise<number> => {
  let upserted = 0;
  for (const entry of entries) {
    const [result] = await connection.execute<ResultSetHeader>(
      `
      INSERT INTO leaderboard_entries (user_id, difficulty, level_index, time_ms, moves, created_at)
      VALUES (?, ?, ?, ?, ?, ?)
      ON DUPLICATE KEY UPDATE
        time_ms = IF(10000 - FLOOR(VALUES(time_ms)/1000)*100 - VALUES(moves)*10 > score, VALUES(time_ms), time_ms),
        moves = IF(10000 - FLOOR(VALUES(time_ms)/1000)*100 - VALUES(moves)*10 > score, VALUES(moves), moves),
        created_at = IF(10000 - FLOOR(VALUES(time_ms)/1000)*100 - VALUES(moves)*10 > score, VALUES(created_at), created_at)
    `,
      [
        entry.userId,
        entry.difficulty,
        entry.levelIndex,
        entry.timeMs,
        entry.moves,
        entry.createdAt,
      ],
    );
    if (result.affectedRows > 0) {
      upserted += 1;
    }
  }
  return upserted;
};

const run = async () => {
  dotenv.config({ path: ".env" });

  const options = parseArgs(process.argv.slice(2));
  if (!options.yes) {
    printUsage();
    throw new Error("Confirmation manquante: ajoute --yes.");
  }

  const absoluteDumpPath = path.resolve(process.cwd(), options.dumpPath);
  if (!fs.existsSync(absoluteDumpPath)) {
    throw new Error(`Dump introuvable: ${absoluteDumpPath}`);
  }

  const config = loadConfig();
  const db = initDb(config);

  try {
    await ensureSchema(db);
    const dump = parseDump(absoluteDumpPath);
    const currentLevels = await fetchCurrentLevels(db);

    const oldLevelsMap = new Map<string, string>();
    for (const item of dump.levels) {
      oldLevelsMap.set(
        levelKey(item.difficulty, item.levelIndex),
        normalizeLevelPayload(item.payload),
      );
    }

    let unchanged = 0;
    let changedMoveLimitOnly = 0;
    let changedStructural = 0;
    const comparableKeys = new Set<string>();
    const structuralChangedKeys = new Set<string>();

    // Calcul robuste moveLimit-only en comparant payload complet old/new.
    const oldFullPayload = new Map<string, string>();
    for (const item of dump.levels) {
      oldFullPayload.set(
        levelKey(item.difficulty, item.levelIndex),
        JSON.stringify(sortObjectDeep(item.payload)),
      );
    }
    const [currentRows] = await db.query<RowDataPacket[]>(
      "SELECT difficulty, level_index as levelIndex, payload FROM levels_catalog",
    );
    const currentFullPayload = new Map<string, string>();
    for (const row of currentRows) {
      const difficulty = parseDifficulty(String(row.difficulty));
      if (!difficulty) continue;
      const payloadRaw = row.payload;
      let payload: Record<string, unknown>;
      if (typeof payloadRaw === "string") {
        payload = JSON.parse(payloadRaw) as Record<string, unknown>;
      } else if (payloadRaw && typeof payloadRaw === "object") {
        payload = payloadRaw as Record<string, unknown>;
      } else {
        continue;
      }
      currentFullPayload.set(
        levelKey(difficulty, Number(row.levelIndex)),
        JSON.stringify(sortObjectDeep(payload)),
      );
    }

    unchanged = 0;
    changedMoveLimitOnly = 0;
    changedStructural = 0;
    comparableKeys.clear();
    structuralChangedKeys.clear();

    for (const [key, oldFull] of oldFullPayload) {
      const currentFull = currentFullPayload.get(key);
      const oldNorm = oldLevelsMap.get(key);
      const currentNorm = currentLevels.get(key);
      if (!currentFull || !oldNorm || !currentNorm) {
        continue;
      }
      if (oldFull === currentFull) {
        unchanged += 1;
        comparableKeys.add(key);
        continue;
      }
      if (oldNorm === currentNorm) {
        changedMoveLimitOnly += 1;
        comparableKeys.add(key);
      } else {
        changedStructural += 1;
        structuralChangedKeys.add(key);
      }
    }

    let skippedStructural = 0;
    let skippedUnknownLevel = 0;
    const candidateEntries: DumpLeaderboardRow[] = [];
    for (const entry of dump.leaderboard) {
      const key = levelKey(entry.difficulty, entry.levelIndex);
      if (comparableKeys.has(key)) {
        candidateEntries.push(entry);
      } else if (structuralChangedKeys.has(key)) {
        skippedStructural += 1;
      } else {
        skippedUnknownLevel += 1;
      }
    }

    const deduped = dedupeLeaderboardEntries(candidateEntries);
    const userIds = Array.from(new Set(deduped.map((row) => row.userId)));
    const existingUserIds = await fetchExistingUserIds(db, userIds);
    const eligibleEntries = deduped.filter((entry) =>
      existingUserIds.has(entry.userId),
    );
    const skippedMissingUser = deduped.length - eligibleEntries.length;

    const beforeCount = await fetchLeaderboardCount(db);
    let upserted = 0;

    if (!options.dryRun && eligibleEntries.length > 0) {
      const connection = await db.getConnection();
      try {
        await connection.beginTransaction();
        upserted = await upsertLeaderboardEntries(connection, eligibleEntries);
        await connection.commit();
      } catch (error) {
        await connection.rollback();
        throw error;
      } finally {
        connection.release();
      }
    }

    const afterCount = options.dryRun
      ? beforeCount
      : await fetchLeaderboardCount(db);

    const summary: RestoreSummary = {
      dumpPath: absoluteDumpPath,
      dryRun: options.dryRun,
      levels: {
        totalOld: dump.levels.length,
        totalCurrent: currentLevels.size,
        unchanged,
        changedMoveLimitOnly,
        changedStructural,
        comparable: comparableKeys.size,
      },
      leaderboard: {
        totalDumpEntries: dump.leaderboard.length,
        eligibleEntries: eligibleEntries.length,
        skippedStructural,
        skippedUnknownLevel,
        skippedMissingUser,
        upserted,
        beforeCount,
        afterCount,
      },
    };

    console.log(JSON.stringify({ ok: true, ...summary }, null, 2));
  } finally {
    await closeDb();
  }
};

run().catch((error) => {
  const message = error instanceof Error ? error.message : String(error);
  console.error(`[leaderboard-restore] ERREUR: ${message}`);
  process.exit(1);
});