// Ledgr Backend - Google Apps Script
// Paste this entire script into Apps Script, then click Deploy

const SHEET_ID = 'YOUR_SHEET_ID_HERE'; // Replace with your Sheet ID
const TABS = ['Properties','Reps','Leases','Overrides','CommLedger','InvLedger'];

function doGet(e) {
  return handleRequest(e);
}

function doPost(e) {
  return handleRequest(e);
}

function handleRequest(e) {
  const action = e.parameter.action;
  const tab = e.parameter.tab;
  const ss = SpreadsheetApp.openById(SHEET_ID);

  try {
    if (action === 'init') return initSheets(ss);
    if (action === 'read') return readTab(ss, tab);
    if (action === 'write') return writeRow(ss, tab, JSON.parse(e.parameter.data));
    if (action === 'update') return updateRow(ss, tab, e.parameter.id, JSON.parse(e.parameter.data));
    if (action === 'delete') return deleteRow(ss, tab, e.parameter.id);
    return resp({error: 'Unknown action'});
  } catch(err) {
    return resp({error: err.toString()});
  }
}

function initSheets(ss) {
  const headers = {
    Properties: ['id','name','client','invoicingEmail','invoiceRate','renewalRate','weeklyFee','weeklyFeeType','repId','contractStart','contractEnd','invoiceType','address','status','notes'],
    Reps: ['id','name','email','commPct','advanceAmt','minCommission','baseSalary','empType','role','status','initials','canSeeCalc'],
    Leases: ['id','propertyId','repId','applicant','apt','rent','promo','promoType','promoValue','leaseTerm','effectiveTerm','miDate','status','invoiced','advancePaid','advancePaidDate','commBalPaid','commBalPaidDate','invoiceNum','invoiceAmt','splitRepId','splitPct','clawback','notes','dateLeased'],
    Overrides: ['id','repId','propertyId','commPct','advanceAmt','minCommission'],
    CommLedger: ['id','leaseId','repId','type','amount','date','note'],
    InvLedger: ['id','leaseId','propertyId','type','amount','periodStart','periodEnd','date','invoiceNum','status','note']
  };
  TABS.forEach(t => {
    let sheet = ss.getSheetByName(t);
    if (!sheet) {
      sheet = ss.insertSheet(t);
      sheet.getRange(1, 1, 1, headers[t].length).setValues([headers[t]]);
      sheet.getRange(1, 1, 1, headers[t].length).setFontWeight('bold').setBackground('#12111f').setFontColor('#78d649');
    }
  });
  return resp({ok: true, message: 'Sheets initialized'});
}

function readTab(ss, tab) {
  const sheet = ss.getSheetByName(tab);
  if (!sheet) return resp({error: 'Tab not found: ' + tab});
  const data = sheet.getDataRange().getValues();
  if (data.length < 2) return resp({rows: []});
  const headers = data[0];
  const rows = data.slice(1).map(row => {
    const obj = {};
    headers.forEach((h, i) => obj[h] = row[i]);
    return obj;
  }).filter(r => r.id);
  return resp({rows});
}

function writeRow(ss, tab, data) {
  const sheet = ss.getSheetByName(tab);
  const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  const row = headers.map(h => data[h] !== undefined ? data[h] : '');
  sheet.appendRow(row);
  return resp({ok: true});
}

function updateRow(ss, tab, id, data) {
  const sheet = ss.getSheetByName(tab);
  const allData = sheet.getDataRange().getValues();
  const headers = allData[0];
  const idIdx = headers.indexOf('id');
  for (let i = 1; i < allData.length; i++) {
    if (String(allData[i][idIdx]) === String(id)) {
      const row = headers.map((h, j) => data[h] !== undefined ? data[h] : allData[i][j]);
      sheet.getRange(i + 1, 1, 1, row.length).setValues([row]);
      return resp({ok: true});
    }
  }
  return resp({error: 'Row not found'});
}

function deleteRow(ss, tab, id) {
  const sheet = ss.getSheetByName(tab);
  const allData = sheet.getDataRange().getValues();
  const idIdx = allData[0].indexOf('id');
  for (let i = 1; i < allData.length; i++) {
    if (String(allData[i][idIdx]) === String(id)) {
      sheet.deleteRow(i + 1);
      return resp({ok: true});
    }
  }
  return resp({error: 'Row not found'});
}

function resp(data) {
  return ContentService.createTextOutput(JSON.stringify(data)).setMimeType(ContentService.MimeType.JSON);
}