-- Fold insurance renewal avisos into the one notification log.
--
-- Until now `renewal_notices` was the only record a renewal send left
-- behind, and its sole job is gating: a row with `sentAt` removes the
-- policy from the pending list. It cannot represent a failed send, a
-- customer with no email, or a second attempt — so the Pólizas tab of
-- /notificaciones had no "Registro de envíos" to show.
--
-- Extending the two enums lets `RenewalsService` write the same
-- `email_notification_log` rows the four bulk jobs write. `renewal_notices`
-- keeps its gating role unchanged.

-- AlterEnum: EmailNotificationType += RENEWAL_NOTICE
ALTER TABLE `email_notification_log`
    MODIFY `notificationType` ENUM('OUTSTANDING_PAYMENT', 'PAYMENT_CONFIRMATION', 'ACCOUNT_STATUS', 'TRUST_PAYMENT_CONFIRMATION', 'RENEWAL_NOTICE') NOT NULL;

-- AlterEnum: EmailNotificationServicio += POLICIES
ALTER TABLE `email_notification_log`
    MODIFY `servicio` ENUM('CUSTOMERS', 'TRUST', 'POLICIES') NOT NULL;

-- Backfill: every renewal notice this platform actually emailed.
--
-- Scope is deliberately `channel = 'EMAIL' AND sentAt IS NOT NULL`. MAIL-
-- channel rows are printed letters carried over from the legacy book — they
-- were never emails, and inventing log rows for them would misreport the
-- send history. Emailed rows carry a real recipient (resolved through the
-- policy's customer) and a real timestamp; the only fields we cannot
-- recover are the rendered body and the exact subject, so `bodySnapshot`
-- stays empty and the subject is reconstructed from the same two templates
-- `renderRenewalEmail()` uses (generation 3 = expired wording).
--
-- Rows whose customer has no email are skipped: `customerEmail` is NOT NULL
-- and a blank recipient would be a lie. Their `renewal_notices` row still
-- gates the pending list exactly as before.
INSERT INTO `email_notification_log` (
    `id`, `sendDate`, `notificationType`, `level`, `servicio`,
    `customerId`, `customerName`, `customerEmail`, `subject`,
    `bodyRequestUrl`, `bodySnapshot`, `debug`,
    `providerMessageId`, `providerResponse`, `status`, `error`
)
SELECT
    UUID(),
    rn.`sentAt`,
    'RENEWAL_NOTICE',
    rn.`generation`,
    'POLICIES',
    c.`id`,
    c.`name`,
    c.`email`,
    CASE WHEN rn.`generation` = 3
         THEN CONCAT('Póliza vencida: ', p.`policyNumber`)
         ELSE CONCAT('Aviso de renovación: póliza ', p.`policyNumber`)
    END,
    NULL,
    '',
    FALSE,
    rn.`providerMessageId`,
    'backfill:20260802120000',
    'SENT',
    NULL
FROM `renewal_notices` rn
JOIN `policies` p ON p.`id` = rn.`policyId`
JOIN `customers` c ON c.`id` = p.`customerId`
WHERE rn.`channel` = 'EMAIL'
  AND rn.`sentAt` IS NOT NULL
  AND c.`email` IS NOT NULL
  AND c.`email` <> '';
