"""DSS-specific utilities for dataset and connection resolution."""

import logging
from dataclasses import dataclass
from typing import Optional

from semantic_models.exceptions import ValidationError
from semantic_models.utils import (
    add_limit_to_query,
    get_dialect_for_connection_type_fuzzy,
    quote_identifier,
    safe_identifier,
)

logger = logging.getLogger(__name__)

CATEGORICAL_TYPE_PREFIXES = (
    "string",
    "varchar",
    "char",
    "text",
    "nvarchar",
    "nchar",
)


def is_categorical_type(column_type: str) -> bool:
    """Check if a column type is categorical (string-like)."""
    return any(column_type.lower().startswith(p) for p in CATEGORICAL_TYPE_PREFIXES)


@dataclass
class DatasetLocationInfo:
    """Resolved SQL location info from a DSS dataset."""

    connection: str
    full_table: str  # Quoted and fully qualified (quotedResolvedTableName)
    dialect: str  # SQLGlot dialect (normalized via get_dialect_for_connection_type_fuzzy)
    schema_name: Optional[str] = None
    table_name: Optional[str] = None
    catalog: Optional[str] = None


def get_dataset_location_info(project_key: str, dataset_name: str) -> DatasetLocationInfo:
    """
    Get SQL location info from a DSS dataset.

    Validates locationInfoType == "SQL" and normalizes dialect using fuzzy matching.

    Raises ValidationError if dataset is not SQL-backed.
    """
    import dataiku

    full_dataset_name = f"{project_key}.{dataset_name}"
    ds = dataiku.Dataset(full_dataset_name)
    location_info = ds.get_location_info()

    if location_info.get("locationInfoType") != "SQL":
        raise ValidationError(
            f"Dataset '{full_dataset_name}' is not SQL-backed (type={location_info.get('locationInfoType')})"
        )

    info = location_info.get("info", {})
    connection = info.get("connectionName")
    if not connection:
        raise ValidationError(f"Dataset '{full_dataset_name}' has no SQL connection")

    full_table = info.get("quotedResolvedTableName")
    if not full_table:
        raise ValidationError(f"Dataset '{full_dataset_name}' has no quotedResolvedTableName")

    db_type = info.get("databaseType") or "generic"
    dialect = get_dialect_for_connection_type_fuzzy(db_type)

    return DatasetLocationInfo(
        connection=connection,
        full_table=full_table,
        dialect=dialect,
        schema_name=info.get("schema"),
        table_name=info.get("table"),
        catalog=info.get("catalog"),
    )


def get_connection_dialect(connection_name: str) -> str:
    """Get SQLGlot dialect for a DSS connection (uses fuzzy matching)."""
    import dataiku

    try:
        client = dataiku.api_client()
        conn = client.get_connection(connection_name)
        conn_type = conn.get_info().get("type", "")
        return get_dialect_for_connection_type_fuzzy(conn_type)
    except Exception as e:
        logger.warning("Failed to resolve dialect for connection '%s': %s", connection_name, e)
        return "generic"


def build_distinct_values_query(
    quoted_column: str,
    full_table: str,
    dialect: str,
    limit: int,
) -> str:
    """
    Build SELECT DISTINCT query for sample values.

    The quoted_column must be pre-validated and pre-quoted by the caller.
    """
    sql = f"SELECT DISTINCT {quoted_column} FROM {full_table} WHERE {quoted_column} IS NOT NULL"
    return add_limit_to_query(sql, limit, dialect)


def _stringify_distinct_values(values: list, max_value_length: Optional[int]) -> list[str]:
    result: list[str] = []
    for val in values:
        if val is None:
            continue
        str_val = str(val)
        if max_value_length and len(str_val) > max_value_length:
            str_val = str_val[:max_value_length] + "..."
        result.append(str_val)
    return result


def fetch_distinct_values(
    *,
    connection: str,
    full_table: str,
    column_name: str,
    dialect: str,
    limit: int,
    max_value_length: Optional[int] = None,
    validate_column: bool = True,
) -> list[str]:
    """
    Fetch distinct, non-null values for a column from a SQL table.

    Uses the provided connection to run the DISTINCT query.
    """
    if validate_column:
        column_name = safe_identifier(column_name, "column")

    quoted_column = quote_identifier(column_name, dialect)
    sql = build_distinct_values_query(quoted_column, full_table, dialect, limit)

    import dataiku

    executor = dataiku.core.sql.SQLExecutor2(connection=connection)
    result = executor.query_to_df(sql)
    return _stringify_distinct_values(result.iloc[:, 0].tolist(), max_value_length)
