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

-- ============================================================
-- 供水管网-监测报警模块 测试数据脚本（人大金仓 KingbaseES / PostgreSQL 兼容）
-- 说明：
--   1. 测试数据使用 TEST- / test- 前缀，可安全重复执行（先删除再插入）。
--   2. 若设备类别中已存在顶级类型"供水"，则复用；否则自动创建测试顶级类型。
--   3. 清理请执行本文件末尾的清理脚本，或单独执行 water_supply_alarm_test_cleanup.sql。
--   4. 部署顺序：须先执行 migration_alarm_case_work_order_id.sql（alarm_data/case_info/
--      hazard_hidden_account/work_order_log 的 order_id→work_order_id 改名 + alarm_data 删 alarm_time），
--      再执行本脚本；否则 work_order_id 列不存在会报错。
-- ============================================================

-- ------------------------------------------------------------
-- 0. 清理历史测试数据（保证可重复执行）
-- ------------------------------------------------------------
DELETE FROM app_user.alarm_audit WHERE alarm_id LIKE 'test-alarm-%';
DELETE FROM app_user.work_order_log WHERE work_order_id IN
    (SELECT order_id FROM app_user.work_order WHERE order_no = 'TEST-WO-001' OR order_id IN (SELECT work_order_id FROM app_user.alarm_data WHERE id LIKE 'test-alarm-%'));
DELETE FROM app_user.work_order WHERE order_no = 'TEST-WO-001' OR order_id IN (SELECT work_order_id FROM app_user.alarm_data WHERE id LIKE 'test-alarm-%');
DELETE FROM app_user.alarm_data WHERE id LIKE 'test-alarm-%';
DELETE FROM app_user.warning_threshold WHERE id LIKE 'test-thr-%' OR device_code LIKE 'TEST-%';
DELETE FROM app_user.equipment_base WHERE equipment_id LIKE 'TEST-EQ-%';
DELETE FROM app_user.equipment_type WHERE id LIKE 'TEST-WATER-%';

-- ------------------------------------------------------------
-- 1. 设备类别与设备基础信息
-- ------------------------------------------------------------
DO $$
DECLARE
    v_top_id       varchar(64);
    v_level_id     varchar(64);
    v_pressure_id  varchar(64);
    v_flow_id      varchar(64);
BEGIN
    -- 顶级类型"供水"（parent_id = 0），不存在则创建
    SELECT id INTO v_top_id FROM app_user.equipment_type
     WHERE type_name = '供水' AND parent_id = 0 LIMIT 1;
    -- 测试类型主键取 9100 段，避免与 EquipmentTypeEnum 的真实类型冲突
    IF v_top_id IS NULL THEN
        INSERT INTO app_user.equipment_type (id, type_name, parent_id, remark)
        VALUES (9100, '供水', 0, '供水监测报警测试-顶级类型(可清理)')
        RETURNING id INTO v_top_id;
    END IF;

    -- 子类型：水位 / 压力 / 流量
    SELECT id INTO v_level_id FROM app_user.equipment_type WHERE id = 9101 LIMIT 1;
    IF v_level_id IS NULL THEN
        INSERT INTO app_user.equipment_type (id, type_name, parent_id, remark)
        VALUES (9101, '水位监测(测试)', v_top_id, '供水监测报警测试子类型')
        RETURNING id INTO v_level_id;
    END IF;

    SELECT id INTO v_pressure_id FROM app_user.equipment_type WHERE id = 9102 LIMIT 1;
    IF v_pressure_id IS NULL THEN
        INSERT INTO app_user.equipment_type (id, type_name, parent_id, remark)
        VALUES (9102, '压力监测(测试)', v_top_id, '供水监测报警测试子类型')
        RETURNING id INTO v_pressure_id;
    END IF;

    SELECT id INTO v_flow_id FROM app_user.equipment_type WHERE id = 9103 LIMIT 1;
    IF v_flow_id IS NULL THEN
        INSERT INTO app_user.equipment_type (id, type_name, parent_id, remark)
        VALUES (9103, '流量监测(测试)', v_top_id, '供水监测报警测试子类型')
        RETURNING id INTO v_flow_id;
    END IF;

    -- 4 台测试设备（均带经纬度，供一张图点位使用）
    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
        ('TEST-EQ-WL-001', 'TEST-WL-001', '测试水位监测点1', 'WL-TEST-1', v_level_id,
         113.28000000, 23.12000000, '测试大道1号', '测试维护员', '13800000001', '测试供水公司',
         '供水监测报警测试数据', now(), now(), '0', '测试区'),
        ('TEST-EQ-WL-002', 'TEST-WL-002', '测试水位监测点2', 'WL-TEST-2', v_level_id,
         113.29000000, 23.13000000, '测试大道2号', '测试维护员', '13800000002', '测试供水公司',
         '供水监测报警测试数据', now(), now(), '0', '测试区'),
        ('TEST-EQ-PR-001', 'TEST-PR-001', '测试压力监测点1', 'PR-TEST-1', v_pressure_id,
         113.30000000, 23.14000000, '测试大道3号', '测试维护员', '13800000003', '测试供水公司',
         '供水监测报警测试数据', now(), now(), '0', '测试区'),
        ('TEST-EQ-FL-001', 'TEST-FL-001', '测试流量监测点1', 'FL-TEST-1', v_flow_id,
         113.31000000, 23.15000000, '测试大道4号', '测试维护员', '13800000004', '测试供水公司',
         '供水监测报警测试数据', now(), now(), '0', '测试区');
END $$;

-- ------------------------------------------------------------
-- 2. 报警数据（alarm_data）
--    预期统计：实时报警3个设备 / 今日报警6条 / 平均响应时间由alarm_log处理日志统计 / 设备异常率75%
--    注意：alarm_time 列已删除，统一使用 create_time。
-- ------------------------------------------------------------
INSERT INTO app_user.alarm_data
    (id, device_code, warning_type, warning_code, actual_value, min_value, max_value, alarm_status, alarm_level, deviation_ratio, remark, create_time, update_time)
VALUES
    ('test-alarm-001', 'TEST-WL-001', '水位预警', 'WARN-WATER-LEVEL-TEST', 0.20, 0.50, 3.00, 0, 1, 40.00, '供水监测报警测试数据', now() - interval '180 minutes', now() - interval '180 minutes'),
    ('test-alarm-002', 'TEST-WL-001', '水位预警', 'WARN-WATER-LEVEL-TEST', 0.30, 0.50, 3.00, 0, 2, 35.00, '供水监测报警测试数据', now() - interval '150 minutes', now() - interval '150 minutes'),
    ('test-alarm-003', 'TEST-WL-002', '水位预警', 'WARN-WATER-LEVEL-TEST', 3.80, 0.50, 3.00, 1, 3, 25.00, '供水监测报警测试数据', now() - interval '120 minutes', now() - interval '85 minutes'),
    ('test-alarm-004', 'TEST-PR-001', '压力预警', 'WARN-PRESSURE-TEST', 0.95, 0.40, 0.80, 0, 1, 30.00, '供水监测报警测试数据', now() - interval '100 minutes', now() - interval '100 minutes'),
    ('test-alarm-005', 'TEST-PR-001', '压力预警', 'WARN-PRESSURE-TEST', 0.90, 0.40, 0.80, 1, 2, 28.00, '供水监测报警测试数据', now() - interval '90 minutes', now() - interval '60 minutes'),
    ('test-alarm-006', 'TEST-FL-001', '流量预警', 'WARN-FLOW-TEST', 120.00, 10.00, 100.00, 1, 4, 20.00, '供水监测报警测试数据', now() - interval '1 day' - interval '60 minutes', now() - interval '1 day'),
    ('test-alarm-007', 'TEST-FL-001', '流量预警', 'WARN-FLOW-TEST', 115.00, 10.00, 100.00, 0, 3, 15.00, '供水监测报警测试数据', now() - interval '1 day' - interval '30 minutes', now() - interval '1 day' - interval '30 minutes'),
    ('test-alarm-008', 'TEST-WL-002', '水位预警', 'WARN-WATER-LEVEL-TEST', 3.50, 0.50, 3.00, 1, 2, 18.00, '供水监测报警测试数据', now() - interval '300 minutes', now() - interval '290 minutes');

-- ------------------------------------------------------------
-- 3. 阈值数据（warning_threshold，含分时阈值）
-- ------------------------------------------------------------
INSERT INTO app_user.warning_threshold
    (id, device_code, warning_code, min_value, max_value,
     period_start, period_end, create_time, update_time)
VALUES
    ('test-thr-001', 'TEST-WL-001', 'WARN-WATER-LEVEL-TEST', 0.50, 3.00,
     NULL, NULL, now(), now()),
    ('test-thr-002', 'TEST-WL-002', 'WARN-WATER-LEVEL-TEST', 0.60, 3.20,
     NULL, NULL, now(), now()),
    ('test-thr-003', 'TEST-PR-001', 'WARN-PRESSURE-TEST', 0.40, 0.80,
     '08:00', '20:00', now(), now()),
    ('test-thr-004', 'TEST-FL-001', 'WARN-FLOW-TEST', 10.00, 100.00,
     NULL, NULL, now(), now());

-- ------------------------------------------------------------
-- 4. 工单与工单日志（关联 test-alarm-003，用于全流程跟踪测试）
-- ------------------------------------------------------------
INSERT INTO app_user.work_order
    (order_no, device_id,
     order_level, order_desc, dept_id, dispatch_user, dispatch_time,
     receive_user, order_status, delayed_reason, delayed_deadline, plan_finish_time,
     real_finish_time, repair_result, verify_user, verify_time, create_time, update_time,
     maintenance_before_img)
VALUES
    ('TEST-WO-001', 'TEST-EQ-WL-002',
     2, '供水监测报警测试工单-水位超限', NULL, NULL, NULL,
     NULL, 5, NULL, NULL, NULL,
     NULL, '现场已处理', NULL, NULL, now(), now(),
     NULL);

UPDATE app_user.alarm_data
   SET work_order_id = (SELECT order_id FROM app_user.work_order WHERE order_no = 'TEST-WO-001')
 WHERE id = 'test-alarm-003';

INSERT INTO app_user.work_order_log
    (work_order_id, oper_type, oper_desc, oper_user_id, oper_user_name, oper_time)
SELECT order_id, 5, '供水监测报警测试-提交验收', 1, '测试用户', now()
  FROM app_user.work_order WHERE order_no = 'TEST-WO-001';

-- ------------------------------------------------------------
-- 5. 报警审核记录（关联 test-alarm-002，用于审核记录/全流程测试）
-- ------------------------------------------------------------
INSERT INTO app_user.alarm_audit
    (alarm_id, audit_action, audit_result, audit_opinion, attach_url,
     audit_user_id, audit_user_name, audit_time, create_time, update_time)
VALUES
    ('test-alarm-002', 1, 1, '测试审核：现场核实报警属实', NULL,
     1, '测试用户', now(), now(), now());

-- ============================================================
-- 清理脚本（如需清理测试数据，执行以下语句）
-- ============================================================
-- DELETE FROM app_user.alarm_audit WHERE alarm_id LIKE 'test-alarm-%';
-- DELETE FROM app_user.work_order_log WHERE work_order_id IN
--     (SELECT order_id FROM app_user.work_order WHERE order_no = 'TEST-WO-001' OR order_id IN (SELECT work_order_id FROM app_user.alarm_data WHERE id LIKE 'test-alarm-%'));
-- DELETE FROM app_user.work_order WHERE order_no = 'TEST-WO-001' OR order_id IN (SELECT work_order_id FROM app_user.alarm_data WHERE id LIKE 'test-alarm-%');
-- DELETE FROM app_user.alarm_data WHERE id LIKE 'test-alarm-%';
-- DELETE FROM app_user.warning_threshold WHERE id LIKE 'test-thr-%' OR device_code LIKE 'TEST-%';
-- DELETE FROM app_user.equipment_base WHERE equipment_id LIKE 'TEST-EQ-%';
-- DELETE FROM app_user.equipment_type WHERE id LIKE 'TEST-WATER-%';