"""
licensing.py  –  EEG-Assistent · Lizenzierungs-Kern

Kapselt alle Zugriffe auf die SQLite-Datenbank `tokens.db`:
  • UID-Validierung + Session-Lock (keine Parallel-Nutzung einer UID)
  • Token-Konten + atomarer Abzug beim Download
  • Nutzungsprotokoll (IP, Fingerprint, User-Agent, Modul-Auswahl)
  • Admin-Funktionen (Konten anlegen, Tokens anpassen, aktivieren)
  • Rate-Limit für Login-Versuche
  • Automatisches tägliches Backup nach tokens_backups/
"""

from __future__ import annotations

import hmac
import json
import logging
import os
import re
import secrets
import shutil
import sqlite3
import threading
import time
import uuid
from contextlib import contextmanager
from datetime import datetime, timedelta, timezone
from pathlib import Path
from typing import Iterable

from werkzeug.security import generate_password_hash, check_password_hash

log = logging.getLogger("licensing")

# ── Konfiguration ────────────────────────────────────────────────────────────

HERE         = Path(__file__).resolve().parent
# v1.04: DB liegt jetzt in <root>/data/, nicht mehr im Versionsordner.
# Damit ueberlebt sie Versions-Spruenge ohne Migration.
_PROJECT_ROOT = HERE.parent
_DEFAULT_DATA_DIR = _PROJECT_ROOT / "data"
if not os.environ.get("ABRECHNUNG_LICENSE_DB"):
    _DEFAULT_DATA_DIR.mkdir(exist_ok=True)
DB_PATH      = Path(os.environ.get("ABRECHNUNG_LICENSE_DB",
                                   str(_DEFAULT_DATA_DIR / "tokens.db")))
BACKUP_DIR   = Path(os.environ.get("ABRECHNUNG_LICENSE_BACKUP_DIR",
                                   str(_DEFAULT_DATA_DIR / "tokens_backups")))
SCHEMA_PATH  = HERE / "licensing_schema.sql"

VALIDITY_DAYS         = 3 * 365                 # 3 Jahre ab Erstnutzung
SESSION_TTL_SECONDS   = 2 * 3600                # 2h, danach gilt Lock als tot
LOGIN_RATE_WINDOW_S   = 3600                    # 1 Stunde
LOGIN_RATE_MAX_FAILS  = 10                      # max. Fehlversuche pro IP im Fenster
BACKUP_KEEP_DAYS      = 30
BACKUP_INTERVAL_SEC   = 24 * 3600

# UID-Format: EEGA-XXXX-XXXX-XXXX (Hex uppercase)
_UID_RE     = re.compile(r"^EEGA-[0-9A-F]{4}-[0-9A-F]{4}-[0-9A-F]{4}$")
_PREFIX     = "EEGA"

# Thread-lokale Connections, damit SQLite nicht zickt
_tls = threading.local()


# ── DB-Connection / Schema ────────────────────────────────────────────────────

def _connect() -> sqlite3.Connection:
    """Liefert eine thread-lokale Connection mit Row-Factory + Foreign-Keys."""
    conn = getattr(_tls, "conn", None)
    if conn is None:
        DB_PATH.parent.mkdir(parents=True, exist_ok=True)
        conn = sqlite3.connect(str(DB_PATH), timeout=10, isolation_level=None)
        conn.row_factory = sqlite3.Row
        conn.execute("PRAGMA foreign_keys = ON")
        conn.execute("PRAGMA journal_mode = WAL")
        conn.execute("PRAGMA synchronous  = NORMAL")
        _tls.conn = conn
    return conn


@contextmanager
def _tx():
    """Kontext-Manager für eine exklusive Transaktion (IMMEDIATE-Lock)."""
    c = _connect()
    c.execute("BEGIN IMMEDIATE")
    try:
        yield c
        c.execute("COMMIT")
    except Exception:
        c.execute("ROLLBACK")
        raise


def init_db() -> None:
    """Legt Schema + Default-Pakete an (idempotent)."""
    c = _connect()
    if not SCHEMA_PATH.exists():
        raise RuntimeError(f"Schema-Datei fehlt: {SCHEMA_PATH}")
    with open(SCHEMA_PATH, "r", encoding="utf-8") as f:
        c.executescript(f.read())
    log.info(f"DB initialisiert: {DB_PATH}")


# ── Hilfsfunktionen ──────────────────────────────────────────────────────────

def _now() -> str:
    """ISO-8601 UTC-Timestamp mit Sekundenauflösung."""
    return datetime.now(timezone.utc).replace(microsecond=0).isoformat()


def _parse_ts(ts: str | None) -> datetime | None:
    if not ts:
        return None
    try:
        return datetime.fromisoformat(ts)
    except Exception:
        return None


def _valid_uid_format(uid: str | None) -> bool:
    return isinstance(uid, str) and bool(_UID_RE.match(uid.strip().upper()))


def _normalize_uid(uid: str) -> str:
    """Trimmt und uppercase'd; Server-seitig wird damit verglichen."""
    return (uid or "").strip().upper()


def generate_uid() -> str:
    """Neue UID im Format EEGA-XXXX-XXXX-XXXX (12 Hex-Zeichen = 48 Bit)."""
    raw = secrets.token_hex(6).upper()     # 12 hex chars
    return f"{_PREFIX}-{raw[0:4]}-{raw[4:8]}-{raw[8:12]}"


def constant_time_eq(a: str, b: str) -> bool:
    """Konstante Zeit für UID-Vergleich — beugt Timing-Angriffen vor."""
    return hmac.compare_digest(a.encode("utf-8"), b.encode("utf-8"))


# ── Public API: UID-Prüfung + Login ──────────────────────────────────────────

class LicenseError(Exception):
    """Wird geworfen, wenn eine UID abgelehnt werden muss. .reason ist ein
    stabiler String-Code für die UI ('invalid', 'disabled', 'expired',
    'locked', 'rate_limited')."""
    def __init__(self, reason: str, msg: str):
        super().__init__(msg)
        self.reason = reason


def check_rate_limit(ip: str) -> None:
    """Wirft LicenseError('rate_limited'), wenn die IP zu oft daneben lag."""
    if not ip:
        return
    c = _connect()
    cutoff = (datetime.now(timezone.utc)
              - timedelta(seconds=LOGIN_RATE_WINDOW_S)).isoformat()
    row = c.execute(
        "SELECT COUNT(*) AS n FROM failed_logins WHERE ip = ? AND timestamp > ?",
        (ip, cutoff),
    ).fetchone()
    if row and row["n"] >= LOGIN_RATE_MAX_FAILS:
        raise LicenseError("rate_limited",
                           f"Zu viele Fehlversuche von {ip} — bitte später erneut.")


def _record_failed_login(ip: str, uid_attempted: str | None, reason: str) -> None:
    c = _connect()
    c.execute(
        "INSERT INTO failed_logins (ip, uid_attempted, timestamp, reason) "
        "VALUES (?, ?, ?, ?)",
        (ip or "", (uid_attempted or "")[:40], _now(), reason),
    )


def _cleanup_stale_sessions() -> None:
    """Entfernt Session-Locks, deren TTL abgelaufen ist."""
    c = _connect()
    c.execute("DELETE FROM active_sessions WHERE expires_at < ?", (_now(),))


def login(uid_raw: str, fingerprint: str, ip: str, user_agent: str) -> dict:
    """Prüft UID und legt eine neue Session an. Gibt dict mit allen Daten
    zurück, die das Frontend für die Header-Anzeige braucht.

    Raises LicenseError mit .reason in:
      'invalid'       – Format falsch oder UID unbekannt
      'disabled'      – Admin hat deaktiviert
      'expired'       – Ablaufdatum überschritten
      'locked'        – Auf einem anderen Gerät aktiv (keine Parallel-Nutzung)
      'rate_limited'  – zu viele Fehlversuche von dieser IP
    """
    check_rate_limit(ip)
    _cleanup_stale_sessions()

    uid = _normalize_uid(uid_raw or "")
    fingerprint = (fingerprint or "").strip()[:128]   # begrenzen

    if not _valid_uid_format(uid):
        _record_failed_login(ip, uid, "invalid")
        raise LicenseError("invalid", "Ungültiges Code-Format.")

    with _tx() as c:
        row = c.execute("SELECT * FROM accounts WHERE uid = ?", (uid,)).fetchone()
        if row is None:
            _record_failed_login(ip, uid, "invalid")
            raise LicenseError("invalid", "Code nicht bekannt.")

        if row["status"] == "disabled":
            _record_failed_login(ip, uid, "disabled")
            raise LicenseError("disabled", "Code wurde deaktiviert.")

        # Ablauf prüfen (falls schon erstgenutzt)
        exp = _parse_ts(row["expires_at"])
        if exp and datetime.now(timezone.utc) > exp:
            c.execute("UPDATE accounts SET status='expired' WHERE uid=?", (uid,))
            _record_failed_login(ip, uid, "expired")
            raise LicenseError("expired", "Code ist abgelaufen.")

        # Parallel-Nutzung blockieren
        lock = c.execute(
            "SELECT * FROM active_sessions WHERE uid = ? AND expires_at > ?",
            (uid, _now()),
        ).fetchone()
        if lock is not None:
            _record_failed_login(ip, uid, "locked")
            raise LicenseError("locked",
                               "Dieser Code ist gerade auf einem anderen Gerät aktiv.")

        # Fingerprint-Match auswerten (nur als Vermerk)
        match_flag: int | None = None
        if row["initial_fingerprint"]:
            match_flag = 1 if fingerprint and fingerprint == row["initial_fingerprint"] else 0
            if match_flag == 0:
                c.execute(
                    "UPDATE accounts SET fingerprint_mismatches = fingerprint_mismatches + 1 "
                    "WHERE uid = ?", (uid,),
                )
        else:
            # Erster Login → Fingerprint als Baseline speichern
            if fingerprint:
                c.execute(
                    "UPDATE accounts SET initial_fingerprint = ? WHERE uid = ?",
                    (fingerprint, uid),
                )

        # Neue Session anlegen
        session_id = uuid.uuid4().hex
        started    = _now()
        expires    = (datetime.now(timezone.utc)
                      + timedelta(seconds=SESSION_TTL_SECONDS)).replace(microsecond=0).isoformat()
        c.execute(
            "INSERT INTO active_sessions (uid, session_id, started_at, expires_at, ip, fingerprint_hash) "
            "VALUES (?, ?, ?, ?, ?, ?)",
            (uid, session_id, started, expires, ip or "", fingerprint or ""),
        )

        # Login-Event ins Nutzungsprotokoll
        c.execute(
            "INSERT INTO usage_log (account_uid, timestamp, ip, user_agent, "
            "fingerprint_hash, fingerprint_match, session_id, periode, modules, "
            "tokens_consumed, remaining_after, event_type) "
            "VALUES (?, ?, ?, ?, ?, ?, ?, NULL, NULL, 0, ?, 'login')",
            (uid, started, ip or "", (user_agent or "")[:500],
             fingerprint or "", match_flag, session_id, row["remaining_tokens"]),
        )

        # Frisch laden (falls status geupdatet)
        row = c.execute("SELECT * FROM accounts WHERE uid = ?", (uid,)).fetchone()

    return _account_public_view(row, session_id)


def _account_public_view(row: sqlite3.Row, session_id: str | None = None) -> dict:
    """Das, was das Frontend vom Konto sehen darf — KEIN Fingerprint, keine
    Notizen, keine IP-Historie."""
    uid = row["uid"]
    masked = f"{uid[:4]}-{uid[5:9]}-…-{uid[-4:]}"
    return {
        "session_id":       session_id,
        "uid_masked":       masked,
        "customer_name":    row["customer_name"],
        "remaining_tokens": row["remaining_tokens"],
        "expires_at":       row["expires_at"],
        "first_used_at":    row["first_used_at"],
        "status":           row["status"],
    }


def logout(session_id: str) -> None:
    """Session-Lock freigeben."""
    if not session_id:
        return
    c = _connect()
    c.execute("DELETE FROM active_sessions WHERE session_id = ?", (session_id,))


def get_session(session_id: str) -> dict | None:
    """Liefert den aktuellen Session-State oder None. Verlängert das TTL."""
    if not session_id:
        return None
    _cleanup_stale_sessions()
    c = _connect()
    row = c.execute(
        "SELECT a.* FROM accounts a "
        "JOIN active_sessions s ON s.uid = a.uid "
        "WHERE s.session_id = ? AND s.expires_at > ?",
        (session_id, _now()),
    ).fetchone()
    if row is None:
        return None
    # TTL verlängern (Sliding-Window)
    new_exp = (datetime.now(timezone.utc)
               + timedelta(seconds=SESSION_TTL_SECONDS)).replace(microsecond=0).isoformat()
    c.execute("UPDATE active_sessions SET expires_at = ? WHERE session_id = ?",
              (new_exp, session_id))
    return _account_public_view(row, session_id)


# ── Pakete / Preise ──────────────────────────────────────────────────────────

def list_packages(only_active: bool = True) -> list[dict]:
    c = _connect()
    sql = "SELECT key, name, cost, is_active, sort_order, description FROM packages"
    if only_active:
        sql += " WHERE is_active = 1"
    sql += " ORDER BY sort_order, key"
    return [dict(r) for r in c.execute(sql).fetchall()]


def update_package(key: str, cost: int | None = None,
                   is_active: bool | None = None,
                   admin_user: str = "system") -> dict:
    """Admin-API: Preis und/oder Aktiv-Flag eines Pakets anpassen.

    ``cost`` muss >= 0 sein, ``is_active`` wird auf 0/1 normalisiert. Änderungen
    landen im admin_audit-Log. Gibt das aktualisierte Paket zurück.
    """
    key = (key or "").strip().lower()
    if not key:
        raise ValueError("Paket-Key fehlt.")
    if cost is None and is_active is None:
        raise ValueError("Mindestens cost oder is_active muss angegeben werden.")
    with _tx() as c:
        row = c.execute("SELECT * FROM packages WHERE key = ?", (key,)).fetchone()
        if row is None:
            raise ValueError(f"Paket '{key}' nicht gefunden.")
        new_cost   = int(cost) if cost is not None else row["cost"]
        if new_cost < 0:
            raise ValueError("cost muss >= 0 sein.")
        new_active = int(bool(is_active)) if is_active is not None else row["is_active"]
        c.execute(
            "UPDATE packages SET cost = ?, is_active = ? WHERE key = ?",
            (new_cost, new_active, key),
        )
        _admin_audit(admin_user, "update_package", None,
                     json.dumps({"key": key,
                                 "old_cost": row["cost"],
                                 "new_cost": new_cost,
                                 "old_active": row["is_active"],
                                 "new_active": new_active}))
        updated = c.execute(
            "SELECT key, name, cost, is_active, sort_order, description "
            "FROM packages WHERE key = ?", (key,)
        ).fetchone()
        return dict(updated)


def list_admin_audit(limit: int = 200) -> list[dict]:
    """Admin-Audit-Log für das Dashboard."""
    c = _connect()
    rows = c.execute(
        "SELECT id, admin_user, timestamp, action, target_uid, details "
        "FROM admin_audit ORDER BY timestamp DESC LIMIT ?",
        (int(limit),),
    ).fetchall()
    return [dict(r) for r in rows]


def get_package_costs(module_keys: Iterable[str]) -> dict[str, int]:
    """Liefert {key: cost} für die angegebenen Keys (nur aktive Pakete)."""
    keys = list({k for k in module_keys if k})
    if not keys:
        return {}
    c = _connect()
    placeholder = ",".join("?" for _ in keys)
    rows = c.execute(
        f"SELECT key, cost FROM packages WHERE is_active = 1 AND key IN ({placeholder})",
        keys,
    ).fetchall()
    return {r["key"]: r["cost"] for r in rows}


def total_cost(module_keys: Iterable[str]) -> int:
    return sum(get_package_costs(module_keys).values())


# ── Atomischer Token-Abzug (beim Download) ───────────────────────────────────

def consume_tokens(
    session_id: str,
    modules: list[str],
    ip: str,
    fingerprint: str,
    user_agent: str,
    periode: str,
) -> dict:
    """Wird direkt vor dem send_file() in /api/v1/download aufgerufen.

    Führt in EINER Transaktion durch:
      1) Session validieren + Konto laden
      2) Status prüfen (active, nicht abgelaufen, Tokens reichen)
      3) Tokens abziehen, first_used_at/expires_at setzen falls NULL
      4) usage_log-Eintrag schreiben
      5) Session-Lock freigeben (Download beendet die aktive Verarbeitung)

    Raises LicenseError bei allen Fehlern. Liefert dict mit den neuen
    Kontostand-Daten bei Erfolg.
    """
    if not session_id:
        raise LicenseError("invalid", "Keine Session.")

    modules = sorted({m for m in (modules or []) if m})
    costs   = get_package_costs(modules)
    unknown = [m for m in modules if m not in costs]
    if unknown:
        raise LicenseError("invalid", f"Unbekanntes Paket: {unknown[0]}")

    amount = sum(costs.values())

    with _tx() as c:
        row = c.execute(
            "SELECT a.* FROM accounts a "
            "JOIN active_sessions s ON s.uid = a.uid "
            "WHERE s.session_id = ? AND s.expires_at > ?",
            (session_id, _now()),
        ).fetchone()
        if row is None:
            raise LicenseError("invalid", "Session ist abgelaufen oder unbekannt.")

        if row["status"] != "active":
            raise LicenseError(row["status"], f"Konto-Status: {row['status']}.")

        # Ablauf prüfen
        exp = _parse_ts(row["expires_at"])
        if exp and datetime.now(timezone.utc) > exp:
            c.execute("UPDATE accounts SET status='expired' WHERE uid=?",
                      (row["uid"],))
            raise LicenseError("expired", "Code ist abgelaufen.")

        if row["remaining_tokens"] < amount:
            raise LicenseError("no_tokens",
                               f"Nicht genug Tokens: {row['remaining_tokens']} "
                               f"verfügbar, {amount} nötig.")

        new_balance = row["remaining_tokens"] - amount
        now_iso     = _now()

        # first_used_at / expires_at einmalig setzen
        if not row["first_used_at"]:
            new_expiry = (datetime.now(timezone.utc)
                          + timedelta(days=VALIDITY_DAYS)).replace(microsecond=0).isoformat()
            c.execute(
                "UPDATE accounts SET remaining_tokens=?, first_used_at=?, expires_at=? "
                "WHERE uid=?",
                (new_balance, now_iso, new_expiry, row["uid"]),
            )
            expiry_to_return = new_expiry
        else:
            c.execute(
                "UPDATE accounts SET remaining_tokens=? WHERE uid=?",
                (new_balance, row["uid"]),
            )
            expiry_to_return = row["expires_at"]

        # Fingerprint-Match dokumentieren
        match_flag: int | None = None
        if row["initial_fingerprint"] and fingerprint:
            match_flag = 1 if fingerprint == row["initial_fingerprint"] else 0
            if match_flag == 0:
                c.execute(
                    "UPDATE accounts SET fingerprint_mismatches = fingerprint_mismatches + 1 "
                    "WHERE uid=?", (row["uid"],),
                )

        c.execute(
            "INSERT INTO usage_log (account_uid, timestamp, ip, user_agent, "
            "fingerprint_hash, fingerprint_match, session_id, periode, modules, "
            "tokens_consumed, remaining_after, event_type) "
            "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'download')",
            (row["uid"], now_iso, ip or "", (user_agent or "")[:500],
             fingerprint or "", match_flag, session_id,
             periode or "", json.dumps(modules),
             amount, new_balance),
        )

        # Session-Lock freigeben (Download ist der letzte Schritt)
        c.execute("DELETE FROM active_sessions WHERE session_id=?", (session_id,))

        return {
            "uid_masked":       f"{row['uid'][:4]}-{row['uid'][5:9]}-…-{row['uid'][-4:]}",
            "customer_name":    row["customer_name"],
            "tokens_consumed":  amount,
            "remaining_tokens": new_balance,
            "expires_at":       expiry_to_return,
        }


# ── Admin-Funktionen (von admin_cli.py + Phase-6-Web-UI genutzt) ─────────────

def create_admin(username: str, password: str) -> None:
    if not username or not password or len(password) < 8:
        raise ValueError("Username + Passwort (min. 8 Zeichen) erforderlich.")
    c = _connect()
    existing = c.execute("SELECT 1 FROM admins WHERE username=?", (username,)).fetchone()
    if existing:
        raise ValueError(f"Admin '{username}' existiert bereits.")
    c.execute(
        "INSERT INTO admins (username, pw_hash, created_at) VALUES (?, ?, ?)",
        (username, generate_password_hash(password), _now()),
    )


def verify_admin(username: str, password: str, ip: str = "") -> bool:
    c = _connect()
    row = c.execute("SELECT pw_hash FROM admins WHERE username=?", (username,)).fetchone()
    if not row:
        # Dummy-Check gegen Timing-Analyse
        check_password_hash(
            "pbkdf2:sha256:600000$dummy$0000000000000000000000000000000000000000000000000000000000000000",
            password or "",
        )
        return False
    ok = check_password_hash(row["pw_hash"], password or "")
    if ok:
        c.execute(
            "UPDATE admins SET last_login_at=?, last_login_ip=? WHERE username=?",
            (_now(), ip or "", username),
        )
    return ok


def _admin_audit(admin_user: str, action: str, target_uid: str | None = None,
                 details: str | None = None) -> None:
    c = _connect()
    c.execute(
        "INSERT INTO admin_audit (admin_user, timestamp, action, target_uid, details) "
        "VALUES (?, ?, ?, ?, ?)",
        (admin_user, _now(), action, target_uid, details),
    )


def create_account(customer_name: str, customer_email: str | None,
                   device_name: str | None, initial_tokens: int,
                   notes: str | None = None, admin_user: str = "system") -> str:
    if not customer_name:
        raise ValueError("customer_name ist Pflicht.")
    if initial_tokens is None or initial_tokens < 0:
        raise ValueError("initial_tokens muss >= 0 sein.")
    uid = generate_uid()
    with _tx() as c:
        # Kollision extrem unwahrscheinlich, aber trotzdem abfragen
        while c.execute("SELECT 1 FROM accounts WHERE uid=?", (uid,)).fetchone():
            uid = generate_uid()
        c.execute(
            "INSERT INTO accounts (uid, customer_name, customer_email, device_name, "
            "initial_tokens, remaining_tokens, created_at, notes) "
            "VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
            (uid, customer_name, customer_email or None, device_name or None,
             int(initial_tokens), int(initial_tokens), _now(), notes or None),
        )
        _admin_audit(admin_user, "create_account", uid,
                     json.dumps({"tokens": initial_tokens,
                                "customer": customer_name,
                                "device": device_name}))
    return uid


# ── v1.04: High-Level-Helper fuer das Onboarding-Tool ──────────────────────

GRUENDUNG_TOKENS = 52   # 2 x (pdfs=16 + sepa=8 + kassabuch=2) - Stand 2026-04-29


def create_gruendungs_account(customer_name: str,
                              customer_email: str | None = None) -> str:
    """Legt einen Account mit dem Inklusiv-Paket der Vereins-Gruendung an.

    Wird vom Onboarding-Tool nach erfolgreicher Bezahlung aufgerufen.
    Token-Anzahl ist hardcoded (GRUENDUNG_TOKENS = 52), damit die Inklusiv-
    Menge nicht versehentlich ueber die packages-Tabelle verschoben wird.

    Rueckgabe: die generierte UID (Format EEGA-XXXX-XXXX-XXXX).
    """
    return create_account(
        customer_name=customer_name,
        customer_email=customer_email,
        device_name=None,
        initial_tokens=GRUENDUNG_TOKENS,
        notes=f"Inklusiv-Paket Gruendung (auto, {GRUENDUNG_TOKENS} Tokens)",
        admin_user="onboarding",
    )

def adjust_tokens(uid: str, delta: int, reason: str,
                  admin_user: str = "system") -> dict:
    """Ändert den Token-Stand um +/- delta. Darf nicht ins Negative führen."""
    uid = _normalize_uid(uid)
    if not _valid_uid_format(uid):
        raise ValueError("Ungültiges UID-Format.")
    with _tx() as c:
        row = c.execute("SELECT * FROM accounts WHERE uid=?", (uid,)).fetchone()
        if row is None:
            raise ValueError(f"UID {uid} nicht gefunden.")
        new_balance = row["remaining_tokens"] + int(delta)
        if new_balance < 0:
            raise ValueError(f"Anpassung würde Saldo negativ machen "
                             f"(aktuell {row['remaining_tokens']}, delta {delta}).")
        c.execute("UPDATE accounts SET remaining_tokens=? WHERE uid=?",
                  (new_balance, uid))
        # Event im Usage-Log (damit der Verlauf vollständig bleibt)
        c.execute(
            "INSERT INTO usage_log (account_uid, timestamp, tokens_consumed, "
            "remaining_after, event_type, modules) "
            "VALUES (?, ?, ?, ?, 'adjust', ?)",
            (uid, _now(), -int(delta), new_balance,
             json.dumps({"reason": reason, "by": admin_user})),
        )
        _admin_audit(admin_user, "adjust_tokens", uid,
                     json.dumps({"delta": delta, "reason": reason,
                                 "new_balance": new_balance}))
        return {"uid": uid, "new_balance": new_balance}


def set_status(uid: str, status: str, admin_user: str = "system") -> None:
    if status not in ("active", "disabled", "expired"):
        raise ValueError("Status muss active/disabled/expired sein.")
    uid = _normalize_uid(uid)
    with _tx() as c:
        r = c.execute("UPDATE accounts SET status=? WHERE uid=?",
                      (status, uid))
        if r.rowcount == 0:
            raise ValueError(f"UID {uid} nicht gefunden.")
        # Bei Deaktivierung Session-Lock mit killen
        if status != "active":
            c.execute("DELETE FROM active_sessions WHERE uid=?", (uid,))
        _admin_audit(admin_user, f"set_status:{status}", uid)


def get_lock_expires(uid: str) -> str | None:
    """Gibt das expires_at der aktiven Session für uid zurück, oder None."""
    uid = _normalize_uid(uid)
    c = _connect()
    row = c.execute(
        "SELECT expires_at FROM active_sessions WHERE uid = ? AND expires_at > ? "
        "ORDER BY expires_at DESC LIMIT 1",
        (uid, _now()),
    ).fetchone()
    return row["expires_at"] if row else None


def unlock_device(uid: str, admin_user: str = "system") -> int:
    """Löscht alle aktiven Sessions für uid (entsperrt das Gerät).
    Gibt die Anzahl der gelöschten Sessions zurück."""
    uid = _normalize_uid(uid)
    if not _valid_uid_format(uid):
        raise ValueError(f"Ungültige UID: {uid}")
    c = _connect()
    row = c.execute("SELECT uid FROM accounts WHERE uid = ?", (uid,)).fetchone()
    if row is None:
        raise ValueError(f"Konto {uid} nicht gefunden.")
    deleted = c.execute("DELETE FROM active_sessions WHERE uid = ?", (uid,)).rowcount
    _admin_audit(admin_user, "unlock_device", uid,
                 json.dumps({"deleted_sessions": deleted}))
    log.info(f"Admin {admin_user}: Device-Lock für {uid} aufgehoben "
             f"({deleted} Session(s) gelöscht)")
    return deleted


def list_accounts(include_disabled: bool = True) -> list[dict]:
    c = _connect()
    now = _now()
    # lock_expires_at: falls eine aktive Session existiert, deren Ablaufzeit mitliefern
    sql = (
        "SELECT a.uid, a.customer_name, a.customer_email, a.device_name, a.initial_tokens, "
        "a.remaining_tokens, a.created_at, a.first_used_at, a.expires_at, a.status, "
        "a.fingerprint_mismatches, a.notes, "
        "(SELECT s.expires_at FROM active_sessions s "
        " WHERE s.uid = a.uid AND s.expires_at > ? "
        " ORDER BY s.expires_at DESC LIMIT 1) AS lock_expires_at "
        "FROM accounts a "
    )
    params = [now]
    if not include_disabled:
        sql += "WHERE a.status != 'disabled' "
    sql += "ORDER BY a.created_at DESC"
    return [dict(r) for r in c.execute(sql, params).fetchall()]


def get_usage_log(uid: str | None = None, limit: int = 200) -> list[dict]:
    c = _connect()
    if uid:
        rows = c.execute(
            "SELECT * FROM usage_log WHERE account_uid = ? "
            "ORDER BY timestamp DESC LIMIT ?",
            (_normalize_uid(uid), int(limit)),
        ).fetchall()
    else:
        rows = c.execute(
            "SELECT * FROM usage_log ORDER BY timestamp DESC LIMIT ?",
            (int(limit),),
        ).fetchall()
    return [dict(r) for r in rows]


# ── DSGVO: Recht auf Auskunft (Art. 15) + Recht auf Löschung (Art. 17) ───────

def export_account_data(uid: str) -> dict:
    """Liefert alle zu einem Konto gespeicherten Daten als Dict.

    Inkl. Stammdaten + komplettem Nutzungsprotokoll. Ziel: Auskunftsersuchen
    nach DSGVO Art. 15 vollständig beantworten zu können (JSON-Export).
    """
    uid = _normalize_uid(uid)
    c = _connect()
    row = c.execute(
        "SELECT uid, customer_name, customer_email, device_name, "
        "initial_tokens, remaining_tokens, created_at, first_used_at, "
        "expires_at, status, fingerprint_mismatches, notes "
        "FROM accounts WHERE uid = ?", (uid,),
    ).fetchone()
    if row is None:
        raise ValueError(f"UID {uid} nicht gefunden.")
    usage = c.execute(
        "SELECT id, timestamp, ip, user_agent, fingerprint_hash, fingerprint_match, "
        "session_id, periode, modules, tokens_consumed, remaining_after, event_type "
        "FROM usage_log WHERE account_uid = ? ORDER BY timestamp ASC",
        (uid,),
    ).fetchall()
    return {
        "uid": uid,
        "exported_at": _now(),
        "account": dict(row),
        "usage_log": [dict(r) for r in usage],
    }


def delete_account(uid: str, admin_user: str = "system") -> dict:
    """DSGVO-konforme Löschung: Personenbezogene Felder werden anonymisiert,
    das Nutzungsprotokoll wird gelöscht, aktive Sessions werden gekillt, der
    Status auf ``disabled`` gesetzt.

    Der UID-Datensatz bleibt mit leeren Feldern bestehen, damit bereits
    ausgestellte Rechnungen / Quittungen weiterhin referenzierbar bleiben.
    Die Aktion landet im admin_audit.
    """
    uid = _normalize_uid(uid)
    with _tx() as c:
        row = c.execute("SELECT uid FROM accounts WHERE uid = ?", (uid,)).fetchone()
        if row is None:
            raise ValueError(f"UID {uid} nicht gefunden.")
        # Protokoll löschen (Art. 17) — in usage_log stehen IP + Fingerprint
        deleted = c.execute(
            "DELETE FROM usage_log WHERE account_uid = ?", (uid,),
        ).rowcount
        # Session-Lock killen
        c.execute("DELETE FROM active_sessions WHERE uid = ?", (uid,))
        # Personenbezogene Felder anonymisieren
        c.execute(
            "UPDATE accounts SET "
            "customer_name = '[gelöscht]', "
            "device_name = NULL, "
            "notes = ?, "
            "status = 'disabled' "
            "WHERE uid = ?",
            (f"DSGVO-Löschung am {_now()} durch {admin_user}", uid),
        )
        _admin_audit(admin_user, "delete_account", uid,
                     json.dumps({"usage_rows_deleted": deleted}))
    log.info("Admin %s: DSGVO-Löschung für %s (%d Log-Einträge)",
             admin_user, uid, deleted)
    return {"uid": uid, "usage_rows_deleted": deleted, "status": "disabled"}


# ── Backup ──────────────────────────────────────────────────────────────────

_BACKUP_THREAD: threading.Thread | None = None
_BACKUP_THREAD_LOCK = threading.Lock()


def _backup_once() -> Path | None:
    """Erstellt eine konsistente Kopie von tokens.db unter tokens_backups/.

    Nutzt die SQLite-Online-Backup-API (keine File-Locks nötig). Alte Backups
    (> BACKUP_KEEP_DAYS) werden entfernt. Gibt den Backup-Pfad zurück oder
    None, wenn die Quell-DB noch nicht existiert.
    """
    try:
        BACKUP_DIR.mkdir(parents=True, exist_ok=True)
        if not DB_PATH.exists():
            return None
        dst = BACKUP_DIR / f"tokens.db.{datetime.now().strftime('%Y-%m-%d')}.bak"
        src_conn = sqlite3.connect(str(DB_PATH))
        dst_conn = sqlite3.connect(str(dst))
        try:
            with dst_conn:
                src_conn.backup(dst_conn)
        finally:
            src_conn.close()
            dst_conn.close()
        # Retention
        cutoff = time.time() - BACKUP_KEEP_DAYS * 86400
        for p in BACKUP_DIR.glob("tokens.db.*.bak"):
            try:
                if p.stat().st_mtime < cutoff:
                    p.unlink()
            except OSError:
                pass
        log.info("Backup geschrieben: %s", dst.name)
        return dst
    except Exception:
        log.exception("Backup fehlgeschlagen")
        return None


def _backup_loop() -> None:
    # Initial-Backup (best effort) sofort
    _backup_once()
    while True:
        try:
            time.sleep(BACKUP_INTERVAL_SEC)
            _backup_once()
        except Exception:
            log.exception("Backup-Schleife: Fehler")
            time.sleep(60)


def start_backup_thread() -> None:
    """Startet den Backup-Thread idempotent (Daemon)."""
    global _BACKUP_THREAD
    with _BACKUP_THREAD_LOCK:
        if _BACKUP_THREAD is not None and _BACKUP_THREAD.is_alive():
            return
        t = threading.Thread(target=_backup_loop, name="licensing-backup", daemon=True)
        t.start()
        _BACKUP_THREAD = t


def ensure_ready() -> None:
    """Muss einmal beim App-Start aufgerufen werden: DB-Schema + Backup-Thread."""
    init_db()
    start_backup_thread()