// Self-contained HTML client reports — inline styles + inline SVG so they
// render in browsers and (mostly) in email clients. Saved under reports/.

import fs from 'node:fs';
import path from 'node:path';
import { fileURLToPath } from 'node:url';
import {
  loadSites, totals, timeseries, breakdown,
  portfolioTotals, portfolioTimeseries,
  resolveRange, previousRange, searchPages, pageDetail, searchTerms,
} from './ga4.js';
import { trafficPeaks } from './peaks.js';
import { computeInsights } from './insights.js';
import { releaseTracker, mediaRadar, reconReport } from './releases.js';
import { emailOverview } from './email.js';
import { aiSummary } from './chat.js';
import { legacyOverview, releasesByDate, journalistRanking, emailEngagers, legacyRelease, quietReleases, uploadStats } from './legacy.js';
import { monitoringOverview, monitoringInsights, monitoringEvidence } from './monitoring.js';
import { recordReport } from './repstore.js';
import { spawnSync } from 'node:child_process';
// (article screenshots retired — highlight images now come from the tenant's media library)

const __dirname = path.dirname(fileURLToPath(import.meta.url));
export const REPORTS_DIR = path.join(__dirname, '..', 'reports');

const fmtInt = (n) => Math.round(n || 0).toLocaleString('en-GB');
const pctOf = (c, p) => (p ? (c - p) / p : null);
const deltaHtml = (c, p, invert = false) => {
  const d = pctOf(c, p);
  if (d == null || Math.abs(d) < 0.001) return '<span style="color:#898781">·</span>';
  const good = invert ? d < 0 : d > 0;
  const color = good ? '#006300' : '#a02c2c';
  return `<span style="color:${color};font-weight:600">${d > 0 ? '↑' : '↓'} ${Math.abs(d * 100).toFixed(1)}%</span>`;
};

// Optional `annos` = [{date, label}] — Hyundai-style highlight callouts: the
// label is drawn in the headroom above the line with a connector down to the
// peak point it explains.
function svgLine(rows, key, width = 640, height = 150, annos = []) {
  if (!rows.length) return '';
  const vals = rows.map((r) => r[key] || 0);
  const max = Math.max(...vals, 1);
  const usable = (annos || []).filter((a) => rows.some((r) => r.date === a.date));
  // Strip a shared leading brand word ("Hyundai …") so labels stay distinctive.
  let annoPrefix = '';
  if (usable.length >= 5) {
    const counts = {};
    usable.forEach((a) => {
      const f2 = (String(a.label).match(/^([A-Za-z][\w'&-]*)\s+\S/) || [])[1];
      if (f2) counts[f2] = (counts[f2] || 0) + 1;
    });
    const [bestWord, n] = Object.entries(counts).sort((a, b) => b[1] - a[1])[0] || [];
    if (bestWord && n >= usable.length * 0.6) annoPrefix = bestWord + ' ';
  }
  const laneCount = usable.length > 8 ? 6 : usable.length > 4 ? 4 : 3;
  const annoLen = usable.length > 8 ? 24 : usable.length > 4 ? 28 : 34;
  const annoPad = usable.length ? 4 + laneCount * 13 : 0;
  const H = height + annoPad;
  const padL = 6, padB = 4, padT = 6 + annoPad;
  const x = (i) => padL + ((width - 12) * (rows.length === 1 ? 0.5 : i / (rows.length - 1)));
  const y = (v) => padT + (H - padT - padB) * (1 - v / max);
  const line = vals.map((v, i) => `${i === 0 ? 'M' : 'L'}${x(i).toFixed(1)},${y(v).toFixed(1)}`).join(' ');
  const area = `${line} L${x(vals.length - 1).toFixed(1)},${y(0)} L${x(0).toFixed(1)},${y(0)} Z`;
  // Lay the callout labels out in up to 3 lanes so they don't collide.
  const laneEnd = [];
  const callouts = usable.map((a) => {
    const i = rows.findIndex((r) => r.date === a.date);
    const px = x(i), py = y(vals[i]);
    let full = String(a.label);
    if (annoPrefix && full.startsWith(annoPrefix)) full = full.slice(annoPrefix.length);
    const text = full.length > annoLen ? full.slice(0, annoLen - 1) + '…' : full;
    const w = text.length * 5.8 + 8;
    let anchor = 'middle', tx = px;
    if (px - w / 2 < padL) { anchor = 'start'; tx = padL; }
    else if (px + w / 2 > width - 6) { anchor = 'end'; tx = width - 6; }
    const x0 = anchor === 'middle' ? tx - w / 2 : anchor === 'start' ? tx : tx - w;
    let lane = 0;
    while (lane < laneCount - 1 && (laneEnd[lane] ?? -1e9) > x0 - 8) lane++;
    laneEnd[lane] = x0 + w;
    const ty = 11 + lane * 13;
    return `<g>
      <line x1="${px.toFixed(1)}" x2="${px.toFixed(1)}" y1="${ty + 3}" y2="${(py - 5).toFixed(1)}" stroke="#0b0b0b" stroke-width="1" opacity="0.55"/>
      <circle cx="${px.toFixed(1)}" cy="${py.toFixed(1)}" r="3.2" fill="#fff" stroke="#eb6834" stroke-width="2"/>
      <text x="${tx.toFixed(1)}" y="${ty}" text-anchor="${anchor}" font-size="10.5" font-weight="700" fill="#0b0b0b" style="paint-order:stroke" stroke="#ffffff" stroke-width="3">${text.replace(/&/g, '&amp;').replace(/</g, '&lt;')}</text>
    </g>`;
  }).join('');
  return `<svg width="${width}" height="${H}" viewBox="0 0 ${width} ${H}" style="max-width:100%">
    <path d="${area}" fill="#eb6834" opacity="0.12"/>
    <path d="${line}" fill="none" stroke="#eb6834" stroke-width="2"/>
    ${callouts}
  </svg>
  <div style="display:flex;justify-content:space-between;color:#898781;font-size:11px"><span>${rows[0].date}</span><span>${rows[rows.length - 1].date}</span></div>`;
}

function barsHtml(rows, valueKey, max = null) {
  const m = max ?? Math.max(1, ...rows.map((r) => r[valueKey] || 0));
  return rows.map((r) => `
    <table role="presentation" width="100%" cellpadding="0" cellspacing="0" style="margin:3px 0"><tr>
      <td style="width:170px;font-size:13px;color:#52514e;padding-right:8px;white-space:nowrap;overflow:hidden">${r.dimensionValue}</td>
      <td><div style="background:#eb6834;border-radius:4px;height:13px;width:${Math.max(2, (100 * (r[valueKey] || 0)) / m)}%"></div></td>
      <td style="width:70px;text-align:right;font-size:12.5px;color:#52514e">${fmtInt(r[valueKey])}</td>
    </tr></table>`).join('');
}

const INS_STYLE = { up: ['#006300', '▲ WIN'], down: ['#a02c2c', '▼ DOWN'], watch: ['#8a5a00', '⚠ WATCH'], info: ['#898781', '• NOTE'] };

function insightsHtml(insights) {
  return insights.map((i) => {
    const [color, label] = INS_STYLE[i.kind] || INS_STYLE.info;
    return `<div style="margin:9px 0">
      <span style="color:${color};font-weight:700;font-size:11px;border:1px solid ${color};border-radius:6px;padding:1px 6px">${label}</span>
      <span style="font-size:13.5px;color:#0b0b0b"> ${i.fact}</span>
      ${i.suggestion ? `<div style="font-size:12.5px;color:#52514e;margin:2px 0 0 4px">→ ${i.suggestion}</div>` : ''}
    </div>`;
  }).join('');
}

// Client CI applied to a finished report: the accent colour recolours every
// chart/bar/donut (all built on the default #eb6834), the logo + client name
// replace the SparkSoftware header, and the font swaps in via Google Fonts.
// Mix a hex colour towards black (f<0) or white (f>0) — used to derive the
// client's secondary chart colours from their single accent.
function mixHex(hex, f) {
  const h = hex.replace('#', '');
  const x = h.length === 3 ? h.split('').map((c) => c + c).join('') : h;
  const n = parseInt(x, 16);
  const ch = (v) => Math.max(0, Math.min(255, Math.round(f < 0 ? v * (1 + f) : v + (255 - v) * f)));
  return '#' + [(n >> 16) & 255, (n >> 8) & 255, n & 255].map((v) => ch(v).toString(16).padStart(2, '0')).join('');
}

function applyCi(html, ci) {
  if (!ci) return html;
  let out = html;
  if (ci.accent && /^#[0-9a-f]{3,8}$/i.test(ci.accent)) {
    out = out.replace(/#eb6834/gi, ci.accent);
    // Secondary palette follows the brand: the reach line / second series
    // (default indigo) becomes a dark shade of the accent, the light slice
    // a tint — semantic greens/reds (sentiment) stay untouched.
    out = out.replace(/#6e62e5/gi, mixHex(ci.accent, -0.45));
    out = out.replace(/#f0956a/gi, mixHex(ci.accent, 0.45));
  }
  if (ci.font) {
    const fam = String(ci.font).replace(/[^\w \-]/g, '').trim();
    if (fam) out = out.replace('</head>',
      `<link rel="stylesheet" href="https://fonts.googleapis.com/css2?family=${encodeURIComponent(fam).replace(/%20/g, '+')}:wght@400;600;700;800&display=swap"></head>`)
      .replace(/font-family:system-ui,-apple-system,'Segoe UI',sans-serif/g, `font-family:'${fam}',system-ui,-apple-system,'Segoe UI',sans-serif`);
  }
  return out;
}

function ciHeader(ci) {
  if (!ci || (!ci.logo && !ci.name)) return `<div style="font-size:20px;font-weight:700">Spark<span style="color:#eb6834">Software</span> Reporting</div>`;
  const logo = ci.logo ? `<img src="${String(ci.logo).replace(/"/g, '&quot;')}" style="height:42px;max-width:220px;object-fit:contain;display:block" alt=""/>` : '';
  const name = ci.name ? `<div style="font-size:20px;font-weight:700">${String(ci.name).replace(/</g, '&lt;')}</div>` : '';
  return `<div style="display:flex;align-items:center;gap:12px">${logo}${logo && name ? '' : name}${logo && name ? name : ''}</div>`;
}

// The CI pack for a site — colours/font/logo from the admin's client
// settings, with the client's display name as the report header fallback.
function siteCi(scope) {
  const s = loadSites().find((x) => x.id === scope);
  if (!s?.ci) return null;
  return { ...s.ci, name: s.ci.name || s.name };
}

function shell(title, periodLabel, inner, ci = null) {
  return applyCi(`<!doctype html><html><head><meta charset="utf-8"><title>${title}</title></head>
<body style="margin:0;background:#f9f9f7;font-family:system-ui,-apple-system,'Segoe UI',sans-serif;color:#0b0b0b">
  <div style="max-width:760px;margin:0 auto;padding:24px 16px">
    ${ciHeader(ci)}
    <div style="color:#898781;font-size:13px;margin:2px 0 18px">${title} · ${periodLabel}</div>
    ${inner}
    <div style="color:#898781;font-size:11.5px;margin-top:22px;border-top:1px solid #e1e0d9;padding-top:10px">
      Generated by SparkSoftware Reporting from live Google Analytics data.
    </div>
  </div>
  <script>
  /* Interactive layer — sortable tables, filters, collapsible sections.  */
  (function () {
    document.querySelectorAll('table').forEach(function (tbl) {
      var head = tbl.querySelector('tr'); if (!head) return;
      var ths = head.querySelectorAll('th'); if (!ths.length) return;
      ths.forEach(function (th, ci) {
        th.style.cursor = 'pointer'; th.title = 'Click to sort';
        th.addEventListener('click', function () {
          var rows = Array.prototype.slice.call(tbl.querySelectorAll('tr')).slice(1);
          var dir = th.dataset.dir === 'd' ? 1 : -1; th.dataset.dir = dir === -1 ? 'd' : 'a';
          rows.sort(function (r1, r2) {
            var a = (r1.children[ci] || {}).textContent || '', b = (r2.children[ci] || {}).textContent || '';
            var na = parseFloat(a.replace(/[^0-9.\-]/g, '')), nb = parseFloat(b.replace(/[^0-9.\-]/g, ''));
            if (!isNaN(na) && !isNaN(nb)) return dir * (na - nb);
            return dir * a.localeCompare(b);
          });
          rows.forEach(function (r) { r.parentNode.appendChild(r); });
        });
      });
      if (tbl.querySelectorAll('tr').length > 10) {
        var inp = document.createElement('input');
        inp.placeholder = 'Filter this table…';
        inp.className = 'rfilter';
        inp.style.cssText = 'margin:0 0 8px;padding:6px 11px;border:1px solid #ddd;border-radius:8px;font:inherit;font-size:12.5px;width:230px';
        tbl.parentNode.insertBefore(inp, tbl);
        inp.addEventListener('input', function () {
          var q = inp.value.toLowerCase();
          Array.prototype.slice.call(tbl.querySelectorAll('tr')).slice(1).forEach(function (r) {
            r.style.display = r.textContent.toLowerCase().indexOf(q) > -1 ? '' : 'none';
          });
        });
      }
    });
    document.querySelectorAll('.rc-t').forEach(function (t) {
      if (!t.textContent.trim()) return;
      t.style.cursor = 'pointer';
      t.title = 'Click to collapse / expand';
      t.addEventListener('click', function () {
        var b = t.parentNode.querySelector('.rc-b');
        if (b) b.style.display = b.style.display === 'none' ? '' : 'none';
      });
    });
  })();
  </script>
  <style>
  /* Graphs and cards must never be sliced across PDF/print page breaks. */
  @media print { .rfilter { display: none } .rc-b { display: block !important } }
  .rc, .hl-card { break-inside: avoid; page-break-inside: avoid; }
  tr { break-inside: avoid; page-break-inside: avoid; }
  svg { break-inside: avoid; page-break-inside: avoid; }
  </style>
</body></html>`, ci);
}

const card = (title, inner) =>
  `<div class="rc" style="background:#fcfcfb;border:1px solid rgba(11,11,11,0.1);border-radius:12px;padding:16px;margin:0 0 14px">
    <div class="rc-t" style="font-size:13px;font-weight:650;color:#52514e;margin-bottom:10px">${title}</div><div class="rc-b">${inner}</div></div>`;

function tilesHtml(cur, prev) {
  const tiles = [
    ['Sessions', fmtInt(cur.sessions), deltaHtml(cur.sessions, prev.sessions)],
    ['Users', fmtInt(cur.totalUsers), deltaHtml(cur.totalUsers, prev.totalUsers)],
    ['Page views', fmtInt(cur.screenPageViews), deltaHtml(cur.screenPageViews, prev.screenPageViews)],
    ['Conversions', fmtInt(cur.conversions), deltaHtml(cur.conversions, prev.conversions)],
  ];
  return `<table role="presentation" width="100%" cellpadding="0" cellspacing="0"><tr>
    ${tiles.map(([l, v, d]) => `<td style="background:#fcfcfb;border:1px solid rgba(11,11,11,0.1);border-radius:12px;padding:12px;width:25%">
      <div style="font-size:11.5px;color:#898781">${l}</div>
      <div style="font-size:22px;font-weight:700;margin:2px 0">${v}</div>
      <div style="font-size:12px">${d}</div>
    </td><td style="width:8px"></td>`).join('')}
  </tr></table><div style="height:14px"></div>`;
}

// Per-section analyst commentary for one monitoring overview (brand OR a
// region) — the bullet notes under each chart, like the manual FEA report.
// Shared by the HTML/PDF report AND the PowerPoint deck so both carry the
// same written analysis. Returns {} when no AI key / no data / AI failure.
async function monNarrFor(monX, label2, ctx = null) {
    if (!process.env.OPENAI_API_KEY || !monX?.totals) return {};
    try {
      const peakDays2 = [...(monX.daily || [])]
        .sort((a2, b2) => (Number(b2.reach) || Number(b2.articles) || 0) - (Number(a2.reach) || Number(a2.articles) || 0))
        .slice(0, 4);
      // Evidence: the actual stories behind each number, so the analyst can
      // EXPLAIN movements (what the journalists wrote, which articles drove
      // sentiment, what ran on the peak days) — not just describe them.
      const evidence2 = ctx
        ? await monitoringEvidence(ctx.siteId, ctx.range, monX, ctx.marketId || null).catch(() => ({}))
        : {};
      // Daily statistics the manual report narrates: averages and coverage
      // gaps ("averaging 21 articles and 4M reach", "every day except June 7").
      const dl2 = monX.daily || [];
      const dailyStats2 = dl2.length ? {
        days: dl2.length,
        avgArticlesPerDay: Math.round(dl2.reduce((a2, d2) => a2 + (Number(d2.articles) || 0), 0) / dl2.length),
        avgReachPerDay: Math.round(dl2.reduce((a2, d2) => a2 + (Number(d2.reach) || 0), 0) / dl2.length),
        daysWithNoCoverage: dl2.filter((d2) => !Number(d2.articles)).map((d2) => String(d2.date).slice(0, 10)).slice(0, 6),
      } : null;
      const pack2 = {
        totals: monX.totals, biggestDays: peakDays2, dailyStats: dailyStats2, evidence: evidence2,
        sentiment: monX.sentiment, prevSentiment: monX.prevSentiment, sentimentByRegion: monX.sentimentByRegion,
        regions: monX.regions, marketSplit: monX.marketSplit,
        topics: monX.topics, prevTopics: monX.prevTopics,
        models: monX.models, prevModels: monX.prevModels,
        mediaTypes: monX.mediaTypes, prevMediaTypes: monX.prevMediaTypes,
        publicationStats: monX.pubStats,
        shareOfVoice: monX.shareOfVoice, topPublications: monX.topPublications, topJournalists: monX.topJournalists,
        topArticles: (monX.topArticles || []).slice(0, 10), releaseCoverage: monX.releaseCoverage,
        computedInsights: monitoringInsights(monX),
      };
      const raw2 = await aiSummary(pack2, label2, 'mynews-sections');
      try { return JSON.parse(raw2.replace(/^```(?:json)?\s*/i, '').replace(/```\s*$/, '').trim()); }
      catch { const m3 = /\{[\s\S]*\}/.exec(raw2); if (m3) { try { return JSON.parse(m3[0]); } catch { return {}; } } }
    } catch { /* commentary is optional */ }
    return {};
}

export async function generateReport(scope, rangeStr, style = 'full', opts0 = {}) {
  const range = resolveRange(rangeStr);
  const prev = previousRange(range);
  const periodLabel = `${range.startDate} → ${range.endDate}`;
  let title, inner = '';

  // ── Dedicated myNewspress monitoring report — the coverage deliverable
  // mirroring the "Media Monitoring and Insight" monthly (key metrics,
  // AI KEY INSIGHT, volume & reach, sentiment, regions, categories, models,
  // release→coverage, publications online/print, SOV, highlight articles).
  if (style === 'monitoring' && scope !== 'all') {
    const site0 = loadSites().find((s) => s.id === scope);
    let monTitle = `Media Monitoring & Insight — ${site0?.name || scope}`;
    let monInner = '';
    const mon = await monitoringOverview(scope, rangeStr, opts0.market || null).catch((e) => ({ enabled: true, error: String(e.message || e) }));
    // Selected market(s) — supports a comma-separated multi-select from the UI.
    const selIds = String(opts0.market || '').split(',').map((s) => s.trim()).filter(Boolean);
    // Merged markets carry comma-joined ids ('1,27') — a market matches when
    // ANY of its ids is selected.
    const marketMatches = (m2) => String(m2.id).split(',').some((x) => selIds.includes(x));
    const selNames = [...new Set((mon.markets || []).filter(marketMatches).map((m2) => m2.name))];
    if (selNames.length) monTitle += ` — ${selNames.join(' + ')}`;
    if (!mon.enabled || mon.error) {
      monInner = card('Media monitoring', `<div style="font-size:13px;color:#a02c2c">${mon.error || 'Monitoring is not enabled for this site.'}</div>`);
    } else {
      const narr = await monNarrFor(mon, site0?.name || scope, { siteId: scope, range: rangeStr, marketId: opts0.market || null });
      if (narr.keyInsight?.length) {
        monInner += card('KEY INSIGHT', `<div style="font-size:13.5px;line-height:1.55">${narr.keyInsight.map((b) => `<div style="margin:6px 0">${String(b).replace(/&/g, '&amp;').replace(/</g, '&lt;')}</div>`).join('')}</div>`);
      }
      // Article images come from the tenant's own media library (no more
      // live-site screenshots that captured cookie-consent walls) — when a
      // site has no monitoring.mediaBaseUrl the cards render as a clean list.
      monInner += monitoringReportHtml(mon, narr, {});
      const matchedMarkets = (mon.markets || []).filter(marketMatches);
      if (matchedMarkets.length > 1) {
        // The user picked SPECIFIC markets → break those down per market.
        const breakdownMarkets = matchedMarkets;
        const per = await Promise.all(breakdownMarkets.slice(0, 10).map((m2) =>
          monitoringOverview(scope, rangeStr, m2.id).then((o) => ({ m: m2, o })).catch(() => null)));
        const blocks = per.filter((x) => x && x.o?.totals?.articles > 0)
          .sort((a2, b2) => (b2.o.totals.reach || b2.o.totals.articles) - (a2.o.totals.reach || a2.o.totals.articles));
        if (blocks.length) {
          monInner += `<div style="margin:26px 0 10px;font-size:17px;font-weight:800">Market breakdown — ${blocks.length} selected market${blocks.length > 1 ? 's' : ''}</div>`;
          for (const { m: mk2, o } of blocks) monInner += marketBlockHtml(mk2, o);
        }
      } else {
        // Default run → FULL per-region deep-dives, exactly like the manual
        // monthly report's per-region sections: each region gets its own
        // complete block (KPIs vs previous, volume by day, market split
        // within the region, sentiment, categories, media type, top
        // publications online/print, share of voice, highlight articles) —
        // computed by scoping the whole overview to that region's markets.
        const regionIds = {};
        for (const mk2 of (mon.markets || [])) {
          if (mk2.region) (regionIds[mk2.region] = regionIds[mk2.region] || []).push(String(mk2.id));
        }
        const regs = (mon.regions || []).filter((rg) => (rg.articles || 0) > 0);
        const deepRegs = regs.filter((rg) => regionIds[rg.name]?.length).slice(0, 6);
        if (deepRegs.length > 1) {
          const per = await Promise.all(deepRegs.map((rg) =>
            monitoringOverview(scope, rangeStr, regionIds[rg.name].join(','))
              .then(async (o) => ({ rg, o, narr: await monNarrFor(o, `${site0?.name || scope} — ${rg.name}`, { siteId: scope, range: rangeStr, marketId: regionIds[rg.name].join(',') }) }))
              .catch(() => null)));
          for (const item of per) {
            if (!item || !item.o?.totals?.articles) continue;
            monInner += `<div style="margin:30px 0 12px;padding-top:14px;border-top:3px solid #eb6834;font-size:19px;font-weight:800;break-before:page;page-break-before:always">Region — ${item.rg.name}</div>`;
            if (item.narr?.keyInsight?.length) {
              monInner += card('KEY INSIGHT — ' + item.rg.name, `<div style="font-size:13.5px;line-height:1.55">${item.narr.keyInsight.map((b) => `<div style="margin:6px 0">${String(b).replace(/&/g, '&amp;').replace(/</g, '&lt;')}</div>`).join('')}</div>`);
            }
            monInner += monitoringReportHtml(item.o, item.narr || {}, {});
          }
          // Unassigned bucket keeps its light block so nothing is hidden.
          const un = regs.find((rg) => rg.name === 'Unassigned');
          if (un) monInner += regionBlockHtml(un, mon);
        } else if (regs.length > 1) {
          monInner += `<div style="margin:26px 0 10px;font-size:17px;font-weight:800">Region breakdown — ${regs.length} regions</div>`;
          for (const rg of regs) monInner += regionBlockHtml(rg, mon);
        }
      }
    }
    const monHtml = shell(monTitle, periodLabel, monInner, siteCi(scope));
    fs.mkdirSync(REPORTS_DIR, { recursive: true });
    const monStamp = new Date().toISOString().slice(0, 10);
    const monFile = `${monStamp}-${scope}-monitoring.html`;
    fs.writeFileSync(path.join(REPORTS_DIR, monFile), monHtml);
    // Also produce a PDF when a local Chrome is available (like AI reports)
    let monPdf = null;
    try {
      const pdfName = monFile.replace(/\.html$/, '.pdf');
      if (htmlToPdf(path.join(REPORTS_DIR, monFile), path.join(REPORTS_DIR, pdfName))) monPdf = pdfName;
    } catch { /* HTML is always there */ }
    recordReport({ file: monFile, siteId: scope, title: monTitle, style: 'monitoring', format: 'html', source: opts0.source || 'manual' });
    if (monPdf) recordReport({ file: monPdf, siteId: scope, title: monTitle, style: 'monitoring', format: 'pdf', source: opts0.source || 'manual' });
    return { filename: monFile, pdfFilename: monPdf, html: monHtml, title: monTitle };
  }

  const { insights } = await computeInsights(scope, rangeStr);

  if (scope === 'all') {
    title = 'Portfolio report — all websites';
    const [cur, prv, trend] = await Promise.all([
      portfolioTotals(range.startDate, range.endDate),
      portfolioTotals(prev.startDate, prev.endDate),
      portfolioTimeseries(range.startDate, range.endDate),
    ]);
    const prevById = Object.fromEntries(prv.perSite.map((s) => [s.id, s]));
    inner += tilesHtml(cur.totals, prv.totals);
    inner += card('Combined daily sessions', svgLine(trend, 'sessions'));
    inner += card('Key facts & suggestions', insightsHtml(insights));
    inner += card('Sites', `<table width="100%" cellpadding="5" cellspacing="0" style="font-size:13px;border-collapse:collapse">
      <tr style="color:#898781;font-size:11px;text-transform:uppercase;text-align:left"><th>Site</th><th style="text-align:right">Sessions</th><th style="text-align:right">vs prev.</th><th style="text-align:right">Conv.</th></tr>
      ${cur.perSite.sort((a, b) => b.sessions - a.sessions).map((s) => `<tr style="border-top:1px solid #e1e0d9">
        <td style="font-weight:600">${s.name}</td>
        <td style="text-align:right">${fmtInt(s.sessions)}</td>
        <td style="text-align:right">${deltaHtml(s.sessions, prevById[s.id]?.sessions)}</td>
        <td style="text-align:right">${fmtInt(s.conversions)}</td>
      </tr>`).join('')}</table>`);
  } else {
    const site = loadSites().find((s) => s.id === scope);
    title = style === 'table' ? `Release report — ${site?.name || scope}` : `Client report — ${site?.name || scope}`;
    const [cur, prv, trend, channels, pages, tracker, radar, email] = await Promise.all([
      totals(scope, range.startDate, range.endDate),
      totals(scope, prev.startDate, prev.endDate),
      timeseries(scope, range.startDate, range.endDate),
      breakdown(scope, 'sessionDefaultChannelGroup', range.startDate, range.endDate, 8),
      breakdown(scope, 'pagePath', range.startDate, range.endDate, 10),
      releaseTracker(scope, '90d').catch(() => null),
      mediaRadar(scope, rangeStr).catch(() => null),
      emailOverview(scope, rangeStr).catch(() => null),
    ]);
    // The data tables the content team actually sends (date order, no top-10)
    const [allRels, legacy, monitoring] = await Promise.all([
      releasesByDate(scope, rangeStr, 300).catch(() => []),
      legacyOverview(scope, rangeStr).catch(() => null),
      style === 'table' ? Promise.resolve(null) : monitoringOverview(scope, rangeStr).catch(() => null),
    ]);
    let tableCards = '';
    if (allRels?.length) {
      const isNP = allRels.some((r) => r.downloaded != null);
      tableCards += card(`All releases this period — by date (${allRels.length})`, `
        <table width="100%" cellpadding="5" cellspacing="0" style="font-size:12.5px;border-collapse:collapse">
        <tr style="color:#898781;font-size:11px;text-transform:uppercase;text-align:left">
          <th>Date</th><th>Release</th>${isNP ? '<th>Client</th>' : ''}<th style="text-align:right">Views</th>${isNP ? '<th style="text-align:right">Downloads</th><th style="text-align:right">Basket</th>' : ''}</tr>
        ${allRels.map((r) => `<tr style="border-top:1px solid #e1e0d9">
          <td style="white-space:nowrap">${String(r.published || '').slice(0, 10)}</td>
          <td>${(r.title || '#' + r.id)}</td>
          ${isNP ? `<td style="color:#898781">${r.companyName || '—'}</td>` : ''}
          <td style="text-align:right">${fmtInt(r.viewed)}</td>
          ${isNP ? `<td style="text-align:right">${fmtInt(r.downloaded)}</td><td style="text-align:right">${fmtInt(r.basket)}</td>` : ''}
        </tr>`).join('')}</table>`);
    }
    if (legacy?.media?.length) {
      const docs = legacy.media.filter((m) => m.type === 'document');
      const assets = legacy.media.filter((m) => m.type !== 'document');
      if (assets.length) tableCards += card('Media assets — views & downloads', `
        <table width="100%" cellpadding="5" cellspacing="0" style="font-size:12.5px;border-collapse:collapse">
        <tr style="color:#898781;font-size:11px;text-transform:uppercase;text-align:left"><th>Asset</th><th>Type</th><th style="text-align:right">Views</th><th style="text-align:right">Downloads</th></tr>
        ${assets.map((m) => `<tr style="border-top:1px solid #e1e0d9"><td>${m.name}</td><td style="color:#898781">${m.type}</td>
          <td style="text-align:right">${fmtInt(m.viewed)}</td><td style="text-align:right">${fmtInt(m.downloaded)}</td></tr>`).join('')}</table>`);
      if (docs.length) tableCards += card('Documents — spec sheets, PDFs & press kits', `
        <table width="100%" cellpadding="5" cellspacing="0" style="font-size:12.5px;border-collapse:collapse">
        <tr style="color:#898781;font-size:11px;text-transform:uppercase;text-align:left"><th>Document</th><th style="text-align:right">Views</th><th style="text-align:right">Downloads</th></tr>
        ${docs.map((m) => `<tr style="border-top:1px solid #e1e0d9"><td>${m.name}</td>
          <td style="text-align:right">${fmtInt(m.viewed)}</td><td style="text-align:right">${fmtInt(m.downloaded)}</td></tr>`).join('')}</table>`);
    }
    if (legacy?.models?.length) {
      tableCards += card('Model pages — views', barsHtml(legacy.models.map((m) => ({ dimensionValue: m.name, viewed: m.viewed })), 'viewed'));
    }
    if (legacy?.emails?.length) {
      tableCards += card('Email distributions — by date', `
        <table width="100%" cellpadding="5" cellspacing="0" style="font-size:12.5px;border-collapse:collapse">
        <tr style="color:#898781;font-size:11px;text-transform:uppercase;text-align:left"><th>Date</th><th>Campaign</th><th style="text-align:right">Recipients</th><th style="text-align:right">Opens</th><th style="text-align:right">Clicks</th></tr>
        ${legacy.emails.map((e2) => `<tr style="border-top:1px solid #e1e0d9">
          <td style="white-space:nowrap">${String(e2.sentDate || '').slice(0, 10)}</td>
          <td>${e2.title || e2.campaignId}</td>
          <td style="text-align:right">${fmtInt(e2.recipients)}</td>
          <td style="text-align:right">${fmtInt(e2.opened)}</td>
          <td style="text-align:right">${fmtInt(e2.clicked)}</td></tr>`).join('')}</table>`);
    }

    if (style === 'table') {
      // The plain client deliverable: just the tables, nothing else.
      inner = tableCards || card('No platform data', '<div style="font-size:13px">No reports database is connected for this site, so there is no release table to show.</div>');
      const html0 = shell(title, periodLabel, inner, siteCi(scope));
      fs.mkdirSync(REPORTS_DIR, { recursive: true });
      const stamp0 = new Date().toISOString().slice(0, 10);
      const filename0 = `${stamp0}-${scope}-table.html`;
      fs.writeFileSync(path.join(REPORTS_DIR, filename0), html0);
      recordReport({ file: filename0, siteId: scope, title, style: 'table', format: 'html', source: opts0.source || 'manual' });
      return { filename: filename0, html: html0, title };
    }

    inner += tilesHtml(cur, prv);
    // Highlights — daily sessions annotated with what caused each peak
    let peaks0 = [];
    try { peaks0 = await trafficPeaks(scope, rangeStr); } catch { /* additive */ }
    inner += card(peaks0.length ? 'Highlights — daily sessions, peaks annotated' : 'Daily sessions',
      svgLine(trend, 'sessions', 640, 150, peaks0.map((p) => ({ date: p.date, label: p.label }))));
    // What drove the traffic peaks — annotated spike days
    try {
      const peaks = peaks0;
      if (peaks.length) {
        const ICON = { release: '📰', email: '📧', page: '📈' };
        inner += card('What drove the traffic peaks', `
          <table width="100%" cellpadding="5" cellspacing="0" style="font-size:12.5px;border-collapse:collapse">
          <tr style="color:#898781;font-size:11px;text-transform:uppercase;text-align:left"><th>Day</th><th>Driven by</th><th style="text-align:right">Sessions</th></tr>
          ${peaks.map((p) => `<tr style="border-top:1px solid #e1e0d9">
            <td style="white-space:nowrap">${p.date}</td>
            <td>${ICON[p.kind] || '📈'} ${String(p.label).slice(0, 80)}${p.email && p.kind !== 'email' ? ' <span style="color:#898781">+ email send</span>' : ''}</td>
            <td style="text-align:right;font-weight:600">${fmtInt(p.sessions)}</td></tr>`).join('')}</table>
          <div style="color:#898781;font-size:11px;margin-top:6px">Spike days meaningfully above the period's typical day, attributed to that day's top release, page or email send.</div>`);
      }
    } catch { /* additive */ }
    // New vs returning + top day of week
    if (cur.totalUsers) {
      const nu = cur.newUsers || 0, ret = Math.max(0, cur.totalUsers - nu);
      inner += card('New vs returning users', barsHtml([
        { dimensionValue: `New users (${((100 * nu) / cur.totalUsers).toFixed(1)}%)`, users: nu },
        { dimensionValue: `Returning (${((100 * ret) / cur.totalUsers).toFixed(1)}%)`, users: ret },
      ], 'users'));
    }
    if (trend.length >= 7) {
      const DAYS = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'];
      const agg = DAYS.map(() => ({ u: 0, n: 0 }));
      trend.forEach((r) => {
        const dow = (new Date(r.date + 'T00:00:00Z').getUTCDay() + 6) % 7;
        agg[dow].u += r.totalUsers || 0; agg[dow].n++;
      });
      inner += card('Top days of the week — average active users',
        barsHtml(DAYS.map((day, i) => ({ dimensionValue: day, users: agg[i].n ? Math.round(agg[i].u / agg[i].n) : 0 }))
          .sort((a, b) => b.users - a.users), 'users'));
    }
    inner += card('Key facts & suggestions', insightsHtml(insights));
    inner += tableCards; // the main tables, right up top after the facts
    // Quietest releases — bottom performers, live ≥30 days
    try {
      const quiet = await quietReleases(scope, rangeStr, 10);
      if (quiet.length) inner += card('Quietest releases — bottom performers (live ≥ 30 days)', `
        <table width="100%" cellpadding="5" cellspacing="0" style="font-size:12.5px;border-collapse:collapse">
        <tr style="color:#898781;font-size:11px;text-transform:uppercase;text-align:left"><th>Release</th><th>Published</th><th style="text-align:right">Views</th></tr>
        ${quiet.map((r) => `<tr style="border-top:1px solid #e1e0d9">
          <td>${(r.title || '#' + r.id)}${r.companyName ? ` <span style="color:#898781">· ${r.companyName}</span>` : ''}</td>
          <td style="white-space:nowrap">${String(r.published || '—').slice(0, 10)}</td>
          <td style="text-align:right">${fmtInt(r.viewed)}</td></tr>`).join('')}</table>
        <div style="color:#898781;font-size:11px;margin-top:6px">Candidates for re-promotion — only releases live for at least a month qualify.</div>`);
    } catch { /* additive */ }
    if (tracker?.releases?.length) {
      inner += card(`Release performance (last 90 days, section ${tracker.releasePath})`, `
        <table width="100%" cellpadding="5" cellspacing="0" style="font-size:12.5px;border-collapse:collapse">
        <tr style="color:#898781;font-size:11px;text-transform:uppercase;text-align:left"><th>Release</th><th>Launched</th><th style="text-align:right">First 7 days</th><th style="text-align:right">vs avg</th></tr>
        ${tracker.releases.slice(0, 10).map((r) => `<tr style="border-top:1px solid #e1e0d9">
          <td>${r.path}</td><td>${r.preExisting ? 'earlier' : r.launch}</td>
          <td style="text-align:right">${fmtInt(r.first7)}</td>
          <td style="text-align:right;font-weight:600;color:${r.index >= 1 ? '#006300' : '#a02c2c'}">${r.index ? r.index.toFixed(1) + '×' : '—'}</td>
        </tr>`).join('')}</table>`);
    }
    if (radar?.newReferrers?.length) {
      inner += card('Earned media radar — new referring domains', barsHtml(radar.newReferrers, 'sessions'));
    }
    if (email?.enabled && email.campaigns?.length) {
      inner += card(`Email campaigns (${email.domain})`, `
        <div style="font-size:12.5px;color:#52514e;margin-bottom:8px">
          ${fmtInt(email.totals.sent)} emails sent · ${(email.totals.openRate * 100).toFixed(1)}% open rate · ${(email.totals.clickRate * 100).toFixed(1)}% click rate
        </div>
        <table width="100%" cellpadding="5" cellspacing="0" style="font-size:12.5px;border-collapse:collapse">
        <tr style="color:#898781;font-size:11px;text-transform:uppercase;text-align:left"><th>Campaign</th><th>Sent</th><th style="text-align:right">Recipients</th><th style="text-align:right">Opens</th><th style="text-align:right">Clicks</th></tr>
        ${email.campaigns.slice(0, 10).map((c) => `<tr style="border-top:1px solid #e1e0d9">
          <td>${c.name}</td><td>${c.date || '—'}</td>
          <td style="text-align:right">${fmtInt(c.sent)}</td>
          <td style="text-align:right">${(c.openRate * 100).toFixed(1)}%</td>
          <td style="text-align:right">${(c.clickRate * 100).toFixed(1)}%</td>
        </tr>`).join('')}</table>`);
    }
    inner += card('Traffic channels', barsHtml(channels, 'sessions'));
    inner += card('Top pages', barsHtml(pages, 'screenPageViews'));
    // On-site searches
    try {
      const terms = await searchTerms(scope, range.startDate, range.endDate, 15);
      if (terms.length) inner += card('Top on-site searches',
        barsHtml(terms.map((t2) => ({ dimensionValue: t2.term.slice(0, 50), searches: t2.searches })), 'searches'));
    } catch { /* additive */ }

    inner += await reconUploadsHtml(scope, rangeStr);
    if (monitoring?.enabled && monitoring.totals) {
      inner += monitoringReportHtml(monitoring);
    }
  }

  // AI executive summary on top, when configured.
  if (process.env.OPENAI_API_KEY) {
    try {
      const summary = await aiSummary({ period: range, computedInsights: insights, scope }, scope === 'all' ? 'the whole portfolio' : `the website ${scope}`);
      const summaryHtml = summary
        .replace(/&/g, '&amp;').replace(/</g, '&lt;')
        .replace(/^### (.*)$/gm, '<div style="font-weight:700;margin:10px 0 4px">$1</div>')
        .replace(/^## (.*)$/gm, '<div style="font-weight:700;margin:10px 0 4px">$1</div>')
        .replace(/\*\*([^*]+)\*\*/g, '<b>$1</b>')
        .replace(/^[-*] (.*)$/gm, '<div style="margin:3px 0 3px 12px">• $1</div>')
        .replace(/\n{2,}/g, '<br>');
      inner = card('Executive summary', `<div style="font-size:13.5px;line-height:1.5">${summaryHtml}</div>`) + inner;
    } catch { /* report still works without AI */ }
  }

  const html = shell(title, periodLabel, inner, siteCi(scope));
  fs.mkdirSync(REPORTS_DIR, { recursive: true });
  const stamp = new Date().toISOString().slice(0, 10);
  const filename = `${stamp}-${scope}.html`;
  fs.writeFileSync(path.join(REPORTS_DIR, filename), html);
  recordReport({ file: filename, siteId: scope, title, style, format: 'html', source: opts0.source || 'manual' });
  return { filename, html, title };
}

export function listReports() {
  if (!fs.existsSync(REPORTS_DIR)) return [];
  return fs.readdirSync(REPORTS_DIR)
    .filter((f) => f.endsWith('.html') || f.endsWith('.pdf') || f.endsWith('.pptx'))
    .map((f) => ({ file: f, mtime: fs.statSync(path.join(REPORTS_DIR, f)).mtimeMs }))
    .sort((a, b) => b.mtime - a.mtime)
    .map((r) => r.file);
}

// ── PowerPoint decks in the client's CI (zero-dep OOXML writer) ─────────
import { Deck } from './pptx.js';

const kf = (v) => v >= 1e6 ? (v / 1e6).toFixed(1) + 'M' : v >= 1000 ? Math.round(v / 1000) + 'k' : String(Math.round(v || 0));
const dl = (c, p) => (p ? `${c >= p ? '↑' : '↓'} ${Math.abs(((c - p) / p) * 100).toFixed(1)}% vs previous` : '');

// Resolve the client's logo (data URI from the admin upload, or an http(s)
// URL) into a PNG/JPEG buffer for embedding in the deck. Best-effort.
async function ciLogoBuf(ci) {
  const src = ci?.logo;
  if (!src) return null;
  try {
    if (/^data:image\/(png|jpe?g);base64,/i.test(src)) {
      return { buf: Buffer.from(String(src).replace(/^data:[^,]+,/, ''), 'base64'), ext: /jpe?g/i.test(String(src).slice(0, 24)) ? 'jpeg' : 'png' };
    }
    if (/^https?:\/\//i.test(src)) {
      const r = await fetch(src, { signal: AbortSignal.timeout(8000) });
      if (!r.ok) return null;
      const ct = String(r.headers.get('content-type') || '');
      if (!/image\/(png|jpe?g)/i.test(ct) && !/\.(png|jpe?g)(\?|$)/i.test(src)) return null;
      const buf = Buffer.from(await r.arrayBuffer());
      if (!buf.length || buf.length > 2_000_000) return null;
      return { buf, ext: /jpe?g/i.test(ct) || /\.jpe?g(\?|$)/i.test(src) ? 'jpeg' : 'png' };
    }
  } catch { /* the logo is optional — the deck works without it */ }
  return null;
}

export async function pptxReport({ siteId, range: rangeStr, companyId = null, releaseId = null, title, subtitle, keyInsights = [], ci = {}, allowed = null, monitoring = false, market = null, source: source0 = 'ai' }) {
  const range = resolveRange(rangeStr || '28d');
  const site = loadSites().find((s) => s.id === siteId);
  const brand = { ...(site?.ci || {}), ...ci };
  const deck = new Deck({
    primary: brand.primary || '#131312',
    accent: brand.accent || brand.primary || '#EB6834',
    name: brand.name || (companyId ? subtitle : site?.name) || 'SparkSoftware Reporting',
    font: brand.font || null,
  });
  const brandLogo = await ciLogoBuf(brand);
  if (brandLogo) deck.setLogo(brandLogo.buf, brandLogo.ext);
  deck.title(title || `Client report — ${site?.name || siteId}`, subtitle || `${range.startDate} → ${range.endDate}`);
  if (keyInsights.length) deck.bullets('Key insights', keyInsights);

  if (monitoring) {
    // MEDIA MONITORING deck — myNewspress coverage only, no GA/platform data.
    // Mirrors the manual monthly deck: brand-level slides first, then a FULL
    // slide group per region (KPIs, market split, sentiment, publications,
    // share of voice, highlights) — computed by scoping the overview to each
    // region's markets.
    // narr = per-section AI analyst commentary (same 'mynews-sections' pack
    // the PDF/HTML report uses) — rendered as bullet notes UNDER each chart
    // so the deck matches the manual FEA monthly report exactly.
    const monSlides = (mon, prefix = '', narr = {}) => {
      const t = mon.totals;
      const sent = mon.sentiment || [];
      const totS = sent.reduce((a, x) => a + (x.articles || 0), 0);
      const posP = totS ? Math.round((100 * sent.filter((x) => String(x.name).includes('positive')).reduce((a, x) => a + x.articles, 0)) / totS) : null;
      const T = (s) => (prefix ? `${prefix} — ${s}` : s);
      deck.kpis(T('Coverage at a glance'), [
        { label: 'ARTICLES', value: kf(t.articles || 0), delta: dl(t.articles, t.prevArticles) },
        { label: 'TOTAL REACH', value: kf(t.reach || 0), delta: dl(t.reach, t.prevReach) },
        ...(t.ave ? [{ label: 'AVE', value: '£' + kf(t.ave), delta: dl(t.ave, t.prevAve) }] : []),
        ...(posP != null ? [{ label: 'POSITIVE', value: posP + '%', delta: '' }] : []),
        ...(t.publications ? [{ label: 'PUBLICATIONS', value: kf(t.publications), delta: '' }] : []),
      ], { notes: narr.volumeReach });
      // Volume & Reach vs previous period — the report's MoM table.
      const pct = (c, p) => (p ? `${c >= p ? '+' : ''}${Math.round(((c - p) / p) * 100)}%` : '—');
      if (t.prevArticles || t.prevReach) {
        deck.table(T('Volume & Reach — vs previous period'), ['KPIs', 'Previous', 'This period', 'MoM'], [
          ['Volume', Number(t.prevArticles || 0).toLocaleString('en-GB'), Number(t.articles || 0).toLocaleString('en-GB'), pct(t.articles, t.prevArticles)],
          ['Reach', kf(t.prevReach || 0), kf(t.reach || 0), pct(t.reach, t.prevReach)],
          ...(t.ave || t.prevAve ? [['AVE', t.prevAve ? '£' + kf(t.prevAve) : '—', t.ave ? '£' + kf(t.ave) : '—', pct(t.ave, t.prevAve)]] : []),
        ]);
      }
      // At a glance — the campaign view (top 5 journalists / publications /
      // articles side by side, like the report card).
      if (mon.topJournalists?.length || mon.topPublications?.length || mon.topArticles?.length) {
        const five = (arr, f) => (arr || []).slice(0, 5).map(f);
        const j5 = five(mon.topJournalists, (j) => j.name), p5 = five(mon.topPublications, (p) => p.name),
          a5 = five(mon.topArticles, (a) => `${String(a.title).slice(0, 52)} (${a.reach ? kf(a.reach) : a.publication || ''})`);
        const rows5 = Array.from({ length: Math.max(j5.length, p5.length, a5.length) }, (_, i) =>
          [j5[i] ? `${i + 1}. ${j5[i]}` : '', p5[i] ? `${i + 1}. ${p5[i]}` : '', a5[i] ? `${i + 1}. ${a5[i]}` : '']);
        if (rows5.length) deck.table(T('At a glance — the campaign view'), ['Top 5 journalists', 'Top 5 publications', 'Top 5 articles'],
          rows5, { widths: [1, 1, 2.1], clip: [30, 30, 64] });
      }
      // Volume & reach over time — articles bars + reach line, one chart.
      if (mon.daily?.length > 1) {
        const peakR = Math.max(0, ...mon.daily.map((d) => Number(d.reach) || 0));
        deck.vbar(T('Volume & reach over time'), mon.daily.map((d) => ({ date: d.date, v: d.articles })), {
          key: 'v', notes: narr.overtime,
          line: mon.daily.map((d) => Number(d.reach) || 0),
          lineLabel: peakR ? `reach (peak ${kf(peakR)})` : 'reach', barLabel: 'articles',
        });
      }
      if (mon.sentiment?.length) deck.hbar(T('Overall sentiment — articles'), mon.sentiment.map((s) => ({ name: s.name, value: s.articles })), { valueFmt: kf, notes: narr.sentiment });
      // Sentiment by region — brand level only (a region-scoped section IS
      // one region already).
      if (!prefix && mon.sentimentByRegion?.length) {
        const byRg = new Map();
        for (const x of mon.sentimentByRegion) {
          const row = byRg.get(x.region) || { positive: 0, neutral: 0, negative: 0, other: 0, total: 0 };
          const k = ['positive', 'neutral', 'negative'].find((s) => String(x.sentiment).toLowerCase().includes(s)) || 'other';
          row[k] += x.articles || 0; row.total += x.articles || 0;
          byRg.set(x.region, row);
        }
        const rows2 = [...byRg.entries()].sort((a, b) => b[1].total - a[1].total)
          .map(([rg2, v]) => [rg2, kf(v.positive), kf(v.neutral), kf(v.negative), kf(v.total)]);
        if (rows2.length) deck.table(T('Sentiment by region — articles'), ['Region', 'Positive', 'Neutral', 'Negative', 'Total'],
          rows2, { widths: [1.8, 1, 1, 1, 1], notes: narr.sentimentByRegion });
      }
      if (!prefix && mon.regions?.length) deck.hbar(T('Region split — articles'), mon.regions.slice(0, 10).map((r) => ({ name: r.name, value: r.articles })), { valueFmt: kf, notes: narr.regions });
      if (mon.marketSplit?.length > 1) deck.hbar(T('Market split — articles'), mon.marketSplit.slice(0, 10).map((r) => ({ name: `${r.name} (${r.reachShare || 0}% of reach)`, value: r.articles })), { valueFmt: kf, notes: narr.marketSplit });
      if (mon.topics?.length) deck.hbar(T('Coverage category — articles'), mon.topics.slice(0, 8).map((s) => ({ name: s.name, value: s.articles })), { valueFmt: kf, notes: narr.topics });
      if (mon.mediaTypes?.length) deck.hbar(T('Media type — articles'), mon.mediaTypes.map((m) => ({ name: m.name, value: m.articles })), { valueFmt: kf, notes: narr.mediaTypes });
      if (mon.models?.length) deck.hbar(T('Current model — coverage volume'), mon.models.slice(0, 8).map((s) => ({ name: s.name, value: s.articles })), { valueFmt: kf, notes: narr.models });
      // Press release engagement vs coverage reach — how each release
      // converted platform interest into earned media.
      const rcov = (mon.releaseCoverage || []).filter(Boolean);
      if (rcov.length) deck.table(T('Press release engagement vs coverage reach'), ['Release', 'Platform engagement', 'Articles', 'Coverage reach'],
        rcov.slice(0, 12).map((x) => [String(x.title).slice(0, 58), x.engagements != null ? kf(x.engagements) : '—', x.articles ? kf(x.articles) : 'none', x.reach ? kf(x.reach) : '—']),
        { widths: [2.6, 1, 0.7, 1], clip: [58, 12, 10, 12], notes: narr.releases });
      if (mon.topPublications?.length) deck.table(T('Top publications — by reach'), ['Publication', 'Type', 'Articles', 'Reach'],
        mon.topPublications.slice(0, 12).map((p) => [p.name, p.mediaType || '—', kf(p.articles || 0), p.reach ? kf(p.reach) : '—']),
        { widths: [2.2, 1, 0.8, 0.8], clip: [46, 14, 8, 10], notes: narr.publications });
      if (mon.topJournalists?.length) deck.hbar(T('Top journalists — writing the coverage'), mon.topJournalists.slice(0, 10).map((j) => ({ name: j.name, value: j.articles })), { valueFmt: kf, notes: narr.journalists });
      if (mon.shareOfVoice?.length) deck.hbar(T('Share of voice — by reach'), mon.shareOfVoice.slice(0, 10).map((s) => ({ name: s.name, value: s.reach || s.articles || 0 })), { valueFmt: kf, notes: narr.shareOfVoice });
      // Monthly highlights — the card grid, then the full ranked table.
      if (mon.topArticles?.length) {
        deck.cards(T('Monthly highlights'), mon.topArticles.slice(0, 6).map((a) => ({
          title: a.title, sentiment: a.sentiment,
          meta: [a.market, String(a.date || '').slice(0, 10), a.publication, a.reach ? 'Reach: ' + kf(a.reach) : null].filter(Boolean).join(' | '),
        })), { notes: narr.articles });
        deck.table(T('All highlight articles — by reach'), ['Article', 'Publication', 'Date', 'Sentiment', 'Reach'],
          mon.topArticles.slice(0, 12).map((a) => [String(a.title).slice(0, 58), a.publication || '—', String(a.date || '—').slice(0, 10), a.sentiment || '—', a.reach ? kf(a.reach) : '—']),
          { widths: [2.6, 1.1, 0.9, 0.8, 0.7], clip: [58, 22, 10, 12, 8] });
      }
    };
    const mon = await monitoringOverview(siteId, rangeStr || '28d', market || null).catch(() => null);
    if (mon?.totals) {
      // Contents slide — the manual monthly opens with one.
      const tocRegions = (mon.regions || []).filter((r2) => (r2.articles || 0) > 0 && r2.name !== 'Unassigned').map((r2) => r2.name);
      if (!market && tocRegions.length > 1) {
        deck.bullets('Contents', [
          `${brand.name || site?.name || siteId} — brand performance`,
          ...tocRegions.map((n2) => `${n2} — regional performance`),
          'Computed insights & highlights',
        ]);
      }
      const narr = await monNarrFor(mon, brand.name || site?.name || siteId, { siteId, range: rangeStr || '28d', marketId: market || null });
      // Only ONE key-insight slide: if the caller (the AI chat tool) already
      // added its own "Key insights" slide, skip the narrative's duplicate.
      if (!keyInsights.length && narr.keyInsight?.length) deck.bullets('KEY INSIGHT', narr.keyInsight.slice(0, 8).map((b) => String(b)));
      monSlides(mon, '', narr);
      const ins = monitoringInsights(mon);
      if (ins?.length) deck.bullets('Computed insights', ins.slice(0, 8).map((i) => i.text || i.fact || String(i)));
      // Per-region slide groups (all-markets runs only — a specific market
      // selection IS already the scope).
      if (!market) {
        const regionIds = {};
        for (const mk2 of (mon.markets || [])) {
          if (mk2.region) (regionIds[mk2.region] = regionIds[mk2.region] || []).push(String(mk2.id));
        }
        const deepRegs = (mon.regions || []).filter((rg) => (rg.articles || 0) > 0 && regionIds[rg.name]?.length).slice(0, 6);
        if (deepRegs.length > 1) {
          const per = await Promise.all(deepRegs.map((rg) =>
            monitoringOverview(siteId, rangeStr || '28d', regionIds[rg.name].join(','))
              .then(async (o) => ({ rg, o, narr: await monNarrFor(o, `${brand.name || site?.name || siteId} — ${rg.name}`, { siteId, range: rangeStr || '28d', marketId: regionIds[rg.name].join(',') }) }))
              .catch(() => null)));
          for (const item of per) {
            if (!item || !item.o?.totals?.articles) continue;
            deck.title(`Region — ${item.rg.name}`, `${kf(item.o.totals.articles)} articles · ${kf(item.o.totals.reach)} reach`);
            if (item.narr?.keyInsight?.length) deck.bullets(`${item.rg.name} — KEY INSIGHT`, item.narr.keyInsight.slice(0, 8).map((b) => String(b)));
            monSlides(item.o, item.rg.name, item.narr || {});
            const ri = monitoringInsights(item.o);
            if (ri?.length) deck.bullets(`${item.rg.name} — insights`, ri.slice(0, 8).map((i) => i.text || i.fact || String(i)));
          }
        }
      }
      deck.title('Thank you', `${brand.name || site?.name || siteId} · ${range.startDate} → ${range.endDate}`);
    } else {
      deck.bullets('Media monitoring', ['No monitoring data available for this site/period.']);
    }
  } else if (releaseId) {
    // ONE RELEASE — nothing site-wide on any slide.
    const r = await legacyRelease(siteId, releaseId, rangeStr || '90d').catch(() => null);
    if (r) {
      let ga = null;
      try { ga = (await searchPages(siteId, String(releaseId), range.startDate, range.endDate))[0] || null; } catch { /* optional */ }
      deck.kpis('This release at a glance', [
        { label: 'PLATFORM VIEWS', value: kf(r.viewed || 0), delta: '' },
        { label: 'DOWNLOADS', value: r.downloaded != null ? kf(r.downloaded) : '—', delta: '' },
        { label: 'BASKET ADDS', value: r.basket != null ? kf(r.basket) : '—', delta: '' },
        { label: 'GA4 PAGE VIEWS', value: ga ? kf(ga.screenPageViews) : '—', delta: '' },
        { label: 'NAMED READERS', value: String((r.readers || []).length), delta: '' },
        { label: 'EMAIL RECIPIENTS', value: kf((r.emails || []).filter((e) => e.dedicated !== false).reduce((a, e) => a + (e.recipients || 0), 0)), delta: '' },
      ]);
      if (r.dailyEvents?.length > 1) deck.vbar('Interest over time — platform events per day', r.dailyEvents.map((d) => ({ date: d.date, v: d.events })), { key: 'v' });
      if (r.readers?.length) deck.table('Who read it — registered readers', ['Reader', 'Views', 'Downloads', 'Last seen'],
        r.readers.map((rd) => [rd.name || rd.email, kf(rd.views || 0), kf(rd.downloads || 0), String(rd.lastSeen || '—').slice(0, 10)]));
      if (r.assets?.length) deck.hbar('Assets in this release — downloads', r.assets.map((m) => ({ name: m.name, value: m.downloaded })), { valueFmt: kf });
      const dedicatedE = (r.emails || []).filter((e) => e.dedicated !== false);
      if (dedicatedE.length) deck.table('Email distribution', ['Campaign', 'Sent', 'Recipients', 'Opens', 'Clicks'],
        dedicatedE.map((e) => [e.title || e.campaignId, String(e.sentDate || '—').slice(0, 10), kf(e.recipients || 0), kf(e.opened || 0), kf(e.clicked || 0)]));
      if (r.countries?.length) deck.hbar('Reader countries — platform events', r.countries.map((c) => ({ name: c.country, value: c.events })), { valueFmt: kf });
    }
  } else if (siteId === 'all') {
    const [cur, prv, trend] = await Promise.all([
      portfolioTotals(range.startDate, range.endDate, allowed || null),
      portfolioTotals(previousRange(range).startDate, previousRange(range).endDate, allowed || null),
      portfolioTimeseries(range.startDate, range.endDate, allowed || null),
    ]);
    deck.kpis('Portfolio at a glance', [
      { label: 'SESSIONS', value: kf(cur.totals.sessions), delta: dl(cur.totals.sessions, prv.totals.sessions) },
      { label: 'USERS', value: kf(cur.totals.totalUsers), delta: dl(cur.totals.totalUsers, prv.totals.totalUsers) },
      { label: 'PAGE VIEWS', value: kf(cur.totals.screenPageViews), delta: dl(cur.totals.screenPageViews, prv.totals.screenPageViews) },
    ]);
    deck.vbar('Combined daily sessions', trend.map((t) => ({ date: t.date, v: t.sessions })), { key: 'v' });
    deck.hbar('Sessions by site', cur.perSite.sort((a, b) => b.sessions - a.sessions).slice(0, 10).map((s) => ({ name: s.name, value: s.sessions })), { valueFmt: kf });
  } else {
    const [cur, prv, trend, channels, legacy, ranking] = await Promise.all([
      totals(siteId, range.startDate, range.endDate),
      totals(siteId, previousRange(range).startDate, previousRange(range).endDate),
      timeseries(siteId, range.startDate, range.endDate),
      companyId ? Promise.resolve(null) : breakdown(siteId, 'sessionDefaultChannelGroup', range.startDate, range.endDate, 8),
      legacyOverview(siteId, rangeStr || '28d', companyId).catch(() => null),
      journalistRanking(siteId, rangeStr || '28d', { limit: 10, companyId }).catch(() => []),
    ]);
    if (!companyId) {
      deck.kpis('At a glance', [
        { label: 'SESSIONS', value: kf(cur.sessions), delta: dl(cur.sessions, prv.sessions) },
        { label: 'USERS', value: kf(cur.totalUsers), delta: dl(cur.totalUsers, prv.totalUsers) },
        { label: 'PAGE VIEWS', value: kf(cur.screenPageViews), delta: dl(cur.screenPageViews, prv.screenPageViews) },
        { label: 'RELEASES PUBLISHED', value: kf(legacy?.cadence?.total || 0), delta: legacy?.cadence?.prevTotal ? dl(legacy.cadence.total, legacy.cadence.prevTotal) : '' },
        { label: 'EMAIL DISTRIBUTIONS', value: kf(legacy?.cadence?.distTotal || 0), delta: legacy?.cadence?.distPrev ? dl(legacy.cadence.distTotal, legacy.cadence.distPrev) : '' },
        { label: 'AVG SESSION', value: `${Math.floor((cur.avgSessionDuration || 0) / 60)}m ${Math.round((cur.avgSessionDuration || 0) % 60)}s`, delta: '' },
      ]);
      deck.vbar('Daily sessions', trend.map((t) => ({ date: t.date, v: t.sessions })), { key: 'v' });
      if (channels?.length) deck.hbar('Traffic channels', channels.map((c) => ({ name: c.dimensionValue, value: c.sessions })), { valueFmt: kf });
    } else {
      deck.kpis('This client at a glance', [
        { label: 'RELEASES PUBLISHED', value: kf(legacy?.cadence?.total || 0), delta: legacy?.cadence?.prevTotal ? dl(legacy.cadence.total, legacy.cadence.prevTotal) : '' },
        { label: 'EMAIL DISTRIBUTIONS', value: kf(legacy?.cadence?.distTotal || 0), delta: legacy?.cadence?.distPrev ? dl(legacy.cadence.distTotal, legacy.cadence.distPrev) : '' },
        { label: 'TOP RELEASE VIEWS', value: kf(legacy?.releases?.[0]?.viewed || 0), delta: '' },
      ]);
    }
    if (legacy?.cadence?.days?.length) deck.vbar(`Releases published per day${companyId ? ' — this client' : ''}`, legacy.cadence.days.map((d) => ({ date: d.date, v: d.published })), { key: 'v' });
    if (legacy?.releases?.length) deck.hbar(`Top releases — platform views${companyId ? ' — this client' : ''}`, legacy.releases.slice(0, 10).map((r) => ({ name: r.title || '#' + r.id, value: r.viewed })), { valueFmt: kf });
    if (ranking?.length) deck.table(`Top journalists${companyId ? ' — this client' : ''}`,
      ['Journalist', 'Publication', 'Views', 'Downloads'],
      ranking.map((j) => [j.name || j.email, String(j.publications || '—').split(',')[0], kf(j.views || 0), kf(j.downloads || 0)]));
    if (legacy?.media?.length) deck.hbar(`Top media assets — downloads${companyId ? ' — this client' : ''}`, legacy.media.slice(0, 10).map((m) => ({ name: m.name, value: m.downloaded })), { valueFmt: kf });
    if (legacy?.emails?.length) deck.table(`Email campaigns${companyId ? ' — this client' : ''}`,
      ['Campaign', 'Date', 'Recipients', 'Opens', 'Clicks'],
      legacy.emails.slice(0, 10).map((e) => [e.title || e.campaignId, String(e.sentDate || '').slice(0, 10), kf(e.recipients || 0), kf(e.opened || 0), kf(e.clicked || 0)]));
  }
  const buf = deck.build();
  fs.mkdirSync(REPORTS_DIR, { recursive: true });
  const stamp = new Date().toISOString().replace(/[:T]/g, '-').slice(0, 16);
  const slug = String(title || siteId).toLowerCase().replace(/[^a-z0-9]+/g, '-').replace(/^-+|-+$/g, '').slice(0, 60) || 'deck';
  const filename = `${stamp}-${slug}.pptx`;
  fs.writeFileSync(path.join(REPORTS_DIR, filename), buf);
  recordReport({ file: filename, siteId, title: title || `Client report — ${site?.name || siteId}`, style: monitoring ? 'monitoring' : releaseId ? 'release' : 'full', format: 'pptx', source: source0 || 'ai' });
  return { filename, format: 'pptx' };
}

// ── AI-authored downloadable reports (markdown → styled HTML → PDF) ─────

// Minimal markdown renderer: headings, bold/italic/code, links, lists, tables, hr.
function mdToHtml(md) {
  const escHtml = (s) => s.replace(/&/g, '&amp;').replace(/</g, '&lt;').replace(/>/g, '&gt;');
  const inline = (s) => s
    .replace(/\[([^\]]+)\]\(([^)\s]+)\)/g, '<a href="$2" style="color:#c14c1e">$1</a>')
    .replace(/\*\*([^*]+)\*\*/g, '<b>$1</b>')
    .replace(/\*([^*]+)\*/g, '<i>$1</i>')
    .replace(/`([^`]+)`/g, '<code style="background:#f1f1ef;border-radius:4px;padding:0 4px">$1</code>');
  const lines = escHtml(md).split(/\r?\n/);
  let html = '', inList = null, tbl = null;
  const flushList = () => { if (inList) { html += `</${inList}>`; inList = null; } };
  const flushTbl = () => {
    if (!tbl) return;
    const [head, ...rows] = tbl;
    html += `<table style="border-collapse:collapse;width:100%;font-size:12.5px;margin:8px 0">
      <tr>${head.map((c) => `<th style="text-align:left;border-bottom:2px solid #d8d7d3;padding:5px 8px;color:#52514e;font-size:11px;text-transform:uppercase">${inline(c)}</th>`).join('')}</tr>
      ${rows.map((r) => `<tr>${r.map((c) => `<td style="border-bottom:1px solid #e8e7e3;padding:5px 8px">${inline(c)}</td>`).join('')}</tr>`).join('')}</table>`;
    tbl = null;
  };
  for (const raw of lines) {
    const line = raw.trimEnd();
    if (/^\s*\|.*\|\s*$/.test(line)) {
      const cells = line.replace(/^\s*\||\|\s*$/g, '').split('|').map((c) => c.trim());
      if (cells.every((c) => /^:?-{2,}:?$/.test(c))) continue; // separator row
      (tbl = tbl || []).push(cells);
      continue;
    }
    flushTbl();
    const h = /^(#{1,4})\s+(.*)$/.exec(line);
    if (h) { flushList(); html += `<div style="font-weight:750;font-size:${[19, 16.5, 14.5, 13.5][h[1].length - 1]}px;margin:${h[1].length === 1 ? 4 : 16}px 0 6px">${inline(h[2])}</div>`; continue; }
    if (/^(-{3,}|\*{3,})$/.test(line)) { flushList(); html += '<hr style="border:0;border-top:1px solid #e1e0d9;margin:14px 0"/>'; continue; }
    const li = /^\s*([-*]|\d+\.)\s+(.*)$/.exec(line);
    if (li) {
      const type = /\d/.test(li[1]) ? 'ol' : 'ul';
      if (inList !== type) { flushList(); html += `<${type} style="margin:6px 0;padding-left:22px">`; inList = type; }
      html += `<li style="margin:3px 0;font-size:13.5px">${inline(li[2])}</li>`;
      continue;
    }
    flushList();
    if (line.trim()) html += `<p style="margin:7px 0;font-size:13.5px;line-height:1.55">${inline(line)}</p>`;
  }
  flushList(); flushTbl();
  return html;
}

// Simple vertical-bar SVG (per-day counts) for report chart packs.
function svgBars(rows, key, width = 640, height = 120) {
  if (!rows?.length) return '';
  const vals = rows.map((r) => Number(r[key]) || 0);
  const max = Math.max(...vals, 1);
  const bw = Math.max(2, Math.floor(width / rows.length) - 2);
  const bars = vals.map((v, i) => {
    const h = Math.max(2, ((height - 18) * v) / max);
    return `<rect x="${i * (bw + 2)}" y="${height - 16 - h}" width="${bw}" height="${h}" rx="1.5" fill="#eb6834" opacity="0.85"><title>${rows[i].date}: ${v}</title></rect>`;
  }).join('');
  return `<svg width="${width}" height="${height}" viewBox="0 0 ${width} ${height}" style="max-width:100%">${bars}</svg>
  <div style="display:flex;justify-content:space-between;color:#898781;font-size:11px"><span>${rows[0].date}</span><span>${rows[rows.length - 1].date}</span></div>`;
}

// ── shared extra sections: recon + uploads YoY (dashboard reports + AI pack)
async function reconUploadsHtml(siteId, rangeStr) {
  let html = '';
  try {
    const r = await reconReport(siteId, rangeStr);
    if (r?.rows?.length) {
      const vis = r.visibility != null ? Math.round(r.visibility * 100) : null;
      html += card('Platform vs GA4 — why the numbers differ', `
        <div style="font-size:13px;margin-bottom:8px">${vis != null ? `GA4 typically sees <b>~${vis}%</b> of true platform traffic on this site (median release). ` : ''}Platform views are counted <b>server-side (every visitor)</b>; GA4 only records visitors who accept analytics cookies under GDPR/CCPA, minus ad-blockers. Where GA4 shows MORE than the platform, that page also reaches consumers and repeat visitors beyond the registered press audience. Platform figures are authoritative for views &amp; downloads; GA4 is used for sources, devices, journeys and audience splits.</div>
        <table width="100%" cellpadding="5" cellspacing="0" style="font-size:12.5px;border-collapse:collapse">
        <tr style="color:#898781;font-size:11px;text-transform:uppercase;text-align:left"><th>Release</th><th style="text-align:right">Platform views</th><th style="text-align:right">GA4 pageviews</th><th style="text-align:right">GA4 visibility</th></tr>
        ${r.rows.map((x) => `<tr style="border-top:1px solid #e1e0d9">
          <td>${String(x.title || '').slice(0, 65)}</td>
          <td style="text-align:right;font-weight:600">${fmtInt(x.platformViews)}</td>
          <td style="text-align:right">${fmtInt(x.gaViews)}</td>
          <td style="text-align:right">${x.platformViews ? Math.round((100 * x.gaViews) / x.platformViews) + '%' : '—'}</td></tr>`).join('')}</table>`);
    }
  } catch { /* additive */ }
  try {
    const s = await uploadStats(siteId, rangeStr);
    if (s?.months?.length) {
      const t = s.totals;
      const dd = (a, b) => (b ? `${a >= b ? '+' : ''}${Math.round(((a - b) / b) * 100)}%` : '—');
      html += card('Publishing volume — this year vs last year', `
        <div style="font-size:13px;margin-bottom:8px">
          Releases: <b>${fmtInt(t.releases)}</b> vs ${fmtInt(t.prevReleases)} last year (${dd(t.releases, t.prevReleases)}) ·
          Media uploads: <b>${fmtInt(t.media)}</b> vs ${fmtInt(t.prevMedia)} last year (${dd(t.media, t.prevMedia)})
        </div>
        <table width="100%" cellpadding="5" cellspacing="0" style="font-size:12.5px;border-collapse:collapse">
        <tr style="color:#898781;font-size:11px;text-transform:uppercase;text-align:left"><th>Month</th><th style="text-align:right">Releases</th><th style="text-align:right">LY</th><th style="text-align:right">Media</th><th style="text-align:right">LY</th></tr>
        ${s.months.map((m) => `<tr style="border-top:1px solid #e1e0d9">
          <td>${m.month}</td><td style="text-align:right;font-weight:600">${fmtInt(m.releases)}</td>
          <td style="text-align:right;color:#898781">${fmtInt(m.prevReleases)}</td>
          <td style="text-align:right;font-weight:600">${fmtInt(m.media)}</td>
          <td style="text-align:right;color:#898781">${fmtInt(m.prevMedia)}</td></tr>`).join('')}</table>`);
    }
  } catch { /* additive */ }
  return html;
}

// Compact per-market block for the all-markets report breakdown.
// One region's block in the monitoring report — volume, reach, positive
// share and the region's top two articles (from regionTopArticles).
function regionBlockHtml(rg, mon) {
  const sbr = (mon.sentimentByRegion || []).filter((x) => x.region === rg.name);
  const totS = sbr.reduce((a, x) => a + (x.articles || 0), 0);
  const posP = totS ? Math.round((100 * sbr.filter((x) => String(x.sentiment).includes('positive')).reduce((a, x) => a + x.articles, 0)) / totS) : null;
  const tops = (mon.regionTopArticles || {})[rg.name] || [];
  const unm = rg.name === 'Unassigned' && mon.unmappedMarkets?.length
    ? `<div style="font-size:11.5px;color:#898781;margin-bottom:6px">Markets with no region set in myNewspress: ${mon.unmappedMarkets.slice(0, 8).map((u) => `${u.market} (${fmtInt(u.articles)})`).join(', ')} — assign them to a region and they move out of this bucket.</div>`
    : '';
  return card(`Region — ${rg.name}`, `
    <div style="font-size:13px;margin-bottom:8px">
      <b>${fmtInt(rg.articles)}</b> articles · reach <b>${kfmtR(rg.reach)}</b>
      ${posP != null ? ` · <b>${posP}%</b> positive` : ''}
    </div>${unm}
    ${tops.length ? `
      <div style="font-weight:700;font-size:11.5px;color:#898781;text-transform:uppercase;margin-bottom:4px">Top articles</div>
      ${tops.map((a2) => `<div style="font-size:12.5px;margin:3px 0">${a2.url ? `<a href="${a2.url}" style="color:#0b0b0b">${String(a2.title).slice(0, 80)}</a>` : String(a2.title).slice(0, 80)} <span style="color:#898781">(${a2.publication || '—'}${a2.reach ? ' — ' + kfmtR(a2.reach) : ''}${a2.date ? ' — ' + String(a2.date).slice(0, 10) : ''})</span></div>`).join('')}` : '<div style="font-size:12px;color:#898781">No article detail for this region in the period.</div>'}`);
}

function marketBlockHtml(mk, o) {
  const t = o.totals || {};
  const dd = (a, b) => (b ? `${a >= b ? '+' : ''}${Math.round(((a - b) / b) * 100)}%` : '—');
  const sent = o.sentiment || [];
  const totS = sent.reduce((a, x) => a + (x.articles || 0), 0);
  const posP = totS ? Math.round((100 * sent.filter((x) => String(x.name).includes('positive')).reduce((a, x) => a + x.articles, 0)) / totS) : null;
  return card(`Market — ${mk.name}${mk.region ? ` (${mk.region})` : ''}`, `
    <div style="font-size:13px;margin-bottom:8px">
      <b>${fmtInt(t.articles)}</b> articles (${dd(t.articles, t.prevArticles)}) ·
      reach <b>${kfmtR(t.reach)}</b> (${dd(t.reach, t.prevReach)})
      ${t.ave ? ` · AVE <b>£${kfmtR(t.ave)}</b>` : ''}
      ${posP != null ? ` · <b>${posP}%</b> positive` : ''}
    </div>
    <table role="presentation" width="100%" cellpadding="0" cellspacing="0"><tr>
      <td style="width:44%;vertical-align:top">
        <div style="font-weight:700;font-size:11.5px;color:#898781;text-transform:uppercase;margin-bottom:4px">Top publications</div>
        ${(o.topPublications || []).slice(0, 5).map((p2) => `<div style="font-size:12.5px;margin:3px 0">${p2.name} <span style="color:#898781">— ${fmtInt(p2.articles)} · ${p2.reach ? kfmtR(p2.reach) : '—'}</span></div>`).join('') || '<div style="font-size:12px;color:#898781">—</div>'}
      </td>
      <td style="width:3%"></td>
      <td style="width:53%;vertical-align:top">
        <div style="font-weight:700;font-size:11.5px;color:#898781;text-transform:uppercase;margin-bottom:4px">Top articles</div>
        ${(o.topArticles || []).slice(0, 3).map((a2) => `<div style="font-size:12.5px;margin:3px 0">${a2.url ? `<a href="${a2.url}" style="color:#0b0b0b">${String(a2.title).slice(0, 70)}</a>` : String(a2.title).slice(0, 70)} <span style="color:#898781">(${a2.publication || '—'}${a2.reach ? ' — ' + kfmtR(a2.reach) : ''})</span></div>`).join('') || '<div style="font-size:12px;color:#898781">—</div>'}
      </td>
    </tr></table>`);
}

// ── myNewspress media-monitoring report block (shared by the dashboard
// reports and the AI chart pack): the full coverage story with intelligence.
const kfmtR = (n) => { n = Number(n) || 0; return n >= 1e9 ? (n / 1e9).toFixed(1) + 'B' : n >= 1e6 ? (n / 1e6).toFixed(1) + 'M' : n >= 1e3 ? (n / 1e3).toFixed(1) + 'k' : String(n); };

// Static SVG donut with legend — sentiment / media-type / category pies.
function svgDonutR(segs, size = 130) {
  const tot = segs.reduce((a, s) => a + (s.value || 0), 0);
  if (!tot) return '';
  const R = 44, C = 2 * Math.PI * R;
  let acc = 0;
  const rings = segs.map((s) => {
    const len = (C * s.value) / tot;
    const el = `<circle cx="60" cy="60" r="${R}" fill="none" stroke="${s.color}" stroke-width="19"
      stroke-dasharray="${len.toFixed(1)} ${C.toFixed(1)}" transform="rotate(${((360 * acc) / tot - 90).toFixed(1)} 60 60)"/>`;
    acc += s.value;
    return el;
  }).join('');
  return `<table role="presentation" cellpadding="0" cellspacing="0"><tr>
    <td><svg viewBox="0 0 120 120" width="${size}" height="${size}">${rings}</svg></td>
    <td style="padding-left:14px;vertical-align:middle">
      ${segs.map((s) => `<div style="font-size:12.5px;margin:3px 0"><span style="display:inline-block;width:10px;height:10px;border-radius:3px;background:${s.color};margin-right:6px"></span>${s.label} — <b>${Math.round((100 * s.value) / tot)}%</b> (${fmtInt(s.value)})</div>`).join('')}
    </td></tr></table>`;
}

// Stacked horizontal bars — sentiment by region (green / grey / red).
function svgStackR(rows) {
  const max = Math.max(1, ...rows.map((r) => r.segs.reduce((a, s) => a + s.v, 0)));
  return rows.map((r) => {
    const tot = r.segs.reduce((a, s) => a + s.v, 0) || 1;
    return `<table role="presentation" width="100%" cellpadding="0" cellspacing="0" style="margin:4px 0"><tr>
      <td style="width:120px;font-size:12.5px;color:#52514e;padding-right:8px;white-space:nowrap;overflow:hidden">${r.label}</td>
      <td><div style="display:flex;height:14px;border-radius:4px;overflow:hidden;width:${Math.max(3, (100 * tot) / max)}%">
        ${r.segs.map((s) => s.v ? `<div style="width:${(100 * s.v) / tot}%;background:${s.color}" title="${s.name}: ${fmtInt(s.v)}"></div>` : '').join('')}
      </div></td>
      <td style="width:64px;text-align:right;font-size:12px;color:#52514e">${fmtInt(tot)}</td>
    </tr></table>`;
  }).join('');
}
const SENT_R = { positive: '#12a06e', 'very positive': '#0d8258', neutral: '#9b9993', negative: '#c0392b', 'very negative': '#8f2519', unclassified: '#c4c2ba' };
const sentColR = (n) => SENT_R[String(n).toLowerCase()] || '#6e62e5';

// Volume & Reach over time — bars (articles) + line (reach), dual axis, the
// signature chart from the FEA monthly report.
function svgDual(rows, barKey = 'articles', lineKey = 'reach', width = 640, height = 170) {
  if (!rows?.length) return '';
  const bv = rows.map((r) => Number(r[barKey]) || 0);
  const lv = rows.map((r) => Number(r[lineKey]) || 0);
  const bMax = Math.max(...bv, 1), lMax = Math.max(...lv, 1);
  const padT = 12, padB = 18, H = height;
  const bw = Math.max(2, Math.floor(width / rows.length) - 2);
  const x = (i) => i * (bw + 2) + bw / 2;
  const by = (v) => H - padB - ((H - padT - padB) * v) / bMax;
  const ly = (v) => H - padB - ((H - padT - padB) * v) / lMax;
  const bars = bv.map((v, i) => `<rect x="${(x(i) - bw / 2).toFixed(1)}" y="${by(v).toFixed(1)}" width="${bw}" height="${(H - padB - by(v)).toFixed(1)}" rx="1.5" fill="#eb6834" opacity="0.8"><title>${rows[i].date}: ${fmtInt(v)} articles · ${kfmtR(lv[i])} reach</title></rect>`).join('');
  const line = lv.map((v, i) => `${i === 0 ? 'M' : 'L'}${x(i).toFixed(1)},${ly(v).toFixed(1)}`).join(' ');
  return `<svg width="${width}" height="${H}" viewBox="0 0 ${width} ${H}" style="max-width:100%">
    ${bars}
    <path d="${line}" fill="none" stroke="#6e62e5" stroke-width="2.2" stroke-linejoin="round"/>
  </svg>
  <div style="display:flex;justify-content:space-between;color:#898781;font-size:11px">
    <span>${rows[0].date}</span>
    <span><span style="color:#eb6834">■</span> articles &nbsp;<span style="color:#6e62e5">—</span> reach (peak ${kfmtR(lMax)})</span>
    <span>${rows[rows.length - 1].date}</span>
  </div>`;
}
function monitoringReportHtml(m, narr = {}, shots = {}) {
  // Per-section analyst bullets (from the mynews-sections AI call), rendered
  // under the matching chart exactly like the source reports.
  const N = (k) => (narr?.[k]?.length
    ? `<div style="margin-top:9px;border-top:1px solid #e1e0d9;padding-top:7px">${narr[k].map((b) => `<div style="font-size:12.5px;line-height:1.5;margin:4px 0;padding-left:13px;position:relative"><span style="position:absolute;left:0;color:#eb6834;font-weight:700">•</span>${String(b).replace(/&/g, '&amp;').replace(/</g, '&lt;')}</div>`).join('')}</div>`
    : '');
  let html = '';
  const t = m.totals || {};
  const sent = m.sentiment || [];
  const totS = sent.reduce((a, x) => a + (x.articles || 0), 0);
  const posShare = totS ? Math.round((100 * sent.filter((x) => String(x.name).toLowerCase().includes('positive')).reduce((a, x) => a + x.articles, 0)) / totS) : null;
  // KEY METRICS
  html += card('Media monitoring — myNewspress key metrics', `
    <table role="presentation" width="100%" cellpadding="0" cellspacing="0"><tr>
    ${[['Articles', fmtInt(t.articles), deltaHtml(t.articles, t.prevArticles)],
       ['Total reach', kfmtR(t.reach), deltaHtml(t.reach, t.prevReach)],
       t.ave ? ['AVE value', '£' + kfmtR(t.ave), deltaHtml(t.ave, t.prevAve)] : null,
       posShare != null ? ['Sentiment', posShare + '% positive', ''] : null,
       t.shares ? ['Social shares', fmtInt(t.shares), ''] : null,
       t.publications ? ['Publications', fmtInt(t.publications), ''] : null]
      .filter(Boolean).slice(0, 6)
      .map(([l, v, d2]) => `<td style="background:#fcfcfb;border:1px solid rgba(11,11,11,0.1);border-radius:12px;padding:11px;vertical-align:top">
        <div style="font-size:11px;color:#898781">${l}</div>
        <div style="font-size:19px;font-weight:700;margin:2px 0">${v}</div>
        <div style="font-size:11.5px">${d2 || ''}</div>
      </td><td style="width:7px"></td>`).join('')}</tr></table>${N('volumeReach')}`);
  // Volume & Reach — the FEA-style MoM table
  html += card('Volume & Reach — vs previous period', `
    <table width="100%" cellpadding="6" cellspacing="0" style="font-size:13px;border-collapse:collapse">
    <tr style="color:#898781;font-size:11px;text-transform:uppercase;text-align:left"><th>KPIs</th><th style="text-align:right">Previous</th><th style="text-align:right">This period</th><th style="text-align:right">MoM</th></tr>
    ${[['Volume', t.prevArticles, t.articles, (v) => fmtInt(v)],
       ['Reach', t.prevReach, t.reach, (v) => kfmtR(v)],
       t.ave != null ? ['AVE', t.prevAve, t.ave, (v) => '£' + kfmtR(v)] : null]
      .filter(Boolean).map(([label, prev2, cur2, f]) => {
        const dd = prev2 ? Math.round(((cur2 - prev2) / prev2) * 100) : null;
        return `<tr style="border-top:1px solid #e1e0d9"><td style="font-weight:600">${label}</td>
          <td style="text-align:right;color:#52514e">${f(prev2 || 0)}</td>
          <td style="text-align:right;font-weight:700">${f(cur2 || 0)}</td>
          <td style="text-align:right;font-weight:700;color:${dd == null ? '#898781' : dd >= 0 ? '#006300' : '#a02c2c'}">${dd == null ? '—' : (dd > 0 ? '+' : '') + dd + '%'}</td></tr>`;
      }).join('')}</table>${N('volumeReach')}`);
  // Campaign one-pager strip: Top 5 journalists · Top 5 publications · Top 5 articles
  if (m.topJournalists?.length || m.topPublications?.length || m.topArticles?.length) {
    const namesBox = (title2, items) => `
      <div style="font-weight:700;font-size:12px;margin-bottom:5px;text-align:center;background:#0b0b0b;color:#fff;border-radius:8px;padding:5px">${title2}</div>
      ${items.map((x, i2) => `<div style="font-size:12.5px;margin:4px 0">${i2 + 1}. ${x}</div>`).join('')}`;
    html += card('At a glance — the campaign view', `
      <table role="presentation" width="100%" cellpadding="0" cellspacing="0"><tr>
        <td style="width:24%;vertical-align:top">${namesBox('Top 5 journalists', (m.topJournalists || []).slice(0, 5).map((j) => j.name))}</td>
        <td style="width:2%"></td>
        <td style="width:24%;vertical-align:top">${namesBox('Top 5 publications', (m.topPublications || []).slice(0, 5).map((p2) => p2.name))}</td>
        <td style="width:2%"></td>
        <td style="width:48%;vertical-align:top">${namesBox('Top 5 articles', (m.topArticles || []).slice(0, 5).map((a2) => `${a2.url ? `<a href="${a2.url}" style="color:#0b0b0b">${String(a2.title).slice(0, 70)}</a>` : String(a2.title).slice(0, 70)} <span style="color:#898781">(${a2.publication || '—'}${a2.reach ? ' — ' + kfmtR(a2.reach) + ' Reach' : ''})</span>`))}</td>
      </tr></table>`);
  }
  // Intelligence
  const ins = monitoringInsights(m);
  if (ins.length) {
    const COLOR = { up: '#006300', down: '#a02c2c', watch: '#8a5a00', note: '#898781' };
    const LABEL = { up: '▲ WIN', down: '▼ DOWN', watch: '⚠ WATCH', note: '• NOTE' };
    html += card("myNewspress intelligence — what's working & what's not",
      ins.map((i) => `<div style="margin:7px 0;font-size:13px">
        <span style="color:${COLOR[i.kind] || COLOR.note};font-weight:700;font-size:11px">${LABEL[i.kind] || LABEL.note}</span>
        &nbsp;${i.text}${i.action ? `<div style="color:#52514e;font-size:12.5px;margin:2px 0 0 8px">→ ${i.action}</div>` : ''}
      </div>`).join(''));
  }
  if (m.daily?.length > 1) {
    const days = m.daily.map((x) => ({ date: String(x.date).slice(0, 10), articles: Number(x.articles) || 0, reach: Number(x.reach) || 0 }));
    html += days.some((x) => x.reach)
      ? card('Volume & reach over time', svgDual(days) + N('overtime'))
      : card('Coverage volume over time — articles per day', svgBars(days, 'articles') + N('overtime'));
  }
  if (sent.length) html += card('Overall sentiment (volume)',
    svgDonutR(sent.map((x) => ({ label: x.name, value: x.articles || 0, color: sentColR(x.name) }))) + N('sentiment'));
  if (m.sentimentByRegion?.length) {
    const byReg = {};
    m.sentimentByRegion.forEach((x) => {
      byReg[x.region] = byReg[x.region] || {};
      byReg[x.region][String(x.sentiment).toLowerCase()] = (byReg[x.region][String(x.sentiment).toLowerCase()] || 0) + (x.articles || 0);
    });
    const stackRows = Object.entries(byReg).map(([reg, s2]) => ({
      label: reg,
      segs: ['very positive', 'positive', 'neutral', 'unclassified', 'negative', 'very negative'].filter((k) => s2[k]).map((k) => ({ name: k, v: s2[k], color: sentColR(k) })),
    }));
    html += card('Sentiment by region (by volume)', svgStackR(stackRows) + `
      <div style="color:#898781;font-size:11px;margin:6px 0"><span style="color:#12a06e">■</span> positive · <span style="color:#9b9993">■</span> neutral · <span style="color:#c0392b">■</span> negative</div>
      <table width="100%" cellpadding="5" cellspacing="0" style="font-size:12.5px;border-collapse:collapse">
      <tr style="color:#898781;font-size:11px;text-transform:uppercase;text-align:left"><th>Region</th><th style="text-align:right;color:#006300">Positive</th><th style="text-align:right">Neutral</th><th style="text-align:right;color:#a02c2c">Negative</th></tr>
      ${Object.entries(byReg).map(([reg, s]) => `<tr style="border-top:1px solid #e1e0d9">
        <td>${reg}</td><td style="text-align:right">${fmtInt(s.positive)}</td>
        <td style="text-align:right">${fmtInt(s.neutral)}</td><td style="text-align:right">${fmtInt(s.negative)}</td></tr>`).join('')}</table>${N('sentimentByRegion')}`);
  }
  if (m.regions?.length) html += card('Region split — coverage volume', barsHtml(m.regions.map((x) => ({ dimensionValue: x.name, articles: x.articles })), 'articles') + N('regions'));
  if (m.marketSplit?.length > 1) {
    html += card('Market split — volume & share of reach',
      barsHtml(m.marketSplit.map((x) => ({ dimensionValue: `${x.name}${x.region ? ` (${x.region})` : ''} · ${x.reachShare || 0}% of reach`, articles: x.articles })), 'articles')
      + N('marketSplit'));
  }
  if (m.topics?.length) {
    const CAT_COL = ['#eb6834', '#f0956a', '#6e62e5', '#12a06e', '#9b9993', '#c0392b', '#8a5a00'];
    html += card('Coverage category', svgDonutR(m.topics.slice(0, 7).map((x, i2) => ({ label: x.name, value: x.articles || 0, color: CAT_COL[i2 % 7] }))) + N('topics'));
  }
  if (m.mediaTypes?.length) {
    const MT_COL = ['#eb6834', '#6e62e5', '#12a06e', '#9b9993'];
    html += card('Media type', svgDonutR(m.mediaTypes.map((x, i2) => ({ label: x.name, value: x.articles || 0, color: MT_COL[i2 % 4] }))) + N('mediaTypes'));
  }
  if (m.models?.length) html += card('Current model — coverage volume', barsHtml(m.models.map((x) => ({ dimensionValue: x.name, articles: x.articles })), 'articles') + N('models'));
  const rc = (m.releaseCoverage || []).filter(Boolean);
  if (rc.length) {
    html += card('Press release engagement vs coverage reach', `
      <table width="100%" cellpadding="5" cellspacing="0" style="font-size:12.5px;border-collapse:collapse">
      <tr style="color:#898781;font-size:11px;text-transform:uppercase;text-align:left"><th>Release</th><th style="text-align:right">Platform engagement</th><th style="text-align:right">Articles</th><th style="text-align:right">Coverage reach</th></tr>
      ${rc.map((x) => `<tr style="border-top:1px solid #e1e0d9">
        <td>${String(x.title).slice(0, 65)}</td>
        <td style="text-align:right">${fmtInt(x.engagements)}</td>
        <td style="text-align:right">${x.articles ? fmtInt(x.articles) : '<span style="color:#a02c2c">none</span>'}</td>
        <td style="text-align:right">${x.reach ? kfmtR(x.reach) : '—'}</td></tr>`).join('')}</table>
      <div style="color:#898781;font-size:11px;margin-top:6px">How each release converted platform interest into earned media.</div>${N('releases')}`);
  }
  if (m.topPublications?.length) {
    const online = m.topPublications.filter((p) => String(p.mediaType || '').toLowerCase() !== 'print');
    const print = m.topPublications.filter((p) => String(p.mediaType || '').toLowerCase() === 'print');
    const pubTable = (list, label) => `
      <div style="font-weight:700;font-size:12px;margin-bottom:4px;text-align:center">${label}</div>
      <table width="100%" cellpadding="4" cellspacing="0" style="font-size:12px;border-collapse:collapse">
      <tr style="color:#898781;font-size:10.5px;text-transform:uppercase;text-align:left"><th>Publication</th><th style="text-align:right">Articles</th><th style="text-align:right">Reach</th></tr>
      ${list.map((p) => `<tr style="border-top:1px solid #e1e0d9">
        <td>${p.name}${p.tier ? ' <span style="color:#898781;font-size:10px">· ' + p.tier + '</span>' : ''}</td>
        <td style="text-align:right">${fmtInt(p.articles)}</td><td style="text-align:right">${p.reach ? kfmtR(p.reach) : '—'}</td></tr>`).join('')}</table>`;
    html += card('Top publications — by reach', print.length && online.length
      ? `<table role="presentation" width="100%" cellpadding="0" cellspacing="0"><tr>
          <td style="width:49%;vertical-align:top">${pubTable(online.slice(0, 10), 'Online')}</td>
          <td style="width:2%"></td>
          <td style="width:49%;vertical-align:top">${pubTable(print.slice(0, 10), 'Print')}</td>
        </tr></table>${N('publications')}`
      : pubTable([...online.slice(0, 10), ...print.slice(0, 8)], online.length ? 'Online' : 'Print') + (print.length && online.length ? '' : N('publications')));
  }
  if (m.topJournalists?.length) html += card('Top journalists — writing the coverage', barsHtml(m.topJournalists.map((x) => ({ dimensionValue: x.name, articles: x.articles })), 'articles') + N('journalists'));
  if (m.shareOfVoice?.length) {
    const totV = m.shareOfVoice.reduce((a, x) => a + (x.reach || x.articles || 0), 0) || 1;
    html += card('Share of voice — by reach', barsHtml(m.shareOfVoice.map((x) => ({ dimensionValue: `${x.name} (${Math.round((100 * (x.reach || x.articles || 0)) / totV)}%)`, v: x.reach || x.articles || 0 })), 'v') + N('shareOfVoice'));
  }
  if (m.topArticles?.length) {
    const cards6 = m.topArticles.slice(0, 6);
    // Images come from the tenant's OWN media library (article.image, resolved
    // via monitoring.mediaBaseUrl) — never from screenshotting live sites,
    // which produced cookie-consent walls. No image → clean text card.
    const anyImg = cards6.some((a2) => a2.image);
    html += card('Monthly highlights', `
      <table role="presentation" width="100%" cellpadding="0" cellspacing="0"><tr>
      ${cards6.map((a2, i2) => `
        ${i2 % 3 === 0 && i2 > 0 ? '</tr><tr>' : ''}
        <td style="width:33%;vertical-align:top;padding:5px">
          <div class="hl-card" style="border:1px solid #d9d7cf;border-radius:10px;overflow:hidden">
            ${a2.image ? `<img src="${a2.image}" style="width:100%;height:150px;object-fit:cover;display:block;border-bottom:1px solid #e1e0d9" alt=""/>` : ''}
            <div style="padding:11px;min-height:${anyImg ? 74 : 64}px">
              <div style="font-size:12.5px;font-weight:700;line-height:1.35">${a2.url ? `<a href="${a2.url}" style="color:#0b0b0b">${String(a2.title).slice(0, 90)}</a>` : String(a2.title).slice(0, 90)}</div>
              ${a2.sentiment ? `<div style="font-size:11px;margin-top:5px;color:${sentColR(a2.sentiment)};font-weight:700">${a2.sentiment}</div>` : ''}
            </div>
            <div style="background:#eb6834;color:#fff;font-size:11px;font-weight:700;padding:7px 10px">
              ${[a2.market, String(a2.date || '').slice(0, 10), a2.publication, a2.reach ? 'Reach: ' + kfmtR(a2.reach) : null].filter(Boolean).join(' | ')}
            </div>
          </div>
        </td>`).join('')}
      </tr></table>${N('articles')}`);
    if (m.topArticles.length > 6) {
      html += card('All highlight articles — by reach', `
        <table width="100%" cellpadding="5" cellspacing="0" style="font-size:12.5px;border-collapse:collapse">
        <tr style="color:#898781;font-size:11px;text-transform:uppercase;text-align:left"><th>Article</th><th>Publication</th><th>Date</th><th>Sentiment</th><th style="text-align:right">Reach</th></tr>
        ${m.topArticles.slice(0, 15).map((a2) => `<tr style="border-top:1px solid #e1e0d9">
          <td>${a2.url ? `<a href="${a2.url}" style="color:#0b0b0b">${String(a2.title).slice(0, 70)}</a>` : String(a2.title).slice(0, 70)}</td>
          <td style="color:#898781">${a2.publication || '—'}</td>
          <td style="white-space:nowrap">${String(a2.date || '').slice(0, 10)}</td>
          <td style="color:${sentColR(a2.sentiment || '')}">${a2.sentiment || '—'}</td>
          <td style="text-align:right">${a2.reach ? kfmtR(a2.reach) : '—'}</td></tr>`).join('')}</table>`);
    }
  }
  return html;
}

// Chart pack auto-embedded into AI-authored reports — real data for the exact
// scope requested: one site, one Newspress client, or the whole portfolio.
// Chart pack for ONE RELEASE — nothing site-wide, exactly what the release
// dossier promises: its own interest curve, readers, assets, email, countries.
async function releaseChartPack(siteId, releaseId, rangeStr) {
  let html = '';
  try {
    const range = resolveRange(rangeStr || '90d');
    const r = await legacyRelease(siteId, releaseId, rangeStr || '90d');
    if (!r) return '';
    let ga = null, gaTrend = null;
    try {
      ga = (await searchPages(siteId, String(releaseId), range.startDate, range.endDate))[0] || null;
      if (ga) gaTrend = await pageDetail(siteId, ga.dimensionValue, range.startDate, range.endDate);
    } catch { /* GA side optional */ }
    html += card('', `<table role="presentation" width="100%" cellpadding="0" cellspacing="0"><tr>
      ${[['Platform views', fmtInt(r.viewed)], ['Downloads', r.downloaded != null ? fmtInt(r.downloaded) : '—'],
         ['Basket adds', r.basket != null ? fmtInt(r.basket) : '—'], ['GA4 page views', ga ? fmtInt(ga.screenPageViews) : '—']]
        .map(([l, v]) => `<td style="background:#fcfcfb;border:1px solid rgba(11,11,11,0.1);border-radius:12px;padding:12px;width:25%">
          <div style="font-size:11.5px;color:#898781">${l}</div>
          <div style="font-size:22px;font-weight:700;margin:2px 0">${v}</div>
        </td><td style="width:8px"></td>`).join('')}</tr></table>`);
    if (r.dailyEvents?.length > 1) html += card('Interest over time — platform events per day', svgBars(r.dailyEvents, 'events'));
    if (gaTrend?.trend?.length > 1) html += card(`Web traffic to this release's page (${ga.dimensionValue})`, svgLine(gaTrend.trend, 'screenPageViews'));
    if (r.readers?.length) html += card(`Who read it — registered readers (${r.readers.length})`, `
      <table width="100%" cellpadding="4" cellspacing="0" style="font-size:12px;border-collapse:collapse">
      <tr style="color:#898781;font-size:10.5px;text-transform:uppercase;text-align:left"><th>Reader</th><th style="text-align:right">Views</th><th style="text-align:right">Downloads</th><th>Last seen</th></tr>
      ${r.readers.map((rd) => `<tr style="border-top:1px solid #e1e0d9">
        <td style="font-weight:600">${rd.name || rd.email || '—'}</td>
        <td style="text-align:right">${fmtInt(rd.views)}</td><td style="text-align:right">${fmtInt(rd.downloads)}</td>
        <td style="color:#898781">${String(rd.lastSeen || '—').slice(0, 10)}</td></tr>`).join('')}</table>`);
    if (r.assets?.length) html += card('Assets in this release — downloads (renditions merged)',
      barsHtml(r.assets.map((m) => ({ dimensionValue: `${m.type === 'document' ? '📄 ' : ''}${m.name}${m.variants > 1 ? ` (${m.variants} files)` : ''}`.slice(0, 60), downloaded: m.downloaded })), 'downloaded'));
    const dedicated = (r.emails || []).filter((e) => e.dedicated !== false);
    const featured = (r.emails || []).filter((e) => e.dedicated === false);
    if (dedicated.length) html += card('Email distribution of this release', `
      <table width="100%" cellpadding="5" cellspacing="0" style="font-size:12.5px;border-collapse:collapse">
      <tr style="color:#898781;font-size:11px;text-transform:uppercase;text-align:left"><th>Campaign</th><th>Sent</th><th style="text-align:right">Recipients</th><th style="text-align:right">Opens</th><th style="text-align:right">Clicks</th></tr>
      ${dedicated.map((e) => `<tr style="border-top:1px solid #e1e0d9">
        <td>${e.title || e.campaignId}</td><td>${String(e.sentDate || '—').slice(0, 10)}</td>
        <td style="text-align:right">${fmtInt(e.recipients)}</td><td style="text-align:right">${fmtInt(e.opened)}</td>
        <td style="text-align:right">${fmtInt(e.clicked)}</td></tr>`).join('')}</table>`);
    if (featured.length) html += card('Also featured in multi-release sends', `
      <div style="font-size:12px;color:#898781;margin-bottom:6px">These campaigns included a link to this release among other stories — their totals cover the WHOLE send, not this release alone.</div>
      ${featured.map((e) => `<div style="font-size:12.5px;margin:3px 0">• ${e.title || e.campaignId} — ${String(e.sentDate || '—').slice(0, 10)}, ${fmtInt(e.recipients)} recipients</div>`).join('')}`);
    if (r.countries?.length) html += card('Reader countries — platform events',
      barsHtml(r.countries.map((c) => ({ dimensionValue: c.country, events: c.events })), 'events'));
  } catch { /* additive */ }
  return html;
}

async function chartPack({ siteId, range: rangeStr, companyId, releaseId, allowed }) {
  if (!siteId) return '';
  if (releaseId) return releaseChartPack(siteId, releaseId, rangeStr);
  const range = resolveRange(rangeStr || '28d');
  let html = '';
  try {
    if (siteId === 'all') {
      const [trend, cur, prv] = await Promise.all([
        portfolioTimeseries(range.startDate, range.endDate, allowed || null),
        portfolioTotals(range.startDate, range.endDate, allowed || null),
        portfolioTotals(previousRange(range).startDate, previousRange(range).endDate, allowed || null),
      ]);
      html += tilesHtml(cur.totals, prv.totals);
      html += card('Combined daily sessions — all sites', svgLine(trend, 'sessions'));
      html += card('Sessions by site', barsHtml(cur.perSite.sort((a, b) => b.sessions - a.sessions).slice(0, 12).map((s) => ({ dimensionValue: s.name, sessions: s.sessions })), 'sessions'));
    } else {
      const scopeTag = companyId ? ' — this client' : '';
      const [legacy, ranking] = await Promise.all([
        legacyOverview(siteId, rangeStr || '28d', companyId || null).catch(() => null),
        journalistRanking(siteId, rangeStr || '28d', { limit: 10, companyId: companyId || null }).catch(() => []),
      ]);
      if (companyId) {
        // Client-scoped report: GA4 can't split by client, so only one small
        // site-wide context chart — everything else is filtered to the client.
        const trend = await timeseries(siteId, range.startDate, range.endDate).catch(() => []);
        if (trend.length) html += card('Site-wide daily sessions (context — GA4 cannot be split per client)', svgLine(trend, 'sessions'));
      } else {
        const [cur, prv, trend, channels, pages] = await Promise.all([
          totals(siteId, range.startDate, range.endDate),
          totals(siteId, previousRange(range).startDate, previousRange(range).endDate),
          timeseries(siteId, range.startDate, range.endDate),
          breakdown(siteId, 'sessionDefaultChannelGroup', range.startDate, range.endDate, 8),
          breakdown(siteId, 'pagePath', range.startDate, range.endDate, 8),
        ]);
        html += tilesHtml(cur, prv);
        // Highlights — daily sessions annotated with what caused each peak
        let peaks0 = [];
        try { peaks0 = await trafficPeaks(siteId, rangeStr || '28d'); } catch { /* additive */ }
        html += card(peaks0.length ? 'Highlights — daily sessions, peaks annotated' : 'Daily sessions',
          svgLine(trend, 'sessions', 640, 150, peaks0.map((p) => ({ date: p.date, label: p.label }))));
        // What drove the peaks — spike days attributed to a release/page/email
        try {
          const peaks = peaks0;
          if (peaks.length) {
            const ICON = { release: '📰', email: '📧', page: '📈' };
            html += card('What drove the traffic peaks', `
              <table width="100%" cellpadding="5" cellspacing="0" style="font-size:12.5px;border-collapse:collapse">
              <tr style="color:#898781;font-size:11px;text-transform:uppercase;text-align:left"><th>Day</th><th>Driven by</th><th style="text-align:right">Sessions</th></tr>
              ${peaks.map((p) => `<tr style="border-top:1px solid #e1e0d9">
                <td style="white-space:nowrap">${p.date}</td>
                <td>${ICON[p.kind] || '📈'} ${String(p.label).slice(0, 80)}${p.email && p.kind !== 'email' ? ' <span style="color:#898781">+ email send</span>' : ''}</td>
                <td style="text-align:right;font-weight:600">${fmtInt(p.sessions)}</td></tr>`).join('')}</table>
              <div style="color:#898781;font-size:11px;margin-top:6px">Spike days meaningfully above the period's typical day, attributed to that day's top release, page or email send.</div>`);
          }
        } catch { /* additive */ }
        // New vs returning users
        if (cur.totalUsers) {
          const nu = cur.newUsers || 0, ret = Math.max(0, cur.totalUsers - nu);
          html += card('New vs returning users',
            barsHtml([
              { dimensionValue: `New users (${((100 * nu) / cur.totalUsers).toFixed(1)}%)`, users: nu },
              { dimensionValue: `Returning (${((100 * ret) / cur.totalUsers).toFixed(1)}%)`, users: ret },
            ], 'users'));
        }
        // Top day of week by active users
        if (trend.length >= 7) {
          const DAYS = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'];
          const agg = DAYS.map(() => ({ u: 0, n: 0 }));
          trend.forEach((r) => {
            const dow = (new Date(r.date + 'T00:00:00Z').getUTCDay() + 6) % 7;
            agg[dow].u += r.totalUsers || 0; agg[dow].n++;
          });
          const dows = DAYS.map((day, i) => ({ dimensionValue: day, users: agg[i].n ? Math.round(agg[i].u / agg[i].n) : 0 }))
            .sort((a, b) => b.users - a.users);
          html += card('Top days of the week — average active users', barsHtml(dows, 'users'));
        }
        html += card('Traffic channels', barsHtml(channels, 'sessions'));
        html += card('Top pages', barsHtml(pages, 'screenPageViews'));
        // On-site searches
        try {
          const terms = await searchTerms(siteId, range.startDate, range.endDate, 15);
          if (terms.length) html += card('Top on-site searches',
            barsHtml(terms.map((t2) => ({ dimensionValue: t2.term.slice(0, 50), searches: t2.searches })), 'searches'));
        } catch { /* additive */ }
      }
      // Every platform graph available for this scope:
      if (legacy?.cadence?.days?.length) {
        html += card(`Releases published per day${scopeTag}`, svgBars(legacy.cadence.days, 'published'));
      }
      if (legacy?.releases?.length) {
        html += card(`Top releases — platform views${scopeTag}`,
          barsHtml(legacy.releases.slice(0, 10).map((r) => ({ dimensionValue: (r.title || '#' + r.id).slice(0, 60), viewed: r.viewed })), 'viewed'));
      }
      // Quietest releases (bottom performers, live ≥30 days) — site-wide only
      if (!companyId) {
        try {
          const quiet = await quietReleases(siteId, rangeStr || '28d', 10);
          if (quiet.length) html += card('Quietest releases — bottom performers (live ≥ 30 days)', `
            <table width="100%" cellpadding="5" cellspacing="0" style="font-size:12.5px;border-collapse:collapse">
            <tr style="color:#898781;font-size:11px;text-transform:uppercase;text-align:left"><th>Release</th><th>Published</th><th style="text-align:right">Views</th></tr>
            ${quiet.map((r) => `<tr style="border-top:1px solid #e1e0d9">
              <td>${String(r.title || '#' + r.id).slice(0, 70)}${r.companyName ? ` <span style="color:#898781">· ${r.companyName}</span>` : ''}</td>
              <td style="white-space:nowrap">${String(r.published || '—').slice(0, 10)}</td>
              <td style="text-align:right">${fmtInt(r.viewed)}</td></tr>`).join('')}</table>
            <div style="color:#898781;font-size:11px;margin-top:6px">Candidates for re-promotion, a fresh angle or better distribution — only releases live for at least a month qualify.</div>`);
        } catch { /* additive */ }
      }
      if (ranking?.length) {
        html += card(`Top journalists — release views & downloads${scopeTag}`,
          barsHtml(ranking.map((j) => ({ dimensionValue: `${j.name || j.email}${j.publications ? ' (' + String(j.publications).split(',')[0].trim() + ')' : ''}`.slice(0, 55), events: Number(j.events) || 0 })), 'events'));
      } else if (legacy?.topJournalists?.length) {
        html += card('Top journalists — most active',
          barsHtml(legacy.topJournalists.map((j) => ({ dimensionValue: (j.name || j.email || '').slice(0, 45), events: j.events })), 'events'));
      }
      if (!companyId && legacy?.topPublications?.length) {
        html += card('Top publications — engagement events',
          barsHtml(legacy.topPublications.map((p) => ({ dimensionValue: p.name.slice(0, 45), events: p.events })), 'events'));
      }
      if (legacy?.media?.length) {
        html += card(`Top media assets — downloads${scopeTag}`,
          barsHtml(legacy.media.slice(0, 10).map((m) => ({ dimensionValue: m.name.slice(0, 50), downloaded: m.downloaded })), 'downloaded'));
        const images = legacy.media.filter((m) => m.type === 'image').slice(0, 10);
        const videos = legacy.media.filter((m) => m.type === 'video').slice(0, 10);
        if (images.length) html += card(`Top image downloads${scopeTag}`,
          barsHtml(images.map((m) => ({ dimensionValue: '🖼 ' + m.name.slice(0, 48), downloaded: m.downloaded })), 'downloaded'));
        if (videos.length) html += card(`Top video downloads${scopeTag}`,
          barsHtml(videos.map((m) => ({ dimensionValue: '🎬 ' + m.name.slice(0, 48), downloaded: m.downloaded })), 'downloaded'));
      }
      if (legacy?.documents?.length) {
        html += card(`Documents — spec sheets & press kits, downloads${scopeTag}`,
          barsHtml(legacy.documents.map((m) => ({ dimensionValue: m.name.slice(0, 50), downloaded: m.downloaded })), 'downloaded'));
      }
      if (legacy?.models?.length) {
        html += card('Model pages — views',
          barsHtml(legacy.models.slice(0, 10).map((m) => ({ dimensionValue: m.name.slice(0, 45), viewed: m.viewed })), 'viewed'));
      }
      if (legacy?.banners?.length) {
        html += card(`Banners — clicks${scopeTag}`,
          barsHtml(legacy.banners.map((b) => ({ dimensionValue: b.title.slice(0, 50), clicked: b.clicked })), 'clicked'));
      }
      if (legacy?.categories?.length) {
        html += card('Categories — releases this period',
          barsHtml(legacy.categories.map((c2) => ({ dimensionValue: c2.name.slice(0, 45), releases: c2.releases })), 'releases'));
      }
      if (legacy?.emails?.length) {
        html += card(`Email campaigns — recipients${scopeTag}`,
          barsHtml(legacy.emails.slice(0, 10).map((e) => ({ dimensionValue: (e.title || e.campaignId).slice(0, 55), recipients: e.recipients })), 'recipients'));
        html += card(`Email campaigns — unique opens${scopeTag}`,
          barsHtml(legacy.emails.slice(0, 10).map((e) => ({ dimensionValue: (e.title || e.campaignId).slice(0, 55), opened: e.opened })), 'opened'));
      }
      if (legacy?.audience) {
        html += card('Registered audience', `<div style="font-size:13.5px">
          🗞 <b>${fmtInt(legacy.audience.journalists)}</b> journalists &nbsp;·&nbsp;
          👥 <b>${fmtInt(legacy.audience.consumers)}</b> consumers &nbsp;·&nbsp;
          🏢 <b>${fmtInt(legacy.audience.mediaClients)}</b> media clients</div>`);
      }
      // myNewspress media-monitoring block (site scope only — coverage isn't
      // split per Newspress client)
      if (!companyId) {
        html += await reconUploadsHtml(siteId, rangeStr || '28d');
        try {
          const mon = await monitoringOverview(siteId, rangeStr || '28d');
          if (mon?.enabled && mon.totals && !mon.error) html += monitoringReportHtml(mon);
        } catch { /* additive */ }
      }
      // The COMPLETE list of journalists who interacted with this scope's
      // content — platform release views/downloads MERGED with email opens
      // and clicks on the scope's campaigns, one row per person.
      try {
        const [fullRank, engagers] = await Promise.all([
          journalistRanking(siteId, rangeStr || '28d', { limit: 200, companyId: companyId || null }),
          emailEngagers(siteId, rangeStr || '28d', { limit: 300, companyId: companyId || null }),
        ]);
        const people = new Map();
        for (const j of fullRank || []) {
          people.set((j.email || j.name || '').toLowerCase(), {
            name: j.name || j.email, publications: j.publications || '',
            views: Number(j.views) || 0, downloads: Number(j.downloads) || 0, opens: 0, clicks: 0,
          });
        }
        for (const e of engagers || []) {
          const key = (e.email || e.name || '').toLowerCase();
          if (!key) continue;
          const p = people.get(key) || { name: e.name || e.email, publications: e.publications || '', views: 0, downloads: 0, opens: 0, clicks: 0 };
          p.opens += Number(e.opens) || 0;
          p.clicks += Number(e.clicks) || 0;
          if (!p.publications && e.publications) p.publications = e.publications;
          people.set(key, p);
        }
        const rows = [...people.values()]
          .map((p) => ({ ...p, total: p.views + p.downloads + p.opens + p.clicks }))
          .filter((p) => p.total > 0)
          .sort((a, b) => b.total - a.total)
          .slice(0, 150);
        if (rows.length) {
          html += card(`All journalists who interacted${scopeTag} (${rows.length})`, `
            <table width="100%" cellpadding="4" cellspacing="0" style="font-size:12px;border-collapse:collapse">
            <tr style="color:#898781;font-size:10.5px;text-transform:uppercase;text-align:left">
              <th>Journalist</th><th>Publication(s)</th>
              <th style="text-align:right">Views</th><th style="text-align:right">Downloads</th>
              <th style="text-align:right">Email opens</th><th style="text-align:right">Clicks</th><th style="text-align:right">Total</th></tr>
            ${rows.map((p) => `<tr style="border-top:1px solid #e1e0d9">
              <td style="font-weight:600">${p.name || '—'}</td>
              <td style="color:#898781">${String(p.publications || '—').slice(0, 60)}</td>
              <td style="text-align:right">${fmtInt(p.views)}</td>
              <td style="text-align:right">${fmtInt(p.downloads)}</td>
              <td style="text-align:right">${fmtInt(p.opens)}</td>
              <td style="text-align:right">${fmtInt(p.clicks)}</td>
              <td style="text-align:right;font-weight:600">${fmtInt(p.total)}</td>
            </tr>`).join('')}</table>
            <div style="color:#898781;font-size:11px;margin-top:6px">Platform release views &amp; downloads merged with email opens/clicks on this ${companyId ? "client's" : "site's"} campaigns, one row per person.</div>`);
        }
      } catch { /* table is additive */ }
    }
  } catch { /* charts are additive — the report still ships without them */ }
  return html;
}

// Try to produce a real PDF via a locally installed Chrome/Chromium.
function htmlToPdf(htmlPath, pdfPath) {
  const candidates = [
    process.env.CHROME_PATH,
    '/Applications/Google Chrome.app/Contents/MacOS/Google Chrome',
    '/Applications/Chromium.app/Contents/MacOS/Chromium',
    'google-chrome', 'chromium', 'chromium-browser',
  ].filter(Boolean);
  for (const bin of candidates) {
    try {
      const r = spawnSync(bin, [
        '--headless=new', '--disable-gpu', '--no-sandbox', '--no-first-run',
        `--print-to-pdf=${pdfPath}`, '--no-pdf-header-footer',
        'file://' + htmlPath,
      ], { timeout: 25000 });
      if (r.status === 0 && fs.existsSync(pdfPath) && fs.statSync(pdfPath).size > 500) return true;
    } catch { /* try next */ }
  }
  return false;
}

export async function markdownReport(title, markdown, opts = {}) {
  const stamp = new Date().toISOString().replace(/[:T]/g, '-').slice(0, 16);
  const slug = String(title).toLowerCase().replace(/[^a-z0-9]+/g, '-').replace(/^-+|-+$/g, '').slice(0, 60) || 'report';
  // Chart pack: full by default, none for plain reports, or the myNewspress
  // monitoring block only ('monitoring') for coverage reports.
  const charts = opts.charts === 'none' ? ''
    : opts.charts === 'monitoring' && opts.siteId && opts.siteId !== 'all'
      ? await monitoringOverview(opts.siteId, opts.range || '28d')
          .then((mon) => (mon?.enabled && !mon.error ? monitoringReportHtml(mon) : ''))
          .catch(() => '')
      : await chartPack(opts); // full graph pack unless a table-only report was requested
  const inner = card('', `<div>${mdToHtml(markdown)}</div>`) + charts;
  const html = shell(title, new Date().toISOString().slice(0, 10), inner, opts.siteId && opts.siteId !== 'all' ? siteCi(opts.siteId) : null)
    .replace('</head>', '<style>@media print { body { background:#fff !important } }</style></head>');
  fs.mkdirSync(REPORTS_DIR, { recursive: true });
  const base = `${stamp}-${slug}`;
  const htmlPath = path.join(REPORTS_DIR, base + '.html');
  fs.writeFileSync(htmlPath, html);
  const pdfPath = path.join(REPORTS_DIR, base + '.pdf');
  const src0 = opts.source || 'ai';
  if (htmlToPdf(htmlPath, pdfPath)) {
    recordReport({ file: base + '.pdf', siteId: opts.siteId || null, title, style: opts.charts === 'monitoring' ? 'monitoring' : 'full', format: 'pdf', source: src0 });
    return { filename: base + '.pdf', format: 'pdf' };
  }
  recordReport({ file: base + '.html', siteId: opts.siteId || null, title, style: opts.charts === 'monitoring' ? 'monitoring' : 'full', format: 'html', source: src0 });
  return { filename: base + '.html', format: 'html' }; // print-ready fallback
}
