import { describe, it, expect, beforeEach, afterEach } from 'vitest';
import Database from 'better-sqlite3';
import { runMigrations } from './migrate.js';

/**
 * Seed the minimal pre-existing schema that runMigrations expects to find.
 * Shared between top-level describes so all tests exercise identical fixtures
 * (matches the shape produced by schema.sql for the columns/constraints that
 * migrate.ts actually reads).
 */
function seedMinimalSchema(db: Database.Database): void {
  db.exec(`
    CREATE TABLE IF NOT EXISTS jobs (
      id TEXT PRIMARY KEY,
      repo TEXT NOT NULL DEFAULT '',
      issue_number INTEGER NOT NULL DEFAULT 0,
      status TEXT NOT NULL DEFAULT 'queued',
      created_at TEXT NOT NULL DEFAULT (datetime('now'))
    );
    CREATE TABLE IF NOT EXISTS local_tasks (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      title TEXT NOT NULL DEFAULT '',
      body TEXT NOT NULL DEFAULT '',
      workspace_path TEXT,
      created_at TEXT NOT NULL DEFAULT (datetime('now'))
    );
    CREATE TABLE IF NOT EXISTS users (
      id TEXT PRIMARY KEY,
      email TEXT UNIQUE NOT NULL,
      name TEXT,
      avatar_url TEXT,
      role TEXT NOT NULL DEFAULT 'user',
      status TEXT NOT NULL DEFAULT 'pending',
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now'))
    );
    CREATE TABLE IF NOT EXISTS oauth_accounts (
      id TEXT PRIMARY KEY,
      user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
      provider TEXT NOT NULL,
      provider_id TEXT NOT NULL,
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      UNIQUE(provider, provider_id)
    );
    CREATE TABLE IF NOT EXISTS sessions (
      sid TEXT PRIMARY KEY,
      sess TEXT NOT NULL,
      expired TEXT NOT NULL
    );
  `);
}

describe('runMigrations', () => {
  let db: Database.Database;

  beforeEach(() => {
    db = new Database(':memory:');
    db.pragma('foreign_keys = ON');
    seedMinimalSchema(db);
  });

  afterEach(() => {
    db.close();
  });

  it('adds owner_id column to jobs', () => {
    runMigrations(db);
    const columns = db.prepare("PRAGMA table_info('jobs')").all() as Array<{ name: string }>;
    expect(columns.some(c => c.name === 'owner_id')).toBe(true);
  });

  it('adds owner_id column to local_tasks', () => {
    runMigrations(db);
    const columns = db.prepare("PRAGMA table_info('local_tasks')").all() as Array<{ name: string }>;
    expect(columns.some(c => c.name === 'owner_id')).toBe(true);
  });

  it('is idempotent — running twice does not error', () => {
    runMigrations(db);
    expect(() => runMigrations(db)).not.toThrow();
  });

  it('adds continued_from_job_id column to jobs (idempotent)', () => {
    runMigrations(db);
    const columns = db.prepare("PRAGMA table_info('jobs')").all() as Array<{ name: string }>;
    expect(columns.some(c => c.name === 'continued_from_job_id')).toBe(true);

    // 2nd run is no-op (idempotent)
    expect(() => runMigrations(db)).not.toThrow();
    const columnsAfter = db.prepare("PRAGMA table_info('jobs')").all() as Array<{ name: string }>;
    expect(columnsAfter.filter(c => c.name === 'continued_from_job_id').length).toBe(1);
  });

  it('adds last_backend_id column to jobs (idempotent)', () => {
    runMigrations(db);
    const columns = db.prepare("PRAGMA table_info('jobs')").all() as Array<{ name: string }>;
    expect(columns.some(c => c.name === 'last_backend_id')).toBe(true);

    // 2nd run is no-op
    expect(() => runMigrations(db)).not.toThrow();
    const columnsAfter = db.prepare("PRAGMA table_info('jobs')").all() as Array<{ name: string }>;
    expect(columnsAfter.filter(c => c.name === 'last_backend_id').length).toBe(1);
  });
});

describe('MCP table migrations', () => {
  let db: Database.Database;

  beforeEach(() => {
    db = new Database(':memory:');
    seedMinimalSchema(db);
  });

  afterEach(() => {
    db.close();
  });

  it('creates mcp_servers/user_mcp_tokens/mcp_server_tools/mcp_oauth_pending idempotently and preserves data', () => {
    runMigrations(db);

    // Insert a sentinel row into mcp_servers after the first migration. If the
    // second runMigrations re-creates (or DROPs) the table, this row will
    // disappear — which is what makes the idempotency claim meaningful beyond
    // mere `CREATE TABLE IF NOT EXISTS` behaviour.
    db.prepare(
      `INSERT INTO mcp_servers
         (id, name, url, oauth_client_id, oauth_client_secret_enc)
       VALUES (?, ?, ?, ?, ?)`
    ).run('test-srv', 'Test', 'https://example.com', 'cid', Buffer.from([0x00]));

    // Second run must not throw AND must not wipe data.
    expect(() => runMigrations(db)).not.toThrow();

    const row = db.prepare(
      "SELECT id, name FROM mcp_servers WHERE id = 'test-srv'"
    ).get() as { id: string; name: string } | undefined;
    expect(row).toBeDefined();
    expect(row?.name).toBe('Test');

    const tables = db.prepare(
      "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name"
    ).all() as Array<{ name: string }>;
    const names = tables.map((t) => t.name);

    expect(names).toContain('mcp_servers');
    expect(names).toContain('user_mcp_tokens');
    expect(names).toContain('mcp_server_tools');
    expect(names).toContain('mcp_oauth_pending');
  });

  it('creates idx_mcp_oauth_pending_created index', () => {
    runMigrations(db);
    const indexes = db.prepare(
      "SELECT name FROM sqlite_master WHERE type='index' AND name='idx_mcp_oauth_pending_created'"
    ).all() as Array<{ name: string }>;
    expect(indexes.length).toBe(1);
  });

  it('uses BLOB type for encrypted columns', () => {
    runMigrations(db);

    const findColType = (table: string, column: string): string | undefined => {
      const cols = db.prepare(`PRAGMA table_info('${table}')`).all() as Array<{
        name: string;
        type: string;
      }>;
      return cols.find((c) => c.name === column)?.type;
    };

    expect(findColType('mcp_servers', 'oauth_client_secret_enc')).toBe('BLOB');
    expect(findColType('mcp_servers', 'static_token_enc')).toBe('BLOB');
    expect(findColType('user_mcp_tokens', 'access_token_enc')).toBe('BLOB');
    expect(findColType('user_mcp_tokens', 'refresh_token_enc')).toBe('BLOB');
  });

  it('adds auth_kind, static_token_enc, and owner_id columns to mcp_servers', () => {
    runMigrations(db);
    const cols = db.prepare("PRAGMA table_info('mcp_servers')").all() as Array<{ name: string }>;
    const names = cols.map(c => c.name);
    expect(names).toContain('auth_kind');
    expect(names).toContain('static_token_enc');
    expect(names).toContain('owner_id');
  });

  it('creates idx_mcp_servers_owner index', () => {
    runMigrations(db);
    const indexes = db.prepare(
      "SELECT name FROM sqlite_master WHERE type='index' AND name='idx_mcp_servers_owner'"
    ).all() as Array<{ name: string }>;
    expect(indexes.length).toBe(1);
  });

  it('auth_kind defaults to oauth for existing rows', () => {
    runMigrations(db);
    // Insert a row without specifying auth_kind to test the DEFAULT
    db.prepare(
      `INSERT INTO mcp_servers (id, name, url, oauth_client_id, oauth_client_secret_enc)
       VALUES (?, ?, ?, ?, ?)`
    ).run('test-default', 'Test', 'https://example.com', 'cid', Buffer.from([0x01]));
    const row = db.prepare("SELECT auth_kind, owner_id FROM mcp_servers WHERE id = 'test-default'").get() as { auth_kind: string; owner_id: string | null };
    expect(row.auth_kind).toBe('oauth');
    expect(row.owner_id).toBeNull();
  });
});
