import { Database } from "bun:sqlite";
import { afterEach, beforeEach, describe, expect, test } from "bun:test";
import { createHash } from "node:crypto";
import { existsSync, mkdtempSync, readFileSync, rmSync } from "node:fs";
import { tmpdir } from "node:os";
import { join } from "node:path";
import {
  ALL_MIGRATION_TAGS,
  allMigrationHashes,
  BASELINE_TAG,
  createAirellState,
  createCorruptMigTable,
  createFresh,
  createHealthyUpToDate,
  createLegacyDriftMissingColumn,
  createPartialMidFile,
  createPreSquashLegacy,
  createStaleCreatedAt,
  expectedTableCount,
  hashLegacyMigration,
  hashMigration,
  LEGACY_MIGRATIONS_FOLDER,
  MIGRATIONS_FOLDER,
  readLegacyJournal,
} from "../fixtures";
import { reconcile } from "../reconcile";

// `when` for the single baseline migration, per apps/desktop/drizzle/meta/_journal.json.
const BASELINE_WHEN = 1784334787884;

let tmpDir: string;
let dbPath: string;

const OLD_0007_APIKEY_CONFIG_REFERENCE_SQL =
  'PRAGMA foreign_keys=OFF;--> statement-breakpoint\nCREATE TABLE `__new_apikey` (\n\t`id` text PRIMARY KEY NOT NULL,\n\t`config_id` text DEFAULT \'default\' NOT NULL,\n\t`name` text,\n\t`start` text,\n\t`reference_id` text NOT NULL,\n\t`prefix` text,\n\t`key` text NOT NULL,\n\t`refill_interval` integer,\n\t`refill_amount` integer,\n\t`last_refill_at` integer,\n\t`enabled` integer DEFAULT true,\n\t`rate_limit_enabled` integer DEFAULT true,\n\t`rate_limit_time_window` integer DEFAULT 86400000,\n\t`rate_limit_max` integer DEFAULT 10,\n\t`request_count` integer DEFAULT 0,\n\t`remaining` integer,\n\t`last_request` integer,\n\t`expires_at` integer,\n\t`created_at` integer NOT NULL,\n\t`updated_at` integer NOT NULL,\n\t`permissions` text,\n\t`metadata` text\n);\n--> statement-breakpoint\nINSERT INTO `__new_apikey`("id", "name", "start", "prefix", "key", "refill_interval", "refill_amount", "last_refill_at", "enabled", "rate_limit_enabled", "rate_limit_time_window", "rate_limit_max", "request_count", "remaining", "last_request", "expires_at", "created_at", "updated_at", "permissions", "metadata") SELECT "id", "name", "start", "prefix", "key", "refill_interval", "refill_amount", "last_refill_at", "enabled", "rate_limit_enabled", "rate_limit_time_window", "rate_limit_max", "request_count", "remaining", "last_request", "expires_at", "created_at", "updated_at", "permissions", "metadata" FROM `apikey`;--> statement-breakpoint\nDROP TABLE `apikey`;--> statement-breakpoint\nALTER TABLE `__new_apikey` RENAME TO `apikey`;--> statement-breakpoint\nPRAGMA foreign_keys=ON;--> statement-breakpoint\nCREATE INDEX `apikey_configId_idx` ON `apikey` (`config_id`);--> statement-breakpoint\nCREATE INDEX `apikey_referenceId_idx` ON `apikey` (`reference_id`);--> statement-breakpoint\nCREATE INDEX `apikey_key_idx` ON `apikey` (`key`);';

const OLD_0007_APIKEY_CONFIG_REFERENCE_HASH = createHash("sha256")
  .update(OLD_0007_APIKEY_CONFIG_REFERENCE_SQL)
  .digest("hex");

beforeEach(() => {
  tmpDir = mkdtempSync(join(tmpdir(), "scribe-reconcile-"));
  dbPath = join(tmpDir, "scribe.db");
});

afterEach(() => {
  rmSync(tmpDir, { recursive: true, force: true });
});

function openAndReconcile(): Database {
  const sqlite = new Database(dbPath, { create: true });
  // Reconcile assumes __drizzle_migrations may or may not exist; it creates
  // the table itself. The sqlite.exec PRAGMA journal_mode=WAL is not
  // required for these unit tests — synchronous default is fine.
  reconcile(sqlite, { folder: MIGRATIONS_FOLDER });
  return sqlite;
}

// Reconcile against the preserved OLD 11-migration history. Used only by the
// apikey-recreation + superseded-hash tests, whose scenarios are properties of
// migration 0007 — squashed out of the shipped baseline but still exercised
// here against the real legacy files so the reconciler's recreation/aliasing
// logic keeps its regression coverage.
function openAndReconcileLegacy(): Database {
  const sqlite = new Database(dbPath, { create: true });
  reconcile(sqlite, { folder: LEGACY_MIGRATIONS_FOLDER });
  return sqlite;
}

function getHashes(sqlite: Database): string[] {
  const rows = sqlite
    .prepare("SELECT hash FROM __drizzle_migrations ORDER BY created_at, id")
    .all() as Array<{ hash: string }>;
  return rows.map((r) => r.hash);
}

function tableExists(sqlite: Database, name: string): boolean {
  return (
    sqlite
      .prepare("SELECT 1 FROM sqlite_master WHERE type = 'table' AND name = ?")
      .get(name) !== null
  );
}

function createPre0007WithApikey(path: string): void {
  createFresh(path);
  const sqlite = new Database(path, { create: true });
  sqlite.exec(
    "CREATE TABLE __drizzle_migrations (id INTEGER PRIMARY KEY AUTOINCREMENT, hash TEXT NOT NULL, created_at NUMERIC)",
  );

  for (const entry of readLegacyJournal().entries.filter((e) => e.idx < 7)) {
    const sql = readFileSync(
      join(LEGACY_MIGRATIONS_FOLDER, `${entry.tag}.sql`),
      "utf8",
    );
    sqlite.exec(sql);
    sqlite
      .prepare(
        "INSERT INTO __drizzle_migrations (hash, created_at) VALUES (?, ?)",
      )
      .run(hashLegacyMigration(entry.tag), entry.when);
  }

  sqlite
    .prepare(
      `INSERT INTO "user" ("id", "name", "email", "email_verified", "created_at", "updated_at")
       VALUES (?, ?, ?, ?, ?, ?)`,
    )
    .run("user_seed", "Seed User", "seed@example.com", 1, 1, 1);
  sqlite
    .prepare(
      `INSERT INTO "apikey" ("id", "name", "prefix", "key", "user_id", "enabled", "rate_limit_enabled", "request_count", "created_at", "updated_at")
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
    )
    .run(
      "key_seed",
      "Seed Key",
      "sk",
      "hashed-key",
      "user_seed",
      1,
      1,
      0,
      2,
      3,
    );

  sqlite.close();
}

function createOld0007AppliedEmptyApikey(path: string): void {
  createFresh(path);
  const sqlite = new Database(path, { create: true });
  sqlite.exec(
    "CREATE TABLE __drizzle_migrations (id INTEGER PRIMARY KEY AUTOINCREMENT, hash TEXT NOT NULL, created_at NUMERIC)",
  );

  let migration0007When = 0;
  for (const entry of readLegacyJournal().entries) {
    if (entry.idx < 7) {
      const sql = readFileSync(
        join(LEGACY_MIGRATIONS_FOLDER, `${entry.tag}.sql`),
        "utf8",
      );
      sqlite.exec(sql);
      sqlite
        .prepare(
          "INSERT INTO __drizzle_migrations (hash, created_at) VALUES (?, ?)",
        )
        .run(hashLegacyMigration(entry.tag), entry.when);
    } else if (entry.tag === "0007_apikey_config_reference") {
      migration0007When = entry.when;
    }
  }

  sqlite.exec(OLD_0007_APIKEY_CONFIG_REFERENCE_SQL);
  sqlite
    .prepare(
      "INSERT INTO __drizzle_migrations (hash, created_at) VALUES (?, ?)",
    )
    .run(OLD_0007_APIKEY_CONFIG_REFERENCE_HASH, migration0007When);

  sqlite.close();
}

describe("reconcile", () => {
  test("reconcile_is_noop_on_up_to_date_db: baseline hash already present, no work to do", () => {
    createHealthyUpToDate(dbPath);
    const before = new Database(dbPath, { readonly: true });
    const hashesBefore = getHashes(before);
    before.close();
    expect(hashesBefore.length).toBe(ALL_MIGRATION_TAGS.length);
    expect(hashesBefore).toEqual(allMigrationHashes());

    const after = openAndReconcile();
    const hashesAfter = getHashes(after);
    after.close();

    expect(hashesAfter).toEqual(hashesBefore);
  });

  test("reconcile_recovers_airell_partial: creates missing timeline_rollups + records baseline hash", () => {
    createAirellState(dbPath);

    const before = new Database(dbPath, { readonly: true });
    expect(tableExists(before, "timeline_rollups")).toBe(false);
    expect(tableExists(before, "live_activity_sessions")).toBe(true);
    expect(tableExists(before, "observer_state")).toBe(true);
    // Baseline hash was removed so reconcile re-walks the file.
    expect(getHashes(before).length).toBe(0);
    before.close();

    const after = openAndReconcile();

    expect(tableExists(after, "timeline_rollups")).toBe(true);
    expect(tableExists(after, "live_activity_sessions")).toBe(true);
    expect(tableExists(after, "observer_state")).toBe(true);
    const hashes = getHashes(after);
    expect(hashes.length).toBe(ALL_MIGRATION_TAGS.length);
    expect(hashes).toContain(hashMigration(BASELINE_TAG));
    for (const h of allMigrationHashes()) expect(hashes).toContain(h);
    after.close();
  });

  test("reconcile_recovers_mid_file_partial: re-runs missing file against objects that already exist, records hash", () => {
    createPartialMidFile(dbPath);

    const before = new Database(dbPath, { readonly: true });
    // Every object exists but the hash row was never written.
    expect(getHashes(before).length).toBe(0);
    expect(tableExists(before, "timeline_rollups")).toBe(true);
    expect(tableExists(before, "user")).toBe(true);
    before.close();

    const after = openAndReconcile();

    const hashes = getHashes(after);
    expect(hashes).toContain(hashMigration(BASELINE_TAG));
    for (const h of allMigrationHashes()) expect(hashes).toContain(h);
    expect(hashes.length).toBe(ALL_MIGRATION_TAGS.length);
    after.close();
  });

  test("reconcile_backfills_pre_squash_legacy_to_current_schema: old-shape DB with no hashes -> adds the tables the old history lacked and records the baseline hash", () => {
    createPreSquashLegacy(dbPath);

    const before = new Database(dbPath, { readonly: true });
    expect(getHashes(before).length).toBe(0);
    // Legacy shape has these; it lacks the ~12 tables the baseline later added.
    expect(tableExists(before, "live_activity_sessions")).toBe(true);
    expect(tableExists(before, "timeline_rollups")).toBe(true);
    expect(tableExists(before, "email_log")).toBe(false);
    expect(tableExists(before, "waitlist_entries")).toBe(false);
    expect(tableExists(before, "git_commits")).toBe(false);
    before.close();

    const after = openAndReconcile();

    // Baseline hash recorded; the legacy tables were preserved and the missing
    // ones created.
    const hashes = getHashes(after);
    expect(hashes.length).toBe(ALL_MIGRATION_TAGS.length);
    expect(hashes).toContain(hashMigration(BASELINE_TAG));
    for (const h of allMigrationHashes()) expect(hashes).toContain(h);
    expect(tableExists(after, "email_log")).toBe(true);
    expect(tableExists(after, "waitlist_entries")).toBe(true);
    expect(tableExists(after, "git_commits")).toBe(true);
    // Full current schema: every table the desktop migrations create.
    const tableCount = (
      after
        .prepare(
          "SELECT COUNT(*) AS c FROM sqlite_master WHERE type = 'table' " +
            "AND name NOT LIKE 'sqlite_%' AND name != '__drizzle_migrations'",
        )
        .get() as { c: number }
    ).c;
    expect(tableCount).toBe(expectedTableCount());
    after.close();
  });

  test("reconcile_applies_0006_auth_admin_billing_columns: fresh DB carries the Better Auth admin + billing columns and the pubkey unique index", () => {
    createFresh(dbPath);

    const after = openAndReconcile();

    const userCols = new Set(
      (
        after.prepare(`PRAGMA table_info("user")`).all() as Array<{
          name: string;
        }>
      ).map((c) => c.name),
    );
    for (const col of [
      "pubkey",
      "role",
      "roles",
      "banned",
      "ban_reason",
      "ban_expires",
    ]) {
      expect(userCols.has(col)).toBe(true);
    }

    const sessionCols = new Set(
      (
        after.prepare(`PRAGMA table_info("session")`).all() as Array<{
          name: string;
        }>
      ).map((c) => c.name),
    );
    expect(sessionCols.has("roles")).toBe(true);
    expect(sessionCols.has("impersonated_by")).toBe(true);

    const subCols = new Set(
      (
        after.prepare(`PRAGMA table_info("subscription")`).all() as Array<{
          name: string;
        }>
      ).map((c) => c.name),
    );
    expect(subCols.has("billing_interval")).toBe(true);
    expect(subCols.has("stripe_schedule_id")).toBe(true);

    const pubkeyIdx = after
      .prepare(
        "SELECT 1 FROM sqlite_master WHERE type = 'index' AND name = 'user_pubkey_unique'",
      )
      .get();
    expect(pubkeyIdx).not.toBeNull();
    after.close();
  });

  test("reconcile_applies_0009_clients_abbreviation_per_user_uniqueness: different users can reuse an abbreviation but one user cannot", () => {
    createFresh(dbPath);

    const after = openAndReconcile();

    after
      .prepare(
        `INSERT INTO clients (id, user_id, name, abbreviation)
         VALUES (?, ?, ?, ?)`,
      )
      .run("client_user_1_a", "user_1", "User One A", "ACME");
    after
      .prepare(
        `INSERT INTO clients (id, user_id, name, abbreviation)
         VALUES (?, ?, ?, ?)`,
      )
      .run("client_user_2_a", "user_2", "User Two A", "ACME");

    expect(() =>
      after
        .prepare(
          `INSERT INTO clients (id, user_id, name, abbreviation)
           VALUES (?, ?, ?, ?)`,
        )
        .run("client_user_1_b", "user_1", "User One B", "ACME"),
    ).toThrow();

    after.close();
  });

  test("reconcile_applies_0007_apikey_recreation: fresh DB carries config_id + reference_id, drops user_id, and swaps the userId index for configId/referenceId", () => {
    createFresh(dbPath);

    const after = openAndReconcile();

    const apikeyCols = new Set(
      (
        after.prepare(`PRAGMA table_info("apikey")`).all() as Array<{
          name: string;
        }>
      ).map((c) => c.name),
    );
    expect(apikeyCols.has("config_id")).toBe(true);
    expect(apikeyCols.has("reference_id")).toBe(true);
    // The Better Auth apikey-plugin change dropped user_id.
    expect(apikeyCols.has("user_id")).toBe(false);

    const indexNames = new Set(
      (
        after
          .prepare(
            "SELECT name FROM sqlite_master WHERE type = 'index' AND tbl_name = 'apikey'",
          )
          .all() as Array<{ name: string }>
      ).map((r) => r.name),
    );
    expect(indexNames.has("apikey_configId_idx")).toBe(true);
    expect(indexNames.has("apikey_referenceId_idx")).toBe(true);
    expect(indexNames.has("apikey_key_idx")).toBe(true);
    expect(indexNames.has("apikey_userId_idx")).toBe(false);

    // The recreation must not leave the temp table behind.
    expect(tableExists(after, "__new_apikey")).toBe(false);
    after.close();
  });

  test("reconcile_applies_0007_apikey_recreation_with_seeded_row: carries user_id into reference_id", () => {
    // 0007 was squashed out of the shipped baseline, so this data-migration
    // behavior is exercised against the preserved legacy history, where the
    // recreation migration still lives.
    createPre0007WithApikey(dbPath);

    const after = openAndReconcileLegacy();

    const apikeyCols = new Set(
      (
        after.prepare(`PRAGMA table_info("apikey")`).all() as Array<{
          name: string;
        }>
      ).map((c) => c.name),
    );
    expect(apikeyCols.has("config_id")).toBe(true);
    expect(apikeyCols.has("reference_id")).toBe(true);
    expect(apikeyCols.has("user_id")).toBe(false);

    const row = after
      .prepare(
        `SELECT "id", "reference_id", "name", "prefix", "key", "created_at", "updated_at"
         FROM "apikey"
         WHERE "id" = ?`,
      )
      .get("key_seed") as {
      id: string;
      reference_id: string;
      name: string | null;
      prefix: string | null;
      key: string;
      created_at: number;
      updated_at: number;
    } | null;

    expect(row).toEqual({
      id: "key_seed",
      reference_id: "user_seed",
      name: "Seed Key",
      prefix: "sk",
      key: "hashed-key",
      created_at: 2,
      updated_at: 3,
    });
    after.close();
  });

  test("reconcile_aliases_superseded_0007_hash_without_rerunning_apikey_recreation", () => {
    // SUPERSEDED_MIGRATION_HASHES aliasing is keyed on the 0007 tag, which only
    // exists in the preserved legacy history — reconcile against it exercises
    // the aliasing path against the real superseded SQL text.
    createOld0007AppliedEmptyApikey(dbPath);

    const before = new Database(dbPath, { readonly: true });
    expect(getHashes(before)).toContain(OLD_0007_APIKEY_CONFIG_REFERENCE_HASH);
    expect(getHashes(before)).not.toContain(
      hashLegacyMigration("0007_apikey_config_reference"),
    );
    before.close();

    const after = openAndReconcileLegacy();

    const hashes = getHashes(after);
    expect(hashes.length).toBe(11);
    expect(hashes).toContain(
      hashLegacyMigration("0007_apikey_config_reference"),
    );
    expect(hashes).not.toContain(OLD_0007_APIKEY_CONFIG_REFERENCE_HASH);

    const apikeyRows = after.prepare("SELECT * FROM apikey").all() as Array<
      Record<string, unknown>
    >;
    expect(apikeyRows).toEqual([]);
    const apikeyCols = new Set(
      (
        after.prepare(`PRAGMA table_info("apikey")`).all() as Array<{
          name: string;
        }>
      ).map((c) => c.name),
    );
    expect(apikeyCols.has("reference_id")).toBe(true);
    expect(apikeyCols.has("user_id")).toBe(false);
    after.close();

    const second = openAndReconcileLegacy();
    expect(getHashes(second)).toEqual(hashes);
    second.close();
  });

  test("reconcile_is_idempotent_across_apikey_recreation: second pass on an already-recreated DB does not throw on the __new_apikey rename", () => {
    // First pass applies 0007 (the apikey table-recreation). Second pass hits
    // the hash-present repair path, which walks the migration's
    // `CREATE TABLE __new_apikey` chunk — reconcile must verify the renamed-to
    // `apikey` table, not throw on the now-gone temp table.
    createFresh(dbPath);
    const first = openAndReconcile();
    const hashesFirst = getHashes(first);
    first.close();

    const second = openAndReconcile();
    const hashesSecond = getHashes(second);
    const apikeyCols = new Set(
      (
        second.prepare(`PRAGMA table_info("apikey")`).all() as Array<{
          name: string;
        }>
      ).map((c) => c.name),
    );
    second.close();

    expect(hashesSecond).toEqual(hashesFirst);
    expect(hashesSecond.length).toBe(ALL_MIGRATION_TAGS.length);
    expect(apikeyCols.has("config_id")).toBe(true);
    expect(apikeyCols.has("user_id")).toBe(false);
  });

  test("reconcile_is_noop_on_fresh_db: no DB, no tables -> creates everything via real SQL from migrations", () => {
    createFresh(dbPath);
    expect(existsSync(dbPath)).toBe(false);

    const after = openAndReconcile();

    const hashes = getHashes(after);
    expect(hashes.length).toBe(ALL_MIGRATION_TAGS.length);
    // Spot-check a few representative tables the baseline creates.
    expect(tableExists(after, "user")).toBe(true);
    expect(tableExists(after, "environments")).toBe(true);
    expect(tableExists(after, "timeline_rollups")).toBe(true);
    after.close();
  });

  test("reconcile_does_not_drop_unknown_hashes: regression guard vs old syncMigrationHistory which did DROP TABLE", () => {
    createCorruptMigTable(dbPath);
    const unknownHash = "f".repeat(64);

    const before = new Database(dbPath, { readonly: true });
    expect(getHashes(before)).toContain(unknownHash);
    before.close();

    const after = openAndReconcile();

    const hashes = getHashes(after);
    expect(hashes).toContain(unknownHash);
    // Every real migration hash plus the one unknown hash.
    expect(hashes.length).toBe(ALL_MIGRATION_TAGS.length + 1);
    after.close();
  });

  test("reconcile is idempotent: running twice in a row produces the same state as running once", () => {
    createAirellState(dbPath);

    const first = openAndReconcile();
    const hashesFirst = getHashes(first);
    first.close();

    const second = openAndReconcile();
    const hashesSecond = getHashes(second);
    second.close();

    expect(hashesSecond).toEqual(hashesFirst);
  });

  test("reconcile uses journal folderMillis as created_at (not Date.now), so Drizzle's own migrate() will skip on subsequent calls", () => {
    createAirellState(dbPath);
    const after = openAndReconcile();

    const row = after
      .prepare("SELECT created_at FROM __drizzle_migrations WHERE hash = ?")
      .get(hashMigration(BASELINE_TAG)) as { created_at: number } | null;
    expect(row).not.toBeNull();
    // The baseline's `when` per _journal.json is 1784334787884 (< Date.now())
    expect(Number(row?.created_at)).toBe(BASELINE_WHEN);
    after.close();
  });

  test("reconcile_repairs_stale_created_at: hash present but created_at wrong -> UPDATE to journal.when", () => {
    createStaleCreatedAt(dbPath);

    const before = new Database(dbPath, { readonly: true });
    const staleRow = before
      .prepare("SELECT created_at FROM __drizzle_migrations WHERE hash = ?")
      .get(hashMigration(BASELINE_TAG)) as { created_at: number } | null;
    expect(Number(staleRow?.created_at)).toBe(0);
    before.close();

    const after = openAndReconcile();
    const repairedRow = after
      .prepare("SELECT created_at FROM __drizzle_migrations WHERE hash = ?")
      .get(hashMigration(BASELINE_TAG)) as { created_at: number } | null;
    expect(Number(repairedRow?.created_at)).toBe(BASELINE_WHEN);

    // Hash count unchanged — we repaired, didn't duplicate.
    const hashes = getHashes(after);
    expect(hashes.length).toBe(ALL_MIGRATION_TAGS.length);
    after.close();
  });

  test("reconcile_repairs_legacy_drift: table exists but missing a column declared in CREATE TABLE -> ALTER TABLE ADD COLUMN", () => {
    createLegacyDriftMissingColumn(dbPath);

    const before = new Database(dbPath, { readonly: true });
    const preCols = before.prepare(`PRAGMA table_info("user")`).all() as Array<{
      name: string;
    }>;
    expect(preCols.some((c) => c.name === "bap_id")).toBe(false);
    before.close();

    const after = openAndReconcile();
    const postCols = after.prepare(`PRAGMA table_info("user")`).all() as Array<{
      name: string;
    }>;
    expect(postCols.some((c) => c.name === "bap_id")).toBe(true);

    // The dependent unique index must now also be creatable (part of 0000).
    const uniqueIdx = after
      .prepare(
        "SELECT 1 FROM sqlite_master WHERE type = 'index' AND name = 'user_bap_id_unique'",
      )
      .get();
    expect(uniqueIdx).not.toBeNull();

    // Baseline hash must be recorded now.
    expect(getHashes(after)).toContain(hashMigration(BASELINE_TAG));
    after.close();
  });

  test("reconcile_heals_hash_present_column_drift: hash recorded but table missing a column gets ADD COLUMN'd", () => {
    // Start from up-to-date state, then simulate a legacy-drift scenario
    // where the hash is present but the live table is missing a column
    // declared in the migration (bap_id was added by backfill post-0000 in
    // an older code path; imagine a DB that recorded 0000 as applied but
    // never got bap_id).
    createHealthyUpToDate(dbPath);

    const manip = new Database(dbPath);
    manip.exec(`
      BEGIN;
      DROP INDEX IF EXISTS user_bap_id_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 NOT NULL,
        image text,
        created_at integer DEFAULT (unixepoch()) NOT NULL,
        updated_at integer DEFAULT (unixepoch()) NOT NULL,
        tour_completed integer DEFAULT false,
        onboarding_completed integer DEFAULT false,
        stripe_customer_id text
      );
      INSERT INTO "user" (id, name, email, email_verified, image, created_at, updated_at, tour_completed, onboarding_completed, stripe_customer_id)
        SELECT id, name, email, email_verified, image, created_at, updated_at, tour_completed, onboarding_completed, stripe_customer_id FROM user_backup_tmp;
      DROP TABLE user_backup_tmp;
      COMMIT;
    `);
    // Verify pre-state: bap_id missing, 0000 hash still recorded
    const preCols = manip.prepare(`PRAGMA table_info("user")`).all() as Array<{
      name: string;
    }>;
    expect(preCols.some((c) => c.name === "bap_id")).toBe(false);
    manip.close();

    const after = openAndReconcile();

    const postCols = after.prepare(`PRAGMA table_info("user")`).all() as Array<{
      name: string;
    }>;
    expect(postCols.some((c) => c.name === "bap_id")).toBe(true);
    // Baseline hash still present, nothing duplicated.
    expect(getHashes(after).length).toBe(ALL_MIGRATION_TAGS.length);
    after.close();
  });

  test("reconcile_refuses_multi_statement_chunk: synthetic migration with two statements per chunk throws", () => {
    // Build an isolated migrations folder with a malicious-looking chunk.
    const migDir = join(tmpDir, "bad-migrations");
    require("node:fs").mkdirSync(join(migDir, "meta"), { recursive: true });
    require("node:fs").writeFileSync(
      join(migDir, "evil.sql"),
      // Single chunk contains TWO statements — the classifier sees the CREATE
      // INDEX prefix and could skip if the index already exists, silently
      // dropping the trailing DELETE. Reconciler must refuse.
      "CREATE INDEX foo_idx ON foo (bar); DELETE FROM evidence_messages;",
    );
    require("node:fs").writeFileSync(
      join(migDir, "meta", "_journal.json"),
      JSON.stringify({
        version: "7",
        dialect: "sqlite",
        entries: [
          { idx: 0, version: "6", when: 1, tag: "evil", breakpoints: true },
        ],
      }),
    );

    const sqlite = new Database(dbPath, { create: true });
    expect(() => reconcile(sqlite, { folder: migDir })).toThrow(
      /multiple top-level statements/i,
    );
    sqlite.close();
  });

  test("reconcile_accepts_trigger_body_with_inner_semicolons: CREATE TRIGGER BEGIN ...; ...; END is one statement", () => {
    // Triggers legitimately contain semicolons inside their BEGIN/END body.
    // Our semicolon guard must exempt classified triggers so future migrations
    // containing triggers don't get falsely rejected.
    const migDir = join(tmpDir, "trigger-migration");
    require("node:fs").mkdirSync(join(migDir, "meta"), { recursive: true });
    require("node:fs").writeFileSync(
      join(migDir, "trig.sql"),
      "CREATE TABLE t (id integer PRIMARY KEY, n integer DEFAULT 0);" +
        "--> statement-breakpoint\n" +
        `CREATE TRIGGER t_after_insert AFTER INSERT ON t
        BEGIN
          UPDATE t SET n = n + 1 WHERE id = NEW.id;
          UPDATE t SET n = n + 1 WHERE id = NEW.id;
        END;`,
    );
    require("node:fs").writeFileSync(
      join(migDir, "meta", "_journal.json"),
      JSON.stringify({
        version: "7",
        dialect: "sqlite",
        entries: [
          { idx: 0, version: "6", when: 1, tag: "trig", breakpoints: true },
        ],
      }),
    );

    const sqlite = new Database(dbPath, { create: true });
    expect(() => reconcile(sqlite, { folder: migDir })).not.toThrow();
    const trig = sqlite
      .prepare(
        "SELECT 1 FROM sqlite_master WHERE type = 'trigger' AND name = 't_after_insert'",
      )
      .get();
    expect(trig).not.toBeNull();
    sqlite.close();
  });
});