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 @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}', 'Custom', NULL, 'Evaluator');
SET @evaluator_subscriber := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, type, user_role_id, name) VALUES
  (@email_media, '{"prevent_delete":true}', 'Custom', NULL, 'AppraisalReviewer.self');
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}', 'Custom', NULL, 'AppraisalReviewer.mainEvaluators');
SET @appraisal_reviewer_main_evaluators_subscriber := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, type, user_role_id, name) VALUES
  (@email_media, '{"prevent_delete":true}', 'Custom', NULL, 'AppraisalReviewer.supervisors');
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}', 'Custom', NULL, 'AppraisalReviewer.peers');
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}', 'Custom', NULL, 'AppraisalReviewer.subordinates');
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}', 'Custom', NULL, 'ess');
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');

SET @notification_id := (SELECT `id` FROM `ohrm_notification` WHERE `name`='Employee Appraisal Initiated Notification');

UPDATE ohrm_notification_template SET body='Dear %subscriberName%,

The performance appraisal for the period of %from_date% to %to_date% for %Employee_Name% has been activated.

Thank you
HR Team
(This is a system generated email, please do not reply!)'
WHERE `notification_id`=@notification_id AND `locale`=@locale;

INSERT INTO `ohrm_notification_template` (`notification_id`,`media_id`,`locale`,`performer_role`,`recipient_role`,`subject`,`body`)
SELECT @notification_id, @email_media, @locale,et.`performer_role`,et.`recipient_role`, et.subject,et.body FROM `ohrm_email` e
LEFT JOIN `ohrm_email_template` et ON et.email_id = e.id
WHERE e.name = 'appraisal.initiated' AND et.`recipient_role`!='Subscriber';

-- Appraisal completed
SET @notification_id := (SELECT `id` FROM `ohrm_notification` WHERE `name`='Employee Appraisal Completed Notification');

UPDATE ohrm_notification_template SET body='Dear %subscriberName%,

The performance appraisal for the period %from_date% to %to_date% for %Employee_Name% has been completed.

Thank you
HR Team
(This is a system generated email, please do not reply!) '
WHERE `notification_id`=@notification_id AND `locale`=@locale;

INSERT INTO `ohrm_notification_template` (`notification_id`,`media_id`,`locale`,`performer_role`,`recipient_role`,`subject`,`body`)
SELECT @notification_id, @email_media, @locale, et.`performer_role`, et.`recipient_role`, et.subject,et.body FROM `ohrm_email` e
LEFT JOIN `ohrm_email_template` et ON et.email_id = e.id
WHERE e.name = 'appraisal.completed' AND et.`recipient_role`!='Subscriber';

-- subscriber review

-- Appraisal Submit
SET @notification_id := (SELECT `id` FROM `ohrm_notification` WHERE `name`='Employee Appraisal Submitted Notification');

UPDATE ohrm_notification_template SET body='Dear %subscriberName%,

The performance appraisal for the period %from_date% to %to_date% for %Employee_Name% has been submitted by %performerFullName%.


Thank you
HR Team
(This is a system generated email, please do not reply!) '
WHERE `notification_id`=@notification_id AND `locale`=@locale;

INSERT INTO `ohrm_notification_template` (`notification_id`,`media_id`,`locale`,`performer_role`,`recipient_role`,`subject`,`body`)
SELECT @notification_id, @email_media, @locale, et.`performer_role`, et.`recipient_role`, et.subject,et.body FROM `ohrm_email` e
LEFT JOIN `ohrm_email_template` et ON et.email_id = e.id
WHERE e.name = 'appraisal.submitted' AND et.`recipient_role`!='Subscriber';

-- Appraisal Rejected
SET @notification_id := (SELECT `id` FROM `ohrm_notification` WHERE `name`='Employee Appraisal Rejected Notification');
-- DELETE FROM `ohrm_notification_template` WHERE notification_id=@notification_id;

UPDATE ohrm_notification_template SET body='Dear %subscriberName%,

The performance appraisal for the period %from_date% to %to_date% for %Employee_Name% has been rejected.


Thank you
HR Team
(This is a system generated email, please do not reply!) '
WHERE `notification_id`=@notification_id AND `locale`=@locale;

INSERT INTO `ohrm_notification_template` (`notification_id`,`media_id`,`locale`,`performer_role`,`recipient_role`,`subject`,`body`)
SELECT @notification_id, @email_media, @locale, et.`performer_role`, et.`recipient_role`, et.subject,et.body FROM `ohrm_email` e
LEFT JOIN `ohrm_email_template` et ON et.email_id = e.id
WHERE e.name = 'appraisal.rejected' AND et.`recipient_role`!='Subscriber';

-- Appraisal Signed

SET @notification_id := (SELECT `id` FROM `ohrm_notification` WHERE `name`='Employee Appraisal Signed Notification');
-- DELETE FROM `ohrm_notification_template` WHERE notification_id=@notification_id;

UPDATE ohrm_notification_template SET body='Dear %subscriberName%,

The performance appraisal for the period %from_date% to %to_date% for %Employee_Name% has been signed.

Thank you
HR Team
(This is a system generated email, please do not reply!) '
WHERE `notification_id`=@notification_id AND `locale`=@locale;

INSERT INTO `ohrm_notification_template` (`notification_id`,`media_id`,`locale`,`performer_role`,`recipient_role`,`subject`,`body`)
SELECT @notification_id, @email_media, @locale,et.`performer_role`, et.`recipient_role`, et.subject,et.body FROM `ohrm_email` e
LEFT JOIN `ohrm_email_template` et ON et.email_id = e.id
WHERE e.name = 'appraisal.signed' AND et.`recipient_role`!='Subscriber';