// Email campaign layer — SparkPost + Brevo, linked to sites by SENDER DOMAIN.
//
// Per-site config in sites.config.json (only for sites that send email).
// Sending domains usually differ per provider, so each can have its own:
//   "email": { "sparkpost": "spark.kia-press.co.uk", "brevo": "mail.kia-press.co.uk" }
// A site using only one provider just sets that one key. If a single domain
// really is shared, "email": { "domain": "news.kia-press.co.uk" } applies it
// to both providers. ("emailDomain": "..." is also accepted.)
//
// .env:
//   SPARKPOST_API_KEY=...     # optional
//   SPARKPOST_EU=1            # set if your SparkPost account is on the EU service
//   BREVO_API_KEY=...         # optional
//
// Both providers are queried when their key is present; campaigns are matched
// to the site by the sending/sender domain. Unified campaign shape:
//   { name, provider, date, sent, delivered, opens, clicks, openRate, clickRate }

import { getSite, isDemo, resolveRange } from './ga4.js';
import { legacyEmailCampaigns, campaignRecipients } from './legacy.js';
import { dbKind } from './db.js';

const hashStr = (s) => [...String(s)].reduce((a, c) => ((a * 31 + c.charCodeAt(0)) >>> 0), 7);

// Per-provider sending domains for a site (or null when the site sends no email).
export function siteEmailDomains(site) {
  const e = site.email;
  if (typeof e === 'string') return { sparkpost: e, brevo: e };
  const shared = e?.domain || site.emailDomain || null;
  const sparkpost = e?.sparkpost || shared;
  const brevo = e?.brevo || shared;
  if (!sparkpost && !brevo) return null;
  return { sparkpost, brevo };
}
export function emailKeysPresent() {
  return Boolean(process.env.SPARKPOST_API_KEY || process.env.BREVO_API_KEY);
}

// ── cache (email APIs are rate-limited; dashboards refresh often) ──────
const cache = new Map();
const TTL = 10 * 60 * 1000;
async function cached(key, fn) {
  const hit = cache.get(key);
  if (hit && Date.now() - hit.t < TTL) return hit.v;
  const v = await fn();
  cache.set(key, { t: Date.now(), v });
  return v;
}

// ── SparkPost ──────────────────────────────────────────────────────────
function sparkBase() {
  return process.env.SPARKPOST_EU === '1' ? 'https://api.eu.sparkpost.com' : 'https://api.sparkpost.com';
}
async function sparkGet(pathAndQuery) {
  const res = await fetch(sparkBase() + pathAndQuery, {
    headers: { Authorization: process.env.SPARKPOST_API_KEY },
  });
  if (!res.ok) throw new Error(`SparkPost API error (${res.status}): ${(await res.text()).slice(0, 300)}`);
  return res.json();
}

// Try to fetch the email subject for a campaign from the events API (only
// works for recent sends — SparkPost keeps events ~10 days — and requires the
// key to have "Message Events: Read-only"). Fails silently to null.
async function sparkpostSubject(campaignId) {
  try {
    const ev = await sparkGet(`/api/v1/events/message?campaigns=${encodeURIComponent(campaignId)}&per_page=1`);
    return ev.results?.[0]?.subject || null;
  } catch { return null; }
}

async function sparkpostCampaigns(domain, startDate, endDate) {
  const from = `${startDate}T00:00`, to = `${endDate}T23:59`;
  const metrics = 'count_sent,count_delivered,count_unique_confirmed_opened,count_unique_clicked';
  const q = `from=${from}&to=${to}&sending_domains=${encodeURIComponent(domain)}&metrics=${metrics}&limit=100`;
  const byCampaign = await sparkGet(`/api/v1/metrics/deliverability/campaign?${q}&order_by=count_sent`);
  const campaigns = (byCampaign.results || []).map((r) => ({
    id: r.campaign_id || '',
    name: r.campaign_id || '(no campaign id)',
    provider: 'sparkpost',
    date: null, // SparkPost campaign metrics are aggregates; send days come from the time-series below
    sent: r.count_sent || 0,
    delivered: r.count_delivered || 0,
    opens: r.count_unique_confirmed_opened || 0,
    clicks: r.count_unique_clicked || 0,
  }));
  // Enrich with subject lines where events are still retained.
  await Promise.all(campaigns.slice(0, 15).map(async (c) => {
    if (c.id) c.subject = await sparkpostSubject(c.id);
  }));
  const series = await sparkGet(`/api/v1/metrics/deliverability/time-series?precision=day&from=${from}&to=${to}&sending_domains=${encodeURIComponent(domain)}&metrics=count_sent`);
  const sendDays = (series.results || [])
    .filter((r) => (r.count_sent || 0) > 0)
    .map((r) => ({ date: String(r.ts).slice(0, 10), sent: r.count_sent }));
  return { campaigns, sendDays };
}

// ── Brevo ──────────────────────────────────────────────────────────────
async function brevoCampaigns(domain, startDate, endDate) {
  const url = `https://api.brevo.com/v3/emailCampaigns?status=sent&limit=100&startDate=${startDate}T00:00:00.000Z&endDate=${endDate}T23:59:59.999Z`;
  const res = await fetch(url, { headers: { 'api-key': process.env.BREVO_API_KEY } });
  if (!res.ok) throw new Error(`Brevo API error (${res.status}): ${(await res.text()).slice(0, 300)}`);
  const data = await res.json();
  const wanted = domain.toLowerCase();
  const campaigns = (data.campaigns || [])
    .filter((c) => (c.sender?.email || '').toLowerCase().split('@')[1] === wanted)
    .map((c) => {
      let g = c.statistics?.globalStats || {};
      // Some campaign types report per-list stats only — sum those instead.
      if (!(g.sent || g.delivered) && Array.isArray(c.statistics?.campaignStats) && c.statistics.campaignStats.length) {
        g = c.statistics.campaignStats.reduce((a, s) => ({
          sent: a.sent + (s.sent || 0),
          delivered: a.delivered + (s.delivered || 0),
          uniqueViews: a.uniqueViews + (s.uniqueViews || 0),
          uniqueClicks: a.uniqueClicks + (s.uniqueClicks || 0),
        }), { sent: 0, delivered: 0, uniqueViews: 0, uniqueClicks: 0 });
      }
      return {
        id: String(c.id),
        name: c.name,
        subject: c.subject || null,
        provider: 'brevo',
        date: c.sentDate ? String(c.sentDate).slice(0, 10) : null,
        sent: g.sent || 0,
        delivered: g.delivered || 0,
        opens: g.uniqueViews || 0,
        clicks: g.uniqueClicks || 0,
      };
    });
  const sendDays = campaigns.filter((c) => c.date).map((c) => ({ date: c.date, sent: c.sent }));
  return { campaigns, sendDays };
}

// ── demo mode ──────────────────────────────────────────────────────────
const DEMO_NAMES = ['Monthly newsletter', 'Product launch alert', 'Press digest', 'Event invitation', 'Media alert', 'Feature announcement'];
function demoEmail(site, startDate, endDate, force = false) {
  const seed = hashStr(site.id + startDate);
  // Roughly half the demo sites "send email" — mirrors real portfolios.
  if (!force && hashStr(site.id) % 2 === 1) return null;
  const start = new Date(startDate + 'T00:00:00Z');
  const end = new Date(endDate + 'T00:00:00Z');
  const days = Math.round((end - start) / 86400000) + 1;
  const n = Math.max(1, Math.min(5, Math.floor(days / 9)));
  const campaigns = Array.from({ length: n }, (_, i) => {
    const d = new Date(start);
    d.setUTCDate(d.getUTCDate() + Math.floor(((seed >>> (i * 3)) % 97) / 97 * (days - 1)));
    const sent = 800 + ((seed >>> (i * 2)) % 4200);
    const delivered = Math.round(sent * (0.955 + ((seed >>> i) % 30) / 1000));
    const opens = Math.round(delivered * (0.24 + ((seed >>> (i + 4)) % 240) / 1000));
    const clicks = Math.round(opens * (0.13 + ((seed >>> (i + 7)) % 170) / 1000));
    return {
      id: `demo-${1000 + ((seed >>> i) % 9000)}`,
      name: `${DEMO_NAMES[(seed + i) % DEMO_NAMES.length]} — ${d.toISOString().slice(5, 10)}`,
      subject: null,
      provider: (seed + i) % 3 === 0 ? 'brevo' : 'sparkpost',
      date: d.toISOString().slice(0, 10),
      sent, delivered, opens, clicks,
    };
  }).sort((a, b) => (b.date || '').localeCompare(a.date || ''));
  return {
    domain: `news.${site.id.replace(/-/g, '')}.example.com`,
    campaigns,
    sendDays: campaigns.map((c) => ({ date: c.date, sent: c.sent })),
  };
}

// ── per-campaign drill-down ────────────────────────────────────────────
function demoCampaignDetail(site, cid, range) {
  const seed = hashStr(site.id + cid);
  const start = new Date(range.startDate + 'T00:00:00Z');
  const end = new Date(range.endDate + 'T00:00:00Z');
  const days = Math.round((end - start) / 86400000) + 1;
  const sendIdx = seed % Math.max(1, days - 6);
  const sent = 900 + (seed % 4000);
  const rows = [];
  for (let i = 0; i < Math.min(7, days - sendIdx); i++) {
    const d = new Date(start); d.setUTCDate(d.getUTCDate() + sendIdx + i);
    const opens = Math.round(sent * 0.32 * Math.exp(-i / 1.6) * (0.8 + ((seed >>> i) % 40) / 100));
    rows.push({
      date: d.toISOString().slice(0, 10),
      sent: i === 0 ? sent : 0,
      delivered: i === 0 ? Math.round(sent * 0.97) : 0,
      opens,
      clicks: Math.round(opens * 0.16),
    });
  }
  return {
    provider: 'demo', id: cid,
    subject: `${DEMO_NAMES[seed % DEMO_NAMES.length]} — full performance`,
    days: rows,
  };
}

export async function campaignDetail(siteId, provider, cid, rangeStr) {
  const site = getSite(siteId);
  const range = resolveRange(rangeStr);
  if (isDemo(site)) return demoCampaignDetail(site, cid, range);
  if (provider === 'db') {
    return { provider: 'db', id: cid, subject: null, days: [],
      recipients: (await campaignRecipients(siteId, cid)) || [] };
  }

  if (provider === 'sparkpost') {
    const metrics = 'count_sent,count_delivered,count_unique_confirmed_opened,count_unique_clicked';
    const q = `from=${range.startDate}T00:00&to=${range.endDate}T23:59&campaigns=${encodeURIComponent(cid)}&metrics=${metrics}&precision=day`;
    const series = await sparkGet(`/api/v1/metrics/deliverability/time-series?${q}`);
    const days = (series.results || [])
      .map((r) => ({
        date: String(r.ts).slice(0, 10),
        sent: r.count_sent || 0,
        delivered: r.count_delivered || 0,
        opens: r.count_unique_confirmed_opened || 0,
        clicks: r.count_unique_clicked || 0,
      }))
      .filter((d) => d.sent || d.opens || d.clicks);
    return { provider, id: cid, subject: await sparkpostSubject(cid), days };
  }

  // brevo — one API call returns the full campaign record
  const res = await fetch(`https://api.brevo.com/v3/emailCampaigns/${encodeURIComponent(cid)}`, {
    headers: { 'api-key': process.env.BREVO_API_KEY },
  });
  if (!res.ok) throw new Error(`Brevo API error (${res.status}): ${(await res.text()).slice(0, 300)}`);
  const c = await res.json();
  const g = c.statistics?.globalStats || {};
  return {
    provider: 'brevo', id: cid,
    name: c.name, subject: c.subject || null,
    sentDate: c.sentDate ? String(c.sentDate).slice(0, 10) : null,
    stats: {
      sent: g.sent || 0, delivered: g.delivered || 0,
      opens: g.uniqueViews || 0, clicks: g.uniqueClicks || 0,
      softBounces: g.softBounces || 0, hardBounces: g.hardBounces || 0,
      unsubscriptions: g.unsubscriptions || 0,
    },
    lists: (c.statistics?.campaignStats || []).map((s) => ({
      listId: s.listId, sent: s.sent || 0, delivered: s.delivered || 0,
      opens: s.uniqueViews || 0, clicks: s.uniqueClicks || 0,
    })),
  };
}

// ── unified overview ───────────────────────────────────────────────────
function finalize(domain, campaigns, sendDays) {
  // Rate denominator: `delivered` when it's plausible, otherwise the send
  // size. Some platform DBs carry a tiny/wrong delivered column — dividing
  // by it forced every campaign to a flat 100% open rate.
  const rateBase = (c) => {
    const d = Number(c.delivered) || 0, s = Number(c.sent) || 0;
    return d >= Math.max(1, s * 0.5) ? d : (s || d);
  };
  campaigns.forEach((c) => {
    // Cap at 100%: if a source ever reports open EVENTS instead of unique
    // openers, a rate above 1 is meaningless — clamp rather than mislead.
    // rateUnknown = the platform only stores event counts and the per-
    // recipient stats table is missing, so NO honest rate exists → null,
    // shown as "n/a" in the UI (never a fake flat 100%).
    const b = rateBase(c);
    c.openRate = c.rateUnknown ? null : b ? Math.min(1, c.opens / b) : 0;
    c.clickRate = c.rateUnknown ? null : b ? Math.min(1, c.clicks / b) : 0;
  });
  campaigns.sort((a, b) => (b.date || '').localeCompare(a.date || '') || b.sent - a.sent);
  const totals = campaigns.reduce(
    (acc, c) => {
      acc.sent += c.sent; acc.delivered += c.delivered; acc.opens += c.opens; acc.clicks += c.clicks;
      acc.base += rateBase(c);
      return acc;
    },
    { sent: 0, delivered: 0, opens: 0, clicks: 0, base: 0 }
  );
  // Portfolio rate over campaigns whose rate is actually knowable.
  const known = campaigns.filter((c) => c.openRate != null);
  const kBase = known.reduce((a, c) => a + rateBase(c), 0);
  totals.openRate = known.length && kBase ? Math.min(1, known.reduce((a, c) => a + c.opens, 0) / kBase) : null;
  totals.clickRate = known.length && kBase ? Math.min(1, known.reduce((a, c) => a + c.clicks, 0) / kBase) : null;
  delete totals.base;
  const dayMap = new Map();
  sendDays.forEach((d) => dayMap.set(d.date, (dayMap.get(d.date) || 0) + d.sent));
  return {
    enabled: true, domain, campaigns, totals,
    sendDays: [...dayMap.entries()].map(([date, sent]) => ({ date, sent })).sort((a, b) => a.date.localeCompare(b.date)),
  };
}

// ── Hybrid stats: DB campaigns + LIVE provider aggregates ───────────────
// The platform DB's delivered/opened counters are unreliable (a campaign
// with 12,054 recipients showing 224 "delivered"), while SparkPost/Brevo
// hold the authoritative aggregates — but SparkPost only for ~6 months.
// So: campaigns younger than the retention window get their delivered /
// unique opens / unique clicks refreshed straight from the provider; older
// campaigns (and per-recipient detail, which providers drop after 10 days)
// stay on the DB numbers exactly as before.
const PROVIDER_RETENTION_DAYS = 175; // just inside SparkPost's 6 months
async function overlayProviderStats(rows) {
  try {
    const cutoff = Date.now() - PROVIDER_RETENTION_DAYS * 86400000;
    const recent = rows.filter((r) => r.provider === 'db' && r.id && r.date
      && new Date(r.date + 'T00:00:00Z').getTime() >= cutoff);
    if (!recent.length) return rows;
    const matched = new Set();
    // 1) SparkPost — ONE metrics call for every recent campaign id at once.
    if (process.env.SPARKPOST_API_KEY) {
      const dates = recent.map((r) => r.date).sort();
      const to = new Date(new Date(dates[dates.length - 1] + 'T00:00:00Z').getTime() + 3 * 86400000)
        .toISOString().slice(0, 10);
      const ids = [...new Set(recent.map((r) => String(r.id)))].slice(0, 100);
      const key = `spovl|${ids.join(',')}|${dates[0]}|${to}`;
      const res = await cached(key, () =>
        sparkGet(`/api/v1/metrics/deliverability/campaign?from=${dates[0]}T00:00&to=${to}T23:59`
          + `&campaigns=${encodeURIComponent(ids.join(','))}`
          + '&metrics=count_sent,count_delivered,count_unique_confirmed_opened,count_unique_clicked&limit=200')
      ).catch(() => null);
      const byId = new Map((res?.results || []).map((x) => [String(x.campaign_id), x]));
      for (const r of recent) {
        const m = byId.get(String(r.id));
        if (!m || !(Number(m.count_sent) || Number(m.count_delivered))) continue;
        r.delivered = Number(m.count_delivered) || 0;
        if (!r.sent && Number(m.count_sent)) r.sent = Number(m.count_sent);
        r.opens = Number(m.count_unique_confirmed_opened) || 0;
        r.clicks = Number(m.count_unique_clicked) || 0;
        r.uniqueCounts = true; r.rateUnknown = false; r.statsSource = 'sparkpost';
        matched.add(r);
      }
    }
    // 2) Brevo — per-campaign stats persist indefinitely; try ids that look
    //    like Brevo campaign ids (small integers) and weren't SparkPost's.
    if (process.env.BREVO_API_KEY) {
      const todo = recent.filter((r) => !matched.has(r) && /^\d{1,10}$/.test(String(r.id))).slice(0, 15);
      await Promise.all(todo.map((r) => cached(`brovl|${r.id}`, async () => {
        const res = await fetch(`https://api.brevo.com/v3/emailCampaigns/${r.id}`,
          { headers: { 'api-key': process.env.BREVO_API_KEY } });
        return res.ok ? res.json() : null;
      }).then((c) => {
        if (!c) return;
        let g = c.statistics?.globalStats || {};
        if (!(g.sent || g.delivered) && Array.isArray(c.statistics?.campaignStats) && c.statistics.campaignStats.length) {
          g = c.statistics.campaignStats.reduce((a, x) => ({
            sent: a.sent + (x.sent || 0), delivered: a.delivered + (x.delivered || 0),
            uniqueViews: a.uniqueViews + (x.uniqueViews || 0), uniqueClicks: a.uniqueClicks + (x.uniqueClicks || 0),
          }), { sent: 0, delivered: 0, uniqueViews: 0, uniqueClicks: 0 });
        }
        if (!(g.sent || g.delivered)) return;
        r.delivered = g.delivered || 0;
        if (!r.sent && g.sent) r.sent = g.sent;
        r.opens = g.uniqueViews || 0;
        r.clicks = g.uniqueClicks || 0;
        r.uniqueCounts = true; r.rateUnknown = false; r.statsSource = 'brevo';
      }).catch(() => { /* per-campaign overlay is best-effort */ })));
    }
  } catch { /* overlay must never break the card — DB numbers stand */ }
  return rows;
}

export async function emailOverview(siteId, rangeStr) {
  const site = getSite(siteId);
  const range = resolveRange(rangeStr);

  // DB-first: when a reports database is connected, its emails table +
  // email_releases joins are exact — no sender-domain matching needed.
  if (!isDemo(site) && dbKind(site)) {
    try {
      const campaigns = await legacyEmailCampaigns(siteId, rangeStr);
      if (campaigns && campaigns.length) {
        await overlayProviderStats(campaigns);
        const anyOvl = campaigns.some((c) => c.statsSource);
        const sendDays = campaigns.filter((c) => c.date).map((c) => ({ date: c.date, sent: c.sent }));
        return { range, ...finalize(anyOvl ? 'reports database + live provider stats' : 'reports database', campaigns, sendDays) };
      }
    } catch { /* fall through to ESP APIs */ }
  }

  const domains = siteEmailDomains(site);

  if (isDemo(site)) {
    const demo = demoEmail(site, range.startDate, range.endDate, Boolean(domains));
    if (!demo) return { enabled: false, reason: 'This site has no email campaigns configured.' };
    const label = domains ? [...new Set([domains.sparkpost, domains.brevo].filter(Boolean))].join(' / ') : demo.domain;
    return { range, ...finalize(label, demo.campaigns, demo.sendDays) };
  }

  if (!domains) return { enabled: false, reason: 'No email domain configured for this site (add "email": { "sparkpost": "...", "brevo": "..." } in sites.config.json).' };
  const label = [...new Set([domains.sparkpost, domains.brevo].filter(Boolean))].join(' / ');
  if (!emailKeysPresent()) return { enabled: true, domain: label, error: 'No email API keys configured — add SPARKPOST_API_KEY and/or BREVO_API_KEY to .env.' };

  return cached(`em|${siteId}|${range.startDate}|${range.endDate}`, async () => {
    const campaigns = [], sendDays = [], errors = [];
    const jobs = [];
    if (process.env.SPARKPOST_API_KEY && domains.sparkpost)
      jobs.push(sparkpostCampaigns(domains.sparkpost, range.startDate, range.endDate).catch((e) => ({ error: 'sparkpost: ' + e.message })));
    if (process.env.BREVO_API_KEY && domains.brevo)
      jobs.push(brevoCampaigns(domains.brevo, range.startDate, range.endDate).catch((e) => ({ error: 'brevo: ' + e.message })));
    for (const r of await Promise.all(jobs)) {
      if (r.error) { errors.push(r.error); continue; }
      campaigns.push(...r.campaigns);
      sendDays.push(...r.sendDays);
    }
    const out = { range, ...finalize(label, campaigns, sendDays) };
    if (errors.length) out.providerErrors = errors;
    return out;
  });
}
