DROP TABLE IF EXISTS `ohrm_audittrail_module`;
DROP TABLE IF EXISTS `ohrm_audittrail_section`;
DROP TABLE IF EXISTS `ohrm_audittrail_section_actions`;
DROP TABLE IF EXISTS `ohrm_audittrail_static_reference`;

CREATE TABLE `ohrm_audittrail_module` (
  `id` SMALLINT NOT NULL,
  `name` VARCHAR(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_UNIQUE` (`name`)
) ENGINE=InnoDB;

CREATE TABLE `ohrm_audittrail_section` (
    `id` SMALLINT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(25) NOT NULL UNIQUE,
    `description` VARCHAR(255) NOT NULL,
    `affected_entity` VARCHAR(25) NOT NULL,
    `module_id` SMALLINT REFERENCES `ohrm_audittrail_module` (`id`)
) Engine=InnoDB;

CREATE TABLE `ohrm_audittrail_section_actions` (
    `section_id` SMALLINT REFERENCES `ohrm_audittrail_section` (`id`),
    `action` VARCHAR(255) NOT NULL UNIQUE,
    `description` VARCHAR(50) NOT NULL,
    PRIMARY KEY (`section_id`, `action`)
) Engine=InnoDB;

CREATE TABLE `ohrm_audittrail_static_reference` (
  `table` VARCHAR(50),
  `field` VARCHAR(50),
  `key_value` VARCHAR(10),
  `reference_value` VARCHAR(200),
  PRIMARY KEY (`table`, `field`, `key_value`)
) Engine=InnoDB;

DROP TABLE IF EXISTS `ohrm_audittrail_pim_job_title_trail`;
CREATE TABLE `ohrm_audittrail_pim_job_title_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(11) NOT NULL DEFAULT '0',
  `action` varchar(255) NOT NULL,
  `affected_entity_id` varchar(20) NOT NULL DEFAULT '',
  `affected_entity_name` VARCHAR(300) NOT NULL DEFAULT '',
  `action_description` VARCHAR(3000) CHARSET UTF8,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

DROP TABLE IF EXISTS `ohrm_audittrail_pim_passport_trail`;
CREATE TABLE `ohrm_audittrail_pim_passport_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;

DROP TABLE IF EXISTS `ohrm_audittrail_pim_emp_directdebit_trail`;
CREATE TABLE `ohrm_audittrail_pim_emp_directdebit_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;

DROP TABLE IF EXISTS `ohrm_audittrail_pim_salary_trail`;
CREATE TABLE `ohrm_audittrail_pim_salary_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;

DROP TABLE IF EXISTS `ohrm_audittrail_pim_reportto_trail`;
CREATE TABLE `ohrm_audittrail_pim_reportto_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) DEFAULT '',
  `action_description` VARCHAR(3000) CHARSET UTF8,
  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `ohrm_audittrail_pim_qualification_trail`;
CREATE TABLE `ohrm_audittrail_pim_qualification_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;

DROP TABLE IF EXISTS `ohrm_audittrail_recruitment_job_vacancy_trail`;
CREATE TABLE `ohrm_audittrail_recruitment_job_vacancy_trail` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `action_time` TIMESTAMP 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` VARCHAR(300),
  `action_description` TEXT CHARSET UTF8,
  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `ohrm_audittrail_reference_archive`;
CREATE TABLE `ohrm_audittrail_reference_archive` (

  `table_name` VARCHAR(50) CHARSET UTF8,
  `reference_key` VARCHAR(10) CHARSET UTF8,
  `record_descriptor` VARCHAR(200) CHARSET UTF8,
  PRIMARY KEY (`table_name`, `reference_key`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `ohrm_audittrail_pim_emergency_contacts_trail`;
CREATE TABLE `ohrm_audittrail_pim_emergency_contacts_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;

DROP TABLE IF EXISTS `ohrm_audittrail_pim_dependents_trail`;
CREATE TABLE `ohrm_audittrail_pim_dependents_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;

DROP TABLE IF EXISTS `ohrm_audittrail_pim_personal_details_trail`;
CREATE TABLE `ohrm_audittrail_pim_personal_details_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),
  PRIMARY KEY(`id`)  

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `ohrm_audittrail_pim_contact_info_trail`;
CREATE TABLE `ohrm_audittrail_pim_contact_info_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;

DROP TABLE IF EXISTS `ohrm_audittrail_pim_employee_trail`;
CREATE TABLE `ohrm_audittrail_pim_employee_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;

DROP TABLE IF EXISTS `ohrm_audittrail_pim_emp_member_detail_trail`;
CREATE TABLE `ohrm_audittrail_pim_emp_member_detail_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;

DROP TABLE IF EXISTS `ohrm_audittrail_pim_us_tax_trail`;
CREATE TABLE `ohrm_audittrail_pim_us_tax_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;

INSERT INTO `ohrm_audittrail_module`
(`id`, `name`) VALUES
(1, 'PIM'),
(2, 'Recruitment');

INSERT INTO `ohrm_audittrail_section` (`id`, `name`, `description`, `affected_entity`, `module_id`) VALUES
(1, 'PersonalDetails', 'Personal Details', 'Employee', 1),
(2, 'JobTitle', 'Job Details', 'Employee', 1),
(3, 'Salary', 'Salary', 'Employee', 1),
(4, 'ContactInformation', 'Contact Information', 'Employee', 1),
(5, 'ReportTo', 'Supervisor/Subordinate', 'Subordinate', 1),
(6, 'Qualification', 'Qualification', 'Employee', 1),
(7, 'JobVacancy', 'Job Vacancy', 'Job Vacancy', 2),
(8, 'EmergencyContact', 'Emergency Contacts', 'Employee', 1),
(9, 'EmpDependent', 'Dependent', 'Employee', 1),
(10, 'EmpImmigration', 'Immigration', 'Employee', 1),
(12, 'EmpMembership', 'Membership', 'Employee', 1),
(13, 'EmpTaxExemption', 'Tax Exemptions', 'Employee', 1),
(14, 'Employee', 'Employee', 'Employee', 1),
(15, 'All', 'All', 'Employee', 1);

INSERT INTO `ohrm_audittrail_section_actions` (`section_id`, `action`, `description`) VALUES
(1, 'UPDATE PERSONAL DETAILS', 'Update Personal Details'),
(2, 'UPDATE JOB DETAILS', 'Update Job Details'),
(3, 'ADD DIRECT DEPOSIT DETAILS', 'Add Direct Deposit Details'),
(3, 'ADD SALARY DETAILS', 'Add Salary Details'),
(3, 'UPDATE SALARY DETAILS', 'Update Salary Details'),
(3, 'DELETE SALARY DETAILS', 'Delete Salary Details'),
(3, 'DELETE DIRECT DEPOSIT DETAILS', 'Delete Direct Deposit Details'),
(3, 'UPDATE DIRECT DEPOSIT DETAILS', 'Update Direct Deposit Details'),
(4, 'UPDATE CONTACT DETAILS', 'Update Contact Details'),
(5, 'ADD SUPERVISOR', 'Add Supervisor/Subordinate Details'),
(5, 'DELETE SUPERVISOR', 'Delete Supervisor/Subordinate Details'),
(5, 'UPDATE REPORT TO DETAILS', 'Update Supervisor/Subordinate Details'),
(6, 'ADD LANGUAGE DETAILS', 'Add Language Details'),
(6, 'ADD LICENSE DETAILS', 'Add License Details'),
(6, 'ADD SKILL DETAILS', 'Add Skill Details'),
(6, 'ADD WORK EXPERIENCE DETAILS', 'Add Work Experience Details'),
(6, 'DELETE LANGUAGE DETAILS', 'Delete Language Details'),
(6, 'DELETE LICENSE DETAILS', 'Delete License Details'),
(6, 'DELETE SKILL DETAILS', 'Delete Skill Details'),
(6, 'DELETE WORK EXPERIENCE DETAILS', 'Delete Work Experience Details'),
(6, 'UPDATE LANGUAGE DETAILS', 'Update Language Details'),
(6, 'UPDATE LICENSE DETAILS', 'Update License Details'),
(6, 'UPDATE SKILL DETAILS', 'Update Skill Details'),
(6, 'UPDATE WORK EXPERIENCE DETAILS', 'Update Work Experience Details'),
(8, 'ADD EMERGENCY CONTACT DETAILS', 'Add Emergency Contact Details'),
(8, 'DELETE EMERGENCY CONTACT DETAILS', 'Delete Emergency Contact Details'),
(8, 'UPDATE EMERGENCY CONTACT DETAILS', 'Update Emergency Contact Details'),
(9, 'ADD DEPENDENT DETAILS', 'Add Dependent Details'),
(9, 'DELETE DEPENDENT DETAILS', 'Delete Dependent Details'),
(9, 'UPDATE DEPENDENT DETAILS', 'Update Dependent Details'),
(10, 'ADD IMMIGRATION DETAILS', 'Add Immigration Details'),
(10, 'DELETE IMMIGRATION DETAILS', 'Delete Immigration Details'),
(10, 'UPDATE IMMIGRATION DETAILS', 'Update Immigration Details'),
(12, 'ADD MEMBERSHIP DETAILS', 'Add Membership Details'),
(12, 'DELETE MEMBERSHIP DETAILS', 'Delete Membership Details'),
(12, 'UPDATE MEMBERSHIP DETAILS', 'Update Membership Details'),
(13, 'ADD TAX EXEMPTION DETAILS', 'Add Tax Exemption Details'),
(13, 'UPDATE TAX EXEMPTION DETAILS', 'Update Tax Exemption Details'),
(14, 'ADD EMPLOYEE DETAILS', 'Add Employee Details'),
(14, 'DELETE EMPLOYEE DETAILS', 'Delete Employee Details'),
(14, 'UPDATE TERMINATION DETAILS', 'Terminate Employee Details'),
(6, 'ADD EDUCATION QUALIFICATION DETAILS', 'Add Education Qualification Details'),
(6, 'DELETE EDUCATION QUALIFICATION DETAILS', 'Delete Education Qualification Details'),
(6, 'UPDATE EDUCATION QUALIFICATION DETAILS', 'Update Education Qualification Details');

INSERT INTO `ohrm_audittrail_static_reference` (`table`, `field`, `key_value`, `reference_value`) VALUES
('hs_hr_employee', 'emp_gender', '1', 'Male'),
('hs_hr_employee', 'emp_gender', '2', 'Female'),
('hs_hr_emp_basicsalary', 'payperiod_code', '1', 'Weekly'),
('hs_hr_emp_basicsalary', 'payperiod_code', '2', 'Bi Weekly'),
('hs_hr_emp_basicsalary', 'payperiod_code', '3', 'Semi Monthly'),
('hs_hr_emp_basicsalary', 'payperiod_code', '4', 'Monthly'),
('hs_hr_emp_basicsalary', 'payperiod_code', '5', 'Monthly on first pay of month'),
('hs_hr_emp_basicsalary', 'payperiod_code', '6', 'Hourly'),
('hs_hr_emp_language', 'competency', '1', 'Poor'),
('hs_hr_emp_language', 'competency', '2', 'Basic'),
('hs_hr_emp_language', 'competency', '3', 'Good'),
('hs_hr_emp_language', 'competency', '4', 'Mother Tongue'),
('hs_hr_emp_language', 'fluency', '1', 'Writing'),
('hs_hr_emp_language', 'fluency', '2', 'Speaking'),
('hs_hr_emp_language', 'fluency', '3', 'Reading'),
('hs_hr_emp_passport', 'document_type', '1', 'Passport'),
('hs_hr_emp_passport', 'document_type', '2', 'Visa'),
('ohrm_job_vacancy', 'published_in_feed', '0', 'Inactive'),
('ohrm_job_vacancy', 'published_in_feed', '1', 'Active'),
('ohrm_job_vacancy', 'status', '1', 'Active'),
('ohrm_job_vacancy', 'status', '2', 'Inactive'),
('hs_hr_emp_us_tax', 'tax_status', 'S', 'Single'),
('hs_hr_emp_us_tax', 'tax_status', 'M', 'Married'),
('hs_hr_emp_us_tax', 'tax_status', 'NRA', 'Non Resident Alien'),
('hs_hr_emp_us_tax', 'tax_status', 'NA', 'Not Applicable'),
('hs_hr_emp_us_tax', 'tax_status', '0', '');

SET @admin_role_id := (SELECT `id` FROM ohrm_user_role WHERE `name` = 'Admin');

INSERT INTO `ohrm_module` (`name`, `status`) VALUES ('audittrail', 1);

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

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('Audit Trail', @module_id, 'viewAuditTrail');

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

SET @admin_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'Admin' AND `level` = 1); 

INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) VALUES
('Audit Trail', @audittrail_screen_id, @admin_menu_id, 2, 1100, '', 1);

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