-- ============================================================================
-- 把 pat_med_inhosp 的「科室 / 病区」回填到 service_subtask（在院 4 列 + 离院 4 列）
-- 生成日期：2026-11-06（v2：修正 join 键 + 合并离院维度）
-- 适用：MySQL 8.0 / 5.7（未用 CTE、窗口函数）
--
-- ⚠️ 前置条件：先执行过 sql/rename_service_subtask_dims_20261106.sql（列已改名）。
--
-- ⚠️⚠️ join 键（v1 写错过，这里更正）：
--     service_subtask.inhospid 存的是 **pat_med_inhosp.inhospid**（业务主键），不是 id。
--     证据：VisitInhospMapper.xml#selectForScan 查的是 b.inhospid；
--           VisitSubtaskFactory 里是 d.setInhospid(inhosp.getInhospid())。
--     门诊侧例外：PatMedOuthospServiceDelegatingImpl 用的是 pat_med_outhosp.getId()，
--     所以 pat_med_outhosp 的 id 会和 pat_med_inhosp.inhospid 撞号 → 必须排除 service_type='3'。
--
-- 字段映射（8 列，注意「在院病区」和「出院病区」不是一回事）
--   在院维度（本次新增列）
--     service_subtask.hospitaldistrictcode/name      ← pat_med_inhosp.hospitaldistrictcode/name      （在院病区）
--     service_subtask.deptcode/name                   ← pat_med_inhosp.deptcode/name                  （在院科室）
--   离院/出院维度（老列，原 deptcode/deptname 改名而来）
--     service_subtask.leaveldeptcode/name             ← pat_med_inhosp.leaveldeptcode/name            （出院科室）
--     service_subtask.leavehospitaldistrictcode/name  ← pat_med_inhosp.leavehospitaldistrictcode/name （出院病区）
--
-- 安全策略
--   1) 全部用 COALESCE「目标列为空才写」→ **不覆盖任何已有值**，可重复执行；
--   2) 更新前先备份受影响行的这 8 列旧值（§2）；
--   3) 大表分批模板见 §6。
-- ============================================================================


-- ----------------------------------------------------------------------------
-- 0) 前置自查
-- ----------------------------------------------------------------------------
SHOW FULL COLUMNS FROM service_subtask LIKE '%dept%';
SHOW FULL COLUMNS FROM service_subtask LIKE 'hospitaldistrict%';
SHOW FULL COLUMNS FROM pat_med_inhosp LIKE '%dept%';
SHOW FULL COLUMNS FROM pat_med_inhosp LIKE 'hospitaldistrict%';


-- ----------------------------------------------------------------------------
-- 1) 影响面评估（先看数，再动手）
-- ----------------------------------------------------------------------------

-- 1.1 能 join 上的子任务数 + 8 列里各有多少是空的
SELECT COUNT(*)                                                                   AS joined_rows,
       SUM(hospitaldistrictcode      IS NULL OR hospitaldistrictcode      = '')    AS hd_code_empty,
       SUM(hospitaldistrictname      IS NULL OR hospitaldistrictname      = '')    AS hd_name_empty,
       SUM(deptcode                  IS NULL OR deptcode                  = '')    AS dept_code_empty,
       SUM(deptname                  IS NULL OR deptname                  = '')    AS dept_name_empty,
       SUM(leaveldeptcode            IS NULL OR leaveldeptcode            = '')    AS lv_dept_code_empty,
       SUM(leaveldeptname            IS NULL OR leaveldeptname            = '')    AS lv_dept_name_empty,
       SUM(leavehospitaldistrictcode IS NULL OR leavehospitaldistrictcode = '')    AS lv_hd_code_empty,
       SUM(leavehospitaldistrictname IS NULL OR leavehospitaldistrictname = '')    AS lv_hd_name_empty
  FROM service_subtask s
  JOIN pat_med_inhosp b
    ON b.inhospid = s.inhospid
   AND b.patid   = s.patid
 WHERE s.inhospid IS NOT NULL
   AND s.service_type <> '3'
   AND (s.hosp_type IS NULL OR s.hosp_type <> '8');

-- 1.2 按 service_type 看分布（2/13 出院随访、17/18 入院宣教/入院随访、6/14/15/16 满意度子任务）
SELECT s.service_type,
       COUNT(*)                                                                  AS cnt,
       SUM(s.deptcode            IS NULL OR s.deptcode            = '')          AS dept_empty,
       SUM(s.hospitaldistrictcode IS NULL OR s.hospitaldistrictcode = '')        AS hd_empty,
       SUM(s.leaveldeptcode      IS NULL OR s.leaveldeptcode      = '')          AS lv_dept_empty,
       SUM(s.leavehospitaldistrictcode IS NULL OR s.leavehospitaldistrictcode = '') AS lv_hd_empty
  FROM service_subtask s
  JOIN pat_med_inhosp b ON b.inhospid = s.inhospid AND b.patid = s.patid
 WHERE s.inhospid IS NOT NULL
   AND s.service_type <> '3'
 GROUP BY s.service_type
 ORDER BY cnt DESC;

-- 1.3 join 不上的诊断：inhospid 有值、但 patid 对不上
SELECT s.service_type,
       COUNT(*) AS unmatched
  FROM service_subtask s
  LEFT JOIN pat_med_inhosp b
         ON b.inhospid = s.inhospid
        AND b.patid   = s.patid
 WHERE s.inhospid IS NOT NULL
   AND s.service_type <> '3'
   AND b.inhospid IS NULL
 GROUP BY s.service_type
 ORDER BY unmatched DESC;

-- 1.4 源表 inhospid 是否唯一（不唯一会让 UPDATE 取到其中一行，结果不确定）
SELECT inhospid, COUNT(*) AS c, COUNT(DISTINCT patid) AS pats
  FROM pat_med_inhosp
 WHERE inhospid IS NOT NULL
 GROUP BY inhospid
HAVING c > 1
 ORDER BY c DESC
 LIMIT 20;

-- 1.5 一条子任务是否会被多行源数据命中（应为 0；有值就先把 §1.4 的重复处理干净）
SELECT COUNT(*) AS subtask_rows, COUNT(*) - COUNT(DISTINCT s.id) AS dup_hit
  FROM service_subtask s
  JOIN pat_med_inhosp b ON b.inhospid = s.inhospid AND b.patid = s.patid
 WHERE s.inhospid IS NOT NULL
   AND s.service_type <> '3';


-- ----------------------------------------------------------------------------
-- 2) 备份受影响行的 8 列旧值（回滚见 §7）
-- ----------------------------------------------------------------------------
CREATE TABLE bak_service_subtask_dept_20261106 AS
SELECT s.id,
       s.service_type,
       s.hosp_type,
       s.inhospid,
       s.patid,
       s.leaveldeptcode,
       s.leaveldeptname,
       s.leavehospitaldistrictcode,
       s.leavehospitaldistrictname,
       s.hospitaldistrictcode,
       s.hospitaldistrictname,
       s.deptcode,
       s.deptname
  FROM service_subtask s
  JOIN pat_med_inhosp b
    ON b.inhospid = s.inhospid
   AND b.patid   = s.patid
 WHERE s.inhospid IS NOT NULL
   AND s.service_type <> '3'
   AND (s.hosp_type IS NULL OR s.hosp_type <> '8')
   AND (  NULLIF(TRIM(s.hospitaldistrictcode), '')       IS NULL
       OR NULLIF(TRIM(s.hospitaldistrictname), '')       IS NULL
       OR NULLIF(TRIM(s.deptcode), '')                   IS NULL
       OR NULLIF(TRIM(s.deptname), '')                   IS NULL
       OR NULLIF(TRIM(s.leaveldeptcode), '')             IS NULL
       OR NULLIF(TRIM(s.leaveldeptname), '')             IS NULL
       OR NULLIF(TRIM(s.leavehospitaldistrictcode), '')  IS NULL
       OR NULLIF(TRIM(s.leavehospitaldistrictname), '')  IS NULL );

SELECT COUNT(*) AS backup_rows FROM bak_service_subtask_dept_20261106;


-- ----------------------------------------------------------------------------
-- 3) 主回填：在院 4 列 + 离院 4 列（只补空，不覆盖；可重复执行）
-- ----------------------------------------------------------------------------
UPDATE service_subtask s
  JOIN pat_med_inhosp b
    ON b.inhospid = s.inhospid
   AND b.patid   = s.patid
   SET s.hospitaldistrictcode      = COALESCE(NULLIF(TRIM(s.hospitaldistrictcode), ''),      b.hospitaldistrictcode),
       s.hospitaldistrictname      = COALESCE(NULLIF(TRIM(s.hospitaldistrictname), ''),      b.hospitaldistrictname),
       s.deptcode                  = COALESCE(NULLIF(TRIM(s.deptcode), ''),                  b.deptcode),
       s.deptname                  = COALESCE(NULLIF(TRIM(s.deptname), ''),                  b.deptname),
       s.leaveldeptcode            = COALESCE(NULLIF(TRIM(s.leaveldeptcode), ''),            b.leaveldeptcode),
       s.leaveldeptname            = COALESCE(NULLIF(TRIM(s.leaveldeptname), ''),            b.leaveldeptname),
       s.leavehospitaldistrictcode = COALESCE(NULLIF(TRIM(s.leavehospitaldistrictcode), ''), b.leavehospitaldistrictcode),
       s.leavehospitaldistrictname = COALESCE(NULLIF(TRIM(s.leavehospitaldistrictname), ''), b.leavehospitaldistrictname)
 WHERE s.inhospid IS NOT NULL
   AND s.service_type <> '3'                        -- 门诊随访的 inhospid 是 pat_med_outhosp.id
   AND (s.hosp_type IS NULL OR s.hosp_type <> '8')  -- 扫码生成的虚拟患者
   AND (  NULLIF(TRIM(s.hospitaldistrictcode), '')      IS NULL
       OR NULLIF(TRIM(s.hospitaldistrictname), '')      IS NULL
       OR NULLIF(TRIM(s.deptcode), '')                  IS NULL
       OR NULLIF(TRIM(s.deptname), '')                  IS NULL
       OR NULLIF(TRIM(s.leaveldeptcode), '')            IS NULL
       OR NULLIF(TRIM(s.leaveldeptname), '')            IS NULL
       OR NULLIF(TRIM(s.leavehospitaldistrictcode), '') IS NULL
       OR NULLIF(TRIM(s.leavehospitaldistrictname), '') IS NULL );


-- ----------------------------------------------------------------------------
-- 4) 口径说明 + 可选的"强制改写"
--
--    4.1 为什么离院 4 列也用「只补空」：
--        service_type = 17/18（入院宣教/入院随访）生成时走 type 4/5 分支，
--        `leaveldeptcode/name` 里存的其实是**入院科室**（d.setLeaveldeptcode(inhosp.getDeptcode())）。
--        这些行非空 → §3 不会动它们；若为空则会被补成 pat_med_inhosp.leaveldeptcode
--        （住院患者通常为空 → 等于没变）。
--        出院随访（2/13）与满意度子任务（6/14/15/16）的离院 4 列生成时本来就取自
--        pat_med_inhosp，只可能是历史脏数据为空，补空即修数据。
--
--    4.2 如果你要的是"17/18 也统一成出院科室口径"，用下面这段（**会覆盖**，先跑 §2 备份）：
-- ----------------------------------------------------------------------------
-- UPDATE service_subtask s
--   JOIN pat_med_inhosp b ON b.inhospid = s.inhospid AND b.patid = s.patid
--    SET s.leaveldeptcode            = b.leaveldeptcode,
--        s.leaveldeptname            = b.leaveldeptname,
--        s.leavehospitaldistrictcode = b.leavehospitaldistrictcode,
--        s.leavehospitaldistrictname = b.leavehospitaldistrictname
--  WHERE s.inhospid IS NOT NULL
--    AND s.service_type IN ('17', '18')
--    AND (NULLIF(TRIM(b.leaveldeptcode), '') IS NOT NULL
--         OR NULLIF(TRIM(b.leavehospitaldistrictcode), '') IS NOT NULL);


-- ----------------------------------------------------------------------------
-- 5) 【可选】全都强制覆盖版（"原来有值但是错的"时用）
--    把 §3 的 COALESCE(...) 换成直接赋值，并去掉 WHERE 里那串「目标列为空」条件：
--      SET s.hospitaldistrictcode = b.hospitaldistrictcode,
--          s.hospitaldistrictname = b.hospitaldistrictname,
--          s.deptcode = b.deptcode, s.deptname = b.deptname,
--          s.leaveldeptcode = b.leaveldeptcode, s.leaveldeptname = b.leaveldeptname,
--          s.leavehospitaldistrictcode = b.leavehospitaldistrictcode,
--          s.leavehospitaldistrictname = b.leavehospitaldistrictname
--    ⚠️ pat_med_inhosp.dept* 会被「在院信息刷新」定时任务按 HIS 当前科室改写，
--       覆盖后可能把"出院当时的科室"改成"现在 HIS 里的科室"。覆盖前务必先备份。
-- ----------------------------------------------------------------------------


-- ----------------------------------------------------------------------------
-- 6) 大表分批模板（多表 UPDATE 不支持 LIMIT，用 id 区间；每段单独提交）
--    先查范围：SELECT MIN(id), MAX(id) FROM service_subtask;
-- ----------------------------------------------------------------------------
-- UPDATE service_subtask s
--   JOIN pat_med_inhosp b ON b.inhospid = s.inhospid AND b.patid = s.patid
--    SET s.hospitaldistrictcode      = COALESCE(NULLIF(TRIM(s.hospitaldistrictcode), ''),      b.hospitaldistrictcode),
--        s.hospitaldistrictname      = COALESCE(NULLIF(TRIM(s.hospitaldistrictname), ''),      b.hospitaldistrictname),
--        s.deptcode                  = COALESCE(NULLIF(TRIM(s.deptcode), ''),                  b.deptcode),
--        s.deptname                  = COALESCE(NULLIF(TRIM(s.deptname), ''),                  b.deptname),
--        s.leaveldeptcode            = COALESCE(NULLIF(TRIM(s.leaveldeptcode), ''),            b.leaveldeptcode),
--        s.leaveldeptname            = COALESCE(NULLIF(TRIM(s.leaveldeptname), ''),            b.leaveldeptname),
--        s.leavehospitaldistrictcode = COALESCE(NULLIF(TRIM(s.leavehospitaldistrictcode), ''), b.leavehospitaldistrictcode),
--        s.leavehospitaldistrictname = COALESCE(NULLIF(TRIM(s.leavehospitaldistrictname), ''), b.leavehospitaldistrictname)
--  WHERE s.id > 1 AND s.id <= 50000
--    AND s.inhospid IS NOT NULL
--    AND s.service_type <> '3'
--    AND (s.hosp_type IS NULL OR s.hosp_type <> '8')
--    AND (  NULLIF(TRIM(s.hospitaldistrictcode), '')      IS NULL
--        OR NULLIF(TRIM(s.hospitaldistrictname), '')      IS NULL
--        OR NULLIF(TRIM(s.deptcode), '')                  IS NULL
--        OR NULLIF(TRIM(s.deptname), '')                  IS NULL
--        OR NULLIF(TRIM(s.leaveldeptcode), '')            IS NULL
--        OR NULLIF(TRIM(s.leaveldeptname), '')            IS NULL
--        OR NULLIF(TRIM(s.leavehospitaldistrictcode), '') IS NULL
--        OR NULLIF(TRIM(s.leavehospitaldistrictname), '') IS NULL );


-- ----------------------------------------------------------------------------
-- 7) 回滚（按 §2 备份表还原 8 列）
-- ----------------------------------------------------------------------------
-- UPDATE service_subtask s
--   JOIN bak_service_subtask_dept_20261106 k ON k.id = s.id
--    SET s.hospitaldistrictcode      = k.hospitaldistrictcode,
--        s.hospitaldistrictname      = k.hospitaldistrictname,
--        s.deptcode                  = k.deptcode,
--        s.deptname                  = k.deptname,
--        s.leaveldeptcode            = k.leaveldeptcode,
--        s.leaveldeptname            = k.leaveldeptname,
--        s.leavehospitaldistrictcode = k.leavehospitaldistrictcode,
--        s.leavehospitaldistrictname = k.leavehospitaldistrictname;


-- ----------------------------------------------------------------------------
-- 8) 回填后核对
-- ----------------------------------------------------------------------------

-- 8.1 还剩多少空（应接近 0；剩下的基本是 §1.3/§1.4 命中不了的）
SELECT COUNT(*)                                                                    AS still_empty_rows,
       SUM(hospitaldistrictcode      IS NULL OR hospitaldistrictcode      = '')     AS hd_code_empty,
       SUM(hospitaldistrictname      IS NULL OR hospitaldistrictname      = '')     AS hd_name_empty,
       SUM(deptcode                  IS NULL OR deptcode                  = '')     AS dept_code_empty,
       SUM(deptname                  IS NULL OR deptname                  = '')     AS dept_name_empty,
       SUM(leaveldeptcode            IS NULL OR leaveldeptcode            = '')     AS lv_dept_code_empty,
       SUM(leaveldeptname            IS NULL OR leaveldeptname            = '')     AS lv_dept_name_empty,
       SUM(leavehospitaldistrictcode IS NULL OR leavehospitaldistrictcode = '')     AS lv_hd_code_empty,
       SUM(leavehospitaldistrictname IS NULL OR leavehospitaldistrictname = '')     AS lv_hd_name_empty
  FROM service_subtask s
  JOIN pat_med_inhosp b ON b.inhospid = s.inhospid AND b.patid = s.patid
 WHERE s.inhospid IS NOT NULL
   AND s.service_type <> '3'
   AND (s.hosp_type IS NULL OR s.hosp_type <> '8')
   AND (  NULLIF(TRIM(s.hospitaldistrictcode), '')      IS NULL
       OR NULLIF(TRIM(s.hospitaldistrictname), '')      IS NULL
       OR NULLIF(TRIM(s.deptcode), '')                  IS NULL
       OR NULLIF(TRIM(s.deptname), '')                  IS NULL
       OR NULLIF(TRIM(s.leaveldeptcode), '')            IS NULL
       OR NULLIF(TRIM(s.leaveldeptname), '')            IS NULL
       OR NULLIF(TRIM(s.leavehospitaldistrictcode), '') IS NULL
       OR NULLIF(TRIM(s.leavehospitaldistrictname), '') IS NULL );

-- 8.2 逐行对照抽检（两边是否一致）
SELECT s.id, s.service_type, s.inhospid,
       s.deptcode                  AS sub_dept,    b.deptcode                  AS inhosp_dept,
       s.hospitaldistrictcode      AS sub_hd,      b.hospitaldistrictcode      AS inhosp_hd,
       s.leaveldeptcode            AS sub_lv_dept, b.leaveldeptcode            AS inhosp_lv_dept,
       s.leavehospitaldistrictcode AS sub_lv_hd,   b.leavehospitaldistrictcode AS inhosp_lv_hd
  FROM service_subtask s
  JOIN pat_med_inhosp b ON b.inhospid = s.inhospid AND b.patid = s.patid
 WHERE s.inhospid IS NOT NULL
   AND s.service_type <> '3'
 ORDER BY s.id DESC
 LIMIT 50;