-- BreakPilot Schema-Separation
-- Erstellt 3 getrennte Schemas für Core, Lehrer und Compliance
-- Wird beim ersten Start von postgres ausgeführt

-- Schemas erstellen
CREATE SCHEMA IF NOT EXISTS core;
CREATE SCHEMA IF NOT EXISTS lehrer;
CREATE SCHEMA IF NOT EXISTS compliance;

-- Berechtigungen für breakpilot User
GRANT ALL ON SCHEMA core TO breakpilot;
GRANT ALL ON SCHEMA lehrer TO breakpilot;
GRANT ALL ON SCHEMA compliance TO breakpilot;

-- Default search_path für den breakpilot User
ALTER ROLE breakpilot SET search_path TO public, core, lehrer, compliance;

-- =============================================
-- TABELLEN-MIGRATION: public -> core Schema
-- =============================================
-- Hinweis: Wird nur ausgeführt wenn Tabellen in public existieren
-- Bei Neuinstallation werden Tabellen direkt im richtigen Schema erstellt

DO $$
BEGIN
    -- Core-Tabellen verschieben (falls vorhanden)
    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'users') THEN
        ALTER TABLE public.users SET SCHEMA core;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'sessions') THEN
        ALTER TABLE public.sessions SET SCHEMA core;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'consent_records') THEN
        ALTER TABLE public.consent_records SET SCHEMA core;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'notifications') THEN
        ALTER TABLE public.notifications SET SCHEMA core;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'email_templates') THEN
        ALTER TABLE public.email_templates SET SCHEMA core;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'billing_customers') THEN
        ALTER TABLE public.billing_customers SET SCHEMA core;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'billing_subscriptions') THEN
        ALTER TABLE public.billing_subscriptions SET SCHEMA core;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'billing_invoices') THEN
        ALTER TABLE public.billing_invoices SET SCHEMA core;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'rbac_roles') THEN
        ALTER TABLE public.rbac_roles SET SCHEMA core;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'rbac_permissions') THEN
        ALTER TABLE public.rbac_permissions SET SCHEMA core;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'rbac_role_permissions') THEN
        ALTER TABLE public.rbac_role_permissions SET SCHEMA core;
    END IF;

    -- Lehrer-Tabellen verschieben (falls vorhanden)
    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'classrooms') THEN
        ALTER TABLE public.classrooms SET SCHEMA lehrer;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'learning_units') THEN
        ALTER TABLE public.learning_units SET SCHEMA lehrer;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'klausuren') THEN
        ALTER TABLE public.klausuren SET SCHEMA lehrer;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'corrections') THEN
        ALTER TABLE public.corrections SET SCHEMA lehrer;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'worksheets') THEN
        ALTER TABLE public.worksheets SET SCHEMA lehrer;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'certificates') THEN
        ALTER TABLE public.certificates SET SCHEMA lehrer;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'letters') THEN
        ALTER TABLE public.letters SET SCHEMA lehrer;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'meetings') THEN
        ALTER TABLE public.meetings SET SCHEMA lehrer;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'messenger_contacts') THEN
        ALTER TABLE public.messenger_contacts SET SCHEMA lehrer;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'messenger_conversations') THEN
        ALTER TABLE public.messenger_conversations SET SCHEMA lehrer;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'messenger_messages') THEN
        ALTER TABLE public.messenger_messages SET SCHEMA lehrer;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'vocab_sessions') THEN
        ALTER TABLE public.vocab_sessions SET SCHEMA lehrer;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'game_sessions') THEN
        ALTER TABLE public.game_sessions SET SCHEMA lehrer;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'game_scores') THEN
        ALTER TABLE public.game_scores SET SCHEMA lehrer;
    END IF;

    -- Compliance-Tabellen verschieben (falls vorhanden)
    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'compliance_risks') THEN
        ALTER TABLE public.compliance_risks SET SCHEMA compliance;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'compliance_controls') THEN
        ALTER TABLE public.compliance_controls SET SCHEMA compliance;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'compliance_requirements') THEN
        ALTER TABLE public.compliance_requirements SET SCHEMA compliance;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'compliance_evidence') THEN
        ALTER TABLE public.compliance_evidence SET SCHEMA compliance;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'dsr_requests') THEN
        ALTER TABLE public.dsr_requests SET SCHEMA compliance;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'sdk_tenants') THEN
        ALTER TABLE public.sdk_tenants SET SCHEMA compliance;
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'sdk_audit_logs') THEN
        ALTER TABLE public.sdk_audit_logs SET SCHEMA compliance;
    END IF;

    RAISE NOTICE 'Schema migration complete.';
END $$;

-- Cross-Schema Views für häufige Lookups
CREATE OR REPLACE VIEW compliance.v_users AS SELECT * FROM core.users;
CREATE OR REPLACE VIEW lehrer.v_users AS SELECT * FROM core.users;
CREATE OR REPLACE VIEW lehrer.v_consent_records AS SELECT * FROM core.consent_records;
CREATE OR REPLACE VIEW compliance.v_consent_records AS SELECT * FROM core.consent_records;