CREATE TABLE `ohrm_performance_tracker_rating_scale` (
    `id` INT(4) NOT NULL,
    `rating` INT(11) NOT NULL,
    `label` VARCHAR(255) NOT NULL,
    `colour` VARCHAR(255) NOT NULL,
    `display_order` INT(4) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `ohrm_performance_track` ( 
  `id` int(11) NOT NULL AUTO_INCREMENT, 
  `emp_number` int(7) NOT NULL, 
  `tracker_name` varchar(200) NOT NULL,
  `added_date` timestamp NULL DEFAULT NULL, 
  `added_by` int(11) DEFAULT NULL, 
  `status` int(11) DEFAULT NULL, 
  `modified_date` timestamp NULL DEFAULT NULL, 
  PRIMARY KEY (`id`), 
  KEY `ohrm_performance_track_fk1_idx` (`emp_number`), 
  KEY `ohrm_performance_track_fk2_idx` (`added_by`),
  CONSTRAINT `ohrm_performance_track_fk1` FOREIGN KEY (`emp_number`) REFERENCES `hs_hr_employee` (`emp_number`) ON DELETE CASCADE, 
  CONSTRAINT `ohrm_performance_track_fk2` FOREIGN KEY (`added_by`) REFERENCES `hs_hr_employee` (`emp_number`) ON DELETE SET NULL 
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;


CREATE TABLE `ohrm_performance_tracker_log` ( 
  `id` int(11) NOT NULL AUTO_INCREMENT, 
  `performance_track_id` int(11) DEFAULT NULL, 
  `log` varchar(150) DEFAULT NULL, 
  `comment` varchar(3000) DEFAULT NULL, 
  `status` int(11) DEFAULT NULL, 
  `added_date` timestamp NULL DEFAULT NULL, 
  `modified_date` timestamp NULL DEFAULT NULL, 
  `reviewer_id` int(7) DEFAULT NULL, 
  `rating_id` int(4) DEFAULT NULL,
  `user_id` int(10) DEFAULT NULL, 
  PRIMARY KEY (`id`), 
  KEY `ohrm_performance_tracker_log_fk1_idx` (`performance_track_id`), 
  KEY `ohrm_performance_tracker_log_fk2_idx` (`reviewer_id`), 
  KEY `fk_ohrm_performance_tracker_log_1` (`user_id`), 
  CONSTRAINT `fk_ohrm_performance_tracker_log_1` FOREIGN KEY (`user_id`) REFERENCES `ohrm_user` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION, 
  CONSTRAINT `ohrm_performance_tracker_log_fk1` FOREIGN KEY (`performance_track_id`) REFERENCES `ohrm_performance_track` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, 
  CONSTRAINT `ohrm_performance_tracker_log_fk2` FOREIGN KEY (`reviewer_id`) REFERENCES `hs_hr_employee` (`emp_number`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `ohrm_performance_tracker_log_rating_id` FOREIGN KEY (`rating_id`) REFERENCES `ohrm_performance_tracker_rating_scale` (`id`) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `ohrm_performance_tracker_reviewer` ( 
  `id` int(11) NOT NULL AUTO_INCREMENT, 
  `performance_track_id` int(11) NOT NULL, 
  `reviewer_id` int(7) NOT NULL, 
  `added_date` timestamp NULL DEFAULT NULL, 
  `status` int(2) DEFAULT NULL, 
  PRIMARY KEY (`id`), 
  KEY `ohrm_performance_tracker_reviewer_fk1_idx` (`performance_track_id`), 
  KEY `ohrm_performance_tracker_reviewer_fk2_idx` (`reviewer_id`), 
  CONSTRAINT `ohrm_performance_tracker_reviewer_fk1` FOREIGN KEY (`performance_track_id`) REFERENCES `ohrm_performance_track` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, 
  CONSTRAINT `ohrm_performance_tracker_reviewer_fk2` FOREIGN KEY (`reviewer_id`) REFERENCES `hs_hr_employee` (`emp_number`) ON DELETE CASCADE ON UPDATE NO ACTION 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `ohrm_module`( `name`, `status`) VALUES
('performanceTracker', 1);
SET @performance_module_id:= (SELECT LAST_INSERT_ID());

-- Admin Section. Manage Trackers.
INSERT INTO `ohrm_screen` (`name`, `module_id`, `action_url`, `route_name`) VALUES
( 'Manage_Trackers', @performance_module_id, 'addPerformanceTracker', 'performancetracker_addPerformanceTracker');
SET @manage_performance_trackers_screen_id :=  (SELECT LAST_INSERT_ID());

SET @culture_pillar_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'Culture' AND parent_id IS NULL AND level = 1);
SET @performance_menu_id := (SELECT id FROM ohrm_menu_item WHERE menu_title = 'Performance' AND level = 2 AND parent_id = @culture_pillar_menu_id);


INSERT INTO `ohrm_user_role`(`name`, `display_name`, `is_assignable`, `is_predefined`) VALUES ('PerformanceTrackerReviewer','Performance Tracker Reviewer',0,1);
SET @performance_tracker_reviewer_role_id := (SELECT `id` FROM ohrm_user_role WHERE `name` = 'PerformanceTrackerReviewer');

-- Admin/ ESS Section Employee Trackers.
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`, `route_name`) VALUES
( 'Employee_Trackers', @performance_module_id, 'viewEmployeePerformanceTrackerList', 'performancetracker_viewEmployeePerformanceTrackerList');
SET @employee_trackers_screen_id :=  (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) VALUES
('Employee Trackers', NULL, @performance_menu_id, 3, 500, NULL, 1);

SET @employee_tracker_manu_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
(1, @employee_trackers_screen_id, 1, 1, 1, 1),
(@performance_tracker_reviewer_role_id, @employee_trackers_screen_id, 1, 1, 1, 0);

INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) VALUES
('Tracker List', @employee_trackers_screen_id, @employee_tracker_manu_id, 4, 600, NULL, 1);


-- ESS secetion. My Trackers.
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`, `route_name`) VALUES
( 'My_Trackers', @performance_module_id, 'viewMyPerformanceTrackerList', 'performancetracker_viewMyPerformanceTrackerList');
SET @my_trackers_screen_id :=  (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) VALUES
('My Trackers', @my_trackers_screen_id, @employee_tracker_manu_id, 4, 700, NULL, 1);

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

INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) VALUES
('Manage Trackers', @manage_performance_trackers_screen_id, @employee_tracker_manu_id, 4, 800, NULL, 1);

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

-- Tracker Logs. (No menu item)
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
( 'Employee_Tracker_Logs', @performance_module_id, 'addPerformanceTrackerLog');
SET @employee_tracker_logs_screen_id :=  (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_default_permitted_screen`(`user_role_type_id`, `screen_id`) VALUES
(2, @my_trackers_screen_id),
(2, @employee_tracker_logs_screen_id);

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

INSERT INTO `ohrm_performance_tracker_rating_scale` (`id`, `rating`, `label`, `colour`, `display_order`) VALUES
('1', '1', 'Positive', '#9cd318', '1'),
('2', '-1', 'Negative', '#f21363', '2');
