// ───────────────────────────────────────────────────────────
// Google Sheets live data layer
// Uses the gviz/tq JSON endpoint — public, CORS-friendly, no API key.
// Sheet must be: Share → "Anyone with the link can view".
// ───────────────────────────────────────────────────────────

const SHEET_ID = '1Kmdc4I4gilOE8vOYHJki7yESV-NIrWpiLcGWPqzWxo8';
const SHEET_NAME = 'Dashboard';
const CACHE_KEY = 'cb_market_share_cache_v9';
const CACHE_TS_KEY = 'cb_market_share_cache_ts_v9';

// Build a gviz query URL for a given A1 range.
// We pass `headers=0` so row 1 is row 0 in the response (no header inference).
const gvizUrl = (range) => {
  const params = new URLSearchParams({
    sheet: SHEET_NAME,
    range,
    headers: '0',
    tqx: 'out:json',
  });
  return `https://docs.google.com/spreadsheets/d/${SHEET_ID}/gviz/tq?${params}`;
};

// Parse the gviz JSONP-ish wrapper.
// Response looks like: /*O_o*/\ngoogle.visualization.Query.setResponse({...});
const parseGviz = (text) => {
  const match = text.match(/setResponse\(([\s\S]+)\)/);
  if (!match) throw new Error('Unexpected gviz response shape');
  return JSON.parse(match[1]);
};

// Pull a 2D array of cell values from a gviz response.
const cellsFromGviz = (resp) => {
  if (!resp.table || !resp.table.rows) return [];
  return resp.table.rows.map(r =>
    (r.c || []).map(cell => (cell == null ? null : (cell.v != null ? cell.v : cell.f != null ? cell.f : null)))
  );
};

// Fetch one A1 range and return cells.
async function fetchRange(range) {
  const res = await fetch(gvizUrl(range));
  if (!res.ok) throw new Error(`HTTP ${res.status}`);
  const text = await res.text();
  const parsed = parseGviz(text);
  if (parsed.status === 'error') {
    throw new Error((parsed.errors || []).map(e => e.detailed_message || e.message).join('; '));
  }
  return cellsFromGviz(parsed);
}

// Coerce to number; null/empty → null
const num = (v) => {
  if (v == null || v === '') return null;
  if (typeof v === 'number') return v;
  const cleaned = String(v).replace(/[,%$\s]/g, '');
  const n = parseFloat(cleaned);
  return Number.isFinite(n) ? n : null;
};
const str = (v) => (v == null ? '' : String(v).trim());

// Percent helper: Sheet stores percentages as decimal fractions (0.627 = 62.7%).
// If the cell already contains a value > 1.5, assume it's already in % form (defensive).
const pct = (v) => {
  const n = num(v);
  if (n == null) return null;
  return Math.abs(n) <= 1.5 ? n * 100 : n;
};

// ─── Map raw rows → structured shapes ─────────────────────

// Defensive row filter — rejects section titles, header labels, blank rows.
// Each section requires its key numeric column to be a real number.
const isDataRow = (r, numCol = 1) => {
  const label = str(r[0]).toLowerCase();
  if (!label) return false;
  if (label === 'domain' || label === 'brand' || label === 'competitor') return false;
  if (/^(section\s+)?[a-e]\d?\s*[·\-—]/i.test(label)) return false;
  if (label.startsWith('section')) return false;
  return num(r[numCol]) != null;
};

// Section A — Traffic share (rows 9-13, B:N → 13 cols)
// B domain, C visits, D %market, E-N channel %s (10 channel cols)
function mapSectionA(rows) {
  return rows
    .filter(r => isDataRow(r, 1))
    .map(r => ({
      domain: str(r[0]),
      visits: num(r[1]),
      marketPct: pct(r[2]),
      channels: r.slice(3, 13).map(pct),
    }));
}

// Section B2 — Absolute channel visits (B:G → 6 cols, 5 channels + domain)
// B domain, C organic visits, D paid visits, E direct visits,
// F referral visits, G social visits.
// Total is computed (sheet's column H is a separate channel like Mail, not a sum).
// Section B2 — Absolute channel visits (B:N → 13 cols, 10 channels + domain)
// Sheet column order in B2 (and B1): direct, search_organic, search_paid,
// referrals, display_ads, social_organic, social_paid, gen_ai, email, affiliates.
// We collapse search_organic+social_organic → "organic", search_paid+display_ads+social_paid → "paid",
// referrals → "referral", gen_ai → "gen_ai" (folded into organic), email+affiliates → "social/other".
// Simpler: keep the 5-bucket model but read from the new positions.
function mapSectionB2(rows) {
  return rows
    .filter(r => isDataRow(r, 1))
    .map(r => {
      const direct        = num(r[1]);
      const searchOrganic = num(r[2]);
      const searchPaid    = num(r[3]);
      const referrals     = num(r[4]);
      const displayAds    = num(r[5]);
      const socialOrganic = num(r[6]);
      const socialPaid    = num(r[7]);
      const genAi         = num(r[8]);
      const email         = num(r[9]);
      const affiliates    = num(r[10]);

      const organic  = (searchOrganic || 0) + (socialOrganic || 0) + (genAi || 0);
      const paid     = (searchPaid || 0) + (socialPaid || 0) + (displayAds || 0);
      const referral = (referrals || 0) + (affiliates || 0);
      const social   = email || 0;
      const total    = (organic || 0) + (paid || 0) + (direct || 0) + (referral || 0) + (social || 0);
      return {
        domain: str(r[0]),
        organic: organic || null,
        paid: paid || null,
        direct,
        referral: referral || null,
        social: social || null,
        total,
      };
    });
}

// Section B3 — Transversal channel share (rows 36-40, B:G → 6 cols)
// B domain, C organic, D paid, E direct, F referral, G social, H total
function mapSectionB(rows) {
  return rows
    .filter(r => isDataRow(r, 1))
    .map(r => ({
      domain: str(r[0]),
      organic:  pct(r[1]),
      paid:     pct(r[2]),
      direct:   pct(r[3]),
      referral: pct(r[4]),
      social:   pct(r[5]),
      total:    pct(r[6]),
    }));
}

// Section C — Branded search (rows 44-48, B:G → 6 cols)
// B brand, C branded visits, D market share branded, E branded % of own,
// F non-branded %, G non-branded visits
function mapSectionC(rows) {
  return rows
    .filter(r => isDataRow(r, 1))
    .map(r => ({
      domain:           str(r[0]),
      brandedVisits:    num(r[1]),  // C
      brandedShareOwn:  pct(r[2]),  // D — branded % of own
      nonBrandedShare:  pct(r[3]),  // E — non-branded % of own
      nonBrandedVisits: num(r[4]),  // F — non-branded visits (raw count)
      brandedMarket:    pct(r[5]),  // G — non-branded share of market
    }));
}

// Section D — Organic SEMrush (rows 53-57, B:I → 8 cols)
// B domain, C avg position, D # kws top-3, E # kws top-10,
// F win rate, G top-3 share, H unique kws, I est. organic traffic
function mapSectionD(rows) {
  return rows
    .filter(r => isDataRow(r, 1))
    .map(r => ({
      domain:        str(r[0]),
      avgPosition:   num(r[1]),
      kwsTop3:       num(r[2]),
      kwsTop10:      num(r[3]),
      winRate:       pct(r[4]),
      top3Share:     pct(r[5]),
      uniqueKws:     num(r[6]),
      estTraffic:    num(r[7]),
    }));
}

// Section E — Paid SOV (rows 61-64, B:F → 5 cols)
// B competitor, C imp share, D top of page rate, E outranking share, F overlap rate
// Section E — Paid SOV (rows 61-65, B:G → 6 cols)
// B competitor, C imp share, D top of page rate, E outranking share,
// F overlap rate (with us in our auctions), G CarBrain brand overlap rate
// (their imp share when searches are for CarBrand branded KWs)
function mapSectionE(rows) {
  return rows
    .filter(r => isDataRow(r, 1))
    .map(r => ({
      domain:           str(r[0]),
      impShare:         pct(r[1]),
      topOfPageRate:    pct(r[2]),
      outrankingShare:  pct(r[3]),
      overlapRate:      pct(r[4]),
      brandOverlapRate: pct(r[5]),
    }));
}

// Section F — Estimated lead share (B:H → 7 cols)
// B domain, C traffic, D CVR applied, E est. leads/mo,
// F lead share %, G est. PR, H est. purchased units
function mapSectionF(rows) {
  return rows
    .filter(r => isDataRow(r, 1))
    .map(r => ({
      domain:        str(r[0]),
      traffic:       num(r[1]),
      cvr:           pct(r[2]),
      estLeads:      num(r[3]),
      leadShare:     pct(r[4]),
      estPR:         pct(r[5]),
      estPurchased:  num(r[6]),
    }));
}

// Master fetch — pulls everything in parallel
async function fetchAll() {
  const [c4, secA, secB2, secB, secC, secD, secE, secF] = await Promise.all([
    fetchRange('C4:C4'),
    fetchRange('B9:N14'),
    fetchRange('B27:N32'),
    fetchRange('B36:H41'),
    fetchRange('B45:G50'),
    fetchRange('B54:I59'),
    fetchRange('B62:G67'),
    fetchRange('B71:H76'),
  ]);

  const result = {
    analysisMonth: c4[0] && c4[0][0] != null ? str(c4[0][0]) : 'Unknown',
    sectionA: mapSectionA(secA),
    sectionB2: mapSectionB2(secB2),
    sectionB: mapSectionB(secB),
    sectionC: mapSectionC(secC),
    sectionD: mapSectionD(secD),
    sectionE: mapSectionE(secE),
    sectionF: mapSectionF(secF),
    fetchedAt: new Date().toISOString(),
  };
  // Expose raw + parsed for debugging in the browser console
  if (typeof window !== 'undefined') {
    window.__sheetDebug = { raw: { secA, secB2, secB, secC, secD, secE, secF }, parsed: result };
    console.log('[sheet-data] raw secB2 rows:', secB2);
    console.log('[sheet-data] parsed sectionB2:', result.sectionB2);
  }
  return result;
}

// Cache helpers
function loadCache() {
  try {
    const raw = localStorage.getItem(CACHE_KEY);
    const ts  = localStorage.getItem(CACHE_TS_KEY);
    if (!raw) return null;
    return { data: JSON.parse(raw), ts: ts || null };
  } catch (e) {
    return null;
  }
}
function saveCache(data) {
  try {
    localStorage.setItem(CACHE_KEY, JSON.stringify(data));
    localStorage.setItem(CACHE_TS_KEY, new Date().toISOString());
  } catch (e) { /* quota — ignore */ }
}

// ─── Domain → display config (color, label, isUs) ─────────
// Order in your sheet drives chart order. We auto-color-assign.
const DOMAIN_CONFIG = {
  // Map common variants → display name + color. CarBrain is "us".
  'carbrain':           { name: 'CarBrain',        color: '#002147', isUs: true  },
  'carbrain.com':       { name: 'CarBrain',        color: '#002147', isUs: true  },
  'peddle':             { name: 'Peddle',          color: '#E07A5F', isUs: false },
  'peddle.com':         { name: 'Peddle',          color: '#E07A5F', isUs: false },
  'wheelzy':            { name: 'Wheelzy',         color: '#7B5EA7', isUs: false },
  'wheelzy.com':        { name: 'Wheelzy',         color: '#7B5EA7', isUs: false },
  'cashforcars':        { name: 'CashForCars',     color: '#3D8B7A', isUs: false },
  'cashforcars.com':    { name: 'CashForCars',     color: '#3D8B7A', isUs: false },
  'clunkerjunker':         { name: 'ClunkerJunker',   color: '#C5A572', isUs: false },
  'clunkerjunker.com':     { name: 'ClunkerJunker',   color: '#C5A572', isUs: false },
  'theclunkerjunker':      { name: 'ClunkerJunker',   color: '#C5A572', isUs: false },
  'theclunkerjunker.com':  { name: 'ClunkerJunker',   color: '#C5A572', isUs: false },
  'junkcarmedics':      { name: 'Junk Car Medics', color: '#3D8B7A', isUs: false },
  'junkcarmedics.com':  { name: 'Junk Car Medics', color: '#3D8B7A', isUs: false },
  'carbuyerusa':        { name: 'CarBuyerUSA',     color: '#C5A572', isUs: false },
  'carbuyerusa.com':    { name: 'CarBuyerUSA',     color: '#C5A572', isUs: false },
};
const FALLBACK_PALETTE = ['#002147', '#E07A5F', '#7B5EA7', '#3D8B7A', '#C5A572', '#00BBEA', '#CBFF00'];

function configForDomain(domain, idx) {
  const key = String(domain).toLowerCase().trim().replace(/^https?:\/\/(www\.)?/, '');
  const exact = DOMAIN_CONFIG[key] || DOMAIN_CONFIG[key.split('.')[0]];
  if (exact) return exact;
  return {
    name: domain,
    color: FALLBACK_PALETTE[idx % FALLBACK_PALETTE.length],
    isUs: /carbrain/i.test(domain),
  };
}

window.MarketShareSheet = {
  fetchAll,
  loadCache,
  saveCache,
  configForDomain,
  SHEET_ID,
  SHEET_NAME,
};