import { DatabaseSync } from "node:sqlite";
import { mkdirSync, chmodSync } from "node:fs";
import { dirname } from "node:path";
import { redact } from "./redact.ts";
import { resolveScope } from "./scopes.ts";
import { loadVecExtension, vecMode } from "./vec.ts";
import { activeEmbedder, featureHash, isAsyncProvider, embed } from "./embeddings.ts";
import { runMigrations } from "./migrations.ts";

export type Db = DatabaseSync;
export type MemType = "episodic" | "semantic" | "procedural" | "working";

const SCHEMA = `
PRAGMA journal_mode=WAL;
CREATE TABLE IF NOT EXISTS state_meta(key TEXT PRIMARY KEY, value TEXT);
CREATE TABLE IF NOT EXISTS workspaces(id TEXT PRIMARY KEY, config_json TEXT);
CREATE TABLE IF NOT EXISTS peers(id TEXT PRIMARY KEY, workspace_id TEXT NOT NULL, kind TEXT NOT NULL, created_at REAL NOT NULL);
CREATE TABLE IF NOT EXISTS scopes(id INTEGER PRIMARY KEY AUTOINCREMENT, workspace_id TEXT NOT NULL, kind TEXT NOT NULL, key TEXT NOT NULL UNIQUE);
CREATE TABLE IF NOT EXISTS sessions(id TEXT PRIMARY KEY, workspace_id TEXT NOT NULL, pi_session_id TEXT UNIQUE, cwd TEXT, repo_root TEXT, scope_id INTEGER, summary TEXT, started_at REAL, ended_at REAL);
CREATE TABLE IF NOT EXISTS session_peers(session_id TEXT NOT NULL, peer_id TEXT NOT NULL, PRIMARY KEY(session_id, peer_id));
CREATE TABLE IF NOT EXISTS messages(id INTEGER PRIMARY KEY AUTOINCREMENT, session_id TEXT NOT NULL, peer_id TEXT NOT NULL, role TEXT NOT NULL, content TEXT NOT NULL, timestamp REAL NOT NULL, token_count INTEGER, observed INTEGER NOT NULL DEFAULT 0);
CREATE TABLE IF NOT EXISTS observations(id INTEGER PRIMARY KEY AUTOINCREMENT, workspace_id TEXT NOT NULL DEFAULT 'pi', peer_id TEXT NOT NULL, session_id TEXT, scope_id INTEGER NOT NULL, mem_type TEXT NOT NULL, content TEXT NOT NULL, importance REAL NOT NULL DEFAULT 0.5, explicit INTEGER NOT NULL DEFAULT 0, supersedes_id INTEGER, expiry REAL, accesses INTEGER NOT NULL DEFAULT 0, created_at REAL NOT NULL, updated_at REAL NOT NULL, embedding BLOB);
CREATE VIRTUAL TABLE IF NOT EXISTS fts_observations USING fts5(content, content='observations', content_rowid='id');
CREATE TRIGGER IF NOT EXISTS obs_fts_insert AFTER INSERT ON observations BEGIN INSERT INTO fts_observations(rowid, content) VALUES (new.id, new.content); END;
CREATE TRIGGER IF NOT EXISTS obs_fts_delete AFTER DELETE ON observations BEGIN INSERT INTO fts_observations(fts_observations, rowid, content) VALUES ('delete', old.id, old.content); END;
CREATE TRIGGER IF NOT EXISTS obs_fts_update AFTER UPDATE OF content ON observations BEGIN INSERT INTO fts_observations(fts_observations, rowid, content) VALUES ('delete', old.id, old.content); INSERT INTO fts_observations(rowid, content) VALUES (new.id, new.content); END;
CREATE TABLE IF NOT EXISTS peer_cards(peer_id TEXT NOT NULL, scope_id INTEGER NOT NULL, content TEXT NOT NULL, updated_at REAL NOT NULL, PRIMARY KEY(peer_id, scope_id));
CREATE TABLE IF NOT EXISTS summaries(id INTEGER PRIMARY KEY AUTOINCREMENT, session_id TEXT, peer_id TEXT, kind TEXT NOT NULL, content TEXT NOT NULL, tokens INTEGER, created_at REAL NOT NULL);
CREATE TABLE IF NOT EXISTS queue(id INTEGER PRIMARY KEY AUTOINCREMENT, session_id TEXT, status TEXT NOT NULL DEFAULT 'pending', attempts INTEGER NOT NULL DEFAULT 0, payload_json TEXT, created_at REAL NOT NULL);
CREATE TABLE IF NOT EXISTS recall_log(id INTEGER PRIMARY KEY AUTOINCREMENT, query TEXT NOT NULL, scope_id INTEGER, result_ids_json TEXT, scores_json TEXT, used_ids_json TEXT, hit INTEGER NOT NULL, pi_session_id TEXT, timestamp REAL NOT NULL);
CREATE INDEX IF NOT EXISTS idx_obs_peer_scope ON observations(peer_id, scope_id, mem_type);
CREATE INDEX IF NOT EXISTS idx_msg_session ON messages(session_id, timestamp);
`;

export function openMindvault(path: string): Db {
  mkdirSync(dirname(path), { recursive: true, mode: 0o700 });
  const db = new DatabaseSync(path, { allowLoadExtension: true } as unknown as Record<string, unknown>);
  try { chmodSync(path, 0o600); } catch { /* best-effort; never block open */ }
  db.exec("PRAGMA journal_mode=WAL; PRAGMA foreign_keys=ON; PRAGMA busy_timeout=5000;");
  db.exec(SCHEMA);
  runMigrations(db);
  db.prepare("INSERT OR IGNORE INTO workspaces(id,config_json) VALUES('pi','{}')").run();
  loadVecExtension(db);
  ensureEmbeddingModel(db);
  backfillEmbeddings(db);
  return db;
}

function scopeId(db: Db, workspaceId: string, scopeKey: string): number {
  const kind = scopeKey === "global" ? "global" : scopeKey.startsWith("dir:") ? "dir" : scopeKey.startsWith("repo:") ? "repo" : "session";
  db.prepare("INSERT OR IGNORE INTO scopes(workspace_id,kind,key) VALUES(?,?,?)").run(workspaceId, kind, scopeKey);
  const row = db.prepare("SELECT id FROM scopes WHERE key=?").get(scopeKey) as { id: number };
  return row.id;
}

function ensurePeer(db: Db, peer: string): void {
  const kind = peer === "pi-agent" ? "agent" : "user";
  db.prepare("INSERT OR IGNORE INTO peers(id,workspace_id,kind,created_at) VALUES(?,?,?,?)").run(peer, "pi", kind, Date.now() / 1000);
}

export function seedVault(db: Db, args: { cwd: string; repoRoot?: string | null }): { peers: number; scopes: number } {
  const peers = ["user", "pi-agent"];
  for (const p of peers) ensurePeer(db, p);
  const scope = resolveScope({ cwd: args.cwd, repoRoot: args.repoRoot ?? null, explicit: null });
  const keys = ["global", scope.key];
  for (const k of keys) scopeId(db, "pi", k);
  return { peers: peers.length, scopes: keys.length };
}

function ensureEmbeddingModel(db: Db): void {
  const emb = activeEmbedder();
  const dimRow = db.prepare("SELECT value FROM state_meta WHERE key='embedding_dim'").get() as { value?: string } | undefined;
  const nameRow = db.prepare("SELECT value FROM state_meta WHERE key='embedding_model'").get() as { value?: string } | undefined;
  const changed = (!!dimRow && Number(dimRow.value) !== emb.dim) || (!!nameRow && nameRow.value !== emb.name);
  db.prepare("INSERT INTO state_meta(key,value) VALUES('embedding_dim',?) ON CONFLICT(key) DO UPDATE SET value=excluded.value").run(String(emb.dim));
  db.prepare("INSERT INTO state_meta(key,value) VALUES('embedding_model',?) ON CONFLICT(key) DO UPDATE SET value=excluded.value").run(emb.name);
  if (changed) {
    // embedding model or dim changed: drop native vectors, null stored blobs for lazy re-embed
    try { db.exec("DELETE FROM vec_observations"); } catch { /* js mode: nothing */ }
    db.prepare("UPDATE observations SET embedding=NULL").run();
  }
}

function toBlob(v: Float32Array): Buffer {
  return Buffer.from(v.buffer, v.byteOffset, v.byteLength);
}
function fromBlob(b: Uint8Array | Buffer | null): Float32Array | null {
  if (!b) return null;
  const buf = Buffer.from(b);
  if (buf.byteLength % 4 !== 0 || buf.byteLength === 0) return null;
  return new Float32Array(buf.buffer, buf.byteOffset, buf.byteLength / 4);
}
function jsCosine(a: Float32Array, b: Float32Array): number {
  if (a.length !== b.length) return -1;
  let d = 0;
  for (let i = 0; i < a.length; i++) d += a[i] * b[i];
  return d;
}

export function backfillEmbeddings(db: Db, limit = 200): number {
  if (isAsyncProvider()) return 0; // real providers fill NULL rows via embedUpgrade
  const rows = db.prepare("SELECT id, content FROM observations WHERE embedding IS NULL LIMIT ?").all(limit) as { id: number; content: string }[];
  if (rows.length === 0) return 0;
  const dim = activeEmbedder().dim;
  const mode = vecMode(db);
  let n = 0;
  for (const r of rows) {
    const v = toBlob(featureHash(r.content, dim));
    db.prepare("UPDATE observations SET embedding=? WHERE id=?").run(v, r.id);
    if (mode === "vec0") {
      try { db.prepare("INSERT OR REPLACE INTO vec_observations(rowid, embedding) VALUES(?,?)").run(r.id, v); } catch { /* js scan covers */ }
    }
    n++;
  }
  return n;
}

export async function embedUpgrade(db: Db, limit = 200): Promise<number> {
  if (!isAsyncProvider()) return 0;
  const rows = db.prepare("SELECT id, content FROM observations WHERE embedding IS NULL LIMIT ?").all(limit) as { id: number; content: string }[];
  if (rows.length === 0) return 0;
  const mode = vecMode(db);
  let n = 0;
  for (const r of rows) {
    try {
      const v = toBlob(await embed(r.content));
      db.prepare("UPDATE observations SET embedding=? WHERE id=?").run(v, r.id);
      if (mode === "vec0") {
        try { db.prepare("INSERT OR REPLACE INTO vec_observations(rowid, embedding) VALUES(?,?)").run(r.id, v); } catch { /* js scan covers */ }
      }
      n++;
    } catch { /* leave NULL; retried next pass */ }
  }
  return n;
}

export function remember(db: Db, args: { peer: string; content: string; memType: MemType; scopeKey: string; explicit: 0 | 1; cwd: string; importance?: number }): number {
  const clean = redact(args.content, { cwd: args.cwd });
  ensurePeer(db, args.peer);
  const sid = scopeId(db, "pi", args.scopeKey);
  const now = Date.now() / 1000;
  const r = db.prepare("INSERT INTO observations(workspace_id,peer_id,session_id,scope_id,mem_type,content,importance,explicit,accesses,created_at,updated_at) VALUES('pi',?,NULL,?,?,?,COALESCE(?,0.5),?,0,?,?)").run(args.peer, sid, args.memType, clean, args.importance ?? null, args.explicit, now, now) as { lastInsertRowid: number | bigint };
  const id = Number(r.lastInsertRowid);
  if (!isAsyncProvider()) {
    try {
      const v = toBlob(featureHash(clean, activeEmbedder().dim));
      db.prepare("UPDATE observations SET embedding=? WHERE id=?").run(v, id);
      if (vecMode(db) === "vec0") {
        try { db.prepare("INSERT INTO vec_observations(rowid, embedding) VALUES(?,?)").run(id, v); } catch { /* js fallback covers */ }
      }
    } catch { /* embedding never blocks a write */ }
  }
  // refresh peer card for global scope (fast inject source)
  if (args.scopeKey === "global") {
    const rows = db.prepare("SELECT content FROM observations WHERE peer_id=? AND scope_id=? ORDER BY id DESC LIMIT 20").all(args.peer, sid) as { content: string }[];
    const card = rows.map((x) => `- ${x.content}`).join("\n").slice(0, 4000);
    db.prepare("INSERT INTO peer_cards(peer_id,scope_id,content,updated_at) VALUES(?,?,?,?) ON CONFLICT(peer_id,scope_id) DO UPDATE SET content=excluded.content, updated_at=excluded.updated_at").run(args.peer, sid, card, now);
  }
  return id;
}

export interface Explanation {
  id: number; content: string; scopeKey: string; memType: string; importance: number;
  explicit: number; accesses: number; createdAt: number; updatedAt: number;
  supersedes: number[]; lastRecall: { query: string; score: number | null; used: boolean } | null;
}

export function explainObservation(db: Db, id: number): Explanation | null {
  const row = db.prepare(
    `SELECT o.id, o.content, s.key AS scopeKey, o.mem_type, o.importance, o.explicit, o.accesses, o.created_at, o.updated_at, o.supersedes_id
     FROM observations o JOIN scopes s ON s.id=o.scope_id WHERE o.id=?`
  ).get(id) as { id: number; content: string; scopeKey: string; mem_type: string; importance: number; explicit: number; accesses: number; created_at: number; updated_at: number; supersedes_id: number | null } | undefined;
  if (!row) return null;
  const supersedes: number[] = [];
  const seen = new Set<number>();
  let sup = row.supersedes_id;
  while (sup && !seen.has(sup)) {
    supersedes.push(sup);
    seen.add(sup);
    const next = db.prepare("SELECT supersedes_id FROM observations WHERE id=?").get(sup) as { supersedes_id: number | null } | undefined;
    sup = next?.supersedes_id ?? null;
  }
  const logs = db.prepare("SELECT query, result_ids_json, scores_json, used_ids_json FROM recall_log ORDER BY id DESC LIMIT 50").all() as { query: string; result_ids_json: string | null; scores_json: string | null; used_ids_json: string | null }[];
  let lastRecall: Explanation["lastRecall"] = null;
  for (const l of logs) {
    const ids = JSON.parse(l.result_ids_json ?? "[]") as number[];
    const k = ids.indexOf(id);
    if (k >= 0) {
      const scores = JSON.parse(l.scores_json ?? "[]") as number[];
      const used = JSON.parse(l.used_ids_json ?? "[]") as number[];
      lastRecall = { query: l.query, score: scores[k] ?? null, used: used.includes(id) };
      break;
    }
  }
  return { id: row.id, content: row.content, scopeKey: row.scopeKey, memType: row.mem_type, importance: row.importance, explicit: row.explicit, accesses: row.accesses, createdAt: row.created_at, updatedAt: row.updated_at, supersedes, lastRecall };
}

export function editObservation(db: Db, args: { id: number; content: string; cwd: string }): boolean {
  const clean = redact(args.content, { cwd: args.cwd });
  const now = Date.now() / 1000;
  const r = db.prepare("UPDATE observations SET content=?, updated_at=? WHERE id=?").run(clean, now, args.id) as { changes: number | bigint };
  if (Number(r.changes) === 0) return false;
  if (!isAsyncProvider()) {
    try {
      const v = toBlob(featureHash(clean, activeEmbedder().dim));
      db.prepare("UPDATE observations SET embedding=? WHERE id=?").run(v, args.id);
      if (vecMode(db) === "vec0") {
        try { db.prepare("INSERT OR REPLACE INTO vec_observations(rowid, embedding) VALUES(?,?)").run(args.id, v); } catch { /* js fallback covers */ }
      }
    } catch { /* embedding never blocks an edit */ }
  } else {
    db.prepare("UPDATE observations SET embedding=NULL WHERE id=?").run(args.id); // embedUpgrade re-fills
  }
  return true;
}

export interface Hit { id: number; content: string; score: number; source: string; scopeKey: string }

export function recallSearch(db: Db, args: { query: string; scopeKeys: string[]; limit?: number; queryVector?: Float32Array }): Hit[] {
  const limit = args.limit ?? 5;
  if (args.scopeKeys.length === 0) return [];
  const placeholders = args.scopeKeys.map(() => "?").join(",");
  const qf = args.queryVector ?? (isAsyncProvider() ? null : featureHash(args.query, activeEmbedder().dim));
  const K = 60;
  const rrf = new Map<number, { rrf: number; sources: string[] }>();
  const add = (id: number, rank: number, src: string) => {
    const cur = rrf.get(id) ?? { rrf: 0, sources: [] as string[] };
    cur.rrf += 1 / (K + rank);
    if (!cur.sources.includes(src)) cur.sources.push(src);
    rrf.set(id, cur);
  };
  // FTS leg (rank = order)
  try {
    const rows = db.prepare(
      `SELECT o.id FROM fts_observations f JOIN observations o ON o.id = f.rowid JOIN scopes s ON s.id = o.scope_id
       WHERE fts_observations MATCH ? AND s.key IN (${placeholders}) ORDER BY bm25(fts_observations) LIMIT 20`
    ).all(args.query, ...args.scopeKeys) as { id: number }[];
    rows.forEach((r, i) => add(r.id, i, "fts"));
  } catch {
    const like = `%${args.query.split(/\s+/)[0] ?? args.query}%`;
    const rows = db.prepare(
      `SELECT o.id FROM observations o JOIN scopes s ON s.id=o.scope_id
       WHERE o.content LIKE ? AND s.key IN (${placeholders}) ORDER BY o.id DESC LIMIT 20`
    ).all(like, ...args.scopeKeys) as { id: number }[];
    rows.forEach((r, i) => add(r.id, i, "like"));
  }
  // vector leg: vec0 when available, JS cosine scan otherwise (skipped when no query vector)
  if (qf) {
    try {
      if (vecMode(db) === "vec0") {
        const qv = toBlob(qf);
        const rows = db.prepare(
          `SELECT o.id FROM vec_observations v JOIN observations o ON o.id = v.rowid JOIN scopes s ON s.id=o.scope_id
           WHERE v.embedding MATCH ? AND k = 20 AND s.key IN (${placeholders})`
        ).all(qv, ...args.scopeKeys) as { id: number }[];
        if (rows.length === 0) throw new Error("vec0-empty");
        rows.forEach((r, i) => add(r.id, i, "vec"));
      } else {
        throw new Error("js-scan");
      }
    } catch {
      const rows = db.prepare(
        `SELECT o.id, o.embedding FROM observations o JOIN scopes s ON s.id=o.scope_id WHERE s.key IN (${placeholders})`
      ).all(...args.scopeKeys) as { id: number; embedding: Buffer | null }[];
      rows
        .map((r) => ({ id: r.id, s: r.embedding ? jsCosine(qf, fromBlob(r.embedding)!) : -1 }))
        .filter((x) => x.s > 0)
        .sort((a, b) => b.s - a.s)
        .slice(0, 20)
        .forEach((r, i) => add(r.id, i, "vec"));
    }
  }
  if (rrf.size === 0) { logRecall(db, args.query, null, [], []); return []; }
  const ids = [...rrf.keys()];
  const meta = db.prepare(
    `SELECT o.id, o.content, o.importance, o.explicit, o.accesses, o.created_at, s.key AS scopeKey FROM observations o JOIN scopes s ON s.id=o.scope_id WHERE o.id IN (${ids.map(() => "?").join(",")})`
  ).all(...ids) as { id: number; content: string; importance: number; explicit: number; accesses: number; created_at: number; scopeKey: string }[];
  const now = Date.now() / 1000;
  const ranked = meta
    .map((m) => {
      const r = rrf.get(m.id)!;
      const rec = 1 / (1 + Math.max(0, now - m.created_at) / 86400);
      const score = 0.6 * r.rrf + 0.25 * rec + 0.15 * Math.min(1, Math.max(0, m.importance)) + (m.explicit ? 0.1 : 0) + 0.05 * Math.min(1, m.accesses / 10);
      return { id: m.id, content: m.content, score, source: r.sources.sort().join("+") || "rrf", scopeKey: m.scopeKey };
    })
    .sort((a, b) => b.score - a.score)
    .slice(0, limit);
  if (ranked.length > 0) {
    try {
      const rids = ranked.map((r) => r.id);
      db.prepare(`UPDATE observations SET accesses=accesses+1 WHERE id IN (${rids.map(() => "?").join(",")})`).run(...rids);
    } catch { /* access accounting never blocks recall */ }
  }
  logRecall(db, args.query, null, ranked.map((r) => r.id), ranked.map((r) => r.score));
  return ranked;
}

function logRecall(db: Db, query: string, scopeIdV: number | null, ids: number[], scores: number[]): void {
  db.prepare("INSERT INTO recall_log(query,scope_id,result_ids_json,scores_json,hit,pi_session_id,timestamp) VALUES(?,?,?,?,?,?,?)")
    .run(query, scopeIdV, JSON.stringify(ids), JSON.stringify(scores), ids.length > 0 ? 1 : 0, null, Date.now() / 1000);
}

export function markUsed(db: Db, ids: number[]): number {
  if (ids.length === 0) return 0;
  const placeholders = ids.map(() => "?").join(",");
  const r = db.prepare(`UPDATE observations SET accesses=accesses+1 WHERE id IN (${placeholders})`).run(...ids) as { changes: number | bigint };
  try {
    const last = db.prepare("SELECT id FROM recall_log ORDER BY id DESC LIMIT 1").get() as { id: number } | undefined;
    if (last) db.prepare("UPDATE recall_log SET used_ids_json=? WHERE id=?").run(JSON.stringify(ids), last.id);
  } catch { /* feedback logging never blocks */ }
  return Number(r.changes);
}

export function getProfile(db: Db, args: { peer: string; scopeKeys: string[] }): string[] {
  if (args.scopeKeys.length === 0) return [];
  const placeholders = args.scopeKeys.map(() => "?").join(",");
  const rows = db.prepare(
    `SELECT c.content FROM peer_cards c JOIN scopes s ON s.id=c.scope_id WHERE c.peer_id=? AND s.key IN (${placeholders})`
  ).all(args.peer, ...args.scopeKeys) as { content: string }[];
  if (rows.length > 0) return rows.map((r) => r.content);
  // fallback: latest global observations
  const fb = db.prepare(
    `SELECT o.content FROM observations o JOIN scopes s ON s.id=o.scope_id WHERE o.peer_id=? AND s.key IN (${placeholders}) ORDER BY o.id DESC LIMIT 10`
  ).all(args.peer, ...args.scopeKeys) as { content: string }[];
  return fb.map((r) => r.content);
}

export function dbStatus(db: Db): { schemaVersion: number; observations: number; ftsCount: number; queuePending: number; dbSize: number | null; embeddingDim: number | null; vecMode: string } {
  const v = db.prepare("SELECT value FROM state_meta WHERE key='schema_version'").get() as { value: string };
  const c = db.prepare("SELECT COUNT(*) AS n FROM observations").get() as { n: number };
  let fts = 0;
  try { fts = (db.prepare("SELECT COUNT(*) AS n FROM fts_observations").get() as { n: number }).n; } catch { fts = -1; }
  const q = db.prepare("SELECT COUNT(*) AS n FROM queue WHERE status='pending'").get() as { n: number };
  let dim: number | null = null;
  try { dim = Number((db.prepare("SELECT value FROM state_meta WHERE key='embedding_dim'").get() as { value: string }).value); } catch { dim = null; }
  return { schemaVersion: Number(v.value), observations: c.n, ftsCount: fts, queuePending: q.n, dbSize: null, embeddingDim: dim, vecMode: vecMode(db) };
}

export function forgetObservation(db: Db, id: number): boolean {
  if (vecMode(db) === "vec0") {
    try { db.prepare("DELETE FROM vec_observations WHERE rowid=?").run(id); } catch { /* js mode covers */ }
  }
  const r = db.prepare("DELETE FROM observations WHERE id=?").run(id) as { changes: number | bigint };
  return Number(r.changes) > 0;
}

export function deleteScope(db: Db, scopeKey: string): number {
  const row = db.prepare("SELECT id FROM scopes WHERE key=?").get(scopeKey) as { id: number } | undefined;
  if (!row) return 0;
  if (vecMode(db) === "vec0") {
    try { db.prepare("DELETE FROM vec_observations WHERE rowid IN (SELECT id FROM observations WHERE scope_id=?)").run(row.id); } catch { /* js */ }
  }
  const r = db.prepare("DELETE FROM observations WHERE scope_id=?").run(row.id) as { changes: number | bigint };
  db.prepare("DELETE FROM peer_cards WHERE scope_id=?").run(row.id);
  db.prepare("DELETE FROM scopes WHERE id=?").run(row.id);
  return Number(r.changes);
}