all-project/backend/script/sql/xg_reset_accommodation_and_apply_bed.sql

-- 用途：
-- 1) 清空所有学生的住宿信息
-- 2) 将学籍状态为“在读(student_status=1)”的学生统一标记为“申请床位(is_boarder=1)”
--
-- 依据代码：
-- - 批量申请床位逻辑：xg_student_msg.is_boarder = 1
-- - 学籍状态在读：xg_student_msg.student_status = '1'
--
-- 执行前建议：
-- - 先在测试环境验证
-- - 如需限制租户，可在每条SQL追加: AND tenant_id = 0

START TRANSACTION;

-- =========================
-- Step 1: 删除/清空住宿信息
-- =========================

-- 1.1 清空学生表中的住宿字段，同时重置是否住校
UPDATE xg_student_msg
SET
    dormitory_building = NULL,
    dormitory_room = NULL,
    ext_field1 = NULL,      -- 床位ID
    is_boarder = 0,
    update_time = NOW()
WHERE deleted = 0;

-- 1.2 逻辑删除床位-学生分配关系（相当于删除住宿分配记录）
UPDATE xg_dorm_bed_student
SET
    deleted = '1',
    update_time = NOW()
WHERE deleted = '0';

-- 1.3 重置床位状态为空闲
UPDATE xg_dorm_bed
SET
    bed_status = '0',
    update_time = NOW()
WHERE deleted = '0';

-- =========================
-- Step 2: 在读学生统一申请床位
-- =========================

-- 系统“批量申请床位”对应逻辑：仅更新 is_boarder = 1
UPDATE xg_student_msg
SET
    is_boarder = 1,
    update_time = NOW()
WHERE deleted = 0
  AND student_status = '1';

COMMIT;

-- 可选核对SQL（执行后可单独运行）
-- SELECT COUNT(1) AS boarding_apply_count FROM xg_student_msg WHERE deleted = 0 AND student_status = '1' AND is_boarder = 1;
-- SELECT COUNT(1) AS active_bed_rel_count FROM xg_dorm_bed_student WHERE deleted = '0';
-- SELECT COUNT(1) AS occupied_bed_count FROM xg_dorm_bed WHERE deleted = '0' AND bed_status = '1';