<?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.inventory.repository.StockRepository">

    <!-- 分页查询库存列表（带条件） -->
    <select id="findByConditions" resultType="com.erp.inventory.entity.Stock">
        SELECT * FROM stocks
        WHERE 1=1
        <if test="skuCode != null and skuCode != ''">
            AND sku_code LIKE CONCAT('%', #{skuCode}, '%')
        </if>
        <if test="skuName != null and skuName != ''">
            AND sku_name LIKE CONCAT('%', #{skuName}, '%')
        </if>
        <if test="warehouseId != null">
            AND warehouse_id = #{warehouseId}
        </if>
        <if test="lowStock != null and lowStock == true">
            AND (quantity - COALESCE(locked_quantity, 0) - COALESCE(reserved_quantity, 0) - COALESCE(defective_quantity, 0)) &lt;= COALESCE(warning_threshold, 0)
        </if>
        ORDER BY updated_at DESC
    </select>

    <!-- 查询低库存预警列表 -->
    <select id="findLowStockWarnings" resultType="com.erp.inventory.entity.Stock">
        SELECT * FROM stocks
        WHERE (quantity - COALESCE(locked_quantity, 0) - COALESCE(reserved_quantity, 0) - COALESCE(defective_quantity, 0)) &lt;= COALESCE(warning_threshold, 0)
        <if test="warehouseId != null">
            AND warehouse_id = #{warehouseId}
        </if>
        ORDER BY (quantity - COALESCE(locked_quantity, 0) - COALESCE(reserved_quantity, 0) - COALESCE(defective_quantity, 0)) ASC
    </select>

</mapper>
