"""WorksheetAccountContractTaxableRevenue repository."""

import typing

from abacus_common_logic.connectors.database import db
from sqlalchemy import and_, func, literal, literal_column, or_, select, table

from payment.constants import constants
from payment.models import (
    PaymentGroupPaymentAccount,
    PaymentGroupPaymentBatch,
    PaymentGroupPaymentBatchAccount,
    WorksheetAccountContractTaxableRevenue,
)


def catchup_taxable_revenue(
    limit: int,
    offset: int,
    account_ids: typing.Optional[typing.List[int]] = None,
) -> typing.Tuple[typing.List[WorksheetAccountContractTaxableRevenue], int]:
    """
    Catchup taxable revenue.

    Return items where related last payment statement period is less
    than statement period of specific item.
    """
    # fmt: off
    last_payment_subquery = (
        select(
            PaymentGroupPaymentAccount.account_id,
            func.max(PaymentGroupPaymentAccount.current_statement_period_id).label(
                'last_payment_period_id'
            ),
        )
        .select_from(PaymentGroupPaymentAccount)
        .join(
            PaymentGroupPaymentBatchAccount,
            PaymentGroupPaymentAccount.payment_group_payment_account_id
            == PaymentGroupPaymentBatchAccount.payment_group_payment_account_id,
        )
        .join(
            table('abacus_state').alias('pgpa_st'),
            and_(
                literal_column('pgpa_st.parent_table_id') == PaymentGroupPaymentAccount.payment_group_payment_account_id,
                literal_column('pgpa_st.parent_table_name') == PaymentGroupPaymentAccount.__tablename__,
                literal_column('pgpa_st.action_name') == constants.PAYMENT_GROUP_PAYMENT_ACTIONS.SEND_PAYMENTS,
                literal_column('pgpa_st.action_status') != constants.ACTION_STATUSES.REJECTED,
            ),
        )
        .join(
            table('abacus_state').alias('pgpba_st'),
            and_(
                literal_column('pgpba_st.parent_table_id') == PaymentGroupPaymentBatchAccount.payment_group_payment_batch_id,
                literal_column('pgpba_st.parent_table_name') == PaymentGroupPaymentBatch.__tablename__,
                literal_column('pgpba_st.action_name') == constants.PAYMENT_GROUP_PAYMENT_BATCH_ACTION_NAMES.SEND_PAYMENT,
                literal_column('pgpba_st.action_status') == constants.ACTION_STATUSES.COMPLETE,
            ),
        )
        .where(
            PaymentGroupPaymentAccount.deleted_at == None,
            or_(
                PaymentGroupPaymentAccount.account_id.in_(account_ids or []),
                literal(not account_ids),
            ),
        )
        .group_by(PaymentGroupPaymentAccount.account_id)
    ).subquery()

    stmt = (
        select(WorksheetAccountContractTaxableRevenue)
        .where(
            WorksheetAccountContractTaxableRevenue.deleted_at == None,
        )
        .join(
            table('statement_period').alias('sp'),
            and_(
                literal_column('sp.statement_period_id') == WorksheetAccountContractTaxableRevenue.statement_period_id,
                literal_column('sp.statement_period_status') == constants.STATEMENT_PERIOD_STATUSES.CLOSED
            ),
        )
        .outerjoin(
            last_payment_subquery,
            WorksheetAccountContractTaxableRevenue.account_id == last_payment_subquery.c.account_id,
        )
        .where(
            or_(
                WorksheetAccountContractTaxableRevenue.statement_period_id > last_payment_subquery.c.last_payment_period_id,
                last_payment_subquery.c.last_payment_period_id == None,
            ),
            or_(
                WorksheetAccountContractTaxableRevenue.account_id.in_(account_ids or []),
                literal(not account_ids),
            ),
        )
    )
    # fmt: on

    total_count = db.session.execute(
        select(func.count()).select_from(stmt.subquery())
    ).scalar_one()
    items = db.session.execute(stmt.limit(limit).offset(offset)).scalars().all()

    return items, total_count