MQTTvue/mqtt-vue-dashboard/server/database/init.sql

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE DATABASE IF NOT EXISTS `mqtt_vue_dashboard` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `mqtt_vue_dashboard`;

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` varchar(36) NOT NULL COMMENT '用户ID',
  `username` varchar(50) NOT NULL COMMENT '用户名',
  `password` varchar(255) NOT NULL COMMENT '密码(加密)',
  `email` varchar(100) DEFAULT NULL COMMENT '邮箱',
  `role` enum('admin','user','viewer') NOT NULL DEFAULT 'user',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp COMMENT '创建时间',
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';

INSERT INTO `users` (`id`, `username`, `password`, `email`, `role`) VALUES
(UUID(), 'admin', '$2a$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2uheWG/igi.', 'admin@example.com', 'admin');

DROP TABLE IF EXISTS `pages`;
CREATE TABLE `pages` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL COMMENT '页面名称',
  `path` varchar(100) NOT NULL COMMENT '页面路径',
  `description` varchar(200) DEFAULT NULL COMMENT '页面描述',
  `created_at` timestamp NULL DEFAULT current_timestamp,
  `updated_at` timestamp NULL DEFAULT current_timestamp ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_path` (`path`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `pages` (`name`, `path`, `description`) VALUES
('仪表板', '/dashboard', '系统概览和数据统计'),
('设备管理', '/devices', '设备列表和状态监控'),
('房间管理', '/rooms', '房间和设备绑定'),
('OTA升级', '/ota', '固件管理和OTA升级'),
('连接管理', '/connections', 'MQTT连接监控'),
('消息管理', '/messages', 'MQTT消息查看'),
('传感器数据', '/sensor-data', '传感器数据可视化'),
('客户端认证', '/client-auth', 'MQTT客户端认证管理'),
('访问控制', '/client-acl', 'MQTT客户端ACL管理'),
('认证日志', '/auth-log', '认证操作日志'),
('系统日志', '/system-log', '系统运行日志'),
('系统设置', '/settings', '系统配置管理');

DROP TABLE IF EXISTS `user_permissions`;
CREATE TABLE `user_permissions` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` varchar(36) NOT NULL,
  `page_id` int NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user_page` (`user_id`, `page_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `devices`;
CREATE TABLE `devices` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '设备ID',
  `clientid` varchar(100) NOT NULL COMMENT 'MQTT客户端ID',
  `device_name` varchar(100) DEFAULT NULL COMMENT '设备名称',
  `username` varchar(100) DEFAULT NULL,
  `firmware_version` varchar(50) DEFAULT NULL COMMENT '固件版本',
  `device_ip_port` varchar(100) DEFAULT NULL COMMENT '设备IP:端口',
  `last_ip_port` varchar(100) DEFAULT NULL COMMENT '最后连接的IP:端口',
  `status` enum('online','offline','unknown') DEFAULT 'unknown' COMMENT '设备状态',
  `last_event_time` datetime DEFAULT NULL COMMENT '最后事件时间',
  `last_online_time` datetime DEFAULT NULL COMMENT '最后上线时间',
  `last_offline_time` datetime DEFAULT NULL COMMENT '最后下线时间',
  `online_duration` int DEFAULT 0 COMMENT '累计在线时长(秒)',
  `connect_count` int DEFAULT 0 COMMENT '累计连接次数',
  `rssi` int DEFAULT NULL COMMENT 'WiFi信号强度(dBm)',
  `created_at` timestamp NULL DEFAULT current_timestamp COMMENT '设备创建时间',
  `updated_at` timestamp NULL DEFAULT current_timestamp ON UPDATE CURRENT_TIMESTAMP COMMENT '设备更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `clientid` (`clientid`),
  KEY `idx_status` (`status`),
  KEY `idx_ip_port` (`device_ip_port`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='设备管理表';

DROP TABLE IF EXISTS `rooms`;
CREATE TABLE `rooms` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '房间唯一标识ID',
  `name` varchar(100) NOT NULL COMMENT '房间名称',
  `floor_id` int NOT NULL COMMENT '所在楼层ID',
  `room_number` varchar(20) NOT NULL COMMENT '房间编号',
  `room_type` varchar(50) NOT NULL COMMENT '房间类型',
  `area` decimal(8,2) DEFAULT NULL COMMENT '房间面积(平方米)',
  `description` text DEFAULT NULL COMMENT '房间描述',
  `status` enum('active','inactive','maintenance') DEFAULT 'active' COMMENT '房间状态',
  `orientation` varchar(20) DEFAULT '东' COMMENT '房间朝向',
  `created_at` timestamp NULL DEFAULT current_timestamp COMMENT '创建时间',
  `updated_at` timestamp NULL DEFAULT current_timestamp ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_floor_id` (`floor_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='房间表';

DROP TABLE IF EXISTS `room_devices`;
CREATE TABLE `room_devices` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '设备唯一标识ID',
  `name` varchar(100) NOT NULL COMMENT '设备名称',
  `type` varchar(50) NOT NULL COMMENT '设备类型',
  `model` varchar(100) DEFAULT NULL COMMENT '设备型号',
  `room_id` int NOT NULL COMMENT '所属房间ID',
  `status` enum('online','offline','error') DEFAULT 'offline' COMMENT '设备状态',
  `last_seen` timestamp NULL DEFAULT NULL COMMENT '最后在线时间',
  `properties` longtext DEFAULT NULL COMMENT '设备属性JSON',
  `created_at` timestamp NULL DEFAULT current_timestamp COMMENT '创建时间',
  `updated_at` timestamp NULL DEFAULT current_timestamp ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_room_id` (`room_id`),
  KEY `idx_status` (`status`),
  CONSTRAINT `room_devices_ibfk_1` FOREIGN KEY (`room_id`) REFERENCES `rooms` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='房间设备表';

DROP TABLE IF EXISTS `device_bindings`;
CREATE TABLE `device_bindings` (
  `id` int NOT NULL AUTO_INCREMENT,
  `device_clientid` varchar(100) NOT NULL COMMENT '设备客户端ID',
  `room_id` int NOT NULL COMMENT '房间ID',
  `device_name` varchar(255) DEFAULT NULL COMMENT '在房间中的设备显示名称',
  `device_type` varchar(100) DEFAULT NULL COMMENT '在房间中的设备类型',
  `properties` text DEFAULT NULL COMMENT 'JSON格式额外属性',
  `created_at` timestamp NULL DEFAULT current_timestamp COMMENT '创建时间',
  `updated_at` timestamp NULL DEFAULT current_timestamp ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_device_binding` (`device_clientid`),
  KEY `idx_room_id` (`room_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='设备与房间绑定关系表';

DROP TABLE IF EXISTS `mqtt_messages`;
CREATE TABLE `mqtt_messages` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `clientid` varchar(100) NOT NULL COMMENT '客户端ID',
  `topic` varchar(512) NOT NULL COMMENT '消息主题',
  `payload` text DEFAULT NULL COMMENT '消息内容',
  `qos` tinyint NOT NULL DEFAULT 0 COMMENT 'QoS等级',
  `retain` tinyint(1) NOT NULL DEFAULT 0 COMMENT '保留标志',
  `message_id` varchar(64) DEFAULT NULL COMMENT 'MQTT消息ID',
  `message_type` enum('publish','subscribe','unsubscribe') NOT NULL DEFAULT 'publish' COMMENT '消息类型',
  `timestamp` bigint NOT NULL COMMENT '消息时间戳(毫秒)',
  `node` varchar(100) NOT NULL DEFAULT '' COMMENT '节点',
  `username` varchar(100) DEFAULT NULL COMMENT '用户名',
  `proto_ver` tinyint NOT NULL DEFAULT 4 COMMENT 'MQTT协议版本',
  `payload_format` varchar(50) DEFAULT 'text' COMMENT '消息格式',
  `created_at` timestamp NULL DEFAULT current_timestamp COMMENT '记录创建时间',
  `message_time` datetime NOT NULL DEFAULT current_timestamp COMMENT '消息时间',
  PRIMARY KEY (`id`),
  KEY `idx_clientid` (`clientid`),
  KEY `idx_topic` (`topic`(255)),
  KEY `idx_timestamp` (`timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='MQTT消息存储表';

DROP TABLE IF EXISTS `client_connections`;
CREATE TABLE `client_connections` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `username` varchar(100) DEFAULT NULL COMMENT '客户端用户名',
  `clientid` varchar(100) NOT NULL COMMENT '客户端ID',
  `event` varchar(50) NOT NULL COMMENT '事件类型',
  `timestamp` datetime DEFAULT NULL COMMENT '事件时间',
  `connected_at` datetime DEFAULT NULL COMMENT '连接时间',
  `node` varchar(100) NOT NULL DEFAULT '' COMMENT '节点',
  `peername` varchar(100) DEFAULT NULL COMMENT '客户端地址',
  `sockname` varchar(100) DEFAULT NULL COMMENT '服务端地址',
  `proto_name` varchar(20) NOT NULL DEFAULT 'MQTT' COMMENT '协议名称',
  `proto_ver` int NOT NULL DEFAULT 4 COMMENT '协议版本',
  `keepalive` int NOT NULL DEFAULT 60 COMMENT '心跳间隔(秒)',
  `clean_start` tinyint(1) DEFAULT 1 COMMENT '是否清洁会话',
  `reason` varchar(50) DEFAULT NULL COMMENT '断开原因',
  `connection_duration` int DEFAULT NULL COMMENT '连接持续时间(秒)',
  `created_at` timestamp NULL DEFAULT current_timestamp COMMENT '记录创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_clientid` (`clientid`),
  KEY `idx_timestamp` (`timestamp`),
  KEY `idx_event` (`event`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='客户端连接事件表';

DROP TABLE IF EXISTS `client_auth`;
CREATE TABLE `client_auth` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '客户端认证记录ID',
  `username` varchar(255) NOT NULL COMMENT '客户端用户名',
  `clientid` varchar(255) NOT NULL COMMENT '客户端ID',
  `password_hash` varchar(255) NOT NULL COMMENT '密码哈希值',
  `salt` varchar(255) DEFAULT NULL,
  `status` enum('enabled','disabled') DEFAULT 'enabled' COMMENT '客户端状态',
  `device_type` varchar(100) DEFAULT 'unknown' COMMENT '设备类型',
  `auth_method` enum('password','token','certificate','external') NOT NULL DEFAULT 'password' COMMENT '认证方法',
  `description` text DEFAULT NULL COMMENT '客户端描述',
  `is_superuser` tinyint(1) DEFAULT 0 COMMENT '是否为超级用户',
  `use_salt` tinyint(1) DEFAULT 1 COMMENT '是否使用盐值加密',
  `created_at` timestamp NULL DEFAULT current_timestamp COMMENT '创建时间',
  `updated_at` timestamp NULL DEFAULT current_timestamp ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `last_login_at` timestamp NULL DEFAULT NULL COMMENT '最后登录时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_username_clientid` (`username`, `clientid`),
  KEY `idx_username` (`username`),
  KEY `idx_clientid` (`clientid`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='客户端认证表';

DROP TABLE IF EXISTS `client_acl`;
CREATE TABLE `client_acl` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `username` varchar(100) NOT NULL COMMENT '用户名',
  `topic` varchar(255) NOT NULL COMMENT 'MQTT主题',
  `action` enum('publish','subscribe','pubsub') NOT NULL COMMENT '操作类型',
  `permission` enum('allow','deny') NOT NULL DEFAULT 'allow' COMMENT '权限类型',
  `priority` int DEFAULT 0 COMMENT '权限优先级',
  `description` text DEFAULT NULL COMMENT '权限规则描述',
  `created_at` timestamp NULL DEFAULT current_timestamp COMMENT '创建时间',
  `updated_at` timestamp NULL DEFAULT current_timestamp ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_topic` (`topic`),
  KEY `idx_action` (`action`),
  KEY `idx_permission` (`permission`),
  KEY `idx_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='客户端访问控制列表';

DROP TABLE IF EXISTS `auth_log`;
CREATE TABLE `auth_log` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `clientid` varchar(255) NOT NULL COMMENT '客户端ID',
  `username` varchar(255) NOT NULL COMMENT '用户名',
  `ip_address` varchar(45) NOT NULL COMMENT '客户端IP地址',
  `operation_type` enum('connect','publish','subscribe','disconnect') NOT NULL COMMENT '操作类型',
  `auth_method` varchar(50) DEFAULT NULL COMMENT '认证方法',
  `result` enum('success','failure') NOT NULL COMMENT '认证结果',
  `reason` varchar(255) DEFAULT NULL COMMENT '认证失败原因',
  `topic` varchar(255) DEFAULT NULL COMMENT 'MQTT主题',
  `created_at` timestamp NULL DEFAULT current_timestamp COMMENT '日志记录时间',
  PRIMARY KEY (`id`),
  KEY `idx_clientid` (`clientid`),
  KEY `idx_username` (`username`),
  KEY `idx_operation_type` (`operation_type`),
  KEY `idx_result` (`result`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='认证日志表';

DROP TABLE IF EXISTS `sensor_data`;
CREATE TABLE `sensor_data` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `device_id` varchar(100) NOT NULL COMMENT '设备ID',
  `topic` varchar(200) NOT NULL COMMENT 'MQTT主题',
  `data_type` varchar(50) DEFAULT NULL COMMENT '数据类型',
  `value` text DEFAULT NULL COMMENT '原始数据',
  `timestamp` datetime NOT NULL COMMENT '数据时间戳',
  `created_at` timestamp NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `idx_device_timestamp` (`device_id`, `timestamp`),
  KEY `idx_topic` (`topic`),
  KEY `idx_data_type` (`data_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='传感器数据表';

DROP TABLE IF EXISTS `firmware_files`;
CREATE TABLE `firmware_files` (
  `id` int NOT NULL AUTO_INCREMENT,
  `version` varchar(50) NOT NULL,
  `filename` varchar(255) NOT NULL,
  `filepath` varchar(255) NOT NULL,
  `filesize` bigint NOT NULL,
  `md5sum` varchar(32) NOT NULL,
  `description` text DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'active',
  `created_by` varchar(50) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp,
  `updated_at` timestamp NULL DEFAULT current_timestamp ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `version` (`version`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='固件文件表';

DROP TABLE IF EXISTS `ota_tasks`;
CREATE TABLE `ota_tasks` (
  `id` int NOT NULL AUTO_INCREMENT,
  `device_id` varchar(255) NOT NULL,
  `firmware_id` int NOT NULL,
  `status` enum('pending','downloading','installing','success','failed') DEFAULT 'pending',
  `progress` int DEFAULT 0,
  `error_message` text DEFAULT NULL,
  `start_time` timestamp NULL DEFAULT NULL,
  `end_time` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp,
  `updated_at` timestamp NULL DEFAULT current_timestamp ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_device_id` (`device_id`),
  KEY `idx_firmware_id` (`firmware_id`),
  KEY `idx_status` (`status`),
  CONSTRAINT `ota_tasks_ibfk_1` FOREIGN KEY (`firmware_id`) REFERENCES `firmware_files` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='OTA升级任务表';

DROP TABLE IF EXISTS `system_logs`;
CREATE TABLE `system_logs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `level` enum('info','warn','error','debug') NOT NULL COMMENT '日志级别',
  `message` text NOT NULL COMMENT '日志消息',
  `source` varchar(255) NOT NULL COMMENT '日志来源',
  `module` varchar(255) DEFAULT NULL COMMENT '模块名称',
  `user_id` int DEFAULT NULL COMMENT '用户ID',
  `username` varchar(255) DEFAULT NULL COMMENT '用户名',
  `ip_address` varchar(45) DEFAULT NULL COMMENT 'IP地址',
  `details` text DEFAULT NULL COMMENT '详细信息',
  `created_at` datetime DEFAULT current_timestamp COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_level` (`level`),
  KEY `idx_source` (`source`),
  KEY `idx_module` (`module`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='系统运行日志表';

DROP TABLE IF EXISTS `wifi_configurations`;
CREATE TABLE `wifi_configurations` (
  `id` int NOT NULL AUTO_INCREMENT,
  `device_clientid` varchar(255) NOT NULL COMMENT '设备客户端ID',
  `ssid` varchar(32) NOT NULL COMMENT 'WiFi SSID',
  `password` varchar(64) NOT NULL COMMENT 'WiFi密码',
  `status` enum('pending','sent','applied','failed') DEFAULT 'sent' COMMENT '配置状态',
  `sent_at` timestamp NULL DEFAULT NULL COMMENT '发送时间',
  `applied_at` timestamp NULL DEFAULT NULL COMMENT '应用时间',
  `created_at` timestamp NULL DEFAULT current_timestamp COMMENT '创建时间',
  `updated_at` timestamp NULL DEFAULT current_timestamp ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_device_clientid` (`device_clientid`),
  KEY `idx_status` (`status`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='设备WiFi配置表';

CREATE OR REPLACE VIEW `vw_client_connections` AS
SELECT `clientid`, `username`, `peername`, `connected_at`, `timestamp` AS `disconnected_at`,
       `reason`, `timestamp`, `event`, `node`, `sockname`, `proto_name`, `proto_ver`
FROM `client_connections`;

CREATE OR REPLACE VIEW `vw_device_logs` AS
SELECT
  NULL as id,
  cc.clientid,
  CASE
    WHEN cc.event = 'client.connected' THEN 'connect'
    WHEN cc.event = 'client.disconnected' THEN 'disconnect'
    ELSE 'unknown'
  END as event_type,
  cc.timestamp as event_time,
  NULL as topic,
  NULL as payload,
  NULL as qos,
  cc.username,
  cc.peername,
  cc.proto_ver,
  cc.node,
  cc.reason as details,
  cc.timestamp as created_at
FROM client_connections cc
WHERE cc.event IN ('client.connected', 'client.disconnected')

UNION ALL

SELECT
  mm.id,
  mm.clientid,
  CASE
    WHEN mm.message_type = 'publish' THEN 'publish'
    WHEN mm.message_type = 'subscribe' THEN 'subscribe'
    WHEN mm.message_type = 'unsubscribe' THEN 'unsubscribe'
    ELSE mm.message_type
  END as event_type,
  mm.message_time as event_time,
  mm.topic,
  mm.payload,
  mm.qos,
  mm.username,
  NULL as peername,
  mm.proto_ver,
  mm.node,
  NULL as details,
  mm.created_at
FROM mqtt_messages mm
WHERE mm.message_type IN ('publish', 'subscribe', 'unsubscribe')

ORDER BY event_time DESC;

SET FOREIGN_KEY_CHECKS = 1;