-- =========================================================
-- Migration 014: Compliance Training Engine
-- =========================================================
-- Training Module Catalog, Compliance Training Matrix (CTM),
-- Assignments, Quiz Engine, Content Pipeline, Audit Trail
-- =========================================================

-- Training-Module-Katalog (erweiterte Kurs-Metadaten)
CREATE TABLE IF NOT EXISTS training_modules (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES compliance_tenants(id) ON DELETE CASCADE,
    academy_course_id UUID REFERENCES academy_courses(id),
    module_code VARCHAR(20) NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    regulation_area VARCHAR(20) NOT NULL,
    nis2_relevant BOOLEAN DEFAULT FALSE,
    iso_controls JSONB DEFAULT '[]',
    frequency_type VARCHAR(20) NOT NULL DEFAULT 'annual',
    validity_days INT DEFAULT 365,
    risk_weight FLOAT DEFAULT 2.0,
    content_type VARCHAR(20) DEFAULT 'text',
    duration_minutes INT DEFAULT 30,
    pass_threshold INT DEFAULT 70,
    is_active BOOLEAN DEFAULT TRUE,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(tenant_id, module_code)
);

-- Compliance Training Matrix: welche Rollen brauchen welche Module
CREATE TABLE IF NOT EXISTS training_matrix (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES compliance_tenants(id) ON DELETE CASCADE,
    role_code VARCHAR(10) NOT NULL,
    module_id UUID NOT NULL REFERENCES training_modules(id) ON DELETE CASCADE,
    is_mandatory BOOLEAN DEFAULT TRUE,
    priority INT DEFAULT 5,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(tenant_id, role_code, module_id)
);

-- Training-Zuweisungen (automatisch oder manuell)
CREATE TABLE IF NOT EXISTS training_assignments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES compliance_tenants(id) ON DELETE CASCADE,
    module_id UUID NOT NULL REFERENCES training_modules(id) ON DELETE CASCADE,
    user_id UUID NOT NULL,
    user_name VARCHAR(255) NOT NULL,
    user_email VARCHAR(255) NOT NULL,
    role_code VARCHAR(10),
    trigger_type VARCHAR(20) NOT NULL,
    trigger_event VARCHAR(100),
    status VARCHAR(20) DEFAULT 'pending',
    progress_percent INT DEFAULT 0,
    quiz_score FLOAT,
    quiz_passed BOOLEAN,
    quiz_attempts INT DEFAULT 0,
    started_at TIMESTAMPTZ,
    completed_at TIMESTAMPTZ,
    deadline TIMESTAMPTZ NOT NULL,
    certificate_id UUID,
    escalation_level INT DEFAULT 0,
    last_escalation_at TIMESTAMPTZ,
    enrollment_id UUID REFERENCES academy_enrollments(id),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Quiz-Fragenbank (persistent, nicht nur JSONB)
CREATE TABLE IF NOT EXISTS training_quiz_questions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    module_id UUID NOT NULL REFERENCES training_modules(id) ON DELETE CASCADE,
    question TEXT NOT NULL,
    options JSONB NOT NULL,
    correct_index INT NOT NULL,
    explanation TEXT,
    difficulty VARCHAR(10) DEFAULT 'medium',
    is_active BOOLEAN DEFAULT TRUE,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Quiz-Versuche (jeder Versuch einzeln getracked)
CREATE TABLE IF NOT EXISTS training_quiz_attempts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    assignment_id UUID NOT NULL REFERENCES training_assignments(id) ON DELETE CASCADE,
    user_id UUID NOT NULL,
    answers JSONB NOT NULL,
    score FLOAT NOT NULL,
    passed BOOLEAN NOT NULL,
    correct_count INT NOT NULL,
    total_count INT NOT NULL,
    duration_seconds INT,
    attempted_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Audit Trail fuer Training-Aktionen
CREATE TABLE IF NOT EXISTS training_audit_log (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES compliance_tenants(id) ON DELETE CASCADE,
    user_id UUID,
    action VARCHAR(50) NOT NULL,
    entity_type VARCHAR(30) NOT NULL,
    entity_id UUID,
    details JSONB DEFAULT '{}',
    ip_address VARCHAR(45),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Module-Inhalte (LLM-generiert oder manuell)
CREATE TABLE IF NOT EXISTS training_module_content (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    module_id UUID NOT NULL REFERENCES training_modules(id) ON DELETE CASCADE,
    version INT DEFAULT 1,
    content_format VARCHAR(20) DEFAULT 'markdown',
    content_body TEXT NOT NULL,
    summary TEXT,
    generated_by VARCHAR(50),
    llm_model VARCHAR(100),
    is_published BOOLEAN DEFAULT FALSE,
    reviewed_by UUID,
    reviewed_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- =========================================================
-- INDEXES
-- =========================================================

CREATE INDEX IF NOT EXISTS idx_training_modules_tenant ON training_modules(tenant_id);
CREATE INDEX IF NOT EXISTS idx_training_modules_regulation ON training_modules(tenant_id, regulation_area);
CREATE INDEX IF NOT EXISTS idx_training_matrix_tenant_role ON training_matrix(tenant_id, role_code);
CREATE INDEX IF NOT EXISTS idx_training_matrix_module ON training_matrix(module_id);
CREATE INDEX IF NOT EXISTS idx_training_assignments_tenant ON training_assignments(tenant_id);
CREATE INDEX IF NOT EXISTS idx_training_assignments_user ON training_assignments(tenant_id, user_id);
CREATE INDEX IF NOT EXISTS idx_training_assignments_status ON training_assignments(tenant_id, status);
CREATE INDEX IF NOT EXISTS idx_training_assignments_deadline ON training_assignments(deadline)
    WHERE status NOT IN ('completed', 'expired');
CREATE INDEX IF NOT EXISTS idx_training_assignments_overdue ON training_assignments(tenant_id, deadline, status)
    WHERE status IN ('pending', 'in_progress') AND deadline < NOW();
CREATE INDEX IF NOT EXISTS idx_training_quiz_questions_module ON training_quiz_questions(module_id);
CREATE INDEX IF NOT EXISTS idx_training_quiz_attempts_assignment ON training_quiz_attempts(assignment_id);
CREATE INDEX IF NOT EXISTS idx_training_audit_log_tenant ON training_audit_log(tenant_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_training_module_content_module ON training_module_content(module_id, is_published);

-- =========================================================
-- SEED DATA: Template Modules (tenant_id = 00000000-...)
-- =========================================================

DO $$
DECLARE
    t_id UUID := '00000000-0000-0000-0000-000000000000';
    m_gdpr_bas UUID; m_gdpr_adv UUID; m_gdpr_art UUID; m_gdpr_dpia UUID;
    m_nis2_m1 UUID; m_nis2_m2 UUID; m_nis2_m3 UUID; m_nis2_m4 UUID; m_nis2_m5 UUID;
    m_isms_bas UUID; m_isms_int UUID; m_isms_aud UUID;
    m_ai_bas UUID; m_ai_adv UUID; m_ai_risk UUID;
    m_gesch_bas UUID; m_hin_bas UUID;
    m_mail_sec UUID; m_soc_eng UUID; m_phish UUID;
BEGIN
    -- Skip if seed data already exists
    IF EXISTS (SELECT 1 FROM training_modules WHERE tenant_id = t_id LIMIT 1) THEN
        RAISE NOTICE 'Seed data already exists, skipping';
        RETURN;
    END IF;

    -- Insert modules and capture IDs
    INSERT INTO training_modules (id, tenant_id, module_code, title, description, regulation_area, nis2_relevant, frequency_type, validity_days, risk_weight, duration_minutes, pass_threshold, sort_order)
    VALUES
        (gen_random_uuid(), t_id, 'GDPR-BAS', 'DSGVO Grundlagen', 'Grundlegende Datenschutzprinzipien, Rechtsgrundlagen, Verarbeitungsgrundsaetze', 'dsgvo', false, 'annual', 365, 2.0, 30, 70, 1),
        (gen_random_uuid(), t_id, 'GDPR-ADV', 'DSGVO Vertiefung', 'Auftragsverarbeitung, Drittlandtransfer, Datenschutz-Management', 'dsgvo', false, 'annual', 365, 2.5, 45, 70, 2),
        (gen_random_uuid(), t_id, 'GDPR-ART', 'Betroffenenrechte', 'Auskunft, Berichtigung, Loeschung, Einschraenkung, Datenportabilitaet', 'dsgvo', false, 'annual', 365, 2.0, 30, 70, 3),
        (gen_random_uuid(), t_id, 'GDPR-DPIA', 'Datenschutz-Folgenabschaetzung', 'DSFA-Durchfuehrung, Schwellwertanalyse, Risikobewertung', 'dsgvo', false, 'event_trigger', 365, 3.0, 60, 80, 4),
        (gen_random_uuid(), t_id, 'NIS2-M1', 'NIS2 Geschaeftsleitungspflicht', 'Haftung, Schulungspflicht, Risikomanagement-Governance', 'nis2', true, 'annual', 365, 3.0, 45, 80, 5),
        (gen_random_uuid(), t_id, 'NIS2-M2', 'NIS2 Incident Response', 'Meldepflichten, 24h/72h-Fristen, Incident-Response-Plan', 'nis2', true, 'annual', 365, 3.0, 60, 80, 6),
        (gen_random_uuid(), t_id, 'NIS2-M3', 'NIS2 Lieferkettensicherheit', 'Supply-Chain-Risiken, Dienstleisterbewertung, Vertragsklauseln', 'nis2', true, 'annual', 365, 2.5, 45, 70, 7),
        (gen_random_uuid(), t_id, 'NIS2-M4', 'NIS2 BCM', 'Business Continuity, Notfallplanung, Wiederherstellung', 'nis2', true, 'annual', 365, 2.5, 45, 70, 8),
        (gen_random_uuid(), t_id, 'NIS2-M5', 'NIS2 Risikomanagement', 'Risikoanalyse, Massnahmenplanung, Wirksamkeitspruefung', 'nis2', true, 'annual', 365, 3.0, 60, 80, 9),
        (gen_random_uuid(), t_id, 'ISMS-BAS', 'ISMS Grundlagen', 'ISO 27001 Anforderungen, PDCA-Zyklus, Informationssicherheitspolitik', 'iso27001', false, 'annual', 365, 2.0, 30, 70, 10),
        (gen_random_uuid(), t_id, 'ISMS-INT', 'ISMS Interne Audits', 'Audit-Planung, Durchfuehrung, Berichterstattung, Massnahmenverfolgung', 'iso27001', false, 'annual', 365, 2.5, 45, 70, 11),
        (gen_random_uuid(), t_id, 'ISMS-AUD', 'ISMS Audit-Vorbereitung', 'Zertifizierungsaudit, Dokumentation, Nachweisfuehrung', 'iso27001', false, 'event_trigger', 365, 2.5, 60, 70, 12),
        (gen_random_uuid(), t_id, 'AI-BAS', 'KI-Kompetenz Grundlagen', 'EU AI Act Ueberblick, KI-Risikokategorien, Transparenzpflichten', 'ai_act', false, 'annual', 365, 2.0, 30, 70, 13),
        (gen_random_uuid(), t_id, 'AI-ADV', 'KI-Risikomanagement', 'Hochrisiko-KI, Konformitaetsbewertung, Dokumentationspflichten', 'ai_act', false, 'annual', 365, 2.5, 45, 70, 14),
        (gen_random_uuid(), t_id, 'AI-RISK', 'Hochrisiko-KI-Systeme', 'Risikomanagementsystem, Qualitaet der Trainingsdaten, Human Oversight', 'ai_act', false, 'event_trigger', 365, 3.0, 60, 80, 15),
        (gen_random_uuid(), t_id, 'GESCH-BAS', 'Geschaeftsgeheimnisschutz', 'GeschGehG, Schutzkonzept, NDAs, technische Massnahmen', 'geschgehg', false, 'annual', 365, 2.0, 30, 70, 16),
        (gen_random_uuid(), t_id, 'HIN-BAS', 'Hinweisgeberschutz', 'HinSchG, Meldekanal, Vertraulichkeit, Repressalienverbot', 'hinschg', false, 'annual', 365, 2.0, 30, 70, 17),
        (gen_random_uuid(), t_id, 'MAIL-SEC', 'E-Mail-Sicherheit', 'Phishing, Spam, Verschluesselung, Sichere Kommunikation', 'iso27001', false, 'micro', 180, 1.5, 15, 70, 18),
        (gen_random_uuid(), t_id, 'SOC-ENG', 'Social Engineering Abwehr', 'Manipulationstechniken, Pretexting, Baiting, Tailgating', 'iso27001', false, 'micro', 180, 1.5, 15, 70, 19),
        (gen_random_uuid(), t_id, 'PHISH', 'Phishing-Erkennung', 'Phishing-Merkmale, Pruefschritte, Meldeprozess', 'iso27001', false, 'micro', 180, 1.5, 10, 70, 20);

    -- Get module IDs for CTM
    SELECT id INTO m_gdpr_bas FROM training_modules WHERE tenant_id = t_id AND module_code = 'GDPR-BAS';
    SELECT id INTO m_gdpr_adv FROM training_modules WHERE tenant_id = t_id AND module_code = 'GDPR-ADV';
    SELECT id INTO m_gdpr_art FROM training_modules WHERE tenant_id = t_id AND module_code = 'GDPR-ART';
    SELECT id INTO m_gdpr_dpia FROM training_modules WHERE tenant_id = t_id AND module_code = 'GDPR-DPIA';
    SELECT id INTO m_nis2_m1 FROM training_modules WHERE tenant_id = t_id AND module_code = 'NIS2-M1';
    SELECT id INTO m_nis2_m2 FROM training_modules WHERE tenant_id = t_id AND module_code = 'NIS2-M2';
    SELECT id INTO m_nis2_m3 FROM training_modules WHERE tenant_id = t_id AND module_code = 'NIS2-M3';
    SELECT id INTO m_nis2_m4 FROM training_modules WHERE tenant_id = t_id AND module_code = 'NIS2-M4';
    SELECT id INTO m_nis2_m5 FROM training_modules WHERE tenant_id = t_id AND module_code = 'NIS2-M5';
    SELECT id INTO m_isms_bas FROM training_modules WHERE tenant_id = t_id AND module_code = 'ISMS-BAS';
    SELECT id INTO m_isms_int FROM training_modules WHERE tenant_id = t_id AND module_code = 'ISMS-INT';
    SELECT id INTO m_isms_aud FROM training_modules WHERE tenant_id = t_id AND module_code = 'ISMS-AUD';
    SELECT id INTO m_ai_bas FROM training_modules WHERE tenant_id = t_id AND module_code = 'AI-BAS';
    SELECT id INTO m_ai_adv FROM training_modules WHERE tenant_id = t_id AND module_code = 'AI-ADV';
    SELECT id INTO m_ai_risk FROM training_modules WHERE tenant_id = t_id AND module_code = 'AI-RISK';
    SELECT id INTO m_gesch_bas FROM training_modules WHERE tenant_id = t_id AND module_code = 'GESCH-BAS';
    SELECT id INTO m_hin_bas FROM training_modules WHERE tenant_id = t_id AND module_code = 'HIN-BAS';
    SELECT id INTO m_mail_sec FROM training_modules WHERE tenant_id = t_id AND module_code = 'MAIL-SEC';
    SELECT id INTO m_soc_eng FROM training_modules WHERE tenant_id = t_id AND module_code = 'SOC-ENG';
    SELECT id INTO m_phish FROM training_modules WHERE tenant_id = t_id AND module_code = 'PHISH';

    -- CTM: R1 Geschaeftsfuehrung
    INSERT INTO training_matrix (tenant_id, role_code, module_id, is_mandatory, priority) VALUES
        (t_id, 'R1', m_gdpr_bas, true, 1), (t_id, 'R1', m_nis2_m1, true, 1),
        (t_id, 'R1', m_nis2_m5, true, 2), (t_id, 'R1', m_isms_bas, true, 2),
        (t_id, 'R1', m_ai_bas, true, 3), (t_id, 'R1', m_gesch_bas, true, 3);

    -- CTM: R2 IT-Leitung
    INSERT INTO training_matrix (tenant_id, role_code, module_id, is_mandatory, priority) VALUES
        (t_id, 'R2', m_nis2_m2, true, 1), (t_id, 'R2', m_nis2_m5, true, 1),
        (t_id, 'R2', m_isms_bas, true, 2), (t_id, 'R2', m_isms_int, true, 2),
        (t_id, 'R2', m_mail_sec, true, 3), (t_id, 'R2', m_soc_eng, true, 3),
        (t_id, 'R2', m_phish, true, 3);

    -- CTM: R3 DSB
    INSERT INTO training_matrix (tenant_id, role_code, module_id, is_mandatory, priority) VALUES
        (t_id, 'R3', m_gdpr_bas, true, 1), (t_id, 'R3', m_gdpr_adv, true, 1),
        (t_id, 'R3', m_gdpr_art, true, 1), (t_id, 'R3', m_gdpr_dpia, true, 2),
        (t_id, 'R3', m_isms_bas, true, 3);

    -- CTM: R4 ISB
    INSERT INTO training_matrix (tenant_id, role_code, module_id, is_mandatory, priority) VALUES
        (t_id, 'R4', m_nis2_m2, true, 1), (t_id, 'R4', m_nis2_m3, true, 1),
        (t_id, 'R4', m_nis2_m4, true, 1), (t_id, 'R4', m_nis2_m5, true, 1),
        (t_id, 'R4', m_isms_bas, true, 2), (t_id, 'R4', m_isms_int, true, 2),
        (t_id, 'R4', m_isms_aud, true, 2);

    -- CTM: R5 HR
    INSERT INTO training_matrix (tenant_id, role_code, module_id, is_mandatory, priority) VALUES
        (t_id, 'R5', m_gdpr_bas, true, 1), (t_id, 'R5', m_gdpr_art, true, 1),
        (t_id, 'R5', m_hin_bas, true, 2);

    -- CTM: R6 Einkauf
    INSERT INTO training_matrix (tenant_id, role_code, module_id, is_mandatory, priority) VALUES
        (t_id, 'R6', m_gdpr_bas, true, 1), (t_id, 'R6', m_nis2_m3, true, 1),
        (t_id, 'R6', m_gesch_bas, true, 2);

    -- CTM: R7 Fachabteilung
    INSERT INTO training_matrix (tenant_id, role_code, module_id, is_mandatory, priority) VALUES
        (t_id, 'R7', m_gdpr_bas, true, 1), (t_id, 'R7', m_ai_bas, true, 2),
        (t_id, 'R7', m_mail_sec, true, 3), (t_id, 'R7', m_soc_eng, true, 3);

    -- CTM: R8 IT-Admin
    INSERT INTO training_matrix (tenant_id, role_code, module_id, is_mandatory, priority) VALUES
        (t_id, 'R8', m_nis2_m2, true, 1), (t_id, 'R8', m_nis2_m5, true, 1),
        (t_id, 'R8', m_isms_bas, true, 2), (t_id, 'R8', m_mail_sec, true, 3),
        (t_id, 'R8', m_soc_eng, true, 3), (t_id, 'R8', m_phish, true, 3);

    -- CTM: R9 Alle Mitarbeiter
    INSERT INTO training_matrix (tenant_id, role_code, module_id, is_mandatory, priority) VALUES
        (t_id, 'R9', m_gdpr_bas, true, 1), (t_id, 'R9', m_mail_sec, true, 2),
        (t_id, 'R9', m_soc_eng, true, 2), (t_id, 'R9', m_phish, true, 2);

    RAISE NOTICE 'Training Engine seed data inserted successfully';
END $$;