package jnpf.limsMapper;

import jnpf.base.mapper.SuperMapper;
import jnpf.limsEntity.LimsJianyanXiangEntity;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

/**
 * 检验任务"要求完成日期"提醒 daily 扫描专用 mapper。
 *
 * <p>4 个 @Select 各自负责一个分支（D7/D3/D1/EXPIRED），命中条件相互互斥，
 * 不在同一次跑批里重复命中。各分支共同前置过滤：
 * <ul>
 *   <li>未软删；wancheng_riqi 非空；jiancha_ren 非空</li>
 *   <li>jianyan_zhuangtai ∈ {pending_test, audit_rejected} —— 还未完成结果录入</li>
 *   <li>对应标记尚未追加到 remind_flags</li>
 * </ul>
 *
 * <p>未绑 mybatis-plus 的 SuperMapper 通用增删改 —— 用 @Select 自定义 SQL
 * 绕开多租户拦截器对列裁剪的副作用（见 LimsQingyandanReportServiceImpl 注释）。
 */
@Mapper
public interface LimsJianceTaskRemindMapper extends SuperMapper<LimsJianyanXiangEntity> {

    /**
     * D-N：wancheng_riqi 距今天还有 N 天（date 差值）且 remind_flags 不含对应标记。
     *
     * <p>列别名：自定义 @Select 不走 MyBatis-Plus 的 @TableField 映射，
     * 必须用 AS 把 db 列名改成 Java 字段名（特别是 f_id → id）。
     * 不发提醒的列（biz_type、规格、批量等）省略不查。
     */
    @Select("SELECT f_id AS id," +
            "       f_tenant_id AS tenantId," +
            "       jiancha_ren AS jianchaRen," +
            "       renwu_fenpeiren AS renwuFenpeiren," +
            "       chanpin_mingcheng AS chanpinMingcheng," +
            "       pihao AS pihao," +
            "       jianyan_xiangmu_mingcheng AS jianyanXiangmuMingcheng," +
            "       wancheng_riqi AS wanchengRiqi," +
            "       remind_flags AS remindFlags" +
            "  FROM lims_jianyan_xiang " +
            " WHERE (f_delete_mark IS NULL OR f_delete_mark <> 1) " +
            "   AND wancheng_riqi IS NOT NULL " +
            "   AND jiancha_ren IS NOT NULL AND jiancha_ren <> '' " +
            "   AND jianyan_zhuangtai IN ('pending_test', 'audit_rejected') " +
            "   AND wancheng_riqi::date - CURRENT_DATE = #{days} " +
            "   AND (remind_flags IS NULL OR NOT (#{flag} = ANY(string_to_array(remind_flags, ',')))) " +
            "   AND NOT EXISTS (SELECT 1 FROM lims_message_event e " +
            "       WHERE e.tenant_id = COALESCE(lims_jianyan_xiang.f_tenant_id, '0') " +
            "         AND e.occurrence_key = CONCAT('LIMS.INSPECTION.TASK.DUE:', lims_jianyan_xiang.f_id, ':', " +
            "             TO_CHAR(lims_jianyan_xiang.wancheng_riqi, 'YYYYMMDD'), ':', #{flag}))")
    List<LimsJianyanXiangEntity> selectDueIn(@Param("days") int days,
                                             @Param("flag") String flag);

    /** EXPIRED：wancheng_riqi < today 且尚未发过过期告警。 */
    @Select("SELECT f_id AS id," +
            "       f_tenant_id AS tenantId," +
            "       jiancha_ren AS jianchaRen," +
            "       renwu_fenpeiren AS renwuFenpeiren," +
            "       chanpin_mingcheng AS chanpinMingcheng," +
            "       pihao AS pihao," +
            "       jianyan_xiangmu_mingcheng AS jianyanXiangmuMingcheng," +
            "       wancheng_riqi AS wanchengRiqi," +
            "       remind_flags AS remindFlags" +
            "  FROM lims_jianyan_xiang " +
            " WHERE (f_delete_mark IS NULL OR f_delete_mark <> 1) " +
            "   AND wancheng_riqi IS NOT NULL " +
            "   AND jiancha_ren IS NOT NULL AND jiancha_ren <> '' " +
            "   AND jianyan_zhuangtai IN ('pending_test', 'audit_rejected') " +
            "   AND wancheng_riqi < CURRENT_DATE " +
            "   AND (remind_flags IS NULL OR NOT ('EXPIRED' = ANY(string_to_array(remind_flags, ',')))) " +
            "   AND NOT EXISTS (SELECT 1 FROM lims_message_event e " +
            "       WHERE e.tenant_id = COALESCE(lims_jianyan_xiang.f_tenant_id, '0') " +
            "         AND e.occurrence_key = CONCAT('LIMS.INSPECTION.TASK.OVERDUE:', lims_jianyan_xiang.f_id, ':', " +
            "             TO_CHAR(lims_jianyan_xiang.wancheng_riqi, 'YYYYMMDD'), ':OVERDUE'))")
    List<LimsJianyanXiangEntity> selectExpired();

    /**
     * 追加标记到 remind_flags（CSV 拼接，CONCAT_WS 自动处理 null）。
     * 仅对仍处于"还未完成"状态的行追加，避免标记已完成项。
     */
    @Update("UPDATE lims_jianyan_xiang " +
            "   SET remind_flags = CASE " +
            "         WHEN remind_flags IS NULL OR remind_flags = '' THEN #{flag} " +
            "         WHEN #{flag} = ANY(string_to_array(remind_flags, ',')) THEN remind_flags " +
            "         ELSE CONCAT(remind_flags, ',', #{flag}) " +
            "       END " +
            " WHERE f_id = #{id} " +
            "   AND (f_delete_mark IS NULL OR f_delete_mark <> 1)")
    int appendFlag(@Param("id") String id, @Param("flag") String flag);
}