28 lines
1.7 KiB
SQL
28 lines
1.7 KiB
SQL
-- 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`;
|