// Hook: AfterCalculation
// Apply a currency number-format to columns whose header mentions Amount,
// Total, or Revenue.
//
// The currency symbol is chosen by the "Currency" job parameter
// (USD / EUR / GBP). Formatting at runtime keeps templates clean and lets
// the same workbook serve multiple regions.

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

if (!Parameters.TryGetValue("Currency", out var currency) || string.IsNullOrWhiteSpace(currency))
{
    AddWarning("Parameter 'Currency' not provided; skipping currency formatting.");
    return;
}

string format = currency.Trim().ToUpperInvariant() switch
{
    "USD" => "[$$-409]#,##0.00;[Red]([$$-409]#,##0.00)",
    "EUR" => "[$€-2] #,##0.00;[Red]([$€-2] #,##0.00)",
    "GBP" => "[$£-809]#,##0.00;[Red]([$£-809]#,##0.00)",
    _ => null,
};

if (format == null)
{
    AddWarning($"Unsupported Currency '{currency}'; expected USD, EUR, or GBP.");
    return;
}

var headerMarkers = new[] { "Amount", "Total", "Revenue" };
int formattedColumns = 0;

foreach (Worksheet sheet in Workbook.Worksheets)
{
    int lastRow = sheet.Cells.MaxDataRow;
    int lastCol = sheet.Cells.MaxDataColumn;
    if (lastRow < 1 || lastCol < 0) continue;

    for (int col = 0; col <= lastCol; col++)
    {
        var header = sheet.Cells[0, col].StringValue ?? string.Empty;
        if (!headerMarkers.Any(m => header.IndexOf(m, StringComparison.OrdinalIgnoreCase) >= 0))
            continue;

        var style = sheet.Cells[1, col].GetStyle();
        style.Custom = format;

        var flag = new StyleFlag { NumberFormat = true };
        var range = sheet.Cells.CreateRange(1, col, lastRow, 1);
        range.ApplyStyle(style, flag);

        formattedColumns++;
    }
}

Logger.LogInformation($"Applied {currency} format to {formattedColumns} column(s) in '{OutputName}'.");