package gudusoft.gsqlparser.resolver2.namespace;

import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.nodes.TTable;
import gudusoft.gsqlparser.nodes.TColumnDefinition;
import gudusoft.gsqlparser.nodes.TColumnDefinitionList;
import gudusoft.gsqlparser.nodes.TObjectName;
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.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: Check for known table-valued functions with well-defined output columns
        if (table.getTableType() == gudusoft.gsqlparser.ETableSource.function) {
            String funcName = getTableFunctionName();
            if (funcName != null) {
                String upperName = funcName.toUpperCase();
                if (upperName.equals("STRING_SPLIT")) {
                    // STRING_SPLIT returns: value, ordinal (ordinal is SQL Server 2022+)
                    hasMetadata = true;
                    addKnownFunctionColumn("value");
                    addKnownFunctionColumn("ordinal");
                    return;
                }
                // Add more known table functions here as needed
                // e.g., OPENJSON, OPENXML, etc.
            }
        }

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

    /**
     * Get the function name for table-valued functions.
     */
    private String getTableFunctionName() {
        if (table.getTableName() != null) {
            return table.getTableName().toString();
        }
        if (table.getFuncCall() != null && table.getFuncCall().getFunctionName() != null) {
            return table.getFuncCall().getFunctionName().toString();
        }
        return table.getName();
    }

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

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

        // Check if column exists in known columns (including inferred ones)
        for (String existingCol : columnSources.keySet()) {
            if (nameMatcher.matches(existingCol, columnName)) {
                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);

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

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

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