#!/usr/bin/env python3
import re, sqlite3, tempfile
from pathlib import Path

root = Path(__file__).resolve().parents[1]
source = (root / 'app/core/identity/identity-store.mjs').read_text(encoding='utf-8')
match = re.search(r"db\.exec\(`(.*?)`\);", source, re.S)
if not match:
    raise SystemExit('Identity-Schema-Block fehlt')
schema = match.group(1)

with tempfile.TemporaryDirectory(prefix='vendoo-identity-138-') as tmp:
    db_path = Path(tmp) / 'identity.db'
    con = sqlite3.connect(db_path)
    con.executescript('PRAGMA foreign_keys=ON;\n' + schema)
    for statement in [
        "ALTER TABLE users ADD COLUMN failed_login_count INTEGER NOT NULL DEFAULT 0",
        "ALTER TABLE users ADD COLUMN locked_until TEXT",
        "ALTER TABLE users ADD COLUMN password_changed_at TEXT",
        "ALTER TABLE sessions ADD COLUMN last_seen_at TEXT",
        "ALTER TABLE sessions ADD COLUMN revoked_at TEXT",
    ]:
        try:
            con.execute(statement)
        except sqlite3.OperationalError:
            pass
    con.execute("INSERT INTO users(email,name,role,password_hash) VALUES(?,?,?,?)", ('admin@example.invalid','Admin','admin','salt:hash'))
    con.execute("INSERT INTO users(email,name,role,password_hash) VALUES(?,?,?,?)", ('editor@example.invalid','Editor','editor','salt:hash'))
    admin_id = con.execute("SELECT id FROM users WHERE email='admin@example.invalid'").fetchone()[0]
    editor_id = con.execute("SELECT id FROM users WHERE email='editor@example.invalid'").fetchone()[0]
    con.execute("INSERT INTO sessions(user_id,token_hash,expires_at,last_seen_at) VALUES(?,?,datetime('now','+1 day'),datetime('now'))", (admin_id,'admin-token'))
    con.execute("INSERT INTO sessions(user_id,token_hash,expires_at,last_seen_at) VALUES(?,?,datetime('now','+1 day'),datetime('now'))", (editor_id,'editor-token'))
    editor_session = con.execute("SELECT id FROM sessions WHERE user_id=?", (editor_id,)).fetchone()[0]
    changed = con.execute("UPDATE sessions SET revoked_at=datetime('now') WHERE id=? AND user_id=? AND revoked_at IS NULL", (editor_session,admin_id)).rowcount
    if changed != 0:
        raise SystemExit('Fremde Sitzung konnte durch falschen Eigentümer widerrufen werden')
    changed = con.execute("UPDATE sessions SET revoked_at=datetime('now') WHERE id=? AND user_id=? AND revoked_at IS NULL", (editor_session,editor_id)).rowcount
    if changed != 1:
        raise SystemExit('Eigene Sitzung konnte nicht widerrufen werden')
    con.execute("CREATE TABLE IF NOT EXISTS settings (key TEXT PRIMARY KEY, value TEXT)")
    con.execute("INSERT OR REPLACE INTO settings(key,value) VALUES(?,?)", ('default_language','de'))
    if con.execute("SELECT value FROM settings WHERE key='default_language'").fetchone()[0] != 'de':
        raise SystemExit('Settings-Persistenz fehlgeschlagen')
    con.commit()
    tables = {r[0] for r in con.execute("SELECT name FROM sqlite_master WHERE type='table'")}
    required = {'users','sessions','auth_tokens','audit_log','login_history','settings'}
    missing = sorted(required - tables)
    if missing:
        raise SystemExit('Fehlende Identity-/Settings-Tabellen: ' + ', '.join(missing))
    print({'ok': True, 'tables': sorted(required), 'foreign_session_blocked': True, 'own_session_revoked': True})