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 Referee Details Submission', '1', @notification_type_id, 'LegacyEmailProcessorWrapperEventNotification', '{"email_processor":"PreboardingCandidateEmailProcessor", "extra_roles":["HiringManager", "PreboardingEventManager"]}', 'PREBOARDING_CANDIDATE_REFERENCE_DETAILS_TASK', 'Submit', @module_id, '0');
SET @notification_id := (SELECT `id` FROM `ohrm_notification` WHERE `name` = 'Onboarding Referee Details Submission');

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

INSERT INTO `ohrm_notification_subscriber` (media_id, parameters, type, user_role_id)
SELECT @notification_media_id, NULL, 'UserRole', id FROM ohrm_user_role WHERE `name` = 'Onboarding Admin' AND type_id = 1;
SET @oa_subscriber_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber` (media_id, parameters, type, user_role_id)
SELECT @notification_media_id, NULL, 'UserRole', id FROM ohrm_user_role WHERE `name` = 'Home Admin' AND type_id = 1;
SET @ha_subscriber_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber` (media_id, parameters, type, user_role_id)
SELECT @notification_media_id, NULL, 'UserRole', id FROM ohrm_user_role WHERE `name` = 'Home Manager' AND type_id = 1;
SET @hm_subscriber_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription_template` (`name`,`notification_id`,`media_id`,`locale`,`subject`,`body`)
VALUES ('Onboarding Future Worker Submits Referee Details Notification Template', @notification_id, @notification_media_id, 'en_US',
        'Referee Details Submitted by %CandidateFirstName% %CandidateLastName%',
        '<p>Hi %RecipientFirstName%,<br /><br />
We wanted to inform you that %CandidateFirstName% %CandidateLastName% has submitted their referee details through the candidate portal.<br /><br />
Please log in to OrangeHRM to review and validate the details provided in the candidate''s profile. If the details are satisfactory, kindly initiate the referee validation process.<br /><br />
Thank you for your attention.<br />
(This is a system-generated email, please do not reply.)</p>');
SET @notification_template_id := (SELECT `id` FROM `ohrm_notification_subscriber_subscription_template` WHERE `name` = 'Onboarding Future Worker Submits Referee Details Notification Template');

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

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

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

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

INSERT INTO `ohrm_notification_subscriber_subscription_template` (`name`,`notification_id`,`media_id`,`locale`,`subject`,`body`)
VALUES ('Onboarding Referee Submits Details Notification Template Sent to Referee', @notification_id, @notification_media_id, 'en_US',
        'Role as Referee for %CandidateFirstName% %CandidateLastName%''s Application at %OrganizationName%',
        '<p>Hi %RecipientFullName%,<br /><br />
Thank you for submitting your referee details for %CandidateFirstName% %CandidateLastName%''s application with %OrganizationName%. Your input is invaluable in our evaluation process.<br /><br />
Below are the details:<br />
%FormData%<br /><br />
Should you need to update or amend any information, please contact us at <a href="mailto:recruitment@coltencare.com">recruitment@coltencare.com</a>.<br /><br />
Thank you once again for your time and support.<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 `id` FROM `ohrm_notification_subscriber_subscription_template` WHERE `name` = 'Onboarding Referee Submits Details Notification Template Sent to Referee');

INSERT INTO `ohrm_notification_subscription` (`notification_id`, `workflow_item_id`)
SELECT @notification_id, id FROM ohrm_workflow_state_machine WHERE
    `workflow` = 'PREBOARDING_CANDIDATE_REFERENCE_DETAILS_TASK' AND
    `action` = 'Submit' AND
    `role` = 'CandidateReferee' AND
    `state` = 'Initiated';
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', 'CandidateReferee');
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;

INSERT INTO `ohrm_notification_subscriber_subscription_template` (`name`,`notification_id`,`media_id`,`locale`,`subject`,`body`)
VALUES ('Onboarding Referee Submits Details Notification Template Sent to Subscriber', @notification_id, @notification_media_id, 'en_US',
        'Referee Details Submitted for %CandidateFirstName% %CandidateLastName%',
        '<p>Hi %RecipientFirstName%,<br /><br />
%ReferenceDetails:first_name% %ReferenceDetails:surname% added as the referee for %CandidateFirstName% %CandidateLastName%, has submitted the relevant details.<br /><br />
Please log into the candidate portal in OrangeHRM to review the information provided.<br /><br />
Thank you for your attention.<br />
(This is a system-generated email, please do not reply.)</p>');
SET @notification_template_id := (SELECT `id` FROM `ohrm_notification_subscriber_subscription_template` WHERE `name` = 'Onboarding Referee Submits Details Notification Template Sent to Subscriber');

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

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

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

INSERT INTO `ohrm_notification_subscription` (`notification_id`, `workflow_item_id`)
SELECT @notification_id, id FROM ohrm_workflow_state_machine WHERE
    `workflow` = 'PREBOARDING_CANDIDATE_REFERENCE_DETAILS_TASK' AND
    `action` = 'Submit' AND
    `role` != 'CandidateReferee' AND
    `state` = 'Initiated';

INSERT INTO `ohrm_notification` ( `name`, `is_enable`, `type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`, `hidden`)
VALUES ('Onboarding Referee Details Rejection', '1', @notification_type_id, 'LegacyEmailProcessorWrapperEventNotification', '{"email_processor":"PreboardingCandidateEmailProcessor", "extra_roles":["HiringManager", "PreboardingEventManager"]}', 'PREBOARDING_CANDIDATE_REFERENCE_DETAILS_TASK', 'Reject', @module_id, '0');
SET @notification_id := (SELECT `id` FROM `ohrm_notification` WHERE `name` = 'Onboarding Referee Details Rejection');

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 Referee Details Rejection Notification Template', @notification_id, @notification_media_id, 'en_US',
        'Referee Details of %ReferenceDetails:first_name% %ReferenceDetails:surname% Rejected - Resubmission Required',
        '<p>Hi %CandidateFirstName%,<br /><br />
We regret to inform you that the referee details for %ReferenceDetails:first_name% %ReferenceDetails:surname% you submitted through the candidate portal have been rejected. Unfortunately, the provided information did not meet our validation criteria.<br /><br />
Upon review, the reason for rejection is as follows:<br />
%ActionComment%<br /><br />
Please log in to the candidate portal to review the feedback and resubmit accurate and complete referee details at your earliest convenience.<br /><br />
Thank you for your prompt attention to this matter.<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 `id` FROM `ohrm_notification_subscriber_subscription_template` WHERE `name` = 'Onboarding Referee Details Rejection Notification Template');

INSERT INTO `ohrm_notification_subscription` (`notification_id`, `workflow_item_id`)
SELECT @notification_id, id FROM ohrm_workflow_state_machine WHERE
    `workflow` = 'PREBOARDING_CANDIDATE_REFERENCE_DETAILS_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;

INSERT INTO `ohrm_notification` ( `name`, `is_enable`, `type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`, `hidden`)
VALUES ('Onboarding Referee Process Initiation', '1', @notification_type_id, 'LegacyEmailProcessorWrapperEventNotification', '{"email_processor":"PreboardingCandidateEmailProcessor", "extra_roles":["HiringManager", "PreboardingEventManager"]}', 'PREBOARDING_CREATE_REFEREE_LOGIN_TASK', 'Create Referee Login', @module_id, '0');
SET @notification_id := (SELECT `id` FROM `ohrm_notification` WHERE `name` = 'Onboarding Referee Process Initiation');

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 Referee Process Initiation Notification Template', @notification_id, @notification_media_id, 'en_US',
        'Role as Referee for %CandidateFirstName% %CandidateLastName%''s Application at %OrganizationName%',
        '<p>Dear %RecipientFullName%,<br /><br />
You have been added as a referee by %CandidateFirstName% %CandidateLastName% for their application with %OrganizationName%. Your role as a referee is crucial in helping us assess %CandidateFirstName%''s suitability for the position.<br /><br />
Please click on the following button to access the candidate''s profile and provide your feedback. Please note that this request is only valid for %ValidityPeriod% days.<br /><br />
<a href="%PortalURL%" target="_blank"
    style="color: #FFF; border-color: #3869d4; border-style: solid; border-width: 10px 18px; background-color: #3869D4; display: inline-block; text-decoration: none; border-radius: 3px; box-shadow: 0 2px 3px rgba(0, 0, 0, 0.16); -webkit-text-size-adjust: none; box-sizing: border-box;"
    >Submit Reference</a><br /><br />
If you prefer to submit the information by post, please send the relevant documents to:<br />
Onboarding Advisor<br />
Colten House<br />
Wellworthy Way<br />
BH24 3FE<br /><br />
Your input will be invaluable in our evaluation process. Should you have any questions or need assistance, please do not hesitate to contact us at <a href="mailto:recruitment@coltencare.com">recruitment@coltencare.com</a>.<br /><br />
Thank you for your time and support.<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 `id` FROM `ohrm_notification_subscriber_subscription_template` WHERE `name` = 'Onboarding Referee Process Initiation Notification Template');

INSERT INTO `ohrm_notification_subscription` (`notification_id`, `workflow_item_id`)
SELECT @notification_id, id FROM ohrm_workflow_state_machine WHERE
    `workflow` = 'PREBOARDING_CREATE_REFEREE_LOGIN_TASK' AND
    `action` = 'Create Referee Login';
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', 'CandidateReferee');
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;
