/**
 * BUKU KAS — Google Apps Script (Merge-Based Sync)
 * ==================================================
 * CARA DEPLOY:
 * 1. Buka Google Spreadsheet → Extensions → Apps Script
 * 2. Hapus semua kode lama, paste kode ini
 * 3. Klik Deploy → New deployment
 *    - Type        : Web app
 *    - Execute as  : Me
 *    - Who can access: Anyone
 * 4. Klik Deploy → salin URL-nya
 * 5. Paste URL ke aplikasi Buku Kas → Settings → URL Apps Script
 *
 * PENTING: Selalu buat deployment BARU setiap update kode.
 * Jangan klik "Edit" deployment lama.
 */

// ─── Kolom Spreadsheet ───────────────────────────────────────
// A=UUID | B=Tanggal | C=Jenis | D=Kategori | E=Jumlah
// F=Keterangan | G=Waktu Input | H=Terakhir Sync | I=createdAt_ts

var HEADERS = [
  'UUID', 'Tanggal', 'Jenis', 'Kategori', 'Jumlah (Rp)',
  'Keterangan', 'Waktu Input', 'Terakhir Sync', 'createdAt_ts'
];

// ─── Router ──────────────────────────────────────────────────
function doPost(e) {
  try {
    var data = JSON.parse(e.postData.contents);
    if (data.action === 'sync')        return handleSync(data);
    if (data.action === 'check_user')  return handleCheckUser(data);
    if (data.action === 'delete_user') return handleDeleteUser(data);
    return jsonResponse({ success: false, error: 'Unknown action: ' + data.action });
  } catch (err) {
    return jsonResponse({ success: false, error: err.toString() });
  }
}

function doGet(e) {
  return jsonResponse({
    status:  'Buku Kas API aktif',
    version: 3,
    time:    new Date().toISOString()
  });
}

// ─── ACTION: sync ─────────────────────────────────────────────
// Menerima transaksi baru + UUID yang dihapus dari client.
// Melakukan merge dengan data di sheet (tidak overwrite).
// Mengembalikan SEMUA transaksi agar device lain bisa update lokal.
function handleSync(data) {
  var ss       = SpreadsheetApp.getActiveSpreadsheet();
  var tz       = Session.getScriptTimeZone();
  var now      = new Date();
  var syncTime = Utilities.formatDate(now, tz, 'dd/MM/yyyy HH:mm:ss');
  var userName = sanitizeName(data.userName || 'User');

  // Ambil atau buat sheet tab untuk user ini
  var sheet = ss.getSheetByName(userName);
  if (!sheet) {
    sheet = ss.insertSheet(userName);
  }

  // Pastikan header ada
  ensureHeader(sheet);

  // Baca semua baris yang ada → Map<uuid, row[]>
  var existingMap = readSheetMap(sheet);

  // 1. Hapus UUID yang dikirim client (soft-delete dari device)
  var deletedUuids = data.deletedUuids || [];
  for (var i = 0; i < deletedUuids.length; i++) {
    delete existingMap[deletedUuids[i]];
  }

  // 2. Tambahkan transaksi baru (skip jika UUID sudah ada = tidak overwrite)
  var newTxns = data.newTransactions || [];
  for (var j = 0; j < newTxns.length; j++) {
    var t = newTxns[j];
    if (t.uuid && !existingMap[t.uuid]) {
      existingMap[t.uuid] = buildRow(t, tz, syncTime);
    }
  }

  // 3. Tulis kembali semua data ke sheet
  rewriteSheet(sheet, existingMap);

  // 4. Update sheet ringkasan
  updateSummarySheet(ss, userName, existingMap, syncTime);

  // 5. Kembalikan semua transaksi ke client (termasuk dari device lain)
  var returnTxns = [];
  var keys = Object.keys(existingMap);
  for (var k = 0; k < keys.length; k++) {
    returnTxns.push(rowToObject(existingMap[keys[k]], tz));
  }

  return jsonResponse({ success: true, transactions: returnTxns });
}

// ─── ACTION: check_user ───────────────────────────────────────
// Cek apakah nama user sudah ada di Sheets.
// Jika ada, kembalikan datanya untuk ditawarkan import ke device baru.
function handleCheckUser(data) {
  var ss       = SpreadsheetApp.getActiveSpreadsheet();
  var tz       = Session.getScriptTimeZone();
  var userName = sanitizeName(data.userName || '');
  var sheet    = ss.getSheetByName(userName);

  if (!sheet) {
    return jsonResponse({ exists: false, rowCount: 0, rows: [] });
  }

  var map  = readSheetMap(sheet);
  var keys = Object.keys(map);

  if (keys.length === 0) {
    return jsonResponse({ exists: true, rowCount: 0, rows: [] });
  }

  var rows = [];
  for (var i = 0; i < keys.length; i++) {
    rows.push(rowToObject(map[keys[i]], tz));
  }

  return jsonResponse({ exists: true, rowCount: rows.length, rows: rows });
}

// ─── ACTION: delete_user ──────────────────────────────────────
// Hapus tab sheet milik user dari spreadsheet.
function handleDeleteUser(data) {
  var ss       = SpreadsheetApp.getActiveSpreadsheet();
  var userName = sanitizeName(data.userName || '');
  var sheet    = ss.getSheetByName(userName);

  if (!sheet) {
    return jsonResponse({ success: true, message: 'Sheet tidak ditemukan' });
  }

  // Google Sheets tidak bisa kosong — minimal harus ada 1 sheet
  if (ss.getSheets().length <= 1) {
    sheet.clearContents();
    sheet.setName('(kosong)');
    return jsonResponse({ success: true, message: 'Sheet dikosongkan' });
  }

  ss.deleteSheet(sheet);
  return jsonResponse({ success: true, message: 'Sheet "' + userName + '" dihapus' });
}

// ─── Helper: Baca sheet → Map ─────────────────────────────────
function readSheetMap(sheet) {
  var map = {};
  var lastRow = sheet.getLastRow();
  if (lastRow < 2) return map;

  var values = sheet.getRange(2, 1, lastRow - 1, HEADERS.length).getValues();
  for (var i = 0; i < values.length; i++) {
    var uuid = String(values[i][0]).trim();
    if (uuid && uuid !== '' && uuid !== 'UUID') {
      map[uuid] = values[i];
    }
  }
  return map;
}

// ─── Helper: Bangun satu baris dari objek transaksi ───────────
function buildRow(t, tz, syncTime) {
  var createdAt = parseInt(t.createdAt) || Date.now();
  var waktuInput = '';
  try {
    waktuInput = Utilities.formatDate(new Date(createdAt), tz, 'dd/MM/yyyy HH:mm:ss');
  } catch(e) {
    waktuInput = syncTime;
  }
  return [
    String(t.uuid        || ''),
    String(t.tgl         || ''),
    t.jenis === 'masuk' ? 'Pemasukan' : 'Pengeluaran',
    String(t.kategori    || 'Lain-lain'),
    parseFloat(t.jumlah) || 0,
    String(t.keterangan  || ''),
    waktuInput,
    syncTime,
    String(createdAt)
  ];
}

// ─── Helper: Konversi baris ke objek (untuk dikirim ke client) ─
function rowToObject(row, tz) {
  // Kolom Tanggal bisa berupa Date (auto-convert Sheets) atau string
  var tgl = '';
  if (row[1] instanceof Date) {
    try { tgl = Utilities.formatDate(row[1], tz, 'yyyy-MM-dd'); } catch(e) {}
  } else {
    tgl = String(row[1] || '').substring(0, 10);
  }

  return {
    uuid:         String(row[0] || ''),
    tgl:          tgl,
    jenis:        String(row[2]) === 'Pemasukan' ? 'masuk' : 'keluar',
    kategori:     String(row[3] || 'Lain-lain'),
    jumlah:       parseFloat(row[4]) || 0,
    keterangan:   String(row[5] || ''),
    createdAt_ts: row[8] ? String(row[8]) : ''
  };
}

// ─── Helper: Pastikan baris header ada ───────────────────────
function ensureHeader(sheet) {
  if (sheet.getLastRow() === 0) {
    writeHeader(sheet);
    return;
  }
  var firstCell = sheet.getRange(1, 1).getValue();
  if (String(firstCell) !== 'UUID') {
    sheet.insertRowBefore(1);
    writeHeader(sheet);
  }
}

function writeHeader(sheet) {
  var range = sheet.getRange(1, 1, 1, HEADERS.length);
  range.setValues([HEADERS]);
  range.setBackground('#166534');
  range.setFontColor('#ffffff');
  range.setFontWeight('bold');
}

// ─── Helper: Tulis ulang seluruh sheet dari Map ───────────────
function rewriteSheet(sheet, existingMap) {
  sheet.clearContents();
  writeHeader(sheet);

  var keys = Object.keys(existingMap);
  if (keys.length === 0) return;

  var allRows = [];
  for (var i = 0; i < keys.length; i++) {
    allRows.push(existingMap[keys[i]]);
  }

  var dataRange = sheet.getRange(2, 1, allRows.length, HEADERS.length);
  dataRange.setValues(allRows);

  // Format kolom Tanggal sebagai teks agar tidak auto-convert ke Date
  sheet.getRange(2, 2, allRows.length, 1).setNumberFormat('@STRING@');

  // Warnai baris berdasarkan jenis
  for (var j = 0; j < allRows.length; j++) {
    var color = String(allRows[j][2]) === 'Pemasukan' ? '#f0fdf4' : '#fef2f2';
    sheet.getRange(j + 2, 1, 1, HEADERS.length).setBackground(color);
  }

  sheet.autoResizeColumns(1, HEADERS.length);
}

// ─── Helper: Update sheet ringkasan ──────────────────────────
function updateSummarySheet(ss, userName, existingMap, syncTime) {
  var SUMMARY = '📊 Ringkasan';
  var summary = ss.getSheetByName(SUMMARY);

  if (!summary) {
    summary = ss.insertSheet(SUMMARY, 0);
    var hdrRange = summary.getRange(1, 1, 1, 5);
    hdrRange.setValues([['Pengguna', 'Total Masuk', 'Total Keluar', 'Saldo', 'Terakhir Sync']]);
    hdrRange.setBackground('#1e40af');
    hdrRange.setFontColor('#ffffff');
    hdrRange.setFontWeight('bold');
  }

  var masuk  = 0;
  var keluar = 0;
  var keys   = Object.keys(existingMap);
  for (var i = 0; i < keys.length; i++) {
    var row = existingMap[keys[i]];
    var amt = parseFloat(row[4]) || 0;
    if (String(row[2]) === 'Pemasukan') masuk  += amt;
    else                                keluar += amt;
  }

  // Cari baris user yang sudah ada atau tambahkan baru
  var lastRow  = summary.getLastRow();
  var rowIndex = lastRow + 1;
  if (lastRow > 1) {
    var existing = summary.getRange(2, 1, lastRow - 1, 1).getValues();
    for (var j = 0; j < existing.length; j++) {
      if (String(existing[j][0]) === userName) {
        rowIndex = j + 2;
        break;
      }
    }
  }

  summary.getRange(rowIndex, 1, 1, 5).setValues([
    [userName, masuk, keluar, masuk - keluar, syncTime]
  ]);
  summary.autoResizeColumns(1, 5);
}

// ─── Helper: Sanitasi nama untuk tab sheet ───────────────────
function sanitizeName(name) {
  // Karakter yang tidak boleh ada di nama tab sheet
  return String(name)
    .replace(/[\\\/\?\*\[\]:\'\"]/g, '')
    .trim()
    .slice(0, 30) || 'User';
}

// ─── Helper: Buat response JSON ──────────────────────────────
function jsonResponse(obj) {
  return ContentService
    .createTextOutput(JSON.stringify(obj))
    .setMimeType(ContentService.MimeType.JSON);
}