// SERP AI Overview Tracker - Google Apps Script
// This script monitors keywords for AI Overview appearances and tracks metrics

// Configuration - Will be read from Settings sheet
const SERP_API_KEY = 'YOUR_SERP_API_KEY_HERE';
const SERP_API_URL = 'https://serpapi.com/search';

// Sheet names
const SHEETS = {
  KEYWORDS: 'Keywords Input',
  OUTPUT: 'AI Overview Results',
  LOG: 'Execution Log',
  SETTINGS: 'Settings',
  URL_TRACKING: 'AI Overview URL Tracking'
};

/**
 * Creates custom menu when spreadsheet opens
 */
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('🔍 SERP AI Tracker')
    .addItem('🛠️ Setup Project', 'setupProject')
    .addSeparator()
    .addItem('▶️ Run Manual Check', 'manualDailyCheck')
    .addItem('🧪 Test API Connection', 'testAPIConnection')
    .addItem('🔍 Debug API Response', 'debugAPIResponse')
    .addSeparator()
    .addItem('⏰ Setup Trigger Only', 'setupTriggerOnly')
    .addItem('⚙️ Clear All Triggers', 'clearAllTriggers')
    .addToUi();
}

/**
 * Initial setup function - Creates all necessary sheets
 * Run this function once to set up your project
 */
function setupProject() {
  try {
    Logger.log('Starting project setup...');
    
    // Create all required sheets
    createKeywordSheet();
    createOutputSheet();
    createLogSheet();
    createSettingsSheet();
    createURLTrackingSheet();
    
    // Set up daily trigger
    setupDailyTrigger();
    
    Logger.log('Project setup completed successfully!');
    SpreadsheetApp.getUi().alert('Setup Complete!', 'All sheets have been created and daily trigger is set up. Configure your API key and domain filters in the Settings sheet, then add keywords to start tracking.', SpreadsheetApp.getUi().ButtonSet.OK);
    
  } catch (error) {
    Logger.log('Setup error: ' + error.toString());
    SpreadsheetApp.getUi().alert('Setup Error', 'There was an error during setup: ' + error.toString(), SpreadsheetApp.getUi().ButtonSet.OK);
  }
}

/**
 * Creates the Keywords Input sheet
 */
function createKeywordSheet() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getSheetByName(SHEETS.KEYWORDS);
  
  if (!sheet) {
    sheet = ss.insertSheet(SHEETS.KEYWORDS);
    Logger.log('Created Keywords Input sheet');
  }
  
  // Set up headers
  const headers = [
    'Keyword',
    'Brand Name (Optional)',
    'Status'
  ];
  
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
  sheet.getRange(1, 1, 1, headers.length).setFontWeight('bold');
  sheet.getRange(1, 1, 1, headers.length).setBackground('#4285f4');
  sheet.getRange(1, 1, 1, headers.length).setFontColor('white');
  
  // Add sample data
  const sampleData = [
    ['best smartphones 2024', 'Apple', 'Active'],
    ['how to lose weight', '', 'Active'],
    ['AI tools for business', 'OpenAI', 'Active']
  ];
  
  sheet.getRange(2, 1, sampleData.length, 3).setValues(sampleData);
  
  // Format columns
  sheet.setColumnWidth(1, 200);
  sheet.setColumnWidth(2, 150);
  sheet.setColumnWidth(3, 100);
  
  // Add data validation for Status column
  const statusRange = sheet.getRange(2, 3, 1000, 1);
  const statusValidation = SpreadsheetApp.newDataValidation()
    .requireValueInList(['Active', 'Paused'])
    .build();
  statusRange.setDataValidation(statusValidation);
  
  Logger.log('Keywords sheet setup complete');
}

/**
 * Creates the Output Results sheet
 */
function createOutputSheet() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getSheetByName(SHEETS.OUTPUT);
  
  if (!sheet) {
    sheet = ss.insertSheet(SHEETS.OUTPUT);
    Logger.log('Created Output sheet');
  }
  
  // Set up headers
  const headers = [
    'Keyword',
    'Last Checked',
    'AI Overview Count',
    'Brand Mentioned',
    'Brand Mention Count',
    'AI Overview Links',
    'Brand-Related Links',
    'My Domain Links',
    'AI Overview Present',
    'Status'
  ];
  
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
  sheet.getRange(1, 1, 1, headers.length).setFontWeight('bold');
  sheet.getRange(1, 1, 1, headers.length).setBackground('#34a853');
  sheet.getRange(1, 1, 1, headers.length).setFontColor('white');
  
  // Format columns
  sheet.setColumnWidth(1, 200);  // Keyword
  sheet.setColumnWidth(2, 150);  // Last Checked
  sheet.setColumnWidth(3, 120);  // AI Overview Count
  sheet.setColumnWidth(4, 120);  // Brand Mentioned
  sheet.setColumnWidth(5, 140);  // Brand Mention Count
  sheet.setColumnWidth(6, 120);  // AI Overview Links
  sheet.setColumnWidth(7, 140);  // Brand-Related Links
  sheet.setColumnWidth(8, 120);  // My Domain Links
  sheet.setColumnWidth(9, 140);  // AI Overview Present
  sheet.setColumnWidth(10, 100); // Status
  
  Logger.log('Output sheet setup complete');
}

/**
 * Creates the Execution Log sheet
 */
function createLogSheet() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getSheetByName(SHEETS.LOG);
  
  if (!sheet) {
    sheet = ss.insertSheet(SHEETS.LOG);
    Logger.log('Created Log sheet');
  }
  
  // Set up headers
  const headers = [
    'Timestamp',
    'Action',
    'Keyword',
    'Status',
    'Details'
  ];
  
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
  sheet.getRange(1, 1, 1, headers.length).setFontWeight('bold');
  sheet.getRange(1, 1, 1, headers.length).setBackground('#ea4335');
  sheet.getRange(1, 1, 1, headers.length).setFontColor('white');
  
  // Format columns
  sheet.setColumnWidth(1, 180);
  sheet.setColumnWidth(2, 120);
  sheet.setColumnWidth(3, 200);
  sheet.setColumnWidth(4, 100);
  sheet.setColumnWidth(5, 300);
  
  Logger.log('Log sheet setup complete');
}

/**
 * Creates the Settings sheet
 */
function createSettingsSheet() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getSheetByName(SHEETS.SETTINGS);
  
  if (!sheet) {
    sheet = ss.insertSheet(SHEETS.SETTINGS);
    Logger.log('Created Settings sheet');
  }
  
  // Set up headers
  const headers = [
    'Setting',
    'Value',
    'Description'
  ];
  
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
  sheet.getRange(1, 1, 1, headers.length).setFontWeight('bold');
  sheet.getRange(1, 1, 1, headers.length).setBackground('#ff9900');
  sheet.getRange(1, 1, 1, headers.length).setFontColor('white');
  
  // Add default settings
  const settings = [
    ['API_KEY', 'YOUR_SERP_API_KEY_HERE', 'Your SerpApi API key'],
    ['COUNTRY', 'uk', 'Country code (uk, us, ca, au, etc.)'],
    ['LANGUAGE', 'en', 'Language code (en, es, fr, de, etc.)'],
    ['LOCATION', 'London,England,United Kingdom', 'Specific location for search'],
    ['FREQUENCY', 'DAILY', 'Run frequency: DAILY or WEEKLY'],
    ['TRIGGER_HOUR', '9', 'Hour to run check (0-23)'],
    ['DELAY_SECONDS', '2', 'Delay between requests (to avoid rate limiting)'],
    ['MAX_LOG_ENTRIES', '1000', 'Maximum number of log entries to keep'],
    ['DOMAIN_FILTER', 'example.com,mycompany.com', 'Your domains to track (comma-separated, includes subdomains)']
  ];
  
  sheet.getRange(2, 1, settings.length, 3).setValues(settings);
  
  // Format columns
  sheet.setColumnWidth(1, 150);
  sheet.setColumnWidth(2, 250);
  sheet.setColumnWidth(3, 300);
  
  // Add data validation for some fields
  const countryRange = sheet.getRange(3, 2);
  const countryValidation = SpreadsheetApp.newDataValidation()
    .requireValueInList(['uk', 'us', 'ca', 'au', 'de', 'fr', 'es', 'it', 'nl', 'be'])
    .build();
  countryRange.setDataValidation(countryValidation);
  
  const languageRange = sheet.getRange(4, 2);
  const languageValidation = SpreadsheetApp.newDataValidation()
    .requireValueInList(['en', 'es', 'fr', 'de', 'it', 'nl', 'pt', 'ru'])
    .build();
  languageRange.setDataValidation(languageValidation);
  
  const frequencyRange = sheet.getRange(6, 2);
  const frequencyValidation = SpreadsheetApp.newDataValidation()
    .requireValueInList(['DAILY', 'WEEKLY'])
    .build();
  frequencyRange.setDataValidation(frequencyValidation);
  
  // Style the settings
  sheet.getRange(2, 1, settings.length, 1).setFontWeight('bold');
  sheet.getRange(2, 3, settings.length, 1).setFontStyle('italic');
  
  // Add instructions
  const instructionText = 'INSTRUCTIONS:\n\n1. Update API_KEY with your SerpApi key\n2. Set DOMAIN_FILTER with your domains (e.g., "altium.com,mysite.com")\n3. Modify COUNTRY and LANGUAGE as needed\n4. Change LOCATION for specific city targeting\n5. Adjust FREQUENCY (DAILY or WEEKLY) for cost control\n6. Set TRIGGER_HOUR for when checks run\n\nSave changes and run setupProject() again to apply new trigger settings.';
  
  sheet.getRange(settings.length + 4, 1, 1, 3).merge();
  sheet.getRange(settings.length + 4, 1).setValue(instructionText);
  sheet.getRange(settings.length + 4, 1).setWrap(true);
  sheet.getRange(settings.length + 4, 1).setBackground('#f0f0f0');
  sheet.setRowHeight(settings.length + 4, 150);
  
  Logger.log('Settings sheet setup complete');
}

/**
 * Creates the AI Overview URL Tracking sheet
 */
function createURLTrackingSheet() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getSheetByName(SHEETS.URL_TRACKING);
  
  if (!sheet) {
    sheet = ss.insertSheet(SHEETS.URL_TRACKING);
    Logger.log('Created AI Overview URL Tracking sheet');
  }
  
  // Set up headers
  const headers = [
    'Date',
    'Keyword',
    'URL',
    'Domain',
    'Page Title',
    'Ranking Position',
    'Reference Type',
    'Brand Related',
    'My Domain',
    'Snippet/Description'
  ];
  
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
  sheet.getRange(1, 1, 1, headers.length).setFontWeight('bold');
  sheet.getRange(1, 1, 1, headers.length).setBackground('#9c27b0');
  sheet.getRange(1, 1, 1, headers.length).setFontColor('white');
  
  // Format columns
  sheet.setColumnWidth(1, 120);  // Date
  sheet.setColumnWidth(2, 200);  // Keyword
  sheet.setColumnWidth(3, 300);  // URL
  sheet.setColumnWidth(4, 150);  // Domain
  sheet.setColumnWidth(5, 250);  // Page Title
  sheet.setColumnWidth(6, 100);  // Ranking Position
  sheet.setColumnWidth(7, 120);  // Reference Type
  sheet.setColumnWidth(8, 100);  // Brand Related
  sheet.setColumnWidth(9, 100);  // My Domain
  sheet.setColumnWidth(10, 300); // Snippet/Description
  
  Logger.log('URL Tracking sheet setup complete');
}

/**
 * Gets setting value from Settings sheet
 */
function getSetting(settingName) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(SHEETS.SETTINGS);
  
  if (!sheet) {
    Logger.log('Settings sheet not found, using default values');
    return getDefaultSetting(settingName);
  }
  
  const data = sheet.getDataRange().getValues();
  
  for (let i = 1; i < data.length; i++) {
    if (data[i][0] === settingName) {
      return data[i][1];
    }
  }
  
  Logger.log(`Setting ${settingName} not found, using default`);
  return getDefaultSetting(settingName);
}

/**
 * Gets default setting values
 */
function getDefaultSetting(settingName) {
  const defaults = {
    'API_KEY': 'YOUR_SERP_API_KEY_HERE',
    'COUNTRY': 'uk',
    'LANGUAGE': 'en',
    'LOCATION': 'London,England,United Kingdom',
    'FREQUENCY': 'DAILY',
    'TRIGGER_HOUR': '9',
    'DELAY_SECONDS': '2',
    'MAX_LOG_ENTRIES': '1000',
    'DOMAIN_FILTER': 'example.com,mycompany.com'
  };
  
  return defaults[settingName] || '';
}

/**
 * Sets up daily trigger for automatic execution
 */
function setupDailyTrigger() {
  // Delete existing triggers first
  const triggers = ScriptApp.getProjectTriggers();
  triggers.forEach(trigger => {
    if (trigger.getHandlerFunction() === 'runDailyCheck') {
      ScriptApp.deleteTrigger(trigger);
    }
  });
  
  // Get settings
  const frequency = getSetting('FREQUENCY') || 'DAILY';
  const triggerHour = parseInt(getSetting('TRIGGER_HOUR')) || 9;
  
  // Create trigger based on frequency
  if (frequency === 'WEEKLY') {
    ScriptApp.newTrigger('runDailyCheck')
      .timeBased()
      .everyWeeks(1)
      .onWeekDay(ScriptApp.WeekDay.MONDAY)
      .atHour(triggerHour)
      .create();
    Logger.log(`Weekly trigger set up successfully for Mondays at ${triggerHour}:00`);
  } else {
    ScriptApp.newTrigger('runDailyCheck')
      .timeBased()
      .everyDays(1)
      .atHour(triggerHour)
      .create();
    Logger.log(`Daily trigger set up successfully for ${triggerHour}:00`);
  }
}

/**
 * Main function that runs daily to check keywords
 */
function runDailyCheck() {
  try {
    logExecution('DAILY_CHECK_START', '', 'SUCCESS', 'Starting daily keyword check');
    
    const keywords = getActiveKeywords();
    Logger.log(`Found ${keywords.length} active keywords to check`);
    
    if (keywords.length === 0) {
      logExecution('NO_KEYWORDS', '', 'WARNING', 'No active keywords found');
      return;
    }
    
    // Process each keyword
    for (const keywordData of keywords) {
      try {
        processKeyword(keywordData.keyword, keywordData.brand, keywordData.row);
        
        // Add delay to avoid rate limiting
        const delaySeconds = parseInt(getSetting('DELAY_SECONDS')) || 2;
        Utilities.sleep(delaySeconds * 1000);
        
      } catch (error) {
        Logger.log(`Error processing keyword "${keywordData.keyword}": ${error.toString()}`);
        logExecution('KEYWORD_ERROR', keywordData.keyword, 'ERROR', error.toString());
      }
    }
    
    logExecution('DAILY_CHECK_COMPLETE', '', 'SUCCESS', `Processed ${keywords.length} keywords`);
    
  } catch (error) {
    Logger.log('Daily check error: ' + error.toString());
    logExecution('DAILY_CHECK_ERROR', '', 'ERROR', error.toString());
  }
}

/**
 * Gets active keywords from the input sheet
 */
function getActiveKeywords() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(SHEETS.KEYWORDS);
  
  if (!sheet) {
    throw new Error('Keywords sheet not found');
  }
  
  const data = sheet.getDataRange().getValues();
  const keywords = [];
  const seenKeywords = new Set(); // Track case-insensitive keywords to avoid duplicates
  
  // Skip header row
  for (let i = 1; i < data.length; i++) {
    const [keyword, brand, status] = data[i];
    
    if (keyword && status === 'Active') {
      const normalizedKeyword = keyword.toString().trim().toLowerCase();
      
      // Skip if we've already seen this keyword (case insensitive)
      if (seenKeywords.has(normalizedKeyword)) {
        Logger.log(`Skipping duplicate keyword: "${keyword}" (already tracking case variation)`);
        continue;
      }
      
      seenKeywords.add(normalizedKeyword);
      
      keywords.push({
        keyword: keyword.toString().trim(), // Keep original case for display
        normalizedKeyword: normalizedKeyword, // Lowercase for comparison
        brand: brand ? brand.toString().trim() : '',
        row: i + 1
      });
    }
  }
  
  return keywords;
}

/**
 * Processes a single keyword
 */
function processKeyword(keyword, brand, row) {
  Logger.log(`Processing keyword: "${keyword}"`);
  
  try {
    // Make SERP API request
    const serpData = makeSerpRequest(keyword);
    
    // Analyze the results
    const analysis = analyzeResults(serpData, brand);
    
    // Update the output sheet
    updateOutputSheet(keyword, analysis);
    
    // Track URLs in AI Overview
    trackAIOverviewURLs(keyword, analysis, brand);
    
    logExecution('KEYWORD_PROCESSED', keyword, 'SUCCESS', 
      `AI Overview: ${analysis.hasAIOverview ? 'Yes' : 'No'}, Brand Mentioned: ${analysis.brandMentioned ? 'Yes' : 'No'}, My Domain Links: ${analysis.myDomainLinks}`);
    
  } catch (error) {
    Logger.log(`Error processing keyword "${keyword}": ${error.toString()}`);
    logExecution('KEYWORD_ERROR', keyword, 'ERROR', error.toString());
    throw error;
  }
}

/**
 * Makes request to SERP API
 */
function makeSerpRequest(keyword) {
  // Get settings from Settings sheet
  const apiKey = getSetting('API_KEY');
  const country = getSetting('COUNTRY');
  const language = getSetting('LANGUAGE');
  const location = getSetting('LOCATION');
  
  if (!apiKey || apiKey === 'YOUR_SERP_API_KEY_HERE') {
    throw new Error('SERP API key not configured. Please set your API key in the Settings sheet.');
  }
  
  // Build URL with settings from sheet
  const url = `${SERP_API_URL}?engine=google&q=${encodeURIComponent(keyword)}&api_key=${apiKey}&gl=${country}&hl=${language}&location=${encodeURIComponent(location)}`;
  
  const response = UrlFetchApp.fetch(url, {
    method: 'GET',
    muteHttpExceptions: true
  });
  
  if (response.getResponseCode() !== 200) {
    throw new Error(`SERP API request failed: ${response.getResponseCode()} - ${response.getContentText()}`);
  }
  
  return JSON.parse(response.getContentText());
}

/**
 * Analyzes SERP results for AI Overview and brand mentions
 */
function analyzeResults(serpData, brand) {
  const analysis = {
    hasAIOverview: false,
    aiOverviewCount: 0,
    brandMentioned: false,
    brandMentionCount: 0,
    totalLinks: 0,
    brandRelatedLinks: 0,
    myDomainLinks: 0,
    aiOverviewContent: '',
    aiOverviewReferences: [] // Store reference data for URL tracking
  };
  
  try {
    // Method 1: Check for AI Overview in ai_overview field (Primary detection)
    if (serpData.ai_overview) {
      analysis.hasAIOverview = true;
      analysis.aiOverviewCount = 1;
      
      // Extract text from complex text_blocks structure
      let aiText = '';
      if (serpData.ai_overview.text_blocks && Array.isArray(serpData.ai_overview.text_blocks)) {
        for (const block of serpData.ai_overview.text_blocks) {
          // Handle different block types
          if (block.snippet) {
            aiText += block.snippet + ' ';
          }
          
          // Handle list blocks
          if (block.list && Array.isArray(block.list)) {
            for (const listItem of block.list) {
              if (listItem.title) {
                aiText += listItem.title + ' ';
              }
              if (listItem.snippet) {
                aiText += listItem.snippet + ' ';
              }
            }
          }
        }
      }
      
      analysis.aiOverviewContent = aiText.trim();
      
      // Count references (links) and check for brand-related and domain links
      if (serpData.ai_overview.references && Array.isArray(serpData.ai_overview.references)) {
        analysis.totalLinks = serpData.ai_overview.references.length;
        analysis.aiOverviewReferences = serpData.ai_overview.references; // Store for URL tracking
        
        // Get domain filters for owned domain detection
        const domainFilterSetting = getSetting('DOMAIN_FILTER') || '';
        const domainFilters = domainFilterSetting.split(',').map(d => d.trim()).filter(d => d.length > 0);
        
        // Count brand-related and owned domain links if specified
        if (brand || domainFilters.length > 0) {
          const brandLower = brand ? brand.toLowerCase() : '';
          let brandLinkCount = 0;
          let myDomainLinkCount = 0;
          
          for (const reference of serpData.ai_overview.references) {
            // Check for brand mentions
            if (brand) {
              let linkContainsBrand = false;
              
              if (reference.title && reference.title.toLowerCase().includes(brandLower)) {
                linkContainsBrand = true;
              }
              if (reference.link && reference.link.toLowerCase().includes(brandLower)) {
                linkContainsBrand = true;
              }
              if (reference.snippet && reference.snippet.toLowerCase().includes(brandLower)) {
                linkContainsBrand = true;
              }
              
              if (linkContainsBrand) {
                brandLinkCount++;
              }
            }
            
            // Check for owned domain matches
            if (domainFilters.length > 0 && reference.link && isDomainMatch(reference.link, domainFilters)) {
              myDomainLinkCount++;
            }
          }
          
          analysis.brandRelatedLinks = brandLinkCount;
          analysis.myDomainLinks = myDomainLinkCount;
        }
      }
      
      Logger.log(`AI Overview found with ${analysis.totalLinks} references, ${analysis.myDomainLinks} from owned domains`);
    }
    
    // Method 2: Check for direct AI Overview in answer_box (fallback)
    if (!analysis.hasAIOverview && serpData.answer_box) {
      if (serpData.answer_box.type === 'ai_overview' || 
          serpData.answer_box.type === 'ai_generated_overview') {
        analysis.hasAIOverview = true;
        analysis.aiOverviewCount = 1;
        analysis.aiOverviewContent = serpData.answer_box.snippet || serpData.answer_box.text || '';
        
        if (serpData.answer_box.links) {
          analysis.totalLinks = serpData.answer_box.links.length;
          analysis.aiOverviewReferences = serpData.answer_box.links;
        }
      }
    }
    
    // Method 3: Check featured snippet as potential AI Overview (fallback)
    if (!analysis.hasAIOverview && serpData.featured_snippet) {
      const snippet = serpData.featured_snippet.snippet || '';
      if (snippet.length > 100) {
        analysis.hasAIOverview = true;
        analysis.aiOverviewCount = 1;
        analysis.aiOverviewContent = snippet;
        
        if (serpData.featured_snippet.links) {
          analysis.totalLinks = serpData.featured_snippet.links.length;
          analysis.aiOverviewReferences = serpData.featured_snippet.links;
        }
      }
    }
    
    // Check for brand mentions ONLY in AI overview content
    if (brand && analysis.aiOverviewContent) {
      const brandLower = brand.toLowerCase();
      const aiContentLower = analysis.aiOverviewContent.toLowerCase();
      
      // Count how many times the brand appears in AI overview
      const matches = aiContentLower.split(brandLower).length - 1;
      
      if (matches > 0) {
        analysis.brandMentioned = true;
        analysis.brandMentionCount = matches;
      }
    }
    
    // Enhanced logging for debugging
    Logger.log(`Raw SERP data keys: ${Object.keys(serpData).join(', ')}`);
    if (analysis.hasAIOverview) {
      Logger.log(`AI Overview detected: ${analysis.aiOverviewContent.substring(0, 200)}...`);
      Logger.log(`Total links: ${analysis.totalLinks}, Brand-related links: ${analysis.brandRelatedLinks}, My domain links: ${analysis.myDomainLinks}`);
      if (brand) {
        Logger.log(`Brand "${brand}" mentioned: ${analysis.brandMentioned} (${analysis.brandMentionCount} times)`);
      }
    } else {
      Logger.log('No AI Overview detected');
    }
    
  } catch (error) {
    Logger.log(`Error analyzing results: ${error.toString()}`);
  }
  
  return analysis;
}

/**
 * Updates the output sheet with results
 */
function updateOutputSheet(keyword, analysis) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(SHEETS.OUTPUT);
  
  if (!sheet) {
    throw new Error('Output sheet not found');
  }
  
  // Always append a new row (don't update existing)
  const targetRow = sheet.getLastRow() + 1;
  
  // Get cumulative AI Overview count by checking previous entries for this keyword (case insensitive)
  let cumulativeAIOverviewCount = 0;
  const data = sheet.getDataRange().getValues();
  
  // Count previous AI overview occurrences for this keyword (case insensitive)
  for (let i = 1; i < data.length; i++) {
    if (data[i][0] && data[i][0].toString().toLowerCase() === keyword.toLowerCase() && data[i][2]) {
      cumulativeAIOverviewCount = Math.max(cumulativeAIOverviewCount, data[i][2] || 0);
    }
  }
  
  // Increment if AI Overview found today
  if (analysis.hasAIOverview) {
    cumulativeAIOverviewCount++;
  }
  
  // Prepare row data
  const rowData = [
    keyword,
    new Date(),
    cumulativeAIOverviewCount,
    analysis.brandMentioned ? 'Yes' : 'No',
    analysis.brandMentionCount,
    analysis.totalLinks,
    analysis.brandRelatedLinks,
    analysis.myDomainLinks,
    analysis.hasAIOverview ? 'Yes' : 'No',
    'Updated'
  ];
  
  sheet.getRange(targetRow, 1, 1, rowData.length).setValues([rowData]);
  
  // Format the row
  if (analysis.hasAIOverview) {
    sheet.getRange(targetRow, 1, 1, rowData.length).setBackground('#e8f5e8');
  }
  
  Logger.log(`Added new row for keyword: ${keyword}`);
}

/**
 * Checks if a URL belongs to specified domains (including subdomains)
 */
function isDomainMatch(url, domainFilters) {
  if (!domainFilters || domainFilters.length === 0) {
    return false;
  }
  
  try {
    const urlObj = new URL(url);
    const hostname = urlObj.hostname.toLowerCase();
    
    for (const domain of domainFilters) {
      const cleanDomain = domain.trim().toLowerCase();
      
      // Check exact match or subdomain match
      if (hostname === cleanDomain || hostname.endsWith('.' + cleanDomain)) {
        return true;
      }
    }
    
    return false;
  } catch (e) {
    // If URL parsing fails, check if any domain appears in the URL string
    const urlLower = url.toLowerCase();
    for (const domain of domainFilters) {
      if (urlLower.includes(domain.trim().toLowerCase())) {
        return true;
      }
    }
    return false;
  }
}

/**
 * Tracks URLs found in AI Overview references
 */
function trackAIOverviewURLs(keyword, analysis, brand) {
  if (!analysis.hasAIOverview || !analysis.aiOverviewReferences || analysis.aiOverviewReferences.length === 0) {
    return;
  }
  
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(SHEETS.URL_TRACKING);
  
  if (!sheet) {
    Logger.log('URL Tracking sheet not found, skipping URL tracking');
    return;
  }
  
  const currentDate = new Date();
  const brandLower = brand ? brand.toLowerCase() : '';
  const domainFilterSetting = getSetting('DOMAIN_FILTER') || '';
  const domainFilters = domainFilterSetting.split(',').map(d => d.trim()).filter(d => d.length > 0);
  
  for (let i = 0; i < analysis.aiOverviewReferences.length; i++) {
    const reference = analysis.aiOverviewReferences[i];
    if (!reference.link) continue;
    
    // Extract domain from URL
    let domain = '';
    try {
      domain = new URL(reference.link).hostname.replace('www.', '');
    } catch (e) {
      // Fallback for malformed URLs
      domain = reference.link.split('/')[2] || reference.link;
    }
    
    // Check if brand-related
    let isBrandRelated = 'No';
    if (brand) {
      if ((reference.title && reference.title.toLowerCase().includes(brandLower)) ||
          (reference.link && reference.link.toLowerCase().includes(brandLower)) ||
          (reference.snippet && reference.snippet.toLowerCase().includes(brandLower))) {
        isBrandRelated = 'Yes';
      }
    }
    
    // Check if it's from our domains
    let isMyDomain = 'No';
    if (domainFilters.length > 0 && isDomainMatch(reference.link, domainFilters)) {
      isMyDomain = 'Yes';
    }
    
    // Prepare row data
    const rowData = [
      currentDate,
      keyword,
      reference.link || '',
      domain,
      reference.title || '',
      i + 1, // Position in AI Overview references
      'Reference', // Reference type
      isBrandRelated,
      isMyDomain,
      (reference.snippet || '').substring(0, 200) // Truncate snippet
    ];
    
    sheet.appendRow(rowData);
    
    // Apply conditional formatting
    const lastRow = sheet.getLastRow();
    if (isMyDomain === 'Yes') {
      sheet.getRange(lastRow, 1, 1, rowData.length).setBackground('#e3f2fd'); // Light blue for owned domains
    } else if (isBrandRelated === 'Yes') {
      sheet.getRange(lastRow, 1, 1, rowData.length).setBackground('#e8f5e8'); // Light green for brand-related
    }
  }
  
  Logger.log(`Tracked ${analysis.aiOverviewReferences.length} URLs for keyword: ${keyword}`);
}

/**
 * Logs execution details to the log sheet
 */
function logExecution(action, keyword, status, details) {
  try {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sheet = ss.getSheetByName(SHEETS.LOG);
    
    if (!sheet) {
      Logger.log('Log sheet not found, skipping log entry');
      return;
    }
    
    // Check if we need to clean up old entries
    const maxEntries = parseInt(getSetting('MAX_LOG_ENTRIES')) || 1000;
    if (sheet.getLastRow() > maxEntries + 1) { // +1 for header
      const rowsToDelete = sheet.getLastRow() - maxEntries;
      sheet.deleteRows(2, rowsToDelete); // Delete from row 2 (after header)
    }
    
    // Add new log entry
    const logData = [
      new Date(),
      action,
      keyword || '',
      status,
      details || ''
    ];
    
    sheet.appendRow(logData);
    
    // Apply conditional formatting based on status
    const lastRow = sheet.getLastRow();
    if (status === 'ERROR') {
      sheet.getRange(lastRow, 1, 1, logData.length).setBackground('#ffebee'); // Light red
    } else if (status === 'WARNING') {
      sheet.getRange(lastRow, 1, 1, logData.length).setBackground('#fff3e0'); // Light orange
    } else if (status === 'SUCCESS') {
      sheet.getRange(lastRow, 1, 1, logData.length).setBackground('#e8f5e8'); // Light green
    }
    
  } catch (error) {
    Logger.log(`Error logging execution: ${error.toString()}`);
  }
}

/**
 * Manual function to run the daily check
 */
function manualDailyCheck() {
  try {
    SpreadsheetApp.getUi().alert('Starting Check', 'Processing keywords... This may take a few minutes.', SpreadsheetApp.getUi().ButtonSet.OK);
    runDailyCheck();
    SpreadsheetApp.getUi().alert('Complete', 'Keyword check completed! Check the results in the AI Overview Results sheet.', SpreadsheetApp.getUi().ButtonSet.OK);
  } catch (error) {
    SpreadsheetApp.getUi().alert('Error', 'Check failed: ' + error.toString(), SpreadsheetApp.getUi().ButtonSet.OK);
  }
}

/**
 * Test API connection with a simple query
 */
function testAPIConnection() {
  try {
    const apiKey = getSetting('API_KEY');
    
    if (!apiKey || apiKey === 'YOUR_SERP_API_KEY_HERE') {
      SpreadsheetApp.getUi().alert('API Key Missing', 'Please configure your SerpApi API key in the Settings sheet first.', SpreadsheetApp.getUi().ButtonSet.OK);
      return;
    }
    
    // Test with a simple query
    const testKeyword = 'test query';
    const serpData = makeSerpRequest(testKeyword);
    
    if (serpData && serpData.search_metadata && serpData.search_metadata.status === 'Success') {
      SpreadsheetApp.getUi().alert('API Test Successful', 'Your SerpApi connection is working correctly!', SpreadsheetApp.getUi().ButtonSet.OK);
      logExecution('API_TEST', testKeyword, 'SUCCESS', 'API connection test successful');
    } else {
      SpreadsheetApp.getUi().alert('API Test Failed', 'API responded but with unexpected data format.', SpreadsheetApp.getUi().ButtonSet.OK);
      logExecution('API_TEST', testKeyword, 'WARNING', 'API responded with unexpected format');
    }
    
  } catch (error) {
    SpreadsheetApp.getUi().alert('API Test Failed', 'Error: ' + error.toString(), SpreadsheetApp.getUi().ButtonSet.OK);
    logExecution('API_TEST', '', 'ERROR', error.toString());
  }
}

/**
 * Debug function to see raw API response
 */
function debugAPIResponse() {
  try {
    const apiKey = getSetting('API_KEY');
    
    if (!apiKey || apiKey === 'YOUR_SERP_API_KEY_HERE') {
      SpreadsheetApp.getUi().alert('API Key Missing', 'Please configure your SerpApi API key in the Settings sheet first.', SpreadsheetApp.getUi().ButtonSet.OK);
      return;
    }
    
    // Get test keyword from user
    const ui = SpreadsheetApp.getUi();
    const response = ui.prompt('Debug API Response', 'Enter a keyword to test:', ui.ButtonSet.OK_CANCEL);
    
    if (response.getSelectedButton() !== ui.Button.OK) {
      return;
    }
    
    const testKeyword = response.getResponseText().trim();
    if (!testKeyword) {
      ui.alert('Invalid Input', 'Please enter a valid keyword.', ui.ButtonSet.OK);
      return;
    }
    
    // Make API request
    const serpData = makeSerpRequest(testKeyword);
    
    // Log the raw response
    Logger.log('=== DEBUG API RESPONSE ===');
    Logger.log('Keyword: ' + testKeyword);
    Logger.log('Response keys: ' + Object.keys(serpData).join(', '));
    
    if (serpData.ai_overview) {
      Logger.log('AI Overview found:');
      Logger.log(JSON.stringify(serpData.ai_overview, null, 2));
    } else {
      Logger.log('No ai_overview field found');
      
      if (serpData.answer_box) {
        Logger.log('Answer box found:');
        Logger.log(JSON.stringify(serpData.answer_box, null, 2));
      }
      
      if (serpData.featured_snippet) {
        Logger.log('Featured snippet found:');
        Logger.log(JSON.stringify(serpData.featured_snippet, null, 2));
      }
    }
    
    ui.alert('Debug Complete', 'Raw API response logged to execution log. Check View > Logs to see the detailed response.', ui.ButtonSet.OK);
    logExecution('DEBUG_API', testKeyword, 'SUCCESS', 'Debug API response logged');
    
  } catch (error) {
    SpreadsheetApp.getUi().alert('Debug Failed', 'Error: ' + error.toString(), SpreadsheetApp.getUi().ButtonSet.OK);
    logExecution('DEBUG_API', '', 'ERROR', error.toString());
  }
}

/**
 * Sets up ONLY the daily trigger (doesn't touch existing data/sheets)
 */
function setupTriggerOnly() {
  try {
    setupDailyTrigger();
    const frequency = getSetting('FREQUENCY') || 'DAILY';
    const triggerHour = parseInt(getSetting('TRIGGER_HOUR')) || 9;
    
    if (frequency === 'WEEKLY') {
      SpreadsheetApp.getUi().alert('Trigger Setup Complete!', `Weekly automation restored! Will run every Monday at ${triggerHour}:00`, SpreadsheetApp.getUi().ButtonSet.OK);
    } else {
      SpreadsheetApp.getUi().alert('Trigger Setup Complete!', `Daily automation restored! Will run every day at ${triggerHour}:00`, SpreadsheetApp.getUi().ButtonSet.OK);
    }
    
    logExecution('TRIGGER_SETUP', '', 'SUCCESS', `${frequency} trigger set for ${triggerHour}:00`);
    
  } catch (error) {
    SpreadsheetApp.getUi().alert('Trigger Setup Error', 'Error setting up trigger: ' + error.toString(), SpreadsheetApp.getUi().ButtonSet.OK);
    logExecution('TRIGGER_SETUP', '', 'ERROR', error.toString());
  }
}

/**
 * Function to clear all triggers
 */
function clearAllTriggers() {
  try {
    const triggers = ScriptApp.getProjectTriggers();
    let deletedCount = 0;
    
    triggers.forEach(trigger => {
      ScriptApp.deleteTrigger(trigger);
      deletedCount++;
    });
    
    Logger.log(`Cleared ${deletedCount} triggers`);
    SpreadsheetApp.getUi().alert('Triggers Cleared', `Cleared ${deletedCount} triggers. Daily automation stopped. Your data and settings remain untouched.`, SpreadsheetApp.getUi().ButtonSet.OK);
    logExecution('TRIGGERS_CLEARED', '', 'SUCCESS', `Cleared ${deletedCount} triggers`);
    
  } catch (error) {
    SpreadsheetApp.getUi().alert('Error', 'Error clearing triggers: ' + error.toString(), SpreadsheetApp.getUi().ButtonSet.OK);
    logExecution('TRIGGERS_CLEARED', '', 'ERROR', error.toString());
  }
}

/**
 * Test function for development
 */
function testFunction() {
  Logger.log('Test function executed successfully');
  return true;
}