workbook_analyser.py

"""
workbook_analyser.py — Orchestrator for the Excel Workbook Risk Diagnostic Tool.

This module is the single public entry point for analysis. It is intentionally
free of Streamlit imports so it can be called from a CLI, batch processor, or
any other context independently of the UI layer.

Usage:
    from workbook_analyser import analyse_workbook
    result = analyse_workbook(Path("my_model.xlsm"))

The status_callback parameter accepts an optional callable that receives
plain-English progress strings. Streamlit passes a status-update function;
batch callers may pass None or a logging function.
"""

from __future__ import annotations

import zipfile
from datetime import datetime, timezone
from pathlib import Path
from typing import Callable, Optional

import openpyxl

import settings
from models import Finding, WorkbookAnalysisResult
from checkers import (
    formula_checker,
    error_checker,
    link_checker,
    structure_checker,
    vba_checker,
    actuarial_checker,
)

# ---------------------------------------------------------------------------
# Type alias
# ---------------------------------------------------------------------------

StatusCallback = Optional[Callable[[str], None]]


# ---------------------------------------------------------------------------
# Private helpers
# ---------------------------------------------------------------------------

def _notify(callback: StatusCallback, message: str) -> None:
    """
    Send a status update via the callback if one is provided.

    Args:
        callback: Optional callable accepting a string message.
        message:  Plain-English status description.
    """
    if callback is not None:
        try:
            callback(message)
        except Exception:
            pass  # Status updates are best-effort; never fail silently


def _try_load_workbook(
    file_path: Path, data_only: bool
) -> tuple[openpyxl.Workbook | None, str]:
    """
    Attempt to open a workbook with openpyxl, returning (wb, error_message).

    Handles common failure modes gracefully:
      - Password-protected files (zipfile.BadZipFile or encryption markers)
      - Corrupted files
      - Unsupported formats

    Args:
        file_path: Path to the Excel file.
        data_only: If True, read computed cell values; if False, read formulas.

    Returns:
        Tuple of (Workbook or None, error message string).
        Error message is empty string on success.
    """
    try:
        wb = openpyxl.load_workbook(
            str(file_path),
            data_only=data_only,
            keep_vba=False,
            read_only=False,
        )
        return wb, ""
    except zipfile.BadZipFile:
        return None, (
            "File could not be opened — it may be password-protected or "
            "corrupted. Password-protected workbooks cannot be analysed."
        )
    except Exception as exc:
        error_str = str(exc)
        if "encrypted" in error_str.lower() or "password" in error_str.lower():
            return None, (
                "File appears to be password-protected. Remove the password "
                "before uploading for analysis."
            )
        return None, f"File could not be parsed by openpyxl: {error_str}"


def _get_file_size_mb(file_path: Path) -> float:
    """
    Return the file size in megabytes.

    Args:
        file_path: Path to the file.

    Returns:
        Size in MB, or 0.0 on error.
    """
    try:
        return file_path.stat().st_size / (1024 * 1024)
    except OSError:
        return 0.0


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

def analyse_workbook(
    file_path: Path,
    status_callback: StatusCallback = None,
    ai_config: dict | None = None,
) -> WorkbookAnalysisResult:
    """
    Run all risk checks against a single Excel workbook and return a
    structured result object.

    This function is the sole public interface of this module and is designed
    to be called independently of the Streamlit UI, making it suitable for
    batch processing, CLI wrappers, or unit tests.

    The analysis pipeline:
      1.  File size check (no workbook open required)
      2.  Open workbook with data_only=False for formula analysis
      3.  Open workbook with data_only=True for computed-value analysis
      4.  Formula checks (1–6)
      5.  Error checks (7) — uses data_only workbook
      6.  Link checks (8–10)
      7.  Structure checks (11–14)
      8.  VBA checks (15–20) — reads file directly via oletools
      9.  Actuarial checks (21–24) — includes severity escalation
      10. Aggregate, score, and RAG-rate the result
      11. AI commentary (optional) — if ai_config["enabled"] is True

    Args:
        file_path:       Absolute path to the .xlsx or .xlsm file to analyse.
        status_callback: Optional callable receiving plain-English status
                         strings during analysis (for UI progress bars).
        ai_config:       Optional dict controlling AI features. Expected keys:
                         enabled (bool), api_key (str), findings_narrative (bool),
                         purpose_and_sheets (bool), formula_explanations (bool),
                         assumption_commentary (bool).

    Returns:
        WorkbookAnalysisResult with all findings, score, RAG rating, and
        optional ai_commentary. If the file cannot be opened, error_message
        is set and findings are empty.
    """
    _notify(status_callback, f"Starting analysis of '{file_path.name}'...")

    timestamp = datetime.now(tz=timezone.utc)
    file_size_mb = _get_file_size_mb(file_path)
    all_findings: list[Finding] = []

    # ------------------------------------------------------------------
    # Step 1: Attempt to open the workbook (formula mode)
    # ------------------------------------------------------------------
    _notify(status_callback, "Opening workbook...")

    formula_wb, open_error = _try_load_workbook(file_path, data_only=False)
    if formula_wb is None:
        return WorkbookAnalysisResult(
            filename=file_path.name,
            file_path=file_path,
            file_size_mb=file_size_mb,
            analysis_timestamp=timestamp,
            findings=[],
            high_sensitivity_sheets=[],
            total_score=0,
            rag_rating="Red",
            error_message=open_error,
        )

    # ------------------------------------------------------------------
    # Step 2: Open workbook in data-only mode for error detection
    # ------------------------------------------------------------------
    value_wb, _ = _try_load_workbook(file_path, data_only=True)
    # If data_only open fails (rare), fall back to formula_wb — errors
    # may not be detected but we continue with partial analysis.
    if value_wb is None:
        value_wb = formula_wb

    # ------------------------------------------------------------------
    # Step 3: File size check (fast, no workbook needed)
    # ------------------------------------------------------------------
    _notify(status_callback, "Checking file size...")
    all_findings.extend(structure_checker.check_file_size(file_path))

    # ------------------------------------------------------------------
    # Step 4: Formula checks (checks 1–6)
    # ------------------------------------------------------------------
    _notify(status_callback, "Checking formulas for literals, VLOOKUP, and volatiles...")
    try:
        all_findings.extend(formula_checker.run_all(formula_wb))
    except Exception as exc:
        all_findings.append(_internal_error_finding("Formula", exc))

    # ------------------------------------------------------------------
    # Step 5: Error checks (check 7)
    # ------------------------------------------------------------------
    _notify(status_callback, "Scanning for cell errors (#REF!, #N/A, etc.)...")
    try:
        all_findings.extend(error_checker.run_all(value_wb))
    except Exception as exc:
        all_findings.append(_internal_error_finding("Error", exc))

    # ------------------------------------------------------------------
    # Step 6: Link checks (checks 8–10)
    # ------------------------------------------------------------------
    _notify(status_callback, "Checking external links and named ranges...")
    try:
        all_findings.extend(link_checker.run_all(formula_wb, file_path))
    except Exception as exc:
        all_findings.append(_internal_error_finding("Link", exc))

    # ------------------------------------------------------------------
    # Step 7: Structure checks (checks 11–14, excluding file size)
    # ------------------------------------------------------------------
    _notify(status_callback, "Checking workbook structure (hidden sheets, protection)...")
    try:
        struct_findings = structure_checker.run_all(formula_wb, file_path)
        # File size was already checked — skip duplicate (check_id == 11)
        struct_findings = [f for f in struct_findings if f.check_id != 11]
        all_findings.extend(struct_findings)
    except Exception as exc:
        all_findings.append(_internal_error_finding("Structure", exc))

    # ------------------------------------------------------------------
    # Step 8: VBA checks (checks 15–20)
    # ------------------------------------------------------------------
    _notify(status_callback, "Analysing VBA macros...")
    try:
        all_findings.extend(vba_checker.run_all(file_path))
    except Exception as exc:
        all_findings.append(_internal_error_finding("VBA", exc))

    # ------------------------------------------------------------------
    # Step 9: Actuarial checks (checks 21–24)
    # ------------------------------------------------------------------
    _notify(status_callback, "Running actuarial domain checks...")
    try:
        sensitive_sheets, actuarial_findings = actuarial_checker.run_all(
            formula_wb, all_findings
        )
        all_findings.extend(actuarial_findings)
    except Exception as exc:
        sensitive_sheets = []
        all_findings.append(_internal_error_finding("Actuarial", exc))

    # ------------------------------------------------------------------
    # Step 10: Assemble and score the result
    # ------------------------------------------------------------------
    _notify(status_callback, "Computing risk score and RAG rating...")

    result = WorkbookAnalysisResult(
        filename=file_path.name,
        file_path=file_path,
        file_size_mb=file_size_mb,
        analysis_timestamp=timestamp,
        findings=all_findings,
        high_sensitivity_sheets=sensitive_sheets,
        total_score=0,
        rag_rating="Green",
        error_message="",
    )
    result.compute_score()
    result.compute_rag()

    # ------------------------------------------------------------------
    # Step 11: AI Commentary (optional)
    # ------------------------------------------------------------------
    if ai_config is not None and ai_config.get("enabled"):
        try:
            from digest_builder import build_digest
            from ai_analyser import run_ai_analysis

            _notify(status_callback, "Building workbook digest for AI analysis...")
            digest = build_digest(file_path, result)

            _notify(status_callback, "Running AI commentary (this may take 30–60 seconds)...")
            result.ai_commentary = run_ai_analysis(
                digest=digest,
                findings_result=result,
                api_key=ai_config.get("api_key", ""),
                run_findings_narrative=ai_config.get("findings_narrative", True),
                run_purpose_and_sheets=ai_config.get("purpose_and_sheets", True),
                run_formula_explanations=ai_config.get("formula_explanations", True),
                run_assumption_commentary=ai_config.get("assumption_commentary", True),
            )
        except Exception as exc:
            from models import AICommentary
            result.ai_commentary = AICommentary(
                api_error=f"AI commentary failed: {type(exc).__name__}: {exc}"
            )

    _notify(
        status_callback,
        f"Analysis complete. {len(all_findings)} finding(s). "
        f"RAG: {result.rag_rating}. Score: {result.total_score}.",
    )

    return result


# ---------------------------------------------------------------------------
# Internal error finding helper
# ---------------------------------------------------------------------------

def _internal_error_finding(category: str, exc: Exception) -> Finding:
    """
    Create a Low-severity informational finding when a checker raises an
    unexpected exception. Ensures one checker failure does not suppress all
    other findings.

    Args:
        category: The checker category that failed.
        exc:      The exception that was raised.

    Returns:
        A Finding recording the internal error.
    """
    return Finding(
        check_id=0,
        name=f"Checker Error ({category})",
        severity="Low",
        category=category,
        description=(
            f"The {category} checker encountered an unexpected error: "
            f"{type(exc).__name__}: {exc}. Other checks were not affected."
        ),
        sheet_name="Workbook",
        cell_ref="",
        explanation=(
            "This is an internal diagnostic message. The check could not "
            "complete, which may mean some risks in this category went "
            "undetected. Review this category manually."
        ),
    )