SET @action_notification_type := (SELECT id FROM ohrm_notification_type WHERE `name`='Action Based');
SET @recruitment_workflow_id := (SELECT id FROM ohrm_workflow WHERE `name`='recruitment');
SET @action_reject := '3';
SET @recruitment_module := (SELECT `id` FROM `ohrm_module` WHERE  `name` ='recruitment');

SET @hiring_manager_user_role := (SELECT id FROM ohrm_user_role WHERE `name` = 'HiringManager');

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


INSERT INTO `ohrm_notification`
    (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`) VALUES
    ('Candidate Reject', 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_template` (`notification_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@notification_id, @email_media, 'en_US', null, null, 'Application Rejection Notification of Candidate %CandidateFirstName% %CandidateLastName% for the vacancy %VacancyName%',
'Hi %EmployeeFirstName%,

Please note that %CandidateFirstName% %CandidateLastName%''s application for the vacancy %VacancyName% was not selected for further consideration.

Thank you.
You are getting this email because you are a subscriber.
This is an automated notification.');

INSERT INTO `ohrm_notification_template` (`notification_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@notification_id, @email_media, 'en_US', null, 'Candidate', 'Application Rejection Notification for the vacancy %VacancyName%',
'Hi %CandidateFirstName%,

Thank you for your interest in joining %OrganizationName% team.
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.

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.

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

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