// Legacy reports-DB adapters — one per site kind, matching the schemas of the
// old Laravel warehouse:
//   newspress: companies / releases(company_id) / category_releases / media_releases
//              / emails / email_releases / banners / detail_* tables
//   newsroom : releases / release_categories / models / blogs / media / emails
// All queries are read-only SELECTs.

import fs from 'node:fs';
import path from 'node:path';
import { fileURLToPath } from 'node:url';
import { getSite, resolveRange, previousRange } from './ga4.js';
import { q, dbKind, dbIsDemo } from './db.js';
import { demoLegacyOverview, demoLegacyRelease } from './demo-legacy.js';
import { textMatchesMarkets, marketAliases, currentLocales } from './market.js';

const __dirname = path.dirname(fileURLToPath(import.meta.url));

// Union published-per-day and distributed-per-day rows into one date-sorted series.
function mergeCadence(days, dist) {
  const m = new Map();
  for (const r of days || []) {
    const k = String(r.date).slice(0, 10);
    m.set(k, { date: k, published: Number(r.published) || 0, distributed: 0 });
  }
  for (const r of dist || []) {
    const k = String(r.date).slice(0, 10);
    if (!m.has(k)) m.set(k, { date: k, published: 0, distributed: 0 });
    m.get(k).distributed = Number(r.distributed) || 0;
  }
  return [...m.values()].sort((a, b) => (a.date < b.date ? -1 : 1));
}


export { dbKind };

const MEDIA_TYPE = { 1: 'image', 2: 'video', 3: 'document', 0: 'misc' };

// Media asset names are often internal codes (EX0112, INT0312…) — the release
// the asset belongs to gives the human context. One-row correlated subquery.
const MREL = `(SELECT r2.title FROM media_releases mr2 JOIN releases r2 ON r2.id = mr2.release_id
                WHERE mr2.media_id = m.id ORDER BY r2.id DESC LIMIT 1) AS releaseTitle`;
// Videos are watched far more than they're downloaded — ranking assets purely
// by downloads hid them entirely ("top videos – no data").
const MRANK = 'GREATEST(COALESCE(m.downloaded, 0), COALESCE(m.viewed, 0))';

// LIVE-content filter for releases — BOTH kinds (Newspress + Newsroom).
// Only active releases count: never inactive (status), never soft-deleted
// (deleted_at), never embargoed/future-dated (published/embargo in the
// future). Columns are probed ONCE per DB so the fragment always matches the
// actual schema; `alias` ("r." etc.) keeps aliased queries unambiguous.
// If `status = 1` matches nothing while releases exist (a different status
// convention), the status leg is dropped instead of blanking every card.
const statusClauseCache = new Map();
async function relStatus(siteId, alias = '') {
  const site = getSite(siteId);
  if (!dbKind(site) || dbIsDemo(site)) return '';
  const key = siteId + '::' + JSON.stringify(site.db || {});
  let parts = statusClauseCache.get(key);
  if (!parts) {
    parts = [];
    try {
      const desc = await q(siteId, 'DESCRIBE releases');
      const cols = new Set(desc.map((d) => String(d.Field).toLowerCase()));
      if (cols.has('status')) {
        let keep = true;
        try {
          const [r] = await q(siteId, 'SELECT COUNT(*) AS n, COALESCE(SUM(status = 1), 0) AS s1 FROM releases');
          if (Number(r?.n) > 0 && !Number(r?.s1)) keep = false;
        } catch { /* keep */ }
        if (keep) parts.push('%A%status = 1');
      }
      if (cols.has('deleted_at')) parts.push('%A%deleted_at IS NULL');
      if (cols.has('published')) parts.push('%A%published <= NOW()'); // embargo = future publish date
      for (const c of ['embargo', 'embargo_date', 'embargo_until', 'embargoed_until', 'embargo_at'])
        if (cols.has(c)) parts.push(`(%A%${c} IS NULL OR %A%${c} <= NOW())`);
    } catch { /* table unreadable — no filter */ }
    statusClauseCache.set(key, parts);
  }
  if (!parts.length) return '';
  return parts.map((p) => p.replaceAll('%A%', alias)).join(' AND ') + ' AND ';
}

// Email counter sanity: the emails table's `opened`/`click` columns count
// EVENTS (one person can open many times — and Apple privacy prefetch
// inflates them further), which produced absurd 1000%+ / flat-100% "open
// rates". `initial_open` is the platform's UNIQUE-opens counter — prefer it,
// and always cap at the audience size so a rate can never exceed 100%.
function fixEmailCounts(e) {
  const base = Number(e.recipients) || Number(e.delivered) || 0;
  const uniq = e.initialOpen != null && e.initialOpen !== '' ? Number(e.initialOpen) : null;
  const rawOpens = Number(e.opened) || 0;
  // A populated initial_open wins; a 0 with real open events means the
  // platform never fills that column — fall back to capped events.
  const opens = uniq != null && (uniq > 0 || rawOpens === 0) ? uniq : rawOpens;
  return {
    ...e,
    openEvents: e.openEvents != null ? e.openEvents : rawOpens, // total open events, kept for reference
    opened: base ? Math.min(opens, base) : opens,        // unique opens, capped at audience
    clicked: base ? Math.min(Number(e.clicked) || 0, base) : Number(e.clicked) || 0,
  };
}

// Which column identifies the RECIPIENT in detail_email_statistics — it
// differs between platform versions (email / recipient / member_id…).
// Probed once per site and cached; null = table absent or no usable column.
const emailStatsColCache = new Map();
async function emailStatsIdCol(siteId) {
  const site = getSite(siteId);
  const key = siteId + '::' + JSON.stringify(site.db || {});
  if (emailStatsColCache.has(key)) return emailStatsColCache.get(key);
  let col = null;
  try {
    const cols = (await q(siteId, 'DESCRIBE detail_email_statistics')).map((c) => String(c.Field));
    col = ['email', 'recipient_email', 'recipient', 'member_id', 'user_id', 'contact_id'].find((c2) => cols.includes(c2)) || null;
  } catch { col = null; }
  emailStatsColCache.set(key, col);
  return col;
}

// Replace event-count opens/clicks with UNIQUE recipients per campaign from
// detail_email_statistics. The emails table's counters are EVENTS (one
// person opening five times counts five) — unique people is the honest
// number, and the only way an open rate below 100% can be computed at all.
// Mutates and returns rows; silently a no-op when the stats table is absent.
export async function applyUniqueOpens(siteId, rows, { idKey = 'campaignId', openKey = 'opened', clickKey = 'clicked' } = {}) {
  try {
    const site = getSite(siteId);
    if (dbIsDemo(site) || !rows?.length) return rows;
    const col = await emailStatsIdCol(siteId);
    if (!col) return rows;
    const ids = [...new Set(rows.map((r) => String(r[idKey] ?? '')).filter(Boolean))].slice(0, 120);
    if (!ids.length) return rows;
    const uniq = await q(siteId,
      `SELECT campaign_id AS id,
              COUNT(DISTINCT CASE WHEN event = 1 THEN \`${col}\` END) AS uOpens,
              COUNT(DISTINCT CASE WHEN event = 2 THEN \`${col}\` END) AS uClicks
         FROM detail_email_statistics
        WHERE campaign_id IN (${ids.map(() => '?').join(',')})
        GROUP BY campaign_id`, ids);
    const byId = new Map(uniq.map((u) => [String(u.id), u]));
    for (const r of rows) {
      const u = byId.get(String(r[idKey]));
      if (!u) continue;
      r.openEvents = Number(r[openKey]) || 0;
      r.clickEvents = Number(r[clickKey]) || 0;
      r[openKey] = Number(u.uOpens) || 0;
      r[clickKey] = Number(u.uClicks) || 0;
      r.uniqueCounts = true;
    }
  } catch { /* stats table not usable — capped counters stay */ }
  return rows;
}

// Same idea for the media table: live assets only.
const mediaLiveCache = new Map();
async function mediaLive(siteId, alias = 'm.') {
  const site = getSite(siteId);
  if (!dbKind(site) || dbIsDemo(site)) return '';
  const key = siteId + '::' + JSON.stringify(site.db || {});
  let parts = mediaLiveCache.get(key);
  if (!parts) {
    parts = [];
    try {
      const desc = await q(siteId, 'DESCRIBE media');
      const cols = new Set(desc.map((d) => String(d.Field).toLowerCase()));
      if (cols.has('status')) parts.push('%A%status = 1');
      if (cols.has('deleted_at')) parts.push('%A%deleted_at IS NULL');
      if (cols.has('published')) parts.push('(%A%published IS NULL OR %A%published <= NOW())');
    } catch { /* no filter */ }
    mediaLiveCache.set(key, parts);
  }
  if (!parts.length) return '';
  return ' AND ' + parts.map((p) => p.replaceAll('%A%', alias)).join(' AND ');
}

// ── Analytics market filter on the PLATFORM database ─────────────────────
// Probes the releases table ONCE for a market-style column (market_id /
// country_id / market / country / locale / region) plus a lookup table, and
// builds a WHERE fragment matching the selected GA country names via loose
// aliases (US/USA/Mexico/MX/México…). Sites whose platform DB isn't
// market-tagged simply get no filter — the UI says so honestly.
const relMarketCache = new Map();
async function relMarketInfo(siteId) {
  const site = getSite(siteId);
  const key = siteId + '::' + JSON.stringify(site.db || {});
  if (relMarketCache.has(key)) return relMarketCache.get(key);
  let info = null;
  try {
    const desc = await q(siteId, 'DESCRIBE releases');
    const cols = Object.fromEntries(desc.map((d) => [String(d.Field).toLowerCase(), String(d.Type).toLowerCase()]));
    const strCol = ['market', 'country', 'locale', 'region', 'language'].find((c) => cols[c] && /char|text|enum/.test(cols[c]));
    const idCol = ['market_id', 'country_id', 'region_id'].find((c) => cols[c]);
    if (idCol) {
      // find the lookup table + its labels
      const lookupTable = { market_id: 'markets', country_id: 'countries', region_id: 'regions' }[idCol];
      const rows = await q(siteId, `SELECT id, name FROM \`${lookupTable}\` LIMIT 200`).catch(() => null);
      if (rows && rows.length) info = { kind: 'id', col: idCol, lookup: rows.map((r) => ({ id: r.id, name: String(r.name || '') })) };
    }
    if (!info && strCol) info = { kind: 'str', col: strCol };
  } catch { /* no releases table / no access */ }
  relMarketCache.set(key, info);
  return info;
}

// → { where(alias), params } for the selected countries, or null.
export async function releaseMarketFilter(siteId, countries) {
  // The DB's own locale column beats any name matching when a locale
  // selection is active (?gml=…).
  const locs = currentLocales();
  if (locs) {
    try {
      const desc = await q(siteId, 'DESCRIBE releases');
      if (desc.some((d2) => String(d2.Field).toLowerCase() === 'locale')) {
        return { where: (a) => ` AND ${a}locale IN (${locs.map(() => '?').join(',')})`,
                 params: locs, meta: { column: 'locale', matched: locs.length } };
      }
    } catch { /* fall through */ }
  }
  if (!countries?.length) return null;
  const info = await relMarketInfo(siteId);
  if (!info) return null;
  if (info.kind === 'id') {
    const ids = info.lookup.filter((r) => textMatchesMarkets(r.name, countries)).map((r) => r.id);
    if (!ids.length) return null;
    return { where: (a) => ` AND ${a}${info.col} IN (${ids.map(() => '?').join(',')})`, params: ids, meta: { column: info.col, matched: ids.length } };
  }
  const aliases = countries.flatMap((c) => marketAliases(c)).filter((x) => x.length >= 2);
  if (!aliases.length) return null;
  return {
    where: (a) => ` AND (${aliases.map(() => `${a}${info.col} LIKE ?`).join(' OR ')})`,
    params: aliases.map((x) => `%${x}%`),
    meta: { column: info.col, matched: aliases.length },
  };
}

// ── Dated media engagement from the platform EVENT LOG ───────────────────
// Newsroom DBs keep detail_media_statistics: one dated row per media view /
// download (media_id, user_id, action, country, date_event) — the SOURCE the
// legacy reports use for "top image downloads in <year>". Probe once per DB:
// does the table exist, and which action codes does it use? (Convention
// throughout this schema: action 1 = view, 2 = download.)
const mediaEventsCache = new Map();
async function mediaEventsInfo(siteId) {
  const site = getSite(siteId);
  const key = siteId + '::' + JSON.stringify(site.db || {});
  if (mediaEventsCache.has(key)) return mediaEventsCache.get(key);
  let info = null;
  try {
    const desc = await q(siteId, 'DESCRIBE detail_media_statistics');
    const cols = new Set(desc.map((d) => String(d.Field).toLowerCase()));
    if (cols.has('media_id') && cols.has('action') && (cols.has('date_event') || cols.has('created_at'))) {
      const dateCol = cols.has('date_event') ? 'date_event' : 'created_at';
      const actions = await q(siteId,
        'SELECT action, COUNT(*) AS n FROM detail_media_statistics GROUP BY action').catch(() => []);
      info = { dateCol, hasCountry: cols.has('country'),
               actions: Object.fromEntries(actions.map((a) => [a.action, Number(a.n) || 0])) };
    }
  } catch { /* table absent — snapshots remain the dated source */ }
  mediaEventsCache.set(key, info);
  return info;
}
const hasMediaTitleCache = new Map();
async function hasMediaTitle(siteId) {
  if (hasMediaTitleCache.has(siteId)) return hasMediaTitleCache.get(siteId);
  let has = false;
  try {
    const desc = await q(siteId, 'DESCRIBE media');
    has = desc.some((d) => String(d.Field).toLowerCase() === 'title');
  } catch { /* keep false */ }
  hasMediaTitleCache.set(siteId, has);
  return has;
}
// Human display name: media.title carries "2026 Palisade | Hybrid Calligraphy";
// media.name is the file code (EX0112). Prefer the title, keep the code.
const MNAME = (withTitle) => withTitle
  ? "COALESCE(NULLIF(TRIM(m.title), ''), m.name)"
  : 'm.name';

// Dated per-asset ranking straight from the event log — full history.
export async function mediaDownloadsDated(siteId, rangeStr, { type = null, limit = 25 } = {}) {
  const site = getSite(siteId);
  if (!dbKind(site) || dbIsDemo(site)) return null;
  const info = await mediaEventsInfo(siteId);
  if (!info) return null;
  const { startDate, endDate } = resolveRange(rangeStr || '28d');
  const withTitle = await hasMediaTitle(siteId);
  const TYPE_ID = { image: 1, video: 2, document: 3 };
  const mcols = await mediaCols(siteId);
  const lwm = mcols.has('locale') ? localeWhere('m.') : { where: '', params: [] };
  const tw = (type && TYPE_ID[type] ? ` AND m.type = ${TYPE_ID[type]}` : '') + lwm.where + await mediaLive(siteId);
  const lim = Math.max(1, Math.min(100, Number(limit) || 25));
  const D = [startDate, endDate + ' 23:59:59', ...lwm.params];
  const [rows, totals, byCountry] = await Promise.all([
    q(siteId,
      `SELECT s.media_id AS id,
              MAX(${MNAME(withTitle)}) AS name, MAX(m.name) AS code, MAX(m.type) AS typeId,
              SUM(s.action = 2) AS downloads, SUM(s.action = 1) AS views
         FROM detail_media_statistics s JOIN media m ON m.id = s.media_id
        WHERE s.\`${info.dateCol}\` BETWEEN ? AND ?${tw}
        GROUP BY s.media_id ORDER BY downloads DESC, views DESC LIMIT ${lim}`, D),
    q(siteId,
      `SELECT SUM(s.action = 2) AS downloads, SUM(s.action = 1) AS views, COUNT(DISTINCT s.media_id) AS assets
         FROM detail_media_statistics s JOIN media m ON m.id = s.media_id
        WHERE s.\`${info.dateCol}\` BETWEEN ? AND ?${tw}`, D).then((v) => v[0] || {}),
    info.hasCountry ? q(siteId,
      `SELECT s.country, SUM(s.action = 2) AS downloads
         FROM detail_media_statistics s JOIN media m ON m.id = s.media_id
        WHERE s.\`${info.dateCol}\` BETWEEN ? AND ?${tw} AND s.country IS NOT NULL AND s.country <> ''
        GROUP BY s.country ORDER BY downloads DESC LIMIT 12`, D).catch(() => []) : Promise.resolve([]),
  ]);
  // Optional splits when the taxonomy tables exist
  const tables = await siteTables(siteId);
  const [byCategory, byModel] = await Promise.all([
    tables.has('category_media') && tables.has('categories') ? q(siteId,
      `SELECT c.name, SUM(s.action = 2) AS downloads
         FROM detail_media_statistics s
         JOIN category_media cm ON cm.media_id = s.media_id
         JOIN categories c ON c.id = cm.category_id JOIN media m ON m.id = s.media_id
        WHERE s.\`${info.dateCol}\` BETWEEN ? AND ?${tw}
        GROUP BY c.name ORDER BY downloads DESC LIMIT 10`, D).catch(() => []) : Promise.resolve([]),
    tables.has('media_model') && tables.has('models') ? q(siteId,
      `SELECT mo.name, SUM(s.action = 2) AS downloads
         FROM detail_media_statistics s
         JOIN media_model mm ON mm.media_id = s.media_id
         JOIN models mo ON mo.id = mm.model_id JOIN media m ON m.id = s.media_id
        WHERE s.\`${info.dateCol}\` BETWEEN ? AND ?${tw}
        GROUP BY mo.name ORDER BY downloads DESC LIMIT 10`, D).catch(() => []) : Promise.resolve([]),
  ]);
  return {
    source: 'platform-event-log',
    period: { startDate, endDate },
    actionCodes: info.actions,
    rows: rows.map((r) => ({ id: r.id, name: r.name, code: r.code !== r.name ? r.code : null,
      type: MEDIA_TYPE[r.typeId] || 'misc', downloads: Number(r.downloads) || 0, views: Number(r.views) || 0 })),
    totals: { downloads: Number(totals.downloads) || 0, views: Number(totals.views) || 0, assets: Number(totals.assets) || 0 },
    byCountry: byCountry.map((c) => ({ country: c.country, downloads: Number(c.downloads) || 0 })),
    byCategory: byCategory.map((c) => ({ category: c.name, downloads: Number(c.downloads) || 0 })),
    byModel: byModel.map((c) => ({ model: c.name, downloads: Number(c.downloads) || 0 })),
  };
}

// Dated media list shaped like the lifetime rows (viewed/downloaded keys),
// so cards and View-all popups swap sources without UI changes.
export async function datedMediaList(siteId, rangeStr, type = null, limit = 15) {
  const dl = await mediaDownloadsDated(siteId, rangeStr, { type, limit }).catch(() => null);
  if (!dl) return null;
  return dl.rows.map((r) => ({ id: r.id, name: r.name, code: r.code, type: r.type,
    viewed: r.views, downloaded: r.downloads }));
}

// ONE event-log scan serving the whole overview (media card, videos,
// documents): top assets of every type in a single pass, split in JS.
// The event table has no date index, so each scan is expensive — one beats
// four, and the query cache + in-flight dedupe make repeats free.
export async function datedMediaOverview(siteId, rangeStr) {
  const site = getSite(siteId);
  if (!dbKind(site) || dbIsDemo(site)) return null;
  const info = await mediaEventsInfo(siteId);
  if (!info) return null;
  const { startDate, endDate } = resolveRange(rangeStr || '28d');
  const withTitle = await hasMediaTitle(siteId);
  // Same market filter as mediaDownloadsDated — the pool must obey the
  // selected market, not just the dates.
  const mcols = await mediaCols(siteId);
  const lwm = mcols.has('locale') ? localeWhere('m.') : { where: '', params: [] };
  const rows = await q(siteId,
    `SELECT s.media_id AS id, MAX(${MNAME(withTitle)}) AS name, MAX(m.name) AS code, MAX(m.type) AS typeId,
            SUM(s.action = 2) AS downloads, SUM(s.action = 1) AS views
       FROM detail_media_statistics s JOIN media m ON m.id = s.media_id
      WHERE s.\`${info.dateCol}\` BETWEEN ? AND ?${lwm.where}${await mediaLive(siteId)}
      GROUP BY s.media_id
      ORDER BY GREATEST(SUM(s.action = 2), SUM(s.action = 1)) DESC LIMIT 150`,
    [startDate, endDate + ' 23:59:59', ...lwm.params]).catch(() => null);
  if (!rows) return null;
  return rows.map((r) => ({ id: r.id, name: r.name, code: r.code !== r.name ? r.code : null,
    type: MEDIA_TYPE[r.typeId] || 'misc',
    viewed: Number(r.views) || 0, downloaded: Number(r.downloads) || 0 }));
}

// Platform media downloads/views per YEAR by type (Jan 1 → today's month-day
// in each year, so years compare fairly) — the numbers behind the old
// "Top image downloads in 2025 (148,765 total)" report, now live.
export async function mediaYearPlatform(siteId) {
  const site = getSite(siteId);
  if (!dbKind(site) || dbIsDemo(site)) return null;
  const info = await mediaEventsInfo(siteId);
  if (!info) return null;
  const today = new Date();
  const thisYear = today.getUTCFullYear();
  const cut = today.toISOString().slice(4, 10); // -MM-DD
  const out = {};
  for (const y of [thisYear - 1, thisYear]) {
    const rows = await q(siteId,
      `SELECT m.type AS typeId, SUM(s.action = 2) AS downloads, SUM(s.action = 1) AS views
         FROM detail_media_statistics s JOIN media m ON m.id = s.media_id
        WHERE s.\`${info.dateCol}\` BETWEEN ? AND ? GROUP BY m.type`,
      [`${y}-01-01`, `${y}${cut} 23:59:59`]).catch(() => []);
    const yr = {};
    for (const r of rows) {
      const t = MEDIA_TYPE[r.typeId] || 'misc';
      yr[t + 'Downloads'] = Number(r.downloads) || 0;
      yr[t + 'Views'] = Number(r.views) || 0;
    }
    out[y] = yr;
  }
  return { source: 'platform-event-log', comparedThrough: cut.slice(1), years: out };
}

// Cached SHOW TABLES per site — cheap existence checks for optional tables.
const siteTablesCache = new Map();
async function siteTables(siteId) {
  const site = getSite(siteId);
  const key = siteId + '::' + JSON.stringify(site.db || {});
  if (siteTablesCache.has(key)) return siteTablesCache.get(key);
  let names = new Set();
  try {
    const rows = await q(siteId, 'SHOW TABLES');
    names = new Set(rows.map((r) => String(Object.values(r)[0]).toLowerCase()));
  } catch { /* leave empty */ }
  siteTablesCache.set(key, names);
  return names;
}
// Generic probe for the detail_*_statistics event tables.
const detailInfoCache = new Map();
async function detailStatsInfo(siteId, table) {
  const site = getSite(siteId);
  const key = siteId + '::' + table + '::' + JSON.stringify(site.db || {});
  if (detailInfoCache.has(key)) return detailInfoCache.get(key);
  let info = null;
  try {
    const desc = await q(siteId, 'DESCRIBE `' + table + '`');
    const cols = new Set(desc.map((d) => String(d.Field).toLowerCase()));
    if (cols.has('action') || cols.has('event')) {
      info = {
        dateCol: cols.has('date_event') ? 'date_event' : 'created_at',
        hasCountry: cols.has('country'), hasUser: cols.has('user_id'),
      };
    }
  } catch { /* table absent */ }
  detailInfoCache.set(key, info);
  return info;
}

// Which release↔media pivot does this DB use? Hyundai newsrooms say
// `release_media`; newspress says `media_releases`. Probing fixes a silent
// gap where release-title lookups failed on one naming or the other.
async function mediaRelPivot(siteId) {
  const tables = await siteTables(siteId);
  return tables.has('media_releases') ? 'media_releases' : tables.has('release_media') ? 'release_media' : null;
}
const MREL_FOR = (pivot) => pivot
  ? `(SELECT r2.title FROM \`${pivot}\` mr2 JOIN releases r2 ON r2.id = mr2.release_id
       WHERE mr2.media_id = m.id ORDER BY r2.id DESC LIMIT 1) AS releaseTitle`
  : 'NULL AS releaseTitle';
const mediaColsCache = new Map();
async function mediaCols(siteId) {
  if (mediaColsCache.has(siteId)) return mediaColsCache.get(siteId);
  let cols = new Set();
  try { cols = new Set((await q(siteId, 'DESCRIBE media')).map((d) => String(d.Field).toLowerCase())); } catch { /* none */ }
  mediaColsCache.set(siteId, cols);
  return cols;
}

// ── Platform LOCALES = the DB's own market column ────────────────────────
// releases/media/models carry `locale` (1, 2, …). Each locale's display name
// is derived from its readers' dominant country in the event log — so locale
// 1 shows as "United States", locale 2 as "Mexico" — with a plain fallback.
// Lightweight language detection over release titles — fully dynamic
// labelling for ANY number of locales, no lookup table required.
const LANG_SIGNALS = [
  ['Spanish', /[áéíóúñ¡¿]|\b(el|la|los|las|del|para|con|nuevo|nueva|más|año|vehículo|llega|presenta)\b/i],
  ['French', /[àâçèêëîôûœ]|\b(le|les|des|une|avec|nouveau|nouvelle|voiture|dévoile|année)\b/i],
  ['German', /[äöüß]|\b(der|die|das|und|mit|für|neue|neuer|fahrzeug|jahr)\b/i],
  ['Italian', /\b(il|gli|della|con|nuovo|nuova|anno|presenta|veicolo)\b/i],
  ['Portuguese', /[ãõç]|\b(o|os|das|com|novo|nova|ano|veículo|chega)\b/i],
  ['Dutch', /\b(de|het|een|met|nieuwe|jaar|voertuig)\b/i],
  ['Polish', /[ąćęłńśźż]/i],
  ['Turkish', /[ğşıİ]/i],
  ['Korean', /[\uac00-\ud7af]/],
  ['Japanese', /[\u3040-\u30ff]/],
  ['Chinese', /[\u4e00-\u9fff]/],
  ['Arabic', /[\u0600-\u06ff]/],
  ['Russian', /[\u0400-\u04ff]/],
];
function detectLanguage(titles) {
  const text = titles.join(' ').replace(/<[^>]+>/g, ' ');
  if (!text.trim()) return null;
  let best = null, bestScore = 0;
  for (const [lang, rx] of LANG_SIGNALS) {
    const hits = (text.match(new RegExp(rx.source, rx.flags.includes('g') ? rx.flags : rx.flags + 'g')) || []).length;
    if (hits > bestScore) { best = lang; bestScore = hits; }
  }
  // English is the default; only claim another language on a real signal.
  return bestScore >= Math.max(3, titles.length / 3) ? best : 'English';
}

// Labels survive restarts (data/market-cache.json, refreshed daily) — the
// expensive reader-country + language derivation runs once a day at most,
// so the picker itself is always instant.
const localesCache = new Map();
const MARKET_CACHE_FILE = path.join(__dirname, '..', 'data', 'market-cache.json');
function readMarketDisk() {
  try { return JSON.parse(fs.readFileSync(MARKET_CACHE_FILE, 'utf8')); } catch { return {}; }
}
function writeMarketDisk(all) {
  try { fs.mkdirSync(path.dirname(MARKET_CACHE_FILE), { recursive: true }); fs.writeFileSync(MARKET_CACHE_FILE, JSON.stringify(all)); } catch { /* cache is best-effort */ }
}
export function clearLocalesCache() {
  localesCache.clear();
  writeMarketDisk({});
}
export async function platformLocales(siteId) {
  const site = getSite(siteId);
  const key = siteId + '::' + JSON.stringify(site.db || {}) + '::' + JSON.stringify(site.marketNames || {});
  if (localesCache.has(key)) return localesCache.get(key);
  let out = [];
  if (!dbKind(site)) { localesCache.set(key, out); return out; }
  // Disk cache (24 h): instant even right after a server restart.
  const disk = readMarketDisk();
  const hit = disk[key];
  if (hit && Date.now() - hit.t < 24 * 3600 * 1000) {
    localesCache.set(key, hit.out);
    return hit.out;
  }
  const named = site.marketNames || {}; // { "14": "United States", "35": "Mexico" } — set via the picker's rename
  if (dbIsDemo(site)) {
    out = [
      { locale: 1, releases: 320, country: 'United States', language: 'English', label: named['1'] || 'United States' },
      { locale: 2, releases: 84, country: 'United States', language: 'Spanish', label: named['2'] || 'United States — Spanish' },
      { locale: 3, releases: 61, country: 'Mexico', language: 'Spanish', label: named['3'] || 'Mexico' },
    ];
    localesCache.set(key, out);
    return out;
  }
  try {
    const desc = await q(siteId, 'DESCRIBE releases');
    if (!desc.some((d) => String(d.Field).toLowerCase() === 'locale')) { localesCache.set(key, []); return []; }
    const rows = await q(siteId,
      'SELECT locale, COUNT(*) AS n FROM releases WHERE locale IS NOT NULL GROUP BY locale ORDER BY n DESC LIMIT 50');
    if (rows.length < 2) { localesCache.set(key, []); return []; }
    const info = await detailStatsInfo(siteId, 'detail_release_statistics');
    out = await Promise.all(rows.map(async (r) => {
      let country = null, language = null;
      // Explicit name wins when set — otherwise everything is derived live.
      if (!named[String(r.locale)]) {
        try {
          // Two-step, index-friendly lookup (a direct join across millions of
          // event rows times out for big locales): recent release ids of this
          // locale first, then the reader countries of just those ids —
          // release_id is indexed, so this is fast at any scale.
          const recent = await q(siteId,
            'SELECT id, title FROM releases WHERE locale = ? ORDER BY id DESC LIMIT 60', [r.locale]);
          language = detectLanguage(recent.map((x) => String(x.title || '')).slice(0, 30));
          const ids = recent.map((x) => x.id).slice(0, 200);
          if (info?.hasCountry && ids.length) {
            const [top] = await q(siteId,
              `SELECT country, COUNT(*) AS n FROM detail_release_statistics
                WHERE release_id IN (${ids.map(() => '?').join(',')})
                  AND country IS NOT NULL AND country <> ''
                GROUP BY country ORDER BY n DESC LIMIT 1`, ids);
            country = top?.country || null;
          }
        } catch { /* label falls back */ }
      }
      return { locale: Number(r.locale), releases: Number(r.n) || 0, country, language,
               named: !!named[String(r.locale)], explicit: named[String(r.locale)] || null };
    }));
    // Dynamic label composition for ANY number of markets:
    //   country unique            → "United States"
    //   country shared, lang diff → "United States — Spanish"
    //   still colliding           → "(locale N)" suffix
    const countryCounts = new Map();
    for (const l of out) if (l.country) countryCounts.set(l.country, (countryCounts.get(l.country) || 0) + 1);
    for (const l of out) {
      if (l.explicit) { l.label = l.explicit; continue; }
      if (l.country && countryCounts.get(l.country) === 1) l.label = l.country;
      else if (l.country && l.language && l.language !== 'English') l.label = `${l.country} — ${l.language}`;
      else if (l.country) l.label = l.country;
      else if (l.language && l.language !== 'English') l.label = `${l.language} (locale ${l.locale})`;
      else l.label = `Market ${l.locale}`;
    }
    const seen = new Map();
    for (const l of out) {
      if (seen.has(l.label)) l.label = `${l.label} (locale ${l.locale})`;
      else seen.set(l.label, true);
    }
  } catch { out = []; }
  localesCache.set(key, out);
  const disk2 = readMarketDisk();
  disk2[key] = { t: Date.now(), out };
  writeMarketDisk(disk2);
  return out;
}
// Fragment factory for the ambient locale filter: ` AND alias.locale IN (…)`.
function localeWhere(alias) {
  const locs = currentLocales();
  if (!locs) return { where: '', params: [] };
  return { where: ` AND ${alias}locale IN (${locs.map(() => '?').join(',')})`, params: locs };
}
const relLocaleColCache = new Map();
async function hasReleaseLocale(siteId) {
  if (relLocaleColCache.has(siteId)) return relLocaleColCache.get(siteId);
  let has = false;
  try { has = (await q(siteId, 'DESCRIBE releases')).some((d) => String(d.Field).toLowerCase() === 'locale'); } catch { /* none */ }
  relLocaleColCache.set(siteId, has);
  return has;
}
// Guarded variants: no-op on DBs without the column.
async function relLocaleWhere(siteId, alias) {
  return (currentLocales() && await hasReleaseLocale(siteId)) ? localeWhere(alias) : { where: '', params: [] };
}
async function medLocaleWhere(siteId, alias) {
  return (currentLocales() && (await mediaCols(siteId)).has('locale')) ? localeWhere(alias) : { where: '', params: [] };
}

// ── Dated RELEASE views from the event log (period-true, with country) ────
// releases.viewed is a lifetime counter; detail_release_statistics gives the
// real per-period view counts and WHERE the readers are.
export async function releaseViewsDated(siteId, rangeStr, { limit = 50 } = {}) {
  const site = getSite(siteId);
  if (!dbKind(site) || dbIsDemo(site)) return null;
  const info = await detailStatsInfo(siteId, 'detail_release_statistics');
  if (!info) return null;
  const { startDate, endDate } = resolveRange(rangeStr || '28d');
  const lw = localeWhere('r.');
  const D = [startDate, endDate + ' 23:59:59', ...lw.params];
  const lim = Math.max(1, Math.min(200, Number(limit) || 50));
  const [rows, byCountry] = await Promise.all([
    q(siteId,
      `SELECT s.release_id AS id, MAX(r.title) AS title, COUNT(*) AS views
         FROM detail_release_statistics s JOIN releases r ON r.id = s.release_id
        WHERE s.\`${info.dateCol}\` BETWEEN ? AND ?${lw.where} GROUP BY s.release_id
        ORDER BY views DESC LIMIT ${lim}`, D),
    info.hasCountry ? q(siteId,
      `SELECT s.country, COUNT(*) AS views FROM detail_release_statistics s
         JOIN releases r ON r.id = s.release_id
        WHERE s.\`${info.dateCol}\` BETWEEN ? AND ?${lw.where} AND s.country IS NOT NULL AND s.country <> ''
        GROUP BY s.country ORDER BY views DESC LIMIT 12`, D).catch(() => []) : Promise.resolve([]),
  ]);
  return {
    source: 'platform-event-log', period: { startDate, endDate },
    rows: rows.map((r) => ({ id: r.id, title: r.title, views: Number(r.views) || 0 })),
    totalViews: rows.reduce((a, r) => a + (Number(r.views) || 0), 0),
    byCountry: byCountry.map((c) => ({ country: c.country, views: Number(c.views) || 0 })),
  };
}

// ── Model interest — which vehicles the press audience is engaging with ──
// Dated model-page views (detail_model_statistics) + asset downloads per
// model (media_model × media event log) + release views per model.
export async function modelInterest(siteId, rangeStr) {
  const site = getSite(siteId);
  if (!dbKind(site)) return null;
  const { startDate, endDate } = resolveRange(rangeStr || '28d');
  if (dbIsDemo(site)) {
    const seed = [...String(siteId)].reduce((a, c) => (a * 31 + c.charCodeAt(0)) >>> 0, 7);
    const names = ['Palisade', 'IONIQ 9', 'Santa Fe', 'Tucson', 'Kona', 'IONIQ 5', 'Elantra', 'Sonata'];
    return { source: 'demo', period: { startDate, endDate },
      models: names.map((name, i) => ({ id: i + 1, name,
        pageViews: Math.max(40, 1400 - i * 170 + (seed % 90)), prevPageViews: Math.max(30, 1300 - i * 160 + (seed % 70)),
        assetDownloads: Math.max(8, 700 - i * 90 + (seed % 60)), releaseViews: Math.max(20, 1100 - i * 140 + (seed % 80)) })) };
  }
  const tables = await siteTables(siteId);
  const D = [startDate, endDate + ' 23:59:59'];
  const prev = previousRange({ startDate, endDate });
  const PD = [prev.startDate, prev.endDate + ' 23:59:59'];
  const jobs = {};
  if (tables.has('detail_model_statistics') && tables.has('models')) {
    const info = await detailStatsInfo(siteId, 'detail_model_statistics');
    if (info) {
      const lwv = localeWhere('mo.');
      jobs.views = q(siteId,
        `SELECT s.model_id AS id, MAX(mo.name) AS name, COUNT(*) AS pageViews
           FROM detail_model_statistics s JOIN models mo ON mo.id = s.model_id
          WHERE s.\`${info.dateCol}\` BETWEEN ? AND ?${lwv.where} GROUP BY s.model_id
          ORDER BY pageViews DESC LIMIT 20`, [...D, ...lwv.params]).catch(() => []);
      jobs.prevViews = q(siteId,
        `SELECT s.model_id AS id, COUNT(*) AS pageViews
           FROM detail_model_statistics s
          WHERE s.\`${info.dateCol}\` BETWEEN ? AND ? GROUP BY s.model_id`, PD).catch(() => []);
    }
  }
  if (tables.has('media_model') && tables.has('detail_media_statistics') && tables.has('models')) {
    jobs.assets = q(siteId,
      `SELECT mm.model_id AS id, MAX(mo.name) AS name, SUM(s.action = 2) AS assetDownloads
         FROM detail_media_statistics s
         JOIN media_model mm ON mm.media_id = s.media_id
         JOIN models mo ON mo.id = mm.model_id
        WHERE s.date_event BETWEEN ? AND ? GROUP BY mm.model_id
        ORDER BY assetDownloads DESC LIMIT 20`, D).catch(() => []);
  }
  if (tables.has('release_models') && tables.has('detail_release_statistics') && tables.has('models')) {
    jobs.releases = q(siteId,
      `SELECT rm.model_id AS id, MAX(mo.name) AS name, COUNT(*) AS releaseViews
         FROM detail_release_statistics s
         JOIN release_models rm ON rm.release_id = s.release_id
         JOIN models mo ON mo.id = rm.model_id
        WHERE s.date_event BETWEEN ? AND ? GROUP BY rm.model_id
        ORDER BY releaseViews DESC LIMIT 20`, D).catch(() => []);
  }
  const [views, prevViews, assets, releases] = await Promise.all([
    jobs.views || [], jobs.prevViews || [], jobs.assets || [], jobs.releases || []]);
  if (!views.length && !assets.length && !releases.length) return null;
  const byId = new Map();
  const touch = (id, name) => {
    const cur = byId.get(String(id)) || { id, name: name || null, pageViews: 0, prevPageViews: 0, assetDownloads: 0, releaseViews: 0 };
    if (name && !cur.name) cur.name = name;
    byId.set(String(id), cur);
    return cur;
  };
  for (const r of views) touch(r.id, r.name).pageViews = Number(r.pageViews) || 0;
  for (const r of prevViews) { if (byId.has(String(r.id))) byId.get(String(r.id)).prevPageViews = Number(r.pageViews) || 0; }
  for (const r of assets) touch(r.id, r.name).assetDownloads = Number(r.assetDownloads) || 0;
  for (const r of releases) touch(r.id, r.name).releaseViews = Number(r.releaseViews) || 0;
  const models = [...byId.values()].filter((m) => m.name)
    .sort((a, b) => (b.pageViews + b.assetDownloads + b.releaseViews) - (a.pageViews + a.assetDownloads + a.releaseViews))
    .slice(0, 15);
  return { source: 'platform-event-log', period: { startDate, endDate }, models };
}

// ── Email intelligence — clicked links, send-time patterns, latency ──────
// detail_email_statistics (7M+ rows): event 7=delivered 1=open 2=click
// 3=unsubscribe, with the clicked URL and both send + event timestamps.
export async function emailIntelligence(siteId, rangeStr) {
  const site = getSite(siteId);
  if (!dbKind(site)) return null;
  const { startDate, endDate } = resolveRange(rangeStr || '90d');
  if (dbIsDemo(site)) {
    const seed = [...String(siteId)].reduce((a, c) => (a * 31 + c.charCodeAt(0)) >>> 0, 7);
    return { source: 'demo', period: { startDate, endDate },
      topClickedUrls: ['releases/2411', 'releases/2398', 'gallery/photos', 'models/ev9', 'releases/2377']
        .map((p, i) => ({ url: 'https://example.com/' + p, clicks: Math.max(6, 240 - i * 45 + (seed % 30)), people: Math.max(4, 180 - i * 35) })),
      byHour: Array.from({ length: 24 }, (_, h) => ({ hour: h, opens: Math.max(2, Math.round(160 * Math.exp(-((h - 9) ** 2) / 18)) + (seed % 9)), clicks: Math.max(0, Math.round(40 * Math.exp(-((h - 10) ** 2) / 16))) })),
      byWeekday: ['Sun', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat'].map((d, i) => ({ day: d, opens: [40, 190, 230, 210, 200, 150, 30][i], clicks: [8, 45, 60, 52, 48, 30, 6][i] })),
      medianOpenDelayMinutes: 42 };
  }
  const info = await detailStatsInfo(siteId, 'detail_email_statistics');
  if (!info) return null;
  const D = [startDate, endDate + ' 23:59:59'];
  const [topClickedUrls, byHour, byWeekday, [lat]] = await Promise.all([
    q(siteId,
      `SELECT url, COUNT(*) AS clicks, COUNT(DISTINCT email) AS people
         FROM detail_email_statistics
        WHERE event = 2 AND url IS NOT NULL AND url <> '' AND \`${info.dateCol}\` BETWEEN ? AND ?
        GROUP BY url ORDER BY clicks DESC LIMIT 15`, D).catch(() => []),
    q(siteId,
      `SELECT HOUR(\`${info.dateCol}\`) AS hour, SUM(event = 1) AS opens, SUM(event = 2) AS clicks
         FROM detail_email_statistics
        WHERE event IN (1, 2) AND \`${info.dateCol}\` BETWEEN ? AND ?
        GROUP BY hour ORDER BY hour`, D).catch(() => []),
    q(siteId,
      `SELECT DAYOFWEEK(\`${info.dateCol}\`) AS dow, SUM(event = 1) AS opens, SUM(event = 2) AS clicks
         FROM detail_email_statistics
        WHERE event IN (1, 2) AND \`${info.dateCol}\` BETWEEN ? AND ?
        GROUP BY dow ORDER BY dow`, D).catch(() => []),
    q(siteId,
      `SELECT AVG(TIMESTAMPDIFF(MINUTE, date_sent, \`${info.dateCol}\`)) AS avgMin
         FROM detail_email_statistics
        WHERE event = 1 AND date_sent IS NOT NULL AND \`${info.dateCol}\` BETWEEN ? AND ?
          AND TIMESTAMPDIFF(MINUTE, date_sent, \`${info.dateCol}\`) BETWEEN 0 AND 10080`, D).catch(() => [{}]),
  ]);
  const DOW = ['', 'Sun', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat'];
  return {
    source: 'platform-event-log', period: { startDate, endDate },
    topClickedUrls: topClickedUrls.map((r) => ({ url: r.url, clicks: Number(r.clicks) || 0, people: Number(r.people) || 0 })),
    byHour: byHour.map((r) => ({ hour: Number(r.hour), opens: Number(r.opens) || 0, clicks: Number(r.clicks) || 0 })),
    byWeekday: byWeekday.map((r) => ({ day: DOW[Number(r.dow)] || String(r.dow), opens: Number(r.opens) || 0, clicks: Number(r.clicks) || 0 })),
    avgOpenDelayMinutes: lat?.avgMin != null ? Math.round(Number(lat.avgMin)) : null,
  };
}

// Media upload-date column varies between copies — probe once.
const mediaDateCache = new Map();
async function mediaDateCol(siteId) {
  const key = siteId;
  if (mediaDateCache.has(key)) return mediaDateCache.get(key);
  let col = null;
  try {
    const desc = await q(siteId, 'DESCRIBE media');
    const names = desc.map((d) => String(d.Field).toLowerCase());
    col = ['created_at', 'created', 'uploaded_at', 'date_added', 'added_at', 'updated_at'].find((c) => names.includes(c)) || null;
  } catch { /* none */ }
  mediaDateCache.set(key, col);
  return col;
}

export async function legacyOverview(siteId, rangeStr, companyId = null, markets = null) {
  const site = getSite(siteId);
  const kind = dbKind(site);
  if (!kind) return { enabled: false };
  const { startDate, endDate } = resolveRange(rangeStr);
  if (dbIsDemo(site)) {
    const demoOut = { enabled: true, ...demoLegacyOverview(site, kind, startDate, endDate, companyId) };
    if (markets?.length) demoOut.platformMarkets = { requested: markets, applied: false, column: null };
    return demoOut;
  }

  const out = { enabled: true, kind, errors: [] };
  const cf = companyId ? Number(companyId) : null;
  // Market filter on the platform data — only when the DB is market-tagged.
  const mf = markets?.length ? await releaseMarketFilter(siteId, markets).catch(() => null) : null;
  const NM = MNAME(await hasMediaTitle(siteId).catch(() => false)); // human media names when the DB has them
  const MRELx = MREL_FOR(await mediaRelPivot(siteId).catch(() => null)); // correct pivot per schema
  if (markets?.length) out.platformMarkets = { requested: markets, applied: !!mf, column: mf?.meta?.column || null };
  const MW = (alias) => (mf ? mf.where(alias) : '');
  const MP = mf ? mf.params : [];
  const softly = async (label, fn, fallback = []) => {
    try { return await fn(); } catch (e) { out.errors.push(label + ': ' + (e.message || e)); return fallback; }
  };

  if (kind === 'newspress') {
    const RSr = await relStatus(siteId, 'r.'); // LIVE releases only
    const P = [];
    P.push(softly('companies', () => q(siteId,
      `SELECT id, name FROM companies WHERE status = 1 AND deleted_at IS NULL ORDER BY name`)).then((v) => (out.companies = v)));
    P.push(softly('releases', () => q(siteId,
      `SELECT r.id, r.title, DATE(r.published) AS published, r.viewed, r.downloaded,
              r.added_to_basket AS basket, r.company_id AS companyId, c.name AS companyName
         FROM releases r LEFT JOIN companies c ON c.id = r.company_id
        WHERE ${RSr}r.published BETWEEN ? AND ? ${cf ? 'AND r.company_id = ?' : ''}${MW('r.')}
        ORDER BY r.viewed DESC LIMIT 25`,
      [...(cf ? [startDate, endDate + ' 23:59:59', cf] : [startDate, endDate + ' 23:59:59']), ...MP])).then((v) => (out.releases = v)));
    P.push(softly('emails', () => {
      const sel = (withInit) => q(siteId,
        `SELECT DISTINCT e.campaign_id AS campaignId, e.title, DATE(e.sent_date) AS sentDate,
                e.recipients, e.delivered, e.opened, e.click AS clicked${withInit ? ', e.initial_open AS initialOpen' : ''},
                e.unsubscribe AS unsubscribed, (e.hard_bounce + e.soft_bounce) AS bounced
           FROM emails e
           ${cf ? 'JOIN email_releases er ON er.email_id = e.id JOIN releases r ON r.id = er.release_id AND r.company_id = ?' : ''}
          WHERE e.sent_date BETWEEN ? AND ?
          ORDER BY e.sent_date DESC LIMIT 25`,
        cf ? [cf, startDate, endDate + ' 23:59:59'] : [startDate, endDate + ' 23:59:59']);
      return sel(true).catch(() => sel(false));
    }).then(async (v) => {
      const rows = v.map((e) => ({ ...e, source: 'db' }));
      await applyUniqueOpens(siteId, rows);
      out.emails = rows.map(fixEmailCounts);
    }));
    P.push(softly('media', async () => {
      const J = cf ? 'JOIN media_releases mr ON mr.media_id = m.id JOIN releases r ON r.id = mr.release_id AND r.company_id = ?' : '';
      const JP = cf ? [cf] : [];
      const ML = await mediaLive(siteId); // live assets only (status/deleted/embargo)
      const fetch1 = (extraWhere, lim2) => q(siteId,
        `SELECT m.id, ${NM} AS name, m.name AS code, m.type, m.viewed, m.downloaded, ${MRELx} FROM media m ${J}
          WHERE 1=1${ML} ${extraWhere} ORDER BY ${MRANK} DESC LIMIT ${lim2}`, JP)
        .catch(() => q(siteId,
        `SELECT m.id, m.name, m.type, m.viewed, m.downloaded FROM media m ${J}
          WHERE 1=1${ML} ${extraWhere} ORDER BY m.downloaded DESC LIMIT ${lim2}`, JP).catch(() => []));
      // Top overall PLUS the top videos and documents — pure download ranking
      // buried videos entirely, so the Videos tab looked like it had no data.
      const [top, vids, docs] = await Promise.all([fetch1('', 15), fetch1('AND m.type = 2', 8), fetch1('AND m.type = 3', 8)]);
      const seen = new Set();
      return [...top, ...vids, ...docs].filter((m) => !seen.has(m.id) && seen.add(m.id))
        .sort((a2, b2) => Math.max(b2.downloaded || 0, b2.viewed || 0) - Math.max(a2.downloaded || 0, a2.viewed || 0));
    }).then((v) => (out.media = v.map((m) => ({ ...m, type: MEDIA_TYPE[m.type] || 'misc' })))));
    P.push(softly('documents', async () => q(siteId,
      `SELECT m.id, m.name, m.viewed, m.downloaded FROM media m
         ${cf ? 'JOIN media_releases mr ON mr.media_id = m.id JOIN releases r ON r.id = mr.release_id AND r.company_id = ?' : ''}
        WHERE m.type = 3${await mediaLive(siteId)} ORDER BY m.downloaded DESC LIMIT 12`,
      cf ? [cf] : [])).then((v) => (out.documents = (v || []).map((m) => ({ ...m, type: 'document' })))));
    P.push(softly('banners', () => q(siteId,
      `SELECT id, title, clicked FROM banners WHERE status = 1 ${cf ? 'AND company_id = ?' : ''}
        ORDER BY clicked DESC LIMIT 10`, cf ? [cf] : [])).then((v) => (out.banners = v)));
    P.push(softly('audience', () => q(siteId, `SELECT type, COUNT(*) AS c FROM users WHERE status = 1 GROUP BY type`)).then((aud) => {
      const by = Object.fromEntries(aud.map((r) => [r.type, r.c]));
      out.audience = { journalists: by[3] || 0, consumers: by[4] || 0, mediaClients: by[2] || 0 };
    }));
    P.push(softly('topJournalists', () => q(siteId,
      `SELECT u.id AS userId, CONCAT(COALESCE(u.name,''), ' ', COALESCE(u.surname,'')) AS name, u.email, COUNT(*) AS events
         FROM detail_release_statistics d JOIN users u ON u.id = d.user_id
        WHERE u.type = 3 AND d.created_at BETWEEN ? AND ?
        GROUP BY u.id, u.name, u.surname, u.email ORDER BY events DESC LIMIT 10`,
      [startDate, endDate + ' 23:59:59'])).then((v) => (out.topJournalists = v)));
    P.push(softly('topPublications', () => q(siteId,
      `SELECT p.id, p.name, COUNT(*) AS events
         FROM detail_release_statistics d
         JOIN publication_users pu ON pu.user_id = d.user_id
         JOIN publications p ON p.id = pu.publication_id
        WHERE d.created_at BETWEEN ? AND ?
        GROUP BY p.id, p.name ORDER BY events DESC LIMIT 10`,
      [startDate, endDate + ' 23:59:59'])).then((v) => (out.topPublications = v)));
    P.push(softly('categories', () => q(siteId,
      `SELECT c.id, c.name, COUNT(p.release_id) AS releases
         FROM categories c JOIN category_releases p ON p.category_id = c.id
         JOIN releases r ON r.id = p.release_id AND ${RSr}r.published BETWEEN ? AND ?
        GROUP BY c.id, c.name ORDER BY releases DESC LIMIT 12`,
      [startDate, endDate + ' 23:59:59'])).then((v) => (out.categories = v)));
    P.push(softly('cadence', async () => {
      const prev = previousRange({ startDate, endDate });
      const emailJoin = cf ? 'JOIN email_releases er ON er.email_id = e.id JOIN releases r ON r.id = er.release_id AND r.company_id = ?' : '';
      const [days, dist, [pv], [dv]] = await Promise.all([
        q(siteId,
          `SELECT DATE(published) AS date, COUNT(*) AS published FROM releases
            WHERE ${await relStatus(siteId)}published BETWEEN ? AND ? ${cf ? 'AND company_id = ?' : ''} GROUP BY DATE(published) ORDER BY date`,
          cf ? [startDate, endDate + ' 23:59:59', cf] : [startDate, endDate + ' 23:59:59']),
        q(siteId,
          `SELECT DATE(e.sent_date) AS date, COUNT(DISTINCT e.campaign_id) AS distributed FROM emails e ${emailJoin}
            WHERE e.sent_date BETWEEN ? AND ? GROUP BY DATE(e.sent_date) ORDER BY date`,
          cf ? [cf, startDate, endDate + ' 23:59:59'] : [startDate, endDate + ' 23:59:59']).catch(() => []),
        q(siteId,
          `SELECT COUNT(*) AS n FROM releases WHERE ${await relStatus(siteId)}published BETWEEN ? AND ? ${cf ? 'AND company_id = ?' : ''}`,
          cf ? [prev.startDate, prev.endDate + ' 23:59:59', cf] : [prev.startDate, prev.endDate + ' 23:59:59']),
        q(siteId,
          `SELECT COUNT(DISTINCT e.campaign_id) AS n FROM emails e ${emailJoin}
            WHERE e.sent_date BETWEEN ? AND ?`,
          cf ? [cf, prev.startDate, prev.endDate + ' 23:59:59'] : [prev.startDate, prev.endDate + ' 23:59:59']).catch(() => [{ n: null }]),
      ]);
      return { days: mergeCadence(days, dist), total: days.reduce((a, r) => a + r.published, 0),
               prevTotal: pv.n, distTotal: dist.reduce((a, r) => a + r.distributed, 0), distPrev: dv?.n ?? null };
    }, null).then((v) => (out.cadence = v)));
    await Promise.all(P);
  } else {
    // Newsroom / Media sites — full parity with Newspress markets, adapted to
    // the Media schema: users are first_name/last_name with a STRING type,
    // there are no publications/companies tables, and stats track views only.
    const RS = await relStatus(siteId); // adaptive status filter
    const P = [];
    P.push(softly('releases', () => q(siteId,
      `SELECT id, title, DATE(published) AS published, viewed
         FROM releases WHERE ${RS}published BETWEEN ? AND ?${MW('')}
        ORDER BY viewed DESC LIMIT 25`, [startDate, endDate + ' 23:59:59', ...MP])).then((v) => (out.releases = v)));
    P.push(softly('emails', () => {
      const sel = (withInit) => q(siteId,
        `SELECT campaign_id AS campaignId, title, DATE(sent_date) AS sentDate,
                recipients, delivered, opened, click AS clicked${withInit ? ', initial_open AS initialOpen' : ''},
                unsubscribe AS unsubscribed, (hard_bounce + soft_bounce) AS bounced
           FROM emails WHERE sent_date BETWEEN ? AND ?
          ORDER BY sent_date DESC LIMIT 25`, [startDate, endDate + ' 23:59:59']);
      return sel(true).catch(() => sel(false));
    }).then(async (v) => {
      const rows = (v || []).map((e) => ({ ...e, source: 'db' }));
      await applyUniqueOpens(siteId, rows);
      out.emails = rows.map(fixEmailCounts);
    }));
    P.push(softly('media', async () => {
      // Period + market scoped ranking — ONE event-log scan for everything.
      const pool2 = await datedMediaOverview(siteId, rangeStr);
      if (pool2 && pool2.length) {
        out.mediaScope = 'period';
        out._datedPool = pool2; // documents job slices from the same scan
        const top = pool2.slice(0, 15);
        const vids = pool2.filter((m2) => m2.type === 'video').slice(0, 8);
        const docs = pool2.filter((m2) => m2.type === 'document').slice(0, 8);
        const seen0 = new Set();
        return [...top, ...vids, ...docs].filter((m2) => !seen0.has(m2.id) && seen0.add(m2.id));
      }
      out.mediaScope = 'lifetime';
      const ML2 = await mediaLive(siteId);
      const fetch1 = (extraWhere, lim2) => q(siteId,
        `SELECT m.id, ${NM} AS name, m.name AS code, m.type, m.viewed, m.downloaded, ${MRELx} FROM media m
          WHERE 1=1${ML2} ${extraWhere} ORDER BY ${MRANK} DESC LIMIT ${lim2}`)
        .catch(() => q(siteId,
        `SELECT m.id, m.name, m.type, m.viewed, m.downloaded FROM media m
          WHERE 1=1${ML2} ${extraWhere} ORDER BY m.downloaded DESC LIMIT ${lim2}`).catch(() => []));
      const [top, vids, docs] = await Promise.all([fetch1('', 15), fetch1('AND m.type = 2', 8), fetch1('AND m.type = 3', 8)]);
      const seen = new Set();
      return [...top, ...vids, ...docs].filter((m) => !seen.has(m.id) && seen.add(m.id))
        .sort((a2, b2) => Math.max(b2.downloaded || 0, b2.viewed || 0) - Math.max(a2.downloaded || 0, a2.viewed || 0));
    // Dated pool rows already carry STRING types ('image'/'video'/'document');
    // only the lifetime rows still have the numeric media.type id to map.
    }).then((v) => (out.media = (v || []).map((m) => ({
      ...m, type: MEDIA_TYPE[m.type] || (typeof m.type === 'string' && m.type ? m.type : 'misc'),
    })))));
    P.push(softly('documents', async () => {
      const pool3 = out._datedPool || await datedMediaOverview(siteId, rangeStr);
      const dated = pool3 ? pool3.filter((m2) => m2.type === 'document').slice(0, 12) : null;
      if (dated && dated.length) { out.docsScope = 'period'; return dated; }
      out.docsScope = 'lifetime';
      return (await q(siteId,
        `SELECT id, name, viewed, downloaded FROM media m WHERE m.type = 3${await mediaLive(siteId)}
          ORDER BY downloaded DESC LIMIT 12`)).map((m) => ({ ...m, type: 'document' }));
    }).then((v) => (out.documents = v || [])));
    P.push(softly('models', async () => {
      // Period-scoped model views from the event log — obeys the date range
      // AND the market filter, exactly like the rest of the dashboard.
      try {
        const mi = await modelInterest(siteId, rangeStr);
        const rows = (mi?.models || []).filter((m) => m.pageViews > 0).slice(0, 15)
          .map((m) => ({ id: m.id, name: m.name, viewed: m.pageViews }));
        if (rows.length) { out.modelsScope = 'period'; return rows; }
      } catch { /* fall back */ }
      out.modelsScope = 'lifetime';
      const lwmo = localeWhere('');
      return q(siteId,
        `SELECT id, name, viewed FROM models WHERE status = 1${lwmo.where} ORDER BY viewed DESC LIMIT 15`, lwmo.params)
        .catch(() => q(siteId, `SELECT id, name, viewed FROM models WHERE status = 1 ORDER BY viewed DESC LIMIT 15`));
    }).then((v) => (out.models = v)));
    P.push(softly('categories', async () => q(siteId,
      `SELECT c.id, c.name, COUNT(p.release_id) AS releases
         FROM categories c JOIN release_categories p ON p.category_id = c.id
         JOIN releases r ON r.id = p.release_id AND ${await relStatus(siteId, 'r.')}r.published BETWEEN ? AND ?
        GROUP BY c.id, c.name ORDER BY releases DESC LIMIT 12`,
      [startDate, endDate + ' 23:59:59'])).then((v) => (out.categories = v)));
    P.push(softly('topJournalists', () => q(siteId,
      `SELECT u.id AS userId, TRIM(CONCAT(COALESCE(u.first_name,''), ' ', COALESCE(u.last_name,''))) AS name,
              u.email, COUNT(*) AS events
         FROM detail_release_statistics d JOIN users u ON u.id = d.user_id
        WHERE d.created_at BETWEEN ? AND ?
        GROUP BY u.id, u.first_name, u.last_name, u.email ORDER BY events DESC LIMIT 10`,
      [startDate, endDate + ' 23:59:59'])).then((v) => (out.topJournalists = v)));
    P.push(softly('audience', () => q(siteId,
      `SELECT COALESCE(NULLIF(TRIM(type), ''), 'unclassified') AS t, COUNT(*) AS n
         FROM users WHERE status = 1 GROUP BY t ORDER BY n DESC LIMIT 8`)).then((rows) => {
      if (rows?.length) out.audienceTypes = rows.map((r) => ({ type: r.t, n: r.n }));
    }));
    P.push(softly('cadence', async () => {
      const prev = previousRange({ startDate, endDate });
      const lwc = await relLocaleWhere(siteId, '');
      const [days, dist, [pv], [dv]] = await Promise.all([
        q(siteId,
          `SELECT DATE(published) AS date, COUNT(*) AS published FROM releases
            WHERE ${RS}published BETWEEN ? AND ?${lwc.where} GROUP BY DATE(published) ORDER BY date`,
          [startDate, endDate + ' 23:59:59', ...lwc.params]),
        q(siteId,
          `SELECT DATE(sent_date) AS date, COUNT(DISTINCT campaign_id) AS distributed FROM emails
            WHERE sent_date BETWEEN ? AND ? GROUP BY DATE(sent_date) ORDER BY date`,
          [startDate, endDate + ' 23:59:59']).catch(() => []),
        q(siteId,
          `SELECT COUNT(*) AS n FROM releases WHERE ${RS}published BETWEEN ? AND ?${lwc.where}`,
          [prev.startDate, prev.endDate + ' 23:59:59', ...lwc.params]),
        q(siteId,
          `SELECT COUNT(DISTINCT campaign_id) AS n FROM emails WHERE sent_date BETWEEN ? AND ?`,
          [prev.startDate, prev.endDate + ' 23:59:59']).catch(() => [{ n: null }]),
      ]);
      return { days: mergeCadence(days, dist), total: days.reduce((a, r) => a + r.published, 0),
               prevTotal: pv.n, distTotal: dist.reduce((a, r) => a + r.distributed, 0), distPrev: dv?.n ?? null };
    }, null).then((v) => (out.cadence = v)));
    await Promise.all(P);
  }
  // DB freshness — when a period shows 0 releases, the newest row's date
  // tells you whether the connected copy is simply behind the live site.
  try {
    const [f] = await q(siteId, 'SELECT DATE(MAX(published)) AS lastPublished, COUNT(*) AS totalReleases FROM releases');
    out.freshness = { lastPublished: f?.lastPublished ? String(f.lastPublished).slice(0, 10) : null, totalReleases: Number(f?.totalReleases) || 0 };
  } catch { /* optional */ }
  return out;
}

export async function legacyRelease(siteId, releaseId, rangeStr) {
  const site = getSite(siteId);
  const kind = dbKind(site);
  if (!kind) return null;
  const { startDate, endDate } = resolveRange(rangeStr);
  if (dbIsDemo(site)) return demoLegacyRelease(site, kind, releaseId, startDate, endDate);

  const rid = Number(releaseId);
  const isNP = kind === 'newspress';
  const [rel] = await q(siteId,
    isNP
      ? `SELECT r.id, r.title, DATE(r.published) AS published, r.viewed, r.downloaded,
                r.added_to_basket AS basket, c.name AS companyName
           FROM releases r LEFT JOIN companies c ON c.id = r.company_id WHERE r.id = ?`
      : `SELECT id, title, DATE(published) AS published, viewed FROM releases WHERE id = ?`,
    [rid]);
  if (!rel) return null;

  const pivot = isNP ? 'category_releases' : 'release_categories';
  const [cats, countries, emailsRaw, readers, assets, dailyEvents] = await Promise.all([
    q(siteId,
      `SELECT c.name FROM categories c JOIN ${pivot} p ON p.category_id = c.id WHERE p.release_id = ?`, [rid]).catch(() => []),
    q(siteId,
      `SELECT country, COUNT(*) AS events FROM detail_release_statistics
        WHERE release_id = ? AND country IS NOT NULL AND country != ''
        GROUP BY country ORDER BY events DESC LIMIT 8`, [rid]).catch(() => []),
    q(siteId,
      `SELECT e.campaign_id AS campaignId, e.title, DATE(e.sent_date) AS sentDate,
              e.recipients, e.delivered, e.opened, e.click AS clicked, e.initial_open AS initialOpen
         FROM emails e JOIN email_releases er ON er.email_id = e.id
        WHERE er.release_id = ? ORDER BY e.sent_date DESC LIMIT 5`, [rid])
      .catch(() => q(siteId,
      `SELECT e.campaign_id AS campaignId, e.title, DATE(e.sent_date) AS sentDate,
              e.recipients, e.opened, e.click AS clicked
         FROM emails e JOIN email_releases er ON er.email_id = e.id
        WHERE er.release_id = ? ORDER BY e.sent_date DESC LIMIT 5`, [rid]).catch(() => []))
      .then(async (v) => { const rows = v || []; await applyUniqueOpens(siteId, rows); return rows.map(fixEmailCounts); }),
    // Named readers: every registered person who viewed/downloaded THIS release
    q(siteId,
      `SELECT u.id AS userId, ${nameSql(isNP)} AS name, u.email, u.type,
              SUM(d.action = 1) AS views, SUM(d.action = 2) AS downloads,
              DATE(MAX(d.created_at)) AS lastSeen
         FROM detail_release_statistics d JOIN users u ON u.id = d.user_id
        WHERE d.release_id = ?
        GROUP BY u.id ORDER BY (SUM(d.action = 1) + SUM(d.action = 2)) DESC LIMIT 40`, [rid]).catch(() => []),
    // The release's media assets with their own counters
    q(siteId,
      `SELECT m.id, m.name, m.type, m.viewed, m.downloaded
         FROM media m JOIN media_releases mr ON mr.media_id = m.id
        WHERE mr.release_id = ? ORDER BY m.downloaded DESC LIMIT 15`, [rid]).catch(() => []),
    // Daily interest curve from the platform's own event log
    q(siteId,
      `SELECT DATE(created_at) AS date, COUNT(*) AS events FROM detail_release_statistics
        WHERE release_id = ? GROUP BY DATE(created_at) ORDER BY date`, [rid]).catch(() => []),
  ]);
  // A campaign is DEDICATED to this release when its title matches; digest
  // sends that merely featured the release are flagged so their totals are
  // never presented as this release's own distribution.
  const norm = (t) => String(t || '').toLowerCase().replace(/[^a-z0-9 ]/g, ' ').split(/\s+/).filter((x) => x.length >= 3);
  const relToks = new Set(norm(rel.title));
  const emails = emailsRaw.map((e) => {
    const ct = norm(e.title);
    const overlap = ct.length ? ct.filter((t) => relToks.has(t)).length / ct.length : 0;
    return { ...e, source: 'db', dedicated: overlap >= 0.5 };
  });

  // Merge asset renditions that share a name (platforms upload several sizes).
  const assetMap = new Map();
  for (const m of assets) {
    const key = String(m.name || m.id);
    const hit = assetMap.get(key);
    if (hit) { hit.viewed += Number(m.viewed) || 0; hit.downloaded += Number(m.downloaded) || 0; hit.variants++; }
    else assetMap.set(key, { ...m, viewed: Number(m.viewed) || 0, downloaded: Number(m.downloaded) || 0, variants: 1, type: MEDIA_TYPE[m.type] || m.type || 'misc' });
  }
  const mergedAssets = [...assetMap.values()].sort((a, b) => b.downloaded - a.downloaded || b.viewed - a.viewed);

  return {
    ...rel, categories: cats.map((c) => c.name), countries, emails,
    readers, assets: mergedAssets, dailyEvents,
  };
}


// Campaigns straight from the reports DB in the unified email shape —
// preferred over ESP-API domain matching when a site has a DB connected.
export async function legacyEmailCampaigns(siteId, rangeStr) {
  const ov = await legacyOverview(siteId, rangeStr);
  if (!ov.enabled || !ov.emails) return null;
  const rows = ov.emails.map((e) => ({
    id: String(e.campaignId), name: e.title || String(e.campaignId), subject: null,
    provider: 'db', date: e.sentDate ? String(e.sentDate).slice(0, 10) : null,
    sent: e.recipients || 0, delivered: e.delivered || 0,
    opens: e.opened || 0, clicks: e.clicked || 0,
    openEvents: e.openEvents, uniqueCounts: e.uniqueCounts || false,
    initialOpen: e.initialOpen,
  }));
  // The emails table's opened/click columns count EVENTS (one person can open
  // many times), which yields absurd >100% "open rates". Replace them with
  // UNIQUE people from detail_email_statistics — one grouped query, cached.
  await applyUniqueOpens(siteId, rows, { idKey: 'id', openKey: 'opens', clickKey: 'clicks' });
  for (const r of rows) {
    // Never let opens/clicks exceed the AUDIENCE — a rate above 100% is
    // always a counting artefact, not a fact. When we could NOT resolve
    // unique openers (no stats table) and the counters are clearly events,
    // a rate cannot honestly be computed at all — flag it instead of
    // showing the old flat 100%.
    const cap = r.sent || r.delivered || 0;
    const uniqueOk = r.uniqueCounts || (r.initialOpen != null && r.initialOpen !== '' && Number(r.initialOpen) > 0);
    if (cap && !uniqueOk && (r.openEvents || 0) > cap) r.rateUnknown = true;
    if (cap) { r.opens = Math.min(r.opens, cap); r.clicks = Math.min(r.clicks, cap); }
  }
  return rows;
}

// Exact release → campaign mapping via email_releases (no heuristics).
export async function emailReleaseMap(siteId, releaseIds) {
  const site = getSite(siteId);
  const kind = dbKind(site);
  if (!kind || !releaseIds.length) return {};
  if (dbIsDemo(site)) {
    const { startDate, endDate } = resolveRange('90d');
    const ov = demoLegacyOverview(site, kind, startDate, endDate);
    const map = {};
    ov.emails.forEach((e) => { if (e.releaseId && releaseIds.includes(e.releaseId)) map[e.releaseId] = e; });
    return Object.fromEntries(Object.entries(map).map(([rid, e]) => [rid, {
      id: String(e.campaignId), provider: 'db', name: e.title, date: e.sentDate,
      sent: e.recipients, opens: e.opened, openRate: e.delivered ? Math.min(1, e.opened / e.delivered) : 0,
    }]));
  }
  const ids = releaseIds.map(Number).filter((n) => Number.isFinite(n));
  if (!ids.length) return {};
  const sel = (withInit) => q(siteId,
    `SELECT er.release_id AS rid, e.campaign_id AS campaignId, e.title,
            DATE(e.sent_date) AS sentDate, e.recipients, e.delivered, e.opened, e.click AS clicked${withInit ? ', e.initial_open AS initialOpen' : ''}
       FROM emails e JOIN email_releases er ON er.email_id = e.id
      WHERE er.release_id IN (${ids.map(() => '?').join(',')})
      ORDER BY e.sent_date DESC`, ids);
  const rows = await sel(true).catch(() => sel(false).catch(() => []));
  await applyUniqueOpens(siteId, rows);
  const map = {};
  for (const r0 of rows) {
    const r = fixEmailCounts(r0); // unique opens, capped at audience
    const base = Number(r.recipients) || Number(r.delivered) || 0;
    // Rate is only honest when the opens are UNIQUE people (stats-table
    // resolution or the platform's initial_open) — otherwise n/a, never a
    // fake 100% from capped event counts.
    const uniqueOk = r.uniqueCounts || (r.initialOpen != null && r.initialOpen !== '' && Number(r.initialOpen) > 0);
    if (!map[r.rid]) map[r.rid] = {
      id: String(r.campaignId), provider: 'db', name: r.title || String(r.campaignId),
      date: r.sentDate ? String(r.sentDate).slice(0, 10) : null,
      sent: r.recipients || 0, opens: r.opened || 0,
      openRate: base ? (uniqueOk || (r.openEvents || 0) <= base ? Math.min(1, (r.opened || 0) / base) : null) : 0,
    };
  }
  return map;
}


// Titles (+ company) for a set of release ids — enriches the GA4 tracker.
export async function releaseTitles(siteId, ids, { throwOnError = false } = {}) {
  const site = getSite(siteId);
  const kind = dbKind(site);
  if (!kind || !ids.length) return {};
  const nums = ids.map(Number).filter(Number.isFinite);
  if (!nums.length) return {};
  if (dbIsDemo(site)) {
    const { startDate, endDate } = resolveRange('90d');
    const ov = demoLegacyOverview(site, kind, startDate, endDate);
    const map = {};
    nums.forEach((id, i) => { map[id] = { title: ov.releases[i % ov.releases.length]?.title || 'Release #' + id, companyName: ov.releases[i % ov.releases.length]?.companyName || null }; });
    return map;
  }
  const isNP = kind === 'newspress';
  const run = q(siteId,
    isNP
      ? `SELECT r.id, r.title, c.name AS companyName FROM releases r LEFT JOIN companies c ON c.id = r.company_id WHERE r.id IN (${nums.map(() => '?').join(',')})`
      : `SELECT id, title, NULL AS companyName FROM releases WHERE id IN (${nums.map(() => '?').join(',')})`,
    nums);
  const rows = throwOnError ? await run : await run.catch(() => []);
  return Object.fromEntries(rows.map((r) => [r.id, { title: r.title, companyName: r.companyName }]));
}

// Publishing & upload volume, this year vs last year — monthly counts of
// releases published and media assets uploaded, for the selected range and
// the SAME range shifted one year back ("prior year uploads vs current").
export async function uploadStats(siteId, rangeStr) {
  const site = getSite(siteId);
  const kind = dbKind(site);
  if (!kind) return null;
  const { startDate, endDate } = resolveRange(rangeStr || '12m');
  const shift = (d) => { const x = new Date(d + 'T00:00:00Z'); x.setUTCFullYear(x.getUTCFullYear() - 1); return x.toISOString().slice(0, 10); };
  const prevStart = shift(startDate), prevEnd = shift(endDate);
  const isNP = kind === 'newspress';
  if (dbIsDemo(site)) {
    const seed = [...String(siteId)].reduce((a, c) => (a * 31 + c.charCodeAt(0)) >>> 0, 7);
    const months = [];
    const d = new Date(startDate + 'T00:00:00Z'); d.setUTCDate(1);
    const end = new Date(endDate + 'T00:00:00Z');
    while (d <= end) {
      const m = d.toISOString().slice(0, 7);
      const i = months.length;
      const media = 20 + ((seed >> (i % 16)) % 40);
      const videos = 2 + ((seed >> ((i + 7) % 16)) % 5);
      const documents = 3 + ((seed >> ((i + 11) % 16)) % 6);
      months.push({
        month: m,
        releases: 4 + ((seed >> (i % 20)) % 9), prevReleases: 3 + ((seed >> ((i + 3) % 20)) % 8),
        media, prevMedia: 15 + ((seed >> ((i + 5) % 16)) % 35),
        images: Math.max(0, media - videos - documents), videos, documents,
        prevImages: Math.max(0, media - videos - documents - 4), prevVideos: Math.max(1, videos - 1), prevDocuments: Math.max(1, documents - 2),
      });
      d.setUTCMonth(d.getUTCMonth() + 1);
    }
    const sum = (k) => months.reduce((a, x) => a + x[k], 0);
    return { months,
      totals: { releases: sum('releases'), prevReleases: sum('prevReleases'), media: sum('media'), prevMedia: sum('prevMedia'),
                byType: { images: sum('images'), videos: sum('videos'), documents: sum('documents'),
                          prevImages: sum('prevImages'), prevVideos: sum('prevVideos'), prevDocuments: sum('prevDocuments') } } };
  }
  const relWhere = await relStatus(siteId); // LIVE releases only — both kinds
  const mdCol = await mediaDateCol(siteId);
  const lwr = await relLocaleWhere(siteId, '');
  const lwm2 = await medLocaleWhere(siteId, '');
  const ml2 = await mediaLive(siteId, ''); // live assets only
  const grab = (sql, params) => q(siteId, sql, params).catch(() => []);
  const [relCur, relPrev, medCur, medPrev, fresh, medTypeCur, medTypePrev] = await Promise.all([
    grab(`SELECT DATE_FORMAT(published, '%Y-%m') AS m, COUNT(*) AS n FROM releases WHERE ${relWhere}published BETWEEN ? AND ?${lwr.where} GROUP BY m`, [startDate, endDate + ' 23:59:59', ...lwr.params]),
    grab(`SELECT DATE_FORMAT(published, '%Y-%m') AS m, COUNT(*) AS n FROM releases WHERE ${relWhere}published BETWEEN ? AND ?${lwr.where} GROUP BY m`, [prevStart, prevEnd + ' 23:59:59', ...lwr.params]),
    mdCol ? grab(`SELECT DATE_FORMAT(\`${mdCol}\`, '%Y-%m') AS m, COUNT(*) AS n FROM media WHERE \`${mdCol}\` BETWEEN ? AND ?${lwm2.where}${ml2} GROUP BY m`, [startDate, endDate + ' 23:59:59', ...lwm2.params]) : Promise.resolve([]),
    mdCol ? grab(`SELECT DATE_FORMAT(\`${mdCol}\`, '%Y-%m') AS m, COUNT(*) AS n FROM media WHERE \`${mdCol}\` BETWEEN ? AND ?${lwm2.where}${ml2} GROUP BY m`, [prevStart, prevEnd + ' 23:59:59', ...lwm2.params]) : Promise.resolve([]),
    grab('SELECT DATE(MAX(published)) AS lastPub FROM releases'),
    // Per media TYPE (image / video / document) — so a video-only upload
    // count is always available, not just "all media together".
    mdCol ? grab(`SELECT DATE_FORMAT(\`${mdCol}\`, '%Y-%m') AS m, \`type\` AS t, COUNT(*) AS n FROM media WHERE \`${mdCol}\` BETWEEN ? AND ?${lwm2.where}${ml2} GROUP BY m, t`, [startDate, endDate + ' 23:59:59', ...lwm2.params]) : Promise.resolve([]),
    // …and the SAME per-type split for the comparison year, so "129 videos
    // in 2026 vs ? in 2025" always has both sides.
    mdCol ? grab(`SELECT DATE_FORMAT(\`${mdCol}\`, '%Y-%m') AS m, \`type\` AS t, COUNT(*) AS n FROM media WHERE \`${mdCol}\` BETWEEN ? AND ?${lwm2.where}${ml2} GROUP BY m, t`, [prevStart, prevEnd + ' 23:59:59', ...lwm2.params]) : Promise.resolve([]),
  ]);
  const toMap = (rows, shiftYear) => Object.fromEntries(rows.map((r) => {
    let m = r.m;
    if (shiftYear) m = String(Number(m.slice(0, 4)) + 1) + m.slice(4); // align prior-year months onto current
    return [m, Number(r.n) || 0];
  }));
  const rc = toMap(relCur), rp = toMap(relPrev, true), mc = toMap(medCur), mp = toMap(medPrev, true);
  // month → { image: n, video: n, document: n, misc: n }
  const byTypeMonth = {};
  for (const r of medTypeCur) {
    const t = MEDIA_TYPE[r.t] || 'misc';
    (byTypeMonth[r.m] = byTypeMonth[r.m] || {})[t] = (byTypeMonth[r.m]?.[t] || 0) + (Number(r.n) || 0);
  }
  const byTypeMonthPrev = {};
  for (const r of medTypePrev || []) {
    const t = MEDIA_TYPE[r.t] || 'misc';
    const m2 = String(Number(String(r.m).slice(0, 4)) + 1) + String(r.m).slice(4); // align prior-year month onto current
    (byTypeMonthPrev[m2] = byTypeMonthPrev[m2] || {})[t] = (byTypeMonthPrev[m2]?.[t] || 0) + (Number(r.n) || 0);
  }
  const months = [];
  const d = new Date(startDate + 'T00:00:00Z'); d.setUTCDate(1);
  const end = new Date(endDate + 'T00:00:00Z');
  while (d <= end) {
    const m = d.toISOString().slice(0, 7);
    const bt = byTypeMonth[m] || {};
    const btp = byTypeMonthPrev[m] || {};
    months.push({ month: m, releases: rc[m] || 0, prevReleases: rp[m] || 0, media: mc[m] || 0, prevMedia: mp[m] || 0,
                  images: bt.image || 0, videos: bt.video || 0, documents: bt.document || 0,
                  prevImages: btp.image || 0, prevVideos: btp.video || 0, prevDocuments: btp.document || 0 });
    d.setUTCMonth(d.getUTCMonth() + 1);
  }
  const sum = (k) => months.reduce((a, x) => a + x[k], 0);
  return {
    months,
    totals: { releases: sum('releases'), prevReleases: sum('prevReleases'), media: sum('media'), prevMedia: sum('prevMedia'),
              byType: medTypeCur.length ? { images: sum('images'), videos: sum('videos'), documents: sum('documents'),
                                            prevImages: sum('prevImages'), prevVideos: sum('prevVideos'), prevDocuments: sum('prevDocuments') } : null },
    lastPublished: fresh?.[0]?.lastPub ? String(fresh[0].lastPub).slice(0, 10) : null,
    mediaDateColumn: mdCol,
  };
}

// Find a specific media asset (image / video / document) by name — powers the
// AI analyst's "how many downloads has 67178-2026KonaSpecsFeatures… had?"
// answers, which previously fell over because the asset wasn't in the top-N.
// ── Top Spot vs regular releases ─────────────────────────────────────────
// If the releases table carries a top-spot/featured flag it's used
// automatically; otherwise the caller can pass the manually-supplied top-spot
// list (ids and/or title fragments) and the comparison is computed from it.
export async function topSpotComparison(siteId, rangeStr, manualIds = [], manualTitles = []) {
  const site = getSite(siteId);
  const kind = dbKind(site);
  if (!kind || dbIsDemo(site)) return { available: false, note: 'No reports database connected (or demo mode).' };
  const { startDate, endDate } = resolveRange(rangeStr || '90d');
  const RS = await relStatus(siteId);
  const D = [startDate, endDate + ' 23:59:59'];
  // 1) A real flag column?
  let flagCol = null;
  try {
    const desc = await q(siteId, 'DESCRIBE releases');
    const cols = desc.map((d) => String(d.Field).toLowerCase());
    flagCol = cols.find((c) => /^(top_?spot|is_?top_?spot|top_?story|featured|is_?featured|homepage|is_?homepage|pinned|highlight(ed)?)$/.test(c)) || null;
  } catch { /* fall through */ }
  const agg = (rows) => {
    const n = rows.length;
    const views = rows.reduce((a, r) => a + (Number(r.viewed) || 0), 0);
    return { releases: n, totalViews: views, avgViews: n ? Math.round(views / n) : 0,
             top: rows.slice(0, 5).map((r) => ({ id: r.id, title: r.title, viewed: Number(r.viewed) || 0 })) };
  };
  const fetchWhere = (extra, params = []) => q(siteId,
    `SELECT id, title, viewed FROM releases WHERE ${RS.replace(/%A%/g, '')}published BETWEEN ? AND ?${extra}
      ORDER BY viewed DESC LIMIT 500`, [...D, ...params]).catch(() => []);
  if (flagCol) {
    const [tops, rest] = await Promise.all([
      fetchWhere(` AND \`${flagCol}\` = 1`),
      fetchWhere(` AND (\`${flagCol}\` = 0 OR \`${flagCol}\` IS NULL)`),
    ]);
    return { available: true, source: `releases.${flagCol} flag`, period: { startDate, endDate },
             topSpot: agg(tops), regular: agg(rest),
             uplift: rest.length && tops.length ? +(agg(tops).avgViews / Math.max(1, agg(rest).avgViews)).toFixed(2) : null };
  }
  // 2) Manually-supplied list
  const ids = (manualIds || []).map(Number).filter(Boolean);
  if (manualTitles?.length) {
    for (const t of manualTitles.slice(0, 30)) {
      const hits = await searchReleases(siteId, String(t), 3).catch(() => []);
      if (hits[0]?.id) ids.push(Number(hits[0].id));
    }
  }
  if (ids.length) {
    const all = await fetchWhere('');
    const idSet = new Set(ids);
    const tops = all.filter((r) => idSet.has(Number(r.id)));
    const rest = all.filter((r) => !idSet.has(Number(r.id)));
    return { available: true, source: 'manually supplied top-spot list', period: { startDate, endDate },
             matched: tops.length, suppliedCount: ids.length,
             topSpot: agg(tops), regular: agg(rest),
             uplift: rest.length && tops.length ? +(agg(tops).avgViews / Math.max(1, agg(rest).avgViews)).toFixed(2) : null };
  }
  return { available: false,
    note: 'This reports database has no top-spot/featured flag on releases, so the comparison needs the top-spot list supplied manually — pass the release ids or titles and it will be computed. (Longer term: ask the platform team to add a top_spot flag to the releases table; the dashboard will then pick it up automatically.)' };
}

export async function searchMediaAssets(siteId, query, limit = 20) {
  const site = getSite(siteId);
  if (!dbKind(site)) return [];
  const lim = Math.max(1, Math.min(50, Number(limit) || 20));
  if (dbIsDemo(site)) {
    const seed = [...String(siteId + query)].reduce((a, c) => (a * 31 + c.charCodeAt(0)) >>> 0, 7);
    return [{ id: 9001, name: String(query), type: 'document', viewed: 300 + (seed % 900), downloaded: 40 + (seed % 200), releaseTitle: 'Demo release' }];
  }
  const like = '%' + String(query || '').trim().replace(/\s+/g, '%') + '%';
  const withT = await hasMediaTitle(siteId).catch(() => false);
  const NM3 = MNAME(withT);
  const MREL3 = MREL_FOR(await mediaRelPivot(siteId).catch(() => null));
  const rows = await q(siteId,
    `SELECT m.id, ${NM3} AS name, m.name AS code, m.type, m.viewed, m.downloaded, ${MREL3} FROM media m
      WHERE (m.name LIKE ?${withT ? ' OR m.title LIKE ?' : ''}) ORDER BY ${MRANK} DESC LIMIT ${lim}`,
    withT ? [like, like] : [like])
    .catch(() => q(siteId,
      `SELECT m.id, m.name, m.type, m.viewed, m.downloaded FROM media m
        WHERE m.name LIKE ? ORDER BY m.downloaded DESC LIMIT ${lim}`, [like]).catch(() => []));
  return rows.map((m) => ({ ...m, type: MEDIA_TYPE[m.type] || 'misc' }));
}

// Thumbnail paths for media assets — probes the media table ONCE for a
// file/path/url-style column, then returns id → path for the asked ids.
let mediaPathCol = new Map(); // siteId|confKey → column name or null
export async function mediaPaths(siteId, ids) {
  const site = getSite(siteId);
  if (!dbKind(site) || dbIsDemo(site)) return {};
  const nums = ids.map(Number).filter(Number.isFinite).slice(0, 30);
  if (!nums.length) return {};
  const ck = siteId;
  if (!mediaPathCol.has(ck)) {
    const desc = await q(siteId, 'DESCRIBE media').catch(() => []);
    const names = desc.map((d) => String(d.Field).toLowerCase());
    const col = ['file_name', 'filename', 'file_path', 'path', 'file', 'url', 'link', 'thumbnail', 'thumb', 'src']
      .find((c) => names.includes(c)) || null;
    mediaPathCol.set(ck, col);
  }
  const col = mediaPathCol.get(ck);
  if (!col) return {};
  const rows = await q(siteId,
    `SELECT id, \`${col}\` AS p FROM media WHERE id IN (${nums.map(() => '?').join(',')})`, nums).catch(() => []);
  return Object.fromEntries(rows.filter((r) => r.p).map((r) => [r.id, String(r.p)]));
}

// The release with the most platform activity on ONE day — used to name a
// traffic peak when GA4's top page for that day isn't a release URL.
export async function topReleaseOfDay(siteId, date) {
  const site = getSite(siteId);
  const kind = dbKind(site);
  if (!kind || dbIsDemo(site)) return null;
  const [row] = await q(siteId,
    `SELECT r.id, r.title, COUNT(*) AS events
       FROM detail_release_statistics d JOIN releases r ON r.id = d.release_id
      WHERE d.created_at BETWEEN ? AND ?
      GROUP BY r.id, r.title ORDER BY events DESC LIMIT 1`,
    [date + ' 00:00:00', date + ' 23:59:59']).catch(() => []);
  if (row?.title) return { id: row.id, title: row.title, events: row.events };
  // Second chance: a release PUBLISHED that day (the launch itself is the story)
  const [pub] = await q(siteId,
    `SELECT id, title FROM releases WHERE ${kind === 'newspress' ? '' : await relStatus(siteId)}published BETWEEN ? AND ?
      ORDER BY viewed DESC LIMIT 1`,
    [date + ' 00:00:00', date + ' 23:59:59']).catch(() => []);
  return pub?.title ? { id: pub.id, title: pub.title, events: null } : null;
}

// Find a client/company on a Newspress market by (partial) name.
export async function searchCompanies(siteId, query) {
  const site = getSite(siteId);
  if (dbKind(site) !== 'newspress') return [];
  if (dbIsDemo(site)) {
    const { startDate, endDate } = resolveRange('28d');
    const ov = demoLegacyOverview(site, 'newspress', startDate, endDate);
    return (ov.companies || []).filter((c) => c.name.toLowerCase().includes(String(query).toLowerCase())).slice(0, 10);
  }
  const like = '%' + String(query).trim() + '%';
  let rows = await q(siteId,
    `SELECT id, name FROM companies WHERE status = 1 AND deleted_at IS NULL AND name LIKE ?
      ORDER BY name LIMIT 10`, [like]).catch(() => []);
  if (!rows.length) {
    // token fallback for partial/misspelled names
    const t = String(query).trim().split(/\s+/)[0];
    if (t && t.length >= 3) {
      rows = await q(siteId,
        `SELECT id, name FROM companies WHERE status = 1 AND deleted_at IS NULL AND name LIKE ?
          ORDER BY name LIMIT 10`, ['%' + t.slice(0, Math.max(4, t.length - 2)) + '%']).catch(() => []);
    }
  }
  return rows;
}

// Every release in a period, DATE-ORDERED (what the content team sends to
// clients — "set by date and easy to find", not a top-10).
export async function releasesByDate(siteId, rangeStr, limit = 200) {
  const site = getSite(siteId);
  const kind = dbKind(site);
  if (!kind) return [];
  const { startDate, endDate } = resolveRange(rangeStr);
  if (dbIsDemo(site)) {
    return demoLegacyOverview(site, kind, startDate, endDate).releases
      .slice().sort((a, b) => String(b.published).localeCompare(String(a.published)));
  }
  const lim = Math.max(1, Math.min(500, Number(limit) || 200));
  const isNP = kind === 'newspress';
  const lwR = await relLocaleWhere(siteId, isNP ? 'r.' : '');
  return q(siteId,
    isNP
      ? `SELECT r.id, r.title, DATE(r.published) AS published, r.viewed, r.downloaded,
                r.added_to_basket AS basket, c.name AS companyName
           FROM releases r LEFT JOIN companies c ON c.id = r.company_id
          WHERE ${await relStatus(siteId, 'r.')}r.published BETWEEN ? AND ?${lwR.where} ORDER BY r.published DESC LIMIT ${lim}`
      : `SELECT id, title, DATE(published) AS published, viewed
           FROM releases WHERE ${await relStatus(siteId)}published BETWEEN ? AND ?${lwR.where}
          ORDER BY published DESC LIMIT ${lim}`,
    [startDate, endDate + ' 23:59:59', ...lwR.params]).catch(() => []);
}

// Quietest releases — bottom performers by views, only counting releases that
// have been live ≥ 30 days (so brand-new stories aren't unfairly flagged).
export async function quietReleases(siteId, rangeStr, limit = 10) {
  const site = getSite(siteId);
  const kind = dbKind(site);
  if (!kind) return [];
  const { startDate, endDate } = resolveRange(rangeStr);
  if (dbIsDemo(site)) {
    const ov = demoLegacyOverview(site, kind, startDate, endDate);
    return [...(ov.releases || [])].sort((a, b) => (a.viewed || 0) - (b.viewed || 0)).slice(0, limit);
  }
  const lim = Math.max(1, Math.min(25, Number(limit) || 10));
  const isNP = kind === 'newspress';
  // Live ≥30 days: published at least 30 days before the end of the range.
  const cutoff = new Date(new Date(endDate + 'T00:00:00Z').getTime() - 30 * 86400000).toISOString().slice(0, 10);
  // Short ranges would leave no qualifying window — look back 90 days instead.
  const startBound = cutoff > startDate
    ? startDate
    : new Date(new Date(cutoff + 'T00:00:00Z').getTime() - 90 * 86400000).toISOString().slice(0, 10);
  return q(siteId,
    isNP
      ? `SELECT r.id, r.title, DATE(r.published) AS published, r.viewed, r.downloaded,
                r.added_to_basket AS basket, c.name AS companyName
           FROM releases r LEFT JOIN companies c ON c.id = r.company_id
          WHERE ${await relStatus(siteId, 'r.')}r.published BETWEEN ? AND ? ORDER BY r.viewed ASC, r.published ASC LIMIT ${lim}`
      : `SELECT id, title, DATE(published) AS published, viewed
           FROM releases WHERE ${await relStatus(siteId)}published BETWEEN ? AND ?
          ORDER BY viewed ASC, published ASC LIMIT ${lim}`,
    [startBound, (cutoff < endDate ? cutoff : endDate) + ' 23:59:59']).catch(() => []);
}

// Full lists behind the "View all" popups — the same queries as the overview
// cards but with a caller-controlled limit (≤500) so the UI can paginate
// and search the complete ranking, not just the top 10.
export async function fullList(siteId, what, rangeStr, limit = 200, companyId = null) {
  const site = getSite(siteId);
  const kind = dbKind(site);
  if (!kind) return [];
  const lim = Math.max(1, Math.min(500, Number(limit) || 200));
  if (what === 'journalists') return journalistRanking(siteId, rangeStr, { limit: lim, companyId });
  const { startDate, endDate } = resolveRange(rangeStr || '28d');
  const cf = companyId ? Number(companyId) : null;
  const isNP = kind === 'newspress';
  if (dbIsDemo(site)) {
    const ov = demoLegacyOverview(site, kind, startDate, endDate, companyId);
    const map = {
      publications: ov.topPublications || [],
      media: ov.media || [], documents: ov.documents || [],
      videos: (ov.media || []).filter((m) => m.type === 'video'),
      models: ov.models || [], categories: ov.categories || [], banners: ov.banners || [],
    };
    // Demo media may not include videos — synthesize a few so the Videos
    // rankings always demonstrate (viewed ≫ downloaded, like real video assets)
    if (what === 'videos' && !map.videos.length) {
      const seed = [...String(siteId)].reduce((a, c) => (a * 31 + c.charCodeAt(0)) >>> 0, 7);
      map.videos = ['Launch film B-roll', 'Dynamic on-road footage', 'Interior walkthrough', 'Track test reel', 'Design story'].map((name, i) => ({
        id: 9100 + i, name, type: 'video',
        viewed: Math.max(60, 900 - i * 160 + (seed % 90)), downloaded: Math.max(3, 40 - i * 6),
        releaseTitle: (ov.releases || [])[i]?.title || null,
      }));
    }
    return (map[what] || []).slice(0, lim);
  }
  const D = [startDate, endDate + ' 23:59:59'];
  switch (what) {
    case 'publications':
      if (!isNP) return [];
      return q(siteId,
        `SELECT p.id, p.name, COUNT(*) AS events
           FROM detail_release_statistics d
           JOIN publication_users pu ON pu.user_id = d.user_id
           JOIN publications p ON p.id = pu.publication_id
          WHERE d.created_at BETWEEN ? AND ?
          GROUP BY p.id, p.name ORDER BY events DESC LIMIT ${lim}`, D).catch(() => []);
    case 'media':
    case 'videos':
    case 'documents': {
      const join = cf && isNP ? 'JOIN media_releases mr ON mr.media_id = m.id JOIN releases r ON r.id = mr.release_id AND r.company_id = ?' : '';
      const W = what === 'videos' ? 'm.status = 1 AND m.type = 2'
        : what === 'documents' ? 'm.type = 3'
        : 'm.status = 1';
      const datedRows = await datedMediaList(siteId, rangeStr, what === 'videos' ? 'video' : what === 'documents' ? 'document' : null, lim);
      if (datedRows && datedRows.length) return datedRows;
      const NM2 = MNAME(await hasMediaTitle(siteId).catch(() => false));
      const MREL2 = MREL_FOR(await mediaRelPivot(siteId).catch(() => null));
      const mc2 = await mediaCols(siteId);
      const BASKET = mc2.has('add_to_basket') ? ', m.add_to_basket AS basketAdds' : '';
      const rows = await q(siteId,
        `SELECT m.id, ${NM2} AS name, m.name AS code, m.type, m.viewed, m.downloaded${BASKET}, ${MREL2} FROM media m ${join}
          WHERE ${W} ORDER BY ${MRANK} DESC LIMIT ${lim}`,
        cf && isNP ? [cf] : []).catch(() => q(siteId,
        `SELECT m.id, m.name, m.type, m.viewed, m.downloaded FROM media m ${join}
          WHERE ${W} ORDER BY m.downloaded DESC LIMIT ${lim}`,
        cf && isNP ? [cf] : []).catch(() => []));
      return rows.map((m) => ({ ...m, type: MEDIA_TYPE[m.type] || (what === 'documents' ? 'document' : 'misc') }));
    }
    case 'models': {
      if (isNP) return [];
      try {
        const mi = await modelInterest(siteId, rangeStr);
        const rows2 = (mi?.models || []).filter((m) => m.pageViews > 0)
          .map((m) => ({ id: m.id, name: m.name, viewed: m.pageViews }));
        if (rows2.length) return rows2.slice(0, lim);
      } catch { /* lifetime fallback */ }
      return q(siteId, `SELECT id, name, viewed FROM models WHERE status = 1 ORDER BY viewed DESC LIMIT ${lim}`).catch(() => []);
    }
    case 'categories': {
      const pivot = isNP ? 'category_releases' : 'release_categories';
      return q(siteId,
        `SELECT c.id, c.name, COUNT(p.release_id) AS releases
           FROM categories c JOIN ${pivot} p ON p.category_id = c.id
           JOIN releases r ON r.id = p.release_id AND ${await relStatus(siteId, 'r.')}r.published BETWEEN ? AND ?
          GROUP BY c.id, c.name ORDER BY releases DESC LIMIT ${lim}`, D).catch(() => []);
    }
    case 'banners':
      if (!isNP) return [];
      return q(siteId,
        `SELECT id, title, clicked FROM banners WHERE status = 1 ${cf ? 'AND company_id = ?' : ''}
          ORDER BY clicked DESC LIMIT ${lim}`, cf ? [cf] : []).catch(() => []);
  }
  return [];
}

// Per-recipient events for one campaign (detail_email_statistics).
export async function campaignRecipients(siteId, campaignId) {
  const site = getSite(siteId);
  if (!dbKind(site)) return null;
  if (dbIsDemo(site)) {
    const seed = [...String(siteId + campaignId)].reduce((a, c) => (a * 31 + c.charCodeAt(0)) >>> 0, 7);
    return Array.from({ length: 24 }, (_, i) => ({
      email: `journalist${(seed + i) % 90}@${['dailynews.co.uk', 'motoringweekly.com', 'pressdesk.io'][(seed + i) % 3]}`,
      delivered: 1, opened: (seed + i) % 3 !== 0 ? 1 : 0,
      clicked: (seed + i) % 4 === 0 ? 1 : 0, unsubscribed: (seed + i) % 37 === 0 ? 1 : 0,
    }));
  }
  // delivered is implied by any open/click — some imports miss the delivered
  // event itself, which used to show contradictory "clicked but not delivered".
  return q(siteId,
    `SELECT email,
            MAX(event IN (7, 1, 2)) AS delivered, MAX(event = 1) AS opened,
            MAX(event = 2) AS clicked, MAX(event = 3) AS unsubscribed
       FROM detail_email_statistics WHERE campaign_id = ?
      GROUP BY email ORDER BY clicked DESC, opened DESC LIMIT 400`, [campaignId]).catch(() => []);
}

// Releases belonging to one category / model / company — the taxonomy drill.
export async function releasesByTaxon(siteId, taxon, id, rangeStr) {
  const site = getSite(siteId);
  const kind = dbKind(site);
  if (!kind) return [];
  const { startDate, endDate } = resolveRange(rangeStr || '90d');
  if (dbIsDemo(site)) {
    const ov = demoLegacyOverview(site, kind, startDate, endDate);
    return ov.releases.slice(0, 6);
  }
  const isNP = kind === 'newspress';
  let joinSql;
  if (taxon === 'category') joinSql = `JOIN ${isNP ? 'category_releases' : 'release_categories'} p ON p.release_id = r.id AND p.category_id = ?`;
  else if (taxon === 'model' && !isNP) joinSql = `JOIN release_models p ON p.release_id = r.id AND p.model_id = ?`;
  else if (taxon === 'company' && isNP) joinSql = ``;
  else return [];
  const where = taxon === 'company' ? 'AND r.company_id = ?' : '';
  const lwt = await relLocaleWhere(siteId, 'r.');
  const inPeriod = await q(siteId,
    `SELECT r.id, r.title, DATE(r.published) AS published, r.viewed${isNP ? ', r.downloaded' : ''}
       FROM releases r ${joinSql}
      WHERE ${await relStatus(siteId, 'r.')}r.published BETWEEN ? AND ? ${where}${lwt.where}
      ORDER BY r.viewed DESC LIMIT 25`,
    taxon === 'company' ? [startDate, endDate + ' 23:59:59', id, ...lwt.params] : [id, startDate, endDate + ' 23:59:59', ...lwt.params]).catch(() => []);
  if (inPeriod.length) return inPeriod;
  // Nothing published in the window (the model page still gets viewed) —
  // show the taxon's most recent releases instead of a dead-end empty modal.
  const recent = await q(siteId,
    `SELECT r.id, r.title, DATE(r.published) AS published, r.viewed${isNP ? ', r.downloaded' : ''}
       FROM releases r ${joinSql}
      WHERE ${await relStatus(siteId, 'r.')}1=1 ${where}${lwt.where}
      ORDER BY r.published DESC LIMIT 8`,
    taxon === 'company' ? [id, ...lwt.params] : [id, ...lwt.params]).catch(() => []);
  if (recent.length) recent.forEach((r) => (r._recentFallback = true));
  return recent;
}


// Free-text release search on TITLES in the reports DB (for the AI + UI).
export async function searchReleases(siteId, query, limit = 15) {
  const site = getSite(siteId);
  const kind = dbKind(site);
  if (!kind) return [];
  if (dbIsDemo(site)) {
    const { startDate, endDate } = resolveRange('12m');
    return demoLegacyOverview(site, kind, startDate, endDate).releases
      .filter((r) => {
        const t = r.title.toLowerCase();
        const qq = String(query).toLowerCase();
        if (t.includes(qq)) return true;
        const toks = qq.split(/\s+/).filter((x) => x.length >= 3 && !['the', 'and', 'release', 'press', 'news', 'story'].includes(x));
        if (toks.length && toks.every((x) => t.includes(x))) return true;
        const best = toks.sort((a, b) => b.length - a.length)[0];
        return Boolean(best && t.includes(best));
      }).slice(0, limit);
  }
  const isNP = kind === 'newspress';
  const lim = Number(limit) || 15;
  const RSs = await relStatus(siteId, 'r.');
  const runLike = (where, params) => q(siteId,
    isNP
      ? `SELECT r.id, r.title, DATE(r.published) AS published, r.viewed, r.downloaded, c.name AS companyName
           FROM releases r LEFT JOIN companies c ON c.id = r.company_id
          WHERE ${RSs}${where} ORDER BY r.published DESC LIMIT ${lim}`
      : `SELECT id, title, DATE(published) AS published, viewed FROM releases
          WHERE ${(RSs + where).replace(/r\./g, '')} ORDER BY published DESC LIMIT ${lim}`,
    params).catch(() => []);
  // 1) whole phrase; 2) ALL words anywhere in the title (order-independent);
  // 3) the most distinctive single word. Never fail on word order again.
  let rows = await runLike('r.title LIKE ?', ['%' + query + '%']);
  const tokens = String(query).split(/\s+/).map((t) => t.trim()).filter((t) => t.length >= 3 && !/^(the|and|release|press|news)$/i.test(t));
  if (!rows.length && tokens.length > 1) {
    rows = await runLike(tokens.map(() => 'r.title LIKE ?').join(' AND '), tokens.map((t) => '%' + t + '%'));
  }
  if (!rows.length && tokens.length) {
    const best = tokens.sort((a, b) => b.length - a.length)[0];
    rows = await runLike('r.title LIKE ?', ['%' + best + '%']);
  }
  return rows;
}


// Kind-aware SQL fragments — Newspress users are name/surname with numeric
// type (3 = journalist); Media/newsroom users are first_name/last_name with a
// free-text type and no publications tables.
const nameSql = (isNP) => isNP
  ? `TRIM(CONCAT(COALESCE(u.name,''), ' ', COALESCE(u.surname,'')))`
  : `TRIM(CONCAT(COALESCE(u.first_name,''), ' ', COALESCE(u.last_name,'')))`;

// Find a person (journalist/consumer) by name or email — fuzzy-ish.
export async function searchPeople(siteId, query) {
  const site = getSite(siteId);
  const kind = dbKind(site);
  if (!kind) return [];
  if (dbIsDemo(site)) return [{ id: 1, name: 'Sam', surname: 'Ford', email: 'sam.ford@pressdesk.io', type: 3 }];
  const isNP = kind === 'newspress';
  const tryQ = (like) => q(siteId,
    isNP
      ? `SELECT id, name, surname, email, type FROM users
          WHERE CONCAT(COALESCE(name,''),' ',COALESCE(surname,'')) LIKE ? OR email LIKE ?
          ORDER BY type LIMIT 10`
      : `SELECT id, first_name AS name, last_name AS surname, email, type FROM users
          WHERE CONCAT(COALESCE(first_name,''),' ',COALESCE(last_name,'')) LIKE ? OR email LIKE ?
          LIMIT 10`, [like, like]).catch(() => []);
  let rows = await tryQ('%' + query + '%');
  if (!rows.length) {
    for (const t of String(query).split(/\s+/).filter((x) => x.length >= 3)) {
      rows = await tryQ('%' + t + '%');
      if (rows.length) break;
      rows = await tryQ('%' + t.slice(0, Math.max(4, t.length - 2)) + '%'); // typo tolerance
      if (rows.length) break;
    }
  }
  return rows;
}

// Everything one person did: releases they viewed/downloaded + email events.
export async function personActivity(siteId, userId, email) {
  const site = getSite(siteId);
  if (!dbKind(site)) return null;
  if (dbIsDemo(site)) return { releases: [{ id: 1234, title: 'Record quarter for UK deliveries', views: 6, downloads: 2, lastSeen: '2026-07-20' }], emailEvents: [], publications: ['The Daily Motor'] };
  const releases = await q(siteId,
    `SELECT r.id, r.title, SUM(d.action = 1) AS views, SUM(d.action = 2) AS downloads, MAX(d.created_at) AS lastSeen
       FROM detail_release_statistics d JOIN releases r ON r.id = d.release_id
      WHERE d.user_id = ? GROUP BY r.id, r.title ORDER BY lastSeen DESC LIMIT 25`, [userId]).catch(() => []);
  const publications = (await q(siteId,
    `SELECT p.name FROM publications p JOIN publication_users pu ON pu.publication_id = p.id WHERE pu.user_id = ?`,
    [userId]).catch(() => [])).map((r) => r.name);
  const emailEvents = email ? await q(siteId,
    `SELECT campaign_id AS campaignId, event, DATE(date_event) AS date FROM detail_email_statistics
      WHERE email = ? ORDER BY date_event DESC LIMIT 50`, [email]).catch(() => []) : [];
  // Which ASSETS this journalist downloaded (media event log, human titles)
  const NM4 = MNAME(await hasMediaTitle(siteId).catch(() => false));
  const mediaDownloads = await q(siteId,
    `SELECT s.media_id AS id, MAX(${NM4}) AS name, MAX(m.type) AS typeId,
            SUM(s.action = 2) AS downloads, MAX(s.date_event) AS lastSeen
       FROM detail_media_statistics s JOIN media m ON m.id = s.media_id
      WHERE s.user_id = ? AND s.action = 2
      GROUP BY s.media_id ORDER BY lastSeen DESC LIMIT 20`, [userId]).catch(() => []);
  return { releases, publications, emailEvents,
           mediaDownloads: mediaDownloads.map((r) => ({ id: r.id, name: r.name, type: MEDIA_TYPE[r.typeId] || 'misc',
             downloads: Number(r.downloads) || 0, lastSeen: r.lastSeen })) };
}

// BULK: ranked journalist activity over a period — top N (up to 500) registered
// journalists by release views + downloads, with their publications.
export async function journalistRanking(siteId, rangeStr, { limit = 100, companyId = null } = {}) {
  const site = getSite(siteId);
  if (!dbKind(site)) return [];
  const lim = Math.max(1, Math.min(500, Number(limit) || 100));
  const { startDate, endDate } = resolveRange(rangeStr || '28d');
  if (dbIsDemo(site)) {
    const seed = [...String(siteId)].reduce((a, c) => (a * 31 + c.charCodeAt(0)) >>> 0, 7);
    const first = ['Sam', 'Priya', 'Tom', 'Elena', 'Marcus', 'Jade', 'Oliver', 'Nina', 'Ravi', 'Clara'];
    const last = ['Ford', 'Nair', 'Whitfield', 'Ruiz', 'Webb', 'Osei', 'Grant', 'Kovacs', 'Menon', 'Doyle'];
    return Array.from({ length: Math.min(lim, 100) }, (_, i) => ({
      id: 100 + i, name: `${first[(seed + i) % 10]} ${last[(seed + i * 3) % 10]}`,
      email: `${first[(seed + i) % 10]}.${last[(seed + i * 3) % 10]}@pressdesk.io`.toLowerCase(),
      views: Math.max(1, 140 - i * 2 + ((seed >> (i % 20)) % 9)), downloads: Math.max(0, 40 - i + ((seed >> (i % 16)) % 5)),
      events: 0, publications: ['The Daily Motor', 'Fleet World', 'Auto Express Weekly'][(seed + i) % 3], lastSeen: endDate,
    })).map((r) => ({ ...r, events: r.views + r.downloads }));
  }
  const cf = companyId ? Number(companyId) : null;
  const isNP = dbKind(site) === 'newspress';
  return q(siteId,
    `SELECT u.id, ${nameSql(isNP)} AS name, u.email,
            SUM(d.action = 1) AS views, SUM(d.action = 2) AS downloads, COUNT(*) AS events,
            DATE(MAX(d.created_at)) AS lastSeen,
            ${isNP ? `(SELECT GROUP_CONCAT(DISTINCT p.name ORDER BY p.name SEPARATOR ', ')
               FROM publication_users pu JOIN publications p ON p.id = pu.publication_id
              WHERE pu.user_id = u.id)` : 'MAX(u.type)'} AS publications
       FROM detail_release_statistics d
       JOIN users u ON u.id = d.user_id
       ${cf && isNP ? 'JOIN releases r ON r.id = d.release_id AND r.company_id = ?' : ''}
      WHERE ${isNP ? 'u.type = 3 AND ' : ''}d.created_at BETWEEN ? AND ?
      GROUP BY u.id
      ORDER BY events DESC
      LIMIT ${lim}`,
    cf && isNP ? [cf, startDate, endDate + ' 23:59:59'] : [startDate, endDate + ' 23:59:59']).catch(() => []);
}

// BULK: everyone who opened/clicked ANY email campaign in a period, deduped by
// address and joined to the users table for names/type and publications.
export async function emailEngagers(siteId, rangeStr, { limit = 100, clickedOnly = false, journalistsOnly = false, companyId = null } = {}) {
  const site = getSite(siteId);
  if (!dbKind(site)) return [];
  const lim = Math.max(1, Math.min(500, Number(limit) || 100));
  const { startDate, endDate } = resolveRange(rangeStr || '28d');
  if (dbIsDemo(site)) {
    const base = await journalistRanking(siteId, rangeStr, { limit: Math.min(lim, 40) });
    return base.map((r, i) => ({
      email: r.email, name: r.name, type: 3, publications: r.publications,
      opens: Math.max(1, 12 - (i % 9)), clicks: clickedOnly ? Math.max(1, 5 - (i % 5)) : Math.max(0, 4 - (i % 6)),
      campaigns: 1 + (i % 4),
    }));
  }
  const cf = companyId ? Number(companyId) : null;
  const camps = await q(siteId,
    cf
      ? `SELECT DISTINCT e.campaign_id AS id FROM emails e
           JOIN email_releases er ON er.email_id = e.id
           JOIN releases r ON r.id = er.release_id AND r.company_id = ?
          WHERE e.sent_date BETWEEN ? AND ?`
      : `SELECT DISTINCT campaign_id AS id FROM emails WHERE sent_date BETWEEN ? AND ?`,
    cf ? [cf, startDate, endDate + ' 23:59:59'] : [startDate, endDate + ' 23:59:59']).catch(() => []);
  if (!camps.length) return [];
  const ids = camps.map((c) => c.id).filter((x) => x != null).slice(0, 200);
  const isNP = dbKind(site) === 'newspress';
  return q(siteId,
    `SELECT s.email,
            MAX(${nameSql(isNP)}) AS name,
            MAX(u.type) AS type,
            SUM(s.event = 1) AS opens, SUM(s.event = 2) AS clicks,
            COUNT(DISTINCT s.campaign_id) AS campaigns,
            ${isNP ? `(SELECT GROUP_CONCAT(DISTINCT p.name ORDER BY p.name SEPARATOR ', ')
               FROM publication_users pu
               JOIN publications p ON p.id = pu.publication_id
               JOIN users u2 ON u2.id = pu.user_id
              WHERE u2.email = s.email)` : 'NULL'} AS publications
       FROM detail_email_statistics s
       LEFT JOIN users u ON u.email = s.email
      WHERE s.campaign_id IN (${ids.map(() => '?').join(',')}) AND s.event IN (1, 2)
        ${journalistsOnly && isNP ? 'AND u.type = 3' : ''}
      GROUP BY s.email
      ${clickedOnly ? 'HAVING clicks > 0' : ''}
      ORDER BY clicks DESC, opens DESC
      LIMIT ${lim}`, ids).catch(() => []);
}

// Drill-down: one PUBLICATION — its journalists (with period activity) and the
// releases its people read most in the period.
export async function publicationDetail(siteId, pubId, rangeStr) {
  const site = getSite(siteId);
  if (!dbKind(site)) return null;
  if (dbKind(site) !== 'newspress' && !dbIsDemo(site)) return null; // Media DBs have no publications tables
  const { startDate, endDate } = resolveRange(rangeStr || '28d');
  if (dbIsDemo(site)) {
    const js = await journalistRanking(siteId, rangeStr, { limit: 8 });
    return {
      id: Number(pubId), name: 'The Daily Motor',
      journalists: js.map((j) => ({ userId: j.id, name: j.name, email: j.email, views: j.views, downloads: j.downloads, events: j.events, lastSeen: j.lastSeen })),
      topReleases: [{ id: 1234, title: 'Record quarter for UK deliveries', events: 18 }, { id: 2345, title: 'Electric line-up expands with new variant', events: 11 }],
    };
  }
  const [[pub], journalists, topReleases] = await Promise.all([
    q(siteId, `SELECT id, name FROM publications WHERE id = ?`, [pubId]).catch(() => [null]),
    q(siteId,
      `SELECT u.id AS userId, TRIM(CONCAT(COALESCE(u.name,''), ' ', COALESCE(u.surname,''))) AS name, u.email,
              SUM(d.action = 1) AS views, SUM(d.action = 2) AS downloads, COUNT(d.user_id) AS events,
              DATE(MAX(d.created_at)) AS lastSeen
         FROM publication_users pu
         JOIN users u ON u.id = pu.user_id
         LEFT JOIN detail_release_statistics d ON d.user_id = u.id AND d.created_at BETWEEN ? AND ?
        WHERE pu.publication_id = ?
        GROUP BY u.id, u.name, u.surname, u.email
        ORDER BY events DESC, name ASC LIMIT 50`,
      [startDate, endDate + ' 23:59:59', pubId]).catch(() => []),
    q(siteId,
      `SELECT r.id, r.title, COUNT(*) AS events
         FROM detail_release_statistics d
         JOIN publication_users pu ON pu.user_id = d.user_id AND pu.publication_id = ?
         JOIN releases r ON r.id = d.release_id
        WHERE d.created_at BETWEEN ? AND ?
        GROUP BY r.id, r.title ORDER BY events DESC LIMIT 10`,
      [pubId, startDate, endDate + ' 23:59:59']).catch(() => []),
  ]);
  return { id: Number(pubId), name: pub?.name || `Publication #${pubId}`, journalists, topReleases };
}

// Drill-down: one MEDIA ASSET — its counters plus the releases it belongs to.
export async function mediaDetail(siteId, mediaId) {
  const site = getSite(siteId);
  if (!dbKind(site)) return null;
  if (dbIsDemo(site)) {
    return {
      id: Number(mediaId), name: 'front-quarter-hero.jpg', type: 'image', viewed: 3200, downloaded: 950,
      releases: [{ id: 1234, title: 'Record quarter for UK deliveries', published: '2026-07-12' }],
    };
  }
  const [[m], releases] = await Promise.all([
    q(siteId, `SELECT id, name, type, viewed, downloaded FROM media WHERE id = ?`, [mediaId]).catch(() => [null]),
    q(siteId,
      `SELECT r.id, r.title, DATE(r.published) AS published, r.viewed
         FROM media_releases mr JOIN releases r ON r.id = mr.release_id
        WHERE mr.media_id = ? ORDER BY r.published DESC LIMIT 10`, [mediaId]).catch(() => []),
  ]);
  if (!m) return null;
  return { ...m, type: MEDIA_TYPE[m.type] || m.type || 'misc', releases };
}
