package com.example.demo.repository;

import com.example.demo.model.Order;
import com.example.demo.model.OrderStatus;
import com.example.demo.model.OrderType;
import com.example.demo.model.User;
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.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {
    
    /**
     * 根据ID查找订单并加载用户信息
     */
    @Query("SELECT o FROM Order o LEFT JOIN FETCH o.user WHERE o.id = :id")
    Optional<Order> findByIdWithUser(@Param("id") Long id);
    
    /**
     * 根据订单号查找订单
     */
    Optional<Order> findByOrderNumber(String orderNumber);
    
    /**
     * 根据用户ID查找订单
     */
    List<Order> findByUserId(Long userId);
    
    /**
     * 根据用户ID分页查找订单
     */
    Page<Order> findByUserId(Long userId, Pageable pageable);
    
    /**
     * 根据订单状态查找订单
     */
    List<Order> findByStatus(OrderStatus status);
    
    /**
     * 根据订单状态分页查找订单
     */
    Page<Order> findByStatus(OrderStatus status, Pageable pageable);
    
    /**
     * 根据订单类型查找订单
     */
    List<Order> findByOrderType(OrderType orderType);
    
    /**
     * 根据用户ID和订单状态查找订单
     */
    List<Order> findByUserIdAndStatus(Long userId, OrderStatus status);
    
    /**
     * 根据创建时间范围查找订单
     */
    List<Order> findByCreatedAtBetween(LocalDateTime startDate, LocalDateTime endDate);
    
    /**
     * 根据用户ID和创建时间范围查找订单
     */
    List<Order> findByUserIdAndCreatedAtBetween(Long userId, LocalDateTime startDate, LocalDateTime endDate);
    
    /**
     * 统计用户订单数量
     */
    long countByUserId(Long userId);
    
    /**
     * 统计指定状态的订单数量
     */
    long countByStatus(OrderStatus status);
    
    /**
     * 统计用户指定状态的订单数量
     */
    long countByUserIdAndStatus(Long userId, OrderStatus status);
    
    /**
     * 查找用户的最近订单
     */
    @Query("SELECT o FROM Order o WHERE o.user.id = :userId ORDER BY o.createdAt DESC")
    List<Order> findRecentOrdersByUserId(@Param("userId") Long userId, Pageable pageable);
    
    /**
     * 查找待支付的订单
     */
    @Query("SELECT o FROM Order o WHERE o.status IN ('PENDING', 'CONFIRMED') AND o.createdAt < :expireTime")
    List<Order> findExpiredPendingOrders(@Param("expireTime") LocalDateTime expireTime);
    
    /**
     * 根据订单号模糊查询
     */
    @Query("SELECT o FROM Order o WHERE o.orderNumber LIKE %:orderNumber%")
    List<Order> findByOrderNumberContaining(@Param("orderNumber") String orderNumber);
    
    /**
     * 根据用户邮箱查找订单
     */
    @Query("SELECT o FROM Order o WHERE o.contactEmail = :email")
    List<Order> findByContactEmail(@Param("email") String email);
    
    /**
     * 根据用户手机号查找订单
     */
    @Query("SELECT o FROM Order o WHERE o.contactPhone = :phone")
    List<Order> findByContactPhone(@Param("phone") String phone);
    
    /**
     * 统计指定时间范围内的订单数量
     */
    @Query("SELECT COUNT(o) FROM Order o WHERE o.createdAt BETWEEN :startDate AND :endDate")
    long countByCreatedAtBetween(@Param("startDate") LocalDateTime startDate, @Param("endDate") LocalDateTime endDate);
    
    /**
     * 统计指定时间范围内的订单总金额
     */
    @Query("SELECT SUM(o.totalAmount) FROM Order o WHERE o.createdAt BETWEEN :startDate AND :endDate AND o.status = 'COMPLETED'")
    BigDecimal sumTotalAmountByCreatedAtBetween(@Param("startDate") LocalDateTime startDate, @Param("endDate") LocalDateTime endDate);
    
    /**
     * 查找需要自动取消的订单（超过指定时间未支付的PENDING订单）
     */
    List<Order> findByStatusAndCreatedAtBefore(OrderStatus status, LocalDateTime cancelTime);
    
    // 新增的查询方法
    
    /**
     * 根据状态和订单号模糊查询（分页）
     */
    Page<Order> findByStatusAndOrderNumberContainingIgnoreCase(OrderStatus status, String orderNumber, Pageable pageable);
    
    /**
     * 根据订单号模糊查询（分页）
     */
    Page<Order> findByOrderNumberContainingIgnoreCase(String orderNumber, Pageable pageable);
    
    /**
     * 根据用户查找订单（分页）
     */
    Page<Order> findByUser(User user, Pageable pageable);
    
    /**
     * 根据用户和状态查找订单（分页）
     */
    Page<Order> findByUserAndStatus(User user, OrderStatus status, Pageable pageable);
    
    /**
     * 根据用户和订单号模糊查询（分页）
     */
    Page<Order> findByUserAndOrderNumberContainingIgnoreCase(User user, String orderNumber, Pageable pageable);
    
    /**
     * 根据用户、状态和订单号模糊查询（分页）
     */
    Page<Order> findByUserAndStatusAndOrderNumberContainingIgnoreCase(User user, OrderStatus status, String orderNumber, Pageable pageable);
    
    /**
     * 统计指定时间之后的订单数量
     */
    long countByCreatedAtAfter(LocalDateTime dateTime);
    
    /**
     * 统计用户指定时间之后的订单数量
     */
    long countByUserAndCreatedAtAfter(User user, LocalDateTime dateTime);
    
    /**
     * 统计用户的订单数量
     */
    long countByUser(User user);
    
    /**
     * 统计用户指定状态的订单数量
     */
    long countByUserAndStatus(User user, OrderStatus status);
    
    /**
     * 统计指定状态的订单总金额
     */
    @Query("SELECT SUM(o.totalAmount) FROM Order o WHERE o.status = :status")
    BigDecimal sumTotalAmountByStatus(@Param("status") OrderStatus status);
    
    /**
     * 统计用户指定状态的订单总金额
     */
    @Query("SELECT SUM(o.totalAmount) FROM Order o WHERE o.user = :user AND o.status = :status")
    BigDecimal sumTotalAmountByUserAndStatus(@Param("user") User user, @Param("status") OrderStatus status);
    
    /**
     * 获取最近的订单（用于仪表盘）
     */
    @Query("SELECT o FROM Order o ORDER BY o.createdAt DESC")
    List<Order> findRecentOrders(org.springframework.data.domain.Pageable pageable);
    
    /**
     * 统计指定时间范围内有订单的不同用户数（日活用户）
     */
    @Query("SELECT COUNT(DISTINCT o.user.id) FROM Order o WHERE o.createdAt BETWEEN :startTime AND :endTime")
    long countDistinctUsersByCreatedAtBetween(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);
    
    // ============ 使用 JOIN FETCH 预加载 User 的查询方法 ============
    
    /**
     * 分页查找所有订单（预加载User）
     */
    @Query(value = "SELECT o FROM Order o LEFT JOIN FETCH o.user",
           countQuery = "SELECT COUNT(o) FROM Order o")
    Page<Order> findAllWithUser(Pageable pageable);
    
    /**
     * 根据状态分页查找订单（预加载User）
     */
    @Query(value = "SELECT o FROM Order o LEFT JOIN FETCH o.user WHERE o.status = :status",
           countQuery = "SELECT COUNT(o) FROM Order o WHERE o.status = :status")
    Page<Order> findByStatusWithUser(@Param("status") OrderStatus status, Pageable pageable);
    
    /**
     * 根据订单号模糊查询（分页，预加载User）
     */
    @Query(value = "SELECT o FROM Order o LEFT JOIN FETCH o.user WHERE LOWER(o.orderNumber) LIKE LOWER(CONCAT('%', :orderNumber, '%'))",
           countQuery = "SELECT COUNT(o) FROM Order o WHERE LOWER(o.orderNumber) LIKE LOWER(CONCAT('%', :orderNumber, '%'))")
    Page<Order> findByOrderNumberContainingIgnoreCaseWithUser(@Param("orderNumber") String orderNumber, Pageable pageable);
    
    /**
     * 根据状态和订单号模糊查询（分页，预加载User）
     */
    @Query(value = "SELECT o FROM Order o LEFT JOIN FETCH o.user WHERE o.status = :status AND LOWER(o.orderNumber) LIKE LOWER(CONCAT('%', :orderNumber, '%'))",
           countQuery = "SELECT COUNT(o) FROM Order o WHERE o.status = :status AND LOWER(o.orderNumber) LIKE LOWER(CONCAT('%', :orderNumber, '%'))")
    Page<Order> findByStatusAndOrderNumberContainingIgnoreCaseWithUser(@Param("status") OrderStatus status, @Param("orderNumber") String orderNumber, Pageable pageable);
    
    /**
     * 根据用户查找订单（分页，预加载User）
     */
    @Query(value = "SELECT o FROM Order o LEFT JOIN FETCH o.user WHERE o.user = :user",
           countQuery = "SELECT COUNT(o) FROM Order o WHERE o.user = :user")
    Page<Order> findByUserWithUser(@Param("user") User user, Pageable pageable);
    
    /**
     * 根据用户和状态查找订单（分页，预加载User）
     */
    @Query(value = "SELECT o FROM Order o LEFT JOIN FETCH o.user WHERE o.user = :user AND o.status = :status",
           countQuery = "SELECT COUNT(o) FROM Order o WHERE o.user = :user AND o.status = :status")
    Page<Order> findByUserAndStatusWithUser(@Param("user") User user, @Param("status") OrderStatus status, Pageable pageable);
    
    /**
     * 根据用户和订单号模糊查询（分页，预加载User）
     */
    @Query(value = "SELECT o FROM Order o LEFT JOIN FETCH o.user WHERE o.user = :user AND LOWER(o.orderNumber) LIKE LOWER(CONCAT('%', :orderNumber, '%'))",
           countQuery = "SELECT COUNT(o) FROM Order o WHERE o.user = :user AND LOWER(o.orderNumber) LIKE LOWER(CONCAT('%', :orderNumber, '%'))")
    Page<Order> findByUserAndOrderNumberContainingIgnoreCaseWithUser(@Param("user") User user, @Param("orderNumber") String orderNumber, Pageable pageable);
    
    /**
     * 根据用户、状态和订单号模糊查询（分页，预加载User）
     */
    @Query(value = "SELECT o FROM Order o LEFT JOIN FETCH o.user WHERE o.user = :user AND o.status = :status AND LOWER(o.orderNumber) LIKE LOWER(CONCAT('%', :orderNumber, '%'))",
           countQuery = "SELECT COUNT(o) FROM Order o WHERE o.user = :user AND o.status = :status AND LOWER(o.orderNumber) LIKE LOWER(CONCAT('%', :orderNumber, '%'))")
    Page<Order> findByUserAndStatusAndOrderNumberContainingIgnoreCaseWithUser(@Param("user") User user, @Param("status") OrderStatus status, @Param("orderNumber") String orderNumber, Pageable pageable);
}
