-- =============================================================================
-- MIGRATION: Email send log (one row per send attempt — sent/failed status)
-- Convention: Prisma maps createdAt→createdOn in DB
-- Run: idempotent — safe to re-execute
-- =============================================================================

BEGIN;

CREATE TABLE IF NOT EXISTS platform.email_logs (
    id             VARCHAR(255) NOT NULL DEFAULT gen_random_uuid()::text,
    "storeId"      VARCHAR(255) NOT NULL DEFAULT 'store_demo',
    "typeCode"     VARCHAR(255),
    recipient      VARCHAR(255) NOT NULL,
    subject        VARCHAR(500) NOT NULL,
    status         VARCHAR(20)  NOT NULL,
    "errorMessage" TEXT,
    "messageId"    VARCHAR(255),
    "createdOn"    TIMESTAMPTZ  NOT NULL DEFAULT NOW(),

    CONSTRAINT email_logs_pkey PRIMARY KEY (id)
);

CREATE INDEX IF NOT EXISTS email_logs_store_id_idx ON platform.email_logs ("storeId");
CREATE INDEX IF NOT EXISTS email_logs_type_code_idx ON platform.email_logs ("typeCode");
CREATE INDEX IF NOT EXISTS email_logs_created_on_idx ON platform.email_logs ("createdOn");

COMMIT;