/* 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{ 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 : {f.confidence>=0.7?'check platform':'unsure — pick platform'}; return ( {}:onClose} width={760}>
Import data files
CSV or Excel exports from any ad platform — campaigns, ad sets, or ads level.
{step==='drop' && (
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">
{busy?'Parsing…':'Drop CSV / XLSX files here'}
or click to browse — multiple files supported
{ addFiles(e.target.files); e.target.value=''; }}/> {error &&
{error}
}
)} {step==='review' && (
{files.map(f=>(
{f.name} {f.rowsData.length.toLocaleString()} rows {f.kind==='brand' ? 'followers data' : f.level + ' level'} {f.kind!=='brand' && (f.map.followers || f.map.followers_gained) && +followers} {f.dates && {f.dates.start} → {f.dates.end}} {f.currency && {f.currency}} {confBadge(f)}
{!f.dates && ( )} {(f.kind==='brand' ? ['date'] : ['campaign','spend','date']).filter(k=>!f.map[k]).map(k=>( ))}
{/* Mapping transparency: SHOW what mapped where, allow override. (The 18-vs-5,240 bug hid because the guess was invisible.) */}
{['spend','conversions','impressions','reach','clicks','followers_gained','date'] .filter(k=>f.map[k]).map(k=>( {k==='conversions'?'results':k.replace('_',' ')} ← {f.map[k]} ))}
{f.showMap && (
{['spend','conversions','impressions','reach','clicks','link_clicks', 'followers_gained','followers','video_views','engagements','revenue','date'].map(k=>( ))}
)} {f.warnings.length>0 && (
{f.warnings.map((w,i)=>
• {w}
)}
)} {f.numericUnmapped.length>0 && (
Unmapped numeric columns — keep as custom metrics?
{f.numericUnmapped.slice(0,8).map(h=>( ))}
)}
))}
{ addFiles(e.target.files); e.target.value=''; }}/> {overlaps.map((o,i)=>(
• {o}
))}
{error &&
{error}
}
{busy ? progress || 'Working…' : ''}
Cancel {busy?'Importing…':'Import '+files.length+' file'+(files.length===1?'':'s')}
)} {step==='done' && result && (
Import complete
{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` : ''}
{notes.map((n,i)=>(
• {n}
))}
Close { onDone && onDone(result); }}> Build report from this data
)}
); } Object.assign(window, { ReportUploadWizard, rAnalyzeFile, rBuildBundles, rIngestBundle, rDetectPlatform, rMapHeaders, R_SYNONYMS, });