AIyaodianzhushou/backend-java/src/main/java/com/pharmacopoeia/repository/UserEventRepository.java

package com.pharmacopoeia.repository;

import com.pharmacopoeia.entity.UserEvent;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.Instant;
import java.util.List;

@Repository
public interface UserEventRepository extends JpaRepository<UserEvent, Long>,
        JpaSpecificationExecutor<UserEvent> {

    /** 总数 */
    @Query("SELECT COUNT(e) FROM UserEvent e " +
           "WHERE e.createdAt >= :startTime AND e.createdAt < :endTime")
    long countTotal(@Param("startTime") Instant startTime, @Param("endTime") Instant endTime);

    /** 去重活跃用户数 */
    @Query("SELECT COUNT(DISTINCT e.userKey) FROM UserEvent e " +
           "WHERE e.createdAt >= :startTime AND e.createdAt < :endTime")
    long countDistinctUsers(@Param("startTime") Instant startTime, @Param("endTime") Instant endTime);

    /** 去重 IP 数 */
    @Query("SELECT COUNT(DISTINCT e.ip) FROM UserEvent e " +
           "WHERE e.createdAt >= :startTime AND e.createdAt < :endTime")
    long countDistinctIps(@Param("startTime") Instant startTime, @Param("endTime") Instant endTime);

    /** 按类型 count */
    @Query("SELECT COUNT(e) FROM UserEvent e " +
           "WHERE e.eventType = :eventType " +
           "AND e.createdAt >= :startTime AND e.createdAt < :endTime")
    long countByType(@Param("eventType") String eventType,
                     @Param("startTime") Instant startTime, @Param("endTime") Instant endTime);

    // ==================== 分组统计（全量，供其他场景用） ====================

    @Query("SELECT e.ip as key, COUNT(e) as cnt FROM UserEvent e " +
           "WHERE e.createdAt >= :startTime AND e.createdAt < :endTime " +
           "GROUP BY e.ip ORDER BY cnt DESC")
    List<Object[]> countByIp(@Param("startTime") Instant startTime, @Param("endTime") Instant endTime);

    @Query("SELECT e.pageUrl as key, COUNT(e) as cnt FROM UserEvent e " +
           "WHERE e.createdAt >= :startTime AND e.createdAt < :endTime " +
           "GROUP BY e.pageUrl ORDER BY cnt DESC")
    List<Object[]> countByPageUrl(@Param("startTime") Instant startTime, @Param("endTime") Instant endTime);

    @Query("SELECT e.eventType as key, COUNT(e) as cnt FROM UserEvent e " +
           "WHERE e.createdAt >= :startTime AND e.createdAt < :endTime " +
           "GROUP BY e.eventType ORDER BY cnt DESC")
    List<Object[]> countByEventType(@Param("startTime") Instant startTime, @Param("endTime") Instant endTime);

    @Query("SELECT e.userKey as key, COUNT(e) as cnt FROM UserEvent e " +
           "WHERE e.createdAt >= :startTime AND e.createdAt < :endTime " +
           "GROUP BY e.userKey ORDER BY cnt DESC")
    List<Object[]> countByUserKey(@Param("startTime") Instant startTime, @Param("endTime") Instant endTime);

    @Query("SELECT e.userKey, e.ip, COUNT(e) as cnt FROM UserEvent e " +
           "WHERE e.createdAt >= :startTime AND e.createdAt < :endTime " +
           "GROUP BY e.userKey, e.ip ORDER BY e.userKey, cnt DESC")
    List<Object[]> countByUserIp(@Param("startTime") Instant startTime, @Param("endTime") Instant endTime);

    // ==================== 分组统计（分页） ====================

    @Query(value = "SELECT e.ip as key, COUNT(e) as cnt FROM UserEvent e " +
           "WHERE e.createdAt >= :startTime AND e.createdAt < :endTime " +
           "GROUP BY e.ip ORDER BY cnt DESC",
           countQuery = "SELECT COUNT(DISTINCT e.ip) FROM UserEvent e " +
           "WHERE e.createdAt >= :startTime AND e.createdAt < :endTime")
    Page<Object[]> countByIpPaged(@Param("startTime") Instant startTime,
                                   @Param("endTime") Instant endTime, Pageable pageable);

    @Query(value = "SELECT e.pageUrl as key, COUNT(e) as cnt FROM UserEvent e " +
           "WHERE e.createdAt >= :startTime AND e.createdAt < :endTime " +
           "GROUP BY e.pageUrl ORDER BY cnt DESC",
           countQuery = "SELECT COUNT(DISTINCT e.pageUrl) FROM UserEvent e " +
           "WHERE e.createdAt >= :startTime AND e.createdAt < :endTime")
    Page<Object[]> countByPageUrlPaged(@Param("startTime") Instant startTime,
                                        @Param("endTime") Instant endTime, Pageable pageable);

    @Query(value = "SELECT e.eventType as key, COUNT(e) as cnt FROM UserEvent e " +
           "WHERE e.createdAt >= :startTime AND e.createdAt < :endTime " +
           "GROUP BY e.eventType ORDER BY cnt DESC",
           countQuery = "SELECT COUNT(DISTINCT e.eventType) FROM UserEvent e " +
           "WHERE e.createdAt >= :startTime AND e.createdAt < :endTime")
    Page<Object[]> countByEventTypePaged(@Param("startTime") Instant startTime,
                                          @Param("endTime") Instant endTime, Pageable pageable);

    @Query(value = "SELECT e.userKey as key, COUNT(e) as cnt FROM UserEvent e " +
           "WHERE e.createdAt >= :startTime AND e.createdAt < :endTime " +
           "GROUP BY e.userKey ORDER BY cnt DESC",
           countQuery = "SELECT COUNT(DISTINCT e.userKey) FROM UserEvent e " +
           "WHERE e.createdAt >= :startTime AND e.createdAt < :endTime")
    Page<Object[]> countByUserKeyPaged(@Param("startTime") Instant startTime,
                                        @Param("endTime") Instant endTime, Pageable pageable);

    @Query(value = "SELECT e.userKey, e.ip, COUNT(e) as cnt FROM UserEvent e " +
           "WHERE e.createdAt >= :startTime AND e.createdAt < :endTime " +
           "GROUP BY e.userKey, e.ip ORDER BY e.userKey, cnt DESC",
           countQuery = "SELECT COUNT(*) FROM (" +
               "SELECT e2.userKey AS userKey, e2.ip AS ip FROM UserEvent e2 " +
               "WHERE e2.createdAt >= :startTime AND e2.createdAt < :endTime " +
               "GROUP BY e2.userKey, e2.ip) sub")
    Page<Object[]> countByUserIpPaged(@Param("startTime") Instant startTime,
                                       @Param("endTime") Instant endTime, Pageable pageable);

    /** 获取所有去重事件类型，供前端筛选 */
    @Query("SELECT DISTINCT e.eventType FROM UserEvent e ORDER BY e.eventType")
    List<String> findDistinctEventTypes();
}