-- -------------
-- Creating tables related to performance
-- -------------

-- updated add form reference to here
CREATE TABLE IF NOT EXISTS `ohrm_performance_appraisal_status` (
  `id` int NOT NULL AUTO_INCREMENT,
  `state_id` int NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `description` VARCHAR(250) NOT NULL,

  PRIMARY KEY(id),
  UNIQUE (`state_id`)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE ohrm_appraisal_template (
    `id` INT AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `description` TEXT DEFAULT NULL,
    `header` LONGTEXT NOT NULL,
    `question_form_id` INT(11) NOT NULL,
    `deleted_at` DATETIME DEFAULT NULL,
    `include_attachments` TINYINT(1) NOT NULL DEFAULT 0,
    `show_section_and_rated_item_weights_on_appraisal` TINYINT(1) NOT NULL DEFAULT 1,
    `include_signature_block` TINYINT(1) NOT NULL DEFAULT 1,
    `evaluator_form_validation_rule` VARCHAR(2048) DEFAULT '',
    `final_rating_type` INT NOT NULL DEFAULT 1,
    `manual_rating_options` TEXT DEFAULT NULL,
    PRIMARY KEY(id),
    CONSTRAINT `fk_ohrm_appraisal_template_question_form`  FOREIGN KEY (`question_form_id`) REFERENCES `ohrm_question_form`(`id`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE = INNODB DEFAULT CHARACTER SET = utf8;

CREATE TABLE IF NOT EXISTS `ohrm_performance_appraisal_cycle_status` (
  `id` int NOT NULL AUTO_INCREMENT,
  `state_id` int NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `description` VARCHAR(250) NOT NULL,

  PRIMARY KEY(id),
  UNIQUE (`state_id`)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `ohrm_appraisal_cycle` (
        `id` INT AUTO_INCREMENT,
        `name` VARCHAR(255) NOT NULL,
        `from_date` DATE NOT NULL,
        `to_date` DATE NOT NULL,
        `due_date` DATE NOT NULL,
        `mainEvaluator_id` int,
        `reportingMethod_id` int,
        `template_id` INT,
        `country_code` char(2) ,
        `location_id` int ,
        `state_id` int,
        `created_by` int,
        `created_by_admin` TINYINT(1) NOT NULL DEFAULT 1,
        PRIMARY KEY(id),
        FOREIGN KEY (`mainEvaluator_id`) REFERENCES `hs_hr_employee`(`emp_number`) ON DELETE SET NULL ON UPDATE CASCADE,
        CONSTRAINT fk_appraisal_cycle_emp_reporting_method FOREIGN KEY (`reportingMethod_id`) REFERENCES `ohrm_emp_reporting_method`(`reporting_method_id`) ON DELETE SET NULL ON UPDATE CASCADE,
        FOREIGN KEY (`template_id`) REFERENCES `ohrm_appraisal_template` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
        FOREIGN KEY (`state_id`) REFERENCES `ohrm_performance_appraisal_cycle_status`(`state_id`) ON UPDATE CASCADE,
        CONSTRAINT fk_appraisal_cycle_user FOREIGN KEY (`created_by`) REFERENCES `ohrm_user`(`id`) ON DELETE SET NULL ON UPDATE CASCADE
)  ENGINE = INNODB DEFAULT CHARSET=utf8;


CREATE  TABLE IF NOT EXISTS `ohrm_performance_appraisal` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `emp_number` INT NULL,
  `description` VARCHAR(350) NOT NULL ,
  `from_date` DATE NOT NULL,
  `to_date` DATE NOT NULL,
  `due_date` DATE NOT NULL,
  `question_form_id` int,
  `state_id` int,
  `template_id` INT,
  `appraisal_cycle_id` int,
  `final_rating_type` INT NOT NULL DEFAULT 1,
  `final_rating` FLOAT DEFAULT NULL,
  `final_rating_percentage` FLOAT DEFAULT NULL,
  `final_rating_source` VARCHAR(255) DEFAULT 'mainEvaluators' NOT NULL,
  `rating_scale_properties` VARCHAR(2048) DEFAULT NULL,
  `manual_rating` VARCHAR(2000) DEFAULT NULL,
  `manual_rating_options` TEXT DEFAULT NULL,
  `consider_empty_sections_for_calculation` BOOLEAN NOT NULL DEFAULT FALSE,
  `header` LONGTEXT DEFAULT NULL,
  `nine_box_matrix_applicable` BOOLEAN NOT NULL DEFAULT FALSE,
  `performance_rating_category` SMALLINT DEFAULT NULL,
  `potential_rating_category` SMALLINT DEFAULT NULL,
  `nine_box_category_id` SMALLINT DEFAULT NULL,
  `include_signature_block` TINYINT(1) NOT NULL DEFAULT 0,
  `evaluator_form_validation_rule` VARCHAR(2048) DEFAULT '',
  `show_section_and_rated_item_weights_on_appraisal` TINYINT(1) NOT NULL DEFAULT 1,
  `show_potential_assessment_section_for_evaluated_employee` TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`) ,
  FOREIGN KEY (`question_form_id`) REFERENCES `ohrm_question_form`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`emp_number`) REFERENCES `hs_hr_employee`(`emp_number`) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`state_id`) REFERENCES `ohrm_performance_appraisal_status`(`state_id`) ON UPDATE CASCADE,
  FOREIGN KEY (`template_id`) REFERENCES `ohrm_appraisal_template`(`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  FOREIGN KEY(`appraisal_cycle_id`) REFERENCES `ohrm_appraisal_cycle`(`id`) ON DELETE SET NULL ON UPDATE CASCADE
)
ENGINE = InnoDB DEFAULT CHARACTER SET = utf8;
-- Foreign key constraints for 'performance_rating_category', 'potential_rating_category' and 'nine_box_category_id' are added in the SuccessionAndDevelopment plugin
-- (Because the respective 3 tables are not available until SuccessionAndDevelopment plugin is stalled.)
-- Check symfony/plugins/orangehrmSuccessionAndDevelopmentPlugin/install/dbscript_1.sql

CREATE TABLE IF NOT EXISTS `ohrm_performance_competency` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `description` VARCHAR(2048) NULL,
  `created_date` DATE NOT NULL,
  `appraisal_id` INT DEFAULT NULL,
  `parent_id` INT DEFAULT NULL,
  `is_group` TINYINT(1) NOT NULL DEFAULT 0,
  `deleted_at` DATETIME DEFAULT NULL,

  PRIMARY KEY (`id`),
  FOREIGN KEY (`appraisal_id`) REFERENCES ohrm_performance_appraisal(id) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`parent_id`) REFERENCES ohrm_performance_competency(id) ON DELETE CASCADE ON UPDATE CASCADE
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `ohrm_performance_title_competency`(
  `job_title_id`  int NOT NULL,
  `employee_competency_id` int NOT NULL,

  PRIMARY KEY (`job_title_id`,`employee_competency_id`),
  FOREIGN KEY (`job_title_id`) REFERENCES ohrm_job_title(id) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`employee_competency_id`) REFERENCES ohrm_performance_competency(id) ON DELETE CASCADE ON UPDATE CASCADE
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `ohrm_goal_level` (
    `id` INT AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `icon` VARCHAR(255) NOT NULL,
    `view_order` INT NOT NULL,
    PRIMARY KEY(id)
) ENGINE = INNODB DEFAULT CHARACTER SET = utf8;

CREATE TABLE IF NOT EXISTS `ohrm_goal_priority` (
    `id` INT AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `view_order` INT NOT NULL,
    `color` VARCHAR(7) NOT NULL,
    PRIMARY KEY(id)
) ENGINE = INNODB DEFAULT CHARACTER SET = utf8;

CREATE TABLE IF NOT EXISTS `ohrm_goal_category` (
    `id` INT AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `display_name` VARCHAR(255) NOT NULL,
    PRIMARY KEY(id)
) ENGINE = INNODB DEFAULT CHARACTER SET = utf8;

INSERT INTO `ohrm_goal_category`
VALUES
    (1, 'goal', 'Goal'),
    (2, 'objective', 'Objective'),
    (3, 'key_result', 'Key Result');

CREATE TABLE IF NOT EXISTS `ohrm_goal_template` (
    `id` INT AUTO_INCREMENT,
    `name` VARCHAR(600) NOT NULL,
    `goal_level_id` INT NULL,
    PRIMARY KEY(`id`),
    CONSTRAINT `ohrm_goal_template_goal_level` FOREIGN KEY (`goal_level_id`) REFERENCES `ohrm_goal_level` (`id`) ON UPDATE CASCADE
) ENGINE = INNODB DEFAULT CHARACTER SET = utf8;

CREATE TABLE `ohrm_goal_status` (
    `id` SMALLINT AUTO_INCREMENT,
    `state_id` SMALLINT NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `display_order` SMALLINT NOT NULL,
    `send_reminders` TINYINT(1) NOT NULL DEFAULT 0,
    PRIMARY KEY(id),
    UNIQUE(`state_id`)
)  ENGINE = INNODB DEFAULT CHARACTER SET = utf8;

CREATE TABLE IF NOT EXISTS `ohrm_performance_metric_number_format` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `strategy` VARCHAR(255) NOT NULL,
    `format` VARCHAR(255) NOT NULL,
    PRIMARY KEY(`id`)
) ENGINE = INNODB DEFAULT CHARACTER SET = utf8;

INSERT INTO `ohrm_performance_metric_number_format` VALUES
(1, 'decimalNumber', 'Number x,xxx.xx'),
(2, 'integerNumber', 'Number x,xxx'),
(3, 'percentageNumber', 'Percent xx.xx%');

CREATE TABLE IF NOT EXISTS `ohrm_performance_key_result_metric` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `unit_of_measurement` VARCHAR(255) NOT NULL,
    `start_value` DECIMAL(65,15) NULL,
    `target_value` DECIMAL(65,15) NULL,
    `current_value` DECIMAL(65,15) NULL,
    `number_format_id` INT NULL,
    `version` BIGINT(20) NOT NULL,
    PRIMARY KEY(`id`),
    CONSTRAINT `fk_ohrm_performance_metric_number_format_metric` FOREIGN KEY (`number_format_id`) REFERENCES `ohrm_performance_metric_number_format`(`id`) ON UPDATE CASCADE
) ENGINE = INNODB DEFAULT CHARACTER SET = utf8;

CREATE TABLE IF NOT EXISTS `ohrm_goal_metric_history` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `unit_of_measurement` VARCHAR(255) NOT NULL,
    `start_value` DECIMAL(65,15) NULL,
    `target_value` DECIMAL(65,15) NULL,
    `current_value` DECIMAL(65,15) NULL,
    `number_format_id` INT NULL,
    `version` BIGINT(20) NOT NULL,
    PRIMARY KEY(`id`, `version`),
    CONSTRAINT `fk_ohrm_goal_metric_history_metric_id` FOREIGN KEY (`id`) REFERENCES `ohrm_performance_key_result_metric` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = INNODB DEFAULT CHARACTER SET = utf8;

CREATE TABLE `ohrm_goal_workflow_status` (
    `id` INT AUTO_INCREMENT,
    `state_id` INT NOT NULL UNIQUE,
    `name` VARCHAR(255) NOT NULL,
    `display_name` VARCHAR(255) NOT NULL,
    `consider_as_approved` TINYINT(1) NOT NULL DEFAULT 0,
    `send_reminders` TINYINT(1) NOT NULL DEFAULT 0,
PRIMARY KEY(id)) ENGINE = INNODB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `ohrm_goal_purpose` (
    `id` INT NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    PRIMARY KEY(id)
) ENGINE = INNODB DEFAULT CHARACTER SET = utf8;

INSERT INTO `ohrm_goal_purpose` VALUES (1, 'Performance'),(2, 'Development');

CREATE TABLE IF NOT EXISTS `ohrm_performance_goal` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `emp_number` INT NOT NULL,
    `name` VARCHAR(500) NOT NULL,
    `description` MEDIUMTEXT NULL,
    `due_date` DATE NULL,
    `created_date`  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `last_updated_at` DATETIME DEFAULT NULL,
    `weight` INT DEFAULT NULL,
    `goal_level_id` INT NULL DEFAULT NULL,
    `goal_priority_id` INT NULL DEFAULT NULL,
    `goal_category_id` INT NOT NULL DEFAULT 1,
    `goal_purpose_id` INT NOT NULL,
    `goal_template_id` INT,
    `metric_id` INT NULL,
    `lft` smallint(6) unsigned default null,
    `rgt` smallint(6) unsigned default null,
    `level` smallint(6) unsigned default null,
    `root_id` BIGINT,
    `status_id` smallint NOT NULL,
    `read_only` TINYINT(1) DEFAULT 0,
    `percentage_completion` INT NULL,
    `workflow_status_id` INT NOT NULL DEFAULT 1,
    `version` BIGINT(20) NOT NULL,
    PRIMARY KEY(`id`),
    CONSTRAINT `fk_ohrm_performance_goal_employee` FOREIGN KEY (`emp_number`) REFERENCES `hs_hr_employee` (`emp_number`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_ohrm_performance_goal_goal_level` FOREIGN KEY (`goal_level_id`) REFERENCES `ohrm_goal_level`(`id`) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT `fk_ohrm_performance_goal_goal_category` FOREIGN KEY (`goal_category_id`) REFERENCES `ohrm_goal_category`(`id`) ON UPDATE CASCADE,
    CONSTRAINT `fk_ohrm_performance_goal_goal_purpose` FOREIGN KEY (`goal_purpose_id`) REFERENCES `ohrm_goal_purpose`(`id`) ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT `fk_ohrm_performance_goal_key_result_metric` FOREIGN KEY (`metric_id`) REFERENCES `ohrm_performance_key_result_metric`(`id`) ON UPDATE CASCADE,
    CONSTRAINT `fk_ohrm_performance_goal_template` FOREIGN KEY (`goal_template_id`) REFERENCES `ohrm_goal_template`(`id`) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT `fk_ohrm_performance_goal_status` FOREIGN KEY (`status_id`) REFERENCES `ohrm_goal_status` (`state_id`) ON UPDATE CASCADE,
    CONSTRAINT `fk_ohrm_performance_goal_workflow_status` FOREIGN KEY (`workflow_status_id`) REFERENCES `ohrm_goal_workflow_status`(`state_id`) ON DELETE CASCADE,
    CONSTRAINT `fk_ohrm_performance_goal_goal_priority` FOREIGN KEY (`goal_priority_id`) REFERENCES `ohrm_goal_priority`(`id`) ON UPDATE CASCADE ON DELETE SET NULL,
    INDEX `name` (`name` ASC),
    INDEX performance_goal_root_id (`root_id`),
    INDEX performance_goal_lft (`lft`),
    INDEX performance_goal_rgt (`rgt`),
    INDEX performance_goal_level (`level`)
) ENGINE = INNODB DEFAULT CHARACTER SET = utf8;

CREATE TABLE IF NOT EXISTS `ohrm_employee_goal_history` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `emp_number` INT NOT NULL,
    `name` VARCHAR(500) NOT NULL,
    `description` MEDIUMTEXT NULL,
    `due_date` DATE NULL,
    `created_date`  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `last_updated_at` DATETIME DEFAULT NULL,
    `weight` INT DEFAULT NULL,
    `goal_level_id` INT NULL DEFAULT NULL,
    `goal_priority_id` INT NULL DEFAULT NULL,
    `goal_category_id` INT NOT NULL DEFAULT 1,
    `goal_purpose_id` INT NOT NULL,
    `goal_template_id` INT,
    `metric_id` INT NULL,
    `lft` smallint(6) unsigned default null,
    `rgt` smallint(6) unsigned default null,
    `level` smallint(6) unsigned default null,
    `root_id` BIGINT,
    `status_id` smallint NOT NULL,
    `read_only` TINYINT(1) DEFAULT 0,
    `percentage_completion` INT NULL,
    `workflow_status_id` INT NOT NULL DEFAULT 1,
    `version` BIGINT(20) NOT NULL,
    PRIMARY KEY(`id`, `version`),
    CONSTRAINT `fk_ohrm_employee_goal_history_goal_id` FOREIGN KEY (`id`) REFERENCES `ohrm_performance_goal` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = INNODB DEFAULT CHARACTER SET = utf8;


CREATE TABLE IF NOT EXISTS `ohrm_performance_review_status` (
  `id` int NOT NULL AUTO_INCREMENT,
  `state_id` int NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `description` VARCHAR(250) NOT NULL,
  `icon` VARCHAR(100),
  `tooltip` VARCHAR(200),
  `consider_as_completed` TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY(id),
  UNIQUE (`state_id`)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `ohrm_performance_evaluator_group`(
  `id`  int NOT NULL AUTO_INCREMENT,
  `group_name` VARCHAR(255) NOT NULL,
  `group_alias` VARCHAR(255) NOT NULL,
  `activated` TINYINT(1) NOT NULL DEFAULT 0,
  `workflow_id` VARCHAR(255) NOT NULL,
  `filterclass` VARCHAR(255) DEFAULT 'DefaultEvaluatorGroup' NOT NULL,
  `precedence` INT NOT NULL,
  `auto_select_allowed` TINYINT(1) NOT NULL DEFAULT 0,
  `auto_select_enabled` TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`workflow_id`) REFERENCES  `ohrm_workflow`(`id`) ON UPDATE CASCADE
)
ENGINE=InnoDB DEFAULT CHARACTER SET = utf8;

CREATE TABLE IF NOT EXISTS `ohrm_performance_appraisal_evaluator`(
  `id` INT NOT NULL AUTO_INCREMENT,
  `employee_appraisal_id`  int NOT NULL,
  `employee_id` int NOT NULL,
  `comment_added_employee_id` int DEFAULT NULL,
  `comment_added_time` DATETIME DEFAULT NULL,
  `status` int NOT NULL DEFAULT 0,
  `admin_comment` MEDIUMTEXT,
  `group_id` int ,
  `deleted_at` DATETIME DEFAULT NULL,
  `review_locked_time` DATETIME DEFAULT NULL,
  `lock_id` VARCHAR(100) DEFAULT NULL,
  `locked_by_user_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`status`) REFERENCES ohrm_performance_review_status(state_id) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`employee_appraisal_id`) REFERENCES ohrm_performance_appraisal(id) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`employee_id`) REFERENCES `hs_hr_employee`(`emp_number`)  ON DELETE CASCADE ON UPDATE CASCADE ,
  FOREIGN KEY (`comment_added_employee_id`) REFERENCES `hs_hr_employee`(`emp_number`)  ON UPDATE CASCADE ,
  FOREIGN KEY (`group_id`) REFERENCES `ohrm_performance_evaluator_group`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_performance_appraisal_evaluator_locked_by_user`
      FOREIGN KEY (`locked_by_user_id`) REFERENCES `ohrm_user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
)
ENGINE=InnoDB DEFAULT CHARACTER SET = utf8;

-- removed tables given below
-- ohrm_performance_appraisal_form
-- ohrm_performance_appraisal_form_section
-- `ohrm_performance_appraisal_form_section_competency`
-- `ohrm_performance_competency_review`
-- `ohrm_performance_appraisal_form_section_goal`
--  `ohrm_performance_goal_review`

CREATE TABLE IF NOT EXISTS `ohrm_performance_appraisal_global_form_configuration`(
    `id`  int NOT NULL AUTO_INCREMENT,
    `section_title` VARCHAR(255) NOT NULL,
    `competency_matching_criteria` VARCHAR(1024) NULL,
    `goal_matching_criteria` VARCHAR(1024) NULL,
    `question_matching_criteria` VARCHAR(1024) NULL,
    `weight` DECIMAL(11, 1),
    `disabled` TINYINT(1) NOT NULL DEFAULT 0,
    `readonly` TINYINT(1) NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`)
)
ENGINE=InnoDB DEFAULT CHARACTER SET = utf8;


CREATE TABLE IF NOT EXISTS `ohrm_audittrail_performance_appraisal_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_performance_appraisal_trail_idx_app_id (app_id),
   INDEX audit_performance_appraisal_trail_idx_screen_name (screen_name)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `ohrm_performance_appraisal_history` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `action_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `action_owner_id` varchar(6),
  `action_owner_name` VARCHAR(300),
  `action` varchar(255) NOT NULL,
  `evaluator_id` VARCHAR(20),
  `action_description` MEDIUMTEXT,

   PRIMARY KEY (`id`)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `ohrm_performance_goal_evaluator_group`(
    `id` INT (11) NOT NULL AUTO_INCREMENT,
    `goal_id` INT (11) NOT NULL,
    `evaluator_group_id` INT (11) NOT NULL,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_ohrm_performance_goal_evaluator_group_1` FOREIGN KEY (`goal_id`) REFERENCES `ohrm_performance_goal` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_ohrm_performance_goal_evaluator_group_2` FOREIGN KEY (`evaluator_group_id`) REFERENCES `ohrm_performance_evaluator_group` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS `ohrm_performance_job_title_questions`(
  `job_title_id`  int NOT NULL,
  `question_id` int NOT NULL,

  PRIMARY KEY (`job_title_id`,`question_id`),
  FOREIGN KEY (`job_title_id`) REFERENCES ohrm_job_title(id) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`question_id`) REFERENCES ohrm_question(id) ON DELETE CASCADE ON UPDATE CASCADE
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `ohrm_performance_eval_group_questions`(
  `id` INT NOT NULL AUTO_INCREMENT,
  `appraisal_evaluator_group_id`  int NOT NULL,
  `question_id` int NOT NULL,
  `appraisal_id` int DEFAULT NULL,

  PRIMARY KEY (`id`),
  FOREIGN KEY (`appraisal_evaluator_group_id`) REFERENCES ohrm_performance_evaluator_group(id) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`question_id`) REFERENCES ohrm_question(id) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`appraisal_id`) REFERENCES ohrm_performance_appraisal(id) ON DELETE CASCADE ON UPDATE CASCADE
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE ohrm_performance_eval_group_questions
    ADD INDEX indx_performance_eval_group_questions_appraisal_id_question_id (appraisal_id, question_id);
-- -----------------------------------------
-- Creating tables related to questionnaire plugin
-- -----------------------------------------
CREATE TABLE IF NOT EXISTS `ohrm_performance_competency_question`(
  `id`  int NOT NULL AUTO_INCREMENT,
  `employee_competency_id`  int NOT NULL,
  `question_id` int NOT NULL,

  PRIMARY KEY (`id`),
  FOREIGN KEY (`question_id`) REFERENCES ohrm_question(id) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`employee_competency_id`) REFERENCES ohrm_performance_competency(id) ON DELETE CASCADE ON UPDATE CASCADE
)
ENGINE=InnoDB DEFAULT CHARACTER SET = utf8;

CREATE TABLE IF NOT EXISTS `ohrm_performance_goal_question`(
  `id`  int NOT NULL AUTO_INCREMENT,
  `employee_goal_id`  int NOT NULL,
  `question_id` int NOT NULL,

  PRIMARY KEY (`id`),
  FOREIGN KEY (`question_id`) REFERENCES ohrm_question(id) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`employee_goal_id`) REFERENCES ohrm_performance_goal(id) ON DELETE CASCADE ON UPDATE CASCADE
)
ENGINE=InnoDB DEFAULT CHARACTER SET = utf8;

CREATE TABLE IF NOT EXISTS `ohrm_performance_evaluator_answer` (
  `id`  int NOT NULL AUTO_INCREMENT,
  `employee_appraisal_evaluator_id` int NOT NULL,
  `question_form_question_id` int NOT NULL,
  `answer_id` int NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_performance_evaluator_answer` (`employee_appraisal_evaluator_id`, `question_form_question_id`),
  FOREIGN KEY (`employee_appraisal_evaluator_id`) REFERENCES `ohrm_performance_appraisal_evaluator`(`id`) ON DELETE CASCADE ON UPDATE CASCADE ,
  FOREIGN KEY (`answer_id`) REFERENCES `ohrm_answer`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ohrm_evaluator_answer_question_form_question` FOREIGN KEY (`question_form_question_id`) REFERENCES `ohrm_question_form_question`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
)
ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE `ohrm_performance_competency_profile` (
    `id` INT AUTO_INCREMENT,
    `name` VARCHAR(255) DEFAULT NULL,
    `auto_apply_to_new_subunits` TINYINT(1) NOT NULL DEFAULT 0,
    PRIMARY KEY(`id`)
)ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE ohrm_performance_competency_profile_mapping (
     competency_profile_id INT,
     job_title_id INT,
     subunit_id INT,
    PRIMARY KEY(`competency_profile_id`, `job_title_id`, `subunit_id`),
    UNIQUE KEY `job_title_subunit_unique` (`job_title_id`,`subunit_id`),
    CONSTRAINT `fk_competency_profile_competency_profile_mapping` FOREIGN KEY (`competency_profile_id`) REFERENCES
    `ohrm_performance_competency_profile`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_job_title_competency_profile_mapping` FOREIGN KEY (`job_title_id`) REFERENCES
    `ohrm_job_title`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_subunit_competency_profile_mapping` FOREIGN KEY (`subunit_id`) REFERENCES
    `ohrm_subunit`(`id`) ON DELETE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE Table `ohrm_performance_competency_profile_competency` (
    `id` int AUTO_INCREMENT,
    `competency_profile_id` int NOT NULL,
    `competency_id` int NOT NULL,
    `weight` int DEFAULT 1,
    `proficiency` FLOAT DEFAULT 0,
    PRIMARY KEY(`id`),
    UNIQUE KEY `competency_profile_id_competency_id_unique` (`competency_profile_id`,`competency_id`),
    CONSTRAINT `fk_competency_profile_competency_profile_competency` FOREIGN KEY (`competency_profile_id`) REFERENCES `ohrm_performance_competency_profile`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_competency_competency_profile_competency` FOREIGN KEY (`competency_id`) REFERENCES `ohrm_performance_competency`(`id`) ON DELETE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE Table `ohrm_performance_competency_profile_competency_evaluator_group` (
    `id` int AUTO_INCREMENT,
    `competency_profile_competency_id` int NOT NULL,
    `evaluator_group_id` int NOT NULL,
    PRIMARY KEY(`id`),
    CONSTRAINT `fk_competency_evaluator_group_competency_profile_competency` FOREIGN KEY (`competency_profile_competency_id`) REFERENCES `ohrm_performance_competency_profile_competency`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_evaluator_group_competency_profile_competency` FOREIGN KEY (`evaluator_group_id`) REFERENCES `ohrm_performance_evaluator_group`(`id`) ON DELETE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE Table `ohrm_performance_form_question_weight` (
    `id` int AUTO_INCREMENT,
    `form_question_id` int NOT NULL,
    `weight` double,

    PRIMARY KEY(`id`),
    FOREIGN KEY (`form_question_id`) REFERENCES `ohrm_question_form_question`(`id`) ON DELETE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `ohrm_performance_competency_question_group` (
    `id` INT AUTO_INCREMENT,
    `employee_competency_id` INT NOT NULL,
    `question_group_id` INT NOT NULL,
    PRIMARY KEY(`id`),
    FOREIGN KEY (`employee_competency_id`) REFERENCES `ohrm_performance_competency`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (`question_group_id`) REFERENCES `ohrm_question_group` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = INNODB DEFAULT CHARSET=utf8;


CREATE TABLE `ohrm_goal_question_group` (
    `id` INT AUTO_INCREMENT,
    `goal_group_id` INT NOT NULL,
    `question_group_id` INT NOT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY (`goal_group_id`) REFERENCES `ohrm_performance_goal` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (`question_group_id`) REFERENCES `ohrm_question_group` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE = INNODB DEFAULT CHARSET=utf8;

CREATE TABLE `ohrm_goal_comment` (
        `id` INT AUTO_INCREMENT,
        `goal_id` INT NOT NULL,
        `comment` TEXT NOT NULL,
        `commenter_name` VARCHAR(255) NOT NULL,
        `created_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `commenter_user_id` INT,
        `commenter_emp_number` INT DEFAULT NULL,
        `version` BIGINT(20) NOT NULL,
        PRIMARY KEY(id),
        FOREIGN KEY (`goal_id`) REFERENCES `ohrm_performance_goal` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
        FOREIGN KEY (`commenter_user_id`) REFERENCES `ohrm_user` (`id`) ON DELETE SET NULL,
        CONSTRAINT `fk_ohrm_goal_comment_hs_hr_employee` FOREIGN KEY (`commenter_emp_number`) REFERENCES `hs_hr_employee` (`emp_number`) ON DELETE SET NULL
)  ENGINE = INNODB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `ohrm_goal_comment_history` (
    `id` INT AUTO_INCREMENT,
    `goal_id` INT NOT NULL,
    `comment` TEXT NOT NULL,
    `commenter_name` VARCHAR(255) NOT NULL,
    `created_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `commenter_user_id` INT,
    `commenter_emp_number` INT DEFAULT NULL,
    `version` BIGINT(20) NOT NULL,
    PRIMARY KEY(`id`, `version`),
    CONSTRAINT `fk_ohrm_goal_comment_history_goal` FOREIGN KEY (`goal_id`) REFERENCES `ohrm_performance_goal`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_ohrm_goal_comment_history_user` FOREIGN KEY (`commenter_user_id`) REFERENCES `ohrm_user` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_ohrm_goal_comment_history_employee` FOREIGN KEY (`commenter_emp_number`) REFERENCES `hs_hr_employee` (`emp_number`) ON DELETE SET NULL
    ) ENGINE = INNODB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS `ohrm_goal_comment_history_for_goal_history` (
    `comment_id` INT,
    `comment_version` BIGINT(20) NOT NULL,
    `goal_id` INT(11) NOT NULL,
    `goal_version` BIGINT(20) NOT NULL,
    PRIMARY KEY(`comment_id`, `comment_version`, `goal_id`, `goal_version`),
    CONSTRAINT `ohrm_goal_comment_history_for_goal_history_goal` FOREIGN KEY (`goal_id`, `goal_version`) REFERENCES
    `ohrm_employee_goal_history`(`id`,`version`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `ohrm_goal_comment_history_for_goal_history_comment` FOREIGN KEY (`comment_id`, `comment_version`) REFERENCES
    `ohrm_goal_comment_history`(`id`, `version`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE = INNODB DEFAULT CHARSET=UTF8;

CREATE TABLE `ohrm_performance_question_types` (
        `id` INT AUTO_INCREMENT,
        `name` VARCHAR(255) NOT NULL,
        `description` VARCHAR(255),
        PRIMARY KEY(id)
)  ENGINE = INNODB DEFAULT CHARSET=utf8;


CREATE TABLE `ohrm_performance_section_question_types` (
        `id` INT AUTO_INCREMENT,
        `section_id` INT NOT NULL,
        `question_type_id` INT NOT NULL,
        `is_dynamic` TINYINT(1) NOT NULL DEFAULT 1,
        PRIMARY KEY(id),
        FOREIGN KEY (`section_id`) REFERENCES `ohrm_question_form_section` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
        FOREIGN KEY (`question_type_id`) REFERENCES `ohrm_performance_question_types` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
)  ENGINE = INNODB DEFAULT CHARSET=utf8;

CREATE TABLE `ohrm_performance_appraisal_cycle_employees` (
        `id` INT AUTO_INCREMENT,
        `cycle_id` INT NOT NULL,
        `emp_number` INT NOT NULL,
        `mainEvaluator_id` int,
        PRIMARY KEY(id),
        FOREIGN KEY (`cycle_id`) REFERENCES `ohrm_appraisal_cycle` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
        FOREIGN KEY (`emp_number`) REFERENCES `hs_hr_employee`(`emp_number`) ON UPDATE CASCADE ON DELETE CASCADE,
        CONSTRAINT performance_appraisal_cycle_employees_evaluator FOREIGN KEY (`mainEvaluator_id`) REFERENCES `hs_hr_employee`(`emp_number`) ON DELETE SET NULL ON UPDATE CASCADE
)  ENGINE = INNODB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `ohrm_key_result_metric_history_for_goal_history` (
    `key_result_id` INT(11) NOT NULL,
    `key_result_version` BIGINT(20) NOT NULL,
    `goal_id` INT(11) NOT NULL,
    `goal_version` BIGINT(20) NOT NULL,
    PRIMARY KEY(`key_result_id`, `key_result_version`, `goal_id`, `goal_version`),
    CONSTRAINT `ohrm_key_result_metric_history_for_goal_history_goal` FOREIGN KEY (`goal_id`, `goal_version`) REFERENCES
    `ohrm_employee_goal_history`(`id`,`version`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `ohrm_key_result_metric_history_for_goal_history_key_result` FOREIGN KEY (`key_result_id`, `key_result_version`) REFERENCES
    `ohrm_goal_metric_history`(`id`, `version`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE = INNODB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS `ohrm_evaluator_group_history_for_goal_history` (
    `evaluator_group_id` INT(11) NOT NULL,
    `goal_id` INT(11) NOT NULL,
    `goal_version` BIGINT(20) NOT NULL,
    PRIMARY KEY(`evaluator_group_id`, `goal_id`, `goal_version`),
    CONSTRAINT `ohrm_evaluator_group_history_for_goal_history_goal` FOREIGN KEY (`goal_id`, `goal_version`) REFERENCES
    `ohrm_employee_goal_history`(`id`,`version`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE = INNODB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS `ohrm_key_result_history_for_objective_history` (
    `key_result_id` INT(11) NOT NULL,
    `key_result_version` BIGINT(20) NOT NULL,
    `objective_id` INT(11) NOT NULL,
    `objective_version` BIGINT(20) NOT NULL,
    PRIMARY KEY(`key_result_id`, `key_result_version`, `objective_id`, `objective_version`),
    CONSTRAINT `ohrm_key_result_history_for_objective_history_objective` FOREIGN KEY (`objective_id`, `objective_version`) REFERENCES
    `ohrm_employee_goal_history`(`id`,`version`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `ohrm_key_result_history_for_objective_history_key_result` FOREIGN KEY (`key_result_id`, `key_result_version`) REFERENCES
    `ohrm_employee_goal_history`(`id`, `version`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE = INNODB DEFAULT CHARSET=UTF8;

CREATE TABLE `ohrm_performance_goal_attachment`(
	`id` int(13) not null auto_increment,
	`goal_id` int(11) not null,
	`user_id` int(10) default null,
	`file_name` varchar(255) not null,
    `file_type` varchar(255) default null,
	`file_size` int(30) not null,
	`file_content` mediumblob,
	`emp_number` int(7) default null,
    `attached_time` timestamp not null default now(),
    `version` BIGINT(20) NOT NULL,
	PRIMARY KEY (`id`),
	CONSTRAINT `ohrm_performance_goal_goal_attachment` FOREIGN KEY (`goal_id`) REFERENCES
    `ohrm_performance_goal`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `ohrm_performance_goal_attach_user` FOREIGN KEY (`user_id`) REFERENCES
    `ohrm_user` (`id`) ON DELETE SET NULL,
    CONSTRAINT `ohrm_performance_goal_attach_employee` FOREIGN KEY (`emp_number`)
    REFERENCES `hs_hr_employee` (`emp_number`) ON DELETE SET NULL
)ENGINE = INNODB DEFAULT CHARSET=UTF8;

CREATE TABLE `ohrm_goal_attachment_history`(
	`id` int(13) not null auto_increment,
	`goal_id` int(11) not null,
	`user_id` int(10) default null,
	`file_name` varchar(255) not null,
    `file_type` varchar(255) default null,
	`file_size` int(30) not null,
	`file_content` mediumblob,
	`emp_number` int(7) default null,
    `attached_time` timestamp not null default now(),
    `version` BIGINT(20) NOT NULL,
	PRIMARY KEY(`id`, `version`),
	CONSTRAINT `ohrm_goal_attachment_history_goal` FOREIGN KEY (`goal_id`) REFERENCES
    `ohrm_performance_goal`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `ohrm_goal_attachment_history_user` FOREIGN KEY (`user_id`) REFERENCES
    `ohrm_user` (`id`) ON DELETE SET NULL,
    CONSTRAINT `ohrm_goal_attachment_history_employee` FOREIGN KEY (`emp_number`)
    REFERENCES `hs_hr_employee` (`emp_number`) ON DELETE SET NULL
)ENGINE = INNODB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS `ohrm_goal_attachment_history_for_goal_history` (
    `attachment_id` INT(13) NOT NULL,
    `attachment_version` BIGINT(20) NOT NULL,
    `goal_id` INT(11) NOT NULL,
    `goal_version` BIGINT(20) NOT NULL,
    PRIMARY KEY(`attachment_id`, `attachment_version`, `goal_id`, `goal_version`),
    CONSTRAINT `ohrm_goal_attachment_history_for_goal_history_goal` FOREIGN KEY (`goal_id`, `goal_version`) REFERENCES
    `ohrm_employee_goal_history`(`id`,`version`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `ohrm_goal_attachment_history_for_goal_history_attachment` FOREIGN KEY (`attachment_id`, `attachment_version`) REFERENCES
    `ohrm_goal_attachment_history`(`id`, `version`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = INNODB DEFAULT CHARSET=UTF8;

-- ----------------------------------------------------------------------------------------------------------
-- ----------------------------------------------------------------------------------------------------------
-- Adding questionnaire related default values
-- ----------------------------------------------------------------------------------------------------------
-- ----------------------------------------------------------------------------------------------------------

INSERT INTO `ohrm_questionnaire` (`questionnaire_name`)
VALUES ('appraisal');
SET @questinnaire_id  := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_question_tag`(`tag_name`,`questionnaire_id`) VALUES
('performance questions',@questinnaire_id),
('goals',@questinnaire_id),
('competencies',@questinnaire_id);

INSERT INTO `ohrm_performance_question_types`(`name`,`description`) VALUES
('Competencies','Competencies'),
('Goals','Goals'),
('Questions','Questions');

-- Insert Status to status table
-- --------------
INSERT INTO  ohrm_performance_appraisal_status (state_id, name, description) VALUES
(0,'NOT INITIATED', 'appraisal has been created, yet to Initiate' ),
(1,'INITIATED', 'appraisal has been initiated, all evaluators have yet to finish' ),
(2,'COMPLETED', 'All appraisals have been submitted and approved' ),
(3,'APPROVED', 'Admin has approved the appraisal' ),
(4,'REJECTED', 'Admin has rejected the appraisal' ),
(5,'CLOSED', 'Admin has Closed the appraisal' );


INSERT INTO  ohrm_performance_review_status (state_id, name, description, icon, tooltip, consider_as_completed) VALUES
(0,'NOT INITIATED', 'Main appraisal has not yet initiated', 'access_time', 'Review Pending', 0),
(1,'INITIATED', 'Main appraisal has been initiated', 'access_time', 'Review Pending', 0),
(2,'IN PROGRESS', 'Evaluator is evaluating the appraisal', 'timelapse', 'Review In Progress', 0),
(3,'SUBMITTED', 'Evaluator has submitted the appraisal', 'check_circle', 'Review Submitted', 1),
(4,'APPROVED', 'Admin has approved the appraisal', 'offline_pin', 'Review Complete', 1),
(6,'REJECTED', 'Admin has rejected the appraisal', 'highlight_off', 'Review Rejected', 0);

INSERT INTO  ohrm_performance_appraisal_cycle_status (state_id, name, description) VALUES
(0,'CREATED', 'appraisal cycle has been created, yet to Initiate' ),
(1,'APPRAISALS CREATED', 'appraisals has been created, all evaluators have yet to finish' ),
(2,'ACTIVATED', 'All appraisals have been activated' ),
(3,'CLOSED', 'appraisal cycle closed' ),
(4,'REOPENED', 'appraisal cycle reopened' );

INSERT INTO `ohrm_goal_status` (`name` ,`state_id`, `display_order`, `send_reminders`) VALUES
    ('Pending', '0', '1', 1),
    ('In Progress', '1', '2', 1),
    ('Achieved', '2', '3', 1),
    ('Not Achieved', '3', '4', 1),
    ('On Hold', '4', '5', 1);




-- ----------------------------
-- Insert goal level
-- ----------------------------
INSERT INTO `ohrm_goal_level` (`name`, `icon`, `view_order`) VALUES
    ('Organizational', 'ohrm_organization', 2),
    ('Individual', 'ohrm_individual', 1);

-- ----------------------------
-- Insert goal priority
-- ----------------------------
INSERT INTO `ohrm_goal_priority` (`name`, `view_order`, `color`) VALUES
    ('Low',1,'#3fd5b7'),
    ('Medium',2,'#f0b74a'),
    ('High',3,'#ff9685'),
    ('Critical',4,'#eb0910');

-- -------------
-- Insert Global Appraisal Form Configuration
-- -------------

INSERT INTO ohrm_performance_appraisal_global_form_configuration (`section_title`, `competency_matching_criteria`, `goal_matching_criteria`, `question_matching_criteria`, `weight`) VALUES
('Competencies', 'SELECT cpc.competency_id as id FROM `ohrm_performance_competency_profile_competency` as cpc INNER JOIN( SELECT MIN(cp.id) as competency_profile_id FROM `ohrm_performance_competency_profile` as cp LEFT JOIN `ohrm_performance_competency_profile_mapping` as cpm ON cp.id = cpm.competency_profile_id WHERE cpm.job_title_id = #jobTitleId# AND cpm.subunit_id = #departmentId#) AS cpf ON cpc.competency_profile_id = cpf.competency_profile_id INNER JOIN `ohrm_performance_competency`as c ON c.id = cpc.competency_id WHERE c.deleted_at IS NULL UNION SELECT id FROM `ohrm_performance_competency`as c1 WHERE c1.appraisal_id = #appraisalId# AND c1.deleted_at IS NULL', NULL, NULL, 50.0),
('Goals', NULL, 'SELECT goal.id FROM `ohrm_performance_goal` goal
LEFT JOIN `ohrm_performance_goal` parent ON goal.root_id = parent.id AND goal.goal_category_id <> (SELECT id FROM `ohrm_goal_category` WHERE name = ''goal'')
LEFT JOIN `ohrm_performance_goal` parent2 ON goal.id = parent2.id AND goal.goal_category_id = (SELECT id FROM `ohrm_goal_category` WHERE name = ''goal'')
 LEFT JOIN `ohrm_goal_purpose` purpose ON goal.goal_purpose_id = purpose.id WHERE purpose.name = ''Performance'' AND goal.emp_number = #empNumber# AND goal.workflow_status_id = #approvedState# AND (parent.workflow_status_id = #approvedState# OR parent2.workflow_status_id = #approvedState#) AND goal.due_date BETWEEN #appraisalStartDate# AND #appraisalEndDate#', NULL, 50.0),
('Questions', NULL, NULL, "SELECT q.id FROM `ohrm_question` as q, `ohrm_question_question_tag` as qt, `ohrm_question_tag` as t, `ohrm_performance_job_title_questions` as jq WHERE q.is_deleted = 0 AND jq.question_id = q.id AND t.id = qt.tag_id AND q.id = qt.question_id AND (t.tag_name = 'performance questions' OR t.tag_name = concat('specific questions for appraisal ',#appraisalId#)) AND jq.job_title_id = #jobTitleId#", null);

-- -----------------------------------------
-- Insert rating scale configuration
-- -----------------------------------------
INSERT INTO `ohrm_config`(`property`, `value`) VALUES ('performance.decimal_rating_scale','on');
INSERT INTO `ohrm_config`(`property`, `value`) VALUES ('performance.rating_scale_properties','{"values":{"0.5":"0.5","1":"1.0","1.5":"1.5", "2":"2.0","2.5":"2.5","3":"3.0","3.5":"3.5","4":"4.0","4.5":"4.5","5":"5.0"}}');


-- --------------
-- Insert new answer types to questioniare plugin
-- --------------
INSERT IGNORE into `ohrm_answer_type` (type_name, class, close_ended, filterable) values
('Rating', 'RatingType', 1, 1),
('Rating with comment', 'RatingWithCommentType', 1, 1);

-- -------------------
-- introducing module
-- -------------------

INSERT INTO `ohrm_module`(`name`, `status`, `data_group_prefix`)
SELECT * FROM (SELECT 'performance' as name, 1 as status, 'Performance' as data_group_prefix) AS tmp
WHERE NOT EXISTS (
    SELECT name FROM `ohrm_module` WHERE name = 'performance'
) LIMIT 1;

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

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

-- --------Not Working,Shuold be added-----------
-- IF (@module_id = null) THEN
-- INSERT INTO `ohrm_module`(`name`, `status`) VALUES ('performance', 1); -- 1 means module is enabled
-- SET @module_id :=(SELECT `id` FROM `ohrm_module` WHERE `name`='performance');
-- END IF;
-- -----------------------------------------------

-- -------------------------------------------------------------------
-- inserting screens, setting screen permissions and adding meny items
-- -------------------------------------------------------------------
-- -------------------------------------------------------
-- insert appraisal reviewer user role and MAIN EVALUATOR user rle
-- -------------------------------------------------------
INSERT INTO `ohrm_user_role`(`name`, `display_name`, `is_assignable`, `is_predefined`) VALUES ('AppraisalReviewer','AppraisalReviewer',0,1);
INSERT INTO `ohrm_user_role`(`name`, `display_name`, `is_assignable`, `is_predefined`) VALUES ('AppraisalNonSelfReviewer','AppraisalNonSelfReviewer',0,1);
INSERT INTO `ohrm_user_role`(`name`, `display_name`, `is_assignable`, `is_predefined`) VALUES ('MainEvaluator','Main Evaluator',0,1);
INSERT INTO `ohrm_user_role`(`name`, `display_name`, `is_assignable`, `is_predefined`) VALUES ('AppraisalSupervisorReviewer','Supervisor Evaluator', 0, 1);

INSERT INTO `ohrm_user_role` (`name`, `display_name`, `is_assignable`, `is_predefined`, `is_editable`, `deletable`) VALUES
('MAIN-EVALUATOR', 'Main-Evaluator', 0, 1, 0, 0),
('EVALUATOR', 'Evaluator', 0, 1, 0, 0);

SET @admin_role_id := (SELECT `id` FROM ohrm_user_role WHERE `name` = 'Admin');
SET @supervisor_role_id := (SELECT `id` FROM ohrm_user_role WHERE `name` = 'Supervisor');
SET @ess_role_id := (SELECT `id` FROM ohrm_user_role WHERE `name` = 'ESS');
SET @appraisal_reviewer_role_id := (SELECT `id` FROM ohrm_user_role WHERE `name` = 'AppraisalReviewer');
SET @culture_pillar_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'Culture' AND parent_id IS NULL AND level = 1);

SET @parent_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'Performance' AND parent_id = @culture_pillar_menu_id);
SET @main_evaluator_role_id := (SELECT `id` FROM ohrm_user_role WHERE `name` = 'MainEvaluator');
SET @appraisal_nonself_reviewer_role_id := (SELECT `id` FROM ohrm_user_role WHERE `name` = 'AppraisalNonSelfReviewer');
SET @hod_user_role_id :=(SELECT `id` FROM `ohrm_user_role` WHERE `name`='HeadOfDepartment');
SET @admin_user_role_type_id := (SELECT id FROM `ohrm_user_role_type` WHERE `name` = 'Admin');
SET @supervisor_user_role_type_id := (SELECT id FROM `ohrm_user_role_type` WHERE `name` = 'Supervisor');

-- "employee competency list" screen --ok
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`, `route_name`) VALUES
('Employee Competency Screen', @admin_module_id, 'getEmployeeCompetencyList', 'admin_getEmployeeCompetencyList');
SET @competency_list_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
(@admin_role_id, @competency_list_screen_id, 1, 1, 1, 1);

SET @people_management_pillar_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'People Management' AND parent_id IS NULL AND level = 1);
SET @employee_management_module_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'Employee Management' AND parent_id = @people_management_pillar_menu_id AND level = 2);

INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) VALUES
('Competencies', NULL, @employee_management_module_menu_id, 3, 800, NULL, 1);
SET @employee_competency_list_menu_id  := (SELECT LAST_INSERT_ID());

SET @competency_list_screen_id := (SELECT `id` FROM `ohrm_screen` WHERE `name`='Employee Competency Screen');
INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) VALUES
('Competency List', @competency_list_screen_id, @employee_competency_list_menu_id, 4, 200, NULL, 1);



-- "add employee competency" screen
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('Add Employee Competency Screen', @admin_module_id, 'addEmployeeCompetency');
SET @add_competency_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
(@admin_role_id, @add_competency_screen_id, 1, 1, 1, 1);

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`, `route_name`) VALUES
('Competency Profiles', @module_id, 'competencyProfiles', 'performance_competencyProfiles');
SET @competency_profile_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
(@admin_role_id, @competency_profile_screen_id, 1, 1, 1, 1);

INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `uisref`, `status`) VALUES
('Competency Profiles', @competency_profile_screen_id, @parent_menu_id, 3, 600, NULL, 'performance.competency_profiles', 1);

-- "goal list" screen
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`, `route_name`) VALUES
('Goal List', @module_id, 'viewEmployeeGoalList', 'performance_viewEmployeeGoalList');
SET @goal_list_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
(@admin_role_id, @goal_list_screen_id, 1, 1, 1, 1),
(@supervisor_role_id, @goal_list_screen_id, 1, 1, 1, 1),
(@hod_user_role_id, @goal_list_screen_id, 1, 1, 1, 1);

INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `image_name`, `status`) VALUES
('Goals', NULL, @culture_pillar_menu_id, 2, 25, NULL, 'goal.svg', 1);
SET @goals_menu_id  := (SELECT LAST_INSERT_ID());
INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `uisref`, `status`) VALUES
('Goal List', @goal_list_screen_id, @goals_menu_id , 3, 100, NULL, 'performance.goals.approved', 1);

-- "My goals screen"
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`, `route_name`) VALUES
('My Goals Screen', @module_id, 'myGoals', 'performance_myGoals');
SET @my_goal_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
(@ess_role_id, @my_goal_screen_id, 1, 1, 1, 1);

INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `uisref`, `status`) VALUES
('My Goals', @my_goal_screen_id, @goals_menu_id, 3, 200, NULL, 'performance.goals.my', 1);

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`, `route_name`) VALUES
('Goal Library', @module_id, 'goalLibrary', 'performance_goalLibrary');
SET @goal_library_list_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
(@admin_role_id, @goal_library_list_screen_id, 1, 0, 0, 1);

INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `uisref`, `status`) VALUES
('Goal Library', @goal_library_list_screen_id, @goals_menu_id , 3, 300, NULL, 'performance.goal_library', 1);

-- "add employee appraisal" screen
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('Add Appraisal', @module_id, 'addEmployeeAppraisal');
SET @add_appraisal_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
(@admin_role_id, @add_appraisal_screen_id, 1, 1, 1, 1),
(@main_evaluator_role_id, @add_appraisal_screen_id, 1, 1, 1, 1),
(@supervisor_role_id, @add_appraisal_screen_id, 1, 1, 1, 1);

-- --- -- "all appraisal list" screen
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('All Appraisal List', @module_id, 'viewAllAppraisals');
SET @appraisal_list_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
(@admin_role_id, @appraisal_list_screen_id, 1, 1, 1, 1),
(@main_evaluator_role_id, @appraisal_list_screen_id, 1, 1, 1, 1),
(@supervisor_role_id, @appraisal_list_screen_id, 1, 1, 1, 1),
(@hod_user_role_id, @appraisal_list_screen_id, 1, 0, 0, 0),
(@appraisal_nonself_reviewer_role_id, @appraisal_list_screen_id, 1, 1, 1, 1);
INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) VALUES
('Appraisal List', @appraisal_list_screen_id, @parent_menu_id, 3, 100, NULL, 1);

-- --- -- -- "my appraisal list" screen
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('My Appraisal List', @module_id, 'viewMyAppraisals');
SET @my_appraisal_list_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
(@ess_role_id, @my_appraisal_list_screen_id, 1, 0, 0, 0);

INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) VALUES
('My Appraisals', @my_appraisal_list_screen_id, @parent_menu_id, 3, 300, NULL, 1);

-- "Appraisal Cycles"
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`, `route_name`) VALUES
('Appraisal Cycles', @module_id, 'viewAppraisalCycles', 'performance_viewAppraisalCycles');
SET @appraisal_cycle_list_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
(@admin_role_id, @appraisal_cycle_list_screen_id, 1, 1, 1, 1);
INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete) VALUES
(@supervisor_role_id, @appraisal_cycle_list_screen_id, 1, 1, 1, 1);
INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete) VALUES
(@hod_user_role_id, @appraisal_cycle_list_screen_id, 1, 1, 1, 1);

INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) VALUES
('Appraisal Cycles', @appraisal_cycle_list_screen_id, @parent_menu_id, 3, 200, NULL, 1);

-- --"config appraisal form" screen
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('Config appraisal form', @module_id, 'configAppraisalForm');
SET @config_appraisal_form_real_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
(@admin_role_id, @config_appraisal_form_real_screen_id, 1, 1, 1, 1),
(@supervisor_role_id, @config_appraisal_form_real_screen_id, 1, 1, 1, 1),
(@main_evaluator_role_id, @config_appraisal_form_real_screen_id, 1, 1, 1, 1);

-- "config appraisal form section" screen
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('Config appraisal form section', @module_id, 'configAppraisalFormSection');
SET @config_appraisal_form_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
(@admin_role_id, @config_appraisal_form_screen_id, 1, 1, 1, 1),
(@supervisor_role_id, @config_appraisal_form_screen_id, 1, 1, 1, 1),
(@main_evaluator_role_id, @config_appraisal_form_screen_id, 1, 1, 1, 1);


-- "config appraisal Questions" screen
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('Config appraisal Questions', @module_id, 'viewAppraisalQuestionList');
SET @config_appraisal_questions_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
(@admin_role_id, @config_appraisal_questions_screen_id, 1, 1, 1, 1),
(@supervisor_role_id, @config_appraisal_questions_screen_id, 1, 1, 1, 1),
(@main_evaluator_role_id, @config_appraisal_questions_screen_id, 1, 1, 1, 1);
-- "Employee Appraisal Competencies" screen
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('Appraisal Competencies', @module_id, 'employeeAppraisalCompetencies');
SET @employee_Appraisal_Competencies_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
(@admin_role_id, @employee_Appraisal_Competencies_screen_id, 1, 1, 1, 1),
(@supervisor_role_id, @employee_Appraisal_Competencies_screen_id, 1, 1, 1, 1),
(@main_evaluator_role_id, @employee_Appraisal_Competencies_screen_id, 1, 1, 1, 1);
-- "delete appraisal" screen
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('Delete Appraisal', @module_id, 'deleteEmployeeAppraisal');
SET @delete_appraisal_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
(@admin_role_id, @delete_appraisal_screen_id, 1, 1, 1, 1);

-- "delete Employee Competency" screen
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('Delete Competency', @admin_module_id, 'deleteEmployeeCompetency');
SET @delete_competency_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
(@admin_role_id, @delete_competency_screen_id, 1, 1, 1, 1),
(@supervisor_role_id, @delete_competency_screen_id, 1, 1, 1, 1),
(@main_evaluator_role_id, @delete_competency_screen_id, 1, 1, 1, 1);

-- "get appraisal form AJAX" screen
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('Get Appraisal Form Ajax', @module_id, 'getAppraisalFormAjax');
SET @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
(@admin_role_id, @screen_id, 1, 1, 1, 1),
(@supervisor_role_id, @screen_id, 1, 1, 1, 1),
(@main_evaluator_role_id, @screen_id, 1, 1, 1, 1),
(@ess_role_id, @screen_id, 1, 1, 1, 1);

INSERT INTO `ohrm_default_permitted_screen` (`user_role_type_id`, `screen_id`) VALUES
    (@admin_role_id, @screen_id),
    (@supervisor_role_id, @screen_id),
    (@ess_role_id, @screen_id);


-- "multiple view" screen
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('Multiple view', @module_id, 'multipleAppraisalView');
SET @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
(@admin_role_id, @screen_id, 1, 1, 1, 1);

INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete) VALUES
(@admin_role_id, @screen_id, 1, 1, 1, 1);

-- "config" screen
INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) VALUES
('Configuration', NULL, @parent_menu_id, 3, 700, NULL, 1);

SET @employee_configuration_menu_id  := (SELECT LAST_INSERT_ID());#

-- "appraisal config" screen
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`, `route_name`) VALUES
('default apraisal configurations', @module_id, 'defaultAppraisalConfiguration', 'performance_defaultAppraisalConfiguration');
SET @default_appraisal_configurations_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
(@admin_role_id, @default_appraisal_configurations_screen_id, 1, 1, 1, 1);

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('apraisal configurations', @module_id, 'evaluatorGroupConfig');
SET @evaluator_group_configurations_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
(@admin_role_id, @evaluator_group_configurations_screen_id, 1, 1, 1, 1);

INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) VALUES
('Appraisal', @default_appraisal_configurations_screen_id, @employee_configuration_menu_id , 4, 1000, NULL, 1);
SET @appraisal_configuration_menu_id  := (SELECT LAST_INSERT_ID());


-- "question configurations config" screen
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('question configurations', @module_id, 'viewQuestionList');
SET @question_configurations_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
(@admin_role_id, @question_configurations_screen_id, 1, 1, 1, 1);

-- "Header template Config" screen
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('Appraisal Template Configurations', @module_id, 'viewAppraisalTemplateList');
SET @appraisal_template_configurations_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
(@admin_role_id, @appraisal_template_configurations_screen_id, 1, 1, 1, 1);


-- "update appraisal section" screen
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('Update appraisal section', @module_id, 'updateAppraisalSection');
SET @update_appraisal_section_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
(@admin_role_id, @update_appraisal_section_screen_id, 1, 1, 1, 1),
(@main_evaluator_role_id, @update_appraisal_section_screen_id, 1, 1, 1, 1),
(@supervisor_role_id, @update_appraisal_section_screen_id, 1, 1, 1, 1);

-- "change appraisal status" screen
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
    ('Update appraisal status', @module_id, 'changeAppraisalStatusAjax');
SET @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
(@admin_role_id, @screen_id, 1, 1, 1, 1),
(@main_evaluator_role_id, @screen_id, 1, 1, 1, 1),
(@supervisor_role_id, @screen_id, 1, 1, 1, 1);

INSERT INTO `ohrm_default_permitted_screen` (`user_role_type_id`, `screen_id`) VALUES
    (@admin_role_id, @screen_id),
    (@supervisor_role_id, @screen_id);
-- "add appraisal evaluator" screen
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('Add appraisal evaluator',@module_id , 'addAppraisalEvaluator');
SET @add_appraisal_evaluator_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
(@admin_role_id, @add_appraisal_evaluator_screen_id, 1, 1, 1, 1),
(@supervisor_role_id, @add_appraisal_evaluator_screen_id, 1, 1, 1, 1),
(@main_evaluator_role_id, @add_appraisal_evaluator_screen_id, 1, 1, 1, 1);

-- -----------------
-- data groups
-- ------------------

-- "goals" data group
INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('Goals', 'Goals - Goal List', 1, 1, 1, 1);
SET @goals_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, @goals_data_group_id, 1, 1, 1, 1, 0),
(2, @goals_data_group_id, 1, 1, 1, 1, 1),
(3, @goals_data_group_id, 1, 1, 1, 1, 0),
(@hod_user_role_id, @goals_data_group_id, 1, 1, 1, 1, 0);
INSERT INTO `ohrm_user_role_type_data_group` (`user_role_type_id`, `data_group_id`) VALUES
(1, @goals_data_group_id),
(2, @goals_data_group_id),
(3, @goals_data_group_id);

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('goal_library', 'Goals - Goal Library', 1, 0, 0, 1);
SET @goals_library_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_role_id, @goals_library_data_group_id, 1, 0, 0, 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, @goals_library_data_group_id, 1, 0, 0, 1);

-- "Appraisal Cycle" data group
INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('Appraisal Cycle', 'Performance - Appraisal Cycle', 1, 1, 1, 1);
SET @appraisal_cycle_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_role_id, @appraisal_cycle_data_group_id, 1, 1, 1, 1, 0);
INSERT INTO `ohrm_user_role_data_group` (`user_role_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`, `self`) VALUES
(@supervisor_role_id, @appraisal_cycle_data_group_id, 1, 1, 1, 1, 0);
INSERT INTO `ohrm_user_role_data_group` (`user_role_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`, `self`) VALUES
(@hod_user_role_id, @appraisal_cycle_data_group_id, 1, 1, 1, 1, 0);
INSERT INTO `ohrm_user_role_type_data_group` (`user_role_type_id`, `data_group_id`) VALUES
(@admin_user_role_type_id, @appraisal_cycle_data_group_id);
INSERT INTO `ohrm_user_role_type_data_group` (`user_role_type_id`, `data_group_id`) VALUES
(@supervisor_user_role_type_id, @appraisal_cycle_data_group_id);

-- "competencies" data group
INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('employee_competencies', 'Employee Management - Employee Competencies', 1, 1, 1, 1);
SET @employee_competencies_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, @employee_competencies_data_group_id, 1, 1, 1, 1, 0);
INSERT INTO `ohrm_user_role_type_data_group` (`user_role_type_id`, `data_group_id`) VALUES
(1, @employee_competencies_data_group_id);
SET @admin_module_screen_id = (SELECT `id` FROM `ohrm_screen` WHERE `action_url` = 'viewAdminModule');
INSERT INTO `ohrm_data_group_screen` (`data_group_id`, `screen_id`, `permission`, `self`) VALUES (@employee_competencies_data_group_id, @admin_module_screen_id, '1', '0');

-- "Competency Mapping" Data Group
INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('competency_profiles', 'Performance - Competency Profiles', 1, 1, 1, 1);
SET @competency_profiles_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_role_id, @competency_profiles_data_group_id, 1, 1, 1, 1, 0);
INSERT INTO `ohrm_user_role_type_data_group` (`user_role_type_id`, `data_group_id`) VALUES
(@admin_role_id, @competency_profiles_data_group_id);


-- "title competencies" data group
INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('employee_title_competencies', 'Performance - Employee Title Competencies', 1, 1, 1, 1);
SET @employee_title_competencies_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, @employee_title_competencies_data_group_id, 1, 1, 1, 1, 0),
(2, @employee_title_competencies_data_group_id, 1, 0, 0, 0, 0);

-- "employee appraisal" data group
INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('employee_appraisals', 'Performance - Employee Appraisals', 1, 1, 1, 1);
SET @employee_appraisal_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, @employee_appraisal_data_group_id, 1, 1, 1, 1, 0),
(@supervisor_role_id, @employee_appraisal_data_group_id, 1, 1, 1, 0, 0),
(@hod_user_role_id, @employee_appraisal_data_group_id, 1, 0, 0, 0, 0),
(@main_evaluator_role_id, @employee_appraisal_data_group_id, 1, 0, 1, 0, 0),
(@appraisal_reviewer_role_id, @employee_appraisal_data_group_id, 1, 0, 0, 0, 0),
(@appraisal_nonself_reviewer_role_id, @employee_appraisal_data_group_id, 1, 0, 0, 0, 0);

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('my_appraisals', 'Performance - My Appraisals', 1, NULL, NULL, NULL);
SET @my_appraisal_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
(@ess_role_id, @my_appraisal_data_group_id, 1, 0, 0, 0, 1);

INSERT INTO `ohrm_user_role_type_data_group` (`user_role_type_id`, `data_group_id`) VALUES
(1, @employee_appraisal_data_group_id),
(@supervisor_role_id, @employee_appraisal_data_group_id),
(@ess_role_id, @my_appraisal_data_group_id);

-- -- "employee appraisal - Manage" data group
-- INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
-- ('employee_appraisal_manage', 'Performance - Employee Appraisals Manage', 1, 1, 1, 1);
-- SET @employee_appraisal_manage_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, @employee_appraisal_manage_data_group_id, 1, 1, 1, 1, 0),
-- (@supervisor_role_id, @employee_appraisal_manage_data_group_id, 1, 1, 1, 0, 0),
-- (@main_evaluator_role_id, @employee_appraisal_manage_data_group_id, 1, 1, 1, 0, 0);
-- INSERT INTO `ohrm_user_role_type_data_group` (`user_role_type_id`, `data_group_id`) VALUES
-- (1, @employee_appraisal_manage_data_group_id);

-- "my appraisal" data group



-- "appraisal configuration evaluators" data group
INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('appraisal_config_evaluators', 'Performance - Appraisal Configuration Evaluators', 1, NULL, 1, NULL);
SET @appraisal_config_evaluators_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, @appraisal_config_evaluators_data_group_id, 1, 0, 1, 0, 0);
INSERT INTO `ohrm_user_role_type_data_group` (`user_role_type_id`, `data_group_id`) VALUES
(1, @appraisal_config_evaluators_data_group_id);

-- "appraisal configuration evaluators" data group
INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('appraisal_config_appraisal_template', 'Performance - Appraisal Configuration Templates', 1, 1, 1, 1);
SET @appraisal_config_appraisal_template_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_role_id, @appraisal_config_appraisal_template_data_group_id, 1, 1, 1, 1, 0);
INSERT INTO `ohrm_user_role_type_data_group` (`user_role_type_id`, `data_group_id`) VALUES
(@admin_user_role_type_id, @appraisal_config_appraisal_template_data_group_id);

-- "generic questions" data group
INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('performance_questions', 'Performance - Appraisal Configuration Questions', 1, 1, 1, 1);
SET @questions_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, @questions_data_group_id, 1, 1, 1, 1, 0);
INSERT INTO `ohrm_user_role_type_data_group` (`user_role_type_id`, `data_group_id`) VALUES
(1, @questions_data_group_id);

-- "specific questions" data group
INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('appraisal_specific_questions', 'Performance - Appraisal specific Questions', 1, 1, 1, 1);
SET @appraisal_specific_questions_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, @appraisal_specific_questions_data_group_id, 1, 1, 1, 1, 0);



-- add values to ohrm_data_group_screen table

INSERT INTO `ohrm_data_group_screen`( `data_group_id`, `screen_id`, `permission`) VALUES (@employee_competencies_data_group_id, @competency_list_screen_id, 1);
INSERT INTO `ohrm_data_group_screen`( `data_group_id`, `screen_id`, `permission`) VALUES (@employee_competencies_data_group_id, @add_competency_screen_id, 2);
INSERT INTO `ohrm_data_group_screen`( `data_group_id`, `screen_id`, `permission`) VALUES (@employee_competencies_data_group_id, @add_competency_screen_id, 1);
INSERT INTO `ohrm_data_group_screen`( `data_group_id`, `screen_id`, `permission`) VALUES (@employee_competencies_data_group_id, @delete_competency_screen_id, 4);

INSERT INTO `ohrm_data_group_screen`( `data_group_id`, `screen_id`, `permission`) VALUES (@goals_data_group_id, @goal_list_screen_id, 1);
INSERT INTO `ohrm_data_group_screen`( `data_group_id`, `screen_id`, `permission`, `self`) VALUES (@goals_data_group_id, @my_goal_screen_id,1, 1);

INSERT INTO `ohrm_data_group_screen`( `data_group_id`, `screen_id`, `permission`) VALUES (@employee_appraisal_data_group_id,@appraisal_list_screen_id,1);
INSERT INTO `ohrm_data_group_screen`( `data_group_id`, `screen_id`, `permission`) VALUES (@employee_appraisal_data_group_id,@add_appraisal_screen_id,2);
INSERT INTO `ohrm_data_group_screen`( `data_group_id`, `screen_id`, `permission`) VALUES (@employee_appraisal_data_group_id,@add_appraisal_screen_id,3);
INSERT INTO `ohrm_data_group_screen`( `data_group_id`, `screen_id`, `permission`) VALUES (@employee_appraisal_data_group_id,@delete_appraisal_screen_id,4);
INSERT INTO `ohrm_data_group_screen`( `data_group_id`, `screen_id`, `permission`) VALUES (@employee_appraisal_data_group_id,@update_appraisal_section_screen_id,3);
INSERT INTO `ohrm_data_group_screen`( `data_group_id`, `screen_id`, `permission`) VALUES (@employee_appraisal_data_group_id,@delete_competency_screen_id,3);
INSERT INTO `ohrm_data_group_screen`( `data_group_id`, `screen_id`, `permission`) VALUES (@employee_appraisal_data_group_id,@config_appraisal_form_real_screen_id,3);
INSERT INTO `ohrm_data_group_screen`( `data_group_id`, `screen_id`, `permission`) VALUES (@appraisal_config_evaluators_data_group_id,@evaluator_group_configurations_screen_id,1);
INSERT INTO `ohrm_data_group_screen`( `data_group_id`, `screen_id`, `permission`) VALUES (@questions_data_group_id,@question_configurations_screen_id,1);
INSERT INTO `ohrm_data_group_screen`( `data_group_id`, `screen_id`, `permission`) VALUES (@appraisal_config_appraisal_template_data_group_id,@appraisal_template_configurations_screen_id,1);

INSERT INTO `ohrm_data_group_screen`( `data_group_id`, `screen_id`, `permission`) VALUES (@appraisal_config_appraisal_template_data_group_id, @config_appraisal_form_real_screen_id, 2);
INSERT INTO `ohrm_data_group_screen`( `data_group_id`, `screen_id`, `permission`) VALUES (@appraisal_config_appraisal_template_data_group_id, @config_appraisal_form_real_screen_id, 3);
INSERT INTO `ohrm_data_group_screen`( `data_group_id`, `screen_id`, `permission`) VALUES (@appraisal_config_appraisal_template_data_group_id, @config_appraisal_form_screen_id, 2);
INSERT INTO `ohrm_data_group_screen`( `data_group_id`, `screen_id`, `permission`) VALUES (@appraisal_config_appraisal_template_data_group_id, @config_appraisal_form_screen_id, 3);
INSERT INTO `ohrm_data_group_screen`( `data_group_id`, `screen_id`, `permission`) VALUES (@appraisal_config_appraisal_template_data_group_id, @update_appraisal_section_screen_id, 3);

INSERT INTO `ohrm_data_group_screen`( `data_group_id`, `screen_id`, `permission`) VALUES (@appraisal_config_evaluators_data_group_id,@default_appraisal_configurations_screen_id,1);
INSERT INTO `ohrm_data_group_screen`( `data_group_id`, `screen_id`, `permission`) VALUES (@questions_data_group_id,@default_appraisal_configurations_screen_id,1);
INSERT INTO `ohrm_data_group_screen`( `data_group_id`, `screen_id`, `permission`) VALUES (@appraisal_config_appraisal_template_data_group_id,@default_appraisal_configurations_screen_id,1);

INSERT INTO `ohrm_data_group_screen`( `data_group_id`, `screen_id`, `permission`) VALUES (@employee_appraisal_data_group_id,@config_appraisal_questions_screen_id,3);
INSERT INTO `ohrm_data_group_screen`( `data_group_id`, `screen_id`, `permission`) VALUES (@employee_appraisal_data_group_id,@add_appraisal_evaluator_screen_id,2);
INSERT INTO `ohrm_data_group_screen`( `data_group_id`, `screen_id`, `permission`) VALUES (@employee_appraisal_data_group_id,@add_appraisal_evaluator_screen_id,3);
INSERT INTO `ohrm_data_group_screen`( `data_group_id`, `screen_id`, `permission`) VALUES (@employee_appraisal_data_group_id,@employee_Appraisal_Competencies_screen_id,3);
INSERT INTO `ohrm_data_group_screen`( `data_group_id`, `screen_id`, `permission`) VALUES (@employee_appraisal_data_group_id,@config_appraisal_form_screen_id,3);

INSERT INTO `ohrm_data_group_screen`( `data_group_id`, `screen_id`, `permission`) VALUES (@appraisal_cycle_data_group_id,@appraisal_cycle_list_screen_id,1);
INSERT INTO `ohrm_data_group_screen`( `data_group_id`, `screen_id`, `permission`, `self`) VALUES (@my_appraisal_data_group_id,@my_appraisal_list_screen_id,1,1);

INSERT INTO `ohrm_data_group_screen`( `data_group_id`, `screen_id`, `permission`) VALUES (@goals_library_data_group_id, @goal_library_list_screen_id, 1);
INSERT INTO `ohrm_data_group_screen`( `data_group_id`, `screen_id`, `permission`) VALUES (@competency_profiles_data_group_id,@competency_profile_screen_id,1);

INSERT INTO `ohrm_performance_competency`(`name`,`description`,`is_group`,`created_date`, `deleted_at`) VALUES ('Miscellaneous','Miscellaneous Competency Group','1', CURRENT_DATE(), NULL);
SET @miscellaneous_competency_id  := (SELECT LAST_INSERT_ID());
INSERT INTO ohrm_config(`property`,`value`) VALUES ('performance.competency_groups_allowed','on');
INSERT INTO `ohrm_question_group` (`name` ,`alias` ,`questionnaire_id`) VALUES
    ('miscellaneous', 'Miscellaneous', @questinnaire_id);
SET @miscellaneous_question_group_id  := (SELECT LAST_INSERT_ID());
INSERT INTO `ohrm_performance_competency_question_group` (`id` ,`employee_competency_id` ,`question_group_id`) VALUES
    (NULL ,@miscellaneous_competency_id, @miscellaneous_question_group_id);
INSERT INTO ohrm_config(`property`,`value`) VALUES ('performance.section_rating_for_appraisal_on','Yes');
INSERT INTO ohrm_config(`property`,`value`) VALUES ('performance.goals_final_rating_total_weight ','100');
INSERT INTO ohrm_config(`property`,`value`) VALUES ('performance.competencies_final_rating_total_weight','100');
INSERT INTO ohrm_config(`property`,`value`) VALUES ('performance.default_final_rating_total_weight','100');
INSERT INTO ohrm_config(`property`,`value`) VALUES ('performance.final_rating_for_section_method','DefaultWeightedAverage');
INSERT INTO ohrm_config(`property`,`value`) VALUES ('performance.final_rating_for_appraisal_on','Yes');
INSERT INTO ohrm_config(`property`,`value`) VALUES ('performance.final_rating_for_appraisal_method','DefaultWeightedSectionAverage');
INSERT INTO ohrm_config(`property`,`value`) VALUES ('performance.final_rating_appraisal_total_weight','100');
INSERT INTO ohrm_config(`property`,`value`) VALUES ('performance.show_final_rating_on_status','COMPLETED,CLOSED');
INSERT INTO ohrm_config(`property`,`value`) VALUES ('performance.show_section_rating_on_status','SUBMITTED,APPROVED,SIGNED,EMPLOYEE ACKNOWLEDGED,IN PROGRESS');
INSERT INTO ohrm_config(`property`,`value`) VALUES ('esign.vender_rest_class','ESignLiveRestServices');

INSERT INTO `ohrm_notifications_type` (`name`, `module`, `text`, `subject`, `param`, `notification_class`, `recipient_types`) VALUES
('Employee Appraisal Notification', 'SYSTEM',
'The performance appraisal for the period of FROM_DATE to TO_DATE FOR_EMPLOYEE is due to be completed in DAYSLEFT.

REVIEW_FOR_EMPLOYEE


Thank you,
HR Team', 'Employee Appraisal Due Date Notification', '', 'AppraisalEventNotification', 'staffOwningEvent,reviewer');
SET @nt_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_notifications_definition` (`employee_id`, `nt_id`, `name`, `text`, `subject`, `recipients`, `schedule`, `param_values`, `status`,`email_id`) VALUES
(null, @nt_id, 'Appraisal Due Date Notifications (send to reviewers)', null, null, '<?xml version="1.0"?>
<xml><recipient/></xml>', '<?xml version="1.0"?><xml><schedule><DaysInAdvanced><type>1 Week</type></DaysInAdvanced></schedule></xml>', null, 'ACTIVE',@email_id);

INSERT INTO ohrm_config(`property`,`value`) VALUES ('performance.competency_rendering_method','RatingWithCommentType');
INSERT INTO ohrm_config(`property`,`value`) VALUES ('performance.goal_rendering_method','RatingWithCommentType');
INSERT INTO ohrm_config(`property`,`value`) VALUES ('performance.question_rendering_method','TextAreaType');
INSERT INTO ohrm_config(`property`,`value`) VALUES ('performance.final_rating_source','mainEvaluators');
INSERT INTO ohrm_config(`property`,`value`) VALUES ('performance.appraisal_cycle','on');
INSERT INTO ohrm_config(`property`,`value`) VALUES ('performance.incognito.settings','<?xml version="1.0" encoding="UTF-8"?>
<data name="data">
    <user name="ESS">
      <evaluatorGroup name="mainEvaluators">
          <evaluatorPicture>Show picture</evaluatorPicture>
          <evaluatorName>Show name</evaluatorName>
      </evaluatorGroup>
      <evaluatorGroup name="self">
          <evaluatorPicture>Show picture</evaluatorPicture>
          <evaluatorName>Show name</evaluatorName>
      </evaluatorGroup>
      <evaluatorGroup name="supervisors">
          <evaluatorPicture>Show picture</evaluatorPicture>
          <evaluatorName>Show name</evaluatorName>
      </evaluatorGroup>
      <evaluatorGroup name="peers">
          <evaluatorPicture>Show picture</evaluatorPicture>
          <evaluatorName>Show name</evaluatorName>
      </evaluatorGroup>
      <evaluatorGroup name="subordinates">
          <evaluatorPicture>Show picture</evaluatorPicture>
          <evaluatorName>Show name</evaluatorName>
      </evaluatorGroup>
    </user>
    <user name="SUPERVISOR">
      <evaluatorGroup name="Main Evaluator">
          <evaluatorPicture>Show picture</evaluatorPicture>
          <evaluatorName>Show name</evaluatorName>
      </evaluatorGroup>
    </user>
</data>');

INSERT INTO ohrm_config(`property`,`value`) VALUES ('performance.show_final_rating_as_percentage','off');

INSERT INTO `ohrm_goal_workflow_status` (`state_id`, `name`, `display_name`, `consider_as_approved`, `send_reminders`) VALUES
(1, 'INITIAL', 'Initial', 0, 0),
(2, 'SAVED', 'Saved', 0, 0),
(3, 'PENDING_SUPERVISOR_APPROVAL', 'Pending Approval', 0, 0),
(4, 'APPROVED', 'Approved', 1, 1),
(5, 'REJECTED', 'Rejected', 0, 0);

INSERT INTO `ohrm_config`(`property`, `value`) VALUES
('performance.goal_definition_workflow','off'),
('performance.goal_max_completion_percentage','999');



UPDATE  `ohrm_menu_item` SET  `uisref` =  'performance.view_appraisals_list({"resetPagination": true})' WHERE `ohrm_menu_item`.`menu_title` ='Appraisal List';
UPDATE  `ohrm_menu_item` SET  `uisref` =  'performance.view_my_appraisals({"resetPagination": true})' WHERE `ohrm_menu_item`.`menu_title` ='My Appraisals';
UPDATE  `ohrm_menu_item` SET  `uisref` =  'performance.appraisal_cycles' WHERE `ohrm_menu_item`.`menu_title` ='Appraisal Cycles';


INSERT INTO `ohrm_audittrail_module` (`id`, `name`) VALUES (NULL, 'Performance');
INSERT INTO `ohrm_audittrail_module` (`id`, `name`) VALUES (NULL, 'Goals');

SET @ohrm_audittrail_module_performance_id := (SELECT `id` FROM `ohrm_audittrail_module` WHERE `name`='Performance');

INSERT INTO `ohrm_audittrail_section` (`name`, `description`, `affected_entity`, `module_id`) VALUES
('AppraisalList', 'Appraisal List', 'Employee', @ohrm_audittrail_module_performance_id);

SET @appraisallist_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='AppraisalList');
INSERT INTO `ohrm_audittrail_section_actions` (`section_id` ,`action` ,`description`) VALUES
(@appraisallist_section_id, 'DELETE APPRAISAL', 'Delete Appraisal');


-- adding competency mapping audit trail/section/action/listeners
 DROP TABLE IF EXISTS `ohrm_audittrail_competency_mapping_trail`;
 CREATE TABLE `ohrm_audittrail_competency_mapping_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) NOT NULL,
  `version_id` INT NOT NULL DEFAULT 0,
  `action` varchar(255) NOT NULL,
  `affected_entity_id` VARCHAR(20),
  `affected_entity_name` TEXT,
  `action_description` TEXT CHARSET UTF8,
  `app_id` INT DEFAULT NULL,
  `app_name` VARCHAR(1000) DEFAULT NULL,
  `screen_name` VARCHAR(1000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX audit_competency_mapping_trail_idx_app_id (app_id),
  INDEX audit_competency_mapping_trail_idx_screen_name (screen_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET @audittrail_module_id := (SELECT `id` FROM `ohrm_audittrail_module` WHERE `name` = 'Performance');

INSERT INTO `ohrm_audittrail_section` (`name`, `description`, `affected_entity`, `module_id`) VALUES ('CompetencyMapping', 'Competency Mapping', '', @audittrail_module_id);
SET @performance_competency_mapping_audit_trail_section := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name` = 'CompetencyMapping' AND `module_id` = @audittrail_module_id);

INSERT INTO `ohrm_audittrail_section_actions` (`section_id`, `action`, `description`) VALUES
  (@performance_competency_mapping_audit_trail_section, 'ADD COMPETENCY MAPPING', 'Add Competency Mapping'),
  (@performance_competency_mapping_audit_trail_section, 'UPDATE COMPETENCY MAPPING', 'Update Competency Mapping'),
  (@performance_competency_mapping_audit_trail_section, 'DELETE COMPETENCY MAPPING', 'Delete Competency Mapping');

CREATE TABLE `ohrm_audittrail_competency_profile_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) NOT NULL,
  `version_id` INT NOT NULL DEFAULT 0,
  `action` varchar(255) NOT NULL,
  `affected_entity_id` VARCHAR(20),
  `affected_entity_name` TEXT,
  `action_description` TEXT CHARSET UTF8,
  `app_id` INT DEFAULT NULL,
  `app_name` VARCHAR(1000) DEFAULT NULL,
  `screen_name` VARCHAR(1000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX audit_competency_profile_trail_idx_app_id (app_id),
  INDEX audit_competency_profile_trail_idx_screen_name (screen_name)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `ohrm_audittrail_section` (`name`, `description`, `affected_entity`, `module_id`) VALUES ('CompetencyProfile', 'Competency Profile', '', @audittrail_module_id);
SET @performance_competency_profile_audit_trail_section := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name` = 'CompetencyProfile' AND `module_id` = @audittrail_module_id);

INSERT INTO `ohrm_audittrail_section_actions` (`section_id`, `action`, `description`) VALUES
  (@performance_competency_profile_audit_trail_section, 'ADD COMPETENCY PROFILE', 'Add Competency Profile'),
  (@performance_competency_profile_audit_trail_section, 'UPDATE COMPETENCY PROFILE', 'Update Competency Profile'),
  (@performance_competency_profile_audit_trail_section, 'DELETE COMPETENCY PROFILE', 'Delete Competency Profile'),
  (@performance_competency_profile_audit_trail_section, 'COPY COMPETENCY PROFILE', 'Copy Competency Profile');

INSERT INTO `ohrm_event_listener` (`event`, `listener_class`, `enabled`, `position`) VALUES
('performance.add_competency_profile', 'PerformanceAddCompetencyProfileListener', '1', '104'),
('performance.update_competency_profile', 'PerformanceUpdateCompetencyProfileListener', '1', '105'),
('performance.delete_competency_profile', 'PerformanceDeleteCompetencyProfileListener', '1', '106'),
('performance.copy_competency_profile', 'PerformanceCopyCompetencyProfileListener', '1', '107'),
('admin.subunit_add', 'SubunitAddListenerForCompetencyProfileUpdate', 1, '108'),
('admin.competency_delete', 'CompetencyDeleteListenerForCompetencyProfileUpdate', 1, '109'),
('admin.subunit_delete', 'SubunitDeleteListenerForCompetencyProfileUpdate', 1, '110'),
('admin.job_title_delete', 'JobTitleDeleteListenerForCompetencyProfileUpdate', 1, '111');

-- adding appraisal cycle audit trail/section/action/listeners
CREATE TABLE `ohrm_audittrail_appraisal_cycle_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) NOT NULL,
  `version_id` INT NOT NULL DEFAULT 0,
  `action` varchar(255) NOT NULL,
  `affected_entity_id` VARCHAR(20),
  `affected_entity_name` TEXT,
  `action_description` TEXT CHARSET UTF8,
  `app_id` INT DEFAULT NULL,
  `app_name` VARCHAR(1000) DEFAULT NULL,
  `screen_name` VARCHAR(1000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX audit_appraisal_cycle_trail_idx_app_id (app_id),
  INDEX audit_appraisal_cycle_trail_idx_screen_name (screen_name)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET @audittrail_module_id := (SELECT `id` FROM `ohrm_audittrail_module` WHERE `name` = 'Performance');

INSERT INTO `ohrm_audittrail_section` (`name`, `description`, `affected_entity`, `module_id`) VALUES ('AppraisalCycle', 'Appraisal Cycle', 'Employee', @audittrail_module_id);
SET @performance_appraisal_cycle_audit_trail_section := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name` = 'AppraisalCycle' AND `module_id` = @audittrail_module_id);

INSERT INTO `ohrm_audittrail_section_actions` (`section_id`, `action`, `description`) VALUES
  (@performance_appraisal_cycle_audit_trail_section, 'ADD APPRAISAL CYCLE', 'Add Appraisal Cycle'),
  (@performance_appraisal_cycle_audit_trail_section, 'UPDATE APPRAISAL CYCLE', 'Update Appraisal Cycle'),
  (@performance_appraisal_cycle_audit_trail_section, 'DELETE APPRAISAL CYCLE', 'Delete Appraisal Cycle'),
  (@performance_appraisal_cycle_audit_trail_section, 'ASSIGN MAIN EVALUATOR', 'Assign Main Evaluator'),
  (@performance_appraisal_cycle_audit_trail_section, 'REMOVE MAIN EVALUATOR', 'Remove Main Evaluator'),
  (@performance_appraisal_cycle_audit_trail_section, 'ADD EMPLOYEES TO APPRAISAL CYCLE', 'Add Employees to Appraisal Cycle'),
  (@performance_appraisal_cycle_audit_trail_section, 'REMOVE EMPLOYEES FROM APPRAISAL CYCLE', 'Remove Employees from Appraisal Cycle'),
  (@performance_appraisal_cycle_audit_trail_section, 'CREATE APPRAISALS VIA CYCLE', 'Create Appraisals via Cycle'),
  (@performance_appraisal_cycle_audit_trail_section, 'ACTIVATE APPRAISALS VIA CYCLE', 'Activate Appraisals via Cycle');

SET @event_listener_max_position := (SELECT MAX(`position`) FROM `ohrm_event_listener`);

INSERT INTO `ohrm_event_listener` (`id`, `event`, `listener_class`, `enabled`, `position`) VALUES
(NULL, 'performance.appraisal_cycle_add', 'PerformanceAppraisalCycleAddListener', '1', @event_listener_max_position + 1),
(NULL, 'performance.appraisal_cycle_update', 'PerformanceAppraisalCycleUpdateListener', '1', @event_listener_max_position + 2),
(NULL, 'performance.appraisal_cycle_delete', 'PerformanceAppraisalCycleDeleteListener', '1', @event_listener_max_position + 3),
(NULL, 'performance.appraisal_cycle_change_employees', 'PerformanceAppraisalCycleChangeEmployeesListener', '1', @event_listener_max_position + 4),
(NULL, 'performance.appraisal_cycle_change_appraisals', 'PerformanceAppraisalCycleChangeAppraisalsListener', '1', @event_listener_max_position + 5);


-- adding employee goal audit trail/section/action/listeners
CREATE TABLE IF NOT EXISTS `ohrm_audittrail_employee_goal_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_employee_goal_trail_idx_app_id (app_id),
   INDEX audit_employee_goal_trail_idx_screen_name (screen_name)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET @audittrail_goal_module_id := (SELECT `id` FROM `ohrm_audittrail_module` WHERE `name` = 'Goals');

INSERT INTO `ohrm_audittrail_section` (`name`, `description`, `affected_entity`, `module_id`) VALUES ('EmployeeGoal', 'Goals', 'Employee', @audittrail_goal_module_id);
SET @performance_employee_goal_audit_trail_section := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name` = 'EmployeeGoal' AND `module_id` = @audittrail_goal_module_id);

INSERT INTO `ohrm_audittrail_section_actions` (`section_id`, `action`, `description`) VALUES
  (@performance_employee_goal_audit_trail_section, 'ADD GOAL', 'Add Goal'),
  (@performance_employee_goal_audit_trail_section, 'UPDATE GOAL', 'Update Goal'),
  (@performance_employee_goal_audit_trail_section, 'DELETE GOAL', 'Delete Goal'),
  (@performance_employee_goal_audit_trail_section, 'BULK ASSIGN GOAL', 'Bulk Assign Goal'),
  (@performance_employee_goal_audit_trail_section, 'ADD GOAL TO LIBRARY', 'Add Goal to Library'),
  (@performance_employee_goal_audit_trail_section, 'DELETE GOAL FROM LIBRARY', 'Delete Goal from Library'),
  (@performance_employee_goal_audit_trail_section, 'ADD COMMENT', 'Add Comment'),
  (@performance_employee_goal_audit_trail_section, 'DELETE COMMENT', 'Delete Comment'),
  (@performance_employee_goal_audit_trail_section, 'ADD KEY RESULT TO OBJECTIVE', 'Add Key Result to Objective'),
  (@performance_employee_goal_audit_trail_section, 'DELETE KEY RESULT FROM OBJECTIVE', 'Delete Key Result from Objective'),
  (@performance_employee_goal_audit_trail_section, 'UPDATE KEY RESULT OF OBJECTIVE', 'Update Key Result of Objective'),
  (@performance_employee_goal_audit_trail_section, 'SAVE GOAL', 'Save Goal'),
  (@performance_employee_goal_audit_trail_section, 'SUBMIT GOAL', 'Submit Goal'),
  (@performance_employee_goal_audit_trail_section, 'APPROVE GOAL', 'Approve Goal'),
  (@performance_employee_goal_audit_trail_section, 'REJECT GOAL', 'Reject Goal'),
  (@performance_employee_goal_audit_trail_section, 'ADD COMMENT TO KEY RESULT', 'Add Comment to Key Result'),
  (@performance_employee_goal_audit_trail_section, 'DELETE COMMENT FROM KEY RESULT', 'Delete Comment from Key Result');

SET @event_listener_max_position := (SELECT MAX(`position`) FROM `ohrm_event_listener`);

INSERT INTO `ohrm_event_listener` (`id`, `event`, `listener_class`, `enabled`, `position`) VALUES
(NULL, 'performance.add_employee_goal', 'PerformanceAddGoalListener', '1', @event_listener_max_position + 1),
(NULL, 'performance.update_employee_goal', 'PerformanceUpdateGoalListener', '1', @event_listener_max_position + 2),
(NULL, 'performance.delete_employee_goal', 'PerformanceDeleteGoalListener', '1', @event_listener_max_position + 3),
(NULL, 'performance.buk_assign_goal', 'PerformanceBulkAssignGoalListener', '1', @event_listener_max_position + 4),
(NULL, 'performance.add_goal_template', 'PerformanceAddGoalTemplateListener', '1', @event_listener_max_position + 5),
(NULL, 'performance.delete_goal_template', 'PerformanceDeleteGoalTemplateListener', '1', @event_listener_max_position + 6),
(NULL, 'performance.add_goal_comment', 'PerformanceAddGoalCommentListener', '1', @event_listener_max_position + 7),
(NULL, 'performance.delete_goal_comment', 'PerformanceDeleteGoalCommentListener', '1', @event_listener_max_position + 8),
(NULL, 'performance.add_key_result_to_objective', 'PerformanceAddKeyResultToObjectiveListener', '1', @event_listener_max_position + 9),
(NULL, 'performance.update_key_result_of_objective', 'PerformanceUpdateKeyResultOfObjectiveListener', '1', @event_listener_max_position + 10),
(NULL, 'performance.add_key_result_comment', 'PerformanceAddKeyResultCommentListener', '1', @event_listener_max_position + 11),
(NULL, 'performance.delete_key_result_comment', 'PerformanceDeleteKeyResultCommentListener', '1', @event_listener_max_position + 12);


-- adding appraisal configuration audit trail/section/action/listeners
CREATE TABLE IF NOT EXISTS `ohrm_audittrail_appraisal_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_appraisal_configuration_trail_idx_app_id (app_id),
   INDEX audit_appraisal_configuration_trail_idx_screen_name (screen_name)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET @audittrail_module_id := (SELECT `id` FROM `ohrm_audittrail_module` WHERE `name` = 'Performance');

INSERT INTO `ohrm_audittrail_section` (`name`, `description`, `affected_entity`, `module_id`) VALUES ('AppraisalConfiguration', 'Appraisal Configuration', '', @audittrail_module_id);
SET @performance_appraisal_configuration_audit_trail_section := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name` = 'AppraisalConfiguration' AND `module_id` = @audittrail_module_id);

INSERT INTO `ohrm_audittrail_section_actions` (`section_id`, `action`, `description`) VALUES
  (@performance_appraisal_configuration_audit_trail_section, 'UPDATE EVALUATOR GROUPS', 'Update Evaluator Groups'),
  (@performance_appraisal_configuration_audit_trail_section, 'ADD QUESTION', 'Add Question'),
  (@performance_appraisal_configuration_audit_trail_section, 'UPDATE QUESTION', 'Update Question'),
  (@performance_appraisal_configuration_audit_trail_section, 'DELETE QUESTION', 'Delete Question'),
  (@performance_appraisal_configuration_audit_trail_section, 'ADD HEADER TEMPLATE', 'Add Header Template'),
  (@performance_appraisal_configuration_audit_trail_section, 'UPDATE HEADER TEMPLATE', 'Update Header Template'),
  (@performance_appraisal_configuration_audit_trail_section, 'DELETE HEADER TEMPLATE', 'Delete Header Template'),
  (@performance_appraisal_configuration_audit_trail_section, 'CHANGE SECTION WEIGHT', 'Change Section Weight'),
  (@performance_appraisal_configuration_audit_trail_section, 'ADD APPRAISAL TEMPLATE', 'Add Appraisal Template'),
  (@performance_appraisal_configuration_audit_trail_section, 'UPDATE APPRAISAL TEMPLATE', 'Update Appraisal Template'),
  (@performance_appraisal_configuration_audit_trail_section, 'COPY APPRAISAL TEMPLATE', 'Copy Appraisal Template'),
  (@performance_appraisal_configuration_audit_trail_section, 'DELETE APPRAISAL TEMPLATE', 'Delete Appraisal Template'),
  (@performance_appraisal_configuration_audit_trail_section, 'ADD SECTION TO APPRAISAL TEMPLATE FORM', 'Add Section to Appraisal Template Form'),
  (@performance_appraisal_configuration_audit_trail_section, 'DELETE SECTION FROM APPRAISAL TEMPLATE FORM', 'Delete Section from Appraisal Template Form'),
  (@performance_appraisal_configuration_audit_trail_section, 'UPDATE APPRAISAL TEMPLATE SECTION', 'Update Appraisal Template Section');

SET @event_listener_max_position := (SELECT MAX(`position`) FROM `ohrm_event_listener`);

INSERT INTO `ohrm_event_listener` (`id`, `event`, `listener_class`, `enabled`, `position`) VALUES
(NULL, 'performance.update_evaluator_groups', 'PerformanceUpdateEvaluatorGroupsListener', '1', @event_listener_max_position + 1),
(NULL, 'performance.add_question', 'PerformanceAddQuestionListener', '1', @event_listener_max_position + 2),
(NULL, 'performance.update_question', 'PerformanceUpdateQuestionListener', '1', @event_listener_max_position + 3),
(NULL, 'performance.delete_question', 'PerformanceDeleteQuestionListener', '1', @event_listener_max_position + 4),
(NULL, 'performance.add_appraisal_template', 'PerformanceAddAppraisalTemplateListener', '1', @event_listener_max_position + 5),
(NULL, 'performance.update_appraisal_template', 'PerformanceUpdateAppraisalTemplateListener', '1', @event_listener_max_position + 6),
(NULL, 'performance.delete_appraisal_template', 'PerformanceDeleteAppraisalTemplateListener', '1', @event_listener_max_position + 7),
(NULL, 'performance.change_section_weight', 'PerformanceChangeSectionWeightListener', '1', @event_listener_max_position + 8);

-- Adding appraisal list audit action/listeners
SET @audittrail_module_id := (SELECT `id` FROM `ohrm_audittrail_module` WHERE `name` = 'Performance');
SET @performance_appraisal_list_audit_trail_section := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name` = 'AppraisalList' AND `module_id` = @audittrail_module_id);

INSERT INTO `ohrm_audittrail_section_actions` (`section_id`, `action`, `description`) VALUES
  (@performance_appraisal_list_audit_trail_section, 'CREATE APPRAISAL', 'Create Appraisal'),
  (@performance_appraisal_list_audit_trail_section, 'ACTIVATE APPRAISAL', 'Activate Appraisal'),
  (@performance_appraisal_list_audit_trail_section, 'APPROVE REVIEW', 'Approve Review'),
  (@performance_appraisal_list_audit_trail_section, 'REJECT REVIEW', 'Reject Review'),
  (@performance_appraisal_list_audit_trail_section, 'SIGN REVIEW', 'Sign Review'),
  (@performance_appraisal_list_audit_trail_section, 'ACKNOWLEDGE REVIEW', 'Acknowledge Review'),
  (@performance_appraisal_list_audit_trail_section, 'UPDATE EVALUATORS', 'Update Evaluators'),
  (@performance_appraisal_list_audit_trail_section, 'ADD SECTION TO APPRAISAL FORM', 'Add Section to Appraisal Form'),
  (@performance_appraisal_list_audit_trail_section, 'DELETE SECTION FROM APPRAISAL FORM', 'Delete Section from Appraisal'),
  (@performance_appraisal_list_audit_trail_section, 'ADD NEW QUESTION TO APPRAISAL FORM', 'Add New Question to Appraisal Form'),
  (@performance_appraisal_list_audit_trail_section, 'UPDATE QUESTION IN APPRAISAL FORM', 'Update Question in Appraisal Form'),
  (@performance_appraisal_list_audit_trail_section, 'DELETE QUESTION IN APPRAISAL FORM', 'Delete Question in Appraisal Form'),
  (@performance_appraisal_list_audit_trail_section, 'ADD NEW COMPETENCY TO APPRAISAL FORM', 'Add New Competency to Appraisal Form'),
  (@performance_appraisal_list_audit_trail_section, 'UPDATE COMPETENCY ADDED TO APPRAISAL FORM', 'Update Competency added to Appraisal Form'),
  (@performance_appraisal_list_audit_trail_section, 'DELETE COMPETENCY ADDED TO APPRAISAL FORM', 'Delete Competency added to Appraisal Form'),
  (@performance_appraisal_list_audit_trail_section, 'UPDATE GENERAL SETTINGS IN APPRAISAL FORM', 'Update General Settings in Appraisal Form'),
  (@performance_appraisal_list_audit_trail_section, 'UPDATE APPRAISAL DETAILS', 'Update Appraisal Details'),
  (@performance_appraisal_list_audit_trail_section, 'UPDATE APPRAISAL SECTION', 'Update Appraisal Section'),
  (@performance_appraisal_list_audit_trail_section, 'SAVE REVIEW', 'Save Review'),
  (@performance_appraisal_list_audit_trail_section, 'SUBMIT REVIEW', 'Submit Review');

SET @event_listener_max_position := (SELECT MAX(`position`) FROM `ohrm_event_listener`);

INSERT INTO `ohrm_event_listener` (`id`, `event`, `listener_class`, `enabled`, `position`) VALUES
(NULL, 'performance.delete_appraisal', 'PerformanceDeleteAppraisalListener', '1', @event_listener_max_position + 1),
(NULL, 'performance.activate_appraisal', 'PerformanceActivateAppraisalListener', '1', @event_listener_max_position + 2),
(NULL, 'performance.review_final_status_change', 'PerformanceReviewFinalStatusChangeListener', '1', @event_listener_max_position + 3),
(NULL, 'performance.create_appraisal', 'PerformanceCreateAppraisalListener', '1', @event_listener_max_position + 4),
(NULL, 'performance.appraisal_update_evaluators', 'PerformanceAppraisalUpdateEvaluatorsListener', '1', @event_listener_max_position + 5),
(NULL, 'performance.add_section_to_appraisal_form', 'PerformanceAddSectionToAppraisalFormListener', '1', @event_listener_max_position + 6),
(NULL, 'performance.delete_section_from_appraisal_form', 'PerformanceDeleteSectionFromAppraisalFormListener', '1', @event_listener_max_position + 7),
(NULL, 'performance.add_appraisal_question', 'PerformanceAddAppraisalQuestionListener', '1', @event_listener_max_position + 8),
(NULL, 'performance.update_appraisal_question', 'PerformanceUpdateAppraisalQuestionListener', '1', @event_listener_max_position + 9),
(NULL, 'performance.delete_appraisal_question', 'PerformanceDeleteAppraisalQuestionListener', '1', @event_listener_max_position + 10),
(NULL, 'performance.add_appraisal_competency', 'PerformanceAddAppraisalCompetencyListener', '1', @event_listener_max_position + 11),
(NULL, 'performance.update_appraisal_competency', 'PerformanceUpdateAppraisalCompetencyListener', '1', @event_listener_max_position + 12),
(NULL, 'performance.delete_appraisal_competency', 'PerformanceDeleteAppraisalCompetencyListener', '1', @event_listener_max_position + 13),
(NULL, 'performance.update_appraisal_details', 'PerformanceUpdateAppraisalDetailsListener', '1', @event_listener_max_position + 14),
(NULL, 'performance.update_appraisal_section_details', 'PerformanceUpdateAppraisalSectionDetailsListener', '1', @event_listener_max_position + 15),
(NULL, 'performance.review_save_submit', 'PerformanceReviewSaveSubmitListener', '1', @event_listener_max_position + 16),
(NULL, 'performance.update_appraisal_section_items', 'PerformanceUpdateAppraisalSectionItemsListener', '1', @event_listener_max_position + 17),
(NULL, 'performance.goal_state_change', 'PerformanceMailer', '1', @event_listener_max_position + 18),
(NULL, 'performance.add_section_to_appraisal_template_form', 'PerformanceAddSectionToAppraisalTemplateFormListener', '1', @event_listener_max_position + 19),
(NULL, 'performance.delete_section_from_appraisal_template_form', 'PerformanceDeleteSectionFromAppraisalTemplateFormListener', '1', @event_listener_max_position + 20),
(NULL, 'performance.update_appraisal_template_section_details', 'PerformanceUpdateAppraisalTemplateSectionDetailsListener', '1', @event_listener_max_position + 21),
(NULL, 'performance.update_appraisal_template_section_items', 'PerformanceUpdateAppraisalTemplateSectionItemsListener', '1', @event_listener_max_position + 22),
(NULL, 'performance.goal_state_change', 'PerformanceApproveOrRejectGoalListener', '1', @event_listener_max_position + 23),
(NULL, 'performance.update_general_settings_appraisal_form', 'PerformanceUpdateGeneralSettingsInAppraisalFormListener', '1', @event_listener_max_position + 24),
(NULL, 'performance.appraisal_reassign_evaluator', 'PerformanceMailer', '1', @event_listener_max_position + 25);

# Inserting a standard appraisal template

INSERT INTO `ohrm_question_form` (`name`) VALUES ('Appraisal Template Question Form');
SET @appraisal_template_question_form_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_question_form_section` (`section_name`, `question_form_id`, `order_number`, `weight`, `options`)
VALUES
  ('Competencies', @appraisal_template_question_form_id, 1, 50, '{"appraisal_form_config":{"is_default":true}}'),
  ('Goals', @appraisal_template_question_form_id, 2, 50, '{"appraisal_form_config":{"is_default":true}}'),
  ('Questions', @appraisal_template_question_form_id, 3, 0, '{"appraisal_form_config":{"is_default":true}}');

SET @competency_section_id := (SELECT `id`
                               FROM `ohrm_question_form_section`
                               WHERE `section_name` = 'Competencies');
SET @goal_section_id := (SELECT `id`
                         FROM `ohrm_question_form_section`
                         WHERE `section_name` = 'Goals');
SET @question_section_id := (SELECT `id`
                             FROM `ohrm_question_form_section`
                             WHERE `section_name` = 'Questions');

SET @competency_question_type_id := (SELECT `id`
                                     FROM `ohrm_performance_question_types`
                                     WHERE `name` = 'Competencies');
SET @goal_question_type_id := (SELECT `id`
                               FROM `ohrm_performance_question_types`
                               WHERE `name` = 'Goals');
SET @question_question_type_id := (SELECT `id`
                                   FROM `ohrm_performance_question_types`
                                   WHERE `name` = 'Questions');

INSERT INTO `ohrm_performance_section_question_types` (`section_id`, `question_type_id`, `is_dynamic`)
VALUES
  (@competency_section_id, @competency_question_type_id, 1),
  (@goal_section_id, @goal_question_type_id, 1),
  (@question_section_id, @question_question_type_id, 1);

INSERT INTO `ohrm_appraisal_template` (`name`, `description`, `header`, `question_form_id`)
VALUES ('Standard Appraisal Template', 'Standard Appraisal Template', '', @appraisal_template_question_form_id);

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

INSERT INTO `ohrm_config`(`property`, `value`) VALUES ('performance.default_appraisal_template_id', @standard_appraisal_template_id);

CREATE TABLE IF NOT EXISTS `ohrm_performance_evaluator_section_attachment` (
    `section_attachment_id` INT NOT NULL,
    `employee_appraisal_evaluator_id` INT NOT NULL,
    PRIMARY KEY (`section_attachment_id`, `employee_appraisal_evaluator_id`),
    CONSTRAINT `fk_performance_evaluator_section_attachment_section_attachment` FOREIGN KEY (`section_attachment_id`) REFERENCES `ohrm_section_attachment`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_performance_evl_section_attachment_performance_appraisal_evl` FOREIGN KEY (`employee_appraisal_evaluator_id`) REFERENCES `ohrm_performance_appraisal_evaluator`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB default charset=utf8;

INSERT INTO ohrm_data_list (`name`, `meta_data`)
VALUES
    ('performanceAppraisalsList', '{"listFields":{"fromDate":"fromDate","toDate":"toDate","dueDate":"dueDate","completion":"completion","final_rating":"final_rating"},"availableFields":{"empId":"Employee Id","employeeName":"Employee Name","jobTitle":"Job Title","subUnit":"Sub Unit","location":"Location","fromDate":"From","toDate":"To","dueDate":"Due Date","description":"Description","appraisalStatus":"Appraisal Status","completion":"Review Progress","final_rating":"Final Rating"},"defaultFields":["fromDate","toDate","dueDate","completion","final_rating"],"mandatoryFields":["employeeName","description","appraisalStatus"]}'),
    ('performanceGoalsList', '{"listFields":{"dueDate":"dueDate","status":"status","level":"level","priority":"priority","weight":"weight"},"availableFields":{"goalName":"Goal Name", "empId":"Employee Id","employeeName":"Owner","jobTitle":"Job Title","location":"Location","subUnit":"Sub Unit","dueDate":"Due Date","status":"Status","level":"Level","priority":"Priority", "weight":"Weight"},"defaultFields":["dueDate","status","level","priority", "weight"],"mandatoryFields":["goalName","employeeName"],"pageParams":{"pageSize":20}}');

INSERT INTO ohrm_config(`property`,`value`) VALUES ('performance.consider_empty_sections_for_calculation','off');
INSERT INTO ohrm_config(`property`,`value`) VALUES ('performance.goal_weight_values_configuration','{"min_goal_weight":1,"max_goal_weight":10,"default_goal_weight":5}');
INSERT INTO ohrm_config(`property`,`value`) VALUES ('performance.unmask_average_ratings_in_report_when_grouping_if_one_review_is_readable','0');



SET @ohrm_data_group_appraisal_config_evaluators_id =  (SELECT `id`  FROM `ohrm_data_group` WHERE `name` = 'appraisal_config_evaluators');
SET @ohrm_data_group_performance_questions_id =  (SELECT `id`  FROM `ohrm_data_group` WHERE `name` = 'performance_questions');
SET @ohrm_data_group_appraisal_config_appraisal_template_id =  (SELECT `id`  FROM `ohrm_data_group` WHERE `name` = 'appraisal_config_appraisal_template');
SET @ohrm_data_group_appraisal_cycle_id =  (SELECT `id`  FROM `ohrm_data_group` WHERE `name` = 'Appraisal Cycle');
SET @ohrm_data_group_employee_appraisals_id =  (SELECT `id`  FROM `ohrm_data_group` WHERE `name` = 'employee_appraisals');
SET @ohrm_data_group_competency_profiles_id =  (SELECT `id`  FROM `ohrm_data_group` WHERE `name` = 'competency_profiles');
SET @ohrm_data_group_goals_id =  (SELECT `id`  FROM `ohrm_data_group` WHERE `name` = 'Goals');

SET @ohrm_audittrail_appraisal_configuration= (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='AppraisalConfiguration');
SET @ohrm_audittrail_appraisal_cycle= (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='AppraisalCycle');
SET @ohrm_audittrail_appraisal_list= (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='AppraisalList');
SET @ohrm_audittrail_competency_profile= (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='CompetencyProfile');
SET @ohrm_audittrail_employee_goal= (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='EmployeeGoal');

INSERT INTO `ohrm_datagroup_audittrail_section` (`audit_section_id`, `datagroup_id`, `consider_in_notification`)
VALUES
    (@ohrm_audittrail_appraisal_configuration, @ohrm_data_group_appraisal_config_evaluators_id, 0),
    (@ohrm_audittrail_appraisal_configuration, @ohrm_data_group_performance_questions_id, 0),
    (@ohrm_audittrail_appraisal_configuration, @ohrm_data_group_appraisal_config_appraisal_template_id, 0),
    (@ohrm_audittrail_appraisal_cycle, @ohrm_data_group_appraisal_cycle_id, 0),
    (@ohrm_audittrail_appraisal_list, @ohrm_data_group_employee_appraisals_id, 0),
    (@ohrm_audittrail_competency_profile, @ohrm_data_group_competency_profiles_id, 0),
    (@ohrm_audittrail_employee_goal, @ohrm_data_group_goals_id, 0);
