//------------------------------------------------------------------------------
// © Adnine
// Config Updater Script
// This script automatically finds the most recent Auction Insights report
// and updates the config spreadsheet with its URL
// 
// SETUP: 
// 1. Go to script.google.com and create a new project
// 2. Paste this code
// 3. Update the CONFIG values below
// 4. Run once to authorize
// 5. Set up a time-based trigger to run daily (or however often you want)
//------------------------------------------------------------------------------
function updateConfigWithLatestReport() {
  // Update these values
  var CONFIG_SPREADSHEET_URL = '';
  var REPORT_NAME = ''; // The exact name of your scheduled reports
  
  Logger.log('Searching for latest report with exact name: ' + REPORT_NAME);
  
  var latestReport = findMostRecentSpreadsheet(REPORT_NAME);
  
  if (!latestReport) {
    Logger.log('ERROR: No spreadsheet found with exact name: ' + REPORT_NAME);
    return;
  }
  
  var reportUrl = latestReport.getUrl();
  var reportName = latestReport.getName();
  var lastModified = latestReport.getLastUpdated();
  
  Logger.log('Found latest report: ' + reportName);
  Logger.log('Last modified: ' + lastModified);
  Logger.log('URL: ' + reportUrl);
  
  // Update the config spreadsheet
  var configSpreadsheet = SpreadsheetApp.openByUrl(CONFIG_SPREADSHEET_URL);
  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];
    var oldName = configSheet.getName();
    configSheet.setName('CONFIG');
    Logger.log('Renamed "' + oldName + '" to CONFIG');
  }
  
  configSheet.getRange('A1').setValue(reportUrl);
  configSheet.getRange('B1').setValue(reportName);
  configSheet.getRange('C1').setValue(lastModified);
  
  Logger.log('Config spreadsheet updated successfully!');
}

function findMostRecentSpreadsheet(exactName) {
  // Search for all spreadsheets with the exact name
  var files = DriveApp.searchFiles(
    'title = "' + exactName + '" and mimeType = "' + MimeType.GOOGLE_SHEETS + '"'
  );
  
  var mostRecentFile = null;
  var mostRecentDate = null;
  
  // Iterate through all matching files and find the most recent one
  while (files.hasNext()) {
    var file = files.next();
    var lastUpdated = file.getLastUpdated();
    
    if (!mostRecentDate || lastUpdated > mostRecentDate) {
      mostRecentDate = lastUpdated;
      mostRecentFile = file;
    }
  }
  
  return mostRecentFile;
}

// Optional: Function to set up the trigger automatically
function createDailyTrigger() {
  // Delete existing triggers to avoid duplicates
  var triggers = ScriptApp.getProjectTriggers();
  for (var i = 0; i < triggers.length; i++) {
    ScriptApp.deleteTrigger(triggers[i]);
  }
  
  // Create new trigger to run daily at 8 AM
  ScriptApp.newTrigger('updateConfigWithLatestReport')
    .timeBased()
    .everyDays(1)
    .atHour(8)
    .create();
  
  Logger.log('Daily trigger created successfully!');
}

  // Update this URL with your master spreadsheet
  var configSpreadsheetUrl = 'yourURL.com';

 var competitorsToTrack = ['amazon.com','bestbuy.com'];

//------------------------------------------------------------------------------
// FULLY AUTOMATED VERSION
// This script visualizes all auction insights data with dynamic column detection.
// Works with both Search and Shopping campaigns.
// © Adnine

function main() {
  Logger.log('Beep boop scrippity script is starting');
  
  // ============================================================================
  // CONFIGURATION
  // ============================================================================
  
  // Update this URL with your master spreadsheet
  var configSpreadsheetUrl = '';
  
  // COMPETITOR FILTER (optional)
  // Leave empty array [] to track ALL competitors
  // Or specify competitors to track: ['domain1.com', 'domain2.com'] please note that these must be exactly identical to the URL thats shown in the Auction Insight report spreadsheets
  // Note: "You" (your own domain) is always included automatically
  var competitorsToTrack = []; 
  
  // ============================================================================
  
  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');
  
  // Log all sheet names
  var allSheets = configSpreadsheet.getSheets();
  Logger.log('All sheets in config spreadsheet: ' + allSheets.length + ' sheets found');
  for (var i = 0; i < allSheets.length; i++) {
    Logger.log('  Sheet ' + i + ': "' + allSheets[i].getName() + '"');
  }
  
  var configSheet = configSpreadsheet.getSheetByName('CONFIG');
  
  // If CONFIG sheet doesn't exist, this is the first run rename the first sheet to CONFIG
  // Note: this tab name can and should not be changed by user
  if (!configSheet) {
    Logger.log('CONFIG sheet not found, renaming first sheet...');
    configSheet = configSpreadsheet.getSheets()[0];
    var oldName = configSheet.getName();
    Logger.log('Old name: "' + oldName + '"');
    configSheet.setName('CONFIG');
    Logger.log('Successfully renamed "' + oldName + '" to "CONFIG"');
  } else {
    Logger.log('Found existing CONFIG sheet, skipping rename');
  }
  
  Logger.log('Reading URL from CONFIG sheet cell A1...');
  var spreadsheetUrl = configSheet.getRange('A1').getValue();
  Logger.log('URL retrieved: ' + spreadsheetUrl);
  
  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];
  
  // Header is always in row 3
  var headerInfo = parseHeaderRow(sourceSheet);
  if (!headerInfo) {
    Logger.log('ERROR: Could not parse header row from source spreadsheet');
    return;
  }
  
  Logger.log('Found header in row 3');
  Logger.log('Day column: ' + headerInfo.dateColumn);
  Logger.log('Domain column: ' + headerInfo.displayUrlColumn);
  Logger.log('Available metrics: ' + JSON.stringify(headerInfo.metrics));
  
  
  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);
    Logger.log('Filtered to ' + sourceData.length + ' rows for specified competitors');
  }
  
  // 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');
}

function parseHeaderRow(sheet) {
  var headerRowIndex = 3;
  var rowData = sheet.getRange(headerRowIndex, 1, 1, sheet.getLastColumn()).getValues()[0];
  
  Logger.log('Header row values:');
  for (var x = 0; x < rowData.length; x++) {
    Logger.log('Column ' + x + ': "' + rowData[x] + '"');
  }
  
  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) {
    Logger.log('ERROR: Missing required columns. Date: ' + dateColumn + ', Domain: ' + displayUrlColumn + ', Metrics: ' + metrics.length);
    return null;
  }
  
  return {
    headerRowIndex: headerRowIndex,
    dateColumn: dateColumn,
    displayUrlColumn: displayUrlColumn,
    metrics: metrics
  };
}

function readSourceData(sheet, headerInfo) {
  // Data starts at row 4 (row 3 is header)
  // someone can't remove rows or script will break
  var dataStartRow = 4;
  var lastRow = sheet.getLastRow();
  
  if (lastRow < dataStartRow) {
    return [];
  }
  
  var numRows = lastRow - dataStartRow + 1;
  var numCols = sheet.getLastColumn();
  var rawData = sheet.getRange(dataStartRow, 1, numRows, numCols).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;
      
      // There was a space in between < and 10% but kept both for safety
      if (typeof rawValue === 'string') {
        var trimmedValue = rawValue.trim();
        if (trimmedValue === '< 10%' || trimmedValue === '<10%') {
          value = 0.099;
        } 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) {
    // Extract date components using UTC methods
    var year = dateValue.getUTCFullYear();
    var month = dateValue.getUTCMonth();
    var day = dateValue.getUTCDate();
    // Return new Date at UTC midnight
    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);
    // Use UTC constructor to avoid timezone shifts
    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();
    // Always include "You"
    if (domain === 'you') {
      return true;
    }
    // Check if this competitor is in the allowed list
    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 (strings) to build a map
  var existingDataMap = {};
  var lastRow = dataSheet.getLastRow();
  
  if (lastRow > 1) {
    // Read as display values to get the exact text strings stored in the sheet
    var existingData = dataSheet.getRange(2, 1, lastRow - 1, 2).getDisplayValues();
    
    for (var i = 0; i < existingData.length; i++) {
      var dateStr = existingData[i][0];  // Date column (as string)
      var domainStr = existingData[i][1];  // Domain column (as string)
      var key = dateStr + '|' + domainStr;
      existingDataMap[key] = true;
    }
    
    Logger.log('Found ' + Object.keys(existingDataMap).length + ' existing date+domain combinations');
  }
  
  // Add new data (avoid duplicates)
  var addedCount = 0;
  var skippedCount = 0;
  
  for (var i = 0; i < newData.length; i++) {
    var dataRow = newData[i];
    
    // Convert date to string format
    var dateStr = formatDate(dataRow.date);
    var domainStr = dataRow.displayUrl;
    var key = dateStr + '|' + domainStr;
    
    // Check if this combination already exists
    if (existingDataMap[key]) {
      skippedCount++;
      continue;
    }
    
    // Build row to add
    var rowToAdd = [dateStr, domainStr];
    
    for (var j = 0; j < headerInfo.metrics.length; j++) {
      var metricName = headerInfo.metrics[j].name;
      rowToAdd.push(dataRow.metrics[metricName] || 0);
    }
    
    dataSheet.appendRow(rowToAdd);
    existingDataMap[key] = true;
    addedCount++;
  }
  
  Logger.log('Added ' + addedCount + ' new rows to historical data');
  Logger.log('Skipped ' + skippedCount + ' duplicate rows');
}

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) {
    Logger.log('ERROR: HISTORICAL_DATA sheet not found');
    return;
  }
  
  var lastRow = dataSheet.getLastRow();
  if (lastRow <= 1) {
    Logger.log('No data in HISTORICAL_DATA sheet');
    return;
  }
  
  // Read ALL data from HISTORICAL_DATA as strings
  var allData = dataSheet.getRange(1, 1, lastRow, dataSheet.getLastColumn()).getDisplayValues();
  var headers = allData[0];
  
  // Find column indices for metrics
  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];
  
  if (dateColumn === undefined || domainColumn === undefined || metricColumn === undefined) {
    Logger.log('ERROR: Could not find required columns for metric: ' + metric.name);
    return null;
  }
  
  // Build dateMap using display values (strings)
  var dateMap = {};
  var displayUrls = new Set();
  
  // Skip header row (index 0)
  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) {
    Logger.log('No data found for metric: ' + metric.name);
    return null;
  }
  
  var headerRow = ['Date'].concat(displayUrlArray.map(function(url) {
    return metric.name + ' ' + url;
  }));
  metricSheet.appendRow(headerRow);
  
  var sortedDates = Object.keys(dateMap).sort();
  
  Logger.log('Creating metric tab for ' + metric.name + ' with date range: ' + sortedDates[0] + ' to ' + sortedDates[sortedDates.length - 1]);
  
  for (var j = 0; j < sortedDates.length; j++) {
    var dateStr = sortedDates[j];
    var rowData = [dateStr];  // Use string directly from HISTORICAL_DATA
    
    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 chartsPerRow = 1;
  var chartWidth = 20;
  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('hAxis.titleTextStyle', {fontSize: 12})
    .setOption('vAxis.title', metricName)
    .setOption('vAxis.titleTextStyle', {fontSize: 12})
    .setOption('legend.position', 'bottom')
    .setOption('legend.textStyle', {fontSize: 12})
    .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('legend.textStyle', {fontSize: 12})
    .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();
}