Skip to content

A local-first personal finance app

4.7 KB
Raw Blame
import { createApp } from '#server/app';
import { aqlQuery } from '#server/aql';
import * as db from '#server/db';
import { runMutator } from '#server/mutators';
import { batchUpdateTransactions } from '#server/transactions';
// @ts-strict-ignore
import { q } from '#shared/query';
import type { TransactionEntity } from '#types/models';

export type ToolsHandlers = {
  'tools/fix-split-transactions': typeof fixSplitTransactions;
};

export const app = createApp<ToolsHandlers>();

app.method('tools/fix-split-transactions', fixSplitTransactions);

async function fixSplitTransactions(): Promise<{
  numBlankPayees: number;
  numCleared: number;
  numDeleted: number;
  numTransfersFixed: number;
  numNonParentErrorsFixed: number;
  numParentTransactionsWithCategoryFixed: number;
  mismatchedSplits: TransactionEntity[];
}> {
  // 1. Check for child transactions that have a blank payee, and set
  //    the payee to whatever the parent has
  const blankPayeeRows = await db.all<
    db.DbViewTransactionInternal & {
      parentPayee: db.DbViewTransactionInternal['payee'];
    }
  >(`
    SELECT t.*, p.payee AS parentPayee FROM v_transactions_internal t
    LEFT JOIN v_transactions_internal p ON t.parent_id = p.id
    WHERE t.is_child = 1 AND t.payee IS NULL AND p.payee IS NOT NULL
  `);

  await runMutator(async () => {
    const updated = blankPayeeRows.map(row => ({
      id: row.id,
      payee: row.parentPayee,
    }));
    await batchUpdateTransactions({ updated });
  });

  // 2. Make sure the "cleared" flag is synced up with the parent
  // transactions
  const clearedRows = await db.all<
    Pick<db.DbViewTransactionInternal, 'id'> &
      Pick<db.DbViewTransactionInternal, 'cleared'>
  >(`
    SELECT t.id, p.cleared FROM v_transactions_internal t
    LEFT JOIN v_transactions_internal p ON t.parent_id = p.id
    WHERE t.is_child = 1 AND t.cleared != p.cleared
  `);

  await runMutator(async () => {
    const updated = clearedRows.map(row => ({
      id: row.id,
      cleared: row.cleared === 1,
    }));
    await batchUpdateTransactions({ updated });
  });

  // 3. Mark the `tombstone` field as true on any child transactions
  //    that have a dead parent
  const deletedRows = await db.all<db.DbViewTransactionInternal>(`
    SELECT t.* FROM v_transactions_internal t
    LEFT JOIN v_transactions_internal p ON t.parent_id = p.id
    WHERE t.is_child = 1 AND t.tombstone = 0 AND (p.tombstone = 1 OR p.id IS NULL)
  `);

  await runMutator(async () => {
    const updated = deletedRows.map(row => ({ id: row.id, tombstone: true }));
    await batchUpdateTransactions({ updated });
  });

  const splitTransactions = (
    await aqlQuery(
      q('transactions')
        .options({ splits: 'grouped' })
        .filter({
          is_parent: true,
        })
        .select('*'),
    )
  ).data;

  const mismatchedSplits = splitTransactions.filter(t => {
    const subValue = t.subtransactions.reduce((acc, st) => acc + st.amount, 0);

    return subValue !== t.amount;
  });

  // 5. Fix transfers that should not have categories
  const brokenTransfers = await db.all<
    Pick<db.DbViewTransactionInternal, 'id'>
  >(`
    SELECT t1.id
    FROM v_transactions_internal t1
           JOIN accounts a1 ON t1.account = a1.id
           JOIN v_transactions_internal t2 ON t1.transfer_id = t2.id
           JOIN accounts a2 ON t2.account = a2.id
    WHERE a1.offbudget = a2.offbudget
      AND t1.category IS NOT NULL
  `);

  await runMutator(async () => {
    const updated = brokenTransfers.map(row => ({
      id: row.id,
      category: null,
    }));
    await batchUpdateTransactions({ updated });
  });

  // 6. Remove transaction errors from non-parent transactions
  const errorRows = await db.all<Pick<db.DbViewTransactionInternal, 'id'>>(`
    SELECT id FROM v_transactions_internal WHERE error IS NOT NULL AND is_parent = 0
  `);

  await runMutator(async () => {
    const updated = errorRows.map(({ id }) => ({ id, error: null }));
    await batchUpdateTransactions({ updated });
  });

  // 7. Clear categories of parent transactions
  const parentTransactionsWithCategory = await db.all<
    Pick<db.DbViewTransactionInternal, 'id'>
  >(`
    SELECT id FROM transactions WHERE isParent = 1 AND category IS NOT NULL
  `);

  await runMutator(async () => {
    const updated = parentTransactionsWithCategory.map(({ id }) => ({
      id,
      category: null,
    }));
    await batchUpdateTransactions({ updated });
  });

  return {
    numBlankPayees: blankPayeeRows.length,
    numCleared: clearedRows.length,
    numDeleted: deletedRows.length,
    numTransfersFixed: brokenTransfers.length,
    numNonParentErrorsFixed: errorRows.length,
    numParentTransactionsWithCategoryFixed:
      parentTransactionsWithCategory.length,
    mismatchedSplits,
  };
}