package gudusoft.gsqlparser.resolver2;

import gudusoft.gsqlparser.EDbObjectType;
import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.EExpressionType;
import gudusoft.gsqlparser.ESqlClause;
import gudusoft.gsqlparser.ESqlStatementType;
import gudusoft.gsqlparser.ETableEffectType;
import gudusoft.gsqlparser.ETableSource;
import gudusoft.gsqlparser.TBaseType;
import gudusoft.gsqlparser.TCustomSqlStatement;
import gudusoft.gsqlparser.TSourceToken;
import gudusoft.gsqlparser.TStatementList;
import gudusoft.gsqlparser.nodes.*;
import gudusoft.gsqlparser.resolver2.model.ColumnSource;
import gudusoft.gsqlparser.resolver2.namespace.CTENamespace;
import gudusoft.gsqlparser.resolver2.namespace.INamespace;
import gudusoft.gsqlparser.stmt.TSelectSqlStatement;
import gudusoft.gsqlparser.stmt.TMergeSqlStatement;
import gudusoft.gsqlparser.stmt.TCreateTableSqlStatement;
import gudusoft.gsqlparser.stmt.TAlterTableStatement;

import gudusoft.gsqlparser.resolver2.format.DisplayNameMode;
import gudusoft.gsqlparser.resolver2.format.DisplayNameNormalizer;
import gudusoft.gsqlparser.resolver2.matcher.INameMatcher;
import gudusoft.gsqlparser.resolver2.matcher.VendorNameMatcher;
import gudusoft.gsqlparser.resolver2.result.IResolutionResult;
import gudusoft.gsqlparser.resolver2.result.ResolutionResultImpl;
import gudusoft.gsqlparser.sqlenv.ESQLDataObjectType;
import gudusoft.gsqlparser.util.SQLUtil;

import java.util.*;


/**
 * Formats the resolution results from {@link TSQLResolver2} into structured output
 * for testing assertions and debugging.
 *
 * <p>This class provides a consistent way to extract and format <b>Tables / Fields / CTE</b>
 * information from {@link TSQLResolver2} results, handling special scenarios such as
 * <b>UNNEST</b>, <b>CTE</b>, <b>JOIN...USING</b>, <b>SELECT * EXCEPT</b>, etc.</p>
 *
 * <h2>Separation of Concerns Principle</h2>
 *
 * <p><b>Key Principle:</b> {@code TSQLResolver2ResultFormatter} is <b>only responsible for
 * formatting output</b>, NOT for name resolution.</p>
 *
 * <ul>
 *   <li><b>Name resolution logic:</b> Must be implemented in {@link TSQLResolver2},
 *       {@link ScopeBuilder}, and {@link NameResolver}</li>
 *   <li><b>Formatting logic:</b> Implemented in this class ({@code TSQLResolver2ResultFormatter})</li>
 * </ul>
 *
 * <p><b>Violating this principle will cause:</b></p>
 * <ul>
 *   <li>Users directly using the {@link TSQLResolver2} API will not get correct resolution results</li>
 *   <li>Data consistency issues in resolution results</li>
 * </ul>
 *
 * <p>This means the formatter should only <b>read</b> data that has already been resolved
 * by the resolver components. It should never attempt to perform resolution logic such as
 * guessing table names for unresolved columns or modifying the AST nodes.</p>
 *
 * <h2>Configuration Options</h2>
 *
 * <p>The formatter supports various configuration options to control output:</p>
 * <ul>
 *   <li>{@link #setShowCTE(boolean)} - Include CTE tables in output</li>
 *   <li>{@link #setOnlyPhysicalTables(boolean)} - Filter to physical tables only</li>
 *   <li>{@link #setShowUnnest(boolean)} - Include UNNEST tables when onlyPhysicalTables=true</li>
 *   <li>{@link #setShowPivotTable(boolean)} - Include PIVOT tables when onlyPhysicalTables=true</li>
 *   <li>{@link #setShowLateralView(boolean)} - Include LATERAL VIEW tables when onlyPhysicalTables=true</li>
 *   <li>{@link #setShowDatatype(boolean)} - Include column datatypes</li>
 *   <li>{@link #setShowColumnLocation(boolean)} - Include SQL clause location</li>
 *   <li>{@link #setShowTableEffect(boolean)} - Include table effect type</li>
 * </ul>
 *
 * <h2>Usage Example</h2>
 *
 * <pre>{@code
 * TSQLResolver2 resolver = new TSQLResolver2(env, statements, config);
 * resolver.resolve();
 *
 * TSQLResolver2ResultFormatter formatter = new TSQLResolver2ResultFormatter(resolver);
 * formatter.setShowCTE(true);
 * formatter.setShowDatatype(true);
 *
 * String result = formatter.format();
 * }</pre>
 *
 * @see TSQLResolver2
 * @see ScopeBuilder
 * @see NameResolver
 * @see gudusoft.gsqlparser.util.TGetTableColumn
 */
public class TSQLResolver2ResultFormatter {

    // ========== Configuration Options ==========

    /** Include CTE tables and their columns in Fields output */
    private boolean showCTE = false;

    /** Include column datatypes in output (e.g., column:string) */
    private boolean showDatatype = false;

    /** Include separate Ctes section with CTE column definitions */
    private boolean showColumnsOfCTE = false;

    /** Include star columns (*) in output */
    private boolean listStarColumn = true;

    /** Include table effect type in output (e.g., tableName(effectInsert)) */
    private boolean showTableEffect = false;

    /** Include column location/clause in output (e.g., columnName(selectList)) */
    private boolean showColumnLocation = false;

    /**
     * Only include physical/base tables in output, excluding:
     * - PL/SQL record variables (rec_xxx)
     * - Cursor variables
     * - Package variables
     * - Other non-table sources
     *
     * This option makes output compatible with the old TGetTableColumn format.
     */
    private boolean onlyPhysicalTables = false;

    /**
     * When true (default), UNNEST tables will be included in output even when onlyPhysicalTables=true.
     * When false, UNNEST tables are excluded when onlyPhysicalTables=true.
     */
    private boolean showUnnest = true;

    /**
     * When true (default), PIVOT tables will be included in output even when onlyPhysicalTables=true.
     * When false, PIVOT tables are excluded when onlyPhysicalTables=true.
     */
    private boolean showPivotTable = true;

    /**
     * When true (default), LATERAL VIEW tables will be included in output even when onlyPhysicalTables=true.
     * When false, LATERAL VIEW tables are excluded when onlyPhysicalTables=true.
     */
    private boolean showLateralView = true;

    /**
     * When true (default), orphan columns (columns that cannot be definitively linked
     * to a single table due to ambiguity or missing metadata) will be linked to the
     * first candidate table in candidateTables.
     *
     * When false, orphan columns will use "missed" as their table prefix in output.
     *
     * Example: For "SELECT column1 FROM tablea, tableb" where column1 exists in both tables:
     * - linkOrphanColumnToFirstTable=true: outputs "tablea.column1"
     * - linkOrphanColumnToFirstTable=false: outputs "missed.column1"
     */
    private boolean linkOrphanColumnToFirstTable = true;

    // ========== Internal State ==========

    private final TSQLResolver2 resolver;
    private final TStatementList statements;
    private final ScopeBuildResult buildResult;

    /** Name matcher for identifier normalization (from config or default) */
    private final INameMatcher nameMatcher;

    /** Display name normalizer for stripping delimiters without case folding */
    private final DisplayNameNormalizer displayNameNormalizer;

    /** Display name mode (DISPLAY, SQL_RENDER, CANONICAL) */
    private DisplayNameMode displayNameMode = DisplayNameMode.DISPLAY;

    /** Resolution result interface for statement-centric access */
    private final IResolutionResult resolutionResult;

    // ========== Constructor ==========

    /**
     * Create a formatter for the given resolver.
     *
     * @param resolver The TSQLResolver2 instance (must have called resolve())
     */
    public TSQLResolver2ResultFormatter(TSQLResolver2 resolver) {
        this.resolver = resolver;
        this.statements = resolver.getStatements();
        this.buildResult = resolver.getScopeBuildResult();
        // Use name matcher from resolver's config
        TSQLResolverConfig config = resolver.getConfig();
        this.nameMatcher = config != null ? config.getNameMatcher() : null;
        // Initialize display name normalizer
        EDbVendor vendor = config != null ? config.getVendor() : getVendorFromStatements();
        this.displayNameNormalizer = new DisplayNameNormalizer(vendor);
        if (config != null) {
            this.displayNameMode = config.getDisplayNameMode();
            this.displayNameNormalizer.setMode(this.displayNameMode);
            this.displayNameNormalizer.setStripDelimiters(config.isStripDelimitersForDisplay());
        }
        // Create resolution result interface for statement-centric access
        this.resolutionResult = (buildResult != null)
            ? new ResolutionResultImpl(buildResult, statements)
            : null;
    }

    /**
     * Create a formatter with a specific configuration.
     *
     * @param resolver The TSQLResolver2 instance
     * @param config Configuration to apply
     */
    public TSQLResolver2ResultFormatter(TSQLResolver2 resolver, TSQLResolverConfig config) {
        this.resolver = resolver;
        this.statements = resolver.getStatements();
        this.buildResult = resolver.getScopeBuildResult();
        if (config != null) {
            this.showDatatype = config.isShowDatatype();
            this.showCTE = config.isShowCTE();
            this.nameMatcher = config.getNameMatcher();
            this.displayNameMode = config.getDisplayNameMode();
        } else {
            this.nameMatcher = null;
        }
        // Initialize display name normalizer
        EDbVendor vendor = config != null ? config.getVendor() : getVendorFromStatements();
        this.displayNameNormalizer = new DisplayNameNormalizer(vendor);
        if (config != null) {
            this.displayNameNormalizer.setMode(this.displayNameMode);
            this.displayNameNormalizer.setStripDelimiters(config.isStripDelimitersForDisplay());
        }
        // Create resolution result interface for statement-centric access
        this.resolutionResult = (buildResult != null)
            ? new ResolutionResultImpl(buildResult, statements)
            : null;
    }

    /**
     * Get vendor from statements if not provided in config.
     */
    private EDbVendor getVendorFromStatements() {
        if (statements != null && statements.size() > 0) {
            return statements.get(0).dbvendor;
        }
        return null;
    }

    // ========== Configuration Setters ==========

    public TSQLResolver2ResultFormatter setShowCTE(boolean showCTE) {
        this.showCTE = showCTE;
        return this;
    }

    public TSQLResolver2ResultFormatter setShowDatatype(boolean showDatatype) {
        this.showDatatype = showDatatype;
        return this;
    }

    public TSQLResolver2ResultFormatter setShowColumnsOfCTE(boolean showColumnsOfCTE) {
        this.showColumnsOfCTE = showColumnsOfCTE;
        return this;
    }

    public TSQLResolver2ResultFormatter setListStarColumn(boolean listStarColumn) {
        this.listStarColumn = listStarColumn;
        return this;
    }

    /**
     * Get the current display name mode.
     *
     * @return the current DisplayNameMode
     */
    public DisplayNameMode getDisplayNameMode() {
        return displayNameMode;
    }

    /**
     * Set the display name mode for identifier formatting.
     *
     * <p>This controls how identifiers (table names, column names) are formatted in output:</p>
     * <ul>
     *   <li>{@link DisplayNameMode#DISPLAY} - Strip delimiters, preserve original case
     *       (e.g., {@code [OrderID]} → {@code OrderID})</li>
     *   <li>{@link DisplayNameMode#SQL_RENDER} - Preserve delimiters for valid SQL regeneration
     *       (e.g., {@code [Order ID]} → {@code [Order ID]})</li>
     *   <li>{@link DisplayNameMode#CANONICAL} - Apply vendor-specific case folding
     *       (e.g., Oracle: {@code MyTable} → {@code MYTABLE})</li>
     * </ul>
     *
     * <p>This method can be called after instantiation to change the mode for each SQL text
     * being processed.</p>
     *
     * @param mode the DisplayNameMode to use
     * @return this formatter for chaining
     */
    public TSQLResolver2ResultFormatter setDisplayNameMode(DisplayNameMode mode) {
        this.displayNameMode = mode != null ? mode : DisplayNameMode.DISPLAY;
        this.displayNameNormalizer.setMode(this.displayNameMode);
        return this;
    }

    /**
     * Set whether to include table effect type in output.
     * When true, tables will be displayed as tableName(effectType) where effectType
     * indicates how the table is used (e.g., effectInsert, effectUpdate, effectSelect).
     *
     * @param showTableEffect true to include table effect
     * @return this formatter for chaining
     */
    public TSQLResolver2ResultFormatter setShowTableEffect(boolean showTableEffect) {
        this.showTableEffect = showTableEffect;
        return this;
    }

    /**
     * Set whether to include column location/clause in output.
     * When true, columns will be displayed as columnName(location) where location
     * indicates the SQL clause where the column appears (e.g., selectList, where, groupBy).
     *
     * @param showColumnLocation true to include column location
     * @return this formatter for chaining
     */
    public TSQLResolver2ResultFormatter setShowColumnLocation(boolean showColumnLocation) {
        this.showColumnLocation = showColumnLocation;
        return this;
    }

    /**
     * Set whether to only include physical/base tables in output.
     * When true, excludes PL/SQL record variables, cursor variables, and other non-table sources.
     * This makes output compatible with the old TGetTableColumn format.
     *
     * @param onlyPhysicalTables true to filter to physical tables only
     * @return this formatter for chaining
     */
    public TSQLResolver2ResultFormatter setOnlyPhysicalTables(boolean onlyPhysicalTables) {
        this.onlyPhysicalTables = onlyPhysicalTables;
        return this;
    }

    /**
     * Set whether to include UNNEST tables in output when onlyPhysicalTables=true.
     * Default is true (UNNEST tables are always included).
     *
     * @param showUnnest true to include UNNEST tables
     * @return this formatter for chaining
     */
    public TSQLResolver2ResultFormatter setShowUnnest(boolean showUnnest) {
        this.showUnnest = showUnnest;
        return this;
    }

    /**
     * Set whether to include PIVOT tables in output when onlyPhysicalTables=true.
     * Default is true (PIVOT tables are always included).
     *
     * @param showPivotTable true to include PIVOT tables
     * @return this formatter for chaining
     */
    public TSQLResolver2ResultFormatter setShowPivotTable(boolean showPivotTable) {
        this.showPivotTable = showPivotTable;
        return this;
    }

    /**
     * Set whether to include LATERAL VIEW tables in output when onlyPhysicalTables=true.
     * Default is true (LATERAL VIEW tables are always included).
     *
     * @param showLateralView true to include LATERAL VIEW tables
     * @return this formatter for chaining
     */
    public TSQLResolver2ResultFormatter setShowLateralView(boolean showLateralView) {
        this.showLateralView = showLateralView;
        return this;
    }

    /**
     * Set whether orphan columns should be linked to the first candidate table.
     *
     * When true (default), orphan columns (columns that cannot be definitively linked
     * to a single table) will be linked to the first candidate table.
     *
     * When false, orphan columns will use "missed" as their table prefix.
     *
     * @param linkOrphanColumnToFirstTable true to use first candidate, false to use "missed"
     * @return this formatter for chaining
     */
    public TSQLResolver2ResultFormatter setLinkOrphanColumnToFirstTable(boolean linkOrphanColumnToFirstTable) {
        this.linkOrphanColumnToFirstTable = linkOrphanColumnToFirstTable;
        return this;
    }

    // ========== Configuration Getters ==========

    public boolean isShowCTE() { return showCTE; }
    public boolean isShowDatatype() { return showDatatype; }
    public boolean isShowColumnsOfCTE() { return showColumnsOfCTE; }
    public boolean isOnlyPhysicalTables() { return onlyPhysicalTables; }
    public boolean isShowUnnest() { return showUnnest; }
    public boolean isShowPivotTable() { return showPivotTable; }
    public boolean isShowLateralView() { return showLateralView; }
    public boolean isListStarColumn() { return listStarColumn; }
    public boolean isLinkOrphanColumnToFirstTable() { return linkOrphanColumnToFirstTable; }
    public boolean isShowTableEffect() { return showTableEffect; }
    public boolean isShowColumnLocation() { return showColumnLocation; }

    /**
     * Get the resolution result interface for statement-centric access.
     * This provides a clean API for programmatically accessing resolution results.
     *
     * <p>Usage example:</p>
     * <pre>
     * IResolutionResult result = formatter.getResolutionResult();
     * for (TCustomSqlStatement stmt : parser.sqlstatements) {
     *     for (TTable table : result.getTables(stmt)) {
     *         System.out.println("Table: " + table.getFullName());
     *         for (TObjectName col : result.getColumnsForTable(stmt, table)) {
     *             System.out.println("  Column: " + col.getColumnNameOnly());
     *         }
     *     }
     * }
     * </pre>
     *
     * @return The resolution result interface, or null if resolver has not been called
     */
    public IResolutionResult getResolutionResult() {
        return resolutionResult;
    }

    // ========== Main Formatting Methods ==========

    /**
     * Format the resolver results into a structured string.
     *
     * @return Formatted string with Tables, Fields, and optionally Ctes sections
     */
    public String format() {
        // Collect tables
        Set<String> tables = new TreeSet<>(String.CASE_INSENSITIVE_ORDER);
        for (int i = 0; i < statements.size(); i++) {
            collectTablesFromStatement(statements.get(i), tables);
        }

        // Collect fields
        Set<String> fields = new TreeSet<>(String.CASE_INSENSITIVE_ORDER);
        // 1. Collect DDL fields (constraints, CTAS targets) via AST traversal
        for (int i = 0; i < statements.size(); i++) {
            collectDDLFieldsFromStatement(statements.get(i), fields);
        }
        // 2. Collect resolved fields (SELECT/DML) via global single-pass optimization
        collectResolvedFieldsGlobal(fields);

        // Collect CTE columns if needed
        Set<String> cteColumns = new TreeSet<>(String.CASE_INSENSITIVE_ORDER);
        if (showColumnsOfCTE) {
            collectCTEColumns(cteColumns);
        }

        // Build result string
        StringBuilder sb = new StringBuilder();
        sb.append("Tables:\n");
        for (String table : tables) {
            sb.append(table).append("\n");
        }
        sb.append("\n");
        sb.append("Fields:\n");
        for (String field : fields) {
            sb.append(field).append("\n");
        }

        if (showColumnsOfCTE && !cteColumns.isEmpty()) {
            sb.append("\n");
            sb.append("Ctes:\n");
            for (String cteCol : cteColumns) {
                sb.append(cteCol).append("\n");
            }
        }

        return sb.toString().trim();
    }

    /**
     * Get the list of tables found in the statements.
     *
     * @return Set of table names in sorted order
     */
    public Set<String> getTables() {
        Set<String> tables = new TreeSet<>(String.CASE_INSENSITIVE_ORDER);
        for (int i = 0; i < statements.size(); i++) {
            collectTablesFromStatement(statements.get(i), tables);
        }
        return tables;
    }

    /**
     * Get the list of fields (table.column) found in the statements.
     *
     * @return Set of field names in sorted order
     */
    public Set<String> getFields() {
        Set<String> fields = new TreeSet<>(String.CASE_INSENSITIVE_ORDER);
        for (int i = 0; i < statements.size(); i++) {
            collectDDLFieldsFromStatement(statements.get(i), fields);
        }
        collectResolvedFieldsGlobal(fields);
        return fields;
    }

    // ========== Table Collection ==========

    private void collectTablesFromStatement(Object stmt, Set<String> tables) {
        if (!(stmt instanceof TCustomSqlStatement)) {
            return;
        }

        TCustomSqlStatement customStmt = (TCustomSqlStatement) stmt;

        // For top-level statements, use optimized single-pass collection
        if (customStmt.getParentStmt() == null) {
            collectTablesSinglePass(customStmt, tables);
        } else {
            // For nested statements (rare case), use simple collection
            collectTablesFromStatementDirect(customStmt, tables);
        }
    }

    /**
     * Optimized single-pass table collection for top-level statements.
     */
    private void collectTablesSinglePass(TCustomSqlStatement topStmt, Set<String> tables) {
        // Use a queue to avoid deep recursion, and a set to avoid re-processing
        java.util.Deque<TCustomSqlStatement> queue = new java.util.ArrayDeque<>();
        Set<TCustomSqlStatement> visited = java.util.Collections.newSetFromMap(new java.util.IdentityHashMap<>());
        queue.add(topStmt);

        while (!queue.isEmpty()) {
            TCustomSqlStatement stmt = queue.poll();
            if (stmt == null || visited.contains(stmt)) continue;
            visited.add(stmt);

            // Handle UNION/combined queries
            if (stmt instanceof TSelectSqlStatement) {
                TSelectSqlStatement selectStmt = (TSelectSqlStatement) stmt;
                if (selectStmt.isCombinedQuery()) {
                    if (selectStmt.getLeftStmt() != null) queue.add(selectStmt.getLeftStmt());
                    if (selectStmt.getRightStmt() != null) queue.add(selectStmt.getRightStmt());
                    continue;
                }
            }

            // Handle CREATE TABLE target
            if (stmt instanceof TCreateTableSqlStatement) {
                TCreateTableSqlStatement createStmt = (TCreateTableSqlStatement) stmt;
                if (createStmt.getTargetTable() != null) {
                    tables.add(getTableDisplayName(createStmt.getTargetTable()));
                }
            }

            // Collect tables from this statement
            for (int i = 0; i < stmt.tables.size(); i++) {
                TTable table = stmt.tables.getTable(i);
                if (table == null) continue;

                // Add subquery's statement to queue
                if (table.getTableType() == ETableSource.subquery) {
                    if (table.getSubquery() != null) queue.add(table.getSubquery());
                    continue;
                }

                // Skip JOINs (they're containers, not actual tables)
                if (table.getTableType() == ETableSource.join) continue;

                // Skip function tables (table-valued functions) - they're included in Fields, not Tables
                if (table.getTableType() == ETableSource.function) continue;

                // Skip CTEs if showCTE is false
                if (table.isCTEName() && !showCTE) continue;

                // Skip virtual trigger tables (deleted/inserted) - resolved to trigger target during name resolution
                // This is unconditional - virtual trigger tables should never appear in output
                if (resolver != null && resolver.getVirtualTriggerTables().contains(table)) continue;

                // Skip non-physical tables (record variables, etc.) if onlyPhysicalTables is true
                if (shouldSkipTableForPhysicalFilter(table)) continue;

                tables.add(getTableDisplayName(table));
            }

            // Add nested statements to queue
            for (int i = 0; i < stmt.getStatements().size(); i++) {
                Object nested = stmt.getStatements().get(i);
                if (nested instanceof TCustomSqlStatement) {
                    queue.add((TCustomSqlStatement) nested);
                }
            }
        }
    }

    /**
     * Direct table collection for a single statement (no deep recursion).
     */
    private void collectTablesFromStatementDirect(TCustomSqlStatement customStmt, Set<String> tables) {
        // Handle CREATE TABLE target
        if (customStmt instanceof TCreateTableSqlStatement) {
            TCreateTableSqlStatement createStmt = (TCreateTableSqlStatement) customStmt;
            if (createStmt.getTargetTable() != null) {
                tables.add(getTableDisplayName(createStmt.getTargetTable()));
            }
        }

        // Collect tables from statement
        for (int i = 0; i < customStmt.tables.size(); i++) {
            TTable table = customStmt.tables.getTable(i);
            if (table == null) continue;
            if (table.getTableType() == ETableSource.subquery) continue;
            if (table.getTableType() == ETableSource.join) continue;
            if (table.getTableType() == ETableSource.function) continue;
            if (table.isCTEName() && !showCTE) continue;
            // Skip virtual trigger tables (deleted/inserted) - resolved to trigger target during name resolution
            if (resolver != null && resolver.getVirtualTriggerTables().contains(table)) continue;
            if (shouldSkipTableForPhysicalFilter(table)) continue;

            tables.add(getTableDisplayName(table));
        }
    }

    private String getTableDisplayName(TTable table) {
        String baseName;
        if (table.getTableType() == ETableSource.subquery) {
            return "(subquery, alias:" + table.getAliasName() + ")";
        } else if (table.getTableType() == ETableSource.unnest) {
            String alias = table.getAliasName();
            return (alias != null && !alias.isEmpty() ? alias : "") + "(unnest table)";
        } else if (table.getTableType() == ETableSource.pivoted_table) {
            // For Tables section, return just the table name without prefix
            baseName = table.getTableName() != null ? normalizeTableName(table.getTableName()) : table.getName();
        } else if (table.getTableType() == ETableSource.function) {
            return "(table-valued function:" + normalizeTableName(table.getTableName()) + ")";
        } else if (table.getTableType() == ETableSource.lateralView) {
            return "(lateral_view:" + normalizeTableName(table.getTableName()) + ")";
        } else if (table.isCTEName()) {
            baseName = table.getTableName() != null ? normalizeTableName(table.getTableName()) : table.getName();
        } else {
            baseName = table.getTableName() != null ? normalizeTableName(table.getTableName()) : table.getName();
        }

        // Append table effect type if enabled and table is a base table
        if (showTableEffect && table.isBaseTable()) {
            return baseName + "(" + table.getEffectType() + ")";
        }
        return baseName;
    }

    /**
     * Check if a table is a physical/base table based on table type information
     * already determined by TSQLResolver2.
     *
     * <p>This method only uses semantic information available from the table object,
     * not name-based heuristics. Non-physical tables include:</p>
     * <ul>
     *   <li>Subqueries (tableType = subquery)</li>
     *   <li>JOINs (tableType = join)</li>
     *   <li>Table functions (tableType = function)</li>
     *   <li>CTEs (isCTEName = true)</li>
     * </ul>
     *
     * <p>Note: Virtual trigger tables (deleted/inserted in SQL Server triggers) are
     * filtered unconditionally in the table collection methods, not here.</p>
     *
     * @param table The TTable to check
     * @return true if the table is a physical database table
     */
    private boolean isPhysicalTable(TTable table) {
        if (table == null) return false;

        // Only objectname type tables can be physical tables
        // This filters out subqueries, joins, functions, pivoted tables, unnest, etc.
        if (table.getTableType() != ETableSource.objectname) {
            return false;
        }

        // Tables from CTE are considered virtual, not physical
        if (table.isCTEName()) {
            return false;
        }

        String tableName = table.getName();
        if (tableName == null || tableName.isEmpty()) {
            return false;
        }

        return true;
    }

    /**
     * Check if a table should be skipped based on onlyPhysicalTables and the show* options.
     *
     * <p>When onlyPhysicalTables=true, tables are skipped unless:</p>
     * <ul>
     *   <li>They are physical tables (isPhysicalTable returns true)</li>
     *   <li>They are UNNEST tables and showUnnest=true</li>
     *   <li>They are PIVOT tables and showPivotTable=true</li>
     *   <li>They are LATERAL VIEW tables and showLateralView=true</li>
     * </ul>
     *
     * @param table The TTable to check
     * @return true if the table should be skipped, false if it should be included
     */
    private boolean shouldSkipTableForPhysicalFilter(TTable table) {
        if (!onlyPhysicalTables) {
            return false; // Not filtering, don't skip
        }

        // CTAS target tables are DDL targets, not existing physical tables
        // Skip them when onlyPhysicalTables=true
        if (buildResult != null && buildResult.isCTASTargetTable(table)) {
            return true;
        }

        // Physical tables are always included when onlyPhysicalTables=true
        if (isPhysicalTable(table)) {
            return false;
        }

        // Check if table type should be included based on show* options
        ETableSource tableType = table.getTableType();
        if (tableType == ETableSource.unnest && showUnnest) {
            return false;
        }
        if (tableType == ETableSource.pivoted_table && showPivotTable) {
            return false;
        }
        if (tableType == ETableSource.lateralView && showLateralView) {
            return false;
        }

        // All other non-physical tables are skipped when onlyPhysicalTables=true
        return true;
    }

    // ========== Field Collection ==========

    /**
     * Collect DDL fields (constraints, CTAS targets) from statements.
     * This only handles DDL-specific columns not covered by the resolver's column list.
     */
    private void collectDDLFieldsFromStatement(Object stmt, Set<String> fields) {
        if (!(stmt instanceof TCustomSqlStatement)) {
            return;
        }

        TCustomSqlStatement customStmt = (TCustomSqlStatement) stmt;

        // Handle UNION/combined queries - follow left chain iteratively
        if (customStmt instanceof TSelectSqlStatement) {
            TSelectSqlStatement selectStmt = (TSelectSqlStatement) customStmt;
            while (selectStmt.isCombinedQuery()) {
                collectDDLFieldsFromStatement(selectStmt.getRightStmt(), fields);
                selectStmt = selectStmt.getLeftStmt();
            }
            if (selectStmt != customStmt) {
                collectDDLFieldsFromStatement(selectStmt, fields);
                return;
            }
        }

        // Handle CREATE TABLE - collect constraint columns only
        // Note: CTAS target columns (tuple aliases, standard aliases, simple column refs)
        // are now fully handled by ScopeBuilder.preVisit(TResultColumn) and included in
        // allColumnReferences. They will be output via collectResolvedFieldsGlobal().
        if (customStmt instanceof TCreateTableSqlStatement) {
            TCreateTableSqlStatement createStmt = (TCreateTableSqlStatement) customStmt;
            // Collect constraint columns (PRIMARY KEY, UNIQUE, FOREIGN KEY references)
            collectConstraintColumns(createStmt, fields);
        }

        // Handle ALTER TABLE - collect constraint columns
        if (customStmt instanceof TAlterTableStatement) {
            TAlterTableStatement alterStmt = (TAlterTableStatement) customStmt;
            collectAlterTableConstraintColumns(alterStmt, fields);
        }

        // Recurse for nested statements to find deeper DDLs (e.g. inside blocks)
        for (int i = 0; i < customStmt.getStatements().size(); i++) {
            Object nestedStmt = customStmt.getStatements().get(i);
            if (nestedStmt instanceof TCustomSqlStatement) {
                collectDDLFieldsFromStatement(nestedStmt, fields);
            }
        }
    }

    /**
     * Optimized global field collection for all resolved columns.
     * Iterates through all column references once globally, calculating table prefixes on-the-fly.
     * This avoids the O(N*M) complexity of per-statement AST traversal for deep nesting.
     */
    private void collectResolvedFieldsGlobal(Set<String> fields) {
        if (buildResult == null) return;

        // Get vendor info once
        TSQLResolverConfig resolverConfig = resolver != null ? resolver.getConfig() : null;
        EDbVendor vendor = resolverConfig != null ? resolverConfig.getVendor() : null;
        if (vendor == null && statements != null && statements.size() > 0) {
            vendor = statements.get(0).dbvendor;
        }
        boolean isSparkOrHive = vendor == EDbVendor.dbvsparksql || vendor == EDbVendor.dbvhive;
        boolean isSnowflake = vendor == EDbVendor.dbvsnowflake;
        boolean isSqlServer = vendor == EDbVendor.dbvmssql || vendor == EDbVendor.dbvazuresql;


        // Single pass through all column references in the entire session
        java.util.List<TObjectName> allColumnReferences = buildResult.getAllColumnReferences();
        for (TObjectName col : allColumnReferences) {
            if (col == null) continue;
            if (col.getValidate_column_status() == TBaseType.MARKED_NOT_A_COLUMN_IN_COLUMN_RESOLVER) continue;
            if (col.getDbObjectType() == EDbObjectType.column_alias) continue;

            // Get column name with vendor-specific handling
            String columnName = getColumnDisplayName(col, isSparkOrHive, isSnowflake, isSqlServer);

            if (columnName == null || columnName.isEmpty()) continue;

            // Handle star columns specially when listStarColumn=false:
            // - We need to process unqualified stars (*) for PIVOT/UNNEST column expansion
            // - Other star columns (qualified like table.*, OUTPUT DELETED.*) should be skipped
            if ("*".equals(columnName) && !listStarColumn) {
                // Only process unqualified stars (SELECT *) for PIVOT/UNNEST expansion
                if (!isUnqualifiedStar(col)) {
                    continue; // Skip qualified stars when listStarColumn=false
                }
                // For unqualified stars, continue processing to reach expandStarToAllTables()
            }

            // Skip ROWNUM pseudo-column when filtering to physical tables
            // Note: ROWID is a real column stored in the table, but ROWNUM is a query-result pseudo-column
            if (onlyPhysicalTables && "ROWNUM".equalsIgnoreCase(columnName)) continue;

            // Get source table
            TTable sourceTable = col.getSourceTable();

            // Handle star columns specially
            if ("*".equals(columnName)) {
                if (!shouldIncludeStarColumn(col, sourceTable)) continue;

                // For unqualified star columns (SELECT *), expand to all tables in FROM clause
                // Note: We use expandStarToAllTables() which adds table.* entries for each table
                // The core resolver's star expansion (attributeNodesDerivedFromFromClause) is for
                // internal resolution purposes, not for output formatting
                if (isUnqualifiedStar(col)) {
                    expandStarToAllTables(col, fields);
                    continue;
                }
            }

            // Check if this is a qualified star column with expanded attributes from push-down
            // These are columns like "src.*" that have been expanded via TSQLResolver2's
            // star column push-down algorithm
            // Note: Use col.toString() instead of columnName, because columnName (from getColumnNameOnly)
            // would be just "*", but we need "src.*" to detect qualified stars
            String colFullString = col.toString();
            if (colFullString != null && colFullString.endsWith("*") && !colFullString.equals("*")) {
                java.util.ArrayList<gudusoft.gsqlparser.TAttributeNode> expandedAttrs =
                    col.getAttributeNodesDerivedFromFromClause();
                if (expandedAttrs != null && !expandedAttrs.isEmpty()) {
                    // Only process if the star column's sourceTable is a physical table (not subquery/CTE/pivot)
                    // This ensures we output with the correct base table prefix
                    // PIVOT tables are excluded since their columns are output individually via PivotNamespace
                    if (sourceTable != null &&
                        sourceTable.getTableType() != ETableSource.subquery &&
                        sourceTable.getTableType() != ETableSource.openquery &&
                        sourceTable.getTableType() != ETableSource.join &&
                        sourceTable.getTableType() != ETableSource.pivoted_table &&
                        !(sourceTable.isCTEName() && !showCTE) &&
                        !shouldSkipTableForPhysicalFilter(sourceTable)) {

                        String tablePrefix = getFieldTablePrefix(sourceTable);
                        if (tablePrefix != null) {
                            // Output the star column itself if listStarColumn is true
                            if (listStarColumn) {
                                fields.add(tablePrefix + ".*");
                            }

                            // Output the expanded individual columns using the star's source table
                            // This ensures columns pushed down through the star get the correct table prefix
                            // BUT skip columns that are explicitly resolved elsewhere (to a different table)
                            for (gudusoft.gsqlparser.TAttributeNode attr : expandedAttrs) {
                                if (attr == null) continue;
                                String attrName = attr.getName();
                                if (attrName != null && !attrName.isEmpty() && !attrName.endsWith("*")) {
                                    // Extract just the column name
                                    String justColumnName = attrName;
                                    int dotIdx = attrName.lastIndexOf('.');
                                    if (dotIdx >= 0) {
                                        justColumnName = attrName.substring(dotIdx + 1);
                                    }

                                    // Check if this column is explicitly resolved to a DIFFERENT PHYSICAL table
                                    // in the column references list - if so, skip it to avoid duplicates
                                    // Note: We use getFinalTable() to trace through subqueries to the actual physical table
                                    // This handles cases like: SELECT al1.COL1 FROM (SELECT t1.COL1, t2.* FROM T1 t1 JOIN T2 t2) al1
                                    // where al1.COL1 should resolve to T1.COL1 (explicit column), not T2.COL1 (from star)
                                    boolean resolvedToOtherPhysicalTable = false;
                                    for (TObjectName otherCol : allColumnReferences) {
                                        if (otherCol == null || otherCol == col) continue;
                                        String otherColName = otherCol.getColumnNameOnly();
                                        if (otherColName != null && (vendor == null
                                                ? otherColName.equalsIgnoreCase(justColumnName)
                                                : SQLUtil.sameName(vendor, ESQLDataObjectType.dotColumn, otherColName, justColumnName))) {
                                            // Get the final physical table through ColumnSource or sourceTable
                                            TTable otherFinalTable = null;
                                            ColumnSource otherColSource = otherCol.getColumnSource();
                                            if (otherColSource != null) {
                                                otherFinalTable = otherColSource.getFinalTable();
                                            }
                                            // Fallback to sourceTable if ColumnSource doesn't have finalTable
                                            if (otherFinalTable == null) {
                                                otherFinalTable = otherCol.getSourceTable();
                                            }

                                            if (otherFinalTable != null && otherFinalTable != sourceTable) {
                                                // Check if the other source is a physical table (not CTE/subquery)
                                                if (otherFinalTable.getTableType() != ETableSource.subquery &&
                                                    otherFinalTable.getTableType() != ETableSource.openquery &&
                                                    otherFinalTable.getTableType() != ETableSource.join &&
                                                    !otherFinalTable.isCTEName()) {
                                                    // Resolved to a different physical table - skip
                                                    resolvedToOtherPhysicalTable = true;
                                                    break;
                                                }
                                            }
                                        }
                                    }

                                    if (!resolvedToOtherPhysicalTable) {
                                        fields.add(tablePrefix + "." + normalizeColumnName(justColumnName));
                                    }
                                }
                            }
                        }
                    }
                    continue; // Star column fully handled
                }
            }

            // Use ColumnSource to determine final table (if available)
            ColumnSource source = col.getColumnSource();
            if (source != null) {
                // Check if the resolution is ambiguous - if so, don't use getFinalTable()
                // because getColumnSource() returns the first candidate but we shouldn't pick one
                gudusoft.gsqlparser.resolver2.model.ResolutionResult colResolution = col.getResolution();
                boolean isResolutionAmbiguous = colResolution != null && colResolution.isAmbiguous();

                // For columns from UNION queries, get all tables and add an entry for each
                java.util.List<TTable> allFinalTables = source.getAllFinalTables();
                if (allFinalTables != null && allFinalTables.size() > 1) {
                    // Multiple tables (UNION query) - add entry for each table
                    for (TTable unionTable : allFinalTables) {
                        if (unionTable == null) continue;
                        if (unionTable.getTableType() == ETableSource.subquery) continue;
                        if (unionTable.getTableType() == ETableSource.openquery) continue;
                        if (unionTable.getTableType() == ETableSource.join) continue;
                        if (unionTable.isCTEName() && !showCTE) continue;
                        if (shouldSkipTableForPhysicalFilter(unionTable)) continue;

                        String tablePrefix = getFieldTablePrefix(unionTable);
                        if (tablePrefix != null) {
                            String datatypeStr = showDatatype ? getColumnDatatype(col) : "";
                            String locationStr = showColumnLocation ? "(" + col.getLocation() + ")" : "";
                            fields.add(tablePrefix + "." + columnName + locationStr + datatypeStr);
                        }
                    }
                    continue; // Already handled this column for all tables
                }

                // Check if this is a column alias tracing (e.g., "col AS alias")
                // When getFinalColumnName() is non-null, the column name is an alias.
                // The original column reference inside the subquery already produces the
                // correct entry in the output, so skip getFinalTable() to avoid duplicates.
                String finalColName = source.getFinalColumnName();
                TTable finalTable = source.getFinalTable();
                // Only use finalTable if the resolution is NOT ambiguous
                // For ambiguous resolutions, let the orphan handling deal with it
                if (finalTable != null && !isResolutionAmbiguous && finalColName == null) {
                    sourceTable = finalTable;
                } else if (sourceTable == null && !source.isAmbiguous() && !isResolutionAmbiguous) {
                    // ColumnSource exists but getFinalTable() is null AND sourceTable not set
                    // AND not an ambiguous multi-table case
                    // Check if we have candidate tables (e.g., from UNION branches)
                    // If so, don't skip - let the orphan handling output all candidates
                    java.util.List<TTable> candidates = source.getCandidateTables();
                    if (candidates == null || candidates.isEmpty()) {
                        // No candidates either - this is truly a calculated expression
                        // Skip these - they don't trace to physical tables
                        continue;
                    }
                    // Has candidate tables - fall through to orphan handling which will output them
                }
                // If getFinalTable() is null but sourceTable is set (e.g., UPDATE SET clause),
                // use sourceTable as fallback
            }
            // If ColumnSource is null, fall back to col.getSourceTable()
            // This handles INSERT columns, direct star columns, and other cases
            // where the resolver doesn't set ColumnSource but the parser did resolve sourceTable

            // Handle orphan columns (no definitive source table)
            // Also handle columns that point to a subquery but couldn't be traced to a physical table
            boolean isOrphan = (sourceTable == null);
            boolean isUnresolvedSubqueryColumn = false;

            // Check if resolution or ColumnSource indicates ambiguity (multiple candidate tables)
            // Note: col.getResolution().isAmbiguous() checks the resolution result
            // source.isAmbiguous() checks if the ColumnSource has multiple candidate tables
            // For ambiguous resolutions, getColumnSource() returns the first candidate which won't have isAmbiguous()=true
            gudusoft.gsqlparser.resolver2.model.ResolutionResult resolvedResult = col.getResolution();
            if ((resolvedResult != null && resolvedResult.isAmbiguous()) ||
                (source != null && source.isAmbiguous())) {
                isUnresolvedSubqueryColumn = true;
            } else if (sourceTable != null && sourceTable.getTableType() == ETableSource.subquery && source == null) {
                // Check if this column is explicitly defined in the subquery's SELECT list
                // If so, it's a calculated/aliased column - skip it
                // If not, it's through an ambiguous star - mark as "missed"
                TSelectSqlStatement subquery = sourceTable.getSubquery();
                if (subquery != null && subquery.getResultColumnList() != null) {
                    boolean foundInSubquery = false;
                    boolean hasAmbiguousStar = false;
                    int tableCount = 0;

                    // Count tables in FROM clause
                    if (subquery.tables != null) {
                        tableCount = subquery.tables.size();
                    }

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

                        String rcStr = rc.toString().trim();
                        // Check if it's an unqualified star with multiple tables
                        if (rcStr.equals("*") && tableCount > 1) {
                            hasAmbiguousStar = true;
                            continue;
                        }

                        // Check for explicit column/alias name
                        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 && nameMatcher.matches(rcName, columnName)) {
                            foundInSubquery = true;
                            break;
                        }
                    }

                    // Column is unresolved only if it's NOT found in explicit columns AND there's an ambiguous star
                    isUnresolvedSubqueryColumn = !foundInSubquery && hasAmbiguousStar;
                }
            }

            if (isOrphan || isUnresolvedSubqueryColumn) {
                // Check for candidate tables (e.g., from UNION branches)
                TTableList candidates = col.getCandidateTables();
                boolean hasCandidates = candidates != null && candidates.size() > 0;

                // If we have multiple candidate tables from UNION/CTE propagation, output all of them
                // For regular ambiguous columns (not from UNION), fall through to "missed" handling
                if (hasCandidates && candidates.size() > 1 && sourceTable == null && col.isCandidatesFromUnion()) {
                    String datatypeStr = showDatatype ? getColumnDatatype(col) : "";
                    String locationStr = showColumnLocation ? "(" + col.getLocation() + ")" : "";
                    for (int ci = 0; ci < candidates.size(); ci++) {
                        TTable candidateTable = candidates.getTable(ci);
                        if (candidateTable != null) {
                            // Filter out tables that shouldn't be reported
                            if (candidateTable.getTableType() == ETableSource.subquery) continue;
                            if (candidateTable.getTableType() == ETableSource.openquery) continue;
                            if (candidateTable.getTableType() == ETableSource.join) continue;
                            if (candidateTable.isCTEName() && !showCTE) continue;
                            if (shouldSkipTableForPhysicalFilter(candidateTable)) continue;

                            String tablePrefix = getFieldTablePrefix(candidateTable);
                            if (tablePrefix != null) {
                                fields.add(tablePrefix + "." + columnName + locationStr + datatypeStr);
                            }
                        }
                    }
                    continue; // Already handled all candidates
                }

                // Orphan column handling:
                // 1. Has candidates AND linkOrphanColumnToFirstTable=true → use first candidate table
                // 2. No candidates AND linkOrphanColumnToFirstTable=true → use statement's first physical table
                // 3. linkOrphanColumnToFirstTable=false → output missed.column
                // 4. No table available at all → output missed.column
                // Note: onlyPhysicalTables does NOT affect orphan column output
                if (sourceTable == null) {
                    // Check if the column resolved to a CTE - if so, skip it when showCTE=false
                    if (source != null) {
                        TTable finalTable = source.getFinalTable();
                        if (finalTable != null && finalTable.isCTEName() && !showCTE) {
                            continue; // Skip CTE columns when not showing CTEs
                        }
                    }

                    if (hasCandidates && linkOrphanColumnToFirstTable) {
                        // Has candidates and linkOrphanColumnToFirstTable=true → use first candidate
                        sourceTable = candidates.getTable(0);
                    } else if (linkOrphanColumnToFirstTable) {
                        // No candidates but linkOrphanColumnToFirstTable=true
                        // → try statement's first physical table (matching TGetTableColumn behavior)
                        TCustomSqlStatement ownStmt = col.getOwnStmt();
                        // If ownStmt is null, try to find containing statement from AST
                        if (ownStmt == null) {
                            ownStmt = findContainingStatement(col);
                        }
                        if (ownStmt != null) {
                            TTable firstPhysicalTable = ownStmt.getFirstPhysicalTable();
                            if (firstPhysicalTable != null) {
                                sourceTable = firstPhysicalTable;
                            }
                        }
                        if (sourceTable == null) {
                            // Still no table → output as missed
                            String datatypeStr = showDatatype ? getColumnDatatype(col) : "";
                            String positionStr = getColumnPositionStr(col);
                            fields.add("missed." + columnName + positionStr + datatypeStr);
                            continue;
                        }
                    } else {
                        // linkOrphanColumnToFirstTable=false → output as missed
                        String datatypeStr = showDatatype ? getColumnDatatype(col) : "";
                        String positionStr = getColumnPositionStr(col);
                        fields.add("missed." + columnName + positionStr + datatypeStr);
                        continue;
                    }
                }

                // For unresolved subquery columns (ambiguous), output as missed if linkOrphanColumnToFirstTable is false
                if (isUnresolvedSubqueryColumn && !linkOrphanColumnToFirstTable) {
                    String datatypeStr = showDatatype ? getColumnDatatype(col) : "";
                    String positionStr = getColumnPositionStr(col);
                    fields.add("missed." + columnName + positionStr + datatypeStr);
                    continue;
                }
            }

            // Filter out tables that shouldn't be reported
            if (sourceTable.getTableType() == ETableSource.subquery) continue;
            if (sourceTable.getTableType() == ETableSource.openquery) continue;
            if (sourceTable.getTableType() == ETableSource.join) continue;
            if (sourceTable.isCTEName() && !showCTE) continue;

            // Skip non-physical tables (record variables, etc.) if onlyPhysicalTables is true
            if (shouldSkipTableForPhysicalFilter(sourceTable)) continue;

            // Skip star columns for PIVOT tables - individual columns are output via PivotNamespace
            if ("*".equals(columnName) && sourceTable.getTableType() == ETableSource.pivoted_table) continue;

            // Calculate table prefix on-the-fly (avoids pre-building map via AST traversal)
            String tablePrefix = getFieldTablePrefix(sourceTable);
            if (tablePrefix != null) {
                String datatypeStr = showDatatype ? getColumnDatatype(col) : "";
                String locationStr = showColumnLocation ? "(" + col.getLocation() + ")" : "";
                fields.add(tablePrefix + "." + columnName + locationStr + datatypeStr);

                // For star columns on UNNEST tables, expand
                if ("*".equals(columnName) && sourceTable.getTableType() == ETableSource.unnest) {
                    expandUnnestStarColumns(sourceTable, tablePrefix, fields);
                }
            }
        }

        // NOTE: TD_UNPIVOT columns are now collected by ScopeBuilder.processTDUnpivotTable()
        // which adds them to allColumnReferences. No need for fallback here.
    }

    /**
     * Get the display name for a column, with vendor-specific handling.
     *
     * For STRUCT field access (BigQuery/Snowflake/SparkSQL), this uses the
     * ColumnSource.exposedName which contains the base column name, rather than
     * getColumnNameOnly() which returns the nested field name.
     */
    private String getColumnDisplayName(TObjectName col, boolean isSparkOrHive, boolean isSnowflake, boolean isSqlServer) {
        String colString = col.toString();
        String colNameOnly = col.getColumnNameOnly();

        // For STRUCT field access (detected by evidence marker), use ColumnSource.exposedName
        // which contains the base column name (e.g., "customer" for "customer.customer_id")
        ColumnSource columnSource = col.getColumnSource();
        if (columnSource != null && "struct_field_access".equals(columnSource.getEvidence())) {
            String exposedName = columnSource.getExposedName();
            if (exposedName != null && !exposedName.isEmpty()) {
                colNameOnly = exposedName;
            }
        }

        if (isSqlServer && colNameOnly != null &&
            colNameOnly.startsWith("[") && colNameOnly.endsWith("]") &&
            colNameOnly.length() > 2) {
            // Normalize SQL Server bracketed identifiers to avoid duplicates like [col3] and col3
            // The TreeSet uses case-insensitive ordering but doesn't handle bracket normalization
            return normalizeColumnName(colNameOnly);
        } else if (isSparkOrHive && colString != null && colNameOnly != null &&
            !colString.contains(".") &&
            colString.startsWith("`") && colString.endsWith("`") &&
            colString.length() > 2) {
            // Use DisplayNameNormalizer to handle backtick-quoted identifiers
            // This respects displayNameMode (DISPLAY strips quotes, SQL_RENDER preserves them)
            return normalizeColumnName(colString);
        } else if (isSnowflake && colString != null && colNameOnly != null &&
            !colString.contains(".") &&
            colString.startsWith("\"") && colString.endsWith("\"") &&
            colString.length() > 2) {
            // Use DisplayNameNormalizer to handle double-quoted identifiers
            // This respects displayNameMode (DISPLAY strips quotes, SQL_RENDER preserves them)
            return normalizeColumnName(colString);
        } else if (isSnowflake && colString != null && colString.startsWith("$")) {
            // Snowflake stage file positional column (e.g., $1, $1:apMac)
            // Extract JSON path if present
            int colonIndex = colString.indexOf(':');
            if (colonIndex > 0) {
                // Has JSON path - extract it (e.g., "$1:apMac" -> ":apMac")
                String jsonPath = colString.substring(colonIndex);
                return jsonPath;
            } else {
                // Simple positional column (e.g., "$1") - return as-is
                return colString;
            }
        } else {
            return normalizeColumnName(colNameOnly);
        }
    }

    /**
     * Check if a star column should be included in output.
     */
    private boolean shouldIncludeStarColumn(TObjectName col, TTable sourceTable) {
        // Skip if the star's source table reference is a CTE
        if (sourceTable != null && sourceTable.isCTEName()) return false;

        // Skip if the star was resolved through a CTE namespace
        ColumnSource starSource = col.getColumnSource();
        if (starSource != null && starSource.getSourceNamespace() instanceof CTENamespace) return false;

        // Skip if the star's source table is a PIVOT/UNPIVOT table
        // PIVOT columns are output individually via PivotNamespace, not as table.*
        if (sourceTable != null && sourceTable.getTableType() == ETableSource.pivoted_table) return false;

        // Find the SELECT statement containing this star column
        TParseTreeNode parent = col.getParentObjectName();
        TSelectSqlStatement starSelectStmt = null;
        while (parent != null) {
            if (parent instanceof TSelectSqlStatement) {
                starSelectStmt = (TSelectSqlStatement) parent;
                break;
            }
            parent = parent.getParentObjectName();
        }

        if (starSelectStmt != null) {
            // Skip if in a nested procedural statement (e.g., inside BEGIN/END block)
            if (starSelectStmt.getParentStmt() != null) return false;

            // Check if sourceTable is in the star's SELECT statement
            // This ensures we only report stars that trace to tables in their own SELECT
            if (sourceTable != null) {
                boolean foundInSameStmt = false;
                for (int ti = 0; ti < starSelectStmt.tables.size(); ti++) {
                    if (starSelectStmt.tables.getTable(ti) == sourceTable) {
                        foundInSameStmt = true;
                        break;
                    }
                }
                if (!foundInSameStmt) return false;
            }
        }

        return true;
    }

    /**
     * Check if a star column is unqualified (SELECT * vs SELECT t.*)
     */
    private boolean isUnqualifiedStar(TObjectName col) {
        // Check if the column has a table prefix
        String colStr = col.toString();
        // Unqualified star is just "*" without any prefix
        return "*".equals(colStr.trim());
    }

    /**
     * Find the statement that directly contains the given table in its tables list.
     * Returns the most specific (innermost) statement that has the table.
     */
    private TCustomSqlStatement findStatementWithTable(TCustomSqlStatement stmt, TTable targetTable) {
        if (stmt == null) return null;

        // Check if this statement directly contains the table
        if (stmt.tables != null) {
            for (int i = 0; i < stmt.tables.size(); i++) {
                if (stmt.tables.getTable(i) == targetTable) {
                    return stmt;
                }
            }
        }

        // Check nested statements (subqueries, etc.)
        if (stmt.getStatements() != null) {
            for (int i = 0; i < stmt.getStatements().size(); i++) {
                Object nested = stmt.getStatements().get(i);
                if (nested instanceof TCustomSqlStatement) {
                    TCustomSqlStatement found = findStatementWithTable((TCustomSqlStatement) nested, targetTable);
                    if (found != null) return found;
                }
            }
        }

        // For SELECT statements, iteratively check UNION branches
        if (stmt instanceof TSelectSqlStatement) {
            TSelectSqlStatement selectStmt = (TSelectSqlStatement) stmt;
            if (selectStmt.isCombinedQuery()) {
                Deque<TSelectSqlStatement> stack = new ArrayDeque<>();
                stack.push(selectStmt);
                while (!stack.isEmpty()) {
                    TSelectSqlStatement current = stack.pop();
                    if (current.isCombinedQuery()) {
                        if (current.getRightStmt() != null) stack.push(current.getRightStmt());
                        if (current.getLeftStmt() != null) stack.push(current.getLeftStmt());
                    } else {
                        TCustomSqlStatement found = findStatementWithTable(current, targetTable);
                        if (found != null) return found;
                    }
                }
            }
        }

        return null;
    }

    /**
     * Expand unqualified star column to all tables in the FROM clause
     */
    private void expandStarToAllTables(TObjectName col, Set<String> fields) {
        // Find the containing statement - try multiple approaches
        TCustomSqlStatement containingStmt = null;

        // Approach 1 (PRIORITY): Find via TResultColumn parent
        // For star columns in SELECT list, traverse up to find the TResultColumn,
        // then get the containing statement from the result column list's parent.
        // This is more reliable than parent chain traversal because the star column's
        // TObjectName parent may have been modified during resolution.
        TParseTreeNode parent = col.getParentObjectName();
        while (parent != null) {
            if (parent instanceof gudusoft.gsqlparser.nodes.TResultColumn) {
                // Found the result column - now get its parent statement
                gudusoft.gsqlparser.nodes.TResultColumn rc = (gudusoft.gsqlparser.nodes.TResultColumn) parent;
                TParseTreeNode rcParent = rc.getParentObjectName();
                while (rcParent != null) {
                    if (rcParent instanceof TCustomSqlStatement) {
                        containingStmt = (TCustomSqlStatement) rcParent;
                        break;
                    }
                    rcParent = rcParent.getParentObjectName();
                }
                break;
            }
            if (parent instanceof TCustomSqlStatement) {
                containingStmt = (TCustomSqlStatement) parent;
                break;
            }
            parent = parent.getParentObjectName();
        }

        // Approach 2 (FALLBACK): Try to find via source table's statement
        if (containingStmt == null) {
            TTable sourceTable = col.getSourceTable();
            if (sourceTable != null && sourceTable.getGsqlparser() != null) {
                // Find the statement containing this source table
                for (int i = 0; i < statements.size(); i++) {
                    TCustomSqlStatement stmt = statements.get(i);
                    containingStmt = findStatementWithTable(stmt, sourceTable);
                    if (containingStmt != null) break;
                }
            }
        }

        if (containingStmt == null || containingStmt.tables == null) {
            // Fallback: use first statement if available
            if (statements != null && statements.size() > 0) {
                TCustomSqlStatement firstStmt = statements.get(0);
                if (firstStmt != null && firstStmt.tables != null) {
                    containingStmt = firstStmt;
                }
            }
            if (containingStmt == null || containingStmt.tables == null) {
                return;
            }
        }

        // Add star column for each physical table in the FROM clause
        for (int i = 0; i < containingStmt.tables.size(); i++) {
            TTable table = containingStmt.tables.getTable(i);
            if (table == null) continue;

            // Skip non-physical table types
            if (table.getTableType() == ETableSource.subquery) continue;
            if (table.getTableType() == ETableSource.openquery) continue;
            if (table.getTableType() == ETableSource.join) continue;
            if (table.isCTEName() && !showCTE) continue;

            // Skip implicit lateral derived tables - they shouldn't have star columns
            if (table.getEffectType() == ETableEffectType.tetImplicitLateralDerivedTable) continue;

            // Skip non-physical tables if onlyPhysicalTables is true
            // Note: PIVOT/UNPIVOT and UNNEST column expansion is now handled by the core resolver
            // This is a fallback path when the core resolver hasn't expanded the star
            if (shouldSkipTableForPhysicalFilter(table)) continue;

            String tablePrefix = getFieldTablePrefix(table);
            if (tablePrefix != null) {
                // For UNNEST tables, add star if requested, then expand individual columns
                if (table.getTableType() == ETableSource.unnest) {
                    if (listStarColumn) {
                        fields.add(tablePrefix + ".*");
                    }
                    expandUnnestStarColumns(table, tablePrefix, fields);
                } else if (table.getTableType() == ETableSource.pivoted_table) {
                    // For PIVOT/UNPIVOT tables, output the generated columns from PivotNamespace
                    // These are virtual tables so we don't output table.*, only individual columns
                    if (buildResult != null) {
                        INamespace namespace = buildResult.getNamespaceForTable(table);
                        if (namespace != null) {
                            Map<String, ColumnSource> columnSources = namespace.getAllColumnSources();
                            if (columnSources != null && !columnSources.isEmpty()) {
                                for (String columnName : columnSources.keySet()) {
                                    if (columnName != null && !columnName.isEmpty()) {
                                        fields.add(tablePrefix + "." + normalizeColumnName(columnName));
                                    }
                                }
                            }
                        }
                    }
                } else {
                    // For regular tables, add star column if listStarColumn is true
                    if (listStarColumn) {
                        fields.add(tablePrefix + ".*");
                    }
                }
            }
        }
    }

    private String getFieldTablePrefix(TTable table) {
        if (table.getTableType() == ETableSource.subquery) {
            return "(subquery, alias:" + table.getAliasName() + ")";
        } else if (table.getTableType() == ETableSource.unnest) {
            String alias = table.getAliasName();
            return "(unnest-table:" + (alias != null ? alias : "") + ")";
        } else if (table.getTableType() == ETableSource.pivoted_table) {
            // Check if this is UNPIVOT or PIVOT
            boolean isUnpivot = false;
            // First try to get from TPivotClause
            if (table.getPivotedTable() != null && table.getPivotedTable().getPivotClause() != null) {
                isUnpivot = (table.getPivotedTable().getPivotClause().getType() == TPivotClause.unpivot);
            } else {
                // Fallback: check if the table alias suggests UNPIVOT
                // When no explicit alias is provided, TPivotClause.doParse() uses "unpivot_alias"
                String tableName = table.getTableName() != null ? table.getTableName().toString() : "";
                String aliasName = table.getAliasName() != null ? table.getAliasName() : "";
                isUnpivot = tableName.startsWith("unpivot_") || aliasName.startsWith("unpivot_");
            }
            String prefix = isUnpivot ? "(unpivot-table:" : "(pivot-table:";
            return prefix + normalizeTableName(table.getTableName()) + ")";
        } else if (table.getTableType() == ETableSource.function) {
            return "(table-valued function:" + normalizeTableName(table.getTableName()) + ")";
        } else if (table.getTableType() == ETableSource.lateralView) {
            return "(lateral_view:" + normalizeTableName(table.getTableName()) + ")";
        } else if (table.isCTEName() && showCTE) {
            String tableName = table.getTableName() != null ? normalizeTableName(table.getTableName()) : table.getName();
            return tableName + "(CTE)";
        } else {
            return table.getTableName() != null ? normalizeTableName(table.getTableName()) : table.getName();
        }
    }

    /**
     * Normalize a table name using the configured name matcher.
     *
     * <p>When a VendorNameMatcher is configured, this delegates to the vendor-specific
     * qualified name normalization (handles case folding and multi-part identifiers).
     * Falls back to manual parsing and quote stripping if no matcher is configured.</p>
     *
     * @param tableName The TObjectName representing the table name
     * @return The normalized table name
     */
    private String normalizeTableName(TObjectName tableName) {
        if (tableName == null) return "";
        String fullName = tableName.toString();
        if (fullName == null || fullName.isEmpty()) return "";

        // Use DisplayNameNormalizer for all modes - it handles DISPLAY, SQL_RENDER, CANONICAL correctly
        if (displayNameNormalizer != null) {
            return displayNameNormalizer.normalizeQualifiedName(fullName);
        }

        // Fallback when no normalizer is available
        // Use VendorNameMatcher if available for vendor-specific normalization (includes case folding)
        if (nameMatcher instanceof VendorNameMatcher) {
            return ((VendorNameMatcher) nameMatcher).normalizeQualifiedName(fullName, ESQLDataObjectType.dotTable);
        }

        // Fallback: manual parsing with quote stripping
        return normalizeTableNameFallback(fullName);
    }

    /**
     * Fallback table name normalization when no vendor matcher is configured.
     * Handles both fully-quoted identifiers and multi-part identifiers.
     */
    private String normalizeTableNameFallback(String fullName) {
        // First check if there are any dots outside of quotes (multi-part identifier)
        boolean hasUnquotedDot = false;
        char quoteChar = 0;
        for (int i = 0; i < fullName.length(); i++) {
            char c = fullName.charAt(i);
            if (quoteChar == 0) {
                if (c == '`' || c == '"' || c == '[') {
                    quoteChar = (c == '[') ? ']' : c;
                } else if (c == '.') {
                    hasUnquotedDot = true;
                    break;
                }
            } else {
                if (c == quoteChar) {
                    quoteChar = 0;
                }
            }
        }

        // If no unquoted dots, check if entire string is quoted
        if (!hasUnquotedDot) {
            return stripQuotes(fullName);
        }

        // Otherwise, split by dot to handle multi-part identifiers like `schema`.`table`
        // But we need to be careful not to split inside quotes
        StringBuilder result = new StringBuilder();
        StringBuilder currentPart = new StringBuilder();
        quoteChar = 0;

        for (int i = 0; i < fullName.length(); i++) {
            char c = fullName.charAt(i);

            if (quoteChar == 0) {
                // Not inside a quote
                if (c == '`' || c == '"' || c == '[') {
                    quoteChar = (c == '[') ? ']' : c;
                    currentPart.append(c);
                } else if (c == '.') {
                    // End of a part
                    if (result.length() > 0) result.append(".");
                    result.append(stripQuotes(currentPart.toString()));
                    currentPart.setLength(0);
                } else {
                    currentPart.append(c);
                }
            } else {
                // Inside a quote
                currentPart.append(c);
                if (c == quoteChar) {
                    quoteChar = 0;
                }
            }
        }

        // Don't forget the last part
        if (currentPart.length() > 0) {
            if (result.length() > 0) result.append(".");
            result.append(stripQuotes(currentPart.toString()));
        }

        return result.toString();
    }

    private void collectConstraintColumns(TCreateTableSqlStatement createStmt, Set<String> fields) {
        if (createStmt == null || createStmt.getTargetTable() == null) return;

        String tableName = createStmt.getTargetTable().getTableName() != null
                ? normalizeTableName(createStmt.getTargetTable().getTableName()) : "";

        // Collect columns from table-level constraints
        if (createStmt.getTableConstraints() != null) {
            for (int i = 0; i < createStmt.getTableConstraints().size(); i++) {
                TConstraint constraint = createStmt.getTableConstraints().getConstraint(i);
                collectColumnsFromConstraint(constraint, tableName, fields);
            }
        }

        // Collect columns from column-level constraints (inline constraints)
        if (createStmt.getColumnList() != null) {
            for (int i = 0; i < createStmt.getColumnList().size(); i++) {
                TColumnDefinition colDef = createStmt.getColumnList().getColumn(i);
                if (colDef != null && colDef.getConstraints() != null) {
                    for (int j = 0; j < colDef.getConstraints().size(); j++) {
                        TConstraint constraint = colDef.getConstraints().getConstraint(j);
                        collectColumnsFromConstraint(constraint, tableName, fields);
                    }
                }
            }
        }
    }

    private void collectAlterTableConstraintColumns(TAlterTableStatement alterStmt, Set<String> fields) {
        if (alterStmt == null) return;

        // Get table name from getTableName() first, fall back to getTargetTable()
        String tableName = "";
        if (alterStmt.getTableName() != null) {
            tableName = normalizeTableName(alterStmt.getTableName());
        } else if (alterStmt.getTargetTable() != null && alterStmt.getTargetTable().getTableName() != null) {
            tableName = normalizeTableName(alterStmt.getTargetTable().getTableName());
        }
        if (tableName.isEmpty()) return;

        // Collect from ALTER TABLE options (ADD CONSTRAINT, etc.)
        if (alterStmt.getAlterTableOptionList() != null) {
            for (int i = 0; i < alterStmt.getAlterTableOptionList().size(); i++) {
                TAlterTableOption option = alterStmt.getAlterTableOptionList().getAlterTableOption(i);
                if (option == null) continue;

                // Handle constraints
                if (option.getConstraintList() != null) {
                    for (int j = 0; j < option.getConstraintList().size(); j++) {
                        TConstraint constraint = option.getConstraintList().getConstraint(j);
                        collectColumnsFromConstraint(constraint, tableName, fields);
                    }
                }

                // Handle index columns (for AddConstraintPK, AddConstraintUnique, AddConstraintFK)
                // These store columns as TPTNodeList<TColumnWithSortOrder>
                if (option.getIndexCols() != null) {
                    for (int j = 0; j < option.getIndexCols().size(); j++) {
                        TColumnWithSortOrder colWithSort = option.getIndexCols().getElement(j);
                        if (colWithSort != null && colWithSort.getColumnName() != null) {
                            String colName = normalizeColumnName(colWithSort.getColumnName().getColumnNameOnly());
                            if (colName != null && !colName.isEmpty()) {
                                fields.add(tableName + "." + colName);
                            }
                        }
                    }
                }

                // Note: We do NOT collect columns from ALTER COLUMN, DROP COLUMN, RENAME COLUMN,
                // CHANGE COLUMN operations. These are DDL targets (columns being modified),
                // not column references. The old resolver does not collect these either.

                // Handle FOREIGN KEY references (for ADD CONSTRAINT FK)
                if (option.getReferencedObjectName() != null && option.getReferencedColumnList() != null) {
                    String refTableName = normalizeTableName(option.getReferencedObjectName());
                    for (int j = 0; j < option.getReferencedColumnList().size(); j++) {
                        TObjectName colObj = option.getReferencedColumnList().getObjectName(j);
                        if (colObj != null) {
                            String colName = normalizeColumnName(colObj.getColumnNameOnly());
                            if (colName != null && !colName.isEmpty()) {
                                fields.add(refTableName + "." + colName);
                            }
                        }
                    }
                }
            }
        }
    }

    private void collectColumnsFromConstraint(TConstraint constraint, String tableName, Set<String> fields) {
        if (constraint == null) return;

        // Collect columns from constraint's column list (TPTNodeList<TColumnWithSortOrder>)
        if (constraint.getColumnList() != null) {
            for (int i = 0; i < constraint.getColumnList().size(); i++) {
                TColumnWithSortOrder colWithSort = constraint.getColumnList().getElement(i);
                if (colWithSort != null && colWithSort.getColumnName() != null) {
                    String columnName = normalizeColumnName(colWithSort.getColumnName().getColumnNameOnly());
                    if (columnName != null && !columnName.isEmpty()) {
                        // Constraint columns don't include datatype
                        // Get location from the column if available
                        String locationStr = "";
                        if (showColumnLocation && colWithSort.getColumnName().getLocation() != null) {
                            locationStr = "(" + colWithSort.getColumnName().getLocation() + ")";
                        }
                        fields.add(tableName + "." + columnName + locationStr);
                    }
                }
            }
        }

        // Collect columns from FOREIGN KEY REFERENCES
        if (constraint.getReferencedColumnList() != null && constraint.getReferencedObject() != null) {
            String refTableName = normalizeTableName(constraint.getReferencedObject());
            for (int i = 0; i < constraint.getReferencedColumnList().size(); i++) {
                TObjectName colName = constraint.getReferencedColumnList().getObjectName(i);
                if (colName != null) {
                    String columnName = normalizeColumnName(colName.getColumnNameOnly());
                    if (columnName != null && !columnName.isEmpty()) {
                        // Use location already set during parsing
                        String locationStr = "";
                        if (showColumnLocation && colName.getLocation() != null) {
                            locationStr = "(" + colName.getLocation() + ")";
                        }
                        fields.add(refTableName + "." + columnName + locationStr);
                    }
                }
            }
        }
    }

    private void expandUnnestStarColumns(TTable unnestTable, String tablePrefix, Set<String> fields) {
        if (unnestTable == null || unnestTable.getTableType() != ETableSource.unnest) {
            return;
        }

        // Get the implicit column name (alias or 'value')
        String alias = unnestTable.getAliasName();
        String implicitColumnName = (alias != null && !alias.isEmpty()) ? alias : "value";
        fields.add(tablePrefix + "." + implicitColumnName);

        // Check for WITH OFFSET column
        TUnnestClause unnestClause = unnestTable.getUnnestClause();
        if (unnestClause != null && unnestClause.getWithOffset() != null) {
            String offsetColumnName;
            if (unnestClause.getWithOffsetAlais() != null &&
                unnestClause.getWithOffsetAlais().getAliasName() != null) {
                offsetColumnName = unnestClause.getWithOffsetAlais().getAliasName().toString();
            } else {
                offsetColumnName = "offset";
            }
            fields.add(tablePrefix + "." + offsetColumnName);
        }
    }

    /**
     * Normalize a column name using the configured name matcher.
     *
     * <p>When a VendorNameMatcher is configured, this delegates to the vendor-specific
     * normalization logic (handles case folding according to vendor rules).
     * Falls back to simple quote stripping if no matcher is configured.</p>
     *
     * @param columnName The raw column name which may include quotes
     * @return The normalized column name
     */
    private String normalizeColumnName(String columnName) {
        if (columnName == null || columnName.isEmpty()) return columnName;

        // Use DisplayNameNormalizer for all modes - it handles DISPLAY, SQL_RENDER, CANONICAL correctly
        if (displayNameNormalizer != null) {
            return displayNameNormalizer.normalizeIdentifier(columnName);
        }

        // Fallback when no normalizer is available
        // Use VendorNameMatcher if available for vendor-specific normalization (includes case folding)
        if (nameMatcher instanceof VendorNameMatcher) {
            return ((VendorNameMatcher) nameMatcher).normalize(columnName, ESQLDataObjectType.dotColumn);
        }

        // Use generic INameMatcher if available
        if (nameMatcher != null) {
            return nameMatcher.normalize(columnName);
        }

        // Fallback: simple quote stripping
        return stripQuotes(columnName);
    }

    /**
     * Strip surrounding quotes from an identifier.
     * This is a fallback when no name matcher is configured.
     *
     * @param name The identifier which may include quotes
     * @return The identifier without surrounding quotes
     */
    private String stripQuotes(String name) {
        if (name == null || name.isEmpty()) return name;

        // Remove surrounding double quotes
        if (name.startsWith("\"") && name.endsWith("\"") && name.length() > 1) {
            return name.substring(1, name.length() - 1);
        }
        // Remove surrounding backticks (MySQL)
        if (name.startsWith("`") && name.endsWith("`") && name.length() > 1) {
            return name.substring(1, name.length() - 1);
        }
        // Remove surrounding square brackets (SQL Server)
        if (name.startsWith("[") && name.endsWith("]") && name.length() > 1) {
            return name.substring(1, name.length() - 1);
        }
        return name;
    }

    private String getColumnDatatype(TObjectName col) {
        if (!showDatatype) return "";

        if (col.getLinkedColumnDef() != null) {
            TTypeName datatype = col.getLinkedColumnDef().getDatatype();
            if (datatype != null) {
                String typeName = datatype.getDataTypeName();
                if (typeName != null && !typeName.isEmpty()) {
                    StringBuilder result = new StringBuilder(":" + typeName.toLowerCase());
                    // Add length if available (e.g., varchar:30, char:50)
                    if (datatype.getLength() != null) {
                        result.append(":").append(datatype.getLength().toString());
                    }
                    // Add precision if available (e.g., decimal:10)
                    else if (datatype.getPrecision() != null) {
                        result.append(":").append(datatype.getPrecision().toString());
                        // Add scale if available (e.g., decimal:10:2)
                        if (datatype.getScale() != null) {
                            result.append(":").append(datatype.getScale().toString());
                        }
                    }
                    // Add display length if available (e.g., int:11 for MySQL INT(11))
                    else if (datatype.getDisplayLength() != null) {
                        result.append(":").append(datatype.getDisplayLength().toString());
                    }
                    return result.toString();
                }
            }
        }
        return "";
    }

    // ========== CTE Column Collection ==========

    /**
     * Collect CTE columns for the Ctes: output section.
     * Prioritizes CTENamespace from resolver when available, falls back to AST traversal.
     */
    private void collectCTEColumns(Set<String> cteColumns) {
        if (buildResult == null) return;

        // Get CTE namespaces from resolver
        Map<String, CTENamespace> cteNamespaces = buildResult.getCTENamespaces();

        if (cteNamespaces != null && !cteNamespaces.isEmpty()) {
            // Preferred: Use CTENamespace to get columns
            for (Map.Entry<String, CTENamespace> entry : cteNamespaces.entrySet()) {
                String cteName = entry.getKey();
                CTENamespace ns = entry.getValue();

                Map<String, ColumnSource> columnSources = ns.getAllColumnSources();
                if (columnSources != null) {
                    for (String colName : columnSources.keySet()) {
                        cteColumns.add(cteName + "." + colName);
                    }
                }
            }
        } else {
            // Fallback: Collect from AST when namespaces not available
            for (int i = 0; i < statements.size(); i++) {
                collectCTEColumnsFromAST(statements.get(i), cteColumns);
            }
        }
    }

    /**
     * Fallback method to collect CTE columns from AST when CTENamespace is not available.
     */
    private void collectCTEColumnsFromAST(Object stmt, Set<String> cteColumns) {
        if (!(stmt instanceof TSelectSqlStatement)) {
            if (stmt instanceof TCustomSqlStatement) {
                TCustomSqlStatement customStmt = (TCustomSqlStatement) stmt;
                for (int i = 0; i < customStmt.getStatements().size(); i++) {
                    collectCTEColumnsFromAST(customStmt.getStatements().get(i), cteColumns);
                }
            }
            return;
        }

        TSelectSqlStatement selectStmt = (TSelectSqlStatement) stmt;

        // Handle combined queries - follow left chain iteratively
        if (selectStmt.isCombinedQuery()) {
            TSelectSqlStatement current = selectStmt;
            while (current.isCombinedQuery()) {
                collectCTEColumnsFromAST(current.getRightStmt(), cteColumns);
                current = current.getLeftStmt();
            }
            collectCTEColumnsFromAST(current, cteColumns);
            return;
        }

        // Process CTEs in this statement
        if (selectStmt.getCteList() != null) {
            for (int i = 0; i < selectStmt.getCteList().size(); i++) {
                TCTE cte = selectStmt.getCteList().getCTE(i);
                String cteName = cte.getTableName().toString();

                // Get columns from CTE definition
                if (cte.getColumnList() != null) {
                    for (int j = 0; j < cte.getColumnList().size(); j++) {
                        TObjectName colName = cte.getColumnList().getObjectName(j);
                        if (colName != null) {
                            cteColumns.add(cteName + "." + colName.getColumnNameOnly());
                        }
                    }
                } else if (cte.getSubquery() != null) {
                    // Infer columns from subquery result columns
                    TResultColumnList resultCols = cte.getSubquery().getResultColumnList();
                    if (resultCols != null) {
                        for (int j = 0; j < resultCols.size(); j++) {
                            TResultColumn rc = resultCols.getResultColumn(j);
                            String colName = inferColumnName(rc);
                            if (colName != null && !"*".equals(colName)) {
                                cteColumns.add(cteName + "." + colName);
                            }
                        }
                    }
                }
            }
        }

        // Recursively process nested statements
        for (int i = 0; i < selectStmt.getStatements().size(); i++) {
            collectCTEColumnsFromAST(selectStmt.getStatements().get(i), cteColumns);
        }
    }

    private String inferColumnName(TResultColumn rc) {
        if (rc == null) return null;

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

        // Check for simple column reference
        if (rc.getExpr() != null && rc.getExpr().getObjectOperand() != null) {
            return rc.getExpr().getObjectOperand().getColumnNameOnly();
        }

        return null;
    }

    // ========== Static Factory Methods ==========

    /**
     * Create a formatter with default settings.
     */
    public static TSQLResolver2ResultFormatter create(TSQLResolver2 resolver) {
        return new TSQLResolver2ResultFormatter(resolver);
    }

    /**
     * Create a formatter configured to show CTE tables and columns.
     */
    public static TSQLResolver2ResultFormatter createWithCTE(TSQLResolver2 resolver) {
        return new TSQLResolver2ResultFormatter(resolver).setShowCTE(true);
    }

    /**
     * Find the containing SQL statement for a column reference by traversing up the AST.
     * This is used as a fallback when ownStmt is not set (e.g., for PL/SQL package constants).
     *
     * @param col The column reference
     * @return The containing statement, or null if not found
     */
    private TCustomSqlStatement findContainingStatement(TObjectName col) {
        if (col == null) return null;

        TParseTreeNode parent = col.getParentObjectName();
        while (parent != null) {
            if (parent instanceof TCustomSqlStatement) {
                return (TCustomSqlStatement) parent;
            }
            parent = parent.getParentObjectName();
        }
        return null;
    }

    /**
     * Get the position string for a column reference, using the actual column name part's position
     * for multi-part identifiers (e.g., for "sch.pkg.col", use the position of "col" not "sch").
     * This matches TGetTableColumn behavior.
     *
     * @param col The column reference
     * @return Position string in format "(line,column)"
     */
    private String getColumnPositionStr(TObjectName col) {
        if (col == null) {
            return "(0,0)";
        }
        // For multi-part names, use the partToken (column name part) position if available
        TSourceToken partToken = col.getPartToken();
        if (partToken != null) {
            return "(" + partToken.lineNo + "," + partToken.columnNo + ")";
        }
        // Fallback to the object name's position
        return "(" + col.getLineNo() + "," + col.getColumnNo() + ")";
    }

    /**
     * Create a formatter configured to show datatypes.
     */
    public static TSQLResolver2ResultFormatter createWithDatatype(TSQLResolver2 resolver) {
        return new TSQLResolver2ResultFormatter(resolver).setShowDatatype(true);
    }

    /**
     * Create a formatter configured to show CTE column definitions.
     */
    public static TSQLResolver2ResultFormatter createWithCTEColumns(TSQLResolver2 resolver) {
        return new TSQLResolver2ResultFormatter(resolver).setShowColumnsOfCTE(true);
    }

    /**
     * Read the expected output from a file.
     * Utility method for tests to load expected results.
     */
    public static String getDesiredTablesColumns(String filePath) {
        try {
            return new String(java.nio.file.Files.readAllBytes(
                java.nio.file.Paths.get(filePath)), java.nio.charset.StandardCharsets.UTF_8);
        } catch (java.io.IOException e) {
            return "";
        }
    }
}