-- orangehrmTimePlugin/install/notifications.sql
SET @action_notification_type := (SELECT id FROM ohrm_notification_type where `name`='Action Based');
SET @date_based_notification_type := (SELECT id FROM ohrm_notification_type where `name`='Date Based');
SET @schedule_based_notification_type := (SELECT id FROM ohrm_notification_type where `name`='Scheduled');
SET @summary_timesheet_workflow_id := (SELECT id FROM ohrm_workflow where `name`='summaryTimesheet');
SET @detailed_timesheet_workflow_id := (SELECT id FROM ohrm_workflow where `name`='detailedTimesheet');
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 @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 @time_module := (SELECT `id` FROM `ohrm_module` WHERE  `name` ='time');

-- ###    ACTION BASED    ########################################
-- *******    Submit Timesheet ***********************************
-- >>> 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
  ('Timesheet Submission', 0, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
   '{"email_processor":"TimesheetEmailProcessor"}', NULL, NULL, @time_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',
'Timesheet submission notification sent to Subscriber',
'%SubmitterName% submitted timesheet for the period %StartDate% - %EndDate%',
    '<p>Hi %recipientFirstName%,</p>
</br>
<p>%SubmitterName% has submitted the timesheet for the period of %StartDate% - %EndDate%.</p>
<p>Please view the timesheet and take the necessary action to Approve/Reject as you wish.
Use the following direct link to access the timesheet.</p>
<a href="%URLToTimesheet%">View Timesheet</a>
</br>
<p>Thank You.</p>
<p>You are getting this email because you are a subscriber.</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',
'Timesheet submission notification sent to Supervisor',
'%SubmitterName% submitted timesheet for the period %StartDate% - %EndDate%',
'<p>Hi %recipientFirstName%,</p>
</br>
<p>%SubmitterName% has submitted the timesheet for the period of %StartDate% - %EndDate%.</p>
<p>Please view the timesheet and take the necessary action to Approve/Reject as you wish.
Use the following direct link to access the timesheet.</p>
<a href="%URLToTimesheet%">View Timesheet</a>
</br>
<p>Thank You.</p>
<p>This is an auto-generated email.</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` = @summary_timesheet_workflow_id OR `workflow` = @detailed_timesheet_workflow_id) AND `action` = '1';
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 Timesheet   *************************
-- >>> 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
  ('Timesheet Approval', 0, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
   '{"email_processor":"TimesheetEmailProcessor"}', NULL, NULL, @time_module);
SET @notification_approved_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',
'Timesheet approval notification sent to Subscriber',
'%ApproverName% approved timesheet of %EmployeeName% for the period %StartDate% - %EndDate%',
'<p>Hi %recipientFirstName%,</p>
</br>
<p>%ApproverName% has approved the timesheet of %EmployeeName% for the period of %StartDate% - %EndDate% with the comment "%Comment%".</p>
<p>Use the following direct link if you wish to access the timesheet.</p>
<a href="%URLToTimesheet%">View Timesheet</a>
</br>
<p>Thank You.</p>
<p>You are getting this email because you are a subscriber.</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_approved_id, @email_media, 'en_US',
'Timesheet approval notification sent to Employee',
'%ApproverName% approved your timesheet for the period %StartDate% - %EndDate%',
'<p>Hi %recipientFirstName%,</p>
</br>
<p>%ApproverName% has approved your timesheet for the period of %StartDate% - %EndDate% with the comment "%Comment%".</p>
<p>Use the following direct link if you wish to access the timesheet.</p>
<a href="%URLToTimesheet%">View Timesheet</a>
</br>
<p>Thank You.</p>
<p>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',
'Timesheet approval notification sent to Supervisor',
'%ApproverName% approved timesheet of %EmployeeName% for the period %StartDate% - %EndDate%',
'<p>Hi %recipientFirstName%,</p>
</br>
<p>%ApproverName% has approved the timesheet of %EmployeeName% for the period of %StartDate% - %EndDate% with the comment "%Comment%".</p>
<p>Use the following direct link if you wish to access the timesheet.</p>
<a href="%URLToTimesheet%">View Timesheet</a>
</br>
<p>Thank You.</p>
<p>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` = @summary_timesheet_workflow_id OR `workflow` = @detailed_timesheet_workflow_id) AND `action` = '2';
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 Timesheet   *************************
-- >>> 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
  ('Timesheet Rejection', 0, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
   '{"email_processor":"TimesheetEmailProcessor"}', NULL, NULL, @time_module);
SET @notification_rejected_id := LAST_INSERT_ID();

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',
'Timesheet rejection notification sent to Subscriber',
'%RejectorName% rejected timesheet of %EmployeeName% for the period %StartDate% - %EndDate%',
'<p>Hi %recipientFirstName%,</p>
</br>
<p>%RejectorName% has rejected the timesheet of %EmployeeName% for the period of %StartDate% - %EndDate% with the comment "%Comment%".</p>
<p>Please amend the timesheet as per the comment provided by %RejectorName% and submit for approval.</p>
<p>Use the following direct link to access the timesheet.</p>
<a href="%URLToTimesheet%">View Timesheet</a>
</br>
<p>Thank You.<p>
<p>You are getting this email because you are a subscriber.</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_rejected_id, @email_media, 'en_US',
'Timesheet rejection notification sent to Employee',
'%RejectorName% rejected your timesheet for the period %StartDate% - %EndDate%',
'<p>Hi %recipientFirstName%,</p>
</br>
<p>%RejectorName% has rejected your timesheet for the period of %StartDate% - %EndDate% with the comment "%Comment%".</p>
<p>Please amend the timesheet as per the comment provided by %RejectorName% and submit for approval.</p>
<p>Use the following direct link to access the timesheet.</p>
<a href="%URLToTimesheet%">View Timesheet</a>
</br>
<p>Thank You.</p>
<p>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',
'Timesheet rejection notification sent to Supervisor',
'%RejectorName% rejected timesheet of %EmployeeName% for the period %StartDate% - %EndDate%',
'<p>Hi %recipientFirstName%,</p>
</br>
<p>%RejectorName% has rejected the timesheet of %EmployeeName% for the period of %StartDate% - %EndDate% with the comment "%Comment%".</p>
<p>Please amend the timesheet as per the comment provided by %RejectorName% and submit for approval.</p>
<p>Use the following direct link to access the timesheet.</p>
<a href="%URLToTimesheet%">View Timesheet</a>
</br>
<p>Thank You.</p>
<p>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` = @summary_timesheet_workflow_id OR `workflow` = @detailed_timesheet_workflow_id) AND `action` = '3';
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;


-- ####   DATE BASED  ###############################################
-- *******    Timesheet Submission Reminder   ***********************
-- >>> Notify: ESS
INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `workflow_id`, `workflow_action`,`module_id`) VALUES
  ('Timesheet Submission Reminder', 0, @schedule_based_notification_type, 'TimesheetSubmissionReminderEventNotification', NULL, NULL,@time_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',
'Timesheet submission reminder notification sent to Subscriber',
'Reminder - Employees have pending timesheets to be submitted',
'<p>Hi %recipientFirstName%,</p>
</br>
<p>Please note that some employees have pending timesheets which are not submitted.</p>

%TimesheetDetails%
</br>
<p>Thank You.</p>
<p>You are getting this email because you are a subscriber.</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',
'Timesheet submission reminder notification sent to Employee',
'Reminder - Submit your pending timesheets',
'<p>Hi %recipientFirstName%,</p>
</br>
<p>Please note that you have timesheets which are not submitted.</p>

%TimesheetDetails%
</br>
<p>Thank You.</p>
<p>This is an auto-generated email.</p>');

SET @template_id := LAST_INSERT_ID();

-- Schedule on Every Monday of the week `schedule_dow`=2
INSERT INTO `ohrm_notification_subscription`
  (notification_id, schedule_dow, workflow_item_id, hidden) VALUES
  (@notification_id, 2, NULL, 1);
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;

-- *******    Timesheet Approval Reminder     ***********************
-- >>> Notify: Supervisor/s
INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `workflow_id`, `workflow_action`,`module_id`) VALUES
  ('Timesheet Approval Reminder', 0, @schedule_based_notification_type, 'TimesheetApprovalReminderEventNotification', NULL, NULL,@time_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',
'Timesheet approval reminder notification sent to Subscriber',
'Reminder - There are timesheets pending for approval',
'<p>Hi %recipientFirstName%,</p>
</br>
<p>Please note that there are timesheets pending for approval.</p>

%TimesheetDetails%
</br>
<p>Thank You.</p>
<p>You are getting this email because you are a subscriber.</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',
'Timesheet approval reminder notification sent to Supervisor',
'Reminder - There are timesheets pending for your approval',
'<p>Hi %recipientFirstName%,</p>
</br>
<p>Please note that you have timesheets pending for approval.</p>

%TimesheetDetails%
</br>
<p>Thank You.</p>
<p>This is an auto-generated email.</p>');

SET @template_id := LAST_INSERT_ID();
-- Schedule on Every Monday of the week `schedule_dow`=2
INSERT INTO `ohrm_notification_subscription`
(notification_id, schedule_dow, workflow_item_id, hidden) VALUES
  (@notification_id, 2, NULL, 1);
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','Default Supervisor');

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;

-- EVENT LISTENERS

INSERT INTO `ohrm_event_listener` (`event`, `listener_class`, `enabled`, `position`) VALUES
  ('time.event', 'TimesheetMailer', 1, 39);



