CREATE TABLE `ohrm_position_hierarchy` (
  `id` int(13) NOT NULL AUTO_INCREMENT,
  `job_title_sup` int(13) NOT NULL DEFAULT '0',
  `job_title_sub` int(13) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `ohrm_reporting_method` (
  `id` int(7) NOT NULL,
  `root` int(7) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `ohrm_reporting_method` VALUES (0,0);


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 ('orgChart', 1);  
      
SET @module_id := (SELECT LAST_INSERT_ID());  
      
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`, `route_name`) VALUES
('Config', @module_id, 'configReportingMethod', 'orgchart_configReportingMethod');
SET @config_screen_id := (SELECT LAST_INSERT_ID());  

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`, `route_name`) VALUES
('Define Position', @module_id, 'defineJobTitleHierarchy', 'orgchart_defineJobTitleHierarchy');
SET @position_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`, `route_name`) VALUES
('View', @module_id, 'orgchartView', 'orgchart_orgchartView');
SET @view_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_default_permitted_screen`(`user_role_type_id`, `screen_id`) VALUES 
(2, @view_screen_id);

SET @people_management_pillar_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'People Management' AND parent_id IS NULL AND level = 1);
SET @employee_management_module_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'Employee Management' AND parent_id = @people_management_pillar_menu_id AND level = 2);

INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) VALUES
('Organization Chart', NULL, @employee_management_module_menu_id, 3, 700, '', 1);

SET @orgChart_menu_id := (SELECT LAST_INSERT_ID());
      
INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) VALUES  
('Config', @config_screen_id, @orgChart_menu_id, 4, 100 , '', 1);
      
INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete) VALUES  
(@admin_role_id, @config_screen_id, 1, 0, 0, 0);

INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) VALUES  
('Define Position', @position_screen_id, @orgChart_menu_id, 4, 101 , '', 1);
      
INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete) VALUES  
(@admin_role_id, @position_screen_id, 1, 0, 0, 0);

INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) VALUES  
('View', @view_screen_id, @orgChart_menu_id, 4, 102 , '', 1);
      
INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete) VALUES  
(@admin_role_id, @view_screen_id, 1, 0, 0, 0),
(@supervisor_role_id, @view_screen_id, 1, 0, 0, 0),
(@ess_role_id, @view_screen_id, 1, 0, 0, 0);

UPDATE ohrm_user_role_screen set can_read = 1 WHERE screen_id IN 
(SELECT s.id FROM ohrm_screen AS s LEFT JOIN ohrm_module AS m ON (s.module_id = m.id) WHERE m.name = 'orgChart');
