from sqlalchemy import func

from app.extensions import db
from app.models.master import Cashier, SavingsPlanMember
from app.models.transactions import Credit, Debit


def _active_member_ids():
    return [
        m[0]
        for m in db.session.query(SavingsPlanMember.member_id)
        .filter(SavingsPlanMember.status == "active")
        .all()
    ]


def get_cashier_balance_map(*, active_members_only: bool = True) -> dict[int, float]:
    member_ids = _active_member_ids() if active_members_only else None

    credit_query = db.session.query(
        Credit.cashier_id, func.sum(Credit.total_amount).label("total_credit")
    ).filter(Credit.cashier_id.isnot(None))
    debit_query = db.session.query(
        Debit.cashier_id, func.sum(Debit.amount).label("total_debit")
    ).filter(Debit.cashier_id.isnot(None))

    if member_ids is not None:
        credit_query = credit_query.filter(Credit.member_id.in_(member_ids))
        debit_query = debit_query.filter(Debit.member_id.in_(member_ids))

    credit_rows = credit_query.group_by(Credit.cashier_id).all()
    debit_rows = debit_query.group_by(Debit.cashier_id).all()

    totals: dict[int, dict[str, float]] = {}
    for cashier_id, total_credit in credit_rows:
        if cashier_id is None:
            continue
        totals[int(cashier_id)] = {"credit": float(total_credit or 0), "debit": 0.0}
    for cashier_id, total_debit in debit_rows:
        if cashier_id is None:
            continue
        cashier_id = int(cashier_id)
        if cashier_id not in totals:
            totals[cashier_id] = {"credit": 0.0, "debit": float(total_debit or 0)}
        else:
            totals[cashier_id]["debit"] = float(total_debit or 0)

    # Include cashiers with no transactions
    for cashier in Cashier.query.all():
        totals.setdefault(int(cashier.id), {"credit": 0.0, "debit": 0.0})

    return {cid: (vals["credit"] - vals["debit"]) for cid, vals in totals.items()}

