"""SME-Hub donor + learning report renderers (FRSME-MEI020/021).

The Phase 6 build wired pure-function builders that produced rendering-
ready dicts; report views displayed them as HTML only. Phase 8 closes the
audit gap by adding XLSX + PDF output paths that consume the same dicts.

Renderers reuse:
- ``openpyxl.Workbook`` (already used at ``apps.mel.reports.services.render_xlsx``)
- ``apps.core.utils.pdf.render_pdf_from_html`` (already used at
  ``apps.mel.reports.services.render_pdf``)

Templates ``reports/smehub_donor_print.html`` /
``reports/smehub_learning_print.html`` mirror the existing
``_report_body_print.html`` style — print-friendly, inline-styled.
"""
from __future__ import annotations

from io import BytesIO
from typing import Any

from django.template.loader import render_to_string


# ---------------------------------------------------------------------------
# Donor report
# ---------------------------------------------------------------------------


def render_smehub_donor_xlsx(data: dict[str, Any]) -> bytes:
    """XLSX rendering of ``build_smehub_donor_report`` output.

    Sheets: ``Totals``, ``By sector``, ``By cohort``, ``By AIH``,
    ``RIMS links``, ``REP links``, ``Repository links``. One sheet per
    section keeps each tab focused and copy-pasteable.
    """
    from openpyxl import Workbook

    wb = Workbook()
    wb.remove(wb.active)

    totals = data.get("totals", {}) or {}
    ws = wb.create_sheet("Totals")
    ws.append(["Metric", "Value"])
    ws.append(["Period start", data.get("period_start") or "—"])
    ws.append(["Period end", data.get("period_end") or "—"])
    ws.append(["Funding total (USD)", totals.get("funding_total", 0)])
    ws.append(["Applications funded", totals.get("applications_funded", 0)])
    ws.append(["Manual records", totals.get("manual_records", 0)])
    ws.append(["Manual total (USD)", totals.get("manual_total", 0)])

    ws = wb.create_sheet("By sector")
    ws.append(["Sector", "Funding total (USD)", "Application count"])
    for row in data.get("by_sector", []) or []:
        ws.append([row.get("sector"), row.get("funding_total", 0), row.get("count", 0)])

    ws = wb.create_sheet("By cohort")
    ws.append(["Cohort ID", "Funding total (USD)", "Application count"])
    for row in data.get("by_cohort", []) or []:
        ws.append([row.get("cohort_id"), row.get("funding_total", 0), row.get("count", 0)])

    ws = wb.create_sheet("By AIH")
    ws.append(["AIH", "Funding total (USD)", "Application count"])
    for row in data.get("by_aih", []) or []:
        ws.append([row.get("aih"), row.get("funding_total", 0), row.get("count", 0)])

    ws = wb.create_sheet("RIMS links")
    ws.append(["Business ID", "Award ID", "Award title", "Award status"])
    for row in data.get("rims_links", []) or []:
        ws.append([
            row.get("business_id"),
            row.get("award_id"),
            row.get("award_title"),
            row.get("award_status"),
        ])

    ws = wb.create_sheet("REP links")
    ws.append(["User ID", "Course", "Status"])
    for row in data.get("rep_links", []) or []:
        ws.append([row.get("user_id"), row.get("course"), row.get("status")])

    ws = wb.create_sheet("Repository links")
    ws.append(["User ID", "Document ID", "Title"])
    for row in data.get("repository_links", []) or []:
        ws.append([row.get("user_id"), row.get("document_id"), row.get("title")])

    buf = BytesIO()
    wb.save(buf)
    return buf.getvalue()


def render_smehub_donor_pdf(data: dict[str, Any]) -> bytes:
    """Render the donor report as PDF via WeasyPrint, mirroring the existing
    ``apps.mel.reports.services.render_pdf`` shape.
    """
    from apps.core.utils.pdf import render_pdf_from_html

    html = render_to_string("reports/smehub_donor_print.html", {"data": data})
    return render_pdf_from_html(html)


# ---------------------------------------------------------------------------
# Learning report
# ---------------------------------------------------------------------------


def render_smehub_learning_xlsx(data: dict[str, Any]) -> bytes:
    """XLSX rendering of ``build_smehub_learning_report`` output."""
    from openpyxl import Workbook

    wb = Workbook()
    wb.remove(wb.active)

    ws = wb.create_sheet("Sector performance")
    ws.append(["Sector", "Records", "SP2", "SP3", "SP4", "SP5"])
    for row in data.get("sector_performance", []) or []:
        ws.append([
            row.get("sector"),
            row.get("records", 0),
            row.get("sp2", 0),
            row.get("sp3", 0),
            row.get("sp4", 0),
            row.get("sp5", 0),
        ])

    ws = wb.create_sheet("AIH completion")
    ws.append(["AIH", "Members", "Completed", "Completion %"])
    for row in data.get("aih_completion", []) or []:
        ws.append([
            row.get("aih"),
            row.get("members", 0),
            row.get("completed", 0),
            row.get("completion_pct", 0),
        ])

    p2d = data.get("partnership_to_deal", {}) or {}
    ws = wb.create_sheet("Partnership → deal")
    ws.append(["Metric", "Value"])
    ws.append(["Partnerships (formalised + amended MoUs)", p2d.get("partnerships", 0)])
    ws.append(["Deals (formalised agreements)", p2d.get("deals", 0)])
    ws.append(["Conversion %", p2d.get("conversion_pct", 0)])

    ws = wb.create_sheet("Dropout reasons")
    ws.append(["Reason", "Count"])
    for row in data.get("dropout_reasons", []) or []:
        ws.append([row.get("reason"), row.get("count", 0)])

    ws = wb.create_sheet("RIMS links")
    ws.append(["Business ID", "Award ID", "Award title", "Award status"])
    for row in data.get("rims_links", []) or []:
        ws.append([
            row.get("business_id"),
            row.get("award_id"),
            row.get("award_title"),
            row.get("award_status"),
        ])

    ws = wb.create_sheet("REP links")
    ws.append(["User ID", "Course", "Status"])
    for row in data.get("rep_links", []) or []:
        ws.append([row.get("user_id"), row.get("course"), row.get("status")])

    ws = wb.create_sheet("Repository links")
    ws.append(["User ID", "Document ID", "Title"])
    for row in data.get("repository_links", []) or []:
        ws.append([row.get("user_id"), row.get("document_id"), row.get("title")])

    buf = BytesIO()
    wb.save(buf)
    return buf.getvalue()


def render_smehub_learning_pdf(data: dict[str, Any]) -> bytes:
    """Render the learning report as PDF via WeasyPrint."""
    from apps.core.utils.pdf import render_pdf_from_html

    html = render_to_string("reports/smehub_learning_print.html", {"data": data})
    return render_pdf_from_html(html)