Ratio of Supervision Hours

// ─── Configuration ───
// 0) Minimum session length (client hours) to count:
const minClientHours = 0.5;      // only count entries where p.hours > this
// 1) absolute-hour limit for non-UKCP supervision (BACP ceiling):
const maxNonUkcpHours   = 20;    // e.g. 20
// 2) percentage-limit (of total supervision) for non-UKCP:
const maxNonUkcpPercent = 100;   // e.g. 100% means no extra cap by percent
 
// ─── Date range ───
const startDate = new Date(`${dv.current()["Start Date"]}`);
const endDate   = new Date(`${dv.current()["End Date"]}`);
 
// ─── Safe number formatter ───
const fmt = n => {
  const num = Number(n);
  return isNaN(num) ? "N/A" : num.toFixed(2);
};
 
// ─── Helper to check array-or-single dates ───
function inRange(p) {
  const dates = Array.isArray(p.date) ? p.date : [p.date];
  return dates.some(d => {
    const dt = new Date(d);
    return dt >= startDate && dt <= endDate;
  });
}
 
// ─── 1) Sum client hours (only where hours > minClientHours) ───
 
let grandTotalClient = 0;
 
dv.pages('"100 CPD Record" and #clienthours')
 
  .where(inRange)
 
  .forEach(p => {
 
    const hrs = Number(p.hours || 0);
 
    if (!(hrs > minClientHours)) return; // strictly greater than threshold
 
  
 
    const dates = Array.isArray(p.date) ? p.date : [p.date];
 
    dates.forEach(d => {
 
      const dt = new Date(d);
 
      if (dt >= startDate && dt <= endDate) {
 
        grandTotalClient += hrs;
 
      }
 
    });
 
  });
 
// ─── 2) Sum supervision & UKCP hours (excluding peer supervision) ───
let grandTotalSup = 0, ukcpTotal = 0;
let formatTotals = { "1-to-1": 0, "group": 0, "peer": 0, "fishbowl": 0, "unspecified": 0 };
 
dv.pages('"100 CPD Record" and #supervision')
  .where(inRange)
  .forEach(p => {
    const dates = Array.isArray(p.date) ? p.date : [p.date];
    dates.forEach(d => {
      const dt = new Date(d);
      if (dt >= startDate && dt <= endDate) {
        const hrs = p.hours || 0;
        const rawFmt = (p.format || "unspecified").toString().toLowerCase();
        const allowed = ["1-to-1", "group", "peer", "fishbowl"];
        const normalised = allowed.includes(rawFmt) ? rawFmt : "unspecified";
 
        // Always record format hours
        formatTotals[normalised] = (formatTotals[normalised] || 0) + hrs;
 
        // Only include non-peer hours in totals
        if (normalised !== "peer") {
          grandTotalSup += hrs;
          if ((p.Body || "").toLowerCase() === "ukcp") {
            ukcpTotal += hrs;
          }
        }
      }
    });
  });
 
// ─── 3) Compute non-UKCP hours & percent ───
const nonUkcpTotal   = grandTotalSup - ukcpTotal;
const nonUkcpPercent = grandTotalSup > 0
  ? (nonUkcpTotal / grandTotalSup) * 100
  : NaN;
 
// ─── 4) Warnings ───
if (nonUkcpTotal > maxNonUkcpHours) {
  dv.paragraph(
    `⚠️ **Warning:** Non-UKCP supervision is ${fmt(nonUkcpTotal)} hrs, exceeding your absolute BACP ceiling of ${fmt(maxNonUkcpHours)} hrs.`
  );
}
if (!isNaN(nonUkcpPercent) && nonUkcpPercent > maxNonUkcpPercent) {
  dv.paragraph(
    `⚠️ **Warning:** Non-UKCP supervision is ${fmt(nonUkcpPercent)} %, exceeding your ${fmt(maxNonUkcpPercent)} % limit.`
  );
}
 
// ─── 5) Determine allowed non-UKCP “actual” threshold ───
const p = maxNonUkcpPercent / 100;
const percentThresholdHours = ((1 / (1 - p)) - 1) * ukcpTotal;
 
let nonUkcpThreshold;
if (nonUkcpTotal <= maxNonUkcpHours && nonUkcpPercent <= maxNonUkcpPercent) {
  nonUkcpThreshold = nonUkcpTotal;
} else {
  nonUkcpThreshold = Math.min(maxNonUkcpHours, percentThresholdHours);
}
 
// ─── 6) Compute Current Actual Ratio ───
const currentActualRatio = (ukcpTotal + nonUkcpThreshold) > 0
  ? fmt(grandTotalClient / (ukcpTotal + nonUkcpThreshold))
  : "N/A";
 
// ─── 7) Other ratios ───
const ratioAll  = grandTotalSup    > 0 ? fmt(grandTotalClient / grandTotalSup) : "N/A";
const ratioUKCP = ukcpTotal        > 0 ? fmt(grandTotalClient / ukcpTotal)   : "N/A";
 
// ─── 8) Render header & thresholds ───
dv.header(2, "Supervision Summary & Ratios");
dv.paragraph(`Allowed Non-UKCP Threshold (hrs): ${fmt(maxNonUkcpHours)} (BACP ceiling)`);
dv.paragraph(`Allowed Non-UKCP Threshold (%): ${fmt(maxNonUkcpPercent)}`);
 
// ─── 9) Render main table ───
const formatRows = Object.entries(formatTotals).map(
  ([f, hrs]) => [`Supervision (${f})`, fmt(hrs)]
);
 
// ─── 10) Explicit list of supervision records with unspecified format ───
 
const unspecifiedRows = [];
 
dv.pages('"100 CPD Record" and #supervision')
  .where(inRange)
  .forEach(p => {
    const dates = Array.isArray(p.date) ? p.date : [p.date];
 
    dates.forEach(d => {
      const dt = new Date(d);
 
      if (dt >= startDate && dt <= endDate) {
        const rawFmt = (p.format ?? "").toString().trim().toLowerCase();
        const allowed = ["1-to-1", "group", "peer", "fishbowl"];
 
        const isUnspecified =
          rawFmt === "" ||
          rawFmt === "unspecified" ||
          !allowed.includes(rawFmt);
 
        if (isUnspecified) {
          unspecifiedRows.push([
            d,
            p.file.link,
            p.hours ?? "",
            p.format ?? "",
            p.Body ?? ""
          ]);
        }
      }
    });
  });
 
dv.header(2, "Supervision Records Counted as Unspecified");
 
if (unspecifiedRows.length === 0) {
  dv.paragraph("No supervision records are currently being counted as unspecified.");
} else {
  dv.table(
    ["Date", "Record", "Hours", "Format", "Body"],
    unspecifiedRows.sort((a, b) => new Date(a[0]) - new Date(b[0]))
  );
}
 
dv.table(
  ["Calculation", "Result"],
  [
    ["**Grand Total Client Hours**", fmt(grandTotalClient)],
    ["Grand Total Supervision Hours (excl. peer)", fmt(grandTotalSup)],
    ["UKCP Supervision Hours", fmt(ukcpTotal)],
    ["Non-UKCP Supervision Hours", fmt(nonUkcpTotal)],
    ["Allowed Non-UKCP Actual (hrs)", fmt(nonUkcpThreshold)],
    ["**Total Allowable Supervision Hours (ex. peer)**", fmt(ukcpTotal + nonUkcpThreshold)],
    ...formatRows,
    ["Client ÷ All Supervision (excl. peer)", ratioAll],
    ["**Current Actual Ratio (Client ÷ (UKCP + allowed non-UKCP))**", currentActualRatio]
  ]
);