package gudusoft.gsqlparser.resolver2.binding;

import gudusoft.gsqlparser.EExpressionType;
import gudusoft.gsqlparser.EJoinType;
import gudusoft.gsqlparser.ESqlClause;
import gudusoft.gsqlparser.ETableSource;
import gudusoft.gsqlparser.TCustomSqlStatement;
import gudusoft.gsqlparser.TStatementList;
import gudusoft.gsqlparser.nodes.TJoinExpr;
import gudusoft.gsqlparser.nodes.TObjectName;
import gudusoft.gsqlparser.nodes.TParseTreeNode;
import gudusoft.gsqlparser.nodes.TParseTreeVisitor;
import gudusoft.gsqlparser.nodes.TResultColumn;
import gudusoft.gsqlparser.nodes.TResultColumnList;
import gudusoft.gsqlparser.nodes.TTable;
import gudusoft.gsqlparser.resolver2.ColumnLevel;
import gudusoft.gsqlparser.resolver2.ScopeBuildResult;
import gudusoft.gsqlparser.resolver2.TSQLResolver2;
import gudusoft.gsqlparser.resolver2.TSQLResolverConfig;
import gudusoft.gsqlparser.resolver2.model.AmbiguousColumnSource;
import gudusoft.gsqlparser.resolver2.model.ColumnSource;
import gudusoft.gsqlparser.resolver2.model.ResolutionContext;
import gudusoft.gsqlparser.resolver2.model.ResolutionResult;
import gudusoft.gsqlparser.resolver2.namespace.CTENamespace;
import gudusoft.gsqlparser.resolver2.namespace.INamespace;
import gudusoft.gsqlparser.resolver2.namespace.MetadataState;
import gudusoft.gsqlparser.resolver2.namespace.SubqueryNamespace;
import gudusoft.gsqlparser.resolver2.namespace.UnionNamespace;
import gudusoft.gsqlparser.resolver2.scope.IScope;
import gudusoft.gsqlparser.stmt.TSelectSqlStatement;

import java.util.ArrayDeque;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Deque;
import java.util.HashSet;
import java.util.IdentityHashMap;
import java.util.List;
import java.util.Locale;
import java.util.Set;

/**
 * Slice S5: orchestrator that walks the recorded binding trace
 * ({@link ResolutionContext#getColumnResolutionResult}) once after
 * iterative resolution converges and converts {@link ResolutionResult}s
 * into a public {@link BindingResult}.
 *
 * <p>The class is {@code public} because {@link TSQLResolver2} (in the
 * parent {@code resolver2} package) instantiates it directly. The static
 * invocation counter accessors remain package-private — they are
 * test-only.</p>
 *
 * <p>Plan §5.6.1 critical clarification — the post-pass MUST NOT perform
 * name binding. It only reads resolver2's final state and decides which
 * stable, public {@link BindingDiagnostic} to surface. In particular it
 * never re-invokes {@code NameResolver.resolve(...)} or mutates AST
 * state.</p>
 *
 * <p>This slice ships the first three diagnostic codes ({@code
 * UNKNOWN_COLUMN}, {@code AMBIGUOUS_COLUMN}, {@code
 * UNBOUND_COLUMN_REFERENCE}). Strict-mode codes ({@code UNKNOWN_TABLE},
 * {@code UNKNOWN_ALIAS}, {@code CATALOG_METADATA_UNAVAILABLE}) and clause
 * attribution land in S6; deeper codes (CTE / subquery output, set-op
 * arity, USING / NATURAL, DML) land in S8–S13.</p>
 */
public final class BindingDiagnosticPostPass {

    /**
     * Static invocation counter used by {@code BindingPostPassInvocationTest}
     * to confirm the post-pass runs at most once per resolver call regardless
     * of how many iterations the resolver loop executed (plan §13 risk row).
     * Test-only — not part of the public surface.
     */
    private static volatile int invocationCount = 0;

    static int getInvocationCount() {
        return invocationCount;
    }

    static void resetInvocationCounter() {
        invocationCount = 0;
    }

    private final TSQLResolver2 resolver;
    private final TSQLResolverConfig config;
    private BindingClauseMapper mapper = BindingClauseMapper.empty();

    public BindingDiagnosticPostPass(TSQLResolver2 resolver, TSQLResolverConfig config) {
        this.resolver = resolver;
        this.config = config;
    }

    /**
     * Run the post-pass exactly once and return the populated
     * {@link BindingResult}.
     *
     * <p>Always non-null. Returns {@link BindingResult#empty()} when the
     * inputs do not yet satisfy the minimum invariants (no scope build
     * result, no resolution context, trace not enabled) — never throws.</p>
     */
    public BindingResult run() {
        invocationCount++;

        ScopeBuildResult sbr = resolver != null ? resolver.getScopeBuildResult() : null;
        ResolutionContext ctx = resolver != null ? resolver.getContext() : null;
        if (sbr == null || ctx == null || !ctx.isBindingTraceEnabled()) {
            return BindingResult.empty();
        }

        mapper = BindingClauseMapper.of(resolver.getStatements());

        boolean includeSuccessful = config != null
            && config.isBindingIncludeSuccessfulReferences();

        List<BindingDiagnostic> diagnostics = new ArrayList<BindingDiagnostic>();
        List<BindingReference> references = new ArrayList<BindingReference>();

        collectSetOperationDiagnostics(diagnostics);
        collectJoinDiagnostics(sbr, diagnostics);

        List<TObjectName> refs = sbr.getAllColumnReferences();
        if (refs == null || refs.isEmpty()) {
            return diagnostics.isEmpty() ? BindingResult.empty()
                : BindingResult.of(diagnostics, references);
        }

        // Identity-keyed dedup so re-emitted diagnostics for the same
        // TObjectName do not stack (e.g., two passes touching the same
        // reference). The trace itself is identity-keyed for the same
        // reason — see ResolutionContext#columnResolutionResults.
        IdentityHashMap<TObjectName, BindingDiagnostic> seenDiagnostics =
            new IdentityHashMap<TObjectName, BindingDiagnostic>();
        IdentityHashMap<TObjectName, BindingReference> seenReferences =
            new IdentityHashMap<TObjectName, BindingReference>();

        for (TObjectName ref : refs) {
            if (ref == null) continue;

            BindingSkipReason skip = ctx.getColumnSkipReason(ref);
            if (skip != null) {
                continue;
            }

            // S13: DML target/definition columns are NOT user references — they
            // declare WHERE the write goes, not what it reads. Skip silently and
            // tag the trace so consumers reading ResolutionContext directly can
            // see intent (plan §10.5 / §13 failure-handling row).
            if (isDmlTargetColumn(ref)) {
                ctx.recordColumnSkipReason(ref, BindingSkipReason.TARGET_COLUMN);
                continue;
            }

            ResolutionResult result = ctx.getColumnResolutionResult(ref);
            if (result == null) {
                // No recorded resolution. This is normal for synthetic clones
                // (already filtered above via SYNTHETIC_STAR_CLONE) and for
                // references the resolver intentionally skipped. The S3
                // coverage gate ensures every other reference is recorded;
                // we trust that gate here.
                continue;
            }

            if (!seenDiagnostics.containsKey(ref)) {
                BindingDiagnostic setOpOutputDiagnostic = buildSetOperationOutputDiagnostic(ref, sbr);
                if (setOpOutputDiagnostic != null) {
                    seenDiagnostics.put(ref, setOpOutputDiagnostic);
                    diagnostics.add(setOpOutputDiagnostic);

                    if (includeSuccessful && !seenReferences.containsKey(ref)) {
                        BindingReference br = buildReference(ref, sbr, /* bound */ false);
                        if (br != null) {
                            seenReferences.put(ref, br);
                            references.add(br);
                        }
                    }
                    continue;
                }
                BindingDiagnostic cteOutputDiagnostic = buildCteOutputDiagnostic(ref, sbr);
                if (cteOutputDiagnostic != null) {
                    seenDiagnostics.put(ref, cteOutputDiagnostic);
                    diagnostics.add(cteOutputDiagnostic);

                    if (includeSuccessful && !seenReferences.containsKey(ref)) {
                        BindingReference br = buildReference(ref, sbr, /* bound */ false);
                        if (br != null) {
                            seenReferences.put(ref, br);
                            references.add(br);
                        }
                    }
                    continue;
                }
                BindingDiagnostic subqueryOutputDiagnostic = buildSubqueryOutputDiagnostic(ref, sbr);
                if (subqueryOutputDiagnostic != null) {
                    seenDiagnostics.put(ref, subqueryOutputDiagnostic);
                    diagnostics.add(subqueryOutputDiagnostic);

                    if (includeSuccessful && !seenReferences.containsKey(ref)) {
                        BindingReference br = buildReference(ref, sbr, /* bound */ false);
                        if (br != null) {
                            seenReferences.put(ref, br);
                            references.add(br);
                        }
                    }
                    continue;
                }
            }

            if (result.isExactMatch()) {
                // S16c (plan §5.6.2): EXACT_MATCH means resolver2 picked one
                // SQL source for this reference, but it does NOT mean catalog
                // metadata has confirmed the column actually lives on the
                // selected source. Read the resolver2-selected namespace from
                // the recorded ResolutionResult and ask the catalog only.
                // We never call NameResolver.resolve(...) again, never search
                // the SQL scope, and never mutate AST state.
                if (!seenDiagnostics.containsKey(ref)) {
                    BindingDiagnostic exactMatchDiag =
                        verifyExactMatchAgainstCatalog(ref, result, sbr);
                    if (exactMatchDiag != null) {
                        seenDiagnostics.put(ref, exactMatchDiag);
                        diagnostics.add(exactMatchDiag);
                        if (includeSuccessful && !seenReferences.containsKey(ref)) {
                            BindingReference br = buildReference(ref, sbr, /* bound */ false);
                            if (br != null) {
                                seenReferences.put(ref, br);
                                references.add(br);
                            }
                        }
                        continue;
                    }
                }
                // In strict mode, even an exact-match resolution may be
                // suspect when the backing namespace has unreliable metadata
                // (e.g. resolver inferred a 0.80-confidence match for a table
                // that is NOT_FOUND_IN_CATALOG or METADATA_UNAVAILABLE).
                if (config != null && config.isBindingStrictCatalogValidation()
                        && !seenDiagnostics.containsKey(ref)) {
                    BindingDiagnostic strictDiag = buildStrictModeNamespaceDiagnostic(ref, sbr);
                    if (strictDiag != null) {
                        seenDiagnostics.put(ref, strictDiag);
                        diagnostics.add(strictDiag);
                        continue;
                    }
                }
                if (includeSuccessful && !seenReferences.containsKey(ref)) {
                    BindingReference br = buildReference(ref, sbr, /* bound */ true);
                    if (br != null) {
                        seenReferences.put(ref, br);
                        references.add(br);
                    }
                }
                continue;
            }

            if (seenDiagnostics.containsKey(ref)) {
                continue;
            }

            BindingDiagnostic diagnostic = null;
            if (result.isAmbiguous()) {
                diagnostic = buildAmbiguousDiagnostic(ref, result, sbr);
            } else if (result.isNotFound()) {
                diagnostic = buildNotFoundDiagnostic(ref, ctx, sbr);
            }

            if (diagnostic != null) {
                seenDiagnostics.put(ref, diagnostic);
                diagnostics.add(diagnostic);

                if (includeSuccessful && !seenReferences.containsKey(ref)) {
                    BindingReference br = buildReference(ref, sbr, /* bound */ false);
                    if (br != null) {
                        seenReferences.put(ref, br);
                        references.add(br);
                    }
                }
            }
        }

        if (diagnostics.isEmpty() && references.isEmpty()) {
            return BindingResult.empty();
        }
        return BindingResult.of(diagnostics, references);
    }

    // ===== AMBIGUOUS =====

    private BindingDiagnostic buildAmbiguousDiagnostic(TObjectName ref,
                                                       ResolutionResult result,
                                                       ScopeBuildResult sbr) {
        AmbiguousColumnSource ambiguous = result.getAmbiguousSource();
        List<String> candidates = candidatesOf(ambiguous);
        String text = ref.toString();

        BindingDiagnosticSeverity severity = severityFor(BindingDiagnosticCode.AMBIGUOUS_COLUMN);
        BindingDiagnostic.Builder b = BindingDiagnosticBuilder
            .forCode(BindingDiagnosticCode.AMBIGUOUS_COLUMN)
            .severity(severity)
            .message("Column " + text + " is ambiguous between "
                + (candidates.isEmpty() ? "multiple sources" : candidates) + ".")
            .objectNameText(text)
            .candidates(candidates)
            .site(siteFor(ref))
            .statement(statementFor(ref, sbr));
        return b.build();
    }

    private static List<String> candidatesOf(AmbiguousColumnSource ambiguous) {
        if (ambiguous == null) {
            return Collections.emptyList();
        }
        List<String> out = new ArrayList<String>();
        for (ColumnSource cs : ambiguous.getCandidates()) {
            if (cs == null) continue;
            String label = describeCandidate(cs, ambiguous.getColumnName());
            if (label != null) {
                out.add(label);
            }
        }
        return out;
    }

    private static String describeCandidate(ColumnSource cs, String columnName) {
        if (cs == null) return null;
        String tableLabel = null;
        if (cs.getFinalTable() != null && cs.getFinalTable().getName() != null) {
            tableLabel = cs.getFinalTable().getName().toString();
        } else if (cs.getSourceNamespace() != null) {
            tableLabel = cs.getSourceNamespace().getDisplayName();
        }
        String exposed = cs.getExposedName();
        String name = (exposed != null && !exposed.isEmpty()) ? exposed : columnName;
        if (tableLabel == null || tableLabel.isEmpty()) {
            return name;
        }
        return tableLabel + "." + name;
    }

    // ===== SET operations =====

    private void collectSetOperationDiagnostics(final List<BindingDiagnostic> diagnostics) {
        if (diagnostics == null) return;
        TStatementList statements = resolver != null ? resolver.getStatements() : null;
        if (statements == null) return;

        final Set<TSelectSqlStatement> covered =
            Collections.newSetFromMap(new IdentityHashMap<TSelectSqlStatement, Boolean>());
        for (int i = 0; i < statements.size(); i++) {
            TCustomSqlStatement stmt = statements.get(i);
            if (stmt == null) continue;
            try {
                stmt.acceptChildren(new TParseTreeVisitor() {
                    @Override
                    public void preVisit(TSelectSqlStatement node) {
                        collectSetOperationDiagnostic(node, diagnostics, covered);
                    }
                });
            } catch (Throwable ignore) {
                // Defensive: set-operation diagnostics must never fail the post-pass.
            }
        }
    }

    private void collectSetOperationDiagnostic(TSelectSqlStatement stmt,
                                               List<BindingDiagnostic> diagnostics,
                                               Set<TSelectSqlStatement> covered) {
        if (stmt == null || !stmt.isCombinedQuery() || covered.contains(stmt)) {
            return;
        }

        SetOperationShape shape = analyzeSetOperationShape(stmt);
        if (!shape.authoritative || !shape.hasMismatch()) {
            return;
        }

        diagnostics.add(buildSetOperationArityDiagnostic(stmt, shape));
        covered.addAll(collectCombinedSetOperationNodes(stmt));
    }

    private SetOperationShape analyzeSetOperationShape(TSelectSqlStatement stmt) {
        List<TSelectSqlStatement> branches = flattenSetOperationBranches(stmt);
        List<Integer> counts = new ArrayList<Integer>();
        if (branches.size() < 2) {
            return SetOperationShape.nonAuthoritative(counts);
        }

        for (TSelectSqlStatement branch : branches) {
            if (branch == null || branch.isCombinedQuery()) {
                return SetOperationShape.nonAuthoritative(counts);
            }
            TResultColumnList selectList = branch.getResultColumnList();
            if (selectList == null || containsStarResultColumn(selectList)) {
                return SetOperationShape.nonAuthoritative(counts);
            }
            counts.add(Integer.valueOf(selectList.size()));
        }

        return SetOperationShape.authoritative(counts);
    }

    private static List<TSelectSqlStatement> flattenSetOperationBranches(TSelectSqlStatement stmt) {
        List<TSelectSqlStatement> branches = new ArrayList<TSelectSqlStatement>();
        if (stmt == null) {
            return branches;
        }

        Deque<TSelectSqlStatement> stack = new ArrayDeque<TSelectSqlStatement>();
        stack.push(stmt);
        while (!stack.isEmpty()) {
            TSelectSqlStatement current = stack.pop();
            if (current == null) continue;
            if (current.isCombinedQuery()) {
                if (current.getRightStmt() != null) {
                    stack.push(current.getRightStmt());
                }
                if (current.getLeftStmt() != null) {
                    stack.push(current.getLeftStmt());
                }
            } else {
                branches.add(current);
            }
        }
        return branches;
    }

    private static Set<TSelectSqlStatement> collectCombinedSetOperationNodes(TSelectSqlStatement stmt) {
        Set<TSelectSqlStatement> nodes =
            Collections.newSetFromMap(new IdentityHashMap<TSelectSqlStatement, Boolean>());
        if (stmt == null) {
            return nodes;
        }

        Deque<TSelectSqlStatement> stack = new ArrayDeque<TSelectSqlStatement>();
        stack.push(stmt);
        while (!stack.isEmpty()) {
            TSelectSqlStatement current = stack.pop();
            if (current == null || !current.isCombinedQuery() || !nodes.add(current)) {
                continue;
            }
            if (current.getRightStmt() != null) {
                stack.push(current.getRightStmt());
            }
            if (current.getLeftStmt() != null) {
                stack.push(current.getLeftStmt());
            }
        }
        return nodes;
    }

    private static boolean containsStarResultColumn(TResultColumnList selectList) {
        if (selectList == null) {
            return true;
        }
        for (int i = 0; i < selectList.size(); i++) {
            TResultColumn resultColumn = selectList.getResultColumn(i);
            if (isStarResultColumn(resultColumn)) {
                return true;
            }
        }
        return false;
    }

    private static boolean isStarResultColumn(TResultColumn resultColumn) {
        if (resultColumn == null) {
            return false;
        }

        // Star modifiers such as BigQuery SELECT * EXCEPT/REPLACE still have
        // non-authoritative expanded output for S11 arity and missing-output
        // diagnostics. Treat those as star-derived even when the full result
        // column text no longer equals "*" or "t.*".
        if (resultColumn.getExceptColumnList() != null
                || (resultColumn.getReplaceExprAsIdentifiers() != null
                    && !resultColumn.getReplaceExprAsIdentifiers().isEmpty())
                || (resultColumn.getExprAsIdentifiers() != null
                    && !resultColumn.getExprAsIdentifiers().isEmpty())) {
            return true;
        }

        if (resultColumn.getExpr() != null
                && resultColumn.getExpr().getExpressionType() == EExpressionType.simple_object_name_t
                && resultColumn.getExpr().getObjectOperand() != null) {
            String starText = resultColumn.getExpr().getObjectOperand().toString();
            if (starText != null) {
                starText = starText.trim();
                if ("*".equals(starText) || starText.endsWith(".*")) {
                    return true;
                }
            }
        }

        String text = resultColumn.toString();
        if (text == null) {
            return false;
        }
        text = text.trim();
        return "*".equals(text) || text.endsWith(".*");
    }

    private BindingDiagnostic buildSetOperationArityDiagnostic(TSelectSqlStatement stmt,
                                                               SetOperationShape shape) {
        String label = setOperationLabel(stmt);
        int expected = shape.counts.get(0).intValue();
        int mismatchIndex = shape.firstMismatchIndex();
        int actual = shape.counts.get(mismatchIndex).intValue();

        return BindingDiagnosticBuilder
            .forCode(BindingDiagnosticCode.SET_OPERATION_ARITY_MISMATCH)
            .severity(severityFor(BindingDiagnosticCode.SET_OPERATION_ARITY_MISMATCH))
            .message("Set operation " + label
                + " has branch column count mismatch: branch 1 has "
                + expected + " column(s), branch " + (mismatchIndex + 1)
                + " has " + actual + " column(s).")
            .objectNameText(label)
            .candidates(branchCountCandidates(shape.counts))
            .site(setOperationSite(label))
            .statement(stmt)
            .build();
    }

    private static List<String> branchCountCandidates(List<Integer> counts) {
        if (counts == null || counts.isEmpty()) {
            return Collections.emptyList();
        }
        List<String> out = new ArrayList<String>();
        for (int i = 0; i < counts.size(); i++) {
            out.add("branch " + (i + 1) + ": " + counts.get(i));
        }
        return out;
    }

    private static BindingReferenceSite setOperationSite(String label) {
        return BindingReferenceSite.builder()
            .clause(BindingClause.OTHER)
            .referenceText(label == null || label.isEmpty() ? "SET OPERATION" : label)
            .build();
    }

    private static String setOperationLabel(TSelectSqlStatement stmt) {
        if (stmt == null || stmt.getSetOperatorType() == null) {
            return "SET OPERATION";
        }
        String type = stmt.getSetOperatorType().name();
        if (type == null || "none".equals(type)) {
            return "SET OPERATION";
        }
        String label = type.toUpperCase(Locale.ROOT);
        if (stmt.isAll()) {
            label += " ALL";
        } else if (stmt.isSetOpDistinct()) {
            label += " DISTINCT";
        }
        return label;
    }

    private static final class SetOperationShape {
        private final boolean authoritative;
        private final List<Integer> counts;

        private SetOperationShape(boolean authoritative, List<Integer> counts) {
            this.authoritative = authoritative;
            this.counts = counts != null
                ? Collections.unmodifiableList(new ArrayList<Integer>(counts))
                : Collections.<Integer>emptyList();
        }

        static SetOperationShape authoritative(List<Integer> counts) {
            return new SetOperationShape(true, counts);
        }

        static SetOperationShape nonAuthoritative(List<Integer> counts) {
            return new SetOperationShape(false, counts);
        }

        boolean hasMismatch() {
            return firstMismatchIndex() >= 0;
        }

        int firstMismatchIndex() {
            if (!authoritative || counts.size() < 2) {
                return -1;
            }
            int expected = counts.get(0).intValue();
            for (int i = 1; i < counts.size(); i++) {
                if (counts.get(i).intValue() != expected) {
                    return i;
                }
            }
            return -1;
        }
    }

    // ===== JOIN diagnostics =====

    private void collectJoinDiagnostics(ScopeBuildResult sbr,
                                        List<BindingDiagnostic> diagnostics) {
        if (sbr == null || diagnostics == null) return;
        collectUsingColumnDiagnostics(sbr, diagnostics);
        collectNaturalJoinDiagnostics(diagnostics);
    }

    private void collectUsingColumnDiagnostics(final ScopeBuildResult sbr,
                                               final List<BindingDiagnostic> diagnostics) {
        TStatementList statements = resolver != null ? resolver.getStatements() : null;
        if (statements == null) return;
        final Set<TJoinExpr> seen = Collections.newSetFromMap(new IdentityHashMap<TJoinExpr, Boolean>());
        for (int i = 0; i < statements.size(); i++) {
            TCustomSqlStatement stmt = statements.get(i);
            if (stmt == null) continue;
            try {
                stmt.acceptChildren(new TParseTreeVisitor() {
                    @Override
                    public void preVisit(TJoinExpr node) {
                        if (node == null || !seen.add(node)
                                || node.getUsingColumns() == null
                                || node.getUsingColumns().size() == 0) {
                            return;
                        }
                        Set<TTable> leftTables = collectInputTables(node.getLeftTable());
                        Set<TTable> rightTables = collectInputTables(node.getRightTable());
                        if (leftTables.isEmpty() || rightTables.isEmpty()) return;
                        for (int c = 0; c < node.getUsingColumns().size(); c++) {
                            TObjectName col = node.getUsingColumns().getObjectName(c);
                            String name = usingColumnName(col);
                            if (name == null) continue;
                            Boolean leftPresent = inputExposesColumn(sbr, leftTables, name);
                            Boolean rightPresent = inputExposesColumn(sbr, rightTables, name);
                            // Catalog-honesty rule: emit only when both JOIN
                            // inputs have authoritative metadata. For a
                            // composite left input, one table exposing the
                            // column is sufficient: SQL visibility is over the
                            // join input, not every physical table beneath it.
                            if (leftPresent == null || rightPresent == null) continue;
                            if (leftPresent.booleanValue() && rightPresent.booleanValue()) continue;
                            diagnostics.add(BindingDiagnosticBuilder
                                .forCode(BindingDiagnosticCode.USING_COLUMN_NOT_COMMON)
                                .severity(severityFor(BindingDiagnosticCode.USING_COLUMN_NOT_COMMON))
                                .message("USING column " + name
                                    + " is not common to both JOIN inputs.")
                                .objectNameText(col != null ? col.toString() : name)
                                .site(joinSite(col != null ? col.toString() : name))
                                .statement(null)
                                .build());
                        }
                    }
                });
            } catch (Throwable ignore) {
                // Defensive: join diagnostics must never fail the post-pass.
            }
        }
    }

    /**
     * @return TRUE when at least one table in the input authoritatively exposes
     * the column, FALSE when all authoritative tables lack it, null when the
     * input has only unavailable/ambiguous metadata or mixed unavailable misses.
     */
    private static Boolean inputExposesColumn(ScopeBuildResult sbr,
                                             Set<TTable> tables,
                                             String columnName) {
        boolean sawAuthoritativeAbsent = false;
        boolean sawUnavailable = false;
        for (TTable table : tables) {
            if (table == null) {
                sawUnavailable = true;
                continue;
            }
            INamespace ns = sbr.getNamespaceForTable(table);
            ColumnAuthority authority = BindingMetadataAuthority.lookup(ns, columnName);
            if (authority == ColumnAuthority.AUTHORITATIVE_PRESENT) return Boolean.TRUE;
            if (authority == ColumnAuthority.AUTHORITATIVE_ABSENT) {
                sawAuthoritativeAbsent = true;
            } else {
                sawUnavailable = true;
            }
        }
        if (sawUnavailable) return null;
        return sawAuthoritativeAbsent ? Boolean.FALSE : null;
    }

    private static Set<TTable> collectInputTables(TTable table) {
        Set<TTable> out = new HashSet<TTable>();
        collectInputTables(table, out);
        return out;
    }

    private static void collectInputTables(TTable table, Set<TTable> out) {
        if (table == null || out == null) return;
        if (table.getTableType() == ETableSource.join && table.getJoinExpr() != null) {
            TJoinExpr join = table.getJoinExpr();
            collectInputTables(join.getLeftTable(), out);
            collectInputTables(join.getRightTable(), out);
            return;
        }
        out.add(table);
    }

    private void collectNaturalJoinDiagnostics(final List<BindingDiagnostic> diagnostics) {
        if (config == null || !config.isBindingStrictCatalogValidation()) return;
        TStatementList statements = resolver != null ? resolver.getStatements() : null;
        if (statements == null) return;
        final Set<TJoinExpr> seen = Collections.newSetFromMap(new IdentityHashMap<TJoinExpr, Boolean>());
        for (int i = 0; i < statements.size(); i++) {
            TCustomSqlStatement stmt = statements.get(i);
            if (stmt == null) continue;
            try {
                stmt.acceptChildren(new TParseTreeVisitor() {
                    @Override
                    public void preVisit(TJoinExpr node) {
                        if (node == null || !seen.add(node) || !isNaturalJoin(node.getJointype())) return;
                        String text = node.toString();
                        diagnostics.add(BindingDiagnosticBuilder
                            .forCode(BindingDiagnosticCode.UNSUPPORTED_BINDING_SCOPE)
                            .severity(severityFor(BindingDiagnosticCode.UNSUPPORTED_BINDING_SCOPE))
                            .message("NATURAL JOIN binding semantics are not yet modeled.")
                            .objectNameText(text != null && !text.isEmpty() ? text : "NATURAL JOIN")
                            .site(joinSite("NATURAL JOIN"))
                            .statement(null)
                            .build());
                    }
                });
            } catch (Throwable ignore) {
                // Defensive: join diagnostics must never fail the post-pass.
            }
        }
    }

    private static boolean isNaturalJoin(EJoinType type) {
        return type == EJoinType.natural
            || type == EJoinType.natural_inner
            || type == EJoinType.natural_left
            || type == EJoinType.natural_right
            || type == EJoinType.natural_full
            || type == EJoinType.natural_leftouter
            || type == EJoinType.natural_rightouter
            || type == EJoinType.natural_fullouter;
    }

    private static String usingColumnName(TObjectName col) {
        if (col == null) return null;
        String name = col.getColumnNameOnly();
        if (name == null || name.isEmpty()) return null;
        // S14: keep the column name in its original form (with quotes if any).
        // {@link BindingMetadataAuthority#lookup} passes the name to
        // {@code namespace.hasColumn(...)} which routes through {@code
        // INameMatcher} — VendorNameMatcher honors per-dialect quoted vs
        // unquoted compare rules. Stripping quotes here would lose that
        // distinction (e.g. Oracle USING("Id") would have falsely matched a
        // catalog column folded to ID).
        return name;
    }

    private static BindingReferenceSite joinSite(String text) {
        return BindingReferenceSite.builder()
            .clause(BindingClause.OTHER)
            .referenceText(text == null || text.isEmpty() ? "JOIN" : text)
            .build();
    }

    // ===== NOT_FOUND =====

    private BindingDiagnostic buildNotFoundDiagnostic(TObjectName ref,
                                                     ResolutionContext ctx,
                                                     ScopeBuildResult sbr) {
        String columnName = ref.getColumnNameOnly();
        if (columnName == null || columnName.isEmpty()) {
            return null;
        }

        String qualifier = qualifierOf(ref);
        IScope scope = sbr.getScopeForColumn(ref);

        // Star reference (`t.*` or bare `*`): wildcard expansion is not a column
        // lookup. We MUST NOT emit UNKNOWN_COLUMN regardless of namespace state.
        // Plan §7.3 S7: user-authored `unknown_alias.*` emits
        // INVALID_STAR_QUALIFIER; everything else is silent.
        if (isStarReference(ref, columnName)) {
            if (qualifier == null || qualifier.isEmpty()) {
                return null;
            }
            INamespace starNs = (scope != null) ? scope.resolveTable(qualifier) : null;
            if (starNs == null) {
                return BindingDiagnosticBuilder
                    .forCode(BindingDiagnosticCode.INVALID_STAR_QUALIFIER)
                    .severity(severityFor(BindingDiagnosticCode.INVALID_STAR_QUALIFIER))
                    .message("Star qualifier " + qualifier
                        + " does not match any table or alias in scope.")
                    .objectNameText(ref.toString())
                    .site(siteFor(ref))
                    .statement(statementFor(ref, sbr))
                    .build();
            }
            return null;
        }

        if (qualifier != null && !qualifier.isEmpty()) {
            INamespace ns = (scope != null) ? scope.resolveTable(qualifier) : null;
            if (ns == null) {
                if (config != null && config.isBindingStrictCatalogValidation()) {
                    return BindingDiagnosticBuilder
                        .forCode(BindingDiagnosticCode.UNKNOWN_ALIAS)
                        .severity(severityFor(BindingDiagnosticCode.UNKNOWN_ALIAS))
                        .message("Qualifier " + qualifier
                            + " does not match any table or alias in scope.")
                        .objectNameText(ref.toString())
                        .site(siteFor(ref))
                        .statement(statementFor(ref, sbr))
                        .build();
                }
                return null;
            }
            MetadataState state = ns.getMetadataState();
            if (state == MetadataState.NOT_FOUND_IN_CATALOG) {
                if (config != null && config.isBindingStrictCatalogValidation()) {
                    return BindingDiagnosticBuilder
                        .forCode(BindingDiagnosticCode.UNKNOWN_TABLE)
                        .severity(severityFor(BindingDiagnosticCode.UNKNOWN_TABLE))
                        .message("Table " + qualifier + " was not found in the catalog.")
                        .objectNameText(ref.toString())
                        .site(siteFor(ref))
                        .statement(statementFor(ref, sbr))
                        .build();
                }
                return null;
            }
            if (state == MetadataState.METADATA_UNAVAILABLE) {
                if (config != null && config.isBindingStrictCatalogValidation()) {
                    return BindingDiagnosticBuilder
                        .forCode(BindingDiagnosticCode.CATALOG_METADATA_UNAVAILABLE)
                        .severity(severityFor(BindingDiagnosticCode.CATALOG_METADATA_UNAVAILABLE))
                        .message("Catalog metadata is unavailable for table " + qualifier + ".")
                        .objectNameText(ref.toString())
                        .site(siteFor(ref))
                        .statement(statementFor(ref, sbr))
                        .build();
                }
                return null;
            }
            ColumnAuthority authority = BindingMetadataAuthority.lookup(ns, columnName);
            if (authority != ColumnAuthority.AUTHORITATIVE_ABSENT) {
                return null;
            }

            String tableLabel = describeTable(ns);
            return BindingDiagnosticBuilder
                .forCode(BindingDiagnosticCode.UNKNOWN_COLUMN)
                .severity(severityFor(BindingDiagnosticCode.UNKNOWN_COLUMN))
                .message("Column " + ref.toString()
                    + " was not found in catalog table " + tableLabel + ".")
                .objectNameText(ref.toString())
                .site(siteFor(ref))
                .statement(statementFor(ref, sbr))
                .build();
        }

        // Unqualified miss. Plan §7.3 S5: UNBOUND_COLUMN_REFERENCE fires when
        // status NOT_FOUND AND no qualifier AND no in-scope table. Otherwise
        // wait for S6+ to add ambiguous/contextual handling.
        if (!hasInScopeTable(scope)) {
            return BindingDiagnosticBuilder
                .forCode(BindingDiagnosticCode.UNBOUND_COLUMN_REFERENCE)
                .severity(severityFor(BindingDiagnosticCode.UNBOUND_COLUMN_REFERENCE))
                .message("Column " + columnName
                    + " could not be bound: no tables are in scope.")
                .objectNameText(ref.toString())
                .site(siteFor(ref))
                .statement(statementFor(ref, sbr))
                .build();
        }
        return null;
    }

    private BindingDiagnostic buildSetOperationOutputDiagnostic(TObjectName ref,
                                                                ScopeBuildResult sbr) {
        if (ref == null || sbr == null) return null;

        String columnName = ref.getColumnNameOnly();
        if (columnName == null || columnName.isEmpty()
                || isStarReference(ref, columnName)) {
            return null;
        }

        String qualifier = qualifierOf(ref);
        if (qualifier == null || qualifier.isEmpty()) return null;

        IScope scope = sbr.getScopeForColumn(ref);
        INamespace ns = (scope != null) ? scope.resolveTable(qualifier) : null;
        if (!(ns instanceof UnionNamespace)) return null;

        UnionNamespace union = (UnionNamespace) ns;
        ColumnLevel level = union.hasAuthoritativeOutputColumn(columnName);
        if (level != ColumnLevel.NOT_EXISTS) return null;

        String displayName = union.getDisplayName();
        if (displayName == null || displayName.isEmpty()) {
            displayName = qualifier;
        }
        return BindingDiagnosticBuilder
            .forCode(BindingDiagnosticCode.SUBQUERY_OUTPUT_COLUMN_MISSING)
            .severity(severityFor(BindingDiagnosticCode.SUBQUERY_OUTPUT_COLUMN_MISSING))
            .message("Column " + ref.toString()
                + " is not exposed by set-operation derived table "
                + displayName + ".")
            .objectNameText(ref.toString())
            .site(siteFor(ref))
            .statement(statementFor(ref, sbr))
            .build();
    }

    private BindingDiagnostic buildCteOutputDiagnostic(TObjectName ref,
                                                       ScopeBuildResult sbr) {
        if (ref == null || sbr == null) return null;

        String columnName = ref.getColumnNameOnly();
        if (columnName == null || columnName.isEmpty()
                || isStarReference(ref, columnName)) {
            return null;
        }

        String qualifier = qualifierOf(ref);
        if (qualifier == null || qualifier.isEmpty()) return null;

        IScope scope = sbr.getScopeForColumn(ref);
        INamespace ns = (scope != null) ? scope.resolveTable(qualifier) : null;
        if (!(ns instanceof CTENamespace)) return null;

        CTENamespace cte = (CTENamespace) ns;
        ColumnLevel level = cte.hasAuthoritativeOutputColumn(columnName);
        if (level != ColumnLevel.NOT_EXISTS) return null;

        String cteName = cte.getDisplayName();
        if (cteName == null || cteName.isEmpty()) {
            cteName = qualifier;
        }
        return BindingDiagnosticBuilder
            .forCode(BindingDiagnosticCode.CTE_OUTPUT_COLUMN_MISSING)
            .severity(severityFor(BindingDiagnosticCode.CTE_OUTPUT_COLUMN_MISSING))
            .message("Column " + ref.toString()
                + " is not exposed by CTE " + cteName + ".")
            .objectNameText(ref.toString())
            .site(siteFor(ref))
            .statement(statementFor(ref, sbr))
            .build();
    }

    private BindingDiagnostic buildSubqueryOutputDiagnostic(TObjectName ref,
                                                            ScopeBuildResult sbr) {
        if (ref == null || sbr == null) return null;

        String columnName = ref.getColumnNameOnly();
        if (columnName == null || columnName.isEmpty()
                || isStarReference(ref, columnName)) {
            return null;
        }

        String qualifier = qualifierOf(ref);
        if (qualifier == null || qualifier.isEmpty()) return null;

        IScope scope = sbr.getScopeForColumn(ref);
        INamespace ns = (scope != null) ? scope.resolveTable(qualifier) : null;
        if (!(ns instanceof SubqueryNamespace)) return null;

        SubqueryNamespace subquery = (SubqueryNamespace) ns;
        ColumnLevel level = subquery.hasAuthoritativeOutputColumn(columnName);
        if (level != ColumnLevel.NOT_EXISTS) return null;

        String displayName = subquery.getDisplayName();
        if (displayName == null || displayName.isEmpty()) {
            displayName = qualifier;
        }
        return BindingDiagnosticBuilder
            .forCode(BindingDiagnosticCode.SUBQUERY_OUTPUT_COLUMN_MISSING)
            .severity(severityFor(BindingDiagnosticCode.SUBQUERY_OUTPUT_COLUMN_MISSING))
            .message("Column " + ref.toString()
                + " is not exposed by derived table " + displayName + ".")
            .objectNameText(ref.toString())
            .site(siteFor(ref))
            .statement(statementFor(ref, sbr))
            .build();
    }

    /**
     * For strict mode: checks a qualified reference's namespace state even when
     * the resolver produced an exact-match result (inferred at low confidence
     * against a NOT_FOUND_IN_CATALOG or METADATA_UNAVAILABLE table).
     * Returns a diagnostic when the namespace warrants one, null otherwise.
     */
    private BindingDiagnostic buildStrictModeNamespaceDiagnostic(TObjectName ref,
                                                                  ScopeBuildResult sbr) {
        String qualifier = qualifierOf(ref);
        if (qualifier == null || qualifier.isEmpty()) return null;
        IScope scope = sbr.getScopeForColumn(ref);
        INamespace ns = (scope != null) ? scope.resolveTable(qualifier) : null;
        if (ns == null) return null;
        MetadataState state = ns.getMetadataState();
        if (state == MetadataState.NOT_FOUND_IN_CATALOG) {
            return BindingDiagnosticBuilder
                .forCode(BindingDiagnosticCode.UNKNOWN_TABLE)
                .severity(severityFor(BindingDiagnosticCode.UNKNOWN_TABLE))
                .message("Table " + qualifier + " was not found in the catalog.")
                .objectNameText(ref.toString())
                .site(siteFor(ref))
                .statement(statementFor(ref, sbr))
                .build();
        }
        if (state == MetadataState.METADATA_UNAVAILABLE) {
            return BindingDiagnosticBuilder
                .forCode(BindingDiagnosticCode.CATALOG_METADATA_UNAVAILABLE)
                .severity(severityFor(BindingDiagnosticCode.CATALOG_METADATA_UNAVAILABLE))
                .message("Catalog metadata is unavailable for table " + qualifier + ".")
                .objectNameText(ref.toString())
                .site(siteFor(ref))
                .statement(statementFor(ref, sbr))
                .build();
        }
        return null;
    }

    /**
     * Slice S16c (plan §5.6.2): verify a resolver2 {@link
     * gudusoft.gsqlparser.resolver2.ResolutionStatus#EXACT_MATCH} against
     * authoritative catalog metadata.
     *
     * <p>Resolver2's exact match proves only that SQL scope binding selected
     * one source; the column may still be authoritatively absent from that
     * source's catalog metadata. The post-pass therefore reads the resolver2-
     * selected {@link INamespace} out of the recorded {@link ResolutionResult}
     * and asks {@link BindingMetadataAuthority#lookup(INamespace, String)}
     * only — it never re-binds names, searches SQL scope, or mutates AST
     * state.</p>
     *
     * <ul>
     *   <li>{@link ColumnAuthority#AUTHORITATIVE_PRESENT} — keep the resolver2
     *       binding; emit no diagnostic. Successful references picked up
     *       upstream still flow.</li>
     *   <li>{@link ColumnAuthority#AUTHORITATIVE_ABSENT} — emit
     *       {@link BindingDiagnosticCode#UNKNOWN_COLUMN} against the
     *       resolver2-selected source. Resolver2's binding state is NOT
     *       mutated.</li>
     *   <li>{@link ColumnAuthority#METADATA_UNAVAILABLE} — return null. Strict
     *       mode's {@link #buildStrictModeNamespaceDiagnostic} may then emit
     *       {@link BindingDiagnosticCode#CATALOG_METADATA_UNAVAILABLE} based on
     *       namespace state; non-strict mode stays silent.</li>
     * </ul>
     *
     * <p>Star references (bare {@code *} or {@code t.*}) are wildcard
     * expansion sites, not column lookups, and are skipped here even if the
     * resolver tagged them as {@code EXACT_MATCH}.</p>
     *
     * <p>The column-name compare flows through
     * {@link BindingMetadataAuthority#lookup} → {@link INamespace#hasColumn}
     * → vendor {@link gudusoft.gsqlparser.resolver2.matcher.INameMatcher}
     * (typically {@code VendorNameMatcher} when the parser auto-wires the
     * resolver), so per-dialect quoted-vs-unquoted rules are honored without
     * any bespoke string compare here. Quotes on the column token are
     * preserved (we never call {@code stripQuotes}).</p>
     */
    private BindingDiagnostic verifyExactMatchAgainstCatalog(TObjectName ref,
                                                             ResolutionResult result,
                                                             ScopeBuildResult sbr) {
        if (ref == null || result == null) return null;

        String columnName = ref.getColumnNameOnly();
        if (columnName == null || columnName.isEmpty()) return null;

        // Star references are not column lookups — skip catalog verification.
        if (isStarReference(ref, columnName)) return null;

        ColumnSource cs = result.getColumnSource();
        if (cs == null) return null;
        INamespace ns = cs.getSourceNamespace();
        if (ns == null) return null;

        ColumnAuthority verdict = BindingMetadataAuthority.lookup(ns, columnName);
        if (verdict != ColumnAuthority.AUTHORITATIVE_ABSENT) {
            // PRESENT → resolver2 binding catalog-verified; no diagnostic.
            // METADATA_UNAVAILABLE → defer to strict-mode namespace handler.
            return null;
        }

        String tableLabel = describeTable(ns);
        return BindingDiagnosticBuilder
            .forCode(BindingDiagnosticCode.UNKNOWN_COLUMN)
            .severity(severityFor(BindingDiagnosticCode.UNKNOWN_COLUMN))
            .message("Column " + ref.toString()
                + " was not found in catalog table " + tableLabel + ".")
            .objectNameText(ref.toString())
            .site(siteFor(ref))
            .statement(statementFor(ref, sbr))
            .build();
    }

    /**
     * S13 — DML target column entries are column DECLARATIONS, not column
     * references. They must never trigger {@code UNKNOWN_COLUMN},
     * {@code UNBOUND_COLUMN_REFERENCE}, or any other binding diagnostic
     * regardless of whether the column exists in the catalog (plan §7.3 S13:
     * "target columns produce zero diagnostics").
     *
     * <p>Detection is via {@link ESqlClause} location stamped by the
     * statement's {@code doParseStatement} when populating the target column
     * list:</p>
     *
     * <ul>
     *   <li>{@link ESqlClause#insertColumn} — {@code INSERT INTO t (a,b,c)} target
     *       columns.</li>
     *   <li>{@link ESqlClause#mergeInsert} — {@code MERGE … WHEN NOT MATCHED THEN
     *       INSERT (a,b,c)} target columns.</li>
     *   <li>{@link ESqlClause#set} — {@code UPDATE … SET col = …} LHS targets
     *       (and the equivalent {@code MERGE … WHEN MATCHED THEN UPDATE SET}
     *       LHS in dialects where it inherits the {@code set} location).
     *       The legacy linker pre-resolves these against the target table so
     *       they are usually silent already, but tagging here records
     *       {@link BindingSkipReason#TARGET_COLUMN} explicitly so consumers
     *       reading {@code ResolutionContext} directly see intent rather
     *       than inferring it from absence.</li>
     * </ul>
     *
     * <p>RHS expressions ({@link ESqlClause#setValue}) are NOT skipped — they
     * are bona-fide value references and continue to emit when missing.</p>
     */
    private static boolean isDmlTargetColumn(TObjectName ref) {
        if (ref == null) return false;
        ESqlClause loc = ref.getLocation();
        return loc == ESqlClause.insertColumn
            || loc == ESqlClause.mergeInsert
            || loc == ESqlClause.set;
    }

    private static boolean isStarReference(TObjectName ref, String columnName) {
        if ("*".equals(columnName)) {
            return true;
        }
        if (ref == null) return false;
        String text = ref.toString();
        return text != null && text.endsWith(".*");
    }

    private static String qualifierOf(TObjectName ref) {
        if (ref == null) return null;
        String tbl = ref.getTableString();
        if (tbl != null && !tbl.isEmpty()) {
            // Slice S15: preserve quotes so downstream {@link
            // IScope#resolveTable(String)} (now matcher-routed in
            // ListBasedScope) can apply per-dialect quoted-vs-unquoted alias
            // rules (e.g. Oracle quoted alias is case-sensitive, unquoted
            // folds to upper). Stripping quotes here drops that distinction.
            return tbl;
        }
        return null;
    }

    private static boolean hasInScopeTable(IScope scope) {
        if (scope == null) return false;
        try {
            List<INamespace> visible = scope.getVisibleNamespaces();
            return visible != null && !visible.isEmpty();
        } catch (Throwable ignore) {
            return false;
        }
    }

    private static String describeTable(INamespace ns) {
        if (ns == null) return "<unknown>";
        if (ns.getSourceTable() != null && ns.getSourceTable().getName() != null) {
            return ns.getSourceTable().getName().toString();
        }
        if (ns.getFinalTable() != null && ns.getFinalTable().getName() != null) {
            return ns.getFinalTable().getName().toString();
        }
        String display = ns.getDisplayName();
        return (display == null || display.isEmpty()) ? "<unknown>" : display;
    }

    // ===== shared =====

    private BindingDiagnosticSeverity severityFor(BindingDiagnosticCode code) {
        if (config == null) {
            return code.defaultSeverity();
        }
        BindingDiagnosticSeverity sev = config.getBindingSeverityFor(code);
        return sev != null ? sev : code.defaultSeverity();
    }

    private BindingReferenceSite siteFor(TObjectName ref) {
        String text = ref.toString();
        if (text == null || text.isEmpty()) {
            text = "<unknown>";
        }
        BindingClause clause = mapper.map(ref);
        return BindingReferenceSite.builder()
            .clause(clause)
            .referenceText(text)
            .build();
    }

    private static TCustomSqlStatement statementFor(TObjectName ref, ScopeBuildResult sbr) {
        if (sbr == null || ref == null) return null;
        IScope scope = sbr.getScopeForColumn(ref);
        while (scope != null) {
            TParseTreeNode node = scope.getNode();
            if (node instanceof TCustomSqlStatement) {
                return (TCustomSqlStatement) node;
            }
            if (node instanceof TSelectSqlStatement) {
                return (TCustomSqlStatement) node;
            }
            IScope parent = scope.getParent();
            if (parent == scope) break;
            scope = parent;
        }
        return null;
    }

    private BindingReference buildReference(TObjectName ref,
                                                   ScopeBuildResult sbr,
                                                   boolean bound) {
        if (ref == null) return null;
        String text = ref.toString();
        if (text == null || text.isEmpty()) return null;
        BindingReferenceKind kind = ref.toString().endsWith("*")
            ? BindingReferenceKind.STAR
            : BindingReferenceKind.COLUMN;
        return BindingReference.builder()
            .kind(kind)
            .objectNameText(text)
            .site(siteFor(ref))
            .bound(bound)
            .build();
    }
}