"""Styled .xlsx export of job requisitions — openpyxl only.

Pure module: no FastAPI imports and no HTTPException.

Takes the already-serialized rows from JobPost.fetch_jobs (serialize_job_row
dicts) so the export always matches what the Jobs screen shows, filters
included. Returns the finished workbook as bytes for a Response body.
"""

from __future__ import annotations

from datetime import datetime
from io import BytesIO

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

BRAND_DARK = "0B3D2E"    # header/banner green, matches the app chrome
BRAND_STRIPE = "EFF7F2"  # zebra row tint
BORDER_TINT = "CBDCD2"

STATUS_LABELS = {"open": "Open", "closed": "Closed", "on_hold": "On Hold", "completed": "Completed"}
STATUS_COLORS = {"open": "1B7F4B", "closed": "B3261E", "on_hold": "9A6700", "completed": "0F6E56"}

# (header, column width)
COLUMNS = [
    ("Title", 34),
    ("Department", 16),
    ("Location", 20),
    ("Type", 12),
    ("Platform", 12),
    ("Vacancies", 11),
    ("Experience", 13),
    ("Salary", 16),
    ("Status", 10),
    ("Publishing", 12),
    ("Recruiter", 18),
    ("Hiring Manager", 18),
    ("Created By", 18),
    ("Created", 13),
    ("Requirements", 46),
    ("Nice to Have", 34),
    ("Description", 60),
]

_THIN = Side(style="thin", color=BORDER_TINT)
_BORDER = Border(left=_THIN, right=_THIN, top=_THIN, bottom=_THIN)


def _experience(row) -> str:
    lo, hi = row.get("experience_min"), row.get("experience_max")
    if lo is None and hi is None:
        return ""
    if lo is not None and hi is not None:
        return f"{lo}-{hi} years"
    return f"{lo if lo is not None else hi}+ years"


def _bullets(items) -> str:
    return "\n".join(f"• {str(i).strip()}" for i in (items or []) if str(i).strip())


def _created(row):
    raw = row.get("created_at")
    if not raw:
        return None
    try:
        return datetime.fromisoformat(raw).replace(tzinfo=None)
    except ValueError:
        return None


def build_jobs_workbook(rows) -> bytes:
    wb = Workbook()
    ws = wb.active
    ws.title = "Jobs"
    ws.sheet_properties.tabColor = BRAND_DARK
    ws.sheet_view.showGridLines = False

    last_col = get_column_letter(len(COLUMNS))
    for idx, (_, width) in enumerate(COLUMNS, start=1):
        ws.column_dimensions[get_column_letter(idx)].width = width

    # Banner
    ws.merge_cells(f"A1:{last_col}1")
    banner = ws["A1"]
    banner.value = "Jobs Export"
    banner.font = Font(size=16, bold=True, color=BRAND_DARK)
    banner.alignment = Alignment(vertical="center")
    ws.row_dimensions[1].height = 30

    ws.merge_cells(f"A2:{last_col}2")
    sub = ws["A2"]
    sub.value = (
        f"TalentFlow · generated {datetime.now().strftime('%d %b %Y, %H:%M')} · "
        f"{len(rows)} requisition{'s' if len(rows) != 1 else ''}"
    )
    sub.font = Font(size=10, color="6B7A72")
    ws.row_dimensions[3].height = 6

    # Header
    header_row = 4
    for idx, (label, _) in enumerate(COLUMNS, start=1):
        cell = ws.cell(row=header_row, column=idx, value=label)
        cell.font = Font(bold=True, color="FFFFFF", size=11)
        cell.fill = PatternFill("solid", fgColor=BRAND_DARK)
        cell.alignment = Alignment(horizontal="center", vertical="center")
        cell.border = _BORDER
    ws.row_dimensions[header_row].height = 22

    # Data
    top = Alignment(vertical="top", wrap_text=False)
    wrap = Alignment(vertical="top", wrap_text=True)
    center = Alignment(horizontal="center", vertical="top")
    for r, row in enumerate(rows, start=header_row + 1):
        status_key = row.get("requisition_status") or ""
        values = [
            row.get("title") or "",
            row.get("department") or "",
            row.get("location") or "",
            row.get("employment_type") or "",
            row.get("platform") or "",
            row.get("vacancies"),
            _experience(row),
            row.get("salary") or "",
            STATUS_LABELS.get(status_key, status_key),
            row.get("status") or "",
            row.get("recruiter_name") or "",
            row.get("hiring_manager_name") or "",
            row.get("created_by_name") or "",
            _created(row),
            _bullets(row.get("requirements")),
            _bullets(row.get("optional_skills")),
            (row.get("description") or "").strip(),
        ]
        stripe = r % 2 == 0
        for c, value in enumerate(values, start=1):
            cell = ws.cell(row=r, column=c, value=value)
            cell.border = _BORDER
            cell.alignment = top
            if stripe:
                cell.fill = PatternFill("solid", fgColor=BRAND_STRIPE)
        ws.cell(row=r, column=1).font = Font(bold=True)
        ws.cell(row=r, column=6).alignment = center
        status_cell = ws.cell(row=r, column=9)
        status_cell.alignment = center
        if status_key in STATUS_COLORS:
            status_cell.font = Font(bold=True, color=STATUS_COLORS[status_key])
        created_cell = ws.cell(row=r, column=14)
        if created_cell.value is not None:
            created_cell.number_format = "dd mmm yyyy"
        for c in (14, 15, 16):
            ws.cell(row=r, column=c).alignment = wrap

    last_row = header_row + max(len(rows), 1)
    ws.auto_filter.ref = f"A{header_row}:{last_col}{last_row}"
    ws.freeze_panes = f"A{header_row + 1}"

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