/**
 * @OnlyCurrentDoc
 *
 * Teirazone - Madran Su Dağıtım Takip Sistemi
 * Google Sheets otomatik operasyon scripti.
 *
 * Bu dosya Google Sheets içinde kullanıcı tarafından çalıştırılır.
 * Google Sheets API, OAuth, backend, veritabanı veya canlı site bağlantısı kurmaz.
 * Gerçek müşteri, telefon, adres veya sipariş verisi oluşturmaz.
 *
 * Yeni boş dosya için:
 *   setupMadranSuDagitim()
 *
 * İçinde veri olan dosyada mümkün olduğunca veri silmeden otomasyon eklemek için:
 *   upgradeMadranSuAutomation()
 */

const ROW_LIMIT = 500;
const DEFAULT_PRODUCT = 'Damacana Su';
const DEFAULT_PAYMENT = 'Nakit';
const DEFAULT_STATUS = 'Bekliyor';
const DEFAULT_WATER_PRICE = 115;
const DEFAULT_DEPOSIT_PRICE = 85;
const DEFAULT_PUMP_PRICE = 0;

const SHEETS = {
  customers: 'Müşteri Listesi',
  orders: 'Günlük Siparişler',
  route: 'Rota Planı',
  deposits: 'Depozito Takibi',
  report: 'Gün Sonu Raporu',
  settings: 'Ayarlar',
  quickOrder: 'Hızlı Sipariş',
};

const CUSTOMER_COL = {
  NO: 1,
  NAME: 2,
  PHONE: 3,
  WHATSAPP: 4,
  NEIGHBORHOOD: 5,
  ADDRESS: 6,
  LOCATION: 7,
  DEFAULT_PRODUCT: 8,
  DEPOSIT_BOTTLES: 9,
  HAS_PUMP: 10,
  PAYMENT: 11,
  NOTE: 12,
  LAST_ORDER_DATE: 13,
  STATUS: 14,
};

const ORDER_COL = {
  DATE: 1,
  TIME: 2,
  ORDER_NO: 3,
  CUSTOMER_NO: 4,
  CUSTOMER_NAME: 5,
  PHONE: 6,
  NEIGHBORHOOD: 7,
  ADDRESS: 8,
  LOCATION: 9,
  PRODUCT: 10,
  QUANTITY: 11,
  UNIT_PRICE: 12,
  NEW_DEPOSIT_QTY: 13,
  DEPOSIT_AMOUNT: 14,
  PUMP_AMOUNT: 15,
  TOTAL: 16,
  PAYMENT: 17,
  MOTOR: 18,
  SEQUENCE: 19,
  STATUS: 20,
  DELIVERED_AT: 21,
  NOTE: 22,
};

const QUICK_ROW = {
  PHONE: 2,
  CUSTOMER_NAME: 3,
  NEIGHBORHOOD: 4,
  ADDRESS: 5,
  LOCATION: 6,
  QUANTITY: 7,
  NEW_DEPOSIT_QTY: 8,
  PAYMENT: 9,
  MOTOR: 10,
  NOTE: 11,
};

const SHEET_CONFIGS = [
  {
    name: SHEETS.customers,
    headers: [
      'Müşteri No',
      'Ad Soyad / Firma',
      'Telefon',
      'WhatsApp',
      'Mahalle',
      'Adres Detayı',
      'Konum Linki',
      'Varsayılan Ürün',
      'Depozitolu Damacana Sayısı',
      'Emanet Pompa Var mı?',
      'Ödeme Tipi',
      'Not',
      'Son Sipariş Tarihi',
      'Müşteri Durumu',
    ],
    widths: [120, 190, 130, 110, 140, 230, 220, 150, 210, 180, 130, 220, 160, 150],
  },
  {
    name: SHEETS.orders,
    headers: [
      'Tarih',
      'Saat',
      'Sipariş No',
      'Müşteri No',
      'Müşteri Adı',
      'Telefon',
      'Mahalle',
      'Adres',
      'Konum Linki',
      'Ürün',
      'Adet',
      'Birim Fiyat',
      'Yeni Depozito Adedi',
      'Depozito Tutarı',
      'Pompa Tutarı',
      'Toplam Tutar',
      'Ödeme Tipi',
      'Motor',
      'Sıra No',
      'Durum',
      'Teslim Saati',
      'Not',
    ],
    widths: [115, 90, 140, 120, 180, 130, 140, 220, 220, 150, 80, 120, 165, 140, 120, 130, 120, 110, 90, 140, 110, 230],
  },
  {
    name: SHEETS.route,
    headers: [
      'Tarih',
      'Motor',
      'Sıra No',
      'Müşteri Adı',
      'Mahalle',
      'Adres Kısa',
      'Konum Linki',
      'Ürün',
      'Adet',
      'Toplam Tutar',
      'Ödeme Tipi',
      'Durum',
      'Not',
    ],
    widths: [115, 110, 90, 180, 140, 220, 220, 150, 80, 130, 120, 140, 220],
  },
  {
    name: SHEETS.deposits,
    headers: [
      'Tarih',
      'Müşteri No',
      'Müşteri Adı',
      'Telefon',
      'İşlem Tipi',
      'Damacana Adedi',
      'Depozito Tutarı',
      'Pompa Var mı?',
      'Tahsil Edildi mi?',
      'İade Edildi mi?',
      'Not',
    ],
    widths: [115, 120, 190, 130, 160, 140, 140, 130, 150, 140, 260],
  },
  {
    name: SHEETS.report,
    headers: [
      'Tarih',
      'Toplam Sipariş',
      'Teslim Edilen',
      'Bekleyen',
      'İptal',
      'Toplam Damacana',
      'Toplam Ciro',
      'Nakit Tahsilat',
      'IBAN / Kart Tahsilat',
      'Depozito Geliri',
      'Motor 1 Teslimat',
      'Motor 2 Teslimat',
      'En Yoğun Mahalle',
      'Not',
    ],
    widths: [115, 140, 130, 110, 90, 150, 130, 140, 170, 140, 150, 150, 170, 240],
  },
  {
    name: SHEETS.settings,
    headers: ['Ürün', 'Birim Fiyat', 'Aktif mi?', 'Not'],
    widths: [180, 130, 110, 300],
  },
  {
    name: SHEETS.quickOrder,
    headers: ['Alan', 'Değer', 'Not'],
    widths: [190, 260, 360],
  },
];

const LISTS = {
  products: ['Damacana Su', 'Depozito', 'Pompa', 'Ekstra Hizmet'],
  paymentTypes: ['Nakit', 'IBAN', 'Kart', 'Veresiye'],
  motors: ['Motor 1', 'Motor 2'],
  orderStatuses: ['Bekliyor', 'Yolda', 'Teslim Edildi', 'İptal', 'Ulaşılamadı'],
  depositActions: ['Yeni Depozito', 'Damacana İade', 'Pompa Verildi', 'Pompa İade', 'Depozito İade'],
  yesNo: ['Evet', 'Hayır'],
  whatsappState: ['Var', 'Yok'],
  customerStatuses: ['Aktif', 'Pasif', 'Kara Liste', 'Yeni'],
};

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Madran Su')
    .addItem('Hızlı Siparişi Listeye Ekle', 'addQuickOrderToList')
    .addItem('Günlük Siparişleri Yeniden Hesapla', 'recalculateAllOrders')
    .addItem('Rota Planını Oluştur', 'createRoutePlan')
    .addItem('Gün Sonu Raporunu Yenile', 'refreshEndOfDayReport')
    .addSeparator()
    .addItem('Ayarları Güncelle / Varsayılanları Kur', 'updateDefaultSettings')
    .addToUi();
}

function setupMadranSuDagitim() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  SHEET_CONFIGS.forEach((config) => {
    const sheet = ensureSheet_(spreadsheet, config.name);
    resetSheet_(sheet, config);
  });

  setupQuickOrderSheet_(spreadsheet.getSheetByName(SHEETS.quickOrder), true);
  fillSettingsSheet_(spreadsheet.getSheetByName(SHEETS.settings), true);
  applyAutomationSetup_(spreadsheet);

  SpreadsheetApp.flush();
  spreadsheet.toast('Madran Su Dağıtım Takip Sistemi otomasyonlu olarak hazırlandı.', 'Teirazone', 5);
}

function upgradeMadranSuAutomation() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  SHEET_CONFIGS.forEach((config) => {
    const sheet = ensureSheet_(spreadsheet, config.name);
    ensureSheetSize_(sheet, ROW_LIMIT, config.headers.length);
    applyHeaderAndLayout_(sheet, config);
  });

  setupQuickOrderSheet_(spreadsheet.getSheetByName(SHEETS.quickOrder), false);
  fillSettingsSheet_(spreadsheet.getSheetByName(SHEETS.settings), false);
  applyAutomationSetup_(spreadsheet);
  fixToplamTutarErrors();

  SpreadsheetApp.flush();
  spreadsheet.toast('Madran Su otomasyonları veri silmeden güncellendi.', 'Teirazone', 5);
}

function updateDefaultSettings() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const settings = ensureSheet_(spreadsheet, SHEETS.settings);
  const config = getConfig_(SHEETS.settings);

  ensureSheetSize_(settings, ROW_LIMIT, config.headers.length);
  applyHeaderAndLayout_(settings, config);
  fillSettingsSheet_(settings, true);
  applyValidations_(spreadsheet);
  applyFormats_(spreadsheet);

  spreadsheet.toast('Varsayılan fiyatlar güncellendi: Damacana 115 TL, Depozito 85 TL, Pompa 0 TL.', 'Teirazone', 5);
}

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

  const sheet = e.range.getSheet();
  if (sheet.getName() !== SHEETS.orders) return;
  if (e.range.getRow() < 2) return;

  const startRow = e.range.getRow();
  const endRow = startRow + e.range.getNumRows() - 1;
  for (let row = startRow; row <= endRow; row += 1) {
    processOrderRow_(sheet, row);
  }
}

function addQuickOrderToList() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const quick = ensureSheet_(spreadsheet, SHEETS.quickOrder);
  const orders = ensureSheet_(spreadsheet, SHEETS.orders);
  const orderConfig = getConfig_(SHEETS.orders);

  ensureSheetSize_(orders, ROW_LIMIT, orderConfig.headers.length);
  applyHeaderAndLayout_(orders, orderConfig);

  const quickOrder = readQuickOrder_(quick);
  if (!quickOrder.phone && !quickOrder.customerName) {
    safeAlert_('Hızlı sipariş için en az Telefon veya Müşteri Adı girin.');
    return;
  }

  const customerNo = ensureCustomerFromQuickOrder_(spreadsheet, quickOrder);
  const row = Math.max(orders.getLastRow() + 1, 2);

  orders.getRange(row, ORDER_COL.CUSTOMER_NO).setValue(customerNo || '');
  orders.getRange(row, ORDER_COL.CUSTOMER_NAME).setValue(quickOrder.customerName || '');
  orders.getRange(row, ORDER_COL.PHONE).setValue(quickOrder.phone || '');
  orders.getRange(row, ORDER_COL.NEIGHBORHOOD).setValue(quickOrder.neighborhood || '');
  orders.getRange(row, ORDER_COL.ADDRESS).setValue(quickOrder.address || '');
  orders.getRange(row, ORDER_COL.LOCATION).setValue(quickOrder.location || '');
  orders.getRange(row, ORDER_COL.QUANTITY).setValue(quickOrder.quantity || 1);
  orders.getRange(row, ORDER_COL.NEW_DEPOSIT_QTY).setValue(quickOrder.newDepositQty || 0);
  orders.getRange(row, ORDER_COL.PAYMENT).setValue(quickOrder.payment || DEFAULT_PAYMENT);
  orders.getRange(row, ORDER_COL.MOTOR).setValue(quickOrder.motor || '');
  orders.getRange(row, ORDER_COL.NOTE).setValue(quickOrder.note || '');

  processOrderRow_(orders, row);
  clearQuickOrderForm_(quick);

  spreadsheet.toast('Hızlı sipariş günlük listeye eklendi.', 'Madran Su', 5);
}

function recalculateAllOrders() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const orders = spreadsheet.getSheetByName(SHEETS.orders);
  if (!orders) return;

  const lastRow = Math.min(Math.max(orders.getLastRow(), 2), ROW_LIMIT);
  for (let row = 2; row <= lastRow; row += 1) {
    processOrderRow_(orders, row);
  }

  spreadsheet.toast('Günlük siparişler yeniden hesaplandı.', 'Madran Su', 5);
}

function fixToplamTutarErrors() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const orders = spreadsheet.getSheetByName(SHEETS.orders);
  if (!orders) return;

  const lastRow = Math.min(Math.max(orders.getLastRow(), 2), ROW_LIMIT);
  for (let row = 2; row <= lastRow; row += 1) {
    const rowValues = getOrderRowValues_(orders, row);
    if (!orderRowHasInput_(rowValues)) {
      orders.getRange(row, ORDER_COL.TOTAL).clearContent();
      continue;
    }

    processOrderRow_(orders, row);
  }

  spreadsheet.toast('Toplam Tutar alanları formülsüz olarak düzeltildi.', 'Madran Su', 5);
}

function createRoutePlan() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const orders = spreadsheet.getSheetByName(SHEETS.orders);
  const route = ensureSheet_(spreadsheet, SHEETS.route);
  const routeConfig = getConfig_(SHEETS.route);

  ensureSheetSize_(route, ROW_LIMIT, routeConfig.headers.length);
  applyHeaderAndLayout_(route, routeConfig);
  clearDataRows_(route, routeConfig.headers.length);

  if (!orders) {
    spreadsheet.toast('Günlük Siparişler sayfası bulunamadı.', 'Madran Su', 5);
    return;
  }

  const today = new Date();
  const orderRows = getOrderRows_(orders)
    .filter((order) => isSameDay_(order.date, today))
    .filter((order) => normalizeText_(order.status) !== normalizeText_('İptal'))
    .sort((a, b) => {
      const motorCompare = String(a.motor || '').localeCompare(String(b.motor || ''), 'tr');
      if (motorCompare !== 0) return motorCompare;
      return parseNumber_(a.sequence) - parseNumber_(b.sequence);
    });

  if (orderRows.length) {
    const routeRows = orderRows.map((order) => [
      order.date,
      order.motor,
      order.sequence,
      order.customerName,
      order.neighborhood,
      order.address,
      order.location,
      order.product,
      order.quantity,
      order.total,
      order.payment,
      order.status,
      order.note,
    ]);

    route.getRange(2, 1, routeRows.length, routeRows[0].length).setValues(routeRows);
  }

  applyFormats_(spreadsheet);
  applyStatusConditionalFormatting_(route, 12);
  spreadsheet.toast('Rota Planı bugünün siparişlerine göre oluşturuldu.', 'Madran Su', 5);
}

function refreshEndOfDayReport() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const orders = spreadsheet.getSheetByName(SHEETS.orders);
  const report = ensureSheet_(spreadsheet, SHEETS.report);
  const reportConfig = getConfig_(SHEETS.report);

  ensureSheetSize_(report, ROW_LIMIT, reportConfig.headers.length);
  applyHeaderAndLayout_(report, reportConfig);

  const today = new Date();
  const todayOrders = orders ? getOrderRows_(orders).filter((order) => isSameDay_(order.date, today)) : [];
  const activeOrders = todayOrders.filter((order) => normalizeText_(order.status) !== normalizeText_('İptal'));
  const deliveredOrders = todayOrders.filter((order) => normalizeText_(order.status) === normalizeText_('Teslim Edildi'));

  const summary = [
    today,
    todayOrders.length,
    deliveredOrders.length,
    todayOrders.filter((order) => normalizeText_(order.status) === normalizeText_('Bekliyor')).length,
    todayOrders.filter((order) => normalizeText_(order.status) === normalizeText_('İptal')).length,
    sum_(activeOrders.map((order) => order.quantity)),
    sum_(activeOrders.map((order) => order.total)),
    sum_(activeOrders.filter((order) => normalizeText_(order.payment) === normalizeText_('Nakit')).map((order) => order.total)),
    sum_(
      activeOrders
        .filter((order) => ['iban', 'kart'].indexOf(normalizeText_(order.payment)) >= 0)
        .map((order) => order.total)
    ),
    sum_(activeOrders.map((order) => order.depositAmount)),
    deliveredOrders.filter((order) => normalizeText_(order.motor) === normalizeText_('Motor 1')).length,
    deliveredOrders.filter((order) => normalizeText_(order.motor) === normalizeText_('Motor 2')).length,
    mostCommon_(activeOrders.map((order) => order.neighborhood).filter(Boolean)),
    'Otomatik rapor: Bugünün siparişlerinden hesaplandı.',
  ];

  const row = findReportRowForDate_(report, today) || Math.max(report.getLastRow() + 1, 2);
  report.getRange(row, 1, 1, summary.length).setValues([summary]);
  applyFormats_(spreadsheet);

  spreadsheet.toast('Gün sonu raporu yenilendi.', 'Madran Su', 5);
}

function applyAutomationSetup_(spreadsheet) {
  applyValidations_(spreadsheet);
  applyFormats_(spreadsheet);
  applyStatusConditionalFormatting_(spreadsheet.getSheetByName(SHEETS.orders), ORDER_COL.STATUS);
  applyStatusConditionalFormatting_(spreadsheet.getSheetByName(SHEETS.route), 12);
}

function processOrderRow_(orders, row) {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const settings = spreadsheet.getSheetByName(SHEETS.settings);

  let values = getOrderRowValues_(orders, row);
  if (!orderRowHasInput_(values)) return;

  if (values.customerNo) {
    pullCustomerIntoOrder_(spreadsheet, orders, row, values.customerNo);
    values = getOrderRowValues_(orders, row);
  }

  const now = new Date();
  const date = values.date || now;
  const time = values.time || now;
  const product = values.product || DEFAULT_PRODUCT;
  const quantity = parseNumber_(values.quantity) || 1;
  const newDepositQty = parseNumber_(values.newDepositQty);
  const unitPrice = getProductPrice_(settings, product);
  const depositPrice = getProductPrice_(settings, 'Depozito');
  const depositAmount = newDepositQty * depositPrice;
  const pumpAmount = DEFAULT_PUMP_PRICE;
  const total = calculateOrderTotal_(quantity, unitPrice, depositAmount, pumpAmount);
  const payment = values.payment || DEFAULT_PAYMENT;
  const status = values.status || DEFAULT_STATUS;
  const orderNo = values.orderNo || generateOrderNo_(orders, date);

  orders.getRange(row, ORDER_COL.DATE).setValue(date);
  orders.getRange(row, ORDER_COL.TIME).setValue(time);
  orders.getRange(row, ORDER_COL.ORDER_NO).setValue(orderNo);
  orders.getRange(row, ORDER_COL.PRODUCT).setValue(product);
  orders.getRange(row, ORDER_COL.QUANTITY).setValue(quantity);
  orders.getRange(row, ORDER_COL.UNIT_PRICE).setValue(unitPrice);
  orders.getRange(row, ORDER_COL.NEW_DEPOSIT_QTY).setValue(newDepositQty);
  orders.getRange(row, ORDER_COL.DEPOSIT_AMOUNT).setValue(depositAmount);
  orders.getRange(row, ORDER_COL.PUMP_AMOUNT).setValue(pumpAmount);
  orders.getRange(row, ORDER_COL.TOTAL).setValue(total);
  orders.getRange(row, ORDER_COL.PAYMENT).setValue(payment);
  orders.getRange(row, ORDER_COL.STATUS).setValue(status);

  if (newDepositQty > 0) {
    upsertDepositRecordForOrder_(spreadsheet, orders, row, orderNo, newDepositQty, depositAmount);
  }
}

function calculateOrderTotal_(quantity, unitPrice, depositAmount, pumpAmount) {
  return parseNumber_(quantity) * parseNumber_(unitPrice) + parseNumber_(depositAmount) + parseNumber_(pumpAmount);
}

function pullCustomerIntoOrder_(spreadsheet, orders, row, customerNo) {
  const customer = findCustomerByNo_(spreadsheet, customerNo);
  if (!customer) return;

  setCellIfBlank_(orders, row, ORDER_COL.CUSTOMER_NAME, customer.name);
  setCellIfBlank_(orders, row, ORDER_COL.PHONE, customer.phone);
  setCellIfBlank_(orders, row, ORDER_COL.NEIGHBORHOOD, customer.neighborhood);
  setCellIfBlank_(orders, row, ORDER_COL.ADDRESS, customer.address);
  setCellIfBlank_(orders, row, ORDER_COL.LOCATION, customer.location);
  setCellIfBlank_(orders, row, ORDER_COL.PRODUCT, customer.defaultProduct || DEFAULT_PRODUCT);
  setCellIfBlank_(orders, row, ORDER_COL.PAYMENT, customer.payment || DEFAULT_PAYMENT);
}

function upsertDepositRecordForOrder_(spreadsheet, orders, orderRow, orderNo, newDepositQty, depositAmount) {
  const deposits = ensureSheet_(spreadsheet, SHEETS.deposits);
  const depositConfig = getConfig_(SHEETS.deposits);

  ensureSheetSize_(deposits, ROW_LIMIT, depositConfig.headers.length);
  applyHeaderAndLayout_(deposits, depositConfig);

  const values = getOrderRowValues_(orders, orderRow);
  const note = `Sipariş No: ${orderNo}`;
  const existingRow = findDepositRowByOrderNo_(deposits, orderNo);
  const targetRow = existingRow || Math.max(deposits.getLastRow() + 1, 2);
  const depositRow = [
    values.date,
    values.customerNo,
    values.customerName,
    values.phone,
    'Yeni Depozito',
    newDepositQty,
    depositAmount,
    'Hayır',
    'Hayır',
    'Hayır',
    values.note ? `${note} | ${values.note}` : note,
  ];

  deposits.getRange(targetRow, 1, 1, depositRow.length).setValues([depositRow]);
  applyFormats_(spreadsheet);
}

function findDepositRowByOrderNo_(sheet, orderNo) {
  if (!orderNo || sheet.getLastRow() < 2) return null;

  const notes = sheet.getRange(2, 11, sheet.getLastRow() - 1, 1).getValues();
  for (let index = 0; index < notes.length; index += 1) {
    if (String(notes[index][0] || '').indexOf(orderNo) >= 0) {
      return index + 2;
    }
  }

  return null;
}

function ensureCustomerFromQuickOrder_(spreadsheet, quickOrder) {
  if (!quickOrder.phone) return '';

  const customers = ensureSheet_(spreadsheet, SHEETS.customers);
  const customerConfig = getConfig_(SHEETS.customers);
  ensureSheetSize_(customers, ROW_LIMIT, customerConfig.headers.length);
  applyHeaderAndLayout_(customers, customerConfig);

  const existing = findCustomerByPhone_(spreadsheet, quickOrder.phone);
  if (existing) return existing.customerNo;

  const customerNo = generateCustomerNo_(customers);
  const row = Math.max(customers.getLastRow() + 1, 2);
  const customerRow = [
    customerNo,
    quickOrder.customerName || '',
    quickOrder.phone,
    'Var',
    quickOrder.neighborhood || '',
    quickOrder.address || '',
    quickOrder.location || '',
    DEFAULT_PRODUCT,
    0,
    'Hayır',
    quickOrder.payment || DEFAULT_PAYMENT,
    quickOrder.note || '',
    '',
    'Yeni',
  ];

  customers.getRange(row, 1, 1, customerRow.length).setValues([customerRow]);
  return customerNo;
}

function readQuickOrder_(quick) {
  return {
    phone: getQuickValue_(quick, QUICK_ROW.PHONE),
    customerName: getQuickValue_(quick, QUICK_ROW.CUSTOMER_NAME),
    neighborhood: getQuickValue_(quick, QUICK_ROW.NEIGHBORHOOD),
    address: getQuickValue_(quick, QUICK_ROW.ADDRESS),
    location: getQuickValue_(quick, QUICK_ROW.LOCATION),
    quantity: parseNumber_(getQuickValue_(quick, QUICK_ROW.QUANTITY)) || 1,
    newDepositQty: parseNumber_(getQuickValue_(quick, QUICK_ROW.NEW_DEPOSIT_QTY)),
    payment: getQuickValue_(quick, QUICK_ROW.PAYMENT) || DEFAULT_PAYMENT,
    motor: getQuickValue_(quick, QUICK_ROW.MOTOR),
    note: getQuickValue_(quick, QUICK_ROW.NOTE),
  };
}

function getQuickValue_(sheet, row) {
  return sheet.getRange(row, 2).getValue();
}

function clearQuickOrderForm_(sheet) {
  sheet.getRange(QUICK_ROW.PHONE, 2, QUICK_ROW.NOTE - QUICK_ROW.PHONE + 1, 1).clearContent();
}

function setupQuickOrderSheet_(sheet, clearForm) {
  const labels = [
    ['Telefon', '', 'Telefon veya WhatsApp üzerinden gelen kayıt.'],
    ['Müşteri Adı', '', 'Yeni müşteri ise bu alan müşteri listesine aktarılır.'],
    ['Mahalle', '', 'Tire içi dağıtım bölgesi.'],
    ['Adres', '', 'Gerçek adres kullanıcı tarafından girilir.'],
    ['Konum Linki', '', 'Google Maps bağlantısı varsa eklenir.'],
    ['Adet', '', 'Boş bırakılırsa 1 kabul edilir.'],
    ['Yeni Depozito Adedi', '', 'Boş bırakılırsa 0 kabul edilir.'],
    ['Ödeme Tipi', '', 'Nakit, IBAN, Kart veya Veresiye.'],
    ['Motor', '', 'Motor 1 veya Motor 2 seçilebilir.'],
    ['Not', '', 'Sipariş notu.'],
  ];

  const config = getConfig_(SHEETS.quickOrder);
  ensureSheetSize_(sheet, ROW_LIMIT, config.headers.length);
  applyHeaderAndLayout_(sheet, config);
  sheet.getRange(2, 1, labels.length, labels[0].length).setValues(labels);
  if (clearForm) {
    clearQuickOrderForm_(sheet);
  }
  sheet.getRange('B7:B8').setNumberFormat('#,##0');
  setSingleCellValidation_(sheet.getRange('B9'), LISTS.paymentTypes);
  setSingleCellValidation_(sheet.getRange('B10'), LISTS.motors);
}

function fillSettingsSheet_(sheet, overwriteExisting) {
  const defaults = [
    ['Damacana Su', DEFAULT_WATER_PRICE, 'Evet', 'Güncel satış fiyatı'],
    ['Depozito', DEFAULT_DEPOSIT_PRICE, 'Evet', 'Güncel depozito ücreti'],
    ['Pompa', DEFAULT_PUMP_PRICE, 'Hayır', 'Pompa takibi yapılmayacak'],
    ['Ekstra Hizmet', 0, 'Hayır', 'Varsa sonra eklenir'],
  ];

  defaults.forEach((item) => upsertSetting_(sheet, item, overwriteExisting));
}

function upsertSetting_(sheet, item, overwriteExisting) {
  const product = item[0];
  const lastRow = Math.max(sheet.getLastRow(), 1);
  const values = lastRow > 1 ? sheet.getRange(2, 1, lastRow - 1, 4).getValues() : [];

  for (let index = 0; index < values.length; index += 1) {
    if (normalizeText_(values[index][0]) === normalizeText_(product)) {
      const row = index + 2;
      if (overwriteExisting || isBlank_(sheet.getRange(row, 2).getValue())) {
        sheet.getRange(row, 1, 1, item.length).setValues([item]);
      }
      return;
    }
  }

  const row = Math.max(sheet.getLastRow() + 1, 2);
  sheet.getRange(row, 1, 1, item.length).setValues([item]);
}

function applyValidations_(spreadsheet) {
  const customers = spreadsheet.getSheetByName(SHEETS.customers);
  const orders = spreadsheet.getSheetByName(SHEETS.orders);
  const deposit = spreadsheet.getSheetByName(SHEETS.deposits);
  const settings = spreadsheet.getSheetByName(SHEETS.settings);
  const quick = spreadsheet.getSheetByName(SHEETS.quickOrder);

  setListValidation_(customers, CUSTOMER_COL.WHATSAPP, LISTS.whatsappState);
  setListValidation_(customers, CUSTOMER_COL.HAS_PUMP, LISTS.yesNo);
  setListValidation_(customers, CUSTOMER_COL.PAYMENT, LISTS.paymentTypes);
  setListValidation_(customers, CUSTOMER_COL.STATUS, LISTS.customerStatuses);

  setListValidation_(orders, ORDER_COL.PRODUCT, LISTS.products);
  setListValidation_(orders, ORDER_COL.PAYMENT, LISTS.paymentTypes);
  setListValidation_(orders, ORDER_COL.MOTOR, LISTS.motors);
  setListValidation_(orders, ORDER_COL.STATUS, LISTS.orderStatuses);

  setListValidation_(deposit, 5, LISTS.depositActions);
  setListValidation_(deposit, 8, LISTS.yesNo);
  setListValidation_(deposit, 9, LISTS.yesNo);
  setListValidation_(deposit, 10, LISTS.yesNo);

  setListValidation_(settings, 3, LISTS.yesNo);
  setSingleCellValidation_(quick.getRange('B9'), LISTS.paymentTypes);
  setSingleCellValidation_(quick.getRange('B10'), LISTS.motors);
}

function setListValidation_(sheet, column, values) {
  if (!sheet) return;

  const rule = SpreadsheetApp.newDataValidation()
    .requireValueInList(values, true)
    .setAllowInvalid(false)
    .build();

  sheet.getRange(2, column, ROW_LIMIT - 1, 1).setDataValidation(rule);
}

function setSingleCellValidation_(range, values) {
  const rule = SpreadsheetApp.newDataValidation()
    .requireValueInList(values, true)
    .setAllowInvalid(false)
    .build();

  range.setDataValidation(rule);
}

function applyFormats_(spreadsheet) {
  const customers = spreadsheet.getSheetByName(SHEETS.customers);
  const orders = spreadsheet.getSheetByName(SHEETS.orders);
  const route = spreadsheet.getSheetByName(SHEETS.route);
  const deposit = spreadsheet.getSheetByName(SHEETS.deposits);
  const report = spreadsheet.getSheetByName(SHEETS.report);
  const settings = spreadsheet.getSheetByName(SHEETS.settings);

  customers.getRange('M2:M500').setNumberFormat('yyyy-mm-dd');

  orders.getRange('A2:A500').setNumberFormat('yyyy-mm-dd');
  orders.getRange('B2:B500').setNumberFormat('hh:mm');
  orders.getRange('K2:P500').setNumberFormat('#,##0.00');
  orders.getRange('U2:U500').setNumberFormat('hh:mm');

  route.getRange('A2:A500').setNumberFormat('yyyy-mm-dd');
  route.getRange('I2:J500').setNumberFormat('#,##0.00');

  deposit.getRange('A2:A500').setNumberFormat('yyyy-mm-dd');
  deposit.getRange('F2:G500').setNumberFormat('#,##0.00');

  report.getRange('A2:A500').setNumberFormat('yyyy-mm-dd');
  report.getRange('B2:F500').setNumberFormat('#,##0');
  report.getRange('G2:J500').setNumberFormat('#,##0.00');
  report.getRange('K2:L500').setNumberFormat('#,##0');

  settings.getRange('B2:B500').setNumberFormat('#,##0.00');
}

function applyStatusConditionalFormatting_(sheet, statusColumn) {
  if (!sheet) return;

  const statusRange = sheet.getRange(2, statusColumn, ROW_LIMIT - 1, 1);
  const rules = [
    buildStatusRule_(statusRange, 'Bekliyor', '#fff2cc'),
    buildStatusRule_(statusRange, 'Yolda', '#cfe2f3'),
    buildStatusRule_(statusRange, 'Teslim Edildi', '#d9ead3'),
    buildStatusRule_(statusRange, 'İptal', '#f4cccc'),
    buildStatusRule_(statusRange, 'Ulaşılamadı', '#fce5cd'),
  ];

  const otherRules = sheet
    .getConditionalFormatRules()
    .filter((rule) => !rule.getRanges().some((range) => range.getA1Notation() === statusRange.getA1Notation()));

  sheet.setConditionalFormatRules(otherRules.concat(rules));
}

function buildStatusRule_(range, value, background) {
  return SpreadsheetApp.newConditionalFormatRule()
    .whenTextEqualTo(value)
    .setBackground(background)
    .setFontColor('#1a1712')
    .setRanges([range])
    .build();
}

function ensureSheet_(spreadsheet, name) {
  return spreadsheet.getSheetByName(name) || spreadsheet.insertSheet(name);
}

function resetSheet_(sheet, config) {
  removeFilter_(sheet);
  ensureSheetSize_(sheet, ROW_LIMIT, config.headers.length);
  sheet.clear();
  sheet.clearConditionalFormatRules();
  applyHeaderAndLayout_(sheet, config);
}

function applyHeaderAndLayout_(sheet, config) {
  removeFilter_(sheet);

  const columns = config.headers.length;
  const headerRange = sheet.getRange(1, 1, 1, columns);
  headerRange
    .setValues([config.headers])
    .setFontWeight('bold')
    .setFontColor('#f0ce82')
    .setBackground('#11100d')
    .setHorizontalAlignment('center')
    .setVerticalAlignment('middle')
    .setWrap(true);

  sheet.setFrozenRows(1);
  sheet.getRange(1, 1, 1, columns).createFilter();
  sheet.getRange(1, 1, ROW_LIMIT, columns).setVerticalAlignment('middle');
  sheet.getRange(2, 1, ROW_LIMIT - 1, columns).setWrap(true);

  config.widths.forEach((width, index) => {
    sheet.setColumnWidth(index + 1, width);
  });
}

function removeFilter_(sheet) {
  const filter = sheet.getFilter();
  if (filter) {
    filter.remove();
  }
}

function clearDataRows_(sheet, columns) {
  const rows = Math.max(sheet.getMaxRows() - 1, 1);
  sheet.getRange(2, 1, rows, columns).clearContent();
}

function ensureSheetSize_(sheet, minRows, minColumns) {
  if (sheet.getMaxRows() < minRows) {
    sheet.insertRowsAfter(sheet.getMaxRows(), minRows - sheet.getMaxRows());
  }

  if (sheet.getMaxColumns() < minColumns) {
    sheet.insertColumnsAfter(sheet.getMaxColumns(), minColumns - sheet.getMaxColumns());
  }
}

function getConfig_(sheetName) {
  return SHEET_CONFIGS.find((config) => config.name === sheetName);
}

function getOrderRowValues_(sheet, row) {
  const values = sheet.getRange(row, 1, 1, ORDER_COL.NOTE).getValues()[0];

  return {
    date: values[ORDER_COL.DATE - 1],
    time: values[ORDER_COL.TIME - 1],
    orderNo: values[ORDER_COL.ORDER_NO - 1],
    customerNo: values[ORDER_COL.CUSTOMER_NO - 1],
    customerName: values[ORDER_COL.CUSTOMER_NAME - 1],
    phone: values[ORDER_COL.PHONE - 1],
    neighborhood: values[ORDER_COL.NEIGHBORHOOD - 1],
    address: values[ORDER_COL.ADDRESS - 1],
    location: values[ORDER_COL.LOCATION - 1],
    product: values[ORDER_COL.PRODUCT - 1],
    quantity: values[ORDER_COL.QUANTITY - 1],
    unitPrice: values[ORDER_COL.UNIT_PRICE - 1],
    newDepositQty: values[ORDER_COL.NEW_DEPOSIT_QTY - 1],
    depositAmount: values[ORDER_COL.DEPOSIT_AMOUNT - 1],
    pumpAmount: values[ORDER_COL.PUMP_AMOUNT - 1],
    total: values[ORDER_COL.TOTAL - 1],
    payment: values[ORDER_COL.PAYMENT - 1],
    motor: values[ORDER_COL.MOTOR - 1],
    sequence: values[ORDER_COL.SEQUENCE - 1],
    status: values[ORDER_COL.STATUS - 1],
    deliveredAt: values[ORDER_COL.DELIVERED_AT - 1],
    note: values[ORDER_COL.NOTE - 1],
  };
}

function getOrderRows_(sheet) {
  if (!sheet || sheet.getLastRow() < 2) return [];

  const values = sheet.getRange(2, 1, sheet.getLastRow() - 1, ORDER_COL.NOTE).getValues();
  return values
    .map((row) => ({
      date: row[ORDER_COL.DATE - 1],
      time: row[ORDER_COL.TIME - 1],
      orderNo: row[ORDER_COL.ORDER_NO - 1],
      customerNo: row[ORDER_COL.CUSTOMER_NO - 1],
      customerName: row[ORDER_COL.CUSTOMER_NAME - 1],
      phone: row[ORDER_COL.PHONE - 1],
      neighborhood: row[ORDER_COL.NEIGHBORHOOD - 1],
      address: row[ORDER_COL.ADDRESS - 1],
      location: row[ORDER_COL.LOCATION - 1],
      product: row[ORDER_COL.PRODUCT - 1],
      quantity: parseNumber_(row[ORDER_COL.QUANTITY - 1]),
      unitPrice: parseNumber_(row[ORDER_COL.UNIT_PRICE - 1]),
      newDepositQty: parseNumber_(row[ORDER_COL.NEW_DEPOSIT_QTY - 1]),
      depositAmount: parseNumber_(row[ORDER_COL.DEPOSIT_AMOUNT - 1]),
      pumpAmount: parseNumber_(row[ORDER_COL.PUMP_AMOUNT - 1]),
      total: parseNumber_(row[ORDER_COL.TOTAL - 1]),
      payment: row[ORDER_COL.PAYMENT - 1],
      motor: row[ORDER_COL.MOTOR - 1],
      sequence: row[ORDER_COL.SEQUENCE - 1],
      status: row[ORDER_COL.STATUS - 1],
      deliveredAt: row[ORDER_COL.DELIVERED_AT - 1],
      note: row[ORDER_COL.NOTE - 1],
    }))
    .filter(orderRowHasInput_);
}

function orderRowHasInput_(values) {
  return Boolean(
    values.customerNo ||
      values.customerName ||
      values.phone ||
      values.neighborhood ||
      values.address ||
      values.location ||
      values.product ||
      values.quantity ||
      values.newDepositQty ||
      values.payment ||
      values.motor ||
      values.note
  );
}

function findCustomerByNo_(spreadsheet, customerNo) {
  const customers = spreadsheet.getSheetByName(SHEETS.customers);
  if (!customers || !customerNo || customers.getLastRow() < 2) return null;

  const values = customers.getRange(2, 1, customers.getLastRow() - 1, 14).getValues();
  for (let index = 0; index < values.length; index += 1) {
    const row = values[index];
    if (normalizeText_(row[CUSTOMER_COL.NO - 1]) === normalizeText_(customerNo)) {
      return mapCustomerRow_(row);
    }
  }

  return null;
}

function findCustomerByPhone_(spreadsheet, phone) {
  const customers = spreadsheet.getSheetByName(SHEETS.customers);
  if (!customers || !phone || customers.getLastRow() < 2) return null;

  const values = customers.getRange(2, 1, customers.getLastRow() - 1, 14).getValues();
  const phoneKey = normalizePhone_(phone);
  for (let index = 0; index < values.length; index += 1) {
    const row = values[index];
    if (normalizePhone_(row[CUSTOMER_COL.PHONE - 1]) === phoneKey) {
      return mapCustomerRow_(row);
    }
  }

  return null;
}

function mapCustomerRow_(row) {
  return {
    customerNo: row[CUSTOMER_COL.NO - 1],
    name: row[CUSTOMER_COL.NAME - 1],
    phone: row[CUSTOMER_COL.PHONE - 1],
    neighborhood: row[CUSTOMER_COL.NEIGHBORHOOD - 1],
    address: row[CUSTOMER_COL.ADDRESS - 1],
    location: row[CUSTOMER_COL.LOCATION - 1],
    defaultProduct: row[CUSTOMER_COL.DEFAULT_PRODUCT - 1],
    payment: row[CUSTOMER_COL.PAYMENT - 1],
  };
}

function getProductPrice_(settings, product) {
  if (!settings || settings.getLastRow() < 2) return getFallbackPrice_(product);

  const values = settings.getRange(2, 1, settings.getLastRow() - 1, 3).getValues();
  for (let index = 0; index < values.length; index += 1) {
    const row = values[index];
    if (normalizeText_(row[0]) === normalizeText_(product)) {
      const price = parseNumber_(row[1]);
      return price || getFallbackPrice_(product);
    }
  }

  return getFallbackPrice_(product);
}

function getFallbackPrice_(product) {
  const key = normalizeText_(product);
  if (key === normalizeText_('Damacana Su')) return DEFAULT_WATER_PRICE;
  if (key === normalizeText_('Depozito')) return DEFAULT_DEPOSIT_PRICE;
  if (key === normalizeText_('Pompa')) return DEFAULT_PUMP_PRICE;
  return 0;
}

function generateOrderNo_(orders, date) {
  const key = formatDateKey_(date);
  const prefix = `S-${key}-`;
  let maxNumber = 0;

  if (orders.getLastRow() >= 2) {
    const orderNumbers = orders.getRange(2, ORDER_COL.ORDER_NO, orders.getLastRow() - 1, 1).getValues();
    orderNumbers.forEach((row) => {
      const value = String(row[0] || '');
      if (value.indexOf(prefix) === 0) {
        const number = parseInt(value.replace(prefix, ''), 10);
        if (!Number.isNaN(number)) {
          maxNumber = Math.max(maxNumber, number);
        }
      }
    });
  }

  return `${prefix}${String(maxNumber + 1).padStart(3, '0')}`;
}

function generateCustomerNo_(customers) {
  let maxNumber = 0;

  if (customers.getLastRow() >= 2) {
    const customerNumbers = customers.getRange(2, CUSTOMER_COL.NO, customers.getLastRow() - 1, 1).getValues();
    customerNumbers.forEach((row) => {
      const match = String(row[0] || '').match(/M-(\d+)/);
      if (match) {
        maxNumber = Math.max(maxNumber, parseInt(match[1], 10));
      }
    });
  }

  return `M-${String(maxNumber + 1).padStart(4, '0')}`;
}

function findReportRowForDate_(report, date) {
  if (report.getLastRow() < 2) return null;

  const dates = report.getRange(2, 1, report.getLastRow() - 1, 1).getValues();
  for (let index = 0; index < dates.length; index += 1) {
    if (isSameDay_(dates[index][0], date)) {
      return index + 2;
    }
  }

  return null;
}

function setCellIfBlank_(sheet, row, column, value) {
  if (isBlank_(value)) return;

  const range = sheet.getRange(row, column);
  if (isBlank_(range.getValue())) {
    range.setValue(value);
  }
}

function isBlank_(value) {
  return value === '' || value === null || typeof value === 'undefined';
}

function parseNumber_(value) {
  if (typeof value === 'number') return value;
  if (isBlank_(value)) return 0;

  const normalized = String(value)
    .replace(/\s/g, '')
    .replace(/\./g, '')
    .replace(',', '.');
  const parsed = Number(normalized);
  return Number.isNaN(parsed) ? 0 : parsed;
}

function normalizeText_(value) {
  return String(value || '')
    .trim()
    .toLocaleLowerCase('tr-TR');
}

function normalizePhone_(value) {
  return String(value || '').replace(/\D/g, '');
}

function formatDateKey_(date) {
  const actualDate = toDate_(date) || new Date();
  return Utilities.formatDate(actualDate, SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone(), 'yyyyMMdd');
}

function isSameDay_(value, date) {
  const firstDate = toDate_(value);
  const secondDate = toDate_(date);
  if (!firstDate || !secondDate) return false;

  const timezone = SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone();
  return Utilities.formatDate(firstDate, timezone, 'yyyy-MM-dd') === Utilities.formatDate(secondDate, timezone, 'yyyy-MM-dd');
}

function toDate_(value) {
  if (value instanceof Date && !Number.isNaN(value.getTime())) return value;
  if (!value) return null;

  const parsed = new Date(value);
  return Number.isNaN(parsed.getTime()) ? null : parsed;
}

function sum_(values) {
  return values.reduce((total, value) => total + parseNumber_(value), 0);
}

function mostCommon_(values) {
  const counts = {};
  let winner = '';
  let winnerCount = 0;

  values.forEach((value) => {
    const key = String(value || '').trim();
    if (!key) return;
    counts[key] = (counts[key] || 0) + 1;
    if (counts[key] > winnerCount) {
      winner = key;
      winnerCount = counts[key];
    }
  });

  return winner;
}

function safeAlert_(message) {
  try {
    SpreadsheetApp.getUi().alert(message);
  } catch (error) {
    SpreadsheetApp.getActiveSpreadsheet().toast(message, 'Madran Su', 5);
  }
}