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

    <!-- 插入订单（支持积分订单） -->
    <insert id="insertOrder" parameterType="com.dora.entity.Order" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO `order` (
            order_no, order_type, user_id, plan_id, points_package_id, points_amount,
            original_price, amount, actual_duration_days,
            discount_type, discount_amount, discount_description,
            status, payment_method, paid_at,
            create_time, update_time, is_deleted
        ) VALUES (
            #{orderNo}, #{orderType}, #{userId}, #{planId}, #{pointsPackageId}, #{pointsAmount},
            #{originalPrice}, #{amount}, #{actualDurationDays},
            #{discountType}, #{discountAmount}, #{discountDescription},
            #{status}, #{paymentMethod}, #{paidAt},
            #{createTime}, #{updateTime}, #{isDeleted}
        )
    </insert>

    <!-- 查询用户的积分订单列表 -->
    <select id="selectPointsOrdersByUserId" resultType="com.dora.entity.Order">
        SELECT * FROM `order`
        WHERE user_id = #{userId}
          AND order_type = 2
          AND is_deleted = 0
        ORDER BY create_time DESC
        LIMIT #{offset}, #{size}
    </select>

    <!-- 统计用户积分订单数量 -->
    <select id="countPointsOrdersByUserId" resultType="java.lang.Long">
        SELECT COUNT(*) FROM `order`
        WHERE user_id = #{userId}
          AND order_type = 2
          AND is_deleted = 0
        <if test="status != null">
            AND status = #{status}
        </if>
    </select>

    <!-- 统计用户积分订单总金额 -->
    <select id="sumPointsOrdersAmountByUserId" resultType="java.math.BigDecimal">
        SELECT COALESCE(SUM(amount), 0) FROM `order`
        WHERE user_id = #{userId}
          AND order_type = 2
          AND is_deleted = 0
        <if test="status != null">
            AND status = #{status}
        </if>
    </select>

    <!-- 统计用户积分订单总积分 -->
    <select id="sumPointsOrdersPointsByUserId" resultType="java.lang.Long">
        SELECT COALESCE(SUM(points_amount), 0) FROM `order`
        WHERE user_id = #{userId}
          AND order_type = 2
          AND is_deleted = 0
        <if test="status != null">
            AND status = #{status}
        </if>
    </select>

    <!-- 查询用户最后一笔积分订单 -->
    <select id="selectLastPointsOrderByUserId" resultType="com.dora.entity.Order">
        SELECT * FROM `order`
        WHERE user_id = #{userId}
          AND order_type = 2
          AND is_deleted = 0
        <if test="status != null">
            AND status = #{status}
        </if>
        ORDER BY paid_at DESC
        LIMIT 1
    </select>

</mapper>

