/* global XLSX */
/* ============================================================
   NBE open-position workbook ingestion (browser port)
   Report Template is the executable matrix; Description and Stress Scenario
   are supporting evidence/context sheets rather than row-wise calculation tabs.
   Canonical implementation + tests: src/lib/ingest/openPosition.ts
   Keep the two in sync — same anchors, same validation rules.
   ============================================================ */
(function (global) {
  var TOLERANCE = 0.05;

  function num(sheet, col, row) {
    var c = 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 = sheet[col + row];
    if (!c || c.v === undefined || c.v === null) return "";
    return String(c.v).trim();
  }

  function parseOpenPositionWorkbook(data) {
    var wb = XLSX.read(data, { type: "array" });
    var sheetName = wb.SheetNames[0];
    for (var i = 0; i < wb.SheetNames.length; i++) if (/report template/i.test(wb.SheetNames[i])) { sheetName = wb.SheetNames[i]; break; }
    var sheet = wb.Sheets[sheetName];
    var issues = [];
    var range = XLSX.utils.decode_range(sheet["!ref"] || "A1:X60");
    var maxRow = Math.min(range.e.r + 1, 5000);

    var rowOf = {};
    var assetsTotalRow = 0, liabTotalRow = 0;
    for (var r = 1; r <= maxRow; r++) {
      var rawNo = text(sheet, "B", r);
      if (rawNo) {
        var n = Number(rawNo);
        var key = isFinite(n) && /\d\.\d{10,}/.test(rawNo) ? String(Math.round(n * 100) / 100) : rawNo;
        if (!rowOf[key]) rowOf[key] = r;
      }
      var label = text(sheet, "C", r);
      if (/^Total Foreign Assets/i.test(label)) assetsTotalRow = r;
      if (/^Total Foreign Liabilities/i.test(label)) liabTotalRow = r;
    }
    var required = ["3.1", "3.2", "4", "5", "6", "7", "8.1", "8.2", "8.3", "8.4", "8.5", "8.6"];
    for (var q = 0; q < required.length; q++) {
      if (!rowOf[required[q]]) throw new Error('NBE open-position template anchor "' + required[q] + '" not found in sheet "' + sheetName + '"');
    }
    if (!assetsTotalRow || !liabTotalRow) throw new Error("Total Foreign Assets / Liabilities rows not found - not the NBE open-position template");
    // SBB/95/2025 Art. 23.9.2 adds the gold position to the overall NOP regardless of sign
    var goldRow = 0;
    for (var gr = rowOf["8.1"]; gr <= maxRow; gr++) {
      if (/gold/i.test(text(sheet, "C", gr))) { goldRow = gr; break; }
    }

    var headerRow = 7;
    for (var hr = 1; hr <= 12; hr++) if (text(sheet, "D", hr) === "USD") { headerRow = hr; break; }
    var asAt = text(sheet, "K", 3) || text(sheet, "D", 3);
    var unit = text(sheet, "U", 5) || "In Thousands";

    var currencyColumns = [];
    var dCol = XLSX.utils.decode_col("D"), wCol = XLSX.utils.decode_col("W");
    for (var hci = dCol; hci <= wCol; hci++) {
      var hCol = XLSX.utils.encode_col(hci);
      var hCode = text(sheet, hCol, headerRow);
      if (hCode) currencyColumns.push({ currency: hCode, column: hCol });
    }

    var currencies = [];
    for (var ci = 0; ci < currencyColumns.length; ci++) {
      var col = currencyColumns[ci].column;
      var code = currencyColumns[ci].currency;
      var totalAssets = num(sheet, col, assetsTotalRow) || 0;
      var totalLiabilities = num(sheet, col, liabTotalRow) || 0;
      var netLong = num(sheet, col, rowOf["3.1"]) || 0;
      var netShort = Math.abs(num(sheet, col, rowOf["3.2"]) || 0);
      var midRate = num(sheet, col, rowOf["4"]);
      var netLongBirr = num(sheet, col, rowOf["5"]) || 0;
      var netShortBirr = Math.abs(num(sheet, col, rowOf["6"]) || 0);
      var nopBirr = num(sheet, col, rowOf["7"]) || 0;
      var nopRatioPct = num(sheet, col, rowOf["7.1"]);
      var hasAnything = totalAssets || totalLiabilities || netLong || netShort || nopBirr;
      if (!code && !hasAnything) continue;
      var cur = { currency: code || ("COL-" + col), column: col, totalAssets: totalAssets, totalLiabilities: totalLiabilities, netLong: netLong, netShort: netShort, midRate: midRate, netLongBirr: netLongBirr, netShortBirr: netShortBirr, nopBirr: nopBirr, nopRatioPct: nopRatioPct };
      currencies.push(cur);
      if (!hasAnything) continue;

      var expLong = Math.max(totalAssets - totalLiabilities, 0);
      var expShort = Math.max(totalLiabilities - totalAssets, 0);
      if (Math.abs(expLong - netLong) > TOLERANCE) issues.push({ itemNo: "3.1·" + cur.currency, severity: "error", message: cur.currency + ": net long does not equal max(assets - liabilities, 0)", expected: expLong, actual: netLong });
      if (Math.abs(expShort - netShort) > TOLERANCE) issues.push({ itemNo: "3.2·" + cur.currency, severity: "error", message: cur.currency + ": net short does not equal max(liabilities - assets, 0)", expected: expShort, actual: netShort });
      if ((netLong > 0 || netShort > 0) && (midRate === null || midRate === 0)) {
        issues.push({ itemNo: "4·" + cur.currency, severity: "error", message: cur.currency + ": open position carried with no mid exchange rate - Birr conversion drops the exposure" });
      } else if (midRate !== null) {
        if (Math.abs(netLong * midRate - netLongBirr) > TOLERANCE * Math.max(1, midRate)) issues.push({ itemNo: "5·" + cur.currency, severity: "error", message: cur.currency + ": Birr long does not equal net long x mid rate", expected: netLong * midRate, actual: netLongBirr });
        if (Math.abs(netShort * midRate - netShortBirr) > TOLERANCE * Math.max(1, midRate)) issues.push({ itemNo: "6·" + cur.currency, severity: "error", message: cur.currency + ": Birr short does not equal net short x mid rate", expected: netShort * midRate, actual: netShortBirr });
      }
      if (Math.abs(Math.max(netLongBirr, netShortBirr) - nopBirr) > TOLERANCE) issues.push({ itemNo: "7·" + cur.currency, severity: "error", message: cur.currency + ": NOP does not equal the greater of Birr long/short", expected: Math.max(netLongBirr, netShortBirr), actual: nopBirr });
    }

    var lineItems = [];
    for (var lr = 1; lr <= maxRow; lr++) {
      var no = text(sheet, "B", lr);
      var label = text(sheet, "C", lr);
      if (!no && !label) continue;
      var values = {};
      var sum = 0;
      var hasValue = false;
      for (var lci = 0; lci < currencyColumns.length; lci++) {
        var value = num(sheet, currencyColumns[lci].column, lr) || 0;
        values[currencyColumns[lci].currency] = value;
        sum += value;
        if (Math.abs(value) > 0) hasValue = true;
      }
      var total = num(sheet, "X", lr);
      if (total === null) total = sum;
      if (Math.abs(total) > 0) hasValue = true;
      if (label && (no || hasValue)) lineItems.push({ no: no, label: label, row: lr, values: values, total: total });
    }

    var totalLongBirr = num(sheet, "X", rowOf["8.1"]) || 0;
    var totalShortBirr = Math.abs(num(sheet, "X", rowOf["8.2"]) || 0);
    var overallNopBirr = num(sheet, "X", rowOf["8.3"]) || 0;
    var tier1Capital = num(sheet, "X", rowOf["8.4"]) || 0;
    var limitBirr = num(sheet, "X", rowOf["8.5"]) || 0;
    var nopRatioPctOverall = num(sheet, "X", rowOf["8.6"]) || 0;

    var sumLong = 0, sumShort = 0;
    for (var s = 0; s < currencies.length; s++) { sumLong += currencies[s].netLongBirr; sumShort += currencies[s].netShortBirr; }
    var goldBirr = goldRow ? Math.abs(num(sheet, "X", goldRow) || 0) : 0;
    if (!goldRow) issues.push({ itemNo: "8.3", severity: "info", message: "Template carries no gold-position row; overall NOP recomputed without the gold add-on (SBB/95/2025 Art. 23.9.2 adds gold regardless of sign)" });
    if (Math.abs(sumLong - totalLongBirr) > TOLERANCE) issues.push({ itemNo: "8.1", severity: "error", message: "Total long position does not tie to the sum of per-currency Birr longs", expected: sumLong, actual: totalLongBirr });
    if (Math.abs(sumShort - totalShortBirr) > TOLERANCE) issues.push({ itemNo: "8.2", severity: "error", message: "Total short position does not tie to the sum of per-currency Birr shorts", expected: sumShort, actual: totalShortBirr });
    var recomputedOverall = Math.max(sumLong, sumShort) + goldBirr;
    if (Math.abs(recomputedOverall - overallNopBirr) > TOLERANCE) issues.push({ itemNo: "8.3", severity: "error", message: "Overall open position does not equal the greater of total long/short (plus gold)", expected: recomputedOverall, actual: overallNopBirr });
    if (tier1Capital > 0) {
      if (Math.abs(tier1Capital * 0.18 - limitBirr) > TOLERANCE * tier1Capital * 0.01) issues.push({ itemNo: "8.5", severity: "error", message: "Limit row does not equal 18% of Tier 1 capital", expected: tier1Capital * 0.18, actual: limitBirr });
      var expRatio = overallNopBirr / tier1Capital * 100;
      if (Math.abs(expRatio - nopRatioPctOverall) > 0.01) issues.push({ itemNo: "8.6", severity: "error", message: "NOP ratio does not equal overall position / Tier 1 x 100", expected: expRatio, actual: nopRatioPctOverall });
    } else {
      issues.push({ itemNo: "8.4", severity: "error", message: "Tier 1 capital missing - limit and ratio cannot be computed" });
    }

    var recomputedRatio = tier1Capital > 0 ? recomputedOverall / tier1Capital * 100 : nopRatioPctOverall;
    var breached = recomputedRatio > 18;
    issues.push({
      itemNo: "8.6", severity: breached ? "error" : "info",
      message: breached
        ? "Overall NOP ratio " + recomputedRatio.toFixed(2) + "% (recomputed) BREACHES the +/-18% of Tier 1 limit"
        : "Overall NOP ratio " + recomputedRatio.toFixed(2) + "% (recomputed) is within the +/-18% of Tier 1 limit (headroom " + (18 - recomputedRatio).toFixed(2) + " pts)"
    });

    return {
      asAt: asAt, unit: unit, currencies: currencies, lineItems: lineItems,
      overall: { totalLongBirr: totalLongBirr, totalShortBirr: totalShortBirr, overallNopBirr: overallNopBirr, tier1Capital: tier1Capital, limitBirr: limitBirr, limitPct: 18, nopRatioPct: nopRatioPctOverall, recomputedOverallBirr: recomputedOverall, recomputedRatioPct: recomputedRatio, breached: breached },
      issues: issues
    };
  }

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