Code.gs

/**
 * TransacFlow - Flow your transactions from Gmail to Sheets
 * 
 * ⚠️ SETUP REQUIRED:
 * 1. Update Config.gs with your Sheet ID
 * 2. Customize BankPatterns.gs if needed
 * 3. Run setupAutomation() to start
 * 
 * ⚠️ DO NOT EDIT THIS FILE (unless you know what you're doing)
 * → All configurations are in Config.gs
 * → Bank patterns are in BankPatterns.gs
 * 
 * 📋 MAIN FUNCTIONS (Run these):
 * - setupAutomation()           → Initial setup & create triggers
 * - processTransactionEmails()  → Manual run (also auto-runs every 10 min)
 * - refreshAllNicknames()       → Update all transactions with latest nicknames
 * - updateDashboard()           → Create/refresh analytics dashboard
 * - addCategoriesToTransactions() → Add categories to existing transactions
 * 
 * 🧪 TESTING FUNCTIONS:
 * - test_SingleEmail()          → Test parsing of one email
 * - test_WriteToSheet()         → Test parsing + writing to sheet
 * 
 * 🔍 DIAGNOSTIC FUNCTIONS:
 * - debug_SearchQuery()         → Show current search query
 * - debug_EmailSearch()         → Diagnose email search issues
 * - debug_ShowRawEmail()        → Show raw email content for pattern debugging
 * - debug_DashboardData()       → Troubleshoot dashboard zero values
 * 
 * ℹ️ ACCOUNT NICKNAMES:
 * - Managed in "Account Nicknames" sheet (auto-created)
 * - Edit "Custom Nickname" column to customize names
 * - Changes automatically update all transactions!
 * 
 * 📊 ANALYTICS DASHBOARD:
 * - Run updateDashboard() to create comprehensive analytics
 * - Includes: Monthly summaries, top merchants, category breakdown, trends, budget tracking
 * - Categories are auto-assigned based on merchant names
 * 
 * @version 1.2.0
 * @author Aakash Goel
 * @license MIT
 * @repository https://github.com/alpha-gamma/transacflow
 */

// ==================== UTILITIES ====================

/**
 * Logger class for centralized logging
 */
class Logger {
  constructor(logLevel = 'INFO') {
    this.logLevel = logLevel;
    this.levels = { 'DEBUG': 0, 'INFO': 1, 'WARN': 2, 'ERROR': 3 };
  }
  
  shouldLog(level) {
    return this.levels[level] >= this.levels[this.logLevel];
  }
  
  formatMessage(level, message) {
    const timestamp = new Date().toISOString();
    return `[${timestamp}] [${level}] ${message}`;
  }
  
  debug(message) {
    if (this.shouldLog('DEBUG')) console.log(this.formatMessage('DEBUG', message));
  }
  
  info(message) {
    if (this.shouldLog('INFO')) console.log(this.formatMessage('INFO', message));
  }
  
  warn(message) {
    if (this.shouldLog('WARN')) console.warn(this.formatMessage('WARN', message));
  }
  
  error(message) {
    if (this.shouldLog('ERROR')) console.error(this.formatMessage('ERROR', message));
  }
  
  success(message) {
    console.log(this.formatMessage('SUCCESS', `✓ ${message}`));
  }
  
  section(title) {
    if (this.shouldLog('INFO')) {
      console.log('\n' + '='.repeat(50));
      console.log(`  ${title}`);
      console.log('='.repeat(50) + '\n');
    }
  }
  
  object(label, obj) {
    if (this.shouldLog('DEBUG')) {
      console.log(`${label}:`);
      console.log(JSON.stringify(obj, null, 2));
    }
  }
}

/**
 * TextUtils class for text processing
 */
class TextUtils {
  static normalizeAmount(amountStr) {
    if (!amountStr) return null;
    try {
      // Get currency format from config
      const currencyFormat = getCurrencyFormat();
      
      let cleaned = amountStr;
      
      // Remove currency symbols based on configuration
      currencyFormat.symbols.forEach(symbol => {
        // Escape special regex characters
        const escapedSymbol = symbol.replace(/[.*+?^${}()|[\]\\]/g, '\\$&');
        cleaned = cleaned.replace(new RegExp(escapedSymbol, 'gi'), '');
      });
      
      // Remove thousands separator
      cleaned = cleaned.replace(new RegExp('\\' + currencyFormat.thousandsSeparator, 'g'), '');
      
      // Remove whitespace
      cleaned = cleaned.replace(/\s+/g, '').trim();
      
      const amount = parseFloat(cleaned);
      return isNaN(amount) ? null : amount.toFixed(2);
    } catch (error) {
      console.error(`Error normalizing amount: ${error.message}`);
      return null;
    }
  }
  
  static cleanText(text) {
    if (!text) return null;
    return text
      .replace(/[\r\n]+/g, ' ')
      .replace(/\s{2,}/g, ' ')
      .replace(/\t+/g, ' ')
      .trim();
  }
  
  static truncate(text, maxLength = 100) {
    if (!text || text.length <= maxLength) return text;
    return text.substring(0, maxLength - 3) + '...';
  }
}

/**
 * DateUtils class for date processing
 */
class DateUtils {
  static parseDate(dateStr, format = null) {
    if (!dateStr) return null;
    
    try {
      let cleanDateStr = dateStr.replace(/\s*(IST|GMT|UTC|EST|PST)\s*$/i, '').trim();
      
      // If format is specified, use format-specific parser
      if (format) {
        let date = null;
        
        if (format === 'DD-MM-YYYY') {
          // Try DD-MM-YYYY formats and also DD MMM, YYYY format
          date = DateUtils.parseDDMMYYYYComma(cleanDateStr) || 
                 DateUtils.parseDDMMYYYY(cleanDateStr) ||
                 DateUtils.parseDDMMMYYYY(cleanDateStr);
        } else if (format === 'DD-MM-YY') {
          // Try DD-MM-YY format (2-digit year)
          date = DateUtils.parseDDMMYY(cleanDateStr);
        } else if (format === 'DD-MMM-YYYY' || format === 'DD MMM YYYY') {
          date = DateUtils.parseDDMMMYYYY(cleanDateStr);
        } else if (format === 'MMM DD, YYYY') {
          // Try MMM DD, YYYY format (e.g., Nov 24, 2025)
          date = DateUtils.parseMMMDDYYYY(cleanDateStr);
        }
        
        if (date && !isNaN(date.getTime())) return date;
      }
      
      // Fallback: Try standard Date() constructor
      let date = new Date(cleanDateStr);
      if (!isNaN(date.getTime())) return date;
      
      // Fallback: Try all parsers
      const parsers = [
        DateUtils.parseDDMMYYYYComma,
        DateUtils.parseDDMMYYYY,
        DateUtils.parseDDMMMYYYY
      ];
      
      for (const parser of parsers) {
        date = parser(cleanDateStr);
        if (date && !isNaN(date.getTime())) return date;
      }
      
      console.warn(`Could not parse date: ${dateStr}, using current time`);
      return new Date();
      
    } catch (error) {
      console.error(`Date parsing error: ${error}`);
      return new Date();
    }
  }
  
  static parseDDMMYYYYComma(dateStr) {
    // DD-MM-YYYY format (e.g., 09-11-2025 = 9 Nov 2025)
    const match = dateStr.match(/(\d{1,2})[\/\-](\d{1,2})[\/\-](\d{4}),?\s*(\d{1,2}):(\d{2}):?(\d{2})?/);
    if (match) {
      const day = parseInt(match[1]);
      const month = parseInt(match[2]);
      const year = parseInt(match[3]);
      const hour = parseInt(match[4]);
      const minute = parseInt(match[5]);
      const second = match[6] ? parseInt(match[6]) : 0;
      
      // JavaScript Date constructor: new Date(year, monthIndex, day, ...)
      // monthIndex is 0-based: 0=Jan, 1=Feb, ..., 11=Dec
      return new Date(year, month - 1, day, hour, minute, second);
    }
    return null;
  }
  
  static parseDDMMYYYY(dateStr) {
    // DD-MM-YYYY format with optional am/pm
    // Handles: "22-11-2025 03:05:35 pm" or "22-11-2025 15:05:35"
    const match = dateStr.match(/(\d{1,2})[\/\-](\d{1,2})[\/\-](\d{4})\s+(\d{1,2}):(\d{2}):?(\d{2})?\s*([ap]m)?/i);
    if (match) {
      const day = parseInt(match[1]);
      const month = parseInt(match[2]);
      const year = parseInt(match[3]);
      let hour = parseInt(match[4]);
      const minute = parseInt(match[5]);
      const second = match[6] ? parseInt(match[6]) : 0;
      const meridiem = match[7] ? match[7].toLowerCase() : null;
      
      // Convert to 24-hour format if am/pm is present
      if (meridiem) {
        if (meridiem === 'pm' && hour < 12) {
          hour += 12;
        } else if (meridiem === 'am' && hour === 12) {
          hour = 0;
        }
      }
      
      return new Date(year, month - 1, day, hour, minute, second);
    }
    return null;
  }
  
  static parseDDMMYY(dateStr) {
    // DD-MM-YY format (2-digit year, e.g., 16-11-25 = 16 Nov 2025)
    const match = dateStr.match(/(\d{1,2})[\/\-](\d{1,2})[\/\-](\d{2})$/);
    if (match) {
      const day = parseInt(match[1]);
      const month = parseInt(match[2]);
      let year = parseInt(match[3]);
      
      // Convert 2-digit year to 4-digit
      // Assume 00-49 = 2000-2049, 50-99 = 1950-1999
      year = year < 50 ? 2000 + year : 1900 + year;
      
      return new Date(year, month - 1, day);
    }
    return null;
  }
  
  static parseDDMMMYYYY(dateStr) {
    // Format 1: DD-MMM-YYYY (e.g., 01-Dec-2025 14:30:45)
    let match = dateStr.match(/(\d{1,2})[\/\-]([A-Za-z]{3})[\/\-](\d{4})\s*(\d{1,2}):(\d{2}):?(\d{2})?/);
    if (match) {
      const [, day, monthName, year, hour, minute, second] = match;
      const date = new Date(`${monthName} ${day}, ${year} ${hour}:${minute}:${second || '00'}`);
      return !isNaN(date.getTime()) ? date : null;
    }
    
    // Format 2: DD MMM, YYYY at HH:MM:SS (e.g., 26 Nov, 2025 at 12:01:34)
    match = dateStr.match(/(\d{1,2})\s+([A-Za-z]{3}),\s+(\d{4})\s+at\s+(\d{1,2}):(\d{2}):?(\d{2})?/);
    if (match) {
      const [, day, monthName, year, hour, minute, second] = match;
      const date = new Date(`${monthName} ${day}, ${year} ${hour}:${minute}:${second || '00'}`);
      return !isNaN(date.getTime()) ? date : null;
    }
    
    return null;
  }
  
  static parseMMMDDYYYY(dateStr) {
    // Format: MMM DD, YYYY HH:MM:SS (e.g., Nov 24, 2025 07:05:19)
    // Also handles: MMM DD, YYYY at HH:MM:SS
    const match = dateStr.match(/([A-Za-z]{3})\s+(\d{1,2}),\s+(\d{4})(?:\s+at)?\s+(\d{1,2}):(\d{2}):?(\d{2})?/);
    if (match) {
      const [, monthName, day, year, hour, minute, second] = match;
      const date = new Date(`${monthName} ${day}, ${year} ${hour}:${minute}:${second || '00'}`);
      return !isNaN(date.getTime()) ? date : null;
    }
    
    return null;
  }
}

// ==================== BUILT-IN BANK PATTERNS ====================

/**
 * Manager for bank patterns (built-in + custom)
 * Encapsulates pattern loading logic
 */
class BankPatternManager {
  static getDefaultPatterns() {
    return [
      // ============================================
      // HDFC BANK
      // ============================================
      {
        name: 'HDFC Bank UPI - Debit Account',
        senderPattern: 'alerts@hdfcbank\\.net',
        subjectPattern: 'You have done a UPI txn',
        bodyPattern: 'has been debited from account \\d{4} to',
        
        amountPattern: 'Rs\\.\\s*([\\d,]+\\.?\\d*)\\s+has been debited from account',
        accountPattern: 'from\\s+account\\s+(\\d{4})',
        // Updated to handle hyphens in VPA (e.g., 9876543210-1@upibank)
        merchantPattern: 'to\\s+(?:VPA\\s+)?[\\w.-]+@\\w+\\s+([A-Za-z0-9][A-Za-z0-9\\s&\\.\\-\\*]+?)\\s+on',
        datePattern: 'on\\s+(\\d{2}[/-]\\d{2}[/-]\\d{2})',
        dateFormat: 'DD-MM-YY',
      },
      {
        name: 'HDFC Bank UPI - RuPay Credit Card',
        senderPattern: 'alerts@hdfcbank\\.net',
        subjectPattern: 'You have done a UPI txn',
        bodyPattern: 'has been debited from your HDFC Bank RuPay Credit Card',
        
        amountPattern: 'Rs\\.\\s*([\\d,]+\\.?\\d*)\\s+has been debited from your HDFC Bank RuPay Credit Card',
        accountPattern: 'from\\s+your\\s+HDFC\\s+Bank\\s+RuPay\\s+Credit\\s+Card\\s+(XX\\d{4})',
        merchantPattern: 'to\\s+[\\w.]+@\\w+\\s+([A-Za-z0-9][A-Za-z0-9\\s&\\.\\-\\*]+?)\\s+on',
        datePattern: 'on\\s+(\\d{2}[/-]\\d{2}[/-]\\d{2})',
        dateFormat: 'DD-MM-YY',
      },
      {
        name: 'HDFC Bank Credit Card',
        senderPattern: 'alerts@hdfcbank\\.net',
        subjectPattern: 'debited via Credit Card|Update on your HDFC Bank Credit Card',
        bodyPattern: 'Credit Card ending \\d{4}',
        
        amountPattern: '(?:for\\s+)?Rs\\.?\\s*([\\d,]+\\.?\\d*)',
        accountPattern: '[Cc](?:redit\\s+)?[Cc]ard\\s+ending\\s+(\\d{4})',
        merchantPattern: '(?:at\\s+|towards\\s+)([A-Za-z0-9][A-Za-z0-9\\s&\\.\\-\\*]+?)\\s+on',
        datePattern: 'on\\s+(\\d{2}[/-]\\d{2}[/-]\\d{4}\\s+\\d{2}:\\d{2}:\\d{2}|\\d{2}\\s+\\w{3},\\s+\\d{4}\\s+at\\s+\\d{2}:\\d{2}:\\d{2})',
        dateFormat: 'DD-MM-YYYY',
      },
      
      // ============================================
      // AXIS BANK - Credit Card
      // ============================================
      {
        name: 'Axis Bank Credit Card',
        senderPattern: 'alerts@axisbank\\.com',
        subjectPattern: 'spent on credit card|Transaction alert on Axis Bank Credit Card',
        bodyPattern: 'summary of your Axis Bank Credit Card Transaction|Thank you for using your Card|Thank you for using your credit card',
        
        amountPattern: '(?:Transaction\\s+Amount:\\s*|for\\s+)?(?:INR|Rs\\.?|₹)\\s*([\\d,]+\\.?\\d*)',
        accountPattern: '(?:(?:Axis\\s+Bank\\s+)?[Cc]redit\\s+)?[Cc]ard\\s+(?:[Nn]o\\.?|ending)\\s*(XX\\d{4})',
        merchantPattern: '(?:Merchant\\s+Name:\\s*|at\\s+)([A-Za-z0-9][A-Za-z0-9\\s&\\.\\-]+?)(?:[\\r\\n]|\\s{2,}|\\s+on)',
        datePattern: '(?:Date\\s+&\\s+Time:\\s*|on\\s+)(\\d{2}[/-]\\d{2}[/-]\\d{4})[,\\s]+(\\d{2}:\\d{2}(?::\\d{2})?)(?:\\s+IST)?',
        dateFormat: 'DD-MM-YYYY',
      },

      // ============================================
      // INDUSIND BANK - Credit Card
      // ============================================
      {
        name: 'IndusInd Bank Credit Card',
        senderPattern: 'transactionalert@indusind\\.com',
        subjectPattern: 'Transaction Alert - IndusInd Bank Credit Card',
        bodyPattern: 'The transaction on your IndusInd Bank Credit Card',
        
        // "for INR 3,275.00 on" - handle commas and optional decimals
        amountPattern: 'for\\s+INR\\s+([\\d,]+(?:\\.\\d{2})?)\\s+on',
        
        // "ending 1234" - will be auto-prefixed with XX
        accountPattern: 'ending\\s+(\\d{4})',
        
        // "at Merchant Name is Approved" - match until " is "
        merchantPattern: 'at\\s+([A-Za-z0-9][^\\r\\n]+?)\\s+is\\s+Approved',
        
        // "on 22-11-2025 03:05:35 pm at"
        // Captures both date and time with am/pm
        datePattern: 'on\\s+(\\d{2}-\\d{2}-\\d{4})\\s+(\\d{2}:\\d{2}:\\d{2}\\s+[ap]m)',
        
        dateFormat: 'DD-MM-YYYY',
      },

      // ============================================
      // ICICI BANK - Credit Card
      // ============================================
      {
        name: 'ICICI Bank Credit Card',
        senderPattern: 'credit_cards@icicibank\\.com',
        subjectPattern: 'Transaction alert for your ICICI Bank Credit Card',
        bodyPattern: 'Your ICICI Bank Credit Card',
        
        // "INR 2,460.00" or "INR 2.00" - handle commas and decimals
        amountPattern: 'INR\\s+([\\d,]+\\.\\d{2})',
        
        // "XX1234" or "XX5678" - already includes XX prefix
        accountPattern: '(XX\\d{4})',
        
        // "Info: AMAZON PAY IN UTILITY" or "Info: UPI-123456789012-MERCHANT NAME"
        // Match everything after "Info: " until period or end of line
        merchantPattern: 'Info:\\s+([A-Za-z0-9\\s\\-*]+?)(?:\\.|$)',
        
        // "on Nov 24, 2025 at 07:05:19" or "on Sep 19, 2025 at 08:14:10"
        // Captures month abbr, day, year and time (24-hour format)
        datePattern: 'on\\s+([A-Z][a-z]{2}\\s+\\d{2},\\s+\\d{4})\\s+at\\s+(\\d{2}:\\d{2}:\\d{2})',
        
        dateFormat: 'MMM DD, YYYY',
      },

      // ============================================
      // OneCard - Credit Card
      // ============================================
      {
        name: 'OneCard Credit Card',
        senderPattern: 'no-reply@getonecard\\.app',
        subjectPattern: 'Payment update on your Federal One credit card',
        bodyPattern: 'Your Federal One Credit Card ending',
        
        // "*Amount:* INR 230.46" or "Amount: INR 2,071.00"
        // Handles with/without asterisks, commas, and decimals
        amountPattern: '\\*?Amount[:\\s*]+INR[\\s]+([\\d,]+(?:\\.\\d{2})?)',
        
        // "ending in 1234" - will be auto-prefixed with XX
        accountPattern: 'ending in\\s+(\\d{4})',
        
        // "*Merchant:* MERCHANT.COM" or "Merchant: STORE* TRANSACTION123"
        // Handles asterisks, alphanumeric, special chars, dots
        merchantPattern: '\\*?Merchant[:\\s*]+([A-Za-z0-9*.][A-Za-z0-9*.\\s-]*?)\\s*(?:\\r?\\n|-{5,}|$)',
        
        // "*Date:* 31/07/2025" and "*Time:* 11:30:18"
        // Handles asterisks and dash separators between fields
        datePattern: '\\*?Date[:\\s*]+(\\d{2}/\\d{2}/\\d{4})\\s*(?:\\r?\\n|-+\\s*\\r?\\n)*\\s*\\*?Time[:\\s*]+(\\d{2}:\\d{2}:\\d{2})',
        
        dateFormat: 'DD-MM-YYYY', // Parser will handle / separator
      },
    ];
  }
  
  static getAllPatterns() {
    const defaultPatterns = BankPatternManager.getDefaultPatterns();
    
    // Check if user has custom patterns defined
    if (typeof getBankPatterns === 'function') {
      try {
        const customPatterns = getBankPatterns();
        return [...defaultPatterns, ...customPatterns];
      } catch (error) {
        console.log('No custom bank patterns found, using defaults only');
        return defaultPatterns;
      }
    }
    
    return defaultPatterns;
  }
}

// ==================== SERVICES ====================

/**
 * EmailService for Gmail operations
 */
class EmailService {
  constructor(logger) {
    this.logger = logger;
  }
  
  searchEmails(searchQuery, maxResults = 50) {
    try {
      this.logger.info(`Searching emails with query: ${searchQuery}`);
      const threads = GmailApp.search(searchQuery, 0, maxResults);
      this.logger.info(`Found ${threads.length} email threads`);
      return threads;
    } catch (error) {
      this.logger.error(`Error searching emails: ${error.message}`);
      throw new Error(`Failed to search emails: ${error.message}`);
    }
  }
  
  getMessagesFromThread(thread) {
    try {
      return thread.getMessages();
    } catch (error) {
      this.logger.error(`Error getting messages from thread: ${error.message}`);
      throw new Error(`Failed to get messages: ${error.message}`);
    }
  }
  
  extractEmailData(message) {
    try {
      let body = message.getPlainBody();
      
      // Check if plain body is empty, null, or the literal string "null"
      const isPlainBodyEmpty = !body || body.trim() === '' || body.trim().toLowerCase() === 'null';
      
      if (isPlainBodyEmpty) {
        this.logger.debug('Plain body empty/null, extracting from HTML...');
        const htmlBody = message.getBody();
        
        if (htmlBody && htmlBody.trim() !== '') {
          // Strip HTML tags
          body = this.stripHtmlTags(htmlBody);
          this.logger.debug(`Extracted ${body.length} chars from HTML`);
        } else {
          body = '';
        }
      }
      
      return {
        id: message.getId(),
        from: message.getFrom(),
        subject: message.getSubject(),
        body: body || '',
        date: message.getDate()
      };
    } catch (error) {
      this.logger.error(`Error extracting email data: ${error.message}`);
      throw new Error(`Failed to extract email data: ${error.message}`);
    }
  }
  
  stripHtmlTags(html) {
    if (!html) return '';
    
    try {
      return html
        .replace(/<style[^>]*>[\s\S]*?<\/style>/gi, '') // Remove style blocks
        .replace(/<script[^>]*>[\s\S]*?<\/script>/gi, '') // Remove script blocks
        .replace(/<br\s*\/?>/gi, '\n') // Convert <br> to newlines
        .replace(/<\/p>/gi, '\n') // Convert </p> to newlines
        .replace(/<\/div>/gi, '\n') // Convert </div> to newlines
        .replace(/<[^>]+>/g, ' ') // Remove all other HTML tags
        .replace(/&nbsp;/gi, ' ') // Replace &nbsp;
        .replace(/&amp;/gi, '&') // Replace &amp;
        .replace(/&lt;/gi, '<') // Replace &lt;
        .replace(/&gt;/gi, '>') // Replace &gt;
        .replace(/&quot;/gi, '"') // Replace &quot;
        .replace(/&#39;/gi, "'") // Replace &#39;
        .replace(/&#8377;/gi, '₹') // Replace &#8377; (rupee symbol)
        .replace(/\s*\n\s*/g, '\n') // Clean up newlines
        .replace(/\n{3,}/g, '\n\n') // Max 2 consecutive newlines
        .replace(/ {2,}/g, ' ') // Normalize spaces
        .trim();
    } catch (error) {
      this.logger.error(`Error stripping HTML: ${error.message}`);
      return html;
    }
  }
  
  markAsRead(message) {
    try {
      message.markRead();
      return true;
    } catch (error) {
      this.logger.error(`Error marking email as read: ${error.message}`);
      return false;
    }
  }
}

/**
 * SheetService for Google Sheets operations
 */
class SheetService {
  constructor(sheetId, sheetName, logger, nicknameManager) {
    this.sheetId = sheetId;
    this.sheetName = sheetName;
    this.logger = logger;
    this.nicknameManager = nicknameManager;
    this.spreadsheet = null;
    this.sheet = null;
  }
  
  initialize() {
    try {
      this.spreadsheet = SpreadsheetApp.openById(this.sheetId);
      this.sheet = this.spreadsheet.getSheetByName(this.sheetName);
      
      if (!this.sheet) {
        this.logger.info(`Sheet "${this.sheetName}" not found, creating...`);
        this.sheet = this.createSheet();
      }
      
      return this.sheet;
    } catch (error) {
      this.logger.error(`Error initializing sheet: ${error.message}`);
      throw new Error(`Failed to initialize sheet: ${error.message}`);
    }
  }
  
  createSheet() {
    try {
      const sheet = this.spreadsheet.insertSheet(this.sheetName);
      this.setupHeaders(sheet);
      return sheet;
    } catch (error) {
      this.logger.error(`Error creating sheet: ${error.message}`);
      throw new Error(`Failed to create sheet: ${error.message}`);
    }
  }
  
  setupHeaders(sheet) {
    const headers = SHEET_HEADERS;
    sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
    
    const headerRange = sheet.getRange(1, 1, 1, headers.length);
    headerRange.setFontWeight(HEADER_STYLE.fontWeight);
    headerRange.setBackground(HEADER_STYLE.backgroundColor);
    headerRange.setFontColor(HEADER_STYLE.fontColor);
    sheet.setFrozenRows(1);
    
    this.setColumnWidths(sheet);
    this.logger.info('Sheet headers created and formatted');
  }
  
  setColumnWidths(sheet) {
    const widths = [100, 80, 100, 150, 180, 300, 150, 150, 250];
    widths.forEach((width, index) => {
      sheet.setColumnWidth(index + 1, width);
    });
  }
  
  getProcessedEmailIds() {
    try {
      if (!this.sheet) this.initialize();
      
      const lastRow = this.sheet.getLastRow();
      if (lastRow <= 1) return new Set();
      
      const emailIds = this.sheet.getRange(2, 9, lastRow - 1, 1).getValues();
      return new Set(emailIds.map(row => row[0]).filter(id => id));
    } catch (error) {
      this.logger.error(`Error getting processed email IDs: ${error.message}`);
      return new Set();
    }
  }
  
  appendTransaction(transaction, emailId) {
    try {
      if (!this.sheet) this.initialize();
      
      const row = this.formatTransactionRow(transaction, emailId);
      
      // Append at bottom for chronological order (oldest first, newest last)
      this.sheet.appendRow(row);
      
      // Format the amount column
      const lastRow = this.sheet.getLastRow();
      this.sheet.getRange(lastRow, 3).setNumberFormat(AMOUNT_FORMAT);
      
      this.logger.info(`Transaction appended: ${transaction.merchant} - ${transaction.amount}`);
      return true;
    } catch (error) {
      this.logger.error(`Error appending transaction: ${error.message}`);
      return false;
    }
  }
  
  formatTransactionRow(transaction, emailId) {
    const date = transaction.datetime || new Date();
    const timezone = getTimezone();
    const accountNickname = this.nicknameManager 
      ? this.nicknameManager.getAccountNickname(transaction.account || '', transaction.source || '')
      : '';
    const category = CategoryMapper.categorize(transaction.merchant || '');
    
    return [
      Utilities.formatDate(date, timezone, 'dd-MMM-yyyy'),
      Utilities.formatDate(date, timezone, 'HH:mm:ss'),
      transaction.amount,
      transaction.account || 'N/A',
      accountNickname || '',
      transaction.merchant || 'N/A',
      category,
      transaction.source || 'Unknown',
      emailId
    ];
  }
  
  getSheet() {
    if (!this.sheet) this.initialize();
    return this.sheet;
  }
}

/**
 * TransactionParser for parsing transaction emails
 */
class TransactionParser {
  constructor(logger) {
    this.logger = logger;
    this.patterns = BankPatternManager.getAllPatterns();
  }
  
  parseEmail(emailData) {
    this.logger.debug(`Parsing email from: ${emailData.from}`);
    this.logger.debug(`Subject: ${emailData.subject}`);
    
    const pattern = this.findMatchingPattern(emailData);
    if (!pattern) {
      this.logger.warn('No matching pattern found for email');
      return null;
    }
    
    this.logger.info(`Matched pattern: ${pattern.name}`);
    const transaction = this.extractTransaction(emailData, pattern);
    
    if (!transaction) {
      this.logger.warn('Failed to extract transaction data');
      return null;
    }
    
    return transaction;
  }
  
  findMatchingPattern(emailData) {
    for (const pattern of this.patterns) {
      if (this.isPatternMatch(emailData, pattern)) {
        return pattern;
      }
    }
    return null;
  }
  
  isPatternMatch(emailData, pattern) {
    const senderMatch = pattern.senderPattern 
      ? new RegExp(pattern.senderPattern, 'i').test(emailData.from)
      : false;
    const subjectMatch = pattern.subjectPattern
      ? new RegExp(pattern.subjectPattern, 'i').test(emailData.subject)
      : false;
    
    this.logger.debug(`Testing pattern: ${pattern.name}`);
    this.logger.debug(`  Sender match: ${senderMatch} (pattern: ${pattern.senderPattern})`);
    this.logger.debug(`  Subject match: ${subjectMatch} (pattern: ${pattern.subjectPattern})`);
    
    // Basic match: sender OR subject
    const basicMatch = senderMatch || subjectMatch;
    
    // If bodyPattern is specified, it MUST also match (additional validation)
    if (basicMatch && pattern.bodyPattern) {
      const bodyMatch = new RegExp(pattern.bodyPattern, 'i').test(emailData.body);
      this.logger.debug(`  Body match: ${bodyMatch} (pattern: ${pattern.bodyPattern})`);
      return bodyMatch;
    }
    
    return basicMatch;
  }
  
  extractTransaction(emailData, pattern) {
    const transaction = {
      datetime: null,
      amount: null,
      account: null,
      merchant: null,
      source: pattern.name
    };

    if (pattern.amountPattern) {
      transaction.amount = this.extractField(emailData.body, pattern.amountPattern, TextUtils.normalizeAmount);
    }
    if (pattern.accountPattern) {
      let account = this.extractField(emailData.body, pattern.accountPattern, TextUtils.cleanText);
      // Add XX prefix if it's just 4 digits (and doesn't already have XX)
      if (account && /^\d{4}$/.test(account)) {
        account = 'XX' + account;
      }
      transaction.account = account;
    }
    if (pattern.merchantPattern) {
      transaction.merchant = this.extractField(emailData.body, pattern.merchantPattern, TextUtils.cleanText);
    }
    if (pattern.datePattern) {
      // Pass date format to parser
      transaction.datetime = this.extractField(
        emailData.body,
        pattern.datePattern,
        (dateStr) => DateUtils.parseDate(dateStr, pattern.dateFormat)
      );
    }
    
    if (!transaction.datetime) {
      transaction.datetime = emailData.date;
    }
    
    if (!transaction.amount) {
      this.logger.warn('Transaction missing amount');
      return null;
    }
    
    return transaction;
  }
  
  extractField(text, pattern, formatter) {
    try {
      const match = text.match(new RegExp(pattern, 'i'));
      if (match) {
        let value;
        
        // Check if we have multiple capture groups
        if (match.length > 2) {
          // Find the first non-null captured group (skip match[0] which is full match)
          for (let i = 1; i < match.length; i++) {
            if (match[i]) {
              value = match[i];
              // For date patterns, check if next group is time
              if (i + 1 < match.length && match[i + 1] && /^\d{2}:\d{2}/.test(match[i + 1])) {
                value = match[i] + ' ' + match[i + 1];
              }
              break;
            }
          }
        } else {
          // Single capture group
          value = match[1] || match[0];
        }
        
        this.logger.debug(`Extracted value: "${value}" using pattern: ${pattern}`);
        return formatter ? formatter(value) : value;
      }
      // Enhanced debug: show portion of text around where we expected to find match
      const patternKey = pattern.substring(0, 20);
      const textSample = text.substring(0, 200);
      this.logger.debug(`No match found for pattern: ${pattern}`);
      this.logger.debug(`Text sample (first 200 chars): ${textSample}`);
      return null;
    } catch (error) {
      this.logger.error(`Error extracting field with pattern "${pattern}": ${error.message}`);
      return null;
    }
  }
}

/**
 * QueryBuilder for building Gmail search queries
 */
class QueryBuilder {
  constructor(logger) {
    this.logger = logger;
  }
  
  buildFromPatterns(patterns, searchDays) {
    const senders = this.extractSenders(patterns);
    const subjectPhrases = this.extractSubjectPhrases(patterns);
    
    const queryParts = [];
    if (senders.length > 0) queryParts.push(`from:(${senders.join(' OR ')})`);
    if (subjectPhrases.length > 0) queryParts.push(`subject:(${subjectPhrases.join(' OR ')})`);
    queryParts.push(`newer_than:${searchDays}d`);
    
    const query = queryParts.join(' ');
    this.logger.debug(`Built search query: ${query}`);
    return query;
  }
  
  extractSenders(patterns) {
    const senders = [];
    patterns.forEach(pattern => {
      if (pattern.senderPattern) {
        // Split by | to get multiple sender patterns
        const senderEmails = pattern.senderPattern.split('|');
        
        senderEmails.forEach(email => {
          // Clean up the regex syntax to get the actual email
          const cleanEmail = email
            .replace(/\\/g, '') // Remove backslashes
            .trim();
          
          if (cleanEmail.includes('@')) {
            senders.push(cleanEmail);
          }
        });
      }
    });
    return [...new Set(senders)];
  }
  
  extractSubjectPhrases(patterns) {
    const phrases = [];
    
    patterns.forEach(pattern => {
      if (pattern.subjectPattern) {
        // Split by | to get individual phrase patterns
        const individualPatterns = pattern.subjectPattern.split('|');
        
        individualPatterns.forEach(subPattern => {
          // Clean up regex syntax and get the actual phrase
          let phrase = subPattern
            .replace(/\\/g, '') // Remove backslashes
            .replace(/\.\*\?/g, '') // Remove .*?
            .replace(/\\s\+/g, ' ') // Replace \s+ with space
            .replace(/\\s/g, ' ') // Replace \s with space
            .replace(/[\(\)\[\]\{\}]/g, '') // Remove grouping brackets
            .trim();
          
          // Only add if it's a meaningful phrase (not just regex)
          if (phrase.length > 0 && phrase.split(' ').length >= 2) {
            // Wrap in quotes for exact phrase matching in Gmail
            phrases.push(`"${phrase}"`);
          }
        });
      }
    });
    
    return phrases;
  }
}

// ==================== SERVICE INITIALIZATION ====================

/**
 * Factory for initializing all services
 */
class ServiceFactory {
  static initialize() {
    const logger = new Logger(LOG_LEVEL);
    const spreadsheet = SpreadsheetApp.openById(SHEET_ID);
    const emailService = new EmailService(logger);
    const nicknameManager = new NicknameManager(spreadsheet, logger);
    const sheetService = new SheetService(SHEET_ID, SHEET_NAME, logger, nicknameManager);
    const transactionParser = new TransactionParser(logger);
    const queryBuilder = new QueryBuilder(logger);
    
    // Initialize nickname manager
    nicknameManager.initialize();
    
    return { logger, emailService, sheetService, transactionParser, queryBuilder, nicknameManager };
  }
}

// ==================== MAIN FUNCTIONS ====================
// These are the primary functions users will run

/**
 * Main automation function - processes transaction emails and adds to sheet
 * This function is called automatically every 10 minutes by the trigger
 * You can also run it manually anytime
 * 
 * @returns {Object} Result with success status, totalEmails, and newTransactions
 */
function processTransactionEmails() {
  const services = ServiceFactory.initialize();
  const { logger, emailService, sheetService, transactionParser, queryBuilder } = services;
  
  try {
    logger.section('TransacFlow - Processing Transaction Emails');
    
    sheetService.initialize();
    const processedEmailIds = sheetService.getProcessedEmailIds();
    logger.info(`Already processed: ${processedEmailIds.size} transactions`);
    
    const searchQuery = SEARCH_QUERY_MODE === 'DYNAMIC'
      ? queryBuilder.buildFromPatterns(BankPatternManager.getAllPatterns(), EMAIL_SEARCH_DAYS)
      : getSearchQuery();
    
    const threads = emailService.searchEmails(searchQuery, MAX_EMAILS_PER_RUN);
    
    if (threads.length === 0) {
      logger.info('No new emails found matching the query');
      return { success: true, totalEmails: 0, newTransactions: 0 };
    }
    
    let emailsProcessed = 0;
    const newTransactionsList = []; // Collect all new transactions
    const messagesToMarkRead = []; // Track messages to mark as read
    
    threads.forEach(thread => {
      const messages = emailService.getMessagesFromThread(thread);
      messages.forEach(message => {
        const emailData = emailService.extractEmailData(message);
        
        if (processedEmailIds.has(emailData.id)) {
          logger.debug(`Skipping already processed email: ${emailData.id}`);
          return;
        }
        
        emailsProcessed++;
        const transaction = transactionParser.parseEmail(emailData);
        
        if (transaction) {
          newTransactionsList.push({
            transaction: transaction,
            emailId: emailData.id,
            message: message
          });
        }
      });
    });
    
    // Sort transactions by datetime (oldest first)
    // Append in chronological order: oldest at top, newest at bottom
    newTransactionsList.sort((a, b) => {
      const dateA = a.transaction.datetime || new Date(0);
      const dateB = b.transaction.datetime || new Date(0);
      return dateA - dateB; // Ascending order (oldest first)
    });
    
    // Append all transactions in chronological order
    let newTransactions = 0;
    newTransactionsList.forEach(item => {
      if (sheetService.appendTransaction(item.transaction, item.emailId)) {
        newTransactions++;
        if (MARK_EMAILS_AS_READ) {
          emailService.markAsRead(item.message);
        }
      }
    });
    
    logger.section('Processing Complete');
    logger.success(`Processed ${emailsProcessed} emails`);
    logger.success(`Added ${newTransactions} new transactions in chronological order`);
    
    // Auto-refresh dashboard if enabled and new transactions were added
    if (newTransactions > 0 && typeof AUTO_UPDATE_DASHBOARD !== 'undefined' && AUTO_UPDATE_DASHBOARD) {
      try {
        logger.info('Auto-refreshing dashboard...');
        const spreadsheet = SpreadsheetApp.openById(SHEET_ID);
        const dashboardManager = new DashboardManager(spreadsheet, logger);
        dashboardManager.createCategoryDataSheet();
        logger.success('Dashboard data refreshed');
      } catch (dashError) {
        logger.warn(`Dashboard auto-refresh failed: ${dashError.message}`);
      }
    }
    
    return { success: true, totalEmails: emailsProcessed, newTransactions: newTransactions };
    
  } catch (error) {
    logger.error(`Fatal error: ${error.message}`);
    if (SEND_ERROR_NOTIFICATIONS) {
      MailApp.sendEmail({
        to: Session.getActiveUser().getEmail(),
        subject: 'TransacFlow Error',
        body: `Error processing transactions: ${error.message}`
      });
    }
    throw error;
  }
}

/**
 * Setup function - validates config and creates the automation trigger
 * Run this once to set up TransacFlow
 * 
 * Steps:
 * 1. Validates configuration in Config.gs
 * 2. Tests connection to Google Sheet
 * 3. Creates trigger to run every 10 minutes
 */
function setupAutomation() {
  const logger = new Logger(LOG_LEVEL);
  logger.section('TransacFlow Setup');
  
  if (!validateConfig()) {
    logger.error('Configuration validation failed. Please fix errors in Config.gs');
    return;
  }
  
  logger.info('Configuration validated ✓');
  
  try {
    const sheet = SpreadsheetApp.openById(SHEET_ID);
    logger.success(`Connected to sheet: ${sheet.getName()}`);
  } catch (error) {
    logger.error(`Cannot access sheet with ID: ${SHEET_ID}`);
    logger.error('Please check SHEET_ID in Config.gs');
    return;
  }
  
  TriggerManager.create();
  
  logger.section('Setup Complete!');
  logger.success('TransacFlow is now active');
  logger.info('Run processTransactionEmails() to test immediately');
}

// ==================== INTERNAL MANAGERS ====================
// These classes encapsulate internal logic and are not shown in dropdown

/**
 * Manager for account nicknames (stored in separate sheet)
 */
class NicknameManager {
  constructor(spreadsheet, logger) {
    this.spreadsheet = spreadsheet;
    this.logger = logger;
    this.sheet = null;
    this.cache = new Map();
    this.nicknamesSheetName = typeof NICKNAMES_SHEET_NAME !== 'undefined' ? NICKNAMES_SHEET_NAME : 'Account Nicknames';
  }
  
  initialize() {
    try {
      this.sheet = this.spreadsheet.getSheetByName(this.nicknamesSheetName);
      
      if (!this.sheet) {
        this.logger.info(`Creating "${this.nicknamesSheetName}" sheet...`);
        this.sheet = this.createNicknamesSheet();
      }
      
      // Load nicknames into cache
      this.loadCache();
      return this.sheet;
    } catch (error) {
      this.logger.error(`Error initializing nicknames sheet: ${error.message}`);
      throw error;
    }
  }
  
  createNicknamesSheet() {
    const sheet = this.spreadsheet.insertSheet(this.nicknamesSheetName);
    
    // Setup headers
    const headers = ['Account/Card/UPI', 'Auto-Generated Name', 'Custom Nickname', 'Last Updated'];
    sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
    
    // Style header
    const headerRange = sheet.getRange(1, 1, 1, headers.length);
    headerRange.setFontWeight('bold');
    headerRange.setBackground('#34a853'); // Green
    headerRange.setFontColor('#ffffff');
    sheet.setFrozenRows(1);
    
    // Set column widths
    sheet.setColumnWidth(1, 180); // Account
    sheet.setColumnWidth(2, 250); // Auto-generated
    sheet.setColumnWidth(3, 250); // Custom
    sheet.setColumnWidth(4, 150); // Last Updated
    
    // Add instructions in row 2
    sheet.getRange(2, 1, 1, 4).setValues([[
      'Instructions →',
      'This is auto-generated',
      'Edit this column to customize names',
      'Auto-updated'
    ]]);
    sheet.getRange(2, 1, 1, 4).setFontStyle('italic').setBackground('#f3f3f3');
    
    this.logger.success('Account Nicknames sheet created');
    return sheet;
  }
  
  loadCache() {
    if (!this.sheet) return;
    
    const lastRow = this.sheet.getLastRow();
    if (lastRow < 3) return; // No data (headers + instructions only)
    
    const data = this.sheet.getRange(3, 1, lastRow - 2, 3).getValues();
    data.forEach(row => {
      const [account, autoName, customName] = row;
      if (account) {
        this.cache.set(account, {
          autoName: autoName || '',
          customName: customName || ''
        });
      }
    });
    
    this.logger.debug(`Loaded ${this.cache.size} account nicknames into cache`);
  }
  
  getAccountNickname(accountNumber, source) {
    if (!accountNumber) return '';
    
    // Check cache first
    if (this.cache.has(accountNumber)) {
      const { autoName, customName } = this.cache.get(accountNumber);
      return customName || autoName || '';
    }
    
    // Generate auto name
    const autoName = this.generateAutoName(accountNumber, source);
    
    // Add to sheet and cache (async, non-blocking)
    this.addAccountIfNew(accountNumber, autoName, source);
    
    return autoName;
  }
  
  generateAutoName(accountNumber, source) {
    if (!source) return accountNumber;
    
    const last4 = accountNumber.replace(/^XX/, '').slice(-4);
    return `${source} (${last4})`;
  }
  
  addAccountIfNew(accountNumber, autoName, source) {
    if (!this.sheet || this.cache.has(accountNumber)) return;
    
    try {
      // Add to cache immediately
      this.cache.set(accountNumber, {
        autoName: autoName,
        customName: ''
      });
      
      // Add to sheet
      const timestamp = new Date();
      this.sheet.appendRow([
        accountNumber,
        autoName,
        '', // Empty custom nickname
        Utilities.formatDate(timestamp, getTimezone(), 'dd-MMM-yyyy HH:mm:ss')
      ]);
      
      this.logger.debug(`Added new account to nicknames sheet: ${accountNumber}`);
    } catch (error) {
      this.logger.error(`Error adding account to nicknames sheet: ${error.message}`);
    }
  }
  
  updateTransactionsWithNickname(accountNumber, newNickname) {
    try {
      const transSheet = this.spreadsheet.getSheetByName(SHEET_NAME);
      if (!transSheet) return 0;
      
      const lastRow = transSheet.getLastRow();
      if (lastRow <= 1) return 0;
      
      // Get all data
      const dataRange = transSheet.getRange(2, 1, lastRow - 1, 9);
      const data = dataRange.getValues();
      
      let updatedCount = 0;
      
      // Update matching rows
      for (let i = 0; i < data.length; i++) {
        if (data[i][3] === accountNumber) { // Column 4 (index 3) is Account/Card/UPI
          data[i][4] = newNickname; // Column 5 (index 4) is Account Name
          updatedCount++;
        }
      }
      
      if (updatedCount > 0) {
        dataRange.setValues(data);
        this.logger.success(`Updated ${updatedCount} transactions with new nickname`);
      }
      
      return updatedCount;
    } catch (error) {
      this.logger.error(`Error updating transactions: ${error.message}`);
      return 0;
    }
  }
}

/**
 * Manager for automation triggers
 */
class TriggerManager {
  static create() {
    const logger = new Logger(LOG_LEVEL);
    
    // Remove existing triggers
    const existingTriggers = ScriptApp.getProjectTriggers();
    existingTriggers.forEach(trigger => {
      const handlerFunc = trigger.getHandlerFunction();
      if (handlerFunc === 'processTransactionEmails' || handlerFunc === 'onNicknameEdit') {
        ScriptApp.deleteTrigger(trigger);
        logger.info(`Removed existing trigger: ${handlerFunc}`);
      }
    });
    
    // Create time-based trigger for automation
    ScriptApp.newTrigger('processTransactionEmails')
      .timeBased()
      .everyMinutes(10)
      .create();
    logger.success('Created time-based trigger: runs every 10 minutes');
    
    // Create onEdit trigger for nickname updates
    ScriptApp.newTrigger('onNicknameEdit')
      .forSpreadsheet(SHEET_ID)
      .onEdit()
      .create();
    logger.success('Created onEdit trigger: detects nickname changes');
  }
}

// ==================== TESTING FUNCTIONS ====================
// Use these to test email parsing before running the full automation

/**
 * TEST: Parse a single email and display the extracted data
 * 
 * What it does:
 * - Finds one matching email
 * - Shows email details (from, subject, date)
 * - Extracts transaction data
 * - Displays in console (does NOT write to sheet)
 * 
 * Use this to verify patterns are working correctly
 */
function test_SingleEmail() {
  const services = ServiceFactory.initialize();
  const { logger, emailService, transactionParser, queryBuilder } = services;
  
  logger.section('Testing Email Parsing');
  
  const patterns = BankPatternManager.getAllPatterns();
  const searchQuery = SEARCH_QUERY_MODE === 'DYNAMIC'
    ? queryBuilder.buildFromPatterns(patterns, EMAIL_SEARCH_DAYS)
    : getSearchQuery();
  
  logger.info(`Patterns loaded: ${patterns.length}`);
  logger.info(`Using search query: ${searchQuery}`);
  const threads = emailService.searchEmails(searchQuery, 1);
  
  if (threads.length === 0) {
    logger.warn('No emails found. Try adjusting the search query.');
    return;
  }
  
  const messages = emailService.getMessagesFromThread(threads[0]);
  const message = messages[0];
  const emailData = emailService.extractEmailData(message);
  
  logger.info('Email Details:');
  logger.info(`From: ${emailData.from}`);
  logger.info(`Subject: ${emailData.subject}`);
  logger.info(`Date: ${emailData.date}`);
  
  const transaction = transactionParser.parseEmail(emailData);
  
  if (transaction) {
    logger.section('Extracted Transaction');
    logger.object('Transaction Data', transaction);
  } else {
    logger.error('Could not parse transaction from email');
  }
}

/**
 * TEST: Parse a single email and write it to the sheet
 * 
 * What it does:
 * - Finds one matching email
 * - Extracts transaction data
 * - Writes to Google Sheet
 * - Shows transaction details in console
 * 
 * Use this to test the complete flow including sheet writing
 */
function test_WriteToSheet() {
  const services = ServiceFactory.initialize();
  const { logger, emailService, sheetService, transactionParser, queryBuilder } = services;
  
  logger.section('Testing Email Parsing & Sheet Writing');
  
  sheetService.initialize();
  
  const patterns = BankPatternManager.getAllPatterns();
  const searchQuery = SEARCH_QUERY_MODE === 'DYNAMIC'
    ? queryBuilder.buildFromPatterns(patterns, EMAIL_SEARCH_DAYS)
    : getSearchQuery();
  
  logger.info(`Patterns loaded: ${patterns.length}`);
  logger.info(`Using search query: ${searchQuery}`);
  const threads = emailService.searchEmails(searchQuery, 1);
  
  if (threads.length === 0) {
    logger.warn('No emails found');
    return false;
  }
  
  const messages = emailService.getMessagesFromThread(threads[0]);
  const message = messages[0];
  const emailData = emailService.extractEmailData(message);
  
  logger.info(`Testing with email from: ${emailData.from}`);
  const transaction = transactionParser.parseEmail(emailData);
  
  if (transaction) {
    logger.object('Transaction Data', transaction);
    if (sheetService.appendTransaction(transaction, emailData.id)) {
      logger.success('Transaction written to sheet!');
      return true;
    } else {
      logger.error('Failed to write transaction to sheet');
      return false;
    }
  } else {
    logger.error('Could not parse transaction');
    return false;
  }
}

// ==================== NICKNAME TRIGGER FUNCTION ====================

/**
 * Trigger function called when user edits the Account Nicknames sheet
 * Automatically updates all transactions with the new nickname
 * 
 * @internal - Installed by setupAutomation()
 */
function onNicknameEdit(e) {
  try {
    // Check if edit is in Account Nicknames sheet
    const nicknamesSheetName = typeof NICKNAMES_SHEET_NAME !== 'undefined' ? NICKNAMES_SHEET_NAME : 'Account Nicknames';
    const sheet = e.source.getActiveSheet();
    if (sheet.getName() !== nicknamesSheetName) return;
    
    const range = e.range;
    const row = range.getRow();
    const col = range.getColumn();
    
    // Check if edit is in "Custom Nickname" column (column 3) and not in header rows
    if (col !== 3 || row <= 2) return;
    
    const logger = new Logger(LOG_LEVEL);
    const spreadsheet = e.source;
    const nicknameManager = new NicknameManager(spreadsheet, logger);
    nicknameManager.sheet = sheet;
    
    // Get the account number and new nickname
    const accountNumber = sheet.getRange(row, 1).getValue();
    const newNickname = sheet.getRange(row, 3).getValue();
    
    // Use auto-generated name if custom nickname is empty
    const finalNickname = newNickname || sheet.getRange(row, 2).getValue();
    
    // Update timestamp
    sheet.getRange(row, 4).setValue(
      Utilities.formatDate(new Date(), getTimezone(), 'dd-MMM-yyyy HH:mm:ss')
    );
    
    // Update all transactions with this account
    const count = nicknameManager.updateTransactionsWithNickname(accountNumber, finalNickname);
    
    if (count > 0) {
      try {
        const activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
        if (activeSpreadsheet) {
          activeSpreadsheet.toast(
            `Updated ${count} transaction(s) with new nickname for ${accountNumber}`,
            'Nickname Updated',
            5
          );
        }
      } catch (e) {
        // Ignore toast errors
      }
    }
    
  } catch (error) {
    console.error(`Error in onNicknameEdit: ${error.message}`);
  }
}

// ==================== DASHBOARD & ANALYTICS ====================

/**
 * CategoryMapper - Intelligent category classification for transactions
 */
class CategoryMapper {
  static getCategoryRules() {
    return {
      'Food & Dining': ['swiggy', 'zomato', 'uber eats', 'dominos', 'pizza', 'mcdonald', 'kfc', 'subway', 'starbucks', 'cafe', 'restaurant', 'food', 'dunkin', 'burger king'],
      'Transportation': ['uber', 'ola', 'rapido', 'metro', 'irctc', 'makemytrip', 'goibibo', 'redbus', 'petrol', 'fuel', 'parking', 'fastag'],
      'Shopping': ['amazon', 'flipkart', 'myntra', 'ajio', 'meesho', 'nykaa', 'shop', 'store', 'mall', 'retail'],
      'Groceries': ['bigbasket', 'grofers', 'blinkit', 'zepto', 'dunzo', 'jiomart', 'dmart', 'grocery', 'supermarket'],
      'Entertainment': ['netflix', 'amazon prime', 'hotstar', 'spotify', 'youtube', 'bookmyshow', 'paytm insider', 'gaana', 'apple music', 'movie', 'cinema'],
      'Bills & Utilities': ['electricity', 'water', 'gas', 'broadband', 'internet', 'mobile', 'recharge', 'postpaid', 'airtel', 'jio', 'vodafone', 'bill payment'],
      'Health & Fitness': ['pharmacy', 'apollo', 'medplus', 'practo', 'cult fit', 'gym', 'hospital', 'doctor', 'medical', 'health'],
      'Education': ['udemy', 'coursera', 'byju', 'unacademy', 'school', 'tuition', 'course', 'book'],
      'Insurance': ['policy', 'insurance', 'premium', 'lic'],
      'Investments': ['mutual fund', 'sip', 'stocks', 'zerodha', 'groww', 'upstox'],
      'Transfer': ['upi', 'neft', 'imps', 'fund transfer', 'paytm wallet', 'phonepe'],
      'Other': []
    };
  }
  
  static categorize(merchant) {
    if (!merchant) return 'Uncategorized';
    
    const merchantLower = merchant.toLowerCase();
    const rules = this.getCategoryRules();
    
    for (const [category, keywords] of Object.entries(rules)) {
      for (const keyword of keywords) {
        if (merchantLower.includes(keyword)) {
          return category;
        }
      }
    }
    
    return 'Other';
  }
}

/**
 * DashboardManager - Creates and updates analytics dashboard
 */
class DashboardManager {
  constructor(spreadsheet, logger) {
    this.spreadsheet = spreadsheet;
    this.logger = logger;
    this.dashboardName = 'Dashboard';
  }
  
  createOrUpdateDashboard() {
    try {
      this.logger.section('Creating/Updating Dashboard');
      
      let dashboard = this.spreadsheet.getSheetByName(this.dashboardName);
      let previousMonth = null;
      
      if (!dashboard) {
        this.logger.info('Creating new Dashboard sheet...');
        dashboard = this.spreadsheet.insertSheet(this.dashboardName, 0); // Insert at first position
      } else {
        this.logger.info('Updating existing Dashboard...');
        // Save the selected month before clearing
        try {
          previousMonth = dashboard.getRange('B2').getValue();
        } catch (e) {
          // Ignore if can't get previous value
        }
        dashboard.clear();
      }
      
      // Create dashboard sections dynamically positioned
      let currentRow = 1;
      
      currentRow = this.createHeader(dashboard, previousMonth, currentRow);
      currentRow = this.createMonthlySummary(dashboard, currentRow);
      currentRow = this.createTopMerchants(dashboard, currentRow);
      currentRow = this.createAccountWiseSpending(dashboard, currentRow);
      currentRow = this.createCategoryBreakdown(dashboard, currentRow);
      currentRow = this.createTrendAnalysis(dashboard, currentRow);
      currentRow = this.createBudgetComparison(dashboard, currentRow);
      
      // Freeze first 2 rows (header + month selector)
      dashboard.setFrozenRows(2);
      
      // Auto-resize columns
      dashboard.autoResizeColumns(1, 8);
      
      this.logger.success('Dashboard created successfully!');
      return dashboard;
      
    } catch (error) {
      this.logger.error(`Error creating dashboard: ${error.message}`);
      throw error;
    }
  }
  
  createHeader(sheet, previousMonth = null, startRow = 1) {
    // Title
    sheet.getRange(`A${startRow}:H${startRow}`).merge();
    sheet.getRange(`A${startRow}`).setValue('📊 TransacFlow Analytics Dashboard');
    sheet.getRange(`A${startRow}`).setFontSize(18).setFontWeight('bold').setHorizontalAlignment('center');
    sheet.getRange(`A${startRow}`).setBackground('#4285f4').setFontColor('#ffffff');
    
    // Month Selector Row
    const row2 = startRow + 1;
    sheet.getRange(`A${row2}`).setValue('Select Month:');
    sheet.getRange(`A${row2}`).setFontWeight('bold').setHorizontalAlignment('right');
    sheet.getRange(`A${row2}`).setBackground('#e8f0fe');
    
    // Create dropdown first, then set default value
    this.createMonthDropdown(sheet, `B${row2}`, previousMonth);
    sheet.getRange(`B${row2}`).setBackground('#ffffff').setHorizontalAlignment('left');
    
    // Last updated
    sheet.getRange(`C${row2}:H${row2}`).merge();
    sheet.getRange(`C${row2}`).setValue('Last Updated: ' + new Date().toLocaleString());
    sheet.getRange(`C${row2}`).setFontSize(10).setFontStyle('italic').setHorizontalAlignment('center');
    sheet.getRange(`C${row2}`).setBackground('#e8f0fe');
    
    sheet.setRowHeight(startRow, 40);
    sheet.setRowHeight(row2, 30);
    
    // Return next available row (leave 1 row gap)
    return row2 + 2;
  }
  
  createMonthDropdown(sheet, cellAddress, previousMonth = null) {
    // Get unique months from actual transactions
    const months = this.getAvailableMonths();
    
    // Fallback to current month if no transactions exist
    if (months.length === 0) {
      const now = new Date();
      const year = now.getFullYear();
      const month = String(now.getMonth() + 1).padStart(2, '0');
      months.push(`${year}-${month}-01`);
    }
    
    const cell = sheet.getRange(cellAddress);
    
    // Set default value FIRST (before validation)
    let defaultMonth = months[0]; // Most recent month
    
    // Convert previousMonth to string format if it's a Date object
    if (previousMonth) {
      let previousMonthStr;
      if (previousMonth instanceof Date) {
        const year = previousMonth.getFullYear();
        const month = String(previousMonth.getMonth() + 1).padStart(2, '0');
        previousMonthStr = `${year}-${month}-01`;
      } else {
        previousMonthStr = String(previousMonth);
      }
      
      // Only use previousMonth if it's in the valid list
      if (months.includes(previousMonthStr)) {
        defaultMonth = previousMonthStr;
      }
    }
    
    cell.setValue(defaultMonth);
    
    // Then set the data validation rule
    const rule = SpreadsheetApp.newDataValidation()
      .requireValueInList(months, true)
      .setAllowInvalid(false)
      .build();
    cell.setDataValidation(rule);
  }
  
  getAvailableMonths() {
    try {
      const transSheet = this.spreadsheet.getSheetByName(SHEET_NAME);
      if (!transSheet || transSheet.getLastRow() <= 1) {
        return [];
      }
      
      // Get all transaction dates (column A)
      const lastRow = transSheet.getLastRow();
      const dates = transSheet.getRange(2, 1, lastRow - 1, 1).getValues();
      
      // Extract unique month-year combinations
      const monthSet = new Set();
      dates.forEach(row => {
        const dateValue = row[0];
        if (dateValue) {
          try {
            let transDate;
            if (dateValue instanceof Date) {
              transDate = dateValue;
            } else if (typeof dateValue === 'string' && dateValue.trim() !== '') {
              transDate = new Date(dateValue);
            }
            
            if (transDate && !isNaN(transDate.getTime())) {
              const year = transDate.getFullYear();
              const month = String(transDate.getMonth() + 1).padStart(2, '0');
              monthSet.add(`${year}-${month}-01`);
            }
          } catch (e) {
            // Skip invalid dates
          }
        }
      });
      
      // Convert to array and sort in descending order (most recent first)
      const months = Array.from(monthSet).sort((a, b) => {
        return new Date(b) - new Date(a);
      });
      
      this.logger.debug(`Found ${months.length} unique months with transactions`);
      return months;
      
    } catch (error) {
      this.logger.error(`Error getting available months: ${error.message}`);
      return [];
    }
  }
  
  updateMonthDropdown(dashboard, currentMonth = null) {
    try {
      // Get updated list of available months
      const months = this.getAvailableMonths();
      
      // Fallback to current month if no transactions exist
      if (months.length === 0) {
        const now = new Date();
        const year = now.getFullYear();
        const month = String(now.getMonth() + 1).padStart(2, '0');
        months.push(`${year}-${month}-01`);
      }
      
      const cell = dashboard.getRange('B2');
      
      // Determine which month to select
      let selectedMonth = months[0]; // Default to most recent
      
      if (currentMonth) {
        let currentMonthStr;
        if (currentMonth instanceof Date) {
          const year = currentMonth.getFullYear();
          const month = String(currentMonth.getMonth() + 1).padStart(2, '0');
          currentMonthStr = `${year}-${month}-01`;
        } else {
          currentMonthStr = String(currentMonth);
        }
        
        // Keep current selection if it's still valid
        if (months.includes(currentMonthStr)) {
          selectedMonth = currentMonthStr;
        }
      }
      
      // Update the dropdown validation
      const rule = SpreadsheetApp.newDataValidation()
        .requireValueInList(months, true)
        .setAllowInvalid(false)
        .build();
      cell.setDataValidation(rule);
      cell.setValue(selectedMonth);
      
      this.logger.debug(`Updated month dropdown with ${months.length} months`);
      
    } catch (error) {
      this.logger.error(`Error updating month dropdown: ${error.message}`);
    }
  }
  
  createMonthlySummary(sheet, startRow) {
    // Section header
    sheet.getRange(startRow, 1, 1, 8).merge();
    sheet.getRange(startRow, 1).setValue('💰 Selected Month Summary');
    this.formatSectionHeader(sheet, startRow);
    
    // Summary metrics using SUMPRODUCT with IFERROR to handle empty cells
    // This filters ALL transactions in the selected month, not just one day
    const metrics = [
      ['Total Transactions:', `=SUMPRODUCT((IFERROR(MONTH(DATEVALUE(Transactions!$A$2:$A$10000)),0)=MONTH(DATEVALUE($B$2)))*(IFERROR(YEAR(DATEVALUE(Transactions!$A$2:$A$10000)),0)=YEAR(DATEVALUE($B$2)))*(Transactions!$A$2:$A$10000<>""))`],
      ['Total Spent:', `=SUMPRODUCT((IFERROR(MONTH(DATEVALUE(Transactions!$A$2:$A$10000)),0)=MONTH(DATEVALUE($B$2)))*(IFERROR(YEAR(DATEVALUE(Transactions!$A$2:$A$10000)),0)=YEAR(DATEVALUE($B$2)))*(Transactions!$A$2:$A$10000<>"")*(Transactions!$C$2:$C$10000))`],
      ['Average Transaction:', `=IFERROR(B${startRow + 2}/B${startRow + 1},0)`],
      ['Highest Transaction:', `=IFERROR(MAXIFS(Transactions!$C$2:$C$10000,Transactions!$A$2:$A$10000,">="&DATEVALUE($B$2),Transactions!$A$2:$A$10000,"<"&EOMONTH(DATEVALUE($B$2),0)+1),0)`]
    ];
    
    sheet.getRange(startRow + 1, 1, metrics.length, 2).setValues(metrics);
    sheet.getRange(startRow + 1, 1, metrics.length, 1).setFontWeight('bold');
    
    // Format currency columns
    sheet.getRange(startRow + 2, 2, 3, 1).setNumberFormat(AMOUNT_FORMAT);
    
    // Add borders
    sheet.getRange(startRow + 1, 1, metrics.length, 2).setBorder(true, true, true, true, true, true);
    
    // Return next available row (section height: 1 header + 4 metrics + 2 gap for safety)
    return startRow + 1 + metrics.length + 2;
  }
  
  createTopMerchants(sheet, startRow) {
    // Section header
    sheet.getRange(startRow, 1, 1, 4).merge();
    sheet.getRange(startRow, 1).setValue('🏆 Top 5 Merchants (All Time)');
    this.formatSectionHeader(sheet, startRow);
    
    // Headers
    sheet.getRange(startRow + 1, 1, 1, 4).setValues([['Rank', 'Merchant', 'Total Spent', 'Transactions']]);
    sheet.getRange(startRow + 1, 1, 1, 4).setFontWeight('bold').setBackground('#f3f3f3');
    
    // Add rank numbers
    for (let i = 1; i <= 5; i++) {
      sheet.getRange(startRow + 1 + i, 1).setValue(i);
    }
    
    // Clear area to prevent array expansion errors
    sheet.getRange(startRow + 2, 2, 5, 3).clearContent();
    
    // Add the query formula for merchant data (spans columns B:D, 5 rows)
    const queryFormula = `=IFERROR(QUERY({Transactions!F2:F10000,Transactions!C2:C10000},"SELECT Col1, SUM(Col2), COUNT(Col2) WHERE Col1 != '' GROUP BY Col1 ORDER BY SUM(Col2) DESC LIMIT 5 LABEL Col1 '', SUM(Col2) '', COUNT(Col2) ''",0),"")`;
    sheet.getRange(startRow + 2, 2, 1, 1).setFormula(queryFormula);
    
    // Format currency column
    sheet.getRange(startRow + 2, 3, 5, 1).setNumberFormat(AMOUNT_FORMAT);
    
    // Add borders
    sheet.getRange(startRow + 1, 1, 6, 4).setBorder(true, true, true, true, true, true);
    
    // Return next available row (1 header + 6 rows + 2 gap for safety)
    return startRow + 1 + 6 + 2;
  }
  
  createAccountWiseSpending(sheet, startRow) {
    // Section header
    sheet.getRange(startRow, 1, 1, 5).merge();
    sheet.getRange(startRow, 1).setValue('💳 Account-wise Spending (All Time)');
    this.formatSectionHeader(sheet, startRow);
    
    // Headers
    sheet.getRange(startRow + 1, 1, 1, 5).setValues([['Account', 'Account Name', 'Total Spent', 'Transactions', 'Avg per Transaction']]);
    sheet.getRange(startRow + 1, 1, 1, 5).setFontWeight('bold').setBackground('#f3f3f3');
    
    // Count unique accounts to determine actual space needed
    const transSheet = this.spreadsheet.getSheetByName(SHEET_NAME);
    let maxAccounts = 10; // Default allocation
    
    if (transSheet && transSheet.getLastRow() > 1) {
      try {
        const accounts = transSheet.getRange(2, 4, transSheet.getLastRow() - 1, 1).getValues();
        const uniqueAccounts = new Set(accounts.map(row => row[0]).filter(a => a));
        maxAccounts = Math.max(uniqueAccounts.size + 3, 10); // Add buffer and at least 10 rows
        this.logger.debug(`Allocating ${maxAccounts} rows for account-wise spending`);
      } catch (e) {
        this.logger.debug('Could not count accounts, using default allocation');
      }
    }
    
    // Clear area to prevent array expansion errors
    sheet.getRange(startRow + 2, 1, maxAccounts, 5).clearContent();
    
    // Use QUERY to get account-wise aggregation (formula will expand automatically)
    const queryFormula = `=IFERROR(QUERY({Transactions!D2:D10000,Transactions!E2:E10000,Transactions!C2:C10000},"SELECT Col1, Col2, SUM(Col3), COUNT(Col3), AVG(Col3) WHERE Col1 != '' GROUP BY Col1, Col2 ORDER BY SUM(Col3) DESC LABEL Col1 '', Col2 '', SUM(Col3) '', COUNT(Col3) '', AVG(Col3) ''",0),"")`;
    sheet.getRange(startRow + 2, 1, 1, 1).setFormula(queryFormula);
    
    // Format currency columns (will apply to all rows returned by QUERY)
    sheet.getRange(startRow + 2, 3, maxAccounts, 1).setNumberFormat(AMOUNT_FORMAT);
    sheet.getRange(startRow + 2, 5, maxAccounts, 1).setNumberFormat(AMOUNT_FORMAT);
    
    // Add borders (adjusted for dynamic content)
    sheet.getRange(startRow + 1, 1, maxAccounts + 1, 5).setBorder(true, true, true, true, true, true);
    
    // Return next available row (1 header + 1 headers row + maxAccounts data rows + 2 gap for safety)
    return startRow + 1 + 1 + maxAccounts + 2;
  }
  
  createCategoryBreakdown(sheet, startRow) {
    // Section header
    sheet.getRange(startRow, 1, 1, 8).merge();
    sheet.getRange(startRow, 1).setValue('📊 Spending by Category (Selected Month)');
    this.formatSectionHeader(sheet, startRow);
    
    // Headers
    sheet.getRange(startRow + 1, 1, 1, 3).setValues([['Category', 'Amount', 'Percentage']]);
    sheet.getRange(startRow + 1, 1, 1, 3).setFontWeight('bold').setBackground('#f3f3f3');
    
    // Categories with SUMIF formulas to pull from Category Data sheet
    const categories = Object.keys(CategoryMapper.getCategoryRules());
    const rows = [];
    const totalFormula = categories.map((_, i) => `B${startRow + 2 + i}`).join('+');
    
    for (let i = 0; i < categories.length; i++) {
      const rowNum = startRow + 2 + i;
      rows.push([
        categories[i],
        `=SUMIF('Category Data'!A:A,"${categories[i]}",'Category Data'!B:B)`,
        `=IFERROR(B${rowNum}/(${totalFormula}),0)`
      ]);
    }
    
    sheet.getRange(startRow + 2, 1, rows.length, 3).setValues(rows);
    sheet.getRange(startRow + 2, 2, rows.length, 1).setNumberFormat(AMOUNT_FORMAT);
    sheet.getRange(startRow + 2, 3, rows.length, 1).setNumberFormat('0.00%');
    
    // Add note
    sheet.getRange(startRow + 2 + rows.length, 1, 1, 3).merge();
    sheet.getRange(startRow + 2 + rows.length, 1).setValue('Note: Categories are auto-assigned. Change month in dropdown above to see different periods. Run updateDashboard() to refresh.');
    sheet.getRange(startRow + 2 + rows.length, 1).setFontSize(9).setFontStyle('italic').setWrap(true);
    
    // Add borders
    sheet.getRange(startRow + 1, 1, rows.length + 1, 3).setBorder(true, true, true, true, true, true);
    
    // Return next available row (1 header + 1 header row + categories + 1 note row + 2 gap for safety)
    return startRow + 1 + 1 + rows.length + 1 + 2;
  }
  
  createTrendAnalysis(sheet, startRow) {
    // Section header
    sheet.getRange(startRow, 1, 1, 8).merge();
    sheet.getRange(startRow, 1).setValue('📈 Spending Trends (Last 6 Months)');
    this.formatSectionHeader(sheet, startRow);
    
    // Headers
    sheet.getRange(startRow + 1, 1, 1, 4).setValues([['Month', 'Total Spent', 'Transactions', 'Daily Average']]);
    sheet.getRange(startRow + 1, 1, 1, 4).setFontWeight('bold').setBackground('#f3f3f3');
    
    // Generate last 6 months with formulas using SUMPRODUCT with IFERROR to handle empty cells
    const months = [];
    for (let i = 5; i >= 0; i--) {
      const date = new Date();
      date.setMonth(date.getMonth() - i);
      date.setDate(1);
      const monthYear = Utilities.formatDate(date, getTimezone(), 'MMM-yyyy');
      const rowNum = startRow + 2 + (5 - i);
      
      months.push([
        monthYear,
        `=SUMPRODUCT((IFERROR(MONTH(DATEVALUE(Transactions!$A$2:$A$10000)),0)=${date.getMonth() + 1})*(IFERROR(YEAR(DATEVALUE(Transactions!$A$2:$A$10000)),0)=${date.getFullYear()})*(Transactions!$A$2:$A$10000<>"")*(Transactions!$C$2:$C$10000))`,
        `=SUMPRODUCT((IFERROR(MONTH(DATEVALUE(Transactions!$A$2:$A$10000)),0)=${date.getMonth() + 1})*(IFERROR(YEAR(DATEVALUE(Transactions!$A$2:$A$10000)),0)=${date.getFullYear()})*(Transactions!$A$2:$A$10000<>""))`,
        `=IFERROR(B${rowNum}/DAY(EOMONTH(DATE(${date.getFullYear()},${date.getMonth() + 1},1),0)),0)`
      ]);
    }
    
    sheet.getRange(startRow + 2, 1, 6, 4).setValues(months);
    sheet.getRange(startRow + 2, 2, 6, 1).setNumberFormat(AMOUNT_FORMAT);
    sheet.getRange(startRow + 2, 4, 6, 1).setNumberFormat(AMOUNT_FORMAT);
    
    // Add sparkline for trend
    sheet.getRange(startRow + 1, 5).setValue('Trend');
    sheet.getRange(startRow + 1, 5).setFontWeight('bold').setBackground('#f3f3f3');
    sheet.getRange(startRow + 2, 5, 6, 1).merge();
    sheet.getRange(startRow + 2, 5).setFormula(`=SPARKLINE(B${startRow + 2}:B${startRow + 7},{"charttype","line";"linewidth",2;"color","#4285f4"})`);
    
    // Add borders
    sheet.getRange(startRow + 1, 1, 7, 5).setBorder(true, true, true, true, true, true);
    
    // Return next available row (1 header + 7 rows + 2 gap for safety)
    return startRow + 1 + 7 + 2;
  }
  
  createBudgetComparison(sheet, startRow) {
    // Section header
    sheet.getRange(startRow, 1, 1, 8).merge();
    sheet.getRange(startRow, 1).setValue('🎯 Budget vs Actual (Selected Month)');
    this.formatSectionHeader(sheet, startRow);
    
    // Instructions
    sheet.getRange(startRow + 1, 1, 1, 5).merge();
    sheet.getRange(startRow + 1, 1).setValue('Edit the Budget column to set your monthly budget targets');
    sheet.getRange(startRow + 1, 1).setFontSize(9).setFontStyle('italic').setBackground('#fff3cd');
    
    // Headers
    sheet.getRange(startRow + 2, 1, 1, 5).setValues([['Category', 'Budget', 'Actual', 'Difference', 'Status']]);
    sheet.getRange(startRow + 2, 1, 1, 5).setFontWeight('bold').setBackground('#f3f3f3');
    
    // Budget categories
    const budgetRows = [
      ['Food & Dining', 10000, `=SUMIF('Category Data'!A:A,"Food & Dining",'Category Data'!B:B)`, `=B${startRow + 3}-C${startRow + 3}`, `=IF(D${startRow + 3}>=0,"✓ Under","✗ Over")`],
      ['Transportation', 5000, `=SUMIF('Category Data'!A:A,"Transportation",'Category Data'!B:B)`, `=B${startRow + 4}-C${startRow + 4}`, `=IF(D${startRow + 4}>=0,"✓ Under","✗ Over")`],
      ['Shopping', 15000, `=SUMIF('Category Data'!A:A,"Shopping",'Category Data'!B:B)`, `=B${startRow + 5}-C${startRow + 5}`, `=IF(D${startRow + 5}>=0,"✓ Under","✗ Over")`],
      ['Groceries', 8000, `=SUMIF('Category Data'!A:A,"Groceries",'Category Data'!B:B)`, `=B${startRow + 6}-C${startRow + 6}`, `=IF(D${startRow + 6}>=0,"✓ Under","✗ Over")`],
      ['Entertainment', 3000, `=SUMIF('Category Data'!A:A,"Entertainment",'Category Data'!B:B)`, `=B${startRow + 7}-C${startRow + 7}`, `=IF(D${startRow + 7}>=0,"✓ Under","✗ Over")`],
      ['Bills & Utilities', 5000, `=SUMIF('Category Data'!A:A,"Bills & Utilities",'Category Data'!B:B)`, `=B${startRow + 8}-C${startRow + 8}`, `=IF(D${startRow + 8}>=0,"✓ Under","✗ Over")`],
      ['Other', 5000, `=SUMIF('Category Data'!A:A,"Other",'Category Data'!B:B)`, `=B${startRow + 9}-C${startRow + 9}`, `=IF(D${startRow + 9}>=0,"✓ Under","✗ Over")`]
    ];
    
    sheet.getRange(startRow + 3, 1, budgetRows.length, 5).setValues(budgetRows);
    
    // Format currency
    sheet.getRange(startRow + 3, 2, budgetRows.length, 3).setNumberFormat(AMOUNT_FORMAT);
    
    // Total row
    sheet.getRange(startRow + 3 + budgetRows.length, 1).setValue('TOTAL');
    sheet.getRange(startRow + 3 + budgetRows.length, 1).setFontWeight('bold');
    sheet.getRange(startRow + 3 + budgetRows.length, 2).setFormula(`=SUM(B${startRow + 3}:B${startRow + 3 + budgetRows.length - 1})`);
    sheet.getRange(startRow + 3 + budgetRows.length, 3).setFormula(`=SUM(C${startRow + 3}:C${startRow + 3 + budgetRows.length - 1})`);
    sheet.getRange(startRow + 3 + budgetRows.length, 4).setFormula(`=SUM(D${startRow + 3}:D${startRow + 3 + budgetRows.length - 1})`);
    sheet.getRange(startRow + 3 + budgetRows.length, 2, 1, 3).setNumberFormat(AMOUNT_FORMAT);
    sheet.getRange(startRow + 3 + budgetRows.length, 1, 1, 5).setBackground('#f3f3f3').setFontWeight('bold');
    
    // Add borders
    sheet.getRange(startRow + 2, 1, budgetRows.length + 2, 5).setBorder(true, true, true, true, true, true);
    
    // Add conditional formatting for status
    const statusRange = sheet.getRange(startRow + 3, 5, budgetRows.length, 1);
    const rule1 = SpreadsheetApp.newConditionalFormatRule()
      .whenTextContains('✓')
      .setBackground('#d4edda')
      .setRanges([statusRange])
      .build();
    const rule2 = SpreadsheetApp.newConditionalFormatRule()
      .whenTextContains('✗')
      .setBackground('#f8d7da')
      .setRanges([statusRange])
      .build();
    const rules = sheet.getConditionalFormatRules();
    rules.push(rule1);
    rules.push(rule2);
    sheet.setConditionalFormatRules(rules);
    
    // Return next available row (1 header + 1 instruction + 1 header row + budgetRows + 1 total row)
    return startRow + 1 + 1 + 1 + budgetRows.length + 1;
  }
  
  formatSectionHeader(sheet, row) {
    sheet.getRange(row, 1).setFontSize(12).setFontWeight('bold');
    sheet.getRange(row, 1).setBackground('#34a853').setFontColor('#ffffff');
    sheet.setRowHeight(row, 30);
  }
  
  createCategoryDataSheet() {
    try {
      let categorySheet = this.spreadsheet.getSheetByName('Category Data');
      
      if (categorySheet) {
        categorySheet.clear();
      } else {
        categorySheet = this.spreadsheet.insertSheet('Category Data');
      }
      
      // Headers
      categorySheet.getRange(1, 1, 1, 3).setValues([['Category', 'Amount', 'Merchant']]);
      categorySheet.getRange(1, 1, 1, 3).setFontWeight('bold').setBackground('#f3f3f3');
      
      // Get transaction data
      const transSheet = this.spreadsheet.getSheetByName(SHEET_NAME);
      if (!transSheet) {
        this.logger.warn('Transactions sheet not found');
        return;
      }
      
      const lastRow = transSheet.getLastRow();
      if (lastRow <= 1) {
        this.logger.warn('No transactions found in sheet');
        return;
      }
      
      this.logger.info(`Found ${lastRow - 1} transactions in sheet`);
      
      // Get selected month from dashboard
      const dashboard = this.spreadsheet.getSheetByName(this.dashboardName);
      const selectedDateStr = dashboard ? dashboard.getRange('B2').getValue() : null;
      
      // Parse selected month and year from yyyy-mm-dd format
      let selectedMonth, selectedYear;
      if (selectedDateStr) {
        const selectedDate = new Date(selectedDateStr);
        selectedMonth = selectedDate.getMonth();
        selectedYear = selectedDate.getFullYear();
      } else {
        const now = new Date();
        selectedMonth = now.getMonth();
        selectedYear = now.getFullYear();
      }
      
      this.logger.info(`Filtering for month: ${selectedMonth + 1}, year: ${selectedYear}`);
      
      // Get all transactions for selected month
      const lastCol = transSheet.getLastColumn();
      const transactions = transSheet.getRange(2, 1, lastRow - 1, Math.max(lastCol, 8)).getValues();
      const categoryData = [];
      
      let processedCount = 0;
      let matchedCount = 0;
      
      transactions.forEach((row, index) => {
        const date = row[0]; // Column A (Date)
        const amount = row[2]; // Column C (Amount)
        const merchant = row[5]; // Column F (Merchant/Description)
        
        processedCount++;
        
        // Skip if no date or amount or merchant
        if (!date || !amount || !merchant) {
          return;
        }
        
        // Handle both Date objects and text dates
        let transDate;
        try {
          if (date instanceof Date) {
            transDate = date;
          } else if (typeof date === 'string' && date.trim() !== '') {
            // Try to parse DD-MMM-YYYY format (e.g., "01-Dec-2025")
            transDate = new Date(date);
          } else {
            return; // Skip invalid dates
          }
          
          // Check if date is valid
          if (isNaN(transDate.getTime())) {
            return;
          }
          
          const transMonth = transDate.getMonth();
          const transYear = transDate.getFullYear();
          
          if (transMonth === selectedMonth && transYear === selectedYear) {
            matchedCount++;
            const category = CategoryMapper.categorize(merchant);
            categoryData.push([category, amount, merchant]);
          }
        } catch (e) {
          // Skip invalid dates
          return;
        }
      });
      
      this.logger.info(`Processed ${processedCount} rows, matched ${matchedCount} transactions`);
      
      if (categoryData.length > 0) {
        categorySheet.getRange(2, 1, categoryData.length, 3).setValues(categoryData);
        this.logger.success(`Added ${categoryData.length} transactions to Category Data sheet`);
      } else {
        this.logger.warn(`No transactions found for the selected month`);
      }
      
      // Hide this sheet (it's just for data processing)
      categorySheet.hideSheet();
      
      const monthName = Utilities.formatDate(new Date(selectedYear, selectedMonth, 1), getTimezone(), 'MMM-yyyy');
      this.logger.info(`Category data created for ${monthName}`);
      
    } catch (error) {
      this.logger.error(`Error creating category data: ${error.message}`);
      throw error;
    }
  }
}

/**
 * Create or update the analytics dashboard
 * Run this function to generate/refresh the dashboard with latest data
 * 
 * Creates:
 * - Monthly summary with key metrics
 * - Top 5 merchants analysis
 * - Account-wise spending breakdown
 * - Category-based spending analysis
 * - 6-month spending trends
 * - Budget vs Actual comparison
 */
function updateDashboard() {
  const logger = new Logger(LOG_LEVEL);
  
  try {
    logger.section('Updating Dashboard');
    
    const spreadsheet = SpreadsheetApp.openById(SHEET_ID);
    const dashboardManager = new DashboardManager(spreadsheet, logger);
    
    // Create category data first
    dashboardManager.createCategoryDataSheet();
    
    // Create/update dashboard
    dashboardManager.createOrUpdateDashboard();
    
    logger.success('Dashboard updated successfully!');
    logger.info('Open the "Dashboard" sheet to view your analytics');
    
    // Show toast notification (only if run from spreadsheet UI)
    try {
      const activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
      if (activeSpreadsheet) {
        activeSpreadsheet.toast(
          'Your analytics dashboard has been updated with the latest data!',
          'Dashboard Updated',
          5
        );
      }
    } catch (e) {
      // Ignore toast errors (happens when run from script editor)
    }
    
  } catch (error) {
    logger.error(`Error updating dashboard: ${error.message}`);
    throw error;
  }
}

/**
 * MENU: Add custom menu to spreadsheet
 * This runs automatically when the spreadsheet is opened
 */
function onOpen() {
  try {
    const ui = SpreadsheetApp.getUi();
    ui.createMenu('📊 TransacFlow')
      .addItem('🔄 Refresh Dashboard', 'menu_RefreshDashboard')
      .addItem('📧 Process New Emails', 'processTransactionEmails')
      .addItem('📈 Update Full Dashboard', 'updateDashboard')
      .addToUi();
    console.log('TransacFlow menu created successfully');
  } catch (error) {
    console.error('Error creating menu:', error.message);
  }
}

/**
 * MANUAL REFRESH: Refresh dashboard data for selected month
 * Run this from script editor if menu doesn't work
 * This is the same as menu_RefreshDashboard but works from script editor
 */
function manualRefreshDashboard() {
  const logger = new Logger('INFO');
  
  try {
    logger.section('Manual Dashboard Refresh');
    
    const spreadsheet = SpreadsheetApp.openById(SHEET_ID);
    logger.info(`Spreadsheet: ${spreadsheet.getName()}`);
    
    const dashboard = spreadsheet.getSheetByName('Dashboard');
    
    if (!dashboard) {
      logger.error('Dashboard sheet not found. Run updateDashboard() first.');
      return;
    }
    
    const selectedMonth = dashboard.getRange('B2').getValue();
    logger.info(`Selected month: ${selectedMonth}`);
    
    const dashboardManager = new DashboardManager(spreadsheet, logger);
    
    // Update month dropdown with any new months
    logger.info('Updating month dropdown...');
    dashboardManager.updateMonthDropdown(dashboard, selectedMonth);
    
    // Regenerate category data
    logger.info('Regenerating Category Data sheet...');
    dashboardManager.createCategoryDataSheet();
    
    // Update timestamp
    const now = new Date().toLocaleString();
    dashboard.getRange('C2').setValue('Last Updated: ' + now);
    logger.info(`Updated timestamp: ${now}`);
    
    logger.success('Dashboard refreshed successfully!');
    logger.info('Check your Dashboard sheet - data should be updated');
    
  } catch (error) {
    logger.error(`Refresh failed: ${error.message}`);
    console.error('Stack:', error.stack);
  }
}

/**
 * MENU FUNCTION: Refresh dashboard for currently selected month
 * This is called from the custom menu
 */
function menu_RefreshDashboard() {
  const ui = SpreadsheetApp.getUi();
  
  try {
    const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    const dashboard = spreadsheet.getSheetByName('Dashboard');
    
    if (!dashboard) {
      ui.alert('Error', 'Dashboard sheet not found. Please run "Update Full Dashboard" first.', ui.ButtonSet.OK);
      return;
    }
    
    const selectedMonth = dashboard.getRange('B2').getValue();
    
    // Show processing message
    spreadsheet.toast('Refreshing dashboard data...', 'Please Wait', -1);
    
    // Force INFO level for menu actions (ignore LOG_LEVEL)
    const logger = new Logger('INFO');
    logger.info(`Refreshing dashboard for month: ${selectedMonth}`);
    
    const dashboardManager = new DashboardManager(spreadsheet, logger);
    
    // Update month dropdown with any new months
    dashboardManager.updateMonthDropdown(dashboard, selectedMonth);
    
    // Refresh category data
    dashboardManager.createCategoryDataSheet();
    
    // Update timestamp in dashboard
    const now = new Date().toLocaleString();
    dashboard.getRange('C2').setValue('Last Updated: ' + now);
    
    spreadsheet.toast('Dashboard refreshed successfully!', 'Success', 3);
    logger.success('Dashboard refresh completed');
    
  } catch (error) {
    ui.alert('Error', `Failed to refresh dashboard: ${error.message}`, ui.ButtonSet.OK);
    console.error('Refresh error:', error);
  }
}

/**
 * TRIGGER: Auto-update dashboard when month is changed
 * This function is automatically called by Google Sheets when any cell is edited
 * It detects changes to the month selector dropdown (cell B2 in Dashboard sheet)
 * and refreshes the category data to match the newly selected month
 */
function onEdit(e) {
  try {
    // Always log to see if trigger fires
    console.log('[onEdit] Trigger fired');
    
    // Check if the edit event exists and has required properties
    if (!e || !e.range) {
      console.log('[onEdit] No event or range, exiting');
      return;
    }
    
    const range = e.range;
    const sheet = range.getSheet();
    const sheetName = sheet.getName();
    const cellAddress = range.getA1Notation();
    
    // Always log the edit details
    console.log(`[onEdit] Sheet: ${sheetName}, Cell: ${cellAddress}`);
    
    // Check if edit is in Dashboard sheet, cell B2 (month selector)
    if (sheetName === 'Dashboard' && cellAddress === 'B2') {
      console.log('[onEdit] Dashboard month changed detected!');
      
      // Use active spreadsheet (more reliable for triggers)
      const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
      
      if (!spreadsheet) {
        console.error('[onEdit] Could not get active spreadsheet!');
        return;
      }
      
      const newMonth = range.getValue();
      console.log(`[onEdit] New month selected: ${newMonth}`);
      
      // Show "updating" message immediately
      try {
        const dashboard = sheet;
        dashboard.getRange('C2').setValue('Updating...');
        spreadsheet.toast('Refreshing dashboard data...', 'Please Wait', 2);
      } catch (e) {
        // Ignore if can't update
      }
      
      // Force INFO level for triggers (ignore global LOG_LEVEL)
      const logger = new Logger('INFO');
      logger.info('Month changed in dashboard, updating category data...');
      
      const dashboardManager = new DashboardManager(spreadsheet, logger);
      dashboardManager.createCategoryDataSheet();
      
      logger.success('Dashboard data refreshed for selected month!');
      console.log('[onEdit] Category data updated successfully');
      
      // Update timestamp to show refresh happened
      try {
        const now = new Date().toLocaleString();
        sheet.getRange('C2').setValue('Last Updated: ' + now);
        
        spreadsheet.toast(
          'Dashboard refreshed for ' + newMonth,
          'Success',
          2
        );
        console.log('[onEdit] Dashboard timestamp updated');
      } catch (toastError) {
        console.error('[onEdit] Error updating timestamp:', toastError.message);
      }
      
      return; // Exit after handling dashboard
    }
    
    // Handle Account Nicknames sheet edits (existing functionality)
    const nicknamesSheetName = typeof NICKNAMES_SHEET_NAME !== 'undefined' ? NICKNAMES_SHEET_NAME : 'Account Nicknames';
    if (sheetName === nicknamesSheetName) {
      console.log('[onEdit] Account Nicknames sheet edited');
      const logger = new Logger('INFO');
      const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
      const nicknameManager = new NicknameManager(spreadsheet, logger);
      nicknameManager.updateTransactionsWithNicknames();
    }
    
    console.log('[onEdit] Completed, no action taken for this edit');
    
  } catch (error) {
    // Log error details for debugging
    console.error('[onEdit] ERROR:', error.message);
    console.error('[onEdit] Stack:', error.stack);
    
    // Show error to user
    try {
      SpreadsheetApp.getActiveSpreadsheet().toast(
        `Error updating dashboard: ${error.message}`,
        'Dashboard Update Failed',
        5
      );
    } catch (toastError) {
      // Can't show toast, just log
      console.error('[onEdit] Could not show error toast');
    }
  }
}

/**
 * DEBUG: Test the dashboard refresh manually
 * Run this to see if the refresh logic works without the trigger
 */
function debug_DashboardRefresh() {
  const logger = new Logger('DEBUG');
  
  try {
    logger.section('Testing Dashboard Refresh');
    
    // Use SHEET_ID from config (works when run from script editor)
    logger.info(`Using SHEET_ID: ${SHEET_ID}`);
    const spreadsheet = SpreadsheetApp.openById(SHEET_ID);
    logger.info(`Spreadsheet: ${spreadsheet.getName()}`);
    
    const dashboard = spreadsheet.getSheetByName('Dashboard');
    if (!dashboard) {
      logger.error('Dashboard sheet not found!');
      logger.info('Available sheets:');
      spreadsheet.getSheets().forEach(sheet => {
        logger.info(`  - ${sheet.getName()}`);
      });
      return;
    }
    
    const selectedMonth = dashboard.getRange('B2').getValue();
    logger.info(`Selected month in B2: ${selectedMonth}`);
    logger.info(`Selected month type: ${typeof selectedMonth}`);
    
    logger.info('Creating DashboardManager...');
    const dashboardManager = new DashboardManager(spreadsheet, logger);
    
    logger.info('Calling createCategoryDataSheet...');
    dashboardManager.createCategoryDataSheet();
    
    logger.success('Dashboard refresh completed!');
    
    // Check if Category Data sheet exists
    const categorySheet = spreadsheet.getSheetByName('Category Data');
    if (categorySheet) {
      const lastRow = categorySheet.getLastRow();
      logger.info(`Category Data sheet has ${lastRow} rows`);
      
      // Show first few rows of data
      if (lastRow > 1) {
        logger.info('Sample data from Category Data sheet:');
        const sampleData = categorySheet.getRange(2, 1, Math.min(5, lastRow - 1), 3).getValues();
        sampleData.forEach((row, idx) => {
          logger.info(`  Row ${idx + 2}: Category="${row[0]}", Amount="${row[1]}", Merchant="${row[2]}"`);
        });
      } else {
        logger.warn('Category Data sheet is empty (only headers)');
      }
    } else {
      logger.warn('Category Data sheet not found!');
    }
    
    // Try to show toast (will only work if run from spreadsheet UI)
    try {
      SpreadsheetApp.getActiveSpreadsheet().toast(
        'Dashboard refresh test completed successfully!',
        'Test Complete',
        3
      );
    } catch (toastError) {
      logger.info('Toast notification skipped (running from script editor)');
    }
    
  } catch (error) {
    logger.error(`Test failed: ${error.message}`);
    console.error('Full error:', error);
    console.error('Stack:', error.stack);
  }
}

/**
 * DEBUG: Show what's in the execution log
 * Check the most recent executions
 */
function debug_CheckLastExecution() {
  console.log('Check View > Executions to see the logs');
  console.log('Look for [onEdit] prefix in the logs');
  console.log('If you see the trigger firing but no action, check:');
  console.log('1. Sheet name is exactly "Dashboard"');
  console.log('2. Cell is exactly "B2"');
  console.log('3. No errors in the execution logs');
}

/**
 * Add category column to existing transactions
 * Run this once to add categories to all your historical transactions
 */
function addCategoriesToTransactions() {
  const logger = new Logger(LOG_LEVEL);
  
  try {
    logger.section('Adding Categories to Transactions');
    
    const spreadsheet = SpreadsheetApp.openById(SHEET_ID);
    const transSheet = spreadsheet.getSheetByName(SHEET_NAME);
    
    if (!transSheet) {
      logger.error('Transactions sheet not found');
      return;
    }
    
    // Check if Category column already exists
    const headers = transSheet.getRange(1, 1, 1, transSheet.getLastColumn()).getValues()[0];
    let categoryCol = headers.indexOf('Category') + 1;
    
    if (categoryCol === 0) {
      // Add Category column after Merchant column (column 6)
      categoryCol = 7;
      transSheet.insertColumnAfter(6);
      transSheet.getRange(1, categoryCol).setValue('Category');
      transSheet.getRange(1, categoryCol).setFontWeight('bold').setBackground('#4285f4').setFontColor('#ffffff');
      logger.info('Added Category column');
    }
    
    // Get all transactions
    const lastRow = transSheet.getLastRow();
    if (lastRow <= 1) {
      logger.info('No transactions to categorize');
      return;
    }
    
    const merchants = transSheet.getRange(2, 6, lastRow - 1, 1).getValues(); // Column F (Merchant)
    const categories = merchants.map(row => [CategoryMapper.categorize(row[0])]);
    
    // Write categories
    transSheet.getRange(2, categoryCol, categories.length, 1).setValues(categories);
    
    logger.success(`Categorized ${categories.length} transactions`);
    
  } catch (error) {
    logger.error(`Error adding categories: ${error.message}`);
    throw error;
  }
}

// ==================== UTILITY FUNCTIONS ====================

/**
 * Manually refresh all account nicknames in the Transactions sheet
 * Run this if you want to update all existing transactions with latest nicknames
 * 
 * Use when: You've edited multiple nicknames and want to apply them all at once
 */
function refreshAllNicknames() {
  const logger = new Logger(LOG_LEVEL);
  
  try {
    logger.section('Refreshing All Account Nicknames');
    
    const spreadsheet = SpreadsheetApp.openById(SHEET_ID);
    const nicknameManager = new NicknameManager(spreadsheet, logger);
    nicknameManager.initialize();
    
    const transSheet = spreadsheet.getSheetByName(SHEET_NAME);
    if (!transSheet) {
      logger.error('Transactions sheet not found');
      return;
    }
    
    const lastRow = transSheet.getLastRow();
    if (lastRow <= 1) {
      logger.info('No transactions to update');
      return;
    }
    
    // Get all transaction data
    const dataRange = transSheet.getRange(2, 1, lastRow - 1, 9);
    const data = dataRange.getValues();
    
    let updatedCount = 0;
    
    // Update each row with latest nickname
    for (let i = 0; i < data.length; i++) {
      const accountNumber = data[i][3]; // Column 4 (index 3) is Account/Card/UPI
      const source = data[i][7]; // Column 8 (index 7) is Source
      
      if (accountNumber) {
        const nickname = nicknameManager.getAccountNickname(accountNumber, source);
        if (nickname && data[i][4] !== nickname) {
          data[i][4] = nickname; // Column 5 (index 4) is Account Name
          updatedCount++;
        }
      }
    }
    
    if (updatedCount > 0) {
      dataRange.setValues(data);
      logger.success(`Updated ${updatedCount} transaction(s) with latest nicknames`);
    } else {
      logger.info('All nicknames are already up to date');
    }
    
  } catch (error) {
    logger.error(`Error refreshing nicknames: ${error.message}`);
  }
}

// ==================== DIAGNOSTIC FUNCTIONS ====================
// Use these to troubleshoot issues with email search and patterns

/**
 * DEBUG: Show raw email content for debugging patterns
 * 
 * What it shows:
 * - Raw email from, subject, body
 * - Useful for creating/debugging regex patterns
 * 
 * Use this to see exact email content
 */
function debug_ShowRawEmail() {
  const logger = new Logger('DEBUG');
  
  logger.section('Raw Email Content Debugging');
  
  const patterns = BankPatternManager.getAllPatterns();
  const queryBuilder = new QueryBuilder(logger);
  const searchQuery = SEARCH_QUERY_MODE === 'DYNAMIC'
    ? queryBuilder.buildFromPatterns(patterns, EMAIL_SEARCH_DAYS)
    : getSearchQuery();
  
  console.log(`Search Query: ${searchQuery}`);
  
  const threads = GmailApp.search(searchQuery, 0, 1);
  
  if (threads.length === 0) {
    console.log('❌ No emails found');
    return;
  }
  
  const messages = threads[0].getMessages();
  const message = messages[0];
  
  console.log('\n' + '='.repeat(60));
  console.log('FROM:');
  console.log(message.getFrom());
  
  console.log('\n' + '='.repeat(60));
  console.log('SUBJECT:');
  console.log(message.getSubject());
  
  console.log('\n' + '='.repeat(60));
  console.log('DATE:');
  console.log(message.getDate());
  
  // Check plain body
  console.log('\n' + '='.repeat(60));
  console.log('PLAIN BODY:');
  let plainBody = '';
  try {
    plainBody = message.getPlainBody();
    console.log(plainBody ? plainBody.substring(0, 1000) : '❌ NULL/EMPTY');
    console.log(`Length: ${plainBody ? plainBody.length : 0}`);
  } catch (e) {
    console.log(`❌ Error getting plain body: ${e.message}`);
  }
  
  // Check HTML body
  console.log('\n' + '='.repeat(60));
  console.log('HTML BODY (First 500 chars):');
  let htmlBody = '';
  try {
    htmlBody = message.getBody();
    console.log(htmlBody ? htmlBody.substring(0, 500) : '❌ NULL/EMPTY');
    console.log(`Length: ${htmlBody ? htmlBody.length : 0}`);
  } catch (e) {
    console.log(`❌ Error getting HTML body: ${e.message}`);
  }
  
  // Try extraction
  console.log('\n' + '='.repeat(60));
  console.log('EXTRACTED CONTENT (what TransacFlow will use):');
  
  let body = plainBody;
  const isPlainEmpty = !body || body.trim() === '' || body.trim().toLowerCase() === 'null';
  
  if (isPlainEmpty) {
    console.log('⚠️ Plain body is empty/null (detected literal "null" string), extracting from HTML...');
    
    if (htmlBody && htmlBody.trim() !== '') {
      const emailService = new EmailService(logger);
      body = emailService.stripHtmlTags(htmlBody);
      console.log(`✓ Successfully extracted from HTML (${body.length} chars)`);
    } else {
      console.log('❌ HTML body is also empty/null!');
      body = '';
    }
  } else {
    console.log('✓ Using plain body');
  }
  
  console.log('\n' + '-'.repeat(60));
  console.log('FINAL CONTENT:');
  console.log(body ? body.substring(0, 1000) : '❌ EMPTY');
  console.log(`\nFinal Body Length: ${body ? body.length : 0}`);
  console.log('='.repeat(60));
}

/**
 * DEBUG: Diagnose email search issues
 * 
 * What it shows:
 * - All loaded bank patterns
 * - Current search query being used
 * - Sample emails found (up to 5)
 * - Troubleshooting suggestions if no emails found
 * 
 * Use this when emails aren't being found
 */
function debug_EmailSearch() {
  const logger = new Logger('DEBUG');
  const patterns = BankPatternManager.getAllPatterns();
  
  logger.section('Email Search Diagnostics');
  logger.info(`Bank Patterns Loaded: ${patterns.length}`);
  patterns.forEach(p => logger.info(`  - ${p.name}`));
  
  const searchQuery = SEARCH_QUERY_MODE === 'DYNAMIC'
    ? new QueryBuilder(logger).buildFromPatterns(patterns, EMAIL_SEARCH_DAYS)
    : getSearchQuery();
  
  logger.info(`\nActive Query: ${searchQuery}`);
  logger.info(`Mode: ${SEARCH_QUERY_MODE}`);
  logger.info(`Search Days: ${EMAIL_SEARCH_DAYS}`);
  
  try {
    const threads = GmailApp.search(searchQuery, 0, 10);
    logger.success(`\nFound ${threads.length} email threads`);
    
    if (threads.length > 0) {
      logger.section('Sample Emails Found');
      threads.slice(0, 5).forEach((thread, index) => {
        const message = thread.getMessages()[0];
        logger.info(`${index + 1}. From: ${message.getFrom()}`);
        logger.info(`   Subject: ${message.getSubject()}`);
        logger.info(`   Date: ${message.getDate()}`);
      });
    } else {
      logger.warn('\nNo emails found. Try:');
      logger.warn('  1. Check if you have transaction emails in last ${EMAIL_SEARCH_DAYS} days');
      logger.warn('  2. Increase EMAIL_SEARCH_DAYS in Config.gs');
      logger.warn('  3. Verify bank patterns match your email format');
    }
  } catch (error) {
    logger.error(`Search failed: ${error.message}`);
  }
}

/**
 * DEBUG: Check dashboard data and formulas
 * 
 * Use this to troubleshoot why dashboard shows zeros
 */
function debug_DashboardData() {
  const logger = new Logger('DEBUG');
  
  try {
    logger.section('Dashboard Data Diagnostics');
    
    const spreadsheet = SpreadsheetApp.openById(SHEET_ID);
    const transSheet = spreadsheet.getSheetByName(SHEET_NAME);
    const dashboard = spreadsheet.getSheetByName('Dashboard');
    
    if (!transSheet) {
      logger.error('Transactions sheet not found!');
      return;
    }
    
    if (!dashboard) {
      logger.error('Dashboard sheet not found! Run updateDashboard() first.');
      return;
    }
    
    // Check transactions
    const lastRow = transSheet.getLastRow();
    logger.info(`Transactions sheet has ${lastRow - 1} transactions`);
    
    if (lastRow > 1) {
      // Sample first few transactions
      const sampleData = transSheet.getRange(2, 1, Math.min(5, lastRow - 1), 8).getValues();
      logger.info('\nSample Transactions:');
      sampleData.forEach((row, idx) => {
        logger.info(`  ${idx + 1}. Date: ${row[0]} | Amount: ${row[2]} | Merchant: ${row[5]}`);
      });
    }
    
    // Check selected month
    const selectedMonth = dashboard.getRange('B2').getValue();
    logger.info(`\nSelected Month in Dashboard: ${selectedMonth}`);
    
    // Check if dates match
    if (lastRow > 1) {
      const allDates = transSheet.getRange(2, 1, lastRow - 1, 1).getValues();
      const matchingCount = allDates.filter(row => {
        const dateStr = row[0] ? row[0].toString() : '';
        return dateStr.includes(selectedMonth);
      }).length;
      
      logger.info(`Transactions matching "${selectedMonth}": ${matchingCount}`);
      
      if (matchingCount === 0) {
        logger.warn('\n⚠️ No transactions match the selected month!');
        logger.info('Date formats in sheet:');
        allDates.slice(0, 5).forEach((row, idx) => {
          logger.info(`  ${idx + 1}. "${row[0]}"`);
        });
      }
    }
    
    // Check formulas
    logger.info('\nDashboard Formulas:');
    const formula1 = dashboard.getRange('B5').getFormula();
    const formula2 = dashboard.getRange('B6').getFormula();
    logger.info(`  Total Transactions: ${formula1}`);
    logger.info(`  Total Spent: ${formula2}`);
    
    // Check Category Data sheet
    const categorySheet = spreadsheet.getSheetByName('Category Data');
    if (categorySheet) {
      try {
        categorySheet.showSheet(); // Temporarily unhide
        const catLastRow = categorySheet.getLastRow();
        logger.info(`\nCategory Data sheet has ${catLastRow - 1} entries`);
        if (catLastRow > 1) {
          const catSample = categorySheet.getRange(2, 1, Math.min(3, catLastRow - 1), 3).getValues();
          logger.info('Sample Category Data:');
          catSample.forEach((row, idx) => {
            logger.info(`  ${idx + 1}. Category: ${row[0]} | Amount: ${row[1]} | Merchant: ${row[2]}`);
          });
        }
        categorySheet.hideSheet(); // Hide again
      } catch (e) {
        logger.warn(`Could not access Category Data: ${e.message}`);
      }
    } else {
      logger.warn('Category Data sheet not found!');
    }
    
    logger.section('Recommendations');
    if (lastRow <= 1) {
      logger.info('1. Run processTransactionEmails() to import transactions');
    } else {
      logger.info('1. Verify date format in Transactions sheet matches dropdown format');
      logger.info('2. Try selecting current month in dropdown');
      logger.info('3. Run updateDashboard() to refresh all data');
    }
    
  } catch (error) {
    logger.error(`Error in diagnostics: ${error.message}`);
  }
}

/**
 * DEBUG: Show current search query and loaded banks
 * 
 * What it shows:
 * - Search mode (DYNAMIC or MANUAL)
 * - Complete search query
 * - List of all loaded bank patterns
 * 
 * Use this to verify which banks are being searched for
 */
function debug_SearchQuery() {
  const logger = new Logger('INFO');
  const queryBuilder = new QueryBuilder(logger);
  const patterns = BankPatternManager.getAllPatterns();
  
  logger.section('Current Search Query');
  logger.info(`Total Bank Patterns Loaded: ${patterns.length}`);
  
  if (SEARCH_QUERY_MODE === 'DYNAMIC') {
    const query = queryBuilder.buildFromPatterns(patterns, EMAIL_SEARCH_DAYS);
    logger.info('Mode: DYNAMIC (auto-generated from bank patterns)');
    logger.info(`Query: ${query}`);
    logger.info('\nLoaded Banks:');
    patterns.forEach(p => logger.info(`  - ${p.name}`));
  } else {
    logger.info('Mode: MANUAL');
    logger.info(`Query: ${getSearchQuery()}`);
  }
}