A Extensão definitiva do vendedor
que usa o WhatsApp Web

/**
 * WA TO SHEETS CRM - BACKEND COM SUPORTE A IA GEMINI & FOLLOW-UP
 * Versão 3.5 - Resumo IA, Score de Vendas, Etiquetas e Agendamento de Follow-up
 */

function jsonResponse(data) {
  return ContentService.createTextOutput(JSON.stringify(data))
    .setMimeType(ContentService.MimeType.JSON);
}

function cleanPhone(phone) {
  if (!phone) return '';
  return String(phone).replace(/\D/g, '');
}

function doGet(e) { return handleRequest(e); }
function doPost(e) { return handleRequest(e); }

function handleRequest(e) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var params = (e && e.parameter) || {};
  var postData = null;
  if (e && e.postData && e.postData.contents) {
    try { postData = JSON.parse(e.postData.contents); } catch(err) {}
  }
  
  var action = (postData && postData.action) || params.action;
  var nameCol = parseInt((postData && postData.nameCol) || params.nameCol) || 1; // Col A
  var phoneCol = parseInt((postData && postData.phoneCol) || params.phoneCol) || 2; // Col B
  var dateCol = parseInt((postData && postData.dateCol) || params.dateCol) || 3; // Col C
  var notesCol = parseInt((postData && postData.notesCol) || params.notesCol) || 4; // Col D
  var sellerCol = parseInt((postData && postData.sellerCol) || params.sellerCol) || 5; // Col E
  var summaryCol = parseInt((postData && postData.summaryCol) || params.summaryCol) || 6; // Col F (IA)
  var scoreCol = parseInt((postData && postData.scoreCol) || params.scoreCol) || 7; // Col G (IA)
  var tagCol = parseInt((postData && postData.tagCol) || params.tagCol) || 8; // Col H (IA)
  var followupCol = parseInt((postData && postData.followupCol) || params.followupCol) || 9; // Col I (Follow-up)

  // Auto-cria cabeçalhos se a planilha for nova
  if (sheet.getLastRow() === 0) {
    sheet.appendRow([
      "Nome do Lead", "Telefone", "Data/Hora", "Origem / Observações", 
      "Vendedor / Atendente", "Resumo IA", "Score IA", "Etiqueta WhatsApp", "Lembrete / Follow-up"
    ]);
    sheet.getRange(1, 1, 1, 9).setFontWeight("bold").setBackground("#e2e8f0");
  }

  try {
    if (action === 'test') {
      return jsonResponse({
        status: "success",
        message: "Conexão com Google Sheets bem-sucedida!",
        sheetName: sheet.getName(),
        totalRows: sheet.getLastRow()
      });
    }

    if (action === 'get_phones') {
      var data = sheet.getDataRange().getValues();
      var phones = [];
      for (var i = 1; i < data.length; i++) {
        var p = cleanPhone(data[i][phoneCol - 1]);
        if (p) phones.push(p);
      }
      return jsonResponse({ status: "success", total: phones.length, phones: phones });
    }

    if (action === 'batch_add') {
      var leads = (postData && postData.leads) || [];
      if (typeof leads === 'string') {
        try { leads = JSON.parse(leads); } catch(err) { leads = []; }
      }
      if (!leads || leads.length === 0) return jsonResponse({ status: "error", message: "Nenhum lead fornecido." });

      var defaultSeller = (postData && postData.seller) || params.seller || "Equipe";
      var existingData = sheet.getDataRange().getValues();
      var existingPhones = {};
      for (var r = 1; r < existingData.length; r++) {
        var existingP = cleanPhone(existingData[r][phoneCol - 1]);
        if (existingP) {
          existingPhones[existingP] = true;
          if (existingP.length >= 8) existingPhones[existingP.slice(-8)] = true;
          if (existingP.length >= 9) existingPhones[existingP.slice(-9)] = true;
        }
      }

      var addedCount = 0;
      var duplicateCount = 0;
      var rowsToAppend = [];
      var maxCol = Math.max(nameCol, phoneCol, dateCol, notesCol, sellerCol, summaryCol, scoreCol, tagCol, followupCol, 9);
      var nowStr = Utilities.formatDate(new Date(), Session.getScriptTimeZone() || "GMT-3", "dd/MM/yyyy HH:mm");

      for (var k = 0; k < leads.length; k++) {
        var lead = leads[k];
        var leadName = String(lead.name || '').trim();
        var leadPhone = String(lead.phone || '').trim();
        var rawClean = cleanPhone(leadPhone);
        if (!rawClean && !leadName) continue;

        var isDuplicate = false;
        if (rawClean) {
          if (existingPhones[rawClean]) isDuplicate = true;
          if (!isDuplicate && rawClean.length >= 8 && existingPhones[rawClean.slice(-8)]) isDuplicate = true;
          if (!isDuplicate && rawClean.length >= 9 && existingPhones[rawClean.slice(-9)]) isDuplicate = true;
        }

        if (isDuplicate) {
          duplicateCount++;
        } else {
          if (rawClean) {
            existingPhones[rawClean] = true;
            if (rawClean.length >= 8) existingPhones[rawClean.slice(-8)] = true;
          }
          var row = [];
          for (var c = 0; c < maxCol; c++) row[c] = "";
          row[nameCol - 1] = leadName;
          row[phoneCol - 1] = leadPhone;
          if (dateCol > 0) row[dateCol - 1] = lead.date || nowStr;
          if (notesCol > 0) row[notesCol - 1] = String(lead.notes || 'WhatsApp Web').trim();
          if (sellerCol > 0) row[sellerCol - 1] = String(lead.seller || defaultSeller).trim();
          if (summaryCol > 0 && lead.aiSummary) row[summaryCol - 1] = lead.aiSummary;
          if (scoreCol > 0 && lead.aiScore) row[scoreCol - 1] = lead.aiScore;
          if (tagCol > 0 && lead.aiTag) row[tagCol - 1] = lead.aiTag;
          if (followupCol > 0 && lead.followup) row[followupCol - 1] = lead.followup;

          rowsToAppend.push(row);
          addedCount++;
        }
      }

      if (rowsToAppend.length > 0) {
        var startRow = sheet.getLastRow() + 1;
        sheet.getRange(startRow, 1, rowsToAppend.length, rowsToAppend[0].length).setValues(rowsToAppend);
      }

      return jsonResponse({ status: "success", addedCount: addedCount, duplicateCount: duplicateCount });
    }

    if (action === 'add') {
      var name = (postData && postData.name) || params.name || '';
      var phone = (postData && postData.phone) || params.phone || '';
      var notes = (postData && postData.notes) || params.notes || 'WhatsApp Web';
      var seller = (postData && postData.seller) || params.seller || 'Equipe';
      var aiSummary = (postData && postData.aiSummary) || params.aiSummary || '';
      var aiScore = (postData && postData.aiScore) || params.aiScore || '';
      var aiTag = (postData && postData.aiTag) || params.aiTag || '';
      var followup = (postData && postData.followup) || params.followup || '';
      var force = (postData && postData.force) || params.force === 'true';
      var rawClean = cleanPhone(phone);

      if (!force && rawClean) {
        var existingData = sheet.getDataRange().getValues();
        for (var i = 1; i < existingData.length; i++) {
          var rowP = cleanPhone(existingData[i][phoneCol - 1]);
          if (rowP === rawClean || (rawClean.length >= 8 && rowP.slice(-8) === rawClean.slice(-8))) {
            return jsonResponse({ status: "already_exists", row: i + 1 });
          }
        }
      }

      var maxCol = Math.max(nameCol, phoneCol, dateCol, notesCol, sellerCol, summaryCol, scoreCol, tagCol, followupCol, 9);
      var newRow = [];
      for (var i = 0; i < maxCol; i++) newRow[i] = "";
      newRow[nameCol - 1] = name;
      newRow[phoneCol - 1] = phone;
      if (dateCol > 0) newRow[dateCol - 1] = Utilities.formatDate(new Date(), Session.getScriptTimeZone() || "GMT-3", "dd/MM/yyyy HH:mm");
      if (notesCol > 0) newRow[notesCol - 1] = notes;
      if (sellerCol > 0) newRow[sellerCol - 1] = seller;
      if (summaryCol > 0) newRow[summaryCol - 1] = aiSummary;
      if (scoreCol > 0) newRow[scoreCol - 1] = aiScore;
      if (tagCol > 0) newRow[tagCol - 1] = aiTag;
      if (followupCol > 0) newRow[followupCol - 1] = followup;

      sheet.appendRow(newRow);
      return jsonResponse({ status: "success", row: sheet.getLastRow() });
    }

    return jsonResponse({ status: "invalid_action" });
  } catch(err) {
    return jsonResponse({ status: "error", message: err.toString() });
  }
}