CREATE DATABASE school;
USE school;

CREATE TABLE sch_fire_alarm(
	id INT PRIMARY KEY AUTO_INCREMENT,
	`type` VARCHAR(10) NOT NULL,
	`date` DATETIME,
	img_path VARCHAR(200),
	number VARCHAR(100),
	`name` VARCHAR(100) DEFAULT 'null',
	confidence DECIMAL(5,3),
	KEY `time` (`date`)
	
)ENGINE INNODB DEFAULT CHARSET=utf8;

CREATE TABLE sch_user(
	id INT PRIMARY KEY AUTO_INCREMENT,
	`username` VARCHAR(100) NOT NULL UNIQUE,
	`password` VARCHAR(100),
	role VARCHAR(10) DEFAULT 'user',
	email VARCHAR(50)
	
)ENGINE INNODB DEFAULT CHARSET=utf8;

CREATE TABLE sch_people_alarm(
	id INT PRIMARY KEY AUTO_INCREMENT,
	`type` VARCHAR(10) NOT NULL,
	`date` DATETIME,
	img_path VARCHAR(200),
	number VARCHAR(100),
	`name` VARCHAR(100) DEFAULT 'null',
	confidence DECIMAL(5,3),
	KEY `time` (`date`)
)ENGINE INNODB DEFAULT CHARSET=utf8;

CREATE TABLE sch_edge_alarm(
	id INT PRIMARY KEY AUTO_INCREMENT,
	`type` VARCHAR(10) NOT NULL,
	`date` DATETIME,
	img_path VARCHAR(200),
	number VARCHAR(100),
	`name` VARCHAR(100) DEFAULT 'null',
	confidence DECIMAL(5,3),
	KEY `time` (`date`)
	
)ENGINE INNODB DEFAULT CHARSET=utf8;

CREATE TABLE sch_kitchen_alarm(
	id INT PRIMARY KEY AUTO_INCREMENT,
	`type` VARCHAR(10) NOT NULL,
	`date` DATETIME,
	img_path VARCHAR(200),
	number VARCHAR(100),
	`name` VARCHAR(100) DEFAULT 'null',
	confidence DECIMAL(5,3),
	KEY `time` (`date`)
	
)ENGINE INNODB DEFAULT CHARSET=utf8;

CREATE TABLE sch_action_alarm(
	id INT PRIMARY KEY AUTO_INCREMENT,
	`type` VARCHAR(10) NOT NULL,
	`date` DATETIME,
	img_path VARCHAR(200),
	number VARCHAR(100),
	`name` VARCHAR(100) DEFAULT 'null',
	confidence DECIMAL(5,3),
	KEY `time` (`date`)
	
)ENGINE INNODB DEFAULT CHARSET=utf8;

#alter table `sch_user` add unique (`username`);

#explain SELECT * FROM `sch_user` WHERE username ='admin';

#show index from `sch_user`;
#设备状态
CREATE TABLE sch_device(
	device_id BIGINT PRIMARY KEY, #唯一标识
	control INT DEFAULT 0,#0关1开
	model INT DEFAULT 0,#切换模式
	on_time DATETIME, #上线时间
	down_time DATETIME, # 下线时间
	device_name VARCHAR(50),
	device_longitude DECIMAL(10,6),
	device_latitude DECIMAL(10,6)
	
)ENGINE INNODB DEFAULT CHARSET=utf8;


#摄像头信息
CREATE TABLE sch_camera(
	number VARCHAR(100) PRIMARY KEY, #一个number对应多条历史记录,由设备id + 摄像头id组成
	control INT DEFAULT 0,#0关1开
	on_time DATETIME, #上线时间
	down_time DATETIME, #下线时间
	camera_type VARCHAR(20),
	camera_name VARCHAR(20),
	camera_width INT,
	camera_height INT,
	camera_FPS INT,
	camera_longitude DECIMAL(10,6),
	camera_latitude DECIMAL(10,6),
	device_id BIGINT #一个设备id对应多个摄像头
)ENGINE INNODB DEFAULT CHARSET=utf8;

CREATE TABLE test(
	`name` VARCHAR(10)
)ENGINE INNODB DEFAULT CHARSET=utf8;