"""Bestandsdaten: Zugangsdaten, Switche, Geraete.

Zugangsdaten sind -- wie im Vorgaenger -- eine eigene, wiederverwendbare
Entitaet und stehen nicht als Benutzer/Passwort-Paar an jedem Switch. Neu ist,
wie das Geheimnis liegt: AES-256-GCM mit rotierbarem Schluessel und dem
Verwendungszweck als zugeordnete Daten (``credentials.secret:<id>``). Damit ist
ein Chiffrat nicht an anderer Stelle einspielbar.
"""

from __future__ import annotations

import ipaddress
import re
import sqlite3
from dataclasses import dataclass
from datetime import datetime, timezone
from typing import Any, Iterable

from tesm_core.db import Database
from tesm_core.keystore import DecryptionError, Keystore

MAC_RE = re.compile(r"^[0-9a-f]{2}(:[0-9a-f]{2}){5}$")
PORT_RE = re.compile(r"^[A-Za-z0-9/.:-]{1,24}$")
HOSTNAME_RE = re.compile(r"^[A-Za-z0-9]([A-Za-z0-9._-]{0,62})$")

CREDENTIAL_CATEGORIES: tuple[tuple[str, str], ...] = (
    ("switch", "Switch"),
    ("linux", "Linux-Client"),
    ("windows", "Windows-Client"),
    ("router", "Router/Sonstiges"),
)

#: Diese Kategorien erscheinen auf der Wartungsseite. Der Weg dorthin ist
#: verschieden: Linux ueber SSH, Windows ueber RPC.
MAINTENANCE_CATEGORIES: tuple[str, ...] = ("linux", "windows")

#: Rueckwaertskompatibler Name -- die Vorgabe, wenn nur eine gemeint ist.
MAINTENANCE_CATEGORY = "linux"

#: Diese Kategorie wird **nicht** ueber SSH bedient. Windows-Geraete haben in
#: aller Regel keinen SSH-Dienst und damit auch keinen Host-Schluessel, den
#: jemand freigeben koennte. Alles, was mit Host-Schluesseln zu tun hat, laesst
#: sie deshalb aus -- sonst stuenden sie fuer immer auf "Freigabe offen".
NON_SSH_CATEGORY = "windows"


def is_ssh_managed(category: str | None) -> bool:
    """Wird ein Geraet dieser Kategorie ueber SSH bedient?"""
    return str(category or "") != NON_SSH_CATEGORY


#: Was eine Kategorie tatsaechlich bewirkt. Steht in der Oberflaeche, damit
#: niemand eine Auswahl trifft, die stillschweigend folgenlos bleibt: genau das
#: war "Windows-Client" vorher -- waehlbar, aber ohne jede Wirkung.
CATEGORY_EFFECTS: dict[str, str] = {
    "switch": "Anmeldung an Switchen: PoE schalten, Konsole, Statusabfragen.",
    "linux": "Wartung ueber SSH: Paketaktualisierung (apt) und Neustart. Konsole.",
    "windows": (
        "Neustart ueber RPC (wie 'shutdown /m'). Kein SSH, kein Host-Schluessel. Das "
        "Geraet braucht offene Datei- und Druckerfreigabe (Port 445); das Konto braucht "
        "das Recht, aus der Ferne herunterzufahren. Paketaktualisierung gibt es hier nicht."
    ),
    "router": "Nur Dokumentation -- diese Kategorie loest keine Aktion aus.",
}

DEVICE_CATEGORIES: tuple[tuple[str, str], ...] = (
    ("", "nicht angegeben"),
    ("kamera", "Kamera"),
    ("accesspoint", "Access Point"),
    ("telefon", "Telefon"),
    ("sensor", "Sensor"),
    ("rechner", "Rechner"),
    ("sonstiges", "Sonstiges"),
)


class ValidationError(Exception):
    """Eingabefehler mit Klartextmeldung."""


def _now() -> str:
    return datetime.now(timezone.utc).isoformat(timespec="seconds")


def aad_for_credential(credential_id: int) -> str:
    return f"credentials.secret:{credential_id}"


# --------------------------------------------------------------------------- #
# Validierung
# --------------------------------------------------------------------------- #


def normalize_mac(value: str) -> str:
    cleaned = re.sub(r"[^0-9a-fA-F]", "", value or "")
    if len(cleaned) != 12:
        raise ValidationError(
            "Die MAC-Adresse muss zwoelf hexadezimale Zeichen enthalten "
            "(z. B. aa:bb:cc:dd:ee:ff)."
        )
    return ":".join(cleaned[index : index + 2] for index in range(0, 12, 2)).lower()


def validate_ip(value: str, *, field: str = "IP-Adresse") -> str:
    text = (value or "").strip()
    try:
        ipaddress.ip_address(text)
    except ValueError as exc:
        raise ValidationError(f"{field}: {text!r} ist keine gueltige IP-Adresse.") from exc
    return text


def validate_port_name(value: str) -> str:
    text = (value or "").strip()
    if not text:
        return ""
    if not PORT_RE.match(text):
        raise ValidationError(
            "Die Portbezeichnung enthaelt unerlaubte Zeichen (erlaubt: Buchstaben, "
            "Ziffern, / . : -)."
        )
    return text


def validate_hostname(value: str) -> str:
    text = (value or "").strip()
    if not HOSTNAME_RE.match(text):
        raise ValidationError("Der Hostname enthaelt unerlaubte Zeichen oder ist leer.")
    return text


def validate_ssh_port(value: Any) -> int:
    try:
        port = int(value or 22)
    except (TypeError, ValueError) as exc:
        raise ValidationError("Der SSH-Port muss eine Zahl sein.") from exc
    if not 1 <= port <= 65535:
        raise ValidationError("Der SSH-Port muss zwischen 1 und 65535 liegen.")
    return port


# --------------------------------------------------------------------------- #
# Zugangsdaten
# --------------------------------------------------------------------------- #


@dataclass(frozen=True, slots=True)
class Credential:
    id: int
    name: str
    username: str
    category: str
    description: str
    has_secret: bool
    in_use: int


def list_credentials(
    conn: sqlite3.Connection, *, include_deleted: bool = False
) -> list[Credential]:
    where = "" if include_deleted else "WHERE c.deleted_at IS NULL"
    rows = Database.all(
        conn,
        f"""
        SELECT c.*,
               (SELECT COUNT(*) FROM switches s
                 WHERE s.credential_id = c.id AND s.deleted_at IS NULL) +
               (SELECT COUNT(*) FROM devices d
                 WHERE d.credential_id = c.id AND d.deleted_at IS NULL) AS in_use
          FROM credentials c {where}
         ORDER BY c.name COLLATE NOCASE
        """,
    )
    return [
        Credential(
            id=int(row["id"]),
            name=str(row["name"]),
            username=str(row["username"] or ""),
            category=str(row["category"] or "switch"),
            description=str(row["description"] or ""),
            has_secret=bool(row["secret_encrypted"]),
            in_use=int(row["in_use"] or 0),
        )
        for row in rows
    ]


def create_credential(
    conn: sqlite3.Connection,
    keystore: Keystore,
    *,
    name: str,
    username: str,
    secret: str,
    category: str,
    description: str = "",
    actor: str = "",
) -> int:
    clean_name = (name or "").strip()
    if not clean_name:
        raise ValidationError("Der Name darf nicht leer sein.")
    if category not in dict(CREDENTIAL_CATEGORIES):
        raise ValidationError("Unbekannte Kategorie.")
    existing = Database.one(
        conn, "SELECT id, deleted_at FROM credentials WHERE name=? COLLATE NOCASE", (clean_name,)
    )
    if existing is not None:
        if existing["deleted_at"]:
            raise ValidationError(
                f"Zugangsdaten namens {clean_name!r} liegen im Papierkorb. Stellen Sie sie "
                "wieder her oder loeschen Sie sie endgueltig."
            )
        raise ValidationError(f"Zugangsdaten namens {clean_name!r} existieren bereits.")

    cursor = conn.execute(
        "INSERT INTO credentials (name, username, secret_encrypted, category, description, "
        "created_at, updated_at, updated_by) VALUES (?,?,'',?,?,?,?,?)",
        (clean_name, (username or "").strip(), category, (description or "").strip(), _now(), _now(), actor),
    )
    credential_id = int(cursor.lastrowid or 0)
    if secret:
        conn.execute(
            "UPDATE credentials SET secret_encrypted=? WHERE id=?",
            (keystore.encrypt(secret, aad=aad_for_credential(credential_id)), credential_id),
        )
    return credential_id


def update_credential(
    conn: sqlite3.Connection,
    keystore: Keystore,
    credential_id: int,
    *,
    name: str,
    username: str,
    secret: str | None,
    category: str,
    description: str = "",
    actor: str = "",
) -> list[str]:
    row = Database.one(
        conn, "SELECT * FROM credentials WHERE id=? AND deleted_at IS NULL", (credential_id,)
    )
    if row is None:
        raise ValidationError("Diese Zugangsdaten existieren nicht (mehr).")
    clean_name = (name or "").strip()
    if not clean_name:
        raise ValidationError("Der Name darf nicht leer sein.")
    clash = Database.one(
        conn,
        "SELECT id FROM credentials WHERE name=? COLLATE NOCASE AND id<>? AND deleted_at IS NULL",
        (clean_name, credential_id),
    )
    if clash is not None:
        raise ValidationError(f"Zugangsdaten namens {clean_name!r} existieren bereits.")

    changes: list[str] = []
    if clean_name != str(row["name"]):
        changes.append(f"Name: {row['name']} -> {clean_name}")
    if (username or "").strip() != str(row["username"] or ""):
        changes.append("Benutzer geaendert")
    if category != str(row["category"]):
        changes.append(f"Kategorie: {row['category']} -> {category}")
    if (description or "").strip() != str(row["description"] or ""):
        changes.append("Beschreibung geaendert")

    conn.execute(
        "UPDATE credentials SET name=?, username=?, category=?, description=?, updated_at=?, "
        "updated_by=? WHERE id=?",
        (clean_name, (username or "").strip(), category, (description or "").strip(), _now(), actor, credential_id),
    )
    if secret:
        conn.execute(
            "UPDATE credentials SET secret_encrypted=? WHERE id=?",
            (keystore.encrypt(secret, aad=aad_for_credential(credential_id)), credential_id),
        )
        changes.append("Passwort geaendert")
    return changes


def credential_secret(
    conn: sqlite3.Connection, keystore: Keystore, credential_id: int
) -> tuple[str, str]:
    """``(benutzer, passwort)`` -- entschluesselt. Nur fuer den Verbindungsaufbau."""
    row = Database.one(
        conn,
        "SELECT username, secret_encrypted FROM credentials WHERE id=? AND deleted_at IS NULL",
        (credential_id,),
    )
    if row is None:
        raise ValidationError("Die hinterlegten Zugangsdaten existieren nicht mehr.")
    encrypted = str(row["secret_encrypted"] or "")
    if not encrypted:
        return str(row["username"] or ""), ""
    try:
        secret = keystore.decrypt(encrypted, aad=aad_for_credential(int(credential_id)))
    except DecryptionError as exc:
        raise ValidationError(
            "Das gespeicherte Passwort konnte nicht entschluesselt werden. Wurde der "
            "Datenschluessel ausgetauscht? Bitte die Zugangsdaten neu eingeben."
        ) from exc
    return str(row["username"] or ""), secret


def delete_credential(conn: sqlite3.Connection, credential_id: int) -> None:
    row = Database.one(
        conn,
        """
        SELECT
          (SELECT COUNT(*) FROM switches WHERE credential_id=? AND deleted_at IS NULL) AS switches,
          (SELECT COUNT(*) FROM devices WHERE credential_id=? AND deleted_at IS NULL) AS devices
        """,
        (credential_id, credential_id),
    ) or {}
    if int(row.get("switches") or 0) or int(row.get("devices") or 0):
        raise ValidationError(
            f"Diese Zugangsdaten werden noch von {row.get('switches')} Switch(en) und "
            f"{row.get('devices')} Geraet(en) verwendet."
        )
    conn.execute("UPDATE credentials SET deleted_at=? WHERE id=?", (_now(), credential_id))


def reencrypt_secrets(conn: sqlite3.Connection, keystore: Keystore) -> int:
    """Verschluesselt alle Geheimnisse unter dem aktuellen Schluessel neu.

    Wird von der Schluesselrotation auf der Diagnoseseite aufgerufen.
    """
    migrated = 0
    for row in Database.all(
        conn, "SELECT id, secret_encrypted FROM credentials WHERE secret_encrypted <> ''"
    ):
        token = str(row["secret_encrypted"])
        if not keystore.needs_reencrypt(token):
            continue
        aad = aad_for_credential(int(row["id"]))
        try:
            plain = keystore.decrypt(token, aad=aad)
        except DecryptionError:
            continue
        conn.execute(
            "UPDATE credentials SET secret_encrypted=? WHERE id=?",
            (keystore.encrypt(plain, aad=aad), row["id"]),
        )
        migrated += 1

    # Bind-Passwort des Verzeichnisdienstes mitnehmen.
    from tesm_core.auth.directory import AAD_LDAP_BIND, SERVICE_PURPOSE

    row = Database.one(
        conn, "SELECT password_encrypted FROM service_accounts WHERE purpose=?", (SERVICE_PURPOSE,)
    )
    if row and str(row["password_encrypted"] or ""):
        token = str(row["password_encrypted"])
        if keystore.needs_reencrypt(token):
            try:
                plain = keystore.decrypt(token, aad=AAD_LDAP_BIND)
            except DecryptionError:
                plain = ""
            if plain:
                conn.execute(
                    "UPDATE service_accounts SET password_encrypted=? WHERE purpose=?",
                    (keystore.encrypt(plain, aad=AAD_LDAP_BIND), SERVICE_PURPOSE),
                )
                migrated += 1
    return migrated


# --------------------------------------------------------------------------- #
# Switche
# --------------------------------------------------------------------------- #


def list_switches(conn: sqlite3.Connection, *, include_deleted: bool = False) -> list[dict[str, Any]]:
    where = "" if include_deleted else "WHERE s.deleted_at IS NULL"
    return Database.all(
        conn,
        f"""
        SELECT s.*, c.name AS credential_name, c.username AS credential_username,
               (SELECT COUNT(*) FROM devices d
                 WHERE d.switch_id = s.id AND d.deleted_at IS NULL) AS device_count,
               (SELECT COUNT(*) FROM switch_host_keys k
                 WHERE k.host = s.ip AND k.port = s.ssh_port) AS host_key_known
          FROM switches s
          LEFT JOIN credentials c ON c.id = s.credential_id AND c.deleted_at IS NULL
          {where}
         ORDER BY s.hostname COLLATE NOCASE
        """,
    )


def get_switch(conn: sqlite3.Connection, switch_id: int) -> dict[str, Any] | None:
    return Database.one(
        conn,
        "SELECT s.*, c.name AS credential_name FROM switches s "
        "LEFT JOIN credentials c ON c.id = s.credential_id "
        "WHERE s.id=? AND s.deleted_at IS NULL",
        (switch_id,),
    )


def create_switch(
    conn: sqlite3.Connection,
    *,
    hostname: str,
    ip: str,
    ssh_port: Any,
    credential_id: Any,
    model: str = "",
    location: str = "",
    notes: str = "",
    actor: str = "",
) -> int:
    clean_host = validate_hostname(hostname)
    clean_ip = validate_ip(ip)
    port = validate_ssh_port(ssh_port)
    existing = Database.one(
        conn, "SELECT id, deleted_at FROM switches WHERE hostname=? COLLATE NOCASE", (clean_host,)
    )
    if existing is not None:
        if existing["deleted_at"]:
            raise ValidationError(
                f"Ein Switch namens {clean_host!r} liegt im Papierkorb."
            )
        raise ValidationError(f"Ein Switch namens {clean_host!r} existiert bereits.")

    cursor = conn.execute(
        "INSERT INTO switches (hostname, ip, ssh_port, credential_id, model, location, notes, "
        "created_at, updated_at, updated_by) VALUES (?,?,?,?,?,?,?,?,?,?)",
        (
            clean_host,
            clean_ip,
            port,
            int(credential_id) if credential_id else None,
            (model or "").strip(),
            (location or "").strip(),
            (notes or "").strip(),
            _now(),
            _now(),
            actor,
        ),
    )
    return int(cursor.lastrowid or 0)


def update_switch(
    conn: sqlite3.Connection,
    switch_id: int,
    *,
    hostname: str,
    ip: str,
    ssh_port: Any,
    credential_id: Any,
    model: str = "",
    location: str = "",
    notes: str = "",
    actor: str = "",
) -> list[str]:
    row = get_switch(conn, switch_id)
    if row is None:
        raise ValidationError("Dieser Switch existiert nicht (mehr).")
    clean_host = validate_hostname(hostname)
    clean_ip = validate_ip(ip)
    port = validate_ssh_port(ssh_port)
    clash = Database.one(
        conn,
        "SELECT id FROM switches WHERE hostname=? COLLATE NOCASE AND id<>? AND deleted_at IS NULL",
        (clean_host, switch_id),
    )
    if clash is not None:
        raise ValidationError(f"Ein Switch namens {clean_host!r} existiert bereits.")

    changes: list[str] = []
    for label, before, after in (
        ("Hostname", str(row["hostname"]), clean_host),
        ("IP", str(row["ip"]), clean_ip),
        ("SSH-Port", str(row["ssh_port"]), str(port)),
        ("Zugangsdaten", str(row["credential_id"] or ""), str(credential_id or "")),
        ("Modell", str(row["model"] or ""), (model or "").strip()),
        ("Standort", str(row["location"] or ""), (location or "").strip()),
    ):
        if before != after:
            changes.append(f"{label}: {before or '-'} -> {after or '-'}")

    conn.execute(
        "UPDATE switches SET hostname=?, ip=?, ssh_port=?, credential_id=?, model=?, location=?, "
        "notes=?, updated_at=?, updated_by=? WHERE id=?",
        (
            clean_host,
            clean_ip,
            port,
            int(credential_id) if credential_id else None,
            (model or "").strip(),
            (location or "").strip(),
            (notes or "").strip(),
            _now(),
            actor,
            switch_id,
        ),
    )
    return changes


def delete_switch(conn: sqlite3.Connection, switch_id: int) -> None:
    count = Database.value(
        conn,
        "SELECT COUNT(*) FROM devices WHERE switch_id=? AND deleted_at IS NULL",
        (switch_id,),
        default=0,
    )
    if int(count or 0):
        raise ValidationError(
            f"Diesem Switch sind noch {count} Geraet(e) zugeordnet. Bitte zuerst umhaengen."
        )
    conn.execute("UPDATE switches SET deleted_at=? WHERE id=?", (_now(), switch_id))


# --------------------------------------------------------------------------- #
# Geraete
# --------------------------------------------------------------------------- #


def list_devices(
    conn: sqlite3.Connection,
    *,
    include_deleted: bool = False,
    only_active: bool = False,
) -> list[dict[str, Any]]:
    clauses = [] if include_deleted else ["d.deleted_at IS NULL"]
    if only_active:
        clauses.append("d.is_active = 1")
    where = f"WHERE {' AND '.join(clauses)}" if clauses else ""
    return Database.all(
        conn,
        f"""
        SELECT d.*, s.hostname AS switch_hostname, s.ip AS switch_ip,
               s.ssh_port AS switch_ssh_port, s.credential_id AS switch_credential_id,
               c.name AS credential_name, c.category AS credential_category,
               -- Wie bei den Switchen: der Zustand der Host-Schluessel-Freigabe
               -- gehoert in die Liste. Ob eine Wartung ueberhaupt laufen kann,
               -- soll man sehen, ohne jede Detailseite zu oeffnen.
               (SELECT COUNT(*) FROM switch_host_keys k
                 WHERE k.host = d.ip AND k.port = d.ssh_port) AS host_key_known,
               COALESCE(st.state,'unknown') AS state, st.latency_ms, st.last_check_at,
               st.last_ok_at, st.last_change_at, st.last_restart_at, st.restart_count,
               st.fail_count
          FROM devices d
          LEFT JOIN switches s ON s.id = d.switch_id AND s.deleted_at IS NULL
          LEFT JOIN credentials c ON c.id = d.credential_id AND c.deleted_at IS NULL
          LEFT JOIN device_status st ON st.device_id = d.id
          {where}
         ORDER BY d.name COLLATE NOCASE
        """,
    )


def get_device(conn: sqlite3.Connection, device_id: int) -> dict[str, Any] | None:
    rows = [
        row
        for row in list_devices(conn, include_deleted=True)
        if int(row["id"]) == int(device_id)
    ]
    return rows[0] if rows else None


def create_device(
    conn: sqlite3.Connection,
    *,
    name: str,
    mac: str,
    ip: str,
    switch_id: Any,
    port: str,
    credential_id: Any,
    ssh_port: Any,
    category: str = "",
    location: str = "",
    notes: str = "",
    is_active: bool = True,
    auto_restart: bool = True,
    actor: str = "",
) -> int:
    clean_name = (name or "").strip()
    if not clean_name:
        raise ValidationError("Der Name darf nicht leer sein.")
    clean_mac = normalize_mac(mac)
    clean_ip = validate_ip(ip)
    clean_port = validate_port_name(port)
    existing = Database.one(
        conn, "SELECT id, deleted_at FROM devices WHERE mac=? COLLATE NOCASE", (clean_mac,)
    )
    if existing is not None:
        if existing["deleted_at"]:
            raise ValidationError(f"Ein Geraet mit der MAC {clean_mac} liegt im Papierkorb.")
        raise ValidationError(f"Ein Geraet mit der MAC {clean_mac} existiert bereits.")

    if switch_id and not clean_port:
        raise ValidationError(
            "Zu einem zugeordneten Switch gehoert eine Portbezeichnung -- sonst ist kein "
            "PoE-Neustart moeglich."
        )

    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 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
        (
            clean_mac,
            clean_ip,
            clean_name,
            int(switch_id) if switch_id else None,
            clean_port,
            int(bool(is_active)),
            int(credential_id) if credential_id else None,
            validate_ssh_port(ssh_port),
            (category or "").strip(),
            (location or "").strip(),
            (notes or "").strip(),
            int(bool(auto_restart)),
            _now(),
            _now(),
            actor,
        ),
    )
    device_id = int(cursor.lastrowid or 0)
    conn.execute(
        "INSERT INTO device_status (device_id, state, last_change_at) VALUES (?,'unknown',?)",
        (device_id, _now()),
    )
    return device_id


def update_device(
    conn: sqlite3.Connection,
    device_id: int,
    *,
    name: str,
    mac: str,
    ip: str,
    switch_id: Any,
    port: str,
    credential_id: Any,
    ssh_port: Any,
    category: str = "",
    location: str = "",
    notes: str = "",
    is_active: bool = True,
    auto_restart: bool = True,
    actor: str = "",
) -> list[str]:
    row = Database.one(
        conn, "SELECT * FROM devices WHERE id=? AND deleted_at IS NULL", (device_id,)
    )
    if row is None:
        raise ValidationError("Dieses Geraet existiert nicht (mehr).")
    clean_name = (name or "").strip()
    if not clean_name:
        raise ValidationError("Der Name darf nicht leer sein.")
    clean_mac = normalize_mac(mac)
    clean_ip = validate_ip(ip)
    clean_port = validate_port_name(port)
    clash = Database.one(
        conn,
        "SELECT id FROM devices WHERE mac=? COLLATE NOCASE AND id<>? AND deleted_at IS NULL",
        (clean_mac, device_id),
    )
    if clash is not None:
        raise ValidationError(f"Ein Geraet mit der MAC {clean_mac} existiert bereits.")
    if switch_id and not clean_port:
        raise ValidationError("Zu einem zugeordneten Switch gehoert eine Portbezeichnung.")

    changes: list[str] = []
    for label, before, after in (
        ("Name", str(row["name"]), clean_name),
        ("MAC", str(row["mac"]), clean_mac),
        ("IP", str(row["ip"]), clean_ip),
        ("Switch", str(row["switch_id"] or ""), str(switch_id or "")),
        ("Port", str(row["port"] or ""), clean_port),
        ("Aktiv", str(bool(row["is_active"])), str(bool(is_active))),
        ("Automatischer Neustart", str(bool(row["auto_restart"])), str(bool(auto_restart))),
    ):
        if before != after:
            changes.append(f"{label}: {before or '-'} -> {after or '-'}")

    conn.execute(
        "UPDATE devices SET mac=?, ip=?, name=?, switch_id=?, port=?, is_active=?, "
        "credential_id=?, ssh_port=?, category=?, location=?, notes=?, auto_restart=?, "
        "updated_at=?, updated_by=? WHERE id=?",
        (
            clean_mac,
            clean_ip,
            clean_name,
            int(switch_id) if switch_id else None,
            clean_port,
            int(bool(is_active)),
            int(credential_id) if credential_id else None,
            validate_ssh_port(ssh_port),
            (category or "").strip(),
            (location or "").strip(),
            (notes or "").strip(),
            int(bool(auto_restart)),
            _now(),
            actor,
            device_id,
        ),
    )
    return changes


def set_device_active(conn: sqlite3.Connection, device_id: int, active: bool) -> None:
    conn.execute(
        "UPDATE devices SET is_active=?, updated_at=? WHERE id=? AND deleted_at IS NULL",
        (int(bool(active)), _now(), device_id),
    )


def delete_device(conn: sqlite3.Connection, device_id: int) -> None:
    conn.execute("UPDATE devices SET deleted_at=? WHERE id=?", (_now(), device_id))


def restore_row(conn: sqlite3.Connection, table: str, row_id: int, *, unique_column: str) -> None:
    """Wiederherstellung mit Konfliktpruefung auf dem eindeutigen Feld."""
    if table not in {"devices", "switches", "credentials"}:
        raise ValidationError("Unbekannte Ressource.")
    row = Database.one(conn, f"SELECT {unique_column} FROM {table} WHERE id=?", (row_id,))
    if row is None:
        raise ValidationError("Dieser Eintrag existiert nicht.")
    value = row[unique_column]
    clash = Database.one(
        conn,
        f"SELECT id FROM {table} WHERE {unique_column}=? COLLATE NOCASE AND id<>? "
        "AND deleted_at IS NULL",
        (value, row_id),
    )
    if clash is not None:
        raise ValidationError(
            f"Es existiert bereits ein aktiver Eintrag mit {unique_column} = {value!r}."
        )
    conn.execute(f"UPDATE {table} SET deleted_at=NULL WHERE id=?", (row_id,))


def purge_row(conn: sqlite3.Connection, table: str, row_id: int) -> None:
    if table not in {"devices", "switches", "credentials"}:
        raise ValidationError("Unbekannte Ressource.")
    conn.execute(f"DELETE FROM {table} WHERE id=?", (row_id,))


def trash_entries(
    conn: sqlite3.Connection, table: str, *, label_column: str, detail_columns: Iterable[str]
) -> list[dict[str, Any]]:
    columns = ", ".join([label_column, *detail_columns])
    rows = Database.all(
        conn,
        f"SELECT id, {columns}, deleted_at FROM {table} "
        "WHERE deleted_at IS NOT NULL ORDER BY deleted_at DESC",
    )
    return [
        {
            "id": str(row["id"]),
            "label": str(row[label_column]),
            "detail": " / ".join(str(row[column] or "-") for column in detail_columns),
            "deleted_at": row["deleted_at"],
        }
        for row in rows
    ]
