package com.it.rattan.monisuo.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.it.rattan.monisuo.entity.OrderFund;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import java.math.BigDecimal;
import java.time.LocalDateTime;

/**
 * 充提订单Mapper
 */
@Mapper
public interface OrderFundMapper extends BaseMapper<OrderFund> {

    @Select("SELECT IFNULL(SUM(amount), 0) FROM order_fund WHERE type = 1 AND status = 2")
    BigDecimal sumCompletedDeposit();

    @Select("SELECT IFNULL(SUM(amount), 0) FROM order_fund WHERE type = 2 AND status = 2")
    BigDecimal sumCompletedWithdraw();

    @Select("SELECT COUNT(*) FROM order_fund WHERE status = 1")
    int countPending();

    // ========== 分析相关查询 ==========

    /**
     * 指定时间段内的手续费总额
     */
    @Select("SELECT IFNULL(SUM(amount * 0.005), 0) FROM order_fund WHERE status = 2 AND create_time >= #{startTime}")
    BigDecimal sumFeeByTime(@Param("startTime") LocalDateTime startTime);

    /**
     * 指定时间段内的充值总额
     */
    @Select("SELECT IFNULL(SUM(amount), 0) FROM order_fund WHERE type = 1 AND status = 2 AND create_time >= #{startTime} AND create_time < #{endTime}")
    BigDecimal sumDepositByTime(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);

    /**
     * 指定时间段内的提现总额
     */
    @Select("SELECT IFNULL(SUM(amount), 0) FROM order_fund WHERE type = 2 AND status = 2 AND create_time >= #{startTime} AND create_time < #{endTime}")
    BigDecimal sumWithdrawByTime(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);

    /**
     * 大额交易数量
     */
    @Select("SELECT COUNT(*) FROM order_fund WHERE amount >= #{threshold} AND status = 2")
    int countLargeAmount(@Param("threshold") BigDecimal threshold);

    /**
     * 异常提现用户数（指定时间内提现次数超过阈值）
     */
    @Select("SELECT COUNT(DISTINCT user_id) FROM order_fund WHERE type = 2 AND create_time >= #{startTime} AND user_id IN " +
            "(SELECT user_id FROM order_fund WHERE type = 2 AND create_time >= #{startTime} GROUP BY user_id HAVING COUNT(*) >= #{minCount})")
    int countAbnormalWithdrawals(@Param("startTime") LocalDateTime startTime, @Param("minCount") int minCount);
}
