<?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.StockWarningRecordRepository">

    <!-- 分页查询预警记录 -->
    <select id="findByConditions" resultType="com.erp.inventory.entity.StockWarningRecord">
        SELECT * FROM stock_warning_records
        WHERE 1=1
        <if test="skuCode != null and skuCode != ''">
            AND sku_code LIKE CONCAT('%', #{skuCode}, '%')
        </if>
        <if test="warehouseId != null">
            AND warehouse_id = #{warehouseId}
        </if>
        <if test="ruleType != null and ruleType != ''">
            AND rule_type = #{ruleType}
        </if>
        <if test="status != null and status != ''">
            AND status = #{status}
        </if>
        <if test="level != null and level != ''">
            AND level = #{level}
        </if>
        <if test="startDate != null">
            AND created_at &gt;= #{startDate}
        </if>
        <if test="endDate != null">
            AND created_at &lt;= #{endDate}
        </if>
        ORDER BY created_at DESC
    </select>

    <!-- 查询未解决的预警记录 -->
    <select id="findUnresolved" resultType="com.erp.inventory.entity.StockWarningRecord">
        SELECT * FROM stock_warning_records
        WHERE sku_code = #{skuCode}
        AND warehouse_id = #{warehouseId}
        AND status IN ('pending', 'notified', 'acknowledged')
        ORDER BY created_at DESC
    </select>

    <!-- 检查是否存在未解决的同类型预警 -->
    <select id="countUnresolvedByRuleIdAndSkuCodeAndWarehouse" resultType="java.lang.Integer">
        SELECT COUNT(*) FROM stock_warning_records
        WHERE rule_id = #{ruleId}
        AND sku_code = #{skuCode}
        AND warehouse_id = #{warehouseId}
        AND status IN ('pending', 'notified', 'acknowledged')
    </select>

    <!-- 统计指定时间范围内的预警数量 -->
    <select id="countByRuleTypeAndCreatedAtBetween" resultType="java.lang.Integer">
        SELECT COUNT(*) FROM stock_warning_records
        WHERE 1=1
        <if test="ruleType != null and ruleType != ''">
            AND rule_type = #{ruleType}
        </if>
        <if test="startDate != null">
            AND created_at &gt;= #{startDate}
        </if>
        <if test="endDate != null">
            AND created_at &lt;= #{endDate}
        </if>
    </select>

    <!-- 统计指定时间范围内的预警数量（按状态） -->
    <select id="countByStatusAndCreatedAtBetween" resultType="java.lang.Integer">
        SELECT COUNT(*) FROM stock_warning_records
        WHERE status = #{status}
        <if test="startDate != null">
            AND created_at &gt;= #{startDate}
        </if>
        <if test="endDate != null">
            AND created_at &lt;= #{endDate}
        </if>
    </select>

</mapper>
