43 lines
1.3 KiB
SQL
43 lines
1.3 KiB
SQL
-- Retail Policy Reminder: schema + seed
|
|
-- Run manually against the application database.
|
|
|
|
-- 1) policy_transaction.renewal_status
|
|
-- Skip this ALTER if the column already exists.
|
|
ALTER TABLE `policy_transaction`
|
|
ADD COLUMN `renewal_status` VARCHAR(50) NULL DEFAULT NULL
|
|
AFTER `renewal_date`;
|
|
|
|
-- Required for multi-offset reminders (30, 15, 7, ...). Skip if the column already exists.
|
|
ALTER TABLE `policy_transaction`
|
|
ADD COLUMN `renewal_last_reminder_offset` INT NULL DEFAULT NULL
|
|
AFTER `renewal_status`;
|
|
|
|
-- 2) notifications.config_json
|
|
ALTER TABLE `notifications`
|
|
ADD COLUMN `config_json` LONGTEXT NULL
|
|
AFTER `common_mail`;
|
|
|
|
-- 3) Seed global retail reminder notification (skip if row already exists)
|
|
INSERT INTO `notifications` (
|
|
`client_id`,
|
|
`template_name`,
|
|
`subject`,
|
|
`mail_content`,
|
|
`enabled`,
|
|
`config_json`
|
|
)
|
|
SELECT
|
|
NULL,
|
|
'retail_reminder_mail',
|
|
'Policy Renewal Reminder',
|
|
'',
|
|
1,
|
|
'{"enabled":true,"daily":true,"days":"mon,tue,wed,thu,fri","clientTypes":[2],"dateField":"renewal_date","daysBefore":"30, 15, 7, 1, -2, -5, -10","includeClientEmail":true,"includeOverdue":false,"toEmails":"","ccEmails":"","bccEmails":"","fromMail":""}'
|
|
FROM DUAL
|
|
WHERE NOT EXISTS (
|
|
SELECT 1
|
|
FROM `notifications`
|
|
WHERE `template_name` = 'retail_reminder_mail'
|
|
AND (`client_id` IS NULL OR `client_id` = 0)
|
|
);
|