/**
 * Database migration runner.
 *
 * For SQLite, uses Drizzle's push-based approach to create/update tables
 * on first start. For production, Drizzle Kit migrations can be used.
 */

import { sql } from 'drizzle-orm';
import type { SqliteDb } from './index.js';
/**
 * Run migrations: create all tables and indexes if they don't exist.
 *
 * Uses CREATE TABLE IF NOT EXISTS for idempotent startup.
 * This approach is simpler than file-based migrations for an
 * embedded SQLite database that auto-creates on first start.
 */
export function runMigrations(db: SqliteDb): void {
  // Create all tables using raw SQL for CREATE IF NOT EXISTS
  // Drizzle doesn't have a built-in "push" that works at runtime,
  // so we use the schema definitions to generate DDL.
  db.run(sql`
    CREATE TABLE IF NOT EXISTS events (
      id TEXT PRIMARY KEY,
      timestamp TEXT NOT NULL,
      session_id TEXT NOT NULL,
      agent_id TEXT NOT NULL,
      event_type TEXT NOT NULL,
      severity TEXT NOT NULL DEFAULT 'info',
      payload TEXT NOT NULL,
      metadata TEXT NOT NULL DEFAULT '{}',
      prev_hash TEXT,
      hash TEXT NOT NULL
    )
  `);

  db.run(sql`
    CREATE TABLE IF NOT EXISTS sessions (
      id TEXT NOT NULL,
      agent_id TEXT NOT NULL,
      agent_name TEXT,
      started_at TEXT NOT NULL,
      ended_at TEXT,
      status TEXT NOT NULL DEFAULT 'active',
      event_count INTEGER NOT NULL DEFAULT 0,
      tool_call_count INTEGER NOT NULL DEFAULT 0,
      error_count INTEGER NOT NULL DEFAULT 0,
      total_cost_usd REAL NOT NULL DEFAULT 0,
      llm_call_count INTEGER NOT NULL DEFAULT 0,
      total_input_tokens INTEGER NOT NULL DEFAULT 0,
      total_output_tokens INTEGER NOT NULL DEFAULT 0,
      tags TEXT NOT NULL DEFAULT '[]',
      tenant_id TEXT NOT NULL DEFAULT 'default',
      PRIMARY KEY (id, tenant_id)
    )
  `);

  db.run(sql`
    CREATE TABLE IF NOT EXISTS agents (
      id TEXT NOT NULL,
      name TEXT NOT NULL,
      description TEXT,
      first_seen_at TEXT NOT NULL,
      last_seen_at TEXT NOT NULL,
      session_count INTEGER NOT NULL DEFAULT 0,
      tenant_id TEXT NOT NULL DEFAULT 'default',
      PRIMARY KEY (id, tenant_id)
    )
  `);

  db.run(sql`
    CREATE TABLE IF NOT EXISTS alert_rules (
      id TEXT PRIMARY KEY,
      name TEXT NOT NULL,
      enabled INTEGER NOT NULL DEFAULT 1,
      condition TEXT NOT NULL,
      threshold REAL NOT NULL,
      window_minutes INTEGER NOT NULL,
      scope TEXT NOT NULL DEFAULT '{}',
      notify_channels TEXT NOT NULL DEFAULT '[]',
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    )
  `);

  db.run(sql`
    CREATE TABLE IF NOT EXISTS alert_history (
      id TEXT PRIMARY KEY,
      rule_id TEXT NOT NULL REFERENCES alert_rules(id),
      triggered_at TEXT NOT NULL,
      resolved_at TEXT,
      current_value REAL NOT NULL,
      threshold REAL NOT NULL,
      message TEXT NOT NULL
    )
  `);

  db.run(sql`
    CREATE TABLE IF NOT EXISTS api_keys (
      id TEXT PRIMARY KEY,
      key_hash TEXT NOT NULL,
      name TEXT NOT NULL,
      scopes TEXT NOT NULL,
      created_at INTEGER NOT NULL,
      last_used_at INTEGER,
      revoked_at INTEGER,
      rate_limit INTEGER
    )
  `);

  // Create indexes (IF NOT EXISTS)
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_events_timestamp ON events(timestamp)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_events_session_id ON events(session_id)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_events_agent_id ON events(agent_id)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_events_type ON events(event_type)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_events_session_ts ON events(session_id, timestamp)`);
  db.run(
    sql`CREATE INDEX IF NOT EXISTS idx_events_agent_type_ts ON events(agent_id, event_type, timestamp)`,
  );
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_sessions_agent_id ON sessions(agent_id)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_sessions_started_at ON sessions(started_at)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_sessions_status ON sessions(status)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_api_keys_hash ON api_keys(key_hash)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_alert_history_rule_id ON alert_history(rule_id)`);

  // ─── Migrations for existing databases ──────────────────
  // Add LLM tracking columns to sessions (v0.3.0)
  // SQLite doesn't support ADD COLUMN IF NOT EXISTS, so we check first
  const sessionColumns = db.all<{ name: string }>(sql`PRAGMA table_info(sessions)`);
  const sessionColumnNames = new Set(sessionColumns.map((c) => c.name));
  if (!sessionColumnNames.has('llm_call_count')) {
    db.run(sql`ALTER TABLE sessions ADD COLUMN llm_call_count INTEGER NOT NULL DEFAULT 0`);
  }
  if (!sessionColumnNames.has('total_input_tokens')) {
    db.run(sql`ALTER TABLE sessions ADD COLUMN total_input_tokens INTEGER NOT NULL DEFAULT 0`);
  }
  if (!sessionColumnNames.has('total_output_tokens')) {
    db.run(sql`ALTER TABLE sessions ADD COLUMN total_output_tokens INTEGER NOT NULL DEFAULT 0`);
  }
  // org→project scoping (#147)
  if (!sessionColumnNames.has('org_id')) {
    db.run(sql`ALTER TABLE sessions ADD COLUMN org_id TEXT NOT NULL DEFAULT 'default'`);
  }
  if (!sessionColumnNames.has('project_id')) {
    db.run(sql`ALTER TABLE sessions ADD COLUMN project_id TEXT`);
    db.run(sql`UPDATE sessions SET project_id = tenant_id WHERE project_id IS NULL`);
  }
  // #281 idle derivation: last activity timestamp; seed existing rows from started_at.
  if (!sessionColumnNames.has('last_event_at')) {
    db.run(sql`ALTER TABLE sessions ADD COLUMN last_event_at TEXT`);
    db.run(sql`UPDATE sessions SET last_event_at = started_at WHERE last_event_at IS NULL`);
  }

  // ─── Tenant isolation migration (Epic 1) ──────────────────
  // Add tenant_id to all data tables for multi-tenant support

  // api_keys.tenant_id
  const apiKeyColumns = db.all<{ name: string }>(sql`PRAGMA table_info(api_keys)`);
  const apiKeyColumnNames = new Set(apiKeyColumns.map((c) => c.name));
  if (!apiKeyColumnNames.has('tenant_id')) {
    db.run(sql`ALTER TABLE api_keys ADD COLUMN tenant_id TEXT NOT NULL DEFAULT 'default'`);
  }
  if (!apiKeyColumnNames.has('created_by')) {
    db.run(sql`ALTER TABLE api_keys ADD COLUMN created_by TEXT REFERENCES users(id)`);
  }
  if (!apiKeyColumnNames.has('role')) {
    db.run(sql`ALTER TABLE api_keys ADD COLUMN role TEXT NOT NULL DEFAULT 'editor'`);
  }

  // events.tenant_id
  const eventColumns = db.all<{ name: string }>(sql`PRAGMA table_info(events)`);
  const eventColumnNames = new Set(eventColumns.map((c) => c.name));
  if (!eventColumnNames.has('tenant_id')) {
    db.run(sql`ALTER TABLE events ADD COLUMN tenant_id TEXT NOT NULL DEFAULT 'default'`);
  }

  // events.verified_agent_id (billing-grade attribution, #87) — derived at
  // insert from metadata.verifiedAgentId; NULL when unverified.
  if (!eventColumnNames.has('verified_agent_id')) {
    db.run(sql`ALTER TABLE events ADD COLUMN verified_agent_id TEXT`);
  }

  // events.pricing_version (reconciliation provenance, #89) — stamped at insert
  // on cost-bearing events; NULL otherwise.
  if (!eventColumnNames.has('pricing_version')) {
    db.run(sql`ALTER TABLE events ADD COLUMN pricing_version TEXT`);
  }

  // org→project scoping columns (#147 cutover). Additive: stamped at insert; the
  // existing tenant_id filtering still enforces isolation, so reads are unchanged.
  // Backfill maps the legacy tenant_id → a project (project_id = tenant_id) under
  // the default org. project_id == tenant_id keeps isolation identical today.
  if (!eventColumnNames.has('org_id')) {
    db.run(sql`ALTER TABLE events ADD COLUMN org_id TEXT NOT NULL DEFAULT 'default'`);
  }
  if (!eventColumnNames.has('project_id')) {
    db.run(sql`ALTER TABLE events ADD COLUMN project_id TEXT`);
    db.run(sql`UPDATE events SET project_id = tenant_id WHERE project_id IS NULL`);
  }
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_events_org_project_ts ON events(org_id, project_id, timestamp)`);

  // sessions.tenant_id
  if (!sessionColumnNames.has('tenant_id')) {
    db.run(sql`ALTER TABLE sessions ADD COLUMN tenant_id TEXT NOT NULL DEFAULT 'default'`);
  }

  // agents.tenant_id
  const agentColumns = db.all<{ name: string }>(sql`PRAGMA table_info(agents)`);
  const agentColumnNames = new Set(agentColumns.map((c) => c.name));
  if (!agentColumnNames.has('tenant_id')) {
    db.run(sql`ALTER TABLE agents ADD COLUMN tenant_id TEXT NOT NULL DEFAULT 'default'`);
  }
  // org→project scoping (#147)
  if (!agentColumnNames.has('org_id')) {
    db.run(sql`ALTER TABLE agents ADD COLUMN org_id TEXT NOT NULL DEFAULT 'default'`);
  }
  if (!agentColumnNames.has('project_id')) {
    db.run(sql`ALTER TABLE agents ADD COLUMN project_id TEXT`);
    db.run(sql`UPDATE agents SET project_id = tenant_id WHERE project_id IS NULL`);
  }

  // alert_rules.tenant_id
  const alertRuleColumns = db.all<{ name: string }>(sql`PRAGMA table_info(alert_rules)`);
  const alertRuleColumnNames = new Set(alertRuleColumns.map((c) => c.name));
  if (!alertRuleColumnNames.has('tenant_id')) {
    db.run(sql`ALTER TABLE alert_rules ADD COLUMN tenant_id TEXT NOT NULL DEFAULT 'default'`);
  }

  // alert_history.tenant_id
  const alertHistoryColumns = db.all<{ name: string }>(sql`PRAGMA table_info(alert_history)`);
  const alertHistoryColumnNames = new Set(alertHistoryColumns.map((c) => c.name));
  if (!alertHistoryColumnNames.has('tenant_id')) {
    db.run(sql`ALTER TABLE alert_history ADD COLUMN tenant_id TEXT NOT NULL DEFAULT 'default'`);
  }

  // Tenant isolation indexes
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_events_tenant_id ON events(tenant_id)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_events_tenant_session ON events(tenant_id, session_id)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_events_tenant_agent_ts ON events(tenant_id, agent_id, timestamp)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_events_tenant_verified_ts ON events(tenant_id, verified_agent_id, timestamp)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_sessions_tenant_id ON sessions(tenant_id)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_sessions_tenant_agent ON sessions(tenant_id, agent_id)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_sessions_tenant_started ON sessions(tenant_id, started_at)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_agents_tenant_id ON agents(tenant_id)`);

  // ─── Embeddings table (Epic 2 — Story 2.2) ──────────────────
  db.run(sql`
    CREATE TABLE IF NOT EXISTS embeddings (
      id TEXT PRIMARY KEY,
      tenant_id TEXT NOT NULL,
      source_type TEXT NOT NULL,
      source_id TEXT NOT NULL,
      content_hash TEXT NOT NULL,
      text_content TEXT NOT NULL,
      embedding BLOB NOT NULL,
      embedding_model TEXT NOT NULL,
      dimensions INTEGER NOT NULL,
      created_at TEXT NOT NULL
    )
  `);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_embeddings_tenant ON embeddings(tenant_id)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_embeddings_source ON embeddings(source_type, source_id)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_embeddings_content_hash ON embeddings(tenant_id, content_hash)`);

  // ─── Session Summaries table (Epic 2 — Story 2.2) ──────────────────
  db.run(sql`
    CREATE TABLE IF NOT EXISTS session_summaries (
      session_id TEXT NOT NULL,
      tenant_id TEXT NOT NULL,
      summary TEXT NOT NULL,
      topics TEXT NOT NULL DEFAULT '[]',
      tool_sequence TEXT NOT NULL DEFAULT '[]',
      error_summary TEXT,
      outcome TEXT,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL,
      PRIMARY KEY (session_id, tenant_id)
    )
  `);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_session_summaries_tenant ON session_summaries(tenant_id)`);

  // ─── Composite PK migration (CRITICAL-2) ──────────────────
  // SQLite doesn't support ALTER TABLE to change PKs, so we recreate
  // tables with composite PKs (id, tenant_id) for tenant isolation.

  // Check if sessions table still has single-column PK
  // by looking at the CREATE TABLE statement
  const sessionsSchema = db.get<{ sql: string }>(
    sql`SELECT sql FROM sqlite_master WHERE type='table' AND name='sessions'`,
  );
  if (sessionsSchema && sessionsSchema.sql.includes('id TEXT PRIMARY KEY')) {
    // Drop old indexes that reference sessions (they'll be recreated)
    db.run(sql`DROP INDEX IF EXISTS idx_sessions_agent_id`);
    db.run(sql`DROP INDEX IF EXISTS idx_sessions_started_at`);
    db.run(sql`DROP INDEX IF EXISTS idx_sessions_status`);
    db.run(sql`DROP INDEX IF EXISTS idx_sessions_tenant_id`);
    db.run(sql`DROP INDEX IF EXISTS idx_sessions_tenant_agent`);
    db.run(sql`DROP INDEX IF EXISTS idx_sessions_tenant_started`);

    db.run(sql`
      CREATE TABLE sessions_new (
        id TEXT NOT NULL,
        agent_id TEXT NOT NULL,
        agent_name TEXT,
        started_at TEXT NOT NULL,
        ended_at TEXT,
        status TEXT NOT NULL DEFAULT 'active',
        event_count INTEGER NOT NULL DEFAULT 0,
        tool_call_count INTEGER NOT NULL DEFAULT 0,
        error_count INTEGER NOT NULL DEFAULT 0,
        total_cost_usd REAL NOT NULL DEFAULT 0,
        llm_call_count INTEGER NOT NULL DEFAULT 0,
        total_input_tokens INTEGER NOT NULL DEFAULT 0,
        total_output_tokens INTEGER NOT NULL DEFAULT 0,
        tags TEXT NOT NULL DEFAULT '[]',
        tenant_id TEXT NOT NULL DEFAULT 'default',
        PRIMARY KEY (id, tenant_id)
      )
    `);
    db.run(sql`
      INSERT INTO sessions_new
        SELECT id, agent_id, agent_name, started_at, ended_at, status,
               event_count, tool_call_count, error_count, total_cost_usd,
               llm_call_count, total_input_tokens, total_output_tokens,
               tags, tenant_id
        FROM sessions
    `);
    db.run(sql`DROP TABLE sessions`);
    db.run(sql`ALTER TABLE sessions_new RENAME TO sessions`);

    // Recreate indexes
    db.run(sql`CREATE INDEX idx_sessions_agent_id ON sessions(agent_id)`);
    db.run(sql`CREATE INDEX idx_sessions_started_at ON sessions(started_at)`);
    db.run(sql`CREATE INDEX idx_sessions_status ON sessions(status)`);
    db.run(sql`CREATE INDEX idx_sessions_tenant_id ON sessions(tenant_id)`);
    db.run(sql`CREATE INDEX idx_sessions_tenant_agent ON sessions(tenant_id, agent_id)`);
    db.run(sql`CREATE INDEX idx_sessions_tenant_started ON sessions(tenant_id, started_at)`);
  }

  // Check if agents table still has single-column PK
  const agentsSchema = db.get<{ sql: string }>(
    sql`SELECT sql FROM sqlite_master WHERE type='table' AND name='agents'`,
  );
  if (agentsSchema && agentsSchema.sql.includes('id TEXT PRIMARY KEY')) {
    db.run(sql`DROP INDEX IF EXISTS idx_agents_tenant_id`);

    db.run(sql`
      CREATE TABLE agents_new (
        id TEXT NOT NULL,
        name TEXT NOT NULL,
        description TEXT,
        first_seen_at TEXT NOT NULL,
        last_seen_at TEXT NOT NULL,
        session_count INTEGER NOT NULL DEFAULT 0,
        tenant_id TEXT NOT NULL DEFAULT 'default',
        PRIMARY KEY (id, tenant_id)
      )
    `);
    db.run(sql`
      INSERT INTO agents_new
        SELECT id, name, description, first_seen_at, last_seen_at,
               session_count, tenant_id
        FROM agents
    `);
    db.run(sql`DROP TABLE agents`);
    db.run(sql`ALTER TABLE agents_new RENAME TO agents`);

    db.run(sql`CREATE INDEX idx_agents_tenant_id ON agents(tenant_id)`);
  }

  // ─── Lessons table (Epic 3) ──────────────────────────────
  db.run(sql`
    CREATE TABLE IF NOT EXISTS lessons (
      id TEXT NOT NULL,
      tenant_id TEXT NOT NULL,
      agent_id TEXT,
      category TEXT NOT NULL DEFAULT 'general',
      title TEXT NOT NULL,
      content TEXT NOT NULL,
      context TEXT NOT NULL DEFAULT '{}',
      importance TEXT NOT NULL DEFAULT 'normal',
      source_session_id TEXT,
      source_event_id TEXT,
      access_count INTEGER NOT NULL DEFAULT 0,
      last_accessed_at TEXT,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL,
      archived_at TEXT,
      PRIMARY KEY (id, tenant_id)
    )
  `);

  db.run(sql`CREATE INDEX IF NOT EXISTS idx_lessons_tenant ON lessons(tenant_id)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_lessons_tenant_agent ON lessons(tenant_id, agent_id)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_lessons_tenant_category ON lessons(tenant_id, category)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_lessons_tenant_importance ON lessons(tenant_id, importance)`);

  // ─── Lessons PK migration (H5) ──────────────────────────
  // Migrate existing lessons tables from single-column PK to composite PK
  const lessonsSchema = db.get<{ sql: string }>(
    sql`SELECT sql FROM sqlite_master WHERE type='table' AND name='lessons'`,
  );
  if (lessonsSchema && lessonsSchema.sql.includes('id TEXT PRIMARY KEY')) {
    db.run(sql`DROP INDEX IF EXISTS idx_lessons_tenant`);
    db.run(sql`DROP INDEX IF EXISTS idx_lessons_tenant_agent`);
    db.run(sql`DROP INDEX IF EXISTS idx_lessons_tenant_category`);
    db.run(sql`DROP INDEX IF EXISTS idx_lessons_tenant_importance`);

    db.run(sql`
      CREATE TABLE lessons_new (
        id TEXT NOT NULL,
        tenant_id TEXT NOT NULL,
        agent_id TEXT,
        category TEXT NOT NULL DEFAULT 'general',
        title TEXT NOT NULL,
        content TEXT NOT NULL,
        context TEXT NOT NULL DEFAULT '{}',
        importance TEXT NOT NULL DEFAULT 'normal',
        source_session_id TEXT,
        source_event_id TEXT,
        access_count INTEGER NOT NULL DEFAULT 0,
        last_accessed_at TEXT,
        created_at TEXT NOT NULL,
        updated_at TEXT NOT NULL,
        archived_at TEXT,
        PRIMARY KEY (id, tenant_id)
      )
    `);
    db.run(sql`
      INSERT INTO lessons_new
        SELECT id, tenant_id, agent_id, category, title, content, context,
               importance, source_session_id, source_event_id, access_count,
               last_accessed_at, created_at, updated_at, archived_at
        FROM lessons
    `);
    db.run(sql`DROP TABLE lessons`);
    db.run(sql`ALTER TABLE lessons_new RENAME TO lessons`);

    db.run(sql`CREATE INDEX idx_lessons_tenant ON lessons(tenant_id)`);
    db.run(sql`CREATE INDEX idx_lessons_tenant_agent ON lessons(tenant_id, agent_id)`);
    db.run(sql`CREATE INDEX idx_lessons_tenant_category ON lessons(tenant_id, category)`);
    db.run(sql`CREATE INDEX idx_lessons_tenant_importance ON lessons(tenant_id, importance)`);
  }

  // ─── Composite index for similarity search (M6) ──────────────────
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_embeddings_tenant_source_time ON embeddings(tenant_id, source_type, created_at)`);

  // ─── Health Snapshots table (Epic 6 — Story 1.3) ──────────────────
  db.run(sql`
    CREATE TABLE IF NOT EXISTS health_snapshots (
      id TEXT NOT NULL,
      tenant_id TEXT NOT NULL,
      agent_id TEXT NOT NULL,
      date TEXT NOT NULL,
      overall_score REAL NOT NULL,
      error_rate_score REAL NOT NULL,
      cost_efficiency_score REAL NOT NULL,
      tool_success_score REAL NOT NULL,
      latency_score REAL NOT NULL,
      completion_rate_score REAL NOT NULL,
      session_count INTEGER NOT NULL,
      created_at TEXT NOT NULL,
      PRIMARY KEY (tenant_id, agent_id, date)
    )
  `);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_health_snapshots_agent ON health_snapshots(tenant_id, agent_id, date DESC)`);

  // ─── Benchmark tables (v0.7.0 — Story 1.3) ──────────────────
  db.run(sql`
    CREATE TABLE IF NOT EXISTS benchmarks (
      id TEXT PRIMARY KEY,
      tenant_id TEXT NOT NULL,
      name TEXT NOT NULL,
      description TEXT,
      status TEXT NOT NULL DEFAULT 'draft',
      agent_id TEXT,
      metrics TEXT NOT NULL DEFAULT '[]',
      min_sessions_per_variant INTEGER NOT NULL DEFAULT 10,
      time_range_from TEXT,
      time_range_to TEXT,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL,
      completed_at TEXT
    )
  `);

  db.run(sql`
    CREATE TABLE IF NOT EXISTS benchmark_variants (
      id TEXT PRIMARY KEY,
      benchmark_id TEXT NOT NULL REFERENCES benchmarks(id) ON DELETE CASCADE,
      tenant_id TEXT NOT NULL,
      name TEXT NOT NULL,
      description TEXT,
      tag TEXT NOT NULL,
      agent_id TEXT,
      sort_order INTEGER NOT NULL DEFAULT 0
    )
  `);

  db.run(sql`
    CREATE TABLE IF NOT EXISTS benchmark_results (
      id TEXT PRIMARY KEY,
      benchmark_id TEXT NOT NULL REFERENCES benchmarks(id) ON DELETE CASCADE,
      tenant_id TEXT NOT NULL,
      variant_metrics TEXT NOT NULL DEFAULT '[]',
      comparisons TEXT NOT NULL DEFAULT '[]',
      summary TEXT,
      computed_at TEXT NOT NULL
    )
  `);

  // Benchmark indexes
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_benchmarks_tenant_id ON benchmarks(tenant_id)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_benchmarks_tenant_status ON benchmarks(tenant_id, status)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_benchmark_variants_benchmark_id ON benchmark_variants(benchmark_id)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_benchmark_variants_tenant_tag ON benchmark_variants(tenant_id, tag)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_benchmark_results_benchmark_id ON benchmark_results(benchmark_id)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_benchmark_results_tenant_id ON benchmark_results(tenant_id)`);

  // ─── Guardrail tables (v0.8.0 — Phase 3) ──────────────────
  db.run(sql`
    CREATE TABLE IF NOT EXISTS guardrail_rules (
      id TEXT PRIMARY KEY,
      tenant_id TEXT NOT NULL,
      name TEXT NOT NULL,
      description TEXT,
      enabled INTEGER NOT NULL DEFAULT 1,
      condition_type TEXT NOT NULL,
      condition_config TEXT NOT NULL DEFAULT '{}',
      action_type TEXT NOT NULL,
      action_config TEXT NOT NULL DEFAULT '{}',
      agent_id TEXT,
      cooldown_minutes INTEGER NOT NULL DEFAULT 15,
      dry_run INTEGER NOT NULL DEFAULT 0,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    )
  `);

  db.run(sql`
    CREATE TABLE IF NOT EXISTS guardrail_state (
      rule_id TEXT NOT NULL,
      tenant_id TEXT NOT NULL,
      last_triggered_at TEXT,
      trigger_count INTEGER NOT NULL DEFAULT 0,
      last_evaluated_at TEXT,
      current_value REAL,
      PRIMARY KEY (rule_id, tenant_id)
    )
  `);

  db.run(sql`
    CREATE TABLE IF NOT EXISTS guardrail_trigger_history (
      id TEXT PRIMARY KEY,
      rule_id TEXT NOT NULL,
      tenant_id TEXT NOT NULL,
      triggered_at TEXT NOT NULL,
      condition_value REAL NOT NULL,
      condition_threshold REAL NOT NULL,
      action_executed INTEGER NOT NULL DEFAULT 0,
      action_result TEXT,
      metadata TEXT NOT NULL DEFAULT '{}'
    )
  `);

  // Guardrail indexes
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_guardrail_rules_tenant ON guardrail_rules(tenant_id)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_guardrail_rules_tenant_enabled ON guardrail_rules(tenant_id, enabled)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_guardrail_state_tenant ON guardrail_state(tenant_id)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_guardrail_trigger_history_tenant ON guardrail_trigger_history(tenant_id)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_guardrail_trigger_history_rule ON guardrail_trigger_history(rule_id, triggered_at)`);

  // ─── Feature 8: Content guardrail columns ──────────────────
  const grColsF8 = db.all<{ name: string }>(sql`PRAGMA table_info(guardrail_rules)`);
  const grColNamesF8 = new Set(grColsF8.map((c) => c.name));
  if (!grColNamesF8.has('direction')) {
    db.run(sql`ALTER TABLE guardrail_rules ADD COLUMN direction TEXT DEFAULT 'both'`);
  }
  if (!grColNamesF8.has('tool_names')) {
    db.run(sql`ALTER TABLE guardrail_rules ADD COLUMN tool_names TEXT`);
  }
  if (!grColNamesF8.has('priority')) {
    db.run(sql`ALTER TABLE guardrail_rules ADD COLUMN priority INTEGER DEFAULT 0`);
  }

  // ─── Agent model override & pause columns (B1 — Story 1.2) ──────
  // Idempotent: only adds columns if they don't already exist
  const agentColsB1 = db.all<{ name: string }>(sql`PRAGMA table_info(agents)`);
  const agentColNamesB1 = new Set(agentColsB1.map((c) => c.name));

  if (!agentColNamesB1.has('model_override')) {
    db.run(sql`ALTER TABLE agents ADD COLUMN model_override TEXT`);
  }
  if (!agentColNamesB1.has('paused_at')) {
    db.run(sql`ALTER TABLE agents ADD COLUMN paused_at TEXT`);
  }
  if (!agentColNamesB1.has('pause_reason')) {
    db.run(sql`ALTER TABLE agents ADD COLUMN pause_reason TEXT`);
  }

  // Partial index for finding paused agents efficiently
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_agents_paused ON agents(tenant_id, paused_at) WHERE paused_at IS NOT NULL`);

  // ─── Phase 4: Sharing & Discovery tables (Stories 1.3) ──────────

  db.run(sql`
    CREATE TABLE IF NOT EXISTS sharing_config (
      tenant_id TEXT PRIMARY KEY,
      enabled INTEGER NOT NULL DEFAULT 0,
      human_review_enabled INTEGER NOT NULL DEFAULT 0,
      pool_endpoint TEXT,
      anonymous_contributor_id TEXT,
      purge_token TEXT,
      rate_limit_per_hour INTEGER NOT NULL DEFAULT 50,
      volume_alert_threshold INTEGER NOT NULL DEFAULT 100,
      updated_at TEXT NOT NULL
    )
  `);

  db.run(sql`
    CREATE TABLE IF NOT EXISTS agent_sharing_config (
      tenant_id TEXT NOT NULL,
      agent_id TEXT NOT NULL,
      enabled INTEGER NOT NULL DEFAULT 0,
      categories TEXT NOT NULL DEFAULT '[]',
      updated_at TEXT NOT NULL,
      PRIMARY KEY (tenant_id, agent_id)
    )
  `);

  db.run(sql`
    CREATE TABLE IF NOT EXISTS deny_list_rules (
      id TEXT PRIMARY KEY,
      tenant_id TEXT NOT NULL,
      pattern TEXT NOT NULL,
      is_regex INTEGER NOT NULL DEFAULT 0,
      reason TEXT NOT NULL,
      created_at TEXT NOT NULL
    )
  `);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_deny_list_rules_tenant ON deny_list_rules(tenant_id)`);

  db.run(sql`
    CREATE TABLE IF NOT EXISTS sharing_audit_log (
      id TEXT PRIMARY KEY,
      tenant_id TEXT NOT NULL,
      event_type TEXT NOT NULL,
      lesson_id TEXT,
      anonymous_lesson_id TEXT,
      lesson_hash TEXT,
      redaction_findings TEXT,
      query_text TEXT,
      result_ids TEXT,
      pool_endpoint TEXT,
      initiated_by TEXT,
      timestamp TEXT NOT NULL
    )
  `);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_sharing_audit_tenant_ts ON sharing_audit_log(tenant_id, timestamp)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_sharing_audit_type ON sharing_audit_log(tenant_id, event_type)`);

  db.run(sql`
    CREATE TABLE IF NOT EXISTS sharing_review_queue (
      id TEXT PRIMARY KEY,
      tenant_id TEXT NOT NULL,
      lesson_id TEXT NOT NULL,
      original_title TEXT NOT NULL,
      original_content TEXT NOT NULL,
      redacted_title TEXT NOT NULL,
      redacted_content TEXT NOT NULL,
      redaction_findings TEXT NOT NULL,
      status TEXT NOT NULL DEFAULT 'pending',
      reviewed_by TEXT,
      reviewed_at TEXT,
      created_at TEXT NOT NULL,
      expires_at TEXT NOT NULL
    )
  `);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_review_queue_tenant_status ON sharing_review_queue(tenant_id, status)`);

  db.run(sql`
    CREATE TABLE IF NOT EXISTS anonymous_id_map (
      tenant_id TEXT NOT NULL,
      agent_id TEXT NOT NULL,
      anonymous_agent_id TEXT NOT NULL,
      valid_from TEXT NOT NULL,
      valid_until TEXT NOT NULL,
      PRIMARY KEY (tenant_id, agent_id, valid_from)
    )
  `);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_anon_map_anon_id ON anonymous_id_map(anonymous_agent_id)`);

  db.run(sql`
    CREATE TABLE IF NOT EXISTS capability_registry (
      id TEXT PRIMARY KEY,
      tenant_id TEXT NOT NULL,
      agent_id TEXT NOT NULL,
      task_type TEXT NOT NULL,
      custom_type TEXT,
      input_schema TEXT NOT NULL,
      output_schema TEXT NOT NULL,
      quality_metrics TEXT NOT NULL DEFAULT '{}',
      estimated_latency_ms INTEGER,
      estimated_cost_usd REAL,
      max_input_bytes INTEGER,
      scope TEXT NOT NULL DEFAULT 'internal',
      enabled INTEGER NOT NULL DEFAULT 1,
      accept_delegations INTEGER NOT NULL DEFAULT 0,
      inbound_rate_limit INTEGER NOT NULL DEFAULT 10,
      outbound_rate_limit INTEGER NOT NULL DEFAULT 20,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    )
  `);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_capability_tenant_agent ON capability_registry(tenant_id, agent_id)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_capability_task_type ON capability_registry(tenant_id, task_type)`);

  db.run(sql`
    CREATE TABLE IF NOT EXISTS delegation_log (
      id TEXT PRIMARY KEY,
      tenant_id TEXT NOT NULL,
      direction TEXT NOT NULL,
      agent_id TEXT NOT NULL,
      anonymous_target_id TEXT,
      anonymous_source_id TEXT,
      task_type TEXT NOT NULL,
      status TEXT NOT NULL,
      request_size_bytes INTEGER,
      response_size_bytes INTEGER,
      execution_time_ms INTEGER,
      cost_usd REAL,
      created_at TEXT NOT NULL,
      completed_at TEXT
    )
  `);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_delegation_tenant_ts ON delegation_log(tenant_id, created_at)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_delegation_agent ON delegation_log(tenant_id, agent_id)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_delegation_status ON delegation_log(tenant_id, status)`);

  // ─── Users Table (Enterprise Auth — S3) ─────────────────
  db.run(sql`
    CREATE TABLE IF NOT EXISTS users (
      id TEXT PRIMARY KEY,
      tenant_id TEXT NOT NULL DEFAULT 'default',
      email TEXT NOT NULL,
      display_name TEXT,
      oidc_subject TEXT,
      oidc_issuer TEXT,
      role TEXT NOT NULL DEFAULT 'viewer',
      created_at INTEGER NOT NULL,
      updated_at INTEGER NOT NULL,
      last_login_at INTEGER,
      disabled_at INTEGER
    )
  `);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_users_tenant ON users(tenant_id)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_users_email ON users(email)`);
  db.run(sql`CREATE UNIQUE INDEX IF NOT EXISTS idx_users_tenant_email ON users(tenant_id, email)`);
  db.run(sql`CREATE UNIQUE INDEX IF NOT EXISTS idx_users_oidc ON users(oidc_issuer, oidc_subject)`);

  // ─── Refresh Tokens Table (Enterprise Auth — S3) ───────
  db.run(sql`
    CREATE TABLE IF NOT EXISTS refresh_tokens (
      id TEXT PRIMARY KEY,
      user_id TEXT NOT NULL REFERENCES users(id),
      tenant_id TEXT NOT NULL DEFAULT 'default',
      token_hash TEXT NOT NULL,
      expires_at INTEGER NOT NULL,
      created_at INTEGER NOT NULL,
      revoked_at INTEGER,
      user_agent TEXT,
      ip_address TEXT
    )
  `);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_refresh_tokens_user ON refresh_tokens(user_id)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_refresh_tokens_hash ON refresh_tokens(token_hash)`);

  // ─── Audit Log table (SH-2) ──────────────────────────────
  db.run(sql`
    CREATE TABLE IF NOT EXISTS audit_log (
      id TEXT PRIMARY KEY,
      timestamp TEXT NOT NULL,
      tenant_id TEXT NOT NULL,
      actor_type TEXT NOT NULL,
      actor_id TEXT NOT NULL,
      action TEXT NOT NULL,
      resource_type TEXT,
      resource_id TEXT,
      details TEXT NOT NULL DEFAULT '{}',
      ip_address TEXT,
      user_agent TEXT
    )
  `);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_audit_log_tenant_ts ON audit_log(tenant_id, timestamp)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_audit_log_action ON audit_log(action)`);

  // ─── API Key Rotation columns (SH-6) ──────────────────
  const apiKeyColsSH6 = db.all<{ name: string }>(sql`PRAGMA table_info(api_keys)`);
  const apiKeyColNamesSH6 = new Set(apiKeyColsSH6.map((c) => c.name));
  if (!apiKeyColNamesSH6.has('rotated_at')) {
    db.run(sql`ALTER TABLE api_keys ADD COLUMN rotated_at INTEGER`);
  }
  if (!apiKeyColNamesSH6.has('expires_at')) {
    db.run(sql`ALTER TABLE api_keys ADD COLUMN expires_at INTEGER`);
  }

  // ─── Discovery Config (Story 5.4) ──────────────────────
  db.run(sql`
    CREATE TABLE IF NOT EXISTS discovery_config (
      tenant_id TEXT PRIMARY KEY,
      min_trust_threshold INTEGER NOT NULL DEFAULT 60,
      delegation_enabled INTEGER NOT NULL DEFAULT 0,
      updated_at TEXT NOT NULL
    )
  `);

  // ─── Cost Budget tables (Feature 5) ──────────────────────
  db.run(sql`
    CREATE TABLE IF NOT EXISTS cost_budgets (
      id TEXT PRIMARY KEY,
      tenant_id TEXT NOT NULL,
      scope TEXT NOT NULL,
      agent_id TEXT,
      period TEXT NOT NULL,
      limit_usd REAL NOT NULL,
      on_breach TEXT NOT NULL DEFAULT 'alert',
      downgrade_target_model TEXT,
      enabled INTEGER NOT NULL DEFAULT 1,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    )
  `);

  db.run(sql`CREATE INDEX IF NOT EXISTS idx_cost_budgets_tenant ON cost_budgets(tenant_id)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_cost_budgets_tenant_enabled ON cost_budgets(tenant_id, enabled)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_cost_budgets_tenant_agent ON cost_budgets(tenant_id, agent_id)`);

  db.run(sql`
    CREATE TABLE IF NOT EXISTS cost_budget_state (
      budget_id TEXT NOT NULL,
      tenant_id TEXT NOT NULL,
      last_breach_at TEXT,
      breach_count INTEGER NOT NULL DEFAULT 0,
      current_spend REAL,
      period_start TEXT,
      PRIMARY KEY (budget_id, tenant_id)
    )
  `);

  // ─── Notification Channels (Feature 12) ──────────────────
  db.run(sql`
    CREATE TABLE IF NOT EXISTS notification_channels (
      id TEXT PRIMARY KEY,
      tenant_id TEXT NOT NULL DEFAULT 'default',
      type TEXT NOT NULL,
      name TEXT NOT NULL,
      config TEXT NOT NULL,
      enabled INTEGER NOT NULL DEFAULT 1,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    )
  `);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_notification_channels_tenant ON notification_channels(tenant_id)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_notification_channels_type ON notification_channels(tenant_id, type)`);

  db.run(sql`
    CREATE TABLE IF NOT EXISTS notification_log (
      id TEXT PRIMARY KEY,
      tenant_id TEXT NOT NULL DEFAULT 'default',
      channel_id TEXT NOT NULL,
      rule_id TEXT,
      rule_type TEXT,
      status TEXT NOT NULL,
      attempt INTEGER NOT NULL DEFAULT 1,
      error_message TEXT,
      payload_summary TEXT,
      created_at TEXT NOT NULL
    )
  `);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_notification_log_tenant ON notification_log(tenant_id)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_notification_log_channel ON notification_log(channel_id)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_notification_log_created ON notification_log(tenant_id, created_at)`);

  db.run(sql`
    CREATE TABLE IF NOT EXISTS cost_anomaly_config (
      tenant_id TEXT PRIMARY KEY,
      multiplier REAL NOT NULL DEFAULT 3.0,
      min_sessions INTEGER NOT NULL DEFAULT 5,
      enabled INTEGER NOT NULL DEFAULT 1,
      updated_at TEXT NOT NULL
    )
  `);

  // ─── Eval Framework tables (Feature 15) ──────────────────

  db.run(sql`
    CREATE TABLE IF NOT EXISTS eval_datasets (
      id            TEXT PRIMARY KEY,
      tenant_id     TEXT NOT NULL,
      agent_id      TEXT,
      name          TEXT NOT NULL,
      description   TEXT,
      version       INTEGER NOT NULL DEFAULT 1,
      parent_id     TEXT,
      folder        TEXT,
      immutable     INTEGER NOT NULL DEFAULT 0,
      created_at    TEXT NOT NULL,
      updated_at    TEXT NOT NULL
    )
  `);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_eval_datasets_tenant ON eval_datasets(tenant_id)`);
  // #224: dataset folders — additive column for existing dbs.
  {
    const cols = db.all<{ name: string }>(sql`PRAGMA table_info(eval_datasets)`);
    if (!new Set(cols.map((c) => c.name)).has('folder')) {
      db.run(sql`ALTER TABLE eval_datasets ADD COLUMN folder TEXT`);
    }
  }

  db.run(sql`
    CREATE TABLE IF NOT EXISTS eval_test_cases (
      id                TEXT PRIMARY KEY,
      dataset_id        TEXT NOT NULL REFERENCES eval_datasets(id) ON DELETE CASCADE,
      tenant_id         TEXT NOT NULL,
      input             TEXT NOT NULL,
      expected_output   TEXT,
      tags              TEXT NOT NULL DEFAULT '[]',
      metadata          TEXT NOT NULL DEFAULT '{}',
      scoring_criteria  TEXT,
      sort_order        INTEGER NOT NULL DEFAULT 0,
      created_at        TEXT NOT NULL
    )
  `);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_eval_test_cases_dataset ON eval_test_cases(dataset_id)`);

  db.run(sql`
    CREATE TABLE IF NOT EXISTS eval_runs (
      id              TEXT PRIMARY KEY,
      tenant_id       TEXT NOT NULL,
      dataset_id      TEXT NOT NULL REFERENCES eval_datasets(id),
      dataset_version INTEGER NOT NULL,
      agent_id        TEXT NOT NULL,
      webhook_url     TEXT NOT NULL,
      status          TEXT NOT NULL DEFAULT 'pending',
      config          TEXT NOT NULL DEFAULT '{}',
      baseline_run_id TEXT,
      prompt_version_id TEXT,
      model_id        TEXT,
      triggered_by    TEXT,
      triggered_by_method TEXT,
      total_cases     INTEGER NOT NULL DEFAULT 0,
      passed_cases    INTEGER NOT NULL DEFAULT 0,
      failed_cases    INTEGER NOT NULL DEFAULT 0,
      avg_score       REAL,
      total_cost_usd  REAL,
      total_duration_ms INTEGER,
      started_at      TEXT,
      completed_at    TEXT,
      created_at      TEXT NOT NULL,
      error           TEXT
    )
  `);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_eval_runs_tenant ON eval_runs(tenant_id)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_eval_runs_dataset ON eval_runs(dataset_id)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_eval_runs_agent ON eval_runs(tenant_id, agent_id)`);

  // Prompt/model variant + triggering actor on runs (#121) — additive columns
  // for DBs created before this migration.
  const evalRunCols = db.all<{ name: string }>(sql`PRAGMA table_info(eval_runs)`);
  if (!evalRunCols.some((c) => c.name === 'prompt_version_id')) {
    db.run(sql`ALTER TABLE eval_runs ADD COLUMN prompt_version_id TEXT`);
  }
  if (!evalRunCols.some((c) => c.name === 'model_id')) {
    db.run(sql`ALTER TABLE eval_runs ADD COLUMN model_id TEXT`);
  }
  if (!evalRunCols.some((c) => c.name === 'triggered_by')) {
    db.run(sql`ALTER TABLE eval_runs ADD COLUMN triggered_by TEXT`);
  }
  if (!evalRunCols.some((c) => c.name === 'triggered_by_method')) {
    db.run(sql`ALTER TABLE eval_runs ADD COLUMN triggered_by_method TEXT`);
  }

  db.run(sql`
    CREATE TABLE IF NOT EXISTS eval_results (
      id              TEXT PRIMARY KEY,
      run_id          TEXT NOT NULL REFERENCES eval_runs(id) ON DELETE CASCADE,
      test_case_id    TEXT NOT NULL REFERENCES eval_test_cases(id),
      tenant_id       TEXT NOT NULL,
      session_id      TEXT,
      actual_output   TEXT,
      score           REAL NOT NULL,
      passed          INTEGER NOT NULL,
      scorer_type     TEXT NOT NULL,
      scorer_details  TEXT NOT NULL DEFAULT '{}',
      latency_ms      INTEGER,
      cost_usd        REAL,
      token_count     INTEGER,
      error           TEXT,
      created_at      TEXT NOT NULL
    )
  `);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_eval_results_run ON eval_results(run_id)`);

  // ─── Evaluator Catalog (#55 Phase 4) ────────────────────────
  db.run(sql`
    CREATE TABLE IF NOT EXISTS evaluator_definitions (
      id              TEXT PRIMARY KEY,
      tenant_id       TEXT NOT NULL,
      name            TEXT NOT NULL,
      description     TEXT,
      scorer_type     TEXT NOT NULL,
      config_template TEXT NOT NULL DEFAULT '{}',
      tags            TEXT NOT NULL DEFAULT '[]',
      builtin         INTEGER NOT NULL DEFAULT 0,
      status          TEXT NOT NULL DEFAULT 'draft',
      published_by    TEXT,
      published_at    TEXT,
      verified_at     TEXT,
      created_at      TEXT NOT NULL,
      updated_at      TEXT NOT NULL
    )
  `);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_evaluator_defs_tenant ON evaluator_definitions(tenant_id)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_evaluator_defs_tenant_type ON evaluator_definitions(tenant_id, scorer_type)`);

  // ─── Prompt Management tables (Feature 19) ──────────────────

  db.run(sql`
    CREATE TABLE IF NOT EXISTS prompt_templates (
      id TEXT PRIMARY KEY,
      tenant_id TEXT NOT NULL,
      name TEXT NOT NULL,
      description TEXT,
      category TEXT NOT NULL DEFAULT 'general',
      folder TEXT,
      current_version_id TEXT,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL,
      deleted_at TEXT
    )
  `);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_prompt_templates_tenant ON prompt_templates(tenant_id)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_prompt_templates_tenant_cat ON prompt_templates(tenant_id, category)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_prompt_templates_tenant_name ON prompt_templates(tenant_id, name)`);
  // #253: prompt folders — additive column for existing dbs.
  {
    const cols = db.all<{ name: string }>(sql`PRAGMA table_info(prompt_templates)`);
    if (!new Set(cols.map((c) => c.name)).has('folder')) {
      db.run(sql`ALTER TABLE prompt_templates ADD COLUMN folder TEXT`);
    }
  }

  db.run(sql`
    CREATE TABLE IF NOT EXISTS prompt_versions (
      id TEXT PRIMARY KEY,
      template_id TEXT NOT NULL,
      tenant_id TEXT NOT NULL,
      version_number INTEGER NOT NULL,
      content TEXT NOT NULL,
      variables TEXT,
      config TEXT,
      prompt_type TEXT NOT NULL DEFAULT 'text',
      content_hash TEXT NOT NULL,
      changelog TEXT,
      created_by TEXT,
      created_at TEXT NOT NULL,
      UNIQUE(template_id, version_number)
    )
  `);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_prompt_versions_tenant ON prompt_versions(tenant_id)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_prompt_versions_hash ON prompt_versions(content_hash)`);
  // Prompt runtime primitives (#145): config + chat type on existing tables.
  {
    const pvCols = db.all<{ name: string }>(sql`PRAGMA table_info(prompt_versions)`);
    const pvNames = new Set(pvCols.map((c) => c.name));
    if (!pvNames.has('config')) db.run(sql`ALTER TABLE prompt_versions ADD COLUMN config TEXT`);
    if (!pvNames.has('prompt_type')) db.run(sql`ALTER TABLE prompt_versions ADD COLUMN prompt_type TEXT NOT NULL DEFAULT 'text'`);
  }

  db.run(sql`
    CREATE TABLE IF NOT EXISTS prompt_fingerprints (
      content_hash TEXT NOT NULL,
      tenant_id TEXT NOT NULL,
      agent_id TEXT NOT NULL,
      first_seen_at TEXT NOT NULL,
      last_seen_at TEXT NOT NULL,
      call_count INTEGER NOT NULL DEFAULT 0,
      template_id TEXT,
      sample_content TEXT,
      PRIMARY KEY (content_hash, tenant_id, agent_id)
    )
  `);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_prompt_fp_tenant ON prompt_fingerprints(tenant_id)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_prompt_fp_agent ON prompt_fingerprints(tenant_id, agent_id)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_prompt_fp_template ON prompt_fingerprints(template_id)`);

  // ─── Prompt deploy ledger (#120) ───────────────────────────
  // Append-only, server-authored hash chain per (tenant_id, environment).
  db.run(sql`
    CREATE TABLE IF NOT EXISTS prompt_deployments (
      id TEXT PRIMARY KEY,
      tenant_id TEXT NOT NULL,
      template_id TEXT NOT NULL,
      environment TEXT NOT NULL,
      version_id TEXT NOT NULL,
      action TEXT NOT NULL,
      status TEXT NOT NULL DEFAULT 'committed',
      actor_id TEXT,
      actor_method TEXT,
      approver_id TEXT,
      approval_ref TEXT,
      note TEXT,
      seq INTEGER NOT NULL,
      prev_hash TEXT,
      hash TEXT NOT NULL,
      created_at TEXT NOT NULL
    )
  `);
  db.run(sql`CREATE UNIQUE INDEX IF NOT EXISTS idx_prompt_deploy_chain_seq ON prompt_deployments(tenant_id, environment, seq)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_prompt_deploy_tenant_env ON prompt_deployments(tenant_id, environment)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_prompt_deploy_template ON prompt_deployments(tenant_id, template_id, environment)`);

  // ─── Annotation queues (#122) ──────────────────────────────
  db.run(sql`
    CREATE TABLE IF NOT EXISTS annotation_queues (
      id          TEXT PRIMARY KEY,
      tenant_id   TEXT NOT NULL,
      name        TEXT NOT NULL,
      description TEXT,
      config      TEXT NOT NULL DEFAULT '{}',
      created_by  TEXT,
      created_at  TEXT NOT NULL,
      updated_at  TEXT NOT NULL
    )
  `);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_annotation_queues_tenant ON annotation_queues(tenant_id)`);

  db.run(sql`
    CREATE TABLE IF NOT EXISTS annotation_items (
      id             TEXT PRIMARY KEY,
      queue_id       TEXT NOT NULL REFERENCES annotation_queues(id) ON DELETE CASCADE,
      tenant_id      TEXT NOT NULL,
      session_id     TEXT NOT NULL,
      trace_id       TEXT,
      status         TEXT NOT NULL DEFAULT 'pending',
      assignee       TEXT,
      due_at         TEXT,
      score_event_id TEXT,
      created_at     TEXT NOT NULL,
      updated_at     TEXT NOT NULL
    )
  `);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_annotation_items_queue ON annotation_items(tenant_id, queue_id, status)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_annotation_items_assignee ON annotation_items(tenant_id, assignee)`);

  // ─── Scale & retention: time-bucketed rollups + chain anchors (#124) ───
  // Aggregate cost/usage/health keyed on verified agent + model + hour bucket,
  // retaining contributing pricing_versions so per-agent cost reconciliation
  // survives a raw-event purge.
  db.run(sql`
    CREATE TABLE IF NOT EXISTS cost_rollups (
      tenant_id         TEXT NOT NULL,
      verified_agent_id TEXT NOT NULL DEFAULT '',
      model             TEXT NOT NULL DEFAULT '',
      bucket_start      TEXT NOT NULL,
      granularity       TEXT NOT NULL DEFAULT 'hour',
      event_count       INTEGER NOT NULL DEFAULT 0,
      tool_call_count   INTEGER NOT NULL DEFAULT 0,
      error_count       INTEGER NOT NULL DEFAULT 0,
      llm_call_count    INTEGER NOT NULL DEFAULT 0,
      input_tokens      INTEGER NOT NULL DEFAULT 0,
      output_tokens     INTEGER NOT NULL DEFAULT 0,
      cache_read_tokens INTEGER NOT NULL DEFAULT 0,
      cache_write_tokens INTEGER NOT NULL DEFAULT 0,
      cost_usd          REAL NOT NULL DEFAULT 0,
      latency_sum_ms    REAL NOT NULL DEFAULT 0,
      latency_count     INTEGER NOT NULL DEFAULT 0,
      pricing_versions  TEXT NOT NULL DEFAULT '[]',
      updated_at        TEXT NOT NULL,
      PRIMARY KEY (tenant_id, verified_agent_id, model, bucket_start, granularity)
    )
  `);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_cost_rollups_tenant_bucket ON cost_rollups(tenant_id, bucket_start)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_cost_rollups_tenant_agent_bucket ON cost_rollups(tenant_id, verified_agent_id, bucket_start)`);

  // Signed checkpoints over contiguous chain segments — let cold ranges be
  // verified (and safely purged) without a full chain walk.
  db.run(sql`
    CREATE TABLE IF NOT EXISTS chain_anchors (
      id                 TEXT PRIMARY KEY,
      tenant_id          TEXT NOT NULL,
      scope              TEXT NOT NULL DEFAULT 'session',
      session_id         TEXT NOT NULL,
      first_prev_hash    TEXT,
      last_hash          TEXT NOT NULL,
      event_count        INTEGER NOT NULL,
      segment_digest     TEXT NOT NULL,
      chained            INTEGER NOT NULL DEFAULT 1,
      ts_min             TEXT,
      ts_max             TEXT,
      pricing_versions   TEXT NOT NULL DEFAULT '[]',
      verified_agent_ids TEXT NOT NULL DEFAULT '[]',
      signature          TEXT,
      created_at         TEXT NOT NULL
    )
  `);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_chain_anchors_tenant_session ON chain_anchors(tenant_id, session_id)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_chain_anchors_tenant_tsmax ON chain_anchors(tenant_id, ts_max)`);

  // ─── LLM connections — bring-your-own provider keys (#143) ───
  // `encrypted_key` is AES-256-GCM (lib/secret-box); the plaintext key is never
  // stored or returned by the API (only `key_last4` is shown).
  db.run(sql`
    CREATE TABLE IF NOT EXISTS llm_connections (
      id            TEXT PRIMARY KEY,
      tenant_id     TEXT NOT NULL DEFAULT 'default',
      provider      TEXT NOT NULL,
      name          TEXT NOT NULL,
      base_url      TEXT,
      default_model TEXT,
      encrypted_key TEXT NOT NULL,
      key_last4     TEXT NOT NULL,
      created_by    TEXT,
      created_at    TEXT NOT NULL,
      updated_at    TEXT NOT NULL
    )
  `);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_llm_connections_tenant ON llm_connections(tenant_id)`);

  // ─── Org → project → member hierarchy (#147, sub-PR 1) ───
  // The SQLite mirror of the cloud org model + a new `projects` tier. Additive:
  // org_id/project_id columns on data tables + TenantScopedStore wiring land in a
  // follow-up sub-PR. 'auditor' is added to the role set in the role-unification PR.
  db.run(sql`
    CREATE TABLE IF NOT EXISTS orgs (
      id         TEXT PRIMARY KEY,
      name       TEXT NOT NULL,
      slug       TEXT NOT NULL,
      plan       TEXT NOT NULL DEFAULT 'free',
      settings   TEXT NOT NULL DEFAULT '{}',
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    )
  `);
  db.run(sql`CREATE UNIQUE INDEX IF NOT EXISTS idx_orgs_slug ON orgs(slug)`);

  db.run(sql`
    CREATE TABLE IF NOT EXISTS projects (
      id         TEXT PRIMARY KEY,
      org_id     TEXT NOT NULL,
      name       TEXT NOT NULL,
      slug       TEXT NOT NULL,
      settings   TEXT NOT NULL DEFAULT '{}',
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    )
  `);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_projects_org ON projects(org_id)`);
  db.run(sql`CREATE UNIQUE INDEX IF NOT EXISTS idx_projects_org_slug ON projects(org_id, slug)`);

  db.run(sql`
    CREATE TABLE IF NOT EXISTS org_members (
      org_id     TEXT NOT NULL,
      user_id    TEXT NOT NULL,
      role       TEXT NOT NULL DEFAULT 'member',
      invited_by TEXT,
      joined_at  TEXT NOT NULL,
      PRIMARY KEY (org_id, user_id)
    )
  `);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_org_members_user ON org_members(user_id)`);

  db.run(sql`
    CREATE TABLE IF NOT EXISTS project_members (
      project_id TEXT NOT NULL,
      user_id    TEXT NOT NULL,
      role       TEXT NOT NULL,
      joined_at  TEXT NOT NULL,
      PRIMARY KEY (project_id, user_id)
    )
  `);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_project_members_user ON project_members(user_id)`);

  // Backfill the default org + project so existing single-tenant ('default') data
  // has a home once scoping moves to (org_id, project_id).
  const nowOrg = new Date().toISOString();
  db.run(sql`INSERT OR IGNORE INTO orgs (id, name, slug, plan, settings, created_at, updated_at) VALUES ('default', 'Default', 'default', 'free', '{}', ${nowOrg}, ${nowOrg})`);
  db.run(sql`INSERT OR IGNORE INTO projects (id, org_id, name, slug, settings, created_at, updated_at) VALUES ('default', 'default', 'Default', 'default', '{}', ${nowOrg}, ${nowOrg})`);

  // ─── Prompt A/B testing (#150) ───
  // A weighted set of versions live concurrently in one environment; the resolver
  // picks a variant (sticky by key). One active test per (template, environment).
  db.run(sql`
    CREATE TABLE IF NOT EXISTS prompt_ab_tests (
      id          TEXT PRIMARY KEY,
      tenant_id   TEXT NOT NULL,
      template_id TEXT NOT NULL,
      environment TEXT NOT NULL,
      variants    TEXT NOT NULL,
      status      TEXT NOT NULL DEFAULT 'active',
      created_by  TEXT,
      created_at  TEXT NOT NULL,
      updated_at  TEXT NOT NULL
    )
  `);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_prompt_ab_tests_lookup ON prompt_ab_tests(tenant_id, template_id, environment, status)`);

  // ─── Enterprise SSO connections (#148) ──────────────────
  // Per-org SAML/OIDC connection config + group→role mapping + domain
  // enforcement. INTEGER booleans (0/1) so the same SQL binds on both dialects.
  db.run(sql`
    CREATE TABLE IF NOT EXISTS sso_connections (
      id TEXT PRIMARY KEY,
      org_id TEXT NOT NULL DEFAULT 'default',
      type TEXT NOT NULL,
      name TEXT NOT NULL,
      enabled INTEGER NOT NULL DEFAULT 0,
      domain TEXT,
      domain_verified INTEGER NOT NULL DEFAULT 0,
      enforced INTEGER NOT NULL DEFAULT 0,
      config TEXT NOT NULL DEFAULT '{}',
      group_role_mappings TEXT NOT NULL DEFAULT '{}',
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    )
  `);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_sso_connections_org ON sso_connections(org_id)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_sso_connections_domain ON sso_connections(domain)`);

  // ─── SCIM 2.0 groups (#148) ─────────────────────────────
  db.run(sql`
    CREATE TABLE IF NOT EXISTS scim_groups (
      id TEXT PRIMARY KEY,
      tenant_id TEXT NOT NULL DEFAULT 'default',
      display_name TEXT NOT NULL,
      external_id TEXT,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    )
  `);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_scim_groups_tenant ON scim_groups(tenant_id)`);
  db.run(sql`
    CREATE TABLE IF NOT EXISTS scim_group_members (
      group_id TEXT NOT NULL,
      user_id TEXT NOT NULL,
      PRIMARY KEY (group_id, user_id)
    )
  `);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_scim_group_members_user ON scim_group_members(user_id)`);

  // #59 — per-tenant service tokens (machine-to-machine auth for /api/internal).
  // Raw-SQL-only (not in the drizzle schema modules), mirrored by drizzle/0020 for pg.
  // Timestamps are unix SECONDS (int4-safe on pg). Only the sha256 hash is stored.
  db.run(sql`
    CREATE TABLE IF NOT EXISTS service_tokens (
      id           TEXT PRIMARY KEY,
      token_hash   TEXT NOT NULL,
      tenant_id    TEXT NOT NULL DEFAULT 'default',
      name         TEXT NOT NULL,
      created_at   INTEGER NOT NULL,
      last_used_at INTEGER,
      revoked_at   INTEGER,
      rotated_at   INTEGER,
      expires_at   INTEGER,
      created_by   TEXT
    )
  `);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_service_tokens_hash ON service_tokens(token_hash)`);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_service_tokens_tenant ON service_tokens(tenant_id)`);

  // #252: offloaded media blobs (base64 image/audio moved out of event payloads).
  db.run(sql`
    CREATE TABLE IF NOT EXISTS media_objects (
      id           TEXT PRIMARY KEY,
      tenant_id    TEXT NOT NULL,
      content_type TEXT NOT NULL,
      size         INTEGER NOT NULL,
      data         TEXT NOT NULL,
      created_at   TEXT NOT NULL
    )
  `);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_media_objects_tenant ON media_objects(tenant_id)`);

  // #253: per-tenant GitHub prompt-sync config (PAT encrypted via secret-box).
  db.run(sql`
    CREATE TABLE IF NOT EXISTS prompt_github_sync (
      tenant_id       TEXT PRIMARY KEY,
      owner           TEXT NOT NULL,
      repo            TEXT NOT NULL,
      base_path       TEXT NOT NULL DEFAULT 'prompts',
      encrypted_token TEXT NOT NULL,
      token_last4     TEXT NOT NULL,
      updated_at      TEXT NOT NULL
    )
  `);

  // #265: last-synced snapshot per prompt (repo blob sha + local content hash) so
  // a two-way sync can tell which side changed and flag conflicts.
  db.run(sql`
    CREATE TABLE IF NOT EXISTS prompt_github_sync_state (
      tenant_id  TEXT NOT NULL,
      path       TEXT NOT NULL,
      repo_sha   TEXT NOT NULL,
      local_hash TEXT NOT NULL,
      updated_at TEXT NOT NULL,
      PRIMARY KEY (tenant_id, path)
    )
  `);

  // #254: online-eval (LiveEvalEngine) config — sample live sessions + score them.
  db.run(sql`
    CREATE TABLE IF NOT EXISTS live_eval_config (
      tenant_id     TEXT PRIMARY KEY,
      enabled       INTEGER NOT NULL DEFAULT 0,
      sampling_rate REAL NOT NULL DEFAULT 0.1,
      scorer_type   TEXT NOT NULL DEFAULT 'regex',
      scorer_config TEXT NOT NULL DEFAULT '{}',
      updated_at    TEXT NOT NULL
    )
  `);

  // #99: RFC 3161 trusted-timestamp tokens anchoring audit-export digests.
  db.run(sql`
    CREATE TABLE IF NOT EXISTS audit_timestamps (
      id           TEXT PRIMARY KEY,
      tenant_id    TEXT NOT NULL,
      subject_hash TEXT NOT NULL,
      tsa_url      TEXT NOT NULL,
      token        TEXT NOT NULL,
      gen_time     TEXT,
      granted      INTEGER NOT NULL DEFAULT 0,
      created_at   TEXT NOT NULL
    )
  `);
  db.run(sql`CREATE INDEX IF NOT EXISTS idx_audit_timestamps_tenant ON audit_timestamps(tenant_id)`);
}

/**
 * Verify that WAL mode is enabled.
 */
export function verifyPragmas(db: SqliteDb): {
  journalMode: string;
  synchronous: string;
  cacheSize: number;
  busyTimeout: number;
  foreignKeys: boolean;
} {
  const journalMode =
    db.get<{ journal_mode: string }>(sql`PRAGMA journal_mode`)?.journal_mode ?? '';
  const synchronous = db.get<{ synchronous: number }>(sql`PRAGMA synchronous`)?.synchronous ?? -1;
  const cacheSize = db.get<{ cache_size: number }>(sql`PRAGMA cache_size`)?.cache_size ?? 0;
  // SQLite returns { timeout: N } for PRAGMA busy_timeout
  const busyTimeout = db.get<{ timeout: number }>(sql`PRAGMA busy_timeout`)?.timeout ?? 0;
  const foreignKeys =
    db.get<{ foreign_keys: number }>(sql`PRAGMA foreign_keys`)?.foreign_keys === 1;

  // synchronous: 0=OFF, 1=NORMAL, 2=FULL, 3=EXTRA
  const syncNames: Record<number, string> = { 0: 'OFF', 1: 'NORMAL', 2: 'FULL', 3: 'EXTRA' };

  return {
    journalMode,
    synchronous: syncNames[synchronous] ?? String(synchronous),
    cacheSize,
    busyTimeout,
    foreignKeys,
  };
}