/* Report Builder — manual upload wizard =================================
   Multi-file CSV/XLSX, parsed client-side with the already-loaded SheetJS.
   Silent automation: platform fingerprinting, synonym column auto-map,
   totals-row stripping, hierarchy rollups (most granular file wins).
   Only low-confidence decisions surface to the user. Ingest is idempotent
   (upsert per entity+date) and chunked for shared hosting.
======================================================================== */

/* ---------- Column synonym dictionary ---------------------------------- */
const R_SYNONYMS = {
  /* order matters: earlier synonym wins at equal match strength
     (Meta has "Reporting starts" AND "Month"; X has "Time period") */
  date:        ['day','date','reporting starts','start date','date start','by day','time period','period','month','التاريخ'],
  campaign:    ['campaign name','campaign','campaign title','اسم الحملة','الحملة'],
  adset:       ['ad set name','adset name','ad set','ad group name','ad group','adgroup name','المجموعة الإعلانية'],
  ad:          ['ad name','ad','ad title','creative name','اسم الإعلان','الإعلان'],
  status:      ['delivery status','delivery','status','campaign state','campaign delivery','ad delivery'],
  budget:      ['budget','campaign budget','daily budget','lifetime budget'],
  spend:       ['amount spent','spend','cost','total cost','total spent','amount','spent','التكلفة','المبلغ المنفق'],
  impressions: ['impressions','impr.','impr','paid impressions','الظهور','مرات الظهور'],
  clicks:      ['clicks (all)','clicks','all clicks','النقرات'],
  link_clicks: ['link clicks','clicks (destination)','outbound clicks','destination clicks','swipe ups','swipes','نقرات الرابط'],
  conversions: ['results','conversions','purchases','website purchases','total conversions','conv.','النتائج','التحويلات'],
  revenue:     ['purchases conversion value','purchase conversion value','conv. value','conversion value','total conv. value','revenue','conv value','قيمة التحويل'],
  reach:       ['reach','unique reach','الوصول'],
  video_views: ['video plays','video views','3-second video plays','2-second continuous video plays','video views at 25%','المشاهدات'],
  engagements: ['post engagements','post engagement','engagements','page engagement','interactions','التفاعلات'],
  leads:       ['leads','on-facebook leads','lead','العملاء المحتملون'],
  currency:    ['currency','currency code','account currency','العملة'],
  /* Followers — captured automatically from files (organic exports OR
     paid follow/page-like columns in ads exports). */
  followers:        ['followers','total followers','lifetime followers','follower count','fans','page likes total','total page likes','subscribers','إجمالي المتابعين','المتابعون'],
  followers_gained: ['new followers','followers gained','net followers','net follower growth','follower growth','follows','paid follows','page likes','page follows','متابعون جدد','متابعين جدد'],
};
const R_METRIC_FIELDS = ['spend','impressions','clicks','link_clicks','conversions','revenue',
  'reach','video_views','engagements','leads'];

/* ---------- Platform fingerprints -------------------------------------- */
const R_FINGERPRINTS = {
  meta:     ['ad set', 'amount spent', 'reporting starts', 'reporting ends', 'delivery level', 'results', 'frequency'],
  google:   ['campaign type', 'avg. cpc', 'currency code', 'search impr. share', 'interactions', 'conv. value', 'campaign state'],
  tiktok:   ['ad group name', 'cpc (destination)', 'cost per conversion', 'video views at 25%', 'cpm (cost per 1,000 impressions)', 'clicks (destination)'],
  snapchat: ['swipe ups', 'swipes', 'story opens', 'paid impressions', 'attachment total views', 'shares'],
  x:        ['on/off', 'time period', 'searches', 'tweet', 'promoted', 'cost per link click', 'billed charge', 'follows'],
  linkedin: ['sponsored', 'member', 'total social actions', 'click through rate', 'cost in usd', 'leads (li)'],
};
function rDetectPlatform(headers){
  const hay = headers.map(h=>String(h).toLowerCase()).join(' | ');
  let best = null;
  Object.entries(R_FINGERPRINTS).forEach(([plat, sigs])=>{
    const score = sigs.reduce((a,s)=> a + (hay.includes(s) ? 1 : 0), 0);
    if (!best || score > best.score) best = { platform:plat, score };
  });
  if (!best || best.score === 0) return { platform:null, confidence:0 };
  return { platform:best.platform, confidence: best.score >= 3 ? 1 : best.score >= 2 ? 0.7 : 0.4 };
}

/* ---------- Header auto-mapping (scored) --------------------------------
   THE 18-vs-5,240 SAR bug lived here: spend's synonym 'cost' prefix-matched
   "Cost per result" (which sits before "Amount spent (SAR)" in Meta exports),
   so spend summed cost-per-result ratios. Now: per-field EXCLUDE guards stop
   generic synonyms grabbing derived/ratio columns, and every header is scored
   (exact > "syn (unit)" > word-prefix > word-contains) — the BEST match wins,
   not the first. */
const R_MAP_EXCLUDE = {
  spend:       /cost per|\bcpc\b|\bcpm\b|\bcpa\b|per result|per click|per view|per follow|per lead|rate/,
  clicks:      /link|outbound|destination|unique|cost|rate|\bper\b/,
  link_clicks: /cost|rate|\bper\b/,
  conversions: /rate|cost|type|value|initial|\bper\b/,
  impressions: /cost|\bper\b|share/,
  reach:       /cost|\bper\b|frequency/,
  revenue:     /cost|\bper\b|rate/,
  video_views: /cost|\bper\b|rate/,
  engagements: /cost|\bper\b|rate|ranking/,
  leads:       /cost|\bper\b|rate/,
  followers:   /cost|\bper\b|rate|new|gained|growth/,
  followers_gained: /cost|\bper\b|rate|total|lifetime/,
  budget:      /remaining|utili[sz]ation/,
  date:        /\bend\b|\bends\b/,          // prefer "reporting starts" over "…ends"
};
/* Priority order matters: link_clicks claims "Link clicks" before clicks runs. */
const R_MAP_ORDER = ['date','campaign','adset','ad','status','budget','spend',
  'impressions','reach','link_clicks','clicks','conversions','revenue',
  'video_views','engagements','leads','followers_gained','followers','currency'];
function _rEscRe(s){ return s.replace(/[.*+?^${}()|[\]\\]/g, '\\$&'); }
function rScoreHeader(h, syns){
  let best = 0, bestPr = 0;
  for (let si = 0; si < syns.length; si++){
    const s = syns[si], pr = syns.length - si;   // earlier synonym = higher priority
    let sc = 0;
    if (h === s) sc = 100;
    else if (new RegExp('^' + _rEscRe(s) + '\\s*\\(.+\\)$').test(h)) sc = 92;
    else if (h.startsWith(s + ' ') || h.startsWith(s + ':')) sc = 70;
    else if (s.length > 4 && (h.includes(' ' + s) || h.includes(s + ' '))) sc = 45;
    if (sc > best || (sc === best && pr > bestPr)){ best = sc; bestPr = pr; }
  }
  return { score: best, pr: bestPr };
}
function rMapHeaders(headers){
  const map = {};       // field -> header
  const used = new Set();
  const low = headers.map(h=>String(h).trim().toLowerCase().replace(/\s+/g,' '));
  for (const field of R_MAP_ORDER){
    const syns = R_SYNONYMS[field];
    if (!syns) continue;
    const ex = R_MAP_EXCLUDE[field];
    let bestIdx = -1, bestScore = 0, bestPr = 0;
    low.forEach((h, i)=>{
      if (used.has(i)) return;
      const r = rScoreHeader(h, syns);
      /* exclusion guards stop FUZZY matches only — an exact synonym match
         (e.g. link_clicks:'clicks (destination)') always passes */
      if (ex && ex.test(h) && r.score < 100) return;
      if (r.score > bestScore || (r.score === bestScore && r.pr > bestPr)){
        bestScore = r.score; bestPr = r.pr; bestIdx = i;
      }
    });
    if (bestIdx >= 0 && bestScore >= 45){ map[field] = headers[bestIdx]; used.add(bestIdx); }
  }
  const unmapped = headers.filter((h,i)=>!used.has(i));
  return { map, unmapped };
}
/* Period hints from export preambles ("June 1, 2026 - July 11, 2026"),
   ISO ranges ("2026-04-05 - 2026-07-11") or filenames (…_20260701-20260711). */
function rExtractPeriod(text){
  const iso = (d)=>{ const x=new Date(d); x.setHours(12); return x.toISOString().slice(0,10); };
  const named = [...String(text).matchAll(/([A-Z][a-z]+ \d{1,2}, \d{4})/g)]
    .map(m=>new Date(m[1])).filter(d=>!isNaN(d));
  if (named.length >= 2){
    named.sort((a,b)=>a-b);
    return { start: iso(named[0]), end: iso(named[named.length-1]) };
  }
  const m2 = String(text).match(/(20\d{2})(\d{2})(\d{2})\s*[-–]\s*(20\d{2})(\d{2})(\d{2})/);
  if (m2) return { start:`${m2[1]}-${m2[2]}-${m2[3]}`, end:`${m2[4]}-${m2[5]}-${m2[6]}` };
  const m3 = String(text).match(/(\d{4}-\d{2}-\d{2})\s*[-–]\s*(\d{4}-\d{2}-\d{2})/);
  if (m3) return { start:m3[1], end:m3[2] };
  return null;
}

/* ---------- Value parsing ----------------------------------------------- */
function rNum(v){
  if (v === null || v === undefined || v === '') return 0;
  if (typeof v === 'number') return Number.isFinite(v) ? v : 0;
  let s = String(v).trim();
  /* Arabic-Indic (٠-٩) and Extended (۰-۹) digits → ASCII */
  s = s.replace(/[٠-٩]/g, d=>String(d.charCodeAt(0)-0x0660))
       .replace(/[۰-۹]/g, d=>String(d.charCodeAt(0)-0x06F0));
  const neg = /^\(.*\)$/.test(s) || /^\s*-/.test(s);
  /* strip currency words/symbols, spaces (incl. NBSP), percent — keep digits . , */
  s = s.replace(/[^\d.,]/g, '');
  if (!s) return 0;
  if (s.includes(',') && s.includes('.')){
    /* both present: the later one is the decimal separator */
    if (s.lastIndexOf(',') > s.lastIndexOf('.')) s = s.replace(/\./g,'').replace(',', '.');
    else s = s.replace(/,/g,'');
  } else if (s.includes(',')){
    const parts = s.split(',');
    /* "1,234" / "1,234,567" = thousands; "12,5" = decimal comma */
    s = (parts.length > 1 && parts.slice(1).every(p=>p.length===3)) ? parts.join('') : s.replace(/,/g,'.');
  }
  const n = parseFloat(s);
  if (!Number.isFinite(n)) return 0;
  return neg ? -n : n;
}
function rDateNorm(v){
  if (v === null || v === undefined || v === '') return null;
  if (v instanceof Date && !isNaN(v)) return v.toISOString().slice(0,10);
  if (typeof v === 'number'){        // Excel serial
    const d = new Date(Math.round((v - 25569) * 86400 * 1000));
    return isNaN(d) ? null : d.toISOString().slice(0,10);
  }
  const s = String(v).trim();
  if (/^\d{4}-\d{2}-\d{2}/.test(s)) return s.slice(0,10);
  const m = s.match(/^(\d{1,2})[\/.-](\d{1,2})[\/.-](\d{2,4})$/);
  if (m){
    let [_, a, b, y] = m;
    a = +a; b = +b; y = +y < 100 ? 2000 + +y : +y;
    const day = a > 12 ? a : b > 12 ? b : b;       // ambiguous → assume D second (US) unless a>12
    const mon = a > 12 ? b : a;
    const d = new Date(Date.UTC(y, mon-1, day));
    return isNaN(d) ? null : d.toISOString().slice(0,10);
  }
  const d = new Date(s);
  return isNaN(d) ? null : d.toISOString().slice(0,10);
}
function rDetectCurrency(headers, rows, map){
  const inHeader = headers.map(h=>String(h)).join(' ').match(/\((SAR|EGP|USD|AED|EUR|GBP|KWD|QAR|BHD)\)/i);
  if (inHeader) return inHeader[1].toUpperCase();
  if (map.currency && rows.length){
    const v = String(rows[0][map.currency] || '').toUpperCase().match(/[A-Z]{3}/);
    if (v) return v[0];
  }
  return null;
}
const rSlug = (s)=> String(s||'').toLowerCase().trim().replace(/[^\w؀-ۿ]+/g,'-').replace(/^-+|-+$/g,'').slice(0,80) || 'x';

/* ---------- File analysis ------------------------------------------------ */
async function rAnalyzeFile(file){
  const buf = await file.arrayBuffer();
  const wb = XLSX.read(buf, { type:'array', cellDates:true });
  const ws = wb.Sheets[wb.SheetNames[0]];
  /* Array-of-arrays first: the header row is NOT always row 1 — Google and
     LinkedIn exports put a report title + date-range preamble above it.
     Score the first 12 rows by synonym-hit count; the best row is the header. */
  const aoa = XLSX.utils.sheet_to_json(ws, { header:1, defval:'', raw:true });
  if (!aoa.length) return { name:file.name, error:'No rows found', rows:[], warnings:[] };
  let headerIdx = 0, bestHits = 0;
  for (let i = 0; i < Math.min(12, aoa.length); i++){
    const cand = (aoa[i]||[]).map(v=>String(v ?? ''));
    if (cand.filter(c=>c.trim()!=='').length < 3) continue;
    const hits = Object.keys(rMapHeaders(cand).map).length;
    if (hits > bestHits){ bestHits = hits; headerIdx = i; }
  }
  const headers = (aoa[headerIdx]||[]).map((v,i)=>{
    const s = String(v ?? '').trim(); return s || ('Column ' + (i+1));
  });
  const raw = aoa.slice(headerIdx + 1)
    .filter(r=>(r||[]).some(v=>String(v ?? '').trim() !== ''))
    .map(r=>Object.fromEntries(headers.map((h,i)=>[h, (r||[])[i] ?? ''])));
  if (!raw.length) return { name:file.name, error:'No data rows found', rows:[], warnings:[] };
  const { map, unmapped } = rMapHeaders(headers);
  const det = rDetectPlatform(headers);
  const warnings = [];
  /* period hint from the preamble rows + the filename */
  const periodHint = rExtractPeriod(
    aoa.slice(0, headerIdx).flat().map(v=>String(v ?? '')).join(' • ') + ' • ' + file.name);
  if (headerIdx > 0) warnings.push(`Report title/preamble detected — headers taken from row ${headerIdx+1}`);

  /* strip totals rows: ANY name column saying "Total…", or all names empty/'-' */
  const isTotal = (v)=>/^\s*(grand\s+)?total(s)?\b|^الإجمالي|^المجموع/i.test(String(v ?? ''));
  const nameKeys = [map.campaign, map.adset, map.ad].filter(Boolean);
  let rows = raw.filter(r=>{
    const names = nameKeys.map(k=>String(r[k] ?? '').trim());
    if (names.some(isTotal)) return false;
    if (nameKeys.length && names.every(n=>!n || n==='-')) return false;
    return true;
  });
  if (rows.length < raw.length) warnings.push(`${raw.length - rows.length} summary/empty row(s) stripped`);

  /* kind: 'ads' (entity data) or 'brand' (organic/follower export: date +
     followers, no campaign structure) */
  const hasEntity = !!(map.campaign || map.adset || map.ad);
  const hasFollowers = !!(map.followers || map.followers_gained);
  const kind = !hasEntity && hasFollowers ? 'brand' : 'ads';
  const level = kind==='brand' ? 'brand' : (map.ad ? 'ad' : map.adset ? 'adset' : 'campaign');
  if (kind === 'ads'){
    if (!map.campaign && level === 'campaign')
      warnings.push('No campaign-name column found — map it below');
    if (!map.spend) warnings.push('No spend column detected — map it below');
  }
  if (hasFollowers) warnings.push('Follower columns detected — will feed the Executive Summary automatically');

  let dates = null;
  if (map.date){
    const ds = rows.map(r=>rDateNorm(r[map.date])).filter(Boolean).sort();
    if (ds.length) dates = { start: ds[0], end: (periodHint && periodHint.end > ds[ds.length-1]) ? periodHint.end : ds[ds.length-1], daily:true };
    /* period-total exports (Meta "Reporting starts/ends", "Month" ranges):
       each row is a period SUM, stamped on the period's start date */
    const lowDate = String(map.date).toLowerCase();
    if (/start|month/.test(lowDate) || headers.some(h=>/reporting ends/i.test(h))){
      warnings.push('Rows are period totals (not daily) — each period is stamped on its start date');
    }
  } else if (periodHint){
    dates = { ...periodHint, daily:false };
    warnings.push(`No date column — period ${periodHint.start} → ${periodHint.end} detected from the file; totals stamped on ${periodHint.start}`);
  } else {
    warnings.push('No date column — totals will be stamped on one date (pick it below)');
  }
  const currency = rDetectCurrency(headers, rows, map);
  /* custom-metric candidates: numeric AND summable — never ratio/rank columns
     (cost per…, rates, rankings would aggregate wrongly) */
  const numericUnmapped = unmapped.filter(h=>
    !/cost per|cost \/|\brate\b|ranking|score|\bper\b|cpc|cpm|cpa|\bctr\b|\bcvr\b|frequency|\bid\b|length|dwell|roas|return on ad spend|budget/i.test(String(h)) &&
    rows.slice(0,20).some(r=> typeof r[h]==='number' || /^[\d.,%\s]+$/.test(String(r[h]).trim()) && String(r[h]).trim()!==''));

  return {
    id: 'f' + Math.random().toString(36).slice(2,8),
    name: file.name, headers, rowsData: rows, map, unmapped, numericUnmapped,
    platform: det.platform, confidence: det.confidence,
    kind, level, dates, currency, warnings, periodHint,
    stampDate: (periodHint && periodHint.start) || new Date().toISOString().slice(0,10),
    keepCustom: {},           // unmapped header -> bool (keep as custom metric)
  };
}

/* ---------- Bundle building (hierarchy + rollups) ------------------------ */
/* Most granular file wins per platform: its rows define metrics for ALL
   levels (rollups derived). Less granular files only enrich budget/status. */
function rBuildBundles(files){
  const byPlat = {};
  files.forEach(f=>{ if (f.platform) (byPlat[f.platform] = byPlat[f.platform] || []).push(f); });
  const bundle = { campaigns:[], adsets:[], ads:[], metrics:[], brand_metrics:[] };
  const notes = [];
  const rank = { campaign:0, adset:1, ad:2 };

  /* ---- Followers: captured from files automatically ----
     Per file, aggregate per date (gained = sum of rows, total = max of rows);
     per platform|date, merge across files with MAX (same underlying data at
     different granularities must not double-count). */
  const brandFacts = {};   // plat|date -> { followers, followers_gained }
  files.forEach(f=>{
    if (!f.platform || (!f.map.followers && !f.map.followers_gained)) return;
    const perDate = {};
    f.rowsData.forEach(r=>{
      const date = f.map.date ? rDateNorm(r[f.map.date]) : f.stampDate;
      if (!date) return;
      const t = perDate[date] = perDate[date] || { g:0, f:0 };
      if (f.map.followers_gained) t.g += rNum(r[f.map.followers_gained]);
      if (f.map.followers) t.f = Math.max(t.f, rNum(r[f.map.followers]));
    });
    Object.entries(perDate).forEach(([date, v])=>{
      const k = f.platform + '|' + date;
      const cur = brandFacts[k] = brandFacts[k] || { platform:f.platform, date, g:0, f:0 };
      cur.g = Math.max(cur.g, Math.round(v.g));
      cur.f = Math.max(cur.f, Math.round(v.f));
    });
  });
  Object.values(brandFacts).forEach(x=>{
    if (!x.g && !x.f) return;
    bundle.brand_metrics.push({ platform:x.platform, date:x.date,
      followers: x.f || undefined, followers_gained: x.g || undefined, source:'manual' });
  });

  Object.entries(byPlat).forEach(([plat, group])=>{
    const entityFiles = group.filter(f=>f.kind !== 'brand');
    if (!entityFiles.length) return;    // brand-only platform: followers captured above
    const primary = [...entityFiles].sort((a,b)=> rank[b.level]-rank[a.level])[0];
    const others = entityFiles.filter(f=>f!==primary);

    const campaigns = {};   // ext -> {name, budget, status}
    const adsets = {};      // ext -> {campaign_ext, name, status}
    const ads = {};         // ext -> {adset_ext, campaign_ext, name, status}
    const facts = {};       // level|ext|date -> sums

    const addFact = (level, ext, date, sums)=>{
      const k = level + '|' + ext + '|' + date;
      const t = facts[k] = facts[k] || { level, ext, date, ...Object.fromEntries(R_METRIC_FIELDS.map(m=>[m,0])), extra:{} };
      R_METRIC_FIELDS.forEach(m=>{ t[m] += sums[m]||0; });
      Object.entries(sums.extra||{}).forEach(([k2,v])=>{ t.extra[k2] = (t.extra[k2]||0) + v; });
    };

    const ingestRows = (f, metricsToo)=>{
      f.rowsData.forEach(r=>{
        const cName = f.map.campaign ? String(r[f.map.campaign]).trim() : 'Imported';
        if (!cName) return;
        const cExt = 'mu-' + rSlug(cName);
        campaigns[cExt] = campaigns[cExt] || { name:cName };
        if (f.map.budget) campaigns[cExt].budget = rNum(r[f.map.budget]) || campaigns[cExt].budget;
        if (f.map.status){
          const st = String(r[f.map.status]).toLowerCase();
          campaigns[cExt].status = /off|paused|inactive|completed|ended|halted|stopped|not_deliver|archived/.test(st) ? 'paused' : 'active';
        }
        let aExt = null, adExt = null;
        if (f.map.adset && String(r[f.map.adset]).trim()){
          const aName = String(r[f.map.adset]).trim();
          aExt = cExt + '~' + rSlug(aName);
          adsets[aExt] = adsets[aExt] || { campaign_ext:cExt, name:aName };
        }
        if (f.map.ad && String(r[f.map.ad]).trim()){
          const adName = String(r[f.map.ad]).trim();
          const parent = aExt || (aExt = (()=>{  // ad without adset → synthetic adset
            const synth = cExt + '~all';
            adsets[synth] = adsets[synth] || { campaign_ext:cExt, name:cName };
            return synth;
          })());
          adExt = parent + '~' + rSlug(adName);
          ads[adExt] = ads[adExt] || { adset_ext:parent, campaign_ext:cExt, name:adName };
        }
        if (!metricsToo) return;
        const date = f.map.date ? rDateNorm(r[f.map.date]) : f.stampDate;
        if (!date) return;
        const sums = { extra:{} };
        R_METRIC_FIELDS.forEach(m=>{ if (f.map[m]) sums[m] = rNum(r[f.map[m]]); });
        /* clicks fallback: many exports only have link clicks */
        if (!sums.clicks && sums.link_clicks) sums.clicks = sums.link_clicks;
        Object.entries(f.keepCustom||{}).forEach(([h,on])=>{
          if (on) sums.extra[rSlug(h)] = rNum(r[h]);
        });
        /* facts at the file's own grain + rollups upward */
        if (adExt) addFact('ad', adExt, date, sums);
        if (aExt)  addFact('adset', aExt, date, sums);
        addFact('campaign', cExt, date, sums);
      });
    };

    ingestRows(primary, true);
    others.forEach(f=>{
      ingestRows(f, false);   // entities/budget/status only — no metrics (no double-count)
      /* reconciliation: compare file spend total vs primary-derived total */
      if (f.map.spend){
        const fileTotal = f.rowsData.reduce((a,r)=>a+rNum(r[f.map.spend]),0);
        const primTotal = Object.values(facts).filter(x=>x.level==='campaign').reduce((a,x)=>a+x.spend,0);
        if (fileTotal > 0 && primTotal > 0 && Math.abs(fileTotal-primTotal)/fileTotal > 0.02){
          notes.push(`${rPlatform(plat).label}: “${f.name}” totals differ ${(Math.abs(fileTotal-primTotal)/fileTotal*100).toFixed(1)}% from the more granular file — the granular file was used.`);
        }
      }
    });

    Object.entries(campaigns).forEach(([ext,c])=> bundle.campaigns.push({
      external_id:ext, platform:plat, name:c.name, status:c.status||'active', budget:c.budget||0 }));
    Object.entries(adsets).forEach(([ext,a])=> bundle.adsets.push({
      external_id:ext, campaign_external_id:a.campaign_ext, platform:plat, name:a.name, status:a.status||'active' }));
    Object.entries(ads).forEach(([ext,a])=> bundle.ads.push({
      external_id:ext, adset_external_id:a.adset_ext, campaign_external_id:a.campaign_ext,
      platform:plat, name:a.name, status:a.status||'active' }));
    Object.values(facts).forEach(x=> bundle.metrics.push({
      entity_type:x.level, entity_external_id:x.ext, platform:plat, date:x.date,
      spend:+x.spend.toFixed(2), impressions:Math.round(x.impressions), clicks:Math.round(x.clicks),
      conversions:Math.round(x.conversions), revenue:+x.revenue.toFixed(2),
      reach:Math.round(x.reach), video_views:Math.round(x.video_views),
      engagements:Math.round(x.engagements), leads:Math.round(x.leads),
      link_clicks:Math.round(x.link_clicks),
      extra: Object.keys(x.extra).length ? x.extra : undefined,
    }));
  });
  return { bundle, notes };
}

/* Chunked POST — shared hosting friendly; ingest is an idempotent upsert. */
async function rIngestBundle(accountId, bundle, onProgress){
  const CHUNK = 800;
  const metaOnly = { campaigns:bundle.campaigns, adsets:bundle.adsets, ads:bundle.ads,
    brand_metrics:bundle.brand_metrics||[], metrics:[] };
  const chunks = [];
  for (let i=0; i<bundle.metrics.length; i+=CHUNK) chunks.push(bundle.metrics.slice(i, i+CHUNK));
  if (!chunks.length) chunks.push([]);
  let total = { campaigns:0, adsets:0, ads:0, metrics:0, brand_metrics:0 };
  for (let i=0; i<chunks.length; i++){
    onProgress && onProgress(`Importing ${i+1}/${chunks.length}…`);
    const payload = i===0 ? { ...metaOnly, metrics:chunks[i] } : { metrics:chunks[i] };
    /* source:'manual' — rows land behind the manual wall, never mixed with system data */
    const res = await RelayAPI.ingest({ account:accountId, source:'manual', bundle:payload });
    const c = (res && res.imported) || {};
    ['campaigns','adsets','ads','metrics','brand_metrics'].forEach(k=>{ total[k] += c[k]||0; });
  }
  return total;
}

/* ============================ Wizard UI ============================ */
function ReportUploadWizard({ open, onClose, accountId, accounts, currentUser, onDone }){
  const [step, setStep] = useState('drop');
  const [files, setFiles] = useState([]);
  const [brandName, setBrandName] = useState('');
  const [target, setTarget] = useState('current');   // current | new
  const [busy, setBusy] = useState(false);
  const [progress, setProgress] = useState('');
  const [error, setError] = useState('');
  const [result, setResult] = useState(null);
  const [notes, setNotes] = useState([]);
  const inputRef = useRef(null);
  const canCreateAccount = typeof can==='function' && can(currentUser,'accounts.manage');
  const acc = (accounts||[]).find(a=>a.id===accountId);

  useEffect(()=>{ if (open){ setStep('drop'); setFiles([]); setError(''); setResult(null); setNotes([]); } }, [open]);
  if (!open) return null;

  const addFiles = async (list)=>{
    setBusy(true); setError('');
    try {
      const parsed = [];
      for (const f of Array.from(list)){
        if (!/\.(csv|xlsx|xls)$/i.test(f.name)){ setError(`${f.name}: only CSV/XLSX files`); continue; }
        parsed.push(await rAnalyzeFile(f));
      }
      setFiles(cur=>[...cur, ...parsed.filter(p=>!p.error)]);
      const bad = parsed.filter(p=>p.error);
      if (bad.length) setError(bad.map(b=>`${b.name}: ${b.error}`).join(' · '));
      if (parsed.some(p=>!p.error)) setStep('review');
    } catch(e){ setError(e.message || 'Could not parse file'); }
    setBusy(false);
  };
  const patchFile = (id, patch)=> setFiles(fs=>fs.map(f=>f.id===id?{...f,...patch}:f));

  const overlaps = (()=>{
    const seen = {};
    const out = [];
    files.forEach(f=>{
      if (!f.platform || !f.dates) return;
      const k = f.platform + '|' + f.level;
      if (seen[k]) out.push(`${f.name} overlaps ${seen[k]} (${rPlatform(f.platform).label} ${f.level}) — rows for the same entity+date will be updated, not duplicated.`);
      else seen[k] = f.name;
    });
    return out;
  })();

  const readyFiles = files.filter(f=> f.platform && (f.kind==='brand'
    ? (f.map.followers || f.map.followers_gained)
    : (f.map.spend && (f.map.campaign||f.map.adset||f.map.ad))));
  const canImport = readyFiles.length === files.length && files.length > 0 && (target==='current' || brandName.trim());

  const doImport = async ()=>{
    setBusy(true); setError('');
    try {
      let accId = accountId;
      if (target === 'new'){
        const a = await RelayAPI.accounts.create({ name: brandName.trim(),
          currency: files.find(f=>f.currency)?.currency || (acc && acc.currency) || 'USD',
          tier:'Manual', handle:'@'+rSlug(brandName) });
        accId = a.id;
      }
      /* persist kept custom metrics into the report metric registry */
      files.forEach(f=> Object.entries(f.keepCustom||{}).forEach(([h,on])=>{
        if (on) rSaveCustomMetric(accId, { key:rSlug(h), label:h, fmt:'int',
          agg:{formula:rSlug(h)}, good:null });
      }));
      const { bundle, notes:recon } = rBuildBundles(files);
      setNotes(recon);
      const counts = await rIngestBundle(accId, bundle, setProgress);
      const plats = rSortPlatforms([...new Set(files.map(f=>f.platform))]);
      const allDates = files.filter(f=>f.dates).flatMap(f=>[f.dates.start, f.dates.end]).sort();
      setResult({ counts, accId, platforms:plats,
        start: allDates[0] || null, end: allDates[allDates.length-1] || null,
        brandName: brandName.trim() || (acc && acc.name) || '' });
      setStep('done');
    } catch(e){ setError(e.message || 'Import failed'); }
    setBusy(false); setProgress('');
  };

  const confBadge = (f)=> f.confidence >= 1 ? null :
    <Pill tone="warn" className="ml-1">{f.confidence>=0.7?'check platform':'unsure — pick platform'}</Pill>;

  return (
    <Modal open={open} onClose={busy?()=>{}:onClose} width={760}>
      <div className="p-5">
        <div className="text-[16px] font-bold mb-0.5">Import data files</div>
        <div className="text-[12px] text-ink-500 dark:text-ink-300 mb-4">
          CSV or Excel exports from any ad platform — campaigns, ad sets, or ads level.
        </div>

        {step==='drop' && (
          <div>
            <div onClick={()=>inputRef.current && inputRef.current.click()}
              onDragOver={e=>e.preventDefault()}
              onDrop={e=>{ e.preventDefault(); addFiles(e.dataTransfer.files); }}
              className="border-2 border-dashed border-ink-200 dark:border-white/15 rounded-2xl py-14 grid place-items-center cursor-pointer hover:border-accent-400 transition">
              <div className="text-center">
                <Icon name="upload" size={26} className="text-ink-400 mx-auto"/>
                <div className="text-[13.5px] font-semibold mt-2">{busy?'Parsing…':'Drop CSV / XLSX files here'}</div>
                <div className="text-[11.5px] text-ink-400 mt-1">or click to browse — multiple files supported</div>
              </div>
            </div>
            <input ref={inputRef} type="file" multiple accept=".csv,.xlsx,.xls" className="hidden"
              onChange={e=>{ addFiles(e.target.files); e.target.value=''; }}/>
            {error && <div className="mt-3 text-[12px] text-rose-600 dark:text-rose-300">{error}</div>}
          </div>
        )}

        {step==='review' && (
          <div>
            <div className="space-y-3 max-h-[420px] overflow-y-auto scroll-soft pr-1">
              {files.map(f=>(
                <div key={f.id} className="border border-ink-200 dark:border-white/10 rounded-xl p-3.5">
                  <div className="flex items-center gap-2 flex-wrap">
                    <Icon name="file" size={15} className="text-ink-400"/>
                    <span className="text-[13px] font-bold truncate max-w-[240px]">{f.name}</span>
                    <Pill tone="neutral">{f.rowsData.length.toLocaleString()} rows</Pill>
                    <Pill tone="accent">{f.kind==='brand' ? 'followers data' : f.level + ' level'}</Pill>
                    {f.kind!=='brand' && (f.map.followers || f.map.followers_gained) &&
                      <Pill tone="neutral">+followers</Pill>}
                    {f.dates && <Pill tone="neutral">{f.dates.start} → {f.dates.end}</Pill>}
                    {f.currency && <Pill tone="neutral">{f.currency}</Pill>}
                    {confBadge(f)}
                    <button className="ml-auto text-ink-400 hover:text-rose-500"
                      onClick={()=>setFiles(fs=>fs.filter(x=>x.id!==f.id))}><Icon name="x" size={14}/></button>
                  </div>

                  <div className="grid sm:grid-cols-2 gap-2.5 mt-3">
                    <label className="block">
                      <div className="text-[10.5px] uppercase font-semibold text-ink-400 mb-1">Platform</div>
                      <select value={f.platform||''} onChange={e=>patchFile(f.id,{platform:e.target.value||null, confidence:1})}
                        className="w-full h-9 px-2 rounded-lg border border-ink-200 dark:border-white/10 bg-white dark:bg-ink-900/60 text-[12.5px]">
                        <option value="">— select —</option>
                        {REPORT_PLATFORMS.map(p=><option key={p.id} value={p.id}>{p.label}</option>)}
                      </select>
                    </label>
                    {!f.dates && (
                      <label className="block">
                        <div className="text-[10.5px] uppercase font-semibold text-ink-400 mb-1">Stamp totals on date</div>
                        <input type="date" value={f.stampDate} onChange={e=>patchFile(f.id,{stampDate:e.target.value})}
                          className="w-full h-9 px-2 rounded-lg border border-ink-200 dark:border-white/10 bg-white dark:bg-ink-900/60 text-[12.5px]"/>
                      </label>
                    )}
                    {(f.kind==='brand' ? ['date'] : ['campaign','spend','date']).filter(k=>!f.map[k]).map(k=>(
                      <label key={k} className="block">
                        <div className="text-[10.5px] uppercase font-semibold text-amber-600 mb-1">Map “{k}” column</div>
                        <select value={f.map[k]||''} onChange={e=>patchFile(f.id,{map:{...f.map,[k]:e.target.value||undefined}})}
                          className="w-full h-9 px-2 rounded-lg border border-amber-300 bg-white dark:bg-ink-900/60 text-[12.5px]">
                          <option value="">— ignore —</option>
                          {f.headers.map(h=><option key={h} value={h}>{h}</option>)}
                        </select>
                      </label>
                    ))}
                  </div>

                  {/* Mapping transparency: SHOW what mapped where, allow override.
                      (The 18-vs-5,240 bug hid because the guess was invisible.) */}
                  <div className="mt-2.5 text-[11px] text-ink-500 dark:text-ink-300 flex items-center gap-1.5 flex-wrap">
                    {['spend','conversions','impressions','reach','clicks','followers_gained','date']
                      .filter(k=>f.map[k]).map(k=>(
                        <span key={k} className="inline-flex items-center gap-1 px-1.5 py-0.5 rounded bg-ink-50 dark:bg-white/5 border border-ink-100 dark:border-white/10 font-mono text-[10px]">
                          <b>{k==='conversions'?'results':k.replace('_',' ')}</b> ← {f.map[k]}
                        </span>
                      ))}
                    <button onClick={()=>patchFile(f.id,{showMap:!f.showMap})}
                      className="text-accent-600 dark:text-accent-300 font-semibold text-[10.5px]">
                      {f.showMap ? 'hide mapping' : 'adjust mapping'}
                    </button>
                  </div>
                  {f.showMap && (
                    <div className="mt-2 grid sm:grid-cols-3 gap-2">
                      {['spend','conversions','impressions','reach','clicks','link_clicks',
                        'followers_gained','followers','video_views','engagements','revenue','date'].map(k=>(
                        <label key={k} className="block">
                          <div className="text-[9.5px] uppercase font-semibold text-ink-400 mb-0.5">
                            {k==='conversions'?'results':k.replace('_',' ')}
                          </div>
                          <select value={f.map[k]||''}
                            onChange={e=>patchFile(f.id,{map:{...f.map,[k]:e.target.value||undefined}})}
                            className="w-full h-7 px-1.5 rounded-md border border-ink-200 dark:border-white/10 bg-white dark:bg-ink-900/60 text-[11px]">
                            <option value="">— none —</option>
                            {f.headers.map(h=><option key={h} value={h}>{h}</option>)}
                          </select>
                        </label>
                      ))}
                    </div>
                  )}

                  {f.warnings.length>0 && (
                    <div className="mt-2 text-[11px] text-amber-600 dark:text-amber-300">
                      {f.warnings.map((w,i)=><div key={i}>• {w}</div>)}
                    </div>
                  )}
                  {f.numericUnmapped.length>0 && (
                    <div className="mt-2.5">
                      <div className="text-[10.5px] uppercase font-semibold text-ink-400 mb-1">
                        Unmapped numeric columns — keep as custom metrics?
                      </div>
                      <div className="flex flex-wrap gap-1.5">
                        {f.numericUnmapped.slice(0,8).map(h=>(
                          <button key={h} onClick={()=>patchFile(f.id,{keepCustom:{...f.keepCustom,[h]:!f.keepCustom[h]}})}
                            className={`text-[11px] px-2 py-1 rounded-full border transition ${f.keepCustom[h]
                              ? 'border-accent-400 bg-accent-50 dark:bg-accent-500/10 text-accent-700 dark:text-accent-300 font-semibold'
                              : 'border-ink-200 dark:border-white/10 text-ink-500'}`}>
                            {f.keepCustom[h]?'✓ ':''}{h}
                          </button>
                        ))}
                      </div>
                    </div>
                  )}
                </div>
              ))}
            </div>

            <button onClick={()=>inputRef.current && inputRef.current.click()}
              className="mt-2.5 text-[12px] text-accent-600 dark:text-accent-300 font-semibold">+ Add another file</button>
            <input ref={inputRef} type="file" multiple accept=".csv,.xlsx,.xls" className="hidden"
              onChange={e=>{ addFiles(e.target.files); e.target.value=''; }}/>

            {overlaps.map((o,i)=>(
              <div key={i} className="mt-2 text-[11px] text-amber-600 dark:text-amber-300">• {o}</div>
            ))}

            <div className="mt-4 pt-4 border-t border-ink-100 dark:border-white/10 grid sm:grid-cols-2 gap-3">
              <label className="block">
                <div className="text-[10.5px] uppercase font-semibold text-ink-400 mb-1">Brand name</div>
                <input value={brandName} onChange={e=>setBrandName(e.target.value)}
                  placeholder={acc ? acc.name : 'Client / brand name'}
                  className="w-full h-9 px-3 rounded-lg border border-ink-200 dark:border-white/10 bg-white dark:bg-ink-900/60 text-[12.5px]"/>
              </label>
              <label className="block">
                <div className="text-[10.5px] uppercase font-semibold text-ink-400 mb-1">Import into</div>
                <select value={target} onChange={e=>setTarget(e.target.value)}
                  className="w-full h-9 px-2 rounded-lg border border-ink-200 dark:border-white/10 bg-white dark:bg-ink-900/60 text-[12.5px]">
                  <option value="current">{acc ? acc.name : 'Current account'}</option>
                  {canCreateAccount && <option value="new">New workspace (from brand name)</option>}
                </select>
              </label>
            </div>

            {error && <div className="mt-3 text-[12px] text-rose-600 dark:text-rose-300">{error}</div>}
            <div className="mt-4 flex items-center gap-2">
              <div className="text-[11px] text-ink-400 font-mono flex-1">{busy ? progress || 'Working…' : ''}</div>
              <Btn variant="ghost" onClick={onClose} className={busy?'opacity-40 pointer-events-none':''}>Cancel</Btn>
              <Btn variant="accent" onClick={doImport}
                className={(!canImport||busy)?'opacity-50 pointer-events-none':''}>
                {busy?'Importing…':'Import '+files.length+' file'+(files.length===1?'':'s')}
              </Btn>
            </div>
          </div>
        )}

        {step==='done' && result && (
          <div className="text-center py-6">
            <span className="inline-flex w-14 h-14 rounded-full bg-emerald-100 dark:bg-emerald-500/15 text-emerald-600 items-center justify-center">
              <Icon name="check" size={26}/>
            </span>
            <div className="text-[15px] font-bold mt-3">Import complete</div>
            <div className="text-[12.5px] text-ink-500 dark:text-ink-300 mt-1 font-mono">
              {result.counts.campaigns} campaigns · {result.counts.adsets} ad sets · {result.counts.ads} ads · {result.counts.metrics} daily rows
              {result.counts.brand_metrics ? ` · ${result.counts.brand_metrics} follower rows` : ''}
            </div>
            {notes.map((n,i)=>(
              <div key={i} className="mt-2 text-[11px] text-amber-600 dark:text-amber-300">• {n}</div>
            ))}
            <div className="mt-5 flex items-center justify-center gap-2">
              <Btn variant="ghost" onClick={onClose}>Close</Btn>
              <Btn variant="accent" icon="chev-right" onClick={()=>{ onDone && onDone(result); }}>
                Build report from this data
              </Btn>
            </div>
          </div>
        )}
      </div>
    </Modal>
  );
}

Object.assign(window, {
  ReportUploadWizard, rAnalyzeFile, rBuildBundles, rIngestBundle,
  rDetectPlatform, rMapHeaders, R_SYNONYMS,
});
