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

-- ============================================================
-- 供水管网 T4 运行监测 测试数据脚本（KingbaseES）
-- 说明：
--   1. 所有测试数据均使用 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;
    SELECT count(*) INTO v_type_count
      FROM app_user.equipment_type
     WHERE id <> '1' AND type_id NOT IN ('water_supply', 'ws_flow', 'ws_pressure', 'ws_leak');
    IF v_type_count = 0 THEN
        RAISE EXCEPTION '缺少非供水设备类型，无法构造边界排除样本';
    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-FLOW-003', 'WS-TEST-FLOW-003', '测试流量监测点03', 'FLOW-TEST-003',
     (SELECT id FROM app_user.equipment_type WHERE type_id = 'ws_flow' AND parent_type_id = '1' LIMIT 1), 113.34000000, 23.18000000, '供水测试大道7号',
     '供水测试维护员', '13800000007', '供水测试公司', '供水T4流量分页测试设备',
     now() - interval '7 days', now(), '0', '供水测试区'),
    ('WS-TEST-EQ-FLOW-004', 'WS-TEST-FLOW-004', '测试流量监测点04', 'FLOW-TEST-004',
     (SELECT id FROM app_user.equipment_type WHERE type_id = 'ws_flow' AND parent_type_id = '1' LIMIT 1), 113.35000000, 23.19000000, '供水测试大道8号',
     '供水测试维护员', '13800000008', '供水测试公司', '供水T4流量分页测试设备',
     now() - interval '7 days', now(), '0', '供水测试区'),
    ('WS-TEST-EQ-FLOW-005', 'WS-TEST-FLOW-005', '测试流量监测点05', 'FLOW-TEST-005',
     (SELECT id FROM app_user.equipment_type WHERE type_id = 'ws_flow' AND parent_type_id = '1' LIMIT 1), 113.36000000, 23.20000000, '供水测试大道9号',
     '供水测试维护员', '13800000009', '供水测试公司', '供水T4流量分页测试设备',
     now() - interval '7 days', now(), '0', '供水测试区'),
    ('WS-TEST-EQ-FLOW-006', 'WS-TEST-FLOW-006', '测试流量监测点06', 'FLOW-TEST-006',
     (SELECT id FROM app_user.equipment_type WHERE type_id = 'ws_flow' AND parent_type_id = '1' LIMIT 1), 113.37000000, 23.21000000, '供水测试大道10号',
     '供水测试维护员', '13800000010', '供水测试公司', '供水T4流量分页测试设备',
     now() - interval '7 days', now(), '0', '供水测试区'),
    ('WS-TEST-EQ-FLOW-007', 'WS-TEST-FLOW-007', '测试流量监测点07', 'FLOW-TEST-007',
     (SELECT id FROM app_user.equipment_type WHERE type_id = 'ws_flow' AND parent_type_id = '1' LIMIT 1), 113.38000000, 23.22000000, '供水测试大道11号',
     '供水测试维护员', '13800000011', '供水测试公司', '供水T4流量分页测试设备',
     now() - interval '7 days', now(), '0', '供水测试区'),
    ('WS-TEST-EQ-FLOW-008', 'WS-TEST-FLOW-008', '测试流量监测点08', 'FLOW-TEST-008',
     (SELECT id FROM app_user.equipment_type WHERE type_id = 'ws_flow' AND parent_type_id = '1' LIMIT 1), 113.39000000, 23.23000000, '供水测试大道12号',
     '供水测试维护员', '13800000012', '供水测试公司', '供水T4流量分页测试设备',
     now() - interval '7 days', now(), '0', '供水测试区'),
    ('WS-TEST-EQ-FLOW-009', 'WS-TEST-FLOW-009', '测试流量监测点09', 'FLOW-TEST-009',
     (SELECT id FROM app_user.equipment_type WHERE type_id = 'ws_flow' AND parent_type_id = '1' LIMIT 1), 113.40000000, 23.24000000, '供水测试大道13号',
     '供水测试维护员', '13800000013', '供水测试公司', '供水T4流量分页测试设备',
     now() - interval '7 days', now(), '0', '供水测试区'),
    ('WS-TEST-EQ-FLOW-010', 'WS-TEST-FLOW-010', '测试流量监测点10', 'FLOW-TEST-010',
     (SELECT id FROM app_user.equipment_type WHERE type_id = 'ws_flow' AND parent_type_id = '1' LIMIT 1), 113.41000000, 23.25000000, '供水测试大道14号',
     '供水测试维护员', '13800000014', '供水测试公司', '供水T4流量分页测试设备',
     now() - interval '7 days', now(), '0', '供水测试区'),
    ('WS-TEST-EQ-FLOW-011', 'WS-TEST-FLOW-011', '测试流量监测点11', 'FLOW-TEST-011',
     (SELECT id FROM app_user.equipment_type WHERE type_id = 'ws_flow' AND parent_type_id = '1' LIMIT 1), 113.42000000, 23.26000000, '供水测试大道15号',
     '供水测试维护员', '13800000015', '供水测试公司', '供水T4流量分页测试设备',
     now() - interval '7 days', now(), '0', '供水测试区'),
    ('WS-TEST-EQ-FLOW-012', 'WS-TEST-FLOW-012', '测试流量监测点12', 'FLOW-TEST-012',
     (SELECT id FROM app_user.equipment_type WHERE type_id = 'ws_flow' AND parent_type_id = '1' LIMIT 1), 113.43000000, 23.27000000, '供水测试大道16号',
     '供水测试维护员', '13800000016', '供水测试公司', '供水T4流量分页测试设备',
     now() - interval '7 days', now(), '0', '供水测试区'),
    ('WS-TEST-EQ-PRESSURE-002', 'WS-TEST-PRESSURE-002', '测试压力监测点02', 'PRESSURE-TEST-002',
     (SELECT id FROM app_user.equipment_type WHERE type_id = 'ws_pressure' AND parent_type_id = '1' LIMIT 1), 113.44000000, 23.28000000, '供水测试大道17号',
     '供水测试维护员', '13800000017', '供水测试公司', '供水T4压力分页测试设备',
     now() - interval '7 days', now(), '0', '供水测试区'),
    ('WS-TEST-EQ-PRESSURE-003', 'WS-TEST-PRESSURE-003', '测试压力监测点03', 'PRESSURE-TEST-003',
     (SELECT id FROM app_user.equipment_type WHERE type_id = 'ws_pressure' AND parent_type_id = '1' LIMIT 1), 113.45000000, 23.29000000, '供水测试大道18号',
     '供水测试维护员', '13800000018', '供水测试公司', '供水T4压力分页测试设备',
     now() - interval '7 days', now(), '0', '供水测试区'),
    ('WS-TEST-EQ-LEAKAGE-002', 'WS-TEST-LEAKAGE-002', '测试漏失监测点02', 'LEAKAGE-TEST-002',
     (SELECT id FROM app_user.equipment_type WHERE type_id = 'ws_leak' AND parent_type_id = '1' LIMIT 1), 113.46000000, 23.30000000, '供水测试大道19号',
     '供水测试维护员', '13800000019', '供水测试公司', '供水T4漏失分页测试设备',
     now() - interval '7 days', now(), '0', '供水测试区'),
    ('WS-TEST-EQ-LEAKAGE-003', 'WS-TEST-LEAKAGE-003', '测试漏失监测点03', 'LEAKAGE-TEST-003',
     (SELECT id FROM app_user.equipment_type WHERE type_id = 'ws_leak' AND parent_type_id = '1' LIMIT 1), 113.47000000, 23.31000000, '供水测试大道20号',
     '供水测试维护员', '13800000020', '供水测试公司', '供水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-FLOW-004', 'WS-TEST-SYSTEM', 'WS-TEST-FLOW-003', '25', '1.00', now() - interval '1 hour'),
    ('WS-TEST-RADAR-FLOW-005', 'WS-TEST-SYSTEM', 'WS-TEST-FLOW-004', '35', '1.10', now() - interval '2 hours'),
    ('WS-TEST-RADAR-FLOW-006', 'WS-TEST-SYSTEM', 'WS-TEST-FLOW-005', '45', '1.20', now() - interval '3 hours'),
    ('WS-TEST-RADAR-FLOW-007', 'WS-TEST-SYSTEM', 'WS-TEST-FLOW-006', '55', '1.30', now() - interval '4 hours'),
    ('WS-TEST-RADAR-FLOW-008', 'WS-TEST-SYSTEM', 'WS-TEST-FLOW-007', '65', '1.40', now() - interval '5 hours'),
    ('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');
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-004', 'WS-TEST-SYSTEM', 'WS-TEST-FLOW-008', '18', '10008', '0.80', '23.80', now() - interval '1 hour'),
    ('WS-TEST-TELEMETRY-005', 'WS-TEST-SYSTEM', 'WS-TEST-FLOW-009', '28', '10009', '0.90', '23.90', now() - interval '2 hours'),
    ('WS-TEST-TELEMETRY-006', 'WS-TEST-SYSTEM', 'WS-TEST-FLOW-010', '38', '10010', '1.00', '24.10', now() - interval '3 hours'),
    ('WS-TEST-TELEMETRY-007', 'WS-TEST-SYSTEM', 'WS-TEST-FLOW-011', '48', '10011', '1.10', '24.20', now() - interval '4 hours'),
    ('WS-TEST-TELEMETRY-008', 'WS-TEST-SYSTEM', 'WS-TEST-FLOW-012', '58', '10012', '1.20', '24.30', now() - interval '5 hours');

-- ------------------------------------------------------------
-- 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');
INSERT INTO app_user.firefighting_pressure
    (id, central_station, telemetering_station, sending_time, observed_time,
     pressure_value, create_time)
VALUES
    ('WS-TEST-PRESSURE-004', 'WS-TEST-CENTER', 'WS-TEST-PRESSURE-002',
     now() - interval '2 hours', now() - interval '2 hours', 0.55, now() - interval '2 hours'),
    ('WS-TEST-PRESSURE-005', 'WS-TEST-CENTER', 'WS-TEST-PRESSURE-003',
     now() - interval '1 hour', now() - interval '1 hour', 0.75, now() - interval '1 hour');

-- ------------------------------------------------------------
-- 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');
INSERT INTO app_user.noise_info
    (encode, power, gain, rms, center_frequency, ts, create_time, update_time)
VALUES
    ('WS-TEST-LEAKAGE-002', 15.00, 2.50, 125, 820.00,
     to_char(now() - interval '1 hour', 'YYYY-MM-DD HH24:MI:SS'),
     now() - interval '1 hour', now() - interval '1 hour'),
    ('WS-TEST-LEAKAGE-003', 18.00, 3.00, 130, 840.00,
     to_char(now() - interval '2 hours', 'YYYY-MM-DD HH24:MI:SS'),
     now() - interval '2 hours', now() - interval '2 hours');

-- ------------------------------------------------------------
-- 快速核验：
--   规范 ws_flow 流量设备共13台（其中12台有 radar/telemetry 测量记录，1台无数据）；
--   流量分页（默认最新值）：12台有测量设备各返回1条；雷达流量点最新值120，遥测流量点最新值42；
--   流量分页 flowMin=20&flowMax=40：符合范围的雷达/遥测点共7条（含回退到雷达30、遥测36）；
--   压力分页 pressureMin=0.30&pressureMax=0.60：0.42、0.55，共2条；
--   漏失分页 powerMin=8&powerMax=15：12、15，共2条；
--   WS-TEST-FLOW-EMPTY 无测量数据，不应出现在任何监测页。
--   WS-TEST-OTHER-FLOW 如数据库存在其他系统类型则带入该类型；不在供水规范子类型内，不应出现在监测页。
-- ============================================================