SET @performance_module = (SELECT `id` FROM `ohrm_module` WHERE  `name` ='performance');
SET @action_notification_type = (SELECT id FROM ohrm_notification_type where `name` = 'Action Based');
SET @email_media = (SELECT id FROM ohrm_notification_media WHERE `name` = 'Email');
SET @main_appraisal_workflow_id := (SELECT id FROM ohrm_workflow where `name`='mainAppraisal');

INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`, `hidden`) VALUES
    ('Employee Appraisal Evaluator Reassigned Notification', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
     '{"email_processor":"PerformanceAppraisalReassignEvaluatorEmailProcessor"}', @main_appraisal_workflow_id, 'Reassign', @performance_module, 0);

SET @notification_id = LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscription` (notification_id, workflow_item_id)
SELECT @notification_id, id FROM ohrm_workflow_state_machine WHERE `action` = 'Reassign' AND workflow = @main_appraisal_workflow_id;

SET @subscription_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, type, name) VALUES
    (@email_media, '{"prevent_delete":true}', 'Custom', 'New Evaluator');

SET @subscriber_id = LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
    (@notification_id, @email_media, 'en_US',
     'Employee Appraisal Evaluator Reassigned Notification',
     'You’re assigned as a new evaluator for the appraisal of %Employee_Name%',
     '<p>Dear %newEvaluatorName%,<br/><br/>You have been assigned as the new evaluator for %applicantFirstName%\'s appraisal for the appraisal period: %from_date% - %to_date%. You are taking over from the previous evaluator.<br/><br/>Please review the previous evaluator\'s ratings, and comments to understand the %applicantFirstName%\'s performance thus far. Your input is essential for a comprehensive evaluation.<br/><br/>You can access the review and proceed with the necessary actions by clicking on the following link: <a href="%appraisalReviewLink%">%appraisalName%</a> <br/><br/>Thank you for your valuable contribution.<br/><br/>This is an automated notification.</p>');

SET @template_id = LAST_INSERT_ID();

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