CREATE TABLE ohrm_leave_calendar_legend_color (
  id int unsigned not null auto_increment,
  leave_type_id int unsigned not null,
  legend_color int unsigned,
  PRIMARY KEY(id),
  UNIQUE KEY(leave_type_id),
  FOREIGN KEY(leave_type_id) REFERENCES ohrm_leave_type(id) ON DELETE CASCADE
) engine=innodb default charset=utf8;


INSERT INTO `ohrm_config`(`property`, `value`) VALUES ('leave_calendar_yearly_view', '1');

SET @admin_role_id := (SELECT `id` FROM ohrm_user_role WHERE `name` = 'Admin');  
SET @supervisor_role_id := (SELECT `id` FROM ohrm_user_role WHERE `name` = 'Supervisor');  
SET @ess_role_id := (SELECT `id` FROM ohrm_user_role WHERE `name` = 'ESS');   
      
-- INSERT INTO `ohrm_module` (`name`, `status`) VALUES ('leavecalendar', 1);  
      
SET @module_id := (SELECT `id` FROM `ohrm_module` WHERE `name` = 'leave');  
      
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES  
('Show Leave Calendar', @module_id, 'calendar');  
SET @leave_calendar_screen_id := (SELECT LAST_INSERT_ID());  

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES  
('Show Leave Calendar Yearly', @module_id, 'showYearlyLeaveCalendar');  
SET @leave_calendar_yearly_screen_id := (SELECT LAST_INSERT_ID());

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

INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `uisref`, `status`) VALUES
('Leave Calendar', @leave_calendar_screen_id, @pto_leave_management_module_menu_id, 3, 500 , 'leave.calendar', 0);
      
INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete) VALUES  
(@admin_role_id, @leave_calendar_screen_id, 1, 0, 0, 0),
(@admin_role_id, @leave_calendar_yearly_screen_id, 1, 0, 0, 0),
(@supervisor_role_id, @leave_calendar_screen_id, 1, 0, 0, 0),
(@ess_role_id, @leave_calendar_screen_id, 1, 0, 0, 0),
(@supervisor_role_id, @leave_calendar_yearly_screen_id, 1, 0, 0, 0),
(@ess_role_id, @leave_calendar_yearly_screen_id, 1, 0, 0, 0);

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('leave_calendar', 'Leave - Leave Calendar', 1, NULL, NULL, NULL);

SET @datagroup_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_user_role_data_group` (`user_role_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`, `self`) VALUES
(1, @datagroup_id, 1, 0, 0, 0, 0),
(2, @datagroup_id, 1, 0, 0, 0, 0),
(3, @datagroup_id, 1, 0, 0, 0, 0);

INSERT INTO `ohrm_data_group_screen` (`data_group_id`, `screen_id`, `permission`) VALUES
(@datagroup_id, @leave_calendar_screen_id, 1),
(@datagroup_id, @leave_calendar_yearly_screen_id, 1);

INSERT INTO `ohrm_data_group_screen` (`data_group_id`, `screen_id`, `permission`, `self`) VALUES
(@datagroup_id, @leave_calendar_screen_id, 1, 1),
(@datagroup_id, @leave_calendar_yearly_screen_id, 1, 1);

INSERT INTO ohrm_user_role_type_data_group VALUES
(1, @datagroup_id, 1, 0, 0, 0);

-- leave calendar permission for HOD
SET @hod_user_role_id = (SELECT `id` FROM ohrm_user_role WHERE `name` = 'HeadOfDepartment' AND `is_predefined` = 1);
SET @leave_calendar_data_group_id = (SELECT `id` FROM ohrm_data_group WHERE `name` = 'leave_calendar');
INSERT INTO `ohrm_user_role_data_group` (`user_role_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`, `self`) VALUES
      (@hod_user_role_id, @leave_calendar_data_group_id, 1, 0, 0, 0, 0);

-- adding leave calendar configuration screen admin can access
INSERT INTO `ohrm_screen` (`name`, `module_id`, `action_url`) VALUES('Leave Calendar Configuration', @module_id, 'leaveCalendarConfiguration');
SET @leave_calendar_configuration_screen_id := (SELECT id FROM ohrm_screen WHERE `action_url` = 'leaveCalendarConfiguration');
INSERT INTO `ohrm_user_role_screen` (`user_role_id`, `screen_id`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES (@admin_role_id, @leave_calendar_configuration_screen_id, 1, 0, 1, 0);

-- adding data groups for leave calendar configurations
INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES ('leave_calendar_configuration', 'Leave - Calendar Configuration', 1, NULL, 1, NULL);

SET @leave_calendar_configuration_data_group_id := (SELECT id FROM `ohrm_data_group` WHERE `name` = 'leave_calendar_configuration');
SET @admin_role_type_id := (SELECT id FROM `ohrm_user_role_type` WHERE `name` = 'Admin');

INSERT INTO `ohrm_data_group_screen` (`data_group_id`, `screen_id`, `permission`, `self`) VALUES (@leave_calendar_configuration_data_group_id, @leave_calendar_configuration_screen_id, 1, 0);
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_calendar_configuration_data_group_id, 1, NULL, 1, NULL, 0);
INSERT INTO `ohrm_user_role_type_data_group` (`user_role_type_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES (@admin_role_type_id, @leave_calendar_configuration_data_group_id, 1, NULL, 1, NULL);

-- adding default leave calendar configurations
INSERT INTO `ohrm_config` (`property`, `value`) VALUES ('leave.calendar_configurations', '{"ess_see_ess":1,"ess_see_ess_exclude_leave_status":[0,1,-1],"ess_see_supervisor":false,"ess_see_supervisor_exclude_leave_status":[0,1,-1],"supervisor_see_ess":true,"supervisor_see_ess_exclude_leave_status":[0],"admin_see_ess":true,"admin_see_ess_exclude_leave_status":[0],"exclude_leave_type":[]}');

-- adding menu items
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`, `menu_category`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `image_name`, `uisref`, `status`, `include_attachment_section`) VALUES
('Leave Calendar', NULL, @leave_calendar_configuration_screen_id, @leave_configure_menu_id, 4, '1100', NULL, NULL, 'leave.calendar_configuration', '0', NULL);

SET @leave_configure_leave_calendar_menu_id := (SELECT id FROM `ohrm_menu_item` WHERE `menu_title` = 'Leave Calendar' AND `parent_id` = @leave_configure_menu_id);

INSERT INTO `ohrm_menu_item_data_group` (`menu_item_id`, `data_group_id`) VALUES (@leave_configure_leave_calendar_menu_id, @leave_calendar_configuration_data_group_id);