var ss = SpreadsheetApp.getActiveSpreadsheet();

var parseNumberFlexible = function(val) {
  if (val === null || val === undefined || val === '') return 0;
  if (typeof val === 'number') return val;
  var s = String(val).trim();
  s = s.replace(/[^\d.,-]/g, '');
  if (!s) return 0;
  var n = parseFloat(s.replace(/,/g, ''));
  return isNaN(n) ? 0 : n;
};

var normalizeImageUrl = function(url) {
  var raw = String(url || '').trim();
  if (!raw) return '';
  var m = raw.match(/\/d\/([^\/\?\s]+)/);
  if (m && m[1]) return 'https://drive.google.com/thumbnail?id=' + m[1] + '&sz=w400';
  return raw;
};

var parseDate = function(val) {
  if (!val) return '';
  if (val instanceof Date) return Utilities.formatDate(val, "GMT+7", "yyyy-MM-dd HH:mm:ss");
  return String(val);
};

var getSettingsSheet_ = function() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  return ss.getSheetByName('Setup') || ss.getSheetByName('Settings') || null;
};

var getSettingsMap_ = function() {
  var sheet = getSettingsSheet_();
  if (!sheet) return {};
  var data = sheet.getDataRange().getValues();
  var settings = {};
  for (var i = 1; i < data.length; i++) {
    if (data[i][0]) settings[String(data[i][0])] = String(data[i][1]);
  }
  return settings;
};

var normalizeHeaderKey_ = function(s) {
  return String(s || '').trim().toLowerCase();
};

var getHeaderIndexMap_ = function(sheet) {
  if (!sheet) return {};
  var lastCol = sheet.getLastColumn();
  if (lastCol < 1) return {};
  var headers = sheet.getRange(1, 1, 1, lastCol).getValues()[0];
  var map = {};
  for (var i = 0; i < headers.length; i++) {
    var k = normalizeHeaderKey_(headers[i]);
    if (!k) continue;
    if (map[k] == null) map[k] = i;
  }
  return map;
};

var ensureColumnExists_ = function(sheet, headerName) {
  if (!sheet) return -1;
  var key = normalizeHeaderKey_(headerName);
  var map = getHeaderIndexMap_(sheet);
  if (map[key] != null) return map[key] + 1;
  var lastCol = sheet.getLastColumn();
  sheet.getRange(1, lastCol + 1).setValue(headerName);
  sheet.getRange(1, lastCol + 1).setFontWeight('bold').setBackground('#f3f3f3');
  return lastCol + 1;
};

var sendTelegram_ = function(text) {
  try {
    var settings = getSettingsMap_();
    if (String(settings.telegram_enabled || '').toLowerCase() !== 'true') return { skipped: true };
    var token = String(settings.telegram_bot_token || '').trim();
    var chatId = String(settings.telegram_chat_id || '').trim();
    if (!token || !chatId) return { skipped: true };

    var url = 'https://api.telegram.org/bot' + token + '/sendMessage';
    var payload = { chat_id: chatId, text: String(text || ''), disable_web_page_preview: true };
    var resp = UrlFetchApp.fetch(url, {
      method: 'post',
      contentType: 'application/json',
      payload: JSON.stringify(payload),
      muteHttpExceptions: true
    });
    return { success: true, status: resp.getResponseCode() };
  } catch (e) {
    return { error: String(e && e.message ? e.message : e) };
  }
};

function doGet(e) {
  if (!e || !e.parameter) {
    return HtmlService.createHtmlOutput('<h1>Error: Parameter e tidak ditemukan.</h1><p>Gunakan URL Web App untuk mengakses aplikasi ini.</p>');
  }
  var page = e.parameter.p || 'index'; // Default ke index (Kasir)
  var meja = e.parameter.meja || '';
  
  // Fitur PWA: Manifest & Service Worker
  if (page === 'manifest') {
    var settings = getSettingsMap_();
    var storeName = String(settings.store_name || 'POS');
    var shortName = storeName.split(' ')[0] || storeName;
    if (shortName.length > 12) shortName = shortName.slice(0, 12);
    var iconUrl = String(settings.qris_image_url || 'https://lh3.googleusercontent.com/d/1hGPL3AlVGRMeZTzpwOHp-l-JzOG5tS09');
    var manifest = {
      "name": storeName + " POS",
      "short_name": shortName,
      "description": "Point of Sale",
      "start_url": "./?p=index",
      "display": "standalone",
      "background_color": "#111111",
      "theme_color": "#e11d48",
      "icons": [
        {
          "src": iconUrl,
          "sizes": "192x192",
          "type": "image/png",
          "purpose": "any maskable"
        },
        {
          "src": iconUrl,
          "sizes": "512x512",
          "type": "image/png",
          "purpose": "any maskable"
        }
      ]
    };
    return ContentService.createTextOutput(JSON.stringify(manifest)).setMimeType(ContentService.MimeType.JSON);
  }
  
  if (page === 'sw') {
    var sw = "const CACHE_NAME = 'pos-v2'; self.addEventListener('install', (e) => { self.skipWaiting(); }); self.addEventListener('activate', (e) => { e.waitUntil(clients.claim()); }); self.addEventListener('fetch', (e) => { e.respondWith(fetch(e.request).catch(() => caches.match(e.request))); });";
    return ContentService.createTextOutput(sw).setMimeType(ContentService.MimeType.JAVASCRIPT);
  }

  var fileName = 'Index';
  if (page === 'dapur') fileName = 'Dapur';
  if (page === 'order' || meja) fileName = 'Pelanggan';
  if (page === 'poin') fileName = 'Poin';
  if (page === 'belanja') fileName = 'Belanja';
  
  try {
    var settings = getSettingsMap_();
    var storeName = String(settings.store_name || 'POS');
    var template = HtmlService.createTemplateFromFile(fileName);
    template.meja = meja;
    
    return template.evaluate()
      .setTitle(storeName + (page === 'dapur' ? ' - DAPUR' : (page === 'poin' ? ' - POIN' : (page === 'belanja' ? ' - BELANJA' : (meja ? ' - MEJA ' + meja : '')))))
      .addMetaTag('viewport', 'width=device-width, initial-scale=1')
      .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
  } catch (err) {
    return HtmlService.createHtmlOutput(
      '<h1>Halaman tidak bisa dimuat</h1>' +
      '<p>Halaman: <b>' + fileName + '</b></p>' +
      '<p>Silakan deploy ulang dan pastikan file HTML sudah ada di project Apps Script yang dideploy.</p>' +
      '<pre style="white-space:pre-wrap; background:#f8fafc; padding:12px; border:1px solid #e5e7eb; border-radius:8px; font-size:12px;">' +
      String(err && err.stack ? err.stack : err) +
      '</pre>'
    );
  }
}

function getMoreHistory(offset) {
  try {
    var sheetTrans = ss.getSheetByName('Transaksi');
    var lastRow = sheetTrans.getLastRow();
    var startRow = Math.max(2, lastRow - offset - 49); // Ambil 50 baris berikutnya
    var numRows = Math.min(50, lastRow - offset - 1);
    
    if (numRows <= 0) return [];
    
    var values = sheetTrans.getRange(startRow, 1, numRows, sheetTrans.getLastColumn()).getValues();
    var result = values.map(function(row) {
      return {
        id: row[0],
        meja: row[1],
        status: row[2],
        nama: row[3],
        wa: row[4],
        tgl: parseDate(row[5]),
        jam: parseDate(row[6]),
        items: JSON.parse(row[7] || '[]'),
        subtotal: Number(row[8]),
        diskon: Number(row[9]),
        poinDipakai: Number(row[10]),
        pajakPersen: Number(row[11]),
        servicePersen: Number(row[12]),
        adminPersen: Number(row[13]),
        pajak: Number(row[14]),
        service: Number(row[15]),
        adminFee: Number(row[16]),
        total: Number(row[17]),
        dp: Number(row[18]),
        metodeDp: String(row[19]),
        tglDp: parseDate(row[20]),
        metodeBayar: String(row[21]),
        bayar: Number(row[22]),
        kembali: Number(row[23]),
        poinDapat: Number(row[24]),
        timestamp: parseDate(row[25]),
        poinAwal: Number(row[26] || 0),
        catatan: String(row[27] || ''),
        buktiBayar: String(row[28] || ''),
        buktiReview: String(row[29] || '')
      };
    });
    return result;
  } catch (e) {
    return [];
  }
}

function uploadPaymentProof(payload) {
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var folder;
    var settingsSheet = getSettingsSheet_();
    var folderId = '';
    if (settingsSheet) {
      var sd = settingsSheet.getDataRange().getValues();
      for (var i = 1; i < sd.length; i++) {
        if (String(sd[i][0]) === 'drive_bukti_folder_id') { folderId = String(sd[i][1] || ''); break; }
      }
    }
    if (folderId) {
      folder = DriveApp.getFolderById(folderId);
    } else {
      var folders = DriveApp.getFoldersByName('POSFuku_BuktiBayar');
      if (folders.hasNext()) folder = folders.next();
      else return { error: "Folder Drive untuk bukti bayar belum ada. Jalankan setupDriveFolders() oleh admin." };
    }
    
    var contentType = payload.mimeType || 'image/jpeg';
    var bytes = Utilities.base64Decode(payload.base64);
    var blob = Utilities.newBlob(bytes, contentType, 'Bukti_' + payload.id + '.jpg');
    var file = folder.createFile(blob);
    file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
    
    return { success: true, url: file.getUrl(), fileId: file.getId() };
  } catch (e) {
    return { error: e.message };
  }
}

function uploadReviewProof(payload) {
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var folder;
    var settingsSheet = getSettingsSheet_();
    var folderId = '';
    if (settingsSheet) {
      var sd = settingsSheet.getDataRange().getValues();
      for (var i = 1; i < sd.length; i++) {
        if (String(sd[i][0]) === 'drive_review_folder_id') { folderId = String(sd[i][1] || ''); break; }
      }
    }
    if (folderId) {
      folder = DriveApp.getFolderById(folderId);
    } else {
      var folders = DriveApp.getFoldersByName('POSFuku_ReviewScreenshots');
      if (folders.hasNext()) folder = folders.next();
      else return { error: "Folder Drive untuk screenshot review belum ada. Jalankan setupDriveFolders() oleh admin." };
    }
    
    var contentType = payload.mimeType || 'image/jpeg';
    var data = String(payload.base64 || '');
    var commaIndex = data.indexOf(',');
    if (commaIndex > -1) {
      data = data.substr(commaIndex + 1);
    }
    var bytes = Utilities.base64Decode(data);
    var blob = Utilities.newBlob(bytes, contentType, 'Review_' + payload.id + '.jpg');
    var file = folder.createFile(blob);
    file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
    
    return { success: true, url: file.getUrl(), fileId: file.getId() };
  } catch (e) {
    return { error: e.message };
  }
}

function setupDriveFolders() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var settings = getSettingsSheet_();
  if (!settings) { setupSheets(); settings = getSettingsSheet_(); }
  if (!settings) throw new Error('Sheet Setup/Settings belum siap.');

  var setRow = function(key, value, desc) {
    var data = settings.getDataRange().getValues();
    for (var i = 1; i < data.length; i++) {
      if (String(data[i][0]) === key) {
        settings.getRange(i + 1, 2).setValue(String(value));
        if (desc !== undefined) settings.getRange(i + 1, 3).setValue(String(desc));
        return;
      }
    }
    settings.appendRow([key, String(value), String(desc || '')]);
  };

  var findOrCreate = function(name) {
    var it = DriveApp.getFoldersByName(name);
    if (it.hasNext()) return it.next();
    return DriveApp.createFolder(name);
  };

  var bukti = findOrCreate('POSFuku_BuktiBayar');
  var review = findOrCreate('POSFuku_ReviewScreenshots');

  setRow('drive_bukti_folder_id', bukti.getId(), 'Folder ID Drive untuk upload bukti bayar');
  setRow('drive_review_folder_id', review.getId(), 'Folder ID Drive untuk upload screenshot review');

  return { success: true, buktiFolderId: bukti.getId(), reviewFolderId: review.getId() };
}

function updateTransactionReviewPhoto(id, photoUrl) {
  var lock = LockService.getScriptLock();
  try { lock.waitLock(30000); } catch (e) { throw new Error('Server sibuk.'); }
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName('Transaksi');
    var lastRow = sheet.getLastRow();
    if (lastRow < 2) throw new Error('Tidak ada data.');
    
    var ids = sheet.getRange(1, 1, lastRow, 1).getValues();
    var rowIdx = -1;
    for (var i = 1; i < ids.length; i++) {
      if (String(ids[i][0]) === String(id)) { rowIdx = i + 1; break; }
    }
    
    if (rowIdx > -1) {
      sheet.getRange(rowIdx, 30).setValue(String(photoUrl)); // Kolom AD adalah 'Bukti Review'
      return getInitialData();
    } else {
      throw new Error('Pesanan tidak ditemukan.');
    }
  } finally { lock.releaseLock(); }
}

function setupSheets() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  if (!ss) throw new Error('Spreadsheet tidak aktif');

  var ensureHeaderRow = function(sheet, headers) {
    if (sheet.getLastRow() === 0) {
      sheet.getRange(1, 1, 1, headers.length).setValues([headers]).setFontWeight('bold').setBackground('#f3f3f3');
      return;
    }
    // Jika baris 1 kosong, isi. Jika tidak, jangan ganggu data.
    var row1 = sheet.getRange(1, 1, 1, headers.length).getValues()[0];
    var empty = row1.every(function(v) { return !String(v).trim(); });
    if (empty) {
      sheet.getRange(1, 1, 1, headers.length).setValues([headers]).setFontWeight('bold').setBackground('#f3f3f3');
    }
  };

  var menu = ss.getSheetByName('Menu') || ss.insertSheet('Menu');
  ensureHeaderRow(menu, ['Nama', 'Harga', 'Kategori', 'Status', 'Gambar URL', 'Stok']);
  menu.getRange('B:B').setNumberFormat('#,##0');
  menu.getRange('F:F').setNumberFormat('0');

  var trans = ss.getSheetByName('Transaksi') || ss.insertSheet('Transaksi');
  ensureHeaderRow(trans, [
    'ID', 'Meja', 'Status', 'Nama', 'WA', 'Tanggal', 'Jam Booking', 'Items (JSON)',
    'Subtotal', 'Diskon', 'Poin Dipakai (Rp)', 'Pajak %', 'Service %', 'Admin %',
    'Pajak (Rp)', 'Service (Rp)', 'Admin (Rp)', 'Total Akhir',
    'DP', 'Metode DP', 'Tgl DP', 'Metode Bayar', 'Jumlah Bayar', 'Kembali',
    'Poin Didapat', 'Timestamp', 'Poin Awal', 'Catatan', 'Bukti Bayar', 'Bukti Review'
  ]);

  var numCols = ['I', 'J', 'K', 'O', 'P', 'Q', 'R', 'S', 'T', 'W', 'X', 'Y', 'AA'];
  numCols.forEach(function(c) { trans.getRange(c + ':' + c).setNumberFormat('#,##0'); });

  var pctCols = ['L', 'M', 'N'];
  pctCols.forEach(function(c) { trans.getRange(c + ':' + c).setNumberFormat('0'); });

  trans.getRange('F:F').setNumberFormat('yyyy-mm-dd');
  trans.getRange('G:G').setNumberFormat('hh:mm');
  trans.getRange('U:U').setNumberFormat('yyyy-mm-dd');
  trans.getRange('Z:Z').setNumberFormat('yyyy-mm-dd hh:mm:ss');
  trans.getRange('E:E').setNumberFormat('@');

  var cust = ss.getSheetByName('Pelanggan') || ss.insertSheet('Pelanggan');
  ensureHeaderRow(cust, ['Nama', 'WhatsApp', 'Poin', 'Updated At']);
  cust.getRange('B:B').setNumberFormat('@');
  cust.getRange('C:C').setNumberFormat('#,##0');
  cust.getRange('D:D').setNumberFormat('yyyy-mm-dd hh:mm:ss');

  var belanja = ss.getSheetByName('Belanja') || ss.insertSheet('Belanja');
  ensureHeaderRow(belanja, ['ID', 'Nama', 'Harga', 'Qty', 'Total', 'Tanggal', 'Kategori', 'Catatan', 'Timestamp']);
  belanja.getRange('C:C').setNumberFormat('#,##0');
  belanja.getRange('E:E').setNumberFormat('#,##0');
  belanja.getRange('F:F').setNumberFormat('yyyy-mm-dd');
  belanja.getRange('I:I').setNumberFormat('yyyy-mm-dd hh:mm:ss');

  var rekap = ss.getSheetByName('Rekap') || ss.insertSheet('Rekap');
  ensureHeaderRow(rekap, ['ID', 'Tanggal', 'Saldo Awal', 'Tunai', 'QRIS', 'Debit', 'Credit', 'Transfer', 'Catatan', 'Timestamp']);
  rekap.getRange('C:H').setNumberFormat('#,##0');
  rekap.getRange('B:B').setNumberFormat('yyyy-mm-dd');
  rekap.getRange('J:J').setNumberFormat('yyyy-mm-dd hh:mm:ss');

  var stokHarian = ss.getSheetByName('StokHarian') || ss.insertSheet('StokHarian');
  ensureHeaderRow(stokHarian, ['Tanggal', 'Menu', 'Stok Awal', 'Terpakai', 'Sisa', 'Timestamp']);
  stokHarian.getRange('A:A').setNumberFormat('yyyy-mm-dd');
  stokHarian.getRange('C:E').setNumberFormat('#,##0');
  stokHarian.getRange('F:F').setNumberFormat('yyyy-mm-dd hh:mm:ss');

  var supHistory = ss.getSheetByName('SupplierHistory') || ss.insertSheet('SupplierHistory');
  ensureHeaderRow(supHistory, ['ID', 'Nama Supplier', 'Kategori', 'WhatsApp', 'Pesan', 'Timestamp']);
  supHistory.getRange('D:D').setNumberFormat('@');
  supHistory.getRange('F:F').setNumberFormat('yyyy-mm-dd hh:mm:ss');

  var suppliers = ss.getSheetByName('Suppliers') || ss.insertSheet('Suppliers');
  ensureHeaderRow(suppliers, ['ID', 'Nama Supplier', 'WhatsApp', 'Rekening', 'Timestamp']);
  suppliers.getRange('C:C').setNumberFormat('@');
  suppliers.getRange('D:D').setNumberFormat('@');
  suppliers.getRange('E:E').setNumberFormat('yyyy-mm-dd hh:mm:ss');

  var feedback = ss.getSheetByName('Feedback') || ss.insertSheet('Feedback');
  ensureHeaderRow(feedback, ['Timestamp', 'Nama Pelanggan', 'WhatsApp', 'Rating', 'Komentar']);
  feedback.getRange('C:C').setNumberFormat('@');
  feedback.getRange('A:A').setNumberFormat('yyyy-mm-dd hh:mm:ss');

  var waiters = ss.getSheetByName('Waiters') || ss.insertSheet('Waiters');
  ensureHeaderRow(waiters, ['Nama', 'WhatsApp', 'Status', 'Timestamp']);
  waiters.getRange('B:B').setNumberFormat('@');
  waiters.getRange('D:D').setNumberFormat('yyyy-mm-dd hh:mm:ss');

  var paketKustom = ss.getSheetByName('PaketKustom') || ss.insertSheet('PaketKustom');
  ensureHeaderRow(paketKustom, ['ID', 'Nama Paket', 'Items (JSON)', 'Total Harga', 'Timestamp']);
  paketKustom.getRange('D:D').setNumberFormat('#,##0');
  paketKustom.getRange('E:E').setNumberFormat('yyyy-mm-dd hh:mm:ss');

  var setup = ss.getSheetByName('Setup') || ss.insertSheet('Setup');
  ensureHeaderRow(setup, ['Key', 'Value', 'Description']);
  setup.getRange('A:A').setNumberFormat('@');
  setup.getRange('B:B').setNumberFormat('@');

  var legacy = ss.getSheetByName('Settings');
  if (legacy && setup.getLastRow() <= 1) {
    var legacyData = legacy.getDataRange().getValues();
    for (var l = 1; l < legacyData.length; l++) {
      if (legacyData[l][0]) setup.appendRow([legacyData[l][0], legacyData[l][1], legacyData[l][2] || '']);
    }
  }

  var setupData = setup.getDataRange().getValues();
  var keys = setupData.map(function(r) { return r[0]; });
  var ensureKey = function(key, value, desc) {
    if (keys.indexOf(key) === -1) {
      setup.appendRow([key, String(value), String(desc || '')]);
      keys.push(key);
    }
  };

  ensureKey('store_name', 'Fuku Shabu & Grill', 'Nama toko');
  ensureKey('store_address', 'Ruko Puridelta Tigaraksa No. 17, Kab. Tangerang', 'Alamat toko');
  ensureKey('store_whatsapp', '6285770558047', 'WhatsApp toko (format 62...)');
  ensureKey('social_instagram_url', 'https://www.instagram.com/fukushabu.grill', 'URL Instagram');
  ensureKey('social_tiktok_url', 'https://www.tiktok.com/@fukushabu.grill', 'URL TikTok');
  ensureKey('social_gmaps_url', 'https://s.id/GMapsFusagi', 'URL Google Maps');
  ensureKey('social_linktree_url', 'https://linktr.ee/fusagifukushabugrill', 'URL Linktree/Website');
  ensureKey('report_wa_numbers', '6285770558047', 'Daftar WA penerima laporan (pisah koma, format 62...)');

  ensureKey('pajak_persen', '0', 'Nilai pajak (%)');
  ensureKey('service_persen', '0', 'Nilai service (%)');
  ensureKey('admin_persen', '0', 'Nilai admin (%)');

  ensureKey('kasir_table_count', '10', 'Jumlah meja kasir (Meja-1..N)');
  ensureKey('kasir_table_combos', '1&5,2&6,3&7,8&9', 'Meja gabungan (pisahkan dengan koma)');
  ensureKey('kasir_include_dump', 'true', 'Tampilkan Meja-dump (true/false)');
  ensureKey('qr_table_count', '10', 'Jumlah meja QR pelanggan (1..N)');

  ensureKey('poin_earn_rupiah', '10000', 'Perolehan: 1 poin per berapa rupiah');
  ensureKey('poin_redeem_rupiah', '100', 'Redeem: 1 poin = berapa rupiah');

  ensureKey('wifi_lock', 'true', 'Kunci informasi WiFi di Pelanggan.html sebelum isi Nama & WA');
  ensureKey('wifi_ssid', 'FukuShabuGrill', 'Nama WiFi');
  ensureKey('wifi_password', 'Fusagi17', 'Password WiFi');
  ensureKey('qris_image_url', 'https://lh3.googleusercontent.com/d/1hGPL3AlVGRMeZTzpwOHp-l-JzOG5tS09', 'URL gambar QRIS');

  ensureKey('drive_bukti_folder_id', '', 'Folder ID Drive untuk upload bukti bayar');
  ensureKey('drive_review_folder_id', '', 'Folder ID Drive untuk upload screenshot review');
  ensureKey('rekap_archive_spreadsheet_id', '', 'Spreadsheet ID tujuan arsip transaksi (RekapTransaksi). Jika kosong, arsip otomatis nonaktif.');

  ensureKey('telegram_enabled', 'false', 'Aktifkan notifikasi Telegram (true/false)');
  ensureKey('telegram_bot_token', '', 'Telegram Bot Token (jangan dibagikan)');
  ensureKey('telegram_chat_id', '', 'Telegram Chat ID (user/group)');
  ensureKey('telegram_order_enabled', 'false', 'Notifikasi Telegram untuk pesanan masuk');
  ensureKey('telegram_bell_enabled', 'false', 'Notifikasi Telegram untuk bel/panggilan');
  ensureKey('telegram_waiter_enabled', 'false', 'Notifikasi Telegram untuk kasir->pelayan');

  if (legacy && legacy.getLastRow() > 0) {
    try { legacy.hideSheet(); } catch(e) {}
  }

  return 'Berhasil: Sheet Menu, Transaksi, Pelanggan, Belanja, Rekap, StokHarian, SupplierHistory, Suppliers, Waiters, Feedback, PaketKustom, Setup siap.';
}

function setRekapArchiveSpreadsheetId(spreadsheetId) {
  var raw = String(spreadsheetId || '').trim();
  if (!raw) {
    try {
      var settings = getSettingsMap_();
      raw = String(settings.rekap_archive_spreadsheet_id || '').trim();
    } catch (e) {}
  }
  var id = normalizeSpreadsheetId_(raw);
  if (!id) throw new Error('Spreadsheet ID kosong.');
  PropertiesService.getScriptProperties().setProperty('rekap_archive_spreadsheet_id', id);
  return { ok: true, spreadsheetId: id };
}

function getRekapArchiveSpreadsheetId_() {
  var props = PropertiesService.getScriptProperties();
  var id = normalizeSpreadsheetId_(String(props.getProperty('rekap_archive_spreadsheet_id') || '').trim());
  if (id) return id;
  try {
    var settings = getSettingsMap_();
    id = normalizeSpreadsheetId_(String(settings.rekap_archive_spreadsheet_id || '').trim());
  } catch (e) {}
  return id;
}

function normalizeSpreadsheetId_(raw) {
  var s = String(raw || '').trim();
  if (!s) return '';
  var m = s.match(/\/spreadsheets\/d\/([a-zA-Z0-9-_]+)/);
  if (m && m[1]) return m[1];
  m = s.match(/[-\w]{25,}/);
  if (m && m[0]) return m[0];
  return '';
}

function exportTransaksiRowToRekapArchive_(row, settings) {
  try {
    var id = normalizeSpreadsheetId_(String((settings && settings.rekap_archive_spreadsheet_id) || getRekapArchiveSpreadsheetId_() || '').trim());
    if (!id) return;
    var ss = SpreadsheetApp.openById(id);
    var sh = ss.getSheetByName('Transaksi') || ss.insertSheet('Transaksi');
    if (sh.getLastRow() < 1) sh.appendRow([]);
    var headers = ['ID','Meja','Status','Nama','WA','Tgl','Jam','Items (JSON)','Subtotal','Diskon','PoinDipakai','PajakPersen','ServicePersen','AdminPersen','Pajak','Service','AdminFee','Total','DP','MetodeDP','TglDP','MetodeBayar','Bayar','Kembali','PoinDapat','Timestamp','PoinAwal','Catatan','BuktiBayar','BuktiReview'];
    if (sh.getLastRow() < 1 || String(sh.getRange(1, 1).getValue() || '') !== 'ID') {
      sh.clearContents();
      sh.getRange(1, 1, 1, headers.length).setValues([headers]);
    } else {
      var curHeaders = sh.getRange(1, 1, 1, headers.length).getValues()[0];
      var okHeader = true;
      for (var i = 0; i < headers.length; i++) {
        if (String(curHeaders[i] || '') !== headers[i]) { okHeader = false; break; }
      }
      if (!okHeader) sh.getRange(1, 1, 1, headers.length).setValues([headers]);
    }

    var targetId = String(row && row[0] ? row[0] : '');
    if (!targetId) return;
    var lastRow = sh.getLastRow();
    var rowIdx = -1;
    if (lastRow > 1) {
      var finder = sh.getRange(2, 1, lastRow - 1, 1).createTextFinder(targetId).matchEntireCell(true);
      var cell = finder.findNext();
      if (cell) rowIdx = cell.getRow();
    }
    if (rowIdx > -1) sh.getRange(rowIdx, 1, 1, row.length).setValues([row]);
    else sh.appendRow(row);
  } catch (e) {}
}

function testRekapArchiveConnection() {
  try {
    var id = getRekapArchiveSpreadsheetId_();
    if (!id) return { ok: false, error: 'rekap_archive_spreadsheet_id belum diisi.' };
    var ss = SpreadsheetApp.openById(id);
    return { ok: true, spreadsheetId: id, name: ss.getName(), url: ss.getUrl() };
  } catch (e) {
    return { ok: false, error: e.message };
  }
}

function updateSetting(key, value) {
  var lock = LockService.getScriptLock();
  try { lock.waitLock(30000); } catch (e) { throw new Error('Server sibuk.'); }
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName('Setup') || ss.getSheetByName('Settings');
    if (!sheet) { setupSheets(); sheet = ss.getSheetByName('Setup') || ss.getSheetByName('Settings'); }
    var data = sheet.getDataRange().getValues();
    var rowIdx = -1;
    for (var i = 1; i < data.length; i++) {
      if (data[i][0] === key) { rowIdx = i + 1; break; }
    }
    if (rowIdx > -1) {
      sheet.getRange(rowIdx, 2).setValue(String(value));
    } else {
      sheet.appendRow([key, String(value), '']);
    }
    return getInitialData();
  } finally { lock.releaseLock(); }
}

function authDigestBase64_(s) {
  var bytes = Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_256, String(s || ''), Utilities.Charset.UTF_8);
  return Utilities.base64Encode(bytes);
}

function authRandomSalt_() {
  var raw = Utilities.getUuid() + ':' + new Date().getTime() + ':' + Math.random();
  return authDigestBase64_(raw).slice(0, 16);
}

function authEnsureDefault_() {
  var props = PropertiesService.getScriptProperties();
  var dbRaw = props.getProperty('auth_users_json');
  var db = null;
  try { db = dbRaw ? JSON.parse(dbRaw) : null; } catch (e) { db = null; }

  if (!db || !db.users) {
    db = { users: {}, recovery: {} };

    var legacyUser = props.getProperty('auth_admin_user');
    var legacySalt = props.getProperty('auth_admin_salt');
    var legacyHash = props.getProperty('auth_admin_hash');
    if (legacyUser && legacySalt && legacyHash) {
      db.users[String(legacyUser)] = { role: 'admin', salt: String(legacySalt), hashes: [String(legacyHash)] };
    }
  }

  var upsertUser = function(username, passwords, role) {
    var u = String(username || '').trim();
    if (!u) return;
    if (!db.users[u]) {
      db.users[u] = { role: String(role || 'admin'), salt: authRandomSalt_(), hashes: [] };
    }
    if (!db.users[u].salt) db.users[u].salt = authRandomSalt_();
    if (!Array.isArray(db.users[u].hashes)) db.users[u].hashes = [];
    if (role) db.users[u].role = String(role);
    (passwords || []).forEach(function(pw) {
      var h = authDigestBase64_(db.users[u].salt + ':' + String(pw || ''));
      if (db.users[u].hashes.indexOf(h) === -1) db.users[u].hashes.push(h);
    });
  };

  upsertUser('admin', ['11022016', 'admin123'], 'superadmin');
  upsertUser('ridho', ['ridho123'], 'kasir');
  upsertUser('mamah', ['mamah123'], 'kasir');
  Object.keys(db.users || {}).forEach(function(k) {
    var u = db.users[k];
    if (!u) return;
    if (String(k) === 'admin') u.role = 'superadmin';
    else if (!u.role) u.role = 'kasir';
  });

  if (!db.recovery || !db.recovery.salt || !db.recovery.hash) {
    db.recovery = { salt: authRandomSalt_(), hash: authDigestBase64_(authRandomSalt_() + ':' + '11022016') };
    db.recovery.hash = authDigestBase64_(db.recovery.salt + ':' + '11022016');
  }

  props.setProperty('auth_users_json', JSON.stringify(db));
}

function loginUser(username, password) {
  authEnsureDefault_();
  var u = String(username || '').trim();
  var p = String(password || '');
  if (!u || !p) return { ok: false };

  var props = PropertiesService.getScriptProperties();
  var dbRaw = props.getProperty('auth_users_json');
  var db = null;
  try { db = dbRaw ? JSON.parse(dbRaw) : null; } catch (e) { db = null; }
  if (!db || !db.users) return { ok: false };
  var user = db.users[u];
  if (!user || !user.salt || !Array.isArray(user.hashes)) return { ok: false };
  var h = authDigestBase64_(String(user.salt) + ':' + p);
  if (user.hashes.indexOf(h) === -1) return { ok: false };
  return { ok: true, role: String(user.role || ''), username: u };
}

function loginAdmin(username, password) {
  authEnsureDefault_();
  var u = String(username || '').trim();
  var p = String(password || '');
  if (!u || !p) return { ok: false };

  var props = PropertiesService.getScriptProperties();
  var dbRaw = props.getProperty('auth_users_json');
  var db = null;
  try { db = dbRaw ? JSON.parse(dbRaw) : null; } catch (e) { db = null; }
  if (!db || !db.users) return { ok: false };
  var user = db.users[u];
  if (!user || !user.salt || !Array.isArray(user.hashes)) return { ok: false };
  var role = String(user.role || '');
  if (role !== 'admin' && role !== 'superadmin') return { ok: false };
  var h = authDigestBase64_(String(user.salt) + ':' + p);
  if (user.hashes.indexOf(h) === -1) return { ok: false };
  return { ok: true, role: role, username: u };
}

function resetAdminWithRecovery(recoveryCode, newUsername, newPassword) {
  authEnsureDefault_();
  var code = String(recoveryCode || '').trim();
  var u = String(newUsername || '').trim();
  var p = String(newPassword || '');
  if (!code || !u || !p) return { ok: false, error: 'Input tidak lengkap.' };

  var props = PropertiesService.getScriptProperties();
  var dbRaw = props.getProperty('auth_users_json');
  var db = null;
  try { db = dbRaw ? JSON.parse(dbRaw) : null; } catch (e) { db = null; }
  if (!db || !db.users) return { ok: false, error: 'Auth belum siap.' };

  if (!db.recovery || !db.recovery.salt || !db.recovery.hash) return { ok: false, error: 'Recovery belum diset.' };
  var h = authDigestBase64_(String(db.recovery.salt) + ':' + code);
  if (h !== String(db.recovery.hash)) return { ok: false, error: 'Recovery code salah.' };

  db.users[u] = { role: 'admin', salt: authRandomSalt_(), hashes: [authDigestBase64_(authRandomSalt_() + ':' + p)] };
  db.users[u].hashes = [authDigestBase64_(db.users[u].salt + ':' + p)];
  props.setProperty('auth_users_json', JSON.stringify(db));
  return { ok: true, role: 'admin', username: u };
}

function changeAdminPassword(username, oldPassword, newPassword) {
  authEnsureDefault_();
  var u = String(username || '').trim();
  var oldP = String(oldPassword || '');
  var newP = String(newPassword || '');
  if (!u || !oldP || !newP) return { ok: false, error: 'Input tidak lengkap.' };

  var ok = loginAdmin(u, oldP);
  if (!ok || !ok.ok) return { ok: false, error: 'Username/password lama salah.' };

  var props = PropertiesService.getScriptProperties();
  var dbRaw = props.getProperty('auth_users_json');
  var db = null;
  try { db = dbRaw ? JSON.parse(dbRaw) : null; } catch (e) { db = null; }
  if (!db || !db.users || !db.users[u]) return { ok: false, error: 'User tidak ditemukan.' };
  db.users[u].salt = authRandomSalt_();
  db.users[u].hashes = [authDigestBase64_(db.users[u].salt + ':' + newP)];
  if (!db.users[u].role) db.users[u].role = 'admin';
  props.setProperty('auth_users_json', JSON.stringify(db));
  return { ok: true, role: String(db.users[u].role || 'admin'), username: u };
}

function changeUserPassword(username, oldPassword, newPassword) {
  authEnsureDefault_();
  var u = String(username || '').trim();
  var oldP = String(oldPassword || '');
  var newP = String(newPassword || '');
  if (!u || !oldP || !newP) return { ok: false, error: 'Input tidak lengkap.' };

  var ok = loginUser(u, oldP);
  if (!ok || !ok.ok) return { ok: false, error: 'Username/password lama salah.' };

  var props = PropertiesService.getScriptProperties();
  var dbRaw = props.getProperty('auth_users_json');
  var db = null;
  try { db = dbRaw ? JSON.parse(dbRaw) : null; } catch (e) { db = null; }
  if (!db || !db.users || !db.users[u]) return { ok: false, error: 'User tidak ditemukan.' };
  db.users[u].salt = authRandomSalt_();
  db.users[u].hashes = [authDigestBase64_(db.users[u].salt + ':' + newP)];
  if (!db.users[u].role) db.users[u].role = ok.role || '';
  props.setProperty('auth_users_json', JSON.stringify(db));
  return { ok: true, role: String(db.users[u].role || ''), username: u };
}

function resetUserPasswordBySuperAdmin(superUsername, superPassword, targetUsername, newPassword) {
  authEnsureDefault_();
  var su = String(superUsername || '').trim();
  var sp = String(superPassword || '');
  var tu = String(targetUsername || '').trim();
  var np = String(newPassword || '');
  if (!su || !sp || !tu || !np) return { ok: false, error: 'Input tidak lengkap.' };

  var ok = loginAdmin(su, sp);
  if (!ok || !ok.ok) return { ok: false, error: 'Akses ditolak.' };
  var roleOk = String(ok.role || '');
  if (roleOk !== 'superadmin' && roleOk !== 'admin') return { ok: false, error: 'Akses ditolak.' };

  var props = PropertiesService.getScriptProperties();
  var dbRaw = props.getProperty('auth_users_json');
  var db = null;
  try { db = dbRaw ? JSON.parse(dbRaw) : null; } catch (e) { db = null; }
  if (!db || !db.users || !db.users[tu]) return { ok: false, error: 'User target tidak ditemukan.' };
  var role = String(db.users[tu].role || '');
  db.users[tu].salt = authRandomSalt_();
  db.users[tu].hashes = [authDigestBase64_(db.users[tu].salt + ':' + np)];
  db.users[tu].role = role;
  props.setProperty('auth_users_json', JSON.stringify(db));
  return { ok: true, username: tu, role: role };
}

function listAuthUsers(superUsername, superPassword) {
  authEnsureDefault_();
  var su = String(superUsername || '').trim();
  var sp = String(superPassword || '');
  if (!su || !sp) return { ok: false, error: 'Input tidak lengkap.' };
  var ok = loginAdmin(su, sp);
  if (!ok || !ok.ok) return { ok: false, error: 'Akses ditolak.' };
  var roleOk = String(ok.role || '');
  if (roleOk !== 'superadmin' && roleOk !== 'admin') return { ok: false, error: 'Akses ditolak.' };
  var props = PropertiesService.getScriptProperties();
  var dbRaw = props.getProperty('auth_users_json');
  var db = null;
  try { db = dbRaw ? JSON.parse(dbRaw) : null; } catch (e) { db = null; }
  if (!db || !db.users) return { ok: false, error: 'Auth belum siap.' };
  var users = Object.keys(db.users).sort().map(function(u) {
    return { username: u, role: String((db.users[u] && db.users[u].role) || '') };
  });
  return { ok: true, users: users };
}

function addAuthUser(superUsername, superPassword, username, password, role) {
  authEnsureDefault_();
  var su = String(superUsername || '').trim();
  var sp = String(superPassword || '');
  var u = String(username || '').trim();
  var p = String(password || '');
  var r = String(role || 'kasir').trim();
  if (!su || !sp || !u || !p) return { ok: false, error: 'Input tidak lengkap.' };
  var ok = loginAdmin(su, sp);
  if (!ok || !ok.ok) return { ok: false, error: 'Akses ditolak.' };
  var roleOk = String(ok.role || '');
  if (roleOk !== 'superadmin' && roleOk !== 'admin') return { ok: false, error: 'Akses ditolak.' };
  if (!/^[a-zA-Z0-9_.-]{2,20}$/.test(u)) return { ok: false, error: 'Username tidak valid.' };
  if (r !== 'kasir' && r !== 'admin' && r !== 'superadmin') r = 'kasir';
  if (r === 'superadmin') r = 'admin';
  if (u === 'admin') r = 'superadmin';

  var props = PropertiesService.getScriptProperties();
  var dbRaw = props.getProperty('auth_users_json');
  var db = null;
  try { db = dbRaw ? JSON.parse(dbRaw) : null; } catch (e) { db = null; }
  if (!db || !db.users) return { ok: false, error: 'Auth belum siap.' };
  if (!db.users[u]) db.users[u] = { role: r, salt: authRandomSalt_(), hashes: [] };
  db.users[u].role = r;
  db.users[u].salt = authRandomSalt_();
  db.users[u].hashes = [authDigestBase64_(db.users[u].salt + ':' + p)];
  props.setProperty('auth_users_json', JSON.stringify(db));
  return { ok: true, username: u, role: r };
}

function resetAndSetup() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  if (!ss) throw new Error('Spreadsheet tidak aktif');
  ['Menu', 'Transaksi', 'Pelanggan', 'Belanja', 'Rekap', 'StokHarian', 'SupplierHistory', 'Suppliers', 'Waiters', 'Feedback', 'PaketKustom', 'Settings', 'Setup'].forEach(function(n) {
    var sh = ss.getSheetByName(n);
    if (sh) ss.deleteSheet(sh);
  });
  return setupSheets();
}

function setupAman() {
  return setupSheets();
}

function resetTotal() {
  return resetAndSetup();
}

function getInitialData() {
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    if (!ss) throw new Error('Spreadsheet tidak aktif');

    var lastReset = PropertiesService.getScriptProperties().getProperty('last_stock_reset') || '';
    var sheets = ss.getSheets();
    var sheetData = { lastReset: lastReset };
    
    // Ambil semua data sheet sekaligus untuk mengurangi network call
    var targetSheets = ['Menu', 'Transaksi', 'Pelanggan', 'Belanja', 'Rekap', 'SupplierHistory', 'Suppliers', 'Waiters', 'PaketKustom', 'Setup', 'Settings'];
    sheets.forEach(function(s) {
      var name = s.getName();
      if (targetSheets.indexOf(name) > -1) {
        try {
          var lastRow = s.getLastRow();
          var values = [];
          if (lastRow > 1) {
            if (name === 'Transaksi') {
              // Untuk Transaksi, ambil 30 baris terakhir (cukup untuk aktif + history awal)
              var startRow = Math.max(2, lastRow - 29);
              var numRows = lastRow - startRow + 1;
              values = s.getRange(startRow, 1, numRows, s.getLastColumn()).getValues();
            } else if (name === 'Belanja' || name === 'SupplierHistory') {
              // Untuk Belanja dan SupplierHistory, ambil maksimal 100 baris terakhir
              var startRow = Math.max(2, lastRow - 99);
              var numRows = lastRow - startRow + 1;
              values = s.getRange(startRow, 1, numRows, s.getLastColumn()).getValues();
            } else {
              values = s.getRange(2, 1, lastRow - 1, s.getLastColumn()).getValues();
            }
          }
          sheetData[name] = values;
        } catch (e) {
          sheetData[name] = [];
        }
      }
    });

    if (!sheetData['Menu']) {
      return { error: "Sheet Menu tidak ditemukan. Klik SETUP AMAN." };
    }

    var settings = {};
    (sheetData['Settings'] || []).forEach(function(r) {
      if (r[0]) settings[String(r[0])] = String(r[1]);
    });
    (sheetData['Setup'] || []).forEach(function(r) {
      if (r[0]) settings[String(r[0])] = String(r[1]);
    });

    var sheetMenuForHeader = ss.getSheetByName('Menu');
    var menuHeaderMap = getHeaderIndexMap_(sheetMenuForHeader);
    var sheetPaketForHeader = ss.getSheetByName('PaketKustom');
    var paketHeaderMap = getHeaderIndexMap_(sheetPaketForHeader);
    var sheetTransForHeader = ss.getSheetByName('Transaksi');
    var transHeaderMap = getHeaderIndexMap_(sheetTransForHeader);

    var menu = (sheetData['Menu'] || []).map(function(r) {
      var idxNama = (menuHeaderMap['nama'] != null) ? menuHeaderMap['nama'] : 0;
      var idxHarga = (menuHeaderMap['harga'] != null) ? menuHeaderMap['harga'] : 1;
      var idxKategori = (menuHeaderMap['kategori'] != null) ? menuHeaderMap['kategori'] : 2;
      var idxStatus = (menuHeaderMap['status'] != null) ? menuHeaderMap['status'] : 3;
      var idxGambar = (menuHeaderMap['gambar url'] != null) ? menuHeaderMap['gambar url'] : (menuHeaderMap['gambar'] != null ? menuHeaderMap['gambar'] : 4);
      var idxStok = (menuHeaderMap['stok'] != null) ? menuHeaderMap['stok'] : 5;
      var idxTampil = (menuHeaderMap['tampildapur'] != null) ? menuHeaderMap['tampildapur'] : (menuHeaderMap['tampil dapur'] != null ? menuHeaderMap['tampil dapur'] : 6);
      var idxModal = (menuHeaderMap['modal'] != null) ? menuHeaderMap['modal'] : -1;

      var status = String((idxStatus != null ? r[idxStatus] : r[3]) || '').toLowerCase().trim();
      var tampilDapur = String((idxTampil != null ? r[idxTampil] : r[6]) || '').toLowerCase().trim();
      if (status !== 'aktif') return null;
      return { 
        nama: String(r[idxNama] || ''), 
        harga: parseNumberFlexible(r[idxHarga]), 
        kategori: String(r[idxKategori] || ''), 
        gambar: normalizeImageUrl(r[idxGambar]), 
        stok: Number(r[idxStok]) || 0,
        modal: idxModal > -1 ? parseNumberFlexible(r[idxModal]) : 0,
        tampilDapur: tampilDapur // Tambahkan info ini agar frontend bisa filter
      };
    }).filter(function(m) { return m !== null; });

    var transaksi = (sheetData['Transaksi'] || []).map(function(r) {
      var items = []; try { items = JSON.parse(r[7] || '[]'); } catch(e) {}
      var ppIdx = transHeaderMap['potensiprofit'];
      return {
        id: String(r[0]), meja: String(r[1]), status: String(r[2]), nama: String(r[3]), wa: String(r[4]), 
        tgl: parseDate(r[5]), jam: parseDate(r[6]), items: items, subtotal: Number(r[8]), diskon: Number(r[9]),
        poinDipakai: Number(r[10]), pajakPersen: Number(r[11]), servicePersen: Number(r[12]), adminPersen: Number(r[13]),
        pajak: Number(r[14]), service: Number(r[15]), adminFee: Number(r[16]), total: Number(r[17]),
        dp: Number(r[18]), metodeDp: String(r[19]), tglDp: parseDate(r[20]), metodeBayar: String(r[21]),
        bayar: Number(r[22]), kembali: Number(r[23]), poinDapat: Number(r[24]), timestamp: parseDate(r[25]),
        poinAwal: Number(r[26] || 0), catatan: String(r[27] || ''),
        buktiBayar: String(r[28] || ''), buktiReview: String(r[29] || ''),
        potensiProfit: (ppIdx != null ? (Number(r[ppIdx]) || 0) : (Number(r[30]) || 0))
      };
    });

    var pelanggan = (sheetData['Pelanggan'] || []).map(function(r) {
      return { nama: String(r[0]), wa: String(r[1]), poin: Number(r[2]), updatedAt: parseDate(r[3]) };
    });

    var belanja = (sheetData['Belanja'] || []).map(function(r) {
      return { id: String(r[0]), nama: String(r[1]), harga: Number(r[2]), qty: Number(r[3]), total: Number(r[4]), tgl: parseDate(r[5]), kategori: String(r[6]), catatan: String(r[7]), timestamp: parseDate(r[8]) };
    });

    var rekap = (sheetData['Rekap'] || []).map(function(r) {
      return { id: String(r[0]), tgl: parseDate(r[1]), saldoAwal: Number(r[2]), tunai: Number(r[3]), qris: Number(r[4]), debit: Number(r[5]), credit: Number(r[6]), transfer: Number(r[7]), catatan: String(r[8]), timestamp: parseDate(r[9]) };
    });

    var supplierHistory = (sheetData['SupplierHistory'] || []).map(function(r) {
      return { id: String(r[0]), nama: String(r[1]), kategori: String(r[2]), wa: String(r[3]), pesan: String(r[4]), timestamp: parseDate(r[5]) };
    });

    var suppliersMaster = (sheetData['Suppliers'] || []).map(function(r) {
      var rekening = '';
      var ts = null;
      if (r.length >= 5) {
        rekening = String(r[3] || '');
        ts = parseDate(r[4]);
      } else {
        rekening = '';
        ts = parseDate(r[3]);
      }
      return { id: String(r[0]), nama: String(r[1]), wa: String(r[2]), rekening: rekening, timestamp: ts };
    });

    var waiters = (sheetData['Waiters'] || []).map(function(r) {
      return { nama: String(r[0]), wa: String(r[1]), status: String(r[2]), timestamp: parseDate(r[3]) };
    });

    var paketKustom = (sheetData['PaketKustom'] || []).map(function(r) {
      var idxId = (paketHeaderMap['id'] != null) ? paketHeaderMap['id'] : 0;
      var idxNama = (paketHeaderMap['nama paket'] != null) ? paketHeaderMap['nama paket'] : (paketHeaderMap['nama'] != null ? paketHeaderMap['nama'] : 1);
      var idxItems = (paketHeaderMap['items (json)'] != null) ? paketHeaderMap['items (json)'] : 2;
      var idxTotal = (paketHeaderMap['total harga'] != null) ? paketHeaderMap['total harga'] : (paketHeaderMap['total'] != null ? paketHeaderMap['total'] : 3);
      var idxTimestamp = (paketHeaderMap['timestamp'] != null) ? paketHeaderMap['timestamp'] : 4;
      var idxGambar = (paketHeaderMap['gambar url'] != null) ? paketHeaderMap['gambar url'] : (paketHeaderMap['gambar'] != null ? paketHeaderMap['gambar'] : 5);
      var idxAktif = (paketHeaderMap['aktif'] != null) ? paketHeaderMap['aktif'] : 6;
      var idxModal = paketHeaderMap['modal'];

      var items = []; try { items = JSON.parse(r[idxItems] || '[]'); } catch(e) {}
      var isAktif = (r[idxAktif] === true || String(r[idxAktif]).toLowerCase() === 'aktif' || String(r[idxAktif]).toLowerCase() === 'true');
      return { id: String(r[idxId]), nama: String(r[idxNama]), items: items, total: Number(r[idxTotal]), modal: (idxModal != null ? parseNumberFlexible(r[idxModal]) : 0), timestamp: parseDate(r[idxTimestamp]), gambar: normalizeImageUrl(r[idxGambar]), aktif: isAktif };
    });

    var scriptUrl = '';
    try { scriptUrl = ScriptApp.getService().getUrl(); } catch(e) {}

    return { menu: menu, transaksi: transaksi, pelanggan: pelanggan, belanja: belanja, rekap: rekap, supplierHistory: supplierHistory, suppliersMaster: suppliersMaster, waiters: waiters, paketKustom: paketKustom, settings: settings, lastReset: sheetData.lastReset, scriptUrl: scriptUrl };
  } catch (e) {
    return { error: e.message };
  }
}

function getActiveTransaksiLite() {
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheetTrans = ss.getSheetByName('Transaksi');
    if (!sheetTrans) return { transaksi: [] };
    var lastRow = sheetTrans.getLastRow();
    if (lastRow <= 1) return { transaksi: [] };
    var startRow = Math.max(2, lastRow - 199);
    var numRows = lastRow - startRow + 1;
    var values = sheetTrans.getRange(startRow, 1, numRows, sheetTrans.getLastColumn()).getValues();
    var list = values.map(function(r) {
      var items = []; try { items = JSON.parse(r[7] || '[]'); } catch(e) {}
      return {
        id: String(r[0]), meja: String(r[1]), status: String(r[2]), nama: String(r[3]), wa: String(r[4]),
        tgl: parseDate(r[5]), jam: parseDate(r[6]), items: items, subtotal: Number(r[8]), diskon: Number(r[9]),
        poinDipakai: Number(r[10]), pajakPersen: Number(r[11]), servicePersen: Number(r[12]), adminPersen: Number(r[13]),
        pajak: Number(r[14]), service: Number(r[15]), adminFee: Number(r[16]), total: Number(r[17]),
        dp: Number(r[18]), metodeDp: String(r[19]), tglDp: parseDate(r[20]), metodeBayar: String(r[21]),
        bayar: Number(r[22]), kembali: Number(r[23]), poinDapat: Number(r[24]), timestamp: parseDate(r[25]),
        poinAwal: Number(r[26] || 0), catatan: String(r[27] || ''),
        buktiBayar: String(r[28] || ''), buktiReview: String(r[29] || '')
      };
    }).filter(function(t) {
      return t.status === 'Pending' || t.status === 'Ready' || t.status === 'Booking' || t.status === 'Selesai' || t.status === 'Void';
    });
    return { transaksi: list };
  } catch (e) {
    return { error: e.message, transaksi: [] };
  }
}

function saveCustomPaket(payload) {
  var lock = LockService.getScriptLock();
  try { lock.waitLock(30000); } catch (e) { throw new Error('Server sibuk.'); }
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName('PaketKustom');
    if (!sheet) { setupSheets(); sheet = ss.getSheetByName('PaketKustom'); }
    var now = new Date();
    var id = 'PK-' + Date.now();
    
    var row = [
      id, 
      payload.nama || 'Paket Kustom', 
      JSON.stringify(payload.items || []),
      Number(payload.total) || 0,
      now,
      '', // gambar
      false // aktif
    ];
    
    sheet.appendRow(row);
    return getInitialData();
  } finally { lock.releaseLock(); }
}

function deleteCustomPaket(id) {
  var lock = LockService.getScriptLock();
  try { lock.waitLock(30000); } catch (e) { throw new Error('Server sibuk.'); }
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName('PaketKustom');
    if (!sheet) return getInitialData(); // Jika sheet tidak ada, anggap data kosong
    var data = sheet.getDataRange().getValues();
    var rowIdx = -1;
    for (var i = 1; i < data.length; i++) {
      if (String(data[i][0]) === String(id)) { rowIdx = i + 1; break; }
    }
    if (rowIdx > -1) {
      sheet.deleteRow(rowIdx);
    }
    return getInitialData();
  } finally { lock.releaseLock(); }
}

function toggleCustomPaketStatus(id, isActive) {
  var lock = LockService.getScriptLock();
  try { lock.waitLock(30000); } catch (e) { throw new Error('Server sibuk.'); }
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName('PaketKustom');
    if (!sheet) return getInitialData();
    var data = sheet.getDataRange().getValues();
    var rowIdx = -1;
    for (var i = 1; i < data.length; i++) {
      if (String(data[i][0]) === String(id)) { rowIdx = i + 1; break; }
    }
    if (rowIdx > -1) {
      sheet.getRange(rowIdx, 7).setValue(isActive); // Kolom G adalah 'Aktif'
    }
    return getInitialData();
  } finally { lock.releaseLock(); }
}

function updateCustomPaketName(id, newName) {
  var lock = LockService.getScriptLock();
  try { lock.waitLock(30000); } catch (e) { throw new Error('Server sibuk.'); }
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName('PaketKustom');
    if (!sheet) return getInitialData();
    var data = sheet.getDataRange().getValues();
    var rowIdx = -1;
    for (var i = 1; i < data.length; i++) {
      if (String(data[i][0]) === String(id)) { rowIdx = i + 1; break; }
    }
    if (rowIdx > -1) {
      sheet.getRange(rowIdx, 2).setValue(newName); // Kolom B adalah 'Nama Paket'
    }
    return getInitialData();
  } finally { lock.releaseLock(); }
}

function uploadPaketLogo(id, base64Data, mimeType, fileName) {
  var lock = LockService.getScriptLock();
  try { lock.waitLock(30000); } catch (e) { throw new Error('Server sibuk.'); }
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName('PaketKustom');
    if (!sheet) throw new Error('Sheet PaketKustom tidak ditemukan.');
    
    var data = sheet.getDataRange().getValues();
    var rowIdx = -1;
    for (var i = 1; i < data.length; i++) {
      if (String(data[i][0]) === String(id)) { rowIdx = i + 1; break; }
    }
    if (rowIdx === -1) throw new Error('Paket tidak ditemukan.');

    var folderId = PropertiesService.getScriptProperties().getProperty('folder_paket_logo');
    var folder;
    if (folderId) {
      try {
        folder = DriveApp.getFolderById(folderId);
      } catch(e) {}
    }
    if (!folder) {
      var folders = DriveApp.getFoldersByName('POSFuku_PaketLogo');
      if (folders.hasNext()) {
        folder = folders.next();
      } else {
        folder = DriveApp.createFolder('POSFuku_PaketLogo');
        folder.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
      }
      PropertiesService.getScriptProperties().setProperty('folder_paket_logo', folder.getId());
    }

    var blob = Utilities.newBlob(Utilities.base64Decode(base64Data), mimeType, fileName);
    var file = folder.createFile(blob);
    var url = file.getUrl();

    sheet.getRange(rowIdx, 6).setValue(url); // Kolom F adalah 'Gambar'
    
    return getInitialData();
  } catch (e) {
    return { error: e.message };
  } finally {
    lock.releaseLock();
  }
}

function generateReceiptPdfFromHtml(html, fileName) {
  try {
    var safeName = String(fileName || 'Struk.pdf').replace(/[\\\/\:\*\?\"\<\>\|]/g, '_');
    if (!safeName.toLowerCase().endsWith('.pdf')) safeName += '.pdf';
    var blob = HtmlService.createHtmlOutput(String(html || '')).getAs(MimeType.PDF).setName(safeName);
    var b64 = Utilities.base64Encode(blob.getBytes());
    return { fileName: safeName, base64: b64 };
  } catch (e) {
    return { error: e.message };
  }
}

function saveTransaction(payload) {
  var lock = LockService.getScriptLock();
  try {
    lock.waitLock(30000);
  } catch (e) {
    throw new Error('Server sibuk. Coba lagi.');
  }

  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheetTrans = ss.getSheetByName('Transaksi');
    var sheetMenu = ss.getSheetByName('Menu');
    var sheetCust = ss.getSheetByName('Pelanggan');
    if (!sheetTrans || !sheetMenu) throw new Error('Sheet belum siap. Klik SETUP DATABASE.');

    var now = new Date();

    var normalizeWA = function(wa) {
      var clean = String(wa || '').replace(/\D/g, '');
      if (!clean) return '';
      if (clean.indexOf('0') === 0) clean = '62' + clean.slice(1);
      else if (clean.indexOf('8') === 0) clean = '62' + clean;
      return clean;
    };
    payload.wa = normalizeWA(payload.wa);
    var settings = getSettingsMap_();

    var toDateObj = function(val) {
      if (!val) return '';
      if (val instanceof Date) return val;
      var s = String(val);
      if (s.includes('T') || s.includes('-')) {
        var d = new Date(s);
        return isNaN(d) ? '' : d;
      }
      return '';
    };

    // Optimasi: Hanya ambil data yang diperlukan (300 baris terakhir untuk pencarian Pending)
    var totalRows = sheetTrans.getLastRow();
    var rowsToRead = Math.min(totalRows, 500); // Batasi pencarian baris ke 500 terakhir
    var startRow = Math.max(1, totalRows - rowsToRead + 1);
    var rows = sheetTrans.getRange(startRow, 1, rowsToRead, sheetTrans.getLastColumn()).getValues();
    
    var findRowById = function(id) {
      for (var i = 0; i < rows.length; i++) {
        if (String(rows[i][0]) === String(id)) return startRow + i;
      }
      return -1;
    };

    var rowToUpdate = findRowById(payload.id);
    var old = null;
    if (rowToUpdate > -1) {
      // Index di array `rows` adalah rowToUpdate - startRow
      old = rows[rowToUpdate - startRow];
    }

    if (payload.status === 'Pending' && rowToUpdate === -1) {
      for (var ip = 0; ip < rows.length; ip++) {
        if (String(rows[ip][1]) === String(payload.meja) && String(rows[ip][2]) === 'Pending') {
          rowToUpdate = startRow + ip;
          payload.id = String(rows[ip][0]);
          old = rows[ip];
          break;
        }
      }
    }

    // Auto-generate name if empty (preserve old name if updating existing transaksi)
     if (!payload.nama || !String(payload.nama).trim()) {
       if (old && old[3] && String(old[3]).trim()) {
         payload.nama = String(old[3]).trim();
       } else {
         var maxFound = 0;
         var scanNames = function(values) {
           (values || []).forEach(function(r) {
             var n = String((Array.isArray(r) ? r[0] : r) || '');
             if (n.indexOf('an-') === 0) {
               var num = parseInt(n.replace('an-', ''), 10);
               if (!isNaN(num) && num > maxFound) maxFound = num;
             } else if (n.indexOf('Pelanggan-') === 0) {
               var num2 = parseInt(n.replace('Pelanggan-', ''), 10);
               if (!isNaN(num2) && num2 > maxFound) maxFound = num2;
             }
           });
         };

         var transLastRow = sheetTrans.getLastRow();
         var batchSize = 5000;
         if (transLastRow > 1) {
           for (var rStart = 2; rStart <= transLastRow; rStart += batchSize) {
             var count = Math.min(batchSize, transLastRow - rStart + 1);
             var transNames = sheetTrans.getRange(rStart, 4, count, 1).getValues();
             scanNames(transNames);
           }
         }

         var nextNum = maxFound + 1;
         payload.nama = 'an-' + nextNum;
       }
     }

    // Kapitalisasi Nama Pelanggan (Title Case)
    if (payload.nama && String(payload.nama).indexOf('an-') !== 0) {
      payload.nama = String(payload.nama).split(' ').map(function(word) {
        return word.charAt(0).toUpperCase() + word.slice(1).toLowerCase();
      }).join(' ');
    }

    var items = Array.isArray(payload.items) ? payload.items : [];
    if (payload.status === 'Pending' && old) {
      try {
        var oldItems = JSON.parse(old[7] || '[]');
        var newCart = items; // Flat list from UI

        var oldMap = {};
        oldItems.forEach(function(it) {
          var k = (it.nama || '').trim();
          oldMap[k] = (oldMap[k] || 0) + (Number(it.qty) || 0);
        });

        var finalItems = [];
        newCart.forEach(function(it) {
          var k = (it.nama || '').trim();
          var oldQty = oldMap[k] || 0;
          var newQty = Number(it.qty) || 0;

          if (oldQty > 0) {
            var existingQty = Math.min(oldQty, newQty);
            finalItems.push({ nama: it.nama, harga: it.harga, qty: existingQty, type: 'Eks' });
            if (newQty > oldQty) {
              finalItems.push({ nama: it.nama, harga: it.harga, qty: newQty - oldQty, type: 'Tam' });
            }
          } else {
            finalItems.push({ nama: it.nama, harga: it.harga, qty: newQty, type: 'Tam' });
          }
        });
        items = finalItems;
      } catch (e) {
        items = payload.items;
      }
      // Jika payload.dp tidak dikirim (undefined/null), baru ambil dari data lama
      if (payload.dp === undefined || payload.dp === null) {
        payload.dp = Number(old[18]) || 0;
        payload.metodeDp = String(old[19] || '');
        payload.tglDp = old[20] ? old[20] : '';
      }
    }

    var subtotal = items.reduce(function(acc, it) { return acc + (Number(it.qty) || 0) * (Number(it.harga) || 0); }, 0);
    var diskon = Number(payload.diskon) || 0;
    var poinDipakai = Number(payload.poinDipakai) || 0;
    var base = Math.max(0, subtotal - diskon - poinDipakai);
    var pajakPersen = Number(payload.pajakPersen) || 0;
    var servicePersen = Number(payload.servicePersen) || 0;
    var adminPersen = Number(payload.adminPersen) || 0;
    
    // Gunakan nilai dari payload jika tersedia, jika tidak baru kalkulasi ulang
    var service = (payload.service !== undefined) ? Number(payload.service) : Math.floor(base * (servicePersen / 100));
    var pajakBase = base + service;
    var pajak = (payload.pajak !== undefined) ? Number(payload.pajak) : Math.floor(pajakBase * (pajakPersen / 100));
    var totalSebelumAdmin = base + service + pajak;
    var adminFee = (payload.adminFee !== undefined) ? Number(payload.adminFee) : Math.floor(totalSebelumAdmin * (adminPersen / 100));
    var totalAkhir = (payload.total !== undefined) ? Number(payload.total) : (totalSebelumAdmin + adminFee);

    var dp = Number(payload.dp) || 0;
    var bayar = Number(payload.bayar) || 0;
    var kembali = (dp + bayar) - totalAkhir;
    if (kembali < 0) kembali = 0;

    // Hitung Poin (Divisor: 10.000, Redeem: 1.000)
    var poinDapat = 0;
    var curPoin = 0;

    var menuDelta = [];
    if (payload.status === 'Selesai') {
      var oldStatus = old ? String(old[2] || '') : '';
      if (oldStatus !== 'Selesai') {
        var menuRange = sheetMenu.getRange(2, 1, sheetMenu.getLastRow() - 1, 6);
        var menuData = menuRange.getValues();
        var menuChanged = false;

        items.forEach(function(it) {
          for (var m = 0; m < menuData.length; m++) {
            if (String(menuData[m][0]) === String(it.nama)) {
              var currentStok = Number(menuData[m][5]) || 0;
              var buyQty = Number(it.qty) || 0;
              menuData[m][5] = Math.max(0, currentStok - buyQty);
              menuDelta.push({ nama: String(it.nama), stok: Number(menuData[m][5]) || 0 });
              menuChanged = true;
              break;
            }
          }
        });
        
        // Simpan SEMUA stok menu sekaligus (Bukan satu per satu)
        if (menuChanged) {
          menuRange.setValues(menuData);
        }
      }
    }

    var pelangganDelta = null;
    if (sheetCust && payload.wa && payload.status === 'Selesai') {
      var earnDiv = parseNumberFlexible(settings.poin_earn_rupiah);
      if (!earnDiv || earnDiv < 1) earnDiv = 10000;
      var redeemRp = parseNumberFlexible(settings.poin_redeem_rupiah);
      if (!redeemRp || redeemRp < 1) redeemRp = 100;
      poinDapat = Math.floor(Math.max(0, base) / earnDiv);
      var wa = String(payload.wa);
      var custData = sheetCust.getDataRange().getValues();
      var foundIdx = -1;
      for (var c = 1; c < custData.length; c++) {
        if (String(custData[c][1]) === wa) { foundIdx = c + 1; break; }
      }
      
      var poinUsed = Math.floor(poinDipakai / redeemRp);
      if (foundIdx > -1) {
        curPoin = Number(custData[foundIdx - 1][2]) || 0;
        var newP = Math.max(0, curPoin - poinUsed + poinDapat);
        sheetCust.getRange(foundIdx, 1, 1, 4).setValues([[payload.nama || custData[foundIdx - 1][0], wa, newP, now]]);
        pelangganDelta = { nama: String(payload.nama || custData[foundIdx - 1][0] || ''), wa: String(wa), poin: Number(newP) || 0, updatedAt: now };
      } else {
        curPoin = 0;
        sheetCust.appendRow([payload.nama || '', wa, Math.max(0, poinDapat - poinUsed), now]);
        pelangganDelta = { nama: String(payload.nama || ''), wa: String(wa), poin: Number(Math.max(0, poinDapat - poinUsed)) || 0, updatedAt: now };
      }
    }

    var ppCol = ensureColumnExists_(sheetTrans, 'PotensiProfit');
    var potensiProfit = 0;
    try {
      potensiProfit = calcPotensiProfitFromItems_(ss, items);
    } catch (ePp) {
      potensiProfit = 0;
    }

    var row = [
      payload.id,
      payload.meja,
      payload.status,
      payload.nama || '',
      payload.wa || '',
      toDateObj(payload.tgl) || now,
      payload.jam ? payload.jam : '',
      JSON.stringify(items),
      subtotal,
      diskon,
      poinDipakai,
      pajakPersen,
      servicePersen,
      adminPersen,
      pajak,
      service,
      adminFee,
      totalAkhir,
      dp,
      payload.metodeDp || '',
      toDateObj(payload.tglDp) || '',
      payload.metodeBayar || '',
      bayar,
      kembali,
      poinDapat,
      now,
      curPoin,
      payload.catatan || '',
      payload.buktiBayar || (old ? (old[28] || '') : ''),
      payload.buktiReview || (old ? (old[29] || '') : '')
    ];

    if (rowToUpdate > -1) {
      sheetTrans.getRange(rowToUpdate, 1, 1, row.length).setValues([row]);
      if (ppCol > 0) sheetTrans.getRange(rowToUpdate, ppCol).setValue(potensiProfit);
    } else {
      sheetTrans.appendRow(row);
      if (ppCol > 0) sheetTrans.getRange(sheetTrans.getLastRow(), ppCol).setValue(potensiProfit);
    }

    try {
      var st = String(payload.status || '');
      if (st === 'Selesai' || st === 'Void') exportTransaksiRowToRekapArchive_(row, settings);
    } catch (eArch) {}

    try {
      var muted = /x123/i.test(String(payload.nama || ''));
      if (!muted && String(payload.status || '') === 'Pending' && String(settings.telegram_order_enabled || '').toLowerCase() === 'true') {
        var oldStatus = old ? String(old[2] || '') : '';
        var isNewPending = !old || oldStatus !== 'Pending';
        var hasTam = false;
        try {
          hasTam = items.some(function(it) { return String(it.type || '') === 'Tam' && (Number(it.qty) || 0) > 0; });
        } catch (eTam) {}
        if (isNewPending || hasTam) {
          var meja = String(payload.meja || '');
          var nama = String(payload.nama || '');
          var lines = items.map(function(it) { return 'x' + (Number(it.qty) || 0) + ' ' + String(it.nama || ''); }).join('\n');
          var text = 'PESANAN MASUK\nMeja: ' + meja + '\nNama: ' + nama + (lines ? '\n\n' + lines : '');
          sendTelegram_(text);
        }
      }
    } catch (eNotify) {}

    var isLite = !!(payload && (payload.__lite === true || payload.lite === true));
    if (isLite) {
      var trx = {
        id: String(payload.id),
        meja: String(payload.meja),
        status: String(payload.status),
        nama: String(payload.nama || ''),
        wa: String(payload.wa || ''),
        tgl: toDateObj(payload.tgl) || now,
        jam: payload.jam ? payload.jam : '',
        items: items,
        subtotal: Number(subtotal) || 0,
        diskon: Number(diskon) || 0,
        poinDipakai: Number(poinDipakai) || 0,
        pajakPersen: Number(pajakPersen) || 0,
        servicePersen: Number(servicePersen) || 0,
        adminPersen: Number(adminPersen) || 0,
        pajak: Number(pajak) || 0,
        service: Number(service) || 0,
        adminFee: Number(adminFee) || 0,
        total: Number(totalAkhir) || 0,
        dp: Number(dp) || 0,
        metodeDp: String(payload.metodeDp || ''),
        tglDp: toDateObj(payload.tglDp) || '',
        metodeBayar: String(payload.metodeBayar || ''),
        bayar: Number(bayar) || 0,
        kembali: Number(kembali) || 0,
        poinDapat: Number(poinDapat) || 0,
        timestamp: now,
        poinAwal: Number(curPoin) || 0,
        catatan: String(payload.catatan || ''),
        buktiBayar: String(payload.buktiBayar || (old ? (old[28] || '') : '')),
        buktiReview: String(payload.buktiReview || (old ? (old[29] || '') : ''))
      };
      var scriptUrl = '';
      try { scriptUrl = ScriptApp.getService().getUrl(); } catch(e) {}
      var lastResetLite = PropertiesService.getScriptProperties().getProperty('last_stock_reset') || '';
      return { ok: true, lite: true, transaksiDelta: [trx], menuDelta: menuDelta, pelangganDelta: pelangganDelta, lastReset: lastResetLite, scriptUrl: scriptUrl };
    }

    return getInitialData();
  } finally {
    lock.releaseLock();
  }
}

function saveCustomerInfo(payload) {
  var lock = LockService.getScriptLock();
  try { lock.waitLock(30000); } catch (e) { throw new Error('Server sibuk.'); }
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheetCust = ss.getSheetByName('Pelanggan');
    if (!sheetCust) { setupSheets(); sheetCust = ss.getSheetByName('Pelanggan'); }
    if (!sheetCust) throw new Error('Sheet Pelanggan belum siap.');

    var now = new Date();
    var rawName = payload && payload.nama ? String(payload.nama).trim() : '';
    var rawWa = payload && payload.wa ? String(payload.wa).trim() : '';
    if (!rawWa) throw new Error('Nomor WhatsApp kosong.');

    var normalizeWA = function(wa) {
      var clean = String(wa || '').replace(/\D/g, '');
      if (!clean) return '';
      if (clean.indexOf('0') === 0) clean = '62' + clean.slice(1);
      else if (clean.indexOf('8') === 0) clean = '62' + clean;
      return clean;
    };

    var wa = normalizeWA(rawWa);
    if (!wa || wa.length < 10) throw new Error('Nomor WhatsApp tidak valid.');

    var nama = rawName;
    if (nama) {
      nama = nama.split(' ').filter(function(w) { return w; }).map(function(word) {
        return word.charAt(0).toUpperCase() + word.slice(1).toLowerCase();
      }).join(' ');
    }

    var lastRow = sheetCust.getLastRow();
    var data = lastRow > 1 ? sheetCust.getRange(2, 1, lastRow - 1, 4).getValues() : [];
    var foundRow = -1;
    var foundPoin = 0;
    for (var i = 0; i < data.length; i++) {
      var rowWa = normalizeWA(data[i][1]);
      if (rowWa && rowWa === wa) {
        foundRow = i + 2;
        foundPoin = Number(data[i][2]) || 0;
        break;
      }
    }

    if (foundRow > -1) {
      var existingNama = String(data[foundRow - 2][0] || '').trim();
      var finalNama = nama || existingNama;
      sheetCust.getRange(foundRow, 1, 1, 4).setValues([[finalNama, wa, foundPoin, now]]);
    } else {
      sheetCust.appendRow([nama || '', wa, 0, now]);
    }

    return { success: true };
  } finally { lock.releaseLock(); }
}

function updatePaymentMethod(id, newMethod) {
  var lock = LockService.getScriptLock();
  try { lock.waitLock(30000); } catch (e) { throw new Error('Server sibuk.'); }
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName('Transaksi');
    var lastRow = sheet.getLastRow();
    if (lastRow < 2) throw new Error('Tidak ada data.');
    
    var ids = sheet.getRange(1, 1, lastRow, 1).getValues();
    var rowIdx = -1;
    for (var i = 1; i < ids.length; i++) {
      if (String(ids[i][0]) === String(id)) { rowIdx = i + 1; break; }
    }
    
    if (rowIdx > -1) {
      sheet.getRange(rowIdx, 22).setValue(String(newMethod)); // Kolom V adalah 'Metode Bayar'
      return getInitialData();
    } else {
      throw new Error('Pesanan tidak ditemukan.');
    }
  } finally { lock.releaseLock(); }
}

function updateTransactionPhoto(id, photoUrl) {
  var lock = LockService.getScriptLock();
  try { lock.waitLock(30000); } catch (e) { throw new Error('Server sibuk.'); }
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName('Transaksi');
    var lastRow = sheet.getLastRow();
    if (lastRow < 2) throw new Error('Tidak ada data.');
    
    var ids = sheet.getRange(1, 1, lastRow, 1).getValues();
    var rowIdx = -1;
    for (var i = 1; i < ids.length; i++) {
      if (String(ids[i][0]) === String(id)) { rowIdx = i + 1; break; }
    }
    
    if (rowIdx > -1) {
      sheet.getRange(rowIdx, 29).setValue(String(photoUrl)); // Kolom AC adalah 'Bukti Bayar'
      return getInitialData();
    } else {
      throw new Error('Pesanan tidak ditemukan.');
    }
  } finally { lock.releaseLock(); }
}

function updateKitchenStatus(id, newStatus) {
  var lock = LockService.getScriptLock();
  try { lock.waitLock(30000); } catch (e) { throw new Error('Server sibuk.'); }
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName('Transaksi');
    var lastRow = sheet.getLastRow();
    if (lastRow < 2) throw new Error('Tidak ada data.');
    
    var ids = sheet.getRange(1, 1, lastRow, 1).getValues();
    var rowIdx = -1;
    for (var i = 1; i < ids.length; i++) {
      if (String(ids[i][0]) === String(id)) { rowIdx = i + 1; break; }
    }
    
    if (rowIdx > -1) {
      sheet.getRange(rowIdx, 3).setValue(newStatus); // Update status di kolom C
      return getInitialData();
    } else {
      throw new Error('Pesanan tidak ditemukan.');
    }
  } finally { lock.releaseLock(); }
}

function importTransactions(data) {
  var lock = LockService.getScriptLock();
  try { lock.waitLock(30000); } catch (e) { throw new Error('Server sibuk.'); }
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName('Transaksi');
    if (!sheet) throw new Error('Sheet Transaksi tidak ditemukan.');
    
    var existingIds = [];
    if (sheet.getLastRow() > 1) {
      existingIds = sheet.getRange(2, 1, sheet.getLastRow() - 1, 1).getValues().map(function(r) { return String(r[0]); });
    }
    
    var rowsToAdd = [];
    data.forEach(function(t) {
      if (existingIds.indexOf(String(t[0])) > -1) return; // Index 0 adalah ID
      
      // Pastikan format tanggal benar untuk kolom Tanggal (Index 5), Tgl DP (Index 20), dan Timestamp (Index 25)
      var row = t.map(function(val, idx) {
        if ((idx === 5 || idx === 20 || idx === 25) && val) {
          try {
            var d = new Date(val);
            if (!isNaN(d.getTime())) return d;
          } catch(e) {}
        }
        return val;
      });
      
      // Pastikan panjang baris sesuai (28 kolom)
      while (row.length < 28) row.push('');
      rowsToAdd.push(row);
    });
    
    if (rowsToAdd.length > 0) {
      sheet.getRange(sheet.getLastRow() + 1, 1, rowsToAdd.length, 28).setValues(rowsToAdd);
    }
    return getInitialData();
  } finally {
    lock.releaseLock();
  }
}

function importBelanjaBulk(data) {
  var lock = LockService.getScriptLock();
  try { lock.waitLock(30000); } catch (e) { throw new Error('Server sibuk.'); }
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName('Belanja');
    if (!sheet) throw new Error('Sheet Belanja tidak ditemukan.');
    
    data.forEach(function(b) {
      var id = 'B-' + Date.now() + Math.floor(Math.random() * 1000);
      var row = [
        id, 
        b.nama || '', 
        Number(b.harga) || 0, 
        Number(b.qty) || 0, 
        Number(b.total) || 0, 
        b.tgl || Utilities.formatDate(new Date(), "GMT+7", "yyyy-MM-dd"), 
        b.kategori || b.kat || 'Dapur', 
        b.catatan || '', 
        new Date()
      ];
      sheet.appendRow(row);
    });
    return getInitialData();
  } finally {
    lock.releaseLock();
  }
}

function saveBelanjaBulk(payloads) {
  var lock = LockService.getScriptLock();
  try { lock.waitLock(30000); } catch (e) { throw new Error('Server sibuk.'); }
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName('Belanja');
    if (!sheet) throw new Error('Sheet Belanja tidak ditemukan.');
    var now = new Date();
    
    payloads.forEach(function(p) {
      if (p.id) {
        var data = sheet.getDataRange().getValues();
        var rowIdx = -1;
        for (var i = 1; i < data.length; i++) {
          if (String(data[i][0]) === String(p.id)) { rowIdx = i + 1; break; }
        }
        if (rowIdx > -1) {
          sheet.getRange(rowIdx, 2, 1, 7).setValues([[
            p.nama || '', Number(p.harga) || 0, Number(p.qty) || 0,
            Number(p.total) || 0, p.tgl || Utilities.formatDate(now, "GMT+7", "yyyy-MM-dd"),
            p.kategori || '', p.catatan || ''
          ]]);
        }
      } else {
        var id = 'B-' + Date.now() + Math.floor(Math.random() * 1000);
        var row = [
          id, p.nama || '', Number(p.harga) || 0, Number(p.qty) || 0,
          Number(p.total) || 0, p.tgl || Utilities.formatDate(now, "GMT+7", "yyyy-MM-dd"),
          p.kategori || '', p.catatan || '', now
        ];
        sheet.appendRow(row);
      }
    });
    return getInitialData();
  } finally { lock.releaseLock(); }
}

function syncBelanjaOut(dateStr) {
  var lock = LockService.getScriptLock();
  try { lock.waitLock(30000); } catch (e) { throw new Error('Server sibuk.'); }
  try {
    var now = new Date();
    var date = (function() {
      var s = String(dateStr || '').trim();
      if (/^\d{4}-\d{2}-\d{2}$/.test(s)) return s;
      return Utilities.formatDate(now, 'GMT+7', 'yyyy-MM-dd');
    })();

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName('Belanja');
    if (!sheet) throw new Error('Sheet Belanja tidak ditemukan.');
    if (sheet.getLastRow() <= 1) return { date: date, localCount: 0, rekap: { ok: true, inserted: 0, updated: 0, skipped: 0 }, stokBelanja: { ok: true, belanja: 0, operasional: 0, pegawai: 0, skipped: 0 } };

    var data = sheet.getDataRange().getValues();
    var items = [];
    for (var i = 1; i < data.length; i++) {
      var r = data[i];
      var id = String(r[0] || '').trim();
      var tgl = r[5] instanceof Date ? Utilities.formatDate(r[5], 'GMT+7', 'yyyy-MM-dd') : String(r[5] || '').trim();
      if (tgl !== date) continue;
      if (!id) continue;
      items.push({
        id: id,
        nama: String(r[1] || ''),
        harga: Number(r[2]) || 0,
        qty: Number(r[3]) || 0,
        total: Number(r[4]) || 0,
        tgl: tgl,
        kategori: String(r[6] || ''),
        catatan: String(r[7] || '')
      });
    }

    var result = {
      date: date,
      localCount: items.length,
      rekap: { ok: false, inserted: 0, updated: 0, skipped: 0, error: '' },
      stokBelanja: { ok: false, belanja: 0, operasional: 0, pegawai: 0, skipped: 0, error: '' }
    };

    var props = PropertiesService.getScriptProperties();
    var rekapId = String(props.getProperty('rekap_transaksi_spreadsheet_id') || '').trim();
    var sbId = String(props.getProperty('stok_belanja_spreadsheet_id') || '').trim();

    if (rekapId) {
      try {
        result.rekap = syncToRekapTransaksi_(rekapId, items, now);
      } catch (e1) {
        result.rekap = { ok: false, inserted: 0, updated: 0, skipped: 0, error: (e1 && e1.message) ? e1.message : String(e1) };
      }
    } else {
      result.rekap = { ok: false, inserted: 0, updated: 0, skipped: 0, error: 'Script Properties rekap_transaksi_spreadsheet_id belum diisi.' };
    }

    if (sbId) {
      try {
        result.stokBelanja = syncToStokBelanja_(sbId, items, now);
      } catch (e2) {
        result.stokBelanja = { ok: false, belanja: 0, operasional: 0, pegawai: 0, skipped: 0, error: (e2 && e2.message) ? e2.message : String(e2) };
      }
    } else {
      result.stokBelanja = { ok: false, belanja: 0, operasional: 0, pegawai: 0, skipped: 0, error: 'Script Properties stok_belanja_spreadsheet_id belum diisi.' };
    }

    return result;
  } finally { lock.releaseLock(); }
}

function ensureSheetWithHeader_(ss, name, headers) {
  var sheet = ss.getSheetByName(name);
  if (!sheet) sheet = ss.insertSheet(name);
  if (sheet.getLastRow() < 1) sheet.appendRow(headers);
  return sheet;
}

function buildIdRowMap_(sheet) {
  var lastRow = sheet.getLastRow();
  if (lastRow <= 1) return {};
  var ids = sheet.getRange(2, 1, lastRow - 1, 1).getValues();
  var map = {};
  for (var i = 0; i < ids.length; i++) {
    var id = String(ids[i][0] || '').trim();
    if (id) map[id] = i + 2;
  }
  return map;
}

function syncToRekapTransaksi_(spreadsheetId, items, now) {
  var ss = SpreadsheetApp.openById(spreadsheetId);
  var sheet = ensureSheetWithHeader_(ss, 'Belanja', ['ID', 'Nama', 'Harga', 'Qty', 'Total', 'Tanggal', 'Kategori', 'Catatan', 'Timestamp']);
  var map = buildIdRowMap_(sheet);
  var inserted = 0;
  var updated = 0;
  var skipped = 0;
  for (var i = 0; i < items.length; i++) {
    var it = items[i];
    if (!it || !it.id) { skipped++; continue; }
    var row = [
      it.id,
      it.nama || '',
      Number(it.harga) || 0,
      Number(it.qty) || 0,
      Number(it.total) || 0,
      it.tgl,
      it.kategori || '',
      it.catatan || '',
      now
    ];
    var existingRow = map[it.id];
    if (existingRow) {
      sheet.getRange(existingRow, 1, 1, row.length).setValues([row]);
      updated++;
    } else {
      sheet.appendRow(row);
      inserted++;
    }
  }
  return { ok: true, inserted: inserted, updated: updated, skipped: skipped };
}

function mapKategoriToSB_(kat) {
  var s = String(kat || '').trim().toLowerCase();
  if (s === 'modal') return 'MODAL';
  if (s === 'bawah') return 'BAWAH';
  if (s === 'dapur') return 'DAPUR';
  if (s === 'makan') return 'MAKAN';
  return '';
}

function syncToStokBelanja_(spreadsheetId, items, now) {
  var ss = SpreadsheetApp.openById(spreadsheetId);
  var sheetBelanja = ensureSheetWithHeader_(ss, 'Belanja', ['ID', 'Nama', 'Harga', 'Qty', 'Total', 'Tanggal', 'Kategori', 'Catatan', 'User', 'Timestamp']);
  var sheetOp = ensureSheetWithHeader_(ss, 'Operasional', ['ID', 'Tanggal', 'Nama', 'Harga', 'Qty', 'Total', 'Catatan', 'User', 'Timestamp']);
  var sheetPg = ensureSheetWithHeader_(ss, 'Pegawai', ['ID', 'Tanggal', 'Nama', 'Nilai', 'Kategori', 'Catatan', 'User', 'Timestamp']);

  var mapBel = buildIdRowMap_(sheetBelanja);
  var mapOp = buildIdRowMap_(sheetOp);
  var mapPg = buildIdRowMap_(sheetPg);

  var belanjaCount = 0;
  var operasionalCount = 0;
  var pegawaiCount = 0;
  var skipped = 0;

  for (var i = 0; i < items.length; i++) {
    var it = items[i];
    if (!it || !it.id) { skipped++; continue; }
    var kat = String(it.kategori || '').trim();
    var low = kat.toLowerCase();

    if (low === 'operasional') {
      var idOp = 'POS-' + it.id;
      var qtyOp = Number(it.qty) || 0;
      if (qtyOp <= 0) qtyOp = 1;
      var hargaOp = Number(it.harga) || 0;
      var totalOp = Number(it.total) || 0;
      if (totalOp <= 0 && hargaOp > 0) totalOp = hargaOp * qtyOp;
      if (hargaOp <= 0 && totalOp > 0) hargaOp = Math.round(totalOp / qtyOp);
      if (totalOp <= 0) { skipped++; continue; }
      var rowOp = [idOp, it.tgl, it.nama || '', hargaOp, qtyOp, totalOp, it.catatan || '', 'POSFuku', now];
      var rOp = mapOp[idOp];
      if (rOp) sheetOp.getRange(rOp, 1, 1, rowOp.length).setValues([rowOp]); else sheetOp.appendRow(rowOp);
      operasionalCount++;
      continue;
    }

    if (low === 'pegawai') {
      var idPg = 'POS-' + it.id;
      var nilai = Number(it.total) || 0;
      if (nilai <= 0) { skipped++; continue; }
      var kategoriPg = /kasbon/i.test(it.nama || '') || /kasbon/i.test(it.catatan || '') ? 'Kasbon' : 'Gaji';
      var rowPg = [idPg, it.tgl, it.nama || '', nilai, kategoriPg, it.catatan || '', 'POSFuku', now];
      var rPg = mapPg[idPg];
      if (rPg) sheetPg.getRange(rPg, 1, 1, rowPg.length).setValues([rowPg]); else sheetPg.appendRow(rowPg);
      pegawaiCount++;
      continue;
    }

    var sbKat = mapKategoriToSB_(kat);
    if (!sbKat) { skipped++; continue; }
    var rowBel = [it.id, it.nama || '', Number(it.harga) || 0, Number(it.qty) || 0, Number(it.total) || 0, it.tgl, sbKat, it.catatan || '', 'POSFuku', now];
    var rBel = mapBel[it.id];
    if (rBel) sheetBelanja.getRange(rBel, 1, 1, rowBel.length).setValues([rowBel]); else sheetBelanja.appendRow(rowBel);
    belanjaCount++;
  }

  return { ok: true, belanja: belanjaCount, operasional: operasionalCount, pegawai: pegawaiCount, skipped: skipped };
}

function searchTransactionsGlobal(query, limit) {
  var q = String(query || '').trim().toLowerCase();
  var lim = Number(limit) || 50;
  if (lim < 1) lim = 50;
  if (lim > 200) lim = 200;
  if (!q) return { items: [] };

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Transaksi');
  if (!sheet) throw new Error('Sheet Transaksi tidak ditemukan.');
  var lastRow = sheet.getLastRow();
  if (lastRow <= 1) return { items: [] };
  var lastCol = sheet.getLastColumn();
  var rowCount = lastRow - 1;
  var maxScan = 5000;
  var startRow = Math.max(2, lastRow - maxScan + 1);
  var scanCount = lastRow - startRow + 1;
  var data = sheet.getRange(startRow, 1, scanCount, lastCol).getValues();

  function fmtDate_(v) {
    if (!v) return '';
    if (v instanceof Date) return Utilities.formatDate(v, 'GMT+7', 'yyyy-MM-dd');
    var s = String(v).trim();
    if (/^\d{4}-\d{2}-\d{2}/.test(s)) return s.slice(0, 10);
    try {
      var d = new Date(s);
      if (!isNaN(d.getTime())) return Utilities.formatDate(d, 'GMT+7', 'yyyy-MM-dd');
    } catch (e) {}
    return s.slice(0, 10);
  }

  function parseItems_(jsonStr) {
    if (!jsonStr) return [];
    try {
      var arr = JSON.parse(String(jsonStr));
      if (!Array.isArray(arr)) return [];
      return arr.map(function(it) {
        return { nama: String(it.nama || ''), qty: Number(it.qty) || 0 };
      }).filter(function(it) { return it.nama; });
    } catch (e) {
      return [];
    }
  }

  var out = [];
  for (var i = data.length - 1; i >= 0; i--) {
    var r = data[i];
    var id = String(r[0] || '').trim();
    if (!id) continue;
    var meja = String(r[1] || '').trim();
    var status = String(r[2] || '').trim();
    var nama = String(r[3] || '').trim();
    var wa = String(r[4] || '').trim();
    var tanggal = fmtDate_(r[5]);
    var itemsJson = r[7];
    var items = parseItems_(itemsJson);
    var itemsText = items.slice(0, 4).map(function(it) { return it.nama + (it.qty ? (' x' + it.qty) : ''); }).join(', ');
    var total = Number(r[17]) || 0;
    var metodeBayar = String(r[21] || '').trim();
    var catatan = String(r[27] || '').trim();
    var hay = (id + ' ' + meja + ' ' + status + ' ' + nama + ' ' + wa + ' ' + tanggal + ' ' + metodeBayar + ' ' + catatan + ' ' + items.map(function(it){ return it.nama; }).join(' ')).toLowerCase();
    if (hay.indexOf(q) === -1) continue;
    out.push({ id: id, tanggal: tanggal, meja: meja, nama: nama, status: status, total: total, metodeBayar: metodeBayar, itemsText: itemsText });
    if (out.length >= lim) break;
  }
  return { items: out };
}

function calcPotensiProfitFromItems_(ss, items) {
  var list = Array.isArray(items) ? items : [];
  if (!list.length) return 0;
  var menuMap = {};
  var paketMap = {};

  var sheetMenu = ss.getSheetByName('Menu');
  if (sheetMenu && sheetMenu.getLastRow() > 1) {
    var hMenu = getHeaderIndexMap_(sheetMenu);
    var lcMenu = sheetMenu.getLastColumn();
    var idxNamaM = (hMenu['nama'] != null) ? hMenu['nama'] : 0;
    var idxHargaM = (hMenu['harga'] != null) ? hMenu['harga'] : 1;
    var idxModalM = (hMenu['modal'] != null) ? hMenu['modal'] : -1;
    var idxStatusM = (hMenu['status'] != null) ? hMenu['status'] : 3;
    var dataMenu = sheetMenu.getRange(2, 1, sheetMenu.getLastRow() - 1, lcMenu).getValues();
    for (var i = 0; i < dataMenu.length; i++) {
      var r = dataMenu[i];
      var nm = String(r[idxNamaM] || '').trim();
      if (!nm) continue;
      var status = String(r[idxStatusM] || '').toLowerCase().trim();
      if (status && status !== 'aktif') continue;
      menuMap[nm.toLowerCase()] = { harga: parseNumberFlexible(r[idxHargaM]), modal: idxModalM > -1 ? parseNumberFlexible(r[idxModalM]) : 0 };
    }
  }

  var sheetPaket = ss.getSheetByName('PaketKustom');
  if (sheetPaket && sheetPaket.getLastRow() > 1) {
    var hP = getHeaderIndexMap_(sheetPaket);
    var lcP = sheetPaket.getLastColumn();
    var idxNamaP = (hP['nama paket'] != null) ? hP['nama paket'] : (hP['nama'] != null ? hP['nama'] : 1);
    var idxTotalP = (hP['total harga'] != null) ? hP['total harga'] : (hP['total'] != null ? hP['total'] : 3);
    var idxModalP = (hP['modal'] != null) ? hP['modal'] : -1;
    var idxAktifP = (hP['aktif'] != null) ? hP['aktif'] : 6;
    var dataP = sheetPaket.getRange(2, 1, sheetPaket.getLastRow() - 1, lcP).getValues();
    for (var j = 0; j < dataP.length; j++) {
      var rp = dataP[j];
      var np = String(rp[idxNamaP] || '').trim();
      if (!np) continue;
      var aktif = rp[idxAktifP];
      var isAktif = (aktif === true || String(aktif).toLowerCase() === 'aktif' || String(aktif).toLowerCase() === 'true' || aktif === 1);
      if (!isAktif) continue;
      paketMap[np.toLowerCase()] = { harga: parseNumberFlexible(rp[idxTotalP]), modal: idxModalP > -1 ? parseNumberFlexible(rp[idxModalP]) : 0 };
    }
  }

  var totalProfit = 0;
  for (var k = 0; k < list.length; k++) {
    var it = list[k] || {};
    var nama = String(it.nama || '').trim();
    var qty = Number(it.qty) || 0;
    if (!nama || !qty) continue;
    var key = nama.toLowerCase();
    var ref = menuMap[key] || paketMap[key];
    if (!ref) continue;
    var margin = (Number(ref.harga) || 0) - (Number(ref.modal) || 0);
    totalProfit += margin * qty;
  }
  return Math.round(totalProfit);
}

function deleteBelanja(id) {
  if (!id) throw new Error('ID belanja tidak valid.');
  var lock = LockService.getScriptLock();
  try { lock.waitLock(30000); } catch (e) { throw new Error('Server sibuk.'); }
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName('Belanja');
    var data = sheet.getDataRange().getValues();
    var rowIdx = -1;
    for (var i = 1; i < data.length; i++) {
      if (String(data[i][0]) === String(id)) { rowIdx = i + 1; break; }
    }
    if (rowIdx > -1) {
      sheet.deleteRow(rowIdx);
    }
    return getInitialData();
  } finally { lock.releaseLock(); }
}

function saveRekap(payload) {
  if (!payload) throw new Error('Data rekap tidak valid (payload kosong).');
  var lock = LockService.getScriptLock();
  try { lock.waitLock(30000); } catch (e) { throw new Error('Server sibuk.'); }
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName('Rekap');
    if (!sheet) { setupSheets(); sheet = ss.getSheetByName('Rekap'); }
    var now = new Date();
    var id = payload.id || ('R-' + Date.now());
    
    // ID, Tanggal, Saldo Awal, Tunai, QRIS, Debit, Credit, Transfer, Catatan, Timestamp
    var row = [
      id, 
      payload.tgl || Utilities.formatDate(now, "GMT+7", "yyyy-MM-dd"),
      Number(payload.saldoAwal) || 0, 
      Number(payload.tunai) || 0,
      Number(payload.qris) || 0,
      Number(payload.debit) || 0,
      Number(payload.credit) || 0,
      Number(payload.transfer) || 0,
      payload.catatan || '', 
      now
    ];
    
    var data = sheet.getDataRange().getValues();
    var rowIdx = -1;
    for (var i = 1; i < data.length; i++) {
      if (String(data[i][0]) === String(id)) { rowIdx = i + 1; break; }
    }
    
    if (rowIdx > -1) sheet.getRange(rowIdx, 1, 1, row.length).setValues([row]);
    else sheet.appendRow(row);
    
    return getInitialData();
  } finally { lock.releaseLock(); }
}

function updateMenuStock(nama, newStok, terpakai) {
  return saveStokBulk([{ nama: nama, sisa: newStok, terpakai: terpakai }]);
}

function saveStokBulk(payloads) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Menu');
  var sheetH = ss.getSheetByName('StokHarian') || ss.insertSheet('StokHarian');
  
  var data = sheet.getDataRange().getValues();
  var now = new Date();
  var todayStr = Utilities.formatDate(now, "GMT+7", "yyyy-MM-dd");
  
  payloads.forEach(function(p) {
    var nama = p.nama;
    var newSisa = Number(p.sisa) || 0;
    var terpakai = Number(p.terpakai) || 0;
    
    // Update di sheet Menu
    for (var i = 1; i < data.length; i++) {
      if (String(data[i][0]) === nama) {
        sheet.getRange(i + 1, 6).setValue(newSisa);
        break;
      }
    }
    
    // Simpan ke StokHarian
    sheetH.appendRow([todayStr, nama, terpakai + newSisa, terpakai, newSisa, now]);
  });
  
  return getInitialData();
}
function voidTransaction(id, reason, fotoUrl) {
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheetT = ss.getSheetByName('Transaksi');
    var sheetV = ss.getSheetByName('Void') || ss.insertSheet('Void');
    
    if (sheetV.getLastRow() === 0) {
      sheetV.appendRow(['Timestamp', 'ID Transaksi', 'Meja', 'Total', 'Alasan', 'Foto', 'Data Lengkap']);
    }

    // Optimasi: Hanya ambil kolom ID (Kolom A) untuk mencari baris
    var lastRow = sheetT.getLastRow();
    if (lastRow < 2) return { error: "Tidak ada transaksi" };
    
    var ids = sheetT.getRange(1, 1, lastRow, 1).getValues();
    var foundIdx = -1;
    for (var i = 1; i < ids.length; i++) {
      if (String(ids[i][0]) === String(id)) {
        foundIdx = i + 1;
        break;
      }
    }

    if (foundIdx > -1) {
      // Ambil data lengkap baris tersebut untuk dipindah ke Void
      var rowData = sheetT.getRange(foundIdx, 1, 1, sheetT.getLastColumn()).getValues()[0];
      sheetV.appendRow([new Date(), id, rowData[1], rowData[17], reason, fotoUrl || '', JSON.stringify(rowData)]);
      
      // Update status di Transaksi menjadi "Void" (bukan hapus baris)
      sheetT.getRange(foundIdx, 3).setValue('Void');
      
      return getInitialData();
    } else {
      return { error: "Transaksi tidak ditemukan" };
    }
  } catch (err) {
    console.error('Error voidTransaction:', err);
    return { error: err.message };
  }
}

function getReport(dari, sampai) {
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName('Transaksi');
    var sheetB = ss.getSheetByName('Belanja');
    if (!sheet) return { omzet: 0, items: [], methods: {}, belanja: 0 };
    
    var lastRow = sheet.getLastRow();
    var data = lastRow > 1 ? sheet.getRange(2, 1, lastRow - 1, 28).getValues() : [];
    var tz = 'GMT+7';
    var today = Utilities.formatDate(new Date(), tz, 'yyyy-MM-dd');
    var from = dari || today;
    var to = sampai || today;
    
    var omzet = 0;
    var counter = {};
    var methods = { 'Tunai': 0, 'QRIS': 0, 'Debit': 0, 'Credit': 0, 'Transfer': 0 };

    data.forEach(function(r, idx) {
      if (r.length < 5) return; // Baris terlalu pendek
      var status = String(r[2] || '');
      if (status !== 'Selesai') return;
      
      // Deteksi tanggal dari berbagai kolom yang mungkin
      var dt = null;
      if (r[25] instanceof Date) dt = r[25];
      else if (r[5] instanceof Date) dt = r[5];
      else if (r[0] instanceof Date) dt = r[0];
      
      // Jika bukan Date, coba parsing string
      if (!dt) {
        var dateStr = String(r[25] || r[5] || r[0] || '');
        if (dateStr) {
          var parsed = new Date(dateStr);
          if (!isNaN(parsed.getTime())) dt = parsed;
        }
      }
      
      if (!dt) {
        console.warn('Baris ' + (idx+2) + ': Gagal mendeteksi tanggal');
        return;
      }
      
      var d = Utilities.formatDate(dt, tz, 'yyyy-MM-dd');
      if (d < from || d > to) return;
      
      var total = Number(r[17]) || 0;
      omzet += total;
      
      var m = String(r[21] || 'Tunai').trim() || 'Tunai';
      if (m === 'Multi') {
        try {
          var details = JSON.parse(r[27] || '{}'); // r[27] adalah kolom Catatan
          for (var key in details) {
            var val = Number(details[key]) || 0;
            if (val > 0) {
              if (methods.hasOwnProperty(key)) methods[key] += val;
              else methods[key] = (methods[key] || 0) + val;
            }
          }
        } catch (e) {
          console.error('Gagal parsing JSON multi-payment baris ' + (idx+2) + ':', e);
          // Fallback jika gagal parse, masukkan ke Tunai atau Multi
          methods['Tunai'] = (methods['Tunai'] || 0) + total;
        }
      } else {
        if (methods.hasOwnProperty(m)) methods[m] += total;
        else methods[m] = (methods[m] || 0) + total;
      }

      try {
        var itemsJson = String(r[7] || '[]');
        if (itemsJson.indexOf('[') === 0) {
          var items = JSON.parse(itemsJson);
          if (Array.isArray(items)) {
            items.forEach(function(it) {
              var k = String(it.nama || '');
              if (!k) return;
              counter[k] = (counter[k] || 0) + (Number(it.qty) || 0);
            });
          }
        }
      } catch (e) {
        console.error('Gagal parsing JSON item baris ' + (idx+2) + ':', e);
      }
    });

    console.log('Report generated: omzet=' + omzet + ', methods=' + JSON.stringify(methods));

    var belanjaTotal = 0;
    var belanjaItems = [];
    if (sheetB) {
      var lastRowB = sheetB.getLastRow();
      var bData = lastRowB > 1 ? sheetB.getRange(2, 1, lastRowB - 1, 9).getValues() : [];
      bData.forEach(function(r) {
        if (r.length < 9) return;
        var dt = r[8] instanceof Date ? r[8] : (r[5] instanceof Date ? r[5] : null);
        if (!dt && r[8]) dt = new Date(r[8]);
        if (!dt && r[5]) dt = new Date(r[5]);
        
        if (!dt || isNaN(dt.getTime())) return;
        var d = Utilities.formatDate(dt, tz, 'yyyy-MM-dd');
        if (d < from || d > to) return;
        var t = Number(r[4]) || 0;
        belanjaTotal += t;
        belanjaItems.push({ nama: r[1], total: t, tgl: r[5], id: r[0] });
      });
    }

    var itemsSorted = Object.keys(counter).map(function(k) { return [k, counter[k]]; }).sort(function(a, b) { return b[1] - a[1]; });
    return { omzet: omzet, items: itemsSorted, methods: methods, belanja: belanjaTotal, belanjaItems: belanjaItems };
  } catch (err) {
    console.error('Error di getReport:', err);
    return { error: err.message, omzet: 0, items: [], methods: {}, belanja: 0 };
  }
}

function saveSupplierWA(payload) {
  var lock = LockService.getScriptLock();
  try { lock.waitLock(30000); } catch (e) { throw new Error('Server sibuk.'); }
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName('SupplierHistory');
    if (!sheet) { setupSheets(); sheet = ss.getSheetByName('SupplierHistory'); }
    var now = new Date();
    var id = 'S-' + Date.now();
    
    // ID, Nama Supplier, Kategori, WhatsApp, Pesan, Timestamp
    var row = [
      id, 
      payload.nama || '', 
      payload.kategori || '',
      payload.wa || '',
      payload.pesan || '',
      now
    ];
    
    sheet.appendRow(row);
    return getInitialData();
  } finally { lock.releaseLock(); }
}

// --- Fungsi Chat & Bel ---
function sendChatMessage(sender, message) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Chat') || ss.insertSheet('Chat');
  if (sheet.getLastRow() === 0) {
    sheet.appendRow(['Timestamp', 'Pengirim', 'Pesan', 'Status']);
  }
  sheet.appendRow([new Date(), sender, message, 'Belum Dibaca']);
  return { success: true };
}

function getChatMessages() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Chat');
  if (!sheet) return [];
  var data = sheet.getDataRange().getValues();
  data.shift(); // Hapus header
  return data.map(function(row) {
    return { timestamp: parseDate(row[0]), sender: row[1], message: row[2], status: row[3] };
  });
}

function markChatAsRead(receiver) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Chat');
  if (!sheet) return { success: false };
  var data = sheet.getDataRange().getValues();
  var changed = false;
  for (var i = 1; i < data.length; i++) {
    // Jika pengirim bukan kita (receiver) dan statusnya Belum Dibaca
    if (data[i][1] !== receiver && data[i][3] === 'Belum Dibaca') {
      sheet.getRange(i + 1, 4).setValue('Sudah Dibaca');
      changed = true;
    }
  }
  return { success: true, changed: changed };
}

function triggerBell(bellId) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('BellStatus') || ss.insertSheet('BellStatus');
  if (sheet.getLastRow() === 0) {
    sheet.appendRow(['BellID', 'Status', 'Timestamp']);
  }
  var data = sheet.getDataRange().getValues();
  var found = false;
  for (var i = 1; i < data.length; i++) {
    if (data[i][0] === bellId) {
      sheet.getRange(i + 1, 2, 1, 2).setValues([['Active', new Date()]]);
      found = true;
      break;
    }
  }
  if (!found) {
    sheet.appendRow([bellId, 'Active', new Date()]);
  }
  try {
    var settings = getSettingsMap_();
    if (String(settings.telegram_bell_enabled || '').toLowerCase() === 'true') {
      sendTelegram_('BEL: ' + String(bellId || ''));
    }
  } catch (eNotify) {}
  return { success: true };
}

function resetBell(bellId) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('BellStatus');
  if (!sheet) return { success: false, error: 'Sheet BellStatus tidak ditemukan.' };
  var data = sheet.getDataRange().getValues();
  for (var i = 1; i < data.length; i++) {
    if (data[i][0] === bellId) {
      sheet.getRange(i + 1, 2).setValue('Inactive');
      break;
    }
  }
  return { success: true };
}

function notifyWaiterTelegram(payload) {
  try {
    var settings = getSettingsMap_();
    if (String(settings.telegram_waiter_enabled || '').toLowerCase() !== 'true') return { success: true, sent: false, skipped: true };
    var meja = payload && payload.meja ? String(payload.meja) : '';
    var waiter = payload && payload.waiter ? String(payload.waiter) : '';
    var message = payload && payload.message ? String(payload.message) : '';
    var muted = /x123/i.test(String(payload && payload.nama ? payload.nama : ''));
    if (muted) return { success: true, sent: false, skipped: true };
    var text = 'PANGGIL PELAYAN\nMeja: ' + meja + (waiter ? '\nPelayan: ' + waiter : '') + (message ? '\n\n' + message : '');
    var res = sendTelegram_(text);
    return { success: true, sent: !!res.success, result: res };
  } catch (e) {
    return { success: false, sent: false, error: String(e && e.message ? e.message : e) };
  }
}

function getBellStatus(bellId) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('BellStatus');
  if (!sheet) return { status: 'Inactive' };
  var data = sheet.getDataRange().getValues();
  for (var i = 1; i < data.length; i++) {
    if (data[i][0] === bellId) {
      return { status: data[i][1] };
    }
  }
  return { status: 'Inactive' };
}


function saveMasterSupplier(payload) {
  var lock = LockService.getScriptLock();
  try { lock.waitLock(30000); } catch (e) { throw new Error('Server sibuk.'); }
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName('Suppliers');
    if (!sheet) { setupSheets(); sheet = ss.getSheetByName('Suppliers'); }
    var lastCol = sheet.getLastColumn();
    var now = new Date();
    
    var data = sheet.getDataRange().getValues();
    var foundIdx = -1;
    for (var i = 1; i < data.length; i++) {
      if (payload && payload.id && String(data[i][0]) === String(payload.id)) {
        foundIdx = i + 1;
        break;
      }
      if (String(data[i][1]).toLowerCase() === String(payload.nama).toLowerCase()) {
        foundIdx = i + 1;
        break;
      }
    }
    
    var id = (payload && payload.id) ? String(payload.id) : ((foundIdx > -1 && data[foundIdx - 1] && data[foundIdx - 1][0]) ? String(data[foundIdx - 1][0]) : ('SUP-' + Date.now()));
    var row = [id, payload.nama || '', payload.wa || ''];
    if (lastCol >= 5) {
      row.push(payload.rekening || '');
      row.push(now);
    } else {
      row.push(now);
    }
    
    if (foundIdx > -1) {
      sheet.getRange(foundIdx, 1, 1, row.length).setValues([row]);
    } else {
      sheet.appendRow(row);
    }
    
    return getInitialData();
  } finally { lock.releaseLock(); }
}

function deleteMasterSupplier(idOrName) {
  var lock = LockService.getScriptLock();
  try { lock.waitLock(30000); } catch (e) { throw new Error('Server sibuk.'); }
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName('Suppliers');
    if (!sheet) return getInitialData();
    var data = sheet.getDataRange().getValues();
    var key = String(idOrName || '').trim();
    if (!key) return getInitialData();
    var keyLower = key.toLowerCase();
    for (var i = 1; i < data.length; i++) {
      if (String(data[i][0]) === key) {
        sheet.deleteRow(i + 1);
        break;
      }
    }
    if (sheet.getLastRow() > 1) {
      data = sheet.getDataRange().getValues();
      for (var j = 1; j < data.length; j++) {
        if (String(data[j][1] || '').trim().toLowerCase() === keyLower) {
          sheet.deleteRow(j + 1);
          break;
        }
      }
    }
    return getInitialData();
  } finally { lock.releaseLock(); }
}

function saveWaiter(payload) {
  var lock = LockService.getScriptLock();
  try { lock.waitLock(30000); } catch (e) { throw new Error('Server sibuk.'); }
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName('Waiters');
    if (!sheet) { setupSheets(); sheet = ss.getSheetByName('Waiters'); }
    var now = new Date();
    
    var data = sheet.getDataRange().getValues();
    var foundIdx = -1;
    for (var i = 1; i < data.length; i++) {
      if (String(data[i][0]).toLowerCase() === String(payload.nama).toLowerCase()) {
        foundIdx = i + 1;
        break;
      }
    }
    
    var row = [
      payload.nama || '',
      payload.wa || '',
      payload.status || 'Aktif',
      now
    ];
    
    if (foundIdx > -1) {
      sheet.getRange(foundIdx, 1, 1, row.length).setValues([row]);
    } else {
      sheet.appendRow(row);
    }
    
    return getInitialData();
  } finally { lock.releaseLock(); }
}

function deleteWaiter(nama) {
  var lock = LockService.getScriptLock();
  try { lock.waitLock(30000); } catch (e) { throw new Error('Server sibuk.'); }
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName('Waiters');
    if (!sheet) return getInitialData();
    
    var data = sheet.getDataRange().getValues();
    for (var i = 1; i < data.length; i++) {
      if (String(data[i][0]).toLowerCase() === String(nama).toLowerCase()) {
        sheet.deleteRow(i + 1);
        break;
      }
    }
    return getInitialData();
  } finally { lock.releaseLock(); }
}

function deleteTransaction(id) {
  var lock = LockService.getScriptLock();
  try { lock.waitLock(30000); } catch (e) { throw new Error('Server sibuk.'); }
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName('Transaksi');
    if (!sheet) return getInitialData();
    
    var data = sheet.getDataRange().getValues();
    var foundIdx = -1;
    for (var i = 1; i < data.length; i++) {
      if (String(data[i][0]) === String(id)) {
        foundIdx = i + 1;
        var t = data[i];
        var wa = String(t[4] || '');
        var poinDapat = Number(t[24]) || 0;
        var poinDipakaiRp = Number(t[10]) || 0;
        var settings = getSettingsMap_();
        var redeemRp = parseNumberFlexible(settings.poin_redeem_rupiah);
        if (!redeemRp || redeemRp < 1) redeemRp = 100;
        var poinUsed = Math.floor(poinDipakaiRp / redeemRp);
        
        if (wa) {
          var sheetCust = ss.getSheetByName('Pelanggan');
          if (sheetCust) {
            var custRows = sheetCust.getDataRange().getValues();
            var cIdx = -1;
            for (var j = 1; j < custRows.length; j++) {
              if (String(custRows[j][1]) === wa) { cIdx = j + 1; break; }
            }
            if (cIdx > -1) {
              var curP = Number(custRows[cIdx - 1][2]) || 0;
              var newP = Math.max(0, curP - poinDapat + poinUsed); // Batal dapat, kembalikan yang dipakai
              sheetCust.getRange(cIdx, 3).setValue(newP);
              sheetCust.getRange(cIdx, 4).setValue(new Date());
            }
          }
        }
        
        sheet.deleteRow(foundIdx);
        break;
      }
    }
    return getInitialData();
  } finally { lock.releaseLock(); }
}

function clearTransactionHistoryKeepLast() {
  var lock = LockService.getScriptLock();
  try { lock.waitLock(30000); } catch (e) { throw new Error('Server sibuk.'); }
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName('Transaksi');
    if (!sheet) return getInitialData();
    var lastRow = sheet.getLastRow();
    if (lastRow <= 2) return getInitialData(); // header + 1 row (atau kosong)
    sheet.deleteRows(2, lastRow - 2); // sisakan header dan 1 transaksi terakhir
    return getInitialData();
  } finally { lock.releaseLock(); }
}

function resetAllStock() {
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheetM = ss.getSheetByName('Menu');
    var dataM = sheetM.getDataRange().getValues();
    var now = new Date();
    var nowStr = Utilities.formatDate(now, "GMT+7", "yyyy-MM-dd HH:mm:ss");
    
    // Simpan timestamp reset ke properties
    PropertiesService.getScriptProperties().setProperty('last_stock_reset', nowStr);
    
    // Log ke StokHarian sebagai pembukaan toko
    var sheetH = ss.getSheetByName('StokHarian') || ss.insertSheet('StokHarian');
    var todayStr = Utilities.formatDate(now, "GMT+7", "yyyy-MM-dd");
    var logs = [];
    for (var i = 1; i < dataM.length; i++) {
      var nama = String(dataM[i][0]);
      var sisa = Number(dataM[i][5]) || 0;
      logs.push([todayStr, nama, sisa, 0, sisa, now]);
    }
    if (logs.length > 0) {
      sheetH.getRange(sheetH.getLastRow() + 1, 1, logs.length, 6).setValues(logs);
    }
    
    return getInitialData();
  } catch (e) {
    return { error: e.message };
  }
}

function saveFeedback(payload) {
  var lock = LockService.getScriptLock();
  try { lock.waitLock(30000); } catch (e) { throw new Error('Server sibuk.'); }
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName('Feedback');
    if (!sheet) { setupSheets(); sheet = ss.getSheetByName('Feedback'); }
    var now = new Date();
    
    // Timestamp, Nama Pelanggan, WhatsApp, Rating, Komentar
    var row = [
      now,
      payload.nama || '',
      payload.wa || '',
      payload.rating || 0,
      payload.komentar || ''
    ];
    
    sheet.appendRow(row);
    return { success: true };
  } finally { lock.releaseLock(); }
}
function paksaIzinDrive() {
  // Baris ini akan memaksa Google mendeteksi kebutuhan akses Drive
  var folder = DriveApp.getRootFolder();
  console.log("Akses Drive Berhasil: " + folder.getName());
}