CREATE TABLE IF NOT EXISTS `ohrm_saml_provider` (
    `id` int(10) NOT NULL AUTO_INCREMENT,
    `name` varchar(255) DEFAULT NULL,
    `type` varchar(10) DEFAULT NULL,
    `metadata` MEDIUMTEXT DEFAULT NULL,
    `username_key` varchar(255) DEFAULT NULL,
    `status` tinyint(1) NOT NULL DEFAULT '1',
    `file_name`  varchar(255) DEFAULT NULL,
    `file_type` varchar(255) DEFAULT NULL,
    `file_size` int(10)  DEFAULT NULL,
    `provider_logo` blob,
      PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;

CREATE TABLE IF NOT EXISTS `ohrm_saml_user_identity` (
  `user_id` int(10) ,
  `provider_id` int(10) ,
  `user_identity` varchar(255) DEFAULT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;

ALTER TABLE `ohrm_saml_user_identity`
  ADD CONSTRAINT  FOREIGN KEY (`user_id`) REFERENCES `ohrm_user` (`id`) ON DELETE SET NULL;
ALTER TABLE `ohrm_saml_user_identity`
  ADD CONSTRAINT  FOREIGN KEY (`provider_id`) REFERENCES `ohrm_saml_provider` (`id`) ON DELETE SET NULL;

CREATE TABLE IF NOT EXISTS `ohrm_saml_provider_attribute` (
    `id` int(10) NOT NULL AUTO_INCREMENT,
    `provider_id` int(10) NOT NULL,
    `token_id` int(10) NOT NULL,
    `claim_name` varchar(255) DEFAULT NULL,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_ohrm_saml_provider_attribute_provider_id` FOREIGN KEY (`provider_id`) REFERENCES `ohrm_saml_provider`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_ohrm_saml_provider_attribute_token_id` FOREIGN KEY (`token_id`) REFERENCES `ohrm_token`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;

SET @module_id := (SELECT `id`  FROM `ohrm_module` WHERE `name` = 'oauth');

INSERT INTO `ohrm_token` ( `name`, `display_name`, `class_name`, `entity_name`, `value_getters`, `data_group_id`, `is_date`, `localize`, `parameters`, `custom_field_id`) VALUES ( 'oauth.username', 'User Name', NULL, 'SystemUser', 'getUserName', NULL, '0', '0', NULL, NULL);
SET @token_id := (SELECT LAST_INSERT_ID());
INSERT INTO `ohrm_restricted_module_token`(  `token_id`, `restricted_module`) VALUES ( @token_id ,@module_id);

INSERT INTO `ohrm_token` ( `name`, `display_name`, `class_name`, `entity_name`, `value_getters`, `data_group_id`, `is_date`, `localize`, `parameters`, `custom_field_id`) VALUES ( 'oauth.empFirstName', 'Employee First Name', NULL, 'Employee', 'getFirstName', NULL, '0', '0', NULL, NULL);
SET @employee_firstName_token_id := (SELECT LAST_INSERT_ID());
INSERT INTO `ohrm_restricted_module_token`(  `token_id`, `restricted_module`) VALUES ( @employee_firstName_token_id ,@module_id);

INSERT INTO `ohrm_token` ( `name`, `display_name`, `class_name`, `entity_name`, `value_getters`, `data_group_id`, `is_date`, `localize`, `parameters`, `custom_field_id`) VALUES ( 'oauth.empFullName', 'Employee Full Name', NULL, 'Employee', 'getFullName', NULL, '0', '0', NULL, NULL);
SET @employee_fullName_token_id := (SELECT LAST_INSERT_ID());
INSERT INTO `ohrm_restricted_module_token`(  `token_id`, `restricted_module`) VALUES ( @employee_fullName_token_id ,@module_id);

INSERT INTO `ohrm_token` ( `name`, `display_name`, `class_name`, `entity_name`, `value_getters`, `data_group_id`, `is_date`, `localize`, `parameters`, `custom_field_id`) VALUES ( 'oauth.email', 'Email', NULL, 'Employee', 'getEmpWorkEmail', NULL, '0', '0', NULL, NULL);
SET @employee_email_token_id := (SELECT LAST_INSERT_ID());
INSERT INTO `ohrm_restricted_module_token`(  `token_id`, `restricted_module`) VALUES ( @employee_email_token_id ,@module_id);

INSERT INTO `ohrm_token` ( `name`, `display_name`, `class_name`, `entity_name`, `value_getters`, `data_group_id`, `is_date`, `localize`, `parameters`, `custom_field_id`) VALUES ( 'oauth.empId', 'Employee ID', NULL, 'Employee', 'getEmployeeId', NULL, '0', '0', NULL, NULL);
SET @employee_id_token_id := (SELECT LAST_INSERT_ID());
INSERT INTO `ohrm_restricted_module_token`(  `token_id`, `restricted_module`) VALUES ( @employee_id_token_id ,@module_id);

-- 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 ( 'Manage SAML Configurations', @admin_module_id, 'samlProvider');
SET @saml_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, @saml_screen_id, 1, 1, 1, 1),
(@admin_role_id, @saml_screen_id, 1, 0, 0, 0);

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('saml_configuration', 'HR Administration - SAML Authentication Configuration', 1, 1, 1, 1);

SET @saml_configuration_data_group_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
(@config_admin_role_id, @saml_configuration_data_group_id, 1, 1, 1, 1, 0),
(@admin_role_id, @saml_configuration_data_group_id, 1, 0, 0, 0, 0);

SET @view_admin_module_id := (SELECT `id` FROM `ohrm_screen` WHERE `module_id` =2 AND `action_url` = 'viewAdminModule');
SET @configure_authentication_screen_id := (SELECT `id` from ohrm_screen where `module_id` = 2 AND `action_url` = 'authenticationConfiguration');

INSERT INTO `ohrm_data_group_screen`(`data_group_id`, `screen_id`, `permission`, `self`) VALUES
(@saml_configuration_data_group_id, @saml_screen_id, 1, 0),
(@saml_configuration_data_group_id, @view_admin_module_id, 1, 0),
(@saml_configuration_data_group_id, @configure_authentication_screen_id, 1, 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, @saml_configuration_data_group_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`, `screen_id`, `parent_id`, `level`, `order_hint`, `uisref`, `status`) VALUES
    ('SAML', @saml_screen_id, @authentication_menu_id, 5, 500, 'admin.samlProvider', 1);
