"""Migrationen der Komponente ``core``: Benutzer, Gruppen, Sitzungen, Audit, Settings.

Diese Tabellen sind in beiden Anwendungen identisch. Neue Aenderungen kommen
als **neue** Migration mit neuer Versionsnummer dazu -- bestehende Migrationen
werden nie editiert (die Pruefsummenwarnung des Runners weist darauf hin).
"""

from __future__ import annotations

from .migrations import Migration

V1_BASELINE = """
-- Benutzerkonten. 'local' = eigenes Passwort, 'ldap' = Schattenkonto aus dem
-- Verzeichnisdienst (Passwort wird dort geprueft, nie hier gespeichert).
CREATE TABLE IF NOT EXISTS users (
    id                   INTEGER PRIMARY KEY AUTOINCREMENT,
    username             TEXT    NOT NULL COLLATE NOCASE,
    password_hash        TEXT    NOT NULL DEFAULT '',
    is_admin             INTEGER NOT NULL DEFAULT 0,
    is_locked            INTEGER NOT NULL DEFAULT 0,
    auth_source          TEXT    NOT NULL DEFAULT 'local',
    first_name           TEXT    NOT NULL DEFAULT '',
    last_name            TEXT    NOT NULL DEFAULT '',
    email                TEXT    NOT NULL DEFAULT '',
    avatar_filename      TEXT    NOT NULL DEFAULT '',
    totp_secret          TEXT    NOT NULL DEFAULT '',
    totp_confirmed_at    TEXT,
    must_change_password INTEGER NOT NULL DEFAULT 0,
    password_changed_at  TEXT,
    last_login_at        TEXT,
    created_at           TEXT    NOT NULL,
    updated_at           TEXT    NOT NULL,
    deleted_at           TEXT
);
-- Eindeutigkeit nur unter den *nicht* geloeschten Konten: ein Name im
-- Papierkorb blockiert die Neuanlage nicht dauerhaft, solange die Anwendung
-- den Konflikt bewusst behandelt.
CREATE UNIQUE INDEX IF NOT EXISTS idx_users_username_live
    ON users(username) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_users_deleted ON users(deleted_at);

CREATE TABLE IF NOT EXISTS groups (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    name        TEXT    NOT NULL COLLATE NOCASE,
    description TEXT    NOT NULL DEFAULT '',
    is_system   INTEGER NOT NULL DEFAULT 0,
    is_default  INTEGER NOT NULL DEFAULT 0,
    preset_key  TEXT    NOT NULL DEFAULT '',
    created_at  TEXT    NOT NULL,
    updated_at  TEXT    NOT NULL,
    deleted_at  TEXT
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_groups_name_live
    ON groups(name) WHERE deleted_at IS NULL;

CREATE TABLE IF NOT EXISTS group_permissions (
    group_id   INTEGER NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    permission TEXT    NOT NULL,
    PRIMARY KEY (group_id, permission)
);

CREATE TABLE IF NOT EXISTS user_groups (
    user_id  INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    group_id INTEGER NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    PRIMARY KEY (user_id, group_id)
);
CREATE INDEX IF NOT EXISTS idx_user_groups_group ON user_groups(group_id);

-- Einmal-Codes fuer den Verlust des Authenticators. Gespeichert wird nur ein
-- Argon2-Hash -- ein Datenbankleck gibt die Codes nicht her.
CREATE TABLE IF NOT EXISTS user_recovery_codes (
    id        INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id   INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    code_hash TEXT    NOT NULL,
    created_at TEXT   NOT NULL,
    used_at   TEXT
);
CREATE INDEX IF NOT EXISTS idx_recovery_user ON user_recovery_codes(user_id);

CREATE TABLE IF NOT EXISTS user_sessions (
    id                  TEXT    PRIMARY KEY,
    user_id             INTEGER REFERENCES users(id) ON DELETE CASCADE,
    created_at          TEXT    NOT NULL,
    last_seen_at        TEXT    NOT NULL,
    idle_expires_at     TEXT    NOT NULL,
    absolute_expires_at TEXT    NOT NULL,
    reauth_at           TEXT,
    ip                  TEXT    NOT NULL DEFAULT '',
    user_agent          TEXT    NOT NULL DEFAULT '',
    data                TEXT    NOT NULL DEFAULT '{}',
    revoked_at          TEXT,
    revoked_reason      TEXT
);
CREATE INDEX IF NOT EXISTS idx_sessions_user ON user_sessions(user_id, revoked_at);
CREATE INDEX IF NOT EXISTS idx_sessions_idle ON user_sessions(idle_expires_at);

-- Sliding-Window-Log der Ratenbegrenzung (Anmeldeversuche, Portal-Anfragen, API).
CREATE TABLE IF NOT EXISTS rate_events (
    id      INTEGER PRIMARY KEY AUTOINCREMENT,
    bucket  TEXT NOT NULL,
    subject TEXT NOT NULL,
    ts      TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_rate_lookup ON rate_events(bucket, subject, ts);

CREATE TABLE IF NOT EXISTS settings (
    key        TEXT PRIMARY KEY,
    value      TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    updated_by TEXT NOT NULL DEFAULT ''
);

-- Aenderungsprotokoll mit Hash-Kette: jede Zeile bindet den Hash ihrer
-- Vorgaengerin ein. Eine nachtraeglich geloeschte oder veraenderte Zeile bricht
-- die Kette und wird von der Pruefroutine gefunden.
CREATE TABLE IF NOT EXISTS audit_log (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    ts         TEXT NOT NULL,
    actor      TEXT NOT NULL,
    actor_id   INTEGER,
    action     TEXT NOT NULL,
    target     TEXT NOT NULL DEFAULT '',
    detail     TEXT NOT NULL DEFAULT '',
    ip         TEXT NOT NULL DEFAULT '',
    request_id TEXT NOT NULL DEFAULT '',
    severity   TEXT NOT NULL DEFAULT 'info',
    prev_hash  TEXT NOT NULL DEFAULT '',
    hash       TEXT NOT NULL DEFAULT ''
);
CREATE INDEX IF NOT EXISTS idx_audit_ts ON audit_log(ts);
CREATE INDEX IF NOT EXISTS idx_audit_actor ON audit_log(actor);
CREATE INDEX IF NOT EXISTS idx_audit_action ON audit_log(action);

-- Dienstkonten (z. B. LDAP-Bind). Das Passwort liegt AES-GCM-verschluesselt vor,
-- gebunden an den Verwendungszweck als AAD.
CREATE TABLE IF NOT EXISTS service_accounts (
    id                 INTEGER PRIMARY KEY AUTOINCREMENT,
    purpose            TEXT NOT NULL UNIQUE,
    username           TEXT NOT NULL DEFAULT '',
    password_encrypted TEXT NOT NULL DEFAULT '',
    updated_at         TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS ldap_group_mappings (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    ad_group_dn   TEXT    NOT NULL UNIQUE,
    ad_group_name TEXT    NOT NULL,
    app_group_id  INTEGER NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    created_at    TEXT    NOT NULL
);
"""

V2_TRUSTED_KEYS = """
-- Gepinnte Aussteller-Schluessel der Lizenzpruefung (Trust on First Use bzw.
-- im Build mitgeliefert). Der Vorgaenger vertraute allein dem in der
-- Lizenzdatei eingebetteten Schluessel.
CREATE TABLE IF NOT EXISTS trusted_issuer_keys (
    key_id     TEXT PRIMARY KEY,
    public_key TEXT NOT NULL,
    label      TEXT NOT NULL DEFAULT '',
    source     TEXT NOT NULL DEFAULT 'tofu',
    added_at   TEXT NOT NULL,
    added_by   TEXT NOT NULL DEFAULT ''
);
"""

V3_JOBS = """
-- Persistente Auftragsverfolgung fuer Hintergrundlaeufe (Wartung, Import,
-- Berichte). Im Vorgaenger lebte dieser Zustand ausschliesslich im
-- Prozessspeicher und war nach einem Dienst-Neustart weg -- inklusive der
-- Information, ob ein Lauf ueberhaupt stattgefunden hat.
CREATE TABLE IF NOT EXISTS jobs (
    id           TEXT PRIMARY KEY,
    kind         TEXT NOT NULL,
    subject      TEXT NOT NULL DEFAULT '',
    state        TEXT NOT NULL DEFAULT 'pending',
    progress     INTEGER NOT NULL DEFAULT 0,
    message      TEXT NOT NULL DEFAULT '',
    output       TEXT NOT NULL DEFAULT '',
    started_by   TEXT NOT NULL DEFAULT '',
    created_at   TEXT NOT NULL,
    updated_at   TEXT NOT NULL,
    finished_at  TEXT
);
CREATE INDEX IF NOT EXISTS idx_jobs_kind_state ON jobs(kind, state);
CREATE INDEX IF NOT EXISTS idx_jobs_created ON jobs(created_at);
"""

CORE_MIGRATIONS: tuple[Migration, ...] = (
    Migration(version=1, name="baseline", sql=V1_BASELINE),
    Migration(version=2, name="trusted-issuer-keys", sql=V2_TRUSTED_KEYS),
    Migration(version=3, name="jobs", sql=V3_JOBS),
)
