// Hook: AfterCalculation
// Mask values in any column whose header looks like personally identifiable
// information (PII).
//
// Useful when the same template is shared between internal and external
// distribution lists - external recipients receive masked SSN, Email, Phone,
// DOB, and Birthdate columns. Adjust 'piiMarkers' to match your data.

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

var piiMarkers = new[] { "SSN", "Email", "Phone", "DOB", "Birthdate" };
int redactedColumns = 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 (string.IsNullOrWhiteSpace(header)) continue;

        bool isPii = piiMarkers.Any(m => header.IndexOf(m, StringComparison.OrdinalIgnoreCase) >= 0);
        if (!isPii) continue;

        for (int row = 1; row <= lastRow; row++)
        {
            var cell = sheet.Cells[row, col];
            if (!string.IsNullOrEmpty(cell.StringValue))
                cell.PutValue("***");
        }

        redactedColumns++;
        Logger.LogInformation($"Redacted PII column '{header}' on sheet '{sheet.Name}'.");
    }
}

if (redactedColumns == 0)
{
    Logger.LogInformation($"No PII columns matched in '{OutputName}'.");
}
else
{
    AddWarning($"Redacted {redactedColumns} PII column(s) in '{OutputName}'.");
}