/*
Navicat MySQL Data Transfer

Source Server         : localhost_3306
Source Server Version : 50635
Source Host           : localhost:3306
Source Database       : conference_room

Target Server Type    : MYSQL
Target Server Version : 50635
File Encoding         : 65001

Date: 2019-02-12 22:28:13
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for reservation
-- ----------------------------
DROP TABLE IF EXISTS `reservation`;
CREATE TABLE `reservation` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `room_id` int(11) DEFAULT NULL,
  `user` varchar(32) DEFAULT NULL,
  `mobile` varchar(20) DEFAULT NULL,
  `date` date DEFAULT NULL,
  `begintime` time DEFAULT NULL,
  `endtime` time DEFAULT NULL,
  `mark` varchar(10) DEFAULT NULL,
  -- 主题
  `subject` varchar(60) DEFAULT NULL,
  -- 部门
  `department` varchar(60) DEFAULT NULL,
  -- 会议纪要
  `summary` varchar(5000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_reservation_room` (`room_id`),
  CONSTRAINT `fk_reservation_room` FOREIGN KEY (`room_id`) REFERENCES `room` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of reservation
-- ----------------------------




-- ---------------------------------------------------
-- Table structure for reservation_users--参会人员
-- ---------------------------------------------------

Drop TABLE IF EXISTS `reservation_users`;
CREATE Table `reservation_users`(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `reservation_id` int(11) NOT NULL,
  `user_id` varchar(32) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_reservation_users_reservation` (reservation_id),
  CONSTRAINT `fk_reservation_users_reservation` FOREIGN KEY (`reservation_id`) REFERENCES `reservation` (`id`),
  KEY `fk_reservation_users_user` (user_id),
  CONSTRAINT `fk_reservation_users_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
)ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of reservation_users
-- ----------------------------




-- ---------------------------------------------------
-- Table structure for reservation_resource--会议资料
-- ---------------------------------------------------
Drop TABLE IF EXISTS `reservation_resource`;
CREATE TABLE `reservation_resource` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `reservation_id` int(11) NOT NULL,
  -- 资料名称
  `name` varchar(60) NOT NULL,
  -- 资料路径
  `path` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_reservation_users_room` (reservation_id),
  CONSTRAINT `fk_reservation_users_room` FOREIGN KEY (`reservation_id`) REFERENCES `reservation` (`id`)
)ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of reservation_resource
-- ----------------------------



-- ----------------------------
-- Table structure for role
-- ----------------------------
DROP TABLE IF EXISTS `role`;
CREATE TABLE `role` (
  `role_id` int(11) NOT NULL,
  `role_name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of role
-- ----------------------------
 INSERT INTO `role` VALUES ('1', 'admin');
 INSERT INTO `role` VALUES ('2', 'ordinary');

-- ----------------------------
-- Table structure for room
-- ----------------------------

DROP TABLE IF EXISTS `room`;
CREATE TABLE `room` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(60) NOT NULL,
  `message` varchar(200) DEFAULT NULL,
  -- 物理位置
  `position` varchar(200) DEFAULT NULL,
  -- 会议室图片
  `image` varchar(200) DEFAULT NULL,
  -- 容量
  `capacity` int(11) DEFAULT NULL,
  -- 会议室区域
  `region` varchar(60) DEFAULT NULL,
  -- 会议室二维码
  `code` varchar(60) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of room
-- ----------------------------

INSERT INTO `room`(id,name,message) VALUES ('1', 'Cpeco会议室', '小会议室，适合十人小型会议，含多媒体');
INSERT INTO `room`(id,name,message) VALUES ('2', 'Meos会议室', '大型会议室，可使用多媒体，适合开会、讲座');
INSERT INTO `room`(id,name,message) VALUES ('3', 'Wellzoom会议室', '小会议室，适合十人小型会议，含多媒体');
INSERT INTO `room`(id,name,message) VALUES ('4', '上格云大会议室', '大型会议室，可使用多媒体，适合开会、讲座');
INSERT INTO `room`(id,name,message) VALUES ('5', '尚格云小会议室', '小会议室，适合十人小型会议，含多媒体');
INSERT INTO `room`(id,name,message) VALUES ('6', 'Beecom会议室', '小会议室，适合十人小型会议，含多媒体');


-- ----------------------------------------
-- Table structure for Facility--会议室设备
-- ----------------------------------------
Drop TABLE IF EXISTS `facility`;
CREATE Table `facility`(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `room_id` int(11) NOT NULL,
  `facility_name` varchar(60) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_facility_room` (`room_id`),
  CONSTRAINT `fk_facility_room` FOREIGN KEY (`room_id`) REFERENCES `room` (`id`)
)ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of Facility
-- ----------------------------




-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` varchar(32) NOT NULL,
  `password` varchar(32) NOT NULL,
  `role` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_user_role` (`role`),
  CONSTRAINT `fk_user_role` FOREIGN KEY (`role`) REFERENCES `role` (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user
-- ----------------------------
 INSERT INTO `user` VALUES ('201801', '123', '2');
 INSERT INTO `user` VALUES ('admin', 'emadmin', '1');
 INSERT INTO `user` VALUES ('ordinary', '123456', '2');

-- ----------------------------------------
-- Table structure for roomauthority--会议室权限
-- ----------------------------------------
Drop TABLE IF EXISTS `room_authority`;
CREATE Table `room_authority`(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `room_id` int(11) NOT NULL,
  `user_id` varchar(32) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_authority_room` (`room_id`),
  CONSTRAINT `fk_authority_room` FOREIGN KEY (`room_id`) REFERENCES `room` (`id`),
  KEY `fk_authority_user` (user_id),
  CONSTRAINT `fk_authority_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
)ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of roomauthority
-- ----------------------------



