--------- 用户管理 ---------
IF EXISTS(SELECT *
          FROM sys.objects
          WHERE object_id = OBJECT_ID(N'[sys_user]')
            AND type in (N'U'))
    DROP TABLE [sys_user];
CREATE TABLE [sys_user]
(
    [id]         int IDENTITY               not null,
    [username]   nvarchar(50)               not null,
    [userPwd]    varchar(255)               not null,
    [nickName]   nvarchar(50)               not null,
    [truename]   nvarchar(50)               not null,
    [avatar]     varchar(200)               not null,
    [sex]        tinyint  default 1         not null, --1男，2女
    [phone]      varchar(11)                null,
    [email]      varchar(50)                null,
    [idCard]     varchar(18)                null,
    [birthday]   date                       null,
    [orgId]      int      default 0         not null,
    [errorTimes] int      default 0         not null,
    [isAdmin]    tinyint  default 1         not null, --0管理员，1普通用户
    [estate]     tinyint  default 0         not null, --0正常，1待审核 ，2 禁用
    [uid]        int      default 0         not null,
    [validDate]  datetime                   null,
    [loginTime]  datetime                   null,
    [createTime] datetime default getdate() not null,
    [updateTime] datetime default getdate() not null
);

insert into sys_user (username, userPwd, nickName, truename, avatar, isAdmin)
values ('admin', '$2y$10$UthO0QJqVo8yVLQQdXzNYum2wqxz3jHPe/ozWH/ES6AC3fiNwseF2', N'管理员', N'管理员',
        'https://cdn.eleadmin.com/20200610/avatar.jpg', 0)

--------- 角色表 ---------
IF EXISTS(SELECT *
          FROM sys.objects
          WHERE object_id = OBJECT_ID(N'[sys_role]')
            AND type in (N'U'))
    DROP TABLE [sys_role];
CREATE TABLE [sys_role]
(
    [id]         int IDENTITY               not null,
    [roleName]   nvarchar(20)               not null,
    [roleCode]   varchar(20)                not null,
    [comments]   nvarchar(100)              not null,
    [estate]     tinyint  default 0         not null, --0正常，1禁用
    [uid]        int      default 0         not null,
    [createTime] datetime default getdate() not null,
    [updateTime] datetime default getdate() not null
);

--------- 用户角色表 ---------
IF EXISTS(SELECT *
          FROM sys.objects
          WHERE object_id = OBJECT_ID(N'[sys_user_role]')
            AND type in (N'U'))
    DROP TABLE [sys_user_role];
CREATE TABLE [sys_user_role]
(
    [id]         int IDENTITY               not null,
    [userId]     int      default 0         not null,
    [roleId]     int      default 0         not null,
    [createTime] datetime default getdate() not null
);

--------- 角色权限表 ---------
IF EXISTS(SELECT *
          FROM sys.objects
          WHERE object_id = OBJECT_ID(N'[sys_role_permission]')
            AND type in (N'U'))
    DROP TABLE [sys_role_permission];
CREATE TABLE [sys_role_permission]
(
    [id]         int IDENTITY               not null,
    [roleID]     int      default 0         not null,
    [menuID]     int      default 0         not null,
    [createTime] datetime default getdate() not null
);

--------- 权限管理 ---------
IF EXISTS(SELECT *
          FROM sys.objects
          WHERE object_id = OBJECT_ID(N'[sys_role]')
            AND type in (N'U'))
    DROP TABLE [sys_menu];
CREATE TABLE [sys_menu]
(
    [id]         int IDENTITY                  not null,
    [title]      nvarchar(50)                  not null,
    [path]       varchar(50)                   null,
    [component]  varchar(50)                   null,
    [authority]  varchar(50)                   null,
    [sortNumber] int         default 0         not null,
    [hide]       tinyint     default 0         not null, --是否隐藏：0否/1是
    [menuType]   tinyint     default 0         not null, --菜单类型：0菜单/1按钮
    [parentId]   int         default 0         not null,
    [icon]       varchar(50)                   null,
    [comments]   nvarchar(100)                 null,
    [target]     varchar(10) default '_self'   not null,
    [color]      varchar(20)                   not null,
    [app]        varchar(10)                   not null,
    [uid]        int         default 0         not null,
    [createTime] datetime    default getdate() not null,
    [updateTime] datetime    default getdate() not null
);

--------- 字典管理 ---------
IF EXISTS(SELECT *
          FROM sys.objects
          WHERE object_id = OBJECT_ID(N'[sys_dict]')
            AND type in (N'U'))
    DROP TABLE [sys_dict];
CREATE TABLE [sys_dict]
(
    [id]         int IDENTITY               not null,
    [dictName]   nvarchar(50)               not null,
    [dictCode]   varchar(50)                not null,
    [comments]   nvarchar(50)               null,
    [sortNumber] int                        not null,
    [uid]        int      default 0         not null,
    [createTime] datetime default getdate() not null,
    [updateTime] datetime default getdate() not null
);

--------- 字典项管理 ---------
IF EXISTS(SELECT *
          FROM sys.objects
          WHERE object_id = OBJECT_ID(N'[sys_dict_data]')
            AND type in (N'U'))
    DROP TABLE [sys_dict_data];
CREATE TABLE [sys_dict_data]
(
    [id]           int IDENTITY               not null,
    [dictDataName] nvarchar(50)               not null,
    [dictDataCode] varchar(50)                not null,
    [dictId]       int                        not null,
    [comments]     nvarchar(50)               null,
    [sortNumber]   int                        not null,
    [uid]          int      default 0         not null,
    [createTime]   datetime default getdate() not null,
    [updateTime]   datetime default getdate() not null
);


