-- =============================================================================
-- 学生基本信息 → 教务对接宽表视图
-- 说明见：跟教务系统同步学生基础信息/学生基本信息对接说明.md
--
-- 口径摘要：
--   * 身份证件类型：有 id_card 则「居民身份证」，否则 NULL
--   * 所在校区：学生 class_id → xg_class_msg.campus_id → xg_dorm_campus.campus_name，再回退班级冗余 campus_name
--   * 班主任工号：class_teacher_id → sys_user.user_no（空则 user_name）
--   * 学生是否在校：默认「在校」；退学(4)、毕业(5) 为「不在校」
--   * 异动类型：固定 NULL
--   * 毕业时间/毕业学年学期：在籍按 入学年份+学制 推算；已毕业(5)取 BI_YE 异动 effective_date
--   * 学习经历/何年何月/在何地学习/在校照片：固定 NULL
--   * 性别：0→男，1→女
--   * 成员1/2：父亲/母亲及关系、电话
--   * 学生当前状态：输出教务中文名称（见对接说明 §状态映射）
--   * 培养层次：班级 class_type 字典值（zsjy_education_level），非中文名称
--   * 系部/专业：优先 college_id、major_id 关联 sys_dept.dept_name；班级名称取 xg_class_msg.class_name
-- =============================================================================

DROP VIEW IF EXISTS `vw_jw_student_export`;

CREATE VIEW `vw_jw_student_export` AS
SELECT
  (m.`name` COLLATE utf8mb4_unicode_ci) AS `姓名`,
  (m.student_no COLLATE utf8mb4_unicode_ci) AS `学号`,
  (m.phone COLLATE utf8mb4_unicode_ci) AS `联系电话`,
  (CASE
    WHEN NULLIF(TRIM(m.id_card), '') IS NOT NULL
      THEN ('居民身份证' COLLATE utf8mb4_unicode_ci)
    ELSE (CAST(NULL AS CHAR(32) CHARACTER SET utf8mb4) COLLATE utf8mb4_unicode_ci)
  END) AS `身份证件类型`,
  (m.id_card COLLATE utf8mb4_unicode_ci) AS `身份证件号`,
  (COALESCE(cm.class_name, m.class_name) COLLATE utf8mb4_unicode_ci) AS `班级信息`,
  (COALESCE(campus_cm.campus_name, cm.campus_name) COLLATE utf8mb4_unicode_ci) AS `所在校区`,
  (COALESCE(m.grade, cm.grade) COLLATE utf8mb4_unicode_ci) AS `所在年级`,
  m.enrollment_year AS `入学年份`,
  (COALESCE(dc_m.dept_name, dc_cm.dept_name, m.college_name, cm.college_name) COLLATE utf8mb4_unicode_ci) AS `系部信息`,
  (COALESCE(dm_m.dept_name, dm_cm.dept_name, m.major_name, cm.major_name) COLLATE utf8mb4_unicode_ci) AS `专业信息`,
  (COALESCE(NULLIF(TRIM(su.user_no), ''), su.user_name) COLLATE utf8mb4_unicode_ci) AS `班主任工号`,
  (COALESCE(m.class_teacher_name, cm.class_teacher_name) COLLATE utf8mb4_unicode_ci) AS `班主任姓名`,
  (CASE m.student_status
    WHEN '1' THEN '在读'
    WHEN '2' THEN '休学'
    WHEN '3' THEN '复学'
    WHEN '4' THEN '退学'
    WHEN '5' THEN '毕业'
    WHEN '6' THEN '其他'
    WHEN '7' THEN '结业'
    WHEN '8' THEN '停学'
    WHEN '9' THEN '其他'
    ELSE COALESCE(m.student_status, '')
  END COLLATE utf8mb4_unicode_ci) AS `学生当前状态`,
  (CASE
    WHEN (m.student_status COLLATE utf8mb4_unicode_ci) IN (
      ('4' COLLATE utf8mb4_unicode_ci),
      ('5' COLLATE utf8mb4_unicode_ci)
    ) THEN ('不在校' COLLATE utf8mb4_unicode_ci)
    ELSE ('在校' COLLATE utf8mb4_unicode_ci)
  END) AS `学生是否在校`,
  (CAST(NULL AS CHAR(32) CHARACTER SET utf8mb4) COLLATE utf8mb4_unicode_ci) AS `异动类型`,
  (cm.class_type COLLATE utf8mb4_unicode_ci) AS `培养层次`,
  COALESCE(cm.school_system, m.school_system) AS `学制`,
  (
    CASE
      WHEN (m.student_status COLLATE utf8mb4_unicode_ci) = ('5' COLLATE utf8mb4_unicode_ci)
       AND grad_sc.effective_date IS NOT NULL
        THEN YEAR(grad_sc.effective_date)
      WHEN m.enrollment_year IS NOT NULL
       AND COALESCE(cm.school_system, m.school_system) IS NOT NULL
        THEN m.enrollment_year + COALESCE(cm.school_system, m.school_system)
      WHEN cm.graduation_year IS NOT NULL
        THEN cm.graduation_year
      ELSE NULL
    END
  ) AS `毕业时间`,
  (
    CASE
      WHEN (
        CASE
          WHEN (m.student_status COLLATE utf8mb4_unicode_ci) = ('5' COLLATE utf8mb4_unicode_ci)
           AND grad_sc.effective_date IS NOT NULL
            THEN YEAR(grad_sc.effective_date)
          WHEN m.enrollment_year IS NOT NULL
           AND COALESCE(cm.school_system, m.school_system) IS NOT NULL
            THEN m.enrollment_year + COALESCE(cm.school_system, m.school_system)
          WHEN cm.graduation_year IS NOT NULL
            THEN cm.graduation_year
          ELSE NULL
        END
      ) IS NOT NULL
        THEN CONCAT(
          (
            CASE
              WHEN (m.student_status COLLATE utf8mb4_unicode_ci) = ('5' COLLATE utf8mb4_unicode_ci)
               AND grad_sc.effective_date IS NOT NULL
                THEN YEAR(grad_sc.effective_date)
              WHEN m.enrollment_year IS NOT NULL
               AND COALESCE(cm.school_system, m.school_system) IS NOT NULL
                THEN m.enrollment_year + COALESCE(cm.school_system, m.school_system)
              WHEN cm.graduation_year IS NOT NULL
                THEN cm.graduation_year
              ELSE NULL
            END
          ) - 1,
          '-',
          (
            CASE
              WHEN (m.student_status COLLATE utf8mb4_unicode_ci) = ('5' COLLATE utf8mb4_unicode_ci)
               AND grad_sc.effective_date IS NOT NULL
                THEN YEAR(grad_sc.effective_date)
              WHEN m.enrollment_year IS NOT NULL
               AND COALESCE(cm.school_system, m.school_system) IS NOT NULL
                THEN m.enrollment_year + COALESCE(cm.school_system, m.school_system)
              WHEN cm.graduation_year IS NOT NULL
                THEN cm.graduation_year
              ELSE NULL
            END
          ),
          '-2'
        )
      ELSE NULL
    END
  ) AS `毕业学年学期`,
  (CAST(NULL AS CHAR(500) CHARACTER SET utf8mb4) COLLATE utf8mb4_unicode_ci) AS `学习经历`,
  (CAST(NULL AS CHAR(128) CHARACTER SET utf8mb4) COLLATE utf8mb4_unicode_ci) AS `何年何月-何年何月`,
  (CAST(NULL AS CHAR(256) CHARACTER SET utf8mb4) COLLATE utf8mb4_unicode_ci) AS `在何地学习`,
  (CASE m.gender
    WHEN '0' THEN '男'
    WHEN '1' THEN '女'
    ELSE NULL
  END COLLATE utf8mb4_unicode_ci) AS `性别`,
  (m.native_place COLLATE utf8mb4_unicode_ci) AS `籍贯地`,
  (CAST(NULL AS CHAR(500) CHARACTER SET utf8mb4) COLLATE utf8mb4_unicode_ci) AS `在校照片`,
  DATE(m.birth_date) AS `出生日期`,
  (COALESCE(NULLIF(TRIM(m.home_address), ''), m.current_address) COLLATE utf8mb4_unicode_ci) AS `家庭现住址`,
  (m.father_name COLLATE utf8mb4_unicode_ci) AS `成员1姓名`,
  (m.mother_name COLLATE utf8mb4_unicode_ci) AS `成员2姓名`,
  (CASE
    WHEN NULLIF(TRIM(m.father_name), '') IS NOT NULL
      THEN ('父亲' COLLATE utf8mb4_unicode_ci)
    ELSE (CAST(NULL AS CHAR(16) CHARACTER SET utf8mb4) COLLATE utf8mb4_unicode_ci)
  END) AS `成员1关系`,
  (CASE
    WHEN NULLIF(TRIM(m.mother_name), '') IS NOT NULL
      THEN ('母亲' COLLATE utf8mb4_unicode_ci)
    ELSE (CAST(NULL AS CHAR(16) CHARACTER SET utf8mb4) COLLATE utf8mb4_unicode_ci)
  END) AS `成员2关系`,
  (m.father_phone COLLATE utf8mb4_unicode_ci) AS `成员1联系电话`,
  (m.mother_phone COLLATE utf8mb4_unicode_ci) AS `成员2联系电话`,
  m.id AS `来源记录id`
FROM xg_student_msg m
LEFT JOIN xg_class_msg cm
  ON cm.deleted = 0
 AND (cm.tenant_id COLLATE utf8mb4_unicode_ci) = (m.tenant_id COLLATE utf8mb4_unicode_ci)
 AND cm.id = CAST(NULLIF(TRIM(m.class_id), '') AS UNSIGNED)
LEFT JOIN sys_dept dc_m
  ON dc_m.dept_id = CAST(NULLIF(TRIM(m.college_id), '') AS UNSIGNED)
 AND (dc_m.del_flag COLLATE utf8mb4_unicode_ci) = ('0' COLLATE utf8mb4_unicode_ci)
 AND (dc_m.tenant_id COLLATE utf8mb4_unicode_ci) = (m.tenant_id COLLATE utf8mb4_unicode_ci)
LEFT JOIN sys_dept dm_m
  ON dm_m.dept_id = CAST(NULLIF(TRIM(m.major_id), '') AS UNSIGNED)
 AND (dm_m.del_flag COLLATE utf8mb4_unicode_ci) = ('0' COLLATE utf8mb4_unicode_ci)
 AND (dm_m.tenant_id COLLATE utf8mb4_unicode_ci) = (m.tenant_id COLLATE utf8mb4_unicode_ci)
LEFT JOIN sys_dept dc_cm
  ON dc_cm.dept_id = cm.college_id
 AND (dc_cm.del_flag COLLATE utf8mb4_unicode_ci) = ('0' COLLATE utf8mb4_unicode_ci)
 AND (dc_cm.tenant_id COLLATE utf8mb4_unicode_ci) = (m.tenant_id COLLATE utf8mb4_unicode_ci)
LEFT JOIN sys_dept dm_cm
  ON dm_cm.dept_id = cm.major_id
 AND (dm_cm.del_flag COLLATE utf8mb4_unicode_ci) = ('0' COLLATE utf8mb4_unicode_ci)
 AND (dm_cm.tenant_id COLLATE utf8mb4_unicode_ci) = (m.tenant_id COLLATE utf8mb4_unicode_ci)
LEFT JOIN xg_dorm_campus campus_cm
  ON campus_cm.id = cm.campus_id
 AND (campus_cm.deleted COLLATE utf8mb4_unicode_ci) = ('0' COLLATE utf8mb4_unicode_ci)
LEFT JOIN sys_user su
  ON su.user_id = COALESCE(m.class_teacher_id, cm.class_teacher_id)
 AND (su.del_flag COLLATE utf8mb4_unicode_ci) = ('0' COLLATE utf8mb4_unicode_ci)
 AND (su.tenant_id COLLATE utf8mb4_unicode_ci) = (m.tenant_id COLLATE utf8mb4_unicode_ci)
LEFT JOIN (
  SELECT
    sc.student_id,
    MAX(sc.effective_date) AS effective_date
  FROM xg_student_status_change sc
  WHERE sc.deleted = 0
    AND sc.process_status = 1
    AND (sc.change_type COLLATE utf8mb4_unicode_ci) = ('BI_YE' COLLATE utf8mb4_unicode_ci)
  GROUP BY sc.student_id
) grad_sc ON grad_sc.student_id = m.id
WHERE m.deleted = 0;

-- -----------------------------------------------------------------------------
-- 授权：用户 jw_cx 仅查询本视图（需使用具备 GRANT 权限的账号执行，如 root）
-- -----------------------------------------------------------------------------
GRANT SELECT ON `hzdyjs_xg`.`vw_jw_student_export` TO 'jw_cx'@'%';
FLUSH PRIVILEGES;