import { describe, it, expect, beforeEach, afterEach } from 'vitest';
import { mkdtempSync, rmSync, writeFileSync } from 'node:fs';
import { tmpdir } from 'node:os';
import { join } from 'node:path';
import Database from 'better-sqlite3';
import {
  applyMigrations,
  listMigrations,
  readAppliedRows,
  stampMigration,
} from '../src/migrate.ts';

const LEGACY_BOOTSTRAP_SQL = `
    CREATE TABLE IF NOT EXISTS users (
      id            TEXT PRIMARY KEY,
      username      TEXT NOT NULL UNIQUE,
      password_hash TEXT NOT NULL,
      created_at    INTEGER NOT NULL
    );

    CREATE TABLE IF NOT EXISTS login_attempts (
      username        TEXT PRIMARY KEY,
      failed_count    INTEGER NOT NULL DEFAULT 0,
      last_failed_at  INTEGER,
      locked_until    INTEGER
    );
    CREATE INDEX IF NOT EXISTS idx_login_attempts_locked ON login_attempts(locked_until);

    CREATE TABLE IF NOT EXISTS oauth_clients (
      client_id            TEXT PRIMARY KEY,
      client_secret_hash   TEXT NOT NULL,
      client_name          TEXT NOT NULL,
      redirect_uris        TEXT NOT NULL,
      allowed_grants       TEXT NOT NULL,
      allowed_scopes       TEXT NOT NULL,
      owner_user_id        TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
      last_used_at         INTEGER,
      created_at           INTEGER NOT NULL,
      updated_at           INTEGER NOT NULL,
      revoked_at           INTEGER
    );
    CREATE INDEX IF NOT EXISTS idx_oauth_clients_owner ON oauth_clients(owner_user_id);

    CREATE TABLE IF NOT EXISTS oauth_authorization_codes (
      code_hash              TEXT PRIMARY KEY,
      client_id              TEXT NOT NULL REFERENCES oauth_clients(client_id) ON DELETE CASCADE,
      user_id                TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
      redirect_uri           TEXT NOT NULL,
      scope                  TEXT NOT NULL,
      code_challenge         TEXT NOT NULL,
      code_challenge_method  TEXT NOT NULL DEFAULT 'S256',
      expires_at             INTEGER NOT NULL,
      redeemed_at            INTEGER,
      created_at             INTEGER NOT NULL
    );
    CREATE INDEX IF NOT EXISTS idx_oauth_codes_expires ON oauth_authorization_codes(expires_at);
    CREATE INDEX IF NOT EXISTS idx_oauth_codes_client  ON oauth_authorization_codes(client_id);

    CREATE TABLE IF NOT EXISTS oauth_access_tokens (
      token_hash    TEXT PRIMARY KEY,
      client_id     TEXT NOT NULL REFERENCES oauth_clients(client_id) ON DELETE CASCADE,
      user_id       TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
      scope         TEXT NOT NULL,
      expires_at    INTEGER NOT NULL,
      revoked_at    INTEGER,
      created_at    INTEGER NOT NULL
    );
    CREATE INDEX IF NOT EXISTS idx_oauth_at_expires ON oauth_access_tokens(expires_at);
    CREATE INDEX IF NOT EXISTS idx_oauth_at_owner   ON oauth_access_tokens(client_id, user_id);

    CREATE TABLE IF NOT EXISTS oauth_refresh_tokens (
      token_hash              TEXT PRIMARY KEY,
      client_id               TEXT NOT NULL REFERENCES oauth_clients(client_id) ON DELETE CASCADE,
      user_id                 TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
      scope                   TEXT NOT NULL,
      expires_at              INTEGER NOT NULL,
      revoked_at              INTEGER,
      replaced_by_token_hash  TEXT,
      family_id               TEXT NOT NULL,
      created_at              INTEGER NOT NULL
    );
    CREATE INDEX IF NOT EXISTS idx_oauth_rt_expires ON oauth_refresh_tokens(expires_at);
    CREATE INDEX IF NOT EXISTS idx_oauth_rt_family  ON oauth_refresh_tokens(family_id);

    CREATE TABLE IF NOT EXISTS mfa_profiles (
      user_id          TEXT PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
      secret_encrypted TEXT NOT NULL,
      enrolled_at      INTEGER NOT NULL
    );

    CREATE TABLE IF NOT EXISTS mfa_recovery_codes (
      user_id    TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
      code_hash  TEXT NOT NULL,
      used_at    INTEGER,
      created_at INTEGER NOT NULL,
      PRIMARY KEY (user_id, code_hash)
    );
    CREATE INDEX IF NOT EXISTS idx_mfa_recovery_user
      ON mfa_recovery_codes(user_id) WHERE used_at IS NULL;

    CREATE TABLE IF NOT EXISTS mfa_attempts (
      user_id        TEXT PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
      failed_count   INTEGER NOT NULL DEFAULT 0,
      last_failed_at INTEGER,
      locked_until   INTEGER
    );
    CREATE INDEX IF NOT EXISTS idx_mfa_attempts_locked ON mfa_attempts(locked_until);
  `;

interface SchemaObject {
  type: string;
  name: string;
  sql: string | null;
}

function normalizeSql(sql: string | null): string | null {
  if (sql === null) return null;
  return sql.replace(/\s+/g, ' ').trim();
}

function snapshotSchema(db: Database.Database): SchemaObject[] {
  const rows = db
    .prepare(
      `SELECT type, name, sql FROM sqlite_master
       WHERE type IN ('table','index')
         AND name NOT LIKE 'sqlite_%'
         AND name != 'schema_migrations'
       ORDER BY type, name`,
    )
    .all() as SchemaObject[];
  return rows.map((r) => ({ ...r, sql: normalizeSql(r.sql) }));
}

describe('migrate', () => {
  let tmpDir: string;
  let db: Database.Database;

  beforeEach(() => {
    tmpDir = mkdtempSync(join(tmpdir(), 'sqlite-migrate-test-'));
    db = new Database(':memory:');
    db.pragma('foreign_keys = ON');
  });

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

  it('fresh apply produces byte-equivalent schema to legacy bootstrap', () => {
    writeFileSync(join(tmpDir, '0001_init.sql'), LEGACY_BOOTSTRAP_SQL);

    const legacyDb = new Database(':memory:');
    legacyDb.pragma('foreign_keys = ON');
    legacyDb.exec(LEGACY_BOOTSTRAP_SQL);
    const legacySnapshot = snapshotSchema(legacyDb);
    legacyDb.close();

    const summary = applyMigrations(db, tmpDir);
    expect(summary.applied).toBe(1);
    expect(summary.alreadyApplied).toBe(0);
    expect(summary.pending).toBe(0);
    expect(summary.stamped).toEqual([]);

    const newSnapshot = snapshotSchema(db);
    expect(newSnapshot).toEqual(legacySnapshot);
  });

  it('is idempotent on re-apply', () => {
    writeFileSync(join(tmpDir, '0001_init.sql'), LEGACY_BOOTSTRAP_SQL);
    applyMigrations(db, tmpDir);
    const summary = applyMigrations(db, tmpDir);
    expect(summary.applied).toBe(0);
    expect(summary.alreadyApplied).toBe(1);
    expect(summary.pending).toBe(0);
    expect(summary.stamped).toEqual([]);

    const rows = readAppliedRows(db);
    expect(rows).toHaveLength(1);
    expect(rows[0].version).toBe('0001');
  });

  it('aborts on checksum mismatch', () => {
    writeFileSync(join(tmpDir, '0001_init.sql'), 'CREATE TABLE original (x INTEGER);');
    applyMigrations(db, tmpDir);

    writeFileSync(join(tmpDir, '0001_init.sql'), 'CREATE TABLE tampered (x INTEGER);');

    expect(() => applyMigrations(db, tmpDir)).toThrow(/checksum mismatch.*0001_init/);
  });

  it('stamps genesis on pre-existing DB with users table', () => {
    writeFileSync(join(tmpDir, '0001_init.sql'), LEGACY_BOOTSTRAP_SQL);
    db.exec(`CREATE TABLE users (id TEXT PRIMARY KEY, username TEXT NOT NULL UNIQUE, password_hash TEXT NOT NULL, created_at INTEGER NOT NULL);`);

    const warnings: string[] = [];
    const summary = applyMigrations(db, tmpDir, {
      logger: (msg) => warnings.push(msg),
    });

    expect(summary.stamped).toEqual(['0001']);
    expect(summary.applied).toBe(0);
    expect(summary.alreadyApplied).toBe(1);
    expect(warnings.some((w) => w.includes('genesis-stamp'))).toBe(true);

    const rows = readAppliedRows(db);
    expect(rows).toHaveLength(1);
    expect(rows[0].version).toBe('0001');

    const files = listMigrations(tmpDir);
    expect(rows[0].checksum).toBe(files[0].checksum);
  });

  it('rolls back when a migration fails partway', () => {
    writeFileSync(join(tmpDir, '0001_ok.sql'), 'CREATE TABLE a (x INTEGER);');
    writeFileSync(
      join(tmpDir, '0002_bad.sql'),
      'CREATE TABLE b (x INTEGER); CREATE TABLE INVALID_SYNTAX_HERE',
    );

    expect(() => applyMigrations(db, tmpDir)).toThrow();

    const rows = readAppliedRows(db);
    expect(rows).toHaveLength(1);
    expect(rows[0].version).toBe('0001');

    const aExists = db
      .prepare(`SELECT name FROM sqlite_master WHERE type='table' AND name='a'`)
      .get();
    expect(aExists).toBeDefined();

    const bExists = db
      .prepare(`SELECT name FROM sqlite_master WHERE type='table' AND name='b'`)
      .get();
    expect(bExists).toBeUndefined();
  });

  it('stampMigration marks version applied without running body', () => {
    writeFileSync(join(tmpDir, '0001_init.sql'), 'CREATE TABLE only_if_applied (x INTEGER);');
    const file = stampMigration(db, tmpDir, '0001');
    expect(file.version).toBe('0001');
    const rows = readAppliedRows(db);
    expect(rows).toHaveLength(1);
    const tableMissing = db
      .prepare(`SELECT name FROM sqlite_master WHERE type='table' AND name='only_if_applied'`)
      .get();
    expect(tableMissing).toBeUndefined();
  });

  it('stampMigration refuses double-stamp', () => {
    writeFileSync(join(tmpDir, '0001_init.sql'), 'CREATE TABLE x (a INTEGER);');
    stampMigration(db, tmpDir, '0001');
    expect(() => stampMigration(db, tmpDir, '0001')).toThrow(/already applied/);
  });

  it('rejects invalid migration filenames', () => {
    writeFileSync(join(tmpDir, 'notamigration.sql'), 'SELECT 1;');
    expect(() => applyMigrations(db, tmpDir)).toThrow(/invalid migration filename/);
  });

  it('honors stampGenesis option even without users table', () => {
    writeFileSync(join(tmpDir, '0001_init.sql'), LEGACY_BOOTSTRAP_SQL);
    const summary = applyMigrations(db, tmpDir, { stampGenesis: true });
    expect(summary.stamped).toEqual(['0001']);
    expect(summary.applied).toBe(0);

    const usersExists = db
      .prepare(`SELECT name FROM sqlite_master WHERE type='table' AND name='users'`)
      .get();
    expect(usersExists).toBeUndefined();
  });

  // New probe-table specs

  it('probe-table-not-present + stampGenesis=false runs first migration normally', () => {
    writeFileSync(join(tmpDir, '0001_init.sql'), 'CREATE TABLE users (id TEXT PRIMARY KEY);');
    const summary = applyMigrations(db, tmpDir, { stampGenesis: false });
    expect(summary.stamped).toEqual([]);
    expect(summary.applied).toBe(1);
    expect(summary.alreadyApplied).toBe(0);
    const usersExists = db
      .prepare(`SELECT name FROM sqlite_master WHERE type='table' AND name='users'`)
      .get();
    expect(usersExists).toBeDefined();
    const rows = readAppliedRows(db);
    expect(rows).toHaveLength(1);
    expect(rows[0].version).toBe('0001');
  });

  it('genesisProbeTable=undefined defaults to users (authd back-compat)', () => {
    writeFileSync(join(tmpDir, '0001_init.sql'), LEGACY_BOOTSTRAP_SQL);
    db.exec(`CREATE TABLE users (id TEXT PRIMARY KEY);`);

    const warnings: string[] = [];
    const summary = applyMigrations(db, tmpDir, {
      logger: (msg) => warnings.push(msg),
    });

    expect(summary.stamped).toEqual(['0001']);
    expect(summary.applied).toBe(0);
    expect(summary.alreadyApplied).toBe(1);
    expect(
      warnings.some((w) => w.includes('genesis-stamp') && w.includes("'users'")),
    ).toBe(true);
  });

  it("genesisProbeTable='sessions' stamps a buchinese-shaped DB", () => {
    writeFileSync(
      join(tmpDir, '0001_init.sql'),
      `CREATE TABLE users (id TEXT PRIMARY KEY); CREATE TABLE sessions (id TEXT PRIMARY KEY);`,
    );
    db.exec(`CREATE TABLE sessions (id TEXT PRIMARY KEY);`);

    const warnings: string[] = [];
    const summary = applyMigrations(db, tmpDir, {
      genesisProbeTable: 'sessions',
      logger: (msg) => warnings.push(msg),
    });

    expect(summary.stamped).toEqual(['0001']);
    expect(summary.applied).toBe(0);

    const usersExists = db
      .prepare(`SELECT name FROM sqlite_master WHERE type='table' AND name='users'`)
      .get();
    expect(usersExists).toBeUndefined();

    expect(
      warnings.some((w) => w.includes('genesis-stamp') && w.includes("'sessions'")),
    ).toBe(true);
  });
});