SET NAMES 'utf8mb4';
SET FOREIGN_KEY_CHECKS = 0;
SET FOREIGN_KEY_CHECKS = 1;

/*人*/

DROP TABLE IF EXISTS admin;
-- 超管
CREATE TABLE admin (
username varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
password varchar(255) DEFAULT NULL,
name varchar(255) DEFAULT NULL,
gender varchar(255) DEFAULT NULL,
age int DEFAULT NULL,
phone_num varchar(255) DEFAULT NULL,
email varchar(255) DEFAULT NULL,
avatar varchar(255) DEFAULT NULL,
PRIMARY KEY (username) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

DROP TABLE IF EXISTS dorm_manager;
-- 宿管
CREATE TABLE dorm_manager (
username varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
password varchar(255) DEFAULT NULL,
dormbuild_id int DEFAULT NULL,
name varchar(255) DEFAULT NULL,
gender varchar(255) DEFAULT NULL,
age int DEFAULT NULL,
phone_num varchar(255) DEFAULT NULL,
email varchar(255) DEFAULT NULL,
avatar varchar(255) DEFAULT NULL,
PRIMARY KEY (username) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

DROP TABLE IF EXISTS student;
-- 学生
CREATE TABLE student (
id int NOT NULL AUTO_INCREMENT,
username varchar(255) DEFAULT NULL,
password varchar(255) DEFAULT NULL,
name varchar(255) DEFAULT NULL,
age int DEFAULT NULL,
gender varchar(255) DEFAULT NULL,
phone_num varchar(255) DEFAULT NULL,
email varchar(255) DEFAULT NULL,
avatar varchar(255) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

DROP TABLE IF EXISTS visitor;
-- 访客
CREATE TABLE visitor (
id int NOT NULL AUTO_INCREMENT,
name varchar(255) DEFAULT NULL,
gender varchar(255) DEFAULT NULL,
phone_num varchar(255) DEFAULT NULL,
origin_city varchar(255) DEFAULT NULL,
visit_time varchar(255) DEFAULT NULL,
content varchar(255) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

DROP TABLE IF EXISTS user;
-- 用户
CREATE TABLE user (
id int NOT NULL AUTO_INCREMENT,
username varchar(255) DEFAULT NULL,
password varchar(255) DEFAULT NULL,
identity varchar(255) DEFAULT NULL,
avatar varchar(255) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

/*非人*/

DROP TABLE IF EXISTS dorm_build;
-- 宿舍楼
CREATE TABLE dorm_build (
id int NOT NULL AUTO_INCREMENT,
dormbuild_id int DEFAULT NULL,
dormbuild_name varchar(255) DEFAULT NULL,
dormbuild_detail varchar(255) DEFAULT NULL,
PRIMARY KEY (id) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

DROP TABLE IF EXISTS dorm_room;
-- 寝室
CREATE TABLE dorm_room (
dormroom_id int NOT NULL,
dormbuild_id int DEFAULT NULL,
floor_num int DEFAULT NULL,
max_capacity int DEFAULT NULL,
current_capacity int DEFAULT NULL,
first_bed varchar(255) DEFAULT NULL,
second_bed varchar(255) DEFAULT NULL,
third_bed varchar(255) DEFAULT NULL,
fourth_bed varchar(255) DEFAULT NULL,
PRIMARY KEY (dormroom_id) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

DROP TABLE IF EXISTS adjust_room;
-- 调宿
CREATE TABLE adjust_room (
id int NOT NULL AUTO_INCREMENT,
username varchar(255) DEFAULT NULL,
name varchar(255) DEFAULT NULL,
currentroom_id int DEFAULT NULL,
currentbed_id int DEFAULT NULL,
towardsroom_id int DEFAULT NULL,
towardsbed_id int DEFAULT NULL,
state varchar(255) DEFAULT NULL,
apply_time varchar(255) DEFAULT NULL,
finish_time varchar(255) DEFAULT NULL,
PRIMARY KEY (id) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

DROP TABLE IF EXISTS repair;
-- 保修
CREATE TABLE repair (
id int NOT NULL AUTO_INCREMENT,
repairer varchar(255) DEFAULT NULL,
dormbuild_id int NOT NULL,
dormroom_id int NOT NULL,
title varchar(255) DEFAULT NULL,
content varchar(255) DEFAULT NULL,
state varchar(255) DEFAULT NULL,
order_buildtime datetime DEFAULT NULL,
order_finishtime datetime DEFAULT NULL,
PRIMARY KEY (id,dormbuild_id,dormroom_id)
) ENGINE=InnoDB AUTO_INCREMENT=1258344450 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

DROP TABLE IF EXISTS notice;
-- 通知
CREATE TABLE notice (
id int NOT NULL AUTO_INCREMENT,
title varchar(255) DEFAULT NULL,
content varchar(255) DEFAULT NULL,
author varchar(255) DEFAULT NULL,
release_time varchar(255) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

select * from admin;