"""Migrationen der Komponente ``tesm``.

Frische Zeitlinie ab Version 1 -- kein Nachbau der 35 gewachsenen
``ALTER TABLE``-Schritte des Vorgaengers. Inhaltliche Aenderungen am Datenmodell:

* **Kuenstliche Schluessel.** Switche hatten den Hostnamen als Primaerschluessel,
  Geraete die MAC. Ein Umbenennen bzw. ein Tausch der Netzwerkkarte zog damit
  jede Referenz nach sich. Jetzt: ``id`` als Schluessel, Hostname/MAC bleiben
  eindeutig -- aber nur unter den nicht geloeschten Zeilen (Teilindex), damit ein
  Eintrag im Papierkorb den Namen nicht auf Dauer blockiert.
* **Status getrennt von Stammdaten.** Erreichbarkeit, Fehlerzaehler und
  Neustart-Historie liegen in ``device_status``/``restart_events``, nicht in der
  Geraetetabelle. Der Ueberwachungsprozess schreibt damit ausschliesslich in
  Tabellen, die niemand von Hand pflegt.
* **Nachvollziehbare Neustarts.** Der Vorgaenger protokollierte sie
  ausdruecklich *nicht* im Aenderungslog, nur in einer Textdatei. Jetzt gibt es
  ``restart_events`` -- mit Ergebnis, Dauer, Ausloeser und **Methode**: ein
  Neustart ueber den PoE-Port des Switches und einer per ``reboot`` ueber SSH
  sind fuer den Betrieb dasselbe Ereignis und gehoeren in dieselbe Liste.
* **Host-Schluessel des Switches** werden gespeichert und geprueft
  (``switch_host_keys``), statt sie im Terminal blind zu akzeptieren.
"""

from __future__ import annotations

from tesm_core.migrations import Migration

V1 = """
-- Wiederverwendbare Zugangsdaten. Das Passwort liegt AES-GCM-verschluesselt vor,
-- gebunden an "credentials.secret:<id>" als AAD.
CREATE TABLE IF NOT EXISTS credentials (
    id               INTEGER PRIMARY KEY AUTOINCREMENT,
    name             TEXT    NOT NULL COLLATE NOCASE,
    username         TEXT    NOT NULL DEFAULT '',
    secret_encrypted TEXT    NOT NULL DEFAULT '',
    category         TEXT    NOT NULL DEFAULT 'switch',
    description      TEXT    NOT NULL DEFAULT '',
    created_at       TEXT    NOT NULL,
    updated_at       TEXT    NOT NULL,
    updated_by       TEXT    NOT NULL DEFAULT '',
    deleted_at       TEXT
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_credentials_name_live
    ON credentials(name) WHERE deleted_at IS NULL;

CREATE TABLE IF NOT EXISTS switches (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    hostname      TEXT    NOT NULL COLLATE NOCASE,
    ip            TEXT    NOT NULL,
    ssh_port      INTEGER NOT NULL DEFAULT 22,
    credential_id INTEGER REFERENCES credentials(id) ON DELETE SET NULL,
    model         TEXT    NOT NULL DEFAULT '',
    location      TEXT    NOT NULL DEFAULT '',
    notes         TEXT    NOT NULL DEFAULT '',
    last_seen_at  TEXT,
    created_at    TEXT    NOT NULL,
    updated_at    TEXT    NOT NULL,
    updated_by    TEXT    NOT NULL DEFAULT '',
    deleted_at    TEXT
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_switches_hostname_live
    ON switches(hostname) WHERE deleted_at IS NULL;

CREATE TABLE IF NOT EXISTS devices (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    mac           TEXT    NOT NULL COLLATE NOCASE,
    ip            TEXT    NOT NULL,
    name          TEXT    NOT NULL,
    switch_id     INTEGER REFERENCES switches(id) ON DELETE SET NULL,
    port          TEXT    NOT NULL DEFAULT '',
    is_active     INTEGER NOT NULL DEFAULT 1,
    credential_id INTEGER REFERENCES credentials(id) ON DELETE SET NULL,
    ssh_port      INTEGER NOT NULL DEFAULT 22,
    category      TEXT    NOT NULL DEFAULT '',
    location      TEXT    NOT NULL DEFAULT '',
    notes         TEXT    NOT NULL DEFAULT '',
    auto_restart  INTEGER NOT NULL DEFAULT 1,
    created_at    TEXT    NOT NULL,
    updated_at    TEXT    NOT NULL,
    updated_by    TEXT    NOT NULL DEFAULT '',
    deleted_at    TEXT
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_devices_mac_live
    ON devices(mac) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_devices_switch ON devices(switch_id);
CREATE INDEX IF NOT EXISTS idx_devices_active ON devices(is_active, deleted_at);

-- Laufender Zustand der Ueberwachung. Getrennt von den Stammdaten, damit der
-- Ueberwachungsprozess nie in vom Menschen gepflegte Spalten schreibt.
CREATE TABLE IF NOT EXISTS device_status (
    device_id      INTEGER PRIMARY KEY REFERENCES devices(id) ON DELETE CASCADE,
    state          TEXT    NOT NULL DEFAULT 'unknown',
    latency_ms     REAL,
    fail_count     INTEGER NOT NULL DEFAULT 0,
    last_check_at  TEXT,
    last_ok_at     TEXT,
    last_change_at TEXT,
    last_restart_at TEXT,
    restart_count  INTEGER NOT NULL DEFAULT 0,
    detail         TEXT    NOT NULL DEFAULT ''
);

-- Jeder PoE-Vorgang, automatisch oder manuell, mit Ergebnis.
CREATE TABLE IF NOT EXISTS poe_events (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    device_id   INTEGER REFERENCES devices(id) ON DELETE CASCADE,
    ts          TEXT    NOT NULL,
    trigger     TEXT    NOT NULL DEFAULT 'auto',
    actor       TEXT    NOT NULL DEFAULT 'system',
    action      TEXT    NOT NULL,
    result      TEXT    NOT NULL,
    duration_ms INTEGER,
    detail      TEXT    NOT NULL DEFAULT ''
);
CREATE INDEX IF NOT EXISTS idx_poe_events_device ON poe_events(device_id, ts);
CREATE INDEX IF NOT EXISTS idx_poe_events_ts ON poe_events(ts);

-- Gespeicherte Host-Schluessel der Switche. Ein Wechsel wird gemeldet, statt
-- stillschweigend akzeptiert zu werden.
CREATE TABLE IF NOT EXISTS switch_host_keys (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    host        TEXT    NOT NULL,
    port        INTEGER NOT NULL DEFAULT 22,
    key_type    TEXT    NOT NULL,
    fingerprint TEXT    NOT NULL,
    added_at    TEXT    NOT NULL,
    added_by    TEXT    NOT NULL DEFAULT '',
    UNIQUE(host, port, key_type)
);

-- DHCP (Kea)
CREATE TABLE IF NOT EXISTS dhcp_subnets (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    interface   TEXT    NOT NULL,
    range_start TEXT    NOT NULL,
    range_end   TEXT    NOT NULL,
    gateway     TEXT    NOT NULL DEFAULT '',
    dns         TEXT    NOT NULL DEFAULT '',
    comment     TEXT    NOT NULL DEFAULT '',
    enabled     INTEGER NOT NULL DEFAULT 1,
    sort_order  INTEGER NOT NULL DEFAULT 0,
    created_at  TEXT    NOT NULL,
    updated_at  TEXT    NOT NULL
);

CREATE TABLE IF NOT EXISTS dhcp_options (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    code        INTEGER NOT NULL UNIQUE,
    name        TEXT    NOT NULL UNIQUE,
    type        TEXT    NOT NULL,
    description TEXT    NOT NULL DEFAULT '',
    is_standard INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS dhcp_option_values (
    id        INTEGER PRIMARY KEY AUTOINCREMENT,
    option_id INTEGER NOT NULL REFERENCES dhcp_options(id) ON DELETE CASCADE,
    device_id INTEGER REFERENCES devices(id) ON DELETE CASCADE,
    value     TEXT    NOT NULL
);
-- Ein globaler Wert je Option (device_id IS NULL) und hoechstens ein Wert je
-- Option und Geraet. Zwei Teilindizes, weil NULL in SQLite in einem UNIQUE
-- nicht mit sich selbst kollidiert.
CREATE UNIQUE INDEX IF NOT EXISTS idx_dhcp_value_global
    ON dhcp_option_values(option_id) WHERE device_id IS NULL;
CREATE UNIQUE INDEX IF NOT EXISTS idx_dhcp_value_device
    ON dhcp_option_values(option_id, device_id) WHERE device_id IS NOT NULL;

CREATE TABLE IF NOT EXISTS dhcp_reservations (
    id       INTEGER PRIMARY KEY AUTOINCREMENT,
    mac      TEXT NOT NULL UNIQUE COLLATE NOCASE,
    ip       TEXT NOT NULL,
    hostname TEXT NOT NULL DEFAULT '',
    comment  TEXT NOT NULL DEFAULT '',
    created_at TEXT NOT NULL
);

-- Dateifreigaben: Zuordnung Verzeichnisgruppe -> UNC-Pfad.
CREATE TABLE IF NOT EXISTS fileshare_mappings (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    ad_group_dn   TEXT NOT NULL,
    ad_group_name TEXT NOT NULL,
    label         TEXT NOT NULL,
    unc           TEXT NOT NULL,
    created_at    TEXT NOT NULL,
    UNIQUE(ad_group_dn, unc)
);

-- Aktive Mounts je Sitzung. Der Sweep-Prozess raeumt hierueber auf, statt das
-- Dateisystem zu durchsuchen.
CREATE TABLE IF NOT EXISTS fileshare_mounts (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    token       TEXT    NOT NULL UNIQUE,
    user_id     INTEGER REFERENCES users(id) ON DELETE CASCADE,
    session_id  TEXT    NOT NULL DEFAULT '',
    label       TEXT    NOT NULL,
    unc         TEXT    NOT NULL,
    mount_path  TEXT    NOT NULL,
    created_at  TEXT    NOT NULL,
    expires_at  TEXT    NOT NULL,
    released_at TEXT
);
CREATE INDEX IF NOT EXISTS idx_fileshare_mounts_user ON fileshare_mounts(user_id, released_at);
"""


def seed_dhcp_options(conn) -> None:  # type: ignore[no-untyped-def]
    """Legt den kuratierten Katalog der DHCP-Standardoptionen an.

    56 Eintraege, gegen eine echte Kea-Instanz verifiziert. Bewusst *nicht*
    enthalten:

    * Optionen mit eigenen Feldern in der Oberflaeche: Router (3), DNS (6),
      Domain-Name (15), Lease-Zeiten (51/58/59).
    * ``host-name`` (12): Kea beantwortet die Anfrage bereits ueber das
      ``hostname``-Feld der Reservierung. Ein zusaetzlicher Override ist laut
      Kea-Dokumentation eine Widerspruchsquelle -- der Vorgaenger hatte den
      Eintrag zeitweise drin und musste ihn per Migration wieder entfernen.
    * Client- und protokollinterne Optionen.
    """
    from .services.kea import STANDARD_OPTIONS

    conn.executemany(
        "INSERT INTO dhcp_options (code, name, type, description, is_standard) "
        "VALUES (?,?,?,?,1) ON CONFLICT(code) DO NOTHING",
        STANDARD_OPTIONS,
    )


# Version 3 -----------------------------------------------------------------
#
# ``poe_events`` hiess so, weil es anfangs nur PoE-Neustarts gab. Inzwischen
# startet auch die Wartung Geraete neu -- per ``reboot`` ueber SSH. Beides ist
# aus Betriebssicht dasselbe Ereignis ("dieses Geraet wurde neu gestartet"),
# und beides gehoert in dieselbe Liste. Ein Name, der nur die halbe Wahrheit
# sagt, faellt spaetestens dem naechsten auf die Fuesse, der die Datenbank
# liest.
#
# Die Spalte ``method`` haelt fest, *wie* neu gestartet wurde. Bestehende
# Zeilen sind ausnahmslos PoE-Vorgaenge -- daher der Vorgabewert.
V3 = """
ALTER TABLE poe_events RENAME TO restart_events;
ALTER TABLE restart_events ADD COLUMN method TEXT NOT NULL DEFAULT 'poe';

DROP INDEX IF EXISTS idx_poe_events_device;
DROP INDEX IF EXISTS idx_poe_events_ts;
CREATE INDEX IF NOT EXISTS idx_restart_events_device ON restart_events(device_id, ts);
CREATE INDEX IF NOT EXISTS idx_restart_events_ts ON restart_events(ts);
CREATE INDEX IF NOT EXISTS idx_restart_events_method ON restart_events(method, ts);
"""


TESM_MIGRATIONS: tuple[Migration, ...] = (
    Migration(version=1, name="baseline", sql=V1),
    Migration(version=2, name="seed-dhcp-options", fn=seed_dhcp_options),
    Migration(version=3, name="restart-events", sql=V3),
)
