function getOrCreateSheet(name) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(name);
  if(!sheet) {
    sheet = ss.insertSheet(name);
    if (name === 'Logs') {
      sheet.appendRow(['Log ID', 'Timestamp', 'Fecha', 'Vendor/Ticket ID', 'Nombre', 'Responsable', 'Acción', 'Detalles']);
    } else {
      initHeaders(sheet);
    }
  }
  return sheet;
}

function doPost(e) {
  var data = JSON.parse(e.postData.contents);
  
  if (data.action === 'log') {
    var logSheet = getOrCreateSheet('Logs');
    var l = data.log;
    logSheet.appendRow([l.id, l.timestamp, l.date, l.vendorId, l.vendorName, l.clerk, l.actionType, l.details]);
    return ContentService.createTextOutput(JSON.stringify({success: true})).setMimeType(ContentService.MimeType.JSON);
  }

  var isTkt = false;
  if (data.vendor) {
    isTkt = (data.vendor.isTicket === 'Yes' || data.vendor.isTicket === true);
  } else if (data.isTicket) {
    isTkt = (data.isTicket === 'Yes' || data.isTicket === true);
  }
  
  var targetSheetName = isTkt ? 'Tickets' : 'Vendors';
  var sheet = getOrCreateSheet(targetSheetName);
  
  if(data.action === 'add') {
    var v = data.vendor;
    sheet.appendRow([v.id, v.name, v.type, v.category, v.assignedTo, v.priority, v.pastDueAmount || 0, v.pendingAmount || 0, v.criticalDate || '', v.status || 'Pendiente', v.notes || '', v.createdAt, v.lastStatementDate || '', v.lastWorkedDate || '', v.completedDate || '', v.isTicket || 'No', v.parentId || '', v.hasPastDue || 'No']);
  } else if (data.action === 'bulk_add') {
    if (data.vendors && data.vendors.length > 0) {
      var rows = data.vendors.map(function(v) { 
        return [v.id, v.name, v.type, v.category, v.assignedTo, v.priority, v.pastDueAmount || 0, v.pendingAmount || 0, v.criticalDate || '', v.status || 'Pendiente', v.notes || '', v.createdAt, v.lastStatementDate || '', v.lastWorkedDate || '', v.completedDate || '', v.isTicket || 'No', v.parentId || '', v.hasPastDue || 'No']; 
      });
      sheet.getRange(sheet.getLastRow() + 1, 1, rows.length, rows[0].length).setValues(rows);
    }
  } else if (data.action === 'update') {
    var all = sheet.getDataRange().getValues();
    for(var i=1; i<all.length; i++) {
      if(String(all[i][0]).trim() === String(data.vendor.id).trim()) {
        var v = data.vendor;
        sheet.getRange(i+1, 2, 1, 17).setValues([[v.name, v.type, v.category, v.assignedTo, v.priority, v.pastDueAmount || 0, v.pendingAmount || 0, v.criticalDate || '', v.status || 'Pendiente', v.notes || '', v.createdAt, v.lastStatementDate || '', v.lastWorkedDate || '', v.completedDate || '', v.isTicket || 'No', v.parentId || '', v.hasPastDue || 'No']]);
        break;
      }
    }
  } else if (data.action === 'delete') {
    var all = sheet.getDataRange().getValues();
    for(var i=1; i<all.length; i++) {
      if(String(all[i][0]).trim() === String(data.id).trim()) { sheet.deleteRow(i+1); break; }
    }
  }
  return ContentService.createTextOutput(JSON.stringify({success: true})).setMimeType(ContentService.MimeType.JSON);
}

function getSheetData(name) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(name);
  if(!sheet) return [];
  initHeaders(sheet);
  
  var lastRow = sheet.getLastRow();
  var lastColumn = Math.max(sheet.getLastColumn(), 18); 
  if (lastRow <= 1) return [];

  var data = sheet.getRange(1, 1, lastRow, lastColumn).getValues();
  var headers = data[0];
  
  var result = [];
  var needsIdFix = false;
  var seenIds = {};

  for(var r=1; r<data.length; r++) {
      var row = data[r];
      // Ignorar filas que estén completamente en blanco
      if(String(row[1]).trim() === "" && String(row[2]).trim() === "") continue; 
      
      var vId = String(row[0]).trim();
      // Si la fila no tiene ID, se le asigna uno y se marca para escribirse en el Excel
      if (!vId || vId === 'undefined' || vId === '' || seenIds[vId]) {
         vId = "ID_" + new Date().getTime() + "_" + r;
         data[r][0] = vId; 
         needsIdFix = true;
      }
      seenIds[vId] = true;

      var obj = {}; 
      headers.forEach(function(h, i) { 
          if(h) obj[h] = data[r][i]; 
      }); 
      result.push(obj);
  }

  // Escribir los IDs sanados de vuelta al Excel de un solo golpe (súper rápido)
  if (needsIdFix) {
      var idValues = [];
      for(var r=1; r<data.length; r++) {
          idValues.push([data[r][0]]);
      }
      sheet.getRange(2, 1, idValues.length, 1).setValues(idValues);
  }

  return result;
}

function doGet(e) {
  try {
    var vendors = getSheetData('Vendors');
    var tickets = getSheetData('Tickets');
    var combined = vendors.concat(tickets);
    return ContentService.createTextOutput(JSON.stringify(combined)).setMimeType(ContentService.MimeType.JSON);
  } catch(err) {
    return ContentService.createTextOutput(JSON.stringify({error: err.toString()})).setMimeType(ContentService.MimeType.JSON);
  }
}

function initHeaders(sheet) {
  var expected = ['id', 'name', 'type', 'category', 'assignedTo', 'priority', 'pastDueAmount', 'pendingAmount', 'criticalDate', 'status', 'notes', 'createdAt', 'lastStatementDate', 'lastWorkedDate', 'completedDate', 'isTicket', 'parentId', 'hasPastDue'];
  if(sheet.getLastColumn() === 0) {
    sheet.appendRow(expected);
    return;
  }
  var actual = sheet.getRange(1, 1, 1, Math.max(sheet.getLastColumn(), expected.length)).getValues()[0];
  for(var i=0; i<expected.length; i++) {
    if(actual[i] !== expected[i]) {
      sheet.getRange(1, i+1).setValue(expected[i]);
    }
  }
}