import { mkdtempSync, readFileSync, rmSync } from 'node:fs';
import { tmpdir } from 'node:os';
import { join, resolve } from 'node:path';
import { fileURLToPath } from 'node:url';
import { DatabaseSync } from 'node:sqlite';

const root = resolve(fileURLToPath(new URL('..', import.meta.url)));
const source = readFileSync(join(root, 'lib/db.mjs'), 'utf8');
const required = ['deployment_runs', 'deployment_events', 'backup_verifications', 'production_checks'];
const statements = new Map();
for (const table of required) {
  const escaped = table.replace(/[.*+?^${}()|[\]\\]/g, '\\$&');
  const regex = new RegExp(`db\\.exec\\(\\\`(CREATE TABLE IF NOT EXISTS ${escaped} \\([\\s\\S]*?\\n  \\))\\\`\\);`);
  const match = source.match(regex);
  if (!match) throw new Error(`Fehlende CREATE-TABLE-Anweisung: ${table}`);
  statements.set(table, match[1]);
}

const tempRoot = mkdtempSync(join(tmpdir(), 'vendoo-production-ops-'));
const db = new DatabaseSync(join(tempRoot, 'production-operations.db'));
try {
  db.exec('PRAGMA foreign_keys = ON');
  db.exec('CREATE TABLE users (id INTEGER PRIMARY KEY)');
  db.exec('CREATE TABLE operation_backups (id TEXT PRIMARY KEY)');
  for (const table of required) db.exec(statements.get(table));
  db.prepare("INSERT INTO deployment_runs (id,idempotency_key,current_version,target_version) VALUES ('r1','idem-0001','1.41.2','1.42.0')").run();
  db.prepare("INSERT INTO deployment_events (run_id,state,event_type,message) VALUES ('r1','checking','run.created','created')").run();
  db.prepare("INSERT INTO operation_backups (id) VALUES ('b1')").run();
  db.prepare("INSERT INTO backup_verifications (id,backup_id,deployment_run_id,status) VALUES ('v1','b1','r1','verified')").run();
  db.prepare("INSERT INTO production_checks (id,overall_status) VALUES ('p1','passed')").run();

  if (db.prepare('SELECT count(*) AS count FROM deployment_events WHERE run_id = ?').get('r1').count !== 1) {
    throw new Error('Deployment-Event wurde nicht gespeichert');
  }
  if (db.prepare('PRAGMA foreign_key_check').all().length !== 0) throw new Error('SQLite-Fremdschlüsselprüfung fehlgeschlagen');
  if (db.prepare('PRAGMA integrity_check').get().integrity_check !== 'ok') throw new Error('SQLite-Integritätsprüfung fehlgeschlagen');

  console.log('Production-Operations-DB-Gate 1.42.0 erfolgreich: additive Tabellen, Fremdschlüssel und SQLite-Integrität geprüft.');
} finally {
  db.close();
  rmSync(tempRoot, { recursive: true, force: true });
}
