-- 
ALTER TABLE `ohrm_menu_item` ADD `has_multiple_records` TINYINT( 4 ) NULL ;
--

ALTER TABLE `ohrm_custom_field_value` ADD `sequence_number` INT NOT NULL ;

-- 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 @pim_configuration_menu_id := (SELECT `id` FROM `ohrm_menu_item` WHERE `menu_title` = 'Configuration' AND `parent_id` = (SELECT id FROM `ohrm_menu_item` WHERE `menu_title` = 'PIM'));
INSERT INTO `ohrm_menu_item` (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) VALUES 
('PIM Tabs', @view_pim_tab_screen_id, @pim_configuration_menu_id, 3, 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', 'PIM - 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';
ALTER TABLE `ohrm_audittrail_section_actions` CHANGE `action` `action` VARCHAR( 767 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL ;

-- 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`='PIM');

-- add a new section for pim configure tabs
INSERT INTO `ohrm_audittrail_section` (`name`, `description`, `affected_entity`, `module_id`) VALUES
('PimTab', 'Configure - PIM Tab', 'Pim 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`,`permission`) 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 PIM TAB', 'Add PIM Tab'),
(@pim_tabs_audit_section_id, 'UPDATE PIM TAB', 'Update PIM Tab'),
(@pim_tabs_audit_section_id, 'DELETE PIM TAB', 'Delete PIM Tab');

CREATE TABLE `ohrm_audittrail_pim_tab_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 `ohrm_audittrail_pim_tab_custom_field_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,
  `pim_tab` VARCHAR(255), -- a new column to identify what is the tab
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP VIEW IF EXISTS `ohrm_audittrail_all_tables`;

CREATE VIEW `ohrm_audittrail_all_tables` AS 
(select 'Contact Information' as section, pci.* from `ohrm_audittrail_pim_contact_info_trail` pci) 
UNION (select 'Dependent' as section, pdt.* from `ohrm_audittrail_pim_dependents_trail` pdt) 
UNION (select 'Emergency Contacts' as section, ect.* from `ohrm_audittrail_pim_emergency_contacts_trail` ect) 
UNION (select 'Membership' as section, emd.* from `ohrm_audittrail_pim_emp_member_detail_trail` emd) 
UNION (select 'Job Details' as section, jtt.* from `ohrm_audittrail_pim_job_record_trail` jtt) 
UNION (select 'Immigration' as section, ppt.* from `ohrm_audittrail_pim_passport_trail` ppt) 
UNION (select 'Personal Details' as section, ppd.* from `ohrm_audittrail_pim_personal_details_trail` ppd) 
UNION (select 'Qualification' as section, pqt.* from `ohrm_audittrail_pim_qualification_trail` pqt) 
UNION (select 'Supervisor/Subordinate' as section, prt.* from `ohrm_audittrail_pim_reportto_trail` prt) 
UNION (select 'Tax Exemptions' as section, put.* from `ohrm_audittrail_pim_us_tax_trail` put)
UNION (select 'Employee' as section, pet.* from `ohrm_audittrail_pim_employee_trail` pet)
UNION (SELECT 'PIM Tab' AS SECTION, pt.* FROM `ohrm_audittrail_pim_tab_trail` pt)
UNION (SELECT 'Custom Field Section' AS SECTION, cfs.* FROM `ohrm_audittrail_pim_custom_field_section_trail` cfs)
UNION (SELECT 'Custom Field' AS SECTION, cf.* FROM `ohrm_audittrail_pim_custom_field_trail` cf)
UNION (SELECT 'Personal Details Custom Fields' AS SECTION, pdcf.* FROM ohrm_audittrail_pim_personal_custom_field_trail pdcf)
UNION (SELECT oas.description AS SECTION, ptcf.id, ptcf.action_time, ptcf.action_owner_id, ptcf.action_owner_name, ptcf.version_id, ptcf.action, ptcf.affected_entity_id, ptcf.affected_entity_name, ptcf.action_description FROM ohrm_audittrail_pim_tab_custom_field_trail ptcf, ohrm_audittrail_section oas WHERE oas.is_deleted = 0 AND ptcf.pim_tab = oas.name)
UNION (SELECT 'Contact Custom Fields' AS SECTION, ccf.* FROM ohrm_audittrail_pim_emergency_custom_field_trail ccf)
UNION (SELECT 'Emergency Custom Fields' AS SECTION, ecf.* FROM ohrm_audittrail_pim_contact_custom_field_trail ecf)
UNION (SELECT 'Dependent Custom Fields' AS SECTION, dcf.* FROM ohrm_audittrail_pim_dependents_custom_field_trail dcf)
UNION (SELECT 'Immigration Custom Fields' AS SECTION, icf.* FROM ohrm_audittrail_pim_immigration_custom_field_trail icf)
UNION (SELECT 'Job Custom Fields' AS SECTION, jcf.* FROM ohrm_audittrail_pim_job_custom_field_trail jcf)
UNION (SELECT 'Salary Custom Fields' AS SECTION, scf.* FROM ohrm_audittrail_pim_salary_custom_field_trail scf)
UNION (SELECT 'Supervisor/Subordinate Custom Fields' AS SECTION, sscf.* FROM ohrm_audittrail_pim_reportto_custom_field_trail sscf)
UNION (SELECT 'Qualifications Custom Fields' AS SECTION, qcf.* FROM ohrm_audittrail_pim_qualifications_custom_field_trail qcf)
UNION (SELECT 'Membership Custom Fields' AS SECTION, mcf.* FROM ohrm_audittrail_pim_membership_custom_field_trail mcf)
UNION (SELECT 'Tax Custom Fields' AS SECTION, tcf.* FROM ohrm_audittrail_pim_tax_custom_field_trail tcf);

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` VARCHAR(255), 
`required` TINYINT, 
PRIMARY KEY(id)) ENGINE = INNODB DEFAULT CHARSET=utf8;
