import Database from 'better-sqlite3';
import { getDbPath } from './config.mjs';

let db;
let broadcast = () => {}; // set by server.mjs

export function setBroadcast(fn) {
  broadcast = fn;
}

export function initDb() {
  db = new Database(getDbPath());
  db.pragma('journal_mode = WAL');
  db.pragma('foreign_keys = ON');

  db.exec(`
    CREATE TABLE IF NOT EXISTS events (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      type TEXT NOT NULL,
      source TEXT NOT NULL,
      content TEXT NOT NULL,
      metadata TEXT,
      created_at TEXT DEFAULT (datetime('now'))
    );

    CREATE TABLE IF NOT EXISTS memories (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      key TEXT UNIQUE NOT NULL,
      content TEXT NOT NULL,
      category TEXT DEFAULT 'general',
      created_at TEXT DEFAULT (datetime('now')),
      updated_at TEXT DEFAULT (datetime('now'))
    );

    CREATE TABLE IF NOT EXISTS skills (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT UNIQUE NOT NULL,
      description TEXT NOT NULL,
      filename TEXT NOT NULL,
      enabled INTEGER DEFAULT 1,
      created_at TEXT DEFAULT (datetime('now'))
    );

    CREATE INDEX IF NOT EXISTS idx_events_type ON events(type);
    CREATE INDEX IF NOT EXISTS idx_events_created ON events(created_at);
    CREATE INDEX IF NOT EXISTS idx_events_type_source ON events(type, source);
    CREATE INDEX IF NOT EXISTS idx_memories_category ON memories(category);

    CREATE TABLE IF NOT EXISTS api_usage (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      date TEXT NOT NULL,
      model TEXT NOT NULL,
      calls INTEGER DEFAULT 0,
      estimated_cost REAL DEFAULT 0,
      created_at TEXT DEFAULT (datetime('now'))
    );
    CREATE UNIQUE INDEX IF NOT EXISTS idx_usage_date_model ON api_usage(date, model);

    CREATE TABLE IF NOT EXISTS tasks (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      source TEXT NOT NULL,
      kind TEXT NOT NULL,
      conversation_id TEXT,
      skill TEXT,
      args_json TEXT,
      creates_files INTEGER DEFAULT 0,
      state TEXT NOT NULL DEFAULT 'queued',
      error TEXT,
      created_at TEXT DEFAULT (datetime('now')),
      started_at TEXT,
      ended_at TEXT
    );
    CREATE INDEX IF NOT EXISTS idx_tasks_state_created ON tasks(state, created_at);
    CREATE INDEX IF NOT EXISTS idx_tasks_conversation ON tasks(conversation_id);
    CREATE INDEX IF NOT EXISTS idx_tasks_status ON tasks(state);
    CREATE INDEX IF NOT EXISTS idx_tasks_created ON tasks(created_at);

    -- v3.0: Knowledge Graph
    CREATE TABLE IF NOT EXISTS kg_entities (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      type TEXT NOT NULL,
      workspace_id TEXT DEFAULT 'default',
      metadata TEXT,
      created_at TEXT DEFAULT (datetime('now')),
      updated_at TEXT DEFAULT (datetime('now'))
    );
    CREATE INDEX IF NOT EXISTS idx_kg_entity_type ON kg_entities(type);
    CREATE INDEX IF NOT EXISTS idx_kg_entities_type ON kg_entities(type);
    CREATE INDEX IF NOT EXISTS idx_kg_entity_ws ON kg_entities(workspace_id);
    CREATE UNIQUE INDEX IF NOT EXISTS idx_kg_entity_name_type_ws ON kg_entities(name, type, workspace_id);

    CREATE TABLE IF NOT EXISTS kg_relations (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      source_id INTEGER NOT NULL REFERENCES kg_entities(id) ON DELETE CASCADE,
      target_id INTEGER NOT NULL REFERENCES kg_entities(id) ON DELETE CASCADE,
      relation TEXT NOT NULL,
      weight REAL DEFAULT 1.0,
      metadata TEXT,
      created_at TEXT DEFAULT (datetime('now'))
    );
    CREATE INDEX IF NOT EXISTS idx_kg_rel_source ON kg_relations(source_id);
    CREATE INDEX IF NOT EXISTS idx_kg_rel_target ON kg_relations(target_id);
    CREATE INDEX IF NOT EXISTS idx_kg_rel_relation ON kg_relations(relation);

    -- v3.0: Workspaces
    CREATE TABLE IF NOT EXISTS workspaces (
      id TEXT PRIMARY KEY,
      name TEXT NOT NULL,
      root_path TEXT NOT NULL,
      description TEXT DEFAULT '',
      is_active INTEGER DEFAULT 0,
      created_at TEXT DEFAULT (datetime('now')),
      updated_at TEXT DEFAULT (datetime('now'))
    );

    -- v3.0: Ingestion Queue
    CREATE TABLE IF NOT EXISTS ingestion_queue (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      source_type TEXT NOT NULL,
      source_path TEXT,
      content_hash TEXT,
      state TEXT NOT NULL DEFAULT 'queued',
      workspace_id TEXT DEFAULT 'default',
      result_entity_id INTEGER,
      error TEXT,
      created_at TEXT DEFAULT (datetime('now')),
      completed_at TEXT
    );
    CREATE INDEX IF NOT EXISTS idx_ingest_state ON ingestion_queue(state);
    CREATE INDEX IF NOT EXISTS idx_ingest_ws ON ingestion_queue(workspace_id);

    -- v3.0: Cognitive Log
    CREATE TABLE IF NOT EXISTS cognitive_log (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      module TEXT NOT NULL,
      input_summary TEXT,
      output_summary TEXT,
      tokens_used INTEGER DEFAULT 0,
      duration_ms INTEGER DEFAULT 0,
      metadata TEXT,
      created_at TEXT DEFAULT (datetime('now'))
    );
    CREATE INDEX IF NOT EXISTS idx_cognitive_module ON cognitive_log(module);
    CREATE INDEX IF NOT EXISTS idx_cognitive_created ON cognitive_log(created_at);

    -- v4.2: Chat Sessions
    CREATE TABLE IF NOT EXISTS sessions (
      id TEXT PRIMARY KEY,
      source TEXT NOT NULL DEFAULT 'chat',
      messages_json TEXT NOT NULL DEFAULT '[]',
      token_estimate INTEGER DEFAULT 0,
      created_at TEXT DEFAULT (datetime('now')),
      updated_at TEXT DEFAULT (datetime('now'))
    );

    -- v4.x: Procedural Memory
    CREATE TABLE IF NOT EXISTS procedural_memory (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      skill_name TEXT NOT NULL,
      input_pattern TEXT,
      approach TEXT NOT NULL DEFAULT 'standard',
      outcome TEXT NOT NULL DEFAULT 'success',
      quality_score REAL DEFAULT 1.0,
      created_at TEXT DEFAULT (datetime('now'))
    );
    CREATE INDEX IF NOT EXISTS idx_proc_mem_skill ON procedural_memory(skill_name);

    -- v4.2: Background Tasks
    CREATE TABLE IF NOT EXISTS background_tasks (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      session_id TEXT,
      subject TEXT NOT NULL,
      description TEXT NOT NULL DEFAULT '',
      status TEXT NOT NULL DEFAULT 'pending',
      scheduled_at TEXT,
      result TEXT,
      error TEXT,
      created_at TEXT DEFAULT (datetime('now')),
      started_at TEXT,
      ended_at TEXT
    );
    CREATE INDEX IF NOT EXISTS idx_bg_tasks_status ON background_tasks(status);
    CREATE INDEX IF NOT EXISTS idx_bg_tasks_scheduled ON background_tasks(scheduled_at);
  `);

  // v4.3: Add skill column to background_tasks for skill-based tasks
  try {
    db.exec(`ALTER TABLE background_tasks ADD COLUMN skill TEXT`);
  } catch {
    // Column already exists
  }

  // v3.0: FTS5 for knowledge graph entity search
  try {
    db.exec(`
      CREATE VIRTUAL TABLE IF NOT EXISTS kg_entities_fts USING fts5(
        name, metadata, content=kg_entities, content_rowid=id
      );
    `);
  } catch {
    // FTS5 table already exists or not supported
  }

  // Migration: add processed column for existing databases
  try {
    db.exec(`ALTER TABLE events ADD COLUMN processed INTEGER DEFAULT 0`);
  } catch {
    // Column already exists
  }
  db.exec(`CREATE INDEX IF NOT EXISTS idx_events_processed ON events(processed) WHERE processed = 0`);

  // Backfill: mark old user chat messages that already have agent replies as processed
  db.exec(`
    UPDATE events SET processed = 1
    WHERE type = 'chat' AND source = 'user' AND processed = 0
    AND EXISTS (
      SELECT 1 FROM events r
      WHERE r.type = 'chat' AND r.source = 'agent'
      AND r.id > events.id
      AND json_extract(r.metadata, '$.reply_to') = events.id
    )
  `);

  // Recover tasks that were in-flight or orphaned during an unclean shutdown.
  db.exec(`
    UPDATE tasks
    SET state = 'failed', error = 'Agent restarted while task was running', ended_at = datetime('now')
    WHERE state = 'running'
  `);
  db.exec(`
    UPDATE tasks
    SET state = 'canceled', error = 'Stale queued task from previous session', ended_at = datetime('now')
    WHERE state = 'queued'
  `);

  // Recover background tasks that were in-flight during an unclean shutdown.
  db.exec(`
    UPDATE background_tasks
    SET status = 'failed', error = 'Agent restarted while task was running', ended_at = datetime('now')
    WHERE status = 'in_progress'
  `);

  return db;
}

export function getDb() {
  if (!db) throw new Error('Database not initialized — call initDb() first');
  return db;
}

// BigInt-safe JSON serializer (better-sqlite3 returns BigInt for rowids)
function safeStringify(obj) {
  return JSON.stringify(obj, (_, v) => typeof v === 'bigint' ? Number(v) : v);
}

// Events
export function addEvent(type, source, content, metadata = null, { silent = false } = {}) {
  const meta = metadata ? safeStringify(metadata) : null;
  const stmt = getDb().prepare(
    'INSERT INTO events (type, source, content, metadata) VALUES (?, ?, ?, ?)'
  );
  const result = stmt.run(type, source, content, meta);
  const event = {
    id: Number(result.lastInsertRowid),
    type, source, content, metadata: meta,
    created_at: new Date().toISOString().replace('T', ' ').slice(0, 19)
  };
  if (!silent) broadcast({ type: 'event', data: event });
  return event;
}

export function getEvents({ type, limit = 50, offset = 0, since } = {}) {
  // Use a subquery to get the most recent N events (DESC), then sort
  // the result chronologically (ASC) so the UI renders oldest-first.
  let inner = 'SELECT * FROM events';
  const params = [];
  const conditions = [];

  if (type) {
    conditions.push('type = ?');
    params.push(type);
  }
  if (since) {
    conditions.push('created_at > ?');
    params.push(since);
  }
  if (conditions.length) inner += ' WHERE ' + conditions.join(' AND ');

  inner += ' ORDER BY id DESC LIMIT ? OFFSET ?';
  params.push(limit, offset);

  const sql = `SELECT * FROM (${inner}) ORDER BY id ASC`;
  return getDb().prepare(sql).all(...params);
}

export function getUnprocessedChats() {
  return getDb().prepare(`
    SELECT * FROM events
    WHERE type = 'chat' AND source = 'user' AND processed = 0
    ORDER BY id ASC
  `).all();
}

export function markProcessed(eventIds) {
  if (!eventIds.length) return;
  const placeholders = eventIds.map(() => '?').join(',');
  getDb().prepare(`UPDATE events SET processed = 1 WHERE id IN (${placeholders})`).run(...eventIds);
}

// Memories
export function addMemory(key, content, category = 'general') {
  const stmt = getDb().prepare(`
    INSERT INTO memories (key, content, category)
    VALUES (?, ?, ?)
    ON CONFLICT(key) DO UPDATE SET
      content = excluded.content,
      category = excluded.category,
      updated_at = datetime('now')
  `);
  stmt.run(key, content, category);
}

export function getMemory(key) {
  return getDb().prepare('SELECT * FROM memories WHERE key = ?').get(key);
}

export function getAllMemories(category) {
  if (category) {
    return getDb().prepare('SELECT * FROM memories WHERE category = ? ORDER BY updated_at DESC').all(category);
  }
  return getDb().prepare('SELECT * FROM memories ORDER BY updated_at DESC').all();
}

export function deleteMemory(key) {
  getDb().prepare('DELETE FROM memories WHERE key = ?').run(key);
}

// Clear/compact chat
export function deleteEventsUpTo(id) {
  const count = getDb().prepare('SELECT COUNT(*) as c FROM events WHERE id <= ? AND type = ?').get(id, 'chat');
  getDb().prepare('DELETE FROM events WHERE id <= ? AND type = ?').run(id, 'chat');
  return count.c;
}

export function deleteAllChatEvents() {
  const count = getDb().prepare("SELECT COUNT(*) as c FROM events WHERE type = 'chat'").get();
  getDb().prepare("DELETE FROM events WHERE type = 'chat'").run();
  return count.c;
}

export function getChatCount() {
  return getDb().prepare("SELECT COUNT(*) as c FROM events WHERE type = ? AND source != 'grok-voice'").get('chat').c;
}

export function getOldestChats(limit) {
  return getDb().prepare(
    "SELECT * FROM events WHERE type = ? AND source != 'grok-voice' ORDER BY id ASC LIMIT ?"
  ).all('chat', limit);
}

export function replaceChatsWithSummary(ids, summary) {
  const placeholders = ids.map(() => '?').join(',');
  getDb().prepare(`DELETE FROM events WHERE id IN (${placeholders}) AND type = 'chat'`).run(...ids);
  const meta = JSON.stringify({ compacted: true, original_count: ids.length });
  const stmt = getDb().prepare(
    'INSERT INTO events (type, source, content, metadata) VALUES (?, ?, ?, ?)'
  );
  stmt.run('chat', 'system', summary, meta);
}

// Skills
export function getSkillsFromDb() {
  return getDb().prepare('SELECT * FROM skills ORDER BY name').all();
}

export function addSkillToDb(name, description, filename) {
  const stmt = getDb().prepare(`
    INSERT INTO skills (name, description, filename)
    VALUES (?, ?, ?)
    ON CONFLICT(name) DO UPDATE SET
      description = excluded.description,
      filename = excluded.filename
  `);
  stmt.run(name, description, filename);
}

export function toggleSkill(name, enabled) {
  getDb().prepare('UPDATE skills SET enabled = ? WHERE name = ?').run(enabled ? 1 : 0, name);
}

// API usage tracking
export function recordApiCall(model, estimatedCost = 0) {
  const date = new Date().toISOString().slice(0, 10);
  getDb().prepare(`
    INSERT INTO api_usage (date, model, calls, estimated_cost)
    VALUES (?, ?, 1, ?)
    ON CONFLICT(date, model) DO UPDATE SET
      calls = calls + 1,
      estimated_cost = estimated_cost + ?
  `).run(date, model, estimatedCost, estimatedCost);
}

export function getDailyUsage(date) {
  const d = date || new Date().toISOString().slice(0, 10);
  return getDb().prepare(`
    SELECT model, calls, estimated_cost FROM api_usage WHERE date = ?
  `).all(d);
}

export function getDailyTotalCost(date) {
  const d = date || new Date().toISOString().slice(0, 10);
  const row = getDb().prepare(`
    SELECT COALESCE(SUM(estimated_cost), 0) as total FROM api_usage WHERE date = ?
  `).get(d);
  return row.total;
}

// Tasks
export function addTask({ source, kind, conversationId = null, skill = null, args = null, createsFiles = false }) {
  const argsJson = args ? safeStringify(args) : null;
  const stmt = getDb().prepare(`
    INSERT INTO tasks (source, kind, conversation_id, skill, args_json, creates_files, state)
    VALUES (?, ?, ?, ?, ?, ?, 'queued')
  `);
  const result = stmt.run(source, kind, conversationId, skill, argsJson, createsFiles ? 1 : 0);
  return Number(result.lastInsertRowid);
}

export function getTaskById(taskId) {
  return getDb().prepare(`SELECT * FROM tasks WHERE id = ?`).get(taskId);
}

export function setTaskRunning(taskId) {
  getDb().prepare(`
    UPDATE tasks
    SET state = 'running', started_at = datetime('now'), error = NULL
    WHERE id = ?
  `).run(taskId);
}

export function setTaskComplete(taskId) {
  getDb().prepare(`
    UPDATE tasks
    SET state = 'completed', ended_at = datetime('now')
    WHERE id = ?
  `).run(taskId);
}

export function setTaskFailed(taskId, error) {
  getDb().prepare(`
    UPDATE tasks
    SET state = 'failed', error = ?, ended_at = datetime('now')
    WHERE id = ?
  `).run(error || 'Task failed', taskId);
}

export function setTaskCanceled(taskId) {
  getDb().prepare(`
    UPDATE tasks
    SET state = 'canceled', ended_at = datetime('now')
    WHERE id = ?
  `).run(taskId);
}

export function getTasks({ limit = 100, state } = {}) {
  if (state) {
    return getDb().prepare(`
      SELECT * FROM tasks
      WHERE state = ?
      ORDER BY id DESC
      LIMIT ?
    `).all(state, limit);
  }
  return getDb().prepare(`
    SELECT * FROM tasks
    ORDER BY id DESC
    LIMIT ?
  `).all(limit);
}

// ── Knowledge Graph: Entities ─────────────────────────────────

export function addEntity(name, type, workspaceId = 'default', metadata = null) {
  const meta = metadata ? (typeof metadata === 'string' ? metadata : safeStringify(metadata)) : null;
  const stmt = getDb().prepare(`
    INSERT INTO kg_entities (name, type, workspace_id, metadata)
    VALUES (?, ?, ?, ?)
    ON CONFLICT(name, type, workspace_id) DO UPDATE SET
      metadata = COALESCE(excluded.metadata, kg_entities.metadata),
      updated_at = datetime('now')
  `);
  stmt.run(name, type, workspaceId, meta);

  // Always look up actual id (lastInsertRowid unreliable on upsert)
  const row = getDb().prepare(
    'SELECT id FROM kg_entities WHERE name = ? AND type = ? AND workspace_id = ?'
  ).get(name, type, workspaceId);
  const id = row.id;

  // Sync FTS index
  try {
    getDb().prepare(`
      INSERT OR REPLACE INTO kg_entities_fts(rowid, name, metadata) VALUES (?, ?, ?)
    `).run(id, name, meta || '');
  } catch {}

  return id;
}

export function getEntity(id) {
  return getDb().prepare('SELECT * FROM kg_entities WHERE id = ?').get(id);
}

export function getEntityByName(name, type, workspaceId = 'default') {
  return getDb().prepare(
    'SELECT * FROM kg_entities WHERE name = ? AND type = ? AND workspace_id = ?'
  ).get(name, type, workspaceId);
}

export function findEntities(query, { workspaceId, type, limit = 50 } = {}) {
  // Try FTS5 first, fall back to LIKE search
  try {
    let sql = `
      SELECT e.* FROM kg_entities e
      JOIN kg_entities_fts fts ON e.id = fts.rowid
      WHERE kg_entities_fts MATCH ?
    `;
    const params = [query];
    if (workspaceId) { sql += ' AND e.workspace_id = ?'; params.push(workspaceId); }
    if (type) { sql += ' AND e.type = ?'; params.push(type); }
    sql += ' LIMIT ?';
    params.push(limit);
    return getDb().prepare(sql).all(...params);
  } catch {
    // Fallback: LIKE search
    let sql = 'SELECT * FROM kg_entities WHERE (name LIKE ? OR metadata LIKE ?)';
    const likeQuery = `%${query}%`;
    const params = [likeQuery, likeQuery];
    if (workspaceId) { sql += ' AND workspace_id = ?'; params.push(workspaceId); }
    if (type) { sql += ' AND type = ?'; params.push(type); }
    sql += ' ORDER BY updated_at DESC LIMIT ?';
    params.push(limit);
    return getDb().prepare(sql).all(...params);
  }
}

export function getEntitiesByType(type, workspaceId) {
  if (workspaceId) {
    return getDb().prepare(
      'SELECT * FROM kg_entities WHERE type = ? AND workspace_id = ? ORDER BY updated_at DESC'
    ).all(type, workspaceId);
  }
  return getDb().prepare(
    'SELECT * FROM kg_entities WHERE type = ? ORDER BY updated_at DESC'
  ).all(type);
}

export function getEntitiesByWorkspace(workspaceId, { limit = 100 } = {}) {
  return getDb().prepare(
    'SELECT * FROM kg_entities WHERE workspace_id = ? ORDER BY updated_at DESC LIMIT ?'
  ).all(workspaceId, limit);
}

export function deleteEntity(id) {
  getDb().prepare('DELETE FROM kg_relations WHERE source_id = ? OR target_id = ?').run(id, id);
  try { getDb().prepare('DELETE FROM kg_entities_fts WHERE rowid = ?').run(id); } catch {}
  getDb().prepare('DELETE FROM kg_entities WHERE id = ?').run(id);
}

export function getEntityCount(workspaceId) {
  if (workspaceId) {
    return getDb().prepare('SELECT COUNT(*) as c FROM kg_entities WHERE workspace_id = ?').get(workspaceId).c;
  }
  return getDb().prepare('SELECT COUNT(*) as c FROM kg_entities').get().c;
}

// ── Knowledge Graph: Relations ────────────────────────────────

export function addRelation(sourceId, targetId, relation, weight = 1.0, metadata = null) {
  const meta = metadata ? (typeof metadata === 'string' ? metadata : safeStringify(metadata)) : null;
  const stmt = getDb().prepare(`
    INSERT INTO kg_relations (source_id, target_id, relation, weight, metadata)
    VALUES (?, ?, ?, ?, ?)
  `);
  const result = stmt.run(sourceId, targetId, relation, weight, meta);
  return Number(result.lastInsertRowid);
}

export function getRelations(entityId) {
  return getDb().prepare(`
    SELECT r.*,
      s.name as source_name, s.type as source_type,
      t.name as target_name, t.type as target_type
    FROM kg_relations r
    JOIN kg_entities s ON r.source_id = s.id
    JOIN kg_entities t ON r.target_id = t.id
    WHERE r.source_id = ? OR r.target_id = ?
    ORDER BY r.weight DESC
  `).all(entityId, entityId);
}

export function getOutgoingRelations(entityId, relation) {
  if (relation) {
    return getDb().prepare(`
      SELECT r.*, t.name as target_name, t.type as target_type
      FROM kg_relations r JOIN kg_entities t ON r.target_id = t.id
      WHERE r.source_id = ? AND r.relation = ?
      ORDER BY r.weight DESC
    `).all(entityId, relation);
  }
  return getDb().prepare(`
    SELECT r.*, t.name as target_name, t.type as target_type
    FROM kg_relations r JOIN kg_entities t ON r.target_id = t.id
    WHERE r.source_id = ?
    ORDER BY r.weight DESC
  `).all(entityId);
}

export function deleteRelation(id) {
  getDb().prepare('DELETE FROM kg_relations WHERE id = ?').run(id);
}

export function getRelationCount() {
  return getDb().prepare('SELECT COUNT(*) as c FROM kg_relations').get().c;
}

// ── Workspaces ────────────────────────────────────────────────

export function addWorkspace(id, name, rootPath, description = '') {
  const stmt = getDb().prepare(`
    INSERT INTO workspaces (id, name, root_path, description)
    VALUES (?, ?, ?, ?)
    ON CONFLICT(id) DO UPDATE SET
      name = excluded.name,
      root_path = excluded.root_path,
      description = excluded.description,
      updated_at = datetime('now')
  `);
  stmt.run(id, name, rootPath, description);
}

export function getWorkspace(id) {
  return getDb().prepare('SELECT * FROM workspaces WHERE id = ?').get(id);
}

export function listWorkspacesFromDb() {
  return getDb().prepare('SELECT * FROM workspaces ORDER BY name').all();
}

export function setActiveWorkspace(id) {
  const txn = getDb().transaction(() => {
    getDb().prepare('UPDATE workspaces SET is_active = 0').run();
    getDb().prepare('UPDATE workspaces SET is_active = 1 WHERE id = ?').run(id);
  });
  txn();
}

export function getActiveWorkspace() {
  return getDb().prepare('SELECT * FROM workspaces WHERE is_active = 1').get() || null;
}

export function deleteWorkspaceFromDb(id) {
  if (id === 'default') throw new Error('Cannot delete the default workspace');
  getDb().prepare('DELETE FROM workspaces WHERE id = ?').run(id);
}

// ── Ingestion Queue ───────────────────────────────────────────

export function addIngestionItem(sourceType, sourcePath, workspaceId = 'default', contentHash = null) {
  const stmt = getDb().prepare(`
    INSERT INTO ingestion_queue (source_type, source_path, workspace_id, content_hash)
    VALUES (?, ?, ?, ?)
  `);
  const result = stmt.run(sourceType, sourcePath, workspaceId, contentHash);
  return Number(result.lastInsertRowid);
}

export function getNextIngestion(limit = 1) {
  return getDb().prepare(`
    SELECT * FROM ingestion_queue
    WHERE state = 'queued'
    ORDER BY id ASC
    LIMIT ?
  `).all(limit);
}

export function setIngestionProcessing(id) {
  getDb().prepare(`UPDATE ingestion_queue SET state = 'processing' WHERE id = ?`).run(id);
}

export function completeIngestion(id, resultEntityId = null) {
  getDb().prepare(`
    UPDATE ingestion_queue
    SET state = 'completed', result_entity_id = ?, completed_at = datetime('now')
    WHERE id = ?
  `).run(resultEntityId, id);
}

export function failIngestion(id, error) {
  getDb().prepare(`
    UPDATE ingestion_queue
    SET state = 'failed', error = ?, completed_at = datetime('now')
    WHERE id = ?
  `).run(error || 'Ingestion failed', id);
}

export function isIngestionQueued(sourcePath) {
  const row = getDb().prepare(
    `SELECT id FROM ingestion_queue WHERE source_path = ? AND state IN ('queued', 'processing') LIMIT 1`
  ).get(sourcePath);
  return !!row;
}

export function getIngestionQueue({ state, limit = 50 } = {}) {
  if (state) {
    return getDb().prepare(
      'SELECT * FROM ingestion_queue WHERE state = ? ORDER BY id DESC LIMIT ?'
    ).all(state, limit);
  }
  return getDb().prepare(
    'SELECT * FROM ingestion_queue ORDER BY id DESC LIMIT ?'
  ).all(limit);
}

// ── Cognitive Log ─────────────────────────────────────────────

export function addCognitiveLog(module, inputSummary, outputSummary, tokensUsed = 0, durationMs = 0, metadata = null) {
  const meta = metadata ? (typeof metadata === 'string' ? metadata : safeStringify(metadata)) : null;
  const stmt = getDb().prepare(`
    INSERT INTO cognitive_log (module, input_summary, output_summary, tokens_used, duration_ms, metadata)
    VALUES (?, ?, ?, ?, ?, ?)
  `);
  const result = stmt.run(module, inputSummary, outputSummary, tokensUsed, durationMs, meta);
  return Number(result.lastInsertRowid);
}

export function getCognitiveLogs(module, limit = 20) {
  if (module) {
    return getDb().prepare(
      'SELECT * FROM cognitive_log WHERE module = ? ORDER BY id DESC LIMIT ?'
    ).all(module, limit);
  }
  return getDb().prepare(
    'SELECT * FROM cognitive_log ORDER BY id DESC LIMIT ?'
  ).all(limit);
}

// ── Procedural Memory ─────────────────────────────────────────

export function addProceduralMemory(skillName, inputPattern, approach = 'standard', outcome = 'success', qualityScore = 1.0) {
  getDb().prepare(`
    INSERT INTO procedural_memory (skill_name, input_pattern, approach, outcome, quality_score)
    VALUES (?, ?, ?, ?, ?)
  `).run(skillName, inputPattern, approach, outcome, qualityScore);
}

export function getProceduralMemories(skillName, limit = 20) {
  if (skillName) {
    return getDb().prepare(
      'SELECT * FROM procedural_memory WHERE skill_name = ? ORDER BY id DESC LIMIT ?'
    ).all(skillName, limit);
  }
  return getDb().prepare(
    'SELECT * FROM procedural_memory ORDER BY id DESC LIMIT ?'
  ).all(limit);
}

// ── Event Archival ─────────────────────────────────────────────

export function archiveOldEvents(db, keepDays = 30) {
  const d = db || getDb();
  const total = d.prepare('SELECT COUNT(*) as c FROM events').get().c;
  if (total <= 10000) return { archived: 0, total };

  const cutoff = new Date(Date.now() - keepDays * 24 * 60 * 60 * 1000)
    .toISOString().replace('T', ' ').slice(0, 19);

  d.exec(`
    CREATE TABLE IF NOT EXISTS events_archive (
      id INTEGER PRIMARY KEY,
      type TEXT NOT NULL,
      source TEXT NOT NULL,
      content TEXT NOT NULL,
      metadata TEXT,
      created_at TEXT DEFAULT (datetime('now')),
      processed INTEGER DEFAULT 0
    )
  `);

  const archiveTxn = d.transaction(() => {
    d.prepare(`
      INSERT OR IGNORE INTO events_archive
      SELECT * FROM events WHERE created_at < ?
    `).run(cutoff);
    const result = d.prepare(`
      DELETE FROM events WHERE created_at < ?
    `).run(cutoff);
    return result.changes;
  });

  const archived = archiveTxn();
  return { archived, total };
}
