vueiot-api/myiot.sql

/*
 Navicat Premium Dump SQL

 Source Server         : 192.168.3.22
 Source Server Type    : MySQL
 Source Server Version : 101106 (10.11.6-MariaDB-0+deb12u1)
 Source Host           : localhost:3306
 Source Schema         : myiot

 Target Server Type    : MySQL
 Target Server Version : 101106 (10.11.6-MariaDB-0+deb12u1)
 File Encoding         : 65001

 Date: 08/03/2025 03:15:55
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for authz_checks
-- ----------------------------
DROP TABLE IF EXISTS `authz_checks`;
CREATE TABLE `authz_checks`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `client_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `topic` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `action` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `result` enum('allow','deny') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `timestamp` datetime NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 55909 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for devices
-- ----------------------------
DROP TABLE IF EXISTS `devices`;
CREATE TABLE `devices`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `device_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `room_id` int NULL DEFAULT NULL,
  `room_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '房间名称',
  `status` enum('online','offline') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT 'offline',
  `last_seen` datetime NULL DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `temperature` decimal(5, 2) NULL DEFAULT NULL COMMENT '设备温度',
  `upload_time` datetime NULL DEFAULT NULL COMMENT '数据上传时间',
  `switch_status` enum('on','off') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '开关状态',
  `switch_status_time` datetime NULL DEFAULT NULL COMMENT '开关状态变化时间',
  `level_status` enum('high','low') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '高低电平状态',
  `level_status_time` datetime NULL DEFAULT NULL COMMENT '高低电平状态变化时间',
  `bound_device_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '绑定设备的ID',
  `bound_time` datetime NULL DEFAULT NULL COMMENT '绑定时间',
  `first_online_time` datetime NULL DEFAULT NULL COMMENT '第一次上线时间',
  `last_online_time` datetime NULL DEFAULT NULL COMMENT '最近一次上线时间',
  `last_offline_time` datetime NULL DEFAULT NULL COMMENT '最近一次离线时间',
  `ip_address` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `system_version` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT 'v1.0.0' COMMENT '系统版本',
  `firmware_version` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '1.0.0',
  `last_upgrade_time` datetime NULL DEFAULT NULL,
  `upgrade_status` enum('pending','upgrading','completed','failed') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT 'pending',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `device_id`(`device_id` ASC) USING BTREE,
  INDEX `room_id`(`room_id` ASC) USING BTREE,
  CONSTRAINT `devices_ibfk_1` FOREIGN KEY (`room_id`) REFERENCES `rooms` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 2749 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for error_logs
-- ----------------------------
DROP TABLE IF EXISTS `error_logs`;
CREATE TABLE `error_logs`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `error_message` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `error_time` datetime NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for firmware_versions
-- ----------------------------
DROP TABLE IF EXISTS `firmware_versions`;
CREATE TABLE `firmware_versions`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `version_number` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `device_type` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `release_date` datetime NOT NULL,
  `file_path` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `file_size` bigint NOT NULL,
  `is_signed` tinyint(1) NULL DEFAULT 0,
  `signature` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `release_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `version_number`(`version_number` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for gpio_state
-- ----------------------------
DROP TABLE IF EXISTS `gpio_state`;
CREATE TABLE `gpio_state`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `device_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `state` enum('high','low') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `timestamp` timestamp NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 238 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for heater_usage
-- ----------------------------
DROP TABLE IF EXISTS `heater_usage`;
CREATE TABLE `heater_usage`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `device_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `room_id` int NOT NULL COMMENT '关联房间ID',
  `room_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `start_time` datetime NOT NULL,
  `end_time` datetime NULL DEFAULT NULL,
  `duration` int NULL DEFAULT 0 COMMENT '使用时长（秒）',
  `date` date NOT NULL COMMENT '统计日期',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `device_id`(`device_id` ASC) USING BTREE,
  INDEX `idx_room_date`(`room_id` ASC, `date` ASC) USING BTREE,
  CONSTRAINT `heater_usage_ibfk_1` FOREIGN KEY (`device_id`) REFERENCES `devices` (`device_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `heater_usage_ibfk_2` FOREIGN KEY (`room_id`) REFERENCES `rooms` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for relay_state
-- ----------------------------
DROP TABLE IF EXISTS `relay_state`;
CREATE TABLE `relay_state`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `device_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `state` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `temperature` float NULL DEFAULT NULL,
  `room_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `room_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `timestamp` datetime NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1002 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for rooms
-- ----------------------------
DROP TABLE IF EXISTS `rooms`;
CREATE TABLE `rooms`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `room_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `floor` int NULL DEFAULT NULL,
  `orientation` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `room_name`(`room_name` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 38 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for switch_status
-- ----------------------------
DROP TABLE IF EXISTS `switch_status`;
CREATE TABLE `switch_status`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `device_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `status` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `timestamp` datetime NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `switch_status_ibfk_1`(`device_id` ASC) USING BTREE,
  CONSTRAINT `switch_status_ibfk_1` FOREIGN KEY (`device_id`) REFERENCES `devices` (`device_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 598 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `username`(`username` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Procedure structure for ProcessHeaterUsage
-- ----------------------------
DROP PROCEDURE IF EXISTS `ProcessHeaterUsage`;
delimiter ;;
CREATE PROCEDURE `ProcessHeaterUsage`(IN p_device_id VARCHAR(255),
    IN p_event_time DATETIME,
    IN p_raw_id INT)
BEGIN
    DECLARE v_room_id INT;
    DECLARE v_room_name VARCHAR(255);
    DECLARE v_device_status ENUM('online','offline');
    DECLARE v_switch_status ENUM('on','off');
    DECLARE v_temperature DECIMAL(5,2);
    
    -- 唯一的异常处理器声明
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        INSERT INTO error_logs (error_message, error_time)
        VALUES (CONCAT('Heater usage processing failed for ', p_device_id), NOW());
    END;

    -- 获取设备最新状态
    SELECT 
        d.room_id,
        d.room_name,
        d.status,
        d.switch_status,
        d.temperature
    INTO 
        v_room_id,
        v_room_name,
        v_device_status,
        v_switch_status,
        v_temperature
    FROM devices d
    WHERE d.device_id = p_device_id
    LIMIT 1;
    
    -- 开启条件：在线、开关开启、温度>14
    IF v_device_status = 'online' 
       AND v_switch_status = 'on' 
       AND v_temperature > 14 THEN
        
        -- 检查是否存在未关闭记录
        IF NOT EXISTS (
            SELECT 1 FROM heater_usage 
            WHERE device_id = p_device_id 
            AND end_time IS NULL
        ) THEN
            INSERT INTO heater_usage (
                device_id,
                room_id,
                room_name,
                start_time,
                end_time,
                duration,
                date
            ) VALUES (
                p_device_id,
                v_room_id,
                v_room_name,
                p_event_time,
                NULL,
                0,
                DATE(p_event_time - INTERVAL IF(HOUR(p_event_time) < 12, 1, 0) DAY)
            );
        END IF;

    -- 关闭条件：任意条件不满足时更新结束时间
    ELSE
        UPDATE heater_usage
        SET end_time = p_event_time,
            duration = TIMESTAMPDIFF(SECOND, start_time, p_event_time),
            -- 同步更新房间信息（设备可能更换房间）
            room_id = v_room_id,
            room_name = v_room_name
        WHERE device_id = p_device_id
          AND end_time IS NULL
        ORDER BY start_time DESC
        LIMIT 1;
    END IF;

END
;;
delimiter ;

SET FOREIGN_KEY_CHECKS = 1;