/**
 * Migration reconciler for Scribe DBs.
 *
 * Input: open bun:sqlite handle + path to a drizzle migrations folder.
 * Effect: brings the `__drizzle_migrations` table into a state that is
 *   consistent with the real objects in `sqlite_master` + per-table columns.
 *   Creates objects that are missing, inserts hash rows for migrations whose
 *   effects are already present. Never drops anything. Never destructively
 *   modifies existing rows.
 *
 * Contract: after reconcile returns, Drizzle's own `migrate()` can be
 * safely called and will be a no-op against every journal entry that this
 * reconciler walked. `created_at` values match drizzle-orm's convention
 * (`journalEntry.when`), so Drizzle's `lastDbMigration.created_at <
 * migration.folderMillis` watermark check works correctly for any future
 * migrations that ship after this call.
 */

import type { Database } from "bun:sqlite";
import { createHash } from "node:crypto";
import { readFileSync } from "node:fs";
import { join } from "node:path";
import type { EmbeddedMigrations } from "./migrations-embedded";
import {
  classifyStatement,
  countTopLevelSemicolons,
  parseCreateTableColumns,
  parseIndexTargetTable,
  parseTableRenames,
  parseUniqueIndex,
  splitStatements,
} from "./sql-parse";

/** Optional progress/repair logger. Reconcile is silent by default. */
export type ReconcileLogger = (message: string) => void;

function isUniqueConstraintError(err: unknown): boolean {
  if (
    err &&
    typeof err === "object" &&
    (err as { code?: unknown }).code === "SQLITE_CONSTRAINT_UNIQUE"
  ) {
    return true;
  }
  const msg = err instanceof Error ? err.message : String(err);
  return /UNIQUE constraint failed/i.test(msg);
}

/**
 * A CREATE UNIQUE INDEX failed because the table holds rows that violate it —
 * a drifted DB that accumulated duplicates under an older non-unique index
 * before schema.ts made the index unique (the desktop's evidence_messages is
 * the canonical case). Delete the offending duplicates, keeping the lowest
 * rowid in each group. NULLs are left untouched: SQLite treats every NULL as
 * distinct in a unique index, so any-NULL combinations never conflict. Returns
 * the number of rows removed.
 */
function dedupeForUniqueIndex(
  sqlite: Database,
  table: string,
  columns: string[],
): number {
  const cols = columns.map((c) => `"${c}"`);
  const notNull = cols.map((c) => `${c} IS NOT NULL`).join(" AND ");
  const group = cols.join(", ");
  const countRows = () =>
    (
      sqlite.prepare(`SELECT COUNT(*) AS c FROM "${table}"`).get() as {
        c: number;
      }
    ).c;
  const before = countRows();
  sqlite.exec(
    `DELETE FROM "${table}" WHERE ${notNull} AND rowid NOT IN (` +
      `SELECT MIN(rowid) FROM "${table}" WHERE ${notNull} GROUP BY ${group})`,
  );
  return before - countRows();
}

type JournalEntry = {
  idx: number;
  version: string;
  when: number;
  tag: string;
  breakpoints: boolean;
};

type Journal = {
  version: string;
  dialect: string;
  entries: JournalEntry[];
};

/**
 * Either a filesystem path (for callers that ship the drizzle/ folder with
 * their app bundle — e.g. the desktop app) or a pre-embedded migration set
 * (for callers that can't rely on fs paths — e.g. the published CLI).
 *
 * Both surface the same journal + per-file SQL to the reconciler; the only
 * difference is whether we readFile lazily or use already-loaded strings.
 */
export type MigrationSource =
  | { folder: string; embedded?: never }
  | { folder?: never; embedded: EmbeddedMigrations };

type ResolvedMigration = {
  entry: JournalEntry;
  sql: string;
};

// Migrations whose SQL text was corrected after shipping. Maps tag -> sha256
// hashes of superseded text. A DB holding a superseded hash has already applied
// this migration; record the current hash instead of re-running it.
const SUPERSEDED_MIGRATION_HASHES: Record<string, string[]> = {
  "0007_apikey_config_reference": [
    "d2aa42be56bf3b858b15df5f493ed6c54165304ca9358cf690125c400cca7143",
  ],
};

function loadMigrations(source: MigrationSource): ResolvedMigration[] {
  // The discriminator is which property is actually set (the other is `never`
  // per the union). TypeScript needs an explicit truthy check on the value,
  // not just `in`, because the optional-never branch still lists the key.
  if (source.embedded) {
    return source.embedded.entries.map((e) => ({
      entry: {
        idx: e.idx,
        version: e.version,
        when: e.when,
        tag: e.tag,
        breakpoints: e.breakpoints,
      },
      sql: e.sql,
    }));
  }
  const folder = source.folder;
  if (!folder) {
    throw new Error(
      "reconcile(): source must specify either { folder } or { embedded }",
    );
  }
  const journalPath = join(folder, "meta", "_journal.json");
  const journal: Journal = JSON.parse(readFileSync(journalPath, "utf8"));
  return journal.entries.map((entry) => ({
    entry,
    sql: readFileSync(join(folder, `${entry.tag}.sql`), "utf8"),
  }));
}

function ensureMigrationsTable(sqlite: Database): void {
  // Match drizzle-orm's own CREATE TABLE for __drizzle_migrations.
  // drizzle-orm writes `id SERIAL PRIMARY KEY` — SQLite does not recognize
  // SERIAL as a real type; it falls through to NUMERIC affinity with no
  // autoincrement behavior. That happens to work for drizzle because the
  // migrator only selects `hash` and `created_at` and never references `id`.
  // We use `INTEGER PRIMARY KEY AUTOINCREMENT` here so rows get genuine
  // monotonic ids; this is backward-compatible with DBs drizzle created
  // under the old shape because drizzle's select ignores `id`.
  sqlite.exec(
    "CREATE TABLE IF NOT EXISTS __drizzle_migrations (id INTEGER PRIMARY KEY AUTOINCREMENT, hash TEXT NOT NULL, created_at NUMERIC)",
  );
}

type HashRow = { created_at: number | string | null };

function getHashRow(sqlite: Database, hash: string): HashRow | null {
  return sqlite
    .prepare(
      "SELECT created_at FROM __drizzle_migrations WHERE hash = ? LIMIT 1",
    )
    .get(hash) as HashRow | null;
}

function updateHashCreatedAt(
  sqlite: Database,
  hash: string,
  createdAt: number,
): void {
  sqlite
    .prepare("UPDATE __drizzle_migrations SET created_at = ? WHERE hash = ?")
    .run(createdAt, hash);
}

function updateHash(sqlite: Database, oldHash: string, newHash: string): void {
  sqlite
    .prepare("UPDATE __drizzle_migrations SET hash = ? WHERE hash = ?")
    .run(newHash, oldHash);
}

function objectExists(
  sqlite: Database,
  kind: "table" | "index" | "trigger" | "virtual",
  name: string,
): boolean {
  // Virtual tables live in sqlite_master with type='table' just like regular
  // tables; classify only needs to tell us which CREATE form to skip.
  const masterType = kind === "virtual" ? "table" : kind;
  return (
    sqlite
      .prepare("SELECT 1 FROM sqlite_master WHERE type = ? AND name = ?")
      .get(masterType, name) !== null
  );
}

function tableColumns(sqlite: Database, table: string): Set<string> {
  const rows = sqlite.prepare(`PRAGMA table_info("${table}")`).all() as Array<{
    name: string;
  }>;
  return new Set(rows.map((r) => r.name));
}

function columnExists(
  sqlite: Database,
  table: string,
  column: string,
): boolean {
  return tableColumns(sqlite, table).has(column);
}

/**
 * When a CREATE TABLE is skipped because the table already exists, parse the
 * column list from the migration and add any missing columns via ALTER TABLE
 * ADD COLUMN. Catches legacy-schema drift where a table was created by an
 * earlier code path with fewer columns than the current migration declares
 * (e.g. pre-backfill DB missing `user.bap_id`).
 *
 * Throws with a specific message if ALTER TABLE ADD COLUMN fails; common
 * causes include the missing column being PRIMARY KEY / UNIQUE / NOT NULL
 * without a default, which SQLite cannot add post-hoc. In that case the user
 * must manually reconcile the legacy DB — silently proceeding would leave
 * the schema in a corrupt state.
 */
function reconcileTableColumns(
  sqlite: Database,
  table: string,
  createTableSql: string,
  tag: string,
): void {
  const expected = parseCreateTableColumns(createTableSql);
  if (expected.length === 0) return;

  const existing = tableColumns(sqlite, table);
  for (const col of expected) {
    if (existing.has(col.name)) continue;
    try {
      sqlite.exec(`ALTER TABLE "${table}" ADD COLUMN ${col.def}`);
    } catch (err) {
      const msg = err instanceof Error ? err.message : String(err);
      throw new Error(
        `Cannot reconcile legacy schema in ${tag}: table "${table}" is missing column "${col.name}" ` +
          `and ALTER TABLE ADD COLUMN failed (${msg}). ` +
          `This usually means the column requires PRIMARY KEY / UNIQUE / NOT NULL without a default, ` +
          `which SQLite cannot add to an existing table. Resolve manually (drop the table and let reconcile rebuild, or recreate via a CLI migration).`,
      );
    }
  }
}

/**
 * Apply a single migration file against the DB, skipping any statement
 * whose effect is already present. Wrapped in a SAVEPOINT so partial
 * failures leave the DB unchanged.
 */
function applyPendingMigration(
  sqlite: Database,
  entry: JournalEntry,
  fileContents: string,
  hash: string,
  recreationIdx: Map<string, number>,
  log: ReconcileLogger,
): void {
  // A table is "recreated later" (relative to this migration) when a migration
  // with a higher index rebuilds it via the __new_<table> RENAME pattern. Any
  // CREATE TABLE / CREATE INDEX for such a table in THIS (earlier) migration is
  // stale — it references the pre-recreation column set. Re-running it against
  // a DB that already reached the recreated shape (e.g. a pre-squash legacy DB
  // whose hashes were wiped) would try to re-add a dropped column or index a
  // column that no longer exists. Skip those; the recreation migration (which
  // reconcile also walks) establishes the final columns and indexes.
  const recreatedLater = (table: string): boolean => {
    const idx = recreationIdx.get(table);
    return idx !== undefined && entry.idx < idx;
  };

  const statements = splitStatements(fileContents);
  const savepoint = `reconcile_mig_${entry.idx}`;
  sqlite.exec(`SAVEPOINT ${savepoint}`);
  try {
    for (const raw of statements) {
      const stmt = raw.trim();
      if (stmt.length === 0) continue;
      const classification = classifyStatement(stmt);

      // Safety check: the classifier only inspects the statement prefix.
      // A chunk containing more than one top-level statement (outside strings
      // and parens) could let an untrusted trailing statement ride through
      // on the classifier's verdict. Refuse to process such chunks — our own
      // generator emits exactly one statement per breakpoint-delimited chunk.
      //
      // Triggers are exempt: a valid `CREATE TRIGGER ... BEGIN ...; ...; END;`
      // legitimately contains multiple semicolons inside its BEGIN/END body
      // (each inner statement is a trigger action, not a top-level statement).
      // The classifier has already identified this as a trigger, and SQLite's
      // own parser will reject malformed trigger bodies — we don't need to
      // police them here.
      if (classification.kind !== "trigger") {
        const semis = countTopLevelSemicolons(stmt);
        if (semis > 1) {
          throw new Error(
            `Migration ${entry.tag} contains a chunk with multiple top-level statements (${semis} semicolons). ` +
              `drizzle-kit output must have one statement per \`--> statement-breakpoint\` chunk. ` +
              `Audit the migration file for manual edits.`,
          );
        }
      }

      let skip = false;
      if (
        classification.kind === "table" ||
        classification.kind === "index" ||
        classification.kind === "trigger" ||
        classification.kind === "virtual"
      ) {
        skip = objectExists(sqlite, classification.kind, classification.name);
      } else if (classification.kind === "column") {
        skip = columnExists(sqlite, classification.table, classification.name);
      }

      // A missing index on a table that a later migration recreates is stale:
      // the recreation dropped it (and possibly the column it references). Don't
      // resurrect it here — the recreation migration re-creates the final index
      // set. (An index that still exists is left alone by the skip above.)
      if (
        !skip &&
        classification.kind === "index" &&
        recreatedLater(parseIndexTargetTable(stmt) ?? "")
      ) {
        continue;
      }

      if (skip && classification.kind === "table") {
        // Legacy-drift repair: the table exists, but may be missing columns
        // this migration expects. Add any missing ones via ALTER TABLE — unless
        // a later migration recreates this table, in which case this CREATE
        // TABLE's column list is stale and the recreation governs the final
        // shape.
        if (!recreatedLater(classification.name)) {
          reconcileTableColumns(sqlite, classification.name, stmt, entry.tag);
        }
      }

      if (!skip) {
        try {
          sqlite.exec(stmt);
        } catch (err) {
          // A CREATE UNIQUE INDEX can fail on a drifted DB that accumulated
          // duplicate rows under an older non-unique index. Dedup the offending
          // rows and retry once — still inside this migration's SAVEPOINT, so a
          // later failure rolls the whole thing back atomically. Any other
          // failure propagates (unchanged rollback behavior).
          const uniq = isUniqueConstraintError(err)
            ? parseUniqueIndex(stmt)
            : null;
          if (!uniq) throw err;
          const removed = dedupeForUniqueIndex(
            sqlite,
            uniq.table,
            uniq.columns,
          );
          log(
            `reconcile ${entry.tag}: removed ${removed} duplicate row(s) from ` +
              `"${uniq.table}" to satisfy unique index on (${uniq.columns.join(", ")})`,
          );
          sqlite.exec(stmt);
        }
      }
    }

    sqlite
      .prepare(
        "INSERT INTO __drizzle_migrations (hash, created_at) VALUES (?, ?)",
      )
      .run(hash, entry.when);

    sqlite.exec(`RELEASE SAVEPOINT ${savepoint}`);
  } catch (err) {
    sqlite.exec(`ROLLBACK TO SAVEPOINT ${savepoint}`);
    sqlite.exec(`RELEASE SAVEPOINT ${savepoint}`);
    throw err;
  }
}

/**
 * For a migration whose hash is already recorded (meaning reconcile/drizzle
 * has previously applied it), walk each `CREATE TABLE` chunk and ensure the
 * live table still has every column the migration declared. Catches the case
 * where the hash row is correct but the actual schema drifted — e.g. a legacy
 * DB was marked-as-applied by an older code path that hand-created the table
 * with an earlier column set, then the migration file's declared column list
 * evolved in a later commit.
 *
 * This is the `continue`-path complement to the missing-hash drift repair
 * already performed inside `applyPendingMigration`.
 */
function repairExistingTableColumns(
  sqlite: Database,
  entry: JournalEntry,
  fileContents: string,
  recreationIdx: Map<string, number>,
): void {
  const tag = entry.tag;
  // Within THIS file, `__new_<table>` -> `<table>` renames (drizzle-kit's
  // table-recreation pattern). A CREATE TABLE `__new_x` builds a temp that this
  // migration renames to `x`, so its columns must be verified against the live
  // final table `x`, not the temp name (which no longer exists post-apply).
  const renames = parseTableRenames(fileContents);
  for (const raw of splitStatements(fileContents)) {
    const stmt = raw.trim();
    if (stmt.length === 0) continue;
    const cls = classifyStatement(stmt);
    if (cls.kind !== "table") continue;

    // Historical CREATE TABLE for a table a LATER migration recreates: its
    // column list is stale (the recreation may drop columns). Skip — the
    // recreation migration's own repair pass verifies the final shape.
    const laterIdx = recreationIdx.get(cls.name);
    if (laterIdx !== undefined && entry.idx < laterIdx) continue;

    // This migration's own recreation: a CREATE TABLE `__new_x` renamed to `x`.
    // Verify the live final table, which is what the rename produced.
    const target = renames.get(cls.name) ?? cls.name;

    if (!objectExists(sqlite, "table", target)) {
      // Hash says applied but the table doesn't exist. This is broken state
      // that auto-repair shouldn't silently paper over — surface it so the
      // user can investigate.
      throw new Error(
        `Reconcile: ${tag} hash is recorded but table "${target}" is missing. ` +
          `DB is in an inconsistent state. Resolve manually by removing the hash ` +
          `row and letting reconcile re-apply, or by restoring from backup.`,
      );
    }
    reconcileTableColumns(sqlite, target, stmt, tag);
  }
}

export function reconcile(
  sqlite: Database,
  source: MigrationSource,
  log: ReconcileLogger = () => {},
): void {
  ensureMigrationsTable(sqlite);

  const migrations = loadMigrations(source);

  // Map each recreated table (a `__new_<table>` RENAME target) to the index of
  // the migration that recreates it. Migrations before that index declare a
  // stale shape for the table; reconcile must not re-apply their table/index
  // statements against a DB that already reached the recreated shape.
  const recreationIdx = new Map<string, number>();
  for (const { entry, sql } of migrations) {
    for (const [, to] of parseTableRenames(sql)) {
      recreationIdx.set(to, entry.idx);
    }
  }

  for (const { entry, sql } of migrations) {
    const hash = createHash("sha256").update(sql).digest("hex");

    const existing = getHashRow(sqlite, hash);
    if (existing !== null) {
      // Hash is recorded. But if its `created_at` doesn't match the journal's
      // `when`, drizzle's subsequent `migrate()` watermark check could decide
      // to re-run this migration, which would crash on pre-existing objects.
      // Repair the watermark in place — never remove the row, never re-run
      // the migration body.
      const currentCreatedAt = Number(existing.created_at ?? 0);
      if (currentCreatedAt !== entry.when) {
        updateHashCreatedAt(sqlite, hash, entry.when);
      }
      // Also repair column drift on already-applied migrations: the hash row
      // being present doesn't guarantee the live table matches the declared
      // column list (happens with legacy DBs that were manually marked
      // applied).
      repairExistingTableColumns(sqlite, entry, sql, recreationIdx);
      continue;
    }

    let aliasedSupersededHash = false;
    for (const supersededHash of SUPERSEDED_MIGRATION_HASHES[entry.tag] ?? []) {
      const superseded = getHashRow(sqlite, supersededHash);
      if (superseded !== null) {
        updateHash(sqlite, supersededHash, hash);
        aliasedSupersededHash = true;
        break;
      }
    }
    if (aliasedSupersededHash) continue;

    applyPendingMigration(sqlite, entry, sql, hash, recreationIdx, log);
  }
}