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

INSERT INTO `ohrm_notification` ( `name`, `is_enable`, `type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`, `hidden`)
VALUES ('Preboarding Welcome Email to the New Hire', '1', @notification_type_id, 'LegacyEmailProcessorWrapperEventNotification', '{"email_processor":"PreboardingEmailProcessor", "list_action_rights":{"change_status":false,"change_subscribers":false,"copy_templates":false}}', 'PREBOARDING_WELCOME_TO_THE_TEAM_TASK', 'Send', @module_id, '0');
SET @notification_id := (SELECT `id` FROM `ohrm_notification` WHERE `name` = 'Preboarding Welcome Email to the New Hire');

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 Welcome Email Sent to New Hire', @notification_id, @notification_media_id, 'en_US',
        'Welcome to %OrganizationName%!',
        '<p>Dear %RecipientFullName%,<br /><br />
Congratulations and welcome to the %OrganizationName% team! We are thrilled to have you on board and look forward to seeing you thrive in your new role.<br />
To help us get started, please click the button below to complete your onboarding form(s). The link will be active for %ValidityPeriod% days, so be sure to fill it out within that time frame.<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;"
    >Complete Preboarding Process Here</a><br /><br />
If you have any questions or need assistance, feel free to reach out. We’re excited to have you with us and can’t wait to see all the great work you’ll do.<br /><br />
Once again, welcome to the team!<br /><br />
Best Regards,<br />
%PerformerFullName%<br />
%PerformerJobTitle%<br />
%OrganizationName%<br />
%PerformerWorkEmail%<br />
%PerformerWorkTelephone%<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` = 'Preboarding Welcome Email Sent to New Hire');

INSERT INTO `ohrm_notification_subscription` (`notification_id`, `workflow_item_id`)
SELECT @notification_id, id FROM ohrm_workflow_state_machine WHERE
    `workflow` = 'PREBOARDING_CREATE_NEW_HIRE_LOGIN_TASK' AND
    `action` = 'Create New Hire Login';
SET @last_subscription_id := (SELECT 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 := (SELECT 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;
