package at.procon.dip.search.repository;

import at.procon.dip.domain.access.DocumentVisibility;
import at.procon.dip.domain.document.DocumentAttributeValueType;
import at.procon.dip.domain.document.DocumentFamily;
import at.procon.dip.domain.document.DocumentType;
import at.procon.dip.domain.document.RepresentationType;
import at.procon.dip.search.api.SearchExecutionContext;
import at.procon.dip.search.dto.DocumentAttributeFilterOperator;
import at.procon.dip.search.dto.DocumentAttributeFilterRequest;
import at.procon.dip.search.dto.SearchRepresentationSelectionMode;
import java.math.BigDecimal;
import java.time.LocalDate;
import java.time.OffsetDateTime;
import java.util.Collection;
import java.util.List;
import java.util.Locale;
import java.util.Set;
import java.util.stream.Collectors;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

final class SearchSqlFilterSupport {

    private SearchSqlFilterSupport() {
    }

    static void appendCommonFilters(StringBuilder sql,
                                    MapSqlParameterSource params,
                                    SearchExecutionContext context,
                                    String documentAlias,
                                    String representationAlias,
                                    boolean tenantJoinPresent) {
        Set<DocumentType> documentTypes = firstNonEmpty(context.getRequest().getDocumentTypes(), context.getScope().documentTypes());
        if (!CollectionUtils.isEmpty(documentTypes)) {
            appendTextEnumFilter(sql, params, documentAlias + ".document_type", documentTypes, "documentTypes");
        }

        Set<DocumentFamily> documentFamilies = firstNonEmpty(context.getRequest().getDocumentFamilies(), context.getScope().documentFamilies());
        if (!CollectionUtils.isEmpty(documentFamilies)) {
            appendTextEnumFilter(sql, params, documentAlias + ".document_family", documentFamilies, "documentFamilies");
        }

        Set<DocumentVisibility> visibilities = firstNonEmpty(context.getRequest().getVisibilities(), context.getScope().visibilities());
        if (!CollectionUtils.isEmpty(visibilities)) {
            appendTextEnumFilter(sql, params, documentAlias + ".visibility", visibilities, "visibilities");
        }

        Set<String> languageCodes = context.getRequest().getLanguageCodes();
        if (CollectionUtils.isEmpty(languageCodes) && context.getScope().languageCode() != null && !context.getScope().languageCode().isBlank()) {
            languageCodes = Set.of(context.getScope().languageCode());
        }
        if (!CollectionUtils.isEmpty(languageCodes)) {
            sql.append(" AND COALESCE(").append(representationAlias).append(".language_code, ")
                    .append(documentAlias).append(".language_code, '') IN (:languageCodes)");
            params.addValue("languageCodes", languageCodes);
        }

        Set<RepresentationType> representationTypes = context.getRequest().getRepresentationTypes();
        if (!CollectionUtils.isEmpty(representationTypes)) {
            appendTextEnumFilter(sql, params, representationAlias + ".representation_type", representationTypes, "representationTypes");
        } else {
            SearchRepresentationSelectionMode selectionMode = context.getRequest().getRepresentationSelectionMode();
            if (selectionMode == null) {
                selectionMode = SearchRepresentationSelectionMode.PRIMARY_AND_CHUNKS;
            }
            switch (selectionMode) {
                case PRIMARY_ONLY -> sql.append(" AND ").append(representationAlias).append(".is_primary = true");
                case PRIMARY_AND_CHUNKS -> sql.append(" AND (")
                        .append(representationAlias).append(".is_primary = true OR CAST(")
                        .append(representationAlias).append(".representation_type AS text) = 'CHUNK')");
                case ALL -> {
                    // no implicit representation restriction
                }
            }
        }

        if (context.getRequest().getCreatedFrom() != null) {
            sql.append(" AND ").append(documentAlias).append(".created_at >= :createdFrom");
            params.addValue("createdFrom", context.getRequest().getCreatedFrom());
        }
        if (context.getRequest().getCreatedTo() != null) {
            sql.append(" AND ").append(documentAlias).append(".created_at <= :createdTo");
            params.addValue("createdTo", context.getRequest().getCreatedTo());
        }

        if (tenantJoinPresent && !CollectionUtils.isEmpty(context.getScope().ownerTenantKeys())) {
            sql.append(" AND dt.tenant_key IN (:ownerTenantKeys)");
            params.addValue("ownerTenantKeys", context.getScope().ownerTenantKeys());
        }

        if (context.getScope() != null && !CollectionUtils.isEmpty(context.getScope().candidateDocumentIds())) {
            sql.append(" AND ").append(documentAlias).append(".id IN (:candidateDocumentIds)");
            params.addValue("candidateDocumentIds", context.getScope().candidateDocumentIds());
        }

        appendAttributeFilters(sql, params, context, documentAlias);
    }

    private static void appendAttributeFilters(StringBuilder sql,
                                               MapSqlParameterSource params,
                                               SearchExecutionContext context,
                                               String documentAlias) {
        List<DocumentAttributeFilterRequest> filters = context.getRequest().getAttributeFilters();
        if (CollectionUtils.isEmpty(filters)) {
            return;
        }
        int index = 0;
        for (DocumentAttributeFilterRequest filter : filters) {
            if (filter == null || !StringUtils.hasText(filter.getName())) {
                continue;
            }
            String filterAlias = "af" + index;
            String nameParam = "attributeName" + index;
            String contextParam = "attributeContext" + index;
            String valueParam = "attributeValue" + index;

            sql.append(" AND ");
            DocumentAttributeFilterOperator operator = filter.getOperator() == null
                    ? DocumentAttributeFilterOperator.EQ
                    : filter.getOperator();
            if (operator == DocumentAttributeFilterOperator.NE) {
                sql.append("NOT ");
            }
            sql.append("EXISTS (SELECT 1 FROM doc.doc_document_attribute ").append(filterAlias)
                    .append(" JOIN doc.doc_attribute_name ").append(filterAlias).append("n ON ")
                    .append(filterAlias).append("n.id = ").append(filterAlias).append(".attribute_name_id")
                    .append(" WHERE ").append(filterAlias).append(".document_id = ").append(documentAlias).append(".id")
                    .append(" AND ").append(filterAlias).append("n.normalized_name = :").append(nameParam);
            params.addValue(nameParam, normalizeName(filter.getName()));

            if (StringUtils.hasText(filter.getContext())) {
                sql.append(" AND ").append(filterAlias).append("n.attribute_context = :").append(contextParam);
                params.addValue(contextParam, filter.getContext().trim().toUpperCase(Locale.ROOT));
            }

            if (operator != DocumentAttributeFilterOperator.EXISTS && operator != DocumentAttributeFilterOperator.NE) {
                appendAttributeValuePredicate(sql, params, filterAlias, valueParam, filter, operator);
            } else if (operator == DocumentAttributeFilterOperator.NE) {
                appendAttributeValuePredicate(sql, params, filterAlias, valueParam, filter, DocumentAttributeFilterOperator.EQ);
            }
            sql.append(")");
            index++;
        }
    }

    private static void appendAttributeValuePredicate(StringBuilder sql,
                                                      MapSqlParameterSource params,
                                                      String filterAlias,
                                                      String valueParam,
                                                      DocumentAttributeFilterRequest filter,
                                                      DocumentAttributeFilterOperator operator) {
        ResolvedAttributeFilterValue resolved = resolveFilterValue(filter);
        String column = switch (resolved.type()) {
            case STRING -> filterAlias + ".string_value";
            case INTEGER -> filterAlias + ".integer_value";
            case NUMBER -> filterAlias + ".number_value";
            case DATE -> filterAlias + ".date_value";
            case DATETIME -> filterAlias + ".datetime_value";
            case BOOLEAN -> filterAlias + ".boolean_value";
        };
        String sqlOperator = switch (operator) {
            case EQ -> "=";
            case GT -> ">";
            case GTE -> ">=";
            case LT -> "<";
            case LTE -> "<=";
            case NE, EXISTS -> throw new IllegalArgumentException("Unsupported attribute operator in value predicate: " + operator);
        };

        if ((resolved.type() == DocumentAttributeValueType.STRING || resolved.type() == DocumentAttributeValueType.BOOLEAN)
                && operator != DocumentAttributeFilterOperator.EQ) {
            throw new IllegalArgumentException("Only EQ/NE operators are supported for " + resolved.type() + " attribute filters");
        }

        sql.append(" AND ").append(column).append(" ").append(sqlOperator).append(" :").append(valueParam);
        params.addValue(valueParam, resolved.value());
    }

    private static ResolvedAttributeFilterValue resolveFilterValue(DocumentAttributeFilterRequest filter) {
        DocumentAttributeValueType type = filter.getType();
        if (type == null) {
            if (StringUtils.hasText(filter.getStringValue())) {
                type = DocumentAttributeValueType.STRING;
            } else if (filter.getIntegerValue() != null) {
                type = DocumentAttributeValueType.INTEGER;
            } else if (filter.getNumberValue() != null) {
                type = DocumentAttributeValueType.NUMBER;
            } else if (filter.getDateValue() != null) {
                type = DocumentAttributeValueType.DATE;
            } else if (filter.getDatetimeValue() != null) {
                type = DocumentAttributeValueType.DATETIME;
            } else if (filter.getBooleanValue() != null) {
                type = DocumentAttributeValueType.BOOLEAN;
            } else {
                throw new IllegalArgumentException("Attribute filter requires a typed value unless operator is EXISTS");
            }
        }

        Object value = switch (type) {
            case STRING -> requireText(filter.getStringValue(), filter.getName(), "string");
            case INTEGER -> requireValue(filter.getIntegerValue(), filter.getName(), "integer");
            case NUMBER -> requireNumber(filter.getNumberValue(), filter.getName());
            case DATE -> requireValue(filter.getDateValue(), filter.getName(), "date");
            case DATETIME -> requireValue(filter.getDatetimeValue(), filter.getName(), "datetime");
            case BOOLEAN -> requireValue(filter.getBooleanValue(), filter.getName(), "boolean");
        };
        return new ResolvedAttributeFilterValue(type, value);
    }

    private static String requireText(String value, String name, String kind) {
        if (!StringUtils.hasText(value)) {
            throw new IllegalArgumentException("Attribute filter '" + name + "' requires a " + kind + " value");
        }
        return value.trim();
    }

    private static BigDecimal requireNumber(BigDecimal value, String name) {
        if (value == null) {
            throw new IllegalArgumentException("Attribute filter '" + name + "' requires a number value");
        }
        return value.stripTrailingZeros();
    }

    private static <T> T requireValue(T value, String name, String kind) {
        if (value == null) {
            throw new IllegalArgumentException("Attribute filter '" + name + "' requires a " + kind + " value");
        }
        return value;
    }

    private static String normalizeName(String name) {
        return name.trim().toLowerCase(Locale.ROOT);
    }

    private static <T> Set<T> firstNonEmpty(Set<T> primary, Set<T> fallback) {
        return !CollectionUtils.isEmpty(primary) ? primary : fallback;
    }

    private static void appendTextEnumFilter(StringBuilder sql,
                                             MapSqlParameterSource params,
                                             String columnExpression,
                                             Collection<? extends Enum<?>> values,
                                             String parameterName) {
        sql.append(" AND CAST(").append(columnExpression).append(" AS text) IN (:").append(parameterName).append(")");
        params.addValue(parameterName, enumNames(values));
    }

    private static List<String> enumNames(Collection<? extends Enum<?>> values) {
        return values.stream().map(Enum::name).collect(Collectors.toList());
    }

    private record ResolvedAttributeFilterValue(DocumentAttributeValueType type, Object value) {
    }
}
