// node:sqlite is Node 22.5+'s built-in SQLite binding. Switched off
// better-sqlite3 in v0.21.0 to eliminate the prebuild-install
// deprecation warning users saw on every install, drop the native
// compilation surface (no more node-gyp failures on weird
// toolchains), and remove the platform-specific binary downloads.
// Same sync API, similar performance.
import { DatabaseSync, type StatementSync } from "node:sqlite";

// Type re-exports so the rest of the codebase imports from a single
// place. Previously every store imported `type Database from
// "better-sqlite3"` and used `Database.Database` / `Database.Statement`;
// now they import `Database` / `Statement` from here.
export type Database = DatabaseSync;
export type Statement = StatementSync;

// Current data-layer version. Bump this when existing stored data
// (not just schema shape) has become incompatible with the new code.
// The Indexer runs data migrations on startup when the stored version
// is lower than this.
//
//   1 — initial. Tables as defined in SCHEMA below.
//   2 — fix for github.com/sverklo/sverklo/issues/13: symbol_refs
//       generated by v0.2.13 and earlier used chunk-wide dedupe in
//       extractReferences(), which collapsed every repeat call of a
//       symbol in the same chunk to one row. Upgrading triggers
//       re-extraction of references from existing chunks (no file
//       I/O — just regex over chunk.content).
//   3 — v0.13 adds markdown chunks (doc_section, doc_code) + doc_mentions
//       edges. Existing dbs need a re-index so .md files get parsed and
//       doc->symbol edges are populated. The migration is incremental:
//       the indexer detects pre-v3 dbs and schedules the extra work on
//       the next refresh. No file content is touched.
//   4 — v0.13 P0-3 adds the evidence table. Evidence rows pin retrieval
//       spans to a git SHA + content hash so sverklo_verify can detect
//       stale citations. The table is additive and ephemeral (TTL + LRU
//       capped) — no reindex required.
//   5 — v0.14 P1-7 adds the concepts + concept_embeddings tables for the
//       LLM-labeled cluster layer. The layer is offline (populated by the
//       `sverklo concept-index` CLI), so bumping the version simply
//       declares the schema without forcing any work at indexer startup.
//   6 — v0.15 P1-8 adds the context_handles table. Search-family tools
//       can return ctx:// handles instead of inline text; ctx_slice /
//       ctx_grep / ctx_stats then operate on the handle without rerunning
//       retrieval. Handles are git-SHA-pinned so they auto-invalidate
//       when the working tree changes.
//       v0.15 P1-12 adds chunks.purpose for symbol-purpose enrichment.
//   7 — v0.15 P2-17 adds the pattern_edges table for LLM-derived design-
//       pattern annotations. P2-18 adds memories.trajectory for tool-call
//       provenance. Both are additive; no reindex needed.
//   8 — Sprint 9 (iwe + Akshay-thread research) adds memories.kind, an
//       episodic/semantic/procedural axis orthogonal to tier. Default is
//       'episodic'; auto-derived from category at insert time. Additive,
//       no reindex required.
//       Also adds doc_mentions.edge_kind ('includes' vs 'references') —
//       the iwe inclusion-vs-reference split. Derived from match_kind at
//       insert time (fenced_code → includes, backtick/bare → references).
//   9 — sv-p4-04 fix: FileStore.upsert switched from INSERT OR REPLACE
//       to INSERT…ON CONFLICT DO UPDATE so file_id stays stable across
//       re-index. The old code triggered ON DELETE CASCADE on every
//       row in `dependencies` that referenced the file (as source OR
//       target), and only outgoing edges of the touched file got
//       restored — incoming edges from cached source files were lost.
//       v9's migration reset `files.hash = ''` to force re-parse, but
//       index()'s toIndex check uses `last_modified` (mtime), not hash —
//       so the migration cleared deps but the no-op "Index is up to
//       date" branch fired before any re-parse. DBs that landed on v9
//       are stuck with empty `dependencies` until something on disk
//       changes mtime. v10 bumps the version and resets last_modified
//       too so the rebuild actually happens on next index().
//  10 — Re-run of v9's intent. Same migration body, but also
//       `UPDATE files SET last_modified = 0` so toIndex sees every
//       file as stale and the graph actually rebuilds. v9 is treated
//       as a no-op stepping stone.
export const CURRENT_DATA_VERSION = 10;

const SCHEMA = `
-- Indexed files
CREATE TABLE IF NOT EXISTS files (
  id INTEGER PRIMARY KEY,
  path TEXT NOT NULL UNIQUE,
  language TEXT,
  hash TEXT NOT NULL,
  last_modified INTEGER NOT NULL,
  size_bytes INTEGER NOT NULL,
  pagerank REAL DEFAULT 0.0,
  indexed_at INTEGER NOT NULL
);

-- Code chunks
CREATE TABLE IF NOT EXISTS chunks (
  id INTEGER PRIMARY KEY,
  file_id INTEGER NOT NULL REFERENCES files(id) ON DELETE CASCADE,
  type TEXT NOT NULL,
  name TEXT,
  signature TEXT,
  start_line INTEGER NOT NULL,
  end_line INTEGER NOT NULL,
  content TEXT NOT NULL,
  description TEXT,
  token_count INTEGER NOT NULL
);

-- Vector embeddings
CREATE TABLE IF NOT EXISTS embeddings (
  chunk_id INTEGER PRIMARY KEY REFERENCES chunks(id) ON DELETE CASCADE,
  vector BLOB NOT NULL
);

-- File-level dependency graph
CREATE TABLE IF NOT EXISTS dependencies (
  id INTEGER PRIMARY KEY,
  source_file_id INTEGER NOT NULL REFERENCES files(id) ON DELETE CASCADE,
  target_file_id INTEGER NOT NULL REFERENCES files(id) ON DELETE CASCADE,
  reference_count INTEGER DEFAULT 1,
  UNIQUE(source_file_id, target_file_id)
);

-- Symbol-level reference graph (function/class call relationships)
-- Each row is "source_chunk references target_name" — resolution to a target
-- chunk is done lazily via name matching since we don't have a type-resolver.
CREATE TABLE IF NOT EXISTS symbol_refs (
  id INTEGER PRIMARY KEY,
  source_chunk_id INTEGER NOT NULL REFERENCES chunks(id) ON DELETE CASCADE,
  target_name TEXT NOT NULL,
  line INTEGER,
  UNIQUE(source_chunk_id, target_name, line)
);

-- Full-text search
CREATE VIRTUAL TABLE IF NOT EXISTS chunks_fts USING fts5(
  name,
  content,
  description,
  content=chunks,
  content_rowid=id,
  tokenize='porter unicode61'
);

-- Triggers to keep FTS in sync
CREATE TRIGGER IF NOT EXISTS chunks_ai AFTER INSERT ON chunks BEGIN
  INSERT INTO chunks_fts(rowid, name, content, description)
  VALUES (new.id, new.name, new.content, new.description);
END;

CREATE TRIGGER IF NOT EXISTS chunks_ad AFTER DELETE ON chunks BEGIN
  INSERT INTO chunks_fts(chunks_fts, rowid, name, content, description)
  VALUES ('delete', old.id, old.name, old.content, old.description);
END;

CREATE TRIGGER IF NOT EXISTS chunks_au AFTER UPDATE ON chunks BEGIN
  INSERT INTO chunks_fts(chunks_fts, rowid, name, content, description)
  VALUES ('delete', old.id, old.name, old.content, old.description);
  INSERT INTO chunks_fts(rowid, name, content, description)
  VALUES (new.id, new.name, new.content, new.description);
END;

-- Session memories (bi-temporal: valid_from_sha -> valid_until_sha)
CREATE TABLE IF NOT EXISTS memories (
  id INTEGER PRIMARY KEY,
  category TEXT NOT NULL,
  content TEXT NOT NULL,
  tags TEXT,
  confidence REAL DEFAULT 1.0,
  git_sha TEXT,              -- alias for valid_from_sha (creation time)
  git_branch TEXT,
  related_files TEXT,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  last_accessed INTEGER NOT NULL,
  access_count INTEGER DEFAULT 0,
  is_stale INTEGER DEFAULT 0,
  -- Bi-temporal fields (added in v0.2)
  tier TEXT DEFAULT 'archive',        -- 'core' | 'archive'
  valid_from_sha TEXT,                -- git SHA when memory was created
  valid_until_sha TEXT,               -- git SHA when invalidated (NULL = still valid)
  invalidated_at INTEGER,             -- epoch ms when invalidated
  superseded_by INTEGER,               -- id of memory that replaced this one
  kind TEXT DEFAULT 'episodic'         -- 'episodic' | 'semantic' | 'procedural'
);

CREATE TABLE IF NOT EXISTS memory_embeddings (
  memory_id INTEGER PRIMARY KEY REFERENCES memories(id) ON DELETE CASCADE,
  vector BLOB NOT NULL
);

CREATE VIRTUAL TABLE IF NOT EXISTS memories_fts USING fts5(
  content,
  tags,
  content=memories,
  content_rowid=id,
  tokenize='porter unicode61'
);

CREATE TRIGGER IF NOT EXISTS memories_ai AFTER INSERT ON memories BEGIN
  INSERT INTO memories_fts(rowid, content, tags)
  VALUES (new.id, new.content, new.tags);
END;

CREATE TRIGGER IF NOT EXISTS memories_ad AFTER DELETE ON memories BEGIN
  INSERT INTO memories_fts(memories_fts, rowid, content, tags)
  VALUES ('delete', old.id, old.content, old.tags);
END;

CREATE TRIGGER IF NOT EXISTS memories_au AFTER UPDATE ON memories BEGIN
  INSERT INTO memories_fts(memories_fts, rowid, content, tags)
  VALUES ('delete', old.id, old.content, old.tags);
  INSERT INTO memories_fts(rowid, content, tags)
  VALUES (new.id, new.content, new.tags);
END;

-- Indexes
CREATE INDEX IF NOT EXISTS idx_files_path ON files(path);
CREATE INDEX IF NOT EXISTS idx_files_language ON files(language);
CREATE INDEX IF NOT EXISTS idx_chunks_file ON chunks(file_id);
CREATE INDEX IF NOT EXISTS idx_chunks_name ON chunks(name);
CREATE INDEX IF NOT EXISTS idx_chunks_type ON chunks(type);
CREATE INDEX IF NOT EXISTS idx_deps_source ON dependencies(source_file_id);
CREATE INDEX IF NOT EXISTS idx_deps_target ON dependencies(target_file_id);
CREATE INDEX IF NOT EXISTS idx_symrefs_source ON symbol_refs(source_chunk_id);
CREATE INDEX IF NOT EXISTS idx_symrefs_target ON symbol_refs(target_name);
CREATE INDEX IF NOT EXISTS idx_memories_category ON memories(category);
CREATE INDEX IF NOT EXISTS idx_memories_created ON memories(created_at);
CREATE INDEX IF NOT EXISTS idx_memories_stale ON memories(is_stale);
CREATE INDEX IF NOT EXISTS idx_memories_tier ON memories(tier);
CREATE INDEX IF NOT EXISTS idx_memories_valid_until ON memories(valid_until_sha);
-- idx_memories_kind is added in MIGRATIONS (depends on the kind column,
-- which on upgraded dbs doesn't exist until ALTER TABLE runs).

-- Pattern edges (v0.15, P2-17). LLM-derived "this symbol implements
-- pattern X" relationships, constrained to a closed taxonomy of ~30
-- design-pattern names. Stored separately from the symbol graph so that
-- the deterministic graph stays load-bearing and pattern data stays
-- advisory. Confidence-gated; rows below 0.6 confidence are dropped.
CREATE TABLE IF NOT EXISTS pattern_edges (
  id INTEGER PRIMARY KEY,
  chunk_id INTEGER NOT NULL REFERENCES chunks(id) ON DELETE CASCADE,
  pattern TEXT NOT NULL,           -- one of the closed taxonomy values
  role TEXT,                       -- optional sub-role (e.g. "subject" for observer)
  confidence REAL NOT NULL,
  content_hash TEXT NOT NULL,
  labeled_at INTEGER NOT NULL,
  UNIQUE(chunk_id, pattern, role)
);
CREATE INDEX IF NOT EXISTS idx_pattern_edges_chunk ON pattern_edges(chunk_id);
CREATE INDEX IF NOT EXISTS idx_pattern_edges_pattern ON pattern_edges(pattern);

-- Context handles (v0.15, P1-8). Persistent ctx:// handles let search-family
-- tools return a stable id + 200-token preview; the agent then pulls slices
-- via ctx_slice / ctx_grep / ctx_stats without rerunning retrieval. SHA
-- pinning auto-invalidates handles whose tree state has shifted.
CREATE TABLE IF NOT EXISTS context_handles (
  id TEXT PRIMARY KEY,            -- "ctx_" + 16 hex
  tool TEXT NOT NULL,
  sha TEXT,                       -- repo HEAD at creation
  created_at INTEGER NOT NULL,
  expires_at INTEGER NOT NULL,
  body TEXT NOT NULL,             -- full rendered text
  preview TEXT NOT NULL           -- ≤200 token excerpt
);
CREATE INDEX IF NOT EXISTS idx_handles_expires ON context_handles(expires_at);

-- Concept layer (v0.14, P1-7). LLM-labeled clusters computed offline by
-- the \`sverklo concept-index\` CLI. content_hash lets us skip re-labeling
-- when the cluster membership hasn't changed. Embeddings land in a
-- sibling table so semantic queries can cosine-match without a join.
CREATE TABLE IF NOT EXISTS concepts (
  cluster_id INTEGER PRIMARY KEY,
  label TEXT NOT NULL,
  summary TEXT,
  tags TEXT,                        -- comma-separated list
  hub_file TEXT,
  member_count INTEGER NOT NULL,
  content_hash TEXT NOT NULL,
  labeled_at INTEGER NOT NULL
);
CREATE TABLE IF NOT EXISTS concept_embeddings (
  cluster_id INTEGER PRIMARY KEY REFERENCES concepts(cluster_id) ON DELETE CASCADE,
  vector BLOB NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_concepts_label ON concepts(label);

-- Evidence rows (v0.13, P0-3). Pin retrieval spans to a git SHA + content
-- hash so sverklo_verify can tell whether a citation is still valid after
-- edits. LRU/TTL eviction keeps the table bounded; the id is surfaced via
-- a fenced [evidence] JSON footer on every search-family response.
CREATE TABLE IF NOT EXISTS evidence (
  id TEXT PRIMARY KEY,              -- "ev_" + 12-char hex
  file_path TEXT NOT NULL,
  start_line INTEGER NOT NULL,
  end_line INTEGER NOT NULL,
  commit_sha TEXT,
  chunk_id INTEGER,
  symbol_name TEXT,
  retrieval_method TEXT NOT NULL,
  score REAL NOT NULL,
  content_hash TEXT NOT NULL,       -- sha256 of the span at creation time
  created_at INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_evidence_created ON evidence(created_at);
CREATE INDEX IF NOT EXISTS idx_evidence_file ON evidence(file_path);

-- Doc -> code edges (v0.13, P0-5).
-- Each row means "this doc chunk mentions this symbol name".
-- target_chunk_id is nullable: we resolve lazily so unresolvable mentions
-- are still recorded (they become "orphan" hints at search time).
CREATE TABLE IF NOT EXISTS doc_mentions (
  id INTEGER PRIMARY KEY,
  doc_chunk_id INTEGER NOT NULL REFERENCES chunks(id) ON DELETE CASCADE,
  target_symbol TEXT NOT NULL,
  target_chunk_id INTEGER REFERENCES chunks(id) ON DELETE SET NULL,
  match_kind TEXT NOT NULL,       -- 'backtick' | 'fenced_code' | 'bare'
  edge_kind TEXT DEFAULT 'references', -- 'includes' (structural) | 'references' (associative)
  confidence REAL NOT NULL,       -- 0..1
  UNIQUE(doc_chunk_id, target_symbol)
);
CREATE INDEX IF NOT EXISTS idx_docmentions_symbol ON doc_mentions(target_symbol);
CREATE INDEX IF NOT EXISTS idx_docmentions_target_chunk ON doc_mentions(target_chunk_id);
CREATE INDEX IF NOT EXISTS idx_docmentions_doc ON doc_mentions(doc_chunk_id);
-- idx_docmentions_edge_kind is added in MIGRATIONS (depends on the
-- edge_kind column added by ALTER TABLE on upgraded dbs).

-- Key-value metadata. Used to track the data-layer version so we can
-- run inline migrations when the code's expectations about stored
-- data drift from what's on disk. Single-row-per-key; no schema here
-- because values are small strings.
CREATE TABLE IF NOT EXISTS meta (
  key TEXT PRIMARY KEY,
  value TEXT NOT NULL
);
`;

// Additive migrations — run after SCHEMA to upgrade existing databases
const MIGRATIONS = [
  "ALTER TABLE memories ADD COLUMN tier TEXT DEFAULT 'archive'",
  "ALTER TABLE memories ADD COLUMN valid_from_sha TEXT",
  "ALTER TABLE memories ADD COLUMN valid_until_sha TEXT",
  "ALTER TABLE memories ADD COLUMN invalidated_at INTEGER",
  "ALTER TABLE memories ADD COLUMN superseded_by INTEGER",
  "ALTER TABLE memories ADD COLUMN pins TEXT",
  "ALTER TABLE chunks ADD COLUMN purpose TEXT",
  "ALTER TABLE memories ADD COLUMN trajectory TEXT",
  "ALTER TABLE memories ADD COLUMN kind TEXT DEFAULT 'episodic'",
  "ALTER TABLE doc_mentions ADD COLUMN edge_kind TEXT DEFAULT 'references'",
  // SQLite's ADD COLUMN with DEFAULT does NOT backfill existing rows
  // through the column metadata reliably across versions — older builds
  // store NULL and only emit the default for new INSERTs. Without these
  // backfills, kind-filtered recall (`memory.kind !== kindFilter`) would
  // silently drop every row created before the migration.
  "UPDATE memories SET kind = 'episodic' WHERE kind IS NULL",
  "UPDATE doc_mentions SET edge_kind = 'references' WHERE edge_kind IS NULL",
  // Indexes that depend on migration-added columns must come after the
  // ALTER TABLE statements above.
  "CREATE INDEX IF NOT EXISTS idx_memories_kind ON memories(kind)",
  "CREATE INDEX IF NOT EXISTS idx_docmentions_edge_kind ON doc_mentions(edge_kind)",
];

export function createDatabase(dbPath: string): Database {
  const db = new DatabaseSync(dbPath);

  // node:sqlite doesn't expose `db.pragma()` — use exec() directly.
  // Same effect, slightly different ergonomics.
  db.exec("PRAGMA journal_mode = WAL");
  db.exec("PRAGMA foreign_keys = ON");
  db.exec("PRAGMA synchronous = NORMAL");
  db.exec("PRAGMA cache_size = -64000"); // 64MB cache

  db.exec(SCHEMA);

  // Run additive migrations for existing databases. Each is idempotent via
  // try/catch since ALTER TABLE ADD COLUMN throws if the column exists.
  for (const stmt of MIGRATIONS) {
    try {
      db.exec(stmt);
    } catch {
      // Column already exists — expected on already-migrated dbs
    }
  }

  return db;
}

/**
 * Read the stored data version. Returns 0 for fresh databases that
 * have never been tagged, so callers can treat any version < current
 * as "migrate me".
 */
export function getDataVersion(db: Database): number {
  try {
    const row = db
      .prepare("SELECT value FROM meta WHERE key = 'data_version'")
      .get() as { value: string } | undefined;
    return row ? parseInt(row.value, 10) || 0 : 0;
  } catch {
    return 0;
  }
}

/**
 * Stamp the database with the current data version. Call after a
 * migration completes (or on a fresh database after the first full
 * index, so subsequent runs don't waste time re-migrating).
 */
export function setDataVersion(db: Database, version: number): void {
  db.prepare(
    "INSERT INTO meta (key, value) VALUES ('data_version', ?) " +
      "ON CONFLICT(key) DO UPDATE SET value = excluded.value"
  ).run(String(version));
}

/**
 * BEGIN / COMMIT / ROLLBACK helper. better-sqlite3 had `db.transaction(fn)`
 * built in; node:sqlite doesn't, so we wrap the pattern explicitly.
 * Used by Indexer.index() for the bulk-insert path.
 */
export function transaction<T>(db: Database, fn: () => T): T {
  db.exec("BEGIN");
  try {
    const result = fn();
    db.exec("COMMIT");
    return result;
  } catch (err) {
    try {
      db.exec("ROLLBACK");
    } catch {
      // best-effort rollback; surface the original error
    }
    throw err;
  }
}
