package com.qaup.collision.rule.repository;

import com.qaup.collision.rule.event.RuleViolationEvent;

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;

/**
 * 规则违规事件数据访问接口 - 简化版本
 * 专门针对简化的RuleViolationEvent实体设计
 * 
 * @author AI Assistant
 * @version 2.0 - 简化版本
 * @since 2025-01-17
 */
@Repository
public interface RuleViolationEventRepository extends JpaRepository<RuleViolationEvent, String> {

    /**
     * 根据车牌号查询违规事件（通过关联sys_vehicle_info表）
     * @param vehicleLicense 车牌号
     * @return 指定车牌的违规事件列表
     */
    @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> findByVehicleLicense(@Param("licensePlate") String vehicleLicense);

    /**
     * 根据车辆ID查询违规事件
     * @param vehicleId 车辆ID
     * @return 指定车辆ID的违规事件列表
     */
    List<RuleViolationEvent> findByVehicleId(Long vehicleId);

    /**
     * 根据规则名称查询违规事件
     * @param ruleName 规则名称
     * @return 指定规则的违规事件列表
     */
    List<RuleViolationEvent> findByRuleName(String ruleName);

    /**
     * 根据违规类型查询事件
     * @param violationType 违规类型
     * @return 指定类型的违规事件列表
     */
    List<RuleViolationEvent> findByViolationType(String violationType);

    /**
     * 查询指定时间范围内的违规事件
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @return 指定时间范围内的违规事件列表
     */
    List<RuleViolationEvent> findByViolationTimeBetween(LocalDateTime startTime, LocalDateTime endTime);

    /**
     * 查询指定车牌在时间范围内的违规事件（通过关联sys_vehicle_info表）
     * @param vehicleLicense 车牌号
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @return 指定车牌在时间范围内的违规事件列表
     */
    @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 " +
                   "AND e.violation_time BETWEEN :startTime AND :endTime " +
                   "ORDER BY e.violation_time DESC", 
           nativeQuery = true)
    List<RuleViolationEvent> findByVehicleLicenseAndViolationTimeBetween(@Param("licensePlate") String vehicleLicense, 
                                                                         @Param("startTime") LocalDateTime startTime, 
                                                                         @Param("endTime") LocalDateTime endTime);

    /**
     * 查询最近的违规事件（限制数量）
     * @param limit 限制数量
     * @return 最近的违规事件列表
     */
    @Query(value = "SELECT e.* FROM uv_violation_events e ORDER BY e.violation_time DESC LIMIT :limit",
           nativeQuery = true)
    List<RuleViolationEvent> findRecentEvents(@Param("limit") Integer limit);

    /**
     * 查询车辆违规统计信息（通过关联sys_vehicle_info表）
     * @param vehicleLicense 车牌号
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @return 各违规类型的数量统计
     */
    @Query(value = "SELECT e.violation_type, 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_time BETWEEN :startTime AND :endTime " +
                   "GROUP BY e.violation_type", 
           nativeQuery = true)
    List<Object[]> getVehicleViolationStats(@Param("licensePlate") String vehicleLicense,
                                            @Param("startTime") LocalDateTime startTime,
                                            @Param("endTime") LocalDateTime endTime);

    /**
     * 查询规则违规统计信息
     * @param ruleName 规则名称
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @return 违规数量统计
     */
    @Query("SELECT COUNT(e) FROM RuleViolationEvent e " +
           "WHERE e.ruleName = :ruleName " +
           "AND e.violationTime BETWEEN :startTime AND :endTime")
    Long getRuleViolationCount(@Param("ruleName") String ruleName,
                               @Param("startTime") LocalDateTime startTime,
                               @Param("endTime") LocalDateTime endTime);

    /**
     * 查询每日违规事件趋势
     * 修复Spring Boot 3.x/Hibernate 6兼容性问题
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @return 每日违规事件数量
     */
    @Query(value = "SELECT DATE_TRUNC('day', e.violation_time) as violation_date, COUNT(e.*) FROM uv_violation_events e " +
                   "WHERE e.violation_time BETWEEN :startTime AND :endTime " +
                   "GROUP BY DATE_TRUNC('day', e.violation_time) " +
                   "ORDER BY DATE_TRUNC('day', e.violation_time)",
           nativeQuery = true)
    List<Object[]> getDailyViolationTrend(@Param("startTime") LocalDateTime startTime,
                                          @Param("endTime") LocalDateTime endTime);

    /**
     * 查询违规类型分布统计
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @return 各违规类型的数量和百分比
     */
    @Query("SELECT e.violationType, COUNT(e) FROM RuleViolationEvent e " +
           "WHERE e.violationTime BETWEEN :startTime AND :endTime " +
           "GROUP BY e.violationType " +
           "ORDER BY COUNT(e) DESC")
    List<Object[]> getViolationTypeDistribution(@Param("startTime") LocalDateTime startTime,
                                                @Param("endTime") LocalDateTime endTime);

    /**
     * 查询特定车辆的重复违规（通过关联sys_vehicle_info表）
     * @param vehicleLicense 车牌号
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @param minCount 最小违规次数
     * @return 重复违规的规则名称和次数
     */
    @Query(value = "SELECT e.rule_name, 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_time BETWEEN :startTime AND :endTime " +
                   "GROUP BY e.rule_name " +
                   "HAVING COUNT(e.*) >= :minCount " +
                   "ORDER BY COUNT(e.*) DESC", 
           nativeQuery = true)
    List<Object[]> findRepeatedViolations(@Param("licensePlate") String vehicleLicense,
                                          @Param("startTime") LocalDateTime startTime,
                                          @Param("endTime") LocalDateTime endTime,
                                          @Param("minCount") Integer minCount);

    /**
     * 统计车辆的违规次数（通过关联sys_vehicle_info表）
     * @param vehicleLicense 车牌号
     * @return 违规次数
     */
    @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 vehicleLicense);

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

    /**
     * 统计今日违规事件数量
     * 修复Spring Boot 3.x/Hibernate 6兼容性问题
     * @param startOfDay 今日开始时间
     * @param endOfDay 今日结束时间
     * @return 今日违规数量
     */
    @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);

    /**
     * 根据车牌和违规类型统计次数（通过关联sys_vehicle_info表）
     * @param vehicleLicense 车牌号
     * @param violationType 违规类型
     * @return 违规次数
     */
    @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 vehicleLicense, 
                                              @Param("violationType") String violationType);

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