"""Sicherung von TESM: welche Kategorien es gibt und wie sie gelesen/geschrieben werden.

Der verschluesselte Umschlag selbst (Argon2id + AES-256-GCM, Zwischenstand des
Imports) liegt im Kern -- ``tesm_core.transfer``. Hier steht nur die Fachlogik.
"""

from __future__ import annotations

import json
import logging
import sqlite3
from datetime import datetime, timezone
from typing import Any, Callable, Iterable

from tesm_core.db import Database
from tesm_core.keystore import Keystore
from tesm_core.transfer import (
    MIN_PASSPHRASE_LENGTH,
    PREVIEW_TTL_MINUTES,
    Category,
    TransferError,
    drop_preview,
    load_preview,
    seal as _seal,
    store_preview,
    summarize as _summarize,
    unseal as _unseal,
)

logger = logging.getLogger(__name__)


def _now() -> datetime:
    return datetime.now(timezone.utc)

APP_TAG = "tesm"


def seal(payload: dict[str, Any], passphrase: str) -> str:
    return _seal(payload, passphrase, app_tag=APP_TAG)


def unseal(raw: bytes | str, passphrase: str) -> tuple[dict[str, Any], dict[str, Any]]:
    return _unseal(raw, passphrase, app_tag=APP_TAG)


# --------------------------------------------------------------------------- #
# Kategorien
# --------------------------------------------------------------------------- #


CATEGORIES: tuple[Category, ...] = (
    Category("devices", "Clients", "Geraetestammdaten inklusive Switch- und Portzuordnung"),
    Category("switches", "Switche", "Switch-Inventar"),
    Category(
        "credentials",
        "Zugangsdaten",
        "SSH-Logins. Die Passwoerter liegen im Export im Klartext -- geschuetzt "
        "ausschliesslich durch die Passphrase.",
        sensitive=True,
    ),
    Category("dhcp", "DHCP-Konfiguration", "Subnetze, Optionen und manuelle Reservierungen"),
    Category("fileshare", "Freigabe-Zuordnungen", "Verzeichnisgruppe zu UNC-Pfad"),
    Category("users", "Benutzer", "Lokale Konten mit Passwort-Hash", admin_only=True),
    Category("groups", "Gruppen und Rechte", "Rechtematrix", admin_only=True),
    Category("settings", "Einstellungen", "Laufzeiteinstellungen der Anwendung", admin_only=True),
)

CATEGORY_BY_KEY = {category.key: category for category in CATEGORIES}


# --------------------------------------------------------------------------- #
# Export
# --------------------------------------------------------------------------- #


def build_export(
    conn: sqlite3.Connection,
    keystore: Keystore,
    *,
    categories: Iterable[str],
    include_secrets: bool,
) -> dict[str, Any]:
    from .inventory import aad_for_credential

    wanted = [key for key in categories if key in CATEGORY_BY_KEY]
    payload: dict[str, Any] = {}

    if "devices" in wanted:
        payload["devices"] = Database.all(
            conn,
            "SELECT d.mac, d.ip, d.name, d.port, d.is_active, d.ssh_port, d.category, "
            "d.location, d.notes, d.auto_restart, s.hostname AS switch_hostname, "
            "c.name AS credential_name FROM devices d "
            "LEFT JOIN switches s ON s.id = d.switch_id "
            "LEFT JOIN credentials c ON c.id = d.credential_id "
            "WHERE d.deleted_at IS NULL",
        )
    if "switches" in wanted:
        payload["switches"] = Database.all(
            conn,
            "SELECT s.hostname, s.ip, s.ssh_port, s.model, s.location, s.notes, "
            "c.name AS credential_name FROM switches s "
            "LEFT JOIN credentials c ON c.id = s.credential_id WHERE s.deleted_at IS NULL",
        )
    if "credentials" in wanted:
        rows = Database.all(
            conn,
            "SELECT id, name, username, category, description, secret_encrypted FROM credentials "
            "WHERE deleted_at IS NULL",
        )
        entries = []
        for row in rows:
            entry = {
                "name": row["name"],
                "username": row["username"],
                "category": row["category"],
                "description": row["description"],
            }
            if include_secrets and str(row["secret_encrypted"] or ""):
                try:
                    entry["secret"] = keystore.decrypt(
                        str(row["secret_encrypted"]), aad=aad_for_credential(int(row["id"]))
                    )
                except Exception:  # noqa: BLE001
                    entry["secret_error"] = "nicht entschluesselbar"
            entries.append(entry)
        payload["credentials"] = entries
    if "dhcp" in wanted:
        payload["dhcp"] = {
            "subnets": Database.all(
                conn,
                "SELECT interface, range_start, range_end, gateway, dns, comment, enabled, "
                "sort_order FROM dhcp_subnets",
            ),
            "options": Database.all(
                conn,
                "SELECT o.code, o.name, o.type, o.description, o.is_standard, v.value, "
                "d.mac AS device_mac FROM dhcp_options o "
                "JOIN dhcp_option_values v ON v.option_id = o.id "
                "LEFT JOIN devices d ON d.id = v.device_id",
            ),
            "reservations": Database.all(
                conn, "SELECT mac, ip, hostname, comment FROM dhcp_reservations"
            ),
        }
    if "fileshare" in wanted:
        payload["fileshare"] = Database.all(
            conn, "SELECT ad_group_dn, ad_group_name, label, unc FROM fileshare_mappings"
        )
    if "users" in wanted:
        # Verzeichniskonten werden nie exportiert -- sie legen sich am Ziel bei
        # der ersten Anmeldung selbst an, und ihr Passwort liegt ohnehin nicht hier.
        payload["users"] = Database.all(
            conn,
            "SELECT u.username, u.password_hash, u.is_admin, u.is_locked, u.first_name, "
            "u.last_name, u.email, u.must_change_password, "
            "(SELECT group_concat(g.name, char(10)) FROM user_groups ug "
            " JOIN groups g ON g.id = ug.group_id WHERE ug.user_id = u.id) AS group_names "
            "FROM users u WHERE u.deleted_at IS NULL AND u.auth_source='local'",
        )
    if "groups" in wanted:
        payload["groups"] = [
            {
                "name": row["name"],
                "description": row["description"],
                "is_default": row["is_default"],
                "is_system": row["is_system"],
                "preset_key": row["preset_key"],
                "permissions": [
                    str(entry["permission"])
                    for entry in Database.all(
                        conn,
                        "SELECT permission FROM group_permissions WHERE group_id=? ORDER BY permission",
                        (row["id"],),
                    )
                ],
            }
            for row in Database.all(conn, "SELECT * FROM groups WHERE deleted_at IS NULL")
        ]
    if "settings" in wanted:
        payload["settings"] = {
            str(row["key"]): str(row["value"])
            for row in Database.all(conn, "SELECT key, value FROM settings")
            # Lizenz- und Aktivierungszustand gehoeren zur Maschine, nicht zur
            # Sicherung -- sonst wuerde ein Restore eine fremde Aktivierung erben.
            if not str(row["key"]).startswith("license_")
        }
    return payload


# --------------------------------------------------------------------------- #
# Zwischenstand des Imports
# --------------------------------------------------------------------------- #


# ``store_preview``/``load_preview``/``drop_preview`` kommen aus dem Kern (siehe
# Import oben). Hier standen zeichengleiche Kopien, die die Importe
# ueberschatteten -- und zwar defekte: ihnen fehlten ``secrets``, ``datetime``,
# ``timedelta``, ``timezone``, ``_now`` und ``PREVIEW_JOB_KIND``. Jeder Aufruf
# waere mit NameError abgebrochen, also der ganze Importweg. Aufgefallen ist es
# erst, als der erste Test ihn wirklich benutzt hat.


def summarize(payload: dict[str, Any]) -> list[dict[str, Any]]:
    entries = _summarize(payload, CATEGORIES)
    for entry in entries:
        if entry["key"] == "credentials":
            data = payload.get("credentials") or []
            entry["has_secrets"] = any(
                isinstance(row, dict) and "secret" in row for row in data
            )
        else:
            entry["has_secrets"] = False
    return entries


# --------------------------------------------------------------------------- #
# Uebernahme
# --------------------------------------------------------------------------- #


def apply_import(
    conn: sqlite3.Connection,
    keystore: Keystore,
    payload: dict[str, Any],
    *,
    categories: Iterable[str],
    set_setting: Callable[[str, Any], None],
) -> dict[str, dict[str, int]]:
    """Uebernimmt ausgewaehlte Kategorien. Rueckgabe: ``{kategorie: {angelegt, aktualisiert}}``.

    Abgleich nach natuerlichem Schluessel (Name/Hostname/MAC/Benutzername).
    Eintraege, die im Papierkorb liegen, werden **nicht** stillschweigend
    wiederbelebt -- das war im Vorgaenger ein Fehler, der geloeschte Geraete
    ueber den Import zurueckbrachte.
    """
    from .inventory import ValidationError, aad_for_credential, normalize_mac

    def mac_or_none(value: Any) -> str | None:
        """MAC normalisieren, ohne den ganzen Import zu sprengen.

        ``normalize_mac`` wirft bei unbrauchbarer Eingabe -- auch bei leerer.
        Aufgerufen wurde es hier ungeprueft, obwohl der Code danach ``if not
        mac: continue`` erwartet. Ein einziger Eintrag ohne MAC haette damit
        den gesamten Import mit einer Meldung ueber Hexzeichen abgebrochen.
        """
        try:
            return normalize_mac(str(value or ""))
        except ValidationError:
            return None


    wanted = {key for key in categories if key in CATEGORY_BY_KEY}
    report: dict[str, dict[str, int]] = {}
    stamp = _now().isoformat(timespec="seconds")

    def bump(category: str, field: str) -> None:
        report.setdefault(category, {"created": 0, "updated": 0, "skipped": 0})[field] += 1

    # Reihenfolge ist wichtig: Zugangsdaten vor Switchen, Switche vor Geraeten.
    if "credentials" in wanted:
        for entry in payload.get("credentials", []):
            name = str(entry.get("name") or "").strip()
            if not name:
                continue
            existing = Database.one(
                conn,
                "SELECT id FROM credentials WHERE name=? COLLATE NOCASE AND deleted_at IS NULL",
                (name,),
            )
            if existing is None:
                cursor = conn.execute(
                    "INSERT INTO credentials (name, username, secret_encrypted, category, "
                    "description, created_at, updated_at, updated_by) VALUES (?,?,'',?,?,?,?,'import')",
                    (
                        name,
                        str(entry.get("username") or ""),
                        str(entry.get("category") or "switch"),
                        str(entry.get("description") or ""),
                        stamp,
                        stamp,
                    ),
                )
                credential_id = int(cursor.lastrowid or 0)
                bump("credentials", "created")
            else:
                credential_id = int(existing["id"])
                conn.execute(
                    "UPDATE credentials SET username=?, category=?, description=?, updated_at=?, "
                    "updated_by='import' WHERE id=?",
                    (
                        str(entry.get("username") or ""),
                        str(entry.get("category") or "switch"),
                        str(entry.get("description") or ""),
                        stamp,
                        credential_id,
                    ),
                )
                bump("credentials", "updated")
            if entry.get("secret"):
                conn.execute(
                    "UPDATE credentials SET secret_encrypted=? WHERE id=?",
                    (
                        keystore.encrypt(str(entry["secret"]), aad=aad_for_credential(credential_id)),
                        credential_id,
                    ),
                )

    def credential_id_by_name(name: str | None) -> int | None:
        if not name:
            return None
        value = Database.value(
            conn,
            "SELECT id FROM credentials WHERE name=? COLLATE NOCASE AND deleted_at IS NULL",
            (name,),
        )
        return int(value) if value is not None else None

    if "switches" in wanted:
        for entry in payload.get("switches", []):
            hostname = str(entry.get("hostname") or "").strip()
            if not hostname:
                continue
            existing = Database.one(
                conn,
                "SELECT id FROM switches WHERE hostname=? COLLATE NOCASE AND deleted_at IS NULL",
                (hostname,),
            )
            values = (
                str(entry.get("ip") or ""),
                int(entry.get("ssh_port") or 22),
                credential_id_by_name(entry.get("credential_name")),
                str(entry.get("model") or ""),
                str(entry.get("location") or ""),
                str(entry.get("notes") or ""),
            )
            if existing is None:
                conn.execute(
                    "INSERT INTO switches (hostname, ip, ssh_port, credential_id, model, location, "
                    "notes, created_at, updated_at, updated_by) VALUES (?,?,?,?,?,?,?,?,?,'import')",
                    (hostname, *values, stamp, stamp),
                )
                bump("switches", "created")
            else:
                conn.execute(
                    "UPDATE switches SET ip=?, ssh_port=?, credential_id=?, model=?, location=?, "
                    "notes=?, updated_at=?, updated_by='import' WHERE id=?",
                    (*values, stamp, existing["id"]),
                )
                bump("switches", "updated")

    def switch_id_by_hostname(hostname: str | None) -> int | None:
        if not hostname:
            return None
        value = Database.value(
            conn,
            "SELECT id FROM switches WHERE hostname=? COLLATE NOCASE AND deleted_at IS NULL",
            (hostname,),
        )
        return int(value) if value is not None else None

    if "devices" in wanted:
        for entry in payload.get("devices", []):
            mac = normalize_mac(str(entry.get("mac") or ""))
            if not mac:
                bump("devices", "skipped")
                continue
            existing = Database.one(
                conn,
                "SELECT id, deleted_at FROM devices WHERE mac=? COLLATE NOCASE",
                (mac,),
            )
            if existing is not None and existing["deleted_at"]:
                # Im Papierkorb liegende Geraete werden nicht wiederbelebt.
                bump("devices", "skipped")
                continue
            values = (
                str(entry.get("ip") or ""),
                str(entry.get("name") or mac),
                switch_id_by_hostname(entry.get("switch_hostname")),
                str(entry.get("port") or ""),
                int(bool(entry.get("is_active", 1))),
                credential_id_by_name(entry.get("credential_name")),
                int(entry.get("ssh_port") or 22),
                str(entry.get("category") or ""),
                str(entry.get("location") or ""),
                str(entry.get("notes") or ""),
                int(bool(entry.get("auto_restart", 1))),
            )
            if existing is None:
                cursor = conn.execute(
                    "INSERT INTO devices (mac, ip, name, switch_id, port, is_active, credential_id, "
                    "ssh_port, category, location, notes, auto_restart, created_at, updated_at, "
                    "updated_by) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,'import')",
                    (mac, *values, stamp, stamp),
                )
                conn.execute(
                    "INSERT INTO device_status (device_id, state, last_change_at) "
                    "VALUES (?,'unknown',?)",
                    (int(cursor.lastrowid or 0), stamp),
                )
                bump("devices", "created")
            else:
                conn.execute(
                    "UPDATE devices SET ip=?, name=?, switch_id=?, port=?, is_active=?, "
                    "credential_id=?, ssh_port=?, category=?, location=?, notes=?, auto_restart=?, "
                    "updated_at=?, updated_by='import' WHERE id=?",
                    (*values, stamp, existing["id"]),
                )
                bump("devices", "updated")

    if "dhcp" in wanted:
        data = payload.get("dhcp") or {}
        for entry in data.get("subnets", []):
            conn.execute(
                "INSERT INTO dhcp_subnets (interface, range_start, range_end, gateway, dns, "
                "comment, enabled, sort_order, created_at, updated_at) VALUES (?,?,?,?,?,?,?,?,?,?)",
                (
                    str(entry.get("interface") or ""),
                    str(entry.get("range_start") or ""),
                    str(entry.get("range_end") or ""),
                    str(entry.get("gateway") or ""),
                    str(entry.get("dns") or ""),
                    str(entry.get("comment") or ""),
                    int(bool(entry.get("enabled", 1))),
                    int(entry.get("sort_order") or 0),
                    stamp,
                    stamp,
                ),
            )
            bump("dhcp", "created")
        # Optionen: Definition und Wert stehen je Zeile beieinander. Sie wurden
        # exportiert, aber nie importiert -- ein Export/Import verlor damit
        # stillschweigend alle eigenen Optionen und alle Werte, auch die je
        # Geraet gesetzten.
        for entry in data.get("options", []):
            code = int(entry.get("code") or 0)
            name = str(entry.get("name") or "").strip()
            if not code or not name:
                continue
            conn.execute(
                "INSERT INTO dhcp_options (code, name, type, description, is_standard) "
                "VALUES (?,?,?,?,?) ON CONFLICT(code) DO UPDATE SET name=excluded.name, "
                "type=excluded.type, description=excluded.description",
                (
                    code,
                    name,
                    str(entry.get("type") or "string"),
                    str(entry.get("description") or ""),
                    int(bool(entry.get("is_standard"))),
                ),
            )
            option_id = Database.value(conn, "SELECT id FROM dhcp_options WHERE code=?", (code,))
            if option_id is None:
                continue
            wert = str(entry.get("value") or "")
            if not wert:
                continue
            roh_mac = str(entry.get("device_mac") or "")
            geraet_mac = mac_or_none(roh_mac) if roh_mac else None
            if roh_mac and geraet_mac is None:
                bump("dhcp", "skipped")
                continue
            device_id = (
                Database.value(
                    conn,
                    "SELECT id FROM devices WHERE mac=? AND deleted_at IS NULL",
                    (geraet_mac,),
                )
                if geraet_mac
                else None
            )
            # Ein Wert, der zu einem Geraet gehoert, das es hier nicht gibt,
            # darf nicht als globaler Wert landen -- das wuerde ihn an alle
            # ausliefern. Er wird uebersprungen.
            if geraet_mac and device_id is None:
                bump("dhcp", "skipped")
                continue
            if device_id is None:
                conn.execute(
                    "INSERT INTO dhcp_option_values (option_id, device_id, value) "
                    "VALUES (?,NULL,?) ON CONFLICT(option_id) WHERE device_id IS NULL "
                    "DO UPDATE SET value=excluded.value",
                    (int(option_id), wert),
                )
            else:
                conn.execute(
                    "INSERT INTO dhcp_option_values (option_id, device_id, value) "
                    "VALUES (?,?,?) ON CONFLICT(option_id, device_id) WHERE device_id IS NOT NULL "
                    "DO UPDATE SET value=excluded.value",
                    (int(option_id), int(device_id), wert),
                )
            bump("dhcp", "updated")
        for entry in data.get("reservations", []):
            mac = mac_or_none(entry.get("mac"))
            if not mac:
                bump("dhcp", "skipped")
                continue
            conn.execute(
                "INSERT INTO dhcp_reservations (mac, ip, hostname, comment, created_at) "
                "VALUES (?,?,?,?,?) ON CONFLICT(mac) DO UPDATE SET ip=excluded.ip, "
                "hostname=excluded.hostname, comment=excluded.comment",
                (
                    mac,
                    str(entry.get("ip") or ""),
                    str(entry.get("hostname") or ""),
                    str(entry.get("comment") or ""),
                    stamp,
                ),
            )
            bump("dhcp", "updated")

    if "fileshare" in wanted:
        for entry in payload.get("fileshare", []):
            conn.execute(
                "INSERT INTO fileshare_mappings (ad_group_dn, ad_group_name, label, unc, created_at) "
                "VALUES (?,?,?,?,?) ON CONFLICT(ad_group_dn, unc) DO UPDATE SET "
                "label=excluded.label, ad_group_name=excluded.ad_group_name",
                (
                    str(entry.get("ad_group_dn") or ""),
                    str(entry.get("ad_group_name") or ""),
                    str(entry.get("label") or ""),
                    str(entry.get("unc") or ""),
                    stamp,
                ),
            )
            bump("fileshare", "updated")

    if "groups" in wanted:
        for entry in payload.get("groups", []):
            name = str(entry.get("name") or "").strip()
            if not name:
                continue
            existing = Database.one(
                conn,
                "SELECT id FROM groups WHERE name=? COLLATE NOCASE AND deleted_at IS NULL",
                (name,),
            )
            if existing is None:
                cursor = conn.execute(
                    "INSERT INTO groups (name, description, is_default, is_system, preset_key, "
                    "created_at, updated_at) VALUES (?,?,?,?,?,?,?)",
                    (
                        name,
                        str(entry.get("description") or ""),
                        int(bool(entry.get("is_default"))),
                        int(bool(entry.get("is_system"))),
                        str(entry.get("preset_key") or ""),
                        stamp,
                        stamp,
                    ),
                )
                group_id = int(cursor.lastrowid or 0)
                bump("groups", "created")
            else:
                group_id = int(existing["id"])
                bump("groups", "updated")
            conn.execute("DELETE FROM group_permissions WHERE group_id=?", (group_id,))
            conn.executemany(
                "INSERT OR IGNORE INTO group_permissions (group_id, permission) VALUES (?,?)",
                [(group_id, str(permission)) for permission in entry.get("permissions", [])],
            )

    if "users" in wanted:
        for entry in payload.get("users", []):
            username = str(entry.get("username") or "").strip()
            if not username:
                continue
            existing = Database.one(
                conn,
                "SELECT id, deleted_at FROM users WHERE username=? COLLATE NOCASE",
                (username,),
            )
            if existing is not None and existing["deleted_at"]:
                bump("users", "skipped")
                continue
            if existing is None:
                cursor = conn.execute(
                    "INSERT INTO users (username, password_hash, is_admin, is_locked, auth_source, "
                    "first_name, last_name, email, must_change_password, created_at, updated_at) "
                    "VALUES (?,?,?,?,'local',?,?,?,?,?,?)",
                    (
                        username,
                        str(entry.get("password_hash") or ""),
                        int(bool(entry.get("is_admin"))),
                        int(bool(entry.get("is_locked"))),
                        str(entry.get("first_name") or ""),
                        str(entry.get("last_name") or ""),
                        str(entry.get("email") or ""),
                        int(bool(entry.get("must_change_password"))),
                        stamp,
                        stamp,
                    ),
                )
                user_id = int(cursor.lastrowid or 0)
                bump("users", "created")
            else:
                user_id = int(existing["id"])
                conn.execute(
                    "UPDATE users SET first_name=?, last_name=?, email=?, updated_at=? WHERE id=?",
                    (
                        str(entry.get("first_name") or ""),
                        str(entry.get("last_name") or ""),
                        str(entry.get("email") or ""),
                        stamp,
                        user_id,
                    ),
                )
                bump("users", "updated")
            names = [
                name for name in str(entry.get("group_names") or "").split("\n") if name.strip()
            ]
            if names:
                conn.execute("DELETE FROM user_groups WHERE user_id=?", (user_id,))
                for name in names:
                    group_id = Database.value(
                        conn,
                        "SELECT id FROM groups WHERE name=? COLLATE NOCASE AND deleted_at IS NULL",
                        (name.strip(),),
                    )
                    if group_id is not None:
                        conn.execute(
                            "INSERT OR IGNORE INTO user_groups (user_id, group_id) VALUES (?,?)",
                            (user_id, int(group_id)),
                        )

    if "settings" in wanted:
        for key, value in (payload.get("settings") or {}).items():
            if str(key).startswith("license_"):
                continue
            # Ueber den Store, damit Typ und Wertebereich geprueft werden -- und
            # bewusst mit derselben Verbindung (keine zweite oeffnen!).
            set_setting(str(key), value)
            bump("settings", "updated")

    return report
