// Author: Simon-Pierre Boucher
// Contact: contact@spboucher.ai
// Project: chat.spboucher.ai

import type { Database } from "better-sqlite3";

interface Migration {
  version: number;
  up: string;
}

const migrations: Migration[] = [
  {
    version: 1,
    up: `
      CREATE TABLE IF NOT EXISTS users (
        id TEXT PRIMARY KEY,
        username TEXT NOT NULL UNIQUE,
        password_hash TEXT NOT NULL,
        totp_secret TEXT,
        created_at INTEGER NOT NULL,
        updated_at INTEGER NOT NULL
      );

      CREATE TABLE IF NOT EXISTS sessions (
        id TEXT PRIMARY KEY,               -- sha256 of the raw token; raw token lives only in the cookie
        user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
        created_at INTEGER NOT NULL,
        expires_at INTEGER NOT NULL,
        last_seen_at INTEGER NOT NULL,
        user_agent TEXT,
        ip TEXT
      );
      CREATE INDEX IF NOT EXISTS idx_sessions_user ON sessions(user_id);
      CREATE INDEX IF NOT EXISTS idx_sessions_expires ON sessions(expires_at);

      CREATE TABLE IF NOT EXISTS login_attempts (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        ip TEXT NOT NULL,
        success INTEGER NOT NULL DEFAULT 0,
        created_at INTEGER NOT NULL
      );
      CREATE INDEX IF NOT EXISTS idx_login_attempts_ip ON login_attempts(ip, created_at);

      CREATE TABLE IF NOT EXISTS conversations (
        id TEXT PRIMARY KEY,
        title TEXT NOT NULL DEFAULT 'New conversation',
        pinned INTEGER NOT NULL DEFAULT 0,
        current_leaf_id TEXT,
        created_at INTEGER NOT NULL,
        updated_at INTEGER NOT NULL
      );
      CREATE INDEX IF NOT EXISTS idx_conversations_updated ON conversations(updated_at DESC);

      -- Messages form a tree: branching = multiple children of the same parent.
      CREATE TABLE IF NOT EXISTS messages (
        id TEXT PRIMARY KEY,
        conversation_id TEXT NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
        parent_id TEXT REFERENCES messages(id) ON DELETE CASCADE,
        role TEXT NOT NULL CHECK (role IN ('user','assistant','system')),
        content TEXT NOT NULL DEFAULT '',
        reasoning TEXT,
        -- model attribution, stored denormalized so history survives catalog changes
        model_id TEXT,
        model_name TEXT,
        provider TEXT,
        generation_id TEXT,
        status TEXT NOT NULL DEFAULT 'completed' CHECK (status IN ('pending','streaming','completed','cancelled','failed')),
        error_message TEXT,
        created_at INTEGER NOT NULL
      );
      CREATE INDEX IF NOT EXISTS idx_messages_conversation ON messages(conversation_id, created_at);
      CREATE INDEX IF NOT EXISTS idx_messages_parent ON messages(parent_id);

      -- Explicit generation state machine: queued → starting → streaming → completed | cancelled | failed
      CREATE TABLE IF NOT EXISTS generations (
        id TEXT PRIMARY KEY,
        message_id TEXT NOT NULL REFERENCES messages(id) ON DELETE CASCADE,
        conversation_id TEXT NOT NULL,
        model_id TEXT NOT NULL,
        state TEXT NOT NULL DEFAULT 'queued' CHECK (state IN ('queued','starting','streaming','completed','cancelled','failed')),
        error_code TEXT,
        error_message TEXT,
        openrouter_generation_id TEXT,
        created_at INTEGER NOT NULL,
        finished_at INTEGER
      );
      CREATE INDEX IF NOT EXISTS idx_generations_message ON generations(message_id);

      CREATE TABLE IF NOT EXISTS generation_usage (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        generation_id TEXT NOT NULL REFERENCES generations(id) ON DELETE CASCADE,
        model_id TEXT NOT NULL,
        prompt_tokens INTEGER,
        completion_tokens INTEGER,
        reasoning_tokens INTEGER,
        cached_tokens INTEGER,
        total_tokens INTEGER,
        estimated_cost_usd REAL,
        reported_cost_usd REAL,
        created_at INTEGER NOT NULL
      );
      CREATE INDEX IF NOT EXISTS idx_usage_created ON generation_usage(created_at);
      CREATE INDEX IF NOT EXISTS idx_usage_model ON generation_usage(model_id);

      -- Dynamic model catalog cache, synced from OpenRouter. Never hand-maintained.
      CREATE TABLE IF NOT EXISTS models (
        openrouter_model_id TEXT PRIMARY KEY,   -- opaque, sent back verbatim
        name TEXT NOT NULL,
        provider TEXT,
        description TEXT,
        context_length INTEGER,
        pricing_prompt REAL,
        pricing_completion REAL,
        pricing_image REAL,
        pricing_request REAL,
        cap_text INTEGER NOT NULL DEFAULT 1,
        cap_vision INTEGER NOT NULL DEFAULT 0,
        cap_reasoning INTEGER NOT NULL DEFAULT 0,
        cap_tools INTEGER NOT NULL DEFAULT 0,
        cap_structured INTEGER NOT NULL DEFAULT 0,
        architecture TEXT,
        tokenizer TEXT,
        raw_json TEXT,
        available INTEGER NOT NULL DEFAULT 1,
        or_created_at INTEGER,
        first_seen_at INTEGER NOT NULL,
        updated_at INTEGER NOT NULL
      );
      CREATE INDEX IF NOT EXISTS idx_models_available ON models(available);

      CREATE TABLE IF NOT EXISTS model_prefs (
        model_id TEXT PRIMARY KEY,
        favorite INTEGER NOT NULL DEFAULT 0,
        pinned INTEGER NOT NULL DEFAULT 0,
        last_used_at INTEGER,
        use_count INTEGER NOT NULL DEFAULT 0
      );

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

export function migrate(db: Database): void {
  db.exec(`CREATE TABLE IF NOT EXISTS schema_migrations (
    version INTEGER PRIMARY KEY,
    applied_at INTEGER NOT NULL
  )`);
  const applied = new Set(
    (db.prepare("SELECT version FROM schema_migrations").all() as { version: number }[]).map(
      (r) => r.version
    )
  );
  for (const m of migrations) {
    if (applied.has(m.version)) continue;
    const tx = db.transaction(() => {
      db.exec(m.up);
      db.prepare("INSERT INTO schema_migrations (version, applied_at) VALUES (?, ?)").run(
        m.version,
        Date.now()
      );
    });
    tx();
  }
}