CREATE TABLE ohrm_wizard_screen (
  `id`            int(11)      NOT NULL AUTO_INCREMENT,
  `route`         varchar(255) NOT NULL,
  `title`         varchar(255) NOT NULL,
  `enabled`       tinyint(2)   NOT NULL,
  `show_in_wizard`  tinyint(2)   DEFAULT 1,
  `menu_item_id`    int(11)      NOT NULL UNIQUE,
  `custom_field_screen_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_ohrm_wizard_screen_custom_field_screen` FOREIGN KEY (`custom_field_screen_id`) REFERENCES `ohrm_custom_field_screen`(`id`),
  CONSTRAINT `fk_ohrm_wizard_screen_menu_item_id` FOREIGN KEY (`menu_item_id`) REFERENCES `ohrm_menu_item` (`id`)

)
  ENGINE = InnoDB
  DEFAULT charset = utf8;

RENAME TABLE ohrm_wizard_field_definition TO ohrm_wizard_field;

-- There was confusion in the meaning of is_visible and enabled in ohrm_wizard_section_processor and ohrm_wizard_field
-- In ohrm_wizard_section_processor:
--   *) enabled -> whether section is enabled from UI
--   *) is_visible -> whether is available
-- In ohrm_wizard_field:
--   *) enabled -> whether field is available
--   *) is_visible -> whether field is enabled from UI

-- This is changed as follows
-- enabled: is screen/section/field enabled and available
-- show_in_wizard: show in wizard
-- mandatory_in_wizard: mandatory in wizard
SET @personalDetailsMenuId := (SELECT `id`
                               FROM `ohrm_menu_item`
                               WHERE `menu_title` = 'Personal Details' AND `menu_category` = 'pim_left_menu');
SET @contactDetailsMenuId := (SELECT `id`
                              FROM `ohrm_menu_item`
                              WHERE `menu_title` = 'Contact Details' AND `menu_category` = 'pim_left_menu');
SET @emergencyContactDetailsMenuId := (SELECT `id`
                                       FROM `ohrm_menu_item`
                                       WHERE `menu_title` = 'Emergency Contacts' AND `menu_category` = 'pim_left_menu');
SET @jobDetailsMenuId := (SELECT `id`
                          FROM `ohrm_menu_item`
                          WHERE `menu_title` = 'Job' AND `menu_category` = 'pim_left_menu');
SET @socialMediaMenuId := (SELECT `id`
                           FROM `ohrm_menu_item`
                           WHERE `menu_title` = 'Social Media Details' AND `menu_category` = 'pim_left_menu');
SET @reportToMenuId := (SELECT `id`
                        FROM `ohrm_menu_item`
                        WHERE `menu_title` = 'Report-to' AND `menu_category` = 'pim_left_menu');
SET @dependentMenuId := (SELECT `id`
                         FROM `ohrm_menu_item`
                         WHERE `menu_title` = 'Dependents' AND `menu_category` = 'pim_left_menu');
SET @salaryMenuId := (SELECT `id`
                      FROM `ohrm_menu_item`
                      WHERE `menu_title` = 'Salary' AND `menu_category` = 'pim_left_menu');
SET @qualificationMenuId := (SELECT `id`
                             FROM `ohrm_menu_item`
                             WHERE `menu_title` = 'Qualifications' AND `menu_category` = 'pim_left_menu');
SET @membershipMenuId := (SELECT `id`
                          FROM `ohrm_menu_item`
                          WHERE `menu_title` = 'Memberships' AND `menu_category` = 'pim_left_menu');
SET @directDepositMenuId := (SELECT `id`
                             FROM `ohrm_menu_item`
                             WHERE `menu_title` = 'Direct Deposit' AND `menu_category` = 'pim_left_menu');
SET @immigrationMenuId := (SELECT `id`
                           FROM `ohrm_menu_item`
                           WHERE `menu_title` = 'Immigration' AND `menu_category` = 'pim_left_menu');
SET @taxExemptionsMenuId := (SELECT `id`
                             FROM `ohrm_menu_item`
                             WHERE `menu_title` = 'Tax Exemptions' AND `menu_category` = 'pim_left_menu');

INSERT INTO ohrm_wizard_screen (id, route, title, enabled, show_in_wizard, menu_item_id) VALUES
(1, 'pim.wizard.personal_details', 'Personal Details', 1, 1, @personalDetailsMenuId),
(2, 'pim.wizard.job', 'Job', 1, 1, @jobDetailsMenuId),
(3, 'pim.wizard.salary', 'Salary', 1, 1, @salaryMenuId),
(4, 'pim.wizard.contact_details', 'Contact Details', 1, 1, @contactDetailsMenuId),
(5, 'pim.wizard.social_media', 'Social Media Details', 1, 1, @socialMediaMenuId),
(6, 'pim.wizard.emergency_contact', 'Emergency Contacts', 1, 1, @emergencyContactDetailsMenuId),
(7, 'pim.wizard.dependent', 'Dependents', 1, 1, @dependentMenuId),
(8, 'pim.wizard.immigration', 'Immigration', 1, 1, @immigrationMenuId),
(9, 'pim.wizard.report_to', 'Report-to', 1, 1, @reportToMenuId),
(10, 'pim.wizard.qualifications', 'Qualifications', 1, 1, @qualificationMenuId),
(11, 'pim.wizard.memberships', 'Memberships', 1, 1, @membershipMenuId),
(12, 'pim.wizard.direct_deposit', 'Direct Deposit', 1, 1, @directDepositMenuId),
(13, 'pim.wizard.tax_exemptions', 'Tax Exemptions', 1, 1, @taxExemptionsMenuId);

RENAME TABLE ohrm_wizard_section_processor TO ohrm_wizard_section;

ALTER TABLE `ohrm_wizard_section`
  DROP COLUMN factory_name,
  DROP COLUMN default_data,
  DROP COLUMN menu_item_id,
  ADD wizard_screen_id INT DEFAULT NULL,
  MODIFY COLUMN  `has_list`  tinyint(2) NULL;

UPDATE `ohrm_wizard_section` SET wizard_screen_id = 1 WHERE `name` = 'PimPersonalDetail';
UPDATE `ohrm_wizard_section` SET wizard_screen_id = 2 WHERE `name` = 'PimJob';
UPDATE `ohrm_wizard_section` SET wizard_screen_id = 3 WHERE `name` = 'PimSalary';
UPDATE `ohrm_wizard_section` SET wizard_screen_id = 4 WHERE `name` = 'PimContactDetails';
UPDATE `ohrm_wizard_section` SET wizard_screen_id = 5 WHERE `name` = 'PimSocialMedia';
UPDATE `ohrm_wizard_section` SET wizard_screen_id = 6 WHERE `name` = 'PimEmergencyContacts';
UPDATE `ohrm_wizard_section` SET wizard_screen_id = 7 WHERE `name` = 'PimDependent';
UPDATE `ohrm_wizard_section` SET wizard_screen_id = 8 WHERE `name` = 'PimImmigrationDetails';
UPDATE `ohrm_wizard_section` SET wizard_screen_id = 9 WHERE `name` = 'PimSupervisor';
UPDATE `ohrm_wizard_section` SET wizard_screen_id = 9 WHERE `name` = 'PimSubordinate';
UPDATE `ohrm_wizard_section` SET wizard_screen_id = 10 WHERE `name` = 'PimWorkExperience';
UPDATE `ohrm_wizard_section` SET wizard_screen_id = 10 WHERE `name` = 'PimEducation';
UPDATE `ohrm_wizard_section` SET wizard_screen_id = 10 WHERE `name` = 'PimSkill';
UPDATE `ohrm_wizard_section` SET wizard_screen_id = 10 WHERE `name` = 'PimLanguage';
UPDATE `ohrm_wizard_section` SET wizard_screen_id = 10 WHERE `name` = 'PimLicense';
UPDATE `ohrm_wizard_section` SET wizard_screen_id = 11 WHERE `name` = 'PimMembership';
UPDATE `ohrm_wizard_section` SET wizard_screen_id = 12 WHERE `name` = 'PimDirectDeposit';
UPDATE `ohrm_wizard_section` SET wizard_screen_id = 13 WHERE `name` = 'PimTaxExemptions';
UPDATE `ohrm_wizard_section` SET wizard_screen_id = 2  WHERE `name` = 'PimCustomFieldJob-Standard';

ALTER TABLE `ohrm_wizard_section`
  MODIFY COLUMN wizard_screen_id INT NOT NULL,
  CHANGE COLUMN enabled show_in_wizard tinyint(2) NOT NULL,
  CHANGE COLUMN is_visible enabled tinyint(2) NOT NULL,
  ADD CONSTRAINT `fk_ohrm_wizard_section_processor_wizard_screen` FOREIGN KEY (`wizard_screen_id`) REFERENCES `ohrm_wizard_screen` (`id`);


ALTER TABLE ohrm_wizard_field
  DROP FOREIGN KEY fk_ohrm_wizard_field_definition_form_input_type,
  DROP FOREIGN KEY fk_ohrm_wizard_field_definition_custom_field,
  DROP COLUMN validation_message,
  DROP COLUMN max_length,
  DROP COLUMN form_input_type_id,
  DROP COLUMN getter,
  DROP COLUMN setter,
  DROP COLUMN validator,
  DROP COLUMN data_service,
  DROP COLUMN js_action,
  DROP COLUMN entity,
  CHANGE is_visible show_in_wizard tinyint(2) NOT NULL,
  CHANGE is_mandatory mandatory_in_wizard tinyint(2) NOT NULL,
  CHANGE processor_id wizard_section_id int(11) NOT NULL,
  ADD CONSTRAINT `fk_ohrm_wizard_field_custom_field` FOREIGN KEY (`custom_field_id`) REFERENCES `ohrm_custom_field` (`id`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT,
  ADD CONSTRAINT `fk_ohrm_wizard_field_wizard_section` FOREIGN KEY (`wizard_section_id`) REFERENCES `ohrm_wizard_section` (`id`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT;



-- Need to flip meaning of read_only field in ohrm_wizard_field. Currently read_only = 0 means read_only true !!
UPDATE ohrm_wizard_field SET `read_only` = CASE WHEN (`read_only` = 0) THEN 1 ELSE 0 END;

DELETE FROM ohrm_wizard_field WHERE field_name = 'province_us';

SET @personalDetailsId := (SELECT id FROM `ohrm_wizard_section` WHERE `name` = 'PimPersonalDetail');
SET @dependentId := (SELECT id FROM `ohrm_wizard_section` WHERE `name` = 'PimDependent');
SET @immigrationWizardSectionId := (SELECT id FROM `ohrm_wizard_section` WHERE `name` = 'PimImmigrationDetails');
SET @membershipWizardSectionId := (SELECT id FROM `ohrm_wizard_section` WHERE `name` = 'PimMembership');
SET @socialMediaWizardSectionId := (SELECT id FROM `ohrm_wizard_section` WHERE `name` = 'PimSocialMedia');
SET @taxExemptionWizardSectionId := (SELECT id FROM `ohrm_wizard_section` WHERE `name` = 'PimTaxExemptions');
SET @jobWizardSectionId := (SELECT id FROM `ohrm_wizard_section` WHERE `name` = 'PimJob');

-- Renaming fields
UPDATE ohrm_wizard_field SET `field_name` = 'licenseNo' WHERE `field_name` = 'txtLicenNo' AND wizard_section_id = @personalDetailsId;
UPDATE ohrm_wizard_field SET `field_name` = 'emp_birthday' WHERE `field_name` = 'dateOfBirth' AND wizard_section_id = @personalDetailsId;
UPDATE ohrm_wizard_field SET `field_name` = 'emp_gender' WHERE `field_name` = 'gender' AND wizard_section_id = @personalDetailsId;
UPDATE ohrm_wizard_field SET `field_name` = 'emp_marital_status' WHERE `field_name` = 'maritalStatus' AND wizard_section_id = @personalDetailsId;
UPDATE ohrm_wizard_field SET `field_name` = 'emp_dri_lice_exp_date' WHERE `field_name` = 'txtLicExpDate' AND wizard_section_id = @personalDetailsId;
UPDATE ohrm_wizard_field SET `field_name` = 'nation_code' WHERE `field_name` = 'nationality' AND wizard_section_id = @personalDetailsId;
UPDATE ohrm_wizard_field SET `field_name` = 'eeo_race_ent' WHERE `field_name` = 'eeo_race_and_ethnicity' AND wizard_section_id = @personalDetailsId;
UPDATE ohrm_wizard_field SET `field_name` = 'ssn' WHERE `field_name` = 'mainId' AND wizard_section_id = @personalDetailsId;
UPDATE ohrm_wizard_field SET `field_name` = 'relationship_type' WHERE `field_name` = 'relationship' AND wizard_section_id = @dependentId;
UPDATE ohrm_wizard_field SET `field_name` = 'nation_code' WHERE `field_name` = 'dependent_nationality' AND wizard_section_id = @dependentId;
UPDATE ohrm_wizard_field SET `field_name` = 'status' WHERE `field_name` = 'immigrationStatus' AND wizard_section_id = @immigrationWizardSectionId;
UPDATE ohrm_wizard_field SET `field_name` = 'membership' WHERE `field_name` = 'membershipId' AND wizard_section_id = @membershipWizardSectionId;
UPDATE ohrm_wizard_field SET `field_name` = 'federalStatus' WHERE `field_name` = 'federal_income_tax_status' AND wizard_section_id = @taxExemptionWizardSectionId;
UPDATE ohrm_wizard_field SET `field_name` = 'federalExemptions' WHERE `field_name` = 'federal_income_tax_exemption' AND wizard_section_id = @taxExemptionWizardSectionId;
UPDATE ohrm_wizard_field SET `field_name` = 'state' WHERE `field_name` = 'state_us' AND wizard_section_id = @taxExemptionWizardSectionId;
UPDATE ohrm_wizard_field SET `field_name` = 'stateStatus' WHERE `field_name` = 'state_income_tax_status' AND wizard_section_id = @taxExemptionWizardSectionId;
UPDATE ohrm_wizard_field SET `field_name` = 'stateExemptions' WHERE `field_name` = 'state_income_tax_exemption' AND wizard_section_id = @taxExemptionWizardSectionId;
UPDATE ohrm_wizard_field SET `field_name` = 'unemploymentState' WHERE `field_name` = 'unemployment_state' AND wizard_section_id = @taxExemptionWizardSectionId;
UPDATE ohrm_wizard_field SET `field_name` = 'workState' WHERE `field_name` = 'work_state' AND wizard_section_id = @taxExemptionWizardSectionId;
UPDATE ohrm_wizard_field SET `field_name` = 'contract_start_date' WHERE `field_name` = 'contractStartDate' AND wizard_section_id = @jobWizardSectionId;
UPDATE ohrm_wizard_field SET `field_name` = 'contract_end_date' WHERE `field_name` = 'contractEndDate' AND wizard_section_id = @jobWizardSectionId;

INSERT INTO `ohrm_wizard_field`
    (`field_name`, `mandatory_in_wizard`, `show_in_wizard`, `sequence`, `wizard_section_id`, `label`, `read_only`, `enabled`)
VALUES
    ('middleName', 0, 1, 6, @personalDetailsId, 'Middle Name', 0, 1);

INSERT INTO `ohrm_wizard_field`
    (`field_name`, `mandatory_in_wizard`, `show_in_wizard`, `sequence`, `wizard_section_id`, `label`, `read_only`, `enabled`)
VALUES
    ('allowShare', 0, 1, 440, @socialMediaWizardSectionId, 'Allow share in public', 0, 1);

INSERT INTO `ohrm_wizard_field`
(`field_name`, `mandatory_in_wizard`, `show_in_wizard`, `sequence`, `wizard_section_id`, `label`, `read_only`, `enabled`)
VALUES
  ('job_spec', 0, 1, 190, 2, 'Job Specification', 0, 1);




