"""historical_contract_advance Model."""

from abacus_common_logic.connectors.database import db
from abacus_common_logic.models import NormalizedDate
from abacus_common_logic.models.base import CRUDMixin
from sqlalchemy import case


class HistoricalContractAdvance(db.Model, CRUDMixin):
    """historical_contract_advance Model."""

    __tablename__ = 'historical_contract_advance'

    id = db.Column(db.Integer, primary_key=True, comment='Primary key.')
    oa_advance_id = db.Column(
        db.Integer,
        nullable=False,
        comment='Historical Orchard Admin advance payment ID.',
    )
    oa_contract_id = db.Column(
        db.Integer,
        nullable=False,
        comment='Orchard Admin contract id this advance is associated with.',
    )
    account_id = db.Column(
        db.Integer,
        nullable=False,
        comment='Abacus and Orchard Admin account id this advance is associated with.',
        index=True,
    )
    amount = db.Column(db.Float, nullable=False, comment='Amount of advance.')
    currency_id = db.Column(
        db.SmallInteger,
        nullable=False,
        comment='Foreign key to currencies table.',
    )
    currency = db.Column(
        db.String(3),
        nullable=False,
        comment='Currency code for the advance amount.',
    )
    due_type = db.Column(
        db.String(100),
        nullable=False,
        comment='Indicates how the advance is due.',
    )
    date_paid = db.Column(
        NormalizedDate(), nullable=True, comment='Date the advance is paid.'
    )
    exchange_rate = db.Column(
        db.Float,
        nullable=True,
        comment='Exchange Rate in decimal between 0 and 1',
    )
    description = db.Column(
        db.Text, nullable=True, comment='Description of the advance payment.'
    )
    apply_to_period_id = db.Column(
        db.SmallInteger,
        nullable=True,
        comment='The period the advance is applied to.',
    )

    @classmethod
    def get_by_account_ids(cls, account_ids: list[int]) -> list:
        """Get historical contract advances by account IDs.

        Args:
            account_ids(list): list of account identifiers

        Returns:
            list of historical contract advance records sorted by
            date_paid descending (most recent first), with NULL dates last
        """
        if not account_ids:
            return []

        # Use CASE to put NULL values last in descending order
        # NULL values get 1, non-NULL get 0, so NULLs appear last
        null_order = case((cls.date_paid.is_(None), 1), else_=0)

        return (
            cls.query.filter(cls.account_id.in_(account_ids))
            .order_by(null_order, cls.date_paid.desc())
            .all()
        )