package com.erp.report.repository;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.erp.report.entity.StockEntity;
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 StockRepository extends BaseMapper<StockEntity> {

    /**
     * 库存概览统计
     */
    @Select("""
        SELECT 
            COUNT(*) as totalStocks,
            COALESCE(SUM(quantity), 0) as totalQuantity,
            COALESCE(SUM(quantity - locked_quantity - reserved_quantity - defective_quantity), 0) as totalAvailable,
            SUM(CASE WHEN (quantity - locked_quantity - reserved_quantity - defective_quantity) <= warning_threshold AND warning_threshold > 0 THEN 1 ELSE 0 END) as lowStockCount,
            SUM(CASE WHEN (quantity - locked_quantity - reserved_quantity - defective_quantity) <= 0 THEN 1 ELSE 0 END) as outOfStockCount
        FROM stocks
        WHERE deleted_at IS NULL
        AND (:warehouseId IS NULL OR warehouse_id = :warehouseId)
        """)
    Map<String, Object> selectStockOverview(@Param("warehouseId") Long warehouseId);

    /**
     * 查询呆滞库存SKU列表
     */
    @Select("""
        SELECT s.sku_code, s.sku_name, s.warehouse_id, s.quantity,
               (s.quantity - s.locked_quantity - s.reserved_quantity - s.defective_quantity) as available,
               w.name as warehouse_name,
               (SELECT MAX(created_at) FROM stock_logs sl WHERE sl.sku_code = s.sku_code AND sl.warehouse_id = s.warehouse_id AND created_at >= :cutoffDate) as last_move_at
        FROM stocks s
        LEFT JOIN warehouses w ON s.warehouse_id = w.id
        WHERE s.deleted_at IS NULL
        AND s.quantity > 0
        AND s.sku_code NOT IN (
            SELECT DISTINCT sku_code FROM stock_logs 
            WHERE deleted_at IS NULL AND created_at >= :cutoffDate
        )
        AND (:warehouseId IS NULL OR s.warehouse_id = :warehouseId)
        ORDER BY s.quantity ${trend}
        """)
    List<Map<String, Object>> selectSlowMovingStocks(
            @Param("warehouseId") Long warehouseId,
            @Param("cutoffDate") String cutoffDate,
            @Param("trend") String trend);

    /**
     * 查询30天内有变动的SKU及其出库总量
     */
    @Select("""
        SELECT sku_code, SUM(ABS(change_quantity)) as total_out
        FROM stock_logs
        WHERE deleted_at IS NULL
        AND type = 'outbound'
        AND created_at >= :cutoffDate
        GROUP BY sku_code
        """)
    List<Map<String, Object>> selectOutboundSummary(@Param("cutoffDate") String cutoffDate);

    /**
     * 库存周转率统计
     */
    @Select("""
        SELECT s.sku_code, s.sku_name, s.warehouse_id, s.quantity,
               (s.quantity - s.locked_quantity - s.reserved_quantity - s.defective_quantity) as available,
               w.name as warehouse_name
        FROM stocks s
        LEFT JOIN warehouses w ON s.warehouse_id = w.id
        WHERE s.deleted_at IS NULL
        AND s.quantity > 0
        AND (:warehouseId IS NULL OR s.warehouse_id = :warehouseId)
        ORDER BY s.quantity ${trend}
        """)
    List<Map<String, Object>> selectTurnoverStocks(
            @Param("warehouseId") Long warehouseId,
            @Param("trend") String trend);

    /**
     * 整体库存周转率
     */
    @Select("""
        SELECT 
            COALESCE(SUM(outbound.total_out), 0) as totalOut,
            COALESCE(SUM(s.quantity), 0) as totalStock
        FROM stocks s
        LEFT JOIN (
            SELECT sku_code, SUM(ABS(change_quantity)) as total_out
            FROM stock_logs
            WHERE deleted_at IS NULL AND type = 'outbound' AND created_at >= :cutoffDate
            GROUP BY sku_code
        ) outbound ON s.sku_code = outbound.sku_code
        WHERE s.deleted_at IS NULL
        AND (:warehouseId IS NULL OR s.warehouse_id = :warehouseId)
        """)
    Map<String, Object> selectOverallTurnover(
            @Param("warehouseId") Long warehouseId,
            @Param("cutoffDate") String cutoffDate);
}
