SET @performace_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'Performance');

-- removing performance360 menu items
DELETE FROM `ohrm_menu_item` WHERE `menu_title`= 'Configure KPI' AND `parent_id` = @performace_menu_id;
DELETE FROM `ohrm_menu_item` WHERE `menu_title`= 'Manage Reviews' AND `parent_id` = @performace_menu_id;
DELETE FROM `ohrm_menu_item` WHERE `menu_title`= 'KPI';
DELETE FROM `ohrm_menu_item` WHERE `menu_title`= 'KPI Group';
DELETE FROM `ohrm_menu_item` WHERE `menu_title`= 'Appraisal Header';
DELETE FROM `ohrm_menu_item` WHERE `menu_title`= 'Manage Reviews';
DELETE FROM `ohrm_menu_item` WHERE `menu_title`= 'My Reviews';
DELETE FROM `ohrm_menu_item` WHERE `menu_title`= 'Review List';

-- setting new values for top level performance menu
UPDATE `ohrm_menu_item` SET `screen_id` = null, `order_hint` = 1000 WHERE `id` = @performace_menu_id;


-- main dbscript of new perormance module

-- ------------- 
-- 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 IF NOT EXISTS `ohrm_performance_appraisal` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `emp_number` INT NOT NULL,
  `description` VARCHAR(255) NOT NULL ,
  `from_date` DATE NOT NULL,
  `to_date` DATE NOT NULL,
  `due_date` DATE NOT NULL,
  `question_form_id` int,
  `state_id` int,

  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
)
ENGINE = InnoDB DEFAULT CHARACTER SET = utf8;

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
) 
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_performance_goal` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `emp_number` INT NOT NULL,
  `name` VARCHAR(255) NOT NULL ,
  `description` VARCHAR(2048) NULL,
  `due_date` DATE NULL,
  `created_date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`) ,
  INDEX `name` (`name` ASC) 
)
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, 

  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,

  PRIMARY KEY (`id`)
)
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,
  `status` int NOT NULL DEFAULT 0,
  `admin_comment` VARCHAR(2048), 
  `group_id` int ,
  `deleted_at` DATETIME 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 (`group_id`) REFERENCES `ohrm_performance_evaluator_group`(`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,
    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 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` VARCHAR(3000) CHARSET UTF8,

   PRIMARY KEY (`id`)   
)
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`(
  `appraisal_evaluator_group_id`  int NOT NULL,
  `question_id` int NOT NULL,

  PRIMARY KEY (`appraisal_evaluator_group_id`,`question_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
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- -----------------------------------------
-- 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,
  `answer_id` int NOT NULL,

  PRIMARY KEY (`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
) 
ENGINE=InnoDB;
-- ----------------------------------------------------------------------------------------------------------
-- Adding Workflow Dependancy Table
-- ----------------------------------------------------------------------------------------------------------
CREATE TABLE   `ohrm_workflow_state_machine_dependancy` (
  `id` BIGINT AUTO_INCREMENT, 
  `master_state_change_id` BIGINT NOT NULL, 
  `slave_state_change_id` BIGINT NOT NULL, 
  `trigger_verification` VARCHAR(100), 

  PRIMARY KEY(`id`),
  FOREIGN KEY (`master_state_change_id`) REFERENCES `ohrm_workflow_state_machine`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`slave_state_change_id`) REFERENCES `ohrm_workflow_state_machine`(`id`) ON DELETE 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 Status to status table
-- --------------
INSERT INTO  ohrm_performance_appraisal_status (state_id, name, description) VALUES
(0,'NOT INITIATE', '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' );


INSERT INTO  ohrm_performance_review_status (state_id, name, description) VALUES 
(0,'INITIATED', 'Main appraisal has been initiated' ),
(1,'IN PROGRESS', 'Evaluator is evaluating the appraisal' ),
(2,'SUBMITTED', 'Evaluator has submitted the appraisal' ),
(3,'APPROVED', 'Admin has approved the appraisal' ),
(4,'APPRAISAL COMPLETED', 'Appraisal has been completed' ),
(5,'REJECTED', 'Admin has rejected the appraisal' );



INSERT INTO  ohrm_performance_evaluator_group(group_name, group_alias, activated) VALUES
('mainEvaluators', 'Main Evaluator', 1),
('self', 'Self', 1),
('supervisors', 'Supervisors', 1),
('peers', 'Peers', 1),
('subordinates', 'Subordinates', 1);


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

INSERT INTO ohrm_performance_appraisal_global_form_configuration (`section_title`, `competency_matching_criteria`, `goal_matching_criteria`, `question_matching_criteria`) VALUES
('Competencies', 'SELECT id FROM `ohrm_performance_competency` as c LEFT JOIN `ohrm_performance_title_competency` as jc ON c.id = jc.employee_competency_id WHERE c.appraisal_id IS NULL AND c.deleted_at IS NULL AND jc.job_title_id = #jobTitleId# UNION SELECT id FROM `ohrm_performance_competency`as c1 WHERE c1.appraisal_id = #appraisalId# AND c1.deleted_at IS NULL', NULL, NULL),
('Goals', NULL, 'SELECT id FROM `ohrm_performance_goal` WHERE emp_number = #empNumber# AND due_date BETWEEN #appraisalStartDate# AND #appraisalEndDate#', NULL),
('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 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#");

-- -----------------------------------------
-- Insert rating scale configuration
-- -----------------------------------------
INSERT INTO `hs_hr_config`(`key`, `value`) VALUES ('performance.decimal_rating_scale','on');


-- --------------
-- 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);


-- ------------- 
-- Alter table
-- -------------

ALTER TABLE `ohrm_performance_goal`
  ADD CONSTRAINT `fk_ohrm_performance_goal_1` FOREIGN KEY (`emp_number`) 
  REFERENCES `hs_hr_employee` (`emp_number`)
ON DELETE CASCADE ON UPDATE CASCADE;

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

SET @goals_menu_id  := (SELECT LAST_INSERT_ID());
-- --------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 ('MainEvaluator','MainEvaluator',0,1);

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 @main_evaluator_role_id := (SELECT `id` FROM ohrm_user_role WHERE `name` = 'MainEvaluator');
SET @appraisal_reviewer_role_id := (SELECT `id` FROM ohrm_user_role WHERE `name` = 'AppraisalReviewer');

-- "employee competency list" screen --ok
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('Employee Competency Screen', @module_id, '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);

INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) VALUES
('Competencies', @competency_list_screen_id, @performace_menu_id, 2, 1000, NULL, 1);

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

-- "goal list" screen
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('Goal List', @module_id, '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),
(@ess_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`, `status`) VALUES
('Goals', @goal_list_screen_id, @performace_menu_id, 2, 1000, NULL, 1);
SET @goals_menu_id  := (SELECT LAST_INSERT_ID());
 
-- "add goals" screen
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('Add Goal', @module_id, 'addEmployeeGoal');
SET @add_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
(@admin_role_id, @add_goal_screen_id, 1, 1, 1, 1),
(@supervisor_role_id, @add_goal_screen_id, 1, 1, 1, 1),
(@ess_role_id, @add_goal_screen_id, 1, 1, 1, 1);


-- "employee goal" screen
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('Employee Goal', @module_id, 'employeeGoal');
SET @edit_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
(@admin_role_id, @edit_goal_screen_id, 1, 1, 1, 1),
(@ess_role_id, @edit_goal_screen_id, 1, 0, 0, 0);

-- "delete employee goal" screen
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('Delete Goals', @module_id, 'deleteEmployeeGoals');
SET @delete_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
(@admin_role_id, @delete_goal_screen_id, 1, 1, 1, 1),
(@supervisor_role_id, @delete_goal_screen_id, 1, 1, 1, 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);
-- "employee appraisal" screen
INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) VALUES
('Appraisals', null, @performace_menu_id, 2, 1000, NULL, 1);
SET @employee_appraisal_list_menu_id  := (SELECT LAST_INSERT_ID());

-- --- -- "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),
(@appraisal_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
('Employee Appraisals', @appraisal_list_screen_id, @employee_appraisal_list_menu_id, 3, 1000, NULL, 1);

-- -- --- -- "employee appraisal list" screen
-- INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
-- ('Employee Appraisal List', @module_id, 'viewEmployeeAppraisals');
-- SET @employee_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
-- (@appraisal_reviewer_role_id, @employee_appraisal_list_screen_id, 1, 0, 1, 0);
-- 
-- INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) VALUES
-- ('Evaluate Appraisals', @employee_appraisal_list_screen_id, @employee_appraisal_list_menu_id, 3, 1000, 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, @employee_appraisal_list_menu_id, 3, 1000, 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', @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);

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

-- "default view" screen
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('Default view', @module_id, 'defaultView');
SET @default_view_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_view_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
(@ess_role_id, @default_view_screen_id, 1, 0, 0, 0);

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

-- "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);

-- "table view" screen
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('Table view', @module_id, 'viewReviewTable');
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);

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

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

-- "appraisal config" screen
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', @evaluator_group_configurations_screen_id, @employee_configuration_menu_id , 3, 1000, NULL, 1);
SET @appraisal_configuration_menu_id  := (SELECT LAST_INSERT_ID());

-- "form format configurations config" screen
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('form format configurations', @module_id, 'appraisalFormatGlobalConf');
SET @form_format_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, @form_format_configurations_screen_id, 1, 1, 1, 1);


-- "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);


-- "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', 'Performance - Employee Goals', 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);
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);

-- "competencies" data group
INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('employee_competencies', 'Performance - 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);

-- "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),
(@main_evaluator_role_id, @employee_appraisal_data_group_id, 1, 1, 1, 0, 0),
(@appraisal_reviewer_role_id, @employee_appraisal_data_group_id, 1, 0, 0, 0, 0);
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, @employee_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
INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('my_appraisals', 'Performance - My Appraisals', 1, 1, 1, 1);
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, 1, 1, 1, 1);


-- "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 form format" data group
INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('appraisal_config_form_format', 'Performance - Appraisal Configuration Form Format', 1, NULL, NULL, NULL);
SET @appraisal_config_form_format_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_form_format_data_group_id, 1, 0, 0, 0, 0);
INSERT INTO `ohrm_user_role_type_data_group` (`user_role_type_id`, `data_group_id`) VALUES
(1, @appraisal_config_form_format_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`) VALUES (@goals_data_group_id,@add_goal_screen_id,2);
INSERT INTO `ohrm_data_group_screen`( `data_group_id`, `screen_id`, `permission`) VALUES (@goals_data_group_id,@edit_goal_screen_id,1);
INSERT INTO `ohrm_data_group_screen`( `data_group_id`, `screen_id`, `permission`) VALUES (@goals_data_group_id,@delete_goal_screen_id,4);

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,@default_view_screen_id,1);
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_specific_questions_data_group_id,@config_appraisal_questions_screen_id,1);
-- INSERT INTO `ohrm_data_group_screen`( `data_group_id`, `screen_id`, `permission`) VALUES (@appraisal_specific_questions_data_group_id,@config_appraisal_questions_screen_id,2);
-- INSERT INTO `ohrm_data_group_screen`( `data_group_id`, `screen_id`, `permission`) VALUES (@appraisal_specific_questions_data_group_id,@config_appraisal_questions_screen_id,3);
-- INSERT INTO `ohrm_data_group_screen`( `data_group_id`, `screen_id`, `permission`) VALUES (@appraisal_specific_questions_data_group_id,@config_appraisal_questions_screen_id,4);
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 (@appraisal_config_form_format_data_group_id,@form_format_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 (@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,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_default_permitted_screen` (`user_role_type_id`, `screen_id`) VALUES (@ess_role_id, @my_appraisal_list_screen_id);


-- workflows
INSERT INTO `ohrm_workflow_state_machine`(`workflow`,`state`,`role`,`action`,`resulting_state`, `roles_to_notify`, `is_user_visible_action`, `priority`)  
                            VALUES ('MAIN APPRAISAL','INITIATED','ADMIN','View','INITIATED', '', 1, 0),
                                   ('MAIN APPRAISAL','INITIATED','MAIN-EVALUATOR','View','INITIATED', '', 1, 0),
                                   ('MAIN APPRAISAL','INITIATED','SUPERVISOR','View','INITIATED', '', 1, 0),
                                   ('MAIN APPRAISAL','INITIATED','SYSTEM','Complete','COMPLETED', '', 1, 0);
SELECT @completed_mainappraisal_flow_id := MAX(`id`) FROM `ohrm_workflow_state_machine`;

/* INSERT INTO `ohrm_workflow_state_machine`(`workflow`,`state`,`role`,`action`,`resulting_state`, `roles_to_notify`, `is_user_visible_action`, `priority`)  
                            VALUES ('MAIN APPRAISAL','COMPLETED','MAIN-EVALUATOR','Reject','REJECTED', '', 1, 0),
                                    ('MAIN APPRAISAL','COMPLETED','MAIN-EVALUATOR','Approve','APPROVED', '', 1, 0);
SELECT @maineval_mainappraisal_approve_flow_id := MAX(`id`) FROM `ohrm_workflow_state_machine`;*/

INSERT INTO `ohrm_workflow_state_machine`(`workflow`,`state`,`role`,`action`,`resulting_state`, `roles_to_notify`, `is_user_visible_action`, `priority`)  
                            VALUES /*('MAIN APPRAISAL','APPROVED','ADMIN','View','APPROVED', '', 1, 0),
                                   ('MAIN APPRAISAL','APPROVED','MAIN-EVALUATOR','View','APPROVED', '', 1, 0),
                                   ('MAIN APPRAISAL','APPROVED','SUPERVISOR','View','APPROVED', '', 1, 0),
                                   ('MAIN APPRAISAL','REJECTED','ADMIN','View','REJECTED', '', 1, 0),
                                   ('MAIN APPRAISAL','REJECTED','MAIN-EVALUATOR','View','REJECTED', '', 1, 0),
                                   ('MAIN APPRAISAL','REJECTED','SUPERVISOR','View','REJECTED', '', 1, 0),*/
                                   ('MAIN APPRAISAL','COMPLETED','ADMIN','View','COMPLETED', '', 1, 0),
                                   ('MAIN APPRAISAL','COMPLETED','MAIN-EVALUATOR','View','COMPLETED', '', 1, 0),
                                   ('MAIN APPRAISAL','COMPLETED','SUPERVISOR','View','COMPLETED', '', 1, 0);
                                 /*  ('MAIN APPRAISAL','COMPLETED','ADMIN','Reject','REJECTED', '', 1, 0),
                                   ('MAIN APPRAISAL','COMPLETED','ADMIN','Approve','APPROVED', '', 1, 0);*/
SELECT @admin_mainappraisal_approve_flow_id := MAX(`id`) FROM `ohrm_workflow_state_machine`;

    

    SELECT @last_id := MAX(`id`) FROM `ohrm_workflow_state_machine`;

    INSERT INTO `ohrm_workflow_state_machine`(`workflow`,`state`,`role`,`action`,`resulting_state`, `roles_to_notify`, `is_user_visible_action`, `priority`) 
                            VALUES ('REVIEW','INITIATED','ADMIN','Read','INITIATED','',0,'0'),
                                   ('REVIEW','INITIATED','MAIN-EVALUATOR','Read','INITIATED','',0,'0'),
                                   ('REVIEW','INITIATED','EVALUATOR','Save','IN PROGRESS','',1,'0'),
                                   ('REVIEW','INITIATED','EVALUATOR','Submit','SUBMITTED','',1,'0'),
                                   ('REVIEW','IN PROGRESS','EVALUATOR','Save','IN PROGRESS','',1,'0'),
                                   ('REVIEW','IN PROGRESS','ADMIN','Read','IN PROGRESS','',0,'0'),
                                   ('REVIEW','IN PROGRESS','MAIN-EVALUATOR','Read','IN PROGRESS','',0,'0'),
                                   ('REVIEW','IN PROGRESS','EVALUATOR','Submit','SUBMITTED','',1,'0'),
                                   ('REVIEW','SUBMITTED','ADMIN','Read','SUBMITTED','',0,'0'),
                                   ('REVIEW','SUBMITTED','MAIN-EVALUATOR','Read','SUBMITTED','',0,'0'),
                                   ('REVIEW','SUBMITTED','ADMIN','Approve','APPROVED','',1,'0');
     SELECT @admin_approve_flow_id := MAX(`id`) FROM `ohrm_workflow_state_machine`;
    INSERT INTO `ohrm_workflow_state_machine` (`workflow`,`state`,`role`,`action`,`resulting_state`, `roles_to_notify`, `is_user_visible_action`, `priority`) 
                            VALUES ('REVIEW','SUBMITTED','MAIN-EVALUATOR','Approve','APPROVED','',1,'0');
     SELECT @main_eval_approve_flow_id := MAX(`id`) FROM `ohrm_workflow_state_machine`;
--     INSERT INTO `ohrm_workflow_state_machine` (`workflow`,`state`,`role`,`action`,`resulting_state`, `roles_to_notify`, `is_user_visible_action`, `priority`) 
--                             VALUES ('REVIEW','APPROVED','SYSTEM','All Completed','APPRAISAL COMPLETED','',1,'0');
--      SELECT @completed_emp_appr_flow_id := MAX(`id`) FROM `ohrm_workflow_state_machine`;

      INSERT INTO `ohrm_workflow_state_machine`(`workflow`,`state`,`role`,`action`,`resulting_state`, `roles_to_notify`, `is_user_visible_action`, `priority`)  
                            VALUES ('REVIEW','SUBMITTED','MAIN-EVALUATOR','Reject','REJECTED','',1,'0'),
                                   ('REVIEW','SUBMITTED','ADMIN','Reject','REJECTED','',1,'0'),
                                   ('REVIEW','SUBMITTED','EVALUATOR','Read','SUBMITTED', '', 0, 0),
                                   ('REVIEW','APPROVED','EVALUATOR','Read','APPROVED', '', 0, 0),
                                   ('REVIEW','APPROVED','ADMIN','Read','APPROVED', '', 0, 0),
                                   ('REVIEW','APPROVED','MAIN-EVALUATOR','Read','APPROVED', '', 0, 0),
                                   ('REVIEW','REJECTED','EVALUATOR','Read','REJECTED', '', 0, 0),
                                   ('REVIEW','REJECTED','ADMIN','Read','REJECTED', '', 0, 0),
                                   ('REVIEW','REJECTED','MAIN-EVALUATOR','Read','REJECTED', '', 0, 0),
                                   ('REVIEW','REJECTED','EVALUATOR','Save','IN PROGRESS', '', 1, 0),                                  
                                   ('REVIEW','REJECTED','EVALUATOR','Submit','SUBMITTED', '', 1, 0);
--                                    ('REVIEW','APPRAISAL COMPLETED','EVALUATOR','Read','APPRAISAL COMPLETED', '', 0, 0),
--                                    ('REVIEW','APPRAISAL COMPLETED','ADMIN','Read','APPRAISAL COMPLETED', '', 0, 0),
--                                    ('REVIEW','APPRAISAL COMPLETED','MAIN-EVALUATOR','Read','APPRAISAL COMPLETED', '', 0, 0);
    -- -----------------------------
    -- Final Review WorkFlow
    -- -----------------------------
    INSERT INTO `ohrm_workflow_state_machine`(`workflow`,`state`,`role`,`action`,`resulting_state`, `roles_to_notify`, `is_user_visible_action`, `priority`)  
                            VALUES ('FINAL REVIEW','INITIATED','MAIN-EVALUATOR','Save','IN PROGRESS', '', 1, 0),
                                    ('FINAL REVIEW','INITIATED','MAIN-EVALUATOR','Submit','SUBMITTED', '',1, 0),
                                   ('FINAL REVIEW','INITIATED','ADMIN','Save','IN PROGRESS', '', 1, 0),
                                   ('FINAL REVIEW','INITIATED','ADMIN','Submit','APPROVED', '', 1, 0);
    SELECT @final_review_admin_approve_flow_id := MAX(`id`) FROM `ohrm_workflow_state_machine`;
    INSERT INTO `ohrm_workflow_state_machine`(`workflow`,`state`,`role`,`action`,`resulting_state`, `roles_to_notify`, `is_user_visible_action`, `priority`)  
                            VALUES ('FINAL REVIEW','IN PROGRESS','ESS','Read','IN PROGRESS', '', 0, 0),
                                   ('FINAL REVIEW','IN PROGRESS','SUPERVISOR','Read','IN PROGRESS', '', 0, 0),
                                   ('FINAL REVIEW','IN PROGRESS','MAIN-EVALUATOR','Save','IN PROGRESS', '', 1, 0),
                                   ('FINAL REVIEW','IN PROGRESS','ADMIN','Save','IN PROGRESS', '', 1, 0),
                                   ('FINAL REVIEW','IN PROGRESS','MAIN-EVALUATOR','Submit','SUBMITTED', '', 1, 0), 
                                   ('FINAL REVIEW','IN PROGRESS','ADMIN','Submit','APPROVED', '', 1, 0);
     SELECT @final_review_admin_submit_approve_flow_id := MAX(`id`) FROM `ohrm_workflow_state_machine`;  
    INSERT INTO `ohrm_workflow_state_machine`(`workflow`,`state`,`role`,`action`,`resulting_state`, `roles_to_notify` , `is_user_visible_action`, `priority`)  
                            VALUES ('FINAL REVIEW','SUBMITTED','ADMIN','Approve','APPROVED', '', 1, 0);
    SELECT @final_review_maineval_submit_approve_flow_id := MAX(`id`) FROM `ohrm_workflow_state_machine`;
    INSERT INTO `ohrm_workflow_state_machine`(`workflow`,`state`,`role`,`action`,`resulting_state`, `roles_to_notify`, `is_user_visible_action`, `priority`)  
                            VALUES ('FINAL REVIEW','SUBMITTED','ADMIN','Reject','REJECTED', '', 1, 0),
                                   ('FINAL REVIEW','SUBMITTED','ESS','Read','SUBMITTED', '', 0, 0),
                                   ('FINAL REVIEW','SUBMITTED','SUPERVISOR','Read','SUBMITTED', '', 0, 0),
                                   ('FINAL REVIEW','SUBMITTED','MAIN-EVALUATOR','Read','SUBMITTED', '', 0, 0),
                                   ('FINAL REVIEW','APPROVED','ESS','Read','APPROVED', '', 0, 0),
                                   ('FINAL REVIEW','APPROVED','SUPERVISOR','Read','APPROVED', '', 0, 0),
                                   ('FINAL REVIEW','APPROVED','ADMIN','Read','APPROVED', '', 0, 0),
                                   ('FINAL REVIEW','APPROVED','MAIN-EVALUATOR','Read','APPROVED', '', 0, 0),
                                   ('FINAL REVIEW','REJECTED','ESS','Read','REJECTED', '', 0, 0),
                                   ('FINAL REVIEW','REJECTED','SUPERVISOR','Read','REJECTED', '', 0, 0),
                                   ('FINAL REVIEW','REJECTED','ADMIN','Read','REJECTED', '', 0, 0),
                                   ('FINAL REVIEW','REJECTED','MAIN-EVALUATOR','Read','REJECTED', '', 0, 0),
                                   ('FINAL REVIEW','REJECTED','ADMIN','Save','IN PROGRESS', '', 1, 0),
                                   ('FINAL REVIEW','REJECTED','MAIN-EVALUATOR','Save','IN PROGRESS', '', 1, 0),
                                   ('FINAL REVIEW','REJECTED','ADMIN','Submit','APPROVED', '', 1, 0),
                                   ('FINAL REVIEW','REJECTED','MAIN-EVALUATOR','Submit','SUBMITTED', '', 1, 0);
                                   
    SELECT @last_id := MAX(`id`) FROM `ohrm_workflow_state_machine`;
    -- ---------------------------------
    -- WorkFlow Dependancies
    -- ---------------------------------
INSERT INTO `ohrm_workflow_state_machine_dependancy` (`master_state_change_id` ,`slave_state_change_id` ,`trigger_verification`)
                    VALUES ( @admin_approve_flow_id, @completed_mainappraisal_flow_id, 'VerifyMainAppraisalCompletion'),
                     ( @main_eval_approve_flow_id, @completed_mainappraisal_flow_id, 'VerifyMainAppraisalCompletion'),
                     ( @final_review_admin_submit_approve_flow_id, @completed_mainappraisal_flow_id, 'VerifyMainAppraisalCompletion'),
                     ( @final_review_admin_approve_flow_id, @completed_mainappraisal_flow_id, 'VerifyMainAppraisalCompletion'),
                     ( @final_review_maineval_submit_approve_flow_id, @completed_mainappraisal_flow_id, 'VerifyMainAppraisalCompletion');
--                      ( @admin_mainappraisal_approve_flow_id, @completed_emp_appr_flow_id, 'VerifyEmployeeAppraisalCompletion'),
--                      ( @maineval_mainappraisal_approve_flow_id, @completed_emp_appr_flow_id, 'VerifyEmployeeAppraisalCompletion');

update hs_hr_unique_id set last_id  = @last_id+1 where table_name  ='ohrm_workflow_state_machine';    