QDAirPortBackend0122/sql/geofence_validation.sql

-- ================================================================
-- 电子围栏功能验证脚本
-- 用于验证无人车在测试区域内的电子围栏检测
-- ================================================================

-- 1. 查看测试区域信息
SELECT 
    area_id,
    name,
    type,
    enabled,
    priority,
    functional_category,
    ST_AsText(boundary) as boundary_wkt,
    ST_Area(boundary::geography) as area_square_meters
FROM airport_areas 
WHERE area_id IN ('UV_TEST_AREA_A', 'UV_TEST_AREA_B', 'UV_INTERSECTION_ZONE')
ORDER BY priority DESC;

-- 2. 查看测试规则信息
SELECT 
    r.rule_id,
    r.rule_name,
    r.rule_category,
    r.status,
    r.spatial_object_id,
    r.alert_level,
    r.rule_parameters,
    array_agg(vt.vehicle_type) as allowed_vehicle_types
FROM spatial_rules r
LEFT JOIN spatial_rule_vehicle_types vt ON r.rule_id = vt.rule_id
WHERE r.rule_id LIKE 'GEOFENCE_%'
GROUP BY r.rule_id, r.rule_name, r.rule_category, r.status, r.spatial_object_id, r.alert_level, r.rule_parameters
ORDER BY r.rule_id;

-- 3. 模拟无人车A在起点位置的检测
-- 无人车A起点：经度120.083084，纬度36.369696
WITH unmanned_vehicle_a AS (
    SELECT 
        'UV_A_鲁B567' as vehicle_id,
        ST_SetSRID(ST_MakePoint(120.083084, 36.369696), 4326) as current_position
),
containing_areas AS (
    SELECT 
        uv.vehicle_id,
        aa.area_id,
        aa.name as area_name,
        aa.type,
        aa.enabled,
        ST_Contains(aa.boundary, uv.current_position) as is_inside
    FROM unmanned_vehicle_a uv
    CROSS JOIN airport_areas aa
    WHERE ST_Contains(aa.boundary, uv.current_position)
)
SELECT 
    ca.*,
    sr.rule_id,
    sr.rule_name,
    sr.alert_level,
    sr.rule_parameters
FROM containing_areas ca
LEFT JOIN spatial_rules sr ON ca.area_id = sr.spatial_object_id 
    AND sr.status = 'ACTIVE'
    AND sr.rule_category = 'ACCESS_CONTROL';

-- 4. 模拟无人车B在起点位置的检测  
-- 无人车B起点：经度120.086965，纬度36.368599
WITH unmanned_vehicle_b AS (
    SELECT 
        'UV_B_鲁B579' as vehicle_id,
        ST_SetSRID(ST_MakePoint(120.086965, 36.368599), 4326) as current_position
),
containing_areas AS (
    SELECT 
        uv.vehicle_id,
        aa.area_id,
        aa.name as area_name,
        aa.type,
        aa.enabled,
        ST_Contains(aa.boundary, uv.current_position) as is_inside
    FROM unmanned_vehicle_b uv
    CROSS JOIN airport_areas aa
    WHERE ST_Contains(aa.boundary, uv.current_position)
)
SELECT 
    ca.*,
    sr.rule_id,
    sr.rule_name,
    sr.alert_level,
    sr.rule_parameters
FROM containing_areas ca
LEFT JOIN spatial_rules sr ON ca.area_id = sr.spatial_object_id 
    AND sr.status = 'ACTIVE'
    AND sr.rule_category = 'ACCESS_CONTROL';

-- 5. 模拟无人车在交汇区域中心的检测
-- 交汇区域中心：经度120.085000，纬度36.368000
WITH unmanned_vehicle_center AS (
    SELECT 
        'UV_CENTER_TEST' as vehicle_id,
        ST_SetSRID(ST_MakePoint(120.085000, 36.368000), 4326) as current_position
),
containing_areas AS (
    SELECT 
        uv.vehicle_id,
        aa.area_id,
        aa.name as area_name,
        aa.type,
        aa.enabled,
        ST_Contains(aa.boundary, uv.current_position) as is_inside
    FROM unmanned_vehicle_center uv
    CROSS JOIN airport_areas aa
    WHERE ST_Contains(aa.boundary, uv.current_position)
)
SELECT 
    ca.*,
    sr.rule_id,
    sr.rule_name,
    sr.alert_level,
    sr.rule_parameters
FROM containing_areas ca
LEFT JOIN spatial_rules sr ON ca.area_id = sr.spatial_object_id 
    AND sr.status = 'ACTIVE'
    AND sr.rule_category = 'ACCESS_CONTROL'
ORDER BY sr.rule_id;

-- 6. 测试禁用区域的检测
-- 临时禁用无人车A区域
UPDATE airport_areas 
SET enabled = false, updated_at = CURRENT_TIMESTAMP
WHERE area_id = 'UV_TEST_AREA_A';

-- 重新测试无人车A位置检测（应该返回拒绝访问）
WITH unmanned_vehicle_a_disabled AS (
    SELECT 
        'UV_A_DISABLED_TEST' as vehicle_id,
        ST_SetSRID(ST_MakePoint(120.083084, 36.369696), 4326) as current_position
),
containing_areas AS (
    SELECT 
        uv.vehicle_id,
        aa.area_id,
        aa.name as area_name,
        aa.type,
        aa.enabled,
        ST_Contains(aa.boundary, uv.current_position) as is_inside,
        CASE 
            WHEN aa.enabled = false THEN 'DENIED - Area Disabled'
            ELSE 'ALLOWED'
        END as access_result
    FROM unmanned_vehicle_a_disabled uv
    CROSS JOIN airport_areas aa
    WHERE ST_Contains(aa.boundary, uv.current_position)
)
SELECT * FROM containing_areas;

-- 恢复区域启用状态
UPDATE airport_areas 
SET enabled = true, updated_at = CURRENT_TIMESTAMP
WHERE area_id = 'UV_TEST_AREA_A';

-- 7. 查看所有测试数据汇总
SELECT 
    'Areas Count' as metric,
    COUNT(*) as value
FROM airport_areas 
WHERE area_id LIKE 'UV_%'

UNION ALL

SELECT 
    'Rules Count' as metric,
    COUNT(*) as value
FROM spatial_rules 
WHERE rule_id LIKE 'GEOFENCE_%'

UNION ALL

SELECT 
    'Vehicle Types Count' as metric,
    COUNT(*) as value
FROM spatial_rule_vehicle_types 
WHERE rule_id LIKE 'GEOFENCE_%';

-- 完成验证
SELECT 'Geofence validation completed successfully!' as status;