/**
 * Strydom Begrafnisstelsel Google Sheets Sync
 * Plak hierdie hele kode in Google Apps Script.
 * Deploy: New deployment > Web app > Execute as: Me > Who has access: Anyone with the link.
 */
const SHEET_NAME = 'Funerals';

function doGet(e) {
  return handleRequest_(e && e.parameter ? e.parameter : {});
}

function doPost(e) {
  let data = {};
  try {
    data = JSON.parse(e.postData && e.postData.contents ? e.postData.contents : '{}');
  } catch (err) {
    return json_({ success:false, error:'Invalid JSON: ' + err });
  }
  return handleRequest_(data);
}

function handleRequest_(data) {
  try {
    const action = data.action || 'loadCases';
    if (action === 'saveCase') return json_(saveCase_(data.case || {}));
    if (action === 'loadCases') return json_(loadCases_(data.bb || ''));
    if (action === 'ping') return json_({ success:true, message:'Google Sheets sync werk' });
    return json_({ success:false, error:'Unknown action: ' + action });
  } catch (err) {
    return json_({ success:false, error:String(err && err.stack ? err.stack : err) });
  }
}

function sheet_() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let sh = ss.getSheetByName(SHEET_NAME);
  if (!sh) sh = ss.insertSheet(SHEET_NAME);
  const headers = ['bb','status','deceasedName','burialDate','dorp','serviceType','consultantName','policyNumber','invoiceNo','updatedAt','json'];
  if (sh.getLastRow() === 0) sh.appendRow(headers);
  const first = sh.getRange(1,1,1,headers.length).getValues()[0];
  if (String(first[0]).trim() !== 'bb') {
    sh.insertRowBefore(1);
    sh.getRange(1,1,1,headers.length).setValues([headers]);
  }
  return sh;
}

function saveCase_(c) {
  if (!c || !c.bb) return { success:false, error:'Geen BB nommer ontvang nie' };
  const sh = sheet_();
  const values = sh.getDataRange().getValues();
  let row = -1;
  for (let i=1; i<values.length; i++) {
    if (String(values[i][0]).trim() === String(c.bb).trim()) { row = i+1; break; }
  }
  const now = new Date();
  const out = [
    c.bb || '', c.status || '', c.deceasedName || '', c.burialDate || '', c.dorp || '',
    c.serviceType || '', c.consultantName || '', c.policyNumber || '', c.invoiceNo || '',
    Utilities.formatDate(now, Session.getScriptTimeZone(), 'yyyy-MM-dd HH:mm:ss'),
    JSON.stringify(c)
  ];
  if (row < 0) sh.appendRow(out); else sh.getRange(row,1,1,out.length).setValues([out]);
  return { success:true, bb:c.bb, row: row < 0 ? sh.getLastRow() : row };
}

function loadCases_(bb) {
  const sh = sheet_();
  const values = sh.getDataRange().getValues();
  const cases = [];
  for (let i=1; i<values.length; i++) {
    const raw = values[i][10];
    if (!raw) continue;
    try {
      const c = JSON.parse(raw);
      if (!c.bb && values[i][0]) c.bb = values[i][0];
      if (bb && String(c.bb).trim() !== String(bb).trim()) continue;
      cases.push(c);
    } catch (err) {}
  }
  if (bb) return { success:true, case: cases.length ? cases[0] : null };
  return { success:true, cases };
}

function json_(obj) {
  return ContentService
    .createTextOutput(JSON.stringify(obj))
    .setMimeType(ContentService.MimeType.JSON);
}
