// Media-monitoring adapter — reads a site's OPTIONAL second database
// (site.monitoring in sites.config.json) holding press-coverage articles.
// Schemas vary, so we PROBE the database once: find the articles table, map
// its columns by name, and only run the queries the schema supports. The
// overview response includes what was detected so mismatches are visible.

import { getSite, resolveRange, previousRange, isDemo } from './ga4.js';
import { qm, monitoringConfigured, confKey } from './db.js';
import { releasesByDate } from './legacy.js';

const TABLE_CANDIDATES = ['articles', 'coverage', 'clips', 'mentions', 'media_monitoring', 'monitoring_articles', 'press_coverage', 'items'];
const COLS = {
  date: ['published_at', 'published', 'date', 'article_date', 'publish_date', 'created_at'],
  title: ['title', 'headline', 'name'],
  publication: ['publication', 'outlet', 'source', 'media_outlet', 'publication_name', 'source_name'],
  url: ['url', 'link', 'article_url'],
  country: ['country', 'market'],
  region: ['region', 'area'],
  sentiment: ['sentiment', 'tone'],
  reach: ['reach', 'audience', 'potential_reach', 'circulation', 'impressions'],
  engagement: ['engagement', 'engagements', 'interactions'],
  topic: ['topic', 'category', 'category_type', 'subject'],
  model: ['model', 'vehicle', 'product', 'model_name'],
  mediaType: ['media_type', 'type', 'channel', 'medium'],
  brand: ['brand', 'company', 'competitor'],
  tier: ['tier', 'top_tier', 'outlet_tier'],
  ave: ['ave', 'ave_value', 'value', 'media_value', 'eav', 'advertising_value'],
  shares: ['social_shares', 'shares', 'share_count', 'social_engagement'],
  journalist: ['journalist', 'author', 'byline', 'writer', 'author_name'],
};

const schemaCache = new Map();

export async function monitoringSchema(siteId) {
  const site = getSite(siteId);
  const ck = siteId + '::' + confKey(site.monitoring);
  if (schemaCache.has(ck)) return schemaCache.get(ck);
  const tables = (await qm(siteId, 'SHOW TABLES')).map((r) => Object.values(r)[0]);
  const describe = async (t) => (await qm(siteId, `DESCRIBE \`${t}\``).catch(() => [])).map((d) => d.Field);
  // The REAL myNewspress schema (Laravel tenant DB from the my-news repo):
  // an `articles` table with published_at + headline. Older tenants may lack
  // side tables or columns — record exactly what exists and adapt.
  if (tables.includes('articles')) {
    const artColsRaw = await describe('articles');
    const lowArt = new Set(artColsRaw.map((c) => c.toLowerCase()));
    if (lowArt.has('published_at') && (lowArt.has('headline') || lowArt.has('title'))) {
      const schema = {
        kind: 'mynews', table: 'articles',
        artCols: lowArt,
        has: Object.fromEntries(['sentiments', 'press_releases', 'publications', 'contacts', 'markets', 'regions', 'market_region', 'tags', 'taggables', 'group_tag', 'groups', 'media_types', 'media_type_connections', 'clients', 'article_client', 'article_booking', 'nova_settings', 'competitors', 'article_competitor', 'nova_media_library']
          .map((t) => [t, tables.includes(t)])),
        cols: {
          date: 'published_at', title: lowArt.has('headline') ? 'headline' : 'title',
          reach: lowArt.has('reach') ? 'reach' : null, ave: lowArt.has('ave') ? 'ave' : null,
          url: lowArt.has('link') ? 'link' : null,
        },
      };
      schemaCache.set(ck, schema);
      return schema;
    }
  }
  // Generic path — STRICT matching: the table name must BE a coverage word
  // (optionally prefixed, e.g. staging_articles) — never a loose substring —
  // and its columns must validate (a date AND a headline/title) before we
  // accept it. Framework and contacts/CRM tables can never slip in.
  const FRAMEWORK = /^(nova_|telescope_|migrations$|action_events$|activity_log$|personal_access_tokens$|password_resets$|failed_jobs$|sessions$|cache$|jobs$|permissions$|roles$|model_has_|role_has_|menu|.*settings$|users$|tenants$|domains$|.*contacts$|.*recipients$)/i;
  const NAME_RX = /(^|_)(articles|coverage|clips|mentions|press_coverage|media_monitoring|monitoring_articles|items)$/i;
  const candidates = [
    ...TABLE_CANDIDATES.filter((t) => tables.includes(t) && !FRAMEWORK.test(t)),
    ...tables.filter((t) => !FRAMEWORK.test(t) && NAME_RX.test(t) && !TABLE_CANDIDATES.includes(t)),
  ];
  for (const table of candidates) {
    const names = await describe(table);
    if (!names.length) continue;
    const low = Object.fromEntries(names.map((n) => [n.toLowerCase(), n]));
    const cols = {};
    for (const [key, cands] of Object.entries(COLS)) {
      const found = cands.find((c) => low[c]);
      if (found) cols[key] = low[found];
    }
    if (!cols.date || !cols.title) continue; // not coverage data — keep looking
    const schema = { table, cols, allColumns: names };
    schemaCache.set(ck, schema);
    return schema;
  }
  if (tables.includes('tenants') || tables.includes('domains')) {
    let list = '';
    try {
      const rows = await qm(siteId, 'SELECT id, data FROM tenants LIMIT 12');
      const names = rows.map((r) => {
        try { return JSON.parse(r.data || '{}').tenancy_db_name || `my-news_${r.id}`; } catch { return `my-news_${r.id}`; }
      });
      if (names.length) list = ` Tenant databases on this host: ${names.join(', ')}.`;
    } catch { /* registry unreadable */ }
    throw new Error(`This is the myNewspress CENTRAL database — each client's coverage lives in its own TENANT database. In Sites → Media-monitoring database, change the database name to this client's tenant schema.${list}`);
  }
  throw new Error(`No coverage table with a date + headline found here. Tables present: ${tables.slice(0, 25).join(', ')}${tables.length > 25 ? ' …' : ''}. If this is myNewspress, connect the client's TENANT database — the one containing \`articles\`.`);
}

// 'veryPositive' → 'very positive' etc, so the UI colour map always matches.
const normSent = (n) => String(n || '').replace(/([a-z])([A-Z])/g, '$1 $2').toLowerCase().trim();
// Real tenant sentiment tables are full of typos and stray entries
// ("Positve", "Postive", "Nergative", "Neutal", "Neutro", "VeyPositive",
// "Positivo", "Ambivalent", "Ferrari", "" …). Canonicalise EVERYTHING to the
// five buckets the charts know, so one mistyped row never splits the donut.
function canonSent(n) {
  const s = normSent(n);
  if (!s) return 'unclassified';
  if (/^ve?r?y?\s*pos/.test(s)) return 'very positive';   // very/vey positive
  if (/^ve?r?y?\s*neg/.test(s)) return 'very negative';
  if (/pos/.test(s)) return 'positive';                    // positve, postive, positivo
  if (/ne[gr]a?[gt]/.test(s) || /^neg/.test(s)) return 'negative'; // negative, nergative
  if (/^neut|^ambiv|^neu\b/.test(s)) return 'neutral';     // neutral, neutal, neutro, ambivalent
  return 'unclassified';                                   // "Ferrari", "Product", junk rows
}

// ── Market alias merging + market→region map ────────────────────────────
// Real tenants carry DUPLICATE market rows for the same place ("UK" id 1 AND
// "United Kingdom" id 27) with articles on one and the region mapping on the
// other. Everything region/market-shaped works from this merged map: one
// entry per real-world market, holding ALL its ids and its named region.
const CANON_MARKET = {
  'uk': 'united kingdom', 'great britain': 'united kingdom', 'britain': 'united kingdom',
  'us': 'united states', 'usa': 'united states', 'america': 'united states',
  'czechia': 'czech republic', 'holland': 'netherlands',
  'uae': 'united arab emirates', 'turkiye': 'turkey',
};
const canonMarket = (n) => {
  const k = String(n || '').toLowerCase().normalize('NFD').replace(/[\u0300-\u036f]/g, '')
    .replace(/[^a-z ]+/g, ' ').replace(/\s+/g, ' ').trim();
  return CANON_MARKET[k] || k;
};
async function marketRegionMap(siteId, H) {
  const rows = await qm(siteId,
    `SELECT m.id, m.name${H.market_region && H.regions
      ? `, (SELECT r.name FROM market_region mr JOIN regions r ON r.id = mr.region_id
             WHERE mr.market_id = m.id AND TRIM(COALESCE(r.name,'')) <> '' ORDER BY mr.region_id LIMIT 1) AS region`
      : ', NULL AS region'}
       FROM markets m`).catch(() => []);
  const groups = new Map(); // canonical name → { ids, name, region }
  for (const m of rows) {
    const k = canonMarket(m.name) || 'id:' + m.id;
    const g = groups.get(k) || { ids: [], name: null, region: null };
    g.ids.push(Number(m.id));
    const reg = m.region && String(m.region).trim() ? String(m.region).trim() : null;
    if (reg && !g.region) { g.region = reg; g.name = String(m.name).trim() || g.name; }
    if (!g.name && String(m.name || '').trim()) g.name = String(m.name).trim();
    groups.set(k, g);
  }
  const byId = new Map();
  for (const g of groups.values()) for (const id of g.ids) byId.set(id, g);
  return { groups: [...groups.values()], byId };
}

// ── SHARED tag registry (multi-tenant myNewspress) ──────────────────────
// Some tenants (Ferrari) keep their tags in the CENTRAL schema on the same
// server (`mynews_prod`.tags, keyed by client_id) while the tenant DB holds
// only the article↔tag links. Probed ONCE per site: if the tenant's links
// don't resolve locally, every schema on the server is checked for the tags
// table that actually contains the referenced ids; joins then go cross-
// database with the same read-only connection. Optional override:
// site.monitoring.sharedDatabase (+ sharedClientId).
const sharedTagsCache = new Map();
async function sharedTagsInfo(siteId, H) {
  const site = getSite(siteId);
  if (!H?.tags || !H?.taggables) return null;
  const key = siteId + '::' + confKey(site.monitoring);
  if (sharedTagsCache.has(key)) return sharedTagsCache.get(key);
  let info = null; // null = use LOCAL tags as before
  try {
    const r = (await qm(siteId,
      `SELECT (SELECT COUNT(*) FROM taggables) AS links,
              (SELECT COUNT(*) FROM taggables tg JOIN tags t ON t.id = tg.tag_id) AS resolved`))[0];
    if (Number(r?.links) > 0 && Number(r?.resolved) === 0) {
      const sample = (await qm(siteId, 'SELECT DISTINCT tag_id FROM taggables LIMIT 40'))
        .map((x) => Number(x.tag_id)).filter((n) => Number.isFinite(n) && n > 0);
      if (sample.length) {
        const cands = [];
        if (site.monitoring?.sharedDatabase) cands.push(String(site.monitoring.sharedDatabase));
        else {
          const dbs = (await qm(siteId, 'SHOW DATABASES').catch(() => []))
            .map((x) => String(Object.values(x)[0]))
            .filter((d) => !/^(information_schema|performance_schema|mysql|sys)$/i.test(d) && d !== site.monitoring?.database);
          // Central/shared schemas FIRST (prod/central/landlord/main names,
          // then anything that is NOT a per-tenant "my-news_…" schema) — a
          // server hosting dozens of tenants must never push the shared DB
          // past the scan cap.
          const score = (d) => /prod|central|landlord|main|shared/i.test(d) ? 0 : /^my.?news[_-]/i.test(d) ? 2 : 1;
          dbs.sort((a2, b2) => score(a2) - score(b2) || a2.localeCompare(b2));
          cands.push(...dbs);
        }
        let best = null;
        for (const db of cands.slice(0, 80)) {
          try {
            const r2 = (await qm(siteId, `SELECT COUNT(*) AS n FROM \`${db.replace(/`/g, '')}\`.tags WHERE id IN (${sample.join(',')})`))[0];
            if (Number(r2?.n) > (best?.n || 0)) best = { db, n: Number(r2.n) };
          } catch { /* schema without tags — skip */ }
        }
        if (best) {
          const dbq = best.db.replace(/`/g, '');
          const cols = new Set((await qm(siteId, `DESCRIBE \`${dbq}\`.tags`).catch(() => []))
            .map((d) => String(d.Field).toLowerCase()));
          let clientId = site.monitoring?.sharedClientId != null ? Number(site.monitoring.sharedClientId) : null;
          if (clientId == null && cols.has('client_id')) {
            const cr = (await qm(siteId,
              `SELECT client_id AS c, COUNT(*) AS n FROM \`${dbq}\`.tags WHERE id IN (${sample.join(',')})
                GROUP BY client_id ORDER BY n DESC LIMIT 1`).catch(() => []))[0];
            clientId = cr?.c != null ? Number(cr.c) : null;
          }
          const tbls = new Set((await qm(siteId, `SHOW TABLES FROM \`${dbq}\``).catch(() => []))
            .map((x) => String(Object.values(x)[0]).toLowerCase()));
          const gcols = tbls.has('groups')
            ? new Set((await qm(siteId, `DESCRIBE \`${dbq}\`.\`groups\``).catch(() => []))
              .map((d) => String(d.Field).toLowerCase()))
            : new Set();
          // Publications & contacts (journalists) can live centrally too —
          // Ferrari's tenant has an EMPTY publications table and no contacts
          // table, while its articles reference the central ones.
          const pubCols = tbls.has('publications')
            ? new Set((await qm(siteId, `DESCRIBE \`${dbq}\`.publications`).catch(() => [])).map((d) => String(d.Field).toLowerCase()))
            : new Set();
          const ctCols = tbls.has('contacts')
            ? new Set((await qm(siteId, `DESCRIBE \`${dbq}\`.contacts`).catch(() => [])).map((d) => String(d.Field).toLowerCase()))
            : new Set();
          info = {
            mode: 'shared', db: dbq, clientId,
            hasClientId: cols.has('client_id'), hasDeleted: cols.has('deleted_at'), hasParent: cols.has('parent_id'),
            hasCompetitorFlag: cols.has('is_competitor_tag'),
            groups: tbls.has('groups') && tbls.has('group_tag'),
            groupsClientId: gcols.has('client_id'),
            catMap: tbls.has('category_mapping'),
            pub: pubCols.size ? { titleCol: pubCols.has('title') ? 'title' : pubCols.has('name') ? 'name' : null, hasKey: pubCols.has('key_publication') } : null,
            contact: ctCols.size ? { cols: ctCols } : null,
          };
        }
      }
    }
    // Only a COMPLETED probe is cached. A thrown probe (momentary DB issue)
    // must retry on the next call, not lock the site into local-mode.
    sharedTagsCache.set(key, info);
  } catch { /* transient — retry next call, do NOT cache */ }
  return info;
}
export function tagSourceInfo(siteId, H) { return sharedTagsInfo(siteId, H); } // diagnostics

// WHERE publications / contacts (journalists) live for this tenant — local
// tables when they hold data, the shared central schema otherwise. Returns
// SQL table refs plus availability flags and column adaptations.
async function sharedLookupRefs(siteId, H) {
  const sh = await sharedTagsInfo(siteId, H);
  const out = {
    PUB: 'publications', pubOk: !!H.publications, pubTitle: 'p.title', pubHasKey: true,
    CT: 'contacts', ctOk: !!H.contacts,
    ctName: "COALESCE(NULLIF(TRIM(CONCAT(COALESCE(ct.first_name,''), ' ', COALESCE(ct.last_name,''))), ''), ct.known_as_name, ct.email)",
  };
  if (!sh) return out;
  if (sh.pub?.titleCol) {
    let localEmpty = !H.publications;
    if (H.publications) {
      const r = (await qm(siteId, 'SELECT COUNT(*) AS n FROM publications').catch(() => [{ n: 1 }]))[0];
      localEmpty = !Number(r?.n);
    }
    if (localEmpty) {
      out.PUB = '`' + sh.db + '`.publications';
      out.pubOk = true;
      out.pubTitle = 'p.`' + sh.pub.titleCol + '`';
      out.pubHasKey = !!sh.pub.hasKey;
    }
  }
  if (sh.contact?.cols) {
    let localEmpty = !H.contacts;
    if (H.contacts) {
      const r = (await qm(siteId, 'SELECT COUNT(*) AS n FROM contacts').catch(() => [{ n: 1 }]))[0];
      localEmpty = !Number(r?.n);
    }
    if (localEmpty) {
      const c = sh.contact.cols;
      const parts = [];
      if (c.has('first_name') || c.has('last_name')) parts.push("NULLIF(TRIM(CONCAT(COALESCE(ct.first_name,''), ' ', COALESCE(ct.last_name,''))), '')");
      if (c.has('known_as_name')) parts.push('ct.known_as_name');
      if (c.has('name')) parts.push('ct.name');
      if (c.has('email')) parts.push('ct.email');
      if (parts.length) {
        out.CT = '`' + sh.db + '`.contacts';
        out.ctOk = true;
        out.ctName = parts.length > 1 ? `COALESCE(${parts.join(', ')})` : parts[0];
      }
    }
  }
  return out;
}
// SQL fragments for wherever the tags actually live.
function tagRefs(sh) {
  if (!sh) return { T: 'tags', G: '`groups`', GT: 'group_tag', extra: '', gExtra: '' };
  const p = '`' + sh.db + '`.';
  return {
    T: p + 'tags', G: p + '`groups`', GT: p + 'group_tag',
    extra: (sh.hasDeleted ? ' AND t.deleted_at IS NULL' : '')
      + (sh.hasClientId && sh.clientId != null ? ` AND t.client_id = ${Number(sh.clientId)}` : '')
      + (sh.hasCompetitorFlag ? ' AND COALESCE(t.is_competitor_tag, 0) = 0' : ''),
    gExtra: sh.groupsClientId && sh.clientId != null ? ` AND g.client_id = ${Number(sh.clientId)}` : '',
  };
}
const MODEL_GROUPS = "('models', 'model', 'product', 'products', 'current model')";
const CAT_PARENTS = "('news categories', 'news category', 'categories', 'category', 'topics')";
// Model / category breakdown from the tag tree — LOCAL or SHARED, via tag
// GROUPS, a PARENT-TAG hierarchy, or (categories only) the flat FEA
// vocabulary — whichever the tenant actually uses.
async function tagBreakdown(siteId, H, kind, { ACTIVE, BETWEEN, P, REACH, morph, lim = 12 }) {
  const sh = await sharedTagsInfo(siteId, H);
  const { T, G, GT, extra, gExtra } = tagRefs(sh);
  const useGroups = sh ? sh.groups : (H.group_tag && H.groups);
  const base = (joinExtra, whereExtra) => qm(siteId,
    `SELECT t.name, COUNT(DISTINCT a.id) AS articles, ${REACH} AS reach
       FROM articles a
       JOIN taggables tg ON tg.taggable_id = a.id AND tg.taggable_type ${morph}
       JOIN ${T} t ON t.id = tg.tag_id${extra}
       ${joinExtra}
      WHERE ${ACTIVE} AND ${BETWEEN}${whereExtra ? ' AND ' + whereExtra : ''}
      GROUP BY t.name ORDER BY articles DESC LIMIT ${lim}`, P).catch(() => []);
  // Categories first choice: the central category_mapping table — the SAME
  // category → tag-ids mapping the manual FEA report uses (Product /
  // Corporate / Racing / Lifestyle), counted as DISTINCT articles per
  // category so multi-tagged articles never double-count.
  if (kind === 'topics' && sh?.catMap) {
    try {
      const maps = await qm(siteId, `SELECT category, tag_ids FROM \`${sh.db}\`.category_mapping`);
      const byCat = new Map();
      for (const m of maps) {
        let ids = [];
        try { ids = JSON.parse(m.tag_ids || '[]'); } catch { ids = String(m.tag_ids || '').split(/[,\s]+/); }
        ids = (Array.isArray(ids) ? ids : []).map(Number).filter((n) => Number.isFinite(n) && n > 0);
        if (!ids.length) continue;
        const cat = String(m.category || '').trim();
        if (!cat) continue;
        const set = byCat.get(cat) || new Set();
        ids.forEach((n) => set.add(n));
        byCat.set(cat, set);
      }
      if (byCat.size) {
        const cats = [...byCat.entries()].slice(0, 15);
        const rows = (await Promise.all(cats.map(async ([cat, set]) => {
          const idList = [...set].slice(0, 2000).join(',');
          const r = (await qm(siteId,
            `SELECT COUNT(DISTINCT a.id) AS articles, ${REACH} AS reach
               FROM articles a
               JOIN taggables tg ON tg.taggable_id = a.id AND tg.taggable_type ${morph}
              WHERE tg.tag_id IN (${idList}) AND ${ACTIVE} AND ${BETWEEN}`, P).catch(() => []))[0];
          return { name: cat, articles: Number(r?.articles) || 0, reach: Number(r?.reach) || 0 };
        }))).filter((x) => x.articles > 0).sort((a2, b2) => b2.articles - a2.articles).slice(0, lim);
        if (rows.length) return rows;
      }
    } catch { /* next strategy */ }
  }
  if (useGroups) {
    if (kind === 'topics') {
      // Prefer the explicit category group when one exists…
      const rows = await base(
        `JOIN ${GT} gt ON gt.tag_id = t.id JOIN ${G} g ON g.id = gt.group_id AND LOWER(TRIM(g.name)) IN ${CAT_PARENTS}${gExtra}`, '');
      if (rows.length) return rows;
    }
    const rows = await base(
      `JOIN ${GT} gt ON gt.tag_id = t.id JOIN ${G} g ON g.id = gt.group_id AND LOWER(TRIM(g.name)) ${kind === 'models' ? 'IN' : 'NOT IN'} ${MODEL_GROUPS}${gExtra}`, '');
    if (rows.length) return rows;
  }
  if (!sh || sh.hasParent) {
    const parents = kind === 'models' ? MODEL_GROUPS : CAT_PARENTS;
    const rows = await base(`JOIN ${T} pt ON pt.id = t.parent_id AND LOWER(TRIM(pt.name)) IN ${parents}`, '');
    if (rows.length) return rows;
  }
  if (kind === 'topics') {
    const rows = await base('', `LOWER(TRIM(t.name)) IN ('product', 'corporate', 'racing', 'lifestyle', 'partnership', 'events', 'event', 'reviews', 'review', 'motorsport')`);
    if (rows.length) return rows;
  }
  return [];
}

// ── Dedicated adapter for the real myNewspress production schema ────────
// (articles.status='active', sentiments/publications/contacts joins, markets
// → regions, tags grouped under "Models", media_types morphs, clients pivot
// for share of voice, press_release_id for direct release attribution.)
const artProbeCache = new Map();
// One-time probe per DB: how many articles exist at all, how many pass the
// status filter, and the newest article date — powers auto-healing + the
// "why is this empty" diagnostics.
async function artProbe(siteId, hasStatus) {
  const site = getSite(siteId);
  const key = siteId + '::' + confKey(site.monitoring);
  if (artProbeCache.has(key)) return artProbeCache.get(key);
  let probe = { total: 0, active: 0, lastArticle: null };
  try {
    const [r] = await qm(siteId,
      `SELECT COUNT(*) AS total,
              ${hasStatus ? "COALESCE(SUM(status = 'active' OR status IS NULL OR status = ''), 0)" : 'COUNT(*)'} AS active,
              DATE(MAX(published_at)) AS lastArticle
         FROM articles`);
    probe = { total: Number(r?.total) || 0, active: Number(r?.active) || 0, lastArticle: r?.lastArticle ? String(r.lastArticle).slice(0, 10) : null };
  } catch { /* diagnostics only */ }
  artProbeCache.set(key, probe);
  return probe;
}

async function myNewsOverview(siteId, rangeStr, schema, marketId = null) {
  const { startDate, endDate } = resolveRange(rangeStr);
  const prev = previousRange({ startDate, endDate });
  const site = getSite(siteId);
  const out = { enabled: true, errors: [], schema: { table: 'articles (myNewspress)', cols: schema.cols } };
  const soft = async (label, fn, fb = []) => { try { return await fn(); } catch (e) { out.errors.push(`${label}: ${String(e.message || e).slice(0, 160)}`); return fb; } };
  const H = schema.has;
  const A = schema.artCols || new Set(); // lowercase article columns present
  const hasC = (c) => A.has(c);
  const TITLE = 'a.`' + (schema.cols.title || 'headline') + '`';
  // Publications / contacts table refs — local or shared central schema.
  const SR = await sharedLookupRefs(siteId, H);
  // Auto-heal: if this tenant's articles use a different status convention
  // (nothing matches 'active'), drop the filter instead of showing nothing.
  const probe = await artProbe(siteId, hasC('status'));
  out.freshness = { totalArticles: probe.total, lastArticle: probe.lastArticle };
  const ACTIVE = hasC('status') && !(probe.total > 0 && probe.active === 0)
    ? "(a.status = 'active' OR a.status IS NULL OR a.status = '')" : '1=1';
  const mktIds = hasC('market_id') ? String(marketId || '').split(',').map(Number).filter(Boolean) : [];
  const MKT = mktIds.length ? ` AND a.market_id IN (${mktIds.map(() => '?').join(',')})` : '';
  const BETWEEN = 'a.published_at BETWEEN ? AND ?' + MKT;
  // Brand-owned coverage only: the tenant's feed also tracks the whole
  // competitive set (for share of voice) — articles tagged to a competitor
  // must not surface as the CLIENT'S highlight articles.
  const BRAND_ONLY = H.competitors && H.article_competitor
    ? ' AND NOT EXISTS (SELECT 1 FROM article_competitor acx WHERE acx.article_id = a.id)' : '';
  const P = [startDate, endDate + ' 23:59:59', ...mktIds];
  const PP = [prev.startDate, prev.endDate + ' 23:59:59', ...mktIds];
  out.marketId = mktIds.length === 1 ? mktIds[0] : null;
  out.marketIds = mktIds;
  const REACH = hasC('reach') ? 'SUM(a.reach)' : 'NULL';
  const REACH1 = hasC('reach') ? 'a.reach' : '0';
  const AVE = hasC('ave') ? "SUM(CAST(REPLACE(REPLACE(REPLACE(REPLACE(COALESCE(a.ave,'0'),'£',''),'$',''),'€',''),',','') AS DECIMAL(16,2)))" : 'NULL';
  const MORPH_ART = "LIKE '%Article'"; // taggable_type = Modules\\...\\Article

  const [totCur, totPrev] = await Promise.all([
    soft('totals', () => qm(siteId,
      `SELECT COUNT(*) AS articles, ${REACH} AS reach, ${AVE} AS ave${hasC('publication_id') ? ', COUNT(DISTINCT a.publication_id) AS publications' : ''}
         FROM articles a WHERE ${ACTIVE} AND ${BETWEEN}`, P), [{}]),
    soft('previous', () => qm(siteId,
      `SELECT COUNT(*) AS articles, ${REACH} AS reach, ${AVE} AS ave
         FROM articles a WHERE ${ACTIVE} AND ${BETWEEN}`, PP), [{}]),
  ]);
  out.totals = {
    articles: Number(totCur[0]?.articles) || 0, reach: fmtReach(totCur[0]?.reach),
    ave: Number(totCur[0]?.ave) || null, shares: null,
    publications: Number(totCur[0]?.publications) || null,
    prevArticles: Number(totPrev[0]?.articles) || 0, prevReach: fmtReach(totPrev[0]?.reach),
    prevAve: Number(totPrev[0]?.ave) || null,
  };

  const jobs = [];
  jobs.push(soft('daily', () => qm(siteId,
    `SELECT DATE(a.published_at) AS date, COUNT(*) AS articles, ${REACH} AS reach
       FROM articles a WHERE ${ACTIVE} AND ${BETWEEN} GROUP BY DATE(a.published_at) ORDER BY date`, P))
    .then((v) => (out.daily = v.map((x) => ({ ...x, articles: Number(x.articles) || 0, reach: fmtReach(x.reach) })))));

  // Canonicalise + MERGE: "Positive" and "Positve" are two DB rows but must
  // be ONE chart segment — sum the duplicates after canonicalising.
  const mergeSent = (rows, key = 'name') => {
    const by = new Map();
    for (const x of rows) {
      const k = canonSent(x[key]);
      const hit = by.get(k);
      if (hit) { hit.articles += Number(x.articles) || 0; hit.reach = (Number(hit.reach) || 0) + (Number(x.reach) || 0); }
      else by.set(k, { ...x, [key]: k, articles: Number(x.articles) || 0, reach: Number(x.reach) || 0 });
    }
    return [...by.values()].sort((a2, b2) => b2.articles - a2.articles);
  };
  if (hasC('sentiment_id') && H.sentiments) jobs.push(soft('sentiment', () => qm(siteId,
    `SELECT s.name, COUNT(*) AS articles, ${REACH} AS reach
       FROM articles a JOIN sentiments s ON s.id = a.sentiment_id
      WHERE ${ACTIVE} AND ${BETWEEN} GROUP BY s.name ORDER BY articles DESC`, P))
    .then((v) => (out.sentiment = mergeSent(v).map((x) => ({ ...x, reach: fmtReach(x.reach) })))));
  if (hasC('sentiment_id') && H.sentiments) jobs.push(soft('prevSentiment', () => qm(siteId,
    `SELECT s.name, COUNT(*) AS articles FROM articles a JOIN sentiments s ON s.id = a.sentiment_id
      WHERE ${ACTIVE} AND ${BETWEEN} GROUP BY s.name`, PP))
    .then((v) => (out.prevSentiment = mergeSent(v))));

  if (hasC('market_id') && H.markets) {
    // Region model, done in JS over the ALIAS-MERGED market map (see
    // marketRegionMap): duplicate market rows meaning the same place ("UK" +
    // "United Kingdom", "Czechia" + "Czech Republic") count as ONE market,
    // and a region owns the articles of EVERY alias — so UK's articles land
    // in Northern Europe even though only the "United Kingdom" row carries
    // the mapping. Region charts show ONLY the tenant's listed regions plus
    // one honest "Unassigned" bucket; unmappedMarkets says what's in it.
    const mmapP = marketRegionMap(siteId, H);
    // Per-market article/reach rows for the period — shared by both region jobs.
    const perMarketP = qm(siteId,
      `SELECT a.market_id AS mid, COUNT(*) AS articles, ${REACH} AS reach
         FROM articles a WHERE ${ACTIVE} AND ${BETWEEN} GROUP BY a.market_id`, P);
    jobs.push(soft('regions', async () => {
      const [rows, mmap] = await Promise.all([perMarketP, mmapP]);
      const agg = new Map();
      const unmapped = new Map();
      for (const r of rows) {
        const g = r.mid != null ? mmap.byId.get(Number(r.mid)) : null;
        const region = g?.region || 'Unassigned';
        const a2 = agg.get(region) || { name: region, articles: 0, reach: 0 };
        a2.articles += Number(r.articles) || 0;
        a2.reach += Number(r.reach) || 0;
        agg.set(region, a2);
        if (!g?.region) {
          const label = g?.name || (r.mid == null ? 'No market set' : 'Market ' + r.mid);
          unmapped.set(label, (unmapped.get(label) || 0) + (Number(r.articles) || 0));
        }
      }
      out.unmappedMarkets = [...unmapped.entries()].map(([market, articles]) => ({ market, articles }))
        .sort((x, y) => y.articles - x.articles).slice(0, 20);
      out.regions = [...agg.values()]
        .sort((x, y) => y.reach - x.reach || y.articles - x.articles).slice(0, 12)
        .map((x) => ({ ...x, reach: fmtReach(x.reach) }));
    }));
    // MARKET SPLIT — the same chart as Region split but per (merged) market:
    // volume + share of reach. Shown whenever there is more than one market.
    jobs.push(soft('marketSplit', async () => {
      const [rows, mmap] = await Promise.all([perMarketP, mmapP]);
      const agg = new Map();
      let totReach = 0;
      for (const r of rows) {
        const g = r.mid != null ? mmap.byId.get(Number(r.mid)) : null;
        const name = g?.name || (r.mid == null ? 'No market set' : 'Market ' + r.mid);
        const a2 = agg.get(name) || { name, region: g?.region || null, articles: 0, reach: 0 };
        a2.articles += Number(r.articles) || 0;
        a2.reach += Number(r.reach) || 0;
        totReach += Number(r.reach) || 0;
        agg.set(name, a2);
      }
      out.marketSplit = [...agg.values()].filter((x) => x.articles > 0)
        .sort((x, y) => y.articles - x.articles || y.reach - x.reach).slice(0, 15)
        .map((x) => ({ ...x, reachShare: totReach ? Math.round((100 * x.reach) / totReach) : 0, reach: fmtReach(x.reach) }));
    }));
    if (hasC('sentiment_id') && H.sentiments) jobs.push(soft('sentimentByRegion', async () => {
      const [rows, mmap] = await Promise.all([qm(siteId,
        `SELECT a.market_id AS mid, s.name AS sentiment, COUNT(*) AS articles
           FROM articles a JOIN sentiments s ON s.id = a.sentiment_id
          WHERE ${ACTIVE} AND ${BETWEEN} GROUP BY a.market_id, s.name`, P), mmapP]);
      const by = new Map();
      for (const x of rows) {
        const g = x.mid != null ? mmap.byId.get(Number(x.mid)) : null;
        const k = (g?.region || 'Unassigned') + '||' + canonSent(x.sentiment);
        by.set(k, (by.get(k) || 0) + (Number(x.articles) || 0));
      }
      out.sentimentByRegion = [...by.entries()].map(([k, n]) => {
        const [region, sentiment] = k.split('||');
        return { region, sentiment, articles: n };
      });
    }));
    // Top 2 articles PER REGION — powers the report's region breakdown blocks.
    jobs.push(soft('regionTopArticles', async () => {
      const [rows, mmap] = await Promise.all([qm(siteId,
        `SELECT a.market_id AS mid, ${TITLE} AS title, DATE(a.published_at) AS date,
                ${REACH1} AS reach, ${hasC('link') ? 'a.link' : 'NULL'} AS url,
                ${hasC('publication_id') && SR.pubOk ? SR.pubTitle : "''"} AS publication
           FROM articles a
           ${hasC('publication_id') && SR.pubOk ? `LEFT JOIN ${SR.PUB} p ON p.id = a.publication_id` : ''}
          WHERE ${ACTIVE} AND ${BETWEEN}${BRAND_ONLY}
          ORDER BY ${hasC('reach') ? 'a.reach' : 'a.published_at'} DESC LIMIT 200`, P), mmapP]);
      const by = {};
      for (const r of rows) {
        const g = r.mid != null ? mmap.byId.get(Number(r.mid)) : null;
        const k = g?.region || 'Unassigned';
        ((by[k] = by[k] || []).length < 6) && by[k].push({ ...r, region: k });
      }
      out.regionTopArticles = by;
    }));
  }

  // Coverage category, myNewspress-style: press release vs press fleet vs the
  // article's media_focus (Product / Corporate / Racing / Lifestyle …).
  // Many tenants never fill media_focus, which collapses everything into
  // 'Other' — in that case the articles' TAG categories (every tag group
  // except 'Models') give the real editorial split.
  // Category breakdown for ANY period — run for the current period and the
  // previous one, so the analyst can compare like-for-like month on month
  // ("Corporate generated 196 articles, only eight fewer than May").
  const topicsFor = async (PA) => {
    const base = await qm(siteId,
      `SELECT CASE ${hasC('press_release_id') ? "WHEN a.press_release_id IS NOT NULL THEN 'Press release'" : ''}
                   ${H.article_booking ? "WHEN ab.article_id IS NOT NULL THEN 'Press fleet'" : ''}
                   ELSE ${hasC('media_focus') ? "COALESCE(NULLIF(TRIM(a.media_focus), ''), 'Other')" : "'Other'"} END AS name,
              COUNT(DISTINCT a.id) AS articles, ${REACH} AS reach
         FROM articles a
         ${H.article_booking ? 'LEFT JOIN (SELECT DISTINCT article_id FROM article_booking) ab ON ab.article_id = a.id' : ''}
        WHERE ${ACTIVE} AND ${BETWEEN} GROUP BY name ORDER BY articles DESC LIMIT 12`, PA);
    const norm = base.map((x) => ({ ...x, articles: Number(x.articles) || 0, reach: fmtReach(x.reach) }));
    const totalN = norm.reduce((a2, x) => a2 + x.articles, 0);
    const otherRow = norm.find((x) => x.name === 'Other');
    if (H.tags && H.taggables && totalN && otherRow && otherRow.articles / totalN > 0.6) {
      // Local tag groups, SHARED central tags (Ferrari: mynews_prod keyed by
      // client_id, incl. the category_mapping table the FEA report uses), or
      // a parent-tag tree — tagBreakdown() picks whichever this tenant has.
      const tagCats = await tagBreakdown(siteId, H, 'topics',
        { ACTIVE, BETWEEN, P: PA, REACH, morph: MORPH_ART, lim: 10 });
      if (tagCats.length) {
        const keep = norm.filter((x) => x.name === 'Press release' || x.name === 'Press fleet');
        const cats = tagCats.map((x) => ({ name: x.name, articles: Number(x.articles) || 0, reach: fmtReach(x.reach) }));
        return [...keep, ...cats].sort((a2, b2) => b2.articles - a2.articles).slice(0, 12);
      }
    }
    return norm;
  };
  if (hasC('press_release_id') || hasC('media_focus')) {
    jobs.push(soft('topics', () => topicsFor(P)).then((v) => (out.topics = v)));
    jobs.push(soft('prevTopics', () => topicsFor(PP)).then((v) => (out.prevTopics = (v || []).slice(0, 8))));
  }

  if (H.tags && H.taggables) {
    // Local 'Models' group (KGM), SHARED central 'Product' group (Ferrari —
    // mynews_prod.groups per client), or a parent-tag tree — auto-detected.
    jobs.push(soft('models', () => tagBreakdown(siteId, H, 'models',
      { ACTIVE, BETWEEN, P, REACH, morph: MORPH_ART, lim: 12 }))
      .then((v) => (out.models = (v || []).map((x) => ({ ...x, articles: Number(x.articles) || 0, reach: fmtReach(x.reach) })))));
    jobs.push(soft('prevModels', () => tagBreakdown(siteId, H, 'models',
      { ACTIVE, BETWEEN, P: PP, REACH, morph: MORPH_ART, lim: 8 }))
      .then((v) => (out.prevModels = (v || []).map((x) => ({ ...x, articles: Number(x.articles) || 0, reach: fmtReach(x.reach) })))));
    // Diagnostic: WHERE the tags were found — visible in the API payload.
    jobs.push(soft('tagSource', async () => {
      const sh = await sharedTagsInfo(siteId, H);
      out.tagSource = sh
        ? `shared:${sh.db} · client ${sh.clientId ?? '?'}${sh.groups ? ' · groups' : ''}${sh.catMap ? ' · category_mapping' : ''}`
        : 'local';
    }, null));
  }

  if (H.media_types && H.media_type_connections) {
    // TRIM + case-merge: real tenants have 'Online ' (trailing space), 'tv'
    // vs 'TV' — they must land in one segment each, not split the donut.
    const mediaTypesFor = (PA) => qm(siteId,
      `SELECT TRIM(mt.name) AS name, COUNT(DISTINCT a.id) AS articles, ${REACH} AS reach
         FROM articles a
         JOIN media_type_connections mtc ON mtc.taggable_id = a.id AND mtc.taggable_type ${MORPH_ART}
         JOIN media_types mt ON mt.id = mtc.media_type_id
        WHERE ${ACTIVE} AND ${BETWEEN} GROUP BY TRIM(mt.name) ORDER BY articles DESC LIMIT 8`, PA);
    const mergeMt = (v) => {
      const by = new Map();
      for (const x of v) {
        const label = String(x.name || 'Other').trim();
        const k = label.toLowerCase();
        const hit = by.get(k);
        if (hit) { hit.articles += Number(x.articles) || 0; hit.reach += Number(x.reach) || 0; }
        else by.set(k, { name: k === 'tv' ? 'TV' : label, articles: Number(x.articles) || 0, reach: Number(x.reach) || 0 });
      }
      return [...by.values()].sort((a2, b2) => b2.articles - a2.articles).map((x) => ({ ...x, reach: fmtReach(x.reach) }));
    };
    jobs.push(soft('prevMediaTypes', () => mediaTypesFor(PP)).then((v) => (out.prevMediaTypes = mergeMt(v || []))));
    jobs.push(soft('mediaTypes', () => mediaTypesFor(P))
      .then((v) => {
        const by = new Map();
        for (const x of v) {
          const label = String(x.name || 'Other').trim();
          const k = label.toLowerCase();
          const hit = by.get(k);
          if (hit) { hit.articles += Number(x.articles) || 0; hit.reach += Number(x.reach) || 0; }
          else by.set(k, { name: k === 'tv' ? 'TV' : label, articles: Number(x.articles) || 0, reach: Number(x.reach) || 0 });
        }
        out.mediaTypes = [...by.values()].sort((a2, b2) => b2.articles - a2.articles)
          .map((x) => ({ ...x, reach: fmtReach(x.reach) }));
      }));
  }

  if (hasC('publication_id') && SR.pubOk) {
    // Publication landscape stats — "Ferrari featured across 44 unique
    // titles; 29 were top-tier, generating 45.9M Reach" (the manual report's
    // tier analysis). key_publication is myNewspress's top-tier flag.
    const pubStatsFor = async (PA) => {
      const sel = SR.pubHasKey
        ? `SELECT COUNT(DISTINCT a.publication_id) AS uniquePubs,
                  COUNT(DISTINCT CASE WHEN p.key_publication = 1 THEN a.publication_id END) AS keyPubs,
                  COUNT(DISTINCT CASE WHEN p.key_publication = 1 THEN a.id END) AS keyArticles,
                  ${hasC('reach') ? 'COALESCE(SUM(CASE WHEN p.key_publication = 1 THEN a.reach ELSE 0 END), 0)' : '0'} AS keyReach,
                  COUNT(DISTINCT a.id) AS totalArticles,
                  ${hasC('reach') ? 'COALESCE(SUM(a.reach), 0)' : '0'} AS totalReach
             FROM articles a JOIN ${SR.PUB} p ON p.id = a.publication_id
            WHERE ${ACTIVE} AND ${BETWEEN}`
        : `SELECT COUNT(DISTINCT a.publication_id) AS uniquePubs FROM articles a WHERE ${ACTIVE} AND ${BETWEEN}`;
      const [r] = await qm(siteId, sel, PA);
      return r || null;
    };
    jobs.push(soft('pubStats', async () => {
      const [cur, prv] = await Promise.all([pubStatsFor(P), pubStatsFor(PP).catch(() => null)]);
      if (!cur) return;
      out.pubStats = {
        uniquePublications: Number(cur.uniquePubs) || 0,
        prevUniquePublications: prv ? Number(prv.uniquePubs) || 0 : undefined,
        ...(SR.pubHasKey ? {
          topTierPublications: Number(cur.keyPubs) || 0,
          topTierArticles: Number(cur.keyArticles) || 0,
          topTierReach: fmtReach(cur.keyReach),
          otherPublications: (Number(cur.uniquePubs) || 0) - (Number(cur.keyPubs) || 0),
          otherArticles: (Number(cur.totalArticles) || 0) - (Number(cur.keyArticles) || 0),
          otherReach: fmtReach((Number(cur.totalReach) || 0) - (Number(cur.keyReach) || 0)),
        } : {}),
      };
    }));
    jobs.push(soft('publications', async () => {
      const pubs = await (SR.pubHasKey ? qm(siteId,
        `SELECT ${SR.pubTitle} AS name, COUNT(*) AS articles, ${REACH} AS reach,
                MAX(p.key_publication) AS keyPub
           FROM articles a JOIN ${SR.PUB} p ON p.id = a.publication_id
          WHERE ${ACTIVE} AND ${BETWEEN}
          GROUP BY p.id, ${SR.pubTitle} ORDER BY reach DESC LIMIT 20`, P) : Promise.reject(new Error('no key col')))
        .catch(() => qm(siteId, // tenants/central without key_publication
          `SELECT ${SR.pubTitle} AS name, COUNT(*) AS articles, ${REACH} AS reach, 0 AS keyPub
             FROM articles a JOIN ${SR.PUB} p ON p.id = a.publication_id
            WHERE ${ACTIVE} AND ${BETWEEN}
            GROUP BY p.id, ${SR.pubTitle} ORDER BY reach DESC LIMIT 20`, P));
      let typeByPub = {};
      if (H.media_types && H.media_type_connections) {
        const tv = await qm(siteId,
          `SELECT ${SR.pubTitle} AS name, mt.name AS mediaType, COUNT(*) AS n
             FROM articles a JOIN ${SR.PUB} p ON p.id = a.publication_id
             JOIN media_type_connections mtc ON mtc.taggable_id = a.id AND mtc.taggable_type ${MORPH_ART}
             JOIN media_types mt ON mt.id = mtc.media_type_id
            WHERE ${ACTIVE} AND ${BETWEEN} GROUP BY ${SR.pubTitle}, mt.name`, P).catch(() => []);
        for (const r of tv) {
          if (!typeByPub[r.name] || r.n > typeByPub[r.name].n) typeByPub[r.name] = { t: r.mediaType, n: r.n };
        }
      }
      return pubs.map((p) => ({
        name: p.name, articles: Number(p.articles) || 0, reach: fmtReach(p.reach),
        mediaType: typeByPub[p.name]?.t ? String(typeByPub[p.name].t).toLowerCase() : null,
        tier: Number(p.keyPub) ? 'key' : null,
      }));
    }).then((v) => (out.topPublications = v)));
  }

  if (hasC('contact_id') && SR.ctOk) {
    jobs.push(soft('journalists', () => qm(siteId,
      `SELECT ${SR.ctName} AS name,
              COUNT(*) AS articles, ${REACH} AS reach
         FROM articles a JOIN ${SR.CT} ct ON ct.id = a.contact_id
        WHERE ${ACTIVE} AND ${BETWEEN}
        GROUP BY ${SR.ctName} ORDER BY articles DESC LIMIT 12`, P))
      .then((v) => (out.topJournalists = v.map((x) => ({ ...x, articles: Number(x.articles) || 0, reach: fmtReach(x.reach) })))));
  }

  // Article thumbnail straight from the tenant's own media library —
  // articles.media_file_path points at nova_media_library (folder + name).
  // The public URL prefix comes from site.monitoring.mediaBaseUrl (e.g.
  // "https://<tenant>.mynewspress.com/storage") — without it we return the
  // relative path and the report simply renders image-less cards.
  const hasImg = hasC('media_file_path') && H.nova_media_library;
  const IMG_SEL = hasImg ? `, (SELECT CONCAT(nm.folder, nm.name) FROM nova_media_library nm WHERE nm.id = CAST(a.media_file_path AS UNSIGNED) AND a.media_file_path REGEXP '^[0-9]+$' LIMIT 1) AS imagePath` : '';
  const mediaBase = String(site.monitoring?.mediaBaseUrl || '').replace(/\/+$/, '');
  const withImage = (x) => ({
    ...x, sentiment: canonSent(x.sentiment),
    image: x.imagePath && mediaBase ? mediaBase + x.imagePath : null,
    imagePath: undefined,
  });
  jobs.push(soft('topArticles', () => qm(siteId,
    `SELECT ${TITLE} AS title, ${hasC('publication_id') && SR.pubOk ? SR.pubTitle : "''"} AS publication,
            DATE(a.published_at) AS date, ${REACH1} AS reach, ${hasC('link') ? 'a.link' : 'NULL'} AS url,
            ${hasC('sentiment_id') && H.sentiments ? 's.name' : 'NULL'} AS sentiment,
            ${hasC('market_id') && H.markets ? 'mk.name' : 'NULL'} AS market${IMG_SEL}
       FROM articles a
       ${hasC('publication_id') && SR.pubOk ? `LEFT JOIN ${SR.PUB} p ON p.id = a.publication_id` : ''}
       ${hasC('sentiment_id') && H.sentiments ? 'LEFT JOIN sentiments s ON s.id = a.sentiment_id' : ''}
       ${hasC('market_id') && H.markets ? 'LEFT JOIN markets mk ON mk.id = a.market_id' : ''}
      WHERE ${ACTIVE} AND ${BETWEEN}${BRAND_ONLY} ORDER BY ${hasC('reach') ? 'a.reach' : 'a.published_at'} DESC LIMIT 30`, P))
    .then((v) => (out.topArticles = v.map(withImage))));

  // Share of voice — the tenant's competitors pivot first (article_competitor
  // rows are attached at import time — the authoritative line-up), then the
  // clients pivot, then the Nova "competitors" setting matched on headlines.
  jobs.push(soft('shareOfVoice', async () => {
    // 0) competitors + article_competitor — the REAL competitor tagging
    //    (Ferrari: 174 competitors, 36k article links). Brand articles =
    //    articles with NO competitor link, so the pie always includes the
    //    client itself alongside the competitors.
    if (H.competitors && H.article_competitor) {
      const comp = await qm(siteId,
        `SELECT TRIM(c2.name) AS name, COUNT(DISTINCT a.id) AS articles, ${REACH} AS reach
           FROM articles a JOIN article_competitor ac2 ON ac2.article_id = a.id
           JOIN competitors c2 ON c2.id = ac2.competitor_id
          WHERE ${ACTIVE} AND ${BETWEEN} AND TRIM(COALESCE(c2.name,'')) <> ''
          GROUP BY TRIM(c2.name) ORDER BY reach DESC, articles DESC LIMIT 9`, P).catch(() => []);
      if (comp.length) {
        const own = await qm(siteId,
          `SELECT COUNT(DISTINCT a.id) AS articles, ${REACH} AS reach
             FROM articles a
            WHERE ${ACTIVE} AND ${BETWEEN}
              AND NOT EXISTS (SELECT 1 FROM article_competitor ac3 WHERE ac3.article_id = a.id)`, P)
          .then((v) => v[0]).catch(() => null);
        const rows = comp.map((x) => ({ name: x.name, articles: Number(x.articles) || 0, reach: fmtReach(x.reach) }));
        if (own && Number(own.articles) > 0) {
          rows.unshift({ name: site.name || 'Brand', articles: Number(own.articles) || 0, reach: fmtReach(own.reach), isBrand: true });
        }
        return rows;
      }
    }
    // 1) The clients pivot — brands attached to articles at import time.
    let pivot = [];
    if (H.clients && H.article_client) {
      pivot = await qm(siteId,
        `SELECT c2.name, COUNT(DISTINCT a.id) AS articles, ${REACH} AS reach
           FROM articles a JOIN article_client ac ON ac.article_id = a.id
           JOIN clients c2 ON c2.id = ac.client_id
          WHERE ${ACTIVE} AND ${BETWEEN} GROUP BY c2.name ORDER BY reach DESC LIMIT 10`, P).catch(() => []);
      pivot = pivot.map((x) => ({ name: x.name, articles: Number(x.articles) || 0, reach: fmtReach(x.reach) }))
        .filter((x) => x.name && !/^\d+$/.test(String(x.name).trim())); // id-like "names" are junk rows
      if (pivot.length > 1) return pivot; // real competitor line-up already
    }
    // 2) Competitor names from the Nova "competitors" setting — matched
    //    against headlines/preview text. Handles every storage shape seen in
    //    the wild: [["Porsche"],...], ["Porsche",...], [{"name":"Porsche"},...]
    //    and double-encoded JSON strings.
    let names = [];
    if (H.nova_settings) {
      const rows = await qm(siteId, `SELECT \`value\` FROM nova_settings WHERE \`key\` = 'competitors' LIMIT 1`).catch(() => []);
      let raw = rows[0]?.value;
      // Competitor lists are stored in many shapes: [["Porsche"]], [[2,"Porsche"]],
      // [{key:2,value:"Porsche"}], {"2":"Porsche"}, ["Porsche"], double-encoded…
      // A NAME is a non-numeric string — never pick the id/key (a "competitor"
      // called "2" would LIKE-match every headline containing a 2).
      const isName = (s) => typeof s === 'string' && s.trim().length >= 2 && !/^\d+$/.test(s.trim());
      const pickName = (row) => {
        if (row == null) return null;
        if (typeof row === 'string') return isName(row) ? row : null;
        if (Array.isArray(row)) { for (const x of row) { const n = pickName(x); if (n) return n; } return null; }
        if (typeof row === 'object') {
          for (const k of ['name', 'label', 'title', 'brand', 'value', 'competitor']) {
            if (isName(row[k])) return row[k];
          }
          for (const v2 of Object.values(row)) { const n = pickName(v2); if (n) return n; }
        }
        return null;
      };
      try {
        let parsed = typeof raw === 'string' ? JSON.parse(raw) : raw;
        if (typeof parsed === 'string') parsed = JSON.parse(parsed); // double-encoded
        const items = Array.isArray(parsed) ? parsed
          : (parsed && typeof parsed === 'object') ? Object.values(parsed) : [];
        for (const row of items) {
          const n = pickName(row);
          if (n) names.push(n.trim());
        }
      } catch { /* not JSON */ }
    }
    if (names.length) {
      const own = getSite(siteId).name || 'This brand';
      const comps = await Promise.all([...new Set(names)].slice(0, 8).map(async (n) => {
        const [m] = await qm(siteId,
          `SELECT COUNT(*) AS articles, ${REACH} AS reach FROM articles a
            WHERE ${ACTIVE} AND ${BETWEEN} AND (${TITLE} LIKE ?${hasC('preview_text') ? ' OR a.preview_text LIKE ?' : ''})`,
          [...P, `%${n}%`, ...(hasC('preview_text') ? [`%${n}%`] : [])]).catch(() => [{}]);
        return { name: n, articles: Number(m?.articles) || 0, reach: fmtReach(m?.reach) };
      }));
      // The own brand ALWAYS anchors the chart — its whole tracked coverage —
      // so competitors line up against it even when their counts are small.
      const ownRow = { name: own, articles: out.totals?.articles || 0, reach: out.totals?.reach || 0 };
      const rows2 = [ownRow, ...comps.filter((x) => x.articles > 0 && x.name.toLowerCase() !== own.toLowerCase())];
      if (rows2.length > 1) return rows2.sort((a2, b2) => (b2.reach || b2.articles) - (a2.reach || a2.articles));
    }
    return pivot; // single-brand pivot (or empty) — better than nothing
  }).then((v) => (out.shareOfVoice = v)));

  // Release → coverage, straight from press_release_id (no fuzzy matching),
  // enriched with the platform-side engagement for the same release title.
  jobs.push(soft('releaseCoverage', async () => {
    const norm = (s) => String(s || '').toLowerCase().replace(/[^a-z0-9 ]+/g, ' ').replace(/\s+/g, ' ').trim();
    const toks = (s) => new Set(norm(s).split(' ').filter((x) => x.length >= 4));
    const overlap = (a2, b2) => { let n = 0; for (const t2 of a2) if (b2.has(t2)) n++; return n / Math.max(1, Math.min(a2.size, b2.size)); };
    const platRels = await releasesByDate(siteId, rangeStr, 200).catch(() => []);
    const top = [...platRels].sort((a2, b2) => (b2.viewed || 0) - (a2.viewed || 0)).slice(0, 8);
    // 1) exact attribution via press_release_id (only articles imported with a
    //    group_by get this link — many tenants barely use it)
    let cov = [];
    if (hasC('press_release_id') && H.press_releases) {
      cov = await qm(siteId,
        `SELECT pr.id, pr.title, COUNT(*) AS articles, ${REACH} AS reach
           FROM articles a JOIN press_releases pr ON pr.id = a.press_release_id
          WHERE ${ACTIVE} AND ${BETWEEN} GROUP BY pr.id, pr.title ORDER BY reach DESC LIMIT 10`, P).catch(() => []);
    }
    const byPlat = new Map(); // platform release id → coverage
    const orphan = [];
    for (const cvr of cov) {
      const ct = toks(cvr.title);
      const match = platRels.find((r) => overlap(ct, toks(r.title)) >= 0.5);
      if (match) byPlat.set(String(match.id), { articles: Number(cvr.articles) || 0, reach: fmtReach(cvr.reach) });
      else orphan.push({ releaseId: null, title: cvr.title, engagements: null, articles: Number(cvr.articles) || 0, reach: fmtReach(cvr.reach) });
    }
    // 2) fallback for every top platform release still unmatched: search the
    //    coverage HEADLINES for the release's distinctive words (brand words
    //    like "KGM Motors UK" excluded — they'd match everything).
    const STOP = new Set(['the', 'and', 'with', 'from', 'for', 'new', 'all', 'joins', 'into', 'this', 'that', 'launches', 'official', 'returns', '2025', '2026']);
    const brand = new Set(norm(getSite(siteId).name || '').split(' ').filter(Boolean));
    const needFallback = top.filter((r) => !byPlat.has(String(r.id)));
    await Promise.all(needFallback.map(async (r) => {
      const t2 = norm(r.title).split(' ')
        .filter((x) => x.length >= 4 && !STOP.has(x) && !brand.has(x))
        .sort((a2, b2) => b2.length - a2.length).slice(0, 2);
      if (!t2.length) return;
      const [m] = await qm(siteId,
        `SELECT COUNT(*) AS articles, ${REACH} AS reach FROM articles a
          WHERE ${ACTIVE} AND ${BETWEEN} AND ${t2.map(() => `${TITLE} LIKE ?`).join(' AND ')}`,
        [...P, ...t2.map((x) => `%${x}%`)]).catch(() => [{}]);
      if (Number(m?.articles) > 0) byPlat.set(String(r.id), { articles: Number(m.articles) || 0, reach: fmtReach(m.reach) });
    }));
    const rows = top.map((r) => {
      const c2 = byPlat.get(String(r.id));
      return {
        releaseId: r.id, title: r.title, engagements: Number(r.viewed) || 0,
        articles: c2?.articles || 0, reach: c2?.reach || 0,
      };
    });
    return [...rows, ...orphan].slice(0, 12);
  }).then((v) => (out.releaseCoverage = v)));

  // Market picker — every market with its coverage count in the period,
  // UNFILTERED by the current selection so the popover always lists all
  // options. Works even without a `markets` table (raw market_id fallback).
  if (hasC('market_id')) {
    jobs.push(soft('markets', async () => {
      const RAW_BETWEEN = 'a2.published_at BETWEEN ? AND ?';
      const RAW_P = [startDate, endDate + ' 23:59:59'];
      if (H.markets) {
        // ALIAS-MERGED picker list: duplicate market rows ("UK" + "United
        // Kingdom") collapse into one entry carrying ALL their ids and the
        // named region of any member. Markets with ZERO articles in the
        // period are dropped — an empty market is only noise in the picker.
        const [counts, mmap] = await Promise.all([
          qm(siteId,
            `SELECT a2.market_id AS mid, COUNT(*) AS n FROM articles a2
              WHERE a2.market_id IS NOT NULL AND ${RAW_BETWEEN} GROUP BY a2.market_id`, RAW_P).catch(() => []),
          marketRegionMap(siteId, H),
        ]);
        const byMid = Object.fromEntries(counts.map((c2) => [Number(c2.mid), Number(c2.n) || 0]));
        const merged = mmap.groups.map((g) => ({
          id: g.ids.join(','), name: g.name || 'Market ' + g.ids[0], region: g.region,
          articles: g.ids.reduce((a2, id) => a2 + (byMid[id] || 0), 0),
        })).filter((g) => g.articles > 0)
          .sort((a2, b2) => b2.articles - a2.articles || String(a2.name).localeCompare(String(b2.name)));
        if (merged.length) return merged;
      }
      // No markets table (or it's empty) — the distinct ids on the articles
      // themselves still make a usable filter.
      const rows = await qm(siteId,
        `SELECT a2.market_id AS id, COUNT(*) AS articles FROM articles a2
          WHERE a2.market_id IS NOT NULL AND ${RAW_BETWEEN}
          GROUP BY a2.market_id ORDER BY articles DESC LIMIT 100`, RAW_P);
      return rows.map((r) => ({ id: r.id, name: 'Market ' + r.id, region: null, articles: Number(r.articles) || 0 }));
    }).then((v) => (out.markets = v)));
  }

  await Promise.all(jobs);

  // ── Highlights must be BRAND stories ──────────────────────────────────
  // article_competitor tagging is filtered in-query, but tenants tag
  // inconsistently — a Porsche road test that nobody tagged still ranks by
  // raw reach. Second guard: drop a highlight whose HEADLINE names a
  // competitor (from the share-of-voice set) without naming the brand.
  // Competitors keep their place in Share of Voice — just not in the
  // client's own highlight cards.
  try {
    const brandTok = String(getSite(siteId).name || '').toLowerCase().split(/[^a-z0-9]+/)
      .filter((w) => w.length >= 4 && !['europe', 'north', 'south', 'east', 'west', 'group', 'motor', 'motors'].includes(w));
    const modelTok = (out.models || []).map((m2) => String(m2.name).toLowerCase()).filter((w) => w.length >= 4);
    const compTok = [...new Set((out.shareOfVoice || [])
      .map((c2) => String(c2.name).toLowerCase())
      .filter((n2) => !brandTok.some((b2) => n2.includes(b2)))
      .flatMap((n2) => n2.split(/[^a-z0-9]+/))
      .filter((w) => w.length >= 4 && !['motor', 'motors', 'group', 'cars', 'auto'].includes(w)))];
    if (compTok.length && brandTok.length) {
      const isBrandStory = (a2) => {
        const t2 = String(a2.title || '').toLowerCase();
        const hasBrand = brandTok.some((b2) => t2.includes(b2)) || modelTok.some((m2) => t2.includes(m2));
        const hasComp = compTok.some((c2) => t2.includes(c2));
        return hasBrand || !hasComp; // competitor-only headlines are out
      };
      const filt = (arr) => {
        const kept = (arr || []).filter(isBrandStory);
        return kept.length >= Math.min(3, (arr || []).length) ? kept : arr; // never empty the list
      };
      if (out.topArticles?.length) out.topArticles = filt(out.topArticles).slice(0, 15);
      if (out.regionTopArticles) {
        for (const k2 of Object.keys(out.regionTopArticles)) {
          out.regionTopArticles[k2] = filt(out.regionTopArticles[k2]).slice(0, 2);
        }
      }
    } else if (out.regionTopArticles) {
      for (const k2 of Object.keys(out.regionTopArticles)) out.regionTopArticles[k2] = (out.regionTopArticles[k2] || []).slice(0, 2);
    }
  } catch { /* highlight filtering is best-effort */ }
  return out;
}

const fmtReach = (n) => Number(n) || 0;

// Deterministic demo data so demo sites showcase the monitoring section.
function demoMonitoring(siteId, startDate, endDate, marketId = null) {
  const mkList = String(marketId || '').split(',').filter(Boolean).join(',');
  const seed = [...String(siteId) + (mkList ? '|m' + mkList : '')].reduce((a, c) => (a * 31 + c.charCodeAt(0)) >>> 0, 7);
  let a = seed; const r = () => { a = (a * 1664525 + 1013904223) >>> 0; return a / 4294967296; };
  const days = [];
  const d0 = new Date(startDate + 'T00:00:00Z'), d1 = new Date(endDate + 'T00:00:00Z');
  for (let d = new Date(d0); d <= d1; d.setUTCDate(d.getUTCDate() + 1)) {
    const articles = 20 + Math.floor(r() * 90);
    days.push({ date: d.toISOString().slice(0, 10), articles, reach: articles * (120000 + Math.floor(r() * 400000)) });
  }
  const tot = days.reduce((acc, x) => ({ articles: acc.articles + x.articles, reach: acc.reach + x.reach }), { articles: 0, reach: 0 });
  const mk = (names, base) => names.map((name, i) => ({
    name,
    articles: Math.max(1, Math.floor(base * Math.max(0.04, 1 - i * 0.16) * (0.8 + r() * 0.4))),
    reach: Math.max(50000, Math.floor(base * Math.max(0.04, 1 - i * 0.14) * (0.9 + r() * 0.4) * 220000)),
  })).sort((x, y) => y.reach - x.reach);
  const REGIONS = ['South Europe', 'West Europe', 'Central Europe', 'East Europe', 'North Europe'];
  const sentiment = [
    { name: 'positive', articles: Math.floor(tot.articles * 0.55), reach: Math.floor(tot.reach * 0.48) },
    { name: 'neutral', articles: Math.floor(tot.articles * 0.36), reach: Math.floor(tot.reach * 0.47) },
    { name: 'negative', articles: Math.floor(tot.articles * 0.09), reach: Math.floor(tot.reach * 0.05) },
  ];
  const ART_TITLES = [
    'New flagship electric SUV incoming: everything we know', 'First drive: the plug-in hybrid surprises everyone',
    'Concept debut steals the show at Goodwood', 'Record UK sales as electrified models surge',
    'Design chief interview: the next decade of the brand', 'Track test: the fastest version yet',
    'Best-selling cars this month — one clear winner', 'Behind the wheel of the new EV before launch',
  ];
  const PUBS = ['The Sun', 'bild.de', 'The Independent', 'Daily Mail', 'Auto Express', 'TechRadar', 'Top Gear', 'GB News'];
  return {
    enabled: true, demo: true,
    schema: { table: 'articles (demo)', cols: Object.fromEntries(Object.keys(COLS).map((k) => [k, k])) },
    totals: {
      ...tot,
      prevArticles: Math.floor(tot.articles * (0.8 + r() * 0.5)), prevReach: Math.floor(tot.reach * (0.8 + r() * 0.5)),
      publications: 320 + (seed % 300),
      ave: Math.floor(tot.reach * 0.0135), prevAve: Math.floor(tot.reach * 0.0135 * (0.8 + r() * 0.5)),
      shares: Math.floor(tot.articles * (2 + r() * 4)),
    },
    daily: days,
    regions: mk(REGIONS, tot.articles / 4),
    marketSplit: (() => {
      const ms = mk(['United Kingdom', 'Germany', 'Italy', 'France', 'Spain'], tot.articles / 4);
      const totR = ms.reduce((a2, x) => a2 + x.reach, 0) || 1;
      return ms.map((x, i) => ({ ...x, region: ['North Europe', 'Central Europe', 'South Europe', 'West Europe', 'West Europe'][i] || null, reachShare: Math.round((100 * x.reach) / totR) }));
    })(),
    topics: mk(['Product', 'Corporate', 'Racing', 'Partnership', 'Review', 'Event'], tot.articles / 4),
    models: mk(['Luce', '12Cilindri', '499P', 'Purosangue', 'SF90 XX'], tot.articles / 6),
    mediaTypes: mk(['online', 'print', 'broadcast'], tot.articles / 2.2),
    sentiment,
    prevSentiment: sentiment.map((s) => ({ name: s.name, articles: Math.floor(s.articles * (0.8 + r() * 0.4)) })),
    sentimentByRegion: REGIONS.flatMap((region) => [
      { region, sentiment: 'positive', articles: 20 + Math.floor(r() * 300) },
      { region, sentiment: 'neutral', articles: 10 + Math.floor(r() * 200) },
      { region, sentiment: 'negative', articles: Math.floor(r() * 35) },
    ]),
    topPublications: mk(['bild.de', 'The Sun', 'Corriere della Sera', 'L\'Équipe', 'Auto Bild', 'Marca', 'The Independent', 'Top Gear', 'Quattroruote', 'Autocar'], tot.articles / 14)
      .map((p, i) => ({ ...p, mediaType: i % 3 === 1 ? 'print' : 'online', tier: i < 6 ? 'top' : 'standard' })),
    topJournalists: mk(['Steve Fowler', 'Leon Poultney', 'Felix Reeves', 'Rob Hull', 'Mark Tisshaw', 'John Murdoch'], tot.articles / 16),
    shareOfVoice: mk(['This brand', 'Porsche', 'Lamborghini', 'Bentley', 'Aston Martin'], tot.articles / 3),
    topArticles: ART_TITLES.map((title, i) => ({
      title, publication: PUBS[i % PUBS.length],
      date: days[Math.floor(r() * days.length)]?.date || startDate,
      reach: Math.floor((0.9 - i * 0.09) * 14000000 * (0.7 + r() * 0.6)),
      url: 'https://example.com/article-' + (i + 1),
      sentiment: i === 6 ? 'neutral' : 'positive',
      market: (() => {
        // When market(s) are selected the demo articles stay inside the selection.
        const names = { 1: 'UK', 2: 'Germany', 3: 'Italy', 4: 'France', 5: 'Spain' };
        const sel = mkList ? mkList.split(',').map((x) => names[Number(x)]).filter(Boolean) : null;
        const all = ['UK', 'Germany', 'Italy', 'Spain', 'France'];
        return (sel && sel.length ? sel : all)[i % (sel && sel.length ? sel.length : all.length)];
      })(),
    })),
    releaseCoverage: ART_TITLES.slice(0, 5).map((t2, i) => ({
      releaseId: 1000 + i, title: t2, engagements: 900 + Math.floor(r() * 8000),
      articles: i === 4 ? 0 : 3 + Math.floor(r() * 34),
      reach: i === 4 ? 0 : Math.floor((1 + r() * 10) * 1e6),
    })),
    regionTopArticles: Object.fromEntries(REGIONS.map((rg, ri) => [rg, ART_TITLES.slice(ri % 4, (ri % 4) + 2).map((t2, i2) => ({
      region: rg, title: t2, publication: PUBS[(ri + i2) % PUBS.length],
      date: days[Math.floor(r() * days.length)]?.date || startDate,
      reach: Math.floor((0.8 - i2 * 0.2) * 9000000 * (0.7 + r() * 0.6)),
      url: 'https://example.com/article-r' + ri + '-' + i2,
    }))])),
    markets: [
      // '1,27' mirrors a real MERGED market (Ferrari's UK + United Kingdom
      // alias rows) so the picker's merged-id handling is exercised in demo.
      { id: '1,27', name: 'United Kingdom', region: 'North Europe', articles: 480 + (seed % 200) }, { id: 2, name: 'Germany', region: 'Central Europe', articles: 400 + (seed % 150) },
      { id: 3, name: 'Italy', region: 'South Europe', articles: 350 + (seed % 130) }, { id: 4, name: 'France', region: 'West Europe', articles: 260 + (seed % 90) },
      { id: 5, name: 'Spain', region: 'West Europe', articles: 190 + (seed % 70) },
    ],
    marketId: mkList && !mkList.includes(',') ? Number(mkList) : null,
    marketIds: mkList ? mkList.split(',').map(Number) : [],
    errors: [],
  };
}

// Full rankings behind the monitoring graphs — powers the "View all" popups
// (publications / journalists / models / topics), with the market filter
// applied and big limits so the popup can search & paginate the whole thing.
export async function monitoringList(siteId, rangeStr, what, { marketId = null, limit = 500 } = {}) {
  const site = getSite(siteId);
  const { startDate, endDate } = resolveRange(rangeStr);
  const lim = Math.max(1, Math.min(1000, Number(limit) || 500));
  if (what === 'articles') {
    // The full article list behind "Highlight articles" — reuse the drill.
    const r = await monitoringArticles(siteId, rangeStr, { marketId, limit: lim });
    return r.articles || [];
  }
  const realMon = monitoringConfigured(site) && !site.monitoring?.demo && process.env.DEMO_MODE !== '1';
  if (!realMon) {
    const d = demoMonitoring(siteId, startDate, endDate, marketId);
    const seed = [...String(siteId + what)].reduce((a, c) => (a * 31 + c.charCodeAt(0)) >>> 0, 7);
    let rows = { publications: d.topPublications, journalists: d.topJournalists, models: d.models, topics: d.topics, regions: d.regions, marketSplit: d.marketSplit }[what] || [];
    // Widen the demo lists so pagination/search show their worth
    if (what === 'publications') rows = rows.concat(Array.from({ length: 60 }, (_, i) => ({ name: ['Autocar', 'CarExpert', 'Motor1', 'AutoTrader', 'WhatCar', 'PistonHeads', 'CAR Magazine', 'EVO', 'Drive', 'Wheels'][i % 10] + ' ' + (2 + Math.floor(i / 10)), articles: Math.max(1, 40 - i), reach: (60 - i) * 90000 + (seed % 50000), mediaType: i % 3 ? 'online' : 'print' })));
    if (what === 'journalists') rows = rows.concat(Array.from({ length: 50 }, (_, i) => ({ name: ['Alex Ward', 'Sam Cole', 'Jo Fisher', 'Chris Lane', 'Pat Reed'][i % 5] + ' ' + (2 + Math.floor(i / 5)), articles: Math.max(1, 30 - i), reach: (50 - i) * 60000 })));
    return rows.slice(0, lim);
  }
  const s = await monitoringSchema(siteId);
  if (s.kind !== 'mynews') return [];
  const H = s.has;
  const A = s.artCols || new Set();
  const hasC = (c) => A.has(c);
  const probe = await artProbe(siteId, hasC('status'));
  const ACTIVE = hasC('status') && !(probe.total > 0 && probe.active === 0)
    ? "(a.status = 'active' OR a.status IS NULL OR a.status = '')" : '1=1';
  const mktIds = hasC('market_id') ? String(marketId || '').split(',').map(Number).filter(Boolean) : [];
  const MKT = mktIds.length ? ` AND a.market_id IN (${mktIds.map(() => '?').join(',')})` : '';
  const BETWEEN = 'a.published_at BETWEEN ? AND ?' + MKT;
  const P = [startDate, endDate + ' 23:59:59', ...mktIds];
  const REACH = hasC('reach') ? 'SUM(a.reach)' : 'NULL';
  const num = (v) => v.map((x) => ({ ...x, articles: Number(x.articles) || 0, reach: fmtReach(x.reach) }));
  try {
    const SRl = await sharedLookupRefs(siteId, H);
    if (what === 'publications' && hasC('publication_id') && SRl.pubOk) {
      return num(await qm(siteId,
        `SELECT ${SRl.pubTitle} AS name, COUNT(*) AS articles, ${REACH} AS reach
           FROM articles a JOIN ${SRl.PUB} p ON p.id = a.publication_id
          WHERE ${ACTIVE} AND ${BETWEEN} GROUP BY p.id, ${SRl.pubTitle} ORDER BY articles DESC LIMIT ${lim}`, P));
    }
    if (what === 'journalists' && hasC('contact_id') && SRl.ctOk) {
      return num(await qm(siteId,
        `SELECT ${SRl.ctName} AS name,
                COUNT(*) AS articles, ${REACH} AS reach
           FROM articles a JOIN ${SRl.CT} ct ON ct.id = a.contact_id
          WHERE ${ACTIVE} AND ${BETWEEN} GROUP BY ${SRl.ctName} ORDER BY articles DESC LIMIT ${lim}`, P));
    }
    if ((what === 'models' || what === 'topics') && H.tags && H.taggables) {
      // Local groups, SHARED central tags (Ferrari), category_mapping, or a
      // parent-tag tree — same auto-detection as the overview cards.
      return num(await tagBreakdown(siteId, H, what === 'models' ? 'models' : 'topics',
        { ACTIVE, BETWEEN, P, REACH, morph: "LIKE '%Article'", lim }));
    }
    if ((what === 'regions' || what === 'marketSplit') && hasC('market_id') && H.markets) {
      // Same ALIAS-MERGED aggregation as the overview cards — the "View all"
      // popup must agree with the chart, listed regions only.
      const [rows, mmap] = await Promise.all([
        qm(siteId,
          `SELECT a.market_id AS mid, COUNT(*) AS articles, ${REACH} AS reach
             FROM articles a WHERE ${ACTIVE} AND ${BETWEEN} GROUP BY a.market_id`, P),
        marketRegionMap(siteId, H),
      ]);
      const agg = new Map();
      for (const r of rows) {
        const g = r.mid != null ? mmap.byId.get(Number(r.mid)) : null;
        const key = what === 'regions'
          ? (g?.region || 'Unassigned')
          : (g?.name || (r.mid == null ? 'No market set' : 'Market ' + r.mid));
        const a2 = agg.get(key) || { name: key, articles: 0, reach: 0 };
        a2.articles += Number(r.articles) || 0;
        a2.reach += Number(r.reach) || 0;
        agg.set(key, a2);
      }
      return [...agg.values()].filter((x) => x.articles > 0)
        .sort((x, y) => y.reach - x.reach || y.articles - x.articles).slice(0, lim);
    }
  } catch { return []; }
  return [];
}

export async function monitoringOverview(siteId, rangeStr, marketId = null) {
  const site = getSite(siteId);
  const { startDate, endDate } = resolveRange(rangeStr);
  const realMon = monitoringConfigured(site) && !site.monitoring?.demo && process.env.DEMO_MODE !== '1';
  if (!realMon) {
    if (isDemo(site) || site.monitoring?.demo) return demoMonitoring(siteId, startDate, endDate, marketId);
    return { enabled: false };
  }

  const s = await monitoringSchema(siteId); // throws with table list when nothing matches
  // The real myNewspress production schema gets its dedicated adapter —
  // proper joins to sentiments/publications/contacts/regions/tags/clients.
  if (s.kind === 'mynews') return myNewsOverview(siteId, rangeStr, s, marketId);

  const out = { enabled: true, errors: [] };
  const soft = async (label, fn, fb = []) => { try { return await fn(); } catch (e) { out.errors.push(`${label}: ${String(e.message || e).slice(0, 160)}`); return fb; } };
  out.schema = { table: s.table, cols: s.cols };
  const c = s.cols;
  if (!c.date) throw new Error(`Articles table "${s.table}" has no recognisable date column (columns: ${s.allColumns.join(', ')})`);
  const T = '`' + s.table + '`';
  const D = '`' + c.date + '`';
  const between = `${D} BETWEEN ? AND ?`;
  const P = [startDate, endDate + ' 23:59:59'];
  const prev = previousRange({ startDate, endDate });
  const PP = [prev.startDate, prev.endDate + ' 23:59:59'];
  const reachSel = c.reach ? `SUM(\`${c.reach}\`)` : 'NULL';
  const aveSel = c.ave ? `SUM(\`${c.ave}\`)` : 'NULL';
  const sharesSel = c.shares ? `SUM(\`${c.shares}\`)` : 'NULL';

  const [totCur, totPrev] = await Promise.all([
    soft('totals', () => qm(siteId, `SELECT COUNT(*) AS articles, ${reachSel} AS reach, ${aveSel} AS ave, ${sharesSel} AS shares${c.publication ? `, COUNT(DISTINCT \`${c.publication}\`) AS publications` : ''} FROM ${T} WHERE ${between}`, P), [{}]),
    soft('previous', () => qm(siteId, `SELECT COUNT(*) AS articles, ${reachSel} AS reach, ${aveSel} AS ave FROM ${T} WHERE ${between}`, PP), [{}]),
  ]);
  out.totals = {
    articles: Number(totCur[0]?.articles) || 0, reach: fmtReach(totCur[0]?.reach),
    ave: Number(totCur[0]?.ave) || null, shares: Number(totCur[0]?.shares) || null,
    publications: Number(totCur[0]?.publications) || null,
    prevArticles: Number(totPrev[0]?.articles) || 0, prevReach: fmtReach(totPrev[0]?.reach),
    prevAve: Number(totPrev[0]?.ave) || null,
  };

  const group = (col, label, extra = '') => soft(label, () => qm(siteId,
    `SELECT \`${col}\` AS name, COUNT(*) AS articles, ${reachSel} AS reach FROM ${T}
      WHERE ${between} AND \`${col}\` IS NOT NULL AND \`${col}\` != '' ${extra}
      GROUP BY \`${col}\` ORDER BY ${c.reach ? 'reach' : 'articles'} DESC LIMIT 12`, P));

  const jobs = [];
  jobs.push(soft('daily', () => qm(siteId,
    `SELECT DATE(${D}) AS date, COUNT(*) AS articles, ${reachSel} AS reach FROM ${T}
      WHERE ${between} GROUP BY DATE(${D}) ORDER BY date`, P)).then((v) => (out.daily = v)));
  if (c.region) jobs.push(group(c.region, 'regions').then((v) => (out.regions = v)));
  else if (c.country) jobs.push(group(c.country, 'countries').then((v) => (out.regions = v)));
  if (c.topic) jobs.push(group(c.topic, 'topics').then((v) => (out.topics = v)));
  if (c.model) jobs.push(group(c.model, 'models').then((v) => (out.models = v)));
  if (c.sentiment) jobs.push(group(c.sentiment, 'sentiment').then((v) => (out.sentiment = v)));
  if (c.sentiment) jobs.push(soft('prevSentiment', () => qm(siteId,
    `SELECT \`${c.sentiment}\` AS name, COUNT(*) AS articles FROM ${T}
      WHERE ${between} AND \`${c.sentiment}\` IS NOT NULL GROUP BY \`${c.sentiment}\``, PP))
    .then((v) => (out.prevSentiment = v)));
  if (c.brand) jobs.push(group(c.brand, 'shareOfVoice').then((v) => (out.shareOfVoice = v)));
  if (c.mediaType) jobs.push(group(c.mediaType, 'mediaTypes').then((v) => (out.mediaTypes = v)));
  if (c.journalist) jobs.push(group(c.journalist, 'journalists').then((v) => (out.topJournalists = v)));
  if (c.publication) jobs.push(soft('publications', () => qm(siteId,
    `SELECT \`${c.publication}\` AS name, COUNT(*) AS articles, ${reachSel} AS reach
            ${c.mediaType ? `, MAX(\`${c.mediaType}\`) AS mediaType` : ''}${c.tier ? `, MAX(\`${c.tier}\`) AS tier` : ''}
       FROM ${T} WHERE ${between} AND \`${c.publication}\` IS NOT NULL AND \`${c.publication}\` != ''
      GROUP BY \`${c.publication}\` ORDER BY ${c.reach ? 'reach' : 'articles'} DESC LIMIT 12`, P))
    .then((v) => (out.topPublications = v)));
  if (c.sentiment && (c.region || c.country)) {
    const rc = c.region || c.country;
    jobs.push(soft('sentimentByRegion', () => qm(siteId,
      `SELECT \`${rc}\` AS region, \`${c.sentiment}\` AS sentiment, COUNT(*) AS articles FROM ${T}
        WHERE ${between} AND \`${rc}\` IS NOT NULL AND \`${rc}\` != ''
        GROUP BY \`${rc}\`, \`${c.sentiment}\` ORDER BY \`${rc}\``, P)).then((v) => (out.sentimentByRegion = v)));
  }
  // Top articles by reach — the "which publications picked up the story" list.
  if (c.title) jobs.push(soft('topArticles', () => qm(siteId,
    `SELECT \`${c.title}\` AS title, ${c.publication ? `\`${c.publication}\`` : "''"} AS publication,
            DATE(${D}) AS date${c.reach ? `, \`${c.reach}\` AS reach` : ''}${c.url ? `, \`${c.url}\` AS url` : ''}${c.sentiment ? `, \`${c.sentiment}\` AS sentiment` : ''}
       FROM ${T} WHERE ${between} ORDER BY ${c.reach ? `\`${c.reach}\`` : D} DESC LIMIT 15`, P))
    .then((v) => (out.topArticles = v)));
  // Press-release engagement vs coverage reach — ties the newspress side to
  // the monitoring side: for each top release, how much coverage did it earn?
  if (c.title) jobs.push(soft('releaseCoverage', async () => {
    const rels = (await releasesByDate(siteId, rangeStr, 200).catch(() => []))
      .sort((a, b) => (b.viewed || 0) - (a.viewed || 0)).slice(0, 8);
    if (!rels.length) return [];
    const STOP = new Set(['the', 'and', 'with', 'from', 'for', 'new', 'release', 'press', '2025', '2026']);
    const rows = await Promise.all(rels.map(async (r) => {
      const toks = String(r.title || '').toLowerCase().split(/[^a-z0-9]+/)
        .filter((t2) => t2.length >= 4 && !STOP.has(t2))
        .sort((a, b) => b.length - a.length).slice(0, 2);
      if (!toks.length) return null;
      const [m] = await qm(siteId,
        `SELECT COUNT(*) AS articles, ${reachSel} AS reach FROM ${T}
          WHERE ${between} AND ${toks.map(() => `\`${c.title}\` LIKE ?`).join(' AND ')}`,
        [...P, ...toks.map((t2) => `%${t2}%`)]).catch(() => [{}]);
      return {
        releaseId: r.id, title: r.title, engagements: Number(r.viewed) || 0,
        articles: Number(m?.articles) || 0, reach: fmtReach(m?.reach),
      };
    }));
    return rows.filter(Boolean);
  }).then((v) => (out.releaseCoverage = v)));
  if (H.markets && hasC('market_id')) {
    jobs.push(soft('markets', async () => {
      // Every market with its coverage volume in the period — unfiltered by
      // the current selection, so the picker always shows all options.
      const rows = await qm(siteId,
        `SELECT mk.id, mk.name${H.market_region && H.regions ? ', (SELECT r.name FROM market_region mr JOIN regions r ON r.id = mr.region_id WHERE mr.market_id = mk.id LIMIT 1) AS region' : ''},
                (SELECT COUNT(*) FROM articles a2 WHERE a2.market_id = mk.id AND a2.published_at BETWEEN ? AND ?) AS articles
           FROM markets mk ORDER BY articles DESC, mk.name`, [startDate, endDate + ' 23:59:59']);
      return rows;
    }).then((v) => (out.markets = v)));
  }
  await Promise.all(jobs);
  return out;
}

// ── article drill-down: the rows behind ANY monitoring graph segment ────
// kind ∈ region | market | topic | model | mediaType | sentiment |
//        publication | journalist | brand | day — value is the clicked label.
export async function monitoringArticles(siteId, rangeStr, { marketId = null, kind = null, value = null, limit = 100, brandOnly = false } = {}) {
  const site = getSite(siteId);
  const { startDate, endDate } = resolveRange(rangeStr);
  const lim = Math.max(1, Math.min(200, Number(limit) || 100));
  const realMonA = monitoringConfigured(site) && !site.monitoring?.demo && process.env.DEMO_MODE !== '1';
  if (!realMonA) {
    const d = demoMonitoring(siteId, startDate, endDate, marketId);
    return {
      kind, value,
      articles: (d.topArticles || []).map((a, i) => ({ ...a, reach: Math.floor(a.reach * (0.6 + (i % 4) * 0.12)) })),
    };
  }
  const s = await monitoringSchema(siteId);
  if (s.kind !== 'mynews') {
    // Generic flat-column adapter: filter on the mapped column when it exists
    const c = s.cols;
    const T = '`' + s.table + '`';
    const D = '`' + c.date + '`';
    const where = [`${D} BETWEEN ? AND ?`];
    const params = [startDate, endDate + ' 23:59:59'];
    const colFor = { region: c.region || c.country, market: c.country, topic: c.topic, model: c.model, mediaType: c.mediaType, sentiment: c.sentiment, publication: c.publication, journalist: c.journalist, brand: c.brand };
    if (kind === 'day' && value) { where.push(`DATE(${D}) = ?`); params.push(value); }
    else if (kind && colFor[kind] && value) { where.push(`\`${colFor[kind]}\` = ?`); params.push(value); }
    const rows = await qm(siteId,
      `SELECT \`${c.title}\` AS title, ${c.publication ? `\`${c.publication}\`` : "''"} AS publication,
              DATE(${D}) AS date${c.reach ? `, \`${c.reach}\` AS reach` : ', 0 AS reach'}${c.url ? `, \`${c.url}\` AS url` : ''}${c.sentiment ? `, \`${c.sentiment}\` AS sentiment` : ''}
         FROM ${T} WHERE ${where.join(' AND ')} ORDER BY ${c.reach ? `\`${c.reach}\`` : D} DESC LIMIT ${lim}`, params).catch(() => []);
    return { kind, value, articles: rows.map((x) => ({ ...x, sentiment: normSent(x.sentiment) })) };
  }
  // myNewspress schema — proper joins per kind
  const A = s.artCols || new Set();
  const hasC = (c2) => A.has(c2);
  const H = s.has;
  const TITLE = 'a.`' + (s.cols.title || 'headline') + '`';
  const ACTIVE = hasC('status') ? "(a.status = 'active' OR a.status IS NULL OR a.status = '')" : '1=1';
  const joins = [];
  const where = [ACTIVE, 'a.published_at BETWEEN ? AND ?'];
  const params = [startDate, endDate + ' 23:59:59'];
  const mktIds2 = hasC('market_id') ? String(marketId || '').split(',').map(Number).filter(Boolean) : [];
  if (mktIds2.length) { where.push(`a.market_id IN (${mktIds2.map(() => '?').join(',')})`); params.push(...mktIds2); }
  // Brand-owned coverage only (used by the AI evidence pack, so commentary
  // never cites a competitor's story as the client's driver). Dashboard
  // click-throughs keep the unfiltered view to match their chart counts.
  if (brandOnly && H.competitors && H.article_competitor && kind !== 'brand') {
    where.push('NOT EXISTS (SELECT 1 FROM article_competitor acx WHERE acx.article_id = a.id)');
  }
  const SRd = await sharedLookupRefs(siteId, H);
  if (hasC('publication_id') && SRd.pubOk) joins.push(`LEFT JOIN ${SRd.PUB} p ON p.id = a.publication_id`);
  if (hasC('sentiment_id') && H.sentiments) joins.push('LEFT JOIN sentiments sn ON sn.id = a.sentiment_id');
  if (hasC('market_id') && H.markets) joins.push('LEFT JOIN markets mk ON mk.id = a.market_id');
  const v = value == null ? '' : String(value);
  switch (kind) {
    case 'day': where.push('DATE(a.published_at) = ?'); params.push(v); break;
    case 'market': {
      // Alias-aware: drilling "United Kingdom" must include the articles on
      // the "UK" alias row (they share a merged market entry).
      if (hasC('market_id') && H.markets) {
        const mmapM = await marketRegionMap(siteId, H);
        const grp = mmapM.groups.find((g) => String(g.name).toLowerCase() === String(v).toLowerCase());
        if (grp?.ids?.length) {
          where.push(`a.market_id IN (${grp.ids.map(() => '?').join(',')})`);
          params.push(...grp.ids);
          break;
        }
      }
      where.push('mk.name = ?'); params.push(v); break;
    }
    case 'region':
      if (hasC('market_id') && H.markets) {
        // Same ALIAS-MERGED map as the charts — the drill for "Northern
        // Europe" includes the "UK" alias rows, and "Unassigned" returns
        // exactly the unmapped markets (plus articles with no market).
        const mmap = await marketRegionMap(siteId, H);
        const ids = [];
        for (const g of mmap.groups) {
          if (v === 'Unassigned' ? !g.region : g.region === v) ids.push(...g.ids);
        }
        if (v === 'Unassigned') {
          where.push(ids.length ? `(a.market_id IS NULL OR a.market_id IN (${ids.map(() => '?').join(',')}))` : 'a.market_id IS NULL');
          params.push(...ids);
        } else if (ids.length) {
          where.push(`a.market_id IN (${ids.map(() => '?').join(',')})`);
          params.push(...ids);
        } else { where.push('1 = 0'); }
      } else { where.push('mk.name = ?'); params.push(v); }
      break;
    case 'sentiment':
      where.push("(LOWER(sn.name) = LOWER(?) OR LOWER(sn.name) = LOWER(REPLACE(?, ' ', '')))");
      params.push(v, v); break;
    case 'publication': where.push(`${SRd.pubTitle} = ?`); params.push(v); break;
    case 'journalist':
      if (hasC('contact_id') && SRd.ctOk) {
        joins.push(`LEFT JOIN ${SRd.CT} ct ON ct.id = a.contact_id`);
        where.push(`${SRd.ctName} = ?`);
        params.push(v);
      }
      break;
    case 'model': {
      if (H.tags && H.taggables) {
        const shT = await sharedTagsInfo(siteId, H);
        const { T: TT, extra: tEx } = tagRefs(shT);
        where.push(`a.id IN (SELECT tg.taggable_id FROM taggables tg JOIN ${TT} t ON t.id = tg.tag_id${tEx} WHERE tg.taggable_type LIKE '%Article' AND t.name = ?)`);
        params.push(v);
      }
      break;
    }
    case 'mediaType':
      if (H.media_types && H.media_type_connections) {
        where.push(`a.id IN (SELECT mtc.taggable_id FROM media_type_connections mtc JOIN media_types mt ON mt.id = mtc.media_type_id WHERE mtc.taggable_type LIKE '%Article' AND mt.name = ?)`);
        params.push(v);
      }
      break;
    case 'brand':
      if (H.clients && H.article_client) {
        where.push('a.id IN (SELECT ac.article_id FROM article_client ac JOIN clients c2 ON c2.id = ac.client_id WHERE c2.name = ?)');
        params.push(v);
      } else { where.push(`(${TITLE} LIKE ? ${hasC('preview_text') ? 'OR a.preview_text LIKE ?' : ''})`); params.push(`%${v}%`); if (hasC('preview_text')) params.push(`%${v}%`); }
      break;
    case 'topic': {
      if (v === 'Press release' && hasC('press_release_id')) { where.push('a.press_release_id IS NOT NULL'); break; }
      if (v === 'Press fleet' && H.article_booking) { where.push('a.id IN (SELECT article_id FROM article_booking)'); break; }
      if (v === 'Other' && hasC('media_focus')) {
        where.push(`(a.media_focus IS NULL OR TRIM(a.media_focus) = '')${hasC('press_release_id') ? ' AND a.press_release_id IS NULL' : ''}`);
        break;
      }
      // Categories can come from media_focus, plain tags, OR the central
      // category_mapping — match whichever holds this value (OR-combined, so
      // the drill always finds the same articles the chart counted).
      const legs = [];
      if (H.tags && H.taggables) {
        const shT = await sharedTagsInfo(siteId, H);
        const { T: TT, extra: tEx } = tagRefs(shT);
        let idList = null;
        if (shT?.catMap) {
          const maps = await qm(siteId,
            `SELECT tag_ids FROM \`${shT.db}\`.category_mapping WHERE TRIM(category) = ?`, [v]).catch(() => []);
          const nums = [];
          for (const m of maps) {
            let ids = [];
            try { ids = JSON.parse(m.tag_ids || '[]'); } catch { ids = String(m.tag_ids || '').split(/[\s,]+/); }
            nums.push(...(Array.isArray(ids) ? ids : []).map(Number).filter((n) => Number.isFinite(n) && n > 0));
          }
          if (nums.length) idList = [...new Set(nums)].slice(0, 2000).join(',');
        }
        if (idList) {
          legs.push(`a.id IN (SELECT tg.taggable_id FROM taggables tg WHERE tg.taggable_type LIKE '%Article' AND tg.tag_id IN (${idList}))`);
        } else {
          legs.push(`a.id IN (SELECT tg.taggable_id FROM taggables tg JOIN ${TT} t ON t.id = tg.tag_id${tEx} WHERE tg.taggable_type LIKE '%Article' AND t.name = ?)`);
          params.push(v);
        }
      }
      if (hasC('media_focus')) { legs.push('TRIM(a.media_focus) = ?'); params.push(v); }
      if (legs.length) where.push('(' + legs.join(' OR ') + ')');
      break;
    }
    default: break; // no extra filter — full list
  }
  const rows = await qm(siteId,
    `SELECT ${TITLE} AS title, ${hasC('publication_id') && SRd.pubOk ? SRd.pubTitle : "''"} AS publication,
            DATE(a.published_at) AS date, ${hasC('reach') ? 'a.reach' : '0'} AS reach,
            ${hasC('link') ? 'a.link' : 'NULL'} AS url,
            ${hasC('sentiment_id') && H.sentiments ? 'sn.name' : 'NULL'} AS sentiment,
            ${hasC('market_id') && H.markets ? 'mk.name' : 'NULL'} AS market
       FROM articles a ${joins.join(' ')}
      WHERE ${where.join(' AND ')}
      ORDER BY ${hasC('reach') ? 'a.reach' : 'a.published_at'} DESC LIMIT ${lim}`, params).catch(() => []);
  return { kind, value, articles: rows.map((x) => ({ ...x, sentiment: normSent(x.sentiment) })) };
}

// ── computed intelligence: what's working, what's not, what to do next ──
// Pure function over the overview — powers the "myNewspress intelligence"
// card, the report section and the AI summary's fact pack.
export function monitoringInsights(ov) {
  const out = [];
  if (!ov?.totals) return out;
  const t = ov.totals;
  const pct = (a, b) => (b ? Math.round(((a - b) / b) * 100) : null);
  const push = (kind, text, action = null) => out.push({ kind, text, action });

  const dv = pct(t.articles, t.prevArticles);
  const dr = pct(t.reach, t.prevReach);
  if (dv != null && dr != null) {
    if (dv >= 10 && dr >= 10) push('up', `Coverage is growing on both fronts: volume ${dv > 0 ? '+' : ''}${dv}% and reach ${dr > 0 ? '+' : ''}${dr}% vs the previous period.`, 'Capture what drove this period (launches, events, exclusives) and repeat the cadence.');
    else if (dv >= 10 && dr < 0) push('watch', `More articles (+${dv}%) but LESS audience (${dr}%): coverage is shifting to smaller outlets.`, 'Prioritise the top-tier titles — offer exclusives or embargoed briefings to the biggest publications below.');
    else if (dv < 0 && dr >= 10) push('up', `Fewer articles (${dv}%) but reach is up ${dr}% — each story is landing in bigger outlets. Quality over quantity is working.`);
    else if (dv <= -10 && dr <= -10) push('down', `Coverage cooled: volume ${dv}% and reach ${dr}% vs the previous period.`, 'Check the content calendar — a quieter release schedule usually explains this; consider a news moment or press-fleet push.');
    else push('note', `Coverage broadly stable vs the previous period (volume ${dv > 0 ? '+' : ''}${dv}%, reach ${dr > 0 ? '+' : ''}${dr}%).`);
  }
  if (t.ave) push('note', `Estimated media value (AVE) this period: £${Math.round(t.ave).toLocaleString('en-GB')}${t.prevAve ? ` (${pct(t.ave, t.prevAve) > 0 ? '+' : ''}${pct(t.ave, t.prevAve)}% vs previous)` : ''}.`);

  const sent = ov.sentiment || [];
  const totS = sent.reduce((a, x) => a + (x.articles || 0), 0);
  if (totS) {
    const get = (n) => sent.find((x) => String(x.name).toLowerCase().includes(n));
    const posP = Math.round((100 * ((get('positive')?.articles || 0) + (get('very')?.articles || 0))) / totS);
    const neg = get('negative');
    const negP = Math.round((100 * (neg?.articles || 0)) / totS);
    if (posP >= 70) push('up', `Sentiment is strong: ${posP}% of coverage is positive.`);
    else if (posP >= 45) push('note', `Sentiment: ${posP}% positive, ${negP}% negative.`);
    if (neg?.articles && (negP >= 10 || (neg.reach && t.reach && neg.reach / t.reach >= 0.1))) {
      push('down', `Negative coverage is significant: ${fmtInt2(neg.articles)} articles (${negP}%)${neg.reach ? ` reaching ${kfmt2(neg.reach)} people` : ''}.`, 'Open the top negative articles below, identify the recurring criticism, and prepare a proactive response or corrective story.');
    } else if (!neg?.articles) {
      push('up', 'Zero negative articles recorded this period.');
    }
  }

  const sov = ov.shareOfVoice || [];
  if (sov.length >= 2) {
    const totV = sov.reduce((a, x) => a + (x.reach || x.articles || 0), 0) || 1;
    const first = sov[0], second = sov[1];
    const share = Math.round((100 * (first.reach || first.articles || 0)) / totV);
    push(share >= 50 ? 'up' : 'note',
      `Share of voice: ${first.name} leads with ${share}% of tracked reach, ahead of ${second.name}.`,
      share < 50 ? 'The field is competitive — a sustained product-news drumbeat protects the lead.' : null);
  }

  const regs = ov.regions || [];
  if (regs.length >= 2) {
    const top = regs[0], weak = regs[regs.length - 1];
    push('note', `${top.name} delivers the most coverage${top.reach ? ` (${kfmt2(top.reach)} reach)` : ''}; ${weak.name} is the quietest market.`, `A localised story or press-fleet loan in ${weak.name} would lift the weakest market.`);
  }

  const tops = ov.topics || [];
  if (tops.length >= 2) {
    const totT = tops.reduce((a, x) => a + (x.articles || 0), 0) || 1;
    const share = Math.round((100 * (tops[0].articles || 0)) / totT);
    if (share >= 60) push('watch', `Coverage is concentrated: ${share}% of articles are "${tops[0].name}".`, `Diversify the story mix — ${tops.slice(1, 3).map((x) => x.name).join(' and ')} are under-represented.`);
    else push('note', `Story mix is healthy: ${tops.slice(0, 3).map((x) => `${x.name} ${Math.round((100 * x.articles) / totT)}%`).join(' · ')}.`);
  }

  const mt = ov.mediaTypes || [];
  if (mt.length >= 2) {
    const totM = mt.reduce((a, x) => a + (x.articles || 0), 0) || 1;
    push('note', `Channel split: ${mt.map((x) => `${x.name} ${Math.round((100 * x.articles) / totM)}%`).join(' · ')}.`);
  }

  const rc = (ov.releaseCoverage || []).filter((r) => r);
  if (rc.length) {
    const picked = rc.filter((r) => r.articles > 0);
    const missed = rc.filter((r) => !r.articles);
    if (picked.length) {
      const best = [...picked].sort((a, b) => (b.reach || b.articles) - (a.reach || a.articles))[0];
      push('up', `Best-earning release: “${String(best.title).slice(0, 70)}” — ${fmtInt2(best.articles)} articles${best.reach ? `, ${kfmt2(best.reach)} reach` : ''}.`, 'This is the story angle journalists want — brief more like it.');
    }
    if (missed.length) {
      push('watch', `${missed.length} of your top ${rc.length} releases earned no tracked coverage (e.g. “${String(missed[0].title).slice(0, 60)}”).`, 'Re-pitch these with a stronger angle, better assets, or a targeted journalist list from the Top journalists card.');
    }
  }

  const arts = ov.topArticles || [];
  if (arts.length && arts[0].reach) {
    push('note', `Biggest single article: “${String(arts[0].title).slice(0, 70)}” in ${arts[0].publication || 'a top outlet'} (${kfmt2(arts[0].reach)} reach).`);
  }
  return out;
}
const fmtInt2 = (n) => Math.round(n || 0).toLocaleString('en-GB');
const kfmt2 = (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); };

// ── Evidence pack for the AI analyst ────────────────────────────────────
// The commentary must EXPLAIN movements, not just describe them — so for
// each headline number we fetch the actual stories behind it: what the top
// journalists wrote, which articles drove positive/negative sentiment,
// each big publication's stories, the leading headlines per category and
// model, and what ran on the peak coverage day. Built on the same drill
// queries the dashboard's click-throughs use (alias/shared-schema aware).
export async function monitoringEvidence(siteId, rangeStr, mon, marketId = null) {
  const out = {};
  const jobs = [];
  const clean = (a) => ({
    title: String(a.title || '').slice(0, 120),
    publication: a.publication || undefined,
    date: a.date ? String(a.date).slice(0, 10) : undefined,
    reach: a.reach || undefined,
    sentiment: a.sentiment || undefined,
  });
  const add = (key, label, kind, value, lim) => jobs.push(
    monitoringArticles(siteId, rangeStr, { marketId, kind, value, limit: lim, brandOnly: true })
      .then((r) => {
        const arts = (r.articles || []).slice(0, lim).map(clean).filter((a) => a.title);
        if (arts.length) (out[key] = out[key] || []).push({ [label]: value, articles: arts });
      })
      .catch(() => { /* evidence is best-effort */ }));
  const top = (arr, n) => (arr || []).slice(0, n);

  for (const s of top(mon.sentiment, 4)) {
    if (/positive|negative|neutral/i.test(String(s.name))) add('sentimentDrivers', 'sentiment', 'sentiment', s.name, 4);
  }
  for (const j of top(mon.topJournalists, 4)) add('whatTopJournalistsWrote', 'journalist', 'journalist', j.name, 3);
  for (const p of top(mon.topPublications, 5)) add('topPublicationStories', 'publication', 'publication', p.name, 3);
  for (const t of top(mon.topics, 5)) add('categoryDrivers', 'category', 'topic', t.name, 4);
  for (const m of top(mon.models, 4)) add('modelDrivers', 'model', 'model', m.name, 3);
  for (const mt of top(mon.mediaTypes, 3)) add('mediaTypeStories', 'mediaType', 'mediaType', mt.name, 3);
  const peaks = [...(mon.daily || [])]
    .sort((a, b) => (Number(b.reach) || Number(b.articles) || 0) - (Number(a.reach) || Number(a.articles) || 0))
    .slice(0, 3);
  for (const pk of peaks) if (pk?.date) add('peakDayStories', 'day', 'day', String(pk.date).slice(0, 10), 4);
  await Promise.all(jobs);
  return out;
}
