Skip to content

Open-source personal finance manager. Self-hosted, privacy-first.

25.8 KB
Raw Blame
"""Integration tests for recurring-bill matching through the CSV/OFX import
path (issue #116)."""
import uuid
from datetime import date, datetime, timezone
from decimal import Decimal
from unittest.mock import AsyncMock, patch

import pytest
from sqlalchemy import select

from app.models.payee import Payee
from app.models.recurring_transaction import RecurringTransaction
from app.models.transaction import Transaction
from app.schemas.recurring_transaction import RecurringTransactionCreate
from app.schemas.rule import RuleAction, RuleCondition, RuleCreate
from app.schemas.transaction import TransactionImport
from app.services import reconciliation_policy, reconciliation_rule_service
from app.services.import_service import import_transactions
from app.services.recurring_transaction_service import create_recurring_transaction
from app.services.rule_service import create_rule


async def _make_bill(session, test_workspace, test_user, account, **ov):
    data = RecurringTransactionCreate(
        description=ov.pop("description", "Netflix Subscription"),
        amount=ov.pop("amount", Decimal("39.90")),
        currency=ov.pop("currency", "BRL"),
        type=ov.pop("type", "debit"),
        frequency=ov.pop("frequency", "monthly"),
        start_date=ov.pop("start_date", date(2026, 1, 10)),
        account_id=account.id, **ov,
    )
    return await create_recurring_transaction(session, test_workspace.id, test_user.id, data)


async def _run_import(session, test_workspace, test_user, account_id, txns, source="csv",
                      stamp=None):
    with patch("app.services.import_service.stamp_primary_amount",
               new=stamp if stamp is not None else AsyncMock()), \
         patch("app.services.import_service.apply_rules_to_transaction", new_callable=AsyncMock):
        return await import_transactions(
            session, test_workspace.id, test_user.id, account_id, txns, source,
        )


async def _usd_placeholder(session, test_workspace, test_user, account):
    """A USD bill and its generated placeholder on the BRL account, stamped
    with the estimate made when it was generated (4.8833 BRL per USD)."""
    bill = await _make_bill(session, test_workspace, test_user, account,
                            amount=Decimal("18.43"), currency="USD",
                            start_date=date(2026, 1, 10))
    bill.next_occurrence = date(2026, 2, 10)
    placeholder = Transaction(
        id=uuid.uuid4(), user_id=test_user.id, account_id=account.id,
        workspace_id=test_workspace.id, description="Netflix Subscription",
        amount=Decimal("18.43"), currency="USD", date=date(2026, 1, 10),
        amount_primary=Decimal("90.00"), fx_rate_used=Decimal("4.8833"),
        type="debit", source="recurring", status="posted",
        recurring_transaction_id=bill.id, created_at=datetime.now(timezone.utc),
    )
    session.add(placeholder)
    await session.commit()
    return placeholder.id


async def _real_txs(session, account_id):
    r = await session.execute(
        select(Transaction).where(
            Transaction.account_id == account_id,
            Transaction.source != "opening_balance",
        )
    )
    return list(r.scalars().all())


@pytest.mark.asyncio
async def test_import_links_and_advances_bill(session, test_user, test_workspace, test_account):
    account_id = test_account.id
    bill = await _make_bill(session, test_workspace, test_user, test_account,
                            amount=Decimal("39.90"), start_date=date(2026, 1, 10))
    bill_id = bill.id
    txns = [TransactionImport(description="NETFLIX SUBSCRIPTION", amount=Decimal("39.90"),
                              date=date(2026, 1, 12), type="debit", currency="BRL")]

    imported, skipped, _, _ = await _run_import(session, test_workspace, test_user, account_id, txns)

    assert imported == 1 and skipped == 0
    txs = await _real_txs(session, account_id)
    assert len(txs) == 1
    assert txs[0].recurring_transaction_id == bill_id
    refreshed = await session.get(RecurringTransaction, bill_id)
    assert refreshed.next_occurrence == date(2026, 2, 10)


@pytest.mark.asyncio
async def test_import_merges_into_placeholder(session, test_user, test_workspace, test_account):
    account_id = test_account.id
    bill = await _make_bill(session, test_workspace, test_user, test_account,
                            amount=Decimal("39.90"), start_date=date(2026, 1, 10))
    bill_id = bill.id
    bill.next_occurrence = date(2026, 2, 10)
    placeholder = Transaction(
        id=uuid.uuid4(), user_id=test_user.id, account_id=account_id,
        workspace_id=test_workspace.id, description="Netflix Subscription",
        amount=Decimal("39.90"), currency="BRL", date=date(2026, 1, 10),
        type="debit", source="recurring", status="posted",
        recurring_transaction_id=bill_id, created_at=datetime.now(timezone.utc),
    )
    session.add(placeholder)
    await session.commit()
    placeholder_id = placeholder.id

    # Different case bypasses the exact-match field dedup, so the recurring
    # merge (case-insensitive similarity) is what catches it.
    txns = [TransactionImport(description="NETFLIX SUBSCRIPTION", amount=Decimal("39.90"),
                              date=date(2026, 1, 11), type="debit", currency="BRL")]
    imported, skipped, _, _ = await _run_import(session, test_workspace, test_user, account_id, txns)

    assert imported == 1
    txs = await _real_txs(session, account_id)
    assert len(txs) == 1  # merged into placeholder, no duplicate
    assert txs[0].id == placeholder_id
    assert txs[0].source == "csv"
    assert txs[0].recurring_transaction_id == bill_id
    assert txs[0].original_description == "NETFLIX SUBSCRIPTION"


@pytest.mark.asyncio
async def test_import_preserves_original_description_without_normalization(
    session, test_user, test_workspace, test_account
):
    incoming = TransactionImport(
        description="BANK RAW DESCRIPTION",
        amount=Decimal("12.34"),
        date=date(2026, 1, 11),
        type="debit",
        currency="BRL",
    )

    imported, skipped, _, _ = await _run_import(
        session,
        test_workspace,
        test_user,
        test_account.id,
        [incoming],
    )

    assert (imported, skipped) == (1, 0)
    txs = await _real_txs(session, test_account.id)
    assert len(txs) == 1
    assert txs[0].description == incoming.description
    assert txs[0].original_description == incoming.description
    assert txs[0].description_is_rule_managed is False

@pytest.mark.asyncio
async def test_import_normalizes_before_recurring_match_and_deduplicates_raw_reimport(
    session, test_user, test_workspace, test_account
):
    account_id = test_account.id
    bill = await _make_bill(
        session,
        test_workspace,
        test_user,
        test_account,
        description="Amazon Prime",
        amount=Decimal("19.90"),
        start_date=date(2026, 1, 10),
    )
    await create_rule(
        session,
        test_workspace.id,
        test_user.id,
        RuleCreate(
            name="Normalize Amazon",
            conditions=[
                RuleCondition(
                    field="description", op="contains", value="AMZNPrime DE"
                )
            ],
            actions=[
                RuleAction(op="set_description", value="Amazon Prime"),
                RuleAction(op="append_notes", value="#subscription"),
            ],
            apply_to_existing=False,
        ),
    )
    noisy = TransactionImport(
        description="D01-123 AMZNPrime DE changing-token",
        amount=Decimal("19.90"),
        date=date(2026, 1, 11),
        type="debit",
        currency="BRL",
    )

    with patch(
        "app.services.import_service.stamp_primary_amount",
        new_callable=AsyncMock,
    ):
        imported, skipped, _, _ = await import_transactions(
            session,
            test_workspace.id,
            test_user.id,
            account_id,
            [noisy],
            "csv",
        )
        imported_again, skipped_again, _, _ = await import_transactions(
            session,
            test_workspace.id,
            test_user.id,
            account_id,
            [noisy],
            "csv",
        )

    assert (imported, skipped) == (1, 0)
    assert (imported_again, skipped_again) == (0, 1)
    txs = await _real_txs(session, account_id)
    assert len(txs) == 1
    assert txs[0].recurring_transaction_id == bill.id
    assert txs[0].description == "Amazon Prime"
    assert txs[0].original_description == noisy.description
    assert txs[0].description_is_rule_managed is True
    assert txs[0].notes == "#subscription"


@pytest.mark.asyncio
async def test_import_normalizes_before_placeholder_upgrade(
    session, test_user, test_workspace, test_account, test_categories
):
    account_id = test_account.id
    rule_payee = Payee(
        user_id=test_user.id,
        workspace_id=test_workspace.id,
        name="iFood Rule Target",
    )
    session.add(rule_payee)
    await session.flush()
    rule_payee_id = rule_payee.id

    bill = await _make_bill(
        session,
        test_workspace,
        test_user,
        test_account,
        description="iFood",
        amount=Decimal("49.90"),
        start_date=date(2026, 1, 10),
    )
    bill.next_occurrence = date(2026, 2, 10)
    placeholder = Transaction(
        user_id=test_user.id,
        workspace_id=test_workspace.id,
        account_id=account_id,
        description="iFood",
        amount=Decimal("49.90"),
        currency="BRL",
        date=date(2026, 1, 10),
        type="debit",
        source="recurring",
        status="pending",
        recurring_transaction_id=bill.id,
        category_id=test_categories[0].id,
    )
    session.add(placeholder)
    await session.commit()
    placeholder_id = placeholder.id

    await create_rule(
        session,
        test_workspace.id,
        test_user.id,
        RuleCreate(
            name="Normalize raw iFood transaction",
            conditions=[
                RuleCondition(
                    field="description", op="contains", value="f|ood Club"
                )
            ],
            actions=[
                RuleAction(op="set_description", value="iFood"),
                RuleAction(op="set_payee", value=str(rule_payee_id)),
                RuleAction(op="ignore", value=True),
            ],
            apply_to_existing=False,
        ),
    )
    incoming = TransactionImport(
        description="|fd*f|ood Club",
        payee_raw="IFOOD.COM AGÊNCIA DE RESTAURANTES ONLINE S.A.",
        amount=Decimal("49.90"),
        date=date(2026, 1, 11),
        type="debit",
        currency="BRL",
        category_id=test_categories[1].id,
        notes="#statement",
    )

    with patch(
        "app.services.import_service.stamp_primary_amount",
        new_callable=AsyncMock,
    ):
        await import_transactions(
            session,
            test_workspace.id,
            test_user.id,
            account_id,
            [incoming],
            "ofx",
        )

    txs = await _real_txs(session, account_id)
    assert len(txs) == 1
    assert txs[0].id == placeholder_id
    assert txs[0].recurring_transaction_id == bill.id
    # The placeholder keeps the recurring definition's own wording, so the
    # description was never rule-managed even though the rule matched and
    # applied its other actions.
    assert txs[0].description == "iFood"
    assert txs[0].original_description == "|fd*f|ood Club"
    assert txs[0].description_is_rule_managed is False
    assert txs[0].source == "ofx"
    assert txs[0].status == "posted"
    assert txs[0].import_id is not None
    # Keep the provider's raw counterparty as provenance, while preserving the
    # canonical Payee chosen by the rule.
    assert txs[0].payee == "IFOOD.COM AGÊNCIA DE RESTAURANTES ONLINE S.A."
    assert txs[0].payee_id == rule_payee_id
    assert txs[0].is_ignored is True
    assert txs[0].notes == "#statement"
    assert txs[0].category_id == test_categories[0].id


@pytest.mark.asyncio
async def test_import_placeholder_rule_payee_without_raw_payee(
    session, test_user, test_workspace, test_account
):
    account_id = test_account.id
    rule_payee = Payee(
        user_id=test_user.id,
        workspace_id=test_workspace.id,
        name="PR586 Rule Target",
    )
    session.add(rule_payee)
    await session.flush()
    rule_payee_id = rule_payee.id

    bill = await _make_bill(
        session,
        test_workspace,
        test_user,
        test_account,
        description="PR586 NoPayee",
        amount=Decimal("29.90"),
        start_date=date(2026, 1, 10),
    )
    bill.next_occurrence = date(2026, 2, 10)
    placeholder = Transaction(
        user_id=test_user.id,
        workspace_id=test_workspace.id,
        account_id=account_id,
        description="PR586 NoPayee",
        amount=Decimal("29.90"),
        currency="BRL",
        date=date(2026, 1, 10),
        type="debit",
        source="recurring",
        status="pending",
        recurring_transaction_id=bill.id,
    )
    session.add(placeholder)
    await session.commit()
    placeholder_id = placeholder.id

    await create_rule(
        session,
        test_workspace.id,
        test_user.id,
        RuleCreate(
            name="Set placeholder payee without raw import payee",
            conditions=[
                RuleCondition(
                    field="description",
                    op="contains",
                    value="PR586 NoPayee bank",
                )
            ],
            actions=[
                RuleAction(op="set_description", value="PR586 NoPayee"),
                RuleAction(op="set_payee", value=str(rule_payee_id)),
            ],
            apply_to_existing=False,
        ),
    )
    incoming = TransactionImport(
        description="PR586 NoPayee bank",
        amount=Decimal("29.90"),
        date=date(2026, 1, 11),
        type="debit",
        currency="BRL",
    )

    with patch(
        "app.services.import_service.stamp_primary_amount",
        new_callable=AsyncMock,
    ):
        await import_transactions(
            session,
            test_workspace.id,
            test_user.id,
            account_id,
            [incoming],
            "csv",
        )

    txs = await _real_txs(session, account_id)
    assert len(txs) == 1
    assert txs[0].id == placeholder_id
    assert txs[0].recurring_transaction_id == bill.id
    assert txs[0].payee is None
    assert txs[0].payee_id == rule_payee_id


@pytest.mark.asyncio
@pytest.mark.parametrize(
    ("incoming_category", "force_uncategorized", "expected_category"),
    [
        (True, False, "incoming"),
        (False, False, "rule"),
        (False, True, None),
    ],
)
async def test_import_placeholder_category_precedence(
    session,
    test_user,
    test_workspace,
    test_account,
    test_categories,
    incoming_category,
    force_uncategorized,
    expected_category,
):
    bill = await _make_bill(
        session,
        test_workspace,
        test_user,
        test_account,
        description="iFood",
        amount=Decimal("59.90"),
        start_date=date(2026, 2, 10),
    )
    bill.next_occurrence = date(2026, 3, 10)
    placeholder = Transaction(
        user_id=test_user.id,
        workspace_id=test_workspace.id,
        account_id=test_account.id,
        description="iFood",
        amount=Decimal("59.90"),
        currency="BRL",
        date=date(2026, 2, 10),
        type="debit",
        source="recurring",
        status="posted",
        recurring_transaction_id=bill.id,
    )
    session.add(placeholder)
    await session.commit()
    placeholder_id = placeholder.id
    await create_rule(
        session,
        test_workspace.id,
        test_user.id,
        RuleCreate(
            name=(
                "Import placeholder precedence "
                f"{incoming_category}-{force_uncategorized}"
            ),
            conditions=[
                RuleCondition(
                    field="description", op="contains", value="noisy iFood"
                )
            ],
            actions=[
                RuleAction(op="set_description", value="iFood"),
                RuleAction(
                    op="set_category", value=str(test_categories[0].id)
                ),
                RuleAction(op="append_notes", value="#delivery"),
            ],
            apply_to_existing=False,
        ),
    )
    incoming = TransactionImport(
        description="changing noisy iFood token",
        amount=Decimal("59.90"),
        date=date(2026, 2, 11),
        type="debit",
        currency="BRL",
        category_id=(
            test_categories[1].id if incoming_category else None
        ),
        force_uncategorized=force_uncategorized,
    )

    with patch(
        "app.services.import_service.stamp_primary_amount",
        new_callable=AsyncMock,
    ):
        await import_transactions(
            session,
            test_workspace.id,
            test_user.id,
            test_account.id,
            [incoming],
            "csv",
        )

    txs = await _real_txs(session, test_account.id)
    assert len(txs) == 1
    assert txs[0].id == placeholder_id
    assert txs[0].recurring_transaction_id == bill.id
    if expected_category == "incoming":
        assert txs[0].category_id == test_categories[1].id
    elif expected_category == "rule":
        assert txs[0].category_id == test_categories[0].id
    else:
        assert txs[0].category_id is None
    assert txs[0].description == "iFood"
    assert txs[0].original_description == incoming.description
    assert txs[0].notes == "#delivery"


@pytest.mark.asyncio
async def test_import_amount_mismatch_not_linked(session, test_user, test_workspace, test_account):
    account_id = test_account.id
    bill = await _make_bill(session, test_workspace, test_user, test_account,
                            amount=Decimal("39.90"), start_date=date(2026, 1, 10))
    bill_id = bill.id
    txns = [TransactionImport(description="NETFLIX SUBSCRIPTION", amount=Decimal("99.00"),
                              date=date(2026, 1, 10), type="debit", currency="BRL")]
    await _run_import(session, test_workspace, test_user, account_id, txns)

    txs = await _real_txs(session, account_id)
    assert len(txs) == 1
    assert txs[0].recurring_transaction_id is None
    refreshed = await session.get(RecurringTransaction, bill_id)
    assert refreshed.next_occurrence == date(2026, 1, 10)


@pytest.mark.asyncio
async def test_import_merges_a_tolerated_amount_into_placeholder(
    session, test_user, test_workspace, test_account
):
    """Issue #1073: the charge differs from the bill by cents. The workspace
    allows 5%, so the placeholder becomes the charge, at the charged amount."""
    account_id = test_account.id
    await reconciliation_rule_service.upsert_override(
        session, test_workspace.id, test_user.id,
        reconciliation_policy.MATCH_RECURRING["node"], "same_account_exact",
        {"when": {"amount": {"match": "tolerance", "percent": "5"},
                  "unique_candidate": True}},
    )
    bill = await _make_bill(session, test_workspace, test_user, test_account,
                            amount=Decimal("18.43"), start_date=date(2026, 1, 10))
    bill_id = bill.id
    bill.next_occurrence = date(2026, 2, 10)
    placeholder = Transaction(
        id=uuid.uuid4(), user_id=test_user.id, account_id=account_id,
        workspace_id=test_workspace.id, description="Netflix Subscription",
        amount=Decimal("18.43"), currency="BRL", date=date(2026, 1, 10),
        amount_primary=Decimal("18.43"), fx_rate_used=Decimal("1"),
        type="debit", source="recurring", status="posted",
        recurring_transaction_id=bill_id, created_at=datetime.now(timezone.utc),
    )
    session.add(placeholder)
    await session.commit()
    placeholder_id = placeholder.id

    txns = [TransactionImport(description="NETFLIX SUBSCRIPTION", amount=Decimal("18.71"),
                              date=date(2026, 1, 11), type="debit", currency="BRL")]
    with patch("app.services.import_service.apply_rules_to_transaction",
               new_callable=AsyncMock):
        imported, _, _, _ = await import_transactions(
            session, test_workspace.id, test_user.id, account_id, txns, "csv",
        )

    assert imported == 1
    txs = await _real_txs(session, account_id)
    assert len(txs) == 1
    assert txs[0].id == placeholder_id
    assert txs[0].amount == Decimal("18.71")
    assert txs[0].source == "csv"
    assert txs[0].recurring_transaction_id == bill_id
    assert txs[0].amount_primary == Decimal("18.71")
    assert txs[0].fx_rate_used == 1


@pytest.mark.asyncio
async def test_import_merges_a_late_charge_inside_a_widened_window(
    session, test_user, test_workspace, test_account
):
    """The workspace's bank posts up to ten days late, so a charge eight days
    after the placeholder still becomes the placeholder rather than a twin."""
    account_id = test_account.id
    await reconciliation_rule_service.upsert_override(
        session, test_workspace.id, test_user.id,
        reconciliation_policy.MATCH_RECURRING["node"], "same_account_exact",
        {"when": {"date": {"before_days": 3, "after_days": 10}}},
    )
    bill = await _make_bill(session, test_workspace, test_user, test_account,
                            amount=Decimal("18.43"), start_date=date(2026, 1, 10))
    bill_id = bill.id
    bill.next_occurrence = date(2026, 2, 10)
    placeholder = Transaction(
        id=uuid.uuid4(), user_id=test_user.id, account_id=account_id,
        workspace_id=test_workspace.id, description="Netflix Subscription",
        amount=Decimal("18.43"), currency="BRL", date=date(2026, 1, 10),
        type="debit", source="recurring", status="posted",
        recurring_transaction_id=bill_id, created_at=datetime.now(timezone.utc),
    )
    session.add(placeholder)
    await session.commit()
    placeholder_id = placeholder.id

    txns = [TransactionImport(description="NETFLIX SUBSCRIPTION", amount=Decimal("18.43"),
                              date=date(2026, 1, 18), type="debit", currency="BRL")]
    imported, _, _, _ = await _run_import(
        session, test_workspace, test_user, account_id, txns
    )

    assert imported == 1
    txs = await _real_txs(session, account_id)
    assert len(txs) == 1
    assert txs[0].id == placeholder_id
    assert txs[0].source == "csv"
    assert txs[0].recurring_transaction_id == bill_id


@pytest.mark.asyncio
async def test_import_placeholder_takes_the_files_fx_rate(
    session, test_user, test_workspace, test_account
):
    """The charge is exactly what the bill expected, but the file carries the
    rate the bank applied: it replaces the estimate made at generation."""
    account_id = test_account.id
    placeholder_id = await _usd_placeholder(session, test_workspace, test_user, test_account)

    txns = [TransactionImport(description="NETFLIX SUBSCRIPTION", amount=Decimal("18.43"),
                              date=date(2026, 1, 11), type="debit", currency="USD",
                              fx_rate=Decimal("5.28"))]
    stamp = AsyncMock()
    imported, _, _, _ = await _run_import(
        session, test_workspace, test_user, account_id, txns, stamp=stamp
    )

    assert imported == 1
    txs = await _real_txs(session, account_id)
    assert len(txs) == 1
    assert txs[0].id == placeholder_id
    assert txs[0].amount == Decimal("18.43")
    # 18.43 * 5.28 = 97.3104, and the amount_primary column keeps cents.
    assert txs[0].amount_primary == Decimal("97.31")
    assert txs[0].fx_rate_used == Decimal("5.28")
    stamp.assert_not_awaited()


@pytest.mark.asyncio
async def test_import_placeholder_without_a_rate_is_not_restamped(
    session, test_user, test_workspace, test_account
):
    """Same amount and no rate in the file: nothing new is known about the
    primary-currency cost, so the placeholder keeps its estimate."""
    account_id = test_account.id
    placeholder_id = await _usd_placeholder(session, test_workspace, test_user, test_account)

    txns = [TransactionImport(description="NETFLIX SUBSCRIPTION", amount=Decimal("18.43"),
                              date=date(2026, 1, 11), type="debit", currency="USD")]
    stamp = AsyncMock()
    imported, _, _, _ = await _run_import(
        session, test_workspace, test_user, account_id, txns, stamp=stamp
    )

    assert imported == 1
    txs = await _real_txs(session, account_id)
    assert len(txs) == 1
    assert txs[0].id == placeholder_id
    assert txs[0].amount_primary == pytest.approx(Decimal("90.00"))
    assert txs[0].fx_rate_used == pytest.approx(Decimal("4.8833"))
    stamp.assert_not_awaited()


@pytest.mark.asyncio
async def test_import_tolerated_placeholder_takes_the_files_fx_rate(
    session, test_user, test_workspace, test_account
):
    """Tolerated amount and a rate in the file: the row becomes the charge,
    priced at the rate the bank applied rather than re-estimated."""
    account_id = test_account.id
    await reconciliation_rule_service.upsert_override(
        session, test_workspace.id, test_user.id,
        reconciliation_policy.MATCH_RECURRING["node"], "same_account_exact",
        {"when": {"amount": {"match": "tolerance", "percent": "5"},
                  "unique_candidate": True}},
    )
    placeholder_id = await _usd_placeholder(session, test_workspace, test_user, test_account)

    txns = [TransactionImport(description="NETFLIX SUBSCRIPTION", amount=Decimal("18.71"),
                              date=date(2026, 1, 11), type="debit", currency="USD",
                              fx_rate=Decimal("5.28"))]
    stamp = AsyncMock()
    imported, _, _, _ = await _run_import(
        session, test_workspace, test_user, account_id, txns, stamp=stamp
    )

    assert imported == 1
    txs = await _real_txs(session, account_id)
    assert len(txs) == 1
    assert txs[0].id == placeholder_id
    assert txs[0].amount == Decimal("18.71")
    # 18.71 * 5.28 = 98.7888, which the amount_primary column keeps as cents.
    assert txs[0].amount_primary == Decimal("98.79")
    assert txs[0].fx_rate_used == Decimal("5.28")
    stamp.assert_not_awaited()