"""Combined adjustments model."""

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

from moneyhub.models.snowflake_base import BaseModel


class CombinedAdjustments(BaseModel):
    """CombinedAdjustments model."""

    __tablename__ = 'combined_adjustments_dbt'

    account_id = Column(Integer, nullable=False)
    activity_statement_period_id = Column(Integer, nullable=False)
    adjustment_amount_payee_currency = Column(Numeric(32, 2), nullable=False)
    adjustment_payee_currency_code = Column(String(3), nullable=False)
    apply_to_statement_period_id = Column(Integer, nullable=False)
    contract_id = Column(Integer, nullable=True)
    note = Column(Text, nullable=True)
    reference_adjustment_type_id = Column(Integer, nullable=False)
    reference_adjustment_type_name = Column(String(500), nullable=False)
    worksheet_adjustment_id = Column(Integer, nullable=True, primary_key=True)
    ledger_adjustment_applied_id = Column(Integer, nullable=False, primary_key=True)
    unique_identifier = Column(String(100), nullable=False)

    @classmethod
    def get_by_account_id(
            cls,
            account_id: int,
            contract_id: int | None,
            statement_period_id_start: int | None,
            statement_period_id_end: int | None,
            adjustment_type_id: int | None) -> list:
        """Get worksheet adjustments by an account_id.

        Args:
            account_id (int): The id of an account
            contract_id (int): Optional id of the contract
            statement_period_id_start (int): Optional id of the statement period to range from
            statement_period_id_end (int): Optional id of the statement period to range to
            adjustment_type_id (int): Optional id of adjustment type to filter on
        Returns:
            list: list of ledger adjustments
        """
        filters = [
            cls.account_id == account_id,
        ]
        with_entities = [
            cls.worksheet_adjustment_id,
            cls.account_id,
            cls.activity_statement_period_id,
            cls.apply_to_statement_period_id,
            cls.contract_id,
            cls.note,
            cls.adjustment_amount_payee_currency,
            cls.adjustment_payee_currency_code,
            cls.reference_adjustment_type_id,
            cls.reference_adjustment_type_name
        ]

        if contract_id:
            filters.append(cls.contract_id == contract_id)
        if statement_period_id_start and statement_period_id_end:
            filters.append(cls.apply_to_statement_period_id.between(
                statement_period_id_start, statement_period_id_end))
        if adjustment_type_id:
            filters.append(cls.reference_adjustment_type_id == adjustment_type_id)

        return cls.query.with_entities(*with_entities) \
            .distinct() \
            .where(*filters) \
            .order_by(cls.apply_to_statement_period_id.desc()) \
            .all()

    @classmethod
    def get_account_adjustments_activity(cls, account_id: int) -> Row:
        """Get revenue account activity.

        Args:
            account_id (int): Account to get revenue for.
        Returns:
            Row: SqlAlchemy row containing adjustment information.
        """
        return cls.query.filter(cls.account_id == account_id).first()

    @classmethod
    def get_publishing_revenue_activity(cls, account_id: int) -> Row:
        """Check whether an account has publishing revenue activity.

        Args:
            account_id (int): Account to check.
        Returns:
            Row: SqlAlchemy row if publishing revenue exists, else None.
        """
        return cls.query.filter(
            cls.account_id == account_id,
            cls.reference_adjustment_type_id == 2
        ).first()

    @classmethod
    def get_adjustments_types_by_account_id(cls, account_id: int) -> list:
        """Get associated adjustments types for adjustments for a given account.

        Args:
            account_id (int): ID of an account
        Returns:
            list: list of adjustments types
        """
        return cls.query \
            .with_entities(cls.reference_adjustment_type_id,
                           cls.reference_adjustment_type_name.label('type_name')) \
            .filter(cls.account_id == account_id)\
            .order_by(cls.reference_adjustment_type_id.asc())\
            .distinct()\
            .all()