package com.erp.dashboard.repository;

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

/**
 * 库存Repository
 */
@Mapper
public interface StockRepository extends BaseMapper<Stock> {

    /**
     * 统计严重缺货商品数（库存低于预警值50%）
     */
    @Select("""
        SELECT COUNT(*)
        FROM stocks s
        INNER JOIN warehouses w ON s.warehouse_id = w.id
        WHERE w.tenant_id = #{tenantId}
          AND (s.quantity - s.locked_quantity - s.reserved_quantity - s.defective_quantity) 
              <= (LEAST(s.warning_threshold, #{globalThreshold}) * 0.5)
          AND s.deleted_at IS NULL
          AND w.deleted_at IS NULL
        """)
    Long countCriticalStock(@Param("tenantId") Long tenantId, 
                            @Param("globalThreshold") Integer globalThreshold);

    /**
     * 统计库存预警商品数（在预警值50%~100%之间）
     */
    @Select("""
        SELECT COUNT(*)
        FROM stocks s
        INNER JOIN warehouses w ON s.warehouse_id = w.id
        WHERE w.tenant_id = #{tenantId}
          AND (s.quantity - s.locked_quantity - s.reserved_quantity - s.defective_quantity) 
              > (LEAST(s.warning_threshold, #{globalThreshold}) * 0.5)
          AND (s.quantity - s.locked_quantity - s.reserved_quantity - s.defective_quantity) 
              <= LEAST(s.warning_threshold, #{globalThreshold})
          AND s.deleted_at IS NULL
          AND w.deleted_at IS NULL
        """)
    Long countWarningStock(@Param("tenantId") Long tenantId,
                           @Param("globalThreshold") Integer globalThreshold);
}
