function sendConfirmationEmail(name, email, date, span, event, signature) {
  const subject = `Your ${event} Signup Confirmation`;
  const body = `Hi ${name},

Thank you for signing up to help with the ${event}!

You're confirmed for:
📅 Date: ${date}
⏰ Time: ${span}

We appreciate your support and look forward to seeing you there.

Warm regards,
${signature}`;

  MailApp.sendEmail(email, subject, body);
}

function doGet(e) {
  const spreadsheetID = e.parameter.spreadsheetId;
  const action = e.parameter.action || "loadSlots";

  if (!spreadsheetID) return ContentService.createTextOutput("Missing spreadsheetID");

  const sheetName = e.parameter.sheetName;
  const spreadsheet = getSpreadsheetById(spreadsheetID);
  const sheet = spreadsheet ? spreadsheet.getSheetByName(sheetName) : null;

  if (!sheet) return ContentService.createTextOutput("Spreadsheet or sheet not found");

  const data = sheet.getDataRange().getValues();
  const headers = data[0];

  const nameIndex = headers.indexOf("Name");
  const emailIndex = headers.indexOf("Email");
  const phoneIndex = headers.indexOf("Phone");
  const dateIndex = headers.indexOf("Date");
  const spanIndex = headers.indexOf("Time Span");
  const tipIndex = headers.indexOf("Slot Tip"); // optional

  if (action === "fetchSignups") {
    const filled = data.slice(1).filter(row => row[nameIndex] && row[emailIndex]);
    const result = filled.map(row => {
      const rawDate = new Date(row[dateIndex]);
      const weekday = rawDate.toLocaleDateString("en-US", { weekday: "short" });
      const month = rawDate.toLocaleDateString("en-US", { month: "short" });
      const day = rawDate.getDate();
      const formattedDate = `${weekday}, ${month} ${day}`;
      const slot = `${formattedDate} – ${row[spanIndex]}`;
      const tip = tipIndex !== -1 ? row[tipIndex] : "";

      return {
        name: row[nameIndex],
        email: row[emailIndex],
        phone: row[phoneIndex] || "",
        slot,
        tip
      };
    });

    return ContentService
      .createTextOutput(JSON.stringify(result))
      .setMimeType(ContentService.MimeType.JSON);
  }

  // Default: return open slots
  const openSlots = data.slice(1).map((row, i) => {
    const nameEmpty = !row[nameIndex];
    const emailEmpty = !row[emailIndex];
    if (!nameEmpty || !emailEmpty) return null;

    const rawDate = new Date(row[dateIndex]);
    const weekday = rawDate.toLocaleDateString("en-US", { weekday: "short" });
    const month = rawDate.toLocaleDateString("en-US", { month: "short" });
    const day = rawDate.getDate();
    const formattedDate = `${weekday}, ${month} ${day}`;
    const label = `${formattedDate} – ${row[spanIndex]}`;
    const tip = tipIndex !== -1 ? row[tipIndex] : "";
    const rowIndex = i + 2;

    return { label, tip, rowIndex };
  }).filter(slot => slot !== null);

  return ContentService
    .createTextOutput(JSON.stringify(openSlots))
    .setMimeType(ContentService.MimeType.JSON);
}

function doPost(e) {
  try {
    const spreadsheetId = e.parameter.spreadsheetId;
    if (!spreadsheetId) { return ContentService.createTextOutput("Missing spreadsheetId");}

    const spreadsheet = getSpreadsheetById(spreadsheetId);
    if (!spreadsheet) { return ContentService.createTextOutput("Spreadsheet not found");}

    const sheetName = e.parameter.sheetName;
    const sheet = spreadsheet.getSheetByName(sheetName);

    if (!sheet) { return ContentService.createTextOutput("Sheet not found in spreadsheet");}

    const headers = sheet.getDataRange().getValues()[0];
    const nameIndex = headers.indexOf("Name");
    const emailIndex = headers.indexOf("Email");
    const phoneIndex = headers.indexOf("Phone");
    const fillInIndex = headers.indexOf("Available to Fill-In");
    const timestampIndex = headers.indexOf("Timestamp");
    const dateIndex = headers.indexOf("Date");
    const spanIndex = headers.indexOf("Time Span");

    if ([nameIndex, emailIndex, phoneIndex, fillInIndex, timestampIndex, dateIndex, spanIndex].includes(-1)) {
      return ContentService.createTextOutput("Missing required headers");
    }

    const rowIndexRaw = e.parameter.rowIndex;
    const rowIndex = Number(rowIndexRaw);
    if (!Number.isInteger(rowIndex) || rowIndex < 2) {
      return ContentService.createTextOutput("Invalid row index");
    }

    const existingName = sheet.getRange(rowIndex, nameIndex + 1).getValue();
    const existingEmail = sheet.getRange(rowIndex, emailIndex + 1).getValue();
    // Optional: Uncomment to prevent overwriting existing signups
     if (existingName || existingEmail) {
       return ContentService.createTextOutput("Slot already taken");
     }

    const name = e.parameter.name;
    const email = e.parameter.email;
    const phone = e.parameter.phone;
    const event = e.parameter.event;
    const signature = e.parameter.signature;
    const fillInValue = e.parameter.fillIn === "on" ? "Yes" : "No";
    const timestamp = new Date();

    sheet.getRange(rowIndex, nameIndex + 1).setValue(name);
    sheet.getRange(rowIndex, emailIndex + 1).setValue(email);
    sheet.getRange(rowIndex, phoneIndex + 1).setValue(phone);
    sheet.getRange(rowIndex, fillInIndex + 1).setValue(fillInValue);
    sheet.getRange(rowIndex, timestampIndex + 1).setValue(timestamp);

    const confirmedDate = Utilities.formatDate(
      new Date(sheet.getRange(rowIndex, dateIndex + 1).getValue()),
      Session.getScriptTimeZone(),
      "MM/dd/yyyy"
    );
    const confirmedSpan = sheet.getRange(rowIndex, spanIndex + 1).getValue();

    sendConfirmationEmail(name, email, confirmedDate, confirmedSpan, event, signature);

    // Log the confirmation in the "Log" sheet
    const logSheet = spreadsheet.getSheetByName("Log");
    if (logSheet) {
       logSheet.appendRow([
        new Date(),         // Timestamp
       name,
       email,
       phone,
       event,
       confirmedDate,
       confirmedSpan,
       fillInValue
      ]);
    }

    return ContentService.createTextOutput("Success");

  } catch (err) {
    Logger.log("Error in doPost: " + err.stack);
    return ContentService.createTextOutput("Error: " + err.message);
  }
}

function getSpreadsheetById(id) {
  try {
    return SpreadsheetApp.openById(id);
  } catch (err) {
    Logger.log("Failed to open spreadsheet by ID: " + err.message);
    return null;
  }
}