Repository: bvanelli/actualpy

Issue:
get_accounts does not return a balance
The get_accounts function does not appear to be populating the balances. I have balances in Actual.

Code snippet:

```python
    accounts = get_accounts(actual.session, 'Bank...6877')
    for account in accounts:
        print(account.name)
        print(account)
```

Output:

```
Bank...6877
account_id='ACT-23432-adsfa-40asdf6c-asdf-asdf' balance_current=None balance_available=None mask=None subtype=None offbudget=0 sort_order=None type=None id='234fefas-32423asd-dsfa23-sdf-asdf2' name='Bank...6877' balance_limit=None official_name='COSTLY BLACK' bank='sadfads-asdfa-2dsf-8928adsfasd-2332423' closed=0 tombstone=0
```

Discussion:
codecov[bot]: ## [Codecov](https://app.codecov.io/gh/bvanelli/actualpy/pull/23?dropdown=coverage&src=pr&el=h1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=Brunno+Vanelli) Report
Attention: Patch coverage is `71.42857%` with `2 lines` in your changes are missing coverage. Please review.
> Project coverage is 94.63%. Comparing base [(`2bcc2cb`)](https://app.codecov.io/gh/bvanelli/actualpy/commit/2bcc2cb1248cc59e736e833f5d70b6a0d3d5d393?dropdown=coverage&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=Brunno+Vanelli) to head [(`4675c62`)](https://app.codecov.io/gh/bvanelli/actualpy/commit/4675c623e5d2222a68c3f0498c3da92cb59d0014?dropdown=coverage&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=Brunno+Vanelli).

| [Files](https://app.codecov.io/gh/bvanelli/actualpy/pull/23?dropdown=coverage&src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=Brunno+Vanelli) | Patch % | Lines |
|---|---|---|
| [actual/database.py](https://app.codecov.io/gh/bvanelli/actualpy/pull/23?src=pr&el=tree&filepath=actual%2Fdatabase.py&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=Brunno+Vanelli#diff-YWN0dWFsL2RhdGFiYXNlLnB5) | 71.42% | [2 Missing :warning: ](https://app.codecov.io/gh/bvanelli/actualpy/pull/23?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=Brunno+Vanelli) |

<details><summary>Additional details and impacted files</summary>


```diff
@@            Coverage Diff             @@
##             main      #23      +/-   ##
==========================================
- Coverage   94.75%   94.63%   -0.13%     
==========================================
  Files           9        9              
  Lines        1412     1416       +4     
==========================================
+ Hits         1338     1340       +2     
- Misses         74       76       +2     
```



</details>

[:umbrella: View full report in Codecov by Sentry](https://app.codecov.io/gh/bvanelli/actualpy/pull/23?dropdown=coverage&src=pr&el=continue&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=Brunno+Vanelli).   
:loudspeaker: Have feedback on the report? [Share it here](https://about.codecov.io/codecov-pr-comment-feedback/?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=Brunno+Vanelli).


Patch:
diff --git a/actual/__init__.py b/actual/__init__.py
index 438f8d9..b154bd4 100644
--- a/actual/__init__.py
+++ b/actual/__init__.py
@@ -159,6 +159,11 @@ class Actual(ActualServer):
         # create a clock
         self.load_clock()
 
+    def rename_budget(self, budget_name: str):
+        if not self._file:
+            raise UnknownFileId("No current file loaded.")
+        self.update_user_file_name(self._file.file_id, budget_name)
+
     def export_data(self, output_file: str | PathLike[str] | IO[bytes] = None) -> bytes:
         """Export your data as a zip file containing db.sqlite and metadata.json files. It can be imported into another
         Actual instance by closing an open file (if any), then clicking the “Import file” button, then choosing
diff --git a/actual/api.py b/actual/api.py
index 3ab805c..aa1a3e7 100644
--- a/actual/api.py
+++ b/actual/api.py
@@ -21,7 +21,7 @@ class Endpoints(enum.Enum):
     SYNC = "sync/sync"
     LIST_USER_FILES = "sync/list-user-files"
     GET_USER_FILE_INFO = "sync/get-user-file-info"
-    UPDATE_USER_FILE_NAME = "sync/update-user-file-name"
+    UPDATE_USER_FILE_NAME = "sync/update-user-filename"
     DOWNLOAD_USER_FILE = "sync/download-user-file"
     UPLOAD_USER_FILE = "sync/upload-user-file"
     RESET_USER_FILE = "sync/reset-user-file"
diff --git a/actual/database.py b/actual/database.py
index e223afa..bdafd73 100644
--- a/actual/database.py
+++ b/actual/database.py
@@ -11,21 +11,24 @@ import datetime
 import decimal
 from typing import List, Optional, Union
 
-from sqlalchemy import (
+from sqlalchemy import event, inspect
+from sqlalchemy.orm import class_mapper, object_session
+from sqlmodel import (
     Boolean,
     Column,
+    Field,
     Float,
     ForeignKey,
     Index,
     Integer,
     LargeBinary,
+    Relationship,
+    SQLModel,
     Text,
-    event,
-    inspect,
+    func,
+    select,
     text,
 )
-from sqlalchemy.orm import class_mapper
-from sqlmodel import Field, Relationship, SQLModel
 
 from actual.exceptions import ActualInvalidOperationError
 from actual.protobuf_models import Message
@@ -172,8 +175,26 @@ class Accounts(BaseModel, table=True):
     account_sync_source: Optional[str] = Field(default=None, sa_column=Column("account_sync_source", Text))
 
     payee: "Payees" = Relationship(back_populates="account", sa_relationship_kwargs={"uselist": False})
-    pending_transactions: List["PendingTransactions"] = Relationship(back_populates="account")
-    transactions: List["Transactions"] = Relationship(back_populates="account")
+    transactions: List["Transactions"] = Relationship(
+        back_populates="account",
+        sa_relationship_kwargs={
+            "primaryjoin": (
+                "and_(Accounts.id == Transactions.acct,Transactions.is_parent == 0, Transactions.tombstone==0)"
+            )
+        },
+    )
+
+    @property
+    def balance(self) -> decimal.Decimal:
+        """Returns the current balance of the account. Deleted transactions are ignored."""
+        value = object_session(self).scalar(
+            select(func.coalesce(func.sum(Transactions.amount), 0)).where(
+                Transactions.acct == self.id,
+                Transactions.is_parent == 0,
+                Transactions.tombstone == 0,
+            )
+        )
+        return decimal.Decimal(value) / 100
 
 
 class Banks(BaseModel, table=True):
@@ -195,8 +216,33 @@ class Categories(BaseModel, table=True):
     tombstone: Optional[int] = Field(default=None, sa_column=Column("tombstone", Integer, server_default=text("0")))
     goal_def: Optional[str] = Field(default=None, sa_column=Column("goal_def", Text, server_default=text("null")))
 
-    transactions: List["Transactions"] = Relationship(back_populates="category")
-    group: "CategoryGroups" = Relationship(back_populates="categories", sa_relationship_kwargs={"uselist": False})
+    transactions: List["Transactions"] = Relationship(
+        back_populates="category",
+        sa_relationship_kwargs={
+            "primaryjoin": (
+                "and_(Categories.id == Transactions.category_id,Transactions.is_parent == 0, Transactions.tombstone==0)"
+            )
+        },
+    )
+    group: "CategoryGroups" = Relationship(
+        back_populates="categories",
+        sa_relationship_kwargs={
+            "primaryjoin": "and_(Categories.cat_group == CategoryGroups.id, CategoryGroups.tombstone == 0)",
+            "uselist": False,
+        },
+    )
+
+    @property
+    def balance(self) -> decimal.Decimal:
+        """Returns the current balance of the category. Deleted transactions are ignored."""
+        value = object_session(self).scalar(
+            select(func.coalesce(func.sum(Transactions.amount), 0)).where(
+                Transactions.category_id == self.id,
+                Transactions.is_parent == 0,
+                Transactions.tombstone == 0,
+            )
+        )
+        return decimal.Decimal(value) / 100
 
 
 class CategoryGroups(BaseModel, table=True):
@@ -209,7 +255,12 @@ class CategoryGroups(BaseModel, table=True):
     sort_order: Optional[float] = Field(default=None, sa_column=Column("sort_order", Float))
     tombstone: Optional[int] = Field(default=None, sa_column=Column("tombstone", Integer, server_default=text("0")))
 
-    categories: List["Categories"] = Relationship(back_populates="group")
+    categories: List["Categories"] = Relationship(
+        back_populates="group",
+        sa_relationship_kwargs={
+            "primaryjoin": "and_(CategoryGroups.id == Categories.cat_group, Categories.tombstone == 0)",
+        },
+    )
 
 
 class CategoryMapping(BaseModel, table=True):
@@ -314,7 +365,22 @@ class Payees(BaseModel, table=True):
     )
 
     account: Optional["Accounts"] = Relationship(back_populates="payee", sa_relationship_kwargs={"uselist": False})
-    transactions: List["Transactions"] = Relationship(back_populates="payee")
+    transactions: List["Transactions"] = Relationship(
+        back_populates="payee",
+        sa_relationship_kwargs={"primaryjoin": "and_(Transactions.payee_id == Payees.id, Transactions.tombstone==0)"},
+    )
+
+    @property
+    def balance(self) -> decimal.Decimal:
+        """Returns the current balance of the payee. Deleted transactions are ignored."""
+        value = object_session(self).scalar(
+            select(func.coalesce(func.sum(Transactions.amount), 0)).where(
+                Transactions.payee_id == self.id,
+                Transactions.is_parent == 0,
+                Transactions.tombstone == 0,
+            )
+        )
+        return decimal.Decimal(value) / 100
 
 
 class ReflectBudgets(SQLModel, table=True):
@@ -423,14 +489,36 @@ class Transactions(BaseModel, table=True):
     tombstone: Optional[int] = Field(default=None, sa_column=Column("tombstone", Integer, server_default=text("0")))
     cleared: Optional[int] = Field(default=None, sa_column=Column("cleared", Integer, server_default=text("1")))
     pending: Optional[int] = Field(default=None, sa_column=Column("pending", Integer, server_default=text("0")))
-    parent_id: Optional[str] = Field(default=None, sa_column=Column("parent_id", Text))
+    parent_id: Optional[str] = Field(default=None, sa_column=Column("parent_id", Text, ForeignKey("transactions.id")))
     schedule_id: Optional[str] = Field(default=None, sa_column=Column("schedule", Text, ForeignKey("schedules.id")))
     reconciled: Optional[int] = Field(default=None, sa_column=Column("reconciled", Integer, server_default=text("0")))
 
-    account: Optional["Accounts"] = Relationship(back_populates="transactions")
-    category: Optional["Categories"] = Relationship(back_populates="transactions")
-    payee: Optional["Payees"] = Relationship(back_populates="transactions")
-    schedule: Optional["Schedules"] = Relationship(back_populates="transactions")
+    account: "Accounts" = Relationship(back_populates="transactions")
+    category: Optional["Categories"] = Relationship(
+        back_populates="transactions",
+        sa_relationship_kwargs={
+            "primaryjoin": "and_(Transactions.category_id == Categories.id, Categories.tombstone==0)"
+        },
+    )
+    payee: Optional["Payees"] = Relationship(
+        back_populates="transactions",
+        sa_relationship_kwargs={"primaryjoin": "and_(Transactions.payee_id == Payees.id, Payees.tombstone==0)"},
+    )
+    schedule: Optional["Schedules"] = Relationship(
+        back_populates="transactions",
+        sa_relationship_kwargs={
+            "primaryjoin": "and_(Transactions.schedule_id == Schedules.id, Schedules.tombstone==0)"
+        },
+    )
+    parent: Optional["Transactions"] = Relationship(
+        back_populates="splits", sa_relationship_kwargs={"remote_side": "Transactions.id"}
+    )
+    splits: List["Transactions"] = Relationship(
+        back_populates="parent",
+        sa_relationship_kwargs={
+            "primaryjoin": "and_(Transactions.id == remote(Transactions.parent_id), remote(Transactions.tombstone)==0)"
+        },
+    )
 
     def get_date(self) -> datetime.date:
         return datetime.datetime.strptime(str(self.date), "%Y%m%d").date()
@@ -472,8 +560,6 @@ class PendingTransactions(SQLModel, table=True):
     description: Optional[str] = Field(default=None, sa_column=Column("description", Text))
     date: Optional[str] = Field(default=None, sa_column=Column("date", Text))
 
-    account: Optional["Accounts"] = Relationship(back_populates="pending_transactions")
-
 
 for t_entry in SQLModel._sa_registry.mappers:
     t_name = t_entry.entity.__tablename__
diff --git a/actual/queries.py b/actual/queries.py
index 4c60241..e06e721 100644
--- a/actual/queries.py
+++ b/actual/queries.py
@@ -33,6 +33,7 @@ def get_transactions(
     start_date: datetime.date = None,
     end_date: datetime.date = None,
     notes: str = None,
+    account: Accounts | str | None = None,
     include_deleted: bool = False,
 ) -> typing.List[Transactions]:
     """
@@ -42,7 +43,8 @@ def get_transactions(
     :param start_date: optional start date for the transaction period (inclusive)
     :param end_date: optional end date for the transaction period (exclusive)
     :param notes: optional notes filter for the transactions, case-insensitive.
-    :param include_deleted: includes deleted transactions from the search
+    :param account: optional account (either Account object or Account name) filter for the transactions.
+    :param include_deleted: includes deleted transactions from the search.
     :return: list of transactions with `account`, `category` and `payee` pre-loaded.
     """
     query = (
@@ -70,6 +72,10 @@ def get_transactions(
         query = query.filter(Transactions.date < int(datetime.date.strftime(end_date, "%Y%m%d")))
     if not include_deleted:
         query = query.filter(sqlalchemy.func.coalesce(Transactions.tombstone, 0) == 0)
+    if account:
+        account = get_account(s, account)
+        if account:
+            query = query.filter(Transactions.acct == account.id)
     if notes:
         query = query.filter(Transactions.notes.ilike(f"%{sqlalchemy.text(notes).compile()}%"))
     return query.all()
@@ -126,6 +132,8 @@ def create_transaction(
     :return: the generated transaction object.
     """
     acct = get_account(s, account)
+    if acct is None:
+        raise ActualError(f"Account {account} not found")
     payee = get_or_create_payee(s, payee)
     if category:
         category_id = get_or_create_category(s, category, "").id
@@ -285,7 +293,7 @@ def get_or_create_payee(s: Session, name: str | Payees | None) -> Payees:
 
 
 def create_account(
-    s: Session, name: str, initial_balance: decimal.Decimal = decimal.Decimal(0), off_budget: bool = False
+    s: Session, name: str, initial_balance: decimal.Decimal | float = decimal.Decimal(0), off_budget: bool = False
 ) -> Accounts:
     """Creates a new account with the name and balance. Make sure you avoid creating accounts with duplicate names, as
     it makes it difficult to find them without knowing the unique id beforehand."""
@@ -295,7 +303,7 @@ def create_account(
     payee = create_payee(s, None)
     payee.transfer_acct = acct.id
     s.add(payee)
-    # if there is an no initial balance, create it
+    # if there is no initial balance, create it
     if initial_balance:
         payee_starting = get_or_create_payee(s, "Starting Balance")
         category = get_or_create_category(s, "Starting Balances", "Income")


Test patch:
diff --git a/tests/test_database.py b/tests/test_database.py
new file mode 100644
index 0000000..3863165
--- /dev/null
+++ b/tests/test_database.py
@@ -0,0 +1,73 @@
+import decimal
+import tempfile
+from datetime import date, timedelta
+
+import pytest
+from sqlmodel import Session, create_engine
+
+from actual import ActualError
+from actual.database import SQLModel
+from actual.queries import (
+    create_account,
+    create_transaction,
+    create_transfer,
+    get_or_create_category,
+    get_or_create_payee,
+    get_transactions,
+)
+
+
+@pytest.fixture
+def session():
+    with tempfile.NamedTemporaryFile() as f:
+        sqlite_url = f"sqlite:///{f.name}"
+        engine = create_engine(sqlite_url, connect_args={"check_same_thread": False})
+        SQLModel.metadata.create_all(engine)
+        with Session(engine) as session:
+            yield session
+
+
+def test_account_relationships(session):
+    today = date.today()
+    bank = create_account(session, "Bank", 5000)
+    create_account(session, "Savings")
+    landlord = get_or_create_payee(session, "Landlord")
+    rent = get_or_create_category(session, "Rent")
+    rent_payment = create_transaction(session, today, "Bank", "Landlord", "Paying rent", "Rent", -1200)
+    utilities_payment = create_transaction(session, today, "Bank", "Landlord", "Utilities", "Rent", -50)
+    create_transfer(session, today, "Bank", "Savings", 200, "Saving money")
+    session.commit()
+    assert bank.balance == decimal.Decimal(3550)
+    assert landlord.balance == decimal.Decimal(-1250)
+    assert rent.balance == decimal.Decimal(-1250)
+    assert rent_payment.category == rent
+    assert len(bank.transactions) == 4  # includes starting balance and one transfer
+    assert len(landlord.transactions) == 2
+    assert len(rent.transactions) == 2
+    # let's now void the utilities_payment
+    utilities_payment.delete()
+    session.commit()
+    assert bank.balance == decimal.Decimal(3600)
+    assert landlord.balance == decimal.Decimal(-1200)
+    assert rent.balance == decimal.Decimal(-1200)
+    assert len(bank.transactions) == 3
+    assert len(landlord.transactions) == 1
+    assert len(rent.transactions) == 1
+    # delete the payee and category
+    rent.delete()
+    landlord.delete()
+    session.commit()
+    assert rent_payment.category is None
+    assert rent_payment.payee is None
+    # find the deleted transaction again
+    deleted_transaction = get_transactions(
+        session, today - timedelta(days=1), today + timedelta(days=1), "Util", bank, True
+    )
+    assert [utilities_payment] == deleted_transaction
+
+
+def test_create_transaction_without_account_error(session):
+    with pytest.raises(ActualError):
+        create_transaction(session, date.today(), "foo", "")
+    with pytest.raises(ActualError):
+        create_transaction(session, date.today(), None, "")
diff --git a/tests/test_integration.py b/tests/test_integration.py
index fea888f..9511653 100644
--- a/tests/test_integration.py
+++ b/tests/test_integration.py
@@ -5,7 +5,7 @@ from testcontainers.core.container import DockerContainer
 from testcontainers.core.waiting_utils import wait_for_logs
 
 from actual import Actual
-from actual.exceptions import ActualDecryptionError, AuthorizationError
+from actual.exceptions import ActualDecryptionError, ActualError, AuthorizationError
 from actual.queries import (
     create_transaction,
     get_accounts,
@@ -36,10 +36,12 @@ def test_create_user_file(actual_server):
         actual.upload_budget()
         # add some entries to the budget
         acct = get_or_create_account(actual.session, "Bank")
+        assert acct.balance == 0
         payee = get_or_create_payee(actual.session, "Landlord")
         category = get_or_create_category(actual.session, "Rent", "Fixed Costs")
         create_transaction(actual.session, datetime.date(2024, 5, 22), acct, payee, "Paying rent", category, -500)
         actual.commit()
+        assert acct.balance == -500
         # list user files
         new_user_files = actual.list_user_files().data
         assert len(new_user_files) == 1
@@ -76,3 +78,19 @@ def test_encrypted_file(actual_server):
         Actual(
             f"http://localhost:{port}", password="mypass", encryption_password="mywrongpass", file="My Encrypted Budget"
         ).download_budget()
+
+
+def test_update_file_name(actual_server):
+    port = actual_server.get_exposed_port(5006)
+    with Actual(f"http://localhost:{port}", password="mypass", bootstrap=True) as actual:
+        assert len(actual.list_user_files().data) == 0
+        actual.create_budget("My Budget")
+        actual.upload_budget()
+        actual.rename_budget("Other name")
+        files = actual.list_user_files().data
+        assert len(files) == 1
+        assert files[0].name == "Other name"
+    # should raise an error if budget does not exist
+    with Actual(f"http://localhost:{port}", password="mypass") as actual:
+        with pytest.raises(ActualError):
+            actual.rename_budget("Failing name")