import { DatabaseSync } from 'node:sqlite'

/**
 * Forward-only, idempotent, bounded migrations (spec: no data rollback,
 * previous revision must keep working, heavy work staged as jobs).
 */
export interface Migration {
  version: number
  name: string
  up(db: DatabaseSync): void
}

export const MIGRATIONS: Migration[] = [
  {
    version: 1,
    name: 'initial schema',
    up: (db) => {
      db.exec('PRAGMA auto_vacuum = INCREMENTAL')
      db.exec(`
        CREATE TABLE audit_events (
          seq INTEGER PRIMARY KEY AUTOINCREMENT,
          id TEXT NOT NULL UNIQUE,
          ts TEXT NOT NULL,
          session_id TEXT,
          revision TEXT,
          agent_digest TEXT,
          worktree_head TEXT,
          worktree_dirty INTEGER NOT NULL DEFAULT 0,
          kind TEXT NOT NULL,
          payload TEXT NOT NULL
        );
        CREATE INDEX audit_ts ON audit_events(ts);
        CREATE INDEX audit_kind_ts ON audit_events(kind, ts);

        CREATE TABLE chat_messages (
          id TEXT PRIMARY KEY,
          update_id INTEGER UNIQUE,
          chat_id INTEGER NOT NULL,
          message_id INTEGER NOT NULL UNIQUE,
          direction TEXT NOT NULL CHECK(direction IN ('in','out')),
          ts TEXT NOT NULL,
          sender_id TEXT,
          sender_name TEXT,
          reply_to_message_id INTEGER,
          trigger_reason TEXT,
          text TEXT,
          media_json TEXT,
          created_at TEXT NOT NULL
        );
        CREATE INDEX chat_ts ON chat_messages(ts);

        CREATE TABLE tombstones (
          message_id INTEGER PRIMARY KEY,
          requester_id TEXT NOT NULL,
          authority TEXT NOT NULL CHECK(authority IN ('sender','oliver')),
          reason TEXT,
          ts TEXT NOT NULL
        );

        CREATE TABLE deployment_events (
          id TEXT PRIMARY KEY,
          ts TEXT NOT NULL,
          kind TEXT NOT NULL,
          revision TEXT,
          detail TEXT NOT NULL,
          received_at TEXT NOT NULL
        );
        CREATE INDEX deploy_ts ON deployment_events(ts);

        CREATE TABLE media (
          digest TEXT PRIMARY KEY,
          size INTEGER NOT NULL,
          type TEXT NOT NULL,
          original_name TEXT,
          telegram_file_id TEXT,
          created_at TEXT NOT NULL
        );

        CREATE TABLE media_analyses (
          digest TEXT NOT NULL,
          capability TEXT NOT NULL,
          model TEXT NOT NULL,
          schema_version INTEGER NOT NULL,
          result TEXT NOT NULL,
          usage_json TEXT,
          created_at TEXT NOT NULL,
          PRIMARY KEY (digest, capability, model, schema_version)
        );

        CREATE TABLE jobs (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          kind TEXT NOT NULL,
          started_at TEXT NOT NULL,
          finished_at TEXT,
          status TEXT NOT NULL CHECK(status IN ('running','done','failed')),
          detail TEXT
        );

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

export function openDatabase(path: string, options: { readonly?: boolean } = {}): DatabaseSync {
  const db = new DatabaseSync(path, { readOnly: options.readonly })
  db.exec('PRAGMA journal_mode = WAL')
  db.exec('PRAGMA synchronous = FULL')
  db.exec('PRAGMA foreign_keys = ON')
  db.exec('PRAGMA busy_timeout = 5000')
  return db
}

export function migrate(db: DatabaseSync): { from: number; to: number } {
  const before = db.prepare('PRAGMA user_version').get() as { user_version: number }
  let current = before.user_version
  for (const m of MIGRATIONS) {
    if (m.version <= current) continue
    db.exec('BEGIN')
    try {
      m.up(db)
      db.exec(`PRAGMA user_version = ${m.version}`)
      db.exec('COMMIT')
      current = m.version
    } catch (err) {
      db.exec('ROLLBACK')
      throw err
    }
  }
  return { from: before.user_version, to: current }
}

export function schemaVersion(db: DatabaseSync): number {
  const row = db.prepare('PRAGMA user_version').get() as { user_version: number }
  return row.user_version
}