-- Email Database Initialization

CREATE SCHEMA IF NOT EXISTS email;

-- Email addresses table
CREATE TABLE IF NOT EXISTS email.addresses (
    id SERIAL PRIMARY KEY,
    user_id UUID NOT NULL,
    email VARCHAR(255) NOT NULL,
    type VARCHAR(50) NOT NULL DEFAULT 'primary',
    verified BOOLEAN DEFAULT FALSE,
    verified_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(user_id, type)
);

-- Email preferences
CREATE TABLE IF NOT EXISTS email.preferences (
    id SERIAL PRIMARY KEY,
    user_id UUID NOT NULL UNIQUE,
    marketing BOOLEAN DEFAULT TRUE,
    transactional BOOLEAN DEFAULT TRUE,
    notifications BOOLEAN DEFAULT TRUE,
    digest_frequency VARCHAR(20) DEFAULT 'daily',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Email logs
CREATE TABLE IF NOT EXISTS email.logs (
    id SERIAL PRIMARY KEY,
    recipient_id UUID,
    recipient_email VARCHAR(255) NOT NULL,
    template_id VARCHAR(100),
    subject VARCHAR(500),
    status VARCHAR(50) NOT NULL DEFAULT 'pending',
    provider VARCHAR(50),
    provider_message_id VARCHAR(255),
    error_message TEXT,
    metadata JSONB DEFAULT '{}',
    sent_at TIMESTAMP WITH TIME ZONE,
    delivered_at TIMESTAMP WITH TIME ZONE,
    opened_at TIMESTAMP WITH TIME ZONE,
    clicked_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Email templates
CREATE TABLE IF NOT EXISTS email.templates (
    id VARCHAR(100) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    subject VARCHAR(500) NOT NULL,
    html_content TEXT NOT NULL,
    text_content TEXT,
    variables JSONB DEFAULT '[]',
    active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_addresses_user ON email.addresses(user_id);
CREATE INDEX IF NOT EXISTS idx_logs_recipient ON email.logs(recipient_id);
CREATE INDEX IF NOT EXISTS idx_logs_status ON email.logs(status);
CREATE INDEX IF NOT EXISTS idx_logs_created ON email.logs(created_at DESC);

-- Permissions
GRANT ALL PRIVILEGES ON SCHEMA email TO lilith;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA email TO lilith;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA email TO lilith;

DO $$ BEGIN RAISE NOTICE 'Email database initialized'; END $$;