package gudusoft.gsqlparser.dlineage.dynamicsql;

import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.TBaseType;
import gudusoft.gsqlparser.TCustomSqlStatement;
import gudusoft.gsqlparser.TSourceToken;
import gudusoft.gsqlparser.TStatementList;
import gudusoft.gsqlparser.dlineage.DataFlowAnalyzer;
import gudusoft.gsqlparser.dlineage.dataflow.model.DynamicSqlSite;
import gudusoft.gsqlparser.dlineage.dataflow.model.Option;
import gudusoft.gsqlparser.dlineage.dataflow.model.xml.dataflow;
import gudusoft.gsqlparser.dlineage.dataflow.model.xml.relationship;
import gudusoft.gsqlparser.dlineage.dataflow.model.xml.sourceColumn;
import gudusoft.gsqlparser.dlineage.dataflow.model.xml.table;
import gudusoft.gsqlparser.dlineage.dataflow.model.xml.targetColumn;
import gudusoft.gsqlparser.nodes.TParameterDeclaration;
import gudusoft.gsqlparser.nodes.TParameterDeclarationList;
import gudusoft.gsqlparser.sqlenv.ESQLDataObjectType;
import gudusoft.gsqlparser.sqlenv.TSQLEnv;
import gudusoft.gsqlparser.stmt.mssql.TMssqlCreateProcedure;
import gudusoft.gsqlparser.util.SQLUtil;

import java.io.UnsupportedEncodingException;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

/**
 * Orchestrates dynamic-SQL lineage resolution: evaluate the procedure's
 * dynamic-SQL string building under the given bindings
 * ({@link TDynamicSqlStringEvaluator}), then for each materialized EXEC site run
 * ordinary dlineage on the concrete string and tag the resulting edges with
 * dynamic provenance.
 *
 * <p>This is purely additive — it constructs fresh, throwaway
 * {@link DataFlowAnalyzer} instances for the materialized strings and never
 * touches the default analysis path, so default dlineage output is unchanged.
 */
public final class DynamicSqlLineageResolver {

    private DynamicSqlLineageResolver() {
    }

    /**
     * Materialized dynamic-SQL text for one EXEC / sp_executesql site, produced by the
     * no-bindings default-path evaluation ({@link #materializeSitesWithoutBindings}).
     */
    public static final class MaterializedSite {
        /** Usable (possibly placeholder-bearing) SQL text, or {@code null} if the string did not reduce. */
        public final String sqlText;
        /** True when the text still contains placeholder tokens for unbound variables/parameters. */
        public final boolean partial;
        /**
         * True unless the materialization is usable and every rendered character
         * has literal provenance. This is intentionally stricter than
         * {@link #partial}: a legacy-CONCRETE numeric/inexact transform can still
         * have a provenance hole that SHADOW must report.
         */
        public final boolean provenanceIncomplete;
        /** Why the string did not reduce; non-null iff {@link #sqlText} is null. */
        public final String reason;
        /**
         * Ordered provenance fragments. When {@link #sqlText} is non-null their
         * concatenated text equals it (design:
         * dynamic-sql-fragment-provenance-design.md); SHADOW completeness checks
         * and resolved dynamic edges consume that. When the string did not reduce
         * ({@link #sqlText} null) they are the best-effort provenance of the
         * argument — every hole met, in order, so a consumer can still name the
         * value that stopped the fold; their concatenated text is only how far
         * the fold got. Empty when the producing channel carries no provenance.
         */
        public final java.util.List<SqlFragment> fragments;
        /** Additive analysis note, such as a constant-relation over-approximation. */
        public final String diagnostic;
        /** Stable value-set row-tuple suffix; empty on the scalar compatibility path. */
        public final String variantPath;

        MaterializedSite(String sqlText, boolean partial, String reason) {
            this(sqlText, partial, reason, null);
        }

        MaterializedSite(String sqlText, boolean partial, String reason,
                java.util.List<SqlFragment> fragments) {
            this(sqlText, partial, reason, fragments, null, "");
        }

        MaterializedSite(String sqlText, boolean partial, String reason,
                java.util.List<SqlFragment> fragments, String diagnostic,
                String variantPath) {
            this.sqlText = sqlText;
            this.partial = partial;
            this.reason = reason;
            this.fragments = fragments == null || fragments.isEmpty()
                    ? java.util.Collections.<SqlFragment>emptyList()
                    : java.util.Collections.unmodifiableList(
                            new java.util.ArrayList<SqlFragment>(fragments));
            this.provenanceIncomplete = !DynamicSqlLineageResolver
                    .hasCompleteLiteralProvenance(sqlText, this.fragments);
            this.diagnostic = diagnostic;
            this.variantPath = variantPath == null ? "" : variantPath;
        }

        public boolean hasCompleteLiteralProvenance() {
            return !provenanceIncomplete;
        }
    }

    /**
     * Default-path hook for {@link DataFlowAnalyzer}: abstractly evaluate a procedure's
     * dynamic-SQL string building with NO call-site bindings — only literal parameter
     * defaults are seeded — and return the materialized string per EXEC/sp_executesql
     * statement node (identity-keyed against the SAME parse tree that was passed in).
     * Constant- and template-built strings (REPLACE/QUOTENAME/CONCAT chains over
     * literals and literal-seeded variables) reduce fully; strings depending on unbound
     * parameters reduce to placeholder-bearing PARTIAL text; anything else is reported
     * with a reason. Purely computational — nothing is executed and no model state of
     * any analyzer is touched.
     */
    public static Map<TCustomSqlStatement, MaterializedSite> materializeSitesWithoutBindings(
            TCustomSqlStatement procAst, EDbVendor vendor, DynamicLineageOptions options) {
        return materializeSites(procAst, vendor, null, options);
    }

    /**
     * Same as {@link #materializeSitesWithoutBindings}, but with caller-supplied parameter
     * bindings (e.g. extracted from a literal call site) seeded before the walk. Parameters
     * absent from the bindings fall back to their literal defaults, then to placeholders.
     */
    public static Map<TCustomSqlStatement, MaterializedSite> materializeSites(
            TCustomSqlStatement procAst, EDbVendor vendor, Map<String, SqlValue> bindings,
            DynamicLineageOptions options) {
        List<Map<TCustomSqlStatement, MaterializedSite>> variants =
                materializeSiteVariants(procAst, vendor, bindings, options);
        return variants.isEmpty()
                ? new java.util.IdentityHashMap<TCustomSqlStatement, MaterializedSite>()
                : variants.get(0);
    }

    /**
     * Materialize all bounded, row-correlated constant-relation variants for a
     * procedure. Variant order is deterministic: declaration/statement order,
     * then INSERT VALUES row order. The legacy single-map API above returns the
     * first entry and is byte-compatible when the value-set feature is unused.
     */
    public static List<Map<TCustomSqlStatement, MaterializedSite>> materializeSiteVariants(
            TCustomSqlStatement procAst, EDbVendor vendor, Map<String, SqlValue> bindings,
            DynamicLineageOptions options) {
        if (options == null) {
            options = DynamicLineageOptions.createDefault();
        }
        List<Map<TCustomSqlStatement, MaterializedSite>> variants =
                new ArrayList<Map<TCustomSqlStatement, MaterializedSite>>();
        // Same resource guardrails as resolve(): a binding count over the cap is
        // rejected outright (best-effort empty result, since this hook must not throw
        // into the default analysis path).
        if (bindings != null && bindings.size() > options.getMaxBindings()) {
            return variants;
        }
        TStatementList body = extractBody(procAst);
        if (body == null) {
            return variants;
        }
        long deadlineNanos = System.nanoTime() + options.getTimeoutMillis() * 1_000_000L;
        List<ConstRelationVariantPlanner.EvaluationPlan> plans =
                ConstRelationVariantPlanner.plan(body, vendor, options);
        for (ConstRelationVariantPlanner.EvaluationPlan plan : plans) {
            Map<TCustomSqlStatement, MaterializedSite> result =
                    new java.util.IdentityHashMap<TCustomSqlStatement, MaterializedSite>();
            TDynamicSqlStringEvaluator evaluator =
                    new TDynamicSqlStringEvaluator(vendor, options, plan, deadlineNanos);
            evaluator.seedBindings(bindings);
            evaluator.seedParameterDefaults(extractParams(procAst));
            for (TDynamicSqlStringEvaluator.RawSite raw : evaluator.evaluate(body)) {
                if (raw.node == null) {
                    continue;
                }
                SqlStringValue sql = raw.sql;
                if (sql != null && sql.usable() && sql.text != null
                        && sql.text.length() > options.getMaxResolvedSqlLength()) {
                    result.put(raw.node, new MaterializedSite(null, false,
                            "resolved SQL length " + sql.text.length()
                                    + " exceeds maxResolvedSqlLength",
                            sql.fragments, raw.diagnostic, raw.variantPath));
                } else if (sql != null && sql.usable()) {
                    result.put(raw.node, new MaterializedSite(sql.text,
                            sql.state == SqlStringValue.State.PARTIAL, null, sql.fragments,
                            raw.diagnostic, raw.variantPath));
                } else {
                    // Unreduced: no text, but the value still carries the holes the
                    // evaluator met (which variable, assigned from what, where) —
                    // that is the site's unresolved-fragment evidence.
                    result.put(raw.node, new MaterializedSite(null, false,
                            sql != null && sql.reason != null ? sql.reason
                                    : "dynamic SQL did not reduce to a concrete string",
                            sql == null ? null : sql.fragments, raw.diagnostic, raw.variantPath));
                }
            }
            variants.add(result);
        }
        return variants;
    }

    public static DynamicLineageResult resolve(TCustomSqlStatement procAst, EDbVendor vendor, TSQLEnv sqlEnv,
            String currentDatabase, String defaultSchema, Map<String, SqlValue> bindings,
            DynamicLineageOptions options) {

        if (options == null) {
            options = DynamicLineageOptions.createDefault();
        }
        if (bindings != null && bindings.size() > options.getMaxBindings()) {
            throw new IllegalArgumentException(
                    "binding count " + bindings.size() + " exceeds maxBindings " + options.getMaxBindings());
        }

        TStatementList body = extractBody(procAst);
        List<TParameterDeclaration> params = extractParams(procAst);
        String sourceProc = extractProcName(procAst);
        String bindingHash = hashBindings(bindings);

        DynamicLineageResult result = new DynamicLineageResult(sourceProc, bindingHash);
        if (body == null) {
            return result; // not a recognized procedure shape — nothing to evaluate
        }

        long deadlineNanos = System.nanoTime() + options.getTimeoutMillis() * 1_000_000L;
        List<ConstRelationVariantPlanner.EvaluationPlan> plans =
                ConstRelationVariantPlanner.plan(body, vendor, options);
        java.util.IdentityHashMap<TCustomSqlStatement, java.util.Set<String>> seen =
                new java.util.IdentityHashMap<TCustomSqlStatement, java.util.Set<String>>();
        boolean truncated = false;
        int emitted = 0;
        for (ConstRelationVariantPlanner.EvaluationPlan plan : plans) {
            TDynamicSqlStringEvaluator evaluator =
                    new TDynamicSqlStringEvaluator(vendor, options, plan, deadlineNanos);
            evaluator.seedBindings(bindings);
            evaluator.seedParameterDefaults(params);
            for (TDynamicSqlStringEvaluator.RawSite raw : evaluator.evaluate(body)) {
                if (raw.node == null) {
                    continue;
                }
                String valueKey = raw.sql == null ? "<null>"
                        : raw.sql.state + "\u0000" + raw.sql.text + "\u0000" + raw.sql.reason
                                + "\u0000" + raw.controlFlowCertain;
                java.util.Set<String> values = seen.get(raw.node);
                if (values == null) {
                    values = new java.util.LinkedHashSet<String>();
                    seen.put(raw.node, values);
                }
                if (!values.add(valueKey)) {
                    continue;
                }
                if (emitted >= options.getMaxSites()) {
                    truncated = true;
                    break;
                }
                result.addSite(buildSite(raw, vendor, sqlEnv, currentDatabase,
                        defaultSchema, sourceProc, bindingHash, options));
                emitted++;
            }
            truncated |= evaluator.isSitesTruncated();
            if (truncated) {
                break;
            }
        }
        // Never let a truncated walk look complete — emit an honest diagnostic.
        if (truncated) {
            result.addSite(new DynamicSiteResult(DynamicSiteResult.Kind.EXEC_STRING,
                    DynamicSiteResult.Status.UNRESOLVED, null, null, sourceProc,
                    dynamicSiteId(sourceProc, "truncated"), bindingHash,
                    "site/time limit reached (maxSites=" + options.getMaxSites()
                            + ", timeout=" + options.getTimeoutMillis() + "ms); further sites dropped",
                    0L, 0L, new ArrayList<DynamicLineageEdge>(), options.getTrustMode(),
                    0, -1, false));
        }
        return result;
    }

    /**
     * Discover parameter bindings from literal call sites of {@code procAst} in the same
     * parsed file: top-level {@code EXEC <proc> 'literal', @p = 'literal', ...} statements
     * whose (unqualified) module name matches the procedure name. Positional and named
     * arguments are supported; OUTPUT and non-literal arguments are left unbound (they
     * become placeholders during evaluation). Returns up to {@code maxBindingSets} distinct
     * binding sets in file order.
     */
    public static List<Map<String, SqlValue>> discoverCallSiteBindings(TCustomSqlStatement procAst,
            int maxBindingSets) {
        List<Map<String, SqlValue>> bindingSets = new ArrayList<Map<String, SqlValue>>();
        if (!(procAst instanceof TMssqlCreateProcedure) || procAst.getGsqlparser() == null) {
            return bindingSets;
        }
        TMssqlCreateProcedure proc = (TMssqlCreateProcedure) procAst;
        if (proc.getProcedureName() == null) {
            return bindingSets;
        }
        String procSimpleName = lastNameSegment(proc.getProcedureName().toString());
        List<TParameterDeclaration> params = extractParams(procAst);
        String procDb = effectiveDatabaseOfProc(procAst.getGsqlparser().getSqlstatements(), procAst);
        for (BindingCandidate candidate : sameFileBindingCandidates(procAst, proc, procSimpleName,
                params, procDb, maxBindingSets)) {
            bindingSets.add(candidate.bindings);
        }
        return bindingSets;
    }

    /**
     * Matching call sites in the procedure's own file, in file order, deduplicated by
     * canonical key, up to {@code cap}. Tracks the effective database (USE statements)
     * so a call after USE DbB is never bound to a same-named procedure defined under
     * USE DbA.
     */
    private static List<BindingCandidate> sameFileBindingCandidates(TCustomSqlStatement procAst,
            TMssqlCreateProcedure proc, String procSimpleName, List<TParameterDeclaration> params,
            String procDb, int cap) {
        List<BindingCandidate> result = new ArrayList<BindingCandidate>();
        java.util.Set<String> seen = new java.util.HashSet<String>();
        TStatementList all = procAst.getGsqlparser().getSqlstatements();
        String currentDb = null;
        for (int i = 0; i < all.size() && result.size() < cap; i++) {
            if (all.get(i) instanceof gudusoft.gsqlparser.stmt.TUseDatabase) {
                currentDb = String.valueOf(((gudusoft.gsqlparser.stmt.TUseDatabase) all.get(i)).getDatabaseName());
                continue;
            }
            if (!(all.get(i) instanceof gudusoft.gsqlparser.stmt.mssql.TMssqlExecute)) {
                continue;
            }
            gudusoft.gsqlparser.stmt.mssql.TMssqlExecute call =
                    (gudusoft.gsqlparser.stmt.mssql.TMssqlExecute) all.get(i);
            if (!callMatchesProc(call, proc, procSimpleName, procDb, currentDb)) {
                continue;
            }
            BindingCandidate candidate = bindingCandidateFromCall(call, params);
            if (candidate != null && seen.add(candidate.canonicalKey)) {
                result.add(candidate);
            }
        }
        return result;
    }

    /**
     * Discover parameter bindings from literal call sites of {@code procAst} located in
     * OTHER files of the same multi-file analysis (plan Phase 5: cross-file routine
     * references). {@code otherFileSqlTexts} holds the raw SQL text of every analysis
     * unit; the procedure's own file is skipped by text identity. A text is parsed at
     * most once per analysis via the caller-owned {@code parseCache} (key: the text),
     * and only when it mentions the procedure's simple name at all.
     *
     * <p>Never guesses across ambiguity: if any other file defines a procedure with the
     * same simple name in a database-compatible context, there are two candidate owners
     * for a matching call and this method returns no bindings at all. Binding sets are
     * returned in canonical-key order, so the result is independent of the file order
     * of the analysis. Binding sets already surfaced by the same-file discovery above
     * are excluded here, so duplicates never consume the caller's shared cap.
     */
    public static List<Map<String, SqlValue>> discoverCrossFileCallSiteBindings(
            TCustomSqlStatement procAst, RoutineCatalog catalog, int maxBindingSets) {
        List<Map<String, SqlValue>> bindingSets = new ArrayList<Map<String, SqlValue>>();
        if (maxBindingSets <= 0 || catalog == null
                || !(procAst instanceof TMssqlCreateProcedure)
                || procAst.getGsqlparser() == null) {
            return bindingSets;
        }
        TMssqlCreateProcedure proc = (TMssqlCreateProcedure) procAst;
        if (proc.getProcedureName() == null) {
            return bindingSets;
        }
        String procSimpleName = lastNameSegment(proc.getProcedureName().toString());
        String probeName = bareName(procSimpleName);
        if (probeName.isEmpty()) {
            return bindingSets;
        }
        List<TParameterDeclaration> params = extractParams(procAst);
        String ownText = procAst.getGsqlparser().sqltext;
        // For matching and the ambiguity verdict, an explicit catalog on the target
        // definition's own three-part name outranks the USE-derived database (a
        // definition CREATE PROC DbA.dbo.p IS provably in DbA).
        String sameFileProcDb = effectiveDatabaseOfProc(procAst.getGsqlparser().getSqlstatements(), procAst);
        String procDb = definitionDatabase(proc, sameFileProcDb);

        // The same-file pass already surfaced these binding sets (cross-file is only
        // invoked when its cap was not reached, so an uncapped re-collection sees
        // exactly the sets it returned). Excluding their keys keeps duplicates from
        // consuming the shared cap and displacing distinct cross-file bindings.
        java.util.Set<String> sameFileKeys = new java.util.HashSet<String>();
        for (BindingCandidate sameFile : sameFileBindingCandidates(procAst, proc, procSimpleName,
                params, sameFileProcDb, Integer.MAX_VALUE)) {
            sameFileKeys.add(sameFile.canonicalKey);
        }

        // Canonical-key ordering makes the accepted binding sets (and which ones
        // survive the cap) independent of the order files were passed in.
        java.util.TreeMap<String, Map<String, SqlValue>> ordered =
                new java.util.TreeMap<String, Map<String, SqlValue>>();
        for (String text : catalog.unitTexts()) {
            if (text == null || text.equals(ownText)) {
                continue;
            }
            if (!text.toLowerCase(java.util.Locale.ENGLISH)
                    .contains(probeName.toLowerCase(java.util.Locale.ENGLISH))) {
                continue;
            }
            RoutineCatalog.UnitIndex unit = catalog.indexOf(text);
            if (unit == null) {
                continue;
            }
            for (RoutineCatalog.Definition def : unit.definitions) {
                // A same-named definition in another file: unless the two are
                // provably in different databases, a matching call has two
                // candidate owners - bail out entirely rather than guess.
                if (SQLUtil.sameName(EDbVendor.dbvmssql, ESQLDataObjectType.dotProcedure,
                        def.simple, procSimpleName)
                        && !provablyDifferentDatabase(procDb, def.db)) {
                    return new ArrayList<Map<String, SqlValue>>();
                }
            }
            for (RoutineCatalog.CallCandidate callCandidate : unit.callCandidates) {
                if (!(callCandidate.stmt instanceof gudusoft.gsqlparser.stmt.mssql.TMssqlExecute)) {
                    continue;
                }
                gudusoft.gsqlparser.stmt.mssql.TMssqlExecute call =
                        (gudusoft.gsqlparser.stmt.mssql.TMssqlExecute) callCandidate.stmt;
                if (!callMatchesProc(call, proc, procSimpleName, procDb, callCandidate.db)) {
                    continue;
                }
                BindingCandidate candidate = bindingCandidateFromCall(call, params);
                if (candidate != null && !sameFileKeys.contains(candidate.canonicalKey)
                        && !ordered.containsKey(candidate.canonicalKey)) {
                    ordered.put(candidate.canonicalKey, candidate.bindings);
                }
            }
        }
        for (Map<String, SqlValue> bindings : ordered.values()) {
            if (bindingSets.size() >= maxBindingSets) {
                break;
            }
            bindingSets.add(bindings);
        }
        return bindingSets;
    }

    /**
     * Oracle analog of cross-file call-site discovery (plan Phase 5): collect
     * top-level statements from OTHER files (anonymous blocks, CALL statements)
     * that mention a routine defined in the current file, so the caller can feed
     * them to {@code TASTEvaluator} together with the file's own statements and
     * fold parameter-dependent EXECUTE IMMEDIATE templates under the callers'
     * literal arguments.
     *
     * <p>Mirrors the MSSQL rules: a text is parsed at most once per analysis via
     * {@code parseCache} and only when it mentions one of the candidate routine
     * names; a same-simple-name routine DEFINITION in another file makes that
     * name ambiguous and drops it from the harvest (never guess between two
     * candidate owners); foreign definitions themselves are never appended.
     * Returned statements are ordered by their source text, so evaluation order
     * is independent of the file order of the analysis.
     */
    public static List<TCustomSqlStatement> plsqlInterpretationStatements(
            TStatementList ownStatements, String ownText, RoutineCatalog catalog,
            int maxForeignCallers) {
        List<TCustomSqlStatement> result = new ArrayList<TCustomSqlStatement>();
        if (ownStatements == null) {
            return result;
        }
        // Routine definitions in this file - the possible callees. Only routines whose
        // partial dynamic-SQL expressions are provably literal-bindable participate:
        // the interpreter folds any dependency it cannot evaluate (SELECT INTO
        // locals, function results, ...) to an EMPTY string, which would produce a
        // concrete-looking but WRONG statement that the placeholder classifier
        // cannot catch.
        List<PlsqlRoutineIdent> idents = new ArrayList<PlsqlRoutineIdent>();
        for (int i = 0; i < ownStatements.size(); i++) {
            TCustomSqlStatement stmt = ownStatements.get(i);
            String name = plsqlRoutineSimpleName(stmt);
            if (name == null || name.isEmpty()) {
                continue;
            }
            if (plsqlDynamicSqlLiteralBindable(stmt)) {
                idents.add(new PlsqlRoutineIdent(stmt, name));
            }
        }
        if (idents.isEmpty()) {
            return result;
        }
        // Foreign pass 1: definitions in other files decide ambiguity. A same-simple-
        // name definition that is not provably in a different schema means a matching
        // call has two candidate owners.
        List<RoutineCatalog.UnitIndex> foreignUnits = new ArrayList<RoutineCatalog.UnitIndex>();
        if (catalog != null) {
            for (String text : catalog.unitTexts()) {
                if (text == null || text.equals(ownText)) {
                    continue;
                }
                String lowered = text.toLowerCase(java.util.Locale.ENGLISH);
                boolean mentionsAny = false;
                for (PlsqlRoutineIdent ident : idents) {
                    if (lowered.contains(bareName(ident.simple).toLowerCase(java.util.Locale.ENGLISH))) {
                        mentionsAny = true;
                        break;
                    }
                }
                if (!mentionsAny) {
                    continue;
                }
                RoutineCatalog.UnitIndex unit = catalog.indexOf(text);
                if (unit == null) {
                    continue;
                }
                foreignUnits.add(unit);
                for (RoutineCatalog.Definition def : unit.definitions) {
                    for (PlsqlRoutineIdent ident : idents) {
                        if (!SQLUtil.sameName(EDbVendor.dbvoracle, ESQLDataObjectType.dotProcedure,
                                def.simple, ident.simple)) {
                            continue;
                        }
                        // ANY same-simple-name duplicate disqualifies UNQUALIFIED calls:
                        // the caller's current schema is unknown, so even a provably
                        // different schema could be the one the call resolves in.
                        ident.hasForeignSameName = true;
                        if (!provablyDifferentSchema(ident.schema, def.schema)) {
                            // Not provably different: qualified calls cannot separate
                            // the two candidates either.
                            ident.ambiguous = true;
                        }
                    }
                }
            }
        }
        // Same-file callers (file order, no cap - they were always visible to the
        // analysis), then foreign callers ordered by their EXACT source text so the
        // pick under the cap is independent of file order and distinct quoted
        // literals are never conflated.
        List<TCustomSqlStatement> callers = new ArrayList<TCustomSqlStatement>();
        for (int i = 0; i < ownStatements.size(); i++) {
            TCustomSqlStatement stmt = ownStatements.get(i);
            if (plsqlRoutineSimpleName(stmt) != null || isPlsqlDefinitionShape(stmt)) {
                continue;
            }
            if (isVettedLiteralCaller(stmt, idents)) {
                callers.add(stmt);
            }
        }
        java.util.TreeMap<String, TCustomSqlStatement> ordered =
                new java.util.TreeMap<String, TCustomSqlStatement>();
        for (RoutineCatalog.UnitIndex unit : foreignUnits) {
            for (RoutineCatalog.CallCandidate callCandidate : unit.callCandidates) {
                TCustomSqlStatement stmt = callCandidate.stmt;
                if (isPlsqlDefinitionShape(stmt)) {
                    continue;
                }
                if (isVettedLiteralCaller(stmt, idents)) {
                    String key = String.valueOf(stmt.toString());
                    if (!ordered.containsKey(key)) {
                        ordered.put(key, stmt);
                    }
                }
            }
        }
        int foreignAdded = 0;
        List<TCustomSqlStatement> foreignCallers = new ArrayList<TCustomSqlStatement>();
        for (TCustomSqlStatement stmt : ordered.values()) {
            if (foreignAdded >= maxForeignCallers) {
                break;
            }
            foreignCallers.add(stmt);
            foreignAdded++;
        }
        callers.addAll(foreignCallers);
        if (callers.isEmpty()) {
            // No vetted caller anywhere - interpretation would bind nothing.
            return result;
        }
        for (PlsqlRoutineIdent ident : idents) {
            result.add(ident.definition);
        }
        result.addAll(callers);
        return result;
    }

    /** Qualified identity of an own-file routine definition, plus its ambiguity verdicts. */
    private static final class PlsqlRoutineIdent {
        final TCustomSqlStatement definition;
        final String schema; // null when the definition name is unqualified
        final String simple;
        /** Some other file defines the same simple name AND it is not provably in a different schema. */
        boolean ambiguous;
        /** Some other file defines the same simple name at all (disqualifies unqualified calls). */
        boolean hasForeignSameName;

        PlsqlRoutineIdent(TCustomSqlStatement definition, String simple) {
            this.definition = definition;
            this.schema = plsqlRoutineSchema(definition);
            this.simple = simple;
        }
    }

    /** Schema segment of a routine definition name; null when unqualified. */
    private static String plsqlRoutineSchema(TCustomSqlStatement stmt) {
        gudusoft.gsqlparser.nodes.TObjectName name = null;
        if (stmt instanceof gudusoft.gsqlparser.stmt.oracle.TPlsqlCreateProcedure) {
            name = ((gudusoft.gsqlparser.stmt.oracle.TPlsqlCreateProcedure) stmt).getProcedureName();
        } else if (stmt instanceof gudusoft.gsqlparser.stmt.oracle.TPlsqlCreateFunction) {
            name = ((gudusoft.gsqlparser.stmt.oracle.TPlsqlCreateFunction) stmt).getFunctionName();
        }
        if (name == null) {
            return null;
        }
        List<String> parts = SQLUtil.parseNames(name.toString());
        return parts.size() >= 2 ? parts.get(parts.size() - 2) : null;
    }

    /**
     * True only when the two schema contexts are BOTH known and differ; a null on
     * either side could be the same schema (the conservative direction for the
     * ambiguity verdict).
     */
    private static boolean provablyDifferentSchema(String schemaA, String schemaB) {
        if (schemaA == null || schemaB == null) {
            return false;
        }
        return !SQLUtil.sameName(EDbVendor.dbvoracle, ESQLDataObjectType.dotSchema, schemaA, schemaB);
    }

    /**
     * Definition shapes that must never enter the interpretation list. Anonymous
     * blocks (TCommonBlock) ARE TStoredProcedureSqlStatement subclasses and are the
     * primary caller shape, so this names the definition classes explicitly.
     */
    private static boolean isPlsqlDefinitionShape(TCustomSqlStatement stmt) {
        return stmt instanceof gudusoft.gsqlparser.stmt.oracle.TPlsqlCreatePackage
                || stmt instanceof gudusoft.gsqlparser.stmt.oracle.TPlsqlCreateTrigger
                || stmt instanceof gudusoft.gsqlparser.stmt.oracle.TPlsqlCreateType;
    }

    /**
     * True when {@code stmt} contains at least one call that provably targets one of
     * {@code idents} and EVERY name-colliding call in the statement (a) resolves to
     * exactly one non-ambiguous identity - unqualified calls match by simple name,
     * two-part calls require the definition's schema to match, anything the shape
     * rules cannot prove is rejected because the interpreter may still bind it by
     * simple name - and (b) passes only compile-time literal arguments. A call fed
     * by a variable or expression is rejected wholesale: the interpreter would fold
     * the unevaluated operand to an EMPTY string - not a placeholder - which can
     * silently produce wrong object names (e.g. 'x' || p_suffix folding to just
     * "x"). The mssql discovery applies the same literal-only discipline.
     */
    private static boolean isVettedLiteralCaller(TCustomSqlStatement stmt,
            List<PlsqlRoutineIdent> idents) {
        final boolean[] found = { false };
        final boolean[] rejected = { false };
        final List<PlsqlRoutineIdent> targets = idents;
        try {
            stmt.acceptChildren(new gudusoft.gsqlparser.nodes.TParseTreeVisitor() {
                public void preVisit(gudusoft.gsqlparser.nodes.TFunctionCall call) {
                    vetCall(call);
                }

                public void preVisit(gudusoft.gsqlparser.stmt.TCallStatement call) {
                    // TCallStatement.acceptChildren does not surface its routine
                    // expression as a TFunctionCall - vet it explicitly, else
                    // CALL p('src') callers are silently never harvested.
                    if (call.getRoutineExpr() != null) {
                        vetCall(call.getRoutineExpr().getFunctionCall());
                    }
                }

                private void vetCall(gudusoft.gsqlparser.nodes.TFunctionCall call) {
                    if (call == null || call.getFunctionName() == null) {
                        return;
                    }
                    List<String> parts = SQLUtil.parseNames(call.getFunctionName().toString());
                    if (parts.isEmpty()) {
                        return;
                    }
                    String simple = parts.get(parts.size() - 1);
                    PlsqlRoutineIdent matched = null;
                    boolean collides = false;
                    boolean multi = false;
                    for (PlsqlRoutineIdent ident : targets) {
                        if (!SQLUtil.sameName(EDbVendor.dbvoracle, ESQLDataObjectType.dotProcedure,
                                simple, ident.simple)) {
                            continue;
                        }
                        collides = true;
                        boolean resolves;
                        if (parts.size() == 1) {
                            // An unqualified call resolves in the CALLER's (unknown)
                            // current schema: any same-named definition elsewhere -
                            // even in a provably different schema - leaves two
                            // candidates.
                            resolves = !ident.hasForeignSameName && !ident.ambiguous;
                        } else if (parts.size() == 2) {
                            resolves = !ident.ambiguous && ident.schema != null
                                    && SQLUtil.sameName(EDbVendor.dbvoracle, ESQLDataObjectType.dotSchema,
                                            parts.get(0), ident.schema);
                        } else {
                            resolves = false;
                        }
                        if (resolves) {
                            if (matched != null) {
                                multi = true;
                            }
                            matched = ident;
                        }
                    }
                    if (!collides) {
                        return;
                    }
                    if (matched == null || multi) {
                        rejected[0] = true;
                        return;
                    }
                    if (call.getArgs() == null || call.getArgs().size() == 0) {
                        found[0] = true;
                        return;
                    }
                    for (int i = 0; i < call.getArgs().size(); i++) {
                        if (!isCompileTimeLiteral(call.getArgs().getExpression(i))) {
                            rejected[0] = true;
                            return;
                        }
                    }
                    found[0] = true;
                }
            });
        } catch (RuntimeException ex) {
            return false;
        }
        return found[0] && !rejected[0];
    }

    /**
     * F1 guard: true when every partial/unfolded EXECUTE IMMEDIATE inside
     * {@code routine} builds its SQL exclusively from compile-time literals,
     * routine parameters, and local variables whose every assignment (and declared
     * default) is itself so composed, connected by || concatenation. Any other
     * dependency - SELECT/FETCH/EXECUTE..INTO targets, function results, variables
     * passed to other calls (possible OUT), nested routine declarations (scope
     * conflation) - disqualifies the whole routine: the interpreter folds what it
     * cannot evaluate to an EMPTY string, which would fabricate concrete-looking
     * but wrong SQL that the placeholder classifier cannot detect.
     */
    private static boolean plsqlDynamicSqlLiteralBindable(TCustomSqlStatement routine) {
        PlsqlBindContext ctx = buildPlsqlBindContext(routine);
        if (ctx == null || ctx.unbindable) {
            return false;
        }
        for (gudusoft.gsqlparser.nodes.TExpression expr : ctx.partialDynamicExprs) {
            if (!isLiteralBindableExpr(expr, ctx.params, ctx.assignments, ctx.disallowed,
                    new java.util.HashSet<String>())) {
                return false;
            }
        }
        return true;
    }

    /**
     * Binding-relevant facts collected from one routine's body, shared between the
     * EXECUTE IMMEDIATE bindability gate and the DBMS_SQL text fold: declared
     * parameters, tracked single-variable assignments, names disqualified by
     * untrackable writes, and the dynamic-string expressions of partial/unfolded
     * EXECUTE IMMEDIATE sites.
     */
    static final class PlsqlBindContext {
        final java.util.Set<String> params = new java.util.HashSet<String>();
        final Map<String, List<BoundSource>> assignments =
                new HashMap<String, List<BoundSource>>();
        final java.util.Set<String> disallowed = new java.util.HashSet<String>();
        final List<gudusoft.gsqlparser.nodes.TExpression> partialDynamicExprs =
                new ArrayList<gudusoft.gsqlparser.nodes.TExpression>();
        /** Nested routine declarations (scope conflation) or a partial site with no expression. */
        boolean unbindable;
    }

    /** One tracked assignment source with its source position (for flow-order checks). */
    static final class BoundSource {
        final gudusoft.gsqlparser.nodes.TExpression expr;
        final long line;
        final long col;

        BoundSource(gudusoft.gsqlparser.nodes.TExpression expr, long line, long col) {
            this.expr = expr;
            this.line = line;
            this.col = col;
        }
    }

    /** Collector behind {@link PlsqlBindContext}; null when the walk crashed. */
    static PlsqlBindContext buildPlsqlBindContext(TCustomSqlStatement routine) {
        try {
            final PlsqlBindContext ctx = new PlsqlBindContext();
            final java.util.Set<String> params = ctx.params;
            if (routine instanceof gudusoft.gsqlparser.stmt.TStoredProcedureSqlStatement) {
                TParameterDeclarationList declared = ((gudusoft.gsqlparser.stmt.TStoredProcedureSqlStatement) routine)
                        .getParameterDeclarations();
                if (declared != null) {
                    for (int i = 0; i < declared.size(); i++) {
                        TParameterDeclaration pd = declared.getParameterDeclarationItem(i);
                        if (pd != null && pd.getParameterName() != null) {
                            params.add(canonVarKey(pd.getParameterName().toString()));
                        }
                    }
                }
            }
            final Map<String, List<BoundSource>> assignments = ctx.assignments;
            final java.util.Set<String> disallowed = ctx.disallowed;
            final List<gudusoft.gsqlparser.nodes.TExpression> dynamicExprs = ctx.partialDynamicExprs;
            final boolean[] nestedRoutine = { false };
            final TCustomSqlStatement root = routine;
            routine.acceptChildren(new gudusoft.gsqlparser.nodes.TParseTreeVisitor() {
                public void preVisit(gudusoft.gsqlparser.stmt.TAssignStmt stmt) {
                    if (stmt.getLeft() == null || stmt.getExpression() == null) {
                        return;
                    }
                    String left = stmt.getLeft().toString();
                    if (left == null || left.indexOf('(') >= 0
                            || SQLUtil.parseNames(left).size() != 1) {
                        return; // record fields / collection elements are not tracked
                    }
                    recordAssignment(canonVarKey(left), stmt.getExpression(),
                            stmt.getStartToken() != null ? stmt.getStartToken()
                                    : stmt.getExpression().getStartToken());
                }

                public void preVisit(gudusoft.gsqlparser.stmt.TVarDeclStmt stmt) {
                    if (stmt.getElementName() == null) {
                        return;
                    }
                    if (stmt.getDefaultValue() != null) {
                        recordAssignment(canonVarKey(stmt.getElementName().toString()),
                                stmt.getDefaultValue(), stmt.getStartToken());
                    }
                }

                private void recordAssignment(String key,
                        gudusoft.gsqlparser.nodes.TExpression source,
                        gudusoft.gsqlparser.TSourceToken position) {
                    List<BoundSource> list = assignments.get(key);
                    if (list == null) {
                        list = new ArrayList<BoundSource>();
                        assignments.put(key, list);
                    }
                    // Unknown position sorts AFTER everything (Long.MAX_VALUE): a source
                    // whose location cannot be established never counts as preceding a
                    // use site - the conservative direction for the flow-order check.
                    list.add(new BoundSource(source,
                            position != null ? position.lineNo : Long.MAX_VALUE,
                            position != null ? position.columnNo : Long.MAX_VALUE));
                }

                public void preVisit(gudusoft.gsqlparser.stmt.TSelectSqlStatement stmt) {
                    if (stmt.getIntoClause() != null && stmt.getIntoClause().getExprList() != null) {
                        for (int i = 0; i < stmt.getIntoClause().getExprList().size(); i++) {
                            disallowExpr(stmt.getIntoClause().getExprList().getExpression(i));
                        }
                    }
                }

                public void preVisit(gudusoft.gsqlparser.stmt.TFetchStmt stmt) {
                    if (stmt.getVariableNames() != null) {
                        for (int i = 0; i < stmt.getVariableNames().size(); i++) {
                            disallowExpr(stmt.getVariableNames().getExpression(i));
                        }
                    }
                }

                public void preVisit(gudusoft.gsqlparser.stmt.TExecImmeStmt stmt) {
                    if (stmt.getIntoVariables() != null) {
                        for (int i = 0; i < stmt.getIntoVariables().size(); i++) {
                            disallowExpr(stmt.getIntoVariables().getExpression(i));
                        }
                    }
                    // USING bind values never rewrite the SQL text, but OUT binds
                    // write variables - disallow every bind target conservatively.
                    if (stmt.getBindArguments() != null) {
                        for (int i = 0; i < stmt.getBindArguments().size(); i++) {
                            gudusoft.gsqlparser.nodes.TBindArgument bind =
                                    stmt.getBindArguments().getBindArgument(i);
                            if (bind != null && bind.getBindArgumentExpr() != null) {
                                disallowExpr(bind.getBindArgumentExpr());
                            }
                        }
                    }
                    if (stmt.getDynamicSQL() == null || stmt.isDynamicSQLPartial()) {
                        if (stmt.getDynamicStringExpr() != null) {
                            dynamicExprs.add(stmt.getDynamicStringExpr());
                        } else {
                            nestedRoutine[0] = true; // unfoldable site with no expression - bail
                        }
                    }
                }

                public void preVisit(gudusoft.gsqlparser.nodes.TFunctionCall call) {
                    disallowBareArgs(call);
                }

                public void preVisit(gudusoft.gsqlparser.stmt.TCallStatement stmt) {
                    // TCallStatement.acceptChildren does not surface its routine
                    // expression as a TFunctionCall - inspect its args explicitly.
                    if (stmt.getRoutineExpr() != null) {
                        disallowBareArgs(stmt.getRoutineExpr().getFunctionCall());
                    }
                }

                public void preVisit(gudusoft.gsqlparser.nodes.TReturningClause clause) {
                    // RETURNING ... INTO writes locals, and the clause's own
                    // acceptChildren does not visit its variable list - handle it
                    // here or a literal-initialized local silently keeps its stale
                    // literal after being overwritten.
                    if (clause.getVariableList() != null) {
                        for (int i = 0; i < clause.getVariableList().size(); i++) {
                            disallowExpr(clause.getVariableList().getExpression(i));
                        }
                    }
                }

                private void disallowBareArgs(gudusoft.gsqlparser.nodes.TFunctionCall call) {
                    // A bare variable handed to any call could be an OUT actual -
                    // except at argument positions of KNOWN in-only builtins
                    // (notably DBMS_SQL.PARSE's own statement argument, which
                    // would otherwise disqualify the very variable it consumes).
                    if (call == null || call.getArgs() == null) {
                        return;
                    }
                    for (int i = 0; i < call.getArgs().size(); i++) {
                        if (isKnownInOnlyArg(call, i)) {
                            continue;
                        }
                        gudusoft.gsqlparser.nodes.TExpression arg = call.getArgs().getExpression(i);
                        if (arg != null && arg.getExpressionType()
                                == gudusoft.gsqlparser.EExpressionType.simple_object_name_t) {
                            disallowExpr(arg);
                        }
                    }
                }

                public void preVisit(gudusoft.gsqlparser.stmt.oracle.TPlsqlCreateProcedure stmt) {
                    if (stmt != root) {
                        nestedRoutine[0] = true; // nested scopes would conflate name keys
                    }
                }

                public void preVisit(gudusoft.gsqlparser.stmt.oracle.TPlsqlCreateFunction stmt) {
                    if (stmt != root) {
                        nestedRoutine[0] = true;
                    }
                }

                private void disallowExpr(gudusoft.gsqlparser.nodes.TExpression expr) {
                    if (expr == null) {
                        return;
                    }
                    String text = expr.toString();
                    if (text != null && SQLUtil.parseNames(text).size() == 1
                            && text.indexOf('(') < 0) {
                        disallowed.add(canonVarKey(text));
                    }
                }
            });
            ctx.unbindable = nestedRoutine[0];
            return ctx;
        } catch (RuntimeException ex) {
            return null;
        }
    }

    /**
     * True when argument {@code index} of {@code call} is an IN-only position of a
     * known builtin, so a bare variable there is a READ and must not disqualify the
     * variable from literal folding. Deliberately tiny: DBMS_SQL.PARSE's statement
     * argument (index 1 positional; named association arrives as an arrow
     * expression and is never a bare name anyway) and DBMS_OUTPUT.PUT_LINE / PUT
     * (pure logging, all-IN) — the calls that most commonly surround dynamic SQL.
     */
    private static boolean isKnownInOnlyArg(gudusoft.gsqlparser.nodes.TFunctionCall call, int index) {
        if (call.getFunctionName() == null) {
            return false;
        }
        String name = call.getFunctionName().toString();
        if (SQLUtil.compareIdentifier(EDbVendor.dbvoracle, ESQLDataObjectType.dotProcedure,
                name, "DBMS_SQL.PARSE")
                || SQLUtil.compareIdentifier(EDbVendor.dbvoracle, ESQLDataObjectType.dotProcedure,
                        name, "SYS.DBMS_SQL.PARSE")) {
            return index == 1;
        }
        return SQLUtil.compareIdentifier(EDbVendor.dbvoracle, ESQLDataObjectType.dotProcedure,
                name, "DBMS_OUTPUT.PUT_LINE")
                || SQLUtil.compareIdentifier(EDbVendor.dbvoracle, ESQLDataObjectType.dotProcedure,
                        name, "DBMS_OUTPUT.PUT");
    }

    /**
     * DBMS_SQL entry point (plan Phase 5 slice 4): fold the SQL-text argument of a
     * {@code DBMS_SQL.PARSE} call into concrete statement texts. Returns the
     * no-binding fold first (literal or literal-composed locals), then one fold per
     * literal call-site binding set (same-file callers first, then cross-file via
     * the catalog), deduplicated, capped at {@code maxVariants}. Empty when nothing
     * folds — the site then keeps its honest unresolved diagnostic. The fold shares
     * the EXECUTE IMMEDIATE whitelist (literals, parameters bound at vetted call
     * sites, locals with exactly one literal-composed assignment, || and
     * parentheses), so a fold can never fabricate object names.
     */
    public static List<String> foldDbmsSqlTexts(TCustomSqlStatement enclosingRoutine,
            gudusoft.gsqlparser.nodes.TExpression sqlArg, RoutineCatalog catalog, int maxVariants) {
        List<String> texts = new ArrayList<String>();
        if (sqlArg == null || maxVariants <= 0) {
            return texts;
        }
        PlsqlBindContext ctx = enclosingRoutine == null ? new PlsqlBindContext()
                : buildPlsqlBindContext(enclosingRoutine);
        if (ctx == null || ctx.unbindable) {
            // Context unavailable: only a directly-literal argument can fold.
            ctx = new PlsqlBindContext();
        }
        // Flow-order bound: only assignments strictly before the PARSE site count.
        // An unknown site position folds no variables at all (literals still do).
        long siteLine = Long.MIN_VALUE;
        long siteCol = Long.MIN_VALUE;
        if (sqlArg.getStartToken() != null) {
            siteLine = sqlArg.getStartToken().lineNo;
            siteCol = sqlArg.getStartToken().columnNo;
        }
        String direct = foldLiteralBindableExpr(sqlArg, ctx,
                new HashMap<String, SqlValue>(), new java.util.HashSet<String>(),
                siteLine, siteCol);
        if (direct != null && !direct.trim().isEmpty()) {
            texts.add(direct);
        }
        if (texts.size() < maxVariants && enclosingRoutine != null) {
            for (Map<String, SqlValue> bindings : discoverPlsqlCallSiteBindings(
                    enclosingRoutine, catalog, maxVariants)) {
                String folded = foldLiteralBindableExpr(sqlArg, ctx, bindings,
                        new java.util.HashSet<String>(), siteLine, siteCol);
                if (folded != null && !folded.trim().isEmpty() && !texts.contains(folded)) {
                    texts.add(folded);
                    if (texts.size() >= maxVariants) {
                        break;
                    }
                }
            }
        }
        return texts;
    }

    /**
     * Fold an expression to its concrete string under the P5-2 whitelist: literals,
     * parameters WITH a supplied binding (a NULL binding folds to the empty string,
     * Oracle's NULL-concatenation semantics), locals with exactly one
     * literal-composed assignment, || and parentheses. Null when anything outside
     * the whitelist (or an unbound parameter) is involved — never a partial text.
     *
     * <p>Flow order: a local's single assignment is usable only when it appears
     * strictly BEFORE ({@code boundLine},{@code boundCol}) — the use site on the
     * first level, then the consuming assignment's own position as the recursion
     * descends — so a value assigned after the site can never fabricate lineage.
     */
    private static String foldLiteralBindableExpr(gudusoft.gsqlparser.nodes.TExpression expr,
            PlsqlBindContext ctx, Map<String, SqlValue> bindings, java.util.Set<String> visiting,
            long boundLine, long boundCol) {
        if (expr == null) {
            return null;
        }
        if (isCompileTimeLiteral(expr)) {
            SqlValue value = literalToSqlValue(expr);
            if (value == null) {
                return null;
            }
            return value.isNull() ? "" : String.valueOf(value.text());
        }
        gudusoft.gsqlparser.EExpressionType type = expr.getExpressionType();
        if (type == gudusoft.gsqlparser.EExpressionType.concatenate_t) {
            String left = foldLiteralBindableExpr(expr.getLeftOperand(), ctx, bindings, visiting,
                    boundLine, boundCol);
            String right = foldLiteralBindableExpr(expr.getRightOperand(), ctx, bindings, visiting,
                    boundLine, boundCol);
            return left == null || right == null ? null : left + right;
        }
        if (type == gudusoft.gsqlparser.EExpressionType.parenthesis_t) {
            return foldLiteralBindableExpr(expr.getLeftOperand(), ctx, bindings, visiting,
                    boundLine, boundCol);
        }
        if (type == gudusoft.gsqlparser.EExpressionType.simple_object_name_t) {
            String text = expr.toString();
            if (text == null || SQLUtil.parseNames(text).size() != 1) {
                return null;
            }
            String key = canonVarKey(text);
            if (ctx.disallowed.contains(key)) {
                return null;
            }
            if (ctx.params.contains(key)) {
                SqlValue bound = bindings.get(key);
                if (bound == null) {
                    return null; // unbound parameter - never fold partially
                }
                return bound.isNull() ? "" : String.valueOf(bound.text());
            }
            List<BoundSource> sources = ctx.assignments.get(key);
            if (sources == null || sources.size() != 1 || !visiting.add(key)) {
                return null;
            }
            try {
                BoundSource source = sources.get(0);
                if (source.line > boundLine
                        || (source.line == boundLine && source.col >= boundCol)) {
                    return null; // assigned at or after the use site - value unavailable
                }
                return foldLiteralBindableExpr(source.expr, ctx, bindings, visiting,
                        source.line, source.col);
            } finally {
                visiting.remove(key);
            }
        }
        return null;
    }

    /**
     * Literal call-site binding sets for an Oracle routine, mirroring the T-SQL
     * {@link #discoverCallSiteBindings} semantics: same-file callers first (file
     * order), then cross-file callers via the catalog (exact-text order); vetted by
     * {@link #isVettedLiteralCaller} (so only positional, compile-time-literal
     * arguments to a provably-unambiguous target survive); one binding map per
     * matching call, deduplicated by canonical key, capped.
     */
    static List<Map<String, SqlValue>> discoverPlsqlCallSiteBindings(
            TCustomSqlStatement procAst, RoutineCatalog catalog, int maxBindingSets) {
        List<Map<String, SqlValue>> bindingSets = new ArrayList<Map<String, SqlValue>>();
        if (maxBindingSets <= 0 || procAst == null || procAst.getGsqlparser() == null) {
            return bindingSets;
        }
        String simple = plsqlRoutineSimpleName(procAst);
        if (simple == null || simple.isEmpty()) {
            return bindingSets;
        }
        final PlsqlRoutineIdent ident = new PlsqlRoutineIdent(procAst, simple);
        TStatementList ownStatements = procAst.getGsqlparser().getSqlstatements();
        String ownText = procAst.getGsqlparser().sqltext;
        // Same-FILE duplicate definitions make the name just as ambiguous as foreign
        // ones (the interpretation path catches this via its multi-ident matching;
        // this single-ident path must check explicitly).
        if (ownStatements != null) {
            for (int i = 0; i < ownStatements.size(); i++) {
                TCustomSqlStatement stmt = ownStatements.get(i);
                if (stmt == procAst) {
                    continue;
                }
                String otherSimple = plsqlRoutineSimpleName(stmt);
                if (otherSimple == null || !SQLUtil.sameName(EDbVendor.dbvoracle,
                        ESQLDataObjectType.dotProcedure, otherSimple, ident.simple)) {
                    continue;
                }
                ident.hasForeignSameName = true;
                if (!provablyDifferentSchema(ident.schema, plsqlRoutineSchema(stmt))) {
                    ident.ambiguous = true;
                }
            }
        }
        // Foreign definitions decide the ambiguity verdicts (same rules as the
        // interpretation path).
        List<RoutineCatalog.UnitIndex> foreignUnits = new ArrayList<RoutineCatalog.UnitIndex>();
        if (catalog != null) {
            String probe = bareName(simple).toLowerCase(java.util.Locale.ENGLISH);
            for (String text : catalog.unitTexts()) {
                if (text == null || text.equals(ownText)
                        || !text.toLowerCase(java.util.Locale.ENGLISH).contains(probe)) {
                    continue;
                }
                RoutineCatalog.UnitIndex unit = catalog.indexOf(text);
                if (unit == null) {
                    continue;
                }
                foreignUnits.add(unit);
                for (RoutineCatalog.Definition def : unit.definitions) {
                    if (!SQLUtil.sameName(EDbVendor.dbvoracle, ESQLDataObjectType.dotProcedure,
                            def.simple, ident.simple)) {
                        continue;
                    }
                    ident.hasForeignSameName = true;
                    if (!provablyDifferentSchema(ident.schema, def.schema)) {
                        ident.ambiguous = true;
                    }
                }
            }
        }
        List<PlsqlRoutineIdent> idents = new ArrayList<PlsqlRoutineIdent>();
        idents.add(ident);
        List<TParameterDeclaration> params = new ArrayList<TParameterDeclaration>();
        if (procAst instanceof gudusoft.gsqlparser.stmt.TStoredProcedureSqlStatement) {
            TParameterDeclarationList declared = ((gudusoft.gsqlparser.stmt.TStoredProcedureSqlStatement) procAst)
                    .getParameterDeclarations();
            if (declared != null) {
                for (int i = 0; i < declared.size(); i++) {
                    params.add(declared.getParameterDeclarationItem(i));
                }
            }
        }
        java.util.Set<String> seen = new java.util.HashSet<String>();
        // Same-file callers in file order.
        if (ownStatements != null) {
            for (int i = 0; i < ownStatements.size()
                    && bindingSets.size() < maxBindingSets; i++) {
                TCustomSqlStatement stmt = ownStatements.get(i);
                if (stmt == procAst || plsqlRoutineSimpleName(stmt) != null
                        || isPlsqlDefinitionShape(stmt)) {
                    continue;
                }
                if (isVettedLiteralCaller(stmt, idents)) {
                    extractPlsqlBindings(stmt, ident, params, seen, bindingSets, maxBindingSets);
                }
            }
        }
        // Cross-file callers ordered by exact statement text.
        java.util.TreeMap<String, TCustomSqlStatement> ordered =
                new java.util.TreeMap<String, TCustomSqlStatement>();
        for (RoutineCatalog.UnitIndex unit : foreignUnits) {
            for (RoutineCatalog.CallCandidate candidate : unit.callCandidates) {
                if (isPlsqlDefinitionShape(candidate.stmt)) {
                    continue;
                }
                if (isVettedLiteralCaller(candidate.stmt, idents)) {
                    String key = String.valueOf(candidate.stmt.toString());
                    if (!ordered.containsKey(key)) {
                        ordered.put(key, candidate.stmt);
                    }
                }
            }
        }
        for (TCustomSqlStatement stmt : ordered.values()) {
            if (bindingSets.size() >= maxBindingSets) {
                break;
            }
            extractPlsqlBindings(stmt, ident, params, seen, bindingSets, maxBindingSets);
        }
        return bindingSets;
    }

    /**
     * Extract one binding map per call to {@code ident} inside a VETTED caller
     * statement. Vetting already guaranteed every matching call is unambiguous and
     * all-literal (positional form — named notation is rejected there), so this
     * maps arguments to parameter declarations by position.
     */
    private static void extractPlsqlBindings(TCustomSqlStatement callerStmt,
            final PlsqlRoutineIdent ident, final List<TParameterDeclaration> params,
            final java.util.Set<String> seen, final List<Map<String, SqlValue>> bindingSets,
            final int maxBindingSets) {
        try {
            callerStmt.acceptChildren(new gudusoft.gsqlparser.nodes.TParseTreeVisitor() {
                public void preVisit(gudusoft.gsqlparser.nodes.TFunctionCall call) {
                    collect(call);
                }

                public void preVisit(gudusoft.gsqlparser.stmt.TCallStatement call) {
                    if (call.getRoutineExpr() != null) {
                        collect(call.getRoutineExpr().getFunctionCall());
                    }
                }

                private void collect(gudusoft.gsqlparser.nodes.TFunctionCall call) {
                    if (call == null || call.getFunctionName() == null
                            || bindingSets.size() >= maxBindingSets) {
                        return;
                    }
                    List<String> parts = SQLUtil.parseNames(call.getFunctionName().toString());
                    if (parts.isEmpty() || !SQLUtil.sameName(EDbVendor.dbvoracle,
                            ESQLDataObjectType.dotProcedure, parts.get(parts.size() - 1),
                            ident.simple)) {
                        return;
                    }
                    if (call.getArgs() == null || call.getArgs().size() == 0) {
                        return;
                    }
                    Map<String, SqlValue> bindings = new java.util.LinkedHashMap<String, SqlValue>();
                    java.util.TreeMap<String, String> canonical = new java.util.TreeMap<String, String>();
                    for (int i = 0; i < call.getArgs().size(); i++) {
                        if (i >= params.size() || params.get(i) == null
                                || params.get(i).getParameterName() == null) {
                            return; // arity beyond declaration - do not guess
                        }
                        SqlValue value = literalToSqlValue(call.getArgs().getExpression(i));
                        if (value == null) {
                            return; // vetting should prevent this; stay safe
                        }
                        String paramName = params.get(i).getParameterName().toString();
                        bindings.put(canonVarKey(paramName), value);
                        canonical.put(canonVarKey(paramName),
                                canonicalValueKey(call.getArgs().getExpression(i)));
                    }
                    if (!bindings.isEmpty() && seen.add(canonical.toString())) {
                        bindingSets.add(bindings);
                    }
                }
            });
        } catch (RuntimeException ex) {
            // best-effort extraction; a crashed walk contributes nothing
        }
    }

    /**
     * Variable-tracking key preserving Oracle quote semantics: unquoted names fold
     * upper (case-insensitive), quoted names keep their exact case inside markers so
     * a quoted lowercase local is never conflated with an unquoted parameter of the
     * same spelling. (Unquoted FOO vs quoted "FOO" key differently - a conservative
     * miss that can only disqualify, never wrongly qualify.)
     */
    private static String canonVarKey(String name) {
        String trimmed = name.trim();
        if (trimmed.length() >= 2 && trimmed.charAt(0) == '"'
                && trimmed.charAt(trimmed.length() - 1) == '"') {
            return "\"" + trimmed.substring(1, trimmed.length() - 1) + "\"";
        }
        return trimmed.toUpperCase(java.util.Locale.ENGLISH);
    }

    /**
     * Recursive whitelist for {@link #plsqlDynamicSqlLiteralBindable}: literals,
     * || concatenation, parentheses, routine parameters, and local variables whose
     * every recorded assignment is itself bindable. Cycles and everything else fail.
     */
    private static boolean isLiteralBindableExpr(gudusoft.gsqlparser.nodes.TExpression expr,
            java.util.Set<String> params,
            Map<String, List<BoundSource>> assignments,
            java.util.Set<String> disallowed, java.util.Set<String> visiting) {
        if (expr == null) {
            return false;
        }
        if (isCompileTimeLiteral(expr)) {
            return true;
        }
        gudusoft.gsqlparser.EExpressionType type = expr.getExpressionType();
        if (type == gudusoft.gsqlparser.EExpressionType.concatenate_t) {
            return isLiteralBindableExpr(expr.getLeftOperand(), params, assignments, disallowed, visiting)
                    && isLiteralBindableExpr(expr.getRightOperand(), params, assignments, disallowed, visiting);
        }
        if (type == gudusoft.gsqlparser.EExpressionType.parenthesis_t) {
            return isLiteralBindableExpr(expr.getLeftOperand(), params, assignments, disallowed, visiting);
        }
        if (type == gudusoft.gsqlparser.EExpressionType.simple_object_name_t) {
            String text = expr.toString();
            if (text == null || SQLUtil.parseNames(text).size() != 1) {
                return false;
            }
            String key = canonVarKey(text);
            if (disallowed.contains(key)) {
                return false;
            }
            if (params.contains(key)) {
                return true;
            }
            List<BoundSource> sources = assignments.get(key);
            // Exactly ONE recorded source (assignment or declared default): with
            // several, control flow (IF/loops) selects which one is live, but the
            // interpreter walks structurally and would collapse them to a single
            // arbitrary winner - presenting one possible lineage as certain. Real
            // branch-sensitive evaluation is Phase 3 CFG work.
            if (sources == null || sources.size() != 1 || !visiting.add(key)) {
                return false;
            }
            try {
                if (!isLiteralBindableExpr(sources.get(0).expr, params, assignments,
                        disallowed, visiting)) {
                    return false;
                }
            } finally {
                visiting.remove(key);
            }
            return true;
        }
        return false;
    }

    /** Compile-time literal: string/numeric constant, signed numeric, or bare NULL. */
    private static boolean isCompileTimeLiteral(gudusoft.gsqlparser.nodes.TExpression expr) {
        if (expr == null) {
            return false;
        }
        gudusoft.gsqlparser.EExpressionType type = expr.getExpressionType();
        if (type == gudusoft.gsqlparser.EExpressionType.unary_plus_t
                || type == gudusoft.gsqlparser.EExpressionType.unary_minus_t) {
            return isCompileTimeLiteral(expr.getRightOperand() != null
                    ? expr.getRightOperand() : expr.getLeftOperand());
        }
        if (type == gudusoft.gsqlparser.EExpressionType.simple_source_token_t
                && expr.toString().trim().equalsIgnoreCase("NULL")) { // non-identifier-compare: the NULL keyword literal
            return true;
        }
        return type == gudusoft.gsqlparser.EExpressionType.simple_constant_t
                && expr.getConstantOperand() != null;
    }

    /** Simple name of an Oracle routine definition; null for any other statement. */
    private static String plsqlRoutineSimpleName(TCustomSqlStatement stmt) {
        gudusoft.gsqlparser.nodes.TObjectName name = null;
        if (stmt instanceof gudusoft.gsqlparser.stmt.oracle.TPlsqlCreateProcedure) {
            name = ((gudusoft.gsqlparser.stmt.oracle.TPlsqlCreateProcedure) stmt).getProcedureName();
        } else if (stmt instanceof gudusoft.gsqlparser.stmt.oracle.TPlsqlCreateFunction) {
            name = ((gudusoft.gsqlparser.stmt.oracle.TPlsqlCreateFunction) stmt).getFunctionName();
        }
        if (name == null) {
            return null;
        }
        return lastNameSegment(name.toString());
    }

    // Package-visible accessors for RoutineCatalog (plan §5.6 index). Same helpers,
    // exposed under index-facing names; no behavior of their own.
    static String mssqlDefinitionDatabase(TMssqlCreateProcedure def, String currentDb) {
        return definitionDatabase(def, currentDb);
    }

    static String simpleNameOf(String name) {
        return lastNameSegment(name);
    }

    static String plsqlRoutineSimpleNameOf(TCustomSqlStatement stmt) {
        return plsqlRoutineSimpleName(stmt);
    }

    static String plsqlRoutineSchemaOf(TCustomSqlStatement stmt) {
        return plsqlRoutineSchema(stmt);
    }

    /** Effective database (last USE before the definition) of {@code procAst} in its file. */
    private static String effectiveDatabaseOfProc(TStatementList all, TCustomSqlStatement procAst) {
        String currentDb = null;
        for (int i = 0; i < all.size(); i++) {
            if (all.get(i) instanceof gudusoft.gsqlparser.stmt.TUseDatabase) {
                currentDb = String.valueOf(((gudusoft.gsqlparser.stmt.TUseDatabase) all.get(i)).getDatabaseName());
            }
            if (all.get(i) == procAst) {
                return currentDb;
            }
        }
        return null;
    }

    /**
     * True when {@code call} is a plain (non-dynamic-text) EXEC of {@code proc} under
     * the matching rules: unqualified operands match by simple name, qualified operands
     * require the full qualified identity (so EXEC audit.load is never taken as a call
     * to dbo.load), and one-/two-part names must agree on the effective USE database.
     */
    private static boolean callMatchesProc(gudusoft.gsqlparser.stmt.mssql.TMssqlExecute call,
            TMssqlCreateProcedure proc, String procSimpleName, String procDb, String callDb) {
        if (call.getModuleName() == null || call.getSqlText() != null) {
            return false;
        }
        String callName = call.getModuleName().toString();
        if (!sameEffectiveDatabase(procDb, callDb, callName)) {
            return false;
        }
        List<String> callParts = SQLUtil.parseNames(callName);
        if (callParts.size() <= 1) {
            return SQLUtil.sameName(EDbVendor.dbvmssql, ESQLDataObjectType.dotProcedure,
                    lastNameSegment(callName), procSimpleName);
        }
        List<String> procParts = SQLUtil.parseNames(proc.getProcedureName().toString());
        if (callParts.size() == procParts.size()) {
            return SQLUtil.compareIdentifier(EDbVendor.dbvmssql, ESQLDataObjectType.dotProcedure,
                    callName, proc.getProcedureName().toString());
        }
        if (callParts.size() == 2 && procParts.size() == 3) {
            // Two-part call to a three-part definition: database agreement was already
            // established by sameEffectiveDatabase (call's USE db vs the definition's
            // catalog); compare the remaining schema.name segments.
            return SQLUtil.sameName(EDbVendor.dbvmssql, ESQLDataObjectType.dotSchema,
                    callParts.get(0), procParts.get(1))
                    && SQLUtil.sameName(EDbVendor.dbvmssql, ESQLDataObjectType.dotProcedure,
                            callParts.get(1), procParts.get(2));
        }
        if (callParts.size() == 3 && procParts.size() == 2) {
            // Three-part call to a two-part definition: the call's explicit catalog
            // must equal the definition's known effective database.
            return procDb != null
                    && SQLUtil.sameName(EDbVendor.dbvmssql, ESQLDataObjectType.dotCatalog,
                            callParts.get(0), procDb)
                    && SQLUtil.sameName(EDbVendor.dbvmssql, ESQLDataObjectType.dotSchema,
                            callParts.get(1), procParts.get(0))
                    && SQLUtil.sameName(EDbVendor.dbvmssql, ESQLDataObjectType.dotProcedure,
                            callParts.get(2), procParts.get(1));
        }
        // Other arity combinations (e.g. one-part definition vs qualified call) keep
        // the conservative no-match: schema defaulting is environment-dependent.
        return false;
    }

    /** A literal binding set extracted from one call site plus its canonical dedupe key. */
    private static final class BindingCandidate {
        final Map<String, SqlValue> bindings;
        final String canonicalKey;

        BindingCandidate(Map<String, SqlValue> bindings, String canonicalKey) {
            this.bindings = bindings;
            this.canonicalKey = canonicalKey;
        }
    }

    /**
     * Convert one EXEC call's arguments into a binding set; null when nothing literal
     * was bound or the call passes a runtime value to a DEFAULTED formal (the declared
     * default would otherwise mask the unknown runtime value).
     */
    private static BindingCandidate bindingCandidateFromCall(
            gudusoft.gsqlparser.stmt.mssql.TMssqlExecute call, List<TParameterDeclaration> params) {
        Map<String, SqlValue> bindings = new java.util.LinkedHashMap<String, SqlValue>();
        java.util.TreeMap<String, String> canonical = new java.util.TreeMap<String, String>();
        if (call.getParameters() != null) {
            for (int j = 0; j < call.getParameters().size(); j++) {
                gudusoft.gsqlparser.nodes.TExecParameter arg = call.getParameters().getExecParameter(j);
                if (arg == null || arg.getParameterMode() != TBaseType.parameter_mode_default) {
                    // OUT/OUTPUT actuals stay unbound: the value at the dynamic
                    // site may differ from the literal passed in.
                    continue;
                }
                String paramName = null;
                if (arg.getParameterName() != null) {
                    paramName = arg.getParameterName().toString();
                } else if (j < params.size() && params.get(j).getParameterName() != null) {
                    paramName = params.get(j).getParameterName().toString();
                }
                SqlValue value = literalToSqlValue(arg.getParameterValue());
                if (paramName != null && value == null && hasLiteralDefault(params, paramName)) {
                    // Explicitly-supplied runtime value for a DEFAULTED formal: the
                    // evaluator would wrongly seed the declared default for it.
                    // This call site cannot be represented soundly - drop it.
                    return null;
                }
                if (paramName != null && value != null) {
                    bindings.put(paramName, value);
                    // Canonical dedupe key: normalized name -> typed escaped value,
                    // insensitive to argument order, @-prefix and case.
                    String normalized = paramName.startsWith("@") ? paramName.substring(1) : paramName;
                    canonical.put(normalized.toUpperCase(java.util.Locale.ENGLISH),
                            canonicalValueKey(arg.getParameterValue()));
                }
            }
        }
        if (bindings.isEmpty()) {
            return null;
        }
        return new BindingCandidate(bindings, canonical.toString());
    }

    /**
     * Database a definition belongs to: the catalog part of a three-part name wins,
     * otherwise the effective USE database at the definition site.
     */
    private static String definitionDatabase(TMssqlCreateProcedure def, String currentDb) {
        List<String> parts = SQLUtil.parseNames(def.getProcedureName().toString());
        if (parts.size() >= 3) {
            return parts.get(0);
        }
        return currentDb;
    }

    /**
     * True only when the two database contexts are BOTH known and differ. A null on
     * either side means "analysis default" - possibly the same database - so it is
     * NOT provably different (the conservative direction for the ambiguity bail-out).
     */
    private static boolean provablyDifferentDatabase(String dbA, String dbB) {
        if (dbA == null || dbB == null) {
            return false;
        }
        return !SQLUtil.sameName(EDbVendor.dbvmssql, ESQLDataObjectType.dotCatalog, dbA, dbB);
    }

    /** Name stripped of [..] / ".." / `..` quoting, for the cheap contains-prefilter only. */
    private static String bareName(String name) {
        String trimmed = name.trim();
        if (trimmed.length() >= 2) {
            char first = trimmed.charAt(0);
            char last = trimmed.charAt(trimmed.length() - 1);
            if ((first == '[' && last == ']') || (first == '"' && last == '"')
                    || (first == '`' && last == '`')) {
                return trimmed.substring(1, trimmed.length() - 1).trim();
            }
        }
        return trimmed;
    }


    /**
     * True when the call's effective database matches the procedure definition's. An
     * explicit three-part call name carries its own database and was already compared
     * as a full qualified identity; here we only reject cross-USE ambiguity for one-
     * and two-part names.
     */
    private static boolean sameEffectiveDatabase(String procDb, String callDb, String callName) {
        if (SQLUtil.parseNames(callName).size() >= 3) {
            return true;
        }
        if (procDb == null && callDb == null) {
            return true;
        }
        if (procDb == null || callDb == null) {
            return false;
        }
        return SQLUtil.sameName(EDbVendor.dbvmssql, ESQLDataObjectType.dotCatalog, procDb, callDb);
    }

    private static boolean hasLiteralDefault(List<TParameterDeclaration> params, String paramName) {
        for (TParameterDeclaration param : params) {
            if (param.getParameterName() != null
                    && SQLUtil.sameName(EDbVendor.dbvmssql, ESQLDataObjectType.dotColumn,
                            param.getParameterName().toString(), paramName)) {
                return param.getDefaultValue() != null;
            }
        }
        return false;
    }

    private static String lastNameSegment(String name) {
        List<String> parts = SQLUtil.parseNames(name);
        return parts.isEmpty() ? name : parts.get(parts.size() - 1);
    }

    /** T-SQL literal -> binding value; null when the expression is not a compile-time literal. */
    private static SqlValue literalToSqlValue(gudusoft.gsqlparser.nodes.TExpression expr) {
        if (expr == null) {
            return null;
        }
        gudusoft.gsqlparser.EExpressionType type = expr.getExpressionType();
        // Signed numerics arrive as unary +/- wrapping the constant.
        if (type == gudusoft.gsqlparser.EExpressionType.unary_plus_t
                || type == gudusoft.gsqlparser.EExpressionType.unary_minus_t) {
            SqlValue inner = literalToSqlValue(expr.getRightOperand() != null
                    ? expr.getRightOperand() : expr.getLeftOperand());
            if (inner == null || type == gudusoft.gsqlparser.EExpressionType.unary_plus_t) {
                return inner;
            }
            try {
                String innerText = inner.text() == null ? "" : inner.text();
                if (innerText.indexOf('.') >= 0) {
                    return SqlValue.of(-Double.parseDouble(innerText));
                }
                return SqlValue.of(-Long.parseLong(innerText));
            } catch (NumberFormatException nfe) {
                return null;
            }
        }
        // Bare NULL in an EXEC argument surfaces as a plain source token.
        if (type == gudusoft.gsqlparser.EExpressionType.simple_source_token_t
                && expr.toString().trim().equalsIgnoreCase("NULL")) { // non-identifier-compare: the NULL keyword literal
            return SqlValue.nullValue();
        }
        if (type != gudusoft.gsqlparser.EExpressionType.simple_constant_t
                || expr.getConstantOperand() == null) {
            return null;
        }
        String text = expr.getConstantOperand().toString().trim();
        if (text.equalsIgnoreCase("NULL")) { // non-identifier-compare: the NULL keyword literal
            return SqlValue.nullValue();
        }
        String unquoted = unquoteTsqlString(text);
        if (unquoted != null) {
            return SqlValue.of(unquoted);
        }
        try {
            if (text.indexOf('.') >= 0 || text.indexOf('e') >= 0 || text.indexOf('E') >= 0) {
                // Bind decimals only when the double round-trips to the exact source
                // text - otherwise the evaluated string could differ from the real
                // runtime value (precision loss); leave inexact values unbound.
                double parsed = Double.parseDouble(text);
                SqlValue candidate = SqlValue.of(parsed);
                if (text.equals(candidate.text())) {
                    return candidate;
                }
                return null;
            }
            return SqlValue.of(Long.parseLong(text));
        } catch (NumberFormatException nfe) {
            return null;
        }
    }

    /** 'abc''d' / N'abc' -> abc'd ; returns null when not a string literal. */
    private static String unquoteTsqlString(String text) {
        String t = text;
        if (t.length() >= 2 && (t.charAt(0) == 'N' || t.charAt(0) == 'n') && t.charAt(1) == '\'') {
            t = t.substring(1);
        }
        if (t.length() >= 2 && t.charAt(0) == '\'' && t.charAt(t.length() - 1) == '\'') {
            return t.substring(1, t.length() - 1).replace("''", "'");
        }
        return null;
    }

    /** Order/spelling-insensitive value key for binding-set dedupe (length-prefixed to defeat collisions). */
    private static String canonicalValueKey(gudusoft.gsqlparser.nodes.TExpression expr) {
        String raw = expr == null ? "" : expr.toString().trim();
        return raw.length() + ":" + raw;
    }

    /* --------------------------------------------------------- site building */

    private static DynamicSiteResult buildSite(TDynamicSqlStringEvaluator.RawSite raw, EDbVendor vendor,
            TSQLEnv sqlEnv, String currentDatabase, String defaultSchema, String sourceProc, String bindingHash,
            DynamicLineageOptions options) {

        long[] pos = coordinate(raw.node);
        String dynamicSite = dynamicSiteId(sourceProc,
                raw.ordinalPath + (raw.variantPath == null ? "" : raw.variantPath));
        SqlStringValue sql = raw.sql;
        // The EXEC site is wrapped in an IF whose predicate we cannot decide from
        // the bindings (e.g. IF @Debug = 0 EXEC(@sql) with @Debug unbound).
        boolean executionGated = !raw.controlFlowCertain;
        DynamicSqlTrustMode trustMode = options.getTrustMode();
        HoleFacts holes = holeFacts(sql);

        // Honest unresolved reasons — never guess.
        // Case (a): the dynamic string itself is not usable — NULL, opaque, or
        // assigned under an undecidable branch and collapsed to UNKNOWN. No
        // usable inner-SQL candidate exists for this site.
        if (sql == null || !sql.usable()) {
            String reason = sql != null && sql.reason != null ? sql.reason
                    : executionGated ? "site guarded by an undecidable conditional"
                            : "dynamic SQL did not reduce to a concrete string";
            return unresolved(raw, dynamicSite, sourceProc, bindingHash, pos, reason, trustMode, holes);
        }
        // Case (b): the EXEC site is execution-gated, but the string itself is usable
        // (CONCRETE or PARTIAL) — only the *did-it-run* fact is uncertain, so we
        // degrade-don't-fail rather than dropping the lineage. We fall through to
        // analyzeResolvedSql either way: a fully-concrete gated site emits all its
        // edges (flagged CONDITIONAL below), and a PARTIAL gated site emits exactly
        // the placeholder-free subset a non-gated PARTIAL site would, suppressing only
        // the placeholder edges. The downstream status/reason block surfaces both the
        // placeholder suppression and the execution gate. (Case (a) — an unusable
        // string, NULL/UNKNOWN — was already returned UNRESOLVED above.)
        String resolvedSql = sql.text;
        if (resolvedSql.length() > options.getMaxResolvedSqlLength()) {
            return unresolved(raw, dynamicSite, sourceProc, bindingHash, pos,
                    "resolved SQL length " + resolvedSql.length() + " exceeds maxResolvedSqlLength",
                    trustMode, holes);
        }

        String concreteHash = sha1Hex(resolvedSql);
        boolean partialPlaceholders = sql.state == SqlStringValue.State.PARTIAL;
        boolean provenanceIncomplete = !hasCompleteLiteralProvenance(sql.text, sql.fragments);

        List<DynamicLineageEdge> edges;
        boolean parseError;
        boolean partialParseError;
        boolean nestedIncomplete;
        try {
            Analysis a = analyzeResolvedSql(resolvedSql, vendor, sqlEnv, currentDatabase, defaultSchema,
                    sourceProc, dynamicSite, bindingHash, options, sql.fragments);
            edges = a.edges;
            nestedIncomplete = a.nestedIncomplete;
            // No lineage + recorded errors = parse failure (generateDataFlow()
            // does not throw on bad SQL). Errors WITH some lineage = a mixed batch
            // where one statement failed; keep the good edges but flag it honestly.
            parseError = a.hadErrors && a.edges.isEmpty();
            partialParseError = a.hadErrors && !a.edges.isEmpty();
        } catch (RuntimeException ex) {
            edges = new ArrayList<DynamicLineageEdge>();
            parseError = true;
            partialParseError = false;
            nestedIncomplete = false;
        }

        boolean aggregateIncomplete = provenanceIncomplete || nestedIncomplete
                || (partialParseError && trustMode == DynamicSqlTrustMode.SHADOW);
        HoleFacts aggregateHoles = nestedIncomplete ? new HoleFacts(-1, false) : holes;

        DynamicSiteResult.Status status;
        if (parseError) {
            status = DynamicSiteResult.Status.PARSE_ERROR;
        } else if (executionGated) {
            // The undecidable gate dominates the status: whether the string is fully
            // concrete or PARTIAL, every emitted edge is reached only if the guarded
            // branch runs. CONDITIONAL keeps that "might not execute" caveat visible —
            // the strictly-stronger signal to preserve — and the reason below adds the
            // placeholder-suppression note when the string is also PARTIAL.
            status = DynamicSiteResult.Status.CONDITIONAL;
        } else if (partialPlaceholders
                || (aggregateIncomplete && trustMode == DynamicSqlTrustMode.SHADOW)) {
            status = DynamicSiteResult.Status.PARTIAL;
        } else {
            status = DynamicSiteResult.Status.RESOLVED;
        }

        // Compose the reason from every applicable fact rather than picking one — a
        // single site can be partial-parse AND placeholder-suppressed AND gated at
        // once, and a consumer needs all three to gauge how far to trust the edges.
        List<String> reasons = new ArrayList<String>();
        if (parseError) {
            reasons.add("materialized SQL failed to parse");
        } else if (partialParseError) {
            reasons.add("some statements in the materialized SQL failed to parse; emitted edges are from the parseable statements");
        }
        if (partialPlaceholders) {
            reasons.add("materialized SQL still contains unbound placeholder(s); placeholder edges suppressed");
        }
        int unprovenRelationshipCount = 0;
        if (nestedIncomplete) {
            reasons.add("nested dynamic SQL is incomplete");
        }
        if (aggregateIncomplete && trustMode == DynamicSqlTrustMode.SHADOW) {
            unprovenRelationshipCount = edges.size();
            reasons.add("SHADOW retained " + unprovenRelationshipCount
                    + " historical candidate relationship(s) without a per-edge commitment proof; "
                    + holeDescription(aggregateHoles));
        }
        if (executionGated) {
            boolean anyEdges = !edges.isEmpty();
            reasons.add("site reached through an undecidable conditional"
                    + (anyEdges ? " — emitted edges assume the guarded statement executed"
                            : " — no resolvable edges were produced"));
        }
        String reason = reasons.isEmpty() ? null : String.join("; ", reasons);

        return new DynamicSiteResult(raw.kind, status, resolvedSql, concreteHash, sourceProc, dynamicSite,
                bindingHash, reason, pos[0], pos[1], edges, trustMode,
                unprovenRelationshipCount, aggregateHoles.count, aggregateHoles.exact,
                raw.diagnostic);
    }

    private static DynamicSiteResult unresolved(TDynamicSqlStringEvaluator.RawSite raw, String dynamicSite,
            String sourceProc, String bindingHash, long[] pos, String reason,
            DynamicSqlTrustMode trustMode, HoleFacts holes) {
        String text = raw.sql != null && raw.sql.usable() ? raw.sql.text : null;
        return new DynamicSiteResult(raw.kind, DynamicSiteResult.Status.UNRESOLVED, text,
                text != null ? sha1Hex(text) : null, sourceProc, dynamicSite, bindingHash, reason,
                pos[0], pos[1], new ArrayList<DynamicLineageEdge>(), trustMode,
                0, holes.count, holes.exact, raw.diagnostic);
    }

    private static final class HoleFacts {
        final int count;
        final boolean exact;

        HoleFacts(int count, boolean exact) {
            this.count = count;
            this.exact = exact;
        }
    }

    private static HoleFacts holeFacts(SqlStringValue sql) {
        if (sql == null) {
            return new HoleFacts(-1, false);
        }
        int count = 0;
        for (SqlFragment fragment : sql.fragments) {
            if (!fragment.isLiteral()) {
                count++;
            }
        }
        if (sql.usable() && sql.fragments.isEmpty()) {
            return new HoleFacts(-1, false);
        }
        if (!sql.usable() && count == 0) {
            // The value did not reduce, yet its provenance shows no hole: whatever
            // stopped the fold left no fragment. Not known - never an exact zero.
            return new HoleFacts(-1, false);
        }
        return new HoleFacts(count, true);
    }

    private static String holeDescription(HoleFacts holes) {
        return holes.exact ? holes.count + " unresolved fragment(s)"
                : "unresolved fragment count unavailable from the producer";
    }

    /** Complete means usable text with non-empty, exclusively literal provenance. */
    private static boolean hasCompleteLiteralProvenance(String text, List<SqlFragment> fragments) {
        if (text == null || fragments == null || fragments.isEmpty()) {
            return false;
        }
        StringBuilder rendered = new StringBuilder();
        for (SqlFragment fragment : fragments) {
            if (fragment == null || !fragment.isLiteral()) {
                return false;
            }
            rendered.append(fragment.text);
        }
        return rendered.toString().equals(text);
    }

    /**
     * Shared "analyze a materialized dynamic-SQL string" routine: run ordinary
     * dlineage with the proc's database/schema context and lift each column edge
     * into a provenance-tagged {@link DynamicLineageEdge}.
     */
    /** Edges from analyzing one materialized string, plus whether dlineage recorded parse/analysis errors. */
    private static final class Analysis {
        final List<DynamicLineageEdge> edges;
        final boolean hadErrors;
        final boolean nestedIncomplete;

        Analysis(List<DynamicLineageEdge> edges, boolean hadErrors, boolean nestedIncomplete) {
            this.edges = edges;
            this.hadErrors = hadErrors;
            this.nestedIncomplete = nestedIncomplete;
        }
    }

    private static Analysis analyzeResolvedSql(String resolvedSql, EDbVendor vendor,
            TSQLEnv sqlEnv, String currentDatabase, String defaultSchema, String sourceProc, String dynamicSite,
            String bindingHash, DynamicLineageOptions options,
            List<SqlFragment> valueProvenance) {

        Option opt = new Option();
        opt.setVendor(vendor);
        opt.setSimpleOutput(false);
        opt.setOutput(false);
        opt.setShowImplicitSchema(options.isShowImplicitSchema());
        opt.setDynamicSqlTrustMode(options.getTrustMode());
        if (currentDatabase != null && !currentDatabase.isEmpty()) {
            opt.setDefaultDatabase(currentDatabase);
        }
        if (defaultSchema != null && !defaultSchema.isEmpty()) {
            opt.setDefaultSchema(defaultSchema);
        }

        DataFlowAnalyzer nested = new DataFlowAnalyzer(resolvedSql, opt);
        if (sqlEnv != null) {
            nested.setSqlEnv(sqlEnv);
        }
        nested.generateDataFlow();
        dataflow df = nested.getDataFlow();
        boolean nestedIncomplete = hasIncompleteNestedDynamicSql(nested, options.getTrustMode());

        List<DynamicLineageEdge> edges = new ArrayList<DynamicLineageEdge>();
        if (df == null) {
            return new Analysis(edges, true, nestedIncomplete);
        }
        boolean hadErrors = df.getErrors() != null && !df.getErrors().isEmpty();
        if (df.getRelationships() == null) {
            return new Analysis(edges, hadErrors, nestedIncomplete);
        }

        // Index the real tables/views by id so we can rebuild fully-qualified
        // names from the structured model and fill in the proc's database/schema
        // for names the dynamic SQL left unqualified.
        Map<String, table> realTables = new HashMap<String, table>();
        indexTables(realTables, df.getTables());
        indexTables(realTables, df.getViews());

        boolean showImplicit = options.isShowImplicitSchema();
        for (relationship rel : df.getRelationships()) {
            if (rel == null || rel.getTarget() == null || rel.getSources() == null) {
                continue;
            }
            String target = fullName(rel.getTarget(), realTables, currentDatabase, defaultSchema, showImplicit);
            // An edge touching an unresolved placeholder (a name still carrying a
            // @variable from an unbound binding) must NOT be emitted as resolved
            // lineage — it would be a guess. The site stays PARTIAL and its
            // resolvedSql still shows the placeholder; only the fully-resolved
            // edges flow out.
            if (target == null || referencesPlaceholder(target)) {
                continue;
            }
            for (sourceColumn src : rel.getSources()) {
                if (isSystem(src)) {
                    continue;
                }
                String source = fullName(src, realTables, currentDatabase, defaultSchema, showImplicit);
                if (source == null || referencesPlaceholder(source)) {
                    continue;
                }
                edges.add(new DynamicLineageEdge(source, target, rel.getEffectType(), rel.getType(),
                        sourceProc, dynamicSite, bindingHash, valueProvenance));
            }
        }
        return new Analysis(edges, hadErrors, nestedIncomplete);
    }

    private static boolean hasIncompleteNestedDynamicSql(DataFlowAnalyzer analyzer,
            DynamicSqlTrustMode trustMode) {
        if (trustMode == DynamicSqlTrustMode.LEGACY) {
            return false;
        }
        for (DynamicSqlSite site : analyzer.getDynamicSqlSites()) {
            // A nested execution is part of the outer program. Surface its
            // incompleteness in SHADOW without changing the published model.
            if (site.getStatus() != DynamicSqlSite.Status.RESOLVED) {
                return true;
            }
        }
        return false;
    }

    /** A resolved object name never legitimately contains '@'; if it does, it still holds an unbound placeholder. */
    private static boolean referencesPlaceholder(String name) {
        return name != null && name.indexOf('@') >= 0;
    }

    /* ------------------------------------------------------------- helpers */

    private static void indexTables(Map<String, table> index, List<table> tables) {
        if (tables == null) {
            return;
        }
        for (table t : tables) {
            if (t != null && t.getId() != null) {
                index.put(t.getId(), t);
            }
        }
    }

    private static String fullName(targetColumn c, Map<String, table> realTables, String currentDatabase,
            String defaultSchema, boolean showImplicit) {
        if (c == null || "system".equals(c.getSource())) {
            return null;
        }
        return joinName(c.getParent_id(), c.getParent_name(), c.getColumn(), realTables, currentDatabase,
                defaultSchema, showImplicit);
    }

    private static String fullName(sourceColumn c, Map<String, table> realTables, String currentDatabase,
            String defaultSchema, boolean showImplicit) {
        return joinName(c.getParent_id(), c.getParent_name(), c.getColumn(), realTables, currentDatabase,
                defaultSchema, showImplicit);
    }

    private static boolean isSystem(sourceColumn c) {
        return c == null || "system".equals(c.getSource()) || "RelationRows".equals(c.getColumn());
    }

    private static String joinName(String parentId, String parentName, String column,
            Map<String, table> realTables, String currentDatabase, String defaultSchema, boolean showImplicit) {
        if (column == null) {
            return null;
        }
        return qualifiedParent(parentId, parentName, realTables, currentDatabase, defaultSchema, showImplicit)
                + "." + stripBrackets(column);
    }

    /**
     * Build the qualified parent name from the real-table model. dlineage may
     * return the name already qualified (db.schema.table), partly qualified
     * (schema.table), bare (table), or with an empty schema slot (db..table). We
     * normalize per-segment brackets positionally — last = table, then schema,
     * then database — and, only when {@code showImplicit} is on, fill a missing
     * schema/database from the proc's {@code defaultSchema}/{@code currentDatabase}
     * so an unqualified {@code RECON_JE_02_QSP_…} binds to
     * {@code JOURNALENGINE.dbo.RECON_JE_02_QSP_…}. Intermediate result sets
     * (no model table) keep their display name unchanged.
     */
    private static String qualifiedParent(String parentId, String parentName, Map<String, table> realTables,
            String currentDatabase, String defaultSchema, boolean showImplicit) {
        table t = parentId == null ? null : realTables.get(parentId);
        if (t == null) {
            return stripBrackets(parentName); // result set / function / intermediate node
        }
        String full = t.getFullName() != null ? t.getFullName() : t.getName();
        if (full == null || full.isEmpty()) {
            full = parentName;
        }
        if (full == null) {
            return stripBrackets(parentName);
        }

        // Keep empty segments so positions are preserved (e.g. db..table).
        String[] raw = full.split("\\.", -1);
        for (int i = 0; i < raw.length; i++) {
            raw[i] = stripBrackets(raw[i].trim());
        }
        int n = raw.length;
        String table = n >= 1 ? raw[n - 1] : stripBrackets(parentName);
        String schema = n >= 2 ? raw[n - 2] : "";
        String db = n >= 3 ? raw[n - 3] : "";
        // Anything left of the database segment (e.g. linked-server) is kept as a prefix.
        StringBuilder prefix = new StringBuilder();
        for (int i = 0; i < n - 3; i++) {
            if (!raw[i].isEmpty()) {
                prefix.append(raw[i]).append('.');
            }
        }
        if (showImplicit) {
            if (schema.isEmpty()) {
                schema = stripBrackets(defaultSchema);
            }
            if (db.isEmpty()) {
                db = stripBrackets(currentDatabase);
            }
        }
        StringBuilder sb = new StringBuilder(prefix.toString());
        if (db != null && !db.isEmpty()) {
            sb.append(db).append('.');
        }
        if (schema != null && !schema.isEmpty()) {
            sb.append(schema).append('.');
        }
        sb.append(table == null ? "" : table);
        return sb.length() == 0 ? stripBrackets(parentName) : sb.toString();
    }

    private static TStatementList extractBody(TCustomSqlStatement procAst) {
        if (procAst instanceof TMssqlCreateProcedure) {
            return ((TMssqlCreateProcedure) procAst).getBodyStatements();
        }
        return null;
    }

    private static List<TParameterDeclaration> extractParams(TCustomSqlStatement procAst) {
        List<TParameterDeclaration> out = new ArrayList<TParameterDeclaration>();
        if (procAst instanceof TMssqlCreateProcedure) {
            TParameterDeclarationList list = ((TMssqlCreateProcedure) procAst).getParameterDeclarations();
            if (list != null) {
                for (int i = 0; i < list.size(); i++) {
                    out.add(list.getParameterDeclarationItem(i));
                }
            }
        }
        return out;
    }

    private static String extractProcName(TCustomSqlStatement procAst) {
        if (procAst instanceof TMssqlCreateProcedure) {
            TMssqlCreateProcedure cp = (TMssqlCreateProcedure) procAst;
            if (cp.getProcedureName() != null) {
                return cp.getProcedureName().toString();
            }
        }
        return procAst == null ? "" : procAst.getClass().getSimpleName();
    }

    private static long[] coordinate(TCustomSqlStatement node) {
        try {
            TSourceToken t = node.getStartToken();
            if (t != null) {
                return new long[] { t.lineNo, t.columnNo };
            }
        } catch (Throwable ignore) {
            // fall through
        }
        return new long[] { 0L, 0L };
    }

    /** Stable id robust to reformatting: proc identity + structural ordinal path, hashed. */
    private static String dynamicSiteId(String sourceProc, String ordinalPath) {
        return "dyn:" + sha1Hex(stripBrackets(sourceProc) + "#" + ordinalPath).substring(0, 12);
    }

    private static String hashBindings(Map<String, SqlValue> bindings) {
        if (bindings == null || bindings.isEmpty()) {
            return sha1Hex("");
        }
        TreeMap<String, String> sorted = new TreeMap<String, String>();
        for (Map.Entry<String, SqlValue> e : bindings.entrySet()) {
            if (e.getKey() == null) {
                continue;
            }
            sorted.put(stripBrackets(e.getKey().trim()).toLowerCase(),
                    e.getValue() == null ? "NULL" : e.getValue().toString());
        }
        StringBuilder sb = new StringBuilder();
        for (Map.Entry<String, String> e : sorted.entrySet()) {
            sb.append(e.getKey()).append('=').append(e.getValue()).append(';');
        }
        return sha1Hex(sb.toString());
    }

    private static String stripBrackets(String s) {
        if (s == null) {
            return "";
        }
        s = s.trim();
        if (s.length() >= 2) {
            char a = s.charAt(0);
            char z = s.charAt(s.length() - 1);
            if ((a == '[' && z == ']') || (a == '"' && z == '"') || (a == '`' && z == '`')) {
                return s.substring(1, s.length() - 1);
            }
        }
        return s;
    }

    private static String sha1Hex(String input) {
        try {
            MessageDigest md = MessageDigest.getInstance("SHA-1");
            byte[] digest = md.digest(input.getBytes("UTF-8"));
            StringBuilder sb = new StringBuilder(digest.length * 2);
            for (byte b : digest) {
                sb.append(Character.forDigit((b >> 4) & 0xF, 16));
                sb.append(Character.forDigit(b & 0xF, 16));
            }
            return sb.toString();
        } catch (NoSuchAlgorithmException | UnsupportedEncodingException e) {
            return Integer.toHexString(input.hashCode());
        }
    }
}