package com.qaup.collision.rule.repository;

import com.qaup.collision.rule.event.RuleViolationEvent;
import com.qaup.collision.rule.model.enums.RuleAlertLevel;

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;

/**
 * 无人车违规事件Repository
 * 专门针对无人车违规场景的数据访问接口
 */
@Repository
public interface SimpleRuleViolationEventRepository extends JpaRepository<RuleViolationEvent, Long> {

    /**
     * 根据车牌号查询违规事件（通过关联sys_vehicle_info表）
     */
    @Query(value = "SELECT e.* FROM uv_violation_events e " +
                   "JOIN sys_vehicle_info vi ON e.vehicle_id = vi.vehicle_id " +
                   "WHERE vi.license_plate = :licensePlate " +
                   "ORDER BY e.violation_time DESC", 
           nativeQuery = true)
    List<RuleViolationEvent> findByVehicleLicenseOrderByViolationTimeDesc(@Param("licensePlate") String licensePlate);

    /**
     * 根据车辆ID查询违规事件
     */
    List<RuleViolationEvent> findByVehicleIdOrderByViolationTimeDesc(Long vehicleId);

    /**
     * 根据规则名称查询违规事件
     */
    List<RuleViolationEvent> findByRuleNameOrderByViolationTimeDesc(String ruleName);

    /**
     * 根据违规类型查询事件
     */
    List<RuleViolationEvent> findByViolationTypeOrderByViolationTimeDesc(String violationType);

    /**
     * 根据告警级别查询事件
     */
    List<RuleViolationEvent> findByAlertLevelOrderByViolationTimeDesc(RuleAlertLevel alertLevel);

    /**
     * 查询指定时间段内的违规事件
     */
    @Query("SELECT e FROM RuleViolationEvent e WHERE e.violationTime BETWEEN :startTime AND :endTime ORDER BY e.violationTime DESC")
    List<RuleViolationEvent> findByTimeRange(@Param("startTime") LocalDateTime startTime, 
                                                   @Param("endTime") LocalDateTime endTime);

    /**
     * 查询超速违规事件
     */
    List<RuleViolationEvent> findByViolationTypeAndActualValueGreaterThanOrderByViolationTimeDesc(
            String violationType, Double speedLimit);

    /**
     * 统计车辆的违规次数（通过关联sys_vehicle_info表）
     */
    @Query(value = "SELECT COUNT(e.*) FROM uv_violation_events e " +
                   "JOIN sys_vehicle_info vi ON e.vehicle_id = vi.vehicle_id " +
                   "WHERE vi.license_plate = :licensePlate", 
           nativeQuery = true)
    Long countByVehicleLicense(@Param("licensePlate") String licensePlate);

    /**
     * 根据车辆ID统计违规次数
     */
    Long countByVehicleId(Long vehicleId);

    /**
     * 统计今日违规事件数量
     * 修复Spring Boot 3.x/Hibernate 6兼容性问题
     */
    @Query("SELECT COUNT(e) FROM RuleViolationEvent e WHERE e.violationTime >= :startOfDay AND e.violationTime < :endOfDay")
    Long countTodayViolations(@Param("startOfDay") LocalDateTime startOfDay, @Param("endOfDay") LocalDateTime endOfDay);

    /**
     * 查询最近的违规事件（用于实时监控）
     */
    @Query("SELECT e FROM RuleViolationEvent e WHERE e.violationTime >= :since ORDER BY e.violationTime DESC")
    List<RuleViolationEvent> findRecentViolations(@Param("since") LocalDateTime since);

    /**
     * 根据车牌和违规类型统计次数（通过关联sys_vehicle_info表）
     */
    @Query(value = "SELECT COUNT(e.*) FROM uv_violation_events e " +
                   "JOIN sys_vehicle_info vi ON e.vehicle_id = vi.vehicle_id " +
                   "WHERE vi.license_plate = :licensePlate AND e.violation_type = :violationType", 
           nativeQuery = true)
    Long countByVehicleLicenseAndViolationType(@Param("licensePlate") String licensePlate, 
                                              @Param("violationType") String violationType);

    /**
     * 根据车辆ID和违规类型统计次数
     */
    Long countByVehicleIdAndViolationType(Long vehicleId, String violationType);
} 