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

    <insert id="insert" parameterType="com.dora.entity.PointsConsumptionLog" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO points_consumption_log (user_id, task_no, change_type, change_amount, balance_before, balance_after, description, is_deleted)
        VALUES (#{userId}, #{taskNo}, #{changeType}, #{changeAmount}, #{balanceBefore}, #{balanceAfter}, #{description}, #{isDeleted})
    </insert>

    <select id="selectByUserId" resultType="com.dora.entity.PointsConsumptionLog">
        SELECT 
            id, user_id, task_no, change_type, change_amount, 
            balance_before, balance_after, description, create_time, is_deleted
        FROM points_consumption_log
        WHERE user_id = #{userId} AND is_deleted = 0
        ORDER BY create_time DESC
        LIMIT #{offset}, #{limit}
    </select>

    <select id="countByUserId" resultType="java.lang.Long">
        SELECT COUNT(*)
        FROM points_consumption_log
        WHERE user_id = #{userId} AND is_deleted = 0
    </select>

    <select id="selectByUserIdAndType" resultType="com.dora.entity.PointsConsumptionLog">
        SELECT 
            id, user_id, task_no, change_type, change_amount, 
            balance_before, balance_after, description, create_time, is_deleted
        FROM points_consumption_log
        WHERE user_id = #{userId} 
          AND change_type = #{changeType}
          AND is_deleted = 0
        ORDER BY create_time DESC
        LIMIT #{offset}, #{limit}
    </select>

    <!-- 查询有效的消费记录（排除已退款的消费） -->
    <select id="selectEffectiveConsumeByUserId" resultType="com.dora.entity.PointsConsumptionLog">
        SELECT 
            c.id, c.user_id, c.task_no, c.change_type, c.change_amount, 
            c.balance_before, c.balance_after, c.description, c.create_time, c.is_deleted
        FROM points_consumption_log c
        WHERE c.user_id = #{userId} 
          AND c.change_type = 'consume'
          AND c.is_deleted = 0
          AND c.task_no IS NOT NULL
          AND NOT EXISTS (
              SELECT 1 FROM points_consumption_log r 
              WHERE r.user_id = c.user_id 
                AND r.task_no = c.task_no 
                AND r.change_type = 'refund'
                AND r.is_deleted = 0
          )
        ORDER BY c.create_time DESC
        LIMIT #{offset}, #{limit}
    </select>

    <!-- 统计有效的消费记录总数（排除已退款的消费） -->
    <select id="countEffectiveConsumeByUserId" resultType="java.lang.Long">
        SELECT COUNT(*)
        FROM points_consumption_log c
        WHERE c.user_id = #{userId} 
          AND c.change_type = 'consume'
          AND c.is_deleted = 0
          AND c.task_no IS NOT NULL
          AND NOT EXISTS (
              SELECT 1 FROM points_consumption_log r 
              WHERE r.user_id = c.user_id 
                AND r.task_no = c.task_no 
                AND r.change_type = 'refund'
                AND r.is_deleted = 0
          )
    </select>

</mapper>
