-- 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)
);