import { describe, expect, test } from "bun:test";
import { readFileSync } from "node:fs";
import { join } from "node:path";
import {
  classifyStatement,
  countTopLevelSemicolons,
  parseCreateTableColumns,
  splitStatements,
} from "../sql-parse";

// The re-baselined desktop folder holds a single additive 0000_baseline.sql.
const BASELINE_DIR = join(
  import.meta.dir,
  "..",
  "..",
  "..",
  "..",
  "..",
  "apps",
  "desktop",
  "drizzle",
);

// The preserved pre-squash 11-migration history. These are real drizzle-kit
// outputs (table recreations, ALTER ADD COLUMN, multi-column CREATE TABLE) and
// remain the richest fixtures for exercising the SQL parser.
const MIGRATIONS_DIR = join(import.meta.dir, "legacy-pre-squash");

describe("classifyStatement", () => {
  test("classifies CREATE TABLE", () => {
    expect(
      classifyStatement("CREATE TABLE `live_activity_sessions` (id text)"),
    ).toEqual({
      kind: "table",
      name: "live_activity_sessions",
    });
  });

  test("classifies CREATE TABLE IF NOT EXISTS", () => {
    expect(
      classifyStatement("CREATE TABLE IF NOT EXISTS `subscription` (id text)"),
    ).toEqual({ kind: "table", name: "subscription" });
  });

  test("classifies CREATE TABLE without backticks", () => {
    expect(classifyStatement("CREATE TABLE users (id text)")).toEqual({
      kind: "table",
      name: "users",
    });
  });

  test("classifies CREATE INDEX", () => {
    expect(
      classifyStatement(
        "CREATE INDEX `live_activity_state_idx` ON `live_activity_sessions` (`state`)",
      ),
    ).toEqual({ kind: "index", name: "live_activity_state_idx" });
  });

  test("classifies CREATE UNIQUE INDEX", () => {
    expect(
      classifyStatement(
        "CREATE UNIQUE INDEX `rollups_upsert_uidx` ON `timeline_rollups` (`organization_id`)",
      ),
    ).toEqual({ kind: "index", name: "rollups_upsert_uidx" });
  });

  test("classifies CREATE INDEX IF NOT EXISTS", () => {
    expect(
      classifyStatement(
        "CREATE INDEX IF NOT EXISTS `foo_idx` ON `foo` (`bar`)",
      ),
    ).toEqual({ kind: "index", name: "foo_idx" });
  });

  test("classifies CREATE VIRTUAL TABLE", () => {
    expect(
      classifyStatement(
        "CREATE VIRTUAL TABLE IF NOT EXISTS timeline_events_fts USING fts5(content)",
      ),
    ).toEqual({ kind: "virtual", name: "timeline_events_fts" });
  });

  test("classifies CREATE TRIGGER", () => {
    expect(
      classifyStatement(
        "CREATE TRIGGER IF NOT EXISTS timeline_events_ai AFTER INSERT ON timeline_events BEGIN END",
      ),
    ).toEqual({ kind: "trigger", name: "timeline_events_ai" });
  });

  test("classifies ALTER TABLE ADD COLUMN", () => {
    expect(
      classifyStatement(
        "ALTER TABLE `evidence_threads` ADD `share_token` text",
      ),
    ).toEqual({
      kind: "column",
      table: "evidence_threads",
      name: "share_token",
    });
  });

  test("classifies ALTER TABLE ADD COLUMN with COLUMN keyword", () => {
    expect(
      classifyStatement("ALTER TABLE `foo` ADD COLUMN `bar` integer DEFAULT 0"),
    ).toEqual({ kind: "column", table: "foo", name: "bar" });
  });

  test("returns unknown for unrecognized statement", () => {
    expect(classifyStatement("PRAGMA foreign_keys=OFF").kind).toBe("unknown");
    expect(classifyStatement("-- comment only").kind).toBe("unknown");
    expect(classifyStatement("").kind).toBe("unknown");
  });

  test("handles multi-line CREATE TABLE with leading whitespace", () => {
    const sql = `\n\tCREATE TABLE \`live_activity_sessions\` (\n\t\t\`id\` text PRIMARY KEY NOT NULL\n\t)`;
    expect(classifyStatement(sql)).toEqual({
      kind: "table",
      name: "live_activity_sessions",
    });
  });
});

describe("splitStatements", () => {
  test("splits on --> statement-breakpoint", () => {
    const input =
      "CREATE TABLE a (x text);--> statement-breakpoint\nCREATE TABLE b (y text);";
    expect(splitStatements(input)).toEqual([
      "CREATE TABLE a (x text);",
      "\nCREATE TABLE b (y text);",
    ]);
  });

  test("returns single statement when no breakpoint present", () => {
    expect(splitStatements("CREATE TABLE a (x text)")).toEqual([
      "CREATE TABLE a (x text)",
    ]);
  });

  test("preserves empty statements (caller filters)", () => {
    const input = "A\n--> statement-breakpoint\n\n--> statement-breakpoint\nB";
    expect(splitStatements(input).length).toBe(3);
  });
});

describe("countTopLevelSemicolons", () => {
  test("zero semicolons when unterminated", () => {
    expect(countTopLevelSemicolons("CREATE TABLE a (id text)")).toBe(0);
  });

  test("one trailing semicolon", () => {
    expect(countTopLevelSemicolons("CREATE TABLE a (id text);")).toBe(1);
  });

  test("ignores semicolons inside parens", () => {
    // Synthetic: SQL rarely has ; inside parens (triggers use BEGIN/END, not
    // parens), but the counter should ignore paren-depth semicolons regardless.
    expect(countTopLevelSemicolons("CREATE TABLE t (id;integer)")).toBe(0);
  });

  test("ignores semicolons inside single-quoted strings", () => {
    expect(
      countTopLevelSemicolons("INSERT INTO a VALUES ('hello; world');"),
    ).toBe(1);
  });

  test("ignores semicolons inside double-quoted identifiers", () => {
    expect(countTopLevelSemicolons(`CREATE TABLE "foo;bar" (id text);`)).toBe(
      1,
    );
  });

  test("ignores semicolons inside backtick identifiers", () => {
    expect(countTopLevelSemicolons("CREATE TABLE `foo;bar` (id text);")).toBe(
      1,
    );
  });

  test("counts two top-level statements separated by semicolon", () => {
    expect(
      countTopLevelSemicolons(
        "CREATE INDEX foo ON t(c); DELETE FROM timeline_events;",
      ),
    ).toBe(2);
  });

  test("counts three top-level statements", () => {
    expect(countTopLevelSemicolons("A;B;C;")).toBe(3);
  });

  test("handles escaped string quotes via doubling", () => {
    // SQL standard: '' inside a string represents a single quote
    expect(countTopLevelSemicolons("INSERT INTO a VALUES ('it''s');")).toBe(1);
  });
});

describe("parseCreateTableColumns", () => {
  test("extracts simple columns with backtick-quoted names", () => {
    const sql = `CREATE TABLE \`user\` (
      \`id\` text PRIMARY KEY NOT NULL,
      \`bap_id\` text,
      \`tour_completed\` integer DEFAULT false
    )`;
    const cols = parseCreateTableColumns(sql);
    expect(cols.map((c) => c.name)).toEqual(["id", "bap_id", "tour_completed"]);
    expect(cols[1]?.def.trim()).toBe("`bap_id` text");
    expect(cols[2]?.def.trim()).toBe("`tour_completed` integer DEFAULT false");
  });

  test("extracts columns without backticks", () => {
    const sql = `CREATE TABLE users (
      id text,
      name text
    )`;
    const cols = parseCreateTableColumns(sql);
    expect(cols.map((c) => c.name)).toEqual(["id", "name"]);
  });

  test("skips table-level FOREIGN KEY / PRIMARY KEY / UNIQUE / CHECK / CONSTRAINT clauses", () => {
    const sql = `CREATE TABLE \`live_activity_sessions\` (
      \`id\` text PRIMARY KEY NOT NULL,
      \`project_id\` text,
      FOREIGN KEY (\`project_id\`) REFERENCES \`projects\`(\`id\`) ON UPDATE no action ON DELETE set null,
      UNIQUE (\`id\`)
    )`;
    const cols = parseCreateTableColumns(sql);
    expect(cols.map((c) => c.name)).toEqual(["id", "project_id"]);
  });

  test("handles columns with nested parens in DEFAULT", () => {
    const sql = `CREATE TABLE foo (
      \`created_at\` integer DEFAULT (unixepoch()),
      \`updated_at\` integer DEFAULT (cast(unixepoch('subsecond') * 1000 as integer))
    )`;
    const cols = parseCreateTableColumns(sql);
    expect(cols.map((c) => c.name)).toEqual(["created_at", "updated_at"]);
    expect(cols[0]?.def.trim()).toBe(
      "`created_at` integer DEFAULT (unixepoch())",
    );
  });

  test("handles CREATE TABLE IF NOT EXISTS", () => {
    const sql = `CREATE TABLE IF NOT EXISTS foo (
      id text
    )`;
    const cols = parseCreateTableColumns(sql);
    expect(cols.map((c) => c.name)).toEqual(["id"]);
  });

  test("returns empty array if not a CREATE TABLE", () => {
    expect(parseCreateTableColumns("CREATE INDEX foo ON bar (c)")).toEqual([]);
    expect(parseCreateTableColumns("")).toEqual([]);
  });

  test("extracts every column from a real shipped migration", () => {
    const sql = readFileSync(
      join(MIGRATIONS_DIR, "0003_zippy_nehzno.sql"),
      "utf8",
    );
    const statements = splitStatements(sql).map((s) => s.trim());
    const liveActivity = statements.find((s) =>
      s.startsWith("CREATE TABLE `live_activity_sessions`"),
    );
    if (!liveActivity)
      throw new Error("expected live_activity_sessions CREATE TABLE in 0003");
    const cols = parseCreateTableColumns(liveActivity);
    // 17 columns per the SQL file.
    expect(cols.map((c) => c.name)).toEqual([
      "id",
      "provider_id",
      "external_thread_id",
      "title",
      "project_root_id",
      "project_id",
      "project_name",
      "project_path",
      "conversation_file",
      "last_message_preview",
      "last_message_role",
      "message_count",
      "last_activity_at",
      "last_ingest_at",
      "state",
      "created_at",
      "updated_at",
    ]);
  });
});

describe("classify every statement in shipped migrations 0000-0003", () => {
  const cases: Array<[string, number]> = [
    ["0000_tricky_squadron_sinister", 89],
    ["0001_unusual_yellowjacket", 6],
    ["0002_keen_black_panther", 5],
    ["0003_zippy_nehzno", 9],
  ];

  for (const [tag, expectedBreakpoints] of cases) {
    test(`${tag} statements all classify to known kinds`, () => {
      const sql = readFileSync(join(MIGRATIONS_DIR, `${tag}.sql`), "utf8");
      const statements = splitStatements(sql);
      expect(statements.length).toBe(expectedBreakpoints + 1);
      const unknowns: string[] = [];
      for (const stmt of statements) {
        const trimmed = stmt.trim();
        if (trimmed.length === 0) continue;
        const result = classifyStatement(trimmed);
        if (result.kind === "unknown") {
          unknowns.push(trimmed.slice(0, 80));
        } else {
          expect(result.name).toBeDefined();
        }
      }
      expect(unknowns).toEqual([]);
    });

    test(`${tag} no chunk has multiple top-level statements`, () => {
      const sql = readFileSync(join(MIGRATIONS_DIR, `${tag}.sql`), "utf8");
      for (const stmt of splitStatements(sql)) {
        const trimmed = stmt.trim();
        if (trimmed.length === 0) continue;
        // Shipped migrations always have one statement per chunk. More
        // than one top-level semicolon is a red flag.
        expect(countTopLevelSemicolons(trimmed)).toBeLessThanOrEqual(1);
      }
    });
  }
});

describe("classify every statement in the current baseline migration", () => {
  test("0000_baseline: all statements classify to table/index, one per chunk", () => {
    const sql = readFileSync(join(BASELINE_DIR, "0000_baseline.sql"), "utf8");
    const statements = splitStatements(sql);
    // 48 CREATE TABLE + 114 CREATE INDEX = 162 statements (161 breakpoints).
    const nonEmpty = statements
      .map((s) => s.trim())
      .filter((s) => s.length > 0);
    expect(nonEmpty.length).toBe(162);

    let tables = 0;
    let indexes = 0;
    const unknowns: string[] = [];
    for (const stmt of nonEmpty) {
      // The parser must handle every chunk of the real current shipped file.
      expect(countTopLevelSemicolons(stmt)).toBeLessThanOrEqual(1);
      const result = classifyStatement(stmt);
      if (result.kind === "table") tables++;
      else if (result.kind === "index") indexes++;
      else unknowns.push(stmt.slice(0, 80));
    }
    expect(unknowns).toEqual([]);
    expect(tables).toBe(48);
    expect(indexes).toBe(114);
  });

  test("parseCreateTableColumns extracts columns from the baseline user table", () => {
    const sql = readFileSync(join(BASELINE_DIR, "0000_baseline.sql"), "utf8");
    const userCreate = splitStatements(sql)
      .map((s) => s.trim())
      .find((s) => s.startsWith("CREATE TABLE `user`"));
    if (!userCreate)
      throw new Error("expected CREATE TABLE `user` in 0000_baseline");
    const cols = parseCreateTableColumns(userCreate).map((c) => c.name);
    // Spot-check columns that later legacy migrations added to `user`.
    for (const c of ["id", "bap_id", "pubkey", "roles", "ban_expires"]) {
      expect(cols).toContain(c);
    }
  });
});