import Database from 'better-sqlite3';
import fs from 'fs';
import { paths, DATA_DIR } from '../shared/paths.js';

const SCHEMA = `
CREATE TABLE IF NOT EXISTS conversations (
  id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(8)))),
  title TEXT,
  model TEXT,
  session_id TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS messages (
  id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(8)))),
  conversation_id TEXT NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
  role TEXT NOT NULL CHECK (role IN ('user', 'assistant', 'system')),
  content TEXT NOT NULL,
  tokens_in INTEGER,
  tokens_out INTEGER,
  model TEXT,
  audio_data TEXT,
  attachments TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_msg_conv ON messages(conversation_id, created_at);
CREATE TABLE IF NOT EXISTS settings (
  key TEXT PRIMARY KEY,
  value TEXT NOT NULL,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS sessions (
  token TEXT PRIMARY KEY,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  expires_at DATETIME NOT NULL
);
CREATE TABLE IF NOT EXISTS push_subscriptions (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  endpoint TEXT NOT NULL UNIQUE,
  keys_p256dh TEXT NOT NULL,
  keys_auth TEXT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS trusted_devices (
  id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(8)))),
  token TEXT NOT NULL UNIQUE,
  label TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  expires_at DATETIME NOT NULL,
  last_seen DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_td_token ON trusted_devices(token);
`;

let db: Database.Database;

export function initDb(): void {
  fs.mkdirSync(DATA_DIR, { recursive: true });
  db = new Database(paths.db);
  db.pragma('journal_mode = WAL');
  db.pragma('foreign_keys = ON');
  db.exec(SCHEMA);

  // Migration: add session_id column if missing (existing DBs)
  const cols = db.prepare("PRAGMA table_info(conversations)").all() as { name: string }[];
  if (!cols.some((c) => c.name === 'session_id')) {
    db.exec('ALTER TABLE conversations ADD COLUMN session_id TEXT');
  }

  // Migration: add audio_data column if missing (voice messages)
  const msgCols = db.prepare("PRAGMA table_info(messages)").all() as { name: string }[];
  if (!msgCols.some((c) => c.name === 'audio_data')) {
    db.exec('ALTER TABLE messages ADD COLUMN audio_data TEXT');
  }

  // Migration: add attachments column if missing (persistent file attachments)
  const msgCols2 = db.prepare("PRAGMA table_info(messages)").all() as { name: string }[];
  if (!msgCols2.some((c) => c.name === 'attachments')) {
    db.exec('ALTER TABLE messages ADD COLUMN attachments TEXT');
  }
}

export function closeDb(): void { db?.close(); }

// Conversations
export function createConversation(title?: string, model?: string) {
  return db.prepare('INSERT INTO conversations (title, model) VALUES (?, ?) RETURNING *').get(title ?? null, model ?? null) as any;
}
export function listConversations(limit = 50) {
  return db.prepare('SELECT * FROM conversations ORDER BY updated_at DESC LIMIT ?').all(limit);
}
export function deleteConversation(id: string) {
  db.prepare('DELETE FROM conversations WHERE id = ?').run(id);
}

// Messages
export function addMessage(convId: string, role: string, content: string, meta?: { tokens_in?: number; tokens_out?: number; model?: string; audio_data?: string; attachments?: string }) {
  // Self-heal: if the conversation row is missing (orphan live convId, harness session
  // drift, deleted parent, etc.), create it so the FK constraint never fires.
  // Use the first user message as title; assistant-first stays NULL (filled by UI).
  const tx = db.transaction(() => {
    db.prepare('INSERT OR IGNORE INTO conversations (id, title, model) VALUES (?, ?, ?)')
      .run(convId, role === 'user' ? content.slice(0, 80) : null, meta?.model ?? null);
    const msg = db.prepare('INSERT INTO messages (conversation_id, role, content, tokens_in, tokens_out, model, audio_data, attachments) VALUES (?, ?, ?, ?, ?, ?, ?, ?) RETURNING *')
      .get(convId, role, content, meta?.tokens_in ?? null, meta?.tokens_out ?? null, meta?.model ?? null, meta?.audio_data ?? null, meta?.attachments ?? null);
    db.prepare('UPDATE conversations SET updated_at = CURRENT_TIMESTAMP WHERE id = ?').run(convId);
    return msg;
  });
  return tx() as any;
}
export function getMessages(convId: string) {
  return db.prepare('SELECT * FROM messages WHERE conversation_id = ? ORDER BY created_at ASC').all(convId);
}

// Settings
export function getSetting(key: string): string | undefined {
  return (db.prepare('SELECT value FROM settings WHERE key = ?').get(key) as any)?.value;
}
export function setSetting(key: string, value: string) {
  db.prepare('INSERT INTO settings (key, value) VALUES (?, ?) ON CONFLICT(key) DO UPDATE SET value = excluded.value, updated_at = CURRENT_TIMESTAMP').run(key, value);
}
export function getAllSettings() {
  const rows = db.prepare('SELECT key, value FROM settings').all() as { key: string; value: string }[];
  return Object.fromEntries(rows.map((r) => [r.key, r.value]));
}

// Auth sessions
export function createSession(token: string, expiresAt: string) {
  db.prepare('INSERT INTO sessions (token, expires_at) VALUES (?, ?)').run(token, expiresAt);
}
export function getSession(token: string): { token: string; created_at: string; expires_at: string } | undefined {
  return db.prepare('SELECT * FROM sessions WHERE token = ? AND expires_at > datetime(\'now\')').get(token) as any;
}
export function deleteSession(token: string) {
  db.prepare('DELETE FROM sessions WHERE token = ?').run(token);
}
export function deleteExpiredSessions() {
  db.prepare('DELETE FROM sessions WHERE expires_at <= datetime(\'now\')').run();
}

// Session ID (Agent SDK)
export function getSessionId(convId: string): string | null {
  const row = db.prepare('SELECT session_id FROM conversations WHERE id = ?').get(convId) as any;
  return row?.session_id ?? null;
}
export function saveSessionId(convId: string, sessionId: string): void {
  db.prepare('UPDATE conversations SET session_id = ? WHERE id = ?').run(sessionId, convId);
}

// Push subscriptions
export function addPushSubscription(endpoint: string, p256dh: string, auth: string) {
  db.prepare(
    `INSERT INTO push_subscriptions (endpoint, keys_p256dh, keys_auth) VALUES (?, ?, ?)
     ON CONFLICT(endpoint) DO UPDATE SET keys_p256dh = excluded.keys_p256dh, keys_auth = excluded.keys_auth`
  ).run(endpoint, p256dh, auth);
}
export function removePushSubscription(endpoint: string) {
  db.prepare('DELETE FROM push_subscriptions WHERE endpoint = ?').run(endpoint);
}
export function getAllPushSubscriptions() {
  return db.prepare('SELECT * FROM push_subscriptions').all() as { id: number; endpoint: string; keys_p256dh: string; keys_auth: string }[];
}
export function getPushSubscriptionByEndpoint(endpoint: string) {
  return db.prepare('SELECT * FROM push_subscriptions WHERE endpoint = ?').get(endpoint) as { id: number; endpoint: string; keys_p256dh: string; keys_auth: string } | undefined;
}

// Trusted devices (2FA)
export function createTrustedDevice(token: string, label: string, expiresAt: string) {
  return db.prepare('INSERT INTO trusted_devices (token, label, expires_at) VALUES (?, ?, ?) RETURNING *').get(token, label, expiresAt) as any;
}
export function getTrustedDevice(token: string): { id: string; token: string; label: string; created_at: string; expires_at: string; last_seen: string } | undefined {
  return db.prepare("SELECT * FROM trusted_devices WHERE token = ? AND expires_at > datetime('now')").get(token) as any;
}
export function updateDeviceLastSeen(token: string) {
  db.prepare("UPDATE trusted_devices SET last_seen = CURRENT_TIMESTAMP WHERE token = ?").run(token);
}
export function listTrustedDevices() {
  return db.prepare("SELECT id, label, last_seen, created_at FROM trusted_devices WHERE expires_at > datetime('now') ORDER BY last_seen DESC").all() as { id: string; label: string; last_seen: string; created_at: string }[];
}
export function deleteTrustedDevice(id: string) {
  db.prepare('DELETE FROM trusted_devices WHERE id = ?').run(id);
}
export function deleteExpiredDevices() {
  db.prepare("DELETE FROM trusted_devices WHERE expires_at <= datetime('now')").run();
}
export function deleteAllTrustedDevices() {
  db.prepare('DELETE FROM trusted_devices').run();
}

// Recent messages (for context injection).
// Order by rowid (monotonic insertion order) — created_at has 1-second resolution
// so rapid-fire messages can collide. rowid never does.
// rowid is a hidden column, so `SELECT *` omits it — we must alias it explicitly
// in the inner query for the outer ORDER BY to reach it.
export function getRecentMessages(convId: string, limit = 20) {
  return db.prepare(`
    SELECT * FROM (
      SELECT messages.*, messages.rowid AS _rid FROM messages
      WHERE conversation_id = ? ORDER BY messages.rowid DESC LIMIT ?
    ) sub ORDER BY _rid ASC
  `).all(convId, limit);
}

// Cursor-based pagination: messages before a given message id.
// Use rowid for comparison — message.id is random hex so `id < ?` is meaningless.
export function getMessagesBefore(convId: string, beforeId: string, limit = 20) {
  return db.prepare(`
    SELECT * FROM (
      SELECT messages.*, messages.rowid AS _rid FROM messages
      WHERE conversation_id = ?
        AND messages.rowid < (SELECT rowid FROM messages WHERE id = ?)
      ORDER BY messages.rowid DESC LIMIT ?
    ) sub ORDER BY _rid ASC
  `).all(convId, beforeId, limit);
}