3-main.gs

/***********************************************
 * Steps:
 * 1. Fetch unread emails and filter them for processing based on the search query.
 * 2. Skip already processed emails unless overridden in the configuration (RERUN_READ_MAILS).
 * 3. Extract email data and determine the source (email or SMS).
 * 4. Identify the applicable rules for parsing transaction details or handle duplicate emails.
 * 5. Sanitize the email body/SMS content and extract key transaction details:
 *    - Date, accounts (from/to), amount, type (debit/credit), merchant, and metadata.
 * 6. Validate mandatory fields (date, amount, category, account) and classify transactions:
 *    - Debit, credit, or transfer.
 * 7. Handle "Transfer" transactions by creating corresponding debit and credit entries.
 * 8. Prepare transaction data for appending to the Google Sheet.
 * 9. Append successfully processed transactions to the spreadsheet.
 * 10. Label processed emails and update their status as read.
 * 11. Generate a summary URL and send an email with the transaction details.
 ***********************************************/

function processTransactionEmails(e) {
  try {
    // Step 1: Fetch unread emails and filter them for processing
    const spreadsheet = SpreadsheetApp.openById(CONFIG.SPREADSHEET_ID);
    transactionSheet = spreadsheet.getSheetByName(DEV_CONFIG.OUTPUT_SHEET_TITLE);

    const emailThreads = getEmailThreads(e);
    Logger.log('[Info] Number of email threads found: ' + emailThreads.length);

    const transactions = []; // Holds all processed transactions
    const metadata = []; // Holds source and warnings

    // Get Label;
    const labelRequestSuccess = getLabelRequest(LABELS.PROCESSED);
    const labelRequestFailed = getLabelRequest(LABELS.TO_FIX);

    // ⚡ PERFORMANCE FIX: Fetch existing rows ONCE before processing emails (moved from inside loop)
    // This changes performance from O(n²) to O(n) where n is the number of emails
    const existingRows = transactionSheet.getLastRow() > 1 ?
        transactionSheet.getRange(2, 1, transactionSheet.getLastRow() - 1, 15)
        .getValues().filter(row => row.some(cell => cell)) : []; // Exclude empty cells

    // Each thread can have multiple emails. Each email will have one transaction related info.
    emailThreads.forEach(function (thread) {
      // TODO: Add bulk SMS sheet mode. For it abstract all thread. and email. calls
      // Emails in each thread
      var emails = thread.getMessages();
      emails.forEach(function (email) {
        // Step 2: Skip already processed emails unless overridden in config
        if (email.isUnread() || DEV_CONFIG.RERUN_READ_MAILS) {
          ProcessedCount.TOTAL++;

          // Step 3: Extract email data and determine the source (email or SMS).
          populateEmailData(email);
          const { emailId, emailSubject } = emailData;  // These are used multiple times below.
          Logger.log(`[START] Processing email ID: ${emailId}, Subject: ${emailSubject}`);

          if (isEmailAlreadyProcessed()) {
            Logger.log(`[SKIP] ${emailId} is Already processed. ${emailSubject}`);
            ProcessedCount.SKIPPED++;
            return;
          }

          // Logger.log(`[INFO] messageBody : ${emailData.messageBody}`);
          currentTransactionWarnings = [];

          // Step 4: Identify the applicable rules for parsing transaction details or handle duplicate emails.
          const applicableRegexMap = getApplicableRegexMap(emailData.isSMS);
          if (!applicableRegexMap) {
            logError(ErrorType.NO_RULE, `(${emailData.source}) No ruleId found for ${emailSubject}`, true); // Error
            createFailureRecord("No applicable rule found.");
            labelEmail(labelRequestFailed, email);
            return;
          }
          if (applicableRegexMap == CONSTANTS.DUPLICATE) return;

          const regexMap = combineWithDefaultRegexMap(applicableRegexMap);

          // Step 5: Sanitize the email body/SMS content and extract key transaction details:
          //          - Date, accounts (from/to), amount, type (debit/credit), merchant, and metadata.
          var sanitizedText = cleanEmailBody(emailData.messageBody);
          const transactionDate = extractTransactionDate(sanitizedText, regexMap.dateRegex, emailData.emailDate);
          const fromAccount = getAccountName(extractFullMatchOrFirstCaptureGroupString(sanitizedText, regexMap.fromAccountRegex), sanitizedText);
          // For toAccount: resolve via accountKeywordMap first, fall back to raw extracted value if not found.
          // If toAccountRegex is a static string (e.g., "Cash"), it's used as the final account name directly.
          // If regex extracts an account number not in our accountKeywordMap (e.g., someone else's account), toAccount becomes null -> treated as normal txn.
          const rawToAccount = extractFullMatchOrFirstCaptureGroupString(sanitizedText, regexMap.toAccountRegex);
          const isStaticToAccount = regexMap.toAccountRegex && !(regexMap.toAccountRegex instanceof RegExp);
          const toAccount = isStaticToAccount ? rawToAccount : getAccountName(rawToAccount, sanitizedText, false);
          const transactionAmount = getTransactionAmount(sanitizedText, regexMap.amountRegex); // Remove everything except digits, decimal & - sign
          const isDebit = isDebitTransaction(sanitizedText, regexMap.isDebit, toAccount, transactionAmount); // Update to isIncome ?

          const transactionType = getTransactionType(toAccount, isDebit);
          const merchant = extractFullMatchOrFirstCaptureGroupString(sanitizedText, regexMap.merchantRegex);
          const notes = extractNotes(sanitizedText, regexMap.notesRegex);
          var {categoryType, category, subcategory} = getTransactionCategory(extractFullMatchOrCaptureGroupsArray(sanitizedText, regexMap.categoryRegex), sanitizedText, isDebit);
          subcategory = getTransactionSubcategory(sanitizedText, regexMap.subcategoryRegex, subcategory);
          // If toAccountRegex extracted data but toAccount resolved to null (unknown destination),
          // the transaction is treated as normal debit, not transfer. Reset Balance Correction to default.
          if (rawToAccount && !toAccount && category === "Balance Correction") {
            category = isDebit ? USER_DEFAULTS.EXPENSE_CATEGORY : USER_DEFAULTS.INCOME_CATEGORY;
            subcategory = isDebit ? USER_DEFAULTS.EXPENSE_SUBCATEGORY : USER_DEFAULTS.INCOME_SUBCATEGORY;
          }

          const adjustedAmount = isDebit ? -transactionAmount : transactionAmount;
          const rawTitle = generateTitle(merchant, sanitizedText);
          const title = normalizeTitle(rawTitle, notes);

          // Step 6: Validate mandatory fields and classify transactions: Debit, credit, or transfer.
          const isTransfer = transactionType === TransactionType.TRANSFER;
          // For transfers, category is not mandatory (Cashew auto-assigns Balance Correction)
          const mandatoryFields = getMandatoryFields(transactionDate, transactionAmount, isTransfer ? "Balance Correction" : category, fromAccount);
          if (!validateMandatoryFields(mandatoryFields, email, labelRequestFailed)) return;

          // Step 7: Build payload -- branch for Transfer vs normal transaction
          var transactionPayload, transferPayload, singleUrl;
          const formattedDate = moment(transactionDate).format(DATE_FORMATS.CASHEW_FORMAT);

          // Resolve budget from subcategory (specific) then category (broad)
          const budget = categoryToBudgetMap[subcategory] || categoryToBudgetMap[category] || null;

          if (isTransfer) {
            // Transfer: build Cashew transfer payload for /addTransferRoute
            transferPayload = filterPayload({
              amountFrom: transactionAmount,  // Always positive
              walletFrom: fromAccount,
              walletTo: toAccount,
              title: title,
              date: formattedDate,
            });
            singleUrl = createSingleTransferUrl(transferPayload);

            // Also build a standard payload for the sheet row and duplicate check
            transactionPayload = filterPayload({
              amount: adjustedAmount,
              title,
              notes: toAccount ? `-> ${toAccount}${notes ? " | " + notes : ""}` : notes,
              date: formattedDate,
              category: category || "Balance Correction",
              subcategory: subcategory || "",
              account: fromAccount
            });
          } else {
            // Normal transaction
            transactionPayload = filterPayload({
              amount: adjustedAmount,
              title,
              notes,
              date: formattedDate,
              category,
              subcategory,
              account: fromAccount,
              categoryPolarity: false,
              budget,
            });
            singleUrl = createSingleTransactionUrl(transactionPayload);
          }

          // Check for duplicate
          const isDuplicate = isDuplicateTransaction(transactionPayload, existingRows, ProcessedCount.TOTAL, emailData.source);
          var warnings = currentTransactionWarnings.join("; ");
          transactions.push({...transactionPayload});
          var metadataEntry = {
            source: emailData.source,
            warnings: warnings,
            txnType: isDuplicate ? TransactionType.DUPLICATE
                   : isTransfer ? TransactionType.TRANSFER
                   : (isDebit ? TransactionType.DEBIT : TransactionType.CREDIT),
          };
          if (isTransfer) metadataEntry.transferPayload = transferPayload;
          metadata.push(metadataEntry);

          // Step 8: Prepare transaction data for appending to the spreadsheet
          var rowData = [
            isDuplicate ? CONSTANTS.DUPLICATE : CONSTANTS.SUCCESS_STATUS,
            moment(transactionDate).format(DATE_FORMATS.DISPLAY.DATETIME),
            `${adjustedAmount}`,
            transactionType,
            fromAccount,
            category || "",
            subcategory || "",
            merchant || "",
            title || "",
            transactionPayload.notes || notes || "",
            warnings,
            emailId,
            getEmailThreadLink(),
            singleUrl,
            emailSubject,
            getToday()
          ];

          // Step 9: Append successfully processed transactions to the Google Sheet
          Logger.log(`[${rowData[0]}] Appending to Google Sheet: ${rowData}`);
          transactionSheet.appendRow(rowData);

          // ⚡ PERFORMANCE FIX: Update in-memory existingRows array to keep it synchronized
          // This ensures duplicate detection works correctly for subsequent emails in the same batch
          existingRows.push(rowData.slice(0, 15)); // Push all 15 columns to match the structure

          isDuplicate ? ProcessedCount.SKIPPED++ : ProcessedCount.SUCCESS++;

          // Step 10: Label processed emails and update their status
          labelEmail(labelRequestSuccess, email);
        }
      });
    });

    // Step 11: Generate a summary URL and send an email with the transaction details.
    printProcessingSummary();
    if (transactions.length > 0) {
      // Separate normal transactions from transfers (by metadata index)
      // Transfers are identified by the presence of transferPayload (even if duplicate)
      const normalIndices = [], transferIndices = [];
      metadata.forEach((m, i) => {
        if (m.transferPayload) transferIndices.push(i);
        else normalIndices.push(i);
      });

      const normalTxns = normalIndices.map(i => transactions[i]);
      const normalMeta = normalIndices.map(i => metadata[i]);

      // Normal transactions: Approve URL excludes duplicates, Edit URL includes all
      const nonDupNormalTxns = normalTxns.filter((_, i) => normalMeta[i].txnType !== TransactionType.DUPLICATE);
      var finalTransactionUrl = nonDupNormalTxns.length > 0 ? createFinalTransactionUrl({transactions: nonDupNormalTxns}) : null;
      var editTransactionUrl = normalTxns.length > 0 ? createFinalTransactionUrl({transactions: normalTxns}) : null;

      sendSuccessEmail(finalTransactionUrl, editTransactionUrl, transactions, metadata, normalIndices, transferIndices)
      return {ProcessedCount, transactions, metadata};  // Response for /dev & /exec
    }
  } catch (e) {
    Logger.log("[ERROR] Unhandled error in mainFunction: " + e.message);
    Logger.log(e.stack); // Logs full stack trace
    throw e; // Re-throw the error after logging
  }
}