from __future__ import annotations

from collections import defaultdict
from datetime import datetime, timezone
from typing import Any


ACTIVITY_METRIC_KEYS = (
    "slow_count",
    "total_latency_s",
    "max_latency_s",
    "avg_latency_s",
    "max_latency_ms",
    "request_count",
    "sum_ru",
)

IGNORED_METRIC_KEYS = frozenset(
    {
        "latest_time",
        "result_row_count",
        "dimension_count",
    }
)

METRIC_DEFINITIONS: dict[str, dict[str, str]] = {
    "slow_count": {
        "label": "Slow queries",
        "unit": "queries",
        "format": "integer",
        "aggregation": "sum",
        "description": "Slow executions observed in each source inspection window.",
    },
    "total_latency_s": {
        "label": "Total latency",
        "unit": "seconds",
        "format": "duration_seconds",
        "aggregation": "sum",
        "description": "Accumulated execution latency in each source inspection window.",
    },
    "max_latency_s": {
        "label": "Max latency",
        "unit": "seconds",
        "format": "duration_seconds",
        "aggregation": "max",
        "description": "Worst observed execution latency in each source inspection window.",
    },
    "avg_latency_s": {
        "label": "Average latency",
        "unit": "seconds",
        "format": "duration_seconds",
        "aggregation": "average",
        "description": "Average observed execution latency in each source inspection window.",
    },
    "max_latency_ms": {
        "label": "Max latency",
        "unit": "milliseconds",
        "format": "duration_milliseconds",
        "aggregation": "max",
        "description": "Worst observed execution latency in each source inspection window.",
    },
    "total_keys": {
        "label": "Total keys",
        "unit": "keys",
        "format": "compact",
        "aggregation": "sum",
        "description": "Keys scanned by the SQL during each source inspection window.",
    },
    "process_keys": {
        "label": "Processed keys",
        "unit": "keys",
        "format": "compact",
        "aggregation": "sum",
        "description": "Keys processed by the SQL during each source inspection window.",
    },
    "request_count": {
        "label": "KV requests",
        "unit": "requests",
        "format": "compact",
        "aggregation": "sum",
        "description": "KV requests attributed to the SQL in each source inspection window.",
    },
    "sum_ru": {
        "label": "Resource units",
        "unit": "RU",
        "format": "compact",
        "aggregation": "sum",
        "description": "Resource units attributed to the SQL in each source inspection window.",
    },
}

METRIC_AGGREGATION_OVERRIDES = {
    "affected_instances": "max",
    "instances": "max",
}


def _datetime(value: str | None) -> datetime:
    try:
        result = datetime.fromisoformat((value or "").replace("Z", "+00:00"))
    except (TypeError, ValueError):
        return datetime.min.replace(tzinfo=timezone.utc)
    if result.tzinfo is None:
        result = result.replace(tzinfo=timezone.utc)
    return result.astimezone(timezone.utc)


def _label(value: str) -> str:
    return value.replace("_", " ").strip().title()


def _numeric_metrics(value: Any) -> dict[str, int | float]:
    if not isinstance(value, dict):
        return {}
    result: dict[str, int | float] = {}
    for key, item in value.items():
        if isinstance(item, bool) or not isinstance(item, (int, float)):
            continue
        result[str(key)] = item
    return result


def metric_aggregation(key: str) -> str:
    definition = METRIC_DEFINITIONS.get(key)
    if definition and definition.get("aggregation"):
        return definition["aggregation"]
    if key in METRIC_AGGREGATION_OVERRIDES:
        return METRIC_AGGREGATION_OVERRIDES[key]

    normalized = key.lower()
    if normalized.startswith("max_") or "_max_" in normalized:
        return "max"
    if normalized.startswith("avg_") or "_avg_" in normalized:
        return "average"
    if normalized.endswith(("_percent", "_percentage", "_ratio", "_share")):
        return "max"
    if (
        normalized.endswith(("_count", "_counts", "_windows", "_events", "_seconds"))
        or normalized.startswith(("count_", "total_", "sum_"))
        or "_total_" in normalized
    ):
        return "sum"
    return "latest"


def aggregate_metric_values(key: str, values: list[float]) -> float | None:
    if not values:
        return None
    aggregation = metric_aggregation(key)
    if aggregation == "sum":
        return sum(values)
    if aggregation == "max":
        return max(values)
    if aggregation == "average":
        return sum(values) / len(values)
    return values[-1]


def _metric_definition(key: str) -> dict[str, str]:
    fallback_format = "percent" if key.lower().endswith(("_percent", "_percentage")) else "number"
    fallback_unit = "%" if fallback_format == "percent" else ""
    return {
        "key": key,
        **METRIC_DEFINITIONS.get(
            key,
            {
                "label": _label(key),
                "unit": fallback_unit,
                "format": fallback_format,
                "aggregation": metric_aggregation(key),
                "description": "Numeric evidence supplied by the Finding source.",
            },
        ),
    }


def project_catalog_metric_history(
    findings: list[dict[str, Any]],
    symptom_names: dict[str, str] | None = None,
) -> dict[str, Any]:
    names = symptom_names or {}
    ordered = sorted(
        findings,
        key=lambda finding: (
            _datetime(finding.get("observed_at") or finding.get("created_at")),
            finding.get("id") or "",
        ),
    )
    points: list[dict[str, Any]] = []
    available_keys: set[str] = set()
    symptom_metric_keys: dict[str, set[str]] = defaultdict(set)
    symptom_occurrences: dict[str, int] = defaultdict(int)

    for finding in ordered:
        values = _numeric_metrics(finding.get("metrics"))
        if not values:
            continue
        available_keys.update(values)
        symptom_codes: list[str] = []
        for symptom in finding.get("symptoms") or []:
            if not isinstance(symptom, dict) or not symptom.get("code"):
                continue
            code = str(symptom["code"])
            symptom_codes.append(code)
            symptom_occurrences[code] += 1
            for key in symptom.get("metric_keys") or []:
                metric_key = str(key)
                if metric_key in values:
                    symptom_metric_keys[code].add(metric_key)
        points.append(
            {
                "finding_id": finding["id"],
                "observed_at": finding.get("observed_at") or finding.get("created_at"),
                "window_start": finding.get("time_window_start"),
                "window_end": finding.get("time_window_end"),
                "source": finding.get("source"),
                "severity": finding.get("severity"),
                "symptom_codes": sorted(set(symptom_codes)),
                "values": values,
            }
        )

    groups: list[dict[str, Any]] = []
    used_keys: set[str] = set()
    activity_keys = [key for key in ACTIVITY_METRIC_KEYS if key in available_keys]
    if activity_keys:
        groups.append(
            {
                "id": "activity",
                "kind": "activity",
                "title": "SQL activity",
                "description": "General workload and latency evidence across Finding observation windows.",
                "symptom_code": None,
                "metric_keys": activity_keys,
            }
        )
        used_keys.update(activity_keys)

    for code in sorted(
        symptom_metric_keys,
        key=lambda item: (-symptom_occurrences[item], names.get(item, item)),
    ):
        keys = sorted(
            symptom_metric_keys[code],
            key=lambda key: (
                list(METRIC_DEFINITIONS).index(key)
                if key in METRIC_DEFINITIONS
                else len(METRIC_DEFINITIONS),
                key,
            ),
        )
        if not keys:
            continue
        groups.append(
            {
                "id": f"symptom:{code}",
                "kind": "symptom",
                "title": names.get(code) or _label(code),
                "description": f"Metrics explicitly bound to the {code} symptom by the Finding source.",
                "symptom_code": code,
                "metric_keys": keys,
            }
        )
        used_keys.update(keys)

    additional_keys = sorted(available_keys - used_keys - IGNORED_METRIC_KEYS)
    if additional_keys:
        groups.append(
            {
                "id": "source-evidence",
                "kind": "source",
                "title": "Additional source evidence",
                "description": "Other numeric evidence supplied by the source metric schema.",
                "symptom_code": None,
                "metric_keys": additional_keys,
            }
        )
        used_keys.update(additional_keys)

    metric_keys = sorted(
        used_keys,
        key=lambda key: (
            list(METRIC_DEFINITIONS).index(key)
            if key in METRIC_DEFINITIONS
            else len(METRIC_DEFINITIONS),
            key,
        ),
    )
    return {
        "points": points,
        "groups": groups,
        "definitions": {key: _metric_definition(key) for key in metric_keys},
        "finding_count": len(findings),
        "metric_observation_count": len(points),
        "from": points[0]["observed_at"] if points else None,
        "to": points[-1]["observed_at"] if points else None,
    }