INSERT INTO `ohrm_audittrail_module` (`name`) VALUES ('Request Desk');
SET @requests_module_id := (SELECT `id` FROM `ohrm_audittrail_module` WHERE `name`='Request Desk');

INSERT INTO `ohrm_audittrail_section` (`name`, `description`, `affected_entity`, `module_id`) VALUES
('RequestTypes', 'Request Types', 'Request Type', @requests_module_id);
INSERT INTO `ohrm_audittrail_section` (`name`, `description`, `affected_entity`, `module_id`) VALUES
('RequestDetails', 'Request Details', 'Request', @requests_module_id);

INSERT INTO `ohrm_audittrail_section` (`name`, `description`, `affected_entity`, `module_id`) VALUES
('RequestsCustomField', 'Request Types - Custom Field', 'Custom Field', @requests_module_id);



SET @request_details_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='RequestDetails');
SET @user_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='SystemUser');
SET @request_type_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='RequestTypes');
SET @custom_field_configure_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='RequestsCustomField');

SET @request_details_datagroup_id := (SELECT `id` FROM `ohrm_data_group` WHERE `name`='employee_requests');

INSERT INTO `ohrm_datagroup_audittrail_section`(`audit_section_id`,`datagroup_id`,`consider_in_notification`) VALUES
(@request_details_section_id,@request_details_datagroup_id,1);

INSERT INTO `ohrm_audittrail_section_actions` (`section_id`, `action`, `description`) VALUES
(@request_details_section_id, 'UPDATE REQUEST', 'Update Request'),
(@request_details_section_id, 'DELETE REQUEST', 'Delete Request'),
(@request_details_section_id, 'UPDATE CUSTOM FIELD VALUE', 'Update Custom Field Value'),
(@request_details_section_id, 'SUBMIT REQUEST', 'Submit Request'),
(@request_details_section_id, 'WITHDRAW REQUEST', 'Withdraw Request'),
(@request_details_section_id, 'APPROVE REQUEST', 'Approve Request'),
(@request_details_section_id, 'INFO REQUEST', 'Info Request'),
(@request_details_section_id, 'RESOLVE REQUEST', 'Resolve Request'),
(@request_details_section_id, 'REOPEN REQUEST', 'Reopen Request'),
(@request_details_section_id, 'ARCHIVE REQUEST', 'Archive Request'),
(@request_details_section_id, 'UNARCHIVE REQUEST', 'Unarchive Request'),
(@request_details_section_id, 'REJECT REQUEST', 'Reject Request'),
(@request_type_section_id, 'UPDATE REQUEST TYPE', 'Update Request Type'),
(@request_type_section_id, 'ADD REQUEST TYPE', 'Add Request Type'),
(@request_type_section_id, 'DELETE REQUEST TYPE', 'Delete Request Type'),
(@custom_field_configure_section_id, 'DELETE CUSTOM FIELD', 'Delete Custom Field'),
(@custom_field_configure_section_id, 'UPDATE CUSTOM FIELD', 'Update Custom Field'),
(@custom_field_configure_section_id, 'ADD CUSTOM FIELD', 'Add Custom Field');


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

SET @module_id := (SELECT id FROM `ohrm_module` WHERE `name` LIKE 'audittrail' );

DROP TABLE IF EXISTS `ohrm_audittrail_request_details_trail`;
CREATE TABLE `ohrm_audittrail_request_details_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` TEXT CHARSET UTF8,
  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;


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

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `ohrm_audittrail_request_custom_field_trail`;
CREATE TABLE `ohrm_audittrail_request_custom_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` TEXT CHARSET UTF8,
  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;


SET @max_event_listener_position := (SELECT MAX(`position`)FROM ohrm_event_listener);
INSERT INTO `ohrm_event_listener` (`event`, `listener_class`, `enabled`, `position`) VALUES
    ('requestTracker.generate_documents', 'RequestDeskGenerateDocumentsAuditListener', '1', @max_event_listener_position+1);

CREATE TABLE IF NOT EXISTS `ohrm_audittrail_request_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_request_created_documents_trail_idx_app_id (app_id),
     INDEX audit_request_created_documents_trail_idx_screen_name (screen_name)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET @audit_request_desk_module_id := (SELECT `id` FROM `ohrm_audittrail_module` WHERE `name`='Request Desk');
INSERT INTO `ohrm_audittrail_section` (`name`, `description`, `affected_entity`, `module_id`) VALUES
    ('RequestCreatedDocuments', 'Documents - Emails', 'Request', @audit_request_desk_module_id);

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

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

SET @ohrm_data_group_employee_request_id =  (SELECT `id`  FROM `ohrm_data_group` WHERE `name` = 'employee_requests');

INSERT INTO `ohrm_datagroup_audittrail_section` (`audit_section_id`, `datagroup_id`, `consider_in_notification`)
VALUES (@request_create_documents_audit_section_id, @ohrm_data_group_employee_request_id, 0);