package gudusoft.gsqlparser.resolver2.metadata;

import gudusoft.gsqlparser.*;
import gudusoft.gsqlparser.nodes.*;
import gudusoft.gsqlparser.resolver.TMetadataCollector;
import gudusoft.gsqlparser.resolver2.context.DatabaseContextTracker;
import gudusoft.gsqlparser.resolver2.model.QualifiedNameResolver;
import gudusoft.gsqlparser.resolver2.namespace.TableNamespace;
import gudusoft.gsqlparser.sqlenv.*;
import gudusoft.gsqlparser.stmt.TCallStatement;
import gudusoft.gsqlparser.stmt.TCreateFunctionStmt;
import gudusoft.gsqlparser.stmt.TCreateTableSqlStatement;
import gudusoft.gsqlparser.stmt.TDropTableSqlStatement;
import gudusoft.gsqlparser.stmt.TStoredProcedureSqlStatement;
import gudusoft.gsqlparser.stmt.TSelectSqlStatement;
import gudusoft.gsqlparser.stmt.mssql.TMssqlExecute;
import gudusoft.gsqlparser.util.SQLUtil;
import java.util.*;

/** Captures table metadata and lexical defaults at each statement's position. */
public final class StatementMetadata {
    /** Distinguishes a routine-local catalog from a separately parsed batch's environment. */
    public static final class ProcedureEnvironment extends TSQLEnv {
        private ProcedureEnvironment(EDbVendor vendor, IdentifierProfile profile) {
            super(vendor, IdentifierProfile.forVendorOr(profile, vendor));
        }
        @Override public void initSQLEnv() { }
    }
    public static final class Context {
        public final String catalog;
        public final String schema;
        private Context(String catalog, String schema) {
            this.catalog = catalog;
            this.schema = schema;
        }
    }

    /**
     * A temporary-table fact in a routine body, or in the top-level script, in statement order:
     * a transient table is defined or dropped, a routine is called, or a table is referenced.
     */
    private static final class TemporaryEvent {
        enum Kind { DEFINE, DROP, CALL, REFERENCE }
        final Kind kind;
        final int sequence;
        /** The routine whose body holds the event; null for the top-level script. */
        final TStoredProcedureSqlStatement body;
        final List<CanonKey> key;
        final TSQLTable table;
        final TemporaryTableSemantics.Scope scope;
        final TTable reference;
        TStoredProcedureSqlStatement target;
        Map<List<CanonKey>, Object> visibleAtCall;

        private TemporaryEvent(Kind kind, int sequence, TStoredProcedureSqlStatement body, List<CanonKey> key,
                TSQLTable table, TemporaryTableSemantics.Scope scope, TTable reference) {
            this.kind = kind;
            this.sequence = sequence;
            this.body = body;
            this.key = key;
            this.table = table;
            this.scope = scope;
            this.reference = reference;
        }
    }

    /** In a map of visible temporary tables: some temporary table is visible, but which one depends on the path. */
    private static final Object CONFLICT = new Object();

    private final Map<TTable, TSQLTable> tables = new IdentityHashMap<>();
    private final Map<TTable, Context> tableContexts = new IdentityHashMap<>();
    private final Map<TCustomSqlStatement, Context> contexts = new LinkedHashMap<>();
    private final Map<TStoredProcedureSqlStatement, TSQLEnv> procedureEnvironments = new IdentityHashMap<>();
    private final Map<TStoredProcedureSqlStatement, List<TemporaryEvent>> routineEvents = new IdentityHashMap<>();
    private final List<TemporaryEvent> scriptEvents = new ArrayList<>();
    private final Set<TSQLTable> transientTables = Collections.newSetFromMap(new IdentityHashMap<TSQLTable, Boolean>());
    private int eventSequence;

    public Map<TCustomSqlStatement, Context> getContexts() { return contexts; }

    /** Register a function using its declaration's lexical defaults, not the batch's defaults. */
    public void registerFunction(TCreateFunctionStmt statement, TSQLEnv env) {
        Context context = contexts.get(statement);
        if (context == null) {
            registerReturnColumns(env.addFunction(statement.getFunctionName(), true), statement);
            return;
        }
        String savedCatalog = env.getDefaultCatalogName();
        String savedSchema = env.getDefaultSchemaName();
        try {
            env.setDefaultCatalogName(context.catalog);
            env.setDefaultSchemaName(context.schema);
            registerReturnColumns(env.addFunction(statement.getFunctionName(), true), statement);
        } finally {
            env.setDefaultCatalogName(savedCatalog);
            env.setDefaultSchemaName(savedSchema);
        }
    }

    /**
     * The columns a function's declaration gives its table result (TableFunctionLayout.declaredColumns), registered
     * with it: a call of it in FROM has them.
     */
    public static void registerReturnColumns(gudusoft.gsqlparser.sqlenv.TSQLFunction function,
            TCreateFunctionStmt statement) {
        if (function != null && function.getReturnType() == null && statement.getReturnDataType() != null) {
            function.setReturnType(statement.getReturnDataType()); // a call reads its row type's columns (slice 13f)
        }
        java.util.List<String> columns = function == null ? null
                : gudusoft.gsqlparser.resolver2.namespace.TableFunctionLayout.declaredColumns(statement);
        if (columns != null) {
            for (String column : columns) {
                function.addReturnColumn(column);
            }
        }
    }

    public void configure(TableNamespace namespace, TTable table) {
        Context context = tableContexts.get(table);
        if (context != null) {
            TSQLTable metadata = tables.get(table);
            // A SELECT INTO * target has identity before its projection has a known shape.
            if (metadata != null && metadata.getColumnList().isEmpty()
                    && (transientTables.contains(metadata)
                    || SQLUtil.isLocalTemporaryTable(table.dbvendor, table.getTableName()))) metadata = null;
            namespace.setStatementMetadata(metadata, new QualifiedNameResolver(
                    context.catalog, context.schema, table.dbvendor));
        }
    }

    public void collect(TStatementList statements, TSQLEnv env) {
        String savedCatalog = env.getDefaultCatalogName();
        String savedSchema = env.getDefaultSchemaName();
        DatabaseContextTracker tracker = new DatabaseContextTracker(savedCatalog, savedSchema);
        Set<TCustomSqlStatement> seen = Collections.newSetFromMap(new IdentityHashMap<TCustomSqlStatement, Boolean>());
        Deque<TCustomSqlStatement> pending = new ArrayDeque<>();
        pushStatements(pending, statements);
        try {
            while (!pending.isEmpty()) {
                TCustomSqlStatement stmt = pending.pop();
                if (!seen.add(stmt)) continue;
                tracker.processStatement(stmt);
                Context context = contextOf(tracker, stmt.dbvendor, env);
                String catalog = context.catalog;
                String schema = context.schema;
                env.setDefaultCatalogName(catalog);
                env.setDefaultSchemaName(schema);
                contexts.put(stmt, context);
                boolean temporary = TemporaryTableSemantics.hasTransientTables(stmt.dbvendor);
                TStoredProcedureSqlStatement body = temporary ? procedureOwner(stmt) : null;
                TSQLEnv localEnv = body == null ? null : procedureEnvironments.get(body);
                if (localEnv != null) {
                    localEnv.setDefaultCatalogName(catalog);
                    localEnv.setDefaultSchemaName(schema);
                }
                if (stmt instanceof TCreateTableSqlStatement) {
                    declareTable((TCreateTableSqlStatement) stmt, context, body, env, temporary);
                } else if (stmt instanceof gudusoft.gsqlparser.stmt.TAlterTableStatement) {
                    addColumns((gudusoft.gsqlparser.stmt.TAlterTableStatement) stmt, env);
                }
                for (int i = 0; i < stmt.tables.size(); i++) {
                    TTable table = stmt.tables.getTable(i);
                    if (table.getTableName() == null) continue;
                    if (temporary && table.getEffectType() == ETableEffectType.tetSelectInto
                            && stmt instanceof TSelectSqlStatement) {
                        declareSelectInto((TSelectSqlStatement) stmt, table, context, body, env);
                    }
                    tableContexts.put(table, context);
                    bindTable(table, context, body, env, temporary);
                }
                if (temporary) {
                    TObjectName called = calledRoutine(stmt);
                    if (called != null) {
                        record(new TemporaryEvent(TemporaryEvent.Kind.CALL, eventSequence++, body,
                                procedureKey(called, context, env), null, null, null));
                    }
                    if (stmt instanceof TDropTableSqlStatement) dropTables((TDropTableSqlStatement) stmt, context, body, env);
                }
                pushStatements(pending, stmt.getStatements());
            }
            bindTemporaryTables(env);
        } finally {
            env.setDefaultCatalogName(savedCatalog);
            env.setDefaultSchemaName(savedSchema);
        }
    }

    /**
     * The database and schema a statement runs in, from the {@code USE} statements before it as {@code tracker}
     * replayed them: Snowflake's schema after a database switch when the catalog proves one, SQL Server's dbo.
     */
    static Context contextOf(DatabaseContextTracker tracker, EDbVendor vendor, TSQLEnv env) {
        String catalog = tracker.getCurrentDatabase();
        String schema = tracker.getCurrentSchemaComponent();
        if (schema == null && vendor == EDbVendor.dbvsnowflake && catalog != null) {
            schema = env.findSchemaAfterDatabaseSwitch(catalog);
        }
        // SQL Server unqualified objects live in dbo unless the caller supplies a schema.
        if (schema == null && (vendor == EDbVendor.dbvmssql || vendor == EDbVendor.dbvazuresql)) {
            schema = "dbo";
        }
        return new Context(catalog, schema);
    }

    /**
     * A CREATE TABLE. A routine's transient table goes to the routine's own catalog; anything
     * else, a top-level temporary table included, to the shared one, as before.
     */
    private void declareTable(TCreateTableSqlStatement create, Context context, TStoredProcedureSqlStatement body,
            TSQLEnv env, boolean temporary) {
        TemporaryTableSemantics.Scope scope = temporary ? TemporaryTableSemantics.ofDeclaration(create) : null;
        if (!TemporaryTableSemantics.isTransient(scope)) {
            TMetadataCollector.registerDeclaredTable(create, env);
            return;
        }
        TSQLEnv target = body != null ? procedureEnvironment(body, context, env) : env;
        TSQLTable declared = TMetadataCollector.registerDeclaredTable(create, target);
        if (declared == null) declared = declareTransientQuery(create, target, body != null);
        if (declared != null) define(declared, create.getTableName(), create.dbvendor, context, body, env, scope);
    }

    /**
     * ALTER TABLE ... ADD COLUMN(S): the statements after it read the table with the columns added, each with its
     * type, a STRUCT column with its fields (Mantis 4846). The catalog gets a new version of the table, and a
     * statement before the ALTER keeps the one it found: there the name of an added column still means what it meant.
     * A table the catalog does not hold is left alone: nothing proves which other columns it has. So is an ALTER inside
     * a routine, which runs when the routine is called, not where it is declared, and a sealed table.
     */
    private static void addColumns(gudusoft.gsqlparser.stmt.TAlterTableStatement alter, TSQLEnv env) {
        if (!env.isEnableGetMetadataFromDDL() || (alter.getParentStmt() != null) || (alter.getTableName() == null)
                || (alter.getAlterTableOptionList() == null)) {
            return;
        }
        TSQLTable found = env.searchTable(alter.getTableName());
        if ((found == null) || found.isSealed() || (found.getSchema() == null) || found.getSchema().isSealed()) return;
        TSQLTable table = found;
        for (TAlterTableOption option : alter.getAlterTableOptionList()) {
            if (((option.getOptionType() != EAlterTableOptionType.AddColumn)
                    && (option.getOptionType() != EAlterTableOptionType.addColumnIfNotExists))
                    || (option.getColumnDefinitionList() == null)) {
                continue;
            }
            for (TColumnDefinition column : option.getColumnDefinitionList()) {
                if ((column.getColumnName() == null) || (table.getColumn(column.getColumnName().toString()) != null)) {
                    continue;
                }
                if (table == found) table = found.newVersion();
                table.addColumn(column.getColumnName().toString(), column.getDatatype());
            }
        }
    }

    /**
     * The table a transient table's declaration with no column list gives {@code target} (its routine's own catalog,
     * or the shared one for a top-level table), or null. Shared with the parse ({@link ParseTimeCatalog}).
     */
    static TSQLTable declareTransientQuery(TCreateTableSqlStatement create, TSQLEnv target, boolean inRoutine) {
        // CREATE TEMP TABLE ... AS SELECT takes the query's projection; any other form (LIKE,
        // a copy) still declares a table of its own, whose shape is not known.
        if (inRoutine) return declareQueryTable(target, create.getTableName(), create.getSubQuery());
        // A top-level table stays in the shared catalog, where the whole-script DDL preload may
        // already have put it (CREATE TEMP TABLE ... AS SELECT); routines see it there.
        // A #name is never a catalog table's, so its query shape cannot be taken for one:
        // register it here too (a dynamic statement's CREATE TABLE #t AS SELECT is never preloaded).
        if (TemporaryTableSemantics.ofName(create.dbvendor, create.getTableName()) == null) return null;
        return declareQueryTable(target, create.getTableName(), create.getSubQuery());
    }

    /** A {@code SELECT ... INTO} target that creates a transient table ({@code INTO #t}, {@code INTO TEMP t}). */
    private void declareSelectInto(TSelectSqlStatement select, TTable table, Context context,
            TStoredProcedureSqlStatement body, TSQLEnv env) {
        TemporaryTableSemantics.Scope scope = TemporaryTableSemantics.ofSelectInto(select.dbvendor, table.getTableName());
        if (!TemporaryTableSemantics.isTransient(scope)) return;
        // Outside a routine TIntoClause has already registered the target in the shared catalog.
        TSQLTable declared = body != null
                ? declareQueryTable(procedureEnvironment(body, context, env), table.getTableName(), select)
                : env.searchTable(table.getTableName());
        if (declared != null) define(declared, table.getTableName(), select.dbvendor, context, body, env, scope);
    }

    static TSQLTable declareQueryTable(TSQLEnv target, TObjectName name, TSelectSqlStatement query) {
        TSQLTable declared = target.addTable(name.toString(), true);
        if (declared != null) addProjection(declared, query);
        return declared;
    }

    /**
     * {@link #declareQueryTable(TSQLEnv, TObjectName, TSelectSqlStatement)} where an unqualified name is in
     * {@code catalog} and {@code schema}, leaving {@code target}'s defaults as they are. With {@code replace} the
     * table is a new one in place of a table {@code target} holds there.
     */
    static TSQLTable declareQueryTable(TSQLEnv target, TObjectName name, TSelectSqlStatement query, String catalog,
            String schema, boolean replace) {
        if (!target.isEnableGetMetadataFromDDL()) return null;
        TSQLSchema owner = TMetadataCollector.declaredTableOwner(name, target.getDBVendor(), target, catalog, schema);
        TSQLTable declared = replace ? new TSQLTable(owner, name.getObjectString())
                : owner.createTable(name.getObjectString());
        if (declared != null) addProjection(declared, query);
        return declared;
    }

    /** The columns {@code query} gives a table it fills: those of its first SELECT. */
    private static void addProjection(TSQLTable declared, TSelectSqlStatement query) {
        if (query == null) return;
        TSelectSqlStatement projection = query.isCombinedQuery() ? query.getLeftStmt() : query;
        while (projection != null && projection.isCombinedQuery()) projection = projection.getLeftStmt();
        if (projection == null || projection.getResultColumnList() == null) return;
        for (TResultColumn column : projection.getResultColumnList()) {
            TObjectName field = column.getFieldAttr();
            // SELECT * has no shape until the star is expanded; leave the table open.
            if (field != null && field.getPartToken() != null
                    && field.getPartToken().tokentype == ETokenType.ttasterisk) return;
        }
        projection.addColumnInSelectListToSQLEnv(declared);
    }

    private void define(TSQLTable declared, TObjectName name, EDbVendor vendor, Context context,
            TStoredProcedureSqlStatement body, TSQLEnv env, TemporaryTableSemantics.Scope scope) {
        transientTables.add(declared);
        record(new TemporaryEvent(TemporaryEvent.Kind.DEFINE, eventSequence++, body,
                temporaryKey(name, vendor, context, env), declared, scope, null));
    }

    private void dropTables(TDropTableSqlStatement drop, Context context, TStoredProcedureSqlStatement body, TSQLEnv env) {
        List<TObjectName> names = new ArrayList<>();
        if (drop.getTableNameList() != null) {
            for (int i = 0; i < drop.getTableNameList().size(); i++) names.add(drop.getTableNameList().getObjectName(i));
        } else if (drop.getTableName() != null) {
            names.add(drop.getTableName());
        }
        for (TObjectName name : names) {
            if (name == null) continue;
            record(new TemporaryEvent(TemporaryEvent.Kind.DROP, eventSequence++, body,
                    temporaryKey(name, drop.dbvendor, context, env), null, null, null));
        }
    }

    /**
     * Bind a reference at its position: a transient table its own body defined so far shadows a
     * catalog table of the same name. Calls, drops and callers are applied after the walk.
     */
    private void bindTable(TTable table, Context context, TStoredProcedureSqlStatement body, TSQLEnv env,
            boolean temporary) {
        boolean namedTemporary = SQLUtil.isLocalTemporaryTable(table.dbvendor, table.getTableName());
        // A #name table in a routine is the routine's, even before a declaration binds it.
        TSQLEnv localEnv = body == null ? null
                : namedTemporary ? procedureEnvironment(body, context, env) : procedureEnvironments.get(body);
        // Only transient definitions of this body are stored in its own catalog.
        TSQLTable metadata = localEnv != null ? localEnv.searchTable(table.getTableName()) : null;
        if (localEnv != null && namedTemporary) table.setSqlEnv(localEnv);
        if (metadata != null) table.setSqlEnv(metadata.getSqlEnv());
        if (metadata == null) metadata = env.searchTable(table.getTableName());
        if (metadata != null && localEnv != null && namedTemporary) table.setSqlEnv(metadata.getSqlEnv());
        tables.put(table, metadata);
        table.setResolvedTable(metadata);
        table.setCatalogStatus(catalogStatus(table, metadata, env, namedTemporary ? localEnv : null));
        if (temporary && table.getTableType() == ETableSource.objectname && !table.isCTEName()) {
            record(new TemporaryEvent(TemporaryEvent.Kind.REFERENCE, eventSequence++, body,
                    temporaryKey(table.getTableName(), table.dbvendor, context, env), null, null, table));
        }
    }

    /**
     * The table-to-catalog fact, kept apart from the column-to-table fact. NOT_FOUND is claimed
     * only for a base table reference, and only when there was metadata to look in.
     */
    private static ETableCatalogStatus catalogStatus(TTable table, TSQLTable metadata, TSQLEnv env, TSQLEnv localEnv) {
        if (metadata != null) return ETableCatalogStatus.FOUND;
        if (table.getTableType() != ETableSource.objectname || table.isCTEName()) {
            return ETableCatalogStatus.NOT_CHECKED;
        }
        TObjectName name = table.getTableName();
        // Decided from the parse, never from the spelling: a table variable (@tv) is not a
        // catalog object, and for a reference through a database link the local catalog is not
        // the authority.
        if (name != null && ((name.getEndToken() != null && name.getEndToken().tokentype == ETokenType.ttsqlvar)
                || name.getDblink() != null)) {
            return ETableCatalogStatus.NOT_CHECKED;
        }
        boolean anyMetadata = (env != null && env.hasCatalogMetadata())
                || (localEnv != null && localEnv.hasCatalogMetadata());
        return anyMetadata ? ETableCatalogStatus.NOT_FOUND : ETableCatalogStatus.NOT_CHECKED;
    }

    /** The routine's own catalog, made when the routine first needs one, with the statement's defaults. */
    private TSQLEnv procedureEnvironment(TStoredProcedureSqlStatement procedure, Context context, TSQLEnv env) {
        TSQLEnv local = procedureEnvironments.get(procedure);
        if (local == null) {
            local = newProcedureEnvironment(env, context);
            procedureEnvironments.put(procedure, local);
        }
        return local;
    }

    /** A routine's own catalog for its transient tables, under the shared catalog's rules and DDL gate. */
    static ProcedureEnvironment newProcedureEnvironment(TSQLEnv env, Context context) {
        // The routine-local catalog is keyed by THIS analysis's rules and probed by them. Built
        // from the bare vendor it ran on the vendor default while the enclosing environment ran
        // the analysis's profile — the two halves of one lookup asking different questions.
        ProcedureEnvironment local = new ProcedureEnvironment(env.getDBVendor(), env.getIdentifierProfile());
        local.setEnableGetMetadataFromDDL(env.isEnableGetMetadataFromDDL());
        local.setDefaultCatalogName(context.catalog);
        local.setDefaultSchemaName(context.schema);
        return local;
    }

    /**
     * The routine whose body holds a statement: the nearest enclosing named routine. An anonymous
     * block ({@code BEGIN ... END} parsed as a block statement) runs as part of the body around it,
     * or of the top-level script.
     */
    static TStoredProcedureSqlStatement procedureOwner(TCustomSqlStatement statement) {
        for (TCustomSqlStatement current = statement; current != null; current = current.getParentStmt()) {
            if (current instanceof TStoredProcedureSqlStatement
                    && ((TStoredProcedureSqlStatement) current).getStoredProcedureName() != null) {
                return (TStoredProcedureSqlStatement) current;
            }
        }
        return null;
    }

    private void record(TemporaryEvent event) {
        if (event.body == null) {
            scriptEvents.add(event);
            return;
        }
        List<TemporaryEvent> events = routineEvents.get(event.body);
        if (events == null) routineEvents.put(event.body, events = new ArrayList<>());
        events.add(event);
    }

    private List<TemporaryEvent> eventsOf(TStoredProcedureSqlStatement body) {
        if (body == null) return scriptEvents;
        List<TemporaryEvent> events = routineEvents.get(body);
        return events == null ? Collections.<TemporaryEvent>emptyList() : events;
    }

    /**
     * A temporary table's key. A name that is temporary by its spelling ({@code #t}) is the same
     * table whatever qualifies it; any other name is keyed with the defaults at its statement.
     */
    private static List<CanonKey> temporaryKey(TObjectName name, EDbVendor vendor, Context context, TSQLEnv env) {
        IdentifierService service = env.getIdentifierService();
        CanonKey object = service.canonKey(name.getObjectString(), ESQLDataObjectType.dotTable);
        if (TemporaryTableSemantics.ofName(vendor, name) != null) return Collections.singletonList(object);
        String catalog = SQLUtil.isEmpty(name.getDatabaseString()) ? context.catalog : name.getDatabaseString();
        String schema = SQLUtil.isEmpty(name.getSchemaString()) ? context.schema : name.getSchemaString();
        return Arrays.asList(service.canonKey(catalog, ESQLDataObjectType.dotCatalog),
                service.canonKey(schema, ESQLDataObjectType.dotSchema), object);
    }

    /**
     * Bind every reference to the transient table visible at it, whatever order the routines
     * were declared in. Within a body the latest definition, or call that returns a session
     * table, wins; a routine body that has none of its own sees what every one of its callers
     * makes visible at the call; the top-level script's tables stay visible to the routines
     * declared after them.
     */
    private void bindTemporaryTables(TSQLEnv env) {
        if (transientTables.isEmpty()) return;
        Set<List<CanonKey>> definedKeys = new HashSet<>();
        List<List<TemporaryEvent>> bodies = new ArrayList<>(routineEvents.values());
        bodies.add(scriptEvents);
        for (List<TemporaryEvent> events : bodies) {
            for (TemporaryEvent event : events) {
                if (event.kind == TemporaryEvent.Kind.DEFINE) definedKeys.add(event.key);
            }
        }
        Map<List<CanonKey>, List<TStoredProcedureSqlStatement>> declarations = new HashMap<>();
        for (TCustomSqlStatement statement : contexts.keySet()) {
            if (!(statement instanceof TStoredProcedureSqlStatement)) continue;
            TStoredProcedureSqlStatement procedure = (TStoredProcedureSqlStatement) statement;
            if (procedure.getStoredProcedureName() == null) continue;
            List<CanonKey> key = procedureKey(procedure.getStoredProcedureName(), contexts.get(statement), env);
            if (!declarations.containsKey(key)) declarations.put(key, new ArrayList<TStoredProcedureSqlStatement>());
            declarations.get(key).add(procedure);
        }
        TemporaryScopes scopes = new TemporaryScopes();
        for (List<TemporaryEvent> events : bodies) {
            for (TemporaryEvent call : events) {
                if (call.kind != TemporaryEvent.Kind.CALL) continue;
                List<TStoredProcedureSqlStatement> targets = declarations.get(call.key);
                if (targets == null || targets.size() != 1) continue;
                call.target = targets.get(0);
                if (!scopes.callers.containsKey(call.target)) {
                    scopes.callers.put(call.target, new ArrayList<TemporaryEvent>());
                }
                scopes.callers.get(call.target).add(call);
            }
        }
        for (TemporaryEvent event : scriptEvents) {
            if (event.kind != TemporaryEvent.Kind.REFERENCE) scopes.script.add(event);
        }
        List<TemporaryEvent> unbound = new ArrayList<>();
        for (List<TemporaryEvent> events : bodies) {
            Map<List<CanonKey>, Object> visible = new HashMap<>();
            for (TemporaryEvent event : events) {
                switch (event.kind) {
                    case DEFINE:
                        visible.put(event.key, event.table);
                        break;
                    case DROP:
                        // A DROP may sit in a branch that did not run: the table is still maybe
                        // present, and a later reference is still to it, never to a catalog table.
                        break;
                    case CALL:
                        event.visibleAtCall = new HashMap<>(visible);
                        if (event.target != null) visible.putAll(scopes.exportsOf(event.target));
                        break;
                    case REFERENCE:
                        if (!definedKeys.contains(event.key)) break;
                        Object bound = visible.get(event.key);
                        if (bound == null && event.body != null) unbound.add(event);
                        else if (bound != null) apply(event, bound, env);
                        break;
                    default:
                        break;
                }
            }
        }
        for (TemporaryEvent reference : unbound) {
            Object bound = scopes.incoming(reference.body, reference.key);
            if (bound == null) bound = scopes.scriptTableAt(reference.sequence, reference.key);
            if (bound != null) apply(reference, bound, env);
        }
    }

    /** The call graph and the tables it carries between routine bodies. */
    private final class TemporaryScopes {
        final Map<TStoredProcedureSqlStatement, List<TemporaryEvent>> callers = new IdentityHashMap<>();
        /** The top-level script's definitions, drops and calls, in statement order. */
        final List<TemporaryEvent> script = new ArrayList<>();
        private final Map<TStoredProcedureSqlStatement, Map<List<CanonKey>, Object>> exports = new IdentityHashMap<>();
        private final Set<TStoredProcedureSqlStatement> exporting = Collections.newSetFromMap(
                new IdentityHashMap<TStoredProcedureSqlStatement, Boolean>());

        /**
         * The session tables a routine leaves to its caller when it returns. A table that lives
         * only while the routine runs, or that the routine creates and drops, is not among them; a
         * DROP of a table the caller had may not have run, so the caller keeps it.
         */
        Map<List<CanonKey>, Object> exportsOf(TStoredProcedureSqlStatement routine) {
            Map<List<CanonKey>, Object> result = exports.get(routine);
            if (result != null) return result;
            // A recursive call returns nothing new while the routine's own effects are being computed.
            if (!exporting.add(routine)) return Collections.emptyMap();
            result = new HashMap<>();
            Set<List<CanonKey>> created = new HashSet<>();
            for (TemporaryEvent event : eventsOf(routine)) {
                if (event.kind == TemporaryEvent.Kind.DEFINE) {
                    created.add(event.key);
                    if (event.scope == TemporaryTableSemantics.Scope.SESSION) result.put(event.key, event.table);
                    else result.remove(event.key);
                } else if (event.kind == TemporaryEvent.Kind.DROP) {
                    if (created.contains(event.key)) result.remove(event.key);
                } else if (event.kind == TemporaryEvent.Kind.CALL && event.target != null) {
                    for (Map.Entry<List<CanonKey>, Object> returned : exportsOf(event.target).entrySet()) {
                        created.add(returned.getKey());
                        result.put(returned.getKey(), returned.getValue());
                    }
                }
            }
            exporting.remove(routine);
            exports.put(routine, result);
            return result;
        }

        /**
         * What every caller of a routine makes visible under a key at its call: one table when
         * they agree, {@link #CONFLICT} when they differ, null when some caller has none.
         */
        Object incoming(TStoredProcedureSqlStatement procedure, List<CanonKey> key) {
            Set<TStoredProcedureSqlStatement> seen = Collections.newSetFromMap(
                    new IdentityHashMap<TStoredProcedureSqlStatement, Boolean>());
            Deque<TStoredProcedureSqlStatement> pending = new ArrayDeque<>();
            pending.push(procedure);
            Object result = null;
            while (!pending.isEmpty()) {
                TStoredProcedureSqlStatement current = pending.pop();
                if (!seen.add(current)) continue;
                List<TemporaryEvent> sites = callers.get(current);
                if (sites == null || sites.isEmpty()) return null;
                for (TemporaryEvent call : sites) {
                    Object candidate = call.visibleAtCall == null ? null : call.visibleAtCall.get(key);
                    if (candidate == null && call.body != null) candidate = scriptTableAt(call.sequence, key);
                    if (candidate == null && call.body != null) {
                        // Nothing visible in the caller: the caller's callers decide.
                        pending.push(call.body);
                    } else if (candidate == null) {
                        return null;
                    } else if (result == null) {
                        result = candidate;
                    } else if (result != candidate) {
                        // Different callers supply different tables; do not invent one binding.
                        return CONFLICT;
                    }
                }
            }
            return result;
        }

        /** The top-level script's table under a key just before a position: routines see the script before them. */
        Object scriptTableAt(int sequence, List<CanonKey> key) {
            Object result = null;
            for (TemporaryEvent event : script) {
                if (event.sequence >= sequence) break;
                if (event.kind == TemporaryEvent.Kind.CALL) {
                    if (event.target == null) continue;
                    Object returned = exportsOf(event.target).get(key);
                    if (returned != null) result = returned;
                } else if (event.kind == TemporaryEvent.Kind.DEFINE && key.equals(event.key)) {
                    result = event.table;
                }
            }
            return result;
        }
    }

    /**
     * Bind a reference to the transient table visible at it, or, when callers disagree on which
     * one, to none: some temporary table is visible there, so a catalog table of the name is not.
     */
    private void apply(TemporaryEvent reference, Object bound, TSQLEnv env) {
        TTable table = reference.reference;
        TSQLTable metadata = bound instanceof TSQLTable ? (TSQLTable) bound : null;
        if (metadata != null) {
            table.setSqlEnv(metadata.getSqlEnv());
            table.setCatalogStatus(ETableCatalogStatus.FOUND);
        } else {
            if (reference.body != null) {
                table.setSqlEnv(procedureEnvironment(reference.body, tableContexts.get(table), env));
            }
            table.setCatalogStatus(ETableCatalogStatus.NOT_CHECKED);
        }
        tables.put(table, metadata);
        table.setResolvedTable(metadata);
    }

    private List<CanonKey> procedureKey(TObjectName name, Context context, TSQLEnv env) {
        String catalog = SQLUtil.isEmpty(name.getDatabaseString()) ? context.catalog : name.getDatabaseString();
        String schema = SQLUtil.isEmpty(name.getSchemaString()) ? context.schema : name.getSchemaString();
        IdentifierService service = env.getIdentifierService();
        return Arrays.asList(service.canonKey(catalog, ESQLDataObjectType.dotCatalog),
                service.canonKey(schema, ESQLDataObjectType.dotSchema),
                service.canonKey(name.getObjectString(), ESQLDataObjectType.dotProcedure));
    }

    /**
     * The procedure a statement calls: {@code EXEC[UTE] <procedure>} in the SQL Server and ASE
     * family, or {@code CALL <procedure>}. Null for anything else, including {@code EXEC} of a
     * string.
     */
    private static TObjectName calledRoutine(TCustomSqlStatement statement) {
        if (statement instanceof TMssqlExecute) {
            TMssqlExecute exec = (TMssqlExecute) statement;
            return exec.getExecType() == TBaseType.metExecSp ? exec.getModuleName() : null;
        }
        if (statement instanceof TCallStatement) {
            return ((TCallStatement) statement).getRoutineName();
        }
        return null;
    }

    private static void pushStatements(Deque<TCustomSqlStatement> pending, TStatementList statements) {
        if (statements == null) return;
        for (int i = statements.size() - 1; i >= 0; i--) {
            if (statements.get(i) != null) pending.push(statements.get(i));
        }
    }
}