// Hook: AfterCalculation
// Halt the job if a KPI value is missing, negative, or absurdly large.
//
// Reads a named cell "KPI_Total" on the active sheet. Catches data quality
// regressions (a wrong join, a unit conversion bug) before a bad number
// reaches an executive inbox.

if (Workbook == null)
{
    AddWarning($"No workbook available for '{OutputName}'; skipping KPI validation.");
    return;
}

var sheet = Workbook.Worksheets[Workbook.Worksheets.ActiveSheetIndex];
var range = sheet.Workbook.Worksheets.GetRangeByName("KPI_Total");

if (range == null)
{
    AddWarning($"Named range 'KPI_Total' not found on '{sheet.Name}'; skipping KPI validation.");
    return;
}

var raw = range.GetCellOrNull(0, 0)?.Value;
double ceiling = 1_000_000_000d;

if (raw is double d)
{
    if (double.IsNaN(d) || double.IsInfinity(d))
        AddError($"KPI_Total on '{sheet.Name}' is not a finite number ({d}).");
    if (d < 0)
        AddError($"KPI_Total on '{sheet.Name}' is negative ({d}); this should never happen.");
    if (d > ceiling)
        AddError($"KPI_Total on '{sheet.Name}' is {d}, above sanity ceiling of {ceiling}.");

    Logger.LogInformation($"KPI_Total = {d} on '{sheet.Name}' is within bounds.");
}
else
{
    AddError($"KPI_Total on '{sheet.Name}' is not numeric (got '{raw}').");
}