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

-- ============================================================
-- 供水报警管理只读台账与态势统计（Gogs #45 / T02）验收测试数据
-- 数据库：人大金仓 KingbaseES（PostgreSQL 语法兼容）
--
-- 说明：
--   1. 全部测试数据使用 T45- / t45- 前缀，与既有 TEST- 种子互不干扰。
--   2. 先删后插，可重复执行；清理语句见文件末尾。
--   3. 设备编码遵循项目命名规范：区域(CN城南/CB城北/TC太常)-类型(GS供水)-YYYYMMDD+4位流水号。
--   4. 设备类型不新增：一律引用库中现有 equipment_type
--      （type_id = pressure_gauge / ultrasonic_flowmeter / leak_monitor 且 parent_type_id = '1'），
--      equipment_base.equipment_type_id 写入该类型记录的 id；找不到类型时脚本直接报错，不做兜底新增。
--
-- 本批数据刻意覆盖以下验收点：
--   · 报警趋势四条等级序列：近 7 天内每天至少一个等级有值，且四级都出现过
--   · 三种报警展示状态：待审核 / 已转工单 / 已解除
--   · 设备报警状态两种取值：报警中(1) 与 正常(0)，另有 1 台离线设备
--   · 关联工单：未办结工单→已转工单；已结案/已取消工单→不得算作已转工单
--   · 无 equipment_status 记录时的设备报警状态回退分支
--   · 报警值为空、阈值未配置、阈值下限为 0 三种边界显示
--   · 窗口外（8/12 天前）的报警，用于验证时间范围查询与趋势补零
--
-- 台账预期口径（15 条报警，执行后应与页面一致）：
--   报警总数 15；待审核 9；已转工单 2；已解除 4；报警中设备 3（城南压力、太常流量、城北流量-兜底）
--   趋势（近 7 天，按等级）：
--     今日       一级1 二级2 三级1 四级1
--     昨日       一级1 二级0 三级1 四级0
--     前2日      一级1 二级1 三级0 四级0
--     前3日      一级1 二级0 三级0 四级0
--     前4日      一级0 二级0 三级0 四级1
--     前5日      一级0 二级0 三级1 四级0
--     前6日      一级0 二级0 三级0 四级1
--   报警分类占比：压力预警6 / 流量预警5 / 漏失预警4
--   设备分类占比：压力监测6 / 流量监测5 / 漏失监测4
--   报警等级占比：一级5 / 二级4 / 三级3 / 四级3
-- ============================================================

-- ------------------------------------------------------------
-- 0. 清理历史数据（保证可重复执行）
-- ------------------------------------------------------------
DELETE FROM app_user.alarm_audit      WHERE alarm_id LIKE 't45-alarm-%';
DELETE FROM app_user.work_order_log   WHERE order_id IN
    (SELECT order_id FROM app_user.work_order WHERE order_no LIKE 'T45-WO-%');
DELETE FROM app_user.work_order       WHERE order_no LIKE 'T45-WO-%' OR alarm_id LIKE 't45-alarm-%';
DELETE FROM app_user.alarm_data       WHERE id LIKE 't45-alarm-%';
DELETE FROM app_user.warning_threshold WHERE id LIKE 't45-thr-%' OR device_code LIKE 'T45-%';
DELETE FROM app_user.equipment_status WHERE equipment_id LIKE 'T45-EQ-%';
DELETE FROM app_user.equipment_base   WHERE equipment_id LIKE 'T45-EQ-%';

-- ------------------------------------------------------------
-- 1. 设备类型（挂在顶级类型“供水”之下）与设备基础信息
-- ------------------------------------------------------------
DO $$
DECLARE
    v_pressure_id varchar(64);
    v_flow_id     varchar(64);
    v_leak_id     varchar(64);
BEGIN
    -- 设备类型以库中现有 equipment_type 为权威：本脚本不新增任何设备类型，
    -- 按 type_id + parent_type_id 定位后取出 id，供 equipment_base.equipment_type_id 使用。
    SELECT id INTO v_pressure_id FROM app_user.equipment_type
     WHERE type_id = 'pressure_gauge' AND parent_type_id = '1' LIMIT 1;
    SELECT id INTO v_flow_id FROM app_user.equipment_type
     WHERE type_id = 'ultrasonic_flowmeter' AND parent_type_id = '1' LIMIT 1;
    SELECT id INTO v_leak_id FROM app_user.equipment_type
     WHERE type_id = 'leak_monitor' AND parent_type_id = '1' LIMIT 1;

    IF v_pressure_id IS NULL OR v_flow_id IS NULL OR v_leak_id IS NULL THEN
        RAISE EXCEPTION 'equipment_type 缺少供水监测类型（pressure_gauge / ultrasonic_flowmeter / leak_monitor 且 parent_type_id=1），请先核对现有类型数据';
    END IF;
    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
        -- 报警中：设备状态 alarm_status=1
        ('T45-EQ-PR-CN', 'CN-GS-202609160001', '城南加压站压力监测点', 'T45-PR-1', v_pressure_id,
         113.28000000, 23.12000000, '城南大道1号', '张巡维', '13800000101', '市供水公司',
         '#45报警台账测试数据', now(), now(), '0', '城南'),
        -- 正常：设备状态 alarm_status=0
        ('T45-EQ-PR-CB', 'CB-GS-202609160002', '城北末梢压力监测点', 'T45-PR-2', v_pressure_id,
         113.31000000, 23.17000000, '城北大道2号', '李巡维', '13800000102', '市供水公司',
         '#45报警台账测试数据', now(), now(), '0', '城北'),
        -- 报警中：用于流量分类
        ('T45-EQ-FL-TC', 'TC-GS-202609160003', '太常厂区流量监测点', 'T45-FL-1', v_flow_id,
         113.25000000, 23.09000000, '太常路3号', '王巡维', '13800000103', '市供水公司',
         '#45报警台账测试数据', now(), now(), '0', '太常'),
        -- 正常但离线：设备报警状态与在线状态分离
        ('T45-EQ-LK-CN', 'CN-GS-202609160004', '城南漏失声波监测点', 'T45-LK-1', v_leak_id,
         113.28500000, 23.12500000, '城南大道4号', '赵巡维', '13800000104', '市供水公司',
         '#45报警台账测试数据', now(), now(), '0', '城南'),
        -- 故意不建 equipment_status 记录：验证设备报警状态回退分支
        ('T45-EQ-FL-CB', 'CB-GS-202609160005', '城北出厂流量监测点', 'T45-FL-2', v_flow_id,
         113.31500000, 23.17500000, '城北大道5号', '孙巡维', '13800000105', '市供水公司',
         '#45报警台账测试数据', now(), now(), '0', '城北');
END $$;

-- ------------------------------------------------------------
-- 2. 设备状态（equipment_status）
--    T45-EQ-FL-CB 刻意缺失，用于验证回退逻辑
-- ------------------------------------------------------------
INSERT INTO app_user.equipment_status
    (equipment_id, current_status, alarm_status, online_status,
     installation_addr, status_update_time, create_time, del_flag)
VALUES
    ('T45-EQ-PR-CN', 1, 1, 1, '城南大道1号', now() - interval '30 minutes', now() - interval '30 minutes', '0'),
    ('T45-EQ-PR-CB', 1, 0, 1, '城北大道2号', now() - interval '1 hour',     now() - interval '1 hour',     '0'),
    ('T45-EQ-FL-TC', 1, 1, 1, '太常路3号',   now() - interval '2 hours',    now() - interval '2 hours',    '0'),
    ('T45-EQ-LK-CN', 1, 0, 0, '城南大道4号', now() - interval '3 hours',    now() - interval '3 hours',    '0');

-- ------------------------------------------------------------
-- 3. 阈值（warning_threshold）
--    t45-thr-004 下限为 0，用于验证“阈值下限为 0”不被当成未配置
-- ------------------------------------------------------------
INSERT INTO app_user.warning_threshold
    (id, device_code, warning_type, warning_code, min_value, max_value,
     period_start, period_end, remark, create_time, update_time)
VALUES
    ('t45-thr-001', 'CN-GS-202609160001', '压力预警', 'T45-WARN-PRESSURE', 0.40, 0.80,
     NULL, NULL, '#45测试-全天阈值', now(), now()),
    ('t45-thr-002', 'CB-GS-202609160002', '压力预警', 'T45-WARN-PRESSURE', 0.35, 0.85,
     NULL, NULL, '#45测试-全天阈值', now(), now()),
    ('t45-thr-003', 'TC-GS-202609160003', '流量预警', 'T45-WARN-FLOW', 10.00, 100.00,
     '08:00', '20:00', '#45测试-分时阈值(08:00-20:00)', now(), now()),
    ('t45-thr-004', 'CN-GS-202609160004', '漏失预警', 'T45-WARN-LEAK', 0.00, 5.00,
     NULL, NULL, '#45测试-阈值为0的边界', now(), now()),
    ('t45-thr-005', 'CB-GS-202609160005', '流量预警', 'T45-WARN-FLOW', 8.00, 120.00,
     NULL, NULL, '#45测试-全天阈值', now(), now());

-- ------------------------------------------------------------
-- 4. 报警流水（alarm_data）
--     alarm_status：0-未处理 1-已处理；alarm_level：1 最高
-- ------------------------------------------------------------
INSERT INTO app_user.alarm_data
    (id, device_code, device_name, device_type, warning_type, warning_code,
     actual_value, min_value, max_value, alarm_status, alarm_level, deviation_ratio,
     alarm_time, handle_time, handle_user, handle_remark, remark, create_time, update_time)
VALUES
    -- 今日：四个等级各一条，全部待审核（等级序列与展示状态的基础样本）
    ('t45-alarm-001', 'CN-GS-202609160001', '城南加压站压力监测点', '压力监测', '压力预警', 'T45-WARN-PRESSURE',
     0.95, 0.40, 0.80, 0, 1, 18.75, now() - interval '40 minutes', NULL, NULL, NULL,
     '#45待审核-一级', now() - interval '40 minutes', now() - interval '40 minutes'),
    ('t45-alarm-002', 'CB-GS-202609160002', '城北末梢压力监测点', '压力监测', '压力预警', 'T45-WARN-PRESSURE',
     0.90, 0.35, 0.85, 0, 2, 5.88, now() - interval '35 minutes', NULL, NULL, NULL,
     '#45待审核-二级', now() - interval '35 minutes', now() - interval '35 minutes'),
    ('t45-alarm-003', 'TC-GS-202609160003', '太常厂区流量监测点', '流量监测', '流量预警', 'T45-WARN-FLOW',
     112.00, 10.00, 100.00, 0, 3, 12.00, now() - interval '30 minutes', NULL, NULL, NULL,
     '#45待审核-三级', now() - interval '30 minutes', now() - interval '30 minutes'),
    ('t45-alarm-004', 'CN-GS-202609160004', '城南漏失声波监测点', '漏失监测', '漏失预警', 'T45-WARN-LEAK',
     7.20, 0.00, 5.00, 0, 4, 44.00, now() - interval '25 minutes', NULL, NULL, NULL,
     '#45待审核-四级(阈值下限为0)', now() - interval '25 minutes', now() - interval '25 minutes'),

    -- 昨日：已转工单（关联未办结工单：处理中 / 待接单）
    ('t45-alarm-005', 'TC-GS-202609160003', '太常厂区流量监测点', '流量监测', '流量预警', 'T45-WARN-FLOW',
     130.50, 10.00, 100.00, 0, 1, 30.50, now() - interval '1 day' - interval '20 minutes',
     NULL, NULL, NULL, '#45已转工单-处理中', now() - interval '1 day', now() - interval '1 day'),
    ('t45-alarm-006', 'CN-GS-202609160001', '城南加压站压力监测点', '压力监测', '压力预警', 'T45-WARN-PRESSURE',
     0.28, 0.40, 0.80, 0, 3, 30.00, now() - interval '1 day' - interval '10 minutes',
     NULL, NULL, NULL, '#45已转工单-待接单', now() - interval '1 day', now() - interval '1 day'),

    -- 前2日：已解除（报警记录已处理）+ 工单已结案不得算已转工单
    ('t45-alarm-007', 'CB-GS-202609160002', '城北末梢压力监测点', '压力监测', '压力预警', 'T45-WARN-PRESSURE',
     0.88, 0.35, 0.85, 1, 2, 3.53, now() - interval '2 days' - interval '30 minutes',
     now() - interval '2 days', '李巡维', '压力恢复正常',
     '#45已解除-已处理', now() - interval '2 days', now() - interval '2 days'),
    ('t45-alarm-015', 'CN-GS-202609160004', '城南漏失声波监测点', '漏失监测', '漏失预警', 'T45-WARN-LEAK',
     6.10, 0.00, 5.00, 0, 1, 22.00, now() - interval '2 days' - interval '15 minutes',
     NULL, NULL, NULL, '#45工单已取消-应回到待审核', now() - interval '2 days', now() - interval '2 days'),

    -- 前3日：误报解除（报警记录未处理，但审核结论为误报）
    ('t45-alarm-008', 'CN-GS-202609160004', '城南漏失声波监测点', '漏失监测', '漏失预警', 'T45-WARN-LEAK',
     5.40, 0.00, 5.00, 0, 1, 8.00, now() - interval '3 days' - interval '20 minutes',
     NULL, NULL, NULL, '#45已解除-误报', now() - interval '3 days', now() - interval '3 days'),

    -- 前4日：无 equipment_status 记录的设备（设备报警状态回退分支）
    ('t45-alarm-012', 'CB-GS-202609160005', '城北出厂流量监测点', '流量监测', '流量预警', 'T45-WARN-FLOW',
     3.20, 8.00, 120.00, 0, 4, 60.00, now() - interval '4 days' - interval '20 minutes',
     NULL, NULL, NULL, '#45无设备状态记录-待审核', now() - interval '4 days', now() - interval '4 days'),

    -- 前5日：报警值与阈值全为空（未配置阈值显示分支）
    ('t45-alarm-013', 'CN-GS-202609160001', '城南加压站压力监测点', '压力监测', '压力预警', 'T45-WARN-PRESSURE',
     NULL, NULL, NULL, 0, 3, NULL, now() - interval '5 days' - interval '20 minutes',
     NULL, NULL, NULL, '#45阈值未配置', now() - interval '5 days', now() - interval '5 days'),

    -- 前6日：趋势窗口首日，仍应出现在趋势里
    ('t45-alarm-009', 'CN-GS-202609160004', '城南漏失声波监测点', '漏失监测', '漏失预警', 'T45-WARN-LEAK',
     5.80, 0.00, 5.00, 1, 4, 16.00, now() - interval '6 days' - interval '20 minutes',
     now() - interval '6 days', '赵巡维', '已现场核查',
     '#45窗口边界-已解除', now() - interval '6 days', now() - interval '6 days'),

    -- 窗口外：用于验证时间范围查询能把它们查出来、而 7 天趋势不含它们
    ('t45-alarm-010', 'TC-GS-202609160003', '太常厂区流量监测点', '流量监测', '流量预警', 'T45-WARN-FLOW',
     140.00, 10.00, 100.00, 0, 1, 40.00, now() - interval '9 days' - interval '20 minutes',
     NULL, NULL, NULL, '#45窗口外-待审核', now() - interval '9 days', now() - interval '9 days'),
    ('t45-alarm-011', 'CB-GS-202609160002', '城北末梢压力监测点', '压力监测', '压力预警', 'T45-WARN-PRESSURE',
     0.30, 0.35, 0.85, 1, 2, 14.71, now() - interval '12 days' - interval '20 minutes',
     now() - interval '12 days', '李巡维', '已换表处理',
     '#45窗口外-已解除', now() - interval '12 days', now() - interval '12 days'),

    -- 今日：阈值越界标红样本（下限 0、上限 120，实际 150）
    ('t45-alarm-014', 'TC-GS-202609160003', '太常厂区流量监测点', '流量监测', '流量预警', 'T45-WARN-FLOW',
     150.00, 0.00, 120.00, 0, 2, 25.00, now() - interval '5 minutes',
     NULL, NULL, NULL, '#45待审核-阈值越界标红', now() - interval '5 minutes', now() - interval '5 minutes');

-- ------------------------------------------------------------
-- 5. 关联工单（work_order）
--    工单状态：0-待派单 1-待接单 2-已接单 3-处理中 4-待验收 5-已结案 6-延期中 7-已取消
--    仅未办结工单会让报警显示为“已转工单”
--
--    注意：工单流程重构后 work_order.order_type 已被 DROP（见
--    migration_work_order_status_refactor.sql），报警来源工单以 alarm_id 关联识别；
--    dispatch_user / receive_user / verify_user 均为 BIGINT 用户ID，不得写姓名。
--    本段只写入 WorkOrder 实体确证的列，避免依赖已废弃字段。
-- ------------------------------------------------------------
INSERT INTO app_user.work_order
    (order_no, alarm_id, device_id, device_code, order_level, order_desc,
     dispatch_user, dispatch_time, receive_user, order_status,
     fault_time, create_time, update_time)
VALUES
    -- 处理中：t45-alarm-005 → 已转工单，可验证“前往工单处置”跳转
    ('T45-WO-001', 't45-alarm-005', 'T45-EQ-FL-TC', 'TC-GS-202609160003', 0,
     '#45测试工单-太常流量超限处置', 1, now() - interval '22 hours', 2, 3,
     now() - interval '1 day', now() - interval '1 day', now() - interval '1 day'),
    -- 待接单：t45-alarm-006 → 已转工单
    ('T45-WO-002', 't45-alarm-006', 'T45-EQ-PR-CN', 'CN-GS-202609160001', 1,
     '#45测试工单-城南压力偏低待接单', 1, now() - interval '22 hours', NULL, 1,
     now() - interval '1 day', now() - interval '1 day', now() - interval '1 day'),
    -- 已取消：t45-alarm-015 关联已取消工单，报警不得算作已转工单
    ('T45-WO-003', 't45-alarm-015', 'T45-EQ-LK-CN', 'CN-GS-202609160004', 1,
     '#45测试工单-已取消应回到待审核', 1, now() - interval '2 days', NULL, 7,
     now() - interval '2 days', now() - interval '2 days', now() - interval '2 days');

INSERT INTO app_user.work_order_log
    (order_id, oper_type, oper_desc, oper_user_id, oper_user_name, oper_time)
SELECT order_id, 1, '#45测试-派单', 1, '调度管理员', now() - interval '22 hours'
  FROM app_user.work_order WHERE order_no IN ('T45-WO-001', 'T45-WO-002');

-- ------------------------------------------------------------
-- 6. 报警审核记录（alarm_audit）
--    t45-alarm-008 审核结论=2（误报）→ 报警展示状态应为已解除
-- ------------------------------------------------------------
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
    ('t45-alarm-008', 2, 2, '#45测试审核：现场核实为误报，已解除', NULL,
     1, '调度管理员', now() - interval '3 days', now() - interval '3 days', now() - interval '3 days'),
    ('t45-alarm-007', 1, 1, '#45测试审核：报警属实，已处置完成', NULL,
     1, '调度管理员', now() - interval '2 days', now() - interval '2 days', now() - interval '2 days');

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