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));


  // Get the last row with content
  const lastRow = sheet.getLastRow();


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


  let rowIndex = lastRow + 1;
  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, rowIndex);
      rowIndex++;
      
      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 if (prefix !== '') {
      // If we've reached a prefix with no product name, we've processed all entries
      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, 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 = sheet.getRange(1, 1, 1, lastColumn).getValues()[0].indexOf("Added to System") + 1;


  // 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();
  const headers = data.shift(); // Remove and store headers


  // 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");


  // 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.slice();
      } 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 and sort
  const newData = Object.values(combinedData).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]);
    }
    return dateB.getTime() - dateA.getTime();
  });


  // Clear all content and formatting, including checkboxes
  const lastRow = sheet.getLastRow();
  const lastColumn = sheet.getLastColumn();
  if (lastRow > 1) {
    sheet.getRange(2, 1, lastRow - 1, lastColumn).clearContent().clearDataValidations();
  }


  // Write combined data back to sheet
  if (newData.length > 0) {
    sheet.getRange(2, 1, newData.length, newData[0].length).setValues(newData);
  }


  // Reapply checkboxes only to rows with data in the "Added to System" column
  if (addedToSystemIndex !== -1) {
    const newLastRow = sheet.getLastRow();
    if (newLastRow > 1) {
      const checkboxRange = sheet.getRange(2, addedToSystemIndex + 1, newLastRow - 1, 1);
      const rule = SpreadsheetApp.newDataValidation().requireCheckbox().build();
      checkboxRange.setDataValidation(rule);
    }
  }


  // Auto-resize columns and hide extra columns
  sheet.autoResizeColumns(1, headers.length);
  hideExtraColumns(sheet);


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