package com.qaup.collision.pathconflict.repository;

import com.qaup.collision.pathconflict.model.entity.TransportRoute;
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
 * 
 * @author AI Assistant
 * @version 1.0
 * @since 2025-01-17
 */
@Repository
public interface TransportRouteRepository extends JpaRepository<TransportRoute, Long> {
    
    /**
     * 根据路径类型和状态查找路径
     */
    List<TransportRoute> findByRouteTypeAndStatus(
        TransportRoute.RouteType routeType, 
        TransportRoute.RouteStatus status);
    
    /**
     * 查找指定范围内的路径
     */
    @Query(value = "SELECT * FROM transport_routes r WHERE " +
           "ST_DWithin(r.route_geometry, ST_GeomFromText(:point, 4326), :radiusMeters) " +
           "AND r.status = :status", nativeQuery = true)
    List<TransportRoute> findRoutesWithinRadius(
        @Param("point") String pointWKT,
        @Param("radiusMeters") double radiusMeters,
        @Param("status") String status);
    
    /**
     * 查找与指定路径相交的路径
     */
    @Query(value = "SELECT r2.* FROM transport_routes r1, transport_routes r2 WHERE " +
           "r1.id = :routeId AND r2.id != :routeId " +
           "AND ST_Intersects(r1.route_geometry, r2.route_geometry) " +
           "AND r2.status = 'ACTIVE'", nativeQuery = true)
    List<TransportRoute> findIntersectingRoutes(@Param("routeId") Long routeId);
    
    /**
     * 查找最近的路径
     */
    @Query(value = "SELECT *, ST_Distance(route_geometry, ST_GeomFromText(:point, 4326)) as distance " +
           "FROM transport_routes WHERE route_type = :routeType AND status = :status " +
           "ORDER BY distance LIMIT :limit", nativeQuery = true)
    List<TransportRoute> findNearestRoutes(
        @Param("point") String pointWKT,
        @Param("routeType") String routeType,
        @Param("status") String status,
        @Param("limit") int limit);
    
    /**
     * 根据路径名称和类型查找路径
     */
    Optional<TransportRoute> findByRouteNameAndRouteType(String routeName, TransportRoute.RouteType routeType);
} 