SET @estimate_request_workflow_id := 'ESTIMATE_REQUEST';
SET @claim_request_workflow_id := 'CLAIM_REQUEST';
SET @action_notification_type := (SELECT id
                                  FROM ohrm_notification_type
                                  WHERE `name` = 'Action Based');
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 := (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 @module_id := (SELECT id FROM ohrm_module where `name` = 'expense');

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

SET @affected_employee_subscriber := LAST_INSERT_ID();

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

SET @supervisor_user_role_subscriber := LAST_INSERT_ID();

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

SET @hod_user_role_subscriber := LAST_INSERT_ID();

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

SET @admin_user_role_subscriber := LAST_INSERT_ID();

-- Visible Emails
-- Hidden Emails
-- Action submit for Ess
INSERT INTO `ohrm_notification`
(`name`, `is_enable`, `type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`, `hidden`) VALUES
  ('Travel Request Submission', 1, @action_notification_type, 'ExpenseEmailProcessorWrapperEventNotification',
   '{"email_processor":"EstimateRequestEmailProcessor"}', @estimate_request_workflow_id, 'Submit', @module_id,'0');
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',
'Travel request submission notification sent to Subscriber',
'Travel Request Notification - %performerFullName% Submitted a Travel Request',
'Hi %recipientFirstName%,<br /><br />%performerFullName% has submitted a travel request.<br /><br />You can click %loginUrl% to directly access the travel request.<br /><br />Thank you,<br />You are getting this email because you are a subscriber.<br /><br />(This is a system generated email, please do not reply!)');

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
'Travel request submission notification sent to Supervisor',
'Travel Request Notification - %performerFullName% Submitted a Travel Request',
'Hi %recipientFirstName%,<br /><br />%performerFullName% has submitted a travel request for your approval.<br /><br />You can click %loginUrl% to directly access the travel request.<br /><br />Thank you.<br /><br />(This is a system generated email, please do not reply!)');
SET @supervisor_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` = @estimate_request_workflow_id AND `action` = 'Submit'  AND
                                          `role` = 'ESS USER';
SET @last_subscription_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription` (subscription_id, subscriber_id, template_id) SELECT
                                                                                           s.id,
                                                                                           @supervisor_user_role_subscriber,
                                                                                           @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_user_role_subscriber,
                                                                                           @supervisor_template_id
                                                                                         FROM
                                                                                           ohrm_notification_subscription s
                                                                                         WHERE s.id >=
                                                                                               @last_subscription_id;


-- Assign
INSERT INTO `ohrm_notification`
(`name`, `is_enable`, `type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`, `hidden`) VALUES
  ('Travel Request Assignment', 1, @action_notification_type, 'ExpenseEmailProcessorWrapperEventNotification',
   '{"email_processor":"EstimateRequestEmailProcessor"}', @estimate_request_workflow_id, 'Submit',@module_id, '0');
SET @notification_id := LAST_INSERT_ID();

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

-- Supervisor assign
INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
'Travel request supervisor assignment notification sent to Subscriber',
'Travel Request Notification - %performerFullName% Has Assigned a Travel Request for %employeeName% and Pending Final Approval',
'Hi %recipientFirstName%,<br /><br />%performerFullName% has assigned a travel request for %employeeName% and pending final approval.<br /><br />You can click %loginUrl% to directly access the travel request.<br /><br />Thank you,<br />You are getting this email because you are a subscriber.<br /><br />(This is a system generated email, please do not reply!)');


INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
'Travel request assignment notification sent to Employee',
'Travel Request Notification - %performerFullName% Has Assigned a Travel Request for You',
'Hi %recipientFirstName%,<br /><br />%performerFullName% has assigned a travel request for you.<br /><br />You can click %loginUrl% to directly access the travel request.<br /><br />Thank you.<br /><br />(This is a system generated email, please do not reply!)');
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_id, @email_media, 'en_US',
'Travel request assignment notification sent to admin',
'Travel Request Notification - %performerFullName% Has Assigned a Travel Request for %employeeName% and Pending Your Approval',
'Hi %recipientFirstName%,<br /><br />%performerFullName% has assigned a travel request for %employeeName% and pending your approval.<br /><br />You can click %loginUrl% to directly access the travel request.<br /><br />Thank you.<br /><br />(This is a system generated email, please do not reply!)');
SET @admin_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` = @estimate_request_workflow_id AND `action` = 'Assign'  AND
                                          (`role` = 'SUPERVISOR' or `role`='HEADOFDEPARTMENT');
SET @last_subscription_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription` (subscription_id, subscriber_id, template_id) SELECT
                                                                                           s.id,
                                                                                           @affected_employee_subscriber,
                                                                                           @affected_emp_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,
                                                                                           @admin_user_role_subscriber,
                                                                                           @admin_template_id
                                                                                         FROM
                                                                                           ohrm_notification_subscription s
                                                                                         WHERE s.id >=
                                                                                               @last_subscription_id;


-- Admin assign

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
'Travel request assignment notification sent to Subscriber',
'Travel Request Notification - %performerFullName% Has Assigned a Travel Request for %employeeName%',
'Hi %recipientFirstName%,<br /><br />%performerFullName% has assigned a travel request for %employeeName%.<br /><br />You can click %loginUrl% to directly access the travel request.<br /><br />Thank you,<br />You are getting this email because you are a subscriber.<br /><br />(This is a system generated email, please do not reply!)');

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
'Travel request assignment notification sent to Supervisor',
'Travel Request Notification - %performerFullName% Has Assigned a Travel Request for %employeeName%',
'Hi %recipientFirstName%,<br /><br />%performerFullName% has assigned a travel request for %employeeName%.<br /><br />You can click %loginUrl% to directly access the travel request.<br /><br />Thank you.<br /><br />(This is a system generated email, please do not reply!)');
SET @supervisor_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` = @estimate_request_workflow_id AND `action` = 'Assign'  AND
                                          `role` = 'ADMIN';
SET @last_subscription_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription` (subscription_id, subscriber_id, template_id) SELECT
                                                                                           s.id,
                                                                                           @affected_employee_subscriber,
                                                                                           @affected_emp_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,
                                                                                           @supervisor_user_role_subscriber,
                                                                                           @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_user_role_subscriber,
                                                                                           @supervisor_template_id
                                                                                         FROM
                                                                                           ohrm_notification_subscription s
                                                                                         WHERE s.id >=
                                                                                               @last_subscription_id;


-- approve estimate

INSERT INTO `ohrm_notification`
(`name`, `is_enable`, `type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`, `hidden`) VALUES
  ('Travel Request Approval', 1, @action_notification_type, 'ExpenseEmailProcessorWrapperEventNotification',
   '{"email_processor":"EstimateRequestEmailProcessor"}', @estimate_request_workflow_id, 'Approve',@module_id, '0');
SET @notification_id := LAST_INSERT_ID();

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

-- supervisor approval
INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
"Travel request supervisor approval notification sent to Subscriber",
"Travel Request Notification - %employeeName%'s Travel Request Has Been Approved by %performerFullName% and Pending Final Approval",
"Hi %recipientFirstName%,<br /><br />%employeeName%'s travel request has been approved by %performerFullName% and pending final approval.<br /><br />You can click %loginUrl% to directly access the travel request.<br /><br />Thank you,<br />You are getting this email because you are a subscriber.<br /><br />(This is a system generated email, please do not reply!)");

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
"Travel request supervisor approval notification sent to Employee",
"Travel Request Notification - %performerFullName% Has Approved Your Travel Request",
"Hi %recipientFirstName%,<br /><br />Your travel request has been approved by %performerFullName% and pending final approval.<br /><br />You can click %loginUrl% to directly access the travel request.<br /><br />Thank you.<br /><br />(This is a system generated email, please do not reply!)");
SET @aff_emp_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',
"Travel request supervisor approval notification sent to Admin",
"Travel Request Notification - %employeeName%'s Travel Request Has Been Approved by %performerFullName% and Pending Your Approval",
"Hi %recipientFirstName%,<br /><br />%employeeName%'s travel request has been approved by %performerFullName% and pending your aproval.<br /><br />You can click %loginUrl% to directly access the travel request.<br /><br />Thank you.<br /><br />(This is a system generated email, please do not reply!)");
SET @admin_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` = @estimate_request_workflow_id AND `action` = 'Approve' AND  (`role` = 'SUPERVISOR' OR `role`='HEADOFDEPARTMENT');
SET @last_subscription_id := 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;

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

-- admin approval
INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
"Travel request approval notification sent to Subscriber",
"Travel Request Notification - %performerFullName% Has Approved %employeeName%'s Travel Request",
"Hi %recipientFirstName%,<br /><br />%performerFullName% has approved %employeeName%'s travel request.<br /><br />You can click %loginUrl% to directly access the travel request.<br /><br />Thank you,<br />You are getting this email because you are a subscriber.<br /><br />(This is a system generated email, please do not reply!)");


INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
"Travel request admin approval notification sent to Employee",
"Travel Request Notification - %performerFullName% Has Approved Your Travel Request",
"Hi %recipientFirstName%,<br /><br />%performerFullName% has approved your travel request.<br /><br />You can click %loginUrl% to directly access the travel request.<br /><br />Thank you.<br /><br />(This is a system generated email, please do not reply!)");
SET @aff_emp_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',
"Travel request supervisor approval notification sent to Employee",
"Travel Request Notification - %performerFullName% Has Approved %employeeName%'s Travel Request",
"Hi %recipientFirstName%,<br /><br />%performerFullName% has approved %employeeName%'s travel request.<br /><br />You can click %loginUrl% to directly access the travel request.<br /><br />Thank you.<br /><br />(This is a system generated email, please do not reply!)");
SET @supervisor_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` = @estimate_request_workflow_id AND `action` = 'Approve' AND  `role` = 'ADMIN';
SET @last_subscription_id := 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;

INSERT INTO `ohrm_notification_subscriber_subscription` (subscription_id, subscriber_id, template_id) SELECT
                                                                                           s.id,
                                                                                           @supervisor_user_role_subscriber,
                                                                                           @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_user_role_subscriber,
                                                                                           @supervisor_template_id
                                                                                         FROM
                                                                                           ohrm_notification_subscription s
                                                                                         WHERE s.id >=
                                                                                               @last_subscription_id;


-- reject estimate

INSERT INTO `ohrm_notification`
(`name`, `is_enable`, `type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`, `hidden`) VALUES
  ('Travel Request Rejection', 1, @action_notification_type, 'ExpenseEmailProcessorWrapperEventNotification',
   '{"email_processor":"EstimateRequestEmailProcessor"}', @estimate_request_workflow_id, 'Reject',@module_id, '0');
SET @notification_id := LAST_INSERT_ID();

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

-- supervisor reject

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
"Travel request rejection notification sent to Subscriber",
"Travel Request Notification - %performerFullName% Has Rejected Travel Request of %employeeName%",
"Hi %recipientFirstName%,<br /><br />%performerFullName% has rejected travel request of %employeeName%.<br /><br />You can click %loginUrl% to directly access the travel request.<br /><br />Thank you,<br />You are getting this email because you are a subscriber.<br /><br />(This is a system generated email, please do not reply!)");


INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
"Travel request rejection notification sent to Employee",
"Travel Request Notification - %performerFullName% Has Rejected Your Travel Request",
"Hi %recipientFirstName%,<br /><br />%performerFullName% has rejected your travel request. Please revisit the details and submit it again.<br /><br />You can click %loginUrl% to directly access the travel request.<br /><br />Thank you.<br /><br />(This is a system generated email, please do not reply!)");
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` = @estimate_request_workflow_id AND `action` = 'Reject' AND  (`role` = 'SUPERVISOR' OR `role`='HEADOFDEPARTMENT');
SET @last_subscription_id := 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;

-- admin reject

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
"Travel request rejection notification sent to Supervisor",
"Travel Request Notification - %performerFullName% Has Rejected Travel Request of %employeeName%",
"Hi %recipientFirstName%,<br /><br />%performerFullName% has rejected travel request of %employeeName%.<br /><br />You can click %loginUrl% to directly access the travel request.<br /><br />Thank you.<br /><br />(This is a system generated email, please do not reply!)");
SET @supervisor_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` = @estimate_request_workflow_id AND `action` = 'Reject' AND  `role` = 'ADMIN';
SET @last_subscription_id := 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;

INSERT INTO `ohrm_notification_subscriber_subscription` (subscription_id, subscriber_id, template_id) SELECT
                                                                                           s.id,
                                                                                           @supervisor_user_role_subscriber,
                                                                                           @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_user_role_subscriber,
                                                                                           @supervisor_template_id
                                                                                         FROM
                                                                                           ohrm_notification_subscription s
                                                                                         WHERE s.id >=
                                                                                               @last_subscription_id;

-- cancel travel request

INSERT INTO `ohrm_notification`
(`name`, `is_enable`, `type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`, `hidden`) VALUES
  ('Travel Request Cancellation', 1, @action_notification_type, 'ExpenseEmailProcessorWrapperEventNotification',
   '{"email_processor":"EstimateRequestEmailProcessor"}', @estimate_request_workflow_id, 'Cancel', @module_id,'0');
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',
'Travel request cancellation notification sent to Subscriber',
'Travel Request Notification - %performerFullName% Has Canceled Travel Request of %employeeName%',
'Hi %recipientFirstName%,<br /><br />%performerFullName% has canceled travel request of %employeeName%.<br /><br />You can click %loginUrl% to directly access the travel request.<br /><br />Thank you,<br />You are getting this email because you are a subscriber.<br /><br />(This is a system generated email, please do not reply!)');

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
'Travel request cancellation notification sent to Employee',
'Travel Request Notification - %performerFullName% Has Canceled Your Travel Request',
'Hi %recipientFirstName%,<br /><br />%performerFullName% has canceled your travel request.<br /><br />You can click %loginUrl% to directly access the travel request.<br /><br />Thank you.<br /><br />(This is a system generated email, please do not reply!)');
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` = @estimate_request_workflow_id AND `action` = 'Cancel'  AND
                                          `role` = 'ADMIN';
SET @last_subscription_id := 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;

-- Claim Request Submit
INSERT INTO `ohrm_notification`
(`name`, `is_enable`, `type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`, `hidden`) VALUES
  ('Claim Request Submission', 1, @action_notification_type, 'ExpenseEmailProcessorWrapperEventNotification',
   '{"email_processor":"ClaimRequestEmailProcessor"}', @claim_request_workflow_id, 'Submit', @module_id,'0');
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',
'Claim request submission notificaion sent to Subscriber',
'Expense Claim Notification - %performerFullName% Has Submitted an Expense Claim',
'Hi %recipientFirstName%,<br /><br />%performerFullName% has submitted an expense claim.<br /><br />You can click %loginUrl% to directly access the expense claim.<br /><br />Thank you,<br />You are getting this email because you are a subscriber.<br /><br />(This is a system generated email, please do not reply!)');

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
'Claim request submission notificaion sent to Supervisor',
'Expense Claim Notification - %performerFullName% Has Submitted an Expense Claim',
'Hi %recipientFirstName%,<br /><br />%performerFullName% has submitted an expense claim for your approval.<br /><br />You can click %loginUrl% to directly access the expense claim.<br /><br />Thank you.<br /><br />(This is a system generated email, please do not reply!)');
SET @supervisor_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` = 'Submit' AND
                                          `role` = 'ESS USER';
SET @last_subscription_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription` (subscription_id, subscriber_id, template_id) SELECT
                                                                                           s.id,
                                                                                           @supervisor_user_role_subscriber,
                                                                                           @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_user_role_subscriber,
                                                                                           @supervisor_template_id
                                                                                         FROM
                                                                                           ohrm_notification_subscription s
                                                                                         WHERE s.id >=
                                                                                               @last_subscription_id;

-- Claim request assign

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

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

-- Supervisor assign
INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
'Claim request assignment by supervisor notificaion sent to Subscriber',
'Expense Claim Notification - %performerFullName% Has Assigned an Expense Claim for %employeeName% and Pending Final Approval',
'Hi %recipientFirstName%,<br /><br />%performerFullName% has assigned an expense claim for %employeeName% and pending final approval.<br /><br />You can click %loginUrl% to directly access the expense claim.<br /><br />Thank you,<br />You are getting this email because you are a subscriber.<br /><br />(This is a system generated email, please do not reply!)');


INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
'Claim request assignment by supervisor notificaion sent to Employee',
'Expense Claim Notification - %performerFullName% Has Assigned an Expense Claim for You',
'Hi %recipientFirstName%,<br /><br />%performerFullName% has assigned an expense claim for you.<br /><br />You can click %loginUrl% to directly access the expense claim.<br /><br />Thank you.<br /><br />(This is a system generated email, please do not reply!)');
SET @aff_emp_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',
'Claim request assignment by supervisor notificaion sent to Admin',
'Expense Claim Notification - %performerFullName% Has Assigned an Expense Claim for %employeeName% and Pending Your Approval',
'Hi %recipientFirstName%,<br /><br />%performerFullName% has assigned an expense claim for %employeeName% and pending your approval.<br /><br />You can click %loginUrl% to directly access the expense claim.<br /><br />Thank you.<br /><br />(This is a system generated email, please do not reply!)');
SET @admin_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` = 'Assign'  AND
                                          (`role` = 'SUPERVISOR' OR `role`='HEADOFDEPARTMENT');
SET @last_subscription_id := 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;

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


-- Admin assign

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
'Claim request assignment by admin notificaion sent to other admins',
'Expense Claim Notification - %performerFullName% Has Assigned an Expense Claim for %employeeName%',
'Hi %recipientFirstName%,<br /><br />%performerFullName% has assigned an expense claim for %employeeName%.<br /><br />You can click %loginUrl% to directly access the expense claim.<br /><br />Thank you,<br />You are getting this email because you are a subscriber.<br /><br />(This is a system generated email, please do not reply!)');
SET @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',
'Claim request assignment by admin notificaion sent to Supervisor',
'Expense Claim Notification - %performerFullName% Has Assigned an Expense Claim for %employeeName%',
'Hi %recipientFirstName%,<br /><br />%performerFullName% has assigned an expense claim for %employeeName%.<br /><br />You can click %loginUrl% to directly access the expense claim.<br /><br />Thank you.<br /><br />(This is a system generated email, please do not reply!)');
SET @supervisor_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` = 'Assign'  AND
                                          `role` = 'ADMIN';
SET @last_subscription_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription` (subscription_id, subscriber_id, template_id) SELECT
                                                                                           s.id,
                                                                                           @admin_user_role,
                                                                                           @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,
                                                                                           @affected_employee_subscriber,
                                                                                           @aff_emp_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,
                                                                                           @supervisor_user_role_subscriber,
                                                                                           @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_user_role_subscriber,
                                                                                           @supervisor_template_id
                                                                                         FROM
                                                                                           ohrm_notification_subscription s
                                                                                         WHERE s.id >=
                                                                                               @last_subscription_id;

-- approve claim

INSERT INTO `ohrm_notification`
(`name`, `is_enable`, `type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`, `hidden`) VALUES
  ('Claim Request Approval', 1, @action_notification_type, 'ExpenseEmailProcessorWrapperEventNotification',
   '{"email_processor":"ClaimRequestEmailProcessor"}', @claim_request_workflow_id, 'Approve', @module_id,'0');

SET @notification_id := LAST_INSERT_ID();

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

-- Supervisor approve
INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
"Claim request supervisor approval notification send to Subscriber",
"Expense Claim Notification - %employeeName%'s Expense Claim Has Been Approved by %performerFullName% and Pending Final Approval",
"Hi %recipientFirstName%,<br /><br />%employeeName%'s expense claim has been approved by %performerFullName% and pending final approval.<br /><br />You can click %loginUrl% to directly access the expense claim.<br /><br />Thank you,<br />You are getting this email because you are a subscriber.<br /><br />(This is a system generated email, please do not reply!)");

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
"Claim request supervisor approval notification sent to Employee",
"Expense Claim Notification - %performerFullName% Has Approved Your Expense Claim",
"Hi %recipientFirstName%,<br /><br />Your expense claim has been approved by %performerFullName% and pending final approval.<br /><br />You can click %loginUrl% to directly access the expense claim.<br /><br />Thank you.<br /><br />(This is a system generated email, please do not reply!)");
SET @aff_emp_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',
"Claim request supervisor approval notification sent to Admin",
"Expense Claim Notification - %employeeName%'s Expense Claim Has Been Approved by %performerFullName% and Pending Your Approval",
"Hi %recipientFirstName%,<br /><br />%employeeName%'s expense claim has been approved by %performerFullName% and pending your approval.<br /><br />You can click %loginUrl% to directly access the expense claim.<br /><br />Thank you.<br /><br />(This is a system generated email, please do not reply!)");
SET @admin_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` = 'Approve' AND (`role` = 'Supervisor' or `role`= 'HeadOfDepartment');
SET @last_subscription_id := 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;

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

-- Admin approve

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
"Claim request approval notification send to Subscriber",
"Expense Claim Notification - %performerFullName% Has Approved %employeeName%'s Expense Claim",
"Hi %recipientFirstName%,<br /><br />%performerFullName% has approved expense claim of %employeeName%.<br /><br />You can click %loginUrl% to directly access the expense claim.<br /><br />Thank you,<br />You are getting this email because you are a subscriber.<br /><br />(This is a system generated email, please do not reply!)");
SET @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',
"Claim request admin approval notification sent to Employee",
"Expense Claim Notification - %performerFullName% Has Approved Your Expense Claim",
"Hi %recipientFirstName%,<br /><br />%performerFullName% has approved your expense claim.<br /><br />You can click %loginUrl% to directly access the expense claim.<br /><br />Thank you.<br /><br />(This is a system generated email, please do not reply!)");
SET @aff_emp_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',
"Claim request admin approval notification sent to Supervisor",
"Expense Claim Notification - %performerFullName% Has Approved %employeeName%'s Expense Claim",
"Hi %recipientFirstName%,<br /><br />%performerFullName% has approved expense claim of %employeeName%.<br /><br />You can click %loginUrl% to directly access the expense claim.<br /><br />Thank you.<br /><br />(This is a system generated email, please do not reply!)");
SET @supervisor_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` = 'Approve' AND  `role` = 'ADMIN';
SET @last_subscription_id := 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;

INSERT INTO `ohrm_notification_subscriber_subscription` (subscription_id, subscriber_id, template_id) SELECT
                                                                                           s.id,
                                                                                           @supervisor_user_role_subscriber,
                                                                                           @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_user_role_subscriber,
                                                                                           @supervisor_template_id
                                                                                         FROM
                                                                                           ohrm_notification_subscription s
                                                                                         WHERE s.id >=
                                                                                               @last_subscription_id;

-- reject claim

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

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

-- Supervisor reject

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
"Claim request rejection notification sent to Subscriber",
"Expense Claim Notification - %performerFullName% Has Rejected Expense Claim of %employeeName%",
"Hi %recipientFirstName%,<br /><br />%performerFullName% has rejected expense claim of %employeeName%.<br /><br />You can click %loginUrl% to directly access the expense claim.<br /><br />Thank you,<br />You are getting this email because you are a subscriber.<br /><br />(This is a system generated email, please do not reply!)");


INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
"Claim request rejection notificaion sent to Employee",
"Expense Claim Notification - %performerFullName% Has Rejected Your Expense Claim",
"Hi %recipientFirstName%,<br /><br />%performerFullName% has rejected your expense claim. Please revisit the details and submit it again.<br /><br />You can click %loginUrl% to directly access the expense claim.<br /><br />Thank you.<br /><br />(This is a system generated email, please do not reply!)");
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` = 'Reject' AND (`role` = 'Supervisor' or `role`='HeadOfDepartment');
SET @last_subscription_id := 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;



-- Admin reject


INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
"Claim request rejection notificaion sent to supervisor",
"Expense Claim Notification - %performerFullName% Has Rejected Expense Claim of %employeeName%",
"Hi %recipientFirstName%,<br /><br />%performerFullName% has rejected expense claim of %employeeName%.<br /><br />You can click %loginUrl% to directly access the expense claim.<br /><br />Thank you.<br /><br />(This is a system generated email, please do not reply!)");
SET @supervisor_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` = 'Reject' AND  `role` = 'ADMIN';
SET @last_subscription_id := 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;

INSERT INTO `ohrm_notification_subscriber_subscription` (subscription_id, subscriber_id, template_id) SELECT
                                                                                           s.id,
                                                                                           @supervisor_user_role_subscriber,
                                                                                           @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_user_role_subscriber,
                                                                                           @supervisor_template_id
                                                                                         FROM
                                                                                           ohrm_notification_subscription s
                                                                                         WHERE s.id >=
                                                                                               @last_subscription_id;

