<?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.WithdrawRequestMapper">

    <resultMap id="WithdrawRequestResultMap" type="com.dora.entity.WithdrawRequest">
        <id column="id" property="id"/>
        <result column="user_id" property="userId"/>
        <result column="amount" property="amount"/>
        <result column="withdraw_type" property="withdrawType"/>
        <result column="content_id" property="contentId"/>
        <result column="account_type" property="accountType"/>
        <result column="account" property="account"/>
        <result column="account_name" property="accountName"/>
        <result column="bank_name" property="bankName"/>
        <result column="bank_branch" property="bankBranch"/>
        <result column="status" property="status"/>
        <result column="reason" property="reason"/>
        <result column="reviewer_id" property="reviewerId"/>
        <result column="transaction_no" property="transactionNo"/>
        <result column="fee_amount" property="feeAmount"/>
        <result column="actual_amount" property="actualAmount"/>
        <result column="processed_at" property="processedAt"/>
        <result column="create_time" property="createTime"/>
        <result column="reviewed_at" property="reviewedAt"/>
        <result column="update_time" property="updateTime"/>
        <result column="is_deleted" property="isDeleted"/>
    </resultMap>

    <insert id="insert" parameterType="com.dora.entity.WithdrawRequest" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO withdraw_request (
            user_id, amount, withdraw_type, content_id, account_type, account, account_name, 
            bank_name, bank_branch, status, reason, create_time, update_time, is_deleted
        ) VALUES (
            #{userId}, #{amount}, #{withdrawType}, #{contentId}, #{accountType}, #{account}, #{accountName}, 
            #{bankName}, #{bankBranch}, #{status}, #{reason}, NOW(), NOW(), 0
        )
    </insert>

    <select id="selectById" resultMap="WithdrawRequestResultMap">
        SELECT * FROM withdraw_request 
        WHERE id = #{id} AND is_deleted = 0
    </select>

    <select id="selectByUserId" resultMap="WithdrawRequestResultMap">
        SELECT * FROM withdraw_request 
        WHERE user_id = #{userId} AND is_deleted = 0
        ORDER BY create_time DESC
        LIMIT #{offset}, #{limit}
    </select>

    <select id="countByUserId" resultType="long">
        SELECT COUNT(*) FROM withdraw_request 
        WHERE user_id = #{userId} AND is_deleted = 0
    </select>

    <select id="sumPendingAmountByUserId" resultType="java.math.BigDecimal">
        SELECT COALESCE(SUM(amount), 0) FROM withdraw_request 
        WHERE user_id = #{userId} AND status = 0 AND is_deleted = 0
    </select>

    <select id="sumApprovedAmountByUserId" resultType="java.math.BigDecimal">
        SELECT COALESCE(SUM(amount), 0) FROM withdraw_request 
        WHERE user_id = #{userId} AND status = 1 AND is_deleted = 0
    </select>

    <update id="updateStatus">
        UPDATE withdraw_request 
        SET status = #{status}, 
            reason = #{reason}, 
            reviewed_at = NOW(), 
            update_time = NOW()
        WHERE id = #{id} AND is_deleted = 0
    </update>

    <!-- ====== 管理员审核相关SQL ====== -->

    <!-- 管理员查询提现申请列表（带条件筛选） -->
    <select id="selectForAdmin" resultMap="WithdrawRequestResultMap">
        SELECT * FROM withdraw_request 
        WHERE is_deleted = 0
        <if test="status != null">
            AND status = #{status}
        </if>
        <if test="userId != null">
            AND user_id = #{userId}
        </if>
        <if test="accountType != null and accountType != ''">
            AND account_type = #{accountType}
        </if>
        ORDER BY 
            CASE WHEN status = 0 THEN 0 ELSE 1 END,  -- 待审核优先
            create_time DESC
        LIMIT #{offset}, #{limit}
    </select>

    <!-- 管理员查询提现申请总数（带条件筛选） -->
    <select id="countForAdmin" resultType="long">
        SELECT COUNT(*) FROM withdraw_request 
        WHERE is_deleted = 0
        <if test="status != null">
            AND status = #{status}
        </if>
        <if test="userId != null">
            AND user_id = #{userId}
        </if>
        <if test="accountType != null and accountType != ''">
            AND account_type = #{accountType}
        </if>
    </select>

    <!-- 更新提现申请状态和审核时间 -->
    <update id="updateStatusWithReviewTime">
        UPDATE withdraw_request 
        SET status = #{status}, 
            reason = #{reason}, 
            reviewer_id = #{reviewerId},
            reviewed_at = NOW(), 
            update_time = NOW()
        WHERE id = #{id} AND is_deleted = 0
    </update>

    <!-- 管理员审核时更新提现申请（包含流水号等处理信息） -->
    <update id="updateWithProcessInfo">
        UPDATE withdraw_request 
        SET status = #{status}, 
            reason = #{reason}, 
            reviewer_id = #{reviewerId},
            <if test="transactionNo != null and transactionNo != ''">
                transaction_no = #{transactionNo},
            </if>
            <if test="feeAmount != null">
                fee_amount = #{feeAmount},
            </if>
            <if test="actualAmount != null">
                actual_amount = #{actualAmount},
            </if>
            <if test="status == 1">
                processed_at = NOW(),
            </if>
            reviewed_at = NOW(), 
            update_time = NOW()
        WHERE id = #{id} AND is_deleted = 0
    </update>

    <!-- 统计指定状态的提现申请数量 -->
    <select id="countByStatus" resultType="long">
        SELECT COUNT(*) FROM withdraw_request 
        WHERE status = #{status} AND is_deleted = 0
    </select>

    <!-- 统计指定状态的提现金额 -->
    <select id="sumAmountByStatus" resultType="java.math.BigDecimal">
        SELECT COALESCE(SUM(amount), 0) FROM withdraw_request 
        WHERE status = #{status} AND is_deleted = 0
    </select>

    <!-- 查询用户信息关联的提现申请（用于管理员审核显示用户详情） -->
    <select id="selectWithUserInfo" resultMap="WithdrawRequestResultMap">
        SELECT wr.* FROM withdraw_request wr 
        LEFT JOIN users u ON wr.user_id = u.id
        WHERE wr.is_deleted = 0 AND u.is_deleted = 0
        <if test="status != null">
            AND wr.status = #{status}
        </if>
        <if test="userId != null">
            AND wr.user_id = #{userId}
        </if>
        <if test="accountType != null and accountType != ''">
            AND wr.account_type = #{accountType}
        </if>
        ORDER BY 
            CASE WHEN wr.status = 0 THEN 0 ELSE 1 END,  -- 待审核优先
            wr.create_time DESC
        LIMIT #{offset}, #{limit}
    </select>

    <!-- 批量更新提现申请状态 -->
    <update id="updateStatusBatch">
        UPDATE withdraw_request 
        SET status = #{status}, 
            reason = #{reason}, 
            reviewed_at = NOW(), 
            update_time = NOW()
        WHERE id IN 
        <foreach collection="ids" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
        AND is_deleted = 0
    </update>

    <!-- ====== 仪表盘统计相关 ====== -->

    <!-- 统计已通过的提现总金额 -->
    <select id="sumApprovedAmount" resultType="java.math.BigDecimal">
        SELECT COALESCE(SUM(amount), 0) FROM withdraw_request 
        WHERE status = 1 AND is_deleted = 0
    </select>

    <!-- 统计今日审核通过的数量 -->
    <select id="countTodayApproved" resultType="long">
        SELECT COUNT(*) FROM withdraw_request 
        WHERE status = 1 
        AND DATE(reviewed_at) = DATE(#{today}) 
        AND is_deleted = 0
    </select>

    <!-- 统计今日审核通过的金额 -->
    <select id="sumTodayApprovedAmount" resultType="java.math.BigDecimal">
        SELECT COALESCE(SUM(amount), 0) FROM withdraw_request 
        WHERE status = 1 
        AND DATE(reviewed_at) = DATE(#{today}) 
        AND is_deleted = 0
    </select>

    <!-- 统计今日提现申请数量 -->
    <select id="countTodayRequests" resultType="long">
        SELECT COUNT(*) FROM withdraw_request 
        WHERE DATE(create_time) = DATE(#{today}) 
        AND is_deleted = 0
    </select>

</mapper> 