"""Adjustments by type model."""

from sqlalchemy import and_
from sqlalchemy import Column
from sqlalchemy import Integer
from sqlalchemy import Numeric
from sqlalchemy import String

from moneyhub.constants.constants import FLOWTHROUGH_ADJUSTMENT_TYPE_ID
from moneyhub.models.snowflake_base import BaseModel


class AdjustmentsByType(BaseModel):
    """Adjustments by type model."""

    __tablename__ = 'combined_adjustments_by_type_dbt'

    account_id = Column(Integer, nullable=False, primary_key=True)
    adjustment_amount_payee_currency = Column(Numeric(32, 2), nullable=False)
    adjustment_payee_currency_code = Column(String(3), nullable=False)
    contract_id = Column(Integer, nullable=False, primary_key=True)
    statement_period_id = Column(Integer, nullable=False, primary_key=True)
    ledger_adjustment_applied_id = Column(Integer, nullable=False)
    reference_adjustment_type_id = Column(Integer, nullable=False, primary_key=True)
    reference_adjustment_type_name = Column(String(500), nullable=False)
    ledger_adjustment_applied_id = Column(Integer, nullable=False, primary_key=True)

    @classmethod
    def get_by_account_id(
        cls,
        account_id: int,
        contract_id: int | None,
        statement_period_ids: list[int]
    ) -> list:
        """Get adjustments grouped by type.

        Args:
            account_id (int): Account ID to filer by
            contract_id (int): Contract ID to filter by
            statement_period_ids (list): List of statement periods to filter by
        Returns:
            list: list of adjustments by type
        """
        from moneyhub.models import PaymentAllocationLedgerAdjustmentReplica
        filters = [
            cls.account_id == account_id,
            cls.statement_period_id.in_(statement_period_ids),
            ~and_(
                PaymentAllocationLedgerAdjustmentReplica.payment_allocation_id.is_not(None),
                cls.reference_adjustment_type_id == FLOWTHROUGH_ADJUSTMENT_TYPE_ID
            ),
        ]

        if contract_id:
            filters.append(cls.contract_id == contract_id)

        return cls.query \
            .join(
                PaymentAllocationLedgerAdjustmentReplica,
                cls.ledger_adjustment_applied_id == PaymentAllocationLedgerAdjustmentReplica.ledger_adjustment_applied_id, isouter=True  # noqa: E501
            ) \
            .filter(*filters) \
            .order_by(cls.reference_adjustment_type_name.asc()) \
            .all()