apps_script.js

/**
 * GET: Handle all actions (CLEAR, READ, CREATE, UPDATE, DELETE)
 */
function doGet(e) {
  try {
    const params = e.parameter || {};
    const action = params.action || "";
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sheet = ss.getSheetByName("appbooking");

    // CLEAR: Remove all bookings before sync
    if (action === "CLEAR") {
      if (!sheet) return errorResponse("appbooking sheet not found");
      
      const lastRow = sheet.getLastRow();
      const lastCol = sheet.getLastColumn();
      let rowsCleared = 0;
      
      if (lastRow > 1) {
        sheet.getRange(2, 1, lastRow - 1, lastCol).clearContent();
        rowsCleared = lastRow - 1;
      }
      
      // Also clear the calendar grid
      const destSheet = ss.getSheetByName("bookings2026");
      if (destSheet && destSheet.getLastRow() > 1 && destSheet.getLastColumn() > 2) {
        destSheet.getRange(2, 3, destSheet.getLastRow() - 1, destSheet.getLastColumn() - 2)
                 .clearContent().setBackground("#FFFFFF").setFontColor("#000000");
      }
      
      return ContentService.createTextOutput(JSON.stringify({
        "status": "success",
        "rowsCleared": rowsCleared
      })).setMimeType(ContentService.MimeType.JSON);
    }

    // READ: Return calendar grid data
    if (action === "READ") {
      const destSheet = ss.getSheetByName("bookings2026");
      if (!destSheet) return errorResponse("bookings2026 sheet not found");

      const data = destSheet.getDataRange().getDisplayValues();
      const backgrounds = destSheet.getDataRange().getBackgrounds();

      return ContentService.createTextOutput(JSON.stringify({
        values: data,
        colors: backgrounds
      })).setMimeType(ContentService.MimeType.JSON);
    }

    // CREATE: Add new booking
    if (action === "CREATE") {
      if (!sheet) return errorResponse("appbooking sheet not found");
      
      const values = sheet.getDataRange().getValues();
      const isDuplicate = values.some(row => 
        row[0] === params.casa && 
        row[1] === params.guest && 
        isSameDay(row[2], params.arrival)
      );

      if (isDuplicate) {
        return ContentService.createTextOutput(JSON.stringify({
          "status": "ignored", 
          "message": "Duplicate found"
        })).setMimeType(ContentService.MimeType.JSON);
      }

      sheet.appendRow([params.casa, params.guest, params.arrival, params.time || "", params.day || "", params.departure, ""]);
      syncBookings();

      return ContentService.createTextOutput(JSON.stringify({
        "status": "success"
      })).setMimeType(ContentService.MimeType.JSON);
    }

    // UPDATE: Update existing booking
    if (action === "UPDATE") {
      if (!sheet) return errorResponse("appbooking sheet not found");
      
      const values = sheet.getDataRange().getValues();
      let rowIndex = -1;

      for (let i = 1; i < values.length; i++) {
        if (values[i][1] === params.oldGuest && isSameDay(values[i][2], params.oldArrival)) {
          rowIndex = i + 1;
          break;
        }
      }

      if (rowIndex === -1) return errorResponse("Booking not found");

      sheet.getRange(rowIndex, 1, 1, 7).setValues([[
        params.casa, params.guest, params.arrival, params.time || "", params.day || "", params.departure, ""
      ]]);

      syncBookings();
      return ContentService.createTextOutput(JSON.stringify({"status": "success"}))
        .setMimeType(ContentService.MimeType.JSON);
    }

    // DELETE: Remove booking
    if (action === "DELETE") {
      if (!sheet) return errorResponse("appbooking sheet not found");
      
      const values = sheet.getDataRange().getValues();
      let rowIndex = -1;

      for (let i = 1; i < values.length; i++) {
        if (values[i][1] === params.oldGuest && isSameDay(values[i][2], params.oldArrival)) {
          rowIndex = i + 1;
          break;
        }
      }

      if (rowIndex === -1) return errorResponse("Booking not found");

      sheet.deleteRow(rowIndex);
      syncBookings();
      return ContentService.createTextOutput(JSON.stringify({"status": "success"}))
        .setMimeType(ContentService.MimeType.JSON);
    }

    return errorResponse("Unknown action: " + action);
  } catch (err) {
    return errorResponse(err.toString());
  }
}

/**
 * SYNC: Transform appbooking list into bookings2026 grid
 */
function syncBookings() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheet = ss.getSheetByName("appbooking");
  const destSheet = ss.getSheetByName("bookings2026");

  if (!sourceSheet || !destSheet) return;

  const sourceData = sourceSheet.getDataRange().getValues();
  const destData = destSheet.getDataRange().getValues();
  const lastCol = destSheet.getLastColumn();
  
  // Map house colors from header
  const houseConfig = {};
  for (let col = 3; col <= lastCol; col++) {
    let houseName = destSheet.getRange(1, col).getValue().toString().trim();
    if (houseName) {
      houseConfig[houseName] = {
        color: destSheet.getRange(1, col).getBackground(),
        index: col
      };
    }
  }

  // Clear calendar grid
  if (destSheet.getLastRow() > 1 && lastCol > 2) {
    destSheet.getRange(2, 3, destSheet.getLastRow() - 1, lastCol - 2)
             .clearContent().setBackground("#FFFFFF").setFontColor("#000000");
  }

  // Process each booking
  for (let i = 1; i < sourceData.length; i++) {
    let house = sourceData[i][0] ? sourceData[i][0].toString().trim() : "";
    let guest = sourceData[i][1] ? sourceData[i][1].toString().trim() : "";
    let arrival = sourceData[i][2] instanceof Date ? sourceData[i][2] : null;
    let departure = sourceData[i][5] instanceof Date ? sourceData[i][5] : null;

    if (!house || !guest || !arrival || !departure || !houseConfig[house]) continue;

    const targetCol = houseConfig[house].index;
    const houseColor = houseConfig[house].color;
    const dayNames = ["Dom", "Lun", "Mar", "Mié", "Jue", "Vie", "Sáb"];

    // Find weeks that overlap with this booking
    for (let j = 1; j < destData.length; j++) {
      let weekStart = destData[j][1] instanceof Date ? destData[j][1] : null;
      if (!weekStart) continue;

      weekStart = new Date(weekStart);
      weekStart.setHours(0, 0, 0, 0);
      
      const weekEnd = new Date(weekStart);
      weekEnd.setDate(weekEnd.getDate() + 6);

      const arrNorm = new Date(arrival);
      arrNorm.setHours(0, 0, 0, 0);
      
      const depNorm = new Date(departure);
      depNorm.setHours(0, 0, 0, 0);

      // Check if booking overlaps this week
      if (arrNorm <= weekEnd && depNorm >= weekStart) {
        const cell = destSheet.getRange(j + 1, targetCol);
        const existing = cell.getValue();
        
        // Check if this is the first week (arrival week)
        const isFirstWeek = arrNorm >= weekStart && arrNorm <= weekEnd;
        
        const dayName = dayNames[arrival.getDay()];
        const label = isFirstWeek
          ? guest + " (" + dayName + " " + arrival.getDate() + "/" + (arrival.getMonth() + 1) + ")"
          : guest;

        if (existing && !existing.toString().includes(guest)) {
          cell.setValue(existing + " / " + label).setBackground("#FF0000").setFontColor("#FFFFFF");
        } else if (!existing) {
          cell.setValue(label)
              .setBackground(houseColor)
              .setFontColor(getContrast(houseColor))
              .setFontWeight("bold")
              .setHorizontalAlignment("center");
        }
      }
    }
  }
}

// Helpers
function isSameDay(d1, d2) {
  if (!d1 || !d2) return false;
  const date1 = d1 instanceof Date ? d1 : new Date(d1);
  const date2 = d2 instanceof Date ? d2 : new Date(d2);
  if (isNaN(date1.getTime()) || isNaN(date2.getTime())) return false;
  return date1.getFullYear() === date2.getFullYear() && 
         date1.getMonth() === date2.getMonth() && 
         date1.getDate() === date2.getDate();
}

function getContrast(hex) {
  if (!hex || hex === "#ffffff" || hex === "white") return "black";
  hex = hex.replace("#", "");
  if (hex.length === 3) hex = hex[0]+hex[0]+hex[1]+hex[1]+hex[2]+hex[2];
  const r = parseInt(hex.substr(0, 2), 16);
  const g = parseInt(hex.substr(2, 2), 16);
  const b = parseInt(hex.substr(4, 2), 16);
  const yiq = ((r * 299) + (g * 587) + (b * 114)) / 1000;
  return (yiq >= 128) ? 'black' : 'white';
}

function errorResponse(msg) {
  return ContentService.createTextOutput(JSON.stringify({
    "status": "error", 
    "message": msg
  })).setMimeType(ContentService.MimeType.JSON);
}