"""contract_term_condition Model."""

from abacus_common_logic.connectors.database import db
from abacus_common_logic.models import BaseModel, NormalizedDateTime


class ContractTermCondition(BaseModel):
    """contract_term_condition Model."""

    __tablename__ = 'contract_term_condition'
    contract_term_condition_id = db.Column(db.Integer, primary_key=True)
    contract_term_condition_name = db.Column(db.String(180), nullable=True)
    contract_term_id = db.Column(
        db.Integer, db.ForeignKey('contract_term.contract_term_id'), nullable=False
    )
    conditions = db.Column(db.JSON, nullable=False, default={})
    term_rate = db.Column(db.Numeric(5, 2), nullable=False)
    commission = db.Column(db.Numeric(5, 2), nullable=False)
    priority = db.Column(db.SmallInteger, nullable=False)
    deleted_at = db.Column(NormalizedDateTime(), nullable=True)
    deleted_by = db.Column(db.String(255), nullable=True)

    @classmethod
    def get_active_term_conditions(cls, contract_term_id):
        """Get active contract term conditions by contract_term_id."""
        return cls.query.filter(
            cls.contract_term_id == contract_term_id,
            cls.deleted_at.is_(None),
            cls.deleted_by.is_(None),
        )

    @classmethod
    def get_active_term_conditions_with_order_by(cls, contract_term_id):
        """Get active contract term conditions with order by.

        The primary key is a stable tiebreaker so equal priorities order
        deterministically and identically to the batch lookup.
        """
        return (
            cls.get_active_term_conditions(contract_term_id)
            .order_by(cls.priority, cls.contract_term_condition_id)
            .all()
        )

    @classmethod
    def get_active_term_conditions_by_term_ids(cls, contract_term_ids: list):
        """Get active contract term conditions for a batch of contract_term_ids.

        Ordered by contract_term_id then priority, with the primary key as a
        stable tiebreaker, so conditions for each term come back in the same
        order as the single-term lookup even when priorities are equal.
        """
        return (
            cls.query.filter(
                cls.contract_term_id.in_(contract_term_ids),
                cls.deleted_at.is_(None),
                cls.deleted_by.is_(None),
            )
            .order_by(
                cls.contract_term_id, cls.priority, cls.contract_term_condition_id
            )
            .all()
        )

    @classmethod
    def get_all_by_id(cls, contract_term_condition_ids: list):
        """Get one or more contract term conditions by contract_term_condition_id."""
        return cls.query.filter(
            cls.contract_term_condition_id.in_(contract_term_condition_ids),
            cls.deleted_at.is_(None),
            cls.deleted_by.is_(None),
        ).all()