-- ============================================================================
--  barox Switch Test Companion – Datenbankschema (MySQL / MariaDB)
--  Zeichensatz: utf8mb4. Engine: InnoDB (Fremdschluessel, Transaktionen).
-- ============================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------------------
--  Rollen & Benutzer
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS roles (
    id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name        VARCHAR(32)  NOT NULL,
    label       VARCHAR(64)  NOT NULL,
    description VARCHAR(255)  NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_roles_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS users (
    id             INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name           VARCHAR(120) NOT NULL,
    email          VARCHAR(190) NOT NULL,
    password_hash  VARCHAR(255)     NULL,          -- NULL bei reinen MS365-Konten
    role           VARCHAR(32)  NOT NULL DEFAULT 'tester',
    auth_provider  ENUM('local','ms365') NOT NULL DEFAULT 'local',
    ms365_oid      VARCHAR(190)     NULL,          -- Object-ID aus Entra ID
    is_active      TINYINT(1)   NOT NULL DEFAULT 1,
    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_email (email),
    UNIQUE KEY uq_users_ms365 (ms365_oid),
    KEY idx_users_role (role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
--  Vorlagen-Versionierung
--  Jeder Excel-Import erzeugt eine neue template_version. Beim Anlegen eines
--  Testlaufs werden die Vorlagen der aktiven Version in den Testlauf KOPIERT.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS template_versions (
    id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
    version       VARCHAR(32)  NOT NULL,           -- z. B. 2026.07.01-1
    source_file   VARCHAR(255)     NULL,
    is_active     TINYINT(1)   NOT NULL DEFAULT 0, -- genau eine aktive Version
    notes         VARCHAR(255)     NULL,
    imported_by   INT UNSIGNED     NULL,
    created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_tv_version (version),
    KEY idx_tv_active (is_active),
    CONSTRAINT fk_tv_user FOREIGN KEY (imported_by) REFERENCES users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS test_categories (
    id                  INT UNSIGNED NOT NULL AUTO_INCREMENT,
    template_version_id INT UNSIGNED NOT NULL,
    name                VARCHAR(64)  NOT NULL,
    sort_order          INT          NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    KEY idx_cat_version (template_version_id),
    CONSTRAINT fk_cat_version FOREIGN KEY (template_version_id) REFERENCES template_versions (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS test_templates (
    id                  INT UNSIGNED NOT NULL AUTO_INCREMENT,
    template_version_id INT UNSIGNED NOT NULL,
    category            VARCHAR(64)  NOT NULL,
    name                VARCHAR(255) NOT NULL,
    description         TEXT             NULL,
    expected_result     TEXT             NULL,
    default_priority    ENUM('Kritisch','Hoch','Mittel','Niedrig') NOT NULL DEFAULT 'Mittel',
    is_security         TINYINT(1)   NOT NULL DEFAULT 0,  -- sicherheitsrelevant (Bewertungslogik)
    notes               TEXT             NULL,            -- Original-Notiz aus Excel
    sort_order          INT          NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    KEY idx_tpl_version (template_version_id),
    KEY idx_tpl_category (category),
    CONSTRAINT fk_tpl_version FOREIGN KEY (template_version_id) REFERENCES template_versions (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sfp_modules (
    id                  INT UNSIGNED NOT NULL AUTO_INCREMENT,
    template_version_id INT UNSIGNED NOT NULL,
    model               VARCHAR(64)  NOT NULL,           -- z. B. AC-SFP-LX-E-10
    description         VARCHAR(255)     NULL,
    sort_order          INT          NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    KEY idx_sfp_version (template_version_id),
    CONSTRAINT fk_sfp_version FOREIGN KEY (template_version_id) REFERENCES template_versions (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS scenario_templates (
    id                  INT UNSIGNED NOT NULL AUTO_INCREMENT,
    template_version_id INT UNSIGNED NOT NULL,
    name                VARCHAR(255) NOT NULL,
    description         TEXT             NULL,
    objective           TEXT             NULL,           -- Testziel
    prerequisites       TEXT             NULL,           -- Voraussetzungen
    required_devices    TEXT             NULL,
    required_config     TEXT             NULL,
    test_steps          TEXT             NULL,
    expected_result     TEXT             NULL,
    sort_order          INT          NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    KEY idx_scen_version (template_version_id),
    CONSTRAINT fk_scen_version FOREIGN KEY (template_version_id) REFERENCES template_versions (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
--  Testlaeufe (Stammdaten)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS test_runs (
    id                  INT UNSIGNED NOT NULL AUTO_INCREMENT,
    report_id           VARCHAR(32)  NOT NULL,           -- z. B. BAROX-STC-2026-0001
    template_version_id INT UNSIGNED     NULL,           -- verwendete Vorlagenversion (Snapshot-Quelle)
    status              ENUM('Entwurf','In Arbeit','Abgeschlossen','Archiviert') NOT NULL DEFAULT 'Entwurf',
    wizard_step         VARCHAR(32)  NOT NULL DEFAULT 'stammdaten',

    -- Switch-Stammdaten
    project_name        VARCHAR(190) NOT NULL,
    vendor              VARCHAR(190)     NULL,
    model               VARCHAR(190) NOT NULL,
    barox_article_no    VARCHAR(64)      NULL,
    hardware_version    VARCHAR(64)      NULL,
    firmware_version    VARCHAR(64)      NULL,
    bootloader_version  VARCHAR(64)      NULL,
    serial_number       VARCHAR(120)     NULL,
    mac_address         VARCHAR(64)      NULL,
    port_count          INT UNSIGNED     NULL,
    has_poe             TINYINT(1)   NOT NULL DEFAULT 0,
    poe_budget_w        DECIMAL(7,2)     NULL,
    sfp_ports           VARCHAR(120)     NULL,
    management_ip       VARCHAR(64)      NULL,

    -- Test-Metadaten
    test_start          DATE             NULL,
    test_end            DATE             NULL,
    tester              VARCHAR(190)     NULL,
    location            VARCHAR(190)     NULL,
    objective           TEXT             NULL,           -- Testziel
    remarks             TEXT             NULL,           -- Besonderheiten

    -- Testumgebung (Freitext-Bloecke)
    env_description     TEXT             NULL,           -- Testumgebung allgemein
    used_peers          TEXT             NULL,           -- Gegenstellen
    used_cameras        TEXT             NULL,
    used_nvrs           TEXT             NULL,
    used_sfps           TEXT             NULL,
    used_instruments    TEXT             NULL,           -- Messgeraete
    used_software       TEXT             NULL,           -- iPerf, Wireshark, SNMP, XProtect ...

    -- Bewertung / Empfehlung
    recommendation          ENUM('Freigeben','Nachbessern','Ablehnen') NULL,
    recommendation_auto      ENUM('Freigeben','Nachbessern','Ablehnen') NULL,
    recommendation_reason    TEXT         NULL,          -- automatische Begruendung
    recommendation_override  TINYINT(1)   NOT NULL DEFAULT 0,
    override_reason          TEXT         NULL,          -- Pflichtbegruendung bei Override
    avg_rating               DECIMAL(4,2) NULL,          -- zwischengespeicherte Kennzahl

    created_by          INT UNSIGNED     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_run_report (report_id),
    KEY idx_run_status (status),
    KEY idx_run_creator (created_by),
    CONSTRAINT fk_run_version FOREIGN KEY (template_version_id) REFERENCES template_versions (id) ON DELETE SET NULL,
    CONSTRAINT fk_run_creator FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
--  Testfaelle je Testlauf (KOPIE der Vorlage – immutabler Snapshot)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS test_cases (
    id               INT UNSIGNED NOT NULL AUTO_INCREMENT,
    test_run_id      INT UNSIGNED NOT NULL,
    source_template_id INT UNSIGNED   NULL,             -- Herkunft (nur informativ)
    category         VARCHAR(64)  NOT NULL,
    name             VARCHAR(255) NOT NULL,
    description      TEXT             NULL,
    expected_result  TEXT             NULL,
    is_security      TINYINT(1)   NOT NULL DEFAULT 0,
    sort_order       INT          NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    KEY idx_tc_run (test_run_id),
    KEY idx_tc_category (category),
    CONSTRAINT fk_tc_run FOREIGN KEY (test_run_id) REFERENCES test_runs (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Ergebnisdaten (1:1 zum Testfall, veraenderbar)
CREATE TABLE IF NOT EXISTS test_case_results (
    id               INT UNSIGNED NOT NULL AUTO_INCREMENT,
    test_case_id     INT UNSIGNED NOT NULL,
    status           ENUM('Nicht gestartet','In Arbeit','Bestanden','Teilweise bestanden','Fehlgeschlagen','Nicht anwendbar','Blockiert') NOT NULL DEFAULT 'Nicht gestartet',
    priority         ENUM('Kritisch','Hoch','Mittel','Niedrig') NOT NULL DEFAULT 'Mittel',
    actual_result    TEXT             NULL,
    measurements     TEXT             NULL,
    rating           TINYINT UNSIGNED NULL,             -- 1..10
    notes            TEXT             NULL,
    internal_notes   TEXT             NULL,
    vendor_query     TINYINT(1)   NOT NULL DEFAULT 0,   -- Hersteller-Rueckfrage noetig
    finding_created  TINYINT(1)   NOT NULL DEFAULT 0,
    tester_id        INT UNSIGNED     NULL,
    executed_at      DATETIME         NULL,
    updated_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_result_case (test_case_id),
    CONSTRAINT fk_result_case FOREIGN KEY (test_case_id) REFERENCES test_cases (id) ON DELETE CASCADE,
    CONSTRAINT fk_result_tester FOREIGN KEY (tester_id) REFERENCES users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
--  SFP-Tests je Testlauf
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS sfp_test_results (
    id               INT UNSIGNED NOT NULL AUTO_INCREMENT,
    test_run_id      INT UNSIGNED NOT NULL,
    sfp_model        VARCHAR(64)  NOT NULL,
    switch_port      VARCHAR(64)      NULL,
    remote_end       VARCHAR(190)     NULL,             -- Gegenstelle
    link_status      ENUM('Up','Down','')            NOT NULL DEFAULT '',
    speed            VARCHAR(32)      NULL,
    duplex           ENUM('Full','Half','')          NOT NULL DEFAULT '',
    dom_available    TINYINT(1)   NOT NULL DEFAULT 0,
    temperature_c    VARCHAR(32)      NULL,
    voltage_v        VARCHAR(32)      NULL,
    tx_power_dbm     VARCHAR(32)      NULL,
    rx_power_dbm     VARCHAR(32)      NULL,
    bias_current_ma  VARCHAR(32)      NULL,
    hotplug_pass     TINYINT(1)   NOT NULL DEFAULT 0,
    ddm_checked      TINYINT(1)   NOT NULL DEFAULT 0,
    result           ENUM('Nicht getestet','Bestanden','Teilweise bestanden','Fehlgeschlagen','Nicht anwendbar') NOT NULL DEFAULT 'Nicht getestet',
    remark           TEXT             NULL,
    tester_id        INT UNSIGNED     NULL,
    updated_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_sfpr_run (test_run_id),
    CONSTRAINT fk_sfpr_run FOREIGN KEY (test_run_id) REFERENCES test_runs (id) ON DELETE CASCADE,
    CONSTRAINT fk_sfpr_tester FOREIGN KEY (tester_id) REFERENCES users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
--  Szenario-Ergebnisse je Testlauf (KOPIE der Szenario-Vorlage)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS scenario_results (
    id               INT UNSIGNED NOT NULL AUTO_INCREMENT,
    test_run_id      INT UNSIGNED NOT NULL,
    source_template_id INT UNSIGNED   NULL,
    name             VARCHAR(255) NOT NULL,
    description      TEXT             NULL,
    objective        TEXT             NULL,
    prerequisites    TEXT             NULL,
    required_devices TEXT             NULL,
    required_config  TEXT             NULL,
    test_steps       TEXT             NULL,
    expected_result  TEXT             NULL,
    actual_result    TEXT             NULL,
    measurements     TEXT             NULL,
    status           ENUM('Nicht gestartet','In Arbeit','Bestanden','Teilweise bestanden','Fehlgeschlagen','Nicht anwendbar','Blockiert') NOT NULL DEFAULT 'Nicht gestartet',
    rating           TINYINT UNSIGNED NULL,             -- Bewertung 1..10
    remarks          TEXT             NULL,
    tester_id        INT UNSIGNED     NULL,
    sort_order       INT          NOT NULL DEFAULT 0,
    updated_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_scr_run (test_run_id),
    CONSTRAINT fk_scr_run FOREIGN KEY (test_run_id) REFERENCES test_runs (id) ON DELETE CASCADE,
    CONSTRAINT fk_scr_tester FOREIGN KEY (tester_id) REFERENCES users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
--  Testumgebung: Geraete & Topologie-Verbindungen
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS devices (
    id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
    test_run_id   INT UNSIGNED NOT NULL,
    role          ENUM('DUT','Gegenstelle','Management-PC','Traffic-Generator','Kamera','NVR','Sonstiges') NOT NULL DEFAULT 'Sonstiges',
    name          VARCHAR(190) NOT NULL,
    model         VARCHAR(190)     NULL,
    ip_address    VARCHAR(64)      NULL,
    ports         VARCHAR(190)     NULL,
    vlans         VARCHAR(190)     NULL,
    notes         TEXT             NULL,
    sort_order    INT          NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    KEY idx_dev_run (test_run_id),
    CONSTRAINT fk_dev_run FOREIGN KEY (test_run_id) REFERENCES test_runs (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS topology_links (
    id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
    test_run_id   INT UNSIGNED NOT NULL,
    from_device   VARCHAR(190) NOT NULL,
    from_port     VARCHAR(64)      NULL,
    to_device     VARCHAR(190) NOT NULL,
    to_port       VARCHAR(64)      NULL,
    medium        VARCHAR(120)     NULL,              -- Kupfer/LWL/SFP-Typ
    is_ring       TINYINT(1)   NOT NULL DEFAULT 0,    -- Ring-/Redundanzstruktur
    notes         VARCHAR(255)     NULL,
    PRIMARY KEY (id),
    KEY idx_topo_run (test_run_id),
    CONSTRAINT fk_topo_run FOREIGN KEY (test_run_id) REFERENCES test_runs (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
--  Anhaenge (polymorph an Testlauf/Testfall/SFP/Szenario/Finding/Topologie)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS attachments (
    id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
    test_run_id   INT UNSIGNED NOT NULL,               -- immer gesetzt (Ablagepfad)
    entity_type   ENUM('test_run','test_case','sfp_test','scenario','finding','topology') NOT NULL,
    entity_id     INT UNSIGNED     NULL,               -- ID im jeweiligen Kontext
    original_name VARCHAR(255) NOT NULL,
    stored_name   VARCHAR(255) NOT NULL,               -- Zufallsname auf der Platte
    rel_path      VARCHAR(512) NOT NULL,               -- relativ zu STORAGE_PATH
    mime_type     VARCHAR(120)     NULL,
    extension     VARCHAR(16)      NULL,
    size_bytes    BIGINT UNSIGNED  NULL,
    caption       VARCHAR(255)     NULL,
    uploaded_by   INT UNSIGNED     NULL,
    created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_att_run (test_run_id),
    KEY idx_att_entity (entity_type, entity_id),
    CONSTRAINT fk_att_run FOREIGN KEY (test_run_id) REFERENCES test_runs (id) ON DELETE CASCADE,
    CONSTRAINT fk_att_user FOREIGN KEY (uploaded_by) REFERENCES users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
--  Findings (entstehen automatisch aus fehlgeschlagenen/teilweisen Tests)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS findings (
    id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
    test_run_id   INT UNSIGNED NOT NULL,
    test_case_id  INT UNSIGNED     NULL,               -- bezogener Testfall
    title         VARCHAR(255) NOT NULL,
    category      VARCHAR(64)      NULL,
    severity      ENUM('Kritisch','Hoch','Mittel','Niedrig','Info') NOT NULL DEFAULT 'Mittel',
    description   TEXT             NULL,
    impact        TEXT             NULL,               -- Auswirkung
    recommendation TEXT            NULL,               -- Massnahme
    status        ENUM('Offen','In Bearbeitung','Behoben','Akzeptiert','Verworfen') NOT NULL DEFAULT 'Offen',
    owner         VARCHAR(190)     NULL,               -- Verantwortlich
    vendor_query  TINYINT(1)   NOT NULL DEFAULT 0,
    auto_generated TINYINT(1)  NOT NULL DEFAULT 0,
    created_by    INT UNSIGNED     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_find_run (test_run_id),
    KEY idx_find_case (test_case_id),
    CONSTRAINT fk_find_run FOREIGN KEY (test_run_id) REFERENCES test_runs (id) ON DELETE CASCADE,
    CONSTRAINT fk_find_case FOREIGN KEY (test_case_id) REFERENCES test_cases (id) ON DELETE SET NULL,
    CONSTRAINT fk_find_user FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
--  Report-Exporte (erzeugte PDFs)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS report_exports (
    id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
    test_run_id   INT UNSIGNED NOT NULL,
    report_id     VARCHAR(48)  NOT NULL,
    version       INT          NOT NULL DEFAULT 1,
    rel_path      VARCHAR(512)     NULL,               -- relativ zu STORAGE_PATH
    format        ENUM('pdf','html') NOT NULL DEFAULT 'pdf',
    recommendation ENUM('Freigeben','Nachbessern','Ablehnen') NULL,
    generated_by  INT UNSIGNED     NULL,
    created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_rep_run (test_run_id),
    CONSTRAINT fk_rep_run FOREIGN KEY (test_run_id) REFERENCES test_runs (id) ON DELETE CASCADE,
    CONSTRAINT fk_rep_user FOREIGN KEY (generated_by) REFERENCES users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
--  Audit-Log
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS audit_log (
    id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id       INT UNSIGNED     NULL,
    action        VARCHAR(64)  NOT NULL,               -- z. B. testrun.created
    entity_type   VARCHAR(64)      NULL,
    entity_id     INT UNSIGNED     NULL,
    test_run_id   INT UNSIGNED     NULL,
    summary       VARCHAR(255)     NULL,
    meta          JSON             NULL,
    ip_address    VARCHAR(64)      NULL,
    created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_audit_run (test_run_id),
    KEY idx_audit_user (user_id),
    KEY idx_audit_action (action),
    CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
--  Einstellungen (Key/Value)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS settings (
    id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `key`         VARCHAR(64)  NOT NULL,
    `value`       TEXT             NULL,
    updated_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_settings_key (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;
