package gudusoft.gsqlparser.resolver2.namespace;

import gudusoft.gsqlparser.EExpressionType;
import gudusoft.gsqlparser.ETableSource;
import gudusoft.gsqlparser.nodes.TExpression;
import gudusoft.gsqlparser.nodes.TObjectName;
import gudusoft.gsqlparser.nodes.TTable;
import gudusoft.gsqlparser.nodes.TUnnestClause;
import gudusoft.gsqlparser.resolver2.ColumnLevel;
import gudusoft.gsqlparser.resolver2.matcher.INameMatcher;
import gudusoft.gsqlparser.resolver2.model.ColumnSource;

import java.util.ArrayList;
import java.util.Collections;
import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Set;

/**
 * Namespace representing an UNNEST table expression in BigQuery.
 *
 * UNNEST flattens an array into rows. For example:
 * - UNNEST(['a', 'b', 'c']) creates a virtual table with rows 'a', 'b', 'c'
 * - SELECT value FROM UNNEST(array_column) - 'value' is an implicit column name
 *
 * The UNNEST namespace provides:
 * 1. An implicit column for the unnested elements (named by alias or 'value')
 * 2. Support for WITH OFFSET which adds an 'offset' column
 * 3. Support for STRUCT arrays which expose struct field names
 */
public class UnnestNamespace extends AbstractNamespace {

    private final TTable unnestTable;
    private final TUnnestClause unnestClause;
    private final String alias;

    /** The implicit column name for unnested elements */
    private String implicitColumnName;

    /** WITH OFFSET column name if present */
    private String offsetColumnName;

    /** Inferred columns for dynamic resolution */
    private Set<String> inferredColumnNames;

    /** Whether this UNNEST has an explicit alias (e.g., UNNEST(...) AS alias) */
    private boolean hasExplicitAlias;

    /** Whether the UNNEST array expression is a subquery (returns a struct that can be expanded) */
    private boolean arrayExpressionIsSubquery;

    public UnnestNamespace(TTable unnestTable, String alias, INameMatcher nameMatcher) {
        super(unnestTable, nameMatcher);
        this.unnestTable = unnestTable;
        this.unnestClause = unnestTable.getUnnestClause();

        // Determine if this is a real alias (from AS clause) or just the table name fallback
        // For UNNEST tables, we should check if there's an explicit alias
        String explicitAlias = unnestTable.getAliasName();
        if (explicitAlias != null && !explicitAlias.isEmpty()) {
            // Has explicit AS alias - use it for both table alias and implicit column
            this.alias = explicitAlias;
            this.implicitColumnName = explicitAlias;
            this.hasExplicitAlias = true;
        } else {
            // No explicit alias - UNNEST without AS clause
            // The implicit column name is "value" (BigQuery default for simple arrays)
            this.alias = alias; // May be null or derived name
            this.implicitColumnName = "value";
            this.hasExplicitAlias = false;
        }

        // Check for WITH OFFSET
        if (unnestClause != null && unnestClause.getWithOffset() != null) {
            if (unnestClause.getWithOffsetAlais() != null &&
                unnestClause.getWithOffsetAlais().getAliasName() != null) {
                this.offsetColumnName = unnestClause.getWithOffsetAlais().getAliasName().toString();
            } else {
                this.offsetColumnName = "offset";
            }
        }

        // Check if the array expression is a subquery (returns a struct)
        // When UNNEST operates on a subquery that returns a struct, struct fields become accessible
        // Example: UNNEST((SELECT struct_column FROM table))
        this.arrayExpressionIsSubquery = false;
        if (unnestClause != null) {
            TExpression arrayExpr = unnestClause.getArrayExpr();
            if (arrayExpr != null && arrayExpr.getExpressionType() == EExpressionType.subquery_t) {
                this.arrayExpressionIsSubquery = true;
            }
        }
    }

    public UnnestNamespace(TTable unnestTable, String alias) {
        this(unnestTable, alias, null);
    }

    @Override
    public String getDisplayName() {
        if (alias != null && !alias.isEmpty()) {
            return alias;
        }
        return "(unnest table)";
    }

    @Override
    public TTable getFinalTable() {
        // UNNEST creates a virtual table, return the TTable representing it
        return unnestTable;
    }

    @Override
    public List<TTable> getAllFinalTables() {
        List<TTable> tables = new ArrayList<>();
        tables.add(unnestTable);
        return tables;
    }

    @Override
    protected void doValidate() {
        columnSources = new LinkedHashMap<>();

        // Check for explicit column aliases in table's alias clause
        // For Presto/Trino syntax: UNNEST(array) AS t (col1, col2)
        // The column names are in the alias clause's column list
        boolean hasExplicitColumns = false;
        if (unnestTable.getAliasClause() != null &&
            unnestTable.getAliasClause().getColumns() != null &&
            unnestTable.getAliasClause().getColumns().size() > 0) {

            hasExplicitColumns = true;
            for (int i = 0; i < unnestTable.getAliasClause().getColumns().size(); i++) {
                TObjectName colName = unnestTable.getAliasClause().getColumns().getObjectName(i);
                if (colName != null) {
                    String name = colName.toString();
                    ColumnSource source = new ColumnSource(
                        this,
                        name,
                        null,
                        1.0,
                        "unnest_explicit_column_alias"
                    );
                    columnSources.put(name, source);
                }
            }
        }

        // Add the implicit column for unnested elements (only if no explicit columns)
        if (!hasExplicitColumns && implicitColumnName != null) {
            ColumnSource implicitSource = new ColumnSource(
                this,
                implicitColumnName,
                null,
                1.0,
                "unnest_implicit_column"
            );
            columnSources.put(implicitColumnName, implicitSource);
        }

        // Add WITH OFFSET column if present
        if (offsetColumnName != null) {
            ColumnSource offsetSource = new ColumnSource(
                this,
                offsetColumnName,
                null,
                1.0,
                "unnest_offset_column"
            );
            columnSources.put(offsetColumnName, offsetSource);
        }

        // Add derived columns from STRUCT types if available
        if (unnestClause != null && unnestClause.getDerivedColumnList() != null) {
            for (int i = 0; i < unnestClause.getDerivedColumnList().size(); i++) {
                TObjectName derivedCol = unnestClause.getDerivedColumnList().getObjectName(i);
                if (derivedCol != null) {
                    String colName = derivedCol.toString();
                    ColumnSource derivedSource = new ColumnSource(
                        this,
                        colName,
                        null,
                        1.0,
                        "unnest_struct_field"
                    );
                    columnSources.put(colName, derivedSource);
                }
            }
        }
    }

    @Override
    public boolean hasStarColumn() {
        // UNNEST tables support star expansion for SELECT *
        // but have fixed columns (implicit + offset + struct fields)
        return true;
    }

    @Override
    public boolean supportsDynamicInference() {
        // Only allow dynamic inference for anonymous UNNEST (no explicit alias) that operates
        // on a subquery returning a struct. In this case, struct fields become accessible
        // as unqualified columns.
        //
        // Example: SELECT field FROM UNNEST((SELECT struct_col FROM table))
        // Here 'field' is a struct field that should be inferred from the UNNEST.
        //
        // For UNNEST with alias or UNNEST on a simple column, don't allow dynamic inference.
        // This prevents external variables from being incorrectly attributed to the UNNEST.
        return !hasExplicitAlias && arrayExpressionIsSubquery;
    }

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

        // Only allow inferred columns when dynamic inference is supported.
        // UNNEST with explicit alias has a fixed set of columns (implicit column, offset, struct fields)
        // and should NOT accept arbitrary inferred columns from star push-down or enhancement.
        // This prevents external variables (like function parameters) from being incorrectly
        // attributed to the UNNEST table.
        if (!supportsDynamicInference()) {
            return false;
        }

        if (inferredColumnNames == null) {
            inferredColumnNames = new HashSet<>();
        }

        if (columnSources != null && columnSources.containsKey(columnName)) {
            return false;
        }

        if (inferredColumnNames.contains(columnName)) {
            return false;
        }

        inferredColumnNames.add(columnName);

        // Also add to column sources
        if (columnSources != null) {
            ColumnSource source = new ColumnSource(
                this,
                columnName,
                null,
                confidence,
                evidence
            );
            columnSources.put(columnName, source);
        }

        return true;
    }

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

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

        // Check explicit columns (implicit column, offset, struct fields)
        if (columnSources != null) {
            for (String existingCol : columnSources.keySet()) {
                if (nameMatcher.matches(existingCol, columnName)) {
                    return ColumnLevel.EXISTS;
                }
            }
        }

        // Check inferred columns
        if (inferredColumnNames != null && inferredColumnNames.contains(columnName)) {
            return ColumnLevel.EXISTS;
        }

        // For anonymous UNNEST on a subquery (struct expansion), return MAYBE to allow inference.
        // The actual column will be inferred in resolveColumn().
        if (!hasExplicitAlias && arrayExpressionIsSubquery) {
            return ColumnLevel.MAYBE;
        }

        // For other UNNEST types, don't auto-infer unknown columns.
        // Unknown columns should resolve to outer scopes (correlated references)
        return ColumnLevel.NOT_EXISTS;
    }

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

        // Check explicit columns (implicit column, offset, struct fields)
        ColumnSource source = super.resolveColumn(columnName);
        if (source != null) {
            return source;
        }

        // Check inferred columns (added via addInferredColumn or resolveQualifiedStructField)
        if (inferredColumnNames != null && inferredColumnNames.contains(columnName)) {
            return columnSources.get(columnName);
        }

        // For anonymous UNNEST on a subquery (struct expansion), dynamically infer struct fields.
        // This allows struct field access without qualification when there's no alias.
        // Example: SELECT field FROM UNNEST((SELECT struct_col FROM table))
        // Here 'field' is a struct field that should be inferred from the UNNEST.
        if (!hasExplicitAlias && arrayExpressionIsSubquery) {
            return resolveQualifiedStructField(columnName);
        }

        // For other UNNEST types, don't auto-infer unknown columns.
        // Unknown unqualified columns should resolve to outer scopes or be marked as missed.
        // For qualified struct field access (e.g., alias.field), use resolveQualifiedStructField().
        return null;
    }

    /**
     * Resolve a qualified struct field reference (e.g., "alias.field").
     * This method infers struct fields when no metadata is available.
     *
     * <p>Use this method when resolving qualified column references like "x.field"
     * where "x" is the UNNEST alias. This is appropriate for UNNEST of STRUCT arrays
     * where the struct fields are accessed via the alias.
     *
     * <p>For unqualified column references, use {@link #resolveColumn(String)} which
     * does NOT infer columns and returns null for unknown columns.
     *
     * @param columnName the column/field name (without the table qualifier)
     * @return ColumnSource for the inferred struct field
     */
    public ColumnSource resolveQualifiedStructField(String columnName) {
        if (columnName == null || columnName.isEmpty()) {
            return null;
        }

        ensureValidated();

        // Check if already known
        ColumnSource existing = super.resolveColumn(columnName);
        if (existing != null) {
            return existing;
        }

        // Infer the column as a struct field
        ColumnSource inferred = new ColumnSource(
            this,
            columnName,
            null,  // No definition node
            0.8,   // Moderate confidence - inferred from usage
            "inferred_unnest_struct_field"
        );

        // Cache it for future lookups
        if (inferredColumnNames == null) {
            inferredColumnNames = new HashSet<>();
        }
        inferredColumnNames.add(columnName);
        columnSources.put(columnName, inferred);

        return inferred;
    }

    /**
     * Get the array expression being unnested.
     * This is used to track the source of the unnested data.
     */
    public TExpression getArrayExpression() {
        return unnestClause != null ? unnestClause.getArrayExpr() : null;
    }

    /**
     * Get the implicit column name for the unnested elements.
     */
    public String getImplicitColumnName() {
        return implicitColumnName;
    }

    /**
     * Get the offset column name if WITH OFFSET is present.
     */
    public String getOffsetColumnName() {
        return offsetColumnName;
    }

    public TTable getUnnestTable() {
        return unnestTable;
    }

    /**
     * {@inheritDoc}
     * For UnnestNamespace, returns the TTable representing the UNNEST expression.
     * This is the immediate source table for columns resolved through this UNNEST.
     */
    @Override
    public TTable getSourceTable() {
        return unnestTable;
    }

    @Override
    public String toString() {
        return String.format("UnnestNamespace(%s, implicitCol=%s, offsetCol=%s)",
            getDisplayName(),
            implicitColumnName,
            offsetColumnName);
    }
}