package gudusoft.gsqlparser.resolver2.namespace;

import gudusoft.gsqlparser.EExpressionType;
import gudusoft.gsqlparser.nodes.TResultColumn;
import gudusoft.gsqlparser.nodes.TResultColumnList;
import gudusoft.gsqlparser.nodes.TTable;
import gudusoft.gsqlparser.resolver2.ColumnLevel;
import gudusoft.gsqlparser.resolver2.matcher.INameMatcher;
import gudusoft.gsqlparser.resolver2.model.ColumnSource;
import gudusoft.gsqlparser.sqlenv.TSQLEnv;
import gudusoft.gsqlparser.stmt.TSelectSqlStatement;

import java.util.ArrayDeque;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Deque;
import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * Namespace representing a subquery.
 * Provides columns from the subquery's SELECT list.
 *
 * Example:
 * FROM (SELECT id, name FROM users) AS t
 *       ^^^^^^^^^^^^^^^^^^^^^^^^
 *       SubqueryNamespace exposes columns: id, name
 */
public class SubqueryNamespace extends AbstractNamespace {

    private final TSelectSqlStatement subquery;
    private final String alias;

    /** Inferred columns from star push-down */
    private Map<String, ColumnSource> inferredColumns;

    /** Track inferred column names for getInferredColumns() */
    private Set<String> inferredColumnNames;

    /** Traced columns from qualified references (e.g., table1.x in ON conditions) */
    private Map<String, TTable> tracedColumnTables;

    /** Whether this namespace was created from a TABLE function */
    private final boolean fromTableFunction;

    /**
     * Cache for nested SubqueryNamespace objects to avoid repeated creation.
     * Key: TSelectSqlStatement, Value: validated SubqueryNamespace
     */
    private Map<TSelectSqlStatement, SubqueryNamespace> nestedSubqueryCache;

    /**
     * Cache for nested TableNamespace objects to avoid repeated creation.
     * Key: TTable, Value: validated TableNamespace
     */
    private Map<TTable, TableNamespace> nestedTableCache;

    /** The TTable that wraps this subquery (for legacy sync) */
    private TTable sourceTable;

    /** TSQLEnv for looking up table metadata during star column resolution */
    private TSQLEnv sqlEnv;

    /**
     * Memoized result of the qualified-star SELECT-list scan (see
     * {@link #scanQualifiedStarMatchTable()}). Both a found table and a null
     * result are cached, and the cache never needs invalidation because the
     * scanned value is a pure function of parse-time-immutable state.
     */
    private boolean qualifiedStarScanned;
    private TTable qualifiedStarMatchTable;

    public SubqueryNamespace(TSelectSqlStatement subquery,
                            String alias,
                            INameMatcher nameMatcher) {
        this(subquery, alias, nameMatcher, false);
    }

    public SubqueryNamespace(TSelectSqlStatement subquery,
                            String alias,
                            INameMatcher nameMatcher,
                            boolean fromTableFunction) {
        super(subquery, nameMatcher);
        this.subquery = subquery;
        this.alias = alias;
        this.fromTableFunction = fromTableFunction;
    }

    public SubqueryNamespace(TSelectSqlStatement subquery, String alias) {
        super(subquery);
        this.subquery = subquery;
        this.alias = alias;
        this.fromTableFunction = false;
    }

    /**
     * Gets or creates a cached nested SubqueryNamespace that inherits the guessColumnStrategy from this namespace.
     * This ensures config-based isolation propagates through nested resolution.
     * The namespace is cached to avoid repeated creation and validation for the same subquery.
     * Also propagates sqlEnv for metadata lookup in star column resolution.
     *
     * @param subquery The subquery statement
     * @param alias The alias for the subquery (not used as cache key, only for display)
     * @return A validated SubqueryNamespace (cached or newly created)
     */
    private SubqueryNamespace getOrCreateNestedNamespace(TSelectSqlStatement subquery, String alias) {
        if (subquery == null) {
            return null;
        }
        // Initialize cache lazily
        if (nestedSubqueryCache == null) {
            nestedSubqueryCache = new java.util.HashMap<>();
        }
        // Check cache first
        SubqueryNamespace cached = nestedSubqueryCache.get(subquery);
        if (cached != null) {
            return cached;
        }
        // Create new namespace
        SubqueryNamespace nested = new SubqueryNamespace(subquery, alias, nameMatcher);
        // Propagate guessColumnStrategy for config-based isolation
        if (this.guessColumnStrategy >= 0) {
            nested.setGuessColumnStrategy(this.guessColumnStrategy);
        }
        // Propagate sqlEnv for metadata lookup during star column resolution
        if (this.sqlEnv != null) {
            nested.setSqlEnv(this.sqlEnv);
        }
        // Validate and cache
        nested.validate();
        nestedSubqueryCache.put(subquery, nested);
        return nested;
    }

    /**
     * Gets or creates a cached TableNamespace.
     * The namespace is cached to avoid repeated creation and validation for the same table.
     * Passes sqlEnv to enable metadata lookup for star column resolution.
     *
     * @param table The table
     * @return A validated TableNamespace (cached or newly created)
     */
    private TableNamespace getOrCreateTableNamespace(TTable table) {
        if (table == null) {
            return null;
        }
        // Initialize cache lazily
        if (nestedTableCache == null) {
            nestedTableCache = new java.util.HashMap<>();
        }
        // Check cache first
        TableNamespace cached = nestedTableCache.get(table);
        if (cached != null) {
            return cached;
        }
        // Create new namespace with sqlEnv for metadata lookup
        TableNamespace tableNs = new TableNamespace(table, nameMatcher, sqlEnv);
        // Validate and cache
        tableNs.validate();
        nestedTableCache.put(table, tableNs);
        return tableNs;
    }

    /**
     * Creates a nested SubqueryNamespace that inherits the guessColumnStrategy from this namespace.
     * This method is deprecated - use getOrCreateNestedNamespace for caching support.
     *
     * @param subquery The subquery statement
     * @param alias The alias for the subquery
     * @return A new SubqueryNamespace with inherited guessColumnStrategy and sqlEnv
     * @deprecated Use getOrCreateNestedNamespace instead
     */
    @Deprecated
    private SubqueryNamespace createNestedNamespace(TSelectSqlStatement subquery, String alias) {
        SubqueryNamespace nested = new SubqueryNamespace(subquery, alias, nameMatcher);
        // Propagate guessColumnStrategy for config-based isolation
        if (this.guessColumnStrategy >= 0) {
            nested.setGuessColumnStrategy(this.guessColumnStrategy);
        }
        // Propagate sqlEnv for metadata lookup
        if (this.sqlEnv != null) {
            nested.setSqlEnv(this.sqlEnv);
        }
        return nested;
    }

    /**
     * Returns true if this namespace was created from a TABLE function's subquery.
     */
    public boolean isFromTableFunction() {
        return fromTableFunction;
    }

    /**
     * Set the TTable that wraps this subquery.
     * Used by ScopeBuilder for legacy sync support.
     *
     * @param sourceTable the TTable that contains this subquery
     */
    public void setSourceTable(TTable sourceTable) {
        this.sourceTable = sourceTable;
    }

    @Override
    public TTable getSourceTable() {
        return sourceTable;
    }

    /**
     * Set the TSQLEnv for metadata lookup.
     * Used when resolving star columns to find which columns exist in underlying tables.
     *
     * @param sqlEnv the SQL environment containing table metadata
     */
    public void setSqlEnv(TSQLEnv sqlEnv) {
        this.sqlEnv = sqlEnv;
    }

    /**
     * Get the TSQLEnv used for metadata lookup.
     *
     * @return the SQL environment, or null if not set
     */
    public TSQLEnv getSqlEnv() {
        return sqlEnv;
    }

    @Override
    public String getDisplayName() {
        return alias != null ? alias : "<subquery>";
    }

    /** Cache-after-completion memo of {@link #computeFinalTable()} (see that method's proof). */
    private boolean finalTableComputed;
    private TTable finalTableValue;

    @Override
    public TTable getFinalTable() {
        // Memoized. The flag is set ONLY after computeFinalTable() returns, so a
        // re-entrant call during computation (e.g. a cyclic recursive CTE) still
        // recomputes exactly as baseline did — no new guard, no behavior change;
        // if baseline would not terminate, this does not terminate on the first
        // call either. No invalidation is needed because the result is a pure
        // function of parse-time-immutable state (see computeFinalTable()).
        if (finalTableComputed) {
            return finalTableValue;
        }
        TTable result = computeFinalTable();
        finalTableValue = result;
        finalTableComputed = true;
        return result;
    }

    /**
     * Determine the underlying physical table a subquery resolves to.
     *
     * <p><b>Purity theorem (why {@link #getFinalTable()} may memoize this with no
     * invalidation).</b> Under resolver2's operating contract — single-threaded
     * resolution over a parser-produced AST that is frozen for the duration of the
     * run, with a matcher that is a pure function of its arguments — this method is
     * a pure function of parse-time-immutable state, so its result never changes
     * across resolution passes.</p>
     *
     * <p>Proof sketch (verified by adversarial review):</p>
     * <ul>
     *   <li>This method and its whole call graph ({@link #findTableFromQualifiedStar},
     *       {@link #scanQualifiedStarMatchTable}, {@link #traceTableThroughCTE},
     *       {@link #findFirstPhysicalTableFromJoin}, {@link #findTableByAliasOrName},
     *       and nested {@link #getFinalTable}) read ONLY immutable AST:
     *       {@code subquery.tables}/{@code getRelations()}, {@code getTableType()},
     *       {@code isCTEName()}/{@code getCTE()}, {@code getSubquery()},
     *       {@code getJoinExpr()}/{@code getLeftTable()}, alias/name. They read NONE
     *       of the mutable namespace fields (inferredColumns, tracedColumnTables,
     *       columnSources, sourceTable, sqlEnv).</li>
     *   <li>Resolver2 never mutates those AST properties during a run: it does not
     *       call setCTE/setCTEName/setTableType/setSubquery, nor add/remove/reorder
     *       {@code tables}/{@code relations}/result columns. Pass 2+ mutates only
     *       resolution/inference state, which this method never reads.</li>
     *   <li>The qualified-star scan's only side effect (marking redundant newline
     *       tokens deleted in toString) is idempotent after the first call.</li>
     * </ul>
     *
     * <p>The contract's preconditions (frozen AST, deterministic matcher, single
     * thread) are NOT new: resolver2 already depends on them — an impure matcher or
     * a mid-resolution AST mutation would corrupt resolution regardless of this
     * cache. Because {@code TTable}/{@code TResultColumnList} remain publicly
     * mutable, this is a resolver-internal optimization, not an API-wide theorem.</p>
     */
    private TTable computeFinalTable() {
        // Try to find the underlying table from the FROM clause
        // This works for both star subqueries (SELECT *) and explicit column subqueries
        if (subquery != null && subquery.tables != null && subquery.tables.size() > 0) {
            // First check if there's a qualified star column (e.g., ta.* or tb.*)
            // If so, we should return the table matching that alias
            TTable qualifiedStarTable = findTableFromQualifiedStar();
            if (qualifiedStarTable != null) {
                return qualifiedStarTable;
            }

            // For single-table subqueries, return that table
            // For multi-table (JOIN/comma) subqueries, only return if we can determine source
            TTable firstTable = subquery.tables.getTable(0);
            if (firstTable != null) {
                // If it's a physical table, return it - BUT only if we can identify the source
                if (firstTable.getTableType() == gudusoft.gsqlparser.ETableSource.objectname) {
                    // For multi-table subqueries without qualified star, we can't determine
                    // which table the column comes from. Don't blindly return first table.
                    if (subquery.tables.size() > 1 && qualifiedStarTable == null) {
                        return null;
                    }
                    // Check if it's a CTE reference - if so, trace through to physical table
                    if (firstTable.isCTEName() && firstTable.getCTE() != null) {
                        return traceTableThroughCTE(firstTable.getCTE());
                    }
                    return firstTable;
                }
                // If it's a subquery, recursively trace (use cached namespace)
                if (firstTable.getSubquery() != null) {
                    SubqueryNamespace nestedNs = getOrCreateNestedNamespace(
                        firstTable.getSubquery(),
                        firstTable.getAliasName()
                    );
                    return nestedNs != null ? nestedNs.getFinalTable() : null;
                }
                // If it's a join, get the first base table from the join
                if (firstTable.getTableType() == gudusoft.gsqlparser.ETableSource.join) {
                    return findFirstPhysicalTableFromJoin(firstTable);
                }
            }
        }
        return null;
    }

    /**
     * Find the table referenced by a qualified star column (e.g., ta.* -> table_a).
     * Returns null if there's no qualified star or if the star is unqualified (*).
     */
    private TTable findTableFromQualifiedStar() {
        // The winning table (first qualified-star column whose prefix resolves to
        // a table) is a pure function of parse-time-immutable state, so it is
        // memoized in scanQualifiedStarMatchTable(). The subsequent CTE/subquery
        // TRACING stays dynamic (it may go through nested namespaces whose final
        // table can depend on accumulated resolution state), so it is NOT cached.
        TTable matchingTable = scanQualifiedStarMatchTable();
        if (matchingTable != null) {
            // Check for CTE reference first - trace through to physical table
            if (matchingTable.isCTEName() && matchingTable.getCTE() != null) {
                return traceTableThroughCTE(matchingTable.getCTE());
            }
            // If the matching table is itself a subquery, trace through it (use cached namespace)
            if (matchingTable.getSubquery() != null) {
                SubqueryNamespace nestedNs = getOrCreateNestedNamespace(
                    matchingTable.getSubquery(),
                    matchingTable.getAliasName()
                );
                return nestedNs != null ? nestedNs.getFinalTable() : null;
            }
            return matchingTable;
        }
        return null;
    }

    /**
     * Scan the SELECT list for the first qualified star (e.g. {@code ta.*}) whose
     * prefix resolves to a table, and return that table (the value baseline acted
     * on before tracing). Memoized: baseline re-ran this scan on every
     * getFinalTable() call across every resolution pass — O(passes x columns x
     * toString) — but the result depends only on immutable state, so it is
     * computed once per namespace.
     *
     * <p>Provably identical to the original inline loop:</p>
     * <ul>
     *   <li>Same predicate: the EXACT {@code resultCol.toString().trim()} test
     *       (ends with {@code "*"} and contains {@code "."}), same prefix
     *       extraction, same {@link #findTableByAliasOrName}.</li>
     *   <li>"First non-null match wins" reproduces SubqueryNamespace's baseline,
     *       which returned unconditionally at the first non-null match. (This does
     *       NOT hold for CTENamespace, which only stops at a terminal-eligible
     *       match; CTENamespace is intentionally left unmemoized.)</li>
     *   <li>Purity holds under resolver2's operating contract: it never mutates a
     *       subquery's result-column list or a table's alias/name; INameMatcher.matches
     *       is a pure function of its arguments (all shipped matchers are); and
     *       TParseTreeNode.toString()'s only side effect (marking redundant newline
     *       tokens deleted) is idempotent after the first call, so computing the
     *       scan once leaves the same token state as baseline's repeated calls.</li>
     * </ul>
     */
    private TTable scanQualifiedStarMatchTable() {
        if (qualifiedStarScanned) {
            return qualifiedStarMatchTable;
        }
        TTable found = null;
        if (subquery != null && subquery.getResultColumnList() != null) {
            TResultColumnList selectList = subquery.getResultColumnList();
            for (int i = 0; i < selectList.size(); i++) {
                TResultColumn resultCol = selectList.getResultColumn(i);
                if (resultCol == null) continue;

                String colStr = resultCol.toString().trim();
                // Check if it's a qualified star (contains . before *)
                if (colStr.endsWith("*") && colStr.contains(".")) {
                    // Extract the table alias/name prefix (e.g., "ta" from "ta.*")
                    int dotIndex = colStr.lastIndexOf('.');
                    if (dotIndex > 0) {
                        String tablePrefix = colStr.substring(0, dotIndex).trim();
                        TTable matchingTable = findTableByAliasOrName(tablePrefix);
                        if (matchingTable != null) {
                            found = matchingTable;
                            break;
                        }
                    }
                }
            }
        }
        qualifiedStarMatchTable = found;
        qualifiedStarScanned = true;
        return found;
    }

    /**
     * Find a table in the FROM clause by alias or table name.
     * Also searches through JOIN expressions to find tables like PRD2_ODW.USI
     * that are joined via LEFT OUTER JOIN.
     */
    private TTable findTableByAliasOrName(String nameOrAlias) {
        if (subquery == null || nameOrAlias == null) {
            return null;
        }

        // Use getRelations() to get ALL tables including those in JOINs
        java.util.ArrayList<TTable> relations = subquery.getRelations();
        if (relations != null) {
            for (TTable table : relations) {
                if (table == null) continue;

                // For JOIN type tables, search inside the join expression
                if (table.getTableType() == gudusoft.gsqlparser.ETableSource.join) {
                    TTable found = findTableInJoinExpr(table.getJoinExpr(), nameOrAlias);
                    if (found != null) {
                        return found;
                    }
                    continue;
                }

                // Check alias first
                String alias = table.getAliasName();
                if (alias != null && nameMatcher.matches(alias, nameOrAlias)) {
                    return table;
                }

                // Check table name (for physical tables)
                if (table.getTableName() != null) {
                    String tableName = table.getTableName().toString();
                    if (nameMatcher.matches(tableName, nameOrAlias)) {
                        return table;
                    }
                    // Also check just the table name part (without schema)
                    String shortName = table.getName();
                    if (shortName != null && nameMatcher.matches(shortName, nameOrAlias)) {
                        return table;
                    }
                }
            }
        }

        // Fallback to subquery.tables if getRelations() didn't find it
        if (subquery.tables != null) {
            for (int i = 0; i < subquery.tables.size(); i++) {
                TTable table = subquery.tables.getTable(i);
                if (table == null) continue;

                // Check alias first
                String alias = table.getAliasName();
                if (alias != null && nameMatcher.matches(alias, nameOrAlias)) {
                    return table;
                }

                // Check table name
                if (table.getTableName() != null) {
                    String tableName = table.getTableName().toString();
                    if (nameMatcher.matches(tableName, nameOrAlias)) {
                        return table;
                    }
                }
            }
        }
        return null;
    }

    /**
     * Find a table within a JOIN expression by alias or table name.
     * Recursively searches both left and right sides of the join.
     */
    private TTable findTableInJoinExpr(gudusoft.gsqlparser.nodes.TJoinExpr joinExpr, String nameOrAlias) {
        if (joinExpr == null || nameOrAlias == null) {
            return null;
        }

        // Check left table
        TTable leftTable = joinExpr.getLeftTable();
        if (leftTable != null) {
            TTable found = matchTableByAliasOrName(leftTable, nameOrAlias);
            if (found != null) {
                return found;
            }
            // Recursively check if left is also a join
            if (leftTable.getTableType() == gudusoft.gsqlparser.ETableSource.join && leftTable.getJoinExpr() != null) {
                found = findTableInJoinExpr(leftTable.getJoinExpr(), nameOrAlias);
                if (found != null) {
                    return found;
                }
            }
        }

        // Check right table
        TTable rightTable = joinExpr.getRightTable();
        if (rightTable != null) {
            TTable found = matchTableByAliasOrName(rightTable, nameOrAlias);
            if (found != null) {
                return found;
            }
            // Recursively check if right is also a join
            if (rightTable.getTableType() == gudusoft.gsqlparser.ETableSource.join && rightTable.getJoinExpr() != null) {
                found = findTableInJoinExpr(rightTable.getJoinExpr(), nameOrAlias);
                if (found != null) {
                    return found;
                }
            }
        }

        return null;
    }

    /**
     * Check if a table matches the given alias or name.
     */
    private TTable matchTableByAliasOrName(TTable table, String nameOrAlias) {
        if (table == null || nameOrAlias == null) {
            return null;
        }

        // Check alias first
        String alias = table.getAliasName();
        if (alias != null && nameMatcher.matches(alias, nameOrAlias)) {
            return table;
        }

        // Check table name (for physical tables)
        if (table.getTableName() != null) {
            String tableName = table.getTableName().toString();
            if (nameMatcher.matches(tableName, nameOrAlias)) {
                return table;
            }
            // Also check just the table name part (without schema)
            // For "PRD2_ODW.USI", getName() returns "USI"
            String shortName = table.getName();
            if (shortName != null && nameMatcher.matches(shortName, nameOrAlias)) {
                return table;
            }
        }

        return null;
    }

    /**
     * Find the first physical table from a JOIN expression.
     * Recursively traverses left side of joins.
     */
    private TTable findFirstPhysicalTableFromJoin(TTable joinTable) {
        if (joinTable == null) {
            return null;
        }

        gudusoft.gsqlparser.nodes.TJoinExpr joinExpr = joinTable.getJoinExpr();
        if (joinExpr != null && joinExpr.getLeftTable() != null) {
            TTable leftTable = joinExpr.getLeftTable();

            if (leftTable.getTableType() == gudusoft.gsqlparser.ETableSource.objectname) {
                // Check if it's a CTE reference - trace through if so
                if (leftTable.isCTEName() && leftTable.getCTE() != null) {
                    return traceTableThroughCTE(leftTable.getCTE());
                }
                return leftTable;
            }
            if (leftTable.getTableType() == gudusoft.gsqlparser.ETableSource.join) {
                return findFirstPhysicalTableFromJoin(leftTable);
            }
            // If it's a subquery, trace through it (use cached namespace)
            if (leftTable.getSubquery() != null) {
                SubqueryNamespace nestedNs = getOrCreateNestedNamespace(
                    leftTable.getSubquery(),
                    leftTable.getAliasName()
                );
                return nestedNs != null ? nestedNs.getFinalTable() : null;
            }
        }
        return null;
    }

    /**
     * Trace through a CTE to find its underlying physical table.
     * This handles CTE chains like: CTE1 -> CTE2 -> CTE3 -> physical_table
     */
    private TTable traceTableThroughCTE(gudusoft.gsqlparser.nodes.TCTE cteNode) {
        if (cteNode == null || cteNode.getSubquery() == null) {
            return null;
        }

        gudusoft.gsqlparser.stmt.TSelectSqlStatement cteSubquery = cteNode.getSubquery();

        // Handle UNION in the CTE
        if (cteSubquery.isCombinedQuery()) {
            // For UNION, trace the left branch
            gudusoft.gsqlparser.stmt.TSelectSqlStatement leftStmt = cteSubquery.getLeftStmt();
            if (leftStmt != null && leftStmt.tables != null && leftStmt.tables.size() > 0) {
                cteSubquery = leftStmt;
            }
        }

        if (cteSubquery.tables == null || cteSubquery.tables.size() == 0) {
            return null;
        }

        TTable firstTable = cteSubquery.tables.getTable(0);
        if (firstTable == null) {
            return null;
        }

        // If it's a physical table (not CTE), we found it
        if (firstTable.getTableType() == gudusoft.gsqlparser.ETableSource.objectname && !firstTable.isCTEName()) {
            return firstTable;
        }

        // If it's another CTE reference, continue tracing
        if (firstTable.isCTEName() && firstTable.getCTE() != null) {
            return traceTableThroughCTE(firstTable.getCTE());
        }

        // If it's a subquery, trace through it (use cached namespace)
        if (firstTable.getSubquery() != null) {
            SubqueryNamespace nestedNs = getOrCreateNestedNamespace(
                firstTable.getSubquery(),
                firstTable.getAliasName()
            );
            return nestedNs != null ? nestedNs.getFinalTable() : null;
        }

        // If it's a join, get the first base table
        if (firstTable.getTableType() == gudusoft.gsqlparser.ETableSource.join) {
            return findFirstPhysicalTableFromJoin(firstTable);
        }

        return null;
    }

    @Override
    public List<TTable> getAllFinalTables() {
        // For non-UNION subqueries, return the single final table
        // Multiple tables only make sense for UNION queries where a column
        // can come from multiple branches (handled by UnionNamespace)
        TTable finalTable = getFinalTable();
        if (finalTable != null) {
            return Collections.singletonList(finalTable);
        }

        return Collections.emptyList();
    }

    @Override
    protected void doValidate() {
        // Extract columns from SELECT list
        columnSources = new LinkedHashMap<>();

        TResultColumnList selectList = subquery.getResultColumnList();
        if (selectList == null) {
            return;
        }

        for (int i = 0; i < selectList.size(); i++) {
            TResultColumn resultCol = selectList.getResultColumn(i);

            // Determine column name
            String colName = getColumnName(resultCol);
            if (colName == null) {
                // Unnamed expression, use ordinal
                colName = "col_" + (i + 1);
            }

            // Find the source table for this column (if it's a qualified column reference)
            TTable sourceTable = findSourceTableForResultColumn(resultCol);

            // Create column source with override table if found
            ColumnSource source;
            if (sourceTable != null) {
                source = new ColumnSource(
                    this,
                    colName,
                    resultCol,
                    1.0,  // Definite - from SELECT list
                    "subquery_select_list_qualified",
                    sourceTable  // Override table for proper tracing
                );
            } else {
                source = new ColumnSource(
                    this,
                    colName,
                    resultCol,
                    1.0,  // Definite - from SELECT list
                    "subquery_select_list"
                );
            }

            columnSources.put(colName, source);
        }
    }

    /**
     * Find the source table for a result column's expression.
     * For qualified column references like SUBS_CUST.FIRST_TP_ID, this returns
     * the SUBS_CUST table from the FROM clause.
     * For star columns like SUBSCR.*, this returns the SUBSCR table.
     *
     * <p>IMPORTANT: For subquery aliases, we return the subquery's TTable directly
     * and do NOT trace through. The column should be attributed to the subquery
     * alias (e.g., SUBS_CUST.FIRST_TP_ID), not to tables inside the subquery.</p>
     *
     * @param resultCol The result column to analyze
     * @return The source table, or null if not determinable
     */
    private TTable findSourceTableForResultColumn(TResultColumn resultCol) {
        if (resultCol == null || resultCol.getExpr() == null) {
            return null;
        }

        gudusoft.gsqlparser.nodes.TExpression expr = resultCol.getExpr();

        // Handle simple column reference (e.g., SUBS_CUST.FIRST_TP_ID or FIRST_TP_ID)
        if (expr.getExpressionType() == gudusoft.gsqlparser.EExpressionType.simple_object_name_t) {
            gudusoft.gsqlparser.nodes.TObjectName objName = expr.getObjectOperand();
            if (objName != null) {
                String tablePrefix = objName.getTableString();
                if (tablePrefix != null && !tablePrefix.isEmpty()) {
                    // Qualified reference - find the table
                    TTable table = findTableByAliasOrName(tablePrefix);
                    if (table != null) {
                        // Return the table directly - don't trace through subqueries
                        // This keeps columns attributed to their immediate source
                        // (e.g., SUBS_CUST.FIRST_TP_ID stays as SUBS_CUST.FIRST_TP_ID)
                        return table;
                    }
                }
            }
        }

        // Handle star column (e.g., SUBSCR.* or *)
        String colStr = resultCol.toString().trim();
        if (colStr.endsWith("*") && colStr.contains(".")) {
            // Qualified star - find the table
            int dotIndex = colStr.lastIndexOf('.');
            if (dotIndex > 0) {
                String tablePrefix = colStr.substring(0, dotIndex).trim();
                TTable table = findTableByAliasOrName(tablePrefix);
                if (table != null) {
                    // For star columns, we DO trace through to get all underlying columns
                    // This is different from regular columns because star needs to expand (use cached namespace)
                    if (table.getSubquery() != null) {
                        SubqueryNamespace nestedNs = getOrCreateNestedNamespace(
                            table.getSubquery(), table.getAliasName());
                        return nestedNs != null ? nestedNs.getFinalTable() : null;
                    }
                    return table;
                }
            }
        }

        return null;
    }

    /**
     * Extract column name from TResultColumn.
     * Handles aliases and expression columns.
     */
    private String getColumnName(TResultColumn resultCol) {
        // Check for alias
        if (resultCol.getAliasClause() != null &&
            resultCol.getAliasClause().getAliasName() != null) {
            return resultCol.getAliasClause().getAliasName().toString();
        }

        // Check for simple column reference
        if (resultCol.getExpr() != null) {
            gudusoft.gsqlparser.nodes.TExpression expr = resultCol.getExpr();
            // Check if it's a simple object reference
            if (expr.getExpressionType() == gudusoft.gsqlparser.EExpressionType.simple_object_name_t) {
                gudusoft.gsqlparser.nodes.TObjectName objName = expr.getObjectOperand();
                if (objName != null) {
                    return objName.getColumnNameOnly();
                }
            }
        }

        // Complex expression - no name
        return null;
    }

    @Override
    public ColumnLevel hasColumn(String columnName) {
        ensureValidated();

        // Check in SELECT list (explicit columns) — matcher-aware.
        if (containsColumnByMatcher(columnSources, columnName)) {
            return ColumnLevel.EXISTS;
        }

        // Check in inferred columns (from star push-down). The map is raw-
        // keyed (= ColumnSource.exposedName); the matcher-aware helper
        // applies per-dialect rules including SQL Server COLLATION_BASED.
        if (containsColumnByMatcher(inferredColumns, columnName)) {
            return ColumnLevel.EXISTS;
        }

        // If subquery has SELECT *, unknown columns MAYBE exist
        // They need to be resolved through star push-down
        if (hasStarColumn()) {
            return ColumnLevel.MAYBE;
        }

        return ColumnLevel.NOT_EXISTS;
    }

    public TSelectSqlStatement getSubquery() {
        return subquery;
    }

    @Override
    public TSelectSqlStatement getSelectStatement() {
        return subquery;
    }

    @Override
    public boolean hasStarColumn() {
        if (subquery == null || subquery.getResultColumnList() == null) {
            return false;
        }

        TResultColumnList selectList = subquery.getResultColumnList();
        for (int i = 0; i < selectList.size(); i++) {
            TResultColumn resultCol = selectList.getResultColumn(i);
            if (isStarResultColumn(resultCol)) {
                return true;
            }
        }
        return false;
    }

    private static boolean isStarResultColumn(TResultColumn resultCol) {
        if (resultCol == null) {
            return false;
        }
        if (resultCol.getExceptColumnList() != null
                || (resultCol.getReplaceExprAsIdentifiers() != null
                    && !resultCol.getReplaceExprAsIdentifiers().isEmpty())
                || (resultCol.getExprAsIdentifiers() != null
                    && !resultCol.getExprAsIdentifiers().isEmpty())) {
            return true;
        }
        if (resultCol.getExpr() != null
                && resultCol.getExpr().getExpressionType() == EExpressionType.simple_object_name_t
                && resultCol.getExpr().getObjectOperand() != null) {
            String starText = resultCol.getExpr().getObjectOperand().toString();
            if (starText != null) {
                starText = starText.trim();
                if ("*".equals(starText) || starText.endsWith(".*")) {
                    return true;
                }
            }
        }
        String text = resultCol.toString();
        if (text == null) {
            return false;
        }
        text = text.trim();
        return "*".equals(text) || text.endsWith(".*");
    }

    /**
     * Slice S4 (plan §5.5): a derived subquery's projection is authoritative
     * once validated AND at least one named (non-star) column exists. A
     * subquery whose only projection is {@code SELECT *} is reported as
     * METADATA_UNAVAILABLE here — S10 refines this when the underlying tables'
     * star expansion fully resolves into named columns.
     */
    @Override
    public MetadataState getMetadataState() {
        ensureValidated();
        if (columnSources != null && !columnSources.isEmpty() && !hasStarColumn()) {
            return MetadataState.FOUND;
        }
        return MetadataState.METADATA_UNAVAILABLE;
    }

    /**
     * Binding-diagnostic view of the derived-table output schema.
     *
     * <p>This deliberately avoids the legacy resolution fallbacks in
     * {@link #resolveColumn(String)}. Binding diagnostics need the SQL-visible
     * derived projection: named SELECT-list columns and already inferred star
     * output columns are visible; hidden base-table columns are not. When the
     * projection includes a star and the requested column is not already known,
     * the output is not fully authoritative, so callers must not report a
     * missing-output error.</p>
     */
    public ColumnLevel hasAuthoritativeOutputColumn(String columnName) {
        ensureValidated();

        if (columnName == null || columnName.isEmpty()) {
            return ColumnLevel.MAYBE;
        }

        if (containsColumnByMatcher(columnSources, columnName)) {
            return ColumnLevel.EXISTS;
        }

        if (containsColumnByMatcher(inferredColumns, columnName)) {
            return ColumnLevel.EXISTS;
        }

        if (hasStarColumn()) {
            return ColumnLevel.MAYBE;
        }

        if (columnSources != null && !columnSources.isEmpty()) {
            return ColumnLevel.NOT_EXISTS;
        }

        return ColumnLevel.MAYBE;
    }

    /**
     * Get the first star column (TResultColumn) from this subquery's SELECT list.
     * Used to track the definition node for columns inferred from the star.
     *
     * @return The star column, or null if no star column exists
     */
    public TResultColumn getStarColumn() {
        if (subquery == null || subquery.getResultColumnList() == null) {
            return null;
        }

        TResultColumnList selectList = subquery.getResultColumnList();
        for (int i = 0; i < selectList.size(); i++) {
            TResultColumn resultCol = selectList.getResultColumn(i);
            if (isStarResultColumn(resultCol)) {
                return resultCol;
            }
        }
        return null;
    }

    /**
     * Check if this subquery has an unqualified star with multiple tables.
     * In this case, columns are ambiguous and should NOT be auto-resolved.
     *
     * Example:
     * SELECT * FROM table_a, table_c  -- ambiguous, columns could come from either table
     * SELECT ta.* FROM table_a ta, table_c tc  -- NOT ambiguous, star is qualified
     * SELECT * FROM table_a JOIN table_c ON ... -- ambiguous, columns from both tables
     *
     * Uses getRelations() and TJoinExpr to properly count tables in JOINs.
     */
    public boolean hasAmbiguousStar() {
        if (subquery == null || subquery.getResultColumnList() == null) {
            return false;
        }

        // Count all sources (tables, subqueries, joins) using getRelations()
        int sourceCount = 0;
        java.util.ArrayList<TTable> relations = subquery.getRelations();
        if (relations != null) {
            for (TTable table : relations) {
                if (table == null) continue;
                if (table.getTableType() == gudusoft.gsqlparser.ETableSource.join) {
                    // For JOIN type, count tables from the JoinExpr
                    List<TTable> joinTables = new ArrayList<>();
                    collectPhysicalTablesFromJoinExpr(table.getJoinExpr(), joinTables);
                    sourceCount += joinTables.size();
                } else if (table.getTableType() == gudusoft.gsqlparser.ETableSource.objectname) {
                    sourceCount++;
                } else if (table.getSubquery() != null) {
                    // Subqueries also count as sources
                    sourceCount++;
                }
            }
        }

        // If only one source, not ambiguous
        if (sourceCount <= 1) {
            return false;
        }

        // Check for unqualified star (just "*" without table prefix)
        TResultColumnList selectList = subquery.getResultColumnList();
        for (int i = 0; i < selectList.size(); i++) {
            TResultColumn resultCol = selectList.getResultColumn(i);
            if (resultCol == null) continue;

            String colStr = resultCol.toString().trim();
            // Check if it's an unqualified star (just "*", not "ta.*")
            if (colStr.equals("*")) {
                return true;
            }
        }

        return false;
    }

    /**
     * Count the number of "real" tables in the FROM clause, excluding implicit lateral
     * derived tables (Teradata feature where references to undeclared tables in WHERE
     * clause create implicit table references).
     *
     * @return The count of real tables (excluding implicit lateral derived tables)
     */
    private int countRealTablesInFromClause() {
        if (subquery == null || subquery.tables == null) {
            return 0;
        }
        int count = 0;
        for (int i = 0; i < subquery.tables.size(); i++) {
            TTable table = subquery.tables.getTable(i);
            if (table != null &&
                table.getEffectType() != gudusoft.gsqlparser.ETableEffectType.tetImplicitLateralDerivedTable) {
                count++;
            }
        }
        return count;
    }

    /**
     * Get the single real table from the FROM clause when there's exactly one.
     * This is used as a fallback for star column push-down when resolveColumnInFromScope()
     * can't find the column (e.g., no SQLEnv metadata).
     *
     * @return The single real table, or null if there's not exactly one
     */
    private TTable getSingleRealTableFromFromClause() {
        if (subquery == null || subquery.tables == null) {
            return null;
        }
        TTable result = null;
        for (int i = 0; i < subquery.tables.size(); i++) {
            TTable table = subquery.tables.getTable(i);
            if (table != null &&
                table.getEffectType() != gudusoft.gsqlparser.ETableEffectType.tetImplicitLateralDerivedTable) {
                if (result != null) {
                    // Multiple tables - can't determine which one
                    return null;
                }
                result = table;
            }
        }
        // If the single table is itself a subquery, trace through it (use cached namespace)
        if (result != null && result.getSubquery() != null) {
            SubqueryNamespace nestedNs = getOrCreateNestedNamespace(result.getSubquery(), result.getAliasName());
            return nestedNs != null ? nestedNs.getFinalTable() : null;
        }

        // If the single table is a CTE reference, trace through the CTE to find the physical table
        // This is critical for star column push-down when SELECT * FROM cte_name
        if (result != null && result.isCTEName() && result.getCTE() != null) {
            gudusoft.gsqlparser.nodes.TCTE cte = result.getCTE();
            if (cte.getSubquery() != null) {
                // Create a temporary CTENamespace to trace through
                CTENamespace cteNs = new CTENamespace(
                    cte,
                    cte.getTableName() != null ? cte.getTableName().toString() : result.getName(),
                    cte.getSubquery(),
                    nameMatcher
                );
                cteNs.setReferencingTable(result);
                cteNs.validate();
                TTable tracedTable = cteNs.getFinalTable();
                // Return the physical table if found, otherwise return the CTE table as fallback
                return tracedTable != null ? tracedTable : result;
            }
        }

        return result;
    }

    @Override
    public boolean supportsDynamicInference() {
        // Support dynamic inference if this subquery has SELECT *
        return hasStarColumn();
    }

    @Override
    public boolean addInferredColumn(String columnName, double confidence, String evidence) {
        if (columnName == null || columnName.isEmpty()) {
            return false;
        }

        // Initialize maps if needed
        if (inferredColumns == null) {
            inferredColumns = new LinkedHashMap<>();
        }
        if (inferredColumnNames == null) {
            inferredColumnNames = new HashSet<>();
        }

        // Slice S1: dedupe through the matcher-aware helper so per-vendor
        // identifier rules govern whether two case-only-different inputs are
        // the same column. Without this, BigQuery / MySQL / SQL Server would
        // accept "MyCol" and "MYCOL" as two separate inferred entries and
        // downstream lookups would non-deterministically pick one of them.
        // Codex round 2: storage uses the raw (exposedName) key — two
        // matcher-distinct identifiers that happen to normalize equally
        // (Postgres "mycol" vs unquoted MYCOL) keep separate entries.
        if (containsColumnByMatcher(columnSources, columnName)) {
            return false;
        }
        if (containsColumnByMatcher(inferredColumns, columnName)) {
            return false;
        }

        // For star columns (SELECT *), trace the column to its underlying table.
        // This is critical for Teradata UPDATE...FROM...SET syntax where columns
        // reference subqueries with SELECT *.
        //
        // IMPORTANT: Only trace when there's exactly ONE real table (excluding implicit
        // lateral derived tables). If there are multiple tables, the column source
        // is ambiguous unless the star is qualified (handled elsewhere).
        TTable overrideTable = null;
        if (hasStarColumn() && !hasAmbiguousStar()) {
            // Count real tables (excluding implicit lateral derived tables)
            int realTableCount = countRealTablesInFromClause();
            if (realTableCount == 1) {
                ColumnSource fromSource = resolveColumnInFromScope(columnName);
                if (fromSource != null) {
                    // Get the table from the underlying source
                    overrideTable = fromSource.getFinalTable();
                    if (overrideTable == null && fromSource.getSourceNamespace() instanceof TableNamespace) {
                        // Try to get table directly from TableNamespace
                        overrideTable = ((TableNamespace) fromSource.getSourceNamespace()).getTable();
                    }
                }

                // Fallback: If resolveColumnInFromScope couldn't find the column (no SQLEnv metadata),
                // but we have exactly one table with SELECT *, assume the column comes from that table.
                // This is the expected behavior for star column push-down in most real-world scenarios
                // where the column name is not provable but is inferred from the context.
                if (overrideTable == null) {
                    overrideTable = getSingleRealTableFromFromClause();
                }
            }
        }

        // Create inferred column source with overrideTable if found
        // Note: Do NOT set the star column as definitionNode here.
        // The definitionNode is used by the formatter to attribute columns to tables.
        // Star-inferred columns should be attributed based on the overrideTable, not the star column.
        // The legacy sourceColumn for star-inferred columns should remain null since they don't
        // have a direct 1:1 relationship with a specific TResultColumn.
        ColumnSource source = new ColumnSource(
            this,
            columnName,
            null,  // No definition node for inferred columns
            confidence,
            evidence,
            overrideTable
        );

        inferredColumns.put(columnName, source);
        inferredColumnNames.add(columnName);
        return true;
    }

    @Override
    public Set<String> getInferredColumns() {
        if (inferredColumnNames == null) {
            return java.util.Collections.emptySet();
        }
        return java.util.Collections.unmodifiableSet(inferredColumnNames);
    }

    @Override
    public ColumnSource resolveColumn(String columnName) {
        ensureValidated();

        // First check explicit columns from this subquery's SELECT list
        ColumnSource source = super.resolveColumn(columnName);
        if (source != null) {
            return source;
        }

        // Then check inferred columns. Slice S1 + codex round 2: the map is
        // raw-keyed (= ColumnSource.exposedName), so the exact-match probe
        // is O(1) for the same identifier queried again. Matcher loop walks
        // values via getExposedName() so quote state is preserved.
        if (inferredColumns != null) {
            ColumnSource exact = inferredColumns.get(columnName);
            if (exact != null) {
                return exact;
            }
            for (ColumnSource entry : inferredColumns.values()) {
                String exposed = entry != null ? entry.getExposedName() : null;
                if (exposed != null && nameMatcher.matches(exposed, columnName)) {
                    return entry;
                }
            }
        }

        // For ambiguous star cases, try to find the column in explicit subquery sources
        // This handles cases like: SELECT * FROM table_a ta, (SELECT col1 FROM table_c) tc
        // where col1 can be uniquely traced to tc -> table_c
        if (hasAmbiguousStar()) {
            ColumnSource explicitSource = findColumnInExplicitSources(columnName);
            if (explicitSource != null) {
                return explicitSource;
            }

            // Try to trace the column through qualified references in the FROM clause
            // This handles JOINs where ON condition uses qualified names like table1.x
            ColumnSource tracedSource = traceColumnThroughQualifiedReferences(columnName);
            if (tracedSource != null) {
                return tracedSource;
            }

            // Try to resolve the column using sqlenv metadata from the FROM clause tables
            // This uses TableNamespace.resolveColumn() which has access to sqlenv
            ColumnSource fromScopeSource = resolveColumnInFromScope(columnName);
            if (fromScopeSource != null) {
                return fromScopeSource;
            }

            // Column not found in any explicit source, and we have multiple physical tables
            // Apply GUESS_COLUMN_STRATEGY to pick a table or leave unresolved
            return applyGuessColumnStrategy(columnName);
        }

        // If hasColumn returns MAYBE (we have SELECT *), auto-infer this column
        // This enables on-demand inference during resolution
        if (hasStarColumn()) {
            // Add as inferred column with moderate confidence
            // The confidence is lower because we're inferring from outer reference
            boolean added = addInferredColumn(columnName, 0.8, "auto_inferred_from_outer_reference");
            if (added && inferredColumns != null) {
                ColumnSource inferredSource = inferredColumns.get(columnName);
                if (inferredSource != null) {
                    return inferredSource;
                }
            }
        }

        return null;
    }

    /**
     * Resolve a column in the FROM scope (child namespaces).
     * This finds where a column originates from in the FROM clause tables/subqueries.
     *
     * <p>Unlike resolveColumn() which returns columns from THIS subquery's SELECT list,
     * this method looks into the FROM clause to find the underlying definition.</p>
     *
     * <p>IMPORTANT: For TableNamespace, this only returns columns that exist in
     * actual metadata (from DDL or SQLEnv). It does NOT use inferred columns.
     * This is critical for multi-table star resolution where we need to know
     * which table actually has the column based on metadata, not inference.</p>
     *
     * @param columnName The column name to find
     * @return ColumnSource from the FROM clause, or null if not found
     */
    public ColumnSource resolveColumnInFromScope(String columnName) {
        if (subquery == null || subquery.tables == null || columnName == null) {
            return null;
        }

        // Search through tables in the FROM clause
        for (int i = 0; i < subquery.tables.size(); i++) {
            TTable table = subquery.tables.getTable(i);
            if (table == null) continue;

            // For subqueries, look in their column sources (use cached namespace)
            if (table.getSubquery() != null) {
                SubqueryNamespace nestedNs = getOrCreateNestedNamespace(
                    table.getSubquery(), table.getAliasName());
                if (nestedNs != null) {
                    ColumnSource source = nestedNs.resolveColumn(columnName);
                    if (source != null) {
                        return source;
                    }
                }
            }

            // For physical tables, create a TableNamespace and resolve (use cached namespace)
            // IMPORTANT: Only return column if the table has actual metadata (DDL or SQLEnv)
            // This prevents inferring columns for ambiguous star resolution
            if (table.getTableType() == gudusoft.gsqlparser.ETableSource.objectname) {
                TableNamespace tableNs = getOrCreateTableNamespace(table);
                if (tableNs != null && tableNs.hasMetadata()) {
                    ColumnSource source = tableNs.resolveColumn(columnName);
                    if (source != null) {
                        return source;
                    }
                }
            }

            // For joins, recursively search
            if (table.getTableType() == gudusoft.gsqlparser.ETableSource.join) {
                ColumnSource source = resolveColumnInJoin(table.getJoinExpr(), columnName);
                if (source != null) {
                    return source;
                }
            }
        }

        return null;
    }

    /**
     * Resolve a column within a JOIN expression.
     * Only returns columns from tables with actual metadata (DDL or SQLEnv).
     */
    private ColumnSource resolveColumnInJoin(gudusoft.gsqlparser.nodes.TJoinExpr joinExpr, String columnName) {
        if (joinExpr == null) return null;

        // Check left table (use cached namespaces)
        TTable leftTable = joinExpr.getLeftTable();
        if (leftTable != null) {
            if (leftTable.getSubquery() != null) {
                SubqueryNamespace nestedNs = getOrCreateNestedNamespace(
                    leftTable.getSubquery(), leftTable.getAliasName());
                if (nestedNs != null) {
                    ColumnSource source = nestedNs.resolveColumn(columnName);
                    if (source != null) {
                        return source;
                    }
                }
            } else if (leftTable.getTableType() == gudusoft.gsqlparser.ETableSource.objectname) {
                TableNamespace tableNs = getOrCreateTableNamespace(leftTable);
                // Only return if table has metadata - don't use inferred columns
                if (tableNs != null && tableNs.hasMetadata()) {
                    ColumnSource source = tableNs.resolveColumn(columnName);
                    if (source != null) {
                        return source;
                    }
                }
            } else if (leftTable.getTableType() == gudusoft.gsqlparser.ETableSource.join) {
                ColumnSource source = resolveColumnInJoin(leftTable.getJoinExpr(), columnName);
                if (source != null) {
                    return source;
                }
            }
        }

        // Check right table (use cached namespaces)
        TTable rightTable = joinExpr.getRightTable();
        if (rightTable != null) {
            if (rightTable.getSubquery() != null) {
                SubqueryNamespace nestedNs = getOrCreateNestedNamespace(
                    rightTable.getSubquery(), rightTable.getAliasName());
                if (nestedNs != null) {
                    ColumnSource source = nestedNs.resolveColumn(columnName);
                    if (source != null) {
                        return source;
                    }
                }
            } else if (rightTable.getTableType() == gudusoft.gsqlparser.ETableSource.objectname) {
                TableNamespace tableNs = getOrCreateTableNamespace(rightTable);
                // Only return if table has metadata - don't use inferred columns
                if (tableNs != null && tableNs.hasMetadata()) {
                    ColumnSource source = tableNs.resolveColumn(columnName);
                    if (source != null) {
                        return source;
                    }
                }
            } else if (rightTable.getTableType() == gudusoft.gsqlparser.ETableSource.join) {
                ColumnSource source = resolveColumnInJoin(rightTable.getJoinExpr(), columnName);
                if (source != null) {
                    return source;
                }
            }
        }

        return null;
    }

    /**
     * Apply GUESS_COLUMN_STRATEGY to pick a table for an ambiguous column.
     * Used when a star column could come from multiple tables.
     *
     * For NOT_PICKUP strategy, returns a ColumnSource with all candidate tables
     * stored so end users can access them via getCandidateTables().
     *
     * @param columnName The column name to resolve
     * @return ColumnSource (may have multiple candidate tables for NOT_PICKUP)
     */
    private ColumnSource applyGuessColumnStrategy(String columnName) {
        // Get the strategy from instance field (which falls back to TBaseType if not set)
        int strategy = getGuessColumnStrategy();

        // Collect all physical tables from the FROM clause
        List<TTable> physicalTables = new ArrayList<>();
        java.util.ArrayList<TTable> relations = subquery.getRelations();
        if (relations != null) {
            for (TTable table : relations) {
                if (table == null) continue;
                if (table.getTableType() == gudusoft.gsqlparser.ETableSource.join) {
                    collectPhysicalTablesFromJoinExpr(table.getJoinExpr(), physicalTables);
                } else if (table.getTableType() == gudusoft.gsqlparser.ETableSource.objectname) {
                    physicalTables.add(table);
                }
            }
        }

        if (physicalTables.isEmpty()) {
            return null;
        }

        if (strategy == gudusoft.gsqlparser.TBaseType.GUESS_COLUMN_STRATEGY_NOT_PICKUP) {
            // Don't pick any table - return null to treat column as "missed"
            // This is the expected behavior for ambiguous star columns with NOT_PICKUP strategy
            if (gudusoft.gsqlparser.TBaseType.DUMP_RESOLVER_LOG_TO_CONSOLE) {
                System.out.println("[GUESS_COLUMN_STRATEGY] Column '" + columnName +
                    "' is ambiguous across " + physicalTables.size() + " tables (NOT_PICKUP strategy - treating as missed)");
            }
            return null;
        }

        // Pick the table based on strategy
        TTable pickedTable;
        String evidence;
        if (strategy == gudusoft.gsqlparser.TBaseType.GUESS_COLUMN_STRATEGY_NEAREST) {
            // Pick the first table (nearest in FROM clause order)
            pickedTable = physicalTables.get(0);
            evidence = "guess_strategy_nearest";
        } else if (strategy == gudusoft.gsqlparser.TBaseType.GUESS_COLUMN_STRATEGY_FARTHEST) {
            // Pick the last table (farthest in FROM clause order)
            pickedTable = physicalTables.get(physicalTables.size() - 1);
            evidence = "guess_strategy_farthest";
        } else {
            // Unknown strategy - don't pick
            return null;
        }

        if (gudusoft.gsqlparser.TBaseType.DUMP_RESOLVER_LOG_TO_CONSOLE) {
            System.out.println("[GUESS_COLUMN_STRATEGY] Column '" + columnName +
                "' picked from table '" + pickedTable.getName() + "' using " + evidence);
        }

        // Create ColumnSource with the picked table
        return new ColumnSource(
            this,
            columnName,
            null,
            0.7,  // Lower confidence since it's a guess
            evidence,
            pickedTable  // Override table for getFinalTable()
        );
    }

    /**
     * Find a column in explicit subquery sources within the FROM clause.
     * This is used for ambiguous star cases where we try to find the column
     * in subqueries that have explicit columns before giving up.
     */
    private ColumnSource findColumnInExplicitSources(String columnName) {
        if (subquery == null || subquery.tables == null) {
            return null;
        }

        ColumnSource foundSource = null;
        List<TTable> physicalTables = new ArrayList<>();

        for (int i = 0; i < subquery.tables.size(); i++) {
            TTable table = subquery.tables.getTable(i);
            if (table == null) continue;

            // For join tables, collect physical tables from within the join
            if (table.getTableType() == gudusoft.gsqlparser.ETableSource.join) {
                collectPhysicalTablesFromJoin(table, physicalTables);
                continue;
            }

            // Check if this is a subquery with explicit columns
            if (table.getSubquery() != null) {
                TResultColumnList resultCols = table.getSubquery().getResultColumnList();
                if (resultCols != null) {
                    for (int j = 0; j < resultCols.size(); j++) {
                        TResultColumn rc = resultCols.getResultColumn(j);
                        String rcName = getResultColumnName(rc);
                        if (rcName != null && nameMatcher.matches(rcName, columnName)) {
                            // Found in this subquery - trace to its final table (use cached namespace)
                            SubqueryNamespace nestedNs = getOrCreateNestedNamespace(
                                table.getSubquery(), table.getAliasName());
                            if (nestedNs != null) {
                                ColumnSource nestedSource = nestedNs.resolveColumn(columnName);
                                if (nestedSource != null) {
                                    if (foundSource != null) {
                                        // Found in multiple sources - ambiguous
                                        return null;
                                    }
                                    foundSource = nestedSource;
                                }
                            }
                        }
                    }
                }
            } else if (table.getTableType() == gudusoft.gsqlparser.ETableSource.objectname) {
                // Physical table - add to list
                physicalTables.add(table);
            }
        }

        // If found in an explicit subquery source, return it
        if (foundSource != null) {
            return foundSource;
        }

        // If column not found in any explicit source, and there's exactly one physical table,
        // infer from that table (use cached namespace)
        if (physicalTables.size() == 1) {
            // Create inferred column source from the single physical table
            TableNamespace tableNs = getOrCreateTableNamespace(physicalTables.get(0));
            return tableNs != null ? tableNs.resolveColumn(columnName) : null;
        }

        return null;
    }

    /**
     * Trace a column through qualified references in the subquery.
     * This looks for qualified column references (like table1.x) in the subquery
     * that match the requested column name, and returns a ColumnSource from the
     * corresponding table.
     *
     * Uses getRelations() and TJoinExpr to properly traverse JOIN structures.
     */
    private ColumnSource traceColumnThroughQualifiedReferences(String columnName) {
        if (subquery == null || columnName == null) {
            return null;
        }

        // Debug logging
        if (gudusoft.gsqlparser.TBaseType.DUMP_RESOLVER_LOG_TO_CONSOLE) {
            System.out.println("TRACE: traceColumnThroughQualifiedReferences called for column: " + columnName);
        }

        // Collect all physical tables using getRelations() - the proper way to get all tables
        List<TTable> physicalTables = new ArrayList<>();
        java.util.ArrayList<TTable> relations = subquery.getRelations();
        if (relations != null) {
            for (TTable table : relations) {
                if (table == null) continue;
                if (table.getTableType() == gudusoft.gsqlparser.ETableSource.join) {
                    // For JOIN type, collect tables from the JoinExpr
                    collectPhysicalTablesFromJoinExpr(table.getJoinExpr(), physicalTables);
                } else if (table.getTableType() == gudusoft.gsqlparser.ETableSource.objectname) {
                    physicalTables.add(table);
                }
            }
        }

        if (physicalTables.isEmpty()) {
            return null;
        }

        // Look for qualified column references in JOIN ON conditions using TJoinExpr
        TTable matchedTable = null;
        int matchCount = 0;

        // Check WHERE clause
        if (subquery.getWhereClause() != null && subquery.getWhereClause().getCondition() != null) {
            TTable found = findTableFromQualifiedColumnInExpression(
                subquery.getWhereClause().getCondition(), columnName, physicalTables);
            if (found != null) {
                matchedTable = found;
                matchCount++;
            }
        }

        // Check JOIN conditions using getRelations() and TJoinExpr
        if (relations != null) {
            for (TTable table : relations) {
                if (table != null && table.getTableType() == gudusoft.gsqlparser.ETableSource.join) {
                    gudusoft.gsqlparser.nodes.TJoinExpr joinExpr = table.getJoinExpr();
                    if (joinExpr != null) {
                        TTable found = findTableFromQualifiedColumnInJoinExpr(joinExpr, columnName, physicalTables);
                        if (found != null && (matchedTable == null || found != matchedTable)) {
                            if (matchedTable != null && found != matchedTable) {
                                matchCount++;
                            } else {
                                matchedTable = found;
                                matchCount = 1;
                            }
                        }
                    }
                }
            }
        }

        // If found in exactly one table, return column source with SubqueryNamespace as source
        // The overrideTable ensures getFinalTable() returns the traced table
        if (matchCount == 1 && matchedTable != null) {
            if (gudusoft.gsqlparser.TBaseType.DUMP_RESOLVER_LOG_TO_CONSOLE) {
                System.out.println("TRACE: Found column " + columnName + " in table: " + matchedTable.getName());
            }
            // Store the traced table mapping for reference
            if (tracedColumnTables == null) {
                tracedColumnTables = new java.util.LinkedHashMap<>();
            }
            tracedColumnTables.put(columnName, matchedTable);

            // Return a ColumnSource with this SubqueryNamespace as source (for priority)
            // and the traced table as overrideTable (for correct getFinalTable())
            ColumnSource source = new ColumnSource(
                this,
                columnName,
                null,
                0.95,  // High confidence - traced through qualified reference
                "traced_through_qualified_ref",
                matchedTable  // Override table for getFinalTable()
            );
            return source;
        }

        if (gudusoft.gsqlparser.TBaseType.DUMP_RESOLVER_LOG_TO_CONSOLE) {
            System.out.println("TRACE: Column " + columnName + " NOT found in qualified references, matchCount=" + matchCount);
        }
        return null;
    }

    /**
     * Collect physical tables from a TJoinExpr recursively.
     */
    private void collectPhysicalTablesFromJoinExpr(gudusoft.gsqlparser.nodes.TJoinExpr joinExpr, List<TTable> physicalTables) {
        if (joinExpr == null) return;

        // Process left table
        TTable leftTable = joinExpr.getLeftTable();
        if (leftTable != null) {
            if (leftTable.getTableType() == gudusoft.gsqlparser.ETableSource.join && leftTable.getJoinExpr() != null) {
                collectPhysicalTablesFromJoinExpr(leftTable.getJoinExpr(), physicalTables);
            } else if (leftTable.getTableType() == gudusoft.gsqlparser.ETableSource.objectname) {
                physicalTables.add(leftTable);
            }
        }

        // Process right table
        TTable rightTable = joinExpr.getRightTable();
        if (rightTable != null) {
            if (rightTable.getTableType() == gudusoft.gsqlparser.ETableSource.join && rightTable.getJoinExpr() != null) {
                collectPhysicalTablesFromJoinExpr(rightTable.getJoinExpr(), physicalTables);
            } else if (rightTable.getTableType() == gudusoft.gsqlparser.ETableSource.objectname) {
                physicalTables.add(rightTable);
            }
        }
    }

    /**
     * Find table from qualified column references in a TJoinExpr and its nested joins.
     */
    private TTable findTableFromQualifiedColumnInJoinExpr(
            gudusoft.gsqlparser.nodes.TJoinExpr joinExpr,
            String columnName,
            List<TTable> physicalTables) {
        if (joinExpr == null) return null;

        // Check ON condition of this join
        if (joinExpr.getOnCondition() != null) {
            if (gudusoft.gsqlparser.TBaseType.DUMP_RESOLVER_LOG_TO_CONSOLE) {
                System.out.println("TRACE: Checking TJoinExpr ON condition: " + joinExpr.getOnCondition());
            }
            TTable found = findTableFromQualifiedColumnInExpression(
                joinExpr.getOnCondition(), columnName, physicalTables);
            if (found != null) return found;
        }

        // Recursively check nested joins on the left side
        TTable leftTable = joinExpr.getLeftTable();
        if (leftTable != null && leftTable.getTableType() == gudusoft.gsqlparser.ETableSource.join
            && leftTable.getJoinExpr() != null) {
            TTable found = findTableFromQualifiedColumnInJoinExpr(leftTable.getJoinExpr(), columnName, physicalTables);
            if (found != null) return found;
        }

        // Recursively check nested joins on the right side
        TTable rightTable = joinExpr.getRightTable();
        if (rightTable != null && rightTable.getTableType() == gudusoft.gsqlparser.ETableSource.join
            && rightTable.getJoinExpr() != null) {
            TTable found = findTableFromQualifiedColumnInJoinExpr(rightTable.getJoinExpr(), columnName, physicalTables);
            if (found != null) return found;
        }

        return null;
    }

    /**
     * Find table from qualified column references in an expression.
     * Looks for patterns like table1.column_name and returns the matching table.
     * Uses iterative DFS to avoid StackOverflowError for deeply nested expression chains.
     */
    private TTable findTableFromQualifiedColumnInExpression(
            gudusoft.gsqlparser.nodes.TExpression expr,
            String columnName,
            List<TTable> physicalTables) {
        if (expr == null) return null;

        Deque<gudusoft.gsqlparser.nodes.TExpression> stack = new ArrayDeque<>();
        stack.push(expr);
        while (!stack.isEmpty()) {
            gudusoft.gsqlparser.nodes.TExpression current = stack.pop();
            if (current == null) continue;

            // Check if this expression is a qualified column reference
            if (current.getExpressionType() == gudusoft.gsqlparser.EExpressionType.simple_object_name_t) {
                gudusoft.gsqlparser.nodes.TObjectName objName = current.getObjectOperand();
                if (objName != null) {
                    String colName = objName.getColumnNameOnly();
                    String tablePrefix = objName.getTableString();

                    if (colName != null && nameMatcher.matches(colName, columnName) &&
                        tablePrefix != null && !tablePrefix.isEmpty()) {
                        // Found a qualified reference to this column - find the matching table
                        for (TTable table : physicalTables) {
                            String tableName = table.getName();
                            String tableAlias = table.getAliasName();

                            if ((tableName != null && nameMatcher.matches(tableName, tablePrefix)) ||
                                (tableAlias != null && nameMatcher.matches(tableAlias, tablePrefix))) {
                                return table;
                            }
                        }
                    }
                }
            }

            // Push sub-expressions onto stack (right first so left is processed first)
            if (current.getRightOperand() != null) stack.push(current.getRightOperand());
            if (current.getLeftOperand() != null) stack.push(current.getLeftOperand());
        }

        return null;
    }

    /**
     * Find table from qualified column references in JOIN conditions.
     */
    private TTable findTableFromQualifiedColumnInJoin(
            TTable joinTable,
            String columnName,
            List<TTable> physicalTables) {
        if (joinTable == null) return null;

        gudusoft.gsqlparser.nodes.TJoinExpr joinExpr = joinTable.getJoinExpr();
        if (joinExpr == null) {
            if (gudusoft.gsqlparser.TBaseType.DUMP_RESOLVER_LOG_TO_CONSOLE) {
                System.out.println("TRACE: findTableFromQualifiedColumnInJoin - joinExpr is null");
            }
            return null;
        }

        // Check the ON condition of this join
        if (joinExpr.getOnCondition() != null) {
            if (gudusoft.gsqlparser.TBaseType.DUMP_RESOLVER_LOG_TO_CONSOLE) {
                System.out.println("TRACE: Checking ON condition for column " + columnName + ": " + joinExpr.getOnCondition());
            }
            TTable found = findTableFromQualifiedColumnInExpression(
                joinExpr.getOnCondition(), columnName, physicalTables);
            if (found != null) return found;
        } else {
            if (gudusoft.gsqlparser.TBaseType.DUMP_RESOLVER_LOG_TO_CONSOLE) {
                System.out.println("TRACE: findTableFromQualifiedColumnInJoin - ON condition is null");
            }
        }

        // Recursively check nested joins on the left side
        if (joinExpr.getLeftTable() != null &&
            joinExpr.getLeftTable().getTableType() == gudusoft.gsqlparser.ETableSource.join) {
            TTable found = findTableFromQualifiedColumnInJoin(
                joinExpr.getLeftTable(), columnName, physicalTables);
            if (found != null) return found;
        }

        // Recursively check nested joins on the right side
        if (joinExpr.getRightTable() != null &&
            joinExpr.getRightTable().getTableType() == gudusoft.gsqlparser.ETableSource.join) {
            TTable found = findTableFromQualifiedColumnInJoin(
                joinExpr.getRightTable(), columnName, physicalTables);
            if (found != null) return found;
        }

        return null;
    }

    /**
     * Collect all physical tables from a JOIN expression recursively.
     */
    private void collectPhysicalTablesFromJoin(TTable joinTable, List<TTable> physicalTables) {
        if (joinTable == null) return;

        gudusoft.gsqlparser.nodes.TJoinExpr joinExpr = joinTable.getJoinExpr();
        if (joinExpr == null) return;

        // Process left side
        TTable leftTable = joinExpr.getLeftTable();
        if (leftTable != null) {
            if (leftTable.getTableType() == gudusoft.gsqlparser.ETableSource.join) {
                collectPhysicalTablesFromJoin(leftTable, physicalTables);
            } else if (leftTable.getTableType() == gudusoft.gsqlparser.ETableSource.objectname) {
                physicalTables.add(leftTable);
            } else if (leftTable.getSubquery() != null) {
                // For subqueries within joins, we could trace through but for now just note it exists
                physicalTables.add(leftTable);
            }
        }

        // Process right side
        TTable rightTable = joinExpr.getRightTable();
        if (rightTable != null) {
            if (rightTable.getTableType() == gudusoft.gsqlparser.ETableSource.join) {
                collectPhysicalTablesFromJoin(rightTable, physicalTables);
            } else if (rightTable.getTableType() == gudusoft.gsqlparser.ETableSource.objectname) {
                physicalTables.add(rightTable);
            } else if (rightTable.getSubquery() != null) {
                physicalTables.add(rightTable);
            }
        }
    }

    /**
     * Get the name of a result column (either alias or column name)
     */
    private String getResultColumnName(TResultColumn rc) {
        if (rc == null) return null;

        // Check for alias
        if (rc.getAliasClause() != null && rc.getAliasClause().getAliasName() != null) {
            return rc.getAliasClause().getAliasName().toString();
        }

        // Check for star - not a named column
        String colStr = rc.toString().trim();
        if (colStr.endsWith("*")) {
            return null;
        }

        // Check for simple column reference
        if (rc.getExpr() != null) {
            gudusoft.gsqlparser.nodes.TExpression expr = rc.getExpr();
            if (expr.getExpressionType() == gudusoft.gsqlparser.EExpressionType.simple_object_name_t) {
                gudusoft.gsqlparser.nodes.TObjectName objName = expr.getObjectOperand();
                if (objName != null) {
                    return objName.getColumnNameOnly();
                }
            }
        }

        return null;
    }

    @Override
    public String toString() {
        int totalColumns = (columnSources != null ? columnSources.size() : 0) +
                          (inferredColumns != null ? inferredColumns.size() : 0);
        return "SubqueryNamespace(" + getDisplayName() + ", columns=" + totalColumns +
               ", inferred=" + (inferredColumns != null ? inferredColumns.size() : 0) + ")";
    }
}