var configSpreadsheetUrl = '';

var competitorsToTrack = [];

var ENABLE_ALERTS = true;

var ALERT_THRESHOLD = 5;

var ALERT_METRIC = 'Overlap rate';

var ALERT_DATE_PERIOD = 'LAST_AVAILABLE_DAY';

var ALERT_COMPARISON_PERIOD = 'PREVIOUS_DAY';

//------------------------------------------------------------------------------
// This script visualizes all auction insights data and sends alerts on significant changes.
// Works with both Search and Shopping campaigns.
// © Adnine
//------------------------------------------------------------------------------

function main() {
  Logger.log('Beep boop scrippity script is starting');
  
  // ============================================================================
  // CONFIGURATION
  // ============================================================================
  
  // Master spreadsheet URL
  var configSpreadsheetUrl = '';
  
  // COMPETITOR FILTER (optional)
  // Leave empty array [] to track ALL competitors
  // Or specify competitors: ['domain1.com', 'domain2.com']
  var competitorsToTrack = []; 
  
  // ============================================================================
  // ALERT CONFIGURATION
  // ============================================================================
  
  // 1. Alerting Toggle
  var ENABLE_ALERTS = true;
  
  // 2. Email Recipients
  var ALERT_EMAILS = ['']; 
  
  // 3. Threshold (Percentage change). e.g. 5 means +/- 5% change triggers alert
  var ALERT_THRESHOLD = 5; 
  
  // 4. Metric to monitor (Must match column header exactly)
  // Options: 'Impression share', 'Overlap rate', 'Position above rate', 'Top of page rate', 'Abs. Top of page rate', 'Outranking share'
  var ALERT_METRIC = 'Overlap rate';
  
  // 5. Date Period
  // Options: 'LAST_AVAILABLE_DAY', 'LAST_7_DAYS'
  var ALERT_DATE_PERIOD = 'LAST_AVAILABLE_DAY';
  
  // 6. Comparison Period
  // If ALERT_DATE_PERIOD is 'LAST_AVAILABLE_DAY', options: 'PREVIOUS_DAY', 'SAME_DAY_LAST_WEEK', 'AVG_LAST_7_DAYS'
  // If ALERT_DATE_PERIOD is 'LAST_7_DAYS', options: 'PREVIOUS_7_DAYS'
  var ALERT_COMPARISON_PERIOD = 'PREVIOUS_DAY';
  
  // ============================================================================
  
  Logger.log('Opening config spreadsheet...');
  
  // Read the actual report URL from the config spreadsheet
  var configSpreadsheet = SpreadsheetApp.openByUrl(configSpreadsheetUrl);
  
  Logger.log('Config spreadsheet opened successfully');
  
  var configSheet = configSpreadsheet.getSheetByName('CONFIG');
  
  // If CONFIG sheet doesn't exist, rename the first sheet to CONFIG
  if (!configSheet) {
    Logger.log('CONFIG sheet not found, renaming first sheet...');
    configSheet = configSpreadsheet.getSheets()[0];
    configSheet.setName('CONFIG');
  }
  
  var spreadsheetUrl = configSheet.getRange('A1').getValue();
  if (!spreadsheetUrl) {
    Logger.log('ERROR: No spreadsheet URL found in config spreadsheet cell A1');
    return;
  }

  // Open the source report
  var sourceSpreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
  var sourceSheet = sourceSpreadsheet.getSheets()[0];
  
  var headerInfo = parseHeaderRow(sourceSheet);
  if (!headerInfo) {
    Logger.log('ERROR: Could not parse header row from source spreadsheet');
    return;
  }
  
  var sourceData = readSourceData(sourceSheet, headerInfo);
  Logger.log('Read ' + sourceData.length + ' rows from source report');
  
  // Filter data to only include specified competitors (if configured)
  if (competitorsToTrack.length > 0) {
    sourceData = filterCompetitors(sourceData, competitorsToTrack);
  }
  
  // Write to HISTORICAL_DATA
  accumulateHistoricalData(configSpreadsheet, sourceData, headerInfo);
  Logger.log('Historical data updated');
  
  // Read directly from HISTORICAL_DATA sheet and create metric tabs
  generateChartsFromHistoricalDataSheet(configSpreadsheet, headerInfo.metrics, competitorsToTrack);
  Logger.log('Charts created successfully');
  
  // ============================================================================
  // ALERT LOGIC
  // ============================================================================
  if (ENABLE_ALERTS) {
    Logger.log('Checking for alerts...');
    checkAndSendAlerts(configSpreadsheet, {
      emails: ALERT_EMAILS,
      metric: ALERT_METRIC,
      threshold: ALERT_THRESHOLD,
      datePeriod: ALERT_DATE_PERIOD,
      comparisonPeriod: ALERT_COMPARISON_PERIOD
    });
  }
}

function parseHeaderRow(sheet) {
  var headerRowIndex = 3;
  var rowData = sheet.getRange(headerRowIndex, 1, 1, sheet.getLastColumn()).getValues()[0];
  
  var metrics = [];
  var dateColumn = -1;
  var displayUrlColumn = -1;
  
  var metricNames = [
    'Impression share',
    'Overlap rate',
    'Position above rate',
    'Top of page rate',
    'Abs. Top of page rate',
    'Outranking share'
  ];
  
  for (var i = 0; i < rowData.length; i++) {
    var cellValue = String(rowData[i]).toLowerCase().trim();
    
    // Check for day/date column
    if (cellValue === 'day' || cellValue === 'date') {
      dateColumn = i;
    }
    
    // Check for Display URL domain column (Search) or Store display name (Shopping)
    if (cellValue === 'display url domain' || cellValue === 'store display name') {
      displayUrlColumn = i;
    }
    
    // Check for metrics
    for (var j = 0; j < metricNames.length; j++) {
      if (cellValue === metricNames[j].toLowerCase()) {
        metrics.push({
          name: metricNames[j],
          columnIndex: i
        });
        break;
      }
    }
  }
  
  if (dateColumn === -1 || displayUrlColumn === -1 || metrics.length === 0) {
    return null;
  }
  
  return {
    headerRowIndex: headerRowIndex,
    dateColumn: dateColumn,
    displayUrlColumn: displayUrlColumn,
    metrics: metrics
  };
}

function readSourceData(sheet, headerInfo) {
  // Data starts at row 4 (row 3 is header)
  var dataStartRow = 4;
  var lastRow = sheet.getLastRow();
  
  if (lastRow < dataStartRow) {
    return [];
  }
  
  var rawData = sheet.getRange(dataStartRow, 1, lastRow - dataStartRow + 1, sheet.getLastColumn()).getValues();
  var processedData = [];
  
  for (var i = 0; i < rawData.length; i++) {
    var row = rawData[i];
    var dateValue = row[headerInfo.dateColumn];
    var displayUrl = String(row[headerInfo.displayUrlColumn]).trim();
    
    // Skip empty rows
    if (!dateValue || !displayUrl) {
      continue;
    }
    
    var dataRow = {
      date: parseDateValue(dateValue),
      displayUrl: displayUrl,
      metrics: {}
    };
    
    // Read all metric values
    for (var j = 0; j < headerInfo.metrics.length; j++) {
      var metric = headerInfo.metrics[j];
      var rawValue = row[metric.columnIndex];
      var value;
      
      if (typeof rawValue === 'string') {
        var trimmedValue = rawValue.trim();
        
        // Explicit handling for Auction Insights specific formats
        if (trimmedValue === '< 10%' || trimmedValue === '<10%') {
          value = 0.099;
        } 
        // Handle dashes or empty strings as 0
        else if (trimmedValue === '-' || trimmedValue === '' || trimmedValue === '--') {
          value = 0;
        } 
        else {
          value = parseFloat(rawValue) || 0;
        }
      } else {
        value = parseFloat(rawValue) || 0;
      }
      
      dataRow.metrics[metric.name] = value;
    }
    
    processedData.push(dataRow);
  }
  
  return processedData;
}

function parseDateValue(dateValue) {
  // Use UTC methods
  if (dateValue instanceof Date) {
    var year = dateValue.getUTCFullYear();
    var month = dateValue.getUTCMonth();
    var day = dateValue.getUTCDate();
    return new Date(Date.UTC(year, month, day));
  }
  
  var dateString = String(dateValue).trim();
  var parts = dateString.split('-');
  
  if (parts.length === 3) {
    var year = parseInt(parts[0], 10);
    var month = parseInt(parts[1], 10) - 1;
    var day = parseInt(parts[2], 10);
    return new Date(Date.UTC(year, month, day));
  }
  
  return new Date(dateString);
}

function filterCompetitors(data, competitorsToTrack) {
  // Always include "You" (case-insensitive)
  var allowedCompetitors = competitorsToTrack.map(function(c) { 
    return c.toLowerCase(); 
  });
  
  return data.filter(function(row) {
    var domain = row.displayUrl.toLowerCase();
    if (domain === 'you') {
      return true;
    }
    return allowedCompetitors.indexOf(domain) !== -1;
  });
}

function accumulateHistoricalData(configSpreadsheet, newData, headerInfo) {
  var dataSheet = configSpreadsheet.getSheetByName('HISTORICAL_DATA');
  
  if (!dataSheet) {
    dataSheet = configSpreadsheet.insertSheet('HISTORICAL_DATA', 0);
    
    // Create header row
    var headers = ['Date', 'Domain'];
    for (var i = 0; i < headerInfo.metrics.length; i++) {
      headers.push(headerInfo.metrics[i].name);
    }
    dataSheet.appendRow(headers);
  }
  
  // Read all existing data as display values to build a map
  var existingDataMap = {};
  var lastRow = dataSheet.getLastRow();
  
  if (lastRow > 1) {
    var existingData = dataSheet.getRange(2, 1, lastRow - 1, 2).getDisplayValues();
    
    for (var i = 0; i < existingData.length; i++) {
      var dateStr = existingData[i][0]; 
      var domainStr = existingData[i][1]; 
      var key = dateStr + '|' + domainStr;
      existingDataMap[key] = true;
    }
  }
  
  // Add new data (avoid duplicates)
  for (var i = 0; i < newData.length; i++) {
    var dataRow = newData[i];
    var dateStr = formatDate(dataRow.date);
    var domainStr = dataRow.displayUrl;
    var key = dateStr + '|' + domainStr;
    
    if (existingDataMap[key]) {
      continue;
    }
    
    var rowToAdd = [dateStr, domainStr];
    for (var j = 0; j < headerInfo.metrics.length; j++) {
      rowToAdd.push(dataRow.metrics[headerInfo.metrics[j].name] || 0);
    }
    
    dataSheet.appendRow(rowToAdd);
    existingDataMap[key] = true;
  }
}

function formatDate(date) {
  if (!(date instanceof Date)) {
    // If it's a string, check if it's already in YYYY-MM-DD format
    var dateStr = String(date).trim();
    if (dateStr.match(/^\d{4}-\d{2}-\d{2}$/)) {
      return dateStr;
    }
    date = new Date(dateStr);
  }
  
  var year = date.getUTCFullYear();
  var month = String(date.getUTCMonth() + 1).padStart(2, '0');
  var day = String(date.getUTCDate()).padStart(2, '0');
  
  return year + '-' + month + '-' + day;
}

function generateChartsFromHistoricalDataSheet(spreadsheet, metrics, competitorsToTrack) {
  var dataSheet = spreadsheet.getSheetByName('HISTORICAL_DATA');
  
  if (!dataSheet || dataSheet.getLastRow() <= 1) {
    return;
  }
  
  // Read ALL data from HISTORICAL_DATA as strings
  var allData = dataSheet.getRange(1, 1, dataSheet.getLastRow(), dataSheet.getLastColumn()).getDisplayValues();
  var headers = allData[0];
  
  var metricColumnMap = {};
  for (var i = 0; i < headers.length; i++) {
    metricColumnMap[headers[i]] = i;
  }
  
  var summarySheet = createSummaryTabPlaceholder(spreadsheet);
  var createdCharts = [];
  var tabPosition = 2;
  
  for (var i = 0; i < metrics.length; i++) {
    var metric = metrics[i];
    var chart = createMetricTabFromSheet(spreadsheet, allData, metricColumnMap, metric, tabPosition, competitorsToTrack);
    if (chart) {
      createdCharts.push({
        chart: chart,
        metricName: metric.name
      });
      tabPosition++;
    }
  }
  
  populateSummaryTab(spreadsheet, summarySheet, createdCharts);
}

function createMetricTabFromSheet(spreadsheet, allData, metricColumnMap, metric, tabPosition, competitorsToTrack) {
  var sheetName = metric.name.replace(/\s+/g, '_').toUpperCase().replace('ABS.', 'ABSOLUTE');
  var metricSheet = spreadsheet.getSheetByName(sheetName);
  
  if (metricSheet) {
    spreadsheet.deleteSheet(metricSheet);
  }
  metricSheet = spreadsheet.insertSheet(sheetName, tabPosition);
  
  var dateColumn = metricColumnMap['Date'];
  var domainColumn = metricColumnMap['Domain'];
  var metricColumn = metricColumnMap[metric.name];
  
  // Build dateMap using display values
  var dateMap = {};
  var displayUrls = new Set();
  
  // Skip header row
  for (var i = 1; i < allData.length; i++) {
    var row = allData[i];
    var dateStr = String(row[dateColumn]).trim();
    var displayUrl = String(row[domainColumn]).trim();
    var metricValue = parseFloat(row[metricColumn]) || 0;
    
    if (competitorsToTrack.length > 0) {
      var domainLower = displayUrl.toLowerCase();
      if (domainLower !== 'you') {
        var allowed = false;
        for (var j = 0; j < competitorsToTrack.length; j++) {
          if (competitorsToTrack[j].toLowerCase() === domainLower) {
            allowed = true;
            break;
          }
        }
        if (!allowed) continue;
      }
    }
    
    if (!dateMap[dateStr]) {
      dateMap[dateStr] = {};
    }
    
    dateMap[dateStr][displayUrl] = metricValue;
    displayUrls.add(displayUrl);
  }
  
  var displayUrlArray = Array.from(displayUrls).sort();
  
  displayUrlArray = displayUrlArray.filter(function(url) {
    var hasNonZero = false;
    for (var date in dateMap) {
      if (dateMap[date][url] && dateMap[date][url] !== 0) {
        hasNonZero = true;
        break;
      }
    }
    return hasNonZero;
  });
  
  if (displayUrlArray.length === 0) {
    return null;
  }
  
  var headerRow = ['Date'].concat(displayUrlArray.map(function(url) {
    return metric.name + ' ' + url;
  }));
  metricSheet.appendRow(headerRow);
  
  var sortedDates = Object.keys(dateMap).sort();
  
  for (var j = 0; j < sortedDates.length; j++) {
    var dateStr = sortedDates[j];
    var rowData = [dateStr];
    
    for (var k = 0; k < displayUrlArray.length; k++) {
      rowData.push(dateMap[dateStr][displayUrlArray[k]] || 0);
    }
    
    metricSheet.appendRow(rowData);
  }
  
  var chart = createChart(metricSheet, metric.name, sortedDates.length, displayUrlArray);
  if (chart) {
    metricSheet.insertChart(chart);
  }
  
  return chart;
}

function createSummaryTabPlaceholder(spreadsheet) {
  var summarySheet = spreadsheet.getSheetByName('SUMMARY');
  if (summarySheet) {
    spreadsheet.deleteSheet(summarySheet);
  }
  summarySheet = spreadsheet.insertSheet('SUMMARY', 1);
  
  summarySheet.getRange(1, 1).setValue('AUCTION INSIGHTS SUMMARY - ALL METRICS');
  summarySheet.getRange(1, 1).setFontSize(20).setFontWeight('bold');
  
  return summarySheet;
}

function populateSummaryTab(spreadsheet, summarySheet, createdCharts) {
  var chartHeight = 25;
  var verticalSpacing = 5;

  for (var i = 0; i < createdCharts.length; i++) {
    var chartData = createdCharts[i];
    
    var startRow = 3 + (i * (chartHeight + verticalSpacing));
    var startCol = 1;
    
    var originalSheetName = chartData.metricName.replace(/\s+/g, '_').toUpperCase().replace('ABS.', 'ABSOLUTE');
    var originalSheet = spreadsheet.getSheetByName(originalSheetName);
    
    if (originalSheet) {
      var summaryChart = createSummaryChart(originalSheet, chartData.metricName, startRow, startCol);
      if (summaryChart) {
        summarySheet.insertChart(summaryChart);
      }
    }
  }
}

function createSummaryChart(sourceSheet, metricName, startRow, startCol) {
  var dataRange = sourceSheet.getDataRange();
  var numRows = dataRange.getNumRows() - 1;
  var numCols = dataRange.getNumColumns();
  
  if (numRows <= 0 || numCols <= 1) {
    return null;
  }

  var chart = sourceSheet.newChart()
    .setChartType(Charts.ChartType.LINE)
    .setOption('title', metricName)
    .setOption('titleTextStyle', {fontSize: 14, bold: true})
    .setOption('hAxis.title', 'Date')
    .setOption('legend.position', 'bottom')
    .setOption('width', 1200)
    .setOption('height', 600)
    .setPosition(startRow, startCol, 0, 0);

  var dateRange = sourceSheet.getRange(2, 1, numRows);
  chart.addRange(dateRange);

  for (var col = 2; col <= numCols; col++) {
    var seriesRange = sourceSheet.getRange(2, col, numRows);
    var displayUrlName = sourceSheet.getRange(1, col).getValue().replace(metricName + ' ', '');
    
    chart.addRange(seriesRange);
    chart.setOption('series.' + (col - 2) + '.labelInLegend', displayUrlName);
  }

  return chart.build();
}

function createChart(sheet, metricName, rowCount, displayUrlArray) {
  var row = rowCount + 3;
  var chart = sheet.newChart()
    .setChartType(Charts.ChartType.LINE)
    .setOption('title', metricName)
    .setOption('hAxis.title', 'Date')
    .setOption('vAxis.title', metricName)
    .setOption('legend.position', 'bottom')
    .setOption('width', 600)
    .setOption('height', 400)
    .setPosition(row, 1, 0, 0);

  var dateRange = sheet.getRange(2, 1, rowCount);
  chart.addRange(dateRange);

  for (var col = 2; col <= displayUrlArray.length + 1; col++) {
    var seriesRange = sheet.getRange(2, col, rowCount);
    var displayUrlName = sheet.getRange(1, col).getValue().replace(metricName + ' ', '');

    chart.addRange(seriesRange);
    chart.setOption('series.' + (col - 2) + '.labelInLegend', displayUrlName);
  }

  return chart.build();
}

// ============================================================================
// ALERTING FUNCTIONS
// ============================================================================

function checkAndSendAlerts(spreadsheet, config) {
  var dataSheet = spreadsheet.getSheetByName('HISTORICAL_DATA');
  if (!dataSheet || dataSheet.getLastRow() <= 1) {
    Logger.log('No data available for alerts.');
    return;
  }

  // 1. Fetch All Data
  var data = dataSheet.getDataRange().getDisplayValues();
  var headers = data[0];
  
  // Map headers to indices
  var colMap = {};
  for (var i = 0; i < headers.length; i++) colMap[headers[i]] = i;
  
  var metricIdx = colMap[config.metric];
  var dateIdx = colMap['Date'];
  var domainIdx = colMap['Domain'];
  
  if (metricIdx === undefined) {
    Logger.log('ERROR: Alert metric "' + config.metric + '" not found in headers.');
    return;
  }

  // 2. Parse Data into Object { Domain: { Date: Value } }
  // Track max date in the dataset
  var domainData = {};
  var allDates = [];
  
  for (var i = 1; i < data.length; i++) {
    var row = data[i];
    var dateStr = row[dateIdx];
    var domain = row[domainIdx];
    var val = parseFloat(row[metricIdx]) || 0;
    
    if (!domainData[domain]) domainData[domain] = {};
    domainData[domain][dateStr] = val;
    
    if (allDates.indexOf(dateStr) === -1) allDates.push(dateStr);
  }
  
  if (allDates.length === 0) return;
  allDates.sort(); // Ascending string sort (YYYY-MM-DD)
  
  var lastDateStr = allDates[allDates.length - 1];
  var lastDate = new Date(lastDateStr); 
  
  Logger.log('Latest date in data: ' + lastDateStr);
  
  // 3. Determine Comparison Dates
  var currentPeriodDates = [];
  var comparisonPeriodDates = [];
  
  var oneDayMs = 24 * 60 * 60 * 1000;
  
  if (config.datePeriod === 'LAST_AVAILABLE_DAY') {
    currentPeriodDates.push(lastDateStr);
    
    if (config.comparisonPeriod === 'PREVIOUS_DAY') {
      var prevDate = new Date(lastDate.getTime() - oneDayMs);
      comparisonPeriodDates.push(formatDate(prevDate));
    } else if (config.comparisonPeriod === 'SAME_DAY_LAST_WEEK') {
      var prevDate = new Date(lastDate.getTime() - (7 * oneDayMs));
      comparisonPeriodDates.push(formatDate(prevDate));
    } else if (config.comparisonPeriod === 'AVG_LAST_7_DAYS') {
      // Comparison against previous history (T-1 to T-7)
      for (var k = 1; k <= 7; k++) {
        var prevDate = new Date(lastDate.getTime() - (k * oneDayMs));
        comparisonPeriodDates.push(formatDate(prevDate));
      }
    }
  } else if (config.datePeriod === 'LAST_7_DAYS') {
    // Current period: T to T-6
    for (var k = 0; k < 7; k++) {
       var d = new Date(lastDate.getTime() - (k * oneDayMs));
       currentPeriodDates.push(formatDate(d));
    }
    
    if (config.comparisonPeriod === 'PREVIOUS_7_DAYS') {
      // Comparison: T-7 to T-13
      for (var k = 7; k < 14; k++) {
        var d = new Date(lastDate.getTime() - (k * oneDayMs));
        comparisonPeriodDates.push(formatDate(d));
      }
    }
  }
  
  // 4. Construct Readable Period Strings
  // Sort dates first to ensure ranges are readable (Start to End)
  currentPeriodDates.sort();
  comparisonPeriodDates.sort();
  
  var currentRangeStr = getReadableDateRange(config.datePeriod, currentPeriodDates);
  var pastRangeStr = getReadableDateRange(config.comparisonPeriod, comparisonPeriodDates);
  var fullPeriodStr = currentRangeStr + " vs " + pastRangeStr;
  
  // 5. Calculate Changes
  var alerts = [];
  
  for (var domain in domainData) {
    var currentAvg = calculateAverage(domainData[domain], currentPeriodDates);
    var pastAvg = calculateAverage(domainData[domain], comparisonPeriodDates);
    
    // If both results are purely 0 (e.g. domain inactive in both periods), skip.
    if (currentAvg === 0 && pastAvg === 0) continue;
    
    var absChange = currentAvg - pastAvg;
    var pctChange = 0;
    var status = '';
    
    if (pastAvg !== 0) {
      pctChange = (absChange / pastAvg) * 100;
      if (currentAvg === 0) status = 'LOST';
    } else if (currentAvg !== 0) {
      // Handle 0 to non-zero growth
      pctChange = 100;
      status = 'NEW';
    }
    
    var shouldAlert = false;
    
    // Trigger if threshold met
    if (Math.abs(pctChange) >= config.threshold) {
      shouldAlert = true;
      Logger.log('Alert Triggered [Threshold]: ' + domain + ' ' + pctChange.toFixed(2) + '%');
    }
    
    // Or trigger if Status indicates NEW/LOST (Always check this)
    if (status !== '') {
      shouldAlert = true;
      Logger.log('Alert Triggered [Status]: ' + domain + ' ' + status);
    }
    
    if (shouldAlert) {
      alerts.push({
        domain: domain,
        current: currentAvg,
        past: pastAvg,
        absChange: absChange,
        pctChange: pctChange,
        status: status
      });
    }
  }
  
  // 6. Log and Send
  if (alerts.length > 0) {
    Logger.log('Triggering ' + alerts.length + ' alerts.');
    
    // Log to Spreadsheet
    logAlertsToSheet(spreadsheet, config, alerts, fullPeriodStr);
    
    // Send Email
    sendAlertEmail(config, alerts, fullPeriodStr);
  } else {
    Logger.log('No alerts triggered. Max change was below ' + config.threshold + '% and no New/Lost found.');
  }
}

function getReadableDateRange(periodName, dates) {
  if (!dates || dates.length === 0) return periodName;
  if (dates.length === 1) {
    return periodName + ' (' + dates[0] + ')';
  }
  // Assuming dates are sorted
  return periodName + ' (' + dates[0] + ' to ' + dates[dates.length - 1] + ')';
}

function calculateAverage(dateValueMap, dates) {
  var sum = 0;
  var count = 0;
  
  for (var i = 0; i < dates.length; i++) {
    var date = dates[i];
    var val = dateValueMap[date];
    
    // If data exists for date, use it. 
    // If undefined (missing row), treat as 0.
    if (val !== undefined) {
      sum += val;
    } else {
      sum += 0; 
    }
    count++;
  }
  
  if (count === 0) return 0; 
  return sum / count; 
}

function logAlertsToSheet(spreadsheet, config, alerts, fullPeriodStr) {
  var sheet = spreadsheet.getSheetByName('ALERTS');
  if (!sheet) {
    sheet = spreadsheet.insertSheet('ALERTS', 1);
    // Status Column is Permanent
    sheet.appendRow(['Timestamp', 'Metric', 'Comparison Period', 'Domain', 'Status', 'Current Value', 'Previous Value', 'Abs Change', '% Change']);
    sheet.setFrozenRows(1);
    sheet.getRange(1, 1, 1, 9).setFontWeight('bold');
  }
  
  var timestamp = new Date();
  
  // Sort matches email sort (Absolute % Change Descending)
  alerts.sort(function(a, b) { return Math.abs(b.pctChange) - Math.abs(a.pctChange); });
  
  for (var i = 0; i < alerts.length; i++) {
    var alert = alerts[i];
    
    var statusText = alert.status || '';
    
    // Convert 0-1 scale to 0-100 and Fix to 2 decimals for numeric columns
    sheet.appendRow([
      timestamp, 
      config.metric, 
      fullPeriodStr, 
      alert.domain,
      statusText,
      parseFloat((alert.current * 100).toFixed(2)), 
      parseFloat((alert.past * 100).toFixed(2)), 
      parseFloat((alert.absChange * 100).toFixed(2)), 
      parseFloat(alert.pctChange.toFixed(2))
    ]);
  }
  Logger.log('Logged ' + alerts.length + ' alerts to ALERTS tab');
}

function sendAlertEmail(config, alerts, fullPeriodStr) {
  Logger.log('Attempting to send email to: ' + config.emails.join(','));

  var subject = 'Auction insights alert';
  var metricText = config.metric;
  
  var htmlBody = '<h3>Auction Insights Alert</h3>';
  htmlBody += '<p><strong>Metric:</strong> ' + metricText + '<br>';
  htmlBody += '<strong>Comparison:</strong> ' + fullPeriodStr + '<br>';
  htmlBody += '<strong>Threshold:</strong> +/- ' + config.threshold + '%</p>';
  
  htmlBody += '<table border="1" cellpadding="5" cellspacing="0" style="border-collapse: collapse; font-family: Arial, sans-serif;">';
  htmlBody += '<tr style="background-color: #f2f2f2;">';
  htmlBody += '<th>Domain</th>';
  
  // Status Column is Permanent
  htmlBody += '<th>Status</th>';
  
  htmlBody += '<th>Comparison Period</th>';
  htmlBody += '<th>Current</th>';
  htmlBody += '<th>Previous</th>';
  htmlBody += '<th>Abs Change</th>';
  htmlBody += '<th>% Change</th>';
  htmlBody += '</tr>';
  
  // Sort by absolute % change descending
  alerts.sort(function(a, b) { return Math.abs(b.pctChange) - Math.abs(a.pctChange); });
  
  for (var i = 0; i < alerts.length; i++) {
    var item = alerts[i];
    // Visualize increase as Green and decrease as Red
    var color = item.pctChange > 0 ? 'green' : 'red';
    
    var statusHtml = '';
    if (item.status === 'NEW') {
      statusHtml = '<span style="color:blue; font-weight:bold;">NEW</span>';
    } else if (item.status === 'LOST') {
      statusHtml = '<span style="color:gray; font-weight:bold;">LOST</span>';
    } else {
      statusHtml = ''; 
    }
    
    htmlBody += '<tr>';
    htmlBody += '<td>' + item.domain + '</td>';
    
    htmlBody += '<td>' + statusHtml + '</td>';
    
    htmlBody += '<td>' + fullPeriodStr + '</td>';
    htmlBody += '<td>' + (item.current * 100).toFixed(2) + '%</td>';
    htmlBody += '<td>' + (item.past * 100).toFixed(2) + '%</td>';
    htmlBody += '<td>' + (item.absChange * 100).toFixed(2) + '%</td>';
    htmlBody += '<td style="color:' + color + '; font-weight:bold;">' + item.pctChange.toFixed(2) + '%</td>';
    htmlBody += '</tr>';
  }
  
  htmlBody += '</table>';
  
  try {
    MailApp.sendEmail({
      to: config.emails.join(','),
      subject: subject,
      htmlBody: htmlBody,
      name: 'Scripts Alerts'
    });
    Logger.log('Email sent successfully.');
  } catch (e) {
    Logger.log('ERROR SENDING EMAIL: ' + e.toString());
  }
}