-- ============================================================
-- 供水管网 T6 设备准入、台账与异常态势 测试数据脚本（KingbaseES）
-- 说明：
--   1. 测试主键与编号均使用 T6-TEST- 前缀，可重复执行。
--   2. 本脚本只初始化测试数据，不创建业务表；执行前请确认以下表已存在：
--      app_user.equipment_type、app_user.equipment_base、app_user.equipment_status、
--      app_user.alarm_data、app_user.work_order
--   3. 覆盖场景：正常供水设备、报警供水设备、维修供水设备、离线供水设备、
--      非供水设备排除、有效报警原因、真实工单编号与状态。
-- ============================================================

-- ------------------------------------------------------------
-- 0. 清理历史测试数据（先清工单与报警，再清状态与设备，最后清临时类型）
-- ------------------------------------------------------------
DELETE FROM app_user.work_order_log WHERE order_id IN
    (SELECT order_id FROM app_user.work_order WHERE order_no LIKE 'T6-TEST-WO-%');
DELETE FROM app_user.work_order WHERE order_no LIKE 'T6-TEST-WO-%';
DELETE FROM app_user.alarm_data WHERE id LIKE 'T6-TEST-ALARM-%';
DELETE FROM app_user.equipment_status WHERE equipment_id LIKE 'T6-TEST-EQ-%';
DELETE FROM app_user.equipment_base WHERE equipment_id LIKE 'T6-TEST-EQ-%';
DELETE FROM app_user.equipment_type WHERE id = 'T6-TEST-GAS-TYPE';

-- ------------------------------------------------------------
-- 1. 校验规范供水设备类型，并创建临时非供水类型用于边界排除
-- ------------------------------------------------------------
DO $$
DECLARE
    v_count integer;
BEGIN
    SELECT count(*) INTO v_count
      FROM app_user.equipment_type
     WHERE id = '1' AND parent_type_id = '0' AND type_name = '供水';
    IF v_count <> 1 THEN
        RAISE EXCEPTION '缺少规范供水顶级设备类型：id=1,type_name=供水,parent_type_id=0';
    END IF;

    SELECT count(*) INTO v_count
      FROM app_user.equipment_type
     WHERE type_id IN ('ws_flow', 'ws_pressure', 'ws_leak')
       AND parent_type_id = '1';
    IF v_count <> 3 THEN
        RAISE EXCEPTION '缺少规范供水监测子类型：ws_flow/ws_pressure/ws_leak 必须 parent_type_id=1';
    END IF;
END $$;

INSERT INTO app_user.equipment_type
    (id, type_id, type_name, parent_type_id, type_remark)
VALUES
    ('T6-TEST-GAS-TYPE', 't6_test_gas', '燃气(测试)', '0', 'T6非供水设备排除测试类型');

-- ------------------------------------------------------------
-- 2. 设备基础信息（4台供水设备 + 1台非供水设备）
-- ------------------------------------------------------------
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
    ('T6-TEST-EQ-PRESSURE-ALARM', 'CN-GS-202609020001', 'T6测试压力报警设备', 'PRESSURE-T6',
     (SELECT id FROM app_user.equipment_type WHERE type_id = 'ws_pressure' AND parent_type_id = '1' LIMIT 1),
     113.28000000, 23.12000000, 'T6测试大道1号', 'T6测试维护员', '13800000001',
     'T6测试供水公司', 'T6设备准入与异常态势测试数据', now() - interval '7 days', now(), '0', '城南'),
    ('T6-TEST-EQ-FLOW-REPAIR', 'CB-GS-202609020001', 'T6测试流量维修设备', 'FLOW-T6',
     (SELECT id FROM app_user.equipment_type WHERE type_id = 'ws_flow' AND parent_type_id = '1' LIMIT 1),
     113.29000000, 23.13000000, 'T6测试大道2号', 'T6测试维护员', '13800000002',
     'T6测试供水公司', 'T6设备准入与异常态势测试数据', now() - interval '7 days', now(), '0', '城北'),
    ('T6-TEST-EQ-LEAK-OFFLINE', 'TC-GS-202609020001', 'T6测试漏失离线设备', 'LEAK-T6',
     (SELECT id FROM app_user.equipment_type WHERE type_id = 'ws_leak' AND parent_type_id = '1' LIMIT 1),
     113.30000000, 23.14000000, 'T6测试大道3号', 'T6测试维护员', '13800000003',
     'T6测试供水公司', 'T6设备准入与异常态势测试数据', now() - interval '7 days', now(), '0', '太常'),
    ('T6-TEST-EQ-PRESSURE-NORMAL', 'CN-GS-202609020002', 'T6测试压力正常设备', 'PRESSURE-T6',
     (SELECT id FROM app_user.equipment_type WHERE type_id = 'ws_pressure' AND parent_type_id = '1' LIMIT 1),
     113.31000000, 23.15000000, 'T6测试大道4号', 'T6测试维护员', '13800000004',
     'T6测试供水公司', 'T6设备准入与异常态势测试数据', now() - interval '7 days', now(), '0', '城南'),
    ('T6-TEST-EQ-GAS-EXCLUDED', 'CN-RQ-202609020001', 'T6测试燃气排除设备', 'GAS-T6',
     'T6-TEST-GAS-TYPE', 113.32000000, 23.16000000, 'T6测试大道5号',
     'T6测试维护员', '13800000005', 'T6测试燃气公司', 'T6非供水排除测试数据',
     now() - interval '7 days', now(), '0', '城南');

-- ------------------------------------------------------------
-- 3. 设备状态（current_status / alarm_status / online_status 分别表达不同状态）
-- ------------------------------------------------------------
INSERT INTO app_user.equipment_status
    (equipment_id, current_status, alarm_status, online_status,
     installation_addr, status_update_time, create_time, del_flag)
VALUES
    ('T6-TEST-EQ-PRESSURE-ALARM', 1, 1, 1, 'T6测试大道1号',
     now() - interval '1 hour', now() - interval '1 hour', '0'),
    ('T6-TEST-EQ-FLOW-REPAIR', 3, 0, 0, 'T6测试大道2号',
     now() - interval '2 hours', now() - interval '2 hours', '0'),
    ('T6-TEST-EQ-LEAK-OFFLINE', 1, 0, 0, 'T6测试大道3号',
     now() - interval '3 hours', now() - interval '3 hours', '0'),
    ('T6-TEST-EQ-PRESSURE-NORMAL', 1, 0, 1, 'T6测试大道4号',
     now() - interval '4 hours', now() - interval '4 hours', '0'),
    ('T6-TEST-EQ-GAS-EXCLUDED', 1, 1, 1, 'T6测试大道5号',
     now() - interval '5 hours', now() - interval '5 hours', '0');

-- ------------------------------------------------------------
-- 4. 有效报警原因（供水报警设备 + 非供水排除设备）
-- ------------------------------------------------------------
INSERT INTO app_user.alarm_data
    (id, device_code, device_name, device_type, warning_type, warning_code,
     min_value, max_value, actual_value, alarm_level, deviation_ratio,
     alarm_status, alarm_time, handle_time, handle_user, handle_remark, remark,
     create_time, update_time)
VALUES
    ('T6-TEST-ALARM-PRESSURE', 'CN-GS-202609020001', 'T6测试压力报警设备', '压力监测',
     '供水压力超限', 'WARN-PRESSURE-T6', 0.20, 0.80, 1.20, 2, 50.00,
     0, now() - interval '1 hour', NULL, NULL, NULL, 'T6设备异常态势测试数据',
     now() - interval '1 hour', now() - interval '1 hour'),
    ('T6-TEST-ALARM-GAS-EXCLUDED', 'CN-RQ-202609020001', 'T6测试燃气排除设备', '燃气监测',
     '燃气浓度超限', 'WARN-GAS-T6', 0.00, 20.00, 45.00, 1, 125.00,
     0, now() - interval '1 hour', NULL, NULL, NULL, 'T6非供水排除测试数据',
     now() - interval '1 hour', now() - interval '1 hour');

-- ------------------------------------------------------------
-- 5. 关联统一工单（真实 order_no，不用随机 ID）
-- ------------------------------------------------------------
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, maintenance_before_img, maintenance_after_img,
     verify_user, verify_time, create_time, update_time)
VALUES
    ('T6-TEST-WO-001', 'T6-TEST-ALARM-PRESSURE', NULL, NULL,
     'T6-TEST-EQ-PRESSURE-ALARM', 'CN-GS-202609020001',
     1, 2, 'T6供水压力超限故障维修', NULL, NULL, now() - interval '50 minutes',
     '1', 4, NULL, NULL, now() + interval '1 day',
     NULL, NULL, NULL, NULL, NULL, NULL, now() - interval '50 minutes', now()),
    ('T6-TEST-WO-002', NULL, NULL, NULL,
     'T6-TEST-EQ-FLOW-REPAIR', 'CB-GS-202609020001',
     1, 2, 'T6供水流量设备维修', NULL, NULL, now() - interval '2 hours',
     '2', 4, NULL, NULL, now() + interval '1 day',
     NULL, NULL, NULL, NULL,
     NULL, NULL, now() - interval '2 hours', now());

-- ============================================================
-- 快速核验：
--   1. 供水设备台账应包含 4 台 T6 供水设备，不包含 CN-RQ-202609020001。
--   2. 异常设备页应包含压力报警设备与流量维修设备；正常压力设备与漏失离线设备不进入异常页。
--   3. CN-GS-202609020001 的异常原因为“供水压力超限”，关联工单号为 T6-TEST-WO-001，状态为处理中。
--   4. CB-GS-202609020001 的异常原因为设备维修中，关联工单号为 T6-TEST-WO-002，状态为处理中。
--   5. CN-RQ-202609020001 属于燃气测试类型，任何供水页面均不应展示。
-- ============================================================