from pathlib import Path
import sqlite3, tempfile

root = Path(__file__).resolve().parents[1]
with tempfile.TemporaryDirectory() as temp:
    db_path = Path(temp) / 'vendoo.db'
    con = sqlite3.connect(db_path)
    con.execute("CREATE TABLE listings (id INTEGER PRIMARY KEY, title TEXT, platform TEXT, ai_provider TEXT)")
    con.execute("INSERT INTO listings VALUES (1,'Bestehender Artikel','vinted','openrouter')")
    for col, typ in [('created_by','INTEGER'),('updated_by','INTEGER')]:
        try: con.execute(f'ALTER TABLE listings ADD COLUMN {col} {typ}')
        except sqlite3.OperationalError: pass
    con.executescript('''
      CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT, email TEXT UNIQUE NOT NULL, name TEXT NOT NULL DEFAULT '',
        password_hash TEXT, role TEXT NOT NULL DEFAULT 'editor', active INTEGER NOT NULL DEFAULT 1,
        failed_login_count INTEGER NOT NULL DEFAULT 0, locked_until TEXT, password_changed_at TEXT,
        created_at TEXT DEFAULT (datetime('now'))
      );
      CREATE TABLE IF NOT EXISTS sessions (
        id INTEGER PRIMARY KEY AUTOINCREMENT, user_id INTEGER NOT NULL, token_hash TEXT UNIQUE NOT NULL,
        expires_at TEXT NOT NULL, last_seen_at TEXT, revoked_at TEXT, created_at TEXT DEFAULT (datetime('now'))
      );
      CREATE TABLE IF NOT EXISTS audit_log (
        id INTEGER PRIMARY KEY AUTOINCREMENT, user_id INTEGER, user_email TEXT, action TEXT NOT NULL,
        target_type TEXT, target_id TEXT, details TEXT, ip TEXT, created_at TEXT DEFAULT (datetime('now'))
      );
      CREATE TABLE IF NOT EXISTS resource_locks (
        id INTEGER PRIMARY KEY AUTOINCREMENT, resource_type TEXT NOT NULL, resource_id TEXT NOT NULL,
        user_id INTEGER NOT NULL, token_hash TEXT NOT NULL UNIQUE, acquired_at TEXT DEFAULT (datetime('now')),
        renewed_at TEXT DEFAULT (datetime('now')), expires_at TEXT NOT NULL, client_label TEXT,
        UNIQUE(resource_type, resource_id)
      );
    ''')
    con.execute("INSERT INTO users(email,name,role) VALUES ('admin@example.test','Admin','admin')")
    uid = con.execute("SELECT id FROM users").fetchone()[0]
    con.execute("UPDATE listings SET created_by=?, updated_by=? WHERE id=1", (uid, uid))
    con.execute("INSERT INTO resource_locks(resource_type,resource_id,user_id,token_hash,expires_at) VALUES ('listing','1',?,'hash',datetime('now','+2 minutes'))", (uid,))
    con.commit()
    assert con.execute("PRAGMA integrity_check").fetchone()[0] == 'ok'
    assert con.execute("SELECT title,platform,ai_provider FROM listings WHERE id=1").fetchone() == ('Bestehender Artikel','vinted','openrouter')
    assert con.execute("SELECT created_by,updated_by FROM listings WHERE id=1").fetchone() == (uid, uid)
    try:
        con.execute("INSERT INTO resource_locks(resource_type,resource_id,user_id,token_hash,expires_at) VALUES ('listing','1',?,'hash2',datetime('now','+2 minutes'))", (uid,))
        raise AssertionError('Doppelte Sperre wurde nicht blockiert')
    except sqlite3.IntegrityError:
        pass
print('SQLite-Sicherheitsmigration 1.31.0 erfolgreich: bestehender Artikel erhalten, Benutzer-/Session-/Audit-/Lock-Struktur gültig.')
