"""Formula utilities — Excel-compatible formula evaluation using the formulas library."""

from __future__ import annotations

from typing import Any, Optional

import openpyxl


def evaluate_formula(wb: openpyxl.Workbook, sheet_name: str, cell_ref: str) -> Optional[Any]:
    """Evaluate an Excel formula in-memory using the formulas library.
    
    Falls back to openpyxl data_only reload if formulas library fails.
    Returns the computed value or None on failure.
    """
    try:
        import formulas
    except ImportError:
        return _fallback_evaluate(wb, sheet_name, cell_ref)

    try:
        xl_model = formulas.ExcelModel().loads(wb.path).finish()
        solution = xl_model.calculate()
        key = f"'{sheet_name}'!{cell_ref.upper()}"
        return solution.get(key)
    except Exception:
        return _fallback_evaluate(wb, sheet_name, cell_ref)


def _fallback_evaluate(wb: openpyxl.Workbook, sheet_name: str, cell_ref: str) -> Optional[Any]:
    """Fallback: reload workbook with data_only=True to get cached values."""
    if not wb.path:
        return None
    try:
        wb_data = openpyxl.load_workbook(wb.path, data_only=True)
        ws = wb_data[sheet_name]
        return ws[cell_ref].value
    except Exception:
        return None