dashboard.gs

// ==========================================
// WEB APP LOADER
// ==========================================
function db_loadDashboard() {
  return HtmlService.createTemplateFromFile('Index')
      .evaluate()
      .setTitle('Financial Dashboard')
      .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL)
      .addMetaTag('viewport', 'width=device-width, initial-scale=1, maximum-scale=1, user-scalable=0');
}

// ==========================================
// MAIN DASHBOARD DATA AGGREGATOR
// ==========================================
function db_getFinancialData() {
  try {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sheetRet = ss.getSheetByName(CONFIG.sheet_Retirement);
    const sheetInv = ss.getSheetByName(CONFIG.sheet_Investment);
    const sheetSpends = ss.getSheetByName(CONFIG.sheet_Spends);
    const sheetAssets = ss.getSheetByName(CONFIG.sheet_Assets);
    const sheetGoals = ss.getSheetByName(CONFIG.sheet_Goals);
    const sheetBudget = ss.getSheetByName(CONFIG.sheet_Budget);

    // 1. Budget Targets (Single Read Cols D:J)
    let budgetTotal = 0, sgdExpenses = 0, phpExpenses = 0;
    const lastBudRow = sheetBudget.getLastRow();
    
    if (lastBudRow >= 4) {
      const range = sheetBudget.getRange(4, 4, lastBudRow - 3, 7); 
      const values = range.getValues();
      const colors = range.getFontColors();

      for (let i = 0; i < values.length; i++) {
        // SGD Columns (D,E,F -> indices 0,1,2)
        const catSGD = String(values[i][0]);
        const colorSGD = colors[i][0];
        const valSGD = Number(values[i][2]) || 0;
        
        if (colorSGD !== '#000000' && colorSGD !== '#000') budgetTotal += valSGD;
        if (catSGD !== "") sgdExpenses += valSGD;

        // PHP Columns (H,I,J -> indices 4,5,6)
        const catPHP = String(values[i][4]);
        const valPHP = Number(values[i][6]) || 0;
        if (catPHP !== "") phpExpenses += valPHP;
      }
    }

    // 2. Spending Metrics
    let spentCurMonth = 0, spentLastYearMonth = 0, spentYTD = 0, spentLastYTD = 0, spent2YearsAgo = 0;
    const lastSpendRow = sheetSpends.getLastRow();
    const now = new Date();
    const curM = now.getMonth(), curY = now.getFullYear();
    
    if (lastSpendRow >= 2) {
      const values = sheetSpends.getRange(2, 1, lastSpendRow - 1, 8).getValues();
      for (let r of values) {
        const d = r[0], amt = Number(r[6]) || 0, type = String(r[7]);
        if (d instanceof Date && type !== "Payment") {
          const rM = d.getMonth(), rY = d.getFullYear();
          if (rY === curY) {
            spentYTD += amt;
            if (rM === curM) spentCurMonth += amt;
          } else if (rY === curY - 1) {
            spentLastYTD += amt;
            if (rM === curM) spentLastYearMonth += amt;
          } else if (rY === curY - 2) {
            spent2YearsAgo += amt;
          }
        }
      }
    }

    // 3. Assets & Exchange Rate (Single Read A1:I)
    let emgFundSGD = 0, emgFundPHP = 0, savCurrent = 0, rate = 1;
    const lastAssetRow = sheetAssets.getLastRow();
    
    if (lastAssetRow >= 1) {
      const allAssets = sheetAssets.getRange(1, 1, lastAssetRow, 9).getValues();
      rate = Number(allAssets[0][8]) || 1; // Cell I1

      for (let i = 1; i < allAssets.length; i++) { // Skip header
        const cat = String(allAssets[i][2]).trim();
        const curr = String(allAssets[i][4]).trim();
        const val = Number(allAssets[i][6]) || 0;

        if (cat === "Emergency Fund") {
          curr === "SGD" ? emgFundSGD += val : emgFundPHP += val;
        } else if (cat === "Bank Savings") {
          savCurrent += val;
        }
      }
    }

    // 4. Calculations
    const retTarget = sheetRet.getRange("C11").getValue();
    const retCurrent = sheetRet.getRange("D14").getValue();
    const totalExpPHP = (sgdExpenses * rate) + phpExpenses;
    const emgTarget = (totalExpPHP * 5) + 1000000;

    // 5. Goal Segments
    let savTarget = 0, goals = [];
    const lastGoalRow = sheetGoals.getLastRow();
    if (lastGoalRow >= 2) {
      const gVals = sheetGoals.getRange(2, 2, lastGoalRow - 1, 3).getValues();
      gVals.forEach(r => {
        if (r[1] && r[2] instanceof Date) {
          savTarget += (Number(r[1]) || 0);
          goals.push({ name: String(r[0]), cost: Number(r[1]), date: r[2] });
        }
      });
    }
    goals.sort((a, b) => a.date - b.date);

    let chartData = [], chartColors = [], runningCash = savCurrent;
    let nextGoalText = "All Secured", nextGoalPct = 100, foundActive = false;

    if (goals.length === 0) {
      chartData = [1]; chartColors = ['#e2e8f0']; nextGoalText = "No Goals"; nextGoalPct = 0;
    } else {
      goals.forEach(g => {
        if (runningCash >= g.cost) {
          chartData.push(1); chartColors.push('#10b981');
          runningCash -= g.cost;
        } else if (runningCash > 0) {
          const pct = runningCash / g.cost;
          chartData.push(pct, 1 - pct);
          chartColors.push('#3b82f6', '#dbeafe');
          if (!foundActive) { nextGoalText = g.name; nextGoalPct = Math.round(pct * 100); foundActive = true; }
          runningCash = 0;
        } else {
          chartData.push(1); chartColors.push('#e2e8f0');
          if (!foundActive) { nextGoalText = g.name; nextGoalPct = 0; foundActive = true; }
        }
      });
    }

    // 6. Investments
    let invTotalMkt = 0, invTotalProfit = 0, invCapital = 0;
    const invData = sheetInv.getRange("A9:P19").getValues();
    invData.forEach(r => {
      if (r[4] === "Day Trading") {
        const mkt = Number(r[12]) || 0;
        const prof = Number(r[15]) || 0;
        invTotalMkt += mkt;
        invTotalProfit += prof;
        invCapital += (mkt - prof);
      }
    });

    return {
      success: true,
      budget: { 
        current: budgetTotal - spentCurMonth, target: budgetTotal, spentCur: spentCurMonth,
        spentLast: spentLastYearMonth, ytdCur: spentYTD, ytdLast: spentLastYTD, ytdLast2: spent2YearsAgo
      },
      savings: { 
        current: savCurrent, target: savTarget,
        segments: chartData, colors: chartColors, activeName: nextGoalText, activePct: nextGoalPct
      }, 
      retirement: { current: retCurrent, target: retTarget },
      emergency:  { current: emgFundSGD + emgFundPHP, target: emgTarget },
      investment: { current: invTotalMkt, profit: invTotalProfit, capital: invCapital }
    };
  } catch (e) {
    return { success: false, error: e.message };
  }
}

// ==========================================
// NET WORTH: GROUPED LIST
// ==========================================
function db_getNetWorthDetails() {
  const sheet = _getSheet(CONFIG.sheet_Assets);
  const lastRow = sheet.getLastRow();
  if (lastRow < 2) return { total: 0, items: [] };

  const values = sheet.getRange(2, 1, lastRow - 1, 7).getValues();
  let groups = {}, totalNetWorth = 0;
  
  values.forEach(row => {
    const classVal = String(row[1]).trim();
    const categoryVal = String(row[2]).trim();
    const rawAmount = Number(row[6]) || 0;
    
    if (categoryVal || classVal) {
      const key = categoryVal + "|" + classVal;
      if (!groups[key]) groups[key] = { account: categoryVal, category: classVal, amount: 0 };
      groups[key].amount += rawAmount;
    }
  });

  const list = [];
  const liabilityRegex = /liabilities|liability|debt|loan|credit|mortgage/i;

  for (let key in groups) {
    const item = groups[key];
    if (Math.abs(item.amount) < 0.01) continue;
    const isLiability = liabilityRegex.test(item.category);
    totalNetWorth += isLiability ? -Math.abs(item.amount) : item.amount;
    list.push({ account: item.account, category: item.category, amount: item.amount, isDebt: isLiability });
  }

  list.sort((a, b) => {
    if (a.isDebt !== b.isDebt) return a.isDebt ? 1 : -1;
    return a.account.localeCompare(b.account);
  });

  return { total: totalNetWorth, items: list };
}

// ==========================================
// NET WORTH: DRILL DOWN & EDITS
// ==========================================
function db_getNetWorthItemDetails(filterAccount, filterCategory) {
  const sheet = _getSheet(CONFIG.sheet_Assets);
  const values = sheet.getRange(2, 1, sheet.getLastRow() - 1, 7).getValues();
  let details = [];
  
  values.forEach((row, index) => {
    const [ , rowClass, rowCategory, rowAccount, currency, , colG ] = row; // Destructure
    
    if (String(rowCategory).trim() === filterAccount && String(rowClass).trim() === filterCategory) {
       details.push({
         row: index + 2,
         classVal: rowClass, categoryVal: rowCategory, accountVal: rowAccount,
         currency: currency,
         amount: Number(colG) || 0,
         editVal: (currency === "SGD") ? (Number(row[5]) || 0) : (Number(colG) || 0)
       });
    }
  });
  return details;
}

function db_saveNetWorthItem(form) {
  const sheet = _getSheet(CONFIG.sheet_Assets);
  const col = (form.currency === "SGD") ? 6 : 7;
  sheet.getRange(Number(form.row), col).setValue(Number(form.amount));
  return true;
}

function db_addNewNetWorthItem(form) {
  const sheet = _getSheet(CONFIG.sheet_Assets);
  const nextRow = sheet.getLastRow() + 1;
  const { classVal, categoryVal, account, currency, amount } = form;
  
  let valF, valG;
  if (currency === "SGD") {
    valF = Number(amount);
    valG = `=F${nextRow}*GET_DBS_PHP_RATE()`;
  } else {
    valG = Number(amount);
    valF = `=G${nextRow}/GET_DBS_PHP_RATE()`;
  }
  
  sheet.appendRow([new Date(), classVal, categoryVal, account, currency, valF, valG]);
  return true;
}

function db_deleteNetWorthItem(row) {
  if (row > 1) {
    _getSheet(CONFIG.sheet_Assets).deleteRow(row);
    return true;
  }
  return false;
}

// ==========================================
// BUDGET: EDIT ALLOCATIONS
// ==========================================
function db_getBudgetBreakdown() {
  const sheet = _getSheet(CONFIG.sheet_Budget);
  const lastRow = sheet.getLastRow();
  if (lastRow < 4) return {}; 

  const range = sheet.getRange(4, 4, lastRow - 3, 3);
  const values = range.getValues();
  const colors = range.getFontColors();
  let groups = {}, order = [];

  for (let i = 0; i < values.length; i++) {
    const cat = String(values[i][0]).trim();
    const color = colors[i][0];
    
    if (cat !== "" && color !== '#000000' && color !== '#000') {
      if (!groups[cat]) {
        groups[cat] = [];
        order.push(cat);
      }
      groups[cat].push({ row: i + 4, label: values[i][1] || "General", amount: Number(values[i][2]) });
    }
  }
  return { order: order, items: groups };
}

function db_saveBudgetBreakdown(formValues) {
  const sheet = _getSheet(CONFIG.sheet_Budget);
  for (let key in formValues) {
    if (key.startsWith("val-")) {
      const row = Number(key.split("-")[1]);
      const amount = Number(formValues[key]);
      if (row >= 4 && !isNaN(amount)) sheet.getRange(row, 6).setValue(amount);
    }
  }
  return true;
}

// ==========================================
// RETIREMENT PLANNING
// ==========================================
function db_getRetirementAssets() {
  const sheetInv = _getSheet(CONFIG.sheet_Investment);
  const sheetRet = _getSheet(CONFIG.sheet_Retirement);
  
  const plan = {
    expenseNow:   Number(sheetRet.getRange("E12").getValue()) || 0,
    expenseFuture: Number(sheetRet.getRange("C10").getValue()) || 0,
    yearRetire:   Number(sheetRet.getRange("C5").getValue()) || 0,
    ageRetire:    Number(sheetRet.getRange("C7").getValue()) || 0,
    ageUntil:     Number(sheetRet.getRange("C8").getValue()) || 0,
    inflation:    Number(sheetRet.getRange("C9").getValue()) || 0,
    corpusTarget: Number(sheetRet.getRange("C11").getValue()) || 0,
    roi:          Number(sheetRet.getRange("C12").getValue()) || 0,
    statusVal:    sheetRet.getRange("B14").getValue(), 
    remark:       sheetRet.getRange("B16").getValue()
  };

  const dataRange = sheetInv.getRange("A9:Q19").getValues();
  let assets = [];
  dataRange.forEach((row, index) => {
    if (row[4] === "Retirement") {
      assets.push({ 
        row: 9 + index, 
        data: [
          { label: "Asset", value: row[1], type: "LABEL" }, 
          { label: "Avg Price", value: row[7], type: "AVG_PRICE" },
          { label: "Cur Price", value: row[8], type: "CUR_PRICE" },
          { label: "Units", value: row[9], type: "UNIT" },
          { label: "Market Value", value: row[12], type: "MKT_VAL" }, 
          { label: "Profit", value: row[15], type: "PROFIT" },
          { label: "Percent", value: row[13], type: "PERCENT" },
          { label: "Description", value: row[16], type: "DESC" }
        ]
      });
    }
  });

  return { plan: plan, assets: assets };
}

function db_saveRetirementPlan(form) {
  const sheet = _getSheet(CONFIG.sheet_Retirement);
  sheet.getRange("C5").setValue(Number(form.yearRetire));
  sheet.getRange("C8").setValue(Number(form.ageUntil));
  sheet.getRange("C9").setValue(Number(form.inflation));
  sheet.getRange("C12").setValue(Number(form.roi));
  return true;
}

function db_saveRetirementAsset(formData) {
  const sheet = _getSheet(CONFIG.sheet_Investment);
  const row = Number(formData.row);
  if (row < 9 || row > 19) return false;
  sheet.getRange(row, 8).setValue(Number(formData.avgPrice));
  sheet.getRange(row, 10).setValue(Number(formData.units));
  return true;
}

// ==========================================
// BUDGET: LIST & TRANSACTIONS
// ==========================================
function db_getBudgetDetails() {
  const sheetSpends = _getSheet(CONFIG.sheet_Spends);
  const sheetBudget = _getSheet(CONFIG.sheet_Budget); 

  // 1. Get Limits (Colored Rows)
  let limitMap = {};
  const lastBudRow = sheetBudget.getLastRow();
  if (lastBudRow >= 4) {
    const range = sheetBudget.getRange(4, 4, lastBudRow - 3, 3);
    const values = range.getValues();
    const colors = range.getFontColors();
    
    for (let i = 0; i < values.length; i++) {
      const cat = String(values[i][0]).trim();
      const color = colors[i][0];
      if (cat !== "" && color !== '#000000' && color !== '#000') {
        limitMap[cat] = (limitMap[cat] || 0) + (Number(values[i][2]) || 0);
      }
    }
  }

  // 2. Get Actuals (Current Month)
  let categories = {};
  const lastRow = sheetSpends.getLastRow();
  const now = new Date();
  const curM = now.getMonth(), curY = now.getFullYear();

  if (lastRow >= 2) {
    const values = sheetSpends.getRange(2, 1, lastRow - 1, 8).getValues();
    for (let r of values) {
      if (r[0] instanceof Date && r[0].getMonth() === curM && r[0].getFullYear() === curY && r[7] !== "Payment") {
        const type = String(r[7]).trim();
        if (type) {
            categories[type] = categories[type] || { spent: 0 };
            categories[type].spent += (Number(r[6]) || 0);
        }
      }
    }
  }

  // 3. Merge
  let details = [];
  let processed = {};
  
  for (let key in limitMap) {
    const spent = categories[key] ? categories[key].spent : 0;
    if (categories[key]) processed[key] = true;
    details.push({ category: key, spent: spent, left: limitMap[key] - spent });
  }

  for (let cat in categories) {
    if (!processed[cat] && !limitMap.hasOwnProperty(cat)) {
      details.push({ category: cat, spent: categories[cat].spent, left: -categories[cat].spent });
    }
  }

  return details.sort((a, b) => b.spent - a.spent);
}

function db_getTransactionsByCategory(targetCategory) {
  const sheet = _getSheet(CONFIG.sheet_Spends);
  const lastRow = sheet.getLastRow();
  if (lastRow < 2) return { list: [], total: 0 }; 
  
  const values = sheet.getRange(2, 1, lastRow - 1, 8).getValues();
  const backgrounds = sheet.getRange(2, 1, lastRow - 1, 8).getBackgrounds();
  const targetParts = String(targetCategory).split(" / ").map(s => s.trim());
  let transactions = [], total = 0;

  values.forEach((r, i) => {
    if (backgrounds[i][0] === "#ffffff") return; // Skip white/unhighlighted
    const cat = String(r[7]).trim();
    
    if (targetParts.indexOf(cat) > -1) {
      const amt = Number(r[6]) || 0;
      if (Math.abs(amt) > 0.01) {
        const dateStr = (r[0] instanceof Date) ? Utilities.formatDate(r[0], CONFIG.timezone, "dd MMM yyyy") : String(r[0]);
        const desc = String(r[2]).split("Ref No")[0].replace(/singapore sg/i, "").trim();
        transactions.push({ date: dateStr, desc: desc, amount: amt });
        total += amt;
      }
    }
  });
  
  return { list: transactions, total: total };
}

function db_saveNewTransaction(form) {
  const sheet = _getSheet(CONFIG.sheet_Spends);
  sheet.appendRow([new Date(form.date), "PENDING", form.description, "", "", "SGD", Number(form.amount), form.category]);
  const cell = sheet.getRange("A" + sheet.getLastRow());
  cell.setBackground("#fff2cc").setNumberFormat("dd MMM yyyy");
  return true;
}

// ==========================================
// INVESTMENT: STOCKS LIST
// ==========================================
function db_getInvestmentDetails() {
  const sheetInv = _getSheet(CONFIG.sheet_Investment);
  const dataRange = sheetInv.getRange("A9:Q19").getValues();
  let assets = [];
  
  dataRange.forEach((row, index) => {
    if (row[4] === "Day Trading") { 
      assets.push({ 
        row: 9 + index, 
        data: [
          { label: "Asset", value: row[1], type: "LABEL" }, 
          { label: "Avg Price", value: row[7], type: "AVG_PRICE" },
          { label: "Cur Price", value: row[8], type: "CUR_PRICE" },
          { label: "Units", value: row[9], type: "UNIT" },
          { label: "Market Value", value: row[12], type: "MKT_VAL" }, 
          { label: "Profit", value: row[15], type: "PROFIT" },
          { label: "Percent", value: row[13], type: "PERCENT" },
          { label: "Description", value: row[16], type: "DESC" }
        ]
      });
    }
  });
  const totalMkt = assets.reduce((acc, curr) => acc + (Number(curr.data.find(d=>d.type=='MKT_VAL').value)||0), 0);
  return { total: totalMkt, assets: assets };
}

function db_addStock(form) {
  const sheet = _getSheet(CONFIG.sheet_Investment);
  let targetRow = -1;
  // Scan for empty row in range 9-24
  for (let r = 9; r <= 24; r++) {
    if (!sheet.getRange(r, 2).getValue()) { targetRow = r; break; }
  }
  if (targetRow === -1) throw new Error("Portfolio is full (Max 16 stocks).");

  sheet.getRange(targetRow, 2).setValue(form.ticker);
  sheet.getRange(targetRow, 3).setValue(form.colC);
  sheet.getRange(targetRow, 4).setValue(form.colD);
  sheet.getRange(targetRow, 5).setValue(form.type);
  sheet.getRange(targetRow, 6).setValue(form.colF);
  sheet.getRange(targetRow, 7).setValue(form.colG);
  sheet.getRange(targetRow, 8).setValue(Number(form.avgPrice));
  sheet.getRange(targetRow, 10).setValue(Number(form.units));
  if(form.desc) sheet.getRange(targetRow, 17).setValue(form.desc);

  // Copy Formulas from Row 9
  if (targetRow > 9) {
    [9, 11, 12, 13, 14, 15, 16].forEach(c => {
      sheet.getRange(9, c).copyTo(sheet.getRange(targetRow, c), SpreadsheetApp.CopyPasteType.PASTE_FORMULA);
      sheet.getRange(9, c).copyTo(sheet.getRange(targetRow, c), SpreadsheetApp.CopyPasteType.PASTE_FORMAT);
    });
  }
  return true;
}

function db_deleteInvestment(row) {
  if (row < 9 || row > 24) return false;
  _getSheet(CONFIG.sheet_Investment).getRange(row, 2, 1, 16).clearContent();
  return true;
}

// ==========================================
// ANALYSIS: TRENDS & EMERGENCY
// ==========================================
function db_getYearlyTrends() {
  const sheet = _getSheet(CONFIG.sheet_Spends);
  const curY = new Date().getFullYear();
  let dCur = Array(12).fill(0), dLast = Array(12).fill(0), dPrev = Array(12).fill(0);
  
  if (sheet.getLastRow() >= 2) {
    const values = sheet.getRange(2, 1, sheet.getLastRow() - 1, 8).getValues();
    for (let r of values) {
      if (r[0] instanceof Date && r[7] !== "Payment") {
        const y = r[0].getFullYear(), m = r[0].getMonth(), amt = Number(r[6]) || 0;
        if (y === curY) dCur[m] += amt;
        else if (y === curY - 1) dLast[m] += amt;
        else if (y === curY - 2) dPrev[m] += amt;
      }
    }
  }
  return { years: [curY, curY - 1, curY - 2], datasets: [dCur, dLast, dPrev] };
}

function db_getExpenseBreakdown() {
  const sheetBudget = _getSheet(CONFIG.sheet_Budget);
  const rate = Number(_getSheet(CONFIG.sheet_Assets).getRange("I1").getValue()) || 1;
  const lastRow = sheetBudget.getLastRow();
  let sgList = [], phList = [], sgTotal = 0, phTotal = 0;

  if (lastRow >= 4) {
    // Read Cols D:J
    const values = sheetBudget.getRange(4, 4, lastRow - 3, 7).getValues();
    let sgMap = {}, phMap = {};

    values.forEach(r => {
      const catS = String(r[0]).trim(), amtS = Number(r[2]) || 0;
      if(catS) { sgMap[catS] = (sgMap[catS] || 0) + amtS; }

      const catP = String(r[4]).trim(), amtP = Number(r[6]) || 0;
      if(catP) { phMap[catP] = (phMap[catP] || 0) + amtP; }
    });

    for(let k in sgMap) { sgList.push({category: k, amount: sgMap[k]}); sgTotal += sgMap[k]; }
    for(let k in phMap) { phList.push({category: k, amount: phMap[k]}); phTotal += phMap[k]; }
  }

  return { 
    sg: { items: sgList.sort((a,b)=>b.amount-a.amount), total: sgTotal },
    ph: { items: phList.sort((a,b)=>b.amount-a.amount), total: phTotal },
    rate: rate 
  };
}

function db_getEmergencyDetails() {
  // Reuse logic for consistency
  const exp = db_getExpenseBreakdown();
  const sheetAssets = _getSheet(CONFIG.sheet_Assets);
  
  return {
    expensePHP: (exp.sg.total * exp.rate) + exp.ph.total,
    fundSGD_PHP: Number(sheetAssets.getRange("G5").getValue()) || 0,
    fundPHP_Actual: Number(sheetAssets.getRange("G6").getValue()) || 0,
    fundPHP_Target: 1000000 
  };
}

// ==========================================
// SAVINGS GOALS ANALYSIS
// ==========================================
function db_getSavingsGoals() {
  const sheetAssets = _getSheet(CONFIG.sheet_Assets);
  const sheetGoals = _getSheet(CONFIG.sheet_Goals);
  
  // 1. Get Totals
  const rate = sheetAssets.getRange("I1").getValue() || 1;
  const monthlySavingsPHP = 1500 * rate; 
  let currentSavings = 0;

  const assets = sheetAssets.getRange(2, 1, sheetAssets.getLastRow() - 1, 7).getValues();
  assets.forEach(r => { if(String(r[2]).trim() === "Bank Savings") currentSavings += (Number(r[6]) || 0); });

  // 2. Get Goals
  const gVals = sheetGoals.getRange(2, 2, sheetGoals.getLastRow() - 1, 3).getValues();
  let goals = [];
  gVals.forEach(r => {
    if(r[0] && r[1] && r[2] instanceof Date) goals.push({ name: String(r[0]), cost: Number(r[1]), date: r[2] });
  });
  goals.sort((a, b) => a.date - b.date);

  // 3. Waterfall
  let running = currentSavings, cumCost = 0;
  const processed = goals.map(g => {
    let allocated = 0;
    if (running >= g.cost) { allocated = g.cost; running -= g.cost; }
    else if (running > 0) { allocated = running; running = 0; }

    cumCost += g.cost;
    let months = (g.date - new Date()) / (1000 * 60 * 60 * 24 * 30.44);
    if (months < 0.1) months = 0.1;

    const projected = currentSavings + (months * monthlySavingsPHP);
    const deficit = cumCost - projected;
    const isOnTrack = (projected >= cumCost);

    return {
      name: g.name, cost: g.cost,
      date: Utilities.formatDate(g.date, CONFIG.timezone, "dd MMM yyyy"),
      allocated: allocated, pctFunded: (g.cost > 0) ? (allocated / g.cost) * 100 : 0,
      status: (allocated >= g.cost) ? "Secured" : (allocated > 0 ? "Partial" : "Pending"),
      onTrack: isOnTrack, remaining: g.cost - allocated,
      fixLumpSum: isOnTrack ? 0 : deficit,
      fixMonthly: isOnTrack ? 0 : (deficit / months)
    };
  });

  return { totalSavings: currentSavings, monthlyRatePHP: monthlySavingsPHP, goals: processed };
}

function db_addSavingsGoal(form) {
  _getSheet(CONFIG.sheet_Goals).appendRow(["", form.name, Number(form.amount), new Date(form.date)]);
  return true;
}

function db_deleteSavingsGoal(goalName) {
  const sheet = _getSheet(CONFIG.sheet_Goals);
  const vals = sheet.getRange(2, 2, sheet.getLastRow() - 1, 1).getValues();
  for (let i = 0; i < vals.length; i++) {
    if (String(vals[i][0]) === goalName) { sheet.deleteRow(i + 2); return true; }
  }
  return false;
}

function _getSheet(name) { return SpreadsheetApp.getActiveSpreadsheet().getSheetByName(name); }