# Candidate Creation Email Notification
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');
SET @workflow_state_machine_id := (SELECT `id` FROM `ohrm_workflow_state_machine` WHERE `workflow` = 'PREBOARDING_CREATE_CANDIDATE_ACCOUNT_TASK' AND `action` = 'Create Candidate Account');

INSERT INTO `ohrm_notification` ( `name`, `is_enable`, `type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`, `hidden`)
VALUES ('Onboarding Initiation Email Notification', '1', @notification_type_id, 'LegacyEmailProcessorWrapperEventNotification', '{"email_processor":"PreboardingCandidateEmailProcessor", "list_action_rights":{"change_status":false,"change_subscribers":false,"copy_templates":false}}', 'PREBOARDING_CREATE_CANDIDATE_ACCOUNT_TASK', 'Create Candidate Account', @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 Initiation Email Notification Template', @notification_id, @notification_media_id, 'en_US',
        'Welcome %CandidateFirstName% %CandidateLastName%',
        '<p>Hi %CandidateFirstName%,<br /><br />
Greetings from %OrganizationName%!<br /><br />
We are excited to invite you to begin the onboarding process for your role with us. Please find below your login credentials for our onboarding portal:<br />
Portal URL: %URL%<br />
Username: %Username%<br />
Password: %Password%<br /><br />
Please use the provided username and password to log in to the portal. Once you have logged in, you can proceed with completing the onboarding process. If you need any assistance, please don''t hesitate to reach out to our Recruitment team at <a href="mailto:recruitment@coltencare.com">recruitment@coltencare.com</a>.<br /><br />
We look forward to your successful onboarding and officially welcoming you to %OrganizationName%!<br /><br />
Warm Regards,<br />
Recruitment Team at %OrganizationName%</p>');
SET @notification_template_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_notification_subscription` (`notification_id`, `notify_affected_employee`, `workflow_item_id`, `hidden`)
VALUES (@notification_id, '0', @workflow_state_machine_id, '0');
SET @notification_subscription_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_notification_subscriber` (media_id, parameters, type, name) VALUES
    (@notification_media_id, '{"prevent_delete":true}', 'Custom', 'Candidate');
SET @notification_subscriber_id_affected_employee := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_notification_subscriber_subscription` (`subscription_id`, `subscriber_id`, `template_id`)
VALUES (@notification_subscription_id, @notification_subscriber_id_affected_employee, @notification_template_id);
