INSERT INTO `ohrm_workflow` (`id`, `name`, `label`, `available_to`, `default_available_to`, `group_name`, `group_label`, `module_id`, `order_no`)
VALUES ('NOMINEE', 'nominee', 'Nominee', '1,3', '2', 'leave', 'Leave', (SELECT `id` FROM `ohrm_module` WHERE `name`='leave'), '2');

INSERT INTO `ohrm_event_listener` (`event`, `listener_class`, `enabled`, `position`) VALUES ('leave.leave_request_change_leave_request_employee_nominees', 'LeaveRequestChangeEmployeeNomineesListener', '1', '22');

SET @section_id = (SELECT id FROM `ohrm_audittrail_section` WHERE `name` = 'LeaveRequest' AND `description` = 'Leave Request');
INSERT INTO `ohrm_audittrail_section_actions` (`section_id`, `action`, `description`) VALUES (@section_id, 'CHANGE LEAVE REQUEST NOMINEES', 'Change Leave Request Nominees ');

SET @nominee_workflow_id = (SELECT `id` FROM `ohrm_workflow` WHERE `name` = 'nominee');

INSERT INTO `ohrm_workflow_state_machine` (`workflow`, `state`, `role`, `action`, `resulting_state`, `roles_to_notify`, `screens_to_show`, `priority`) VALUES
(@nominee_workflow_id, 'INITIAL', 'ADMIN', 'Add', 'ADDED', NULL, 'INVISIBLE', 0),
(@nominee_workflow_id, 'INITIAL', 'SUPERVISOR', 'Add', 'ADDED', NULL, 'INVISIBLE', 0),
(@nominee_workflow_id, 'INITIAL', 'HEADOFDEPARTMENT', 'Add', 'ADDED', NULL, 'INVISIBLE', 0),
(@nominee_workflow_id, 'INITIAL', 'ESS User', 'Add', 'NOMINATED', NULL, 'INVISIBLE', 0),

(@nominee_workflow_id, 'NOMINATED', 'ADMIN', 'Edit', 'NOMINATED', NULL, 'INVISIBLE', 0),
(@nominee_workflow_id, 'NOMINATED', 'SUPERVISOR', 'Edit', 'NOMINATED', NULL, 'INVISIBLE', 0),
(@nominee_workflow_id, 'NOMINATED', 'HEADOFDEPARTMENT', 'Edit', 'NOMINATED', NULL, 'INVISIBLE', 0),
(@nominee_workflow_id, 'NOMINATED', 'ESS User', 'Edit', 'NOMINATED', NULL, 'INVISIBLE', 0),

(@nominee_workflow_id, 'NOMINATED', 'SYSTEM', 'Add', 'ADDED', NULL, 'INVISIBLE', 0),
(@nominee_workflow_id, 'INITIAL', 'SYSTEM', 'Add', 'ADDED', NULL, 'INVISIBLE', 0),
(@nominee_workflow_id, 'INITIAL', 'SYSTEM', 'Add', 'NOMINATED', NULL, 'INVISIBLE', 0),

(@nominee_workflow_id, 'ADDED', 'ADMIN', 'Edit', 'ADDED', NULL, 'INVISIBLE', 0),

(@nominee_workflow_id, 'ADDED', 'SYSTEM', 'Freez', 'FROZEN', NULL, 'INVISIBLE', 0),
(@nominee_workflow_id, 'NOMINATED', 'SYSTEM', 'Freez', 'FROZEN', NULL, 'INVISIBLE', 0),

(@nominee_workflow_id, 'NOMINATED', 'SYSTEM', 'Remove', 'REMOVED', NULL, 'INVISIBLE', 0),
(@nominee_workflow_id, 'NOMINATED', 'ADMIN', 'Remove', 'REMOVED', NULL, 'INVISIBLE', 0),
(@nominee_workflow_id, 'NOMINATED', 'SUPERVISOR', 'Remove', 'REMOVED', NULL, 'INVISIBLE', 0),
(@nominee_workflow_id, 'NOMINATED', 'HEADOFDEPARTMENT', 'Remove', 'REMOVED', NULL, 'INVISIBLE', 0),
(@nominee_workflow_id, 'NOMINATED', 'ESS User', 'Remove', 'REMOVED', NULL, 'INVISIBLE', 0),
(@nominee_workflow_id, 'ADDED', 'SYSTEM', 'Remove', 'REMOVED', NULL, 'INVISIBLE', 0),
(@nominee_workflow_id, 'ADDED', 'ADMIN', 'Remove', 'REMOVED', NULL, 'INVISIBLE', 0);

SET @leave_workflow_id := (SELECT id FROM `ohrm_workflow` WHERE `name` = 'leave');

SET @NOMINATED_to_ADDED_by_SYSTEM_workflow_id := (SELECT id FROM `ohrm_workflow_state_machine` WHERE `state` = 'NOMINATED' AND `role` = 'SYSTEM' AND `resulting_state` = 'ADDED');
SET @INITIAL_to_ADDED_by_SYSTEM_workflow_id := (SELECT id FROM `ohrm_workflow_state_machine` WHERE `state` = 'INITIAL' AND `role` = 'SYSTEM' AND `resulting_state` = 'ADDED');
SET @ADDED_to_FROZEN_by_SYSTEM_workflow_id := (SELECT id FROM `ohrm_workflow_state_machine` WHERE `state` = 'ADDED' AND `role` = 'SYSTEM' AND `resulting_state` = 'FROZEN');
SET @NOMINATED_to_FROZEN_by_SYSTEM_workflow_id := (SELECT id FROM `ohrm_workflow_state_machine` WHERE `state` = 'NOMINATED' AND `role` = 'SYSTEM' AND `resulting_state` = 'FROZEN');
SET @INITIAL_to_NOMINATED_by_SYSTEM_workflow_id := (SELECT id FROM `ohrm_workflow_state_machine` WHERE `state` = 'INITIAL' AND `role` = 'SYSTEM' AND `resulting_state` = 'NOMINATED');

INSERT INTO `ohrm_workflow_state_machine_dependancy` (master_state_change_id, slave_state_change_id, trigger_verification) SELECT
                                                                                                                            wsm.id,
                                                                                                                            @NOMINATED_to_ADDED_by_SYSTEM_workflow_id,
                                                                                                                            'VerifyEmployeeNomineeStateChange'
                                                                                                                            FROM `ohrm_workflow_state_machine` wsm WHERE
                                                                                                                            wsm.workflow IN (SELECT `id` FROM `ohrm_workflow` WHERE `group_name` = 'leave' AND `name` <> 'nominee') AND
                                                                                                                            wsm.state <> 'INITIAL' AND
                                                                                                                            wsm.resulting_state IN('SCHEDULED');

INSERT INTO `ohrm_workflow_state_machine_dependancy` (master_state_change_id, slave_state_change_id, trigger_verification) SELECT
                                                                                                                            wsm.id,
                                                                                                                            @INITIAL_to_NOMINATED_by_SYSTEM_workflow_id,
                                                                                                                            'VerifyEmployeeNomineeStateChange'
                                                                                                                            FROM `ohrm_workflow_state_machine` wsm WHERE
                                                                                                                            wsm.workflow IN (SELECT `id` FROM `ohrm_workflow` WHERE `group_name` = 'leave' AND `name` <> 'nominee') AND
                                                                                                                            wsm.state = 'INITIAL' AND
                                                                                                                            wsm.action = 'APPLY' AND
                                                                                                                            wsm.resulting_state LIKE '%PENDING%';

INSERT INTO `ohrm_workflow_state_machine_dependancy` (master_state_change_id, slave_state_change_id, trigger_verification) SELECT
                                                                                                                            wsm.id,
                                                                                                                            @INITIAL_to_ADDED_by_SYSTEM_workflow_id,
                                                                                                                            'VerifyEmployeeNomineeStateChange'
                                                                                                                            FROM `ohrm_workflow_state_machine` wsm WHERE
                                                                                                                            wsm.workflow IN (SELECT `id` FROM `ohrm_workflow` WHERE `group_name` = 'leave' AND `name` <> 'nominee') AND
                                                                                                                            wsm.state = 'INITIAL' AND
                                                                                                                            wsm.resulting_state IN('SCHEDULED');

INSERT INTO `ohrm_workflow_state_machine_dependancy` (master_state_change_id, slave_state_change_id, trigger_verification) SELECT
                                                                                                                            wsm.id,
                                                                                                                            @NOMINATED_to_FROZEN_by_SYSTEM_workflow_id,
                                                                                                                            'VerifyEmployeeNomineeStateChange'
                                                                                                                            FROM `ohrm_workflow_state_machine` wsm WHERE
                                                                                                                            wsm.workflow IN (SELECT `id` FROM `ohrm_workflow` WHERE `group_name` = 'leave' AND `name` <> 'nominee') AND
                                                                                                                            wsm.resulting_state IN ('CANCELLED','REJECTED') AND
                                                                                                                            (wsm.state NOT LIKE '%SCHEDULED%' AND wsm.state NOT LIKE '%TAKEN%') AND
                                                                                                                            wsm.role <> 'ADMIN';

INSERT INTO `ohrm_workflow_state_machine_dependancy` (master_state_change_id, slave_state_change_id, trigger_verification) SELECT
                                                                                                                            wsm.id,
                                                                                                                            @ADDED_to_FROZEN_by_SYSTEM_workflow_id,
                                                                                                                            'VerifyEmployeeNomineeStateChange'
                                                                                                                            FROM `ohrm_workflow_state_machine` wsm WHERE
                                                                                                                            wsm.workflow IN (SELECT `id` FROM `ohrm_workflow` WHERE `group_name` = 'leave' AND `name` <> 'nominee') AND
                                                                                                                            wsm.resulting_state IN ('CANCELLED','REJECTED') AND
                                                                                                                            (wsm.state LIKE '%SCHEDULED%' OR wsm.state LIKE '%TAKEN%') AND
                                                                                                                            wsm.role <> 'ADMIN';

INSERT INTO `ohrm_workflow_state_machine_dependancy` (master_state_change_id, slave_state_change_id, trigger_verification) SELECT
                                                                                                                            wsm.id,
                                                                                                                            @NOMINATED_to_FROZEN_by_SYSTEM_workflow_id,
                                                                                                                            'VerifyEmployeeNomineeStateChange'
                                                                                                                            FROM `ohrm_workflow_state_machine` wsm WHERE
                                                                                                                            wsm.workflow IN (SELECT `id` FROM `ohrm_workflow` WHERE `group_name` = 'leave' AND `name` <> 'nominee') AND
                                                                                                                            wsm.resulting_state IN ('CANCELLED','REJECTED') AND
                                                                                                                            (wsm.state NOT LIKE '%SCHEDULED%' AND wsm.state NOT LIKE '%TAKEN%') AND
                                                                                                                            wsm.role = 'ADMIN';

INSERT INTO `ohrm_workflow_state_machine_dependancy` (master_state_change_id, slave_state_change_id, trigger_verification) SELECT
                                                                                                                            wsm.id,
                                                                                                                            @ADDED_to_FROZEN_by_SYSTEM_workflow_id,
                                                                                                                            'VerifyEmployeeNomineeStateChange'
                                                                                                                            FROM `ohrm_workflow_state_machine` wsm WHERE
                                                                                                                            wsm.workflow IN (SELECT `id` FROM `ohrm_workflow` WHERE `group_name` = 'leave' AND `name` <> 'nominee') AND
                                                                                                                            wsm.resulting_state IN ('CANCELLED','REJECTED') AND
                                                                                                                            (wsm.state LIKE '%SCHEDULED%' OR wsm.state LIKE '%TAKEN%') AND
                                                                                                                            wsm.role = 'ADMIN';

SET @notification_type_id := (SELECT `id` FROM `ohrm_notification_type` WHERE `name` = 'Action Based');
SET @leave_module_id := (SELECT `id` FROM `ohrm_module` WHERE `name` = 'leave');
SET @email_media := (SELECT `id` FROM `ohrm_notification_media` WHERE `name` = 'Email');

INSERT INTO `ohrm_notification` (`name`, `is_enable`, `type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`, `hidden`) VALUES
('Nominee Added', '1', @notification_type_id, 'EmployeeNomineeNotificationProcessor', '{"notifyAffectedUserOptionVisible": true}', @leave_workflow_id, NULL, @leave_module_id, '0');
SET @nominee_added_notification := LAST_INSERT_ID();

INSERT INTO `ohrm_notification` (`name`, `is_enable`, `type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`, `hidden`) VALUES
('Nominee Removed', '1', @notification_type_id, 'EmployeeNomineeNotificationProcessor', '{"notifyAffectedUserOptionVisible": true}', @leave_workflow_id, NULL, @leave_module_id, '0');
SET @nominee_removed_notification := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@nominee_added_notification, @email_media, 'en_US',
'Addition of nominee notification sent to subscriber',
'%affectedEmployeeName% is added as a nominee for %leaveRequestOwner%''s leave request',
'<p>Hi %subscriberName%,<br /><br />%affectedEmployeeName% has been added as a nominee by %performerName% for the following leave request made by %leaveRequestOwner%.<br /><br />%body%<br /><br />You received this email because you are a subscriber.<br /><br />Thank you.<br /><br />This is an automated notification.</p>');

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@nominee_added_notification, @email_media, 'en_US',
'Addition of nominee notification sent to Employee',
'You are added as a nominee for %leaveRequestOwner%''s leave request',
'<p>Hi %subscriberName%,<br /><br />You are added as a nominee by %performerName% for the following leave request made by %leaveRequestOwner%.<br /><br />%body%<br /><br />Thank you.<br /><br />This is an automated notification.</p>');

SET @nominee_added_template := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@nominee_removed_notification, @email_media, 'en_US',
'Removal of nominee notification sent to Subscriber',
'%affectedEmployeeName% has been removed from being a nominee for %leaveRequestOwner%''s leave',
'<p>Hi %subscriberName%,<br /><br />%affectedEmployeeName% has been removed from being a nominee by %performerName% for the following leave request made by %leaveRequestOwner%.<br /><br />%body%<br /><br />You received this email because you are a subscriber.<br /><br />Thank you.<br /><br />This is an automated notification.</p>');

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@nominee_removed_notification, @email_media, 'en_US',
'Removal of nominee notification sent to Employee',
'You have been removed from being a nominee for %leaveRequestOwner%''s leave request',
'<p>Hi %subscriberName%,<br /><br />You have been removed from being a nominee by %performerName% for the following leave request made by %leaveRequestOwner%.<br /><br />%body%<br /><br />Thank you.<br /><br />This is an automated notification.</p>');
SET @nominee_removed_template := LAST_INSERT_ID();

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

SET @nominee_notification_subscriber := LAST_INSERT_ID();



INSERT INTO `ohrm_notification_subscription` (`notification_id`, `workflow_item_id`,`notify_affected_employee`) SELECT
                                                                        @nominee_added_notification,
                                                                        wsm.id,
                                                                        0
                                                                        FROM `ohrm_workflow_state_machine` wsm WHERE
                                                                        wsm.workflow = @nominee_workflow_id AND
                                                                        wsm.state = 'INITIAL' AND
                                                                        wsm.action = 'Add';
SET @last_subscription_id:= LAST_INSERT_ID();

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



-- Nominee Removed
INSERT INTO `ohrm_notification_subscription` (`notification_id`, `workflow_item_id`,`notify_affected_employee`) SELECT
                                                                        @nominee_removed_notification,
                                                                        wsm.id,
                                                                        0
                                                                        FROM `ohrm_workflow_state_machine` wsm WHERE
                                                                        wsm.workflow = @nominee_workflow_id AND
                                                                        wsm.action = 'Remove';

SET @last_subscription_id:= LAST_INSERT_ID();

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

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