-- ============================================================
-- 供水管网统计分析 / GIS 一张图接口测试数据
-- KingbaseES / PostgreSQL 兼容；目标 schema：app_user
--
-- 用法：
--   1. 先执行 water_supply_facility_gis.sql，确保坐标列已存在。
--   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.water_pump_station
 WHERE station_code LIKE 'GIS-TEST-%';
DELETE FROM app_user.water_pipe_info
 WHERE pipe_code LIKE 'GIS-TEST-%';
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 个缺失坐标
-- ------------------------------------------------------------
INSERT INTO app_user.water_pump_station
    (station_code, station_name, "location", 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号', 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号', 2, 600.00,
     28.00, 180.00, DATE '2021-09-01', NULL, '测试站长2', '13800000004',
     'GIS接口测试-缺失坐标-概览计数', '0', 'test', CURRENT_TIMESTAMP, NULL, NULL);

-- ------------------------------------------------------------
-- 4. 管网：3 条坐标完整 + 1 条缺失坐标
--    其中包含 laying_year NULL，验证“未填报”分组。
-- ------------------------------------------------------------
INSERT INTO app_user.water_pipe_info
    (pipe_code, pipe_name, pipe_material, pipe_diameter, pipe_length,
     start_point, end_point, laying_year, pressure_rating, status,
     remark, del_flag, create_by, create_time,
     start_longitude, start_latitude, end_longitude, end_latitude)
VALUES
    ('GIS-TEST-PIPE-001', 'GIS测试管网-东湖主干线', '球墨铸铁', 800, 12500.50,
     '东湖水源地', '一厂', 2012, 1.60, '0',
     'GIS接口测试-完整LineString-大管径', '0', 'test', CURRENT_TIMESTAMP,
     113.28000000, 23.12000000, 113.30000000, 23.14000000),
    ('GIS-TEST-PIPE-002', 'GIS测试管网-北河支线', '钢管', 250, 6800.00,
     '北河水源地', '东区泵站', 2021, 1.00, '1',
     'GIS接口测试-完整LineString-异常', '0', 'test', CURRENT_TIMESTAMP,
     113.29000000, 23.13000000, 113.31000000, 23.15000000),
    ('GIS-TEST-PIPE-003', 'GIS测试管网-待补年代线', NULL, 80, 900.00,
     '东区泵站', '测试用户1', NULL, NULL, NULL,
     'GIS接口测试-完整LineString-空分组字段', '0', 'test', CURRENT_TIMESTAMP,
     113.31000000, 23.15000000, 113.32000000, 23.16000000),
    ('GIS-TEST-PIPE-004', 'GIS测试管网-缺失坐标线', 'PVC', 300, 1500.00,
     '测试用户1', '测试用户2', 2018, 0.80, '0',
     'GIS接口测试-缺失终点坐标-不应进入图层', '0', 'test', CURRENT_TIMESTAMP,
     113.32000000, 23.16000000, NULL, NULL);

-- ------------------------------------------------------------
-- 5. 大用水户：2 个坐标完整 + 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, NULL, NULL,
     'GIS接口测试-缺失坐标-概览计数', '0', 'test', CURRENT_TIMESTAMP, NULL, NULL);

COMMIT;

-- ------------------------------------------------------------
-- 6. 验证输出
-- ------------------------------------------------------------
-- 预期：source=3/完整2，plant=2/完整1，pumpStation=2/完整1，pipe=4/完整3，user=3/完整2。
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.water_pump_station WHERE station_code LIKE 'GIS-TEST-%'
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.water_pipe_info WHERE pipe_code LIKE 'GIS-TEST-%'
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.water_pump_station WHERE station_code LIKE 'GIS-TEST-%' ORDER BY station_code;
SELECT 'pipe' facility_type, id, pipe_code code FROM app_user.water_pipe_info WHERE pipe_code LIKE 'GIS-TEST-%' ORDER BY pipe_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.water_pump_station WHERE station_code LIKE 'GIS-TEST-%';
-- DELETE FROM app_user.water_pipe_info WHERE pipe_code LIKE 'GIS-TEST-%';
-- DELETE FROM app_user.water_user_info WHERE user_code LIKE 'GIS-TEST-%';