Skip to content

A local-first personal finance app

21.9 KB
Raw Blame
import { useEffect, useRef, useState } from 'react';

import { send } from '@actual-app/core/platform/client/connection';
import {
  createBudgetQueryPrefetchKey,
  setCachedUserPreferences,
} from '@actual-app/core/shared/formulas/customFunctions';
import type { FormulaQueryContext } from '@actual-app/core/shared/formulas/customFunctions';
import * as monthUtils from '@actual-app/core/shared/months';
import { q } from '@actual-app/core/shared/query';
import type { Query } from '@actual-app/core/shared/query';
import { integerToAmount } from '@actual-app/core/shared/util';
import type {
  CategoryEntity,
  RuleConditionEntity,
  TimeFrame,
} from '@actual-app/core/types/models';
import { HyperFormula } from 'hyperformula';

import {
  normalizeQueryTimeFrameEnd,
  normalizeQueryTimeFrameStart,
} from '#components/formula/queryTimeFrame';
import {
  asMonthSlidingTimeFrame,
  calculateTimeRange,
} from '#components/reports/reportRanges';
import { bootstrapHyperFormula } from '#util/bootstrapHyperFormula';

import { useGlobalPref } from './useGlobalPref';
import { useLocale } from './useLocale';

bootstrapHyperFormula();

type QueryConfig = {
  conditions?: RuleConditionEntity[];
  conditionsOp?: 'and' | 'or';
  timeFrame?: Partial<TimeFrame>;
};

type QueriesMap = Record<string, QueryConfig>;

type FormulaCellValue = number | string | boolean | null;

export type SimpleAccount = { id: string; name: string };

function createFormulaQueryContext(): Required<FormulaQueryContext> {
  return {
    queryNames: new Set(),
    queryCountNames: new Set(),
    queryExtractCategoryNames: new Set(),
    queryExtractTimeframeStartNames: new Set(),
    queryExtractTimeframeEndNames: new Set(),
    balanceOfNames: new Set(),
    budgetQueryRequests: new Map(),
    querySumPrefetch: new Map(),
    queryCountPrefetch: new Map(),
    queryExtractCategoriesPrefetch: new Map(),
    queryExtractTimeframeStartPrefetch: new Map(),
    queryExtractTimeframeEndPrefetch: new Map(),
    balanceOfPrefetch: new Map(),
    budgetQueryPrefetch: new Map(),
    budgetQueryErrors: new Map(),
  };
}

function isHyperFormulaError(
  cellValue: unknown,
): cellValue is { type: string; message?: string } {
  return Boolean(
    cellValue && typeof cellValue === 'object' && 'type' in cellValue,
  );
}

function evaluateFormulaWithContext({
  formula,
  formulaQueryContext,
  locale,
  namedExpressions,
  throwOnCellError = true,
}: {
  formula: string;
  formulaQueryContext: FormulaQueryContext;
  locale: unknown;
  namedExpressions?: Record<string, number | string>;
  throwOnCellError?: boolean;
}): FormulaCellValue {
  let hfInstance: ReturnType<typeof HyperFormula.buildEmpty> | null = null;

  try {
    hfInstance = HyperFormula.buildEmpty({
      licenseKey: 'gpl-v3',
      language: 'enUS',
      localeLang: typeof locale === 'string' ? locale : 'en-US',
      dateFormats: ['DD/MM/YYYY', 'YYYY-MM-DD', 'YYYY/MM/DD'],
      context: {
        formulaQuery: formulaQueryContext,
      },
    });

    const sheetName = hfInstance.addSheet('Sheet1');
    const sheetId = hfInstance.getSheetId(sheetName);

    if (sheetId === undefined) {
      throw new Error('Failed to create sheet');
    }

    if (namedExpressions) {
      for (const [name, value] of Object.entries(namedExpressions)) {
        hfInstance.addNamedExpression(
          name,
          typeof value === 'number' ? value : String(value),
        );
      }
    }

    hfInstance.setCellContents({ sheet: sheetId, col: 0, row: 0 }, [[formula]]);

    const cellValue = hfInstance.getCellValue({
      sheet: sheetId,
      col: 0,
      row: 0,
    });

    if (isHyperFormulaError(cellValue)) {
      if (throwOnCellError) {
        throw new Error(`Formula error: ${cellValue.type}`);
      }
      return null;
    }

    return cellValue as FormulaCellValue;
  } finally {
    hfInstance?.destroy();
  }
}

export function useFormulaExecution(
  formula: string,
  queries: QueriesMap,
  queriesVersion?: number,
  namedExpressions?: Record<string, number | string>,
  accounts?: SimpleAccount[],
) {
  const locale = useLocale();
  const [language] = useGlobalPref('language');
  const [result, setResult] = useState<number | string | null>(null);
  const [isLoading, setIsLoading] = useState(false);
  const [error, setError] = useState<string | null>(null);

  // Callers pass `queries`/`namedExpressions`/`accounts` as plain objects, and
  // several of them build a fresh object on every render. Keying the effect on
  // the object identity would re-execute the formula on every render (and each
  // execution toggles `isLoading`, which renders again — an endless loop).
  // Depend on the serialized contents instead, and read the live objects
  // through refs.
  const queriesKey = JSON.stringify(queries ?? {});
  const namedExpressionsKey = JSON.stringify(namedExpressions ?? null);
  const accountsKey = JSON.stringify(accounts ?? null);

  const queriesRef = useRef(queries);
  queriesRef.current = queries;
  const namedExpressionsRef = useRef(namedExpressions);
  namedExpressionsRef.current = namedExpressions;
  const accountsRef = useRef(accounts);
  accountsRef.current = accounts;

  useEffect(() => {
    let cancelled = false;

    async function executeFormula() {
      if (!formula || !formula.startsWith('=')) {
        setResult(null);
        setError('Formula must start with =');
        setIsLoading(false);
        return;
      }

      const currentQueries = queriesRef.current;
      const currentNamedExpressions = namedExpressionsRef.current;
      const currentAccounts = accountsRef.current;

      setIsLoading(true);
      setError(null);

      try {
        const browserLocale =
          typeof navigator === 'undefined' ? undefined : navigator.language;
        const formulaLocale = language || browserLocale || locale || 'en-US';

        try {
          setCachedUserPreferences(
            await send('formula-load-user-preferences', {
              selectedLocale: language,
              browserLocale,
            }),
          );
        } catch (err) {
          console.error('Error loading formula preferences:', err);
        }

        const formulaQueryContext = createFormulaQueryContext();

        evaluateFormulaWithContext({
          formula,
          formulaQueryContext,
          locale: formulaLocale,
          namedExpressions: currentNamedExpressions,
          throwOnCellError: false,
        });

        await prefetchFormulaQueries(formulaQueryContext, currentQueries);
        await prefetchAccountBalances(
          formulaQueryContext,
          currentAccounts ?? [],
        );

        formulaQueryContext.budgetQueryRequests.clear();
        evaluateFormulaWithContext({
          formula,
          formulaQueryContext,
          locale: formulaLocale,
          namedExpressions: currentNamedExpressions,
          throwOnCellError: false,
        });

        await prefetchBudgetQueries(formulaQueryContext);

        const cellValue = evaluateFormulaWithContext({
          formula,
          formulaQueryContext,
          locale: formulaLocale,
          namedExpressions: currentNamedExpressions,
        });

        if (cancelled) return;

        setResult(cellValue as number | string);
        setError(null);
      } catch (err) {
        if (cancelled) return;
        console.error('Formula execution error:', err);
        setError(err instanceof Error ? err.message : 'Unknown error');
        setResult(null);
      } finally {
        if (!cancelled) {
          setIsLoading(false);
        }
      }
    }

    void executeFormula();

    return () => {
      cancelled = true;
    };
  }, [
    formula,
    queriesVersion,
    locale,
    language,
    queriesKey,
    namedExpressionsKey,
    accountsKey,
  ]);

  return { result, isLoading, error };
}

async function prefetchFormulaQueries(
  formulaQueryContext: Required<FormulaQueryContext>,
  queries: QueriesMap,
) {
  for (const queryName of formulaQueryContext.queryNames) {
    const queryConfig = queries[queryName];

    if (!queryConfig) {
      console.warn(`Query "${queryName}" not found in queries config`);
      formulaQueryContext.querySumPrefetch.set(queryName, 0);
      continue;
    }

    const data = await fetchQuerySum(queryConfig);
    formulaQueryContext.querySumPrefetch.set(
      queryName,
      integerToAmount(data, 2),
    );
  }

  for (const queryName of formulaQueryContext.queryCountNames) {
    const queryConfig = queries[queryName];

    if (!queryConfig) {
      console.warn(`Query "${queryName}" not found in queries config`);
      formulaQueryContext.queryCountPrefetch.set(queryName, 0);
      continue;
    }

    formulaQueryContext.queryCountPrefetch.set(
      queryName,
      await fetchQueryCount(queryConfig),
    );
  }

  for (const queryName of formulaQueryContext.queryExtractCategoryNames) {
    formulaQueryContext.queryExtractCategoriesPrefetch.set(
      queryName,
      await extractQueryCategories(queryName, queries),
    );
  }

  for (const queryName of formulaQueryContext.queryExtractTimeframeStartNames) {
    formulaQueryContext.queryExtractTimeframeStartPrefetch.set(
      queryName,
      await extractQueryTimeframeStart(queryName, queries),
    );
  }

  for (const queryName of formulaQueryContext.queryExtractTimeframeEndNames) {
    formulaQueryContext.queryExtractTimeframeEndPrefetch.set(
      queryName,
      await extractQueryTimeframeEnd(queryName, queries),
    );
  }
}

async function prefetchAccountBalances(
  formulaQueryContext: Required<FormulaQueryContext>,
  accounts: SimpleAccount[],
) {
  for (const literal of formulaQueryContext.balanceOfNames) {
    const account =
      accounts.find(candidate => candidate.id === literal) ??
      accounts.find(candidate => candidate.name === literal);

    if (!account) {
      formulaQueryContext.balanceOfPrefetch.set(literal, 0);
      continue;
    }

    formulaQueryContext.balanceOfPrefetch.set(
      literal,
      await fetchAccountBalance(account.id),
    );
  }
}

async function fetchAccountBalance(accountId: string): Promise<number> {
  try {
    const balanceQuery = q('transactions')
      .filter({ account: accountId })
      .calculate({ $sum: '$amount' });
    const { data } = await send('query', balanceQuery.serialize());
    return integerToAmount(data || 0, 2);
  } catch (err) {
    console.error('Error fetching account balance:', err);
    return 0;
  }
}

async function prefetchBudgetQueries(
  formulaQueryContext: Required<FormulaQueryContext>,
) {
  for (const request of formulaQueryContext.budgetQueryRequests.values()) {
    const key = createBudgetQueryPrefetchKey(request);

    try {
      formulaQueryContext.budgetQueryPrefetch.set(
        key,
        await fetchBudgetDimensionValueDirect(
          request.dimension,
          request.categoryIds,
          request.startMonth,
          request.endMonth,
        ),
      );
      formulaQueryContext.budgetQueryErrors.delete(key);
    } catch (err) {
      console.error('Error evaluating BUDGET_QUERY', err);
      formulaQueryContext.budgetQueryPrefetch.delete(key);
      formulaQueryContext.budgetQueryErrors.set(
        key,
        err instanceof Error ? err.message : String(err),
      );
    }
  }
}

export async function buildFilteredTransactionsQuery(
  config: QueryConfig,
): Promise<Query> {
  const conditions = config.conditions || [];
  const conditionsOp = config.conditionsOp || 'and';
  const timeFrame = config.timeFrame;

  // Convert conditions to query filters
  const { filters: queryFilters } = await send('make-filters-from-conditions', {
    conditions,
  });

  const conditionsOpKey = conditionsOp === 'or' ? '$or' : '$and';

  // Start building the query
  let transQuery = q('transactions');

  // Add date range filter if provided
  if (timeFrame && timeFrame.mode) {
    const [calculatedStart, calculatedEnd] = calculateTimeRange(
      asMonthSlidingTimeFrame(timeFrame),
    );
    const startDate = normalizeQueryTimeFrameStart(calculatedStart);
    const endDate = normalizeQueryTimeFrameEnd(calculatedEnd);

    transQuery = transQuery.filter({
      $and: [{ date: { $gte: startDate } }, { date: { $lte: endDate } }],
    });
  }

  // Add user-defined filters
  if (queryFilters.length > 0) {
    transQuery = transQuery.filter({ [conditionsOpKey]: queryFilters });
  }

  return transQuery;
}

async function fetchQuerySum(config: QueryConfig): Promise<number> {
  try {
    const transQuery = await buildFilteredTransactionsQuery(config);
    const summedQuery = transQuery.calculate({ $sum: '$amount' });
    const { data } = await send('query', summedQuery.serialize());
    return data || 0;
  } catch (err) {
    console.error('Error fetching query sum:', err);
    return 0;
  }
}

async function fetchQueryCount(config: QueryConfig): Promise<number> {
  try {
    const transQuery = await buildFilteredTransactionsQuery(config);
    const countQuery = transQuery.calculate({ $count: '*' });
    const { data } = await send('query', countQuery.serialize());
    return data || 0;
  } catch (err) {
    console.error('Error fetching query count:', err);
    return 0;
  }
}

// Helper: Extract category-based conditions (ignore transaction-specific filters)
function extractCategoryConditions(
  conditions: RuleConditionEntity[],
): RuleConditionEntity[] {
  return conditions.filter(
    cond =>
      !cond.customName &&
      (cond.field === 'category' || cond.field === 'category_group'),
  );
}

// Helper: Evaluate category conditions to get matching categories
async function getCategoriesFromConditions(
  allCategories: CategoryEntity[],
  conditions: RuleConditionEntity[],
  conditionsOp: 'and' | 'or',
): Promise<string[]> {
  if (conditions.length === 0) {
    // No category filter: include all non-income, non-hidden categories
    return allCategories
      .filter((cat: CategoryEntity) => !cat.is_income && !cat.hidden)
      .map((cat: CategoryEntity) => cat.id);
  }

  // Get category groups for resolving group IDs to names
  const { grouped: categoryGroups } = await send('get-categories');
  const groupNameById = new Map(
    categoryGroups.map((g: { id: string; name: string }) => [g.id, g.name]),
  );

  // Evaluate each condition to get sets of matching categories
  const conditionResults = conditions.map(cond => {
    // For category_group conditions, we check cat.group; for category, we check cat.id
    const getKey = (cat: CategoryEntity) =>
      cond.field === 'category_group' ? cat.group : cat.id;

    const matching = allCategories.filter((cat: CategoryEntity) => {
      const key = getKey(cat);

      const textValue =
        cond.field === 'category_group'
          ? (groupNameById.get(key) ?? key)
          : cat.name;

      if (cond.op === 'is') {
        return cond.value === key;
      } else if (cond.op === 'isNot') {
        return cond.value !== key;
      } else if (cond.op === 'oneOf') {
        return cond.value.includes(key);
      } else if (cond.op === 'notOneOf') {
        return !cond.value.includes(key);
      } else if (cond.op === 'contains') {
        return textValue
          .toLowerCase()
          .includes((cond.value as string).toLowerCase());
      } else if (cond.op === 'doesNotContain') {
        return !textValue
          .toLowerCase()
          .includes((cond.value as string).toLowerCase());
      } else if (cond.op === 'matches') {
        try {
          return new RegExp(cond.value as string, 'i').test(textValue);
        } catch (e) {
          console.warn('Invalid regexp in matches condition', e);
          return false;
        }
      }
      // Unknown operator: exclude category by default and log warning
      console.warn(`Unknown category condition operator: ${cond.op}`);
      return false;
    });
    return matching.map((cat: CategoryEntity) => cat.id);
  });

  if (conditionsOp === 'or') {
    // OR: Union of all matching categories
    const categoryIds = new Set(conditionResults.flat());
    return Array.from(categoryIds);
  } else {
    // AND: Intersection of all matching categories
    if (conditionResults.length === 0) {
      return [];
    }
    const firstSet = new Set(conditionResults[0]);
    for (let i = 1; i < conditionResults.length; i++) {
      const currentIds = new Set(conditionResults[i]);
      // Keep only categories that are in both sets
      const toRemove: string[] = [];
      firstSet.forEach(id => {
        if (!currentIds.has(id)) {
          toRemove.push(id);
        }
      });
      toRemove.forEach(id => firstSet.delete(id));
    }
    return Array.from(firstSet);
  }
}

// Helper: Get month data from envelope-budget-month RPC
async function getMonthBudgetData(
  month: string,
): Promise<Array<{ name: string; value: string | number | boolean }>> {
  const monthData = await send('envelope-budget-month', { month });
  return monthData || [];
}

// Helper: Extract value from month data by field pattern
function getMonthDataValue(
  monthData: Array<{ name: string; value: string | number | boolean }>,
  pattern: string,
  catId: string,
): string | number | boolean {
  const fieldName = pattern.replace('{catId}', catId);
  const cell = monthData.find(c => c.name.endsWith(fieldName));
  return cell?.value ?? 0;
}

// Helper: Extract categories from a named query (for QUERY_EXTRACT_CATEGORIES)
async function extractQueryCategories(
  queryName: string,
  queries: QueriesMap,
): Promise<string[]> {
  const queryConfig = queries[queryName];
  if (!queryConfig) {
    console.warn(`Query "${queryName}" not found in queries config`);
    return [];
  }

  const categoryConditions = extractCategoryConditions(
    queryConfig.conditions || [],
  );
  const { list: allCategories } = await send('get-categories');
  return getCategoriesFromConditions(
    allCategories,
    categoryConditions,
    queryConfig.conditionsOp || 'and',
  );
}

// Helper: Extract timeframe start month from a named query (for QUERY_EXTRACT_TIMEFRAME_START)
async function extractQueryTimeframeStart(
  queryName: string,
  queries: QueriesMap,
): Promise<string> {
  const queryConfig = queries[queryName];
  if (!queryConfig || !queryConfig.timeFrame) {
    console.warn(
      `Query "${queryName}" not found or has no timeframe; cannot extract start`,
    );
    return monthUtils.currentMonth();
  }

  const [startMonth] = calculateTimeRange(
    asMonthSlidingTimeFrame(queryConfig.timeFrame),
  );
  return startMonth;
}

// Helper: Extract timeframe end month from a named query (for QUERY_EXTRACT_TIMEFRAME_END)
async function extractQueryTimeframeEnd(
  queryName: string,
  queries: QueriesMap,
): Promise<string> {
  const queryConfig = queries[queryName];
  if (!queryConfig || !queryConfig.timeFrame) {
    console.warn(
      `Query "${queryName}" not found or has no timeframe; cannot extract end`,
    );
    return monthUtils.currentMonth();
  }

  const [, endMonth] = calculateTimeRange(
    asMonthSlidingTimeFrame(queryConfig.timeFrame),
  );
  return endMonth;
}

// Helper: Evaluate budget dimension with already-extracted parameters (used by compositional BUDGET_QUERY)
async function fetchBudgetDimensionValueDirect(
  dimension: string,
  categoryIds: string[],
  startMonth: string,
  endMonth: string,
): Promise<number> {
  const allowed = new Set([
    'budgeted',
    'spent',
    'balance_start',
    'balance_end',
    'goal',
  ]);
  const dim = dimension.toLowerCase();
  if (!allowed.has(dim)) {
    throw new Error(`Invalid BUDGET_QUERY dimension: ${dimension}`);
  }

  const intervals = monthUtils.rangeInclusive(startMonth, endMonth);

  // Helper: sum a dimension across all months/categories
  const sumDimension = async (fieldPattern: string): Promise<number> => {
    let total = 0;
    for (const month of intervals) {
      const monthData = await getMonthBudgetData(month);
      for (const catId of categoryIds) {
        total += getMonthDataValue(monthData, fieldPattern, catId) as number;
      }
    }
    return total;
  };

  if (dim === 'budgeted') {
    return integerToAmount(await sumDimension('budget-{catId}'), 2);
  }

  if (dim === 'spent') {
    return integerToAmount(await sumDimension('sum-amount-{catId}'), 2);
  }

  if (dim === 'goal') {
    return integerToAmount(await sumDimension('goal-{catId}'), 2);
  }

  // Handle balance dimensions: chain month-by-month with carryover logic
  if (dim === 'balance_start' || dim === 'balance_end') {
    let runningBalance = 0;
    const monthBeforeStart = monthUtils.subMonths(startMonth, 1);
    const prevMonthData = await getMonthBudgetData(monthBeforeStart);

    for (const catId of categoryIds) {
      const catBalance = getMonthDataValue(
        prevMonthData,
        'leftover-{catId}',
        catId,
      ) as number;
      const hasCarryover = Boolean(
        getMonthDataValue(prevMonthData, 'carryover-{catId}', catId),
      );
      if (catBalance > 0 || (catBalance < 0 && hasCarryover)) {
        runningBalance += catBalance;
      }
    }

    const balances: Record<string, { start: number; end: number }> = {};

    for (const month of intervals) {
      const monthData = await getMonthBudgetData(month);
      let budgeted = 0;
      let spent = 0;
      let carryoverToNextMonth = 0;

      for (const catId of categoryIds) {
        const catBudgeted =
          Number(getMonthDataValue(monthData, 'budget-{catId}', catId)) || 0;
        const catSpent =
          Number(getMonthDataValue(monthData, 'sum-amount-{catId}', catId)) ||
          0;
        const catBalance =
          Number(getMonthDataValue(monthData, 'leftover-{catId}', catId)) || 0;
        const hasCarryover = Boolean(
          getMonthDataValue(monthData, 'carryover-{catId}', catId),
        );

        budgeted += catBudgeted;
        spent += catSpent;

        if (catBalance > 0 || (catBalance < 0 && hasCarryover)) {
          carryoverToNextMonth += catBalance;
        }
      }

      const balanceStart = runningBalance;
      const balanceEnd = budgeted + spent + runningBalance;

      balances[month] = { start: balanceStart, end: balanceEnd };
      runningBalance = carryoverToNextMonth;
    }

    if (dim === 'balance_start') {
      return integerToAmount(balances[intervals[0]]?.start || 0, 2);
    }
    return integerToAmount(
      balances[intervals[intervals.length - 1]]?.end || 0,
      2,
    );
  }

  return 0;
}