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` = 'onboardingPro');

INSERT INTO `ohrm_notification` ( `name`, `is_enable`, `type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`, `hidden`)
VALUES ('Preboarding Form Rejection Notification', '1', @notification_type_id, 'LegacyEmailProcessorWrapperEventNotification', '{"email_processor":"PreboardingEmailProcessor"}', 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 ('Preboarding Form Rejection Notification Template', @notification_id, @notification_media_id, 'en_US',
        'Action Required: Resubmit Details for %TabName%',
        '<p>Dear %newHireFirstName% %newHireLastName%,<br /><br />
We regret to inform you that the <span style="text-transform: lowercase;">%TabName%</span> you submitted via the preboarding portal could not be processed. Unfortunately, the information provided did not meet our validation criteria.<br /><br />
Reason for Rejection:<br />
%ActionComment%<br /><br />
To proceed, please access to the preboarding portal and resubmit your updated <span style="text-transform: lowercase;">%TabName%</span> with all required information.<br /><br />
We appreciate your attention to this matter and look forward to receiving the updated information.<br /><br />
Best Regards,<br />
%OrganizationName% Recruitment Team<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_PERSONAL_DETAILS_TASK',
        'PREBOARDING_CONTACT_DETAILS_TASK',
        'PREBOARDING_EMERGENCY_CONTACTS_TASK',
        'PREBOARDING_DEPENDENT_INFORMATION_TASK',
        'PREBOARDING_IMMIGRATION_INFORMATION_TASK',
        'PREBOARDING_WORK_EXPERIENCE_TASK',
        'PREBOARDING_EDUCATION_TASK',
        'PREBOARDING_SKILL_TASK',
        'PREBOARDING_LANGUAGE_TASK',
        'PREBOARDING_LICENSE_TASK',
        'PREBOARDING_MEMBERSHIPS_TASK',
        'PREBOARDING_FORMS_AND_ATTACHMENTS_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', 'Preboarding New Hire');
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;

