expense-tracker

/**
 * ============================================================
 * AUTO EXPENSE TRACKER — Google Apps Script
 * ------------------------------------------------------------
 * Scans your Gmail every hour for payment/transaction emails
 * from senders you trust (your bank/card issuer), uses the
 * Gemini API to read them (works across different providers'
 * formats since it's not regex/template based), and logs each
 * expense into a monthly tab with a live category summary,
 * budget highlighting, and pie chart.
 *
 * SETUP: See Guide above. If you made a copy of the template, this is
 * already installed: just click Expense Tracker > Run Setup.
 * To install it by hand instead —
 *   1. Paste this whole code into Extensions > Apps Script.
 *   2. Run `setup` once and authorize it.
 *   3. Paste your free Gemini API key when the popup asks for it.
 *   That's it: setup also starts the hourly scan and runs the first one.
 * ============================================================
 */

// ====================== CONFIG ======================
const SETUP_SHEET_NAME    = 'Setup';
const PROCESSED_LOG_SHEET = 'ProcessedLog';

// The Gemini key lives in Script Properties (private to the script), not in
// the sheet, so sharing the sheet never shares the key. B3 only shows status.
const API_KEY_PROP    = 'GEMINI_API_KEY';
const KEY_STATUS_CELL = 'B3';
const KEY_SAVED_TEXT   = '✓ Saved';
const KEY_MISSING_TEXT = 'Not set';
const MODEL_CELL      = 'B5';
const KEYWORDS_CELL   = 'B7';
const SENDERS_CELL    = 'B9';
const WARNING_CELL    = 'D3';
const LAST_SCAN_TIME_CELL   = 'B64';
const LAST_SCAN_RESULT_CELL = 'B65';

const CATEGORY_START_ROW = 13;
const CATEGORY_END_ROW   = 27; // up to 15 categories
const TOTAL_BUDGET_ROW   = CATEGORY_END_ROW + 2; // Setup row showing the auto-summed total budget
const BUDGET_COL         = 2;  // column B, beside the category list

const TIMEZONE = Session.getScriptTimeZone();

const DEFAULT_MODEL = 'gemini-flash-lite-latest'; // Google-maintained alias, good free-tier fit
const DEFAULT_KEYWORDS =
  'payment,transaction,receipt,purchase,charged,debited,debit,you spent,payment confirmation,' +
  'order confirmation,paid,invoice,withdrawal,bill payment,card statement';
const DEFAULT_SENDERS =
  'paylah.alert@dbs.com,ibanking.alert@dbs.com,unialerts@uobgroup.com,Notifications@ocbc.com,' +
  'noreply@notify.ocbc.com,HSBC.Bank.Singapore.Limited@notification.hsbc.com.hk,alerts@citibank.com.sg,' +
  'from_us@trustbank.sg,onlinebanking.sg@sc.com,notifications@maribank.sg,onlinebanking.alerts@maybank.com.sg';
const DEFAULT_CATEGORIES = [
  'Food & Dining', 'Groceries', 'Transportation', 'Shopping', 'Bills & Utilities',
  'Entertainment', 'Health & Fitness', 'Subscriptions', 'Donations & Gifts', 'Others'
];

// Maps a sender's email/domain to a friendly bank name for the Source
// column. Matched by substring so subdomains (e.g. notification.hsbc.com.hk)
// still resolve correctly. Senders not on this list (e.g. one you add later
// yourself) still get a readable label — see getBankLabel() — rather than
// the raw address.
const BANK_NAME_MAP = [
  { match: 'dbs.com', name: 'DBS' },
  { match: 'uobgroup.com', name: 'UOB' },
  { match: 'ocbc.com', name: 'OCBC' },
  { match: 'hsbc', name: 'HSBC' },
  { match: 'citibank', name: 'Citibank' },
  { match: 'trustbank.sg', name: 'Trust Bank' },
  { match: 'sc.com', name: 'Standard Chartered' },
  { match: 'maribank.sg', name: 'MariBank' },
  { match: 'maybank.com.sg', name: 'Maybank' }
];

const GMAIL_LABEL  = 'Expense-Tracked';
const SCAN_WINDOW  = 'newer_than:2d'; // wide buffer so a missed hour still gets caught
const MAX_THREADS_PER_RUN = 100;

// ====================== MENU ======================
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Expense Tracker')
    .addItem('Run Setup', 'setup')
    .addItem('Update Gemini API Key', 'updateApiKey')
    .addItem('Create This Month\'s Tab', 'createCurrentMonthSheetMenu')
    .addSeparator()
    .addItem('Start Hourly Auto-Scan', 'startAutoScan')
    .addItem('Stop Auto-Scan', 'stopAutoScan')
    .addItem('Scan Now', 'scanEmailsMenu')
    .addToUi();
}

// ====================== SETUP ======================
// The one entry point for first-time setup. Safe to re-run: it never
// overwrites your categories, budgets, senders, or an API key already saved.
function setup() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = buildSetupSheet(ss);
  onOpen(); // show the menu now, no page refresh needed

  if (!getApiKey(sheet)) {
    const key = promptForApiKey();
    if (key) saveApiKey(sheet, key);
  }

  if (!getApiKey(sheet)) {
    SpreadsheetApp.getUi().alert(
      'The Setup tab is ready, but there is no API key yet.\n\n' +
      'Get a free key at aistudio.google.com/apikey, then click ' +
      'Expense Tracker > Update Gemini API Key to finish.'
    );
    return;
  }

  finishSetup(sheet);
}

function updateApiKey() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(SETUP_SHEET_NAME) || buildSetupSheet(ss);
  const key = promptForApiKey();
  if (!key) return;
  saveApiKey(sheet, key);
  finishSetup(sheet);
}

// Starts the hourly trigger and runs one scan straight away, so a bad key or
// a missing bank alert shows up now instead of silently an hour from now.
function finishSetup(sheet) {
  installScanTrigger();
  const added = scanEmails();
  const warning = sheet.getRange(WARNING_CELL).getValue().toString();

  let message = 'Hourly auto-scan is on.\n\nFirst scan: ' + added +
    ' expense(s) added from the last 2 days.';
  if (warning) {
    message += '\n\nHeads-up: ' + warning;
  }
  SpreadsheetApp.getUi().alert('Setup complete!\n\n' + message);
}

function promptForApiKey() {
  const ui = SpreadsheetApp.getUi();
  const response = ui.prompt(
    'Gemini API key',
    'Paste your free key from aistudio.google.com/apikey. It is stored privately ' +
    'in this script, not in the sheet.',
    ui.ButtonSet.OK_CANCEL
  );
  if (response.getSelectedButton() !== ui.Button.OK) return '';
  return response.getResponseText().trim();
}

function saveApiKey(setupSheet, key) {
  PropertiesService.getScriptProperties().setProperty(API_KEY_PROP, key);
  setupSheet.getRange(KEY_STATUS_CELL).setValue(KEY_SAVED_TEXT);
}

function getApiKey(setupSheet) {
  const saved = PropertiesService.getScriptProperties().getProperty(API_KEY_PROP);
  if (saved) return saved;

  // Older versions had you paste the key into B3. Move it into private
  // storage the first time it's seen, and leave only a status behind.
  const legacy = setupSheet.getRange(KEY_STATUS_CELL).getValue().toString().trim();
  if (legacy && legacy !== KEY_SAVED_TEXT && legacy !== KEY_MISSING_TEXT) {
    saveApiKey(setupSheet, legacy);
    return legacy;
  }
  return '';
}

function buildSetupSheet(ss) {
  let sheet = ss.getSheetByName(SETUP_SHEET_NAME);
  if (!sheet) {
    sheet = ss.insertSheet(SETUP_SHEET_NAME, 0);
  } else {
    ss.setActiveSheet(sheet);
  }

  sheet.getRange('A1:C1').merge().setValue('EXPENSE TRACKER — SETUP')
    .setFontWeight('bold').setFontSize(16)
    .setBackground('#4a86e8').setFontColor('#ffffff');

  sheet.getRange('A3').setValue('Gemini API Key:').setFontWeight('bold');
  sheet.getRange('C3').setValue(
    'Stored privately in this script, not in the sheet. To change it, click ' +
    'Expense Tracker > Update Gemini API Key.'
  );

  sheet.getRange('A5').setValue('Gemini Model (advanced):').setFontWeight('bold');
  if (!sheet.getRange(MODEL_CELL).getValue()) sheet.getRange(MODEL_CELL).setValue(DEFAULT_MODEL);
  sheet.getRange('C5').setValue('Leave as-is unless you know what this means. See ai.google.dev/gemini-api/docs/models');

  sheet.getRange('A7').setValue('Email Search Keywords:').setFontWeight('bold');
  if (!sheet.getRange(KEYWORDS_CELL).getValue()) sheet.getRange(KEYWORDS_CELL).setValue(DEFAULT_KEYWORDS);
  sheet.getRange('C7').setValue('Comma-separated. Narrows the search within your trusted senders below.');

  sheet.getRange('A9').setValue('Trusted Bank Senders:').setFontWeight('bold');
  if (!sheet.getRange(SENDERS_CELL).getValue()) sheet.getRange(SENDERS_CELL).setValue(DEFAULT_SENDERS);
  sheet.getRange('C9').setValue(
    'Comma-separated sender domains/emails, e.g. "dbs.com, notifications@ocbc.com". Open a past ' +
    'notification email from your bank/card issuer to find its address. Only emails from these ' +
    'senders are scanned — this is what keeps a merchant\'s own receipt email from being ' +
    'double-counted alongside your bank\'s notification of the same purchase.'
  );

  sheet.getRange('A11').setValue('Expense Categories').setFontWeight('bold').setFontSize(12);
  sheet.getRange('C11').setValue('Edit, delete, or add categories below (up to 15). Dropdowns update automatically.');
  sheet.getRange('A12').setValue('Category').setFontWeight('bold').setBackground('#eeeeee');
  sheet.getRange('B12').setValue('Budget ($)').setFontWeight('bold').setBackground('#eeeeee');
  sheet.getRange('C12').setValue(
    'Budget is optional — leave blank for no limit. When a category\'s spending this month ' +
    'exceeds its budget, that category turns red in the month tab\'s summary table.'
  );

  const existing = sheet.getRange(CATEGORY_START_ROW, 1, CATEGORY_END_ROW - CATEGORY_START_ROW + 1, 1)
    .getValues().flat().filter(String);
  if (existing.length === 0) {
    sheet.getRange(CATEGORY_START_ROW, 1, DEFAULT_CATEGORIES.length, 1)
      .setValues(DEFAULT_CATEGORIES.map(c => [c]));
  }

  sheet.getRange(CATEGORY_START_ROW, BUDGET_COL, CATEGORY_END_ROW - CATEGORY_START_ROW + 1, 1)
    .setNumberFormat('#,##0.00');

  sheet.getRange(`A${TOTAL_BUDGET_ROW}`).setValue('Total Monthly Budget ($):').setFontWeight('bold');
  sheet.getRange(`B${TOTAL_BUDGET_ROW}`).setFormula(
    `=IF(SUM(B${CATEGORY_START_ROW}:B${CATEGORY_END_ROW})=0,"",` +
    `SUM(B${CATEGORY_START_ROW}:B${CATEGORY_END_ROW}))`
  ).setFontWeight('bold').setNumberFormat('#,##0.00');
  sheet.getRange(`C${TOTAL_BUDGET_ROW}`).setValue(
    'Auto-calculated as the sum of the category budgets above — this can\'t be set ' +
    'independently, so your total and category budgets can never contradict each other. ' +
    'To track an overall limit without per-category detail, just put the whole amount in ' +
    'one category\'s budget.'
  );
  applyTotalBudgetProtection(sheet);

  sheet.getRange('A64').setValue('Last scan time:').setFontWeight('bold');
  sheet.getRange('A65').setValue('Last scan result:').setFontWeight('bold');

  sheet.setColumnWidth(1, 190);
  sheet.setColumnWidth(2, 260);
  sheet.setColumnWidth(3, 430);
  sheet.getRange('C:C').setWrap(true);
  sheet.setFrozenRows(1);

  // getApiKey() also moves a key an older version left in B3 into private storage.
  sheet.getRange(KEY_STATUS_CELL).setValue(getApiKey(sheet) ? KEY_SAVED_TEXT : KEY_MISSING_TEXT);

  getOrCreateProcessedLogSheet(ss);
  ensureCurrentMonthSheet(ss);
  return sheet;
}

// Applies warning-only protection to the auto-calculated Total Monthly
// Budget cell, so editing it directly shows a heads-up instead of silently
// overwriting the formula. Checks for an existing protection on this exact
// range first, so re-running Initialize never stacks duplicates.
function applyTotalBudgetProtection(sheet) {
  const range = sheet.getRange(`B${TOTAL_BUDGET_ROW}`);
  const already = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE)
    .some(p => p.getRange().getA1Notation() === range.getA1Notation());
  if (already) return;

  const protection = range.protect();
  protection.setWarningOnly(true);
  protection.setDescription('Auto-calculated — edits here will be overwritten by the sum of category budgets.');
}

function ensureCurrentMonthSheet(ss) {
  const name = Utilities.formatDate(new Date(), TIMEZONE, 'yyyy-MM');
  let sheet = ss.getSheetByName(name);

  if (sheet) {
    // Tab already exists — bring its columns up to date (see
    // migrateMonthSheetColumns), then rebuild the summary block, budget
    // highlighting, and chart. The expense data itself (columns A–G) is
    // never touched.
    migrateMonthSheetColumns(sheet);
    fixSummaryTable(sheet);
  } else {
    sheet = getOrCreateMonthSheet(ss, new Date());
  }

  return sheet;
}

function createCurrentMonthSheetMenu() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ensureCurrentMonthSheet(ss);
  ss.setActiveSheet(sheet);
  SpreadsheetApp.getUi().alert(`"${sheet.getName()}" tab is ready with the latest layout.`);
}

// ====================== MONTH TABS ======================
function getOrCreateMonthSheet(ss, date) {
  const name = Utilities.formatDate(date, TIMEZONE, 'yyyy-MM');
  let sheet = ss.getSheetByName(name);

  if (sheet) {
    // A sheet for this month may already exist with an older column layout
    // — make sure it's migrated before anything gets written into it, even
    // if it's not the current month (a late-arriving email can target an
    // older tab).
    migrateMonthSheetColumns(sheet);
    return sheet;
  }

  sheet = ss.insertSheet(name, 1);
  const headers = ['Date', 'Time', 'Amount ($)', 'Description', 'Category', 'Source', 'Message ID'];
  sheet.getRange(1, 1, 1, headers.length).setValues([headers])
    .setFontWeight('bold').setBackground('#4a86e8').setFontColor('#ffffff');
  sheet.setFrozenRows(1);

  sheet.setColumnWidth(1, 100);
  sheet.setColumnWidth(2, 70);
  sheet.setColumnWidth(3, 90);
  sheet.setColumnWidth(4, 260);
  sheet.setColumnWidth(5, 150);
  sheet.setColumnWidth(6, 160);
  sheet.hideColumns(7); // Message ID, used for de-duplication only

  sheet.getRange(2, 1, 998, 1).setNumberFormat('yyyy-mm-dd');
  sheet.getRange(2, 3, 998, 1).setNumberFormat('#,##0.00');

  // Category dropdown — sourced live from the Setup sheet range, so
  // editing categories there updates every month's dropdown automatically.
  const setupSheet = ss.getSheetByName(SETUP_SHEET_NAME);
  const categoryRule = SpreadsheetApp.newDataValidation()
    .requireValueInRange(setupSheet.getRange(`A${CATEGORY_START_ROW}:A${CATEGORY_END_ROW}`), true)
    .setAllowInvalid(false)
    .build();
  sheet.getRange(2, 5, 998, 1).setDataValidation(categoryRule);

  buildSummaryTable(sheet);
  return sheet;
}

// Ensures a month tab's columns match the current layout. Right now that
// just means the Message ID column stays hidden — the check against F1
// keeps this a safe no-op once a sheet is already correct. This is the
// extension point if a future version changes the column layout again:
// the goal is that "Create This Month's Tab" always brings an existing tab
// up to date without ever touching the expense data itself.
function migrateMonthSheetColumns(sheet) {
  if (sheet.getRange('F1').getValue() === 'Source') {
    sheet.hideColumns(7); // Message ID
  }
}

function buildSummaryTable(sheet) {
  sheet.getRange('J1').setValue('Summary').setFontWeight('bold').setFontSize(12);

  sheet.getRange('J2').setValue('Total ($)').setFontWeight('bold').setBackground('#eeeeee');
  sheet.getRange('K2').setFormula('=SUM(C2:C1000)')
    .setFontWeight('bold').setNumberFormat('#,##0.00');

  // Hidden helper mirroring the Setup total budget, for the same
  // cross-sheet-reference reason as the category budget mirror below.
  sheet.getRange('M2').setFormula(`=IF(Setup!B${TOTAL_BUDGET_ROW}="","",Setup!B${TOTAL_BUDGET_ROW})`);
  sheet.getRange('M2').setNumberFormat('#,##0.00');
  sheet.hideColumns(13); // M — helper only, mirrors the overall total budget

  applyTotalBudgetConditionalFormatting(sheet);

  // Row 3 is left blank as a spacer between the total and the category table.

  sheet.getRange('J4').setValue('Category').setFontWeight('bold').setBackground('#eeeeee');
  sheet.getRange('K4').setValue('Total ($)').setFontWeight('bold').setBackground('#eeeeee');

  const rangeSize = CATEGORY_END_ROW - CATEGORY_START_ROW + 1; // 15
  const firstCatRow = 5;
  const lastCatRow = firstCatRow + rangeSize - 1; // 19

  sheet.getRange(`J${firstCatRow}`).setFormula(
    `=ARRAYFORMULA(IF(Setup!A${CATEGORY_START_ROW}:A${CATEGORY_END_ROW}="","",` +
    `Setup!A${CATEGORY_START_ROW}:A${CATEGORY_END_ROW}))`
  );
  sheet.getRange(`K${firstCatRow}`).setFormula(
    `=ARRAYFORMULA(IF(J${firstCatRow}:J${lastCatRow}="","",` +
    `SUMIF($E$2:$E$1000,J${firstCatRow}:J${lastCatRow},$C$2:$C$1000)))`
  );
  sheet.getRange(`K${firstCatRow}:K${lastCatRow}`).setNumberFormat('#,##0.00');

  // Hidden helper column mirroring Setup budgets. Needed because Google
  // Sheets conditional format rules can't reference a different sheet
  // directly — a normal formula can, so we mirror the value in first,
  // then point the conditional format rule at this local column.
  sheet.getRange(`L${firstCatRow}`).setFormula(
    `=ARRAYFORMULA(IF(Setup!B${CATEGORY_START_ROW}:B${CATEGORY_END_ROW}="","",` +
    `Setup!B${CATEGORY_START_ROW}:B${CATEGORY_END_ROW}))`
  );
  sheet.getRange(`L${firstCatRow}:L${lastCatRow}`).setNumberFormat('#,##0.00');
  sheet.hideColumns(12); // L — helper only, not meant to be viewed/edited

  applyBudgetConditionalFormatting(sheet, firstCatRow, lastCatRow);

  sheet.setColumnWidth(10, 160);
  sheet.setColumnWidth(11, 100);

  buildCategoryChart(sheet, lastCatRow);
}

// Turns the overall Total ($) cell red when total spending this month
// exceeds the auto-calculated Total Monthly Budget from Setup (mirrored
// locally into M2 — see buildSummaryTable). There's no independently-set
// total budget to clash with the category budgets; this value is always
// their sum.
function applyTotalBudgetConditionalFormatting(sheet) {
  const range = sheet.getRange('J2:K2');

  const rule = SpreadsheetApp.newConditionalFormatRule()
    .whenFormulaSatisfied('=AND($K2<>"", $M2<>"", $K2>$M2)')
    .setBackground('#f4cccc')
    .setFontColor('#990000')
    .setRanges([range])
    .build();

  const otherRules = sheet.getConditionalFormatRules().filter(r =>
    !r.getRanges().some(rg => rg.getA1Notation() === range.getA1Notation())
  );
  otherRules.push(rule);
  sheet.setConditionalFormatRules(otherRules);
}

// Turns a category's row red in the summary table (both the Category and
// Total cells) when that category's spending this month exceeds the budget
// mirrored into column L (see buildSummaryTable — conditional format rules
// can't reference another sheet, so the budget value is copied in locally
// first via a plain formula, which has no such restriction). A blank budget
// means no limit. This is pure conditional formatting — Sheets evaluates it
// live, so no script logic is needed on every scan to "check" anything.
function applyBudgetConditionalFormatting(sheet, firstCatRow, lastCatRow) {
  const range = sheet.getRange(`J${firstCatRow}:K${lastCatRow}`);

  const rule = SpreadsheetApp.newConditionalFormatRule()
    .whenFormulaSatisfied(
      `=AND($K${firstCatRow}<>"", $L${firstCatRow}<>"", ` +
      `$K${firstCatRow}>$L${firstCatRow})`
    )
    .setBackground('#f4cccc')
    .setFontColor('#990000')
    .setRanges([range])
    .build();

  // Replace any existing rule already targeting this exact range, so
  // rebuilding the summary table never stacks duplicate rules.
  const otherRules = sheet.getConditionalFormatRules().filter(r =>
    !r.getRanges().some(rg => rg.getA1Notation() === range.getA1Notation())
  );
  otherRules.push(rule);
  sheet.setConditionalFormatRules(otherRules);
}

// Rebuilds the pie chart from the category summary table, anchored below
// the table rather than beside it. Removes any existing chart on the sheet
// first, so re-running this never stacks duplicate charts on top of each other.
function buildCategoryChart(sheet, lastCatRow) {
  sheet.getCharts().forEach(chart => sheet.removeChart(chart));

  const chartRow = lastCatRow + 2; // one blank row of buffer below the table
  const dataRange = sheet.getRange(`J4:K${lastCatRow}`); // header row + category rows
  const chart = sheet.newChart()
    .setChartType(Charts.ChartType.PIE)
    .addRange(dataRange)
    .setOption('title', 'Category Breakdown')
    .setOption('pieSliceText', 'percentage')
    .setOption('legend', { position: 'right' })
    .setOption('width', 480)
    .setOption('height', 320)
    .setPosition(chartRow, 10, 0, 0) // column J, below the category table
    .build();

  sheet.insertChart(chart);
}

// Rebuilds the J:K summary block, budget highlighting, and chart from
// scratch — used to upgrade a month tab to the current layout without
// touching the expense table. Clears a generous range to also mop up an
// older-position summary/chart.
function fixSummaryTable(sheet) {
  sheet.getRange(1, 8, 75, 7).clearContent().clearFormat(); // H1:N75
  buildSummaryTable(sheet);
}

// ====================== TRIGGERS ======================
function installScanTrigger() {
  stopAutoScan();
  ScriptApp.newTrigger('scanEmails').timeBased().everyHours(1).create();
}

function startAutoScan() {
  installScanTrigger();
  SpreadsheetApp.getUi().alert('Auto-scan started — emails will be checked every hour.');
}

function stopAutoScan() {
  ScriptApp.getProjectTriggers().forEach(t => {
    if (t.getHandlerFunction() === 'scanEmails') ScriptApp.deleteTrigger(t);
  });
}

function scanEmailsMenu() {
  const added = scanEmails();
  SpreadsheetApp.getUi().alert(`Scan complete. ${added} new expense(s) added.`);
}

// ====================== CORE SCAN ======================
function scanEmails() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const setupSheet = ss.getSheetByName(SETUP_SHEET_NAME);
  if (!setupSheet) throw new Error('Run "Run Setup" from the Expense Tracker menu first.');

  setupSheet.getRange(WARNING_CELL).setValue('');

  const apiKey = getApiKey(setupSheet);
  if (!apiKey) {
    setupSheet.getRange(WARNING_CELL).setValue(
      'No Gemini API key saved. Click Expense Tracker > Update Gemini API Key.'
    );
    return 0;
  }

  const model = (setupSheet.getRange(MODEL_CELL).getValue() || DEFAULT_MODEL).toString().trim();
  const keywordsRaw = setupSheet.getRange(KEYWORDS_CELL).getValue().toString().trim();
  const keywords = (keywordsRaw || DEFAULT_KEYWORDS).split(',').map(k => k.trim()).filter(Boolean);
  const sendersRaw = setupSheet.getRange(SENDERS_CELL).getValue().toString().trim();
  const senders = sendersRaw ? sendersRaw.split(',').map(s => s.trim()).filter(Boolean) : [];
  const categories = getCategoryList(setupSheet);

  const processedLogSheet = getOrCreateProcessedLogSheet(ss);
  const lastLogRow = processedLogSheet.getLastRow();
  const processedIds = lastLogRow > 1
    ? new Set(processedLogSheet.getRange(2, 1, lastLogRow - 1, 1).getValues().flat())
    : new Set();

  const keywordQuery = keywords.map(k => `"${k}"`).join(' OR ');
  let query;
  if (senders.length > 0) {
    // Restrict to trusted bank/card-issuer senders — the main defense against
    // double-counting a purchase that both the bank and the merchant email about.
    const senderQuery = senders.map(s => `from:${s}`).join(' OR ');
    query = `(${senderQuery}) (${keywordQuery}) ${SCAN_WINDOW} -label:${GMAIL_LABEL}`;
  } else {
    query = `(${keywordQuery}) ${SCAN_WINDOW} -label:${GMAIL_LABEL}`;
    setupSheet.getRange(WARNING_CELL).setValue(
      'No trusted bank senders set in B9 — scanning all matching emails. This can ' +
      'double-count a purchase if both your bank and a merchant email you about it. ' +
      'Add your bank/card issuer\'s notification sender domain(s) to B9 for accurate results.'
    );
  }

  let threads;
  try {
    threads = GmailApp.search(query, 0, MAX_THREADS_PER_RUN);
  } catch (e) {
    setupSheet.getRange(WARNING_CELL).setValue('Gmail search failed: ' + e.message);
    return 0;
  }

  const label = GmailApp.getUserLabelByName(GMAIL_LABEL) || GmailApp.createLabel(GMAIL_LABEL);
  const newLogRows = [];
  const pendingTransactions = []; // collected here, then inserted in date order below
  let apiError = ''; // first Gemini failure this run; later emails are left for the next run

  threads.forEach(thread => {
    let threadFullyHandled = true;

    // Every message is analyzed independently — some senders reuse one Gmail
    // thread across multiple distinct transactions, so skipping later messages
    // in a thread would silently miss real expenses.
    thread.getMessages().forEach(message => {
      const msgId = message.getId();
      if (processedIds.has(msgId)) return;

      if (apiError) {
        threadFullyHandled = false;
        return;
      }

      try {
        const result = analyzeEmailWithGemini(message, categories, apiKey, model);

        if (result && result.is_transaction) {
          let txDate = message.getDate();
          if (result.date) {
            const parsed = new Date(result.date + 'T00:00:00');
            if (!isNaN(parsed)) txDate = parsed;
          }
          const timeValue = /^\d{1,2}:\d{2}$/.test(result.time || '') ? result.time : '00:00';
          const [hh, mm] = timeValue.split(':').map(Number);
          const sortKey = new Date(txDate);
          sortKey.setHours(hh, mm, 0, 0);

          pendingTransactions.push({ result, message, txDate, sortKey });
        }

        processedIds.add(msgId);
        newLogRows.push([msgId, new Date()]);
      } catch (err) {
        Logger.log('Error on message ' + msgId + ': ' + err);
        if (/^Gemini API error/.test(err.message)) apiError = err.message;
        threadFullyHandled = false; // leave unlabeled so it's retried next run
      }
    });

    if (threadFullyHandled) thread.addLabel(label);
  });

  // Gmail search doesn't return results in chronological order (roughly
  // newest-first, but not guaranteed), so sort every transaction found in
  // this run oldest-to-newest before writing any rows. This keeps each
  // month's sheet in a consistent, predictable order no matter how many
  // emails a single run picks up.
  pendingTransactions.sort((a, b) => a.sortKey - b.sortKey);

  let insertedCount = 0;
  pendingTransactions.forEach(({ result, message, txDate }) => {
    const targetSheet = getOrCreateMonthSheet(ss, txDate);

    // Same date + same amount already logged elsewhere in this sheet — still
    // inserted (skipping risks losing a genuine second purchase) but flagged
    // so it's easy to spot and remove if it really is a duplicate.
    const dupRows = findPossibleDuplicateRows(targetSheet, txDate, result.amount);
    insertExpenseRow(targetSheet, result, message, categories, txDate, dupRows.length > 0);
    insertedCount++;
  });

  if (newLogRows.length > 0) {
    processedLogSheet.getRange(processedLogSheet.getLastRow() + 1, 1, newLogRows.length, 2)
      .setValues(newLogRows);
  }

  if (apiError) {
    setupSheet.getRange(WARNING_CELL).setValue(
      apiError + ' — affected emails will be retried on the next scan. If the key is ' +
      'wrong, click Expense Tracker > Update Gemini API Key.'
    );
  }

  setupSheet.getRange(LAST_SCAN_TIME_CELL).setValue(new Date());
  setupSheet.getRange(LAST_SCAN_RESULT_CELL).setValue(`${insertedCount} new expense(s) added`);

  return insertedCount;
}

// ====================== GEMINI ======================
function analyzeEmailWithGemini(message, categories, apiKey, model) {
  const subject = message.getSubject() || '';
  const from = message.getFrom() || '';
  let body = message.getPlainBody();
  if (!body) body = message.getBody().replace(/<[^>]+>/g, ' ');
  body = body.substring(0, 6000);

  const emailDateStr = Utilities.formatDate(message.getDate(), TIMEZONE, 'yyyy-MM-dd');

  const prompt =
    'You are a financial email classifier for a personal expense tracker.\n' +
    'Decide whether the email below is a payment/transaction/purchase NOTIFICATION ' +
    '(from a bank, card issuer, e-wallet, or merchant) reporting a specific amount of ' +
    'money spent, paid, or charged to the recipient.\n\n' +
    'If it is NOT a single transaction notification (e.g. marketing, newsletter, monthly ' +
    'statement summary, security alert, balance inquiry, promo), return only ' +
    '{"is_transaction": false}.\n\n' +
    'If it IS a transaction notification, extract:\n' +
    '- date: transaction date as YYYY-MM-DD (use ' + emailDateStr + ' if not stated in the email)\n' +
    '- time: transaction time as 24-hour HH:MM (use "00:00" if not stated)\n' +
    '- amount: the transaction amount as a positive plain number, no currency symbols/commas\n' +
    '- description: a short 3-8 word merchant or purpose summary\n' +
    '- category: the single best match from this exact list: [' + categories.join(', ') + ']. ' +
    'If nothing fits well, use "Other".\n\n' +
    'Email From: ' + from + '\n' +
    'Email Subject: ' + subject + '\n' +
    'Email Body:\n' + body;

  const schema = {
    type: 'OBJECT',
    properties: {
      is_transaction: { type: 'BOOLEAN' },
      date:        { type: 'STRING' },
      time:        { type: 'STRING' },
      amount:      { type: 'NUMBER' },
      description: { type: 'STRING' },
      category:    { type: 'STRING' }
    },
    required: ['is_transaction']
  };

  const url = `https://generativelanguage.googleapis.com/v1beta/models/${model}:generateContent?key=${apiKey}`;
  const payload = {
    contents: [{ parts: [{ text: prompt }] }],
    generationConfig: {
      responseMimeType: 'application/json',
      responseSchema: schema,
      temperature: 0
    }
  };

  const response = UrlFetchApp.fetch(url, {
    method: 'post',
    contentType: 'application/json',
    payload: JSON.stringify(payload),
    muteHttpExceptions: true
  });

  // Throw rather than return null: a null here would be logged as "processed,
  // not a transaction" and the email would never be retried (e.g. after a
  // wrong key or a rate-limit hit). scanEmails() catches this and surfaces it.
  if (response.getResponseCode() !== 200) {
    let detail = response.getContentText();
    try { detail = JSON.parse(detail).error.message; } catch (e) {}
    throw new Error('Gemini API error ' + response.getResponseCode() + ': ' + detail);
  }

  const json = JSON.parse(response.getContentText());
  const text = json.candidates && json.candidates[0] &&
               json.candidates[0].content && json.candidates[0].content.parts[0].text;
  if (!text) return null;

  let parsed;
  try {
    parsed = JSON.parse(text);
  } catch (e) {
    Logger.log('Could not parse Gemini JSON output: ' + text);
    return null;
  }

  if (!parsed.is_transaction) return { is_transaction: false };

  const matched = categories.find(c => c.toLowerCase() === (parsed.category || '').toLowerCase());
  parsed.category = matched || categories[categories.length - 1] || 'Other';
  parsed.amount = Number(parsed.amount) || 0;

  return parsed;
}

// ====================== ROW INSERT ======================
function insertExpenseRow(sheet, data, message, categories, txDate, isPossibleDuplicate) {
  const newRow = getNextExpenseRow(sheet);
  const timeValue = /^\d{1,2}:\d{2}$/.test(data.time || '') ? data.time : '00:00';
  let description = data.description || message.getSubject() || '(no description)';
  if (isPossibleDuplicate) description = '** ' + description;

  sheet.getRange(newRow, 1, 1, 7).setValues([[
    Utilities.formatDate(txDate, TIMEZONE, 'yyyy-MM-dd'),
    timeValue,
    data.amount,
    description,
    data.category,
    getBankLabel(message),
    message.getId()
  ]]);

  if (isPossibleDuplicate) {
    sheet.getRange(newRow, 1, 1, 6).setBackground('#fff2cc');
    sheet.getRange(newRow, 4).setNote(
      'Same date and amount as another row in this sheet — this can happen when two ' +
      'different emails (e.g. your bank and a merchant) report the same purchase. ' +
      'Check and delete this row if it\'s a duplicate.'
    );
  }
}

// Extracts a clean email address from a "From" header like
// 'DBS PayLah! <paylah.alert@dbs.com>', falling back to the raw string.
function getSenderEmail(message) {
  const from = message.getFrom() || '';
  const match = from.match(/<([^>]+)>/);
  return match ? match[1] : from.trim();
}

// Resolves a sender's email to a friendly bank name for the Source column.
// Falls back to a capitalized guess from the domain for senders not in
// BANK_NAME_MAP, rather than showing the raw address.
function getBankLabel(message) {
  const email = getSenderEmail(message).toLowerCase();
  const known = BANK_NAME_MAP.find(b => email.includes(b.match));
  if (known) return known.name;

  const domain = email.split('@')[1] || email;
  const mainPart = domain.split('.')[0];
  return mainPart ? mainPart.charAt(0).toUpperCase() + mainPart.slice(1) : email;
}

// Finds the next empty row in the expense TABLE specifically (column A),
// ignoring the category-summary formulas in columns J/K — those extend
// much further down the sheet and must not affect where new rows go.
function getNextExpenseRow(sheet) {
  const numRows = sheet.getMaxRows();
  const colA = sheet.getRange(2, 1, numRows - 1, 1).getValues();
  for (let i = colA.length - 1; i >= 0; i--) {
    if (colA[i][0] !== '' && colA[i][0] !== null) {
      return i + 3; // row (2+i) is the last filled row; next row is +1
    }
  }
  return 2; // no expenses yet — first one goes right under the header
}

// Finds existing rows in a month sheet with the same date and amount (±0.01).
function findPossibleDuplicateRows(sheet, txDate, amount) {
  const lastFilledRow = getNextExpenseRow(sheet) - 1;
  if (lastFilledRow < 2) return [];
  const dateStr = Utilities.formatDate(txDate, TIMEZONE, 'yyyy-MM-dd');
  const values = sheet.getRange(2, 1, lastFilledRow - 1, 3).getValues(); // Date, Time, Amount
  const matches = [];
  values.forEach((row, i) => {
    const rowDateStr = row[0] instanceof Date ? Utilities.formatDate(row[0], TIMEZONE, 'yyyy-MM-dd') : row[0];
    if (rowDateStr === dateStr && Math.abs(Number(row[2]) - amount) < 0.01) {
      matches.push(i + 2);
    }
  });
  return matches;
}

// ====================== HELPERS ======================
function getCategoryList(setupSheet) {
  return setupSheet.getRange(CATEGORY_START_ROW, 1, CATEGORY_END_ROW - CATEGORY_START_ROW + 1, 1)
    .getValues().flat().map(v => v.toString().trim()).filter(Boolean);
}

function getOrCreateProcessedLogSheet(ss) {
  let sheet = ss.getSheetByName(PROCESSED_LOG_SHEET);
  if (!sheet) {
    sheet = ss.insertSheet(PROCESSED_LOG_SHEET);
    sheet.getRange('A1:B1').setValues([['Message ID', 'Processed At']]).setFontWeight('bold');
    sheet.hideSheet();
  }
  return sheet;
}