
CREATE TABLE `ohrm_audittrail_module` (
  `id` SMALLINT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(30) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_UNIQUE` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE `ohrm_audittrail_section` (
    `id` SMALLINT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL UNIQUE,
    `description` VARCHAR(255) NOT NULL,
    `affected_entity` VARCHAR(25) NOT NULL,
    `module_id` SMALLINT,
    CONSTRAINT `fk_ohrm_audittrail_section_module_id` FOREIGN KEY (`module_id`)
        REFERENCES `ohrm_audittrail_module`(`id`) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `ohrm_audittrail_section_actions` (
    `section_id` SMALLINT NOT NULL,
    `action` VARCHAR(255) NOT NULL,
    `description` VARCHAR(50) NOT NULL,
    PRIMARY KEY (`section_id`, `action`),
    CONSTRAINT `ohrm_audittrail_section_actions_section_id` FOREIGN KEY (`section_id`)
      REFERENCES `ohrm_audittrail_section`(`id`) ON DELETE CASCADE
) Engine=InnoDB DEFAULT CHARSET=UTF8;

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 DEFAULT CHARSET=UTF8;

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 ;

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,
  `app_id` INT DEFAULT NULL,
  `app_name` VARCHAR(1000) DEFAULT NULL,
  `screen_name` VARCHAR(1000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX audit_pim_passport_trail_idx_app_id (app_id),
  INDEX audit_pim_passport_trail_idx_screen_name (screen_name)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;


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,
  `app_id` INT DEFAULT NULL,
  `app_name` VARCHAR(1000) DEFAULT NULL,
  `screen_name` VARCHAR(1000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX audit_pim_reportto_trail_idx_app_id (app_id),
  INDEX audit_pim_reportto_trail_idx_screen_name (screen_name)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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,
  `app_id` INT DEFAULT NULL,
  `app_name` VARCHAR(1000) DEFAULT NULL,
  `screen_name` VARCHAR(1000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX audit_pim_qualification_trail_idx_app_id (app_id),
  INDEX audit_pim_qualification_trail_idx_screen_name (screen_name)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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,
  `app_id` INT DEFAULT NULL,
  `app_name` VARCHAR(1000) DEFAULT NULL,
  `screen_name` VARCHAR(1000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX audit_recruitment_job_vacancy_trail_idx_app_id (app_id),
  INDEX audit_recruitment_job_vacancy_trail_idx_screen_name (screen_name)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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;

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,
  `app_id` INT DEFAULT NULL,
  `app_name` VARCHAR(1000) DEFAULT NULL,
  `screen_name` VARCHAR(1000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX audit_pim_emergency_contacts_trail_idx_app_id (app_id),
  INDEX audit_pim_emergency_contacts_trail_idx_screen_name (screen_name)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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,
  `app_id` INT DEFAULT NULL,
  `app_name` VARCHAR(1000) DEFAULT NULL,
  `screen_name` VARCHAR(1000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX audit_pim_dependents_trail_idx_app_id (app_id),
  INDEX audit_pim_dependents_trail_idx_screen_name (screen_name)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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),
  `app_id` INT DEFAULT NULL,
  `app_name` VARCHAR(1000) DEFAULT NULL,
  `screen_name` VARCHAR(1000) DEFAULT NULL,
  PRIMARY KEY(`id`),
  INDEX audit_pim_personal_details_trail_idx_app_id (app_id),
  INDEX audit_pim_personal_details_trail_idx_screen_name (screen_name)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `ohrm_audittrail_pim_profile_picture_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` TEXT CHARSET UTF8,
  `app_id` INT DEFAULT NULL,
  `app_name` VARCHAR(1000) DEFAULT NULL,
  `screen_name` VARCHAR(1000) DEFAULT NULL,
  PRIMARY KEY(`id`),
  INDEX audit_pim_profile_picture_trail_idx_app_id (app_id),
  INDEX audit_pim_profile_picture_trail_idx_screen_name (screen_name)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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,
  `app_id` INT DEFAULT NULL,
  `app_name` VARCHAR(1000) DEFAULT NULL,
  `screen_name` VARCHAR(1000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX audit_pim_contact_info_trail_idx_app_id (app_id),
  INDEX audit_pim_contact_info_trail_idx_screen_name (screen_name)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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,
  `app_id` INT DEFAULT NULL,
  `app_name` VARCHAR(1000) DEFAULT NULL,
  `screen_name` VARCHAR(1000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX audit_pim_employee_trail_idx_app_id (app_id),
  INDEX audit_pim_employee_trail_idx_screen_name (screen_name)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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,
  `app_id` INT DEFAULT NULL,
  `app_name` VARCHAR(1000) DEFAULT NULL,
  `screen_name` VARCHAR(1000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX audit_pim_emp_member_detail_trail_idx_app_id (app_id),
  INDEX audit_pim_emp_member_detail_trail_idx_screen_name (screen_name)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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,
  `app_id` INT DEFAULT NULL,
  `app_name` VARCHAR(1000) DEFAULT NULL,
  `screen_name` VARCHAR(1000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX audit_pim_us_tax_trail_idx_app_id (app_id),
  INDEX audit_pim_us_tax_trail_idx_screen_name (screen_name)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `ohrm_audittrail_pim_custom_field_section_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,
  `app_id` INT DEFAULT NULL,
  `app_name` VARCHAR(1000) DEFAULT NULL,
  `screen_name` VARCHAR(1000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX audit_pim_custom_field_section_trail_idx_app_id (app_id),
  INDEX audit_pim_custom_field_section_trail_idx_screen_name (screen_name)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `ohrm_audittrail_pim_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,
  `app_id` INT DEFAULT NULL,
  `app_name` VARCHAR(1000) DEFAULT NULL,
  `screen_name` VARCHAR(1000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX audit_pim_custom_field_trail_idx_app_id (app_id),
  INDEX audit_pim_custom_field_trail_idx_screen_name (screen_name)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `ohrm_audittrail_pim_personal_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,
  `app_id` INT DEFAULT NULL,
  `app_name` VARCHAR(1000) DEFAULT NULL,
  `screen_name` VARCHAR(1000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX audit_pim_personal_custom_field_trail_idx_app_id (app_id),
  INDEX audit_pim_personal_custom_field_trail_idx_screen_name (screen_name)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `ohrm_audittrail_pim_contact_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,
  `app_id` INT DEFAULT NULL,
  `app_name` VARCHAR(1000) DEFAULT NULL,
  `screen_name` VARCHAR(1000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX audit_pim_contact_custom_field_trail_idx_app_id (app_id),
  INDEX audit_pim_contact_custom_field_trail_idx_screen_name (screen_name)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `ohrm_audittrail_pim_emergency_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,
  `app_id` INT DEFAULT NULL,
  `app_name` VARCHAR(1000) DEFAULT NULL,
  `screen_name` VARCHAR(1000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX audit_pim_emergency_custom_field_trail_idx_app_id (app_id),
  INDEX audit_pim_emergency_custom_field_trail_idx_screen_name (screen_name)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `ohrm_audittrail_pim_dependents_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,
  `app_id` INT DEFAULT NULL,
  `app_name` VARCHAR(1000) DEFAULT NULL,
  `screen_name` VARCHAR(1000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX audit_pim_dep_custom_field_trail_idx_app_id (app_id),
  INDEX audit_pim_dep_custom_field_trail_idx_screen_name (screen_name)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `ohrm_audittrail_pim_immigration_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,
  `app_id` INT DEFAULT NULL,
  `app_name` VARCHAR(1000) DEFAULT NULL,
  `screen_name` VARCHAR(1000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX audit_pim_immigration_custom_field_trail_idx_app_id (app_id),
  INDEX audit_pim_immigration_custom_field_trail_idx_screen_name (screen_name)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `ohrm_audittrail_pim_job_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,
  `app_id` INT DEFAULT NULL,
  `app_name` VARCHAR(1000) DEFAULT NULL,
  `screen_name` VARCHAR(1000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX audit_pim_job_custom_field_trail_idx_app_id (app_id),
  INDEX audit_pim_job_custom_field_trail_idx_screen_name (screen_name)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `ohrm_audittrail_pim_salary_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,
  `app_id` INT DEFAULT NULL,
  `app_name` VARCHAR(1000) DEFAULT NULL,
  `screen_name` VARCHAR(1000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX audit_pim_salary_custom_field_trail_idx_app_id (app_id),
  INDEX audit_pim_salary_custom_field_trail_idx_screen_name (screen_name)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `ohrm_audittrail_pim_reportto_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,
  `app_id` INT DEFAULT NULL,
  `app_name` VARCHAR(1000) DEFAULT NULL,
  `screen_name` VARCHAR(1000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX audit_pim_reportto_custom_field_trail_idx_app_id (app_id),
  INDEX audit_pim_reportto_custom_field_trail_idx_screen_name (screen_name)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `ohrm_audittrail_pim_qualifications_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,
  `app_id` INT DEFAULT NULL,
  `app_name` VARCHAR(1000) DEFAULT NULL,
  `screen_name` VARCHAR(1000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX audit_pim_qualifications_custom_field_trail_idx_app_id (app_id),
  INDEX audit_pim_qualifications_custom_field_trail_idx_screen_name (screen_name)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `ohrm_audittrail_pim_membership_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,
  `app_id` INT DEFAULT NULL,
  `app_name` VARCHAR(1000) DEFAULT NULL,
  `screen_name` VARCHAR(1000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX audit_pim_membership_custom_field_trail_idx_app_id (app_id),
  INDEX audit_pim_membership_custom_field_trail_idx_screen_name (screen_name)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `ohrm_audittrail_pim_tax_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,
  `app_id` INT DEFAULT NULL,
  `app_name` VARCHAR(1000) DEFAULT NULL,
  `screen_name` VARCHAR(1000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX audit_pim_tax_custom_field_trail_idx_app_id (app_id),
  INDEX audit_pim_tax_custom_field_trail_idx_screen_name (screen_name)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `ohrm_datagroup_audittrail_section` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `audit_section_id` SMALLINT NOT NULL,
  `datagroup_id` INT NOT NULL,
  `permission` SMALLINT NOT NULL,  
  PRIMARY KEY (`id`),
  FOREIGN KEY(`audit_section_id`) REFERENCES ohrm_audittrail_section(`id`) ON DELETE CASCADE,
  FOREIGN KEY(`datagroup_id`) REFERENCES `ohrm_data_group`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

INSERT INTO `ohrm_audittrail_section` (`name`, `description`, `affected_entity`, `module_id`) VALUES
('PersonalDetails', 'Personal Details', 'Employee', @pim_module_id),
('JobTitle', 'Job Details', 'Employee', @pim_module_id),
('Salary', 'Salary', 'Employee', @pim_module_id),
('ContactInformation', 'Contact Information', 'Employee', @pim_module_id),
('ReportTo', 'Supervisor/Subordinate', 'Subordinate', @pim_module_id),
('Qualification', 'Qualification', 'Employee', @pim_module_id),
('EmergencyContact', 'Emergency Contacts', 'Employee', @pim_module_id),
('EmpDependent', 'Dependent', 'Employee', @pim_module_id),
('EmpImmigration', 'Immigration', 'Employee', @pim_module_id),
('EmpMembership', 'Membership', 'Employee', @pim_module_id),
('EmpTaxExemption', 'Tax Exemptions', 'Employee', @pim_module_id),
('Employee', 'Employee', 'Employee', @pim_module_id),
('CustomFieldSection', 'Configure - Custom Field Section', 'Custom Field Section', @pim_module_id),
('CustomField', 'Configure - Custom Field', 'Custom Field', @pim_module_id),
('PersonalCustomField', 'Personal Details Custom Fields', 'Employee', @pim_module_id),
('ContactCustomField', 'Contact Custom Fields', 'Employee', @pim_module_id),
('EmergencyCustomField', 'Emergency Custom Fields', 'Employee', @pim_module_id),
('DependentsCustomField', 'Dependent Custom Fields', 'Employee', @pim_module_id),
('ImmigrationCustomField', 'Immigration Custom Fields', 'Employee', @pim_module_id),
('JobCustomField', 'Job Custom Fields', 'Employee', @pim_module_id),
('SalaryCustomField', 'Salary Custom Fields', 'Employee', @pim_module_id),
('ReportToCustomField', 'Supervisor/Subordinate Custom Fields', 'Employee', @pim_module_id),
('QualificationsCustomField', 'Qualifications Custom Fields', 'Employee', @pim_module_id),
('MembershipCustomField', 'Membership Custom Fields', 'Employee', @pim_module_id),
('TaxCustomField', 'Tax Custom Fields', 'Employee', @pim_module_id),
('ProfilePicture', 'Profile', 'Employee', @pim_module_id),
('All', 'All Employee Data', 'Employee', @pim_module_id);

SET @personal_details_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='PersonalDetails' AND `module_id` = @pim_module_id);
SET @profile_picture_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='ProfilePicture' AND `module_id` = @pim_module_id);
SET @job_title_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='JobTitle' AND `module_id` = @pim_module_id);
SET @salary_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='Salary' AND `module_id` = @pim_module_id);

SET @contact_information_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='ContactInformation' AND `module_id` = @pim_module_id);
SET @report_to_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='ReportTo' AND `module_id` = @pim_module_id);
SET @qualification_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='Qualification' AND `module_id` = @pim_module_id);
SET @emergency_contact_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='EmergencyContact' AND `module_id` = @pim_module_id);
SET @emp_dependent_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='EmpDependent' AND `module_id` = @pim_module_id);
SET @emp_immigration_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='EmpImmigration' AND `module_id` = @pim_module_id);
SET @emp_membership_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='EmpMembership' AND `module_id` = @pim_module_id);
SET @emp_tax_exemption_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='EmpTaxExemption' AND `module_id` = @pim_module_id);
SET @employee_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='Employee' AND `module_id` = @pim_module_id);
SET @custom_field_section_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='CustomFieldSection' AND `module_id` = @pim_module_id);
SET @custom_field_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='CustomField' AND `module_id` = @pim_module_id);
SET @personal_custom_field_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name` = 'PersonalCustomField' AND `module_id` = @pim_module_id);
SET @contact_custom_field_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name` = 'ContactCustomField' AND `module_id` = @pim_module_id);
SET @emergency_custom_field_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name` = 'EmergencyCustomField' AND `module_id` = @pim_module_id);
SET @dependents_custom_field_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name` = 'DependentsCustomField' AND `module_id` = @pim_module_id);
SET @immigration_custom_field_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name` = 'ImmigrationCustomField' AND `module_id` = @pim_module_id);
SET @job_custom_field_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name` = 'JobCustomField' AND `module_id` = @pim_module_id);
SET @salary_custom_field_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name` = 'SalaryCustomField' AND `module_id` = @pim_module_id);
SET @reportTo_custom_field_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name` = 'ReportToCustomField' AND `module_id` = @pim_module_id);
SET @qualifications_custom_field_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name` = 'QualificationsCustomField' AND `module_id` = @pim_module_id);
SET @membership_custom_field_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name` = 'MembershipCustomField' AND `module_id` = @pim_module_id);
SET @tax_custom_field_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name` = 'TaxCustomField' AND `module_id` = @pim_module_id);
SET @all_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='All' AND `module_id` = @pim_module_id);

SET @persoanl_details_datagroup_id := (SELECT `id` FROM `ohrm_data_group` WHERE `name`='personal_information');
SET @contact_info_datagroup_id := (SELECT `id` FROM `ohrm_data_group` WHERE `name`='contact_details');
SET @supervisor_datagroup_id := (SELECT `id` FROM `ohrm_data_group` WHERE `name`='supervisor');
SET @subordinate_datagroup_id := (SELECT `id` FROM `ohrm_data_group` WHERE `name`='subordinates');
SET @qualification_work_datagroup_id := (SELECT `id` FROM `ohrm_data_group` WHERE `name`='qualification_work');
SET @qualification_edu_datagroup_id := (SELECT `id` FROM `ohrm_data_group` WHERE `name`='qualification_education');
SET @emergency_contact_datagroup_id := (SELECT `id` FROM `ohrm_data_group` WHERE `name`='emergency_contacts');
SET @emp_dependent_datagroup_id := (SELECT `id` FROM `ohrm_data_group` WHERE `name`='dependents');
SET @emp_immigration_datagroup_id := (SELECT `id` FROM `ohrm_data_group` WHERE `name`='immigration');
SET @emp_membership_datagroup_id := (SELECT `id` FROM `ohrm_data_group` WHERE `name`='membership');
SET @emp_tax_exemption_datagroup_id := (SELECT `id` FROM `ohrm_data_group` WHERE `name`='tax_exemptions');
SET @contact_custom_fields_datagroup_id := (SELECT `id` FROM `ohrm_data_group` WHERE `name` = 'contact_custom_fields');
SET @dependents_custom_fields_datagroup_id := (SELECT `id` FROM `ohrm_data_group` WHERE `name` = 'dependents_custom_fields');
SET @emergency_custom_fields_datagroup_id := (SELECT `id` FROM `ohrm_data_group` WHERE `name` = 'emergency_custom_fields');
SET @immigration_custom_fields_datagroup_id := (SELECT `id` FROM `ohrm_data_group` WHERE `name` = 'immigration_custom_fields');
SET @job_custom_fields_datagroup_id := (SELECT `id` FROM `ohrm_data_group` WHERE `name` = 'job_custom_fields');
SET @membership_custom_fields_datagroup_id := (SELECT `id` FROM `ohrm_data_group` WHERE `name` = 'membership_custom_fields');
SET @personal_custom_fields_datagroup_id := (SELECT `id` FROM `ohrm_data_group` WHERE `name` = 'personal_custom_fields');
SET @pim_configure_custom_fields_datagroup_id := (SELECT `id` FROM `ohrm_data_group` WHERE `name` = 'pim_configure_custom_fields');
SET @qualifications_custom_fields_datagroup_id := (SELECT `id` FROM `ohrm_data_group` WHERE `name` = 'qualifications_custom_fields');
SET @report_datagroup_id := (SELECT `id` FROM `ohrm_data_group` WHERE `name` = 'report-to_custom_fields');
SET @salary_custom_fields_datagroup_id := (SELECT `id` FROM `ohrm_data_group` WHERE `name` = 'salary_custom_fields');
SET @tax_custom_fields_datagroup_id := (SELECT `id` FROM `ohrm_data_group` WHERE `name` = 'tax_custom_fields');
SET @pim_configure_custom_fields := (SELECT `id` FROM `ohrm_data_group` WHERE `name`='pim_configure_custom_fields');
SET @pim_photograph_datagroup_id := (SELECT `id` FROM `ohrm_data_group` WHERE `name`='photograph');


INSERT INTO `ohrm_datagroup_audittrail_section`(`audit_section_id`,`datagroup_id`,`permission`) VALUES
(@personal_details_section_id,@persoanl_details_datagroup_id,1),
(@contact_information_section_id,@contact_info_datagroup_id,1),
(@report_to_section_id,@supervisor_datagroup_id,1),
(@report_to_section_id,@subordinate_datagroup_id,1),
(@qualification_section_id,@qualification_work_datagroup_id,1),
(@qualification_section_id,@qualification_edu_datagroup_id,1),
(@emergency_contact_section_id,@emergency_contact_datagroup_id,1),
(@emp_dependent_section_id,@emp_dependent_datagroup_id,1),
(@emp_immigration_section_id,@emp_immigration_datagroup_id,1),
(@emp_membership_section_id,@emp_membership_datagroup_id,1),
(@emp_tax_exemption_section_id,@emp_tax_exemption_datagroup_id,1),
(@custom_field_section_section_id,@pim_configure_custom_fields,1),
(@custom_field_section_id,@pim_configure_custom_fields,1),
(@personal_custom_field_section_id, @personal_custom_fields_datagroup_id, 1),
(@contact_custom_field_section_id, @contact_custom_fields_datagroup_id, 1),
(@emergency_custom_field_section_id, @emergency_custom_fields_datagroup_id, 1),
(@dependents_custom_field_section_id, @dependents_custom_fields_datagroup_id, 1),
(@immigration_custom_field_section_id, @immigration_custom_fields_datagroup_id, 1),

(@job_custom_field_section_id, @job_custom_fields_datagroup_id, 1),
(@salary_custom_field_section_id, @salary_custom_fields_datagroup_id, 1),
(@reportTo_custom_field_section_id, @report_datagroup_id, 1),
(@qualifications_custom_field_section_id, @qualifications_custom_fields_datagroup_id, 1),
(@membership_custom_field_section_id, @membership_custom_fields_datagroup_id, 1),
(@tax_custom_field_section_id, @tax_custom_fields_datagroup_id, 1),
(@profile_picture_section_id,@pim_photograph_datagroup_id,1);



INSERT INTO `ohrm_audittrail_section_actions` (`section_id`, `action`, `description`) VALUES
(@personal_details_section_id, 'UPDATE PERSONAL DETAILS', 'Update Personal Details'),
(@job_title_section_id, 'UPDATE JOB DETAILS', 'Update Job Details'),
(@salary_section_id, 'ADD DIRECT DEPOSIT DETAILS', 'Add Direct Deposit Details'),
(@salary_section_id, 'ADD SALARY DETAILS', 'Add Salary Details'),
(@salary_section_id, 'UPDATE SALARY DETAILS', 'Update Salary Details'),
(@salary_section_id, 'DELETE SALARY DETAILS', 'Delete Salary Details'),
(@salary_section_id, 'DELETE DIRECT DEPOSIT DETAILS', 'Delete Direct Deposit Details'),
(@salary_section_id, 'UPDATE DIRECT DEPOSIT DETAILS', 'Update Direct Deposit Details'),
(@contact_information_section_id, 'UPDATE CONTACT DETAILS', 'Update Contact Details'),
(@report_to_section_id, 'ADD SUPERVISOR', 'Add Supervisor/Subordinate Details'),
(@report_to_section_id, 'DELETE SUPERVISOR', 'Delete Supervisor/Subordinate Details'),
(@report_to_section_id, 'UPDATE REPORT TO DETAILS', 'Update Supervisor/Subordinate Details'),
(@qualification_section_id, 'ADD LANGUAGE DETAILS', 'Add Language Details'),
(@qualification_section_id, 'ADD LICENSE DETAILS', 'Add License Details'),
(@qualification_section_id, 'ADD SKILL DETAILS', 'Add Skill Details'),
(@qualification_section_id, 'ADD WORK EXPERIENCE DETAILS', 'Add Work Experience Details'),
(@qualification_section_id, 'DELETE LANGUAGE DETAILS', 'Delete Language Details'),
(@qualification_section_id, 'DELETE LICENSE DETAILS', 'Delete License Details'),
(@qualification_section_id, 'DELETE SKILL DETAILS', 'Delete Skill Details'),
(@qualification_section_id, 'DELETE WORK EXPERIENCE DETAILS', 'Delete Work Experience Details'),
(@qualification_section_id, 'UPDATE LANGUAGE DETAILS', 'Update Language Details'),
(@qualification_section_id, 'UPDATE LICENSE DETAILS', 'Update License Details'),
(@qualification_section_id, 'UPDATE SKILL DETAILS', 'Update Skill Details'),
(@qualification_section_id, 'UPDATE WORK EXPERIENCE DETAILS', 'Update Work Experience Details'),
(@emergency_contact_section_id, 'ADD EMERGENCY CONTACT DETAILS', 'Add Emergency Contact Details'),
(@emergency_contact_section_id, 'DELETE EMERGENCY CONTACT DETAILS', 'Delete Emergency Contact Details'),
(@emergency_contact_section_id, 'UPDATE EMERGENCY CONTACT DETAILS', 'Update Emergency Contact Details'),
(@emp_dependent_section_id, 'ADD DEPENDENT DETAILS', 'Add Dependent Details'),
(@emp_dependent_section_id, 'DELETE DEPENDENT DETAILS', 'Delete Dependent Details'),
(@emp_dependent_section_id, 'UPDATE DEPENDENT DETAILS', 'Update Dependent Details'),
(@emp_immigration_section_id, 'ADD IMMIGRATION DETAILS', 'Add Immigration Details'),
(@emp_immigration_section_id, 'DELETE IMMIGRATION DETAILS', 'Delete Immigration Details'),
(@emp_immigration_section_id, 'UPDATE IMMIGRATION DETAILS', 'Update Immigration Details'),
(@emp_membership_section_id, 'ADD MEMBERSHIP DETAILS', 'Add Membership Details'),
(@emp_membership_section_id, 'DELETE MEMBERSHIP DETAILS', 'Delete Membership Details'),
(@emp_membership_section_id, 'UPDATE MEMBERSHIP DETAILS', 'Update Membership Details'),
(@emp_tax_exemption_section_id, 'ADD TAX EXEMPTION DETAILS', 'Add Tax Exemption Details'),
(@emp_tax_exemption_section_id, 'UPDATE TAX EXEMPTION DETAILS', 'Update Tax Exemption Details'),
(@employee_section_id, 'ADD EMPLOYEE DETAILS', 'Add Employee Details'),
(@employee_section_id, 'DELETE EMPLOYEE DETAILS', 'Delete Employee Details'),
(@employee_section_id, 'UPDATE TERMINATION DETAILS', 'Terminate Employee Details'),
(@custom_field_section_section_id, 'ADD CUSTOM FIELD SECTION', 'Add Custom Field Section'),
(@custom_field_section_section_id, 'DELETE CUSTOM FIELD SECTION', 'Delete Custom Field Section'),
(@custom_field_section_section_id, 'UPDATE CUSTOM FIELD SECTION', 'Update Custom Field Section'),
(@custom_field_section_id, 'ADD CUSTOM FIELD', 'Add Custom Field'),
(@custom_field_section_id, 'DELETE CUSTOM FIELD', 'Delete Custom Field'),
(@custom_field_section_id, 'UPDATE CUSTOM FIELD', 'Update Custom Field'),
(@qualification_section_id, 'ADD EDUCATION QUALIFICATION DETAILS', 'Add Education Qualification Details'),
(@qualification_section_id, 'DELETE EDUCATION QUALIFICATION DETAILS', 'Delete Education Qualification Details'),
(@qualification_section_id, 'UPDATE EDUCATION QUALIFICATION DETAILS', 'Update Education Qualification Details'),
(@personal_custom_field_section_id, 'UPDATE PERSONAL DETAILS CUSTOM FIELDS', 'Update Personal Details Custom Fields'),
(@contact_custom_field_section_id, 'UPDATE CONTACT CUSTOM FIELDS', 'Update Contact Custom Fields'),
(@emergency_custom_field_section_id, 'UPDATE EMEREGENCY CUSTOM FIELDS', 'Update Emergency Custom Fields'),
(@dependents_custom_field_section_id, 'UPDATE DEPENDENTS CUSTOM_FIELDS', 'Update Dependents Custom Fields'),
(@immigration_custom_field_section_id, 'UPDATE IMMIGRATION CUSTOM FIELDS', 'Update Immigration Custom Fields'),
(@job_custom_field_section_id, 'UPDATE JOB CUSTOM FIELDS', 'Update Job Custom Fields'),
(@salary_custom_field_section_id, 'UPDATE SALARY CUSTOM FIELDS', 'Update Salary Custom Fields'),
(@reportTo_custom_field_section_id, 'UPDATE SUPERVISOR/SUBORDINATE CUSTOM FIELDS', 'Update Supervisor/Subordinate Custom Fields'),
(@qualifications_custom_field_section_id, 'UPDATE QUALIFICATIONS CUSTOM FIELDS', 'Update Qualifications Custom Fields'),
(@membership_custom_field_section_id, 'UPDATE MEMBERSHIP CUSTOM FIELDS', 'Update Membership Custom Fields'),
(@tax_custom_field_section_id, 'UPDATE TAX CUSTOM FIELDS', 'Update Tax Custom Fields'),
(@profile_picture_section_id, 'ADD PROFILE PICTURE', 'Add Profile Picture'),
(@profile_picture_section_id, 'UPDATE PROFILE PICTURE', 'Update Profile Picture');

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_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 @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 @hr_administration_module_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'HR Administration' AND parent_id = @people_management_pillar_menu_id AND level = 2);

INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, uisref, `status`) VALUES
('Audit Trail', @audittrail_screen_id, @hr_administration_module_menu_id, 3, 1100, NULL, 'admin.auditTrail', 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);

-- admin module related entries
CREATE TABLE `ohrm_audittrail_admin_system_user` (
  `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,
  `app_id` INT DEFAULT NULL,
  `app_name` VARCHAR(1000) DEFAULT NULL,
  `screen_name` VARCHAR(1000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX audit_admin_system_user_idx_app_id (app_id),
  INDEX audit_admin_system_user_idx_screen_name (screen_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `ohrm_audittrail_module` (`name`) VALUES ('HR Administration');

SET @admin_module_id := (SELECT `id` FROM `ohrm_audittrail_module` WHERE `name`='HR Administration');
INSERT INTO `ohrm_audittrail_section` (`name`, `description`, `affected_entity`, `module_id`) VALUES ('SystemUser', 'User Settings', 'Employee', @admin_module_id);
SET @password_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='SystemUser' AND `module_id` = @admin_module_id);
SET @system_users_datagroup_id := (SELECT `id` FROM `ohrm_data_group` WHERE `name`='system_users');

INSERT INTO `ohrm_datagroup_audittrail_section`(`audit_section_id`,`datagroup_id`,`permission`) VALUES
(@password_section_id, @system_users_datagroup_id, 1);



CREATE TABLE `ohrm_audittrail_recruitment_job_vacancy_template_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,
  `app_id` INT DEFAULT NULL,
  `app_name` VARCHAR(1000) DEFAULT NULL,
  `screen_name` VARCHAR(1000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX audit_recruitment_job_vacancy_template_trail_idx_app_id (app_id),
  INDEX audit_recruitment_job_vacancy_template_trail_idx_screen_name (screen_name)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--  HR Admin default authentication trail
CREATE TABLE `ohrm_audittrail_authentication_settings_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,
    `app_id`               INT                   DEFAULT NULL,
    `app_name`             VARCHAR(1000)         DEFAULT NULL,
    `screen_name`          VARCHAR(1000)         DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX                  audit_default_authentication_settings_trail_idx_app_id (app_id),
    INDEX                  audit_default_authentication_settings_trail_idx_screen_name (screen_name)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `ohrm_audittrail_section` (`name`, `description`, `affected_entity`, `module_id`)
VALUES ('AuthenticationSettings', 'Authentication Settings', '', @admin_module_id);

SET
@ohrm_audittrail_authentication_settings_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='AuthenticationSettings');

INSERT INTO `ohrm_audittrail_section_actions` (`section_id`, `action`, `description`)
VALUES (@ohrm_audittrail_authentication_settings_section_id, 'UPDATE AUTHENTICATION SETTINGS', 'Update Authentication Settings');