pacsonline_new/doc/pacsonlie_new.sql

/*
 Navicat Premium Data Transfer

 Source Server         : docker-mysql8
 Source Server Type    : MySQL
 Source Server Version : 80043 (8.0.43)
 Source Host           : localhost:3306
 Source Schema         : pacsonlie_new

 Target Server Type    : MySQL
 Target Server Version : 80043 (8.0.43)
 File Encoding         : 65001

 Date: 18/11/2025 14:50:42
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for notification
-- ----------------------------
DROP TABLE IF EXISTS `notification`;
CREATE TABLE `notification` (
  `id` bigint NOT NULL COMMENT '主键ID',
  `receiver_id` bigint NOT NULL COMMENT '接收者用户ID',
  `receiver_name` varchar(100) DEFAULT NULL COMMENT '接收者用户名',
  `message_type` varchar(20) NOT NULL COMMENT '消息类型：SYSTEM-系统通知，TASK-任务通知，REPORT-报告通知，MESSAGE-消息通知，REMINDER-提醒通知，WARNING-警告通知',
  `title` varchar(200) NOT NULL COMMENT '消息标题',
  `content` text COMMENT '消息内容',
  `priority` varchar(10) NOT NULL DEFAULT 'NORMAL' COMMENT '消息优先级：LOW-低，NORMAL-普通，HIGH-高，URGENT-紧急',
  `status` varchar(10) NOT NULL DEFAULT 'UNREAD' COMMENT '消息状态：UNREAD-未读，READ-已读，ARCHIVED-已归档',
  `pushed` tinyint(1) DEFAULT '0' COMMENT '是否已推送到前端：0-未推送，1-已推送',
  `business_id` varchar(50) DEFAULT NULL COMMENT '关联的业务ID（可选）',
  `business_type` varchar(50) DEFAULT NULL COMMENT '关联的业务类型（可选）',
  `sender_id` bigint DEFAULT NULL COMMENT '发送者用户ID（系统通知可为空）',
  `sender_name` varchar(100) DEFAULT NULL COMMENT '发送者用户名（系统通知可为空）',
  `send_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '发送时间',
  `read_time` datetime DEFAULT NULL COMMENT '阅读时间',
  `expire_time` datetime DEFAULT NULL COMMENT '过期时间',
  `extra_params` text COMMENT '扩展参数（JSON格式）',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted` tinyint(1) DEFAULT '0' COMMENT '删除标记：0-未删除，1-已删除',
  PRIMARY KEY (`id`),
  KEY `idx_receiver_id` (`receiver_id`),
  KEY `idx_message_type` (`message_type`),
  KEY `idx_status` (`status`),
  KEY `idx_send_time` (`send_time`),
  KEY `idx_receiver_status` (`receiver_id`,`status`),
  KEY `idx_expire_time` (`expire_time`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='消息通知表';

-- ----------------------------
-- Records of notification
-- ----------------------------
BEGIN;
INSERT INTO `notification` (`id`, `receiver_id`, `receiver_name`, `message_type`, `title`, `content`, `priority`, `status`, `pushed`, `business_id`, `business_type`, `sender_id`, `sender_name`, `send_time`, `read_time`, `expire_time`, `extra_params`, `create_time`, `update_time`, `deleted`) VALUES (1990673114661261314, 1, NULL, 'SYSTEM', '111', '111', 'NORMAL', 'UNREAD', 1, NULL, NULL, NULL, NULL, '2025-11-18 14:47:10', NULL, '2025-11-25 14:47:10', NULL, NULL, NULL, 0);
COMMIT;

-- ----------------------------
-- Table structure for sys_log
-- ----------------------------
DROP TABLE IF EXISTS `sys_log`;
CREATE TABLE `sys_log` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '日志ID',
  `user_id` bigint DEFAULT NULL COMMENT '用户ID',
  `username` varchar(50) DEFAULT NULL COMMENT '用户名',
  `real_name` varchar(50) DEFAULT NULL COMMENT '真实姓名',
  `log_type` varchar(20) NOT NULL COMMENT '日志类型：登录日志、操作日志',
  `operate_type` varchar(50) DEFAULT NULL COMMENT '操作类型：增删改查等',
  `detail` varchar(500) DEFAULT NULL COMMENT '日志详情描述',
  `controller` varchar(100) DEFAULT NULL COMMENT '控制器名称',
  `method` varchar(100) DEFAULT NULL COMMENT '方法名称',
  `request_url` varchar(255) DEFAULT NULL COMMENT '请求URL',
  `request_method` varchar(10) DEFAULT NULL COMMENT '请求方式：GET/POST/PUT/DELETE',
  `request_params` text COMMENT '请求参数',
  `operate_ip` varchar(50) DEFAULT NULL COMMENT '操作IP地址',
  `operate_location` varchar(100) DEFAULT NULL COMMENT '操作地点',
  `operate_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间',
  `use_time` bigint DEFAULT NULL COMMENT '执行时长（毫秒）',
  `status` tinyint(1) DEFAULT '1' COMMENT '状态：0-失败，1-成功',
  `error_msg` text COMMENT '错误信息',
  `browser` varchar(100) DEFAULT NULL COMMENT '浏览器',
  `os` varchar(100) DEFAULT NULL COMMENT '操作系统',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_operate_time` (`operate_time`),
  KEY `idx_log_type` (`log_type`),
  KEY `idx_username` (`username`),
  KEY `idx_log_type_time` (`log_type`,`operate_time`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='系统操作日志表';

-- ----------------------------
-- Records of sys_log
-- ----------------------------
BEGIN;
INSERT INTO `sys_log` (`id`, `user_id`, `username`, `real_name`, `log_type`, `operate_type`, `detail`, `controller`, `method`, `request_url`, `request_method`, `request_params`, `operate_ip`, `operate_location`, `operate_time`, `use_time`, `status`, `error_msg`, `browser`, `os`) VALUES (1, NULL, NULL, NULL, '登录日志', '登录', '用户登录', 'SysUserController', 'login', '/auth/login', 'POST', '{\"password\":\"******\",\"username\":\"admin\"}', '127.0.0.1', NULL, '2025-11-18 14:45:01', 494, 0, 'Bad credentials', 'Chrome', 'Mac OS');
INSERT INTO `sys_log` (`id`, `user_id`, `username`, `real_name`, `log_type`, `operate_type`, `detail`, `controller`, `method`, `request_url`, `request_method`, `request_params`, `operate_ip`, `operate_location`, `operate_time`, `use_time`, `status`, `error_msg`, `browser`, `os`) VALUES (2, 1, 'admin', '超级管理员', '登录日志', '登录', '用户登录', 'SysUserController', 'login', '/auth/login', 'POST', '{\"password\":\"******\",\"username\":\"admin\"}', '127.0.0.1', NULL, '2025-11-18 14:46:48', 403, 1, NULL, 'Chrome', 'Mac OS');
COMMIT;

-- ----------------------------
-- Table structure for sys_menu
-- ----------------------------
DROP TABLE IF EXISTS `sys_menu`;
CREATE TABLE `sys_menu` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '菜单ID',
  `menu_name` varchar(50) NOT NULL COMMENT '菜单名称',
  `parent_id` bigint NOT NULL DEFAULT '0' COMMENT '父菜单ID，0表示顶级菜单',
  `menu_type` char(1) NOT NULL COMMENT '菜单类型：M-目录，C-菜单，F-按钮',
  `path` varchar(200) DEFAULT NULL COMMENT '路由地址',
  `component` varchar(255) DEFAULT NULL COMMENT '组件路径',
  `perms` varchar(100) DEFAULT NULL COMMENT '权限标识',
  `icon` varchar(100) DEFAULT NULL COMMENT '菜单图标',
  `order_num` int NOT NULL DEFAULT '0' COMMENT '显示顺序',
  `visible` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否可见：0-隐藏，1-显示',
  `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '状态：0-禁用，1-正常',
  `create_by` varchar(50) DEFAULT NULL COMMENT '创建人',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` varchar(50) DEFAULT NULL COMMENT '更新人',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `del_flag` tinyint(1) NOT NULL DEFAULT '0' COMMENT '删除标志：0-正常，1-删除',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  KEY `idx_parent_id` (`parent_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1024 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='系统菜单表';

-- ----------------------------
-- Records of sys_menu
-- ----------------------------
BEGIN;
INSERT INTO `sys_menu` (`id`, `menu_name`, `parent_id`, `menu_type`, `path`, `component`, `perms`, `icon`, `order_num`, `visible`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `del_flag`, `remark`) VALUES (1, '系统管理', 0, 'M', '/system', NULL, NULL, 'system', 1, 1, 1, NULL, '2025-11-18 06:43:47', NULL, '2025-11-18 06:43:47', 0, NULL);
INSERT INTO `sys_menu` (`id`, `menu_name`, `parent_id`, `menu_type`, `path`, `component`, `perms`, `icon`, `order_num`, `visible`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `del_flag`, `remark`) VALUES (2, '用户中心', 0, 'M', '/user', NULL, NULL, 'user', 2, 1, 1, NULL, '2025-11-18 06:43:47', NULL, '2025-11-18 06:43:47', 0, NULL);
INSERT INTO `sys_menu` (`id`, `menu_name`, `parent_id`, `menu_type`, `path`, `component`, `perms`, `icon`, `order_num`, `visible`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `del_flag`, `remark`) VALUES (101, '用户管理', 1, 'C', '/system/user', 'system/user/index', 'system:user:list', NULL, 1, 1, 1, NULL, '2025-11-18 06:43:47', NULL, '2025-11-18 06:43:47', 0, NULL);
INSERT INTO `sys_menu` (`id`, `menu_name`, `parent_id`, `menu_type`, `path`, `component`, `perms`, `icon`, `order_num`, `visible`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `del_flag`, `remark`) VALUES (102, '角色管理', 1, 'C', '/system/role', 'system/role/index', 'system:role:list', NULL, 2, 1, 1, NULL, '2025-11-18 06:43:47', NULL, '2025-11-18 06:43:47', 0, NULL);
INSERT INTO `sys_menu` (`id`, `menu_name`, `parent_id`, `menu_type`, `path`, `component`, `perms`, `icon`, `order_num`, `visible`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `del_flag`, `remark`) VALUES (103, '菜单管理', 1, 'C', '/system/menu', 'system/menu/index', 'system:menu:list', NULL, 3, 1, 1, NULL, '2025-11-18 06:43:47', NULL, '2025-11-18 06:43:47', 0, NULL);
INSERT INTO `sys_menu` (`id`, `menu_name`, `parent_id`, `menu_type`, `path`, `component`, `perms`, `icon`, `order_num`, `visible`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `del_flag`, `remark`) VALUES (201, '个人信息', 2, 'C', '/user/profile', 'user/profile/index', 'user:profile:view', NULL, 1, 1, 1, NULL, '2025-11-18 06:43:47', NULL, '2025-11-18 06:43:47', 0, NULL);
INSERT INTO `sys_menu` (`id`, `menu_name`, `parent_id`, `menu_type`, `path`, `component`, `perms`, `icon`, `order_num`, `visible`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `del_flag`, `remark`) VALUES (1011, '用户新增', 101, 'F', NULL, NULL, 'system:user:add', NULL, 1, 1, 1, NULL, '2025-11-18 06:43:47', NULL, '2025-11-18 06:43:47', 0, NULL);
INSERT INTO `sys_menu` (`id`, `menu_name`, `parent_id`, `menu_type`, `path`, `component`, `perms`, `icon`, `order_num`, `visible`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `del_flag`, `remark`) VALUES (1012, '用户修改', 101, 'F', NULL, NULL, 'system:user:edit', NULL, 2, 1, 1, NULL, '2025-11-18 06:43:47', NULL, '2025-11-18 06:43:47', 0, NULL);
INSERT INTO `sys_menu` (`id`, `menu_name`, `parent_id`, `menu_type`, `path`, `component`, `perms`, `icon`, `order_num`, `visible`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `del_flag`, `remark`) VALUES (1013, '用户删除', 101, 'F', NULL, NULL, 'system:user:remove', NULL, 3, 1, 1, NULL, '2025-11-18 06:43:47', NULL, '2025-11-18 06:43:47', 0, NULL);
INSERT INTO `sys_menu` (`id`, `menu_name`, `parent_id`, `menu_type`, `path`, `component`, `perms`, `icon`, `order_num`, `visible`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `del_flag`, `remark`) VALUES (1021, '角色新增', 102, 'F', NULL, NULL, 'system:role:add', NULL, 1, 1, 1, NULL, '2025-11-18 06:43:47', NULL, '2025-11-18 06:43:47', 0, NULL);
INSERT INTO `sys_menu` (`id`, `menu_name`, `parent_id`, `menu_type`, `path`, `component`, `perms`, `icon`, `order_num`, `visible`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `del_flag`, `remark`) VALUES (1022, '角色修改', 102, 'F', NULL, NULL, 'system:role:edit', NULL, 2, 1, 1, NULL, '2025-11-18 06:43:47', NULL, '2025-11-18 06:43:47', 0, NULL);
INSERT INTO `sys_menu` (`id`, `menu_name`, `parent_id`, `menu_type`, `path`, `component`, `perms`, `icon`, `order_num`, `visible`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `del_flag`, `remark`) VALUES (1023, '角色删除', 102, 'F', NULL, NULL, 'system:role:remove', NULL, 3, 1, 1, NULL, '2025-11-18 06:43:47', NULL, '2025-11-18 06:43:47', 0, NULL);
COMMIT;

-- ----------------------------
-- Table structure for sys_role
-- ----------------------------
DROP TABLE IF EXISTS `sys_role`;
CREATE TABLE `sys_role` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '角色ID',
  `role_name` varchar(50) NOT NULL COMMENT '角色名称',
  `role_key` varchar(50) NOT NULL COMMENT '角色权限字符串',
  `role_sort` int NOT NULL DEFAULT '0' COMMENT '显示顺序',
  `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '状态：0-禁用，1-正常',
  `create_by` varchar(50) DEFAULT NULL COMMENT '创建人',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` varchar(50) DEFAULT NULL COMMENT '更新人',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `del_flag` tinyint(1) NOT NULL DEFAULT '0' COMMENT '删除标志：0-正常，1-删除',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_role_key` (`role_key`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='系统角色表';

-- ----------------------------
-- Records of sys_role
-- ----------------------------
BEGIN;
INSERT INTO `sys_role` (`id`, `role_name`, `role_key`, `role_sort`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `del_flag`, `remark`) VALUES (1, '超级管理员', 'admin', 1, 1, NULL, '2025-11-18 06:43:47', NULL, '2025-11-18 06:43:47', 0, NULL);
INSERT INTO `sys_role` (`id`, `role_name`, `role_key`, `role_sort`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `del_flag`, `remark`) VALUES (2, '普通用户', 'user', 2, 1, NULL, '2025-11-18 06:43:47', NULL, '2025-11-18 06:43:47', 0, NULL);
COMMIT;

-- ----------------------------
-- Table structure for sys_role_menu
-- ----------------------------
DROP TABLE IF EXISTS `sys_role_menu`;
CREATE TABLE `sys_role_menu` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `role_id` bigint NOT NULL COMMENT '角色ID',
  `menu_id` bigint NOT NULL COMMENT '菜单ID',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_role_menu` (`role_id`,`menu_id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='角色菜单关联表';

-- ----------------------------
-- Records of sys_role_menu
-- ----------------------------
BEGIN;
INSERT INTO `sys_role_menu` (`id`, `role_id`, `menu_id`) VALUES (1, 1, 1);
INSERT INTO `sys_role_menu` (`id`, `role_id`, `menu_id`) VALUES (2, 1, 2);
INSERT INTO `sys_role_menu` (`id`, `role_id`, `menu_id`) VALUES (3, 1, 101);
INSERT INTO `sys_role_menu` (`id`, `role_id`, `menu_id`) VALUES (4, 1, 102);
INSERT INTO `sys_role_menu` (`id`, `role_id`, `menu_id`) VALUES (5, 1, 103);
INSERT INTO `sys_role_menu` (`id`, `role_id`, `menu_id`) VALUES (6, 1, 201);
INSERT INTO `sys_role_menu` (`id`, `role_id`, `menu_id`) VALUES (7, 1, 1011);
INSERT INTO `sys_role_menu` (`id`, `role_id`, `menu_id`) VALUES (8, 1, 1012);
INSERT INTO `sys_role_menu` (`id`, `role_id`, `menu_id`) VALUES (9, 1, 1013);
INSERT INTO `sys_role_menu` (`id`, `role_id`, `menu_id`) VALUES (10, 1, 1021);
INSERT INTO `sys_role_menu` (`id`, `role_id`, `menu_id`) VALUES (11, 1, 1022);
INSERT INTO `sys_role_menu` (`id`, `role_id`, `menu_id`) VALUES (12, 1, 1023);
INSERT INTO `sys_role_menu` (`id`, `role_id`, `menu_id`) VALUES (13, 2, 2);
INSERT INTO `sys_role_menu` (`id`, `role_id`, `menu_id`) VALUES (14, 2, 201);
COMMIT;

-- ----------------------------
-- Table structure for sys_sms_code
-- ----------------------------
DROP TABLE IF EXISTS `sys_sms_code`;
CREATE TABLE `sys_sms_code` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `phone` varchar(20)  NOT NULL,
  `code` varchar(10)  NOT NULL,
  `expire_time` datetime NOT NULL,
  `used` tinyint NOT NULL DEFAULT '0',
  `used_time` datetime DEFAULT NULL,
  `request_ip` varchar(64)  DEFAULT NULL,
  `send_status` tinyint NOT NULL DEFAULT '1',
  `send_msg` varchar(255)  DEFAULT NULL,
  `create_time` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='短信验证码记录';

-- ----------------------------
-- Records of sys_sms_code
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for sys_user
-- ----------------------------
DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `username` varchar(50) NOT NULL COMMENT '用户名',
  `password` varchar(100) NOT NULL COMMENT '密码',
  `nickname` varchar(50) DEFAULT NULL COMMENT '昵称',
  `email` varchar(100) DEFAULT NULL COMMENT '邮箱',
  `phone` varchar(20) DEFAULT NULL COMMENT '手机号',
  `avatar` varchar(255) DEFAULT NULL COMMENT '头像',
  `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '状态：0-禁用，1-正常',
  `create_by` varchar(50) DEFAULT NULL COMMENT '创建人',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` varchar(50) DEFAULT NULL COMMENT '更新人',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `del_flag` tinyint(1) NOT NULL DEFAULT '0' COMMENT '删除标志：0-正常，1-删除',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='系统用户表';

-- ----------------------------
-- Records of sys_user
-- ----------------------------
BEGIN;
INSERT INTO `sys_user` (`id`, `username`, `password`, `nickname`, `email`, `phone`, `avatar`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `del_flag`, `remark`) VALUES (1, 'admin', '$2a$10$AlzNDWmP7wjhH1mCij52n.mbPadKY7VZa3UZDEwQ4CZEIgdwc2gIK', '超级管理员', NULL, NULL, NULL, 1, NULL, '2025-11-18 06:43:47', NULL, '2025-11-18 06:46:40', 0, NULL);
INSERT INTO `sys_user` (`id`, `username`, `password`, `nickname`, `email`, `phone`, `avatar`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `del_flag`, `remark`) VALUES (2, 'user', '$2a$10$N.ZOn9G6/YLFixAOPMg/h.z7pCu6v2XyFDtC4q.jeeGm/TEZySmDu', '普通用户', NULL, NULL, NULL, 1, NULL, '2025-11-18 06:43:47', NULL, '2025-11-18 06:43:47', 0, NULL);
COMMIT;

-- ----------------------------
-- Table structure for sys_user_role
-- ----------------------------
DROP TABLE IF EXISTS `sys_user_role`;
CREATE TABLE `sys_user_role` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `user_id` bigint NOT NULL COMMENT '用户ID',
  `role_id` bigint NOT NULL COMMENT '角色ID',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user_role` (`user_id`,`role_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户角色关联表';

-- ----------------------------
-- Records of sys_user_role
-- ----------------------------
BEGIN;
INSERT INTO `sys_user_role` (`id`, `user_id`, `role_id`) VALUES (1, 1, 1);
INSERT INTO `sys_user_role` (`id`, `user_id`, `role_id`) VALUES (2, 2, 2);
COMMIT;

-- ----------------------------
-- Table structure for user_online_status
-- ----------------------------
DROP TABLE IF EXISTS `user_online_status`;
CREATE TABLE `user_online_status` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` bigint NOT NULL COMMENT '用户ID',
  `username` varchar(100) DEFAULT NULL COMMENT '用户名',
  `session_id` varchar(100) NOT NULL COMMENT 'WebSocket会话ID',
  `server_ip` varchar(50) DEFAULT NULL COMMENT '服务器IP',
  `client_ip` varchar(50) DEFAULT NULL COMMENT '客户端IP',
  `browser` varchar(100) DEFAULT NULL COMMENT '浏览器信息',
  `os` varchar(100) DEFAULT NULL COMMENT '操作系统信息',
  `status` varchar(20) DEFAULT 'ONLINE' COMMENT '状态：ONLINE-在线，OFFLINE-离线',
  `connect_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '连接时间',
  `last_heartbeat` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '最后心跳时间',
  `disconnect_time` datetime DEFAULT NULL COMMENT '断开时间',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_session_id` (`session_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_status` (`status`),
  KEY `idx_last_heartbeat` (`last_heartbeat`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户在线状态表';

-- ----------------------------
-- Records of user_online_status
-- ----------------------------
BEGIN;
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;