CREATE TABLE IF NOT EXISTS `ohrm_external_service_type` (
    `id` int(10) NOT NULL AUTO_INCREMENT,
    `name` varchar(50) NOT NULL,
    `display_name` varchar(255) DEFAULT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;

CREATE TABLE IF NOT EXISTS `ohrm_external_service` (
    `id` int(10) NOT NULL AUTO_INCREMENT,
    `name` varchar(30) DEFAULT NULL,
    `status` tinyint(1) NOT NULL DEFAULT '1',
    `type_id` int(10) DEFAULT NULL,
    `url` varchar(2048) DEFAULT NULL,
    `idp_id` int(10) DEFAULT NULL,
    `menu_item_id` int DEFAULT NULL,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_ohrm_menu_item_external_service` FOREIGN KEY (`menu_item_id`) REFERENCES `ohrm_menu_item` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_ohrm_saml_provider_external_service` FOREIGN KEY (`idp_id`) REFERENCES `ohrm_saml_provider` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_ohrm_external_service_type` FOREIGN KEY (`type_id`) REFERENCES `ohrm_external_service_type` (`id`) ON DELETE CASCADE
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;

INSERT INTO `ohrm_external_service_type` (`id`, `name`) VALUES (1, 'url'), (2, 'idp');

-- user role
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 ( 'External Services', @admin_module_id, 'externalService');
SET @external_services_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete) VALUES
(@config_admin_role_id, @external_services_screen_id, 1, 1, 1, 1),
(@admin_role_id, @external_services_screen_id, 1, 0, 0, 0);

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);
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);
SET @authentication_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'Authentication' AND `level` = 4 AND `parent_id` = @configuration_menu_id);

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
('External Services', NULL, @external_services_screen_id, @authentication_menu_id, 5, 350, NULL, NULL, 'admin.externalServices', 1, NULL);

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('external_services', 'HR Administration - External Services Configuration', 1, 1, 1, 1);

SET @external_services_data_group_id := (SELECT LAST_INSERT_ID());
SET @admin_user_roles_data_group_id := (SELECT `id` FROM `ohrm_data_group` WHERE `name` = 'admin_user_roles');

INSERT INTO `ohrm_user_role_data_group` (`user_role_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`, `self`) VALUES
(@config_admin_role_id, @external_services_data_group_id, 1, 1, 1, 1, 0),
(@config_admin_role_id, @admin_user_roles_data_group_id, 1, 0, 0, 0, 0),
(@admin_role_id, @external_services_data_group_id, 1, 0, 0, 0, 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_id, @external_services_data_group_id, 1, 0, 0, 0);