<?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.czg.service.market.mapper.PpPackageOrderMapper">

    <select id="getOrderList" resultType="com.czg.market.vo.PpOrderListVO">
        SELECT
          o.id, o.shop_id, o.order_no, o.package_id, o.share_num, o.status, o.final_price, o.pay_time, o.verify_code, o.verify_time,
          s.shop_name as shopName, s.address as shopAddress,
          p.origin_price as originPrice, p.price, p.images as imagesStr, p.tiered_discount as tieredDiscountStr,
          p.expire_hours as expireHours, p.package_name as packageName,
          u.head_img as avatar, u.nick_name as nickname, u.phone
        from pp_package_order o
        left join tb_shop_info s on o.shop_id = s.id
        left join pp_package p on o.package_id = p.id
        left join tb_user_info u on o.user_id = u.id
        where 1=1
        <if test="shopId != null">
            and o.shop_id = #{shopId}
        </if>
        <if test="userId != null">
            and o.user_id = #{userId}
        </if>
        <if test="param.orderNo != null">
            and o.order_no = #{param.orderNo}
        </if>
        <if test="param.status != null">
            <choose>
                <!-- 当 status 传参为 'ref' 时，匹配 refund 或 refunding -->
                <when test="param.status == 'ref'">
                    and o.status in ('refund', 'refunding')
                </when>
                <!-- 其他非空情况，保持原有的精准匹配逻辑 -->
                <otherwise>
                    and o.status = #{param.status}
                </otherwise>
            </choose>
        </if>
        <if test="param.orderStartTime != null and param.orderEndTime != null ">
            and o.pay_time BETWEEN #{param.orderStartTime} and #{param.orderEndTime}
        </if>
        <if test="param.verifyStartTime != null and param.verifyEndTime != null ">
            and o.verify_time BETWEEN #{param.verifyStartTime} and #{param.verifyEndTime}
        </if>
        <if test="param.name != null">
            and p.package_name like concat('%', #{param.name}, '%')
        </if>
        order by o.create_time desc
    </select>

    <select id="getOrderCount" resultType="com.czg.order.vo.GbOrderCountVO">
        SELECT
        COUNT(*) AS countNum,
        COALESCE(SUM(IF(pay_time IS NOT NULL, IFNULL(final_price, 0), 0)), 0) AS paidAmountTotal,
        COALESCE(SUM(IF(status IN ('refunding', 'refund'), IFNULL(o.final_price, 0), 0)), 0) AS refundAmount
        from pp_package_order o
        left join pp_package p on o.package_id = p.id
        where o.shop_id = #{shopId}
        <if test="param.orderNo != null">
            and o.order_no = #{param.orderNo}
        </if>
        <if test="param.status != null">
            and o.status = #{param.status}
        </if>
        <if test="param.orderStartTime != null and param.orderEndTime != null ">
            and o.pay_time BETWEEN #{param.orderStartTime} and #{param.orderEndTime}
        </if>
        <if test="param.verifyStartTime != null and param.verifyEndTime != null ">
            and o.verify_time BETWEEN #{param.verifyStartTime} and #{param.verifyEndTime}
        </if>
        <if test="param.name != null">
            and p.package_name like concat('%', #{param.name}, '%')
        </if>
    </select>

</mapper>
