Mable PDF Budget Summary Tracker

MablePdfBudgetSummaryTracker

/***** Mable Invoice / NDIS Budget Tracker for Google Sheets *****/
/*
  Paste this whole file into Extensions > Apps Script.
  Run initMableInvoiceTracker() once from the Apps Script editor.

  This version uses the Gemini API to extract PDF invoice transactions directly from the PDF.
  It does NOT try to create Gmail filters.
*/

const MABLE = {
  subjectNeedle: 'Mable - Copy of invoice',
  labels: {
    pending: 'Mable Pending',
    processed: 'Mable Processed',
    error: 'Mable Error'
  },
  sheets: {
    summary: 'Summary',
    transactions: 'Transactions',
    invoices: 'Invoices Processed',
    instructions: 'Instructions',
    setupLog: 'Setup Log',
    debug: 'PDF Text Debug'
  },
  properties: {
    spreadsheetId: 'MABLE_TRACKER_SPREADSHEET_ID',
    geminiApiKey: 'MABLE_GEMINI_API_KEY',
    geminiModelOverride: 'MABLE_GEMINI_MODEL'
  },
  gemini: {
    apiBase: 'https://generativelanguage.googleapis.com/v1beta',
    preferredFallbackModel: 'gemini-2.5-pro'
  },
  transactionHeaders: [
    'Invoice Number',
    'Invoice Date',
    'Message Date',
    'Processed At',
    'Participant',
    'Provider',
    'Provider ABN',
    'Session Date',
    'Description',
    'Budget Category',
    'Support Item',
    'Quantity',
    'Rate',
    'Worker GST',
    'Mable GST',
    'Total GST',
    'Total',
    'Attachment Name',
    'Gmail Thread ID',
    'Gmail Message ID',
    'Unique Key'
  ],
  invoiceHeaders: [
    'Invoice Number',
    'Status',
    'Invoice Date',
    'Amount Due',
    'Subject',
    'Attachment Name',
    'From',
    'Message Date',
    'Processed At',
    'Transactions Added',
    'Parsed Total',
    'Gmail Thread ID',
    'Gmail Message ID',
    'Error'
  ]
};

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Mable Tracker')
    .addItem('Process pending Mable invoices now', 'processPendingMableInvoices')
    .addItem('Find matching emails and label pending', 'findAndLabelPendingMableInvoices')
    .addItem('Reset one invoice for re-import', 'promptResetMableInvoiceForReimport')
    .addItem('Refresh summary', 'refreshSummary')
    .addSeparator()
    .addItem('Set Gemini API key', 'setGeminiApiKey')
    .addItem('Test Gemini configuration', 'testGeminiConfiguration')
    .addSeparator()
    .addItem('Rebuild tracker setup', 'initMableInvoiceTracker')
    .addToUi();
}

function initMableInvoiceTracker() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  PropertiesService.getScriptProperties().setProperty(MABLE.properties.spreadsheetId, ss.getId());

  ensureLabels_();
  ensureSheets_();
  removeOldCsvConfigSheets_();
  initialiseTransactionsSheet_();
  initialiseInvoicesSheet_();
  initialiseInstructionsSheet_();
  initialiseSetupLogSheet_();
  installDailyTrigger_();
  refreshSummary();
  onOpen();

  if (!getGeminiApiKey_()) {
    logSetup_('Initialised tracker. Gemini API key is not set yet. Use Mable Tracker > Set Gemini API key.');
    ss.toast('Tracker initialised. Now use Mable Tracker > Set Gemini API key.', 'Mable Tracker', 10);
  } else {
    logSetup_('Initialised tracker. Labels exist. Daily trigger installed. Gemini API key is set.');
    ss.toast('Mable invoice tracker initialised.', 'Mable Tracker', 10);
  }
}

function setGeminiApiKey() {
  const ui = SpreadsheetApp.getUi();
  const response = ui.prompt(
    'Set Gemini API key',
    'Paste your Gemini API key from Google AI Studio.',
    ui.ButtonSet.OK_CANCEL
  );

  if (response.getSelectedButton() !== ui.Button.OK) return;

  const apiKey = cleanCell_(response.getResponseText());
  if (!apiKey) {
    ui.alert('No API key entered.');
    return;
  }

  PropertiesService.getScriptProperties().setProperty(MABLE.properties.geminiApiKey, apiKey);
  logSetup_('Gemini API key saved to Script Properties.');
  ui.alert('Gemini API key saved.');
}

function testGeminiConfiguration() {
  const ui = SpreadsheetApp.getUi();

  try {
    const apiKey = requireGeminiApiKey_();
    const model = resolveGeminiModel_(apiKey);
    logSetup_('Gemini configuration OK. Using model: ' + model);
    ui.alert('Gemini configuration OK.\n\nUsing model:\n' + model);
  } catch (err) {
    const message = String(err && err.stack ? err.stack : err);
    logSetup_('Gemini configuration test failed: ' + message);
    ui.alert('Gemini configuration failed:\n\n' + message);
  }
}

function processPendingMableInvoices() {
  const ss = getSpreadsheet_();
  ensureLabels_();
  ensureSheets_();
  initialiseTransactionsSheet_();
  initialiseInvoicesSheet_();

  const labels = getLabels_();
  findAndLabelPendingMableInvoices();

  const pendingThreads = labels.pending.getThreads(0, 100);
  let processedThreads = 0;
  let processedInvoices = 0;
  let skippedInvoices = 0;
  let transactionCount = 0;
  let errorCount = 0;

  pendingThreads.forEach(function (thread) {
    let threadHadProcessableMableInvoice = false;
    let threadHadError = false;

    try {
      const messages = thread.getMessages();

      messages.forEach(function (message) {
        const subject = String(message.getSubject() || '');

        if (subject.indexOf(MABLE.subjectNeedle) === -1) return;

        const attachments = message.getAttachments({ includeInlineImages: false, includeAttachments: true });
        const pdfs = attachments.filter(function (attachment) {
          const name = String(attachment.getName() || '').toLowerCase();
          const type = String(attachment.getContentType() || '').toLowerCase();
          return name.endsWith('.pdf') || type.indexOf('pdf') !== -1;
        });

        if (pdfs.length === 0) {
          threadHadProcessableMableInvoice = true;
          threadHadError = true;
          upsertInvoiceLog_({
            invoiceNumber: extractInvoiceNumberFromSubject_(subject) || 'NO_PDF_' + message.getId(),
            status: 'Error',
            invoiceDate: '',
            amountDue: '',
            subject: subject,
            attachmentName: '',
            from: message.getFrom(),
            messageDate: message.getDate(),
            processedAt: new Date(),
            transactionsAdded: 0,
            parsedTotal: '',
            threadId: thread.getId(),
            messageId: message.getId(),
            error: 'No PDF attachment found.'
          });
          errorCount++;
          return;
        }

        pdfs.forEach(function (pdf) {
          threadHadProcessableMableInvoice = true;

          const subjectInvoiceNumber = extractInvoiceNumberFromSubject_(subject);

          if (subjectInvoiceNumber && isInvoiceProcessed_(subjectInvoiceNumber)) {
            skippedInvoices++;
            return;
          }

          try {
            const result = processMablePdfAttachment_(thread, message, pdf, subjectInvoiceNumber);
            processedInvoices++;
            transactionCount += result.transactionsAdded;
          } catch (err) {
            threadHadError = true;
            errorCount++;

            upsertInvoiceLog_({
              invoiceNumber: subjectInvoiceNumber || 'ERROR_' + message.getId() + '_' + pdf.getName(),
              status: 'Error',
              invoiceDate: '',
              amountDue: '',
              subject: subject,
              attachmentName: pdf.getName(),
              from: message.getFrom(),
              messageDate: message.getDate(),
              processedAt: new Date(),
              transactionsAdded: 0,
              parsedTotal: '',
              threadId: thread.getId(),
              messageId: message.getId(),
              error: String(err && err.stack ? err.stack : err)
            });
          }
        });
      });

      if (threadHadProcessableMableInvoice) {
        if (threadHadError) {
          labels.error.addToThread(thread);
          labels.pending.removeFromThread(thread);
        } else {
          labels.processed.addToThread(thread);
          labels.error.removeFromThread(thread);
          labels.pending.removeFromThread(thread);
        }

        processedThreads++;
      }
    } catch (err) {
      labels.error.addToThread(thread);
      labels.pending.removeFromThread(thread);
      errorCount++;
      logSetup_('Thread processing error: ' + String(err && err.stack ? err.stack : err));
    }
  });

  refreshSummary();

  ss.toast(
    'Threads: ' + processedThreads +
    ' | Invoices: ' + processedInvoices +
    ' | Skipped: ' + skippedInvoices +
    ' | Transactions: ' + transactionCount +
    ' | Errors: ' + errorCount,
    'Mable Tracker',
    10
  );
}

function promptResetMableInvoiceForReimport() {
  const ui = SpreadsheetApp.getUi();
  const response = ui.prompt(
    'Reset Mable invoice for re-import',
    'Enter the invoice number to remove from Transactions and Invoices Processed, then queue the Gmail thread again if it can be found from the invoice log.',
    ui.ButtonSet.OK_CANCEL
  );

  if (response.getSelectedButton() !== ui.Button.OK) return;

  const invoiceNumber = cleanCell_(response.getResponseText());
  if (!invoiceNumber) return;

  const result = resetMableInvoiceForReimport_(invoiceNumber);

  ui.alert(
    'Reset complete',
    'Invoice: ' + invoiceNumber +
      '\nTransactions deleted: ' + result.transactionsDeleted +
      '\nInvoice log rows deleted: ' + result.invoiceRowsDeleted +
      '\nGmail thread queued: ' + (result.threadQueued ? 'yes' : 'no'),
    ui.ButtonSet.OK
  );
}

function resetMableInvoiceForReimport_(invoiceNumber) {
  const target = cleanCell_(invoiceNumber);
  if (!target) throw new Error('Invoice number is required.');

  ensureLabels_();

  const labels = getLabels_();
  const txSheet = getSheet_(MABLE.sheets.transactions);
  const invoiceSheet = getSheet_(MABLE.sheets.invoices);

  let threadId = '';
  let transactionsDeleted = 0;
  let invoiceRowsDeleted = 0;

  const invoiceLastRow = invoiceSheet.getLastRow();
  if (invoiceLastRow >= 2) {
    const invoiceValues = invoiceSheet.getRange(2, 1, invoiceLastRow - 1, MABLE.invoiceHeaders.length).getDisplayValues();

    for (let i = invoiceValues.length - 1; i >= 0; i--) {
      if (cleanCell_(invoiceValues[i][0]) !== target) continue;

      if (!threadId) threadId = cleanCell_(invoiceValues[i][11]);
      invoiceSheet.deleteRow(i + 2);
      invoiceRowsDeleted++;
    }
  }

  const txLastRow = txSheet.getLastRow();
  if (txLastRow >= 2) {
    const invoiceNumbers = txSheet.getRange(2, 1, txLastRow - 1, 1).getDisplayValues();

    for (let i = invoiceNumbers.length - 1; i >= 0; i--) {
      if (cleanCell_(invoiceNumbers[i][0]) !== target) continue;

      txSheet.deleteRow(i + 2);
      transactionsDeleted++;
    }
  }

  let threadQueued = false;

  if (threadId) {
    try {
      const thread = GmailApp.getThreadById(threadId);

      if (thread) {
        labels.processed.removeFromThread(thread);
        labels.error.removeFromThread(thread);
        labels.pending.addToThread(thread);
        threadQueued = true;
      }
    } catch (err) {
      logSetup_('Could not queue Gmail thread for invoice ' + target + ': ' + String(err));
    }
  }

  initialiseInvoicesSheet_();
  formatTransactionsSheet_();
  refreshSummary();

  return {
    transactionsDeleted: transactionsDeleted,
    invoiceRowsDeleted: invoiceRowsDeleted,
    threadQueued: threadQueued
  };
}

function findAndLabelPendingMableInvoices() {
  ensureLabels_();
  const labels = getLabels_();
  const processed = readProcessedInvoiceNumbers_();
  const threads = GmailApp.search('subject:"' + MABLE.subjectNeedle + '" has:attachment filename:pdf', 0, 100);
  let labelled = 0;

  threads.forEach(function (thread) {
    if (threadHasLabel_(thread, MABLE.labels.processed) || threadHasLabel_(thread, MABLE.labels.error)) return;

    const messages = thread.getMessages();
    let shouldLabel = false;

    messages.forEach(function (message) {
      const subject = String(message.getSubject() || '');
      if (subject.indexOf(MABLE.subjectNeedle) === -1) return;

      const invoiceNumber = extractInvoiceNumberFromSubject_(subject);
      if (invoiceNumber && processed[invoiceNumber]) return;

      const attachments = message.getAttachments({ includeInlineImages: false, includeAttachments: true });
      const hasPdf = attachments.some(function (attachment) {
        const name = String(attachment.getName() || '').toLowerCase();
        const type = String(attachment.getContentType() || '').toLowerCase();
        return name.endsWith('.pdf') || type.indexOf('pdf') !== -1;
      });

      if (hasPdf) shouldLabel = true;
    });

    if (shouldLabel) {
      labels.pending.addToThread(thread);
      labelled++;
    }
  });

  logSetup_('findAndLabelPendingMableInvoices labelled ' + labelled + ' thread(s).');
  return labelled;
}

function processMablePdfAttachment_(thread, message, pdf, subjectInvoiceNumber) {
  const parsed = parseMableInvoicePdfWithGemini_(pdf, subjectInvoiceNumber);
  const alreadyProcessed = isInvoiceProcessed_(parsed.invoiceNumber);

  if (alreadyProcessed) {
    return { invoiceNumber: parsed.invoiceNumber, transactionsAdded: 0, skipped: true };
  }

  if (!parsed.transactions.length) {
    throw new Error('Gemini returned no invoice transactions for PDF: ' + pdf.getName());
  }

  const txSheet = getSheet_(MABLE.sheets.transactions);
  const existingTransactionKeys = readExistingTransactionKeys_();
  const now = new Date();
  const rows = [];

  parsed.transactions.forEach(function (tx) {
    const uniqueKey = makeTransactionKey_(parsed.invoiceNumber, tx);
    if (existingTransactionKeys[uniqueKey]) return;

    rows.push([
      parsed.invoiceNumber,
      parsed.invoiceDate,
      message.getDate(),
      now,
      parsed.participant,
      tx.provider,
      tx.providerAbn,
      tx.sessionDate,
      tx.description,
      tx.budgetCategory,
      tx.supportItem,
      tx.quantity,
      tx.rate,
      tx.workerGst,
      tx.mableGst,
      tx.totalGst,
      tx.total,
      pdf.getName(),
      thread.getId(),
      message.getId(),
      uniqueKey
    ]);

    existingTransactionKeys[uniqueKey] = true;
  });

  if (rows.length > 0) {
    txSheet.getRange(txSheet.getLastRow() + 1, 1, rows.length, MABLE.transactionHeaders.length).setValues(rows);
    sortTransactions_();
    formatTransactionsSheet_();
  }

  const parsedTotal = parsed.transactions.reduce(function (sum, tx) {
    return sum + (Number(tx.total) || 0);
  }, 0);

  upsertInvoiceLog_({
    invoiceNumber: parsed.invoiceNumber,
    status: 'Processed',
    invoiceDate: parsed.invoiceDate,
    amountDue: parsed.amountDue,
    subject: message.getSubject(),
    attachmentName: pdf.getName(),
    from: message.getFrom(),
    messageDate: message.getDate(),
    processedAt: now,
    transactionsAdded: rows.length,
    parsedTotal: parsedTotal,
    threadId: thread.getId(),
    messageId: message.getId(),
    error: ''
  });

  return { invoiceNumber: parsed.invoiceNumber, transactionsAdded: rows.length, skipped: false };
}

function parseMableInvoicePdfWithGemini_(pdfBlob, fallbackInvoiceNumber) {
  const apiKey = requireGeminiApiKey_();
  const model = resolveGeminiModel_(apiKey);
  const prompt = buildGeminiInvoiceExtractionPrompt_(fallbackInvoiceNumber);
  const schema = buildGeminiInvoiceJsonSchema_();

  const payload = {
    contents: [
      {
        role: 'user',
        parts: [
          { text: prompt },
          {
            inline_data: {
              mime_type: 'application/pdf',
              data: Utilities.base64Encode(pdfBlob.getBytes())
            }
          }
        ]
      }
    ],
    generationConfig: {
      temperature: 0,
      top_p: 1,
      max_output_tokens: 32768,
      response_mime_type: 'application/json',
      response_schema: schema
    }
  };

  const response = callGeminiGenerateContent_(apiKey, model, payload);
  const jsonText = extractGeminiJsonText_(response);
  let parsed;

  try {
    parsed = JSON.parse(jsonText);
  } catch (err) {
    writeDebugText_(fallbackInvoiceNumber || '', jsonText);
    throw new Error('Gemini returned invalid JSON: ' + String(err) + '\n\nRaw response was written to PDF Text Debug.');
  }

  const normalised = normaliseGeminiInvoiceResult_(parsed, fallbackInvoiceNumber);
  validateGeminiInvoiceResult_(normalised);

  logSetup_(
    'Gemini parsed invoice ' + normalised.invoiceNumber +
    ' using model ' + model +
    ': transactions=' + normalised.transactions.length +
    ', parsedTotal=' + normalised.transactions.reduce(function (sum, tx) {
      return sum + (Number(tx.total) || 0);
    }, 0).toFixed(2)
  );

  return normalised;
}

function buildGeminiInvoiceExtractionPrompt_(fallbackInvoiceNumber) {
  return [
    'You are extracting structured transaction data from a Mable NDIS tax invoice PDF.',
    '',
    'Return JSON only. Do not include markdown, explanations, comments, or extra keys.',
    '',
    'Critical requirements:',
    '1. Extract every individual invoice table line item exactly once.',
    '2. Do not aggregate, merge, summarise, or deduplicate different transaction rows.',
    '3. Page totals, invoice totals, amount paid, amount due, payment details, bank details, and remittance text are not transaction rows.',
    '4. Each transaction row has a session start date, description, support item, quantity, rate, worker GST, Mable GST, total GST, and total.',
    '5. Support item codes look like 04_104_0125_6_1 or 01_011_0107_1_1.',
    '6. Preserve the full human-readable description for each row, including provider name, ABN, service text, day, time range, and km text when present.',
    '7. provider is the worker name before "ABN".',
    '8. providerAbn is the 11 digit ABN after "ABN".',
    '9. budgetCategory is the service/category text after the provider ABN and dash, stopping before modifiers such as "- Standard", "- Weekday", "- Saturday", "- Sunday", "- Public Holiday", or day/time details.',
    '10. Dates must be DD/MM/YYYY strings.',
    '11. Money and quantity fields must be numbers, not strings, and must not include "$" or commas.',
    '12. invoiceTotal should be the invoice total before payment details.',
    '13. amountDue should be the amount due shown on the invoice.',
    '14. The sum of transaction total values should equal invoiceTotal unless the PDF itself is inconsistent.',
    '',
    'For Mable invoices, travel rows are separate transactions and must be included.',
    'For Mable invoices, repeated support item codes on different dates/times are separate transactions and must be included.',
    '',
    'Fallback invoice number, if the PDF invoice number is unclear: ' + cleanCell_(fallbackInvoiceNumber || '')
  ].join('\n');
}

function buildGeminiInvoiceJsonSchema_() {
  return {
    type: 'OBJECT',
    required: [
      'invoiceNumber',
      'invoiceDate',
      'invoiceTotal',
      'amountDue',
      'participant',
      'transactions'
    ],
    properties: {
      invoiceNumber: { type: 'STRING' },
      invoiceDate: { type: 'STRING' },
      invoiceTotal: { type: 'NUMBER' },
      amountDue: { type: 'NUMBER' },
      participant: { type: 'STRING' },
      transactions: {
        type: 'ARRAY',
        items: {
          type: 'OBJECT',
          required: [
            'sessionDate',
            'description',
            'provider',
            'providerAbn',
            'budgetCategory',
            'supportItem',
            'quantity',
            'rate',
            'workerGst',
            'mableGst',
            'totalGst',
            'total'
          ],
          properties: {
            sessionDate: { type: 'STRING' },
            description: { type: 'STRING' },
            provider: { type: 'STRING' },
            providerAbn: { type: 'STRING' },
            budgetCategory: { type: 'STRING' },
            supportItem: { type: 'STRING' },
            quantity: { type: 'NUMBER' },
            rate: { type: 'NUMBER' },
            workerGst: { type: 'NUMBER' },
            mableGst: { type: 'NUMBER' },
            totalGst: { type: 'NUMBER' },
            total: { type: 'NUMBER' }
          }
        }
      }
    }
  };
}

function callGeminiGenerateContent_(apiKey, model, payload) {
  const url =
    MABLE.gemini.apiBase +
    '/models/' +
    encodeURIComponent(model) +
    ':generateContent?key=' +
    encodeURIComponent(apiKey);

  let lastError = '';

  for (let attempt = 1; attempt <= 2; attempt++) {
    const response = UrlFetchApp.fetch(url, {
      method: 'post',
      contentType: 'application/json',
      payload: JSON.stringify(payload),
      muteHttpExceptions: true
    });

    const code = response.getResponseCode();
    const body = response.getContentText();

    if (code >= 200 && code < 300) {
      return JSON.parse(body);
    }

    lastError = 'HTTP ' + code + ': ' + body;

    if (code === 429 || code === 500 || code === 502 || code === 503 || code === 504) {
      Utilities.sleep(2500 * attempt);
      continue;
    }

    break;
  }

  throw new Error('Gemini generateContent failed using model ' + model + '. ' + lastError);
}

function extractGeminiJsonText_(response) {
  if (!response) {
    throw new Error('Gemini response was empty.');
  }

  if (response.promptFeedback && response.promptFeedback.blockReason) {
    throw new Error('Gemini blocked the prompt: ' + JSON.stringify(response.promptFeedback));
  }

  const candidates = response.candidates || [];
  if (!candidates.length) {
    throw new Error('Gemini returned no candidates: ' + JSON.stringify(response));
  }

  const candidate = candidates[0];

  if (candidate.finishReason && candidate.finishReason !== 'STOP') {
    throw new Error('Gemini did not finish cleanly. finishReason=' + candidate.finishReason + ' response=' + JSON.stringify(response));
  }

  const parts = candidate.content && candidate.content.parts ? candidate.content.parts : [];
  const text = parts.map(function (part) {
    return part.text || '';
  }).join('').trim();

  if (!text) {
    throw new Error('Gemini candidate contained no text: ' + JSON.stringify(response));
  }

  return text
    .replace(/^```json\s*/i, '')
    .replace(/^```\s*/i, '')
    .replace(/\s*```$/i, '')
    .trim();
}

function normaliseGeminiInvoiceResult_(parsed, fallbackInvoiceNumber) {
  const invoiceNumber = cleanCell_(parsed.invoiceNumber || fallbackInvoiceNumber || '');
  const invoiceDate = parseDate_(parsed.invoiceDate);
  const amountDue = parseMoney_(parsed.amountDue);
  const invoiceTotal = parseMoney_(parsed.invoiceTotal);
  const participant = cleanCell_(parsed.participant || '');

  const transactions = (parsed.transactions || []).map(function (tx) {
    const description = cleanDescription_(tx.description || '');
    const parts = parseDescriptionParts_(description);

    const provider = cleanCell_(tx.provider || parts.provider);
    const providerAbn = cleanCell_(tx.providerAbn || parts.providerAbn);
    const budgetCategory = cleanCell_(tx.budgetCategory || parts.budgetCategory);

    return {
      sessionDate: parseDate_(tx.sessionDate),
      description: description,
      provider: provider,
      providerAbn: providerAbn,
      budgetCategory: budgetCategory || 'Uncategorised',
      supportItem: cleanCell_(tx.supportItem || ''),
      quantity: parseNumber_(tx.quantity),
      rate: parseMoney_(tx.rate),
      workerGst: parseMoney_(tx.workerGst),
      mableGst: parseMoney_(tx.mableGst),
      totalGst: parseMoney_(tx.totalGst),
      total: parseMoney_(tx.total)
    };
  });

  transactions.forEach(function (tx) {
    tx.sessionDateRaw = formatDateForGeminiKey_(tx.sessionDate);
  });

  return {
    invoiceNumber: invoiceNumber,
    invoiceDate: invoiceDate,
    invoiceTotal: invoiceTotal,
    amountDue: amountDue,
    participant: participant,
    transactions: transactions
  };
}

function validateGeminiInvoiceResult_(parsed) {
  if (!parsed.invoiceNumber) {
    throw new Error('Gemini did not return an invoice number.');
  }

  if (!(parsed.invoiceDate instanceof Date) || isNaN(parsed.invoiceDate.getTime())) {
    throw new Error('Gemini returned an invalid invoice date for invoice ' + parsed.invoiceNumber + '.');
  }

  if (!parsed.transactions || !parsed.transactions.length) {
    throw new Error('Gemini returned no transactions for invoice ' + parsed.invoiceNumber + '.');
  }

  parsed.transactions.forEach(function (tx, index) {
    const row = index + 1;

    if (!(tx.sessionDate instanceof Date) || isNaN(tx.sessionDate.getTime())) {
      throw new Error('Gemini returned invalid sessionDate on transaction row ' + row + ' for invoice ' + parsed.invoiceNumber + '.');
    }

    if (!tx.description) throw new Error('Gemini returned blank description on transaction row ' + row + '.');
    if (!tx.supportItem) throw new Error('Gemini returned blank supportItem on transaction row ' + row + '.');
    if (!/^\d{2}_\d{3}_\d{4}_\d_\d$/.test(tx.supportItem)) {
      throw new Error('Gemini returned invalid supportItem "' + tx.supportItem + '" on transaction row ' + row + '.');
    }

    if (!tx.provider) throw new Error('Gemini returned blank provider on transaction row ' + row + '.');
    if (!tx.providerAbn) throw new Error('Gemini returned blank providerAbn on transaction row ' + row + '.');
    if (!/^\d{11}$/.test(tx.providerAbn)) {
      throw new Error('Gemini returned invalid providerAbn "' + tx.providerAbn + '" on transaction row ' + row + '.');
    }

    if (!(Number(tx.quantity) > 0)) throw new Error('Gemini returned invalid quantity on transaction row ' + row + '.');
    if (!(Number(tx.rate) >= 0)) throw new Error('Gemini returned invalid rate on transaction row ' + row + '.');
    if (!(Number(tx.total) >= 0)) throw new Error('Gemini returned invalid total on transaction row ' + row + '.');
  });

  const parsedTotal = parsed.transactions.reduce(function (sum, tx) {
    return sum + Number(tx.total || 0);
  }, 0);

  const expectedTotal = Number(parsed.invoiceTotal || 0) || Number(parsed.amountDue || 0);

  if (expectedTotal > 0 && Math.abs(parsedTotal - expectedTotal) > 0.05) {
    throw new Error(
      'Gemini transaction total mismatch for invoice ' + parsed.invoiceNumber +
      '. Transaction total=' + parsedTotal.toFixed(2) +
      ', expected invoice total=' + expectedTotal.toFixed(2) +
      '. The invoice was not imported.'
    );
  }
}

function getGeminiApiKey_() {
  return cleanCell_(PropertiesService.getScriptProperties().getProperty(MABLE.properties.geminiApiKey));
}

function requireGeminiApiKey_() {
  const apiKey = getGeminiApiKey_();

  if (!apiKey) {
    throw new Error('Gemini API key is not set. Use Mable Tracker > Set Gemini API key.');
  }

  return apiKey;
}

function resolveGeminiModel_(apiKey) {
  const override = cleanCell_(PropertiesService.getScriptProperties().getProperty(MABLE.properties.geminiModelOverride));

  if (override) {
    return override.replace(/^models\//, '');
  }

  try {
    const models = listGeminiModels_(apiKey);
    const chosen = chooseBestGeminiProModel_(models);

    if (chosen) {
      return chosen;
    }
  } catch (err) {
    logSetup_('Could not list Gemini models, falling back to ' + MABLE.gemini.preferredFallbackModel + ': ' + String(err));
  }

  return MABLE.gemini.preferredFallbackModel;
}

function listGeminiModels_(apiKey) {
  const url = MABLE.gemini.apiBase + '/models?key=' + encodeURIComponent(apiKey);

  const response = UrlFetchApp.fetch(url, {
    method: 'get',
    muteHttpExceptions: true
  });

  const code = response.getResponseCode();
  const body = response.getContentText();

  if (code < 200 || code >= 300) {
    throw new Error('Gemini models list failed. HTTP ' + code + ': ' + body);
  }

  const parsed = JSON.parse(body);
  return parsed.models || [];
}

function chooseBestGeminiProModel_(models) {
  const candidates = (models || []).filter(function (model) {
    const name = cleanCell_(model.name || '').replace(/^models\//, '').toLowerCase();
    const methods = model.supportedGenerationMethods || [];

    if (methods.indexOf('generateContent') === -1) return false;
    if (name.indexOf('gemini') === -1) return false;
    if (name.indexOf('pro') === -1) return false;
    if (name.indexOf('image') !== -1) return false;
    if (name.indexOf('embedding') !== -1) return false;
    if (name.indexOf('tts') !== -1) return false;
    if (name.indexOf('flash') !== -1) return false;
    if (name.indexOf('lite') !== -1) return false;

    return true;
  });

  if (!candidates.length) return '';

  candidates.sort(function (a, b) {
    return scoreGeminiModel_(b) - scoreGeminiModel_(a);
  });

  return cleanCell_(candidates[0].name || '').replace(/^models\//, '');
}

function scoreGeminiModel_(model) {
  const rawName = cleanCell_(model.name || '').replace(/^models\//, '');
  const name = rawName.toLowerCase();
  const versionMatch = name.match(/gemini[-_](\d+(?:\.\d+)?)/);
  const version = versionMatch ? Number(versionMatch[1]) : 0;

  let score = version * 10000;

  if (name.indexOf('pro') !== -1) score += 1000;
  if (name.indexOf('deep') !== -1 || name.indexOf('think') !== -1) score += 100;
  if (name.indexOf('preview') !== -1) score += 10;
  if (name.indexOf('exp') !== -1 || name.indexOf('experimental') !== -1) score += 5;

  return score;
}

function refreshSummary() {
  ensureSheets_();
  initialiseTransactionsSheet_();

  const sheet = getSheet_(MABLE.sheets.summary);
  const budgets = readExistingSummaryBudgets_(sheet);
  const pairs = readSupportItemCategoryPairs_();

  breakAllMerges_(sheet);
  sheet.clear();
  sheet.setFrozenRows(0);
  sheet.setFrozenColumns(0);
  sheet.setHiddenGridlines(true);

  sheet.getRange('A1').setValue('NDIS Spend Summary');
  sheet.getRange('A2').setValue('Budgets are entered directly into the yellow budget columns. Spend is grouped by Support Item and the budget category parsed from the Mable invoice description.');
  sheet.getRange('A3').setValue('Transactions are imported from Gmail PDF invoices labelled Mable Pending, extracted by Gemini, then moved to Mable Processed after import.');

  sheet.getRange('A1:Y1').merge().setFontWeight('bold').setFontSize(16).setBackground('#d9ead3');
  sheet.getRange('A2:Y2').merge().setWrap(true).setBackground('#f3f6f4');
  sheet.getRange('A3:Y3').merge().setWrap(true).setBackground('#f3f6f4');

  const headers = [
    'Support Item',
    'Budget Category',
    'Weekly Budget',
    'This Week',
    'Previous Week',
    'Week Δ',
    'Weekly Remaining',
    'Monthly Budget',
    'This Month',
    'Previous Month',
    'Month Δ',
    'Monthly Remaining',
    'Quarterly Budget',
    'This Quarter',
    'Previous Quarter',
    'Quarter Δ',
    'Quarterly Remaining',
    'Annual Budget',
    'YTD',
    'Previous YTD',
    'YTD Δ',
    'Annual Remaining',
    'Rolling 12 Months',
    'Previous Rolling 12',
    'Rolling 12 Δ'
  ];

  const headerRow = 5;
  const firstDataRow = 6;

  sheet.getRange(headerRow, 1, 1, headers.length).setValues([headers]);
  sheet.getRange(headerRow, 1, 1, headers.length).setFontWeight('bold').setBackground('#b6d7a8').setWrap(true);

  if (pairs.length > 0) {
    const rows = pairs.map(function (pair, i) {
      const row = firstDataRow + i;
      const key = summaryBudgetKey_(pair.supportItem, pair.budgetCategory);
      const budget = budgets[key] || { weekly: '', monthly: '', quarterly: '', annual: '' };

      return [
        pair.supportItem,
        pair.budgetCategory,
        budget.weekly,
        formulaThisWeek_(row),
        formulaPreviousWeek_(row),
        '=IF($A' + row + '="","",D' + row + '-E' + row + ')',
        '=IF($A' + row + '="","",IF($C' + row + '="","",$C' + row + '-D' + row + '))',
        budget.monthly,
        formulaThisMonth_(row),
        formulaPreviousMonth_(row),
        '=IF($A' + row + '="","",I' + row + '-J' + row + ')',
        '=IF($A' + row + '="","",IF($H' + row + '="","",$H' + row + '-I' + row + '))',
        budget.quarterly,
        formulaThisQuarter_(row),
        formulaPreviousQuarter_(row),
        '=IF($A' + row + '="","",N' + row + '-O' + row + ')',
        '=IF($A' + row + '="","",IF($M' + row + '="","",$M' + row + '-N' + row + '))',
        budget.annual,
        formulaYtd_(row),
        formulaPreviousYtd_(row),
        '=IF($A' + row + '="","",S' + row + '-T' + row + ')',
        '=IF($A' + row + '="","",IF($R' + row + '="","",$R' + row + '-S' + row + '))',
        formulaRolling12_(row),
        formulaPreviousRolling12_(row),
        '=IF($A' + row + '="","",W' + row + '-X' + row + ')'
      ];
    });

    sheet.getRange(firstDataRow, 1, rows.length, headers.length).setValues(rows);
  }

  const totalRow = firstDataRow + pairs.length;
  sheet.getRange(totalRow, 1).setValue('TOTAL');
  sheet.getRange(totalRow, 1, 1, headers.length).setFontWeight('bold').setBackground('#d9ead3');

  if (pairs.length > 0) {
    const lastDataRow = totalRow - 1;

    for (let col = 3; col <= headers.length; col++) {
      const colLetter = columnToLetter_(col);
      sheet.getRange(totalRow, col).setFormula('=SUM(' + colLetter + firstDataRow + ':' + colLetter + lastDataRow + ')');
    }
  }

  sheet.setFrozenRows(headerRow);
  sheet.setColumnWidth(1, 145);
  sheet.setColumnWidth(2, 280);
  sheet.setColumnWidths(3, headers.length - 2, 115);

  const dataRowCount = Math.max(pairs.length + 1, 1);
  sheet.getRange(firstDataRow, 3, dataRowCount, headers.length - 2).setNumberFormat('$#,##0.00;-$#,##0.00;');
  sheet.getRange(firstDataRow, 1, dataRowCount, headers.length).setBorder(true, true, true, true, true, true, '#d9ead3', SpreadsheetApp.BorderStyle.SOLID);
  sheet.getRange(firstDataRow, 3, Math.max(pairs.length, 1), 1).setBackground('#fff2cc');
  sheet.getRange(firstDataRow, 8, Math.max(pairs.length, 1), 1).setBackground('#fff2cc');
  sheet.getRange(firstDataRow, 13, Math.max(pairs.length, 1), 1).setBackground('#fff2cc');
  sheet.getRange(firstDataRow, 18, Math.max(pairs.length, 1), 1).setBackground('#fff2cc');

  applySummaryConditionalFormatting_(sheet, firstDataRow, Math.max(pairs.length, 1));
}

function ensureLabels_() {
  Object.keys(MABLE.labels).forEach(function (key) {
    const name = MABLE.labels[key];
    if (!GmailApp.getUserLabelByName(name)) {
      GmailApp.createLabel(name);
    }
  });
}

function getLabels_() {
  return {
    pending: GmailApp.getUserLabelByName(MABLE.labels.pending),
    processed: GmailApp.getUserLabelByName(MABLE.labels.processed),
    error: GmailApp.getUserLabelByName(MABLE.labels.error)
  };
}

function ensureSheets_() {
  getSheet_(MABLE.sheets.summary);
  getSheet_(MABLE.sheets.transactions);
  getSheet_(MABLE.sheets.invoices);
  getSheet_(MABLE.sheets.instructions);
  getSheet_(MABLE.sheets.setupLog);
  getSheet_(MABLE.sheets.debug);
}

function initialiseTransactionsSheet_() {
  const sheet = getSheet_(MABLE.sheets.transactions);
  const current = sheet.getRange(1, 1, 1, MABLE.transactionHeaders.length).getDisplayValues()[0];

  if (current.join('|') !== MABLE.transactionHeaders.join('|')) {
    sheet.clear();
    sheet.getRange(1, 1, 1, MABLE.transactionHeaders.length).setValues([MABLE.transactionHeaders]);
  }

  formatTransactionsSheet_();
}

function initialiseInvoicesSheet_() {
  const sheet = getSheet_(MABLE.sheets.invoices);
  const current = sheet.getRange(1, 1, 1, MABLE.invoiceHeaders.length).getDisplayValues()[0];

  if (current.join('|') !== MABLE.invoiceHeaders.join('|')) {
    sheet.clear();
    sheet.getRange(1, 1, 1, MABLE.invoiceHeaders.length).setValues([MABLE.invoiceHeaders]);
  }

  sheet.getRange(1, 1, 1, MABLE.invoiceHeaders.length).setFontWeight('bold').setBackground('#d9ead3').setWrap(true);
  sheet.setFrozenRows(1);
  sheet.setColumnWidths(1, MABLE.invoiceHeaders.length, 145);
  sheet.setColumnWidth(5, 330);
  sheet.setColumnWidth(14, 420);
  sheet.getRange('C:C').setNumberFormat('dd/mm/yyyy');
  sheet.getRange('D:D').setNumberFormat('$#,##0.00;-$#,##0.00;');
  sheet.getRange('H:I').setNumberFormat('dd/mm/yyyy hh:mm');
  sheet.getRange('K:K').setNumberFormat('$#,##0.00;-$#,##0.00;');
}

function initialiseInstructionsSheet_() {
  const sheet = getSheet_(MABLE.sheets.instructions);
  breakAllMerges_(sheet);
  sheet.clear();

  const rows = [
    ['Mable Invoice Tracker'],
    [''],
    ['Initial setup'],
    ['1. Create a Gemini API key in Google AI Studio: https://aistudio.google.com/app/apikey'],
    ['2. Paste this whole script into Extensions > Apps Script.'],
    ['3. Run initMableInvoiceTracker() once from the Apps Script editor.'],
    ['4. Authorise the script when Google asks.'],
    ['5. Reload the spreadsheet tab.'],
    ['6. Use Mable Tracker > Set Gemini API key and paste the key.'],
    ['7. Use Mable Tracker > Test Gemini configuration.'],
    ['8. Create the Gmail filter manually using the exact steps below.'],
    [''],
    ['Manual Gmail filter setup'],
    ['1. In Gmail, click the search options/sliders icon in the search bar.'],
    ['2. Put this in Subject: Mable - Copy of invoice'],
    ['3. Tick Has attachment.'],
    ['4. Click Create filter.'],
    ['5. Tick Apply the label and choose Mable Pending.'],
    ['6. Optionally tick Also apply filter to matching conversations if you want existing invoice emails queued.'],
    ['7. Click Create filter.'],
    [''],
    ['Normal operation'],
    ['Every 24 hours, the script finds Mable invoice emails, sends PDF attachments to Gemini for structured JSON extraction, writes Transactions, logs Invoices Processed, then moves the email thread to Mable Processed.'],
    ['Use Mable Tracker > Process pending Mable invoices now to run it manually.'],
    ['To retest one invoice email, use Mable Tracker > Reset one invoice for re-import, then run Mable Tracker > Process pending Mable invoices now.'],
    [''],
    ['Gemini extraction'],
    ['The script sends each PDF attachment to the Gemini API and requests fixed JSON containing invoice metadata and every transaction line item.'],
    ['The script validates that required fields exist, support item codes look valid, ABNs are 11 digits, and transaction totals match the invoice total.'],
    ['The script automatically chooses the highest available Gemini Pro model from the API model list, unless a MABLE_GEMINI_MODEL script property is set manually.'],
    [''],
    ['Summary'],
    ['Set budgets directly in the yellow budget columns on Summary. Rows are grouped by Support Item and the budget category returned from the invoice extraction.'],
    ['The budget category is the service category after the worker ABN, for example: Access Community Social and Rec Activ.'],
    ['The Support Item is the NDIS support code, for example: 04_104_0125_6_1.'],
    [''],
    ['Troubleshooting'],
    ['If Gemini extraction fails, the email is moved to Mable Error and the error is written to Invoices Processed.'],
    ['If Gemini returns invalid JSON, the raw response is written to PDF Text Debug.']
  ];

  sheet.getRange(1, 1, rows.length, 1).setValues(rows);
  sheet.getRange('A1').setFontWeight('bold').setFontSize(16).setBackground('#d9ead3');

  [3, 13, 22, 27, 32, 37].forEach(function (row) {
    sheet.getRange(row, 1).setFontWeight('bold').setBackground('#f3f6f4');
  });

  sheet.setColumnWidth(1, 1100);
  sheet.getRange(1, 1, rows.length, 1).setWrap(true).setVerticalAlignment('top');
}

function initialiseSetupLogSheet_() {
  const sheet = getSheet_(MABLE.sheets.setupLog);
  const headers = ['Timestamp', 'Message'];
  const current = sheet.getRange(1, 1, 1, headers.length).getDisplayValues()[0];

  if (current.join('|') !== headers.join('|')) {
    sheet.clear();
    sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
  }

  sheet.getRange(1, 1, 1, headers.length).setFontWeight('bold').setBackground('#d9ead3');
  sheet.setFrozenRows(1);
  sheet.setColumnWidth(1, 180);
  sheet.setColumnWidth(2, 1200);
  sheet.getRange('A:A').setNumberFormat('dd/mm/yyyy hh:mm:ss');
}

function installDailyTrigger_() {
  const triggers = ScriptApp.getProjectTriggers();

  triggers.forEach(function (trigger) {
    if (trigger.getHandlerFunction() === 'processPendingMableInvoices') {
      ScriptApp.deleteTrigger(trigger);
    }
  });

  ScriptApp.newTrigger('processPendingMableInvoices')
    .timeBased()
    .everyDays(1)
    .atHour(6)
    .create();
}

function extractInvoiceTableBody_(text) {
  const start = String(text || '').search(/Session start date/i);
  const payment = String(text || '').search(/Payment details/i);

  if (start !== -1 && payment !== -1 && payment > start) {
    return String(text || '').substring(start, payment);
  }

  if (start !== -1) {
    return String(text || '').substring(start);
  }

  return String(text || '');
}

function parseDescriptionParts_(description) {
  const clean = cleanDescription_(description);
  let provider = '';
  let providerAbn = '';
  let servicePart = clean;

  const abnMatch = clean.match(/^(.+?)\s+ABN\s+(\d{11})\s*-?\s*(.*)$/i);

  if (abnMatch) {
    provider = cleanCell_(abnMatch[1]);
    providerAbn = abnMatch[2];
    servicePart = cleanCell_(abnMatch[3]);
  } else {
    const looseAbnMatch = clean.match(/^(.+?)\s+ABN\s+(\d{11})(.*)$/i);

    if (looseAbnMatch) {
      provider = cleanCell_(looseAbnMatch[1]);
      providerAbn = looseAbnMatch[2];
      servicePart = cleanCell_(looseAbnMatch[3].replace(/^\s*-\s*/, ''));
    } else {
      const dashIndex = clean.indexOf(' - ');
      if (dashIndex !== -1) {
        provider = cleanCell_(clean.substring(0, dashIndex));
        servicePart = cleanCell_(clean.substring(dashIndex + 3));
      }
    }
  }

  const budgetCategory = extractBudgetCategory_(servicePart);

  return {
    provider: provider,
    providerAbn: providerAbn,
    budgetCategory: budgetCategory || servicePart || 'Uncategorised'
  };
}

function extractBudgetCategory_(servicePart) {
  const s = cleanCell_(servicePart);
  if (!s) return '';

  const patterns = [
    /\s+-\s+Standard\b/i,
    /\s+-\s+Weekday\b/i,
    /\s+-\s+Saturday\b/i,
    /\s+-\s+Sunday\b/i,
    /\s+-\s+Public Holiday\b/i,
    /\s+-\s+Monday\b/i,
    /\s+-\s+Tuesday\b/i,
    /\s+-\s+Wednesday\b/i,
    /\s+-\s+Thursday\b/i,
    /\s+-\s+Friday\b/i
  ];

  let cut = -1;

  patterns.forEach(function (pattern) {
    const match = s.match(pattern);
    if (match && (cut === -1 || match.index < cut)) {
      cut = match.index;
    }
  });

  if (cut !== -1) {
    return cleanCell_(s.substring(0, cut));
  }

  return cleanCell_(s.replace(/\s+-\s+[A-Z][a-z]+day\b.*$/, ''));
}

function normalisePdfText_(rawText) {
  return String(rawText || '')
    .replace(/[\uFFFC\uFFFD\uFFFE]/g, '-')
    .replace(/[–—]/g, '-')
    .replace(/\r/g, '\n')
    .replace(/\t/g, ' ')
    .replace(/ +\n/g, '\n')
    .replace(/\n +/g, '\n')
    .replace(/\n{3,}/g, '\n\n');
}

function cleanDescription_(value) {
  return cleanCell_(String(value || '')
    .replace(/[\uFFFC\uFFFD\uFFFE]/g, '-')
    .replace(/[–—]/g, '-')
    .replace(/\s+-\s+/g, ' - ')
    .replace(/\s+-/g, ' - ')
    .replace(/-\s+/g, ' - ')
    .replace(/\s+/g, ' '));
}

function cleanCell_(value) {
  return String(value || '')
    .replace(/\u00a0/g, ' ')
    .replace(/\s+/g, ' ')
    .trim();
}

function extractInvoiceNumberFromText_(text) {
  return firstMatch_(text, /Invoice Number\s+(\d+)/i) || firstMatch_(text, /Payment reference\s+Amount due\s+.*?\s(\d{6,})\s+\$/i);
}

function extractInvoiceNumberFromSubject_(subject) {
  return firstMatch_(String(subject || ''), /invoice\s*#\s*(\d+)/i) || firstMatch_(String(subject || ''), /Invoice\s+(\d{6,})/i);
}

function firstMatch_(text, regex) {
  const match = String(text || '').match(regex);
  return match ? String(match[1] || '').trim() : '';
}

function makeTransactionKey_(invoiceNumber, tx) {
  return [
    invoiceNumber,
    formatDateKey_(tx.sessionDate),
    tx.provider,
    tx.providerAbn,
    tx.supportItem,
    tx.quantity,
    tx.rate,
    tx.total,
    tx.description
  ].map(function (value) {
    return cleanCell_(value);
  }).join('|');
}

function readExistingTransactionKeys_() {
  const sheet = getSheet_(MABLE.sheets.transactions);
  const lastRow = sheet.getLastRow();
  const keys = {};

  if (lastRow < 2) return keys;

  const values = sheet.getRange(2, 21, lastRow - 1, 1).getDisplayValues();
  values.forEach(function (row) {
    if (row[0]) keys[row[0]] = true;
  });

  return keys;
}

function readProcessedInvoiceNumbers_() {
  const sheet = getSheet_(MABLE.sheets.invoices);
  const lastRow = sheet.getLastRow();
  const processed = {};

  if (lastRow < 2) return processed;

  const values = sheet.getRange(2, 1, lastRow - 1, 2).getDisplayValues();
  values.forEach(function (row) {
    const invoiceNumber = String(row[0] || '').trim();
    const status = String(row[1] || '').trim();

    if (invoiceNumber && status === 'Processed') {
      processed[invoiceNumber] = true;
    }
  });

  return processed;
}

function isInvoiceProcessed_(invoiceNumber) {
  if (!invoiceNumber) return false;
  return !!readProcessedInvoiceNumbers_()[String(invoiceNumber).trim()];
}

function upsertInvoiceLog_(entry) {
  const sheet = getSheet_(MABLE.sheets.invoices);
  const row = findInvoiceLogRow_(entry.invoiceNumber);
  const values = [[
    entry.invoiceNumber || '',
    entry.status || '',
    entry.invoiceDate || '',
    entry.amountDue === undefined ? '' : entry.amountDue,
    entry.subject || '',
    entry.attachmentName || '',
    entry.from || '',
    entry.messageDate || '',
    entry.processedAt || new Date(),
    entry.transactionsAdded === undefined ? '' : entry.transactionsAdded,
    entry.parsedTotal === undefined ? '' : entry.parsedTotal,
    entry.threadId || '',
    entry.messageId || '',
    entry.error || ''
  ]];

  if (row) {
    sheet.getRange(row, 1, 1, MABLE.invoiceHeaders.length).setValues(values);
  } else {
    sheet.getRange(sheet.getLastRow() + 1, 1, 1, MABLE.invoiceHeaders.length).setValues(values);
  }

  initialiseInvoicesSheet_();
}

function findInvoiceLogRow_(invoiceNumber) {
  if (!invoiceNumber) return null;

  const sheet = getSheet_(MABLE.sheets.invoices);
  const lastRow = sheet.getLastRow();

  if (lastRow < 2) return null;

  const values = sheet.getRange(2, 1, lastRow - 1, 1).getDisplayValues();
  const target = String(invoiceNumber).trim();

  for (let i = 0; i < values.length; i++) {
    if (String(values[i][0] || '').trim() === target) {
      return i + 2;
    }
  }

  return null;
}

function readSupportItemCategoryPairs_() {
  const sheet = getSheet_(MABLE.sheets.transactions);
  const lastRow = sheet.getLastRow();
  const map = {};

  if (lastRow < 2) return [];

  const values = sheet.getRange(2, 10, lastRow - 1, 2).getDisplayValues();

  values.forEach(function (row) {
    const category = cleanCell_(row[0]);
    const supportItem = cleanCell_(row[1]);
    if (!supportItem && !category) return;
    map[summaryBudgetKey_(supportItem, category)] = { supportItem: supportItem, budgetCategory: category };
  });

  return Object.keys(map).map(function (key) {
    return map[key];
  }).sort(function (a, b) {
    return (a.budgetCategory + '|' + a.supportItem).localeCompare(b.budgetCategory + '|' + b.supportItem);
  });
}

function readExistingSummaryBudgets_(sheet) {
  const budgets = {};
  const lastRow = sheet.getLastRow();

  if (lastRow < 6) return budgets;

  const values = sheet.getRange(6, 1, lastRow - 5, 18).getValues();

  values.forEach(function (row) {
    const supportItem = cleanCell_(row[0]);
    const budgetCategory = cleanCell_(row[1]);

    if (!supportItem && !budgetCategory) return;
    if (supportItem === 'TOTAL') return;

    budgets[summaryBudgetKey_(supportItem, budgetCategory)] = {
      weekly: row[2] || '',
      monthly: row[7] || '',
      quarterly: row[12] || '',
      annual: row[17] || ''
    };
  });

  return budgets;
}

function summaryBudgetKey_(supportItem, budgetCategory) {
  return cleanCell_(supportItem) + '|' + cleanCell_(budgetCategory);
}

function formatTransactionsSheet_() {
  const sheet = getSheet_(MABLE.sheets.transactions);
  sheet.getRange(1, 1, 1, MABLE.transactionHeaders.length).setFontWeight('bold').setBackground('#d9ead3').setWrap(true);
  sheet.setFrozenRows(1);
  sheet.setColumnWidths(1, MABLE.transactionHeaders.length, 135);
  sheet.setColumnWidth(9, 420);
  sheet.setColumnWidth(10, 280);
  sheet.setColumnWidth(11, 145);
  sheet.getRange('B:D').setNumberFormat('dd/mm/yyyy hh:mm');
  sheet.getRange('H:H').setNumberFormat('dd/mm/yyyy');
  sheet.getRange('L:L').setNumberFormat('0.00');
  sheet.getRange('M:Q').setNumberFormat('$#,##0.00;-$#,##0.00;');

  try {
    sheet.hideColumns(19, 3);
  } catch (err) {}
}

function sortTransactions_() {
  const sheet = getSheet_(MABLE.sheets.transactions);
  const lastRow = sheet.getLastRow();

  if (lastRow < 3) return;

  sheet.getRange(2, 1, lastRow - 1, MABLE.transactionHeaders.length).sort([
    { column: 8, ascending: false },
    { column: 1, ascending: false },
    { column: 6, ascending: true }
  ]);
}

function applySummaryConditionalFormatting_(sheet, firstDataRow, rowCount) {
  const rule = SpreadsheetApp.newConditionalFormatRule()
    .whenNumberLessThan(0)
    .setBackground('#f4cccc')
    .setRanges([
      sheet.getRange(firstDataRow, 7, rowCount, 1),
      sheet.getRange(firstDataRow, 12, rowCount, 1),
      sheet.getRange(firstDataRow, 17, rowCount, 1),
      sheet.getRange(firstDataRow, 22, rowCount, 1)
    ])
    .build();

  sheet.setConditionalFormatRules([rule]);
}

function formulaThisWeek_(row) {
  return sumFormula_(row, 'TODAY()-WEEKDAY(TODAY(),2)+1', 'TODAY()-WEEKDAY(TODAY(),2)+8');
}

function formulaPreviousWeek_(row) {
  return sumFormula_(row, 'TODAY()-WEEKDAY(TODAY(),2)-6', 'TODAY()-WEEKDAY(TODAY(),2)+1');
}

function formulaThisMonth_(row) {
  return sumFormula_(row, 'DATE(YEAR(TODAY()),MONTH(TODAY()),1)', 'EDATE(DATE(YEAR(TODAY()),MONTH(TODAY()),1),1)');
}

function formulaPreviousMonth_(row) {
  return sumFormula_(row, 'EDATE(DATE(YEAR(TODAY()),MONTH(TODAY()),1),-1)', 'DATE(YEAR(TODAY()),MONTH(TODAY()),1)');
}

function formulaThisQuarter_(row) {
  return sumFormula_(row, 'DATE(YEAR(TODAY()),3*INT((MONTH(TODAY())-1)/3)+1,1)', 'EDATE(DATE(YEAR(TODAY()),3*INT((MONTH(TODAY())-1)/3)+1,1),3)');
}

function formulaPreviousQuarter_(row) {
  return sumFormula_(row, 'EDATE(DATE(YEAR(TODAY()),3*INT((MONTH(TODAY())-1)/3)+1,1),-3)', 'DATE(YEAR(TODAY()),3*INT((MONTH(TODAY())-1)/3)+1,1)');
}

function formulaYtd_(row) {
  return sumFormula_(row, 'DATE(YEAR(TODAY()),1,1)', 'TODAY()+1');
}

function formulaPreviousYtd_(row) {
  return sumFormula_(row, 'DATE(YEAR(TODAY())-1,1,1)', 'DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))+1');
}

function formulaRolling12_(row) {
  return sumFormula_(row, 'EDATE(TODAY(),-12)', 'TODAY()+1');
}

function formulaPreviousRolling12_(row) {
  return sumFormula_(row, 'EDATE(TODAY(),-24)', 'EDATE(TODAY(),-12)');
}

function sumFormula_(row, startExpression, endExpression) {
  return '=IF($A' + row + '="","",SUMIFS(Transactions!$Q:$Q,Transactions!$K:$K,$A' + row + ',Transactions!$J:$J,$B' + row + ',Transactions!$H:$H,">="&(' + startExpression + '),Transactions!$H:$H,"<"&(' + endExpression + ')))';
}

function parseDate_(value) {
  if (value instanceof Date && !isNaN(value.getTime())) {
    return new Date(value.getFullYear(), value.getMonth(), value.getDate());
  }

  const s = String(value || '').trim();
  const match = s.match(/^(\d{1,2})\/(\d{1,2})\/(\d{4})$/);

  if (!match) return '';

  return new Date(Number(match[3]), Number(match[2]) - 1, Number(match[1]));
}

function parseMoney_(value) {
  if (value === '' || value === null || value === undefined) return 0;
  if (typeof value === 'number') return value;

  const s = String(value).replace(/,/g, '').replace(/[^0-9.\-]/g, '');
  if (!s || s === '-' || s === '.') return 0;

  return Number(s) || 0;
}

function parseNumber_(value) {
  if (value === '' || value === null || value === undefined) return 0;
  if (typeof value === 'number') return value;

  return Number(String(value).replace(/,/g, '').trim()) || 0;
}

function formatDateKey_(date) {
  if (!(date instanceof Date) || isNaN(date.getTime())) return cleanCell_(date);
  return Utilities.formatDate(date, Session.getScriptTimeZone(), 'yyyy-MM-dd');
}

function formatDateForGeminiKey_(date) {
  if (!(date instanceof Date) || isNaN(date.getTime())) return '';
  return Utilities.formatDate(date, Session.getScriptTimeZone(), 'dd/MM/yyyy');
}

function threadHasLabel_(thread, labelName) {
  return thread.getLabels().some(function (label) {
    return label.getName() === labelName;
  });
}

function getSpreadsheet_() {
  const id = PropertiesService.getScriptProperties().getProperty(MABLE.properties.spreadsheetId);
  if (id) return SpreadsheetApp.openById(id);
  return SpreadsheetApp.getActiveSpreadsheet();
}

function getSheet_(name) {
  const ss = getSpreadsheet_();
  return ss.getSheetByName(name) || ss.insertSheet(name);
}

function logSetup_(message) {
  try {
    const sheet = getSheet_(MABLE.sheets.setupLog);
    if (sheet.getLastRow() === 0) {
      sheet.getRange(1, 1, 1, 2).setValues([['Timestamp', 'Message']]);
    }
    sheet.appendRow([new Date(), String(message || '')]);
  } catch (err) {}
}

function writeDebugText_(invoiceNumber, text) {
  const sheet = getSheet_(MABLE.sheets.debug);

  if (sheet.getLastRow() === 0) {
    sheet.getRange(1, 1, 1, 3).setValues([['Timestamp', 'Invoice Number', 'Debug Text']]);
  }

  sheet.appendRow([new Date(), invoiceNumber || '', String(text || '').substring(0, 45000)]);
  sheet.setColumnWidth(1, 180);
  sheet.setColumnWidth(2, 160);
  sheet.setColumnWidth(3, 1000);
  sheet.getRange('C:C').setWrap(true);
}

function breakAllMerges_(sheet) {
  try {
    sheet.getDataRange().breakApart();
  } catch (err) {}
}

function removeOldCsvConfigSheets_() {
  const ss = getSpreadsheet_();
  const config = ss.getSheetByName('Config');

  if (config) {
    const a1 = config.getRange('A1').getDisplayValue();
    const c1 = config.getRange('C1').getDisplayValue();

    if (a1 === 'Categories' && c1 === 'Provider' && ss.getSheets().length > 1) {
      ss.deleteSheet(config);
    }
  }

  const importLog = ss.getSheetByName('Import Log');

  if (importLog) {
    const a1 = importLog.getRange('A1').getDisplayValue();

    if (a1 === 'Import ID' && ss.getSheets().length > 1) {
      ss.deleteSheet(importLog);
    }
  }
}

function columnToLetter_(column) {
  let temp = '';
  let letter = '';

  while (column > 0) {
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = (column - temp - 1) / 26;
  }

  return letter;
}