/**
 * FetchSERP → Google Sheets tracker for AI Overviews (AIO) and AI Mode
 * with rank-like signals and brand presence pivot.
 *
 * Tabs expected/created:
 *  - Keywords: column A header "keyword", then one query per row.
 *  - Brands: column A header "domain" (your brand domains, no www).
 *  - AIO_Results: appended per run (presence + top domains).
 *  - AI_Mode_Results: appended per run (presence + top domains).
 *  - AI_Sources: appended per run (1 row per citation with rank and metadata).
 *  - GEO_AISummary: summary counts + chart (AIO vs AI Mode triggers).
 *  - GEO_BrandPresence: pivot for latest run + chart of rank-1 shares.
 */

const FETCHSERP_BASE = 'https://www.fetchserp.com/api/v1';
const DEFAULT_COUNTRY = 'us';

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('GEO (FetchSERP)')
    .addItem('Fetch AIO & AI Mode', 'runAIOTracking')
    .addToUi();
}

function runAIOTracking() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const token = getApiToken_();
  const keywords = getKeywords_(ss);
  ensureBrandsSheet_(ss); // make sure Brands exists (empty is fine)

  const aioSheet = ensureSheet_(ss, 'AIO_Results', [
    'timestamp', 'keyword', 'country',
    'has_ai_overview', 'source_count', 'top_source_domain', 'all_sources'
  ]);

  const aimodeSheet = ensureSheet_(ss, 'AI_Mode_Results', [
    'timestamp', 'keyword', 'country',
    'has_ai_mode', 'source_count', 'top_source_domain', 'all_sources'
  ]);

  const srcSheet = ensureSheet_(ss, 'AI_Sources', [
    'timestamp', 'keyword', 'country', 'surface', // AIO or AI_MODE
    'rank', 'url', 'domain', 'title', 'site_name'
  ]);

  const now = new Date();
  const aioRows = [];
  const aimodeRows = [];
  const sourceRows = [];

  for (const kw of keywords) {
    const country = DEFAULT_COUNTRY;

    // Primary combined endpoint
    const aiData = callFetchSerp_('serp_ai', { query: kw, country }, token);

    // Optional AI Mode accelerator (US-only, cached)
    let aiModeData = null;
    try {
      aiModeData = callFetchSerp_('serp_ai_mode', { query: kw }, token);
    } catch (e) {
      // OK to ignore; not always necessary
    }

    // ---- AI OVERVIEW ----
    const aioBlock = getBlock_(aiData, 'ai_overview');
    const aioSources = normalizeSources_(aioBlock && aioBlock.sources);
    const aioTop = aioSources.length ? aioSources[0] : null;
    aioRows.push([
      now, kw, country,
      !!aioBlock,
      aioSources.length,
      aioTop ? domainOnly_(aioTop.url || aioTop.site_name || '') : '',
      aioSources.map(s => domainOnly_(s.url || s.site_name || '')).join(' | ')
    ]);

    // push detailed sources with rank
    aioSources.forEach((s, i) => {
      sourceRows.push([
        now, kw, country, 'AIO',
        i + 1,
        s.url || '',
        domainOnly_(s.url || s.site_name || ''),
        s.title || '',
        s.site_name || ''
      ]);
    });

    // ---- AI MODE ----
    const aiModeBlock = extractAiMode_(aiModeData) || extractAiMode_(aiData);
    const aimSources = normalizeSources_(aiModeBlock && aiModeBlock.sources);
    const aimTop = aimSources.length ? aimSources[0] : null;
    aimodeRows.push([
      now, kw, country,
      !!aiModeBlock,
      aimSources.length,
      aimTop ? domainOnly_(aimTop.url || aimTop.site_name || '') : '',
      aimSources.map(s => domainOnly_(s.url || s.site_name || '')).join(' | ')
    ]);

    aimSources.forEach((s, i) => {
      sourceRows.push([
        now, kw, country, 'AI_MODE',
        i + 1,
        s.url || '',
        domainOnly_(s.url || s.site_name || ''),
        s.title || '',
        s.site_name || ''
      ]);
    });

    Utilities.sleep(400); // rate-friendly
  }

  if (aioRows.length) appendRows_(aioSheet, aioRows);
  if (aimodeRows.length) appendRows_(aimodeSheet, aimodeRows);
  if (sourceRows.length) appendRows_(srcSheet, sourceRows);

  buildSummaryAndChart_();
  buildBrandPresencePivotAndChart_(); // NEW
}

/* ----------------------- Helpers & Builders ----------------------- */

function getApiToken_() {
  const props = PropertiesService.getScriptProperties();
  const token = props.getProperty('FETCHSERP_API_TOKEN') || '';
  if (!token) {
    throw new Error('Missing FETCHSERP_API_TOKEN in Script properties. Set it in Project Settings.');
  }
  return token;
}

function getKeywords_(ss) {
  const sh = ss.getSheetByName('Keywords');
  if (!sh) throw new Error('Missing "Keywords" sheet with header "keyword" in A1.');
  const values = sh.getRange(2, 1, Math.max(0, sh.getLastRow() - 1), 1)
    .getValues().flat().map(String).map(s => s.trim()).filter(Boolean);
  if (!values.length) throw new Error('No keywords found under header "keyword" (A2:A).');
  return values;
}

function ensureSheet_(ss, name, headers) {
  let sh = ss.getSheetByName(name);
  if (!sh) sh = ss.insertSheet(name);
  if (sh.getLastRow() === 0) {
    sh.getRange(1, 1, 1, headers.length).setValues([headers]);
    sh.setFrozenRows(1);
  }
  return sh;
}

function ensureBrandsSheet_(ss) {
  let sh = ss.getSheetByName('Brands');
  if (!sh) {
    sh = ss.insertSheet('Brands');
    sh.getRange(1, 1).setValue('domain');
    sh.setFrozenRows(1);
  }
  return sh;
}

function callFetchSerp_(path, params, token) {
  const url = `${FETCHSERP_BASE}/${path}?` + Object.keys(params)
    .map(k => `${encodeURIComponent(k)}=${encodeURIComponent(params[k])}`).join('&');

  const res = UrlFetchApp.fetch(url, {
    method: 'get',
    headers: { 'accept': 'application/json', 'authorization': `Bearer ${token}` },
    muteHttpExceptions: true
  });

  const code = res.getResponseCode();
  const text = res.getContentText();
  if (code < 200 || code >= 300) throw new Error(`FetchSERP ${path} error ${code}: ${text}`);

  try { return JSON.parse(text); }
  catch (e) { throw new Error(`FetchSERP ${path} invalid JSON: ${text.slice(0, 300)}…`); }
}

function getBlock_(payload, key) {
  if (!payload) return null;
  const d = payload.data || payload;
  if (d.results && d.results[key]) return d.results[key];
  if (d[key]) return d[key];
  return null;
}

function extractAiMode_(payload) { // tolerate different shapes
  if (!payload) return null;
  const d = payload.data || payload;
  if (d.results && d.results.ai_mode) return d.results.ai_mode;
  if (d.ai_mode) return d.ai_mode;
  return null;
}

function normalizeSources_(sources) {
  if (!Array.isArray(sources)) return [];
  return sources
    .map(s => s || {})
    .map(s => ({
      url: s.url || '',
      title: s.title || '',
      site_name: s.site_name || ''
    }))
    .filter(s => s.url || s.site_name);
}

function domainOnly_(u) {
  try {
    const host = (new URL(u)).hostname || '';
    return host.replace(/^www\./i, '');
  } catch (e) {
    return (u || '').replace(/^www\./i, '');
  }
}

function appendRows_(sheet, rows) {
  sheet.getRange(sheet.getLastRow() + 1, 1, rows.length, rows[0].length).setValues(rows);
}

/** Create or refresh a historical summary tab with counts & share of voice, plus trend chart. */
/** Create or refresh a historical summary tab with counts, SOV, and 7-day rolling averages, plus a trend chart. */
function buildSummaryAndChart_() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sum = ensureSheet_(ss, 'GEO_AISummary', [
    'date',
    'AIO_count', 'AIO_share_of_voice',
    'AI_Mode_count', 'AI_Mode_share_of_voice',
    'keywords_tracked',
    'AIO_count_7dma', 'AI_Mode_count_7dma',
    'AIO_sov_7dma', 'AI_Mode_sov_7dma'
  ]);

  // Clear old rows (keep header)
  if (sum.getLastRow() > 1) {
    sum.getRange(2, 1, sum.getLastRow() - 1, 11).clearContent();
  }

  const aio = ss.getSheetByName('AIO_Results');
  const aim = ss.getSheetByName('AI_Mode_Results');
  const kwSheet = ss.getSheetByName('Keywords');
  if (!aio || !aim || !kwSheet) return;

  const keywordsTracked = Math.max(0, kwSheet.getLastRow() - 1);

  // Build daily counts maps
  const aioCounts = countByDate_(aio, 1, 4); // timestamp col 1, has_ai_overview col 4
  const aimCounts = countByDate_(aim, 1, 4); // timestamp col 1, has_ai_mode col 4

  const allDates = Array.from(new Set([...Object.keys(aioCounts), ...Object.keys(aimCounts)]))
    .sort((a, b) => new Date(a) - new Date(b));

  // Build rows with daily values first
  const rows = allDates.map(date => {
    const aCount = aioCounts[date] || 0;
    const mCount = aimCounts[date] || 0;
    const aSOV = keywordsTracked ? aCount / keywordsTracked : 0;
    const mSOV = keywordsTracked ? mCount / keywordsTracked : 0;
    return [
      date,
      aCount, aSOV,
      mCount, mSOV,
      keywordsTracked,
      null, null, // AIO_count_7dma, AI_Mode_count_7dma (fill after)
      null, null  // AIO_sov_7dma, AI_Mode_sov_7dma (fill after)
    ];
  });

  // Compute 7-day rolling averages (centered on the last 7 days ending at index i)
  const aCountSeries = rows.map(r => r[1]);
  const mCountSeries = rows.map(r => r[3]);
  const aSovSeries   = rows.map(r => r[2]);
  const mSovSeries   = rows.map(r => r[4]);

  const aCount7 = rollingMean_(aCountSeries, 7);
  const mCount7 = rollingMean_(mCountSeries, 7);
  const aSov7   = rollingMean_(aSovSeries,   7);
  const mSov7   = rollingMean_(mSovSeries,   7);

  // Fill the rolling columns
  for (let i = 0; i < rows.length; i++) {
    rows[i][6]  = aCount7[i]; // AIO_count_7dma
    rows[i][7]  = mCount7[i]; // AI_Mode_count_7dma
    rows[i][8]  = aSov7[i];   // AIO_sov_7dma
    rows[i][9]  = mSov7[i];   // AI_Mode_sov_7dma
  }

  if (rows.length) {
    sum.getRange(2, 1, rows.length, rows[0].length).setValues(rows);
  }

  // Rebuild chart: daily counts + smoothed SOV on dual axes
  const charts = sum.getCharts();
  charts.forEach(c => sum.removeChart(c));

  const dataHeight = rows.length + 1; // include header
  const chart = sum.newChart()
    .setChartType(Charts.ChartType.LINE)
    .addRange(sum.getRange(1, 1, dataHeight, 10)) // includes counts, SOV, and 7d SOV
    .setPosition(5, 1, 0, 0)
    .setOption('title', 'AIO & AI Mode — Daily Counts and 7-Day SOV Averages')
    .setOption('hAxis', { title: 'Date' })
    .setOption('vAxes', {
      0: { title: 'Keyword Count' },
      1: { title: 'Share of Voice (7-day avg)', format: 'percent' }
    })
    // Series mapping: 0=AIO_count, 1=AIO_SOV, 2=AI_Mode_count, 3=AI_Mode_SOV, 4=keywords_tracked,
    //                 5=AIO_count_7dma, 6=AI_Mode_count_7dma, 7=AIO_sov_7dma, 8=AI_Mode_sov_7dma
    // We'll show daily counts (0,2) on axis 0, hide raw daily SOV (1,3) to reduce noise,
    // show smoothed SOV (7,8) on axis 1, and hide keywords_tracked (4) + count_7dma (5,6) from display.
    .setOption('series', {
      0: { targetAxisIndex: 0 }, // AIO_count (line)
      1: { targetAxisIndex: 1, visibleInLegend: false, lineWidth: 0, pointsVisible: false }, // raw AIO SOV (hidden)
      2: { targetAxisIndex: 0 }, // AI_Mode_count (line)
      3: { targetAxisIndex: 1, visibleInLegend: false, lineWidth: 0, pointsVisible: false }, // raw AI Mode SOV (hidden)
      4: { visibleInLegend: false, lineWidth: 0, pointsVisible: false }, // keywords_tracked (hidden)
      5: { visibleInLegend: false, lineWidth: 0, pointsVisible: false }, // AIO_count_7dma (hidden to avoid clutter)
      6: { visibleInLegend: false, lineWidth: 0, pointsVisible: false }, // AI_Mode_count_7dma (hidden)
      7: { targetAxisIndex: 1 }, // AIO_sov_7dma (smooth)
      8: { targetAxisIndex: 1 }  // AI_Mode_sov_7dma (smooth)
    })
    .setOption('legend', { position: 'bottom' })
    .build();

  sum.insertChart(chart);
}

/** Simple trailing rolling mean with window W; returns array aligned to input length (nulls until window is filled). */
function rollingMean_(arr, W) {
  const out = new Array(arr.length).fill(null);
  let sum = 0;
  for (let i = 0; i < arr.length; i++) {
    sum += (typeof arr[i] === 'number' ? arr[i] : 0);
    if (i >= W) sum -= (typeof arr[i - W] === 'number' ? arr[i - W] : 0);
    if (i >= W - 1) out[i] = sum / W;
  }
  return out;
}


/** Helper: count how many rows have TRUE in booleanCol, grouped by date from dateCol. */
function countByDate_(sheet, dateCol, booleanCol) {
  const rows = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()).getValues();
  const counts = {};
  rows.forEach(row => {
    const ts = row[dateCol - 1];
    const has = row[booleanCol - 1];
    if (ts && (has === true || has === 'TRUE')) {
      const d = new Date(ts);
      const dateStr = Utilities.formatDate(d, Session.getScriptTimeZone(), 'yyyy-MM-dd');
      counts[dateStr] = (counts[dateStr] || 0) + 1;
    }
  });
  return counts;
}



function countTrue_(sheet, col) {
  if (!sheet || sheet.getLastRow() < 2) return 0;
  const vals = sheet.getRange(2, col, sheet.getLastRow() - 1, 1).getValues().flat();
  return vals.filter(v => v === true || v === 'TRUE').length;
}

/**
 * Build a pivot for BRAND presence (latest run only).
 * For each surface (AIO / AI_MODE), we compute:
 *  - queries_with_surface: number of keywords where that surface triggered
 *  - queries_brand_cited: number of those keywords where brand domain appears in any citation
 *  - presence_rate = brand_cited / queries_with_surface
 *  - queries_brand_rank1: number where brand is rank 1 citation
 *  - rank1_rate = brand_rank1 / queries_with_surface
 */
function buildBrandPresencePivotAndChart_() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const brands = readBrands_(ss); // array of domains (no www)

  const aioSheet = ss.getSheetByName('AIO_Results');
  const aimSheet = ss.getSheetByName('AI_Mode_Results');
  const srcSheet = ss.getSheetByName('AI_Sources');

  if (!brands.length || !srcSheet || srcSheet.getLastRow() < 2) {
    ensureSheet_(ss, 'GEO_BrandPresence', ['brand_domain', 'surface', 'queries_with_surface', 'queries_brand_cited', 'presence_rate', 'queries_brand_rank1', 'rank1_rate']);
    return;
  }

  // Determine latest run timestamp (max timestamp across results)
  const latest = latestTimestamp_([aioSheet, aimSheet, srcSheet].filter(Boolean));
  if (!latest) return;

  // Build per-surface sets of keywords that triggered surface in the latest run
  const latestAioKeywords = new Set(filterKeywordsByTimestampAndBool_(aioSheet, latest, 4)); // has_ai_overview
  const latestAimKeywords = new Set(filterKeywordsByTimestampAndBool_(aimSheet, latest, 4)); // has_ai_mode

  // Build maps for brand presence by keyword and rank1 by keyword (latest run only)
  const surfaceBrandAny = { AIO: new Map(), AI_MODE: new Map() }; // brand -> Set(keywords)
  const surfaceBrandRank1 = { AIO: new Map(), AI_MODE: new Map() }; // brand -> Set(keywords)

  // Iterate source rows for latest timestamp only
  const srcVals = srcSheet.getRange(2, 1, srcSheet.getLastRow() - 1, 9).getValues();
  for (const row of srcVals) {
    const [ts, kw, country, surface, rank, url, domain/*clean*/, title, site_name] = row;
    if (!sameDay_(ts, latest)) continue; // group by day/run timestamp granularity

    const dom = String(domain || '').toLowerCase();
    if (!dom) continue;

    // For presence calculations, only consider keywords that triggered that surface
    if (surface === 'AIO' && !latestAioKeywords.has(kw)) continue;
    if (surface === 'AI_MODE' && !latestAimKeywords.has(kw)) continue;

    // For each brand, check match
    for (const b of brands) {
      if (dom.endsWith(b)) {
        // any-cited
        if (!surfaceBrandAny[surface].has(b)) surfaceBrandAny[surface].set(b, new Set());
        surfaceBrandAny[surface].get(b).add(kw);

        // rank1
        if (rank === 1) {
          if (!surfaceBrandRank1[surface].has(b)) surfaceBrandRank1[surface].set(b, new Set());
          surfaceBrandRank1[surface].get(b).add(kw);
        }
      }
    }
  }

  // Prepare output rows
  const out = [];
  const surfaces = ['AIO', 'AI_MODE'];
  for (const s of surfaces) {
    const queriesWithSurface = (s === 'AIO') ? latestAioKeywords.size : latestAimKeywords.size;
    for (const b of brands) {
      const cited = surfaceBrandAny[s].get(b)?.size || 0;
      const r1 = surfaceBrandRank1[s].get(b)?.size || 0;
      const presenceRate = queriesWithSurface ? (cited / queriesWithSurface) : 0;
      const rank1Rate = queriesWithSurface ? (r1 / queriesWithSurface) : 0;
      out.push([
        b, s, queriesWithSurface, cited, presenceRate, r1, rank1Rate
      ]);
    }
  }

  const pivot = ensureSheet_(ss, 'GEO_BrandPresence', ['brand_domain', 'surface', 'queries_with_surface', 'queries_brand_cited', 'presence_rate', 'queries_brand_rank1', 'rank1_rate']);
  // Clear old data
  if (pivot.getLastRow() > 1) pivot.getRange(2, 1, pivot.getLastRow() - 1, 7).clearContent();
  if (out.length) pivot.getRange(2, 1, out.length, 7).setValues(out);

  // Build/refresh a chart for Rank-1 share per brand per surface
  const charts = pivot.getCharts();
  charts.forEach(c => pivot.removeChart(c));

  // Simple approach: chart all rows, data has both surfaces; users can filter in Sheets UI.
  const chart = pivot.newChart()
    .setChartType(Charts.ChartType.COLUMN)
    .addRange(pivot.getRange(1, 1, Math.max(2, pivot.getLastRow()), 7))
    .setPosition(5, 1, 0, 0)
    .setOption('title', 'Brand Rank-1 Share (latest run)')
    .setOption('series', {
      0: { targetAxisIndex: 0 }, // presence metrics
      1: { targetAxisIndex: 0 },
      2: { targetAxisIndex: 1 }  // rate on secondary axis if desired
    })
    .setOption('legend', { position: 'right' })
    .build();
  pivot.insertChart(chart);
}

/* ---------- Pivot helpers ---------- */

function readBrands_(ss) {
  const sh = ss.getSheetByName('Brands');
  if (!sh || sh.getLastRow() < 2) return [];
  const vals = sh.getRange(2, 1, sh.getLastRow() - 1, 1).getValues().flat()
    .map(String).map(v => v.trim().toLowerCase().replace(/^www\./, ''))
    .filter(Boolean);
  return Array.from(new Set(vals));
}

function latestTimestamp_(sheets) {
  let latest = null;
  for (const sh of sheets) {
    if (!sh || sh.getLastRow() < 2) continue;
    const tsCol = 1; // first col in our schemas
    const vals = sh.getRange(2, tsCol, sh.getLastRow() - 1, 1).getValues().flat();
    for (const v of vals) {
      const d = (v instanceof Date) ? v : new Date(v);
      if (!isNaN(+d)) {
        if (!latest || d > latest) latest = d;
      }
    }
  }
  return latest;
}

function sameDay_(a, b) {
  if (!(a instanceof Date)) a = new Date(a);
  if (!(b instanceof Date)) b = new Date(b);
  return a.getFullYear() === b.getFullYear() &&
         a.getMonth() === b.getMonth() &&
         a.getDate() === b.getDate();
}

function filterKeywordsByTimestampAndBool_(sheet, latestTs, boolColIndex) {
  if (!sheet || sheet.getLastRow() < 2) return [];
  const rows = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()).getValues();
  const out = [];
  for (const r of rows) {
    const ts = r[0];
    const kw = r[1];
    const val = r[boolColIndex - 1];
    if (sameDay_(ts, latestTs) && (val === true || val === 'TRUE')) out.push(kw);
  }
  return out;
}