package at.procon.dip.domain.ted.search;

import at.procon.dip.domain.ted.search.dto.TedStructuredSearchFacetEntry;
import at.procon.dip.domain.ted.search.dto.TedStructuredSearchFacets;
import at.procon.dip.domain.ted.search.dto.TedStructuredSearchFilter;
import at.procon.dip.domain.ted.search.dto.TedStructuredSearchSummaryRow;
import at.procon.ted.model.entity.ContractNature;
import at.procon.ted.model.entity.NoticeType;
import at.procon.ted.model.entity.ProcedureType;
import java.math.BigDecimal;
import java.sql.Array;
import java.sql.SQLException;
import java.time.LocalDate;
import java.time.OffsetDateTime;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;
import java.util.stream.Collectors;
import lombok.RequiredArgsConstructor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

@Repository
@RequiredArgsConstructor
public class TedStructuredSearchRepository {

    private final NamedParameterJdbcTemplate jdbcTemplate;

    public List<UUID> findCandidateDocumentIds(TedStructuredSearchFilter filter, int limit) {
        StringBuilder sql = new StringBuilder(baseFromWhere(filter, false));
        sql.insert(0, "SELECT p.document_id ");
        sql.append(" GROUP BY p.document_id, p.publication_date, p.created_at");
        sql.append(" ORDER BY p.publication_date DESC NULLS LAST, p.created_at DESC LIMIT :limit");
        MapSqlParameterSource params = params(filter);
        params.addValue("limit", limit);
        return jdbcTemplate.query(sql.toString(), params, (rs, rowNum) -> rs.getObject(1, UUID.class));
    }

    public long countDistinctDocuments(TedStructuredSearchFilter filter) {
        StringBuilder sql = new StringBuilder("SELECT COUNT(DISTINCT p.document_id) ");
        sql.append(baseFromWhere(filter, false));
        return jdbcTemplate.queryForObject(sql.toString(), params(filter), Long.class);
    }

    public List<TedStructuredSearchSummaryRow> searchStructured(TedStructuredSearchFilter filter,
                                                                int page,
                                                                int size,
                                                                String sortBy,
                                                                String sortDirection) {
        StringBuilder sql = new StringBuilder("""
                SELECT
                    p.document_id,
                    p.publication_id,
                    p.notice_id,
                    p.notice_type,
                    p.project_title,
                    p.buyer_name,
                    p.buyer_country_code,
                    p.buyer_city,
                    p.contract_nature,
                    p.procedure_type,
                    p.publication_date,
                    p.submission_deadline,
                    p.cpv_codes,
                    p.total_lots,
                    p.estimated_value,
                    p.estimated_value_currency
                """);
        sql.append(baseFromWhere(filter, false));
        sql.append(" GROUP BY p.document_id, p.publication_id, p.notice_id, p.notice_type, p.project_title, p.buyer_name, p.buyer_country_code, p.buyer_city, p.contract_nature, p.procedure_type, p.publication_date, p.submission_deadline, p.cpv_codes, p.total_lots, p.estimated_value, p.estimated_value_currency");
        sql.append(" ORDER BY ").append(resolveSort(sortBy, sortDirection));
        sql.append(" LIMIT :limit OFFSET :offset");

        MapSqlParameterSource params = params(filter);
        params.addValue("limit", size);
        params.addValue("offset", Math.max(0, page) * size);
        return jdbcTemplate.query(sql.toString(), params, SUMMARY_ROW_MAPPER);
    }

    public List<TedStructuredSearchSummaryRow> findSummariesByDocumentIds(List<UUID> documentIds) {
        if (documentIds == null || documentIds.isEmpty()) {
            return List.of();
        }
        String sql = """
                SELECT
                    p.document_id,
                    p.publication_id,
                    p.notice_id,
                    p.notice_type,
                    p.project_title,
                    p.buyer_name,
                    p.buyer_country_code,
                    p.buyer_city,
                    p.contract_nature,
                    p.procedure_type,
                    p.publication_date,
                    p.submission_deadline,
                    p.cpv_codes,
                    p.total_lots,
                    p.estimated_value,
                    p.estimated_value_currency
                FROM TED.ted_notice_projection p
                WHERE p.document_id IN (:documentIds)
                """;
        List<TedStructuredSearchSummaryRow> rows = jdbcTemplate.query(sql, new MapSqlParameterSource("documentIds", documentIds), SUMMARY_ROW_MAPPER);
        Map<UUID, TedStructuredSearchSummaryRow> byId = rows.stream().collect(Collectors.toMap(TedStructuredSearchSummaryRow::documentId, r -> r));
        List<TedStructuredSearchSummaryRow> ordered = new ArrayList<>();
        for (UUID id : documentIds) {
            TedStructuredSearchSummaryRow row = byId.get(id);
            if (row != null) {
                ordered.add(row);
            }
        }
        return ordered;
    }

    public TedStructuredSearchFacets computeFacets(TedStructuredSearchFilter filter, int bucketLimit) {
        int safeLimit = Math.max(1, bucketLimit);
        return TedStructuredSearchFacets.builder()
                .countries(runFacet(filter, "COALESCE(p.buyer_country_code, '')", "COALESCE(p.buyer_country_code, '')", safeLimit))
                .noticeTypes(runFacet(filter, "CAST(p.notice_type AS text)", "CAST(p.notice_type AS text)", safeLimit))
                .procedureTypes(runFacet(filter, "CAST(p.procedure_type AS text)", "CAST(p.procedure_type AS text)", safeLimit))
                .buyers(runFacet(filter, "COALESCE(p.buyer_name, '')", "COALESCE(p.buyer_name, '')", safeLimit))
                .publicationMonths(runFacet(filter, "to_char(p.publication_date, 'YYYY-MM')", "to_char(p.publication_date, 'YYYY-MM')", safeLimit))
                .cpvFamilies(runCpvFamilyFacet(filter, safeLimit))
                .build();
    }

    private List<TedStructuredSearchFacetEntry> runFacet(TedStructuredSearchFilter filter,
                                                         String keyExpr,
                                                         String labelExpr,
                                                         int limit) {
        StringBuilder sql = new StringBuilder("SELECT ")
                .append(keyExpr).append(" AS key, ")
                .append(labelExpr).append(" AS label, COUNT(DISTINCT p.document_id) AS cnt ");
        sql.append(baseFromWhere(filter, false));
        sql.append(" GROUP BY ").append(keyExpr).append(", ").append(labelExpr)
                .append(" HAVING ").append(keyExpr).append(" IS NOT NULL AND ").append(keyExpr).append(" <> ''")
                .append(" ORDER BY cnt DESC, label ASC LIMIT :facetLimit");
        MapSqlParameterSource params = params(filter);
        params.addValue("facetLimit", limit);
        return jdbcTemplate.query(sql.toString(), params, (rs, rowNum) -> TedStructuredSearchFacetEntry.builder()
                .key(rs.getString("key"))
                .label(rs.getString("label"))
                .count(rs.getLong("cnt"))
                .build());
    }

    private List<TedStructuredSearchFacetEntry> runCpvFamilyFacet(TedStructuredSearchFilter filter, int limit) {
        StringBuilder sql = new StringBuilder("""
                SELECT LEFT(code, 2) AS key, LEFT(code, 2) AS label, COUNT(DISTINCT document_id) AS cnt
                FROM (
                    SELECT p.document_id, unnest(COALESCE(p.cpv_codes, ARRAY[]::varchar[])) AS code
                    """);
        sql.append(baseFromWhere(filter, true));
        sql.append(" UNION ALL SELECT p.document_id, unnest(COALESCE(l.cpv_codes, ARRAY[]::varchar[])) AS code ");
        sql.append(baseFromWhere(filter, false));
        sql.append(" ) cpv WHERE code IS NOT NULL AND code <> '' GROUP BY LEFT(code, 2) ORDER BY cnt DESC, label ASC LIMIT :facetLimit");
        MapSqlParameterSource params = params(filter);
        params.addValue("facetLimit", limit);
        return jdbcTemplate.query(sql.toString(), params, (rs, rowNum) -> TedStructuredSearchFacetEntry.builder()
                .key(rs.getString("key"))
                .label(rs.getString("label"))
                .count(rs.getLong("cnt"))
                .build());
    }

    private String baseFromWhere(TedStructuredSearchFilter filter, boolean projectionOnly) {
        StringBuilder sql = new StringBuilder("""
                FROM TED.ted_notice_projection p
                JOIN DOC.doc_document d ON d.id = p.document_id
                """);
        if (!projectionOnly) {
            sql.append(" LEFT JOIN TED.ted_notice_lot l ON l.notice_projection_id = p.id");
            sql.append(" LEFT JOIN TED.ted_notice_organization o ON o.notice_projection_id = p.id");
        }
        sql.append(" WHERE 1=1");
        appendFilters(sql, filter, projectionOnly);
        return sql.toString();
    }

    private MapSqlParameterSource params(TedStructuredSearchFilter filter) {
        MapSqlParameterSource params = new MapSqlParameterSource();
        if (filter == null) {
            return params;
        }
        if (StringUtils.hasText(filter.getCountryCode())) {
            params.addValue("countryCode", filter.getCountryCode().trim());
        }
        if (filter.getCountryCodes() != null && !filter.getCountryCodes().isEmpty()) {
            params.addValue("countryCodes", filter.getCountryCodes());
        }
        if (filter.getNoticeType() != null) {
            params.addValue("noticeType", filter.getNoticeType().name());
        }
        if (filter.getContractNature() != null) {
            params.addValue("contractNature", filter.getContractNature().name());
        }
        if (filter.getProcedureType() != null) {
            params.addValue("procedureType", filter.getProcedureType().name());
        }
        if (StringUtils.hasText(filter.getCpvPrefix())) {
            params.addValue("cpvPrefixLike", filter.getCpvPrefix().trim() + "%");
        }
        if (filter.getCpvCodes() != null && !filter.getCpvCodes().isEmpty()) {
            params.addValue("cpvCodes", filter.getCpvCodes());
        }
        if (StringUtils.hasText(filter.getNutsCode())) {
            params.addValue("nutsCodeLike", filter.getNutsCode().trim() + "%");
        }
        if (filter.getNutsCodes() != null && !filter.getNutsCodes().isEmpty()) {
            params.addValue("nutsCodes", filter.getNutsCodes());
        }
        if (filter.getPublicationDateFrom() != null) {
            params.addValue("publicationDateFrom", filter.getPublicationDateFrom());
        }
        if (filter.getPublicationDateTo() != null) {
            params.addValue("publicationDateTo", filter.getPublicationDateTo());
        }
        if (filter.getSubmissionDeadlineAfter() != null) {
            params.addValue("submissionDeadlineAfter", filter.getSubmissionDeadlineAfter());
        }
        if (filter.getEuFunded() != null) {
            params.addValue("euFunded", filter.getEuFunded());
        }
        if (StringUtils.hasText(filter.getBuyerNameContains())) {
            params.addValue("buyerNameLike", "%" + filter.getBuyerNameContains().trim().toLowerCase() + "%");
        }
        if (StringUtils.hasText(filter.getProjectTitleContains())) {
            params.addValue("projectTitleLike", "%" + filter.getProjectTitleContains().trim().toLowerCase() + "%");
        }
        return params;
    }

    private void appendFilters(StringBuilder sql, TedStructuredSearchFilter filter, boolean projectionOnly) {
        if (filter == null) {
            return;
        }
        if (StringUtils.hasText(filter.getCountryCode())) {
            sql.append(" AND p.buyer_country_code = :countryCode");
        }
        if (filter.getCountryCodes() != null && !filter.getCountryCodes().isEmpty()) {
            sql.append(" AND p.buyer_country_code IN (:countryCodes)");
        }
        if (filter.getNoticeType() != null) {
            sql.append(" AND CAST(p.notice_type AS text) = :noticeType");
        }
        if (filter.getContractNature() != null) {
            sql.append(" AND CAST(p.contract_nature AS text) = :contractNature");
        }
        if (filter.getProcedureType() != null) {
            sql.append(" AND CAST(p.procedure_type AS text) = :procedureType");
        }
        if (StringUtils.hasText(filter.getCpvPrefix())) {
            sql.append(" AND (")
                    .append(" EXISTS (SELECT 1 FROM unnest(COALESCE(p.cpv_codes, ARRAY[]::varchar[])) cpv WHERE cpv LIKE :cpvPrefixLike)");
            if (!projectionOnly) {
                sql.append(" OR EXISTS (SELECT 1 FROM unnest(COALESCE(l.cpv_codes, ARRAY[]::varchar[])) cpv WHERE cpv LIKE :cpvPrefixLike)");
            }
            sql.append(")");
        }
        if (filter.getCpvCodes() != null && !filter.getCpvCodes().isEmpty()) {
            sql.append(" AND (")
                    .append(" EXISTS (SELECT 1 FROM unnest(COALESCE(p.cpv_codes, ARRAY[]::varchar[])) cpv WHERE cpv IN (:cpvCodes))");
            if (!projectionOnly) {
                sql.append(" OR EXISTS (SELECT 1 FROM unnest(COALESCE(l.cpv_codes, ARRAY[]::varchar[])) cpv WHERE cpv IN (:cpvCodes))");
            }
            sql.append(")");
        }
        if (StringUtils.hasText(filter.getNutsCode())) {
            sql.append(" AND (")
                    .append(" p.buyer_nuts_code LIKE :nutsCodeLike")
                    .append(" OR EXISTS (SELECT 1 FROM unnest(COALESCE(p.nuts_codes, ARRAY[]::varchar[])) nuts WHERE nuts LIKE :nutsCodeLike)");
            if (!projectionOnly) {
                sql.append(" OR EXISTS (SELECT 1 FROM unnest(COALESCE(l.nuts_codes, ARRAY[]::varchar[])) nuts WHERE nuts LIKE :nutsCodeLike)")
                        .append(" OR COALESCE(o.nuts_code, '') LIKE :nutsCodeLike");
            }
            sql.append(")");
        }
        if (filter.getNutsCodes() != null && !filter.getNutsCodes().isEmpty()) {
            sql.append(" AND (")
                    .append(" p.buyer_nuts_code IN (:nutsCodes)")
                    .append(" OR EXISTS (SELECT 1 FROM unnest(COALESCE(p.nuts_codes, ARRAY[]::varchar[])) nuts WHERE nuts IN (:nutsCodes))");
            if (!projectionOnly) {
                sql.append(" OR EXISTS (SELECT 1 FROM unnest(COALESCE(l.nuts_codes, ARRAY[]::varchar[])) nuts WHERE nuts IN (:nutsCodes))")
                        .append(" OR COALESCE(o.nuts_code, '') IN (:nutsCodes)");
            }
            sql.append(")");
        }
        if (filter.getPublicationDateFrom() != null) {
            sql.append(" AND p.publication_date >= :publicationDateFrom");
        }
        if (filter.getPublicationDateTo() != null) {
            sql.append(" AND p.publication_date <= :publicationDateTo");
        }
        if (filter.getSubmissionDeadlineAfter() != null) {
            sql.append(" AND (p.submission_deadline > :submissionDeadlineAfter");
            if (!projectionOnly) {
                sql.append(" OR l.submission_deadline > :submissionDeadlineAfter");
            }
            sql.append(")");
        }
        if (filter.getEuFunded() != null) {
            if (filter.getEuFunded()) {
                sql.append(" AND (COALESCE(p.eu_funded, false) = true");
                if (!projectionOnly) {
                    sql.append(" OR COALESCE(l.eu_funded, false) = true");
                }
                sql.append(")");
            } else {
                sql.append(" AND COALESCE(p.eu_funded, false) = false");
                if (!projectionOnly) {
                    sql.append(" AND NOT EXISTS (SELECT 1 FROM TED.ted_notice_lot lx WHERE lx.notice_projection_id = p.id AND COALESCE(lx.eu_funded, false) = true)");
                }
            }
        }
        if (StringUtils.hasText(filter.getBuyerNameContains())) {
            sql.append(" AND (")
                    .append(" LOWER(COALESCE(p.buyer_name, '')) LIKE :buyerNameLike");
            if (!projectionOnly) {
                sql.append(" OR LOWER(COALESCE(o.name, '')) LIKE :buyerNameLike");
            }
            sql.append(")");
        }
        if (StringUtils.hasText(filter.getProjectTitleContains())) {
            sql.append(" AND (")
                    .append(" LOWER(COALESCE(p.project_title, '')) LIKE :projectTitleLike");
            if (!projectionOnly) {
                sql.append(" OR LOWER(COALESCE(l.title, '')) LIKE :projectTitleLike");
            }
            sql.append(")");
        }
    }

    private String resolveSort(String sortBy, String sortDirection) {
        boolean asc = "asc".equalsIgnoreCase(sortDirection);
        String dir = asc ? "ASC" : "DESC";
        String field = sortBy == null ? "publicationDate" : sortBy;
        return switch (field) {
            case "submissionDeadline" -> "p.submission_deadline " + dir + " NULLS LAST, p.publication_date DESC NULLS LAST";
            case "buyerName" -> "p.buyer_name " + dir + " NULLS LAST, p.publication_date DESC NULLS LAST";
            case "projectTitle" -> "p.project_title " + dir + " NULLS LAST, p.publication_date DESC NULLS LAST";
            default -> "p.publication_date " + dir + " NULLS LAST";
        };
    }

    private static final RowMapper<TedStructuredSearchSummaryRow> SUMMARY_ROW_MAPPER = (rs, rowNum) -> new TedStructuredSearchSummaryRow(
            rs.getObject("document_id", UUID.class),
            rs.getString("publication_id"),
            rs.getString("notice_id"),
            parseNoticeType(rs.getString("notice_type")),
            rs.getString("project_title"),
            rs.getString("buyer_name"),
            rs.getString("buyer_country_code"),
            rs.getString("buyer_city"),
            parseContractNature(rs.getString("contract_nature")),
            parseProcedureType(rs.getString("procedure_type")),
            rs.getObject("publication_date", LocalDate.class),
            rs.getObject("submission_deadline", OffsetDateTime.class),
            stringArray(rs.getArray("cpv_codes")),
            rs.getObject("total_lots") != null ? rs.getInt("total_lots") : null,
            rs.getBigDecimal("estimated_value"),
            rs.getString("estimated_value_currency")
    );

    private static NoticeType parseNoticeType(String value) {
        return value == null ? null : NoticeType.valueOf(value);
    }

    private static ContractNature parseContractNature(String value) {
        return value == null ? null : ContractNature.valueOf(value);
    }

    private static ProcedureType parseProcedureType(String value) {
        return value == null ? null : ProcedureType.valueOf(value);
    }

    private static List<String> stringArray(Array array) throws SQLException {
        if (array == null) {
            return List.of();
        }
        Object raw = array.getArray();
        if (raw instanceof String[] strings) {
            return Arrays.asList(strings);
        }
        return List.of();
    }
}
