<?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.ruoyi.jarvis.mapper.GiftCouponMapper">

    <resultMap id="GiftCouponResult" type="GiftCoupon">
        <result property="giftCouponKey" column="gift_coupon_key"/>
        <result property="skuId" column="sku_id"/>
        <result property="skuName" column="sku_name"/>
        <result property="owner" column="owner"/>
        <result property="amount" column="amount"/>
        <result property="quantity" column="quantity"/>
        <result property="usedQuantity" column="used_quantity"/>
        <result property="totalOcsAmount" column="total_ocs_amount"/>
        <result property="useCount" column="use_count"/>
        <result property="createTime" column="create_time"/>
        <result property="expireTime" column="expire_time"/>
        <result property="isExpired" column="is_expired"/>
    </resultMap>

    <sql id="selectGiftCouponBase">
        SELECT 
            gift_coupon_key,
            MAX(sku_id) as sku_id,
            MAX(sku_name) as sku_name,
            MAX(owner) as owner,
            COALESCE(SUM(use_count), 0) as use_count,
            COALESCE(SUM(total_ocs_amount), 0) as total_ocs_amount,
            MIN(create_time) as create_time,
            MAX(expire_time) as expire_time,
            CASE 
                WHEN MAX(expire_time) IS NOT NULL AND MAX(expire_time) &lt; NOW() THEN 1 
                ELSE 0 
            END as is_expired
        FROM (
            -- 从order_rows表聚合已使用的礼金数据
            SELECT 
                or_rows.gift_coupon_key,
                CAST(or_rows.sku_id AS CHAR) as sku_id,
                or_rows.sku_name,
                CASE 
                    WHEN or_rows.pop_id IS NULL OR or_rows.pop_id = '' THEN 'g' 
                    ELSE 'pop' 
                END as owner,
                COUNT(DISTINCT or_rows.order_id) as use_count,
                COALESCE(SUM(or_rows.gift_coupon_ocs_amount), 0) as total_ocs_amount,
                MIN(or_rows.order_time) as create_time,
                NULL as expire_time
            FROM order_rows or_rows
            WHERE or_rows.gift_coupon_key IS NOT NULL AND or_rows.gift_coupon_key != ''
            <if test="giftCouponKey != null and giftCouponKey != ''">
                AND or_rows.gift_coupon_key like concat('%', #{giftCouponKey}, '%')
            </if>
            <if test="skuId != null and skuId != ''">
                AND CAST(or_rows.sku_id AS CHAR) like concat('%', #{skuId}, '%')
            </if>
            <if test="skuName != null and skuName != ''">
                AND or_rows.sku_name like concat('%', #{skuName}, '%')
            </if>
            <if test="owner != null and owner != ''">
                AND CASE 
                    WHEN or_rows.pop_id IS NULL OR or_rows.pop_id = '' THEN 'g' 
                    ELSE 'pop' 
                END = #{owner}
            </if>
            <if test="params.beginTime != null and params.beginTime != ''">
                AND or_rows.order_time &gt;= #{params.beginTime}
            </if>
            <if test="params.endTime != null and params.endTime != ''">
                AND or_rows.order_time &lt;= #{params.endTime}
            </if>
            GROUP BY or_rows.gift_coupon_key, or_rows.sku_id, or_rows.sku_name, or_rows.pop_id
            
            UNION ALL
            
            -- 从gift_coupon表获取所有创建的礼金（包括未使用的）
            SELECT 
                gc.gift_coupon_key,
                gc.sku_id,
                gc.sku_name,
                gc.owner,
                0 as use_count,
                0 as total_ocs_amount,
                gc.create_time,
                gc.expire_time
            FROM gift_coupon gc
            WHERE 1=1
            <if test="giftCouponKey != null and giftCouponKey != ''">
                AND gc.gift_coupon_key like concat('%', #{giftCouponKey}, '%')
            </if>
            <if test="skuId != null and skuId != ''">
                AND gc.sku_id like concat('%', #{skuId}, '%')
            </if>
            <if test="skuName != null and skuName != ''">
                AND gc.sku_name like concat('%', #{skuName}, '%')
            </if>
            <if test="owner != null and owner != ''">
                AND gc.owner = #{owner}
            </if>
            <if test="params.beginTime != null and params.beginTime != ''">
                AND gc.create_time &gt;= #{params.beginTime}
            </if>
            <if test="params.endTime != null and params.endTime != ''">
                AND gc.create_time &lt;= #{params.endTime}
            </if>
        ) gc
        GROUP BY gift_coupon_key
        <if test="isExpired != null">
            HAVING CASE 
                WHEN MAX(expire_time) IS NOT NULL AND MAX(expire_time) &lt; NOW() THEN 1 
                ELSE 0 
            END = #{isExpired}
        </if>
    </sql>

    <select id="selectGiftCouponList" parameterType="GiftCoupon" resultMap="GiftCouponResult">
        <include refid="selectGiftCouponBase"/>
        ORDER BY create_time DESC, gift_coupon_key DESC
    </select>

    <select id="selectGiftCouponByKey" parameterType="String" resultMap="GiftCouponResult">
        SELECT 
            gift_coupon_key,
            MAX(sku_id) as sku_id,
            MAX(sku_name) as sku_name,
            MAX(owner) as owner,
            COUNT(DISTINCT order_id) as use_count,
            SUM(gift_coupon_ocs_amount) as total_ocs_amount,
            MIN(order_time) as create_time,
            MAX(expire_time) as expire_time,
            CASE 
                WHEN MAX(expire_time) IS NOT NULL AND MAX(expire_time) &lt; NOW() THEN 1 
                ELSE 0 
            END as is_expired
        FROM (
            SELECT 
                or_rows.gift_coupon_key,
                or_rows.sku_id,
                or_rows.sku_name,
                CASE 
                    WHEN or_rows.pop_id IS NULL OR or_rows.pop_id = '' THEN 'g' 
                    ELSE 'pop' 
                END as owner,
                or_rows.order_id,
                or_rows.gift_coupon_ocs_amount,
                or_rows.order_time,
                NULL as expire_time
            FROM order_rows or_rows
            WHERE or_rows.gift_coupon_key IS NOT NULL AND or_rows.gift_coupon_key != ''
            AND or_rows.gift_coupon_key = #{giftCouponKey}
        ) gc
        GROUP BY gift_coupon_key
    </select>

    <select id="selectGiftCouponStatistics" parameterType="GiftCoupon" resultMap="GiftCouponResult">
        SELECT 
            COUNT(*) as use_count,
            COALESCE(SUM(total_ocs_amount), 0) as total_ocs_amount
        FROM (
            SELECT 
                gift_coupon_key,
                use_count,
                total_ocs_amount
            FROM (
                <include refid="selectGiftCouponBase"/>
            ) grouped
        ) stats
    </select>

    <insert id="insertGiftCoupon" parameterType="GiftCoupon">
        INSERT INTO gift_coupon (
            gift_coupon_key,
            sku_id,
            sku_name,
            owner,
            amount,
            quantity,
            create_time,
            expire_time
        ) VALUES (
            #{giftCouponKey},
            #{skuId},
            #{skuName},
            #{owner},
            #{amount},
            #{quantity},
            #{createTime},
            #{expireTime}
        )
        ON DUPLICATE KEY UPDATE
            sku_id = VALUES(sku_id),
            sku_name = VALUES(sku_name),
            owner = VALUES(owner),
            amount = VALUES(amount),
            quantity = VALUES(quantity),
            expire_time = VALUES(expire_time)
    </insert>

    <update id="updateGiftCoupon" parameterType="GiftCoupon">
        UPDATE gift_coupon
        <set>
            <if test="skuId != null and skuId != ''">sku_id = #{skuId},</if>
            <if test="skuName != null and skuName != ''">sku_name = #{skuName},</if>
            <if test="owner != null and owner != ''">owner = #{owner},</if>
            <if test="amount != null">amount = #{amount},</if>
            <if test="quantity != null">quantity = #{quantity},</if>
            <if test="expireTime != null">expire_time = #{expireTime},</if>
        </set>
        WHERE gift_coupon_key = #{giftCouponKey}
    </update>

</mapper>
