package gudusoft.gsqlparser.ir.semantic.diff;

import gudusoft.gsqlparser.ir.semantic.ColumnRef;
import gudusoft.gsqlparser.ir.semantic.LineageEdge;
import gudusoft.gsqlparser.ir.semantic.LineageRef;
import gudusoft.gsqlparser.ir.semantic.OutputColumn;
import gudusoft.gsqlparser.ir.semantic.RelationKind;
import gudusoft.gsqlparser.ir.semantic.RelationSource;
import gudusoft.gsqlparser.ir.semantic.SemanticProgram;
import gudusoft.gsqlparser.ir.semantic.StatementGraph;
import gudusoft.gsqlparser.ir.semantic.builder.SemanticIRBuilder;

import java.util.ArrayDeque;
import java.util.ArrayList;
import java.util.Deque;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.Set;

/**
 * Project a {@link SemanticProgram} into a {@link CanonicalLineageModel}.
 *
 * <p>SELECT lineage: for each outer-statement output, BFS the
 * program-level lineage edges (target → sources) until we hit
 * {@code TABLE_COLUMN} terminals. Each terminal becomes one canonical
 * SELECT edge.
 *
 * <p>Row influence: for each statement reachable from the outer (via
 * CTE/SUBQUERY relations), every column ref in {@code filterColumnRefs}
 * and {@code joinColumnRefs} is resolved to a base table by walking the
 * lineage chain that starts at the relation it references.
 */
public final class SemanticIRProjector {

    private SemanticIRProjector() {}

    public static ProjectorResult project(SemanticProgram program) {
        if (program == null) {
            throw new IllegalArgumentException("program must not be null");
        }
        if (program.getStatements().isEmpty()) {
            return ProjectorResult.unsupported(
                    ProjectorResult.UnsupportedReason.NO_RELATIONSHIPS,
                    "program has no statements");
        }
        // A degrade placeholder means some block was never analyzed. Its empty
        // OutputColumns would contribute zero edges below and the resulting
        // model would look complete while silently missing them, so refuse
        // rather than project an incomplete canonical model.
        for (StatementGraph s : program.getStatements()) {
            if (s.isUnanalyzed()) {
                return ProjectorResult.unsupported(
                        ProjectorResult.UnsupportedReason.UNANALYZED_BLOCK,
                        "block '" + s.getName() + "' was not analyzed: "
                                + s.getUnanalyzedReason().getMessage());
            }
        }
        // Outer statement: last unnamed statement. Named statements are CTE
        // bodies, FROM-subquery bodies, and (slice 11) scalar-subquery
        // bodies (synthetic angle-bracketed names — see
        // SemanticIRBuilder.SCALAR_BODY_PREFIX). The outer is the one that
        // reads from them and is always unnamed.
        int outerIndex = -1;
        for (int i = program.getStatements().size() - 1; i >= 0; i--) {
            if (program.getStatements().get(i).getName() == null) {
                outerIndex = i;
                break;
            }
        }
        if (outerIndex < 0) {
            return ProjectorResult.unsupported(
                    ProjectorResult.UnsupportedReason.NO_RELATIONSHIPS,
                    "no outer (unnamed) statement found");
        }

        // Index lineage edges by their from-key so the BFS is O(N) per query.
        Map<String, List<LineageEdge>> outgoingByFrom = indexLineage(program.getLineage());

        // Kind-aware lookups for row-influence resolution. A `CTE`-bound
        // relation refers to a body whose name is the CTE name; a
        // `SUBQUERY`-bound relation refers to a body whose name is the
        // FROM-clause alias. Keeping the maps separate avoids collision when
        // a CTE name and a subquery alias happen to match (e.g.
        // {@code WITH x AS (...) SELECT FROM (SELECT ...) x}).
        BodyIndexes bodies = new BodyIndexes(program);

        StatementGraph outer = program.getStatements().get(outerIndex);

        Set<String> outputNames = new LinkedHashSet<>();
        Map<String, Boolean> aggregateByOutput = new LinkedHashMap<>();
        Set<CanonicalLineageEdge> edges = new LinkedHashSet<>();

        // 1) SELECT edges per outer output.
        for (OutputColumn out : outer.getOutputColumns()) {
            String outName = out.getName().toLowerCase(Locale.ROOT);
            outputNames.add(outName);
            // Last write wins if the SQL has duplicate output names; the
            // builder doesn't reject them today and the canonical model
            // can't either, so we treat it as the OutputColumn list does.
            aggregateByOutput.put(outName, out.isAggregate());

            String startKey = stmtOutputKey(outerIndex, out.getName());
            for (TableColumn tc : bfsToBaseColumns(startKey, outgoingByFrom)) {
                edges.add(new CanonicalLineageEdge(
                        EdgeRole.SELECT, outName, tc.table, tc.column));
            }
        }

        // 2) Reachable statements from the outer. Single fixpoint BFS that
        // combines (a) CTE / SUBQUERY relation edges and (b) program-level
        // STATEMENT_OUTPUT → STATEMENT_OUTPUT lineage edges. The lineage
        // walk picks up scalar-subquery bodies (slice 11) — they are
        // referenced only via lineage, never via relations — and any
        // statement those scalar bodies reach via their own relations
        // gets visited in the same pass.
        Set<Integer> reachable = computeReachable(program, outerIndex, outgoingByFrom);

        // 3) Row-influence edges from every reachable statement's filter/join refs.
        for (int idx : reachable) {
            StatementGraph s = program.getStatements().get(idx);
            for (ColumnRef ref : s.getFilterColumnRefs()) {
                addRowInfluenceEdges(EdgeRole.FILTER, idx, s, ref,
                        outgoingByFrom, bodies, edges);
            }
            for (ColumnRef ref : s.getJoinColumnRefs()) {
                addRowInfluenceEdges(EdgeRole.JOIN, idx, s, ref,
                        outgoingByFrom, bodies, edges);
            }
        }

        // 4) Slice 24: predicate-body JOIN edges. Emit one JOIN canonical
        // edge per base-column terminal of each predicate body's
        // OutputColumn(s). The pass is intentionally OUTSIDE the
        // `reachable` BFS:
        //
        // (a) Predicate bodies are unreachable from outer by slice-23
        //     design — outer holds no relation pointing at them and no
        //     STATEMENT_OUTPUT lineage edge into them. Adding them to
        //     `reachable` would let `addRowInfluenceEdges` walk their
        //     filter/join refs, which would manufacture FILTER / JOIN
        //     edges from inner WHERE / inner JOIN refs — dlineage's BFS
        //     does not chain into RS-2's RelationRows from RS-1
        //     (slice-24 probe finding 3), so adding those edges would
        //     break the slice-7 zero-divergence guarantee.
        // (b) Iterating ONLY OutputColumns (not filter/join refs) means
        //     inner WHERE / inner JOIN refs do NOT contribute to outer's
        //     canonical model. This matches dlineage's behaviour: the
        //     fdr clause="on" source `clauseType="selectList"
        //     parent_id=<inner-RS>` resolves to the inner-projected
        //     column's base-column terminal via fdd chains, but the
        //     non-system source attribute prevents BFS from chaining
        //     into the inner RS's RelationRows.
        // (c) Slice-23 constant-only bodies have OutputColumns with
        //     empty `sources` → bfsToBaseColumns finds no terminals →
        //     zero JOIN edges added (preserves slice-23 zero-divergence
        //     on corpus 21). Slice-24 column-ref bodies have one
        //     ColumnRef source whose lineage chain leads to the inner
        //     base column → exactly one JOIN edge per terminal.
        // (d) De-duplication via the `Set<CanonicalLineageEdge>`
        //     semantics: a `(JOIN, null, departments, id)` edge from
        //     outer's `d.id = e.id` and from a column-bearing EXISTS
        //     `(SELECT d.id FROM departments d)` collapse to one.
        for (int idx = 0; idx < program.getStatements().size(); idx++) {
            StatementGraph s = program.getStatements().get(idx);
            if (s.getName() == null) continue;
            if (!SemanticIRBuilder.isPredicateSubquerySyntheticName(s.getName())) continue;
            for (OutputColumn out : s.getOutputColumns()) {
                String startKey = stmtOutputKey(idx, out.getName());
                for (TableColumn tc : bfsToBaseColumns(startKey, outgoingByFrom)) {
                    edges.add(new CanonicalLineageEdge(
                            EdgeRole.JOIN, null, tc.table, tc.column));
                }
            }
        }

        return ProjectorResult.ok(
                new CanonicalLineageModel(edges, outputNames, aggregateByOutput));
    }

    /**
     * Resolve a {@code ColumnRef} that appears in {@code stmt}'s filter/join
     * clause down to base-table columns and emit one row-influence edge per
     * terminal.
     */
    private static void addRowInfluenceEdges(EdgeRole role,
                                             int consumerIdx,
                                             StatementGraph stmt,
                                             ColumnRef ref,
                                             Map<String, List<LineageEdge>> outgoingByFrom,
                                             BodyIndexes bodies,
                                             Set<CanonicalLineageEdge> sink) {
        RelationSource matched = null;
        for (RelationSource r : stmt.getRelations()) {
            if (r.getAlias().equals(ref.getRelationAlias())) {
                matched = r;
                break;
            }
        }
        if (matched == null) {
            // Builder guarantees an alias is in scope, but be defensive — a
            // dropped row-influence edge would silently mask divergence.
            return;
        }
        // Slice 15: resolved-kind dispatch. OUTER_REFERENCE bindings
        // delegate to the underlying outerKind. TABLE emits a
        // base-column edge; CTE / SUBQUERY BFS through the outer body
        // to reach base columns (mirroring dlineage's behaviour).
        RelationKind kind = matched.getBinding().getKind();
        RelationKind resolvedKind = (kind == RelationKind.OUTER_REFERENCE)
                ? matched.getBinding().getOuterKind()
                : kind;
        if (resolvedKind == RelationKind.TABLE
                || resolvedKind == RelationKind.FUNCTION) {
            // FUNCTION (table-valued function) is an opaque terminal source,
            // projected like a base table column.
            sink.add(new CanonicalLineageEdge(role, null,
                    matched.getBinding().getQualifiedName().toLowerCase(Locale.ROOT),
                    ref.getColumnName().toLowerCase(Locale.ROOT)));
            return;
        }
        if (resolvedKind == RelationKind.CTE || resolvedKind == RelationKind.SUBQUERY) {
            Integer downstream = bodies.lookup(consumerIdx, resolvedKind, matched);
            if (downstream == null) return;
            String startKey = stmtOutputKey(downstream, ref.getColumnName());
            for (TableColumn tc : bfsToBaseColumns(startKey, outgoingByFrom)) {
                sink.add(new CanonicalLineageEdge(role, null, tc.table, tc.column));
            }
            return;
        }
        // UNION / UNKNOWN / null outerKind — should not appear in
        // row-influence today. A future slice introducing them must
        // extend this dispatch.
        throw new IllegalStateException(
                "unhandled RelationKind in addRowInfluenceEdges: " + kind
                        + (kind == RelationKind.OUTER_REFERENCE
                            ? " (outerKind=" + matched.getBinding().getOuterKind() + ")"
                            : ""));
    }

    /**
     * Single-fixpoint BFS over both (a) CTE/SUBQUERY relation edges
     * and (b) program-level STATEMENT_OUTPUT → STATEMENT_OUTPUT
     * lineage edges (slice 11). Each pop visits both edge kinds in
     * the same pass, so a scalar-subquery body reached via lineage
     * has its own CTE/SUBQUERY relations traversed before the BFS
     * terminates. The lineage edges are pre-indexed by the caller
     * to avoid rebuilding the index inside this method.
     */
    private static Set<Integer> computeReachable(SemanticProgram program,
                                                 int outerIndex,
                                                 Map<String, List<LineageEdge>> outgoingByFrom) {
        BodyIndexes bodies = new BodyIndexes(program);
        Set<Integer> reachable = new LinkedHashSet<>();
        Deque<Integer> q = new ArrayDeque<>();
        q.add(outerIndex);
        reachable.add(outerIndex);
        while (!q.isEmpty()) {
            int idx = q.removeFirst();
            StatementGraph s = program.getStatements().get(idx);
            // (a) CTE/SUBQUERY relations.
            for (RelationSource r : s.getRelations()) {
                RelationKind k = r.getBinding().getKind();
                if (k != RelationKind.CTE && k != RelationKind.SUBQUERY) continue;
                Integer downstream = bodies.lookup(idx, k, r);
                if (downstream != null && reachable.add(downstream)) {
                    q.add(downstream);
                }
            }
            // (b) STATEMENT_OUTPUT → STATEMENT_OUTPUT lineage edges
            // originating from this statement's outputs (slice 11
            // scalar-subquery body reachability).
            for (OutputColumn out : s.getOutputColumns()) {
                String key = stmtOutputKey(idx, out.getName());
                List<LineageEdge> outgoing = outgoingByFrom.get(key);
                if (outgoing == null) continue;
                for (LineageEdge e : outgoing) {
                    LineageRef to = e.getTo();
                    if (to.getKind() != LineageRef.Kind.STATEMENT_OUTPUT) continue;
                    int downstream = to.getStatementIndex();
                    if (reachable.add(downstream)) {
                        q.add(downstream);
                    }
                }
            }
        }
        return reachable;
    }

    /** Pure helper: BFS from a statement-output key over lineage edges. */
    private static List<TableColumn> bfsToBaseColumns(String startKey,
                                                       Map<String, List<LineageEdge>> outgoingByFrom) {
        List<TableColumn> out = new ArrayList<>();
        Set<String> visited = new HashSet<>();
        Deque<String> q = new ArrayDeque<>();
        q.add(startKey);
        visited.add(startKey);
        while (!q.isEmpty()) {
            String cur = q.removeFirst();
            List<LineageEdge> outgoing = outgoingByFrom.get(cur);
            if (outgoing == null) continue;
            for (LineageEdge e : outgoing) {
                LineageRef to = e.getTo();
                if (to.getKind() == LineageRef.Kind.TABLE_COLUMN) {
                    out.add(new TableColumn(
                            to.getQualifiedName().toLowerCase(Locale.ROOT),
                            to.getColumnName().toLowerCase(Locale.ROOT)));
                } else {
                    String nextKey = stmtOutputKey(to.getStatementIndex(), to.getOutputName());
                    if (visited.add(nextKey)) {
                        q.add(nextKey);
                    }
                }
            }
        }
        return out;
    }

    private static Map<String, List<LineageEdge>> indexLineage(List<LineageEdge> all) {
        Map<String, List<LineageEdge>> out = new HashMap<>();
        for (LineageEdge e : all) {
            LineageRef from = e.getFrom();
            if (from.getKind() != LineageRef.Kind.STATEMENT_OUTPUT) continue;
            String key = stmtOutputKey(from.getStatementIndex(), from.getOutputName());
            out.computeIfAbsent(key, k -> new ArrayList<>()).add(e);
        }
        return out;
    }

    private static String stmtOutputKey(int idx, String name) {
        return idx + "/" + name;
    }

    private static final class TableColumn {
        final String table;
        final String column;
        TableColumn(String table, String column) {
            this.table = table;
            this.column = column;
        }
    }

    /**
     * Kind-aware lookup from a CTE name or subquery alias to the index of
     * its body statement, scoped per consumer statement. Maps are keyed by
     * {@code consumerStmtIdx + "|" + name_lower}.
     *
     * <p>Slice 18 makes the lookup consumer-scoped (was: global alias-keyed)
     * to handle the new shape "two CTE bodies each containing a FROM-subquery
     * aliased {@code s}". With consumer-scoping each consumer sees its own
     * body, instead of the last-write-wins value of a global map.
     *
     * <p>Construction (two passes):
     * <ol>
     * <li>Walk consumers in statement order over their direct CTE / SUBQUERY
     *     relations. {@code SUBQUERY} consumers exclusively claim a body
     *     (FROM-subquery bodies are extracted by exactly one consumer);
     *     {@code CTE} consumers do <i>not</i> claim — CTEs are reusable, so
     *     multiple consumers can record their own per-consumer entry pointing
     *     at the same CTE body. Subquery-claimed bodies are skipped by CTE
     *     pickers so a CTE name colliding with a FROM alias does not
     *     mis-bind.</li>
     * <li>Walk STATEMENT_OUTPUT → STATEMENT_OUTPUT lineage edges to derive
     *     each child statement's immediate parent. For each child with an
     *     {@code OUTER_REFERENCE-of-CTE} or {@code OUTER_REFERENCE-of-SUBQUERY}
     *     relation, walk the immediate-parent chain (innermost → outermost)
     *     until an ancestor's Pass-1 entry is found, and copy that entry
     *     under {@code (childIdx, name)}. This is what makes the consumer-
     *     keyed lookup self-sufficient for slice-14/15 OUTER_REFERENCE row-
     *     influence — without it, a scalar body's lookup at its own index
     *     would return {@code null} because Pass 1 only stores entries for
     *     direct CTE/SUBQUERY relations (slice-15 invariant: OUTER_REFERENCE
     *     does not claim). Slice 20 generalises one-step propagation to a
     *     transitive chain walk so doubly-nested (and deeper) correlated
     *     scalars resolve their grandparent body via the same lookup.</li>
     * </ol>
     *
     * <p>Collision behaviour: when both a CTE named {@code x} and a subquery
     * aliased {@code x} exist, the SUBQUERY consumer's exclusive claim takes
     * the FROM-subquery body (latest unclaimed prior); the CTE consumer's
     * non-claiming pick takes the CTE body (earliest non-subquery-claimed
     * prior). Pinned by {@code subqueryAliasResolvesPastUnusedCteWithSameName}
     * and {@code outerReferenceRelationDoesNotClaimBodies} in
     * {@link gudusoft.gsqlparser.ir.semantic.slice7.SemanticIRProjectorBodyIndexesTest}.
     *
     * <p>Unused CTEs (declared but never referenced) have no entry in any
     * map. Reachability BFS does not visit them, so this is correct — there
     * is no caller that could ask for them. Slice 18 removed the legacy
     * default-population pass for the same reason.
     */
    private static final class BodyIndexes {
        // Per-consumer claim. Key: "<consumerStmtIdx>|<name_lower>".
        private final Map<String, Integer> cteByConsumerAndName = new HashMap<>();
        private final Map<String, Integer> subqueryByConsumerAndAlias = new HashMap<>();

        BodyIndexes(SemanticProgram program) {
            // Build the candidate-bodies-by-name index. Synthetic-named
            // bodies (slice-11 scalar bodies, slice-12 set-op branches) are
            // skipped — they are not CTE/FROM-subquery candidates and are
            // reached only via lineage edges in computeReachable.
            Map<String, java.util.List<Integer>> bodyIndicesByName = new HashMap<>();
            for (int i = 0; i < program.getStatements().size(); i++) {
                StatementGraph s = program.getStatements().get(i);
                if (s.getName() == null) continue;
                if (SemanticIRBuilder.isScalarSyntheticName(s.getName())) continue;
                if (SemanticIRBuilder.isSetOpBranchSyntheticName(s.getName())) continue;
                if (SemanticIRBuilder.isPredicateSubquerySyntheticName(s.getName())) continue;
                bodyIndicesByName
                        .computeIfAbsent(s.getName().toLowerCase(Locale.ROOT),
                                k -> new java.util.ArrayList<>())
                        .add(i);
            }

            // Pass 1: walk consumer relations in statement order. Body
            // claiming is kind-aware:
            //   - CTE consumer: pick the EARLIEST candidate matching the
            //     name with index < ci that is not already subquery-claimed.
            //     Do NOT claim — CTE bodies are reusable across consumers.
            //   - SUBQUERY consumer: pick the LATEST unclaimed candidate
            //     matching the alias with index < ci. Exclusive claim.
            //
            // Slice-15 invariant: OUTER_REFERENCE relations do NOT claim
            // bodies in this pass — the filter at line ~XXX excludes them.
            // Pass 2 below derives their per-consumer entries from the
            // parent's entries via lineage.
            Set<Integer> subqueryClaimed = new HashSet<>();
            for (int ci = 0; ci < program.getStatements().size(); ci++) {
                StatementGraph cs = program.getStatements().get(ci);
                for (RelationSource r : cs.getRelations()) {
                    RelationKind k = r.getBinding().getKind();
                    if (k != RelationKind.CTE && k != RelationKind.SUBQUERY) continue;
                    String name = (k == RelationKind.SUBQUERY)
                            ? r.getAlias().toLowerCase(Locale.ROOT)
                            : r.getBinding().getQualifiedName().toLowerCase(Locale.ROOT);
                    java.util.List<Integer> candidates = bodyIndicesByName.get(name);
                    if (candidates == null) continue;
                    Integer chosen = null;
                    if (k == RelationKind.CTE) {
                        for (Integer idx : candidates) {
                            if (idx >= ci) break;
                            if (subqueryClaimed.contains(idx)) continue;
                            chosen = idx;
                            break;
                        }
                    } else { // SUBQUERY
                        for (int j = candidates.size() - 1; j >= 0; j--) {
                            int idx = candidates.get(j);
                            if (idx >= ci) continue;
                            if (subqueryClaimed.contains(idx)) continue;
                            chosen = idx;
                            break;
                        }
                        if (chosen != null) subqueryClaimed.add(chosen);
                    }
                    if (chosen == null) continue;
                    String key = ci + "|" + name;
                    if (k == RelationKind.CTE) cteByConsumerAndName.put(key, chosen);
                    else                       subqueryByConsumerAndAlias.put(key, chosen);
                }
            }

            // Pass 2: derive each child statement's immediate parent from
            // STATEMENT_OUTPUT → STATEMENT_OUTPUT lineage. For each child
            // with an OUTER_REFERENCE-of-CTE or OUTER_REFERENCE-of-SUBQUERY
            // relation, walk the IMMEDIATE-PARENT CHAIN (innermost →
            // outermost) until an ancestor's per-consumer Pass-1 entry is
            // found, and copy that entry under the child's index. This
            // makes the consumer-keyed lookup self-sufficient for
            // OUTER_REFERENCE row-influence at any nesting depth (slice 18
            // codex round-2 MUST 2; slice 20 generalises one-step → chain).
            //
            // Slice-14/15 carry only the immediate-parent scope and one
            // STATEMENT_OUTPUT edge per child output is typical, so
            // first-write-wins is correct. Slice-20 chain walks support
            // doubly-nested (and deeper) correlated scalars where the
            // inner-inner's OUTER_REFERENCE is anchored at the
            // grandparent (or further-ancestor) statement.
            //
            // Cycle guard via `visited`: STATEMENT_OUTPUT → STATEMENT_OUTPUT
            // is a DAG by construction (a child's output never flows back
            // to an ancestor's output), but the guard makes the
            // non-termination invariant explicit.
            Map<Integer, Integer> immediateParent = new HashMap<>();
            for (LineageEdge e : program.getLineage()) {
                if (e.getFrom().getKind() != LineageRef.Kind.STATEMENT_OUTPUT) continue;
                if (e.getTo().getKind() != LineageRef.Kind.STATEMENT_OUTPUT) continue;
                // Slice 24: predicate-body edges do NOT anchor immediate-
                // parent chains. The predicate body is unreachable from
                // outer (slice-23 invariant); recording it as the
                // "immediate parent" of a CTE / SUBQUERY body would
                // mis-route OUTER_REFERENCE chain walks below — a slice-15
                // / slice-20 child looking for the CTE owner would land
                // at the predicate body's index, find no Pass-1 entry,
                // walk to the predicate's parent (none recorded), and
                // give up. Slice-23 constant predicate bodies emitted no
                // STATEMENT_OUTPUT → STATEMENT_OUTPUT edges (their
                // synthetic OutputColumn had empty sources), so the side
                // effect surfaces only when slice-24 column-bearing
                // predicate bodies have CTE-bound inner relations.
                int fromIdx = e.getFrom().getStatementIndex();
                StatementGraph fromStmt = program.getStatements().get(fromIdx);
                if (fromStmt.getName() != null
                        && SemanticIRBuilder.isPredicateSubquerySyntheticName(fromStmt.getName())) {
                    continue;
                }
                immediateParent.putIfAbsent(
                        e.getTo().getStatementIndex(),
                        e.getFrom().getStatementIndex());
            }
            for (Map.Entry<Integer, Integer> entry : immediateParent.entrySet()) {
                int childIdx = entry.getKey();
                StatementGraph cs = program.getStatements().get(childIdx);
                for (RelationSource r : cs.getRelations()) {
                    if (r.getBinding().getKind() != RelationKind.OUTER_REFERENCE) continue;
                    RelationKind ok = r.getBinding().getOuterKind();
                    if (ok != RelationKind.CTE && ok != RelationKind.SUBQUERY) continue;
                    String name = (ok == RelationKind.SUBQUERY)
                            ? r.getAlias().toLowerCase(Locale.ROOT)
                            : r.getBinding().getQualifiedName().toLowerCase(Locale.ROOT);
                    Map<String, Integer> sourceMap = (ok == RelationKind.CTE)
                            ? cteByConsumerAndName : subqueryByConsumerAndAlias;
                    Integer body = null;
                    Integer cur = entry.getValue();
                    Set<Integer> visited = new HashSet<>();
                    visited.add(childIdx); // never claim self
                    while (cur != null && visited.add(cur)) {
                        body = sourceMap.get(cur + "|" + name);
                        if (body != null) break;
                        cur = immediateParent.get(cur);
                    }
                    if (body == null) continue;
                    String childKey = childIdx + "|" + name;
                    if (ok == RelationKind.CTE)
                        cteByConsumerAndName.putIfAbsent(childKey, body);
                    else
                        subqueryByConsumerAndAlias.putIfAbsent(childKey, body);
                }
            }
        }

        Integer lookup(int consumerIdx, RelationKind kind, RelationSource consumer) {
            String name = (kind == RelationKind.CTE)
                    ? consumer.getBinding().getQualifiedName().toLowerCase(Locale.ROOT)
                    : consumer.getAlias().toLowerCase(Locale.ROOT);
            String key = consumerIdx + "|" + name;
            if (kind == RelationKind.CTE) return cteByConsumerAndName.get(key);
            if (kind == RelationKind.SUBQUERY) return subqueryByConsumerAndAlias.get(key);
            return null;
        }
    }
}