-- Athena Auth: persistent email send failure log

CREATE TABLE IF NOT EXISTS email_send_failures (
    id TEXT PRIMARY KEY,
    user_id TEXT REFERENCES users (id) ON DELETE SET NULL,
    recipient_email TEXT NOT NULL,
    flow TEXT NOT NULL,
    provider TEXT,
    error_message TEXT NOT NULL,
    metadata JSONB NOT NULL DEFAULT '{}',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_email_send_failures_created_at
    ON email_send_failures (created_at DESC);

CREATE INDEX IF NOT EXISTS idx_email_send_failures_recipient_email
    ON email_send_failures (recipient_email);
