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

from sqlalchemy import cast
from sqlalchemy import Column
from sqlalchemy import DateTime
from sqlalchemy import ForeignKey
from sqlalchemy import Integer
from sqlalchemy import Numeric
from sqlalchemy import select
from sqlalchemy import String
from sqlalchemy.orm import relationship

from moneyhub.connectors.mysql import db
from moneyhub.models.contract_advance import ContractAdvance
from moneyhub.models.mysql_base import BaseModel


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

    __tablename__ = 'ledger_contract_advance_applied'
    ledger_contract_advance_applied_id = Column(Integer, primary_key=True)
    abacus_event_id = Column(Integer, nullable=False)
    account_id = Column(Integer, nullable=False)
    contract_id = Column(Integer, nullable=False)
    contract_advance_id = Column(
        Integer,
        ForeignKey(ContractAdvance.contract_advance_id))
    contract_advance = relationship(
        'ContractAdvance', foreign_keys=[contract_advance_id])
    statement_period_id = Column(Integer, nullable=False)
    advance_amount = Column(Numeric(20, 2), nullable=False)
    advance_currency_code = Column(String(3), nullable=False)
    advance_amount_payee_currency = Column(Numeric(20, 2), nullable=False)
    advance_payee_currency_code = Column(String(3), nullable=False)
    created_at = Column(DateTime, nullable=False)

    @classmethod
    def get_by_account_and_statement_periods(
        cls, account_id: int, statement_period_ids: list, contract_id: int | None
    ) -> list:
        """Get a payee's paid advances by statement_period_id.

        Args:
            account_id (int): The id of an account
            statement_period_ids (list): The ids of the statement periods
            contract_id (int): Optional id of the contract
        Returns:
            dict: dict of paid advances
        """
        filters = [
            (LedgerContractAdvanceApplied.account_id == account_id),
            (LedgerContractAdvanceApplied.statement_period_id.in_(statement_period_ids))
        ]
        if contract_id:
            filters.append(LedgerContractAdvanceApplied.contract_id == contract_id)

        query = select(
            LedgerContractAdvanceApplied.ledger_contract_advance_applied_id,
            cast(
                LedgerContractAdvanceApplied.ledger_contract_advance_applied_id.label('advance_id'),
                String),
            LedgerContractAdvanceApplied.abacus_event_id,
            LedgerContractAdvanceApplied.account_id,
            LedgerContractAdvanceApplied.contract_id,
            LedgerContractAdvanceApplied.contract_advance_id,
            LedgerContractAdvanceApplied.statement_period_id,
            LedgerContractAdvanceApplied.advance_amount,
            LedgerContractAdvanceApplied.advance_currency_code,
            LedgerContractAdvanceApplied.advance_amount_payee_currency,
            LedgerContractAdvanceApplied.advance_payee_currency_code,
            LedgerContractAdvanceApplied.created_at,
            ContractAdvance.advance_description
        ) \
            .select_from(LedgerContractAdvanceApplied) \
            .join(
                ContractAdvance,
                LedgerContractAdvanceApplied.contract_advance_id
                == ContractAdvance.contract_advance_id) \
            .filter(*filters)

        return db.session.execute(query).fetchall()