ALTER TABLE `ohrm_request` ADD `secondary_owner_id` INT(7) NOT NULL AFTER `owner_id`;
ALTER TABLE `ohrm_request` ADD CONSTRAINT FOREIGN KEY (`owner_id`) REFERENCES `hs_hr_employee`(`emp_number`) ON DELETE SET NULL ;

INSERT INTO `ohrm_event_listener` ( `event`, `listener_class`, `enabled`, `position`) VALUES ('requestTracker.add_request_type', 'RequestDeskRequestTypeOwnerUpdateEventListener', '1', '10');
INSERT INTO `ohrm_event_listener` ( `event`, `listener_class`, `enabled`, `position`) VALUES ('requestTracker.update_request_type', 'RequestDeskRequestTypeOwnerUpdateEventListener', '1', '10');
INSERT INTO `ohrm_event_listener` ( `event`, `listener_class`, `enabled`, `position`) VALUES ('requestTracker.update_request_type', 'RequestDeskRequestTypeOwnerRemoveEventListener', '1', '12');
INSERT INTO `ohrm_event_listener` ( `event`, `listener_class`, `enabled`, `position`) VALUES ('requestTracker.request_update', 'RequestDeskRequestOwnerUpdateEventListener', '1', '12');
INSERT INTO `ohrm_event_listener` ( `event`, `listener_class`, `enabled`, `position`) VALUES ('requestTracker.request_update', 'RequestDeskRequestOwnerRemoveEventListener', '1', '15');

-- request owner added/updated from request type notification


SET @email_media := (SELECT id FROM ohrm_notification_media WHERE `name` = 'Email');
SET @notification_type := (SELECT id FROM ohrm_notification_type WHERE `name`='Other');
SET @rd_module_id := (SELECT `id` FROM `ohrm_module` WHERE name = 'Request Desk');




INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`, `hidden`) VALUES
    ('(General/Hiring) Request Desk: Request Type Owner Updated', 1, @notification_type, 'LegacyEmailProcessorWrapperEventNotification',
     '{"email_processor":"RequestTrackerRequestTypeEmailProcessor"}', NULL, NULL,@rd_module_id, 0);
SET @notification_id := LAST_INSERT_ID();


INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
    (@notification_id, @email_media, 'en_US',
     'Request Type Owner Updated',
     'You are Selected as the Request Type Owner of %requestType%',
     '<p>Hi %recipientFullName%,<br /><br />You are assigned for %requestType% as a %ownerType%.<br />Assigned By: %assignedBy%<br /><br />Assigned On: %assignedDateTime%<br /><br />Please login to system, for more details.<br /><br />Thank you.<br /><br />You are getting this email because you are a subscriber.<br />This is an automated notification.</p>');


SET @template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscription`
(notification_id,  workflow_item_id, hidden) VALUES
    (@notification_id,  NULL, 0);
SET @last_subscription_id := LAST_INSERT_ID();


INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, type, name) VALUES
    (@email_media, '{"prevent_delete":true}', 'Custom','Request Owner');

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;

-- removing owner from a request type

SET @email_media := (SELECT id FROM ohrm_notification_media WHERE `name` = 'Email');
SET @notification_type := (SELECT id FROM ohrm_notification_type WHERE `name`='Other');
SET @rd_module_id := (SELECT `id` FROM `ohrm_module` WHERE name = 'Request Desk');

INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`, `hidden`) VALUES
('(General/Hiring) Request Desk: Request Type Owner Removed', 1, @notification_type, 'LegacyEmailProcessorWrapperEventNotification',
 '{"email_processor":"RequestTrackerRequestTypeOwnerRemoveEmailProcessor"}', NULL, NULL,@rd_module_id, 0);
SET @notification_id := LAST_INSERT_ID();


INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
 'Request Type Owner Removed',
 'Your Work on %requestType% Has Been Delegated',
 '<p>Hi %recipientFullName%,<br /><br />Your work as the %ownerType% of the %requestType% has been
delegated to another user.<br /><br/>Thank you for taking part on the request type.</br><br />Delegated By: %assignedBy%<br /><br />Delegated On: %assignedDateTime%<br /><br />Thank you.<br /><br />You are getting this email because you are a subscriber.<br />This is an automated notification.</p>');


SET @template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscription`
(notification_id,  workflow_item_id, hidden) VALUES
(@notification_id,  NULL, 0);
SET @last_subscription_id := LAST_INSERT_ID();


INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, type, name) VALUES
(@email_media, '{"prevent_delete":true}', 'Custom','Request Owner');

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;


-- request owner add/update on request

SET @email_media := (SELECT id FROM ohrm_notification_media WHERE `name` = 'Email');
SET @notification_type := (SELECT id FROM ohrm_notification_type WHERE `name`='Other');
SET @rd_module_id := (SELECT `id` FROM `ohrm_module` WHERE name = 'Request Desk');


INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`, `hidden`) VALUES
('(General/Hiring) Request Desk: Request Owner Updated', 1, @notification_type, 'LegacyEmailProcessorWrapperEventNotification',
 '{"email_processor":"RequestTrackerRequestEmailProcessor"}', NULL, NULL,@rd_module_id, 0);
SET @notification_id := LAST_INSERT_ID();


INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
 'Request Owner Updated',
 'You are Selected as the Request Owner of %requestType%: %title%',
 '<p>Hi %recipientFullName%,<br /><br />You are assigned for %requestType%: %title% as a %ownerType%.<br />Assigned By: %assignedBy%<br /><br />Assigned On: %assignedDateTime%<br /><br />Please login to system, for more details.<br /><br />Link: %link%<br /><br />Thank you.<br /><br />You are getting this email because you are a subscriber.<br />This is an automated notification.</p>');


SET @template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscription`
(notification_id,  workflow_item_id, hidden) VALUES
(@notification_id,  NULL, 0);
SET @last_subscription_id := LAST_INSERT_ID();


INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, type, name) VALUES
(@email_media, '{"prevent_delete":true}', 'Custom','Request Owner');

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;
-- removing owner from a request

SET @email_media := (SELECT id FROM ohrm_notification_media WHERE `name` = 'Email');
SET @notification_type := (SELECT id FROM ohrm_notification_type WHERE `name`='Other');
SET @rd_module_id := (SELECT `id` FROM `ohrm_module` WHERE name = 'Request Desk');

INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`, `hidden`) VALUES
('(General/Hiring) Request Desk: Request Owner Removed', 1, @notification_type, 'LegacyEmailProcessorWrapperEventNotification',
 '{"email_processor":"RequestTrackerRequestOwnerRemoveEmailProcessor"}', NULL, NULL,@rd_module_id, 0);
SET @notification_id := LAST_INSERT_ID();


INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
 'Request Owner Removed',
 'Your Work on %requestType%: %title% Has Been Delegated',
 '<p>Hi %recipientFullName%,<br /><br />Your work as the %ownerType% of the %requestType%: %title% has
been delegated to another user.<br /><br/>Thank you for taking part on the request.</br>Delegated By: %assignedBy%<br /><br />Delegated On: %assignedDateTime%<br /><br />Thank you.<br /><br />You are getting this email because you are a subscriber.<br />This is an automated notification.</p>');


SET @template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscription`
(notification_id,  workflow_item_id, hidden) VALUES
(@notification_id,  NULL, 0);
SET @last_subscription_id := LAST_INSERT_ID();


INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, type, name) VALUES
(@email_media, '{"prevent_delete":true}', 'Custom','Request Owner');

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;