appserver/database/init/04-smtp-settings.sql

-- SMTP Configuration Table
CREATE TABLE IF NOT EXISTS __sys_smtp_settings (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    host VARCHAR(255) NOT NULL,
    port INTEGER NOT NULL DEFAULT 587,
    secure BOOLEAN DEFAULT false,
    username VARCHAR(255),
    password VARCHAR(255),
    from_email VARCHAR(255) NOT NULL,
    from_name VARCHAR(255) DEFAULT 'SaaS Platform',
    enabled BOOLEAN DEFAULT true,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Create index on enabled field
CREATE INDEX IF NOT EXISTS idx_smtp_settings_enabled ON __sys_smtp_settings(enabled);

-- Insert default SMTP settings (disabled by default)
INSERT INTO __sys_smtp_settings (
    host,
    port,
    secure,
    from_email,
    from_name,
    enabled
) VALUES (
    'smtp.example.com',
    587,
    false,
    'noreply@example.com',
    'SaaS Platform',
    false
) ON CONFLICT DO NOTHING;

-- Email Queue Table (for asynchronous email sending)
CREATE TABLE IF NOT EXISTS __sys_email_queue (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    to_email VARCHAR(255) NOT NULL,
    subject VARCHAR(500) NOT NULL,
    body TEXT NOT NULL,
    html_body TEXT,
    status VARCHAR(50) DEFAULT 'pending',
    attempts INTEGER DEFAULT 0,
    max_attempts INTEGER DEFAULT 3,
    error_message TEXT,
    scheduled_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    sent_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Create indexes on email queue
CREATE INDEX IF NOT EXISTS idx_email_queue_status ON __sys_email_queue(status);
CREATE INDEX IF NOT EXISTS idx_email_queue_scheduled ON __sys_email_queue(scheduled_at);
CREATE INDEX IF NOT EXISTS idx_email_queue_to_email ON __sys_email_queue(to_email);

-- Trigger to update updated_at timestamp
CREATE OR REPLACE FUNCTION update_smtp_settings_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER smtp_settings_updated_at_trigger
    BEFORE UPDATE ON __sys_smtp_settings
    FOR EACH ROW
    EXECUTE FUNCTION update_smtp_settings_updated_at();

CREATE TRIGGER email_queue_updated_at_trigger
    BEFORE UPDATE ON __sys_email_queue
    FOR EACH ROW
    EXECUTE FUNCTION update_smtp_settings_updated_at();