package jnpf.limsMapper;

import jnpf.base.mapper.SuperMapper;
import jnpf.limsEntity.LimsHaocaiAlertRow;
import jnpf.limsEntity.LimsHaocaiRukuEntity;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * 耗材缺货预警 daily 扫描专用 mapper。
 *
 * <p>用 @Select 自定义 SQL：跨 lims 业务表与 base 平台表聚合查询，
 * 列名按 L007 用 AS 别名映射到 Java 字段名。
 */
@Mapper
public interface LimsHaocaiAlertMapper extends SuperMapper<LimsHaocaiRukuEntity> {

    /**
     * 查所有"结余总量 < 缺货预警值"的耗材档案。
     *
     * <p>按 haocai_id = 档案 f_id 关联入库表，剔除已删除与 jieyu_shuliang='0' 后求和；
     * LEFT JOIN 保证完全无库存（sum=0）的耗材也能命中。
     * quehuo_yujing / jieyu_shuliang 是 varchar：PG 下 CAST 遇非数值直接抛错（MySQL 宽松返 0），
     * 一律先用正则守护，非数值按 NULL 处理（该行不参与求和/不触发预警，不炸整个跑批）。
     */
    @Select("SELECT d.f_id AS id," +
            "       d.f_tenant_id AS tenantId," +
            "       d.mingcheng AS mingcheng," +
            "       d.quehuo_yujing AS quehuoYujing," +
            "       COALESCE(SUM(CASE WHEN r.jieyu_shuliang ~ '^-?[0-9]+(\\.[0-9]+)?$'" +
            "                         THEN CAST(r.jieyu_shuliang AS NUMERIC(20,4)) END), 0) AS jieyuSum" +
            "  FROM lims_haocai_dangan d" +
            "  LEFT JOIN lims_haocai_ruku r" +
            "    ON r.haocai_id = d.f_id" +
            "   AND (r.f_delete_mark IS NULL OR r.f_delete_mark <> 1)" +
            "   AND r.jieyu_shuliang IS NOT NULL AND r.jieyu_shuliang <> '' AND r.jieyu_shuliang <> '0'" +
            " WHERE (d.f_delete_mark IS NULL OR d.f_delete_mark <> 1)" +
            "   AND d.quehuo_yujing ~ '^-?[0-9]+(\\.[0-9]+)?$'" +
            "   AND NOT EXISTS (SELECT 1 FROM lims_message_event e" +
            "       WHERE e.tenant_id = COALESCE(d.f_tenant_id, '0')" +
            "         AND e.occurrence_key = CONCAT('LIMS.CONSUMABLE.STOCK.LOW:', d.f_id, ':'," +
            "             TO_CHAR(CURRENT_DATE, 'YYYYMMDD')))" +
            " GROUP BY d.f_id, d.f_tenant_id, d.mingcheng, d.quehuo_yujing" +
            " HAVING COALESCE(SUM(CASE WHEN r.jieyu_shuliang ~ '^-?[0-9]+(\\.[0-9]+)?$'" +
            "                          THEN CAST(r.jieyu_shuliang AS NUMERIC(20,4)) END), 0)" +
            "        < CAST(d.quehuo_yujing AS NUMERIC(20,4))")
    List<LimsHaocaiAlertRow> selectBelowThreshold();

    /**
     * 按角色编码查该角色下在职（启用、未删）用户 id 列表。
     * 用 enCode 而非角色 f_id，避免硬编码环境相关主键。
     * 角色成员存 base_role_relation（f_object_type='user' 小写）。
     */
    @Select("SELECT r.f_object_id" +
            "  FROM base_role_relation r" +
            "  JOIN base_role b ON r.f_role_id = b.f_id" +
            "  JOIN base_user u ON u.f_id = r.f_object_id" +
            " WHERE b.f_en_code = #{enCode}" +
            "   AND r.f_object_type = 'user'" +
            "   AND (b.f_delete_mark IS NULL OR b.f_delete_mark <> 1)" +
            "   AND (u.f_delete_mark IS NULL OR u.f_delete_mark <> 1)" +
            "   AND u.f_enabled_mark = 1")
    List<String> selectUserIdsByRoleEnCode(@Param("enCode") String enCode);
}