package com.example.demo.repository;

import java.util.List;
import java.util.Optional;

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 com.example.demo.model.Payment;
import com.example.demo.model.PaymentStatus;

@Repository
public interface PaymentRepository extends JpaRepository<Payment, Long> {
    
    Optional<Payment> findByOrderId(String orderId);
    
    /**
     * 根据ID查询Payment并立即加载User（避免LazyInitializationException）
     */
    @Query("SELECT p FROM Payment p LEFT JOIN FETCH p.user WHERE p.id = :id")
    Optional<Payment> findByIdWithUser(@Param("id") Long id);
    
    /**
     * 根据ID查询Payment并立即加载User和Order（避免LazyInitializationException）
     */
    @Query("SELECT p FROM Payment p LEFT JOIN FETCH p.user LEFT JOIN FETCH p.order WHERE p.id = :id")
    Optional<Payment> findByIdWithUserAndOrder(@Param("id") Long id);
    
    Optional<Payment> findByExternalTransactionId(String externalTransactionId);
    
    List<Payment> findByUserId(Long userId);
    
    List<Payment> findByStatus(PaymentStatus status);
    
    @Query("SELECT p FROM Payment p WHERE p.user.id = :userId ORDER BY p.createdAt DESC")
    List<Payment> findByUserIdOrderByCreatedAtDesc(@Param("userId") Long userId);
    
    @Query("SELECT COUNT(p) FROM Payment p WHERE p.status = :status")
    long countByStatus(@Param("status") PaymentStatus status);
    
    /**
     * 统计用户支付记录数量
     */
    long countByUserId(Long userId);
    
    /**
     * 统计用户指定状态的支付记录数量
     */
    long countByUserIdAndStatus(Long userId, PaymentStatus status);
    
    /**
     * 获取今日收入
     */
    @Query("SELECT SUM(p.amount) FROM Payment p WHERE p.status = 'COMPLETED' AND p.paidAt BETWEEN :startTime AND :endTime")
    Double findTodayRevenue(@Param("startTime") java.time.LocalDateTime startTime, @Param("endTime") java.time.LocalDateTime endTime);
    
    /**
     * 获取总收入
     */
    @Query("SELECT SUM(p.amount) FROM Payment p WHERE p.status = 'COMPLETED'")
    Double findTotalRevenue();
    
    /**
     * 获取指定日期范围的收入
     */
    @Query("SELECT SUM(p.amount) FROM Payment p WHERE p.status = 'COMPLETED' AND p.paidAt BETWEEN :startTime AND :endTime")
    Double findRevenueByDateRange(@Param("startTime") java.time.LocalDateTime startTime, @Param("endTime") java.time.LocalDateTime endTime);
    
    /**
     * 获取指定年份的月度收入数据
     */
    @Query("SELECT MONTH(p.paidAt) as month, SUM(p.amount) as revenue, COUNT(p) as orderCount " +
           "FROM Payment p WHERE p.status = 'COMPLETED' AND YEAR(p.paidAt) = :year " +
           "GROUP BY MONTH(p.paidAt) ORDER BY MONTH(p.paidAt)")
    List<java.util.Map<String, Object>> findMonthlyRevenueByYear(@Param("year") int year);
    
    /**
     * 获取用户最近一次成功的订阅支付（按支付时间倒序）
     */
    @Query("SELECT p FROM Payment p WHERE p.user.id = :userId " +
           "AND p.status = :status " +
           "AND (p.description LIKE '%标准版%' OR p.description LIKE '%专业版%' OR p.description LIKE '%会员%') " +
           "ORDER BY p.paidAt DESC, p.createdAt DESC")
    List<Payment> findLatestSuccessfulSubscriptionByUserId(@Param("userId") Long userId, @Param("status") PaymentStatus status);
    
    /**
     * 统计成功支付的不同用户数（付费用户数）
     */
    @Query("SELECT COUNT(DISTINCT p.user.id) FROM Payment p WHERE p.status = :status")
    long countDistinctUsersByStatus(@Param("status") PaymentStatus status);
    
    /**
     * 获取今日成功支付的总金额
     */
    @Query("SELECT COALESCE(SUM(p.amount), 0) FROM Payment p WHERE p.status = :status AND p.paidAt BETWEEN :startTime AND :endTime")
    java.math.BigDecimal sumAmountByStatusAndPaidAtBetween(@Param("status") PaymentStatus status, @Param("startTime") java.time.LocalDateTime startTime, @Param("endTime") java.time.LocalDateTime endTime);
    
    /**
     * 统计指定时间范围内成功支付的不同用户数
     */
    @Query("SELECT COUNT(DISTINCT p.user.id) FROM Payment p WHERE p.status = :status AND p.paidAt BETWEEN :startTime AND :endTime")
    long countDistinctUsersByStatusAndPaidAtBetween(@Param("status") PaymentStatus status, @Param("startTime") java.time.LocalDateTime startTime, @Param("endTime") java.time.LocalDateTime endTime);
    
    /**
     * 统计指定时间之前成功支付的不同用户数（用于累计统计）
     */
    @Query("SELECT COUNT(DISTINCT p.user.id) FROM Payment p WHERE p.status = :status AND p.paidAt < :beforeTime")
    long countDistinctUsersByStatusAndPaidAtBefore(@Param("status") PaymentStatus status, @Param("beforeTime") java.time.LocalDateTime beforeTime);
    
    /**
     * 计算用户指定状态的支付总金额
     * 用于统计用户总充值金额
     */
    @Query("SELECT COALESCE(SUM(p.amount), 0) FROM Payment p WHERE p.user.id = :userId AND p.status = :status")
    java.math.BigDecimal sumAmountByUserIdAndStatus(@Param("userId") Long userId, @Param("status") PaymentStatus status);
}
