"""Payment Group Payment Account repository."""

import typing

from abacus_common_logic.models.base import db
from sqlalchemy import (
    and_,
    func,
    literal,
    literal_column,
    not_,
    or_,
    outerjoin,
    select,
    table,
    text,
)

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


def get_last_payments(
    limit: int,
    offset: int,
    account_ids: typing.List[int],
) -> typing.Tuple[typing.List[PaymentGroupPaymentAccount], int]:
    """
    Return last payments which have needed action status (from abacus state)
    and completed corresponding payment group payment batch.
    """
    # 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(
            PaymentGroupPayment,
            PaymentGroupPayment.payment_group_payment_id
            == PaymentGroupPaymentAccount.payment_group_payment_id,
        )
        .where(
            PaymentGroupPaymentAccount.deleted_at == None,
            PaymentGroupPayment.deleted_at == None,
            PaymentGroupPaymentAccount.payoneer_program_id != 0,
            PaymentGroupPaymentAccount.prior_payment_group_payment_id == None,
            or_(
                PaymentGroupPaymentAccount.account_id.in_(account_ids or []),
                literal(not account_ids),
            ),
            select(literal(1))
            .select_from(table('abacus_state'))
            .where(
                and_(
                    literal_column('parent_table_id') == PaymentGroupPaymentBatchAccount.payment_group_payment_batch_id,
                    literal_column('parent_table_name') == PaymentGroupPaymentBatch.__tablename__,
                    literal_column('action_name') == constants.PAYMENT_GROUP_PAYMENT_BATCH_ACTION_NAMES.SEND_PAYMENT,
                    literal_column('action_status') == constants.ACTION_STATUSES.COMPLETE,
                )
            )
            .exists(),
            select(literal(1))
            .select_from(table('abacus_state'))
            .where(
                and_(
                    literal_column('parent_table_id') == PaymentGroupPaymentAccount.payment_group_payment_account_id,
                    literal_column('parent_table_name') == PaymentGroupPaymentAccount.__tablename__,
                    literal_column('action_name') == constants.PAYMENT_GROUP_PAYMENT_ACCOUNT_ACTION_NAMES.SEND_PAYMENTS,
                    literal_column('action_status') == constants.ACTION_STATUSES.COMPLETE,
                )
            )
            .exists(),
            select(literal(1))
            .select_from(table('abacus_state'))
            .where(
                and_(
                    literal_column('parent_table_id') == PaymentGroupPaymentAccount.payment_group_payment_id,
                    literal_column('parent_table_name') == PaymentGroupPayment.__tablename__,
                    literal_column('action_name') == constants.PAYMENT_GROUP_PAYMENT_ACTIONS.SEND_PAYMENTS,
                    literal_column('action_status') == constants.ACTION_STATUSES.COMPLETE,
                )
            )
            .exists(),
        )
        .group_by(PaymentGroupPaymentAccount.account_id)
        .subquery()
    )

    stmt = (
        select(PaymentGroupPaymentAccount)
        .join(
            last_payment_subquery,
            PaymentGroupPaymentAccount.account_id == last_payment_subquery.c.account_id,
        )
        .where(
            PaymentGroupPaymentAccount.current_statement_period_id == last_payment_subquery.c.last_payment_period_id,
            PaymentGroupPaymentAccount.deleted_at == None,
            PaymentGroupPaymentAccount.payoneer_program_id != 0,
            PaymentGroupPaymentAccount.prior_payment_group_payment_id == None,
        )
        .order_by(PaymentGroupPaymentAccount.account_id)
    )
    # 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


def get_payment_group_payment_account_status_overviews(
    payment_group_payment_ids: typing.List[int],
) -> typing.List[object]:
    """Get the total number of accounts payments failed/rejected/successful for one or more payment_group_payments.

    Arg:
        payment_group_payment_id(List[int]): ids of the payment_group_payments
    Returns:
        One record per payment_group_payment having fields
            number_of_canceled_payments,
            number_of_failed_payments
            number_of_pending_payments
            number_of_successful_payments
            number_of_payments_failed_with_batch
    """  # noqa: E501
    query = (
        select(
            literal_column('pgpb.payment_group_payment_id').label(
                'payment_group_payment_id'
            ),
            literal_column(
                """
            SUM(
                CASE WHEN st_pgpb.action_status = 'error' THEN 1 ELSE 0 END
            )
        """
            ).label('number_of_payments_failed_with_batch'),
            literal_column(
                """
            SUM(
                CASE WHEN st_pgpb.action_status = 'complete'
                    AND st_pgpba.action_status = 'rejected'
                THEN 1 ELSE 0 END
            )
        """
            ).label('number_of_canceled_payments'),
            literal_column(
                """
            SUM(
                CASE WHEN st_pgpb.action_status = 'complete'
                    AND st_pgpba.action_status = 'error'
                THEN 1 ELSE 0 END
            )
        """
            ).label('number_of_failed_payments'),
            literal_column(
                """
            SUM(
                CASE WHEN st_pgpb.action_status = 'complete'
                    AND st_pgpba.action_status = 'complete'
                THEN 1 ELSE 0 END
            )
        """
            ).label('number_of_successful_payments'),
            literal_column(
                """
            SUM(
                CASE WHEN st_pgpb.action_status = 'complete'
                    AND st_pgpba.action_status in ('init', 'running')
                THEN 1 ELSE 0 END
            )
        """
            ).label('number_of_pending_payments'),
        )
        .where(
            literal_column('pgpb.payment_group_payment_id').in_(
                payment_group_payment_ids
            )
        )
        .select_from(
            outerjoin(
                table('payment_group_payment_batch').alias('pgpb'),
                table('payment_group_payment_batch_account').alias('pgpba'),
                text(
                    'pgpb.payment_group_payment_batch_id=pgpba.payment_group_payment_batch_id'
                ),  # noqa: E501
            )
            .outerjoin(
                table('payment_group_payment_account').alias('pgpa'),
                text(
                    'pgpa.payment_group_payment_account_id=pgpba.payment_group_payment_account_id'
                ),  # noqa: E501
            )
            .outerjoin(
                table('abacus_state').alias('st_pgpba'),
                and_(
                    text(
                        'st_pgpba.parent_table_id=pgpba.payment_group_payment_account_id'
                    ),  # noqa: E501
                    text('st_pgpba.parent_table_name="payment_group_payment_account"'),
                    text('st_pgpba.action_name="send_payments"'),
                ),
            )
            .outerjoin(
                table('abacus_state').alias('st_pgpb'),
                and_(
                    text(
                        'st_pgpb.parent_table_id=pgpba.payment_group_payment_batch_id'
                    ),  # noqa: E501
                    text('st_pgpb.parent_table_name="payment_group_payment_batch"'),
                    text('st_pgpb.action_name="send_payment"'),
                ),
            )
        )
        .group_by(literal_column('pgpb.payment_group_payment_id'))
    )

    return db.session.execute(query).fetchall()


def payments_search(
    limit: int,
    offset: int,
    account_ids: set[int] | None = None,
    contract_ids: set[int] | None = None,
    payment_statuses: set[str] | None = None,
) -> tuple[list[PaymentGroupPaymentAccount], int]:
    """Search payments by criteria."""
    # fmt: off
    stmt = (
        select(PaymentGroupPaymentAccount)
        .distinct()
        .join(
            PaymentGroupPaymentAccountDetail,
            PaymentGroupPaymentAccount.payment_group_payment_account_id
            == PaymentGroupPaymentAccountDetail.payment_group_payment_account_id,
        )
        .where(
            PaymentGroupPaymentAccount.deleted_at.is_(None),
            PaymentGroupPaymentAccount.prior_payment_group_payment_id.is_(None),
        )
    )

    if account_ids:
        stmt = stmt.where(
            PaymentGroupPaymentAccount.account_id.in_(account_ids)
        )

    if contract_ids:
        stmt = stmt.where(
            PaymentGroupPaymentAccountDetail.contract_id.in_(contract_ids),
            PaymentGroupPaymentAccountDetail.deleted_at.is_(None),
        )

    if payment_statuses:
        # TODO: This filter does not cover cases where we haven't yet sent to payoneer
        #   or the payment passed approval. So it makes sense to extend this later if
        #   needed.
        stmt = stmt.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').in_(payment_statuses),
            ),
        )
    # 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