package com.erp.dashboard.repository;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.erp.dashboard.dto.CloudWarehouseStatsDTO;
import com.erp.dashboard.dto.OrderStatsDTO;
import com.erp.dashboard.dto.RecentOrderDTO;
import com.erp.dashboard.entity.Order;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.time.LocalDateTime;
import java.util.List;

/**
 * 订单Repository
 */
@Mapper
public interface OrderRepository extends BaseMapper<Order> {

    /**
     * 查询本月订单统计
     */
    @Select("""
        SELECT 
            COUNT(*) as totalOrders,
            SUM(CASE WHEN order_status = 'shipped' 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 payment_time BETWEEN #{monthStart} AND #{monthEnd}
          AND deleted_at IS NULL
        """)
    OrderStatsDTO selectMonthOrderStats(@Param("tenantId") Long tenantId,
                                         @Param("monthStart") LocalDateTime monthStart,
                                         @Param("monthEnd") LocalDateTime monthEnd);

    /**
     * 查询近期订单列表
     */
    @Select("""
        SELECT o.id, o.short_id, o.platform, o.shop_name, o.order_status, o.delivery_status,
               o.total_amount, o.goods_amount, o.freight, o.payment_time, 
               o.warehouse_name, o.express_company, o.express_no,
               (SELECT COUNT(*) FROM order_items WHERE order_id = o.id AND deleted_at IS NULL) as itemCount
        FROM orders o
        WHERE o.tenant_id = #{tenantId}
          AND o.payment_time BETWEEN #{startTime} AND #{endTime}
          AND o.deleted_at IS NULL
        ORDER BY o.payment_time DESC
        LIMIT #{limit}
        """)
    List<RecentOrderDTO> selectRecentOrders(@Param("tenantId") Long tenantId,
                                             @Param("startTime") LocalDateTime startTime,
                                             @Param("endTime") LocalDateTime endTime,
                                             @Param("limit") int limit);

    /**
     * 查询本月云仓订单统计
     */
    @Select("""
        SELECT 
            COUNT(o.id) as totalCloudOrders,
            SUM(CASE WHEN o.delivery_status = 'shipped' THEN 1 ELSE 0 END) as shippedOrders
        FROM orders o
        INNER JOIN warehouses w ON o.warehouse_id = w.id
        WHERE o.tenant_id = #{tenantId}
          AND o.payment_time BETWEEN #{monthStart} AND #{monthEnd}
          AND o.warehouse_id IS NOT NULL
          AND o.warehouse_id > 0
          AND w.type = 'cloud'
          AND o.deleted_at IS NULL
          AND w.deleted_at IS NULL
        """)
    CloudWarehouseStatsDTO selectCloudWarehouseStats(@Param("tenantId") Long tenantId,
                                                       @Param("monthStart") LocalDateTime monthStart,
                                                       @Param("monthEnd") LocalDateTime monthEnd);

    /**
     * 统计本月订单数
     */
    @Select("""
        SELECT COUNT(*)
        FROM orders
        WHERE tenant_id = #{tenantId}
          AND payment_time BETWEEN #{monthStart} AND #{monthEnd}
          AND deleted_at IS NULL
        """)
    Long countMonthOrders(@Param("tenantId") Long tenantId,
                          @Param("monthStart") LocalDateTime monthStart,
                          @Param("monthEnd") LocalDateTime monthEnd);

    /**
     * 统计本月云仓未发货订单数
     */
    @Select("""
        SELECT COUNT(o.id)
        FROM orders o
        INNER JOIN warehouses w ON o.warehouse_id = w.id
        WHERE o.tenant_id = #{tenantId}
          AND o.payment_time BETWEEN #{monthStart} AND #{monthEnd}
          AND o.delivery_status != 'shipped'
          AND o.warehouse_id IS NOT NULL
          AND w.type = 'cloud'
          AND o.deleted_at IS NULL
          AND w.deleted_at IS NULL
        """)
    Long countCloudFailedOrders(@Param("tenantId") Long tenantId,
                                 @Param("monthStart") LocalDateTime monthStart,
                                 @Param("monthEnd") LocalDateTime monthEnd);
}
