database.js

import fs from 'fs';
import path from 'path';
import os from 'os';
import { createRequire } from 'module';

const require = createRequire(import.meta.url);

function getDataDir() {
  if (process.env.PORTABLE_DATA_DIR) {
    return process.env.PORTABLE_DATA_DIR;
  }
  const exeDir = process.pkg?.path ? path.dirname(process.pkg.path) : null;
  if (exeDir) {
    return path.join(exeDir, 'data');
  }
  if (process.env.BUN_BE_BUN && process.argv[1]) {
    return path.join(path.dirname(process.argv[1]), 'data');
  }
  return path.join(os.homedir(), '.gmgui');
}

export const dataDir = getDataDir();
const dbDir = dataDir;
const dbFilePath = path.join(dbDir, 'data.db');
const oldJsonPath = path.join(dbDir, 'data.json');

if (!fs.existsSync(dbDir)) {
  fs.mkdirSync(dbDir, { recursive: true });
}

let db;
try {
  const Database = (await import('bun:sqlite')).default;
  db = new Database(dbFilePath);
  db.run('PRAGMA journal_mode = WAL');
  db.run('PRAGMA foreign_keys = ON');
  db.run('PRAGMA encoding = "UTF-8"');
  db.run('PRAGMA synchronous = NORMAL');
  db.run('PRAGMA busy_timeout = 5000');
  db.run('PRAGMA cache_size = -64000');
  db.run('PRAGMA mmap_size = 268435456');
  db.run('PRAGMA temp_store = MEMORY');
} catch (e) {
  try {
    const sqlite3 = require('better-sqlite3');
    db = new sqlite3(dbFilePath);
    db.pragma('journal_mode = WAL');
    db.pragma('foreign_keys = ON');
    db.pragma('encoding = "UTF-8"');
    db.pragma('synchronous = NORMAL');
    db.pragma('busy_timeout = 5000');
    db.pragma('cache_size = -64000');
    db.pragma('mmap_size = 268435456');
    db.pragma('temp_store = MEMORY');
  } catch (e2) {
    throw new Error('SQLite database is required. Please run with bun (recommended) or install better-sqlite3: npm install better-sqlite3');
  }
}

function initSchema() {
  // Create table with minimal schema - columns will be added by migration
  db.exec(`
    CREATE TABLE IF NOT EXISTS conversations (
      id TEXT PRIMARY KEY,
      agentId TEXT NOT NULL,
      title TEXT,
      created_at INTEGER NOT NULL,
      updated_at INTEGER NOT NULL,
      status TEXT DEFAULT 'active'
    );

    CREATE INDEX IF NOT EXISTS idx_conversations_agent ON conversations(agentId);
    CREATE INDEX IF NOT EXISTS idx_conversations_updated ON conversations(updated_at DESC);

    CREATE TABLE IF NOT EXISTS messages (
      id TEXT PRIMARY KEY,
      conversationId TEXT NOT NULL,
      role TEXT NOT NULL,
      content TEXT NOT NULL,
      created_at INTEGER NOT NULL,
      FOREIGN KEY (conversationId) REFERENCES conversations(id)
    );

    CREATE INDEX IF NOT EXISTS idx_messages_conversation ON messages(conversationId);

    CREATE TABLE IF NOT EXISTS sessions (
      id TEXT PRIMARY KEY,
      conversationId TEXT NOT NULL,
      status TEXT NOT NULL,
      started_at INTEGER NOT NULL,
      completed_at INTEGER,
      response TEXT,
      error TEXT,
      FOREIGN KEY (conversationId) REFERENCES conversations(id)
    );

    CREATE INDEX IF NOT EXISTS idx_sessions_conversation ON sessions(conversationId);
    CREATE INDEX IF NOT EXISTS idx_sessions_status ON sessions(conversationId, status);

    CREATE TABLE IF NOT EXISTS events (
      id TEXT PRIMARY KEY,
      type TEXT NOT NULL,
      conversationId TEXT,
      sessionId TEXT,
      data TEXT NOT NULL,
      created_at INTEGER NOT NULL,
      FOREIGN KEY (conversationId) REFERENCES conversations(id),
      FOREIGN KEY (sessionId) REFERENCES sessions(id)
    );

    CREATE INDEX IF NOT EXISTS idx_events_conversation ON events(conversationId);

    CREATE TABLE IF NOT EXISTS idempotencyKeys (
      key TEXT PRIMARY KEY,
      value TEXT NOT NULL,
      created_at INTEGER NOT NULL,
      ttl INTEGER NOT NULL
    );

    CREATE INDEX IF NOT EXISTS idx_idempotency_created ON idempotencyKeys(created_at);

    CREATE TABLE IF NOT EXISTS stream_updates (
      id TEXT PRIMARY KEY,
      sessionId TEXT NOT NULL,
      conversationId TEXT NOT NULL,
      updateType TEXT NOT NULL,
      content TEXT NOT NULL,
      sequence INTEGER NOT NULL,
      created_at INTEGER NOT NULL,
      FOREIGN KEY (sessionId) REFERENCES sessions(id),
      FOREIGN KEY (conversationId) REFERENCES conversations(id)
    );

    CREATE INDEX IF NOT EXISTS idx_stream_updates_session ON stream_updates(sessionId);
    CREATE INDEX IF NOT EXISTS idx_stream_updates_created ON stream_updates(created_at);

    CREATE TABLE IF NOT EXISTS chunks (
      id TEXT PRIMARY KEY,
      sessionId TEXT NOT NULL,
      conversationId TEXT NOT NULL,
      sequence INTEGER NOT NULL,
      type TEXT NOT NULL,
      data BLOB NOT NULL,
      created_at INTEGER NOT NULL,
      FOREIGN KEY (sessionId) REFERENCES sessions(id),
      FOREIGN KEY (conversationId) REFERENCES conversations(id)
    );

    CREATE INDEX IF NOT EXISTS idx_chunks_session ON chunks(sessionId, sequence);
    CREATE INDEX IF NOT EXISTS idx_chunks_conversation ON chunks(conversationId, sequence);
    CREATE UNIQUE INDEX IF NOT EXISTS idx_chunks_unique ON chunks(sessionId, sequence);
    CREATE INDEX IF NOT EXISTS idx_chunks_conv_created ON chunks(conversationId, created_at);
    CREATE INDEX IF NOT EXISTS idx_chunks_sess_created ON chunks(sessionId, created_at);

    CREATE TABLE IF NOT EXISTS voice_cache (
      id TEXT PRIMARY KEY,
      conversationId TEXT NOT NULL,
      text TEXT NOT NULL,
      audioBlob BLOB,
      byteSize INTEGER NOT NULL,
      created_at INTEGER NOT NULL,
      expires_at INTEGER NOT NULL,
      FOREIGN KEY (conversationId) REFERENCES conversations(id)
    );

    CREATE INDEX IF NOT EXISTS idx_voice_cache_conv ON voice_cache(conversationId);
    CREATE INDEX IF NOT EXISTS idx_voice_cache_expires ON voice_cache(expires_at);

    CREATE TABLE IF NOT EXISTS tool_installations (
      id TEXT PRIMARY KEY,
      tool_id TEXT NOT NULL UNIQUE,
      version TEXT,
      installed_at INTEGER,
      status TEXT NOT NULL DEFAULT 'not_installed',
      last_check_at INTEGER,
      error_message TEXT,
      update_available INTEGER DEFAULT 0,
      latest_version TEXT,
      created_at INTEGER NOT NULL,
      updated_at INTEGER NOT NULL
    );

    CREATE INDEX IF NOT EXISTS idx_tool_installations_status ON tool_installations(status);
    CREATE INDEX IF NOT EXISTS idx_tool_installations_last_check ON tool_installations(last_check_at);

    CREATE TABLE IF NOT EXISTS tool_install_history (
      id TEXT PRIMARY KEY,
      tool_id TEXT NOT NULL,
      action TEXT NOT NULL,
      started_at INTEGER NOT NULL,
      completed_at INTEGER,
      status TEXT NOT NULL,
      error_message TEXT,
      created_at INTEGER NOT NULL,
      FOREIGN KEY (tool_id) REFERENCES tool_installations(tool_id)
    );

    CREATE INDEX IF NOT EXISTS idx_tool_install_history_tool ON tool_install_history(tool_id);
    CREATE INDEX IF NOT EXISTS idx_tool_install_history_completed ON tool_install_history(completed_at);

    CREATE TABLE IF NOT EXISTS workflow_runs (
      id TEXT PRIMARY KEY,
      workflowName TEXT NOT NULL,
      workflowId TEXT,
      runId TEXT,
      sha TEXT,
      branch TEXT,
      status TEXT,
      conclusion TEXT,
      htmlUrl TEXT,
      triggeredAt INTEGER NOT NULL,
      completedAt INTEGER,
      created_at INTEGER NOT NULL
    );

    CREATE INDEX IF NOT EXISTS idx_workflow_runs_name ON workflow_runs(workflowName);
    CREATE INDEX IF NOT EXISTS idx_workflow_runs_sha ON workflow_runs(sha);
    CREATE INDEX IF NOT EXISTS idx_workflow_runs_completed ON workflow_runs(completedAt);

    CREATE TABLE IF NOT EXISTS oauth_tokens (
      id TEXT PRIMARY KEY,
      provider TEXT NOT NULL,
      token TEXT NOT NULL,
      email TEXT,
      expires_at INTEGER,
      created_at INTEGER NOT NULL,
      updated_at INTEGER NOT NULL
    );

    CREATE INDEX IF NOT EXISTS idx_oauth_tokens_provider ON oauth_tokens(provider);

  `);
}

function migrateFromJson() {
  if (!fs.existsSync(oldJsonPath)) return;

  try {
    const content = fs.readFileSync(oldJsonPath, 'utf-8');
    const data = JSON.parse(content);

    const migrationStmt = db.transaction(() => {
      if (data.conversations) {
        for (const id in data.conversations) {
          const conv = data.conversations[id];
          db.prepare(
            `INSERT OR REPLACE INTO conversations (id, agentId, title, created_at, updated_at, status) VALUES (?, ?, ?, ?, ?, ?)`
          ).run(conv.id, conv.agentId, conv.title || null, conv.created_at, conv.updated_at, conv.status || 'active');
        }
      }

       if (data.messages) {
         for (const id in data.messages) {
           const msg = data.messages[id];
           // Ensure content is always a string (stringify objects)
           const contentStr = typeof msg.content === 'string' ? msg.content : JSON.stringify(msg.content);
           db.prepare(
             `INSERT OR REPLACE INTO messages (id, conversationId, role, content, created_at) VALUES (?, ?, ?, ?, ?)`
           ).run(msg.id, msg.conversationId, msg.role, contentStr, msg.created_at);
         }
       }

       if (data.sessions) {
         for (const id in data.sessions) {
           const sess = data.sessions[id];
           // Ensure response and error are strings, not objects
           const responseStr = sess.response ? (typeof sess.response === 'string' ? sess.response : JSON.stringify(sess.response)) : null;
           const errorStr = sess.error ? (typeof sess.error === 'string' ? sess.error : JSON.stringify(sess.error)) : null;
           db.prepare(
             `INSERT OR REPLACE INTO sessions (id, conversationId, status, started_at, completed_at, response, error) VALUES (?, ?, ?, ?, ?, ?, ?)`
           ).run(sess.id, sess.conversationId, sess.status, sess.started_at, sess.completed_at || null, responseStr, errorStr);
         }
       }

       if (data.events) {
         for (const id in data.events) {
           const evt = data.events[id];
           // Ensure data is always valid JSON string
           const dataStr = typeof evt.data === 'string' ? evt.data : JSON.stringify(evt.data || {});
           db.prepare(
             `INSERT OR REPLACE INTO events (id, type, conversationId, sessionId, data, created_at) VALUES (?, ?, ?, ?, ?, ?)`
           ).run(evt.id, evt.type, evt.conversationId || null, evt.sessionId || null, dataStr, evt.created_at);
         }
       }

       if (data.idempotencyKeys) {
         for (const key in data.idempotencyKeys) {
           const entry = data.idempotencyKeys[key];
           // Ensure value is always valid JSON string
           const valueStr = typeof entry.value === 'string' ? entry.value : JSON.stringify(entry.value || {});
           // Ensure ttl is a number
           const ttl = typeof entry.ttl === 'number' ? entry.ttl : (entry.ttl ? parseInt(entry.ttl) : 0);
           db.prepare(
             `INSERT OR REPLACE INTO idempotencyKeys (key, value, created_at, ttl) VALUES (?, ?, ?, ?)`
           ).run(key, valueStr, entry.created_at, ttl);
         }
       }
    });

    migrationStmt();
    fs.renameSync(oldJsonPath, `${oldJsonPath}.migrated`);
    console.log('Migrated data from JSON to SQLite');
  } catch (e) {
    console.error('Error during migration:', e.message);
  }
}

function migrateToACP() {
  try {
    const migrate = db.transaction(() => {
      // Create new tables for ACP support
      db.exec(`
        CREATE TABLE IF NOT EXISTS thread_states (
          id TEXT PRIMARY KEY,
          thread_id TEXT NOT NULL,
          checkpoint_id TEXT,
          state_data TEXT NOT NULL,
          created_at INTEGER NOT NULL,
          FOREIGN KEY (thread_id) REFERENCES conversations(id) ON DELETE CASCADE,
          FOREIGN KEY (checkpoint_id) REFERENCES checkpoints(id) ON DELETE SET NULL
        )
      `);

      db.exec(`
        CREATE TABLE IF NOT EXISTS checkpoints (
          id TEXT PRIMARY KEY,
          thread_id TEXT NOT NULL,
          checkpoint_name TEXT NOT NULL,
          sequence INTEGER NOT NULL,
          created_at INTEGER NOT NULL,
          FOREIGN KEY (thread_id) REFERENCES conversations(id) ON DELETE CASCADE
        )
      `);

      db.exec(`
        CREATE TABLE IF NOT EXISTS run_metadata (
          id TEXT PRIMARY KEY,
          run_id TEXT NOT NULL UNIQUE,
          thread_id TEXT,
          agent_id TEXT NOT NULL,
          status TEXT NOT NULL DEFAULT 'pending',
          input TEXT,
          config TEXT,
          webhook_url TEXT,
          created_at INTEGER NOT NULL,
          updated_at INTEGER NOT NULL,
          FOREIGN KEY (thread_id) REFERENCES conversations(id) ON DELETE CASCADE
        )
      `);

      // Add new columns to existing tables
      const convCols = db.prepare("PRAGMA table_info(conversations)").all();
      const convColNames = convCols.map(c => c.name);

      if (!convColNames.includes('metadata')) {
        db.exec('ALTER TABLE conversations ADD COLUMN metadata TEXT');
      }

      const sessCols = db.prepare("PRAGMA table_info(sessions)").all();
      const sessColNames = sessCols.map(c => c.name);

      const sessionCols = {
        run_id: 'TEXT',
        input: 'TEXT',
        config: 'TEXT',
        interrupt: 'TEXT'
      };

      for (const [colName, colType] of Object.entries(sessionCols)) {
        if (!sessColNames.includes(colName)) {
          db.exec(`ALTER TABLE sessions ADD COLUMN ${colName} ${colType}`);
        }
      }

      // Create indexes
      db.exec(`
        CREATE INDEX IF NOT EXISTS idx_thread_states_thread ON thread_states(thread_id);
        CREATE INDEX IF NOT EXISTS idx_thread_states_checkpoint ON thread_states(checkpoint_id);
        CREATE INDEX IF NOT EXISTS idx_thread_states_created ON thread_states(created_at);

        CREATE INDEX IF NOT EXISTS idx_checkpoints_thread ON checkpoints(thread_id);
        CREATE INDEX IF NOT EXISTS idx_checkpoints_sequence ON checkpoints(thread_id, sequence);
        CREATE UNIQUE INDEX IF NOT EXISTS idx_checkpoints_unique_seq ON checkpoints(thread_id, sequence);

        CREATE INDEX IF NOT EXISTS idx_run_metadata_run_id ON run_metadata(run_id);
        CREATE INDEX IF NOT EXISTS idx_run_metadata_thread ON run_metadata(thread_id);
        CREATE INDEX IF NOT EXISTS idx_run_metadata_status ON run_metadata(status);
        CREATE INDEX IF NOT EXISTS idx_run_metadata_agent ON run_metadata(agent_id);
        CREATE INDEX IF NOT EXISTS idx_run_metadata_created ON run_metadata(created_at);

        CREATE INDEX IF NOT EXISTS idx_sessions_run_id ON sessions(run_id);
      `);
    });

    migrate();
  } catch (err) {
    console.error('[Migration] ACP schema migration error:', err.message);
  }
}

initSchema();
migrateFromJson();
migrateToACP();

// Migration: Add imported conversation columns if they don't exist
try {
  const result = db.prepare("PRAGMA table_info(conversations)").all();
  const columnNames = result.map(r => r.name);
  const requiredColumns = {
    agentType: 'TEXT',
    source: 'TEXT DEFAULT "gui"',
    externalId: 'TEXT',
    firstPrompt: 'TEXT',
    messageCount: 'INTEGER DEFAULT 0',
    projectPath: 'TEXT',
    gitBranch: 'TEXT',
    sourcePath: 'TEXT',
    lastSyncedAt: 'INTEGER',
    workingDirectory: 'TEXT',
    claudeSessionId: 'TEXT',
    isStreaming: 'INTEGER DEFAULT 0',
    model: 'TEXT',
    subAgent: 'TEXT',
    pinned: 'INTEGER DEFAULT 0',
    tags: 'TEXT',
    sortOrder: 'INTEGER DEFAULT 0'
  };

  let addedColumns = false;
  for (const [colName, colDef] of Object.entries(requiredColumns)) {
    if (!columnNames.includes(colName)) {
      db.exec(`ALTER TABLE conversations ADD COLUMN ${colName} ${colDef}`);
      console.log(`[Migration] Added column ${colName} to conversations table`);
      addedColumns = true;
    }
  }

  // Add indexes for new columns
  if (addedColumns) {
    try {
      db.exec(`CREATE INDEX IF NOT EXISTS idx_conversations_external ON conversations(externalId)`);
      db.exec(`CREATE INDEX IF NOT EXISTS idx_conversations_agent_type ON conversations(agentType)`);
      db.exec(`CREATE INDEX IF NOT EXISTS idx_conversations_source ON conversations(source)`);
    } catch (e) {
      console.warn('[Migration] Index creation warning:', e.message);
    }
  }
} catch (err) {
  console.error('[Migration] Error:', err.message);
}

// Migration: Add resume capability columns (disabled - incomplete migration)
// This migration block was incomplete and has been removed

// ============ ACP SCHEMA MIGRATION ============
try {
  console.log('[Migration] Running ACP schema migration...');

  // Add metadata column to conversations if not exists
  const convColsACP = db.prepare("PRAGMA table_info(conversations)").all().map(c => c.name);
  if (!convColsACP.includes('metadata')) {
    db.exec('ALTER TABLE conversations ADD COLUMN metadata TEXT DEFAULT "{}"');
    console.log('[Migration] Added metadata column to conversations');
  }

  // Add run_id, input, config, interrupt to sessions if not exists
  const sessColsACP = db.prepare("PRAGMA table_info(sessions)").all().map(c => c.name);
  if (!sessColsACP.includes('run_id')) {
    db.exec('ALTER TABLE sessions ADD COLUMN run_id TEXT');
    console.log('[Migration] Added run_id column to sessions');
  }
  if (!sessColsACP.includes('input')) {
    db.exec('ALTER TABLE sessions ADD COLUMN input TEXT');
    console.log('[Migration] Added input column to sessions');
  }
  if (!sessColsACP.includes('config')) {
    db.exec('ALTER TABLE sessions ADD COLUMN config TEXT');
    console.log('[Migration] Added config column to sessions');
  }
  if (!sessColsACP.includes('interrupt')) {
    db.exec('ALTER TABLE sessions ADD COLUMN interrupt TEXT');
    console.log('[Migration] Added interrupt column to sessions');
  }
  if (!sessColsACP.includes('claudeSessionId')) {
    db.exec('ALTER TABLE sessions ADD COLUMN claudeSessionId TEXT');
    console.log('[Migration] Added claudeSessionId column to sessions');
  }

  // Create ACP tables
  db.exec(`
    CREATE TABLE IF NOT EXISTS thread_states (
      id TEXT PRIMARY KEY,
      thread_id TEXT NOT NULL,
      checkpoint_id TEXT NOT NULL,
      state_data TEXT NOT NULL,
      created_at INTEGER NOT NULL,
      FOREIGN KEY (thread_id) REFERENCES conversations(id) ON DELETE CASCADE
    );

    CREATE INDEX IF NOT EXISTS idx_thread_states_thread ON thread_states(thread_id);
    CREATE INDEX IF NOT EXISTS idx_thread_states_checkpoint ON thread_states(checkpoint_id);
    CREATE INDEX IF NOT EXISTS idx_thread_states_created ON thread_states(created_at);

    CREATE TABLE IF NOT EXISTS checkpoints (
      id TEXT PRIMARY KEY,
      thread_id TEXT NOT NULL,
      checkpoint_name TEXT,
      sequence INTEGER NOT NULL,
      created_at INTEGER NOT NULL,
      FOREIGN KEY (thread_id) REFERENCES conversations(id) ON DELETE CASCADE
    );

    CREATE INDEX IF NOT EXISTS idx_checkpoints_thread ON checkpoints(thread_id);
    CREATE INDEX IF NOT EXISTS idx_checkpoints_sequence ON checkpoints(thread_id, sequence);
    CREATE UNIQUE INDEX IF NOT EXISTS idx_checkpoints_unique ON checkpoints(thread_id, sequence);

    CREATE TABLE IF NOT EXISTS run_metadata (
      run_id TEXT PRIMARY KEY,
      thread_id TEXT,
      agent_id TEXT NOT NULL,
      status TEXT NOT NULL,
      input TEXT,
      config TEXT,
      webhook_url TEXT,
      created_at INTEGER NOT NULL,
      updated_at INTEGER NOT NULL,
      FOREIGN KEY (thread_id) REFERENCES conversations(id) ON DELETE CASCADE,
      FOREIGN KEY (run_id) REFERENCES sessions(id) ON DELETE CASCADE
    );

    CREATE INDEX IF NOT EXISTS idx_run_metadata_thread ON run_metadata(thread_id);
    CREATE INDEX IF NOT EXISTS idx_run_metadata_agent ON run_metadata(agent_id);
    CREATE INDEX IF NOT EXISTS idx_run_metadata_status ON run_metadata(status);
    CREATE INDEX IF NOT EXISTS idx_run_metadata_created ON run_metadata(created_at);
  `);

  console.log('[Migration] ACP schema migration complete');
} catch (err) {
  console.error('[Migration] ACP schema migration error:', err.message);
}

// Migration: Backfill messages for conversations imported without message content
try {
  const emptyImported = db.prepare(`
    SELECT c.id, c.sourcePath FROM conversations c
    LEFT JOIN messages m ON c.id = m.conversationId
    WHERE c.sourcePath IS NOT NULL AND c.status != 'deleted'
    GROUP BY c.id HAVING COUNT(m.id) = 0
  `).all();

  if (emptyImported.length > 0) {
    console.log(`[Migration] Backfilling messages for ${emptyImported.length} imported conversation(s)`);
    const insertMsg = db.prepare(`INSERT OR IGNORE INTO messages (id, conversationId, role, content, created_at) VALUES (?, ?, ?, ?, ?)`);
    const backfill = db.transaction(() => {
      for (const conv of emptyImported) {
        if (!fs.existsSync(conv.sourcePath)) continue;
        try {
          const lines = fs.readFileSync(conv.sourcePath, 'utf-8').split('\n');
          let count = 0;
          for (const line of lines) {
            if (!line.trim()) continue;
            try {
              const obj = JSON.parse(line);
              const msgId = obj.uuid || `msg-${Date.now()}-${Math.random().toString(36).substr(2,9)}`;
              const ts = obj.timestamp ? new Date(obj.timestamp).getTime() : Date.now();
              if (obj.type === 'user' && obj.message?.content) {
                const raw = obj.message.content;
                const text = typeof raw === 'string' ? raw
                  : Array.isArray(raw) ? raw.filter(c => c.type === 'text').map(c => c.text).join('\n')
                  : JSON.stringify(raw);
                if (text && !text.startsWith('[{"tool_use_id"')) {
                  insertMsg.run(msgId, conv.id, 'user', text, ts);
                  count++;
                }
              } else if (obj.type === 'assistant' && obj.message?.content) {
                const raw = obj.message.content;
                const text = Array.isArray(raw)
                  ? raw.filter(c => c.type === 'text' && c.text).map(c => c.text).join('\n\n')
                  : typeof raw === 'string' ? raw : '';
                if (text) {
                  insertMsg.run(msgId, conv.id, 'assistant', text, ts);
                  count++;
                }
              }
            } catch (_) {}
          }
          if (count > 0) console.log(`[Migration] Backfilled ${count} messages for conversation ${conv.id}`);
        } catch (e) {
          console.error(`[Migration] Error backfilling ${conv.id}:`, e.message);
        }
      }
    });
    backfill();
  }
} catch (err) {
  console.error('[Migration] Backfill error:', err.message);
}

try {
  const hasFts = db.prepare("SELECT name FROM sqlite_master WHERE type='table' AND name='messages_fts'").get();
  if (!hasFts) {
    db.exec("CREATE VIRTUAL TABLE messages_fts USING fts5(content, conversationId UNINDEXED, role UNINDEXED, content_rowid='rowid')");
    const msgs = db.prepare("SELECT rowid, content, conversationId, role FROM messages").all();
    if (msgs.length > 0) {
      const ins = db.prepare("INSERT INTO messages_fts(rowid, content, conversationId, role) VALUES (?, ?, ?, ?)");
      const tx = db.transaction(() => { for (const m of msgs) ins.run(m.rowid, m.content, m.conversationId, m.role); });
      tx();
      console.log(`[Migration] FTS5 index created with ${msgs.length} messages`);
    }
  }
} catch (err) {
  console.error('[Migration] FTS5 error:', err.message);
}

const stmtCache = new Map();
function prep(sql) {
  let s = stmtCache.get(sql);
  if (!s) {
    s = db.prepare(sql);
    stmtCache.set(sql, s);
  }
  return s;
}

function generateId(prefix) {
  return `${prefix}-${Date.now()}-${Math.random().toString(36).substr(2, 9)}`;
}

import { createQueries } from './lib/db-queries.js';

export const queries = createQueries(db, prep, generateId);

export default { queries };