/* global XLSX */
/* ============================================================
   NBE credit-risk workbook ingestion (browser port)
   Canonical implementation + tests: src/lib/ingest/creditRisk.ts
   Keep the two in sync — same row model, same validation rules.
   Parses the bank's actual NBE submission (SBB/95/2025 Annex 2
   layout), recomputes every derived cell, and reports mismatches
   as validation issues instead of silently trusting the file.
   ============================================================ */
(function (global) {
  var OFF_BALANCE_CCF = { "1": 10, "2": 20, "3": 40, "4": 50, "5": 50, "6": 100, "7": 100, "8": 100, "9": 100, "10": 100 };
  var RWA_TOLERANCE = 0.01;
  var ILM_TOLERANCE = 0.001;
  var OPERATIONAL_RWA_MULTIPLIER = 9.1;
  var MARKET_RWA_MULTIPLIER = 9.1;
  var FX_CAPITAL_SCALAR = 1.2;
  var CAPITAL_REQUIREMENT_RATE = 0.11;

  function cellAt(sheet, col, row) { return sheet[col + row]; }
  function num(sheet, col, row) {
    var c = cellAt(sheet, col, row);
    if (!c || c.v === undefined || c.v === null || c.v === "") return null;
    var v = typeof c.v === "number" ? c.v : Number(c.v);
    return isFinite(v) ? v : null;
  }
  function text(sheet, col, row) {
    var c = cellAt(sheet, col, row);
    if (!c || c.v === undefined || c.v === null) return "";
    return String(c.v).trim();
  }
  function isSumFormula(sheet, col, row) {
    var c = cellAt(sheet, col, row);
    if (!c || typeof c.f !== "string") return false;
    var f = c.f.trim();
    return /SUM\(/i.test(f) || /^[A-Z]+\d+(\s*\+\s*[A-Z]+\d+)+$/.test(f);
  }
  function normalizeItemNo(raw) {
    var trimmed = raw.replace(/^\s+|\s+$/g, "");
    var asNumber = Number(trimmed);
    if (!isFinite(asNumber) || /[^0-9.]/.test(trimmed)) return trimmed;
    if (/\d\.\d{10,}/.test(trimmed)) return String(Math.round(asNumber * 100) / 100);
    return trimmed;
  }
  function topLevelBlock(itemNo) { return itemNo.split(".")[0].replace(/^\s+|\s+$/g, ""); }
  function maxSheetRow(sheet) { return Math.min(XLSX.utils.decode_range(sheet["!ref"] || "A1:A1").e.r + 1, 5000); }
  function findFirstSheetName(wb, pattern) {
    for (var i = 0; i < wb.SheetNames.length; i++) if (pattern.test(wb.SheetNames[i])) return wb.SheetNames[i];
    return null;
  }
  function yearTriple(sheet, row, cols) { return [num(sheet, cols[0], row), num(sheet, cols[1], row), num(sheet, cols[2], row)]; }
  function avg(values) {
    var sum = 0, count = 0;
    for (var i = 0; i < values.length; i++) if (values[i] !== null && isFinite(values[i])) { sum += values[i]; count++; }
    return count ? sum / count : 0;
  }
  function avgAbs(values) {
    var sum = 0, count = 0;
    for (var i = 0; i < values.length; i++) if (values[i] !== null && isFinite(values[i])) { sum += Math.abs(values[i]); count++; }
    return count ? sum / count : 0;
  }
  function avgAbsDiff(a, b) {
    var sum = 0, count = 0;
    for (var i = 0; i < 3; i++) if (a[i] !== null && b[i] !== null) { sum += Math.abs(a[i] - b[i]); count++; }
    return count ? sum / count : 0;
  }
  function findRowByLabel(sheet, col, pattern) {
    var maxRow = maxSheetRow(sheet);
    for (var r = 1; r <= maxRow; r++) if (pattern.test(text(sheet, col, r))) return r;
    return null;
  }
  function sheetContains(sheet, pattern) {
    var range = XLSX.utils.decode_range(sheet["!ref"] || "A1:A1");
    var maxRow = Math.min(range.e.r + 1, 5000);
    var maxCol = Math.min(range.e.c, 50);
    for (var r = 1; r <= maxRow; r++) {
      for (var c = 0; c <= maxCol; c++) {
        var value = text(sheet, XLSX.utils.encode_col(c), r);
        if (value && pattern.test(value)) return true;
      }
    }
    return false;
  }
  function bicOf(bi) {
    return Math.min(bi, 3500) * 0.12 + Math.max(0, Math.min(bi, 106000) - 3500) * 0.15 + Math.max(0, bi - 106000) * 0.18;
  }
  function ilmOf(lossComponent, bic) {
    if (bic <= 0 || lossComponent <= 0) return 1;
    return Math.log(Math.E - 1 + Math.pow(lossComponent / bic, 0.8));
  }
  function parseMarketRiskSheet(wb, issues) {
    var sheetName = findFirstSheetName(wb, /^mkt$/i) || findFirstSheetName(wb, /market/i);
    if (!sheetName) return null;
    var sheet = wb.Sheets[sheetName];
    var cols = ["C", "D", "E", "F", "G", "H"];
    var currencies = [];
    for (var i = 0; i < cols.length; i++) {
      var col = cols[i], currency = text(sheet, col, 5);
      if (!currency) continue;
      currencies.push({
        currency: currency,
        totalAssets: num(sheet, col, 19),
        totalLiabilities: num(sheet, col, 34),
        netLong: num(sheet, col, 36),
        netShort: num(sheet, col, 37),
        exchangeRate: num(sheet, col, 38),
        netLongBirr: num(sheet, col, 39),
        netShortBirr: num(sheet, col, 40)
      });
    }
    var fxCurrencies = [], goldCurrency = null;
    for (var cidx = 0; cidx < currencies.length; cidx++) {
      if (/gold/i.test(currencies[cidx].currency)) goldCurrency = currencies[cidx]; else fxCurrencies.push(currencies[cidx]);
    }
    var computedTotalLong = 0;
    for (var a = 0; a < fxCurrencies.length; a++) computedTotalLong += fxCurrencies[a].netLongBirr || 0;
    var computedTotalShort = 0;
    for (var b = 0; b < fxCurrencies.length; b++) computedTotalShort += Math.abs(fxCurrencies[b].netShortBirr || 0);
    var computedGold = goldCurrency ? Math.max(Math.abs(goldCurrency.netLongBirr || 0), Math.abs(goldCurrency.netShortBirr || 0)) : 0;
    var submittedTotalLong = num(sheet, "C", 43);
    var submittedTotalShort = num(sheet, "C", 44);
    var submittedGold = num(sheet, "C", 46);
    var submittedOverall = num(sheet, "C", 47);
    var submittedCharge = num(sheet, "C", 48);
    var submittedRwa = num(sheet, "C", 49);
    var totalLong = fxCurrencies.length ? computedTotalLong : (submittedTotalLong === null ? 0 : submittedTotalLong);
    var totalShort = fxCurrencies.length ? computedTotalShort : (submittedTotalShort === null ? 0 : submittedTotalShort);
    var gold = computedGold !== 0 ? computedGold : (submittedGold === null ? 0 : submittedGold);
    var overall = Math.max(Math.abs(totalLong), Math.abs(totalShort)) + Math.abs(gold);
    var charge = overall * CAPITAL_REQUIREMENT_RATE * FX_CAPITAL_SCALAR;
    var rwa = charge * MARKET_RWA_MULTIPLIER;

    if (submittedTotalLong !== null && Math.abs(submittedTotalLong - totalLong) > RWA_TOLERANCE) {
      issues.push({ sheet: "market", itemNo: "8.1", severity: "error", message: "Total long FX position does not equal the sum of currency net-long Birr positions", expected: totalLong, actual: submittedTotalLong });
    }
    if (submittedTotalShort !== null && Math.abs(submittedTotalShort - totalShort) > RWA_TOLERANCE) {
      issues.push({ sheet: "market", itemNo: "8.2", severity: "error", message: "Total short FX position does not equal the sum of currency net-short Birr positions", expected: totalShort, actual: submittedTotalShort });
    }
    if (submittedGold !== null && computedGold !== 0 && Math.abs(submittedGold - computedGold) > RWA_TOLERANCE) {
      issues.push({ sheet: "market", itemNo: "10", severity: "error", message: "Gold open position does not equal the absolute Gold currency position", expected: computedGold, actual: submittedGold });
    }
    if (submittedOverall !== null && Math.abs(submittedOverall - overall) > RWA_TOLERANCE) {
      issues.push({ sheet: "market", itemNo: "11", severity: "error", message: "Net global open position does not equal max(total long, total short) plus absolute gold position", expected: overall, actual: submittedOverall });
    }
    if (submittedCharge !== null && Math.abs(submittedCharge - charge) > RWA_TOLERANCE) {
      issues.push({ sheet: "market", itemNo: "12", severity: "error", message: "FX capital charge does not equal net global open position x 11% x 1.2", expected: charge, actual: submittedCharge });
    }
    if (submittedRwa !== null && Math.abs(submittedRwa - rwa) > RWA_TOLERANCE) {
      issues.push({ sheet: "market", itemNo: "13", severity: "error", message: "FX RWA does not equal FX capital charge x 9.1", expected: rwa, actual: submittedRwa });
    }
    return {
      sheetName: sheetName,
      unit: "ETB millions",
      currencies: currencies,
      totalLongPosition: totalLong,
      totalShortPosition: totalShort,
      goldOpenPosition: gold,
      overallNop: overall,
      fxCapitalCharge: charge,
      rwaEquivalent: rwa
    };
  }
  function parseOperationalRiskSheet(wb, issues) {
    var sheetName = findFirstSheetName(wb, /operational/i);
    if (!sheetName) return null;
    var sheet = wb.Sheets[sheetName];
    var maxRow = maxSheetRow(sheet);
    var interestEarningAssets = [];
    for (var r = 1; r <= maxRow; r++) {
      var raw = text(sheet, "A", r), itemNo = normalizeItemNo(raw), itemAsNumber = Number(itemNo), label = text(sheet, "B", r);
      if (!label || !isFinite(itemAsNumber) || itemAsNumber < 1 || itemAsNumber > 11) continue;
      interestEarningAssets.push({ itemNo: itemNo, label: label, values: yearTriple(sheet, r, ["C", "D", "E"]), sheetRow: r, isTotal: itemNo === "11" || /Interest earning assets/i.test(label) });
    }
    var profitLossRows = [];
    var totalLabels = /^(Total|Interest earning assets|Dividend income|Net profit \(loss\))/i;
    for (var r2 = 1; r2 <= maxRow; r2++) {
      var rawItem = text(sheet, "G", r2), plItemNo = normalizeItemNo(rawItem), plLabel = text(sheet, "H", r2);
      if (!plLabel || !/^\d+(\.\d+)?$/.test(plItemNo)) continue;
      profitLossRows.push({ itemNo: plItemNo, label: plLabel, values: yearTriple(sheet, r2, ["I", "J", "K"]), sheetRow: r2, isTotal: totalLabels.test(plLabel) });
    }
    function requireValues(pattern, label) {
      var row = findRowByLabel(sheet, "H", pattern);
      if (row === null) {
        issues.push({ sheet: "operational", itemNo: label, severity: "error", message: "Operational risk sheet is missing " + label });
        return [0, 0, 0];
      }
      return yearTriple(sheet, row, ["I", "J", "K"]);
    }
    var interestIncome = requireValues(/^Total Interest income/i, "total interest income");
    var interestExpense = requireValues(/^Total Interest expense/i, "total interest expense");
    var opInterestEarningAssets = requireValues(/^Interest earning assets/i, "interest earning assets");
    var dividendIncome = requireValues(/^Dividend income/i, "dividend income");
    var feeIncome = requireValues(/^Total fee and commission income/i, "fee and commission income");
    var feeExpense = requireValues(/^Total fee and commission expense/i, "fee and commission expense");
    var otherOperatingIncome = requireValues(/^Total other operating income/i, "other operating income");
    var otherOperatingExpense = requireValues(/^Total other operating expense/i, "other operating expense");
    var tradingBook = requireValues(/^Net profit \(loss\) on the trading book/i, "trading book result");
    var bankingBook = requireValues(/^Net profit \(loss\) on the banking book/i, "banking book result");
    var ildcTerm = avgAbsDiff(interestIncome, interestExpense);
    var ildcCap = avg(opInterestEarningAssets) * 0.0225;
    var divAvg = avg(dividendIncome);
    var ildc = Math.min(ildcTerm, ildcCap) + divAvg;
    var feeIAvg = avg(feeIncome), feeEAvg = avg(feeExpense), ooiAvg = avg(otherOperatingIncome), ooeAvg = avg(otherOperatingExpense);
    var sc = Math.max(feeIAvg, feeEAvg) + Math.max(ooiAvg, ooeAvg);
    var tbAvg = avgAbs(tradingBook), bbAvg = avgAbs(bankingBook);
    var fc = tbAvg + bbAvg;
    var bi = ildc + sc + fc;
    var bic = bicOf(bi);
    var submittedBic = num(sheet, "Q", 43);
    if (submittedBic === null) submittedBic = num(sheet, "P", 34);
    var submittedLc = num(sheet, "Q", 42);
    var submittedIlm = num(sheet, "Q", 46);
    var submittedCharge = num(sheet, "Q", 48);
    var submittedRwa = num(sheet, "Q", 50);
    var fallback = sheetContains(sheet, /ILM\s+of\s+1/i) || submittedIlm === null || Math.abs(submittedIlm - 1) < 0.001;
    var lossComponent = submittedLc === null ? bic : submittedLc;
    var ilm = fallback ? 1 : ilmOf(lossComponent, bic);
    var capitalCharge = bic * ilm;
    var rwaEquivalent = capitalCharge * OPERATIONAL_RWA_MULTIPLIER;
    if (submittedBic !== null && Math.abs(submittedBic - bic) > RWA_TOLERANCE) {
      issues.push({ sheet: "operational", itemNo: "BIC", severity: "error", message: "Business Indicator Component does not match the recomputed SMA bucket charge", expected: bic, actual: submittedBic });
    }
    if (submittedIlm !== null && Math.abs(submittedIlm - ilm) > ILM_TOLERANCE) {
      issues.push({ sheet: "operational", itemNo: "ILM", severity: "error", message: "Internal Loss Multiplier does not match the official SMA ILM/fallback", expected: ilm, actual: submittedIlm });
    }
    if (submittedCharge !== null) {
      if (Math.abs(submittedCharge - capitalCharge) > RWA_TOLERANCE) {
        issues.push({ sheet: "operational", itemNo: "Step 2", severity: "error", message: "Operational capital charge does not equal BIC x ILM", expected: capitalCharge, actual: submittedCharge });
      }
    }
    if (submittedRwa !== null) {
      if (Math.abs(submittedRwa - rwaEquivalent) > RWA_TOLERANCE) {
        issues.push({ sheet: "operational", itemNo: "Step 3", severity: "error", message: "Operational RWA does not equal operational capital charge x 9.1", expected: rwaEquivalent, actual: submittedRwa });
      }
    }
    return {
      sheetName: sheetName,
      unit: "ETB millions",
      interestEarningAssets: interestEarningAssets,
      profitLossRows: profitLossRows,
      inputs: {
        interestIncome: interestIncome, interestExpense: interestExpense, interestEarningAssets: opInterestEarningAssets,
        dividendIncome: dividendIncome, feeIncome: feeIncome, feeExpense: feeExpense,
        otherOperatingIncome: otherOperatingIncome, otherOperatingExpense: otherOperatingExpense,
        tradingBook: tradingBook, bankingBook: bankingBook
      },
      metrics: {
        ildcInterestIncomeExpenseAvg: ildcTerm, ildcInterestEarningAssetCap: ildcCap, dividendIncomeAverage: divAvg, ildc: ildc,
        feeIncomeAverage: feeIAvg, feeExpenseAverage: feeEAvg, otherOperatingIncomeAverage: ooiAvg, otherOperatingExpenseAverage: ooeAvg,
        serviceComponent: sc, tradingBookAverageAbs: tbAvg, bankingBookAverageAbs: bbAvg, financialComponent: fc,
        businessIndicator: bi, businessIndicatorComponent: bic, lossComponent: lossComponent, internalLossMultiplier: ilm,
        capitalCharge: capitalCharge, rwaEquivalent: rwaEquivalent, officialIlmFallback: fallback,
        submittedBusinessIndicatorComponent: submittedBic, submittedInternalLossMultiplier: submittedIlm,
        submittedCapitalCharge: submittedCharge, submittedRwaEquivalent: submittedRwa
      }
    };
  }

  function parseCreditRiskWorkbook(data) {
    var wb = XLSX.read(data, { type: "array", cellFormula: true });
    var onName = null, offName = null;
    for (var i = 0; i < wb.SheetNames.length; i++) {
      if (!onName && /on balance/i.test(wb.SheetNames[i])) onName = wb.SheetNames[i];
      if (!offName && /off balance/i.test(wb.SheetNames[i])) offName = wb.SheetNames[i];
    }
    if (!onName || !offName) throw new Error("Workbook is missing the expected NBE credit-risk sheets (found: " + wb.SheetNames.join(", ") + ")");
    var on = wb.Sheets[onName], off = wb.Sheets[offName];
    var issues = [];
    var bankName = text(on, "A", 1);
    var asAt = text(on, "A", 2);

    // ---- On-balance sheet ----
    var onRows = [];
    var onRange = XLSX.utils.decode_range(on["!ref"] || "A1:H1");
    var onMaxRow = Math.min(onRange.e.r + 1, 5000);
    var onTotalRwa = null, onCapital = null;
    var seen = {};
    for (var r = 7; r <= onMaxRow; r++) {
      var rawItem = text(on, "A", r);
      if (!rawItem) continue;
      if (/^TOTAL ON BALANCE SHEET CREDIT RISK RWA/i.test(rawItem)) { onTotalRwa = num(on, "H", r); continue; }
      if (/^TOTAL ON BALANCE SHEET CREDIT RISK CAPITAL/i.test(rawItem)) { onCapital = num(on, "H", r); continue; }
      var itemNo = normalizeItemNo(rawItem);
      var row = {
        itemNo: itemNo,
        label: text(on, "B", r),
        ecr: text(on, "D", r) || undefined,
        totalExposure: num(on, "C", r),
        netExposure: num(on, "E", r),
        crmExposure: num(on, "F", r),
        riskWeightPct: num(on, "G", r),
        rwa: num(on, "H", r),
        isAggregate: isSumFormula(on, "C", r) || isSumFormula(on, "H", r),
        sheetRow: r
      };
      onRows.push(row);
      if (itemNo !== rawItem.replace(/^\s+|\s+$/g, "")) {
        issues.push({ sheet: "on-balance", itemNo: itemNo, severity: "warning", message: 'Item number stored as Excel float "' + rawItem + '" - normalized to "' + itemNo + '"' });
      }
      if (seen[itemNo] !== undefined) {
        issues.push({ sheet: "on-balance", itemNo: itemNo, severity: "error", message: "Duplicate item number (rows " + seen[itemNo] + " and " + r + ") - template numbering defect" });
      } else { seen[itemNo] = r; }
      if (!row.isAggregate && row.riskWeightPct !== null && row.rwa !== null) {
        var base = (row.netExposure || 0) + (row.crmExposure || 0);
        var expected = base * row.riskWeightPct / 100;
        if (Math.abs(expected - row.rwa) > RWA_TOLERANCE) {
          issues.push({ sheet: "on-balance", itemNo: itemNo, severity: "error", message: "Cached RWA does not equal (net exposure + CRM exposure) x RW", expected: expected, actual: row.rwa });
        }
      }
    }
    var onLeafTotal = 0;
    for (var j = 0; j < onRows.length; j++) if (!onRows[j].isAggregate && onRows[j].rwa !== null) onLeafTotal += onRows[j].rwa;
    if (onTotalRwa === null) {
      issues.push({ sheet: "on-balance", itemNo: "TOTAL", severity: "error", message: "Total on-balance RWA row not found" });
      onTotalRwa = onLeafTotal;
    } else if (Math.abs(onLeafTotal - onTotalRwa) > RWA_TOLERANCE) {
      issues.push({ sheet: "on-balance", itemNo: "TOTAL", severity: "error", message: "Sum of leaf-row RWA does not tie to the template's total RWA", expected: onLeafTotal, actual: onTotalRwa });
    }
    if (onCapital !== null && Math.abs(onCapital - onTotalRwa * 0.11) > RWA_TOLERANCE) {
      issues.push({ sheet: "on-balance", itemNo: "TOTAL", severity: "error", message: "Capital row does not equal 11% of total RWA", expected: onTotalRwa * 0.11, actual: onCapital });
    }
    var crmSameWeight = false;
    for (var k = 0; k < onRows.length; k++) if (!onRows[k].isAggregate && (onRows[k].crmExposure || 0) > 0 && (onRows[k].rwa || 0) > 0) { crmSameWeight = true; break; }
    if (crmSameWeight) {
      issues.push({ sheet: "on-balance", itemNo: "CRM", severity: "info", message: "CRM-covered exposures are risk-weighted at the counterparty RW (no substitution) - the template provides no capital relief for CRM" });
    }

    // ---- Off-balance sheet ----
    var offRows = [];
    var offRange = XLSX.utils.decode_range(off["!ref"] || "A1:I1");
    var offMaxRow = Math.min(offRange.e.r + 1, 5000);
    var offTotalRwa = null, offCapital = null;
    var currentBlock = "";
    for (var r2 = 7; r2 <= offMaxRow; r2++) {
      var totalLabel = text(off, "B", r2);
      if (/^TOTAL OFF-BALANCE SHEET/i.test(totalLabel)) {
        var v2 = num(off, "I", r2);
        if (offTotalRwa === null) offTotalRwa = v2; else offCapital = v2;
        continue;
      }
      var rawItem2 = text(off, "A", r2);
      if (!rawItem2) continue;
      var itemNo2 = normalizeItemNo(rawItem2);
      var block = topLevelBlock(itemNo2);
      if (itemNo2.indexOf(".") === -1) { currentBlock = block; continue; }
      var ccfPct = OFF_BALANCE_CCF[currentBlock || block];
      if (ccfPct === undefined) ccfPct = null;
      var rawRw = num(off, "D", r2);
      var cpRw = rawRw;
      if (rawRw !== null && rawRw < 10) {
        cpRw = rawRw * 100;
        if (rawRw !== 0 && (num(off, "I", r2) || 0) !== 0) {
          issues.push({ sheet: "off-balance", itemNo: itemNo2, severity: "warning", message: "Counterparty RW entered as fraction " + rawRw + " - normalized to " + cpRw + "%; template formulas mix /100 and x1 conventions on this column" });
        }
      }
      var orow = {
        itemNo: itemNo2,
        label: text(off, "B", r2),
        faceValue: num(off, "C", r2),
        counterpartyRwPct: cpRw,
        exposureBeforeCcf: num(off, "E", r2),
        creditEquivalent: num(off, "F", r2),
        crmCoverage: num(off, "G", r2),
        creditEquivalentWithCrm: num(off, "H", r2),
        rwa: num(off, "I", r2),
        ccfPct: ccfPct,
        sheetRow: r2
      };
      offRows.push(orow);
      if (ccfPct !== null && orow.exposureBeforeCcf !== null && orow.creditEquivalent !== null) {
        var expCe = orow.exposureBeforeCcf * ccfPct / 100;
        if (Math.abs(expCe - orow.creditEquivalent) > RWA_TOLERANCE) {
          issues.push({ sheet: "off-balance", itemNo: itemNo2, severity: "error", message: "Credit equivalent does not equal exposure x " + ccfPct + "% CCF", expected: expCe, actual: orow.creditEquivalent });
        }
      }
      if (orow.rwa !== null && orow.counterpartyRwPct !== null && (orow.creditEquivalent !== null || orow.creditEquivalentWithCrm !== null)) {
        var expRwa = ((orow.creditEquivalent || 0) + (orow.creditEquivalentWithCrm || 0)) * orow.counterpartyRwPct / 100;
        if (Math.abs(expRwa - orow.rwa) > RWA_TOLERANCE) {
          issues.push({ sheet: "off-balance", itemNo: itemNo2, severity: "error", message: "Cached RWA does not equal (credit equivalent + CRM credit equivalent) x counterparty RW", expected: expRwa, actual: orow.rwa });
        }
      }
    }
    var offLeafTotal = 0;
    for (var m = 0; m < offRows.length; m++) offLeafTotal += offRows[m].rwa || 0;
    if (offTotalRwa === null) {
      issues.push({ sheet: "off-balance", itemNo: "TOTAL", severity: "error", message: "Total off-balance RWA row not found" });
      offTotalRwa = offLeafTotal;
    } else if (Math.abs(offLeafTotal - offTotalRwa) > RWA_TOLERANCE) {
      issues.push({ sheet: "off-balance", itemNo: "TOTAL", severity: "error", message: "Sum of row RWA does not tie to the template's total RWA", expected: offLeafTotal, actual: offTotalRwa });
    }
    if (offCapital !== null && Math.abs(offCapital - offTotalRwa * 0.11) > RWA_TOLERANCE) {
      issues.push({ sheet: "off-balance", itemNo: "TOTAL", severity: "error", message: "Capital row does not equal 11% of total RWA", expected: offTotalRwa * 0.11, actual: offCapital });
    }

    var marketRisk = parseMarketRiskSheet(wb, issues);
    var operationalRisk = parseOperationalRiskSheet(wb, issues);
    var result = {
      bankName: bankName,
      asAt: asAt,
      onBalance: { rows: onRows, totalRwa: onTotalRwa, leafTotalRwa: onLeafTotal, capitalAt11Pct: onCapital !== null ? onCapital : onTotalRwa * 0.11 },
      offBalance: { rows: offRows, totalRwa: offTotalRwa, leafTotalRwa: offLeafTotal, capitalAt11Pct: offCapital !== null ? offCapital : offTotalRwa * 0.11 },
      issues: issues
    };
    if (marketRisk) result.marketRisk = marketRisk;
    if (operationalRisk) result.operationalRisk = operationalRisk;
    return result;
  }

  global.parseCreditRiskWorkbook = parseCreditRiskWorkbook;
})(window);