create table `ohrm_job_contract_attachment` (
    `id` INT AUTO_INCREMENT,
    `description` varchar(400) default null,
    `filename` varchar(255) default null,
    `size` int(11) default '0',
    `attachment` mediumblob,
    `file_type` varchar(200) default null,
    `attached_by` int default null,
    `attached_by_name` varchar(200),
    `attached_time` timestamp not null default now(),
    PRIMARY KEY(id)
) engine=innodb default charset=utf8;

CREATE TABLE IF NOT EXISTS `ohrm_salary_component` (
    `id` INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(60) NOT NULL UNIQUE,
    `type` TINYINT UNSIGNED NOT NULL DEFAULT 1,
    `value_type` TINYINT UNSIGNED NOT NULL DEFAULT 1,
    `is_cost_to_company` TINYINT UNSIGNED DEFAULT NULL,
    `is_part_of_total_payable` TINYINT UNSIGNED DEFAULT NULL,
    `version` BIGINT(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

create table `ohrm_candidate_job_offer_salary_component_value`
(
    `id`                         int not null auto_increment,
    `value` VARCHAR(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT '',
    `amount` VARCHAR(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT '',
    `candidate_job_offer_event_detail_id` int not null,
    `salary_component_id` INT UNSIGNED NOT NULL,
    primary key (`id`),
    CONSTRAINT `fk_ohrm_candidate_job_offer_salary_component_event_detail` FOREIGN KEY (`candidate_job_offer_event_detail_id`) REFERENCES `ohrm_candidate_job_offer_event_detail` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT `fk_ohrm_candidate_job_offer_salary_component_values_component` FOREIGN KEY (`salary_component_id`) REFERENCES `ohrm_salary_component` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) engine = innodb
  default charset = utf8;

CREATE TABLE IF NOT EXISTS `ohrm_salary_component_history` (
    `history_id` INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    `id` INT UNSIGNED,
    `name` VARCHAR(60) NOT NULL,
    `type` TINYINT UNSIGNED NOT NULL DEFAULT 1,
    `value_type` TINYINT UNSIGNED NOT NULL DEFAULT 1,
    `is_cost_to_company` TINYINT UNSIGNED DEFAULT NULL,
    `is_part_of_total_payable` TINYINT UNSIGNED DEFAULT NULL,
    `version` BIGINT(20) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

INSERT INTO `ohrm_salary_component` VALUES (1, 'Annual Basic Payment', 1, 1, 1, 1, 1);
INSERT INTO `ohrm_salary_component_history` VALUES (1, 1, 'Annual Basic Payment', 1, 1, 1, 1, 1);

CREATE TABLE IF NOT EXISTS `ohrm_job_record_history` (
  `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
  `employee_number` INT(7) NOT NULL,
  `job_title_id` INT(13),
  `job_title_name` VARCHAR(100),
  `employment_status_id` INT,
  `employment_status_name` VARCHAR(60),
  `job_category_id` INT,
  `job_category_name` VARCHAR(60),
  `joined_date` DATE,
  `probation_end_date` DATE,
  `date_of_permanency` DATE,
  `effective_date` DATE,
  `event_id` INT(6),
  `subunit_id` INT(6),
  `subunit_name` VARCHAR(100),
  `location_id` INT,
  `location_name` VARCHAR(110),
  `comment` VARCHAR(250),
  `has_contract_details` TINYINT UNSIGNED DEFAULT 0,
  `contract_start_date` DATE,
  `contract_end_date` DATE,
  `contract_attachment_id` INT,
  `work_schedule_id` int unsigned  DEFAULT NULL,
  `work_schedule_name` VARCHAR(255),
  `modified_time`    datetime DEFAULT NULL,
  `modified_by_id`   BIGINT(20) DEFAULT NULL,
  `modified_by_name` VARCHAR(100) DEFAULT NULL,
  `cost_centre_id` BIGINT DEFAULT NULL,
  `cost_centre_name` VARCHAR(60) DEFAULT NULL,
  `eeo_job_category` int  null,
  `eeo_job_category_name` VARCHAR(45) DEFAULT null,
  `flsa_exempt` int  null,
  `pay_policy_id` INT default null,
  `pay_policy_name` VARCHAR(255) default null,
  `readonly` TINYINT(1) NOT NULL DEFAULT 0,
  `notified_on_effective_date` TINYINT(1) NOT NULL DEFAULT 1,
  INDEX ohrm_job_record_history_notified_idx (`notified_on_effective_date`),
  CONSTRAINT `fk_job_record_history_emp_number` FOREIGN KEY (`employee_number`) REFERENCES `hs_hr_employee` (`emp_number`) ON DELETE CASCADE,
  CONSTRAINT `fk_job_record_history_job_title_id` FOREIGN KEY (`job_title_id`) REFERENCES `ohrm_job_title` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_job_record_history_employment_status_id` FOREIGN KEY (`employment_status_id`) REFERENCES `ohrm_employment_status` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_job_record_history_job_category_id` FOREIGN KEY (`job_category_id`) REFERENCES `ohrm_job_category` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_job_record_history_subunit_id` FOREIGN KEY (`subunit_id`) REFERENCES `ohrm_subunit` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_job_record_history_event_id` FOREIGN KEY (`event_id`) REFERENCES `ohrm_pim_history_event` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_job_record_history_location_id` FOREIGN KEY (`location_id`) REFERENCES `ohrm_location` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_job_record_history_cost_centre_id` FOREIGN KEY (`cost_centre_id`) REFERENCES `ohrm_cost_centre` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_job_record_history_eeo_job_category` FOREIGN KEY (`eeo_job_category`) REFERENCES `ohrm_eeo_job_category` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_job_record_history_work_schedule_id` FOREIGN KEY (`work_schedule_id`) REFERENCES `ohrm_work_schedule` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_job_record_history_contract_attachment_id` FOREIGN KEY (`contract_attachment_id`) REFERENCES `ohrm_job_contract_attachment` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

ALTER TABLE `ohrm_emp_termination`
    ADD CONSTRAINT  `ohrm_emp_termination_job_termination_job_record_history_id` FOREIGN KEY (`termination_job_record_history_id`)
        REFERENCES `ohrm_job_record_history`(`id`) ON DELETE SET NULL;
ALTER TABLE `ohrm_emp_termination`
    ADD CONSTRAINT  `ohrm_emp_termination_job_reactivation_job_record_history_id` FOREIGN KEY (`reactivation_job_record_history_id`)
        REFERENCES `ohrm_job_record_history`(`id`) ON DELETE SET NULL;

CREATE TABLE IF NOT EXISTS `ohrm_custom_field_value_history_for_job_record_history` (
    `job_record_history_id` BIGINT NOT NULL,
    `custom_field_value_id` INT(11) NOT NULL,
    `custom_field_value_history_version` BIGINT(20) NOT NULL,
    PRIMARY KEY(`job_record_history_id`, `custom_field_value_id`, `custom_field_value_history_version`),
    CONSTRAINT `ohrm_custom_field_value_history_for_job_record_history_job` FOREIGN KEY (`job_record_history_id`) REFERENCES
    `ohrm_job_record_history`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `ohrm_custom_field_value_history_for_job_record_history_custom` FOREIGN KEY (`custom_field_value_id`, `custom_field_value_history_version`) REFERENCES
    `ohrm_custom_field_value_history`(`id`, `version`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = INNODB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS `ohrm_salary_record_history` (
    `id` INT AUTO_INCREMENT,
    `employee_number` INT(7),
    `effective_date` DATE NULL DEFAULT NULL,
    `event_id` INT(6),
    `pay_grade_id` INT,
    `pay_grade_name` VARCHAR(60),
    `currency_id` CHAR(3),
    `currency_name` VARCHAR(70),
    `eeo_paygrade` INT DEFAULT NULL,
    `eeo_paygrade_name` VARCHAR(60),
    `comment` VARCHAR(250),
    `modified_time`    datetime DEFAULT NULL,
    `modified_by_id`   BIGINT(20) DEFAULT NULL,
    `modified_by_name` VARCHAR(100) DEFAULT NULL,
    `notified_on_effective_date` TINYINT(1) NOT NULL DEFAULT 1,
    PRIMARY KEY (`id`),
    INDEX ohrm_salary_record_history_notified_idx (`notified_on_effective_date`),
    CONSTRAINT `fk_salary_record_history_employee_number` FOREIGN KEY (`employee_number`) REFERENCES `hs_hr_employee` (`emp_number`) ON DELETE CASCADE,
    CONSTRAINT `fk_salary_record_history_event_id` FOREIGN KEY (`event_id`) REFERENCES `ohrm_pim_history_event` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_salary_record_history_pay_grade_id` FOREIGN KEY (`pay_grade_id`) REFERENCES `ohrm_pay_grade` (`id`) ON DELETE RESTRICT,
    CONSTRAINT `fk_salary_record_history_currency_id`FOREIGN KEY (`currency_id`) REFERENCES `hs_hr_currency_type` (`currency_id`) ON DELETE RESTRICT,
    CONSTRAINT fk_salary_record_history_eeo_paygrade FOREIGN KEY (`eeo_paygrade`) REFERENCES `ohrm_eeo_pay_grade` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS `ohrm_employee_salary_component_history` (
    `id` INT AUTO_INCREMENT,
    `salary_record_history_id` INT NOT NULL,
    `salary_component_history_id` INT UNSIGNED NOT NULL,
    `value` VARCHAR(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT '',
    `amount` VARCHAR(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT '',
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_employee_salary_component_history_salary_history_id` FOREIGN KEY (`salary_record_history_id`) REFERENCES `ohrm_salary_record_history` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_employee_salary_component_history_component_history_id` FOREIGN KEY (`salary_component_history_id`) REFERENCES `ohrm_salary_component_history` (`history_id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS `ohrm_direct_deposit_record` (
    `id` INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    `employee_number` INT(7) NOT NULL,
    `amount` VARCHAR(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '',
    `account_number` VARCHAR(100),
    `institute_name` VARCHAR(255),
    `account_type` VARCHAR(20),
    `branch_location` VARCHAR(255),
    `routing_number` VARCHAR(255),
    CONSTRAINT FOREIGN KEY (`employee_number`) REFERENCES `hs_hr_employee` (`emp_number`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;


-- Improve performance when joined with other tables (MariaDB 10.11 compatibility)
CREATE OR REPLACE VIEW `ohrm_job_record` AS
SELECT
  H.`history_id`, H.`employee_number`, H.`effective_date`, H.`event_id`,
  H.`job_title_id`, H.`employment_status_id`, H.`job_category_id`, H.`joined_date`,
  H.`probation_end_date`, H.`date_of_permanency`, H.`subunit_id`, H.`location_id`,
  H.`has_contract_details`, H.`contract_start_date`, H.`contract_end_date`, H.`cost_centre_id`,
  H.`eeo_job_category`, H.`flsa_exempt`, H.`work_schedule_id`, H.`pay_policy_id`,
  H.`event_name`, H.`comment`
FROM (
  SELECT
    -- Rank based on `effective_date` and `id`
    ROW_NUMBER() OVER (PARTITION BY HI.`employee_number` ORDER BY
      IFNULL(HI.`effective_date`, STR_TO_DATE('1970-01-01', '%Y-%m-%d')) DESC,
      HI.`id` DESC
    ) AS `rank`,
    HI.`id` AS `history_id`, HI.`employee_number`, HI.`effective_date`, HI.`event_id`,
    HI.`job_title_id`, HI.`employment_status_id`, HI.`job_category_id`, HI.`joined_date`,
    HI.`probation_end_date`, HI.`date_of_permanency`, HI.`subunit_id`, HI.`location_id`,
    HI.`has_contract_details`, HI.`contract_start_date`, HI.`contract_end_date`, HI.`cost_centre_id`,
    HI.`eeo_job_category`, HI.`flsa_exempt`, HI.`work_schedule_id`, HI.`pay_policy_id`,
    E.`name` AS `event_name`, HI.`comment`
  FROM `ohrm_job_record_history` HI
  LEFT JOIN `ohrm_pim_history_event` E ON (E.`id` = HI.`event_id`)
  WHERE ((HI.`effective_date` IS NULL) OR (HI.`effective_date` <= CURDATE()))
) H
-- Filter records with highest rank
WHERE (H.`rank` = 1);


-- Improve performance when joined with other tables (MariaDB 10.11 compatibility)
CREATE OR REPLACE VIEW `ohrm_salary_record` AS
SELECT
  H.`history_id`, H.`employee_number`, H.`effective_date`, H.`event_id`,
  H.`pay_grade_id`, H.`currency_id`, H.`eeo_paygrade`,
  H.`event_name`, H.`comment`
FROM (
  SELECT
    -- Rank based on `effective_date` and `id`
    ROW_NUMBER() OVER (PARTITION BY HI.`employee_number` ORDER BY
      IFNULL(HI.`effective_date`, STR_TO_DATE('1970-01-01', '%Y-%m-%d')) DESC,
      HI.`id` DESC
    ) AS `rank`,
    HI.`id` AS `history_id`, HI.`employee_number`, HI.`effective_date`, HI.`event_id`,
    HI.`pay_grade_id`, HI.`currency_id`, HI.`eeo_paygrade`,
    E.`name` AS `event_name`, HI.`comment`
  FROM `ohrm_salary_record_history` HI
  LEFT JOIN `ohrm_pim_history_event` E ON (E.`id` = HI.`event_id`)
  WHERE ((HI.`effective_date` IS NULL) OR (HI.`effective_date` <= CURDATE()))
) H
-- Filter records with highest rank
WHERE (H.`rank` = 1);


Drop view if EXISTS  ohrm_job_salary_event_dates ;

CREATE VIEW ohrm_job_salary_event_dates AS
SELECT employee_number, effective_date FROM `ohrm_salary_record_history` where effective_date IS NOT NULL
        UNION
        SELECT employee_number, effective_date FROM `ohrm_job_record_history` where effective_date IS NOT NULL;

INSERT INTO `ohrm_screen` (`name` ,`module_id` ,`action_url`)VALUES
( 'View Salary Components', '2', 'viewSalaryComponentList');
SET @screen_id := (SELECT LAST_INSERT_ID());
INSERT INTO `ohrm_user_role_screen`
(`user_role_id` ,`screen_id` ,`can_read` ,`can_create` ,`can_update` ,`can_delete`)VALUES
( '1', @screen_id, '1', '1', '1', '1');

SET @people_management_pillar_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'People Management' AND `level` = 1);
SET @hr_administration_module_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'HR Administration'
    AND `level` = 2 AND parent_id = @people_management_pillar_menu_id);
SET @job_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'Job' AND `level` = 3 AND `parent_id` = @hr_administration_module_menu_id);

INSERT INTO `ohrm_menu_item` ( `menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`,`image_name`, `uisref`, `status`)
VALUES( 'Manage Salary Components', @screen_id, @job_menu_id, 4, 100, NULL,NULL,'admin.salary_components', 1);

SET @action_based_notification_type := (SELECT id FROM ohrm_notification_type where `name`='Action Based');
SET @date_based_notification_type := (SELECT id FROM ohrm_notification_type where `name`='Date Based');
SET @email_media := (SELECT id FROM ohrm_notification_media WHERE `name` = 'Email');
SET @pim_module := (SELECT `id` FROM `ohrm_module` WHERE  `name` ='pim');

INSERT INTO `ohrm_notification`
    (`name`, `is_enable`,`type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`,`module_id`) VALUES
    ('Employee Termination', 0, @action_based_notification_type, 'LegacyEmailProcessorWrapperEventNotification', '{ "email_processor": "EmployeeTerminationEmailProcessor", "auxiliaryTokens": [ { "id": "SubscriberName", "name": "SubscriberName", "display_name": "Subscriber Name", "section": "", "tab_name": "Employee Termination", "value_getters": "" }, { "id": "TerminationEffectiveDate", "name": "TerminationEffectiveDate", "display_name": "Termination Effective Date", "section": "", "tab_name": "Employee Termination", "value_getters": "" }, { "id": "AdjustLeaveBalanceNotice", "name": "AdjustLeaveBalanceNotice", "display_name": "Adjust Leave Balance Notice", "section": "", "tab_name": "Employee Termination", "value_getters": "" }, { "id": "EmployeeName", "name": "EmployeeName", "display_name": "Employee Name", "section": "", "tab_name": "Employee Termination", "value_getters": "" } ] }', NULL, NULL,@pim_module ),
    ('Employee Termination Scheduled', 0, @action_based_notification_type, 'LegacyEmailProcessorWrapperEventNotification', '{ "email_processor": "EmployeeTerminationEmailProcessor", "notifyAffectedUserOptionVisible": true, "auxiliaryTokens": [ { "id": "SubscriberName", "name": "SubscriberName", "display_name": "Subscriber Name", "section": "", "tab_name": "Employee Termination Scheduled", "value_getters": "" }, { "id": "TerminationEffectiveDate", "name": "TerminationEffectiveDate", "display_name": "Termination Effective Date", "section": "", "tab_name": "Employee Termination Scheduled", "value_getters": "" }, { "id": "AdjustLeaveBalanceNotice", "name": "AdjustLeaveBalanceNotice", "display_name": "Adjust Leave Balance Notice", "section": "", "tab_name": "Employee Termination Scheduled", "value_getters": "" }, { "id": "EmployeeName", "name": "EmployeeName", "display_name": "Employee Name", "section": "", "tab_name": "Employee Termination Scheduled", "value_getters": "" } ] }', NULL, NULL,@pim_module ),
    ('Employee Termination Reminder', 0, @date_based_notification_type, 'TerminationReminderEventNotification', '{ "entity": "EmployeeTerminationRecord", "attribute": "date", "employeeJoinAttribute": "empNumber", "dateFormat": "YYYY-MM-DD", "employeeReference": "foreign", "conditions": [ "reactivated != 1" ], "auxiliaryTokens": [ { "id": "SubscriberName", "name": "SubscriberName", "display_name": "Subscriber Name", "section": "", "tab_name": "Employee Termination Reminder", "value_getters": "" }, { "id": "TerminationEffectiveDate", "name": "TerminationEffectiveDate", "display_name": "Termination Effective Date", "section": "", "tab_name": "Employee Termination Reminder", "value_getters": "" }, { "id": "AdjustLeaveBalanceNotice", "name": "AdjustLeaveBalanceNotice", "display_name": "Adjust Leave Balance Notice", "section": "", "tab_name": "Employee Termination Reminder", "value_getters": "" }, { "id": "EmployeeName", "name": "EmployeeName", "display_name": "Employee Name", "section": "", "tab_name": "Employee Termination Reminder", "value_getters": "" } ] }', NULL, NULL,@pim_module );
SET @termination_notification_id := (SELECT `id` FROM `ohrm_notification` WHERE  `name` ='Employee Termination');
SET @termination_scheduled_notification_id := (SELECT `id` FROM `ohrm_notification` WHERE  `name` ='Employee Termination Scheduled');
SET @termination_reminder_notification_id := (SELECT `id` FROM `ohrm_notification` WHERE  `name` ='Employee Termination Reminder');

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

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
(@termination_notification_id, @email_media, 'en_US',
'Employee Termination',
'Employment Termination Notification',
'<p>Hi %SubscriberName%,<br /><br />%EmployeeName%''s employment has been terminated on %TerminationEffectiveDate%.<br />%AdjustLeaveBalanceNotice%<br />Thank you<br />(This is an auto-generated email, please do not reply.)</p>'),
(@termination_scheduled_notification_id, @email_media, 'en_US',
'Employee Termination Scheduled',
'Employment Termination Scheduled Notification',
'<p>Hi %SubscriberName%,<br /><br />%EmployeeName%''s employment termination is scheduled on %TerminationEffectiveDate%.<br />%AdjustLeaveBalanceNotice%<br />Thank you<br />(This is an auto-generated email, please do not reply.)</p>'),
(@termination_reminder_notification_id, @email_media, 'en_US',
'Employee Termination Reminder',
'Employment Termination Reminder for %EmployeeName%',
'<p>Hi %SubscriberName%,<br /><br />Please note that %EmployeeName%''s employment will be terminated on %TerminationEffectiveDate%.<br />%AdjustLeaveBalanceNotice%<br />Thank you<br />(This is an auto-generated email, please do not reply.)</p>');

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id) SELECT @termination_notification_id, id FROM ohrm_workflow_state_machine WHERE `workflow` = '3' AND `action` = '3';

INSERT INTO `ohrm_notification_subscription`
(notification_id, workflow_item_id) SELECT @termination_scheduled_notification_id, id FROM ohrm_workflow_state_machine WHERE `workflow` = '3' AND `action` = '6';

INSERT INTO `ohrm_event_listener` (`event`, `listener_class`, `enabled`, `position`) VALUES
('pim.job_history_delete', 'JobRecordHistoryDeleteAuditListener', 1, 1),
('pim.salary_history_delete', 'SalaryRecordHistoryDeleteAuditListener', 1, 1);

SET @job_and_salary_history_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'pim.Job_And_Salary_History_Report');
SET @salary_events_report := (SELECT id FROM ohrm_advanced_report WHERE unique_name = 'pim.Salary_Events_Report');

INSERT INTO ohrm_report_configuration (report_id, configuration) VALUES
(@job_and_salary_history_report, '{"selectedDisplayFieldGroups":["1","7_1"],"selectedDisplayFields":["jsh_1","jh_1","jh_2","jh_5","jh_7","sh_1","sh_2","sh_3","sh_4","sh_5","sh_6","jh_6","jh_3","jh_4","jh_8","jh_9","jh_10","jh_11","jh_12","jh_13","jh_14","jh_15","jh_16","jh_17","9","10","11","h_comp_1","jh_18","sh_12","sh_9","sh_10","sh_11","sh_13"],"selectedFilterCriteria":{"include":"1","show_only_filtered_records":true,"employee_name":"","job_salary_history_employment_status":"","job_salary_history_sub_unit":"","job_salary_history_location":"","job_salary_history_job_title":"","job_salary_history_event_date_dynamic_date_group":{"job_salary_history_event_date_dynamic_date_group":"job_salary_history_event_date_date_more_custom","job_salary_history_event_date_date_more_custom":"","job_salary_history_event_date_date_more_today":"1","job_salary_history_event_date_date_more_from_field":""},"job_salary_history_effective_date_dynamic_single_date":{"job_salary_history_effective_date_dynamic_single_date":"job_salary_history_effective_date_single_date_custom","job_salary_history_effective_date_single_date_custom":"","job_salary_history_effective_date_single_date_today":"1","job_salary_history_effective_date_single_date_from_field":""}}}'),
(@salary_events_report, '{"selectedDisplayFieldGroups":["1","73"],"selectedDisplayFields":["9","10","11","se_1","se_2","se_3","se_4","se_5"],"selectedFilterCriteria":{"include":"1","show_only_filtered_records":true,"employee_name":"","employment_status":"","job_title":"","job_category":"","sub_unit":"","salary_event_effective_date_dynamic_date_group":{"salary_event_effective_date_dynamic_date_group":"salary_event_effective_date_date_more_custom","salary_event_effective_date_date_more_custom":"","salary_event_effective_date_date_more_today":"1","salary_event_effective_date_date_more_from_field":""},"salary_event":""}}');

INSERT INTO ohrm_report_share (report_id, role_type_id) VALUES
(@job_and_salary_history_report, 1),
(@salary_events_report, 1);

CREATE TABLE IF NOT EXISTS `ohrm_token_salary_component` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `token_id` INT(11) NOT NULL,
    `salary_component_id` INT UNSIGNED NOT NULL,
    `token_salary_component_value_type` TINYINT UNSIGNED NOT NULL DEFAULT 1,
    CONSTRAINT `fk_token_salary_component_token` FOREIGN KEY (`token_id`) REFERENCES `ohrm_token` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_token_salary_component_salary_component` FOREIGN KEY (`salary_component_id`) REFERENCES `ohrm_salary_component` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS `ohrm_salary_summary_field` (
    `id` INT UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
    `name` VARCHAR(50) NOT NULL UNIQUE,
    `description` VARCHAR(255) NOT NULL,
    `display_name` VARCHAR(255) NOT NULL,
    `enabled` TINYINT UNSIGNED NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

INSERT INTO `ohrm_salary_summary_field`
VALUES (1, 'EmpGrossPay', 'Base Pay + Total Earnings', 'Gross Pay', 1),
       (2, 'EmpTotalDeductions', 'Total Deductions', 'Total Deductions', 1),
       (3, 'EmpCostToTheCompany', 'Base Pay + Earnings + Other Employer Contributions', 'Cost to the Company', 1),
       (4, 'EmpTotalPayable', 'Base Pay + Total Earnings - Total Deductions', 'Total Payable', 1);