package gudusoft.gsqlparser.resolver2.namespace;

import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.sqlenv.ESQLDataObjectType;
import gudusoft.gsqlparser.nodes.TTable;
import gudusoft.gsqlparser.nodes.TColumnDefinition;
import gudusoft.gsqlparser.nodes.TColumnDefinitionList;
import gudusoft.gsqlparser.nodes.TExpression;
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;

    /**
     * Whether two generated outputs of a known table function ended up under ONE name — a partial
     * alias list renames the leading outputs, and a renamed one can collide with a built-in name
     * that follows ({@code STRING_SPLIT(...) AS f(ordinal)} renames {@code value} to {@code ordinal}
     * and then meets the real {@code ordinal}). The name-keyed map then holds one of the two, so
     * the enumerated set is NOT the whole schema and this namespace must not claim it is.
     */
    private boolean generatedNameCollision = false;

    /** The resolved TSQLTable from SQLEnv (if found) */
    private TSQLTable resolvedTable;
    private java.util.Map<gudusoft.gsqlparser.sqlenv.CanonKey, String> catalogColumnKeys;

    /** A namespace exposes catalog names bare; references still use SQL spelling. */
    public String getColumnLookupSpelling(String exposedName) {
        ensureValidated();
        if (resolvedTable == null) return exposedName;
        return gudusoft.gsqlparser.sqlenv.IdentifierCodec.encodeStored(
                sqlEnv.getIdentifierService().getProfile(), ESQLDataObjectType.dotColumn, exposedName);
    }

    @Override
    protected String findColumnKeyByMatcher(String lexicalName) {
        if (catalogColumnKeys == null) return super.findColumnKeyByMatcher(lexicalName);
        return catalogColumnKeys.get(sqlEnv.getIdentifierService().canonKeyOf(lexicalName,
                ESQLDataObjectType.dotColumn, gudusoft.gsqlparser.sqlenv.IdentifierInputForm.SQL_LEXICAL));
    }

    private boolean hasStatementMetadata;
    private TSQLTable statementMetadata;

    public void setStatementMetadata(TSQLTable metadata, QualifiedNameResolver resolver) {
        hasStatementMetadata = true;
        statementMetadata = metadata;
        qualifiedNameResolver = resolver;
        qualifiedName = null;
    }


    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;
        generatedNameCollision = false;
        resolvedTable = null;
        catalogColumnKeys = 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 = hasStatementMetadata ? statementMetadata : sqlEnv.searchTable(tableName);
            }

            if (sqlTable != null) {
                // Found table in SQLEnv - load all columns
                resolvedTable = sqlTable;
                catalogColumnKeys = new java.util.HashMap<>();
                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);
                    catalogColumnKeys.put(sqlEnv.getIdentifierService().canonKeyOf(colName,
                            ESQLDataObjectType.dotColumn, gudusoft.gsqlparser.sqlenv.IdentifierInputForm.CATALOG_STORED), colName);
                }
                return; // SQLEnv metadata found
            }
        }

        // Priority 3: Table-valued functions.
        if (table.getTableType() == gudusoft.gsqlparser.ETableSource.function) {
            // 3a: a built-in whose columns the vendor fixes (TableFunctionLayout, the one rule the SQL-LR
            //     producer publishes too): its names after an alias column list renames the leading ones
            //     POSITIONALLY (FROM TABLE(FLATTEN(input => t.c)) f(s, k)), or after PostgreSQL's table alias
            //     names the one column of a function of a base type (FROM unnest(a) AS u has the column u)
            // a user table function the catalog declares (a CREATE FUNCTION of the input before the call): its
            // declared columns, recorded on the call for every consumer of the layout
            TableFunctionLayout.recordDeclared(table, sqlEnv);
            TableFunctionLayout layout = TableFunctionLayout.of(table);
            if (layout != null && layout.getNames() != null) {
                for (String name : layout.getNames()) {
                    if (name != null) {
                        addKnownFunctionColumn(name);
                    }
                }
                // an unnest of an array of unknown element type may have a composite's columns as well
                hasMetadata = layout.isClosed();
                return;
            }
            TObjectNameList aliasColumns = functionAliasColumns();

            // 3b: an explicit column alias list names the leading output columns of a function
            //     whose own columns are unknown
            if (aliasColumns != null) {
                for (int i = 0; i < aliasColumns.size(); i++) {
                    TObjectName colName = aliasColumns.getObjectName(i);
                    if (colName != null) {
                        addKnownFunctionColumn(colName.toString());
                    }
                }
                // 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;
            }
        }

        // 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()
    }

    /**
     * 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;
    }

    /**
     * Add a known column for a table-valued function.
     */
    private void addKnownFunctionColumn(String columnName) {
        if (columnSources.containsKey(columnName)) {
            generatedNameCollision = true;
        }
        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
        String matchedKey = findColumnKeyByMatcher(columnName);
        ColumnSource existing = matchedKey == null ? null : columnSources.get(matchedKey);
        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 — unless two
        // generated outputs collapsed onto one name, in which case the map is not the schema.
        if (hasMetadata && columnSources != null && !columnSources.isEmpty() && !generatedNameCollision) {
            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() : "?") + ")";
    }
}