// Enhanced Lusha Enrichment Script with Contact Validation Fixes
// Google Apps Script for Lusha Contact Enrichment

// Function to create the custom menu when the spreadsheet opens
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Lusha Enrichment')
    .addItem('Enrich All Contacts', 'enrichAllContacts')
    .addItem('Enrich From Specific Row', 'enrichFromSpecificRow')
    .addSeparator()
    .addItem('🔍 Diagnose Contact Data', 'diagnoseContactData')
    .addItem('🧪 Test API with Working Example', 'testWithWorkingExample')
    .addToUi();
    
  // Set up just the status row and input headers - NOT the output headers
  setupInitialStructure();
}

// Function to set up the initial spreadsheet structure
function setupInitialStructure() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  // Set up status row (row 1)
  setupStatusRow(sheet);
  
  // Set up input headers (row 2)
  setupInputHeaders(sheet);
  
  // Format the sheet for better usability
  sheet.setFrozenRows(2);  // Freeze both header rows
  
  // Set overflow text wrapping for all columns (better display)
  try {
    const lastCol = sheet.getLastColumn();
    if (lastCol > 0) {
      // Apply overflow to all columns in rows 1 and 2
      sheet.getRange(1, 1, 2, Math.max(lastCol, 70))
           .setWrapStrategy(SpreadsheetApp.WrapStrategy.OVERFLOW);
    }
  } catch (error) {
    Logger.log(`Error setting initial text wrapping: ${error.message}`);
  }
}

// Function to set up the status row (row 1)
function setupStatusRow(sheet) {
  // Check if status row already exists
  const hasStatusLabel = sheet.getRange("A1").getValue() === "Enrichment Status";
  
  if (!hasStatusLabel) {
    // Set up simplified status area in row 1
    sheet.getRange("A1").setValue("Enrichment Status");
    sheet.getRange("A1").setFontWeight("bold");
    
    sheet.getRange("C1").setValue("Last Updated:");
    sheet.getRange("C1").setFontWeight("bold");
    
    // Move final stats to start from column G
    sheet.getRange("G1").setValue("Final Stats:");
    sheet.getRange("G1").setFontWeight("bold");
    
    // Set placeholder values
    sheet.getRange("B1").setValue("Not started");
    sheet.getRange("D1").setValue("-");
    
    // Set placeholder values for final stats starting at column H
    sheet.getRange("H1").setValue("Success: -");
    sheet.getRange("I1").setValue("No Data: -");
    sheet.getRange("J1").setValue("Failed: -");
    
    // Format the status row
    sheet.getRange("A1:J1").setBackground("#f3f3f3");
  }
}

// Function to set up input headers in row 2 (columns A-F)
function setupInputHeaders(sheet) {
  const inputHeaders = [
    "First Name (Input)", 
    "Last Name (Input)", 
    "Company Name (Input)", 
    "Company Domain (Input)", 
    "Email Address (Input)", 
    "LinkedIn URL (Input)"
  ];
  
  // Check if headers already exist in row 2
  const existingHeaders = sheet.getRange("A2:F2").getValues()[0];
  const hasHeaders = existingHeaders.some(header => header !== "");
  
  // If no headers exist, add them to row 2
  if (!hasHeaders) {
    sheet.getRange("A2:F2").setValues([inputHeaders]);
    sheet.getRange("A2:F2").setFontWeight("bold");
    sheet.getRange("A2:F2").setBackground("#f3f3f3");
    
    // Auto-resize columns for better visibility
    sheet.autoResizeColumns(1, 6);
  }
}

// Function to set up output headers in row 2 (starting at column G)
function setupOutputHeaders(sheet) {
  const outputHeaders = [
    "Status", "Is Credit Charged", "Person ID", "First Name", "Last Name", "Full Name", 
    "Email 1", "Email Type 1", "Email Confidence 1", 
    "Email 2", "Email Type 2", "Email Confidence 2",
    "Phone Number 1", "Phone Type 1", "Do Not Call 1", 
    "Phone Number 2", "Phone Type 2", "Do Not Call 2",
    "Contact Tags", 
    "Contact Location Country", "Contact Location Country ISO2", 
    "Contact Location Continent", "Contact Location City", 
    "Contact Location State", "Contact Location State Code",
    "Contact Location Coordinates (Latitude)", "Contact Location Coordinates (Longitude)", 
    "Is EU Contact", "Job Title", "Job Departments", 
    "Seniority Level", "LinkedIn Profile", 
    "Company Name", "Company Description", "Company Homepage",
    "Company Location", "Company Location City", "Company Location State", 
    "Company Location State Code", "Company Location Country", 
    "Company Location Country ISO2", "Company Location Continent", 
    "Company Location Coordinates (Latitude)", "Company Location Coordinates (Longitude)", 
    "Company Size Min", "Company Size Max", 
    "Revenue Range Min", "Revenue Range Max", 
    "Company Logo URL", "Company LinkedIn", "Company Crunchbase", 
    "Technologies", "Funding Rounds (String)", "Total Funding Rounds", 
    "Total Funding Amount", "Is IPO", "Last Funding Type", 
    "Last Funding Amount", "Last Funding Date", 
    "Intent Topics (String)", "Specialities", 
    "Previous Job Title", "Previous Company Name", "Previous Company Domain"
  ];
  
  // Check if headers already exist and are correct
  const existingHeaders = sheet.getRange(2, 7, 1, outputHeaders.length).getValues()[0];
  if (existingHeaders.some((header, i) => header !== outputHeaders[i])) {
    sheet.getRange(2, 7, 1, outputHeaders.length).setValues([outputHeaders]);
    sheet.getRange(2, 7, 1, outputHeaders.length).setFontWeight("bold");
    sheet.getRange(2, 7, 1, outputHeaders.length).setBackground("#f3f3f3");
  }
}

// Function to update status in the first row
function updateStatusInTopRow(message, processed, total) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  // Ensure status row exists
  setupStatusRow(sheet);
  
  // Update only status and timestamp - no progress or processed counts
  sheet.getRange("B1").setValue(message);
  
  // Format date and time with both date and time
  sheet.getRange("D1").setValue(Utilities.formatDate(
    new Date(), 
    Session.getScriptTimeZone(), 
    "yyyy-MM-dd HH:mm:ss"
  ));
  
  // Set status background
  if (message.includes("Complete")) {
    sheet.getRange("B1").setBackground("#d9ead3"); // Green for complete
  } else if (message.includes("Error")) {
    sheet.getRange("B1").setBackground("#f4cccc"); // Red for error
  } else {
    sheet.getRange("B1").setBackground("#fff2cc"); // Yellow for in progress
  }
}

// Function to update final statistics (now starting at column H)
function updateFinalStats(success, nodata, failed) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  // Set final statistics in row 1, starting at column H
  sheet.getRange("H1").setValue("Success: " + success);
  sheet.getRange("I1").setValue("No Data: " + nodata);
  sheet.getRange("J1").setValue("Failed: " + failed);
  
  // Format stats
  sheet.getRange("H1:J1").setBackground("#e8f4fe");
}

// Trigger to enrich all contacts
function enrichAllContacts() {
  startEnrichment(true);
}

// Function to enrich from a specific row
function enrichFromSpecificRow() {
  const ui = SpreadsheetApp.getUi();
  const response = ui.prompt(
    'Enrich From Specific Row',
    'Enter the row number to start enrichment from:',
    ui.ButtonSet.OK_CANCEL
  );
  
  // Check if the user clicked "OK"
  if (response.getSelectedButton() == ui.Button.OK) {
    const rowNumber = parseInt(response.getResponseText());
    
    // Validate that it's a valid row number (must be >= 3 now that headers are in row 2)
    if (isNaN(rowNumber) || rowNumber < 3) {
      ui.alert('Invalid row number. Please enter a number greater than or equal to 3.');
      return;
    }
    
    // Call the enrichment function with the specific row number
    startEnrichment(true, rowNumber);
  }
}

// Main function to start enrichment process
function startEnrichment(processAll, customStartRow) {
  // Clear any previous enrichment state to ensure a fresh start
  PropertiesService.getScriptProperties().deleteProperty('enrichment_state');
  
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  // Set up the output headers now (only when enrichment is actually starting)
  setupOutputHeaders(sheet);
  
  // Get sheet and calculate total rows to process
  const lastRow = sheet.getLastRow();
  const startRow = customStartRow || 3; // Start from row 3 by default (after headers)
  const totalRowsToProcess = lastRow < 3 ? 0 : (lastRow - startRow + 1);
  
  // Initialize the status area
  updateStatusInTopRow("In progress: Starting enrichment process...", 0, totalRowsToProcess);
  
  // Create and store enrichment state
  const state = {
    processAll: processAll,
    startRow: startRow,
    chunkSize: 1000,  // Process data in chunks of 1000 rows at a time
    batchSize: 100,   // Lusha API batch size - 100 contacts per request for optimal speed
    totalRowsToProcess: totalRowsToProcess, // Store total rows for progress tracking
    stats: {
      processed: 0,
      success: 0,
      nodata: 0,
      failed: 0,
      batches: 0
    }
  };
  
  // Save state
  saveState(state);
  
  // Display toast notification to inform user
  SpreadsheetApp.getActiveSpreadsheet().toast(
    "Enrichment process has started. You can continue working while the process runs in the background. Check the status in row 1 for updates.",
    "Process Started",
    10 // Show for 10 seconds
  );
  
  // If no data to process, complete immediately
  if (totalRowsToProcess <= 0) {
    updateStatusInTopRow("Complete: No data to process", 0, 0);
    updateFinalStats(0, 0, 0);
    return;
  }
  
  // Start the first chunk processing
  processNextChunk();
}

// Function to save current state to script properties
function saveState(state) {
  PropertiesService.getScriptProperties().setProperty(
    'enrichment_state', 
    JSON.stringify(state)
  );
}

// Function to retrieve state from script properties
function getState() {
  const stateJson = PropertiesService.getScriptProperties().getProperty('enrichment_state');
  return stateJson ? JSON.parse(stateJson) : null;
}

// Function to process the next chunk of data
function processNextChunk() {
  const state = getState();
  if (!state) return;
  
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  // DEBUG: Log what we're doing
  Logger.log("Processing next chunk...");
  Logger.log("Current processed count: " + state.stats.processed);
  
  // IMPORTANT: Get the last row with any data
  const lastRow = sheet.getLastRow();
  Logger.log("Last row with any data: " + lastRow);
  
  // Check if we've processed all rows
  if (state.currentChunkStart && state.currentChunkStart > lastRow) {
    Logger.log("All rows processed. Current chunk start: " + state.currentChunkStart + ", Last row: " + lastRow);
    completeProcessing(state);
    return;
  }
  
  // Determine the current chunk boundaries
  const chunkStart = state.currentChunkStart || state.startRow;
  const chunkEnd = Math.min(chunkStart + state.chunkSize - 1, lastRow);
  
  Logger.log("Processing chunk from row " + chunkStart + " to " + chunkEnd);
  
  // Update status
  updateStatusInTopRow(
    `In progress: Processing rows ${chunkStart} to ${chunkEnd}`,
    state.stats.processed,
    state.totalRowsToProcess
  );
  
  // Process this chunk
  processChunk(chunkStart, chunkEnd);
  
  // Update state for next chunk
  state.currentChunkStart = chunkEnd + 1;
  saveState(state);
  
  // Log the updated state
  Logger.log("After processing chunk, processed count is now: " + state.stats.processed);
  
  // Schedule the next chunk processing
  if (chunkEnd < lastRow) {
    processNextChunk();
  } else {
    completeProcessing(state);
  }
}

// Function to process a chunk of data
function processChunk(startRow, endRow) {
  const state = getState();
  if (!state) return;
  
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  // Get the API key
  const API_KEY = PropertiesService.getScriptProperties().getProperty('api_key');
  if (!API_KEY) {
    throw new Error("API key not found in Script Properties. Please set it under Project Settings with the key 'api_key'.");
  }
  
  // Get data for this chunk
  const dataRange = sheet.getRange(startRow, 1, endRow - startRow + 1, 6);
  const data = dataRange.getValues();
  
  // DEBUG: Log the data we're attempting to process
  Logger.log("Processing rows from " + startRow + " to " + endRow);
  Logger.log("Number of rows in data: " + data.length);
  
  // Get statuses if needed
  let statuses = [];
  if (!state.processAll) {
    statuses = sheet.getRange(startRow, 7, endRow - startRow + 1, 1).getValues().flat();
    Logger.log("Not processing all - number of status values: " + statuses.length);
  } else {
    Logger.log("Processing all rows regardless of status");
  }
  
  // Collect contacts for this chunk with improved validation
  const validContacts = [];
  data.forEach((row, index) => {
    const rowIndex = startRow + index;
    const [firstName, lastName, companyName, companyDomain, emailAddress, linkedinUrl] = row;
    
    // Skip if already successful and not processing all
    if (!state.processAll && statuses[index] === "Success") {
      Logger.log("Skipping row " + rowIndex + " because status is Success");
      return;
    }
    
    // Check if this row has ANY data at all
    const hasAnyData = firstName || lastName || companyName || companyDomain || emailAddress || linkedinUrl;
    
    if (hasAnyData) {
      // Let processBatch handle the detailed validation
      Logger.log("Row " + rowIndex + " has data and will be processed");
      validContacts.push({
        rowIndex: rowIndex,
        data: row
      });
    } else {
      // Completely empty row
      Logger.log("Row " + rowIndex + " is completely empty");
      sheet.getRange(rowIndex, 7).setValue("Failed: No data provided");
      sheet.getRange(rowIndex, 7).setFontColor("#8B0000");
      state.stats.failed++;
    }
  });
  
  Logger.log("Contacts with data found: " + validContacts.length);
  
  // Process contacts in API batches
  for (let i = 0; i < validContacts.length; i += state.batchSize) {
    const batchContacts = validContacts.slice(i, i + state.batchSize);
    
    // Update batch counter BEFORE processing
    state.stats.batches++;
    
    Logger.log("========================================");
    Logger.log(`PROCESSING BATCH #${state.stats.batches} of ${batchContacts.length} contacts`);
    Logger.log(`Rows in this batch: ${batchContacts.map(c => c.rowIndex).join(", ")}`);
    Logger.log("========================================");
    
    // Update processed count before sending to API
    state.stats.processed += batchContacts.length;
    
    // Update status with simplified message
    updateStatusInTopRow(
      `In progress: Processing batch ${state.stats.batches}`,
      state.stats.processed,
      state.totalRowsToProcess
    );
    
    // Process the batch
    const batchResult = processBatch(batchContacts, API_KEY);
    
    // Check if we hit credit limit
    if (batchResult && batchResult.outOfCredits) {
      Logger.log("⚠️ OUT OF CREDITS - Stopping enrichment");
      updateStatusInTopRow(
        `⚠️ Stopped: Out of Lusha credits at row ${batchResult.stopRow}. Please add credits and resume.`,
        state.stats.processed,
        state.totalRowsToProcess
      );
      saveState(state);
      return; // Stop processing
    }
    
    // Save state after each batch
    saveState(state);
    
    // Add delay between batches to avoid rate limiting (except for last batch)
    if (i + state.batchSize < validContacts.length) {
      Logger.log("⏱️ Waiting 1 second before next batch to avoid rate limiting...");
      Utilities.sleep(1000); // 1 second delay between batches
    }
  }
  
  // Save state after all batches in this chunk
  saveState(state);
  
  // Log final progress after this chunk
  const percentComplete = (state.stats.processed / state.totalRowsToProcess) * 100;
  Logger.log("After all batches, progress is: " + state.stats.processed + 
            " / " + state.totalRowsToProcess + " = " + percentComplete + "%");
  
  // Retry failed contacts (error code 4) in smaller batches
  retryFailedContacts(state, API_KEY);
}

// FIXED: Enhanced processBatch that filters insufficient contacts before API calls
function processBatch(contacts, apiKey) {
  if (contacts.length === 0) return;
  
  const state = getState();
  if (!state) return;
  
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const url = 'https://api.lusha.com/v2/person';
  
  // STEP 1: Filter contacts into valid and insufficient categories
  const validContacts = [];
  const insufficientContacts = [];
  
  contacts.forEach(contact => {
    const [firstName, lastName, companyName, companyDomain, emailAddress, linkedinUrl] = contact.data;
    
    // Lusha API accepts: LinkedIn URL OR Email OR (firstName AND lastName AND (companyName OR companyDomain))
    const hasLinkedIn = linkedinUrl && linkedinUrl.toString().trim() !== "";
    const hasEmail = emailAddress && emailAddress.toString().trim() !== "";
    const hasName = (firstName && firstName.toString().trim() !== "") || (lastName && lastName.toString().trim() !== "");
    const hasCompany = (companyName && companyName.toString().trim() !== "") || (companyDomain && companyDomain.toString().trim() !== "");
    
    const hasStrongIdentifier = hasLinkedIn || hasEmail || (hasName && hasCompany);
    
    if (hasStrongIdentifier) {
      validContacts.push(contact);
    } else {
      // Insufficient data for Lusha API
      insufficientContacts.push(contact);
    }
  });
  
  Logger.log(`Contact filtering: ${validContacts.length} valid, ${insufficientContacts.length} insufficient`);
  
  // STEP 2: Immediately mark insufficient contacts as failed (don't send to API)
  insufficientContacts.forEach(contact => {
    const rowIndex = contact.rowIndex;
    const [firstName, lastName, companyName, companyDomain] = contact.data;
    const fullName = [firstName, lastName].filter(Boolean).join(" ");
    const company = companyName || companyDomain || "";
    
    const dataList = [];
    if (fullName) dataList.push(`name: ${fullName}`);
    if (company) dataList.push(`company: ${company}`);
    
    sheet.getRange(rowIndex, 7).setValue(
      `Failed: Insufficient data - Lusha requires: (Email OR LinkedIn) OR (Name AND Company) - (currently have: ${dataList.join(', ') || 'no data'})`
    );
    sheet.getRange(rowIndex, 7).setFontColor("#8B0000");
    state.stats.failed++;
    
    Logger.log(`Row ${rowIndex}: Marked as insufficient - has ${dataList.join(', ')} but needs more data`);
  });
  
  // STEP 3: Process valid contacts if any exist
  if (validContacts.length > 0) {
    // Separate valid contacts into two groups: with company info and without
    const contactsWithCompany = [];
    const contactsWithoutCompany = [];
    
    validContacts.forEach(contact => {
      const [firstName, lastName, companyName, companyDomain, emailAddress, linkedinUrl] = contact.data;
      
      // Check if we have any company information
      if (companyName || companyDomain) {
        contactsWithCompany.push(contact);
      } else {
        contactsWithoutCompany.push(contact);
      }
    });
    
    Logger.log(`Valid contact split: ${contactsWithCompany.length} with company, ${contactsWithoutCompany.length} without company`);
    
    // Log row indices for debugging
    if (contactsWithCompany.length > 0) {
      Logger.log(`Rows WITH company: ${contactsWithCompany.map(c => c.rowIndex).join(", ")}`);
    }
    if (contactsWithoutCompany.length > 0) {
      Logger.log(`Rows WITHOUT company: ${contactsWithoutCompany.map(c => c.rowIndex).join(", ")}`);
    }
    
    // Process contacts with company info first
    if (contactsWithCompany.length > 0) {
      const result = processBatchWithCompanyInfo(contactsWithCompany, apiKey, url, sheet, state);
      if (result && result.outOfCredits) {
        return result; // Propagate out of credits signal
      }
    }
    
    // Process contacts without company info separately
    if (contactsWithoutCompany.length > 0) {
      const result = processBatchWithoutCompanyInfo(contactsWithoutCompany, apiKey, url, sheet, state);
      if (result && result.outOfCredits) {
        return result; // Propagate out of credits signal
      }
    }
  } else {
    Logger.log("No valid contacts to send to API in this batch");
  }
  
  // Save updated state after processing
  saveState(state);
  
  return null; // No issues
}

// Function to retry failed contacts (error code 4) in smaller batches
function retryFailedContacts(state, apiKey) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const lastRow = sheet.getLastRow();
  
  if (lastRow < 3) return; // No data rows
  
  Logger.log("========================================");
  Logger.log("🔄 CHECKING FOR FAILED CONTACTS TO RETRY");
  Logger.log("========================================");
  
  // Get all status cells
  const statusRange = sheet.getRange(3, 7, lastRow - 2, 1);
  const statuses = statusRange.getValues().flat();
  
  // Get all input data
  const inputRange = sheet.getRange(3, 1, lastRow - 2, 6);
  const inputData = inputRange.getValues();
  
  // Find contacts with error code 4
  const failedContacts = [];
  statuses.forEach((status, index) => {
    const statusStr = String(status);
    // Check if this is an error code 4 failure
    if (statusStr.includes("Code 4") || statusStr.includes("code 4")) {
      const rowIndex = index + 3;
      const row = inputData[index];
      const [firstName, lastName, companyName, companyDomain, emailAddress, linkedinUrl] = row;
      
      // Check if we have at least some data to retry with
      if (firstName || lastName || companyDomain || emailAddress || linkedinUrl) {
        failedContacts.push({
          rowIndex: rowIndex,
          data: row
        });
        Logger.log(`Found failed contact at row ${rowIndex}: ${firstName} ${lastName} - ${companyDomain}`);
      }
    }
  });
  
  if (failedContacts.length === 0) {
    Logger.log("✅ No failed contacts to retry");
    return;
  }
  
  Logger.log(`🔄 Found ${failedContacts.length} failed contacts to retry in smaller batches`);
  
  // Retry in very small batches of 2 (Lusha works better with tiny batches for difficult lookups)
  const retryBatchSize = 2;
  let retriedCount = 0;
  let retrySuccessCount = 0;
  
  for (let i = 0; i < failedContacts.length; i += retryBatchSize) {
    const retryBatch = failedContacts.slice(i, i + retryBatchSize);
    
    Logger.log(`========================================`);
    Logger.log(`🔄 RETRY BATCH ${Math.floor(i / retryBatchSize) + 1} - ${retryBatch.length} contacts`);
    Logger.log(`Rows: ${retryBatch.map(c => c.rowIndex).join(", ")}`);
    Logger.log(`========================================`);
    
    // Update status to show retry
    updateStatusInTopRow(
      `🔄 Retrying ${failedContacts.length} failed contacts (batch ${Math.floor(i / retryBatchSize) + 1})`,
      state.stats.processed,
      state.totalRowsToProcess
    );
    
    // Process the retry batch
    const beforeFailed = state.stats.failed;
    processBatch(retryBatch, apiKey);
    const afterFailed = state.stats.failed;
    
    // Calculate how many succeeded in this retry
    const retrySuccess = beforeFailed - afterFailed;
    if (retrySuccess > 0) {
      retrySuccessCount += retrySuccess;
      Logger.log(`✅ Retry batch recovered ${retrySuccess} contacts!`);
    }
    
    retriedCount += retryBatch.length;
    
    // Small delay between retry batches
    if (i + retryBatchSize < failedContacts.length) {
      Logger.log("⏱️ Waiting 2 seconds before next retry batch...");
      Utilities.sleep(2000);
    }
  }
  
  Logger.log(`========================================`);
  Logger.log(`🔄 RETRY COMPLETE: ${retrySuccessCount}/${retriedCount} recovered`);
  Logger.log(`========================================`);
  
  saveState(state);
}

// Function to clean and validate email address
function cleanAndValidateEmail(email) {
  if (!email) return { cleaned: "", isValid: true, error: null };
  
  const cleaned = email.toString().trim().toLowerCase();
  
  // Check for basic email format
  const emailRegex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
  if (!emailRegex.test(cleaned)) {
    return { cleaned: "", isValid: false, error: "Invalid email format" };
  }
  
  // Check for common issues
  if (cleaned.includes('..')) {
    return { cleaned: "", isValid: false, error: "Email contains consecutive dots" };
  }
  
  if (cleaned.startsWith('.') || cleaned.startsWith('@')) {
    return { cleaned: "", isValid: false, error: "Email starts with invalid character" };
  }
  
  if (cleaned.endsWith('.') || cleaned.endsWith('@')) {
    return { cleaned: "", isValid: false, error: "Email ends with invalid character" };
  }
  
  return { cleaned: cleaned, isValid: true, error: null };
}

// Function to clean and validate domain
function cleanAndValidateDomain(domain) {
  if (!domain) return { cleaned: "", isValid: true, error: null };
  
  let cleaned = domain.toString().trim().toLowerCase();
  
  // If it's empty after trimming, return empty
  if (cleaned === "") return { cleaned: "", isValid: true, error: null };
  
  // Remove common prefixes
  cleaned = cleaned.replace(/^(https?:\/\/)?(www\.)?/i, '');
  
  // Remove trailing slashes and paths
  cleaned = cleaned.split('/')[0];
  
  // Basic validation - just check for obvious issues
  // Must contain at least one dot
  if (!cleaned.includes('.')) {
    return { cleaned: cleaned, isValid: false, error: "Domain missing TLD (e.g., .com)" };
  }
  
  // Check for spaces (obvious error)
  if (cleaned.includes(' ')) {
    return { cleaned: cleaned, isValid: false, error: "Domain contains spaces" };
  }
  
  // Must have something before and after the dot
  const parts = cleaned.split('.');
  if (parts.some(part => part.length === 0)) {
    return { cleaned: cleaned, isValid: false, error: "Invalid domain structure" };
  }
  
  // Return cleaned domain - let the API do final validation
  return { cleaned: cleaned, isValid: true, error: null };
}

// Function to clean and validate LinkedIn URL
function cleanAndValidateLinkedIn(url) {
  if (!url) return { cleaned: "", isValid: true, error: null };
  
  const cleaned = url.toString().trim();
  
  // Check if it's a LinkedIn URL
  if (!cleaned.toLowerCase().includes('linkedin.com')) {
    return { cleaned: "", isValid: false, error: "Not a LinkedIn URL" };
  }
  
  // Ensure it has proper format
  if (!cleaned.match(/^https?:\/\//i)) {
    return { cleaned: "", isValid: false, error: "LinkedIn URL missing protocol (http/https)" };
  }
  
  return { cleaned: cleaned, isValid: true, error: null };
}

// Function to process contacts that have company information - ENHANCED WITH VALIDATION
function processBatchWithCompanyInfo(contacts, apiKey, url, sheet, state) {
  const batchData = [];
  const rowMap = {};
  const invalidContacts = [];
  
  Logger.log("Processing batch WITH company info: " + contacts.length + " contacts");
  
  // Prepare the contacts for the API request with validation
  contacts.forEach(contact => {
    const [firstName, lastName, companyName, companyDomain, emailAddress, linkedinUrl] = contact.data;
    
    // Clean and validate each field
    const emailResult = cleanAndValidateEmail(emailAddress);
    const domainResult = cleanAndValidateDomain(companyDomain);
    const linkedInResult = cleanAndValidateLinkedIn(linkedinUrl);
    
    // Check for validation errors
    const validationErrors = [];
    if (!emailResult.isValid && emailAddress) {
      validationErrors.push(`Email: ${emailResult.error}`);
    }
    if (!domainResult.isValid && companyDomain) {
      validationErrors.push(`Domain: ${domainResult.error}`);
    }
    if (!linkedInResult.isValid && linkedinUrl) {
      validationErrors.push(`LinkedIn: ${linkedInResult.error}`);
    }
    
    // If there are validation errors, mark contact as failed
    if (validationErrors.length > 0) {
      Logger.log(`Row ${contact.rowIndex}: Data validation failed - ${validationErrors.join(', ')}`);
      invalidContacts.push({
        rowIndex: contact.rowIndex,
        errors: validationErrors
      });
      return;
    }
    
    const fullName = [firstName, lastName].filter(Boolean).join(" ").trim();
    
    // Clean and validate company data
    const cleanCompanyName = companyName ? companyName.toString().trim() : "";
    const cleanCompanyDomain = domainResult.cleaned || "";
    
    // Only include companies array if we have valid company data
    // IMPORTANT: Include both name and domain even if one is empty (matching your working example)
    const companies = [];
    if (cleanCompanyName || cleanCompanyDomain) {
      companies.push({ 
        name: cleanCompanyName, 
        domain: cleanCompanyDomain, 
        isCurrent: true 
      });
    }
    
    // Build payload matching your working example structure EXACTLY
    // IMPORTANT: Include email and linkedinUrl even if empty (API may require these fields)
    let contactPayload = {
      contactId: String(contact.rowIndex),
      fullName: fullName || "",
      linkedinUrl: linkedInResult.cleaned || "",
      email: emailResult.cleaned || ""
    };
    
    // Add companies if available
    if (companies.length > 0) {
      contactPayload.companies = companies;
    }
    
    // Final validation check
    const validationIssues = validateContactPayload(contactPayload, contact.rowIndex);
    if (validationIssues.length > 0) {
      Logger.log(`WARNING: Contact ${contact.rowIndex} has validation issues: ${validationIssues.join(', ')}`);
    }
    
    batchData.push(contactPayload);
    rowMap[contact.rowIndex] = contact.rowIndex;
  });
  
  // Mark invalid contacts immediately
  invalidContacts.forEach(contact => {
    sheet.getRange(contact.rowIndex, 7).setValue(`Failed: Data validation error - ${contact.errors.join('; ')}`);
    sheet.getRange(contact.rowIndex, 7).setFontColor("#8B0000");
    state.stats.failed++;
  });
  
  // Send the batch request for valid contacts
  if (batchData.length > 0) {
    const result = sendBatchRequest(batchData, rowMap, contacts.filter(c => !invalidContacts.find(ic => ic.rowIndex === c.rowIndex)), apiKey, url, sheet, state);
    if (result && result.outOfCredits) {
      return result; // Propagate out of credits signal
    }
  }
  
  saveState(state);
  return null; // No issues
}

// Function to process contacts that don't have company information - ENHANCED WITH VALIDATION
function processBatchWithoutCompanyInfo(contacts, apiKey, url, sheet, state) {
  const batchData = [];
  const rowMap = {};
  const invalidContacts = [];
  
  Logger.log("Processing batch WITHOUT company info: " + contacts.length + " contacts");
  
  // Prepare the contacts for the API request (without companies array)
  contacts.forEach(contact => {
    const [firstName, lastName, companyName, companyDomain, emailAddress, linkedinUrl] = contact.data;
    
    // Clean and validate each field
    const emailResult = cleanAndValidateEmail(emailAddress);
    const linkedInResult = cleanAndValidateLinkedIn(linkedinUrl);
    
    // Check for validation errors
    const validationErrors = [];
    if (!emailResult.isValid && emailAddress) {
      validationErrors.push(`Email: ${emailResult.error}`);
    }
    if (!linkedInResult.isValid && linkedinUrl) {
      validationErrors.push(`LinkedIn: ${linkedInResult.error}`);
    }
    
    // If there are validation errors, mark contact as failed
    if (validationErrors.length > 0) {
      Logger.log(`Row ${contact.rowIndex}: Data validation failed - ${validationErrors.join(', ')}`);
      invalidContacts.push({
        rowIndex: contact.rowIndex,
        errors: validationErrors
      });
      return;
    }
    
    const fullName = [firstName, lastName].filter(Boolean).join(" ").trim();
    
    // Build payload matching your working example structure EXACTLY
    // IMPORTANT: Include email and linkedinUrl even if empty (API may require these fields)
    let contactPayload = {
      contactId: String(contact.rowIndex),
      fullName: fullName || "",
      linkedinUrl: linkedInResult.cleaned || "",
      email: emailResult.cleaned || ""
    };
    
    // CRITICAL: Don't include an empty companies array - omit it entirely
    // This prevents API errors when processing contacts without company info
    
    // Validate the contact payload
    const validationIssues = validateContactPayload(contactPayload, contact.rowIndex);
    if (validationIssues.length > 0) {
      Logger.log(`WARNING: Contact ${contact.rowIndex} has validation issues: ${validationIssues.join(', ')}`);
    }
    
    batchData.push(contactPayload);
    rowMap[contact.rowIndex] = contact.rowIndex;
  });
  
  // Mark invalid contacts immediately
  invalidContacts.forEach(contact => {
    sheet.getRange(contact.rowIndex, 7).setValue(`Failed: Data validation error - ${contact.errors.join('; ')}`);
    sheet.getRange(contact.rowIndex, 7).setFontColor("#8B0000");
    state.stats.failed++;
  });
  
  // Send the batch request for valid contacts
  if (batchData.length > 0) {
    const result = sendBatchRequest(batchData, rowMap, contacts.filter(c => !invalidContacts.find(ic => ic.rowIndex === c.rowIndex)), apiKey, url, sheet, state);
    if (result && result.outOfCredits) {
      return result; // Propagate out of credits signal
    }
  }
  
  saveState(state);
  return null; // No issues
}

// ENHANCED sendBatchRequest function with comprehensive error handling and debugging
function sendBatchRequest(batchData, rowMap, contacts, apiKey, url, sheet, state) {
  // Log the request payload for debugging
  const requestPayload = { contacts: batchData };
  
  // Get current batch number from state (already incremented before this function is called)
  const batchNumber = state.stats.batches;
  
  // Log first few contacts for debugging (don't log all to avoid quota issues)
  Logger.log("=== API REQUEST DEBUG ===");
  Logger.log("Batch Number: " + batchNumber);
  Logger.log("Number of contacts in this API call: " + batchData.length);
  Logger.log("Sample contact payload (first contact): " + JSON.stringify(batchData[0], null, 2));
  if (batchData.length > 1) {
    Logger.log("Sample contact payload (second contact): " + JSON.stringify(batchData[1], null, 2));
  }
  if (batchData.length > 2) {
    Logger.log("Sample contact payload (last contact): " + JSON.stringify(batchData[batchData.length - 1], null, 2));
  }
  Logger.log("Row indices in this API call: " + contacts.map(c => c.rowIndex).join(", "));
  
  // CRITICAL: Log the full request body for the first batch to compare with working example
  if (batchNumber === 1) {
    Logger.log("=== FULL BATCH #1 REQUEST (first 3 contacts) ===");
    Logger.log(JSON.stringify({ contacts: batchData.slice(0, 3) }, null, 2));
  }
  
  Logger.log("API URL: " + url);
  Logger.log("Has API key: " + (apiKey ? "Yes" : "No"));
  
  const options = {
    method: 'POST',
    contentType: 'application/json',
    headers: { 'api_key': apiKey },
    muteHttpExceptions: true,
    payload: JSON.stringify(requestPayload)
  };
  
  try {
    Logger.log("Sending API request with " + batchData.length + " contacts");
    const response = UrlFetchApp.fetch(url, options);
    const statusCode = response.getResponseCode();
    const responseText = response.getContentText();
    
    Logger.log("=== API RESPONSE DEBUG ===");
    Logger.log("HTTP Status Code: " + statusCode);
    Logger.log("Response Headers: " + JSON.stringify(response.getAllHeaders()));
    Logger.log("Response Body (first 1000 chars): " + responseText.substring(0, 1000));
    
    // Enhanced error handling for HTTP status codes
    if (statusCode < 200 || statusCode >= 300) {
      let errorMessage = `API Error (HTTP ${statusCode}): `;
      let detailedError = "";
      
      try {
        // Try to parse the error response as JSON
        const errorResponse = JSON.parse(responseText);
        Logger.log("Parsed error response: " + JSON.stringify(errorResponse, null, 2));
        
        // Handle different possible error response structures
        if (errorResponse.error) {
          // Structure: { error: { message, code, details } }
          detailedError = errorResponse.error.message || "No error message provided";
          if (errorResponse.error.code) {
            detailedError += ` (Code: ${errorResponse.error.code})`;
          }
          if (errorResponse.error.details) {
            detailedError += ` - Details: ${errorResponse.error.details}`;
          }
        } else if (errorResponse.errors && Array.isArray(errorResponse.errors)) {
          // Structure: { errors: [{ message, code }] }
          detailedError = errorResponse.errors.map(err => 
            (err.message || "Unknown error") + (err.code ? ` (${err.code})` : "")
          ).join("; ");
        } else if (errorResponse.message) {
          // Structure: { message: "error message" }
          detailedError = errorResponse.message;
        } else if (errorResponse.detail) {
          // Structure: { detail: "error message" }
          detailedError = errorResponse.detail;
        } else if (typeof errorResponse === 'string') {
          // Response is just a string
          detailedError = errorResponse;
        } else {
          // Unknown structure - log the keys and provide what we can
          const keys = Object.keys(errorResponse);
          Logger.log("Unknown error response structure. Keys: " + keys.join(", "));
          detailedError = `Unknown error structure. Available fields: ${keys.join(", ")}`;
          
          // Try to extract any field that might contain error info
          for (const key of keys) {
            if (typeof errorResponse[key] === 'string' && errorResponse[key].length > 0) {
              detailedError += `. ${key}: ${errorResponse[key]}`;
              break;
            }
          }
        }
        
        errorMessage += detailedError;
        
      } catch (parseError) {
        // If response is not valid JSON, use the raw response text
        Logger.log("Failed to parse error response as JSON: " + parseError.message);
        errorMessage += `Raw response: ${responseText}`;
        
        // Also try to extract common error patterns from non-JSON responses
        if (responseText.toLowerCase().includes('unauthorized')) {
          errorMessage += " (Check your API key)";
        } else if (responseText.toLowerCase().includes('rate limit')) {
          errorMessage += " (Rate limit exceeded)";
        } else if (responseText.toLowerCase().includes('quota')) {
          errorMessage += " (Quota exceeded)";
        }
      }
      
      // Log the final error message
      Logger.log(`Final error message: ${errorMessage}`);
      
      // Mark all contacts in this batch as failed
      contacts.forEach(contact => {
        const rowIndex = contact.rowIndex;
        sheet.getRange(rowIndex, 7).setValue(errorMessage);
        sheet.getRange(rowIndex, 7).setFontColor("#8B0000");
        state.stats.failed++;
      });
      
      // Update status with error
      updateStatusInTopRow(
        `Error: ${errorMessage}`,
        state.stats.processed,
        state.totalRowsToProcess
      );
      
      saveState(state);
      return;
    }
    
    // Success case - parse response
    let responseData;
    try {
      responseData = JSON.parse(responseText);
      Logger.log("Successfully parsed response JSON");
    } catch (parseError) {
      const errorMessage = `Failed to parse successful API response: ${parseError.message}`;
      Logger.log(errorMessage);
      Logger.log(`Response that failed to parse: ${responseText.substring(0, 500)}...`);
      
      // Mark all contacts as failed
      contacts.forEach(contact => {
        const rowIndex = contact.rowIndex;
        sheet.getRange(rowIndex, 7).setValue(`Failed: ${errorMessage}`);
        sheet.getRange(rowIndex, 7).setFontColor("#8B0000");
        state.stats.failed++;
      });
      
      saveState(state);
      return;
    }
    
    // Validate response structure
    if (!responseData.contacts) {
      const errorMessage = "API response missing 'contacts' field";
      Logger.log(errorMessage);
      Logger.log("Response structure: " + JSON.stringify(Object.keys(responseData)));
      
      contacts.forEach(contact => {
        const rowIndex = contact.rowIndex;
        sheet.getRange(rowIndex, 7).setValue(`Failed: ${errorMessage}`);
        sheet.getRange(rowIndex, 7).setFontColor("#8B0000");
        state.stats.failed++;
      });
      
      saveState(state);
      return;
    }
    
    // Log successful response structure
    Logger.log("Response has " + Object.keys(responseData.contacts).length + " contacts");
    Logger.log("Companies in response: " + (responseData.companies ? Object.keys(responseData.companies).length : "none"));
    
    // Prepare data for batch updates to the sheet
    const updateData = [];
    const statusUpdates = [];
    let outOfCreditsFlag = false;
    let outOfCreditsRow = null;
    let outOfCreditsCount = 0; // Count how many contacts return OUT_OF_CREDIT
    
    // Process each contact in the response
    batchData.forEach(contact => {
      const rowIndex = parseInt(contact.contactId);
      const contactResponse = responseData.contacts[contact.contactId];
      
      if (!contactResponse) {
        // Missing contact in response
        const errorMsg = "Contact missing from API response";
        const emptyRow = Array(65).fill("");
        emptyRow[0] = `Failed: ${errorMsg}`;
        
        updateData.push({
          row: rowIndex,
          data: emptyRow
        });
        
        statusUpdates.push({
          row: rowIndex,
          color: "#8B0000" // Red
        });
        
        state.stats.failed++;
        return;
      }
      
      // Log individual contact response for debugging (first few only)
      if (batchData.indexOf(contact) < 3) {
        Logger.log(`Contact ${contact.contactId} response: ${JSON.stringify(contactResponse, null, 2).substring(0, 500)}`);
      }
      
      if (statusCode === 201 && contactResponse && !contactResponse.error) {
        // Success case
        const contactData = contactResponse.data || {};
        const companyData = responseData.companies[contactData.companyId] || {};
        
        // Fix company description: trim, normalize spaces, and remove line breaks
        let companyDescription = companyData.description || "";
        companyDescription = companyDescription.trim().replace(/\s+/g, ' ').replace(/[\r\n]+/g, ' ');
        
        // Prepare row data - Convert booleans to strings to avoid issues with setValues
        const rowData = [
          "Success", // Status
          contactResponse.isCreditCharged ? "TRUE" : "FALSE", // Convert boolean to string "TRUE"/"FALSE"
          contactData.personId || "", 
          contactData.firstName || "", 
          contactData.lastName || "", 
          contactData.fullName || "",
          contactData.emailAddresses?.[0]?.email || "", 
          contactData.emailAddresses?.[0]?.emailType || "", 
          contactData.emailAddresses?.[0]?.emailConfidence || "",
          contactData.emailAddresses?.[1]?.email || "", 
          contactData.emailAddresses?.[1]?.emailType || "", 
          contactData.emailAddresses?.[1]?.emailConfidence || "",
          contactData.phoneNumbers?.[0]?.number || "", 
          contactData.phoneNumbers?.[0]?.phoneType || "", 
          contactData.phoneNumbers?.[0]?.doNotCall ? "TRUE" : "FALSE", // Convert boolean
          contactData.phoneNumbers?.[1]?.number || "", 
          contactData.phoneNumbers?.[1]?.phoneType || "", 
          contactData.phoneNumbers?.[1]?.doNotCall ? "TRUE" : "FALSE", // Convert boolean
          contactData.contactTags?.map(tag => tag.name).join(", ") || "",
          contactData.location?.country || "", 
          contactData.location?.country_iso2 || "", 
          contactData.location?.continent || "",
          contactData.location?.city || "", 
          contactData.location?.state || "", 
          contactData.location?.state_code || "",
          contactData.location?.location_coordinates?.[1] || "", 
          contactData.location?.location_coordinates?.[0] || "",
          contactData.location?.is_eu_contact ? "TRUE" : "FALSE", // Convert boolean
          contactData.jobTitle?.title || "", 
          contactData.jobTitle?.departments?.join(", ") || "",
          contactData.jobTitle?.seniority || "", 
          contactData.socialLinks?.linkedin || "",
          companyData.name || "", 
          companyDescription, 
          companyData.domains?.homepage || "",
          companyData.location?.rawLocation || "", 
          companyData.location?.city || "",
          companyData.location?.state || "", 
          companyData.location?.stateCode || "",
          companyData.location?.country || "", 
          companyData.location?.countryIso2 || "",
          companyData.location?.continent || "",
          companyData.location?.locationCoordinates?.[1] || "", 
          companyData.location?.locationCoordinates?.[0] || "",
          companyData.companySize?.[0] || "", 
          companyData.companySize?.[1] || "",
          companyData.revenueRange?.[0] || "", 
          companyData.revenueRange?.[1] || "",
          companyData.logoUrl || "", 
          companyData.social?.linkedin || "", 
          companyData.social?.crunchbase || "",
          companyData.technologies?.map(tech => tech.name).join(", ") || "",
          companyData.funding?.rounds?.map(f => `${f.roundType} (${f.roundAmount} ${f.currency}, ${f.roundDate})`).join(", ") || "",
          companyData.funding?.totalRounds || "", 
          companyData.funding?.totalRoundsAmount || "",
          companyData.funding?.isIpo ? "TRUE" : "FALSE", // Convert boolean
          companyData.funding?.lastRoundType || "",
          companyData.funding?.lastRoundAmount || "", 
          companyData.funding?.lastRoundDate || "",
          companyData.intent?.detectedTopics?.map(i => `${i.topicName} (Score: ${i.metadata.topicScore}, Trend: ${i.metadata.topicTrend})`).join(", ") || "",
          companyData.specialities?.join(", ") || "",
          contactData.previousJob?.jobTitle?.title || "", 
          contactData.previousJob?.company?.name || "", 
          contactData.previousJob?.company?.domain || ""
        ];
        
        updateData.push({
          row: rowIndex,
          data: rowData
        });
        
        statusUpdates.push({
          row: rowIndex,
          color: "#006400" // Green
        });
        
        state.stats.success++;
      } else {
// Error case - Enhanced error information with debugging
let errorMsg;

if (contactResponse.error) {
  Logger.log(`Contact ${contact.contactId} error details: ${JSON.stringify(contactResponse.error)}`);
  
  // Handle different types of "no data" scenarios
  const isNoDataError = 
    contactResponse.error.message === "Could not find requested data" || 
    contactResponse.error.code === "DATA_NOT_FOUND" ||
    contactResponse.error.code === "3" ||  // Lusha's "no data found" error code
    (contactResponse.error.code === 3) ||  // In case it's a number instead of string
    contactResponse.error.message === "No data found" ||
    contactResponse.error.message === "Contact not found" ||
    (contactResponse.error.message === "" && contactResponse.error.code === "3") ||
    (contactResponse.error.message === "" && contactResponse.error.code === 3);
  
  if (isNoDataError) {
    errorMsg = "Could not find requested data";
  } else {
    // For other errors, include detailed information
    errorMsg = "Error: ";
    
    // Include error code if available
    if (contactResponse.error.code) {
      errorMsg += `[${contactResponse.error.code}] `;
    }
    
    // Include error message
    const message = contactResponse.error.message || "No error message provided";
    errorMsg += message;
    
    // Include details if available
    if (contactResponse.error.details) {
      errorMsg += ` - ${contactResponse.error.details}`;
    }
    
    // If we still have a generic error, provide more context
    if (message === "No error message provided" || message === "Unknown error" || message === "") {
      Logger.log(`Generic/empty error for contact ${contact.contactId}. Full contact response: ${JSON.stringify(contactResponse)}`);
      
      // Check if this might actually be a "no data" case based on error code
      if (contactResponse.error.code === "3" || contactResponse.error.code === 3) {
        errorMsg = "Could not find requested data";
      } else if (contactResponse.error.code === "4" || contactResponse.error.code === 4) {
        // Error code 4 typically indicates invalid or insufficient data for enrichment
        // Log the actual payload sent to API for debugging
        const sentPayload = batchData.find(c => c.contactId === contact.contactId);
        Logger.log(`=== ERROR CODE 4 DEBUG ===`);
        Logger.log(`Row ${contact.contactId}: Error code 4 received`);
        Logger.log(`Payload sent to API: ${JSON.stringify(sentPayload, null, 2)}`);
        Logger.log(`Full error response: ${JSON.stringify(contactResponse.error, null, 2)}`);
        
        // Check what data was actually provided - including empty strings
        const dataProvided = [];
        if (sentPayload) {
          if (sentPayload.email !== undefined) {
            dataProvided.push(`email: "${sentPayload.email}"`);
          }
          if (sentPayload.linkedinUrl !== undefined) {
            dataProvided.push(`LinkedIn: "${sentPayload.linkedinUrl}"`);
          }
          if (sentPayload.fullName) {
            dataProvided.push(`name: ${sentPayload.fullName}`);
          }
          if (sentPayload.companies && sentPayload.companies.length > 0) {
            const company = sentPayload.companies[0];
            dataProvided.push(`company: {name: "${company.name || ''}", domain: "${company.domain || ''}"}`);
      } else {
            dataProvided.push(`companies: MISSING`);
          }
        }
        
        errorMsg = `Error: Invalid or insufficient input data for enrichment (Code 4) - Data provided: ${dataProvided.join(', ') || 'none'}`;
        Logger.log(`Error message set to: ${errorMsg}`);
        Logger.log(`IMPORTANT: Check if payload structure matches working example`);
      } else if (contactResponse.error.code === "5" || contactResponse.error.code === 5) {
        // Error code 5 typically indicates API validation error
        errorMsg = "Error: Request validation failed (Code 5) - check data format";
        Logger.log(`Error code 5 for contact ${contact.contactId}. Input data: ${JSON.stringify(contact.data)}`);
      } else if (contactResponse.error.code === "2" || contactResponse.error.code === 2) {
        // Error code 2 = OUT_OF_CREDIT 
        // Note: Lusha sometimes returns this for individual contacts even when account has credits
        errorMsg = "⚠️ Credit not available for this contact (Code 2)";
        Logger.log(`OUT_OF_CREDIT error for contact ${contact.contactId}`);
        // Count this, but don't immediately stop - might just be this contact
        outOfCreditsCount++;
        if (!outOfCreditsRow) {
          outOfCreditsRow = contact.rowIndex;
        }
      } else if (contactResponse.error.code) {
        errorMsg = `Error: API returned error code ${contactResponse.error.code} - please check input data format and API documentation`;
        Logger.log(`Unknown error code ${contactResponse.error.code} for contact ${contact.contactId}. Input data: ${JSON.stringify(contact.data)}`);
      } else {
        errorMsg = "Error: API returned an error with no code or message - please contact support";
        Logger.log(`No error code or message for contact ${contact.contactId}. Full response: ${JSON.stringify(contactResponse)}`);
      }
    }
    
    // Check for rate limiting
    if (contactResponse.error.code === "RATE_LIMIT_EXCEEDED") {
      errorMsg += " (Consider slowing down the requests)";
    }
    
    // Check for credit issues
    if (contactResponse.error.code === "INSUFFICIENT_CREDITS") {
      errorMsg += " (Please check your account credits)";
    }
  }
} else {
  Logger.log(`Contact ${contact.contactId} failed without error object. Full response: ${JSON.stringify(contactResponse)}`);
  errorMsg = `Failed: No error details available (Contact ID: ${contact.contactId}, HTTP: ${statusCode})`;
}

const emptyRow = Array(65).fill(""); // Empty row for all columns
emptyRow[0] = errorMsg; // Set status message

updateData.push({
  row: rowIndex,
  data: emptyRow
});

// Enhanced categorization for coloring
const isNoDataCase = errorMsg === "Could not find requested data";

if (isNoDataCase) {
  statusUpdates.push({
    row: rowIndex, 
    color: "#FF8C00" // Orange for no data
  });
  state.stats.nodata++;
} else {
  statusUpdates.push({
    row: rowIndex,
    color: "#8B0000" // Red for actual errors
  });
  state.stats.failed++;
}
      }
    });
    
    // Batch update the sheet
    batchUpdateSheet(sheet, updateData, statusUpdates);
    
    // Save the updated state
    saveState(state);
    
    // Check if we should stop due to out of credits
    // Only stop if MORE THAN 50% of the batch returned OUT_OF_CREDIT
    // (Lusha sometimes returns code 2 for individual contacts even when account has credits)
    const batchSize = batchData.length;
    const outOfCreditsPercentage = (outOfCreditsCount / batchSize) * 100;
    
    Logger.log(`OUT_OF_CREDIT summary: ${outOfCreditsCount} out of ${batchSize} contacts (${outOfCreditsPercentage.toFixed(1)}%)`);
    
    if (outOfCreditsCount > 0 && outOfCreditsPercentage > 50) {
      // More than half the batch failed with OUT_OF_CREDIT - likely a real account issue
      Logger.log(`⚠️ STOPPING: More than 50% of batch returned OUT_OF_CREDIT`);
      return { outOfCredits: true, stopRow: outOfCreditsRow };
    } else if (outOfCreditsCount > 0) {
      // Some contacts failed with OUT_OF_CREDIT, but not enough to stop
      Logger.log(`ℹ️ ${outOfCreditsCount} contacts returned OUT_OF_CREDIT, but continuing (likely false positives from Lusha API)`);
    }
    
  } catch (error) {
    // Handle connection errors and other exceptions with enhanced logging
    Logger.log("=== EXCEPTION DEBUG ===");
    Logger.log("Exception type: " + error.name);
    Logger.log("Exception message: " + error.message);
    Logger.log("Exception stack: " + error.stack);
    
    const errorMessage = `Connection Error: ${error.message}`;
    Logger.log(errorMessage);
    
    contacts.forEach(contact => {
      const rowIndex = contact.rowIndex;
      sheet.getRange(rowIndex, 7).setValue(`Failed: ${errorMessage}`);
      sheet.getRange(rowIndex, 7).setFontColor("#8B0000");
      state.stats.failed++;
    });
    
    // Update status with error
    updateStatusInTopRow(
      `Error: ${errorMessage}`,
      state.stats.processed,
      state.totalRowsToProcess
    );
    
    saveState(state);
  }
}

// ENHANCED: More strict validation that matches what Lusha actually needs
function validateContactPayload(contact, rowIndex) {
  const issues = [];
  
  // Check required fields
  if (!contact.contactId) {
    issues.push("Missing contactId");
  }
  
  // Lusha API requires: LinkedIn URL OR Email OR (fullName AND (company.name OR company.domain))
  const hasEmail = contact.email && contact.email.trim() !== "";
  const hasLinkedIn = contact.linkedinUrl && contact.linkedinUrl.trim() !== "";
  const hasFullName = contact.fullName && contact.fullName.trim() !== "";
  const hasCompany = contact.companies && contact.companies.length > 0 && 
                     (contact.companies[0].name || contact.companies[0].domain);
  
  const hasStrongIdentifier = hasEmail || hasLinkedIn || (hasFullName && hasCompany);
  
  if (!hasStrongIdentifier) {
    issues.push("No strong identifier - Lusha requires: (Email OR LinkedIn) OR (Name AND Company)");
  }
  
  // Check email format if provided
  if (contact.email && !isValidEmail(contact.email)) {
    issues.push("Invalid email format");
  }
  
  // Check LinkedIn URL format if provided
  if (contact.linkedinUrl && !contact.linkedinUrl.includes('linkedin.com')) {
    issues.push("LinkedIn URL doesn't contain linkedin.com");
  }
  
  // Check companies array if provided
  if (contact.companies && contact.companies.length > 0) {
    contact.companies.forEach((company, index) => {
      if (!company.name && !company.domain) {
        issues.push(`Company ${index} missing both name and domain`);
      }
    });
  }
  
  // Only log significant validation issues
  if (issues.length > 0) {
    Logger.log(`Validation issues for row ${rowIndex}: ${issues.join(", ")}`);
  }
  
  return issues;
}

// Simple email validation function
function isValidEmail(email) {
  const emailRegex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
  return emailRegex.test(email);
}

// Updated batch update function with improved text wrapping handling
function batchUpdateSheet(sheet, rowUpdates, statusUpdates) {
  // Group updates by adjacent rows to minimize API calls
  const rowGroups = {};
  
  rowUpdates.forEach(update => {
    if (!rowGroups[update.row]) {
      rowGroups[update.row] = update.data;
    }
  });
  
  // Update data in batches
  const batchSize = 20; // Number of rows to update at once
  const rowIndices = Object.keys(rowGroups).map(Number).sort((a, b) => a - b);
  
  for (let i = 0; i < rowIndices.length; i += batchSize) {
    const batch = rowIndices.slice(i, i + batchSize);
    
    batch.forEach(rowIndex => {
      try {
        // Make sure the data array length matches the sheet's expected width
        // If the output headers have 65 columns, ensure our data has exactly 65 columns
        const data = rowGroups[rowIndex];
        
        // Verify the row index is valid
        if (rowIndex < 1) {
          Logger.log(`Skipping invalid row index: ${rowIndex}`);
          return;
        }
        
        // Ensure data array is of expected length
        while (data.length > 65) {
          data.pop(); // Trim extra columns
        }
        while (data.length < 65) {
          data.push(""); // Add missing columns
        }
        
        // Update the row data
        sheet.getRange(rowIndex, 7, 1, data.length).setValues([data]);
      } catch (error) {
        Logger.log(`Error updating row ${rowIndex}: ${error.message}`);
        // Set a simple error message for this row
        try {
          sheet.getRange(rowIndex, 7).setValue(`Failed: Error updating row (${error.message})`);
        } catch (e) {
          Logger.log(`Unable to set error message: ${e.message}`);
        }
      }
    });
  }
  
  // Update status colors
  statusUpdates.forEach(update => {
    try {
      sheet.getRange(update.row, 7).setFontColor(update.color);
    } catch (error) {
      Logger.log(`Error setting color for row ${update.row}: ${error.message}`);
    }
  });
  
  // Set text wrapping for status and company description columns for all updated rows
  if (rowIndices.length > 0) {
    try {
      const startRow = Math.min(...rowIndices);
      const endRow = Math.max(...rowIndices);
      
      // Apply overflow to Status column (column 7) for all updated rows
      sheet.getRange(startRow, 7, endRow - startRow + 1, 1)
           .setWrapStrategy(SpreadsheetApp.WrapStrategy.OVERFLOW);
      
      // Company Description is typically column "AN" which should be 34 columns after G
      // Let's determine its position from the headers to be safe
      const headers = sheet.getRange(2, 7, 1, 65).getValues()[0];
      let companyDescIndex = headers.findIndex(header => header === "Company Description");
      
      if (companyDescIndex === -1) {
        // If we can't find it, use a default position (34 columns after G)
        companyDescIndex = 34;
      }
      
      const companyDescColumn = 7 + companyDescIndex;
      
      // Apply overflow wrapping to company description column
      sheet.getRange(startRow, companyDescColumn, endRow - startRow + 1, 1)
           .setWrapStrategy(SpreadsheetApp.WrapStrategy.OVERFLOW);
      
      // Set column width to ensure adequate display space
      sheet.setColumnWidth(companyDescColumn, 300); // 300 pixels should be enough for most descriptions
      
      // Make sure row height adjusts automatically - but only if there aren't too many rows
      // to avoid script timeout
      if (endRow - startRow < 100) {
        for (let row = startRow; row <= endRow; row++) {
          sheet.setRowHeight(row, -1); // -1 means automatic height based on content
        }
      }
    } catch (error) {
      Logger.log(`Error applying text wrapping: ${error.message}`);
    }
  }
}

// Function to complete the processing with improved text wrapping
function completeProcessing(state) {
  // Get accurate final statistics from the sheet
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  // Get all status values from column G (excluding header rows)
  let finalStats = {
    processed: state.stats.processed,
    success: 0,
    nodata: 0,
    failed: 0,
    batches: state.stats.batches
  };
  
  // Only count status values if there are rows with data
  if (sheet.getLastRow() > 2) { // > 2 because row 1-2 are headers
    try {
      const statusRange = sheet.getRange(3, 7, sheet.getLastRow() - 2, 1);
      const statusValues = statusRange.getValues().flat();
      
      // Count each type of status - IMPROVED VERSION
      statusValues.forEach(status => {
        if (status === "Success") {
          finalStats.success++;
        } else if (status === "Could not find requested data") {
          finalStats.nodata++;
        } else if (status && status !== "" && !status.startsWith("Failed:")) {
          // Any other non-empty, non-failed status gets counted as failed
          finalStats.failed++;
        } else if (status && status.startsWith("Failed:")) {
          // Explicit failed messages
          finalStats.failed++;
        }
      });
      
      Logger.log("Final stats from sheet - Success: " + finalStats.success + 
                ", No data: " + finalStats.nodata + 
                ", Failed: " + finalStats.failed);
      
      // Find the Company Description column
      const headers = sheet.getRange(2, 7, 1, 65).getValues()[0];
      let companyDescIndex = headers.findIndex(header => header === "Company Description");
      
      if (companyDescIndex === -1) {
        // If we can't find it, use a default position (34 columns after G)
        companyDescIndex = 34;
      }
      
      const companyDescColumn = 7 + companyDescIndex;
      
      // Apply proper wrapping to all Company Description cells
      if (sheet.getLastRow() >= 3) {
        // Get the range for all company descriptions
        const descRange = sheet.getRange(3, companyDescColumn, sheet.getLastRow() - 2, 1);
        
        // Apply overflow strategy
        descRange.setWrapStrategy(SpreadsheetApp.WrapStrategy.OVERFLOW);
        
        // Set column width to ensure adequate display space
        sheet.setColumnWidth(companyDescColumn, 300); // 300 pixels is usually sufficient
        
        // Reset all row heights to automatic - but limit to avoid timeout
        const maxRowsToAdjust = 500;
        const rowsToAdjust = Math.min(sheet.getLastRow() - 2, maxRowsToAdjust);
        
        for (let row = 3; row <= 3 + rowsToAdjust - 1; row++) {
          try {
            sheet.setRowHeight(row, -1); // -1 means automatic row height
          } catch (err) {
            Logger.log(`Error setting row height for row ${row}: ${err.message}`);
          }
        }
        
        if (sheet.getLastRow() - 2 > maxRowsToAdjust) {
          Logger.log(`Only adjusted row heights for first ${maxRowsToAdjust} rows to avoid timeout.`);
        }
      }
      
    } catch (e) {
      // Ignore formatting errors
      Logger.log('Error setting text wrapping: ' + e);
    }
  }
  
  // Update final status with simplified message
  updateStatusInTopRow(
    `Complete!`,
    state.totalRowsToProcess,
    state.totalRowsToProcess
  );
  
  // Update final stats
  updateFinalStats(finalStats.success, finalStats.nodata, finalStats.failed);
  
  // Show toast notification to inform user
  SpreadsheetApp.getActiveSpreadsheet().toast(
    `Enrichment process complete! Successfully enriched: ${finalStats.success}, No data found: ${finalStats.nodata}, Failed: ${finalStats.failed}`,
    "Process Complete",
    10 // Show for 10 seconds
  );
  
  // Clean up state
  PropertiesService.getScriptProperties().deleteProperty('enrichment_state');
}

// Helper function to find the last row with actual data
function getLastRowWithData(sheet, numColumns) {
  const data = sheet.getDataRange().getValues();
  for (let i = data.length - 1; i >= 0; i--) {
    for (let j = 0; j < numColumns; j++) {
      if (data[i][j] !== '') {
        return i + 1; // +1 because array is 0-indexed
      }
    }
  }
  return 1; // Return 1 if no data found (just the header row)
}

// TEST FUNCTION - Test with your exact working payload structure
function testWithWorkingExample() {
  const API_KEY = PropertiesService.getScriptProperties().getProperty('api_key');
  
  if (!API_KEY) {
    Logger.log("ERROR: No API key found!");
    SpreadsheetApp.getUi().alert("Error: No API key found in Script Properties");
    return;
  }
  
  // Use the EXACT structure from your working example
  const testPayload = {
    "contacts": [
      {
        "contactId": "test1",
        "fullName": "Jasmine Jordan",
        "linkedinUrl": "",
        "email": "",
        "companies": [
          {
            "name": "",
            "domain": "thefirmyoutrust.com",
            "isCurrent": true
          }
        ]
      },
      {
        "contactId": "test2",
        "fullName": "Laura Holloway",
        "linkedinUrl": "",
        "email": "",
        "companies": [
          {
            "name": "",
            "domain": "tedlaw.com",
            "isCurrent": true
          }
        ]
      }
    ]
  };
  
  const options = {
    method: 'POST',
    contentType: 'application/json',
    headers: { 'api_key': API_KEY },
    muteHttpExceptions: true,
    payload: JSON.stringify(testPayload)
  };
  
  Logger.log("=== TESTING WITH YOUR WORKING EXAMPLE ===");
  Logger.log("Payload: " + JSON.stringify(testPayload, null, 2));
  
  try {
    const response = UrlFetchApp.fetch('https://api.lusha.com/v2/person', options);
    const statusCode = response.getResponseCode();
    const responseText = response.getContentText();
    
    Logger.log("=== TEST RESPONSE ===");
    Logger.log("Status Code: " + statusCode);
    Logger.log("Response Body: " + responseText);
    
    try {
      const responseData = JSON.parse(responseText);
      Logger.log("\nParsed Response:");
      Logger.log(JSON.stringify(responseData, null, 2));
      
      // Check for errors
      if (responseData.contacts) {
        Object.keys(responseData.contacts).forEach(contactId => {
          const contact = responseData.contacts[contactId];
          if (contact.error) {
            Logger.log(`Contact ${contactId} ERROR: ${JSON.stringify(contact.error)}`);
          } else {
            Logger.log(`Contact ${contactId} SUCCESS`);
          }
        });
      }
      
      SpreadsheetApp.getUi().alert("Test Complete", 
        "Status: " + statusCode + "\nCheck the logs (View > Logs) for full details", 
        SpreadsheetApp.getUi().ButtonSet.OK);
      
    } catch (e) {
      Logger.log("Response is not valid JSON: " + e.message);
      SpreadsheetApp.getUi().alert("Error", "Response is not valid JSON: " + e.message, SpreadsheetApp.getUi().ButtonSet.OK);
    }
    
  } catch (error) {
    Logger.log("=== REQUEST FAILED ===");
    Logger.log("Error: " + error.message);
    Logger.log("Error stack: " + error.stack);
    SpreadsheetApp.getUi().alert("Error", "Request failed: " + error.message, SpreadsheetApp.getUi().ButtonSet.OK);
  }
}

// Original test function
function testLushaAPI() {
  const API_KEY = PropertiesService.getScriptProperties().getProperty('api_key');
  
  if (!API_KEY) {
    Logger.log("ERROR: No API key found!");
    return;
  }
  
  Logger.log("=== LUSHA API TEST ===");
  Logger.log("API Key exists: " + (API_KEY ? "Yes" : "No"));
  Logger.log("API Key length: " + (API_KEY ? API_KEY.length : "N/A"));
  Logger.log("API Key first 10 chars: " + (API_KEY ? API_KEY.substring(0, 10) + "..." : "N/A"));
  
  // Test with the simplest possible request
  const testPayload = {
    contacts: [
      {
        contactId: "test123",
        email: "test@example.com"
      }
    ]
  };
  
  const options = {
    method: 'POST',
    contentType: 'application/json',
    headers: { 'api_key': API_KEY },
    muteHttpExceptions: true,
    payload: JSON.stringify(testPayload)
  };
  
  Logger.log("=== TEST REQUEST ===");
  Logger.log("URL: https://api.lusha.com/v2/person");
  Logger.log("Method: POST");
  Logger.log("Headers: " + JSON.stringify(options.headers));
  Logger.log("Content-Type: " + options.contentType);
  Logger.log("Payload: " + options.payload);
  
  try {
    const response = UrlFetchApp.fetch('https://api.lusha.com/v2/person', options);
    const statusCode = response.getResponseCode();
    const responseText = response.getContentText();
    const headers = response.getAllHeaders();
    
    Logger.log("=== TEST RESPONSE ===");
    Logger.log("Status Code: " + statusCode);
    Logger.log("Response Headers: " + JSON.stringify(headers));
    Logger.log("Response Body: " + responseText);
    
    // Try to parse response
    try {
      const responseData = JSON.parse(responseText);
      Logger.log("Parsed Response: " + JSON.stringify(responseData, null, 2));
    } catch (e) {
      Logger.log("Response is not valid JSON: " + e.message);
    }
    
  } catch (error) {
    Logger.log("=== REQUEST FAILED ===");
    Logger.log("Error: " + error.message);
    Logger.log("Error stack: " + error.stack);
  }
}

// DIAGNOSTIC FUNCTION - Run this on a specific row to diagnose data issues
function diagnoseContactData() {
  const ui = SpreadsheetApp.getUi();
  const response = ui.prompt(
    'Diagnose Contact Data',
    'Enter the row number to diagnose (e.g., 86 for the first data row after headers):',
    ui.ButtonSet.OK_CANCEL
  );
  
  if (response.getSelectedButton() !== ui.Button.OK) {
    return;
  }
  
  const rowNumber = parseInt(response.getResponseText());
  
  if (isNaN(rowNumber) || rowNumber < 3) {
    ui.alert('Invalid row number. Please enter a number >= 3.');
    return;
  }
  
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = sheet.getRange(rowNumber, 1, 1, 6).getValues()[0];
  const [firstName, lastName, companyName, companyDomain, emailAddress, linkedinUrl] = data;
  
  Logger.log("=== CONTACT DATA DIAGNOSIS ===");
  Logger.log(`Row: ${rowNumber}`);
  Logger.log(`First Name: "${firstName}" (Type: ${typeof firstName}, Length: ${firstName ? firstName.toString().length : 0})`);
  Logger.log(`Last Name: "${lastName}" (Type: ${typeof lastName}, Length: ${lastName ? lastName.toString().length : 0})`);
  Logger.log(`Company Name: "${companyName}" (Type: ${typeof companyName}, Length: ${companyName ? companyName.toString().length : 0})`);
  Logger.log(`Company Domain: "${companyDomain}" (Type: ${typeof companyDomain}, Length: ${companyDomain ? companyDomain.toString().length : 0})`);
  Logger.log(`Email: "${emailAddress}" (Type: ${typeof emailAddress}, Length: ${emailAddress ? emailAddress.toString().length : 0})`);
  Logger.log(`LinkedIn: "${linkedinUrl}" (Type: ${typeof linkedinUrl}, Length: ${linkedinUrl ? linkedinUrl.toString().length : 0})`);
  
  // Run validation
  Logger.log("\n=== VALIDATION RESULTS ===");
  
  const emailResult = cleanAndValidateEmail(emailAddress);
  Logger.log(`Email Validation: ${emailResult.isValid ? "PASS" : "FAIL"}`);
  if (!emailResult.isValid) {
    Logger.log(`  Error: ${emailResult.error}`);
  } else if (emailResult.cleaned) {
    Logger.log(`  Cleaned: "${emailResult.cleaned}"`);
  }
  
  const domainResult = cleanAndValidateDomain(companyDomain);
  Logger.log(`Domain Validation: ${domainResult.isValid ? "PASS" : "FAIL"}`);
  if (!domainResult.isValid) {
    Logger.log(`  Error: ${domainResult.error}`);
  } else if (domainResult.cleaned) {
    Logger.log(`  Cleaned: "${domainResult.cleaned}"`);
  }
  
  const linkedInResult = cleanAndValidateLinkedIn(linkedinUrl);
  Logger.log(`LinkedIn Validation: ${linkedInResult.isValid ? "PASS" : "FAIL"}`);
  if (!linkedInResult.isValid) {
    Logger.log(`  Error: ${linkedInResult.error}`);
  } else if (linkedInResult.cleaned) {
    Logger.log(`  Cleaned: "${linkedInResult.cleaned}"`);
  }
  
  // Check for special characters
  Logger.log("\n=== SPECIAL CHARACTER CHECK ===");
  const checkSpecialChars = (value, fieldName) => {
    if (!value) return;
    const str = value.toString();
    const specialChars = str.match(/[^\x20-\x7E]/g);
    if (specialChars) {
      Logger.log(`${fieldName} contains special characters: ${specialChars.join(', ')} (codes: ${specialChars.map(c => c.charCodeAt(0)).join(', ')})`);
    } else {
      Logger.log(`${fieldName}: No special characters detected`);
    }
  };
  
  checkSpecialChars(firstName, "First Name");
  checkSpecialChars(lastName, "Last Name");
  checkSpecialChars(companyName, "Company Name");
  checkSpecialChars(companyDomain, "Company Domain");
  checkSpecialChars(emailAddress, "Email");
  checkSpecialChars(linkedinUrl, "LinkedIn URL");
  
  // Show what would be sent to API
  Logger.log("\n=== PAYLOAD THAT WOULD BE SENT ===");
  const fullName = [firstName, lastName].filter(Boolean).join(" ").trim();
  const payload = {
    contactId: String(rowNumber),
    fullName: fullName,
    email: emailResult.cleaned,
    linkedinUrl: linkedInResult.cleaned
  };
  
  if (companyName || domainResult.cleaned) {
    payload.companies = [{
      name: companyName ? companyName.toString().trim() : "",
      domain: domainResult.cleaned,
      isCurrent: true
    }];
  }
  
  Logger.log(JSON.stringify(payload, null, 2));
  
  ui.alert(
    'Diagnosis Complete',
    'Check the Logs (View > Logs or Ctrl+Enter) for detailed diagnostics of row ' + rowNumber,
    ui.ButtonSet.OK
  );
}