package com.erp.report.repository;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.erp.report.entity.PurchaseOrderEntity;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * 采购订单Repository
 */
@Mapper
public interface PurchaseOrderRepository extends BaseMapper<PurchaseOrderEntity> {

    /**
     * 采购汇总统计
     */
    @Select("""
        SELECT 
            COUNT(*) as totalCount,
            SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) as completedCount,
            SUM(CASE WHEN status = 'under_review' THEN 1 ELSE 0 END) as underReviewCount,
            SUM(CASE WHEN status = 'approved' THEN 1 ELSE 0 END) as approvedCount,
            SUM(CASE WHEN status = 'rejected' THEN 1 ELSE 0 END) as rejectedCount,
            COALESCE(SUM(total_amount), 0) as totalAmount,
            COALESCE(SUM(freight), 0) as totalFreight,
            COALESCE(SUM(CASE WHEN status = 'completed' THEN total_amount ELSE 0 END), 0) as completedAmount
        FROM purchase_orders po
        WHERE po.deleted_at IS NULL
        AND (:platform IS NULL OR EXISTS (
            SELECT 1 FROM suppliers s WHERE s.id = po.supplier_id AND s.platform = :platform
        ))
        AND (:shopId IS NULL OR EXISTS (
            SELECT 1 FROM suppliers s WHERE s.id = po.supplier_id AND s.shop_id = :shopId
        ))
        AND (:warehouseId IS NULL OR po.warehouse_id = :warehouseId)
        AND (:startDate IS NULL OR po.created_at >= :startDate)
        AND (:endDate IS NULL OR po.created_at <= :endDate)
        AND (:status IS NULL OR po.status = :status)
        """)
    Map<String, Object> selectPurchaseSummary(
            @Param("platform") String platform,
            @Param("shopId") Long shopId,
            @Param("warehouseId") Long warehouseId,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate,
            @Param("status") String status);

    /**
     * 采购订单列表（分页）
     */
    @Select("""
        SELECT po.id, po.po_no, po.warehouse_id, po.supplier_id, po.brand_id,
               po.expected_arrival, po.shipping_method, po.total_amount, po.freight,
               po.status, po.created_at,
               w.name as warehouse_name,
               s.name as supplier_name, s.platform,
               b.name as brand_name
        FROM purchase_orders po
        LEFT JOIN warehouses w ON po.warehouse_id = w.id
        LEFT JOIN suppliers s ON po.supplier_id = s.id
        LEFT JOIN brands b ON po.brand_id = b.id
        WHERE po.deleted_at IS NULL
        AND (:platform IS NULL OR s.platform = :platform)
        AND (:shopId IS NULL OR s.shop_id = :shopId)
        AND (:warehouseId IS NULL OR po.warehouse_id = :warehouseId)
        AND (:startDate IS NULL OR po.created_at >= :startDate)
        AND (:endDate IS NULL OR po.created_at <= :endDate)
        AND (:status IS NULL OR po.status = :status)
        ORDER BY po.created_at ${trend}
        """)
    List<Map<String, Object>> selectPurchaseList(
            @Param("platform") String platform,
            @Param("shopId") Long shopId,
            @Param("warehouseId") Long warehouseId,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate,
            @Param("status") String status,
            @Param("trend") String trend);

    /**
     * 资金报表 - 采购支出汇总
     */
    @Select("""
        SELECT 
            COUNT(*) as poCount,
            COALESCE(SUM(total_amount), 0) as totalExpense,
            COALESCE(SUM(freight), 0) as freightExpense
        FROM purchase_orders po
        LEFT JOIN suppliers s ON po.supplier_id = s.id
        WHERE po.deleted_at IS NULL
        AND po.status = 'completed'
        AND (:platform IS NULL OR s.platform = :platform)
        AND (:shopId IS NULL OR s.shop_id = :shopId)
        AND (:startDate IS NULL OR po.created_at >= :startDate)
        AND (:endDate IS NULL OR po.created_at <= :endDate)
        """)
    Map<String, Object> selectExpenseSummary(
            @Param("platform") String platform,
            @Param("shopId") Long shopId,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);

    /**
     * 资金报表 - 按日期统计支出
     */
    @Select("""
        SELECT 
            DATE_FORMAT(po.created_at, '%Y-%m-%d') as dateKey,
            COALESCE(SUM(po.total_amount), 0) as expense,
            COUNT(*) as poCount
        FROM purchase_orders po
        LEFT JOIN suppliers s ON po.supplier_id = s.id
        WHERE po.deleted_at IS NULL
        AND po.status = 'completed'
        AND (:platform IS NULL OR s.platform = :platform)
        AND (:shopId IS NULL OR s.shop_id = :shopId)
        AND (:startDate IS NULL OR po.created_at >= :startDate)
        AND (:endDate IS NULL OR po.created_at <= :endDate)
        GROUP BY DATE_FORMAT(po.created_at, '%Y-%m-%d')
        ORDER BY dateKey ${trend}
        """)
    List<Map<String, Object>> selectExpenseByDate(
            @Param("platform") String platform,
            @Param("shopId") Long shopId,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate,
            @Param("trend") String trend);
}
