CREATE TABLE IF NOT EXISTS `ohrm_performance_category` (
        `id` SMALLINT NOT NULL,
        `name` VARCHAR(255) NOT NULL,
        `display_name` VARCHAR(255) NOT NULL,
        `final_rating_percentage_floor_value` FLOAT NOT NULL,
        `value` INT NOT NULL,
        PRIMARY KEY(id)
)  ENGINE = INNODB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `ohrm_potential_category` (
        `id` SMALLINT NOT NULL,
        `name` VARCHAR(255) NOT NULL,
        `display_name` VARCHAR(255) NOT NULL,
        `value` INT NOT NULL,
        PRIMARY KEY(id)
)  ENGINE = INNODB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `ohrm_nine_box_master_data` (
        `id` SMALLINT NOT NULL,
        `name` VARCHAR(255) NOT NULL,
        `display_name` VARCHAR(255) NOT NULL,
        `colour` VARCHAR(255) NOT NULL,
        PRIMARY KEY(id)
)  ENGINE = INNODB DEFAULT CHARSET=utf8;

ALTER TABLE ohrm_performance_appraisal
    ADD CONSTRAINT `fk_ohrm_performance_appraisal_ohrm_performance_category` FOREIGN KEY(`performance_rating_category`) REFERENCES `ohrm_performance_category`(`id`) ON DELETE SET NULL ON UPDATE CASCADE,
    ADD CONSTRAINT `fk_ohrm_performance_appraisal_ohrm_potential_category` FOREIGN KEY(`potential_rating_category`) REFERENCES `ohrm_potential_category`(`id`) ON DELETE SET NULL ON UPDATE CASCADE,
    ADD CONSTRAINT `fk_ohrm_performance_appraisal_ohrm_nine_box_master_data` FOREIGN KEY (`nine_box_category_id`) REFERENCES `ohrm_nine_box_master_data`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

CREATE TABLE IF NOT EXISTS `ohrm_audittrail_9_box_matrix_configuration_trail` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `action_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `action_owner_id` varchar(6),
  `action_owner_name` VARCHAR(300),
  `version_id` INT NOT NULL DEFAULT 0,
  `action` varchar(255) NOT NULL,
  `affected_entity_id` VARCHAR(20),
  `affected_entity_name` VARCHAR(300) NOT NULL DEFAULT '',
  `action_description` MEDIUMTEXT,
  `is_description_formatting_enabled` BOOLEAN NOT NULL DEFAULT FALSE ,
  `app_id` INT DEFAULT NULL,
  `app_name` VARCHAR(1000) DEFAULT NULL,
  `screen_name` VARCHAR(1000) DEFAULT NULL,
   PRIMARY KEY (`id`),
   INDEX audit_9_box_matrix_configuration_trail_idx_app_id (app_id),
   INDEX audit_9_box_matrix_configuration_trail_idx_screen_name (screen_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS ohrm_individual_development_plan_status (
  `state_id` INT NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `display_name` VARCHAR(100) NOT NULL,
  `consider_as_closed` TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY(state_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS ohrm_individual_development_plan (
  `id`               INT          NOT NULL AUTO_INCREMENT,
  `name`             VARCHAR(255) NOT NULL,
  `description`      MEDIUMTEXT   NULL,
  `owner_emp_number` INT(7)       NOT NULL,
  `coach_emp_number` INT(7),
  `initiated_date`   DATE,
  `closed_date`      DATE,
  `state_id`         INT,
  PRIMARY KEY (id),
  CONSTRAINT `fk_ohrm_individual_development_plan_status` FOREIGN KEY (`state_id`) REFERENCES `ohrm_individual_development_plan_status` (`state_id`) ON UPDATE CASCADE ON DELETE SET NULL,
  CONSTRAINT `fk_ohrm_individual_development_plan_owner` FOREIGN KEY (`owner_emp_number`) REFERENCES `hs_hr_employee` (`emp_number`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_ohrm_individual_development_plan_coach` FOREIGN KEY (`coach_emp_number`) REFERENCES `hs_hr_employee` (`emp_number`) ON UPDATE CASCADE ON DELETE SET NULL
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

CREATE TABLE IF NOT EXISTS ohrm_individual_development_plan_goal_targeted_role (
    `id` INT NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL,
    `display_name` VARCHAR(255) NOT NULL,
    `icon` VARCHAR(100),
     PRIMARY KEY(id)
     ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS ohrm_individual_development_plan_goal (
    `id` INT NOT NULL AUTO_INCREMENT,
    `idp_id` INT NOT NULL,
    `goal_id` INT NOT NULL,
    `goal_version` BIGINT(20) DEFAULT NULL,
    `targeted_role_id` INT NOT NULL,
     PRIMARY KEY(id),
     CONSTRAINT `fk_ohrm_individual_development_plan_goal_targeted_role` FOREIGN KEY (`targeted_role_id`) REFERENCES `ohrm_individual_development_plan_goal_targeted_role` (`id`) ON UPDATE CASCADE ON DELETE RESTRICT,
     CONSTRAINT `fk_ohrm_individual_development_plan_goal_performance_goal` FOREIGN KEY (`goal_id`) REFERENCES `ohrm_performance_goal` (`id`) ON UPDATE CASCADE ON DELETE RESTRICT,
     CONSTRAINT `fk_ohrm_individual_development_plan_goal_idp` FOREIGN KEY (`idp_id`) REFERENCES `ohrm_individual_development_plan` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
     ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `ohrm_audittrail_individual_development_plan_trail` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `action_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `action_owner_id` varchar(6),
  `action_owner_name` VARCHAR(300),
  `version_id` INT NOT NULL DEFAULT 0,
  `action` varchar(255) NOT NULL,
  `affected_entity_id` VARCHAR(20),
  `affected_entity_name` VARCHAR(300) NOT NULL DEFAULT '',
  `action_description` MEDIUMTEXT,
  `is_description_formatting_enabled` BOOLEAN NOT NULL DEFAULT FALSE ,
  `app_id` INT DEFAULT NULL,
  `app_name` VARCHAR(1000) DEFAULT NULL,
  `screen_name` VARCHAR(1000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX audit_individual_development_plan_trail_idx_app_id (app_id),
  INDEX audit_individual_development_plan_trail_idx_screen_name (screen_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
