'use strict';

const test = require('node:test');
const assert = require('node:assert/strict');
const crypto = require('node:crypto');
const fs = require('node:fs');
const os = require('node:os');
const path = require('node:path');
const initSqlJs = require('sql.js');
const database = require('../../src/database');

const MIGRATION_DIR = path.join(__dirname, '../../src/database/migrations');
const EXPECTED_VERSIONS = Array.from({ length: 20 }, (_, index) => index + 1);
const quietLogger = { info() {}, warn() {}, error() {} };

function resultRows(result) {
  if (!result?.length) return [];
  const [{ columns, values }] = result;
  return values.map((valuesRow) => Object.fromEntries(columns.map((column, index) => [column, valuesRow[index]])));
}

function scalar(db, sql) {
  return db.exec(sql)?.[0]?.values?.[0]?.[0] ?? null;
}

function migrationEntries(throughVersion) {
  return fs.readdirSync(MIGRATION_DIR)
    .filter((name) => /^\d{4}-.+\.js$/.test(name))
    .sort()
    .map((name) => ({
      name,
      path: path.join(MIGRATION_DIR, name),
      migration: require(path.join(MIGRATION_DIR, name)),
    }))
    .filter(({ migration }) => migration.version <= throughVersion);
}

function recordAppliedMigrations(db, entries) {
  const columns = resultRows(db.exec('PRAGMA table_info(schema_migrations)')).map((column) => column.name);
  for (const entry of entries) {
    const valuesByColumn = {
      version: entry.migration.version,
      checksum: crypto.createHash('sha256').update(fs.readFileSync(entry.path)).digest('hex'),
      description: entry.migration.description,
      status: 'applied',
      applied_at: new Date(1700000000000 + entry.migration.version * 1000).toISOString(),
      error: null,
      snapshot_path: null,
      recovery_instructions: null,
      row_count_delta_json: '{}',
    };
    const selected = columns.filter((column) => Object.hasOwn(valuesByColumn, column));
    db.run(
      `INSERT OR REPLACE INTO schema_migrations (${selected.join(', ')}) VALUES (${selected.map(() => '?').join(', ')})`,
      selected.map((column) => valuesByColumn[column]),
    );
  }
}

function seedLegacyGame(db, { gameId, title, version = '1.00', backupPath = '/srv/backups/game.pkg' }) {
  db.run(`
    INSERT INTO games (game_id, title, content_id, size, version, installed, backed_up, ftp_path)
    VALUES (?, ?, ?, 4096, ?, 1, 1, ?)
  `, [gameId, title, gameId, version, `/mnt/console/${gameId}`]);
  db.run('INSERT INTO backups (game_id, backup_path) VALUES (?, ?)', [gameId, backupPath]);
}

function insertAdaptiveTrophyEvidence(db) {
  const columns = resultRows(db.exec('PRAGMA table_info(trophy_psn_snapshots)'));
  const overrides = {
    snapshot_id: 'fixture-psn-snapshot',
    snapshot_key: 'fixture-psn-snapshot',
    account_id: 'fixture-account',
    account_ref: 'fixture-account',
    online_id: 'fixture-user',
    source: 'psn-read-only',
    source_type: 'psn-read-only',
    source_ref: 'fixture-export',
    captured_at: 1700000000000,
    observed_at: 1700000000000,
    imported_at: 1700000000000,
    evidence_hash: 'a'.repeat(64),
    payload_hash: 'b'.repeat(64),
    canonical_sha256: 'c'.repeat(64),
    payload_json: '{"titles":[]}',
    snapshot_json: '{"titles":[]}',
    trophy_count: 1,
    title_count: 1,
    confidence: 'high',
    provenance: 'migration-rehearsal',
    status: 'verified',
  };
  const selected = [];
  const values = [];
  for (const column of columns) {
    if (column.pk && /INT/i.test(column.type || '')) continue;
    if (Object.hasOwn(overrides, column.name)) {
      selected.push(column.name);
      values.push(overrides[column.name]);
      continue;
    }
    if (column.notnull && column.dflt_value === null) {
      selected.push(column.name);
      values.push(/INT|REAL|NUM/i.test(column.type || '') ? 1 : `fixture-${column.name}`);
    }
  }
  db.run(
    `INSERT INTO trophy_psn_snapshots (${selected.join(', ')}) VALUES (${selected.map(() => '?').join(', ')})`,
    values,
  );
}

async function buildVersionedDatabase(root, throughVersion, { beforeVersion, afterMigrations } = {}) {
  const SQL = await initSqlJs();
  const db = new SQL.Database();
  db.run('PRAGMA foreign_keys = ON');
  const entries = migrationEntries(throughVersion);
  for (const entry of entries) {
    try {
      beforeVersion?.(entry.migration.version, db);
      db.run('BEGIN IMMEDIATE');
      entry.migration.up(db);
      db.run('COMMIT');
    } catch (error) {
      try { db.run('ROLLBACK'); } catch {}
      throw new Error(`Direct migration fixture failed at version ${entry.migration.version}: ${error.message || error}`, { cause: error });
    }
  }
  try {
    afterMigrations?.(db);
  } catch (error) {
    throw new Error(`Post-migration fixture failed at version ${throughVersion}: ${error.message || error}`, { cause: error });
  }
  recordAppliedMigrations(db, entries);
  fs.writeFileSync(path.join(root, 'chimera.db'), Buffer.from(db.export()));
  db.close();
}

async function buildAmbiguousLegacyDatabase(root) {
  const SQL = await initSqlJs();
  const db = new SQL.Database();
  db.run(`
    CREATE TABLE games (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      title TEXT NOT NULL DEFAULT '',
      game_id TEXT NOT NULL,
      content_id TEXT DEFAULT '',
      size INTEGER DEFAULT 0,
      version TEXT DEFAULT '',
      installed INTEGER DEFAULT 0,
      backed_up INTEGER DEFAULT 0,
      ftp_path TEXT DEFAULT '',
      created_at TEXT DEFAULT (datetime('now'))
    )
  `);
  db.run('CREATE TABLE backups (id INTEGER PRIMARY KEY AUTOINCREMENT, game_id TEXT NOT NULL, backup_path TEXT NOT NULL UNIQUE)');
  db.run(`
    INSERT INTO games (game_id, title, content_id, size, version, installed)
    VALUES ('CUSA10002', 'Ambiguous Fixture', 'CUSA10002', 1, '1.00', 0),
           ('CUSA10002', 'Ambiguous Fixture', 'CUSA10002', 1, '1.01', 0)
  `);
  fs.writeFileSync(path.join(root, 'chimera.db'), Buffer.from(db.export()));
  db.close();
}

async function openRoot(root) {
  database.configure({ dataDir: root, logger: quietLogger });
  return database.initialize();
}

async function closeAndRemove(root, { closeDatabase = true } = {}) {
  database.setTestMigrationFailureVersion(null);
  if (closeDatabase) {
    try {
      await database.close();
    } catch (error) {
      throw new Error(`Cleanup database close failed: ${error.message || error}`, { cause: error });
    }
  }
  try {
    fs.rmSync(root, { recursive: true, force: true });
  } catch (error) {
    throw new Error(`Cleanup fixture removal failed: ${error.message || error}`, { cause: error });
  }
}

function assertAllMigrationsApplied() {
  const rows = database.runQuery('SELECT version, status FROM schema_migrations ORDER BY version');
  assert.deepEqual(rows.map((row) => row.version), EXPECTED_VERSIONS);
  assert.ok(rows.every((row) => row.status === 'applied'));
}

function assertForeignKeysClean() {
  assert.deepEqual(database.checkForeignKeys(), []);
}

test('CH-1002 rehearses legacy, v11 and v16 migration and recovery paths', async (t) => {
  await t.test('ambiguous legacy schema migrates without inventing version certainty', async () => {
    const root = fs.mkdtempSync(path.join(os.tmpdir(), 'chimera-ch1002-legacy-'));
    try {
      await buildAmbiguousLegacyDatabase(root);
      await openRoot(root);
      assertAllMigrationsApplied();
      const report = JSON.parse(database.runQuery("SELECT report_json FROM migration_reports WHERE migration_name = 'legacy-to-v3'")[0].report_json);
      assert.deepEqual(report.ambiguousVersions, [{ game_id: 'CUSA10002', versions: ['1.00', '1.01'] }]);
      assert.equal(database.runQuery("SELECT COUNT(*) AS count FROM titles WHERE title_id = 'CUSA10002'")[0].count, 1);
      assertForeignKeysClean();
    } finally {
      await closeAndRemove(root);
    }
  });

  await t.test('v11 install, update and backup evidence survives through v20', async () => {
    const root = fs.mkdtempSync(path.join(os.tmpdir(), 'chimera-ch1002-v11-'));
    try {
      await buildVersionedDatabase(root, 11, {
        beforeVersion(version, db) {
          if (version === 4) seedLegacyGame(db, {
            gameId: 'CUSA10011',
            title: 'Version Eleven Fixture',
            backupPath: '/srv/backups/CUSA10011/game.pkg',
          });
        },
        afterMigrations(db) {
          const titleId = scalar(db, "SELECT id FROM titles WHERE title_id = 'CUSA10011'");
          db.run(`
            INSERT INTO version_evidence (subject_type, subject_id, semantic_version, raw_version, version_source, observed_at, confidence)
            VALUES ('title', ?, '1.02', '01.020.000', 'fixture-update-manifest', 1700000000000, 'high')
          `, [titleId]);
        },
      });
      await openRoot(root);
      assertAllMigrationsApplied();
      assert.equal(database.runQuery("SELECT COUNT(*) AS count FROM console_installs ci JOIN titles t ON t.id = ci.title_id WHERE t.title_id = 'CUSA10011'")[0].count, 1);
      assert.equal(database.runQuery("SELECT COUNT(*) AS count FROM local_copies lc JOIN titles t ON t.id = lc.title_id WHERE t.title_id = 'CUSA10011'")[0].count, 1);
      assert.equal(database.runQuery("SELECT semantic_version FROM version_evidence ve JOIN titles t ON t.id = ve.subject_id WHERE ve.subject_type = 'title' AND t.title_id = 'CUSA10011'")[0].semantic_version, '1.02');
      assertForeignKeysClean();
    } finally {
      await closeAndRemove(root);
    }
  });

  await t.test('v16 trophy evidence survives forced failure, snapshot rollback and idempotent rerun', async () => {
    const root = fs.mkdtempSync(path.join(os.tmpdir(), 'chimera-ch1002-v16-'));
    let stage = 'build-v16-copy';
    try {
      await buildVersionedDatabase(root, 16, {
        beforeVersion(version, db) {
          if (version === 4) seedLegacyGame(db, {
            gameId: 'PPSA10016',
            title: 'Version Sixteen Fixture',
            backupPath: '/srv/backups/PPSA10016/game.pkg',
          });
        },
        afterMigrations(db) {
          insertAdaptiveTrophyEvidence(db);
        },
      });

      stage = 'forced-version-18-failure';
      database.setTestMigrationFailureVersion(18);
      await assert.rejects(() => openRoot(root), /Test-gedwongen migratiefout voor versie 18/);
      assert.equal(database.runQuery('SELECT COUNT(*) AS count FROM trophy_psn_snapshots')[0].count, 1);
      assert.equal(database.runQuery("SELECT COUNT(*) AS count FROM local_copies lc JOIN titles t ON t.id = lc.title_id WHERE t.title_id = 'PPSA10016'")[0].count, 1);
      const failed = database.runQuery('SELECT status, snapshot_path, recovery_instructions FROM schema_migrations WHERE version = 18')[0];
      assert.equal(failed.status, 'failed');
      assert.ok(failed.snapshot_path);
      assert.ok(failed.recovery_instructions);
      assertForeignKeysClean();

      stage = 'recovery-retry';
      database.setTestMigrationFailureVersion(null);
      await database.initialize();
      assertAllMigrationsApplied();
      const semanticBefore = database.runQuery(`
        SELECT
          (SELECT COUNT(*) FROM trophy_psn_snapshots) AS trophies,
          (SELECT COUNT(*) FROM local_copies) AS backups,
          (SELECT COUNT(*) FROM schema_migrations WHERE status = 'applied') AS migrations
      `)[0];
      assert.deepEqual(semanticBefore, { trophies: 1, backups: 1, migrations: 20 });
      assertForeignKeysClean();

      stage = 'idempotent-reopen';
      await database.close();
      await openRoot(root);
      const semanticAfter = database.runQuery(`
        SELECT
          (SELECT COUNT(*) FROM trophy_psn_snapshots) AS trophies,
          (SELECT COUNT(*) FROM local_copies) AS backups,
          (SELECT COUNT(*) FROM schema_migrations WHERE status = 'applied') AS migrations
      `)[0];
      assert.deepEqual(semanticAfter, semanticBefore);
      assertAllMigrationsApplied();
      assertForeignKeysClean();
    } catch (error) {
      throw new Error(`CH-1002 v16 stage ${stage} failed: ${error.message || error}`, { cause: error });
    } finally {
      await closeAndRemove(root, { closeDatabase: stage !== 'build-v16-copy' });
    }
  });
});