/*==============================================================*/
/* DBMS name:      MySQL 5.0                                    */
/* Created on:     2021/3/11 10:10:21                           */
/*==============================================================*/


drop table if exists logistics_aftermarket;

drop table if exists logistics_area;

drop table if exists logistics_car;

drop table if exists logistics_cargo;

drop table if exists logistics_client;

drop table if exists logistics_courier;

drop table if exists logistics_driver;

drop table if exists logistics_expend;

drop table if exists logistics_in_storage;

drop table if exists logistics_income;

drop table if exists logistics_log;

drop table if exists logistics_model;

drop table if exists logistics_order;

drop table if exists logistics_prediction;

drop table if exists logistics_price;

drop table if exists logistics_role;

drop table if exists logistics_role_model;

drop table if exists logistics_sign;

drop table if exists logistics_staff;

drop table if exists logistics_storage;

drop table if exists logistics_user;

drop table if exists logistics_user_role;

/*==============================================================*/
/* Table: logistics_aftermarket                                 */
/*==============================================================*/
create table logistics_aftermarket
(
    aftermarket_id       int not null auto_increment comment '售后主键',
    order_id             int comment '订单ID',
    aftermarket_comment  varchar(200) comment '详情',
    aftermarket_process  varchar(100) comment '处理方式',
    aftermarket_state    varchar(20) comment '状态',
    primary key (aftermarket_id)
);

alter table logistics_aftermarket comment '售后表';

/*==============================================================*/
/* Table: logistics_area                                        */
/*==============================================================*/
create table logistics_area
(
    area_id              int not null comment '地区id',
    area_name            varchar(20) comment '地区名称',
    area_description     varchar(200) comment '地区描述',
    area_up              int comment '上级地区',
    primary key (area_id)
);

alter table logistics_area comment '地区表';

/*==============================================================*/
/* Table: logistics_car                                         */
/*==============================================================*/
create table logistics_car
(
    car_id               int not null auto_increment comment '车辆ID',
    driver_id            int comment '司机id',
    car_state            varchar(200) comment '车辆状态',
    car_type             varchar(200) comment '车辆类型',
    car_num              varchar(30) comment '车牌号',
    primary key (car_id)
);

/*==============================================================*/
/* Table: logistics_cargo                                       */
/*==============================================================*/
create table logistics_cargo
(
    cargo_id             int not null auto_increment comment '主键',
    cargo_type           varchar(20) comment '货物类型',
    cargo_weight         double comment '货物重量',
    cargo_explain        varchar(200) comment '货物说明',
    primary key (cargo_id)
);

/*==============================================================*/
/* Table: logistics_client                                      */
/*==============================================================*/
create table logistics_client
(
    client_id            int not null auto_increment comment '寄件人ID',
    client_tel           varchar(20) comment '寄件人手机号',
    client_name          varchar(20) comment '寄件人姓名',
    client_address       varchar(100) comment '寄件人地址',
    client_pwd           varchar(20) comment '客户密码',
    primary key (client_id)
);

/*==============================================================*/
/* Table: logistics_courier                                     */
/*==============================================================*/
create table logistics_courier
(
    courier_id           int not null auto_increment comment '快递员id',
    staff_id             int comment '员工ID',
    courier_state        varchar(50) comment '快递员状态',
    courier_count        int comment '接单量',
    primary key (courier_id)
);

/*==============================================================*/
/* Table: logistics_driver                                      */
/*==============================================================*/
create table logistics_driver
(
    driver_id            int not null auto_increment comment '司机id',
    staff_id             int comment '员工ID',
    driver_years         int comment '驾龄',
    driver_state         varchar(30) comment '状态',
    primary key (driver_id)
);

/*==============================================================*/
/* Table: logistics_expend                                      */
/*==============================================================*/
create table logistics_expend
(
    expend_id            int not null auto_increment comment '支出id',
    expend_comment       varchar(200) comment '支出详情',
    expend_type          varchar(20) comment '支出类型',
    expend_time          datetime comment '支出时间',
    primary key (expend_id)
);

alter table logistics_expend comment '支出表';

/*==============================================================*/
/* Table: logistics_in_storage                                  */
/*==============================================================*/
create table logistics_in_storage
(
    in_storage_id        int not null auto_increment comment '入库id',
    storage_id           int comment '仓库id',
    order_id             int comment '订单ID',
    in_storage_time      datetime comment '入库时间',
    in_storage_previous  varchar(100) comment '上一站',
    in_storage_next      varchar(100) comment '下一站',
    primary key (in_storage_id)
);

/*==============================================================*/
/* Table: logistics_income                                      */
/*==============================================================*/
create table logistics_income
(
    income_id            int not null auto_increment comment '收入主键',
    order_id             int comment '订单ID',
    income_remark        varchar(200) comment '备注',
    primary key (income_id)
);

alter table logistics_income comment '收入表';

/*==============================================================*/
/* Table: logistics_log                                         */
/*==============================================================*/
create table logistics_log
(
    log_id               int not null auto_increment comment '日志id',
    staff_id             int comment '员工ID',
    log_items            varchar(50) comment '操作事项',
    log_time             datetime comment '操作时间',
    primary key (log_id)
);

/*==============================================================*/
/* Table: logistics_model                                       */
/*==============================================================*/
create table logistics_model
(
    model_id             int not null auto_increment comment '模块ID',
    model_name           varchar(50) comment '模块名称',
    model_remark         varchar(200) comment '模块备注',
    model_parent         int comment '上级模块',
    model_url            varchar(200) comment 'url地址',
    primary key (model_id)
);

/*==============================================================*/
/* Table: logistics_order                                       */
/*==============================================================*/
create table logistics_order
(
    order_id             int not null auto_increment comment '订单ID',
    staff_id             int comment '员工ID',
    client_id            int comment '寄件人ID',
    cargo_id             int comment '主键',
    order_state          varchar(100) comment '订单状态',
    order_price          double comment '订单金额',
    order_transport      varchar(100) comment '运输方式',
    order_bourn          varchar(40) comment '目的地',
    order_departure      varchar(40) comment '出发地',
    order_number         varchar(50) comment '订单编号',
    order_business       varchar(20) comment '业务类型',
    primary key (order_id)
);

/*==============================================================*/
/* Table: logistics_prediction                                  */
/*==============================================================*/
create table logistics_prediction
(
    prediction_id        int not null auto_increment comment '下单主键',
    cargo_id             int comment '主键',
    client_id            int comment '寄件人ID',
    prediction_bourn     varchar(20) comment '目的地',
    prediction_departure varchar(20) comment '出发地',
    prediction_time      datetime comment '下单时间',
    prediction_money     double comment '下单金额',
    prediction_recipient varchar(20) comment '取件人',
    prediction_business  varchar(20) comment '业务类型',
    primary key (prediction_id)
);

alter table logistics_prediction comment '下单表';

/*==============================================================*/
/* Table: logistics_price                                       */
/*==============================================================*/
create table logistics_price
(
    price_id             int not null auto_increment comment '价格主键',
    price_money          double comment '价格金额',
    price_bourn          varchar(40) comment '目的地',
    price_departure      varchar(40) comment '出发地',
    price_weight         varchar(10) comment '货物重量区间',
    price_business_type  varchar(40) comment '业务类型',
    primary key (price_id)
);

alter table logistics_price comment '价格表';

/*==============================================================*/
/* Table: logistics_role                                        */
/*==============================================================*/
create table logistics_role
(
    role_id              int not null auto_increment comment '角色ID',
    role_name            varchar(50) comment '角色名称',
    role_remark          varchar(300) comment '备注',
    primary key (role_id)
);

/*==============================================================*/
/* Table: logistics_role_model                                  */
/*==============================================================*/
create table logistics_role_model
(
    role_model_id        int not null auto_increment comment '主键',
    model_id             int comment '模块ID',
    role_id              int comment '角色ID',
    primary key (role_model_id)
);

/*==============================================================*/
/* Table: logistics_sign                                        */
/*==============================================================*/
create table logistics_sign
(
    sign_id              int not null auto_increment comment '签收id',
    order_id             int comment '订单ID',
    sign_recipients      varchar(20) comment '收件人',
    sign_time            datetime comment '送达时间',
    sign_area            varchar(100) comment '存放地点',
    sign_tel             varchar(20) comment '收件人电话',
    primary key (sign_id)
);

/*==============================================================*/
/* Table: logistics_staff                                       */
/*==============================================================*/
create table logistics_staff
(
    staff_id             int not null auto_increment comment '员工ID',
    staff_name           varchar(20) comment '员工姓名',
    staff_sex            varchar(20) comment '员工性别',
    staff_birthday       date comment '员工生日',
    staff_tel            varchar(20) comment '员工电话',
    staff_photo          varchar(200) comment '员工照片',
    staff_remark         varchar(200) comment '员工备注',
    staff_address        varchar(200) comment '员工住址',
    staff_username       varchar(20) comment '用户名',
    staff_pwd            varchar(20) comment '密码',
    staff_slary          double comment '工资',
    primary key (staff_id)
);

/*==============================================================*/
/* Table: logistics_storage                                     */
/*==============================================================*/
create table logistics_storage
(
    storage_id           int not null auto_increment comment '仓库id',
    storage_address      varchar(100) comment '仓库地址',
    storage_tel          varchar(20) comment '联系电话',
    storage_principal    varchar(20) comment '负责人',
    storage_name         varchar(50) comment '仓库名',
    primary key (storage_id)
);

/*==============================================================*/
/* Table: logistics_user                                        */
/*==============================================================*/
create table logistics_user
(
    user_id              int not null auto_increment comment '用户id',
    user_account         varchar(20) comment '用户账号',
    user_pwd             varchar(20) comment '用户密码',
    primary key (user_id)
);

alter table logistics_user comment '用户表';

/*==============================================================*/
/* Table: logistics_user_role                                   */
/*==============================================================*/
create table logistics_user_role
(
    staff_role_id        int not null auto_increment comment '员工角色主键',
    role_id              int comment '角色ID',
    user_id              int comment '用户id',
    primary key (staff_role_id)
);

alter table logistics_aftermarket add constraint FK_Reference_22 foreign key (order_id)
    references logistics_order (order_id) on delete restrict on update restrict;

alter table logistics_car add constraint FK_Reference_16 foreign key (driver_id)
    references logistics_driver (driver_id) on delete restrict on update restrict;

alter table logistics_courier add constraint FK_Reference_17 foreign key (staff_id)
    references logistics_staff (staff_id) on delete restrict on update restrict;

alter table logistics_driver add constraint FK_Reference_15 foreign key (staff_id)
    references logistics_staff (staff_id) on delete restrict on update restrict;

alter table logistics_in_storage add constraint FK_Reference_11 foreign key (storage_id)
    references logistics_storage (storage_id) on delete restrict on update restrict;

alter table logistics_in_storage add constraint FK_Reference_13 foreign key (order_id)
    references logistics_order (order_id) on delete restrict on update restrict;

alter table logistics_income add constraint FK_Reference_23 foreign key (order_id)
    references logistics_order (order_id) on delete restrict on update restrict;

alter table logistics_log add constraint FK_Reference_18 foreign key (staff_id)
    references logistics_staff (staff_id) on delete restrict on update restrict;

alter table logistics_order add constraint FK_Reference_10 foreign key (cargo_id)
    references logistics_cargo (cargo_id) on delete restrict on update restrict;

alter table logistics_order add constraint FK_Reference_12 foreign key (client_id)
    references logistics_client (client_id) on delete restrict on update restrict;

alter table logistics_order add constraint FK_Reference_7 foreign key (staff_id)
    references logistics_staff (staff_id) on delete restrict on update restrict;

alter table logistics_prediction add constraint FK_Reference_19 foreign key (cargo_id)
    references logistics_cargo (cargo_id) on delete restrict on update restrict;

alter table logistics_prediction add constraint FK_Reference_20 foreign key (client_id)
    references logistics_client (client_id) on delete restrict on update restrict;

alter table logistics_role_model add constraint FK_Reference_3 foreign key (model_id)
    references logistics_model (model_id) on delete restrict on update restrict;

alter table logistics_role_model add constraint FK_Reference_4 foreign key (role_id)
    references logistics_role (role_id) on delete restrict on update restrict;

alter table logistics_sign add constraint FK_Reference_21 foreign key (order_id)
    references logistics_order (order_id) on delete restrict on update restrict;

alter table logistics_user_role add constraint FK_Reference_2 foreign key (role_id)
    references logistics_role (role_id) on delete restrict on update restrict;

alter table logistics_user_role add constraint FK_Reference_24 foreign key (user_id)
    references logistics_user (user_id) on delete restrict on update restrict;