package com.rongyichuang.player.repository;

import com.rongyichuang.player.entity.ActivityPlayer;
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接口
 */
@Repository
public interface ActivityPlayerRepository extends JpaRepository<ActivityPlayer, Long> {

    /**
     * 根据活动ID查找参赛选手
     */
    List<ActivityPlayer> findByActivityId(Long activityId);

    /**
     * 根据选手ID查找参赛记录
     */
    List<ActivityPlayer> findByPlayerId(Long playerId);

    /**
     * 根据活动ID和选手ID查找参赛记录
     */
    Optional<ActivityPlayer> findByActivityIdAndPlayerId(Long activityId, Long playerId);

    /**
     * 根据阶段ID查找参赛选手
     */
    List<ActivityPlayer> findByStageId(Long stageId);

    /**
     * 根据区域ID查找参赛选手
     */
    List<ActivityPlayer> findByRegionId(Long regionId);

    /**
     * 根据活动ID和区域ID查找参赛选手
     */
    List<ActivityPlayer> findByActivityIdAndRegionId(Long activityId, Long regionId);

    /**
     * 根据状态查找参赛选手
     */
    List<ActivityPlayer> findByState(Integer state);

    /**
     * 查找活动的参赛选手数量
     */
    @Query("SELECT COUNT(ap) FROM ActivityPlayer ap WHERE ap.activityId = :activityId AND ap.state = 1")
    Long countByActivityId(@Param("activityId") Long activityId);

    /**
     * 查找区域的参赛选手数量
     */
    @Query("SELECT COUNT(ap) FROM ActivityPlayer ap WHERE ap.regionId = :regionId AND ap.state = 1")
    Long countByRegionId(@Param("regionId") Long regionId);

    /**
     * 根据活动ID查找参赛选手，包含选手和区域信息
     */
    @Query("SELECT ap FROM ActivityPlayer ap " +
           "LEFT JOIN FETCH ap.player p " +
           "LEFT JOIN FETCH ap.region r " +
           "WHERE ap.activityId = :activityId AND ap.state = 1 " +
           "ORDER BY ap.createTime DESC")
    List<ActivityPlayer> findByActivityIdWithPlayerAndRegion(@Param("activityId") Long activityId);

    /**
     * 根据区域统计各活动的参赛人数
     */
    @Query("SELECT ap.activityId, ap.regionId, COUNT(ap) " +
           "FROM ActivityPlayer ap " +
           "WHERE ap.state = 1 " +
           "GROUP BY ap.activityId, ap.regionId")
    List<Object[]> countByActivityAndRegion();

    /**
     * 查找排名前N的选手
     */
    @Query("SELECT ap FROM ActivityPlayer ap " +
           "WHERE ap.activityId = :activityId AND ap.state = 1 AND ap.rank IS NOT NULL " +
           "ORDER BY ap.rank ASC")
    List<ActivityPlayer> findTopRankedPlayers(@Param("activityId") Long activityId);

    /**
     * 根据状态统计报名数量
     */
    long countByState(Integer state);
}