package com.erp.tenant.repository.order;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.erp.tenant.entity.order.Order;
import org.apache.ibatis.annotations.*;

/**
 * 订单数据访问
 */
@Mapper
public interface OrderRepository extends BaseMapper<Order> {

    // ==================== 基础查询 ====================

    @Select("SELECT * FROM orders WHERE id = #{id} AND tenant_id = #{tenantId} AND deleted_at IS NULL")
    Order findById(@Param("id") Long id, @Param("tenantId") Long tenantId);

    @Select("SELECT * FROM orders WHERE order_sn = #{orderSn} AND tenant_id = #{tenantId} AND deleted_at IS NULL")
    Order findByOrderSn(@Param("orderSn") String orderSn, @Param("tenantId") Long tenantId);

    // ==================== 分页查询 ====================

    @Select("<script>" +
            "SELECT o.* FROM orders o " +
            "WHERE o.tenant_id = #{tenantId} AND o.deleted_at IS NULL " +
            "<if test='platform != null'> AND o.platform = #{platform} </if>" +
            "<if test='erpStatus != null'> AND o.erp_status = #{erpStatus} </if>" +
            "<if test='warehouseId != null'> AND o.warehouse_id = #{warehouseId} </if>" +
            "<if test='keyword != null'> AND (o.order_sn LIKE CONCAT('%', #{keyword}, '%') OR o.receiver_name LIKE CONCAT('%', #{keyword}, '%') OR o.receiver_phone LIKE CONCAT('%', #{keyword}, '%')) </if>" +
            "<if test='startDate != null'> AND o.payment_time &gt;= #{startDate} </if>" +
            "<if test='endDate != null'> AND o.payment_time &lt;= #{endDate} </if>" +
            "ORDER BY o.created_at DESC" +
            "</script>")
    IPage<Order> findPage(Page<Order> page,
                          @Param("tenantId") Long tenantId,
                          @Param("platform") String platform,
                          @Param("erpStatus") String erpStatus,
                          @Param("warehouseId") Long warehouseId,
                          @Param("keyword") String keyword,
                          @Param("startDate") String startDate,
                          @Param("endDate") String endDate);

    // ==================== Dashboard统计 ====================

    @Select("SELECT COUNT(*) as total_orders, " +
            "SUM(CASE WHEN order_status = 'completed' THEN 1 ELSE 0 END) as completed_orders, " +
            "SUM(CASE WHEN order_status = 'cancelled' THEN 1 ELSE 0 END) as cancelled_orders, " +
            "COALESCE(SUM(total_amount), 0) as total_amount " +
            "FROM orders " +
            "WHERE tenant_id = #{tenantId} AND deleted_at IS NULL " +
            "AND payment_time BETWEEN #{monthStart} AND #{monthEnd}")
    OrderStats getMonthOrderStats(@Param("tenantId") Long tenantId,
                                   @Param("monthStart") String monthStart,
                                   @Param("monthEnd") String monthEnd);

    @Select("SELECT COUNT(*) FROM orders WHERE tenant_id = #{tenantId} AND deleted_at IS NULL " +
            "AND payment_time BETWEEN #{startDate} AND #{endDate}")
    Long countByDateRange(@Param("tenantId") Long tenantId,
                          @Param("startDate") String startDate,
                          @Param("endDate") String endEnd);

    @Select("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}")
    java.util.List<Order> findRecentOrders(@Param("tenantId") Long tenantId,
                                          @Param("startDate") String startDate,
                                          @Param("endDate") String endDate,
                                          @Param("limit") int limit);

    // ==================== 报表统计 ====================

    @Select("<script>" +
            "SELECT platform, COUNT(*) as order_count, " +
            "COALESCE(SUM(total_amount), 0) as total_amount, " +
            "COALESCE(SUM(goods_amount), 0) as goods_amount, " +
            "COALESCE(SUM(freight), 0) as freight, " +
            "SUM(CASE WHEN order_status = 'completed' THEN 1 ELSE 0 END) as completed_count, " +
            "SUM(CASE WHEN order_status = 'cancelled' THEN 1 ELSE 0 END) as cancelled_count " +
            "FROM orders " +
            "WHERE tenant_id = #{tenantId} AND deleted_at IS NULL " +
            "<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 order_count DESC" +
            "</script>")
    java.util.List<OrderPlatformStats> statsByPlatform(@Param("tenantId") Long tenantId,
                                                       @Param("startDate") String startDate,
                                                       @Param("endDate") String endDate);

    @Select("<script>" +
            "SELECT DATE_FORMAT(payment_time, #{dateFormat}) as period, " +
            "COUNT(*) as order_count, " +
            "COALESCE(SUM(total_amount), 0) as sales_amount, " +
            "COALESCE(SUM(goods_amount), 0) as goods_amount, " +
            "COALESCE(AVG(total_amount), 0) as avg_order_amount, " +
            "SUM(CASE WHEN order_status = 'completed' THEN 1 ELSE 0 END) as completed_count " +
            "FROM orders " +
            "WHERE tenant_id = #{tenantId} AND deleted_at IS NULL " +
            "AND payment_time IS NOT NULL " +
            "<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 period DESC" +
            "</script>")
    java.util.List<OrderSalesStats> statsByPeriod(@Param("tenantId") Long tenantId,
                                                  @Param("startDate") String startDate,
                                                  @Param("endDate") String endDate,
                                                  @Param("dateFormat") String dateFormat);

    // ==================== 状态更新 ====================

    @Update("UPDATE orders SET erp_status = #{status}, updated_at = NOW() " +
            "WHERE id = #{id} AND tenant_id = #{tenantId}")
    int updateErpStatus(@Param("id") Long id, @Param("tenantId") Long tenantId, @Param("status") String status);

    @Update("UPDATE orders SET erp_status = 'approved', approved_at = NOW(), approved_by = #{operatorId} " +
            "WHERE id = #{id} AND tenant_id = #{tenantId}")
    int approveOrder(@Param("id") Long id, @Param("tenantId") Long tenantId, @Param("operatorId") Long operatorId);

    @Update("UPDATE orders SET erp_status = 'rejected', rejected_at = NOW(), rejected_by = #{operatorId}, " +
            "reject_reason = #{reason} WHERE id = #{id} AND tenant_id = #{tenantId}")
    int rejectOrder(@Param("id") Long id, @Param("tenantId") Long tenantId,
                    @Param("operatorId") Long operatorId, @Param("reason") String reason);

    // ==================== 内部类 ====================

    class OrderStats {
        public Long totalOrders;
        public Long completedOrders;
        public Long cancelledOrders;
        public java.math.BigDecimal totalAmount;
    }

    class OrderPlatformStats {
        public String platform;
        public Long orderCount;
        public java.math.BigDecimal totalAmount;
        public java.math.BigDecimal goodsAmount;
        public java.math.BigDecimal freight;
        public Long completedCount;
        public Long cancelledCount;
    }

    class OrderSalesStats {
        public String period;
        public Long orderCount;
        public java.math.BigDecimal salesAmount;
        public java.math.BigDecimal goodsAmount;
        public java.math.BigDecimal avgOrderAmount;
        public Long completedCount;
    }
}
