const FORM_ID = '1HJHLnwlh5KZivHRGzab1gjO5HDw1fJWFw7B_rH6LOs4';


function onFormSubmit(e) {
 let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();


 Logger.log("Form submit event triggered");
 Logger.log("Event object: " + JSON.stringify(e));


 if (e && e.namedValues) {
   processFormSubmission(e.namedValues, sheet);
 } else {
   // If triggered manually, fetch the last form response
   const form = FormApp.openById(FORM_ID);
   const formResponses = form.getResponses();
   if (formResponses.length === 0) {
     Logger.log("No form responses found.");
     return;
   }
   const lastResponse = formResponses[formResponses.length - 1];
   const itemResponses = lastResponse.getItemResponses();
   const namedValues = itemResponsesToNamedValues(itemResponses);
   processFormSubmission(namedValues, sheet);
 }
}


function itemResponsesToNamedValues(itemResponses) {
 let namedValues = {};
 itemResponses.forEach(itemResponse => {
   namedValues[itemResponse.getItem().getTitle()] = [itemResponse.getResponse()];
 });
 return namedValues;
}


function processFormSubmission(namedValues, sheet) {
 const timestamp = namedValues['Timestamp'][0];
 const dateCompleted = namedValues['Date Completed'] ? namedValues['Date Completed'][0] : null;


 if (!dateCompleted) {
   Logger.log("No Date Completed found in submission");
   return;
 }


 Logger.log("Form submission received. Timestamp: " + timestamp + ", Date Completed: " + dateCompleted);
 Logger.log("namedValues: " + JSON.stringify(namedValues));


 // Process all product entries
 const columnPrefixes = ['', 'Column 9', 'Column 15', 'Column 21', 'Column 27', 'Column 33', 'Column 39', 'Column 45', 'Column 51', 'Column 57'];


 for (let i = 0; i < columnPrefixes.length; i++) {
   const prefix = columnPrefixes[i];
   const productName = prefix === '' ? namedValues['Product Name'][0] : namedValues[prefix] ? namedValues[prefix][0] : null;
  
   if (productName && productName !== "" && productName !== "Choose") {
     processProductEntry(namedValues, sheet, timestamp, dateCompleted, prefix, i);
    
     const continueEntry = prefix === '' ?
       namedValues['Would you like to enter details for another product?'][0] :
       namedValues[`Column ${Number(prefix.match(/\d+$/)[0]) + 5}`] ?
         namedValues[`Column ${Number(prefix.match(/\d+$/)[0]) + 5}`][0] : "No";
    
     if (continueEntry !== "Yes") break;
   } else {
     break;
   }
 }


 sortSheetByDate(sheet);
 addSystemColumn(sheet);
 hideExtraColumns(sheet);
}


function sortSheetByDate(sheet) {
 const dataRange = sheet.getDataRange();
 const numRows = dataRange.getNumRows();
 const numCols = dataRange.getNumColumns();


 if (numRows <= 1) return; // No data to sort


 const range = sheet.getRange(2, 1, numRows - 1, numCols);
 range.sort({column: 2, descending: true}); // Sort by Date Completed (column B) in descending order
}


function processProductEntry(namedValues, sheet, timestamp, dateCompleted, columnPrefix, rowIndex) {
 let productName, sachetsUsed, packsCreated, bumpersCreated, amazonBoxes, continueEntry;


 if (columnPrefix === '') {
   productName = namedValues['Product Name'][0];
   sachetsUsed = Number(namedValues['Sachets Used'][0]) || 0;
   packsCreated = Number(namedValues['Packs Created'][0]) || 0;
   bumpersCreated = Number(namedValues['Bumpers Created'][0]) || 0;
   amazonBoxes = Number(namedValues['Amazon Boxes Created'][0]) || 0;
   continueEntry = namedValues['Would you like to enter details for another product?'][0];
 } else {
   const columnIndex = Number(columnPrefix.match(/\d+$/)[0]);
   productName = namedValues[columnPrefix][0];
   sachetsUsed = Number(namedValues[`Column ${columnIndex + 1}`][0]) || 0;
   packsCreated = Number(namedValues[`Column ${columnIndex + 2}`][0]) || 0;
   bumpersCreated = Number(namedValues[`Column ${columnIndex + 3}`][0]) || 0;
   amazonBoxes = Number(namedValues[`Column ${columnIndex + 4}`][0]) || 0;
   continueEntry = namedValues[`Column ${columnIndex + 5}`] ? namedValues[`Column ${columnIndex + 5}`][0] : "No";
 }


 Logger.log(`Processing Product Name: ${JSON.stringify(productName)}`);


 if (productName && productName !== "Choose") {
   let newData = [timestamp, dateCompleted, productName, sachetsUsed, packsCreated, bumpersCreated, amazonBoxes, continueEntry];
   sheet.getRange(rowIndex + 2, 1, 1, newData.length).setValues([newData]);
   Logger.log(`Product entry processed and added: ${newData.join(", ")}`);
 } else {
   Logger.log(`Invalid product name: ${productName}, skipping entry.`);
 }
}


function addSystemColumn(sheet) {
 const lastColumn = sheet.getLastColumn();
 const lastRow = sheet.getLastRow();
  // Check if "Added to System" column already exists
 const headers = sheet.getRange(1, 1, 1, lastColumn).getValues()[0];
 let addedToSystemIndex = headers.indexOf("Added to System");
  if (addedToSystemIndex === -1) {
   // If it doesn't exist, add it at the end
   sheet.insertColumnAfter(lastColumn);
   sheet.getRange(1, lastColumn + 1).setValue("Added to System");
   addedToSystemIndex = lastColumn;
 }


 // Add checkboxes to rows with product entries
 if (lastRow > 1) {
   const checkboxRange = sheet.getRange(2, addedToSystemIndex + 1, lastRow - 1, 1);
   const rule = SpreadsheetApp.newDataValidation().requireCheckbox().build();
   checkboxRange.setDataValidation(rule);
 }
}


function hideExtraColumns(sheet) {
 const lastColumn = sheet.getLastColumn();
 const visibleColumns = 8; // Columns A-H (including "Would you like to enter details for another product?")
 const addedToSystemColumn = lastColumn; // Assuming it's the last column


 // Hide columns between H and "Added to System"
 if (addedToSystemColumn > visibleColumns + 1) {
   sheet.hideColumns(visibleColumns + 1, addedToSystemColumn - visibleColumns - 1);
 }
  Logger.log(`Hidden extra columns, keeping "Added to System" visible`);
}


function setupHeaders() {
 const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();


 try {
   const headers = ["Timestamp", "Date Completed", "Product Name", "Sachets Used", "Packs Created", "Bumpers Created", "Amazon Boxes Created", "Would you like to enter details for another product?"];
   sheet.getRange(1, 1, 1, headers.length).setValues([headers]);


   // Set header formatting
   const headerRange = sheet.getRange(1, 1, 1, headers.length);
   headerRange.setFontWeight("bold");
   headerRange.setBackground("#5b3f86");  // Purple color
   headerRange.setFontColor("#ffffff");   // White text


   // Auto-resize columns to fit content
   sheet.autoResizeColumns(1, headers.length);


   addSystemColumn(sheet);
   hideExtraColumns(sheet);


   Logger.log("Headers set up successfully");
 } catch (error) {
   Logger.log(`Error setting up headers: ${error.message}`);
 }
}


function resetSheetStructure() {
 const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();


 try {
   // Clear content and formatting, but keep the form data
   sheet.clear({contentsOnly: true, skipFilteredRows: true});
   setupHeaders();
   hideExtraColumns(sheet); // Ensure this is called after setupHeaders
   SpreadsheetApp.getUi().alert('Sheet structure has been reset successfully!');
   Logger.log("Sheet structure reset successfully");
 } catch (error) {
   Logger.log(`Error resetting sheet structure: ${error.message}`);
 }
}


function onOpen() {
 createCustomMenu();
}


function createCustomMenu() {
 const ui = SpreadsheetApp.getUi();
 ui.createMenu('Custom Tools')
   .addItem('Reset Sheet Structure', 'resetSheetStructure')
   .addItem('Combine Totals', 'combineTotals')
   .addToUi();
}


function combineTotals() {
 const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
 Logger.log("Starting combineTotals function");


 const data = sheet.getDataRange().getValues();
 Logger.log(`Initial data rows: ${data.length}`);


 const headers = data.shift(); // Remove and store headers
 Logger.log(`Headers: ${headers.join(', ')}`);


 // Find the indices of important columns
 const dateIndex = headers.indexOf("Date Completed");
 const productIndex = headers.indexOf("Product Name");
 const addedToSystemIndex = headers.indexOf("Added to System");
 Logger.log(`Column indices - Date: ${dateIndex}, Product: ${productIndex}, Added to System: ${addedToSystemIndex}`);


 // Object to store combined data
 let combinedData = {};


 // Combine totals for each unique date and product combination
 data.forEach((row, index) => {
   if (row[productIndex]) { // Check if Product Name is not empty
     const key = `${row[dateIndex]}_${row[productIndex]}`; // Date_ProductName
     Logger.log(`Processing row ${index + 2}: ${key}`);
     if (!combinedData[key]) {
       combinedData[key] = row;
     } else {
       for (let i = 3; i < 7; i++) { // Columns D to G (Sachets Used, Packs Created, Bumpers Created, Amazon Boxes Created)
         combinedData[key][i] += row[i];
       }
       // Keep the "Added to System" value as false if any of the combined rows are false
       if (addedToSystemIndex !== -1) {
         combinedData[key][addedToSystemIndex] = combinedData[key][addedToSystemIndex] && row[addedToSystemIndex];
       }
     }
   } else {
     Logger.log(`Skipping row ${index + 2}: Empty product name`);
   }
 });


 // Convert combined data back to array
 const newData = Object.values(combinedData);
 Logger.log(`Combined data rows: ${newData.length}`);


 // Sort the data by date (newest first) and then by product name
 newData.sort((a, b) => {
   const dateA = new Date(a[dateIndex]);
   const dateB = new Date(b[dateIndex]);
   if (dateB.getTime() - dateA.getTime() === 0) {
     return a[productIndex].localeCompare(b[productIndex]); // Sort by product name if dates are the same
   }
   return dateB.getTime() - dateA.getTime();
 });


 // Update existing rows and add new ones
 const existingRows = Math.min(newData.length, data.length);
 if (existingRows > 0) {
   sheet.getRange(2, 1, existingRows, headers.length).setValues(newData.slice(0, existingRows));
 }
 if (newData.length > existingRows) {
   sheet.getRange(existingRows + 2, 1, newData.length - existingRows, headers.length).setValues(newData.slice(existingRows));
 }
 if (data.length > newData.length) {
   sheet.deleteRows(newData.length + 2, data.length - newData.length);
 }


 // Ensure checkboxes are applied to the "Added to System" column
 if (addedToSystemIndex !== -1) {
   const lastRow = sheet.getLastRow();
   Logger.log(`Last row after writing data: ${lastRow}`);
   if (lastRow > 1) {
     const checkboxRange = sheet.getRange(2, addedToSystemIndex + 1, lastRow - 1, 1);
     const rule = SpreadsheetApp.newDataValidation().requireCheckbox().build();
     checkboxRange.setDataValidation(rule);
     Logger.log(`Checkboxes applied to rows 2-${lastRow}`);
   } else {
     Logger.log("No rows to apply checkboxes");
   }
 } else {
   Logger.log("'Added to System' column not found");
 }


 // Reapply formatting to headers
 const headerRange = sheet.getRange(1, 1, 1, headers.length);
 headerRange.setFontWeight("bold");
 headerRange.setBackground("#5b3f86");  // Purple color
 headerRange.setFontColor("#ffffff");   // White text
 Logger.log("Header formatting applied");


 // Auto-resize columns to fit content
 sheet.autoResizeColumns(1, headers.length);
 Logger.log("Columns auto-resized");


 hideExtraColumns(sheet);
 Logger.log("Extra columns hidden");


 // Show a completion message
 SpreadsheetApp.getUi().alert('Totals have been combined successfully!');
 Logger.log("combineTotals function completed");
}