package com.example.demo.repository;

import com.example.demo.model.UserActivityStats;
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.LocalDate;
import java.util.List;
import java.util.Optional;

@Repository
public interface UserActivityStatsRepository extends JpaRepository<UserActivityStats, Long> {
    
    /**
     * 根据日期查找日活用户数
     */
    @Query("SELECT uas.dailyActiveUsers FROM UserActivityStats uas WHERE uas.activityDate = :date")
    Integer findDailyActiveUsersByDate(@Param("date") LocalDate date);
    
    /**
     * 获取指定年份的月度日活用户数据
     */
    @Query("SELECT MONTH(uas.activityDate) as month, " +
           "AVG(uas.dailyActiveUsers) as avgDailyActive, " +
           "MAX(uas.dailyActiveUsers) as maxDailyActive, " +
           "MIN(uas.dailyActiveUsers) as minDailyActive " +
           "FROM UserActivityStats uas " +
           "WHERE YEAR(uas.activityDate) = :year " +
           "GROUP BY MONTH(uas.activityDate) " +
           "ORDER BY MONTH(uas.activityDate)")
    List<java.util.Map<String, Object>> findMonthlyActiveUsers(@Param("year") int year);
    
    /**
     * 获取指定年份的每日日活用户数据
     */
    @Query("SELECT DAYOFYEAR(uas.activityDate) as dayOfYear, " +
           "WEEK(uas.activityDate) as weekOfYear, " +
           "uas.dailyActiveUsers as dailyActiveUsers, " +
           "uas.activityDate as activityDate " +
           "FROM UserActivityStats uas " +
           "WHERE YEAR(uas.activityDate) = :year " +
           "ORDER BY uas.activityDate")
    List<java.util.Map<String, Object>> findDailyActiveUsersByYear(@Param("year") int year);
    
    /**
     * 获取指定月份的平均日活用户数
     */
    @Query("SELECT AVG(uas.dailyActiveUsers) FROM UserActivityStats uas " +
           "WHERE YEAR(uas.activityDate) = :year AND MONTH(uas.activityDate) = :month")
    Double findAverageDailyActiveUsersByMonth(@Param("year") int year, @Param("month") int month);
    
    /**
     * 获取指定年份的平均日活用户数
     */
    @Query("SELECT AVG(uas.dailyActiveUsers) FROM UserActivityStats uas " +
           "WHERE YEAR(uas.activityDate) = :year")
    Double findAverageDailyActiveUsersByYear(@Param("year") int year);
    
    /**
     * 获取最新的统计数据
     */
    Optional<UserActivityStats> findTopByOrderByActivityDateDesc();
    
    /**
     * 获取指定日期范围的统计数据
     */
    List<UserActivityStats> findByActivityDateBetween(LocalDate startDate, LocalDate endDate);
    
    /**
     * 获取指定年份的所有统计数据
     */
    @Query("SELECT uas FROM UserActivityStats uas WHERE YEAR(uas.activityDate) = :year ORDER BY uas.activityDate")
    List<UserActivityStats> findByActivityDateYear(@Param("year") int year);
}
