pipe-ner-server/pipe-network-service/sql/water_supply_monitoring_test_data.sql

-- ============================================================
-- 供水管网 T4 运行监测 测试数据脚本（KingbaseES）
-- 说明：
--   1. 所有测试数据均使用 WS-TEST- / ws_test_ 前缀，可重复执行。
--   2. 本脚本只初始化测试行，不创建监测业务表；执行前请确认以下表已存在：
--      app_user.equipment_type、app_user.equipment_base、app_user.radar_data、
--      app_user.telemetry_data、app_user.firefighting_pressure、app_user.noise_info
--   3. 覆盖场景：雷达流量、遥测流量、压力、漏失、无数据设备、
--      非供水同名设备排除、数值范围过滤、时间范围过滤与近24小时之外的旧趋势点。
-- ============================================================

-- ------------------------------------------------------------
-- 0. 清理历史测试数据（先清测量值，再清设备与类型）
-- ------------------------------------------------------------
DELETE FROM app_user.radar_data WHERE device_code LIKE 'WS-TEST-%';
DELETE FROM app_user.telemetry_data WHERE device_code LIKE 'WS-TEST-%';
DELETE FROM app_user.firefighting_pressure WHERE telemetering_station LIKE 'WS-TEST-%';
DELETE FROM app_user.noise_info WHERE encode LIKE 'WS-TEST-%';
DELETE FROM app_user.equipment_base WHERE equipment_id LIKE 'WS-TEST-EQ-%';
DELETE FROM app_user.equipment_type WHERE id LIKE 'WS-TEST-TYPE-%';

-- ------------------------------------------------------------
-- 1. 设备类型与监测设备（供水测试类型挂在顶级类型“供水”之下）
-- ------------------------------------------------------------
DO $$
DECLARE
    v_water_top_id varchar(64);
BEGIN
    SELECT id INTO v_water_top_id
      FROM app_user.equipment_type
     WHERE type_name = '供水' AND parent_type_id = '0'
     LIMIT 1;

    IF v_water_top_id IS NULL THEN
        INSERT INTO app_user.equipment_type
            (id, type_id, type_name, parent_type_id, type_remark)
        VALUES
            ('WS-TEST-TYPE-WATER', 'ws_test_water_top', '供水', '0', '供水T4运行监测测试顶级类型');
        v_water_top_id := 'WS-TEST-TYPE-WATER';
    END IF;

    INSERT INTO app_user.equipment_type
        (id, type_id, type_name, parent_type_id, type_remark)
    VALUES
        ('WS-TEST-TYPE-FLOW', 'ws_test_flow_monitoring', '流量监测(测试)', v_water_top_id, '供水T4流量监测测试类型'),
        ('WS-TEST-TYPE-PRESSURE', 'ws_test_pressure_monitoring', '压力监测(测试)', v_water_top_id, '供水T4压力监测测试类型'),
        ('WS-TEST-TYPE-LEAKAGE', 'ws_test_leakage_monitoring', '漏失监测(测试)', v_water_top_id, '供水T4漏失监测测试类型'),
        ('WS-TEST-TYPE-OTHER', 'ws_test_other_top', '其他系统(测试)', '0', '供水T4边界测试顶级类型'),
        ('WS-TEST-TYPE-OTHER-FLOW', 'ws_test_other_flow', '流量监测(测试)', 'WS-TEST-TYPE-OTHER', '供水T4非供水边界测试类型');
END $$;

INSERT INTO app_user.equipment_base
    (equipment_id, equipment_code, equipment_name, equipment_model, equipment_type_id,
     longitude, latitude, equipment_location, maintainer, maintainer_phone,
     ownership_unit, remark, create_time, update_time, del_flag, district)
VALUES
    ('WS-TEST-EQ-FLOW-RADAR', 'WS-TEST-FLOW-RADAR', '测试雷达流量监测点', 'RADAR-FLOW-TEST',
     'WS-TEST-TYPE-FLOW', 113.28000000, 23.12000000, '供水测试大道1号',
     '供水测试维护员', '13800000001', '供水测试公司', '供水T4运行监测测试数据',
     now() - interval '7 days', now(), '0', '供水测试区'),
    ('WS-TEST-EQ-FLOW-TELEMETRY', 'WS-TEST-FLOW-TELEMETRY', '测试遥测终端流量监测点', 'TELEMETRY-FLOW-TEST',
     'WS-TEST-TYPE-FLOW', 113.29000000, 23.13000000, '供水测试大道2号',
     '供水测试维护员', '13800000002', '供水测试公司', '供水T4运行监测测试数据',
     now() - interval '7 days', now(), '0', '供水测试区'),
    ('WS-TEST-EQ-PRESSURE', 'WS-TEST-PRESSURE', '测试压力监测点', 'PRESSURE-TEST',
     'WS-TEST-TYPE-PRESSURE', 113.30000000, 23.14000000, '供水测试大道3号',
     '供水测试维护员', '13800000003', '供水测试公司', '供水T4运行监测测试数据',
     now() - interval '7 days', now(), '0', '供水测试区'),
    ('WS-TEST-EQ-LEAKAGE', 'WS-TEST-LEAKAGE', '测试漏失监测点', 'LEAKAGE-TEST',
     'WS-TEST-TYPE-LEAKAGE', 113.31000000, 23.15000000, '供水测试大道4号',
     '供水测试维护员', '13800000004', '供水测试公司', '供水T4运行监测测试数据',
     now() - interval '7 days', now(), '0', '供水测试区'),
    ('WS-TEST-EQ-FLOW-EMPTY', 'WS-TEST-FLOW-EMPTY', '测试无测量流量监测点', 'FLOW-EMPTY-TEST',
     'WS-TEST-TYPE-FLOW', 113.32000000, 23.16000000, '供水测试大道5号',
     '供水测试维护员', '13800000005', '供水测试公司', '供水T4运行监测无数据测试设备',
     now() - interval '7 days', now(), '0', '供水测试区'),
    ('WS-TEST-EQ-OTHER-FLOW', 'WS-TEST-OTHER-FLOW', '测试非供水流量监测点', 'OTHER-FLOW-TEST',
     'WS-TEST-TYPE-OTHER-FLOW', 113.33000000, 23.17000000, '其他系统测试大道6号',
     '供水测试维护员', '13800000006', '供水测试公司', '供水T4非供水设备边界测试数据',
     now() - interval '7 days', now(), '0', '供水测试区');

-- ------------------------------------------------------------
-- 2. 雷达流量计数据
--    数值范围 20-40 时：最新 120 不符合，应回退到 30。
--    5 天前数据用于验证趋势时间范围过滤。
-- ------------------------------------------------------------
INSERT INTO app_user.radar_data
    (id, system_identifier, device_code, meter1_instant_flow, flow_speed, create_time)
VALUES
    ('WS-TEST-RADAR-FLOW-001', 'WS-TEST-SYSTEM', 'WS-TEST-FLOW-RADAR', '30', '1.20',
     now() - interval '3 hours'),
    ('WS-TEST-RADAR-FLOW-002', 'WS-TEST-SYSTEM', 'WS-TEST-FLOW-RADAR', '120', '3.50',
     now() - interval '1 hour'),
    ('WS-TEST-RADAR-FLOW-003', 'WS-TEST-SYSTEM', 'WS-TEST-FLOW-RADAR', '18', '0.80',
     now() - interval '5 days'),
    ('WS-TEST-RADAR-OTHER-FLOW-001', 'WS-TEST-SYSTEM', 'WS-TEST-OTHER-FLOW', '66', '2.00',
     now() - interval '1 hour');

-- ------------------------------------------------------------
-- 3. 遥测终端流量数据（device_code 与 equipment_base.equipment_code 关联）
-- ------------------------------------------------------------
INSERT INTO app_user.telemetry_data
    (id, system_identifier, device_code, meter1_instant_flow, meter1_positive_total,
     flow_speed, temperature, create_time)
VALUES
    ('WS-TEST-TELEMETRY-001', 'WS-TEST-SYSTEM', 'WS-TEST-FLOW-TELEMETRY', '42', '12345',
     '1.80', '24.50', now() - interval '2 hours'),
    ('WS-TEST-TELEMETRY-002', 'WS-TEST-SYSTEM', 'WS-TEST-FLOW-TELEMETRY', '36', '12300',
     '1.50', '24.00', now() - interval '4 hours'),
    ('WS-TEST-TELEMETRY-003', 'WS-TEST-SYSTEM', 'WS-TEST-FLOW-TELEMETRY', '20', '12200',
     '0.90', '23.50', now() - interval '5 days');

-- ------------------------------------------------------------
-- 4. 消防压力数据（telemetering_station 与 equipment_base.equipment_code 关联）
-- ------------------------------------------------------------
INSERT INTO app_user.firefighting_pressure
    (id, central_station, telemetering_station, sending_time, observed_time,
     pressure_value, create_time)
VALUES
    ('WS-TEST-PRESSURE-001', 'WS-TEST-CENTER', 'WS-TEST-PRESSURE',
     now() - interval '3 hours', now() - interval '3 hours', 0.42, now() - interval '3 hours'),
    ('WS-TEST-PRESSURE-002', 'WS-TEST-CENTER', 'WS-TEST-PRESSURE',
     now() - interval '1 hour', now() - interval '1 hour', 0.95, now() - interval '1 hour'),
    ('WS-TEST-PRESSURE-003', 'WS-TEST-CENTER', 'WS-TEST-PRESSURE',
     now() - interval '5 days', now() - interval '5 days', 0.20, now() - interval '5 days');

-- ------------------------------------------------------------
-- 5. 漏失噪声数据（encode 与 equipment_base.equipment_code 关联）
-- ------------------------------------------------------------
INSERT INTO app_user.noise_info
    (encode, power, gain, rms, center_frequency, ts, create_time, update_time)
VALUES
    ('WS-TEST-LEAKAGE', 12.00, 2.00, 120, 800.00,
     to_char(now() - interval '2 hours', 'YYYY-MM-DD HH24:MI:SS'),
     now() - interval '2 hours', now() - interval '2 hours'),
    ('WS-TEST-LEAKAGE', 9.00, 2.00, 100, 780.00,
     to_char(now() - interval '4 hours', 'YYYY-MM-DD HH24:MI:SS'),
     now() - interval '4 hours', now() - interval '4 hours'),
    ('WS-TEST-LEAKAGE', 5.00, 1.00, 80, 700.00,
     to_char(now() - interval '5 days', 'YYYY-MM-DD HH24:MI:SS'),
     now() - interval '5 days', now() - interval '5 days');

-- ------------------------------------------------------------
-- 快速核验：
--   流量分页（默认最新值）：雷达120、遥测42，共2条；
--   流量分页 flowMin=20&flowMax=40：雷达30、遥测36，共2条；
--   压力分页 pressureMin=0.30&pressureMax=0.60：0.42，共1条；
--   漏失分页 powerMin=8&powerMax=15：12，共1条；
--   WS-TEST-FLOW-EMPTY 无测量数据，不应出现在任何监测页。
--   WS-TEST-OTHER-FLOW 有同名流量类型和测量数据，但不在供水类型树内，不应出现在任何监测页。
-- ============================================================