"""Payment Group Payment Account model."""

from sqlalchemy import and_
from sqlalchemy import Column
from sqlalchemy import DateTime
from sqlalchemy import func
from sqlalchemy import Integer
from sqlalchemy import JSON
from sqlalchemy import literal_column
from sqlalchemy import Numeric
from sqlalchemy import String
from sqlalchemy import table

from moneyhub.models.mysql_base import BaseModel


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

    __tablename__ = 'payment_group_payment_account'

    payment_group_payment_account_id = Column(Integer, nullable=False, primary_key=True)
    payment_group_payment_id = Column(Integer, nullable=True)
    account_id = Column(Integer, nullable=True)
    payoneer_program_id = Column(Integer, nullable=True)
    prior_payment_group_payment_id = Column(Integer, nullable=True)
    current_statement_period_id = Column(Integer, nullable=True)
    last_statement_period_id = Column(Integer, nullable=True)
    currency_code = Column(String(3), nullable=True)
    last_payment = Column(Numeric(20, 2), nullable=True)
    contracts_payable = Column(JSON, nullable=True)
    current_balance = Column(Numeric(20, 2), nullable=True)
    vat_amount = Column(Numeric(20, 2), nullable=True)
    balance_after_tax = Column(Numeric(20, 2), nullable=True)
    created_at = Column(DateTime, nullable=True)
    created_by = Column(String(255), nullable=True)
    last_modified = Column(DateTime, nullable=True)
    last_modified_by = Column(String(255), nullable=True)
    deleted_at = Column(DateTime, nullable=True)
    deleted_by = Column(String(255), nullable=True)

    @classmethod
    def get_payment_details(
            cls,
            account_id: int,
            statement_period_ids: list[int]
    ) -> list:
        """Get aggregated account-level payment details by statement periods."""
        filters = [
            cls.account_id == account_id,
            literal_column('waccb.statement_period_id').in_(statement_period_ids),
            cls.deleted_by.is_(None),
            literal_column('pgpad.deleted_by').is_(None),
            literal_column('wacpat.deleted_by').is_(None),
        ]

        with_entities = [
            cls.payment_group_payment_account_id.label('worksheet_account_contract_closing_balance_id'),  # noqa: E501 #TODO needs to be moved to more generic id variable to support both cases
            literal_column('waccb.statement_period_id').label('closing_balance_statement_period'),  # noqa: E501
            cls.current_balance.label('closing_balance_amount'),
            func.sum(literal_column('pgpad.payable_amount_pre_tax')).label('payable_amount_pre_tax'),   # noqa: E501
            func.sum(literal_column('pgpad.tax_withholding_amount')).label('tax_withholding_amount'),   # noqa: E501
            cls.vat_amount.label('vat_amount'),
            cls.balance_after_tax.label('payable_amount_post_tax'),
            cls.currency_code.label('currency_code'),
            literal_column('pgpba_st.action_status').label('batch_status'),
            literal_column('pgpa_st.action_status').label('individual_payment_status'),
            literal_column('pgpa_st.created_at').label('created_at'),
            literal_column('pgpa_st.last_modified').label('last_modified'),
            cls.current_statement_period_id.label('payment_statement_period_id'),
        ]

        return cls.query \
            .with_entities(*with_entities) \
            .join(
                table('payment_group_payment_account_detail').alias('pgpad'),
                literal_column('pgpad.payment_group_payment_account_id') == cls.payment_group_payment_account_id  # noqa: E501
            ) \
            .join(
                table('payment_group_payment_batch_account').alias('pgpba'),
                literal_column('pgpba.payment_group_payment_account_id') == cls.payment_group_payment_account_id  # noqa: E501
            ) \
            .join(
                table('abacus_state').alias('pgpa_st'),
                and_(
                    literal_column('pgpa_st.parent_table_id') == cls.payment_group_payment_account_id,  # noqa: E501
                    literal_column('pgpa_st.parent_table_name') == literal_column("'payment_group_payment_account'"),  # noqa: E501
                    literal_column('pgpa_st.action_name') == literal_column("'send_payments'")
                )
            ) \
            .join(
                table('abacus_state').alias('pgpba_st'),
                and_(
                    literal_column('pgpba_st.parent_table_id') == literal_column('pgpba.payment_group_payment_batch_id'),  # noqa: E501
                    literal_column('pgpba_st.parent_table_name') == literal_column("'payment_group_payment_batch'"),  # noqa: E501
                    literal_column('pgpba_st.action_name') == literal_column("'send_payment'")  # noqa: E501
                )
            ) \
            .join(
                table('worksheet_account_contract_payable_after_tax').alias('wacpat'),
                literal_column('pgpad.worksheet_account_contract_payable_after_tax_id') == literal_column('wacpat.worksheet_account_contract_payable_after_tax_id')  # noqa: E501
            ) \
            .join(
                table('worksheet_account_contract_closing_balance').alias('waccb'),
                literal_column('wacpat.worksheet_account_contract_closing_balance_id') == literal_column('waccb.worksheet_account_contract_closing_balance_id')  # noqa: E501
            ) \
            .filter(*filters) \
            .group_by(
                cls.payment_group_payment_account_id,
                cls.current_statement_period_id,
                cls.current_balance,
                cls.balance_after_tax,
                cls.currency_code,
                cls.vat_amount,
                literal_column('pgpba_st.action_status'),
                literal_column('pgpa_st.action_status'),
                literal_column('pgpa_st.created_at'),
                literal_column('pgpa_st.last_modified'),
                literal_column('waccb.statement_period_id')
            ).all()