/**
Source Server Version: 8.0.31
Source Database: backendsys
Date: 2023/05/23 17:09:22
*/

DROP TABLE IF EXISTS `sys_user_info`;

/* `role_ids` VARCHAR(256) NOT NULL COMMENT '角色ID (多个,以逗号隔开)', */
CREATE TABLE `sys_user_info` (
    PRIMARY KEY (`id`),
    `id` BIGINT(10) NOT NULL AUTO_INCREMENT COMMENT 'ID',
    `user_id` BIGINT(10) NOT NULL COMMENT '系统用户ID',
    `nickname` VARCHAR(20) COMMENT '昵称',
    `email` VARCHAR(50) COMMENT '邮箱',
    `phone` VARCHAR(20) COMMENT '手机号码',
    `gender` TINYINT(1) COMMENT '性别(1男, 2女, 3保密)',
    `avatar` VARCHAR(1000) COMMENT '头像',
    `last_login_uuid` VARCHAR(36) COMMENT '最后登录UUID',
    `last_login_ip` VARCHAR(20) COMMENT '最后登录IP',
    `last_login_time` DATETIME COMMENT '最后登录时间',
    `is_super` TINYINT(1) DEFAULT '-1' COMMENT '是否超级管理员 (-1否, 1是)',
    `audit_status` TINYINT(1) DEFAULT '1' COMMENT '账号审核状态 (-1审核拒绝, 1待审核, 2审核通过)',
    `audit_note` VARCHAR(255) COMMENT '账号审核备注',
    `point_balance` FLOAT DEFAULT 0 COMMENT '积分余额',
    `invite_code` VARCHAR(255) COMMENT '邀请码',
    `status` TINYINT(1) DEFAULT '1' COMMENT '账号状态 (-1禁用, 1启用)',
    `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `del_flag` TINYINT(1) DEFAULT '-1' COMMENT '删除标志 (-1未删除, 1删除)',
    UNIQUE KEY (`nickname`),
    INDEX `idx_nickname` (`nickname`),
    INDEX `idx_phone` (`phone`),
    INDEX `idx_email` (`email`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='系统用户表';

# 禁用外键
# FOREIGN KEY (`user_id`) REFERENCES `sys_user`(`id`) ON DELETE CASCADE

# ALTER TABLE sys_user_info DROP FOREIGN KEY sys_user_info_ibfk_1;


# 创建一个触发器 (每次更新时，自动更新 update_time 时间字段) (使用应用层逻辑替代)
# CREATE TRIGGER update_user_info_trigger
#     BEFORE UPDATE ON sys_user_info
#     FOR EACH ROW
#     SET NEW.update_time = NOW();

# 设置索引
# INDEX `idx_nickname` (`nickname`)

# 设置唯一索引
# UNIQUE KEY (`nickname`)

# 创建外键 id 与 user_id 关联
# FOREIGN KEY (`user_id`) REFERENCES `sys_user`(`id`) ON DELETE CASCADE


INSERT INTO sys_user_info(user_id, nickname, email, phone, gender, is_super, audit_status, audit_note, status, avatar, invite_code, create_time) VALUES
    (1, '超人', 'admin@qq.com', '13123412331', '1', '1', '2', '同意通过备注', '1', null, '12c9dd17-b7f4-4483-a513-fbcc36512d8d', '2023-07-19 10:45:00'),
    (2, '测试账号', '1111@qq.com', '13333311111', '1', '1', '2', '同意通过备注', '1', null, '12c9dd17-b7f4-4483-a513-fbcc36512d8d', '2023-07-19 10:45:01'),
    (3, '测试账号2', '2222@qq.com', '13333311112', '2', '1', '2', '同意通过备注', '1', null, '12c9dd17-b7f4-4483-a513-fbcc36512d8d', '2023-07-19 10:45:02'),
    (4, 'aaa', 'aaa@qq.com', '13333311113', '2', '-1', '1', '', '1', null, '12c9dd17-b7f4-4483-a513-fbcc36512d8d', '2023-07-19 10:45:03'),
    (5, 'bbb', 'bbb@qq.com', '13333311114', '2', '-1', '-1', '拒绝通过备注', '1', null, '12c9dd17-b7f4-4483-a513-fbcc36512d8d', '2023-07-19 10:45:04'),
    (6, 'ccc', 'ccc@qq.com', '13333311115', '1', '-1', '-1', '拒绝通过备注', '1', null, '12c9dd17-b7f4-4483-a513-fbcc36512d8d', '2023-07-19 10:45:05'),
    (7, 'ddd', 'ddd@qq.com', '13333311116', '1', '-1', '-1', '拒绝通过备注', '1', null, '12c9dd17-b7f4-4483-a513-fbcc36512d8d', '2023-07-19 10:45:06'),
    (8, 'eee', 'eee@qq.com', '13333311117', '1', '-1', '-1', '拒绝通过备注', '1', null, '12c9dd17-b7f4-4483-a513-fbcc36512d8d', '2023-07-19 10:45:07'),
    (9, 'fff', 'fff@qq.com', '13333311118', '1', '-1', '-1', '拒绝通过备注', '1', null, '12c9dd17-b7f4-4483-a513-fbcc36512d8d', '2023-07-19 10:45:08'),
    (10, 'ggg', 'ggg@qq.com', '13333311119', '1', '-1', '-1', '拒绝通过备注', '-1', null, '12c9dd17-b7f4-4483-a513-fbcc36512d8d', '2023-07-19 10:45:09'),
    (11, 'hhh', 'hhh@qq.com', '13333311120', '1', '-1', '1', '', '-1', null, '12c9dd17-b7f4-4483-a513-fbcc36512d8d', '2023-07-19 10:45:10'),
    (12, 'iii', 'iii@qq.com', '13333311121', '1', '-1', '2', '同意通过备注', '1', null, '12c9dd17-b7f4-4483-a513-fbcc36512d8d', '2023-07-19 10:45:11'),
    (13, 'Lu', 'lulu@qq.com', '13333333330', '1', '1', '2', '同意通过备注', '1', null, '12c9dd17-b7f4-4483-a513-fbcc36512d8d', '2024-03-06 15:14:11'),
    (14, '李美莹', 'Suui_isoo@qq.com', '18688028666', '2', '1', '2', '同意通过备注', '1', null, '12c9dd17-b7f4-4483-a513-fbcc36512d8d', '2024-03-06 15:14:12'),
    (15, '陈嘉阳', 'chak0804@qq.com', '13333333333', '1', '1', '2', '同意通过备注', '1', null, '12c9dd17-b7f4-4483-a513-fbcc36512d8d', '2024-03-06 15:14:13'),
    (16, '洪泽鑫', 'zzzzexin@qq.com', '13333333334', '1', '1', '2', '同意通过备注', '1', null, '12c9dd17-b7f4-4483-a513-fbcc36512d8d', '2024-03-06 15:14:14')
;