create Database BoatHouse;
use BoatHouse;
DROP TABLE IF EXISTS `FoodCategory`;
create table FoodCategory ( Id int auto_increment, Name nvarchar(50) null, Description nvarchar(1000) null, constraint FoodCategory_pk primary key (Id) );
CREATE TABLE Food (
	Id INT auto_increment,
	CategoryId INT NOT NULL,
	Name nvarchar ( 50 ) NOT NULL,
	Price DECIMAL ( 5, 2 ) NOT NULL DEFAULT 0,
	Description nvarchar ( 1000 ) NULL,
	Picture nvarchar ( 1000 ) NULL,
CONSTRAINT food_pk PRIMARY KEY ( Id ),
CONSTRAINT food_category_fk FOREIGN KEY ( CategoryId ) REFERENCES FoodCategory ( Id ));

DROP TABLE IF EXISTS `join_us`;
CREATE TABLE `join_us`
(
    `id`       int(11) NOT NULL AUTO_INCREMENT,
    `name`     varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci   DEFAULT NULL,
    `telephone` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci   DEFAULT NULL,
    `comment`  varchar(1000) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4; 

DROP TABLE IF EXISTS `intropage`;
CREATE TABLE `intropage` (
  `page_id` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `page_title` varchar(500) DEFAULT NULL,
  `page_api_url` varchar(500) DEFAULT NULL,
  `text` text,
  `image` text,
  `deleted` bit(1) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
   PRIMARY KEY (`page_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `intropage` VALUES ('intro', 'this is page title', 'http://www.baidu.com', '[\" <div>第一段  文字</div> \",\"<html> <p>第二段文字</p></html> \"]', '  [\"http://www.baidu.com\",\"https://www.cnblogs.com\"]', '\0', '2020-03-05 12:07:18', '2020-03-05 12:07:18');

DROP TABLE IF EXISTS user;
CREATE TABLE user
(
    id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    account VARCHAR(30) NULL DEFAULT NULL COMMENT '姓名',
    password VARCHAR(100) NULL DEFAULT NULL COMMENT '密码',
    age INT(11) NULL DEFAULT NULL COMMENT '年龄',
    email VARCHAR(50) NULL DEFAULT NULL COMMENT '邮箱',
    PRIMARY KEY (id)
);
ALTER TABLE `BoatHouse`.`user` 
ADD UNIQUE INDEX `account_UNIQUE` (`account` ASC);

DROP TABLE IF EXISTS shop_cart;
CREATE TABLE shop_cart (
    `id` VARCHAR(100) NOT NULL,
    `userid` INT(11) NOT NULL,
    `foodid` INT(11) NOT NULL,
    `num` INT(4) NOT NULL,
    `comment` VARCHAR(1000) DEFAULT NULL,
    PRIMARY KEY (id),
    unique key userid_foodid(`userid` ,`foodid`)
);

DROP TABLE IF EXISTS `idcf_orders`;
create table `idcf_orders` (
  `id`                bigint        auto_increment
  comment 'primary key',
  `order_id`          varchar(32) CHARACTER SET utf8
  COLLATE utf8_general_ci    not null
  comment 'unique key for orders',
  `user_id`           bigint not null
  comment '用户id',
  `create_time`       datetime      default null
  comment '订单创建时间',
  `update_time`       datetime      default null
  comment '订单修改时间',
  `pay_type`          tinyint(2)    default 1
  comment '1: alipay, 2: wxpay, 3: unionpay',
  `total_amount`      decimal(6, 2) default 0.00
  comment 'total order price',
  `additional_amount` decimal(6, 2) default 0.00
  comment 'order additional price,for example Cost of tableware etc.',
  `order_status`      tinyint(2)    default 0
  comment '-2: expired, -1: rejected, 0: unpaid, 1: paid & order pending, 2: wait for delivery, 3: finished',
  `reason`            varchar(32) CHARACTER SET utf8
  COLLATE utf8_general_ci           default null
  comment '拒单理由',
  `note`              varchar(500) CHARACTER SET utf8
  COLLATE utf8_general_ci           default null
  comment '用户备注',
  primary key order_pk (`id`),
  unique order_unique (`order_id`),
  index order_user_idx (`user_id`)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

DROP table if exists `idcf_order_items`;
create table `idcf_order_items` (
  `id`             bigint                                                                                                                                                                                                                                                                                                                                                                                                                                                                   auto_increment
  comment 'primary key',
  `order_id`       varchar(32) CHARACTER SET utf8
  COLLATE utf8_general_ci         not null
  comment 'unique key for orders',
  `food_id`        int    not null
  comment 'Food table id',
  `food_name`      nvarchar(50) NOT NULL,
  `food_price`     DECIMAL(5, 2) NOT NULL                                                                  DEFAULT 0.00
  comment 'food single price',
  `food_sub_total` decimal(5, 2) not null                                                                                                                                                                                                                                                                                                                                                                                                                                                   default 0.00
  comment 'food sub total price',
  `food_num`       int           not null
  comment 'food number',
  `food_picture`   nvarchar(1000) NULL
  comment 'food intro picture uri',
  primary key order_items_pk (`id`),
  index order_items_idx (`order_id`)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8;