-- Privacy Gateway Datenbank-Schema

-- Sessions/Tabs für Multi-Chat
CREATE TABLE IF NOT EXISTS sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    model VARCHAR(100) DEFAULT 'llama3.2:latest',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    metadata JSONB DEFAULT '{}'
);

-- Nachrichten pro Session
CREATE TABLE IF NOT EXISTS messages (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    session_id UUID REFERENCES sessions(id) ON DELETE CASCADE,
    role VARCHAR(20) NOT NULL CHECK (role IN ('user', 'assistant', 'system')),
    original_content TEXT NOT NULL,
    anonymized_content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- PII-Mappings für Re-Identifizierung
CREATE TABLE IF NOT EXISTS pii_mappings (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    session_id UUID REFERENCES sessions(id) ON DELETE CASCADE,
    message_id UUID REFERENCES messages(id) ON DELETE CASCADE,
    pii_type VARCHAR(50) NOT NULL,
    original_value TEXT NOT NULL,
    anonymized_value TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- PII-Typen-Enum
CREATE TYPE pii_type AS ENUM (
    'name_person',
    'name_company',
    'address',
    'email',
    'phone',
    'birthdate',
    'account_number',
    'iban',
    'employee_id',
    'credit_card',
    'tax_id',
    'custom'
);

-- Indexe für Performance
CREATE INDEX IF NOT EXISTS idx_messages_session ON messages(session_id);
CREATE INDEX IF NOT EXISTS idx_messages_created ON messages(created_at);
CREATE INDEX IF NOT EXISTS idx_pii_session ON pii_mappings(session_id);
CREATE INDEX IF NOT EXISTS idx_pii_message ON pii_mappings(message_id);
CREATE INDEX IF NOT EXISTS idx_pii_anonymized ON pii_mappings(anonymized_value);

-- Trigger für updated_at
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_sessions_updated_at
    BEFORE UPDATE ON sessions
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();