-- ══════════════════════════════════════════════════════════════════════════════
--  EEG-Assistent · Lizenzierungs-Schema (SQLite)
--  Wird von licensing.py beim ersten Start automatisch angelegt.
--  Alle Datumsfelder als ISO-8601-Text (UTC).
-- ══════════════════════════════════════════════════════════════════════════════

PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;           -- erlaubt gleichzeitige Leser
PRAGMA synchronous  = NORMAL;        -- guter Kompromiss für eine Single-File-DB


-- ── Zugangscodes / Kundenkonten ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS accounts (
    uid                TEXT PRIMARY KEY,        -- z.B. 'EEGA-XXXX-XXXX-XXXX'
    customer_name      TEXT NOT NULL,
    customer_email     TEXT,
    device_name        TEXT,                    -- vom Admin vergebener Identifier
    initial_tokens     INTEGER NOT NULL,
    remaining_tokens   INTEGER NOT NULL CHECK (remaining_tokens >= 0),
    created_at         TEXT NOT NULL,           -- ISO-8601 UTC
    first_used_at      TEXT,                    -- erst gesetzt beim ersten Download
    expires_at         TEXT,                    -- first_used_at + 3 Jahre
    status             TEXT NOT NULL DEFAULT 'active'
                       CHECK (status IN ('active','disabled','expired')),
    initial_fingerprint TEXT,                   -- Fingerprint des ersten Logins
    fingerprint_mismatches INTEGER NOT NULL DEFAULT 0,
    notes              TEXT
);
CREATE INDEX IF NOT EXISTS idx_accounts_status ON accounts(status);


-- ── Append-only Nutzungsprotokoll ────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS usage_log (
    id                 INTEGER PRIMARY KEY AUTOINCREMENT,
    account_uid        TEXT NOT NULL REFERENCES accounts(uid) ON DELETE RESTRICT,
    timestamp          TEXT NOT NULL,           -- ISO-8601 UTC
    ip                 TEXT,
    user_agent         TEXT,
    fingerprint_hash   TEXT,
    fingerprint_match  INTEGER,                 -- 1=match, 0=mismatch, NULL=first-use
    session_id         TEXT,
    periode            TEXT,
    modules            TEXT,                    -- JSON-Liste, z.B. '["pdfs","sepa"]'
    tokens_consumed    INTEGER NOT NULL,
    remaining_after    INTEGER NOT NULL,
    event_type         TEXT NOT NULL DEFAULT 'download'
                       CHECK (event_type IN ('download','adjust','login','login_fail'))
);
CREATE INDEX IF NOT EXISTS idx_usage_account ON usage_log(account_uid, timestamp);
CREATE INDEX IF NOT EXISTS idx_usage_ts      ON usage_log(timestamp);


-- ── Aktive Sessions (verhindert Parallel-Nutzung einer UID) ──────────────────
CREATE TABLE IF NOT EXISTS active_sessions (
    uid                TEXT PRIMARY KEY REFERENCES accounts(uid) ON DELETE CASCADE,
    session_id         TEXT NOT NULL,
    started_at         TEXT NOT NULL,
    expires_at         TEXT NOT NULL,           -- 2h TTL, verhindert tote Locks
    ip                 TEXT,
    fingerprint_hash   TEXT
);
CREATE INDEX IF NOT EXISTS idx_sessions_expires ON active_sessions(expires_at);


-- ── Paket-Katalog (Admin-editierbar) ─────────────────────────────────────────
CREATE TABLE IF NOT EXISTS packages (
    key                TEXT PRIMARY KEY,        -- 'pdfs','sepa','kassabuch','report'
    name               TEXT NOT NULL,           -- Anzeigename im Frontend
    cost               INTEGER NOT NULL CHECK (cost >= 0),
    is_active          INTEGER NOT NULL DEFAULT 1,
    sort_order         INTEGER NOT NULL DEFAULT 0,
    description        TEXT
);

-- Default-Pakete (nur beim allerersten Start eingefügt)
INSERT OR IGNORE INTO packages (key, name, cost, sort_order, description) VALUES
    ('pdfs',              'PDF-Rechnungen',        16, 1, 'Alle Rechnungen als einzelne PDF-Dateien'),
    ('sepa',              'SEPA-Dateien',           8, 2, 'Lastschrift- und Gutschrift-XML für die Bank'),
    ('kassabuch',         'Kassabuch',              2, 3, 'Excel-Kassabuch mit allen Buchungen'),
    ('report',            'Gemeinschaftsbericht',  16, 4, 'PDF-Report mit Kennzahlen der EEG'),
    ('rechnungsversand',  'Rechnungsversand',       8, 5, 'Rechnungen automatisch per E-Mail versenden'),
    ('abrechnungsverein', 'Abrechnungsverein',     16, 6, 'Abbuchung über Verein zur Abrechnung von Energiegemeinschaften');


-- ── Fehlversuch-Log für Rate-Limit ───────────────────────────────────────────
CREATE TABLE IF NOT EXISTS failed_logins (
    id                 INTEGER PRIMARY KEY AUTOINCREMENT,
    ip                 TEXT NOT NULL,
    uid_attempted      TEXT,                    -- evtl. nur Anfang, falls geloggt
    timestamp          TEXT NOT NULL,
    reason             TEXT                     -- 'invalid','expired','disabled','no_tokens'
);
CREATE INDEX IF NOT EXISTS idx_failed_ip_ts ON failed_logins(ip, timestamp);


-- ── Admin-Accounts (Web-Oberfläche in Phase 6) ──────────────────────────────
CREATE TABLE IF NOT EXISTS admins (
    username           TEXT PRIMARY KEY,
    pw_hash            TEXT NOT NULL,           -- werkzeug.security PBKDF2
    created_at         TEXT NOT NULL,
    last_login_at      TEXT,
    last_login_ip      TEXT
);


-- ── Admin-Audit (wer hat wann welchen Account angelegt/geändert) ─────────────
CREATE TABLE IF NOT EXISTS admin_audit (
    id                 INTEGER PRIMARY KEY AUTOINCREMENT,
    admin_user         TEXT NOT NULL,
    timestamp          TEXT NOT NULL,
    action             TEXT NOT NULL,           -- 'create_account','adjust','disable',...
    target_uid         TEXT,
    details            TEXT                     -- freier Kommentar / JSON
);
CREATE INDEX IF NOT EXISTS idx_admin_audit_ts ON admin_audit(timestamp);
