-- Reminder mail configuration table (fresh install + upgrade + legacy data migration)

CREATE TABLE IF NOT EXISTS `reminder_mail_config` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `client_policy_id` INT UNSIGNED NOT NULL,
    `frequency` ENUM('daily', 'weekly', 'monthly', 'custom', 'working_days') NOT NULL DEFAULT 'custom',    
    `reminder_days` VARCHAR(255) DEFAULT NULL COMMENT 'weekly: 0-6 (Sun-Sat); working_days: 1-7 (Mon-Sun) or Mon,Tue,...; monthly/custom: 1-31; daily: NULL',
    `email_subject` VARCHAR(500) DEFAULT NULL COMMENT 'Custom reminder mail subject; falls back to notification template when NULL',
    `email_body` TEXT DEFAULT NULL COMMENT 'Custom reminder mail body; falls back to notification template when NULL',
    `is_enabled` TINYINT(1) NOT NULL DEFAULT 1,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_by` INT UNSIGNED DEFAULT NULL,
    `updated_by` INT UNSIGNED DEFAULT NULL,
    `created_at` DATETIME DEFAULT NULL,
    `updated_at` DATETIME DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_reminder_mail_config_client_policy_id` (`client_policy_id`),
    KEY `idx_reminder_mail_config_frequency` (`frequency`),
    KEY `idx_reminder_mail_config_is_enabled` (`is_enabled`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Upgrade existing installations (ignore duplicate-column errors if already applied)
ALTER TABLE `reminder_mail_config`
    ADD COLUMN `email_subject` VARCHAR(500) DEFAULT NULL COMMENT 'Custom reminder mail subject; falls back to notification template when NULL' AFTER `reminder_days`;

ALTER TABLE `reminder_mail_config`
    ADD COLUMN `email_body` TEXT DEFAULT NULL COMMENT 'Custom reminder mail body; falls back to notification template when NULL' AFTER `email_subject`;