"""Ledger Contract Advance Applied model."""

from abacus_common_logic.connectors.database import db
from abacus_common_logic.models.base import BaseModel
from sqlalchemy import and_, literal_column, select, table


class LedgerContractAdvanceApplied(BaseModel):
    """Ledger contract advance applied model."""

    __tablename__ = 'ledger_contract_advance_applied'
    ledger_contract_advance_applied_id = db.Column(db.Integer, primary_key=True)
    abacus_event_id = db.Column(db.Integer, nullable=False)
    account_id = db.Column(db.Integer, nullable=False)
    contract_id = db.Column(db.Integer, nullable=False)
    contract_advance_id = db.Column(db.Integer, nullable=False)
    statement_period_id = db.Column(db.Integer, nullable=False)
    worksheet_payment_contract_advance_id = db.Column(db.Integer, nullable=False)
    advance_amount = db.Column(db.Numeric(20, 2), nullable=False)
    advance_currency_code = db.Column(db.String(3), nullable=False)
    advance_amount_payee_currency = db.Column(db.Numeric(20, 2), nullable=False)
    advance_payee_currency_code = db.Column(db.String(3), nullable=False)
    vat_amount = db.Column(db.Numeric(20, 2), nullable=True)
    withholding_tax_amount = db.Column(db.Numeric(20, 2), nullable=True)
    amount_after_withholding_and_vat = db.Column(db.Numeric(20, 2), nullable=True)
    vat_amount_payee_currency = db.Column(db.Numeric(20, 2), nullable=True)
    withholding_tax_amount_payee_currency = db.Column(db.Numeric(20, 2), nullable=True)
    amount_after_withholding_and_vat_payee_currency = db.Column(
        db.Numeric(20, 2), nullable=True
    )
    us_source_income_rate = db.Column(db.Numeric(9, 6), nullable=True)
    exchange_rate = db.Column(db.Numeric(30, 19), nullable=True)

    __table_args__ = (
        db.UniqueConstraint('contract_advance_id', name='unique_contract_advance_id'),
    )

    @classmethod
    def get_by_contract_advance_id(cls, contract_advance_id: int):
        """Get ledger_contract_advance_applied by contract_advance_id.

        Args;
            contract_advance_id (int): ID of the parent contract_advance
        """
        return cls.query.filter(cls.contract_advance_id == contract_advance_id).first()

    @classmethod
    def get_by_worksheet_payment_contract_advance_id(
        cls, worksheet_payment_contract_advance_id: int, event_name: str
    ):
        """Get ledger_contract_advance_applied by worksheet_payment_contract_advance_id.

        Args;
            worksheet_payment_contract_advance_id (int): ID of the parent worksheet
        """
        event_id_subquery = (
            select([literal_column('abacus_event.abacus_event_id')])
            .where(
                and_(
                    literal_column('abacus_event.event_name') == event_name,
                    literal_column('abacus_event.target_type')
                    == 'worksheet_payment_contract_advance',
                    literal_column('abacus_event.target_id')
                    == worksheet_payment_contract_advance_id,
                )
            )
            .select_from(table('abacus_event'))
        )

        return cls.query.filter(
            cls.worksheet_payment_contract_advance_id
            == worksheet_payment_contract_advance_id,
            cls.abacus_event_id.in_(event_id_subquery),
        ).first()