/**
 * DB-state fixtures for migration-system tests.
 *
 * Each fixture produces a real SQLite file at a caller-supplied path,
 * mutated to match a named DB state that exercises a specific reconcile path.
 *
 * Fixtures intentionally do not depend on the reconciler — they use Drizzle's
 * migrator directly and then mutate state as needed. This keeps the tests
 * independent of the code they exercise.
 *
 * Two migration sources are referenced:
 *   - MIGRATIONS_FOLDER: the CURRENT desktop drizzle folder, which after the
 *     2026 re-baseline holds the additive `0000_baseline` migration plus any
 *     migrations added since (e.g. `0001_generated_reports`). Counts that depend
 *     on it (recorded hashes, table totals) are derived — see ALL_MIGRATION_TAGS
 *     and expectedTableCount(). This is what reconcile runs against in production.
 *   - LEGACY_MIGRATIONS_FOLDER: a preserved snapshot of the OLD 11-migration
 *     history (0000_tricky_squadron_sinister … 0010_linear_project_mappings).
 *     Used to build genuine pre-squash "old user DB" shapes so we can prove
 *     that reconcile upgrades them to the current schema without data loss.
 */

import { Database } from "bun:sqlite";
import { createHash } from "node:crypto";
import { existsSync, readFileSync, unlinkSync } from "node:fs";
import { join } from "node:path";
import { drizzle } from "drizzle-orm/bun-sqlite";
import { migrate } from "drizzle-orm/bun-sqlite/migrator";

export const MIGRATIONS_FOLDER = join(
  import.meta.dir,
  "..",
  "..",
  "..",
  "..",
  "..",
  "apps",
  "desktop",
  "drizzle",
);

/**
 * Snapshot of the pre-squash 11-migration history, preserved under __tests__.
 * Only the fixtures/tests use it — production never ships it.
 */
export const LEGACY_MIGRATIONS_FOLDER = join(
  import.meta.dir,
  "..",
  "__tests__",
  "legacy-pre-squash",
);

/** The single tag in the re-baselined desktop migrations folder. */
export const BASELINE_TAG = "0000_baseline";

type JournalEntry = { idx: number; when: number; tag: string };
type Journal = { entries: JournalEntry[] };

export function readJournal(): Journal {
  return JSON.parse(
    readFileSync(join(MIGRATIONS_FOLDER, "meta", "_journal.json"), "utf8"),
  );
}

export function readLegacyJournal(): Journal {
  return JSON.parse(
    readFileSync(
      join(LEGACY_MIGRATIONS_FOLDER, "meta", "_journal.json"),
      "utf8",
    ),
  );
}

export function hashMigration(tag: string): string {
  const sql = readFileSync(join(MIGRATIONS_FOLDER, `${tag}.sql`), "utf8");
  return createHash("sha256").update(sql).digest("hex");
}

export function hashLegacyMigration(tag: string): string {
  const sql = readFileSync(
    join(LEGACY_MIGRATIONS_FOLDER, `${tag}.sql`),
    "utf8",
  );
  return createHash("sha256").update(sql).digest("hex");
}

/**
 * Every tag from the desktop drizzle journal, in journal (created_at) order.
 * Derived so adding a migration never requires touching the tests that count
 * recorded hashes.
 */
export const ALL_MIGRATION_TAGS: string[] = readJournal().entries.map(
  (e) => e.tag,
);

/** SHA-256 of each desktop migration's SQL, in journal order. */
export function allMigrationHashes(): string[] {
  return ALL_MIGRATION_TAGS.map(hashMigration);
}

/**
 * The number of DISTINCT tables that exist after applying every desktop
 * migration. Parses `CREATE TABLE <name>` across all `${tag}.sql` files,
 * excluding Drizzle's `__new_...` recreation temp tables. This history has no
 * table drops, so the set of created tables is the final table set.
 */
export function expectedTableCount(): number {
  const tables = new Set<string>();
  for (const tag of ALL_MIGRATION_TAGS) {
    const sql = readFileSync(join(MIGRATIONS_FOLDER, `${tag}.sql`), "utf8");
    for (const match of sql.matchAll(/CREATE TABLE\s+`?(\w+)`?/gi)) {
      const name = match[1];
      if (!name.startsWith("__new_")) tables.add(name);
    }
  }
  return tables.size;
}

function removeIfExists(path: string): void {
  if (existsSync(path)) unlinkSync(path);
  if (existsSync(`${path}-shm`)) unlinkSync(`${path}-shm`);
  if (existsSync(`${path}-wal`)) unlinkSync(`${path}-wal`);
}

/** Apply the CURRENT (re-baselined) Drizzle migrations against a fresh path. */
function applyDrizzleMigrations(path: string): Database {
  removeIfExists(path);
  const sqlite = new Database(path, { create: true });
  const db = drizzle({ client: sqlite });
  migrate(db, { migrationsFolder: MIGRATIONS_FOLDER });
  return sqlite;
}

/**
 * Apply the OLD 11-migration history against a fresh path, reproducing a real
 * pre-squash desktop DB shape (36 tables — missing the ~12 tables the baseline
 * later added, e.g. git_commits, email_log, waitlist_entries). The caller owns
 * the returned open handle.
 */
export function applyLegacyMigrations(path: string): Database {
  removeIfExists(path);
  const sqlite = new Database(path, { create: true });
  const db = drizzle({ client: sqlite });
  migrate(db, { migrationsFolder: LEGACY_MIGRATIONS_FOLDER });
  return sqlite;
}

/**
 * Fresh: DB file does not exist. Caller's job is to not create it.
 */
export function createFresh(path: string): void {
  removeIfExists(path);
}

/**
 * Healthy up-to-date: Drizzle migrate() ran cleanly against the current
 * baseline, so the single baseline hash is recorded.
 */
export function createHealthyUpToDate(path: string): void {
  const sqlite = applyDrizzleMigrations(path);
  sqlite.close();
}

/**
 * Airell's state (adapted for the re-baseline): a healthy DB whose
 * `timeline_rollups` table (+ its indexes, dropped automatically with the
 * table) is missing, as if a partial apply never created it. The migration
 * history is wiped so reconcile re-walks every migration as unapplied,
 * re-creates the missing table, and records the hashes. This is the crash-loop
 * state we have to heal.
 */
export function createAirellState(path: string): void {
  const sqlite = applyDrizzleMigrations(path);

  // Dropping the table also drops its indexes; observer_state +
  // live_activity_sessions remain, matching the original partial shape.
  sqlite.exec("DROP TABLE IF EXISTS timeline_rollups");

  // Wipe the migration history so reconcile walks every migration as unapplied
  // and re-creates the missing objects.
  sqlite.exec("DELETE FROM __drizzle_migrations");

  sqlite.close();
}

/**
 * Partial mid-file: every migration object exists, but the migration history is
 * missing from __drizzle_migrations (simulating a crash after all statements
 * applied but before the hash rows were inserted). Reconcile must re-walk each
 * file, find every object already present (skip), and record the hashes.
 */
export function createPartialMidFile(path: string): void {
  const sqlite = applyDrizzleMigrations(path);
  sqlite.exec("DELETE FROM __drizzle_migrations");
  sqlite.close();
}

/**
 * Pre-squash legacy: a genuine OLD-shape DB built from the 11-migration
 * history, with the migration-history rows wiped (the ancient backfill path
 * created tables without recording hashes). Reconcile against the current
 * baseline must add the ~12 tables the old history lacked, ALTER-add any
 * missing columns, and record the baseline hash — all without dropping data.
 */
export function createPreSquashLegacy(path: string): void {
  const sqlite = applyLegacyMigrations(path);
  sqlite.exec("DELETE FROM __drizzle_migrations");
  sqlite.close();
}

/**
 * Corrupt migrations table: the baseline hash plus a random unknown hash.
 * Regression guard: the old syncMigrationHistory would have DROPped this
 * whole table. The new reconciler must leave the unknown hash alone.
 */
export function createCorruptMigTable(path: string): void {
  const sqlite = applyDrizzleMigrations(path);
  const unknownHash = "f".repeat(64); // not the hash of any real file
  sqlite
    .prepare(
      "INSERT INTO __drizzle_migrations (hash, created_at) VALUES (?, ?)",
    )
    .run(unknownHash, Date.now());
  sqlite.close();
}

/**
 * Legacy drift: full baseline schema present, but the `user` table is missing
 * the `bap_id` column that the baseline declares. Simulates a DB created by an
 * old backfill path that built `user` with an earlier column set.
 *
 * Reconciler must detect the drift, add the missing column via ALTER TABLE,
 * then be able to execute `CREATE UNIQUE INDEX user_bap_id_unique` without
 * error. The baseline hash is removed so reconcile walks the baseline and sees
 * the drift on the missing-hash path.
 */
export function createLegacyDriftMissingColumn(path: string): void {
  const sqlite = applyDrizzleMigrations(path);

  // Simulate drift: recreate `user` without bap_id, and drop the dependent
  // unique indexes. Use a transaction so related tables don't see an
  // intermediate state. The recreated column set is the baseline's minus
  // bap_id; reconcile's ALTER-add path fills bap_id back in.
  sqlite.exec(`
    BEGIN;
    DROP INDEX IF EXISTS user_bap_id_unique;
    DROP INDEX IF EXISTS user_pubkey_unique;
    ALTER TABLE user RENAME TO user_backup_tmp;
    CREATE TABLE user (
      id text PRIMARY KEY NOT NULL,
      name text NOT NULL,
      email text NOT NULL,
      email_verified integer DEFAULT false NOT NULL,
      image text,
      created_at integer DEFAULT (cast(unixepoch('subsecond') * 1000 as integer)) NOT NULL,
      updated_at integer DEFAULT (cast(unixepoch('subsecond') * 1000 as integer)) NOT NULL,
      pubkey text,
      stripe_customer_id text,
      tour_completed integer DEFAULT false,
      onboarding_completed integer DEFAULT false,
      roles text DEFAULT 'user',
      role text,
      banned integer DEFAULT false,
      ban_reason text,
      ban_expires integer
    );
    INSERT INTO user (id, name, email, email_verified, image, created_at, updated_at, pubkey, stripe_customer_id, tour_completed, onboarding_completed, roles, role, banned, ban_reason, ban_expires)
      SELECT id, name, email, email_verified, image, created_at, updated_at, pubkey, stripe_customer_id, tour_completed, onboarding_completed, roles, role, banned, ban_reason, ban_expires FROM user_backup_tmp;
    DROP TABLE user_backup_tmp;
    COMMIT;
  `);

  // Remove the baseline hash so reconcile walks the baseline and sees the drift.
  sqlite
    .prepare("DELETE FROM __drizzle_migrations WHERE hash = ?")
    .run(hashMigration(BASELINE_TAG));

  sqlite.close();
}

/**
 * Hash present with a stale `created_at`. Simulates a DB where a previous
 * reconcile (or manual intervention) recorded the baseline hash but wrote the
 * wrong watermark timestamp. Drizzle's migrate() uses `created_at` as a
 * watermark, so a bad value would cause it to re-run the migration and collide.
 */
export function createStaleCreatedAt(path: string): void {
  const sqlite = applyDrizzleMigrations(path);
  sqlite
    .prepare("UPDATE __drizzle_migrations SET created_at = 0 WHERE hash = ?")
    .run(hashMigration(BASELINE_TAG));
  sqlite.close();
}