iot/yudao-module-iot/yudao-module-iot-biz/sql/cj/iot_cj_tdengine_prod.sql

-- ============================================================
-- TDengine 生产环境初始化脚本
-- 数据库：ruoyi_vue_pro
-- 导出时间：2026-05-18
-- TDengine 版本：3.3.6.13
-- ============================================================

-- 1. 创建数据库
CREATE DATABASE IF NOT EXISTS `ruoyi_vue_pro`
    BUFFER 256
    CACHESIZE 1
    CACHEMODEL 'none'
    COMP 2
    DURATION 10d
    WAL_FSYNC_PERIOD 3000
    MAXROWS 4096
    MINROWS 100
    STT_TRIGGER 2
    KEEP 365d,365d,365d
    PAGES 256
    PAGESIZE 4
    PRECISION 'ms'
    REPLICA 1
    WAL_LEVEL 1
    VGROUPS 2
    SINGLE_STABLE 0
    TABLE_PREFIX 0
    TABLE_SUFFIX 0
    TSDB_PAGESIZE 4
    WAL_RETENTION_PERIOD 3600
    WAL_RETENTION_SIZE 0;

USE `ruoyi_vue_pro`;

-- ============================================================
-- 2. 超级表：iot_cj_meter_data（集抄水表数据 - 主用）
-- ============================================================
CREATE STABLE IF NOT EXISTS `ruoyi_vue_pro`.`iot_cj_meter_data` (
    `ts`              TIMESTAMP ENCODE 'delta-i' COMPRESS 'lz4' LEVEL 'medium',
    `cumulative_flow` DOUBLE   ENCODE 'delta-d' COMPRESS 'lz4' LEVEL 'medium',
    `forward_flow`    DOUBLE   ENCODE 'delta-d' COMPRESS 'lz4' LEVEL 'medium',
    `reverse_flow`    DOUBLE   ENCODE 'delta-d' COMPRESS 'lz4' LEVEL 'medium',
    `instant_flow`    DOUBLE   ENCODE 'delta-d' COMPRESS 'lz4' LEVEL 'medium',
    `max_flow`        DOUBLE   ENCODE 'delta-d' COMPRESS 'lz4' LEVEL 'medium',
    `min_flow`        DOUBLE   ENCODE 'delta-d' COMPRESS 'lz4' LEVEL 'medium',
    `pressure`        DOUBLE   ENCODE 'delta-d' COMPRESS 'lz4' LEVEL 'medium',
    `data_type`       TINYINT  ENCODE 'simple8b' COMPRESS 'zlib' LEVEL 'medium',
    `signal_quality`  TINYINT  ENCODE 'simple8b' COMPRESS 'zlib' LEVEL 'medium',
    `voltage`         DOUBLE   ENCODE 'delta-d' COMPRESS 'lz4' LEVEL 'medium',
    `valve_opening`   TINYINT  ENCODE 'simple8b' COMPRESS 'zlib' LEVEL 'medium',
    `status_byte1`    TINYINT  ENCODE 'simple8b' COMPRESS 'zlib' LEVEL 'medium',
    `status_byte2`    TINYINT  ENCODE 'simple8b' COMPRESS 'zlib' LEVEL 'medium',
    `temperature`     DOUBLE   ENCODE 'delta-d' COMPRESS 'lz4' LEVEL 'medium',
    `imei`            VARCHAR(20) ENCODE 'disabled' COMPRESS 'zstd' LEVEL 'medium'
) TAGS (
    `meter_address`   VARCHAR(14),
    `meter_id`        BIGINT
) SMA(`ts`,`cumulative_flow`,`forward_flow`,`reverse_flow`,`instant_flow`,`max_flow`,`min_flow`,`pressure`,`data_type`);

-- ============================================================
-- 3. 超级表：cj_meter_data（集抄水表数据 - 旧版兼容）
-- ============================================================
CREATE STABLE IF NOT EXISTS `ruoyi_vue_pro`.`cj_meter_data` (
    `ts`               TIMESTAMP ENCODE 'delta-i' COMPRESS 'lz4' LEVEL 'medium',
    `cumulative_flow`  DOUBLE    ENCODE 'delta-d' COMPRESS 'lz4' LEVEL 'medium',
    `forward_flow`     DOUBLE    ENCODE 'delta-d' COMPRESS 'lz4' LEVEL 'medium',
    `reverse_flow`     DOUBLE    ENCODE 'delta-d' COMPRESS 'lz4' LEVEL 'medium',
    `instant_flow`     DOUBLE    ENCODE 'delta-d' COMPRESS 'lz4' LEVEL 'medium',
    `max_flow`         DOUBLE    ENCODE 'delta-d' COMPRESS 'lz4' LEVEL 'medium',
    `min_flow`         DOUBLE    ENCODE 'delta-d' COMPRESS 'lz4' LEVEL 'medium',
    `pressure`         DOUBLE    ENCODE 'delta-d' COMPRESS 'lz4' LEVEL 'medium',
    `battery_voltage`  DOUBLE    ENCODE 'delta-d' COMPRESS 'lz4' LEVEL 'medium',
    `signal_strength`  INT       ENCODE 'simple8b' COMPRESS 'lz4' LEVEL 'medium',
    `valve_status`     INT       ENCODE 'simple8b' COMPRESS 'lz4' LEVEL 'medium',
    `raw_hex`          NCHAR(512) ENCODE 'disabled' COMPRESS 'zstd' LEVEL 'medium'
) TAGS (
    `meter_id`         BIGINT
);

-- ============================================================
-- 4. 超级表：cj_concentrator_status（集中器状态）
-- ============================================================
CREATE STABLE IF NOT EXISTS `ruoyi_vue_pro`.`cj_concentrator_status` (
    `ts`               TIMESTAMP ENCODE 'delta-i' COMPRESS 'lz4' LEVEL 'medium',
    `online`           BOOL      ENCODE 'bit-packing' COMPRESS 'zstd' LEVEL 'medium',
    `meter_count`      INT       ENCODE 'simple8b' COMPRESS 'lz4' LEVEL 'medium',
    `success_count`    INT       ENCODE 'simple8b' COMPRESS 'lz4' LEVEL 'medium',
    `fail_count`       INT       ENCODE 'simple8b' COMPRESS 'lz4' LEVEL 'medium',
    `signal_strength`  INT       ENCODE 'simple8b' COMPRESS 'lz4' LEVEL 'medium'
) TAGS (
    `concentrator_id`  BIGINT,
    `address`          NCHAR(32)
);

-- ============================================================
-- 5. 超级表：device_message（IoT 设备消息）
-- ============================================================
CREATE STABLE IF NOT EXISTS `ruoyi_vue_pro`.`device_message` (
    `ts`           TIMESTAMP    ENCODE 'delta-i' COMPRESS 'lz4' LEVEL 'medium',
    `id`           NCHAR(50)    ENCODE 'disabled' COMPRESS 'zstd' LEVEL 'medium',
    `report_time`  TIMESTAMP    ENCODE 'delta-i' COMPRESS 'lz4' LEVEL 'medium',
    `tenant_id`    BIGINT       ENCODE 'simple8b' COMPRESS 'lz4' LEVEL 'medium',
    `server_id`    NCHAR(50)    ENCODE 'disabled' COMPRESS 'zstd' LEVEL 'medium',
    `upstream`     BOOL         ENCODE 'bit-packing' COMPRESS 'zstd' LEVEL 'medium',
    `reply`        BOOL         ENCODE 'bit-packing' COMPRESS 'zstd' LEVEL 'medium',
    `identifier`   NCHAR(100)   ENCODE 'disabled' COMPRESS 'zstd' LEVEL 'medium',
    `request_id`   NCHAR(50)    ENCODE 'disabled' COMPRESS 'zstd' LEVEL 'medium',
    `method`       NCHAR(100)   ENCODE 'disabled' COMPRESS 'zstd' LEVEL 'medium',
    `params`       NCHAR(2048)  ENCODE 'disabled' COMPRESS 'zstd' LEVEL 'medium',
    `data`         NCHAR(2048)  ENCODE 'disabled' COMPRESS 'zstd' LEVEL 'medium',
    `code`         INT          ENCODE 'simple8b' COMPRESS 'lz4' LEVEL 'medium',
    `msg`          NCHAR(256)   ENCODE 'disabled' COMPRESS 'zstd' LEVEL 'medium'
) TAGS (
    `device_id`    BIGINT
);