CREATE TABLE `ohrm_eeo_job_category` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  PRIMARY KEY (`id`));

CREATE TABLE `ohrm_eeo_pay_grade` (
  `id` INT NOT NULL,
  `name` VARCHAR(45) NULL,
  PRIMARY KEY (`id`));

CREATE TABLE `ohrm_eeo_race_ethnicity` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  PRIMARY KEY (`id`));


INSERT INTO  `ohrm_eeo_job_category` (`id`, `name`) VALUES
('1', 'Executive/Senior Level Officials and Managers'),
('2', 'First/Mid Level Officials and Managers'),
('3', 'Professionals'),
('4', 'Technicians'),
('5', 'Sales Workers'),
('6', 'Administrative Support Workers'),
('7', 'Craft Workers'),
('8', 'Operatives'),
('9', 'Laborers and Helpers'),
('10', 'Service Workers');

INSERT INTO  `ohrm_eeo_pay_grade` (`id`, `name`) VALUES
('1', '$19239 and under'),
('2', '$19240 - $ 24439'),
('3', '$24440 - $ 30679'),
('4', '$30680 - $ 38999'),
('5', '$39000 - $ 49919'),
('6', '$49920 - $ 62919'),
('7', '$62920 - $ 80079'),
('8', '$80080 - $101919'),
('9', '$101920 - $12895'),
('10', '$128960 - $163799'),
('11', '$163800 - $207999'),
('12', '$208000 and over');

INSERT INTO `ohrm_eeo_race_ethnicity` (`id`, `name`) VALUES
('1', 'Hispanic or Latino'),
('2', 'White'),
('3', 'Black or African American'),
('4', 'Native Hawaiian'),
('5', 'Other Pacific Islander'),
('6', 'Asian'),
('7', 'American Indian or Alaska Native'),
('8', 'Two or More Races');


ALTER TABLE `hs_hr_employee` ADD COLUMN `eeo_job_category` INT DEFAULT NULL AFTER `has_work_week`;

ALTER TABLE `hs_hr_employee` ADD COLUMN `eeo_paygrade` INT DEFAULT NULL AFTER `eeo_job_category`;

ALTER TABLE `hs_hr_employee` ADD COLUMN `eeo_race_ent` INT DEFAULT NULL AFTER `eeo_paygrade`;

ALTER TABLE `hs_hr_employee` ADD COLUMN `flsa_exempt` INT DEFAULT NULL AFTER `eeo_race_ent`;

ALTER TABLE `ohrm_job_record` ADD COLUMN `eeo_job_category` INT DEFAULT NULL AFTER `cost_centre_id`;

ALTER TABLE `ohrm_job_record` ADD COLUMN `flsa_exempt` INT DEFAULT NULL AFTER `eeo_job_category`;

ALTER TABLE `ohrm_salary_record` ADD COLUMN `eeo_paygrade` INT DEFAULT NULL AFTER `comment`;

ALTER TABLE `ohrm_job_record_history` ADD COLUMN `eeo_job_category` INT NULL AFTER `cost_centre_id`;

ALTER TABLE `ohrm_job_record_history` ADD COLUMN `flsa_exempt` INT NULL AFTER `eeo_job_category`;

ALTER TABLE `ohrm_location` ADD COLUMN `eeo_applicable` INT DEFAULT NULL AFTER `notes`;

ALTER TABLE `ohrm_organization_gen_info` ADD COLUMN `eeo_applicable` INT(5) NULL AFTER `note`;

ALTER TABLE `ohrm_salary_history_record` ADD COLUMN `eeo_paygrade` VARCHAR(60) NULL AFTER `pay_grade` ;


ALTER TABLE `ohrm_job_record`
ADD CONSTRAINT `ohrm_job_record_ohrm_eeo_job_category` FOREIGN KEY (`eeo_job_category`)
REFERENCES `ohrm_eeo_job_category`(`id`) ON DELETE SET NULL;

ALTER TABLE `ohrm_salary_record`
ADD CONSTRAINT `fk_ohrm_salary_record_eeo_paygrade` FOREIGN KEY (`eeo_paygrade`)
REFERENCES `ohrm_eeo_pay_grade`(`id`) ON DELETE RESTRICT;

/********************* eeo menu item ******************/

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

INSERT INTO `ohrm_screen` (`name`, `module_id`, `action_url`) VALUES
('Process EEO Filling', @module_id, 'processEEOFilling');

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

SET @organization_menu_item_id := (SELECT `id` FROM `ohrm_menu_item` WHERE `menu_title`='Organization');

INSERT INTO `ohrm_menu_item` (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `uisref`, `status`) VALUES
('Process EEO Filing', @screen_id, @organization_menu_item_id, 3, 500, NULL, 'admin.processEEOFilling', 0);

SET @admin_user_role_id := (SELECT `id` FROM `ohrm_user_role` WHERE `name`='Admin');
INSERT INTO `ohrm_user_role_screen` (`user_role_id`, `screen_id`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
(@admin_user_role_id, @screen_id, 1, 1, 1, 1);

INSERT INTO `ohrm_data_group` ( `name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
( 'admin_process_eeo_filling', 'Admin - Process EEO Filing', 1, 1, 1, 1);

SET @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
      (@admin_user_role_id, @data_group_id, 1, 1, 1, 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
(1, @data_group_id,1,0,0,0);

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

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

INSERT INTO `ohrm_data_group_screen`(`data_group_id`, `screen_id`, `permission`) VALUES
(@data_group_id, @view_admin_module_id, 1);