package gudusoft.gsqlparser.resolver2;

import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.ETableSource;
import gudusoft.gsqlparser.TSourceToken;
import gudusoft.gsqlparser.nodes.TObjectName;
import gudusoft.gsqlparser.nodes.TTable;
import gudusoft.gsqlparser.resolver2.matcher.INameMatcher;
import gudusoft.gsqlparser.resolver2.model.AmbiguousColumnSource;
import gudusoft.gsqlparser.resolver2.model.ColumnSource;
import gudusoft.gsqlparser.resolver2.model.FieldPath;
import gudusoft.gsqlparser.resolver2.model.ResolutionContext;
import gudusoft.gsqlparser.resolver2.model.ResolutionResult;
import gudusoft.gsqlparser.resolver2.namespace.INamespace;
import gudusoft.gsqlparser.resolver2.namespace.UnnestNamespace;
import gudusoft.gsqlparser.resolver2.scope.IScope;
import gudusoft.gsqlparser.resolver2.scope.ResolvedImpl;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;

/**
 * Core component for resolving column references to their sources.
 *
 * Key responsibilities:
 * 1. Resolve TObjectName (column references) using scope tree
 * 2. Handle qualified and unqualified names
 * 3. Detect and report ambiguities
 * 4. Apply GUESS_COLUMN_STRATEGY for ambiguous columns
 * 5. Update TObjectName with resolution results
 * 6. Update ResolutionContext for global querying
 */
public class NameResolver {

    private final INameMatcher nameMatcher;
    private final ResolutionContext context;
    private final TSQLResolverConfig config;

    /**
     * Create a NameResolver with full configuration.
     *
     * @param config The resolver configuration (includes name matcher and strategy)
     * @param context The resolution context for tracking results
     */
    public NameResolver(TSQLResolverConfig config, ResolutionContext context) {
        this.config = config;
        this.nameMatcher = config.getNameMatcher();
        this.context = context;
    }

    /**
     * Create a NameResolver with just name matcher (backward compatibility).
     * Uses default configuration for GUESS_COLUMN_STRATEGY.
     *
     * @deprecated Use NameResolver(TSQLResolverConfig, ResolutionContext) instead
     */
    @Deprecated
    public NameResolver(INameMatcher nameMatcher, ResolutionContext context) {
        this.nameMatcher = nameMatcher;
        this.context = context;
        this.config = null; // Will use TBaseType.GUESS_COLUMN_STRATEGY
    }

    /**
     * Get the effective GUESS_COLUMN_STRATEGY.
     * Returns config value if available, otherwise TBaseType.GUESS_COLUMN_STRATEGY.
     */
    private int getGuessColumnStrategy() {
        if (config != null) {
            return config.getGuessColumnStrategy();
        }
        return gudusoft.gsqlparser.TBaseType.GUESS_COLUMN_STRATEGY;
    }

    private static final boolean DEBUG_RESOLUTION = false;

    /**
     * Resolve a column reference (TObjectName) within a given scope.
     *
     * @param objName The column reference to resolve
     * @param scope The scope where the reference appears
     * @return Resolution result
     */
    public ResolutionResult resolve(TObjectName objName, IScope scope) {
        if (objName == null || scope == null) {
            return ResolutionResult.notFound("<null>");
        }

        // Extract name parts from TObjectName
        List<String> nameParts = extractNameParts(objName);
        if (nameParts.isEmpty()) {
            return ResolutionResult.notFound("<empty>");
        }

        if (DEBUG_RESOLUTION) {
            System.out.println("[DEBUG-RESOLVE] Resolving: " + objName +
                " nameParts=" + nameParts + " scopeType=" + scope.getScopeType());
        }

        // Use scope to resolve the name
        ResolvedImpl resolved = new ResolvedImpl();
        scope.resolve(nameParts, nameMatcher, false, resolved);

        if (DEBUG_RESOLUTION) {
            System.out.println("[DEBUG-RESOLVE]   Resolved matches: " + resolved.getCount());
            if (resolved.getCount() > 1) {
                for (ResolvedImpl.Match m : resolved.getMatches()) {
                    System.out.println("[DEBUG-RESOLVE]     Match: " + m.namespace.getDisplayName() +
                        " type=" + m.namespace.getClass().getSimpleName() +
                        " id=" + System.identityHashCode(m.namespace) +
                        " remaining=" + m.remainingNames +
                        " scope=" + m.scope.getScopeType());
                }
            }
        }

        // Process resolution results
        ResolutionResult result = processResolvedMatches(objName, nameParts, resolved);

        // Delta 3: Struct-field fallback for BigQuery/Snowflake
        // If resolution failed and we have a 2-part qualified name like "customer.customer_id",
        // try interpreting it as column.field (struct field access) instead of table.column.
        // Only for 2-part names; 3+ part no-alias case (e.g., customer.address.city) is not
        // handled here to avoid changing resolution paths for existing 3-part BigQuery patterns
        // (e.g., purchases.first.msts) that are correctly handled by DataFlowAnalyzer heuristics.
        // The alias case (o.customer.address.city) IS handled by resolveColumnPath() in
        // processResolvedMatches() and does not depend on this fallback.
        if (!result.isExactMatch() && nameParts.size() == 2 && isStructFieldVendor()) {
            ResolutionResult structFieldResult = tryStructFieldFallback(objName, nameParts, scope);
            if (structFieldResult != null && structFieldResult.isExactMatch()) {
                result = structFieldResult;
                if (DEBUG_RESOLUTION) {
                    System.out.println("[DEBUG-RESOLVE]   Struct-field fallback succeeded for: " + objName);
                }
            }
        }

        // Delta 4: Side-channel hint for 3+ part no-alias struct access (BigQuery only).
        // When resolution failed and we have 3+ parts (e.g., customer.address.city),
        // set a StructFieldHint WITHOUT changing the resolution result or sourceTable.
        // This provides struct path info to DataFlowAnalyzer without altering lineage topology.
        if (!result.isExactMatch() && nameParts.size() >= 3 && isStructFieldHintVendor()) {
            trySetStructFieldHint(objName, nameParts, scope);
        }

        if (DEBUG_RESOLUTION) {
            System.out.println("[DEBUG-RESOLVE]   Result: " + result.getStatus() +
                (result.isExactMatch() && result.getColumnSource() != null ?
                    " source=" + result.getColumnSource().getExposedName() : ""));
        }

        // Update TObjectName and context
        updateObjectNameWithResult(objName, result);

        return result;
    }

    /**
     * Check if the current vendor supports struct-field access syntax (column.field).
     * Currently supported: BigQuery, Snowflake
     */
    private boolean isStructFieldVendor() {
        if (config == null) {
            return false;
        }
        EDbVendor vendor = config.getVendor();
        return vendor == EDbVendor.dbvbigquery || vendor == EDbVendor.dbvsnowflake;
    }

    /**
     * Check if the current vendor supports struct-field hint annotations.
     * Currently BigQuery only (Snowflake uses schema.table.column for 3-part names).
     */
    private boolean isStructFieldHintVendor() {
        if (config == null) {
            return false;
        }
        return config.getVendor() == EDbVendor.dbvbigquery;
    }

    /**
     * Delta 4: Try to set a StructFieldHint for 3+ part no-alias struct access.
     *
     * For "customer.address.city" (3 parts, no alias):
     * - Treat the first part ("customer") as a potential base column
     * - If found in any visible namespace, set a hint with fieldPath=["address", "city"]
     * - Does NOT change ResolutionResult or sourceTable
     *
     * @param objName The TObjectName to annotate with a hint
     * @param nameParts The extracted name parts (e.g., ["customer", "address", "city"])
     * @param scope The scope to search in
     */
    private void trySetStructFieldHint(TObjectName objName, List<String> nameParts, IScope scope) {
        String baseColumnName = nameParts.get(0);
        List<String> fieldPathSegments = nameParts.subList(1, nameParts.size());

        // Try to find the base column as an unqualified name
        List<String> singlePartName = Collections.singletonList(baseColumnName);
        ResolvedImpl resolved = new ResolvedImpl();
        scope.resolve(singlePartName, nameMatcher, false, resolved);

        if (resolved.isEmpty()) {
            return; // Base column not found in any namespace
        }

        // Check if any namespace actually has this column
        for (ResolvedImpl.Match match : resolved.getMatches()) {
            gudusoft.gsqlparser.resolver2.model.ColumnSource baseColumnSource =
                match.namespace.resolveColumn(baseColumnName);
            if (baseColumnSource != null) {
                // Found the base column - create hint
                gudusoft.gsqlparser.resolver2.model.FieldPath fieldPath =
                    gudusoft.gsqlparser.resolver2.model.FieldPath.of(fieldPathSegments);
                gudusoft.gsqlparser.resolver2.model.StructFieldHint hint =
                    new gudusoft.gsqlparser.resolver2.model.StructFieldHint(
                        baseColumnName, fieldPath,
                        "struct_field_hint_no_alias", 0.7);
                objName.setStructFieldHint(hint);

                if (DEBUG_RESOLUTION) {
                    System.out.println("[DEBUG-RESOLVE]   StructFieldHint set for: " + objName +
                        " -> base=" + baseColumnName + ", fieldPath=" + fieldPathSegments);
                }
                return;
            }
        }
    }

    /**
     * Delta 3: Try to resolve a qualified name as struct-field access (column.field).
     *
     * In BigQuery/Snowflake, "customer.customer_id" might be:
     * 1. Table "customer" with column "customer_id" (standard interpretation)
     * 2. Column "customer" (STRUCT type) with field "customer_id" (struct-field access)
     *
     * If the standard interpretation failed, try the struct-field interpretation:
     * - Treat the first part as an unqualified column name
     * - If found, return the base column as the source (the STRUCT column)
     * - Preserve the field path (segments beyond the base column) for downstream use
     *
     * @param objName The original TObjectName
     * @param nameParts The extracted name parts (e.g., ["customer", "customer_id"])
     * @param scope The scope to search in
     * @return Resolution result if struct-field interpretation succeeds, null otherwise
     */
    private ResolutionResult tryStructFieldFallback(TObjectName objName, List<String> nameParts, IScope scope) {
        // The base column is the first part (e.g., "customer" in "customer.customer_id")
        String baseColumnName = nameParts.get(0);

        // The field path is everything after the base column
        // For "customer.customer_id", fieldPath = ["customer_id"]
        // For "customer.address.city", fieldPath = ["address", "city"]
        List<String> fieldPathSegments = nameParts.size() > 1
            ? nameParts.subList(1, nameParts.size())
            : Collections.emptyList();

        // Try to resolve the base column as an unqualified name
        List<String> singlePartName = Collections.singletonList(baseColumnName);
        ResolvedImpl resolved = new ResolvedImpl();
        scope.resolve(singlePartName, nameMatcher, false, resolved);

        if (resolved.isEmpty()) {
            return null; // Base column not found
        }

        // Found potential matches - check if any namespace has this column
        for (ResolvedImpl.Match match : resolved.getMatches()) {
            INamespace namespace = match.namespace;

            // Try to resolve the base column name in this namespace
            ColumnSource baseColumnSource = namespace.resolveColumn(baseColumnName);
            if (baseColumnSource != null) {
                // Found the base column - return it as the source with field path preserved
                if (DEBUG_RESOLUTION) {
                    System.out.println("[DEBUG-RESOLVE]   Struct-field: found base column '" +
                        baseColumnName + "' in " + namespace.getDisplayName() +
                        ", fieldPath=" + fieldPathSegments);
                }

                // Create a new ColumnSource with:
                // 1. struct_field_access evidence marker (for backward compatibility)
                // 2. fieldPath preserved (new in Improvement B)
                FieldPath fieldPath = FieldPath.of(fieldPathSegments);
                ColumnSource structFieldSource = baseColumnSource.withFieldPath(fieldPath, "struct_field_access");

                return ResolutionResult.exactMatch(structFieldSource);
            }
        }

        return null;
    }

    /**
     * Extract name parts from TObjectName.
     * Examples:
     * - "col" -> ["col"]
     * - "t.col" -> ["t", "col"]
     * - "schema.table.col" -> ["schema", "table", "col"]
     *
     * For BigQuery/Snowflake struct field access like "customer.customer_id":
     * - If schema/table tokens are not set, but toString() contains dots,
     *   extract all parts from toString() to capture field paths.
     */
    private List<String> extractNameParts(TObjectName objName) {
        List<String> parts = new ArrayList<>();

        // Add schema if present, but NOT when databaseToken is also set.
        // When databaseToken is present, the name is fully qualified (db.schema.table.column)
        // and the schema position IS the schema, not a table alias. Including it would cause
        // the resolver to incorrectly match the schema against table aliases. (Mantis #4268)
        if (objName.getSchemaToken() != null && objName.getDatabaseToken() == null) {
            parts.add(objName.getSchemaString());
        }

        // Add table/qualifier if present
        if (objName.getTableToken() != null) {
            parts.add(objName.getTableString());
        }

        // Add column name
        String columnName = objName.getColumnNameOnly();
        if (columnName != null) {
            parts.add(columnName);
        }

        // Improvement B: Handle BigQuery/Snowflake struct field access
        // If we only got a single part from standard extraction,
        // but toString() contains more segments (dots), extract them
        // This handles cases like "customer.customer_id" where:
        // - getColumnNameOnly() returns "customer"
        // - toString() returns "customer.customer_id"
        if (isStructFieldVendor()) {
            String fullName = objName.toString();
            if (fullName != null && fullName.contains(".")) {
                // Check if fullName has more segments than what we extracted
                String[] fullParts = splitNameParts(fullName);
                if (fullParts.length > parts.size() || hasConsecutiveDuplicates(parts)) {
                    // Replace parts with full extracted segments
                    parts.clear();
                    Collections.addAll(parts, fullParts);
                }
            }
        }

        return parts;
    }

    /**
     * Check if a list has consecutive duplicate elements.
     * This detects parser bugs where segments are duplicated.
     * Example: ["customer", "customer", "address"] returns true.
     */
    private boolean hasConsecutiveDuplicates(List<String> list) {
        if (list == null || list.size() < 2) {
            return false;
        }
        for (int i = 1; i < list.size(); i++) {
            if (list.get(i) != null && list.get(i).equals(list.get(i - 1))) {
                return true;
            }
        }
        return false;
    }


    /**
     * Split a dotted name into parts, handling quoted identifiers.
     * Examples:
     * - "a.b.c" -> ["a", "b", "c"]
     * - "`a.b`.c" -> ["`a.b`", "c"]
     * - "a.`b.c`" -> ["a", "`b.c`"]
     *
     * @param name The dotted name string
     * @return Array of name parts
     */
    private String[] splitNameParts(String name) {
        if (name == null || name.isEmpty()) {
            return new String[0];
        }

        // Simple case: no quotes, just split on dots
        if (!name.contains("`") && !name.contains("\"") && !name.contains("[")) {
            return name.split("\\.");
        }

        // Complex case: handle quoted identifiers
        List<String> parts = new ArrayList<>();
        StringBuilder current = new StringBuilder();
        char quoteChar = 0;

        for (int i = 0; i < name.length(); i++) {
            char c = name.charAt(i);

            if (quoteChar != 0) {
                // Inside a quoted identifier
                current.append(c);
                if (c == quoteChar) {
                    // Check for escaped quote (doubled)
                    if (i + 1 < name.length() && name.charAt(i + 1) == quoteChar) {
                        current.append(name.charAt(++i));
                    } else {
                        // End of quoted identifier
                        quoteChar = 0;
                    }
                }
            } else if (c == '`' || c == '"' || c == '[') {
                // Start of quoted identifier
                quoteChar = (c == '[') ? ']' : c;
                current.append(c);
            } else if (c == '.') {
                // Separator
                if (current.length() > 0) {
                    parts.add(current.toString());
                    current.setLength(0);
                }
            } else {
                current.append(c);
            }
        }

        // Add last part
        if (current.length() > 0) {
            parts.add(current.toString());
        }

        return parts.toArray(new String[0]);
    }

    /**
     * Process resolution matches and determine final result.
     */
    private ResolutionResult processResolvedMatches(TObjectName objName,
                                                    List<String> nameParts,
                                                    ResolvedImpl resolved) {
        String columnName = nameParts.get(nameParts.size() - 1);

        if (resolved.isEmpty()) {
            // No matches found
            return ResolutionResult.notFound(columnName);
        }

        // Deduplicate matches by namespace identity
        // The same namespace can be found through different scope paths (e.g., CTE scope and SELECT scope)
        // but it's still the same source - not truly ambiguous
        List<ResolvedImpl.Match> uniqueMatches = deduplicateMatchesByNamespace(resolved.getMatches());

        if (uniqueMatches.size() == 1) {
            // Exactly one unique namespace - success!
            ResolvedImpl.Match match = uniqueMatches.get(0);
            INamespace namespace = match.namespace;

            // For qualified names like "t.col", we need to resolve the column
            // For unqualified names like "col", we need to find it in the namespace
            ColumnSource columnSource;

            if (!match.remainingNames.isEmpty()) {
                // Still have parts to resolve (e.g., found table, need to find column)
                // Phase B3: Support multi-segment paths (table.column.field...)
                if (match.remainingNames.size() > 1 && isStructFieldVendor()) {
                    // Multiple segments: use resolveColumnPath for deep field access
                    // e.g., remainingNames = ["customer", "address", "city"]
                    // -> base column "customer", fieldPath ["address", "city"]
                    columnSource = namespace.resolveColumnPath(match.remainingNames);

                    if (columnSource == null) {
                        // Base column not found
                        String baseColName = match.remainingNames.get(0);
                        return ResolutionResult.notFound(baseColName,
                            "Column '" + baseColName + "' not found in " + namespace.getDisplayName());
                    }

                    if (DEBUG_RESOLUTION) {
                        System.out.println("[DEBUG-RESOLVE]   Deep path resolved: " +
                            match.remainingNames + " -> base=" + columnSource.getExposedName() +
                            ", fieldPath=" + (columnSource.hasFieldPath() ? columnSource.getFieldPath() : "none"));
                    }
                } else {
                    // Single segment: regular column resolution
                    String remainingColName = match.remainingNames.get(match.remainingNames.size() - 1);
                    columnSource = namespace.resolveColumn(remainingColName);

                    if (columnSource == null) {
                        // Column not found in the namespace
                        return ResolutionResult.notFound(remainingColName,
                            "Column '" + remainingColName + "' not found in " + namespace.getDisplayName());
                    }
                }
            } else {
                // Name resolved to a table/namespace directly with no remaining parts
                // This happens when the column name matches the table alias exactly.
                //
                // For UNNEST tables (e.g., "UNNEST(arr) AS x"), the alias "x" is ALSO
                // the implicit column name. When user writes "x" as a column reference,
                // the scope resolution matches the table alias "x", leaving remainingNames empty.
                // We should still try to resolve "x" as a column in the namespace.
                //
                // Example: SELECT x FROM UNNEST([1,2,3]) AS x
                // Here "x" in SELECT refers to the implicit column, not the table.
                if (namespace instanceof UnnestNamespace) {
                    // For UNNEST, try to resolve the matched name as a column
                    columnSource = namespace.resolveColumn(columnName);
                    if (columnSource != null) {
                        if (DEBUG_RESOLUTION) {
                            System.out.println("[DEBUG-RESOLVE]   UNNEST implicit column resolved: " +
                                columnName + " in " + namespace.getDisplayName());
                        }
                        return ResolutionResult.exactMatch(columnSource);
                    }
                }

                // For other namespaces or if column not found, this is an error
                return ResolutionResult.notFound(columnName,
                    "Name '" + columnName + "' resolves to a table, not a column");
            }

            return ResolutionResult.exactMatch(columnSource);
        }

        // Multiple unique namespaces - truly ambiguous
        List<ColumnSource> candidates = new ArrayList<>();

        for (ResolvedImpl.Match match : uniqueMatches) {
            INamespace namespace = match.namespace;

            // Resolve column in this namespace
            // Phase B3: Support multi-segment paths
            ColumnSource columnSource;
            if (!match.remainingNames.isEmpty()) {
                if (match.remainingNames.size() > 1 && isStructFieldVendor()) {
                    // Multi-segment: use resolveColumnPath
                    columnSource = namespace.resolveColumnPath(match.remainingNames);
                } else {
                    // Single segment: use resolveColumn
                    String remainingColName = match.remainingNames.get(match.remainingNames.size() - 1);
                    columnSource = namespace.resolveColumn(remainingColName);
                }
            } else {
                columnSource = namespace.resolveColumn(columnName);
            }

            if (columnSource != null) {
                candidates.add(columnSource);
            }
        }

        if (candidates.isEmpty()) {
            // Resolved to tables, but none have the column
            return ResolutionResult.notFound(columnName,
                "Column '" + columnName + "' not found in any of " + resolved.getCount() + " tables");
        }

        if (candidates.size() == 1) {
            // Only one table actually has the column
            return ResolutionResult.exactMatch(candidates.get(0));
        }

        // Sort candidates by their table's position in the SQL text (FROM clause order)
        // This ensures consistent ordering for both ambiguous results and GUESS_COLUMN_STRATEGY
        sortCandidatesByTablePosition(candidates);

        // Check if all candidates are "inferred" (from tables without DDL metadata)
        // If so, return AMBIGUOUS regardless of GUESS_COLUMN_STRATEGY
        // Only apply GUESS_COLUMN_STRATEGY when we have definite knowledge about the columns
        //
        // The determination uses configurable thresholds:
        // - minDefiniteConfidence: minimum confidence to be considered "definite" (default 0.9)
        // - allowGuessWhenAllInferred: if true, allow guessing even when all candidates are inferred
        boolean hasDefiniteCandidate = false;
        double highestConfidence = 0.0;
        double minDefiniteConf = config != null ? config.getMinDefiniteConfidence() : 0.9;

        for (ColumnSource candidate : candidates) {
            // A candidate is "definite" if it has high confidence and is not just inferred from usage
            String evidence = candidate.getEvidence();
            double confidence = candidate.getConfidence();

            // Track highest confidence among candidates
            if (confidence > highestConfidence) {
                highestConfidence = confidence;
            }

            // Use structured evidence if available, otherwise check legacy evidence
            boolean isInferred;
            if (candidate.getEvidenceDetail() != null) {
                // Use the structured evidence's own determination
                isInferred = !candidate.getEvidenceDetail().isHighConfidence() ||
                             candidate.getEvidenceDetail().isInferred();
            } else {
                // Fallback to legacy logic
                // "inferred_from_usage" means table without DDL metadata - not definite
                // Low confidence (< minDefiniteConf) means we're guessing - not definite
                // Null evidence with high confidence is also considered inferred
                isInferred = (evidence == null || evidence.equals("inferred_from_usage")) ||
                             confidence < minDefiniteConf;
            }

            if (!isInferred) {
                hasDefiniteCandidate = true;
                break;
            }
        }

        // Check if we should allow guessing when all candidates are inferred
        boolean allowGuessInferred = config != null && config.isAllowGuessWhenAllInferred();

        if (!hasDefiniteCandidate && !allowGuessInferred) {
            // All candidates are from tables without DDL metadata (all inferred)
            // Don't guess - return as ambiguous so formatter can handle appropriately
            if (DEBUG_RESOLUTION) {
                System.out.println("[DEBUG-RESOLVE]   AMBIGUOUS (all inferred): " + columnName + " with " + candidates.size() + " candidates");
                for (ColumnSource c : candidates) {
                    System.out.println("[DEBUG-RESOLVE]     - " + (c.getSourceNamespace() != null ? c.getSourceNamespace().getDisplayName() : "null") +
                        " evidence=" + c.getEvidence() + " confidence=" + c.getConfidence());
                }
            }
            AmbiguousColumnSource ambiguous = new AmbiguousColumnSource(columnName, candidates);
            return ResolutionResult.ambiguous(ambiguous);
        }

        // Additional check: even if we have definite candidates or allow inferred guessing,
        // require at least one candidate to meet the minConfidenceToGuess threshold
        double minConfToGuess = config != null ? config.getMinConfidenceToGuess() : 0.95;
        if (highestConfidence < minConfToGuess && !allowGuessInferred) {
            // No candidate has sufficient confidence for guessing
            if (DEBUG_RESOLUTION) {
                System.out.println("[DEBUG-RESOLVE]   AMBIGUOUS (confidence too low): " + columnName +
                    " highest=" + highestConfidence + " required=" + minConfToGuess);
            }
            AmbiguousColumnSource ambiguous = new AmbiguousColumnSource(columnName, candidates);
            return ResolutionResult.ambiguous(ambiguous);
        }

        // Multiple tables have the column - apply GUESS_COLUMN_STRATEGY
        // Candidates are already sorted by table position (done earlier)
        int strategy = getGuessColumnStrategy();

        if (strategy == TSQLResolverConfig.GUESS_COLUMN_STRATEGY_NEAREST) {
            // Pick the first candidate (nearest table in FROM clause order)
            return ResolutionResult.exactMatch(candidates.get(0));
        } else if (strategy == TSQLResolverConfig.GUESS_COLUMN_STRATEGY_FARTHEST) {
            // Pick the last candidate (farthest table in FROM clause order)
            return ResolutionResult.exactMatch(candidates.get(candidates.size() - 1));
        }

        // GUESS_COLUMN_STRATEGY_NOT_PICKUP: leave as ambiguous
        if (DEBUG_RESOLUTION) {
            System.out.println("[DEBUG-RESOLVE]   AMBIGUOUS (NOT_PICKUP strategy): " + columnName + " with " + candidates.size() + " candidates");
        }
        AmbiguousColumnSource ambiguous = new AmbiguousColumnSource(columnName, candidates);
        return ResolutionResult.ambiguous(ambiguous);
    }

    /**
     * Deduplicate matches by namespace identity.
     * The same namespace can be found through different scope paths (e.g., CTE scope and SELECT scope)
     * but it's still the same source - not truly ambiguous.
     *
     * @param matches All matches from scope resolution
     * @return List of unique matches by namespace identity
     */
    private List<ResolvedImpl.Match> deduplicateMatchesByNamespace(List<ResolvedImpl.Match> matches) {
        if (matches == null || matches.size() <= 1) {
            return matches;
        }

        // Use identity-based set to track unique namespaces
        java.util.IdentityHashMap<INamespace, ResolvedImpl.Match> uniqueByNamespace =
            new java.util.IdentityHashMap<>();

        for (ResolvedImpl.Match match : matches) {
            if (match.namespace != null && !uniqueByNamespace.containsKey(match.namespace)) {
                uniqueByNamespace.put(match.namespace, match);
            }
        }

        return new ArrayList<>(uniqueByNamespace.values());
    }

    /**
     * Sort candidates by their source table's position in the SQL text.
     * This ensures that when GUESS_COLUMN_STRATEGY_NEAREST or FARTHEST is applied,
     * the candidates are ordered according to their actual position in the FROM clause.
     *
     * Uses the table's start token (lineNo, columnNo) to determine position.
     *
     * @param candidates List of ColumnSource candidates to sort in place
     */
    private void sortCandidatesByTablePosition(List<ColumnSource> candidates) {
        if (candidates == null || candidates.size() <= 1) {
            return;
        }

        candidates.sort(new Comparator<ColumnSource>() {
            @Override
            public int compare(ColumnSource c1, ColumnSource c2) {
                TTable t1 = c1.getFinalTable();
                TTable t2 = c2.getFinalTable();

                // If either table is null, maintain relative order
                if (t1 == null && t2 == null) return 0;
                if (t1 == null) return 1;  // null tables go to the end
                if (t2 == null) return -1;

                TSourceToken token1 = t1.getStartToken();
                TSourceToken token2 = t2.getStartToken();

                // If either token is null, maintain relative order
                if (token1 == null && token2 == null) return 0;
                if (token1 == null) return 1;
                if (token2 == null) return -1;

                // Compare by line number first
                int lineCmp = Long.compare(token1.lineNo, token2.lineNo);
                if (lineCmp != 0) {
                    return lineCmp;
                }

                // If same line, compare by column number
                return Long.compare(token1.columnNo, token2.columnNo);
            }
        });
    }

    /**
     * Update TObjectName with the resolution result.
     * Also registers with ResolutionContext.
     *
     * For ambiguous columns (multiple candidate tables), this also populates
     * TObjectName.candidateTables with all possible source tables.
     *
     * IMPORTANT: When resolution fails (notFound) and the column's sourceTable
     * was set during Phase 1 to an UNNEST table, we clear the sourceTable.
     * This is because UNNEST tables have a fixed set of columns (implicit column,
     * offset, struct fields) and should NOT have arbitrary columns inferred.
     * Clearing sourceTable allows the formatter to treat these as "missed" columns.
     */
    private void updateObjectNameWithResult(TObjectName objName, ResolutionResult result) {
        // Update TObjectName with resolution result
        objName.setResolution(result);

        // For notFound results, check if Phase 1 incorrectly linked to UNNEST table
        // UNNEST tables have a fixed column set - don't allow inferred columns
        if (!result.isExactMatch() && !result.isAmbiguous()) {
            TTable currentSourceTable = objName.getSourceTable();
            if (currentSourceTable != null &&
                currentSourceTable.getTableType() == ETableSource.unnest) {
                // Clear the incorrectly set sourceTable from Phase 1
                // This column wasn't found in the UNNEST namespace, so it shouldn't
                // be attributed to the UNNEST table
                objName.setSourceTable(null);
                if (DEBUG_RESOLUTION) {
                    System.out.println("[DEBUG-RESOLVE]   Cleared incorrect UNNEST sourceTable for: " +
                        objName + " (not found in UNNEST namespace)");
                }
            }
        }

        // For ambiguous results, populate candidateTables with all candidate tables
        // IMPORTANT: Clear existing candidateTables first, as Phase 1 (linkColumnToTable)
        // may have added candidates from incorrect scopes (e.g., MERGE target table for
        // columns inside USING subquery). Phase 2 (NameResolver) has proper scope awareness
        // and produces the authoritative candidate list.
        if (result.isAmbiguous() && result.getAmbiguousSource() != null) {
            AmbiguousColumnSource ambiguous = result.getAmbiguousSource();
            // Clear Phase 1 candidates before adding Phase 2's scope-aware candidates
            objName.getCandidateTables().clear();
            for (ColumnSource candidate : ambiguous.getCandidates()) {
                gudusoft.gsqlparser.nodes.TTable candidateTable = candidate.getFinalTable();
                if (candidateTable != null) {
                    objName.getCandidateTables().addTable(candidateTable);
                }
            }
        }

        // Register with context for global querying
        context.registerResolution(objName, result);
    }

    /**
     * Resolve a column within a specific namespace (for direct lookups).
     */
    public ResolutionResult resolveInNamespace(String columnName, INamespace namespace) {
        if (columnName == null || namespace == null) {
            return ResolutionResult.notFound("<null>");
        }

        ColumnSource source = namespace.resolveColumn(columnName);
        if (source != null) {
            return ResolutionResult.exactMatch(source);
        }

        return ResolutionResult.notFound(columnName);
    }

    /**
     * Find all namespaces that contain a given column.
     * Used for implementing full candidate collection in ambiguous scenarios.
     */
    public List<INamespace> findNamespacesWithColumn(String columnName, IScope scope) {
        List<INamespace> result = new ArrayList<>();

        for (INamespace ns : scope.getVisibleNamespaces()) {
            if (ns.hasColumn(columnName) == ColumnLevel.EXISTS) {
                result.add(ns);
            }
        }

        return result;
    }

    public INameMatcher getNameMatcher() {
        return nameMatcher;
    }

    public ResolutionContext getContext() {
        return context;
    }
}