create table ohrm_user_user_role (
    id int auto_increment, 
    user_id int, 
    user_role_id int, 
    rule_xml text, 
    primary key(id)
) engine=innodb default charset=utf8;

alter table ohrm_user_user_role
       add constraint foreign key (user_role_id)
                             references ohrm_user_role(id) on delete cascade;
alter table ohrm_user_user_role
       add constraint foreign key (user_id)
                             references ohrm_user(id) on delete cascade;

-- Advanced user role manager
update `hs_hr_config` set `value` = 'AdvancedUserRoleManager'
where `key` = 'authorize_user_role_manager_class';

-- Add User Roles menu item.

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

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES  
('View User Roles', @admin_module_id, 'viewUserRoles');    
SET @view_user_roles_screen_id := (SELECT LAST_INSERT_ID());  
  
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES  
('Save User Role', @admin_module_id, 'saveUserRole');    
SET @save_user_role_screen_id := (SELECT LAST_INSERT_ID()); 

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES  
('Delete User Roles', @admin_module_id, 'deleteUserRoles');    
SET @delete_user_roles_screen_id := (SELECT LAST_INSERT_ID());    
  
SET @admin_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'Admin' AND `level` = 1);  
SET @user_managemenbt_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'User Management' AND `level` = 2 AND `parent_id` = @admin_menu_id);  
   
INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) VALUES  
('User Roles', @view_user_roles_screen_id, @user_managemenbt_menu_id, 3, 100, '', 0);  
  
INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete) VALUES  
(@admin_role_id, @view_user_roles_screen_id, 1, 1, 1, 1),  
(@admin_role_id, @delete_user_roles_screen_id, 1, 0, 0, 1), 
(@admin_role_id, @save_user_role_screen_id, 1, 1, 1, 1);

-- Insert screen viewAuthorizationSystemUsers (advanced action)
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('User List', 2, 'viewAuthorizationSystemUsers'),
('View Employee List', 3, 'viewAuthorizationEmployeeList');


-- Give 'Admin' access to viewAuthorizationSystemUsers screen
INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete)
SELECT r.id, s.id, 1, 1, 1, 1 FROM ohrm_screen s, ohrm_user_role r WHERE 
    s.action_url in ('viewAuthorizationSystemUsers', 'viewSystemUsers', 'saveSystemUser', 'deleteSystemUsers', 'viewAuthorizationEmployeeList')
    AND r.`name` = 'Admin';

-- Give supervisor access to viewAuthorization employee list
INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete)
SELECT r.id, s.id, 1, 0, 0, 0 FROM ohrm_screen s, ohrm_user_role r WHERE 
    s.action_url in ('viewAuthorizationEmployeeList')
    AND r.`name` = 'Supervisor';