// data.jsx — REAL Silsal data engine
// Loads data.json (snapshot from Google Sheet 1NWVqFsROcdz...) and exposes
// helpers used by all tabs. Pull fresh data with `python3 pull_data.py`.

const COUNTRIES = [
  { code: 'AE', name: 'UAE',   flag: '🇦🇪', color: '#5B5BF0', currency: 'AED' },
  { code: 'SA', name: 'KSA',   flag: '🇸🇦', color: '#6FD2C0', currency: 'AED' },
  { code: 'QA', name: 'Qatar', flag: '🇶🇦', color: '#FF8FB1', currency: 'AED' },
];
const COUNTRY_KEY = { AE: 'UAE', SA: 'KSA', QA: 'Qatar' };

const RANGES = {
  '7d':  { label: 'Last 7 days',    days: 7,   buckets: { daily: 7,  weekly: 1,  monthly: 1, quarterly: 1 }, sub: 'vs previous 7 days'    },
  '30d': { label: 'Last 30 days',   days: 30,  buckets: { daily: 30, weekly: 4,  monthly: 1, quarterly: 1 }, sub: 'vs previous 30 days'   },
  '90d': { label: 'Last 90 days',   days: 90,  buckets: { daily: 90, weekly: 13, monthly: 3, quarterly: 1 }, sub: 'vs previous 90 days'   },
  '12m': { label: 'Last 12 months', days: 365, buckets: { daily: 60, weekly: 26, monthly: 12, quarterly: 4 }, sub: 'vs previous 12 months' },
  'ytd': { label: 'Year to date',   days: 'ytd', buckets: { daily: 60, weekly: 17, monthly: 4, quarterly: 2 }, sub: 'vs previous year YTD' },
};

let RAW = null;        // Loaded payload from data.json
let MAX_DATE = null;   // Date object — last day in dataset

function loadReal() {
  return fetch('data.json', { cache: 'no-cache' })
    .then(r => r.json())
    .then(d => { RAW = d; MAX_DATE = parseISO(d.meta.maxDate); return d; });
}

function parseISO(s) { const [y, m, d] = s.split('-').map(Number); return new Date(Date.UTC(y, m - 1, d)); }
function fmtISO(d)   { return d.toISOString().slice(0, 10); }
function addDays(d, n) { const x = new Date(d.getTime()); x.setUTCDate(x.getUTCDate() + n); return x; }

// Build [start, end] inclusive ISO strings for current and previous periods of a given range.
function rangeBounds(range, refDate = MAX_DATE) {
  const end = new Date(refDate.getTime());
  let start, prevEnd, prevStart;
  if (range === 'ytd') {
    start = new Date(Date.UTC(end.getUTCFullYear(), 0, 1));
    const days = Math.round((end - start) / 86400000) + 1;
    prevEnd   = new Date(Date.UTC(end.getUTCFullYear() - 1, end.getUTCMonth(), end.getUTCDate()));
    prevStart = new Date(Date.UTC(end.getUTCFullYear() - 1, 0, 1));
    return { start, end, prevStart, prevEnd, days };
  }
  if (range === '12m') {
    start = addDays(end, -364);
    prevEnd = addDays(start, -1);
    prevStart = addDays(prevEnd, -364);
    return { start, end, prevStart, prevEnd, days: 365 };
  }
  const days = RANGES[range].days;
  start = addDays(end, -(days - 1));
  prevEnd = addDays(start, -1);
  prevStart = addDays(prevEnd, -(days - 1));
  return { start, end, prevStart, prevEnd, days };
}

// Rows for a country between [start, end] inclusive (Date objects).
function rowsBetween(country, start, end) {
  const arr = RAW.countries[COUNTRY_KEY[country] || country] || [];
  const s = fmtISO(start), e = fmtISO(end);
  return arr.filter(r => r.d >= s && r.d <= e);
}

// All-country merge: union daily rows by date, summing numeric fields.
function rowsAllCountries(start, end) {
  const acc = {};
  for (const cc of ['UAE', 'Qatar', 'KSA']) {
    for (const r of rowsBetween(cc, start, end)) {
      const t = acc[r.d] = acc[r.d] || { d: r.d };
      for (const k of Object.keys(r)) {
        if (k === 'd') continue;
        t[k] = (t[k] || 0) + (r[k] || 0);
      }
    }
  }
  return Object.values(acc).sort((a, b) => a.d.localeCompare(b.d));
}

function getRows(country, start, end) {
  return country === 'all' ? rowsAllCountries(start, end) : rowsBetween(country, start, end);
}

// Aggregate sums for a row collection.
function sumRows(rows) {
  const o = {
    metaSpend: 0, metaPurch: 0, metaRev: 0, metaClicks: 0, metaImp: 0,
    googleSpend: 0, googlePurch: 0, googleRev: 0, googleClicks: 0, googleImp: 0,
    sessions: 0, atc: 0, checkouts: 0, purchases: 0, ga4Rev: 0,
    magentoOrd: 0, magentoRev: 0,
  };
  for (const r of rows) for (const k of Object.keys(o)) o[k] += r[k] || 0;
  o.spend = o.metaSpend + o.googleSpend;
  o.purchasesAds = o.metaPurch + o.googlePurch;   // ad-attributed purchases
  o.revenueAds  = o.metaRev + o.googleRev;        // ad-attributed revenue
  o.revenue = o.magentoRev || o.ga4Rev;           // headline (Magento preferred when present)
  o.roas    = o.spend > 0 ? o.revenueAds / o.spend : 0;
  o.cac     = o.purchasesAds > 0 ? o.spend / o.purchasesAds : 0;
  o.aov     = o.purchases > 0 ? o.ga4Rev / o.purchases : 0;
  o.ctrMeta = o.metaImp > 0 ? (o.metaClicks / o.metaImp) * 100 : 0;
  o.ctrGoogle = o.googleImp > 0 ? (o.googleClicks / o.googleImp) * 100 : 0;
  o.ctr     = (o.metaImp + o.googleImp) > 0 ? ((o.metaClicks + o.googleClicks) / (o.metaImp + o.googleImp)) * 100 : 0;
  o.cpc     = (o.metaClicks + o.googleClicks) > 0 ? o.spend / (o.metaClicks + o.googleClicks) : 0;
  o.cpm     = (o.metaImp + o.googleImp) > 0 ? (o.spend / (o.metaImp + o.googleImp)) * 1000 : 0;
  o.convRate = o.sessions > 0 ? (o.purchases / o.sessions) * 100 : 0;
  return o;
}

// Bucket rows into N evenly-sized time buckets (by day index, not calendar week/month).
// Returns { labels: [...], rows: [aggregatedRow, ...] } — each aggregated row is a sumRows() result.
function bucketRows(rows, granularity, start, end) {
  if (!rows.length) return { labels: [], rows: [] };
  const dayCount = Math.round((end - start) / 86400000) + 1;

  let bucketSize;
  if (granularity === 'daily')      bucketSize = 1;
  else if (granularity === 'weekly') bucketSize = 7;
  else if (granularity === 'monthly') bucketSize = Math.max(28, Math.round(dayCount / 12));
  else if (granularity === 'quarterly') bucketSize = Math.max(60, Math.round(dayCount / 4));
  else bucketSize = 1;

  const buckets = [];
  for (let cursor = new Date(start.getTime()); cursor <= end; ) {
    const bEnd = addDays(cursor, bucketSize - 1);
    const stop = bEnd > end ? end : bEnd;
    const bRows = rows.filter(r => r.d >= fmtISO(cursor) && r.d <= fmtISO(stop));
    buckets.push({ start: new Date(cursor.getTime()), end: stop, agg: sumRows(bRows) });
    cursor = addDays(stop, 1);
  }

  const labels = buckets.map(b => labelFor(b.start, b.end, granularity));
  return { labels, rows: buckets.map(b => b.agg) };
}

function labelFor(start, end, gran) {
  const M = ['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'];
  if (gran === 'daily')      return `${start.getUTCDate()}/${start.getUTCMonth() + 1}`;
  if (gran === 'weekly')     return `${start.getUTCDate()}/${start.getUTCMonth() + 1}`;
  if (gran === 'monthly')    return `${M[start.getUTCMonth()]} ${String(start.getUTCFullYear()).slice(-2)}`;
  if (gran === 'quarterly')  return `Q${Math.floor(start.getUTCMonth() / 3) + 1} ${String(start.getUTCFullYear()).slice(-2)}`;
  return fmtISO(start);
}

// ---------- High-level helpers used by tabs ----------

function realKPIs(country, range) {
  const b = rangeBounds(range);
  const cur  = sumRows(getRows(country, b.start,    b.end));
  const prev = sumRows(getRows(country, b.prevStart, b.prevEnd));
  return { cur, prev, bounds: b };
}

function realCountrySplit(range) {
  const b = rangeBounds(range);
  const out = ['UAE', 'Qatar', 'KSA'].map(name => ({
    name, agg: sumRows(rowsBetween(name, b.start, b.end)),
    aggPrev: sumRows(rowsBetween(name, b.prevStart, b.prevEnd)),
  }));
  return out;
}

function realChannelSeries(country, range, granularity) {
  const b = rangeBounds(range);
  const rows = getRows(country, b.start, b.end);
  const { labels, rows: bk } = bucketRows(rows, granularity, b.start, b.end);
  return {
    labels,
    meta:    bk.map(r => Math.round(r.metaRev)),
    google:  bk.map(r => Math.round(r.googleRev)),
    metaSpend:   bk.map(r => Math.round(r.metaSpend)),
    googleSpend: bk.map(r => Math.round(r.googleSpend)),
    sessions: bk.map(r => r.sessions),
    purchases: bk.map(r => r.purchases),
    ga4Rev: bk.map(r => Math.round(r.ga4Rev)),
    magentoRev: bk.map(r => Math.round(r.magentoRev)),
    spend: bk.map(r => Math.round(r.spend)),
    revenueAds: bk.map(r => Math.round(r.revenueAds)),
    roas: bk.map(r => +r.roas.toFixed(2)),
    aov: bk.map(r => +r.aov.toFixed(2)),
    cpc: bk.map(r => +r.cpc.toFixed(2)),
    ctr: bk.map(r => +r.ctr.toFixed(2)),
    convRate: bk.map(r => +r.convRate.toFixed(2)),
    atc: bk.map(r => r.atc),
    checkouts: bk.map(r => r.checkouts),
  };
}

function realChannelSeriesPrev(country, range, granularity) {
  const b = rangeBounds(range);
  const rows = getRows(country, b.prevStart, b.prevEnd);
  const { rows: bk } = bucketRows(rows, granularity, b.prevStart, b.prevEnd);
  return {
    meta:   bk.map(r => Math.round(r.metaRev)),
    google: bk.map(r => Math.round(r.googleRev)),
    spend:  bk.map(r => Math.round(r.spend)),
    revenueAds: bk.map(r => Math.round(r.revenueAds)),
    sessions: bk.map(r => r.sessions),
    purchases: bk.map(r => r.purchases),
    magentoRev: bk.map(r => Math.round(r.magentoRev)),
  };
}

function realFunnel(country, range) {
  const b = rangeBounds(range);
  const r = sumRows(getRows(country, b.start, b.end));
  return {
    sessions: r.sessions, atc: r.atc, checkouts: r.checkouts,
    purchases: r.purchases, revenue: r.ga4Rev,
  };
}

function realProducts(limit = 10) { return (RAW.products || []).slice(0, limit); }

function realSparkline(country, metric, days = 14) {
  const end = MAX_DATE;
  const start = addDays(end, -(days - 1));
  const rows = getRows(country, start, end);
  return rows.map(r => {
    if (metric === 'spend')    return Math.round((r.metaSpend || 0) + (r.googleSpend || 0));
    if (metric === 'revenue')  return Math.round(r.magentoRev || r.ga4Rev || 0);
    if (metric === 'purchases') return r.purchases || 0;
    if (metric === 'sessions') return r.sessions || 0;
    if (metric === 'roas') {
      const sp = (r.metaSpend || 0) + (r.googleSpend || 0);
      const rv = (r.metaRev || 0) + (r.googleRev || 0);
      return sp > 0 ? +(rv / sp).toFixed(2) : 0;
    }
    return r[metric] || 0;
  });
}

// ---------- Backwards-compatible facade for old tabs ----------
// Old tabs call SilsalData.genData({range,...}).labels / .series() / .seriesPrev() / .kpiBase()
// We provide those backed by real data so anything not yet refactored still works.
function genData({ range = '30d', granularity = 'daily', country = 'all' }) {
  const series = realChannelSeries(country, range, granularity);
  const seriesPrev = realChannelSeriesPrev(country, range, granularity);
  const kpis = realKPIs(country, range);

  const namedScale = { spend: 'spend', revenue: 'revenueAds', purchases: 'purchases',
                       sessions: 'sessions', atc: 'atc', clicks: null, impressions: null };

  return {
    range, granularity, currency: 'AED',
    labels: series.labels, n: series.labels.length,
    countries: COUNTRIES,
    series: (_seed, _base, _vol, _trend, key = 'meta') => series[key] || series.meta,
    seriesPrev: (_seed, _base, _vol, _trend, key = 'meta') => seriesPrev[key] || seriesPrev.meta,
    spark: (_seed, _base, _vol, _trend, metric = 'revenue') => realSparkline(country, metric, 14),
    kpiBase: (k) => kpis.cur[namedScale[k] || k] || 0,
    kpiPrev: (k) => kpis.prev[namedScale[k] || k] || 0,
    kpiDelta: (k) => {
      const c = kpis.cur[namedScale[k] || k] || 0;
      const p = kpis.prev[namedScale[k] || k] || 0;
      return p > 0 ? ((c - p) / p) * 100 : 0;
    },
    raw: { kpis, channels: series, channelsPrev: seriesPrev },
  };
}

// Pseudo-random fallback spark (kept so any caller without real data still renders).
function buildSeries(seed, base, vol, n, trend = 0.02) {
  let s = seed;
  const r = () => { s = (s * 9301 + 49297) % 233280; return s / 233280; };
  const out = []; let v = base;
  for (let i = 0; i < n; i++) {
    const noise = (r() - 0.5) * vol;
    const seasonal = Math.sin(i / Math.max(2, n / 6)) * vol * 0.4;
    v = base * (1 + trend * (i / n)) + noise + seasonal;
    out.push(Math.max(0, Math.round(v)));
  }
  return out;
}

window.SilsalData = {
  COUNTRIES, RANGES, COUNTRY_KEY,
  loadReal, genData, buildSeries,
  realKPIs, realCountrySplit, realChannelSeries, realChannelSeriesPrev,
  realFunnel, realProducts, realSparkline,
  rangeBounds, sumRows,
};