function updateInventoryManually() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var inventorySheet = ss.getSheetByName('Inventory');
  var servicesSheet = ss.getSheetByName('Services & Products');
  var transactionsSheet = ss.getSheetByName('Daily Transactions');
  var replenishmentSheet = ss.getSheetByName('Replenishment Rules');

  Logger.log("Script started successfully.");

  if (!inventorySheet || !servicesSheet || !transactionsSheet || !replenishmentSheet) {
    Logger.log("Error: One or more sheets are missing.");
    return;
  }

  var today = new Date();
  var transactionsData = transactionsSheet.getDataRange().getValues();
  var dateHeader = Utilities.formatDate(today, Session.getScriptTimeZone(), 'yyyy-MM-dd');

  var lastColumn = inventorySheet.getLastColumn();
  var previousColumn = lastColumn;
  lastColumn++;

  inventorySheet.setColumnWidth(lastColumn, 100);
  inventorySheet.getRange(1, lastColumn).setValue(dateHeader);

  var headerRow = transactionsData[0];
  var processedColumnIndex = headerRow.indexOf("Processed");

  if (processedColumnIndex === -1) {
    processedColumnIndex = headerRow.length;
    transactionsSheet.getRange(1, processedColumnIndex + 1).setValue("Processed");
  }

  var productTransactionCount = {};

  // Collect transaction data
  for (var i = 1; i < transactionsData.length; i++) {
    var serviceName = transactionsData[i][1];
    var isProcessed = transactionsData[i][processedColumnIndex];

    if (!isProcessed) {
      productTransactionCount[serviceName] = (productTransactionCount[serviceName] || 0) + 1;
      transactionsSheet.getRange(i + 1, processedColumnIndex + 1).setValue("Yes");
      Logger.log("Processing transaction for: " + serviceName + ", Count: " + productTransactionCount[serviceName]);
    }
  }

  // Deduct inventory based on transactions
  for (var serviceName in productTransactionCount) {
    var count = productTransactionCount[serviceName];
    var consumablesList = getConsumablesForService(serviceName, servicesSheet);

    Logger.log("Checking consumables for service: " + serviceName + " -> " + consumablesList.join(", "));

    if (consumablesList.length > 0) {
      for (var j = 0; j < consumablesList.length; j++) {
        var consumable = consumablesList[j].trim();
        updateInventory(consumable, inventorySheet, lastColumn, previousColumn, count, replenishmentSheet, 'A2:A19', 2);
      }
    }

    updateInventory(serviceName, inventorySheet, lastColumn, previousColumn, count, replenishmentSheet, 'A21:A53', 21);
  }

  carryOverBalance(inventorySheet, lastColumn, previousColumn);

  Logger.log("Inventory update process completed.");
}

// Retrieve consumables linked to a service
function getConsumablesForService(serviceName, servicesSheet) {
  var data = servicesSheet.getDataRange().getValues();
  for (var i = 1; i < data.length; i++) {
    if (data[i][0] == serviceName) {
      return data[i].slice(4, 13).filter(Boolean); // Extract non-empty consumables from columns E to M
    }
  }
  return [];
}

// Retrieve replenishment settings
function getReplenishmentDetails(itemName, replenishmentSheet) {
  var replenishmentData = replenishmentSheet.getDataRange().getValues();
  for (var i = 1; i < replenishmentData.length; i++) {
    if (replenishmentData[i][0] === itemName) {
      Logger.log("Replenishment rules for " + itemName + ": Threshold = " + replenishmentData[i][1] + ", Replenishment Amount = " + replenishmentData[i][2]);
      return {
        threshold: replenishmentData[i][1] || 0,
        replenishmentAmount: replenishmentData[i][2] || 0
      };
    }
  }
  Logger.log("No replenishment rules found for " + itemName);
  return { threshold: 0, replenishmentAmount: 0 };
}

// Deduct inventory and replenish if needed
function updateInventory(itemName, inventorySheet, lastColumn, previousColumn, count, replenishmentSheet, range, startRow) {
  var itemRange = inventorySheet.getRange(range).getValues();
  var replenishmentDetails = getReplenishmentDetails(itemName, replenishmentSheet);
  var threshold = replenishmentDetails.threshold;
  var replenishmentAmount = replenishmentDetails.replenishmentAmount;

  Logger.log("Updating inventory for: " + itemName);

  for (var i = 0; i < itemRange.length; i++) {
    if (itemRange[i][0] === itemName) {
      var previousBalance = inventorySheet.getRange(i + startRow, previousColumn).getValue() || inventorySheet.getRange(i + startRow, 2).getValue();
      var newBalance = previousBalance - count;
      var balanceCell = inventorySheet.getRange(i + startRow, lastColumn);

      Logger.log("Previous Balance: " + previousBalance + ", New Balance: " + newBalance);

      if (newBalance <= threshold && replenishmentAmount > 0) {
        newBalance += replenishmentAmount;
        balanceCell.setBackground("#EE82EE"); // Violet for replenishment
        Logger.log(itemName + " reached threshold, replenishing " + replenishmentAmount + " units.");
      } else if (newBalance !== previousBalance) {
        balanceCell.setBackground("#FFFF00"); // Yellow for deduction change
      }

      balanceCell.setValue(newBalance);
      return;
    }
  }

  Logger.log("Item not found in inventory: " + itemName);
}

// Carry over balances
function carryOverBalance(inventorySheet, lastColumn, previousColumn) {
  var allItemsRange = inventorySheet.getRange('A2:A53').getValues();

  for (var i = 0; i < allItemsRange.length; i++) {
    var currentBalanceCell = inventorySheet.getRange(i + 2, lastColumn);
    var previousBalance = inventorySheet.getRange(i + 2, previousColumn).getValue();

    if (!currentBalanceCell.getValue()) {
      currentBalanceCell.setValue(previousBalance || 0);
      Logger.log("Carrying over balance for row " + (i + 2) + ": " + previousBalance);
    }
  }
}