package jnpf.limsMapper;

import jnpf.limsEntity.*;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * Read-only M1 reminder queries. Event-table exclusion keeps the daily scan bounded.
 */
@Mapper
public interface LimsBusinessReminderMapper {

    @Select("SELECT x.f_id AS id," +
            "       x.f_tenant_id AS tenantId," +
            "       j.mingcheng AS name," +
            "       j.bianhao AS code," +
            "       j.jiliang_ren AS recipientUserId," +
            "       x.jiliang_tixing_riqi AS reminderDate," +
            "       x.xiaci_jiliang_shijian AS dueDate" +
            "  FROM lims_shebei_yiqi_xinxi x" +
            "  JOIN LATERAL (" +
            "       SELECT r.mingcheng, r.bianhao, r.jiliang_ren" +
            "         FROM lims_shebei_yiqi_jiliang r" +
            "        WHERE r.yiqi_id = x.f_id" +
            "          AND COALESCE(r.f_tenant_id, '0') = COALESCE(x.f_tenant_id, '0')" +
            "          AND (r.f_delete_mark IS NULL OR r.f_delete_mark <> 1)" +
            "          AND r.xiaci_jiliang_shijian = x.xiaci_jiliang_shijian" +
            "          AND r.jiliang_ren IS NOT NULL AND r.jiliang_ren <> ''" +
            "        ORDER BY r.shangci_jiliang_shijian DESC NULLS LAST, r.f_id DESC" +
            "        LIMIT 1" +
            "  ) j ON TRUE" +
            " WHERE (x.f_delete_mark IS NULL OR x.f_delete_mark <> 1)" +
            "   AND (x.biz_status IS NULL OR x.biz_status <> 'yibaofei')" +
            "   AND x.jiliang_tixing_riqi IS NOT NULL" +
            "   AND x.xiaci_jiliang_shijian IS NOT NULL" +
            "   AND x.jiliang_tixing_riqi::date <= CURRENT_DATE" +
            "   AND NOT EXISTS (SELECT 1 FROM lims_message_event e" +
            "       WHERE e.tenant_id = COALESCE(x.f_tenant_id, '0')" +
            "         AND e.occurrence_key = CONCAT('LIMS.EQUIPMENT.CALIBRATION.DUE:', x.f_id, ':'," +
            "             TO_CHAR(x.xiaci_jiliang_shijian, 'YYYYMMDD')))")
    List<LimsEquipmentCalibrationReminderRow> selectEquipmentCalibrationDue();

    @Select("SELECT x.f_id AS id," +
            "       x.f_tenant_id AS tenantId," +
            "       j.mingcheng AS name," +
            "       j.bianhao AS code," +
            "       j.yanzheng_ren AS recipientUserId," +
            "       x.yanzheng_tixing_riqi AS reminderDate," +
            "       x.xiaci_yanzheng_shijian AS dueDate" +
            "  FROM lims_shebei_yiqi_xinxi x" +
            "  JOIN LATERAL (" +
            "       SELECT r.mingcheng, r.bianhao, r.yanzheng_ren" +
            "         FROM lims_shebei_yiqi_yanzheng r" +
            "        WHERE r.yiqi_id = x.f_id" +
            "          AND COALESCE(r.f_tenant_id, '0') = COALESCE(x.f_tenant_id, '0')" +
            "          AND (r.f_delete_mark IS NULL OR r.f_delete_mark <> 1)" +
            "          AND r.xiaci_yanzheng_shijian = x.xiaci_yanzheng_shijian" +
            "          AND r.yanzheng_ren IS NOT NULL AND r.yanzheng_ren <> ''" +
            "        ORDER BY r.shangci_yanzheng_shijian DESC NULLS LAST, r.f_id DESC LIMIT 1" +
            "  ) j ON TRUE" +
            " WHERE (x.f_delete_mark IS NULL OR x.f_delete_mark <> 1)" +
            "   AND (x.biz_status IS NULL OR x.biz_status <> 'yibaofei')" +
            "   AND x.yanzheng_tixing_riqi IS NOT NULL" +
            "   AND x.xiaci_yanzheng_shijian IS NOT NULL" +
            "   AND x.yanzheng_tixing_riqi::date <= CURRENT_DATE" +
            "   AND NOT EXISTS (SELECT 1 FROM lims_message_event e" +
            "       WHERE e.tenant_id = COALESCE(x.f_tenant_id, '0')" +
            "         AND e.occurrence_key = CONCAT('LIMS.EQUIPMENT.VERIFICATION.DUE:', x.f_id, ':'," +
            "             TO_CHAR(x.xiaci_yanzheng_shijian, 'YYYYMMDD')))")
    List<LimsEquipmentCalibrationReminderRow> selectEquipmentVerificationDue();

    @Select("SELECT z.f_id AS id," +
            "       z.f_tenant_id AS tenantId," +
            "       z.f_foreign_id AS planId," +
            "       z.yangpin_mingcheng AS sampleName," +
            "       z.yangpin_bianma AS sampleCode," +
            "       z.pihao AS batchNo," +
            "       z.guancha_ren AS recipientUserId," +
            "       z.guancha_riqi AS observationDate" +
            "  FROM lims_liuyang_jihua_zixiang z" +
            " WHERE (z.f_delete_mark IS NULL OR z.f_delete_mark <> 1)" +
            "   AND (z.biz_status IS NULL OR z.biz_status <> 'disabled')" +
            "   AND (z.shifou_yiguancha IS NULL OR z.shifou_yiguancha <> 'yes')" +
            "   AND z.guancha_riqi IS NOT NULL" +
            "   AND z.guancha_riqi::date <= CURRENT_DATE" +
            "   AND z.guancha_ren IS NOT NULL AND z.guancha_ren <> ''" +
            "   AND NOT EXISTS (SELECT 1 FROM lims_message_event e" +
            "       WHERE e.tenant_id = COALESCE(z.f_tenant_id, '0')" +
            "         AND e.occurrence_key = CONCAT('LIMS.RETAIN.SAMPLE.OBSERVATION.DUE:', z.f_id, ':'," +
            "             TO_CHAR(z.guancha_riqi, 'YYYYMMDD')))")
    List<LimsRetainObservationReminderRow> selectRetainObservationDue();

    @Select("SELECT r.jihua_id AS planId, r.f_tenant_id AS tenantId," +
            "       MAX(r.jihua_mingcheng) AS planName, MAX(r.jihua_leixing) AS planType," +
            "       r.jihua_riqi AS plannedDate," +
            "       STRING_AGG(DISTINCT NULLIF(r.jiance_quyu, ''), '、') AS monitoringArea," +
            "       STRING_AGG(DISTINCT NULLIF(r.jiejing_jibie, ''), '、') AS cleanlinessLevel," +
            "       STRING_AGG(DISTINCT NULLIF(r.jiance_xiangmu, ''), '、') AS monitoringItems" +
            "  FROM lims_huanjing_jiance_jilu r" +
            " WHERE (r.f_delete_mark IS NULL OR r.f_delete_mark <> 1)" +
            "   AND r.biz_status = 'enabled'" +
            "   AND r.jihua_riqi IS NOT NULL AND r.jihua_riqi::date <= CURRENT_DATE" +
            "   AND r.quyang_riqi IS NULL AND (r.quyang_ren IS NULL OR r.quyang_ren = '')" +
            "   AND NOT EXISTS (SELECT 1 FROM lims_message_event e" +
            "       WHERE e.tenant_id = COALESCE(r.f_tenant_id, '0')" +
            "         AND e.occurrence_key = CONCAT('LIMS.ENVIRONMENT.SAMPLING.DUE:', r.jihua_id, ':'," +
            "             TO_CHAR(r.jihua_riqi, 'YYYYMMDD')))" +
            " GROUP BY r.jihua_id, r.f_tenant_id, r.jihua_riqi")
    List<LimsEnvironmentSamplingReminderRow> selectEnvironmentSamplingDue();

    @Select("SELECT z.f_id AS id, z.f_tenant_id AS tenantId, z.bianhao AS batchCode," +
            "       z.tiaoma_bianma AS barcode, z.mingcheng AS name, z.guige AS specification," +
            "       z.shengyuliang AS remainingQuantity, z.danwei AS unit," +
            "       z.cunfang_weizhi AS storageLocation, z.youxiaoqi AS expiryDate," +
            "       (z.youxiaoqi::date - CURRENT_DATE) AS remainingDays," +
            "       CASE (z.youxiaoqi::date - CURRENT_DATE)" +
            "         WHEN 30 THEN 'D30' WHEN 7 THEN 'D7' WHEN 1 THEN 'D1' ELSE 'D0' END AS reminderStage" +
            "  FROM lims_haocai_ruku_zixiang z" +
            " WHERE (z.f_delete_mark IS NULL OR z.f_delete_mark <> 1)" +
            "   AND z.biz_status IN ('weichuku', 'yikaiping')" +
            "   AND z.shengyuliang ~ '^\\s*[0-9]+(\\.[0-9]+)?\\s*$'" +
            "   AND z.shengyuliang::numeric > 0 AND z.youxiaoqi IS NOT NULL" +
            "   AND (z.youxiaoqi::date - CURRENT_DATE) IN (30, 7, 1, 0)" +
            "   AND NOT EXISTS (SELECT 1 FROM lims_message_event e" +
            "       WHERE e.tenant_id = COALESCE(z.f_tenant_id, '0')" +
            "         AND e.occurrence_key = CONCAT('LIMS.CONSUMABLE.BATCH.EXPIRY.DUE:', z.f_id, ':'," +
            "             TO_CHAR(z.youxiaoqi, 'YYYYMMDD'), ':'," +
            "             CASE (z.youxiaoqi::date - CURRENT_DATE)" +
            "               WHEN 30 THEN 'D30' WHEN 7 THEN 'D7' WHEN 1 THEN 'D1' ELSE 'D0' END))")
    List<LimsConsumableExpiryReminderRow> selectConsumableExpiryDue();

    @Select("SELECT l.f_id AS id, l.f_tenant_id AS tenantId, l.yangpin_mingcheng AS sampleName," +
            "       l.yangpin_bianma AS sampleCode, l.pihao AS batchNo," +
            "       l.liuyang_xiancunliang AS currentQuantity," +
            "       COALESCE(NULLIF(l.liuyang_xiancunliang_danwei, ''), l.liuyang_danwei) AS unit," +
            "       l.liuyang_weizhi AS storageLocation, l.baocun_qixian AS retentionDeadline" +
            "  FROM lims_liuyang_jilu l" +
            " WHERE (l.f_delete_mark IS NULL OR l.f_delete_mark <> 1)" +
            "   AND l.baocun_qixian IS NOT NULL AND l.baocun_qixian::date <= CURRENT_DATE" +
            "   AND (l.xiaohui_danhao IS NULL OR l.xiaohui_danhao = '')" +
            "   AND COALESCE(l.biz_status, '') NOT IN ('daixiaohui', 'yixiaohui', 'destroyed')" +
            "   AND NOT EXISTS (SELECT 1 FROM lims_message_event e" +
            "       WHERE e.tenant_id = COALESCE(l.f_tenant_id, '0')" +
            "         AND e.occurrence_key = CONCAT('LIMS.RETAIN.SAMPLE.DESTRUCTION.DUE:', l.f_id, ':'," +
            "             TO_CHAR(l.baocun_qixian, 'YYYYMMDD')))")
    List<LimsRetainDestructionReminderRow> selectRetainDestructionDue();

    @Select("SELECT c.f_id AS id, c.kaoca_jihua_id AS planId, c.f_tenant_id AS tenantId," +
            "       p.bianhao AS planCode, p.kaoca_mingcheng AS planName," +
            "       p.yangpin_mingcheng AS sampleName, c.pihao AS batchNo," +
            "       c.zhouqi AS period, c.zhouqi_danwei AS periodUnit," +
            "       c.jiahua_anpai_riqi AS plannedDate," +
            "       STRING_AGG(DISTINCT NULLIF(z.jianyan_xiangmu_mingcheng, ''), '、') AS inspectionItems," +
            "       STRING_AGG(DISTINCT NULLIF(z.jiancha_banzu, ''), ',') AS organizationIds" +
            "  FROM lims_wendingxing_kaoca_jihua_zhouqi c" +
            "  JOIN lims_wendingxing_kaoca_jihua p ON p.f_id = c.kaoca_jihua_id" +
            "  CROSS JOIN LATERAL regexp_split_to_table(" +
            "       TRIM(BOTH '[] ' FROM COALESCE(c.jiancha_xiangmu_ids, '')), '\\s*,\\s*') item(item_id)" +
            "  JOIN zhiliang_biaozhun_guanli_zixiang z" +
            "    ON z.f_id = TRIM(BOTH '\"' FROM item.item_id)" +
            "   AND COALESCE(z.f_tenant_id, '0') = COALESCE(c.f_tenant_id, '0')" +
            " WHERE (c.f_delete_mark IS NULL OR c.f_delete_mark <> 1)" +
            "   AND (p.f_delete_mark IS NULL OR p.f_delete_mark <> 1)" +
            "   AND (z.f_delete_mark IS NULL OR z.f_delete_mark <> 1)" +
            "   AND (c.shifou_yizhixing IS NULL OR c.shifou_yizhixing <> 'yes')" +
            "   AND (c.shifou_jixu_zhixing IS NULL OR c.shifou_jixu_zhixing <> 'no')" +
            "   AND c.jiahua_anpai_riqi IS NOT NULL AND c.jiahua_anpai_riqi::date <= CURRENT_DATE" +
            "   AND NOT EXISTS (SELECT 1 FROM lims_message_event e" +
            "       WHERE e.tenant_id = COALESCE(c.f_tenant_id, '0')" +
            "         AND e.occurrence_key = CONCAT('LIMS.STABILITY.PERIOD.DUE:', c.f_id, ':'," +
            "             TO_CHAR(c.jiahua_anpai_riqi, 'YYYYMMDD')))" +
            " GROUP BY c.f_id, c.kaoca_jihua_id, c.f_tenant_id, p.bianhao, p.kaoca_mingcheng," +
            "          p.yangpin_mingcheng, c.pihao, c.zhouqi, c.zhouqi_danwei, c.jiahua_anpai_riqi" +
            " HAVING COUNT(NULLIF(z.jiancha_banzu, '')) > 0")
    List<LimsStabilityPeriodReminderRow> selectStabilityPeriodDue();
}