package gudusoft.gsqlparser.resolver2.namespace;

import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.sqlenv.ESQLDataObjectType;
import gudusoft.gsqlparser.util.SQLUtil;
import gudusoft.gsqlparser.nodes.TTable;
import gudusoft.gsqlparser.nodes.TColumnDefinition;
import gudusoft.gsqlparser.nodes.TColumnDefinitionList;
import gudusoft.gsqlparser.nodes.TExpression;
import gudusoft.gsqlparser.nodes.TFunctionCall;
import gudusoft.gsqlparser.nodes.TObjectName;
import gudusoft.gsqlparser.nodes.TObjectNameList;
import gudusoft.gsqlparser.resolver2.ColumnLevel;
import gudusoft.gsqlparser.resolver2.matcher.INameMatcher;
import gudusoft.gsqlparser.resolver2.model.ColumnSource;
import gudusoft.gsqlparser.resolver2.model.QualifiedName;
import gudusoft.gsqlparser.resolver2.model.QualifiedNameResolver;
import gudusoft.gsqlparser.sqlenv.TSQLCatalog;
import gudusoft.gsqlparser.sqlenv.TSQLEnv;
import gudusoft.gsqlparser.sqlenv.TSQLTable;
import gudusoft.gsqlparser.sqlenv.TSQLColumn;

import java.util.LinkedHashMap;
import java.util.Map;

/**
 * Namespace representing a physical table.
 * Provides column information from table metadata.
 *
 * Column sources come from:
 * 1. Table metadata (if available from DDL)
 * 2. External metadata providers (SQLEnv)
 * 3. Inferred from usage (when no metadata available)
 */
public class TableNamespace extends AbstractNamespace {

    private final TTable table;

    /** TSQLEnv for looking up table metadata */
    private TSQLEnv sqlEnv;

    /** Database vendor for qualified name resolution */
    private EDbVendor vendor;

    /** Qualified name resolver for normalizing table references */
    private QualifiedNameResolver qualifiedNameResolver;

    /** The qualified name of this table (with defaults applied) */
    private QualifiedName qualifiedName;

    /** Flag to track if this table has actual metadata (vs. only inferred columns) */
    private boolean hasMetadata = false;

    /** The resolved TSQLTable from SQLEnv (if found) */
    private TSQLTable resolvedTable;

    public TableNamespace(TTable table, INameMatcher nameMatcher) {
        super(table, nameMatcher);
        this.table = table;
    }

    public TableNamespace(TTable table, INameMatcher nameMatcher, TSQLEnv sqlEnv) {
        super(table, nameMatcher);
        this.table = table;
        this.sqlEnv = sqlEnv;
        // Default vendor - use setVendor() to override
        if (sqlEnv != null) {
            this.qualifiedNameResolver = new QualifiedNameResolver(sqlEnv, EDbVendor.dbvoracle);
        }
    }

    /**
     * Create a TableNamespace with full qualified name resolution support.
     *
     * @param table The table AST node
     * @param nameMatcher The name matcher for case sensitivity
     * @param sqlEnv The SQL environment for metadata lookup
     * @param vendor The database vendor
     */
    public TableNamespace(TTable table, INameMatcher nameMatcher, TSQLEnv sqlEnv, EDbVendor vendor) {
        super(table, nameMatcher);
        this.table = table;
        this.sqlEnv = sqlEnv;
        this.vendor = vendor;
        if (sqlEnv != null) {
            this.qualifiedNameResolver = new QualifiedNameResolver(sqlEnv, vendor);
        }
    }

    public TableNamespace(TTable table) {
        super(table);
        this.table = table;
    }

    /**
     * Set the TSQLEnv for metadata lookup.
     * @param sqlEnv the SQL environment containing table metadata
     */
    public void setSqlEnv(TSQLEnv sqlEnv) {
        this.sqlEnv = sqlEnv;
        if (sqlEnv != null && vendor != null) {
            this.qualifiedNameResolver = new QualifiedNameResolver(sqlEnv, vendor);
        }
        // Reset cached qualified name so it's recomputed with new env
        this.qualifiedName = null;
    }

    /**
     * Get the TSQLEnv used for metadata lookup.
     * @return the SQL environment, or null if not set
     */
    public TSQLEnv getSqlEnv() {
        return sqlEnv;
    }

    /**
     * Set the database vendor.
     * @param vendor the database vendor
     */
    public void setVendor(EDbVendor vendor) {
        this.vendor = vendor;
        if (sqlEnv != null) {
            this.qualifiedNameResolver = new QualifiedNameResolver(sqlEnv, vendor);
        }
        // Reset cached qualified name
        this.qualifiedName = null;
    }

    /**
     * Get the database vendor.
     * @return the database vendor, or null if not set
     */
    public EDbVendor getVendor() {
        return vendor;
    }

    /**
     * Get the qualified name resolver.
     * @return the resolver, or null if sqlEnv is not set
     */
    public QualifiedNameResolver getQualifiedNameResolver() {
        return qualifiedNameResolver;
    }

    /**
     * Get the fully qualified name of this table.
     *
     * <p>The qualified name is computed by applying defaults from TSQLEnv
     * to the table's partial name.
     *
     * @return the qualified name, or null if table name is unavailable
     */
    public QualifiedName getQualifiedName() {
        if (qualifiedName != null) {
            return qualifiedName;
        }

        TObjectName tableName = table.getTableName();
        if (tableName == null) {
            return null;
        }

        if (qualifiedNameResolver != null) {
            qualifiedName = qualifiedNameResolver.resolve(tableName);
        } else {
            // Fallback: build from table name parts without defaults
            String catalog = tableName.getDatabaseString();
            String schema = tableName.getSchemaString();
            String name = tableName.getObjectString();
            if (name == null || name.isEmpty()) {
                name = tableName.toString();
            }
            if (name != null && !name.isEmpty()) {
                qualifiedName = QualifiedName.forTable(catalog, schema, name);
            }
        }

        return qualifiedName;
    }

    /**
     * Get the resolved TSQLTable from SQLEnv.
     * @return the resolved table, or null if not found in SQLEnv
     */
    public TSQLTable getResolvedTable() {
        return resolvedTable;
    }

    @Override
    public String getDisplayName() {
        String alias = table.getAliasName();
        if (alias != null && !alias.isEmpty()) {
            return alias;
        }
        String fullName = table.getFullName();
        if (fullName != null && !fullName.isEmpty()) {
            return fullName;
        }
        // Fallback to simple table name
        String name = table.getName();
        if (name != null && !name.isEmpty()) {
            return name;
        }
        // Last resort: try table name object
        TObjectName tableName = table.getTableName();
        if (tableName != null) {
            String tableStr = tableName.getTableString();
            if (tableStr != null && !tableStr.isEmpty()) {
                return tableStr;
            }
            String tableNameStr = tableName.toString();
            if (tableNameStr != null && !tableNameStr.isEmpty()) {
                return tableNameStr;
            }
        }
        return "";
    }

    @Override
    public TTable getFinalTable() {
        return table;
    }

    @Override
    public TTable getSourceTable() {
        return table;
    }

    @Override
    protected void doValidate() {
        // Load columns from metadata
        columnSources = new LinkedHashMap<>();
        hasMetadata = false;
        resolvedTable = null;

        // Priority 1: Check if table has column list (from DDL like CREATE TABLE)
        TColumnDefinitionList columnDefs = table.getColumnDefinitions();
        if (columnDefs != null && columnDefs.size() > 0) {
            // Has explicit column list from CREATE TABLE or metadata
            hasMetadata = true;
            for (int i = 0; i < columnDefs.size(); i++) {
                TColumnDefinition colDef = columnDefs.getColumn(i);
                TObjectName colNameObj = colDef.getColumnName();
                if (colNameObj == null) continue;

                String colName = colNameObj.toString();

                ColumnSource source = new ColumnSource(
                    this,
                    colName,
                    colDef,
                    1.0,  // Definite - from DDL metadata
                    "ddl_metadata"
                );
                columnSources.put(colName, source);
            }
            return; // DDL metadata takes priority
        }

        // Priority 2: Try to load from TSQLEnv if available
        if (sqlEnv != null) {
            TObjectName tableName = table.getTableName();
            TSQLTable sqlTable = null;
            if (tableName != null) {
                sqlTable = sqlEnv.searchTable(tableName);
            }

            if (sqlTable != null) {
                // Found table in SQLEnv - load all columns
                resolvedTable = sqlTable;
                hasMetadata = true;

                for (TSQLColumn column : sqlTable.getColumnList()) {
                    String colName = column.getName();

                    ColumnSource source = new ColumnSource(
                        this,
                        colName,
                        null,  // No TColumnDefinition node from SQLEnv
                        1.0,   // Definite - from SQLEnv metadata
                        "sqlenv_metadata"
                    );
                    columnSources.put(colName, source);
                }
                return; // SQLEnv metadata found
            }
        }

        // Priority 3: Table-valued functions.
        if (table.getTableType() == gudusoft.gsqlparser.ETableSource.function) {
            String[] knownColumns = knownTableFunctionColumns();
            TObjectNameList aliasColumns = functionAliasColumns();

            // 3a: an explicit column alias list renames the function's output columns
            //     POSITIONALLY, e.g. FROM TABLE(FLATTEN(input => t.c)) f(s, k).
            if (aliasColumns != null) {
                for (int i = 0; i < aliasColumns.size(); i++) {
                    TObjectName colName = aliasColumns.getObjectName(i);
                    if (colName != null) {
                        addKnownFunctionColumn(colName.toString());
                    }
                }
                if (knownColumns != null) {
                    // A partial list renames only the leading columns; the rest keep
                    // their built-in names, so the exposed set is still fully known.
                    for (int i = aliasColumns.size(); i < knownColumns.length; i++) {
                        addKnownFunctionColumn(knownColumns[i]);
                    }
                    hasMetadata = true;
                } else {
                    // The function's own output schema is unknown, so a partial list
                    // leaves unnamed columns that keep names we cannot enumerate.
                    // Stay non-authoritative: those must remain inferable rather than
                    // being reported as non-existent.
                    hasMetadata = false;
                }
                return;
            }

            // 3b: known table-valued functions with well-defined output columns
            if (knownColumns != null) {
                hasMetadata = true;
                for (String knownColumn : knownColumns) {
                    addKnownFunctionColumn(knownColumn);
                }
                return;
            }
        }

        // Priority 4: No metadata available
        // For tables without metadata, we'll handle this as MAYBE in hasColumn
        // Columns will be inferred from usage in resolveColumn()
    }

    /** SQL Server STRING_SPLIT output columns (ordinal is SQL Server 2022+). */
    private static final String[] STRING_SPLIT_COLUMNS = {"value", "ordinal"};

    /**
     * Snowflake FLATTEN output columns. FLATTEN is a built-in table function with a
     * fixed, documented output schema:
     * https://docs.snowflake.com/en/sql-reference/functions/flatten
     */
    private static final String[] SNOWFLAKE_FLATTEN_COLUMNS =
        {"SEQ", "KEY", "PATH", "INDEX", "VALUE", "THIS"};

    /**
     * Snowflake SPLIT_TO_TABLE output columns — same fixed-schema shape as FLATTEN:
     * https://docs.snowflake.com/en/sql-reference/functions/split_to_table
     */
    private static final String[] SNOWFLAKE_SPLIT_TO_TABLE_COLUMNS = {"SEQ", "INDEX", "VALUE"};

    /**
     * The explicit column alias list of this table, or null when there is none.
     *
     * <p>Snowflake, PostgreSQL and Presto/Trino all allow renaming a table
     * function's output columns: {@code FROM f(...) alias (c1, c2, ...)}. The list
     * may be shorter than the function's output — the remaining columns then keep
     * their original names.</p>
     */
    private TObjectNameList functionAliasColumns() {
        if (table.getAliasClause() == null) {
            return null;
        }
        TObjectNameList columns = table.getAliasClause().getColumns();
        if (columns == null || columns.size() == 0) {
            return null;
        }
        return columns;
    }

    /**
     * Output columns of a built-in table-valued function whose schema is fixed by
     * the vendor, or null when this function has no known schema (the caller then
     * falls back to inferring columns from usage).
     *
     * <p>Only an unqualified function name is matched: a schema-qualified call such
     * as {@code myschema.flatten(...)} is a user-defined function that merely shares
     * the name, and must keep the inferring behaviour.</p>
     */
    private String[] knownTableFunctionColumns() {
        EDbVendor tableVendor = getTableVendor();

        if (tableVendor == EDbVendor.dbvsnowflake) {
            // Mantis 4663: an unqualified reference to a FLATTEN output column (VALUE,
            // SEQ, ...) must resolve to the FLATTEN table rather than being left
            // ambiguous against the other FROM-clause items. Snowflake spells these
            // built-ins both as LATERAL f(...) and as TABLE(f(...)), so the TABLE
            // wrapper is transparent here.
            String snowflakeName = getUnqualifiedTableFunctionName(true);
            if (snowflakeName != null && !snowflakeName.isEmpty()) {
                if (SQLUtil.sameName(tableVendor, ESQLDataObjectType.dotFunction,
                                     snowflakeName, "FLATTEN")) {
                    return SNOWFLAKE_FLATTEN_COLUMNS;
                }
                if (SQLUtil.sameName(tableVendor, ESQLDataObjectType.dotFunction,
                                     snowflakeName, "SPLIT_TO_TABLE")) {
                    return SNOWFLAKE_SPLIT_TO_TABLE_COLUMNS;
                }
            }
        }

        // STRING_SPLIT is matched on the directly named function only. Looking through
        // a TABLE(...) wrapper here would newly claim SQL Server's schema for a
        // same-named user-defined table function in another dialect, whose real output
        // columns would then be reported as non-existent.
        String directName = getUnqualifiedTableFunctionName(false);
        if (directName != null && !directName.isEmpty()
            && SQLUtil.sameName(getTableVendor(), ESQLDataObjectType.dotFunction,
                                directName, "STRING_SPLIT")) {
            return STRING_SPLIT_COLUMNS;
        }

        // Add more known table functions here as needed
        // e.g., OPENJSON, OPENXML, etc.
        return null;
    }

    /**
     * The vendor of the parsed statement this table belongs to. Prefers the vendor
     * the namespace was constructed with and falls back to the AST node's own
     * vendor, because {@link gudusoft.gsqlparser.resolver2.ScopeBuilder} builds
     * table namespaces through constructors that do not carry the vendor.
     */
    private EDbVendor getTableVendor() {
        if (vendor != null) {
            return vendor;
        }
        return table.dbvendor;
    }

    /**
     * Get the function name for table-valued functions, but only when the call is
     * unqualified. Returns null for a schema/database-qualified call.
     *
     * @param lookThroughTableKeyword when true, an explicit {@code TABLE(...)} wrapper
     *        is transparent: Snowflake spells the same table function both ways —
     *        {@code LATERAL FLATTEN(input => c)} and {@code TABLE(FLATTEN(input => c))}
     *        — and both expose the same output columns.
     */
    private String getUnqualifiedTableFunctionName(boolean lookThroughTableKeyword) {
        if (lookThroughTableKeyword) {
            TFunctionCall unwrapped = unwrapTableKeywordFunction(table.getFuncCall());
            if (unwrapped != null && unwrapped != table.getFuncCall()
                && unwrapped.getFunctionName() != null) {
                TObjectName funcName = unwrapped.getFunctionName();
                return isQualified(funcName) ? null : funcName.toString();
            }
        }

        TObjectName tableName = table.getTableName();
        if (tableName != null) {
            if (isQualified(tableName)) {
                return null;
            }
            return tableName.toString();
        }
        if (table.getFuncCall() != null && table.getFuncCall().getFunctionName() != null) {
            TObjectName funcName = table.getFuncCall().getFunctionName();
            if (isQualified(funcName)) {
                return null;
            }
            return funcName.toString();
        }
        return table.getName();
    }

    /**
     * Unwrap {@code TABLE(f(...))} to the inner call {@code f(...)}, or return the
     * call unchanged when it is not such a wrapper. Only a single-argument
     * {@code TABLE(...)} whose sole argument is itself a function call is unwrapped,
     * so a genuine user function named TABLE with other arguments is left alone.
     */
    private TFunctionCall unwrapTableKeywordFunction(TFunctionCall funcCall) {
        if (funcCall == null || funcCall.getFunctionName() == null) {
            return funcCall;
        }
        if (isQualified(funcCall.getFunctionName())) {
            return funcCall;
        }
        if (!SQLUtil.sameName(getTableVendor(), ESQLDataObjectType.dotFunction,
                              funcCall.getFunctionName().toString(), "TABLE")) {
            return funcCall;
        }
        if (funcCall.getArgs() == null || funcCall.getArgs().size() != 1) {
            return funcCall;
        }
        TExpression arg = funcCall.getArgs().getExpression(0);
        if (arg == null || arg.getFunctionCall() == null) {
            return funcCall;
        }
        return arg.getFunctionCall();
    }

    private static boolean isQualified(TObjectName name) {
        String schema = name.getSchemaString();
        if (schema != null && !schema.isEmpty()) {
            return true;
        }
        String database = name.getDatabaseString();
        return database != null && !database.isEmpty();
    }


    /**
     * Add a known column for a table-valued function.
     */
    private void addKnownFunctionColumn(String columnName) {
        ColumnSource source = new ColumnSource(
            this,
            columnName,
            null,
            1.0,  // Definite - known function output column
            "known_function_column"
        );
        columnSources.put(columnName, source);
        noteColumnSourceAdded(columnName);
    }

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

        // Check if column exists in known columns (including inferred ones).
        // Canonical-key indexed lookup (Mantis 4684): the previous matcher loop
        // over the inferred-column map made wide select lists O(N²), with a
        // collator comparison per entry on SQL Server.
        if (findColumnKeyByMatcher(columnName) != null) {
            return ColumnLevel.EXISTS;
        }

        // If we don't have metadata, any column MAYBE exists
        // (we can't definitively say it doesn't exist)
        if (!hasMetadata) {
            return ColumnLevel.MAYBE;
        }

        return ColumnLevel.NOT_EXISTS;
    }

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

        // First try to find in known columns
        ColumnSource existing = super.resolveColumn(columnName);
        if (existing != null) {
            return existing;
        }

        // If no metadata available, create an inferred ColumnSource
        // This allows columns to be resolved against tables without metadata
        // Note: hasMetadata is false when we don't have DDL or SQLEnv metadata
        if (!hasMetadata && columnName != null && !columnName.isEmpty()) {
            // Create a column source with moderate confidence
            // Since we don't have metadata, we're inferring this column exists
            ColumnSource inferred = new ColumnSource(
                this,
                columnName,
                null,  // No definition node
                0.8,   // Moderate confidence - we don't have proof this column exists
                "inferred_from_usage"
            );

            // Cache it for future lookups
            columnSources.put(columnName, inferred);
            noteColumnSourceAdded(columnName);

            return inferred;
        }

        return null;
    }

    public TTable getTable() {
        return table;
    }

    /**
     * Returns true if this namespace has actual metadata (from DDL or SQLEnv).
     * When false, column resolution will infer columns from usage.
     *
     * @return true if metadata is available, false if columns are inferred
     */
    public boolean hasMetadata() {
        ensureValidated();
        return hasMetadata;
    }

    /**
     * Slice S4: authoritative metadata-state tri-value (plan §5.5).
     *
     * <p>Distinguishes:</p>
     * <ul>
     *   <li>{@link MetadataState#FOUND} — DDL columns present, or
     *       {@link TSQLEnv} returned a table with a non-empty column list, or
     *       a known table-valued function (e.g. STRING_SPLIT) populated its
     *       columns.</li>
     *   <li>{@link MetadataState#NOT_FOUND_IN_CATALOG} — a non-empty
     *       {@code TSQLEnv} was consulted but the table itself is absent.
     *       Never used when no environment has been configured.</li>
     *   <li>{@link MetadataState#METADATA_UNAVAILABLE} — no environment, an
     *       empty environment (no catalogs registered), the lookup returned a
     *       table without expanded columns (Q8 view-without-columns), or any
     *       other unauthoritative state.</li>
     * </ul>
     */
    @Override
    public MetadataState getMetadataState() {
        ensureValidated();

        // Authoritative: DDL columns or TSQLEnv-resolved table with columns.
        if (hasMetadata && columnSources != null && !columnSources.isEmpty()) {
            return MetadataState.FOUND;
        }

        // Found-but-unexpanded: TSQLEnv returned the table but no columns came
        // through. Q8 view-without-columns falls here. Never NOT_FOUND_IN_CATALOG
        // and never UNKNOWN_COLUMN — strict mode may emit a WARNING in S6.
        if (resolvedTable != null) {
            return MetadataState.METADATA_UNAVAILABLE;
        }

        // Lookup ran against a populated environment and missed entirely. The
        // env has at least one catalog registered, so we trust the absence as
        // an authoritative table-not-found signal. Bridges pre-register an
        // empty default catalog/schema during construction (see
        // CatalogRuntimeToSQLEnvBridge.applyDefaults), which preserves this
        // behavior under lazy materialization.
        if (sqlEnv != null && hasAnyCatalogContent(sqlEnv)) {
            return MetadataState.NOT_FOUND_IN_CATALOG;
        }

        return MetadataState.METADATA_UNAVAILABLE;
    }

    private static boolean hasAnyCatalogContent(TSQLEnv env) {
        if (env == null) {
            return false;
        }
        java.util.List<TSQLCatalog> catalogs = env.getCatalogList();
        return catalogs != null && !catalogs.isEmpty();
    }

    /**
     * Add a USING clause column to this table's namespace.
     * This is called during scope building when a JOIN...USING clause
     * includes columns from this table. The USING column exists in BOTH
     * tables of the join.
     *
     * @param columnName the name of the USING column
     */
    public void addUsingColumn(String columnName) {
        ensureValidated();

        // Only add if not already present
        if (hasColumn(columnName) == ColumnLevel.EXISTS) {
            return;
        }

        ColumnSource source = new ColumnSource(
            this,
            columnName,
            null,  // No definition node
            1.0,   // Definite - USING clause semantics guarantee the column exists
            "using_clause_column"
        );
        columnSources.put(columnName, source);
        noteColumnSourceAdded(columnName);
    }

    @Override
    public String toString() {
        return "TableNamespace(" + getDisplayName() + ", columns=" +
               (columnSources != null ? columnSources.size() : "?") + ")";
    }
}