from datetime import date
from decimal import Decimal, ROUND_HALF_UP
from sqlalchemy import func
from sqlalchemy.orm import Session
from app.models.finance import Account, JournalEntry
from app.models.accounting import AccountingPeriod, AccountBalance, JournalLine
from app.services.accounting import d


def recompute_account_balances(db: Session) -> None:
    db.query(AccountBalance).delete()
    db.flush()
    rows = db.query(
        JournalLine.account_id,
        JournalEntry.period_id,
        func.coalesce(JournalLine.branch_id, "").label("branch"),
        func.sum(JournalLine.debit).label("debit_sum"),
        func.sum(JournalLine.credit).label("credit_sum"),
    ).join(JournalEntry, JournalEntry.id == JournalLine.entry_id).filter(
        JournalEntry.status == "posted"
    ).group_by(JournalLine.account_id, JournalEntry.period_id, "branch").all()

    periods = {p.id: p for p in db.query(AccountingPeriod).all()}
    for account_id, period_id, branch, debit_sum, credit_sum in rows:
        opening = Decimal("0")
        period = periods.get(period_id)
        if period:
            prev = db.query(AccountBalance).join(
                AccountingPeriod, AccountingPeriod.id == AccountBalance.period_id
            ).filter(
                AccountBalance.account_id == account_id,
                AccountBalance.branch_id == branch,
                AccountingPeriod.period_code < period.period_code,
            ).order_by(AccountingPeriod.period_code.desc()).first()
            if prev:
                opening = Decimal(str(prev.closing_balance))
        account = db.query(Account).filter(Account.id == account_id).first()
        normal = (account.normal_balance if account else None) or "D"
        total_debit = d(debit_sum or 0)
        total_credit = d(credit_sum or 0)
        net = total_debit - total_credit
        if normal == "C":
            net = -net
        closing = d(opening + net)
        db.add(AccountBalance(
            account_id=account_id,
            period_id=period_id,
            branch_id=branch,
            opening_balance=opening,
            total_debit=total_debit,
            total_credit=total_credit,
            closing_balance=closing,
        ))
    db.commit()


def account_balance(db: Session, account_id: str, branch_id: str | None = None,
                    as_of: date | None = None) -> Decimal:
    query = db.query(JournalLine).join(
        JournalEntry, JournalEntry.id == JournalLine.entry_id
    ).filter(
        JournalEntry.status == "posted",
        JournalLine.account_id == account_id,
    )
    if branch_id:
        query = query.filter(JournalLine.branch_id == branch_id)
    if as_of:
        query = query.filter(JournalEntry.entry_date <= as_of)
    debit = sum((d(ln.debit) for ln in query.all()), Decimal("0"))
    credit = sum((d(ln.credit) for ln in query.all()), Decimal("0"))
    account = db.query(Account).filter(Account.id == account_id).first()
    normal = (account.normal_balance if account else None) or "D"
    if normal == "C":
        return d(credit - debit)
    return d(debit - credit)