package com.qaup.collision.common.model.repository;

import com.qaup.collision.common.model.spatial.AirportArea;

import org.locationtech.jts.geom.Point;
import org.locationtech.jts.geom.Geometry;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Optional;

/**
 * 机场区域Repository接口 - 基于PostGIS空间查询
 * 提供机场区域的CRUD操作和空间查询功能
 */
@Repository
public interface AirportAreaRepository extends JpaRepository<AirportArea, Long> {

    /**
     * 根据区域ID查找
     */
    Optional<AirportArea> findByAreaId(@Param("areaId") String areaId);

    /**
     * 根据区域名称查找
     */
    Optional<AirportArea> findByName(@Param("name") String name);

    /**
     * 根据区域类型查找所有活跃区域
     */
    @Query("SELECT aa FROM AirportArea aa WHERE aa.type = :areaType AND aa.enabled = true " +
           "ORDER BY aa.priority DESC")
    List<AirportArea> findActiveAreasByType(@Param("areaType") String areaType);

    /**
     * 空间查询：查找包含指定点的所有区域
     * 使用PostGIS ST_Contains函数
     */
    @Query(value = "SELECT * FROM airport_areas aa " +
                   "WHERE ST_Contains(aa.boundary, :point) " +
                   "AND aa.enabled = true " +
                   "ORDER BY aa.priority DESC", 
           nativeQuery = true)
    List<AirportArea> findAreasContainingPoint(@Param("point") Point point);

    /**
     * 空间查询：查找与指定几何形状相交的区域
     * 使用PostGIS ST_Intersects函数
     */
    @Query(value = "SELECT * FROM airport_areas aa " +
                   "WHERE ST_Intersects(aa.boundary, :geometry) " +
                   "AND aa.enabled = true " +
                   "ORDER BY aa.priority DESC", 
           nativeQuery = true)
    List<AirportArea> findAreasIntersectingGeometry(@Param("geometry") Geometry geometry);

    /**
     * 空间查询：查找指定距离范围内的区域
     * 使用PostGIS ST_DWithin函数
     */
    @Query(value = "SELECT * FROM airport_areas aa " +
                   "WHERE ST_DWithin(aa.boundary, :centerPoint, :radiusMeters) " +
                   "AND aa.enabled = true " +
                   "ORDER BY ST_Distance(aa.boundary, :centerPoint)", 
           nativeQuery = true)
    List<AirportArea> findAreasWithinRadius(@Param("centerPoint") Point centerPoint,
                                           @Param("radiusMeters") double radiusMeters);

    /**
     * 空间查询：查找与指定区域重叠的其他区域
     * 用于检测区域冲突
     */
    @Query(value = "SELECT * FROM airport_areas aa " +
                   "WHERE aa.id != :excludeId " +
                   "AND ST_Overlaps(aa.boundary, :geometry) " +
                   "AND aa.enabled = true", 
           nativeQuery = true)
    List<AirportArea> findOverlappingAreas(@Param("geometry") Geometry geometry,
                                          @Param("excludeId") Long excludeId);

    /**
     * 根据优先级范围查找区域
     */
    @Query("SELECT aa FROM AirportArea aa WHERE aa.priority BETWEEN :minPriority AND :maxPriority " +
           "AND aa.enabled = true ORDER BY aa.priority DESC")
    List<AirportArea> findAreasByPriorityRange(@Param("minPriority") Integer minPriority,
                                              @Param("maxPriority") Integer maxPriority);

    /**
     * 查找包含特定限制类型的区域
     * 使用JSONB查询功能 - 使用jsonb_exists函数避免参数冲突
     */
    @Query(value = "SELECT * FROM airport_areas aa " +
                   "WHERE jsonb_exists(aa.allowed_vehicle_types, :restrictionType) " +
                   "AND aa.enabled = true " +
                   "ORDER BY aa.priority DESC", 
           nativeQuery = true)
    List<AirportArea> findAreasByRestrictionType(@Param("restrictionType") String restrictionType);

    /**
     * 空间聚合查询：计算区域面积
     * 使用PostGIS ST_Area函数
     */
    @Query(value = "SELECT aa.*, ST_Area(aa.boundary) as area " +
                   "FROM airport_areas aa " +
                   "WHERE aa.type = :areaType " +
                   "AND aa.enabled = true " +
                   "ORDER BY ST_Area(aa.boundary) DESC", 
           nativeQuery = true)
    List<Object[]> findAreasByTypeWithSize(@Param("areaType") String areaType);

    /**
     * 查找最高优先级的包含指定点的区域
     * 处理重叠区域时使用
     */
    @Query(value = "SELECT * FROM airport_areas aa " +
                   "WHERE ST_Contains(aa.boundary, :point) " +
                   "AND aa.enabled = true " +
                   "ORDER BY aa.priority DESC " +
                   "LIMIT 1", 
           nativeQuery = true)
    Optional<AirportArea> findHighestPriorityAreaContainingPoint(@Param("point") Point point);

    /**
     * 空间查询：获取区域的边界框
     * 使用PostGIS ST_Envelope函数
     */
    @Query(value = "SELECT ST_Envelope(aa.boundary) " +
                   "FROM airport_areas aa " +
                   "WHERE aa.id = :areaId", 
           nativeQuery = true)
    Optional<Geometry> getAreaBoundingBox(@Param("areaId") Long areaId);

    /**
     * 查找与车辆轨迹相交的所有区域
     * 用于轨迹分析
     */
    @Query(value = "SELECT aa.* FROM airport_areas aa " +
                   "WHERE ST_Intersects(aa.boundary, ST_GeomFromText(:trajectoryWkt, 4326)) " +
                   "AND aa.enabled = true " +
                   "ORDER BY aa.priority DESC", 
           nativeQuery = true)
    List<AirportArea> findAreasIntersectingTrajectory(@Param("trajectoryWkt") String trajectoryWkt);

    /**
     * 统计活跃区域数量（按类型分组）
     */
    @Query("SELECT aa.type, COUNT(aa) FROM AirportArea aa " +
           "WHERE aa.enabled = true GROUP BY aa.type")
    List<Object[]> countActiveAreasByType();

    /**
     * 查找指定版本的区域配置
     * 用于配置版本管理
     */
    @Query("SELECT aa FROM AirportArea aa WHERE aa.version = :version " +
           "ORDER BY aa.priority DESC")
    List<AirportArea> findAreasByVersion(@Param("version") Long version);

    /**
     * 空间查询：查找距离指定点最近的N个区域
     */
    @Query(value = "SELECT * FROM airport_areas aa " +
                   "WHERE aa.enabled = true " +
                   "ORDER BY ST_Distance(aa.boundary, :referencePoint) " +
                   "LIMIT :limit", 
           nativeQuery = true)
    List<AirportArea> findNearestAreas(@Param("referencePoint") Point referencePoint,
                                      @Param("limit") int limit);

    /**
     * 根据区域类型统计数量
     */
    @Query("SELECT COUNT(aa) FROM AirportArea aa WHERE aa.type = :type")
    long countByType(@Param("type") String type);

    /**
     * 根据启用状态统计数量
     */
    @Query("SELECT COUNT(aa) FROM AirportArea aa WHERE aa.enabled = :enabled")
    long countByEnabled(@Param("enabled") boolean enabled);
} 