"""Excel and Word exports of the public-holiday calendar.

Two shapes, two jobs:

* **Excel** — one row per day, flat. What HR wants for data work: sorting,
  pivoting, re-importing after a bulk edit (the import parser accepts exactly
  this column set, so an export round-trips).
* **Word** — a faithful rebuild of the HR-owned source document
  *PingCAP Public Holidays for Global Team Members*: a numbered section per
  country, multi-day holidays folded into one table cell, the "N days off with
  adjustment / X is a working day" sentence generated from the stored makeup
  workdays, the regional-holiday advisory, and the Google Calendar subscription
  link. What HR sends to the company.

The Word headline count per country comes from the stored ``stated_days`` when HR
has one, so the exported document reproduces the number HR actually published;
the per-holiday adjustment sentences are derived from the data. When the two
disagree the console flags it — the export does not quietly pick a side.
"""
from __future__ import annotations

import io
import re
from collections.abc import Sequence
from datetime import UTC, date, datetime

from fastapi import Request
from fastapi.responses import StreamingResponse
from sqlalchemy.ext.asyncio import AsyncSession

from app.auth.models import AuthUser
from app.core.exporting import _content_disposition, audit_export
from app.domains.hr.holiday_models import (
    MAKEUP_WORKDAY,
    PUBLIC_HOLIDAY,
    HrPublicHoliday,
    HrPublicHolidayCalendar,
)
from app.domains.hr.holiday_service import HolidayGroup, build_groups, observed_day_count

DOCX_MEDIA_TYPE = (
    "application/vnd.openxmlformats-officedocument.wordprocessingml.document"
)

_EN_WEEKDAYS = (
    "Monday", "Tuesday", "Wednesday", "Thursday",
    "Friday", "Saturday", "Sunday",
)
_EN_MONTHS = (
    "January", "February", "March", "April", "May", "June",
    "July", "August", "September", "October", "November", "December",
)

# Column set for the Excel export. Header labels double as the import parser's
# recognised aliases, so an exported sheet can be edited and imported back.
EXPORT_COLUMNS: tuple[tuple[str, str], ...] = (
    ("country", "Country"),
    ("country_label", "Country Name"),
    ("year", "Year"),
    ("day", "Date"),
    ("weekday", "Weekday"),
    ("name_en", "Holiday"),
    ("name_local", "Holiday (Local)"),
    ("day_type", "Type"),
    ("note", "Note"),
    ("group_key", "Group"),
    ("source", "Source"),
)

_TYPE_LABELS = {
    PUBLIC_HOLIDAY: "Public holiday",
    MAKEUP_WORKDAY: "Makeup workday",
}


def long_date(day: date) -> str:
    """``Thursday, January 1, 2026`` — the source document's date format."""
    return f"{_EN_WEEKDAYS[day.weekday()]}, {_EN_MONTHS[day.month - 1]} {day.day}, {day.year}"


def _short_date(day: date) -> str:
    """``January 4 (Sunday)`` — the form used inside adjustment sentences."""
    return f"{_EN_MONTHS[day.month - 1]} {day.day} ({_EN_WEEKDAYS[day.weekday()]})"


def excel_rows(
    rows: Sequence[HrPublicHoliday],
    labels: dict[str, str] | None = None,
) -> list[dict[str, object]]:
    """Flatten day rows into the export's column shape."""
    labels = labels or {}
    return [
        {
            "country": row.country,
            "country_label": labels.get(row.country, row.country),
            "year": row.day.year,
            "day": row.day.isoformat(),
            "weekday": _EN_WEEKDAYS[row.day.weekday()],
            "name_en": row.name_en,
            "name_local": row.name_local or "",
            "day_type": _TYPE_LABELS.get(row.day_type, row.day_type),
            "note": row.note or "",
            "group_key": row.group_key,
            "source": row.source,
        }
        for row in rows
    ]


def adjustment_sentence(group: HolidayGroup) -> str | None:
    """``3 days off with adjustment. January 4 (Sunday) is a working day.``

    Returns ``None`` for a holiday with no makeup workdays, which is every
    country except China in the 2026 baseline.
    """
    if not group.makeup_days:
        return None
    listed = " and ".join(_short_date(d) for d in group.makeup_days)
    verb = "is a working day" if len(group.makeup_days) == 1 else "are working days"
    # "1 days off" read as a typo in a document HR mails to the whole company.
    unit = "day" if group.total_days == 1 else "days"
    return (
        f"{group.total_days} {unit} off with adjustment. {listed} {verb}."
    )


def _country_heading(
    index: int, label: str, calendar: HrPublicHolidayCalendar | None,
    groups: Sequence[HolidayGroup],
) -> str:
    """The numbered section heading, carrying HR's published day count.

    Note the two units in one document, which is inherited from the source: this
    heading counts *observed* days off (Mon–Fri, net of makeup workdays) while the
    per-holiday adjustment sentences count *calendar* days. HR's original reads
    the same way, so the export reproduces it rather than silently switching one
    of them — but the difference is why "China … 13 days" sits above rows summing
    to 33.
    """
    days = [d for g in groups for d in g.days]
    makeups = [d for g in groups for d in g.makeup_days]
    stated = calendar.stated_days if calendar and calendar.stated_days is not None else None
    count = stated if stated is not None else observed_day_count(days, makeups)
    return (
        f"{index}. PingCAP observes the following {label} Holidays - {count} days"
    )


def _add_hyperlink(paragraph, url: str, text: str) -> None:
    """Insert a real clickable hyperlink run.

    python-docx has no hyperlink API, so the relationship and the ``w:hyperlink``
    element are built by hand — the same approach the library's own docs suggest.
    """
    from docx.oxml.ns import qn
    from docx.oxml.shared import OxmlElement

    part = paragraph.part
    r_id = part.relate_to(
        url,
        "http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink",
        is_external=True,
    )
    link = OxmlElement("w:hyperlink")
    link.set(qn("r:id"), r_id)
    run = OxmlElement("w:r")
    props = OxmlElement("w:rPr")
    colour = OxmlElement("w:color")
    colour.set(qn("w:val"), "0563C1")
    underline = OxmlElement("w:u")
    underline.set(qn("w:val"), "single")
    props.append(colour)
    props.append(underline)
    run.append(props)
    node = OxmlElement("w:t")
    node.text = text
    run.append(node)
    link.append(run)
    paragraph._p.append(link)


def _shade(cell, hex_fill: str) -> None:
    from docx.oxml.ns import qn
    from docx.oxml.shared import OxmlElement

    shading = OxmlElement("w:shd")
    shading.set(qn("w:val"), "clear")
    shading.set(qn("w:fill"), hex_fill)
    cell._tc.get_or_add_tcPr().append(shading)


def build_docx(
    *,
    year: int,
    rows: Sequence[HrPublicHoliday],
    calendars: Sequence[HrPublicHolidayCalendar],
) -> io.BytesIO:
    """Rebuild the HR source document from stored calendar data.

    Countries are ordered by their display label so the numbered sections read
    alphabetically, as in the original.
    """
    from docx import Document
    from docx.enum.text import WD_ALIGN_PARAGRAPH
    from docx.shared import Pt, RGBColor

    by_country: dict[str, list[HrPublicHoliday]] = {}
    for row in rows:
        by_country.setdefault(row.country, []).append(row)
    cal_by_country = {c.country: c for c in calendars}

    ordered = sorted(
        cal_by_country.values(),
        key=lambda c: ((c.label or c.country).lower(), c.country),
    )
    # A country with days but no calendar row should still export rather than
    # silently vanish from the document.
    for code in sorted(set(by_country) - set(cal_by_country)):
        ordered.append(
            HrPublicHolidayCalendar(country=code, year=year, label=code)
        )

    doc = Document()
    style = doc.styles["Normal"]
    style.font.name = "Calibri"
    style.font.size = Pt(10.5)

    title = doc.add_heading(
        f"PingCAP Public Holidays for Global Team Members {year}", level=0,
    )
    title.alignment = WD_ALIGN_PARAGRAPH.CENTER

    index = 0
    for calendar in ordered:
        country_rows = by_country.get(calendar.country, [])
        if not country_rows:
            continue
        index += 1
        label = calendar.label or calendar.country
        groups = [g for g in build_groups(country_rows)]

        heading = doc.add_paragraph()
        heading.paragraph_format.space_before = Pt(14)
        run = heading.add_run(_country_heading(index, label, calendar, groups))
        run.bold = True
        run.font.size = Pt(11)

        if calendar.region_note:
            advisory = doc.add_table(rows=1, cols=1)
            advisory.style = "Table Grid"
            cell = advisory.cell(0, 0)
            cell.text = calendar.region_note
            _shade(cell, "FFF7E6")
            for para in cell.paragraphs:
                for r in para.runs:
                    r.italic = True
                    r.font.size = Pt(9.5)

        table = doc.add_table(rows=1, cols=2)
        table.style = "Table Grid"
        header = table.rows[0]
        for col, text in enumerate(("Date", "Holiday")):
            cell = header.cells[col]
            cell.text = text
            _shade(cell, "F2F2F2")
            for para in cell.paragraphs:
                for r in para.runs:
                    r.bold = True

        for group in groups:
            row_cells = table.add_row().cells
            # Each date on its own line, then the adjustment sentence — exactly
            # how the source document renders a multi-day holiday.
            date_cell = row_cells[0]
            date_cell.text = ""
            first = True
            for day in group.days or group.makeup_days:
                para = date_cell.paragraphs[0] if first else date_cell.add_paragraph()
                para.add_run(long_date(day))
                first = False
            sentence = adjustment_sentence(group)
            if sentence:
                note_para = date_cell.add_paragraph()
                note_run = note_para.add_run(sentence)
                note_run.italic = True
                note_run.font.size = Pt(9)
                note_run.font.color.rgb = RGBColor(0x59, 0x59, 0x59)

            name = group.name_en
            if group.name_local:
                name = f"{group.name_local} {group.name_en}"
            if group.is_makeup_only:
                name = f"{name} (makeup workday)"
            row_cells[1].text = name
            if group.note:
                extra = row_cells[1].add_paragraph()
                extra_run = extra.add_run(group.note)
                extra_run.italic = True
                extra_run.font.size = Pt(9)

        if calendar.gcal_url:
            lead = doc.add_paragraph()
            lead.paragraph_format.space_before = Pt(6)
            lead.add_run(
                "You can click the following Google Calendar links to "
                "subscribe directly."
            )
            link_para = doc.add_paragraph()
            _add_hyperlink(
                link_para,
                calendar.gcal_url,
                f"{year} {label} Public Holiday for PingCAPer",
            )

    buf = io.BytesIO()
    doc.save(buf)
    buf.seek(0)
    return buf


def docx_response(
    buf: io.BytesIO, *, filename_stem: str, returned: int,
) -> StreamingResponse:
    stamp = datetime.now(UTC).strftime("%Y%m%d_%H%M%S")
    safe_stem = re.sub(r"[^\w.-]", "_", filename_stem) or "export"
    return StreamingResponse(
        buf,
        media_type=DOCX_MEDIA_TYPE,
        headers={
            "Content-Disposition": _content_disposition(f"{safe_stem}_{stamp}.docx"),
            "X-Export-Returned": str(returned),
            "X-Content-Type-Options": "nosniff",
        },
    )


async def docx_export_response(
    db: AsyncSession,
    request: Request,
    current_user: AuthUser,
    *,
    year: int,
    rows: Sequence[HrPublicHoliday],
    calendars: Sequence[HrPublicHolidayCalendar],
    countries: Sequence[str] | None = None,
) -> StreamingResponse:
    """Audit + build + response headers for the Word export."""
    await audit_export(
        db,
        request,
        current_user,
        action="hr.public_holiday.export_docx",
        resource_type="hr.public_holiday",
        row_count=len(rows),
        total_matched=len(rows),
        filters={"year": year, "countries": list(countries or [])},
    )
    buf = build_docx(year=year, rows=rows, calendars=calendars)
    return docx_response(
        buf, filename_stem=f"PingCAP_Public_Holidays_{year}", returned=len(rows),
    )