<?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.erp.report.repository.OrderRepository">

    <!-- 订单统计 - 按平台分组 (使用动态ORDER BY) -->
    <select id="selectGroupByPlatformDynamic" resultType="map">
        SELECT 
            platform,
            COUNT(*) as orderCount,
            COALESCE(SUM(total_amount), 0) as totalAmount,
            COALESCE(SUM(goods_amount), 0) as goodsAmount,
            COALESCE(SUM(freight), 0) as freight,
            SUM(CASE WHEN order_status = 'completed' THEN 1 ELSE 0 END) as completedCount,
            SUM(CASE WHEN order_status = 'cancelled' THEN 1 ELSE 0 END) as cancelledCount
        FROM orders
        WHERE deleted_at IS NULL
        AND payment_time IS NOT NULL
        <if test="platform != null">
            AND platform = #{platform}
        </if>
        <if test="shopId != null">
            AND shop_id = #{shopId}
        </if>
        <if test="startDate != null">
            AND payment_time &gt;= #{startDate}
        </if>
        <if test="endDate != null">
            AND payment_time &lt;= #{endDate}
        </if>
        GROUP BY platform
        ORDER BY orderCount ${trend}
    </select>

    <!-- 订单统计 - 按店铺分组 -->
    <select id="selectGroupByShopDynamic" resultType="map">
        SELECT 
            shop_id,
            shop_name,
            platform,
            COUNT(*) as orderCount,
            COALESCE(SUM(total_amount), 0) as totalAmount,
            COALESCE(SUM(goods_amount), 0) as goodsAmount,
            COALESCE(SUM(freight), 0) as freight,
            SUM(CASE WHEN order_status = 'completed' THEN 1 ELSE 0 END) as completedCount,
            SUM(CASE WHEN order_status = 'cancelled' THEN 1 ELSE 0 END) as cancelledCount
        FROM orders
        WHERE deleted_at IS NULL
        AND payment_time IS NOT NULL
        <if test="platform != null">
            AND platform = #{platform}
        </if>
        <if test="shopId != null">
            AND shop_id = #{shopId}
        </if>
        <if test="startDate != null">
            AND payment_time &gt;= #{startDate}
        </if>
        <if test="endDate != null">
            AND payment_time &lt;= #{endDate}
        </if>
        GROUP BY shop_id, shop_name, platform
        ORDER BY orderCount ${trend}
    </select>

    <!-- 订单统计 - 按日期分组 -->
    <select id="selectGroupByDateDynamic" resultType="map">
        SELECT 
            DATE_FORMAT(payment_time, #{dateFormat}) as dateKey,
            COUNT(*) as orderCount,
            COALESCE(SUM(total_amount), 0) as totalAmount,
            COALESCE(SUM(goods_amount), 0) as goodsAmount,
            COALESCE(SUM(freight), 0) as freight
        FROM orders
        WHERE deleted_at IS NULL
        AND payment_time IS NOT NULL
        <if test="platform != null">
            AND platform = #{platform}
        </if>
        <if test="shopId != null">
            AND shop_id = #{shopId}
        </if>
        <if test="startDate != null">
            AND payment_time &gt;= #{startDate}
        </if>
        <if test="endDate != null">
            AND payment_time &lt;= #{endDate}
        </if>
        GROUP BY DATE_FORMAT(payment_time, #{dateFormat})
        ORDER BY dateKey ${trend}
    </select>

    <!-- 销售统计 - 按日期列表 -->
    <select id="selectSalesByDateDynamic" resultType="map">
        SELECT 
            DATE_FORMAT(payment_time, #{dateFormat}) as dateKey,
            COUNT(*) as orderCount,
            COALESCE(SUM(total_amount), 0) as salesAmount,
            COALESCE(SUM(goods_amount), 0) as goodsAmount,
            COALESCE(AVG(total_amount), 0) as avgOrderAmount,
            SUM(CASE WHEN order_status = 'completed' THEN 1 ELSE 0 END) as completedCount
        FROM orders
        WHERE deleted_at IS NULL
        AND payment_time IS NOT NULL
        <if test="platform != null">
            AND platform = #{platform}
        </if>
        <if test="shopId != null">
            AND shop_id = #{shopId}
        </if>
        <if test="startDate != null">
            AND payment_time &gt;= #{startDate}
        </if>
        <if test="endDate != null">
            AND payment_time &lt;= #{endDate}
        </if>
        GROUP BY DATE_FORMAT(payment_time, #{dateFormat})
        ORDER BY dateKey ${trend}
    </select>

    <!-- 资金报表 - 按日期统计收入 -->
    <select id="selectIncomeByDateDynamic" resultType="map">
        SELECT 
            DATE_FORMAT(payment_time, '%Y-%m-%d') as dateKey,
            COALESCE(SUM(total_amount), 0) as income,
            COUNT(*) as orderCount
        FROM orders
        WHERE deleted_at IS NULL
        AND order_status = 'completed'
        AND payment_time IS NOT NULL
        <if test="platform != null">
            AND platform = #{platform}
        </if>
        <if test="shopId != null">
            AND shop_id = #{shopId}
        </if>
        <if test="startDate != null">
            AND payment_time &gt;= #{startDate}
        </if>
        <if test="endDate != null">
            AND payment_time &lt;= #{endDate}
        </if>
        GROUP BY DATE_FORMAT(payment_time, '%Y-%m-%d')
        ORDER BY dateKey ${trend}
    </select>

</mapper>
