SET @notification_media_id := (SELECT `id` FROM `ohrm_notification_media` WHERE `name` = 'Email');
SET @notification_type_id := (SELECT `id` FROM `ohrm_notification_type` WHERE `name` = 'Action Based');
SET @module_id := (SELECT `id` FROM `ohrm_module` WHERE `name` = 'recruitment');

INSERT INTO `ohrm_notification` ( `name`, `is_enable`, `type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`, `hidden`)
VALUES ('Onboarding Other Details Rejection', '1', @notification_type_id, 'LegacyEmailProcessorWrapperEventNotification', '{"email_processor":"PreboardingCandidateEmailProcessor"}', null, null, @module_id, '0');
SET @notification_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscriber_subscription_template` (`name`,`notification_id`,`media_id`,`locale`,`subject`,`body`)
VALUES ('Onboarding Other Details Rejection Notification Template', @notification_id, @notification_media_id, 'en_US',
        'Action Required: Resubmit Details for %TabName%',
        '<p>Hi %CandidateFirstName%,<br /><br />
Your submission for the %TabName% tab has been rejected due to the following reason(s):<br />
%ActionComment%<br /><br />
Please log in to your account, review the details, make necessary corrections, and resubmit the form.<br /><br />
For further assistance, please contact <a href="mailto:recruitment@coltencare.com">recruitment@coltencare.com</a>.<br /><br />
Thank you for your prompt attention.<br /><br />
Best Regards,<br />
Recruitment Team at %OrganizationName%<br />
(This is a system-generated email, please do not reply.)</p>');
SET @notification_template_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_notification_subscription` (`notification_id`, `workflow_item_id`)
SELECT @notification_id, id FROM ohrm_workflow_state_machine WHERE
`workflow` IN ('PREBOARDING_CANDIDATE_PERSONAL_INFO_TASK',
               'PREBOARDING_CANDIDATE_CONTACT_DETAIL_TASK',
               'PREBOARDING_CANDIDATE_BANK_DETAILS_TASK',
               'PREBOARDING_CANDIDATE_EMERGENCY_CONTACT_TASK',
               'PREBOARDING_CANDIDATE_DBS_DETAIL_TASK',
               'PREBOARDING_CANDIDATE_PAYROLL_DETAIL_TASK',
               'PREBOARDING_CANDIDATE_CONSENT_TASK')
AND `action` = 'Reject';
SET @last_subscription_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber` (media_id, parameters, type, name) VALUES
    (@notification_media_id, '{"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, @notification_template_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;

