DROP TABLE IF EXISTS `ohrm_audittrail_leave_holiday_trail`;
CREATE TABLE `ohrm_audittrail_leave_holiday_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) NOT NULL,
  `version_id` INT NOT NULL DEFAULT 0,
  `action` varchar(255) NOT NULL,
  `affected_entity_id` VARCHAR(20),
  `affected_entity_name` TEXT,
  `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_leave_holiday_trail_idx_app_id (app_id),
  INDEX audit_leave_holiday_trail_idx_screen_name (screen_name)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `ohrm_audittrail_leave_types_trail`;
CREATE TABLE `ohrm_audittrail_leave_types_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) NOT NULL,
  `version_id` INT NOT NULL DEFAULT 0,
  `action` varchar(255) NOT NULL,
  `affected_entity_id` VARCHAR(20),
  `affected_entity_name` TEXT,
  `action_description` LONGTEXT CHARSET UTF8,
  `app_id` INT DEFAULT NULL,
  `app_name` VARCHAR(1000) DEFAULT NULL,
  `screen_name` VARCHAR(1000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX audit_leave_types_trail_idx_app_id (app_id),
  INDEX audit_leave_types_trail_idx_screen_name (screen_name)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `ohrm_audittrail_leave_entitlement_trail`;
CREATE TABLE `ohrm_audittrail_leave_entitlement_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) NOT NULL,
  `version_id` INT NOT NULL DEFAULT 0,
  `action` varchar(255) NOT NULL,
  `affected_entity_id` VARCHAR(20),
  `affected_entity_name` TEXT,
  `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_leave_entitlement_trail_idx_app_id (app_id),
  INDEX audit_leave_entitlement_trail_idx_screen_name (screen_name)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `ohrm_audittrail_leave_request_trail`;
CREATE TABLE `ohrm_audittrail_leave_request_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) NOT NULL,
  `version_id` INT NOT NULL DEFAULT 0,
  `action` varchar(255) NOT NULL,
  `affected_entity_id` VARCHAR(20),
  `affected_entity_name` TEXT,
  `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_leave_request_trail_idx_app_id (app_id),
  INDEX audit_leave_request_trail_idx_screen_name (screen_name)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `ohrm_audittrail_leave_period_trail`;
CREATE TABLE `ohrm_audittrail_leave_period_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) NOT NULL,
  `version_id` INT NOT NULL DEFAULT 0,
  `action` varchar(255) NOT NULL,
  `affected_entity_id` VARCHAR(20),
  `affected_entity_name` TEXT,
  `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_leave_period_trail_idx_app_id (app_id),
  INDEX audit_leave_period_trail_idx_screen_name (screen_name)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `ohrm_audittrail_module` (`name`) VALUES
('Leave');

SET @leave_module_id := (SELECT `id` FROM `ohrm_audittrail_module` WHERE `name`='Leave');
INSERT INTO `ohrm_audittrail_section` (`name`, `description`, `affected_entity`, `module_id`) VALUES
('Holiday', 'Holiday', 'Holiday', @leave_module_id),
('LeaveEntitlement', 'Leave Entitlement', 'Employee', @leave_module_id),
('LeaveType', 'Leave Type', 'Leave Type', @leave_module_id),
('LeaveRequest', 'Leave Request', 'Employee', @leave_module_id),
('LeavePeriod', 'Leave Period', 'Leave Period', @leave_module_id);

SET @holiday_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='Holiday');
INSERT INTO `ohrm_audittrail_section_actions` (`section_id` ,`action` ,`description`) VALUES
(@holiday_section_id, 'ADD HOLIDAY', 'Add Holiday'),
(@holiday_section_id, 'DELETE HOLIDAY', 'Delete Holiday'),
(@holiday_section_id, 'UPDATE HOLIDAY', 'Update Holiday');

SET @leave_entitlement_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='LeaveEntitlement');
INSERT INTO `ohrm_audittrail_section_actions` (`section_id` ,`action` ,`description`) VALUES
(@leave_entitlement_section_id, 'ADD ENTITLEMENT', 'Add Entitlement'),
(@leave_entitlement_section_id, 'ADD ACCRUAL', 'Add Accrual'),
(@leave_entitlement_section_id, 'ADD ENTITLEMENT - BULK', 'Add Entitlement - Bulk'),
(@leave_entitlement_section_id, 'ADD BROUGHT FORWARD', 'Add Brought Forward'),
(@leave_entitlement_section_id, 'UPDATE ENTITLEMENT', 'Update Entitlement'),
(@leave_entitlement_section_id, 'DELETE ENTITLEMENT', 'Delete Entitlement'),
(@leave_entitlement_section_id, 'ADJUST BROUGHT FORWARD', 'Adjust Brought Forward');

SET @leave_request_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='LeaveRequest');
INSERT INTO `ohrm_audittrail_section_actions` (`section_id` ,`action` ,`description`) VALUES
(@leave_request_section_id, 'APPLY LEAVE', 'Apply Leave'),
(@leave_request_section_id, 'ASSIGN LEAVE ', 'Assign Leave'),
(@leave_request_section_id, 'CHANGE LEAVE STATUS', 'Change Leave Status'),
(@leave_request_section_id, 'CHANGE LEAVE DURATION', 'Change Leave Duration'),
(@leave_request_section_id, 'ADD LEAVE REQUEST COMMENT', 'Add Leave Request Comment'),
(@leave_request_section_id, 'ADD INDIVIDUAL LEAVE COMMENT', 'Add Individual Leave Comment');

SET @leave_type_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='LeaveType');
INSERT INTO `ohrm_audittrail_section_actions` (`section_id` ,`action` ,`description`) VALUES
(@leave_type_section_id, 'CREATE LEAVE TYPE', 'Create leave type'),
(@leave_type_section_id, 'UPDATE LEAVE TYPE', 'Update leave type'),
(@leave_type_section_id, 'DELETE LEAVE TYPE', 'Delete leave type'),
(@leave_type_section_id, 'REACTIVATE LEAVE TYPE', 'Reactivate leave type'),
(@leave_type_section_id, 'UPDATE RULES - WHO CAN APPLY', 'Update Rules - Who can apply'),
(@leave_type_section_id, 'UPDATE RULES - APPLY RESTRICTIONS', 'Update Rules - Apply Restrictions'),
(@leave_type_section_id, 'UPDATE RULES - ACCRUAL RULES', 'Update Rules - Accrual Rules'),
(@leave_type_section_id, 'UPDATE RULES - CARRY FORWARD RULES', 'Update Rules - Carry Forward Rules'),
(@leave_type_section_id, 'UPDATE RULES - GENERAL', 'Update Rules - General'),
(@leave_type_section_id, 'UPDATE RULES - EMPLOYEE GROUPS', 'Update Rules - Employee Groups');

SET @leave_period_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='LeavePeriod');
INSERT INTO `ohrm_audittrail_section_actions` (`section_id` ,`action` ,`description`) VALUES
(@leave_period_section_id, 'CONFIGURE LEAVE PERIOD', 'Configure Leave Period');

INSERT INTO `ohrm_event_listener` (`event`, `listener_class`, `enabled`, `position`) VALUES
('leave.entitlement_add', 'LeaveEntitlementAddListener', 1, 1),
('leave.entitlement_update', 'LeaveEntitlementUpdateListener', 1, 2),
('leave.entitlement_bulk_update', 'LeaveEntitlementBulkUpdateListener', 1, 3),
('leave.entitlement_delete', 'LeaveEntitlementDeleteListener', 1, 4),
('leave.entitlement_add_accrual', 'LeaveEntitlementAddAccrualListener', 1, 5),
('leave.entitlement_add_brought_forward', 'LeaveEntitlementBroughtForwardListener', 1, 6),
('leave.holiday_add', 'AdjustLeaveOnHolidayListener', 1, 6),
('leave.holiday_update', 'AdjustLeaveOnHolidayListener', 1, 6),
('leave.holiday_add', 'HolidayAddListener', 1, 7),
('leave.holiday_update', 'HolidayUpdateListener', 1, 8),
('leave.holiday_delete', 'HolidayDeleteListener', 1, 9),
('leave.assign', 'LeaveAssignListener', 1, 10),
('leave.change', 'LeaveRequestChangeStatusListener', 1, 11),
('leave.change_duration', 'LeaveChangeDurationListener', 1, 11),
('leave.leavetype_add', 'LeaveTypeAddListener', 1, 12),
('leave.leavetype_update', 'LeaveTypeUpdateListener', 1, 13),
('leave.leavetype_delete', 'LeaveTypeDeleteListener', 1, 14),
('leave.leavetype_updaterules_general', 'LeaveTypeUpdateGeneratRulesListener', 1, 16),
('leave.leavetype_updaterules_whocanapply', 'LeaveTypeUpdateWhoCanApplyRuleListener', 1, 17),
('leave.leavetype_updaterules_applyrestriction', 'LeaveTypeUpdateApplyRestrictionRuleListener', 1, 18),
('leave.leavetype_updaterules_accrualrules', 'LeaveTypeUpdateAccerualRuleListener', 1, 19),
('leave.leavetype_updaterules_carryforwardrules', 'LeaveTypeUpdateCarryForwardRuleListener', 1, 20),
('leave.leaverequest_add_leave_request_comment', 'LeaveRequestAddCommentListener', 1, 21),
('leave.apply', 'LeaveApplyListener', 1, 22),
('leave.leave_period_config', 'LeavePeriodConfigureListener', 1, 23),
('leave.leavetype_updaterules_employee_groups', 'LeaveTypeUpdateEmployeeGroupListener', 1, 37),
('leave.entitlement_adjust_brought_forward', 'LeaveBroughtForwardAdjustListener', 1, 38),
('admin.system_user.update_password', 'UpdatePasswordAuditListener', 1, 49),
('admin.system_user.delete_user', 'DeleteUserAuditListener', 1, 59),
('system_user_added', 'AddUserAuditListener', 1, 60),
('admin.system_user.update_user','UpdateUserAuditListener', 1, 60),
('leave.leave_accrual_time_schedule_change','LeaveAccrualTimeScheduleChangeListener', 1, 61);

SET @ohrm_data_group_leave_entitlements_id =  (SELECT `id`  FROM `ohrm_data_group` WHERE `name` = 'leave_entitlements');
SET @ohrm_data_group_holidays_id =  (SELECT `id`  FROM `ohrm_data_group` WHERE `name` = 'holidays');
SET @leave_types_data_group_id := (SELECT id FROM `ohrm_data_group` WHERE `name` = 'leave_types');
SET @leave_period_data_group_id := (SELECT id FROM `ohrm_data_group` WHERE `name` = 'leave_period');

SET @ohrm_audittrail_section_leave_entitlement = (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='LeaveEntitlement');
SET @ohrm_audittrail_section_holiday = (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='Holiday');
SET @leave_type_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='LeaveType');

INSERT INTO `ohrm_datagroup_audittrail_section` (`audit_section_id`, `datagroup_id`, `consider_in_notification`)
VALUES
    ( @ohrm_audittrail_section_leave_entitlement, @ohrm_data_group_leave_entitlements_id, 0),
    ( @ohrm_audittrail_section_holiday, @ohrm_data_group_holidays_id, 0),
    ( @leave_type_section_id, @leave_types_data_group_id, 0),
    ( @leave_period_section_id, @leave_period_data_group_id, 0);
