SET @action_notification_type := (SELECT id FROM ohrm_notification_type where `name`='Action Based');
SET @main_appraisal_workflow_id := (SELECT id FROM ohrm_workflow where `name`='mainAppraisal');
SET @admin_user_role := (SELECT id FROM ohrm_user_role WHERE `name` = 'Admin');
SET @supervisor_user_role := (SELECT id FROM ohrm_user_role WHERE `name` = 'Supervisor');
SET @ess_user_role := (SELECT id FROM ohrm_user_role WHERE `name` = 'ESS');
SET @main_evaluator_user_role := (SELECT id FROM ohrm_user_role WHERE `name` = 'MainEvaluator');
SET @appraisal_reviewer_user_role := (SELECT id FROM ohrm_user_role WHERE `name` = 'AppraisalReviewer');
SET @appraisal_supervisor_reviewer_user_role := (SELECT id FROM ohrm_user_role WHERE `name` = 'AppraisalSupervisorReviewer');
SET @locale := 'en_US';
SET @email_media := (SELECT id FROM ohrm_notification_media WHERE `name` = 'Email');

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, type, user_role_id, name) VALUES
  (@email_media, '{"prevent_delete":true,"restrict_employees":true}', 'Custom', NULL, 'Evaluator');
SET @evaluator_subscriber := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, type, user_role_id) VALUES
  (@email_media, 'UserRole', @main_evaluator_user_role);
SET @appraisal_reviewer_main_evaluators_subscriber := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, type, user_role_id) VALUES
  (@email_media, 'UserRole', @appraisal_supervisor_reviewer_user_role);
SET @appraisal_reviewer_supervisors_subscriber := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, type, user_role_id, name) VALUES
    (@email_media, '{"prevent_delete":true,"restrict_employees":true}', 'Custom', NULL, 'Appraisal Reviewer - Self Evaluator');
SET @appraisal_reviewer_self_subscriber := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, type, user_role_id, name) VALUES
  (@email_media, '{"prevent_delete":true,"restrict_employees":true}', 'Custom', NULL, 'Appraisal Reviewer - Peer');
SET @appraisal_reviewer_peers_subscriber := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, type, user_role_id, name) VALUES
  (@email_media, '{"prevent_delete":true,"restrict_employees":true}', 'Custom', NULL, 'Appraisal Reviewer - Subordinate');
SET @appraisal_reviewer_subordinates_subscriber := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, type, user_role_id, name) VALUES
  (@email_media, '{"prevent_delete":true,"restrict_employees":true}', 'Custom', NULL, 'Affected Employee');
SET @affected_employee_subscriber := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber` (media_id, parameters, type, user_role_id) VALUES
  (@email_media, NULL, 'UserRole', @main_evaluator_user_role);
SET @main_evaluator_user_role_subscriber := LAST_INSERT_ID();

SET @performance_module := (SELECT `id` FROM `ohrm_module` WHERE  `name` ='performance');

-- Event Notifications
SET @date_based_notification_type := (SELECT id FROM ohrm_notification_type where `name`='Date Based');
--
-- Employee Appraisal Notification
INSERT INTO `ohrm_notification` 
    (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`) VALUES
    ('Appraisal Due Date Notifications', 0, @date_based_notification_type, 'AppraisalEventNotification', '{"extra_roles":["AppraisalReviewer"],"entity":"EmployeeAppraisal","attribute":"dueDate","employeeJoinAttribute":"emp_number,id AS app.id","dateFormat":"YYYY-MM-DD","employeeReference":"foreign","conditions":["state_id IN (SELECT eas.state_id FROM AppraisalStatus eas WHERE eas.name <> ''NOT INITIATED'')","0 <> (SELECT count(eae.id) FROM EmployeeAppraisalEvaluator eae WHERE eae.ReviewStatus.stateId IN (SELECT prs.stateId FROM ReviewStatus prs WHERE prs.name IN (''INITIATED'',''IN PROGRESS'')) AND EmployeeAppraisal.id = eae.EmployeeAppraisal.id)"]}', NULL, NULL,@performance_module);
SET @notification_id := LAST_INSERT_ID();

-- Converted to New Email Engine
INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
'Appraisal Due Date Notifications',
'Employee Appraisal Due Date Notification',
'<p>Dear %RECIPINTNAME%,<br /><br />The performance appraisal for the period of %FROM_DATE% to %TO_DATE% %FOR_EMPLOYEE% is due to be completed in %DAYSLEFT%.<br /><br />%REVIEW_FOR_EMPLOYEE%<br /><br /><br />Thank you,<br />HR Team</p>');

SET @template_id := LAST_INSERT_ID();

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

-- Subscribe Evaluators
INSERT INTO `ohrm_notification_subscription`
    (notification_id, notify_before_value, notify_before_unit) VALUES
    (@notification_id, 1, 'WEEK');

SET @last_subscription_id := LAST_INSERT_ID();

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

SET @subscriber_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 >= @last_subscription_id;

-- Appraisal Initiated (workflow id is null to support multiple performance workflows)
-- Converted to New Email Engine
INSERT INTO `ohrm_notification` 
    (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`) VALUES
    ('Employee Appraisal Initiated Notification', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification', 
    '{"email_processor":"PerformanceEmailProcessor", "email.id":"appraisal.initiated", "extra_roles":["MainEvaluator","AppraisalSupervisorReviewer"]}', @main_appraisal_workflow_id, 'Activate',@performance_module);
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 `workflow` = @main_appraisal_workflow_id AND `action` = 'Activate';

SET @last_subscription_id := LAST_INSERT_ID();

-- self appraisal review

INSERT INTO `ohrm_notification_subscriber_subscription_template` (`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`)
VALUES (@notification_id, @email_media, @locale, 'Employee appraisal initiation notification sent to Self Evaluator', 'Employee Appraisal Initiated Notification',
'<p>Dear %subscriberName%,<br /><br />Your performance appraisal for the period %from_date% to %to_date% has been activated.<br /><br />Please log onto OrangeHRM, and submit your self-review.<br /><br />Thank you<br />HR Team<br />(This is a system generated email, please do not reply!)</p>');

SET @initiated_template_id_self := LAST_INSERT_ID();


-- main evaluator appraisal review

INSERT INTO `ohrm_notification_subscriber_subscription_template` (`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`)
VALUES (@notification_id, @email_media, @locale, 'Employee appraisal initiation notification sent to Evaluator', 'Employee Appraisal Initiated Notification',
'<p>Dear %subscriberName%,<br /><br />The performance appraisal for the period of %from_date% to %to_date% for %Employee_Name% has been activated.<br /><br />Please log onto OrangeHRM, and submit your review for %Employee_Name%.<br /><br />Thank you<br />HR Team<br />(This is a system generated email, please do not reply!)</p>');

SET @initiated_template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription`(`subscription_id`, `subscriber_id`, `template_id`)
VALUES (@last_subscription_id, @appraisal_reviewer_main_evaluators_subscriber, @initiated_template_id);

INSERT INTO `ohrm_notification_subscriber_subscription`(`subscription_id`, `subscriber_id`, `template_id`)
VALUES (@last_subscription_id, @appraisal_reviewer_supervisors_subscriber, @initiated_template_id);

INSERT INTO `ohrm_notification_subscriber_subscription`(`subscription_id`, `subscriber_id`, `template_id`)
VALUES (@last_subscription_id, @appraisal_reviewer_self_subscriber, @initiated_template_id_self);

INSERT INTO `ohrm_notification_subscriber_subscription`(`subscription_id`, `subscriber_id`, `template_id`)
VALUES (@last_subscription_id, @appraisal_reviewer_peers_subscriber, @initiated_template_id);

INSERT INTO `ohrm_notification_subscriber_subscription`(`subscription_id`, `subscriber_id`, `template_id`)
VALUES (@last_subscription_id, @appraisal_reviewer_subordinates_subscriber, @initiated_template_id);

-- subscriber appraisal review
INSERT INTO `ohrm_notification_subscriber_subscription_template` (`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`)
VALUES (@notification_id, @email_media, @locale, 'Employee appraisal initiation notification sent to Subscriber', 'Employee Appraisal Initiated Notification',
'<p>Dear %subscriberName%,<br /><br />The performance appraisal for the period of %from_date% to %to_date% for %Employee_Name% has been activated.<br /><br />Thank you<br />HR Team<br />(This is a system generated email, please do not reply!)</p>');

-- Appraisal completed
INSERT INTO `ohrm_notification` 
    (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`) VALUES
    ('Employee Appraisal Completed Notification', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification', 
    '{"email_processor":"PerformanceEmailProcessor", "email.id":"appraisal.completed", "extra_roles":["MainEvaluator","AppraisalSupervisorReviewer"]}', @main_appraisal_workflow_id, 'Complete',@performance_module);
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 `workflow` = @main_appraisal_workflow_id AND `action` = 'Complete';

SET @last_subscription_id := LAST_INSERT_ID();

-- ess review
INSERT INTO `ohrm_notification_subscriber_subscription_template` (`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`)
VALUES (@notification_id, @email_media, @locale, 'Employee appraisal completion notification sent to Employee', 'Employee Appraisal Completed Notification',
'<p>Dear %subscriberName%,<br /><br />Your performance appraisal for the period %from_date% to %to_date% has been completed.<br /><br />Thank you<br />HR Team<br />(This is a system generated email, please do not reply!)</p>');

SET @completed_template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription`(`subscription_id`, `subscriber_id`, `template_id`)
VALUES (@last_subscription_id, @affected_employee_subscriber, @completed_template_id);

-- main evaluator review
INSERT INTO `ohrm_notification_subscriber_subscription_template` (`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`)
VALUES (@notification_id, @email_media, @locale, 'Employee appraisal completion notification sent to Subscriber', 'Employee Appraisal Completed Notification',
'<p>Dear %subscriberName%,<br /><br />The performance appraisal for the period %from_date% to %to_date% for %Employee_Name% has been completed.<br /><br />Thank you<br />HR Team<br />(This is a system generated email, please do not reply!)</p>');

SET @completed_template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription`(`subscription_id`, `subscriber_id`, `template_id`)
VALUES (@last_subscription_id, @main_evaluator_user_role_subscriber, @completed_template_id);

-- Appraisal Submitted
INSERT INTO `ohrm_notification` 
    (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`) VALUES
    ('Employee Appraisal Submitted Notification', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification', 
    '{"email_processor":"PerformanceEmailProcessor", "email.id":"appraisal.submitted", "extra_roles":["MainEvaluator","AppraisalSupervisorReviewer"], "notifyAffectedUserOptionVisible": true}', @main_appraisal_workflow_id, 'Submit',@performance_module);
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_subscriber_subscription_template` (`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`)
VALUES (@notification_id, @email_media, @locale, 'Employee appraisal submission notification sent to Main Evaluator', 'Employee Appraisal Submitted Notification',
'<p>Dear %subscriberName%,<br /><br />The performance appraisal for the period %from_date% to %to_date% for %Employee_Name% has been submitted.<br /><br /><br />Thank you<br />HR Team<br />(This is a system generated email, please do not reply!)</p>');

INSERT INTO `ohrm_notification_subscriber_subscription_template` (`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`)
VALUES (@notification_id, @email_media, @locale, 'Employee appraisal submission sent to Subscriber', 'Employee Appraisal Submitted Notification',
'<p>Dear %subscriberName%,<br /><br />The performance appraisal for the period %from_date% to %to_date% for %Employee_Name% has been submitted by %performerFullName%.<br /><br /><br />Thank you<br />HR Team<br />(This is a system generated email, please do not reply!)</p>');

INSERT INTO `ohrm_notification_subscription`
    (notification_id, workflow_item_id) SELECT
    @notification_id, id FROM ohrm_workflow_state_machine WHERE `workflow` IN (
        select id from ohrm_workflow where group_name = 'performance appraisals'
        )
    AND `action` = 'Submit'
    AND roles_to_notify LIKE '%Subscriber%';

-- Appraisal Rejected
INSERT INTO `ohrm_notification`
    (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`) VALUES
    ('Employee Appraisal Rejected Notification', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
    '{"email_processor":"PerformanceEmailProcessor", "email.id":"appraisal.rejected"}', @main_appraisal_workflow_id, 'Reject',@performance_module);
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_subscriber_subscription_template` (`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`)
VALUES (@notification_id, @email_media, @locale, 'Employee appraisal rejection notification sent to Evaluator', 'Employee Appraisal Rejected Notification',
'<p>Dear %subscriberName%,<br /><br />The performance appraisal for the period %from_date% to %to_date% for %Employee_Name% has been rejected. Please log onto OrangeHRM and re-submit it..<br /><br /><br />Thank you<br />HR Team<br />(This is a system generated email, please do not reply!)</p>');

SET @rejected_template_id := 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 (
        select id from ohrm_workflow where group_name = 'performance appraisals'
    )
    AND `action` = 'Reject'
    AND roles_to_notify LIKE '%Evaluator%';

SET @last_subscription_id := LAST_INSERT_ID();

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

INSERT INTO `ohrm_notification_subscriber_subscription_template` (`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`)
VALUES (@notification_id, @email_media, @locale, 'Employee appraisal rejection notification sent to Subscriber', 'Employee Appraisal Rejected Notification',
'<p>Dear %subscriberName%,<br /><br />The performance appraisal for the period %from_date% to %to_date% for %Employee_Name% has been rejected.<br /><br /><br />Thank you<br />HR Team<br />(This is a system generated email, please do not reply!)</p>');

-- Appraisal Signed
INSERT INTO `ohrm_notification`
    (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`) VALUES
    ('Employee Appraisal Signed Notification', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
    '{"email_processor":"PerformanceEmailProcessor", "email.id":"appraisal.signed"}', @main_appraisal_workflow_id, 'Sign',@performance_module);
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_subscriber_subscription_template` (`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`)
VALUES (@notification_id, @email_media, @locale, 'Employee appraisal signed notification sent to Employee', 'Employee Appraisal Signed Notification',
'<p>Dear %subscriberName%,<br /><br />The performance appraisal for the period %from_date% to %to_date% for %Employee_Name% has been signed. Please Log onto OrangeHRM and acknowledge.<br /><br />You will find the acknowledgement option at the bottom of final review in the single view or click on the final reviewers name from the multiple view screen.<br /><br />Thank you<br />HR Team<br />(This is a system generated email, please do not reply!)</p>');

INSERT INTO `ohrm_notification_subscriber_subscription_template` (`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`)
VALUES (@notification_id, @email_media, @locale, 'Employee appraisal signed notification send to Subscriber', 'Employee Appraisal Signed Notification',
'<p>Dear %subscriberName%,<br /><br />The performance appraisal for the period %from_date% to %to_date% for %Employee_Name% has been signed.<br /><br />Thank you<br />HR Team<br />(This is a system generated email, please do not reply!)</p>');

-- Hide notification if signing is not enabled
UPDATE ohrm_notification SET
hidden = (SELECT IF(count(*)>0, 0, 1) FROM ohrm_workflow_state_machine WHERE workflow = 'FINAL REVIEW' AND `action` = 'Sign')
WHERE name = 'Employee Appraisal Signed Notification';

SET @action_notification_type := (SELECT id
                                  FROM ohrm_notification_type
                                  where `name` = 'Action Based');
SET @goal_workflow_id := (SELECT id
                          FROM ohrm_workflow
                          where `name` = 'performanceGoal');
SET @supervisor_user_role := (SELECT id
                              FROM ohrm_user_role
                              WHERE `name` = 'Supervisor');
SET @email_media := (SELECT id
                     FROM ohrm_notification_media
                     WHERE `name` = 'Email');
SET @performance_module := (SELECT `id`
                            FROM `ohrm_module`
                            WHERE `name` = 'performance');

-- Goal Workflow related Email Notifications

-- Submit - default sent to supervisor
INSERT INTO `ohrm_notification`
(`name`, `is_enable`, `type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`, `hidden`) VALUES
  ('Goal Submissions', 0, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
   '{"email_processor":"PerformanceGoalEmailProcessor","extra_roles":["HeadOfDepartment"]}', @goal_workflow_id, 'Submit', @performance_module, 1);
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_subscriber_subscription_template`
(`notification_id`, `media_id`, `locale`, `name`, `subject`, `body`) VALUES
  (@notification_id, @email_media, 'en_US',
   'Goal Pending Approval Notification Sent to Supervisor',
   '%performerFullName% submitted %goalType%: %goalName%',
   '<p>Dear %subscriberName%,<br /><br />%performerFullName% has submitted %goalType% %goalName% which is pending your approval.<br /><br />You can use this <a href="%goalUrl%">link</a> to access the %goalType%.<br /><br />Thank you.<br /><br />This is an automated notification.</p>');

SET @template_id := LAST_INSERT_ID();

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

-- Subscriber for Default Supervisor
INSERT INTO `ohrm_notification_subscriber` (media_id, parameters, type, user_role_id) VALUES
  (@email_media, NULL, 'UserRole', @supervisor_user_role);

SET @subscriber_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 >= @last_subscription_id;


-- Save Goal (by Supervisor\HOD\Admin) - default sent to employee
INSERT INTO `ohrm_notification`
(`name`, `is_enable`, `type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`, `hidden`) VALUES
  ('Goal Assignments', 0, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
   '{"email_processor":"PerformanceGoalEmailProcessor","extra_roles":["HeadOfDepartment"]}', @goal_workflow_id, 'Save', @performance_module, 1);
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_subscriber_subscription_template`
(`notification_id`, `media_id`, `locale`, `name`, `subject`, `body`) VALUES
  (@notification_id, @email_media, 'en_US',
   'Goal Assigned Notification Sent to Employee',
   '%performerFullName% assigned you %goalType%: %goalName%',
   '<p>Dear %subscriberName%,<br /><br />%performerFullName% has assigned %goalType% %goalName% to you.<br /><br />You can use this <a href="%goalUrl%">link</a> to access the %goalType%.<br /><br />Thank you.<br /><br />This is an automated notification.</p>');

SET @template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id) SELECT
                                      @notification_id,
                                      id
                                    FROM ohrm_workflow_state_machine
                                    WHERE `workflow` = @goal_workflow_id AND state = 'INITIAL' AND `action` = 'Save' AND role IN ('ADMIN', 'SUPERVISOR', 'HEADOFDEPARTMENT');
SET @last_subscription_id := LAST_INSERT_ID();

-- Subscriber for Employee
INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, type, name) VALUES
  (@email_media, '{"restrict_employees":true}', 'Custom', 'Affected Employee');

SET @subscriber_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 >= @last_subscription_id;

-- Approve Goal (by Supervisor\HOD\Admin) - default sent to employee
INSERT INTO `ohrm_notification`
(`name`, `is_enable`, `type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`, `hidden`) VALUES
  ('Goal Approvals', 0, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
   '{"email_processor":"PerformanceGoalEmailProcessor","extra_roles":["HeadOfDepartment"]}', @goal_workflow_id, 'Approve', @performance_module, 1);
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_subscriber_subscription_template`
(`notification_id`, `media_id`, `locale`, `name`, `subject`, `body`) VALUES
  (@notification_id, @email_media, 'en_US',
   'Goal Approved Notification Sent to Employee',
   '%performerFullName% approved your %goalType%: %goalName%',
   '<p>Dear %subscriberName%,<br /><br />%performerFullName% has approved %goalType% %goalName%.<br /><br />Goal approval comment: %actionComment%<br /><br />You can use this <a href="%goalUrl%">link</a> to access the %goalType%.<br /><br />Thank you.<br /><br />This is an automated notification.</p>');

SET @template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id) SELECT
                                      @notification_id,
                                      id
                                    FROM ohrm_workflow_state_machine
                                    WHERE `workflow` = @goal_workflow_id AND `action` = 'Approve' AND role IN ('ADMIN', 'SUPERVISOR', 'HEADOFDEPARTMENT');
SET @last_subscription_id := LAST_INSERT_ID();

-- Subscriber for Employee
INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, type, name) VALUES
  (@email_media, '{"restrict_employees":true}', 'Custom', 'Affected Employee');

SET @subscriber_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 >= @last_subscription_id;

-- Reject Goal (by Supervisor\HOD\Admin) - default sent to employee
INSERT INTO `ohrm_notification`
(`name`, `is_enable`, `type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`, `hidden`) VALUES
  ('Goal Rejections', 0, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
   '{"email_processor":"PerformanceGoalEmailProcessor","extra_roles":["HeadOfDepartment"]}', @goal_workflow_id, 'Reject', @performance_module, 1);
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_subscriber_subscription_template`
(`notification_id`, `media_id`, `locale`, `name`, `subject`, `body`) VALUES
  (@notification_id, @email_media, 'en_US',
   'Goal Rejected Notification Sent to Employee',
   '%performerFullName% rejected your %goalType%: %goalName%',
   '<p>Dear %subscriberName%,<br /><br />%performerFullName% has rejected %goalType% %goalName%.<br /><br />Goal rejection comment: %actionComment%<br /><br />You can use this <a href="%goalUrl%">link</a> to access the %goalType%.<br /><br />Thank you.<br /><br />This is an automated notification.</p>');

SET @template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id) SELECT
                                      @notification_id,
                                      id
                                    FROM ohrm_workflow_state_machine
                                    WHERE `workflow` = @goal_workflow_id AND `action` = 'Reject' AND role IN ('ADMIN', 'SUPERVISOR', 'HEADOFDEPARTMENT');
SET @last_subscription_id := LAST_INSERT_ID();

-- Subscriber for Employee
INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, type, name) VALUES
  (@email_media, '{"restrict_employees":true}', 'Custom', 'Affected Employee');

SET @subscriber_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 >= @last_subscription_id;

-- Update Goal by ESS - default sent to Supervisor
INSERT INTO `ohrm_notification`
(`name`, `is_enable`, `type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`, `hidden`) VALUES
  ('Goal Updates', 0, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification',
   '{"email_processor":"PerformanceGoalEmailProcessor","extra_roles":["HeadOfDepartment"]}', @goal_workflow_id, 'Update', @performance_module, 1);
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_subscriber_subscription_template`
(`notification_id`, `media_id`, `locale`, `name`, `subject`, `body`) VALUES
  (@notification_id, @email_media, 'en_US',
   'Goal Updated Notification Sent to Supervisor',
   '%performerFullName% updated %goalType%: %goalName%',
   '<p>Dear %subscriberName%,<br /><br />%performerFullName% has updated %goalType% %goalName%<br /><br />You can use this <a href=\"%goalUrl%\">link</a> to access the %goalType%.<br /><br />Thank you.<br /><br />This is an automated notification.</p>');

SET @template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id) SELECT
                                      @notification_id,
                                      id
                                    FROM ohrm_workflow_state_machine
                                    WHERE `workflow` = @goal_workflow_id AND state = 'APPROVED' AND `action` = 'Update' AND role = 'ESS User';
SET @last_subscription_id := LAST_INSERT_ID();

-- Subscriber for Default Supervisor
INSERT INTO `ohrm_notification_subscriber` (media_id, parameters, type, user_role_id) VALUES
  (@email_media, NULL, 'UserRole', @supervisor_user_role);

SET @subscriber_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 >= @last_subscription_id;


-- Goal Due Date Notifications
SET @date_based_notification_type := (SELECT id FROM ohrm_notification_type where `name`='Date Based');

SET @performance_module := (SELECT `id` FROM `ohrm_module` WHERE  `name` ='performance');

INSERT INTO `ohrm_notification`
    (`name`, `is_enable`,`type_id`, `generator_class`, `workflow_id`, `workflow_action`, `module_id`) VALUES
    ('Goal Due Date Notification', 1, @date_based_notification_type, 'GoalDueDateReminderNotification', NULL, NULL, @performance_module);
SET @notification_id := (SELECT `id` FROM `ohrm_notification` WHERE `name` LIKE 'Goal Due Date Notification');

SET @email_media := (SELECT id FROM ohrm_notification_media WHERE `name` = 'Email');

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@notification_id, @email_media, 'en_US',
'Reminder Sent to the Goal Owner',
'Reminder - %GoalType% is due on %DueDate%',
'<p>Dear %GoalOwnerName%,<br /><br />This is a kind reminder that your %GoalTypeInLowerCase% due date is near.<br /><br />%GoalDetailsTable%<br />You can view and update its progress from the <a href="%GoalLink%">link</a><br /><br />Thank you.<br />This is an automated notification.</p>');

SET @template_id := (SELECT `id` FROM `ohrm_notification_subscriber_subscription_template` WHERE `name` LIKE 'Reminder Sent to the Goal Owner');

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

INSERT INTO `ohrm_notification_subscription`
    (notification_id, notify_before_value, notify_before_unit) VALUES
    (@notification_id, 0, 'DAY');

SET @last_subscription_id := (SELECT `id` FROM `ohrm_notification_subscription` WHERE `notification_id` = @notification_id);


INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, type, name) VALUES
  (@email_media, '{"prevent_delete":true,"restrict_employees":true}', 'Custom', 'Affected Employee');
SET @subscriber_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription`(subscription_id, subscriber_id, template_id) VALUES
(@last_subscription_id, @subscriber_id, @template_id);
