-- Paid Claim Notification Email

SET @email_media := (SELECT id
                     FROM ohrm_notification_media
                     WHERE `name` = 'Email');

SET @action_notification_type := (SELECT id
                                  FROM ohrm_notification_type
                                  WHERE `name` = 'Action Based');

SET @module_id := (SELECT id FROM ohrm_module where `name` = 'expense');

SET @claim_request_workflow_id := 'CLAIM_REQUEST';

INSERT INTO `ohrm_notification`
(`name`, `is_enable`, `type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`, `hidden`) VALUES
    ('Paid Claim Notification', 1, @action_notification_type, 'ExpenseEmailProcessorWrapperEventNotification',
     '{"email_processor":"ClaimRequestEmailProcessor"}', @claim_request_workflow_id, 'Paid', @module_id,'0');
SET @notification_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_supported_media` (`notification_id`, `media_id`) VALUES
    (@notification_id, @email_media);

-- Template for affected employee

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
    (@notification_id, @email_media, 'en_US',
     "Paid Claim Notification sent to Employee",
     "Expense Paid Notification - Your Expense Claim Has Been Marked Paid by %performerFullName%",
     "Hi %recipientFirstName%,<br /><br />Your expense claim has been marked as Paid by %performerFullName%.<br /><br />You can click %loginUrl% to directly access the expense claim.<br /><br />Thank you.<br /><br />You are getting this email because you are a subscriber.<br /><br />This is an auto generated email.");

SET @aff_emp_template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id) SELECT
                                        @notification_id,
                                        id FROM ohrm_workflow_state_machine
WHERE `workflow` = @claim_request_workflow_id AND `action` = 'Paid' AND  `role` = 'Admin';
SET @last_subscription_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber` (media_id, parameters, type, name) VALUES
    (@email_media, '{"prevent_delete":true,"restrict_employees":true}', 'Custom', 'Affected Employee');

SET @affected_employee_subscriber := LAST_INSERT_ID();


INSERT INTO `ohrm_notification_subscriber_subscription` (subscription_id, subscriber_id, template_id) SELECT
                                                                                                          s.id,
                                                                                                          @affected_employee_subscriber,
                                                                                                          @aff_emp_template_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;

-- Template for Other Subscribers

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
    (@notification_id, @email_media, 'en_US',
     "Paid Claim Notification sent to Subscriber",
     "Expense Claim Notification - %employeeName%'s Expense Claim Has Been Marked Paid by %performerFullName%",
     "Hi %recipientFirstName%,<br /><br />%employeeName%'s Expense Claim Has Been Marked Paid by %performerFullName%.<br /><br />You can click %loginUrl% to directly access the expense claim.<br /><br />Thank you.<br /><br />You are getting this email because you are a subscriber.<br /><br />This is an auto generated email.");




