import { Database } from "bun:sqlite";
import { afterEach, beforeEach, describe, expect, test } from "bun:test";
import { mkdirSync, mkdtempSync, rmSync, writeFileSync } from "node:fs";
import { tmpdir } from "node:os";
import { join } from "node:path";
import {
  BASELINE_TAG,
  createAirellState,
  createFresh,
  createHealthyUpToDate,
  hashMigration,
  MIGRATIONS_FOLDER,
} from "../fixtures";
import { initScribeDb } from "../init";

let tmpDir: string;
let dbPath: string;

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

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

function schemaSnapshot(sqlite: Database): string {
  const rows = sqlite
    .prepare(
      "SELECT type, name, tbl_name, sql FROM sqlite_master " +
        "WHERE name NOT LIKE 'sqlite_%' ORDER BY type, name",
    )
    .all();
  return JSON.stringify(rows, null, 2);
}

describe("initScribeDb", () => {
  test("init_fresh_produces_same_schema_as_init_then_init: double-call idempotence on fresh DB", () => {
    createFresh(dbPath);
    const handle1 = initScribeDb({
      dbPath,
      migrationsFolder: MIGRATIONS_FOLDER,
    });
    const snap1 = schemaSnapshot(handle1.sqlite);
    handle1.sqlite.close();

    const handle2 = initScribeDb({
      dbPath,
      migrationsFolder: MIGRATIONS_FOLDER,
    });
    const snap2 = schemaSnapshot(handle2.sqlite);
    handle2.sqlite.close();

    expect(snap2).toBe(snap1);
  });

  test("init_airell_fixture_then_insert_into_timeline_rollups_succeeds: the actual bug fix", () => {
    createAirellState(dbPath);

    const handle = initScribeDb({
      dbPath,
      migrationsFolder: MIGRATIONS_FOLDER,
    });

    // This insert would throw "no such table: timeline_rollups" on the
    // broken pre-fix code. After initScribeDb, it must succeed.
    handle.sqlite
      .prepare(
        `INSERT INTO timeline_rollups
         (id, organization_id, granularity, bucket_start, bucket_end,
          project_id, project_name, project_color, event_count,
          estimated_minutes, source_counts, updated_at)
         VALUES (?, ?, 'day', 0, 86400, '__unassigned__',
                 'Unassigned', 'oklch(0.55 0.02 260)',
                 0, 0, '{}', unixepoch())`,
      )
      .run("test-row-1", "org-test");

    const count = (
      handle.sqlite
        .prepare("SELECT COUNT(*) AS c FROM timeline_rollups")
        .get() as { c: number }
    ).c;
    expect(count).toBe(1);

    // The baseline migration hash must be recorded after init.
    const hashes = (
      handle.sqlite
        .prepare("SELECT hash FROM __drizzle_migrations")
        .all() as Array<{ hash: string }>
    ).map((r) => r.hash);
    expect(hashes).toContain(hashMigration(BASELINE_TAG));

    handle.sqlite.close();
  });

  test("init_is_idempotent_across_10_calls", () => {
    createFresh(dbPath);

    let prevSnap: string | null = null;
    for (let i = 0; i < 10; i++) {
      const handle = initScribeDb({
        dbPath,
        migrationsFolder: MIGRATIONS_FOLDER,
      });
      const snap = schemaSnapshot(handle.sqlite);
      if (prevSnap !== null) expect(snap).toBe(prevSnap);
      prevSnap = snap;
      handle.sqlite.close();
    }
  });

  test("init_installed_on_healthy_up_to_date_does_not_drop_or_duplicate: post-init snapshot is stable across reopen", () => {
    // healthyUpToDate applies drizzle migrations but not FTS5; init adds
    // FTS5, so the first init *will* modify the schema. The invariant we
    // care about is that running init a second time changes nothing.
    createHealthyUpToDate(dbPath);
    const first = initScribeDb({
      dbPath,
      migrationsFolder: MIGRATIONS_FOLDER,
    });
    const snapFirst = schemaSnapshot(first.sqlite);
    first.sqlite.close();

    const second = initScribeDb({
      dbPath,
      migrationsFolder: MIGRATIONS_FOLDER,
    });
    const snapSecond = schemaSnapshot(second.sqlite);
    second.sqlite.close();

    expect(snapSecond).toBe(snapFirst);
  });

  test("init_installs_fts5_on_fresh_db: virtual table + triggers present", () => {
    createFresh(dbPath);
    const handle = initScribeDb({
      dbPath,
      migrationsFolder: MIGRATIONS_FOLDER,
    });

    const ftsTable = handle.sqlite
      .prepare(
        "SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'timeline_events_fts'",
      )
      .get();
    const insertTrigger = handle.sqlite
      .prepare(
        "SELECT name FROM sqlite_master WHERE type = 'trigger' AND name = 'timeline_events_ai'",
      )
      .get();

    expect(ftsTable).not.toBeNull();
    expect(insertTrigger).not.toBeNull();
    handle.sqlite.close();
  });

  test("init_rolls_back_per_file_on_reconcile_throw: per-file SAVEPOINT isolates failures", () => {
    // Build an isolated migrations folder whose second file has bogus SQL.
    // Reconcile should apply file 1, fail on file 2, leave file 2 unrecorded,
    // and not corrupt the DB with partial file-2 state.
    const migDir = join(tmpDir, "bad-migrations");
    mkdirSync(join(migDir, "meta"), { recursive: true });
    writeFileSync(
      join(migDir, "a.sql"),
      "CREATE TABLE good (id text PRIMARY KEY);",
    );
    writeFileSync(
      join(migDir, "b.sql"),
      "CREATE TABLE before_bad (id text);--> statement-breakpoint\n" +
        "NOT A REAL SQL STATEMENT;",
    );
    writeFileSync(
      join(migDir, "meta", "_journal.json"),
      JSON.stringify({
        version: "7",
        dialect: "sqlite",
        entries: [
          {
            idx: 0,
            version: "6",
            when: 1000,
            tag: "a",
            breakpoints: true,
          },
          {
            idx: 1,
            version: "6",
            when: 2000,
            tag: "b",
            breakpoints: true,
          },
        ],
      }),
    );

    expect(() => initScribeDb({ dbPath, migrationsFolder: migDir })).toThrow();

    const sqlite = new Database(dbPath, { readonly: true });
    // File `a` succeeded.
    const good = sqlite
      .prepare(
        "SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'good'",
      )
      .get();
    expect(good).not.toBeNull();

    // File `b`'s first statement created `before_bad` but the SAVEPOINT must
    // have rolled it back when the second statement failed.
    const beforeBad = sqlite
      .prepare(
        "SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'before_bad'",
      )
      .get();
    expect(beforeBad).toBeNull();

    sqlite.close();
  });
});