drop database ganzi;
CREATE database if NOT EXISTS ganzi default character set utf8 collate utf8_general_ci;
use ganzi;

create table sys_users (
  id bigint auto_increment comment '编号',
  username varchar(100) comment '用户名',
  password varchar(100) comment '密码',
  salt varchar(100) comment '盐值',
  random varchar(100) comment '随机数',
  role_id varchar(50) comment '角色列表',
  locked int(2) default 0 comment '是否锁定  1锁定',
  constraint pk_sys_users primary key(id)
) charset=utf8 ENGINE=InnoDB;
create unique index idx_sys_users_username on sys_users(username);

create table sys_roles (
  id bigint auto_increment comment '角色编号',
  role varchar(100) comment '角色名称',
  description varchar(100) comment '角色描述',
  pid bigint comment '父节点',
  available int(2) default '0' comment '是否锁定 1锁定',
  constraint pk_sys_roles primary key(id)
) charset=utf8 ENGINE=InnoDB;
create unique index idx_sys_roles_role on sys_roles(role);

create table sys_permissions (
  id bigint auto_increment comment '编号',
  permission varchar(100) comment '权限编号',
  description varchar(100) comment '权限描述',
  rid bigint comment '此权限关联角色的id',
  available int(2) default '0' comment '是否锁定 1锁定',
  constraint pk_sys_permissions primary key(id)
) charset=utf8 ENGINE=InnoDB;
create unique index idx_sys_permissions_permission on sys_permissions(permission);

create table sys_users_roles (
  id  bigint auto_increment comment '编号',
  user_id bigint comment '用户编号',
  role_id bigint comment '角色编号',
  constraint pk_sys_users_roles primary key(id)
) charset=utf8 ENGINE=InnoDB;

create table sys_roles_permissions (
  id bigint auto_increment comment '编号',
  role_id bigint comment '角色编号',
  permission_id bigint comment '权限编号',
  constraint pk_sys_roles_permissions primary key(id)
) charset=utf8 ENGINE=InnoDB;

create table user_log(
  id bigint NOT NULL auto_increment comment '编号',
  user_id bigint comment '用户编号',
  information text NOT NULL COMMENT '描述信息',
  createTime timestamp NOT NULL COMMENT '创建时间',
  primary key(id)
)charset=utf8 ENGINE=InnoDB;

create table control_log(
  id bigint NOT NULL auto_increment comment '编号',
  user_id bigint NOT NULL comment '用户编号',
  school_id bigint NOT NULL comment '学校编号',
  school_name varchar(100) NOT NULL comment '学校名称',
  collect_number bigint NOT NULL comment '采集器编号', 
  fan_number int(5) comment '盘管编号', 
  send_type tinyint(4) NOT NULL DEFAULT '0' COMMENT '发送类型：0-控制一台、1-控制全部',
  control_type tinyint(4) NOT NULL DEFAULT '0' COMMENT '控制类型：-1-查询学校在线、0-查询盘管状态数据、1-开关机、2-设置风速',
  send_state tinyint(4) NOT NULL DEFAULT '0' COMMENT '发送状态：1-已发送、2-发送失败',
  information text NOT NULL COMMENT '指令内容',
  state_information text NOT NULL COMMENT '发送状态描述信息,包含部分失败的具体原因',
  createTime bigint NOT NULL COMMENT '创建时间',
  info_id varchar(100) NOT NULL comment '消息id',
  primary key(id),
  INDEX user_find (user_id,createTime,send_state )
)charset=utf8 ENGINE=InnoDB;

create table system_log(
  id bigint NOT NULL auto_increment comment '编号',
  information text NOT NULL COMMENT '描述信息',
  createTime timestamp  NOT NULL COMMENT '创建时间',
  primary key(id),
  INDEX create_time (createTime)
)charset=utf8 ENGINE=InnoDB;

create table school_information(
  id bigint NOT NULL auto_increment comment '编号',
  school_name varchar(100) comment '学校名称',
  school_number int(5) comment '学校编号', 
  information text NOT NULL COMMENT '描述信息',
  createTime timestamp NOT NULL COMMENT '创建时间',
  channelName varchar(100) comment '通道名称',
  locked int(2) NOT NULL default '1' comment '是否锁定  2锁定',
  isonline  int(2) NOT NULL DEFAULT '1' comment '在线状态 1-在线  2-离线',
  primary key(id),
  unique index s_number (school_number),
  unique index s_channel (channelName)
)charset=utf8 ENGINE=InnoDB;

create table sys_users_school (
  id  bigint auto_increment comment '编号',
  user_id bigint comment '用户编号',
  school_id bigint comment '学校编号',
  primary key(id)
) charset=utf8 ENGINE=InnoDB;

create table fan_information(
  id bigint NOT NULL auto_increment comment '编号',
  school_id bigint NOT NULL comment '学校编号',
  collect_id bigint NOT NULL comment '采集器编号', 
  fan_number int(5) comment '盘管编号', 
  information text NOT NULL COMMENT '描述信息',
  floor int(2) NOT NULL COMMENT '楼层号',
  room varchar(10) NOT NULL DEFAULT '1'COMMENT '房间号',
  createTime timestamp NOT NULL COMMENT '创建时间',
  primary key(id),
  INDEX school_floor (school_id,floor),
  INDEX school_collect (school_id,collect_id)
)charset=utf8 ENGINE=InnoDB;

create table fan_state(
   id bigint NOT NULL auto_increment comment '编号',
   new_runtime bigint NOT NULL DEFAULT '0.00' comment '新风运行时间',
   kt_runtime bigint NOT NULL DEFAULT '0.00' comment '空调运行时间',
   isonline  int(2) NOT NULL DEFAULT '1' comment '在线状态 1-在线  2-离线',
   fan_id bigint NOT NULL comment '盘管的编号',
   switchgear  int(2) NOT NULL DEFAULT '1' comment '开关机',
   model  int(2) NOT NULL DEFAULT '1' comment '模式',
   wind_speed  int(2) NOT NULL DEFAULT '1' comment '风速',
   circulate  int(2) NOT NULL DEFAULT '1' comment '循环',
   temperature  int(2) NOT NULL DEFAULT '15' comment '温度',
   update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY (id),
   unique INDEX fan_id (fan_id)
) charset=utf8 ENGINE=InnoDB;

create table fan_collect(
  id bigint NOT NULL auto_increment comment '编号',
  school_id bigint NOT NULL comment '学校编号',
  collect_number int(5) NOT NULL comment '采集器编号',
  ip varchar(25) NOT NULL comment 'ip地址',
  all_count varchar(10) DEFAULT NULL comment '采集下的总数',
  addrlist varchar(300) DEFAULT NULL comment '盘管地址列表',
  isonline int(2) NOT NULL DEFAULT '1' comment '1-在线  2-离线',
  update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  primary key(id),
  INDEX school_number (school_id,collect_number)
)charset=utf8 ENGINE=InnoDB;


create table collect_information_log(
  id bigint NOT NULL auto_increment comment '编号',
  fan_id bigint NOT NULL comment '盘管的编号',
  information text NOT NULL comment '具体的状态信息',
  time_point bigint NOT NULL comment '创建时间',
  primary key(id),
  INDEX fan_id_time (fan_id,time_point)
)charset=utf8 ENGINE=InnoDB;