-- 西溪6号楼住宿预检查脚本（根据 学校学生住宿情况@西溪6号楼.md 自动生成）
-- 说明: 本脚本仅做预检查，不会对正式表进行增删改

DROP TEMPORARY TABLE IF EXISTS tmp_assign_input_raw;
CREATE TEMPORARY TABLE tmp_assign_input_raw (
    room_code VARCHAR(20) NOT NULL,
    bed_name VARCHAR(20) NOT NULL,
    student_name VARCHAR(50) NULL,
    class_name_raw VARCHAR(100) NULL,
    remark VARCHAR(200) NULL,
    source_line INT NULL,
    raw_text VARCHAR(255) NULL
);

INSERT INTO tmp_assign_input_raw(room_code, bed_name, student_name, class_name_raw, remark, source_line, raw_text)
VALUES
('6302', '1上', '徐湘琴', '药剂211', NULL, 32, '1上 徐湘琴 药剂211'),
('6302', '1下', '王瑜洁', '药剂211', NULL, 33, '1下 王瑜洁 药剂211'),
('6302', '2上', '周慧颖', '药剂211', NULL, 34, '2上 周慧颖 药剂211'),
('6302', '2下', '鲁子奕', '药剂211', NULL, 35, '2下 鲁子奕 药剂211'),
('6303', '1上', NULL, NULL, NULL, 39, '1上'),
('6303', '1下', '王佳慧', '药剂211', NULL, 40, '1下 王佳慧 药剂211'),
('6303', '2上', '张雅婷', '药剂211', NULL, 41, '2上 张雅婷 药剂211'),
('6303', '2下', '章征浣', '药剂211', NULL, 42, '2下 章征浣 药剂211'),
('6304', '1上', '吕杭', '药剂211', NULL, 46, '1上 吕杭 药剂211'),
('6304', '1下', '沈晓冉', '药剂211', NULL, 47, '1下 沈晓冉 药剂211'),
('6304', '2上', '应雪燕', '药剂211', NULL, 48, '2上 应雪燕 药剂211'),
('6304', '2下', NULL, NULL, NULL, 49, '2下'),
('6305', '1上', '邱夕颜', '药剂232', NULL, 53, '1上 邱夕颜 药剂232'),
('6305', '1下', '郑飞杨', '药剂211', NULL, 54, '1下 郑飞杨 药剂211'),
('6305', '2上', '涂舒涵', '药剂211', NULL, 55, '2上 涂舒涵 药剂211'),
('6305', '2下', '张欣雅', '中药232', NULL, 56, '2下 张欣雅 中药232'),
('6306', '1上', '来思琴', '药剂211', NULL, 60, '1上 来思琴 药剂211'),
('6306', '1下', '祝丽倩', '药剂211', NULL, 61, '1下 祝丽倩 药剂211'),
('6306', '2上', '吴思辰', '药剂211', NULL, 62, '2上 吴思辰 药剂211'),
('6306', '2下', '姚冰荧', '药剂211', NULL, 63, '2下 姚冰荧 药剂211'),
('6307', '1上', '刘欢', '药剂211', NULL, 67, '1上 刘欢 药剂211'),
('6307', '1下', '代舒琴', '药剂211', NULL, 68, '1下 代舒琴 药剂211'),
('6307', '2上', '冉庆铃', '药剂211', NULL, 69, '2上 冉庆铃 药剂211'),
('6307', '2下', '金舒琪', '药剂211', NULL, 70, '2下 金舒琪 药剂211'),
('6309', '1上', '傅瑜菲', '药剂211', '升学', 74, '1上 傅瑜菲 药剂211 升学'),
('6309', '1下', '何苗苗', '机器人231', NULL, 75, '1下 何苗苗 机器人231'),
('6309', '2上', '何静', '机器人231', NULL, 76, '2上 何静 机器人231'),
('6309', '2下', '袁笑笑', '机器人231', NULL, 77, '2下 袁笑笑 机器人231'),
('6401', '1上', '四郎志玛', '烹饪221', NULL, 81, '1上 四郎志玛 烹饪221'),
('6401', '1下', '仁青拉姆', '烹饪221', NULL, 82, '1下 仁青拉姆 烹饪221'),
('6401', '2上', '多吉志玛', '烹饪221', NULL, 83, '2上 多吉志玛 烹饪221'),
('6401', '2下', NULL, NULL, NULL, 84, '2下'),
('6402', '1上', '倪诗余', '电商231', NULL, 88, '1上 倪诗余 电商231'),
('6402', '1下', '颜雨焓', '电商231', NULL, 89, '1下 颜雨焓 电商231'),
('6402', '2上', '龚艾雯', '电商231', NULL, 90, '2上 龚艾雯 电商231'),
('6402', '2下', '占佳琪', '电商231', NULL, 91, '2下 占佳琪 电商231'),
('6403', '1上', '任果果', '电商231', NULL, 95, '1上 任果果 电商231'),
('6403', '1下', '赖桦蓉', '电商231', NULL, 96, '1下 赖桦蓉 电商231'),
('6403', '2上', '顾晶晶', '电商231', NULL, 97, '2上 顾晶晶 电商231'),
('6403', '2下', '裘园园', '电商231', NULL, 98, '2下 裘园园 电商231'),
('6404', '1上', '张未茜', '电商231', NULL, 102, '1上 张未茜 电商231'),
('6404', '1下', '周美德', '电商231', NULL, 103, '1下 周美德 电商231'),
('6404', '2上', '孙喜悦', '电商231', NULL, 104, '2上 孙喜悦 电商231'),
('6404', '2下', '姜萌怡', '电商231', NULL, 105, '2下 姜萌怡 电商231'),
('6405', '1上', '张善媛', '电商231', NULL, 109, '1上 张善媛 电商231'),
('6405', '1下', '秦诗怡', '电商231', NULL, 110, '1下 秦诗怡 电商231'),
('6405', '2上', '沈张瑶', '电商231', NULL, 111, '2上 沈张瑶 电商231'),
('6405', '2下', '李玲', '电商231', NULL, 112, '2下 李玲 电商231'),
('6406', '1上', '项子婷', '电商231', NULL, 116, '1上 项子婷 电商231'),
('6406', '1下', '李静姝', '电商231', NULL, 117, '1下 李静姝 电商231'),
('6406', '2上', NULL, NULL, NULL, 118, '2上'),
('6406', '2下', '吴晓燕', '电商231', NULL, 119, '2下 吴晓燕 电商231'),
('6407', '1上', '邱夕颜', '药剂232', NULL, 123, '1上 邱夕颜 药剂232'),
('6407', '1下', '张欣雅', '中药232', NULL, 124, '1下 张欣雅 中药232'),
('6407', '2上', '郑佳妮', '中药251', NULL, 125, '2上 郑佳妮 中药251'),
('6407', '2下', '张雅琴', '西餐运营232', NULL, 126, '2下 张雅琴 西餐运营232')
;

DROP TEMPORARY TABLE IF EXISTS tmp_assign_norm;
CREATE TEMPORARY TABLE tmp_assign_norm AS
SELECT
    TRIM(room_code) AS room_code,
    TRIM(bed_name) AS bed_name,
    NULLIF(TRIM(student_name), '') AS student_name,
    CASE NULLIF(TRIM(SUBSTRING_INDEX(COALESCE(class_name_raw, ''), ' ', 1)), '')
        WHEN '数媒251' THEN '数字媒体251'
        WHEN '电商251' THEN '电子商务251'
        WHEN '电商252' THEN '电子商务252'
        WHEN '西餐运营211' THEN '西餐211'
        ELSE NULLIF(TRIM(SUBSTRING_INDEX(COALESCE(class_name_raw, ''), ' ', 1)), '')
    END AS class_name,
    NULLIF(TRIM(class_name_raw), '') AS class_name_raw,
    NULLIF(TRIM(remark), '') AS remark,
    source_line,
    raw_text
FROM tmp_assign_input_raw;

-- 1) 学生存在性检查（按姓名）
DROP TEMPORARY TABLE IF EXISTS tmp_student_precheck;
CREATE TEMPORARY TABLE tmp_student_precheck AS
SELECT
    t.room_code,
    t.bed_name,
    t.student_name,
    COUNT(s.id) AS student_match_count,
    GROUP_CONCAT(DISTINCT s.class_name ORDER BY s.class_name SEPARATOR ', ') AS db_class_names,
    GROUP_CONCAT(DISTINCT s.id ORDER BY s.id) AS student_ids,
    t.source_line,
    t.raw_text
FROM tmp_assign_norm t
LEFT JOIN xg_student_msg s
  ON s.deleted = 0
 AND s.name COLLATE utf8mb4_general_ci = t.student_name COLLATE utf8mb4_general_ci
WHERE t.student_name IS NOT NULL
GROUP BY t.room_code, t.bed_name, t.student_name, t.source_line, t.raw_text;

-- 2) 班级存在性检查（按班级名）
DROP TEMPORARY TABLE IF EXISTS tmp_class_precheck;
CREATE TEMPORARY TABLE tmp_class_precheck AS
SELECT
    t.class_name,
    COUNT(c.id) AS class_match_count,
    GROUP_CONCAT(DISTINCT c.id ORDER BY c.id) AS class_ids
FROM (SELECT DISTINCT class_name FROM tmp_assign_norm WHERE class_name IS NOT NULL) t
LEFT JOIN xg_class_msg c
  ON c.deleted = 0
 AND c.class_name COLLATE utf8mb4_general_ci = t.class_name COLLATE utf8mb4_general_ci
GROUP BY t.class_name;

-- 3) 学生-班级匹配检查（学生存在，但不在输入班级）
DROP TEMPORARY TABLE IF EXISTS tmp_student_class_precheck;
CREATE TEMPORARY TABLE tmp_student_class_precheck AS
SELECT
    t.room_code,
    t.bed_name,
    t.student_name,
    t.class_name AS input_class_name,
    COUNT(s_any.id) AS name_match_count,
    COUNT(s_exact.id) AS exact_match_count,
    GROUP_CONCAT(DISTINCT s_any.class_name ORDER BY s_any.class_name SEPARATOR ', ') AS db_class_names,
    t.source_line,
    t.raw_text
FROM tmp_assign_norm t
LEFT JOIN xg_student_msg s_any
  ON s_any.deleted = 0
 AND s_any.name COLLATE utf8mb4_general_ci = t.student_name COLLATE utf8mb4_general_ci
LEFT JOIN xg_student_msg s_exact
  ON s_exact.deleted = 0
 AND s_exact.name COLLATE utf8mb4_general_ci = t.student_name COLLATE utf8mb4_general_ci
 AND s_exact.class_name COLLATE utf8mb4_general_ci = t.class_name COLLATE utf8mb4_general_ci
WHERE t.student_name IS NOT NULL
  AND t.class_name IS NOT NULL
GROUP BY t.room_code, t.bed_name, t.student_name, t.class_name, t.source_line, t.raw_text;

-- 4) 统计总览：三类问题数量
SELECT
    (SELECT COUNT(*) FROM tmp_student_precheck WHERE student_match_count = 0) AS student_not_found_cnt,
    (SELECT COUNT(*) FROM tmp_class_precheck WHERE class_match_count = 0) AS class_not_found_cnt,
    (SELECT COUNT(*) FROM tmp_student_class_precheck WHERE name_match_count > 0 AND exact_match_count = 0) AS student_class_mismatch_cnt;

-- 5) 学生不存在明细
SELECT
    room_code,
    bed_name,
    student_name,
    source_line,
    raw_text
FROM tmp_student_precheck
WHERE student_match_count = 0
ORDER BY room_code, bed_name;

-- 6) 班级不存在明细
SELECT
    class_name
FROM tmp_class_precheck
WHERE class_match_count = 0
ORDER BY class_name;

-- 7) 学生和班级不匹配明细
SELECT
    room_code,
    bed_name,
    student_name,
    input_class_name,
    db_class_names,
    source_line,
    raw_text
FROM tmp_student_class_precheck
WHERE name_match_count > 0
  AND exact_match_count = 0
ORDER BY room_code, bed_name, student_name;