"""Combined advances model."""

from sqlalchemy import Column
from sqlalchemy import Integer
from sqlalchemy import Numeric
from sqlalchemy import String
from sqlalchemy.engine.row import Row

from moneyhub.models.snowflake_base import BaseModel


class CombinedAdvances(BaseModel):
    """Combined advances model."""

    __tablename__ = 'combined_advances_dbt'

    account_id = Column(Integer, nullable=False)
    contract_id = Column(Integer, nullable=False)
    statement_period_id = Column(Integer, nullable=False)
    advance_amount = Column(Numeric(32, 2), nullable=False)
    advance_currency_code = Column(String(12), nullable=False)
    advance_amount_payee_currency = Column(Numeric(32, 2), nullable=False)
    advance_payee_currency_code = Column(String(12), nullable=False)
    advance_description = Column(String(500), nullable=True)
    unique_key = Column(String(500), nullable=False, primary_key=True)

    @classmethod
    def get_by_account_and_statement_periods(
        cls, account_id: int, statement_period_ids: list, contract_id: int | None
    ) -> list:
        """Get advances by account and 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:
            list:
        """
        filters = [
            (cls.account_id == account_id),
            (cls.statement_period_id.in_(statement_period_ids))
        ]
        if contract_id:
            filters.append(cls.contract_id == contract_id)

        return cls.query.filter(*filters).all()

    @classmethod
    def get_account_advance_activity(cls, account_id: int) -> Row:
        """Get the first row associated with a given account.

        Args:
            account_id (int): Account ID to filter by
        Returns:
            Row: SqlAlchemy row
        """
        return cls.query.filter(cls.account_id == account_id) \
            .first()