CREATE TABLE IF NOT EXISTS `ohrm_openid_provider` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `provider_name` varchar(40) DEFAULT NULL,  
  `provider_url` varchar(1000) DEFAULT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '1',
  `provider_logo` blob,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

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

ALTER TABLE `ohrm_openid_user_identity`
  ADD CONSTRAINT `ohrm_user_identity_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `ohrm_user` (`id`) ON DELETE SET NULL;
ALTER TABLE `ohrm_openid_user_identity`
  ADD CONSTRAINT `ohrm_user_identity_ibfk_2` FOREIGN KEY (`provider_id`) REFERENCES `ohrm_openid_provider` (`id`) ON DELETE SET NULL;



-- ALTER TABLE `ohrm_openid_user_identity`
-- ADD CONSTRAINT `ohrm_user_identity_pk_1` PRIMARY KEY (`user_id`,`provider_id`)
INSERT INTO `ohrm_config` (`property` ,`value`) VALUES ('domain.name',  'localhost');

SET @config_admin_role_id := (SELECT `id` FROM ohrm_user_role WHERE `name` = 'ConfigAdmin');

INSERT INTO ohrm_screen ( `name`, `module_id`, `action_url`) VALUES ( 'Manage OpenId', 2, 'openIdProvider');
SET @opnid_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
(1, @opnid_screen_id, 1, 1, 1, 0),
(@config_admin_role_id, @opnid_screen_id, 1, 1, 1, 0);

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES 
('openid_configuration', 'HR Administration - Social Media Configuration', 1, NULL, 1, NULL);

SET @openid_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
(1, @openid_configuration_data_group_id, 1, 0, 0, 0, 0),
(@config_admin_role_id, @openid_configuration_data_group_id, 1, 1, 1, 1, 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`) VALUES
(@openid_configuration_data_group_id, @opnid_screen_id, 1),
(@openid_configuration_data_group_id, @view_admin_module_id, 1),
(@openid_configuration_data_group_id, @configure_authentication_screen_id, 1);

INSERT INTO ohrm_user_role_type_data_group(user_role_type_id, data_group_id,can_read,can_create,can_update,can_delete) VALUES
(1, @openid_configuration_data_group_id,1,0,0,0);

INSERT INTO ohrm_config (property, `value`) VALUES  
('openId.provider.added', 'off');

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
   ('Social Media', @opnid_screen_id, @authentication_menu_id, 5, 300, 'admin.openIdProvider', 1);