-- =============================================================================
-- 刷新 xg_student_msg 冗余字段：班级名称、专业名称
--
-- 背景：老师修改班级/专业后，xg_class_msg、sys_dept 已更新，
--       但 xg_student_msg 上冗余的 class_name、major_name 仍为旧值。
--
-- 刷新口径（与 vw_jw_student_export 一致）：
--   * class_name  ← 关联班级 xg_class_msg.class_name（按 class_id）
--   * major_name  ← 优先学生 major_id → sys_dept.dept_name
--                   其次班级 major_id → sys_dept.dept_name
--                   再回退 xg_class_msg.major_name
--
-- 用法：
--   mysql -h <host> -u root -p hzdyjs_xg < docs/数据库脚本/2026-09-02-xg_student_msg-refresh-class-major-name.sql
--
-- 建议：先执行「一、预览」确认影响范围，再执行「二、更新」
-- =============================================================================

SET NAMES utf8mb4;

-- -----------------------------------------------------------------------------
-- 一、预览：即将刷新的记录数
-- -----------------------------------------------------------------------------

SELECT '========== 1. 班级名称不一致（将刷新）==========' AS section;

SELECT COUNT(*) AS need_refresh_class_name
FROM xg_student_msg m
INNER JOIN xg_class_msg cm
  ON cm.deleted = 0
 AND cm.tenant_id = m.tenant_id
 AND cm.id = CAST(NULLIF(TRIM(m.class_id), '') AS UNSIGNED)
WHERE m.deleted = 0
  AND NULLIF(TRIM(m.class_id), '') IS NOT NULL
  AND (
    m.class_name IS NULL
    OR TRIM(m.class_name) = ''
    OR m.class_name <> cm.class_name
  );

SELECT '========== 2. 专业名称不一致（将刷新）==========' AS section;

SELECT COUNT(*) AS need_refresh_major_name
FROM xg_student_msg m
LEFT JOIN xg_class_msg cm
  ON cm.deleted = 0
 AND cm.tenant_id = m.tenant_id
 AND cm.id = CAST(NULLIF(TRIM(m.class_id), '') AS UNSIGNED)
LEFT JOIN sys_dept dm_m
  ON dm_m.dept_id = CAST(NULLIF(TRIM(m.major_id), '') AS UNSIGNED)
 AND dm_m.del_flag = '0'
 AND dm_m.tenant_id = m.tenant_id
LEFT JOIN sys_dept dm_cm
  ON dm_cm.dept_id = cm.major_id
 AND dm_cm.del_flag = '0'
 AND dm_cm.tenant_id = m.tenant_id
WHERE m.deleted = 0
  AND COALESCE(dm_m.dept_name, dm_cm.dept_name, cm.major_name) IS NOT NULL
  AND TRIM(COALESCE(dm_m.dept_name, dm_cm.dept_name, cm.major_name)) <> ''
  AND (
    m.major_name IS NULL
    OR TRIM(m.major_name) = ''
    OR m.major_name <> COALESCE(dm_m.dept_name, dm_cm.dept_name, cm.major_name)
  );

SELECT '========== 3. 不一致样例（班级名称，最多 20 条）==========' AS section;

SELECT
  m.id,
  m.student_no AS 学号,
  m.name AS 姓名,
  m.class_id,
  m.class_name AS 当前班级名称,
  cm.class_name AS 新班级名称
FROM xg_student_msg m
INNER JOIN xg_class_msg cm
  ON cm.deleted = 0
 AND cm.tenant_id = m.tenant_id
 AND cm.id = CAST(NULLIF(TRIM(m.class_id), '') AS UNSIGNED)
WHERE m.deleted = 0
  AND NULLIF(TRIM(m.class_id), '') IS NOT NULL
  AND (
    m.class_name IS NULL
    OR TRIM(m.class_name) = ''
    OR m.class_name <> cm.class_name
  )
ORDER BY m.student_no
LIMIT 20;

SELECT '========== 4. 不一致样例（专业名称，最多 20 条）==========' AS section;

SELECT
  m.id,
  m.student_no AS 学号,
  m.name AS 姓名,
  m.major_id,
  m.major_name AS 当前专业名称,
  COALESCE(dm_m.dept_name, dm_cm.dept_name, cm.major_name) AS 新专业名称
FROM xg_student_msg m
LEFT JOIN xg_class_msg cm
  ON cm.deleted = 0
 AND cm.tenant_id = m.tenant_id
 AND cm.id = CAST(NULLIF(TRIM(m.class_id), '') AS UNSIGNED)
LEFT JOIN sys_dept dm_m
  ON dm_m.dept_id = CAST(NULLIF(TRIM(m.major_id), '') AS UNSIGNED)
 AND dm_m.del_flag = '0'
 AND dm_m.tenant_id = m.tenant_id
LEFT JOIN sys_dept dm_cm
  ON dm_cm.dept_id = cm.major_id
 AND dm_cm.del_flag = '0'
 AND dm_cm.tenant_id = m.tenant_id
WHERE m.deleted = 0
  AND COALESCE(dm_m.dept_name, dm_cm.dept_name, cm.major_name) IS NOT NULL
  AND TRIM(COALESCE(dm_m.dept_name, dm_cm.dept_name, cm.major_name)) <> ''
  AND (
    m.major_name IS NULL
    OR TRIM(m.major_name) = ''
    OR m.major_name <> COALESCE(dm_m.dept_name, dm_cm.dept_name, cm.major_name)
  )
ORDER BY m.student_no
LIMIT 20;

-- -----------------------------------------------------------------------------
-- 二、执行更新（确认预览无误后再执行）
-- -----------------------------------------------------------------------------

START TRANSACTION;

-- 2.1 刷新班级名称
UPDATE xg_student_msg m
INNER JOIN xg_class_msg cm
  ON cm.deleted = 0
 AND cm.tenant_id = m.tenant_id
 AND cm.id = CAST(NULLIF(TRIM(m.class_id), '') AS UNSIGNED)
SET
  m.class_name = cm.class_name,
  m.update_time = NOW()
WHERE m.deleted = 0
  AND NULLIF(TRIM(m.class_id), '') IS NOT NULL
  AND (
    m.class_name IS NULL
    OR TRIM(m.class_name) = ''
    OR m.class_name <> cm.class_name
  );

SELECT ROW_COUNT() AS updated_class_name_rows;

-- 2.2 刷新专业名称
UPDATE xg_student_msg m
LEFT JOIN xg_class_msg cm
  ON cm.deleted = 0
 AND cm.tenant_id = m.tenant_id
 AND cm.id = CAST(NULLIF(TRIM(m.class_id), '') AS UNSIGNED)
LEFT JOIN sys_dept dm_m
  ON dm_m.dept_id = CAST(NULLIF(TRIM(m.major_id), '') AS UNSIGNED)
 AND dm_m.del_flag = '0'
 AND dm_m.tenant_id = m.tenant_id
LEFT JOIN sys_dept dm_cm
  ON dm_cm.dept_id = cm.major_id
 AND dm_cm.del_flag = '0'
 AND dm_cm.tenant_id = m.tenant_id
SET
  m.major_name = COALESCE(dm_m.dept_name, dm_cm.dept_name, cm.major_name),
  m.update_time = NOW()
WHERE m.deleted = 0
  AND COALESCE(dm_m.dept_name, dm_cm.dept_name, cm.major_name) IS NOT NULL
  AND TRIM(COALESCE(dm_m.dept_name, dm_cm.dept_name, cm.major_name)) <> ''
  AND (
    m.major_name IS NULL
    OR TRIM(m.major_name) = ''
    OR m.major_name <> COALESCE(dm_m.dept_name, dm_cm.dept_name, cm.major_name)
  );

SELECT ROW_COUNT() AS updated_major_name_rows;

-- 确认无误后提交；若要撤销请执行 ROLLBACK;
COMMIT;

-- -----------------------------------------------------------------------------
-- 三、更新后复核
-- -----------------------------------------------------------------------------

SELECT '========== 5. 更新后仍不一致（应为 0 或接近 0）==========' AS section;

SELECT
  (
    SELECT COUNT(*)
    FROM xg_student_msg m
    INNER JOIN xg_class_msg cm
      ON cm.deleted = 0
     AND cm.tenant_id = m.tenant_id
     AND cm.id = CAST(NULLIF(TRIM(m.class_id), '') AS UNSIGNED)
    WHERE m.deleted = 0
      AND NULLIF(TRIM(m.class_id), '') IS NOT NULL
      AND (
        m.class_name IS NULL
        OR TRIM(m.class_name) = ''
        OR m.class_name <> cm.class_name
      )
  ) AS remain_class_name_mismatch,
  (
    SELECT COUNT(*)
    FROM xg_student_msg m
    LEFT JOIN xg_class_msg cm
      ON cm.deleted = 0
     AND cm.tenant_id = m.tenant_id
     AND cm.id = CAST(NULLIF(TRIM(m.class_id), '') AS UNSIGNED)
    LEFT JOIN sys_dept dm_m
      ON dm_m.dept_id = CAST(NULLIF(TRIM(m.major_id), '') AS UNSIGNED)
     AND dm_m.del_flag = '0'
     AND dm_m.tenant_id = m.tenant_id
    LEFT JOIN sys_dept dm_cm
      ON dm_cm.dept_id = cm.major_id
     AND dm_cm.del_flag = '0'
     AND dm_cm.tenant_id = m.tenant_id
    WHERE m.deleted = 0
      AND COALESCE(dm_m.dept_name, dm_cm.dept_name, cm.major_name) IS NOT NULL
      AND TRIM(COALESCE(dm_m.dept_name, dm_cm.dept_name, cm.major_name)) <> ''
      AND (
        m.major_name IS NULL
        OR TRIM(m.major_name) = ''
        OR m.major_name <> COALESCE(dm_m.dept_name, dm_cm.dept_name, cm.major_name)
      )
  ) AS remain_major_name_mismatch;