"""Worksheet Account Contract Closing Balance Model."""
from sqlalchemy import and_
from sqlalchemy import Column
from sqlalchemy import Integer
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 WorksheetAccountContractClosingBalance(BaseModel):
    """Worksheet Account Contract Closing Balance Model."""

    __tablename__ = 'worksheet_account_contract_closing_balance'

    worksheet_account_contract_closing_balance_id = Column(Integer, nullable=False, primary_key=True)  # noqa: E501
    account_id = Column(Integer, nullable=False)
    contract_id = Column(Integer, nullable=False)
    amount = Column(Numeric(20, 2), nullable=False)
    statement_period_id = Column(Integer, nullable=False)
    deleted_by = Column(String(255), nullable=True)

    @classmethod
    def get_payment_details(
            cls,
            account_id: int,
            statement_period_ids: list[int],
            contract_id: int | None = None
    ) -> list:
        """Get payment details by statement periods.

        Args:
            account_id (int): The id of an account
            contract_id (int): Optional id of the contract
            statement_period_ids (int): The id of the statement period
        Returns:
            list: list of payments details
        """
        filters = [
            cls.account_id == account_id,
            cls.statement_period_id.in_(statement_period_ids),
            cls.deleted_by.is_(None),
            literal_column('wacpat.deleted_by').is_(None),
            literal_column('pgpad.deleted_by').is_(None),
            literal_column('pgpa.deleted_by').is_(None),
            literal_column('pgpad.payable_amount_post_tax') != 0
        ]

        if contract_id:
            filters.append(cls.contract_id == contract_id)

        with_entities = [
            cls.worksheet_account_contract_closing_balance_id,
            cls.contract_id,
            cls.statement_period_id.label('closing_balance_statement_period'),
            cls.amount.label('closing_balance_amount'),
            literal_column('pgpad.payable_amount_pre_tax').label('payable_amount_pre_tax'),
            literal_column('pgpad.tax_withholding_amount').label('tax_withholding_amount'),
            literal_column('pgpad.vat_amount').label('vat_amount'),
            literal_column('pgpad.payable_amount_post_tax').label('payable_amount_post_tax'),
            literal_column('pgpad.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'),
            literal_column('pgp.statement_period_id').label('payment_statement_period_id'),
        ]

        return cls.query.with_entities(*with_entities) \
            .join(
                table('worksheet_account_contract_payable_after_tax').alias('wacpat'),
                cls.worksheet_account_contract_closing_balance_id
                == literal_column('wacpat.worksheet_account_contract_closing_balance_id')) \
            .join(
                table('payment_group_payment_account_detail').alias('pgpad'),
                literal_column('pgpad.worksheet_account_contract_payable_after_tax_id')
                == literal_column('wacpat.worksheet_account_contract_payable_after_tax_id')) \
            .join(
                table('payment_group_payment_account').alias('pgpa'),
                literal_column('pgpad.payment_group_payment_account_id')
                == literal_column('pgpa.payment_group_payment_account_id')) \
            .join(
                table('payment_group_payment').alias('pgp'),
                literal_column('pgp.payment_group_payment_id')
                == literal_column('pgpa.payment_group_payment_id')) \
            .join(
                table('payment_group_payment_batch_account').alias('pgpba'),
                literal_column('pgpa.payment_group_payment_account_id')
                == literal_column('pgpba.payment_group_payment_account_id')) \
            .join(
                table('abacus_state').alias('pgpa_st'),
                and_(
                    literal_column('pgpa_st.parent_table_id') == literal_column('pgpa.payment_group_payment_account_id'),  # noqa: E501
                    literal_column('pgpa_st.parent_table_name') == 'payment_group_payment_account',
                    literal_column('pgpa_st.action_name') == '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') == 'payment_group_payment_batch',
                    literal_column('pgpba_st.action_name') == 'send_payment'
                )) \
            .filter(*filters).all()