/**
 * AAO Gateway Phase 2b migration test.
 *
 * Coverage:
 *  - Fresh DB: new columns exist, gateway_key_usage table exists
 *  - Idempotency: runMigrations called twice produces the same shape
 *  - Pre-Phase-2b shape: drops the new columns, runs migrate, columns are
 *    re-added as nullable INTEGER
 */
import { describe, it, expect, beforeEach, afterEach } from 'vitest';
import Database from 'better-sqlite3';
import { mkdtempSync, rmSync } from 'fs';
import { tmpdir } from 'os';
import { join } from 'path';
import { runMigrations } from './migrate.js';

function getColumnNames(db: Database.Database, table: string): string[] {
  return (db.prepare(`PRAGMA table_info(${table})`).all() as Array<{ name: string }>).map(c => c.name);
}

function getColumnInfo(db: Database.Database, table: string, col: string): { notnull: number; dflt_value: unknown; type: string } | undefined {
  const rows = db.prepare(`PRAGMA table_info(${table})`).all() as Array<{ name: string; notnull: number; dflt_value: unknown; type: string }>;
  return rows.find(r => r.name === col);
}

describe('Phase 2b migration: gateway_virtual_keys columns + gateway_key_usage table', () => {
  let dir: string;
  let dbPath: string;
  let db: Database.Database;

  beforeEach(() => {
    dir = mkdtempSync(join(tmpdir(), 'gw-2b-mig-'));
    dbPath = join(dir, 'db.sqlite');
    db = new Database(dbPath);
    db.pragma('foreign_keys = ON');
    // Seed an existing Phase 2a-shaped gateway_virtual_keys (no Phase 2b
    // columns) to simulate a deployed pre-2b DB.
    db.exec(`
      CREATE TABLE IF NOT EXISTS gateway_virtual_keys (
        id             TEXT PRIMARY KEY,
        key_hash       TEXT NOT NULL UNIQUE,
        key_prefix     TEXT NOT NULL,
        team           TEXT NOT NULL,
        allowed_models TEXT,
        source         TEXT NOT NULL DEFAULT 'admin',
        created_at     TEXT NOT NULL,
        created_by     TEXT,
        revoked_at     TEXT,
        revoked_by     TEXT,
        last_used_at   TEXT
      );
    `);
  });

  afterEach(() => {
    db.close();
    rmSync(dir, { recursive: true, force: true });
  });

  it('adds tokens_budget + rate_limit_rpm columns to a Phase 2a-shaped DB', () => {
    expect(getColumnNames(db, 'gateway_virtual_keys')).not.toContain('tokens_budget');
    expect(getColumnNames(db, 'gateway_virtual_keys')).not.toContain('rate_limit_rpm');

    runMigrations(db);

    const cols = getColumnNames(db, 'gateway_virtual_keys');
    expect(cols).toContain('tokens_budget');
    expect(cols).toContain('rate_limit_rpm');

    // Nullable (no NOT NULL constraint) so existing rows survive.
    const tb = getColumnInfo(db, 'gateway_virtual_keys', 'tokens_budget');
    expect(tb?.notnull).toBe(0);
    expect(tb?.type.toUpperCase()).toBe('INTEGER');
    const rl = getColumnInfo(db, 'gateway_virtual_keys', 'rate_limit_rpm');
    expect(rl?.notnull).toBe(0);
    expect(rl?.type.toUpperCase()).toBe('INTEGER');
  });

  it('creates gateway_key_usage table with composite PK', () => {
    runMigrations(db);
    const tables = db
      .prepare("SELECT name FROM sqlite_master WHERE type='table' AND name='gateway_key_usage'")
      .all() as Array<{ name: string }>;
    expect(tables).toHaveLength(1);

    const cols = getColumnNames(db, 'gateway_key_usage');
    expect(cols).toEqual(
      expect.arrayContaining(['key_id', 'period_start', 'tokens_in', 'tokens_out', 'requests', 'last_updated_at']),
    );

    // PRIMARY KEY columns are flagged in pk > 0 in PRAGMA table_info.
    const pkInfo = db.prepare(`PRAGMA table_info(gateway_key_usage)`).all() as Array<{ name: string; pk: number }>;
    const pkCols = pkInfo.filter(c => c.pk > 0).map(c => c.name).sort();
    expect(pkCols).toEqual(['key_id', 'period_start']);
  });

  it('is idempotent (runMigrations twice has no effect)', () => {
    runMigrations(db);
    const colsAfter1 = getColumnNames(db, 'gateway_virtual_keys');
    runMigrations(db); // should not throw or duplicate columns
    const colsAfter2 = getColumnNames(db, 'gateway_virtual_keys');
    expect(colsAfter2).toEqual(colsAfter1);
  });
});
