-- orangehrmLeavePlugin/install/dbscript.sql
SET @action_notification_type := (SELECT id FROM ohrm_notification_type where `name`='Action Based');
SET @leave_workflow_id := (SELECT id FROM ohrm_workflow where `name`='leave');
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 @email_media := (SELECT id FROM ohrm_notification_media WHERE `name` = 'Email');


-- Leave Workflow
-- TODO: supported_media, subscription_template

-- APPLY - default sent to supervisor
INSERT INTO `ohrm_notification` 
    (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`) VALUES
    ('Leave Applications', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification', 
    '{"email_processor":"LeaveEmailProcessor"}', @leave_workflow_id, 'APPLY');
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, 
'Leave Notification - %performerFullName% Applied for %numberOfDays% Day(s) of %leaveType% Leave',
'%performerFullName% has applied for leave. The leave details:

%leaveDetails%
%additionalLeaveDetails%

You can use the below link to directly access the leave.
%loginUrl%

Thank you.

This is an automated notification.'
);

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

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) SELECT
    s.id, @subscriber_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;

-- ASSIGN - sent to ess, supervisor
INSERT INTO `ohrm_notification` 
    (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`) VALUES
    ('Leave Assignments', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification', 
    '{"email_processor":"LeaveEmailProcessor"}', @leave_workflow_id, 'ASSIGN');
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, 
'Leave Notification - %performerFullName% Has Assigned Leave to %assigneeFullName%',
'%performerFullName% has assigned leave to %assigneeFullName%. The leave details:

%leaveDetails%
%additionalLeaveDetails%

Thank you.

This is an automated notification.'
);

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


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) SELECT
    s.id, @subscriber_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, type, name) VALUES
    (@email_media, NULL, 'Custom', 'Affected Employee');

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;

-- APPROVE - sent to ess
INSERT INTO `ohrm_notification` 
    (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`) VALUES
    ('Leave Approvals', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification', 
    '{"email_processor":"LeaveEmailProcessor"}', @leave_workflow_id, 'APPROVE');
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, 
'Leave Notification - %performerFullName% Has Approved Leave Request of %applicantFullName%',
'%performerFullName% has approved leave request of %applicantFullName%. The leave details:

%leaveDetails%

Thank you.

This is an automated notification.'
);

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

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, type, name) VALUES
    (@email_media, NULL, 'Custom', 'Affected Employee');

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;


-- CANCEL - if done by ess: sent to supervisor. If done by someone else. sent to ess
INSERT INTO `ohrm_notification` 
    (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`) VALUES
    ('Leave Cancellations', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification', 
     '{"email_processor":"LeaveEmailProcessor"}', @leave_workflow_id, 'CANCEL');
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, 
'Leave Notification - %performerFullName% Has Canceled Leave',
'%performerFullName% has cancelled leave. The leave details:

%leaveDetails%
%additionalLeaveCancelDetails%

Thank you.

This is an automated notification.'
);

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

-- Performed by ESS
INSERT INTO `ohrm_notification_subscription`
    (notification_id, workflow_item_id) SELECT
    @notification_id, id FROM ohrm_workflow_state_machine WHERE `workflow` = @leave_workflow_id AND `action` = 'CANCEL' AND `role` = 'ESS';
SET @last_subscription_id := LAST_INSERT_ID();

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) SELECT
    s.id, @subscriber_id FROM ohrm_notification_subscription s WHERE s.id >= @last_subscription_id;

-- Not Performed by ESS
INSERT INTO `ohrm_notification_subscription`
    (notification_id, workflow_item_id) SELECT
    @notification_id, id FROM ohrm_workflow_state_machine WHERE `workflow` = @leave_workflow_id 
    AND `action` = 'CANCEL' AND `role` <>'ESS';
SET @last_subscription_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, type, name) VALUES
    (@email_media, NULL, 'Custom', 'Affected Employee');

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;

-- REJECT - sent to ess
INSERT INTO `ohrm_notification` 
    (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`) VALUES
    ('Leave Rejections', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification', 
    '{"email_processor":"LeaveEmailProcessor"}', @leave_workflow_id, 'REJECT');
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, 
'Leave Notification - %performerFullName% Has Rejected Leave Request of %applicantFullName%',
'%performerFullName% has rejected leave request of %applicantFullName%. The leave details:

%leaveDetails%

Thank you.

This is an automated notification.'
);


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` = @leave_workflow_id AND `action` = 'REJECT';
SET @last_subscription_id := LAST_INSERT_ID();


INSERT INTO `ohrm_notification_subscriber`(media_id, parameters, type, name) VALUES
    (@email_media, NULL, 'Custom', 'Affected Employee');

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;

-- Change Email: Multiple actions, no subscriptions. Hidden 

INSERT INTO `ohrm_notification` 
    (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `hidden`) VALUES
    ('Leave Change Email', 1, @action_notification_type, 'LegacyEmailProcessorWrapperEventNotification', 
    '{"email_processor":"LeaveEmailProcessor"}', @leave_workflow_id, NULL, 1);
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, 
'Leave Notification - %performerFullName% Has Processed Leave Request of %applicantFullName%',
'%performerFullName% has done the following changes to leave request of %applicantFullName%. The leave details:

%leaveDetails%
%additionalLeaveCancelDetails%
You were sent this mail, as you have subscribed to OrangeHRM leave changes.

Thank you.

This is an automated notification.'
);

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