-- 西溪3号楼批量入库脚本（排除3个缺失学生）
-- 说明：
-- 1) 请先执行 xixi3_dorm_precheck.sql 中的数据装载部分（tmp_assign_input_raw + tmp_assign_norm）
-- 2) 本脚本会排除：倪俊麟、胡俊𤋮、马腾子乐
-- 3) 本脚本会写正式表：xg_dorm_bed_student / xg_dorm_bed / xg_student_msg

START TRANSACTION;

DROP TEMPORARY TABLE IF EXISTS tmp_assign_import_ready;
CREATE TEMPORARY TABLE tmp_assign_import_ready AS
SELECT
    t.room_code,
    t.bed_name,
    t.student_name,
    t.class_name,
    c.id AS class_id,
    c.college_id,
    c.major_id,
    s.id AS student_id,
    bld.id AS building_id,
    bld.area_id,
    bld.campus_id,
    rm.id AS room_id,
    bed.id AS bed_id,
    t.source_line,
    t.raw_text
FROM tmp_assign_norm 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
LEFT JOIN xg_student_msg s
  ON s.deleted = 0
 AND s.name COLLATE utf8mb4_general_ci = t.student_name COLLATE utf8mb4_general_ci
 AND s.class_name COLLATE utf8mb4_general_ci = t.class_name COLLATE utf8mb4_general_ci
LEFT JOIN xg_dorm_building bld
  ON bld.deleted = '0'
 AND bld.building_name = '西溪3号楼'
LEFT JOIN xg_dorm_room rm
  ON rm.deleted = '0'
 AND rm.building_id = bld.id
 AND rm.room_code COLLATE utf8mb4_general_ci = t.room_code COLLATE utf8mb4_general_ci
LEFT JOIN xg_dorm_bed bed
  ON bed.deleted = '0'
 AND bed.room_id = rm.id
 AND bed.bed_name COLLATE utf8mb4_general_ci = t.bed_name COLLATE utf8mb4_general_ci
WHERE t.student_name IS NOT NULL
  AND t.class_name IS NOT NULL
  AND t.student_name NOT IN ('倪俊麟', '胡俊𤋮', '马腾子乐');

DROP TEMPORARY TABLE IF EXISTS tmp_assign_import_final;
CREATE TEMPORARY TABLE tmp_assign_import_final AS
SELECT r.*
FROM tmp_assign_import_ready r
LEFT JOIN xg_dorm_bed_student bs_bed
  ON bs_bed.deleted = '0' AND bs_bed.status = '0' AND bs_bed.bed_id = r.bed_id
LEFT JOIN xg_dorm_bed_student bs_student
  ON bs_student.deleted = '0' AND bs_student.status = '0' AND bs_student.student_id = r.student_id
WHERE r.class_id IS NOT NULL
  AND r.student_id IS NOT NULL
  AND r.building_id IS NOT NULL
  AND r.room_id IS NOT NULL
  AND r.bed_id IS NOT NULL
  AND bs_bed.id IS NULL
  AND bs_student.id IS NULL;

SET @next_rel_id := (SELECT COALESCE(MAX(id), 0) FROM xg_dorm_bed_student);

INSERT INTO xg_dorm_bed_student (
    id, college_id, major_id, class_id, student_id, bed_id, room_id, building_id, area_id, campus_id,
    status, assign_time, checkout_time, deleted, tenant_id, create_by, create_dept, create_time, update_by, update_time, remark
)
SELECT
    (@next_rel_id := @next_rel_id + 1) AS id,
    college_id,
    major_id,
    class_id,
    student_id,
    bed_id,
    room_id,
    building_id,
    area_id,
    campus_id,
    '0' AS status,
    NOW() AS assign_time,
    NULL AS checkout_time,
    '0' AS deleted,
    0 AS tenant_id,
    NULL AS create_by,
    NULL AS create_dept,
    NOW() AS create_time,
    NULL AS update_by,
    NOW() AS update_time,
    CONCAT('西溪3号楼批量导入，source_line=', source_line) AS remark
FROM tmp_assign_import_final;

UPDATE xg_dorm_bed bed
JOIN tmp_assign_import_final i ON i.bed_id = bed.id
SET bed.bed_status = '1',
    bed.update_time = NOW()
WHERE bed.deleted = '0';

UPDATE xg_student_msg s
JOIN tmp_assign_import_final i ON i.student_id = s.id
SET s.is_boarder = 1,
    s.dormitory_building = CAST(i.building_id AS CHAR),
    s.dormitory_room = CAST(i.room_id AS CHAR),
    s.ext_field1 = CAST(i.bed_id AS CHAR),
    s.update_time = NOW()
WHERE s.deleted = 0;

SELECT
    (SELECT COUNT(*) FROM tmp_assign_import_ready) AS ready_cnt,
    (SELECT COUNT(*) FROM tmp_assign_import_final) AS imported_cnt;

COMMIT;