-- 历史数据回填：把按"人名字符串"关联的记录改为按用户 ID 关联。
-- 处理旧账号名→当前账号名的改名映射，再解析为用户 ID。可重复执行（幂等）。

WITH alias(oldname, newname) AS (VALUES
  ('销售账号','张伟'), ('销售二','王芳'), ('销售三','李娜'),
  ('风控账号','刘洋'), ('风控二','陈静'),
  ('管理账号','赵磊'), ('管理二','孙莉'),
  ('系统账号','周强')
)
UPDATE assessments a
SET assessment = jsonb_set(a.assessment, '{assessorId}', to_jsonb(u.id))
FROM alias al JOIN users u ON u.username = al.newname
WHERE a.assessment->>'assessorId' = al.oldname;

-- assessorId 已是当前用户名 → 也转为 ID
UPDATE assessments a
SET assessment = jsonb_set(a.assessment, '{assessorId}', to_jsonb(u.id))
FROM users u
WHERE a.assessment->>'assessorId' = u.username;

-- 审计：按旧名映射回填 actor_id，并把 username 刷新为当前账号名
WITH alias(oldname, newname) AS (VALUES
  ('销售账号','张伟'), ('销售二','王芳'), ('销售三','李娜'),
  ('风控账号','刘洋'), ('风控二','陈静'),
  ('管理账号','赵磊'), ('管理二','孙莉'),
  ('系统账号','周强')
)
UPDATE audit_logs al2
SET actor_id = u.id, username = u.username
FROM alias al JOIN users u ON u.username = al.newname
WHERE al2.actor_id IS NULL AND al2.username = al.oldname;

-- 审计：username 已是当前账号名 → 回填 actor_id
UPDATE audit_logs al2
SET actor_id = u.id
FROM users u
WHERE al2.actor_id IS NULL AND al2.username = u.username;