"""MEI011 — SME-Hub indicator dashboard aggregation services.

Provides query helpers used by ``SMEHubIndicatorDashboardView`` to roll up
participant tracking across cohorts, sectors, AIH affiliations, countries
and time periods. Data sources:

* :class:`SMEHubTrackingRecord` — primary fact table (FRSME-MEI001–007).
* :class:`smehub_onboarding.Business` — sector / country dimensions.
* :class:`smehub_onboarding.AIHAffiliation` — primary AIH dimension.
* :class:`smehub_incubation.CohortMember` — cohort dimension.

Pure-Python aggregation kept in one place so the view stays thin and tests
can hit the aggregator directly with synthetic fixtures.
"""
from __future__ import annotations

import datetime as _dt
from decimal import Decimal
from typing import Literal

from django.db.models import Count, Q, Sum

from apps.mel.tracking.models import SMEHubTrackingRecord
from apps.smehub.onboarding.models import AIHAffiliation, Business

Dimension = Literal["cohort", "sector", "aih", "country", "month"]
DIMENSIONS = ("cohort", "sector", "aih", "country", "month")


def _base_queryset(
    *,
    cohort: int | None = None,
    sector: str | None = None,
    aih: int | None = None,
    country: str | None = None,
    period_start: _dt.date | None = None,
    period_end: _dt.date | None = None,
):
    """Filtered SMEHubTrackingRecord queryset shared by overview + drill-down.

    Keeps the queryset minimal — callers add ``.only()`` / ``.values()``
    / ``.select_related()`` per their access pattern.
    """
    qs = SMEHubTrackingRecord.objects.all()
    if sector:
        qs = qs.filter(business__sector=sector)
    if country:
        qs = qs.filter(business__country=country)
    if aih:
        qs = qs.filter(business__affiliations__institution_id=aih, business__affiliations__is_primary=True)
    if cohort:
        qs = qs.filter(entrepreneur__cohort_memberships__cohort_id=cohort)
    if period_start:
        qs = qs.filter(created_at__date__gte=period_start)
    if period_end:
        qs = qs.filter(created_at__date__lte=period_end)
    return qs.distinct()


def smehub_metrics_overview(
    *,
    cohort: int | None = None,
    sector: str | None = None,
    aih: int | None = None,
    country: str | None = None,
    period_start: _dt.date | None = None,
    period_end: _dt.date | None = None,
) -> dict:
    """Return headline KPIs for the SME-Hub indicator dashboard.

    Returns keys: ``total_entrepreneurs``, ``with_baseline``, ``baseline_pct``,
    ``with_partnerships``, ``stalled``, ``stalled_pct``,
    ``total_funding_secured`` (Decimal), ``partnerships_logged``,
    ``commercialisation_events``, ``investment_events``.
    """
    qs = _base_queryset(
        cohort=cohort, sector=sector, aih=aih, country=country,
        period_start=period_start, period_end=period_end,
    )
    agg = qs.aggregate(
        total_entrepreneurs=Count("pk"),
        with_baseline=Count("pk", filter=Q(sp1_baseline_at__isnull=False)),
        stalled=Count("pk", filter=Q(is_stalled=True)),
        total_funding_secured=Sum("funding_secured_total"),
    )

    total = agg["total_entrepreneurs"] or 0
    funding = agg["total_funding_secured"] or Decimal("0")

    # Count derived from JSON lists — done in Python because JSON length
    # aggregation differs by database backend.
    partnerships_logged = 0
    commercialisation_events = 0
    investment_events = 0
    with_partnerships = 0
    for record in qs.only(
        "pk", "sp3_partnerships", "sp4_commercialisation", "sp5_investment",
    ):
        partnerships = len(record.sp3_partnerships or [])
        partnerships_logged += partnerships
        if partnerships:
            with_partnerships += 1
        commercialisation_events += len(record.sp4_commercialisation or [])
        investment_events += len(record.sp5_investment or [])

    def _pct(numerator: int) -> float:
        return round((numerator / total) * 100, 1) if total else 0.0

    # FRSME-MEI013 — jobs created (latest employment snapshot minus baseline,
    # clamped per record), aggregated across the filtered portfolio.
    from apps.mel.tracking.smehub_employment import compute_total_jobs_created

    jobs_created = compute_total_jobs_created(qs)

    return {
        "total_entrepreneurs": total,
        "with_baseline": agg["with_baseline"] or 0,
        "baseline_pct": _pct(agg["with_baseline"] or 0),
        "with_partnerships": with_partnerships,
        "with_partnerships_pct": _pct(with_partnerships),
        "stalled": agg["stalled"] or 0,
        "stalled_pct": _pct(agg["stalled"] or 0),
        "total_funding_secured": funding,
        "jobs_created": jobs_created,
        "partnerships_logged": partnerships_logged,
        "commercialisation_events": commercialisation_events,
        "investment_events": investment_events,
    }


def _by_sector(qs) -> list[dict]:
    rows = (
        qs.values("business__sector")
        .annotate(
            entrepreneurs=Count("pk", distinct=True),
            funding=Sum("funding_secured_total"),
            stalled=Count("pk", filter=Q(is_stalled=True), distinct=True),
        )
        .order_by("-entrepreneurs")
    )
    return [
        {
            "label": (row["business__sector"] or "—"),
            "entrepreneurs": row["entrepreneurs"] or 0,
            "funding": row["funding"] or Decimal("0"),
            "stalled": row["stalled"] or 0,
        }
        for row in rows
    ]


def _by_country(qs) -> list[dict]:
    rows = (
        qs.values("business__country")
        .annotate(
            entrepreneurs=Count("pk", distinct=True),
            funding=Sum("funding_secured_total"),
            stalled=Count("pk", filter=Q(is_stalled=True), distinct=True),
        )
        .order_by("-entrepreneurs")
    )
    return [
        {
            "label": row["business__country"] or "—",
            "entrepreneurs": row["entrepreneurs"] or 0,
            "funding": row["funding"] or Decimal("0"),
            "stalled": row["stalled"] or 0,
        }
        for row in rows
    ]


def _by_cohort(qs) -> list[dict]:
    rows = (
        qs.values(
            "entrepreneur__cohort_memberships__cohort__pk",
            "entrepreneur__cohort_memberships__cohort__name",
            "entrepreneur__cohort_memberships__cohort__programme__title",
        )
        .annotate(
            entrepreneurs=Count("pk", distinct=True),
            funding=Sum("funding_secured_total"),
            stalled=Count("pk", filter=Q(is_stalled=True), distinct=True),
        )
        .order_by("-entrepreneurs")
    )
    out: list[dict] = []
    for row in rows:
        cohort_pk = row["entrepreneur__cohort_memberships__cohort__pk"]
        if cohort_pk is None:
            continue  # entrepreneurs without a cohort
        out.append(
            {
                "label": f"{row['entrepreneur__cohort_memberships__cohort__programme__title']} — {row['entrepreneur__cohort_memberships__cohort__name']}",
                "entrepreneurs": row["entrepreneurs"] or 0,
                "funding": row["funding"] or Decimal("0"),
                "stalled": row["stalled"] or 0,
            }
        )
    return out


def _by_aih(qs) -> list[dict]:
    rows = (
        qs.filter(business__affiliations__is_primary=True)
        .values(
            "business__affiliations__institution__pk",
            "business__affiliations__institution__name",
        )
        .annotate(
            entrepreneurs=Count("pk", distinct=True),
            funding=Sum("funding_secured_total"),
            stalled=Count("pk", filter=Q(is_stalled=True), distinct=True),
        )
        .order_by("-entrepreneurs")
    )
    return [
        {
            "label": row["business__affiliations__institution__name"] or "—",
            "entrepreneurs": row["entrepreneurs"] or 0,
            "funding": row["funding"] or Decimal("0"),
            "stalled": row["stalled"] or 0,
        }
        for row in rows
        if row["business__affiliations__institution__pk"] is not None
    ]


def _by_month(qs) -> list[dict]:
    """Bucket by year-month of last_milestone_at (falls back to created_at)."""
    from collections import defaultdict

    buckets: dict[str, dict] = defaultdict(lambda: {"entrepreneurs": 0, "funding": Decimal("0"), "stalled": 0})
    for record in qs.only("pk", "last_milestone_at", "created_at", "funding_secured_total", "is_stalled"):
        ts = record.last_milestone_at or record.created_at
        key = ts.strftime("%Y-%m") if ts else "—"
        buckets[key]["entrepreneurs"] += 1
        buckets[key]["funding"] += record.funding_secured_total or Decimal("0")
        if record.is_stalled:
            buckets[key]["stalled"] += 1
    return [
        {"label": label, **values}
        for label, values in sorted(buckets.items())
    ]


_DIMENSION_BUILDERS = {
    "sector": _by_sector,
    "country": _by_country,
    "cohort": _by_cohort,
    "aih": _by_aih,
    "month": _by_month,
}


def smehub_metrics_by_dimension(
    dimension: Dimension,
    *,
    cohort: int | None = None,
    sector: str | None = None,
    aih: int | None = None,
    country: str | None = None,
    period_start: _dt.date | None = None,
    period_end: _dt.date | None = None,
) -> list[dict]:
    """Drill-down rows for ``dimension``. Filters are AND-combined with the
    primary filter (e.g. a sector filter narrows the cohort drill-down).
    """
    if dimension not in _DIMENSION_BUILDERS:
        raise ValueError(f"Unknown dimension: {dimension!r}")
    qs = _base_queryset(
        cohort=cohort, sector=sector, aih=aih, country=country,
        period_start=period_start, period_end=period_end,
    )
    return _DIMENSION_BUILDERS[dimension](qs)


def filter_options() -> dict[str, list[tuple]]:
    """Return distinct values for each filter so the form can render selects.

    Cohort + AIH return ``(pk, label)`` tuples. Sector + country are plain
    string lists (deduped).
    """
    from apps.smehub.incubation.models import Cohort

    sectors = sorted({s for s in Business.objects.values_list("sector", flat=True).distinct() if s})
    countries = sorted({c for c in Business.objects.values_list("country", flat=True).distinct() if c})
    cohorts = list(
        Cohort.objects.select_related("programme")
        .order_by("-start_date")
        .values_list("pk", "name", "programme__title")
    )
    aihs = list(
        AIHAffiliation.objects.filter(is_primary=True)
        .values_list("institution_id", "institution__name")
        .distinct()
        .order_by("institution__name")
    )
    return {
        "sectors": sectors,
        "countries": countries,
        "cohorts": [(pk, f"{programme} — {name}") for pk, name, programme in cohorts],
        "aihs": [(pk, name) for pk, name in aihs if name],
    }