"""CSV and XLSX rendering for report tables.

Both formats serialize the *same* list of row dicts through the same column spec, so a
spreadsheet and a CSV of one report can never disagree about which columns exist or what
order they are in.

Values arrive as they are stored — timestamps aware and in UTC — so this module is where
they become Asia/Dhaka. A spreadsheet has no timezone to carry an offset in, and a cell
reading `14:30` must mean half past two in Dhaka.
"""
import csv
import io
import re
from datetime import date, datetime
from decimal import Decimal
from enum import Enum

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

from app.core.time import to_local

CSV_MEDIA_TYPE = "text/csv; charset=utf-8"
XLSX_MEDIA_TYPE = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"

#: Excel opens a UTF-8 CSV as Windows-1252 unless the file starts with a BOM, which
#: mangles every Bangla name in an address column. The BOM costs three bytes.
BOM = ""

DATETIME_FORMAT = "%Y-%m-%d %H:%M"
DATE_FORMAT = "%Y-%m-%d"

_FILENAME_SAFE = re.compile(r"[^A-Za-z0-9._-]+")


def _dhaka_naive(value: datetime) -> datetime:
    """Dhaka wall-clock time, stripped of its offset — a spreadsheet cannot hold one."""
    return to_local(value).replace(tzinfo=None)


def _text(value) -> str:
    """One cell, as a human would want to read it."""
    if value is None:
        return ""
    if isinstance(value, Enum):
        return value.value.replace("_", " ").title()
    if isinstance(value, bool):
        return "Yes" if value else "No"
    if isinstance(value, datetime):
        return _dhaka_naive(value).strftime(DATETIME_FORMAT)
    if isinstance(value, date):
        return value.strftime(DATE_FORMAT)
    if isinstance(value, Decimal):
        return f"{value:.2f}"
    return str(value)


def _cell(value):
    """Same value for Excel, but keeping the native type where Excel understands it.

    Numbers and dates stay real so a user can sort and sum them; enums and booleans are
    flattened to the text a reader expects.
    """
    if isinstance(value, (Enum, bool)) or value is None:
        return _text(value) or None
    if isinstance(value, Decimal):
        return float(value)
    if isinstance(value, datetime):
        return _dhaka_naive(value)
    if isinstance(value, (int, float, date)):
        return value
    return str(value)


def filename(stem: str, extension: str) -> str:
    """`stem` comes from user-supplied dates, so strip anything that isn't path-safe."""
    return f"{_FILENAME_SAFE.sub('-', stem).strip('-') or 'report'}.{extension}"


def to_csv(rows: list[dict], columns: list[tuple[str, str]]) -> bytes:
    """`columns` is [(key, header)] — the same spec both writers consume."""
    buffer = io.StringIO()
    buffer.write(BOM)
    writer = csv.writer(buffer, lineterminator="\r\n")
    writer.writerow([header for _, header in columns])
    for row in rows:
        writer.writerow([_text(row.get(key)) for key, _ in columns])
    return buffer.getvalue().encode("utf-8")


def to_xlsx(rows: list[dict], columns: list[tuple[str, str]], *, sheet_name: str) -> bytes:
    """A real workbook: frozen bold header, autofilter, and columns wide enough to read."""
    workbook = Workbook()
    sheet = workbook.active
    # Excel rejects sheet names over 31 chars or containing []:*?/\.
    sheet.title = _FILENAME_SAFE.sub(" ", sheet_name)[:31] or "Report"

    header_fill = PatternFill("solid", fgColor="1E293B")
    sheet.append([header for _, header in columns])
    for cell in sheet[1]:
        cell.font = Font(bold=True, color="FFFFFF")
        cell.fill = header_fill
        cell.alignment = Alignment(vertical="center")

    for row in rows:
        sheet.append([_cell(row.get(key)) for key, _ in columns])

    for index, (key, header) in enumerate(columns, start=1):
        letter = get_column_letter(index)
        # Width from the widest of the header and the first 200 rows — scanning every
        # row of a 10k-row export to size a column is not worth the wait.
        widest = max(
            [len(header)] + [len(_text(row.get(key))) for row in rows[:200]]
        )
        sheet.column_dimensions[letter].width = min(max(widest + 2, 10), 50)

        # openpyxl stores a `date` as a midnight datetime, so a date column shows a
        # useless "00:00" unless it is told to format as a date.
        sample = next((row[key] for row in rows[:200] if row.get(key) is not None), None)
        if isinstance(sample, datetime):
            fmt = "yyyy-mm-dd hh:mm"
        elif isinstance(sample, date):
            fmt = "yyyy-mm-dd"
        else:
            continue
        for cell in sheet[letter][1:]:
            cell.number_format = fmt

    sheet.freeze_panes = "A2"
    sheet.auto_filter.ref = f"A1:{get_column_letter(len(columns))}{len(rows) + 1}"

    buffer = io.BytesIO()
    workbook.save(buffer)
    return buffer.getvalue()