package gudusoft.gsqlparser.resolver2.model;

import gudusoft.gsqlparser.EExpressionType;
import gudusoft.gsqlparser.nodes.TExpression;
import gudusoft.gsqlparser.nodes.TObjectName;
import gudusoft.gsqlparser.nodes.TParseTreeNode;
import gudusoft.gsqlparser.nodes.TResultColumn;
import gudusoft.gsqlparser.nodes.TTable;
import gudusoft.gsqlparser.resolver2.inference.EvidenceType;
import gudusoft.gsqlparser.resolver2.namespace.INamespace;
import gudusoft.gsqlparser.resolver2.namespace.SubqueryNamespace;
import gudusoft.gsqlparser.resolver2.namespace.CTENamespace;
import gudusoft.gsqlparser.resolver2.namespace.UnionNamespace;
import gudusoft.gsqlparser.stmt.TSelectSqlStatement;

import java.util.Collections;
import java.util.List;

/**
 * Represents the source of a column reference.
 * Tracks where a column comes from, including intermediate transformations
 * through subqueries and CTEs.
 *
 * Design principles:
 * 1. Immutable - once created, cannot be modified
 * 2. Recursive - can trace back through subquery/CTE layers
 * 3. Confidence-scored - supports evidence-based inference
 */
public class ColumnSource {
    /** The namespace where this column is exposed (e.g., subquery, table) */
    private final INamespace sourceNamespace;

    /** The name by which this column is exposed in the namespace */
    private final String exposedName;

    /** The AST node where this column is defined (TResultColumn, TTableColumn, etc.) */
    private final TParseTreeNode definitionNode;

    /** Location information for the definition */
    private final SourceLocation definitionLocation;

    /**
     * Confidence score [0.0, 1.0]:
     * - 1.0: Definite (from metadata or explicit definition)
     * - 0.7-0.9: High confidence inference (strong evidence)
     * - 0.5-0.7: Medium confidence inference (some evidence)
     * - 0.0-0.5: Low confidence guess
     */
    private final double confidence;

    /**
     * Evidence that supports this resolution.
     * Used for debugging and explaining inference decisions.
     *
     * @deprecated Use {@link #evidenceDetail} instead. This field is kept for backward
     *             compatibility and will be derived from evidenceDetail if not explicitly set.
     */
    private final String evidence;

    /**
     * Structured evidence detail for this resolution.
     * Provides type-safe evidence with confidence weight and source traceability.
     * This is the preferred way to access resolution evidence.
     *
     * @see ResolutionEvidence
     */
    private final ResolutionEvidence evidenceDetail;

    /**
     * Override table for traced columns.
     * When set, getFinalTable() returns this instead of namespace's table.
     */
    private final TTable overrideTable;

    /**
     * Candidate tables for ambiguous columns.
     * When a column could come from multiple tables (e.g., SELECT * FROM t1, t2),
     * this list contains all possible source tables so end users can access them.
     */
    private final List<TTable> candidateTables;

    /**
     * Field path for deep/record field access (e.g., struct.field.subfield).
     *
     * <p>When a column reference includes field access beyond the base column,
     * this captures the field path. For example, in {@code customer.address.city},
     * if base column is {@code customer}, fieldPath contains {@code ["address", "city"]}.</p>
     *
     * <p>This field is null or empty for regular column references without field access.</p>
     *
     * @see FieldPath
     */
    private final FieldPath fieldPath;

    public ColumnSource(INamespace sourceNamespace,
                       String exposedName,
                       TParseTreeNode definitionNode,
                       double confidence,
                       String evidence) {
        this(sourceNamespace, exposedName, definitionNode, confidence, evidence, null, null);
    }

    public ColumnSource(INamespace sourceNamespace,
                       String exposedName,
                       TParseTreeNode definitionNode,
                       double confidence,
                       String evidence,
                       TTable overrideTable) {
        this(sourceNamespace, exposedName, definitionNode, confidence, evidence, overrideTable, null);
    }

    public ColumnSource(INamespace sourceNamespace,
                       String exposedName,
                       TParseTreeNode definitionNode,
                       double confidence,
                       String evidence,
                       TTable overrideTable,
                       List<TTable> candidateTables) {
        this(sourceNamespace, exposedName, definitionNode, confidence, evidence, overrideTable, candidateTables, null, null);
    }

    /**
     * Full constructor with all fields including ResolutionEvidence.
     */
    public ColumnSource(INamespace sourceNamespace,
                       String exposedName,
                       TParseTreeNode definitionNode,
                       double confidence,
                       String evidence,
                       TTable overrideTable,
                       List<TTable> candidateTables,
                       ResolutionEvidence evidenceDetail) {
        this(sourceNamespace, exposedName, definitionNode, confidence, evidence, overrideTable, candidateTables, evidenceDetail, null);
    }

    /**
     * Full constructor with all fields including ResolutionEvidence and FieldPath.
     *
     * @param sourceNamespace The namespace where this column is exposed
     * @param exposedName The name by which this column is exposed
     * @param definitionNode The AST node where this column is defined
     * @param confidence Confidence score [0.0, 1.0]
     * @param evidence Evidence string for this resolution
     * @param overrideTable Override table for traced columns
     * @param candidateTables Candidate tables for ambiguous columns
     * @param evidenceDetail Structured evidence detail
     * @param fieldPath Field path for deep/record field access
     */
    public ColumnSource(INamespace sourceNamespace,
                       String exposedName,
                       TParseTreeNode definitionNode,
                       double confidence,
                       String evidence,
                       TTable overrideTable,
                       List<TTable> candidateTables,
                       ResolutionEvidence evidenceDetail,
                       FieldPath fieldPath) {
        this.sourceNamespace = sourceNamespace;
        this.exposedName = exposedName;
        this.definitionNode = definitionNode;
        this.definitionLocation = definitionNode != null
            ? new SourceLocation(definitionNode)
            : null;
        this.confidence = Math.max(0.0, Math.min(1.0, confidence));
        this.evidence = evidence;
        this.overrideTable = overrideTable;
        this.candidateTables = candidateTables != null ? Collections.unmodifiableList(candidateTables) : null;
        this.fieldPath = fieldPath;
        // If evidenceDetail not provided, create from legacy evidence
        if (evidenceDetail != null) {
            this.evidenceDetail = evidenceDetail;
        } else if (evidence != null) {
            this.evidenceDetail = ResolutionEvidence.fromLegacyEvidence(evidence, confidence, definitionNode);
        } else {
            this.evidenceDetail = null;
        }
    }

    /**
     * Constructor with ResolutionEvidence (preferred for new code).
     */
    public ColumnSource(INamespace sourceNamespace,
                       String exposedName,
                       TParseTreeNode definitionNode,
                       ResolutionEvidence evidenceDetail) {
        this(sourceNamespace, exposedName, definitionNode,
             evidenceDetail != null ? evidenceDetail.getWeight() : 1.0,
             evidenceDetail != null ? evidenceDetail.toLegacyEvidence() : "metadata",
             null, null, evidenceDetail);
    }

    /**
     * Constructor with ResolutionEvidence and override table.
     */
    public ColumnSource(INamespace sourceNamespace,
                       String exposedName,
                       TParseTreeNode definitionNode,
                       ResolutionEvidence evidenceDetail,
                       TTable overrideTable) {
        this(sourceNamespace, exposedName, definitionNode,
             evidenceDetail != null ? evidenceDetail.getWeight() : 1.0,
             evidenceDetail != null ? evidenceDetail.toLegacyEvidence() : "metadata",
             overrideTable, null, evidenceDetail);
    }

    /**
     * Constructor for definite matches (confidence = 1.0)
     */
    public ColumnSource(INamespace sourceNamespace,
                       String exposedName,
                       TParseTreeNode definitionNode) {
        this(sourceNamespace, exposedName, definitionNode, 1.0, "metadata");
    }

    public INamespace getSourceNamespace() {
        return sourceNamespace;
    }

    public String getExposedName() {
        return exposedName;
    }

    public TParseTreeNode getDefinitionNode() {
        return definitionNode;
    }

    public SourceLocation getDefinitionLocation() {
        return definitionLocation;
    }

    public double getConfidence() {
        return confidence;
    }

    public String getEvidence() {
        return evidence;
    }

    /**
     * Get the structured evidence detail for this resolution.
     *
     * <p>This is the preferred way to access resolution evidence as it provides:
     * <ul>
     *   <li>Type-safe evidence type (enum)</li>
     *   <li>Confidence weight with clear semantics</li>
     *   <li>Source location for traceability</li>
     *   <li>Human-readable messages</li>
     * </ul>
     *
     * @return The structured evidence detail, or null if not available
     */
    public ResolutionEvidence getEvidenceDetail() {
        return evidenceDetail;
    }

    /**
     * Get the evidence type from the structured evidence detail.
     * Convenience method for common use cases.
     *
     * @return The evidence type, or null if no evidence detail
     */
    public EvidenceType getEvidenceType() {
        return evidenceDetail != null ? evidenceDetail.getType() : null;
    }

    /**
     * Check if this resolution has definite evidence (not inferred).
     * Definite evidence comes from DDL, metadata, or explicit definitions.
     *
     * @return true if evidence is definite
     */
    public boolean hasDefiniteEvidence() {
        if (evidenceDetail != null) {
            return evidenceDetail.isDefinite();
        }
        // Fallback: check legacy evidence and confidence
        if (confidence >= 1.0) {
            return true;
        }
        if (evidence != null) {
            String lower = evidence.toLowerCase();
            return lower.contains("metadata") || lower.contains("ddl") ||
                   lower.contains("explicit") || lower.contains("insert_column");
        }
        return false;
    }

    /**
     * Get the <b>final</b> physical table this column originates from after tracing
     * through all subqueries and CTEs.
     *
     * <h3>Semantic Difference: getFinalTable() vs TObjectName.getSourceTable()</h3>
     * <ul>
     *   <li><b>getFinalTable()</b> (this method): The final physical table after
     *       recursively tracing through all subqueries and CTEs. Use this for data lineage.</li>
     *   <li><b>TObjectName.getSourceTable()</b>: The immediate source in the current scope.
     *       For a column from a subquery, this points to the subquery's TTable itself.</li>
     * </ul>
     *
     * <h3>Example</h3>
     * <pre>{@code
     * SELECT title FROM (SELECT * FROM books) sub
     *
     * For the 'title' column in outer SELECT:
     * - TObjectName.getSourceTable() → TTable for subquery 'sub' (immediate source)
     * - ColumnSource.getFinalTable() → TTable for 'books' (final physical table)
     * }</pre>
     *
     * <p>For calculated columns in subqueries (expressions like {@code START_DT - x AS alias}),
     * this returns null because such calculated columns don't originate from a physical
     * table - they are derived values computed in the subquery.</p>
     *
     * <p>For aliased columns in subqueries (e.g., {@code SELECT t.id AS col1 FROM my_table t}),
     * this traces through the alias to find the physical table, because the data still
     * originates from the physical table even though the column has been renamed.</p>
     *
     * <p>Note: For CTEs, calculated columns ARE the CTE's own columns, so they trace
     * to the CTE itself (handled by CTENamespace.getFinalTable()).</p>
     *
     * @return The physical table, or null if unable to determine or if calculated in subquery
     * @see gudusoft.gsqlparser.nodes.TObjectName#getSourceTable()
     */
    public TTable getFinalTable() {
        if (sourceNamespace == null && overrideTable == null) {
            return null;
        }

        // For SubqueryNamespace: calculated columns should NOT trace to base table
        // They are derived values that don't exist in the underlying physical table
        // Example: SELECT *, expr AS alias FROM table - alias is calculated, not from table
        //
        // IMPORTANT: Check BEFORE overrideTable to prevent alias/calculated columns
        // from being traced to base tables even when overrideTable is explicitly set
        if (sourceNamespace instanceof SubqueryNamespace && isCalculatedColumn()) {
            return null;
        }

        // For CTENamespace: calculated columns ARE the CTE's own columns
        // They should trace to the CTE itself (referencing table), NOT to underlying base tables
        // Example: WITH cte AS (SELECT SUM(x) AS total FROM t) SELECT total FROM cte
        // The 'total' column traces to 'cte', not to 't'
        if (sourceNamespace instanceof CTENamespace && isCalculatedColumn()) {
            return ((CTENamespace) sourceNamespace).getReferencingTable();
        }

        // For SubqueryNamespace: column aliases - trace through to find the source table.
        // The alias changes the column name but the data still originates from a physical table.
        // Example: SELECT t.id AS col1 FROM my_table t - col1's data comes from my_table
        if (sourceNamespace instanceof SubqueryNamespace && isColumnAlias()) {
            return traceColumnAliasThroughSubquery((SubqueryNamespace) sourceNamespace);
        }

        // For CTENamespace: column aliases ARE the CTE's own columns
        // They should trace to the CTE itself, NOT to underlying base tables
        // Example: WITH cte AS (SELECT x AS y FROM t) SELECT y FROM cte
        // The 'y' column traces to 'cte', not to 't'
        if (sourceNamespace instanceof CTENamespace && isColumnAlias()) {
            return ((CTENamespace) sourceNamespace).getReferencingTable();
        }

        // For CTENamespace: explicit column names ARE the CTE's own columns
        // They should trace to the CTE itself, NOT to underlying base tables
        // Example: WITH cte(c1, c2) AS (SELECT id, name FROM t) SELECT c1 FROM cte
        // The 'c1' column traces to 'cte', not to 't' (because 'c1' doesn't exist in 't')
        if (sourceNamespace instanceof CTENamespace && isCTEExplicitColumn()) {
            return ((CTENamespace) sourceNamespace).getReferencingTable();
        }

        // For SubqueryNamespace: passthrough columns that reference calculated columns should NOT trace
        // IMPORTANT: This check must come BEFORE isPassthroughToAlias() because
        // isPassthroughToAlias() returns true for BOTH alias and calculated passthroughs.
        // Example: SELECT kko_lfz_9 FROM (SELECT CASE...END AS kko_lfz_9 FROM t) subq
        // The outer kko_lfz_9 references a calculated column in the subquery
        if (sourceNamespace instanceof SubqueryNamespace && isPassthroughToCalculatedInSubquery()) {
            return null;
        }

        // For SubqueryNamespace: passthrough columns that reference aliases - trace through
        // to find the source table. The data still originates from a physical table.
        // Example: SELECT stat_typ FROM (SELECT stat_typ = stellplatz_typ FROM t) AS b
        // The stat_typ in outer query references b.stat_typ → traces to t
        if (sourceNamespace instanceof SubqueryNamespace && isPassthroughToAlias()) {
            return traceColumnAliasThroughSubquery((SubqueryNamespace) sourceNamespace);
        }

        // For CTENamespace: passthrough columns that reference calculated subquery columns should NOT trace
        // Example: WITH DataCTE AS (SELECT subq.calc_col FROM (SELECT CASE...END AS calc_col FROM t) subq)
        // The CTE column calc_col references a calculated column in the subquery
        if (sourceNamespace instanceof CTENamespace && isPassthroughToCalculatedInCTE()) {
            return null;
        }

        // For CTE explicit column + star pattern: c1/c2/c3 trace to the star, NOT through the star
        // Example: WITH cte(c1, c2, c3) AS (SELECT * FROM Employees)
        // Without metadata, c1 traces to Employees.* (the star), not Employees.c1 (which doesn't exist)
        // The evidence "cte_explicit_column_via_star" indicates this pattern
        if ("cte_explicit_column_via_star".equals(evidence)) {
            return null;
        }

        // If an override table is set (e.g., for traced columns), use it
        if (overrideTable != null) {
            return overrideTable;
        }

        if (sourceNamespace == null) {
            return null;
        }

        // For UnionNamespace: UNION columns don't belong to any specific physical table.
        // They're a combination of multiple branches. UnionNamespace.getFinalTable() returns
        // the first branch's table which is incorrect for tracking column origins.
        if (sourceNamespace instanceof UnionNamespace) {
            return null;
        }

        // For SubqueryNamespace without override table: if the subquery has multiple tables
        // AND no qualified star to identify the source, we can't determine which table
        // the column comes from. Returning the first table would be incorrect.
        // Example: FROM CDS_H_PARTNER PAR, (SELECT kategorie ... FROM CDS_H_KUNDEN_OBJEKT) subq
        // But if there's a qualified star like "ta.*", that identifies the source table.
        //
        // IMPORTANT: For Teradata, implicit lateral derived tables (auto-added tables when
        // a column references an undeclared table in WHERE clause) should be excluded from
        // the multiple-table count. These are syntactic sugar and shouldn't affect column
        // resolution to the actual source table.
        if (sourceNamespace instanceof SubqueryNamespace && overrideTable == null) {
            SubqueryNamespace subNs = (SubqueryNamespace) sourceNamespace;
            gudusoft.gsqlparser.stmt.TSelectSqlStatement subquery = subNs.getSubquery();
            if (subquery != null && subquery.tables != null) {
                // Count only real tables (excluding implicit lateral derived tables)
                int realTableCount = countRealTables(subquery.tables);
                if (realTableCount > 1) {
                    // Check if there's a qualified star that can identify the source
                    if (!hasQualifiedStar(subquery)) {
                        return null;
                    }
                }
            }
        }

        // For CTENamespace with multiple tables (e.g., JOIN): trace the specific column
        // to its correct source table using the definitionNode
        // Example: WITH cte AS (SELECT m.album_id, b.band_name FROM albums m JOIN bands b ...)
        // When tracing 'band_name', we need to find it comes from 'b' (bands), not 'm' (albums)
        if (sourceNamespace instanceof CTENamespace) {
            CTENamespace cteNs = (CTENamespace) sourceNamespace;
            TTable tracedTable = null;

            if (definitionNode instanceof TResultColumn) {
                // Direct case: definitionNode is a TResultColumn from the CTE's SELECT list
                tracedTable = traceColumnThroughCTE(cteNs, (TResultColumn) definitionNode);
            } else {
                // Indirect case: The column might be traced through a star column
                // Try to find the column by name in the CTE chain
                tracedTable = traceColumnByNameThroughCTE(cteNs, exposedName);
            }

            if (tracedTable != null) {
                return tracedTable;
            }

            // For CTEs with multiple tables, if tracing failed (unqualified column),
            // return null instead of the first table to avoid incorrect lineage.
            // Example: WITH cte AS (SELECT musicians.id, musician_name, music_bands.band_name
            //          FROM musicians JOIN ... JOIN music_bands)
            // The unqualified 'musician_name' cannot be traced to any specific table
            // without metadata, so we should NOT guess and pick the first table.
            TSelectSqlStatement cteSelect = cteNs.getSelectStatement();
            if (cteSelect != null && cteSelect.tables != null) {
                int tableCount = countRealTables(cteSelect.tables);
                if (tableCount > 1) {
                    // Cannot determine which table - don't guess
                    return null;
                }
            }
        }

        return sourceNamespace.getFinalTable();
    }

    /**
     * Get the original column name in the physical table when this column is an alias.
     *
     * <p>When a column is aliased in a subquery (e.g., {@code SELECT t.id AS col1}),
     * the exposed name is {@code col1} but the original column in the physical table
     * is {@code id}. This method returns {@code id} so callers can pair the correct
     * column name with the physical table returned by {@link #getFinalTable()}.</p>
     *
     * <p>For multi-level aliases (e.g., {@code SELECT ADCS_MSISDN FROM (SELECT MSISDN AS ADCS_MSISDN FROM t) sub}),
     * this recursively traces through all levels to find the original column name ({@code MSISDN}).</p>
     *
     * @return The original column name if this is an alias, or null if not an alias
     *         or unable to determine
     */
    public String getFinalColumnName() {
        return getFinalColumnNameInternal(0);
    }

    /**
     * Internal recursive implementation of getFinalColumnName with depth limit.
     */
    private String getFinalColumnNameInternal(int depth) {
        if (depth > 10) return null; // safety limit for deeply nested aliases

        if (!isColumnAlias() && !isPassthroughToAlias()) {
            return null;
        }

        if (definitionNode == null || !(definitionNode instanceof TResultColumn)) {
            return null;
        }

        TResultColumn rc = (TResultColumn) definitionNode;
        TExpression expr = rc.getExpr();
        if (expr == null) {
            return null;
        }

        TObjectName colRef = null;
        if (expr.getExpressionType() == EExpressionType.simple_object_name_t) {
            colRef = expr.getObjectOperand();
        } else if (expr.getExpressionType() == EExpressionType.sqlserver_proprietary_column_alias_t) {
            TExpression rightExpr = expr.getRightOperand();
            if (rightExpr != null && rightExpr.getExpressionType() == EExpressionType.simple_object_name_t) {
                colRef = rightExpr.getObjectOperand();
            }
        }

        if (colRef != null) {
            // Check if this inner column reference itself has a ColumnSource that's also an alias.
            // If so, recursively trace to get the deepest original column name.
            ColumnSource innerSource = colRef.getColumnSource();
            if (innerSource != null && (innerSource.isColumnAlias() || innerSource.isPassthroughToAlias())) {
                String deeperName = innerSource.getFinalColumnNameInternal(depth + 1);
                if (deeperName != null) {
                    return deeperName;
                }
            }
            return colRef.getColumnNameOnly();
        }
        return null;
    }

    /**
     * Trace a column by name through a CTE to find its correct source table.
     * This handles the case when the column is traced through star columns
     * and we don't have the direct TResultColumn definition.
     *
     * @param cteNs The CTENamespace to trace through
     * @param columnName The name of the column to find
     * @return The correct source table, or null if unable to determine
     */
    private TTable traceColumnByNameThroughCTE(CTENamespace cteNs, String columnName) {
        if (columnName == null || columnName.isEmpty()) {
            return null;
        }

        // Get the CTE's SELECT statement
        TSelectSqlStatement cteSelect = cteNs.getSelectStatement();
        if (cteSelect == null) {
            return null;
        }

        // First, check if this CTE has explicit columns matching the name
        TTable result = findColumnInSelectList(cteSelect, columnName);
        if (result != null) {
            return result;
        }

        // If not found directly, check if this CTE uses SELECT * from another CTE
        if (cteSelect.tables != null) {
            for (int i = 0; i < cteSelect.tables.size(); i++) {
                TTable table = cteSelect.tables.getTable(i);
                if (table == null) continue;

                // If it references another CTE, trace through it
                if (table.isCTEName() && table.getCTE() != null) {
                    gudusoft.gsqlparser.nodes.TCTE underlyingCte = table.getCTE();
                    TSelectSqlStatement underlyingSelect = underlyingCte.getSubquery();
                    if (underlyingSelect != null) {
                        result = findColumnInSelectList(underlyingSelect, columnName);
                        if (result != null) {
                            return result;
                        }
                    }
                }
            }
        }

        return null;
    }

    /**
     * Trace a column through a CTE to find its correct source table.
     * This handles CTEs with JOINs where columns come from different tables,
     * including CTEs with star columns that reference other CTEs.
     *
     * @param cteNs The CTENamespace
     * @param resultColumn The TResultColumn from the CTE's SELECT list
     * @return The correct source table, or null if unable to determine
     */
    private TTable traceColumnThroughCTE(CTENamespace cteNs, TResultColumn resultColumn) {
        if (resultColumn == null || resultColumn.getExpr() == null) {
            return null;
        }

        TExpression expr = resultColumn.getExpr();

        // Check if the expression is a star column (e.g., SELECT * FROM other_cte)
        // In this case, we need to trace through to the underlying CTE
        if (expr.getExpressionType() == EExpressionType.simple_object_name_t) {
            TObjectName colRef = expr.getObjectOperand();
            if (colRef != null && "*".equals(colRef.getColumnNameOnly())) {
                // This is a star column - trace through to find the actual column
                return traceColumnThroughStarInCTE(cteNs, exposedName);
            }
        }

        // Check if the expression is a simple column reference
        if (expr.getExpressionType() != EExpressionType.simple_object_name_t) {
            return null;
        }

        TObjectName colRef = expr.getObjectOperand();
        if (colRef == null) {
            return null;
        }

        // Check if the column has a table qualifier (e.g., "b.band_name")
        String tableQualifier = colRef.getTableString();
        if (tableQualifier == null || tableQualifier.isEmpty()) {
            // No qualifier - can't determine which table
            return null;
        }

        // Get the CTE's subquery to find the table with matching alias
        TSelectSqlStatement cteSubquery = cteNs.getSelectStatement();
        if (cteSubquery == null || cteSubquery.tables == null) {
            return null;
        }

        // Search for the table with matching alias or name
        for (int i = 0; i < cteSubquery.tables.size(); i++) {
            TTable table = cteSubquery.tables.getTable(i);
            if (table == null) continue;

            // Check alias match
            String alias = table.getAliasName();
            if (alias != null && alias.equalsIgnoreCase(tableQualifier)) {
                // Found the table - now trace to its final physical table if needed
                return traceToPhysicalTable(table);
            }

            // Check table name match (for unaliased tables)
            String tableName = table.getTableName() != null ? table.getTableName().toString() : null;
            if (tableName != null && tableName.equalsIgnoreCase(tableQualifier)) {
                return traceToPhysicalTable(table);
            }
        }

        return null;
    }

    /**
     * Trace a specific column through a CTE that uses SELECT *.
     * This finds the underlying CTE that defines the column and traces it to the correct table.
     *
     * @param cteNs The CTE namespace with SELECT *
     * @param columnName The name of the column to trace
     * @return The correct source table, or null if unable to determine
     */
    private TTable traceColumnThroughStarInCTE(CTENamespace cteNs, String columnName) {
        if (columnName == null || columnName.isEmpty()) {
            return null;
        }

        TSelectSqlStatement cteSubquery = cteNs.getSelectStatement();
        if (cteSubquery == null || cteSubquery.tables == null) {
            return null;
        }

        // Find the underlying CTE or table that the star column references
        for (int i = 0; i < cteSubquery.tables.size(); i++) {
            TTable table = cteSubquery.tables.getTable(i);
            if (table == null) continue;

            // If it's a CTE reference, look for the column in that CTE
            if (table.isCTEName() && table.getCTE() != null) {
                gudusoft.gsqlparser.nodes.TCTE underlyingCte = table.getCTE();
                TSelectSqlStatement underlyingSubquery = underlyingCte.getSubquery();
                if (underlyingSubquery != null) {
                    // Look for the column in the underlying CTE's SELECT list
                    TTable tracedTable = findColumnInSelectList(underlyingSubquery, columnName);
                    if (tracedTable != null) {
                        return tracedTable;
                    }
                }
            }
        }

        return null;
    }

    /**
     * Find a column by name in a SELECT list and trace it to its source table.
     *
     * @param selectStmt The SELECT statement to search
     * @param columnName The column name to find
     * @return The source table for the column, or null if not found
     */
    private TTable findColumnInSelectList(TSelectSqlStatement selectStmt, String columnName) {
        if (selectStmt == null || selectStmt.getResultColumnList() == null) {
            return null;
        }

        gudusoft.gsqlparser.nodes.TResultColumnList resultList = selectStmt.getResultColumnList();
        for (int i = 0; i < resultList.size(); i++) {
            TResultColumn rc = resultList.getResultColumn(i);
            if (rc == null) continue;

            // Get the exposed name (alias or column name)
            String exposedColName = rc.getAliasClause() != null
                ? rc.getAliasClause().toString()
                : (rc.getExpr() != null && rc.getExpr().getObjectOperand() != null
                    ? rc.getExpr().getObjectOperand().getColumnNameOnly()
                    : null);

            if (exposedColName != null && exposedColName.equalsIgnoreCase(columnName)) {
                // Found the column - trace it to its source table
                TExpression expr = rc.getExpr();
                if (expr != null && expr.getExpressionType() == EExpressionType.simple_object_name_t) {
                    TObjectName colRef = expr.getObjectOperand();
                    if (colRef != null) {
                        String tableQualifier = colRef.getTableString();
                        if (tableQualifier != null && !tableQualifier.isEmpty()) {
                            // Find the table with this qualifier in the FROM clause
                            if (selectStmt.tables != null) {
                                for (int j = 0; j < selectStmt.tables.size(); j++) {
                                    TTable table = selectStmt.tables.getTable(j);
                                    if (table == null) continue;

                                    String alias = table.getAliasName();
                                    if (alias != null && alias.equalsIgnoreCase(tableQualifier)) {
                                        return traceToPhysicalTable(table);
                                    }

                                    String tableName = table.getTableName() != null
                                        ? table.getTableName().toString() : null;
                                    if (tableName != null && tableName.equalsIgnoreCase(tableQualifier)) {
                                        return traceToPhysicalTable(table);
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }

        return null;
    }

    /**
     * Trace a table to its underlying physical table.
     * Handles CTEs, subqueries, and JOINs.
     */
    private TTable traceToPhysicalTable(TTable table) {
        if (table == null) {
            return null;
        }

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

        // If it's a CTE reference, trace through the CTE
        if (table.isCTEName() && table.getCTE() != null) {
            // Use a simple approach - get the first physical table from the CTE
            // This could be enhanced to trace specific columns through nested CTEs
            gudusoft.gsqlparser.nodes.TCTE nestedCte = table.getCTE();
            if (nestedCte.getSubquery() != null && nestedCte.getSubquery().tables != null) {
                for (int i = 0; i < nestedCte.getSubquery().tables.size(); i++) {
                    TTable nestedTable = nestedCte.getSubquery().tables.getTable(i);
                    TTable physical = traceToPhysicalTable(nestedTable);
                    if (physical != null) {
                        return physical;
                    }
                }
            }
        }

        // If it's a subquery, trace through it
        if (table.getSubquery() != null) {
            SubqueryNamespace nestedNs = new SubqueryNamespace(
                table.getSubquery(),
                table.getAliasName(),
                null  // nameMatcher not needed for simple tracing
            );
            return nestedNs.getFinalTable();
        }

        return null;
    }

    /**
     * Trace a column alias through a subquery to find its source table.
     * When a column is aliased (e.g., SELECT t.id AS col1 FROM my_table t),
     * the alias changes the column name but the data still comes from the
     * underlying table. This method traces through to find that table.
     *
     * <p>Used by {@link #getTracedFinalTable()} to provide alias-aware lineage
     * tracing. Handles both qualified (t.id AS col1) and unqualified (id AS col1)
     * column references, as well as SQL Server proprietary alias syntax.</p>
     *
     * @param subNs The SubqueryNamespace containing the aliased column
     * @return The physical table the column traces to, or null if undetermined
     */
    private TTable traceColumnAliasThroughSubquery(SubqueryNamespace subNs) {
        if (definitionNode == null || !(definitionNode instanceof TResultColumn)) {
            return null;
        }

        TResultColumn rc = (TResultColumn) definitionNode;
        TExpression expr = rc.getExpr();
        if (expr == null) {
            return null;
        }

        TObjectName colRef = null;

        if (expr.getExpressionType() == EExpressionType.simple_object_name_t) {
            // Standard: SELECT col AS alias
            colRef = expr.getObjectOperand();
        } else if (expr.getExpressionType() == EExpressionType.sqlserver_proprietary_column_alias_t) {
            // SQL Server: SELECT alias = col
            TExpression rightExpr = expr.getRightOperand();
            if (rightExpr != null && rightExpr.getExpressionType() == EExpressionType.simple_object_name_t) {
                colRef = rightExpr.getObjectOperand();
            }
        }

        if (colRef == null) {
            return null;
        }

        // Use the resolved sourceTable from the inner column reference
        TTable sourceTable = colRef.getSourceTable();
        if (sourceTable != null) {
            return traceToPhysicalTable(sourceTable);
        }

        // Fallback: if no sourceTable resolved, try to find by table qualifier
        String tableQualifier = colRef.getTableString();
        if (tableQualifier != null && !tableQualifier.isEmpty()) {
            TSelectSqlStatement subquery = subNs.getSubquery();
            if (subquery != null && subquery.tables != null) {
                for (int i = 0; i < subquery.tables.size(); i++) {
                    TTable table = subquery.tables.getTable(i);
                    if (table == null) continue;

                    String alias = table.getAliasName();
                    if (alias != null && alias.equalsIgnoreCase(tableQualifier)) {
                        return traceToPhysicalTable(table);
                    }

                    String tableName = table.getTableName() != null ? table.getTableName().toString() : null;
                    if (tableName != null && tableName.equalsIgnoreCase(tableQualifier)) {
                        return traceToPhysicalTable(table);
                    }
                }
            }
        }

        return null;
    }

    /**
     * Get all physical tables that this column might originate from.
     *
     * <p>For columns from UNION queries, this returns tables from ALL branches,
     * not just the first one. This is essential for proper lineage tracking
     * where a column like {@code actor_id} in a UNION query should be linked
     * to {@code actor.actor_id}, {@code actor2.actor_id}, {@code actor3.actor_id}.</p>
     *
     * <p>For regular single-table sources, this returns a single-element list
     * with the same table as {@link #getFinalTable()}.</p>
     *
     * @return List of all physical tables, or empty list if unable to determine
     */
    public java.util.List<TTable> getAllFinalTables() {
        // If this ColumnSource has explicit candidateTables set (e.g., from UNION inference),
        // use those instead of delegating to namespace. This is critical for UNION queries
        // where only branches with SELECT * should contribute candidate tables for inferred columns.
        // An EMPTY list means "no matching tables" - return it as-is without delegating.
        // A NULL means "not determined" - delegate to namespace.
        if (candidateTables != null) {
            return candidateTables;
        }

        if (sourceNamespace == null) {
            if (overrideTable != null) {
                return java.util.Collections.singletonList(overrideTable);
            }
            return java.util.Collections.emptyList();
        }

        // For calculated columns and aliases in SubqueryNamespace, don't trace
        if (sourceNamespace instanceof SubqueryNamespace) {
            if (isCalculatedColumn() || isColumnAlias()) {
                return java.util.Collections.emptyList();
            }
        }

        // For CTENamespace calculated/alias/explicit columns, trace to CTE itself
        if (sourceNamespace instanceof CTENamespace) {
            if (isCalculatedColumn() || isColumnAlias() || isCTEExplicitColumn()) {
                TTable cteTable = ((CTENamespace) sourceNamespace).getReferencingTable();
                if (cteTable != null) {
                    return java.util.Collections.singletonList(cteTable);
                }
                return java.util.Collections.emptyList();
            }
        }

        // Delegate to namespace - handles UNION queries via UnionNamespace.getAllFinalTables()
        return sourceNamespace.getAllFinalTables();
    }

    /**
     * Check if this column is a passthrough reference to an underlying alias.
     *
     * <p>A passthrough column is a simple column reference in a subquery that
     * references another column from its FROM clause. If that underlying column
     * is an alias, then this passthrough should not trace to the base table.</p>
     *
     * <p>Example: In {@code SELECT stat_typ FROM (SELECT stat_typ = col FROM t) AS b},
     * the outer {@code stat_typ} is a passthrough to {@code b.stat_typ}, which is an alias.</p>
     *
     * @return true if this is a passthrough to an alias
     */
    private boolean isPassthroughToAlias() {
        if (definitionNode == null || !(definitionNode instanceof TResultColumn)) {
            return false;
        }

        TResultColumn rc = (TResultColumn) definitionNode;
        TExpression expr = rc.getExpr();
        if (expr == null) {
            return false;
        }

        // Only check simple column references (passthroughs)
        if (expr.getExpressionType() != EExpressionType.simple_object_name_t) {
            return false;
        }

        // If this column itself has an alias that differs, it's already handled by isColumnAlias()
        if (rc.getAliasClause() != null && rc.getAliasClause().getAliasName() != null) {
            return false;
        }

        // Get the column name being referenced
        gudusoft.gsqlparser.nodes.TObjectName objName = expr.getObjectOperand();
        if (objName == null) {
            return false;
        }
        String columnName = objName.getColumnNameOnly();
        if (columnName == null || columnName.isEmpty()) {
            return false;
        }

        // Resolve this column in the subquery's FROM scope to find the underlying ColumnSource
        if (sourceNamespace instanceof SubqueryNamespace) {
            SubqueryNamespace subNs = (SubqueryNamespace) sourceNamespace;
            ColumnSource underlyingSource = subNs.resolveColumnInFromScope(columnName);
            if (underlyingSource != null) {
                // Check if the underlying column is an alias or calculated
                if (underlyingSource.isColumnAlias() || underlyingSource.isCalculatedColumn()) {
                    return true;
                }
                // Recursively check if it's a passthrough to alias
                if (underlyingSource.isPassthroughToAlias()) {
                    return true;
                }
            }
        }

        return false;
    }

    /**
     * Check if this subquery column is a passthrough reference to a calculated column.
     *
     * <p>A subquery column is a passthrough to calculated if:</p>
     * <ol>
     *   <li>The column definition is a simple column reference (e.g., kko_lfz_9)</li>
     *   <li>The referenced column in the FROM scope is calculated (CASE, function, etc.)</li>
     * </ol>
     *
     * <p>Example:</p>
     * <pre>
     * SELECT kko_lfz_9 AS KKO_LFZ_9
     * FROM (SELECT CASE WHEN... END AS kko_lfz_9 FROM t) subq
     * </pre>
     * <p>Here, kko_lfz_9 in the outer query is a passthrough to a calculated column in subq.</p>
     *
     * <p>This differs from {@link #isPassthroughToAlias()} which skips columns with aliases.
     * Here we check even aliased passthroughs to see if they reference calculated columns.</p>
     *
     * @return true if this is a passthrough to a calculated column in a subquery
     */
    private boolean isPassthroughToCalculatedInSubquery() {
        if (definitionNode == null || !(definitionNode instanceof TResultColumn)) {
            return false;
        }

        TResultColumn rc = (TResultColumn) definitionNode;
        TExpression expr = rc.getExpr();
        if (expr == null) {
            return false;
        }

        // Only check simple column references (passthroughs)
        if (expr.getExpressionType() != EExpressionType.simple_object_name_t) {
            return false;
        }

        // Get the column name being referenced
        gudusoft.gsqlparser.nodes.TObjectName objName = expr.getObjectOperand();
        if (objName == null) {
            return false;
        }
        String columnName = objName.getColumnNameOnly();
        if (columnName == null || columnName.isEmpty()) {
            return false;
        }

        // Resolve this column in the subquery's FROM scope to find the underlying ColumnSource
        if (sourceNamespace instanceof SubqueryNamespace) {
            SubqueryNamespace subNs = (SubqueryNamespace) sourceNamespace;
            ColumnSource underlyingSource = subNs.resolveColumnInFromScope(columnName);
            if (underlyingSource != null) {
                // Check if the underlying column is calculated
                if (underlyingSource.isCalculatedColumn()) {
                    return true;
                }
                // Recursively check if it's a passthrough to calculated
                if (underlyingSource.isPassthroughToCalculatedInSubquery()) {
                    return true;
                }
            }
        }

        return false;
    }

    /**
     * Check if this CTE column is a passthrough reference to a calculated column in a subquery or nested CTE.
     *
     * <p>A CTE column is a passthrough to calculated if:</p>
     * <ol>
     *   <li>The column definition is a simple qualified column reference (e.g., subq.calc_col or cte.calc_col)</li>
     *   <li>The qualifier refers to a subquery or CTE in the CTE's body</li>
     *   <li>The referenced column in that subquery/CTE is calculated (CASE, function, etc.)</li>
     * </ol>
     *
     * <p>Example with subquery:</p>
     * <pre>
     * WITH DataCTE AS (
     *   SELECT ErrorCountsCTE.ErrorSeverityCategory  -- passthrough
     *   FROM (SELECT CASE...END AS ErrorSeverityCategory FROM t) ErrorCountsCTE
     * )
     * </pre>
     *
     * <p>Example with nested CTE:</p>
     * <pre>
     * WITH attendance_summary AS (
     *   SELECT date_trunc('month', attendance_date) as month FROM attendance
     * )
     * WITH outer_cte AS (
     *   SELECT a.month FROM attendance_summary a  -- passthrough to calculated in nested CTE
     * )
     * </pre>
     *
     * @return true if this is a passthrough to a calculated column in a CTE
     */
    private boolean isPassthroughToCalculatedInCTE() {
        if (definitionNode == null || !(definitionNode instanceof TResultColumn)) {
            return false;
        }

        TResultColumn rc = (TResultColumn) definitionNode;
        TExpression expr = rc.getExpr();
        if (expr == null) {
            return false;
        }

        // Only check simple qualified column references (passthroughs like subq.column)
        if (expr.getExpressionType() != EExpressionType.simple_object_name_t) {
            return false;
        }

        // Get the column reference
        gudusoft.gsqlparser.nodes.TObjectName objName = expr.getObjectOperand();
        if (objName == null) {
            return false;
        }

        // Must have a table qualifier (e.g., "ErrorCountsCTE" in "ErrorCountsCTE.ErrorSeverityCategory")
        String tableQualifier = objName.getTableString();
        if (tableQualifier == null || tableQualifier.isEmpty()) {
            return false;
        }

        String columnName = objName.getColumnNameOnly();
        if (columnName == null || columnName.isEmpty()) {
            return false;
        }

        // Get the CTE's subquery to find the referenced subquery alias
        if (!(sourceNamespace instanceof CTENamespace)) {
            return false;
        }

        CTENamespace cteNs = (CTENamespace) sourceNamespace;
        gudusoft.gsqlparser.nodes.TCTE cte = cteNs.getCTE();
        if (cte == null || cte.getSubquery() == null) {
            return false;
        }

        // Find the subquery/table with this alias in the CTE's body
        gudusoft.gsqlparser.stmt.TSelectSqlStatement cteBody = cte.getSubquery();
        TTable referencedTable = findTableByAlias(cteBody, tableQualifier);
        if (referencedTable == null) {
            return false;
        }

        // Case 1: Referenced table is a subquery
        if (referencedTable.getSubquery() != null) {
            gudusoft.gsqlparser.stmt.TSelectSqlStatement subquery = referencedTable.getSubquery();
            return isCalculatedColumnInSelect(subquery, columnName);
        }

        // Case 2: Referenced table is a CTE reference
        if (referencedTable.isCTEName() && referencedTable.getCTE() != null) {
            gudusoft.gsqlparser.nodes.TCTE referencedCTE = referencedTable.getCTE();
            if (referencedCTE.getSubquery() != null) {
                return isCalculatedColumnInSelect(referencedCTE.getSubquery(), columnName);
            }
        }

        return false;
    }

    /**
     * Find a table in a SELECT statement by its alias.
     */
    private TTable findTableByAlias(gudusoft.gsqlparser.stmt.TSelectSqlStatement select, String alias) {
        if (select == null || select.tables == null || alias == null) {
            return null;
        }

        for (int i = 0; i < select.tables.size(); i++) {
            TTable table = select.tables.getTable(i);
            if (table != null) {
                String tableAlias = table.getAliasName();
                if (tableAlias != null && tableAlias.equalsIgnoreCase(alias)) {
                    return table;
                }
                // Also check table name for non-aliased references
                if (tableAlias == null && table.getTableName() != null) {
                    String tableName = table.getTableName().toString();
                    if (tableName != null && tableName.equalsIgnoreCase(alias)) {
                        return table;
                    }
                }
            }
        }
        return null;
    }

    /**
     * Check if a column in a SELECT statement is calculated (not a simple column reference).
     */
    private boolean isCalculatedColumnInSelect(gudusoft.gsqlparser.stmt.TSelectSqlStatement select, String columnName) {
        if (select == null || select.getResultColumnList() == null || columnName == null) {
            return false;
        }

        for (int i = 0; i < select.getResultColumnList().size(); i++) {
            TResultColumn rc = select.getResultColumnList().getResultColumn(i);
            if (rc == null) continue;

            // Get the column name for this result column
            String rcName = null;
            if (rc.getAliasClause() != null && rc.getAliasClause().getAliasName() != null) {
                rcName = rc.getAliasClause().getAliasName().toString();
            } else if (rc.getExpr() != null &&
                       rc.getExpr().getExpressionType() == EExpressionType.simple_object_name_t &&
                       rc.getExpr().getObjectOperand() != null) {
                rcName = rc.getExpr().getObjectOperand().getColumnNameOnly();
            }

            if (rcName != null && rcName.equalsIgnoreCase(columnName)) {
                // Found the column - check if it's calculated
                TExpression expr = rc.getExpr();
                if (expr != null && expr.getExpressionType() != EExpressionType.simple_object_name_t) {
                    // Non-simple expression = calculated
                    return true;
                }
            }
        }
        return false;
    }

    /**
     * Check if this column source represents a calculated expression.
     *
     * <p>A column is calculated if its definition is a TResultColumn with
     * a non-simple expression (not a direct column reference or star).</p>
     *
     * <p>For inferred columns (via star expansion), we trace back to the
     * source CTE/subquery to check if the original column is calculated.</p>
     *
     * @return true if this is a calculated column
     */
    public boolean isCalculatedColumn() {
        if (definitionNode == null) {
            // For inferred columns through star expansion, check if the underlying
            // column in the source CTE/subquery is calculated
            return isInferredFromCalculatedColumn();
        }

        if (!(definitionNode instanceof TResultColumn)) {
            return false;
        }

        TResultColumn rc = (TResultColumn) definitionNode;
        TExpression expr = rc.getExpr();
        if (expr == null) {
            return false;
        }

        EExpressionType exprType = expr.getExpressionType();

        // Simple column reference - NOT calculated (passthrough)
        if (exprType == EExpressionType.simple_object_name_t) {
            return false;
        }

        // Star column - NOT calculated (passthrough)
        String colText = rc.toString();
        if (colText != null && colText.endsWith("*")) {
            return false;
        }

        // SQL Server proprietary column alias (col = expr)
        if (exprType == EExpressionType.sqlserver_proprietary_column_alias_t) {
            if (expr.getRightOperand() != null &&
                expr.getRightOperand().getExpressionType() == EExpressionType.simple_object_name_t) {
                return false;
            }
        }

        // Any other expression type is calculated
        return true;
    }

    /**
     * Check if this is an inferred column (via star expansion) that originates from
     * a calculated column in the source CTE/subquery.
     *
     * <p>When a column is resolved through star expansion (e.g., SELECT * FROM CTE),
     * the definitionNode is null. We need to trace back to the source namespace
     * to check if the original column is calculated.</p>
     *
     * @return true if this inferred column traces back to a calculated column
     */
    private boolean isInferredFromCalculatedColumn() {
        // Only check for inferred columns (evidence contains "auto_inferred")
        if (evidence == null || !evidence.contains("auto_inferred")) {
            return false;
        }

        // Need the source namespace and column name to trace
        if (sourceNamespace == null || exposedName == null) {
            return false;
        }

        // For CTE namespace, check if the column is calculated in the CTE's SELECT list
        if (sourceNamespace instanceof CTENamespace) {
            CTENamespace cteNs = (CTENamespace) sourceNamespace;
            gudusoft.gsqlparser.nodes.TCTE cte = cteNs.getCTE();
            if (cte != null && cte.getSubquery() != null) {
                // First check the CTE's direct SELECT list
                if (isCalculatedColumnInSelect(cte.getSubquery(), exposedName)) {
                    return true;
                }

                // If the CTE has a star column, trace through to referenced CTEs
                if (cteNs.hasStarColumn()) {
                    return isCalculatedInCTEChain(cte.getSubquery(), exposedName);
                }
            }
        }

        // For Subquery namespace, check if the column is calculated in the subquery's SELECT list
        if (sourceNamespace instanceof SubqueryNamespace) {
            SubqueryNamespace subNs = (SubqueryNamespace) sourceNamespace;
            gudusoft.gsqlparser.stmt.TSelectSqlStatement subquery = subNs.getSubquery();
            if (subquery != null) {
                // First check the subquery's direct SELECT list
                if (isCalculatedColumnInSelect(subquery, exposedName)) {
                    return true;
                }

                // If the subquery has a star column, trace through to source tables
                if (subNs.hasStarColumn()) {
                    return isCalculatedInSubqueryChain(subquery, exposedName);
                }
            }
        }

        return false;
    }

    /**
     * Check if a column is calculated by tracing through CTE references.
     * This handles cases like Stage4 -> Stage3 -> Stage2 where the column
     * is calculated at some intermediate level.
     */
    private boolean isCalculatedInCTEChain(gudusoft.gsqlparser.stmt.TSelectSqlStatement select, String columnName) {
        if (select == null || select.tables == null) {
            return false;
        }

        // Look for CTE references in the FROM clause
        for (int i = 0; i < select.tables.size(); i++) {
            TTable table = select.tables.getTable(i);
            if (table != null && table.isCTEName() && table.getCTE() != null) {
                gudusoft.gsqlparser.nodes.TCTE referencedCTE = table.getCTE();
                if (referencedCTE.getSubquery() != null) {
                    // Check if the column is calculated in this CTE
                    if (isCalculatedColumnInSelect(referencedCTE.getSubquery(), columnName)) {
                        return true;
                    }
                    // Recursively check the CTE chain
                    if (isCalculatedInCTEChain(referencedCTE.getSubquery(), columnName)) {
                        return true;
                    }
                }
            }
        }
        return false;
    }

    /**
     * Check if a column is calculated by tracing through subquery references.
     */
    private boolean isCalculatedInSubqueryChain(gudusoft.gsqlparser.stmt.TSelectSqlStatement select, String columnName) {
        if (select == null || select.tables == null) {
            return false;
        }

        // Look for subquery tables in the FROM clause
        for (int i = 0; i < select.tables.size(); i++) {
            TTable table = select.tables.getTable(i);
            if (table != null && table.getSubquery() != null) {
                gudusoft.gsqlparser.stmt.TSelectSqlStatement subquery = table.getSubquery();
                // Check if the column is calculated in this subquery
                if (isCalculatedColumnInSelect(subquery, columnName)) {
                    return true;
                }
                // Recursively check the subquery chain
                if (isCalculatedInSubqueryChain(subquery, columnName)) {
                    return true;
                }
            }
            // Also check CTE references within subqueries
            if (table != null && table.isCTEName() && table.getCTE() != null) {
                gudusoft.gsqlparser.nodes.TCTE referencedCTE = table.getCTE();
                if (referencedCTE.getSubquery() != null) {
                    if (isCalculatedColumnInSelect(referencedCTE.getSubquery(), columnName)) {
                        return true;
                    }
                    if (isCalculatedInCTEChain(referencedCTE.getSubquery(), columnName)) {
                        return true;
                    }
                }
            }
        }
        return false;
    }

    /**
     * Check if this column source represents a column alias (renamed column).
     *
     * <p>A column is an alias if it's a simple column reference in a subquery
     * that has been given a different name via AS or NAMED. For example:</p>
     * <ul>
     *   <li>{@code SELECT col AS alias FROM table} - alias is different from col</li>
     *   <li>{@code SELECT col (NAMED alias) FROM table} - Teradata NAMED syntax</li>
     *   <li>{@code SELECT alias = col FROM table} - SQL Server proprietary syntax</li>
     * </ul>
     *
     * <p>Column aliases are traced through in {@link #getFinalTable()} to find the
     * physical table the data originates from, since the alias only renames the column
     * but the data still comes from the physical table.</p>
     *
     * @return true if this is a column alias with a different name than the original
     */
    public boolean isColumnAlias() {
        if (definitionNode == null) {
            return false;
        }

        if (!(definitionNode instanceof TResultColumn)) {
            return false;
        }

        TResultColumn rc = (TResultColumn) definitionNode;
        TExpression expr = rc.getExpr();
        if (expr == null) {
            return false;
        }

        EExpressionType exprType = expr.getExpressionType();

        // Handle SQL Server proprietary alias syntax: alias = column
        // Example: stat_typ = stellplatz_typ
        if (exprType == EExpressionType.sqlserver_proprietary_column_alias_t) {
            TExpression rightExpr = expr.getRightOperand();
            TExpression leftExpr = expr.getLeftOperand();
            // Only if right side is a simple column reference
            if (rightExpr != null && leftExpr != null &&
                rightExpr.getExpressionType() == EExpressionType.simple_object_name_t) {
                gudusoft.gsqlparser.nodes.TObjectName rightObjName = rightExpr.getObjectOperand();
                gudusoft.gsqlparser.nodes.TObjectName leftObjName = leftExpr.getObjectOperand();
                if (rightObjName != null && leftObjName != null) {
                    String origName = rightObjName.getColumnNameOnly();
                    String aliasName = leftObjName.getColumnNameOnly();
                    // If alias name differs from original column name, it's an alias
                    if (origName != null && aliasName != null &&
                        !origName.equalsIgnoreCase(aliasName)) {
                        return true;
                    }
                }
            }
            return false;
        }

        // Standard alias syntax: column AS alias
        // Only applies to simple column references
        if (exprType != EExpressionType.simple_object_name_t) {
            return false;
        }

        // Check if there's an alias that differs from the column name
        if (rc.getAliasClause() != null && rc.getAliasClause().getAliasName() != null) {
            String aliasName = rc.getAliasClause().getAliasName().toString();
            if (aliasName != null && !aliasName.isEmpty()) {
                gudusoft.gsqlparser.nodes.TObjectName objName = expr.getObjectOperand();
                if (objName != null) {
                    String origName = objName.getColumnNameOnly();
                    // If alias name differs from original name, it's an alias
                    if (origName != null && !origName.equalsIgnoreCase(aliasName)) {
                        return true;
                    }
                }
            }
        }

        return false;
    }

    /**
     * Check if this column is a CTE explicit column with a different name than the underlying column.
     *
     * <p>A CTE explicit column is one defined in the CTE's column list that maps to a
     * different column name in the CTE's SELECT list. For example:</p>
     * <pre>
     * WITH cte(c1, c2) AS (SELECT id, name FROM users)
     * SELECT c1 FROM cte  -- c1 maps to 'id', names differ
     * </pre>
     *
     * <p>CTE explicit columns should NOT trace to base tables because the explicit
     * column name (c1) doesn't exist as an actual column in the base table (users).</p>
     *
     * @return true if this is a CTE explicit column with a different name
     */
    public boolean isCTEExplicitColumn() {
        // Must be from a CTENamespace
        if (!(sourceNamespace instanceof CTENamespace)) {
            return false;
        }

        // Check evidence for explicit column marker
        if (!"cte_explicit_column".equals(evidence)) {
            return false;
        }

        // Get the underlying column name from the definition node
        if (definitionNode == null || !(definitionNode instanceof TResultColumn)) {
            return false;
        }

        TResultColumn rc = (TResultColumn) definitionNode;
        TExpression expr = rc.getExpr();
        if (expr == null) {
            return false;
        }

        // Get the column name from the SELECT list item
        String underlyingName = null;

        // Check for alias first
        if (rc.getAliasClause() != null && rc.getAliasClause().getAliasName() != null) {
            underlyingName = rc.getAliasClause().getAliasName().toString();
        }
        // Then check for simple column reference
        else if (expr.getExpressionType() == EExpressionType.simple_object_name_t &&
                 expr.getObjectOperand() != null) {
            underlyingName = expr.getObjectOperand().getColumnNameOnly();
        }

        // If we can't determine the underlying name, assume it's different
        // (calculated expressions, etc. are definitely different from explicit column names)
        if (underlyingName == null) {
            return true;
        }

        // If the exposed name differs from the underlying column name, it's an explicit column rename
        return !exposedName.equalsIgnoreCase(underlyingName);
    }

    /**
     * Get the override table, if set.
     */
    public TTable getOverrideTable() {
        return overrideTable;
    }

    /**
     * Get the candidate tables for ambiguous columns.
     *
     * <p>When a column could come from multiple tables (e.g., SELECT * FROM t1, t2),
     * this returns all possible source tables. End users can iterate through this
     * list to understand all potential sources for the column.</p>
     *
     * @return List of candidate tables, or empty list if not ambiguous
     */
    public List<TTable> getCandidateTables() {
        return candidateTables != null ? candidateTables : Collections.emptyList();
    }

    /**
     * Check if this column has multiple candidate tables (is ambiguous).
     *
     * @return true if there are multiple candidate tables
     */
    public boolean isAmbiguous() {
        return candidateTables != null && candidateTables.size() > 1;
    }

    /**
     * Get the field path for deep/record field access.
     *
     * <p>When a column reference includes field access beyond the base column,
     * this returns the field path. For example, in {@code customer.address.city},
     * if base column is {@code customer}, this returns a FieldPath with
     * segments {@code ["address", "city"]}.</p>
     *
     * @return The field path, or null if no field access
     */
    public FieldPath getFieldPath() {
        return fieldPath;
    }

    /**
     * Check if this column source has a field path (deep/record field access).
     *
     * @return true if a non-empty field path exists
     */
    public boolean hasFieldPath() {
        return fieldPath != null && !fieldPath.isEmpty();
    }

    /**
     * Check if this is a struct field access (has evidence "struct_field_access").
     *
     * <p>This is a convenience method for checking if this column source represents
     * a struct/record field dereference operation.</p>
     *
     * @return true if this is a struct field access
     */
    public boolean isStructFieldAccess() {
        return "struct_field_access".equals(evidence);
    }

    /**
     * Checks if this is a definite resolution (confidence = 1.0)
     */
    public boolean isDefinite() {
        return confidence >= 1.0;
    }

    /**
     * Checks if this is an inferred resolution (confidence < 1.0)
     */
    public boolean isInferred() {
        return confidence < 1.0;
    }

    @Override
    public String toString() {
        StringBuilder sb = new StringBuilder();
        sb.append(exposedName);
        if (sourceNamespace != null) {
            sb.append(" from ").append(sourceNamespace.getDisplayName());
        }
        if (confidence < 1.0) {
            sb.append(String.format(" (confidence: %.2f)", confidence));
        }
        return sb.toString();
    }

    /**
     * Creates a copy with updated confidence and evidence.
     * Used when merging or updating inference results.
     *
     * @deprecated Use {@link #withEvidence(ResolutionEvidence)} instead
     */
    public ColumnSource withConfidence(double newConfidence, String newEvidence) {
        return new ColumnSource(
            this.sourceNamespace,
            this.exposedName,
            this.definitionNode,
            newConfidence,
            newEvidence,
            this.overrideTable,
            this.candidateTables != null ? new java.util.ArrayList<>(this.candidateTables) : null,
            null, // will create from legacy evidence
            this.fieldPath
        );
    }

    /**
     * Creates a copy with updated ResolutionEvidence.
     * This is the preferred method for updating evidence in new code.
     *
     * @param newEvidence The new evidence detail
     * @return A new ColumnSource with updated evidence
     */
    public ColumnSource withEvidence(ResolutionEvidence newEvidence) {
        return new ColumnSource(
            this.sourceNamespace,
            this.exposedName,
            this.definitionNode,
            newEvidence != null ? newEvidence.getWeight() : this.confidence,
            newEvidence != null ? newEvidence.toLegacyEvidence() : this.evidence,
            this.overrideTable,
            this.candidateTables != null ? new java.util.ArrayList<>(this.candidateTables) : null,
            newEvidence,
            this.fieldPath
        );
    }

    /**
     * Creates a copy with candidate tables.
     * Used when a column could come from multiple tables.
     */
    public ColumnSource withCandidateTables(List<TTable> candidates) {
        return new ColumnSource(
            this.sourceNamespace,
            this.exposedName,
            this.definitionNode,
            this.confidence,
            this.evidence,
            this.overrideTable,
            candidates != null ? new java.util.ArrayList<>(candidates) : null,
            this.evidenceDetail,
            this.fieldPath
        );
    }

    /**
     * Creates a copy with a field path for deep/record field access.
     *
     * <p>This method is used when resolving struct/record field access patterns
     * like {@code customer.address.city}. The base column is preserved as the
     * exposedName, and the field path captures the remaining segments.</p>
     *
     * @param newFieldPath The field path segments (beyond the base column)
     * @return A new ColumnSource with the field path set
     */
    public ColumnSource withFieldPath(FieldPath newFieldPath) {
        return new ColumnSource(
            this.sourceNamespace,
            this.exposedName,
            this.definitionNode,
            this.confidence,
            this.evidence,
            this.overrideTable,
            this.candidateTables != null ? new java.util.ArrayList<>(this.candidateTables) : null,
            this.evidenceDetail,
            newFieldPath
        );
    }

    /**
     * Creates a copy with a field path from a list of segments.
     *
     * <p>Convenience method for creating a ColumnSource with a field path
     * from a list of string segments.</p>
     *
     * @param segments The field path segments
     * @return A new ColumnSource with the field path set
     */
    public ColumnSource withFieldPath(List<String> segments) {
        return withFieldPath(FieldPath.of(segments));
    }

    /**
     * Creates a copy with field path and updated evidence.
     *
     * <p>This method is used when resolving struct field access, combining
     * both the field path and the struct_field_access evidence marker.</p>
     *
     * @param newFieldPath The field path segments
     * @param newEvidence The evidence string (e.g., "struct_field_access")
     * @return A new ColumnSource with field path and evidence updated
     */
    public ColumnSource withFieldPath(FieldPath newFieldPath, String newEvidence) {
        return new ColumnSource(
            this.sourceNamespace,
            this.exposedName,
            this.definitionNode,
            this.confidence,
            newEvidence,
            this.overrideTable,
            this.candidateTables != null ? new java.util.ArrayList<>(this.candidateTables) : null,
            null, // will create from legacy evidence
            newFieldPath
        );
    }

    /**
     * Count the number of "real" tables in a table list, excluding implicit lateral derived tables.
     *
     * <p>Teradata supports implicit lateral derived tables, which are auto-added when a column
     * references an undeclared table in the WHERE clause. These should not be counted when
     * determining if a subquery has multiple tables for column resolution purposes.</p>
     *
     * @param tables The table list to count
     * @return The number of real (non-implicit) tables
     */
    private static int countRealTables(gudusoft.gsqlparser.nodes.TTableList tables) {
        if (tables == null) {
            return 0;
        }
        int count = 0;
        for (int i = 0; i < tables.size(); i++) {
            TTable table = tables.getTable(i);
            if (table != null && table.getEffectType() != gudusoft.gsqlparser.ETableEffectType.tetImplicitLateralDerivedTable) {
                count++;
            }
        }
        return count;
    }

    /**
     * Check if a SELECT statement has a qualified star column (e.g., ta.*, tb.*).
     * Qualified stars identify which table columns come from in multi-table subqueries.
     */
    private static boolean hasQualifiedStar(gudusoft.gsqlparser.stmt.TSelectSqlStatement select) {
        if (select == null || select.getResultColumnList() == null) {
            return false;
        }
        gudusoft.gsqlparser.nodes.TResultColumnList resultCols = select.getResultColumnList();
        for (int i = 0; i < resultCols.size(); i++) {
            TResultColumn rc = resultCols.getResultColumn(i);
            if (rc != null) {
                String colStr = rc.toString().trim();
                // Qualified star has format "alias.*" or "table.*"
                if (colStr.endsWith("*") && colStr.contains(".")) {
                    return true;
                }
            }
        }
        return false;
    }

    /**
     * Check if a column exists in a table's DDL definition.
     *
     * <p>This method checks the table's column definitions (from CREATE TABLE statements
     * parsed in the same script) to verify if the column name is defined.</p>
     *
     * @param table The table to check
     * @param columnName The column name to look for
     * @return true if the column exists in the table's DDL, false if not found or no DDL available
     */
    public static boolean isColumnInTableDdl(TTable table, String columnName) {
        if (table == null || columnName == null || columnName.isEmpty()) {
            return false;
        }

        // Check if the table has column definitions (from CREATE TABLE DDL)
        gudusoft.gsqlparser.nodes.TColumnDefinitionList columnDefs = table.getColumnDefinitions();
        if (columnDefs != null && columnDefs.size() > 0) {
            for (int i = 0; i < columnDefs.size(); i++) {
                gudusoft.gsqlparser.nodes.TColumnDefinition colDef = columnDefs.getColumn(i);
                if (colDef != null && colDef.getColumnName() != null) {
                    String defColName = colDef.getColumnName().toString();
                    if (defColName != null && defColName.equalsIgnoreCase(columnName)) {
                        return true;
                    }
                }
            }
            // DDL exists but column not found
            return false;
        }

        // No DDL available - return false (cannot verify)
        return false;
    }

    /**
     * Check if a table has DDL metadata available (from CREATE TABLE in same script).
     *
     * @param table The table to check
     * @return true if DDL metadata is available for this table
     */
    public static boolean hasTableDdl(TTable table) {
        if (table == null) {
            return false;
        }
        gudusoft.gsqlparser.nodes.TColumnDefinitionList columnDefs = table.getColumnDefinitions();
        return columnDefs != null && columnDefs.size() > 0;
    }

    /**
     * Check DDL verification status for a candidate table.
     *
     * <p>Returns a tri-state result:</p>
     * <ul>
     *   <li>1 = Column exists in table's DDL</li>
     *   <li>0 = Column NOT found in table's DDL (DDL available but column missing)</li>
     *   <li>-1 = Cannot verify (no DDL available for this table)</li>
     * </ul>
     *
     * @param table The candidate table to check
     * @param columnName The column name to verify
     * @return DDL verification status: 1 (exists), 0 (not found), -1 (no DDL)
     */
    public static int getDdlVerificationStatus(TTable table, String columnName) {
        if (table == null || columnName == null) {
            return -1;
        }

        gudusoft.gsqlparser.nodes.TColumnDefinitionList columnDefs = table.getColumnDefinitions();
        if (columnDefs == null || columnDefs.size() == 0) {
            return -1; // No DDL available
        }

        // DDL available - check if column exists
        for (int i = 0; i < columnDefs.size(); i++) {
            gudusoft.gsqlparser.nodes.TColumnDefinition colDef = columnDefs.getColumn(i);
            if (colDef != null && colDef.getColumnName() != null) {
                String defColName = colDef.getColumnName().toString();
                if (defColName != null && defColName.equalsIgnoreCase(columnName)) {
                    return 1; // Column exists in DDL
                }
            }
        }

        return 0; // DDL exists but column not found
    }

    /**
     * Get DDL verification status for all candidate tables.
     *
     * <p>Returns a map from each candidate table to its DDL verification status:</p>
     * <ul>
     *   <li>1 = Column exists in table's DDL</li>
     *   <li>0 = Column NOT found in table's DDL</li>
     *   <li>-1 = Cannot verify (no DDL available)</li>
     * </ul>
     *
     * @return Map of candidate tables to their DDL verification status, or empty map if no candidates
     */
    public java.util.Map<TTable, Integer> getCandidateTableDdlStatus() {
        java.util.Map<TTable, Integer> result = new java.util.LinkedHashMap<>();
        if (candidateTables == null || candidateTables.isEmpty() || exposedName == null) {
            return result;
        }

        for (TTable candidate : candidateTables) {
            int status = getDdlVerificationStatus(candidate, exposedName);
            result.put(candidate, status);
        }
        return result;
    }
}