-- 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`));