DROP TABLE IF EXISTS `authority_info`;
CREATE TABLE IF NOT EXISTS `authority_info`
(
    `id`        bigint(20)   NOT NULL,
    `parent_id` bigint(20)   NOT NULL COMMENT '上级ID',
    `name`      varchar(32)  NOT NULL COMMENT '权限名称',
    `remark`    varchar(255) NOT NULL COMMENT '权限说明',
    PRIMARY KEY (`id`)
) ENGINE = InnoDB COMMENT ='平台权限表';

INSERT INTO `authority_info` (`id`, `parent_id`, `name`, `remark`)
VALUES (1000, 0, 'oauth.role', '角色管理'),
       (1001, 1000, 'oauth.role.find', '查询'),
       (1002, 1000, 'oauth.role.add', '添加'),
       (1003, 1000, 'oauth.role.edit', '编辑'),
       (1004, 1000, 'oauth.role.delete', '删除'),
       (1100, 0, 'oauth.user', '用户管理'),
       (1101, 1100, 'oauth.user.find', '查询'),
       (1102, 1100, 'oauth.user.add', '添加'),
       (1103, 1100, 'oauth.user.edit', '编辑'),
       (1104, 1100, 'oauth.user.disable', '禁用'),
       (1105, 1100, 'oauth.user.enable', '启用'),
       (1106, 1100, 'oauth.user.reset.login.password', '重置登录密码'),
       (1107, 1100, 'oauth.user.find.login.log', '查询登录日志'),
       (1108, 1100, 'oauth.user.find.operate.log', '查询操作日志'),
       (1200, 0, 'system.config', '系统参数配置'),
       (1201, 1200, 'system.config.find', '查询'),
       (1202, 1200, 'system.config.edit', '修改');

DROP TABLE IF EXISTS `role_authority`;
CREATE TABLE IF NOT EXISTS `role_authority`
(
    `id`           bigint(20) NOT NULL AUTO_INCREMENT,
    `authority_id` bigint(20) NOT NULL COMMENT '权限ID',
    `role_id`      bigint(20) NOT NULL COMMENT '角色ID',
    PRIMARY KEY (`id`),
    KEY `authority_id_index` (`authority_id`),
    KEY `role_id_index` (`role_id`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 801 COMMENT ='平台角色权限关联表';

INSERT INTO `role_authority` (`id`, `authority_id`, `role_id`)
VALUES (1, 1001, 1000),
       (2, 1002, 1000),
       (3, 1003, 1000),
       (4, 1004, 1000),
       (5, 1101, 1000),
       (6, 1102, 1000),
       (7, 1103, 1000),
       (8, 1104, 1000),
       (9, 1105, 1000),
       (10, 1106, 1000),
       (11, 1107, 1000),
       (12, 1108, 1000),
       (13, 1201, 1000),
       (14, 1202, 1000);

DROP TABLE IF EXISTS `role_info`;
CREATE TABLE IF NOT EXISTS `role_info`
(
    `id`     bigint(20)  NOT NULL AUTO_INCREMENT,
    `name`   varchar(50) NOT NULL COMMENT '角色名称',
    `remark` varchar(100) DEFAULT NULL COMMENT '角色说明',
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 1039 COMMENT ='平台角色';


INSERT INTO `role_info` (`id`, `name`, `remark`)
VALUES (1000, '系统管理员', '管理网站的一切事务');


DROP TABLE IF EXISTS `sequence`;
CREATE TABLE IF NOT EXISTS `sequence`
(
    `id`           bigint(20) NOT NULL AUTO_INCREMENT,
    `salt_random`  int(11)    NOT NULL,
    `created_time` datetime   NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB COMMENT ='系统生成序列';


INSERT INTO `sequence` (`id`, `salt_random`, `created_time`)
VALUES (1000, 5, '2022-01-14 02:00:11'),
       (1001, 1, '2022-01-14 02:03:05'),
       (1002, 6, '2022-01-15 00:18:44');


DROP TABLE IF EXISTS `user_base_info`;
CREATE TABLE IF NOT EXISTS `user_base_info`
(
    `id`            bigint(20)   NOT NULL,
    `nick_name`     varchar(50)  NOT NULL COMMENT '昵称',
    `avatar`        varchar(200) NOT NULL DEFAULT '' COMMENT '用户头像',
    `gender`        int(1)       NOT NULL DEFAULT '0',
    `state`         int(1)       NOT NULL COMMENT '用户状态',
    `register_time` datetime     NOT NULL,
    `register_ip`   varchar(50)  NOT NULL,
    PRIMARY KEY (`id`),
    KEY `register_time_index` (`register_time`),
    KEY `state_index` (`state`),
    KEY `gender_index` (`gender`),
    KEY `nick_name_index` (`nick_name`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_0900_ai_ci COMMENT ='用户基本信息表';



INSERT INTO `user_base_info` (`id`, `nick_name`, `avatar`, `gender`, `state`, `register_time`, `register_ip`)
VALUES (1000, 'admin11111', '/2020/11/15/4565685fb6a02e4fd04b0267b9f4932f.jpg', 0, 1, '2019-08-06 16:12:57',
        '127.0.0.1');


DROP TABLE IF EXISTS `user_login_account`;
CREATE TABLE IF NOT EXISTS `user_login_account`
(
    `id`            varchar(32) NOT NULL,
    `uid`           bigint(20)  NOT NULL COMMENT '平台uid',
    `login_account` varchar(50) NOT NULL COMMENT '登录账号\n微信登录保存的为unionid',
    `account_type`  int(1)      NOT NULL DEFAULT '1' COMMENT '账号类型',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uid_account_type_unique` (`uid`, `account_type`),
    UNIQUE KEY `login_account_account_type_unique` (`login_account`, `account_type`),
    KEY `uid_index` (`uid`),
    KEY `login_account_index` (`login_account`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_0900_ai_ci COMMENT ='用户基本信息表';



INSERT INTO `user_login_account` (`id`, `uid`, `login_account`, `account_type`)
VALUES ('13401bc84fcc27970a4dbc282d224333', 1000, 'admin@zhiqiyun.com', 1),
       ('8604638900001fbee1de5551e67ea9e3', 1000, '18888888888', 2);


DROP TABLE IF EXISTS `user_login_logs`;
CREATE TABLE IF NOT EXISTS `user_login_logs`
(
    `id`            bigint(20)   NOT NULL AUTO_INCREMENT,
    `login_account` varchar(50)  NOT NULL,
    `account_type`  int(5)       NOT NULL,
    `login_ip`      varchar(20)  NOT NULL,
    `login_local`   varchar(100) NOT NULL,
    `from_source`   varchar(20) DEFAULT NULL,
    `device_id`     varchar(45) DEFAULT NULL COMMENT '登录的设备号',
    `login_time`    datetime     NOT NULL,
    `state`         int(5)       NOT NULL,
    PRIMARY KEY (`id`),
    KEY `login_account_index` (`login_account`),
    KEY `account_type_index` (`account_type`),
    KEY `from_source_index` (`from_source`),
    KEY `login_ip_index` (`login_ip`),
    KEY `login_time_index` (`login_time`),
    KEY `state_index` (`state`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 406
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_0900_ai_ci COMMENT ='用户登录日志';


DROP TABLE IF EXISTS `user_operate_logs`;
CREATE TABLE IF NOT EXISTS `user_operate_logs`
(
    `id`             varchar(50)  NOT NULL COMMENT '请请ID',
    `uid`            bigint(20)   NOT NULL DEFAULT '0' COMMENT '用户ID',
    `tags`           varchar(100)          DEFAULT NULL COMMENT '标签说明',
    `from_source`    varchar(20)           DEFAULT NULL COMMENT '来源',
    `request_url`    varchar(200) NOT NULL COMMENT '请求地址',
    `request_ip`     varchar(50)  NOT NULL COMMENT '请求IP地址',
    `request_params` text         NOT NULL COMMENT '请求参数',
    `request_data`   text         NOT NULL COMMENT '请求主体参数',
    `request_time`   datetime     NOT NULL COMMENT '请求时间',
    `response_time`  datetime              DEFAULT NULL COMMENT '响应时间',
    PRIMARY KEY (`id`),
    KEY `uid_index` (`uid`),
    KEY `request_time_index` (`request_time`),
    KEY `respone_time_index` (`response_time`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_0900_ai_ci COMMENT ='用户操作日志';


DROP TABLE IF EXISTS `user_password`;
CREATE TABLE IF NOT EXISTS `user_password`
(
    `id`            varchar(32) NOT NULL,
    `uid`           bigint(20)  NOT NULL,
    `passwd`        varchar(50) NOT NULL,
    `salt`          varchar(8)  NOT NULL,
    `password_type` int(1)      NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uid_type_unique` (`uid`, `password_type`),
    KEY `uid_index` (`uid`),
    KEY `password_type_index` (`password_type`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_0900_ai_ci COMMENT ='用户基本信息表';


INSERT INTO `user_password` (`id`, `uid`, `passwd`, `salt`, `password_type`)
VALUES ('f37011db6434955899ea64fb3ebe888f', 1000, '4257ff6ac1cd7c0dbb2f508efa6ebfc4', 'OcZII0zZ', 1);


DROP TABLE IF EXISTS `user_role`;
CREATE TABLE IF NOT EXISTS `user_role`
(
    `id`      bigint(20) NOT NULL AUTO_INCREMENT,
    `uid`     bigint(20) NOT NULL,
    `role_id` bigint(20) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `uid_index` (`uid`),
    KEY `role_id_index` (`role_id`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 1040 COMMENT ='管理平台用户与角色关联表';

INSERT INTO `user_role` (`id`, `uid`, `role_id`)
VALUES (1035, 1000, 1000);
