const SHEET_NAME = 'Transactions';
const LEARNED_SHEET = 'Learned';
const INCOME_SHEET = 'Income';
const SAVINGS_SHEET = 'Savings';
const CONTRIB_SHEET = 'Contributions';
const UPLOADS_SHEET = 'Uploads';
const RENTAL_SHEET = 'Rental';
const CATS_SHEET = 'Categories';

function doPost(e) {
  const lock = LockService.getScriptLock();
  lock.tryLock(10000);
  try {
    const data = JSON.parse(e.postData.contents);
    const ss = SpreadsheetApp.getActiveSpreadsheet();

    if (data.action === 'push') {
      writeSheet(ss, SHEET_NAME, ['id','date','merchant','amount','category','person','card','note','refund','manual','incomeType','savingsAccountId','incomeCategory','needs'],
        (data.txns||[]).map(t => [t.id,t.date,t.merchant,t.amount,t.category,t.person||'',t.card||'',t.note||'',t.refund?'true':'false',t.manual?'true':'false',t.incomeType||'',t.savingsAccountId||'',t.incomeCategory||'',t.needs===true?'true':t.needs===false?'false':'']));
      writeSheet(ss, LEARNED_SHEET, ['merchant','category'],
        Object.entries(data.learned||{}).map(([m,c]) => [m,c]));
      writeSheet(ss, INCOME_SHEET, ['id','date','amount','type','person','desc','note','fromTxnId','category'],
        (data.incomes||[]).map(i => [i.id,i.date,i.amount,i.type,i.person||'',i.desc||'',i.note||'',i.fromTxnId||'',i.category||'']));
      writeSheet(ss, SAVINGS_SHEET, ['id','name','type','person','balance','baseBalance','goal'],
        (data.savAccts||[]).map(a => [a.id,a.name,a.type,a.person||'',a.balance||0,a.baseBalance||0,a.goal||'']));
      writeSheet(ss, CONTRIB_SHEET, ['id','date','amount','accountId','type','person','note','fromTxnId'],
        (data.contribs||[]).map(c => [c.id,c.date,c.amount,c.accountId,c.type,c.person||'',c.note||'',c.fromTxnId||'']));
      writeSheet(ss, RENTAL_SHEET, ['id','date','amount','cat','person','card','desc','note'],
        (data.rentalExpenses||[]).map(r => [r.id,r.date,r.amount,r.cat,r.person||'',r.card||'',r.desc||'',r.note||'']));
      writeSheet(ss, CATS_SHEET, ['key','label','color','fixed','budget','keywords'],
        (data.cats||[]).map(c => [c.key,c.label,c.color,c.fixed?'true':'false',c.budget||0,JSON.stringify(c.kw||[])]));
      return buildResponse({ok:true, count:(data.txns||[]).length});
    }

    if (data.action === 'pull') {
      const txns     = readSheet(ss, SHEET_NAME,   r => ({id:r.id,date:r.date,merchant:r.merchant,amount:parseFloat(r.amount)||0,category:r.category,person:r.person,card:r.card||'',note:r.note,refund:r.refund==='true',manual:r.manual==='true',incomeType:r.incomeType||undefined,savingsAccountId:r.savingsAccountId||undefined,incomeCategory:r.incomeCategory||undefined,needs:r.needs==='true'?true:r.needs==='false'?false:undefined}));
      const learned  = {}; readSheet(ss, LEARNED_SHEET, r => r).forEach(r => { if(r.merchant) learned[r.merchant]=r.category; });
      const incomes  = readSheet(ss, INCOME_SHEET,  r => ({id:r.id,date:r.date,amount:parseFloat(r.amount)||0,type:r.type,person:r.person,desc:r.desc,note:r.note,fromTxnId:r.fromTxnId||undefined,category:r.category||undefined}));
      const savAccts = readSheet(ss, SAVINGS_SHEET, r => ({id:r.id,name:r.name,type:r.type,person:r.person,balance:parseFloat(r.balance)||0,baseBalance:parseFloat(r.baseBalance)||0,goal:r.goal?parseFloat(r.goal):null}));
      const contribs = readSheet(ss, CONTRIB_SHEET, r => ({id:r.id,date:r.date,amount:parseFloat(r.amount)||0,accountId:r.accountId,type:r.type,person:r.person,note:r.note,fromTxnId:r.fromTxnId||undefined}));
      const rentalExpenses = readSheet(ss, RENTAL_SHEET, r => ({id:r.id,date:r.date,amount:parseFloat(r.amount)||0,cat:r.cat,person:r.person,card:r.card||'',desc:r.desc,note:r.note}));
      const catsRaw = readSheet(ss, CATS_SHEET, r => { try { return {key:r.key,label:r.label,color:r.color,fixed:r.fixed==='true',budget:parseFloat(r.budget)||0,kw:JSON.parse(r.keywords||'[]')}; } catch(e) { return {key:r.key,label:r.label,color:r.color,fixed:r.fixed==='true',budget:parseFloat(r.budget)||0,kw:[]}; } });
      return buildResponse({ok:true, txns, learned, incomes, savAccts, contribs, rentalExpenses, cats:catsRaw.length?catsRaw:null});
    }

    return buildResponse({ok:false, error:'Unknown action'});
  } catch(err) {
    return buildResponse({ok:false, error:err.toString()});
  } finally {
    lock.releaseLock();
  }
}

function writeSheet(ss, name, headers, rows) {
  let sheet = ss.getSheetByName(name);
  if (!sheet) sheet = ss.insertSheet(name);
  sheet.clearContents();
  const data = rows.length ? [headers, ...rows] : [headers];
  sheet.getRange(1, 1, data.length, headers.length).setValues(data);
}

function readSheet(ss, name, mapper) {
  const sheet = ss.getSheetByName(name);
  if (!sheet) return [];
  const rows = sheet.getDataRange().getValues();
  if (rows.length < 2) return [];
  const headers = rows[0];
  return rows.slice(1).map(r => {
    const obj = {};
    headers.forEach((h,i) => {
      const v = r[i];
      // Convert Google Sheets Date objects to YYYY-MM-DD strings
      if (v instanceof Date) {
        const y = v.getFullYear();
        const mo = String(v.getMonth()+1).padStart(2,'0');
        const d = String(v.getDate()).padStart(2,'0');
        obj[h] = y + '-' + mo + '-' + d;
      } else {
        obj[h] = v;
      }
    });
    return mapper(obj);
  }).filter(r => r.id || r.key);
}

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

function doGet(e) {
  return buildResponse({ok:true, status:'Ledger connected'});
}