-- Migration 020 — Registro de todos los emails enviados por Vale.
-- Permite auditar entregas, reenvíos y estadísticas desde el admin.

CREATE TABLE IF NOT EXISTS email_logs (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id    BIGINT UNSIGNED NULL,
    to_email     VARCHAR(255)    NOT NULL,
    subject      VARCHAR(255)    NOT NULL,
    type         VARCHAR(64)     NOT NULL DEFAULT 'generic',
    status       ENUM('sent','failed') NOT NULL DEFAULT 'sent',
    resend_id    VARCHAR(128)    NULL,
    error        TEXT            NULL,
    created_at   TIMESTAMP       DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_email_logs_tenant  (tenant_id),
    INDEX idx_email_logs_type    (type, status),
    INDEX idx_email_logs_date    (created_at),
    CONSTRAINT fk_email_logs_tenant
        FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
