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 @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 @interviewer_user_role := (SELECT id FROM ohrm_user_role WHERE `name` = 'Interviewer');

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


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

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

INSERT INTO `ohrm_notification` 
    (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`) VALUES
    ('Vacancy Publishing', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification', 
    '{"email_processor":"VacancyEmailProcessor", "extra_roles":["HiringManager"]}', @vacancy_workflow_id, 'PUBLISH');
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_template` 
(`notification_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@notification_id, @email_media, 'en_US', NULL, NULL, 
'Notification: %VacancyName% has been %StateName%',
'The vacancy, %VacancyName%, has been %VacancyCurrentState% on %CurrentDate%. It was in %InitialState% state before being %VacancyCurrentState%.

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

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) SELECT
    s.id, @subscriber_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) SELECT
    s.id, @subscriber_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;

-- UNPUBLISH

INSERT INTO `ohrm_notification` 
    (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`) VALUES
    ('Vacancy Inactivation', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification', 
    '{"email_processor":"VacancyEmailProcessor", "extra_roles":["HiringManager"]}', @vacancy_workflow_id, 'UNPUBLISH');
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_template` 
(`notification_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@notification_id, @email_media, 'en_US', NULL, NULL, 
'Notification: %VacancyName% has been %StateName%',
'The vacancy, %VacancyName%, has been %VacancyCurrentState% on %CurrentDate%. It was in %InitialState% state before being %VacancyCurrentState%.

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

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) SELECT
    s.id, @subscriber_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) SELECT
    s.id, @subscriber_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;

-- CLOSE
INSERT INTO `ohrm_notification` 
    (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`) VALUES
    ('Vacancy Closure', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification', 
    '{"email_processor":"VacancyEmailProcessor", "extra_roles":["HiringManager"]}', @vacancy_workflow_id, 'CLOSE');
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_template` 
(`notification_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@notification_id, @email_media, 'en_US', NULL, NULL, 
'Notification: %VacancyName% has been %StateName%',
'The vacancy, %VacancyName%, has been %VacancyCurrentState% on %CurrentDate%. It was in %InitialState% state before being %VacancyCurrentState%.

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

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) SELECT
    s.id, @subscriber_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) SELECT
    s.id, @subscriber_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;



-- ----------------------------------------------------------------------------------------------------------
-- candidate related emails, notifications, email processors, templates
-- ----------------------------------------------------------------------------------------------------------
SET @action_attach := '1';
SET @action_shortlist := '2';
SET @action_reject := '3';
SET @action_schedule_interview := '4';
SET @action_mark_interview_passed := '5';
SET @action_mark_interview_failed := '6';
SET @action_offer_job := '7';
SET @action_decline_offer := '8';
SET @action_hire := '9';
SET @action_schedule_2nd_interview := '10';

-- Candidate Assigned to Vacancy
INSERT INTO `ohrm_notification` 
    (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`) VALUES
    ('Candidate Assigned to Vacancy', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification', 
    '{"email_processor":"CandidateEmailProcessor"}', @recruitment_workflow_id, @action_attach);
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_template` 
(`notification_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@notification_id, @email_media, 'en_US', NULL, NULL, 
'Notification: Application is received for %VacancyName%',
'An application for the position of %VacancyName% was received from %CandidateFirstName% %CandidateLastName%.

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

-- Hidden subscription for Candidate
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_attach;
SET @last_subscription_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscription_template` 
(`subscription_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@last_subscription_id, @email_media, 'en_US', NULL, NULL, 
'Notification: Your application is received for %VacancyName%',
'Dear %CandidateFirstName% %CandidateLastName%,

Your application for the position of %VacancyName% was received.
Thank you for your interest in %OrganizationName%.

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

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) SELECT
    s.id, @subscriber_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;

-- Generic subscription
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;

-- Interview Scheduled
INSERT INTO `ohrm_notification` 
    (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`) VALUES
    ('Interview Schedules', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification', 
    '{"email_processor":"CandidateEmailProcessor", "extra_roles":["HiringManager", "Interviewer"]}', @recruitment_workflow_id, @action_schedule_interview);
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_template` 
(`notification_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@notification_id, @email_media, 'en_US', NULL, NULL, 
'Notification: An interview has been scheduled for %VacancyName% with %CandidateFirstName% %CandidateLastName%',
'%CandidateFirstName% %CandidateLastName% has been selected for an interview on %ScheduledDate%
Interview type: %InterviewType%
Interviewers: %Interviewers%

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

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_schedule_interview;
SET @last_subscription_id := LAST_INSERT_ID();

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

SET @subscriber_id := LAST_INSERT_ID();

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

-- Hidden subscription for Candidate
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_schedule_interview;
SET @last_subscription_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscription_template` 
(`subscription_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@last_subscription_id, @email_media, 'en_US', NULL, NULL, 
'Notification: An interview has been scheduled for %VacancyName% on %ScheduledDate%',
'Hi %CandidateFirstName%  %CandidateLastName%,

You have been selected for an interview on %ScheduledDate%
Interview type: %InterviewType%
We expect your presence on time.

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

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) SELECT
    s.id, @subscriber_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;

-- candidate Interview passed email templates
INSERT INTO `ohrm_notification` 
    (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`) VALUES
    ('Interview Passes', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification', 
    '{"email_processor":"CandidateEmailProcessor", "extra_roles":["HiringManager", "Interviewer"]}', @recruitment_workflow_id, @action_mark_interview_passed);
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_template` 
(`notification_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@notification_id, @email_media, 'en_US', NULL, NULL, 
'Notification: %CandidateFirstName% %CandidateLastName% has passed the %InterviewType% interview for %VacancyName%',
'%CandidateFirstName% %CandidateLastName% has passed the interview, which was held on %ScheduledDate%
Interview type: %InterviewType%
Interviewers: %Interviewers%

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

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_mark_interview_passed;
SET @last_subscription_id := LAST_INSERT_ID();

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

SET @subscriber_id := LAST_INSERT_ID();

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

-- candidate Interview Failed
INSERT INTO `ohrm_notification` 
    (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`) VALUES
    ('Interview Failures', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification', 
    '{"email_processor":"CandidateEmailProcessor", "extra_roles":["HiringManager", "Interviewer"]}', @recruitment_workflow_id, @action_mark_interview_failed);
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_template` 
(`notification_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@notification_id, @email_media, 'en_US', NULL, NULL, 
'Notification: %CandidateFirstName% %CandidateLastName% has failed the %InterviewType% interview for %VacancyName%',
'%CandidateFirstName% %CandidateLastName% has failed the interview, which was held on %ScheduledDate%
Interview type: %InterviewType%
Interviewers: %Interviewers%

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

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_mark_interview_failed;
SET @last_subscription_id := LAST_INSERT_ID();

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

SET @subscriber_id := LAST_INSERT_ID();

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

-- candidate job offer email templates

INSERT INTO `ohrm_notification` 
    (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`) VALUES
    ('Job Offers', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification', 
    '{"email_processor":"CandidateEmailProcessor", "extra_roles":["HiringManager"]}', @recruitment_workflow_id, @action_offer_job);
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_template` 
(`notification_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@notification_id, @email_media, 'en_US', NULL, NULL, 
'Notification: %CandidateFirstName% %CandidateLastName% has been offered a job for %VacancyName%',
'%CandidateFirstName% %CandidateLastName% has been offered a job for %VacancyName%.

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

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_offer_job;
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) SELECT
    s.id, @subscriber_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) SELECT
    s.id, @subscriber_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;

-- candidate Decline offer email templates
INSERT INTO `ohrm_notification` 
    (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`) VALUES
    ('Offer Declinations', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification', 
    '{"email_processor":"CandidateEmailProcessor", "extra_roles":["HiringManager"]}', @recruitment_workflow_id, @action_decline_offer);
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_template` 
(`notification_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@notification_id, @email_media, 'en_US', NULL, NULL, 
'Notification: %CandidateFirstName% %CandidateLastName% has declined the offer for %VacancyName%',
'%CandidateFirstName% %CandidateLastName% has declined the offer given for %VacancyName%.

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

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_decline_offer;
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) SELECT
    s.id, @subscriber_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) SELECT
    s.id, @subscriber_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;

-- candidate hire email templates
INSERT INTO `ohrm_notification` 
    (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`) VALUES
    ('Candidate Hires', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification', 
    '{"email_processor":"CandidateEmailProcessor", "extra_roles":["HiringManager"]}', @recruitment_workflow_id, @action_hire);
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_template` 
(`notification_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@notification_id, @email_media, 'en_US', NULL, NULL, 
'Notification: %CandidateFirstName% %CandidateLastName% has been hired for %VacancyName%',
'%CandidateFirstName% %CandidateLastName% has accepted the offer and officially hired for %VacancyName%.

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

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_hire;
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) SELECT
    s.id, @subscriber_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) SELECT
    s.id, @subscriber_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;

-- Hidden subscription for Candidate
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_subscription_template` 
(`subscription_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@last_subscription_id, @email_media, 'en_US', NULL, NULL, 
'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. ');

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) SELECT
    s.id, @subscriber_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;

-- ICS related queries

INSERT INTO `ohrm_ics_calendar_invitation_processor` (`notification_id` ,`processor`, `is_enabled`, `calendar_method`, `status`, `opacity`) VALUES
(@interview_schedule_notification, 'RecruitmentInvitationProcessor', '1', 'REQUEST', 'CONFIRMED', 'OPAQUE');

INSERT INTO `ohrm_ics_calendar_content_template` (`notification_id` ,`organizer` ,`recipient` ,`summary_text` ,`description_text`) VALUES 
( @interview_schedule_notification, NULL, NULL, 'Interview Scheduled for %vacancy%', 'Interview scheduled for %vacancy% for %candidate% on %date%. Please be on time.');
