ALTER TABLE `ohrm_job_vacancy` ADD COLUMN `request_consent`  TINYINT(1) DEFAULT '0' NOT NULL;
ALTER TABLE `ohrm_job_candidate` ADD `purged_at` TIMESTAMP NULL DEFAULT NULL;

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');
SET @old_purge_records_ststus := (SELECT `status` FROM `ohrm_menu_item` WHERE `menu_title`='Purge Records');

UPDATE `ohrm_data_group` SET `description`='Maintenance - Purge Employee Records' WHERE `name`='maintenance_purge_employee_records';

UPDATE `ohrm_menu_item` SET `screen_id`= NULL WHERE `menu_title`='Purge Records';
UPDATE `ohrm_menu_item` SET `uisref`= NULL WHERE `menu_title`='Purge Records';
UPDATE `ohrm_menu_item` SET `status`= '1' WHERE `menu_title`='Purge Records';

SET @maintenance_menu_id := (SELECT id FROM `ohrm_menu_item` WHERE `menu_title` = 'Maintenance');
SET @maintenance_purge_records_menu_id := (SELECT id FROM `ohrm_menu_item` WHERE `menu_title` = 'Purge Records' AND `parent_id` = @maintenance_menu_id);
SET @maintenance_purge_employee_records_menu_id := (SELECT id FROM `ohrm_menu_item` WHERE `menu_title` = 'Employee Records' AND `parent_id` = @maintenance_purge_records_menu_id);
SET @maintenance_purge_employee_records_screen_id := (SELECT id FROM ohrm_screen WHERE `name` = 'maintenancePurgeEmployeeRecords');

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, @maintenance_purge_records_menu_id, '3', '4003', NULL, NULL, 'maintenance.purge_employee_records', @old_purge_records_ststus, NULL);
SET @maintenance_purge_employee_records_menu_id := (SELECT id FROM `ohrm_menu_item` WHERE `menu_title` = 'Employee Records' AND `parent_id` = @maintenance_purge_records_menu_id);


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, @maintenance_purge_records_menu_id, '3', '4004', NULL, NULL, 'maintenance.purge_candidate_records', @old_purge_records_ststus, NULL);
SET @maintenance_purge_candidate_records_menu_id := (SELECT id FROM `ohrm_menu_item` WHERE `menu_title` = 'Candidate Records' AND `parent_id` = @maintenance_purge_records_menu_id);

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES ('maintenance_purge_candidate_records', 'Maintenance - 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
CREATE TABLE `ohrm_audittrail_maintenance_candidate_trail` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `action_time` TIMESTAMP 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,
  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET @audittrail_module_id := (SELECT `id` FROM `ohrm_audittrail_module` WHERE `name` = 'Maintenance');

INSERT INTO `ohrm_audittrail_section` (`name`, `description`, `affected_entity`, `module_id`, `is_deleted`) VALUES ('MaintenanceCandidate', 'Purge Candidate Records', 'Job Vacancy', @audittrail_module_id, '0');
SET @maintenance_candidate_audit_trail_section := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name` = 'MaintenanceCandidate' AND `module_id` = @audittrail_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');