
-- Adding new Maintenance module
INSERT INTO `ohrm_module` (`name`, `status`) VALUES ('Maintenance', '1');
SET @maintenance_module := (SELECT `id` FROM `ohrm_module` WHERE `name` = 'Maintenance');

SET @config_admin_user_role_id := (SELECT id FROM `ohrm_user_role` WHERE `name` = 'ConfigAdmin');
SET @admin_user_role_id := (SELECT id FROM `ohrm_user_role` WHERE `name` = 'Admin');
SET @admin_user_role_type_id := (SELECT id FROM `ohrm_user_role_type` WHERE `name` = 'Admin');


-- adding maintenance configuration screen only sys admin can access
INSERT INTO `ohrm_screen` (`name`, `module_id`, `action_url`) VALUES('maintenanceConfiguration', @maintenance_module, 'maintenanceConfiguration');
SET @maintenance_configuration_screen_id := (SELECT id FROM ohrm_screen WHERE `name` = 'maintenanceConfiguration');

INSERT INTO `ohrm_user_role_screen` (`user_role_id`, `screen_id`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES (@config_admin_user_role_id, @maintenance_configuration_screen_id, 1, 1, 1, 1);

-- adding maintenance purge employee screen admin can access
INSERT INTO `ohrm_screen` (`name`, `module_id`, `action_url`) VALUES('maintenancePurgeEmployeeRecords', @maintenance_module, 'maintenancePurgeEmployeeRecords');
SET @maintenance_purge_employee_records_screen_id := (SELECT id FROM ohrm_screen WHERE `name` = 'maintenancePurgeEmployeeRecords');

INSERT INTO `ohrm_user_role_screen` (`user_role_id`, `screen_id`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES (@admin_user_role_id, @maintenance_purge_employee_records_screen_id, 1, 1, 1, 1);


-- adding maintenance related data groups for configuration screen and purge employee screen
INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES ('maintenance_configuration', 'Maintenance - Configuration', 1, 1, 1, 1);
SET @maintenance_configuration_data_group_id := (SELECT id FROM `ohrm_data_group` WHERE `name` = 'maintenance_configuration');

INSERT INTO `ohrm_user_role_data_group` (`user_role_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`, `self`) VALUES
  (@config_admin_user_role_id, @maintenance_configuration_data_group_id, 1, 1, 1, 1, 0);

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES ('maintenance_purge_employee_records', 'Employee Management - Purge Employee Records', 1, NULL, NULL, 1);
SET @maintenance_purge_employee_records_data_group_id := (SELECT id FROM `ohrm_data_group` WHERE `name` = 'maintenance_purge_employee_records');

INSERT INTO `ohrm_data_group_screen` (`data_group_id`, `screen_id`, `permission`, `self`) VALUES (@maintenance_purge_employee_records_data_group_id, @maintenance_purge_employee_records_screen_id, 1, 0);

INSERT INTO `ohrm_user_role_data_group` (`user_role_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`, `self`) VALUES (@admin_user_role_id, @maintenance_purge_employee_records_data_group_id, 1, NULL, NULL, 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 (@admin_user_role_type_id, @maintenance_purge_employee_records_data_group_id, 1, NULL, NULL, 1);

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 @employee_management_module_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'Employee Management' AND parent_id = @people_management_pillar_menu_id AND level = 2);

-- adding menu items
INSERT INTO `ohrm_menu_item` (`id`, `menu_title`, `menu_category`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `image_name`, `uisref`, `status`, `include_attachment_section`) VALUES
(NULL, 'Purge Records', NULL, NULL, @employee_management_module_menu_id, 3, 1500, NULL, NULL, NULL, 1, NULL);
SET @purge_records_menu_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_menu_item` (`id`, `menu_title`, `menu_category`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `image_name`, `uisref`, `status`, `include_attachment_section`) VALUES
(NULL, 'Configuration', NULL, @maintenance_configuration_screen_id, @purge_records_menu_id, 4, 100, NULL, NULL, 'maintenance.configuration', '1', NULL);

INSERT INTO `ohrm_menu_item` (`id`, `menu_title`, `menu_category`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `image_name`, `uisref`, `status`, `include_attachment_section`)
VALUES (NULL, 'Employee Records', NULL, @maintenance_purge_employee_records_screen_id, @purge_records_menu_id, 4, 200, NULL, NULL, 'maintenance.purge_employee_records', '0', NULL);
SET @maintenance_purge_employee_records_menu_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_menu_item_data_group` (`menu_item_id`, `data_group_id`) VALUES (@maintenance_purge_employee_records_menu_id, @maintenance_purge_employee_records_data_group_id);


-- adding default maintenance configuration
INSERT INTO `ohrm_config` (`property`, `value`) VALUES ('maintenance.configurations', '{\"enableMaintenanceModule\": false}');


-- adding maintenance related audit trail/section/action/listeners
DROP TABLE IF EXISTS `ohrm_audittrail_purge_employee_records_trail`;
CREATE TABLE `ohrm_audittrail_purge_employee_records_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_purge_employee_records_trail_idx_app_id (app_id),
  INDEX audit_purge_employee_records_trail_idx_screen_name (screen_name)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;


SET @employee_management_audit_trail_module_id := (SELECT `id` FROM `ohrm_audittrail_module` WHERE `name`='Employee Management');

INSERT INTO `ohrm_audittrail_section` (`name`, `description`, `affected_entity`, `module_id`, `is_deleted`) VALUES ('PurgeEmployeeRecords', 'Purge Employee Records', 'Employee', @employee_management_audit_trail_module_id, '0');
SET @maintenance_audit_trail_section := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name` = 'PurgeEmployeeRecords' AND `module_id` = @employee_management_audit_trail_module_id);

INSERT INTO `ohrm_audittrail_section_actions` (`section_id`, `action`, `description`) VALUES (@maintenance_audit_trail_section, 'PURGE EMPLOYEE', 'Purge Employee');

INSERT INTO `ohrm_event_listener` (`id`, `event`, `listener_class`, `enabled`, `position`) VALUES (NULL, 'maintenance.purge_employee', 'MaintenancePurgeEmployeeListener', '1', '82');


INSERT INTO `ohrm_screen` (`name`, `module_id`, `action_url`) VALUES('maintenancePurgeCandidateRecords', @maintenance_module, 'maintenancePurgeCandidateRecords');
SET @maintenance_purge_candidate_records_screen_id := (SELECT id FROM ohrm_screen WHERE `name` = 'maintenancePurgeCandidateRecords');

INSERT INTO `ohrm_user_role_screen` (`user_role_id`, `screen_id`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES (@admin_user_role_id, @maintenance_purge_candidate_records_screen_id, 1, 1, 1, 1);

INSERT INTO `ohrm_menu_item` (`id`, `menu_title`, `menu_category`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `image_name`, `uisref`, `status`, `include_attachment_section`) VALUES
(NULL, 'Candidate Records', NULL, @maintenance_purge_candidate_records_screen_id, @purge_records_menu_id, 4, 300, NULL, NULL, 'maintenance.purge_candidate_records', 0, NULL);

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES ('maintenance_purge_candidate_records', 'Employee Management - Purge Candidate Records', 1, NULL, NULL, 1);
SET @maintenance_purge_candidate_records_data_group_id := (SELECT id FROM `ohrm_data_group` WHERE `name` = 'maintenance_purge_candidate_records');


INSERT INTO `ohrm_data_group_screen` (`data_group_id`, `screen_id`, `permission`, `self`) VALUES (@maintenance_purge_candidate_records_data_group_id, @maintenance_purge_candidate_records_screen_id, 1, 0);

INSERT INTO `ohrm_user_role_data_group` (`user_role_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`, `self`) VALUES (@admin_user_role_id, @maintenance_purge_candidate_records_data_group_id, 1, NULL, NULL, 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 (@admin_user_role_type_id, @maintenance_purge_candidate_records_data_group_id, 1, NULL, NULL, 1);

INSERT INTO `ohrm_menu_item_data_group` (`menu_item_id`, `data_group_id`) VALUES (@maintenance_purge_employee_records_menu_id, @maintenance_purge_candidate_records_data_group_id);


-- adding maintenance candidate related audit trail/section/action/listeners
DROP TABLE IF EXISTS `ohrm_audittrail_purge_candidate_records_trail`;
CREATE TABLE `ohrm_audittrail_purge_candidate_records_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_purge_candidate_records_trail_idx_app_id (app_id),
  INDEX audit_purge_candidate_records_trail_idx_screen_name (screen_name)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `ohrm_audittrail_section` (`name`, `description`, `affected_entity`, `module_id`, `is_deleted`) VALUES ('PurgeCandidateRecords', 'Purge Candidate Records', 'Job Vacancy', @employee_management_audit_trail_module_id, '0');
SET @maintenance_candidate_audit_trail_section := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name` = 'PurgeCandidateRecords' AND `module_id` = @employee_management_audit_trail_module_id);

INSERT INTO `ohrm_audittrail_section_actions` (`section_id`, `action`, `description`) VALUES (@maintenance_candidate_audit_trail_section, 'PURGE CANDIDATES', 'Purge Candidates');

INSERT INTO `ohrm_event_listener` (`id`, `event`, `listener_class`, `enabled`, `position`) VALUES (NULL, 'maintenance.purge_candidate', 'MaintenancePurgeCandidateListener', '1', '83');

INSERT INTO `ohrm_config` (`property`, `value`) VALUES ('maintenance.log_archive_rollover_thresholds', '{\"default\":\"-1 months\",\"overrides\":{\"sent_emails.log\":\"-2 months\"}}');

INSERT INTO `ohrm_async_event` (`name`, `display_name`, `group_name`, `enabled`, `position`)
VALUES ('maintenance.purge_employee', 'Purge Employee', 'maintenance', 1, 10);

SET @ohrm_data_group_maintenance_purge_candidate_records_id =  (SELECT `id`  FROM `ohrm_data_group` WHERE `name` = 'maintenance_purge_candidate_records');
SET @ohrm_data_group_maintenance_purge_employee_records_id =  (SELECT `id`  FROM `ohrm_data_group` WHERE `name` = 'maintenance_purge_employee_records');

SET @ohrm_audittrail_section_purge_candidate_records = (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='PurgeCandidateRecords');
SET @ohrm_audittrail_section_purge_employee_records = (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='PurgeEmployeeRecords');

INSERT INTO `ohrm_datagroup_audittrail_section` (`audit_section_id`, `datagroup_id`, `consider_in_notification`)
VALUES
    (@ohrm_audittrail_section_purge_candidate_records, @ohrm_data_group_maintenance_purge_candidate_records_id, 0),
    (@ohrm_audittrail_section_purge_employee_records, @ohrm_data_group_maintenance_purge_employee_records_id, 0);
