-- ============================================================================
-- Migration: Mehrere Schwachstellen (Findings) pro Meldung
--
-- Anwenden auf eine bestehende Datenbank:
--   mysql <db-name> < database/migrations/2026-07-multi-findings.sql
--
-- Wirkung:
--   * neue Tabelle report_findings (1..n Schwachstellen je Meldung), je mit
--     eigener CWE-/CVSS-/CVE-Zuordnung
--   * bestehende Meldungsinhalte werden in je ein Finding überführt
--   * neue report-weite Offenlegungs-Frist (Coordinated Disclosure)
--   * die je-Meldung-Schwachstellenfelder werden aus vulnerability_reports
--     entfernt (reporter_severity/reporter_exploited bleiben als Aggregat)
-- ============================================================================

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,
    title                 VARCHAR(190)  NOT NULL,
    cwe                   VARCHAR(190)  NULL,
    reporter_advisory_id  VARCHAR(100)  NULL,
    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_version          VARCHAR(8)    NULL,
    cvss_vector           VARCHAR(255)  NULL,
    cvss_score            DECIMAL(3,1)  NULL,
    internal_severity     ENUM('low','medium','high','critical') NULL,
    internal_cvss_vector  VARCHAR(255)  NULL,
    internal_cvss_score   DECIMAL(3,1)  NULL,
    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;

-- Bestehende Meldungen in je ein Finding überführen (nur falls Altspalten existieren).
INSERT INTO report_findings
    (report_id, sort_order, title, description, reproduction_steps, impact,
     reporter_exploited, reporter_severity, internal_severity,
     cve_status, cve_id)
SELECT id, 0, vulnerability_type, description, reproduction_steps, impact,
       reporter_exploited, reporter_severity, internal_severity,
       cve_status, cve_id
FROM vulnerability_reports;

-- Report-weite Offenlegungs-Frist
ALTER TABLE vulnerability_reports
    ADD COLUMN disclosure_deadline DATE         NULL AFTER reference_links,
    ADD COLUMN disclosure_note     VARCHAR(255) NULL AFTER disclosure_deadline;

-- Je-Meldung-Schwachstellenfelder entfernen (jetzt in report_findings)
ALTER TABLE vulnerability_reports
    DROP COLUMN vulnerability_type,
    DROP COLUMN description,
    DROP COLUMN reproduction_steps,
    DROP COLUMN impact,
    DROP COLUMN cve_status,
    DROP COLUMN cve_id;
