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

-- ------------------------------------------------------------
-- 0. 清理历史测试数据（保证可重复执行）
-- ------------------------------------------------------------
DELETE FROM app_user.alarm_audit WHERE alarm_id LIKE 'test-alarm-%';
DELETE FROM app_user.work_order_log WHERE order_id IN
    (SELECT order_id FROM app_user.work_order WHERE alarm_id LIKE 'test-alarm-%' OR order_no = 'TEST-WO-001');
DELETE FROM app_user.work_order WHERE alarm_id LIKE 'test-alarm-%' OR order_no = 'TEST-WO-001';
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_type_id='0'），不存在则创建
    SELECT id INTO v_top_id FROM app_user.equipment_type
     WHERE type_name = '供水' AND parent_type_id = '0' LIMIT 1;
    IF v_top_id IS NULL THEN
        INSERT INTO app_user.equipment_type (id, type_id, type_name, parent_type_id, type_remark)
        VALUES ('TEST-WATER-TOP', 'test_water_top', '供水', '0', '供水监测报警测试-顶级类型(可清理)')
        RETURNING id INTO v_top_id;
    END IF;

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

    SELECT id INTO v_pressure_id FROM app_user.equipment_type WHERE type_id = 'test_water_pressure' LIMIT 1;
    IF v_pressure_id IS NULL THEN
        INSERT INTO app_user.equipment_type (id, type_id, type_name, parent_type_id, type_remark)
        VALUES ('TEST-WATER-PRESSURE', 'test_water_pressure', '压力监测(测试)', v_top_id, '供水监测报警测试子类型')
        RETURNING id INTO v_pressure_id;
    END IF;

    SELECT id INTO v_flow_id FROM app_user.equipment_type WHERE type_id = 'test_water_flow' LIMIT 1;
    IF v_flow_id IS NULL THEN
        INSERT INTO app_user.equipment_type (id, type_id, type_name, parent_type_id, type_remark)
        VALUES ('TEST-WATER-FLOW', 'test_water_flow', '流量监测(测试)', 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条 / 平均响应25.00分钟 / 设备异常率75%
-- ------------------------------------------------------------
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
    -- 未处理：水位1级（今日）
    ('test-alarm-001', 'TEST-WL-001', '测试水位监测点1', '水位监测', '水位预警', 'WARN-WATER-LEVEL-TEST',
     0.20, 0.50, 3.00, 0, 1, 40.00,
     now() - interval '180 minutes', NULL, NULL, NULL, '供水监测报警测试数据',
     now() - interval '180 minutes', now() - interval '180 minutes'),
    -- 未处理：水位2级（今日，同设备第二条）
    ('test-alarm-002', 'TEST-WL-001', '测试水位监测点1', '水位监测', '水位预警', 'WARN-WATER-LEVEL-TEST',
     0.30, 0.50, 3.00, 0, 2, 35.00,
     now() - interval '150 minutes', NULL, NULL, NULL, '供水监测报警测试数据',
     now() - interval '150 minutes', now() - interval '150 minutes'),
    -- 已处理：水位3级（今日，响应35分钟）→ 关联测试工单
    ('test-alarm-003', 'TEST-WL-002', '测试水位监测点2', '水位监测', '水位预警', 'WARN-WATER-LEVEL-TEST',
     3.80, 0.50, 3.00, 1, 3, 25.00,
     now() - interval '120 minutes', now() - interval '85 minutes', '测试用户', '现场处理完成',
     '供水监测报警测试数据', now() - interval '120 minutes', now() - interval '85 minutes'),
    -- 未处理：压力1级（今日，一张图最高级点位）
    ('test-alarm-004', 'TEST-PR-001', '测试压力监测点1', '压力监测', '压力预警', 'WARN-PRESSURE-TEST',
     0.95, 0.40, 0.80, 0, 1, 30.00,
     now() - interval '100 minutes', NULL, NULL, NULL, '供水监测报警测试数据',
     now() - interval '100 minutes', now() - interval '100 minutes'),
    -- 已处理：压力2级（今日，响应30分钟）
    ('test-alarm-005', 'TEST-PR-001', '测试压力监测点1', '压力监测', '压力预警', 'WARN-PRESSURE-TEST',
     0.90, 0.40, 0.80, 1, 2, 28.00,
     now() - interval '90 minutes', now() - interval '60 minutes', '测试用户', '压力调整',
     '供水监测报警测试数据', now() - interval '90 minutes', now() - interval '60 minutes'),
    -- 已处理：流量4级（昨日，不计入今日统计）
    ('test-alarm-006', 'TEST-FL-001', '测试流量监测点1', '流量监测', '流量预警', 'WARN-FLOW-TEST',
     120.00, 10.00, 100.00, 1, 4, 20.00,
     now() - interval '1 day' - interval '60 minutes', now() - interval '1 day',
     '测试用户', '流量恢复正常', '供水监测报警测试数据',
     now() - interval '1 day' - interval '60 minutes', now() - interval '1 day'),
    -- 未处理：流量3级（昨日，用于实时列表/历史过滤/解除测试）
    ('test-alarm-007', 'TEST-FL-001', '测试流量监测点1', '流量监测', '流量预警', 'WARN-FLOW-TEST',
     115.00, 10.00, 100.00, 0, 3, 15.00,
     now() - interval '1 day' - interval '30 minutes', NULL, NULL, NULL, '供水监测报警测试数据',
     now() - interval '1 day' - interval '30 minutes', now() - interval '1 day' - interval '30 minutes'),
    -- 已处理：水位2级（今日，响应10分钟）
    ('test-alarm-008', 'TEST-WL-002', '测试水位监测点2', '水位监测', '水位预警', 'WARN-WATER-LEVEL-TEST',
     3.50, 0.50, 3.00, 1, 2, 18.00,
     now() - interval '300 minutes', now() - interval '290 minutes', '测试用户', '水位回落',
     '供水监测报警测试数据', now() - interval '300 minutes', now() - interval '290 minutes');

-- ------------------------------------------------------------
-- 3. 阈值数据（warning_threshold，含分时阈值）
-- ------------------------------------------------------------
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
    ('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', '供水监测报警测试-分时阈值(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, alarm_id, case_id, case_no, device_id, device_code,
     order_type, 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,
     work_image, hazard_id, hazard_no)
VALUES
    ('TEST-WO-001', 'test-alarm-003', NULL, NULL, 'TEST-EQ-WL-002', 'TEST-WL-002',
     1, 2, '供水监测报警测试工单-水位超限', NULL, NULL, NULL,
     NULL, 5, NULL, NULL, NULL,
     NULL, '现场已处理', NULL, NULL, now(), now(),
     NULL, NULL, NULL);

INSERT INTO app_user.work_order_log
    (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 order_id IN
--     (SELECT order_id FROM app_user.work_order WHERE alarm_id LIKE 'test-alarm-%' OR order_no = 'TEST-WO-001');
-- DELETE FROM app_user.work_order WHERE alarm_id LIKE 'test-alarm-%' OR order_no = 'TEST-WO-001';
-- 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-%';