"""Pure functions that consolidate upstream data into report-ready dicts.

Each builder takes a scope (logframe or None) and a period window; returns a
plain dict that is embedded in ``Report.consolidated_data`` and passed to the
PDF / XLSX / HTML renderers. Builders must be pure — no side effects, no
database writes. Optional upstream apps are imported lazily inside a try/except
so the reports module degrades gracefully when those apps aren't installed.
"""
from __future__ import annotations

from datetime import date
from decimal import Decimal
from typing import Any


# ---------------------------------------------------------------------------
# Indicator rollup (RAG + variance)
# ---------------------------------------------------------------------------


def build_indicator_rollup(
    *, logframe, period_label: str, indicator_codes: list[str] | None = None,
) -> dict[str, Any]:
    """Roll up indicators within a logframe: actual vs target and RAG status.

    When ``indicator_codes`` is a non-empty list (from the report builder's
    per-section config), the rollup is narrowed to exactly those indicator
    codes — mirroring the HTMX fragment path in ``services.render_section`` so
    the generated report honours the section configuration (C-D3).
    """
    from apps.mel.indicators.models import Indicator
    from apps.mel.indicators.services import calculate_progress

    # M&E SRS Table 64 — DRAFT indicators are excluded from reporting until
    # published.
    from apps.mel.indicators.models import IndicatorState

    qs = Indicator.objects.filter(is_active=True).exclude(
        workflow_status=IndicatorState.DRAFT
    )
    if logframe is not None:
        qs = qs.filter(logframe_row__logframe=logframe)
    if indicator_codes:
        qs = qs.filter(code__in=indicator_codes)
    qs = qs.select_related("logframe_row")

    rows: list[dict[str, Any]] = []
    counts = {"green": 0, "amber": 0, "red": 0, "unknown": 0}
    for indicator in qs:
        progress = calculate_progress(indicator, period_label)
        rag = str(progress["rag"])
        counts[rag] = counts.get(rag, 0) + 1
        rows.append(
            {
                "code": indicator.code,
                "name": indicator.name,
                "level": indicator.logframe_row.level,
                "unit": indicator.unit,
                "actual": _to_float(progress["actual"]),
                "target": _to_float(progress["target"]),
                "baseline": _to_float(progress["baseline"]),
                "percent": progress["percent"],
                "rag": rag,
            }
        )
    return {"rows": rows, "counts": counts, "period_label": period_label}


def _to_float(value) -> float | None:
    if value is None:
        return None
    try:
        return float(value)
    except (TypeError, ValueError):  # pragma: no cover - defensive
        return None


# ---------------------------------------------------------------------------
# Variance table (FRMFL028–029)
# ---------------------------------------------------------------------------


def build_variance_table(
    *, logframe, period_label: str, indicator_codes: list[str] | None = None,
) -> dict[str, Any]:
    from apps.mel.indicators.models import IndicatorVariance

    qs = IndicatorVariance.objects.filter(period_label=period_label).select_related(
        "indicator__logframe_row__logframe"
    )
    if logframe is not None:
        qs = qs.filter(indicator__logframe_row__logframe=logframe)
    if indicator_codes:
        qs = qs.filter(indicator__code__in=indicator_codes)

    rows = [
        {
            "code": v.indicator.code,
            "name": v.indicator.name,
            "target": _to_float(v.target_value),
            "actual": _to_float(v.actual_value),
            "variance_pct": _to_float(v.variance_pct),
            "cause_analysis": v.cause_analysis,
            "adjustment": v.adjustment,
        }
        for v in qs
    ]
    return {"rows": rows, "period_label": period_label}


# ---------------------------------------------------------------------------
# Activity summary (FRMFL009–013)
# ---------------------------------------------------------------------------


def build_activity_summary(*, logframe, period_start: date, period_end: date) -> dict[str, Any]:
    from apps.mel.tracking.models import Activity

    qs = Activity.objects.filter(
        scheduled_end__gte=period_start,
        scheduled_start__lte=period_end,
    ).select_related("logframe_row")
    if logframe is not None:
        qs = qs.filter(logframe_row__logframe=logframe)

    counts: dict[str, int] = {}
    rows: list[dict[str, Any]] = []
    for act in qs:
        counts[act.status] = counts.get(act.status, 0) + 1
        rows.append(
            {
                "name": act.name,
                "status": act.status,
                "scheduled_start": act.scheduled_start.isoformat(),
                "scheduled_end": act.scheduled_end.isoformat(),
                "actual_start": act.actual_start.isoformat() if act.actual_start else None,
                "actual_end": act.actual_end.isoformat() if act.actual_end else None,
                "deviation_flag": act.deviation_flag,
            }
        )
    return {"rows": rows, "counts": counts}


# ---------------------------------------------------------------------------
# Output deliverables snapshot (FRMFL014–019)
# ---------------------------------------------------------------------------


def build_output_snapshot(*, logframe) -> dict[str, Any]:
    from apps.mel.tracking.models import OutputDeliverable

    qs = OutputDeliverable.objects.select_related("logframe_row")
    if logframe is not None:
        qs = qs.filter(logframe_row__logframe=logframe)

    rows: list[dict[str, Any]] = []
    for d in qs:
        rows.append(
            {
                "title": d.title,
                "unit": d.unit,
                "target_quantity": _to_float(d.target_quantity),
                "achieved_quantity": _to_float(d.achieved_quantity),
                "percent_achieved": d.percent_achieved,
                "status": d.status,
                "due_date": d.due_date.isoformat() if d.due_date else None,
            }
        )
    return {"rows": rows}


# ---------------------------------------------------------------------------
# Finance (optional — graceful if rims.finance is not installed)
# ---------------------------------------------------------------------------


def build_finance_snapshot(*, logframe, period_start: date, period_end: date) -> dict[str, Any]:
    try:  # pragma: no cover - optional upstream
        from apps.rims.finance.models import Budget  # type: ignore
    except Exception:
        return {"installed": False, "rows": []}

    rows: list[dict[str, Any]] = []
    try:
        qs = Budget.objects.all()[:50]
        for b in qs:
            rows.append(
                {
                    "name": getattr(b, "name", str(b)),
                    "amount": str(getattr(b, "amount", "")),
                }
            )
    except Exception:  # pragma: no cover - defensive
        pass
    return {"installed": True, "rows": rows}


# ---------------------------------------------------------------------------
# G3 — Budget vs expenditure variance (LogFrame.finance_budget)
# ---------------------------------------------------------------------------

# RAG threshold: per-category utilisation above this fraction trips RED.
# Whole-budget elapsed-vs-spent gap above this fraction also trips RED.
# 0.15 == 15 percentage points — matches the prompt's "actual > planned by >15%".
BUDGET_VARIANCE_RED_THRESHOLD = 0.15


def _category_rag(utilisation: float, elapsed_ratio: float) -> str:
    """Classify a budget category against pacing.

    GREEN  utilisation is within 5 pp of elapsed pacing.
    AMBER  utilisation is between +5 pp and +15 pp ahead of pacing.
    RED    utilisation exceeds elapsed pacing by more than 15 pp.
    """
    over = utilisation - elapsed_ratio
    if over > BUDGET_VARIANCE_RED_THRESHOLD:
        return "red"
    if over > 0.05:
        return "amber"
    return "green"


def build_budget_variance(
    *,
    logframe,
    today: date | None = None,
) -> dict[str, Any]:
    """Report-section payload for a log frame's bound finance Budget.

    Reuses :func:`apps.rims.finance.services_analytics.compute_variance` and
    :func:`cost_breakdown_by_category` so the report agrees with the donor
    finance dashboard. Returns ``{"installed": False}`` when the logframe has
    no budget bound; ``{"variance": None, ...}`` when the budget exists but
    can't be analysed (missing dates, zero ceiling).
    """
    if logframe is None or getattr(logframe, "finance_budget_id", None) is None:
        return {"installed": False, "categories": [], "variance": None}

    try:  # pragma: no cover - optional upstream
        from apps.rims.finance.services_analytics import (
            CategorySlice,
            cost_breakdown_by_category,
            compute_variance,
        )
    except Exception:
        return {"installed": False, "categories": [], "variance": None}

    budget = logframe.finance_budget
    variance = compute_variance(budget, today=today)
    slices: list[CategorySlice] = list(cost_breakdown_by_category(budget))
    elapsed = variance.elapsed_ratio if variance else 0.0

    categories: list[dict[str, Any]] = []
    flagged = 0
    for s in slices:
        rag = _category_rag(s.utilisation, elapsed)
        if rag == "red":
            flagged += 1
        categories.append(
            {
                "category": s.category,
                "label": s.label,
                "budgeted": _to_float(s.budgeted),
                "expended": _to_float(s.expended),
                "utilisation": s.utilisation,
                "rag": rag,
            }
        )

    return {
        "installed": True,
        "budget_id": budget.pk,
        "budget_name": budget.name,
        "award_id": budget.award_id,
        "currency": budget.currency,
        "rag_threshold_pct": int(BUDGET_VARIANCE_RED_THRESHOLD * 100),
        "flagged_categories": flagged,
        "variance": (
            {
                "elapsed_ratio": variance.elapsed_ratio,
                "spent_ratio": variance.spent_ratio,
                "gap_pct": variance.gap_pct,
                "direction": variance.direction,
                "ceiling": _to_float(variance.ceiling),
                "disbursed": _to_float(variance.disbursed),
            }
            if variance
            else None
        ),
        "categories": categories,
    }


# ---------------------------------------------------------------------------
# Outcome assessment section (FRMFL020–023)
# ---------------------------------------------------------------------------


def build_outcome_section(*, logframe, period_label: str) -> dict[str, Any]:
    """Roll up outcome assessments + their indicator readings for a period."""
    from apps.mel.indicators.models import OutcomeAssessment
    from apps.mel.indicators.services import calculate_progress

    qs = OutcomeAssessment.objects.select_related(
        "logframe_row__logframe", "assessor",
    ).filter(period_label=period_label)
    if logframe is not None:
        qs = qs.filter(logframe_row__logframe=logframe)

    rows: list[dict[str, Any]] = []
    for oa in qs:
        indicators_payload = []
        for ind in oa.logframe_row.indicators.all():
            progress = calculate_progress(ind, period_label)
            indicators_payload.append(
                {
                    "code": ind.code,
                    "name": ind.name,
                    "actual": _to_float(progress["actual"]),
                    "target": _to_float(progress["target"]),
                    "rag": str(progress["rag"]),
                }
            )
        rows.append(
            {
                "outcome_row": oa.logframe_row.title,
                "logframe": oa.logframe_row.logframe.name,
                "period_label": oa.period_label,
                "metrics": oa.metrics or {},
                "contribution_analysis": oa.contribution_analysis,
                "recommendations": oa.recommendations,
                "assessor": str(oa.assessor) if oa.assessor else None,
                "assessed_at": oa.assessed_at.isoformat(),
                "indicators": indicators_payload,
            }
        )
    return {"rows": rows, "period_label": period_label}


# ---------------------------------------------------------------------------
# Impact evaluation section (FRMFL024–027)
# ---------------------------------------------------------------------------


def build_impact_section(*, logframe) -> dict[str, Any]:
    """Surface impact evaluations attached to the scope, newest first.

    Includes the Cohen's d effect size (FRMFL026) when an evaluation has
    enough datapoints to compute it.
    """
    from apps.mel.indicators.models import ImpactEvaluation
    from apps.mel.indicators.services import calculate_cohens_d

    qs = ImpactEvaluation.objects.select_related("logframe", "evaluator")
    if logframe is not None:
        qs = qs.filter(logframe=logframe)
    qs = qs.order_by("-completed_on", "-created_at")

    rows = []
    for ev in qs:
        d = calculate_cohens_d(ev)
        rows.append({
            "title": ev.title,
            "logframe": ev.logframe.name,
            "objectives": ev.objectives,
            "counterfactual": ev.counterfactual,
            "comparison_group": ev.comparison_group,
            "findings": ev.findings,
            "recommendations": ev.recommendations,
            "evaluator": str(ev.evaluator) if ev.evaluator else None,
            "started_on": ev.started_on.isoformat() if ev.started_on else None,
            "completed_on": ev.completed_on.isoformat() if ev.completed_on else None,
            "peer_review_status": ev.peer_review_status,
            "cohens_d": (
                {
                    "value": round(d.cohens_d, 4),
                    "magnitude": d.magnitude,
                    "mean_treatment": round(d.mean_treatment, 4),
                    "mean_control": round(d.mean_control, 4),
                    "pooled_std": round(d.pooled_std, 4),
                    "n_treatment": d.n_treatment,
                    "n_control": d.n_control,
                }
                if d is not None else None
            ),
        })
    return {"rows": rows}


# ---------------------------------------------------------------------------
# Compliance summary
# ---------------------------------------------------------------------------


def build_compliance_summary(*, report_id: int | None = None) -> dict[str, Any]:
    from apps.mel.reports.models import ComplianceAlert

    qs = ComplianceAlert.objects.filter(resolved=False)
    if report_id is not None:
        qs = qs.filter(report_id=report_id)
    rows = [
        {
            "code": a.code,
            "severity": a.severity,
            "message": a.message,
            "indicator": a.indicator.code if a.indicator else None,
            "created_at": a.created_at.isoformat(),
        }
        for a in qs
    ]
    counts: dict[str, int] = {}
    for a in qs:
        counts[a.severity] = counts.get(a.severity, 0) + 1
    return {"rows": rows, "counts": counts}


# ---------------------------------------------------------------------------
# Top-level orchestrator
# ---------------------------------------------------------------------------


SECTION_BUILDERS = {
    "indicator_rollup": "indicator_rollup",
    "activity_summary": "activity_summary",
    "variance_table": "variance_table",
    "output_snapshot": "output_snapshot",
    "outcome_assessment": "outcome_assessment",
    "impact_evaluation": "impact_evaluation",
    "finance": "finance",
    "compliance": "compliance",
    "smehub_donor": "smehub_donor",
    "smehub_learning": "smehub_learning",
}


# ---------------------------------------------------------------------------
# SME-Hub donor report (FRSME-MEI014–016, MEI021–022)
# ---------------------------------------------------------------------------


def build_smehub_donor_report(*, period_start: date | None = None, period_end: date | None = None) -> dict[str, Any]:
    """Funding-secured by sector / cohort / AIH / period — donor-ready rollup.

    JOINS against RIMS Award + Scholar (FRSME-MEI021): each entrepreneur with
    a baseline whose AIH affiliation links to a RIMS award is annotated with
    the award metadata. Pure function — no DB writes.
    """
    out: dict[str, Any] = {
        "period_start": period_start.isoformat() if period_start else None,
        "period_end": period_end.isoformat() if period_end else None,
        "by_sector": [],
        "by_cohort": [],
        "by_aih": [],
        "totals": {"funding_total": 0.0, "applications_funded": 0, "manual_records": 0},
        "rims_links": [],
        "rep_links": [],
        "repository_links": [],
    }

    try:
        from apps.smehub.investment.models import (
            FundingApplication,
            ManualFundingRecord,
        )
        from apps.smehub.onboarding.models import Business
    except ImportError:
        return out

    # By sector ---------------------------------------------------------
    accepted_apps = FundingApplication.objects.filter(
        status=FundingApplication.Status.ACCEPTED,
    )
    if period_start:
        accepted_apps = accepted_apps.filter(decided_at__gte=period_start)
    if period_end:
        accepted_apps = accepted_apps.filter(decided_at__lte=period_end)

    sector_totals: dict[str, dict[str, Any]] = {}
    for app in accepted_apps.select_related("business"):
        sector = app.business.sector or "Unspecified"
        bucket = sector_totals.setdefault(sector, {"funding_total": 0.0, "count": 0})
        if app.funding_request_amount is not None:
            bucket["funding_total"] += float(app.funding_request_amount)
        bucket["count"] += 1
    out["by_sector"] = [
        {"sector": k, **v} for k, v in sorted(sector_totals.items(), key=lambda kv: -kv[1]["funding_total"])
    ]

    # By cohort ---------------------------------------------------------
    try:
        from apps.smehub.incubation.models import CohortMember

        cohort_lookup = {
            cm.entrepreneur_id: cm.cohort_id
            for cm in CohortMember.objects.all().only("entrepreneur_id", "cohort_id")
        }
    except ImportError:
        cohort_lookup = {}
    cohort_totals: dict[Any, dict[str, Any]] = {}
    for app in accepted_apps:
        cohort_id = cohort_lookup.get(app.entrepreneur_id, None)
        bucket = cohort_totals.setdefault(cohort_id, {"funding_total": 0.0, "count": 0})
        if app.funding_request_amount is not None:
            bucket["funding_total"] += float(app.funding_request_amount)
        bucket["count"] += 1
    out["by_cohort"] = [
        {"cohort_id": k, **v}
        for k, v in sorted(cohort_totals.items(), key=lambda kv: -kv[1]["funding_total"])
    ]

    # By AIH ------------------------------------------------------------
    aih_totals: dict[str, dict[str, Any]] = {}
    for app in accepted_apps:
        biz = app.business
        primary_aff = biz.affiliations.filter(is_primary=True).first()
        aih_label = primary_aff.institution.name if primary_aff else "Unaffiliated"
        bucket = aih_totals.setdefault(aih_label, {"funding_total": 0.0, "count": 0})
        if app.funding_request_amount is not None:
            bucket["funding_total"] += float(app.funding_request_amount)
        bucket["count"] += 1
    out["by_aih"] = [
        {"aih": k, **v}
        for k, v in sorted(aih_totals.items(), key=lambda kv: -kv[1]["funding_total"])
    ]

    # Totals ------------------------------------------------------------
    manual_total = 0.0
    manual_qs = ManualFundingRecord.objects.all()
    if period_start:
        manual_qs = manual_qs.filter(funded_at__gte=period_start)
    if period_end:
        manual_qs = manual_qs.filter(funded_at__lte=period_end)
    for row in manual_qs:
        if row.amount is not None:
            manual_total += float(row.amount)

    funding_total = sum(b["funding_total"] for b in out["by_sector"]) + manual_total
    out["totals"] = {
        "funding_total": funding_total,
        "applications_funded": accepted_apps.count(),
        "manual_records": manual_qs.count(),
        "manual_total": manual_total,
    }

    # Cross-system JOINs (FRSME-MEI021) ---------------------------------
    out["rims_links"] = _smehub_rims_links()
    out["rep_links"] = _smehub_rep_links()
    out["repository_links"] = _smehub_repository_links()
    return out


# ---------------------------------------------------------------------------
# SME-Hub learning report (FRSME-MEI020)
# ---------------------------------------------------------------------------


def build_smehub_learning_report(
    *,
    programme: str = "",
    cohort_id: int | None = None,
    period_start: date | None = None,
    period_end: date | None = None,
    geographic_scope: str = "",
) -> dict[str, Any]:
    """Sector performance, AIH completion rates, partnership-to-deal
    conversion, and dropout reasons. Pure function — no writes.

    MEI020 parameters (all optional, free-text/PK filters):
      * ``programme`` — narrow cohort-based analyses to cohorts whose
        programme title matches (icontains).
      * ``cohort_id`` — narrow to a single cohort.
      * ``period_start`` / ``period_end`` — window on record/member creation.
      * ``geographic_scope`` — narrow to a business country (icontains).
    """
    out: dict[str, Any] = {
        "params": {
            "programme": programme or "",
            "cohort_id": cohort_id,
            "period_start": period_start.isoformat() if period_start else None,
            "period_end": period_end.isoformat() if period_end else None,
            "geographic_scope": geographic_scope or "",
        },
        "sector_performance": [],
        "aih_completion": [],
        "partnership_to_deal": {},
        "dropout_reasons": [],
        "rims_links": _smehub_rims_links(),
        "rep_links": _smehub_rep_links(),
        "repository_links": _smehub_repository_links(),
    }

    try:
        from apps.mel.tracking.models import SMEHubTrackingRecord
        from apps.smehub.incubation.models import CohortMember
        from apps.smehub.onboarding.models import Business
    except ImportError:
        return out

    def _scope_members(qs):
        """Apply the shared cohort-member filters (programme / cohort / geo / period)."""
        if programme:
            qs = qs.filter(cohort__programme__title__icontains=programme)
        if cohort_id:
            qs = qs.filter(cohort_id=cohort_id)
        if geographic_scope:
            qs = qs.filter(business__country__icontains=geographic_scope)
        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

    # Sector performance ------------------------------------------------
    records = SMEHubTrackingRecord.objects.select_related("business")
    if geographic_scope:
        records = records.filter(business__country__icontains=geographic_scope)
    if period_start:
        records = records.filter(created_at__date__gte=period_start)
    if period_end:
        records = records.filter(created_at__date__lte=period_end)
    if programme or cohort_id:
        # Narrow to businesses that belong to a matching cohort membership.
        member_biz = _scope_members(
            CohortMember.objects.all()
        ).values_list("business_id", flat=True)
        records = records.filter(business_id__in=list(member_biz))
    sector_perf: dict[str, dict[str, int]] = {}
    for record in records:
        sector = (record.business.sector if record.business else "Unspecified") or "Unspecified"
        bucket = sector_perf.setdefault(sector, {
            "records": 0,
            "sp2": 0,
            "sp3": 0,
            "sp4": 0,
            "sp5": 0,
        })
        bucket["records"] += 1
        bucket["sp2"] += len(record.sp2_milestones or [])
        bucket["sp3"] += len(record.sp3_partnerships or [])
        bucket["sp4"] += len(record.sp4_commercialisation or [])
        bucket["sp5"] += len(record.sp5_investment or [])
    out["sector_performance"] = [
        {"sector": k, **v} for k, v in sorted(sector_perf.items(), key=lambda kv: -kv[1]["records"])
    ]

    # AIH completion rates ---------------------------------------------
    aih_rates: dict[str, dict[str, int]] = {}
    for member in _scope_members(
        CohortMember.objects.select_related("business").prefetch_related(
            "business__affiliations__institution",
        )
    ):
        primary = member.business.affiliations.filter(is_primary=True).first()
        label = primary.institution.name if primary else "Unaffiliated"
        bucket = aih_rates.setdefault(label, {"members": 0, "completed": 0})
        bucket["members"] += 1
        if member.status == CohortMember.Status.PROGRAMME_COMPLETE:
            bucket["completed"] += 1
    for k, v in aih_rates.items():
        v["completion_pct"] = round((v["completed"] / v["members"]) * 100, 1) if v["members"] else 0
    out["aih_completion"] = [
        {"aih": k, **v} for k, v in sorted(aih_rates.items(), key=lambda kv: -kv[1]["members"])
    ]

    # Partnership → deal conversion ------------------------------------
    try:
        from apps.smehub.linkage.models import MoU
        from apps.smehub.showcasing.models import DealAgreement

        partnerships = MoU.objects.filter(
            status__in=[MoU.Status.FORMALISED, MoU.Status.AMENDED],
        ).count()
        deals = DealAgreement.objects.count()
        out["partnership_to_deal"] = {
            "partnerships": partnerships,
            "deals": deals,
            "conversion_pct": round((deals / partnerships) * 100, 1) if partnerships else 0,
        }
    except ImportError:
        pass

    # Dropout reasons ---------------------------------------------------
    reasons: dict[str, int] = {}
    for member in _scope_members(
        CohortMember.objects.filter(status=CohortMember.Status.WITHDRAWN)
    ).only("withdrawn_reason"):
        reason = (member.withdrawn_reason or "Unspecified")[:120]
        reasons[reason] = reasons.get(reason, 0) + 1
    out["dropout_reasons"] = [
        {"reason": k, "count": v}
        for k, v in sorted(reasons.items(), key=lambda kv: -kv[1])
    ]
    return out


# ---------------------------------------------------------------------------
# Cross-system join helpers (FRSME-MEI021)
# ---------------------------------------------------------------------------


def _smehub_rims_links() -> list[dict[str, Any]]:
    rows: list[dict[str, Any]] = []
    try:
        from apps.smehub.onboarding.models import AIHAffiliation
    except ImportError:
        return rows
    qs = (
        AIHAffiliation.objects.filter(rims_award__isnull=False)
        .select_related("business", "rims_award")[:200]
    )
    for aff in qs:
        rows.append({
            "business_id": aff.business_id,
            "award_id": aff.rims_award_id,
            "award_title": getattr(aff.rims_award, "title", ""),
            "award_status": getattr(aff.rims_award, "status", ""),
        })
    return rows


def _smehub_rep_links() -> list[dict[str, Any]]:
    # Cross-links between SME-Hub cohort members and their course enrolments came
    # from the REP LMS (retired for Moodle). A Moodle-backed version can read
    # completions from MEL Moodle DataPoints later.
    return []


def _smehub_repository_links() -> list[dict[str, Any]]:
    rows: list[dict[str, Any]] = []
    try:
        from apps.smehub.onboarding.models import EntrepreneurProfile
        from apps.repository.documents.models import Document  # type: ignore

        user_ids = list(
            EntrepreneurProfile.objects.values_list("user_id", flat=True),
        )
    except Exception:  # pragma: no cover
        return rows
    qs = Document.objects.filter(submitted_by_id__in=user_ids)[:200]
    for doc in qs:
        rows.append({
            "user_id": doc.submitted_by_id,
            "document_id": doc.pk,
            "title": getattr(doc, "title", ""),
        })
    return rows


# ---------------------------------------------------------------------------
# G20 — Cross-system report sections (RIMS / REP / Repository)
# ---------------------------------------------------------------------------


def build_rims_funding_summary(
    *,
    logframe=None,
    period_start: date | None = None,
    period_end: date | None = None,
) -> dict[str, Any]:
    """Counts and totals from RIMS grants/awards for the bound programme.

    When ``logframe.programme`` is set, narrows by that programme name; otherwise
    aggregates all awards. Lazy-imports the RIMS models so this submodule
    doesn't hard-depend on RIMS being installed.
    """
    try:
        from apps.rims.finance.services import total_disbursed
        from apps.rims.grants.models import Award
    except Exception:  # pragma: no cover - optional upstream
        return {"installed": False, "rows": []}

    programme = getattr(logframe, "programme", "") if logframe else ""
    qs = Award.objects.select_related("application", "application__call")
    # C-D11 — honour the report period window (previously ignored, so the
    # section always showed all-time awards). ``awarded_at`` is the grant's
    # award timestamp.
    if period_start:
        qs = qs.filter(awarded_at__date__gte=period_start)
    if period_end:
        qs = qs.filter(awarded_at__date__lte=period_end)

    # C-D11 — a programme scope that matches no call title silently emptied the
    # section. Detect the mismatch so the report can surface an explicit note
    # instead of a bare "0 awards".
    programme_matched = True
    if programme:
        scoped = qs.filter(application__call__title__icontains=programme)
        if not scoped.exists() and qs.exists():
            programme_matched = False
        qs = scoped

    awards_count = qs.count()
    active_count = qs.filter(status=Award.Status.ACTIVE).count()
    closed_count = qs.filter(status=Award.Status.CLOSED).count()

    total_disbursed_sum = Decimal("0")
    rows: list[dict[str, Any]] = []
    for award in qs[:50]:
        award_total = Decimal("0")
        for budget in award.budgets.all():
            award_total += total_disbursed(budget)
        total_disbursed_sum += award_total
        rows.append({
            "award_id": getattr(award.application, "public_grant_id", "") or f"AW-{award.pk}",
            "title": award.application.call.title if award.application_id else "",
            "status": award.status,
            "amount": _to_float(award.amount),
            "disbursed": _to_float(award_total),
            "currency": award.currency,
        })

    note = ""
    if not programme_matched:
        note = (
            f"No RIMS awards matched the programme scope “{programme}”. "
            "The section is empty because the Strategic Objective's programme label does "
            "not match any grant call title — broaden or correct the scope."
        )

    return {
        "installed": True,
        "programme": programme,
        "programme_matched": programme_matched,
        "note": note,
        "awards_count": awards_count,
        "active_count": active_count,
        "closed_count": closed_count,
        "total_disbursed": _to_float(total_disbursed_sum),
        "rows": rows,
    }


def build_rep_completions_summary(
    *,
    logframe=None,
    period_start: date | None = None,
    period_end: date | None = None,
) -> dict[str, Any]:
    """Course-completions summary.

    Sourced from the REP LMS (retired for Moodle). Superseded by
    :func:`build_moodle_completions_summary`; renders as not-installed.
    """
    return {"installed": False, "rows": []}


def build_moodle_completions_summary(
    *,
    logframe=None,
    period_start: date | None = None,
    period_end: date | None = None,
) -> dict[str, Any]:
    """Moodle course completions/enrolments for the period.

    Reads MEL DataPoints written by ``pull_moodle_outcomes``
    (``source_module="moodle"``) rather than any LMS model, so it works without
    REP. Sums the ``moodle-courses-completed`` and ``moodle-enrolments``
    indicators over the period window.
    """
    from django.db.models import Sum

    from apps.mel.indicators.models import DataPoint

    def _sum(code: str) -> int:
        qs = DataPoint.objects.filter(
            indicator__code=code, source_module="moodle"
        )
        if period_start:
            qs = qs.filter(reported_at__date__gte=period_start)
        if period_end:
            qs = qs.filter(reported_at__date__lte=period_end)
        total = qs.aggregate(total=Sum("value")).get("total")
        return int(total or 0)

    completions = _sum("moodle-courses-completed")
    enrolments = _sum("moodle-enrolments")
    return {
        "installed": True,
        "total_completions": completions,
        "course_completions": completions,
        "enrolments": enrolments,
        "rows": [],
    }


def build_repository_outputs_summary(
    *,
    logframe=None,
    period_start: date | None = None,
    period_end: date | None = None,
) -> dict[str, Any]:
    """Counts of repository documents tagged to RIMS grants (programme scope).

    When ``logframe.programme`` is set, filters via Document.grant_reference
    pattern-matched against the programme name. Otherwise returns the total
    document count.
    """
    try:
        from apps.repository.documents.models import Document
    except Exception:  # pragma: no cover - optional upstream
        return {"installed": False, "rows": []}

    qs = Document.objects.all()
    if period_start:
        qs = qs.filter(created_at__date__gte=period_start)
    if period_end:
        qs = qs.filter(created_at__date__lte=period_end)

    programme = getattr(logframe, "programme", "") if logframe else ""
    if programme:
        # Programme is a free-text label on LogFrame today (CharField). We
        # match against grant_reference which usually carries the
        # public_grant_id. Defensive icontains — when no overlap exists, the
        # section degrades to an empty table rather than crashing.
        try:
            from apps.rims.grants.models import Application

            grant_ids = list(
                Application.objects.filter(call__title__icontains=programme)
                .values_list("public_grant_id", flat=True)
            )
            if grant_ids:
                qs = qs.filter(grant_reference__in=[g for g in grant_ids if g])
            else:
                qs = qs.none()
        except Exception:  # pragma: no cover
            pass

    rows: list[dict[str, Any]] = []
    for doc in qs.order_by("-created_at")[:50]:
        rows.append({
            "document_id": doc.pk,
            "title": getattr(doc, "title", ""),
            "grant_reference": getattr(doc, "grant_reference", ""),
            "grant_closed": getattr(doc, "grant_closed_flag", False),
            "created_at": doc.created_at.isoformat() if getattr(doc, "created_at", None) else None,
        })
    return {
        "installed": True,
        "programme": programme,
        "total": qs.count(),
        "closed_grants": qs.filter(grant_closed_flag=True).count() if "grant_closed_flag" in {f.name for f in Document._meta.fields} else 0,
        "rows": rows,
    }


def build_consolidated_data(
    *,
    logframe,
    period_label: str,
    period_start: date,
    period_end: date,
    sections: list[str],
    report_id: int | None = None,
    section_configs: dict[str, dict] | None = None,
) -> dict[str, Any]:
    """Run each requested builder and return a serialisable dict.

    ``section_configs`` maps a section kind → its persisted ReportSection
    ``config`` JSON (from the drag-drop builder). Per-kind config — currently
    ``indicator_codes`` for the indicator rollup / variance table — is threaded
    into the builders so the generated report honours the author's selection
    (C-D3). Kinds without config, or reports generated purely from the wizard
    section list, degrade to the full un-filtered rollup as before.
    """
    section_configs = section_configs or {}

    def _codes_for(kind: str) -> list[str] | None:
        cfg = section_configs.get(kind) or {}
        codes = cfg.get("indicator_codes")
        return [c for c in codes if c] if codes else None

    out: dict[str, Any] = {
        "scope": {
            "logframe_slug": getattr(logframe, "slug", None),
            "logframe_name": getattr(logframe, "name", None),
            "period_label": period_label,
            "period_start": period_start.isoformat(),
            "period_end": period_end.isoformat(),
        },
        # MEI015 — the exact section sequence requested (from the wizard's
        # section list OR the drag-drop builder). The report body iterates this
        # so the rendered order matches what the author arranged. Legacy reports
        # generated before this field existed fall back to a canonical order in
        # the ``report_section_order`` template filter.
        "section_order": list(sections),
    }
    if "indicator_rollup" in sections:
        out["indicator_rollup"] = build_indicator_rollup(
            logframe=logframe,
            period_label=period_label,
            indicator_codes=_codes_for("indicator_rollup"),
        )
    if "variance_table" in sections:
        out["variance_table"] = build_variance_table(
            logframe=logframe,
            period_label=period_label,
            indicator_codes=_codes_for("variance_table"),
        )
    if "activity_summary" in sections:
        out["activity_summary"] = build_activity_summary(
            logframe=logframe, period_start=period_start, period_end=period_end,
        )
    if "output_snapshot" in sections:
        out["output_snapshot"] = build_output_snapshot(logframe=logframe)
    if "outcome_assessment" in sections:
        out["outcome_assessment"] = build_outcome_section(
            logframe=logframe, period_label=period_label,
        )
    if "impact_evaluation" in sections:
        out["impact_evaluation"] = build_impact_section(logframe=logframe)
    if "finance" in sections:
        out["finance"] = build_finance_snapshot(
            logframe=logframe, period_start=period_start, period_end=period_end,
        )
    if "budget_variance" in sections:
        out["budget_variance"] = build_budget_variance(logframe=logframe)
    if "rims_funding_summary" in sections:
        out["rims_funding_summary"] = build_rims_funding_summary(
            logframe=logframe, period_start=period_start, period_end=period_end,
        )
    if "rep_completions_summary" in sections:
        out["rep_completions_summary"] = build_rep_completions_summary(
            logframe=logframe, period_start=period_start, period_end=period_end,
        )
    if "moodle_completions_summary" in sections:
        out["moodle_completions_summary"] = build_moodle_completions_summary(
            logframe=logframe, period_start=period_start, period_end=period_end,
        )
    if "repository_outputs_summary" in sections:
        out["repository_outputs_summary"] = build_repository_outputs_summary(
            logframe=logframe, period_start=period_start, period_end=period_end,
        )
    if "compliance" in sections:
        out["compliance"] = build_compliance_summary(report_id=report_id)
    return out