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-%';

-- ------------------------------------------------------------
-- 1. 复用既有设备类型：id=1/type_id=water_supply，监测子类型严格挂在 parent_type_id=1
-- ------------------------------------------------------------
DO $$
DECLARE
    v_type_count integer;
BEGIN
    SELECT count(*) INTO v_type_count
      FROM app_user.equipment_type
     WHERE id = '1' AND type_id = 'water_supply' AND parent_type_id = '0';
    IF v_type_count <> 1 THEN
        RAISE EXCEPTION '缺少规范供水顶级设备类型：id=1,type_id=water_supply,parent_type_id=0';
    END IF;
    SELECT count(*) INTO v_type_count
      FROM app_user.equipment_type
     WHERE type_id IN ('ws_flow', 'ws_pressure', 'ws_leak')
       AND parent_type_id = '1';
    IF v_type_count <> 3 THEN
        RAISE EXCEPTION '缺少规范供水监测子类型：ws_flow/ws_pressure/ws_leak 必须 parent_type_id=1';
    END IF;
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',
     (SELECT id FROM app_user.equipment_type WHERE type_id = 'ws_flow' AND parent_type_id = '1' LIMIT 1), 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',
     (SELECT id FROM app_user.equipment_type WHERE type_id = 'ws_flow' AND parent_type_id = '1' LIMIT 1), 113.29000000, 23.13000000, '供水测试大道2号',
     '供水测试维护员', '13800000002', '供水测试公司', '供水T4运行监测测试数据',
     now() - interval '7 days', now(), '0', '供水测试区'),
    ('WS-TEST-EQ-PRESSURE', 'WS-TEST-PRESSURE', '测试压力监测点', 'PRESSURE-TEST',
     (SELECT id FROM app_user.equipment_type WHERE type_id = 'ws_pressure' AND parent_type_id = '1' LIMIT 1), 113.30000000, 23.14000000, '供水测试大道3号',
     '供水测试维护员', '13800000003', '供水测试公司', '供水T4运行监测测试数据',
     now() - interval '7 days', now(), '0', '供水测试区'),
    ('WS-TEST-EQ-LEAKAGE', 'WS-TEST-LEAKAGE', '测试漏失监测点', 'LEAKAGE-TEST',
     (SELECT id FROM app_user.equipment_type WHERE type_id = 'ws_leak' AND parent_type_id = '1' LIMIT 1), 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',
     (SELECT id FROM app_user.equipment_type WHERE type_id = 'ws_flow' AND parent_type_id = '1' LIMIT 1), 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',
     (SELECT id FROM app_user.equipment_type
       WHERE id <> '1' AND type_id NOT IN ('water_supply', 'ws_flow', 'ws_pressure', 'ws_leak')
       LIMIT 1), 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 如数据库存在其他系统类型则带入该类型；不在供水规范子类型内，不应出现在监测页。
-- ============================================================