package jnpf.limsService.provider;

import com.fasterxml.jackson.databind.PropertyNamingStrategies;
import com.fasterxml.jackson.databind.annotation.JsonNaming;
import lombok.Value;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;
import java.util.List;
import java.util.function.Supplier;

/** Lists only preview samples that the current field manager may already read as test-work-item snapshots. */
public class LimsElnPreviewSampleCatalog {
    private final JdbcTemplate jdbc;
    private final Supplier<LimsElnSourceCatalog.Actor> actors;

    public LimsElnPreviewSampleCatalog(DataSource readOnlyDataSource,
            Supplier<LimsElnSourceCatalog.Actor> actors) {
        this.jdbc = new JdbcTemplate(readOnlyDataSource);
        this.jdbc.setQueryTimeout(5);
        this.actors = actors;
    }

    public List<Sample> search(String keyword) {
        LimsElnSourceCatalog.Actor actor = actors.get();
        if (actor == null || !actor.isFieldManager() || !"0".equals(actor.getTenantId())) {
            throw new LimsElnReadonlyResolver.SourceFailure("SOURCE_FORBIDDEN", "Field management permission required");
        }
        String query = keyword == null ? "" : keyword.trim();
        String pattern = "%" + query.replace("\\", "\\\\").replace("%", "\\%").replace("_", "\\_") + "%";
        return jdbc.query("SELECT task.f_id, task.jianyan_liushuihao, task.jianyan_xiangmu_mingcheng, "
                        + "task.chanpin_mingcheng, task.pihao FROM lims_jianyan_xiang task "
                        + "LEFT JOIN (SELECT f_tenant_id, bianma, COUNT(*) AS match_count FROM jianyan_xiangmu_guanli "
                        + "WHERE f_tenant_id = '0' GROUP BY f_tenant_id, bianma) project ON project.f_tenant_id = task.f_tenant_id "
                        + "AND project.bianma = task.jianyan_xiangmu_bianma "
                        + "WHERE task.f_tenant_id = '0' AND (task.f_delete_mark IS NULL OR task.f_delete_mark = 0) "
                        + "AND task.jianyan_zhuangtai IN ('pending_test','audit_rejected','cancelled') "
                        + "AND NULLIF(TRIM(task.qingyandan_id), '') IS NOT NULL AND project.match_count = 1 "
                        + "AND CASE WHEN LOWER(COALESCE(task.shifou_waijian, '')) = 'yes' "
                        + "THEN task.waijian_lururen = ? ELSE task.jiancha_ren = ? END "
                        + "AND (? = '' OR COALESCE(task.jianyan_liushuihao, '') ILIKE ? ESCAPE '\\' "
                        + "OR COALESCE(task.jianyan_xiangmu_mingcheng, '') ILIKE ? ESCAPE '\\' "
                        + "OR COALESCE(task.chanpin_mingcheng, '') ILIKE ? ESCAPE '\\' "
                        + "OR COALESCE(task.pihao, '') ILIKE ? ESCAPE '\\') "
                        + "ORDER BY task.jianyan_liushuihao DESC NULLS LAST, task.f_id LIMIT 50",
                (row, index) -> new Sample(row.getString(1), row.getString(2), row.getString(3),
                        row.getString(4), row.getString(5)),
                actor.getUserId(), actor.getUserId(), query, pattern, pattern, pattern, pattern);
    }

    @Value
    @JsonNaming(PropertyNamingStrategies.LowerCamelCaseStrategy.class)
    public static class Sample {
        String id;
        String testNo;
        String testName;
        String sampleName;
        String batchNo;
    }
}