/* 
	百惠WMS仓储管理系统 SQL 语句
	1. 创建表语句
	2. 测试数据
*/
-- 创建数据库
CREATE DATABASE bh_wms DEFAULT CHARACTER SET UTF8;
-- 切换数据库
USE bh_wms;

-- --------------------------------------------------------------------
-- 系统相关表
-- --------------------------------------------------------------------

-- 用户表
CREATE TABLE user(
	USER_ID INT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID,主键自增',
	USER_NAME VARCHAR(20) NOT NULL COMMENT '用户名',
	USER_TEL VARCHAR(20) UNIQUE NOT NULL COMMENT '联系电话',
	USER_EMAIL VARCHAR(100) UNIQUE NOT NULL COMMENT '邮箱',
	USER_NICK VARCHAR(10) DEFAULT 'WMS' COMMENT '用户昵称',
	PASSWORD VARCHAR(255) NOT NULL COMMENT '密码',
	SALT VARCHAR(100) DEFAULT NULL COMMENT '盐值',
	AVATAR VARCHAR(255)  DEFAULT '/img/avatar.jpeg' COMMENT '用户头像地址',
	CREATE_TIME DATETIME DEFAULT NULL COMMENT '创建时间',
	UPDATE_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '设置当前时间，并且自动更更新时间',
	IS_DELETE INT DEFAULT 1 COMMENT '是否删除 0:删除 1:未删除'
)DEFAULT CHARSET=UTF8 COMMENT="用户表";

-- 角色表
CREATE TABLE role (
	ROLE_ID INT PRIMARY KEY AUTO_INCREMENT COMMENT '角色ID,主键自增',
	ROLE_NAME VARCHAR(100) NOT NULL UNIQUE COMMENT '角色名称',
	CREATE_TIME DATETIME DEFAULT NULL COMMENT '创建时间',
	UPDATE_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '设置当前时间，并且自动更更新时间',
    IS_DELETE INT DEFAULT 1 COMMENT '是否删除 0:删除 1:未删除'
) DEFAULT CHARSET=UTF8 COMMENT="角色表";

-- 部门表
CREATE TABLE department (
	DEPT_ID INT PRIMARY KEY AUTO_INCREMENT COMMENT '部门ID',
	DEPT_NAME VARCHAR(100) COMMENT '部门名称',
	LEVEL INT DEFAULT 1 COMMENT '层级',
	PARENT_ID INT  DEFAULT 0 COMMENT '父ID,自关联DEPT_ID',
	CREATE_TIME DATETIME DEFAULT NULL COMMENT '创建时间',
	UPDATE_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '设置当前时间，并且自动更更新时间',
    IS_DELETE INT DEFAULT 1 COMMENT '是否删除 0:删除 1:未删除'
)DEFAULT CHARSET=UTF8 COMMENT="部门表";

-- 用户部门关联表
CREATE TABLE user_department (
    DEPT_ID INT COMMENT '部门ID',
    USER_ID INT COMMENT '用户ID',
    CREATE_TIME DATETIME DEFAULT NULL COMMENT '创建时间',
    UPDATE_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '设置当前时间，并且自动更更新时间',
    PRIMARY KEY(USER_ID,DEPT_ID),
    FOREIGN KEY(USER_ID) REFERENCES user(USER_ID),
    FOREIGN KEY(DEPT_ID) REFERENCES department(DEPT_ID),
    IS_DELETE INT DEFAULT 1 COMMENT '是否删除 0:删除 1:未删除'
)DEFAULT CHARSET=UTF8 COMMENT="用户部门关联表";

-- 用户角色关联表
CREATE TABLE user_role (
  USER_ID INT COMMENT '用户ID,关联用户表主键',
  ROLE_ID INT COMMENT '角色ID,关联角色表主键',
  CREATE_TIME DATETIME DEFAULT NULL COMMENT '创建时间',
  UPDATE_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '设置当前时间，并且自动更更新时间',
  PRIMARY KEY(USER_ID,ROLE_ID),
  FOREIGN KEY(USER_ID) REFERENCES user(USER_ID),
  FOREIGN KEY(ROLE_ID) REFERENCES role(ROLE_ID),
  IS_DELETE INT DEFAULT 1 COMMENT '是否删除 0:删除 1:未删除'
)DEFAULT CHARSET=UTF8 COMMENT="用户角色关联表";


-- 菜单表
CREATE TABLE menus (
     MENU_ID INT PRIMARY KEY AUTO_INCREMENT COMMENT '菜单ID,主键自增',
     MENU_NAME VARCHAR(100) NOT NULL COMMENT '菜单名称',
     MENU_URL VARCHAR(255) DEFAULT NULL COMMENT '菜单地址',
     LEVEL INT DEFAULT 1 COMMENT '层级',
     PARENT_ID INT COMMENT '父ID,关联menus表主键,默认值为0,表示菜单第一级',
     CREATE_TIME DATETIME DEFAULT NULL COMMENT '创建时间',
     UPDATE_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '设置当前时间，并且自动更更新时间',
     IS_DELETE INT DEFAULT 1 COMMENT '菜单状态 0:删除 1:未删除'
)DEFAULT CHARSET=UTF8 COMMENT="菜单表";

-- 角色菜单关联表
CREATE TABLE role_menus (
     ROLE_ID INT COMMENT '角色ID,关联角色表主键',
     MENU_ID INT COMMENT '菜单ID,关联菜单表主键',
     CREATE_TIME DATETIME DEFAULT NULL COMMENT '创建时间',
     UPDATE_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '设置当前时间，并且自动更更新时间',
     PRIMARY KEY(ROLE_ID,MENU_ID),
     FOREIGN KEY(ROLE_ID) REFERENCES role(ROLE_ID),
     FOREIGN KEY(MENU_ID) REFERENCES menus(MENU_ID),
     IS_DELETE INT DEFAULT 1 COMMENT '是否删除 0:删除 1:未删除'
)DEFAULT CHARSET=UTF8 COMMENT="角色菜单关联表";


-- --------------------------------------------------------------------
-- 字典表
-- --------------------------------------------------------------------

-- 商品类目表
CREATE TABLE category(
     CATEGORY_ID INT PRIMARY KEY AUTO_INCREMENT COMMENT '类目ID,主键自增',
     CATEGORY_NAME VARCHAR(100) NOT NULL COMMENT '类目名称',
     `LEVEL` INT DEFAULT 1 COMMENT '层级',
     PARENT_ID INT DEFAULT 0 COMMENT '父ID,关联主键(自关联),顶层父ID为0',
     CREATE_TIME DATETIME DEFAULT NULL COMMENT '创建时间',
     UPDATE_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '设置当前时间，并且自动更更新时间',
     IS_DELETE INT DEFAULT 1 COMMENT '是否删除 0:删除 1:未删除'
)DEFAULT CHARSET=UTF8 COMMENT="商品类目表";

-- 客户级别表
CREATE TABLE level(
     LEVEL_ID INT PRIMARY KEY AUTO_INCREMENT COMMENT '客户级别ID,主键自增',
     LEVEL_NAME VARCHAR(100) NOT NULL COMMENT '客户级别名称',
     CREATE_TIME DATETIME DEFAULT NULL COMMENT '创建时间',
     UPDATE_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '设置当前时间，并且自动更更新时间',
     IS_DELETE INT DEFAULT 1 COMMENT '是否删除 0:删除 1:未删除'
)DEFAULT CHARSET=UTF8 COMMENT="客户级别表";

-- 客户来源表
CREATE TABLE source(
    SOURCE_ID INT PRIMARY KEY AUTO_INCREMENT COMMENT '客户来源ID,主键自增',
    SOURCE_NAME VARCHAR(100) NOT NULL COMMENT '客户来源名称',
    CREATE_TIME DATETIME DEFAULT NULL COMMENT '创建时间',
    UPDATE_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '设置当前时间，并且自动更更新时间',
    IS_DELETE INT DEFAULT 1 COMMENT '是否删除 0:删除 1:未删除'
)DEFAULT CHARSET=UTF8 COMMENT="客户来源表";

-- 客户行业表
CREATE TABLE industry(
     INDUSTRY_ID INT PRIMARY KEY AUTO_INCREMENT COMMENT '行业ID,主键自增',
     INDUSTRY_NAME VARCHAR(100) NOT NULL COMMENT '行业名称',
     CREATE_TIME DATETIME DEFAULT NULL COMMENT '创建时间',
     UPDATE_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '设置当前时间，并且自动更更新时间',
     IS_DELETE INT DEFAULT 1 COMMENT '是否删除 0:删除 1:未删除'
)DEFAULT CHARSET=UTF8 COMMENT="客户行业表";

-- --------------------------------------------------------------------
-- 业务相关表
-- --------------------------------------------------------------------

-- 供应商信息表
CREATE TABLE supplier(
	SUPPLIER_ID INT PRIMARY KEY AUTO_INCREMENT COMMENT '供应商ID,主键自增',
	SUPPLIER_NAME VARCHAR(100) NOT NULL COMMENT '供应商信息,如果为个人(非公司),那么当前字段和SUPPLIER_LEADER信息相同,如果为公司,那么是公司名称',
	SUPPLIER_LEADER  VARCHAR(100) NOT NULL COMMENT '负责人,如果为个人(非公司),那么当前字段和SUPPLIER_NAME信息相同',
	SUPPLIER_TEL VARCHAR(100) NOT NULL COMMENT '联系电话',
	SUPPLIER_EMAIL VARCHAR(100) NOT NULL COMMENT '邮箱',
	SUPPLIER_ADDRESS VARCHAR(100) NOT NULL COMMENT '地址',
	CREATE_TIME DATETIME DEFAULT NULL COMMENT '创建时间',
	UPDATE_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '设置当前时间，并且自动更更新时间',
    IS_DELETE INT DEFAULT 1 COMMENT '是否删除 0:删除 1:未删除'
)DEFAULT CHARSET=UTF8 COMMENT="供应商信息表";

-- 客户信息表
CREATE TABLE customer(
	CUSTOMER_ID INT PRIMARY KEY AUTO_INCREMENT COMMENT '客户ID,主键自增',
	CUSTOMER_NAME VARCHAR(100) NOT NULL COMMENT '客户名称,如果为公司,那么是公司名,如果为个人,那么填写个人名称',
	CUSTOMER_LEADER VARCHAR(100) NOT NULL COMMENT '客户负责人,如果公司为个人,那么和CUSTOMER_NAME内容相同',
	CUSTOMER_TEL VARCHAR(100) NOT NULL COMMENT '联系电话',
	CUSTOMER_EMAIL VARCHAR(100) COMMENT '联系邮箱',
	CUSTOMER_ADDRESS VARCHAR(255) COMMENT '联系地址',
    LEVEL_ID INT NOT NULL COMMENT '客户级别,关联级别表主键',
    SOURCE_ID INT NOT NULL COMMENT '客户来源,关联来源表主键',
    INDUSTRY_ID INT NOT NULL COMMENT '客户行业,关联行业表主键',
	CREATE_TIME DATETIME DEFAULT NULL COMMENT '创建时间',
	UPDATE_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '设置当前时间，并且自动更更新时间',
    IS_DELETE INT DEFAULT 1 COMMENT '是否删除 0:删除 1:未删除'
)DEFAULT CHARSET=UTF8 COMMENT="客户表";

-- 将客户和级别关联
ALTER TABLE customer ADD FOREIGN KEY(LEVEL_ID) REFERENCES level(LEVEL_ID);
-- 将客户和来源关联
ALTER TABLE customer ADD FOREIGN KEY(SOURCE_ID) REFERENCES source(SOURCE_ID);
-- 将客户和行业关联
ALTER TABLE customer ADD FOREIGN KEY(INDUSTRY_ID) REFERENCES industry(INDUSTRY_ID);

-- 承运商
CREATE TABLE carrier(
	CARRIER_ID INT PRIMARY KEY AUTO_INCREMENT COMMENT '承运商ID,主键自增',
	CARRIER_NAME VARCHAR(100) NOT NULL COMMENT '承运商名称',
	CARRIER_LEADER VARCHAR(100) NOT NULL COMMENT '承运商负责人',
	CARRIER_TEL VARCHAR(100) NOT NULL COMMENT '联系电话',
	CARRIER_EMAIL VARCHAR(100) COMMENT '联系邮箱',
	CARRIER_ADDRESS VARCHAR(255) COMMENT '联系地址',
	CREATE_TIME DATETIME DEFAULT NULL COMMENT '创建时间',
	UPDATE_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '设置当前时间，并且自动更更新时间',
    IS_DELETE INT DEFAULT 1 COMMENT '是否删除 0:删除 1:未删除'
)DEFAULT CHARSET=UTF8 COMMENT="承运商表";

-- 产品信息表
CREATE TABLE product(
	PRODUCT_ID INT PRIMARY KEY AUTO_INCREMENT COMMENT '产品ID,主键自增',
	PRODUCT_NAME VARCHAR(100) NOT NULL COMMENT '产品名称',
	CATEGORY_ID INT NOT NULL COMMENT '产品类目ID,关联类目表主键',
	PRODUCT_SIZE VARCHAR(100) COMMENT '产品尺寸(长乘宽),单位 毫米(mm)',
	PRODUCT_PRICE DOUBLE(16,3) COMMENT '产品价格',
	CREATE_TIME DATETIME DEFAULT NULL COMMENT '创建时间',
	UPDATE_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '设置当前时间，并且自动更更新时间',
	IS_DELETE INT DEFAULT 1 COMMENT '是否删除 0:删除 1:未删除'
)DEFAULT CHARSET=UTF8 COMMENT="产品信息表";

-- 将类目和产品关联
ALTER TABLE product ADD FOREIGN KEY(CATEGORY_ID) REFERENCES category(CATEGORY_ID);

-- 仓库信息表
CREATE TABLE repository(
	REPO_ID INT PRIMARY KEY AUTO_INCREMENT COMMENT '仓库ID,主键自增',
	REPO_NAME VARCHAR(100) DEFAULT NULL COMMENT '仓库名称',
	REPO_ADDRESS VARCHAR(100) NOT NULL COMMENT '仓库地址',
	REPO_AREA VARCHAR(50) COMMENT '仓库面积,单位:平方米',
	REPO_DESC VARCHAR(255) COMMENT '仓库描述(备注)',
	REPO_STATUS TINYINT DEFAULT 1 COMMENT '仓库状态 0:故障 1:正常 2:已满 3:废弃',
	CREATE_TIME DATETIME DEFAULT NULL COMMENT '创建时间',
	UPDATE_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '设置当前时间，并且自动更更新时间',
    IS_DELETE INT DEFAULT 1 COMMENT '是否删除 0:删除 1:未删除'
)DEFAULT CHARSET=UTF8 COMMENT="仓库信息表";

-- 添加仓库名称列
-- ALTER TABLE repository ADD REPO_NAME VARCHAR(100) DEFAULT NULL COMMENT '仓库名称';

-- 用户仓库关联表(一个用户可以管理多个仓库,一个仓库可以被多个用户管理,多对多)
CREATE TABLE user_repository(
   REPO_ID INT  COMMENT '仓库ID',
   USER_ID INT  COMMENT '用户ID',
   CREATE_TIME DATETIME DEFAULT NULL COMMENT '创建时间',
   UPDATE_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '设置当前时间，并且自动更更新时间',
   IS_DELETE INT DEFAULT 1 COMMENT '是否删除 0:删除 1:未删除',
   PRIMARY KEY(REPO_ID,USER_ID),
   FOREIGN KEY(REPO_ID) REFERENCES repository(REPO_ID),
   FOREIGN KEY(USER_ID) REFERENCES user(USER_ID)
)DEFAULT CHARSET=UTF8 COMMENT="用户仓库关联表";


-- 入库记录表
CREATE TABLE record_in(
	RECORD_IN_ID INT PRIMARY KEY AUTO_INCREMENT COMMENT '入库ID,主键自增',
	SUPPLIER_ID INT NOT NULL COMMENT '供应商ID,关联供应商表主键',
	PRODUCT_ID INT NOT NULL COMMENT '产品ID,关联产品表主键',
	RECORD_IN_NUMBER INT DEFAULT 0 COMMENT '入库数量',
	RECORD_IN_TIME DATETIME DEFAULT NULL COMMENT '入库时间',
	USER_ID INT NOT NULL COMMENT '入库负责人ID,关联用户表主键',
	REPO_ID INT NOT NULL COMMENT '仓库ID,关联仓库表主键',
	REMARKS VARCHAR(255) DEFAULT NULL COMMENT '备注',
	CREATE_TIME DATETIME DEFAULT NULL COMMENT '创建时间',
	UPDATE_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '设置当前时间，并且自动更更新时间',
    IS_DELETE INT DEFAULT 1 COMMENT '是否删除 0:删除 1:未删除',
	FOREIGN KEY(SUPPLIER_ID) REFERENCES supplier(SUPPLIER_ID),
	FOREIGN KEY(PRODUCT_ID) REFERENCES product(PRODUCT_ID),
	FOREIGN KEY(USER_ID) REFERENCES user(USER_ID)
)DEFAULT CHARSET=UTF8 COMMENT="入库记录表";

-- 出库记录表
CREATE TABLE record_out(
	RECORD_OUT_ID INT PRIMARY KEY AUTO_INCREMENT COMMENT '出库ID,主键自增',
	CUSTOMER_ID INT NOT NULL COMMENT '客户ID,关联客户表主键',
	PRODUCT_ID INT NOT NULL COMMENT '产品ID,关联产品表主键',
	RECORD_OUT_NUMBER INT DEFAULT 0 COMMENT '出库数量',
	RECORD_OUT_TIME DATETIME DEFAULT NULL COMMENT '出库时间',
	USER_ID INT NOT NULL COMMENT '出库负责人ID,关联用户表主键',
	REPO_ID INT NOT NULL COMMENT '仓库ID,关联仓库表主键',
	REMARKS VARCHAR(255) DEFAULT NULL COMMENT '备注',
	CREATE_TIME DATETIME DEFAULT NULL COMMENT '创建时间',
	UPDATE_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '设置当前时间，并且自动更更新时间',
    IS_DELETE INT DEFAULT 1 COMMENT '是否删除 0:删除 1:未删除',
	FOREIGN KEY(CUSTOMER_ID) REFERENCES customer(CUSTOMER_ID),
	FOREIGN KEY(PRODUCT_ID) REFERENCES product(PRODUCT_ID),
	FOREIGN KEY(USER_ID) REFERENCES user(USER_ID)
)DEFAULT CHARSET=UTF8 COMMENT="出库记录表";


-- 库存记录表
CREATE TABLE record_stock(
	PRODUCT_ID INT NOT NULL COMMENT '产品ID,关联产品表主键',
	REPO_ID INT NOT NULL COMMENT '仓库ID,关联仓库表主键',
	PRODUCT_STOCK INT DEFAULT 0 COMMENT '库存数量',
	CREATE_TIME DATETIME DEFAULT NULL COMMENT '创建时间',
	UPDATE_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '设置当前时间，并且自动更更新时间',
    IS_DELETE INT DEFAULT 1 COMMENT '是否删除 0:删除 1:未删除',
	PRIMARY KEY(PRODUCT_ID,REPO_ID),
	FOREIGN KEY(PRODUCT_ID) REFERENCES product(PRODUCT_ID),
	FOREIGN KEY(REPO_ID) REFERENCES repository(REPO_ID)
)DEFAULT CHARSET=UTF8 COMMENT="库存记录表";

-- 日志表
CREATE TABLE sys_log(
    log_id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '日志ID',
    `type` INT DEFAULT 1 COMMENT '日志类型 1:登陆日志; 0:其他日志',
    ip_addr VARCHAR(255) NOT NULL COMMENT '请求地址',
    method_name VARCHAR(100) NOT NULL COMMENT '请求方法名称',
    method VARCHAR(100) NOT NULL COMMENT '请求方式',
    nick_name VARCHAR(100) NOT NULL COMMENT '昵称',
    `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
)DEFAULT CHARSET=UTF8 COMMENT="日志记录表";