-- ----------------------------------------------------------------------------------------------------------
-- ----------------------------------------------------------------------------------------------------------
-- Email notifications for workflow changes
-- ----------------------------------------------------------------------------------------------------------
-- ----------------------------------------------------------------------------------------------------------

-- ----------------------------------------------------------------------------------------------------------
-- vacancy related emails, notifications, email processors, templates
-- ----------------------------------------------------------------------------------------------------------

SET @action_notification_type := (SELECT id FROM ohrm_notification_type WHERE `name`='Action Based');
SET @vacancy_workflow_id := (SELECT id FROM ohrm_workflow WHERE `name`='vacancy');
SET @recruitment_workflow_id := (SELECT id FROM ohrm_workflow WHERE `name`='recruitment');

SET @panel_interview_workflow_id := (SELECT id FROM ohrm_workflow WHERE `name`='Panel Interview');
SET @one_on_one_interview_workflow_id := (SELECT id FROM ohrm_workflow WHERE `name`='One-On-One Interview');
SET @phone_interview_workflow_id := (SELECT id FROM ohrm_workflow WHERE `name`='Phone Interview');
SET @test_workflow_id := (SELECT id FROM ohrm_workflow WHERE `name`='Test');
SET @hire_workflow_id := (SELECT id FROM ohrm_workflow WHERE `name`='Hire');

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 @hiring_manager_user_role := (SELECT id FROM ohrm_user_role WHERE `name` = 'HiringManager');
SET @ess_user_role := (SELECT id FROM ohrm_user_role WHERE `name` = 'ESS');
SET @event_manager_user_role := (SELECT id FROM ohrm_user_role WHERE `name` = 'EventManager');

SET @email_media := (SELECT id FROM ohrm_notification_media WHERE `name` = 'Email');

SET @action_attach := '1';
SET @action_shortlist := '2';
SET @action_reject := '3';
SET @action_job_offer := '7';
SET @action_hire := '9';
SET @action_phone_interview := '10';
SET @action_panel_interview := '11';
SET @action_one_on_one_interview := '12';
SET @action_test := '13';
SET @action_preboarding := '14';

SET @action_update_marks := 'Update Marks';

SET @action_schedule_interview := 'Schedule';
SET @action_reschedule_interview := 'ReSchedule';

SET @init_state_schedule := 'Scheduled';
SET @init_state_reschedule := 'ReScheduled';

SET @recruitment_module := (SELECT `id` FROM `ohrm_module` WHERE  `name` ='recruitment');

INSERT INTO `ohrm_email` (`name`) VALUES ('vacancy.publish');
SET @vacancy_publish_email_id := (SELECT LAST_INSERT_ID());
INSERT INTO `ohrm_email` (`name`) VALUES ('vacancy.unpublish');
SET @vacancy_unpublish_email_id := (SELECT LAST_INSERT_ID());
INSERT INTO `ohrm_email` (`name`) VALUES ('vacancy.close');
SET @vacancy_close_email_id := (SELECT LAST_INSERT_ID());

SET @action_email_notification_type := (SELECT id FROM ohrm_email_notification_type where `name`='Action Based');
INSERT INTO `ohrm_email_notification` (`name`, `email_id`, `is_enable`,`type_id`) VALUES 
('Vacancy Publishing', @vacancy_publish_email_id, 0,@action_email_notification_type),
('Vacancy Inactivation', @vacancy_unpublish_email_id, 0,@action_email_notification_type),
('Vacancy Closure', @vacancy_close_email_id, 0,@action_email_notification_type);

INSERT INTO `ohrm_email_processor` (`email_id`, `class_name`) VALUES 
(@vacancy_publish_email_id, 'VacancyEmailProcessor'),
(@vacancy_unpublish_email_id, 'VacancyEmailProcessor'),
(@vacancy_close_email_id, 'VacancyEmailProcessor');


-- vacancy published email templates

INSERT INTO `ohrm_notification`
    (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`) VALUES
    ('Vacancy Publishing', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
    '{"email_processor":"VacancyEmailProcessor", "extra_roles":["HiringManager"]}', @vacancy_workflow_id, 'PUBLISH',@recruitment_module);
SET @notification_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
'Vacancy publishing notification sent to Admin',
'Notification: %VacancyName% has been %StateName%',
'<p>Hi %EmployeeFirstName%,<br /><br />The vacancy, %VacancyName%, has been %VacancyCurrentState% on %CurrentDate%. It was in %InitialState% state before being %VacancyCurrentState%.<br /><br /><br />Thank you.<br />This is an automated notification.</p>');
SET @template_id := LAST_INSERT_ID();

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

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

SET @subscriber_id := LAST_INSERT_ID();

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

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

SET @subscriber_id := LAST_INSERT_ID();

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


-- vacancy unpublished email templates

INSERT INTO `ohrm_notification`
    (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`) VALUES
    ('Vacancy Inactivation', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
    '{"email_processor":"VacancyEmailProcessor", "extra_roles":["HiringManager"]}', @vacancy_workflow_id, 'UNPUBLISH',@recruitment_module);
SET @notification_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
'Vacancy inactivation notification sent to Admin',
'Notification: %VacancyName% has been %StateName%',
'<p>Hi %EmployeeFirstName%,<br /><br />The vacancy, %VacancyName%, has been %VacancyCurrentState% on %CurrentDate%. It was in %InitialState% state before being %VacancyCurrentState%.<br /><br /><br />Thank you.<br />This is an automated notification.</p>');
SET @template_id := LAST_INSERT_ID();

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

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

SET @subscriber_id := LAST_INSERT_ID();

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

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

SET @subscriber_id := LAST_INSERT_ID();

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

-- vacancy closed email templates

INSERT INTO `ohrm_notification`
    (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`) VALUES
    ('Vacancy Closure', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
    '{"email_processor":"VacancyEmailProcessor", "extra_roles":["HiringManager"]}', @vacancy_workflow_id, 'CLOSE',@recruitment_module);
SET @notification_id := LAST_INSERT_ID();

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


INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
'Vacancy closure notification sent to Admin',
'Notification: %VacancyName% has been %StateName%',
'<p>Hi %EmployeeFirstName%,<br /><br />The vacancy, %VacancyName%, has been %VacancyCurrentState% on %CurrentDate%. It was in %InitialState% state before being %VacancyCurrentState%.<br /><br /><br />Thank you.<br />This is an automated notification.</p>');
SET @template_id := LAST_INSERT_ID();

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

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

SET @subscriber_id := LAST_INSERT_ID();

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

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

SET @subscriber_id := LAST_INSERT_ID();

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





-- ----------------------------------------------------------------------------------------------------------
-- candidate related emails, notifications, email processors, templates
-- ----------------------------------------------------------------------------------------------------------

INSERT INTO `ohrm_email` (`name`) VALUES ('candidate.Assigned a Vacancy');
SET @candidate_assign_vacancy_email_id := (SELECT LAST_INSERT_ID());
INSERT INTO `ohrm_email` (`name`) VALUES ('candidate.Schedule Interview');
SET @candidate_schedule_interview_email_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_email` (`name`) VALUES ('candidate.ReSchedule Event');
SET @candidate_reschedule_event_email_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_email` (`name`) VALUES ('candidate.Mark Interview Passed');
SET @candidate_mark_interview_passed_email_id := (SELECT LAST_INSERT_ID());
INSERT INTO `ohrm_email` (`name`) VALUES ('candidate.Mark Interview Failed');
SET @candidate_mark_interview_failed_email_id := (SELECT LAST_INSERT_ID());
INSERT INTO `ohrm_email` (`name`) VALUES ('candidate.Offer Job');
SET @candidate_offer_job_email_id := (SELECT LAST_INSERT_ID());
INSERT INTO `ohrm_email` (`name`) VALUES ('candidate.Decline Offer');
SET @candidate_decline_offer_email_id := (SELECT LAST_INSERT_ID());
INSERT INTO `ohrm_email` (`name`) VALUES ('candidate.Hire');
SET @candidate_hire_email_id := (SELECT LAST_INSERT_ID());

SET @action_email_notification_type := (SELECT id FROM ohrm_email_notification_type where `name`='Action Based');

INSERT INTO `ohrm_email_notification` (`name`, `email_id`, `is_enable`,`type_id`) VALUES 
('Interview schedules', @candidate_schedule_interview_email_id, 0,@action_email_notification_type),
('Event ReSchedule', @candidate_reschedule_event_email_id, 0,@action_email_notification_type),
('Interview Passes', @candidate_mark_interview_passed_email_id, 0,@action_email_notification_type),
('Interview Failures', @candidate_mark_interview_failed_email_id, 0,@action_email_notification_type),
('Job Offers', @candidate_offer_job_email_id, 0,@action_email_notification_type),
('Offer Declinations', @candidate_decline_offer_email_id, 0,@action_email_notification_type),
('Candidate Hires', @candidate_hire_email_id, 0,@action_email_notification_type);

INSERT INTO `ohrm_email_processor` (`email_id`, `class_name`) VALUES 
(@candidate_assign_vacancy_email_id, 'CandidateEmailProcessor'),
(@candidate_schedule_interview_email_id, 'CandidateEmailProcessor'),
(@candidate_reschedule_event_email_id, 'CandidateEmailProcessor'),
(@candidate_mark_interview_passed_email_id, 'CandidateEmailProcessor'),
(@candidate_mark_interview_failed_email_id, 'CandidateEmailProcessor'),
(@candidate_offer_job_email_id, 'CandidateEmailProcessor'),
(@candidate_decline_offer_email_id, 'CandidateEmailProcessor'),
(@candidate_hire_email_id, 'CandidateEmailProcessor');


-- candidate Assigned Vacancy email templates

INSERT INTO `ohrm_notification`
    (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`) VALUES
    ('Candidate Assigned to Vacancy', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
    '{"email_processor":"CandidateEmailProcessor", "extra_roles":["HiringManager", "EventManager"]}', @recruitment_workflow_id, @action_attach,@recruitment_module);
SET @notification_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
'Assignment of candidate to vacancy notification sent to Candidate',
'Notification: Your application is received for %VacancyName%',
'<p>Dear %CandidateFirstName% %CandidateLastName%,<br /><br />We received your application for %VacancyName%.<br />Thank you for your interest in %OrganizationName%.<br /><br />This is an automated notification.</p>');

SET @template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscription`
    (notification_id, workflow_item_id) SELECT
    @notification_id, id FROM ohrm_workflow_state_machine WHERE `workflow` = @recruitment_workflow_id AND `action` = @action_attach;
SET @last_subscription_id := LAST_INSERT_ID();
INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, TYPE, name) VALUES
    (@email_media, '{"prevent_delete":true}', 'Custom', 'Candidate');

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;

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
    (@notification_id, @email_media, 'en_US',
     'Candidate Assigned to Vacancy notification sent to Hiring Manager',
     'Candidate assigned to the %VacancyName% vacancy',
     '<p>Hi %SubscriberFirstName%,<br /><br />This is to inform you that we have added a candidate for the %VacancyName% vacancy.<br /><ul><li>Candidate Name: %CandidateFirstName% %CandidateLastName%</li><li>Department: %VacancySubunit%</li><li>Location: %VacancyLocation%</li><li>Job Position: %VacancyJobTitle%</li></ul>Please <a href="%CandidateProfileLink%">click here</a> to review the candidate''s profile.<br /><br />You were sent this email as you are a %UserRole% for this position.<br /><br />Thank you.<br />This is an automated notification.<br />%CandidateResume%</p>');
SET @template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, TYPE, user_role_id) VALUES
    (@email_media, NULL, 'UserRole', @hiring_manager_user_role);
SET @subscriber_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription`(subscription_id, subscriber_id, template_id) SELECT
                                                                                                         s.id, @subscriber_id, @template_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;
INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
    (@notification_id, @email_media, 'en_US',
     'Candidate Assigned to Vacancy notification sent to Event Manager',
     'Candidate assigned to the %VacancyName% vacancy',
     '<p>Hi %SubscriberFirstName%,<br /><br />This is to inform you that we have added a candidate for the %VacancyName% vacancy.<br /><ul><li>Candidate Name: %CandidateFirstName% %CandidateLastName%</li><li>Department: %VacancySubunit%</li><li>Location: %VacancyLocation%</li><li>Job Position: %VacancyJobTitle%</li></ul>Please <a href="%CandidateProfileLink%">click here</a> to review the candidate''s profile.<br /><br />You were sent this email as you are a %UserRole% for this position.<br /><br />Thank you.<br />This is an automated notification.<br />%CandidateResume%</p>');
SET @template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, TYPE, user_role_id) VALUES
    (@email_media, NULL, 'UserRole', @event_manager_user_role);
SET @subscriber_id := LAST_INSERT_ID();

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

-- Candidate Panel Interview Result Notification
INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`) VALUES
    ('Candidate Panel Interview Result Notification', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
     '{"email_processor":"CandidateEmailProcessor", "extra_roles":["HiringManager", "EventManager"]}', @panel_interview_workflow_id, @action_update_marks,@recruitment_module);
SET @notification_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id) SELECT
                                        @notification_id, id FROM ohrm_workflow_state_machine WHERE `workflow` = @panel_interview_workflow_id AND `action` = @action_update_marks;
SET @last_subscription_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',
     'Candidate Panel Interview Result Notification sent to Hiring Manager',
     'Candidate interview status update:  %CandidateFirstName% %CandidateLastName% for %VacancyName% - %EventName%',
     '<p>Hi %SubscriberFirstName%,<br /><br />This is to inform you of the results of a recent interview for %CandidateFirstName% %CandidateLastName%<br /><ul><li>Candidate Name: %CandidateFirstName% %CandidateLastName%</li><li>Interviewers: %InterviewerNames%</li><li>Interview Status: %EventResult%</li><li>Notes:<br />%EventNotes%</li></ul>You were sent this email as you are a %UserRole% for this position.<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, TYPE, user_role_id) VALUES
    (@email_media, NULL, 'UserRole', @hiring_manager_user_role);
SET @subscriber_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
    (@notification_id, @email_media, 'en_US',
     'Candidate Panel Interview Result Notification sent to Event Manager',
     'Candidate interview status update:  %CandidateFirstName% %CandidateLastName% for %VacancyName% - %EventName%',
     '<p>Hi %SubscriberFirstName%,<br /><br />This is to inform you of the results of a recent interview for %CandidateFirstName% %CandidateLastName%<br /><ul><li>Candidate Name: %CandidateFirstName% %CandidateLastName%</li><li>Interviewers: %InterviewerNames%</li><li>Interview Status: %EventResult%</li><li>Notes:<br />%EventNotes%</li></ul>You were sent this email as you are a %UserRole% for this position.<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, TYPE, user_role_id) VALUES
    (@email_media, NULL, 'UserRole', @event_manager_user_role);
SET @subscriber_id := LAST_INSERT_ID();

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

-- Candidate Phone Interview Result Notification
INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`) VALUES
    ('Candidate Phone Interview Result Notification', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
     '{"email_processor":"CandidateEmailProcessor", "extra_roles":["HiringManager", "EventManager"]}', @phone_interview_workflow_id, @action_update_marks,@recruitment_module);
SET @notification_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id) SELECT
                                        @notification_id, id FROM ohrm_workflow_state_machine WHERE `workflow` = @phone_interview_workflow_id AND `action` = @action_update_marks;
SET @last_subscription_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',
     'Candidate Phone Interview Result Notification sent to Hiring Manager',
     'Candidate interview status update:  %CandidateFirstName% %CandidateLastName% for %VacancyName% - %EventName%',
     '<p>Hi %SubscriberFirstName%,<br /><br />This is to inform you of the results of a recent interview for %CandidateFirstName% %CandidateLastName%<br /><ul><li>Candidate Name: %CandidateFirstName% %CandidateLastName%</li><li>Interviewers: %InterviewerNames%</li><li>Interview Status: %EventResult%</li><li>Notes:<br />%EventNotes%</li></ul>You were sent this email as you are a %UserRole% for this position.<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, TYPE, user_role_id) VALUES
    (@email_media, NULL, 'UserRole', @hiring_manager_user_role);
SET @subscriber_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
    (@notification_id, @email_media, 'en_US',
     'Candidate Phone Interview Result Notification sent to Event Manager',
     'Candidate interview status update:  %CandidateFirstName% %CandidateLastName% for %VacancyName% - %EventName%',
     '<p>Hi %SubscriberFirstName%,<br /><br />This is to inform you of the results of a recent interview for %CandidateFirstName% %CandidateLastName%<br /><ul><li>Candidate Name: %CandidateFirstName% %CandidateLastName%</li><li>Interviewers: %InterviewerNames%</li><li>Interview Status: %EventResult%</li><li>Notes:<br />%EventNotes%</li></ul>You were sent this email as you are a %UserRole% for this position.<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, TYPE, user_role_id) VALUES
    (@email_media, NULL, 'UserRole', @event_manager_user_role);
SET @subscriber_id := LAST_INSERT_ID();

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

-- Candidate One-On-One Interview Result Notification
INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`) VALUES
    ('Candidate One-On-One Interview Result Notification', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
     '{"email_processor":"CandidateEmailProcessor", "extra_roles":["HiringManager", "EventManager"]}', @one_on_one_interview_workflow_id, @action_update_marks,@recruitment_module);
SET @notification_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id) SELECT
                                        @notification_id, id FROM ohrm_workflow_state_machine WHERE `workflow` = @one_on_one_interview_workflow_id AND `action` = @action_update_marks;
SET @last_subscription_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',
     'Candidate One-On-One Interview Result Notification sent to Hiring Manager',
     'Candidate interview status update:  %CandidateFirstName% %CandidateLastName% for %VacancyName% - %EventName%',
     '<p>Hi %SubscriberFirstName%,<br /><br />This is to inform you of the results of a recent interview for %CandidateFirstName% %CandidateLastName%<br /><ul><li>Candidate Name: %CandidateFirstName% %CandidateLastName%</li><li>Interviewers: %InterviewerNames%</li><li>Interview Status: %EventResult%</li><li>Notes:<br />%EventNotes%</li></ul>You were sent this email as you are a %UserRole% for this position.<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, TYPE, user_role_id) VALUES
    (@email_media, NULL, 'UserRole', @hiring_manager_user_role);
SET @subscriber_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
    (@notification_id, @email_media, 'en_US',
     'Candidate One-On-One Interview Result Notification sent to Event Manager',
     'Candidate interview status update:  %CandidateFirstName% %CandidateLastName% for %VacancyName% - %EventName%',
     '<p>Hi %SubscriberFirstName%,<br /><br />This is to inform you of the results of a recent interview for %CandidateFirstName% %CandidateLastName%<br /><ul><li>Candidate Name: %CandidateFirstName% %CandidateLastName%</li><li>Interviewers: %InterviewerNames%</li><li>Interview Status: %EventResult%</li><li>Notes:<br />%EventNotes%</li></ul>You were sent this email as you are a %UserRole% for this position.<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, TYPE, user_role_id) VALUES
    (@email_media, NULL, 'UserRole', @event_manager_user_role);
SET @subscriber_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription`(subscription_id, subscriber_id, template_id) SELECT

                                                                                                         s.id, @subscriber_id, @template_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;

-- Candidate Test Result Notification
INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`) VALUES
    ('Candidate Test Result Notification', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
     '{"email_processor":"CandidateEmailProcessor", "extra_roles":["HiringManager", "EventManager"]}', @test_workflow_id, @action_update_marks,@recruitment_module);
SET @notification_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id) SELECT
                                        @notification_id, id FROM ohrm_workflow_state_machine WHERE `workflow` = @test_workflow_id AND `action` = @action_update_marks;
SET @last_subscription_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',
     'Candidate Test Result Notification sent to Hiring Manager',
     'Candidate test status update:  %CandidateFirstName% %CandidateLastName% for  %VacancyName% - %EventName%',
     '<p>Hi %SubscriberFirstName%,<br /><br />This is to inform you of the results of a recent test for %CandidateFirstName% %CandidateLastName%<br /><ul><li>Candidate Name: %CandidateFirstName% %CandidateLastName%</li><li>Test Coordinators: %TestCoordinatorNames%</li><li>Test Status: %EventResult%</li><li>Notes:<br />%EventNotes%</li></ul>You were sent this email as you are a %UserRole% for this position.<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, TYPE, user_role_id) VALUES
    (@email_media, NULL, 'UserRole', @hiring_manager_user_role);
SET @subscriber_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
    (@notification_id, @email_media, 'en_US',
     'Candidate Test Result Notification sent to Event Manager',
     'Candidate test status update:  %CandidateFirstName% %CandidateLastName% for  %VacancyName% - %EventName%',
     '<p>Hi %SubscriberFirstName%,<br /><br />This is to inform you of the results of a recent test for %CandidateFirstName% %CandidateLastName%<br /><ul><li>Candidate Name: %CandidateFirstName% %CandidateLastName%</li><li>Test Coordinators: %TestCoordinatorNames%</li><li>Test Status: %EventResult%</li><li>Notes:<br />%EventNotes%</li></ul>You were sent this email as you are a %UserRole% for this position.<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, TYPE, user_role_id) VALUES
    (@email_media, NULL, 'UserRole', @event_manager_user_role);
SET @subscriber_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription`(subscription_id, subscriber_id, template_id) SELECT

                                                                                                         s.id, @subscriber_id, @template_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;

-- candidate Schedule Interview email templates
-- Panel Interview Scheduled

INSERT INTO `ohrm_notification`
    (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`) VALUES
    ('Panel Interview Scheduled', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
    '{"email_processor":"CandidateEmailProcessor", "extra_roles":["HiringManager", "EventManager"]}', @panel_interview_workflow_id, @action_schedule_interview,@recruitment_module);
SET @notification_id := LAST_INSERT_ID();

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


INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
'Panel interview scheduled notification sent to Hiring Manager',
'Notification: %OrganizationName% %VacancyName% - %EventName% on Scheduled',
'<p>Hi %EmployeeFirstName%,<br /><br />%CandidateFirstName% %CandidateLastName% has been selected for %EventName% on %ScheduledDateTime%<br /><br />You were sent this mail as you are a %UserRole%.<br /><br />Thank you.<br />This is an automated notification.</p>');


INSERT INTO `ohrm_notification_subscription`
    (notification_id, workflow_item_id) SELECT
    @notification_id, id FROM ohrm_workflow_state_machine WHERE `workflow` = @panel_interview_workflow_id AND `action` = @action_schedule_interview;

SET @panel_interview_scheduled_subscription_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',
'Panel interview scheduled notification sent to Event Manager',
'Notification: %OrganizationName% %VacancyName% - %EventName% on Scheduled',
'<p>Hi %EmployeeFirstName%,<br /><br />%CandidateFirstName% %CandidateLastName% has been selected for %EventName% on %ScheduledDateTime%.<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @template_id := LAST_INSERT_ID();

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

SET @subscriber_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
'Panel interview scheduled notification sent to Hiring Manager',
'Notification: %OrganizationName% %VacancyName% - %EventName% on Scheduled',
'<p>Hi %EmployeeFirstName%,<br /><br />%CandidateFirstName% %CandidateLastName% has been selected for %EventName% on %ScheduledDateTime%.<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @template_id := LAST_INSERT_ID();

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

SET @subscriber_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
'Panel interview scheduled notification sent to Candidate',
'Notification: %OrganizationName% %VacancyName% - %EventName% Scheduled',
'<p>Dear %CandidateFirstName% %CandidateLastName%,<br /><br />You have been selected for %EventName% on %ScheduledDateTime%<br />We expect your presence on time.<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @template_id := LAST_INSERT_ID();

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

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 >= @panel_interview_scheduled_subscription_id;


-- Phone Interview Scheduled

INSERT INTO `ohrm_notification`
    (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`) VALUES
    ('Phone Interview Scheduled', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
    '{"email_processor":"CandidateEmailProcessor", "extra_roles":["HiringManager", "EventManager"]}', @phone_interview_workflow_id, @action_schedule_interview,@recruitment_module);
SET @notification_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
'Scheduled phone interview sent to Hiring Manager',
'Notification: %OrganizationName% %VacancyName% - %EventName% on Scheduled',
'<p>Hi %EmployeeFirstName%,<br /><br />%CandidateFirstName% %CandidateLastName% has been selected for %EventName% on %ScheduledDateTime%<br /><br />You were sent this mail as you are a %UserRole%.<br /><br />Thank you.<br />This is an automated notification.</p>');

INSERT INTO `ohrm_notification_subscription`
    (notification_id, workflow_item_id) SELECT
    @notification_id, id FROM ohrm_workflow_state_machine WHERE `workflow` = @phone_interview_workflow_id AND `action` = @action_schedule_interview;

SET @phone_interview_scheduled_subscription_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',
'Phone interview scheduled notification sent to Event Manager',
'Notification: %OrganizationName% %VacancyName% - %EventName% on Scheduled',
'<p>Hi %EmployeeFirstName%,<br /><br />%CandidateFirstName% %CandidateLastName% has been selected for %EventName% on %ScheduledDateTime%.<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @template_id := LAST_INSERT_ID();

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

SET @subscriber_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
'Phone interview scheduled notification sent to Hiring Manager',
'Notification: %OrganizationName% %VacancyName% - %EventName% on Scheduled',
'<p>Hi %EmployeeFirstName%,<br /><br />%CandidateFirstName% %CandidateLastName% has been selected for %EventName% on %ScheduledDateTime%.<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @template_id := LAST_INSERT_ID();

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

SET @subscriber_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
'Phone interview scheduled notification sent to Candidate',
'Notification: %OrganizationName% %VacancyName% - %EventName% Scheduled',
'<p>Dear %CandidateFirstName% %CandidateLastName%,<br /><br />You have been selected for %EventName% on %ScheduledDateTime%<br />We expect your presence on time.<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @template_id := LAST_INSERT_ID();

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

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 >= @phone_interview_scheduled_subscription_id;


-- One-On-One Interview Scheduled
INSERT INTO `ohrm_notification`
    (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`) VALUES
    ('One-On-One Interview Scheduled', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
    '{"email_processor":"CandidateEmailProcessor", "extra_roles":["HiringManager", "EventManager"]}', @one_on_one_interview_workflow_id, @action_schedule_interview,@recruitment_module);
SET @notification_id := LAST_INSERT_ID();

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


INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
'One-on-one interview notification sent to Hiring Manager',
'Notification: %OrganizationName% %VacancyName% - %EventName% on Scheduled',
'<p>Hi %EmployeeFirstName%,<br /><br />%CandidateFirstName% %CandidateLastName% has been selected for %EventName% on %ScheduledDateTime%<br /><br />You were sent this mail as you are a %UserRole%.<br /><br />Thank you.<br />This is an automated notification.</p>');


INSERT INTO `ohrm_notification_subscription`
    (notification_id, workflow_item_id) SELECT
    @notification_id, id FROM ohrm_workflow_state_machine WHERE `workflow` = @one_on_one_interview_workflow_id AND `action` = @action_schedule_interview;

SET @last_subscription_id := LAST_INSERT_ID();
SET @one_on_one_interview_scheduled_subscription_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',
'One-on-one interview scheduled notification sent to Event Manager',
'Notification: %OrganizationName% %VacancyName% - %EventName% on Scheduled',
'<p>Hi %EmployeeFirstName%,<br /><br />%CandidateFirstName% %CandidateLastName% has been selected for %EventName% on %ScheduledDateTime%.<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @template_id := LAST_INSERT_ID();

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

SET @subscriber_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
'One-on-one interview scheduled by admin, notification sent to Hiring Manager',
'Notification: %OrganizationName% %VacancyName% - %EventName% on Scheduled',
'<p>Hi %EmployeeFirstName%,<br /><br />%CandidateFirstName% %CandidateLastName% has been selected for %EventName% on %ScheduledDateTime%.<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @template_id := LAST_INSERT_ID();

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

SET @subscriber_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
'One-on-one interview scheduled notification sent to Candidate',
'Notification: %OrganizationName% %VacancyName% - %EventName% Scheduled',
'<p>Dear %CandidateFirstName% %CandidateLastName%,<br /><br />You have been selected for %EventName% on %ScheduledDateTime%<br />We expect your presence on time.<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @template_id := LAST_INSERT_ID();

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

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 >= @one_on_one_interview_scheduled_subscription_id;


-- Test Scheduled
INSERT INTO `ohrm_notification`
    (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`) VALUES
    ('Test Scheduled', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
    '{"email_processor":"CandidateEmailProcessor", "extra_roles":["HiringManager", "EventManager"]}', @test_workflow_id, @action_schedule_interview,@recruitment_module);
SET @notification_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
'Test scheduled notification sent to Hiring Manager',
'Notification: %OrganizationName% %VacancyName% - %EventName% on Scheduled',
'<p>Hi %EmployeeFirstName%,<br /><br />%CandidateFirstName% %CandidateLastName% has been selected for %EventName% on %ScheduledDateTime%<br /><br />You were sent this mail as you are a %UserRole%.<br /><br />Thank you.<br />This is an automated notification.</p>');

INSERT INTO `ohrm_notification_subscription`
    (notification_id, workflow_item_id) SELECT
    @notification_id, id FROM ohrm_workflow_state_machine WHERE `workflow` = @test_workflow_id AND `action` = @action_schedule_interview;

SET @test_scheduled_subscription_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',
'Test scheduled notification sent to Event Manager',
'Notification: %OrganizationName% %VacancyName% - %EventName% on Scheduled',
'<p>Hi %EmployeeFirstName%,<br /><br />%CandidateFirstName% %CandidateLastName% has been selected for %EventName% on %ScheduledDateTime%.<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @template_id := LAST_INSERT_ID();

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

SET @subscriber_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
'Test scheduled by admin notification sent to Hiring Manager',
'Notification: %OrganizationName% %VacancyName% - %EventName% on Scheduled',
'<p>Hi %EmployeeFirstName%,<br /><br />%CandidateFirstName% %CandidateLastName% has been selected for %EventName% on %ScheduledDateTime%.<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @template_id := LAST_INSERT_ID();

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

SET @subscriber_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
'Test scheduled notification sent to Candidate',
'Notification: %OrganizationName% %VacancyName% - %EventName% Scheduled',
'<p>Dear %CandidateFirstName% %CandidateLastName%,<br /><br />You have been selected for %EventName% on %ScheduledDateTime%<br />We expect your presence on time.<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @template_id := LAST_INSERT_ID();

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

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 >= @test_scheduled_subscription_id;



-- Candidate Shortlisted
INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`) VALUES
    ('Candidate Shortlisted', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
     '{"email_processor":"CandidateEmailProcessor", "extra_roles":["HiringManager", "EventManager"]}', @recruitment_workflow_id, @action_shortlist,@recruitment_module);
SET @notification_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id) SELECT
                                        @notification_id, id FROM ohrm_workflow_state_machine WHERE `workflow` = @recruitment_workflow_id AND `action` = @action_shortlist;
SET @last_subscription_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',
     'Candidate Shortlisted notification sent to Hiring Manager',
     'Candidate Shortlisted: %CandidateFirstName% %CandidateLastName% for %VacancyName%',
     '<p>Hi %SubscriberFirstName%,<br /><br />%CandidateFirstName% %CandidateLastName% has been shortlisted by %ActionPerformer% for the %VacancyName% vacancy.<br /><br />You were sent this email as you are a %UserRole% for this position.<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, TYPE, user_role_id) VALUES
    (@email_media, NULL, 'UserRole', @hiring_manager_user_role);
SET @subscriber_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
    (@notification_id, @email_media, 'en_US',
     'Candidate Shortlisted notification sent to Event Manager',
     'Candidate Shortlisted: %CandidateFirstName% %CandidateLastName% for  %VacancyName%',
     '<p>Hi %SubscriberFirstName%,<br /><br />%CandidateFirstName% %CandidateLastName% has been shortlisted by %ActionPerformer% for the %VacancyName% vacancy.<br /><br />You were sent this email as you are a %UserRole% for this position.<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, TYPE, user_role_id) VALUES
    (@email_media, NULL, 'UserRole', @event_manager_user_role);
SET @subscriber_id := LAST_INSERT_ID();

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

-- Candidate Moved to Phone Interview Stage
INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`) VALUES
    ('Candidate Moved to Phone Interview Stage', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
     '{"email_processor":"CandidateEmailProcessor", "extra_roles":["HiringManager", "EventManager"]}', @recruitment_workflow_id, @action_phone_interview,@recruitment_module);
SET @notification_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id) SELECT
                                        @notification_id, id FROM ohrm_workflow_state_machine WHERE `workflow` = @recruitment_workflow_id AND `action` = @action_phone_interview;
SET @last_subscription_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',
     'Candidate Moved to Phone Interview Stage notification sent to Hiring Manager',
     'Candidate moved to %EventName% Stage: %CandidateFirstName% %CandidateLastName% for %VacancyName%',
     '<p>Hi %SubscriberFirstName%,<br /><br />%CandidateFirstName% %CandidateLastName% has been moved to the %EventName% stage by %ActionPerformer% for the %VacancyName% vacancy.<br /><br />You were sent this email as you are a %UserRole% for this position.<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, TYPE, user_role_id) VALUES
    (@email_media, NULL, 'UserRole', @hiring_manager_user_role);
SET @subscriber_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
    (@notification_id, @email_media, 'en_US',
     'Candidate Moved to Phone Interview Stage notification sent to Event Manager',
     'Candidate moved to %EventName% Stage: %CandidateFirstName% %CandidateLastName% for %VacancyName%',
     '<p>Hi %SubscriberFirstName%,<br /><br />%CandidateFirstName% %CandidateLastName% has been moved to the %EventName% stage by %ActionPerformer% for the %VacancyName% vacancy.<br /><br />You were sent this email as you are a %UserRole% for this position.<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, TYPE, user_role_id) VALUES
    (@email_media, NULL, 'UserRole', @event_manager_user_role);
SET @subscriber_id := LAST_INSERT_ID();

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

-- Candidate Moved to Panel Interview Stage
INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`) VALUES
    ('Candidate Moved to Panel Interview Stage', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
     '{"email_processor":"CandidateEmailProcessor", "extra_roles":["HiringManager", "EventManager"]}', @recruitment_workflow_id, @action_panel_interview,@recruitment_module);
SET @notification_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id) SELECT
                                        @notification_id, id FROM ohrm_workflow_state_machine WHERE `workflow` = @recruitment_workflow_id AND `action` = @action_panel_interview;
SET @last_subscription_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',
     'Candidate Moved to Panel Interview Stage notification sent to Hiring Manager',
     'Candidate moved to %EventName% Stage: %CandidateFirstName% %CandidateLastName% for %VacancyName%',
     '<p>Hi %SubscriberFirstName%,<br /><br />%CandidateFirstName% %CandidateLastName% has been moved to the %EventName% stage by %ActionPerformer% for the %VacancyName% vacancy.<br /><br />You were sent this email as you are a %UserRole% for this position.<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, TYPE, user_role_id) VALUES
    (@email_media, NULL, 'UserRole', @hiring_manager_user_role);
SET @subscriber_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
    (@notification_id, @email_media, 'en_US',
     'Candidate Moved to Panel Interview Stage notification sent to Event Manager',
     'Candidate moved to %EventName% Stage: %CandidateFirstName% %CandidateLastName% for %VacancyName%',
     '<p>Hi %SubscriberFirstName%,<br /><br />%CandidateFirstName% %CandidateLastName% has been moved to the %EventName% stage by %ActionPerformer% for the %VacancyName% vacancy.<br /><br />You were sent this email as you are a %UserRole% for this position.<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, TYPE, user_role_id) VALUES
    (@email_media, NULL, 'UserRole', @event_manager_user_role);
SET @subscriber_id := LAST_INSERT_ID();

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

-- Candidate Moved to One-On-One Interview Stage
INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`) VALUES
    ('Candidate Moved to One-On-One Interview Stage', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
     '{"email_processor":"CandidateEmailProcessor", "extra_roles":["HiringManager", "EventManager"]}', @recruitment_workflow_id, @action_one_on_one_interview,@recruitment_module);
SET @notification_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id) SELECT
                                        @notification_id, id FROM ohrm_workflow_state_machine WHERE `workflow` = @recruitment_workflow_id AND `action` = @action_one_on_one_interview;
SET @last_subscription_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',
     'Candidate Moved to One-On-One Interview Stage notification sent to Hiring Manager',
     'Candidate moved to %EventName% Stage: %CandidateFirstName% %CandidateLastName% for %VacancyName%',
     '<p>Hi %SubscriberFirstName%,<br /><br />%CandidateFirstName% %CandidateLastName% has been moved to the %EventName% stage by %ActionPerformer% for the %VacancyName% vacancy.<br /><br />You were sent this email as you are a %UserRole% for this position.<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, TYPE, user_role_id) VALUES
    (@email_media, NULL, 'UserRole', @hiring_manager_user_role);
SET @subscriber_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
    (@notification_id, @email_media, 'en_US',
     'Candidate Moved to One-On-One Interview Stage notification sent to Event Manager',
     'Candidate moved to %EventName% Stage: %CandidateFirstName% %CandidateLastName% for %VacancyName%',
     '<p>Hi %SubscriberFirstName%,<br /><br />%CandidateFirstName% %CandidateLastName% has been moved to the %EventName% stage by %ActionPerformer% for the %VacancyName% vacancy.<br /><br />You were sent this email as you are a %UserRole% for this position.<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, TYPE, user_role_id) VALUES
    (@email_media, NULL, 'UserRole', @event_manager_user_role);
SET @subscriber_id := LAST_INSERT_ID();

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

-- Candidate Moved to Test Stage
INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`) VALUES
    ('Candidate Moved to Test Stage', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
     '{"email_processor":"CandidateEmailProcessor", "extra_roles":["HiringManager", "EventManager"]}', @recruitment_workflow_id, @action_test,@recruitment_module);
SET @notification_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id) SELECT
                                        @notification_id, id FROM ohrm_workflow_state_machine WHERE `workflow` = @recruitment_workflow_id AND `action` = @action_test;
SET @last_subscription_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',
     'Candidate Moved to Test Stage notification sent to Hiring Manager',
     'Candidate moved to %EventName% Stage: %CandidateFirstName% %CandidateLastName% for %VacancyName%',
     '<p>Hi %SubscriberFirstName%,<br /><br />%CandidateFirstName% %CandidateLastName% has been moved to the %EventName% stage by %ActionPerformer% for the %VacancyName% vacancy.<br /><br />You were sent this email as you are a %UserRole% for this position.<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, TYPE, user_role_id) VALUES
    (@email_media, NULL, 'UserRole', @hiring_manager_user_role);
SET @subscriber_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
    (@notification_id, @email_media, 'en_US',
     'Candidate Moved to Test Stage notification sent to Event Manager',
     'Candidate moved to %EventName% Stage: %CandidateFirstName% %CandidateLastName% for %VacancyName%',
     '<p>Hi %SubscriberFirstName%,<br /><br />%CandidateFirstName% %CandidateLastName% has been moved to the %EventName% stage by %ActionPerformer% for the %VacancyName% vacancy.<br /><br />You were sent this email as you are a %UserRole% for this position.<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, TYPE, user_role_id) VALUES
    (@email_media, NULL, 'UserRole', @event_manager_user_role);
SET @subscriber_id := LAST_INSERT_ID();

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

-- Candidate Offered Job
INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`) VALUES
    ('Candidate Offered Job', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
     '{"email_processor":"CandidateEmailProcessor", "extra_roles":["HiringManager", "EventManager"]}', @recruitment_workflow_id, @action_job_offer,@recruitment_module);
SET @notification_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id) SELECT
                                        @notification_id, id FROM ohrm_workflow_state_machine WHERE `workflow` = @recruitment_workflow_id AND `action` = @action_job_offer;
SET @last_subscription_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',
     'Candidate Offered Job notification sent to Hiring Manager',
     'Job Offer Extended: %CandidateFirstName% %CandidateLastName% for %VacancyJobTitle% Position',
     '<p>Hi %SubscriberFirstName%,<br /><br />A job offer has been extended to %CandidateFirstName% %CandidateLastName% for the %VacancyJobTitle% position.<br /><br />You were sent this email as you are a %UserRole% for this position.<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, TYPE, user_role_id) VALUES
    (@email_media, NULL, 'UserRole', @hiring_manager_user_role);
SET @subscriber_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
    (@notification_id, @email_media, 'en_US',
     'Candidate Offered Job notification sent to Event Manager',
     'Job Offer Extended: %CandidateFirstName% %CandidateLastName% for %VacancyJobTitle% Position',
     '<p>Hi %SubscriberFirstName%,<br /><br />A job offer has been extended to %CandidateFirstName% %CandidateLastName% for the %VacancyJobTitle% position.<br /><br />You were sent this email as you are a %UserRole% for this position.<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, TYPE, user_role_id) VALUES
    (@email_media, NULL, 'UserRole', @event_manager_user_role);
SET @subscriber_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
    (@notification_id, @email_media, 'en_US',
     'Candidate Offered Job notification sent to Candidate',
     'Congratulations %CandidateFirstName% %CandidateLastName%! You have been offered the %VacancyJobTitle% position',
     '<p>Hi %CandidateFirstName%,<br /><br />We are excited to inform you that you have been offered the position of %VacancyJobTitle% at %OrganizationName%.<br /><br />Please take some time to review the attached offer letter and contact us with any questions.<br /><br />We look forward to bringing you onboard!<br /><br />Thank you for your interest in being part of our organization.<br /><br />This is an automated notification.</p>');
SET @template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, TYPE, name) VALUES
    (@email_media, '{"prevent_delete":true}', 'Custom', 'Candidate');
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;

-- Candidate Hired
INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`) VALUES
    ('Candidate Hired', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
     '{"email_processor":"CandidateEmailProcessor", "extra_roles":["HiringManager", "EventManager"]}', @recruitment_workflow_id, @action_hire,@recruitment_module);
SET @notification_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id, hidden) SELECT
                                                @notification_id, id, 1 FROM ohrm_workflow_state_machine WHERE `workflow` = @recruitment_workflow_id AND `action` = @action_hire;
SET @last_subscription_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',
     'Candidate Hired notification sent to Hiring Manager',
     'New Hire Notification: %CandidateFirstName% %CandidateLastName% for %VacancyJobTitle% Position',
     '<p>Hi %SubscriberFirstName%,<br /><br />The Candidate %CandidateFirstName% %CandidateLastName% has been hired for the %VacancyJobTitle% position.<br /><ul><li>Employee Name: %CandidateFirstName% %CandidateLastName%</li><li>Department: %VacancySubunit%</li><li>Location: %VacancyLocation%</li><li>Job Position: %VacancyJobTitle%</li><li>Supervisor: %NewHireSupervisor%</li><li>Start Date: %NewHireJoinDate%</li></ul>You were sent this email as you are a %UserRole% for this position.<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, TYPE, user_role_id) VALUES
    (@email_media, NULL, 'UserRole', @hiring_manager_user_role);
SET @subscriber_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
    (@notification_id, @email_media, 'en_US',
     'Candidate Hired notification sent to Event Manager',
     'New Hire Notification: %CandidateFirstName% %CandidateLastName% for %VacancyJobTitle% Position',
     '<p>Hi %SubscriberFirstName%,<br /><br />The Candidate %CandidateFirstName% %CandidateLastName% has been hired for the %VacancyJobTitle% position.<br /><ul><li>Employee Name: %CandidateFirstName% %CandidateLastName%</li><li>Department: %VacancySubunit%</li><li>Location: %VacancyLocation%</li><li>Job Position: %VacancyJobTitle%</li><li>Supervisor: %NewHireSupervisor%</li><li>Start Date: %NewHireJoinDate%</li></ul>You were sent this email as you are a %UserRole% for this position.<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, TYPE, user_role_id) VALUES
    (@email_media, NULL, 'UserRole', @event_manager_user_role);
SET @subscriber_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
    (@notification_id, @email_media, 'en_US',
     'Candidate Hired notification sent to Candidate',
     'Welcome Aboard %CandidateFirstName% %CandidateLastName%! Your New Role as %VacancyJobTitle% at %OrganizationName% awaits',
     '<p>Hi %CandidateFirstName%,<br /><br />Congratulations! We are delighted to officially welcome you to the %OrganizationName% team in the position of %VacancyJobTitle%.<br /><br />Here are the details of your new role:<ul><li>Department: %VacancySubunit%</li><li>Location: %VacancyLocation%</li><li>Job Position: %VacancyJobTitle%</li><li>Start Date: %NewHireJoinDate%</li></ul>We are excited to have you onboard and look forward to the great things we can accomplish together. Please feel free to reach out if you have any questions before your start date.<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, TYPE, name) VALUES
    (@email_media, '{"prevent_delete":true}', 'Custom', 'Candidate');
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;

-- Candidate Moved to Preboarding Stage
INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`) VALUES
    ('Candidate Moved to Preboarding Stage', 0, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
     '{"email_processor":"CandidateEmailProcessor", "extra_roles":["HiringManager", "EventManager"]}', @recruitment_workflow_id, @action_preboarding,@recruitment_module);
SET @notification_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id) SELECT
                                        @notification_id, id FROM ohrm_workflow_state_machine WHERE `workflow` = @recruitment_workflow_id AND `action` = @action_preboarding;
SET @last_subscription_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',
     'Candidate Moved to Preboarding Stage notification sent to Hiring Manager',
     'Candidate moved to %EventName% Stage: %CandidateFirstName% %CandidateLastName% for %VacancyName%',
     '<p>Hi %SubscriberFirstName%,<br /><br />%CandidateFirstName% %CandidateLastName% has been moved to the %EventName% stage by %ActionPerformer% for the %VacancyName% vacancy.<br /><br />You were sent this email as you are a %UserRole% for this position.<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, TYPE, user_role_id) VALUES
    (@email_media, NULL, 'UserRole', @hiring_manager_user_role);
SET @subscriber_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
    (@notification_id, @email_media, 'en_US',
     'Candidate Moved to Preboarding Stage notification sent to Event Manager',
     'Candidate moved to %EventName% Stage: %CandidateFirstName% %CandidateLastName% for %VacancyName%',
     '<p>Hi %SubscriberFirstName%,<br /><br />%CandidateFirstName% %CandidateLastName% has been moved to the %EventName% stage by %ActionPerformer% for the %VacancyName% vacancy.<br /><br />You were sent this email as you are a %UserRole% for this position.<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, TYPE, user_role_id) VALUES
    (@email_media, NULL, 'UserRole', @event_manager_user_role);
SET @subscriber_id := LAST_INSERT_ID();

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

-- candidte Event Reschedule email templates

INSERT INTO `ohrm_notification`
    (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`) VALUES
    ('Panel Interview Rescheduled', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
    '{"email_processor":"CandidateEmailProcessor", "extra_roles":["HiringManager", "EventManager"]}', @panel_interview_workflow_id, @action_reschedule_interview,@recruitment_module);
SET @notification_id := LAST_INSERT_ID();
SET @interview_schedule_notification := @notification_id;

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

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, TYPE, user_role_id) VALUES
    (@email_media, NULL, 'UserRole', @event_manager_user_role);
SET @em_subscriber_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, TYPE, user_role_id) VALUES
    (@email_media, NULL, 'UserRole', @hiring_manager_user_role);
SET @hm_subscriber_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, TYPE, name) VALUES
    (@email_media, '{"prevent_delete":true}', 'Custom', 'Candidate');
SET @candidate_subscriber_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',
'Panel interview rescheduled notification sent to Event Manager',
'Notification: %OrganizationName% %VacancyName% - %EventName% on Re-scheduled',
'<p>Hi %EmployeeFirstName%,<br /><br />%CandidateFirstName% %CandidateLastName%''s %EventName% is rescheduled for %ScheduledDateTime%<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @em_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',
'Panel interview rescheduled notification for already rescheduled interviews sent to Hiring Manager',
'Notification: %OrganizationName% %VacancyName% - %EventName% on Re-scheduled',
'<p>Hi %EmployeeFirstName%,<br /><br />%CandidateFirstName% %CandidateLastName%''s %EventName% is rescheduled for %ScheduledDateTime%<br /><br />You were sent this mail as you are a %UserRole%.<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @hm_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',
'Panel interview rescheduled notification sent to Candidate',
'Notification: %OrganizationName% %VacancyName% - %EventName% Re-scheduled',
'<p>Dear %CandidateFirstName% %CandidateLastName%,<br /><br />%EventName% rescheduled for %ScheduledDateTime%<br />We expect your presence on time.<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @candidate_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` = @panel_interview_workflow_id AND `state` = @init_state_reschedule AND `action` = @action_reschedule_interview;

SET @last_subscription_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription`(subscription_id, subscriber_id, template_id) SELECT
    s.id, @em_subscriber_id, @em_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, @hm_subscriber_id, @hm_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, @candidate_subscriber_id, @candidate_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',
'Panel interview rescheduled notification sent to Hiring Manager',
'Notification: %OrganizationName% %VacancyName% - %EventName% on Re-scheduled',
'<p>Hi %EmployeeFirstName%,<br /><br />%CandidateFirstName% %CandidateLastName%''s %EventName% is rescheduled for %ScheduledDateTime%.<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @hm_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` = @panel_interview_workflow_id AND `state` = @init_state_schedule AND `action` = @action_reschedule_interview;

SET @last_subscription_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription`(subscription_id, subscriber_id, template_id) SELECT
    s.id, @em_subscriber_id, @em_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, @hm_subscriber_id, @hm_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, @candidate_subscriber_id, @candidate_template_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;

-- One-On-One Interview Rescheduled
INSERT INTO `ohrm_notification`
    (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`) VALUES
    ('One-On-One Interview Rescheduled', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
    '{"email_processor":"CandidateEmailProcessor", "extra_roles":["HiringManager", "EventManager"]}', @one_on_one_interview_workflow_id, @action_reschedule_interview,@recruitment_module);
SET @notification_id := LAST_INSERT_ID();
SET @interview_schedule_notification := @notification_id;

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

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, TYPE, user_role_id) VALUES
    (@email_media, NULL, 'UserRole', @event_manager_user_role);
SET @em_subscriber_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, TYPE, user_role_id) VALUES
    (@email_media, NULL, 'UserRole', @hiring_manager_user_role);
SET @hm_subscriber_id := LAST_INSERT_ID();

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

SET @candidate_subscriber_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',
'One-on-one interview rescheduled notification sent to Event Manager',
'Notification: %OrganizationName% %VacancyName% - %EventName% on Re-scheduled',
'<p>Hi %EmployeeFirstName%,<br /><br />%CandidateFirstName% %CandidateLastName%''s %EventName% is rescheduled for %ScheduledDateTime%<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @em_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',
'One-on-one interview rescheduled notification for already rescheduled interviews sent to Hiring Manager',
'Notification: %OrganizationName% %VacancyName% - %EventName% on Re-scheduled',
'<p>Hi %EmployeeFirstName%,<br /><br />%CandidateFirstName% %CandidateLastName%''s %EventName% is rescheduled for %ScheduledDateTime%<br /><br />You were sent this mail as you are a %UserRole%.<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @hm_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',
'One-on-one interview rescheduled notification sent to Candidate',
'Notification: %OrganizationName% %VacancyName% - %EventName% Re-scheduled',
'<p>Dear %CandidateFirstName% %CandidateLastName%,<br /><br />%EventName% rescheduled for %ScheduledDateTime%<br />We expect your presence on time.<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @candidate_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` = @one_on_one_interview_workflow_id AND `state` = @init_state_reschedule AND `action` = @action_reschedule_interview;

SET @last_subscription_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription`(subscription_id, subscriber_id, template_id) SELECT
    s.id, @em_subscriber_id, @em_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, @hm_subscriber_id, @hm_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, @candidate_subscriber_id, @candidate_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',
'One-on-one interview rescheduled notification sent to Hiring Manager',
'Notification: %OrganizationName% %VacancyName% - %EventName% on Re-scheduled',
'<p>Hi %EmployeeFirstName%,<br /><br />%CandidateFirstName% %CandidateLastName%''s %EventName% is rescheduled for %ScheduledDateTime%.<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @hm_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` = @one_on_one_interview_workflow_id AND `state` = @init_state_schedule AND `action` = @action_reschedule_interview;

SET @last_subscription_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription`(subscription_id, subscriber_id, template_id) SELECT
    s.id, @em_subscriber_id, @em_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, @hm_subscriber_id, @hm_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, @candidate_subscriber_id, @candidate_template_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;

-- Phone Interview Rescheduled
INSERT INTO `ohrm_notification`
    (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`) VALUES
    ('Phone Interview Rescheduled', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
    '{"email_processor":"CandidateEmailProcessor", "extra_roles":["HiringManager", "EventManager"]}', @phone_interview_workflow_id, @action_reschedule_interview,@recruitment_module);
SET @notification_id := LAST_INSERT_ID();
SET @interview_schedule_notification := @notification_id;

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

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

SET @em_subscriber_id := LAST_INSERT_ID();

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

SET @hm_subscriber_id := LAST_INSERT_ID();

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

SET @candidate_subscriber_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',
'Phone interview rescheduled notification for already rescheduled interviews sent to Event Manager',
'Notification: %OrganizationName% %VacancyName% - %EventName% on Re-scheduled',
'<p>Hi %EmployeeFirstName%,<br /><br />%CandidateFirstName% %CandidateLastName%''s %EventName% is rescheduled for %ScheduledDateTime%<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @em_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',
'Phone interview rescheduled notification for already rescheduled interviews sent to Hiring Manager',
'Notification: %OrganizationName% %VacancyName% - %EventName% on Re-scheduled',
'<p>Hi %EmployeeFirstName%,<br /><br />%CandidateFirstName% %CandidateLastName%''s %EventName% is rescheduled for %ScheduledDateTime%<br /><br />You were sent this mail as you are a %UserRole%.<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @hm_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',
'Phone interview rescheduled notification sent to Candidate',
'Notification: %OrganizationName% %VacancyName% - %EventName% Re-scheduled',
'<p>Dear %CandidateFirstName% %CandidateLastName%,<br /><br />%EventName% rescheduled for %ScheduledDateTime%<br />We expect your presence on time.<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @candidate_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` = @phone_interview_workflow_id AND `state` =  @init_state_reschedule AND `action` = @action_reschedule_interview;

SET @last_subscription_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription`(subscription_id, subscriber_id, template_id) SELECT
    s.id, @em_subscriber_id, @em_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, @hm_subscriber_id, @hm_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, @candidate_subscriber_id, @candidate_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',
'Phone interview rescheduled notification sent to Hiring Manager',
'Notification: %OrganizationName% %VacancyName% - %EventName% on Re-scheduled',
'<p>Hi %EmployeeFirstName%,<br /><br />%CandidateFirstName% %CandidateLastName%''s %EventName% is rescheduled for %ScheduledDateTime%.<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @hm_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` = @phone_interview_workflow_id AND `state` =  @init_state_schedule AND `action` = @action_reschedule_interview;

SET @last_subscription_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription`(subscription_id, subscriber_id, template_id) SELECT
    s.id, @em_subscriber_id, @em_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, @hm_subscriber_id, @hm_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, @candidate_subscriber_id, @candidate_template_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;

-- Test Rescheduled
INSERT INTO `ohrm_notification`
    (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`) VALUES
    ('Test Rescheduled', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
    '{"email_processor":"CandidateEmailProcessor", "extra_roles":["HiringManager", "EventManager"]}', @test_workflow_id, @action_reschedule_interview,@recruitment_module);
SET @notification_id := LAST_INSERT_ID();
SET @interview_schedule_notification := @notification_id;

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

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, TYPE, user_role_id) VALUES
    (@email_media, NULL, 'UserRole', @event_manager_user_role);
SET @em_subscriber_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, TYPE, user_role_id) VALUES
    (@email_media, NULL, 'UserRole', @hiring_manager_user_role);
SET @hm_subscriber_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, TYPE, name) VALUES
    (@email_media, '{"prevent_delete":true}', 'Custom', 'Candidate');
SET @candidatesubscriber_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',
'Test rescheduled notification sent to Event Manager',
'Notification: %OrganizationName% %VacancyName% - %EventName% on Re-scheduled',
'<p>Hi %EmployeeFirstName%,<br /><br />%CandidateFirstName% %CandidateLastName%''s %EventName% is rescheduled for %ScheduledDateTime%<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @em_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',
'Test rescheduled notification for already rescheduled tests sent to Hiring Manager',
'Notification: %OrganizationName% %VacancyName% - %EventName% on Re-scheduled',
'<p>Hi %EmployeeFirstName%,<br /><br />%CandidateFirstName% %CandidateLastName%''s %EventName% is rescheduled for %ScheduledDateTime%<br /><br />You were sent this mail as you are a %UserRole%.<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @hm_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',
'Test rescheduled notification sent to Candidate',
'Notification: %OrganizationName% %VacancyName% - %EventName% Re-scheduled',
'<p>Dear %CandidateFirstName% %CandidateLastName%,<br /><br />%EventName% rescheduled for %ScheduledDateTime%<br />We expect your presence on time.<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @candidate_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` = @test_workflow_id AND `state` = @init_state_schedule AND `action` = @action_reschedule_interview;

SET @last_subscription_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription`(subscription_id, subscriber_id, template_id) SELECT
    s.id, @em_subscriber_id, @em_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, @hm_subscriber_id, @hm_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, @candidate_subscriber_id, @candidate_template_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;

INSERT INTO `ohrm_notification_subscription`
    (notification_id, workflow_item_id) SELECT
    @notification_id, id FROM ohrm_workflow_state_machine WHERE `workflow` = @test_workflow_id AND `state` = @init_state_reschedule AND `action` = @action_reschedule_interview;

SET @last_subscription_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription`(subscription_id, subscriber_id, template_id) SELECT
    s.id, @em_subscriber_id, @em_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, @hm_subscriber_id, @hm_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, @candidate_subscriber_id, @candidate_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',
'Test rescheduled notification sent to Hiring Manager',
'Notification: %OrganizationName% %VacancyName% - %EventName% on Re-scheduled',
'<p>Hi %EmployeeFirstName%,<br /><br />%CandidateFirstName% %CandidateLastName%''s %EventName% is rescheduled for %ScheduledDateTime%.<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @hm_template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@candidate_mark_interview_passed_email_id, 'en_US', null, 'Interviewer', 'Notification: %CandidateFirstName% %CandidateLastName% has passed the %InterviewType% interview for %VacancyName%',
'Hi %EmployeeFirstName%,

%CandidateFirstName% %CandidateLastName% has passed the interview, which was held on %ScheduledDate%
Interview type: %InterviewType%
Interviewers: %Interviewers%

You were sent this mail as you are an %UserRole%.

Thank you.
This is an automated notification. ');

INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@candidate_mark_interview_passed_email_id, 'en_US', null, 'HiringManager', 'Notification: %CandidateFirstName% %CandidateLastName% has passed the %InterviewType% interview for %VacancyName%',
'Hi %EmployeeFirstName%,

%CandidateFirstName% %CandidateLastName% has passed the interview, which was held on %ScheduledDate%
Interview type: %InterviewType%
Interviewers: %Interviewers%

You were sent this mail as you are a %UserRole%.

Thank you.
This is an automated notification. ');

INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@candidate_mark_interview_passed_email_id, 'en_US', null, 'Subscriber', 'Notification: %CandidateFirstName% %CandidateLastName% has passed the %InterviewType% interview for %VacancyName%',
'Hi %EmployeeFirstName%,

%CandidateFirstName% %CandidateLastName% has passed the interview, which was held on %ScheduledDate%
Interview type: %InterviewType%
Interviewers: %Interviewers%

You were sent this mail as you are a %UserRole%.

Thank you.
This is an automated notification. ');

-- candidate Interview Failed email templates

INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@candidate_mark_interview_failed_email_id, 'en_US', null, 'Interviewer', 'Notification: %CandidateFirstName% %CandidateLastName% has failed the %InterviewType% interview for %VacancyName%',
'Hi %EmployeeFirstName%,

%CandidateFirstName% %CandidateLastName% has failed the interview, which was held on %ScheduledDate%
Interview type: %InterviewType%
Interviewers: %Interviewers%

You were sent this mail as you are an %UserRole%.

Thank you.
This is an automated notification. ');

INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@candidate_mark_interview_failed_email_id, 'en_US', null, 'HiringManager', 'Notification: %CandidateFirstName% %CandidateLastName% has failed the %InterviewType% interview for %VacancyName%',
'Hi %EmployeeFirstName%,

%CandidateFirstName% %CandidateLastName% has failed the interview, which was held on %ScheduledDate%
Interview type: %InterviewType%
Interviewers: %Interviewers%

You were sent this mail as you are a %UserRole%.

Thank you.
This is an automated notification. ');

INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@candidate_mark_interview_failed_email_id, 'en_US', null, 'Subscriber', 'Notification: %CandidateFirstName% %CandidateLastName% has failed the %InterviewType% interview for %VacancyName%',
'Hi %EmployeeFirstName%,

%CandidateFirstName% %CandidateLastName% has failed the interview, which was held on %ScheduledDate%
Interview type: %InterviewType%
Interviewers: %Interviewers%

You were sent this mail as you are a %UserRole%.

Thank you.
This is an automated notification. ');


-- candidate job offer email templates

INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@candidate_offer_job_email_id, 'en_US', null, 'Regional Admin', 'Notification: %CandidateFirstName% %CandidateLastName% has been offered a job for %VacancyName%',
'Hi %EmployeeFirstName%,

%CandidateFirstName% %CandidateLastName% has been offered a job for %VacancyName%.

You were sent this mail as you are a %UserRole%.

Thank you.
This is an automated notification. ');

INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@candidate_offer_job_email_id, 'en_US', null, 'Admin', 'Notification: %CandidateFirstName% %CandidateLastName% has been offered a job for %VacancyName%',
'Hi %EmployeeFirstName%,

%CandidateFirstName% %CandidateLastName% has been offered a job for %VacancyName%.

You were sent this mail as you are a %UserRole%.

Thank you.
This is an automated notification. ');

INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@candidate_offer_job_email_id, 'en_US', null, 'HiringManager', 'Notification: %CandidateFirstName% %CandidateLastName% has been offered a job for %VacancyName%',
'Hi %EmployeeFirstName%,

%CandidateFirstName% %CandidateLastName% has been offered a job for %VacancyName%.

You were sent this mail as you are a %UserRole%.

Thank you.
This is an automated notification. ');

INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@candidate_offer_job_email_id, 'en_US', null, 'Subscriber', 'Notification: %CandidateFirstName% %CandidateLastName% has been offered a job for %VacancyName%',
'Hi %EmployeeFirstName%,

%CandidateFirstName% %CandidateLastName% has been offered a job for %VacancyName%.

You were sent this mail as you are a %UserRole%.

Thank you.
This is an automated notification. ');


-- candidate Decline offer email templates

INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@candidate_decline_offer_email_id, 'en_US', null, 'Regional Admin', 'Notification: %CandidateFirstName% %CandidateLastName% has declined the offer for %VacancyName%',
'Hi %EmployeeFirstName%,

%CandidateFirstName% %CandidateLastName% has declined the offer given for %VacancyName%.

You were sent this mail as you are a %UserRole%.

Thank you.
This is an automated notification. ');

INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@candidate_decline_offer_email_id, 'en_US', null, 'Admin', 'Notification: %CandidateFirstName% %CandidateLastName% has declined the offer for %VacancyName%',
'Hi %EmployeeFirstName%,

%CandidateFirstName% %CandidateLastName% has declined the offer given for %VacancyName%.

You were sent this mail as you are a %UserRole%.

Thank you.
This is an automated notification. ');

INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@candidate_decline_offer_email_id, 'en_US', null, 'HiringManager', 'Notification: %CandidateFirstName% %CandidateLastName% has declined the offer for %VacancyName%',
'Hi %EmployeeFirstName%,

%CandidateFirstName% %CandidateLastName% has declined the offer given for %VacancyName%.

You were sent this mail as you are a %UserRole%.

Thank you.
This is an automated notification. ');

INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@candidate_decline_offer_email_id, 'en_US', null, 'Subscriber', 'Notification: %CandidateFirstName% %CandidateLastName% has declined the offer for %VacancyName%',
'Hi %EmployeeFirstName%,

%CandidateFirstName% %CandidateLastName% has declined the offer given for %VacancyName%.

You were sent this mail as you are a %UserRole%.

Thank you.
This is an automated notification. ');


-- candidate hire email templates

INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@candidate_hire_email_id, 'en_US', null, 'Regional Admin', 'Notification: %CandidateFirstName% %CandidateLastName% has been hired for %VacancyName%',
'Hi %EmployeeFirstName%,

%CandidateFirstName% %CandidateLastName% has accepted the offer and officially hired for %VacancyName%.

You were sent this mail as you are a %UserRole%.

Thank you.
This is an automated notification. ');

INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@candidate_hire_email_id, 'en_US', null, 'Admin', 'Notification: %CandidateFirstName% %CandidateLastName% has been hired for %VacancyName%',
'Hi %EmployeeFirstName%,

%CandidateFirstName% %CandidateLastName% has accepted the offer and officially hired for %VacancyName%.

You were sent this mail as you are a %UserRole%.

Thank you.
This is an automated notification. ');

INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@candidate_hire_email_id, 'en_US', null, 'HiringManager', 'Notification: %CandidateFirstName% %CandidateLastName% has been hired for %VacancyName%',
'Hi %EmployeeFirstName%,

%CandidateFirstName% %CandidateLastName% has accepted the offer and officially hired for %VacancyName%.

You were sent this mail as you are a %UserRole%.

Thank you.
This is an automated notification. ');

INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@candidate_hire_email_id, 'en_US', null, 'Subscriber', 'Notification: %CandidateFirstName% %CandidateLastName% has been hired for %VacancyName%',
'Hi %EmployeeFirstName%,

%CandidateFirstName% %CandidateLastName% has accepted the offer and officially hired for %VacancyName%.

You were sent this mail as you are a %UserRole%.

Thank you.
This is an automated notification. ');

INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@candidate_hire_email_id, 'en_US', null, 'Candidate', 'Welcome: You are hired for %VacancyName%',
'Hi %CandidateFirstName%,

It is with pleasure that we confirm that you are selected for %VacancyName%. Please contact our HR Team for further details.
We are delighted to have you as a part of the team.
Welcome to the team.

Thank you.
This is an automated notification. ');

-- Candidate Rejected ---
INSERT INTO `ohrm_notification`
    (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`) VALUES
    ('Candidate Rejected', 0, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
    '{"email_processor":"CandidateEmailProcessor", "extra_roles":["HiringManager"]}', @recruitment_workflow_id, @action_reject,@recruitment_module);
SET @notification_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
'Candidate rejection notification sent to Subscriber',
'Application Rejection Notification of Candidate %CandidateFirstName% %CandidateLastName% for the vacancy %VacancyName%',
'<p>Hi %EmployeeFirstName%,<br /><br />Please note that %CandidateFirstName% %CandidateLastName%''s application for the vacancy %VacancyName% was not selected for further consideration.<br /><br />Thank you.<br />You are getting this email because you are a subscriber.<br />This is an automated notification.</p>');

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
'Candidate Rejected notification sent to Candidate',
'Application Rejection Notification for the vacancy %VacancyName%',
'<p>Hi %CandidateFirstName%,<br /><br />Thank you for your interest in joining %OrganizationName% team.<br />We wanted to let you know that although your resume and cover letter were very competitive, our hiring team reviewed your application and it was not selected for further consideration.<br /><br />We wish you all the best in your job search and hope we will have the chance to consider you for another role in the future.<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscription`
    (notification_id, workflow_item_id, hidden) SELECT
    @notification_id, id, 0 FROM ohrm_workflow_state_machine WHERE `workflow` = @recruitment_workflow_id AND `action` = @action_reject;
SET @last_subscription_id := LAST_INSERT_ID();

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

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;

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
    (@notification_id, @email_media, 'en_US',
     'Candidate Rejected notification sent to Hiring Manager',
     'Application Rejection Notification of Candidate %CandidateFirstName% %CandidateLastName% for the vacancy %VacancyName%',
     '<p>Hi %SubscriberFirstName%,<br /><br />Please note that %CandidateFirstName% %CandidateLastName%''s application for the vacancy %VacancyName% was not selected for further consideration.<br /><br />You were sent this email as you are a %UserRole% for this position.<br /><br />Thank you.<br />This is an automated notification.</p>');
SET @template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, TYPE, user_role_id) VALUES
    (@email_media, NULL, 'UserRole', @hiring_manager_user_role);
SET @subscriber_id := LAST_INSERT_ID();

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

SET @test_interview_schedule_notification := (SELECT id  FROM `ohrm_notification` WHERE `name` = 'Test Scheduled');
SET @test_interview_reschedule_notification := (SELECT id  FROM `ohrm_notification` WHERE `name` = 'Test Rescheduled');

SET @phone_interview_schedule_notification := (SELECT id  FROM `ohrm_notification` WHERE `name` = 'Phone Interview Scheduled');
SET @phone_interview_reschedule_notification := (SELECT id  FROM `ohrm_notification` WHERE `name` = 'Phone Interview Rescheduled');

SET @panal_interview_schedule_notification := (SELECT id  FROM `ohrm_notification` WHERE `name` = 'Panel Interview Scheduled');
SET @panal_interview_reschedule_notification := (SELECT id  FROM `ohrm_notification` WHERE `name` = 'Panel Interview Rescheduled');

SET @oneonone_interview_schedule_notification := (SELECT id  FROM `ohrm_notification` WHERE `name` = 'One-On-One Interview Scheduled');
SET @oneonone_interview_reschedule_notification := (SELECT id  FROM `ohrm_notification` WHERE `name` = 'One-On-One Interview Rescheduled');


INSERT INTO `ohrm_ics_calendar_invitation_processor` (`notification_id` ,`processor`, `is_enabled`, `calendar_method`, `status`, `opacity`) VALUES
(@test_interview_schedule_notification, 'RecruitmentInvitationProcessor', '1', 'REQUEST', 'CONFIRMED', 'OPAQUE'),
(@test_interview_reschedule_notification, 'RecruitmentInvitationProcessor', '1', 'REQUEST', 'CONFIRMED', 'OPAQUE'),
(@phone_interview_schedule_notification, 'RecruitmentInvitationProcessor', '1', 'REQUEST', 'CONFIRMED', 'OPAQUE'),
(@phone_interview_reschedule_notification, 'RecruitmentInvitationProcessor', '1', 'REQUEST', 'CONFIRMED', 'OPAQUE'),
(@panal_interview_schedule_notification, 'RecruitmentInvitationProcessor', '1', 'REQUEST', 'CONFIRMED', 'OPAQUE'),
(@panal_interview_reschedule_notification, 'RecruitmentInvitationProcessor', '1', 'REQUEST', 'CONFIRMED', 'OPAQUE'),
(@oneonone_interview_schedule_notification, 'RecruitmentInvitationProcessor', '1', 'REQUEST', 'CONFIRMED', 'OPAQUE'),
(@oneonone_interview_reschedule_notification, 'RecruitmentInvitationProcessor', '1', 'REQUEST', 'CONFIRMED', 'OPAQUE');


INSERT INTO `ohrm_ics_calendar_content_template` (`notification_id` ,`organizer` ,`recipient` ,`summary_text` ,`description_text`) VALUES
( @test_interview_schedule_notification, NULL, NULL, 'Test Scheduled for %vacancy%', 'Test scheduled for %vacancy% for %candidate% on %date%. Please be on time.'),
( @test_interview_reschedule_notification, NULL, NULL, 'Test Scheduled for %vacancy% Has Rcheduled', 'Test rescheduled for %vacancy% for %candidate% on %date%. Please be on time.'),
( @phone_interview_schedule_notification, NULL, NULL, 'Phone Interview Scheduled for %vacancy%', 'Phone Interview scheduled for %vacancy% for %candidate% on %date%. Please be on time.'),
( @phone_interview_reschedule_notification, NULL, NULL, 'Phone Interview Scheduled for %vacancy% Has Rcheduled', 'Phone Interview rescheduled for %vacancy% for %candidate% on %date%. Please be on time.'),
( @panal_interview_schedule_notification, NULL, NULL, 'Panel Interview Scheduled for %vacancy%', 'Panel Interview scheduled for %vacancy% for %candidate% on %date%. Please be on time.'),
( @panal_interview_reschedule_notification, NULL, NULL, 'Panel Interview Scheduled for %vacancy% Has Rcheduled', 'Panel Interview rescheduled for %vacancy% for %candidate% on %date%. Please be on time.'),
( @oneonone_interview_schedule_notification, NULL, NULL, 'One-On-One Interview Scheduled for %vacancy%', 'One-On-OneInterview scheduled for %vacancy% for %candidate% on %date%. Please be on time.'),
( @oneonone_interview_reschedule_notification, NULL, NULL, ' One-On-One Interview Scheduled for %vacancy% Has Rcheduled', 'One-On-One Interview rescheduled for %vacancy% for %candidate% on %date%. Please be on time.');

--
-- Notification to stage coordinator, hiring manager when candidate moves between stages
--
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 @recruitment_module := (SELECT `id` FROM `ohrm_module` WHERE  `name` ='recruitment');

INSERT INTO `ohrm_notification` (`name`, `is_enable`,`type_id`, `generator_class`, `module_id`, `hidden`)
VALUES ('Vacancy Workflow Change', 0, @action_notification_type, NULL, @recruitment_module, 1);

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` (`name`,`notification_id`,`media_id`,`locale`,
                                                                  `subject`,`body`)
VALUES ('Vacancy Workflow Change Template', @notification_id, @email_media, 'en_US',
        'Candidate Notification - %candidateFirstName% %candidateLastName% for the %vacancyName% Vacancy - %candidateNewEvent%',
        'Hi %subscriberFirstName%,<br/><br/>Please note that candidate %candidateFirstName% %candidateLastName% is now in the %candidateNewEvent% stage for the %vacancyName% Vacancy.<br/><br/>The candidate was in the %candidateOldStateDescription% stage before.<br/><br/>Thank you.<br/>This is an automated notification.'),
       ('Vacancy Workflow Change Template Initial', @notification_id, @email_media, 'en_US',
        'Candidate Notification - %candidateFirstName% %candidateLastName% for the %vacancyName% Vacancy - %candidateNewEvent%',
        'Hi %subscriberFirstName%,<br/><br/>Please note that candidate %candidateFirstName% %candidateLastName% is now in the %candidateNewEvent% stage for the %vacancyName% Vacancy.<br/><br/>Thank you.<br/>This is an automated notification.');

SET @max_position := (SELECT MAX(`position`) FROM `ohrm_event_listener`);

INSERT INTO `ohrm_event_listener` ( `event`, `listener_class`, `enabled`, `position`) VALUES
('recruitment.workflow_change', 'VacancyCandidateWorkflowChangeListener', 1, @max_position+1);
