Client Activity this 12 month and previous 12 month

// ─────────────────────────────────────────────────────────────
// Client Hours by Rolling 12-Month Periods — grouped + stacked
// Filters: Agency, Start Date, End Date
// Rules:
//   - include only sessions with hours > 0.5
// Graph:
//   - Period 1 = last 12 months to today
//   - Period 2 = 12 months immediately before that
//   - for each month: two bars (comparison vs current), each stacked by agency
// Tables:
//   - monthly totals by period
//   - period totals by agency
// ─────────────────────────────────────────────────────────────
 
const MIN_HOURS = 0.5;
 
const getFM = (k) => dv.current()?.[k];
 
const FILTER_AGENCY = (getFM("Agency Filter") ?? getFM("agency filter") ?? "").toString().trim();
const rawStart = getFM("Start Date") ?? getFM("start date") ?? "";
const rawEnd   = getFM("End Date")   ?? getFM("end date")   ?? "";
 
const SVGNS = "http://www.w3.org/2000/svg";
 
// Helpers
const firstOf = (v) => Array.isArray(v) ? v[0] : v;
 
const toDate = (v) => {
  v = firstOf(v);
  if (!v) return null;
  const d = new Date(v);
  return Number.isNaN(d.getTime()) ? null : d;
};
 
const toNumber = (v) => {
  v = firstOf(v);
  if (v == null || v === "") return 0;
  const n = parseFloat(String(v).trim());
  return Number.isNaN(n) ? 0 : n;
};
 
const svgEl = (name, attrs = {}) => {
  const el = document.createElementNS(SVGNS, name);
  for (const [k, v] of Object.entries(attrs)) el.setAttribute(k, String(v));
  return el;
};
 
const fmtHours = (n) => Number(n || 0).toFixed(2);
 
const fmtISODate = (d) => {
  if (!d) return "—";
  const y = d.getFullYear();
  const m = String(d.getMonth() + 1).padStart(2, "0");
  const day = String(d.getDate()).padStart(2, "0");
  return `${y}-${m}-${day}`;
};
 
const fmtMonthYear = (d) =>
  d.toLocaleString("en-GB", { month: "short", year: "numeric" });
 
const startOfDay = (d) => {
  const x = new Date(d);
  x.setHours(0, 0, 0, 0);
  return x;
};
 
const endOfDay = (d) => {
  const x = new Date(d);
  x.setHours(23, 59, 59, 999);
  return x;
};
 
const startOfMonth = (d) => new Date(d.getFullYear(), d.getMonth(), 1);
const endOfMonth = (d) => new Date(d.getFullYear(), d.getMonth() + 1, 0, 23, 59, 59, 999);
const addMonths = (d, n) => new Date(d.getFullYear(), d.getMonth() + n, d.getDate());
const monthKey = (d) => `${d.getFullYear()}-${String(d.getMonth() + 1).padStart(2, "0")}`;
 
// Optional outer filter window from frontmatter
let START_DATE = toDate(rawStart);
let END_DATE   = toDate(rawEnd);
 
if (START_DATE && END_DATE && START_DATE > END_DATE) {
  const tmp = START_DATE;
  START_DATE = END_DATE;
  END_DATE = tmp;
}
 
// ------------------------------------------------------------------
// Rolling 12-month periods
// ------------------------------------------------------------------
const today = new Date();
const todayStart = startOfDay(today);
const todayEnd = endOfDay(today);
 
const currentPeriodEnd = todayEnd;
const currentPeriodStart = startOfMonth(addMonths(today, -11));
 
const previousPeriodStart = startOfMonth(addMonths(currentPeriodStart, -12));
const previousPeriodEnd = endOfMonth(addMonths(currentPeriodStart, -1));
 
const currentMonths = Array.from({ length: 12 }, (_, i) => startOfMonth(addMonths(currentPeriodStart, i)));
const previousMonths = currentMonths.map(d => startOfMonth(addMonths(d, -12)));
 
// 1) Load + normalise records
const src = dv.pages("#clienthours").where(p => p.clientref && p.date);
 
const records = Array.from(src).map(p => ({
  clientref: firstOf(p.clientref),
  agency: (firstOf(p.agency) ?? "Unknown").toString().trim() || "Unknown",
  date: toDate(p.date),
  hours: toNumber(p.hours)
})).filter(r => r.date !== null);
 
// 2) Apply outer filters and minimum-hours rule
const filtered = records.filter(r => {
  const agencyOk = !FILTER_AGENCY || r.agency === FILTER_AGENCY;
  const startOk  = !START_DATE || r.date >= START_DATE;
  const endOk    = !END_DATE   || r.date <= END_DATE;
  const hoursOk  = r.hours > MIN_HOURS;
  return agencyOk && startOk && endOk && hoursOk;
});
 
// 3) Gather agencies
const agencies = Array.from(new Set(filtered.map(r => r.agency))).sort((a, b) => a.localeCompare(b));
 
// Ensure we still work if no data
if (agencies.length === 0) agencies.push("Unknown");
 
// 4) Aggregate by month + agency
// monthAgencyTotals["YYYY-MM"]["Agency"] = hours
const monthAgencyTotals = new Map();
 
for (const r of filtered) {
  const mk = monthKey(r.date);
  if (!monthAgencyTotals.has(mk)) monthAgencyTotals.set(mk, new Map());
  const byAgency = monthAgencyTotals.get(mk);
  byAgency.set(r.agency, (byAgency.get(r.agency) ?? 0) + r.hours);
}
 
// 5) Build comparison rows
const monthlyComparison = currentMonths.map((currMonthStart, i) => {
  const prevMonthStart = previousMonths[i];
  const currKey = monthKey(currMonthStart);
  const prevKey = monthKey(prevMonthStart);
 
  const currMap = monthAgencyTotals.get(currKey) ?? new Map();
  const prevMap = monthAgencyTotals.get(prevKey) ?? new Map();
 
  const currentByAgency = Object.fromEntries(
    agencies.map(a => [a, currMap.get(a) ?? 0])
  );
  const previousByAgency = Object.fromEntries(
    agencies.map(a => [a, prevMap.get(a) ?? 0])
  );
 
  const currentTotal = agencies.reduce((acc, a) => acc + (currentByAgency[a] ?? 0), 0);
  const previousTotal = agencies.reduce((acc, a) => acc + (previousByAgency[a] ?? 0), 0);
 
  return {
    monthLabel: currMonthStart.toLocaleString("en-GB", { month: "short" }),
    currentMonthLabel: fmtMonthYear(currMonthStart),
    previousMonthLabel: fmtMonthYear(prevMonthStart),
    currentByAgency,
    previousByAgency,
    currentTotal,
    previousTotal
  };
});
 
// 6) Period totals by agency
const currentTotalsByAgency = Object.fromEntries(agencies.map(a => [a, 0]));
const previousTotalsByAgency = Object.fromEntries(agencies.map(a => [a, 0]));
 
monthlyComparison.forEach(row => {
  agencies.forEach(a => {
    currentTotalsByAgency[a] += row.currentByAgency[a] ?? 0;
    previousTotalsByAgency[a] += row.previousByAgency[a] ?? 0;
  });
});
 
const currentTotal = agencies.reduce((acc, a) => acc + currentTotalsByAgency[a], 0);
const previousTotal = agencies.reduce((acc, a) => acc + previousTotalsByAgency[a], 0);
 
// 7) Summary text
const bits = [];
if (FILTER_AGENCY) bits.push(`Agency: **${FILTER_AGENCY}**`);
if (START_DATE || END_DATE) bits.push(`Outer Window: **${fmtISODate(START_DATE)} → ${fmtISODate(END_DATE)}**`);
bits.push(`Min Session Hours: **>${MIN_HOURS}**`);
bits.push(`Current Period: **${fmtISODate(currentPeriodStart)} → ${fmtISODate(currentPeriodEnd)}**`);
bits.push(`Comparison Period: **${fmtISODate(previousPeriodStart)} → ${fmtISODate(previousPeriodEnd)}**`);
 
dv.paragraph(`Client hours by rolling 12-month periods (${bits.join(" · ")}):`);
 
// 8) Monthly totals table
dv.header(3, "Monthly client hours comparison");
dv.table(
  ["Month", "Comparison Period", "Last 12 Months"],
  monthlyComparison.map(r => [
    `${r.monthLabel} (${r.previousMonthLabel} / ${r.currentMonthLabel})`,
    fmtHours(r.previousTotal),
    fmtHours(r.currentTotal)
  ])
);
 
// 9) Period totals by agency table
dv.header(3, "Period totals by agency");
dv.table(
  ["Agency", "Comparison Period", "Last 12 Months"],
  [
    ...agencies.map(a => [a, fmtHours(previousTotalsByAgency[a]), fmtHours(currentTotalsByAgency[a])]),
    ["Total", fmtHours(previousTotal), fmtHours(currentTotal)]
  ]
);
 
// 10) SVG chart (render only in live Obsidian preview context)
if (typeof document !== "undefined" && this?.container?.createEl) {
const container = this.container.createEl("div", { cls: "client-hours-chart" });
container.style.width = "100%";
container.style.maxWidth = "1100px";
 
const width = 1100;
const baseHeight = 360;
const legendRowH = 18;
const legendTitleH = 16;
const legendPadding = 10;
const legendH = (2 + agencies.length) * legendRowH + legendTitleH + legendPadding;
const height = baseHeight + legendH;
 
const margin = { top: 28, right: 20, bottom: 56, left: 56 };
const innerW = width - margin.left - margin.right;
const innerH = baseHeight - margin.top - margin.bottom;
 
const svg = svgEl("svg", {
  viewBox: `0 0 ${width} ${height}`,
  preserveAspectRatio: "xMidYMid meet"
});
svg.style.width = "100%";
svg.style.height = "auto";
container.appendChild(svg);
 
// Definitions / patterns
const defs = svgEl("defs");
svg.appendChild(defs);
 
const palette = [
  "#4C78A8", "#F58518", "#54A24B", "#E45756", "#72B7B2",
  "#B279A2", "#FF9DA6", "#9D755D", "#BAB0AC"
];
 
function addHatchPattern(id, stroke, angleDeg, spacing, strokeW) {
  const p = svgEl("pattern", {
    id,
    patternUnits: "userSpaceOnUse",
    width: spacing,
    height: spacing,
    patternTransform: `rotate(${angleDeg})`
  });
  p.appendChild(svgEl("line", {
    x1: 0, y1: 0,
    x2: 0, y2: spacing,
    stroke,
    "stroke-width": strokeW,
    "stroke-linecap": "square",
    "stroke-opacity": "0.9"
  }));
  defs.appendChild(p);
}
 
// One style per agency
const agencyStyle = new Map();
 
agencies.forEach((agency, idx) => {
  const colour = palette[idx % palette.length];
  const angle = (idx % 4) * 45;
  const spacing = 6 + (idx % 3) * 2;
  const strokeW = 1 + ((idx % 2) * 0.4);
  const patId = `agency-hatch-${idx}`;
 
  addHatchPattern(patId, colour, angle, spacing, strokeW);
 
  agencyStyle.set(agency, {
    fill: colour,
    fillOpacity: 0.35,
    patternUrl: `url(#${patId})`
  });
});
 
// Period outline style
const periodOutline = new Map([
  ["previous", { strokeDasharray: "4 2", strokeOpacity: 0.45 }],
  ["current",  { strokeDasharray: "",    strokeOpacity: 0.25 }]
]);
 
// Scales
const maxY = Math.max(
  1,
  ...monthlyComparison.flatMap(d => [d.previousTotal, d.currentTotal])
);
 
const xStep = innerW / monthlyComparison.length;
const groupW = xStep * 0.76;
const barGap = 8;
const barW = Math.max(8, (groupW - barGap) / 2);
 
const groupX = i => margin.left + i * xStep + (xStep - groupW) / 2;
const y = v => margin.top + innerH - (v / maxY) * innerH;
 
// Axes
const axisG = svg.appendChild(svgEl("g"));
 
axisG.appendChild(svgEl("line", {
  x1: margin.left, y1: margin.top,
  x2: margin.left, y2: margin.top + innerH,
  stroke: "currentColor"
}));
 
axisG.appendChild(svgEl("line", {
  x1: margin.left, y1: margin.top + innerH,
  x2: margin.left + innerW, y2: margin.top + innerH,
  stroke: "currentColor"
}));
 
const yTicks = Math.min(6, Math.max(1, Math.ceil(maxY)));
for (let t = 0; t <= yTicks; t++) {
  const val = (t / yTicks) * maxY;
  const yy = y(val);
 
  axisG.appendChild(svgEl("line", {
    x1: margin.left,
    y1: yy,
    x2: margin.left + innerW,
    y2: yy,
    stroke: "currentColor",
    "stroke-opacity": "0.15"
  }));
 
  const lbl = svgEl("text", {
    x: margin.left - 8,
    y: yy + 4,
    "text-anchor": "end",
    fill: "currentColor"
  });
  lbl.style.fontSize = "12px";
  lbl.textContent = fmtHours(val);
  axisG.appendChild(lbl);
}
 
// Bars
const barsG = svg.appendChild(svgEl("g"));
 
monthlyComparison.forEach((d, i) => {
  const gx = groupX(i);
 
  const seriesValues = [
    { key: "previous", byAgency: d.previousByAgency, total: d.previousTotal, x: gx },
    { key: "current",  byAgency: d.currentByAgency,  total: d.currentTotal,  x: gx + barW + barGap }
  ];
 
  seriesValues.forEach(({ key, byAgency, total, x }) => {
    let cumulative = 0;
 
    agencies.forEach(agency => {
      const value = byAgency[agency] ?? 0;
      if (!value) return;
 
      const y0 = y(cumulative);
      const y1 = y(cumulative + value);
      const h = Math.max(1, y0 - y1);
 
      const st = agencyStyle.get(agency);
 
      barsG.appendChild(svgEl("rect", {
        x, y: y1, width: barW, height: h,
        fill: st.fill,
        "fill-opacity": String(st.fillOpacity),
        stroke: "none"
      }));
 
      barsG.appendChild(svgEl("rect", {
        x, y: y1, width: barW, height: h,
        fill: st.patternUrl,
        "fill-opacity": "1",
        stroke: "currentColor",
        "stroke-opacity": "0.10"
      }));
 
      cumulative += value;
    });
 
    // Period outline around the whole bar
    if (total > 0) {
      const yy = y(total);
      const h = Math.max(1, margin.top + innerH - yy);
      const po = periodOutline.get(key);
 
      barsG.appendChild(svgEl("rect", {
        x, y: yy, width: barW, height: h,
        fill: "none",
        stroke: "currentColor",
        "stroke-opacity": String(po.strokeOpacity),
        "stroke-dasharray": po.strokeDasharray
      }));
    } else {
      barsG.appendChild(svgEl("rect", {
        x,
        y: margin.top + innerH - 1,
        width: barW,
        height: 1,
        fill: "currentColor",
        "fill-opacity": "0.15"
      }));
    }
  });
 
  const xl = svgEl("text", {
    x: margin.left + i * xStep + xStep / 2,
    y: margin.top + innerH + 18,
    "text-anchor": "middle",
    fill: "currentColor"
  });
  xl.style.fontSize = "12px";
  xl.textContent = d.monthLabel;
  barsG.appendChild(xl);
 
  const sub = svgEl("text", {
    x: margin.left + i * xStep + xStep / 2,
    y: margin.top + innerH + 32,
    "text-anchor": "middle",
    fill: "currentColor"
  });
  sub.style.fontSize = "10px";
  sub.style.opacity = "0.7";
  sub.textContent = d.currentMonthLabel.split(" ")[1];
  barsG.appendChild(sub);
});
 
// Title
const title = svgEl("text", {
  x: margin.left,
  y: 18,
  fill: "currentColor"
});
title.style.fontSize = "14px";
title.style.fontWeight = "600";
title.textContent = "Client Hours by Month (Last 12 Months vs Previous 12 Months; stacked by agency)";
svg.appendChild(title);
 
// Legend below chart
const legendG = svg.appendChild(svgEl("g"));
const legendX = margin.left;
const legendTop = baseHeight + 14;
const boxW = 14;
const boxH = 10;
 
// Legend title
const legendTitle = svgEl("text", {
  x: legendX,
  y: legendTop,
  fill: "currentColor"
});
legendTitle.style.fontSize = "12px";
legendTitle.style.fontWeight = "600";
legendTitle.textContent = "Key";
legendG.appendChild(legendTitle);
 
// Period styles
[
  ["Comparison Period", "previous"],
  ["Last 12 Months", "current"]
].forEach(([label, key], idx) => {
  const yRow = legendTop + legendTitleH + idx * legendRowH;
  const po = periodOutline.get(key);
 
  legendG.appendChild(svgEl("rect", {
    x: legendX,
    y: yRow - boxH + 2,
    width: boxW,
    height: boxH,
    fill: "none",
    stroke: "currentColor",
    "stroke-opacity": String(po.strokeOpacity),
    "stroke-dasharray": po.strokeDasharray
  }));
 
  const t = svgEl("text", {
    x: legendX + boxW + 8,
    y: yRow + 2,
    fill: "currentColor"
  });
  t.style.fontSize = "12px";
  t.textContent = label;
  legendG.appendChild(t);
});
 
// Agency styles
agencies.forEach((agency, idx) => {
  const st = agencyStyle.get(agency);
  const yRow = legendTop + legendTitleH + (2 + idx) * legendRowH;
 
  legendG.appendChild(svgEl("rect", {
    x: legendX,
    y: yRow - boxH + 2,
    width: boxW,
    height: boxH,
    fill: st.fill,
    "fill-opacity": String(st.fillOpacity),
    stroke: "none"
  }));
 
  legendG.appendChild(svgEl("rect", {
    x: legendX,
    y: yRow - boxH + 2,
    width: boxW,
    height: boxH,
    fill: st.patternUrl,
    stroke: "currentColor",
    "stroke-opacity": "0.35"
  }));
 
  const t = svgEl("text", {
    x: legendX + boxW + 8,
    y: yRow + 2,
    fill: "currentColor"
  });
  t.style.fontSize = "12px";
  t.textContent = agency;
  legendG.appendChild(t);
});
} else {
  dv.paragraph("_Chart omitted in markdown export context._");
}