from pathlib import Path
import re
import sqlite3
import tempfile

root = Path(__file__).resolve().parent.parent
source = (root / 'lib' / 'db.mjs').read_text(encoding='utf-8')
required = ['deployment_runs', 'deployment_events', 'backup_verifications', 'production_checks']
statements = {}
for table in required:
    match = re.search(rf"db\.exec\(`(CREATE TABLE IF NOT EXISTS {re.escape(table)} \(.*?\n  \))`\);", source, re.S)
    if not match:
        raise SystemExit(f'Fehlende CREATE-TABLE-Anweisung: {table}')
    statements[table] = match.group(1)

with tempfile.NamedTemporaryFile(suffix='.db') as handle:
    con = sqlite3.connect(handle.name)
    con.execute('PRAGMA foreign_keys = ON')
    con.execute('CREATE TABLE users (id INTEGER PRIMARY KEY)')
    con.execute('CREATE TABLE operation_backups (id TEXT PRIMARY KEY)')
    for table in required:
        con.execute(statements[table])
    con.execute("INSERT INTO deployment_runs (id,idempotency_key,current_version,target_version) VALUES ('r1','idem-0001','1.41.2','1.42.0')")
    con.execute("INSERT INTO deployment_events (run_id,state,event_type,message) VALUES ('r1','checking','run.created','created')")
    con.execute("INSERT INTO operation_backups (id) VALUES ('b1')")
    con.execute("INSERT INTO backup_verifications (id,backup_id,deployment_run_id,status) VALUES ('v1','b1','r1','verified')")
    con.execute("INSERT INTO production_checks (id,overall_status) VALUES ('p1','passed')")
    assert con.execute('SELECT count(*) FROM deployment_events WHERE run_id = ?', ('r1',)).fetchone()[0] == 1
    assert con.execute('PRAGMA foreign_key_check').fetchall() == []
    assert con.execute('PRAGMA integrity_check').fetchone()[0] == 'ok'

print('Production-Operations-DB-Gate 1.42.0 erfolgreich: additive Tabellen, Fremdschlüssel und SQLite-Integrität geprüft.')