SET sql_generate_invisible_primary_key=OFF;

-- Adds a notification queue (privileged admins review/edit/send instead of
-- emails going out immediately) and an admin-configurable trigger table that
-- maps appeal status transitions to notification templates, matching
-- ../queensu_artsci_acrp's notification queue architecture.
--
-- Apply to every environment's database before deploying this change.

CREATE TABLE `notification_queue` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `appeal` int(10) UNSIGNED NOT NULL,
  `appeal_level_2` int(10) UNSIGNED DEFAULT NULL,
  `notification` int(10) UNSIGNED NOT NULL,
  `to` varchar(255) NOT NULL,
  `cc` varchar(255) DEFAULT NULL,
  `bcc` varchar(255) DEFAULT NULL,
  `reply_to` varchar(255) DEFAULT NULL,
  `subject` varchar(255) NOT NULL,
  `body` mediumtext NOT NULL,
  `netid` varchar(255) NOT NULL,
  `sent_netid` varchar(255) DEFAULT NULL,
  `created` int(10) UNSIGNED NOT NULL,
  `sent` int(10) UNSIGNED DEFAULT NULL,
  `status` tinyint(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- appeal_type_ids/notification_ids store JSON arrays of ids;
-- old_status_names/new_status_names store JSON arrays of status names
-- (appeal.status/appeal_level_2.status are varchar-name-keyed, not FK-keyed).
-- An empty array means "any" for that column, matching the existing
-- checkbox-filter convention elsewhere in this app ("checking no boxes is
-- equivalent to checking all").
CREATE TABLE `appeal_status_notification_trigger` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `appeal_type_ids` text NOT NULL,
  `old_status_names` text NOT NULL,
  `new_status_names` text NOT NULL,
  `notification_ids` text NOT NULL,
  `send_immediately` tinyint(1) NOT NULL DEFAULT 0,
  `status` tinyint(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Identifies who a template is sent to (student / organization_email /
-- organization_email_level_2 / student_file_email) when a notification is
-- queued without an explicit recipient override.
ALTER TABLE `notification`
  ADD COLUMN `recipient` varchar(255) DEFAULT NULL AFTER `body`;

INSERT INTO `privilege` (`name`) VALUES ('process notifications');
