import { randomBytes, randomUUID } from "node:crypto";
import type { DatabaseAdapter } from "./db.js";

interface Migration {
  version: number;
  name: string;
  up: (db: DatabaseAdapter) => Promise<void>;
}

function nowIso(): string {
  return new Date().toISOString();
}

const MIGRATIONS: readonly Migration[] = [
  {
    version: 1,
    name: "init_settings_and_schema_migrations",
    up: async (db: DatabaseAdapter) => {
      await db.exec(`
        PRAGMA foreign_keys = ON;
        CREATE TABLE IF NOT EXISTS settings (
          key TEXT PRIMARY KEY CHECK (key IN ('machine_id', 'hmac_salt')),
          value TEXT NOT NULL,
          updated_at TEXT NOT NULL
        );
        CREATE TABLE IF NOT EXISTS schema_migrations (
          version INTEGER PRIMARY KEY,
          name TEXT NOT NULL,
          applied_at TEXT NOT NULL
        );
      `);
    }
  },
  {
    version: 2,
    name: "add_scan_runs_and_source_files",
    up: async (db: DatabaseAdapter) => {
      await db.exec(`
        CREATE TABLE IF NOT EXISTS scan_runs (
          id TEXT PRIMARY KEY,
          started_at TEXT NOT NULL,
          completed_at TEXT,
          status TEXT NOT NULL CHECK (status IN ('running', 'completed', 'partial', 'failed', 'interrupted')),
          sources_json TEXT NOT NULL,
          dry_run INTEGER NOT NULL DEFAULT 0,
          force INTEGER NOT NULL DEFAULT 0,
          files_seen INTEGER NOT NULL DEFAULT 0,
          files_imported INTEGER NOT NULL DEFAULT 0,
          files_skipped INTEGER NOT NULL DEFAULT 0,
          files_failed INTEGER NOT NULL DEFAULT 0,
          sessions_upserted INTEGER NOT NULL DEFAULT 0,
          messages_upserted INTEGER NOT NULL DEFAULT 0,
          runtime_events_upserted INTEGER NOT NULL DEFAULT 0,
          warnings_json TEXT,
          metadata_json TEXT
        );

        CREATE TABLE IF NOT EXISTS source_files (
          id TEXT PRIMARY KEY,
          source TEXT NOT NULL CHECK (source IN ('codex', 'copilot', 'claude', 'hook_log')),
          path_hash TEXT NOT NULL,
          file_hash TEXT,
          size_bytes INTEGER NOT NULL DEFAULT 0,
          mtime_ms INTEGER,
          first_seen_at TEXT NOT NULL,
          last_seen_at TEXT NOT NULL,
          last_imported_at TEXT,
          last_status TEXT NOT NULL CHECK (last_status IN ('pending', 'imported', 'failed')),
          last_error_code TEXT,
          last_error_severity TEXT CHECK (last_error_severity IN ('warning', 'error')),
          warnings_json TEXT,
          metadata_json TEXT,
          UNIQUE(source, path_hash)
        );
      `);
    }
  }
  ,
  {
    version: 3,
    name: "add_sessions_message_metrics_runtime_events",
    up: async (db: DatabaseAdapter) => {
      await db.exec(`
        CREATE TABLE IF NOT EXISTS sessions (
          id TEXT PRIMARY KEY,
          source TEXT NOT NULL CHECK (source IN ('codex', 'copilot', 'claude')),
          source_session_hash TEXT NOT NULL,
          source_file_id TEXT NOT NULL,
          machine_id TEXT NOT NULL,
          project_id TEXT,
          mode TEXT NOT NULL DEFAULT 'unknown' CHECK (mode IN ('chat', 'agent', 'edit', 'unknown')),
          model_primary TEXT,
          token_available INTEGER NOT NULL DEFAULT 0,
          message_count INTEGER NOT NULL DEFAULT 0,
          user_message_count INTEGER NOT NULL DEFAULT 0,
          assistant_message_count INTEGER NOT NULL DEFAULT 0,
          created_at TEXT,
          updated_at TEXT NOT NULL,
          created_at_source TEXT CHECK (created_at_source IN ('native', 'updated_at', 'file_mtime', 'unknown')),
          client_surface TEXT CHECK (client_surface IN ('cli', 'desktop', 'vscode', 'vscode_insiders', 'code', 'unknown')),
          metadata_json TEXT,
          FOREIGN KEY(source_file_id) REFERENCES source_files(id) ON DELETE CASCADE
        );

        CREATE TABLE IF NOT EXISTS message_metrics (
          id TEXT PRIMARY KEY,
          session_id TEXT NOT NULL,
          source_file_id TEXT NOT NULL,
          seq INTEGER NOT NULL,
          role TEXT NOT NULL CHECK (role IN ('user', 'assistant', 'unknown')),
          model TEXT,
          input_tokens INTEGER DEFAULT 0,
          output_tokens INTEGER DEFAULT 0,
          cache_read_tokens INTEGER DEFAULT 0,
          cache_write_tokens INTEGER DEFAULT 0,
          token_available INTEGER NOT NULL DEFAULT 0,
          partial_token_data INTEGER NOT NULL DEFAULT 0,
          created_at TEXT,
          timestamp_source TEXT CHECK (timestamp_source IN ('native', 'session', 'file_mtime', 'fallback', 'unknown')),
          metadata_json TEXT,
          FOREIGN KEY(session_id) REFERENCES sessions(id) ON DELETE CASCADE,
          FOREIGN KEY(source_file_id) REFERENCES source_files(id) ON DELETE CASCADE,
          UNIQUE(session_id, seq)
        );

        CREATE TABLE IF NOT EXISTS runtime_events (
          id TEXT PRIMARY KEY,
          source TEXT NOT NULL CHECK (source IN ('codex', 'copilot', 'claude', 'hook_log')),
          source_file_id TEXT NOT NULL,
          session_id TEXT,
          event_type TEXT NOT NULL CHECK (event_type IN ('mcp', 'skill', 'hook')),
          event_origin TEXT NOT NULL CHECK (event_origin IN ('observed_structured', 'hook_hint')),
          event_name TEXT NOT NULL,
          mcp_server_name TEXT,
          tool_name TEXT,
          skill_name TEXT,
          hook_name TEXT,
          hook_event TEXT,
          source_line_no INTEGER,
          event_seq INTEGER NOT NULL DEFAULT 0,
          args_keys_json TEXT,
          args_hash TEXT,
          args_bytes INTEGER DEFAULT 0,
          occurred_at TEXT NOT NULL,
          timestamp_source TEXT CHECK (timestamp_source IN ('native', 'session', 'file_mtime', 'fallback', 'unknown')),
          is_self_event INTEGER NOT NULL DEFAULT 0,
          metadata_json TEXT,
          FOREIGN KEY(source_file_id) REFERENCES source_files(id) ON DELETE CASCADE,
          FOREIGN KEY(session_id) REFERENCES sessions(id) ON DELETE CASCADE
        );
      `);
    }
  }
  ,
  {
    version: 4,
    name: "extend_sources_post_mvp_m8",
    up: async (db: DatabaseAdapter) => {
      await db.exec(`
        PRAGMA foreign_keys = OFF;

        CREATE TABLE source_files_new_m8 (
          id TEXT PRIMARY KEY,
          source TEXT NOT NULL CHECK (source IN ('codex','copilot','claude','hook_log')),
          path_hash TEXT NOT NULL,
          file_hash TEXT,
          size_bytes INTEGER NOT NULL DEFAULT 0,
          mtime_ms INTEGER,
          first_seen_at TEXT NOT NULL,
          last_seen_at TEXT NOT NULL,
          last_imported_at TEXT,
          last_status TEXT NOT NULL CHECK (last_status IN ('pending','imported','failed')),
          last_error_code TEXT,
          last_error_severity TEXT CHECK (last_error_severity IN ('warning','error')),
          warnings_json TEXT,
          metadata_json TEXT,
          UNIQUE(source, path_hash)
        );
        INSERT INTO source_files_new_m8 SELECT * FROM source_files;

        CREATE TABLE sessions_new_m8 (
          id TEXT PRIMARY KEY,
          source TEXT NOT NULL CHECK (source IN ('codex','copilot','claude')),
          source_session_hash TEXT NOT NULL,
          source_file_id TEXT NOT NULL,
          machine_id TEXT NOT NULL,
          project_id TEXT,
          mode TEXT NOT NULL DEFAULT 'unknown' CHECK (mode IN ('chat','agent','edit','unknown')),
          model_primary TEXT,
          token_available INTEGER NOT NULL DEFAULT 0,
          message_count INTEGER NOT NULL DEFAULT 0,
          user_message_count INTEGER NOT NULL DEFAULT 0,
          assistant_message_count INTEGER NOT NULL DEFAULT 0,
          created_at TEXT,
          updated_at TEXT NOT NULL,
          created_at_source TEXT CHECK (created_at_source IN ('native','updated_at','file_mtime','unknown')),
          client_surface TEXT CHECK (client_surface IN ('cli', 'desktop', 'vscode', 'vscode_insiders', 'code', 'unknown')),
          metadata_json TEXT,
          FOREIGN KEY(source_file_id) REFERENCES source_files_new_m8(id) ON DELETE CASCADE
        );
        INSERT INTO sessions_new_m8(id, source, source_session_hash, source_file_id, machine_id, project_id, mode, model_primary, token_available, message_count, user_message_count, assistant_message_count, created_at, updated_at, created_at_source, client_surface, metadata_json)
        SELECT id, source, source_session_hash, source_file_id, machine_id, project_id, mode, model_primary, token_available, message_count, user_message_count, assistant_message_count, created_at, updated_at, created_at_source, 'unknown', metadata_json
        FROM sessions;

        CREATE TABLE message_metrics_new_m8 (
          id TEXT PRIMARY KEY,
          session_id TEXT NOT NULL,
          source_file_id TEXT NOT NULL,
          seq INTEGER NOT NULL,
          role TEXT NOT NULL CHECK (role IN ('user', 'assistant', 'unknown')),
          model TEXT,
          input_tokens INTEGER DEFAULT 0,
          output_tokens INTEGER DEFAULT 0,
          cache_read_tokens INTEGER DEFAULT 0,
          cache_write_tokens INTEGER DEFAULT 0,
          token_available INTEGER NOT NULL DEFAULT 0,
          partial_token_data INTEGER NOT NULL DEFAULT 0,
          created_at TEXT,
          timestamp_source TEXT CHECK (timestamp_source IN ('native', 'session', 'file_mtime', 'fallback', 'unknown')),
          metadata_json TEXT,
          FOREIGN KEY(session_id) REFERENCES sessions_new_m8(id) ON DELETE CASCADE,
          FOREIGN KEY(source_file_id) REFERENCES source_files_new_m8(id) ON DELETE CASCADE,
          UNIQUE(session_id, seq)
        );
        INSERT INTO message_metrics_new_m8 SELECT * FROM message_metrics;

        CREATE TABLE runtime_events_new_m8 (
          id TEXT PRIMARY KEY,
          source TEXT NOT NULL CHECK (source IN ('codex','copilot','claude','hook_log')),
          source_file_id TEXT NOT NULL,
          session_id TEXT,
          event_type TEXT NOT NULL CHECK (event_type IN ('mcp','skill','hook')),
          event_origin TEXT NOT NULL CHECK (event_origin IN ('observed_structured','hook_hint')),
          event_name TEXT NOT NULL,
          mcp_server_name TEXT,
          tool_name TEXT,
          skill_name TEXT,
          hook_name TEXT,
          hook_event TEXT,
          source_line_no INTEGER,
          event_seq INTEGER NOT NULL DEFAULT 0,
          args_keys_json TEXT,
          args_hash TEXT,
          args_bytes INTEGER DEFAULT 0,
          occurred_at TEXT NOT NULL,
          timestamp_source TEXT CHECK (timestamp_source IN ('native','session','file_mtime','fallback','unknown')),
          is_self_event INTEGER NOT NULL DEFAULT 0,
          metadata_json TEXT,
          FOREIGN KEY(source_file_id) REFERENCES source_files_new_m8(id) ON DELETE CASCADE,
          FOREIGN KEY(session_id) REFERENCES sessions_new_m8(id) ON DELETE CASCADE
        );
        INSERT INTO runtime_events_new_m8 SELECT * FROM runtime_events;

        DROP TABLE runtime_events;
        DROP TABLE message_metrics;
        DROP TABLE sessions;
        DROP TABLE source_files;

        ALTER TABLE source_files_new_m8 RENAME TO source_files;
        ALTER TABLE sessions_new_m8 RENAME TO sessions;
        ALTER TABLE message_metrics_new_m8 RENAME TO message_metrics;
        ALTER TABLE runtime_events_new_m8 RENAME TO runtime_events;

        PRAGMA foreign_keys = ON;
      `);
    }
  }
  ,
  {
    version: 5,
    name: "add_delete_runs_post_mvp",
    up: async (db: DatabaseAdapter) => {
      await db.exec(`
        CREATE TABLE IF NOT EXISTS delete_runs (
          id TEXT PRIMARY KEY,
          plan_id TEXT NOT NULL,
          started_at TEXT NOT NULL,
          completed_at TEXT,
          status TEXT NOT NULL CHECK (status IN ('planned', 'completed', 'failed', 'cancelled')),
          dry_run INTEGER NOT NULL DEFAULT 1,
          sources_json TEXT NOT NULL,
          filters_json TEXT NOT NULL,
          matched_json TEXT NOT NULL,
          deleted_json TEXT,
          warnings_json TEXT
        );
      `);
    }
  },
  {
    version: 6,
    name: "hook_log_incremental_offsets",
    up: async (db: DatabaseAdapter) => {
      await db.exec(`
        ALTER TABLE source_files ADD COLUMN last_read_offset INTEGER NOT NULL DEFAULT 0;
      `).catch(() => undefined);
      await db.exec(`
        ALTER TABLE source_files ADD COLUMN last_read_line_no INTEGER NOT NULL DEFAULT 0;
      `).catch(() => undefined);
      await db.exec(`
        ALTER TABLE source_files ADD COLUMN last_read_mtime_ms INTEGER;
      `).catch(() => undefined);
    }
  },
  {
    version: 7,
    name: "runtime_events_extended_types_and_observations",
    up: async (db: DatabaseAdapter) => {
      await db.exec(`
        PRAGMA foreign_keys = OFF;

        CREATE TABLE runtime_events_new_m18 (
          id TEXT PRIMARY KEY,
          source TEXT NOT NULL CHECK (source IN ('codex','copilot','claude','hook_log')),
          source_file_id TEXT NOT NULL,
          session_id TEXT,
          event_type TEXT NOT NULL CHECK (event_type IN ('mcp','skill','hook','agent_tool','agent_lifecycle')),
          event_origin TEXT NOT NULL CHECK (event_origin IN ('observed_structured','hook_hint')),
          event_name TEXT NOT NULL,
          mcp_server_name TEXT,
          tool_name TEXT,
          skill_name TEXT,
          hook_name TEXT,
          hook_event TEXT,
          source_line_no INTEGER,
          event_seq INTEGER NOT NULL DEFAULT 0,
          args_keys_json TEXT,
          args_hash TEXT,
          args_bytes INTEGER DEFAULT 0,
          occurred_at TEXT NOT NULL,
          timestamp_source TEXT CHECK (timestamp_source IN ('native','session','file_mtime','fallback','unknown')),
          is_self_event INTEGER NOT NULL DEFAULT 0,
          metadata_json TEXT,
          FOREIGN KEY(source_file_id) REFERENCES source_files(id) ON DELETE CASCADE,
          FOREIGN KEY(session_id) REFERENCES sessions(id) ON DELETE CASCADE
        );
        INSERT INTO runtime_events_new_m18 SELECT * FROM runtime_events;
        DROP TABLE runtime_events;
        ALTER TABLE runtime_events_new_m18 RENAME TO runtime_events;

        CREATE TABLE IF NOT EXISTS runtime_event_observations (
          id TEXT PRIMARY KEY,
          runtime_event_id TEXT NOT NULL,
          observed_source TEXT NOT NULL CHECK (observed_source IN ('codex', 'copilot', 'claude', 'hook_log')),
          source_file_id TEXT NOT NULL,
          session_id TEXT,
          source_line_no INTEGER,
          event_seq INTEGER,
          observation_kind TEXT NOT NULL CHECK (observation_kind IN ('chat_structured', 'hook_log', 'hook_hint', 'skill_hint')),
          observed_at TEXT NOT NULL,
          FOREIGN KEY(runtime_event_id) REFERENCES runtime_events(id) ON DELETE CASCADE,
          FOREIGN KEY(source_file_id) REFERENCES source_files(id) ON DELETE CASCADE
        );
        CREATE UNIQUE INDEX IF NOT EXISTS idx_runtime_event_observations_dedup
          ON runtime_event_observations(runtime_event_id, observed_source, source_file_id, COALESCE(source_line_no, -1), COALESCE(event_seq, -1), observation_kind);

        PRAGMA foreign_keys = ON;
      `);
    }
  },
  {
    version: 8,
    name: "add_session_kind",
    up: async (db: DatabaseAdapter) => {
      await db.exec(`
        ALTER TABLE sessions ADD COLUMN session_kind TEXT CHECK (session_kind IN ('main', 'subagent', 'task', 'unknown')) DEFAULT 'unknown';
      `).catch(() => undefined);
      await db.run("UPDATE sessions SET session_kind = 'main' WHERE session_kind IS NULL");
    }
  },
  {
    version: 9,
    name: "add_reasoning_tokens_to_message_metrics",
    up: async (db: DatabaseAdapter) => {
      await db.exec(`
        ALTER TABLE message_metrics ADD COLUMN reasoning_tokens INTEGER DEFAULT 0;
      `).catch(() => undefined);
    }
  },
  {
    version: 10,
    name: "add_cursor_source",
    up: async (db: DatabaseAdapter) => {
      // SQLite cannot ALTER a CHECK constraint in place, so this follows the same
      // create-copy-drop-rename pattern already used by migrations 4 and 7, applied to the
      // 4 tables whose `source`/`observed_source` CHECK needs to allow 'cursor'.
      // message_metrics is intentionally left untouched: it has no source column.
      await db.exec(`
        PRAGMA foreign_keys = OFF;

        CREATE TABLE source_files_new_m20 (
          id TEXT PRIMARY KEY,
          source TEXT NOT NULL CHECK (source IN ('codex','copilot','claude','cursor','hook_log')),
          path_hash TEXT NOT NULL,
          file_hash TEXT,
          size_bytes INTEGER NOT NULL DEFAULT 0,
          mtime_ms INTEGER,
          first_seen_at TEXT NOT NULL,
          last_seen_at TEXT NOT NULL,
          last_imported_at TEXT,
          last_status TEXT NOT NULL CHECK (last_status IN ('pending','imported','failed')),
          last_error_code TEXT,
          last_error_severity TEXT CHECK (last_error_severity IN ('warning','error')),
          warnings_json TEXT,
          metadata_json TEXT,
          last_read_offset INTEGER NOT NULL DEFAULT 0,
          last_read_line_no INTEGER NOT NULL DEFAULT 0,
          last_read_mtime_ms INTEGER,
          UNIQUE(source, path_hash)
        );
        INSERT INTO source_files_new_m20 SELECT * FROM source_files;

        CREATE TABLE sessions_new_m20 (
          id TEXT PRIMARY KEY,
          source TEXT NOT NULL CHECK (source IN ('codex','copilot','claude','cursor')),
          source_session_hash TEXT NOT NULL,
          source_file_id TEXT NOT NULL,
          machine_id TEXT NOT NULL,
          project_id TEXT,
          mode TEXT NOT NULL DEFAULT 'unknown' CHECK (mode IN ('chat','agent','edit','unknown')),
          model_primary TEXT,
          token_available INTEGER NOT NULL DEFAULT 0,
          message_count INTEGER NOT NULL DEFAULT 0,
          user_message_count INTEGER NOT NULL DEFAULT 0,
          assistant_message_count INTEGER NOT NULL DEFAULT 0,
          created_at TEXT,
          updated_at TEXT NOT NULL,
          created_at_source TEXT CHECK (created_at_source IN ('native','updated_at','file_mtime','unknown')),
          client_surface TEXT CHECK (client_surface IN ('cli', 'desktop', 'vscode', 'vscode_insiders', 'code', 'unknown')),
          metadata_json TEXT,
          session_kind TEXT CHECK (session_kind IN ('main', 'subagent', 'task', 'unknown')) DEFAULT 'unknown',
          FOREIGN KEY(source_file_id) REFERENCES source_files(id) ON DELETE CASCADE
        );
        INSERT INTO sessions_new_m20 SELECT * FROM sessions;

        CREATE TABLE runtime_events_new_m20 (
          id TEXT PRIMARY KEY,
          source TEXT NOT NULL CHECK (source IN ('codex','copilot','claude','cursor','hook_log')),
          source_file_id TEXT NOT NULL,
          session_id TEXT,
          event_type TEXT NOT NULL CHECK (event_type IN ('mcp','skill','hook','agent_tool','agent_lifecycle')),
          event_origin TEXT NOT NULL CHECK (event_origin IN ('observed_structured','hook_hint')),
          event_name TEXT NOT NULL,
          mcp_server_name TEXT,
          tool_name TEXT,
          skill_name TEXT,
          hook_name TEXT,
          hook_event TEXT,
          source_line_no INTEGER,
          event_seq INTEGER NOT NULL DEFAULT 0,
          args_keys_json TEXT,
          args_hash TEXT,
          args_bytes INTEGER DEFAULT 0,
          occurred_at TEXT NOT NULL,
          timestamp_source TEXT CHECK (timestamp_source IN ('native','session','file_mtime','fallback','unknown')),
          is_self_event INTEGER NOT NULL DEFAULT 0,
          metadata_json TEXT,
          FOREIGN KEY(source_file_id) REFERENCES source_files(id) ON DELETE CASCADE,
          FOREIGN KEY(session_id) REFERENCES sessions(id) ON DELETE CASCADE
        );
        INSERT INTO runtime_events_new_m20 SELECT * FROM runtime_events;

        CREATE TABLE runtime_event_observations_new_m20 (
          id TEXT PRIMARY KEY,
          runtime_event_id TEXT NOT NULL,
          observed_source TEXT NOT NULL CHECK (observed_source IN ('codex', 'copilot', 'claude', 'cursor', 'hook_log')),
          source_file_id TEXT NOT NULL,
          session_id TEXT,
          source_line_no INTEGER,
          event_seq INTEGER,
          observation_kind TEXT NOT NULL CHECK (observation_kind IN ('chat_structured', 'hook_log', 'hook_hint', 'skill_hint')),
          observed_at TEXT NOT NULL,
          FOREIGN KEY(runtime_event_id) REFERENCES runtime_events(id) ON DELETE CASCADE,
          FOREIGN KEY(source_file_id) REFERENCES source_files(id) ON DELETE CASCADE
        );
        INSERT INTO runtime_event_observations_new_m20 SELECT * FROM runtime_event_observations;

        DROP TABLE runtime_event_observations;
        DROP TABLE runtime_events;
        DROP TABLE sessions;
        DROP TABLE source_files;

        ALTER TABLE source_files_new_m20 RENAME TO source_files;
        ALTER TABLE sessions_new_m20 RENAME TO sessions;
        ALTER TABLE runtime_events_new_m20 RENAME TO runtime_events;
        ALTER TABLE runtime_event_observations_new_m20 RENAME TO runtime_event_observations;

        CREATE UNIQUE INDEX IF NOT EXISTS idx_runtime_event_observations_dedup
          ON runtime_event_observations(runtime_event_id, observed_source, source_file_id, COALESCE(source_line_no, -1), COALESCE(event_seq, -1), observation_kind);

        PRAGMA foreign_keys = ON;
      `);
    }
  },
  {
    version: 11,
    name: "add_antigravity_source",
    up: async (db: DatabaseAdapter) => {
      // Same create-copy-drop-rename pattern as migration 10 (add_cursor_source), applied again to
      // the same 4 tables whose `source`/`observed_source` CHECK needs to allow 'antigravity'.
      // message_metrics is intentionally left untouched: it has no source column.
      await db.exec(`
        PRAGMA foreign_keys = OFF;

        CREATE TABLE source_files_new_m21 (
          id TEXT PRIMARY KEY,
          source TEXT NOT NULL CHECK (source IN ('codex','copilot','claude','cursor','antigravity','hook_log')),
          path_hash TEXT NOT NULL,
          file_hash TEXT,
          size_bytes INTEGER NOT NULL DEFAULT 0,
          mtime_ms INTEGER,
          first_seen_at TEXT NOT NULL,
          last_seen_at TEXT NOT NULL,
          last_imported_at TEXT,
          last_status TEXT NOT NULL CHECK (last_status IN ('pending','imported','failed')),
          last_error_code TEXT,
          last_error_severity TEXT CHECK (last_error_severity IN ('warning','error')),
          warnings_json TEXT,
          metadata_json TEXT,
          last_read_offset INTEGER NOT NULL DEFAULT 0,
          last_read_line_no INTEGER NOT NULL DEFAULT 0,
          last_read_mtime_ms INTEGER,
          UNIQUE(source, path_hash)
        );
        INSERT INTO source_files_new_m21 SELECT * FROM source_files;

        CREATE TABLE sessions_new_m21 (
          id TEXT PRIMARY KEY,
          source TEXT NOT NULL CHECK (source IN ('codex','copilot','claude','cursor','antigravity')),
          source_session_hash TEXT NOT NULL,
          source_file_id TEXT NOT NULL,
          machine_id TEXT NOT NULL,
          project_id TEXT,
          mode TEXT NOT NULL DEFAULT 'unknown' CHECK (mode IN ('chat','agent','edit','unknown')),
          model_primary TEXT,
          token_available INTEGER NOT NULL DEFAULT 0,
          message_count INTEGER NOT NULL DEFAULT 0,
          user_message_count INTEGER NOT NULL DEFAULT 0,
          assistant_message_count INTEGER NOT NULL DEFAULT 0,
          created_at TEXT,
          updated_at TEXT NOT NULL,
          created_at_source TEXT CHECK (created_at_source IN ('native','updated_at','file_mtime','unknown')),
          client_surface TEXT CHECK (client_surface IN ('cli', 'desktop', 'vscode', 'vscode_insiders', 'code', 'unknown')),
          metadata_json TEXT,
          session_kind TEXT CHECK (session_kind IN ('main', 'subagent', 'task', 'unknown')) DEFAULT 'unknown',
          FOREIGN KEY(source_file_id) REFERENCES source_files(id) ON DELETE CASCADE
        );
        INSERT INTO sessions_new_m21 SELECT * FROM sessions;

        CREATE TABLE runtime_events_new_m21 (
          id TEXT PRIMARY KEY,
          source TEXT NOT NULL CHECK (source IN ('codex','copilot','claude','cursor','antigravity','hook_log')),
          source_file_id TEXT NOT NULL,
          session_id TEXT,
          event_type TEXT NOT NULL CHECK (event_type IN ('mcp','skill','hook','agent_tool','agent_lifecycle')),
          event_origin TEXT NOT NULL CHECK (event_origin IN ('observed_structured','hook_hint')),
          event_name TEXT NOT NULL,
          mcp_server_name TEXT,
          tool_name TEXT,
          skill_name TEXT,
          hook_name TEXT,
          hook_event TEXT,
          source_line_no INTEGER,
          event_seq INTEGER NOT NULL DEFAULT 0,
          args_keys_json TEXT,
          args_hash TEXT,
          args_bytes INTEGER DEFAULT 0,
          occurred_at TEXT NOT NULL,
          timestamp_source TEXT CHECK (timestamp_source IN ('native','session','file_mtime','fallback','unknown')),
          is_self_event INTEGER NOT NULL DEFAULT 0,
          metadata_json TEXT,
          FOREIGN KEY(source_file_id) REFERENCES source_files(id) ON DELETE CASCADE,
          FOREIGN KEY(session_id) REFERENCES sessions(id) ON DELETE CASCADE
        );
        INSERT INTO runtime_events_new_m21 SELECT * FROM runtime_events;

        CREATE TABLE runtime_event_observations_new_m21 (
          id TEXT PRIMARY KEY,
          runtime_event_id TEXT NOT NULL,
          observed_source TEXT NOT NULL CHECK (observed_source IN ('codex', 'copilot', 'claude', 'cursor', 'antigravity', 'hook_log')),
          source_file_id TEXT NOT NULL,
          session_id TEXT,
          source_line_no INTEGER,
          event_seq INTEGER,
          observation_kind TEXT NOT NULL CHECK (observation_kind IN ('chat_structured', 'hook_log', 'hook_hint', 'skill_hint')),
          observed_at TEXT NOT NULL,
          FOREIGN KEY(runtime_event_id) REFERENCES runtime_events(id) ON DELETE CASCADE,
          FOREIGN KEY(source_file_id) REFERENCES source_files(id) ON DELETE CASCADE
        );
        INSERT INTO runtime_event_observations_new_m21 SELECT * FROM runtime_event_observations;

        DROP TABLE runtime_event_observations;
        DROP TABLE runtime_events;
        DROP TABLE sessions;
        DROP TABLE source_files;

        ALTER TABLE source_files_new_m21 RENAME TO source_files;
        ALTER TABLE sessions_new_m21 RENAME TO sessions;
        ALTER TABLE runtime_events_new_m21 RENAME TO runtime_events;
        ALTER TABLE runtime_event_observations_new_m21 RENAME TO runtime_event_observations;

        CREATE UNIQUE INDEX IF NOT EXISTS idx_runtime_event_observations_dedup
          ON runtime_event_observations(runtime_event_id, observed_source, source_file_id, COALESCE(source_line_no, -1), COALESCE(event_seq, -1), observation_kind);

        PRAGMA foreign_keys = ON;
      `);
    }
  },
  {
    version: 12,
    name: "cleanup_empty_codex_copilot_session_shells",
    up: async (db: DatabaseAdapter) => {
      // One-time data cleanup (no schema change): older codex.ts/vscode-copilot.ts builds
      // inserted a "session" row for every session_meta/resume/reconnect group even when that
      // group had zero messages, zero mcp_calls, and zero lifecycle_events -- a completely empty
      // shell with no usage data (confirmed on real data: 61% of codex sessions and 81% of
      // copilot sessions were these empty shells). The adapters no longer create them going
      // forward; this migration removes the ones already sitting in existing databases.
      //
      // message_count is only an additional guard: child-table absence is authoritative.
      await db.run(`
        DELETE FROM sessions
        WHERE source IN ('codex', 'copilot')
          AND message_count = 0
          AND NOT EXISTS (
            SELECT 1 FROM message_metrics WHERE message_metrics.session_id = sessions.id
          )
          AND NOT EXISTS (
            SELECT 1 FROM runtime_events WHERE runtime_events.session_id = sessions.id
          )
      `);
    }
  },
  {
    version: 13,
    name: "add_query_indexes",
    up: async (db: DatabaseAdapter) => {
      // No indexes exist beyond the runtime_event_observations dedup constraint. Every read tool
      // (summary/models/sessions/events) filters sessions/runtime_events by source and a date
      // column, and every cascading delete/lookup joins on session_id -- all currently full table
      // scans. The dataset only grows over time (thousands of sessions, tens of thousands of
      // message_metrics rows already on a single real machine), so this is pure, non-destructive
      // performance hardening: no behavior change, just faster queries as data accumulates.
      await db.exec(`
        CREATE INDEX IF NOT EXISTS idx_sessions_source ON sessions(source);
        CREATE INDEX IF NOT EXISTS idx_sessions_updated_at ON sessions(updated_at);
        CREATE INDEX IF NOT EXISTS idx_sessions_source_file_id ON sessions(source_file_id);
        CREATE INDEX IF NOT EXISTS idx_message_metrics_session_id ON message_metrics(session_id);
        CREATE INDEX IF NOT EXISTS idx_runtime_events_session_id ON runtime_events(session_id);
        CREATE INDEX IF NOT EXISTS idx_runtime_events_source ON runtime_events(source);
        CREATE INDEX IF NOT EXISTS idx_runtime_events_occurred_at ON runtime_events(occurred_at);
        CREATE INDEX IF NOT EXISTS idx_runtime_events_source_file_id ON runtime_events(source_file_id);
        CREATE INDEX IF NOT EXISTS idx_runtime_event_observations_runtime_event_id ON runtime_event_observations(runtime_event_id);
        CREATE INDEX IF NOT EXISTS idx_source_files_source ON source_files(source);
      `);
    }
  },
  {
    version: 14,
    name: "remove_empty_claude_cursor_sessions",
    up: async (db: DatabaseAdapter) => {
      // Remove only shell sessions that have neither message metrics nor runtime events.
      // Event-only sessions are intentionally preserved.
      await db.run(`
        DELETE FROM sessions
        WHERE source IN ('claude', 'cursor')
          AND message_count = 0
          AND NOT EXISTS (
            SELECT 1 FROM message_metrics WHERE message_metrics.session_id = sessions.id
          )
          AND NOT EXISTS (
            SELECT 1 FROM runtime_events WHERE runtime_events.session_id = sessions.id
          )
      `);
    }
  },
  {
    version: 15,
    name: "add_codex_session_index_lookup_index",
    up: async (db: DatabaseAdapter) => {
      // The Codex session-index enrichment looks up every HMACed session id by both source and
      // source_session_hash. This remains deliberately non-unique: historical data may contain
      // multiple matching session rows and the synchronizer updates every matching row.
      await db.exec(`
        CREATE INDEX IF NOT EXISTS idx_sessions_source_source_session_hash
        ON sessions(source, source_session_hash);
      `);
    }
  },
  {
    version: 16,
    name: "add_hotword_and_hook_outcome_contract",
    up: async (db: DatabaseAdapter) => {
      // SQLite cannot extend the event_type CHECK in place. The new nullable dimensions keep
      // pre-Milestone-1 rows valid without deriving metadata that was not recorded originally.
      await db.exec(`
        PRAGMA foreign_keys = OFF;

        CREATE TABLE runtime_events_new_m22 (
          id TEXT PRIMARY KEY,
          source TEXT NOT NULL CHECK (source IN ('codex','copilot','claude','cursor','antigravity','hook_log')),
          source_file_id TEXT NOT NULL,
          session_id TEXT,
          event_type TEXT NOT NULL CHECK (event_type IN ('mcp','skill','hook','agent_tool','agent_lifecycle','hotword')),
          event_origin TEXT NOT NULL CHECK (event_origin IN ('observed_structured','hook_hint')),
          event_name TEXT NOT NULL,
          mcp_server_name TEXT,
          tool_name TEXT,
          skill_name TEXT,
          hook_name TEXT,
          hook_event TEXT,
          hook_phase TEXT CHECK (hook_phase IN ('lifecycle','prompt','pretool')),
          suggestion_outcome TEXT CHECK (suggestion_outcome IN ('none','mcp','skill','mcp_and_skill')),
          mcp_suggestion_count INTEGER NOT NULL DEFAULT 0 CHECK (mcp_suggestion_count >= 0),
          skill_suggestion_count INTEGER NOT NULL DEFAULT 0 CHECK (skill_suggestion_count >= 0),
          hotword_mode TEXT CHECK (hotword_mode IN ('quick','agents','quick_agents')),
          context_mode TEXT CHECK (context_mode IN ('light','full')),
          subagent_mode TEXT CHECK (subagent_mode IN ('off','requested','required')),
          quick_overridden INTEGER CHECK (quick_overridden IN (0,1)),
          source_line_no INTEGER,
          event_seq INTEGER NOT NULL DEFAULT 0,
          args_keys_json TEXT,
          args_hash TEXT,
          args_bytes INTEGER DEFAULT 0,
          occurred_at TEXT NOT NULL,
          timestamp_source TEXT CHECK (timestamp_source IN ('native','session','file_mtime','fallback','unknown')),
          is_self_event INTEGER NOT NULL DEFAULT 0,
          metadata_json TEXT,
          FOREIGN KEY(source_file_id) REFERENCES source_files(id) ON DELETE CASCADE,
          FOREIGN KEY(session_id) REFERENCES sessions(id) ON DELETE CASCADE
        );
        INSERT INTO runtime_events_new_m22(
          id, source, source_file_id, session_id, event_type, event_origin, event_name,
          mcp_server_name, tool_name, skill_name, hook_name, hook_event, source_line_no,
          event_seq, args_keys_json, args_hash, args_bytes, occurred_at, timestamp_source,
          is_self_event, metadata_json
        ) SELECT
          id, source, source_file_id, session_id, event_type, event_origin, event_name,
          mcp_server_name, tool_name, skill_name, hook_name, hook_event, source_line_no,
          event_seq, args_keys_json, args_hash, args_bytes, occurred_at, timestamp_source,
          is_self_event, metadata_json
        FROM runtime_events;
        DROP TABLE runtime_events;
        ALTER TABLE runtime_events_new_m22 RENAME TO runtime_events;

        CREATE INDEX IF NOT EXISTS idx_runtime_events_session_id ON runtime_events(session_id);
        CREATE INDEX IF NOT EXISTS idx_runtime_events_source ON runtime_events(source);
        CREATE INDEX IF NOT EXISTS idx_runtime_events_occurred_at ON runtime_events(occurred_at);
        CREATE INDEX IF NOT EXISTS idx_runtime_events_source_file_id ON runtime_events(source_file_id);
        CREATE INDEX IF NOT EXISTS idx_runtime_events_event_type_occurred_at ON runtime_events(event_type, occurred_at);
        CREATE INDEX IF NOT EXISTS idx_runtime_events_hook_name_occurred_at ON runtime_events(hook_name, occurred_at);
        CREATE INDEX IF NOT EXISTS idx_runtime_events_hotword_mode_occurred_at ON runtime_events(hotword_mode, occurred_at);

        PRAGMA foreign_keys = ON;
      `);
    }
  }
];

const STRUCTURAL_MIGRATIONS = new Set([4, 7, 10, 11, 16]);

type StructuralAction = "run" | "register" | "promote";
interface StructuralTableSpec {
  final: string;
  temporary: string | null;
  columns: string[];
  comparableColumns: string[];
  comparisonProjection?: { final: string[]; temporary: string[] };
  marker?: string;
  recreatable?: boolean;
}
interface StructuralSpec { version: number; tables: StructuralTableSpec[]; indexes: string[]; requiredOriginals: string[]; }

const COMMON_SOURCE_FILE_COLUMNS = ["id", "source", "path_hash", "file_hash", "size_bytes", "mtime_ms", "first_seen_at", "last_seen_at", "last_imported_at", "last_status", "last_error_code", "last_error_severity", "warnings_json", "metadata_json"];
const SESSION_COLUMNS = ["id", "source", "source_session_hash", "source_file_id", "machine_id", "project_id", "mode", "model_primary", "token_available", "message_count", "user_message_count", "assistant_message_count", "created_at", "updated_at", "created_at_source", "client_surface", "metadata_json"];
const MESSAGE_COLUMNS = ["id", "session_id", "source_file_id", "seq", "role", "model", "input_tokens", "output_tokens", "cache_read_tokens", "cache_write_tokens", "token_available", "partial_token_data", "created_at", "timestamp_source", "metadata_json"];
const EVENT_COLUMNS = ["id", "source", "source_file_id", "session_id", "event_type", "event_origin", "event_name", "mcp_server_name", "tool_name", "skill_name", "hook_name", "hook_event", "source_line_no", "event_seq", "args_keys_json", "args_hash", "args_bytes", "occurred_at", "timestamp_source", "is_self_event", "metadata_json"];
const EXTENDED_EVENT_COLUMNS = EVENT_COLUMNS;
const HOTWORD_HOOK_EVENT_COLUMNS = ["id", "source", "source_file_id", "session_id", "event_type", "event_origin", "event_name", "mcp_server_name", "tool_name", "skill_name", "hook_name", "hook_event", "hook_phase", "suggestion_outcome", "mcp_suggestion_count", "skill_suggestion_count", "hotword_mode", "context_mode", "subagent_mode", "quick_overridden", "source_line_no", "event_seq", "args_keys_json", "args_hash", "args_bytes", "occurred_at", "timestamp_source", "is_self_event", "metadata_json"];
const OBSERVATION_COLUMNS = ["id", "runtime_event_id", "observed_source", "source_file_id", "session_id", "source_line_no", "event_seq", "observation_kind", "observed_at"];
const SOURCE_OFFSET_COLUMNS = ["last_read_offset", "last_read_line_no", "last_read_mtime_ms"];

function structuralSpec(version: number): StructuralSpec {
  const suffix = version === 4 ? "m8" : version === 7 ? "m18" : version === 10 ? "m20" : version === 11 ? "m21" : "m22";
  const sourceMarker = version === 11 ? "antigravity" : version === 10 ? "cursor" : undefined;
  const eventColumns = version >= 16 ? HOTWORD_HOOK_EVENT_COLUMNS : version === 7 || version >= 10 ? EXTENDED_EVENT_COLUMNS : EVENT_COLUMNS;
  const tables: StructuralTableSpec[] = version === 7
    ? [
        { final: "runtime_events", temporary: "runtime_events_new_m18", columns: eventColumns, comparableColumns: eventColumns, marker: "agent_tool" },
        { final: "runtime_event_observations", temporary: null, columns: OBSERVATION_COLUMNS, comparableColumns: OBSERVATION_COLUMNS, recreatable: true }
      ]
    : version === 16
    ? [
        // Version 16 is a cumulative successor. Only runtime_events is rebuilt by its own
        // migration, while the other tables are verification-only invariants inherited from v11.
        { final: "source_files", temporary: null, columns: [...COMMON_SOURCE_FILE_COLUMNS, ...SOURCE_OFFSET_COLUMNS], comparableColumns: [...COMMON_SOURCE_FILE_COLUMNS, ...SOURCE_OFFSET_COLUMNS], marker: "antigravity" },
        { final: "sessions", temporary: null, columns: [...SESSION_COLUMNS, "session_kind"], comparableColumns: [...SESSION_COLUMNS, "session_kind"], marker: "antigravity" },
        { final: "runtime_events", temporary: "runtime_events_new_m22", columns: eventColumns, comparableColumns: eventColumns },
        { final: "runtime_event_observations", temporary: null, columns: OBSERVATION_COLUMNS, comparableColumns: OBSERVATION_COLUMNS, marker: "antigravity" }
      ]
    : version === 4
    ? [
        { final: "source_files", temporary: "source_files_new_m8", columns: COMMON_SOURCE_FILE_COLUMNS, comparableColumns: COMMON_SOURCE_FILE_COLUMNS },
        {
          final: "sessions",
          temporary: "sessions_new_m8",
          columns: SESSION_COLUMNS,
          comparableColumns: SESSION_COLUMNS,
          comparisonProjection: {
            final: SESSION_COLUMNS.map((column) => column === "client_surface" ? "'unknown' AS client_surface" : column),
            temporary: SESSION_COLUMNS
          }
        },
        { final: "message_metrics", temporary: "message_metrics_new_m8", columns: MESSAGE_COLUMNS, comparableColumns: MESSAGE_COLUMNS },
        { final: "runtime_events", temporary: "runtime_events_new_m8", columns: eventColumns, comparableColumns: eventColumns }
      ]
    : [
        { final: "source_files", temporary: `source_files_new_${suffix}`, columns: [...COMMON_SOURCE_FILE_COLUMNS, ...SOURCE_OFFSET_COLUMNS], comparableColumns: [...COMMON_SOURCE_FILE_COLUMNS, ...SOURCE_OFFSET_COLUMNS], marker: sourceMarker },
        { final: "sessions", temporary: `sessions_new_${suffix}`, columns: [...SESSION_COLUMNS, "session_kind"], comparableColumns: [...SESSION_COLUMNS, "session_kind"], marker: sourceMarker },
        { final: "runtime_events", temporary: `runtime_events_new_${suffix}`, columns: eventColumns, comparableColumns: eventColumns, marker: sourceMarker },
        ...(version >= 10 ? [{ final: "runtime_event_observations", temporary: `runtime_event_observations_new_${suffix}`, columns: OBSERVATION_COLUMNS, comparableColumns: OBSERVATION_COLUMNS, marker: sourceMarker }] : [])
      ];
  const requiredOriginals = version === 7 || version === 10 ? ["source_files", "sessions", "message_metrics", "runtime_events"] : [...tables.map((table) => table.final), ...(version >= 10 ? ["message_metrics"] : [])];
  const indexes = version === 16
    ? ["idx_runtime_events_session_id", "idx_runtime_events_source", "idx_runtime_events_occurred_at", "idx_runtime_events_source_file_id", "idx_runtime_events_event_type_occurred_at", "idx_runtime_events_hook_name_occurred_at", "idx_runtime_events_hotword_mode_occurred_at"]
    : version >= 7 ? ["idx_runtime_event_observations_dedup"] : [];
  return { version, tables, indexes, requiredOriginals };
}

async function tableInfo(db: DatabaseAdapter, name: string): Promise<{ name: string }[]> {
  return db.all<{ name: string }>(`PRAGMA table_info(${name})`);
}

async function tableSql(db: DatabaseAdapter, name: string): Promise<string> {
  const row = await db.get<{ sql: string | null }>("SELECT sql FROM sqlite_master WHERE type='table' AND name = ?", [name]);
  return row?.sql ?? "";
}

function compactSql(sql: string): string {
  return sql.toLowerCase().replace(/["`\[\]]/gu, "").replace(/\s+/gu, "");
}

function expectedSourceValues(version: number, sessions: boolean): string {
  if (version >= 11) return sessions ? "'codex','copilot','claude','cursor','antigravity'" : "'codex','copilot','claude','cursor','antigravity','hook_log'";
  if (version === 10) return sessions ? "'codex','copilot','claude','cursor'" : "'codex','copilot','claude','cursor','hook_log'";
  return sessions ? "'codex','copilot','claude'" : "'codex','copilot','claude','hook_log'";
}

function expectedStructuralSqlFragments(version: number, tableName: string): string[] {
  const fragments: string[] = [];
  if (tableName === "source_files" || tableName === "sessions" || tableName === "runtime_events") {
    fragments.push(`check(sourcein(${expectedSourceValues(version, tableName === "sessions")}))`);
  }
  if (tableName === "source_files") fragments.push("check(last_error_severityin('warning','error'))");
  if (tableName === "sessions") {
    fragments.push("check(modein('chat','agent','edit','unknown'))");
    fragments.push("check(created_at_sourcein('native','updated_at','file_mtime','unknown'))");
    fragments.push("check(client_surfacein('cli','desktop','vscode','vscode_insiders','code','unknown'))");
    if (version >= 10) fragments.push("check(session_kindin('main','subagent','task','unknown'))");
  }
  if (tableName === "message_metrics") {
    fragments.push("check(rolein('user','assistant','unknown'))");
    fragments.push("check(timestamp_sourcein('native','session','file_mtime','fallback','unknown'))");
  }
  if (tableName === "runtime_events") {
    fragments.push(`check(event_typein(${version >= 16 ? "'mcp','skill','hook','agent_tool','agent_lifecycle','hotword'" : version >= 7 ? "'mcp','skill','hook','agent_tool','agent_lifecycle'" : "'mcp','skill','hook'"}))`);
    fragments.push("check(event_originin('observed_structured','hook_hint'))");
    fragments.push("check(timestamp_sourcein('native','session','file_mtime','fallback','unknown'))");
    if (version >= 16) {
      fragments.push("check(hook_phasein('lifecycle','prompt','pretool'))");
      fragments.push("check(suggestion_outcomein('none','mcp','skill','mcp_and_skill'))");
      fragments.push("check(mcp_suggestion_count>=0)");
      fragments.push("check(skill_suggestion_count>=0)");
      fragments.push("check(hotword_modein('quick','agents','quick_agents'))");
      fragments.push("check(context_modein('light','full'))");
      fragments.push("check(subagent_modein('off','requested','required'))");
      fragments.push("check(quick_overriddenin(0,1))");
    }
  }
  if (tableName === "runtime_event_observations") {
    fragments.push(`check(observed_sourcein(${expectedSourceValues(version, false)}))`);
    fragments.push("check(observation_kindin('chat_structured','hook_log','hook_hint','skill_hint'))");
  }
  if (tableName === "source_files") fragments.push("unique(source,path_hash)");
  if (tableName === "sessions") fragments.push("foreignkey(source_file_id)referencessource_files(id)ondeletecascade");
  if (tableName === "message_metrics") {
    fragments.push("foreignkey(session_id)referencessessions(id)ondeletecascade");
    fragments.push("foreignkey(source_file_id)referencessource_files(id)ondeletecascade");
    fragments.push("unique(session_id,seq)");
  }
  if (tableName === "runtime_events") {
    fragments.push("foreignkey(source_file_id)referencessource_files(id)ondeletecascade");
    fragments.push("foreignkey(session_id)referencessessions(id)ondeletecascade");
  }
  if (tableName === "runtime_event_observations") {
    fragments.push("foreignkey(runtime_event_id)referencesruntime_events(id)ondeletecascade");
    fragments.push("foreignkey(source_file_id)referencessource_files(id)ondeletecascade");
  }
  return fragments;
}

async function indexExists(db: DatabaseAdapter, name: string): Promise<boolean> {
  const row = await db.get<{ name: string }>("SELECT name FROM sqlite_master WHERE type='index' AND name = ?", [name]);
  return !!row;
}

async function tableMatches(db: DatabaseAdapter, table: StructuralTableSpec, version: number): Promise<boolean> {
  const columns = await tableInfo(db, table.final);
  if (!table.columns.every((column) => columns.some((entry) => entry.name === column))) return false;
  const compact = compactSql(await tableSql(db, table.final));
  if (!expectedStructuralSqlFragments(version, table.final).every((fragment) => compact.includes(fragment))) return false;
  if (table.marker && !compact.includes(`'${table.marker}'`)) return false;
  return true;
}

async function temporaryMatches(db: DatabaseAdapter, table: StructuralTableSpec, version: number): Promise<boolean> {
  if (!table.temporary) return false;
  const columns = await db.all<{ name: string }>(`PRAGMA table_info(${table.temporary})`);
  if (!table.columns.every((column) => columns.some((entry) => entry.name === column))) return false;
  const compact = compactSql(await tableSql(db, table.temporary));
  const fragments = expectedStructuralSqlFragments(version, table.final);
  if (version === 4) {
    const historicalTargets: Record<string, string> = {
      source_files: "source_files_new_m8",
      sessions: "sessions_new_m8"
    };
    for (const [finalTarget, temporaryTarget] of Object.entries(historicalTargets)) {
      const temporaryTargetExists = (await tableInfo(db, temporaryTarget)).length > 0;
      const expectedTarget = temporaryTargetExists ? temporaryTarget : finalTarget;
      for (let index = 0; index < fragments.length; index += 1) {
        fragments[index] = fragments[index].replace(`references${finalTarget}(id)`, `references${expectedTarget}(id)`);
      }
    }
  }
  if (!fragments.every((fragment) => compact.includes(fragment))) return false;
  if (table.marker && !compact.includes(`'${table.marker}'`)) return false;
  return true;
}

async function projectionDifferenceCount(
  db: DatabaseAdapter,
  leftTable: string,
  rightTable: string,
  leftProjection: string[],
  rightProjection: string[]
): Promise<number> {
  const row = await db.get<{ n: number }>(
    `SELECT COUNT(*) AS n FROM (
       SELECT ${leftProjection.join(", ")} FROM ${leftTable}
       EXCEPT
       SELECT ${rightProjection.join(", ")} FROM ${rightTable}
     )`
  );
  return row?.n ?? 0;
}

interface ComparisonProjection {
  final: string[];
  temporary: string[];
  requireM22Defaults: boolean;
}

const M22_ONLY_EVENT_COLUMNS = ["hook_phase", "suggestion_outcome", "mcp_suggestion_count", "skill_suggestion_count", "hotword_mode", "context_mode", "subagent_mode", "quick_overridden"];

function hasColumns(columns: readonly { name: string }[], required: readonly string[]): boolean {
  return required.every((column) => columns.some((entry) => entry.name === column));
}

async function comparisonProjection(db: DatabaseAdapter, table: StructuralTableSpec): Promise<ComparisonProjection | null> {
  if (table.final !== "runtime_events" || table.temporary !== "runtime_events_new_m22") {
    const projection = table.comparisonProjection ?? { final: table.comparableColumns, temporary: table.comparableColumns };
    return { ...projection, requireM22Defaults: false };
  }

  const [finalColumns, temporaryColumns] = await Promise.all([tableInfo(db, table.final), tableInfo(db, table.temporary)]);
  if (!hasColumns(temporaryColumns, HOTWORD_HOOK_EVENT_COLUMNS) || !hasColumns(finalColumns, EVENT_COLUMNS)) return null;
  if (hasColumns(finalColumns, HOTWORD_HOOK_EVENT_COLUMNS)) {
    return { final: HOTWORD_HOOK_EVENT_COLUMNS, temporary: HOTWORD_HOOK_EVENT_COLUMNS, requireM22Defaults: false };
  }
  return { final: EVENT_COLUMNS, temporary: EVENT_COLUMNS, requireM22Defaults: true };
}

async function m22OnlyColumnsAreDefaults(db: DatabaseAdapter, table: StructuralTableSpec): Promise<boolean> {
  if (!table.temporary) return false;
  const row = await db.get<{ n: number }>(
    `SELECT COUNT(*) AS n FROM ${table.temporary}
     WHERE hook_phase IS NOT NULL
        OR suggestion_outcome IS NOT NULL
        OR mcp_suggestion_count <> 0
        OR skill_suggestion_count <> 0
        OR hotword_mode IS NOT NULL
        OR context_mode IS NOT NULL
        OR subagent_mode IS NOT NULL
        OR quick_overridden IS NOT NULL`
  );
  return (row?.n ?? 0) === 0;
}

async function temporaryIsSafeToDiscard(db: DatabaseAdapter, table: StructuralTableSpec): Promise<boolean> {
  if (!table.temporary || (await tableInfo(db, table.final)).length === 0 || (await tableInfo(db, table.temporary)).length === 0) return false;
  const projection = await comparisonProjection(db, table);
  if (!projection || (projection.requireM22Defaults && !(await m22OnlyColumnsAreDefaults(db, table)))) return false;
  return (await projectionDifferenceCount(db, table.temporary, table.final, projection.temporary, projection.final)) === 0;
}

async function finalAndTemporaryAreEquivalent(db: DatabaseAdapter, table: StructuralTableSpec): Promise<boolean> {
  if (!table.temporary || (await tableInfo(db, table.final)).length === 0 || (await tableInfo(db, table.temporary)).length === 0) return true;
  const projection = await comparisonProjection(db, table);
  if (!projection || (projection.requireM22Defaults && !(await m22OnlyColumnsAreDefaults(db, table)))) return false;
  const temporaryOnly = await projectionDifferenceCount(db, table.temporary, table.final, projection.temporary, projection.final);
  if (temporaryOnly !== 0) return false;
  const finalOnly = await projectionDifferenceCount(db, table.final, table.temporary, projection.final, projection.temporary);
  return finalOnly === 0;
}

async function assertCleanupDataIsSafe(
  db: DatabaseAdapter,
  spec: StructuralSpec,
  temporaryExists: boolean[]
): Promise<void> {
  const safe = await Promise.all(spec.tables.map((table, index) => !temporaryExists[index] || temporaryIsSafeToDiscard(db, table)));
  if (!safe.every(Boolean)) {
    throw new Error(`STRUCTURAL_MIGRATION_AMBIGUOUS:migration_${spec.version}:temporary data is not a historical subset of the final table`);
  }
}

async function assertPromotionDataIsEquivalent(db: DatabaseAdapter, spec: StructuralSpec): Promise<void> {
  const equivalent = await Promise.all(spec.tables.map((table) => finalAndTemporaryAreEquivalent(db, table)));
  if (!equivalent.every(Boolean)) {
    throw new Error(`STRUCTURAL_MIGRATION_AMBIGUOUS:migration_${spec.version}:coexisting final and temporary data differ`);
  }
}

async function messageMetricsPrerequisiteMatches(db: DatabaseAdapter, version: number): Promise<boolean> {
  if (version < 10) return true;
  const columns = await tableInfo(db, "message_metrics");
  const required = [...MESSAGE_COLUMNS, "reasoning_tokens"];
  if (!required.every((column) => columns.some((entry) => entry.name === column))) return false;
  const compact = compactSql(await tableSql(db, "message_metrics"));
  return compact.includes("foreignkey(session_id)referencessessions(id)ondeletecascade")
    && compact.includes("foreignkey(source_file_id)referencessource_files(id)ondeletecascade")
    && compact.includes("unique(session_id,seq)");
}

async function schemaExactlyMatches(db: DatabaseAdapter, version: number): Promise<boolean> {
  const candidate = structuralSpec(version);
  const tablesMatch = await Promise.all(candidate.tables.map((table) => tableMatches(db, table, version)));
  if (!tablesMatch.every(Boolean) || !(await messageMetricsPrerequisiteMatches(db, version))) return false;
  const indexesMatch = await Promise.all(candidate.indexes.map((index) => indexExists(db, index)));
  return indexesMatch.every(Boolean);
}

async function validatedSuccessorVersion(db: DatabaseAdapter, version: number): Promise<number | null> {
  for (const candidate of [16, 11, 10, 7, 4]) {
    if (candidate > version && await schemaExactlyMatches(db, candidate)) return candidate;
  }
  return null;
}

async function hasIncompleteNewerStructuralState(db: DatabaseAdapter, version: number): Promise<boolean> {
  const currentSpec = structuralSpec(version);
  const currentMatches = new Map(await Promise.all(currentSpec.tables.map(async (table) => [table.final, await tableMatches(db, table, version)] as const)));
  for (const candidate of [16, 11, 10, 7, 4]) {
    if (candidate <= version || await schemaExactlyMatches(db, candidate)) continue;
    const candidateSpec = structuralSpec(candidate);
    const tableMatchesNewerOnly = await Promise.all(candidateSpec.tables.map(async (table) =>
      !(currentMatches.get(table.final) ?? false) && await tableMatches(db, table, candidate)
    ));
    if (tableMatchesNewerOnly.some(Boolean)) return true;
  }
  return false;
}

async function inspectStructuralState(db: DatabaseAdapter, spec: StructuralSpec): Promise<{ action: StructuralAction; cleanup: string[]; promote: StructuralTableSpec[] }> {
  if (spec.version >= 10 && (await tableInfo(db, `message_metrics_new_m${spec.version === 10 ? "20" : "21"}`)).length > 0) {
    throw new Error(`STRUCTURAL_MIGRATION_AMBIGUOUS:migration_${spec.version}:message_metrics temporary table is not part of the historical migration`);
  }
  if (!(await messageMetricsPrerequisiteMatches(db, spec.version))) {
    throw new Error(`STRUCTURAL_MIGRATION_AMBIGUOUS:migration_${spec.version}:message_metrics prerequisite is missing or has incompatible constraints`);
  }
  const finalExists = await Promise.all(spec.tables.map(async (table) => (await tableInfo(db, table.final)).length > 0));
  const finalMatches = await Promise.all(spec.tables.map((table) => tableMatches(db, table, spec.version)));
  const temporaryExists = await Promise.all(spec.tables.map(async (table) => table.temporary ? (await tableInfo(db, table.temporary)).length > 0 : false));
  const temporaryMatchesState = await Promise.all(spec.tables.map((table) => temporaryMatches(db, table, spec.version)));
  const allFinal = finalMatches.every(Boolean);
  const anyTemporary = temporaryExists.some(Boolean);

  if (temporaryExists.some((exists, index) => exists && !temporaryMatchesState[index])) {
    throw new Error(`STRUCTURAL_MIGRATION_AMBIGUOUS:migration_${spec.version}:temporary table schema or constraints are incompatible`);
  }

  const successor = await validatedSuccessorVersion(db, spec.version);
  if (successor !== null) {
    await assertCleanupDataIsSafe(db, spec, temporaryExists);
    return { action: "register", cleanup: spec.tables.filter((_, i) => temporaryExists[i]).map((table) => table.temporary!).filter(Boolean), promote: [] };
  }

  if (await hasIncompleteNewerStructuralState(db, spec.version)) {
    throw new Error(`STRUCTURAL_MIGRATION_AMBIGUOUS:migration_${spec.version}:newer structural state is incomplete`);
  }

  if (allFinal && spec.version === 4 && anyTemporary) {
    await assertCleanupDataIsSafe(db, spec, temporaryExists);
    return { action: "run", cleanup: spec.tables.filter((_, i) => temporaryExists[i]).map((table) => table.temporary!).filter(Boolean), promote: [] };
  }

  if (allFinal) {
    await assertCleanupDataIsSafe(db, spec, temporaryExists);
    return { action: "register", cleanup: spec.tables.filter((_, i) => temporaryExists[i]).map((table) => table.temporary!).filter(Boolean), promote: [] };
  }

  // Migration 4 changed no durable column/constraint marker that can distinguish its final
  // schema from the legacy schema. Existing complete originals are therefore safe to register;
  // rerunning its copy operation would be needlessly destructive.
  if (spec.version === 4 && !anyTemporary && finalMatches.every(Boolean)) {
    return { action: "register", cleanup: [], promote: [] };
  }

  if (!anyTemporary) {
    const requiredOriginalsExist = await Promise.all(spec.requiredOriginals.map(async (name) => (await tableInfo(db, name)).length > 0));
    if (!requiredOriginalsExist.every(Boolean)) throw new Error(`STRUCTURAL_MIGRATION_AMBIGUOUS:migration_${spec.version}:required originals are missing and no recoverable temporary tables exist`);
    return { action: "run", cleanup: [], promote: [] };
  }

  // If every original table still exists, temporary tables are regenerable copies. They may be
  // partial, so they are removed only after this state has been proven safe.
  const originalNamesExist = await Promise.all(spec.requiredOriginals.map(async (name) => (await tableInfo(db, name)).length > 0));
  if (originalNamesExist.every(Boolean)) {
    await assertCleanupDataIsSafe(db, spec, temporaryExists);
    return { action: "run", cleanup: spec.tables.filter((_, i) => temporaryExists[i]).map((table) => table.temporary!).filter(Boolean), promote: [] };
  }

  // A legacy interruption may have dropped some originals, or may have renamed some tables
  // already. Promote only tables whose replacement is complete; never discard an unmatched table.
  const allHistoricalReplacementsExist = spec.tables.every((table, index) => table.recreatable || temporaryMatchesState[index]);
  if (finalExists.some((exists) => !exists) && allHistoricalReplacementsExist) {
    await assertPromotionDataIsEquivalent(db, spec);
    return { action: "promote", cleanup: [], promote: spec.tables.filter((table) => table.temporary !== null) };
  }
  if (finalExists.some((exists, index) => exists && !finalMatches[index])) {
    throw new Error(`STRUCTURAL_MIGRATION_AMBIGUOUS:migration_${spec.version}:an existing final table has incompatible schema or constraints`);
  }
  const promotable = spec.tables.filter((table, i) => !finalExists[i] && temporaryMatchesState[i]);
  const unresolved = spec.tables.filter((table, i) => !finalExists[i] && !temporaryMatchesState[i] && !table.recreatable);
  if (unresolved.length === 0 && promotable.length > 0) {
    await assertPromotionDataIsEquivalent(db, spec);
    return { action: "promote", cleanup: [], promote: promotable };
  }

  throw new Error(`STRUCTURAL_MIGRATION_AMBIGUOUS:migration_${spec.version}:original and temporary tables do not form a recoverable state`);
}

async function ensureStructuralIndexes(db: DatabaseAdapter, spec: StructuralSpec): Promise<void> {
  const indexDdl: Readonly<Record<string, string>> = {
    idx_runtime_event_observations_dedup: "CREATE UNIQUE INDEX IF NOT EXISTS idx_runtime_event_observations_dedup ON runtime_event_observations(runtime_event_id, observed_source, source_file_id, COALESCE(source_line_no, -1), COALESCE(event_seq, -1), observation_kind)",
    idx_runtime_events_session_id: "CREATE INDEX IF NOT EXISTS idx_runtime_events_session_id ON runtime_events(session_id)",
    idx_runtime_events_source: "CREATE INDEX IF NOT EXISTS idx_runtime_events_source ON runtime_events(source)",
    idx_runtime_events_occurred_at: "CREATE INDEX IF NOT EXISTS idx_runtime_events_occurred_at ON runtime_events(occurred_at)",
    idx_runtime_events_source_file_id: "CREATE INDEX IF NOT EXISTS idx_runtime_events_source_file_id ON runtime_events(source_file_id)",
    idx_runtime_events_event_type_occurred_at: "CREATE INDEX IF NOT EXISTS idx_runtime_events_event_type_occurred_at ON runtime_events(event_type, occurred_at)",
    idx_runtime_events_hook_name_occurred_at: "CREATE INDEX IF NOT EXISTS idx_runtime_events_hook_name_occurred_at ON runtime_events(hook_name, occurred_at)",
    idx_runtime_events_hotword_mode_occurred_at: "CREATE INDEX IF NOT EXISTS idx_runtime_events_hotword_mode_occurred_at ON runtime_events(hotword_mode, occurred_at)"
  };
  for (const index of spec.indexes) {
    const ddl = indexDdl[index];
    if (!ddl) throw new Error(`STRUCTURAL_MIGRATION_INDEX_UNKNOWN:${spec.version}:${index}`);
    if (!(await indexExists(db, index))) await db.exec(ddl);
  }
}

async function recreateStructuralTables(db: DatabaseAdapter, spec: StructuralSpec): Promise<void> {
  if (spec.version !== 7) return;
  const observations = spec.tables.find((table) => table.final === "runtime_event_observations");
  if (!observations || (await tableInfo(db, observations.final)).length > 0) return;
  await db.exec(`
    CREATE TABLE runtime_event_observations (
      id TEXT PRIMARY KEY,
      runtime_event_id TEXT NOT NULL,
      observed_source TEXT NOT NULL CHECK (observed_source IN ('codex', 'copilot', 'claude', 'hook_log')),
      source_file_id TEXT NOT NULL,
      session_id TEXT,
      source_line_no INTEGER,
      event_seq INTEGER,
      observation_kind TEXT NOT NULL CHECK (observation_kind IN ('chat_structured', 'hook_log', 'hook_hint', 'skill_hint')),
      observed_at TEXT NOT NULL,
      FOREIGN KEY(runtime_event_id) REFERENCES runtime_events(id) ON DELETE CASCADE,
      FOREIGN KEY(source_file_id) REFERENCES source_files(id) ON DELETE CASCADE
    );
  `);
}

async function foreignKeyViolations(db: DatabaseAdapter): Promise<unknown[]> {
  return db.all("PRAGMA foreign_key_check");
}

async function ensureRequiredSettings(db: DatabaseAdapter): Promise<void> {
  let transactionOpen = false;
  try {
    await db.exec("BEGIN IMMEDIATE TRANSACTION");
    transactionOpen = true;
    const now = nowIso();
    const machine = await db.get<{ value: string }>("SELECT value FROM settings WHERE key = 'machine_id'");
    const salt = await db.get<{ value: string }>("SELECT value FROM settings WHERE key = 'hmac_salt'");
    if (!machine) {
      await db.run("INSERT OR IGNORE INTO settings(key, value, updated_at) VALUES (?, ?, ?)", ["machine_id", randomUUID(), now]);
    }
    if (!salt) {
      await db.run("INSERT OR IGNORE INTO settings(key, value, updated_at) VALUES (?, ?, ?)", [
        "hmac_salt",
        randomBytes(32).toString("base64url"),
        now
      ]);
    }
    const required = await db.all<{ key: string; value: string }>(
      "SELECT key, value FROM settings WHERE key IN ('machine_id', 'hmac_salt') ORDER BY key"
    );
    if (required.length !== 2 || required.some((row) => typeof row.value !== "string" || row.value.length === 0)) {
      throw new Error("REQUIRED_SETTINGS_BOOTSTRAP_FAILED: machine_id and hmac_salt must be non-empty strings");
    }
    await db.exec("COMMIT");
    transactionOpen = false;
  } catch (error) {
    if (transactionOpen) {
      try { await db.exec("ROLLBACK"); } catch { /* preserve the bootstrap error */ }
      transactionOpen = false;
    }
    throw error;
  } finally {
    if (transactionOpen) {
      try { await db.exec("ROLLBACK"); } catch { /* best effort: the primary path preserves its own error */ }
    }
  }
}

export async function migrate(db: DatabaseAdapter): Promise<void> {
  await db.exec(`
    CREATE TABLE IF NOT EXISTS schema_migrations (
      version INTEGER PRIMARY KEY,
      name TEXT NOT NULL,
      applied_at TEXT NOT NULL
    );
  `);

  for (const migration of MIGRATIONS) {
    const structural = STRUCTURAL_MIGRATIONS.has(migration.version);
    if (!structural) {
      const row = await db.get<{ version: number }>("SELECT version FROM schema_migrations WHERE version = ?", [migration.version]);
      if (row) continue;
    }
    if (structural) {
      // PRAGMA foreign_keys is a no-op inside a transaction. Disable it before BEGIN for the
      // SQLite recovery/create-copy-drop-rename sequence, then restore it even on failure.
      await db.exec("PRAGMA foreign_keys = OFF");
    }
    try {
      await db.exec("BEGIN IMMEDIATE TRANSACTION");
      // Version state and every structural classification/data comparison must be read only
      // after the write lock is held. A waiter therefore observes changes committed by the
      // process that owned the lock before it.
      const row = await db.get<{ version: number }>("SELECT version FROM schema_migrations WHERE version = ?", [migration.version]);
      if (row) {
        await db.exec("COMMIT");
        continue;
      }
      const structuralPlan = structural ? await inspectStructuralState(db, structuralSpec(migration.version)) : null;
      if (structural && structuralPlan) {
        for (const table of structuralPlan.cleanup) await db.exec(`DROP TABLE IF EXISTS ${table}`);
        for (const table of structuralPlan.promote) {
          if (!table.temporary) throw new Error(`STRUCTURAL_MIGRATION_AMBIGUOUS:migration_${migration.version}:missing temporary table name`);
          await db.exec(`DROP TABLE IF EXISTS ${table.final}`);
          await db.exec(`ALTER TABLE ${table.temporary} RENAME TO ${table.final}`);
        }
        const structuralSpecForMigration = structuralSpec(migration.version);
        const finalAfterRecovery = await Promise.all(structuralSpecForMigration.tables.map((table) => tableMatches(db, table, migration.version)));
        if (structuralPlan.action === "run") {
          await migration.up(db);
        }
        else if (!finalAfterRecovery.every(Boolean)) await recreateStructuralTables(db, structuralSpecForMigration);
        await ensureStructuralIndexes(db, structuralSpecForMigration);
      } else {
        await migration.up(db);
      }
      if (structural) {
        const violations = await foreignKeyViolations(db);
        if (violations.length > 0) throw new Error(`STRUCTURAL_MIGRATION_FOREIGN_KEY_CHECK_FAILED:migration_${migration.version}`);
        const successor = structuralPlan?.action === "register"
          ? await validatedSuccessorVersion(db, migration.version)
          : null;
        const verifiedVersion = successor ?? migration.version;
        if (!(await schemaExactlyMatches(db, verifiedVersion))) {
          throw new Error(`STRUCTURAL_MIGRATION_SCHEMA_CHECK_FAILED:migration_${migration.version}`);
        }
      }
      await db.run("INSERT INTO schema_migrations(version, name, applied_at) VALUES (?, ?, ?)", [
        migration.version,
        migration.name,
        nowIso()
      ]);
      await db.exec("COMMIT");
    } catch (error) {
      try { await db.exec("ROLLBACK"); } catch { /* preserve the migration error */ }
      throw error;
    } finally {
      if (structural) await db.exec("PRAGMA foreign_keys = ON");
    }
  }

  await db.exec("PRAGMA foreign_keys = ON");
  await ensureRequiredSettings(db);
}