-- orangehrmTimePlugin/install/notifications.sql
SET @action_notification_type := (SELECT id FROM ohrm_notification_type where `name`='Action Based');
SET @supervisor_user_role := (SELECT id FROM ohrm_user_role WHERE `name` = 'Supervisor');
SET @email_media := (SELECT id FROM ohrm_notification_media WHERE `name` = 'Email');
SET @module_id := (SELECT `id` FROM `ohrm_module` WHERE  `name` ='attendance');

-- ###    ACTION BASED    ########################################
-- *******    Submit Attendance Sheet ***********************************
-- >>> By: ESS User ->> Notify: Employee's Supervisor/s
INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`) VALUES
('Attendance Sheet Submission', 0, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
 '{"email_processor":"AttendanceSheetEmailProcessor"}', 'ATTENDANCE SHEET', 'Submit', @module_id);
SET @notification_id := (SELECT id FROM ohrm_notification WHERE name = 'Attendance Sheet Submission');

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',
 'Attendance Sheet submission notification sent to Subscriber',
 'Attendance Sheet of %EmployeeName% for the period %StartDate% - %EndDate% is submitted',
 '<p>Hi %recipientFirstName%,</p>
<p>Attendance Sheet of %EmployeeName% for the period %StartDate% - %EndDate% is submitted.<br />Please view the attendance sheet and take the necessary action to Approve/Reject as you wish.<br />Use the following direct link to access the attendance sheet.</p>
<p><a href="%URLToAttendanceSheet%">View Attendance Sheet</a></p>
<p>Thank You.<br />You are getting this email because you are a subscriber.<br />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',
 'Attendance Sheet submission notification sent to Supervisor',
 'Attendance Sheet of %EmployeeName% for the period %StartDate% - %EndDate% is submitted',
 '<p>Hi %recipientFirstName%,</p>
 <p>Attendance Sheet of %EmployeeName% for the period %StartDate% - %EndDate% is submitted.<br />Please view the attendance sheet and take the necessary action to Approve/Reject as you wish.<br />Use the following direct link to access the attendance sheet.</p>
 <p><a href="%URLToAttendanceSheet%">View Attendance Sheet</a></p>
 <p>Thank You.<br />This is an auto-generated email.</p>');

SET @template_id := (SELECT id FROM ohrm_notification_subscriber_subscription_template WHERE name = 'Attendance Sheet submission notification sent to Supervisor');

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

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;

-- ********   Approve Attendance Sheet   *************************
-- >>> By: Employee's Supervisor, Admin ->> Notify: Employee, Other Supervisors
INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`) VALUES
('Attendance Sheet Approval', 0, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
 '{"email_processor":"AttendanceSheetEmailProcessor"}', 'ATTENDANCE SHEET', 'Approve', @module_id);
SET @notification_approved_id := (SELECT id FROM ohrm_notification WHERE name = 'Attendance Sheet Approval');

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_approved_id, @email_media, 'en_US',
 'Attendance Sheet approval notification sent to Subscriber',
 '%performerFullName% approved attendance sheet of %EmployeeName% for the period %StartDate% - %EndDate%',
 '<p>Hi %recipientFirstName%,</p>
 <p>%performerFullName% has approved the attendance sheet of %EmployeeName% for the period of %StartDate% - %EndDate%.<br />Use the following direct link if you wish to access the attendance sheet.</p>
 <p><a href="%URLToAttendanceSheet%">View Attendance Sheet</a></p>
 <p>Thank You.<br />You are getting this email because you are a subscriber.<br />This is an auto-generated email.</p>');

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_approved_id, @email_media, 'en_US',
 'Attendance Sheet approval notification sent to Employee',
 '%performerFullName% approved your attendance sheet for the period %StartDate% - %EndDate%',
 '<p>Hi %recipientFirstName%,</p>
 <p>%performerFullName% has approved your attendance sheet for the period of %StartDate% - %EndDate%.<br />Use the following direct link if you wish to access the attendance sheet.</p>
 <p><a href="%URLToAttendanceSheet%">View Attendance Sheet</a></p>
 <p>Thank You.<br />This is an auto-generated email.</p>');

SET @affected_emp_template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_approved_id, @email_media, 'en_US',
 'Attendance Sheet approval notification sent to Supervisor',
 '%performerFullName% approved attendance sheet of %EmployeeName% for the period %StartDate% - %EndDate%',
 '<p>Hi %recipientFirstName%,</p>
 <p>%performerFullName% has approved the attendance sheet of %EmployeeName% for the period of %StartDate% - %EndDate%.<br />Use the following direct link if you wish to access the attendance sheet.</p>
 <p><a href="%URLToAttendanceSheet%">View Attendance Sheet</a></p>
 <p>Thank You.<br />This is an auto-generated email.</p>');

SET @supervisor_template_id := LAST_INSERT_ID();

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

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

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, @supervisor_template_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, type, name) VALUES
(@email_media, '{"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, @affected_emp_template_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;

-- ********   Reject Attendance Sheet   *************************
-- >>> By: Employee's Supervisor, Admin ->> Notify: Employee, Other Supervisors
INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`) VALUES
('Attendance Sheet Rejection', 0, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
 '{"email_processor":"AttendanceSheetEmailProcessor"}', 'ATTENDANCE SHEET', 'Reject', @module_id);
SET @notification_rejected_id := (SELECT id FROM ohrm_notification WHERE name = 'Attendance Sheet Rejection');

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

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_rejected_id, @email_media, 'en_US',
 'Attendance Sheet rejection notification sent to Subscriber',
 '%performerFullName% rejected attendance sheet of %EmployeeName% for the period %StartDate% - %EndDate%',
 '<p>Hi %recipientFirstName%,</p>
 <p>%performerFullName% has rejected the attendance sheet of %EmployeeName% for the period of %StartDate% - %EndDate%.<br />Please amend the attendance sheet and submit for approval.<br />Use the following direct link to access the attendance sheet.</p>
 <p><a href="%URLToAttendanceSheet%">View Attendance Sheet</a></p>
 <p>Thank You.<br />You are getting this email because you are a subscriber.<br />This is an auto-generated email.</p>');

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_rejected_id, @email_media, 'en_US',
 'Attendance Sheet rejection notification sent to Employee',
 '%performerFullName% rejected your attendance sheet for the period %StartDate% - %EndDate%',
 '<p>Hi %recipientFirstName%,</p>
 <p>%performerFullName% has rejected your attendance sheet for the period of %StartDate% - %EndDate%.<br />Please amend the attendance sheet and submit for approval.
 Use the following direct link to access the attendance sheet.</p>
 <p><a href="%URLToAttendanceSheet%">View Attendance Sheet</a></p>
 <p>Thank You.<br />This is an auto-generated email.</p>');
SET @affected_emp_template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_rejected_id, @email_media, 'en_US',
 'Attendance Sheet rejection notification sent to Supervisor',
 '%performerFullName% rejected attendance sheet of %EmployeeName% for the period %StartDate% - %EndDate%',
 '<p>Hi %recipientFirstName%,</p>
 <p>%performerFullName% has rejected the attendance sheet of %EmployeeName% for the period of %StartDate% - %EndDate%.<br />Please amend the attendance sheet and submit for approval.<br />Use the following direct link to access the attendance sheet.</p>
 <p><a href="%URLToAttendanceSheet%">View Attendance Sheet</a></p>
 <p>Thank You.<br />This is an auto-generated email.</p>');
SET @supervisor_template_id := LAST_INSERT_ID();

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

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, @supervisor_template_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, type, name) VALUES
(@email_media, '{"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, @affected_emp_template_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;

SET @max_event_listener_position := (SELECT MAX(`position`)FROM ohrm_event_listener);
INSERT INTO `ohrm_event_listener` (`event`, `listener_class`, `enabled`, `position`) VALUES
('attendance.sheet_state_change', 'AttendanceSheetMailer', '1', @max_event_listener_position+1);
