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

import com.qaup.collision.common.model.spatial.VehicleLocation;
import org.locationtech.jts.geom.Point;
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.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

/**
 * 车辆位置Repository接口 - 基于PostGIS空间查询
 * 提供车辆位置的CRUD操作和空间查询功能
 */
@Repository
public interface VehicleLocationRepository extends JpaRepository<VehicleLocation, Long> {

    /**
     * 根据车辆ID查找最新位置记录
     */
    @Query(value = "SELECT * FROM vehicle_locations vl WHERE vl.vehicle_id = :vehicleId " +
                   "ORDER BY vl.timestamp DESC LIMIT 1", 
           nativeQuery = true)
    Optional<VehicleLocation> findLatestByVehicleId(@Param("vehicleId") Long vehicleId);

    /**
     * 根据车牌号查找最新位置记录（通过关联查询）
     */
    @Query(value = "SELECT vl.* FROM vehicle_locations vl " +
                   "JOIN sys_vehicle_info vi ON vl.vehicle_id = vi.vehicle_id " +
                   "WHERE vi.license_plate = :licensePlate " +
                   "ORDER BY vl.timestamp DESC LIMIT 1", 
           nativeQuery = true)
    Optional<VehicleLocation> findLatestByLicensePlate(@Param("licensePlate") String licensePlate);

    /**
     * 根据车辆类型查找活跃车辆（通过关联查询）
     * 注意：现在使用type_code进行关联查询，支持路径编码模式
     */
    @Query(value = "SELECT vl.* FROM vehicle_locations vl " +
                   "JOIN sys_vehicle_info vi ON vl.vehicle_id = vi.vehicle_id " +
                   "JOIN sys_vehicle_type vt ON vi.type_code = vt.type_code " +
                   "WHERE vt.type_name LIKE :typeName " +
                   "AND vl.timestamp >= :since " +
                   "ORDER BY vl.timestamp DESC", 
           nativeQuery = true)
    List<VehicleLocation> findActiveByVehicleType(@Param("typeName") String typeName, 
                                                  @Param("since") LocalDateTime since);

    /**
     * 空间查询：查找指定距离范围内的车辆
     * 使用PostGIS ST_DWithin函数进行空间距离查询
     */
    @Query(value = "SELECT * FROM vehicle_locations vl " +
                   "WHERE ST_DWithin(vl.location, :centerPoint, :radiusMeters) " +
                   "AND vl.timestamp >= :since " +
                   "ORDER BY ST_Distance(vl.location, :centerPoint)", 
           nativeQuery = true)
    List<VehicleLocation> findVehiclesWithinRadius(@Param("centerPoint") Point centerPoint,
                                                   @Param("radiusMeters") double radiusMeters,
                                                   @Param("since") LocalDateTime since);

    /**
     * 空间查询：查找指定区域内的车辆
     * 使用PostGIS ST_Contains函数检查点是否在多边形内
     */
    @Query(value = "SELECT * FROM vehicle_locations vl " +
                   "WHERE ST_Contains(ST_GeomFromText(:areaWkt, 4326), vl.location) " +
                   "AND vl.timestamp >= :since " +
                   "ORDER BY vl.timestamp DESC", 
           nativeQuery = true)
    List<VehicleLocation> findVehiclesInArea(@Param("areaWkt") String areaWkt,
                                            @Param("since") LocalDateTime since);

    /**
     * 根据车辆ID和时间范围查询轨迹数据
     */
    @Query("SELECT vl FROM VehicleLocation vl WHERE vl.vehicleId = :vehicleId " +
           "AND vl.timestamp BETWEEN :startTime AND :endTime " +
           "ORDER BY vl.timestamp")
    List<VehicleLocation> findVehicleTrajectory(@Param("vehicleId") Long vehicleId,
                                               @Param("startTime") LocalDateTime startTime,
                                               @Param("endTime") LocalDateTime endTime);

    /**
     * 根据车牌号和时间范围查询轨迹数据（通过关联查询）
     */
    @Query(value = "SELECT vl.* FROM vehicle_locations vl " +
                   "JOIN sys_vehicle_info vi ON vl.vehicle_id = vi.vehicle_id " +
                   "WHERE vi.license_plate = :licensePlate " +
                   "AND vl.timestamp BETWEEN :startTime AND :endTime " +
                   "ORDER BY vl.timestamp", 
           nativeQuery = true)
    List<VehicleLocation> findVehicleTrajectoryByLicensePlate(@Param("licensePlate") String licensePlate,
                                                             @Param("startTime") LocalDateTime startTime,
                                                             @Param("endTime") LocalDateTime endTime);

    /**
     * 查找指定高度范围内的车辆
     */
    @Query("SELECT vl FROM VehicleLocation vl WHERE vl.altitude BETWEEN :minAltitude AND :maxAltitude " +
           "AND vl.timestamp >= :since ORDER BY vl.altitude")
    List<VehicleLocation> findVehiclesByAltitudeRange(@Param("minAltitude") Double minAltitude,
                                                      @Param("maxAltitude") Double maxAltitude,
                                                      @Param("since") LocalDateTime since);

    /**
     * 查找移动速度超过阈值的车辆
     */
    @Query("SELECT vl FROM VehicleLocation vl WHERE vl.speed > :speedThreshold " +
           "AND vl.timestamp >= :since ORDER BY vl.speed DESC")
    List<VehicleLocation> findHighSpeedVehicles(@Param("speedThreshold") Double speedThreshold,
                                               @Param("since") LocalDateTime since);

    /**
     * 删除指定时间之前的历史数据
     */
    @Query("DELETE FROM VehicleLocation vl WHERE vl.timestamp < :beforeTime")
    int deleteHistoricalData(@Param("beforeTime") LocalDateTime beforeTime);

    /**
     * 统计指定时间段内的车辆数量
     */
    @Query("SELECT COUNT(DISTINCT vl.vehicleId) FROM VehicleLocation vl " +
           "WHERE vl.timestamp BETWEEN :startTime AND :endTime")
    long countUniqueVehiclesInTimeRange(@Param("startTime") LocalDateTime startTime,
                                       @Param("endTime") LocalDateTime endTime);

    /**
     * 空间聚合查询：查找与指定点最近的N个车辆
     */
    @Query(value = "SELECT * FROM vehicle_locations vl " +
                   "WHERE vl.timestamp >= :since " +
                   "ORDER BY ST_Distance(vl.location, :referencePoint) " +
                   "LIMIT :limit", 
           nativeQuery = true)
    List<VehicleLocation> findNearestVehicles(@Param("referencePoint") Point referencePoint,
                                             @Param("since") LocalDateTime since,
                                             @Param("limit") int limit);
} 