

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 LAST_INSERT_ID());  
      
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES  
('Show Leave Calendar', @module_id, 'showLeaveCalendar');  
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 @leave_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'Leave' AND `level` = 1); 
      
INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) VALUES  
('Leave Calendar', @leave_calendar_screen_id, @leave_menu_id, 2, 804 , '', 1);
      
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 Calender', 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);

DELIMITER //

CREATE PROCEDURE insert_regional_admin()
  BEGIN
    DECLARE regional_admin_user_role_id, leave_calender_data_group_id INT;
    
    SET regional_admin_user_role_id = (SELECT `id` FROM `ohrm_user_role` WHERE `name` = 'RegionalAdmin');
    SET leave_calender_data_group_id = (SELECT `id` FROM `ohrm_data_group` WHERE `name` = 'leave_calendar');

    IF regional_admin_user_role_id IS NOT NULL THEN
        INSERT INTO `ohrm_user_role_data_group` (`user_role_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`, `self`) VALUES(regional_admin_user_role_id, leave_calender_data_group_id, 1, 0, 0, 0, 0);
    END IF;
  END //


DELIMITER ;

call insert_regional_admin();
