/**
 * Canonical Scribe DB initialization.
 *
 * Called once per process on the SQLite-backed apps (desktop, CLI). Handles:
 *   - Opening the DB file with the agreed PRAGMAs
 *   - Reconciling the migration table against real schema objects
 *   - Running Drizzle's migrate() as a safety net for migrations that ship
 *     in the future and haven't been reconciled yet
 *   - Installing the FTS5 virtual table + triggers for timeline search
 *
 * Replaces the previous desktop-only stack of `syncMigrationHistory` +
 * `backfillLegacySchema` + `migrate()`, which carried three overlapping
 * schema sources of truth and shipped OPL-1965's crash loop.
 *
 * This module intentionally does not handle:
 *   - DB path resolution (caller chooses where `~/.scribe/scribe.db` lives)
 *   - Drizzle ORM wrapper creation (caller holds the `drizzle()` handle so
 *     it can choose a schema binding)
 *
 * The raw `bun:sqlite` handle is returned so callers can immediately wrap
 * it with `drizzle({ client, schema })` and keep a reference to both.
 */

import { Database } from "bun:sqlite";
import { drizzle } from "drizzle-orm/bun-sqlite";
import { migrate } from "drizzle-orm/bun-sqlite/migrator";
import { backfillProjectRootGitRemotes } from "./backfill-git-remotes";
import type { EmbeddedMigrations } from "./migrations-embedded";
import { deduplicateProjectRootsByPath } from "./project-root-dedup";
import { type MigrationSource, reconcile } from "./reconcile";

/**
 * initScribeDb accepts migrations from either a filesystem folder (desktop
 * app ships drizzle/ in its bundle) or embedded constants (CLI doesn't have
 * a reliable fs path once published to npm). Exactly one must be provided.
 */
export type InitScribeDbOptions = {
  dbPath: string;
  logger?: (msg: string) => void;
} & (
  | { migrationsFolder: string; embeddedMigrations?: never }
  | { migrationsFolder?: never; embeddedMigrations: EmbeddedMigrations }
);

export type InitScribeDbResult = {
  sqlite: Database;
};

const PRAGMAS = `
  PRAGMA journal_mode = WAL;
  PRAGMA synchronous = NORMAL;
  PRAGMA cache_size = -65536;
  PRAGMA temp_store = memory;
  PRAGMA mmap_size = 268435456;
  PRAGMA busy_timeout = 5000;
`;

const FTS5_SETUP_SQL = `
CREATE VIRTUAL TABLE IF NOT EXISTS timeline_events_fts USING fts5(
  content,
  source,
  content='timeline_events',
  content_rowid='rowid',
  tokenize='porter unicode61'
);

CREATE TRIGGER IF NOT EXISTS timeline_events_ai AFTER INSERT ON timeline_events
WHEN NEW.content IS NOT NULL
BEGIN
  INSERT INTO timeline_events_fts(rowid, content, source)
  VALUES (NEW.rowid, NEW.content, NEW.source);
END;

CREATE TRIGGER IF NOT EXISTS timeline_events_ad AFTER DELETE ON timeline_events
WHEN OLD.content IS NOT NULL
BEGIN
  INSERT INTO timeline_events_fts(timeline_events_fts, rowid, content, source)
  VALUES ('delete', OLD.rowid, OLD.content, OLD.source);
END;

CREATE TRIGGER IF NOT EXISTS timeline_events_au AFTER UPDATE ON timeline_events
WHEN OLD.content IS NOT NULL OR NEW.content IS NOT NULL
BEGIN
  INSERT INTO timeline_events_fts(timeline_events_fts, rowid, content, source)
  VALUES ('delete', OLD.rowid, OLD.content, OLD.source);
  INSERT INTO timeline_events_fts(rowid, content, source)
  VALUES (NEW.rowid, NEW.content, NEW.source);
END;
`;

function installFts5(sqlite: Database, log: (msg: string) => void): void {
  try {
    // Upgrade the tokenizer if an older FTS table exists without `porter`.
    try {
      const configRow = sqlite
        .prepare(
          "SELECT v FROM timeline_events_fts_config WHERE k = 'tokenize'",
        )
        .get() as { v: string } | null;
      if (configRow && !configRow.v.includes("porter")) {
        log("FTS5 tokenizer upgrade -> porter unicode61");
        sqlite.exec("DROP TRIGGER IF EXISTS timeline_events_ai");
        sqlite.exec("DROP TRIGGER IF EXISTS timeline_events_ad");
        sqlite.exec("DROP TRIGGER IF EXISTS timeline_events_au");
        sqlite.exec("DROP TABLE IF EXISTS timeline_events_fts");
      }
    } catch {
      // timeline_events_fts_config doesn't exist yet; FTS5_SETUP_SQL will
      // create the whole stack below.
    }

    sqlite.exec(FTS5_SETUP_SQL);

    const eventsCount = sqlite
      .prepare(
        "SELECT COUNT(*) AS c FROM timeline_events WHERE content IS NOT NULL",
      )
      .get() as { c: number };
    const ftsCount = sqlite
      .prepare("SELECT COUNT(*) AS c FROM timeline_events_fts")
      .get() as { c: number };

    if (eventsCount.c !== ftsCount.c) {
      log(`FTS5 out of sync (${ftsCount.c} vs ${eventsCount.c}), rebuilding`);
      sqlite.exec(
        "INSERT INTO timeline_events_fts(timeline_events_fts) VALUES('rebuild')",
      );
    }
  } catch (err) {
    log(
      `FTS5 setup warning: ${err instanceof Error ? err.message : String(err)}`,
    );
  }
}

export function initScribeDb(options: InitScribeDbOptions): InitScribeDbResult {
  const log = options.logger ?? (() => {});

  // PRAGMAs must run outside any transaction; journal_mode=WAL is a no-op
  // if active transaction is present. Apply before reconcile.
  const sqlite = new Database(options.dbPath, { create: true });
  sqlite.exec(PRAGMAS);

  // Reconcile: single source of truth for schema. Brings __drizzle_migrations
  // into agreement with actual sqlite_master + PRAGMA table_info state.
  // Each migration file is wrapped in its own SAVEPOINT, so partial failures
  // roll back just that file.
  const source: MigrationSource = options.embeddedMigrations
    ? { embedded: options.embeddedMigrations }
    : { folder: options.migrationsFolder };
  reconcile(sqlite, source, log);

  // Drizzle migrate() as defense-in-depth. After reconcile, every journal
  // entry has its hash recorded with `created_at = folderMillis`, so
  // migrate()'s `lastDbMigration.created_at < migration.folderMillis`
  // watermark check is false for all entries; migrate() becomes a no-op.
  // Genuinely new migrations that land post-reconcile (e.g. during a hot
  // reload, though unlikely in production) still apply normally. Drizzle's
  // migrator only works against a folder on disk, so skip it when we're
  // running off embedded constants (reconcile is already authoritative).
  if (options.migrationsFolder) {
    const db = drizzle({ client: sqlite });
    migrate(db, { migrationsFolder: options.migrationsFolder });
  }

  const rootDedup = deduplicateProjectRootsByPath(sqlite);
  if (
    rootDedup.groupsMerged > 0 ||
    rootDedup.skippedRemoteConflicts > 0 ||
    rootDedup.skippedProjectConflicts > 0
  ) {
    log(
      `Project root cleanup: merged ${rootDedup.groupsMerged} groups, ` +
        `removed ${rootDedup.rootsRemoved} roots, ` +
        `skipped ${rootDedup.skippedRemoteConflicts} remote conflicts and ` +
        `${rootDedup.skippedProjectConflicts} project conflicts`,
    );
  }

  const remotesNormalized = backfillProjectRootGitRemotes(sqlite);
  if (remotesNormalized > 0) {
    log(`Project root cleanup: normalized ${remotesNormalized} git remotes`);
  }

  installFts5(sqlite, log);

  return { sqlite };
}