package gudusoft.gsqlparser.resolver2.namespace;

import gudusoft.gsqlparser.EDbVendor;
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: BigQuery {@code UNNEST(arr) AS x}, the Trino family
 * {@code CROSS JOIN UNNEST(arr) AS t(x)}, and the other dialects with an UNNEST relation.
 *
 * 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;

    /**
     * How many output columns the clause generated, as opposed to how many distinct names they
     * ended up under: {@code UNNEST(x) AS x WITH OFFSET AS x} generates two outputs that share one
     * name, and a map keyed by name then holds one of them. The schema is complete only when
     * every generated output has a name of its own.
     */
    private int generatedOutputs;
    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;
    }

    /**
     * Whether this namespace enumerates the UNNEST table's whole schema.
     *
     * <p>It does when the structure was DECLARED — explicit alias columns
     * ({@code UNNEST(x) AS t(a, b)}) or a derived struct-field list — or when the element type
     * is visibly scalar: an array literal whose every item is a plain constant
     * ({@code UNNEST(['foo','bar'])}), for which the implicit element column and WITH OFFSET are
     * the complete output. For anything else — a column, a function result, an array literal of
     * {@code STRUCT(...)} items — the element may be a struct whose fields this namespace cannot
     * list (they are resolved on demand as struct field access), so the schema is NOT complete
     * and a star over the table still carries information. Any inferred field settles it the
     * same way.
     */
    /**
     * {@code FROM orders_raw, UNNEST(items)}: an unaliased value table whose element type is unknown exposes the
     * element's fields under their own names, so an unqualified name no table proves may be one of them (Mantis
     * 4828, STRUCT tree plan rule R3). BigQuery exposes the fields of a STRUCT element; Presto, Trino and Athena
     * expand an ARRAY(ROW) into one column per field, named after the field. Other vendors (PostgreSQL names the
     * one unnest column after the function) never expose fields this way.
     */
    public boolean mayHoldUnknownFields() {
        if (hasExplicitAlias || arrayExpressionIsSubquery || !exposesElementFields(unnestTable.dbvendor)) {
            return false;
        }
        return getMetadataState() == MetadataState.METADATA_UNAVAILABLE;
    }

    /** Whether the vendor's unaliased UNNEST exposes the fields of a STRUCT or ROW element as columns. */
    private static boolean exposesElementFields(EDbVendor vendor) {
        return vendor == EDbVendor.dbvbigquery || vendor == EDbVendor.dbvpresto || vendor == EDbVendor.dbvtrino
                || vendor == EDbVendor.dbvathena;
    }

    @Override
    public MetadataState getMetadataState() {
        ensureValidated();
        if (columnSources == null || columnSources.isEmpty()) {
            return MetadataState.METADATA_UNAVAILABLE;
        }
        if (inferredColumnNames != null && !inferredColumnNames.isEmpty()) {
            return MetadataState.METADATA_UNAVAILABLE;
        }
        if (generatedOutputs != columnSources.size()) {
            return MetadataState.METADATA_UNAVAILABLE;   // two outputs collapsed onto one name
        }
        boolean declared = (unnestTable.getAliasClause() != null
                && unnestTable.getAliasClause().getColumns() != null
                && unnestTable.getAliasClause().getColumns().size() > 0)
                || (unnestClause != null && unnestClause.getDerivedColumnList() != null
                && unnestClause.getDerivedColumnList().size() > 0);
        if (declared) {
            return MetadataState.FOUND;
        }
        return elementIsScalarLiteral() ? MetadataState.FOUND : MetadataState.METADATA_UNAVAILABLE;
    }

    /** The UNNEST argument is an array literal whose every item is a plain constant. */
    private boolean elementIsScalarLiteral() {
        if (unnestClause == null) return false;
        gudusoft.gsqlparser.nodes.TExpression array = unnestClause.getArrayExpr();
        if (array == null || array.getExpressionType() != gudusoft.gsqlparser.EExpressionType.array_t) {
            return false;
        }
        gudusoft.gsqlparser.nodes.TExpressionList items = array.getExprList();
        if (items == null || items.size() == 0) return false;
        for (int i = 0; i < items.size(); i++) {
            gudusoft.gsqlparser.nodes.TExpression item = items.getExpression(i);
            if (item == null || item.getExpressionType() != gudusoft.gsqlparser.EExpressionType.simple_constant_t) {
                return false;
            }
        }
        return true;
    }

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

        // 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);
                    generatedOutputs++;
                }
            }
        }

        // 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);
            generatedOutputs++;
        }

        // 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);
            generatedOutputs++;
        }

        // 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);
                    generatedOutputs++;
                }
            }
        } else if (unnestTable != null) {
            // The fields of the STRUCT literals the array holds, as the parse reads them (Mantis 4797):
            // UNNEST([STRUCT(0 AS a, 1 AS b)]), or a CTE or derived column defined so, has the columns a and b.
            java.util.List<String> fields = gudusoft.gsqlparser.stmt.TSelectSqlStatement.unnestStructFieldNames(unnestTable);
            if (fields != null) {
                for (String field : fields) {
                    columnSources.put(field, new ColumnSource(this, field, null, 1.0, "unnest_struct_field"));
                    generatedOutputs++;
                }
            }
        }
    }

    @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<>();
        }

        // Slice S1: dedupe via matcher-aware helpers so per-vendor identifier
        // rules (BigQuery columns case-insensitive; Oracle quoted vs unquoted)
        // govern collision detection.
        if (containsColumnByMatcher(columnSources, columnName)) {
            return false;
        }

        if (containsColumnNameByMatcher(inferredColumnNames, 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 (containsColumnByMatcher(columnSources, columnName)) {
            return ColumnLevel.EXISTS;
        }

        // Check inferred columns. Slice S1: route through matcher-aware
        // helper so case-only-different references don't drop to NOT_EXISTS
        // on case-insensitive vendors.
        if (containsColumnNameByMatcher(inferredColumnNames, 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).
        // Slice S1: matcher-aware containment check; lookup in columnSources is
        // matcher-aware via super.resolveColumn() above.
        if (containsColumnNameByMatcher(inferredColumnNames, columnName)) {
            ColumnSource viaMatcher = super.resolveColumn(columnName);
            if (viaMatcher != null) {
                return viaMatcher;
            }
            return columnSources != null ? columnSources.get(columnName) : null;
        }

        // 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);
        noteColumnSourceAdded(columnName);

        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);
    }
}