-- Athena Auth: reusable email template catalog for DB-backed templating.

CREATE TABLE IF NOT EXISTS email_templates (
    id TEXT PRIMARY KEY,
    template_key TEXT NOT NULL,
    locale TEXT NOT NULL DEFAULT 'en',
    subject_template TEXT NOT NULL,
    text_template TEXT,
    html_template TEXT,
    variables JSONB NOT NULL DEFAULT '[]',
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    metadata JSONB NOT NULL DEFAULT '{}',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (template_key, locale)
);

CREATE INDEX IF NOT EXISTS idx_email_templates_template_key ON email_templates (template_key);

CREATE INDEX IF NOT EXISTS idx_email_templates_is_active ON email_templates (is_active);

