/*==============================================================*/
/* DBMS name:      MySQL 5.0                                    */
/* Created on:     2018/3/14 17:22:08                           */
/*==============================================================*/
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;

drop table if exists api_interface;

/*==============================================================*/
/* 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),
   farmer               varchar(20) comment '农场联系人',
   farm_phone           varchar(50) comment '农场联系人电话',
   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       int comment '1 目录；2菜单；3按钮',
   resources_mark       varchar(100) comment '如：系统管理 01，农场管理0101',
   is_enable            bool comment '1 启用；0停用',
   create_date_time     bigint,
   modified_date_time   bigint,
   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            int 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
);


/*==============================================================*/
/* Table: api_interface                                         */
/*==============================================================*/
create table api_interface
(
   ai_id                bigint not null auto_increment,
   ai_describe          varchar(300),
   ai_method            varchar(20),
   ai_url               varchar(300),
   primary key (ai_id)
);

drop table if exists resource_api;

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

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

alter table resource_api add constraint FK_resource_api2 foreign key (ai_id)
      references api_interface (ai_id) on delete restrict on update restrict;

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;

DROP TABLE IF EXISTS sequence; 
CREATE TABLE sequence ( 
     name VARCHAR(50) NOT NULL, 
     current_value INT NOT NULL, 
     increment INT NOT NULL DEFAULT 1, 
     PRIMARY KEY (name) 
) 	  
/*	  
DROP FUNCTION IF EXISTS currval; 
DELIMITER $ 
CREATE FUNCTION currval (seq_name VARCHAR(50)) 
     RETURNS INTEGER
     LANGUAGE SQL 
     DETERMINISTIC 
     CONTAINS SQL 
     SQL SECURITY DEFINER 
     COMMENT ''
BEGIN
     DECLARE value INTEGER; 
     SET value = -1; 
     SELECT current_value INTO value 
          FROM sequence
          WHERE name = seq_name; 
     RETURN value; 
END
$ 
DELIMITER ; 	  
	  
DROP FUNCTION IF EXISTS nextval; 
DELIMITER $ 
CREATE FUNCTION nextval (seq_name VARCHAR(50)) 
     RETURNS INTEGER
     LANGUAGE SQL 
     DETERMINISTIC 
     CONTAINS SQL 
     SQL SECURITY DEFINER 
     COMMENT ''
BEGIN

IF currval(seq_name)=-1 THEN
INSERT INTO sequence VALUES (seq_name, 0, 1);
END IF;

     UPDATE sequence
          SET current_value = current_value + increment 
          WHERE name = seq_name; 
     RETURN currval(seq_name); 
END
$ 
DELIMITER ; 

DROP FUNCTION IF EXISTS setval; 
DELIMITER $ 
CREATE FUNCTION setval (seq_name VARCHAR(50), value INTEGER) 
     RETURNS INTEGER
     LANGUAGE SQL 
     DETERMINISTIC 
     CONTAINS SQL 
     SQL SECURITY DEFINER 
     COMMENT ''
BEGIN
     UPDATE sequence
          SET current_value = value 
          WHERE name = seq_name; 
     RETURN currval(seq_name); 
END
$ 
DELIMITER ; 
*/