-- ============================================================================
--  Migration 002 – Vorlagen (Profile)
--  Eine "Vorlage" ist eine benannte Auswahl von Tests (und optional Szenarien
--  und SFP-Modulen) aus dem Test-Katalog einer Vorlagenversion, z. B.
--  "L2-Switch", "L3-Switch" oder "Medienkonverter". Beim Anlegen eines
--  Testlaufs waehlt der Tester eine Vorlage; nur deren Tests werden geladen.
-- ============================================================================


CREATE TABLE IF NOT EXISTS profiles (
    id                  INT UNSIGNED NOT NULL AUTO_INCREMENT,
    template_version_id INT UNSIGNED NOT NULL,          -- Katalogversion, aus der die Tests stammen
    name                VARCHAR(120) NOT NULL,          -- z. B. "L2-Switch 24 Port"
    device_type         VARCHAR(64)      NULL,          -- z. B. L2-Switch, L3-Switch, Medienkonverter
    description         VARCHAR(255)     NULL,
    is_active           TINYINT(1)   NOT NULL DEFAULT 1,
    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_profiles_version (template_version_id),
    CONSTRAINT fk_profiles_version FOREIGN KEY (template_version_id) REFERENCES template_versions (id) ON DELETE CASCADE,
    CONSTRAINT fk_profiles_user FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS profile_tests (
    profile_id       INT UNSIGNED NOT NULL,
    test_template_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (profile_id, test_template_id),
    KEY idx_pt_template (test_template_id),
    CONSTRAINT fk_pt_profile FOREIGN KEY (profile_id) REFERENCES profiles (id) ON DELETE CASCADE,
    CONSTRAINT fk_pt_template FOREIGN KEY (test_template_id) REFERENCES test_templates (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS profile_scenarios (
    profile_id           INT UNSIGNED NOT NULL,
    scenario_template_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (profile_id, scenario_template_id),
    KEY idx_ps_template (scenario_template_id),
    CONSTRAINT fk_ps_profile FOREIGN KEY (profile_id) REFERENCES profiles (id) ON DELETE CASCADE,
    CONSTRAINT fk_ps_template FOREIGN KEY (scenario_template_id) REFERENCES scenario_templates (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS profile_sfps (
    profile_id     INT UNSIGNED NOT NULL,
    sfp_module_id  INT UNSIGNED NOT NULL,
    PRIMARY KEY (profile_id, sfp_module_id),
    KEY idx_psf_module (sfp_module_id),
    CONSTRAINT fk_psf_profile FOREIGN KEY (profile_id) REFERENCES profiles (id) ON DELETE CASCADE,
    CONSTRAINT fk_psf_module FOREIGN KEY (sfp_module_id) REFERENCES sfp_modules (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Verwendete Vorlage am Testlauf vermerken (nur informativ; Snapshot bleibt Kopie)
ALTER TABLE test_runs
    ADD COLUMN profile_id INT UNSIGNED NULL AFTER template_version_id,
    ADD KEY idx_run_profile (profile_id),
    ADD CONSTRAINT fk_run_profile FOREIGN KEY (profile_id) REFERENCES profiles (id) ON DELETE SET NULL;
