import logging

from flask import Blueprint, jsonify, request

from semantic_models.exceptions import ValidationError

logger = logging.getLogger(__name__)

bp = Blueprint("dss_resources", __name__, url_prefix="/api/dss")

# https://developer.dataiku.com/latest/api-reference/python/datasets.html
SQL_DATASET_TYPES = {
    "PostgreSQL",
    "MySQL",
    "Snowflake",
    "BigQuery",
    "SQLServer",
    "Oracle",
    "Redshift",
    "Synapse",
    "Teradata",
    "Vertica",
    "Greenplum",
    "JDBC",
    "Athena",
    "Databricks",
    "SAPHANA",
    "Netezza",
    "hiveserver2",
}


@bp.route("/projects", methods=["GET"])
def list_projects():
    try:
        import dataiku

        client = dataiku.api_client()
        projects = []
        for project in client.list_projects():
            projects.append(
                {
                    "key": project["projectKey"],
                    "name": project.get("name", project["projectKey"]),
                }
            )

        return jsonify(projects)

    except Exception:
        logger.exception("Failed to list projects")
        raise


# =============================================================================
# Datasets
# =============================================================================


@bp.route("/datasets/<project_key>/list", methods=["GET"])
def list_datasets(project_key: str):
    try:
        import dataiku

        client = dataiku.api_client()
        project = client.get_project(project_key)

        datasets = []
        for dataset in project.list_datasets():
            dataset_type = dataset.get("type", "Unknown")

            # Only include SQL-compatible datasets
            if dataset_type not in SQL_DATASET_TYPES:
                continue

            ds = project.get_dataset(dataset["name"])
            try:
                schema = ds.get_schema()
                columns = schema.get("columns", [])
            except Exception:
                columns = []

            datasets.append(
                {
                    "name": dataset["name"],
                    "type": dataset_type,
                    "schema": [{"name": col["name"], "type": col.get("type", "string")} for col in columns],
                }
            )

        return jsonify(datasets)

    except Exception:
        logger.exception("Failed to list datasets")
        raise


@bp.route("/datasets/<project_key>/<dataset_name>/schema", methods=["GET"])
def get_dataset_schema(project_key: str, dataset_name: str):
    try:
        import dataiku

        client = dataiku.api_client()
        project = client.get_project(project_key)
        dataset = project.get_dataset(dataset_name)

        # Get schema with column comments
        schema = dataset.get_schema()

        # Get dataset metadata for description
        try:
            metadata = dataset.get_metadata()
            dataset_description = metadata.get("description", "")
            dataset_label = metadata.get("label", "")
        except Exception:
            dataset_description = ""
            dataset_label = ""

        return jsonify(
            {
                "description": dataset_description,
                "label": dataset_label,
                "columns": [
                    {
                        "name": col["name"],
                        "type": col.get("type", "string"),
                        "comment": col.get("comment") or "",
                    }
                    for col in schema.get("columns", [])
                ],
            }
        )

    except Exception:
        logger.exception("Failed to get dataset schema")
        raise


@bp.route("/column-values", methods=["GET", "POST"])
def fetch_column_values():
    try:
        data = request.get_json(silent=True) or {}
        if request.method == "GET":
            data = {
                "semantic_model_id": request.args.get("semantic_model_id"),
                "version_id": request.args.get("version_id"),
                "entity": request.args.get("entity"),
                "attribute": request.args.get("attribute"),
                "limit": request.args.get("limit"),
            }
        if not data:
            raise ValidationError("Request body is required")

        semantic_model_id = data.get("semantic_model_id")
        version_id = data.get("version_id")
        entity = data.get("entity")
        attribute = data.get("attribute")
        limit = data.get("limit", 5)

        # Validate limit parameter
        if isinstance(limit, str) and limit.isdigit():
            limit = int(limit)
        if not isinstance(limit, int) or limit < 1 or limit > 10000:
            raise ValidationError("limit must be an integer between 1 and 10000")

        if not semantic_model_id:
            raise ValidationError("semantic_model_id is required")
        if not version_id:
            raise ValidationError("version_id is required")
        if not entity:
            raise ValidationError("entity is required")
        if not attribute:
            raise ValidationError("attribute is required")

        import dataiku

        client = dataiku.api_client()
        project = client.get_default_project()
        sm = project.get_semantic_model(semantic_model_id)
        version = sm.get_version(version_id)
        result = version.get_basic_distinct_values_for_attribute(
            entity=entity,
            attribute=attribute,
            max_values=limit + 1,
        )
        raw_values = (result or {}).get("values", []) or []
        values = ["" if v is None else str(v) for v in raw_values]

        # Check if truncated
        truncated = len(values) > limit
        if truncated:
            values = values[:limit]

        return jsonify({"values": values, "truncated": truncated})

    except ValidationError:
        raise
    except Exception:
        logger.exception("Failed to fetch column values")
        raise


# =============================================================================
# LLMs
# =============================================================================


@bp.route("/llms", methods=["GET"])
def list_llms():
    """
    List LLMs available in the DSS instance.

    Query parameters:
        purpose: Optional filter for LLM purpose. Values:
            - TEXT_EMBEDDING_EXTRACTION: LLMs suitable for embeddings
            - GENERIC_COMPLETION: LLMs suitable for text generation
            - (empty): All LLMs
    """
    try:
        import dataiku

        client = dataiku.api_client()
        project = client.get_default_project()

        # Get optional purpose filter from query params
        purpose = request.args.get("purpose")

        llms = []
        # Only pass purpose if specified, otherwise call without it
        llm_list = project.list_llms(purpose=purpose) if purpose else project.list_llms()
        for llm in llm_list:
            if llm.get("type") == "SAVED_MODEL_AGENT":
                continue
            llms.append(
                {
                    "id": llm["id"],
                    "label": llm.get("friendlyNameShort", llm.get("friendlyName", "")),
                    "type": llm.get("type", ""),
                    "connection": llm.get("connection"),
                }
            )

        return jsonify(llms)

    except Exception:
        logger.exception("Failed to list LLMs")
        raise


# Common aggregation functions by SQL dialect
# TEMPORARY for demo, this will be removed
SQL_AGGREGATIONS = {
    "standard": [
        {"name": "COUNT", "syntax": "COUNT(column)", "description": "Count rows"},
        {"name": "SUM", "syntax": "SUM(column)", "description": "Sum of values"},
        {"name": "AVG", "syntax": "AVG(column)", "description": "Average of values"},
        {"name": "MIN", "syntax": "MIN(column)", "description": "Minimum value"},
        {"name": "MAX", "syntax": "MAX(column)", "description": "Maximum value"},
        {"name": "COUNT DISTINCT", "syntax": "COUNT(DISTINCT column)", "description": "Count unique values"},
    ],
    "PostgreSQL": [
        {"name": "STDDEV", "syntax": "STDDEV(column)", "description": "Standard deviation"},
        {"name": "VARIANCE", "syntax": "VARIANCE(column)", "description": "Variance"},
        {
            "name": "PERCENTILE_CONT",
            "syntax": "PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY column)",
            "description": "Percentile",
        },
        {"name": "STRING_AGG", "syntax": "STRING_AGG(column, ',')", "description": "Concatenate strings"},
        {"name": "ARRAY_AGG", "syntax": "ARRAY_AGG(column)", "description": "Aggregate into array"},
    ],
    "Snowflake": [
        {"name": "STDDEV", "syntax": "STDDEV(column)", "description": "Standard deviation"},
        {"name": "VARIANCE", "syntax": "VARIANCE(column)", "description": "Variance"},
        {"name": "MEDIAN", "syntax": "MEDIAN(column)", "description": "Median value"},
        {
            "name": "PERCENTILE_CONT",
            "syntax": "PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY column)",
            "description": "Percentile",
        },
        {"name": "LISTAGG", "syntax": "LISTAGG(column, ',')", "description": "Concatenate strings"},
        {
            "name": "APPROX_COUNT_DISTINCT",
            "syntax": "APPROX_COUNT_DISTINCT(column)",
            "description": "Approximate distinct count",
        },
    ],
    "BigQuery": [
        {"name": "STDDEV", "syntax": "STDDEV(column)", "description": "Standard deviation"},
        {"name": "VARIANCE", "syntax": "VARIANCE(column)", "description": "Variance"},
        {"name": "PERCENTILE_CONT", "syntax": "PERCENTILE_CONT(column, 0.5)", "description": "Percentile"},
        {"name": "STRING_AGG", "syntax": "STRING_AGG(column, ',')", "description": "Concatenate strings"},
        {"name": "ARRAY_AGG", "syntax": "ARRAY_AGG(column)", "description": "Aggregate into array"},
        {
            "name": "APPROX_COUNT_DISTINCT",
            "syntax": "APPROX_COUNT_DISTINCT(column)",
            "description": "Approximate distinct count",
        },
    ],
    "MySQL": [
        {"name": "STDDEV", "syntax": "STDDEV(column)", "description": "Standard deviation"},
        {"name": "VARIANCE", "syntax": "VARIANCE(column)", "description": "Variance"},
        {"name": "GROUP_CONCAT", "syntax": "GROUP_CONCAT(column)", "description": "Concatenate strings"},
    ],
    "SQLServer": [
        {"name": "STDEV", "syntax": "STDEV(column)", "description": "Standard deviation"},
        {"name": "VAR", "syntax": "VAR(column)", "description": "Variance"},
        {"name": "STRING_AGG", "syntax": "STRING_AGG(column, ',')", "description": "Concatenate strings"},
        {
            "name": "PERCENTILE_CONT",
            "syntax": "PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY column) OVER ()",
            "description": "Percentile",
        },
    ],
}


@bp.route("/aggregations", methods=["GET"])
def list_aggregations():
    """List available SQL aggregation functions."""
    return jsonify(
        {
            "standard": SQL_AGGREGATIONS["standard"],
            "dialects": list(SQL_AGGREGATIONS.keys()),
        }
    )


@bp.route("/aggregations/<dialect>", methods=["GET"])
def list_aggregations_by_dialect(dialect: str):
    """List aggregation functions for a specific SQL dialect."""
    # Get standard aggregations
    aggregations = SQL_AGGREGATIONS["standard"].copy()

    # Add dialect-specific aggregations if available
    if dialect in SQL_AGGREGATIONS:
        aggregations.extend(SQL_AGGREGATIONS[dialect])

    return jsonify(aggregations)
