"""Repository for worksheet_account_contract_closing_balance."""

import typing

from abacus_common_logic.connectors.database import db
from sqlalchemy import and_, func, select

from payment import models
from payment.models.worksheet_account_contract_closing_balance import (
    WorksheetAccountContractClosingBalance,
)


def get_closing_balances_related_to_payment_accounts(ids: typing.List[int]):
    """Return WorksheetAccountContractClosingBalance data and payment_ids."""
    payment_accounts_to_worksheets_subquery = (
        select(
            models.PaymentGroupPaymentAccountDetail.payment_group_payment_account_id,  # noqa
            func.max(
                models.WorksheetPayableBalanceAfterTax.worksheet_account_contract_closing_balance_id  # noqa
            ).label('worksheet_account_contract_closing_balance_id'),
        )
        .select_from(models.PaymentGroupPaymentAccountDetail)
        .outerjoin(
            models.WorksheetPayableBalanceAfterTax,
            and_(
                models.WorksheetPayableBalanceAfterTax.worksheet_account_contract_payable_after_tax_id  # noqa
                == models.PaymentGroupPaymentAccountDetail.worksheet_account_contract_payable_after_tax_id,  # noqa
                models.WorksheetPayableBalanceAfterTax.deleted_at == None,  # noqa
            ),
        )
        .where(
            and_(
                models.PaymentGroupPaymentAccountDetail.payment_group_payment_account_id.in_(
                    ids
                ),  # noqa
                models.PaymentGroupPaymentAccountDetail.deleted_at == None,  # noqa
            )
        )
        .group_by(
            models.PaymentGroupPaymentAccountDetail.payment_group_payment_account_id
        )
        .subquery()
    )

    worksheets_query = (
        select(
            *WorksheetAccountContractClosingBalance.__table__.columns,
            payment_accounts_to_worksheets_subquery.c.payment_group_payment_account_id,  # noqa
        )
        .select_from(WorksheetAccountContractClosingBalance)
        .join(
            payment_accounts_to_worksheets_subquery,
            WorksheetAccountContractClosingBalance.worksheet_account_contract_closing_balance_id  # noqa
            == payment_accounts_to_worksheets_subquery.c.worksheet_account_contract_closing_balance_id,  # noqa
        )
    )

    return db.session.execute(worksheets_query).all()