-- 仅用于“100% 可确定”的历史借用记录工号回填。
-- 执行前必须先确认：
-- 1. person.job_number 已由可信 HR 同步写入
-- 2. 本次只允许回填当前 person_identifier 为空的记录
-- 3. 只处理 source_system = 'ding_hr' 且 source_userid 非空的可信同步人员

SELECT
  br.id,
  br.equipment_code,
  br.person_id,
  br.person_name,
  br.person_nickname,
  br.person_identifier AS current_identifier,
  p.job_number AS target_job_number,
  p.source_userid,
  p.source_system
FROM borrow_record br
JOIN person p ON p.id = br.person_id
WHERE (br.person_identifier IS NULL OR TRIM(br.person_identifier) = '')
  AND p.job_number IS NOT NULL
  AND TRIM(p.job_number) <> ''
  AND p.source_system = 'ding_hr'
  AND p.source_userid IS NOT NULL
ORDER BY br.id;

-- 确认上面的候选数据无误后，再手动执行下面的 UPDATE。
-- UPDATE borrow_record br
-- JOIN person p ON p.id = br.person_id
-- SET br.person_identifier = p.job_number
-- WHERE (br.person_identifier IS NULL OR TRIM(br.person_identifier) = '')
--   AND p.job_number IS NOT NULL
--   AND TRIM(p.job_number) <> ''
--   AND p.source_system = 'ding_hr'
--   AND p.source_userid IS NOT NULL;