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);
    }
  }
];

async function ensureRequiredSettings(db: DatabaseAdapter): Promise<void> {
  const now = nowIso();
  const machine = await db.get<{ value: string }>("SELECT value FROM settings WHERE key = 'machine_id'");
  if (!machine) {
    await db.run("INSERT INTO settings(key, value, updated_at) VALUES (?, ?, ?)", ["machine_id", randomUUID(), now]);
  }

  const salt = await db.get<{ value: string }>("SELECT value FROM settings WHERE key = 'hmac_salt'");
  if (!salt) {
    await db.run("INSERT INTO settings(key, value, updated_at) VALUES (?, ?, ?)", [
      "hmac_salt",
      randomBytes(32).toString("base64url"),
      now
    ]);
  }
}

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 row = await db.get<{ version: number }>("SELECT version FROM schema_migrations WHERE version = ?", [migration.version]);
    if (row) {
      continue;
    }
    await migration.up(db);
    await db.run("INSERT INTO schema_migrations(version, name, applied_at) VALUES (?, ?, ?)", [
      migration.version,
      migration.name,
      nowIso()
    ]);
  }

  await ensureRequiredSettings(db);
}