/*==============================================================*/
/* DBMS name:      MySQL 5.0                                    */
/* Created on:     2018/3/13 15:41:04                           */
/*==============================================================*/

SET FOREIGN_KEY_CHECKS=0;

drop table if exists farm;

drop table if exists resources;

drop index farm_role_index on role;

drop table if exists role;

drop table if exists role_resources;

drop table if exists user_role;

drop index farm_user_index on users;

drop table if exists users;

/*==============================================================*/
/* Table: farm                                                  */
/*==============================================================*/
create table farm
(
   farm_id              bigint not null auto_increment,
   is_enable            bool comment '1 启用；0停用',
   farm_name            varchar(200),
   farm_no              varchar(100) comment '以全国地理位置编码为前缀进行编码',
   farm_province        varchar(20),
   farm_city            varchar(20),
   farm_district        varchar(20),
   farm_address         varchar(300),
   farm_all_address     varchar(500),
   farm_long            varchar(20),
   farm_lat             varchar(20),
   farm_describe        varchar(500),
   farm_img_url         varchar(300),
   farm_auth_img_url    varchar(300),
   create_date_time     bigint,
   modified_date_time   bigint,
   primary key (farm_id)
);

/*==============================================================*/
/* Table: resources                                             */
/*==============================================================*/
create table resources
(
   resources_id         bigint not null auto_increment,
   res_resources_id     bigint,
   resources_name       varchar(100),
   resources_type       tinyint comment '1 目录；2菜单；3接口',
   resources_url        varchar(300),
   request_method       varchar(20) comment 'post；get；put；delete；',
   is_enable            bool comment '1 启用；0停用',
   create_date_time     bigint,
   modified_date_time   bigint,
   type_order           int comment '类别排序号',
   primary key (resources_id)
);

/*==============================================================*/
/* Table: role                                                  */
/*==============================================================*/
create table role
(
   role_id              bigint not null auto_increment,
   farm_id              bigint,
   role_name            varchar(100),
   is_enable            bool comment '1 启用；0停用',
   create_date_time     bigint,
   modified_date_time   bigint,
   role_type            tinyint comment '1 超级管理员，2管理员；3农场主，4 农户，5农技人员，6第三方游客',
   primary key (role_id)
);

alter table role comment '角色';

/*==============================================================*/
/* Index: farm_role_index                                       */
/*==============================================================*/
create index farm_role_index on role
(
   farm_id
);

/*==============================================================*/
/* Table: role_resources                                        */
/*==============================================================*/
create table role_resources
(
   resources_id         bigint not null,
   role_id              bigint not null,
   primary key (resources_id, role_id)
);

/*==============================================================*/
/* Table: user_role                                             */
/*==============================================================*/
create table user_role
(
   user_id              bigint not null,
   role_id              bigint not null,
   primary key (user_id, role_id)
);

/*==============================================================*/
/* Table: users                                                 */
/*==============================================================*/
create table users
(
   user_id              bigint not null auto_increment,
   farm_id              bigint,
   password             varchar(500) comment 'MD5加密；英文，数字，符号',
   phone                varchar(20) comment '手机作为登录账户',
   realname             varchar(20),
   create_date_time     bigint,
   is_enable            bool default 0 comment '1启用；0停用；用户没有删除，只有启用停用',
   avatar               varchar(300) comment '头像',
   id_card              char(18) comment '身份证',
   modified_date_time   bigint,
   primary key (user_id),
   unique key AK_phone_uniquekey (phone)
);

/*==============================================================*/
/* Index: farm_user_index                                       */
/*==============================================================*/
create index farm_user_index on users
(
   farm_id
);

alter table resources add constraint FK_resources_resources foreign key (res_resources_id)
      references resources (resources_id) on delete restrict on update restrict;

alter table role add constraint FK_farm_role foreign key (farm_id)
      references farm (farm_id) on delete restrict on update restrict;

alter table role_resources add constraint FK_role_resources foreign key (resources_id)
      references resources (resources_id) on delete restrict on update restrict;

alter table role_resources add constraint FK_role_resources2 foreign key (role_id)
      references role (role_id) on delete restrict on update restrict;

alter table user_role add constraint FK_user_role foreign key (user_id)
      references users (user_id) on delete restrict on update restrict;

alter table user_role add constraint FK_user_role2 foreign key (role_id)
      references role (role_id) on delete restrict on update restrict;

alter table users add constraint FK_farm_user foreign key (farm_id)
      references farm (farm_id) on delete restrict on update restrict;

