DROP DATABASE IF EXISTS onetoo;
CREATE DATABASE onetoo DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE onetoo;

CREATE TABLE member (
  id BIGINT PRIMARY KEY,
  username VARCHAR(64) NOT NULL,
  name VARCHAR(64) NOT NULL,
  password VARCHAR(64) NOT NULL,
  phone VARCHAR(32),
  identity VARCHAR(32),
  sex VARCHAR(8),
  id_number VARCHAR(32),
  status INT DEFAULT 1,
  create_time DATETIME,
  update_time DATETIME,
  create_user BIGINT,
  update_user BIGINT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE company (
  id BIGINT PRIMARY KEY,
  company_name VARCHAR(128) NOT NULL,
  company_id VARCHAR(64),
  address VARCHAR(255),
  phone VARCHAR(32),
  status INT DEFAULT 1,
  create_time DATETIME,
  update_time DATETIME,
  create_user BIGINT,
  update_user BIGINT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE category (
  id BIGINT PRIMARY KEY,
  type INT DEFAULT 1,
  name VARCHAR(64) NOT NULL,
  create_time DATETIME,
  update_time DATETIME,
  create_user BIGINT,
  update_user BIGINT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE dish (
  id BIGINT PRIMARY KEY,
  name VARCHAR(128) NOT NULL,
  category_id BIGINT NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  code VARCHAR(64),
  image VARCHAR(255),
  description VARCHAR(255),
  status INT DEFAULT 1,
  create_time DATETIME,
  update_time DATETIME,
  create_user BIGINT,
  update_user BIGINT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  number VARCHAR(32),
  status INT DEFAULT 1,
  user_id BIGINT,
  order_time DATETIME,
  pay_method INT,
  amount DECIMAL(10,2),
  address VARCHAR(255),
  remark VARCHAR(255),
  username VARCHAR(64),
  phone VARCHAR(32),
  company_name VARCHAR(128)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE order_detail (
  id BIGINT PRIMARY KEY,
  name VARCHAR(128),
  order_id BIGINT,
  dish_id BIGINT,
  number INT,
  amount INT,
  image VARCHAR(255)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE shopping_cart (
  id BIGINT PRIMARY KEY,
  name VARCHAR(128),
  user_id BIGINT,
  dish_id BIGINT,
  number INT,
  price INT,
  image VARCHAR(255),
  create_time DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO member VALUES
(1, 'admin', '系统管理员', 'e10adc3949ba59abbe56e057f20f883e', '13800000000', '管理员', '男', '110101199001010011', 1, NOW(), NOW(), 1, 1),
(2, 'user', '演示用户', 'e10adc3949ba59abbe56e057f20f883e', '13900000000', '用户', '女', '110101199201010022', 1, NOW(), NOW(), 1, 1),
(3, 'staff01', '门店店员', 'e10adc3949ba59abbe56e057f20f883e', '13700000000', '员工', '男', '110101199303030033', 1, NOW(), NOW(), 1, 1);

INSERT INTO company VALUES
(101, '远影归灯·城北店', 'OT-CB-001', '城北万象街 18 号', '021-66010001', 1, NOW(), NOW(), 1, 1),
(102, '远影归灯·滨江店', 'OT-BJ-002', '滨江路 66 号', '021-66010002', 1, NOW(), NOW(), 1, 1);

INSERT INTO category VALUES
(201, 1, '招牌热卖', NOW(), NOW(), 1, 1),
(202, 1, '轻食套餐', NOW(), NOW(), 1, 1),
(203, 1, '饮品甜点', NOW(), NOW(), 1, 1);

INSERT INTO dish VALUES
(301, '黑椒牛肉饭', 201, 28.00, 'D-301', '1fdbfbf3-1d86-4b29-a3fc-46345852f2f8.jpg', '黑椒汁浓郁，适合工作日午餐。', 1, NOW(), NOW(), 1, 1),
(302, '照烧鸡腿饭', 201, 26.00, 'D-302', '2feb6778-5a15-4d7e-90ba-3172189e67a5.jpg', '鸡腿肉鲜嫩，搭配时蔬。', 1, NOW(), NOW(), 1, 1),
(303, '鸡胸轻食沙拉', 202, 22.00, 'D-303', '84eca402-73cc-4c7e-a218-cbf3b0cba034.jpg', '高蛋白低负担。', 1, NOW(), NOW(), 1, 1),
(304, '柠檬气泡水', 203, 12.00, 'D-304', 'df50fdce-737b-4453-885a-f55b1011d04c.png', '清爽解腻。', 1, NOW(), NOW(), 1, 1);

INSERT INTO orders VALUES
(401, '3', 2, 2, NOW() - INTERVAL 2 HOUR, 1, 66.00, '城北万象街 3 栋 1201', '少辣', '演示用户', '13900000000', '远影归灯·城北店'),
(402, '2', 4, 2, NOW() - INTERVAL 1 DAY, 2, 40.00, '滨江路 8 号', '尽快送达', '演示用户', '13900000000', '远影归灯·滨江店');

INSERT INTO order_detail VALUES
(501, '黑椒牛肉饭', 401, 301, 1, 28, '1fdbfbf3-1d86-4b29-a3fc-46345852f2f8.jpg'),
(502, '照烧鸡腿饭', 401, 302, 1, 26, '2feb6778-5a15-4d7e-90ba-3172189e67a5.jpg'),
(503, '柠檬气泡水', 401, 304, 1, 12, 'df50fdce-737b-4453-885a-f55b1011d04c.png'),
(504, '鸡胸轻食沙拉', 402, 303, 1, 22, '84eca402-73cc-4c7e-a218-cbf3b0cba034.jpg');
