"""Data export services — one function per module, each returns bytes (xlsx).

Every export mirrors the corresponding import template column layout so data
can be round-tripped: export → edit → re-import without reformatting.
"""
from __future__ import annotations

from io import BytesIO

from openpyxl import Workbook
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side

# ── Styling helpers ────────────────────────────────────────────────────────────
_H_FILL = PatternFill("solid", start_color="1a5c38", end_color="1a5c38")
_H_FONT = Font(name="Arial", bold=True, color="FFFFFF", size=10)
_NORM   = Font(name="Arial", size=10)
_thin   = Side(style="thin", color="cccccc")
_BDR    = Border(left=_thin, right=_thin, top=_thin, bottom=_thin)


def _header(ws, cols: list[str], freeze: bool = True) -> None:
    for c, label in enumerate(cols, 1):
        cell = ws.cell(row=1, column=c, value=label)
        cell.font = _H_FONT
        cell.fill = _H_FILL
        cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
        cell.border = _BDR
    ws.row_dimensions[1].height = 26
    if freeze:
        ws.freeze_panes = ws.cell(row=2, column=1)


def _row(ws, values: list, row: int) -> None:
    for c, v in enumerate(values, 1):
        cell = ws.cell(row=row, column=c, value=v)
        cell.font = _NORM
        cell.border = _BDR
        cell.alignment = Alignment(wrap_text=True, vertical="top")


def _col_widths(ws, cols: list[str]) -> None:
    from openpyxl.utils import get_column_letter
    for i, c in enumerate(cols, 1):
        ws.column_dimensions[get_column_letter(i)].width = max(16, len(c) + 4)


def _wb_bytes(wb: Workbook) -> bytes:
    buf = BytesIO()
    wb.save(buf)
    return buf.getvalue()


def _str(val) -> str:
    return str(val) if val is not None else ""


def _date(val) -> str:
    return val.strftime("%Y-%m-%d") if val else ""


def _dt(val) -> str:
    return val.strftime("%Y-%m-%d %H:%M") if val else ""


# ══════════════════════════════════════════════════════════════════════════════
# MODULE EXPORTS
# ══════════════════════════════════════════════════════════════════════════════

def export_users() -> bytes:
    from django.contrib.auth import get_user_model
    User = get_user_model()
    wb = Workbook()
    ws = wb.active
    ws.title = "Users"

    cols = ["email", "first_name", "last_name", "role", "institution", "country",
            "phone", "is_active", "date_joined"]
    _header(ws, cols)
    for i, u in enumerate(User.objects.select_related("institution").order_by("email"), 2):
        _row(ws, [
            u.email, u.first_name, u.last_name,
            _str(getattr(u, "role", "")),
            _str(u.institution.name if u.institution else u.non_partner_institution_name),
            _str(getattr(u.profile, "country", "") if hasattr(u, "profile") else ""),
            _str(getattr(u, "phone", "")),
            "yes" if u.is_active else "no",
            _date(u.date_joined),
        ], i)
    _col_widths(ws, cols)
    return _wb_bytes(wb)


def export_institutions() -> bytes:
    from apps.core.authentication.models import Institution
    wb = Workbook()
    ws = wb.active; ws.title = "Institutions"
    cols = ["name", "country", "institution_type", "is_member", "is_active", "website", "address", "region"]
    _header(ws, cols)
    for i, inst in enumerate(Institution.objects.order_by("name"), 2):
        _row(ws, [
            inst.name, inst.country, _str(inst.institution_type),
            "yes" if inst.is_member else "no",
            "yes" if inst.is_active else "no",
            _str(inst.website), _str(inst.address), _str(inst.region),
        ], i)
    _col_widths(ws, cols)
    return _wb_bytes(wb)


def export_alumni() -> bytes:
    from apps.alumni.profiles.models import AlumniProfile, Degree, Employment, Publication
    wb = Workbook()

    # Sheet 1 — Alumni profiles
    ws1 = wb.active; ws1.title = "Alumni"
    cols1 = ["email", "first_name", "last_name", "current_employer", "current_position",
             "current_institution", "country", "orcid_id", "linkedin_url", "consent", "source"]
    _header(ws1, cols1)
    profiles = AlumniProfile.objects.select_related("user", "current_institution").order_by("user__email")
    for i, p in enumerate(profiles, 2):
        _row(ws1, [
            p.user.email, p.user.first_name, p.user.last_name,
            _str(p.current_employer), _str(p.current_position),
            _str(p.current_institution.name if p.current_institution else ""),
            _str(getattr(p.user, "profile", None) and p.user.profile.country or ""),
            _str(p.orcid_id), _str(p.linkedin_url),
            "yes" if p.visibility_consent else "no", _str(p.source),
        ], i)
    _col_widths(ws1, cols1)

    # Sheet 2 — Degrees
    ws2 = wb.create_sheet("Degrees")
    cols2 = ["email", "institution", "programme_name", "degree_type", "started_on", "graduated_on",
             "thesis_title", "advisor_name", "funder"]
    _header(ws2, cols2)
    for i, d in enumerate(Degree.objects.select_related("profile__user", "institution").order_by("profile__user__email"), 2):
        _row(ws2, [
            d.profile.user.email,
            _str(d.institution.name if d.institution else ""),
            d.programme_name, d.degree_type,
            _date(d.started_on), _date(d.graduated_on),
            _str(d.thesis_title), _str(d.advisor_name), _str(d.funder),
        ], i)
    _col_widths(ws2, cols2)

    # Sheet 3 — Employment
    ws3 = wb.create_sheet("Employment")
    cols3 = ["email", "organisation", "position", "sector", "country", "started_on", "ended_on", "description"]
    _header(ws3, cols3)
    for i, e in enumerate(Employment.objects.select_related("profile__user").order_by("profile__user__email", "started_on"), 2):
        _row(ws3, [
            e.profile.user.email, e.organisation, e.position,
            _str(e.sector), _str(e.country), _date(e.started_on), _date(e.ended_on), _str(e.description),
        ], i)
    _col_widths(ws3, cols3)

    # Sheet 4 — Publications
    ws4 = wb.create_sheet("Publications")
    cols4 = ["email", "title", "publication_type", "year", "doi", "journal_or_venue", "authors_text"]
    _header(ws4, cols4)
    for i, p in enumerate(Publication.objects.select_related("profile__user").order_by("profile__user__email"), 2):
        _row(ws4, [
            p.profile.user.email, p.title, _str(p.publication_type),
            _str(p.year), _str(p.doi), "", _str(p.authors_text),
        ], i)
    _col_widths(ws4, cols4)

    return _wb_bytes(wb)


def export_scholars() -> bytes:
    from apps.rims.scholarships.models import Scholar, Stipend, ProgressReport, GraduationRecord
    wb = Workbook()

    ws1 = wb.active; ws1.title = "Scholars"
    cols1 = ["email", "scholarship", "institution", "degree_level", "degree_name",
             "cohort_year", "intake_year", "expected_graduation", "student_number",
             "thesis_title", "student_type", "status", "enrolled_at"]
    _header(ws1, cols1)
    for i, s in enumerate(Scholar.objects.select_related("user", "scholarship", "institution").order_by("user__email"), 2):
        _row(ws1, [
            s.user.email,
            _str(s.scholarship.name if s.scholarship else ""),
            _str(s.institution.name if s.institution else ""),
            s.degree_level, s.degree_name,
            _str(s.cohort_year), _str(s.intake_year),
            _date(s.expected_graduation), _str(s.student_number),
            _str(s.thesis_title), s.student_type, s.status,
            _dt(s.enrolled_at),
        ], i)
    _col_widths(ws1, cols1)

    ws2 = wb.create_sheet("Stipends")
    cols2 = ["scholar_email", "amount", "currency", "paid_on", "reference", "status"]
    _header(ws2, cols2)
    for i, st in enumerate(Stipend.objects.select_related("scholar__user").order_by("scholar__user__email", "paid_on"), 2):
        _row(ws2, [
            st.scholar.user.email, _str(st.amount), _str(st.currency),
            _date(st.paid_on), _str(st.reference), st.status,
        ], i)
    _col_widths(ws2, cols2)

    ws3 = wb.create_sheet("Progress_Reports")
    cols3 = ["scholar_email", "period_label", "semester", "year", "status", "submitted_on"]
    _header(ws3, cols3)
    for i, r in enumerate(ProgressReport.objects.select_related("scholar__user").order_by("scholar__user__email", "year"), 2):
        _row(ws3, [
            r.scholar.user.email, _str(r.period_label),
            _str(r.semester), _str(r.year), r.status, _date(r.submitted_on),
        ], i)
    _col_widths(ws3, cols3)

    ws4 = wb.create_sheet("Graduation_Records")
    cols4 = ["scholar_email", "graduation_date", "final_degree_name", "thesis_title", "institution"]
    _header(ws4, cols4)
    for i, g in enumerate(GraduationRecord.objects.select_related("scholar__user", "scholar__institution").order_by("graduation_date"), 2):
        _row(ws4, [
            g.scholar.user.email, _date(g.graduation_date),
            _str(g.final_degree_name), _str(g.thesis_title),
            _str(g.scholar.institution.name if g.scholar.institution else ""),
        ], i)
    _col_widths(ws4, cols4)

    return _wb_bytes(wb)


def export_grants() -> bytes:
    from apps.rims.grants.models import FundingRecord, GrantCall, Application, Award
    wb = Workbook()

    ws1 = wb.active; ws1.title = "Funding_Records"
    cols1 = ["title", "donor", "currency", "amount", "start_date", "end_date", "status", "public_grant_id"]
    _header(ws1, cols1)
    for i, fr in enumerate(FundingRecord.objects.select_related("partner").order_by("title"), 2):
        _row(ws1, [
            fr.title, _str(fr.partner.name if fr.partner else ""),
            fr.currency, _str(fr.amount), _date(fr.start_date), _date(fr.end_date),
            fr.status, _str(fr.public_grant_id),
        ], i)
    _col_widths(ws1, cols1)

    ws2 = wb.create_sheet("Grant_Calls")
    cols2 = ["title", "call_type", "status", "opens_at", "closes_at", "max_awards", "budget_ceiling", "funding_record"]
    _header(ws2, cols2)
    for i, gc in enumerate(GrantCall.objects.select_related("funding_record").order_by("-opens_at"), 2):
        _row(ws2, [
            gc.title, gc.call_type, gc.status,
            _dt(gc.opens_at), _dt(gc.closes_at),
            _str(gc.max_awards), _str(gc.budget_ceiling),
            _str(gc.funding_record.title if gc.funding_record else ""),
        ], i)
    _col_widths(ws2, cols2)

    ws3 = wb.create_sheet("Applications")
    cols3 = ["applicant_email", "call_title", "institution", "status", "submitted_at",
             "eligibility_passed", "ranking_score", "ranking_position"]
    _header(ws3, cols3)
    for i, app in enumerate(
        Application.objects.select_related("applicant", "call", "institution").order_by("call__title", "applicant__email"), 2
    ):
        _row(ws3, [
            app.applicant.email, app.call.title,
            _str(app.institution.name if app.institution else ""),
            app.status, _dt(app.submitted_at),
            "yes" if app.eligibility_passed else "no",
            _str(app.ranking_score), _str(app.ranking_position),
        ], i)
    _col_widths(ws3, cols3)

    ws4 = wb.create_sheet("Awards")
    cols4 = ["applicant_email", "call_title", "amount", "currency", "status", "activation_date", "project_end_date"]
    _header(ws4, cols4)
    for i, aw in enumerate(
        Award.objects.select_related("application__applicant", "application__call").order_by("application__call__title"), 2
    ):
        _row(ws4, [
            aw.application.applicant.email, aw.application.call.title,
            _str(aw.amount), aw.currency, aw.status,
            _date(aw.activation_date), _date(aw.project_end_date),
        ], i)
    _col_widths(ws4, cols4)

    return _wb_bytes(wb)


def export_mel() -> bytes:
    from apps.mel.indicators.models import LogFrame, Indicator, IndicatorTarget, DataPoint
    from apps.mel.tracking.models import Activity
    wb = Workbook()

    ws1 = wb.active; ws1.title = "LogFrames"
    cols1 = ["name", "programme", "period_start", "period_end", "status"]
    _header(ws1, cols1)
    for i, lf in enumerate(LogFrame.objects.order_by("name"), 2):
        _row(ws1, [lf.name, _str(lf.programme), _date(lf.period_start), _date(lf.period_end), lf.status], i)
    _col_widths(ws1, cols1)

    ws2 = wb.create_sheet("Indicators")
    cols2 = ["logframe", "code", "name", "level", "unit", "frequency", "baseline", "responsible"]
    _header(ws2, cols2)
    for i, ind in enumerate(Indicator.objects.select_related("logframe_row__logframe", "responsible").order_by("code"), 2):
        _row(ws2, [
            _str(ind.logframe_row.logframe.name if ind.logframe_row else ""),
            ind.code, ind.name, ind.indicator_type, ind.unit, ind.frequency,
            "", _str(ind.responsible.email if ind.responsible else ""),
        ], i)
    _col_widths(ws2, cols2)

    ws3 = wb.create_sheet("Targets")
    cols3 = ["indicator_code", "period_start", "period_end", "target_value", "notes"]
    _header(ws3, cols3)
    for i, t in enumerate(IndicatorTarget.objects.select_related("indicator").order_by("indicator__code", "period_start"), 2):
        _row(ws3, [t.indicator.code, _date(t.period_start), _date(t.period_end), _str(t.target_value), _str(t.notes)], i)
    _col_widths(ws3, cols3)

    ws4 = wb.create_sheet("DataPoints")
    cols4 = ["indicator_code", "period", "value", "reported_by", "disaggregation", "disaggregation_value", "notes"]
    _header(ws4, cols4)
    for i, dp in enumerate(DataPoint.objects.select_related("indicator", "reported_by").order_by("indicator__code", "period"), 2):
        _row(ws4, [
            dp.indicator.code, _date(dp.period), _str(dp.value),
            _str(dp.reported_by.email if dp.reported_by else ""),
            _str(dp.disaggregation), _str(dp.disaggregation_value), _str(dp.notes),
        ], i)
    _col_widths(ws4, cols4)

    ws5 = wb.create_sheet("Activities")
    cols5 = ["logframe", "logframe_row", "name", "scheduled_start", "scheduled_end",
             "actual_start", "actual_end", "status", "responsible"]
    _header(ws5, cols5)
    for i, a in enumerate(Activity.objects.select_related("logframe_row__logframe", "responsible").order_by("scheduled_start"), 2):
        _row(ws5, [
            _str(a.logframe_row.logframe.name if a.logframe_row else ""),
            _str(a.logframe_row.title if a.logframe_row else ""),
            a.name, _date(a.scheduled_start), _date(a.scheduled_end),
            _date(a.actual_start), _date(a.actual_end), a.status,
            _str(a.responsible.email if a.responsible else ""),
        ], i)
    _col_widths(ws5, cols5)

    return _wb_bytes(wb)


def export_smehub() -> bytes:
    from apps.smehub.onboarding.models import EntrepreneurProfile, Business, Innovation, BusinessBaseline
    wb = Workbook()

    ws1 = wb.active; ws1.title = "Entrepreneurs"
    cols1 = ["email", "first_name", "last_name", "gender", "country", "phone", "sector", "registration_status"]
    _header(ws1, cols1)
    for i, e in enumerate(EntrepreneurProfile.objects.select_related("user").order_by("user__email"), 2):
        _row(ws1, [
            e.user.email, e.user.first_name, e.user.last_name,
            _str(e.gender), _str(e.country), _str(e.phone),
            _str(e.sector), _str(e.registration_status),
        ], i)
    _col_widths(ws1, cols1)

    ws2 = wb.create_sheet("Businesses")
    cols2 = ["owner_email", "business_name", "sector", "country", "city", "year_founded",
             "employees", "annual_revenue_usd", "registration_number", "website", "stage"]
    _header(ws2, cols2)
    for i, b in enumerate(Business.objects.select_related("entrepreneur__user").order_by("name"), 2):
        _row(ws2, [
            b.entrepreneur.user.email, b.name, _str(b.sector),
            _str(b.country), _str(b.city), _str(b.year_founded),
            _str(b.employees), _str(b.annual_revenue_usd),
            _str(b.registration_number), _str(b.website), _str(b.stage),
        ], i)
    _col_widths(ws2, cols2)

    ws3 = wb.create_sheet("Innovations")
    cols3 = ["business_name", "title", "stage", "description", "target_market", "funding_received_usd"]
    _header(ws3, cols3)
    for i, inv in enumerate(Innovation.objects.select_related("business").order_by("business__name"), 2):
        _row(ws3, [
            _str(inv.business.name), inv.title, _str(inv.stage),
            _str(inv.description), _str(inv.target_market), _str(inv.funding_received_usd),
        ], i)
    _col_widths(ws3, cols3)

    ws4 = wb.create_sheet("Business_Baseline")
    cols4 = ["business_name", "baseline_date", "annual_revenue_usd", "total_employees",
             "female_employees", "total_assets_usd"]
    _header(ws4, cols4)
    for i, bb in enumerate(BusinessBaseline.objects.select_related("business").order_by("business__name", "captured_at"), 2):
        _row(ws4, [
            _str(bb.business.name), _date(bb.captured_at),
            _str(bb.annual_revenue_usd), _str(bb.employees_total),
            _str(bb.employees_female), _str(bb.assets_usd),
        ], i)
    _col_widths(ws4, cols4)

    return _wb_bytes(wb)


def export_repository() -> bytes:
    from apps.repository.documents.models import Document
    wb = Workbook()

    ws1 = wb.active; ws1.title = "Documents"
    cols1 = ["title", "year", "document_type", "collection", "authors", "keywords",
             "doi", "language", "country_of_study", "open_access", "published_at"]
    _header(ws1, cols1)
    for i, doc in enumerate(
        Document.objects.prefetch_related("collections", "keywords", "documentauthor_set__author").order_by("title"), 2
    ):
        authors = "; ".join(
            a.author.full_name for a in doc.documentauthor_set.select_related("author").order_by("position")
        )
        keywords = "; ".join(k.term for k in doc.keywords.all())
        collection = ", ".join(c.name for c in doc.collections.all())
        _row(ws1, [
            doc.title, _str(doc.year), _str(doc.document_type),
            collection, authors, keywords,
            _str(doc.doi), _str(doc.language),
            _str(doc.country_of_study),
            "yes" if doc.open_access else "no",
            _date(doc.published_at),
        ], i)
    _col_widths(ws1, cols1)

    return _wb_bytes(wb)


# ── Registry used by views ─────────────────────────────────────────────────────
EXPORT_REGISTRY: dict[str, dict] = {
    "users":        {"label": "Users & Staff",      "icon": "👥", "fn": export_users,        "filename": "iilmp_users"},
    "institutions": {"label": "Institutions",        "icon": "🏫", "fn": export_institutions, "filename": "iilmp_institutions"},
    "alumni":       {"label": "Alumni",              "icon": "🎓", "fn": export_alumni,       "filename": "iilmp_alumni"},
    "scholars":     {"label": "Scholars",            "icon": "📖", "fn": export_scholars,     "filename": "iilmp_scholars"},
    "grants":       {"label": "Grants & Awards",     "icon": "💰", "fn": export_grants,       "filename": "iilmp_grants"},
    "mel":          {"label": "MEL Indicators",      "icon": "📊", "fn": export_mel,          "filename": "iilmp_mel"},
    "smehub":       {"label": "SME-Hub",             "icon": "💼", "fn": export_smehub,       "filename": "iilmp_smehub"},
    "repository":   {"label": "Repository",          "icon": "📚", "fn": export_repository,   "filename": "iilmp_repository"},
}