<?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.tenant.repository.order.OrderRepository">

    <!-- 订单分页查询（使用动态SQL） -->
    <select id="findPage" resultType="com.erp.tenant.entity.order.Order">
        SELECT o.* FROM orders o
        WHERE o.tenant_id = #{tenantId} AND o.deleted_at IS NULL
        <if test="platform != null and platform != ''">
            AND o.platform = #{platform}
        </if>
        <if test="erpStatus != null and erpStatus != ''">
            AND o.erp_status = #{erpStatus}
        </if>
        <if test="warehouseId != null">
            AND o.warehouse_id = #{warehouseId}
        </if>
        <if test="keyword != null and keyword != ''">
            AND (o.order_sn LIKE CONCAT('%', #{keyword}, '%') 
                 OR o.receiver_name LIKE CONCAT('%', #{keyword}, '%') 
                 OR o.receiver_phone LIKE CONCAT('%', #{keyword}, '%')
                 OR o.platform_order_sn LIKE CONCAT('%', #{keyword}, '%'))
        </if>
        <if test="startDate != null and startDate != ''">
            AND o.payment_time &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != ''">
            AND o.payment_time &lt;= #{endDate}
        </if>
        ORDER BY o.created_at DESC
    </select>

    <!-- 本月订单统计 -->
    <select id="getMonthOrderStats" resultType="com.erp.tenant.repository.order.OrderRepository$OrderStats">
        SELECT 
            COUNT(*) as totalOrders,
            SUM(CASE WHEN order_status = 'completed' THEN 1 ELSE 0 END) as completedOrders,
            SUM(CASE WHEN order_status = 'cancelled' THEN 1 ELSE 0 END) as cancelledOrders,
            COALESCE(SUM(total_amount), 0) as totalAmount
        FROM orders
        WHERE tenant_id = #{tenantId} AND deleted_at IS NULL
        AND payment_time BETWEEN #{monthStart} AND #{monthEnd}
    </select>

    <!-- 按日期范围统计订单数 -->
    <select id="countByDateRange" resultType="java.lang.Long">
        SELECT COUNT(*) FROM orders 
        WHERE tenant_id = #{tenantId} AND deleted_at IS NULL
        AND payment_time BETWEEN #{startDate} AND #{endDate}
    </select>

    <!-- 近期订单（用于Dashboard） -->
    <select id="findRecentOrders" resultType="com.erp.tenant.entity.order.Order">
        SELECT o.* FROM orders o
        WHERE o.tenant_id = #{tenantId} AND o.deleted_at IS NULL
        AND o.payment_time BETWEEN #{startDate} AND #{endDate}
        ORDER BY o.payment_time DESC
        LIMIT #{limit}
    </select>

    <!-- 按平台统计订单 -->
    <select id="statsByPlatform" resultType="com.erp.tenant.repository.order.OrderRepository$OrderPlatformStats">
        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 tenant_id = #{tenantId} AND deleted_at IS NULL
        <if test="startDate != null and startDate != ''">
            AND payment_time &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != ''">
            AND payment_time &lt;= #{endDate}
        </if>
        GROUP BY platform
        ORDER BY orderCount DESC
    </select>

    <!-- 按周期统计订单销售 -->
    <select id="statsByPeriod" resultType="com.erp.tenant.repository.order.OrderRepository$OrderSalesStats">
        SELECT 
            DATE_FORMAT(payment_time, #{dateFormat}) as period,
            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 tenant_id = #{tenantId} AND deleted_at IS NULL
        AND payment_time IS NOT NULL
        <if test="startDate != null and startDate != ''">
            AND payment_time &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != ''">
            AND payment_time &lt;= #{endDate}
        </if>
        GROUP BY DATE_FORMAT(payment_time, #{dateFormat})
        ORDER BY period DESC
    </select>

    <!-- 更新ERP状态 -->
    <update id="updateErpStatus">
        UPDATE orders 
        SET erp_status = #{status}, updated_at = NOW() 
        WHERE id = #{id} AND tenant_id = #{tenantId}
    </update>

    <!-- 审核通过 -->
    <update id="approveOrder">
        UPDATE orders 
        SET erp_status = 'approved', approved_at = NOW(), approved_by = #{operatorId}
        WHERE id = #{id} AND tenant_id = #{tenantId}
    </update>

    <!-- 审核驳回 -->
    <update id="rejectOrder">
        UPDATE orders 
        SET erp_status = 'rejected', rejected_at = NOW(), rejected_by = #{operatorId},
            reject_reason = #{reason}
        WHERE id = #{id} AND tenant_id = #{tenantId}
    </update>

</mapper>
