<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
    "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.erp.tenant.repository.stock.StockRepository">

    <!-- 库存预警列表 -->
    <select id="findAlertPage" resultType="com.erp.tenant.entity.stock.Stock">
        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 keyword != ''">
            AND (s.sku_code LIKE CONCAT('%', #{keyword}, '%') 
                 OR s.sku_name LIKE CONCAT('%', #{keyword}, '%'))
        </if>
        <if test="alertLevel == 'critical'">
            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 == 'warning'">
            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
    </select>

    <!-- 库存列表 -->
    <select id="findPage" resultType="com.erp.tenant.entity.stock.Stock">
        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
    </select>

    <!-- 按仓库统计库存数量 -->
    <select id="countByWarehouse" resultType="java.lang.Long">
        SELECT COUNT(*) FROM stocks 
        WHERE tenant_id = #{tenantId} AND deleted_at IS NULL
        <if test="warehouseId != null">
            AND warehouse_id = #{warehouseId}
        </if>
    </select>

    <!-- 统计总库存数量 -->
    <select id="sumQuantityByTenant" resultType="java.lang.Integer">
        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>
    </select>

    <!-- 低库存预警统计 -->
    <select id="countLowStock" resultType="java.lang.Long">
        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>
    </select>

    <!-- 缺货统计 -->
    <select id="countOutOfStock" resultType="java.lang.Long">
        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>
    </select>

</mapper>
