-- INSERT INTO `ohrm_module` (`name`, `status`) VALUES ('leaveNotification', 1);

-- SET @module_id := (SELECT LAST_INSERT_ID());
SET @leave_module_id := (SELECT `id` FROM `ohrm_module` WHERE `name` = 'leave');

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('Notifications', @leave_module_id, 'viewLeaveNotifications'),
('Add Leave Notifications', @leave_module_id, 'addNotificationRule'),
('Delete Leave Notifications', @leave_module_id, 'deleteLeaveNotification');

SET @view_leave_notifications_screen_id := (SELECT id FROM ohrm_screen WHERE name = 'Notifications');
SET @add_leave_notifications_screen_id := (SELECT id FROM ohrm_screen WHERE name = 'Add Leave Notifications');
SET @delete_leave_notifications_screen_id := (SELECT id FROM ohrm_screen WHERE name = 'Delete Leave Notifications');

SET @compensation_pillar_menu_id := (SELECT `id` FROM `ohrm_menu_item` WHERE `menu_title`= 'Compensation' AND `level` = 1);
SET @pto_leave_management_module_menu_id := (SELECT `id` FROM `ohrm_menu_item` WHERE `menu_title`= 'Leave' AND `parent_id` = @compensation_pillar_menu_id);
SET @leave_configure_menu_id := (SELECT id FROM `ohrm_menu_item` WHERE `parent_id` = @pto_leave_management_module_menu_id AND `menu_title` = 'Configure');

INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `uisref`, `status`) VALUES
('Notifications', @view_leave_notifications_screen_id, @leave_configure_menu_id, 4, 1000, '', 'leave.notifications', 0);

SET @admin_role_id := (SELECT `id` FROM ohrm_user_role WHERE `name` = 'Admin');

INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete) VALUES
(@admin_role_id, @view_leave_notifications_screen_id, 1, 1, 1, 1),
(@admin_role_id, @add_leave_notifications_screen_id, 1, 1, 1, 1),
(@admin_role_id, @delete_leave_notifications_screen_id, 1, 1, 1, 1);

CREATE TABLE IF NOT EXISTS ohrm_employee_on_leave_notification (id BIGINT AUTO_INCREMENT, name VARCHAR(100) NOT NULL, send_on BIGINT, on_next BIGINT, date_created DATE, is_enable TINYINT(1) DEFAULT '1', mail_subject VARCHAR(100), notified_to_job_title_id BIGINT, notified_to_sub_unit_id BIGINT, notified_to_location_id BIGINT, applicable_to_job_title_id BIGINT, applicable_to_sub_unit_id BIGINT, applicable_to_location_id BIGINT, PRIMARY KEY(id)) ENGINE = INNODB;
ALTER TABLE ohrm_employee_on_leave_notification CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('leave_notifications', 'Leave - Leave Notifications', 1, 1, 1, 1);

SET @leave_notifications_data_group_id := (SELECT id FROM ohrm_data_group WHERE name = 'leave_notifications');

INSERT INTO `ohrm_data_group_screen`(`data_group_id`, `screen_id`, `permission`) VALUES
(@leave_notifications_data_group_id, @view_leave_notifications_screen_id, 1),
(@leave_notifications_data_group_id, @add_leave_notifications_screen_id, 1),
(@leave_notifications_data_group_id, @add_leave_notifications_screen_id, 2),
(@leave_notifications_data_group_id, @add_leave_notifications_screen_id, 3),
(@leave_notifications_data_group_id, @delete_leave_notifications_screen_id, 4);

INSERT INTO `ohrm_user_role_data_group`(`user_role_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`, `self`) VALUES 
(@admin_role_id, @leave_notifications_data_group_id, 1,1,1,1,0);

INSERT INTO `ohrm_user_role_type_data_group`(`user_role_type_id`, `data_group_id`) VALUES 
(1,@leave_notifications_data_group_id);

INSERT INTO `ohrm_config`(`property`, `value`) VALUES('leave.on_leave_notification_mail_body', 'Hi %Recipient%,

The following employees will be on leave within the next %numberOfDays% days.

%list%

Thank you.

This is an automated notification.');

INSERT INTO `ohrm_config`(`property`, `value`) VALUES('leave.on_leave_notification_no_employees_mail_body', 'Hi %Recipient%,

There are no employees on leave within the next %numberOfDays% days.

Thank you.

This is an automated notification.');