Solabran — Data Sync Tool

// ═══════════════════════════════════════════════════════════
// SOLABRAN NUKE SCRIPT — Wipes ALL live data
// ⚠ Run ONLY on portal.solabran.ca (F12 → Console)
// ✅ DOWNLOADS A FULL BACKUP before deleting anything
// After this, use data-import.html Step 1→2→3 to reimport.
// ═══════════════════════════════════════════════════════════
(async () => {
  const TABLES = [
    'payments','invoices','estimates','jobs',
    'customers','product_catalog','settings',
    'catalog_versions','activities','receipts'
  ];

  async function fetchAll(t) {
    let all=[], page=1;
    while(true){
      const r=await fetch(`tables/${t}?limit=500&page=${page}`);
      if(!r.ok) break;
      const j=await r.json(); const rows=j.data||[];
      all=all.concat(rows); if(rows.length<500) break; page++;
    } return all;
  }

  // ── STEP 1: Type-to-confirm guard ────────────────────────────
  const answer = prompt('⚠️ DANGER: This will DELETE ALL DATA on portal.solabran.ca.\n\nA backup will be downloaded first.\n\nType  NUKE  (all caps) to continue, or Cancel to abort:');
  if (answer !== 'NUKE') { alert('Aborted. Nothing was changed.'); return; }

  // ── STEP 2: Download a full backup BEFORE deleting anything ──
  console.log('%c[Nuke] Downloading backup first…','color:#f97316;font-weight:bold;font-size:14px');
  const backup = { app:'Solabran CRM', backed_up: new Date().toISOString(), tables:{} };
  let backupTotal = 0;
  for(const t of TABLES){
    try {
      backup.tables[t] = await fetchAll(t);
      backupTotal += backup.tables[t].length;
      console.log(`[Nuke] Backed up ${t}: ${backup.tables[t].length} records`);
    } catch(e) { backup.tables[t]=[]; }
  }
  // Trigger download
  const blob = new Blob([JSON.stringify(backup,null,2)],{type:'application/json'});
  const a = document.createElement('a');
  a.href = URL.createObjectURL(blob);
  a.download = 'solabran-backup-before-nuke-' + new Date().toISOString().slice(0,10) + '.json';
  document.body.appendChild(a); a.click(); document.body.removeChild(a);
  console.log(`%c[Nuke] ✅ Backup downloaded — ${backupTotal} records saved`,'color:#10b981;font-weight:bold');

  // ── STEP 3: Second confirmation after seeing backup count ─────
  const confirm2 = confirm(`✅ Backup downloaded (${backupTotal} records saved to your Downloads folder).\n\nNow deleting all live data. Continue?`);
  if(!confirm2) { alert('Aborted after backup. Your data is unchanged. Use the downloaded backup file with the MERGE tool if needed.'); return; }

  // ── STEP 4: Delete everything ─────────────────────────────────
  let totalDel=0;
  console.log('%c[Nuke] Deleting…','color:#ef4444;font-weight:bold;font-size:14px');
  for(const t of TABLES){
    const rows=backup.tables[t]||[];
    let n=0;
    for(const row of rows){
      try{ const r=await fetch(`tables/${t}/${row.id}`,{method:'DELETE'}); if(r.ok||r.status===204) n++; }catch(e){}
    }
    totalDel+=n;
    console.log(`[Nuke] ${t}: ${n}/${rows.length} deleted`);
  }
  console.log(`%c[Nuke] Done — ${totalDel} records deleted`,'color:#10b981;font-weight:bold;font-size:14px');
  alert(`✅ Nuke complete!\n\n${totalDel} records deleted.\nBackup file saved to your Downloads folder.\n\nNow go back to data-import.html and follow Steps 1 → 2 → 3.\n\nIf you need to recover data, use the MERGE tool in data-import.html with your backup file.`);
})();

// ═══════════════════════════════════════════════════════════
// SOLABRAN DEDUP SCRIPT — Keeps newest, removes duplicates
// ⚠ Run ONLY on portal.solabran.ca (F12 → Console)
// ═══════════════════════════════════════════════════════════
(async () => {
  const BKEY = {
    customers:       ['customer_number','company_name'],
    estimates:       ['estimate_number'],
    invoices:        ['invoice_number'],
    jobs:            ['title'],
    payments:        ['invoice_id','amount','payment_date'],
    product_catalog: ['sku'],
    settings:        ['key'],
  };

  async function fetchAll(t) {
    let all=[], page=1;
    while(true){
      const r=await fetch(`tables/${t}?limit=500&page=${page}`);
      if(!r.ok) break;
      const j=await r.json(); const rows=j.data||[];
      all=all.concat(rows); if(rows.length<500) break; page++;
    } return all;
  }

  let totalDel=0;
  console.log('%c[Dedup] Starting…','color:#6366f1;font-weight:bold;font-size:14px');
  for(const [table,keyFields] of Object.entries(BKEY)){
    const rows=await fetchAll(table);
    if(!rows.length){ console.log(`[Dedup] ${table}: empty`); continue; }
    const groups={};
    for(const row of rows){
      const kp=keyFields.map(f=>String(row[f]??'').toLowerCase().trim());
      if(kp.every(k=>!k||k==='undefined')) continue;
      const key=kp.join('||');
      if(!groups[key]) groups[key]=[];
      groups[key].push(row);
    }
    let del=0;
    for(const [,group] of Object.entries(groups)){
      if(group.length<=1) continue;
      group.sort((a,b)=>(b.created_at||0)-(a.created_at||0));
      for(const row of group.slice(1)){
        try{ const r=await fetch(`tables/${table}/${row.id}`,{method:'DELETE'}); if(r.ok||r.status===204) del++; }catch(e){}
      }
    }
    totalDel+=del;
    console.log(`[Dedup] ${table}: ${rows.length} rows → ${del} duplicates removed`);
  }
  console.log(`%c[Dedup] Done — ${totalDel} duplicates removed`,'color:#10b981;font-weight:bold;font-size:14px');
  alert(`✅ Dedup complete!\n\n${totalDel} duplicate records removed.\n\nRefresh portal.solabran.ca to verify.\n\nIf customer names still show as raw IDs, run Steps 1→2→3 below to do a fresh import, OR use Step 4 to run the re-link script.`);
})();