-- Mass email notifications — modern replacement for the legacy
-- `utility_dbo.email_alert_log` + `utility_dbo.send_account_status_history`
-- tables, fed by the four PHP scripts under
-- `email.notifications/send*.php`. See
-- docs/MASS_EMAIL_NOTIFICATIONS.md for the design.
--
-- The two legacy tables stay on `utility_dbo` untouched: their `NUMid`
-- column references a string identifier that no longer exists in the
-- unified schema, so a backfill would be destructive, not additive. New
-- notifications log here against the unified `customers.id` (uuid) and
-- the legacy rows are eventually retired by `utility_dbo` itself once
-- the office flips to this codebase as the source of truth.

-- CreateTable
-- ENUM values are declared inline per column (MySQL has no CREATE TYPE)
-- and match the Prisma enums `EmailNotificationType`,
-- `EmailNotificationServicio`, `EmailNotificationStatus`.
CREATE TABLE `email_notification_log` (
    `id` VARCHAR(191) NOT NULL,
    `sendDate` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `notificationType` ENUM('OUTSTANDING_PAYMENT', 'PAYMENT_CONFIRMATION', 'ACCOUNT_STATUS', 'TRUST_PAYMENT_CONFIRMATION') NOT NULL,
    `level` INTEGER NULL,
    `servicio` ENUM('CUSTOMERS', 'TRUST') NOT NULL,
    `customerId` VARCHAR(191) NULL,
    `customerName` VARCHAR(191) NOT NULL,
    `customerEmail` VARCHAR(191) NOT NULL,
    `subject` VARCHAR(191) NOT NULL,
    `bodyRequestUrl` TEXT NULL,
    `bodySnapshot` TEXT NOT NULL,
    `debug` BOOLEAN NOT NULL DEFAULT false,
    `providerMessageId` VARCHAR(191) NULL,
    `providerResponse` VARCHAR(191) NULL,
    `status` ENUM('SENT', 'FAILED', 'SKIPPED_NO_EMAIL', 'SKIPPED_GATE') NOT NULL,
    `error` TEXT NULL,

    INDEX `email_notification_log_sendDate_idx`(`sendDate`),
    INDEX `email_notification_log_notificationType_sendDate_idx`(`notificationType`, `sendDate`),
    INDEX `email_notification_log_customerId_sendDate_idx`(`customerId`, `sendDate`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `account_status_history` (
    `id` VARCHAR(191) NOT NULL,
    `sendDate` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `customerId` VARCHAR(191) NOT NULL,
    `customerName` VARCHAR(191) NOT NULL,
    `customerEmail` VARCHAR(191) NOT NULL,
    `tipo` VARCHAR(191) NOT NULL,
    `tCambio` DECIMAL(10, 4) NULL,
    `balance` DECIMAL(12, 2) NOT NULL,
    `solicitado` DECIMAL(12, 2) NOT NULL,
    `level` INTEGER NOT NULL,

    INDEX `account_status_history_sendDate_idx`(`sendDate`),
    INDEX `account_status_history_customerId_sendDate_idx`(`customerId`, `sendDate`),
    INDEX `account_status_history_level_sendDate_idx`(`level`, `sendDate`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- AddForeignKey
ALTER TABLE `email_notification_log` ADD CONSTRAINT `email_notification_log_customerId_fkey` FOREIGN KEY (`customerId`) REFERENCES `customers`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `account_status_history` ADD CONSTRAINT `account_status_history_customerId_fkey` FOREIGN KEY (`customerId`) REFERENCES `customers`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
