/*==============================================================*/
/* DBMS name:      MySQL 5.0                                    */
/* Created on:     2019/3/5 22:17:43                            */
/*==============================================================*/


drop table if exists action_bar;

drop table if exists action_permission;

drop table if exists user_permission;

drop table if exists user;

/*==============================================================*/
/* Table: action_bar                                            */
/*==============================================================*/
create table action_bar
(
   id                   int not null auto_increment,
   parent               int default 0 comment '该层菜单的上级菜单',
   level                int default 1 comment '该级菜单是第几级菜单',
   link_type            char(32) comment '定义link的含义，可以是跳转，可以是发起请求',
   link                 char(255),
   status               char(32) comment '状态表，可能用户编辑了这个菜单，但是没有编辑完，所以只是存了一份草稿',
   create_time          timestamp default CURRENT_TIMESTAMP,
   modify_time          timestamp default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   is_del               bool default false,
   primary key (id)
);

alter table action_bar comment '下拉菜单';

/*==============================================================*/
/* Table: action_permission                                     */
/*==============================================================*/
create table action_permission
(
   id                   int not null comment '用户id',
   bars                 varchar(128) comment '用户有权限的下拉列表，多个如1，2，3，4用逗号隔开',
   create_time          timestamp default CURRENT_TIMESTAMP comment '创建时间',
   modify_time          timestamp default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP comment '最后更新时间',
   status               char(32) comment '角色是否生效的状态',
   is_del               bool default false comment '删除标志',
   primary key (id)
);

alter table action_permission comment '下拉菜单权限表';

/*==============================================================*/
/* Table: user                                                  */
/*==============================================================*/
create table user
(
   id                   int not null,
   username             varchar(128) not null comment '用户名',
   password             varchar(128) not null comment '用户密码',
   nickname             varchar(255) comment '用户昵称',
   tel                  char(16) comment '用户电话',
   email                char(64) comment '用户邮箱',
   create_time          timestamp default CURRENT_TIMESTAMP comment '创建时间',
   modify_time          timestamp default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP comment '最后更新时间',
   is_del               bool default false comment '删除标志',
   primary key (id)
);

alter table user comment '用户表';

/*==============================================================*/
/* Table: user_permission                                       */
/*==============================================================*/
create table user_permission
(
   id                   int not null comment '用户id',
   role                 int comment '用户权限，为管理员或者普通用户，取值为1，2，4，8，16...',
   create_time          timestamp default CURRENT_TIMESTAMP,
   modify_time          timestamp default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   status               char(32),
   is_del               bool default false,
   primary key (id)
);

alter table user_permission comment '用户权限表';

alter table action_permission add constraint FK_bar_permission foreign key (id)
      references user (id) on delete restrict on update restrict;

alter table user_permission add constraint FK_user_permission foreign key (id)
      references user (id) on delete restrict on update restrict;