"""Payment Group Payment Account model. Yes, really."""

from abacus_common_data.currency import get_currency_object_from_code
from abacus_common_logic.models.base import BaseModel, db
from abacus_common_logic.utils.users import get_flask_user_id
from sqlalchemy import (
    and_,
    asc,
    delete,
    desc,
    func,
    literal_column,
    or_,
    outerjoin,
    select,
    table,
    text,
    update,
)


class PaymentGroupPaymentAccount(BaseModel):
    """Payment Group Payment Account model."""

    __tablename__ = 'payment_group_payment_account'
    payment_group_payment_account_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=True)
    prior_payment_group_payment_id = db.Column(db.Integer, nullable=True, default=None)
    account_id = db.Column(db.Integer, nullable=False)
    last_statement_period_id = db.Column(db.Integer, nullable=True)
    current_statement_period_id = db.Column(db.Integer, nullable=False)
    currency_code = db.Column(db.String(3), nullable=False)
    last_payment = db.Column(db.Numeric(20, 2), nullable=False, default=0)
    contracts_payable = db.Column(db.JSON, nullable=False, default=[])
    current_balance = db.Column(db.Numeric(20, 2), nullable=False, default=0)
    tax_withholding = db.Column(db.Numeric(20, 2), nullable=True)
    vat_amount = db.Column(db.Numeric(20, 2), nullable=True)
    balance_after_tax = db.Column(db.Numeric(20, 2), nullable=False)
    note = db.Column(db.String(255), nullable=True)
    deleted_at = db.Column(db.DateTime, nullable=True)
    deleted_by = db.Column(db.String(255), nullable=True)

    payment_group_payment_batch_account = db.relationship(
        'PaymentGroupPaymentBatchAccount',
        backref='payment_group_payment_account',
        cascade='all, delete-orphan',
        uselist=False,
    )
    details = db.relationship(
        'PaymentGroupPaymentAccountDetail',
        backref='payment_group_payment_account',
        cascade='all, delete-orphan',
    )

    @property
    def currency_name(self):
        """Class property for currency name."""
        return get_currency_object_from_code(self.currency_code).get('currency_name')

    @classmethod
    def get_by_payment_group_payment(
        cls,
        limit,
        offset,
        order_by,
        order_dir,
        payment_group_payment_id,
        is_pending=False,
        search_term=None,
    ):
        """Get a page of payment_accounts for a payment_group_payment_id."""
        query = (
            cls._query_by_payment_group_payment_id(
                is_pending, payment_group_payment_id, search_term
            )
            .order_by(desc(order_by) if order_dir == 'desc' else asc(order_by))
            .offset(offset)
            .limit(limit)
        )

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

    @classmethod
    def get_by_payment_group_payment_count(
        cls, payment_group_payment_id, is_pending=False, search_term=None
    ):
        """Get total count of payment_accounts for a payment_group_payment_id."""
        query = cls._query_by_payment_group_payment_id(
            is_pending, payment_group_payment_id, search_term
        )
        return db.session.execute(
            select(func.count()).select_from(query.subquery())
        ).scalar_one()

    @classmethod
    def group_by_currency_code(cls, payment_group_payment_id):
        """Get payment_group_payment_account totals grouped by currency_code."""
        return (
            db.session.execute(
                select(
                    cls.currency_code,
                    cls.payment_group_payment_id,
                    func.count(cls.account_id).label('account_count'),
                    func.sum(cls.balance_after_tax).label('currency_total'),
                )
                .where(
                    cls.payment_group_payment_id == payment_group_payment_id,
                    cls.deleted_at.is_(None),
                    cls.prior_payment_group_payment_id.is_(None),
                )
                .group_by(cls.currency_code)
                .order_by(func.count(cls.account_id).desc())
            )
            .mappings()
            .all()
        )

    @classmethod
    def group_by_currency_code_for_many_payment_group_payments(cls, ids):
        """Get payment_group_payment_account totals grouped by currency_code for many payment_group_payments."""
        return (
            db.session.execute(
                select(
                    cls.currency_code,
                    cls.payment_group_payment_id,
                    func.count(cls.account_id).label('account_count'),
                    func.sum(cls.balance_after_tax).label('currency_total'),
                )
                .where(
                    cls.payment_group_payment_id.in_(ids),
                    cls.deleted_at.is_(None),
                    cls.prior_payment_group_payment_id.is_(None),
                )
                .group_by(cls.currency_code)
                .group_by(cls.payment_group_payment_id)
                .order_by(func.count(cls.account_id).desc())
            )
            .mappings()
            .all()
        )

    @classmethod
    def last_posted_payment_by_account(cls, account_id):
        """Return a specified account's most recently posted payment, if any.

        The _query_by_post_status() method returns record if action_status of
        payment_group_payment, payment_group_payment_batch and
        payment_group_payment_account is complete.
        """
        query = cls._query_by_post_status(account_id=account_id, is_posted=True)
        return db.session.execute(query).mappings().first()

    @classmethod
    def soft_delete_by_payment_group_payment(
        cls, payment_group_payment_id, commit: bool = True
    ):
        """Soft delete payment_accounts belonging to payment_group_payment.

        Args:
            payment_group_payment_id: ID of the payment group payment
            commit: whether to commit the transaction
        """
        db.session.execute(
            update(cls)
            .where(
                cls.payment_group_payment_id == payment_group_payment_id,
                cls.deleted_at.is_(None),
            )
            .values(
                deleted_at=cls.current_timestamp(),
                deleted_by=get_flask_user_id(),
            )
            .execution_options(synchronize_session=False)
        )
        if commit:
            db.session.commit()

    @classmethod
    def hard_delete_by_payment_group_payment(
        cls, payment_group_payment_id, commit: bool = True
    ):
        """Hard delete payment_accounts belonging to payment_group_payment.

        Args:
            payment_group_payment_id: ID of the payment group payment
            commit: whether to commit the transaction
        """
        db.session.execute(
            delete(cls)
            .where(cls.payment_group_payment_id == payment_group_payment_id)
            .execution_options(synchronize_session=False)
        )
        if commit:
            db.session.commit()

    @classmethod
    def pending_payments_by_account(cls, account_id):
        """Return any un-posted payments for the specified account, if any.

        Or payments are posted to payoneer but its in processing state.

        The _query_by_post_status() method returns records if action_status of
        payment_group_payment or payment_group_payment_batch is not complete
        or action_status of payment_group_payment_account is in init/running state.
        """
        query = cls._query_by_post_status(account_id=account_id, is_posted=False)
        return db.session.execute(query).mappings().all()

    @classmethod
    def get_payment_totals_grouped_by_program_id(cls, payment_group_payment_id):
        """Return payment total amounts."""
        query = cls._get_payment_totals_grouped_by_program_id(payment_group_payment_id)
        return db.session.execute(query).mappings().all()

    # ************ #
    #   QUERIES:
    # ************ #

    @staticmethod
    def _query_by_payment_group_payment_id(
        is_pending, payment_group_payment_id, search_term
    ):
        """
        Build a query to get payment_group_payment_accounts that have not been deleted.

        Includes a join to 'account' to be able to order by account_name.

        Args:
            is_pending (bool): whether to get records that are pending payment or not
            payment_group_payment_id (int): id of parent payment_group_payment
            search_term (str): str to search accounts by

        If is_pending is true, get payment_group_payment_accounts that have a
        prior_payment_group_payment_id.
        If is_pending is false, get payment_group_payment_accounts that do NOT have
        a prior_payment_group_payment_id.
        """
        filter_by = [
            literal_column('pa.account_id') == literal_column('a.account_id'),
            literal_column('pa.account_id') == literal_column('ap.account_id'),
            literal_column('pa.current_statement_period_id')
            == literal_column('sp_c.statement_period_id'),
            literal_column('pa.deleted_at').is_(None),
            literal_column('pa.payment_group_payment_id') == payment_group_payment_id,
            (
                literal_column('pa.prior_payment_group_payment_id').isnot(None)
                if is_pending
                else literal_column('pa.prior_payment_group_payment_id').is_(None)
            ),
        ]
        if search_term:
            search_term_text = (
                str(search_term).replace('\\', '\\\\').replace('%', '\\%')
            )
            filter_by.append(
                or_(
                    literal_column('a.account_id').ilike(f'%{search_term_text}%'),
                    literal_column('a.account_name').ilike(f'%{search_term_text}%'),
                )
            )
        return (
            select(
                literal_column('pa.account_id').label('account_id'),
                literal_column('a.account_name').label('account_name'),
                literal_column('ap.account_payee_id').label('account_payee_id'),
                literal_column('pa.balance_after_tax').label('balance_after_tax'),
                literal_column('pa.contracts_payable').label('contracts_payable'),
                literal_column('pa.currency_code').label('currency_code'),
                literal_column('current_balance').label('current_balance'),
                literal_column('pa.last_payment').label('last_payment'),
                literal_column('pa.note').label('note'),
                literal_column('ap.payoneer_payee_id').label('payoneer_payee_id'),
                literal_column('pa.payoneer_program_id').label('payoneer_program_id'),
                literal_column('pa.tax_withholding').label('tax_withholding'),
                literal_column('pa.vat_amount').label('vat_amount'),
                literal_column(
                    """
                CASE
                    WHEN pa.prior_payment_group_payment_id IS NULL
                        AND pa.last_payment > 0
                    THEN pa.balance_after_tax - pa.last_payment
                END
            """
                ).label('payment_difference'),
                literal_column(
                    """
                (CASE
                    WHEN pa.prior_payment_group_payment_id IS NULL
                        AND pa.last_payment > 0
                    THEN pa.balance_after_tax - pa.last_payment
                END / pa.last_payment) * 100
            """
                ).label('percent_difference'),
                literal_column('pa.payment_group_payment_account_id').label(
                    'payment_group_payment_account_id'
                ),
                literal_column('pa.payment_group_payment_id').label(
                    'payment_group_payment_id'
                ),
                literal_column('pa.prior_payment_group_payment_id').label(
                    'prior_payment_group_payment_id'
                ),
                literal_column('pa.current_statement_period_id').label(
                    'current_statement_period_id'
                ),
                literal_column('sp_c.statement_period_name').label(
                    'current_statement_period_name'
                ),
                literal_column('pa.last_statement_period_id').label(
                    'last_statement_period_id'
                ),
                literal_column('sp_l.statement_period_name').label(
                    'last_statement_period_name'
                ),
                literal_column(
                    """
                CASE
                    WHEN st_pgpb.action_status = 'error' THEN 'Batch Failure'
                    WHEN st_pgpb.action_status = 'complete'
                        AND st_pgpba.action_status in ('init', 'running')
                        THEN 'Pending'
                    WHEN st_pgpb.action_status = 'complete'
                        AND st_pgpba.action_status = 'rejected' THEN 'Canceled'
                    WHEN st_pgpb.action_status = 'complete'
                        AND st_pgpba.action_status = 'error' THEN 'Failed'
                    WHEN st_pgpb.action_status = 'complete'
                        AND st_pgpba.action_status = 'complete' THEN 'Successful'
                END
            """
                ).label('payment_status'),
                literal_column(
                    """
                CASE
                    WHEN st_pgpb.action_status = 'error'
                        THEN CONCAT('Batch ', LPAD(pgpb.batch_num, 3, 0), ' Has Failed')
                    WHEN st_pgpb.action_status = 'complete'
                        AND st_pgpba.action_status in ('error', 'rejected')
                        THEN st_pgpba.message
                END
            """  # noqa: E501
                ).label('payment_error_code'),
            )
            .where(and_(*filter_by))
            .select_from(
                outerjoin(
                    table('payment_group_payment_account').alias('pa'),
                    table('statement_period').alias('sp_l'),
                    text('pa.last_statement_period_id=sp_l.statement_period_id'),
                )
                .outerjoin(
                    table('payment_group_payment_batch_account').alias('pgpba'),
                    text(
                        'pa.payment_group_payment_account_id=pgpba.payment_group_payment_account_id'
                    ),  # noqa: E501
                )
                .outerjoin(
                    table('payment_group_payment_batch').alias('pgpb'),
                    text(
                        'pgpb.payment_group_payment_batch_id=pgpba.payment_group_payment_batch_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"'),
                    ),
                )
            )
            .select_from(table('statement_period').alias('sp_c'))
            .select_from(table('account').alias('a'))
            .select_from(table('account_payee').alias('ap'))
        )

    @staticmethod
    def _query_by_post_status(account_id, is_posted):
        """Build query to get an account's payments.

        The query returns the payments accounts based on action_status of
        payment_group_payment, payment_group_payment_batch and
        payment_group_payment_account.
        """
        if is_posted:
            is_posted_payoneer = and_(
                text('st_pgpba.action_status="complete"'),
                text('st_pgpb.action_status="complete"'),
                text('st_pgp.action_status="complete"'),
                text('pgpa.payoneer_program_id<>0'),
            )
            is_posted_knr = and_(
                text('st_pgpb.action_status="complete"'),
                text('st_pgp.action_status="complete"'),
                text('pgpa.payoneer_program_id=0'),
            )
            is_posted_condition = or_(is_posted_payoneer, is_posted_knr)
        else:
            is_posted_condition = or_(
                text('st_pgp.action_status<>"complete"'),
                text('st_pgpb.action_status<>"complete"'),
                text('st_pgpba.action_status IN ("init", "running")'),
            )

        return (
            select(literal_column('pgpa.*'))
            .where(
                and_(
                    literal_column('pgpa.payment_group_payment_id')
                    == literal_column('pgp.payment_group_payment_id'),
                    literal_column('pgp.deleted_at').is_(None),
                    literal_column('pgpa.deleted_at').is_(None),
                    literal_column('pgpa.prior_payment_group_payment_id').is_(None),
                    literal_column('pgpa.account_id') == account_id,
                    is_posted_condition,
                )
            )
            .order_by(literal_column('pgp.created_at').desc())
            .select_from(
                outerjoin(
                    table('payment_group_payment').alias('pgp'),
                    table('payment_group_payment_account').alias('pgpa'),
                    text('pgp.payment_group_payment_id=pgpa.payment_group_payment_id'),  # noqa: E501
                )
                .outerjoin(
                    table('payment_group_payment_batch_account').alias('pgpba'),
                    text(
                        'pgpa.payment_group_payment_account_id=pgpba.payment_group_payment_account_id'
                    ),  # noqa: E501
                )
                .outerjoin(
                    table('payment_group_payment_batch').alias('pgpb'),
                    text(
                        'pgpb.payment_group_payment_batch_id=pgpba.payment_group_payment_batch_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"'),
                    ),
                )
                .outerjoin(
                    table('abacus_state').alias('st_pgp'),
                    and_(
                        text('st_pgp.parent_table_id=pgp.payment_group_payment_id'),  # noqa: E501
                        text('st_pgp.parent_table_name="payment_group_payment"'),
                        text('st_pgp.action_name="send_payments"'),
                    ),
                )
            )
        )

    @staticmethod
    def _get_payment_totals_grouped_by_program_id(payment_group_payment_id):
        """
        Build a query to get payment amounts grouped by payoneer program id.

        Args:
            payment_group_payment_id (int): id of parent payment_group_payment
        """
        return (
            select(
                literal_column('pa.currency_code').label('currency_code'),
                literal_column('pa.payoneer_program_id').label('payoneer_program_id'),
                literal_column('rpe.payment_entity_name').label('payment_entity_name'),
                func.count(literal_column('pa.account_id')).label('account_count'),
                func.sum(literal_column('pa.balance_after_tax')).label(
                    'currency_total'
                ),
            )
            .where(
                and_(
                    literal_column('pa.account_id') == literal_column('at.account_id'),
                    literal_column('at.payment_entity_id')
                    == literal_column('rpe.reference_payment_entity_id'),
                    literal_column('pa.deleted_at').is_(None),
                    literal_column('pa.payment_group_payment_id')
                    == payment_group_payment_id,
                    literal_column('pa.prior_payment_group_payment_id').is_(None),
                )
            )
            .select_from(table('payment_group_payment_account').alias('pa'))
            .select_from(table('account_payment_term').alias('at'))
            .select_from(table('reference_payment_entity').alias('rpe'))
            .group_by(
                literal_column('pa.currency_code'),
                literal_column('pa.payoneer_program_id'),
                literal_column('rpe.reference_payment_entity_id'),
            )
            .order_by(
                text('currency_total desc, payment_entity_name, payoneer_program_id')
            )
        )

    @classmethod
    def get_payment_group_payment_account_status_overview(
        cls, payment_group_payment_id: int
    ) -> object:
        """Get the total number of accounts payments failed/rejected/successful for a payment_group_payment.

        Arg:
            payment_group_payment_id(int): id of the payment_group_payment
        Returns:
            A record having fields
                number_of_canceled_payments,
                number_of_failed_payments
                number_of_pending_payments
                number_of_successful_payments
                number_of_payments_failed_with_batch
        """
        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')
                == payment_group_payment_id
            )
            .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).one_or_none()