breakpilot-pwa/consent-service/internal/database/database.go

package database

import (
	"context"
	"fmt"
	"time"

	"github.com/jackc/pgx/v5/pgxpool"
)

// DB wraps the pgx pool
type DB struct {
	Pool *pgxpool.Pool
}

// Connect establishes a connection to the PostgreSQL database
func Connect(databaseURL string) (*DB, error) {
	config, err := pgxpool.ParseConfig(databaseURL)
	if err != nil {
		return nil, fmt.Errorf("failed to parse database URL: %w", err)
	}

	// Configure connection pool
	config.MaxConns = 25
	config.MinConns = 5
	config.MaxConnLifetime = time.Hour
	config.MaxConnIdleTime = 30 * time.Minute
	config.HealthCheckPeriod = time.Minute

	ctx, cancel := context.WithTimeout(context.Background(), 10*time.Second)
	defer cancel()

	pool, err := pgxpool.NewWithConfig(ctx, config)
	if err != nil {
		return nil, fmt.Errorf("failed to create connection pool: %w", err)
	}

	// Test the connection
	if err := pool.Ping(ctx); err != nil {
		return nil, fmt.Errorf("failed to ping database: %w", err)
	}

	return &DB{Pool: pool}, nil
}

// Close closes the database connection pool
func (db *DB) Close() {
	db.Pool.Close()
}

// Migrate runs database migrations
func Migrate(db *DB) error {
	ctx := context.Background()

	// Create tables
	migrations := []string{
		// Users table (extended for full auth)
		`CREATE TABLE IF NOT EXISTS users (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			external_id VARCHAR(255) UNIQUE,
			email VARCHAR(255) UNIQUE NOT NULL,
			password_hash VARCHAR(255),
			name VARCHAR(255),
			role VARCHAR(50) DEFAULT 'user',
			email_verified BOOLEAN DEFAULT FALSE,
			email_verified_at TIMESTAMPTZ,
			account_status VARCHAR(20) DEFAULT 'active',
			last_login_at TIMESTAMPTZ,
			failed_login_attempts INT DEFAULT 0,
			locked_until TIMESTAMPTZ,
			created_at TIMESTAMPTZ DEFAULT NOW(),
			updated_at TIMESTAMPTZ DEFAULT NOW()
		)`,

		// Legal documents table
		`CREATE TABLE IF NOT EXISTS legal_documents (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			type VARCHAR(50) NOT NULL,
			name VARCHAR(255) NOT NULL,
			description TEXT,
			is_mandatory BOOLEAN DEFAULT true,
			is_active BOOLEAN DEFAULT true,
			sort_order INT DEFAULT 0,
			created_at TIMESTAMPTZ DEFAULT NOW(),
			updated_at TIMESTAMPTZ DEFAULT NOW()
		)`,

		// Document versions table
		`CREATE TABLE IF NOT EXISTS document_versions (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			document_id UUID REFERENCES legal_documents(id) ON DELETE CASCADE,
			version VARCHAR(20) NOT NULL,
			language VARCHAR(5) DEFAULT 'de',
			title VARCHAR(255) NOT NULL,
			content TEXT NOT NULL,
			summary TEXT,
			status VARCHAR(20) DEFAULT 'draft',
			published_at TIMESTAMPTZ,
			scheduled_publish_at TIMESTAMPTZ,
			created_by UUID REFERENCES users(id),
			approved_by UUID REFERENCES users(id),
			approved_at TIMESTAMPTZ,
			created_at TIMESTAMPTZ DEFAULT NOW(),
			updated_at TIMESTAMPTZ DEFAULT NOW(),
			UNIQUE(document_id, version, language)
		)`,

		// Add scheduled_publish_at column if not exists (migration)
		`ALTER TABLE document_versions ADD COLUMN IF NOT EXISTS scheduled_publish_at TIMESTAMPTZ`,
		`ALTER TABLE document_versions ADD COLUMN IF NOT EXISTS approved_at TIMESTAMPTZ`,

		// User consents table
		`CREATE TABLE IF NOT EXISTS user_consents (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			user_id UUID REFERENCES users(id) ON DELETE CASCADE,
			document_version_id UUID REFERENCES document_versions(id),
			consented BOOLEAN NOT NULL,
			ip_address INET,
			user_agent TEXT,
			consented_at TIMESTAMPTZ DEFAULT NOW(),
			withdrawn_at TIMESTAMPTZ,
			UNIQUE(user_id, document_version_id)
		)`,

		// Cookie categories table
		`CREATE TABLE IF NOT EXISTS cookie_categories (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			name VARCHAR(100) NOT NULL UNIQUE,
			display_name_de VARCHAR(255) NOT NULL,
			display_name_en VARCHAR(255),
			description_de TEXT,
			description_en TEXT,
			is_mandatory BOOLEAN DEFAULT false,
			sort_order INT DEFAULT 0,
			is_active BOOLEAN DEFAULT true,
			created_at TIMESTAMPTZ DEFAULT NOW(),
			updated_at TIMESTAMPTZ DEFAULT NOW()
		)`,

		// Cookie consents table
		`CREATE TABLE IF NOT EXISTS cookie_consents (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			user_id UUID REFERENCES users(id) ON DELETE CASCADE,
			category_id UUID REFERENCES cookie_categories(id) ON DELETE CASCADE,
			consented BOOLEAN NOT NULL,
			consented_at TIMESTAMPTZ DEFAULT NOW(),
			updated_at TIMESTAMPTZ DEFAULT NOW(),
			UNIQUE(user_id, category_id)
		)`,

		// Audit log table
		`CREATE TABLE IF NOT EXISTS consent_audit_log (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			user_id UUID REFERENCES users(id) ON DELETE SET NULL,
			action VARCHAR(50) NOT NULL,
			entity_type VARCHAR(50),
			entity_id UUID,
			details JSONB,
			ip_address INET,
			user_agent TEXT,
			created_at TIMESTAMPTZ DEFAULT NOW()
		)`,

		// Data export requests table
		`CREATE TABLE IF NOT EXISTS data_export_requests (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			user_id UUID REFERENCES users(id) ON DELETE CASCADE,
			status VARCHAR(20) DEFAULT 'pending',
			download_url TEXT,
			expires_at TIMESTAMPTZ,
			created_at TIMESTAMPTZ DEFAULT NOW(),
			completed_at TIMESTAMPTZ
		)`,

		// Data deletion requests table
		`CREATE TABLE IF NOT EXISTS data_deletion_requests (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			user_id UUID REFERENCES users(id) ON DELETE CASCADE,
			status VARCHAR(20) DEFAULT 'pending',
			reason TEXT,
			created_at TIMESTAMPTZ DEFAULT NOW(),
			processed_at TIMESTAMPTZ,
			processed_by UUID REFERENCES users(id)
		)`,

		// =============================================
		// Phase 1: User Management Tables
		// =============================================

		// Email verification tokens
		`CREATE TABLE IF NOT EXISTS email_verification_tokens (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			user_id UUID REFERENCES users(id) ON DELETE CASCADE,
			token VARCHAR(255) UNIQUE NOT NULL,
			expires_at TIMESTAMPTZ NOT NULL,
			used_at TIMESTAMPTZ,
			created_at TIMESTAMPTZ DEFAULT NOW()
		)`,

		// Password reset tokens
		`CREATE TABLE IF NOT EXISTS password_reset_tokens (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			user_id UUID REFERENCES users(id) ON DELETE CASCADE,
			token VARCHAR(255) UNIQUE NOT NULL,
			expires_at TIMESTAMPTZ NOT NULL,
			used_at TIMESTAMPTZ,
			ip_address INET,
			created_at TIMESTAMPTZ DEFAULT NOW()
		)`,

		// User sessions (for JWT revocation and session management)
		`CREATE TABLE IF NOT EXISTS user_sessions (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			user_id UUID REFERENCES users(id) ON DELETE CASCADE,
			token_hash VARCHAR(255) NOT NULL,
			device_info TEXT,
			ip_address INET,
			user_agent TEXT,
			expires_at TIMESTAMPTZ NOT NULL,
			revoked_at TIMESTAMPTZ,
			created_at TIMESTAMPTZ DEFAULT NOW(),
			last_activity_at TIMESTAMPTZ DEFAULT NOW()
		)`,

		// =============================================
		// Phase 3: Version Approvals (DSB Workflow)
		// =============================================

		// Version approval tracking
		`CREATE TABLE IF NOT EXISTS version_approvals (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			version_id UUID REFERENCES document_versions(id) ON DELETE CASCADE,
			approver_id UUID REFERENCES users(id),
			action VARCHAR(30) NOT NULL,
			comment TEXT,
			created_at TIMESTAMPTZ DEFAULT NOW()
		)`,

		// =============================================
		// Phase 4: Notification System
		// =============================================

		// Notifications
		`CREATE TABLE IF NOT EXISTS notifications (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			user_id UUID REFERENCES users(id) ON DELETE CASCADE,
			type VARCHAR(50) NOT NULL,
			channel VARCHAR(20) NOT NULL,
			title VARCHAR(255) NOT NULL,
			body TEXT NOT NULL,
			data JSONB,
			read_at TIMESTAMPTZ,
			sent_at TIMESTAMPTZ,
			created_at TIMESTAMPTZ DEFAULT NOW()
		)`,

		// Push subscriptions for Web Push
		`CREATE TABLE IF NOT EXISTS push_subscriptions (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			user_id UUID REFERENCES users(id) ON DELETE CASCADE,
			endpoint TEXT NOT NULL,
			p256dh TEXT NOT NULL,
			auth TEXT NOT NULL,
			user_agent TEXT,
			created_at TIMESTAMPTZ DEFAULT NOW(),
			UNIQUE(user_id, endpoint)
		)`,

		// Notification preferences per user
		`CREATE TABLE IF NOT EXISTS notification_preferences (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			user_id UUID REFERENCES users(id) ON DELETE CASCADE UNIQUE,
			email_enabled BOOLEAN DEFAULT TRUE,
			push_enabled BOOLEAN DEFAULT TRUE,
			in_app_enabled BOOLEAN DEFAULT TRUE,
			reminder_frequency VARCHAR(20) DEFAULT 'weekly',
			created_at TIMESTAMPTZ DEFAULT NOW(),
			updated_at TIMESTAMPTZ DEFAULT NOW()
		)`,

		// =============================================
		// Phase 5: Consent Deadlines & Account Suspension
		// =============================================

		// Consent deadlines per user per version
		`CREATE TABLE IF NOT EXISTS consent_deadlines (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			user_id UUID REFERENCES users(id) ON DELETE CASCADE,
			document_version_id UUID REFERENCES document_versions(id) ON DELETE CASCADE,
			deadline_at TIMESTAMPTZ NOT NULL,
			reminder_count INT DEFAULT 0,
			last_reminder_at TIMESTAMPTZ,
			consent_given_at TIMESTAMPTZ,
			created_at TIMESTAMPTZ DEFAULT NOW(),
			UNIQUE(user_id, document_version_id)
		)`,

		// Account suspensions tracking
		`CREATE TABLE IF NOT EXISTS account_suspensions (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			user_id UUID REFERENCES users(id) ON DELETE CASCADE,
			reason VARCHAR(50) NOT NULL,
			details JSONB,
			suspended_at TIMESTAMPTZ DEFAULT NOW(),
			lifted_at TIMESTAMPTZ,
			lifted_reason TEXT
		)`,

		// =============================================
		// Indexes for performance
		// =============================================
		`CREATE INDEX IF NOT EXISTS idx_user_consents_user ON user_consents(user_id)`,
		`CREATE INDEX IF NOT EXISTS idx_user_consents_version ON user_consents(document_version_id)`,
		`CREATE INDEX IF NOT EXISTS idx_cookie_consents_user ON cookie_consents(user_id)`,
		`CREATE INDEX IF NOT EXISTS idx_audit_log_user ON consent_audit_log(user_id)`,
		`CREATE INDEX IF NOT EXISTS idx_audit_log_created ON consent_audit_log(created_at)`,
		`CREATE INDEX IF NOT EXISTS idx_document_versions_document ON document_versions(document_id)`,
		`CREATE INDEX IF NOT EXISTS idx_document_versions_status ON document_versions(status)`,
		`CREATE INDEX IF NOT EXISTS idx_legal_documents_type ON legal_documents(type)`,

		// Phase 1: Auth indexes
		`CREATE INDEX IF NOT EXISTS idx_email_verification_tokens_token ON email_verification_tokens(token)`,
		`CREATE INDEX IF NOT EXISTS idx_email_verification_tokens_user ON email_verification_tokens(user_id)`,
		`CREATE INDEX IF NOT EXISTS idx_password_reset_tokens_token ON password_reset_tokens(token)`,
		`CREATE INDEX IF NOT EXISTS idx_user_sessions_user ON user_sessions(user_id)`,
		`CREATE INDEX IF NOT EXISTS idx_user_sessions_token ON user_sessions(token_hash)`,

		// Phase 3: Approval indexes
		`CREATE INDEX IF NOT EXISTS idx_version_approvals_version ON version_approvals(version_id)`,

		// Phase 4: Notification indexes
		`CREATE INDEX IF NOT EXISTS idx_notifications_user ON notifications(user_id)`,
		`CREATE INDEX IF NOT EXISTS idx_notifications_unread ON notifications(user_id, read_at)`,
		`CREATE INDEX IF NOT EXISTS idx_push_subscriptions_user ON push_subscriptions(user_id)`,

		// Phase 5: Deadline indexes
		`CREATE INDEX IF NOT EXISTS idx_consent_deadlines_user ON consent_deadlines(user_id)`,
		`CREATE INDEX IF NOT EXISTS idx_consent_deadlines_deadline ON consent_deadlines(deadline_at)`,
		`CREATE INDEX IF NOT EXISTS idx_account_suspensions_user ON account_suspensions(user_id)`,

		// =============================================
		// Phase 6: OAuth 2.0 Authorization Code Flow
		// =============================================

		// OAuth 2.0 Clients
		`CREATE TABLE IF NOT EXISTS oauth_clients (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			client_id VARCHAR(64) UNIQUE NOT NULL,
			client_secret VARCHAR(255),
			name VARCHAR(255) NOT NULL,
			description TEXT,
			redirect_uris JSONB NOT NULL DEFAULT '[]',
			scopes JSONB NOT NULL DEFAULT '["openid", "profile", "email"]',
			grant_types JSONB NOT NULL DEFAULT '["authorization_code", "refresh_token"]',
			is_public BOOLEAN DEFAULT FALSE,
			is_active BOOLEAN DEFAULT TRUE,
			created_by UUID REFERENCES users(id),
			created_at TIMESTAMPTZ DEFAULT NOW(),
			updated_at TIMESTAMPTZ DEFAULT NOW()
		)`,

		// OAuth 2.0 Authorization Codes
		`CREATE TABLE IF NOT EXISTS oauth_authorization_codes (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			code VARCHAR(255) UNIQUE NOT NULL,
			client_id VARCHAR(64) NOT NULL REFERENCES oauth_clients(client_id),
			user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
			redirect_uri TEXT NOT NULL,
			scopes JSONB NOT NULL DEFAULT '[]',
			code_challenge VARCHAR(255),
			code_challenge_method VARCHAR(10),
			expires_at TIMESTAMPTZ NOT NULL,
			used_at TIMESTAMPTZ,
			created_at TIMESTAMPTZ DEFAULT NOW()
		)`,

		// OAuth 2.0 Access Tokens
		`CREATE TABLE IF NOT EXISTS oauth_access_tokens (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			token_hash VARCHAR(255) UNIQUE NOT NULL,
			client_id VARCHAR(64) NOT NULL REFERENCES oauth_clients(client_id),
			user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
			scopes JSONB NOT NULL DEFAULT '[]',
			expires_at TIMESTAMPTZ NOT NULL,
			revoked_at TIMESTAMPTZ,
			created_at TIMESTAMPTZ DEFAULT NOW()
		)`,

		// OAuth 2.0 Refresh Tokens
		`CREATE TABLE IF NOT EXISTS oauth_refresh_tokens (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			token_hash VARCHAR(255) UNIQUE NOT NULL,
			access_token_id UUID REFERENCES oauth_access_tokens(id) ON DELETE CASCADE,
			client_id VARCHAR(64) NOT NULL REFERENCES oauth_clients(client_id),
			user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
			scopes JSONB NOT NULL DEFAULT '[]',
			expires_at TIMESTAMPTZ NOT NULL,
			revoked_at TIMESTAMPTZ,
			created_at TIMESTAMPTZ DEFAULT NOW()
		)`,

		// =============================================
		// Phase 7: Two-Factor Authentication (2FA/TOTP)
		// =============================================

		// User TOTP secrets and recovery codes
		`CREATE TABLE IF NOT EXISTS user_totp (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			user_id UUID UNIQUE NOT NULL REFERENCES users(id) ON DELETE CASCADE,
			secret VARCHAR(255) NOT NULL,
			verified BOOLEAN DEFAULT FALSE,
			recovery_codes JSONB DEFAULT '[]',
			enabled_at TIMESTAMPTZ,
			last_used_at TIMESTAMPTZ,
			created_at TIMESTAMPTZ DEFAULT NOW(),
			updated_at TIMESTAMPTZ DEFAULT NOW()
		)`,

		// 2FA challenges during login
		`CREATE TABLE IF NOT EXISTS two_factor_challenges (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
			challenge_id VARCHAR(255) UNIQUE NOT NULL,
			ip_address INET,
			user_agent TEXT,
			expires_at TIMESTAMPTZ NOT NULL,
			used_at TIMESTAMPTZ,
			created_at TIMESTAMPTZ DEFAULT NOW()
		)`,

		// Add 2FA required flag to users
		`ALTER TABLE users ADD COLUMN IF NOT EXISTS two_factor_enabled BOOLEAN DEFAULT FALSE`,
		`ALTER TABLE users ADD COLUMN IF NOT EXISTS two_factor_verified_at TIMESTAMPTZ`,

		// Phase 6 & 7 Indexes
		`CREATE INDEX IF NOT EXISTS idx_oauth_clients_client_id ON oauth_clients(client_id)`,
		`CREATE INDEX IF NOT EXISTS idx_oauth_auth_codes_code ON oauth_authorization_codes(code)`,
		`CREATE INDEX IF NOT EXISTS idx_oauth_auth_codes_user ON oauth_authorization_codes(user_id)`,
		`CREATE INDEX IF NOT EXISTS idx_oauth_access_tokens_hash ON oauth_access_tokens(token_hash)`,
		`CREATE INDEX IF NOT EXISTS idx_oauth_access_tokens_user ON oauth_access_tokens(user_id)`,
		`CREATE INDEX IF NOT EXISTS idx_oauth_refresh_tokens_hash ON oauth_refresh_tokens(token_hash)`,
		`CREATE INDEX IF NOT EXISTS idx_oauth_refresh_tokens_user ON oauth_refresh_tokens(user_id)`,
		`CREATE INDEX IF NOT EXISTS idx_user_totp_user ON user_totp(user_id)`,
		`CREATE INDEX IF NOT EXISTS idx_two_factor_challenges_id ON two_factor_challenges(challenge_id)`,
		`CREATE INDEX IF NOT EXISTS idx_two_factor_challenges_user ON two_factor_challenges(user_id)`,

		// Insert default OAuth client for BreakPilot PWA (public client with PKCE)
		`INSERT INTO oauth_clients (client_id, name, description, redirect_uris, scopes, grant_types, is_public)
		VALUES (
			'breakpilot-pwa',
			'BreakPilot PWA',
			'Official BreakPilot Progressive Web Application',
			'["http://localhost:8000/oauth/callback", "http://localhost:8000/app/oauth/callback"]',
			'["openid", "profile", "email", "consent:read", "consent:write"]',
			'["authorization_code", "refresh_token"]',
			true
		) ON CONFLICT (client_id) DO NOTHING`,

		// Insert default cookie categories
		`INSERT INTO cookie_categories (name, display_name_de, display_name_en, description_de, description_en, is_mandatory, sort_order)
		VALUES
			('necessary', 'Notwendige Cookies', 'Necessary Cookies',
			 'Diese Cookies sind für die Grundfunktionen der Website unbedingt erforderlich.',
			 'These cookies are essential for the basic functions of the website.',
			 true, 1),
			('functional', 'Funktionale Cookies', 'Functional Cookies',
			 'Diese Cookies ermöglichen erweiterte Funktionen und Personalisierung.',
			 'These cookies enable enhanced functionality and personalization.',
			 false, 2),
			('analytics', 'Analyse Cookies', 'Analytics Cookies',
			 'Diese Cookies helfen uns zu verstehen, wie Besucher mit der Website interagieren.',
			 'These cookies help us understand how visitors interact with the website.',
			 false, 3),
			('marketing', 'Marketing Cookies', 'Marketing Cookies',
			 'Diese Cookies werden verwendet, um Werbung relevanter für Sie zu gestalten.',
			 'These cookies are used to make advertising more relevant to you.',
			 false, 4)
		ON CONFLICT (name) DO NOTHING`,

		// Insert default legal documents
		`INSERT INTO legal_documents (type, name, description, is_mandatory, sort_order)
		VALUES
			('terms', 'Allgemeine Geschäftsbedingungen', 'Die allgemeinen Geschäftsbedingungen für die Nutzung von BreakPilot.', true, 1),
			('privacy', 'Datenschutzerklärung', 'Informationen über die Verarbeitung Ihrer personenbezogenen Daten.', true, 2),
			('cookies', 'Cookie-Richtlinie', 'Informationen über die Verwendung von Cookies auf unserer Website.', false, 3),
			('community', 'Community Guidelines', 'Regeln für das Verhalten in der BreakPilot Community.', true, 4)
		ON CONFLICT DO NOTHING`,

		// =============================================
		// Phase 8: E-Mail Templates (Transactional)
		// =============================================

		// Email templates (like legal_documents)
		`CREATE TABLE IF NOT EXISTS email_templates (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			type VARCHAR(50) UNIQUE NOT NULL,
			name VARCHAR(255) NOT NULL,
			description TEXT,
			is_active BOOLEAN DEFAULT TRUE,
			sort_order INT DEFAULT 0,
			created_at TIMESTAMPTZ DEFAULT NOW(),
			updated_at TIMESTAMPTZ DEFAULT NOW()
		)`,

		// Email template versions (like document_versions)
		`CREATE TABLE IF NOT EXISTS email_template_versions (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			template_id UUID REFERENCES email_templates(id) ON DELETE CASCADE,
			version VARCHAR(20) NOT NULL,
			language VARCHAR(5) DEFAULT 'de',
			subject VARCHAR(500) NOT NULL,
			body_html TEXT NOT NULL,
			body_text TEXT NOT NULL,
			summary TEXT,
			status VARCHAR(20) DEFAULT 'draft',
			published_at TIMESTAMPTZ,
			scheduled_publish_at TIMESTAMPTZ,
			created_by UUID REFERENCES users(id),
			approved_by UUID REFERENCES users(id),
			approved_at TIMESTAMPTZ,
			created_at TIMESTAMPTZ DEFAULT NOW(),
			updated_at TIMESTAMPTZ DEFAULT NOW(),
			UNIQUE(template_id, version, language)
		)`,

		// Email template approvals (like version_approvals)
		`CREATE TABLE IF NOT EXISTS email_template_approvals (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			version_id UUID REFERENCES email_template_versions(id) ON DELETE CASCADE,
			approver_id UUID REFERENCES users(id),
			action VARCHAR(30) NOT NULL,
			comment TEXT,
			created_at TIMESTAMPTZ DEFAULT NOW()
		)`,

		// Email send logs for audit
		`CREATE TABLE IF NOT EXISTS email_send_logs (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			user_id UUID REFERENCES users(id) ON DELETE SET NULL,
			version_id UUID REFERENCES email_template_versions(id) ON DELETE SET NULL,
			recipient VARCHAR(255) NOT NULL,
			subject VARCHAR(500) NOT NULL,
			status VARCHAR(20) DEFAULT 'queued',
			error_msg TEXT,
			variables JSONB,
			sent_at TIMESTAMPTZ,
			delivered_at TIMESTAMPTZ,
			created_at TIMESTAMPTZ DEFAULT NOW()
		)`,

		// Global email settings (logo, colors, signature)
		`CREATE TABLE IF NOT EXISTS email_template_settings (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			logo_url TEXT,
			logo_base64 TEXT,
			company_name VARCHAR(255) DEFAULT 'BreakPilot',
			sender_name VARCHAR(255) DEFAULT 'BreakPilot',
			sender_email VARCHAR(255) DEFAULT 'noreply@breakpilot.app',
			reply_to_email VARCHAR(255),
			footer_html TEXT,
			footer_text TEXT,
			primary_color VARCHAR(7) DEFAULT '#2563eb',
			secondary_color VARCHAR(7) DEFAULT '#64748b',
			updated_at TIMESTAMPTZ DEFAULT NOW(),
			updated_by UUID REFERENCES users(id)
		)`,

		// Insert default email settings
		`INSERT INTO email_template_settings (id, company_name, sender_name, sender_email, primary_color, secondary_color)
		VALUES (gen_random_uuid(), 'BreakPilot', 'BreakPilot', 'noreply@breakpilot.app', '#2563eb', '#64748b')
		ON CONFLICT DO NOTHING`,

		// Phase 8 Indexes
		`CREATE INDEX IF NOT EXISTS idx_email_templates_type ON email_templates(type)`,
		`CREATE INDEX IF NOT EXISTS idx_email_template_versions_template ON email_template_versions(template_id)`,
		`CREATE INDEX IF NOT EXISTS idx_email_template_versions_status ON email_template_versions(status)`,
		`CREATE INDEX IF NOT EXISTS idx_email_template_approvals_version ON email_template_approvals(version_id)`,
		`CREATE INDEX IF NOT EXISTS idx_email_send_logs_user ON email_send_logs(user_id)`,
		`CREATE INDEX IF NOT EXISTS idx_email_send_logs_created ON email_send_logs(created_at)`,
		`CREATE INDEX IF NOT EXISTS idx_email_send_logs_status ON email_send_logs(status)`,

		// =============================================
		// Phase 9: Schulverwaltung / School Management
		// Matrix-basierte Kommunikation für Schulen
		// =============================================

		// Schools table
		`CREATE TABLE IF NOT EXISTS schools (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			name VARCHAR(255) NOT NULL,
			short_name VARCHAR(50),
			type VARCHAR(50) NOT NULL,
			address TEXT,
			city VARCHAR(100),
			postal_code VARCHAR(20),
			state VARCHAR(50),
			country VARCHAR(2) DEFAULT 'DE',
			phone VARCHAR(50),
			email VARCHAR(255),
			website VARCHAR(255),
			matrix_server_name VARCHAR(255),
			logo_url TEXT,
			is_active BOOLEAN DEFAULT TRUE,
			created_at TIMESTAMPTZ DEFAULT NOW(),
			updated_at TIMESTAMPTZ DEFAULT NOW()
		)`,

		// School years
		`CREATE TABLE IF NOT EXISTS school_years (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			school_id UUID NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
			name VARCHAR(20) NOT NULL,
			start_date DATE NOT NULL,
			end_date DATE NOT NULL,
			is_current BOOLEAN DEFAULT FALSE,
			created_at TIMESTAMPTZ DEFAULT NOW(),
			UNIQUE(school_id, name)
		)`,

		// Subjects
		`CREATE TABLE IF NOT EXISTS subjects (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			school_id UUID NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
			name VARCHAR(100) NOT NULL,
			short_name VARCHAR(10) NOT NULL,
			color VARCHAR(7),
			is_active BOOLEAN DEFAULT TRUE,
			created_at TIMESTAMPTZ DEFAULT NOW(),
			UNIQUE(school_id, short_name)
		)`,

		// Classes
		`CREATE TABLE IF NOT EXISTS classes (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			school_id UUID NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
			school_year_id UUID NOT NULL REFERENCES school_years(id) ON DELETE CASCADE,
			name VARCHAR(20) NOT NULL,
			grade INT NOT NULL,
			section VARCHAR(5),
			room VARCHAR(50),
			matrix_info_room VARCHAR(255),
			matrix_rep_room VARCHAR(255),
			is_active BOOLEAN DEFAULT TRUE,
			created_at TIMESTAMPTZ DEFAULT NOW(),
			updated_at TIMESTAMPTZ DEFAULT NOW(),
			UNIQUE(school_id, school_year_id, name)
		)`,

		// Students
		`CREATE TABLE IF NOT EXISTS students (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			school_id UUID NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
			class_id UUID NOT NULL REFERENCES classes(id) ON DELETE CASCADE,
			user_id UUID REFERENCES users(id) ON DELETE SET NULL,
			student_number VARCHAR(50),
			first_name VARCHAR(100) NOT NULL,
			last_name VARCHAR(100) NOT NULL,
			date_of_birth DATE,
			gender VARCHAR(1),
			matrix_user_id VARCHAR(255),
			matrix_dm_room VARCHAR(255),
			is_active BOOLEAN DEFAULT TRUE,
			created_at TIMESTAMPTZ DEFAULT NOW(),
			updated_at TIMESTAMPTZ DEFAULT NOW()
		)`,

		// Teachers
		`CREATE TABLE IF NOT EXISTS teachers (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			school_id UUID NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
			user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
			teacher_code VARCHAR(10),
			title VARCHAR(20),
			first_name VARCHAR(100) NOT NULL,
			last_name VARCHAR(100) NOT NULL,
			matrix_user_id VARCHAR(255),
			is_active BOOLEAN DEFAULT TRUE,
			created_at TIMESTAMPTZ DEFAULT NOW(),
			updated_at TIMESTAMPTZ DEFAULT NOW(),
			UNIQUE(school_id, user_id)
		)`,

		// Class teachers assignment
		`CREATE TABLE IF NOT EXISTS class_teachers (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			class_id UUID NOT NULL REFERENCES classes(id) ON DELETE CASCADE,
			teacher_id UUID NOT NULL REFERENCES teachers(id) ON DELETE CASCADE,
			is_primary BOOLEAN DEFAULT FALSE,
			created_at TIMESTAMPTZ DEFAULT NOW(),
			UNIQUE(class_id, teacher_id)
		)`,

		// Teacher subjects assignment
		`CREATE TABLE IF NOT EXISTS teacher_subjects (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			teacher_id UUID NOT NULL REFERENCES teachers(id) ON DELETE CASCADE,
			subject_id UUID NOT NULL REFERENCES subjects(id) ON DELETE CASCADE,
			created_at TIMESTAMPTZ DEFAULT NOW(),
			UNIQUE(teacher_id, subject_id)
		)`,

		// Parents
		`CREATE TABLE IF NOT EXISTS parents (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
			matrix_user_id VARCHAR(255),
			first_name VARCHAR(100) NOT NULL,
			last_name VARCHAR(100) NOT NULL,
			phone VARCHAR(50),
			emergency_contact BOOLEAN DEFAULT FALSE,
			created_at TIMESTAMPTZ DEFAULT NOW(),
			updated_at TIMESTAMPTZ DEFAULT NOW(),
			UNIQUE(user_id)
		)`,

		// Student-parent relationships
		`CREATE TABLE IF NOT EXISTS student_parents (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			student_id UUID NOT NULL REFERENCES students(id) ON DELETE CASCADE,
			parent_id UUID NOT NULL REFERENCES parents(id) ON DELETE CASCADE,
			relationship VARCHAR(20) NOT NULL,
			is_primary BOOLEAN DEFAULT FALSE,
			has_custody BOOLEAN DEFAULT TRUE,
			created_at TIMESTAMPTZ DEFAULT NOW(),
			UNIQUE(student_id, parent_id)
		)`,

		// Parent representatives
		`CREATE TABLE IF NOT EXISTS parent_representatives (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			class_id UUID NOT NULL REFERENCES classes(id) ON DELETE CASCADE,
			parent_id UUID NOT NULL REFERENCES parents(id) ON DELETE CASCADE,
			role VARCHAR(20) NOT NULL,
			elected_at TIMESTAMPTZ NOT NULL,
			expires_at TIMESTAMPTZ,
			is_active BOOLEAN DEFAULT TRUE,
			created_at TIMESTAMPTZ DEFAULT NOW()
		)`,

		// =============================================
		// Stundenplan / Timetable
		// =============================================

		// Timetable slots (Stundenraster)
		`CREATE TABLE IF NOT EXISTS timetable_slots (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			school_id UUID NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
			slot_number INT NOT NULL,
			start_time TIME NOT NULL,
			end_time TIME NOT NULL,
			is_break BOOLEAN DEFAULT FALSE,
			name VARCHAR(50),
			UNIQUE(school_id, slot_number)
		)`,

		// Timetable entries (Stundenplan)
		`CREATE TABLE IF NOT EXISTS timetable_entries (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			school_year_id UUID NOT NULL REFERENCES school_years(id) ON DELETE CASCADE,
			class_id UUID NOT NULL REFERENCES classes(id) ON DELETE CASCADE,
			subject_id UUID NOT NULL REFERENCES subjects(id) ON DELETE CASCADE,
			teacher_id UUID NOT NULL REFERENCES teachers(id) ON DELETE CASCADE,
			slot_id UUID NOT NULL REFERENCES timetable_slots(id) ON DELETE CASCADE,
			day_of_week INT NOT NULL CHECK (day_of_week >= 1 AND day_of_week <= 7),
			room VARCHAR(50),
			valid_from DATE NOT NULL,
			valid_until DATE,
			created_at TIMESTAMPTZ DEFAULT NOW(),
			updated_at TIMESTAMPTZ DEFAULT NOW()
		)`,

		// Timetable substitutions (Vertretungsplan)
		`CREATE TABLE IF NOT EXISTS timetable_substitutions (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			original_entry_id UUID NOT NULL REFERENCES timetable_entries(id) ON DELETE CASCADE,
			date DATE NOT NULL,
			substitute_teacher_id UUID REFERENCES teachers(id) ON DELETE SET NULL,
			substitute_subject_id UUID REFERENCES subjects(id) ON DELETE SET NULL,
			room VARCHAR(50),
			type VARCHAR(20) NOT NULL,
			note TEXT,
			created_at TIMESTAMPTZ DEFAULT NOW(),
			created_by UUID NOT NULL REFERENCES users(id)
		)`,

		// =============================================
		// Abwesenheit / Attendance
		// =============================================

		// Attendance records per lesson
		`CREATE TABLE IF NOT EXISTS attendance_records (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			student_id UUID NOT NULL REFERENCES students(id) ON DELETE CASCADE,
			timetable_entry_id UUID REFERENCES timetable_entries(id) ON DELETE SET NULL,
			date DATE NOT NULL,
			slot_id UUID NOT NULL REFERENCES timetable_slots(id) ON DELETE CASCADE,
			status VARCHAR(30) NOT NULL,
			recorded_by UUID NOT NULL REFERENCES users(id),
			note TEXT,
			created_at TIMESTAMPTZ DEFAULT NOW(),
			updated_at TIMESTAMPTZ DEFAULT NOW(),
			UNIQUE(student_id, date, slot_id)
		)`,

		// Absence reports (Krankmeldungen)
		`CREATE TABLE IF NOT EXISTS absence_reports (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			student_id UUID NOT NULL REFERENCES students(id) ON DELETE CASCADE,
			start_date DATE NOT NULL,
			end_date DATE NOT NULL,
			reason TEXT,
			reason_category VARCHAR(30) NOT NULL,
			status VARCHAR(20) NOT NULL DEFAULT 'reported',
			reported_by UUID NOT NULL REFERENCES users(id),
			reported_at TIMESTAMPTZ DEFAULT NOW(),
			confirmed_by UUID REFERENCES users(id),
			confirmed_at TIMESTAMPTZ,
			medical_certificate BOOLEAN DEFAULT FALSE,
			certificate_uploaded BOOLEAN DEFAULT FALSE,
			matrix_notification_sent BOOLEAN DEFAULT FALSE,
			email_notification_sent BOOLEAN DEFAULT FALSE,
			created_at TIMESTAMPTZ DEFAULT NOW(),
			updated_at TIMESTAMPTZ DEFAULT NOW()
		)`,

		// Absence notifications to parents
		`CREATE TABLE IF NOT EXISTS absence_notifications (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			attendance_record_id UUID NOT NULL REFERENCES attendance_records(id) ON DELETE CASCADE,
			parent_id UUID NOT NULL REFERENCES parents(id) ON DELETE CASCADE,
			channel VARCHAR(20) NOT NULL,
			message_content TEXT NOT NULL,
			sent_at TIMESTAMPTZ,
			read_at TIMESTAMPTZ,
			response_received BOOLEAN DEFAULT FALSE,
			response_content TEXT,
			response_at TIMESTAMPTZ,
			created_at TIMESTAMPTZ DEFAULT NOW()
		)`,

		// =============================================
		// Notenspiegel / Grades
		// =============================================

		// Grade scales
		`CREATE TABLE IF NOT EXISTS grade_scales (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			school_id UUID NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
			name VARCHAR(50) NOT NULL,
			min_value DECIMAL(5,2) NOT NULL,
			max_value DECIMAL(5,2) NOT NULL,
			passing_value DECIMAL(5,2) NOT NULL,
			is_ascending BOOLEAN DEFAULT FALSE,
			is_default BOOLEAN DEFAULT FALSE,
			created_at TIMESTAMPTZ DEFAULT NOW()
		)`,

		// Grades
		`CREATE TABLE IF NOT EXISTS grades (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			student_id UUID NOT NULL REFERENCES students(id) ON DELETE CASCADE,
			subject_id UUID NOT NULL REFERENCES subjects(id) ON DELETE CASCADE,
			teacher_id UUID NOT NULL REFERENCES teachers(id) ON DELETE CASCADE,
			school_year_id UUID NOT NULL REFERENCES school_years(id) ON DELETE CASCADE,
			grade_scale_id UUID NOT NULL REFERENCES grade_scales(id) ON DELETE CASCADE,
			type VARCHAR(30) NOT NULL,
			value DECIMAL(5,2) NOT NULL,
			weight DECIMAL(3,2) DEFAULT 1.0,
			date DATE NOT NULL,
			title VARCHAR(100),
			description TEXT,
			is_visible BOOLEAN DEFAULT TRUE,
			semester INT NOT NULL CHECK (semester IN (1, 2)),
			created_at TIMESTAMPTZ DEFAULT NOW(),
			updated_at TIMESTAMPTZ DEFAULT NOW()
		)`,

		// Grade comments
		`CREATE TABLE IF NOT EXISTS grade_comments (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			grade_id UUID NOT NULL REFERENCES grades(id) ON DELETE CASCADE,
			teacher_id UUID NOT NULL REFERENCES teachers(id) ON DELETE CASCADE,
			comment TEXT NOT NULL,
			is_private BOOLEAN DEFAULT FALSE,
			created_at TIMESTAMPTZ DEFAULT NOW()
		)`,

		// =============================================
		// Klassenbuch / Class Diary
		// =============================================

		// Class diary entries
		`CREATE TABLE IF NOT EXISTS class_diary_entries (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			class_id UUID NOT NULL REFERENCES classes(id) ON DELETE CASCADE,
			date DATE NOT NULL,
			slot_id UUID NOT NULL REFERENCES timetable_slots(id) ON DELETE CASCADE,
			subject_id UUID NOT NULL REFERENCES subjects(id) ON DELETE CASCADE,
			teacher_id UUID NOT NULL REFERENCES teachers(id) ON DELETE CASCADE,
			topic TEXT,
			homework TEXT,
			homework_due_date DATE,
			materials TEXT,
			notes TEXT,
			is_cancelled BOOLEAN DEFAULT FALSE,
			cancellation_reason TEXT,
			created_at TIMESTAMPTZ DEFAULT NOW(),
			updated_at TIMESTAMPTZ DEFAULT NOW(),
			UNIQUE(class_id, date, slot_id)
		)`,

		// =============================================
		// Elterngespräche / Parent Meetings
		// =============================================

		// Parent meeting slots
		`CREATE TABLE IF NOT EXISTS parent_meeting_slots (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			teacher_id UUID NOT NULL REFERENCES teachers(id) ON DELETE CASCADE,
			date DATE NOT NULL,
			start_time TIME NOT NULL,
			end_time TIME NOT NULL,
			location VARCHAR(100),
			is_online BOOLEAN DEFAULT FALSE,
			meeting_link TEXT,
			is_booked BOOLEAN DEFAULT FALSE,
			created_at TIMESTAMPTZ DEFAULT NOW()
		)`,

		// Parent meetings
		`CREATE TABLE IF NOT EXISTS parent_meetings (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			slot_id UUID NOT NULL REFERENCES parent_meeting_slots(id) ON DELETE CASCADE,
			parent_id UUID NOT NULL REFERENCES parents(id) ON DELETE CASCADE,
			student_id UUID NOT NULL REFERENCES students(id) ON DELETE CASCADE,
			topic TEXT,
			notes TEXT,
			status VARCHAR(20) NOT NULL DEFAULT 'scheduled',
			cancelled_at TIMESTAMPTZ,
			cancelled_by UUID REFERENCES users(id),
			cancel_reason TEXT,
			completed_at TIMESTAMPTZ,
			created_at TIMESTAMPTZ DEFAULT NOW(),
			updated_at TIMESTAMPTZ DEFAULT NOW()
		)`,

		// =============================================
		// Matrix / Communication Integration
		// =============================================

		// Matrix rooms
		`CREATE TABLE IF NOT EXISTS matrix_rooms (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			school_id UUID NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
			matrix_room_id VARCHAR(255) NOT NULL UNIQUE,
			type VARCHAR(30) NOT NULL,
			class_id UUID REFERENCES classes(id) ON DELETE SET NULL,
			student_id UUID REFERENCES students(id) ON DELETE SET NULL,
			name VARCHAR(255) NOT NULL,
			is_encrypted BOOLEAN DEFAULT TRUE,
			created_at TIMESTAMPTZ DEFAULT NOW()
		)`,

		// Matrix room members
		`CREATE TABLE IF NOT EXISTS matrix_room_members (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			matrix_room_id UUID NOT NULL REFERENCES matrix_rooms(id) ON DELETE CASCADE,
			matrix_user_id VARCHAR(255) NOT NULL,
			user_id UUID REFERENCES users(id) ON DELETE SET NULL,
			power_level INT DEFAULT 0,
			can_write BOOLEAN DEFAULT TRUE,
			joined_at TIMESTAMPTZ DEFAULT NOW(),
			left_at TIMESTAMPTZ,
			UNIQUE(matrix_room_id, matrix_user_id)
		)`,

		// Parent onboarding tokens (QR codes)
		`CREATE TABLE IF NOT EXISTS parent_onboarding_tokens (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			school_id UUID NOT NULL REFERENCES schools(id) ON DELETE CASCADE,
			class_id UUID NOT NULL REFERENCES classes(id) ON DELETE CASCADE,
			student_id UUID NOT NULL REFERENCES students(id) ON DELETE CASCADE,
			token VARCHAR(255) NOT NULL UNIQUE,
			role VARCHAR(30) NOT NULL DEFAULT 'parent',
			expires_at TIMESTAMPTZ NOT NULL,
			used_at TIMESTAMPTZ,
			used_by_user_id UUID REFERENCES users(id),
			created_at TIMESTAMPTZ DEFAULT NOW(),
			created_by UUID NOT NULL REFERENCES users(id)
		)`,

		// =============================================
		// Phase 9 Indexes
		// =============================================
		`CREATE INDEX IF NOT EXISTS idx_schools_active ON schools(is_active)`,
		`CREATE INDEX IF NOT EXISTS idx_school_years_school ON school_years(school_id)`,
		`CREATE INDEX IF NOT EXISTS idx_school_years_current ON school_years(is_current)`,
		`CREATE INDEX IF NOT EXISTS idx_classes_school ON classes(school_id)`,
		`CREATE INDEX IF NOT EXISTS idx_classes_school_year ON classes(school_year_id)`,
		`CREATE INDEX IF NOT EXISTS idx_students_school ON students(school_id)`,
		`CREATE INDEX IF NOT EXISTS idx_students_class ON students(class_id)`,
		`CREATE INDEX IF NOT EXISTS idx_students_user ON students(user_id)`,
		`CREATE INDEX IF NOT EXISTS idx_teachers_school ON teachers(school_id)`,
		`CREATE INDEX IF NOT EXISTS idx_teachers_user ON teachers(user_id)`,
		`CREATE INDEX IF NOT EXISTS idx_class_teachers_class ON class_teachers(class_id)`,
		`CREATE INDEX IF NOT EXISTS idx_class_teachers_teacher ON class_teachers(teacher_id)`,
		`CREATE INDEX IF NOT EXISTS idx_parents_user ON parents(user_id)`,
		`CREATE INDEX IF NOT EXISTS idx_student_parents_student ON student_parents(student_id)`,
		`CREATE INDEX IF NOT EXISTS idx_student_parents_parent ON student_parents(parent_id)`,
		`CREATE INDEX IF NOT EXISTS idx_timetable_entries_class ON timetable_entries(class_id)`,
		`CREATE INDEX IF NOT EXISTS idx_timetable_entries_teacher ON timetable_entries(teacher_id)`,
		`CREATE INDEX IF NOT EXISTS idx_timetable_entries_day ON timetable_entries(day_of_week)`,
		`CREATE INDEX IF NOT EXISTS idx_timetable_substitutions_date ON timetable_substitutions(date)`,
		`CREATE INDEX IF NOT EXISTS idx_timetable_substitutions_entry ON timetable_substitutions(original_entry_id)`,
		`CREATE INDEX IF NOT EXISTS idx_attendance_records_student ON attendance_records(student_id)`,
		`CREATE INDEX IF NOT EXISTS idx_attendance_records_date ON attendance_records(date)`,
		`CREATE INDEX IF NOT EXISTS idx_absence_reports_student ON absence_reports(student_id)`,
		`CREATE INDEX IF NOT EXISTS idx_absence_reports_dates ON absence_reports(start_date, end_date)`,
		`CREATE INDEX IF NOT EXISTS idx_grades_student ON grades(student_id)`,
		`CREATE INDEX IF NOT EXISTS idx_grades_subject ON grades(subject_id)`,
		`CREATE INDEX IF NOT EXISTS idx_grades_teacher ON grades(teacher_id)`,
		`CREATE INDEX IF NOT EXISTS idx_grades_school_year ON grades(school_year_id)`,
		`CREATE INDEX IF NOT EXISTS idx_class_diary_class_date ON class_diary_entries(class_id, date)`,
		`CREATE INDEX IF NOT EXISTS idx_parent_meeting_slots_teacher ON parent_meeting_slots(teacher_id)`,
		`CREATE INDEX IF NOT EXISTS idx_parent_meeting_slots_date ON parent_meeting_slots(date)`,
		`CREATE INDEX IF NOT EXISTS idx_parent_meetings_slot ON parent_meetings(slot_id)`,
		`CREATE INDEX IF NOT EXISTS idx_parent_meetings_parent ON parent_meetings(parent_id)`,
		`CREATE INDEX IF NOT EXISTS idx_matrix_rooms_school ON matrix_rooms(school_id)`,
		`CREATE INDEX IF NOT EXISTS idx_matrix_rooms_class ON matrix_rooms(class_id)`,
		`CREATE INDEX IF NOT EXISTS idx_matrix_room_members_room ON matrix_room_members(matrix_room_id)`,
		`CREATE INDEX IF NOT EXISTS idx_parent_onboarding_tokens_token ON parent_onboarding_tokens(token)`,
		`CREATE INDEX IF NOT EXISTS idx_parent_onboarding_tokens_student ON parent_onboarding_tokens(student_id)`,

		// Insert default grade scales
		`INSERT INTO grade_scales (id, school_id, name, min_value, max_value, passing_value, is_ascending, is_default)
		SELECT gen_random_uuid(), s.id, '1-6 (Noten)', 1, 6, 4, false, true
		FROM schools s
		WHERE NOT EXISTS (SELECT 1 FROM grade_scales gs WHERE gs.school_id = s.id AND gs.name = '1-6 (Noten)')
		ON CONFLICT DO NOTHING`,

		// Insert default timetable slots for schools
		`DO $$
		DECLARE
			school_rec RECORD;
		BEGIN
			FOR school_rec IN SELECT id FROM schools LOOP
				INSERT INTO timetable_slots (school_id, slot_number, start_time, end_time, is_break, name)
				VALUES
					(school_rec.id, 1, '08:00', '08:45', false, '1. Stunde'),
					(school_rec.id, 2, '08:45', '09:30', false, '2. Stunde'),
					(school_rec.id, 3, '09:30', '09:50', true, 'Erste Pause'),
					(school_rec.id, 4, '09:50', '10:35', false, '3. Stunde'),
					(school_rec.id, 5, '10:35', '11:20', false, '4. Stunde'),
					(school_rec.id, 6, '11:20', '11:40', true, 'Zweite Pause'),
					(school_rec.id, 7, '11:40', '12:25', false, '5. Stunde'),
					(school_rec.id, 8, '12:25', '13:10', false, '6. Stunde'),
					(school_rec.id, 9, '13:10', '14:00', true, 'Mittagspause'),
					(school_rec.id, 10, '14:00', '14:45', false, '7. Stunde'),
					(school_rec.id, 11, '14:45', '15:30', false, '8. Stunde')
				ON CONFLICT (school_id, slot_number) DO NOTHING;
			END LOOP;
		END $$`,

		// =============================================
		// Phase 10: DSGVO Betroffenenanfragen (DSR)
		// Data Subject Request Management
		// =============================================

		// Sequence for request numbers
		`CREATE SEQUENCE IF NOT EXISTS dsr_request_number_seq START 1`,

		// Main table: Data Subject Requests
		`CREATE TABLE IF NOT EXISTS data_subject_requests (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			user_id UUID REFERENCES users(id) ON DELETE SET NULL,
			request_number VARCHAR(50) UNIQUE NOT NULL,
			request_type VARCHAR(30) NOT NULL,
			status VARCHAR(30) NOT NULL DEFAULT 'intake',
			priority VARCHAR(20) DEFAULT 'normal',
			source VARCHAR(30) NOT NULL DEFAULT 'api',
			requester_email VARCHAR(255) NOT NULL,
			requester_name VARCHAR(255),
			requester_phone VARCHAR(50),
			identity_verified BOOLEAN DEFAULT FALSE,
			identity_verified_at TIMESTAMPTZ,
			identity_verified_by UUID REFERENCES users(id),
			identity_verification_method VARCHAR(50),
			request_details JSONB DEFAULT '{}',
			deadline_at TIMESTAMPTZ NOT NULL,
			legal_deadline_days INT NOT NULL,
			extended_deadline_at TIMESTAMPTZ,
			extension_reason TEXT,
			assigned_to UUID REFERENCES users(id),
			processing_notes TEXT,
			completed_at TIMESTAMPTZ,
			completed_by UUID REFERENCES users(id),
			result_summary TEXT,
			result_data JSONB,
			rejected_at TIMESTAMPTZ,
			rejected_by UUID REFERENCES users(id),
			rejection_reason TEXT,
			rejection_legal_basis TEXT,
			created_at TIMESTAMPTZ DEFAULT NOW(),
			updated_at TIMESTAMPTZ DEFAULT NOW(),
			created_by UUID REFERENCES users(id)
		)`,

		// DSR Status History for audit trail
		`CREATE TABLE IF NOT EXISTS dsr_status_history (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			request_id UUID NOT NULL REFERENCES data_subject_requests(id) ON DELETE CASCADE,
			from_status VARCHAR(30),
			to_status VARCHAR(30) NOT NULL,
			changed_by UUID REFERENCES users(id),
			comment TEXT,
			metadata JSONB,
			created_at TIMESTAMPTZ DEFAULT NOW()
		)`,

		// DSR Communications log
		`CREATE TABLE IF NOT EXISTS dsr_communications (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			request_id UUID NOT NULL REFERENCES data_subject_requests(id) ON DELETE CASCADE,
			direction VARCHAR(10) NOT NULL,
			channel VARCHAR(20) NOT NULL,
			communication_type VARCHAR(50) NOT NULL,
			template_version_id UUID,
			subject VARCHAR(500),
			body_html TEXT,
			body_text TEXT,
			recipient_email VARCHAR(255),
			sent_at TIMESTAMPTZ,
			error_message TEXT,
			attachments JSONB DEFAULT '[]',
			created_at TIMESTAMPTZ DEFAULT NOW(),
			created_by UUID REFERENCES users(id)
		)`,

		// DSR Templates
		`CREATE TABLE IF NOT EXISTS dsr_templates (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			template_type VARCHAR(50) UNIQUE NOT NULL,
			name VARCHAR(255) NOT NULL,
			description TEXT,
			request_types JSONB DEFAULT '["access","rectification","erasure","restriction","portability"]',
			is_active BOOLEAN DEFAULT TRUE,
			sort_order INT DEFAULT 0,
			created_at TIMESTAMPTZ DEFAULT NOW(),
			updated_at TIMESTAMPTZ DEFAULT NOW()
		)`,

		// DSR Template Versions
		`CREATE TABLE IF NOT EXISTS dsr_template_versions (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			template_id UUID REFERENCES dsr_templates(id) ON DELETE CASCADE,
			version VARCHAR(20) NOT NULL,
			language VARCHAR(5) DEFAULT 'de',
			subject VARCHAR(500) NOT NULL,
			body_html TEXT NOT NULL,
			body_text TEXT NOT NULL,
			status VARCHAR(20) DEFAULT 'draft',
			published_at TIMESTAMPTZ,
			created_by UUID REFERENCES users(id),
			approved_by UUID REFERENCES users(id),
			approved_at TIMESTAMPTZ,
			created_at TIMESTAMPTZ DEFAULT NOW(),
			updated_at TIMESTAMPTZ DEFAULT NOW(),
			UNIQUE(template_id, version, language)
		)`,

		// DSR Exception Checks (for Art. 17(3) erasure exceptions)
		`CREATE TABLE IF NOT EXISTS dsr_exception_checks (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			request_id UUID NOT NULL REFERENCES data_subject_requests(id) ON DELETE CASCADE,
			exception_type VARCHAR(50) NOT NULL,
			description TEXT NOT NULL,
			applies BOOLEAN,
			checked_by UUID REFERENCES users(id),
			checked_at TIMESTAMPTZ,
			notes TEXT,
			created_at TIMESTAMPTZ DEFAULT NOW()
		)`,

		// Phase 10 Indexes
		`CREATE INDEX IF NOT EXISTS idx_dsr_user ON data_subject_requests(user_id)`,
		`CREATE INDEX IF NOT EXISTS idx_dsr_status ON data_subject_requests(status)`,
		`CREATE INDEX IF NOT EXISTS idx_dsr_type ON data_subject_requests(request_type)`,
		`CREATE INDEX IF NOT EXISTS idx_dsr_deadline ON data_subject_requests(deadline_at)`,
		`CREATE INDEX IF NOT EXISTS idx_dsr_assigned ON data_subject_requests(assigned_to)`,
		`CREATE INDEX IF NOT EXISTS idx_dsr_request_number ON data_subject_requests(request_number)`,
		`CREATE INDEX IF NOT EXISTS idx_dsr_created ON data_subject_requests(created_at)`,
		`CREATE INDEX IF NOT EXISTS idx_dsr_status_history_request ON dsr_status_history(request_id)`,
		`CREATE INDEX IF NOT EXISTS idx_dsr_communications_request ON dsr_communications(request_id)`,
		`CREATE INDEX IF NOT EXISTS idx_dsr_exception_checks_request ON dsr_exception_checks(request_id)`,
		`CREATE INDEX IF NOT EXISTS idx_dsr_templates_type ON dsr_templates(template_type)`,
		`CREATE INDEX IF NOT EXISTS idx_dsr_template_versions_template ON dsr_template_versions(template_id)`,
		`CREATE INDEX IF NOT EXISTS idx_dsr_template_versions_status ON dsr_template_versions(status)`,

		// Insert default DSR templates
		`INSERT INTO dsr_templates (template_type, name, description, request_types, sort_order)
		VALUES
			('dsr_receipt_access', 'Eingangsbestätigung Auskunft', 'Bestätigung des Eingangs einer Auskunftsanfrage nach Art. 15 DSGVO', '["access"]', 1),
			('dsr_receipt_rectification', 'Eingangsbestätigung Berichtigung', 'Bestätigung des Eingangs einer Berichtigungsanfrage nach Art. 16 DSGVO', '["rectification"]', 2),
			('dsr_receipt_erasure', 'Eingangsbestätigung Löschung', 'Bestätigung des Eingangs einer Löschanfrage nach Art. 17 DSGVO', '["erasure"]', 3),
			('dsr_receipt_restriction', 'Eingangsbestätigung Einschränkung', 'Bestätigung des Eingangs einer Einschränkungsanfrage nach Art. 18 DSGVO', '["restriction"]', 4),
			('dsr_receipt_portability', 'Eingangsbestätigung Datenübertragung', 'Bestätigung des Eingangs einer Datenübertragungsanfrage nach Art. 20 DSGVO', '["portability"]', 5),
			('dsr_identity_request', 'Anfrage Identitätsnachweis', 'Aufforderung zur Identitätsverifizierung', '["access","rectification","erasure","restriction","portability"]', 6),
			('dsr_processing_started', 'Bearbeitungsbestätigung', 'Bestätigung, dass die Bearbeitung begonnen hat', '["access","rectification","erasure","restriction","portability"]', 7),
			('dsr_processing_update', 'Zwischenbericht', 'Zwischenstand zur Bearbeitung', '["access","rectification","erasure","restriction","portability"]', 8),
			('dsr_clarification_request', 'Rückfragen', 'Anfrage zur Klärung des Begehrens', '["access","rectification","erasure","restriction","portability"]', 9),
			('dsr_completed_access', 'Auskunft erteilt', 'Abschließende Mitteilung mit Datenauskunft', '["access"]', 10),
			('dsr_completed_access_negative', 'Negativauskunft', 'Mitteilung dass keine Daten vorhanden sind', '["access"]', 11),
			('dsr_completed_rectification', 'Berichtigung durchgeführt', 'Bestätigung der Datenberichtigung', '["rectification"]', 12),
			('dsr_completed_erasure', 'Löschung durchgeführt', 'Bestätigung der Datenlöschung', '["erasure"]', 13),
			('dsr_completed_restriction', 'Einschränkung aktiviert', 'Bestätigung der Verarbeitungseinschränkung', '["restriction"]', 14),
			('dsr_completed_portability', 'Daten bereitgestellt', 'Mitteilung zur Datenübermittlung', '["portability"]', 15),
			('dsr_restriction_lifted', 'Einschränkung aufgehoben', 'Vorabbenachrichtigung vor Aufhebung der Einschränkung', '["restriction"]', 16),
			('dsr_rejected_identity', 'Ablehnung - Identität nicht verifizierbar', 'Ablehnung mangels Identitätsnachweis', '["access","rectification","erasure","restriction","portability"]', 17),
			('dsr_rejected_exception', 'Ablehnung - Ausnahme', 'Ablehnung aufgrund gesetzlicher Ausnahmen (z.B. Art. 17 Abs. 3)', '["erasure","restriction"]', 18),
			('dsr_rejected_unfounded', 'Ablehnung - Offensichtlich unbegründet', 'Ablehnung nach Art. 12 Abs. 5 DSGVO', '["access","rectification","erasure","restriction","portability"]', 19)
		ON CONFLICT (template_type) DO NOTHING`,

		// =============================================
		// Phase 11: EduSearch Seeds Management
		// Seed URLs for the education search crawler
		// =============================================

		// EduSearch Seed Categories
		`CREATE TABLE IF NOT EXISTS edu_search_categories (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			name VARCHAR(50) UNIQUE NOT NULL,
			display_name VARCHAR(100) NOT NULL,
			description TEXT,
			icon VARCHAR(10),
			sort_order INT DEFAULT 0,
			is_active BOOLEAN DEFAULT TRUE,
			created_at TIMESTAMPTZ DEFAULT NOW(),
			updated_at TIMESTAMPTZ DEFAULT NOW()
		)`,

		// EduSearch Seeds (crawler seed URLs)
		`CREATE TABLE IF NOT EXISTS edu_search_seeds (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			url VARCHAR(500) UNIQUE NOT NULL,
			name VARCHAR(255) NOT NULL,
			description TEXT,
			category_id UUID REFERENCES edu_search_categories(id) ON DELETE SET NULL,
			source_type VARCHAR(20) DEFAULT 'GOV',
			scope VARCHAR(20) DEFAULT 'FEDERAL',
			state VARCHAR(5),
			trust_boost DECIMAL(3,2) DEFAULT 0.50,
			enabled BOOLEAN DEFAULT TRUE,
			crawl_depth INT DEFAULT 2,
			crawl_frequency VARCHAR(20) DEFAULT 'weekly',
			last_crawled_at TIMESTAMPTZ,
			last_crawl_status VARCHAR(20),
			last_crawl_docs INT DEFAULT 0,
			total_documents INT DEFAULT 0,
			created_at TIMESTAMPTZ DEFAULT NOW(),
			updated_at TIMESTAMPTZ DEFAULT NOW(),
			created_by UUID REFERENCES users(id)
		)`,

		// EduSearch Crawl Runs (history of crawl executions)
		`CREATE TABLE IF NOT EXISTS edu_search_crawl_runs (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			seed_id UUID REFERENCES edu_search_seeds(id) ON DELETE CASCADE,
			status VARCHAR(20) DEFAULT 'running',
			started_at TIMESTAMPTZ DEFAULT NOW(),
			completed_at TIMESTAMPTZ,
			pages_crawled INT DEFAULT 0,
			documents_indexed INT DEFAULT 0,
			errors_count INT DEFAULT 0,
			error_details JSONB,
			triggered_by UUID REFERENCES users(id)
		)`,

		// EduSearch Denylist (URLs/domains to never crawl)
		`CREATE TABLE IF NOT EXISTS edu_search_denylist (
			id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
			pattern VARCHAR(500) UNIQUE NOT NULL,
			pattern_type VARCHAR(20) DEFAULT 'domain',
			reason TEXT,
			created_at TIMESTAMPTZ DEFAULT NOW(),
			created_by UUID REFERENCES users(id)
		)`,

		// Phase 11 Indexes
		`CREATE INDEX IF NOT EXISTS idx_edu_search_seeds_category ON edu_search_seeds(category_id)`,
		`CREATE INDEX IF NOT EXISTS idx_edu_search_seeds_enabled ON edu_search_seeds(enabled)`,
		`CREATE INDEX IF NOT EXISTS idx_edu_search_seeds_state ON edu_search_seeds(state)`,
		`CREATE INDEX IF NOT EXISTS idx_edu_search_seeds_scope ON edu_search_seeds(scope)`,
		`CREATE INDEX IF NOT EXISTS idx_edu_search_crawl_runs_seed ON edu_search_crawl_runs(seed_id)`,
		`CREATE INDEX IF NOT EXISTS idx_edu_search_crawl_runs_status ON edu_search_crawl_runs(status)`,

		// Insert default EduSearch categories
		`INSERT INTO edu_search_categories (name, display_name, description, icon, sort_order)
		VALUES
			('federal', 'Bundesebene', 'KMK, BMBF, Bildungsserver', '🏛️', 1),
			('states', 'Bundesländer', 'Ministerien, Landesbildungsserver', '🗺️', 2),
			('science', 'Wissenschaft', 'Bertelsmann, PISA, IGLU, TIMSS', '🔬', 3),
			('universities', 'Universitäten', 'Deutsche Hochschulen', '🎓', 4),
			('schools', 'Schulen', 'Schulwebsites', '🏫', 5),
			('portals', 'Bildungsportale', 'Lehrer-Online, 4teachers, ZUM', '📚', 6),
			('eu', 'EU/International', 'Europäische Bildungsberichte', '🇪🇺', 7),
			('authorities', 'Schulbehörden', 'Regierungspräsidien, Schulämter', '📋', 8)
		ON CONFLICT (name) DO NOTHING`,
	}

	for _, migration := range migrations {
		if _, err := db.Pool.Exec(ctx, migration); err != nil {
			return fmt.Errorf("failed to run migration: %w", err)
		}
	}

	return nil
}