"""Payment Group Payment Batch model."""

from abacus_common_logic.models.base import BaseModel, db
from sqlalchemy import and_, join, literal_column, select, table, text


class PaymentGroupPaymentBatch(BaseModel):
    """Payment Group Payment Batch model."""

    __tablename__ = 'payment_group_payment_batch'
    payment_group_payment_batch_id = db.Column(db.Integer, primary_key=True)
    payment_group_payment_id = db.Column(
        db.Integer,
        db.ForeignKey('payment_group_payment.payment_group_payment_id'),
        nullable=False,
    )
    payoneer_program_id = db.Column(db.Integer, nullable=False)
    batch_num = db.Column(db.Integer, nullable=False, default=1)

    batch_accounts = db.relationship(
        'PaymentGroupPaymentBatchAccount',
        backref='payment_group_payment_batch',
        cascade='all, delete-orphan',
    )

    @classmethod
    def get_by_payment_group_payment_and_batch_status(
        cls, payment_group_payment_id: int, payment_batch_status: str = None
    ) -> list:
        """Get payment batches for specified params.

        The query returns all payment batches if payment_batch_status is None.
        Arg:
            payment_group_payment_id(int): id of the payment_group_payment
            payment_batch_status(str)(Optional):
                status of the payment batch is either failed, pending, or success
        Returns:
            A List of payment batches
        """
        map_payment_batch_status = {
            'failed': ['error'],
            'success': ['complete'],
            'pending': ['init', 'running'],
        }
        query = cls._query_by_payment_group_payment_id(payment_group_payment_id)

        if payment_batch_status and payment_batch_status in map_payment_batch_status:
            query = query.where(
                literal_column('st_pgpb.action_status').in_(
                    map_payment_batch_status[payment_batch_status]
                )
            )

        return db.session.execute(query).mappings().all()

    @staticmethod
    def _query_by_payment_group_payment_id(payment_group_payment_id: int):
        """
        Build a query to get payment_group_payment_batche's by payment_group_payment_id.

        Args:
            payment_group_payment_id (int): id of payment_group_payment
        """
        return (
            select(
                literal_column('pgpb.*'),
                literal_column('st_pgpb.abacus_state_id').label('abacus_state_id'),
                literal_column('st_pgpb.action_status').label('action_status'),
                literal_column('st_pgpb.message').label('error_message'),
            )
            .where(
                and_(
                    literal_column('st_pgpb.action_name') == 'send_payment',
                    literal_column('pgpb.payment_group_payment_id')
                    == payment_group_payment_id,
                )
            )
            .select_from(
                join(
                    table('payment_group_payment_batch').alias('pgpb'),
                    table('abacus_state').alias('st_pgpb'),
                    and_(
                        text(
                            'st_pgpb.parent_table_id=pgpb.payment_group_payment_batch_id'
                        ),
                        text('st_pgpb.parent_table_name="payment_group_payment_batch"'),
                    ),
                )
            )
        )