-- Migration 027: E-Mail-Templates — Benachrichtigungsvorlagen fuer DSGVO-Compliance
-- Zentrale Verwaltung von E-Mail-Templates fuer DSR, Consent, Breach-Notifications etc.

-- Template-Definitionen
CREATE TABLE IF NOT EXISTS compliance_email_templates (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id       UUID NOT NULL DEFAULT '9282a473-5c95-4b3a-bf78-0ecc0ec71d3e',
    template_type   TEXT NOT NULL,
    name            TEXT NOT NULL,
    description     TEXT,
    category        TEXT NOT NULL DEFAULT 'general',
    is_active       BOOLEAN NOT NULL DEFAULT TRUE,
    sort_order      INTEGER NOT NULL DEFAULT 0,
    variables       JSONB DEFAULT '[]'::jsonb,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_email_tpl_tenant ON compliance_email_templates(tenant_id);
CREATE INDEX IF NOT EXISTS idx_email_tpl_type ON compliance_email_templates(template_type);
CREATE INDEX IF NOT EXISTS idx_email_tpl_category ON compliance_email_templates(category);
CREATE UNIQUE INDEX IF NOT EXISTS idx_email_tpl_tenant_type ON compliance_email_templates(tenant_id, template_type);

-- Versionierte Template-Inhalte
CREATE TABLE IF NOT EXISTS compliance_email_template_versions (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    template_id     UUID NOT NULL,
    version         TEXT NOT NULL DEFAULT '1.0',
    language        TEXT NOT NULL DEFAULT 'de',
    subject         TEXT NOT NULL,
    body_html       TEXT NOT NULL,
    body_text       TEXT,
    status          TEXT NOT NULL DEFAULT 'draft',
    submitted_at    TIMESTAMPTZ,
    submitted_by    TEXT,
    published_at    TIMESTAMPTZ,
    published_by    TEXT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by      TEXT DEFAULT 'system'
);

CREATE INDEX IF NOT EXISTS idx_email_tpl_ver_template ON compliance_email_template_versions(template_id);
CREATE INDEX IF NOT EXISTS idx_email_tpl_ver_status ON compliance_email_template_versions(status);

-- Approval-Workflow
CREATE TABLE IF NOT EXISTS compliance_email_template_approvals (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    version_id      UUID NOT NULL,
    action          TEXT NOT NULL DEFAULT 'approve',
    comment         TEXT,
    approved_by     TEXT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_email_tpl_appr_version ON compliance_email_template_approvals(version_id);

-- Audit-Trail gesendeter E-Mails
CREATE TABLE IF NOT EXISTS compliance_email_send_logs (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id       UUID NOT NULL DEFAULT '9282a473-5c95-4b3a-bf78-0ecc0ec71d3e',
    template_type   TEXT NOT NULL,
    version_id      UUID,
    recipient       TEXT NOT NULL,
    subject         TEXT NOT NULL,
    status          TEXT NOT NULL DEFAULT 'sent',
    variables       JSONB DEFAULT '{}'::jsonb,
    error_message   TEXT,
    sent_at         TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_email_logs_tenant ON compliance_email_send_logs(tenant_id);
CREATE INDEX IF NOT EXISTS idx_email_logs_type ON compliance_email_send_logs(template_type);
CREATE INDEX IF NOT EXISTS idx_email_logs_sent ON compliance_email_send_logs(sent_at);

-- Globale Einstellungen (Branding)
CREATE TABLE IF NOT EXISTS compliance_email_template_settings (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id       UUID NOT NULL DEFAULT '9282a473-5c95-4b3a-bf78-0ecc0ec71d3e',
    sender_name     TEXT DEFAULT 'Datenschutzbeauftragter',
    sender_email    TEXT DEFAULT 'datenschutz@example.de',
    reply_to        TEXT,
    logo_url        TEXT,
    primary_color   TEXT DEFAULT '#4F46E5',
    secondary_color TEXT DEFAULT '#7C3AED',
    footer_text     TEXT DEFAULT 'Datenschutzhinweis: Diese E-Mail enthaelt vertrauliche Informationen.',
    company_name    TEXT,
    company_address TEXT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_email_settings_tenant ON compliance_email_template_settings(tenant_id);

-- Default-Templates einfuegen
INSERT INTO compliance_email_templates (id, tenant_id, template_type, name, description, category, sort_order, variables)
VALUES
    (gen_random_uuid(), '9282a473-5c95-4b3a-bf78-0ecc0ec71d3e', 'welcome', 'Willkommen', 'Willkommens-E-Mail fuer neue Nutzer', 'general', 1, '["user_name", "company_name", "login_url"]'::jsonb),
    (gen_random_uuid(), '9282a473-5c95-4b3a-bf78-0ecc0ec71d3e', 'verification', 'E-Mail-Verifizierung', 'Verifizierungs-Link fuer E-Mail-Adressen', 'general', 2, '["user_name", "verification_url", "expiry_hours"]'::jsonb),
    (gen_random_uuid(), '9282a473-5c95-4b3a-bf78-0ecc0ec71d3e', 'password_reset', 'Passwort zuruecksetzen', 'Link zum Zuruecksetzen des Passworts', 'general', 3, '["user_name", "reset_url", "expiry_hours"]'::jsonb),
    (gen_random_uuid(), '9282a473-5c95-4b3a-bf78-0ecc0ec71d3e', 'dsr_receipt', 'DSR Eingangsbestaetigung', 'Bestaetigung fuer eingehende Betroffenenanfrage', 'dsr', 10, '["requester_name", "reference_number", "request_type", "deadline"]'::jsonb),
    (gen_random_uuid(), '9282a473-5c95-4b3a-bf78-0ecc0ec71d3e', 'dsr_identity_request', 'DSR Identitaetsanfrage', 'Anforderung zur Identitaetspruefung', 'dsr', 11, '["requester_name", "reference_number"]'::jsonb),
    (gen_random_uuid(), '9282a473-5c95-4b3a-bf78-0ecc0ec71d3e', 'dsr_completion', 'DSR Abschluss', 'Benachrichtigung ueber abgeschlossene Anfrage', 'dsr', 12, '["requester_name", "reference_number", "request_type", "completion_date"]'::jsonb),
    (gen_random_uuid(), '9282a473-5c95-4b3a-bf78-0ecc0ec71d3e', 'dsr_rejection', 'DSR Ablehnung', 'Benachrichtigung ueber abgelehnte Anfrage', 'dsr', 13, '["requester_name", "reference_number", "rejection_reason", "legal_basis"]'::jsonb),
    (gen_random_uuid(), '9282a473-5c95-4b3a-bf78-0ecc0ec71d3e', 'dsr_extension', 'DSR Fristverlaengerung', 'Benachrichtigung ueber verlaengerte Bearbeitungsfrist', 'dsr', 14, '["requester_name", "reference_number", "new_deadline", "extension_reason"]'::jsonb),
    (gen_random_uuid(), '9282a473-5c95-4b3a-bf78-0ecc0ec71d3e', 'consent_request', 'Einwilligungsanfrage', 'Anfrage zur Einwilligung in Datenverarbeitung', 'consent', 20, '["user_name", "purpose", "consent_url"]'::jsonb),
    (gen_random_uuid(), '9282a473-5c95-4b3a-bf78-0ecc0ec71d3e', 'consent_confirmation', 'Einwilligungsbestaetigung', 'Bestaetigung der erteilten Einwilligung', 'consent', 21, '["user_name", "purpose", "consent_date"]'::jsonb),
    (gen_random_uuid(), '9282a473-5c95-4b3a-bf78-0ecc0ec71d3e', 'consent_withdrawal', 'Widerruf bestaetigt', 'Bestaetigung des Widerrufs einer Einwilligung', 'consent', 22, '["user_name", "purpose", "withdrawal_date"]'::jsonb),
    (gen_random_uuid(), '9282a473-5c95-4b3a-bf78-0ecc0ec71d3e', 'consent_reminder', 'Einwilligungs-Erinnerung', 'Erinnerung an auslaufende Einwilligung', 'consent', 23, '["user_name", "purpose", "expiry_date"]'::jsonb),
    (gen_random_uuid(), '9282a473-5c95-4b3a-bf78-0ecc0ec71d3e', 'breach_notification_authority', 'Datenpanne Aufsichtsbehoerde', 'Meldung an Datenschutzbehoerde (Art. 33)', 'breach', 30, '["incident_date", "incident_description", "affected_count", "measures_taken", "authority_name"]'::jsonb),
    (gen_random_uuid(), '9282a473-5c95-4b3a-bf78-0ecc0ec71d3e', 'breach_notification_affected', 'Datenpanne Betroffene', 'Benachrichtigung betroffener Personen (Art. 34)', 'breach', 31, '["user_name", "incident_date", "incident_description", "measures_taken", "contact_info"]'::jsonb),
    (gen_random_uuid(), '9282a473-5c95-4b3a-bf78-0ecc0ec71d3e', 'breach_internal', 'Datenpanne intern', 'Interne Meldung einer Datenschutzverletzung', 'breach', 32, '["reporter_name", "incident_date", "incident_description", "severity"]'::jsonb),
    (gen_random_uuid(), '9282a473-5c95-4b3a-bf78-0ecc0ec71d3e', 'vendor_dpa_request', 'AVV-Anfrage', 'Anforderung eines Auftragsverarbeitungsvertrags', 'vendor', 40, '["vendor_name", "contact_name", "deadline", "requirements"]'::jsonb),
    (gen_random_uuid(), '9282a473-5c95-4b3a-bf78-0ecc0ec71d3e', 'vendor_review_reminder', 'Vendor-Pruefung Erinnerung', 'Erinnerung an faellige Dienstleisterpruefung', 'vendor', 41, '["vendor_name", "review_due_date", "last_review_date"]'::jsonb),
    (gen_random_uuid(), '9282a473-5c95-4b3a-bf78-0ecc0ec71d3e', 'training_invitation', 'Schulungseinladung', 'Einladung zu Datenschutz-Schulung', 'training', 50, '["user_name", "training_title", "training_date", "training_url"]'::jsonb),
    (gen_random_uuid(), '9282a473-5c95-4b3a-bf78-0ecc0ec71d3e', 'training_reminder', 'Schulungs-Erinnerung', 'Erinnerung an ausstehende Pflichtschulung', 'training', 51, '["user_name", "training_title", "deadline"]'::jsonb),
    (gen_random_uuid(), '9282a473-5c95-4b3a-bf78-0ecc0ec71d3e', 'training_completion', 'Schulung abgeschlossen', 'Bestaetigung und Zertifikat nach Schulungsabschluss', 'training', 52, '["user_name", "training_title", "completion_date", "certificate_url"]'::jsonb)
ON CONFLICT DO NOTHING;