from decimal import Decimal

from sqlalchemy import func, select
from sqlalchemy.orm import Session

from app.models.tables import Account, Statement, Transaction


def account_balance(session: Session, account: Account) -> Decimal:
    stmt = session.execute(
        select(Statement).where(Statement.account_id == account.id,
                                Statement.status == "confirmed")
        .order_by(Statement.period_end.desc())).scalars().first()
    q = select(func.coalesce(func.sum(Transaction.amount), 0)).where(
        Transaction.account_id == account.id, Transaction.status == "confirmed")
    if stmt is not None:
        q = q.where(Transaction.booking_date > stmt.period_end)
        base = stmt.closing_balance
    else:
        base = Decimal("0")
    return Decimal(base) + Decimal(session.execute(q).scalar_one())


def total_balance(session: Session) -> Decimal:
    accounts = session.execute(select(Account)).scalars().all()
    return sum((account_balance(session, a) for a in accounts), Decimal("0"))