// =========================================================
// Marketing Engine — Starter (Sheets-bound)
// Paste this whole file into Extensions → Apps Script.
// Set Script Property GEMINI_API_KEY to your AI Studio key.
// See SETUP.md for the 5-minute walkthrough.
// =========================================================

const MODEL = 'gemini-2.5-flash';
const DATA_SHEET = 'Data';

// ---------- Custom function -------------------------------

/**
 * AI() — custom Sheet function. Use it like:
 *   =ASK("Summarise this week vs last", A1:F61)
 *
 * @customfunction
 */
function ASK(instruction, content) {
  if (!instruction) return '';
  let body = '';
  if (Array.isArray(content)) {
    body = content.map(function (row) {
      return Array.isArray(row) ? row.join('\t') : String(row);
    }).join('\n');
  } else if (content != null) {
    body = String(content);
  }
  return callGemini_(instruction + '\n\n---\nData:\n' + body);
}

function callGemini_(prompt) {
  const apiKey = PropertiesService.getScriptProperties().getProperty('GEMINI_API_KEY');
  if (!apiKey) throw new Error('Set GEMINI_API_KEY in Project Settings → Script Properties.');

  const url = 'https://generativelanguage.googleapis.com/v1beta/models/' +
              MODEL + ':generateContent?key=' + apiKey;
  const response = UrlFetchApp.fetch(url, {
    method: 'post',
    contentType: 'application/json',
    payload: JSON.stringify({
      contents: [{ parts: [{ text: prompt }] }],
      generationConfig: { temperature: 0.6 }
    }),
    muteHttpExceptions: true
  });

  if (response.getResponseCode() !== 200) {
    return 'API ' + response.getResponseCode() + ': ' +
           response.getContentText().substring(0, 200);
  }
  const data = JSON.parse(response.getContentText());
  return (data.candidates && data.candidates[0].content.parts[0].text) || '';
}

// ---------- Menu ------------------------------------------

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Marketing engine')
    .addItem('Build basic dashboard', 'buildBasicDashboard')
    .addItem('Generate weekly summary', 'generateWeeklySummary')
    .addToUi();
}

// ---------- Dashboard -------------------------------------

/**
 * Aggregates sessions per week from the Data tab and renders a single
 * column chart on a fresh Dashboard tab.
 */
function buildBasicDashboard() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const data = ss.getSheetByName(DATA_SHEET);
  if (!data) {
    SpreadsheetApp.getUi().alert(
      'No "' + DATA_SHEET + '" tab. Import marketing_metrics.csv first ' +
      '(File → Import → Upload).'
    );
    return;
  }

  // Replace any existing Dashboard tab.
  const old = ss.getSheetByName('Dashboard');
  if (old) ss.deleteSheet(old);
  const dash = ss.insertSheet('Dashboard');

  dash.getRange('A1').setValue('Marketing dashboard')
    .setFontSize(18).setFontWeight('bold');
  dash.getRange('A2').setValue('Auto-generated ' + new Date().toLocaleString())
    .setFontStyle('italic').setFontColor('#888');

  // Aggregate Sessions by Week.
  const values = data.getDataRange().getValues();
  const totals = {};
  for (let i = 1; i < values.length; i++) {
    const week = values[i][0];
    const sessions = Number(values[i][2]) || 0;
    if (!week) continue;
    const key = (week instanceof Date)
      ? Utilities.formatDate(week, 'GMT', 'yyyy-MM-dd')
      : String(week);
    totals[key] = (totals[key] || 0) + sessions;
  }
  const weeks = Object.keys(totals).sort();
  const tableRows = weeks.map(function (w) { return [w, totals[w]]; });

  dash.getRange('A4').setValue('Week').setFontWeight('bold');
  dash.getRange('B4').setValue('Total sessions').setFontWeight('bold');
  if (tableRows.length) {
    dash.getRange(5, 1, tableRows.length, 2).setValues(tableRows);
  }
  dash.setColumnWidth(1, 120);
  dash.setColumnWidth(2, 140);

  // Chart.
  const chartRange = dash.getRange(4, 1, tableRows.length + 1, 2);
  const chart = dash.newChart()
    .setChartType(Charts.ChartType.COLUMN)
    .addRange(chartRange)
    .setPosition(4, 4, 0, 0)
    .setOption('title', 'Total sessions per week')
    .setOption('width', 640)
    .setOption('height', 360)
    .setOption('legend', { position: 'none' })
    .build();
  dash.insertChart(chart);

  ss.setActiveSheet(dash);
}

// ---------- AI summary -----------------------------------

/**
 * Reads the whole Data tab, asks Gemini to write a 4-6 bullet weekly summary,
 * and drops it on a Summary tab.
 */
function generateWeeklySummary() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const data = ss.getSheetByName(DATA_SHEET);
  if (!data) {
    SpreadsheetApp.getUi().alert('No "' + DATA_SHEET + '" tab.');
    return;
  }

  // Format the data as a tab-separated table the model can read.
  const values = data.getDataRange().getValues();
  const tableText = values.map(function (row) {
    return row.map(function (c) {
      return (c instanceof Date) ? Utilities.formatDate(c, 'GMT', 'yyyy-MM-dd') : c;
    }).join('\t');
  }).join('\n');

  const prompt =
    'You are a marketing analyst writing a weekly update for a B2B SaaS team.\n\n' +
    'Weekly data, tab-separated, one row per week per channel:\n' +
    tableText + '\n\n' +
    'Write 4-6 bullets covering:\n' +
    '- Headline: latest week vs prior week (totals across channels). Specific numbers.\n' +
    '- Which channel(s) drove the change.\n' +
    '- Anything unusual across the 12 weeks (a week or channel that stands out).\n' +
    '- One thing to investigate next week.\n\n' +
    'Plain language. No jargon. No "leverage", "synergy", "unlock". Be concrete with numbers.';

  const summary = callGemini_(prompt);

  let sheet = ss.getSheetByName('Summary');
  if (!sheet) sheet = ss.insertSheet('Summary');
  sheet.clear();
  sheet.setColumnWidth(1, 800);
  sheet.getRange('A1').setValue('Weekly summary')
    .setFontSize(16).setFontWeight('bold');
  sheet.getRange('A2').setValue('Generated ' + new Date().toLocaleString())
    .setFontStyle('italic').setFontColor('#888');
  sheet.getRange('A4').setValue(summary).setWrap(true).setVerticalAlignment('top');
  ss.setActiveSheet(sheet);
}