drop table if exists users;
CREATE TABLE users
(
    id          INT PRIMARY KEY auto_increment COMMENT '用户编号',
    username    VARCHAR(30)  NOT NULL COMMENT '用户名' UNIQUE,
    nickname    VARCHAR(30)  NULL COMMENT '昵称',
    avatar      VARCHAR(200) NULL COMMENT '头像',
    create_time datetime   DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time datetime ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    state       TINYINT(2) DEFAULT 1 COMMENT '状态',
    remark      VARCHAR(200) COMMENT '备注'
) ENGINE = INNODB
  DEFAULT CHARSET = utf8mb4 COMMENT '用户表';
drop table if exists address;
CREATE TABLE address
(
    id          INT PRIMARY KEY auto_increment COMMENT '地址编号',
    uid         INT NOT NULL COMMENT '用户编号',
    username    VARCHAR(50) COMMENT '收货人',
    phone       VARCHAR(20) COMMENT '收货电话',
    provice     INT COMMENT '省编号',
    city        INT COMMENT '市编号',
    county      INT COMMENT '区/县编号',
    street      INT COMMENT '街道编号',
    detail      VARCHAR(200) COMMENT '详细地址',
    is_default  TINYINT(1) DEFAULT 0 COMMENT '是否默认(0:否 1:是)',
    create_time datetime   DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time datetime ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    state       TINYINT(2) DEFAULT 1 COMMENT '状态',
    remark      VARCHAR(200) COMMENT '备注'
) ENGINE = INNODB
  DEFAULT CHARSET = utf8mb4 COMMENT '收货地址';
drop table if exists food_category;
CREATE TABLE food_category
(
    id          INT PRIMARY KEY auto_increment COMMENT '食品分类编号',
    title       VARCHAR(200) COMMENT '分类标题',
    sort        INT(3)     DEFAULT 0 COMMENT '排序',
    create_time datetime   DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time datetime ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    state       TINYINT(2) DEFAULT 1 COMMENT '状态',
    remark      VARCHAR(200) COMMENT '备注'
) ENGINE = INNODB
  DEFAULT CHARSET = utf8mb4 COMMENT '食品分类';
drop table if exists food;
CREATE TABLE food
(
    id                   INT PRIMARY KEY auto_increment COMMENT '食品编号',
    cid                  INT COMMENT '分类编号',
    title                VARCHAR(500) COMMENT '食品标题',
    description          VARCHAR(500) COMMENT '食品简介',
    content              LONGTEXT COMMENT '食品详情',
    coupon               INT        DEFAULT 0 COMMENT '优惠(0：无优惠 正数涨价 负数：降价)',
    coupon_start_time    datetime COMMENT '优惠开始时间',
    coupon_end_time      datetime COMMENT '优惠结束时间',
    price                INT        NOT NULL COMMENT '价格',
    sort                 INT(3)     DEFAULT 0 COMMENT '排序',
    area                 varchar(200)       default 0 COMMENT '售卖区域(匹配收货地址,null表示不限制)',
    recommend            TINYINT(1) NOT NULL COMMENT '推荐(0,1)',
    recommend_start_time datetime COMMENT '推荐开始时间',
    recommend_end_time   datetime COMMENT '推荐结束时间',
    sale_count           INT        NOT NULL COMMENT '已售出数量',
    create_time          datetime   DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time          datetime ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    state                TINYINT(2) DEFAULT 1 COMMENT '状态',
    remark               VARCHAR(200) COMMENT '备注'
) ENGINE = INNODB
  DEFAULT CHARSET = utf8mb4 COMMENT '食品';

drop table if exists orders;
CREATE TABLE orders
(
    id           VARCHAR(50) NOT NULL COMMENT '订单编号',
    order_title  VARCHAR(50) DEFAULT NULL COMMENT '订单标题',
    price        INT(10)     NOT NULL COMMENT '价格',
    uid          INT(10)     NOT NULL COMMENT '用户编号',
    data         varchar(200)        DEFAULT NULL COMMENT '订单数据',
    receive_info VARCHAR(500) COMMENT '收货信息',
    order_status TINYINT(2)  DEFAULT '1' COMMENT '订单状态(0:已删除 1:待付款  2:已取消 3:已完成)',

    create_time  datetime    DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time  datetime ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    state        TINYINT(2)  DEFAULT 1 COMMENT '状态',
    remark       VARCHAR(200) COMMENT '备注',
    PRIMARY KEY (id) USING BTREE
) ENGINE = INNODB   DEFAULT CHARSET = utf8mb4 COMMENT = '订单记录';
drop table if exists order_foods;
CREATE TABLE order_foods
(
    id          INT PRIMARY KEY auto_increment COMMENT '编号',
    oid         VARCHAR(50) NOT NULL COMMENT '订单编号',
    fid         INT         NOT NULL COMMENT '食品编号',
    price       INT         NOT NULL COMMENT '价格',
    count       INT         NOT NULL COMMENT '购买数量',
    coupon      INT        DEFAULT 0 COMMENT '优惠(0：无优惠 正数涨价 负数：降价)',
    create_time datetime   DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time datetime ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    state       TINYINT(2) DEFAULT 1 COMMENT '状态',
    remark      VARCHAR(200) COMMENT '备注'
) ENGINE = INNODB
  DEFAULT CHARSET = utf8mb4 COMMENT = '订单商品';