CREATE TABLE IF NOT EXISTS ohrm_pim_history_event_type (
    id INT AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL UNIQUE,
    PRIMARY KEY(id)
) ENGINE = INNODB DEFAULT CHARACTER SET = utf8;

CREATE TABLE IF NOT EXISTS ohrm_pim_history_event (
    id INT AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_at DATETIME DEFAULT NULL,
    is_readonly tinyint(1) not null default 0,
    is_hidden tinyint(1) not null default 0,
    PRIMARY KEY(id)
) ENGINE = INNODB DEFAULT CHARACTER SET = utf8;

CREATE TABLE IF NOT EXISTS ohrm_pim_history_event_event_type (
    event_id INT NOT NULL,
    type_id INT NOT NULL,
    PRIMARY KEY(event_id, type_id),
    CONSTRAINT `fk_pim_history_event_event_type_event_id` FOREIGN KEY (`event_id`) REFERENCES `ohrm_pim_history_event`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_pim_history_event_event_type_type_id` FOREIGN KEY (`type_id`) REFERENCES `ohrm_pim_history_event_type`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = INNODB DEFAULT CHARACTER SET = utf8;

CREATE TABLE IF NOT EXISTS `ohrm_audittrail_pim_history_event_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` MEDIUMTEXT,
    `is_description_formatting_enabled` BOOLEAN NOT NULL DEFAULT FALSE ,
    `app_id` INT DEFAULT NULL,
    `app_name` VARCHAR(1000) DEFAULT NULL,
    `screen_name` VARCHAR(1000) DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX audit_pim_history_event_trail_idx_app_id (app_id),
    INDEX audit_pim_history_event_trail_idx_screen_name (screen_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `ohrm_pim_history_event_type`(`id`, `name`) VALUES
    (1, 'Job'),
    (2, 'Salary');

INSERT INTO `ohrm_pim_history_event`(`id`, `name`, `is_readonly`, `is_hidden`) VALUES
    (1, 'Joined', 1, 0),
    (2, 'Promoted', 1, 0),
    (3, 'Location Changed', 1, 0),
    (4, 'Initial Contract', 1, 0),
    (5, 'Contract Renewal', 1, 0),
    (6, 'First Time Addition', 1, 0),
    (7, 'Cost Center Move', 1, 0),
    (8, 'FLSA Exemption Changed', 1, 0),
    (9, 'EEO Job Category Changed', 1, 0),
    (10, 'Salary Increment', 1, 0),
    (11, 'EEO Pay Band Changed', 1, 0),
    (12, 'Employment Terminated', 1, 1),
    (13, 'Employment Activated', 1, 1),
    (14, 'Bulk Data Upload', 1, 1),
    (15, 'Work Shift Deleted', 1, 1),
    (16, 'Pay Policy Criteria Updated', 1, 1),
    (17, 'Sub Unit Changed', 1, 0);

INSERT INTO `ohrm_pim_history_event_event_type` (event_id, type_id) VALUES
    (1, 1), (2, 1), (3, 1), (4, 1), (5, 1), (6, 1), (7, 1), (8, 1), (9, 1), (12, 1), (13, 1), (14, 1), (15, 1), (16, 1), (17, 1),
    (1, 2), (2, 2), (4, 2), (5, 2), (6, 2), (10, 2), (11, 2);

INSERT INTO `ohrm_email` (`name`) VALUES ('pim.updates');
SET @email_id := (SELECT LAST_INSERT_ID());
INSERT INTO `ohrm_email_processor` (  `email_id`, `class_name`) VALUES ( @email_id, 'PimAuditEmailProcessor');

SET @other_email_notification_type := (SELECT id FROM ohrm_email_notification_type where `name`='Other');
INSERT INTO `ohrm_email_notification`(`name`,`email_id`,`is_enable`,`type_id`) VALUES
    ('PIM Updates',@email_id,0,@other_email_notification_type);

INSERT INTO `ohrm_email_template` (`email_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`)  VALUES
    (@email_id, 'en_US', NULL, NULL, 'PIM Field Update Notification',
'Dear %subscriberName%,


The changes to pim fields from %fromDate% to %toDate% are listed below.

%pimChanges%


Thank you
System Admin
(This is an auto-generated email, please do not reply!) ');

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
    ('pim_reporting_methods', 'Employee Management - Configure - Reporting Methods', 1, 1, 1, 1);

SET @pim_reporting_methods_data_group_id := (SELECT LAST_INSERT_ID());
INSERT INTO `ohrm_user_role_data_group` (`user_role_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`, `self`) VALUES
    (1, @pim_reporting_methods_data_group_id, 1, 1, 1, 1, 0);

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
    ('pim_optional_fields', 'Employee Management - Configure - Optional Fields', 1, 1, 1, 1);

SET @pim_optional_fields_data_group_id := (SELECT LAST_INSERT_ID());
INSERT INTO `ohrm_user_role_data_group` (`user_role_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`, `self`) VALUES
    (1, @pim_optional_fields_data_group_id, 1, 1, 1, 1, 0);

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
    ('pim_termination_reasons', 'Employee Management - Configure - Termination Reasons', 1, 1, 1, 1);

SET @pim_termination_reasons_data_group_id := (SELECT LAST_INSERT_ID());
INSERT INTO `ohrm_user_role_data_group` (`user_role_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`, `self`) VALUES
    (1, @pim_termination_reasons_data_group_id, 1, 1, 1, 1, 0);

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`, `route_name`) VALUES
    ('PIM History Events', 3, 'pimHistoryEvents', NULL);

SET @pim_history_events_screen_id := (SELECT id FROM ohrm_screen WHERE name = 'PIM History Events');
INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete) VALUES
    (1, @pim_history_events_screen_id, 1, 1, 1, 1);

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
    ('pim_history_events', 'Employee Management - Configure - Job and Salary Events List', 1, 1, 1, 1);

SET @pim_history_events_data_group_id := (SELECT id FROM ohrm_data_group WHERE name = 'pim_history_events');
INSERT INTO `ohrm_user_role_data_group` (`user_role_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`, `self`) VALUES
    (1, @pim_history_events_data_group_id, 1, 1, 1, 1, 0);

INSERT INTO ohrm_user_role_type_data_group(user_role_type_id, data_group_id, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
    (1, @pim_history_events_data_group_id, 1, 1, 1, 1);

INSERT INTO `ohrm_data_group_screen`(`data_group_id`, `screen_id`, `permission`) VALUES
    (@pim_history_events_data_group_id, @pim_history_events_screen_id, 1);

SET @employee_management_module_menu_id := (SELECT id FROM ohrm_menu_item WHERE menu_title = 'Employee Management');
SET @configuration_menu_id := (SELECT id FROM ohrm_menu_item WHERE menu_title = 'Configurations' AND parent_id = @employee_management_module_menu_id);
INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `uisref`, `status`) VALUES
    ('Job and Salary Events', @pim_history_events_screen_id, @configuration_menu_id, 4, 1000, NULL, 'vuepim.history_events', 1);

SET @audit_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
    ('WorkWeek', 'Work Week (Archived)', 'Employee', @audit_pim_module_id);

SET @workweek_audit_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name` = 'WorkWeek' LIMIT 1);

INSERT INTO `ohrm_audittrail_section_actions` (`section_id`, `action`, `description`) VALUES
    (@workweek_audit_section_id, 'ADD WORK WEEK DETAILS', 'Add Work Week Details'),
    (@workweek_audit_section_id, 'UPDATE WORK WEEK DETAILS', 'Update Work Week Details'),
    (@workweek_audit_section_id, 'DELETE WORK WEEK DETAILS', 'Delete Work Week Details');

DROP TABLE IF EXISTS `ohrm_audittrail_pim_work_week_trail`;

CREATE TABLE `ohrm_audittrail_pim_work_week_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_pim_work_week_trail_idx_app_id (app_id),
  INDEX audit_pim_work_week_trail_idx_screen_name (screen_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `ohrm_eeo_job_category` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `ohrm_eeo_pay_grade` (
  `id` INT NOT NULL,
  `name` VARCHAR(45) NULL,
  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `ohrm_eeo_race_ethnicity` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO  `ohrm_eeo_job_category` (`id`, `name`) VALUES
    ('1', 'Executive/Senior Level Officials and Managers'),
    ('2', 'First/Mid Level Officials and Managers'),
    ('3', 'Professionals'),
    ('4', 'Technicians'),
    ('5', 'Sales Workers'),
    ('6', 'Administrative Support Workers'),
    ('7', 'Craft Workers'),
    ('8', 'Operatives'),
    ('9', 'Laborers and Helpers'),
    ('10', 'Service Workers');

INSERT INTO  `ohrm_eeo_pay_grade` (`id`, `name`) VALUES
    ('1', '$19239 and under'),
    ('2', '$19240 - $24439'),
    ('3', '$24440 - $30679'),
    ('4', '$30680 - $38999'),
    ('5', '$39000 - $49919'),
    ('6', '$49920 - $62919'),
    ('7', '$62920 - $80079'),
    ('8', '$80080 - $101919'),
    ('9', '$101920 - $128959'),
    ('10', '$128960 - $163799'),
    ('11', '$163800 - $207999'),
    ('12', '$208000 and over');

INSERT INTO `ohrm_eeo_race_ethnicity` (`id`, `name`) VALUES
    ('1', 'Hispanic or Latino'),
    ('2', 'White'),
    ('3', 'Black or African American'),
    ('4', 'Native Hawaiian or Other Pacific Islander'),
    ('6', 'Asian'),
    ('7', 'American Indian or Alaska Native'),
    ('8', 'Two or More Races');

INSERT INTO `ohrm_async_event` (`name`, `display_name`, `group_name`, `enabled`, `position`) VALUES
    ('employee_added', 'Add Employee', 'pim.add_employee', 1, 10),
    ('employee_updated', 'Update Employee Common Details', 'pim.update_employee', 1, 10),
    ('pim.save_job_record', 'Update Employee Job Details', 'pim.update_employee', 1, 10),
    ('pim.save_direct_deposit_record', 'Update Employee Direct Deposit Details', 'pim.update_employee', 1, 10),
    ('pim.save_salary_record', 'Update Employee Salary Details', 'pim.update_employee', 1, 10),
    ('pim.custom_field_value_update', 'Update Employee Custom Field Value', 'pim.update_employee', 1, 10),
    ('employees_terminated', 'Terminate Employee', 'pim.update_employee', 1, 10),
    ('employees_activated', 'Re-activate Employee', 'pim.update_employee', 1, 10),
    ('pim.custom_field_add', 'Add Custom Field', 'pim.config', 1, 10),
    ('pim.custom_field_update', 'Update Custom Field', 'pim.config', 1, 10),
    ('pim.custom_field_delete', 'Delete Custom Field', 'pim.config', 1, 10),
    ('pim.job_history_delete', 'Delete Employee Job History Record', 'pim.update_employee', 1, 10),
    ('pim.salary_history_delete', 'Delete Employee Salary History Record', 'pim.update_employee', 1, 10),
    ('pim.delete_direct_deposit_record', 'Delete Employee Direct Deposit Record', 'pim.update_employee', 1, 10),
    ('pim.job_change_effective_date_reached', 'Effective date of a Job Record change reached', 'pim.update_employee', 1, 10),
    ('pim.custom_field_change_effective_date_reached', 'Effective date of a Job Record custom field change reached', 'pim.update_employee', 1, 10),
    ('pim.salary_change_effective_date_reached', 'Effective date of a Salary Record change reached', 'pim.update_employee', 1, 10),
    ('pim.first_time_joined_date_reached', 'Joined date of an employee is reached', 'pim.update_employee', 1, 10),
    ('pim.current_job_record_updated_due_to_history_delete', 'Current Job Details updated due to Job History Record delete', 'pim.update_employee', 1, 10),
    ('pim.current_salary_record_updated_due_to_history_delete', 'Current Salary Details updated due to Job History Record deleted', 'pim.update_employee', 1, 10),
    ('pim.latest_job_record_updated_due_to_history_delete', 'Latest Job Details updated due to Job History Record delete', 'pim.update_employee', 1, 10),
    ('pim.latest_salary_record_updated_due_to_history_delete', 'Latest Salary Details updated due to Job History Record deleted', 'pim.update_employee', 1, 10),
    ('pim.supervisor_add', 'Add Supervisor', 'pim.update_employee', 1, 10),
    ('pim.subordinate_add', 'Add Subordinate', 'pim.update_employee', 1, 10),
    ('pim.supervisor_update', 'Update Supervisor', 'pim.update_employee', 1, 10),
    ('pim.subordinate_update', 'Update Subordinate', 'pim.update_employee', 1, 10),
    ('pim.supervisor_delete', 'Delete Supervisor', 'pim.update_employee', 1, 10),
    ('pim.subordinate_delete', 'Delete Subordinate', 'pim.update_employee', 1, 10),
    ('pim.save_emergency_contact_record', 'Update Employee Emergency Contact Details', 'pim.update_employee', 1, 10),
    ('pim.delete_emergency_contact_record', 'Delete Employee Emergency Contact Details', 'pim.update_employee', 1, 10),
    ('pim.save_work_experience_qualification_record', 'Update Employee Work Experience Qualification Details', 'pim.update_employee', 1, 10),
    ('pim.delete_work_experience_qualification_record', 'Delete Employee Work Experience Qualification Details', 'pim.update_employee', 1, 10),
    ('pim.save_education_qualification_record', 'Update Employee Education Qualification Details', 'pim.update_employee', 1, 10),
    ('pim.delete_education_qualification_record', 'Delete Employee Education Qualification Details', 'pim.update_employee', 1, 10),
    ('pim.save_skill_qualification_record', 'Update Employee Skill Qualification Details', 'pim.update_employee', 1, 10),
    ('pim.delete_skill_qualification_record', 'Delete Employee Skill Qualification Details', 'pim.update_employee', 1, 10),
    ('pim.save_language_qualification_record', 'Update Employee Language Qualification Details', 'pim.update_employee', 1, 10),
    ('pim.delete_language_qualification_record', 'Delete Employee Language Qualification Details', 'pim.update_employee', 1, 10),
    ('pim.save_license_qualification_record', 'Update Employee License Qualification Details', 'pim.update_employee', 1, 10),
    ('pim.delete_license_qualification_record', 'Delete Employee License Qualification Details', 'pim.update_employee', 1, 10),
    ('pim.custom_field_value_delete', 'Delete Employee Custom Field Value', 'pim.update_employee', 1, 10),
    ('pim.job_record_updated_due_to_history_delete', 'Job Details updated due to Job History Record delete', 'pim.update_employee', 1, 10),
    ('pim.salary_record_updated_due_to_history_delete', 'Salary Details updated due to Salary History Record delete', 'pim.update_employee', 1, 10);

INSERT INTO `ohrm_audittrail_section` (`name`, `description`, `affected_entity`, `module_id`) VALUES
    ('PimHistoryEvent', 'Job and Salary Events', 'Job and Salary Event', @audit_pim_module_id);

SET @ohrm_audittrail_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='PimHistoryEvent');
INSERT INTO `ohrm_audittrail_section_actions` (`section_id` ,`action` ,`description`) VALUES
    (@ohrm_audittrail_section_id, 'ADD EVENT', 'Add Event'),
    (@ohrm_audittrail_section_id, 'UPDATE EVENT', 'Update Event'),
    (@ohrm_audittrail_section_id, 'DELETE EVENT', 'Delete Event'),
    (@ohrm_audittrail_section_id, 'REACTIVATE EVENT', 'Reactivate Event');

SET @max_event_listener_position := (SELECT MAX(`position`)FROM ohrm_event_listener);
INSERT INTO `ohrm_event_listener` (`event`, `listener_class`, `enabled`, `position`) VALUES
    ('pim.add_pim_history_event', 'AddPimHistoryEventAuditListener', '1', @max_event_listener_position+1),
    ('pim.update_pim_history_event', 'UpdatePimHistoryEventAuditListener', '1', @max_event_listener_position+2),
    ('pim.delete_pim_history_event', 'DeletePimHistoryEventAuditListener', '1', @max_event_listener_position+3),
    ('employees_terminated', 'EmployeeTerminationMailer', '1', @max_event_listener_position+4),
    ('employees_termination_scheduled', 'EmployeeTerminationMailer', '1', @max_event_listener_position+5);

CREATE TABLE IF NOT EXISTS `ohrm_audittrail_work_schedule_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` MEDIUMTEXT,
    `is_description_formatting_enabled` BOOLEAN NOT NULL DEFAULT FALSE ,
    `app_id` INT DEFAULT NULL,
    `app_name` VARCHAR(1000) DEFAULT NULL,
    `screen_name` VARCHAR(1000) DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX audit_work_schedule_trail_idx_app_id (app_id),
    INDEX audit_work_schedule_trail_idx_screen_name (screen_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `ohrm_audittrail_section` (`name`, `description`, `affected_entity`, `module_id`) VALUES
    ('WorkSchedule', 'Work Schedules', 'Work Schedule', @audit_pim_module_id);

SET @ohrm_work_schedule_audittrail_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='WorkSchedule' LIMIT 1);
INSERT INTO `ohrm_audittrail_section_actions` (`section_id` ,`action` ,`description`) VALUES
    (@ohrm_work_schedule_audittrail_section_id, 'ADD WORK SCHEDULE', 'Add Work Schedule'),
    (@ohrm_work_schedule_audittrail_section_id, 'UPDATE WORK SCHEDULE', 'Update Work Schedule'),
    (@ohrm_work_schedule_audittrail_section_id, 'DELETE WORK SCHEDULE', 'Delete Work Schedule');

SET @max_event_listener_position := (SELECT MAX(`position`)FROM ohrm_event_listener);
INSERT INTO `ohrm_event_listener` (`event`, `listener_class`, `enabled`, `position`) VALUES
    ('pim.add_work_schedule', 'AddWorkScheduleAuditListener', '1', @max_event_listener_position+1),
    ('pim.add_work_schedule', 'WorkCalendarUpdateListener', '1', @max_event_listener_position+2),
    ('pim.update_work_schedule', 'UpdateWorkScheduleAuditListener', '1', @max_event_listener_position+3),
    ('pim.update_work_schedule', 'WorkCalendarUpdateListener', '1', @max_event_listener_position+4),
    ('pim.delete_work_schedule', 'DeleteWorkScheduleAuditListener', '1', @max_event_listener_position+5),
    ('pim.delete_work_schedule', 'WorkCalendarUpdateListener', '1', @max_event_listener_position+6),
    ('pim.update_work_schedule', 'RecalculatePayCodesOnWorkScheduleUpdateListener', '1', @max_event_listener_position+7),
    ('pim.save_job_record', 'RecalculatePayCodesOnEmployeeWorkScheduleUpdateListener', '1', @max_event_listener_position+8),
    ('pim.current_job_record_updated_due_to_history_delete', 'RecalculatePayCodesOnEmployeeWorkScheduleUpdateListener', '1', @max_event_listener_position+9),
    ('pim.job_change_effective_date_reached', 'RecalculatePayCodesOnEmployeeWorkScheduleUpdateListener', '1', @max_event_listener_position+10),
    ('pim.generate_documents', 'GenerateDocumentsAuditListener', '1', @max_event_listener_position+11);

SET @other_notification_type := (SELECT id FROM ohrm_notification_type where `name`='Other');
SET @pim_module_id := (SELECT `id` FROM `ohrm_module` WHERE `name`='pim');
SET @email_media := (SELECT id FROM ohrm_notification_media WHERE `name` = 'Email');

INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`, `hidden`) VALUES
    ('Employee Management Document Notification', 1, @other_notification_type, 'PimDocumentEmailProcessor',
     '{"data_group_permissions":{"data_group":"pim_document_templates"},"list_action_rights":{"change_status":false,"change_subscribers":true,"copy_templates":true},"show_user_role_selection": true}', null, null,@pim_module_id, 1);
SET @notification_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_supported_media` (`notification_id`, `media_id`) VALUES
    (@notification_id, @email_media);

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`is_default`,`name`,`subject`,`body`) VALUES
    (@notification_id, @email_media, 'en_US', 1,
     'Default Template',
     '%DocumentName% for %EmpFirstName% %EmpLastName% ',
     '<p>Dear %EmpFirstName%,</p>
<p>I hope this message finds you well.</p>
<p>Attached to this email, you will find the document titled "%DocumentName%". Should you have any questions or require further assistance regarding its contents, please do not hesitate to reach out.</p>
<p>Best regards,</p>');

SET @notification_subscriber_subscription_template_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber` (`id`, `media_id`, `address_part`, `parameters`, `type`, `emp_number`, `name`, `email`, `user_role_id`) VALUES
    (NULL, '1', "to", '{"restrict_employees":true}', 'Custom', NULL, 'Affected Employee', NULL, NULL);

SET @notification_subscriber_id := LAST_INSERT_ID();

SET @document_notification_id := (SELECT id FROM ohrm_notification WHERE name = 'Employee Management Document Notification');

INSERT INTO `ohrm_notification_subscription` (`id`, `notification_id`, `parameters`, `affected_emp_number`, `notify_affected_employee`, `notify_before_value`, `notify_before_unit`, `schedule_dom`, `schedule_dow`, `schedule_lastrun`, `workflow_item_id`, `hidden`) VALUES
    (NULL, @document_notification_id, NULL, NULL, '0', NULL, NULL, NULL, NULL, NULL, NULL, '0');

SET @notification_subscription_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription` (`subscription_id`, `subscriber_id`, `template_id`) VALUES
    (@notification_subscription_id, @notification_subscriber_id, @notification_subscriber_subscription_template_id);


create table `ohrm_employee_generated_document`
(
    `id`       int(13) not null auto_increment,
    `generated_document_id` int(13) not null,
    `employee_number` INT(7) NOT NULL,
    `document_enabled_data_group_id` INT,
    primary key (`id`),
    CONSTRAINT `fk_employee_generated_document_generated_document` FOREIGN KEY (`generated_document_id`) REFERENCES `ohrm_generated_document` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_employee_document_employee` FOREIGN KEY (`employee_number`) REFERENCES `hs_hr_employee` (`emp_number`)
        ON DELETE CASCADE,
    CONSTRAINT `fk_document_data_group_data_group` FOREIGN KEY (`document_enabled_data_group_id`) REFERENCES `ohrm_data_group`(`id`)
        ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table `ohrm_employee_notification_history`
(
    `id`       int(13) not null auto_increment,
    `notification_history_id` int(13) not null,
    `employee_number` INT(7) NOT NULL,
    primary key (`id`),
    CONSTRAINT `fk_employee_notification_notification_history` FOREIGN KEY (`notification_history_id`) REFERENCES `ohrm_notification_history` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_employee_notification_history_employee` FOREIGN KEY (`employee_number`) REFERENCES `hs_hr_employee` (`emp_number`)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `ohrm_audittrail_pim_created_documents_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` MEDIUMTEXT,
    `is_description_formatting_enabled` BOOLEAN NOT NULL DEFAULT FALSE ,
    `app_id` INT DEFAULT NULL,
    `app_name` VARCHAR(1000) DEFAULT NULL,
    `screen_name` VARCHAR(1000) DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX audit_pim_created_documents_trail_idx_app_id (app_id),
    INDEX audit_pim_created_documents_trail_idx_screen_name (screen_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `ohrm_audittrail_section` (`name`, `description`, `affected_entity`, `module_id`) VALUES
    ('CreatedDocuments', 'Created Documents', 'Employee', @audit_pim_module_id);

SET @pim_create_documents_audit_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name` = 'CreatedDocuments' LIMIT 1);

INSERT INTO `ohrm_audittrail_section_actions` (`section_id`, `action`, `description`) VALUES
    (@pim_create_documents_audit_section_id, 'GENERATE DOCUMENTS', 'Generate Documents');

SET @ohrm_data_group_qualification_languages_id =  (SELECT `id`  FROM `ohrm_data_group` WHERE `name` = 'qualification_languages');
SET @ohrm_data_group_qualification_license_id =  (SELECT `id`  FROM `ohrm_data_group` WHERE `name` = 'qualification_license');
SET @ohrm_data_group_qualification_skills_id =  (SELECT `id`  FROM `ohrm_data_group` WHERE `name` = 'qualification_skills');
SET @ohrm_data_group_work_schedules_id =  (SELECT `id`  FROM `ohrm_data_group` WHERE `name` = 'work_schedules');


SET @ohrm_audittrail_section_qualification = (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='Qualification');
SET @ohrm_audittrail_section_work_schedule = (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='WorkSchedule');

SET @ohrm_data_group_pim_document_templates_id =  (SELECT `id`  FROM `ohrm_data_group` WHERE `name` = 'pim_document_templates');

INSERT INTO `ohrm_datagroup_audittrail_section` (`audit_section_id`, `datagroup_id`, `consider_in_notification`)
VALUES

    (@ohrm_audittrail_section_qualification, @ohrm_data_group_qualification_license_id, 0),
    (@ohrm_audittrail_section_qualification, @ohrm_data_group_qualification_languages_id, 0),
    (@ohrm_audittrail_section_qualification, @ohrm_data_group_qualification_skills_id, 0),
    (@ohrm_audittrail_section_work_schedule, @ohrm_data_group_work_schedules_id, 0),
    (@ohrm_audittrail_section_id, @pim_history_events_data_group_id, 0),
    (@pim_create_documents_audit_section_id, @ohrm_data_group_pim_document_templates_id, 0);

INSERT INTO ohrm_config (`property`, `value`)
VALUES ('pim.employee_document_digital_sign', 'off');

-- Salary Fields Visibility
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`, `route_name`) VALUES
    ('Salary Fields Visibility', 3, 'salaryFieldsVisibility', NULL);

SET @salary_fields_visibility_screen_id := (SELECT id FROM ohrm_screen WHERE name = 'Salary Fields Visibility');
INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete) VALUES
    (1, @salary_fields_visibility_screen_id, 1, 0, 1, 0);

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
    ('salary_configuration', 'Employee Management - Configure - Salary Fields Visibility', 1, 0, 1, 0);

SET @salary_configuration_data_group_id := (SELECT id FROM ohrm_data_group WHERE name = 'salary_configuration');
INSERT INTO `ohrm_user_role_data_group` (`user_role_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`, `self`) VALUES
    (1, @salary_configuration_data_group_id, 1, 0, 1, 0, 0);

INSERT INTO ohrm_user_role_type_data_group(user_role_type_id, data_group_id, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
    (1, @salary_configuration_data_group_id, 1, 0, 1, 0);

INSERT INTO `ohrm_data_group_screen`(`data_group_id`, `screen_id`, `permission`) VALUES
    (@salary_configuration_data_group_id, @salary_fields_visibility_screen_id, 1);

SET @employee_management_module_menu_id := (SELECT id FROM ohrm_menu_item WHERE menu_title = 'Employee Management');
SET @configuration_menu_id := (SELECT id FROM ohrm_menu_item WHERE menu_title = 'Configurations' AND parent_id = @employee_management_module_menu_id);
INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `uisref`, `status`) VALUES
    ('Salary Fields Visibility', @salary_fields_visibility_screen_id, @configuration_menu_id, 4, 1000, NULL, 'vuepim.salary_configuration', 1);

-- Salary Fields Visibility - Audit
INSERT INTO `ohrm_audittrail_section` (`name`, `description`, `affected_entity`, `module_id`) VALUES
    ('PimSalarySummaryField', 'Salary Fields Visibility', '', @audit_pim_module_id);

SET @ohrm_audittrail_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='PimSalarySummaryField');
INSERT INTO `ohrm_audittrail_section_actions` (`section_id` ,`action` ,`description`) VALUES
(@ohrm_audittrail_section_id, 'UPDATE SALARY FIELDS VISIBILITY', 'Update Salary Fields Visibility');

INSERT INTO `ohrm_datagroup_audittrail_section` (`audit_section_id`, `datagroup_id`, `consider_in_notification`) VALUES
(@ohrm_audittrail_section_id, @salary_configuration_data_group_id, 0);

SET @max_event_listener_position := (SELECT MAX(`position`)FROM ohrm_event_listener);
INSERT INTO `ohrm_event_listener` (`event`, `listener_class`, `enabled`, `position`) VALUES
('pim.salary_configuration.update', 'UpdatePimSalaryConfigurationAuditListener', '1', @max_event_listener_position+1),
('pim.salary_configuration.update', 'PIMSalaryConfigurationListener', '1', @max_event_listener_position+2);

CREATE TABLE IF NOT EXISTS `ohrm_audittrail_salary_summary_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` MEDIUMTEXT,
    `is_description_formatting_enabled` BOOLEAN NOT NULL DEFAULT FALSE ,
    `app_id` INT DEFAULT NULL,
    `app_name` VARCHAR(1000) DEFAULT NULL,
    `screen_name` VARCHAR(1000) DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX audit_salary_summary_field_trail_idx_app_id (app_id),
    INDEX audit_salary_summary_field_trail_idx_screen_name (screen_name)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;