<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.dora.mapper.AdminPaymentUserMapper">

    <!-- 获取支付概览统计 -->
    <select id="selectPaymentOverview" resultType="com.dora.dto.AdminPaymentUserDto$PaymentOverview">
        SELECT 
            (SELECT COUNT(DISTINCT o.user_id) 
             FROM `order` o 
             WHERE o.status = 1 AND o.is_deleted = 0
             <if test="startDate != null and endDate != null">
                 AND o.paid_at &gt;= #{startDate} AND o.paid_at &lt; #{endDate}
             </if>
            ) as totalPaymentUsers,
            
            (SELECT COUNT(*) 
             FROM `order` o 
             WHERE o.status = 1 AND o.is_deleted = 0
             <if test="startDate != null and endDate != null">
                 AND o.paid_at &gt;= #{startDate} AND o.paid_at &lt; #{endDate}
             </if>
            ) as totalPaymentOrders,
            
            (SELECT COALESCE(SUM(o.amount), 0) 
             FROM `order` o 
             WHERE o.status = 1 AND o.is_deleted = 0
             <if test="startDate != null and endDate != null">
                 AND o.paid_at &gt;= #{startDate} AND o.paid_at &lt; #{endDate}
             </if>
            ) as totalPaymentAmount,
            
            (SELECT COALESCE(AVG(o.amount), 0) 
             FROM `order` o 
             WHERE o.status = 1 AND o.is_deleted = 0
             <if test="startDate != null and endDate != null">
                 AND o.paid_at &gt;= #{startDate} AND o.paid_at &lt; #{endDate}
             </if>
            ) as avgOrderAmount,
            
            (SELECT COUNT(DISTINCT o.user_id) 
             FROM `order` o 
             LEFT JOIN membership_plan mp ON o.plan_id = mp.id 
             WHERE o.status = 1 AND o.is_deleted = 0 AND mp.target_role = 2
             <if test="startDate != null and endDate != null">
                 AND o.paid_at &gt;= #{startDate} AND o.paid_at &lt; #{endDate}
             </if>
            ) as newVipUsers,
            
            (SELECT COUNT(DISTINCT o.user_id) 
             FROM `order` o 
             LEFT JOIN membership_plan mp ON o.plan_id = mp.id 
             WHERE o.status = 1 AND o.is_deleted = 0 AND mp.target_role = 3
             <if test="startDate != null and endDate != null">
                 AND o.paid_at &gt;= #{startDate} AND o.paid_at &lt; #{endDate}
             </if>
            ) as newSvipUsers,
            
            (SELECT COUNT(DISTINCT o.user_id) 
             FROM `order` o 
             WHERE o.status = 1 AND o.is_deleted = 0
             <if test="startDate != null and endDate != null">
                 AND o.paid_at &gt;= #{startDate} AND o.paid_at &lt; #{endDate}
             </if>
             AND o.user_id IN (
                 SELECT user_id 
                 FROM `order` 
                 WHERE status = 1 AND is_deleted = 0 
                 GROUP BY user_id 
                 HAVING COUNT(*) &gt; 1
             )
            ) as repeatPurchaseUsers,
            
            (SELECT COUNT(DISTINCT o.user_id) 
             FROM `order` o 
             WHERE o.status = 1 AND o.is_deleted = 0
             <if test="startDate != null and endDate != null">
                 AND o.paid_at &gt;= #{startDate} AND o.paid_at &lt; #{endDate}
             </if>
             AND o.user_id NOT IN (
                 SELECT DISTINCT user_id 
                 FROM `order` 
                 WHERE status = 1 AND is_deleted = 0 
                 <if test="startDate != null">
                     AND paid_at &lt; #{startDate}
                 </if>
             )
            ) as firstTimeUsers
    </select>

    <!-- 获取支付用户列表 -->
    <select id="selectPaymentUserList" resultType="com.dora.dto.AdminPaymentUserDto$PaymentUserInfo">
        SELECT 
            u.id as userId,
            u.username,
            u.phone,
            u.role,
            pay_stats.orderCount,
            pay_stats.totalAmount,
            pay_stats.lastPaidAt,
            pay_stats.firstPaidAt,
            pay_stats.paymentMethod,
            CASE WHEN pay_stats.orderCount &gt; 1 THEN true ELSE false END as isRepeatUser
        FROM user u
        INNER JOIN (
            SELECT 
                o.user_id,
                COUNT(*) as orderCount,
                SUM(o.amount) as totalAmount,
                MAX(o.paid_at) as lastPaidAt,
                MIN(o.paid_at) as firstPaidAt,
                MAX(o.payment_method) as paymentMethod
            FROM `order` o
            WHERE o.status = 1 AND o.is_deleted = 0
            <if test="startDate != null and endDate != null">
                AND o.paid_at &gt;= #{startDate} AND o.paid_at &lt; #{endDate}
            </if>
            GROUP BY o.user_id
        ) pay_stats ON u.id = pay_stats.user_id
        WHERE u.is_deleted = 0
        <if test="role != null">
            AND u.role = #{role}
        </if>
        <if test="onlyRepeatUsers != null and onlyRepeatUsers == true">
            AND pay_stats.orderCount &gt; 1
        </if>
        <if test="minAmount != null">
            AND pay_stats.totalAmount &gt;= #{minAmount}
        </if>
        <if test="maxAmount != null">
            AND pay_stats.totalAmount &lt;= #{maxAmount}
        </if>
        <choose>
            <when test="sortField == 'totalAmount'">
                ORDER BY pay_stats.totalAmount ${sortOrder}
            </when>
            <when test="sortField == 'orderCount'">
                ORDER BY pay_stats.orderCount ${sortOrder}
            </when>
            <when test="sortField == 'firstPaidAt'">
                ORDER BY pay_stats.firstPaidAt ${sortOrder}
            </when>
            <otherwise>
                ORDER BY pay_stats.lastPaidAt ${sortOrder}
            </otherwise>
        </choose>
        LIMIT #{offset}, #{size}
    </select>

    <!-- 统计支付用户列表总数 -->
    <select id="countPaymentUserList" resultType="java.lang.Long">
        SELECT COUNT(*)
        FROM user u
        INNER JOIN (
            SELECT 
                o.user_id,
                COUNT(*) as orderCount,
                SUM(o.amount) as totalAmount
            FROM `order` o
            WHERE o.status = 1 AND o.is_deleted = 0
            <if test="startDate != null and endDate != null">
                AND o.paid_at &gt;= #{startDate} AND o.paid_at &lt; #{endDate}
            </if>
            GROUP BY o.user_id
        ) pay_stats ON u.id = pay_stats.user_id
        WHERE u.is_deleted = 0
        <if test="role != null">
            AND u.role = #{role}
        </if>
        <if test="onlyRepeatUsers != null and onlyRepeatUsers == true">
            AND pay_stats.orderCount &gt; 1
        </if>
        <if test="minAmount != null">
            AND pay_stats.totalAmount &gt;= #{minAmount}
        </if>
        <if test="maxAmount != null">
            AND pay_stats.totalAmount &lt;= #{maxAmount}
        </if>
    </select>

    <!-- 获取支付金额分布统计 -->
    <select id="selectAmountDistribution" resultType="java.util.Map">
        SELECT 
            CASE 
                WHEN user_total.totalAmount &lt; 50 THEN '0-50元'
                WHEN user_total.totalAmount &lt; 100 THEN '50-100元'
                WHEN user_total.totalAmount &lt; 200 THEN '100-200元'
                WHEN user_total.totalAmount &lt; 500 THEN '200-500元'
                ELSE '500元以上'
            END as rangeName,
            COUNT(*) as userCount
        FROM (
            SELECT 
                o.user_id,
                SUM(o.amount) as totalAmount
            FROM `order` o
            WHERE o.status = 1 AND o.is_deleted = 0
            <if test="startDate != null and endDate != null">
                AND o.paid_at &gt;= #{startDate} AND o.paid_at &lt; #{endDate}
            </if>
            GROUP BY o.user_id
        ) user_total
        GROUP BY 
            CASE 
                WHEN user_total.totalAmount &lt; 50 THEN '0-50元'
                WHEN user_total.totalAmount &lt; 100 THEN '50-100元'
                WHEN user_total.totalAmount &lt; 200 THEN '100-200元'
                WHEN user_total.totalAmount &lt; 500 THEN '200-500元'
                ELSE '500元以上'
            END
        ORDER BY 
            CASE 
                WHEN user_total.totalAmount &lt; 50 THEN 1
                WHEN user_total.totalAmount &lt; 100 THEN 2
                WHEN user_total.totalAmount &lt; 200 THEN 3
                WHEN user_total.totalAmount &lt; 500 THEN 4
                ELSE 5
            END
    </select>

    <!-- 获取每日支付统计 -->
    <select id="selectDailyPaymentStats" resultType="com.dora.dto.AdminPaymentUserDto$DailyPaymentStats">
        SELECT 
            DATE(o.paid_at) as date,
            COUNT(DISTINCT o.user_id) as paymentUsers,
            COUNT(*) as paymentOrders,
            COALESCE(SUM(o.amount), 0) as paymentAmount,
            COUNT(DISTINCT CASE WHEN mp.target_role = 2 THEN o.user_id END) as newVipUsers,
            COUNT(DISTINCT CASE WHEN mp.target_role = 3 THEN o.user_id END) as newSvipUsers
        FROM `order` o
        LEFT JOIN membership_plan mp ON o.plan_id = mp.id
        WHERE o.status = 1 AND o.is_deleted = 0
        <if test="startDate != null and endDate != null">
            AND o.paid_at &gt;= #{startDate} AND o.paid_at &lt; #{endDate}
        </if>
        GROUP BY DATE(o.paid_at)
        ORDER BY DATE(o.paid_at) ASC
    </select>

    <!-- 统计指定时间范围内的总支付用户数 -->
    <select id="countTotalPaymentUsers" resultType="java.lang.Long">
        SELECT COUNT(DISTINCT o.user_id)
        FROM `order` o
        WHERE o.status = 1 AND o.is_deleted = 0
        <if test="startDate != null and endDate != null">
            AND o.paid_at &gt;= #{startDate} AND o.paid_at &lt; #{endDate}
        </if>
    </select>

    <!-- 统计指定时间范围内的总支付订单数 -->
    <select id="countTotalPaymentOrders" resultType="java.lang.Long">
        SELECT COUNT(*)
        FROM `order` o
        WHERE o.status = 1 AND o.is_deleted = 0
        <if test="startDate != null and endDate != null">
            AND o.paid_at &gt;= #{startDate} AND o.paid_at &lt; #{endDate}
        </if>
    </select>

    <!-- 统计指定时间范围内的总支付金额 -->
    <select id="sumTotalPaymentAmount" resultType="java.math.BigDecimal">
        SELECT COALESCE(SUM(o.amount), 0)
        FROM `order` o
        WHERE o.status = 1 AND o.is_deleted = 0
        <if test="startDate != null and endDate != null">
            AND o.paid_at &gt;= #{startDate} AND o.paid_at &lt; #{endDate}
        </if>
    </select>

    <!-- 统计指定时间范围内的新增VIP用户数 -->
    <select id="countNewVipUsers" resultType="java.lang.Long">
        SELECT COUNT(DISTINCT o.user_id)
        FROM `order` o
        LEFT JOIN membership_plan mp ON o.plan_id = mp.id
        WHERE o.status = 1 AND o.is_deleted = 0 AND mp.target_role = 2
        <if test="startDate != null and endDate != null">
            AND o.paid_at &gt;= #{startDate} AND o.paid_at &lt; #{endDate}
        </if>
    </select>

    <!-- 统计指定时间范围内的新增SVIP用户数 -->
    <select id="countNewSvipUsers" resultType="java.lang.Long">
        SELECT COUNT(DISTINCT o.user_id)
        FROM `order` o
        LEFT JOIN membership_plan mp ON o.plan_id = mp.id
        WHERE o.status = 1 AND o.is_deleted = 0 AND mp.target_role = 3
        <if test="startDate != null and endDate != null">
            AND o.paid_at &gt;= #{startDate} AND o.paid_at &lt; #{endDate}
        </if>
    </select>

    <!-- 统计指定时间范围内的复购用户数 -->
    <select id="countRepeatPurchaseUsers" resultType="java.lang.Long">
        SELECT COUNT(DISTINCT o.user_id)
        FROM `order` o
        WHERE o.status = 1 AND o.is_deleted = 0
        <if test="startDate != null and endDate != null">
            AND o.paid_at &gt;= #{startDate} AND o.paid_at &lt; #{endDate}
        </if>
        AND o.user_id IN (
            SELECT user_id
            FROM `order`
            WHERE status = 1 AND is_deleted = 0
            GROUP BY user_id
            HAVING COUNT(*) &gt; 1
        )
    </select>

    <!-- 统计指定时间范围内的首次购买用户数 -->
    <select id="countFirstTimeUsers" resultType="java.lang.Long">
        SELECT COUNT(DISTINCT o.user_id)
        FROM `order` o
        WHERE o.status = 1 AND o.is_deleted = 0
        <if test="startDate != null and endDate != null">
            AND o.paid_at &gt;= #{startDate} AND o.paid_at &lt; #{endDate}
        </if>
        AND o.user_id NOT IN (
            SELECT DISTINCT user_id
            FROM `order`
            WHERE status = 1 AND is_deleted = 0
            <if test="startDate != null">
                AND paid_at &lt; #{startDate}
            </if>
        )
    </select>

</mapper>





