// This is the main code that processes the raw data from the 'Raw Data' sheet and places it in 'Formatted Data' sheet.
function processRawData() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var rawSheet = sheet.getSheetByName('Raw Data'); // Raw data arrives here from Zapier
  var formattedSheet = sheet.getSheetByName('Formatted Data'); // Formatted data is posted here after processing

  // Get the data from the Raw Data sheet
  var rawData = rawSheet.getDataRange().getValues();

  for (var i = 1; i < rawData.length; i++) { // Start from row 1 to skip headers
    var row = rawData[i];
    if (row[2] === "Processed") continue; // Skip already processed rows (assuming column C)

    var processedRow = [];

    // Extract fields
    var rawDate = row[0]; // Assuming Date is in the first column
    var emailBody = row[1]; // Assuming Email Body is in the second column
    
    // Process Date
    var formattedDate = ""; // Default empty
    try {
      var dateMatch = rawDate.match(/\b(\d{1,2}\s\w+\s\d{4})\b/);
      if (dateMatch) {
        var dateObj = new Date(dateMatch[0]); // Extract and parse the date
        formattedDate = Utilities.formatDate(dateObj, Session.getScriptTimeZone(), "dd/MM/yy");
      }
    } catch (err) {
      formattedDate = "Invalid Date"; // Fallback for invalid dates
    }

    // Extract Restaurant Name
    var restaurantName = emailBody.match(/from (.+)/)?.[1]?.trim() || "Unknown";

    // Extract Order Items
    var orderStart = emailBody.indexOf("Delivered") + "Delivered".length;
    var orderEnd = emailBody.indexOf("Total paid -");
    var orderText = emailBody.substring(orderStart, orderEnd).trim();
    var orderItems = orderText.split("\n").filter(item => item.includes("X") && /\d/.test(item)); // Filter valid items

    // Extract Total Paid
    var totalPaidMatch = emailBody.match(/Total paid -\s*₹([\d.,]+)/);
    var totalPaid = totalPaidMatch ? parseFloat(totalPaidMatch[1].replace(/,/g, '')).toFixed(2) : "0.00";

    // Skip rows with price 0
    if (parseFloat(totalPaid) === 0) {
      rawSheet.getRange(i + 1, 3).setValue("Skipped: Price 0"); // Mark as skipped
      continue;
    }

    // Append processed data
    processedRow.push(formattedDate, restaurantName, orderItems.join(", "), totalPaid);

    // Append to Formatted Data sheet
    formattedSheet.appendRow(processedRow);

    // Mark as processed in Raw Data
    rawSheet.getRange(i + 1, 3).setValue("Processed"); // Add "Processed" in column C
  }
}