function onEdit(e) {
  if (!e || !e.range) return;

  const sheet = e.source.getActiveSheet();
  const sheetName = 'Daily Transactions';
  const servicesSheetName = 'Services & Products';

  if (sheet.getName() === sheetName) {
    const serviceCol = 2;    // Column B (Service/Product)
    const priceCol = 3;      // Column C (Price)
    const installmentCol = 8; // Column H (Installment?)
    const paymentCol = 9;    // Column I (Payment Made)
    const discountCol = 10;  // Column J (Discount)
    const commissionCol = 7; // Column G (Commission)

    let paymentMade = sheet.getRange(editedRow, paymentCol).getValue();

    if (editedColumn === paymentCol || editedColumn === serviceCol || editedColumn === discountCol) {
      updateFinalPrice(sheet, editedRow, serviceCol, priceCol, installmentCol, paymentCol, discountCol, commissionCol, servicesSheetName);
    }
  }
}

function updateFinalPrice(sheet, row, serviceCol, priceCol, installmentCol, paymentCol, discountCol, commissionCol, servicesSheetName) {
  const servicesSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(servicesSheetName);
  if (!servicesSheet) return;

  const servicesData = servicesSheet.getDataRange().getValues();
  let serviceName = sheet.getRange(row, serviceCol).getValue();
  let installment = sheet.getRange(row, installmentCol).getValue();
  let paymentMade = sheet.getRange(row, paymentCol).getValue();
  let discount = sheet.getRange(row, discountCol).getValue();

  let servicePrice = null;
  let sessionCount = null;

  for (let i = 1; i < servicesData.length; i++) {
    if (servicesData[i][0] === serviceName) {
      servicePrice = servicesData[i][3];
      sessionCount = servicesData[i][1];
      break;
    }
  }

  if (servicePrice === null) return;

  servicePrice = parseFloat(servicePrice);
  if (isNaN(servicePrice)) return;

  discount = discount ? parseFloat(discount) : 0;
  if (discount > 1) discount /= 100;

  let discountedPrice = servicePrice * (1 - discount);

  if (paymentMade === "Custom") {
    sheet.getRange(row, priceCol).setValue("");
    return;
  }

  if (installment === "Yes") {
    let installmentAmount = 0;
    if (paymentMade === '1st Payment (60%)') {
      installmentAmount = discountedPrice * 0.6;
    } else if (paymentMade === '2nd Payment (30%)') {
      installmentAmount = discountedPrice * 0.3;
    } else if (paymentMade === '3rd Payment (10%)') {
      installmentAmount = discountedPrice * 0.1;
    }

    if (installmentAmount > 0) {
      sheet.getRange(row, priceCol).setValue(installmentAmount).setNumberFormat("₱#,##0.00");
      updateCommission(sheet, row, discountedPrice, sessionCount, commissionCol);
      return;
    }
  }

  sheet.getRange(row, priceCol).setValue(discountedPrice).setNumberFormat("₱#,##0.00");
  updateCommission(sheet, row, discountedPrice, sessionCount, commissionCol);
}

function updateCommission(sheet, row, discountedPrice, sessionCount, commissionCol) {
  let perSessionPrice = (sessionCount && [2, 3, 4, 6, 10, 13, 15].includes(sessionCount)) ? discountedPrice / sessionCount : discountedPrice;
  let commission = perSessionPrice * 0.1;
  sheet.getRange(row, commissionCol).setValue(commission).setNumberFormat("₱#,##0.00");
}