SET @email_media := (SELECT id FROM ohrm_notification_media WHERE `name` = 'Email');
SET @action_notification_type := (SELECT id FROM ohrm_notification_type WHERE `name`='Action Based');
SET @module_id := (SELECT `id` FROM `ohrm_module` WHERE name = 'Request Desk');

SET @requester_id := (SELECT id FROM ohrm_user_role where name = 'Requester');
SET @request_owner_id := (SELECT id FROM ohrm_user_role where name = 'RequestOwner');
SET @request_approver_id := (SELECT id FROM ohrm_user_role where name = 'RequestApprover');

SET @requester_subscriber_id := (SELECT `id` FROM `ohrm_notification_subscriber` WHERE user_role_id = @requester_id);
SET @approver_subscriber_id := (SELECT `id` FROM `ohrm_notification_subscriber` WHERE user_role_id = @request_approver_id);
SET @owner_subscriber_id := (SELECT `id` FROM `ohrm_notification_subscriber` WHERE user_role_id = @request_owner_id);

INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`) VALUES
('Request Desk: Request Submission for Approval', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
 '{"email_processor":"RequestTrackerEmailProcessor", "extra_roles":["RequestApprover"]}', @workflow_id, 'Submit', @module_id);
SET @notification_id := (SELECT id FROM ohrm_notification WHERE `name` = 'Request Desk: Request Submission for Approval');

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', 'Help desk request submission notification sent to subscriber',
 'Notify Approver',
 'Hi %recipientFullName%,

 This is to notify that you have been included as the approver for below request. Please refer the details below.

 Request ID: %id%

 Request Title: %title%

 Description: %description%

 User: %requester%

 Resolution Date: %resolutionDate%

 Please login to your Request desk instance, for more details.
 Link: %link%

 Thank you.

 This is an automated notification.');

SET @last_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` = @workflow_id AND `action` = 'Submit' AND state IN ('Initial');
SET @last_subscription_id := LAST_INSERT_ID();

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


INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`) VALUES
('Request Desk: Requester Info Required', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
 '{"email_processor":"RequestTrackerEmailProcessor", "extra_roles":["RequestOwner", "RequestApprover", "Requester"]}', @workflow_id, 'Request Info', @module_id);
SET @notification_id := (SELECT id FROM ohrm_notification WHERE `name` = 'Request Desk: Requester Info Required');

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', 'Help desk request more information notification sent to subscriber', 'Requester Information Required',
 'Hi %recipientFullName%,

 Information is required about the request. Please refer the details below.

 Request ID: %id%

 Request Title: %title%

 Comment: %requestedInfo%

 User: %requester%

 Resolution Date: %resolutionDate%

 Please login to your Request desk instance, for more details.
 Link: %link%

 Thank you.

 This is an automated notification.');

SET @last_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` = @workflow_id AND `action` = 'Request Info';
SET @last_subscription_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`) VALUES
('Request Desk: Request Resubmission', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
 '{"email_processor":"RequestTrackerEmailProcessor", "extra_roles":["RequestOwner", "RequestApprover", "Requester"]}', @workflow_id, 'Submit', @module_id);
SET @notification_id := (SELECT id FROM ohrm_notification WHERE `name` = 'Request Desk: Request Resubmission');

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', 'Help desk request resubmitted notification sent to subscriber', 'Requester Information Resubmitted',
 'Hi %recipientFullName%,

 Requested Information has been provided by the user. Please refer the details below.

 Request ID: %id%

 Request Title: %title%

 Comment: %requestedInfo%

 Resolution Date: %resolutionDate%

 User: %requester%

 Please login to Request desk instance, for more details.
 Link: %link%

 Thank you.

 This is an automated notification.');

SET @last_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` = @workflow_id AND `action` = 'Submit' AND state IN ('Request Info Required') ;
SET @last_subscription_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`) VALUES
('Request Desk: Request Withdrawal', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
 '{"email_processor":"RequestTrackerEmailProcessor", "extra_roles":["RequestOwner", "RequestApprover", "Requester"]}', @workflow_id, 'Withdraw', @module_id);
SET @notification_id := (SELECT id FROM ohrm_notification WHERE `name` = 'Request Desk: Request Withdrawal');

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', 'Help desk request withdrawn notification sent to subscriber', 'Request Withdrawn',
 'Hi %recipientFullName%,

 Request was withdrawn. Please refer the details below.

 Request ID: %id%

 Request Title: %title%

 Description: %description%

 User: %requester%

 Resolution Date: %resolutionDate%

 Please login to your Request desk instance, for more details.
 Link: %link%

 Thank you.

 This is an automated notification.');

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

-- INSERT INTO `ohrm_notification_subscriber_subscription`(subscription_id, subscriber_id, template_id) SELECT
--     s.id, @owner_subscriber_id, @last_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, @approver_subscriber_id, @last_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, @requester_subscriber_id, @last_template_id FROM ohrm_notification_subscription s
--     WHERE s.workflow_item_id IN (select id from ohrm_workflow_state_machine WHERE `workflow` = @workflow_id AND `action` = 'Withdraw' AND role = 'Admin');

INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`) VALUES
('Request Desk: Request Completion', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
 '{"email_processor":"RequestTrackerEmailProcessor", "extra_roles":["RequestOwner", "RequestApprover", "Requester"]}', @workflow_id, 'Resolve', @module_id);
SET @notification_id := (SELECT id FROM ohrm_notification WHERE `name` = 'Request Desk: Request Completion');

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', 'Help desk request complete notification sent to subscriber', 'Request was Completed',
 'Hi %recipientFullName%,

 Request was completed. Please refer the details below.

 Request ID: %id%

 Request Title: %title%

 Description: %description%

 User: %requester%

 Resolution Date: %resolutionDate%

 Resolution: %resolution%

 Please login to Request desk instance, for more details.
 Link: %link%

 Thank you.

 This is an automated notification.');

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

-- INSERT INTO `ohrm_notification_subscriber_subscription`(subscription_id, subscriber_id, template_id) SELECT
--     s.id, @requester_subscriber_id, @last_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, @approver_subscriber_id, @last_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, @owner_subscriber_id, @last_template_id FROM ohrm_notification_subscription s
--     WHERE s.workflow_item_id = (select id from ohrm_workflow_state_machine WHERE `workflow` = @workflow_id AND `action` = 'Resolve' AND role = 'Admin');

INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`) VALUES
('Request Desk: Request Reopen', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
 '{"email_processor":"RequestTrackerEmailProcessor", "extra_roles":["RequestOwner", "RequestApprover", "Requester"]}', @workflow_id, 'Reopen', @module_id);
SET @notification_id := (SELECT id FROM ohrm_notification WHERE `name` = 'Request Desk: Request Reopen');

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', 'Help desk request reopened notification sent to subscriber', 'Request Reopened',
 'Hi %recipientFullName%,

 Request was reopened. Please refer the details below.

 Request ID: %id%

 Request Title: %title%

 Description: %description%

 User: %requester%

 Please login to your Request desk instance, for more details.
 Link: %link%

 Thank you.

 This is an automated notification.');

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

-- INSERT INTO `ohrm_notification_subscriber_subscription`(subscription_id, subscriber_id, template_id) SELECT
--     s.id, @requester_subscriber_id, @last_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, @approver_subscriber_id, @last_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, @owner_subscriber_id, @last_template_id FROM ohrm_notification_subscription s
--     WHERE s.workflow_item_id = (select id from ohrm_workflow_state_machine WHERE `workflow` = @workflow_id AND `action` = 'Reopen' AND role = 'Admin');

INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`) VALUES
('Request Desk: Request Adding Comment', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
 '{"email_processor":"RequestTrackerEmailProcessor", "extra_roles":["RequestOwner", "RequestApprover", "Requester"]}', @workflow_id, 'Add Comment', @module_id);
SET @notification_id := (SELECT id FROM ohrm_notification WHERE `name` = 'Request Desk: Request Adding Comment');

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', 'Help desk request comment added notification sent to subscriber', 'Comment Added to the Request',
 'Hi %recipientFullName%,

 Comment was added to the request.. Please refer the details below.

 Request ID: %id%

 Request Title: %title%

 Description: %description%

 Comment: %comment%

 User: %requester%

 Please login to your Request desk instance, for more details.
 Link: %link%

 Thank you.

 This is an automated notification.');

SET @last_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` = @workflow_id AND `action` = 'Add Comment' ;
SET @last_subscription_id := LAST_INSERT_ID();

-- INSERT INTO `ohrm_notification_subscriber_subscription`(subscription_id, subscriber_id, template_id) SELECT
--     s.id, @approver_subscriber_id, @last_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, @owner_subscriber_id, @last_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, @requester_subscriber_id, @last_template_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;


INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`) VALUES
('Request Desk: Request Editing Comment', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
 '{"email_processor":"RequestTrackerEmailProcessor", "extra_roles":["RequestOwner", "RequestApprover", "Requester"]}', @workflow_id, 'Edit Comment', @module_id);
SET @notification_id := (SELECT id FROM ohrm_notification WHERE `name` = 'Request Desk: Request Editing Comment');

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', 'Help desk request comment added notification sent to subscriber', 'Comment Added to the Request',
 'Hi %recipientFullName%,

 Comment was added to the request.. Please refer the details below.

 Request ID: %id%

 Request Title: %title%

 Description: %description%

 Previous Comment: %prevComment%

 New Comment: %newComment%

 User: %requester%

 Please login to your Request desk instance, for more details.
 Link: %link%

 Thank you.

 This is an automated notification.');

SET @last_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` = @workflow_id AND `action` = 'Edit Comment' ;
SET @last_subscription_id := LAST_INSERT_ID();

-- INSERT INTO `ohrm_notification_subscriber_subscription`(subscription_id, subscriber_id, template_id) SELECT
--     s.id, @approver_subscriber_id, @last_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, @owner_subscriber_id, @last_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, @requester_subscriber_id, @last_template_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;


INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`) VALUES
('Request Desk: Request Approval', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
 '{"email_processor":"RequestTrackerEmailProcessor", "extra_roles":["RequestApprover", "Requester"]}', @workflow_id, 'Approve', @module_id);
SET @notification_id := (SELECT id FROM ohrm_notification WHERE `name` = 'Request Desk: Request Approval');

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', 'Help desk request approval notification sent to subscriber', 'Your request is Inprogress',
 'Hi %recipientFullName%,

 Your request has been approved and currently In progress. Please refer the details below.

 Request ID: %id%

 Request Title: %title%

 Description: %description%

 User: %requester%

 Resolution Date: %resolutionDate%

 Please login to Request desk instance, for more details.
 Link: %link%

 Thank you.

 This is an automated notification.');

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

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


INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US', 'Help desk request approval notification sent to subscriber', 'New Request Received',
 'Hi %recipientFullName%,

 New request received. Please refer the details below.

 Request ID: %id%

 Request Title: %title%

 Description: %description%

 User: %requester%

 Resolution Date: %resolutionDate%

 Please login to Request desk instance, for more details.
 Link: %link%

 Thank you.

 This is an automated notification.');

SET @last_template_id := LAST_INSERT_ID();

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


INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`) VALUES
('Request Desk: Request Rejection', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
 '{"email_processor":"RequestTrackerEmailProcessor", "extra_roles":["RequestApprover", "Requester"]}', @workflow_id, 'Approve', @module_id);
SET @notification_id := (SELECT id FROM ohrm_notification WHERE `name` = 'Request Desk: Request Rejection');

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', 'Help desk request rejection notification sent to subscriber', 'Request Rejected',
 'Hi %recipientFullName%,

 Your request has been Rejected. Please refer the details below.

 Request ID: %id%

 Request Title: %title%

 Description: %description%

 User: %requester%

 Resolution Date: %resolutionDate%

 Please login to Request desk instance, for more details.
 Link: %link%

 Thank you.

 This is an automated notification.');

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

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