-- ============================================================================
-- barox Security Vulnerability Reporting Tool
-- MySQL / MariaDB Schema
--
-- Zeichensatz utf8mb4 für vollständige Unicode-Unterstützung.
-- Alle Zeitstempel in UTC (Applikation setzt time_zone = '+00:00').
--
-- WICHTIG: Dieses Skript legt bewusst KEINE Datenbank an. Die Zieldatenbank
-- muss dem Wert DB_NAME in config/.env entsprechen und wird beim Import
-- angegeben:
--   mysql -e "CREATE DATABASE meine_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
--   mysql meine_db < database/schema.sql
-- ============================================================================

-- ----------------------------------------------------------------------------
-- users – interne Bearbeiter (Admin-Bereich)
-- ----------------------------------------------------------------------------
CREATE TABLE users (
    id              INT UNSIGNED     NOT NULL AUTO_INCREMENT,
    username        VARCHAR(64)      NOT NULL,
    display_name    VARCHAR(128)     NOT NULL,
    email           VARCHAR(190)     NOT NULL,
    -- Anmeldung erfolgt per Microsoft 365 (Entra ID); kein lokales Passwort.
    -- Spalte bleibt für optionale Break-Glass-Zwecke bestehen, ist aber NULL.
    password_hash   VARCHAR(255)     NULL,
    -- Entra-Objekt-ID (oid), nach erstem erfolgreichen Login gebunden
    entra_oid       VARCHAR(64)      NULL,
    role            ENUM('admin','analyst') NOT NULL DEFAULT 'analyst',
    is_active       TINYINT(1)       NOT NULL DEFAULT 1,
    -- Brute-Force-Schutz: Zähler + Sperre auf Kontoebene
    failed_logins   INT UNSIGNED     NOT NULL DEFAULT 0,
    locked_until    DATETIME         NULL,
    last_login_at   DATETIME         NULL,
    created_at      DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP
                                     ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_users_username (username),
    UNIQUE KEY uq_users_email (email),
    UNIQUE KEY uq_users_entra_oid (entra_oid)
) ENGINE = InnoDB;

-- ----------------------------------------------------------------------------
-- vulnerability_reports – zentrale Meldungen inkl. CRA-Felder
-- ----------------------------------------------------------------------------
CREATE TABLE vulnerability_reports (
    id                      INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    -- Öffentliche Fallnummer, z. B. BSEC-2026-0001
    case_number             VARCHAR(20)   NOT NULL,

    -- ------------------------- Meldung (öffentlich) -------------------------
    -- Produktangaben gelten für die gesamte Meldung; die einzelnen
    -- Schwachstellen liegen als 1..n Findings in report_findings.
    product                 VARCHAR(190)  NOT NULL,
    firmware_version        VARCHAR(100)  NOT NULL,
    -- Aggregat über alle Findings (höchster Wert) – für Triage/Dashboard/Filter
    reporter_exploited      ENUM('yes','no','unknown') NOT NULL DEFAULT 'unknown',
    reporter_severity       ENUM('low','medium','high','critical') NOT NULL,

    -- Kontakt (optional, anonym möglich)
    is_anonymous            TINYINT(1)    NOT NULL DEFAULT 0,
    reporter_name           VARCHAR(190)  NULL,
    reporter_company        VARCHAR(190)  NULL,
    reporter_email          VARCHAR(190)  NULL,
    reporter_phone          VARCHAR(64)   NULL,
    -- Name/Handle für die öffentliche Danksagung (Credit), falls gewünscht
    credit_name             VARCHAR(190)  NULL,

    -- Optionale Angaben
    serial_number           VARCHAR(100)  NULL,
    article_number          VARCHAR(100)  NULL,
    environment             TEXT          NULL,
    reference_links         TEXT          NULL,
    -- Vom Melder gesetzte Offenlegungs-/Embargo-Frist (Coordinated Disclosure)
    disclosure_deadline     DATE          NULL,
    disclosure_note         VARCHAR(255)  NULL COMMENT 'z. B. Disclosure-Policy des Melders',

    -- ------------------------- interne Bearbeitung --------------------------
    status                  ENUM('new','in_review','confirmed','not_reproducible',
                                 'fix_in_progress','fix_released','closed')
                            NOT NULL DEFAULT 'new',
    -- interne Einstufung (kann von Melder-Einschätzung abweichen)
    internal_severity       ENUM('low','medium','high','critical') NULL,
    assigned_to             INT UNSIGNED  NULL,
    due_date                DATE          NULL,

    -- ------------------------- CRA-relevante Felder -------------------------
    -- Art. 14 CRA: aktiv ausgenutzte Schwachstelle / schwerer Vorfall
    cra_actively_exploited  ENUM('yes','no','unknown') NOT NULL DEFAULT 'unknown',
    cra_severe_incident     ENUM('yes','no','unknown') NOT NULL DEFAULT 'unknown',
    cra_24h_required        TINYINT(1)    NOT NULL DEFAULT 0,
    cra_72h_required        TINYINT(1)    NOT NULL DEFAULT 0,
    cra_aware_at            DATETIME      NULL COMMENT 'Kenntnisnahme (Start 24h/72h-Frist)',
    cra_classified_at       DATETIME      NULL COMMENT 'Interne Einstufung',
    cra_authority_notified_at DATETIME    NULL COMMENT 'Behördliche Meldung (ENISA/CSIRT)',
    cra_fix_available_at    DATETIME      NULL COMMENT 'Korrekturmassnahme verfügbar',
    cra_final_report_at     DATETIME      NULL COMMENT 'Abschlussbericht eingereicht',
    cra_notes               TEXT          NULL COMMENT 'Notizen ENISA/CSIRT-Kommunikation',

    -- ------------------------- Metadaten -------------------------------------
    -- DSGVO: IP nur als HMAC-Hash (Missbrauchsabwehr), keine Klartext-IP
    submitted_ip_hash       CHAR(64)      NULL,
    created_at              DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at              DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP
                                          ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    UNIQUE KEY uq_reports_case_number (case_number),
    KEY idx_reports_status (status),
    KEY idx_reports_severity (reporter_severity),
    KEY idx_reports_exploited (cra_actively_exploited),
    KEY idx_reports_product (product),
    KEY idx_reports_created (created_at),
    CONSTRAINT fk_reports_assigned FOREIGN KEY (assigned_to)
        REFERENCES users (id) ON DELETE SET NULL
) ENGINE = InnoDB;

-- ----------------------------------------------------------------------------
-- report_findings – einzelne Schwachstellen einer Meldung (1..n je Meldung)
-- Eine Meldung kann mehrere, ggf. auf dieselbe Ursache zurückgehende
-- Schwachstellen enthalten – je mit eigener CWE/CVSS/CVE-Zuordnung.
-- ----------------------------------------------------------------------------
CREATE TABLE report_findings (
    id                    INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    report_id             INT UNSIGNED  NOT NULL,
    sort_order            SMALLINT UNSIGNED NOT NULL DEFAULT 0,

    -- ------------------------- Angaben des Melders --------------------------
    title                 VARCHAR(190)  NOT NULL COMMENT 'Art/Titel der Schwachstelle',
    cwe                   VARCHAR(190)  NULL COMMENT 'z. B. CWE-200 oder CWE-257, CWE-522',
    reporter_advisory_id  VARCHAR(100)  NULL COMMENT 'Advisory-ID des Melders, z. B. SA-2026-010',
    description           TEXT          NOT NULL,
    reproduction_steps    TEXT          NOT NULL,
    impact                TEXT          NOT NULL,
    reporter_exploited    ENUM('yes','no','unknown') NOT NULL DEFAULT 'unknown',
    reporter_severity     ENUM('low','medium','high','critical') NOT NULL,
    -- CVSS-Angabe des Melders (Version, Vektor, Basis-Score)
    cvss_version          VARCHAR(8)    NULL COMMENT 'z. B. 4.0 oder 3.1',
    cvss_vector           VARCHAR(255)  NULL,
    cvss_score            DECIMAL(3,1)  NULL,

    -- ------------------------- interne Einstufung ---------------------------
    internal_severity     ENUM('low','medium','high','critical') NULL,
    internal_cvss_vector  VARCHAR(255)  NULL,
    internal_cvss_score   DECIMAL(3,1)  NULL,

    -- CVE-Zuordnung (Melder-Angabe, intern durch CNA/Bearbeiter ergänzt)
    cve_status            ENUM('yes','no','unknown') NOT NULL DEFAULT 'unknown',
    cve_id                VARCHAR(64)   NULL,

    created_at            DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at            DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP
                                        ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_findings_report (report_id),
    KEY idx_findings_cve (cve_id),
    CONSTRAINT fk_findings_report FOREIGN KEY (report_id)
        REFERENCES vulnerability_reports (id) ON DELETE CASCADE
) ENGINE = InnoDB;

-- ----------------------------------------------------------------------------
-- report_attachments – Datei-Metadaten (Dateien liegen ausserhalb des Webroots)
-- ----------------------------------------------------------------------------
CREATE TABLE report_attachments (
    id                INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    report_id         INT UNSIGNED  NOT NULL,
    original_name     VARCHAR(255)  NOT NULL,
    -- randomisierter Dateiname im Storage, ohne ausführbare Endung
    stored_name       VARCHAR(80)   NOT NULL,
    mime_type         VARCHAR(120)  NOT NULL,
    size_bytes        BIGINT UNSIGNED NOT NULL,
    sha256            CHAR(64)      NOT NULL,
    -- ClamAV-Hook: pending = noch nicht gescannt, skipped = Scanner deaktiviert
    virus_scan_status ENUM('pending','clean','infected','skipped')
                      NOT NULL DEFAULT 'skipped',
    created_at        DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_attachments_stored (stored_name),
    KEY idx_attachments_report (report_id),
    CONSTRAINT fk_attachments_report FOREIGN KEY (report_id)
        REFERENCES vulnerability_reports (id) ON DELETE CASCADE
) ENGINE = InnoDB;

-- ----------------------------------------------------------------------------
-- report_comments – interne Kommentare
-- ----------------------------------------------------------------------------
CREATE TABLE report_comments (
    id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
    report_id   INT UNSIGNED NOT NULL,
    user_id     INT UNSIGNED NULL,
    comment     TEXT         NOT NULL,
    created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_comments_report (report_id),
    CONSTRAINT fk_comments_report FOREIGN KEY (report_id)
        REFERENCES vulnerability_reports (id) ON DELETE CASCADE,
    CONSTRAINT fk_comments_user FOREIGN KEY (user_id)
        REFERENCES users (id) ON DELETE SET NULL
) ENGINE = InnoDB;

-- ----------------------------------------------------------------------------
-- report_status_history – Zeitstempel aller Statusänderungen
-- ----------------------------------------------------------------------------
CREATE TABLE report_status_history (
    id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
    report_id   INT UNSIGNED NOT NULL,
    user_id     INT UNSIGNED NULL,
    old_status  VARCHAR(32)  NULL,
    new_status  VARCHAR(32)  NOT NULL,
    note        TEXT         NULL,
    created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_history_report (report_id),
    CONSTRAINT fk_history_report FOREIGN KEY (report_id)
        REFERENCES vulnerability_reports (id) ON DELETE CASCADE,
    CONSTRAINT fk_history_user FOREIGN KEY (user_id)
        REFERENCES users (id) ON DELETE SET NULL
) ENGINE = InnoDB;

-- ----------------------------------------------------------------------------
-- audit_log – revisionssichere Protokollierung aller relevanten Aktionen
-- ----------------------------------------------------------------------------
CREATE TABLE audit_log (
    id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id      INT UNSIGNED NULL COMMENT 'NULL = System / öffentliche Aktion',
    action       VARCHAR(64)  NOT NULL COMMENT 'z. B. report.created, case.status_changed',
    entity_type  VARCHAR(64)  NOT NULL,
    entity_id    INT UNSIGNED NULL,
    details      TEXT         NULL COMMENT 'JSON mit Alt-/Neuwerten',
    ip_hash      CHAR(64)     NULL,
    created_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_audit_entity (entity_type, entity_id),
    KEY idx_audit_action (action),
    KEY idx_audit_created (created_at),
    CONSTRAINT fk_audit_user FOREIGN KEY (user_id)
        REFERENCES users (id) ON DELETE SET NULL
) ENGINE = InnoDB;

-- ----------------------------------------------------------------------------
-- settings – Key/Value-Konfiguration (u. a. Fallnummern-Sequenz pro Jahr)
-- ----------------------------------------------------------------------------
CREATE TABLE settings (
    setting_key   VARCHAR(64)  NOT NULL,
    setting_value VARCHAR(255) NOT NULL,
    updated_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
                               ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (setting_key)
) ENGINE = InnoDB;

INSERT INTO settings (setting_key, setting_value) VALUES
    ('upload_max_mb',        '10'),   -- max. Grösse pro Datei in MB
    ('upload_max_files',     '5'),    -- max. Anzahl Dateien pro Meldung
    ('report_rate_limit',    '5'),    -- max. Meldungen pro IP pro Stunde
    ('case_number_prefix',   'BSEC');

-- ----------------------------------------------------------------------------
-- rate_limits – Zusatztabelle für Rate Limiting (öffentliches Formular, Login)
-- Nicht in der Basisanforderung gelistet, aber für Brute-Force-Schutz und
-- Spam-Abwehr erforderlich. Wird per Cronjob bereinigt (bin/cleanup.php).
-- ----------------------------------------------------------------------------
CREATE TABLE rate_limits (
    bucket        VARCHAR(120) NOT NULL COMMENT 'aktion:ip_hash',
    window_start  DATETIME     NOT NULL,
    hits          INT UNSIGNED NOT NULL DEFAULT 1,
    PRIMARY KEY (bucket)
) ENGINE = InnoDB;
