-- 建立hotelbook数据库
CREATE SCHEMA `hotelbook` DEFAULT CHARACTER SET utf8 ;

-- 建立登录表
CREATE TABLE `hotelbook`.`login` (
   `loginId` INT NOT NULL AUTO_INCREMENT COMMENT '登录内部标示，主键、自动增长。',
   `loginName` VARCHAR(45) NOT NULL COMMENT '登录用户名，前台唯一性的标识。不能重复。',
   `loginPwd` VARCHAR(45) NOT NULL COMMENT '登录用户的密码，不能为空。',
   `loginNickName` VARCHAR(45) NULL COMMENT '登录用户的昵称' ,
   `loginAdmin` INT NULL COMMENT '权限，默认最高权限是0',
   PRIMARY KEY (`loginId`),
   UNIQUE INDEX `loginName_UNIQUE` (`loginName` ASC));

-- 为login表插入默认的管理员用户
INSERT INTO `hotelbook`.`login` (`loginName`,`loginPwd`,`loginNickName`,`loginAdmin`) VALUE ("root","eySvyLyA5UjWbE5/9yFxxQ==","管理员",0);

-- 建立日志表
CREATE TABLE `hotelbook`.`logInfo` (
   `logId` INT NOT NULL AUTO_INCREMENT COMMENT '日志，主键、自动增长。',
   `logName` VARCHAR(45) NOT NULL COMMENT '日志项目',
   `loginId` INT NOT NULL COMMENT '用户 外键 login表字段值',
   `loginName` VARCHAR(45)  NULL COMMENT '用户名称',
   `logDate` VARCHAR(45) NULL COMMENT '日志时间' ,
   PRIMARY KEY (`logId`),
   CONSTRAINT `fk_logInfo_1`
    FOREIGN KEY (`loginId`)
    REFERENCES `hotelbook`.`login` (`loginId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

-- 创建主体数据表
-- 核心数据库内容
-- 将不使用存储过程，仅仅用到外键/外联
-- 数据维护主要依靠后端维护

-- 楼层信息
CREATE TABLE `hotelbook`.`floorInfo` (
  `floorId` INT NOT NULL AUTO_INCREMENT COMMENT '楼层编号',
  `floorName` VARCHAR(45) NULL COMMENT '楼层名称',
  PRIMARY KEY (`floorId`));

-- 客房类型
CREATE TABLE `hotelbook`.`roomType` (
  `typeId` VARCHAR(45) NOT NULL COMMENT '类型编号',
  `typeName` VARCHAR(45) NULL COMMENT '类型名称',
  `price` VARCHAR(20) NULL COMMENT '价格',
  `splicPrice` VARCHAR(20) NULL COMMENT '拼房价格',
  `exceedance` INT NULL COMMENT '可超预定数',
  `isSplice` VARCHAR(10) NULL COMMENT '是否可拼房',
  PRIMARY KEY (`typeId`));

-- 客房信息
CREATE TABLE `hotelbook`.`roomInfo` (
  `roomId` VARCHAR(45) NOT NULL COMMENT '客房编号',
  `typeId` VARCHAR(45) NOT NULL COMMENT '类型编号',
  `floorId` INT NOT NULL COMMENT '楼层编号',
  `ratedNum` INT NULL COMMENT '额定人数',
  `bedNum` INT NULL COMMENT '床数',
  `roomDescription` VARCHAR(45) NULL COMMENT '客房描述',
  `remark` VARCHAR(100) NULL COMMENT '备注',
  `status` VARCHAR(10) NULL COMMENT '状态',
  `isSplice` VARCHAR(10) NULL COMMENT '是否可拼房',
  PRIMARY KEY (`roomId`),
  INDEX `fk_roomInfo_1_idx` (`typeId` ASC),
  INDEX `fk_roomInfo_2_idx` (`floorId` ASC),
  CONSTRAINT `fk_roomInfo_1`
    FOREIGN KEY (`typeId`)
    REFERENCES `hotelbook`.`roomType` (`typeId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_roomInfo_2`
    FOREIGN KEY (`floorId`)
    REFERENCES `hotelbook`.`floorInfo` (`floorId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

-- 预订单
CREATE TABLE `hotelbook`.`orderInfo` (
  `orderId` VARCHAR(45) NOT NULL COMMENT '预定单号',
  `orderName` VARCHAR(45) NULL COMMENT '预定人',
  `orderPhone` VARCHAR(45) NULL COMMENT '联系电话',
  `orderIDcard` VARCHAR(18) NULL COMMENT '身份证号',
  `typeId` VARCHAR(45) NOT NULL COMMENT '客房类型',
  `arrireDate` VARCHAR(45) NULL COMMENT '抵店时间',
  `leaveDate` VARCHAR(45) NULL COMMENT '离店时间',
  `orderState` VARCHAR(20) NULL COMMENT '单据状态',
  `checkNum` VARCHAR(45) NULL COMMENT '入住人数',
  `roomId` VARCHAR(45) NULL COMMENT '客房编号',
  `price` VARCHAR(20) NULL COMMENT '客房价格',
  `checkPrice` VARCHAR(20) NULL COMMENT '入住价格',
  `discount` INT NULL COMMENT '折扣',
  `discountReason` VARCHAR(60) NULL COMMENT '折扣原因',
  `addBed` VARCHAR(10) NULL COMMENT '是否加床',
  `addBedPrice` VARCHAR(20) NULL COMMENT '加床价格',
  `orderMoney` VARCHAR(20) NULL COMMENT '预收款',
  `remark` VARCHAR(500) NULL COMMENT '备注',
  `operatorId` VARCHAR(45) NULL COMMENT '操作员',
  PRIMARY KEY (`orderId`),
  INDEX `fk_orderInfo_1_idx` (`typeId` ASC),
  CONSTRAINT `fk_orderInfo_1`
    FOREIGN KEY (`typeId`)
    REFERENCES `hotelbook`.`roomType` (`typeId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

-- 入住单
CREATE TABLE `hotelbook`.`checkInInfo` (
  `checkId` VARCHAR(45) NOT NULL COMMENT '入住单号',
  `orderId` VARCHAR(45) NULL COMMENT '预定单号',
  `checkName` VARCHAR(45) NULL COMMENT '入住人',
  `checkPhone` VARCHAR(45) NULL COMMENT '联系电话',
  `checkIDcard` VARCHAR(18) NULL COMMENT '身份证号',
  `typeId` VARCHAR(45) NOT NULL COMMENT '客房类型',
  `arrireTime` VARCHAR(45) NULL COMMENT '抵店时间',
  `leaveTime` VARCHAR(45) NULL COMMENT '离店时间',
  `checkState` VARCHAR(20) NULL COMMENT '单据状态',
  `checkNum` INT NULL COMMENT '入住人数',
  `roomId` VARCHAR(45) NOT NULL COMMENT '客房编号',
  `price` VARCHAR(20) NULL COMMENT '客房价格',
  `checkPrice` VARCHAR(20) NULL COMMENT '入住价格',
  `discount` INT NULL COMMENT '折扣',
  `discountReason` VARCHAR(60) NULL COMMENT '折扣原因',
  `addBed` VARCHAR(10) NULL COMMENT '是否加床',
  `addBedPrice` VARCHAR(20) NULL COMMENT '加床价格',
  `orderMoney` VARCHAR(20) NULL COMMENT '预收款',
  `money` VARCHAR(20) NULL COMMENT '应收账款',
  `isCheck` VARCHAR(10) NULL COMMENT '是否结账',
  `checkMoney` VARCHAR(20) NULL COMMENT '结账金额',
  `checkDate` VARCHAR(45) NULL COMMENT '结账日期',
  `remark` VARCHAR(500) NULL COMMENT '备注',
  `operatorId` VARCHAR(45) NULL COMMENT '操作员',
  PRIMARY KEY (`checkId`),
  INDEX `fk_checkInfo_1_idx` (`typeId` ASC),
  INDEX `fk_checkInfo_2_idx` (`roomId` ASC),
  CONSTRAINT `fk_checkInfo_1`
    FOREIGN KEY (`typeId`)
    REFERENCES `hotelbook`.`roomType` (`typeId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_checkInfo_2`
    FOREIGN KEY (`roomId`)
    REFERENCES `hotelbook`.`roomInfo` (`roomId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

-- 账单明细
CREATE TABLE `hotelbook`.`billInfo` (
  `billId` INT NOT NULL COMMENT '账单编号',
  `checkId` VARCHAR(45) NOT NULL COMMENT '入住单号',
  `costMoney` VARCHAR(20) NULL COMMENT '消费金额',
  `costDate` VARCHAR(45) NULL COMMENT '消费时间',
  `remark` VARCHAR(100) NULL COMMENT '备注',
  PRIMARY KEY (`billId`),
  INDEX `fk_billInfo_1_idx` (`checkId` ASC),
  CONSTRAINT `fk_billInfo_1`
    FOREIGN KEY (`checkId`)
    REFERENCES `hotelbook`.`checkInInfo` (`checkId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

-- 权限管理
CREATE TABLE `hotelbook`.`authInfo` (
  `authId` INT NOT NULL AUTO_INCREMENT COMMENT '权限编号',
  `authItem` VARCHAR(45) NULL COMMENT '权限项',
  `isRead` VARCHAR(45) NULL COMMENT '可读',
  `isWrite` VARCHAR(45) NULL COMMENT '可写',
  `isChange` VARCHAR(45) NULL COMMENT '可改',
  `isDelete` VARCHAR(45) NULL COMMENT '可删',
  PRIMARY KEY (`authId`));