dormitory_manage/sql/init.sql

-- ============================================================
-- 宿舍管理系统 - 数据库初始化脚本
-- 数据库: dormitory_manage
-- ============================================================

CREATE DATABASE IF NOT EXISTS dormitory_manage DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE dormitory_manage;

-- ============================================================
-- 用户表
-- ============================================================
CREATE TABLE user (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    name VARCHAR(50) NOT NULL,
    gender TINYINT DEFAULT 0 COMMENT '0-女, 1-男',
    phone VARCHAR(20),
    role TINYINT NOT NULL DEFAULT 1 COMMENT '0-管理员, 1-学生',
    student_id VARCHAR(50) UNIQUE COMMENT '学号',
    avatar VARCHAR(255) COMMENT '头像路径',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

-- ============================================================
-- 宿舍楼表
-- ============================================================
CREATE TABLE dormitory_building (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL COMMENT '楼栋名称',
    code VARCHAR(20) COMMENT '楼栋编号',
    gender TINYINT DEFAULT 2 COMMENT '0-女, 1-男, 2-混合',
    floor_count INT DEFAULT 6 COMMENT '楼层数',
    description VARCHAR(500) COMMENT '备注',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='宿舍楼表';

-- ============================================================
-- 房间表
-- ============================================================
CREATE TABLE room (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    building_id BIGINT NOT NULL COMMENT '所属楼栋',
    room_number VARCHAR(20) NOT NULL COMMENT '房间号',
    floor INT COMMENT '所在楼层',
    capacity INT NOT NULL COMMENT '总床位数',
    gender TINYINT COMMENT '可覆盖楼栋性别限制',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_building_id (building_id),
    UNIQUE KEY uk_building_room (building_id, room_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='房间表';

-- ============================================================
-- 床位表
-- ============================================================
CREATE TABLE bed (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    room_id BIGINT NOT NULL COMMENT '所属房间',
    bed_number VARCHAR(10) NOT NULL COMMENT '床位编号',
    status TINYINT DEFAULT 0 COMMENT '0-空闲, 1-占用, 2-维修',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_room_id (room_id),
    UNIQUE KEY uk_room_bed (room_id, bed_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='床位表';

-- ============================================================
-- 分配记录表（中间表）
-- ============================================================
CREATE TABLE assignment (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL COMMENT '学生用户',
    bed_id BIGINT NOT NULL COMMENT '分配的床位',
    assign_date DATE NOT NULL COMMENT '入住日期',
    end_date DATE COMMENT '迁出日期, NULL=当前住宿',
    status TINYINT DEFAULT 0 COMMENT '0-当前住宿, 1-已迁出',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),
    INDEX idx_bed_id (bed_id),
    INDEX idx_user_status (user_id, status),
    INDEX idx_bed_status (bed_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='分配记录表';

-- ============================================================
-- 测试数据
-- ============================================================

-- 管理员账号 (密码: admin123, MD5: 21232f297a57a5a743894a0e4a801fc3)
INSERT INTO user (username, password, name, role) VALUES
('admin', '21232f297a57a5a743894a0e4a801fc3', '系统管理员', 0);

-- 学生账号 (密码: 123456, MD5: e10adc3949ba59abbe56e057f20f883e)
INSERT INTO user (username, password, name, gender, role, student_id) VALUES
('zhangsan', 'e10adc3949ba59abbe56e057f20f883e', '张三', 1, 1, '2024001'),
('lisi', 'e10adc3949ba59abbe56e057f20f883e', '李四', 1, 1, '2024002'),
('wangwu', 'e10adc3949ba59abbe56e057f20f883e', '王五', 1, 1, '2024003');

-- 宿舍楼
INSERT INTO dormitory_building (name, code, gender, floor_count) VALUES
('梅苑A栋', 'MA', 1, 6),
('兰苑B栋', 'LB', 0, 6),
('竹苑C栋', 'ZC', 2, 5);

-- 房间（梅苑A栋 101~103）
INSERT INTO room (building_id, room_number, floor, capacity, gender) VALUES
(1, '101', 1, 4, 1),
(1, '102', 1, 4, 1),
(1, '103', 1, 4, 1);

-- 床位（101 房间 4 个床位）
INSERT INTO bed (room_id, bed_number, status) VALUES
(1, 'A', 0), (1, 'B', 0), (1, 'C', 0), (1, 'D', 0);