DELETE FROM `ohrm_leave_request_attachment`
  WHERE `leave_request_id` IN (
      SELECT `id` FROM `ohrm_leave_request`
      WHERE `emp_number` IN (
            SELECT `emp_number` FROM `hs_hr_employee` WHERE `purged_at` IS NOT NULL
        )
    );


DELETE FROM `ohrm_leave_request_comment`
  WHERE `leave_request_id` IN (
    SELECT `id` FROM `ohrm_leave_request`
    WHERE `emp_number` IN (
      SELECT `emp_number` FROM `hs_hr_employee` WHERE `purged_at` IS NOT NULL
    )
  );


UPDATE `ohrm_leave_request_comment`
JOIN `ohrm_user` ON `ohrm_leave_request_comment`.`created_by_id` = `ohrm_user`.`id`
JOIN `hs_hr_employee` ON `ohrm_user`.`emp_number` = `hs_hr_employee`.`emp_number` AND `hs_hr_employee`.`purged_at` IS NOT NULL
SET `ohrm_leave_request_comment`.`created_by_name` = concat(`hs_hr_employee`.`emp_firstname`,' ', `hs_hr_employee`.`emp_lastname`);


UPDATE `ohrm_leave_request_comment`
JOIN `hs_hr_employee` ON `ohrm_leave_request_comment`.`created_by_emp_number` = `hs_hr_employee`.`emp_number` AND `hs_hr_employee`.`purged_at` IS NOT NULL
SET `ohrm_leave_request_comment`.`created_by_name` = concat(`hs_hr_employee`.`emp_firstname`,' ', `hs_hr_employee`.`emp_lastname`);


DELETE FROM `ohrm_leave_comment`
WHERE `leave_id` IN (
    SELECT `id` FROM `ohrm_leave`
    WHERE `leave_request_id` IN (
      SELECT `id` FROM `ohrm_leave_request`
      WHERE `emp_number` IN (
        SELECT `emp_number` FROM `hs_hr_employee` WHERE `purged_at` IS NOT NULL
      )
    )
);


UPDATE `ohrm_leave_comment`
  JOIN `ohrm_user` ON `ohrm_leave_comment`.`created_by_id` = `ohrm_user`.`id`
  JOIN `hs_hr_employee` ON `ohrm_user`.`emp_number` = `hs_hr_employee`.`emp_number` AND `hs_hr_employee`.`purged_at` IS NOT NULL
SET `ohrm_leave_comment`.`created_by_name` = concat(`hs_hr_employee`.`emp_firstname`,' ', `hs_hr_employee`.`emp_lastname`);


UPDATE `ohrm_leave_comment`
  JOIN `hs_hr_employee` ON `ohrm_leave_comment`.`created_by_emp_number` = `hs_hr_employee`.`emp_number` AND `hs_hr_employee`.`purged_at` IS NOT NULL
SET `ohrm_leave_comment`.`created_by_name` = concat(`hs_hr_employee`.`emp_firstname`,' ', `hs_hr_employee`.`emp_lastname`);


UPDATE `ohrm_leave_request`
    SET `comments` = NULL
WHERE `emp_number` IN (SELECT `emp_number` FROM `hs_hr_employee` WHERE `purged_at` IS NOT NULL);


UPDATE `ohrm_leave_entitlement`
  JOIN `ohrm_user` ON `ohrm_leave_entitlement`.`created_by_id` = `ohrm_user`.`id`
  JOIN `hs_hr_employee` ON `ohrm_user`.`emp_number` = `hs_hr_employee`.`emp_number` AND `hs_hr_employee`.`purged_at` IS NOT NULL
SET `ohrm_leave_entitlement`.`created_by_name` = concat(`hs_hr_employee`.`emp_firstname`,' ', `hs_hr_employee`.`emp_lastname`);


UPDATE `ohrm_leave_entitlement_comment`
  JOIN `ohrm_user` ON `ohrm_leave_entitlement_comment`.`created_by_id` = `ohrm_user`.`id`
  JOIN `hs_hr_employee` ON `ohrm_user`.`emp_number` = `hs_hr_employee`.`emp_number` AND `hs_hr_employee`.`purged_at` IS NOT NULL
SET `ohrm_leave_entitlement_comment`.`created_by_name` = concat(`hs_hr_employee`.`emp_firstname`,' ', `hs_hr_employee`.`emp_lastname`);


UPDATE `ohrm_leave_entitlement_comment`
  JOIN `hs_hr_employee` ON `ohrm_leave_entitlement_comment`.`created_by_emp_number` = `hs_hr_employee`.`emp_number` AND `hs_hr_employee`.`purged_at` IS NOT NULL
SET `ohrm_leave_entitlement_comment`.`created_by_name` = concat(`hs_hr_employee`.`emp_firstname`,' ', `hs_hr_employee`.`emp_lastname`);


UPDATE `ohrm_performance_appraisal`
  JOIN `hs_hr_employee` ON `ohrm_performance_appraisal`.`emp_number` = `hs_hr_employee`.`emp_number` AND `hs_hr_employee`.`purged_at` IS NOT NULL
SET `ohrm_performance_appraisal`.`description` = concat(`hs_hr_employee`.`emp_firstname`,' ', `hs_hr_employee`.`emp_lastname`,' Appraisal ',`ohrm_performance_appraisal`.`due_date`);


