AegisSight-Monitor-Verwaltung/migrations/migrate_2026-05-02.py

"""Migration 2026-05-02: User-Delete-Fix + unlimited_budget + Audit/Brute-Force Tabellen.

Aenderungen:
  (A) ON DELETE SET NULL fuer incidents.created_by, magic_links.user_id, network_analyses.created_by
  (B) Neue Spalte licenses.unlimited_budget (mit Backfill)
  (C) Neue Tabellen portal_audit_log, portal_login_attempts

Idempotent: jeder Schritt prueft, ob er bereits ausgefuehrt wurde.
Vorher: Backup der DB anlegen!
"""
import sqlite3
import sys

DB_PATH = "/home/claude-dev/osint-data/osint.db"


def has_on_delete_set_null(db, table, column):
    """Prueft, ob die ON DELETE SET NULL Klausel bereits gesetzt ist."""
    row = db.execute(
        "SELECT sql FROM sqlite_master WHERE type='table' AND name=?", (table,)
    ).fetchone()
    if not row:
        return False
    sql = row[0]
    return "ON DELETE SET NULL" in sql.upper() and column in sql


def column_exists(db, table, column):
    cols = [r[1] for r in db.execute("PRAGMA table_info(" + table + ")").fetchall()]
    return column in cols


def table_exists(db, name):
    return db.execute(
        "SELECT 1 FROM sqlite_master WHERE type='table' AND name=?", (name,)
    ).fetchone() is not None


def recreate_table(db, name, new_create_sql, indexes):
    """Recreate-Pattern: alte Spalten in neue Tabelle kopieren."""
    print("  - Recreate " + name + "...")
    cols_old = [r[1] for r in db.execute("PRAGMA table_info(" + name + ")").fetchall()]
    db.execute(new_create_sql.replace("TABLE " + name, "TABLE " + name + "_new"))
    cols_new = [r[1] for r in db.execute("PRAGMA table_info(" + name + "_new)").fetchall()]
    common = [c for c in cols_old if c in cols_new]
    cols_csv = ", ".join(common)
    db.execute("INSERT INTO " + name + "_new (" + cols_csv + ") SELECT " + cols_csv + " FROM " + name)
    db.execute("DROP TABLE " + name)
    db.execute("ALTER TABLE " + name + "_new RENAME TO " + name)
    for idx_sql in indexes:
        db.execute(idx_sql)


def main():
    db = sqlite3.connect(DB_PATH)
    db.row_factory = sqlite3.Row

    counts_before = {}
    for t in ["incidents", "magic_links", "network_analyses", "licenses",
              "users", "organizations", "sources"]:
        counts_before[t] = db.execute("SELECT COUNT(*) FROM " + t).fetchone()[0]
    print("Counts vor Migration:")
    for t, n in counts_before.items():
        print("  " + t + ": " + str(n))
    print()

    db.execute("PRAGMA foreign_keys=OFF")
    db.execute("BEGIN")

    try:
        print("(A) ON DELETE SET NULL fuer 3 Tabellen")

        if not has_on_delete_set_null(db, "incidents", "created_by"):
            recreate_table(
                db, "incidents",
                """CREATE TABLE incidents (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    description TEXT,
    status TEXT DEFAULT 'active',
    refresh_mode TEXT DEFAULT 'manual',
    refresh_interval INTEGER DEFAULT 15,
    retention_days INTEGER DEFAULT 0,
    summary TEXT,
    created_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    type TEXT DEFAULT 'adhoc',
    sources_json TEXT,
    international_sources INTEGER DEFAULT 1,
    visibility TEXT DEFAULT 'public',
    tenant_id INTEGER REFERENCES organizations(id),
    notify_email_summary INTEGER DEFAULT 0,
    notify_email_contradiction INTEGER DEFAULT 0,
    notify_email_status_change INTEGER DEFAULT 0,
    include_telegram INTEGER DEFAULT 0,
    telegram_categories TEXT DEFAULT NULL,
    category_labels TEXT,
    executive_summary TEXT,
    refresh_start_time TEXT,
    latest_developments TEXT
)""",
                [
                    "CREATE INDEX idx_incidents_tenant_status ON incidents(tenant_id, status)",
                ],
            )
        else:
            print("  - incidents: bereits ON DELETE SET NULL, skip")

        if not has_on_delete_set_null(db, "magic_links", "user_id"):
            recreate_table(
                db, "magic_links",
                """CREATE TABLE magic_links (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    email TEXT NOT NULL,
    token TEXT UNIQUE NOT NULL,
    code TEXT NOT NULL,
    purpose TEXT NOT NULL DEFAULT 'login',
    user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
    is_used INTEGER DEFAULT 0,
    expires_at TIMESTAMP NOT NULL,
    ip_address TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)""",
                [],
            )
        else:
            print("  - magic_links: bereits ON DELETE SET NULL, skip")

        if not has_on_delete_set_null(db, "network_analyses", "created_by"):
            recreate_table(
                db, "network_analyses",
                """CREATE TABLE network_analyses (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    status TEXT DEFAULT 'pending',
    entity_count INTEGER DEFAULT 0,
    relation_count INTEGER DEFAULT 0,
    data_hash TEXT,
    last_generated_at TIMESTAMP,
    tenant_id INTEGER REFERENCES organizations(id),
    created_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)""",
                [],
            )
        else:
            print("  - network_analyses: bereits ON DELETE SET NULL, skip")

        print("")
        print("(B) licenses.unlimited_budget Spalte")
        if not column_exists(db, "licenses", "unlimited_budget"):
            db.execute("ALTER TABLE licenses ADD COLUMN unlimited_budget INTEGER DEFAULT 0")
            print("  - Spalte angelegt")
            res = db.execute(
                "UPDATE licenses SET unlimited_budget=1 "
                "WHERE credits_total IS NULL OR cost_per_credit IS NULL OR cost_per_credit=0"
            )
            print("  - Backfill: " + str(res.rowcount) + " Lizenzen auf unlimited gesetzt")
        else:
            print("  - bereits vorhanden, skip")

        print("")
        print("(C) portal_audit_log + portal_login_attempts")
        if not table_exists(db, "portal_audit_log"):
            db.execute("""
                CREATE TABLE portal_audit_log (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    admin_id INTEGER,
                    admin_username TEXT,
                    ip TEXT,
                    action TEXT NOT NULL,
                    resource_type TEXT,
                    resource_id INTEGER,
                    before_json TEXT,
                    after_json TEXT
                )
            """)
            db.execute("CREATE INDEX idx_audit_ts ON portal_audit_log(ts DESC)")
            db.execute("CREATE INDEX idx_audit_admin ON portal_audit_log(admin_id)")
            db.execute("CREATE INDEX idx_audit_resource ON portal_audit_log(resource_type, resource_id)")
            print("  - portal_audit_log angelegt")
        else:
            print("  - portal_audit_log bereits vorhanden, skip")

        if not table_exists(db, "portal_login_attempts"):
            db.execute("""
                CREATE TABLE portal_login_attempts (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    ip TEXT NOT NULL,
                    username TEXT NOT NULL,
                    success INTEGER NOT NULL DEFAULT 0
                )
            """)
            db.execute("CREATE INDEX idx_login_lookup ON portal_login_attempts(ip, username, ts)")
            print("  - portal_login_attempts angelegt")
        else:
            print("  - portal_login_attempts bereits vorhanden, skip")

        db.execute("COMMIT")
        db.execute("PRAGMA foreign_keys=ON")

        print("")
        print("=== Verifikation ===")
        ok = True
        for t, n in counts_before.items():
            n_after = db.execute("SELECT COUNT(*) FROM " + t).fetchone()[0]
            status = "OK" if n_after == n else "MISMATCH (" + str(n) + " -> " + str(n_after) + ")"
            print("  " + t + ": " + str(n_after) + " " + status)
            if n_after != n:
                ok = False
        for t, c in [("incidents", "created_by"), ("magic_links", "user_id"),
                     ("network_analyses", "created_by")]:
            on_del = has_on_delete_set_null(db, t, c)
            print("  " + t + "." + c + " ON DELETE SET NULL: " + str(on_del))
            if not on_del:
                ok = False
        col_ok = column_exists(db, "licenses", "unlimited_budget")
        print("  licenses.unlimited_budget: " + str(col_ok))
        if not col_ok:
            ok = False
        for t in ["portal_audit_log", "portal_login_attempts"]:
            t_ok = table_exists(db, t)
            print("  Tabelle " + t + ": " + str(t_ok))
            if not t_ok:
                ok = False

        if ok:
            print("")
            print("Alle Checks OK")
            return 0
        else:
            print("")
            print("FEHLER: Mindestens ein Check fehlgeschlagen")
            return 1

    except Exception as e:
        db.execute("ROLLBACK")
        db.execute("PRAGMA foreign_keys=ON")
        print("")
        print("FEHLER: " + type(e).__name__ + ": " + str(e))
        raise

    finally:
        db.close()


if __name__ == "__main__":
    sys.exit(main())