package gudusoft.gsqlparser.resolver2.namespace;

import gudusoft.gsqlparser.nodes.TObjectName;
import gudusoft.gsqlparser.nodes.TObjectNameList;
import gudusoft.gsqlparser.nodes.TResultColumn;
import gudusoft.gsqlparser.nodes.TResultColumnList;
import gudusoft.gsqlparser.nodes.TTable;
import gudusoft.gsqlparser.nodes.TPivotClause;
import gudusoft.gsqlparser.nodes.TPivotInClause;
import gudusoft.gsqlparser.nodes.TUnpivotInClause;
import gudusoft.gsqlparser.nodes.TUnpivotInClauseItem;
import gudusoft.gsqlparser.nodes.TExpression;
import gudusoft.gsqlparser.nodes.TExpressionList;
import gudusoft.gsqlparser.nodes.TFunctionCall;
import gudusoft.gsqlparser.nodes.TConstant;
import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.EExpressionType;
import gudusoft.gsqlparser.resolver2.ColumnLevel;
import gudusoft.gsqlparser.resolver2.matcher.INameMatcher;
import gudusoft.gsqlparser.resolver2.model.ColumnSource;

import java.util.*;

/**
 * Namespace representing a PIVOT table.
 *
 * <p>A PIVOT transforms rows into columns using an aggregate function:
 * <pre>
 * SELECT ...
 * FROM source_table
 * PIVOT (aggregate_function(value_column) FOR pivot_column IN (val1, val2, ...)) AS alias
 * </pre>
 *
 * <p>The PIVOT produces:
 * <ul>
 *   <li>Columns from the IN clause (val1, val2, ...) - these are the pivoted columns</li>
 *   <li>Pass-through columns from the source table (all columns NOT used in aggregate or FOR clause)</li>
 * </ul>
 *
 * <p>For column resolution purposes:
 * <ul>
 *   <li>Pivot columns (from IN clause) resolve to the PivotNamespace</li>
 *   <li>Other columns may come from the source table (pass-through columns)</li>
 * </ul>
 */
public class PivotNamespace extends AbstractNamespace {

    /** The pivot table this namespace represents */
    private final TTable pivotTable;

    /** The source table that the PIVOT operates on */
    private final TTable sourceTable;

    /** The PIVOT clause */
    private final TPivotClause pivotClause;

    /** Alias for this pivot table (from AS clause) */
    private final String alias;

    /** Namespace for the source table (for pass-through column resolution) */
    private INamespace sourceNamespace;

    /** Columns defined by the PIVOT IN clause */
    private final Set<String> pivotColumns = new LinkedHashSet<>();

    /** Columns consumed by UNPIVOT IN (...) list (these do NOT exist as output columns) */
    private final Set<String> unpivotInColumns = new LinkedHashSet<>();

    /** Display name format for PIVOT table */
    public static final String PIVOT_TABLE_SUFFIX = "(piviot_table)";

    /** Display name format for UNPIVOT table */
    public static final String UNPIVOT_TABLE_SUFFIX = "(unpivot_table)";

    /** Whether this is an UNPIVOT clause */
    private boolean isUnpivot = false;

    public PivotNamespace(TTable pivotTable, TPivotClause pivotClause,
                          TTable sourceTable, String alias, INameMatcher nameMatcher) {
        super(pivotTable, nameMatcher);
        this.pivotTable = pivotTable;
        this.pivotClause = pivotClause;
        this.sourceTable = sourceTable;
        this.alias = alias;
        // Check if this is an UNPIVOT clause
        if (pivotClause != null) {
            this.isUnpivot = (pivotClause.getType() == TPivotClause.unpivot);
        }
    }

    /**
     * Check if this is an UNPIVOT namespace.
     */
    public boolean isUnpivot() {
        return isUnpivot;
    }

    /**
     * Set the namespace representing the source table.
     * This is used for resolving pass-through columns.
     */
    public void setSourceNamespace(INamespace sourceNamespace) {
        this.sourceNamespace = sourceNamespace;
    }

    /**
     * Get the namespace representing the source table.
     */
    public INamespace getSourceNamespace() {
        return sourceNamespace;
    }

    /**
     * Get the pivot table.
     */
    public TTable getPivotTable() {
        return pivotTable;
    }

    /**
     * Get the underlying table that PIVOT operates on.
     * This is the table whose columns are being pivoted.
     */
    public TTable getUnderlyingSourceTable() {
        return sourceTable;
    }

    /**
     * {@inheritDoc}
     * For PivotNamespace, returns the PIVOT table (the result of the PIVOT operation).
     * This is the immediate source table for columns resolved through this PIVOT.
     */
    @Override
    public TTable getSourceTable() {
        return pivotTable;
    }

    /**
     * Get the PIVOT clause.
     */
    public TPivotClause getPivotClause() {
        return pivotClause;
    }

    /**
     * Get the set of pivot column names (from IN clause).
     */
    public Set<String> getPivotColumns() {
        return Collections.unmodifiableSet(pivotColumns);
    }

    /**
     * Check if a column is a pivot column (from IN clause).
     */
    public boolean isPivotColumn(String columnName) {
        if (columnName == null) return false;
        for (String pivotCol : pivotColumns) {
            if (nameMatcher.matches(lookupSpelling(pivotCol), columnName)) {
                return true;
            }
        }
        return false;
    }

    @Override
    public String getDisplayName() {
        String suffix = isUnpivot ? UNPIVOT_TABLE_SUFFIX : PIVOT_TABLE_SUFFIX;
        if (alias != null && !alias.isEmpty()) {
            return alias + suffix;
        }
        return (isUnpivot ? "unpivot_alias" : "pivot_alias") + suffix;
    }

    @Override
    public TTable getFinalTable() {
        // Return the pivot table itself so that pivot columns are properly attributed
        // to the pivot table (e.g., "(pivot-table:p(piviot_table)).[1]")
        // For lineage tracing to source columns, use getAllFinalTables()
        return pivotTable;
    }

    @Override
    public List<TTable> getAllFinalTables() {
        // Return source table for lineage tracing
        if (sourceTable != null) {
            List<TTable> tables = new ArrayList<>();
            tables.add(sourceTable);
            return tables;
        }
        return Collections.emptyList();
    }

    @Override
    protected void doValidate() {
        columnSources = new LinkedHashMap<>();
        pivotColumns.clear();
        unpivotInColumns.clear();

        if (pivotClause == null) {
            return;
        }

        lookupSpellings.clear();
        for (String[] generated : generatedColumns(pivotClause)) {
            String columnName = generated[0];
            if (columnName == null || columnName.isEmpty()) {
                continue;
            }
            // a name stored as written (Oracle's 'direct') is looked up by its delimited spelling ("'direct'")
            String lookup = generated[2] != null ? encodeStored(columnName) : columnName;
            boolean known = false;
            for (String existing : columnSources.keySet()) {
                known |= nameMatcher.matches(lookupSpelling(existing), lookup);
            }
            if (!known) {
                if (generated[2] != null) {
                    lookupSpellings.put(columnName, lookup);
                }
                addPivotColumn(columnName, generated[1]);
            }
        }

        if (isUnpivot) {
            // UNPIVOT case: Collect consumed IN(...) source columns so they are NOT treated as pass-through
            // Example: UNPIVOT (col3 FOR col4 IN (p.col2, p.col3))
            // - p.col2, p.col3 are consumed source columns and should not be visible via unpivot alias
            TUnpivotInClause unpivotInClause = pivotClause.getUnpivotInClause();
            if (unpivotInClause != null && unpivotInClause.getItems() != null) {
                for (int i = 0; i < unpivotInClause.getItems().size(); i++) {
                    TUnpivotInClauseItem item = unpivotInClause.getItems().getElement(i);
                    if (item == null) continue;

                    if (item.getColumn() != null) {
                        String name = item.getColumn().getColumnNameOnly();
                        if (name != null && !name.isEmpty()) {
                            unpivotInColumns.add(name);
                        }
                    }
                    if (item.getColumnList() != null) {
                        TObjectNameList cols = item.getColumnList();
                        for (int j = 0; j < cols.size(); j++) {
                            TObjectName col = cols.getObjectName(j);
                            if (col != null) {
                                String name = col.getColumnNameOnly();
                                if (name != null && !name.isEmpty()) {
                                    unpivotInColumns.add(name);
                                }
                            }
                        }
                    }
                }
            }
        }
    }

    /**
     * The columns a PIVOT or UNPIVOT operator generates, in text order, each as {name, evidence, stored}, duplicates
     * included (this namespace keeps the first of names its matcher equates). {@code stored} is non-null when the
     * name is the column's stored name as written, an IN value of a vendor that names the column after the literal
     * ({@link #inListValueColumnName}): it is looked up by its delimited spelling, never folded as an unquoted
     * identifier ({@code IN ('direct')} is referenced as {@code "'direct'"}, not as {@code "'DIRECT'"}).
     *
     * <p>UNPIVOT: its value columns, then its FOR columns. PIVOT: the names of an alias column list ({@code AS p
     * (a, b)}), which replace the generated names, else one name per IN item ({@link #inListItemAlias}, else the
     * value: {@link #inListValueColumnName}), the columns of an IN subquery, the values of an IN value list.</p>
     *
     * <p>The one rule for the names a pivot generates: this namespace resolves by it, and the SQL-LR producer
     * publishes by it ({@code TPivotClause.getGeneratedColumnNames()}, plan 09 slice 10), so a name is never
     * published under one spelling and looked up under another.</p>
     */
    public static List<String[]> generatedColumns(TPivotClause pivotClause) {
        List<String[]> out = new ArrayList<>();
        if (pivotClause == null) {
            return out;
        }
        if (pivotClause.getType() == TPivotClause.unpivot) {
            // UNPIVOT (yearly_total FOR order_mode IN (store AS 'direct', internet AS 'online'))
            // - yearly_total is the value column (new column containing values)
            // - order_mode is the FOR column (new column containing labels)
            // Note: For single value column UNPIVOT (like Oracle), use getValueColumn() (deprecated singular)
            // For multi-value column UNPIVOT (like SQL Server), use getValueColumnList()
            TObjectNameList valueColumns = pivotClause.getValueColumnList();
            if (valueColumns != null && valueColumns.size() > 0) {
                for (int i = 0; i < valueColumns.size(); i++) {
                    TObjectName valueCol = valueColumns.getObjectName(i);
                    if (valueCol != null) {
                        out.add(new String[] {valueCol.getColumnNameOnly(), "unpivot_value_column", null});
                    }
                }
            } else {
                // Fallback to deprecated singular method for Oracle compatibility
                @SuppressWarnings("deprecation")
                TObjectName valueCol = pivotClause.getValueColumn();
                if (valueCol != null) {
                    out.add(new String[] {valueCol.getColumnNameOnly(), "unpivot_value_column", null});
                }
            }
            TObjectNameList pivotColumnList = pivotClause.getPivotColumnList();
            if (pivotColumnList != null && pivotColumnList.size() > 0) {
                for (int i = 0; i < pivotColumnList.size(); i++) {
                    TObjectName forCol = pivotColumnList.getObjectName(i);
                    if (forCol != null) {
                        out.add(new String[] {forCol.getColumnNameOnly(), "unpivot_for_column", null});
                    }
                }
            } else {
                @SuppressWarnings("deprecation")
                TObjectName forCol = pivotClause.getPivotColumn();
                if (forCol != null) {
                    out.add(new String[] {forCol.getColumnNameOnly(), "unpivot_for_column", null});
                }
            }
            return out;
        }
        // PIVOT: if the alias clause has a column list (e.g., AS p (col1, col2, col3)), its names REPLACE the
        // generated ones (e.g., PIVOT(...) AS p (empid_renamed, Q1, Q2, Q3, Q4))
        if (pivotClause.getAliasClause() != null && pivotClause.getAliasClause().getColumns() != null
                && pivotClause.getAliasClause().getColumns().size() > 0) {
            for (TObjectName column : pivotClause.getAliasClause().getColumns()) {
                // verbatim, quote state included, as inListItemAlias: p ("MiX") names MiX, not MIX (plan 09 slice 10)
                out.add(new String[] {column.toString(), "pivot_alias_clause", null});
            }
            return out;
        }
        TPivotInClause inClause = pivotClause.getPivotInClause();
        if (inClause == null) {
            return out;
        }
        // Case 1: IN clause has items (e.g., IN ([1], [2], [3]) or IN ([Sammich], [Pickle]))
        if (inClause.getItems() != null) {
            TResultColumnList items = inClause.getItems();
            for (int i = 0; i < items.size(); i++) {
                TResultColumn item = items.getResultColumn(i);
                out.add(new String[] {inListItemColumnName(item), "pivot_in_clause",
                    inListItemStored(item) ? "stored" : null});
            }
        }
        // Case 2: IN clause has a subquery (e.g., IN (SELECT DISTINCT col FROM table))
        if (inClause.getSubQuery() != null) {
            TResultColumnList subqueryColumns = inClause.getSubQuery().getResultColumnList();
            if (subqueryColumns != null) {
                for (int i = 0; i < subqueryColumns.size(); i++) {
                    out.add(new String[] {stripDelimitersFrom(subqueryColumns.getResultColumn(i).getDisplayName()),
                            "pivot_in_subquery", null});
                }
            }
        }
        // Case 3: IN clause has value list (e.g., IN ((val1, val2), (val3, val4)))
        if (inClause.getValueList() != null) {
            for (TExpressionList exprList : inClause.getValueList()) {
                if (exprList != null) {
                    for (int i = 0; i < exprList.size(); i++) {
                        out.add(new String[] {extractColumnNameFromExpression(exprList.getExpression(i)),
                                "pivot_in_valuelist", null});
                    }
                }
            }
        }
        return out;
    }

    /** The names of {@link #generatedColumns}, in order, duplicates included. */
    public static List<String> generatedColumnNames(TPivotClause pivotClause) {
        List<String> names = new ArrayList<>();
        for (String[] generated : generatedColumns(pivotClause)) {
            names.add(generated[0]);
        }
        return names;
    }

    /** Whether each name of {@link #generatedColumnNames} is a stored name as written (see {@link #generatedColumns}). */
    public static List<Boolean> generatedColumnsStored(TPivotClause pivotClause) {
        List<Boolean> stored = new ArrayList<>();
        for (String[] generated : generatedColumns(pivotClause)) {
            stored.add(Boolean.valueOf(generated[2] != null));
        }
        return stored;
    }

    /**
     * The name a PIVOT IN-list item gives its generated column: its alias ({@link #inListItemAlias}), else the name
     * its value gives ({@link #inListValueColumnName}). The pre-resolver2 path ({@code TPivotInClause.linkColumnToTable})
     * publishes by it too.
     */
    public static String inListItemColumnName(TResultColumn resultColumn) {
        return extractPivotColumnName(resultColumn);
    }

    /** Whether the name of an IN-list item is its value's literal as written, a stored name (see generatedColumns). */
    public static boolean inListItemStored(TResultColumn resultColumn) {
        if (resultColumn == null || inListItemAlias(resultColumn) != null || resultColumn.getExpr() == null) {
            return false;
        }
        TExpression expr = resultColumn.getExpr();
        return expr.getExpressionType() == EExpressionType.simple_constant_t && expr.getConstantOperand() != null
            && expr.getConstantOperand().getValueToken() != null && keepsLiteralAsWritten(expr.dbvendor);
    }

    /**
     * The vendors that name the column of an unaliased IN value after the literal as written, quotes included:
     * Oracle and Snowflake (Mantis 4662), Teradata (its documentation: the values are enclosed in double quotes and
     * become the alias names, so {@code IN ('JAN')} is referenced as {@code "'JAN'"}).
     */
    private static boolean keepsLiteralAsWritten(EDbVendor vendor) {
        return vendor == EDbVendor.dbvsnowflake || vendor == EDbVendor.dbvoracle || vendor == EDbVendor.dbvteradata;
    }

    /** Display name -> lookup spelling of the generated names stored as written. */
    private final Map<String, String> lookupSpellings = new java.util.HashMap<>();

    /** The spelling a generated column is matched by: the delimited stored name, or the name itself. */
    private String lookupSpelling(String name) {
        String lookup = lookupSpellings.get(name);
        return lookup != null ? lookup : name;
    }

    /** The delimited spelling of a stored name, under the analysis's identifier profile. */
    private String encodeStored(String stored) {
        gudusoft.gsqlparser.sqlenv.IdentifierProfile profile = nameMatcher
            instanceof gudusoft.gsqlparser.resolver2.matcher.VendorNameMatcher
            ? ((gudusoft.gsqlparser.resolver2.matcher.VendorNameMatcher) nameMatcher).getIdentifierService()
                .getProfile()
            : gudusoft.gsqlparser.sqlenv.IdentifierProfile.forVendor(pivotClause.dbvendor,
                gudusoft.gsqlparser.sqlenv.IdentifierProfile.VendorFlags.defaults());
        return gudusoft.gsqlparser.sqlenv.IdentifierCodec.encodeStored(profile,
            gudusoft.gsqlparser.sqlenv.ESQLDataObjectType.dotColumn, stored);
    }

    /**
     * Extract the pivot column name from a result column in the IN clause.
     * Handles both column references and constant values.
     */
    private static String extractPivotColumnName(TResultColumn resultColumn) {
        if (resultColumn == null) return null;

        // An explicit alias names the generated column outright.
        String alias = inListItemAlias(resultColumn);
        if (alias != null) {
            return alias;
        }

        TExpression expr = resultColumn.getExpr();
        if (expr != null) {
            return extractColumnNameFromExpression(expr);
        }

        // Fallback to display name
        return stripDelimitersFrom(resultColumn.getDisplayName());
    }

    /**
     * The explicit alias of one PIVOT IN-list item, or null when it has none.
     *
     * <p>{@code IN ('direct' AS Store)} names the generated column {@code Store}.
     * Oracle, Snowflake, BigQuery and Teradata all accept this form and in every one
     * of them the alias — not the value — is the output column name, so it takes
     * precedence over any value-derived naming.</p>
     *
     * <p>Shared with {@code ScopeBuilder.addPivotInClauseColumns} and matched by
     * {@code TPivotInClause.linkColumnToTable} on purpose. The name a pivot column
     * is <em>published</em> under and the name it can be <em>resolved</em> by have
     * to be produced by the same rule, or a reference to the real column reports
     * NOT_FOUND while a stale name still matches.</p>
     *
     * <p>The alias is returned <em>verbatim</em>, quote state included. Stripping it
     * would be wrong in both directions for {@code IN ('x' AS "Store")}: bare
     * {@code SELECT Store} would become a false exact match, and the only valid
     * reference, {@code SELECT "Store"}, would report NOT_FOUND. Retaining quote
     * state is what the identifier normalization guide requires; comparison is the
     * matcher's job, not this method's.</p>
     */
    public static String inListItemAlias(TResultColumn resultColumn) {
        if (resultColumn == null
            || resultColumn.getAliasClause() == null
            || resultColumn.getAliasClause().getAliasName() == null) {
            return null;
        }
        String alias = resultColumn.getAliasClause().getAliasName().toString();
        return (alias == null || alias.isEmpty()) ? null : alias;
    }

    /**
     * The name a PIVOT gives to the column generated by one unaliased IN-list value.
     *
     * <p>Vendor-specific, and the difference is not cosmetic. Oracle, Snowflake and
     * Teradata name the column after the literal <em>as written</em>, quotes included, so
     * {@code IN ('direct')} yields a column really named {@code 'direct'} that must
     * be referenced as {@code "'direct'"} (Mantis 4662; that name is stored as written,
     * so it is looked up by its delimited spelling: {@link #generatedColumns}). BigQuery requires its
     * IN-list values to form valid identifiers and names the column {@code direct},
     * so the quotes are still stripped there and for every other vendor.</p>
     *
     * <p>Shared with {@code ScopeBuilder} for the same reason as
     * {@link #inListItemAlias}.</p>
     */
    public static String inListValueColumnName(EDbVendor vendor, String rawValue) {
        if (keepsLiteralAsWritten(vendor)) {
            return rawValue;
        }
        return stripDelimitersFrom(rawValue);
    }

    /** Strip a leading+trailing quote pair. SQL Server brackets are part of the identity. */
    public static String stripDelimitersFrom(String name) {
        if (name == null || name.isEmpty()) {
            return name;
        }
        // Strip double quotes (BigQuery style identifier)
        if (name.startsWith("\"") && name.endsWith("\"") && name.length() > 2) {
            return name.substring(1, name.length() - 1);
        }
        // Strip single quotes (string literal)
        if (name.startsWith("'") && name.endsWith("'") && name.length() > 2) {
            return name.substring(1, name.length() - 1);
        }
        // SQL Server brackets [] are preserved as they're part of the column identity
        return name;
    }

    /**
     * Extract column name from an expression (handles constants and column references).
     */
    private static String extractColumnNameFromExpression(TExpression expr) {
        if (expr == null) return null;

        EExpressionType exprType = expr.getExpressionType();

        // Column reference (e.g., [Sammich], [Apple], "HOUSTON" in BigQuery)
        // For BigQuery, double-quoted identifiers like "HOUSTON" are parsed as simple_object_name_t
        // and we need to strip the quotes for display purposes.
        if (exprType == EExpressionType.simple_object_name_t) {
            TObjectName objName = expr.getObjectOperand();
            if (objName != null) {
                // Strip delimiters (quotes) for display - per identifier_normalization.md
                return stripDelimitersFrom(objName.toString());
            }
        }

        // Constant value (e.g., [1], [2], 'value', "HOUSTON")
        if (exprType == EExpressionType.simple_constant_t) {
            TConstant constant = expr.getConstantOperand();
            if (constant != null && constant.getValueToken() != null) {
                // Vendor-specific: Oracle/Snowflake keep the literal as written.
                return inListValueColumnName(expr.dbvendor, constant.getValueToken().toString());
            }
        }

        // Fallback: use expression string with delimiters stripped
        return stripDelimitersFrom(expr.toString());
    }

    /**
     * Add a pivot column to the namespace.
     */
    private void addPivotColumn(String columnName, String evidence) {
        pivotColumns.add(columnName);
        ColumnSource source = new ColumnSource(
            this,
            columnName,
            null,
            1.0,  // Definite - from PIVOT IN clause
            evidence
        );
        columnSources.put(columnName, source);
    }

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

        // First check if it's a pivot column
        if (isPivotColumn(columnName)) {
            return ColumnLevel.EXISTS;
        }

        // UNPIVOT: IN(...) columns are consumed and do NOT exist in the virtual table output
        if (isUnpivot && isUnpivotInColumn(columnName)) {
            return ColumnLevel.NOT_EXISTS;
        }

        // Check in columnSources
        for (String existingCol : columnSources.keySet()) {
            if (nameMatcher.matches(lookupSpelling(existingCol), columnName)) {
                return ColumnLevel.EXISTS;
            }
        }

        // For PIVOT tables without explicit column list, any column MAYBE exists
        // (pass-through columns from source table)
        return ColumnLevel.MAYBE;
    }

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

        // First try to find in pivot columns (from IN clause)
        for (Map.Entry<String, ColumnSource> entry : columnSources.entrySet()) {
            if (nameMatcher.matches(lookupSpelling(entry.getKey()), columnName)) {
                return entry.getValue();
            }
        }

        // UNPIVOT: IN(...) columns are consumed and must NOT resolve as pass-through columns
        if (isUnpivot && isUnpivotInColumn(columnName)) {
            return null;
        }

        // Delta 2: Delegate to source namespace for pass-through columns
        // Pass-through columns are all source columns EXCEPT:
        // - The FOR column (pivot_column)
        // - The aggregate input column (value_column)
        if (sourceNamespace != null) {
            // Check if this is a pass-through column (not FOR or aggregate column)
            if (!isForColumn(columnName) && !isAggregateInputColumn(columnName)) {
                ColumnSource sourceColumn = sourceNamespace.resolveColumn(columnName);
                if (sourceColumn != null) {
                    // Both PIVOT and UNPIVOT pass-through columns keep their source table attribution
                    // (e.g., SELECT year FROM ... UNPIVOT ... -> year attributed to source table)
                    // This matches the expected behavior where passthrough columns should be
                    // attributed to their original source table, not the PIVOT/UNPIVOT virtual table.
                    return sourceColumn;
                }
            }
        }

        return null;
    }

    private boolean isUnpivotInColumn(String columnName) {
        if (columnName == null || columnName.isEmpty()) return false;
        for (String consumed : unpivotInColumns) {
            if (nameMatcher.matches(consumed, columnName)) {
                return true;
            }
        }
        return false;
    }

    /**
     * Check if the column is the FOR column (pivot_column) in the PIVOT clause.
     * The FOR column is consumed by PIVOT and should not be passed through.
     */
    private boolean isForColumn(String columnName) {
        if (pivotClause == null || columnName == null) {
            return false;
        }

        // Try pivotColumnList first (modern API, used by BigQuery and others)
        TObjectNameList pivotColumns = pivotClause.getPivotColumnList();
        if (pivotColumns != null && pivotColumns.size() > 0) {
            for (int i = 0; i < pivotColumns.size(); i++) {
                TObjectName forColumn = pivotColumns.getObjectName(i);
                if (forColumn != null) {
                    String forColName = forColumn.getColumnNameOnly();
                    if (forColName != null && nameMatcher.matches(forColName, columnName)) {
                        return true;
                    }
                }
            }
        }

        // Fallback to deprecated singular method
        @SuppressWarnings("deprecation")
        TObjectName forColumn = pivotClause.getPivotColumn();
        if (forColumn != null) {
            String forColName = forColumn.getColumnNameOnly();
            if (forColName != null && nameMatcher.matches(forColName, columnName)) {
                return true;
            }
        }
        return false;
    }

    /**
     * Check if the column is the aggregate input column (value_column) in the PIVOT clause.
     * The aggregate input column is consumed by PIVOT and should not be passed through.
     */
    private boolean isAggregateInputColumn(String columnName) {
        if (pivotClause == null || columnName == null) {
            return false;
        }

        // Try to extract columns from aggregate function (SQL Server / BigQuery style)
        // e.g., PIVOT(AVG(departure_delay) FOR airline IN (...))
        TFunctionCall aggFunc = pivotClause.getAggregation_function();
        if (aggFunc != null && aggFunc.getArgs() != null) {
            for (int i = 0; i < aggFunc.getArgs().size(); i++) {
                TExpression argExpr = aggFunc.getArgs().getExpression(i);
                if (argExpr != null && argExpr.getObjectOperand() != null) {
                    TObjectName argCol = argExpr.getObjectOperand();
                    String argColName = argCol.getColumnNameOnly();
                    if (argColName != null && nameMatcher.matches(argColName, columnName)) {
                        return true;
                    }
                }
            }
        }

        // Try to extract columns from aggregate function list (Oracle style)
        TResultColumnList aggFuncList = pivotClause.getAggregation_function_list();
        if (aggFuncList != null) {
            for (int i = 0; i < aggFuncList.size(); i++) {
                TResultColumn rc = aggFuncList.getResultColumn(i);
                if (rc != null && rc.getExpr() != null) {
                    TExpression expr = rc.getExpr();
                    if (expr.getFunctionCall() != null && expr.getFunctionCall().getArgs() != null) {
                        for (int j = 0; j < expr.getFunctionCall().getArgs().size(); j++) {
                            TExpression argExpr = expr.getFunctionCall().getArgs().getExpression(j);
                            if (argExpr != null && argExpr.getObjectOperand() != null) {
                                TObjectName argCol = argExpr.getObjectOperand();
                                String argColName = argCol.getColumnNameOnly();
                                if (argColName != null && nameMatcher.matches(argColName, columnName)) {
                                    return true;
                                }
                            }
                        }
                    }
                }
            }
        }

        // Fallback: use valueColumnList (for UNPIVOT or direct value column specification)
        TObjectNameList valueColumns = pivotClause.getValueColumnList();
        if (valueColumns != null && valueColumns.size() > 0) {
            for (int i = 0; i < valueColumns.size(); i++) {
                TObjectName valueCol = valueColumns.getObjectName(i);
                if (valueCol != null) {
                    String valueColName = valueCol.getColumnNameOnly();
                    if (valueColName != null && nameMatcher.matches(valueColName, columnName)) {
                        return true;
                    }
                }
            }
        }

        // Fallback to deprecated singular method
        @SuppressWarnings("deprecation")
        TObjectName valueCol = pivotClause.getValueColumn();
        if (valueCol != null) {
            String valueColName = valueCol.getColumnNameOnly();
            if (valueColName != null && nameMatcher.matches(valueColName, columnName)) {
                return true;
            }
        }
        return false;
    }

    /**
     * Get all column sources for this PIVOT namespace.
     * This includes both pivot columns (from IN clause) and pass-through columns from the source.
     *
     * <p>For PIVOT, the output columns are:
     * <ul>
     *   <li>Pivot columns: generated from IN clause values (e.g., 'AA', 'KH', 'DL', '9E')</li>
     *   <li>Pass-through columns: source columns NOT consumed by FOR or aggregate</li>
     * </ul>
     *
     * <p>This method is critical for star column expansion: when outer query uses SELECT *
     * from a PIVOT table, we need to return ALL columns the pivot table exposes.
     */
    @Override
    public Map<String, ColumnSource> getAllColumnSources() {
        ensureValidated();

        if (columnSources == null) {
            return Collections.emptyMap();
        }

        // Start with pivot columns (from IN clause)
        Map<String, ColumnSource> allColumns = new LinkedHashMap<>(columnSources);

        // Add pass-through columns from source namespace
        // Pass-through = all source columns EXCEPT FOR column and aggregate input column
        if (sourceNamespace != null) {
            Map<String, ColumnSource> sourceColumns = sourceNamespace.getAllColumnSources();
            for (Map.Entry<String, ColumnSource> entry : sourceColumns.entrySet()) {
                String colName = entry.getKey();

                // Skip if already a pivot column (from IN clause). Slice S1:
                // matcher-aware containment so case-only-different source
                // column names don't slip past on case-insensitive vendors.
                // Both sides by their lookup spellings: a generated name stored as
                // written ('direct') is no source column 'DIRECT' (plan 09 slice 10)
                String sourceLookup = sourceNamespace instanceof TableNamespace
                    ? ((TableNamespace) sourceNamespace).getColumnLookupSpelling(colName) : colName;
                boolean generated = false;
                for (String existing : allColumns.keySet()) {
                    generated |= nameMatcher.matches(lookupSpelling(existing), sourceLookup);
                }
                if (generated) {
                    continue;
                }

                // Skip FOR column (consumed by PIVOT)
                if (isForColumn(colName)) {
                    continue;
                }

                // Skip aggregate input column (consumed by PIVOT)
                if (isAggregateInputColumn(colName)) {
                    continue;
                }

                // This is a pass-through column - add it with pivot table attribution
                ColumnSource sourceCol = entry.getValue();
                ColumnSource passthroughCol = new ColumnSource(
                    this,  // PivotNamespace
                    colName,
                    sourceCol.getDefinitionNode(),
                    sourceCol.getConfidence(),
                    "pivot_passthrough",
                    pivotTable,  // attribute to pivot table
                    null
                );
                allColumns.put(colName, passthroughCol);
            }
        }
        return Collections.unmodifiableMap(allColumns);
    }

    @Override
    public String toString() {
        return "PivotNamespace(" + getDisplayName() + ", pivotCols=" + pivotColumns.size() + ")";
    }
}