-- Pim -> Pim Tab
-- add screens
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('Pim Tabs', 3, 'viewPimTabList');
SET @view_pim_tab_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('Configure Pim Tabs', 3, 'savePimTab');
SET @save_pim_tab_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('Delete Pim Tabs', 3, 'deletePimTab');
SET @delete_pim_tab_screen_id := (SELECT LAST_INSERT_ID());

-- add menu
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);
SET @configurations_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'Configurations' AND parent_id = @employee_management_module_menu_id AND level = 3);

INSERT INTO `ohrm_menu_item` (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) VALUES
('Custom Tabs', @view_pim_tab_screen_id, @configurations_menu_id, 4, 250, NULL, 1);

SET @view_PIM_module_screen_id := (SELECT `id` FROM ohrm_screen WHERE `name` = 'View PIM Module');

SET @config_admin_role_id := (SELECT `id` FROM ohrm_user_role WHERE `name` = 'ConfigAdmin');

-- add ohrm_user_role_screen
INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete) VALUES
(@config_admin_role_id, @view_PIM_module_screen_id, 1, 1, 1, 1),
(@config_admin_role_id, @view_pim_tab_screen_id, 1, 1, 1, 1),
(@config_admin_role_id, @save_pim_tab_screen_id, 1, 1, 1, 1),
(@config_admin_role_id, @delete_pim_tab_screen_id, 1, 1, 1, 1);

-- add ohrm_data_group
INSERT INTO `ohrm_data_group` (`id`, `name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
(NULL, 'pim_configure_tabs', 'Employee Management - Configure - Tabs', 1, 1, 1, 1);
SET @pim_tab_data_group_id := (SELECT LAST_INSERT_ID());

-- add ohrm_user_role_data_group
INSERT INTO `ohrm_user_role_data_group` (`user_role_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`, `self`) VALUES
(@config_admin_role_id, @pim_tab_data_group_id, 1, 1, 1, 1, 0),
(1, @pim_tab_data_group_id, 0, 0, 0, 0, 0),
(2, @pim_tab_data_group_id, 0, 0, 0, 0, 0),
(3, @pim_tab_data_group_id, 0, 0, 0, 0, 0);

-- add ohrm_data_group_screen
INSERT INTO `ohrm_data_group_screen`(`data_group_id`, `screen_id`, `permission`, `self`) VALUES
(@pim_tab_data_group_id, @view_pim_tab_screen_id, 1, 0),
(@pim_tab_data_group_id, @save_pim_tab_screen_id, 1, 0),
(@pim_tab_data_group_id, @save_pim_tab_screen_id, 2, 0),
(@pim_tab_data_group_id, @save_pim_tab_screen_id, 3, 0),
(@pim_tab_data_group_id, @delete_pim_tab_screen_id, 4, 0);

-- view in UI
-- INSERT INTO ohrm_user_role_type_data_group(user_role_type_id, data_group_id) VALUES
-- (1, @pim_tab_data_group_id);
-- Pim -> Pim Tab Ends

-- Pim -> Employee -> View Employee Tab
-- add screens
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('Employee Pim Tabs', 3, 'viewEmployeePimTab');
SET @view_employee_pim_tab_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('Delete Employee Pim Tabs', 3, 'deleteEmployeePimTab');
SET @delete_employee_pim_tab_screen_id := (SELECT LAST_INSERT_ID());

-- add ohrm_default_permitted_screen
INSERT INTO `ohrm_default_permitted_screen`(`user_role_type_id`, `screen_id`) VALUES
(1,@view_employee_pim_tab_screen_id),
(1,@delete_employee_pim_tab_screen_id),
(2,@view_employee_pim_tab_screen_id),
(2,@delete_employee_pim_tab_screen_id),
(3,@view_employee_pim_tab_screen_id),
(3,@delete_employee_pim_tab_screen_id);

-- add ohrm_user_role_screen
INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete) VALUES
(1,@view_employee_pim_tab_screen_id,1,1,1,1),
(1,@delete_employee_pim_tab_screen_id,1,1,1,1),
(2,@view_employee_pim_tab_screen_id,1,1,1,1),
(2,@delete_employee_pim_tab_screen_id,1,1,1,1),
(3,@view_employee_pim_tab_screen_id,1,1,1,1),
(3,@delete_employee_pim_tab_screen_id,1,1,1,1);

-- Pim -> Employee -> View Employee Tab Ends


-- audit trail
INSERT INTO `ohrm_event_listener` (`id`, `event`, `listener_class`, `enabled`, `position`) VALUES
(NULL, 'pim.custom_field_tab_add', 'PIMCustomFieldTabAuditListener', '1', '27'),
(NULL, 'pim.custom_field_tab_update', 'PIMCustomFieldTabAuditListener', '1', '27'),
(NULL, 'pim.custom_field_tab_delete', 'PIMCustomFieldTabAuditListener', '1', '27');

ALTER TABLE `ohrm_audittrail_section` ADD `is_deleted` INT NOT NULL DEFAULT '0';

-- utf support
ALTER TABLE `ohrm_audittrail_section` CHANGE `description` `description` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ;
ALTER TABLE `ohrm_audittrail_section` CHANGE `name` `name` VARCHAR( 25 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ;
ALTER TABLE `ohrm_audittrail_section` CHANGE `affected_entity` `affected_entity` VARCHAR( 25 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ;
ALTER TABLE `ohrm_audittrail_section_actions` CHANGE `action` `action` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ;
ALTER TABLE `ohrm_audittrail_section_actions` CHANGE `description` `description` VARCHAR( 50 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ;

alter table ohrm_custom_field_screen drop index name;
ALTER TABLE `ohrm_custom_field_screen` CHANGE `name` `name` VARCHAR( 767 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ;

SET @pim_module_id := (SELECT `id` FROM `ohrm_audittrail_module` WHERE `name`='Employee Management');

-- add a new section for pim configure tabs
INSERT INTO `ohrm_audittrail_section` (`name`, `description`, `affected_entity`, `module_id`) VALUES
('CustomTab', 'Configure - Custom Tab', 'Custom Tab', @pim_module_id);

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

INSERT INTO `ohrm_datagroup_audittrail_section`(`audit_section_id`,`datagroup_id`,`consider_in_notification`) VALUES
(@pim_tabs_audit_section_id, @pim_tab_data_group_id, 1);

INSERT INTO `ohrm_audittrail_section_actions` (`section_id`, `action`, `description`) VALUES
(@pim_tabs_audit_section_id, 'ADD CUSTOM TAB', 'Add Custom Tab'),
(@pim_tabs_audit_section_id, 'UPDATE CUSTOM TAB', 'Update Custom Tab'),
(@pim_tabs_audit_section_id, 'DELETE CUSTOM TAB', 'Delete Custom Tab');

CREATE TABLE `ohrm_audittrail_custom_tab_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` VARCHAR(3000) CHARSET UTF8,
  `app_id` INT DEFAULT NULL,
  `app_name` VARCHAR(1000) DEFAULT NULL,
  `screen_name` VARCHAR(1000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX audit_custom_tab_trail_idx_app_id (app_id),
  INDEX audit_custom_tab_trail_idx_screen_name (screen_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `ohrm_audittrail_pim_tab_custom_field_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` TEXT CHARSET UTF8,
  `pim_tab` VARCHAR(255), -- a new column to identify what is the tab
  `app_id` INT DEFAULT NULL,
  `app_name` VARCHAR(1000) DEFAULT NULL,
  `screen_name` VARCHAR(1000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX audit_pim_tab_custom_field_trail_idx_app_id (app_id),
  INDEX audit_pim_tab_custom_field_trail_idx_screen_name (screen_name)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DELIMITER $$

ALTER TABLE `ohrm_custom_field`
ADD COLUMN `advance_type_map` INT NULL AFTER `global`;

CREATE TABLE `ohrm_custom_advance_type_map` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `entity` VARCHAR(255) NOT NULL,
  `display_name` VARCHAR(255) NOT NULL,
  `id_field` VARCHAR(255) NOT NULL,
  `name_field` VARCHAR(255) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO `ohrm_custom_advance_type_map`(`entity`,`display_name`,`id_field`,`name_field`) VALUES ('JobTitle','Job Title','id','jobTitleName');
INSERT INTO `ohrm_custom_advance_type_map`(`entity`,`display_name`,`id_field`,`name_field`) VALUES ('Subunit','Sub Unit','id','name');

CREATE TRIGGER ohrm_custom_field_display_order
BEFORE INSERT
ON ohrm_custom_field FOR EACH ROW
BEGIN
	SET @row_count:=(SELECT COUNT(id) FROM ohrm_custom_field);
	SET NEW.display_order=@row_count+1;
END;

CREATE TABLE `ohrm_custom_field_display_condition` (
`id` INT AUTO_INCREMENT,
`customfieldid` INT,
`parentcustomfieldId` INT,
`parentcustomfieldvalue` text,
`required` TINYINT,
PRIMARY KEY(id)) ENGINE = INNODB DEFAULT CHARSET=utf8;

UPDATE `ohrm_menu_item` SET `uisref` = 'pim.customizable_pim_tabs' WHERE `ohrm_menu_item`.`menu_title` ='Custom Tabs' AND `ohrm_menu_item`.`level` = 4;
