function onOpen() {
  const ui = SpreadsheetApp.getUi();
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  ensureHeaders(sheet); // Ensure headers are set
  setupStatusField(sheet); // Set up the status field
  
  // Count and display initial statistics
  countAndDisplayCurrentStats(sheet);

  ui.createMenu('Company Enrichment')
    .addItem('Enrich All Companies', 'runEnrichmentAllCompanies')
    .addItem('Enrich New/Failed Companies', 'runEnrichmentNewCompanies')
    .addItem('Choose Row Range to Enrich', 'chooseRowRangeToEnrich')
    .addItem('Set Max Row for All/New Enrichment', 'setMaxRowForEnrichment')
    .addToUi();
}

function chooseRowRangeToEnrich() {
  const ui = SpreadsheetApp.getUi();
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  // Get the total number of rows with data
  const lastRow = sheet.getLastRow();
  
  // Show input dialog for starting row
  const startResponse = ui.prompt(
    'Choose Row Range - Step 1',
    `Enter the STARTING row number (Row 3 to ${lastRow}):\n\nNote: Row 1 contains status info, Row 2 contains headers, so data rows start from Row 3.`,
    ui.ButtonSet.OK_CANCEL
  );
  
  // Check if user clicked OK and entered a value
  if (startResponse.getSelectedButton() !== ui.Button.OK) {
    return; // User cancelled
  }
  
  const startInputText = startResponse.getResponseText().trim();
  const startRowNumber = parseInt(startInputText);
  
  // Validate the start row input
  if (isNaN(startRowNumber)) {
    ui.alert('Invalid Input', 'Please enter a valid number for the starting row.', ui.ButtonSet.OK);
    return;
  }
  
  if (startRowNumber < 3) {
    ui.alert('Invalid Row', 'Please enter a starting row number 3 or higher (rows 1-2 are reserved for headers).', ui.ButtonSet.OK);
    return;
  }
  
  if (startRowNumber > lastRow) {
    ui.alert('Invalid Row', `Please enter a starting row number between 3 and ${lastRow}.`, ui.ButtonSet.OK);
    return;
  }
  
  // Show input dialog for ending row
  const endResponse = ui.prompt(
    'Choose Row Range - Step 2',
    `Enter the ENDING row number (${startRowNumber} to ${lastRow}):\n\nStarting row: ${startRowNumber}\nEnter the last row you want to enrich (inclusive).`,
    ui.ButtonSet.OK_CANCEL
  );
  
  // Check if user clicked OK and entered a value
  if (endResponse.getSelectedButton() !== ui.Button.OK) {
    return; // User cancelled
  }
  
  const endInputText = endResponse.getResponseText().trim();
  const endRowNumber = parseInt(endInputText);
  
  // Validate the end row input
  if (isNaN(endRowNumber)) {
    ui.alert('Invalid Input', 'Please enter a valid number for the ending row.', ui.ButtonSet.OK);
    return;
  }
  
  if (endRowNumber < startRowNumber) {
    ui.alert('Invalid Range', `The ending row (${endRowNumber}) must be greater than or equal to the starting row (${startRowNumber}).`, ui.ButtonSet.OK);
    return;
  }
  
  if (endRowNumber > lastRow) {
    ui.alert('Invalid Row', `Please enter an ending row number between ${startRowNumber} and ${lastRow}.`, ui.ButtonSet.OK);
    return;
  }
  
  // Confirm the action
  const confirmResponse = ui.alert(
    'Confirm Range Enrichment',
    `This will enrich companies from row ${startRowNumber} to row ${endRowNumber} (${endRowNumber - startRowNumber + 1} rows). Continue?`,
    ui.ButtonSet.YES_NO
  );
  
  if (confirmResponse === ui.Button.YES) {
    updateEnrichmentStatus(sheet, 'In Progress...');
    countAndDisplayCurrentStats(sheet);
    runEnrichmentFromRowRange(startRowNumber, endRowNumber);
  }
}

function setMaxRowForEnrichment() {
  const ui = SpreadsheetApp.getUi();
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  // Get the total number of rows with data
  const lastRow = sheet.getLastRow();
  
  // Show input dialog for max row
  const response = ui.prompt(
    'Set Maximum Row for Enrichment',
    `Enter the MAXIMUM row number to limit enrichment (Row 3 to ${lastRow}):\n\nThis will cap the "Enrich All Companies" and "Enrich New/Failed Companies" options.\nLeave empty to remove any existing limit.`,
    ui.ButtonSet.OK_CANCEL
  );
  
  // Check if user clicked OK
  if (response.getSelectedButton() !== ui.Button.OK) {
    return; // User cancelled
  }
  
  const inputText = response.getResponseText().trim();
  
  // If empty, remove the limit
  if (inputText === '') {
    PropertiesService.getScriptProperties().deleteProperty('max_enrichment_row');
    ui.alert('Limit Removed', 'Maximum row limit has been removed. All future enrichments will process all available rows.', ui.ButtonSet.OK);
    return;
  }
  
  const maxRowNumber = parseInt(inputText);
  
  // Validate the input
  if (isNaN(maxRowNumber)) {
    ui.alert('Invalid Input', 'Please enter a valid number or leave empty to remove the limit.', ui.ButtonSet.OK);
    return;
  }
  
  if (maxRowNumber < 3) {
    ui.alert('Invalid Row', 'Please enter a row number 3 or higher (rows 1-2 are reserved for headers).', ui.ButtonSet.OK);
    return;
  }
  
  if (maxRowNumber > lastRow) {
    ui.alert('Invalid Row', `Please enter a row number between 3 and ${lastRow}.`, ui.ButtonSet.OK);
    return;
  }
  
  // Save the max row setting
  PropertiesService.getScriptProperties().setProperty('max_enrichment_row', maxRowNumber.toString());
  
  // Confirm the setting
  ui.alert('Limit Set', `Maximum enrichment row has been set to ${maxRowNumber}. This limit will apply to "Enrich All Companies" and "Enrich New/Failed Companies" options.`, ui.ButtonSet.OK);
}

function runEnrichmentFromRowRange(startingRow, endingRow) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = sheet.getDataRange().getValues();
  const apiKey = PropertiesService.getScriptProperties().getProperty('api_key');

  if (!apiKey) {
    Logger.log('API Key not set. Please set it in Script Properties.');
    updateEnrichmentStatus(sheet, 'Error: API Key Missing');
    return;
  }

  let companiesToEnrich = [];
  // Convert to 0-based indices
  const startIndex = startingRow - 1;
  const endIndex = endingRow - 1;
  
  for (let i = startIndex; i <= endIndex; i++) {
    // Make sure we don't go beyond the data array
    if (i >= data.length) break;
    
    const companyName = data[i][0];
    const companyDomain = data[i][1];

    // Skip empty rows
    if (!companyName && !companyDomain) continue;

    if (companyName || companyDomain) {
      companiesToEnrich.push({
        id: (i - 1).toString(), // Adjust ID to match previous logic
        index: i,
        name: companyName,
        domain: companyDomain
      });
    }
  }

  Logger.log(`Found ${companiesToEnrich.length} companies to enrich from row ${startingRow} to ${endingRow}`);

  if (companiesToEnrich.length > 0) {
    processBatches(companiesToEnrich, sheet, apiKey);
  } else {
    updateEnrichmentStatus(sheet, 'No Companies to Enrich in Range');
  }
}

function setupStatusField(sheet) {
  // Set up the Enrichment Status field in A1 and B1
  sheet.getRange('A1').setValue('Enrichment Status:');
  sheet.getRange('A1').setFontWeight('bold');
  sheet.getRange('B1').setValue('Ready');
  sheet.getRange('B1').setFontWeight('bold');
  sheet.getRange('B1').setBackground('#f3f3f3');
  
  // Set up the Last Updated field in C1 and D1
  sheet.getRange('C1').setValue('Last Updated:');
  sheet.getRange('C1').setFontWeight('bold');
  sheet.getRange('D1').setValue('N/A');
  sheet.getRange('D1').setFontWeight('bold');
  sheet.getRange('D1').setBackground('#f3f3f3');
  
  // Set up the Enrichment Stats fields in E1 through H1
  sheet.getRange('E1').setValue('Enrichment Stats:');
  sheet.getRange('E1').setFontWeight('bold');
  
  sheet.getRange('F1').setValue('Success:0');
  sheet.getRange('F1').setFontWeight('bold');
  sheet.getRange('F1').setBackground('#f3f3f3');
  
  sheet.getRange('G1').setValue('No Data:0');
  sheet.getRange('G1').setFontWeight('bold');
  sheet.getRange('G1').setBackground('#f3f3f3');
  
  sheet.getRange('H1').setValue('Failed:0');
  sheet.getRange('H1').setFontWeight('bold');
  sheet.getRange('H1').setBackground('#f3f3f3');
}

function updateEnrichmentStatus(sheet, status) {
  try {
    // Make sure sheet is defined
    if (!sheet) {
      Logger.log("Error: Sheet is undefined in updateEnrichmentStatus");
      sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    }
    
    // Check if the range exists before setting values
    if (sheet.getRange('B1')) {
      sheet.getRange('B1').setValue(status);
      SpreadsheetApp.flush(); // Force the update to show immediately
    } else {
      Logger.log("Error: Could not find the B1 range");
    }
  } catch (e) {
    Logger.log("Error in updateEnrichmentStatus: " + e.message);
  }
}

// Function to update the statistics in the header row
function updateEnrichmentStats(sheet, successCount, noDataCount, failureCount) {
  try {
    // Make sure sheet is defined
    if (!sheet) {
      Logger.log("Error: Sheet is undefined in updateEnrichmentStats");
      sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    }
    
    // Check if the ranges exist before setting values
    if (sheet.getRange('F1') && sheet.getRange('G1') && sheet.getRange('H1')) {
      sheet.getRange('F1').setValue(`Success:${successCount}`);
      sheet.getRange('G1').setValue(`No Data:${noDataCount}`);
      sheet.getRange('H1').setValue(`Failed:${failureCount}`);
      SpreadsheetApp.flush(); // Force the update to show immediately
    } else {
      Logger.log("Error: Could not find one or more of the required ranges (F1, G1, H1)");
    }
  } catch (e) {
    Logger.log("Error in updateEnrichmentStats: " + e.message);
  }
}

function ensureHeaders(sheet) {
  const expectedHeaders = [
    "Company Name (Input)", "Company Domain (Input)", "Status", "Company ID", "Data Update", 
    "Company Name", "Company FQDN", "Founded", "Company Size", 
    "Specialties", "Categories", "Intent", "Technologies", "Funding", 
    "Revenue Range", "Company LinkedIn", "CrunchBase", "Main Industry", 
    "Sub Industry", "Address", "Full Location", "Country", "City", "State", 
    "State Code", "Country ISO2", "Description", "Status Code", "Message", 
    "Full Message"
  ];
  
  // Clear the first row except for A1-H1 which are used for status and statistics
  const firstRow = sheet.getRange(1, 9, 1, expectedHeaders.length - 8);
  firstRow.clear();
  
  // Set headers in row 2
  const headerRow = sheet.getRange(2, 1, 1, expectedHeaders.length);
  const existingHeaders = headerRow.getValues()[0];

  if (!existingHeaders.every((val, i) => val === expectedHeaders[i])) {
    headerRow.setValues([expectedHeaders]);  // Set headers
  }
  
  // Apply light grey background to row 2
  headerRow.setBackground('#f3f3f3');  // Light grey color
  
  // Make the headers bold for better visibility
  headerRow.setFontWeight('bold');
  
  // Optional: Center-align the headers
  headerRow.setHorizontalAlignment('center');
  
  sheet.setFrozenRows(2);  // Freeze rows 1 and 2
}

function runEnrichmentAllCompanies() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  updateEnrichmentStatus(sheet, 'In Progress...');
  
  // Count current statuses in the sheet first
  countAndDisplayCurrentStats(sheet);
  
  runEnrichmentCompanies(true); // Enrich all companies
}

function runEnrichmentNewCompanies() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  updateEnrichmentStatus(sheet, 'In Progress...');
  
  // Count current statuses in the sheet first
  countAndDisplayCurrentStats(sheet);
  
  runEnrichmentCompanies(false); // Enrich only new or failed companies
}

function runEnrichmentCompanies(all) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = sheet.getDataRange().getValues();
  const apiKey = PropertiesService.getScriptProperties().getProperty('api_key');

  if (!apiKey) {
    Logger.log('API Key not set. Please set it in Script Properties.');
    updateEnrichmentStatus(sheet, 'Error: API Key Missing');
    return;
  }

  // Check if there's a max row limit set
  const maxRowProperty = PropertiesService.getScriptProperties().getProperty('max_enrichment_row');
  const maxRowLimit = maxRowProperty ? parseInt(maxRowProperty) : null;
  const effectiveMaxRow = maxRowLimit ? Math.min(maxRowLimit, data.length - 1) : data.length - 1;

  let companiesToEnrich = [];
  // Start from row 3 (index 2) since row 2 is the header
  for (let i = 2; i <= effectiveMaxRow; i++) {
    const status = data[i][2];
    const companyName = data[i][0];
    const companyDomain = data[i][1];

    // Skip empty rows
    if (!companyName && !companyDomain) continue;

    if (all || (!status || status === "Failed")) {
      if (companyName || companyDomain) {
        companiesToEnrich.push({
          id: (i - 1).toString(), // Adjust ID to match previous logic
          index: i,
          name: companyName,
          domain: companyDomain
        });
      }
    }
  }

  const limitMessage = maxRowLimit ? ` (limited to row ${maxRowLimit})` : '';
  Logger.log(`Found ${companiesToEnrich.length} companies to enrich${limitMessage}`);

  if (companiesToEnrich.length > 0) {
    processBatches(companiesToEnrich, sheet, apiKey);
  } else {
    updateEnrichmentStatus(sheet, 'No Companies to Enrich');
  }
}

function processBatches(companies, sheet, apiKey) {
  const batchSize = 100;
  const totalBatches = Math.ceil(companies.length / batchSize);
  
  // Reset the statistics before starting by counting current status
  countAndDisplayCurrentStats(sheet);

  for (let batchIndex = 0; batchIndex < totalBatches; batchIndex++) {
    const batch = companies.slice(batchIndex * batchSize, (batchIndex + 1) * batchSize);
    const batchNumber = batchIndex + 1;
    updateEnrichmentStatus(sheet, `Processing Batch ${batchNumber}/${totalBatches}...`);
    
    // Process the batch
    processBulkCompanies(batch, sheet, apiKey);
    
    // Update statistics after each batch
    countAndDisplayCurrentStats(sheet);
  }
  
  // Do a final count to ensure accuracy
  countAndDisplayCurrentStats(sheet);
  
  updateEnrichmentStatus(sheet, 'Complete!');
  
  // Update the last updated timestamp
  const currentDateTime = new Date();
  const formattedDateTime = Utilities.formatDate(currentDateTime, Session.getScriptTimeZone(), "yyyy-MM-dd HH:mm:ss");
  sheet.getRange('D1').setValue(formattedDateTime);
}

function processBulkCompanies(batch, sheet, apiKey) {
  const url = 'https://api.lusha.com/bulk/company';
  const payload = {
    companies: batch.map(company => ({
      id: company.id,
      ...(company.name ? { name: company.name } : {}),
      ...(company.domain ? { domain: company.domain } : {}),
    }))
  };

  const options = {
    method: 'post',
    headers: {
      "Content-Type": "application/json",
      "api_key": apiKey
    },
    payload: JSON.stringify(payload),
    muteHttpExceptions: true
  };

  try {
    const response = UrlFetchApp.fetch(url, options);
    const rawResponse = response.getContentText();
    const statusCode = response.getResponseCode();

    Logger.log(`Batch API Response Status Code: ${statusCode}`);
    Logger.log(`Batch API Response Content: ${rawResponse}`);

    let results;
    try {
      results = JSON.parse(rawResponse);
    } catch (e) {
      batch.forEach(company => {
        sheet.getRange(company.index + 1, 3).setValue("Failed");
        sheet.getRange(company.index + 1, 16).setValue('Failed to parse response');
        sheet.getRange(company.index + 1, 28).setValue(400); // Column AB: Status Code - Bad request
        sheet.getRange(company.index + 1, 29).setValue('Bad request - Badly formatted request'); // Column AC: Status Message
        sheet.getRange(company.index + 1, 30).setValue('Failed to parse API response: ' + e.message); // Column AD: Full Message
      });
      return;
    }

    // Check if results is structured as {"data": {...}} for single company calls
    if (results.data && typeof results.data === 'object' && !Array.isArray(results.data)) {
      // Single company response structure
      const singleResults = results.data;
      // Convert to expected bulk structure
      results = {
        [batch[0].id]: singleResults
      };
    }

    batch.forEach(company => {
      const result = results[company.id];
      const companyRow = company.index;

      let statusMessage = 'Unknown error';
      let fullMessage = '';
      let individualStatusCode = statusCode; // Default to the batch status code
      let isValidResponse = false;
      
      if (result && typeof result === 'object' && Object.keys(result).length > 0) {
        // Check if there's meaningful data
        isValidResponse = (
          (result.name && result.name !== "EMPTY_DATA" && result.name !== "N/A") ||
          (result.fqdn && result.fqdn !== "N/A") ||
          (result.companySize && result.companySize.length > 0) ||
          result.employees ||
          result.industryPrimaryGroupDetails ||
          (result.industryTags && result.industryTags.length > 0)
        );
        
        // Check if we have a specific status code for this company
        if (result.status && typeof result.status === 'number') {
          individualStatusCode = result.status;
        }
        
        // Check for specific error message
        if (result.errorMessage) {
          fullMessage = result.errorMessage;
        }
      }

      // Generate status message based on individual status code
      switch (individualStatusCode) {
        case 200:
          statusMessage = 'OK – Successful request';
          break;
        case 201:
          statusMessage = 'Created – The request has been fulfilled and a new resource has been created';
          break;
        case 400:
          statusMessage = 'Bad request - Badly formatted request';
          break;
        case 401:
          statusMessage = 'Unauthorised – The API key is invalid';
          break;
        case 403:
          statusMessage = 'Unauthorised – Your account is not active. Please reach out to support';
          break;
        case 404:
          statusMessage = 'Not found – The requested endpoint was not found';
          break;
        case 412:
          statusMessage = 'The request failed due to invalid syntax';
          break;
        case 429:
          statusMessage = 'Limit reached – You\'ve reached your trial limit, please contact support for upgrade';
          break;
        case 451:
          statusMessage = 'We are unable to process this contact request due to our GDPR regulations';
          break;
        case 499:
          statusMessage = 'Request failed due to request timeout';
          break;
        default:
          if (individualStatusCode >= 500) {
            statusMessage = 'Server error – There\'s a problem on Lusha\'s end';
          }
      }

      if (!isValidResponse) {
        sheet.getRange(company.index + 1, 3).setValue("Failed");
        sheet.getRange(company.index + 1, 16).setValue('No valid data returned'); // Column P: Full Message
        
        // For failed requests with no valid data, use 404 if not already set
        if (individualStatusCode === statusCode && [200, 201].includes(individualStatusCode)) {
          individualStatusCode = 404; // Not found - no data
          statusMessage = 'Not found – No data available for this company';
        }
        
        // If no specific full message, create one
        if (!fullMessage) {
          fullMessage = 'No data available for this company';
        }
      } else {
        sheet.getRange(company.index + 1, 3).setValue("Enriched");
        
        // If no specific full message for success, create one
        if (!fullMessage) {
          fullMessage = 'Successfully enriched';
        }
      }

      sheet.getRange(company.index + 1, 28).setValue(individualStatusCode); // Column AB: Status Code
      sheet.getRange(company.index + 1, 29).setValue(statusMessage); // Column AC: Status Message
      sheet.getRange(company.index + 1, 30).setValue(fullMessage); // Column AD: Full Message

      const enrichedData = [
        String(result?.id) || 'N/A',                             // Column D: Company ID as text
        new Date(),                                              // Column E: Data Update
        result?.name || 'N/A',                                   // Column F: Company Name
        result?.fqdn || 'N/A',                                   // Column G: Company FQDN
        parseFoundedDate(result?.founded),                       // Column I: Founded
        result?.companySize?.join(' - ') || result?.employees || 'N/A', // Column J: Company Size
        (result?.specialities || []).join(', ') || 'N/A',        // Column K: Specialties
        result?.industryPrimaryGroupDetails?.sics?.map(s => s.description).join(', ') || 'N/A', // Column L: Categories (SIC)
        (result?.intent?.detectedTopics?.map(t => t.topicName).join(', ') || 'N/A'), // Column M: Intent Topics
        (result?.technologies || []).map(t => t.name).join(', ') || 'N/A', // Column N: Technologies
        formatFundingData(result?.funding),                      // Column O: Formatted Funding
        result?.revenueRange?.join(' - ') || 'N/A',              // Column P: Revenue Range
        result?.social?.linkedin?.url || result?.linkedin || 'N/A', // Column Q: LinkedIn URL
        result?.social?.crunchbase?.url || result?.crunchbase || 'N/A', // Column R: Crunchbase URL
        result?.mainIndustry || 'N/A', // Column S: Main Industry (from SIC)
        result?.subIndustry || 'N/A', // Column T: Sub Industry (from NAICS)
        result?.address || result?.location?.fullLocation || result?.rawLocation || 'N/A', // Column U: Address
        result?.location?.fullLocation || result?.rawLocation || 'N/A', // Column V: Full Location
        result?.location?.country || result?.country || 'N/A',    // Column W: Country
        result?.location?.city || result?.city || 'N/A',          // Column X: City
        result?.location?.state || result?.state || 'N/A',        // Column Y: State
        result?.location?.stateCode || result?.stateCode || 'N/A', // Column Z: State Code
        result?.location?.countryIso2 || result?.countryIso2 || 'N/A', // Column AA: Country ISO2
        result?.description || 'N/A'                             // Column AB: Description
      ];

      sheet.getRange(company.index + 1, 4, 1, enrichedData.length).setValues([enrichedData]);
    });
  } catch (error) {
    batch.forEach(company => {
      sheet.getRange(company.index + 1, 3).setValue("Failed");
      sheet.getRange(company.index + 1, 16).setValue(error.message);
      sheet.getRange(company.index + 1, 28).setValue(500); // Column AB: Use 5XX error for server issues
      sheet.getRange(company.index + 1, 29).setValue('Server error – There\'s a problem on Lusha\'s end'); // Column AC
      sheet.getRange(company.index + 1, 30).setValue(error.message); // Column AD: Full Message
    });
    updateEnrichmentStatus(sheet, 'Error Occurred');
  }
}

function parseFoundedDate(dateValue) {
  return typeof dateValue === 'string' ? dateValue : 'N/A';
}

function formatFundingData(funding) {
  if (!funding || !funding.rounds) return 'N/A';

  funding.rounds.sort((a, b) => new Date(a.roundDate) - new Date(b.roundDate));

  const totalRounds = funding.totalRounds || 'N/A';
  const totalAmount = funding.totalRoundsAmount ? funding.totalRoundsAmount.toLocaleString('en-US') : 'N/A';
  const currency = funding.currency || '';
  const isIpo = funding.isIpo ? 'IPO' : 'Private';
  let fundingInfo = `Total Rounds: ${totalRounds}, Total Amount: ${currency} ${totalAmount}, IPO Status: ${isIpo}\n`;

  funding.rounds.forEach((round, index) => {
    const roundType = round.roundType || 'Unknown Type';
    const roundAmount = round.roundAmount ? round.roundAmount.toLocaleString('en-US') : 'N/A';
    const roundDate = round.roundDate || 'Unknown Date';
    fundingInfo += `${index + 1}. Round Type: ${roundType}, Amount: ${currency} ${roundAmount}, Date: ${roundDate}\n`;
  });

  return fundingInfo.trim();
}

// Function to count and display current stats from the sheet
function countAndDisplayCurrentStats(sheet) {
  try {
    // Make sure sheet is defined
    if (!sheet) {
      Logger.log("Error: Sheet is undefined in countAndDisplayCurrentStats");
      sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    }
    
    const data = sheet.getDataRange().getValues();
    let successCount = 0;
    let noDataCount = 0;
    let failureCount = 0;
    
    // Start from row 3 (index 2) since row 2 is header
    for (let i = 2; i < data.length; i++) {
      // Skip empty rows
      if (!data[i][0] && !data[i][1]) continue;
      
      const status = data[i][2]; // Status is in column C (index 2)
      const companyName = data[i][5]; // Company Name is in column F (index 5)
      
      if (status === "Enriched") {
        successCount++;
      } else if (status === "Failed") {
        failureCount++; // Count all failed rows
        
        // If it's an EMPTY_DATA case, also count it as No Data
        if (companyName === "EMPTY_DATA") {
          noDataCount++;
        }
      }
    }
    
    // Update the stats display
    updateEnrichmentStats(sheet, successCount, noDataCount, failureCount);
    
    // Log for debugging
    Logger.log(`Current count - Success: ${successCount}, No Data: ${noDataCount}, Failed: ${failureCount}`);
    return { success: successCount, noData: noDataCount, failure: failureCount };
  } catch (e) {
    Logger.log("Error in countAndDisplayCurrentStats: " + e.message);
    return { success: 0, noData: 0, failure: 0 };
  }
}

function refreshStatistics() {
  try {
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    if (!sheet) {
      Logger.log("Error: Could not get active sheet in refreshStatistics");
      return;
    }
    
    const stats = countAndDisplayCurrentStats(sheet);
    
    // Display a toast message with the current stats
    const message = `Statistics refreshed: Success: ${stats.success}, No Data: ${stats.noData}, Failed: ${stats.failure}`;
    SpreadsheetApp.getActiveSpreadsheet().toast(message, "Statistics Updated", 5);
    
    SpreadsheetApp.flush();
  } catch (e) {
    Logger.log("Error in refreshStatistics: " + e.message);
  }
}

// Function to correct status codes for failed entries
function correctFailedStatusCodes() {
  try {
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    const data = sheet.getDataRange().getValues();
    let fixedCount = 0;
    
    // Start from row 3 (index 2) since row 2 is header
    for (let i = 2; i < data.length; i++) {
      // Skip empty rows
      if (!data[i][0] && !data[i][1]) continue;
      
      const status = data[i][2]; // Status is in column C (index 2)
      const companyName = data[i][5]; // Company Name is in column F (index 5)
      
      // Force update ANY rows with status "Failed" regardless of current status code
      if (status === "Failed") {
        let newCode = 404; // Default to 404 Not found
        let newMessage = 'Not found – Endpoint not found';
        let fullMessage = 'No data available for this company';
        
        // If it's EMPTY_DATA, use 404 (no data found)
        if (companyName === "EMPTY_DATA") {
          newCode = 404;
          newMessage = 'Not found – Endpoint not found';
        }
        
        // Always update the status code for any Failed entry
        sheet.getRange(i+1, 28).setValue(newCode); // Column AB: Status Code
        sheet.getRange(i+1, 29).setValue(newMessage); // Column AC: Message
        sheet.getRange(i+1, 30).setValue(fullMessage); // Column AD: Full Message
        fixedCount++;
      }
    }
    
    // Show a toast message with the results
    if (fixedCount > 0) {
      SpreadsheetApp.getActiveSpreadsheet().toast(`Fixed status codes for ${fixedCount} failed entries`, "Status Codes Updated", 5);
    } else {
      SpreadsheetApp.getActiveSpreadsheet().toast("No failed entries found to update", "Status Check Complete", 3);
    }
    
    return fixedCount;
  } catch (e) {
    Logger.log("Error in correctFailedStatusCodes: " + e.message);
    return 0;
  }
}

// Force fix status codes function - more aggressive approach
function forceFixStatusCodes() {
  try {
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    const dataRange = sheet.getDataRange();
    const values = dataRange.getValues();
    let fixedCount = 0;
    
    // Start from row 3 (index 2) since row 2 is header
    for (let i = 2; i < values.length; i++) {
      // Skip empty rows
      if (!values[i][0] && !values[i][1]) continue;
      
      const status = values[i][2]; // Status is in column C (index 2)
      
      if (status === "Failed") {
        // These are the specific column indices
        const statusCodeCell = sheet.getRange(i+1, 28); // AB column (index 27 + 1)
        const messageCell = sheet.getRange(i+1, 29);    // AC column (index 28 + 1)
        const fullMessageCell = sheet.getRange(i+1, 30); // AD column (index 29 + 1)
        
        // Force conversion to string "404" to avoid number formatting issues
        statusCodeCell.setValue("404");
        messageCell.setValue("Not found – The requested endpoint was not found");
        fullMessageCell.setValue("No data available for this company");
        
        fixedCount++;
      }
    }
    
    // Show a toast message with the results
    if (fixedCount > 0) {
      SpreadsheetApp.getActiveSpreadsheet().toast(`Forcefully fixed ${fixedCount} failed entries`, "Status Codes Updated", 5);
    } else {
      SpreadsheetApp.getActiveSpreadsheet().toast("No failed entries found to update", "Status Check Complete", 3);
    }
    
    return fixedCount;
  } catch (e) {
    Logger.log("Error in forceFixStatusCodes: " + e.message);
    return 0;
  }
}