// Unified analytics data layer.
// Each site in sites.config.json either has a real GA4 propertyId (numeric
// string) or "demo". Real properties are queried live through the GA4 Data
// API using the service-account credentials; demo sites are served from the
// deterministic generator so the app works out of the box.

import fs from 'node:fs';
import path from 'node:path';
import { fileURLToPath } from 'node:url';
import { demoTimeseries, demoTotals, demoBreakdown, demoPagesByPrefix, demoFiltered, demoPageTrend, demoSearchTerms, demoFlows, demoOutbound } from './demo-data.js';
import { runReport } from './ga4-rest.js';
import { currentMarkets } from './market.js';

const __dirname = path.dirname(fileURLToPath(import.meta.url));
const configPath = path.join(__dirname, '..', 'sites.config.json');

export function loadSites() {
  const raw = JSON.parse(fs.readFileSync(configPath, 'utf8'));
  return raw.sites;
}

export function getSite(siteId) {
  const site = loadSites().find((s) => s.id === siteId);
  if (!site) throw new Error(`Unknown site: ${siteId}`);
  return site;
}

const DEMO = process.env.DEMO_MODE === '1';
export function isDemo(site) {
  return DEMO || !site.propertyId || site.propertyId === 'demo';
}

// ---- tiny cache so repeated dashboard loads / chat questions don't burn quota
const cache = new Map();
const CACHE_TTL_MS = 10 * 60 * 1000;
function cached(key, fn) {
  const hit = cache.get(key);
  if (hit && Date.now() - hit.t < CACHE_TTL_MS) return hit.v;
  const v = fn();
  cache.set(key, { t: Date.now(), v });
  return v;
}

const METRIC_IDS = ['sessions', 'totalUsers', 'newUsers', 'screenPageViews', 'engagedSessions', 'conversions', 'averageSessionDuration'];

// ---- demo-mode market shim: real properties are filtered inside runReport;
// generated demo data gets a deterministic per-selection scale instead, and
// country breakdowns are filtered to the selected markets.
function dmScale() {
  const m = currentMarkets();
  if (!m) return null;
  const h = [...m.join('|')].reduce((a, c2) => (a * 31 + c2.charCodeAt(0)) >>> 0, 7);
  return Math.min(0.92, 0.3 + (m.length - 1) * 0.17 + (h % 15) / 100);
}
const DM_KEYS = /^(sessions|totalUsers|newUsers|screenPageViews|engagedSessions|conversions|users|views|clicks|events|searches|eventCount|pageviews|prevSessions)$/;
function dmRows(rows) {
  const f = dmScale();
  if (!f || !Array.isArray(rows)) return rows;
  return rows.map((r) => {
    const o = { ...r };
    for (const k of Object.keys(o)) if (typeof o[k] === 'number' && DM_KEYS.test(k)) o[k] = Math.max(1, Math.round(o[k] * f));
    return o;
  });
}
function dmObj(o0) {
  const f = dmScale();
  if (!f || !o0) return o0;
  const o = { ...o0 };
  for (const k of Object.keys(o)) if (typeof o[k] === 'number' && DM_KEYS.test(k)) o[k] = Math.max(1, Math.round(o[k] * f));
  return o;
}
function dmCountryRows(rows) {
  const m = currentMarkets();
  if (!m) return rows;
  const low = m.map((x) => x.toLowerCase());
  const hit = rows.filter((r) => low.includes(String(r.dimensionValue).toLowerCase()));
  return hit.length ? hit : rows.slice(0, m.length); // demo data may lack the exact names
}

// Daily timeseries of core metrics for one site.
export async function timeseries(siteId, startDate, endDate) {
  const site = getSite(siteId);
  if (isDemo(site)) return dmRows(cached(`ts|${siteId}|${startDate}|${endDate}`, () => demoTimeseries(site, startDate, endDate)));

  const resp = await runReport(site.propertyId, {
    dateRanges: [{ startDate, endDate }],
    dimensions: [{ name: 'date' }],
    metrics: METRIC_IDS.map((name) => ({ name })),
    orderBys: [{ dimension: { dimensionName: 'date' } }],
    limit: 100000,
  });
  return (resp.rows || []).map((row) => {
    const d = row.dimensionValues[0].value; // YYYYMMDD
    const m = row.metricValues.map((v) => Number(v.value));
    return {
      date: `${d.slice(0, 4)}-${d.slice(4, 6)}-${d.slice(6, 8)}`,
      sessions: m[0], totalUsers: m[1], newUsers: m[2], screenPageViews: m[3],
      engagedSessions: m[4], conversions: m[5], avgSessionDuration: m[6],
    };
  });
}

// Totals of core metrics for one site over a range.
export async function totals(siteId, startDate, endDate) {
  const site = getSite(siteId);
  if (isDemo(site)) return dmObj(cached(`tot|${siteId}|${startDate}|${endDate}`, () => demoTotals(site, startDate, endDate)));

  const resp = await runReport(site.propertyId, {
    dateRanges: [{ startDate, endDate }],
    metrics: METRIC_IDS.map((name) => ({ name })),
  });
  const m = (resp.rows?.[0]?.metricValues || []).map((v) => Number(v.value));
  return {
    sessions: m[0] || 0, totalUsers: m[1] || 0, newUsers: m[2] || 0,
    screenPageViews: m[3] || 0, engagedSessions: m[4] || 0, conversions: m[5] || 0,
    engagementRate: m[0] ? (m[4] || 0) / m[0] : 0,
    avgSessionDuration: m[6] || 0,
  };
}

// Breakdown by a single dimension for one site.
// dimension ∈ sessionDefaultChannelGroup | deviceCategory | country | pagePath | sessionSource
// opts.pathPrefix (pagePath only): return pages *under* that path, e.g.
// pathPrefix '/releases' → '/releases/1652', '/releases/1634', …
export async function breakdown(siteId, dimension, startDate, endDate, limit = 12, opts = {}) {
  const site = getSite(siteId);
  const prefix = dimension === 'pagePath' && opts.pathPrefix
    ? String(opts.pathPrefix).replace(/\/+$/, '')
    : null;
  if (isDemo(site)) {
    if (prefix != null) {
      return dmRows(cached(`bdp|${siteId}|${prefix}|${startDate}|${endDate}`, () =>
        demoPagesByPrefix(site, prefix, startDate, endDate)
      )).slice(0, limit);
    }
    const demoRows = cached(`bd|${siteId}|${dimension}|${startDate}|${endDate}`, () =>
      demoBreakdown(site, dimension, startDate, endDate)
    );
    return (dimension === 'country' ? dmCountryRows(dmRows(demoRows)) : dmRows(demoRows)).slice(0, limit);
  }
  const isPages = dimension === 'pagePath';
  const metrics = isPages
    ? [{ name: 'screenPageViews' }, { name: 'sessions' }]
    : [{ name: 'sessions' }, { name: 'conversions' }];
  const body = {
    dateRanges: [{ startDate, endDate }],
    dimensions: [{ name: dimension }],
    metrics,
    orderBys: [{ metric: { metricName: metrics[0].name }, desc: true }],
    limit,
  };
  if (prefix != null) {
    body.dimensionFilter = {
      filter: {
        fieldName: 'pagePath',
        stringFilter: { matchType: 'BEGINS_WITH', value: prefix + '/' },
      },
    };
  }
  const resp = await runReport(site.propertyId, body);
  return (resp.rows || []).map((row) => {
    const out = { dimensionValue: row.dimensionValues[0].value };
    metrics.forEach((mt, i) => (out[mt.name] = Number(row.metricValues[i].value)));
    return out;
  });
}

// On-site search terms — what visitors typed into the site's search box.
// TWO collectors merged, because GA4's `searchTerm` dimension only captures
// sites that fire view_search_results (Enhanced measurement) — many properties
// under-report it badly. The search-page URLs themselves (?q=…, ?query=…)
// recover the rest.
export async function searchTerms(siteId, startDate, endDate, limit = 50) {
  const site = getSite(siteId);
  if (isDemo(site)) {
    return dmRows(cached(`st|${siteId}|${startDate}|${endDate}`, () => demoSearchTerms(site, startDate, endDate))).slice(0, limit);
  }
  const [evResp, urlResp] = await Promise.all([
    runReport(site.propertyId, {
      dateRanges: [{ startDate, endDate }],
      dimensions: [{ name: 'searchTerm' }],
      metrics: [{ name: 'eventCount' }, { name: 'totalUsers' }],
      dimensionFilter: {
        notExpression: { filter: { fieldName: 'searchTerm', stringFilter: { matchType: 'EXACT', value: '(not set)' } } },
      },
      orderBys: [{ metric: { metricName: 'eventCount' }, desc: true }],
      limit: 250,
    }).catch(() => ({ rows: [] })),
    runReport(site.propertyId, {
      dateRanges: [{ startDate, endDate }],
      dimensions: [{ name: 'pagePathPlusQueryString' }],
      metrics: [{ name: 'screenPageViews' }, { name: 'totalUsers' }],
      dimensionFilter: {
        filter: { fieldName: 'pagePathPlusQueryString', stringFilter: { matchType: 'CONTAINS', value: 'search', caseSensitive: false } },
      },
      orderBys: [{ metric: { metricName: 'screenPageViews' }, desc: true }],
      limit: 500,
    }).catch(() => ({ rows: [] })),
  ]);
  const acc = new Map();
  const add = (term, searches, users) => {
    const clean = String(term || '').trim();
    const k = clean.toLowerCase();
    if (!k || k === '(not set)') return;
    const cur = acc.get(k) || { term: clean, searches: 0, users: 0 };
    cur.searches += searches;
    cur.users = Math.max(cur.users, users); // users can't be summed across sources
    acc.set(k, cur);
  };
  for (const row of evResp.rows || []) {
    add(row.dimensionValues[0].value, Number(row.metricValues[0].value), Number(row.metricValues[1].value));
  }
  for (const row of urlResp.rows || []) {
    const url = row.dimensionValues[0].value;
    const qs = String(url).split('?')[1];
    if (!qs) continue;
    let term = null;
    try {
      const params = new URLSearchParams(qs);
      for (const key of ['q', 'query', 's', 'search', 'term', 'keyword', 'searchterm', 'search_term', 'keywords']) {
        const v = params.get(key);
        if (v && v.trim()) { term = v; break; }
      }
    } catch { /* malformed query string */ }
    if (term) add(term, Number(row.metricValues[0].value), Number(row.metricValues[1].value));
  }
  return [...acc.values()].sort((a, b) => b.searches - a.searches).slice(0, limit);
}

// Channel mix — sessions per GA4 default channel group, current vs previous
// period, so movements in channels the client monitors (e.g. Organic Video)
// are always visible. Organic Video is pinned into the result even at zero.
export async function channelMix(siteId, rangeStr, compare = 'previous') {
  const site = getSite(siteId);
  const { startDate, endDate } = resolveRange(rangeStr);
  // compare='year' → same dates LAST YEAR (true year-on-year, e.g. "has
  // organic video grown yearly"); default is the immediately-previous period.
  const shiftYear = (d) => { const x = new Date(d + 'T00:00:00Z'); x.setUTCFullYear(x.getUTCFullYear() - 1); return x.toISOString().slice(0, 10); };
  const prev = compare === 'year'
    ? { startDate: shiftYear(startDate), endDate: shiftYear(endDate) }
    : previousRange({ startDate, endDate });
  if (isDemo(site)) {
    const mk = (sd, ed) => cached(`bd|${siteId}|sessionDefaultChannelGroup|${sd}|${ed}`, () =>
      demoBreakdown(site, 'sessionDefaultChannelGroup', sd, ed));
    const cur = mk(startDate, endDate), pv = mk(prev.startDate, prev.endDate);
    const pmap = Object.fromEntries(pv.map((x) => [x.dimensionValue, x.sessions]));
    const rows = cur.map((x) => ({ channel: x.dimensionValue, sessions: x.sessions, users: Math.round(x.sessions * 0.8), prevSessions: Math.round((pmap[x.dimensionValue] || x.sessions) * 0.92) }));
    if (!rows.some((r) => r.channel === 'Organic Video')) rows.push({ channel: 'Organic Video', sessions: 840, users: 704, prevSessions: compare === 'year' ? 380 : 512 });
    return { startDate, endDate, compare, previousPeriod: prev, channels: dmRows(rows) };
  }
  const q = (sd, ed) => runReport(site.propertyId, {
    dateRanges: [{ startDate: sd, endDate: ed }],
    dimensions: [{ name: 'sessionDefaultChannelGroup' }],
    metrics: [{ name: 'sessions' }, { name: 'totalUsers' }],
    orderBys: [{ metric: { metricName: 'sessions' }, desc: true }],
    limit: 20,
  });
  const [curR, pvR] = await Promise.all([q(startDate, endDate), q(prev.startDate, prev.endDate)]);
  const parse = (resp) => (resp.rows || []).map((row) => ({
    channel: row.dimensionValues[0].value,
    sessions: Number(row.metricValues[0].value),
    users: Number(row.metricValues[1].value),
  }));
  const c = parse(curR), p = parse(pvR);
  const pmap = Object.fromEntries(p.map((x) => [x.channel, x.sessions]));
  const seen = new Set(c.map((x) => x.channel));
  const rows = c.map((x) => ({ ...x, prevSessions: pmap[x.channel] || 0 }));
  for (const x of p) if (!seen.has(x.channel)) rows.push({ channel: x.channel, sessions: 0, users: 0, prevSessions: x.sessions });
  if (!rows.some((r) => r.channel === 'Organic Video')) rows.push({ channel: 'Organic Video', sessions: 0, users: 0, prevSessions: 0 });
  return { startDate, endDate, compare, previousPeriod: prev, channels: rows };
}

// Year-on-year media engagement from GA4 — the platform's viewed/downloaded
// columns are LIFETIME counters (no dates), so yearly comparisons must come
// from GA4's dated events: video-page views, video player events, and
// file_download events bucketed by extension (video / image / document).
const EXT_BUCKET = (ext) => {
  const e = String(ext || '').toLowerCase();
  if (/^(mp4|mov|avi|m4v|webm|mkv|mpg|mpeg)$/.test(e)) return 'videos';
  if (/^(jpg|jpeg|png|gif|webp|tif|tiff|bmp|heic)$/.test(e)) return 'images';
  if (/^(pdf|docx?|xlsx?|pptx?|zip|txt|rtf)$/.test(e)) return 'documents';
  return 'other';
};
export async function mediaYearly(siteId) {
  const site = getSite(siteId);
  const today = new Date();
  const thisYear = today.getUTCFullYear();
  const years = [thisYear - 1, thisYear];
  const out = { years: {}, comparedThrough: today.toISOString().slice(5, 10) };
  if (isDemo(site)) {
    const seed = [...String(siteId)].reduce((a, c) => (a * 31 + c.charCodeAt(0)) >>> 0, 7);
    for (const [i, y] of years.entries()) {
      const f = i === 0 ? 1 : 1.35 + (seed % 20) / 100;
      out.years[y] = {
        videoPageViews: Math.round(21000 * f), videoEvents: { video_start: Math.round(5200 * f), video_progress: Math.round(3100 * f), video_complete: Math.round(1400 * f) },
        downloadsByType: { images: Math.round(10400 * f), videos: Math.round(900 * f), documents: Math.round(3900 * f), other: Math.round(160 * f) },
      };
    }
    out.note = 'Same-period comparison: each year is counted 1 Jan → today\'s date, so the years are directly comparable.';
    return out;
  }
  for (const y of years) {
    const startDate = `${y}-01-01`;
    // Same-period comparison: cut BOTH years at today's month-day.
    const endDate = `${y}${today.toISOString().slice(4, 10)}`;
    const [vidPages, vidEvents, dls] = await Promise.all([
      runReport(site.propertyId, {
        dateRanges: [{ startDate, endDate }],
        dimensions: [{ name: 'pagePath' }],
        metrics: [{ name: 'screenPageViews' }],
        dimensionFilter: { filter: { fieldName: 'pagePath', stringFilter: { matchType: 'CONTAINS', value: 'video', caseSensitive: false } } },
        limit: 1000,
      }).catch(() => ({ rows: [] })),
      runReport(site.propertyId, {
        dateRanges: [{ startDate, endDate }],
        dimensions: [{ name: 'eventName' }],
        metrics: [{ name: 'eventCount' }],
        dimensionFilter: { filter: { fieldName: 'eventName', inListFilter: { values: ['video_start', 'video_progress', 'video_complete'] } } },
        limit: 10,
      }).catch(() => ({ rows: [] })),
      runReport(site.propertyId, {
        dateRanges: [{ startDate, endDate }],
        dimensions: [{ name: 'fileExtension' }],
        metrics: [{ name: 'eventCount' }],
        dimensionFilter: { filter: { fieldName: 'eventName', stringFilter: { matchType: 'EXACT', value: 'file_download' } } },
        limit: 50,
      }).catch(() => ({ rows: [] })),
    ]);
    const videoPageViews = (vidPages.rows || []).reduce((a, r) => a + (Number(r.metricValues[0].value) || 0), 0);
    const videoEvents = Object.fromEntries((vidEvents.rows || []).map((r) => [r.dimensionValues[0].value, Number(r.metricValues[0].value) || 0]));
    const downloadsByType = { images: 0, videos: 0, documents: 0, other: 0 };
    for (const r of dls.rows || []) downloadsByType[EXT_BUCKET(r.dimensionValues[0].value)] += Number(r.metricValues[0].value) || 0;
    out.years[y] = { videoPageViews, videoEvents, downloadsByType };
  }
  out.note = `Same-period comparison: each year counted 1 Jan → ${out.comparedThrough}, so the years are directly comparable. GA4 counts consented visitors; video_start events require Enhanced Measurement video engagement (embedded YouTube) — zeros mean the site's player isn't instrumented, in which case video-PAGE views are the reliable yearly video metric.`;
  return out;
}

// Dated per-FILE download ranking from GA4 file_download events — works for
// whatever the tag actually captures (extension-classified or not).
export async function fileDownloadsRanked(siteId, rangeStr, limit = 50) {
  const site = getSite(siteId);
  const { startDate, endDate } = resolveRange(rangeStr || '28d');
  if (isDemo(site)) return [];
  const resp = await runReport(site.propertyId, {
    dateRanges: [{ startDate, endDate }],
    dimensions: [{ name: 'fileName' }, { name: 'fileExtension' }],
    metrics: [{ name: 'eventCount' }, { name: 'totalUsers' }],
    dimensionFilter: { filter: { fieldName: 'eventName', stringFilter: { matchType: 'EXACT', value: 'file_download' } } },
    orderBys: [{ metric: { metricName: 'eventCount' }, desc: true }],
    limit: Math.max(1, Math.min(500, limit)),
  }).catch(() => ({ rows: [] }));
  return (resp.rows || []).map((row) => ({
    fileName: row.dimensionValues[0].value,
    extension: row.dimensionValues[1].value,
    bucket: EXT_BUCKET(row.dimensionValues[1].value),
    downloads: Number(row.metricValues[0].value) || 0,
    users: Number(row.metricValues[1].value) || 0,
  }));
}

// What KIND of page a path is — used to explain the exit context in plain
// language ("they left after reading a press release / browsing photos…").
export function pageKind(p) {
  const x = String(p || '').toLowerCase();
  if (x === '/' || x === '' || x === '/home') return 'Homepage';
  if (/\/(releases?|news|articles?|stories|press-releases?)\/./.test(x)) return 'Press release';
  if (/video/.test(x)) return 'Video gallery';
  if (/photo|image|gallery/.test(x)) return 'Photo gallery';
  if (/download|document|spec|basket|cart/.test(x)) return 'Downloads & documents';
  if (/model|vehicle|lineup/.test(x)) return 'Model page';
  if (/executive|leadership|bios?\b/.test(x)) return 'Executives';
  if (/search/.test(x)) return 'Search';
  if (/contact/.test(x)) return 'Contacts';
  if (/subscri|register|signup|sign-up|alert/.test(x)) return 'Registration & alerts';
  return 'Other pages';
}

// Exit journeys — WHERE ON THE SITE visitors were when they clicked away.
// GA4's outbound-click event fires ON the page, so each event carries both
// the destination (linkUrl) and the page it happened on (pagePath). Joining
// the two — plus pageFlows' previous-page edges — reconstructs the 3-step
// journey: how they arrived → what they were reading → where they went.
export async function outboundJourneys(siteId, startDate, endDate) {
  const site = getSite(siteId);
  const brand = String(site.name || '').toLowerCase().split(' ')[0];
  const isOwn = (domain) => brand.length > 2 && String(domain).toLowerCase().includes(brand);
  if (isDemo(site)) {
    const seed = [...String(siteId)].reduce((a, c) => (a * 31 + c.charCodeAt(0)) >>> 0, 7);
    const dom = (site.url ? new URL(site.url).host.replace(/^www\./, '').split('.')[0] : 'acme') + '.com';
    const pages = [
      { page: '/releases/2411', clicks: 64 + (seed % 30) }, { page: '/releases/2398', clicks: 45 + (seed % 20) },
      { page: '/gallery/photos', clicks: 38 + (seed % 18) }, { page: '/models/ev9', clicks: 30 + (seed % 14) },
      { page: '/', clicks: 22 + (seed % 10) }, { page: '/gallery/videos', clicks: 17 + (seed % 8) },
      { page: '/releases/2377', clicks: 12 + (seed % 6) },
    ];
    const doms = [dom, 'youtube.com', 'instagram.com', dom, 'facebook.com', 'youtube.com', dom];
    const exits = dmRows(pages.map((p, i) => ({ page: p.page, domain: doms[i], url: 'https://' + doms[i] + '/x', clicks: p.clicks, users: Math.round(p.clicks * 0.8) })));
    return buildJourneys(exits, isOwn, {
      internal: [
        { from: '/', to: '/releases/2411', views: 220 }, { from: '/releases', to: '/releases/2411', views: 130 },
        { from: '/', to: '/releases/2398', views: 150 }, { from: '/releases/2411', to: '/gallery/photos', views: 90 },
        { from: '/', to: '/models/ev9', views: 80 },
      ],
    });
  }
  const [resp, flows] = await Promise.all([
    runReport(site.propertyId, {
      dateRanges: [{ startDate, endDate }],
      dimensions: [{ name: 'pagePath' }, { name: 'linkUrl' }],
      metrics: [{ name: 'eventCount' }, { name: 'totalUsers' }],
      dimensionFilter: {
        andGroup: { expressions: [
          { filter: { fieldName: 'eventName', stringFilter: { matchType: 'EXACT', value: 'click' } } },
          { notExpression: { filter: { fieldName: 'linkUrl', stringFilter: { matchType: 'EXACT', value: '' } } } },
        ] },
      },
      orderBys: [{ metric: { metricName: 'eventCount' }, desc: true }],
      limit: 1000,
    }),
    pageFlows(siteId, startDate, endDate).catch(() => ({ internal: [] })),
  ]);
  const exits = [];
  for (const row of resp.rows || []) {
    const page = row.dimensionValues[0].value;
    const url2 = row.dimensionValues[1].value;
    let domain = null;
    try { domain = new URL(url2).hostname.replace(/^www\./, ''); } catch { continue; }
    // skip self-links (same host as the media site itself)
    try { if (site.url && domain === new URL(site.url).hostname.replace(/^www\./, '')) continue; } catch { /* keep */ }
    exits.push({ page, domain, url: url2, clicks: Number(row.metricValues[0].value) || 0, users: Number(row.metricValues[1].value) || 0 });
  }
  return buildJourneys(exits, isOwn, flows);
}

function buildJourneys(exits, isOwn, flows) {
  const totalClicks = exits.reduce((a, x) => a + x.clicks, 0);
  const ownClicks = exits.filter((x) => isOwn(x.domain)).reduce((a, x) => a + x.clicks, 0);
  // per exit PAGE — what they were doing when they left
  const byPageM = new Map();
  for (const x of exits) {
    const cur = byPageM.get(x.page) || { page: x.page, kind: pageKind(x.page), clicks: 0, users: 0, ownClicks: 0, domains: new Map() };
    cur.clicks += x.clicks; cur.users += x.users;
    if (isOwn(x.domain)) cur.ownClicks += x.clicks;
    cur.domains.set(x.domain, (cur.domains.get(x.domain) || 0) + x.clicks);
    byPageM.set(x.page, cur);
  }
  const byPage = [...byPageM.values()]
    .map((p) => ({ ...p, domains: [...p.domains.entries()].map(([domain, clicks]) => ({ domain, clicks, own: isOwn(domain) })).sort((a, b) => b.clicks - a.clicks).slice(0, 6) }))
    .sort((a, b) => b.clicks - a.clicks);
  // per page KIND — the stage of the visit when they leave
  const byKindM = new Map();
  for (const p of byPage) {
    const cur = byKindM.get(p.kind) || { kind: p.kind, clicks: 0, ownClicks: 0, pages: 0 };
    cur.clicks += p.clicks; cur.ownClicks += p.ownClicks; cur.pages += 1;
    byKindM.set(p.kind, cur);
  }
  const byKind = [...byKindM.values()].sort((a, b) => b.clicks - a.clicks)
    .map((k) => ({ ...k, share: totalClicks ? Math.round((100 * k.clicks) / totalClicks) : 0 }));
  // handovers to the CONSUMER site specifically, with the arrival path
  const prevFor = (page) => (flows.internal || [])
    .filter((e) => e.to === page)
    .sort((a, b) => b.views - a.views)
    .slice(0, 4)
    .map((e) => ({ from: e.from, views: e.views }));
  const ownByPage = byPage.filter((p) => p.ownClicks > 0).slice(0, 15)
    .map((p) => ({ page: p.page, kind: p.kind, clicks: p.ownClicks, domains: p.domains.filter((d) => d.own), cameFrom: prevFor(p.page) }));
  return {
    collected: exits.length > 0,
    totalClicks, ownClicks,
    ownShare: totalClicks ? Math.round((100 * ownClicks) / totalClicks) : 0,
    byPage: byPage.slice(0, 30).map((p) => ({ ...p, cameFrom: prevFor(p.page) })),
    byKind,
    ownByPage,
  };
}

// Where the traffic to ONE page (usually a release) came from — real
// source/medium rows (google / organic, bing / organic, direct…), not the
// platform-vs-GA4 measurement table.
export async function pageTrafficSources(siteId, path, rangeStr, limit = 12) {
  const site = getSite(siteId);
  const { startDate, endDate } = resolveRange(rangeStr || '28d');
  const p = '/' + String(path || '').replace(/^\/+/, '');
  if (isDemo(site)) {
    const seed = [...String(siteId + p)].reduce((a, c) => (a * 31 + c.charCodeAt(0)) >>> 0, 7);
    return dmRows([
      { source: 'google', medium: 'organic', sessions: 300 + (seed % 400) },
      { source: '(direct)', medium: '(none)', sessions: 150 + (seed % 200) },
      { source: 'news.google.com', medium: 'referral', sessions: 80 + (seed % 90) },
      { source: 'bing', medium: 'organic', sessions: 40 + (seed % 50) },
      { source: 'linkedin.com', medium: 'referral', sessions: 20 + (seed % 30) },
    ]);
  }
  const resp = await runReport(site.propertyId, {
    dateRanges: [{ startDate, endDate }],
    dimensions: [{ name: 'sessionSource' }, { name: 'sessionMedium' }, { name: 'pagePath' }],
    metrics: [{ name: 'sessions' }],
    dimensionFilter: {
      filter: { fieldName: 'pagePath', stringFilter: { matchType: 'BEGINS_WITH', value: p } },
    },
    orderBys: [{ metric: { metricName: 'sessions' }, desc: true }],
    limit: 200,
  });
  const acc = new Map();
  for (const row of resp.rows || []) {
    const k = row.dimensionValues[0].value + '|' + row.dimensionValues[1].value;
    const cur = acc.get(k) || { source: row.dimensionValues[0].value, medium: row.dimensionValues[1].value, sessions: 0 };
    cur.sessions += Number(row.metricValues[0].value) || 0;
    acc.set(k, cur);
  }
  return [...acc.values()].sort((a, b) => b.sessions - a.sessions).slice(0, limit);
}

// "Misc pages" + "Top executives" — the two hand-built charts from the old
// manual reports. Non-release pages grouped by cleaned page title, with the
// executive-bio pages split out into their own ranking.
export async function miscPages(siteId, rangeStr, limit = 25) {
  const site = getSite(siteId);
  const { startDate, endDate } = resolveRange(rangeStr || '28d');
  if (isDemo(site)) {
    const seed = [...String(siteId)].reduce((a, c) => (a * 31 + c.charCodeAt(0)) >>> 0, 7);
    const mk = (names, base) => names.map((name, i) => ({ name, pageviews: Math.max(60, Math.floor(base * (1 - i * 0.13) * (0.8 + ((seed >> (i % 20)) % 40) / 100))) }));
    return {
      sections: dmRows(mk(['Photo gallery', 'Video gallery', 'Executives', 'Downloads cart', 'Contacts', 'Technology', 'Clean Mobility', 'Registration options', 'News alert subscription', 'Website support', 'Corporate', 'N Brand', 'E-GMP'], 48000)),
      executives: dmRows(mk(['José Muñoz', 'Randy Parker', 'Chris Susock', 'Olabisi Boyle', 'Claudia Marquez', 'Steve Flamand', 'Barry Ratzlaff', 'Michel Poirier', 'Jason Erb', 'Sean Gilpin', 'Szymom Walus', 'Robert Grafton', 'Brian Latouf', 'Carlos Servin', 'Jim Park'], 4900)),
    };
  }
  const resp = await runReport(site.propertyId, {
    dateRanges: [{ startDate, endDate }],
    dimensions: [{ name: 'pageTitle' }, { name: 'pagePath' }],
    metrics: [{ name: 'screenPageViews' }],
    orderBys: [{ metric: { metricName: 'screenPageViews' }, desc: true }],
    limit: 2000,
  });
  // Strip the site-name suffix newsrooms append to every title.
  const cleanTitle = (t) => String(t || '').split(/\s+[|·]\s+/)[0].replace(/\s+[-–—]\s+[^-–—]*(newsroom|news|media|press)[^-–—]*$/i, '').trim();
  const isRelease = (p) => /^\/(releases?|news|articles?|stories|press-releases?)\//i.test(String(p || ''));
  const isExec = (p, t) => /executive|leadership|management-team|bios?\//i.test(String(p || '')) ||
    /^\/(about\/)?(executives?|leadership)\//i.test(String(p || ''));
  const secAcc = new Map(); const exAcc = new Map();
  for (const row of resp.rows || []) {
    const title = cleanTitle(row.dimensionValues[0].value);
    const path = row.dimensionValues[1].value;
    const views = Number(row.metricValues[0].value) || 0;
    if (!title || title === '(not set)' || isRelease(path)) continue;
    const acc = isExec(path, title) ? exAcc : secAcc;
    const k = title.toLowerCase();
    const cur = acc.get(k) || { name: title, pageviews: 0 };
    cur.pageviews += views;
    acc.set(k, cur);
  }
  const top = (m2, n) => [...m2.values()].sort((a, b) => b.pageviews - a.pageviews).slice(0, n);
  // Executive pages often carry the person's name as the title — drop generic
  // index pages from the ranking.
  const execs = top(exAcc, 15).filter((x) => !/^executives?$|^leadership$/i.test(x.name));
  return { sections: top(secAcc, limit), executives: execs };
}

// Navigation journeys — who came from where, and how visitors move INSIDE the
// site. GA4's pageReferrer (the full previous URL) × pagePath gives the edges:
// referrer on our own host = an internal step A → B; anything else = an entry.
export async function pageFlows(siteId, startDate, endDate) {
  const site = getSite(siteId);
  if (isDemo(site)) {
    const d0 = cached(`fl|${siteId}|${startDate}|${endDate}`, () => demoFlows(site, startDate, endDate));
    return { ...d0, internal: dmRows(d0.internal), entries: dmRows(d0.entries) };
  }
  const resp = await runReport(site.propertyId, {
    dateRanges: [{ startDate, endDate }],
    dimensions: [{ name: 'pageReferrer' }, { name: 'pagePath' }],
    metrics: [{ name: 'screenPageViews' }],
    orderBys: [{ metric: { metricName: 'screenPageViews' }, desc: true }],
    limit: 500,
  });
  const siteHost = (() => { try { return new URL(site.url).host.replace(/^www\./, ''); } catch { return null; } })();
  const internal = new Map(), entries = new Map();
  for (const row of resp.rows || []) {
    const ref = row.dimensionValues[0].value;
    const to = row.dimensionValues[1].value;
    const views = Number(row.metricValues[0].value) || 0;
    if (!ref || ref === '(not set)') continue;
    let host = null, refPath = null;
    try { const u = new URL(ref); host = u.host.replace(/^www\./, ''); refPath = u.pathname || '/'; } catch { continue; }
    if (siteHost && host === siteHost) {
      if (refPath === to) continue; // reloads
      const k = refPath + '→' + to;
      internal.set(k, { from: refPath, to, views: (internal.get(k)?.views || 0) + views });
    } else {
      const k = host + '→' + to;
      entries.set(k, { source: host, to, views: (entries.get(k)?.views || 0) + views });
    }
  }
  const top = (m) => [...m.values()].sort((a, b) => b.views - a.views).slice(0, 20);
  return { internal: top(internal), entries: top(entries), siteHost };
}

// Where visitors GO when they leave — outbound link clicks (GA4 enhanced
// measurement collects eventName='click' with the destination linkUrl).
export async function outboundClicks(siteId, startDate, endDate, limit = 20) {
  const site = getSite(siteId);
  if (isDemo(site)) {
    const d0 = cached(`ob|${siteId}|${startDate}|${endDate}`, () => demoOutbound(site, startDate, endDate));
    return { ...d0, domains: dmRows(d0.domains), urls: dmRows(d0.urls) };
  }
  const resp = await runReport(site.propertyId, {
    dateRanges: [{ startDate, endDate }],
    dimensions: [{ name: 'linkUrl' }],
    metrics: [{ name: 'eventCount' }, { name: 'totalUsers' }],
    dimensionFilter: {
      andGroup: { expressions: [
        { filter: { fieldName: 'eventName', stringFilter: { matchType: 'EXACT', value: 'click' } } },
        { notExpression: { filter: { fieldName: 'linkUrl', stringFilter: { matchType: 'EXACT', value: '' } } } },
      ] },
    },
    orderBys: [{ metric: { metricName: 'eventCount' }, desc: true }],
    limit: 200,
  });
  const byDomain = new Map();
  const urls = [];
  for (const row of resp.rows || []) {
    const url = row.dimensionValues[0].value;
    const clicks = Number(row.metricValues[0].value) || 0;
    const users = Number(row.metricValues[1].value) || 0;
    if (!url || url === '(not set)') continue;
    let host;
    try { host = new URL(url).host.replace(/^www\./, ''); } catch { continue; }
    const d = byDomain.get(host) || { domain: host, clicks: 0, users: 0 };
    d.clicks += clicks; d.users += users;
    byDomain.set(host, d);
    urls.push({ url, domain: host, clicks, users });
  }
  return {
    domains: [...byDomain.values()].sort((a, b) => b.clicks - a.clicks).slice(0, limit),
    urls: urls.slice(0, 30),
    collected: urls.length > 0,
  };
}

// Breakdown of one dimension, filtered by an exact value of another dimension.
// e.g. sources within channel "Organic Social", cities within "United Kingdom",
// or sources/countries for one exact pagePath.
export async function filteredBreakdown(siteId, spec, startDate, endDate, limit = 10) {
  const site = getSite(siteId);
  const { dimension, metric = 'sessions', filterField, filterValue } = spec;
  if (isDemo(site)) {
    return dmRows(cached(`fb|${siteId}|${dimension}|${metric}|${filterField}|${filterValue}|${startDate}|${endDate}`, () =>
      demoFiltered(site, dimension, metric, filterField, filterValue, startDate, endDate)
    )).slice(0, limit);
  }
  const resp = await runReport(site.propertyId, {
    dateRanges: [{ startDate, endDate }],
    dimensions: [{ name: dimension }],
    metrics: [{ name: metric }],
    dimensionFilter: {
      filter: { fieldName: filterField, stringFilter: { matchType: 'EXACT', value: filterValue } },
    },
    orderBys: [{ metric: { metricName: metric }, desc: true }],
    limit,
  });
  return (resp.rows || []).map((row) => ({
    dimensionValue: row.dimensionValues[0].value,
    [metric]: Number(row.metricValues[0].value),
  }));
}

// Daily views per page under a prefix — one query, grouped by path.
// Returns Map<path, Array<{date, views}>> (dates sorted).
export async function pagesDailyByPrefix(siteId, prefix, startDate, endDate, maxPages = 20) {
  const site = getSite(siteId);
  const clean = String(prefix).replace(/\/+$/, '');
  if (isDemo(site)) {
    const children = await breakdown(siteId, 'pagePath', startDate, endDate, maxPages, { pathPrefix: clean });
    const map = new Map();
    for (const c of children) {
      map.set(c.dimensionValue, demoPageTrend(site, c.dimensionValue, startDate, endDate)
        .map((r) => ({ date: r.date, views: r.screenPageViews })));
    }
    return map;
  }
  const resp = await runReport(site.propertyId, {
    dateRanges: [{ startDate, endDate }],
    dimensions: [{ name: 'date' }, { name: 'pagePath' }],
    metrics: [{ name: 'screenPageViews' }],
    dimensionFilter: {
      filter: { fieldName: 'pagePath', stringFilter: { matchType: 'BEGINS_WITH', value: clean + '/' } },
    },
    limit: 100000,
  });
  const map = new Map();
  for (const row of resp.rows || []) {
    const d = row.dimensionValues[0].value;
    const date = `${d.slice(0, 4)}-${d.slice(4, 6)}-${d.slice(6, 8)}`;
    const p = row.dimensionValues[1].value;
    if (!map.has(p)) map.set(p, []);
    map.get(p).push({ date, views: Number(row.metricValues[0].value) });
  }
  // keep the top pages by total views
  const ranked = [...map.entries()]
    .map(([p, rows]) => [p, rows.sort((a, b) => a.date.localeCompare(b.date))])
    .sort((a, b) => b[1].reduce((x, r) => x + r.views, 0) - a[1].reduce((x, r) => x + r.views, 0))
    .slice(0, maxPages);
  return new Map(ranked);
}

// Free-text page search — used when the dashboard's local filter finds
// nothing: queries GA4 for any path CONTAINING the term.
export async function searchPages(siteId, q, startDate, endDate, limit = 20) {
  const site = getSite(siteId);
  const needle = String(q).toLowerCase();
  if (isDemo(site)) {
    const all = demoBreakdown(site, 'pagePath', startDate, endDate);
    let rows = all.filter((r) => r.dimensionValue.toLowerCase().includes(needle));
    if (!rows.length && /^\d{3,}$/.test(needle)) {
      // Demo nicety: fabricate the article page for a searched numeric id.
      rows = [{ dimensionValue: `/articles/${needle}`, screenPageViews: 120 + (Number(needle) % 900), sessions: 80 + (Number(needle) % 600) }];
    }
    return rows.slice(0, limit);
  }
  const resp = await runReport(site.propertyId, {
    dateRanges: [{ startDate, endDate }],
    dimensions: [{ name: 'pagePath' }],
    metrics: [{ name: 'screenPageViews' }, { name: 'sessions' }],
    dimensionFilter: {
      filter: { fieldName: 'pagePath', stringFilter: { matchType: 'CONTAINS', value: q, caseSensitive: false } },
    },
    orderBys: [{ metric: { metricName: 'screenPageViews' }, desc: true }],
    limit,
  });
  return (resp.rows || []).map((row) => ({
    dimensionValue: row.dimensionValues[0].value,
    screenPageViews: Number(row.metricValues[0].value),
    sessions: Number(row.metricValues[1].value),
  }));
}

// Everything about one page: daily views, sources, countries.
export async function pageDetail(siteId, path, startDate, endDate) {
  const site = getSite(siteId);
  if (isDemo(site)) {
    return {
      path,
      trend: cached(`pt|${siteId}|${path}|${startDate}|${endDate}`, () => demoPageTrend(site, path, startDate, endDate)),
      sources: await filteredBreakdown(siteId, { dimension: 'sessionSource', metric: 'screenPageViews', filterField: 'pagePath', filterValue: path }, startDate, endDate, 8),
      countries: await filteredBreakdown(siteId, { dimension: 'country', metric: 'screenPageViews', filterField: 'pagePath', filterValue: path }, startDate, endDate, 8),
      devices: await filteredBreakdown(siteId, { dimension: 'deviceCategory', metric: 'screenPageViews', filterField: 'pagePath', filterValue: path }, startDate, endDate, 3),
    };
  }
  const filter = { filter: { fieldName: 'pagePath', stringFilter: { matchType: 'EXACT', value: path } } };
  const [trendResp, sources, countries, devices] = await Promise.all([
    runReport(site.propertyId, {
      dateRanges: [{ startDate, endDate }],
      dimensions: [{ name: 'date' }],
      metrics: [{ name: 'screenPageViews' }],
      dimensionFilter: filter,
      orderBys: [{ dimension: { dimensionName: 'date' } }],
      limit: 100000,
    }),
    filteredBreakdown(siteId, { dimension: 'sessionSource', metric: 'screenPageViews', filterField: 'pagePath', filterValue: path }, startDate, endDate, 8),
    filteredBreakdown(siteId, { dimension: 'country', metric: 'screenPageViews', filterField: 'pagePath', filterValue: path }, startDate, endDate, 8),
    filteredBreakdown(siteId, { dimension: 'deviceCategory', metric: 'screenPageViews', filterField: 'pagePath', filterValue: path }, startDate, endDate, 3),
  ]);
  const byDate = new Map(
    (trendResp.rows || []).map((row) => {
      const d = row.dimensionValues[0].value;
      return [`${d.slice(0, 4)}-${d.slice(4, 6)}-${d.slice(6, 8)}`, Number(row.metricValues[0].value)];
    })
  );
  // Fill missing days with 0 so the chart shows the true shape.
  const trend = [];
  const cur = new Date(startDate + 'T00:00:00Z');
  const end = new Date(endDate + 'T00:00:00Z');
  while (cur <= end) {
    const k = cur.toISOString().slice(0, 10);
    trend.push({ date: k, screenPageViews: byDate.get(k) || 0 });
    cur.setUTCDate(cur.getUTCDate() + 1);
  }
  return { path, trend, sources, countries, devices };
}

// ---- portfolio helpers (aggregate across all sites) ----

export async function portfolioTotals(startDate, endDate, siteList = null) {
  const sites = siteList || loadSites();
  const perSite = await Promise.all(
    sites.map(async (s) => ({ site: s, totals: await totals(s.id, startDate, endDate) }))
  );
  const agg = perSite.reduce(
    (acc, { totals: t }) => {
      for (const k of ['sessions', 'totalUsers', 'newUsers', 'screenPageViews', 'engagedSessions', 'conversions'])
        acc[k] += t[k];
      acc._dur += t.avgSessionDuration * t.sessions;
      return acc;
    },
    { sessions: 0, totalUsers: 0, newUsers: 0, screenPageViews: 0, engagedSessions: 0, conversions: 0, _dur: 0 }
  );
  return {
    totals: {
      sessions: agg.sessions, totalUsers: agg.totalUsers, newUsers: agg.newUsers,
      screenPageViews: agg.screenPageViews, engagedSessions: agg.engagedSessions,
      conversions: agg.conversions,
      engagementRate: agg.sessions ? agg.engagedSessions / agg.sessions : 0,
      avgSessionDuration: agg.sessions ? agg._dur / agg.sessions : 0,
    },
    perSite: perSite.map(({ site, totals: t }) => ({ id: site.id, name: site.name, url: site.url, ...t })),
  };
}

export async function portfolioTimeseries(startDate, endDate, siteList = null) {
  const sites = siteList || loadSites();
  const all = await Promise.all(sites.map((s) => timeseries(s.id, startDate, endDate)));
  const byDate = new Map();
  all.forEach((rows) => {
    rows.forEach((r) => {
      const cur = byDate.get(r.date) || { date: r.date, sessions: 0, totalUsers: 0, conversions: 0, screenPageViews: 0 };
      cur.sessions += r.sessions;
      cur.totalUsers += r.totalUsers;
      cur.conversions += r.conversions;
      cur.screenPageViews += r.screenPageViews;
      byDate.set(r.date, cur);
    });
  });
  return [...byDate.values()].sort((a, b) => a.date.localeCompare(b.date));
}

export async function portfolioBreakdown(dimension, startDate, endDate, limit = 12, opts = {}, siteList = null) {
  const sites = siteList || loadSites();
  const all = await Promise.all(sites.map((s) => breakdown(s.id, dimension, startDate, endDate, 50, opts)));
  const byVal = new Map();
  all.flat().forEach((r) => {
    const cur = byVal.get(r.dimensionValue) || { dimensionValue: r.dimensionValue };
    for (const [k, v] of Object.entries(r)) {
      if (k === 'dimensionValue') continue;
      cur[k] = (cur[k] || 0) + v;
    }
    byVal.set(r.dimensionValue, cur);
  });
  const key = dimension === 'pagePath' ? 'screenPageViews' : 'sessions';
  return [...byVal.values()].sort((a, b) => (b[key] || 0) - (a[key] || 0)).slice(0, limit);
}

// ---- date range helpers ----
export function resolveRange(range) {
  const now = new Date();
  const end = new Date(Date.UTC(now.getUTCFullYear(), now.getUTCMonth(), now.getUTCDate()));
  end.setUTCDate(end.getUTCDate() - 1); // data through yesterday
  const fmt = (d) => d.toISOString().slice(0, 10);
  const days = { '7d': 7, '28d': 28, '90d': 90, '12m': 365 }[range];
  if (days) {
    const start = new Date(end);
    start.setUTCDate(start.getUTCDate() - (days - 1));
    return { startDate: fmt(start), endDate: fmt(end) };
  }
  if (range === 'mtd') {
    const start = new Date(Date.UTC(end.getUTCFullYear(), end.getUTCMonth(), 1));
    return { startDate: fmt(start), endDate: fmt(end) };
  }
  // custom "YYYY-MM-DD..YYYY-MM-DD"
  const m = /^(\d{4}-\d{2}-\d{2})\.\.(\d{4}-\d{2}-\d{2})$/.exec(range || '');
  if (m) return { startDate: m[1], endDate: m[2] };
  // default 28 days
  const start = new Date(end);
  start.setUTCDate(start.getUTCDate() - 27);
  return { startDate: fmt(start), endDate: fmt(end) };
}

// Previous period of equal length, for deltas.
export function previousRange({ startDate, endDate }) {
  const s = new Date(startDate + 'T00:00:00Z');
  const e = new Date(endDate + 'T00:00:00Z');
  const len = Math.round((e - s) / 86400000) + 1;
  const prevEnd = new Date(s); prevEnd.setUTCDate(prevEnd.getUTCDate() - 1);
  const prevStart = new Date(prevEnd); prevStart.setUTCDate(prevStart.getUTCDate() - (len - 1));
  const fmt = (d) => d.toISOString().slice(0, 10);
  return { startDate: fmt(prevStart), endDate: fmt(prevEnd) };
}
