-- Advanced user role manager
update `ohrm_config` set `value` = 'AdvancedUserRoleManager'
where `property` = 'authorize_user_role_manager_class';

-- Add User Roles menu item.

SET @admin_role_id := (SELECT `id` FROM ohrm_user_role WHERE `name` = 'Admin');
SET @config_admin_role_id := (SELECT `id` FROM ohrm_user_role WHERE `name` = 'ConfigAdmin');
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());

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('Configure Authentication', @admin_module_id, 'authenticationConfiguration');
SET @configure_authentication_screen_id := (SELECT LAST_INSERT_ID());

SET @people_management_pillar_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'People Management' AND `level` = 1);
SET @hr_administration_module_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'HR Administration'
                                                  AND `level` = 2 AND parent_id = @people_management_pillar_menu_id);

INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `uisref`, `status`) VALUES
('Manage User Roles', @view_user_roles_screen_id, @hr_administration_module_menu_id, 3, 150, 'admin.user_roles', 1);

SET @configuration_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'Configurations' AND `level` = 3 AND `parent_id` = @hr_administration_module_menu_id);
INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `status`) VALUES
('Authentication', NULL, @configuration_menu_id, 4, 600, 1);

SET @authentication_menu_id := (SELECT LAST_INSERT_ID());
SET @oauth_screen_id := (SELECT id FROM ohrm_screen WHERE name = 'OAuth' AND module_id = @admin_module_id);

INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `uisref`, `status`) VALUES
    ('Default', @configure_authentication_screen_id, @authentication_menu_id, 5, 100, 'admin.default_authentication_settings', 1),
    ('OAuth Clients', @oauth_screen_id, @authentication_menu_id, 5, 400, 'admin.oauth_clients', 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_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),
(@admin_role_id, @configure_authentication_screen_id, 1, 1, 1, 1),
(@config_admin_role_id, @configure_authentication_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');

SET @screen_id := (SELECT `id` FROM `ohrm_screen` WHERE `action_url` = 'viewAuthorizationEmployeeList');

INSERT INTO `ohrm_default_permitted_screen`(`user_role_type_id`, `screen_id`) VALUES
(1, @screen_id),
(3, @screen_id);

SET @user_list_screen_id := (SELECT `id` FROM `ohrm_screen` WHERE `action_url` = 'viewAuthorizationSystemUsers');

SET @system_user_data_group_id := (SELECT `id` FROM `ohrm_data_group` WHERE `name` = 'system_users');
INSERT INTO `ohrm_data_group_screen`(`data_group_id`, `screen_id`, `permission`) VALUES
(@system_user_data_group_id, @user_list_screen_id, 1);

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

REPLACE INTO ohrm_config VALUES('feature.user_defined_user_roles', 'Yes');

CREATE TABLE `ohrm_data_group_restriction` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `map_xml` TEXT NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

ALTER TABLE  `ohrm_user_role_data_group` ADD `restriction_id` INT( 11 )  NULL AFTER  `self` ;
ALTER TABLE  `ohrm_user_role_data_group` ADD INDEX (  `restriction_id` ) ;
ALTER TABLE ohrm_user_role_data_group ADD CONSTRAINT fk_ohrm_user_role_data_group_restriction FOREIGN KEY (restriction_id) REFERENCES ohrm_data_group_restriction(id);


SET @moduleId := (SELECT `id` FROM `ohrm_module` where `name`='admin');

INSERT INTO `ohrm_screen` (`name`, `module_id`, `action_url`) VALUES
('Restricted UserRoles', @moduleId, 'addRestrictedUserRoles');

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

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('data_group_restriction', 'Data Group Restriction', '1', '1', '1', '1');

SET @dataGroupId := (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`,`restriction_id`)
VALUES (@admin_role_id,@dataGroupId, 1, 1, 1, 1, 0, null);

INSERT INTO `ohrm_user_role_screen` (`user_role_id`,`screen_id`,`can_read`,`can_create`,`can_update`,`can_delete`)
VALUES (@admin_role_id,@screenId , 1, 1, 1, 1);

INSERT INTO `ohrm_data_group_screen` (`data_group_id`,`screen_id`,`permission`,`self`)
VALUES(@dataGroupId, @screenId ,1,1);


CREATE TABLE `ohrm_session_identity` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `sessionId` VARCHAR (255) NOT NULL,
    `hash` VARCHAR(255) NOT NULL,
    `user_id` INT(10) NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `ohrm_session_identity_sessionId` (`sessionId`),
    INDEX ohrm_session_identity_sessionId_created_at (`created_at`),
    CONSTRAINT `fk_ohrm_session_identity_user_id` FOREIGN KEY (`user_id`) REFERENCES `ohrm_user` (`id`) ON DELETE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET=UTF8;
