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 @email_media := (SELECT id FROM ohrm_notification_media WHERE `name` = 'Email');



-- 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`, `workflow_id`, `workflow_action`) VALUES
    ('Appraisal Due Date Notifications', 0, @date_based_notification_type, 'AppraisalEventNotification', NULL, NULL);
SET @notification_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_template` 
(`notification_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@notification_id, @email_media, 'en_US', NULL, NULL, 
'Employee Appraisal Due Date Notification',
'The performance appraisal for the period of FROM_DATE to TO_DATE FOR_EMPLOYEE is due to be completed in DAYSLEFT.

REVIEW_FOR_EMPLOYEE


Thank you,
HR Team'
);

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, '{"prevent_delete":true}', 'Custom', NULL, 'Evaluator');

SET @subscriber_id := LAST_INSERT_ID();

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

-- Appraisal Initiated (workflow id is null to support multiple performance workflows)
INSERT INTO `ohrm_notification` 
    (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`) VALUES
    ('Employee Appraisal Initiated Notification', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification', 
    '{"email_processor":"PerformanceEmailProcessor", "email.id":"appraisal.initiated"}', @main_appraisal_workflow_id, 'Activate');
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_template` 
(`notification_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@notification_id, @email_media, 'en_US', NULL, NULL, 
'Employee Appraisal Initiated Notification',
'The performance appraisal for the period of %from_date% to %to_date% for %Employee_Name% has been activated.

Please log onto OrangeHRM, and submit your review for %Employee_Name%.

Thank you
HR Team    
(This is a system generated email, please do not reply!) ');

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';

-- Appraisal completed
INSERT INTO `ohrm_notification` 
    (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`) VALUES
    ('Employee Appraisal Completed Notification', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification', 
    '{"email_processor":"PerformanceEmailProcessor", "email.id":"appraisal.completed"}', @main_appraisal_workflow_id, 'Complete');
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_template` 
(`notification_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@notification_id, @email_media, 'en_US', NULL, NULL, 
'Employee Appraisal Completed Notification',
'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!) ');

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';

-- Appraisal Submitted
INSERT INTO `ohrm_notification` 
    (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`) VALUES
    ('Employee Appraisal Submitted Notification', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification', 
    '{"email_processor":"PerformanceEmailProcessor", "email.id":"appraisal.submitted"}', @main_appraisal_workflow_id, 'Submit');
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_template` 
(`notification_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@notification_id, @email_media, 'en_US', NULL, NULL, 
'Employee Appraisal Submitted Notification',
'The performance appraisal for the period %from_date% to %to_date% for %Employee_Name% has been submitted. Please log onto OrangeHRM and sign it electronically.

Thank you
HR Team    
(This is a system generated email, please do not reply!) ');

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') AND `action` = 'Submit';
-- Appraisal Rejected
INSERT INTO `ohrm_notification` 
    (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`) VALUES
    ('Employee Appraisal Rejected Notification', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification', 
    '{"email_processor":"PerformanceEmailProcessor", "email.id":"appraisal.rejected"}', @main_appraisal_workflow_id, 'Reject');
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_template` 
(`notification_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@notification_id, @email_media, 'en_US', NULL, NULL, 
'Employee Appraisal Rejected Notification',
'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..

Thank you
HR Team    
(This is a system generated email, please do not reply!) ');

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') AND `action` = 'Reject';
-- Appraisal Signed
INSERT INTO `ohrm_notification` 
    (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`) VALUES
    ('Employee Appraisal Signed Notification', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification', 
    '{"email_processor":"PerformanceEmailProcessor", "email.id":"appraisal.signed"}', @main_appraisal_workflow_id, 'Sign');
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_template` 
(`notification_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@notification_id, @email_media, 'en_US', NULL, NULL, 
'Employee Appraisal Signed Notification',
'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!) ');

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') AND `action` = 'Sign';

-- 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';
