-- orangehrmLeavePlugin/install/dbscript.sql
SET @action_notification_type := (SELECT id FROM ohrm_notification_type where `name`='Action Based');
SET @leave_workflow_id := (SELECT id FROM ohrm_workflow where `name`='leave');
SET @admin_user_role := (SELECT id FROM ohrm_user_role WHERE `name` = 'Admin');
SET @supervisor_user_role := (SELECT id FROM ohrm_user_role WHERE `name` = 'Supervisor');
SET @hod_user_role_id := (SELECT `id` FROM `ohrm_user_role` WHERE `name` = 'HeadOfDepartment');
SET @ess_user_role := (SELECT id FROM ohrm_user_role WHERE `name` = 'ESS');
SET @email_media := (SELECT id FROM ohrm_notification_media WHERE `name` = 'Email');
SET @leave_module := (SELECT `id` FROM `ohrm_module` WHERE  `name` ='leave');

-- Leave Workflow
-- TODO: supported_media, subscription_template

-- APPLY - default sent to supervisor
INSERT INTO `ohrm_notification` 
    (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`) VALUES
    ('Leave Applications', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification', 
    '{"email_processor":"LeaveEmailProcessor","extra_roles":["HeadOfDepartment"], "notifyAffectedUserOptionVisible": true}', @leave_workflow_id, 'APPLY',@leave_module);
SET @notification_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
'Employee leave application notification sent to Supervisor',
'Leave Notification - %performerFullName% Applied for %leaveDuration% %leaveDurationUnit% of %leaveType% Leave',
'<p>%performerFullName% has applied for leave. The leave details:<br /><br />%leaveDetails%<br />%additionalLeaveDetails%<br />%nomineeDetails%<br /><br />You can click %loginUrl% to directly access the leave.<br /><br />Thank you.<br /><br />This is an automated notification.</p>');

SET @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` = @leave_workflow_id AND `action` = 'APPLY';
SET @last_subscription_id := LAST_INSERT_ID();

-- Subscriber for Default Supervisor
INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, type, user_role_id) VALUES
    (@email_media, NULL, 'UserRole', @supervisor_user_role);

SET @subscriber_id := LAST_INSERT_ID();

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

-- Subscriber for HoD
INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, TYPE, user_role_id) VALUES
    (@email_media, '{"extra":"notification"}', 'UserRole', @hod_user_role_id);

SET @subscriber_id := LAST_INSERT_ID();

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

-- ASSIGN - sent to ess, supervisor
INSERT INTO `ohrm_notification`
    (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`) VALUES
    ('Leave Assignments', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
    '{"email_processor":"LeaveEmailProcessor","extra_roles":["HeadOfDepartment"], "notifyAffectedUserOptionVisible": true}', @leave_workflow_id, 'ASSIGN',@leave_module);
SET @notification_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
'Admin leave assignment notification sent to Supervisor',
'Leave Notification - %performerFullName% Has Assigned Leave to %assigneeFullName%',
'<p>%performerFullName% has assigned leave to %assigneeFullName%. The leave details:<br /><br />%leaveDetails%<br />%additionalLeaveDetails%<br />%nomineeDetails%<br /><br />Thank you.<br /><br />This is an automated notification.</p>');

SET @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` = @leave_workflow_id AND `action` = 'ASSIGN';
SET @last_subscription_id := LAST_INSERT_ID();

-- Subscriber for Default Supervisor
INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, type, user_role_id) VALUES
    (@email_media, NULL, 'UserRole', @supervisor_user_role);

SET @subscriber_id := LAST_INSERT_ID();

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

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

SET @subscriber_id := LAST_INSERT_ID();

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

-- Subscriber for Hod
INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, TYPE, user_role_id) VALUES
    (@email_media, '{"extra":"notification"}', 'UserRole', @hod_user_role_id);

SET @subscriber_id := LAST_INSERT_ID();

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

-- APPROVE - sent to ess
INSERT INTO `ohrm_notification`
    (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`) VALUES
    ('Leave Approvals', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
    '{"email_processor":"LeaveEmailProcessor","extra_roles":["HeadOfDepartment"], "notifyAffectedUserOptionVisible": true}', @leave_workflow_id, 'APPROVE',@leave_module);
SET @notification_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
'Admin leave approval notification sent to Employee',
'Leave Notification - %performerFullName% Has Approved Leave Request of %applicantFullName%',
'<p>%performerFullName% has approved leave request of %applicantFullName%. The leave details:<br /><br />%leaveDetails%<br /><br />Thank you.<br /><br />This is an automated notification.</p>');

SET @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` = @leave_workflow_id AND `action` = 'APPROVE';
SET @last_subscription_id := LAST_INSERT_ID();

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

SET @approve_subscriber_id := LAST_INSERT_ID();

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


-- CANCEL - if done by ess: sent to supervisor. If done by someone else. sent to ess
INSERT INTO `ohrm_notification`
    (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`) VALUES
    ('Leave Cancellations', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
     '{"email_processor":"LeaveEmailProcessor","extra_roles":["HeadOfDepartment"], "notifyAffectedUserOptionVisible": true}', @leave_workflow_id, 'CANCEL',@leave_module);
SET @notification_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
'Employee leave cancellation notification sent to Supervisor',
'Leave Notification - %performerFullName% Has Canceled Leave Request of %applicantFullName%',
'<p>%performerFullName% has cancelled leave request of %applicantFullName%. The leave details:<br /><br />%leaveDetails%<br />%additionalLeaveCancelDetails%<br /><br />Thank you.<br /><br />This is an automated notification.</p>');

SET @supervisor_template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
'Leave cancellation notification sent to ESS',
'Leave Notification - %performerFullName% Has Canceled Your Leave',
'<p>%performerFullName% has cancelled your leave. The leave details:<br /><br />%leaveDetails%<br />%additionalLeaveCancelDetails%<br /><br />Thank you.<br /><br />This is an automated notification.</p>');

SET @ess_template_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, type, user_role_id) VALUES
    (@email_media, NULL, 'UserRole', @supervisor_user_role);

SET @supervisor_subscriber_id := LAST_INSERT_ID();

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

SET @ess_subscriber_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, TYPE, user_role_id) VALUES
    (@email_media, '{"extra":"notification"}', 'UserRole', @hod_user_role_id);

SET @hod_subscriber_id := LAST_INSERT_ID();

-- Performed by ESS
INSERT INTO `ohrm_notification_subscription`
    (notification_id, workflow_item_id) SELECT
    @notification_id, id FROM ohrm_workflow_state_machine WHERE `workflow` = @leave_workflow_id AND `action` = 'CANCEL' AND `role` = 'ESS';
SET @last_subscription_id := LAST_INSERT_ID();

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

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

-- Not Performed by ESS
INSERT INTO `ohrm_notification_subscription`
    (notification_id, workflow_item_id) SELECT
    @notification_id, id FROM ohrm_workflow_state_machine WHERE `workflow` = @leave_workflow_id
    AND `action` = 'CANCEL' AND `role` <>'ESS';
SET @last_subscription_id := LAST_INSERT_ID();

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

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

-- REJECT - sent to ess
INSERT INTO `ohrm_notification`
    (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`) VALUES
    ('Leave Rejections', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
    '{"email_processor":"LeaveEmailProcessor","extra_roles":["HeadOfDepartment"], "notifyAffectedUserOptionVisible": true}', @leave_workflow_id, 'REJECT',@leave_module);
SET @notification_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
'Admin leave rejection notification send to Employee',
'Leave Notification - %performerFullName% Has Rejected Leave Request of %applicantFullName%',
'<p>%performerFullName% has rejected leave request of %applicantFullName%. The leave details:<br /><br />%leaveDetails%<br /><br />Thank you.<br /><br />This is an automated notification.</p>');

SET @template_id := LAST_INSERT_ID();

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

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


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

SET @subscriber_id := LAST_INSERT_ID();

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

-- Change Email: Multiple actions, no subscriptions. Hidden 

INSERT INTO `ohrm_notification` 
    (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `hidden`,`module_id`) VALUES
    ('Leave Change Email', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification', 
    '{"email_processor":"LeaveChangeMailProcessor"}', @leave_workflow_id, NULL, 1,@leave_module);
SET @notification_id := LAST_INSERT_ID();

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

UPDATE `ohrm_workflow` SET `group_name`='leave',`group_label`='Leave' WHERE `name` = 'leave';
INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
'Leave Change Email',
'Leave Notification - %performerFullName% Has Processed Leave Request of %applicantFullName%',
'<p>%performerFullName% has done the following changes to leave request of %applicantFullName%. The leave details:<br /><br />%leaveDetails%<br />%additionalLeaveCancelDetails%<br />You were sent this mail, as you have subscribed to OrangeHRM leave changes.<br /><br />Thank you.<br /><br />This is an automated notification.</p>');

-- Leave Approval Reminder Notification
SET @date_based_notification_type := (SELECT id FROM ohrm_notification_type where `name`='Date Based');

SET @module_id := (SELECT `id` FROM ohrm_module WHERE `name` = 'leave');

INSERT INTO `ohrm_notification`
    (`name`, `is_enable`,`type_id`, `generator_class`, `workflow_id`, `workflow_action`, `module_id`) VALUES
    ('Leave Approval Reminder', 0, @date_based_notification_type, 'LeaveApprovalReminderNotification', NULL, NULL, @module_id);
SET @notification_id := (SELECT `id` FROM `ohrm_notification` WHERE `name` LIKE 'Leave Approval Reminder');

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

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
'Leave Approval Reminder',
'Leave Approval Reminder - %LeaveRequestOwnerFullName% %leaveDuration% %leaveDurationUnit% of %LeaveType% Leave',
'<p>Hi %RecipientName%,<br /><br />This is to remind you that following leave request by %LeaveRequestOwnerFullName% is pending for approval.<br /><br />The leave details:<br />%LeaveDetails%<br />You can use this <a href="%URLToLeaveRequest%">link</a> to directly access the leave.<br /><br />Thank you.<br /><br />This is an automated notification.</p>');

SET @template_id := (SELECT `id` FROM `ohrm_notification_subscriber_subscription_template` WHERE `name` LIKE 'Leave Approval Reminder');

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

INSERT INTO `ohrm_notification_subscription`
    (notification_id, notify_before_value, notify_before_unit) VALUES
    (@notification_id, 2, 'DAY');

SET @last_subscription_id := (SELECT `id` FROM `ohrm_notification_subscription` WHERE `notification_id` = @notification_id);

SET @supervisor_user_role := (SELECT id FROM ohrm_user_role WHERE `name` = 'Supervisor');

INSERT INTO `ohrm_notification_subscriber`(`media_id`, `parameters`, `type`, `user_role_id`, `name`) VALUES
    (@email_media, NULL, 'UserRole', @supervisor_user_role, NULL);

SET @subscriber_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription`(subscription_id, subscriber_id, template_id) VALUES
(@last_subscription_id, @subscriber_id, @template_id);


-- *******    Leave Attachment Submission Reminder   ***********************
SET @schedule_based_notification_type := (SELECT id FROM ohrm_notification_type where `name`='Scheduled');
SET @leave_module := (SELECT `id` FROM ohrm_module WHERE `name` = 'leave');
SET @email_media := (SELECT id FROM ohrm_notification_media WHERE `name` = 'Email');


INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`) VALUES
  ('Leave Attachment Submission Reminder', 0, @schedule_based_notification_type, 'LeaveRequestAttachmentReminderEventNotification', '{"extra_roles":["HeadOfDepartment"]}', NULL, NULL, @leave_module);
SET @notification_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
    (@notification_id, @email_media, 'en_US',
     'Leave attachment submission reminder sent to Subscriber',
     'Reminder: Please attach the required documents for your leave request(s)',
     '<p>Hi %recipientFirstName%,</p>
     </br>
     <p>This is to remind you that the following leave requests are still awaiting attachments submission:</p>

     %pastLeaveRequestsPendingAttachments%
     </br>
     <p>Please log into the system and submit the necessary attachments as soon as possible.</p>
     <p>Thank you for your cooperation.</p>
     <p>You are receiving this email because you were added as a subscriber to this email.</p>
     <p>This is an auto-generated email.</p>');

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
    (@notification_id, @email_media, 'en_US',
     'Leave attachment submission reminder sent to Affected Employee',
     'Reminder: Please attach the required documents for your leave request(s)',
     '<p>Hi %recipientFirstName%,</p>
     </br>
     <p>This is to remind you that the following leave requests are still awaiting your attachments submission:</p>

     %pastLeaveRequestsPendingAttachments%
     </br>
     <p>Please log into the system and submit the necessary attachments as soon as possible.</p>
     <p>Thank you for your cooperation.</p>
     <p>This is an auto-generated email.</p>');

SET @template_id := LAST_INSERT_ID();

-- Schedule on Every Day of the week `schedule_dow`=NULL
INSERT INTO `ohrm_notification_subscription`
(notification_id, schedule_dow, workflow_item_id, hidden) VALUES
    (@notification_id, NULL , NULL, 0);
SET @last_subscription_id := LAST_INSERT_ID();

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

SET @subscriber_id := LAST_INSERT_ID();

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

-- *******    Leave Approval Reminder - Summary   ******
INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`) VALUES
    ('Leave Approval Reminder - Summary', 0, @schedule_based_notification_type, 'SubscriberPendingActionsEventNotification', '{"email_processor":"PendingLeaveRequestEmailProcessor", "extra_roles":["HeadOfDepartment"]}', NULL, NULL, @leave_module);
SET @notification_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
    (@notification_id, @email_media, 'en_US',
     'Leave request approval reminder notification sent to Subscriber',
     'Reminder: There are pending leave request(s) that require your approval',
     '<p>Hi %recipientFirstName%,</p>
     </br>
     <p>The details of the leave requests are as follows:</p>
     </br>
     %LeaveRequestsPendingApproval%
     </br>
     <p>In addition, you can click %loginUrl% to access all pending leave requests.</p>
     </br>
     <p>Please log into the system and review the requests as soon as possible. Your timely action will ensure that adequate coverage is in place during the employees'' absence.</p>
     <p>Thank you for your attention to this matter.</p>
     <p>You are getting this email because you were added as a subscriber to this email.</p>
     <p>This is an auto-generated email.</p>');

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
    (@notification_id, @email_media, 'en_US',
     'Leave request approval reminder notification sent to Supervisor',
     'Reminder: There are pending leave request(s) that require your approval',
     '<p>Hi %recipientFirstName%,</p>
     </br>
     <p>The details of the leave requests are as follows:</p>
     </br>
     %LeaveRequestsPendingApproval%
     </br>
     <p>In addition, you can click %loginUrl% to access all pending leave requests.</p>
     </br>
     <p>Please log into the system and review the requests as soon as possible. Your timely action will ensure that adequate coverage is in place during the employees'' absence.</p>
     <p>Thank you for your attention to this matter.</p>
     <p>This is an auto-generated email.</p>');

SET @template_id := LAST_INSERT_ID();

-- Schedule on Every Day of the week `schedule_dow`=NULL
INSERT INTO `ohrm_notification_subscription`
(notification_id, schedule_dow, workflow_item_id, hidden) VALUES
    (@notification_id, NULL , NULL, 0);
SET @last_subscription_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, address_part, parameters, type, user_role_id) VALUES
    (@email_media, 'to', NULL, 'UserRole', @supervisor_user_role);

SET @subscriber_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_token` (`name`, `display_name`, `class_name`, `entity_name`, `value_getters`, `data_group_id`,
                          `is_date`, `localize`, `parameters`,is_multi_attribute)
VALUES ('LeaveRequestsPendingApproval', 'Pending Leave Request Detail', 'LeaveRequestsTokenValueGenerator', 'LeaveRequestsPendingApproval', NULL, null, 0, 0, null,1),
('recipientFirstName', 'Recipient FirstName', null, 'Recipient', 'getRecipientName', null, 0, 0, null,0);

SET @firstTokenId := (SELECT LAST_INSERT_ID());
SET @module_id := (SELECT id FROM `ohrm_module` WHERE name = 'notification');


INSERT INTO `ohrm_restricted_module_token`(`token_id`,`restricted_module`)
SELECT `id`,@module_id FROM `ohrm_token` WHERE `id` >=@firstTokenId;