create table oauth_token
(
  token_id      int auto_increment
    primary key,
  access_token  varchar(128)                        not null,
  user_id       varchar(128)                        not null,
  permissions   varchar(512)                        null,
  roles         varchar(512)                        null,
  refresh_token varchar(128)                        null,
  expire_time   datetime                            null,
  create_time   timestamp default CURRENT_TIMESTAMP not null,
  update_time   timestamp default CURRENT_TIMESTAMP not null
  on update CURRENT_TIMESTAMP
)
  charset = utf8mb4;
----------------------------------------------------------
  create table oauth_token_key
(
  id          int auto_increment
    primary key,
  token_key   varchar(128)                        not null
  comment '生成token时的key',
  create_time timestamp default CURRENT_TIMESTAMP not null
)
  charset = utf8mb4;

  --------------------------------------------------------

  create table sys_authorities
(
  authority      varchar(128)                        not null
  comment '授权标识'
    primary key,
  authority_name varchar(128)                        not null
  comment '名称',
  parent_name    varchar(128)                        null
  comment '模块',
  sort           int default '0'                     not null
  comment '排序号',
  create_time    timestamp default CURRENT_TIMESTAMP not null
  comment '创建时间'
)
  comment '权限'
  charset = utf8mb4;

-----------------------------------------------------------

create table sys_menu
(
  menu_id     int auto_increment
  comment '菜单id'
    primary key,
  parent_id   int default '-1'                    not null
  comment '父级id',
  menu_name   varchar(200)                        not null
  comment '菜单名称',
  menu_url    varchar(200)                        null
  comment '菜单url',
  menu_icon   varchar(200)                        null
  comment '菜单图标',
  sort_number int default '0'                     not null
  comment '排序号',
  authority   varchar(200)                        null
  comment '对应权限',
  create_time timestamp default CURRENT_TIMESTAMP not null
  comment '创建时间',
  update_time timestamp default CURRENT_TIMESTAMP not null
  on update CURRENT_TIMESTAMP
  comment '修改时间'
)
  comment '菜单表'
  charset = utf8mb4;

  ----------------------------------------------

  create table sys_role
(
  role_id     int auto_increment
  comment '角色id'
    primary key,
  role_name   varchar(64)                         not null
  comment '角色名称',
  comments    varchar(256)                        null
  comment '备注',
  create_time timestamp default CURRENT_TIMESTAMP not null
  comment '创建时间',
  update_time timestamp default CURRENT_TIMESTAMP not null
  on update CURRENT_TIMESTAMP
  comment '修改时间'
)
  comment '角色'
  charset = utf8mb4;

------------------------------------------------

create table sys_role_authorities
(
  id          int auto_increment
    primary key,
  role_id     int                                 not null
  comment '角色id',
  authority   varchar(128)                        not null
  comment '权限id',
  create_time timestamp default CURRENT_TIMESTAMP null
  comment '创建时间',
  constraint sys_role_authorities_ibfk_1
  foreign key (role_id) references sys_role (role_id)
    on delete cascade
)
  comment '角色权限'
  charset = utf8mb4;

create index FK_sys_role_permission_pm
  on sys_role_authorities (authority);

create index FK_sys_role_permission_role
  on sys_role_authorities (role_id);

  ------------------------------------------------

  create table sys_user
(
  user_id        int auto_increment
  comment '用户id'
    primary key,
  username       varchar(32)                         not null
  comment '账号',
  password       varchar(128)                        not null
  comment '密码',
  nick_name      varchar(20)                         not null
  comment '昵称',
  avatar         varchar(256)                        null
  comment '头像',
  sex            varchar(1) default '男'              not null
  comment '性别',
  phone          varchar(12)                         null
  comment '手机号',
  email          varchar(256)                        null
  comment '邮箱',
  email_verified int(1) default '0'                  null
  comment '邮箱是否验证，0未验证，1已验证',
  true_name      varchar(20)                         null
  comment '真实姓名',
  id_card        varchar(20)                         null
  comment '身份证号',
  birthday       date                                null
  comment '出生日期',
  department_id  int                                 null
  comment '部门id',
  state          int(1) default '0'                  not null
  comment '状态，0正常，1冻结',
  create_time    timestamp default CURRENT_TIMESTAMP not null
  comment '注册时间',
  update_time    timestamp default CURRENT_TIMESTAMP not null
  on update CURRENT_TIMESTAMP
  comment '修改时间',
  constraint user_account
  unique (username)
)
  comment '用户'
  charset = utf8mb4;

create index FK_sys_user
  on sys_user (true_name);

  -----------------------------------------------------------

 create table sys_user_role
(
  id          int auto_increment
    primary key,
  user_id     int                                 not null
  comment '用户id',
  role_id     int                                 not null
  comment '角色id',
  create_time timestamp default CURRENT_TIMESTAMP not null
  comment '创建时间'
)
  comment '用户角色'
  charset = utf8mb4;

create index FK_sys_user_role
  on sys_user_role (user_id);

create index FK_sys_user_role_role
  on sys_user_role (role_id);