shanxia2.0/backend/scripts/migration_20260713_pl_personnel_cert_split.sql

-- 人员从业资格证拆分：cert_name 重命名（可重复执行）
-- 前置 spec: docs/superpowers/specs/2026-07-13-pl-personnel-cert-split-design.md

-- 1) 押运员证 → 押运员从业资格证（全部 personnel 证书）
UPDATE pl_cert c
INNER JOIN pl_personnel p ON p.id = c.owner_id AND p.del_flag = 0
SET c.cert_name = '押运员从业资格证',
    c.update_time = NOW()
WHERE c.del_flag = 0
  AND c.owner_type = 'personnel'
  AND c.cert_name = '押运员证';

-- 2) 泛称从业资格证 → 驾驶员从业资格证（driver / both）
UPDATE pl_cert c
INNER JOIN pl_personnel p ON p.id = c.owner_id AND p.del_flag = 0
SET c.cert_name = '驾驶员从业资格证',
    c.update_time = NOW()
WHERE c.del_flag = 0
  AND c.owner_type = 'personnel'
  AND c.cert_name = '从业资格证'
  AND p.personnel_type IN ('driver', 'both');

-- 3) 泛称从业资格证 → 押运员从业资格证（escort）
UPDATE pl_cert c
INNER JOIN pl_personnel p ON p.id = c.owner_id AND p.del_flag = 0
SET c.cert_name = '押运员从业资格证',
    c.update_time = NOW()
WHERE c.del_flag = 0
  AND c.owner_type = 'personnel'
  AND c.cert_name = '从业资格证'
  AND p.personnel_type = 'escort';

-- 4) both 人员：若迁移后仍缺驾/押任一从业证，供运营补录（验证查询，不修改数据）
SELECT
  p.id AS personnel_id,
  p.personnel_name,
  p.personnel_type,
  GROUP_CONCAT(c.cert_name ORDER BY c.cert_name SEPARATOR '、') AS cert_names
FROM pl_personnel p
LEFT JOIN pl_cert c
  ON c.owner_id = p.id
  AND c.owner_type = 'personnel'
  AND c.del_flag = 0
WHERE p.del_flag = 0
  AND p.personnel_type = 'both'
GROUP BY p.id, p.personnel_name, p.personnel_type
HAVING SUM(CASE WHEN c.cert_name IN ('驾驶员从业资格证') THEN 1 ELSE 0 END) = 0
    OR SUM(CASE WHEN c.cert_name IN ('押运员从业资格证', '押运员证') THEN 1 ELSE 0 END) = 0;

-- 5) 危化品从业资格证：不自动迁移（人工清单）
SELECT
  p.id AS personnel_id,
  p.personnel_name,
  p.personnel_type,
  c.id AS cert_id,
  c.cert_name
FROM pl_cert c
INNER JOIN pl_personnel p ON p.id = c.owner_id AND p.del_flag = 0
WHERE c.del_flag = 0
  AND c.owner_type = 'personnel'
  AND c.cert_name = '危化品从业资格证';

-- 6) both + 仍保留泛称「从业资格证」的歧义记录（需人工处理，不自动改）
SELECT
  p.id AS personnel_id,
  p.personnel_name,
  c.id AS cert_id,
  c.cert_name
FROM pl_cert c
INNER JOIN pl_personnel p ON p.id = c.owner_id AND p.del_flag = 0
WHERE c.del_flag = 0
  AND c.owner_type = 'personnel'
  AND c.cert_name = '从业资格证'
  AND p.personnel_type = 'both';

-- 7) 字典 pl_cert_name 增量（可选，可重复执行）
INSERT INTO sys_dict_data (id, dict_label, dict_value, tag_type, dict_type, tenant_no, order_num, del_flag, create_time, update_time)
SELECT 'PL_CERT_DRIVER_QUAL', '驾驶员从业资格证', '驾驶员从业资格证', 'primary', 'pl_cert_name', 'shanxia2.0', 6, 0, NOW(), NOW()
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE id = 'PL_CERT_DRIVER_QUAL');

INSERT INTO sys_dict_data (id, dict_label, dict_value, tag_type, dict_type, tenant_no, order_num, del_flag, create_time, update_time)
SELECT 'PL_CERT_ESCORT_QUAL', '押运员从业资格证', '押运员从业资格证', 'warning', 'pl_cert_name', 'shanxia2.0', 7, 0, NOW(), NOW()
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE id = 'PL_CERT_ESCORT_QUAL');