package com.erp.tenant.repository.stock;

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.stock.Stock;
import org.apache.ibatis.annotations.*;

/**
 * 库存数据访问
 */
@Mapper
public interface StockRepository extends BaseMapper<Stock> {

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

    @Select("SELECT * FROM stocks WHERE warehouse_id = #{warehouseId} AND sku_code = #{skuCode} " +
            "AND tenant_id = #{tenantId} AND deleted_at IS NULL")
    Stock findByWarehouseAndSku(@Param("warehouseId") Long warehouseId,
                                 @Param("skuCode") String skuCode,
                                 @Param("tenantId") Long tenantId);

    @Select("<script>" +
            "SELECT s.* FROM stocks s " +
            "WHERE s.tenant_id = #{tenantId} AND s.deleted_at IS NULL " +
            "<if test='warehouseId != null'> AND s.warehouse_id = #{warehouseId} </if>" +
            "<if test='keyword != null'> AND (s.sku_code LIKE CONCAT('%', #{keyword}, '%') OR s.sku_name LIKE CONCAT('%', #{keyword}, '%')) </if>" +
            "<if test='alertLevel == &quot;critical&quot;'> AND (s.quantity - COALESCE(s.locked_quantity,0) - COALESCE(s.reserved_quantity,0) - COALESCE(s.defective_quantity,0)) &lt;= (LEAST(s.warning_threshold, #{globalThreshold}) * 0.5) </if>" +
            "<if test='alertLevel == &quot;warning&quot;'> AND (s.quantity - COALESCE(s.locked_quantity,0) - COALESCE(s.reserved_quantity,0) - COALESCE(s.defective_quantity,0)) &gt; (LEAST(s.warning_threshold, #{globalThreshold}) * 0.5) AND (s.quantity - COALESCE(s.locked_quantity,0) - COALESCE(s.reserved_quantity,0) - COALESCE(s.defective_quantity,0)) &lt;= LEAST(s.warning_threshold, #{globalThreshold}) </if>" +
            "ORDER BY (s.quantity - COALESCE(s.locked_quantity,0) - COALESCE(s.reserved_quantity,0) - COALESCE(s.defective_quantity,0)) ASC" +
            "</script>")
    IPage<Stock> findAlertPage(Page<Stock> page,
                               @Param("tenantId") Long tenantId,
                               @Param("warehouseId") Long warehouseId,
                               @Param("keyword") String keyword,
                               @Param("alertLevel") String alertLevel,
                               @Param("globalThreshold") int globalThreshold);

    @Select("<script>" +
            "SELECT s.* FROM stocks s " +
            "WHERE s.tenant_id = #{tenantId} AND s.deleted_at IS NULL " +
            "<if test='warehouseId != null'> AND s.warehouse_id = #{warehouseId} </if>" +
            "ORDER BY s.updated_at DESC" +
            "</script>")
    IPage<Stock> findPage(Page<Stock> page,
                          @Param("tenantId") Long tenantId,
                          @Param("warehouseId") Long warehouseId);

    @Select("<script>" +
            "SELECT COUNT(*) FROM stocks WHERE tenant_id = #{tenantId} AND deleted_at IS NULL " +
            "<if test='warehouseId != null'> AND warehouse_id = #{warehouseId} </if>" +
            "</script>")
    Long countByWarehouse(@Param("tenantId") Long tenantId, @Param("warehouseId") Long warehouseId);

    @Select("<script>" +
            "SELECT COALESCE(SUM(quantity), 0) FROM stocks WHERE tenant_id = #{tenantId} AND deleted_at IS NULL " +
            "<if test='warehouseId != null'> AND warehouse_id = #{warehouseId} </if>" +
            "</script>")
    Integer sumQuantityByTenant(@Param("tenantId") Long tenantId, @Param("warehouseId") Long warehouseId);

    @Select("<script>" +
            "SELECT COUNT(*) FROM stocks " +
            "WHERE tenant_id = #{tenantId} AND deleted_at IS NULL " +
            "AND (quantity - COALESCE(locked_quantity,0) - COALESCE(reserved_quantity,0) - COALESCE(defective_quantity,0)) &lt;= warning_threshold " +
            "<if test='warehouseId != null'> AND warehouse_id = #{warehouseId} </if>" +
            "</script>")
    Long countLowStock(@Param("tenantId") Long tenantId, @Param("warehouseId") Long warehouseId);

    @Select("<script>" +
            "SELECT COUNT(*) FROM stocks " +
            "WHERE tenant_id = #{tenantId} AND deleted_at IS NULL " +
            "AND (quantity - COALESCE(locked_quantity,0) - COALESCE(reserved_quantity,0) - COALESCE(defective_quantity,0)) &lt;= 0 " +
            "<if test='warehouseId != null'> AND warehouse_id = #{warehouseId} </if>" +
            "</script>")
    Long countOutOfStock(@Param("tenantId") Long tenantId, @Param("warehouseId") Long warehouseId);
}
