reporter.py

"""
reporter.py — PDF report generation for the Excel Workbook Risk Diagnostic Tool.

Produces a structured multi-page PDF report using ReportLab Platypus.

Report structure:
    1. Cover page — filename, timestamp, RAG badge, finding counts
    2. Executive summary — auto-generated plain-English paragraph
    3. Findings by category — one section per checker category
    4. High-sensitivity sheets summary
    5. Appendix — full tabular listing sorted by severity descending
"""

from __future__ import annotations

import io
from collections import Counter, defaultdict
from datetime import datetime
from pathlib import Path

from reportlab.lib import colors
from reportlab.lib.enums import TA_CENTER, TA_LEFT, TA_RIGHT
from reportlab.lib.pagesizes import A4
from reportlab.lib.styles import ParagraphStyle, getSampleStyleSheet
from reportlab.lib.units import cm, mm
from reportlab.platypus import (
    HRFlowable,
    NextPageTemplate,
    PageBreak,
    Paragraph,
    SimpleDocTemplate,
    Spacer,
    Table,
    TableStyle,
)
from reportlab.platypus.frames import Frame
from reportlab.platypus.doctemplate import PageTemplate, BaseDocTemplate

import settings
from models import Finding, WorkbookAnalysisResult, AICommentary

# ---------------------------------------------------------------------------
# Colour palette
# ---------------------------------------------------------------------------

_COLOUR_RED = colors.HexColor("#C0392B")
_COLOUR_AMBER = colors.HexColor("#E67E22")
_COLOUR_GREEN = colors.HexColor("#27AE60")
_COLOUR_HIGH = colors.HexColor("#FADBD8")       # Light red background
_COLOUR_HIGH_BORDER = colors.HexColor("#C0392B")
_COLOUR_MEDIUM = colors.HexColor("#FDEBD0")     # Light orange background
_COLOUR_MEDIUM_BORDER = colors.HexColor("#E67E22")
_COLOUR_LOW = colors.HexColor("#D5F5E3")        # Light green background
_COLOUR_LOW_BORDER = colors.HexColor("#27AE60")
_COLOUR_HEADER_BG = colors.HexColor("#2C3E50")  # Dark slate header
_COLOUR_HEADER_TEXT = colors.white
_COLOUR_ROW_ALT = colors.HexColor("#F8F9FA")    # Alternate row tint
_COLOUR_LIGHT_GREY = colors.HexColor("#ECF0F1")
_COLOUR_TEXT = colors.HexColor("#2C3E50")
_COLOUR_AI_BLUE = colors.HexColor("#EBF4FF")    # AI purpose/sheets background
_COLOUR_AI_YELLOW = colors.HexColor("#FFFDE7")  # AI assumptions background
_COLOUR_AI_LABEL = colors.HexColor("#7F8C8D")   # AI label text (grey)

# ---------------------------------------------------------------------------
# Page dimensions
# ---------------------------------------------------------------------------

PAGE_WIDTH, PAGE_HEIGHT = A4
LEFT_MARGIN = 2 * cm
RIGHT_MARGIN = 2 * cm
TOP_MARGIN = 2 * cm
BOTTOM_MARGIN = 2 * cm

# ---------------------------------------------------------------------------
# Styles
# ---------------------------------------------------------------------------

_BASE_STYLES = getSampleStyleSheet()


def _build_styles() -> dict[str, ParagraphStyle]:
    """
    Build and return a dictionary of named ParagraphStyle objects used
    throughout the report.

    Returns:
        Dict mapping style name to ParagraphStyle.
    """
    styles: dict[str, ParagraphStyle] = {}

    styles["cover_title"] = ParagraphStyle(
        "cover_title",
        fontName="Helvetica-Bold",
        fontSize=22,
        textColor=_COLOUR_TEXT,
        spaceAfter=8,
        alignment=TA_CENTER,
    )
    styles["cover_subtitle"] = ParagraphStyle(
        "cover_subtitle",
        fontName="Helvetica",
        fontSize=12,
        textColor=colors.HexColor("#7F8C8D"),
        spaceAfter=4,
        alignment=TA_CENTER,
    )
    styles["cover_rag"] = ParagraphStyle(
        "cover_rag",
        fontName="Helvetica-Bold",
        fontSize=28,
        textColor=colors.white,
        alignment=TA_CENTER,
    )
    styles["section_heading"] = ParagraphStyle(
        "section_heading",
        fontName="Helvetica-Bold",
        fontSize=14,
        textColor=_COLOUR_TEXT,
        spaceBefore=14,
        spaceAfter=6,
        borderPad=4,
    )
    styles["subsection_heading"] = ParagraphStyle(
        "subsection_heading",
        fontName="Helvetica-Bold",
        fontSize=11,
        textColor=_COLOUR_TEXT,
        spaceBefore=10,
        spaceAfter=4,
    )
    styles["body"] = ParagraphStyle(
        "body_text",
        fontName="Helvetica",
        fontSize=9,
        textColor=_COLOUR_TEXT,
        spaceAfter=4,
        leading=13,
    )
    styles["body_italic"] = ParagraphStyle(
        "body_italic",
        fontName="Helvetica-Oblique",
        fontSize=9,
        textColor=colors.HexColor("#7F8C8D"),
        spaceAfter=4,
        leading=13,
    )
    styles["cell_small"] = ParagraphStyle(
        "cell_small",
        fontName="Helvetica",
        fontSize=8,
        textColor=_COLOUR_TEXT,
        leading=10,
        wordWrap="CJK",
    )
    styles["cell_small_bold"] = ParagraphStyle(
        "cell_small_bold",
        fontName="Helvetica-Bold",
        fontSize=8,
        textColor=_COLOUR_TEXT,
        leading=10,
    )
    styles["cell_mono"] = ParagraphStyle(
        "cell_mono",
        fontName="Courier",
        fontSize=7,
        textColor=_COLOUR_TEXT,
        leading=9,
        wordWrap="CJK",
    )
    styles["table_header"] = ParagraphStyle(
        "table_header",
        fontName="Helvetica-Bold",
        fontSize=8,
        textColor=colors.white,
        leading=10,
    )
    return styles


_STYLES = _build_styles()


# ---------------------------------------------------------------------------
# RAG helpers
# ---------------------------------------------------------------------------

def _rag_colour(rag: str) -> colors.Color:
    """
    Map a RAG rating string to its ReportLab colour.

    Args:
        rag: "Red", "Amber", or "Green".

    Returns:
        ReportLab Color object.
    """
    return {"Red": _COLOUR_RED, "Amber": _COLOUR_AMBER, "Green": _COLOUR_GREEN}.get(
        rag, _COLOUR_LIGHT_GREY
    )


def _severity_bg_colour(severity: str) -> colors.Color:
    """Return the background colour for a severity badge cell."""
    return {
        "High": _COLOUR_HIGH,
        "Medium": _COLOUR_MEDIUM,
        "Low": _COLOUR_LOW,
    }.get(severity, _COLOUR_LIGHT_GREY)


def _severity_text_colour(severity: str) -> colors.Color:
    """Return the text/border colour for a severity badge."""
    return {
        "High": _COLOUR_HIGH_BORDER,
        "Medium": _COLOUR_MEDIUM_BORDER,
        "Low": _COLOUR_LOW_BORDER,
    }.get(severity, _COLOUR_TEXT)


# ---------------------------------------------------------------------------
# Executive summary generator
# ---------------------------------------------------------------------------

def _generate_executive_summary(result: WorkbookAnalysisResult) -> str:
    """
    Build a plain-English executive summary paragraph from the analysis
    findings. Generated programmatically from finding aggregates — no
    hardcoded template text.

    Args:
        result: The completed WorkbookAnalysisResult.

    Returns:
        Multi-sentence summary string.
    """
    if result.error_message:
        return (
            f"This workbook could not be analysed: {result.error_message} "
            "No automated findings are available."
        )

    counts = result.count_by_severity()
    total = len(result.findings)
    n_high = counts["High"]
    n_medium = counts["Medium"]
    n_low = counts["Low"]

    # Sentence 1: overall finding count
    if total == 0:
        return (
            f"No risk findings were detected in '{result.filename}'. "
            f"The workbook achieved a risk score of {result.total_score} "
            f"({result.rag_rating} rating)."
        )

    parts: list[str] = []

    parts.append(
        f"Analysis of '{result.filename}' identified {total} finding(s): "
        f"{n_high} High, {n_medium} Medium, and {n_low} Low severity. "
        f"The overall risk score is {result.total_score} "
        f"({result.rag_rating} rating)."
    )

    # Sentence 2: top categories
    cat_counts: Counter = Counter(f.category for f in result.findings)
    top_cats = [cat for cat, _ in cat_counts.most_common(3)]
    if top_cats:
        parts.append(
            f"The most prevalent risk categories are: "
            f"{', '.join(top_cats)}."
        )

    # Sentence 3: high-severity specific mention
    if n_high > 0:
        high_cats = Counter(
            f.category for f in result.findings if f.severity == "High"
        )
        top_high = [cat for cat, _ in high_cats.most_common(2)]
        parts.append(
            f"Immediate attention is recommended for {n_high} High-severity "
            f"finding(s), particularly in the "
            f"{' and '.join(top_high)} category(ies)."
        )

    # Sentence 4: sensitive sheets
    if result.high_sensitivity_sheets:
        sheets = ", ".join(f"'{s}'" for s in result.high_sensitivity_sheets[:4])
        if len(result.high_sensitivity_sheets) > 4:
            sheets += f" and {len(result.high_sensitivity_sheets) - 4} more"
        parts.append(
            f"The following sheets were identified as actuarially sensitive "
            f"and warrant heightened review: {sheets}."
        )

    # Sentence 5: VBA note
    vba_findings = [f for f in result.findings if f.category == "VBA"]
    if vba_findings:
        dangerous = [f for f in vba_findings if f.severity == "High"]
        if dangerous:
            parts.append(
                f"The workbook contains VBA macros with {len(dangerous)} "
                f"High-severity finding(s) including potentially dangerous "
                f"commands or error suppression. VBA code should be reviewed "
                f"by a developer before the model is used in production."
            )
        else:
            parts.append(
                "The workbook contains VBA macros. While no dangerous commands "
                "were detected, all macro code should be reviewed and documented."
            )

    return " ".join(parts)


# ---------------------------------------------------------------------------
# Table builders
# ---------------------------------------------------------------------------

def _make_summary_table(result: WorkbookAnalysisResult) -> Table:
    """
    Build a 2×4 summary table showing finding counts by severity.

    Args:
        result: WorkbookAnalysisResult.

    Returns:
        ReportLab Table flowable.
    """
    counts = result.count_by_severity()
    data = [
        [
            Paragraph("Severity", _STYLES["table_header"]),
            Paragraph("Count", _STYLES["table_header"]),
            Paragraph("Score Contribution", _STYLES["table_header"]),
        ],
        [
            Paragraph("High", _STYLES["cell_small_bold"]),
            Paragraph(str(counts["High"]), _STYLES["cell_small"]),
            Paragraph(
                str(counts["High"] * settings.SEVERITY_WEIGHTS["High"]),
                _STYLES["cell_small"],
            ),
        ],
        [
            Paragraph("Medium", _STYLES["cell_small_bold"]),
            Paragraph(str(counts["Medium"]), _STYLES["cell_small"]),
            Paragraph(
                str(counts["Medium"] * settings.SEVERITY_WEIGHTS["Medium"]),
                _STYLES["cell_small"],
            ),
        ],
        [
            Paragraph("Low", _STYLES["cell_small_bold"]),
            Paragraph(str(counts["Low"]), _STYLES["cell_small"]),
            Paragraph(
                str(counts["Low"] * settings.SEVERITY_WEIGHTS["Low"]),
                _STYLES["cell_small"],
            ),
        ],
        [
            Paragraph("Total", _STYLES["cell_small_bold"]),
            Paragraph(str(len(result.findings)), _STYLES["cell_small_bold"]),
            Paragraph(str(result.total_score), _STYLES["cell_small_bold"]),
        ],
    ]

    col_widths = [4 * cm, 3 * cm, 5 * cm]
    t = Table(data, colWidths=col_widths, repeatRows=1)
    t.setStyle(TableStyle([
        ("BACKGROUND", (0, 0), (-1, 0), _COLOUR_HEADER_BG),
        ("TEXTCOLOR", (0, 0), (-1, 0), colors.white),
        ("BACKGROUND", (0, 1), (-1, 1), _COLOUR_HIGH),
        ("BACKGROUND", (0, 2), (-1, 2), _COLOUR_MEDIUM),
        ("BACKGROUND", (0, 3), (-1, 3), _COLOUR_LOW),
        ("BACKGROUND", (0, 4), (-1, 4), _COLOUR_LIGHT_GREY),
        ("FONTNAME", (0, 4), (-1, 4), "Helvetica-Bold"),
        ("GRID", (0, 0), (-1, -1), 0.5, colors.HexColor("#BDC3C7")),
        ("VALIGN", (0, 0), (-1, -1), "MIDDLE"),
        ("ROWBACKGROUNDS", (0, 0), (-1, -1), [None]),  # Disable auto alternation
    ]))
    return t


def _make_findings_table(findings: list[Finding]) -> Table:
    """
    Build a detailed findings table with columns:
    Severity | Sheet | Cell | Finding Name | Description | Explanation

    Args:
        findings: List of Finding objects to include.

    Returns:
        ReportLab Table flowable.
    """
    if not findings:
        return Table([[Paragraph("No findings in this category.", _STYLES["body_italic"])]])

    header = [
        Paragraph("Sev.", _STYLES["table_header"]),
        Paragraph("Sheet", _STYLES["table_header"]),
        Paragraph("Cell", _STYLES["table_header"]),
        Paragraph("Finding", _STYLES["table_header"]),
        Paragraph("Description", _STYLES["table_header"]),
    ]

    usable_width = PAGE_WIDTH - LEFT_MARGIN - RIGHT_MARGIN
    col_widths = [
        1.4 * cm,   # Severity
        3.2 * cm,   # Sheet
        1.5 * cm,   # Cell
        3.5 * cm,   # Finding name
        usable_width - (1.4 + 3.2 + 1.5 + 3.5) * cm,  # Description (remainder)
    ]

    rows = [header]
    style_commands = [
        ("BACKGROUND", (0, 0), (-1, 0), _COLOUR_HEADER_BG),
        ("TEXTCOLOR", (0, 0), (-1, 0), colors.white),
        ("FONTNAME", (0, 0), (-1, 0), "Helvetica-Bold"),
        ("FONTSIZE", (0, 0), (-1, 0), 8),
        ("GRID", (0, 0), (-1, -1), 0.3, colors.HexColor("#BDC3C7")),
        ("VALIGN", (0, 0), (-1, -1), "TOP"),
        ("ROWBACKGROUNDS", (1, 1), (-1, -1), [colors.white, _COLOUR_ROW_ALT]),
    ]

    severity_order = {"High": 0, "Medium": 1, "Low": 2}
    sorted_findings = sorted(
        findings,
        key=lambda f: (severity_order.get(f.severity, 3), f.check_id),
    )

    for i, f in enumerate(sorted_findings, start=1):
        sev_para = Paragraph(
            f"<b>{f.severity}</b>", _STYLES["cell_small_bold"]
        )
        sheet_para = Paragraph(
            _escape_xml(f.sheet_name[:30]), _STYLES["cell_small"]
        )
        cell_para = Paragraph(
            _escape_xml(f.cell_ref[:12]), _STYLES["cell_mono"]
        )
        name_para = Paragraph(
            _escape_xml(f.name), _STYLES["cell_small_bold"]
        )
        desc_para = Paragraph(
            _escape_xml(f.description[:300]), _STYLES["cell_small"]
        )

        rows.append([sev_para, sheet_para, cell_para, name_para, desc_para])

        bg = _severity_bg_colour(f.severity)
        style_commands.append(("BACKGROUND", (0, i), (0, i), bg))
        style_commands.append(
            ("TEXTCOLOR", (0, i), (0, i), _severity_text_colour(f.severity))
        )

    t = Table(rows, colWidths=col_widths, repeatRows=1)
    t.setStyle(TableStyle(style_commands))
    return t


def _make_appendix_table(findings: list[Finding]) -> Table:
    """
    Build the full appendix table with all findings sorted by severity
    descending, then check_id ascending.

    Args:
        findings: All findings from the analysis result.

    Returns:
        ReportLab Table flowable.
    """
    severity_order = {"High": 0, "Medium": 1, "Low": 2}
    sorted_findings = sorted(
        findings,
        key=lambda f: (severity_order.get(f.severity, 3), f.check_id),
    )

    header = [
        Paragraph("#", _STYLES["table_header"]),
        Paragraph("Severity", _STYLES["table_header"]),
        Paragraph("Category", _STYLES["table_header"]),
        Paragraph("Sheet", _STYLES["table_header"]),
        Paragraph("Cell", _STYLES["table_header"]),
        Paragraph("Finding", _STYLES["table_header"]),
        Paragraph("Description", _STYLES["table_header"]),
    ]

    usable_width = PAGE_WIDTH - LEFT_MARGIN - RIGHT_MARGIN
    col_widths = [
        0.7 * cm,   # #
        1.5 * cm,   # Severity
        2.0 * cm,   # Category
        2.8 * cm,   # Sheet
        1.4 * cm,   # Cell
        3.0 * cm,   # Finding name
        usable_width - (0.7 + 1.5 + 2.0 + 2.8 + 1.4 + 3.0) * cm,  # Description
    ]

    rows = [header]
    style_commands = [
        ("BACKGROUND", (0, 0), (-1, 0), _COLOUR_HEADER_BG),
        ("TEXTCOLOR", (0, 0), (-1, 0), colors.white),
        ("GRID", (0, 0), (-1, -1), 0.3, colors.HexColor("#BDC3C7")),
        ("VALIGN", (0, 0), (-1, -1), "TOP"),
        ("FONTSIZE", (0, 0), (-1, -1), 7),
        ("ROWBACKGROUNDS", (1, 1), (-1, -1), [colors.white, _COLOUR_ROW_ALT]),
    ]

    for i, f in enumerate(sorted_findings, start=1):
        rows.append([
            Paragraph(str(i), _STYLES["cell_small"]),
            Paragraph(f"<b>{f.severity}</b>", _STYLES["cell_small_bold"]),
            Paragraph(_escape_xml(f.category), _STYLES["cell_small"]),
            Paragraph(_escape_xml(f.sheet_name[:25]), _STYLES["cell_small"]),
            Paragraph(_escape_xml(f.cell_ref[:10]), _STYLES["cell_mono"]),
            Paragraph(_escape_xml(f.name[:40]), _STYLES["cell_small_bold"]),
            Paragraph(_escape_xml(f.description[:250]), _STYLES["cell_small"]),
        ])
        bg = _severity_bg_colour(f.severity)
        style_commands.append(("BACKGROUND", (1, i), (1, i), bg))

    t = Table(rows, colWidths=col_widths, repeatRows=1)
    t.setStyle(TableStyle(style_commands))
    return t


# ---------------------------------------------------------------------------
# XML escaping helper
# ---------------------------------------------------------------------------

def _escape_xml(text: str) -> str:
    """
    Escape characters that would break ReportLab's XML-like Paragraph parser.

    Args:
        text: Raw string.

    Returns:
        String safe for use in Paragraph() content.
    """
    return (
        str(text)
        .replace("&", "&amp;")
        .replace("<", "&lt;")
        .replace(">", "&gt;")
    )


# ---------------------------------------------------------------------------
# AI disclaimer paragraph
# ---------------------------------------------------------------------------

_AI_DISCLAIMER = (
    "AI commentary is generated from formula metadata and structural analysis. "
    "It is interpretive and may not fully reflect model intent. Always validate "
    "with the model owner."
)


def _ai_label_para() -> Paragraph:
    """Return the 'AI-Generated — Interpretive Only' italic label."""
    return Paragraph(
        "<i>AI-Generated — Interpretive Only</i>",
        ParagraphStyle(
            "ai_label",
            fontName="Helvetica-Oblique",
            fontSize=8,
            textColor=_COLOUR_AI_LABEL,
            spaceAfter=2,
        ),
    )


def _ai_disclaimer_para() -> Paragraph:
    """Return the standard AI disclaimer paragraph."""
    return Paragraph(
        f"<i>{_escape_xml(_AI_DISCLAIMER)}</i>",
        ParagraphStyle(
            "ai_disclaimer",
            fontName="Helvetica-Oblique",
            fontSize=7,
            textColor=_COLOUR_AI_LABEL,
            spaceBefore=4,
            spaceAfter=4,
        ),
    )


def _coloured_box(text: str, bg_colour: colors.Color) -> Table:
    """Render a paragraph of text inside a coloured background box."""
    inner = Paragraph(
        _escape_xml(text[:1500]),
        ParagraphStyle(
            "ai_box_text",
            fontName="Helvetica",
            fontSize=9,
            textColor=_COLOUR_TEXT,
            leading=13,
        ),
    )
    usable = PAGE_WIDTH - LEFT_MARGIN - RIGHT_MARGIN
    t = Table([[inner]], colWidths=[usable])
    t.setStyle(TableStyle([
        ("BACKGROUND", (0, 0), (0, 0), bg_colour),
        ("BOX", (0, 0), (0, 0), 0.5, colors.HexColor("#BDC3C7")),
        ("LEFTPADDING", (0, 0), (0, 0), 8),
        ("RIGHTPADDING", (0, 0), (0, 0), 8),
        ("TOPPADDING", (0, 0), (0, 0), 6),
        ("BOTTOMPADDING", (0, 0), (0, 0), 6),
    ]))
    return t


def _build_ai_section(result: WorkbookAnalysisResult, story: list) -> None:
    """
    Append the 'AI Workbook Intelligence' section to the story.
    Only called when result.ai_commentary is not None.
    """
    ai = result.ai_commentary
    if ai is None:
        return

    has_content = any([
        ai.workbook_purpose,
        ai.sheet_narratives,
        ai.assumption_commentary,
        ai.formula_explanations,
    ])
    if not has_content:
        return

    story.append(PageBreak())
    story.append(Paragraph("AI Workbook Intelligence", _STYLES["section_heading"]))
    story.append(HRFlowable(width="100%", thickness=1, color=_COLOUR_LIGHT_GREY))
    story.append(Spacer(1, 0.2 * cm))

    # 1. Workbook Purpose
    if ai.workbook_purpose:
        story.append(Paragraph("Workbook Purpose", _STYLES["subsection_heading"]))
        story.append(_ai_label_para())
        story.append(_coloured_box(ai.workbook_purpose, _COLOUR_AI_BLUE))
        story.append(_ai_disclaimer_para())
        story.append(Spacer(1, 0.3 * cm))

    # 2. Sheet Narratives — two-column table
    if ai.sheet_narratives:
        story.append(Paragraph("Sheet-by-Sheet Narratives", _STYLES["subsection_heading"]))
        story.append(_ai_label_para())

        sensitive_names = set(result.high_sensitivity_sheets)
        usable = PAGE_WIDTH - LEFT_MARGIN - RIGHT_MARGIN
        col_w_name = usable * 0.30
        col_w_narr = usable * 0.70

        header_row = [
            Paragraph("Sheet", _STYLES["table_header"]),
            Paragraph("Narrative", _STYLES["table_header"]),
        ]
        rows = [header_row]
        style_cmds = [
            ("BACKGROUND", (0, 0), (-1, 0), _COLOUR_HEADER_BG),
            ("GRID", (0, 0), (-1, -1), 0.3, colors.HexColor("#BDC3C7")),
            ("VALIGN", (0, 0), (-1, -1), "TOP"),
            ("ROWBACKGROUNDS", (1, 1), (-1, -1), [colors.white, _COLOUR_AI_BLUE]),
        ]

        for sn in ai.sheet_narratives:
            name_style = _STYLES["cell_small_bold"] if sn.sheet_name in sensitive_names else _STYLES["cell_small"]
            rows.append([
                Paragraph(_escape_xml(sn.sheet_name), name_style),
                Paragraph(_escape_xml(sn.narrative[:800]), _STYLES["cell_small"]),
            ])

        t = Table(rows, colWidths=[col_w_name, col_w_narr], repeatRows=1)
        t.setStyle(TableStyle(style_cmds))
        story.append(t)
        story.append(_ai_disclaimer_para())
        story.append(Spacer(1, 0.3 * cm))

    # 3. Key Formula Explanations
    if ai.formula_explanations:
        story.append(Paragraph("Key Formula Explanations", _STYLES["subsection_heading"]))
        story.append(_ai_label_para())

        usable = PAGE_WIDTH - LEFT_MARGIN - RIGHT_MARGIN
        col_widths = [
            usable * 0.15,  # Sheet
            usable * 0.09,  # Cell
            usable * 0.26,  # Formula
            usable * 0.50,  # Explanation
        ]

        header_row = [
            Paragraph("Sheet", _STYLES["table_header"]),
            Paragraph("Cell", _STYLES["table_header"]),
            Paragraph("Formula", _STYLES["table_header"]),
            Paragraph("Explanation", _STYLES["table_header"]),
        ]
        rows = [header_row]
        style_cmds = [
            ("BACKGROUND", (0, 0), (-1, 0), _COLOUR_HEADER_BG),
            ("GRID", (0, 0), (-1, -1), 0.3, colors.HexColor("#BDC3C7")),
            ("VALIGN", (0, 0), (-1, -1), "TOP"),
            ("ROWBACKGROUNDS", (1, 1), (-1, -1), [colors.white, _COLOUR_ROW_ALT]),
        ]

        for fe in ai.formula_explanations:
            formula_display = fe.formula[:60] + "…" if len(fe.formula) > 60 else fe.formula
            rows.append([
                Paragraph(_escape_xml(fe.sheet_name[:25]), _STYLES["cell_small"]),
                Paragraph(_escape_xml(fe.cell_address[:10]), _STYLES["cell_mono"]),
                Paragraph(_escape_xml(formula_display), _STYLES["cell_mono"]),
                Paragraph(_escape_xml(fe.explanation[:600]), _STYLES["cell_small"]),
            ])

        t = Table(rows, colWidths=col_widths, repeatRows=1)
        t.setStyle(TableStyle(style_cmds))
        story.append(t)
        story.append(_ai_disclaimer_para())
        story.append(Spacer(1, 0.3 * cm))

    # 4. Assumption and Input Commentary
    if ai.assumption_commentary:
        story.append(Paragraph("Assumption and Input Commentary", _STYLES["subsection_heading"]))
        story.append(_ai_label_para())
        story.append(_coloured_box(ai.assumption_commentary, _COLOUR_AI_YELLOW))
        story.append(_ai_disclaimer_para())
        story.append(Spacer(1, 0.3 * cm))


# ---------------------------------------------------------------------------
# Cover page builder
# ---------------------------------------------------------------------------

def _build_cover_page(
    result: WorkbookAnalysisResult, story: list
) -> None:
    """
    Append cover page elements to the story list.

    Args:
        result: WorkbookAnalysisResult.
        story:  Mutable list of ReportLab flowables.
    """
    story.append(Spacer(1, 3 * cm))

    # Tool title
    story.append(Paragraph(
        "Excel Workbook Risk Diagnostic Report",
        _STYLES["cover_title"],
    ))
    story.append(HRFlowable(
        width="80%", thickness=2, color=_COLOUR_HEADER_BG, hAlign="CENTER"
    ))
    story.append(Spacer(1, 0.5 * cm))

    # Workbook filename
    story.append(Paragraph(
        f"<b>{_escape_xml(result.filename)}</b>",
        _STYLES["cover_subtitle"],
    ))

    # Timestamp
    ts = result.analysis_timestamp.strftime("%d %B %Y %H:%M UTC")
    story.append(Paragraph(f"Analysed: {ts}", _STYLES["cover_subtitle"]))
    story.append(Paragraph(
        f"File size: {result.file_size_mb:.2f} MB",
        _STYLES["cover_subtitle"],
    ))
    story.append(Spacer(1, 1 * cm))

    # RAG badge — centred table cell with coloured background
    rag_colour = _rag_colour(result.rag_rating)
    rag_table = Table(
        [[Paragraph(result.rag_rating.upper(), _STYLES["cover_rag"])]],
        colWidths=[6 * cm],
        rowHeights=[2 * cm],
    )
    rag_table.setStyle(TableStyle([
        ("BACKGROUND", (0, 0), (0, 0), rag_colour),
        ("ALIGN", (0, 0), (0, 0), "CENTER"),
        ("VALIGN", (0, 0), (0, 0), "MIDDLE"),
        ("ROUNDEDCORNERS", [8]),
        ("BOX", (0, 0), (0, 0), 1, rag_colour),
    ]))

    # Wrap RAG badge in a centred outer table
    outer = Table([[rag_table]], colWidths=[PAGE_WIDTH - LEFT_MARGIN - RIGHT_MARGIN])
    outer.setStyle(TableStyle([("ALIGN", (0, 0), (0, 0), "CENTER")]))
    story.append(outer)

    story.append(Spacer(1, 0.5 * cm))
    story.append(Paragraph(
        f"Overall Risk Score: <b>{result.total_score}</b>",
        _STYLES["cover_subtitle"],
    ))
    story.append(Spacer(1, 0.8 * cm))

    # Summary counts table
    story.append(_make_summary_table(result))
    story.append(PageBreak())


# ---------------------------------------------------------------------------
# Findings-by-category section builder
# ---------------------------------------------------------------------------

_CATEGORY_ORDER = ["Formula", "Error", "Link", "Structure", "VBA", "Actuarial"]

_CATEGORY_DESCRIPTIONS: dict[str, str] = {
    "Formula": (
        "Formula integrity checks examine cell formulas for hardcoded literals, "
        "dangerous lookup modes, volatile functions, inconsistent patterns, and "
        "possible paste-over overrides."
    ),
    "Error": (
        "Cell error checks scan all computed cell values for Excel error codes "
        "such as #REF!, #N/A, and #DIV/0! which indicate broken formula chains."
    ),
    "Link": (
        "Link checks verify external workbook references, named ranges, and "
        "the presence of circular reference indicators in the workbook metadata."
    ),
    "Structure": (
        "Structure checks assess file size, cell population density, hidden "
        "worksheets, and sheet protection settings."
    ),
    "VBA": (
        "VBA macro checks inspect embedded Visual Basic code for hardcoded "
        "parameters, missing error handlers, dangerous commands, and absolute "
        "file path references."
    ),
    "Actuarial": (
        "Actuarial domain checks apply heuristics specific to life insurance "
        "models, flagging high-sensitivity sheets, inconsistent assumptions "
        "across sheets, and isolated hardcoded values."
    ),
}


def _build_findings_by_category(
    result: WorkbookAnalysisResult, story: list
) -> None:
    """
    Append a 'Findings by Category' section to the story.

    Args:
        result: WorkbookAnalysisResult.
        story:  Mutable list of ReportLab flowables.
    """
    story.append(Paragraph("Findings by Category", _STYLES["section_heading"]))
    story.append(HRFlowable(width="100%", thickness=1, color=_COLOUR_LIGHT_GREY))
    story.append(Spacer(1, 0.3 * cm))

    # Build category → findings map
    cat_map: dict[str, list[Finding]] = defaultdict(list)
    for f in result.findings:
        cat_map[f.category].append(f)

    for category in _CATEGORY_ORDER:
        cat_findings = cat_map.get(category, [])
        desc = _CATEGORY_DESCRIPTIONS.get(category, "")
        counts = Counter(f.severity for f in cat_findings)

        story.append(Paragraph(
            f"{category} Checks  "
            f"<font color='#C0392B'>{counts.get('High', 0)}H</font>  "
            f"<font color='#E67E22'>{counts.get('Medium', 0)}M</font>  "
            f"<font color='#27AE60'>{counts.get('Low', 0)}L</font>",
            _STYLES["subsection_heading"],
        ))
        if desc:
            story.append(Paragraph(desc, _STYLES["body_italic"]))

        if not cat_findings:
            story.append(Paragraph(
                "No findings detected in this category.",
                _STYLES["body_italic"],
            ))
        else:
            story.append(_make_findings_table(cat_findings))

        story.append(Spacer(1, 0.4 * cm))


# ---------------------------------------------------------------------------
# High-sensitivity sheets section
# ---------------------------------------------------------------------------

def _build_sensitive_sheets_section(
    result: WorkbookAnalysisResult, story: list
) -> None:
    """
    Append the High-Sensitivity Sheets summary section.

    Args:
        result: WorkbookAnalysisResult.
        story:  Mutable list of ReportLab flowables.
    """
    story.append(PageBreak())
    story.append(Paragraph(
        "High-Sensitivity Actuarial Sheets", _STYLES["section_heading"]
    ))
    story.append(HRFlowable(width="100%", thickness=1, color=_COLOUR_LIGHT_GREY))
    story.append(Spacer(1, 0.3 * cm))

    if not result.high_sensitivity_sheets:
        story.append(Paragraph(
            "No high-sensitivity actuarial sheets were detected in this workbook. "
            "Consider reviewing the actuarial keyword list in settings if this "
            "is unexpected.",
            _STYLES["body"],
        ))
        return

    story.append(Paragraph(
        "The following sheets were identified as actuarially sensitive based on "
        "keyword matching. Findings on these sheets carry elevated significance.",
        _STYLES["body"],
    ))
    story.append(Spacer(1, 0.3 * cm))

    sensitive_set = set(result.high_sensitivity_sheets)

    for sheet_name in result.high_sensitivity_sheets:
        sheet_findings = [
            f for f in result.findings
            if f.sheet_name == sheet_name and f.check_id != 21
        ]
        counts = Counter(f.severity for f in sheet_findings)

        story.append(Paragraph(
            f"Sheet: <b>{_escape_xml(sheet_name)}</b>  —  "
            f"{len(sheet_findings)} finding(s)  "
            f"[{counts.get('High', 0)}H / "
            f"{counts.get('Medium', 0)}M / "
            f"{counts.get('Low', 0)}L]",
            _STYLES["subsection_heading"],
        ))

        if sheet_findings:
            story.append(_make_findings_table(sheet_findings))
        else:
            story.append(Paragraph(
                "No specific findings on this sheet.", _STYLES["body_italic"]
            ))
        story.append(Spacer(1, 0.3 * cm))


# ---------------------------------------------------------------------------
# Appendix builder
# ---------------------------------------------------------------------------

def _build_appendix(result: WorkbookAnalysisResult, story: list) -> None:
    """
    Append the full findings appendix table, sorted by severity descending.

    Args:
        result: WorkbookAnalysisResult.
        story:  Mutable list of ReportLab flowables.
    """
    story.append(PageBreak())
    story.append(Paragraph("Appendix: Full Findings Listing", _STYLES["section_heading"]))
    story.append(HRFlowable(width="100%", thickness=1, color=_COLOUR_LIGHT_GREY))
    story.append(Paragraph(
        f"All {len(result.findings)} finding(s) sorted by severity (High → Medium → Low).",
        _STYLES["body_italic"],
    ))
    story.append(Spacer(1, 0.3 * cm))

    if result.findings:
        story.append(_make_appendix_table(result.findings))
    else:
        story.append(Paragraph("No findings to list.", _STYLES["body_italic"]))


# ---------------------------------------------------------------------------
# Page header/footer callback
# ---------------------------------------------------------------------------

def _add_page_decorations(canvas, doc) -> None:
    """
    Draw running header and footer on every page.
    Called by ReportLab as an onPage callback.

    Args:
        canvas: ReportLab Canvas object.
        doc:    The document being built.
    """
    canvas.saveState()

    # Footer: page number + tool name
    canvas.setFont("Helvetica", 7)
    canvas.setFillColor(colors.HexColor("#95A5A6"))
    page_num_text = f"Page {doc.page}"
    canvas.drawRightString(
        PAGE_WIDTH - RIGHT_MARGIN, 1.2 * cm, page_num_text
    )
    canvas.drawString(
        LEFT_MARGIN, 1.2 * cm, "Excel Workbook Risk Diagnostic Tool"
    )

    # Light top border line
    canvas.setStrokeColor(colors.HexColor("#ECF0F1"))
    canvas.setLineWidth(0.5)
    canvas.line(LEFT_MARGIN, PAGE_HEIGHT - 1.5 * cm,
                PAGE_WIDTH - RIGHT_MARGIN, PAGE_HEIGHT - 1.5 * cm)

    canvas.restoreState()


# ---------------------------------------------------------------------------
# Public entry point
# ---------------------------------------------------------------------------

def generate_pdf_report(
    result: WorkbookAnalysisResult, output_path: Path
) -> None:
    """
    Generate a structured PDF diagnostic report for a single workbook analysis.

    The report contains:
      1. Cover page with RAG badge and finding counts
      2. Executive summary (auto-generated from findings)
      3. Findings by category (Formula, Error, Link, Structure, VBA, Actuarial)
      4. High-sensitivity sheets summary
      5. Appendix: full findings table sorted by severity descending

    Args:
        result:      Completed WorkbookAnalysisResult from the orchestrator.
        output_path: Path where the PDF file will be written.

    Raises:
        IOError: If the output path cannot be written.
    """
    doc = SimpleDocTemplate(
        str(output_path),
        pagesize=A4,
        leftMargin=LEFT_MARGIN,
        rightMargin=RIGHT_MARGIN,
        topMargin=TOP_MARGIN + 0.5 * cm,
        bottomMargin=BOTTOM_MARGIN + 0.5 * cm,
        title=f"Risk Diagnostic — {result.filename}",
        author="Excel Workbook Risk Diagnostic Tool",
    )

    story: list = []

    # 1. Cover page
    _build_cover_page(result, story)

    # 2. Executive summary — use AI findings narrative if available
    story.append(Paragraph("Executive Summary", _STYLES["section_heading"]))
    story.append(HRFlowable(width="100%", thickness=1, color=_COLOUR_LIGHT_GREY))
    story.append(Spacer(1, 0.3 * cm))
    ai_narrative = (
        result.ai_commentary.findings_narrative
        if result.ai_commentary and result.ai_commentary.findings_narrative
        else None
    )
    if ai_narrative:
        story.append(Paragraph(_escape_xml(ai_narrative), _STYLES["body"]))
        story.append(_ai_disclaimer_para())
    else:
        summary_text = _generate_executive_summary(result)
        story.append(Paragraph(_escape_xml(summary_text), _STYLES["body"]))
    story.append(Spacer(1, 0.5 * cm))

    # 2b. AI Workbook Intelligence section (between exec summary and findings)
    _build_ai_section(result, story)

    # 3. Findings by category
    _build_findings_by_category(result, story)

    # 4. High-sensitivity sheets
    _build_sensitive_sheets_section(result, story)

    # 5. Appendix
    _build_appendix(result, story)

    doc.build(
        story,
        onFirstPage=_add_page_decorations,
        onLaterPages=_add_page_decorations,
    )


def generate_pdf_bytes(result: WorkbookAnalysisResult) -> bytes:
    """
    Generate the PDF report and return its content as a bytes object.

    Useful for Streamlit's st.download_button which expects bytes rather
    than a file path.

    Args:
        result: Completed WorkbookAnalysisResult.

    Returns:
        PDF content as bytes.
    """
    buffer = io.BytesIO()

    doc = SimpleDocTemplate(
        buffer,
        pagesize=A4,
        leftMargin=LEFT_MARGIN,
        rightMargin=RIGHT_MARGIN,
        topMargin=TOP_MARGIN + 0.5 * cm,
        bottomMargin=BOTTOM_MARGIN + 0.5 * cm,
        title=f"Risk Diagnostic — {result.filename}",
        author="Excel Workbook Risk Diagnostic Tool",
    )

    story: list = []
    _build_cover_page(result, story)

    story.append(Paragraph("Executive Summary", _STYLES["section_heading"]))
    story.append(HRFlowable(width="100%", thickness=1, color=_COLOUR_LIGHT_GREY))
    story.append(Spacer(1, 0.3 * cm))
    ai_narrative = (
        result.ai_commentary.findings_narrative
        if result.ai_commentary and result.ai_commentary.findings_narrative
        else None
    )
    if ai_narrative:
        story.append(Paragraph(_escape_xml(ai_narrative), _STYLES["body"]))
        story.append(_ai_disclaimer_para())
    else:
        summary_text = _generate_executive_summary(result)
        story.append(Paragraph(_escape_xml(summary_text), _STYLES["body"]))
    story.append(Spacer(1, 0.5 * cm))

    _build_ai_section(result, story)
    _build_findings_by_category(result, story)
    _build_sensitive_sheets_section(result, story)
    _build_appendix(result, story)

    doc.build(
        story,
        onFirstPage=_add_page_decorations,
        onLaterPages=_add_page_decorations,
    )

    buffer.seek(0)
    return buffer.read()