import Database from "better-sqlite3";
import path from "node:path";
import fs from "node:fs";
import { SYSTEM_PROMPT } from "./system-prompt";

const dataDir = path.resolve(process.cwd(), "data");
if (!fs.existsSync(dataDir)) {
  fs.mkdirSync(dataDir, { recursive: true });
}

const dbPath = path.join(dataDir, "messages.db");

let _dbInstance: Database.Database | null = null;

function getDb(): Database.Database {
  if (_dbInstance) return _dbInstance;

  _dbInstance = new Database(dbPath);
  
  // Si estamos en entorno de build de Next.js, evitamos ejecutar pragmas y queries
  // para no bloquear la base de datos concurrentemente.
  if (
    process.env.npm_lifecycle_event === "build" || 
    process.env.NEXT_PHASE === "phase-production-build" ||
    process.env.NODE_ENV === "production" && process.env.NEXT_BUILD === "true"
  ) {
    return _dbInstance;
  }

  // Configuración de concurrencia y DDL
  _dbInstance.pragma("journal_mode = WAL");
  _dbInstance.pragma("foreign_keys = ON");

  _dbInstance.exec(`
    CREATE TABLE IF NOT EXISTS conversations (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      phone TEXT UNIQUE NOT NULL,
      name TEXT,
      mode TEXT CHECK(mode IN ('AI','HUMAN')) NOT NULL DEFAULT 'HUMAN',
      last_message_at INTEGER,
      created_at INTEGER NOT NULL DEFAULT (unixepoch())
    );

    CREATE TABLE IF NOT EXISTS messages (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      conversation_id INTEGER NOT NULL REFERENCES conversations(id),
      role TEXT CHECK(role IN ('user','assistant','human')) NOT NULL,
      content TEXT NOT NULL,
      created_at INTEGER NOT NULL DEFAULT (unixepoch())
    );

    CREATE INDEX IF NOT EXISTS idx_messages_conv
      ON messages(conversation_id, created_at);

    CREATE TABLE IF NOT EXISTS connection_state (
      id INTEGER PRIMARY KEY CHECK (id = 1),
      status TEXT CHECK(status IN ('disconnected','qr','connecting','connected')) NOT NULL DEFAULT 'disconnected',
      qr_string TEXT,
      phone TEXT,
      updated_at INTEGER NOT NULL DEFAULT (unixepoch())
    );

    INSERT OR IGNORE INTO connection_state (id, status) VALUES (1, 'disconnected');

    CREATE TABLE IF NOT EXISTS outbox (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      conversation_id INTEGER NOT NULL,
      phone TEXT NOT NULL,
      content TEXT NOT NULL,
      sent INTEGER NOT NULL DEFAULT 0,
      created_at INTEGER NOT NULL DEFAULT (unixepoch())
    );

    CREATE INDEX IF NOT EXISTS idx_outbox_pending
      ON outbox(sent, created_at);

    CREATE TABLE IF NOT EXISTS settings (
      key TEXT PRIMARY KEY,
      value TEXT NOT NULL
    );

    INSERT OR IGNORE INTO settings (key, value) VALUES ('quick_replies', '[]');
    INSERT OR IGNORE INTO settings (key, value) VALUES ('auto_replies', '[]');
    INSERT OR IGNORE INTO settings (key, value) VALUES ('bot_name', 'IgnacIA');
    INSERT OR IGNORE INTO settings (key, value) VALUES ('welcome_enabled', 'false');
    INSERT OR IGNORE INTO settings (key, value) VALUES ('welcome_message', '¡Hola! Gracias por contactarnos. ¿En qué podemos ayudarte hoy?');
    INSERT OR IGNORE INTO settings (key, value) VALUES ('business_hours_enabled', 'false');
    INSERT OR IGNORE INTO settings (key, value) VALUES ('business_hours_start', '09:00');
    INSERT OR IGNORE INTO settings (key, value) VALUES ('business_hours_end', '18:00');
    INSERT OR IGNORE INTO settings (key, value) VALUES ('business_days', '[1,2,3,4,5]');
    INSERT OR IGNORE INTO settings (key, value) VALUES ('away_message', 'Estamos fuera del horario de atención. Te responderemos en cuanto sea posible 🙏');
    INSERT OR IGNORE INTO settings (key, value) VALUES ('bot_response_delay', '0');
    INSERT OR IGNORE INTO settings (key, value) VALUES ('reaction_enabled', 'true');
    INSERT OR IGNORE INTO settings (key, value) VALUES ('reaction_emoji', '👀');
    INSERT OR IGNORE INTO settings (key, value) VALUES ('commands_enabled', 'true');

    -- ── Fase 2: Persistencia de sesión WhatsApp en SQLite ──────────────────────
    CREATE TABLE IF NOT EXISTS wa_session_backup (
      file_name TEXT PRIMARY KEY,
      content   TEXT NOT NULL,
      updated_at INTEGER NOT NULL DEFAULT (unixepoch())
    );

    -- ── Fase 3: Webhooks (inspirado en OpenWA webhook.service.ts) ──────────────
    CREATE TABLE IF NOT EXISTS webhooks (
      id         TEXT PRIMARY KEY,
      url        TEXT NOT NULL,
      secret     TEXT,
      events     TEXT NOT NULL DEFAULT '["*"]',
      enabled    INTEGER NOT NULL DEFAULT 1,
      created_at INTEGER NOT NULL DEFAULT (unixepoch())
    );

    -- ── Fase 4: API Keys para acceso externo (inspirado en OpenWA api-key.guard) ─
    CREATE TABLE IF NOT EXISTS api_keys (
      id         TEXT PRIMARY KEY,
      key_hash   TEXT NOT NULL,
      label      TEXT,
      scopes     TEXT NOT NULL DEFAULT '["read","write"]',
      rate_limit INTEGER NOT NULL DEFAULT 60,
      created_at INTEGER NOT NULL DEFAULT (unixepoch())
    );

    -- ── Logs de Auditoría ──────────────────────────────────────────────────────
    CREATE TABLE IF NOT EXISTS audit_logs (
      id         INTEGER PRIMARY KEY AUTOINCREMENT,
      action     TEXT NOT NULL,
      details    TEXT,
      operator   TEXT NOT NULL DEFAULT 'sistema',
      created_at INTEGER NOT NULL DEFAULT (unixepoch())
    );

    -- ── CRM Leads ─────────────────────────────────────────────────────────────
    CREATE TABLE IF NOT EXISTS leads (
      id             INTEGER PRIMARY KEY AUTOINCREMENT,
      conversation_id INTEGER UNIQUE REFERENCES conversations(id) ON DELETE CASCADE,
      phone          TEXT NOT NULL,
      name           TEXT,
      email          TEXT,
      agency_name    TEXT,
      current_system TEXT,
      booking_volume TEXT,
      status         TEXT NOT NULL DEFAULT 'Nuevo',
      updated_at     INTEGER NOT NULL DEFAULT (unixepoch())
    );

    -- ── Base de Conocimiento ──────────────────────────────────────────────────
    CREATE TABLE IF NOT EXISTS knowledge_base (
      id         INTEGER PRIMARY KEY AUTOINCREMENT,
      title      TEXT NOT NULL,
      content    TEXT NOT NULL,
      category   TEXT,
      created_at INTEGER NOT NULL DEFAULT (unixepoch())
    );
  `);

  // Migraciones seguras para bases de datos existentes
  try {
    _dbInstance.exec("ALTER TABLE api_keys ADD COLUMN scopes TEXT NOT NULL DEFAULT '[\"read\",\"write\"]'");
    console.log("[db] ✅ Columna 'scopes' agregada a 'api_keys'.");
  } catch (err) {
    // Ya existe la columna
  }

  try {
    _dbInstance.exec("ALTER TABLE api_keys ADD COLUMN rate_limit INTEGER NOT NULL DEFAULT 60");
    console.log("[db] ✅ Columna 'rate_limit' agregada a 'api_keys'.");
  } catch (err) {
    // Ya existe la columna
  }

  _dbInstance.prepare("INSERT OR IGNORE INTO settings (key, value) VALUES ('system_prompt', ?)").run(SYSTEM_PROMPT);

  return _dbInstance;
}

// Proxy para que las exportaciones actuales que usan "db" funcionen sin modificar todo el archivo
const db = new Proxy({} as Database.Database, {
  get(target, prop) {
    const instance = getDb();
    const value = (instance as any)[prop];
    if (typeof value === 'function') {
      return value.bind(instance);
    }
    return value;
  }
});
export interface Conversation {
  id: number;
  phone: string;
  name: string | null;
  mode: "AI" | "HUMAN";
  last_message_at: number | null;
  created_at: number;
  last_message_preview?: string | null;
}

export interface Message {
  id: number;
  conversation_id: number;
  role: "user" | "assistant" | "human";
  content: string;
  created_at: number;
}

export interface ConnectionState {
  status: "disconnected" | "qr" | "connecting" | "connected";
  qr_string: string | null;
  phone: string | null;
  updated_at: number;
}

export interface OutboxItem {
  id: number;
  conversation_id: number;
  phone: string;
  content: string;
  sent: number;
  created_at: number;
}

// Helpers exportados

export function normalizeJid(jid: string): string {
  if (!jid) return "";
  const parts = jid.split("@");
  const user = parts[0].split(":")[0];
  let server = parts[1] || "s.whatsapp.net";

  // LID identifiers are typically 14 or 15 digits. Real phone numbers are 11-13 digits.
  // If the user part is a LID, force the server to be 'lid' for correct routing.
  if (user.length >= 14 && server === "s.whatsapp.net") {
    server = "lid";
  }
  return `${user}@${server}`;
}

export function getOrCreateConversation(phone: string, name?: string | null): Conversation {
  // Preserve full JID format (without session suffix :X)
  const cleanPhone = normalizeJid(phone);
  const existing = getDb().prepare("SELECT * FROM conversations WHERE phone = ?").get(cleanPhone) as Conversation | undefined;
  
  if (existing) {
    if (name && existing.name !== name) {
      getDb().prepare("UPDATE conversations SET name = ? WHERE id = ?").run(name, existing.id);
      existing.name = name;
    }
    return existing;
  }
  
  const info = getDb().prepare("INSERT INTO conversations (phone, name, mode) VALUES (?, ?, 'HUMAN')").run(cleanPhone, name || null);
  return {
    id: Number(info.lastInsertRowid),
    phone: cleanPhone,
    name: name || null,
    mode: "HUMAN",
    last_message_at: null,
    created_at: Math.floor(Date.now() / 1000)
  };
}

export function getConversationById(id: number): Conversation | null {
  const row = getDb().prepare("SELECT * FROM conversations WHERE id = ?").get(id) as Conversation | undefined;
  return row || null;
}

export function insertMessage(conversationId: number, role: "user" | "assistant" | "human", content: string): Message {
  const now = Math.floor(Date.now() / 1000);
  const transaction = getDb().transaction(() => {
    const info = getDb().prepare(`
      INSERT INTO messages (conversation_id, role, content, created_at)
      VALUES (?, ?, ?, ?)
    `).run(conversationId, role, content, now);
    
    getDb().prepare(`
      UPDATE conversations
      SET last_message_at = ?
      WHERE id = ?
    `).run(now, conversationId);
    
    return Number(info.lastInsertRowid);
  });
  
  const messageId = transaction();
  return {
    id: messageId,
    conversation_id: conversationId,
    role,
    content,
    created_at: now
  };
}

export function getMessages(conversationId: number, limit = 50): Message[] {
  return getDb().prepare("SELECT * FROM messages WHERE conversation_id = ? ORDER BY created_at ASC LIMIT ?").all(conversationId, limit) as Message[];
}

export function getRecentHistory(conversationId: number, limit = 20): Message[] {
  const rows = getDb().prepare("SELECT * FROM messages WHERE conversation_id = ? ORDER BY created_at DESC LIMIT ?").all(conversationId, limit) as Message[];
  return rows.reverse();
}

export function setMode(conversationId: number, mode: "AI" | "HUMAN"): void {
  getDb().prepare("UPDATE conversations SET mode = ? WHERE id = ?").run(mode, conversationId);
}

export function listConversations(): Conversation[] {
  return getDb().prepare(`
    SELECT c.*, 
      (SELECT m.content FROM messages m 
       WHERE m.conversation_id = c.id 
       ORDER BY m.created_at DESC LIMIT 1) as last_message_preview
    FROM conversations c
    ORDER BY COALESCE(c.last_message_at, c.created_at) DESC
  `).all() as Conversation[];
}

export function getConnectionState(): ConnectionState {
  const state = getDb().prepare("SELECT status, qr_string, phone, updated_at FROM connection_state WHERE id = 1").get() as ConnectionState | undefined;
  return state || { status: "disconnected", qr_string: null, phone: null, updated_at: Math.floor(Date.now() / 1000) };
}

export function setConnectionState(state: Partial<Omit<ConnectionState, "updated_at">>): void {
  const current = getConnectionState();
  const now = Math.floor(Date.now() / 1000);
  
  const status = state.status !== undefined ? state.status : current.status;
  const qr_string = state.qr_string !== undefined ? state.qr_string : current.qr_string;
  const phone = state.phone !== undefined ? state.phone : current.phone;
  
  getDb().prepare(`
    UPDATE connection_state 
    SET status = ?, qr_string = ?, phone = ?, updated_at = ? 
    WHERE id = 1
  `).run(status, qr_string, phone, now);
}

export function enqueueOutbox(conversationId: number, phone: string, content: string): number {
  const now = Math.floor(Date.now() / 1000);
  const cleanPhone = normalizeJid(phone);
  const info = getDb().prepare(`
    INSERT INTO outbox (conversation_id, phone, content, sent, created_at) 
    VALUES (?, ?, ?, 0, ?)
  `).run(conversationId, cleanPhone, content, now);
  return Number(info.lastInsertRowid);
}

export function getPendingOutbox(limit = 20): OutboxItem[] {
  return getDb().prepare("SELECT * FROM outbox WHERE sent = 0 ORDER BY created_at ASC LIMIT ?").all(limit) as OutboxItem[];
}

export function markOutboxSent(id: number): void {
  getDb().prepare("UPDATE outbox SET sent = 1 WHERE id = ?").run(id);
}

export function deleteConversation(id: number): void {
  const transaction = getDb().transaction(() => {
    getDb().prepare("DELETE FROM messages WHERE conversation_id = ?").run(id);
    getDb().prepare("DELETE FROM outbox WHERE conversation_id = ? AND sent = 0").run(id);
    getDb().prepare("DELETE FROM conversations WHERE id = ?").run(id);
  });
  transaction();
}

export function getSetting(key: string, defaultValue: string): string {
  try {
    const row = getDb().prepare("SELECT value FROM settings WHERE key = ?").get(key) as { value: string } | undefined;
    return row ? row.value : defaultValue;
  } catch (err) {
    console.error(`Error getting setting ${key}:`, err);
    return defaultValue;
  }
}

export function setSetting(key: string, value: string): void {
  try {
    getDb().prepare("INSERT OR REPLACE INTO settings (key, value) VALUES (?, ?)").run(key, value);
  } catch (err) {
    console.error(`Error setting ${key}:`, err);
  }
}

export function getMessageCount(conversationId: number): number {
  const row = getDb().prepare("SELECT COUNT(*) as cnt FROM messages WHERE conversation_id = ?").get(conversationId) as { cnt: number };
  return row?.cnt ?? 0;
}

export function setAllConversationsMode(mode: "AI" | "HUMAN"): void {
  getDb().prepare("UPDATE conversations SET mode = ?").run(mode);
}

export interface Stats {
  total_conversations: number;
  convos_ai: number;
  convos_human: number;
  messages_today_ai: number;
  messages_today_human: number;
  messages_today_user: number;
}

export function getStats(): Stats {
  const todayStart = Math.floor(new Date().setHours(0, 0, 0, 0) / 1000);
  const total = getDb().prepare("SELECT COUNT(*) as cnt FROM conversations").get() as { cnt: number };
  const ai = getDb().prepare("SELECT COUNT(*) as cnt FROM conversations WHERE mode = 'AI'").get() as { cnt: number };
  const human = getDb().prepare("SELECT COUNT(*) as cnt FROM conversations WHERE mode = 'HUMAN'").get() as { cnt: number };
  const msgAi = getDb().prepare("SELECT COUNT(*) as cnt FROM messages WHERE role = 'assistant' AND created_at >= ?").get(todayStart) as { cnt: number };
  const msgHuman = getDb().prepare("SELECT COUNT(*) as cnt FROM messages WHERE role = 'human' AND created_at >= ?").get(todayStart) as { cnt: number };
  const msgUser = getDb().prepare("SELECT COUNT(*) as cnt FROM messages WHERE role = 'user' AND created_at >= ?").get(todayStart) as { cnt: number };
  return {
    total_conversations: total.cnt,
    convos_ai: ai.cnt,
    convos_human: human.cnt,
    messages_today_ai: msgAi.cnt,
    messages_today_human: msgHuman.cnt,
    messages_today_user: msgUser.cnt,
  };
}

// ── Fase 2: Session Backup (SQLite Auth State) ────────────────────────────────

export function saveSessionFile(fileName: string, content: string): void {
  const now = Math.floor(Date.now() / 1000);
  getDb().prepare(`
    INSERT OR REPLACE INTO wa_session_backup (file_name, content, updated_at)
    VALUES (?, ?, ?)
  `).run(fileName, content, now);
}

export function loadSessionFiles(): Array<{ file_name: string; content: string }> {
  return getDb().prepare("SELECT file_name, content FROM wa_session_backup").all() as Array<{ file_name: string; content: string }>;
}

export function deleteSessionFile(fileName: string): void {
  getDb().prepare("DELETE FROM wa_session_backup WHERE file_name = ?").run(fileName);
}

export function clearAllSessionFiles(): void {
  getDb().prepare("DELETE FROM wa_session_backup").run();
}

// ── Fase 3: Webhooks ──────────────────────────────────────────────────────────

export interface Webhook {
  id: string;
  url: string;
  secret: string | null;
  events: string;
  enabled: number;
  created_at: number;
}

export function listWebhooks(): Webhook[] {
  return getDb().prepare("SELECT * FROM webhooks ORDER BY created_at DESC").all() as Webhook[];
}

export function getActiveWebhooks(): Webhook[] {
  return getDb().prepare("SELECT * FROM webhooks WHERE enabled = 1").all() as Webhook[];
}

export function createWebhook(id: string, url: string, secret: string | null, events: string): Webhook {
  const now = Math.floor(Date.now() / 1000);
  getDb().prepare(`
    INSERT INTO webhooks (id, url, secret, events, enabled, created_at)
    VALUES (?, ?, ?, ?, 1, ?)
  `).run(id, url, secret, events, now);
  return { id, url, secret, events, enabled: 1, created_at: now };
}

export function deleteWebhook(id: string): boolean {
  const info = getDb().prepare("DELETE FROM webhooks WHERE id = ?").run(id);
  return info.changes > 0;
}

// ── Fase 4: API Keys ──────────────────────────────────────────────────────────

export interface ApiKey {
  id: string;
  key_hash: string;
  label: string | null;
  scopes: string; // JSON string array
  rate_limit: number;
  created_at: number;
}

export function listApiKeys(): Omit<ApiKey, "key_hash">[] {
  return getDb().prepare("SELECT id, label, scopes, rate_limit, created_at FROM api_keys ORDER BY created_at DESC").all() as Omit<ApiKey, "key_hash">[];
}

export function createApiKey(id: string, keyHash: string, label: string | null, scopes = '["read","write"]', rateLimit = 60): void {
  const now = Math.floor(Date.now() / 1000);
  getDb().prepare(`
    INSERT INTO api_keys (id, key_hash, label, scopes, rate_limit, created_at)
    VALUES (?, ?, ?, ?, ?, ?)
  `).run(id, keyHash, label, scopes, rateLimit, now);
}

export function findApiKeyByHash(keyHash: string): ApiKey | null {
  const row = getDb().prepare("SELECT * FROM api_keys WHERE key_hash = ?").get(keyHash) as ApiKey | undefined;
  return row || null;
}

export function deleteApiKey(id: string): boolean {
  const info = getDb().prepare("DELETE FROM api_keys WHERE id = ?").run(id);
  return info.changes > 0;
}

// ── Logs de Auditoría Helpers ──────────────────────────────────────────────────

export interface AuditLog {
  id: number;
  action: string;
  details: string | null;
  operator: string;
  created_at: number;
}

export function logAuditEvent(action: string, details: any, operator = 'sistema'): void {
  try {
    const detailsStr = details ? (typeof details === 'string' ? details : JSON.stringify(details)) : null;
    getDb().prepare(`
      INSERT INTO audit_logs (action, details, operator)
      VALUES (?, ?, ?)
    `).run(action, detailsStr, operator);
  } catch (err) {
    console.error("Error logging audit event:", err);
  }
}

export function listAuditLogs(limit = 100): AuditLog[] {
  try {
    return getDb().prepare("SELECT * FROM audit_logs ORDER BY created_at DESC LIMIT ?").all(limit) as AuditLog[];
  } catch (err) {
    console.error("Error listing audit logs:", err);
    return [];
  }
}

// ── CRM Leads Helpers ──────────────────────────────────────────────────────────

export interface Lead {
  id: number;
  conversation_id: number;
  phone: string;
  name: string | null;
  email: string | null;
  agency_name: string | null;
  current_system: string | null;
  booking_volume: string | null;
  status: string;
  updated_at: number;
}

export function upsertLead(lead: Omit<Lead, "id" | "updated_at">): void {
  const now = Math.floor(Date.now() / 1000);
  
  // Buscar si ya existe un lead para esta conversación
  const existing = getDb().prepare("SELECT id, name, email, agency_name, current_system, booking_volume, status FROM leads WHERE conversation_id = ?").get(lead.conversation_id) as Lead | undefined;
  
  if (existing) {
    // Actualizar solo los campos que no sean nulos en el nuevo input
    const name = lead.name !== null ? lead.name : existing.name;
    const email = lead.email !== null ? lead.email : existing.email;
    const agency_name = lead.agency_name !== null ? lead.agency_name : existing.agency_name;
    const current_system = lead.current_system !== null ? lead.current_system : existing.current_system;
    const booking_volume = lead.booking_volume !== null ? lead.booking_volume : existing.booking_volume;
    const status = lead.status !== undefined ? lead.status : existing.status;

    getDb().prepare(`
      UPDATE leads 
      SET name = ?, email = ?, agency_name = ?, current_system = ?, booking_volume = ?, status = ?, updated_at = ?
      WHERE id = ?
    `).run(name, email, agency_name, current_system, booking_volume, status, now, existing.id);
  } else {
    // Insertar nuevo lead
    getDb().prepare(`
      INSERT INTO leads (conversation_id, phone, name, email, agency_name, current_system, booking_volume, status, updated_at)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    `).run(
      lead.conversation_id,
      lead.phone,
      lead.name,
      lead.email,
      lead.agency_name,
      lead.current_system,
      lead.booking_volume,
      lead.status || 'Nuevo',
      now
    );
  }
}

export function listLeads(): Lead[] {
  return getDb().prepare("SELECT * FROM leads ORDER BY updated_at DESC").all() as Lead[];
}

export function updateLeadStatus(id: number, status: string): void {
  const now = Math.floor(Date.now() / 1000);
  getDb().prepare("UPDATE leads SET status = ?, updated_at = ? WHERE id = ?").run(status, now, id);
}

export function deleteLead(id: number): boolean {
  const info = getDb().prepare("DELETE FROM leads WHERE id = ?").run(id);
  return info.changes > 0;
}

export interface KnowledgeArticle {
  id: number;
  title: string;
  content: string;
  category: string | null;
  created_at: number;
}

export function getLeadByConversationId(conversationId: number): Lead | null {
  try {
    const row = getDb().prepare("SELECT * FROM leads WHERE conversation_id = ?").get(conversationId) as Lead | undefined;
    return row || null;
  } catch (err) {
    console.error("Error getLeadByConversationId:", err);
    return null;
  }
}

export function createKnowledgeArticle(title: string, content: string, category?: string | null): number {
  const info = getDb().prepare(`
    INSERT INTO knowledge_base (title, content, category)
    VALUES (?, ?, ?)
  `).run(title, content, category || null);
  return Number(info.lastInsertRowid);
}

export function deleteKnowledgeArticle(id: number): boolean {
  const info = getDb().prepare("DELETE FROM knowledge_base WHERE id = ?").run(id);
  return info.changes > 0;
}

export function listKnowledgeArticles(): KnowledgeArticle[] {
  return getDb().prepare("SELECT * FROM knowledge_base ORDER BY created_at DESC").all() as KnowledgeArticle[];
}

export function searchKnowledgeBase(query: string, limit = 3): string[] {
  try {
    const articles = getDb().prepare("SELECT content FROM knowledge_base").all() as { content: string }[];
    // Simple keyword relevance ranking
    const queryWords = query.toLowerCase().split(/\W+/).filter(w => w.length > 2);
    if (queryWords.length === 0) return [];
    
    const ranked = articles.map(art => {
      const contentLower = art.content.toLowerCase();
      let score = 0;
      queryWords.forEach(word => {
        if (contentLower.includes(word)) score++;
      });
      return { content: art.content, score };
    })
    .filter(item => item.score > 0)
    .sort((a, b) => b.score - a.score)
    .slice(0, limit)
    .map(item => item.content);
    
    return ranked;
  } catch (err) {
    console.error("Error searchKnowledgeBase:", err);
    return [];
  }
}