from dataclasses import dataclass, field
from typing import Optional


@dataclass(frozen=True)
class DialectInfo:
    """Configuration for a SQL dialect."""

    name: str
    """SQLGlot-compatible dialect name."""

    quote_char: str
    """Character used to quote identifiers (` or ")."""

    system_schemas: tuple[str, ...] = field(default_factory=tuple)
    """Schema names to exclude when listing user tables."""

    list_tables_query: str = ""
    """SQL query template to list tables. Use {excluded_schemas} placeholder.
    Query should return columns that can be mapped via tables_column_map."""

    list_columns_query: str = ""
    """SQL query template to list columns. Use {schema} and {table} placeholders.
    Query should return columns that can be mapped via columns_column_map."""

    # Column name mappings: maps query result columns -> standard names
    # Standard table columns: schema, name, type
    # Standard column columns: name, type, nullable
    tables_column_map: tuple[tuple[str, str], ...] = (
        ("schema", "schema"),
        ("name", "name"),
        ("type", "type"),
    )
    """Maps result column names to standard names for list_tables queries."""

    columns_column_map: tuple[tuple[str, str], ...] = (
        ("column_name", "name"),
        ("data_type", "type"),
        ("is_nullable", "nullable"),
    )
    """Maps result column names to standard names for list_columns queries."""

    @property
    def uses_backticks(self) -> bool:
        """Whether this dialect uses backticks for identifier quoting."""
        return self.quote_char == "`"

    def quote_identifier(self, name: str) -> str:
        """Quote an identifier for this dialect."""
        if not name:
            return name
        return f"{self.quote_char}{name}{self.quote_char}"

    def get_excluded_schemas_sql(self) -> str:
        """Get comma-separated quoted schema names for SQL IN clause."""
        if not self.system_schemas:
            return "''"
        return ", ".join(f"'{s}'" for s in self.system_schemas)

    def map_tables_row(self, row: dict) -> dict:
        """Map a row from list_tables query to standard format {schema, name, type}."""
        result = {"schema": "", "name": "", "type": "table"}
        for src_col, dst_col in self.tables_column_map:
            if src_col in row:
                result[dst_col] = row[src_col] or ""
        return result

    def map_columns_row(self, row: dict) -> dict:
        """Map a row from list_columns query to standard format {name, type, nullable}."""
        result = {"name": "", "type": "", "nullable": True}
        for src_col, dst_col in self.columns_column_map:
            if src_col in row:
                value = row[src_col]
                if dst_col == "nullable":
                    # Handle various nullable representations
                    result[dst_col] = value in ("YES", "Y", True, 1, "TRUE", "true")
                else:
                    result[dst_col] = value or ""
        return result


# =============================================================================
# Dialect Definitions
# =============================================================================

_POSTGRESQL = DialectInfo(
    name="postgres",
    quote_char='"',
    system_schemas=("pg_catalog", "information_schema"),
    list_tables_query="""
        SELECT table_schema as schema, table_name as name, 'table' as type
        FROM information_schema.tables
        WHERE table_schema NOT IN ({excluded_schemas})
        AND table_type = 'BASE TABLE'
        ORDER BY table_schema, table_name
    """,
    list_columns_query="""
        SELECT column_name, data_type, is_nullable
        FROM information_schema.columns
        WHERE table_schema = '{schema}' AND table_name = '{table}'
        ORDER BY ordinal_position
    """,
)

_MYSQL = DialectInfo(
    name="mysql",
    quote_char="`",
    system_schemas=("mysql", "information_schema", "performance_schema", "sys"),
    list_tables_query="""
        SELECT table_schema as schema, table_name as name, 'table' as type
        FROM information_schema.tables
        WHERE table_schema NOT IN ({excluded_schemas})
        AND table_type = 'BASE TABLE'
        ORDER BY table_schema, table_name
    """,
    list_columns_query="""
        SELECT column_name, data_type, is_nullable
        FROM information_schema.columns
        WHERE table_schema = '{schema}' AND table_name = '{table}'
        ORDER BY ordinal_position
    """,
)

_SNOWFLAKE = DialectInfo(
    name="snowflake",
    quote_char='"',
    system_schemas=("INFORMATION_SCHEMA",),
    list_tables_query="""
        SELECT table_schema as schema, table_name as name, table_type as type
        FROM information_schema.tables
        WHERE table_schema NOT IN ({excluded_schemas})
        ORDER BY table_schema, table_name
    """,
    list_columns_query="""
        SELECT column_name, data_type, is_nullable
        FROM information_schema.columns
        WHERE table_schema = '{schema}' AND table_name = '{table}'
        ORDER BY ordinal_position
    """,
)

_BIGQUERY = DialectInfo(
    name="bigquery",
    quote_char="`",
    system_schemas=(),
    list_tables_query="""
        SELECT table_schema as schema, table_name as name, table_type as type
        FROM INFORMATION_SCHEMA.TABLES
        ORDER BY table_schema, table_name
    """,
    list_columns_query="""
        SELECT column_name, data_type, is_nullable
        FROM `{schema}`.INFORMATION_SCHEMA.COLUMNS
        WHERE table_name = '{table}'
        ORDER BY ordinal_position
    """,
)

_REDSHIFT = DialectInfo(
    name="redshift",
    quote_char='"',
    system_schemas=("pg_catalog", "information_schema", "pg_internal"),
    list_tables_query="""
        SELECT table_schema as schema, table_name as name, 'table' as type
        FROM information_schema.tables
        WHERE table_schema NOT IN ({excluded_schemas})
        AND table_type = 'BASE TABLE'
        ORDER BY table_schema, table_name
    """,
    list_columns_query="""
        SELECT column_name, data_type, is_nullable
        FROM information_schema.columns
        WHERE table_schema = '{schema}' AND table_name = '{table}'
        ORDER BY ordinal_position
    """,
)

_TSQL = DialectInfo(
    name="tsql",
    quote_char='"',
    system_schemas=("sys", "INFORMATION_SCHEMA"),
    list_tables_query="""
        SELECT SCHEMA_NAME(schema_id) as schema, name, 'table' as type
        FROM sys.tables
        ORDER BY SCHEMA_NAME(schema_id), name
    """,
    list_columns_query="""
        SELECT c.name as column_name, t.name as data_type,
               CASE WHEN c.is_nullable = 1 THEN 'YES' ELSE 'NO' END as is_nullable
        FROM sys.columns c
        JOIN sys.types t ON c.user_type_id = t.user_type_id
        JOIN sys.tables tbl ON c.object_id = tbl.object_id
        WHERE SCHEMA_NAME(tbl.schema_id) = '{schema}' AND tbl.name = '{table}'
        ORDER BY c.column_id
    """,
)

_ORACLE = DialectInfo(
    name="oracle",
    quote_char='"',
    system_schemas=("SYS", "SYSTEM", "DBSNMP", "OUTLN", "XDB", "CTXSYS", "MDSYS", "ORDDATA"),
    list_tables_query="""
        SELECT owner as schema, table_name as name, 'TABLE' as type
        FROM all_tables
        WHERE owner NOT IN ({excluded_schemas})
        ORDER BY owner, table_name
    """,
    list_columns_query="""
        SELECT column_name, data_type,
               CASE WHEN nullable = 'Y' THEN 'YES' ELSE 'NO' END as is_nullable
        FROM all_tab_columns
        WHERE owner = '{schema}' AND table_name = '{table}'
        ORDER BY column_id
    """,
)

_DATABRICKS = DialectInfo(
    name="databricks",
    quote_char="`",
    system_schemas=("information_schema",),
    list_tables_query="""
        SELECT table_schema as schema, table_name as name, table_type as type
        FROM information_schema.tables
        WHERE table_schema NOT IN ({excluded_schemas})
        ORDER BY table_schema, table_name
    """,
    list_columns_query="""
        SELECT column_name, data_type, is_nullable
        FROM information_schema.columns
        WHERE table_schema = '{schema}' AND table_name = '{table}'
        ORDER BY ordinal_position
    """,
)

_SPARK = DialectInfo(
    name="spark",
    quote_char="`",
    system_schemas=(),
    list_tables_query="""
        SHOW TABLES
    """,
    # DESCRIBE TABLE returns: col_name, data_type, comment
    list_columns_query="""
        DESCRIBE TABLE {schema}.{table}
    """,
    # Spark SHOW TABLES returns: database, tableName, isTemporary
    tables_column_map=(
        ("database", "schema"),
        ("tableName", "name"),
        ("isTemporary", "type"),  # Will be mapped, not ideal but functional
    ),
    # Spark DESCRIBE returns: col_name, data_type, comment
    columns_column_map=(
        ("col_name", "name"),
        ("data_type", "type"),
    ),
)

_PRESTO = DialectInfo(
    name="presto",
    quote_char='"',
    system_schemas=("information_schema",),
    list_tables_query="""
        SELECT table_schema as schema, table_name as name, table_type as type
        FROM information_schema.tables
        WHERE table_schema NOT IN ({excluded_schemas})
        ORDER BY table_schema, table_name
    """,
    list_columns_query="""
        SELECT column_name, data_type, is_nullable
        FROM information_schema.columns
        WHERE table_schema = '{schema}' AND table_name = '{table}'
        ORDER BY ordinal_position
    """,
)

_ATHENA = DialectInfo(
    name="athena",  # SQLGlot has native athena dialect
    quote_char='"',
    system_schemas=("information_schema",),
    list_tables_query="""
        SELECT table_schema as schema, table_name as name, table_type as type
        FROM information_schema.tables
        WHERE table_schema NOT IN ({excluded_schemas})
        ORDER BY table_schema, table_name
    """,
    list_columns_query="""
        SELECT column_name, data_type, is_nullable
        FROM information_schema.columns
        WHERE table_schema = '{schema}' AND table_name = '{table}'
        ORDER BY ordinal_position
    """,
)

_TRINO = DialectInfo(
    name="trino",
    quote_char='"',
    system_schemas=("information_schema",),
    list_tables_query="""
        SELECT table_schema as schema, table_name as name, table_type as type
        FROM information_schema.tables
        WHERE table_schema NOT IN ({excluded_schemas})
        ORDER BY table_schema, table_name
    """,
    list_columns_query="""
        SELECT column_name, data_type, is_nullable
        FROM information_schema.columns
        WHERE table_schema = '{schema}' AND table_name = '{table}'
        ORDER BY ordinal_position
    """,
)

_TERADATA = DialectInfo(
    name="teradata",
    quote_char='"',
    system_schemas=("DBC", "SYSLIB", "SystemFE", "SYSUDTLIB"),
    list_tables_query="""
        SELECT DatabaseName as schema, TableName as name, TableKind as type
        FROM DBC.TablesV
        WHERE DatabaseName NOT IN ({excluded_schemas})
        AND TableKind = 'T'
        ORDER BY DatabaseName, TableName
    """,
    list_columns_query="""
        SELECT ColumnName as column_name, ColumnType as data_type,
               CASE WHEN Nullable = 'Y' THEN 'YES' ELSE 'NO' END as is_nullable
        FROM DBC.ColumnsV
        WHERE DatabaseName = '{schema}' AND TableName = '{table}'
        ORDER BY ColumnId
    """,
)

_GENERIC = DialectInfo(
    name="",  # Empty string means no dialect-specific parsing in SQLGlot
    quote_char='"',
    system_schemas=(),
    list_tables_query="""
        SELECT table_schema as schema, table_name as name, table_type as type
        FROM information_schema.tables
        WHERE table_type IN ('BASE TABLE', 'TABLE')
        ORDER BY table_schema, table_name
    """,
    list_columns_query="""
        SELECT column_name, data_type, is_nullable
        FROM information_schema.columns
        WHERE table_schema = '{schema}' AND table_name = '{table}'
        ORDER BY ordinal_position
    """,
)

# =============================================================================
# Dialect Registry
# =============================================================================

DIALECTS: dict[str, DialectInfo] = {
    "postgresql": _POSTGRESQL,
    "postgres": _POSTGRESQL,
    "mysql": _MYSQL,
    "mariadb": _MYSQL,
    "snowflake": _SNOWFLAKE,
    "bigquery": _BIGQUERY,
    "redshift": _REDSHIFT,
    "tsql": _TSQL,
    "sqlserver": _TSQL,
    "mssql": _TSQL,
    "synapse": _TSQL,
    "oracle": _ORACLE,
    "databricks": _DATABRICKS,
    "spark": _SPARK,
    "sparksql": _SPARK,
    "presto": _PRESTO,
    "athena": _ATHENA,
    "trino": _TRINO,
    "teradata": _TERADATA,
    "generic": _GENERIC,
}

# Mapping from DSS connection types to dialect names (SQLGlot dialect names)
DSS_CONNECTION_TYPE_TO_DIALECT: dict[str, str] = {
    "PostgreSQL": "postgres",
    "MySQL": "mysql",
    "MariaDB": "mysql",
    "Snowflake": "snowflake",
    "BigQuery": "bigquery",
    "Redshift": "redshift",
    "SQLServer": "tsql",
    "AzureSQLDW": "tsql",
    "Synapse": "tsql",
    "Oracle": "oracle",
    "Databricks": "databricks",
    "Spark": "spark",
    "Athena": "athena",
    "Presto": "presto",
    "Trino": "trino",
    "Teradata": "teradata",
    "Vertica": "postgres",
    "Greenplum": "postgres",
    "Netezza": "postgres",
    "SAPHANA": "generic",
}

DSS_CONNECTION_TYPE_TO_DIALECT_LOWER: dict[str, str] = {
    key.lower(): value for key, value in DSS_CONNECTION_TYPE_TO_DIALECT.items()
}

FUZZY_DIALECT_HINTS: tuple[tuple[str, str], ...] = (
    ("postgres", "postgres"),
    ("mysql", "mysql"),
    ("mariadb", "mysql"),
    ("snowflake", "snowflake"),
    ("bigquery", "bigquery"),
    ("redshift", "redshift"),
    ("sqlserver", "tsql"),
    ("mssql", "tsql"),
    ("synapse", "tsql"),
    ("azuresql", "tsql"),
    ("oracle", "oracle"),
    ("databricks", "databricks"),
    ("spark", "spark"),
    ("athena", "athena"),
    ("presto", "presto"),
    ("trino", "trino"),
    ("teradata", "teradata"),
)


# =============================================================================
# Public API
# =============================================================================


def get_dialect_info(dialect: str) -> DialectInfo:
    """
    Get dialect configuration by name.

    Args:
        dialect: Dialect name (e.g., "postgres", "postgresql", "bigquery").
            Both "postgres" and "postgresql" are accepted and return the same info.

    Returns:
        DialectInfo with quoting rules, system schemas, and query templates.
        The DialectInfo.name field contains the SQLGlot-compatible name.
        Returns generic dialect if not found.

    """
    return DIALECTS.get(dialect.lower(), _GENERIC)


def get_dialect_for_connection_type(connection_type: str) -> str:
    """
    Map a DSS connection type to a SQLGlot dialect name.

    Args:
        connection_type: DSS connection type (e.g., "PostgreSQL", "BigQuery")

    Returns:
        SQLGlot dialect name (e.g., "postgres", "bigquery").
        Returns "generic" if not found.

    """
    return DSS_CONNECTION_TYPE_TO_DIALECT.get(connection_type, "generic")


def get_dialect_for_connection_type_fuzzy(connection_type: str) -> str:
    """
    Map a DSS connection type to a SQLGlot dialect name using fuzzy matching.

    This handles cases where the connection type is lowercase or partial
    (e.g., "postgres" instead of "PostgreSQL").

    Args:
        connection_type: DSS connection type string

    Returns:
        SQLGlot dialect name. Returns "generic" if not found.

    """
    if not connection_type:
        return "generic"

    # Try exact match first
    dialect = DSS_CONNECTION_TYPE_TO_DIALECT.get(connection_type)
    if dialect:
        return dialect

    conn_lower = connection_type.lower()
    dialect = DSS_CONNECTION_TYPE_TO_DIALECT_LOWER.get(conn_lower)
    if dialect:
        return dialect

    for hint, mapped in FUZZY_DIALECT_HINTS:
        if hint in conn_lower:
            return mapped

    return "generic"


def quote_identifier(name: str, dialect: str = "postgres") -> str:
    """
    Quote an identifier using the appropriate quoting style for the dialect.

    This is a convenience function that wraps DialectInfo.quote_identifier.

    Args:
        name: The identifier to quote
        dialect: SQLGlot dialect name

    Returns:
        Properly quoted identifier

    """
    return get_dialect_info(dialect).quote_identifier(name)


def add_limit_to_query(sql: str, limit: int, dialect: str = "generic") -> str:
    """
    Add a LIMIT clause to a SQL query in a dialect-aware manner.

    Uses SQLGlot to parse the query and add LIMIT, then transpile to the
    target dialect. This handles dialect differences like:
    - PostgreSQL/MySQL/Snowflake: SELECT ... LIMIT N
    - SQL Server: SELECT TOP N ...
    - Oracle: SELECT ... FETCH FIRST N ROWS ONLY

    Args:
        sql: The SQL query to add LIMIT to
        limit: Maximum number of rows
        dialect: SQLGlot dialect name (e.g., "postgres", "tsql", "snowflake")

    Returns:
        SQL query with LIMIT clause appropriate for the dialect
    """
    import sqlglot

    # Get SQLGlot-compatible dialect name (empty string for generic)
    dialect_name = get_dialect_info(dialect).name or None

    try:
        # Parse, add limit, and transpile back to the target dialect
        return sqlglot.parse_one(sql, dialect=dialect_name).limit(limit).sql(dialect=dialect_name)
    except Exception:
        # Fallback: append LIMIT directly (works for most dialects)
        return f"{sql.rstrip().rstrip(';')} LIMIT {limit}"
