-- barox transfer – Datenbankschema (MySQL 8 / MariaDB 10.4+)
-- Zeichensatz durchgehend utf8mb4.
--
-- WICHTIG (Zero-Knowledge): Der Server speichert ausschließlich Chiffrate und
-- gewickelte Schlüssel. Es liegen KEINE Klartext-Dateien, -Dateinamen,
-- -Nachrichten oder -Schlüssel in der Datenbank oder im Speicher.


-- ---------------------------------------------------------------------------
-- Benutzer (interne Mitarbeiter, via Azure AD authentifiziert)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
    id                    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    azure_oid             VARCHAR(64)  NOT NULL,                 -- Azure AD Object ID (stabil)
    email                 VARCHAR(255) NOT NULL,
    display_name          VARCHAR(255) NOT NULL DEFAULT '',
    role                  ENUM('user','admin') NOT NULL DEFAULT 'user',

    -- Zero-Knowledge-Schlüsselmaterial (Base64). Server kann damit NICHT entschlüsseln.
    public_key            TEXT NULL,                             -- X25519 Public Key
    enc_private_key       TEXT NULL,                             -- mit Passphrase gewickelter Private Key
    kdf_salt              VARCHAR(64) NULL,                      -- Salt für Argon2id (Passphrase)
    recovery_wrapped_key  TEXT NULL,                             -- mit Recovery-Code gewickelter Private Key
    recovery_salt         VARCHAR(64) NULL,                      -- Salt für Argon2id (Recovery-Code)
    key_setup_completed   TINYINT(1) NOT NULL DEFAULT 0,

    created_at            DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_login_at         DATETIME NULL,

    PRIMARY KEY (id),
    UNIQUE KEY uq_users_azure_oid (azure_oid),
    UNIQUE KEY uq_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Transfers (ein Sende-Vorgang mit ein/mehreren Dateien und Empfängern)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS transfers (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    uuid            CHAR(36) NOT NULL,                           -- öffentliche Kennung
    sender_user_id  BIGINT UNSIGNED NOT NULL,

    -- Verschlüsselte Metadaten (Dateinamen, optionale Nachricht), Base64.
    -- Mit dem File-Key des Transfers verschlüsselt -> Server sieht keine Namen.
    enc_metadata    MEDIUMTEXT NULL,

    total_size      BIGINT UNSIGNED NOT NULL DEFAULT 0,          -- Summe Chiffrat-Größen
    max_downloads   INT UNSIGNED NULL,                           -- NULL = unbegrenzt
    expires_at      DATETIME NOT NULL,
    status          ENUM('draft','active','expired','revoked') NOT NULL DEFAULT 'draft',
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    completed_at    DATETIME NULL,

    PRIMARY KEY (id),
    UNIQUE KEY uq_transfers_uuid (uuid),
    KEY idx_transfers_sender (sender_user_id),
    KEY idx_transfers_status_expiry (status, expires_at),
    CONSTRAINT fk_transfers_sender FOREIGN KEY (sender_user_id)
        REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Dateien eines Transfers (jeweils gechunktes Chiffrat im Storage)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS transfer_files (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    transfer_id     BIGINT UNSIGNED NOT NULL,
    file_index      INT UNSIGNED NOT NULL,                       -- Position im Transfer (0-basiert)
    storage_path    VARCHAR(255) NOT NULL,                       -- relativer Pfad im STORAGE_PATH
    ciphertext_size BIGINT UNSIGNED NOT NULL DEFAULT 0,
    chunk_count     INT UNSIGNED NOT NULL DEFAULT 0,
    received_chunks INT UNSIGNED NOT NULL DEFAULT 0,             -- für resumierbaren Upload
    content_hash    CHAR(64) NULL,                               -- SHA-256 des Chiffrats (Integrität)

    PRIMARY KEY (id),
    UNIQUE KEY uq_file_transfer_index (transfer_id, file_index),
    CONSTRAINT fk_files_transfer FOREIGN KEY (transfer_id)
        REFERENCES transfers (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Empfänger eines Transfers (intern = Account, extern = Link + Passwort)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS transfer_recipients (
    id                 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    transfer_id        BIGINT UNSIGNED NOT NULL,
    type               ENUM('internal','external') NOT NULL,
    recipient_user_id  BIGINT UNSIGNED NULL,                     -- bei internal
    recipient_email    VARCHAR(255) NOT NULL,                    -- für Benachrichtigung/Anzeige

    -- File-Key, gewickelt für genau diesen Empfänger (Base64).
    --   internal: crypto_box_seal an public_key des Empfängers
    --   external: mit Argon2id(Passwort)+Link-Secret abgeleitetem Key verschlüsselt
    wrapped_key        TEXT NOT NULL,

    -- Externer Zugriff:
    access_token       CHAR(43) NULL,                            -- URL-sicheres Token (Base64url, 32 Byte)
    password_hash      VARCHAR(255) NULL,                        -- Argon2id-Hash des Zugangs-Passworts
    password_attempts  INT UNSIGNED NOT NULL DEFAULT 0,
    locked_until       DATETIME NULL,

    downloads_count    INT UNSIGNED NOT NULL DEFAULT 0,
    first_accessed_at  DATETIME NULL,
    revoked            TINYINT(1) NOT NULL DEFAULT 0,
    created_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    UNIQUE KEY uq_recipient_access_token (access_token),
    KEY idx_recipients_transfer (transfer_id),
    KEY idx_recipients_user (recipient_user_id),
    CONSTRAINT fk_recipients_transfer FOREIGN KEY (transfer_id)
        REFERENCES transfers (id) ON DELETE CASCADE,
    CONSTRAINT fk_recipients_user FOREIGN KEY (recipient_user_id)
        REFERENCES users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Audit-Log (lückenlose Nachvollziehbarkeit, KEINE Geheimnisse speichern)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS audit_log (
    id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    transfer_id   BIGINT UNSIGNED NULL,
    recipient_id  BIGINT UNSIGNED NULL,
    actor         VARCHAR(255) NOT NULL DEFAULT '',              -- E-Mail oder "system"/"external"
    action        VARCHAR(32) NOT NULL,                          -- create/upload/notify/access/download/expire/revoke/login/key_setup
    ip            VARCHAR(45) NOT NULL DEFAULT '',
    user_agent    VARCHAR(255) NOT NULL DEFAULT '',
    detail        VARCHAR(255) NOT NULL DEFAULT '',
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    KEY idx_audit_transfer (transfer_id),
    KEY idx_audit_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Rate-Limiting (Brute-Force-Schutz, z.B. externe Passwort-Eingaben & Login)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS rate_limits (
    id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    rl_key        VARCHAR(191) NOT NULL,                         -- z.B. "pwverify:<ip>:<token>"
    window_start  DATETIME NOT NULL,
    counter       INT UNSIGNED NOT NULL DEFAULT 0,

    PRIMARY KEY (id),
    UNIQUE KEY uq_rate_key (rl_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
