"""Report export rendering — PDF (fpdf2), Excel (openpyxl) and CSV.

All string values are sanitised before rendering to neutralise CSV/Excel formula
injection (OWASP A08 / API10) and to strip null bytes and control characters.
Generation is fully in-memory: no user-supplied URL is ever fetched (OWASP A10/API7).
"""
from __future__ import annotations

import csv
import io
import re
from datetime import date, datetime, timezone
from typing import Any, AsyncGenerator, Iterable, Optional
from uuid import UUID
from xml.sax.saxutils import escape as _xml_escape

from sqlalchemy.ext.asyncio import AsyncSession

from ..constants import VALID_REPORT_TYPES
from .data import ReportDataService, ReportResult

# Leading characters that spreadsheet apps interpret as a formula.
_FORMULA_PREFIXES = ("=", "+", "-", "@", "\t", "\r")
# Characters Excel disallows in a worksheet name.
_INVALID_SHEET = re.compile(r"[\\/?*\[\]:]")

# Order the reports appear in the bulk CSV export.
BULK_REPORT_ORDER = [
    "monthly-sales", "burial-register", "capacity", "revenue-by-section",
    "outstanding-balances", "service-schedule", "audit-log", "memorial-status",
]


def _sanitize_str(value: str) -> str:
    """Strip null bytes / control chars and defuse leading formula characters."""
    value = value.replace("\x00", "")
    value = "".join(ch for ch in value if ch in ("\n", "\t") or ord(ch) >= 32)
    if value and value[0] in _FORMULA_PREFIXES:
        value = "\t" + value
    return value


def sanitize_cell(value: Any) -> Any:
    """Return a cell value safe for Excel: numbers stay numeric, strings sanitised."""
    if value is None:
        return ""
    if isinstance(value, bool):
        return str(value)
    if isinstance(value, (int, float)):
        return value
    return _sanitize_str(str(value))


def _csv_cell(value: Any) -> str:
    if value is None:
        return ""
    return _sanitize_str(str(value))


def _pdf_cell(value: Any) -> str:
    """Sanitise then XML-escape a value for a reportlab Paragraph."""
    if value is None:
        return ""
    return _xml_escape(_sanitize_str(str(value)))


class ReportExportService:
    """Renders a ReportResult (or several) into PDF / Excel / CSV byte streams."""

    # ── PDF ──────────────────────────────────────────────────────────────────
    def generate_pdf(self, result: ReportResult) -> io.BytesIO:
        from reportlab.lib import colors
        from reportlab.lib.pagesizes import A4, landscape
        from reportlab.lib.styles import ParagraphStyle, getSampleStyleSheet
        from reportlab.lib.units import mm
        from reportlab.platypus import (
            Paragraph, SimpleDocTemplate, Spacer, Table, TableStyle,
        )

        buffer = io.BytesIO()
        page_size = landscape(A4)
        margin = 12 * mm
        doc = SimpleDocTemplate(
            buffer, pagesize=page_size,
            leftMargin=margin, rightMargin=margin,
            topMargin=14 * mm, bottomMargin=12 * mm,
            title=result.title,
        )
        styles = getSampleStyleSheet()
        generated = datetime.now(timezone.utc).strftime("%Y-%m-%d %H:%M UTC")

        elements: list = [
            Paragraph(_pdf_cell(result.title), styles["Title"]),
            Paragraph(
                f"Generated {generated}  |  {len(result.rows)} rows",
                styles["Normal"],
            ),
            Spacer(1, 8),
        ]

        columns = result.columns or ["(no columns)"]
        if result.rows:
            header_style = ParagraphStyle(
                "cell_header", fontName="Helvetica-Bold", fontSize=7,
                leading=8, textColor=colors.white,
            )
            cell_style = ParagraphStyle(
                "cell_body", fontName="Helvetica", fontSize=7, leading=8,
            )
            table_data = [[Paragraph(_pdf_cell(c), header_style) for c in columns]]
            for row in result.rows:
                table_data.append([Paragraph(_pdf_cell(v), cell_style) for v in row])

            avail_width = page_size[0] - 2 * margin
            col_w = avail_width / len(columns)
            table = Table(
                table_data, colWidths=[col_w] * len(columns), repeatRows=1,
            )
            table.setStyle(TableStyle([
                ("BACKGROUND", (0, 0), (-1, 0), colors.HexColor("#1E4A6E")),
                ("GRID", (0, 0), (-1, -1), 0.4, colors.HexColor("#B8C4CE")),
                ("VALIGN", (0, 0), (-1, -1), "TOP"),
                ("LEFTPADDING", (0, 0), (-1, -1), 3),
                ("RIGHTPADDING", (0, 0), (-1, -1), 3),
                ("TOPPADDING", (0, 0), (-1, -1), 2),
                ("BOTTOMPADDING", (0, 0), (-1, -1), 2),
                ("ROWBACKGROUNDS", (0, 1), (-1, -1),
                 [colors.white, colors.HexColor("#F3F6F9")]),
            ]))
            elements.append(table)
        else:
            elements.append(
                Paragraph("No data for the selected filters.", styles["Italic"])
            )

        doc.build(elements)
        buffer.seek(0)
        return buffer

    # ── Excel ────────────────────────────────────────────────────────────────
    def generate_excel(self, result: ReportResult) -> io.BytesIO:
        import openpyxl
        from openpyxl.styles import Font

        wb = openpyxl.Workbook()
        ws = wb.active
        ws.title = (_INVALID_SHEET.sub("", result.title) or "Report")[:31]

        ws.append([sanitize_cell(c) for c in result.columns])
        for cell in ws[1]:
            cell.font = Font(bold=True)

        for row in result.rows:
            ws.append([sanitize_cell(v) for v in row])

        # Reasonable column widths.
        for idx, col in enumerate(result.columns, start=1):
            ws.column_dimensions[
                openpyxl.utils.get_column_letter(idx)
            ].width = min(max(len(str(col)) + 4, 12), 40)

        buffer = io.BytesIO()
        wb.save(buffer)
        buffer.seek(0)
        return buffer

    # ── CSV (single report) ────────────────────────────────────────────────
    def generate_csv(self, result: ReportResult) -> Iterable[str]:
        buf = io.StringIO()
        writer = csv.writer(buf)

        writer.writerow([_csv_cell(c) for c in result.columns])
        yield buf.getvalue()
        buf.truncate(0)
        buf.seek(0)

        for row in result.rows:
            writer.writerow([_csv_cell(v) for v in row])
            yield buf.getvalue()
            buf.truncate(0)
            buf.seek(0)

    # ── Bulk CSV (all report types combined) ──────────────────────────────────
    async def generate_bulk_csv(
        self,
        db: AsyncSession,
        *,
        tenant_id: str,
        from_date: date,
        to_date: date,
        section_id: Optional[UUID] = None,
        report_types: Optional[list[str]] = None,
    ) -> AsyncGenerator[str, None]:
        data_service = ReportDataService()
        types = [t for t in (report_types or BULK_REPORT_ORDER) if t in VALID_REPORT_TYPES]

        buf = io.StringIO()
        writer = csv.writer(buf)

        def _flush() -> str:
            out = buf.getvalue()
            buf.truncate(0)
            buf.seek(0)
            return out

        for report_type in types:
            result = await data_service.build(
                db, report_type,
                tenant_id=tenant_id,
                from_date=from_date,
                to_date=to_date,
                section_id=section_id,
            )
            # Section marker — prefixed with '#' so it never begins with '='.
            writer.writerow([f"## {result.title}"])
            writer.writerow([_csv_cell(c) for c in result.columns])
            yield _flush()
            for row in result.rows:
                writer.writerow([_csv_cell(v) for v in row])
                yield _flush()
            writer.writerow([])
            yield _flush()