package com.agent.management.rag.structured;

import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@Service
public class StructuredSqlRelationshipValidator {
    private static final Pattern BASE_TABLE = Pattern.compile(
            "(?is)\\bFROM\\s+`?([A-Za-z0-9_]+)`?(?:\\s+(?:AS\\s+)?([A-Za-z0-9_]+))?");
    private static final Pattern JOIN = Pattern.compile(
            "(?is)\\bJOIN\\s+`?([A-Za-z0-9_]+)`?(?:\\s+(?:AS\\s+)?([A-Za-z0-9_]+))?\\s+ON\\s+(.+?)"
                    + "(?=\\b(?:LEFT|RIGHT|INNER|OUTER|CROSS)?\\s*JOIN\\b|\\bWHERE\\b|\\bGROUP\\b|\\bORDER\\b|\\bLIMIT\\b|$)");
    private static final Pattern CARTESIAN_COMMA = Pattern.compile(
            "(?is)\\bFROM\\s+`?[A-Za-z0-9_]+`?(?:\\s+[A-Za-z0-9_]+)?\\s*,\\s*`?[A-Za-z0-9_]+`?");
    private static final Pattern CROSS_JOIN = Pattern.compile("(?is)\\bCROSS\\s+JOIN\\b");
    private static final Pattern AGGREGATE = Pattern.compile("(?i)\\b(?:COUNT|AVG|MIN|MAX|SUM)\\s*\\(");
    private static final Pattern SCALAR_FROM = Pattern.compile(
            "(?is)^FROM\\s+`?[A-Za-z0-9_]+`?(?:\\s+(?:AS\\s+)?[A-Za-z0-9_]+)?(?:\\s+WHERE\\s+.+)?$");
    private static final Set<String> RESERVED = Set.of("where", "left", "right", "inner", "outer", "join",
            "group", "order", "limit", "having", "on", "union");

    public ValidationResult validate(String sql, StructuredQueryContext context) {
        int scalarCrossJoinCount = validateCrossJoins(sql);
        if (CARTESIAN_COMMA.matcher(sql).find()) {
            throw new IllegalArgumentException("SQL contains an obvious cartesian product");
        }
        Matcher base = BASE_TABLE.matcher(sql);
        if (!base.find()) return new ValidationResult(List.of(), List.of());
        Map<String, String> aliases = new HashMap<>();
        String baseTable = base.group(1);
        aliases.put(alias(base.group(2), baseTable), baseTable);
        Set<String> introduced = new LinkedHashSet<>();
        introduced.add(baseTable);
        List<String> used = new ArrayList<>();
        List<String> warnings = new ArrayList<>();

        Matcher joins = JOIN.matcher(sql);
        while (joins.find()) {
            String joinedTable = joins.group(1);
            String joinedAlias = alias(joins.group(2), joinedTable);
            aliases.put(joinedAlias, joinedTable);
            String onClause = joins.group(3);
            List<StructuredRelationship> candidates = context.relationships().stream()
                    .filter(relation -> relation.type() != StructuredRelationship.RelationshipType.AMBIGUOUS)
                    .filter(relation -> relation.confidence() >= 0.58)
                    .filter(relation -> connects(relation, joinedTable, introduced))
                    .filter(relation -> predicatePresent(relation, onClause, aliases))
                    .sorted((left, right) -> Double.compare(right.confidence(), left.confidence())).toList();
            StructuredRelationship selected = candidates.stream()
                    .filter(relation -> conditionsPresent(relation, sql, aliases)).findFirst().orElse(null);
            if (selected == null) {
                boolean missingCondition = candidates.stream().anyMatch(relation -> !relation.conditions().isEmpty());
                throw new IllegalArgumentException(missingCondition
                        ? "SQL JOIN omitted a required conditional relationship predicate for " + joinedTable
                        : "SQL JOIN is not supported by the structured relationship profile for " + joinedTable);
            }
            used.add(selected.id());
            if (selected.confidence() < 0.78) {
                warnings.add("JOIN used a medium-confidence relationship: " + selected.id());
            }
            warnings.addAll(selected.warnings());
            introduced.add(joinedTable);
        }

        int joinTokenCount = countMatches(Pattern.compile("(?i)\\bJOIN\\b"), sql);
        if (joinTokenCount - scalarCrossJoinCount != used.size()) {
            throw new IllegalArgumentException("SQL contains a JOIN without a verifiable ON relationship");
        }
        return new ValidationResult(List.copyOf(used), List.copyOf(warnings));
    }

    private static boolean connects(StructuredRelationship relation, String joinedTable, Set<String> introduced) {
        return relation.sourceTable().equalsIgnoreCase(joinedTable) && containsIgnoreCase(introduced, relation.targetTable())
                || relation.targetTable().equalsIgnoreCase(joinedTable) && containsIgnoreCase(introduced, relation.sourceTable());
    }

    private static boolean predicatePresent(StructuredRelationship relation, String clause, Map<String, String> aliases) {
        String normalized = normalizeSql(clause);
        for (String sourceAlias : qualifiers(aliases, relation.sourceTable())) {
            String source = sourceAlias + "." + relation.sourceColumn();
            for (String targetAlias : qualifiers(aliases, relation.targetTable())) {
                String target = targetAlias + "." + relation.targetColumn();
                if (normalized.contains(source.toLowerCase(Locale.ROOT) + "=" + target.toLowerCase(Locale.ROOT))
                        || normalized.contains(target.toLowerCase(Locale.ROOT) + "=" + source.toLowerCase(Locale.ROOT))) {
                    return true;
                }
            }
        }
        return false;
    }

    private static boolean conditionsPresent(StructuredRelationship relation, String sql, Map<String, String> aliases) {
        String normalized = normalizeSql(sql);
        for (StructuredRelationship.Condition condition : relation.conditions()) {
            boolean found = qualifiers(aliases, condition.table()).stream().anyMatch(alias -> {
                String field = alias + "." + condition.column();
                String expected = field.toLowerCase(Locale.ROOT) + condition.operator().toLowerCase(Locale.ROOT)
                        + normalizeSql(literal(condition.value()));
                return normalized.contains(expected);
            });
            if (!found) return false;
        }
        return true;
    }

    private static List<String> qualifiers(Map<String, String> aliases, String table) {
        List<String> result = aliases.entrySet().stream().filter(entry -> entry.getValue().equalsIgnoreCase(table))
                .map(Map.Entry::getKey).toList();
        return result.isEmpty() ? List.of(table) : result;
    }

    private static int validateCrossJoins(String sql) {
        int count = 0;
        Matcher matcher = CROSS_JOIN.matcher(sql);
        while (matcher.find()) {
            int open = skipWhitespace(sql, matcher.end());
            if (open >= sql.length() || sql.charAt(open) != '(') {
                throw new IllegalArgumentException("SQL contains an obvious cartesian product");
            }
            int close = matchingParenthesis(sql, open);
            if (close < 0 || !isSafeScalarAggregate(sql.substring(open + 1, close))) {
                throw new IllegalArgumentException("SQL contains an obvious cartesian product");
            }
            int aliasStart = skipWhitespace(sql, close + 1);
            if (startsWithWord(sql, aliasStart, "AS")) aliasStart = skipWhitespace(sql, aliasStart + 2);
            if (aliasStart >= sql.length() || !isIdentifierStart(sql.charAt(aliasStart))) {
                throw new IllegalArgumentException("SQL scalar aggregate JOIN must have an alias");
            }
            count++;
        }
        return count;
    }

    private static boolean isSafeScalarAggregate(String query) {
        String normalized = query.trim().replaceAll("\\s+", " ");
        String upper = normalized.toUpperCase(Locale.ROOT);
        if (!upper.startsWith("SELECT ") || upper.contains(";") || upper.contains(" JOIN ")
                || upper.contains(" UNION ") || upper.contains(" GROUP BY ") || upper.contains(" HAVING ")) {
            return false;
        }
        int from = upper.indexOf(" FROM ");
        if (from < 0 || countMatches(Pattern.compile("(?i)\\bFROM\\b"), normalized) != 1) return false;
        String select = normalized.substring("SELECT ".length(), from);
        String fromClause = normalized.substring(from + 1);
        return AGGREGATE.matcher(select).find() && SCALAR_FROM.matcher(fromClause).matches()
                && !CARTESIAN_COMMA.matcher(normalized).find();
    }

    private static int matchingParenthesis(String value, int open) {
        int depth = 0;
        char quote = 0;
        for (int i = open; i < value.length(); i++) {
            char current = value.charAt(i);
            if (quote != 0) {
                if (current == quote && (i == 0 || value.charAt(i - 1) != '\\')) quote = 0;
                continue;
            }
            if (current == '\'' || current == '"' || current == '`') {
                quote = current;
            } else if (current == '(') {
                depth++;
            } else if (current == ')' && --depth == 0) {
                return i;
            }
        }
        return -1;
    }

    private static int skipWhitespace(String value, int offset) {
        int current = offset;
        while (current < value.length() && Character.isWhitespace(value.charAt(current))) current++;
        return current;
    }

    private static boolean startsWithWord(String value, int offset, String word) {
        int end = offset + word.length();
        return end <= value.length() && value.regionMatches(true, offset, word, 0, word.length())
                && (end == value.length() || !Character.isLetterOrDigit(value.charAt(end)));
    }

    private static boolean isIdentifierStart(char value) {
        return Character.isLetter(value) || value == '_' || value == '`';
    }

    private static String alias(String candidate, String table) {
        return candidate == null || RESERVED.contains(candidate.toLowerCase(Locale.ROOT)) ? table : candidate;
    }

    private static String normalizeSql(String sql) {
        return sql.replace("`", "").replaceAll("\\s+", "").toLowerCase(Locale.ROOT);
    }

    private static String literal(Object value) {
        return value instanceof Number || value instanceof Boolean ? String.valueOf(value)
                : "'" + String.valueOf(value).replace("'", "''") + "'";
    }

    private static boolean containsIgnoreCase(Set<String> values, String expected) {
        return values.stream().anyMatch(value -> value.equalsIgnoreCase(expected));
    }

    private static int countMatches(Pattern pattern, String value) {
        int count = 0; Matcher matcher = pattern.matcher(value); while (matcher.find()) count++; return count;
    }

    public record ValidationResult(List<String> usedRelationships, List<String> warnings) {}
}
