Skip to content

A local-first personal finance app

15.4 KB
Raw Blame
import { beforeEach, describe, expect, it } from 'vitest';

import type { TransactionForRules } from '#server/transactions/transaction-rules';
import { getCurrency } from '#shared/currencies';
import { setCachedUserPreferences } from '#shared/formulas/customFunctions';

import { Action } from './action';

describe('Formula-based rule actions', () => {
  beforeEach(() => {
    setCachedUserPreferences({
      currency: getCurrency('USD'),
      numberFormat: 'comma-dot',
      decimalPlaces: 2,
      thousandsSeparator: ',',
      decimalSeparator: '.',
      locale: 'en-US',
      currencySymbolPosition: 'before',
      currencySpaceBetweenAmountAndSymbol: false,
    });
  });

  it('should execute a simple math formula', () => {
    const action = new Action('set', 'amount', null, {});
    const transaction: Partial<TransactionForRules> = { amount: 500 };
    const result = action.executeFormulaSync('=100 + 200', transaction);

    expect(result).toBe(300);
  });

  it('should use transaction field variables', () => {
    const action = new Action('set', 'notes', null, {});
    const transaction = { amount: 5000 };
    const result = action.executeFormulaSync('=amount', transaction);

    expect(result).toBe(50);
  });

  it('should provide amount and parent_amount as amounts, not integers', () => {
    const action = new Action('set', 'notes', null, {});
    const transaction: Partial<TransactionForRules> = {
      amount: -4567,
      parent_amount: 20000,
    };

    expect(action.executeFormulaSync('=amount', transaction)).toBe(-45.67);
    expect(action.executeFormulaSync('=parent_amount * 0.5', transaction)).toBe(
      100,
    );
  });

  it('should leave non-monetary numeric fields alone', () => {
    const action = new Action('set', 'notes', null, {});
    const transaction: Partial<TransactionForRules> = {
      amount: 100,
      sort_order: 1234,
    };

    expect(action.executeFormulaSync('=sort_order', transaction)).toBe(1234);
  });

  it('should support IF function with transaction fields', () => {
    const action = new Action('set', 'notes', null, {});

    const transaction1 = { amount: 1000 };
    const result1 = action.executeFormulaSync(
      '=IF(amount > 0, "Income", "Expense")',
      transaction1,
    );
    expect(result1).toBe('Income');

    const transaction2 = { amount: -1000 };
    const result2 = action.executeFormulaSync(
      '=IF(amount > 0, "Income", "Expense")',
      transaction2,
    );
    expect(result2).toBe('Expense');
  });

  it('should support UPPER string function', () => {
    const action = new Action('set', 'notes', null, {});
    const transaction = { notes: 'hello world' };
    const result = action.executeFormulaSync('=UPPER(notes)', transaction);

    expect(result).toBe('HELLO WORLD');
  });

  it('should support CONCATENATE function', () => {
    const action = new Action('set', 'notes', null, {});
    const transaction = {
      imported_payee: 'Store Name',
      notes: 'Purchase',
    };
    const result = action.executeFormulaSync(
      '=CONCATENATE(imported_payee, " - ", notes)',
      transaction,
    );

    expect(result).toBe('Store Name - Purchase');
  });

  it('should support LEFT function', () => {
    const action = new Action('set', 'notes', null, {});
    const transaction = { imported_payee: 'Store Name' };
    const result = action.executeFormulaSync(
      '=LEFT(imported_payee, 5)',
      transaction,
    );

    expect(result).toBe('Store');
  });

  it('should provide today variable', () => {
    const action = new Action('set', 'date', null, {});
    const transaction = { date: '2024-01-01' };
    const result = action.executeFormulaSync(
      '=TEXT(today,"YYYY-MM-DD")',
      transaction,
    );

    // Should be a date string in YYYY-MM-DD format
    expect(typeof result).toBe('string');
    expect(result).toMatch(/^\d{4}-\d{2}-\d{2}$/);
  });

  it('should throw error for invalid formula', () => {
    const action = new Action('set', 'notes', null, {});
    const transaction = { amount: 100 };

    expect(() => {
      action.executeFormulaSync('=INVALID_FUNCTION()', transaction);
    }).toThrow();
  });

  it('should throw error for formula without = prefix', () => {
    const action = new Action('set', 'notes', null, {});
    const transaction = { amount: 100 };

    expect(() => {
      action.executeFormulaSync('amount + 100', transaction);
    }).toThrow('Formula must start with =');
  });

  it('should support balance field variable', () => {
    const action = new Action('set', 'notes', null, {});
    const transaction: Partial<TransactionForRules> = {
      balance: 1500,
      notes: 'original',
    };
    const result = action.executeFormulaSync('=balance * 2', transaction);

    expect(result).toBe(30);
  });

  it('should support BALANCE_OF with prefetched map', () => {
    const action = new Action('set', 'notes', null, {});
    const transaction: Partial<TransactionForRules> = {
      notes: 'original',
      _balanceOfPrefetched: new Map([
        ['Savings', 50000],
        ['550e8400-e29b-41d4-a716-446655440000', 1200],
      ]),
    };
    const byName = action.executeFormulaSync(
      '=BALANCE_OF("Savings") + 100',
      transaction,
    );
    expect(byName).toBe(600);

    const byId = action.executeFormulaSync(
      '=BALANCE_OF("550e8400-e29b-41d4-a716-446655440000")',
      transaction,
    );
    expect(byId).toBe(12);
  });

  it('should return 0 for BALANCE_OF when literal missing from prefetch map', () => {
    const action = new Action('set', 'amount', null, {});
    const transaction: Partial<TransactionForRules> = {
      amount: 100,
      _balanceOfPrefetched: new Map(),
    };
    const result = action.executeFormulaSync(
      '=BALANCE_OF("Unknown")',
      transaction,
    );
    expect(result).toBe(0);
  });

  it('should execute formula and convert to number type', () => {
    const action = new Action('set', 'amount', null, {
      formula: '=500 + 250',
    });

    const transaction = { amount: 100 };
    action.exec(transaction);

    expect(transaction.amount).toBe(75000);
  });

  it('should round-trip the amount field through a formula', () => {
    const action = new Action('set', 'amount', null, { formula: '=amount' });

    const transaction = { amount: 12345 };
    action.exec(transaction);

    expect(transaction.amount).toBe(12345);
  });

  it('should apply a percentage to the amount field', () => {
    const action = new Action('set', 'amount', null, {
      formula: '=amount * 1.05',
    });

    const transaction = { amount: -10000 };
    action.exec(transaction);

    expect(transaction.amount).toBe(-10500);
  });

  it('should execute formula and convert to string type', () => {
    const action = new Action('set', 'notes', null, {
      formula: '=UPPER("hello")',
    });

    const transaction = { notes: 'original' };
    action.exec(transaction);

    expect(transaction.notes).toBe('HELLO');
  });

  it('should handle formula errors gracefully', () => {
    const action = new Action('set', 'notes', null, {
      formula: '=1/0', // Division by zero
    });

    const transaction = { notes: 'original' };

    // Should not throw, but keep original value on error
    expect(() => {
      action.exec(transaction);
    }).not.toThrow();

    // Original value should be preserved
    expect(transaction.notes).toBe('original');
  });

  it('should validate numeric field output', () => {
    const action = new Action('set', 'amount', null, {
      formula: '=UPPER("test")', // Returns string for numeric field
    });

    const transaction = { amount: 100 };
    action.exec(transaction);

    // Should keep original value when formula produces non-numeric result
    expect(transaction.amount).toBe(100);
  });

  it('should convert non-string results to string for text fields', () => {
    const action = new Action('set', 'notes', null, {
      formula: '=500 + 250', // Returns number
    });

    const transaction = { notes: 'original' };
    action.exec(transaction);

    // Text fields get the number the formula produced, not an amount scaled
    // up for storage
    expect(transaction.notes).toBe('750');
  });

  it('should format numbers with thousands separators using FORMATNUMBER', () => {
    const action = new Action('set', 'notes', null, {
      formula: '=FORMATNUMBER(1234567.89, 2)',
    });

    const transaction = { notes: 'original' };
    action.exec(transaction);

    expect(transaction.notes).toBe('1,234,567.89');
  });

  it('should format numbers using number-format preferences with FORMATNUMBER', () => {
    setCachedUserPreferences({
      currency: getCurrency('USD'),
      numberFormat: 'dot-comma',
      decimalPlaces: 2,
      thousandsSeparator: '.',
      decimalSeparator: ',',
      locale: 'en-US',
      currencySymbolPosition: 'before',
      currencySpaceBetweenAmountAndSymbol: false,
    });

    const action = new Action('set', 'notes', null, {
      formula: '=FORMATNUMBER(1234567.89, 2)',
    });

    const transaction = { notes: 'original' };
    action.exec(transaction);

    expect(transaction.notes).toBe('1.234.567,89');
  });

  it('should format numbers using decimal-place preferences with FORMATNUMBER', () => {
    setCachedUserPreferences({
      currency: getCurrency('JPY'),
      numberFormat: 'comma-dot',
      decimalPlaces: 0,
      thousandsSeparator: ',',
      decimalSeparator: '.',
      locale: 'en-US',
      currencySymbolPosition: 'before',
      currencySpaceBetweenAmountAndSymbol: false,
    });

    const action = new Action('set', 'notes', null, {
      formula: '=FORMATNUMBER(1234567.89)',
    });

    const transaction = { notes: 'original' };
    action.exec(transaction);

    expect(transaction.notes).toBe('1,234,568');
  });

  it('should let FORMATNUMBER decimals override decimal-place preferences', () => {
    setCachedUserPreferences({
      currency: getCurrency('JPY'),
      numberFormat: 'comma-dot',
      decimalPlaces: 0,
      thousandsSeparator: ',',
      decimalSeparator: '.',
      locale: 'en-US',
      currencySymbolPosition: 'before',
      currencySpaceBetweenAmountAndSymbol: false,
    });

    const action = new Action('set', 'notes', null, {
      formula: '=FORMATNUMBER(1234567.89, 2)',
    });

    const transaction = { notes: 'original' };
    action.exec(transaction);

    expect(transaction.notes).toBe('1,234,567.89');
  });

  it('should format numbers with custom separators using FORMATNUMBER', () => {
    const action = new Action('set', 'notes', null, {
      formula: '=FORMATNUMBER(1234567.89, 2, ".", ",")',
    });

    const transaction = { notes: 'original' };
    action.exec(transaction);

    expect(transaction.notes).toBe('1.234.567,89');
  });

  it('should format numbers without decimals using FORMATNUMBER', () => {
    const action = new Action('set', 'notes', null, {
      formula: '=FORMATNUMBER(1234567, 0)',
    });

    const transaction = { notes: 'original' };
    action.exec(transaction);

    expect(transaction.notes).toBe('1,234,567');
  });

  it('should let IFERROR catch invalid FORMATNUMBER values', () => {
    const action = new Action('set', 'notes', null, {
      formula: '=IFERROR(FORMATNUMBER("not a number"), "fallback")',
    });

    const transaction = { notes: 'original' };
    action.exec(transaction);

    expect(transaction.notes).toBe('fallback');
  });

  it('should format currency with default settings using FORMATCURRENCY', () => {
    const action = new Action('set', 'notes', null, {
      formula: '=FORMATCURRENCY(1234567.89)',
    });

    const transaction = { notes: 'original' };
    action.exec(transaction);

    expect(transaction.notes).toBe('$1,234,567.89');
  });

  it('should use cached currency preferences when FORMATCURRENCY arguments are omitted', () => {
    setCachedUserPreferences({
      currency: getCurrency('BRL'),
      numberFormat: 'dot-comma',
      decimalPlaces: 2,
      thousandsSeparator: '.',
      decimalSeparator: ',',
      locale: 'pt-BR',
      currencySymbolPosition: 'before',
      currencySpaceBetweenAmountAndSymbol: false,
    });

    const action = new Action('set', 'notes', null, {
      formula: '=FORMATCURRENCY(SUM(1, 2, 3))',
    });

    const transaction = { notes: 'original' };
    action.exec(transaction);

    expect(transaction.notes).toBe('R$6,00');
  });

  it('should use cached currency symbol position and spacing preferences', () => {
    setCachedUserPreferences({
      currency: getCurrency('BRL'),
      numberFormat: 'dot-comma',
      decimalPlaces: 2,
      thousandsSeparator: '.',
      decimalSeparator: ',',
      locale: 'pt-BR',
      currencySymbolPosition: 'after',
      currencySpaceBetweenAmountAndSymbol: true,
    });

    const action = new Action('set', 'notes', null, {
      formula: '=FORMATCURRENCY(SUM(1, 2, 3))',
    });

    const transaction = { notes: 'original' };
    action.exec(transaction);

    expect(transaction.notes).toBe('6,00\u202FR$');
  });

  it('should format currency with custom symbol using FORMATCURRENCY', () => {
    const action = new Action('set', 'notes', null, {
      formula: '=FORMATCURRENCY(1234567.89, "€")',
    });

    const transaction = { notes: 'original' };
    action.exec(transaction);

    expect(transaction.notes).toBe('€1,234,567.89');
  });

  it('should use cached currency position and spacing preferences with custom symbols', () => {
    setCachedUserPreferences({
      currency: getCurrency('BRL'),
      numberFormat: 'dot-comma',
      decimalPlaces: 2,
      thousandsSeparator: '.',
      decimalSeparator: ',',
      locale: 'pt-BR',
      currencySymbolPosition: 'after',
      currencySpaceBetweenAmountAndSymbol: true,
    });

    const action = new Action('set', 'notes', null, {
      formula: '=FORMATCURRENCY(1234567.89, "€")',
    });

    const transaction = { notes: 'original' };
    action.exec(transaction);

    expect(transaction.notes).toBe('1.234.567,89\u202F€');
  });

  it('should override cached currency position and spacing preferences with custom arguments', () => {
    setCachedUserPreferences({
      currency: getCurrency('BRL'),
      numberFormat: 'dot-comma',
      decimalPlaces: 2,
      thousandsSeparator: '.',
      decimalSeparator: ',',
      locale: 'pt-BR',
      currencySymbolPosition: 'after',
      currencySpaceBetweenAmountAndSymbol: true,
    });

    const action = new Action('set', 'notes', null, {
      formula:
        '=FORMATCURRENCY(1234567.89, "€", 2, ".", ",", "before", FALSE())',
    });

    const transaction = { notes: 'original' };
    action.exec(transaction);

    expect(transaction.notes).toBe('€1.234.567,89');
  });

  it('should format negative currency correctly using FORMATCURRENCY', () => {
    const action = new Action('set', 'notes', null, {
      formula: '=FORMATCURRENCY(-1234567.89)',
    });

    const transaction = { notes: 'original' };
    action.exec(transaction);

    expect(transaction.notes).toBe('-$1,234,567.89');
  });

  it('should format currency with custom separators using FORMATCURRENCY', () => {
    const action = new Action('set', 'notes', null, {
      formula: '=FORMATCURRENCY(1234567.89, "€", 2, ".", ",")',
    });

    const transaction = { notes: 'original' };
    action.exec(transaction);

    expect(transaction.notes).toBe('€1.234.567,89');
  });

  it('should let IFERROR catch invalid FORMATCURRENCY values', () => {
    const action = new Action('set', 'notes', null, {
      formula: '=IFERROR(FORMATCURRENCY("not a number"), "fallback")',
    });

    const transaction = { notes: 'original' };
    action.exec(transaction);

    expect(transaction.notes).toBe('fallback');
  });
});