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

-- ============================================================
-- 供水管网统计分析 / GIS 一张图接口测试数据
-- KingbaseES / PostgreSQL 兼容；目标 schema：app_user
--
-- 用法：
--   1. 先执行 water_supply_facility_gis.sql，确保四类点设施坐标列已存在。
--      管网设施的坐标列随统一管网表 pipe_network_base 提供，无需额外迁移。
--   2. 执行本脚本。本脚本按 GIS-TEST- 前缀清理后重建，可重复执行。
--   3. 通过末尾 SELECT 获取详情接口所需的实际主键 ID。
--   4. 测试完成后执行文件末尾注释中的清理 SQL。
--
-- 设计覆盖：
--   - 五类设施的 Point / LineString GeoJSON；
--   - 完整坐标与缺失坐标过滤；
--   - 正常、异常、空状态；
--   - 管径、铺设年代（含未填报）、材质、压力等级和用水量分组。
-- ============================================================

BEGIN;

-- ------------------------------------------------------------
-- 0. 清理本脚本历史数据
-- ------------------------------------------------------------
DELETE FROM app_user.water_source_info
 WHERE source_id LIKE 'GIS-TEST-%';
DELETE FROM app_user.water_plant_info
 WHERE plant_code LIKE 'GIS-TEST-%';
DELETE FROM app_user.pump_station
 WHERE station_code LIKE 'GIS-TEST-%'
   AND station_type = 'water_supply';
DELETE FROM app_user.pipe_network_base
 WHERE network_code LIKE 'GIS-TEST-%'
   AND network_type = '2';
DELETE FROM app_user.water_user_info
 WHERE user_code LIKE 'GIS-TEST-%';

-- ------------------------------------------------------------
-- 1. 水源地：2 个坐标完整 + 1 个缺失坐标
-- ------------------------------------------------------------
INSERT INTO app_user.water_source_info
    (source_id, source_name, source_type, unit, address, water_type,
     protection_level, water_quality_level, protection_scope, report_time,
     status, remark, del_flag, create_by, create_time, longitude, latitude)
VALUES
    ('GIS-TEST-S-001', 'GIS测试水源地-东湖', '地表水', '东湖水务', '测试东湖路1号', '湖泊',
     '一级', 'II类', '半径500米', DATE '2025-01-15',
     '0', 'GIS接口测试-坐标完整-正常', '0', 'test', CURRENT_TIMESTAMP, 113.28000000, 23.12000000),
    ('GIS-TEST-S-002', 'GIS测试水源地-北河', '河流水源', '北河水务', '测试北河路2号', '河流',
     '二级', 'III类', '半径300米', DATE '2025-02-15',
     '1', 'GIS接口测试-坐标完整-异常', '0', 'test', CURRENT_TIMESTAMP, 113.29000000, 23.13000000),
    ('GIS-TEST-S-003', 'GIS测试水源地-待测', '地下水', '测试水务', '测试待测路3号', '地下水',
     '三级', 'IV类', '待补录', DATE '2025-03-15',
     NULL, 'GIS接口测试-缺失坐标-不应进入图层', '0', 'test', CURRENT_TIMESTAMP, NULL, NULL);

-- ------------------------------------------------------------
-- 2. 水厂：1 个坐标完整 + 1 个缺失坐标
-- ------------------------------------------------------------
INSERT INTO app_user.water_plant_info
    (plant_code, plant_name, "location", water_source_type,
     design_capacity, current_capacity, treatment_process,
     commission_date, water_quality_standard, status, principal,
     contact_phone, remark, del_flag, create_by, create_time, longitude, latitude)
VALUES
    ('GIS-TEST-P-001', 'GIS测试水厂-一厂', '测试工业园88号', '地表水',
     80000.00, 60000.00, '常规处理', DATE '2018-06-01', 'GB5749-2022', '0',
     '测试厂长', '13800000001', 'GIS接口测试-坐标完整-正常', '0', 'test', CURRENT_TIMESTAMP,
     113.30000000, 23.14000000),
    ('GIS-TEST-P-002', 'GIS测试水厂-二厂', '测试开发区99号', '地下水',
     50000.00, 45000.00, '深度处理', DATE '2020-08-01', 'GB5749-2022', '1',
     '测试厂长2', '13800000002', 'GIS接口测试-缺失坐标-概览计数', '0', 'test', CURRENT_TIMESTAMP,
     NULL, NULL);

-- ------------------------------------------------------------
-- 3. 泵站：1 个坐标完整 + 1 个缺失坐标
--    泵站表由供水/排水共用，必须以 station_type='water_supply' 限定供水口径。
-- ------------------------------------------------------------
INSERT INTO app_user.pump_station
    (station_code, station_name, "location", station_type, pump_count, design_flow,
     design_head, power_capacity, commission_date, status, principal,
     contact_phone, remark, del_flag, create_by, create_time, longitude, latitude)
VALUES
    ('GIS-TEST-PS-001', 'GIS测试泵站-东区', '测试东区泵站路10号', 'water_supply', 4, 1200.00,
     35.00, 450.00, DATE '2019-04-01', '0', '测试站长', '13800000003',
     'GIS接口测试-坐标完整-正常', '0', 'test', CURRENT_TIMESTAMP, 113.31000000, 23.15000000),
    ('GIS-TEST-PS-002', 'GIS测试泵站-西区', '测试西区泵站路20号', 'water_supply', 2, 600.00,
     28.00, 180.00, DATE '2021-09-01', NULL, '测试站长2', '13800000004',
     'GIS接口测试-缺失坐标-概览计数', '0', 'test', CURRENT_TIMESTAMP, NULL, NULL);

-- ------------------------------------------------------------
-- 4. 管网：3 条坐标完整 + 1 条缺失坐标
--    统一写入 pipe_network_base，network_type='2' 表示供水设施。
--    其中包含 build_date NULL，验证“未填报”分组；
--    原压力等级口径已退场，改用 network_level 等级（pipe_network_level 字典）。
-- ------------------------------------------------------------
INSERT INTO app_user.pipe_network_base
    (network_id, network_code, network_name, network_type, network_level,
     management_unit, location, length, diameter, material, build_date, status,
     remark, del_flag, create_by, create_time,
     start_longitude, start_latitude, end_longitude, end_latitude)
VALUES
    ('GIS-TEST-PIPE-001', 'GIS-TEST-PIPE-001', 'GIS测试管网-东湖主干线', '2', '0',
     '测试水务', '东湖水源地→一厂', 12500.50, 800, '球墨铸铁', DATE '2012-01-01', '0',
     'GIS接口测试-完整LineString-大管径', '0', 'test', CURRENT_TIMESTAMP,
     113.28000000, 23.12000000, 113.30000000, 23.14000000),
    ('GIS-TEST-PIPE-002', 'GIS-TEST-PIPE-002', 'GIS测试管网-北河支线', '2', '2',
     '测试水务', '北河水源地→东区泵站', 6800.00, 250, '钢管', DATE '2021-01-01', '1',
     'GIS接口测试-完整LineString-异常', '0', 'test', CURRENT_TIMESTAMP,
     113.29000000, 23.13000000, 113.31000000, 23.15000000),
    ('GIS-TEST-PIPE-003', 'GIS-TEST-PIPE-003', 'GIS测试管网-待补年代线', '2', NULL,
     '测试水务', '东区泵站→测试用户1', 900.00, 80, NULL, NULL, NULL,
     'GIS接口测试-完整LineString-空分组字段', '0', 'test', CURRENT_TIMESTAMP,
     113.31000000, 23.15000000, 113.32000000, 23.16000000),
    ('GIS-TEST-PIPE-004', 'GIS-TEST-PIPE-004', 'GIS测试管网-缺失坐标线', '2', '3',
     '测试水务', '测试用户1→测试用户2', 1500.00, 300, 'PVC', DATE '2018-01-01', '0',
     'GIS接口测试-缺失终点坐标-不应进入图层', '0', 'test', CURRENT_TIMESTAMP,
     113.32000000, 23.16000000, NULL, NULL);

-- ------------------------------------------------------------
-- 5. 用水户：居民、工业、商业、行政事业均参与，4 个坐标完整 + 1 个缺失坐标
-- ------------------------------------------------------------
INSERT INTO app_user.water_user_info
    (user_code, user_name, user_type, address, contact_person,
     contact_phone, meter_number, initial_reading, current_reading,
     annual_water_consumption, status, remark, del_flag, create_by,
     create_time, longitude, latitude)
VALUES
    ('GIS-TEST-U-001', 'GIS测试用水户-工业园', '工业', '测试工业园1号', '测试联系人1',
     '13800000005', 'GIS-METER-001', 1000.00, 52000.00, 51000.00, '0',
     'GIS接口测试-坐标完整-工业', '0', 'test', CURRENT_TIMESTAMP, 113.32000000, 23.16000000),
    ('GIS-TEST-U-002', 'GIS测试用水户-医院', '行政事业', '测试医院2号', '测试联系人2',
     '13800000006', 'GIS-METER-002', 500.00, 12500.00, 12000.00, '1',
     'GIS接口测试-坐标完整-行政事业', '0', 'test', CURRENT_TIMESTAMP, 113.33000000, 23.17000000),
    ('GIS-TEST-U-003', 'GIS测试用水户-商业街', '商业', '测试商业街3号', '测试联系人3',
     '13800000007', 'GIS-METER-003', 100.00, 800.00, 700.00, '0',
     'GIS接口测试-坐标完整-商业', '0', 'test', CURRENT_TIMESTAMP, 113.34000000, 23.18000000),
    ('GIS-TEST-U-004', 'GIS测试用水户-居民', '居民', '测试小区4号', '测试联系人4',
     '13800000008', 'GIS-METER-004', 10.00, 120.00, 110.00, '0',
     'GIS接口测试-坐标完整-居民', '0', 'test', CURRENT_TIMESTAMP, 113.35000000, 23.19000000),
    ('GIS-TEST-U-005', 'GIS测试用水户-缺失坐标', '商业', '测试商业街5号', '测试联系人5',
     '13800000009', 'GIS-METER-005', 100.00, 800.00, NULL, NULL,
     'GIS接口测试-缺失坐标-概览计数', '0', 'test', CURRENT_TIMESTAMP, NULL, NULL);

COMMIT;

-- ------------------------------------------------------------
-- 6. 验证输出
-- ------------------------------------------------------------
-- 预期：source=3/完整2，plant=2/完整1，pumpStation=2/完整1，pipe=4/完整3，user=5/完整4。
SELECT 'source' facility_type, COUNT(*) total_count,
       COALESCE(SUM(CASE WHEN longitude IS NOT NULL AND latitude IS NOT NULL THEN 1 ELSE 0 END), 0) geo_complete_count
  FROM app_user.water_source_info WHERE source_id LIKE 'GIS-TEST-%'
UNION ALL
SELECT 'plant', COUNT(*), COALESCE(SUM(CASE WHEN longitude IS NOT NULL AND latitude IS NOT NULL THEN 1 ELSE 0 END), 0)
  FROM app_user.water_plant_info WHERE plant_code LIKE 'GIS-TEST-%'
UNION ALL
SELECT 'pumpStation', COUNT(*), COALESCE(SUM(CASE WHEN longitude IS NOT NULL AND latitude IS NOT NULL THEN 1 ELSE 0 END), 0)
  FROM app_user.pump_station WHERE station_code LIKE 'GIS-TEST-%' AND station_type = 'water_supply'
UNION ALL
SELECT 'pipe', COUNT(*), COALESCE(SUM(CASE WHEN start_longitude IS NOT NULL AND start_latitude IS NOT NULL
                                             AND end_longitude IS NOT NULL AND end_latitude IS NOT NULL THEN 1 ELSE 0 END), 0)
  FROM app_user.pipe_network_base WHERE network_code LIKE 'GIS-TEST-%' AND network_type = '2'
UNION ALL
SELECT 'user', COUNT(*), COALESCE(SUM(CASE WHEN longitude IS NOT NULL AND latitude IS NOT NULL THEN 1 ELSE 0 END), 0)
  FROM app_user.water_user_info WHERE user_code LIKE 'GIS-TEST-%';

-- 详情接口 ID 查询：将结果中的 id 填入 /waterSupply/gis/map/detail/{facilityType}/{id}。
SELECT 'source' facility_type, id, source_id code FROM app_user.water_source_info WHERE source_id LIKE 'GIS-TEST-%' ORDER BY source_id;
SELECT 'plant' facility_type, id, plant_code code FROM app_user.water_plant_info WHERE plant_code LIKE 'GIS-TEST-%' ORDER BY plant_code;
SELECT 'pumpStation' facility_type, id, station_code code FROM app_user.pump_station WHERE station_code LIKE 'GIS-TEST-%' AND station_type = 'water_supply' ORDER BY station_code;
SELECT 'pipe' facility_type, network_id id, network_code code FROM app_user.pipe_network_base WHERE network_code LIKE 'GIS-TEST-%' AND network_type = '2' ORDER BY network_code;
SELECT 'user' facility_type, id, user_code code FROM app_user.water_user_info WHERE user_code LIKE 'GIS-TEST-%' ORDER BY user_code;

-- ============================================================
-- 清理脚本（测试完成后手动执行）
-- ============================================================
-- DELETE FROM app_user.water_source_info WHERE source_id LIKE 'GIS-TEST-%';
-- DELETE FROM app_user.water_plant_info WHERE plant_code LIKE 'GIS-TEST-%';
-- DELETE FROM app_user.pump_station WHERE station_code LIKE 'GIS-TEST-%' AND station_type = 'water_supply';
-- DELETE FROM app_user.pipe_network_base WHERE network_code LIKE 'GIS-TEST-%' AND network_type = '2';
-- DELETE FROM app_user.water_user_info WHERE user_code LIKE 'GIS-TEST-%';