import { createHash, randomUUID } from "node:crypto";
import type Database from "better-sqlite3";
function embedFingerprint(url: string): string {
  try {
    const u = new URL(url);
    const key = `${u.hostname.toLowerCase()}${u.pathname.replace(/\/$/, "")}`;
    return createHash("sha256").update(key).digest("hex").slice(0, 32);
  } catch {
    return createHash("sha256").update(url).digest("hex").slice(0, 32);
  }
}

let migrationsDone = false;

type GameSeedRow = {
  id: string;
  slug: string;
  title: string;
  description: string | null;
  instructions: string | null;
  is_featured: number;
};

function syncSiteGames(database: Database.Database, siteId: string, games: GameSeedRow[]) {
  if (games.length === 0) return;
  const insertSg = database.prepare(
    `INSERT OR IGNORE INTO site_games (id, site_id, game_id, slug, title, description, instructions, is_featured)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
  );
  const insertAll = database.transaction((rows: GameSeedRow[]) => {
    for (const g of rows) {
      insertSg.run(randomUUID(), siteId, g.id, g.slug, g.title, g.description, g.instructions, g.is_featured);
    }
  });
  insertAll(games);
}

export function runMigrations(database: Database.Database) {
  if (migrationsDone) return;
  database.exec(`
    CREATE TABLE IF NOT EXISTS sites (
      id TEXT PRIMARY KEY,
      slug TEXT NOT NULL UNIQUE,
      name TEXT NOT NULL,
      tagline TEXT,
      meta_title TEXT,
      meta_description TEXT,
      logo_primary TEXT NOT NULL DEFAULT 'GAME',
      logo_accent TEXT NOT NULL DEFAULT 'HUB',
      theme_json TEXT NOT NULL DEFAULT '{}',
      uniquify_seed TEXT NOT NULL,
      is_active INTEGER NOT NULL DEFAULT 1,
      created_at TEXT NOT NULL DEFAULT (datetime('now'))
    );

    CREATE TABLE IF NOT EXISTS site_games (
      id TEXT PRIMARY KEY,
      site_id TEXT NOT NULL REFERENCES sites(id) ON DELETE CASCADE,
      game_id TEXT NOT NULL REFERENCES games(id) ON DELETE CASCADE,
      slug TEXT NOT NULL,
      title TEXT NOT NULL,
      description TEXT,
      instructions TEXT,
      meta_title TEXT,
      is_featured INTEGER NOT NULL DEFAULT 0,
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      UNIQUE (site_id, slug),
      UNIQUE (site_id, game_id)
    );

    CREATE INDEX IF NOT EXISTS site_games_site_idx ON site_games(site_id);
    CREATE INDEX IF NOT EXISTS site_games_slug_idx ON site_games(site_id, slug);

    CREATE TABLE IF NOT EXISTS translation_cache (
      hash TEXT NOT NULL,
      lang TEXT NOT NULL,
      translated TEXT NOT NULL,
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      PRIMARY KEY (hash, lang)
    );

    CREATE TABLE IF NOT EXISTS app_settings (
      key TEXT PRIMARY KEY,
      value TEXT NOT NULL
    );
  `);

  const gameCols = database.prepare("PRAGMA table_info(games)").all() as { name: string }[];
  if (!gameCols.some((c) => c.name === "embed_fingerprint")) {
    database.exec("ALTER TABLE games ADD COLUMN embed_fingerprint TEXT");
  }
  if (!gameCols.some((c) => c.name === "thumb_local")) {
    database.exec("ALTER TABLE games ADD COLUMN thumb_local TEXT");
  }

  database.exec(`
    CREATE UNIQUE INDEX IF NOT EXISTS games_embed_fp_idx
      ON games(embed_fingerprint) WHERE embed_fingerprint IS NOT NULL;
  `);

  const LIGHT_THEME_JSON = JSON.stringify({
    accent: "#ea580c",
    neon: "#0284c7",
    bg: "#f4f6fa",
    surface: "#ffffff",
  });
  database.prepare(`UPDATE sites SET theme_json = ?`).run(LIGHT_THEME_JSON);

  const fpRows = database
    .prepare("SELECT id, embed_url FROM games WHERE embed_fingerprint IS NULL OR embed_fingerprint = ''")
    .all() as { id: string; embed_url: string }[];
  const setFp = database.prepare("UPDATE games SET embed_fingerprint = ? WHERE id = ?");
  if (fpRows.length > 0) {
    const backfill = database.transaction((rows: typeof fpRows) => {
      for (const row of rows) {
        setFp.run(embedFingerprint(row.embed_url), row.id);
      }
    });
    backfill(fpRows);
  }

  database.exec(`
    INSERT OR IGNORE INTO app_settings (key, value) VALUES ('auto_translate', '1');
    INSERT OR IGNORE INTO app_settings (key, value) VALUES ('translate_lang', 'ru');
  `);

  const siteCount = database.prepare("SELECT COUNT(*) AS c FROM sites").get() as { c: number };
  if (siteCount.c === 0) {
    const mainId = randomUUID();
    database
      .prepare(
        `INSERT INTO sites (id, slug, name, tagline, meta_title, meta_description, logo_primary, logo_accent, uniquify_seed, theme_json)
         VALUES (?, 'main', 'GameOrbit', 'Тысячи HTML5 игр онлайн', 'GameOrbit — бесплатные онлайн игры', 'Играй в тысячи бесплатных браузерных игр без регистрации', 'GAME', 'ORBIT', 'main', ?)`,
      )
      .run(
        mainId,
        JSON.stringify({
          accent: "#ea580c",
          neon: "#0284c7",
          bg: "#f4f6fa",
          surface: "#ffffff",
        }),
      );

    const games = database
      .prepare("SELECT id, slug, title, description, instructions, is_featured FROM games")
      .all() as GameSeedRow[];
    syncSiteGames(database, mainId, games);
  }

  const sgCount = database.prepare("SELECT COUNT(*) AS c FROM site_games").get() as { c: number };
  const gameCount = database.prepare("SELECT COUNT(*) AS c FROM games").get() as { c: number };
  if (sgCount.c === 0 && gameCount.c > 0) {
    const main = database.prepare("SELECT id FROM sites WHERE slug = 'main'").get() as { id: string };
    const games = database
      .prepare("SELECT id, slug, title, description, instructions, is_featured FROM games")
      .all() as GameSeedRow[];
    syncSiteGames(database, main.id, games);
  }

  migrationsDone = true;
}

export { embedFingerprint };