src/main/java/edu/mai/pastsolution/StopListChecker.java - master - monzikov/past-solution

src/main/java/edu/mai/pastsolution/StopListChecker.java
401 строка26 KB
Yuri Monzikov
add past-solution code
04 июл 2026, 14:38
04 июл 2026, 14:381516693
package edu.mai.pastsolution;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import java.util.*;
import java.util.stream.Collectors;

@SuppressWarnings("SqlDerivedTableAlias")
public class StopListChecker {

    private static final int RESULT_LIMIT = 10*3; // максимум по 10 в каждую из категорий

    private static final double MIN_SIMILARITY_COMMON = 0.45;
    private static final double MIN_SIMILARITY_LASTNAME = 0.81;
    private static final double MIN_SIMILARITY_FIRSTNAME = 0.57;
    private static final double MIN_SIMILARITY_PATRONYMIC = 0.6;

    private final LcsSimilarity lcsSimilarity = new LcsSimilarity();

    private final JdbcTemplate  jdbcTemplate;
    private final NamedParameterJdbcTemplate namedParameterJdbcTemplate;
    private final float thresholdLow;
    private final float thresholdHigh;

    public StopListChecker(JdbcTemplate jdbcTemplate, float thresholdLow, float thresholdHigh) {
        this.jdbcTemplate = jdbcTemplate;
        this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
        this.thresholdLow = thresholdLow;
        this.thresholdHigh = thresholdHigh;
    }

    public int checkDatabase() {
        try {
            var r = jdbcTemplate.queryForObject("SELECT 1", Integer.class);
            if (r != null) {
                return r;
            }
        }
        catch(Exception e) {
            return 0;
        }
        return 0;
    }

    public List<Figurant> searchFigurants(String lastName, String firstName, String patronymic) {
        String sql = "SELECT id, stoplist_id, last_name, first_name, patronymic, " +
                "last_name_en, first_name_en, patronymic_en " +
                "FROM figurants " +
                "WHERE (last_name = :lastName OR last_name_en = :lastName) " +
                "AND (first_name = :firstName OR first_name_en = :firstName) " +
                "AND (patronymic = :patronymic OR patronymic_en = :patronymic)";

        var params = new MapSqlParameterSource();
            params.addValue("lastName", lastName);
            params.addValue("firstName", firstName);
            params.addValue("patronymic", patronymic);

        return namedParameterJdbcTemplate.query(sql, params, (rs, rowNum) ->
                new Figurant(
                        String.valueOf(rs.getInt("id")),
                        rs.getString("last_name"),
                        rs.getString("first_name"),
                        rs.getString("patronymic"),
                        rs.getString("stoplist_id")
                )
        );
    }

    public List<LastnameDto> searchLastname(String lastName){

        var ln_req_en = IcanTransliterator.transliterate(lastName);
        var ln_seqs = lcsSimilarity.getLcsSubstrings(lastName);
        var ln_seqs_en = lcsSimilarity.getLcsSubstrings(IcanTransliterator.transliterate(lastName));

        String sql = "(SELECT last_name_out, max(lcs_out) last_name_lcs_out " +
                "FROM (SELECT DISTINCT last_name_out, lcs_in, last_name_in_len, last_name_out_len, sequence_len, (2.0*sequence_len/(last_name_in_len+last_name_out_len)) lcs_out " +
                "FROM (SELECT seq2.last_name last_name_out, length(seq2.last_name) last_name_out_len, seq1.lcs_in, seq1.last_name_in_len, seq1.sequence_len " +
                "FROM public.last_name_seq seq2 " +
                "INNER JOIN (SELECT last_name, sequence , sequence_len, 2.0 - (0.8915 + LN(length(last_name)+1)) / LN(length(last_name)+1) lcs_in , length(:lastNameIn) last_name_in_len " +
                "FROM public.last_name_seq " +
                "WHERE sequence IN (:lastNames)) seq1 on seq1.sequence = seq2.sequence) " +
                "WHERE (2.0*sequence_len/(last_name_in_len+last_name_out_len))>=lcs_in " +
                "order by lcs_out desc ) " +
                "WHERE lcs_out >= :last_name_lcsMin " +
                "group by last_name_out order by last_name_lcs_out desc) "+
                "UNION "+
                "(SELECT last_name_out, max(lcs_out) last_name_lcs_out " +
                "FROM (SELECT DISTINCT last_name_out, lcs_in, last_name_in_len, last_name_out_len, sequence_len, (2.0*sequence_len/(last_name_in_len+last_name_out_len)) lcs_out " +
                "FROM (SELECT seq2.last_name_en last_name_out, length(seq2.last_name_en) last_name_out_len, seq1.lcs_in, seq1.last_name_in_len, seq1.sequence_len " +
                "FROM public.last_name_en_seq seq2 " +
                "INNER JOIN (SELECT last_name_en, sequence , sequence_len, 2.0 - (0.8915 + LN(length(last_name_en)+1)) / LN(length(last_name_en)+1) lcs_in , length(:lastNameEnIn) last_name_in_len " +
                "FROM public.last_name_en_seq " +
                "WHERE sequence IN (:lastNamesEn)) seq1 on seq1.sequence = seq2.sequence) " +
                "WHERE (2.0*sequence_len/(last_name_in_len+last_name_out_len))>=lcs_in " +
                "order by lcs_out desc ) " +
                "WHERE lcs_out >= :last_name_lcsMin " +
                "group by last_name_out order by last_name_lcs_out desc) ";

        var params = new MapSqlParameterSource();
        params.addValue("lastNameIn", lastName);
        params.addValue("lastNameEnIn", ln_req_en);
        params.addValue("lastNames", ln_seqs);
        params.addValue("lastNamesEn", ln_seqs_en);
        params.addValue("last_name_lcsMin", MIN_SIMILARITY_LASTNAME);

        return namedParameterJdbcTemplate.query(sql, params, (rs, rowNum) ->
                new LastnameDto(
                        rs.getString("last_name_out"),
                        rs.getDouble("last_name_lcs_out")
                )
        );
    }

    public List<FirstnameDto> searchFirstname(String firstName){

        var fn_seqs = lcsSimilarity.getLcsSubstrings(firstName);
        var fn_req_en = IcanTransliterator.transliterate(firstName);
        var fn_seqs_en = lcsSimilarity.getLcsSubstrings(IcanTransliterator.transliterate(firstName));

        String sql = "(SELECT first_name_out, max(lcs_out) first_name_lcs_out " +
                "FROM (SELECT DISTINCT first_name_out, lcs_in, first_name_in_len, first_name_out_len, sequence_len, (2.0*sequence_len/(first_name_in_len+first_name_out_len)) lcs_out " +
                "FROM (SELECT seq2.first_name first_name_out, length(seq2.first_name) first_name_out_len, seq1.lcs_in, seq1.first_name_in_len, seq1.sequence_len " +
                "FROM public.first_name_seq seq2 " +
                "INNER JOIN (SELECT first_name, sequence , sequence_len, 2.0 - (0.8915 + LN(length(first_name)+1)) / LN(length(first_name)+1) lcs_in , length(:firstNameIn) first_name_in_len " +
                "FROM public.first_name_seq " +
                "WHERE sequence IN (:firstNames)) seq1 on seq1.sequence = seq2.sequence) " +
                "WHERE (2.0*sequence_len/(first_name_in_len+first_name_out_len))>=lcs_in " +
                "order by lcs_out desc ) " +
                "WHERE lcs_out >= :first_name_lcsMin " +
                "group by first_name_out order by first_name_lcs_out desc) "+
                "UNION "+
                "(SELECT first_name_out, max(lcs_out) first_name_lcs_out " +
                "FROM (SELECT DISTINCT first_name_out, lcs_in, first_name_in_len, first_name_out_len, sequence_len, (2.0*sequence_len/(first_name_in_len+first_name_out_len)) lcs_out " +
                "FROM (SELECT seq2.first_name_en first_name_out, length(seq2.first_name_en) first_name_out_len, seq1.lcs_in, seq1.first_name_in_len, seq1.sequence_len " +
                "FROM public.first_name_en_seq seq2 " +
                "INNER JOIN (SELECT first_name_en, sequence , sequence_len, 2.0 - (0.8915 + LN(length(first_name_en)+1)) / LN(length(first_name_en)+1) lcs_in , length(:firstNameEnIn) first_name_in_len " +
                "FROM public.first_name_en_seq " +
                "WHERE sequence IN (:firstNamesEn)) seq1 on seq1.sequence = seq2.sequence) " +
                "WHERE (2.0*sequence_len/(first_name_in_len+first_name_out_len))>=lcs_in " +
                "order by lcs_out desc ) " +
                "WHERE lcs_out >= :first_name_lcsMin " +
                "group by first_name_out order by first_name_lcs_out desc)";

        var params = new MapSqlParameterSource();
        params.addValue("firstNameIn", firstName);
        params.addValue("firstNameEnIn", fn_req_en);
        params.addValue("firstNames", fn_seqs);
        params.addValue("firstNamesEn", fn_seqs_en);
        params.addValue("first_name_lcsMin", MIN_SIMILARITY_FIRSTNAME);

        return namedParameterJdbcTemplate.query(sql, params, (rs, rowNum) ->
                new FirstnameDto(
                        rs.getString("first_name_out"),
                        rs.getDouble("first_name_lcs_out")
                )
        );
    }

    public List<PatronymicDto> searchPatronymic(String patronymic){

        var pn_seqs = lcsSimilarity.getLcsSubstrings(patronymic);
        var pn_req_en = IcanTransliterator.transliterate(patronymic);
        var pn_seqs_en = lcsSimilarity.getLcsSubstrings(IcanTransliterator.transliterate(patronymic));

        String sql = "(SELECT patronymic_out, max(lcs_out) patronymic_lcs_out " +
                "FROM (SELECT DISTINCT patronymic_out, lcs_in, patronymic_in_len, patronymic_out_len, sequence_len, (2.0*sequence_len/(patronymic_in_len+patronymic_out_len)) lcs_out " +
                "FROM (SELECT seq2.patronymic patronymic_out, length(seq2.patronymic) patronymic_out_len, seq1.lcs_in, seq1.patronymic_in_len, seq1.sequence_len " +
                "FROM public.patronymic_seq seq2 " +
                "INNER JOIN (SELECT patronymic, sequence , sequence_len, 2.0 - (0.8915 + LN(length(patronymic)+1)) / LN(length(patronymic)+1) lcs_in , length(:patronymicIn) patronymic_in_len " +
                "FROM public.patronymic_seq " +
                "WHERE sequence IN(:patronymics)) seq1 on seq1.sequence = seq2.sequence) " +
                "WHERE (2.0*sequence_len/(patronymic_in_len+patronymic_out_len))>=lcs_in " +
                "order by lcs_out desc ) " +
                "WHERE lcs_out >= :patronymic_lcsMin " +
                "group by patronymic_out order by patronymic_lcs_out desc )"+
                "UNION "+
                "(SELECT patronymic_out, max(lcs_out) patronymic_lcs_out " +
                "FROM (SELECT DISTINCT patronymic_out, lcs_in, patronymic_in_len, patronymic_out_len, sequence_len, (2.0*sequence_len/(patronymic_in_len+patronymic_out_len)) lcs_out " +
                "FROM (SELECT seq2.patronymic_en patronymic_out, length(seq2.patronymic_en) patronymic_out_len, seq1.lcs_in, seq1.patronymic_in_len, seq1.sequence_len " +
                "FROM public.patronymic_en_seq seq2 " +
                "INNER JOIN (SELECT patronymic_en, sequence , sequence_len, 2.0 - (0.8915 + LN(length(patronymic_en)+1)) / LN(length(patronymic_en)+1) lcs_in , length(:patronymicEnIn) patronymic_in_len " +
                "FROM public.patronymic_en_seq " +
                "WHERE sequence IN(:patronymicsEn)) seq1 on seq1.sequence = seq2.sequence) " +
                "WHERE (2.0*sequence_len/(patronymic_in_len+patronymic_out_len))>=lcs_in " +
                "order by lcs_out desc ) " +
                "WHERE lcs_out >= :patronymic_lcsMin " +
                "group by patronymic_out order by patronymic_lcs_out desc)";

        var params = new MapSqlParameterSource();
        params.addValue("patronymicIn", patronymic);
        params.addValue("patronymicEnIn", pn_req_en);
        params.addValue("patronymics", pn_seqs);
        params.addValue("patronymicsEn", pn_seqs_en);
        params.addValue("patronymic_lcsMin", MIN_SIMILARITY_PATRONYMIC);

        return namedParameterJdbcTemplate.query(sql, params, (rs, rowNum) ->
                new PatronymicDto(
                        rs.getString("patronymic_out"),
                        rs.getDouble("patronymic_lcs_out")
                )
        );
    }

    public SearchResult checkClient(SearchRequest client) {
        String fullName = String.format("%s %s %s",
                client.getLastName(), client.getFirstName(), client.getPatronymic()).trim();
        try {

            var figurants = searchWithLcs(client.getLastName(), client.getFirstName(), client.getPatronymic());

            if (figurants.isEmpty()) {
                return new SearchResult(fullName, SearchResult.Status.APPROVED, "Авто-пропуск. Совпадений не найдено.");
            }

            var figurants_red = figurants.stream().filter(f -> f.getSimilarity() >= thresholdHigh).toList();
            var figurants_yellow = figurants.stream().filter(f -> f.getSimilarity() >= thresholdLow && f.getSimilarity() < thresholdHigh).toList();
            var figurants_green = figurants.stream().filter(f -> f.getSimilarity() < thresholdLow).toList();
            for (var match : figurants) {
                if (match.getSimilarity() >= thresholdHigh) {
                    return new SearchResult(fullName, SearchResult.Status.REJECTED,
                            "Авто-блок! Найдено критическое совпадение с фигурантом (uuid:"+match.getUuid()+", "+match.getFullFio()+") из стоп-листа: " + match.getStopListId(), figurants_red, figurants_yellow, figurants_green);
                }
                if (match.getSimilarity() >= thresholdLow) {
                    return new SearchResult(fullName, SearchResult.Status.MANUAL_REVIEW,
                            "Подозрение на совпадение совпадение с фигурантом (uuid:"+match.getUuid()+", "+match.getFullFio()+") из стоп-листа: " + match.getStopListId(), figurants_red, figurants_yellow, figurants_green);
                }
            }
            return new SearchResult(fullName, SearchResult.Status.APPROVED, "Авто-пропуск. Значимых совпадений не найдено.", figurants_red, figurants_yellow, figurants_green);

        } catch (Exception e) {
            return new SearchResult(fullName, SearchResult.Status.MANUAL_REVIEW, "Ошибка проверки: " + e.getMessage());
        }
    }

    private List<Figurant> searchWithLcs(String lastName, String firstName, String patronymic) {

        var ln_req_en = IcanTransliterator.transliterate(lastName);
        var fn_req_en = IcanTransliterator.transliterate(firstName);
        var pn_req_en = IcanTransliterator.transliterate(patronymic);


        var ln_seqs = lcsSimilarity.getLcsSubstrings(lastName);
        var fn_seqs = lcsSimilarity.getLcsSubstrings(firstName);
        var pn_seqs = lcsSimilarity.getLcsSubstrings(patronymic);


        var ln_seqs_en = lcsSimilarity.getLcsSubstrings(IcanTransliterator.transliterate(lastName));
        var fn_seqs_en = lcsSimilarity.getLcsSubstrings(IcanTransliterator.transliterate(firstName));
        var pn_seqs_en = lcsSimilarity.getLcsSubstrings(IcanTransliterator.transliterate(patronymic));

        String sql =
                "SELECT " +
                        "last_name, first_name, patronymic, " +
                        "last_name_en, first_name_en, patronymic_en, "+
                        "MAX(last_name_lcs_out) last_name_lcs_out, MAX(first_name_lcs_out) first_name_lcs_out, MAX(patronymic_lcs_out) patronymic_lcs_out, " +
                        "(MAX(last_name_lcs_out) + MAX(first_name_lcs_out) + MAX(patronymic_lcs_out)) sum_lcs_out, " +
                        "stoplist_id, id "+
                        "FROM " +
                        "(SELECT " +
                        "last_name, first_name, patronymic, " +
                        "last_name_en, first_name_en, patronymic_en, "+
                        "last_name_lcs.last_name_lcs_out, first_name_lcs.first_name_lcs_out, patronymic_lcs.patronymic_lcs_out, " +
                        "stoplist_id, id "+
                        "FROM figurants " +
                        "INNER JOIN " +
                        "(SELECT patronymic_out, max(lcs_out) patronymic_lcs_out " +
                        "FROM (SELECT DISTINCT patronymic_out, lcs_in, patronymic_in_len, patronymic_out_len, sequence_len, (2.0*sequence_len/(patronymic_in_len+patronymic_out_len)) lcs_out " +
                        "FROM (SELECT seq2.patronymic patronymic_out, length(seq2.patronymic) patronymic_out_len, seq1.lcs_in, seq1.patronymic_in_len, seq1.sequence_len " +
                        "FROM public.patronymic_seq seq2 " +
                        "INNER JOIN (SELECT patronymic, sequence , sequence_len, 2.0 - (0.8915 + LN(length(patronymic)+1)) / LN(length(patronymic)+1) lcs_in , length(:patronymicIn) patronymic_in_len " +
                        "FROM public.patronymic_seq " +
                        "WHERE sequence IN(:patronymics)) seq1 on seq1.sequence = seq2.sequence) " +
                        "WHERE (2.0*sequence_len/(patronymic_in_len+patronymic_out_len))>=lcs_in " +
                        "order by lcs_out desc ) " +
                        "WHERE lcs_out >= :patronymic_lcsMin " +
                        "group by patronymic_out order by patronymic_lcs_out desc) patronymic_lcs on patronymic_lcs.patronymic_out = figurants.patronymic " +
                        "INNER JOIN "+
                        "(SELECT first_name_out, max(lcs_out) first_name_lcs_out " +
                        "FROM (SELECT DISTINCT first_name_out, lcs_in, first_name_in_len, first_name_out_len, sequence_len, (2.0*sequence_len/(first_name_in_len+first_name_out_len)) lcs_out " +
                        "FROM (SELECT seq2.first_name first_name_out, length(seq2.first_name) first_name_out_len, seq1.lcs_in, seq1.first_name_in_len, seq1.sequence_len " +
                        "FROM public.first_name_seq seq2 " +
                        "INNER JOIN (SELECT first_name, sequence , sequence_len, 2.0 - (0.8915 + LN(length(first_name)+1)) / LN(length(first_name)+1) lcs_in , length(:firstNameIn) first_name_in_len " +
                        "FROM public.first_name_seq " +
                        "WHERE sequence IN (:firstNames)) seq1 on seq1.sequence = seq2.sequence) " +
                        "WHERE (2.0*sequence_len/(first_name_in_len+first_name_out_len))>=lcs_in " +
                        "order by lcs_out desc ) " +
                        "WHERE lcs_out >= :first_name_lcsMin " +
                        "group by first_name_out order by first_name_lcs_out desc) first_name_lcs on first_name_lcs.first_name_out = figurants.first_name "+
                        "INNER JOIN "+
                        "(SELECT last_name_out, max(lcs_out) last_name_lcs_out " +
                        "FROM (SELECT DISTINCT last_name_out, lcs_in, last_name_in_len, last_name_out_len, sequence_len, (2.0*sequence_len/(last_name_in_len+last_name_out_len)) lcs_out " +
                        "FROM (SELECT seq2.last_name last_name_out, length(seq2.last_name) last_name_out_len, seq1.lcs_in, seq1.last_name_in_len, seq1.sequence_len " +
                        "FROM public.last_name_seq seq2 " +
                        "INNER JOIN (SELECT last_name, sequence , sequence_len, 2.0 - (0.8915 + LN(length(last_name)+1)) / LN(length(last_name)+1) lcs_in , length(:lastNameIn) last_name_in_len " +
                        "FROM public.last_name_seq " +
                        "WHERE sequence IN (:lastNames)) seq1 on seq1.sequence = seq2.sequence) " +
                        "WHERE (2.0*sequence_len/(last_name_in_len+last_name_out_len))>=lcs_in " +
                        "order by lcs_out desc ) " +
                        "WHERE lcs_out >= :last_name_lcsMin " +
                        "group by last_name_out order by last_name_lcs_out desc) last_name_lcs on last_name_lcs.last_name_out = figurants.last_name "+
                        "UNION "+
                        "SELECT " +
                        "last_name, first_name, patronymic, " +
                        "last_name_en, first_name_en, patronymic_en, "+
                        "last_name_lcs.last_name_lcs_out, first_name_lcs.first_name_lcs_out, patronymic_lcs.patronymic_lcs_out, " +
                        "stoplist_id, id "+
                        "FROM figurants " +
                        "INNER JOIN " +
                        "(SELECT patronymic_out, max(lcs_out) patronymic_lcs_out " +
                        "FROM (SELECT DISTINCT patronymic_out, lcs_in, patronymic_in_len, patronymic_out_len, sequence_len, (2.0*sequence_len/(patronymic_in_len+patronymic_out_len)) lcs_out " +
                        "FROM (SELECT seq2.patronymic_en patronymic_out, length(seq2.patronymic_en) patronymic_out_len, seq1.lcs_in, seq1.patronymic_in_len, seq1.sequence_len " +
                        "FROM public.patronymic_en_seq seq2 " +
                        "INNER JOIN (SELECT patronymic_en, sequence , sequence_len, 2.0 - (0.8915 + LN(length(patronymic_en)+1)) / LN(length(patronymic_en)+1) lcs_in , length(:patronymicEnIn) patronymic_in_len " +
                        "FROM public.patronymic_en_seq " +
                        "WHERE sequence IN(:patronymicsEn)) seq1 on seq1.sequence = seq2.sequence) " +
                        "WHERE (2.0*sequence_len/(patronymic_in_len+patronymic_out_len))>=lcs_in " +
                        "order by lcs_out desc ) " +
                        "WHERE lcs_out >= :patronymic_lcsMin " +
                        "group by patronymic_out order by patronymic_lcs_out desc) patronymic_lcs on patronymic_lcs.patronymic_out = figurants.patronymic_en " +
                        "INNER JOIN "+
                        "(SELECT first_name_out, max(lcs_out) first_name_lcs_out " +
                        "FROM (SELECT DISTINCT first_name_out, lcs_in, first_name_in_len, first_name_out_len, sequence_len, (2.0*sequence_len/(first_name_in_len+first_name_out_len)) lcs_out " +
                        "FROM (SELECT seq2.first_name_en first_name_out, length(seq2.first_name_en) first_name_out_len, seq1.lcs_in, seq1.first_name_in_len, seq1.sequence_len " +
                        "FROM public.first_name_en_seq seq2 " +
                        "INNER JOIN (SELECT first_name_en, sequence , sequence_len, 2.0 - (0.8915 + LN(length(first_name_en)+1)) / LN(length(first_name_en)+1) lcs_in , length(:firstNameEnIn) first_name_in_len " +
                        "FROM public.first_name_en_seq " +
                        "WHERE sequence IN (:firstNamesEn)) seq1 on seq1.sequence = seq2.sequence) " +
                        "WHERE (2.0*sequence_len/(first_name_in_len+first_name_out_len))>=lcs_in " +
                        "order by lcs_out desc ) " +
                        "WHERE lcs_out >= :first_name_lcsMin " +
                        "group by first_name_out order by first_name_lcs_out desc) first_name_lcs on first_name_lcs.first_name_out = figurants.first_name_en "+
                        "INNER JOIN "+
                        "(SELECT last_name_out, max(lcs_out) last_name_lcs_out " +
                        "FROM (SELECT DISTINCT last_name_out, lcs_in, last_name_in_len, last_name_out_len, sequence_len, (2.0*sequence_len/(last_name_in_len+last_name_out_len)) lcs_out " +
                        "FROM (SELECT seq2.last_name_en last_name_out, length(seq2.last_name_en) last_name_out_len, seq1.lcs_in, seq1.last_name_in_len, seq1.sequence_len " +
                        "FROM public.last_name_en_seq seq2 " +
                        "INNER JOIN (SELECT last_name_en, sequence , sequence_len, 2.0 - (0.8915 + LN(length(last_name_en)+1)) / LN(length(last_name_en)+1) lcs_in , length(:lastNameEnIn) last_name_in_len " +
                        "FROM public.last_name_en_seq " +
                        "WHERE sequence IN (:lastNamesEn)) seq1 on seq1.sequence = seq2.sequence) " +
                        "WHERE (2.0*sequence_len/(last_name_in_len+last_name_out_len))>=lcs_in " +
                        "order by lcs_out desc ) " +
                        "WHERE lcs_out >= :last_name_lcsMin " +
                        "group by last_name_out order by last_name_lcs_out desc) last_name_lcs on last_name_lcs.last_name_out = figurants.last_name_en) " +
                        "group by last_name, first_name, patronymic, last_name_en, first_name_en, patronymic_en, stoplist_id, id "+
                        "order by sum_lcs_out desc "+
                        "limit :resultLimit";

        var params = new MapSqlParameterSource();
        params.addValue("firstNameIn", firstName);
        params.addValue("firstNameEnIn", fn_req_en);
        params.addValue("lastNameIn", lastName);
        params.addValue("lastNameEnIn", ln_req_en);
        params.addValue("patronymicIn", patronymic);
        params.addValue("patronymicEnIn", pn_req_en);
        params.addValue("lastNames", ln_seqs);
        params.addValue("lastNamesEn", ln_seqs_en);
        params.addValue("last_name_lcsMin", Math.max(MIN_SIMILARITY_LASTNAME, MIN_SIMILARITY_COMMON));
        params.addValue("firstNames", fn_seqs);
        params.addValue("firstNamesEn", fn_seqs_en);
        params.addValue("first_name_lcsMin", Math.max(MIN_SIMILARITY_FIRSTNAME, MIN_SIMILARITY_COMMON));
        params.addValue("patronymics", pn_seqs);
        params.addValue("patronymicsEn", pn_seqs_en);
        params.addValue("patronymic_lcsMin", Math.max(MIN_SIMILARITY_PATRONYMIC, MIN_SIMILARITY_COMMON));
        params.addValue("resultLimit", RESULT_LIMIT);

        var results = namedParameterJdbcTemplate.query(sql, params, (rs, rowNum) ->
                new Figurant(
                        String.valueOf(rs.getInt("id")),
                        rs.getString("last_name"), rs.getString("first_name"), rs.getString("patronymic"),
                        rs.getDouble("last_name_lcs_out"), rs.getDouble("first_name_lcs_out"), rs.getDouble("patronymic_lcs_out"),
                        rs.getString("stoplist_id")
                )
        );

        return results.stream()
                .sorted(Comparator.comparingDouble(Figurant::getSimilarity).reversed())
                .collect(Collectors.toList());

    }

    public SimilarityResult checkSimilarity(SimilarityRequest request) {

        try {
            double score = lcsSimilarity.calculateSimilarityWithIcanTransliterator(request.getString_1(), request.getString_2());
            var status = verdict(score);
            return new SimilarityResult(request.getString_1(), request.getString_2(), status, score);
        } catch (Exception e) {
            return null;
        }
    }

    private SimilarityResult.Status verdict(double score) {
        if (score >= (double)thresholdHigh) {
            return SimilarityResult.Status.REJECTED;
        } else {
            return score >= (double)thresholdLow ? SimilarityResult.Status.MANUAL_REVIEW : SimilarityResult.Status.APPROVED;
        }
    }
 }