package com.haha.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.haha.entity.Order;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

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

public interface OrderMapper extends BaseMapper<Order> {

    /**
     * 统计已完成订单的销售总额
     * 
     * @param startDate 开始时间（null表示统计全部）
     * @return 销售总额
     */
    @Select("<script>" +
            "SELECT COALESCE(SUM(total_amount), 0) FROM t_order " +
            "WHERE status = 2 AND pay_status = 'PAID' " +
            "<if test='startDate != null'>" +
            "AND create_time &gt;= #{startDate} " +
            "</if>" +
            "</script>")
    BigDecimal sumCompletedOrderAmount(@Param("startDate") LocalDateTime startDate);

    /**
     * 按支付渠道统计订单金额
     *
     * @param startDate 开始时间（null表示统计全部）
     * @param endDate 结束时间（null表示统计全部）
     * @return 每个渠道的金额汇总（channel, amount）
     */
    @Select("<script>" +
            "SELECT pay_channel as channel, COALESCE(SUM(paid_amount), 0) as amount FROM t_order " +
            "WHERE status = 2 AND pay_status = 'PAID' " +
            "<if test='startDate != null'>AND create_time &gt;= #{startDate} </if>" +
            "<if test='endDate != null'>AND create_time &lt;= #{endDate} </if>" +
            "GROUP BY pay_channel" +
            "</script>")
    List<Map<String, Object>> sumAmountByChannel(
            @Param("startDate") LocalDateTime startDate,
            @Param("endDate") LocalDateTime endDate);

    /**
     * 统计指定时间范围内的订单总数
     *
     * @param startDate 开始时间（null表示统计全部）
     * @param endDate 结束时间（null表示统计全部）
     * @return 订单总数
     */
    @Select("<script>" +
            "SELECT COUNT(*) FROM t_order WHERE 1=1 " +
            "<if test='startDate != null'>AND create_time &gt;= #{startDate} </if>" +
            "<if test='endDate != null'>AND create_time &lt;= #{endDate} </if>" +
            "</script>")
    Long countByDateRange(
            @Param("startDate") LocalDateTime startDate,
            @Param("endDate") LocalDateTime endDate);

    /**
     * 统计指定时间范围内的已完成订单数
     *
     * @param startDate 开始时间（null表示统计全部）
     * @param endDate 结束时间（null表示统计全部）
     * @return 已完成订单数
     */
    @Select("<script>" +
            "SELECT COUNT(*) FROM t_order WHERE status = 2 " +
            "<if test='startDate != null'>AND create_time &gt;= #{startDate} </if>" +
            "<if test='endDate != null'>AND create_time &lt;= #{endDate} </if>" +
            "</script>")
    Long countCompletedByDateRange(
            @Param("startDate") LocalDateTime startDate,
            @Param("endDate") LocalDateTime endDate);

    /**
     * 统计指定时间范围内的已退款订单数
     *
     * @param startDate 开始时间（null表示统计全部）
     * @param endDate 结束时间（null表示统计全部）
     * @return 已退款订单数
     */
    @Select("<script>" +
            "SELECT COUNT(*) FROM t_order WHERE pay_status = 'REFUND' " +
            "<if test='startDate != null'>AND create_time &gt;= #{startDate} </if>" +
            "<if test='endDate != null'>AND create_time &lt;= #{endDate} </if>" +
            "</script>")
    Long countRefundedByDateRange(
            @Param("startDate") LocalDateTime startDate,
            @Param("endDate") LocalDateTime endDate);

    /**
     * 批量查询用户历史已支付订单数
     *
     * @param userIds 用户ID列表
     * @return 每个用户的已支付订单数（user_id, order_count）
     */
    @Select("<script>" +
            "SELECT user_id, COUNT(*) as order_count FROM t_order " +
            "WHERE pay_status IN ('PAID', 'PARTIAL_REFUND') AND user_id IN " +
            "<foreach collection='userIds' item='id' open='(' separator=',' close=')'>" +
            "#{id}" +
            "</foreach>" +
            " GROUP BY user_id" +
            "</script>")
    List<Map<String, Object>> batchCountPaidOrdersByUserIds(@Param("userIds") List<Long> userIds);

    /**
     * 批量查询用户近30天已支付订单数
     *
     * @param userIds 用户ID列表
     * @param thirtyDaysAgo 30天前的时间
     * @return 每个用户近30天的已支付订单数（user_id, order_count）
     */
    @Select("<script>" +
            "SELECT user_id, COUNT(*) as order_count FROM t_order " +
            "WHERE pay_status IN ('PAID', 'PARTIAL_REFUND') AND pay_time &gt;= #{thirtyDaysAgo} AND user_id IN " +
            "<foreach collection='userIds' item='id' open='(' separator=',' close=')'>" +
            "#{id}" +
            "</foreach>" +
            " GROUP BY user_id" +
            "</script>")
    List<Map<String, Object>> batchCountRecentPaidOrdersByUserIds(
            @Param("userIds") List<Long> userIds,
            @Param("thirtyDaysAgo") LocalDateTime thirtyDaysAgo);

    /**
     * 批量查询用户总订单数（包含所有状态）
     *
     * @param userIds 用户ID列表
     * @return 每个用户的总订单数（user_id, order_count）
     */
    @Select("<script>" +
            "SELECT user_id, COUNT(*) as order_count FROM t_order " +
            "WHERE user_id IN " +
            "<foreach collection='userIds' item='id' open='(' separator=',' close=')'>" +
            "#{id}" +
            "</foreach>" +
            " GROUP BY user_id" +
            "</script>")
    List<Map<String, Object>> batchCountAllOrdersByUserIds(@Param("userIds") List<Long> userIds);

    @Select("<script>" +
            "SELECT user_id, COALESCE(SUM(paid_amount), 0) as total_spent FROM t_order " +
            "WHERE pay_status IN ('PAID', 'PARTIAL_REFUND', 'REFUND') AND user_id IN " +
            "<foreach collection='userIds' item='id' open='(' separator=',' close=')'>" +
            "#{id}" +
            "</foreach>" +
            " GROUP BY user_id" +
            "</script>")
    List<Map<String, Object>> batchSumPaidAmountByUserIds(@Param("userIds") List<Long> userIds);
}
