"""Worksheet adjustment model."""

from sqlalchemy import Column
from sqlalchemy import Integer
from sqlalchemy import Numeric
from sqlalchemy import String
from sqlalchemy import Text
from sqlalchemy.engine.row import Row
from sqlalchemy.orm import aliased

from moneyhub.constants.constants import EXPENSE_ADJUSTMENT_TYPE_ID
from moneyhub.constants.constants import VAT_ADJUSTMENT_TYPE_ID
from moneyhub.models.mysql_base import BaseModel


class WorksheetAdjustment(BaseModel):
    """Worksheet adjustment model."""

    __tablename__ = 'worksheet_adjustment'
    worksheet_adjustment_id = Column(Integer, primary_key=True)
    statement_period_adjustment_file_id = Column(Integer, nullable=False)
    internal_note = Column(Text, nullable=True)
    created_by = Column(String(180), nullable=True)
    abacus_event_id = Column(Integer, nullable=False)
    account_id = Column(Integer, nullable=False)
    activity_statement_period_id = Column(Integer, nullable=False)
    adjustment_amount = Column(Numeric(20, 2), nullable=False)
    adjustment_currency_code = Column(String(3), nullable=False)
    apply_to_statement_period_id = Column(Integer, nullable=False)
    contract_id = Column(Integer, nullable=True)
    reference_adjustment_type_id = Column(Integer, nullable=False)
    note = Column(Text, nullable=True)

    @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
        """
        # Prevent circular dependencies
        from moneyhub.models.ledger_adjustment_applied import LedgerAdjustmentApplied
        from moneyhub.models.reference_adjustment_type import ReferenceAdjustmentType

        rat = aliased(ReferenceAdjustmentType)

        filters = [
            cls.account_id == account_id,
            cls.reference_adjustment_type_id != EXPENSE_ADJUSTMENT_TYPE_ID
        ]
        with_entities = [
            cls.worksheet_adjustment_id,
            cls.abacus_event_id,
            cls.account_id,
            cls.activity_statement_period_id,
            cls.adjustment_amount,
            cls.adjustment_currency_code,
            cls.apply_to_statement_period_id,
            cls.contract_id,
            cls.note,
            cls.reference_adjustment_type_id,
            LedgerAdjustmentApplied.adjustment_amount_payee_currency,
            LedgerAdjustmentApplied.adjustment_payee_currency_code,
            rat.type_name.label('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) \
            .join(
            LedgerAdjustmentApplied,
            LedgerAdjustmentApplied.worksheet_adjustment_id == cls.worksheet_adjustment_id) \
            .outerjoin(
            rat,
            rat.reference_adjustment_type_id
            == cls.reference_adjustment_type_id) \
            .distinct() \
            .where(*filters) \
            .order_by(cls.worksheet_adjustment_id.desc()) \
            .all()

    @classmethod
    def get_vat_adjustments(cls, statement_period_id: int) -> list:
        """Get all adjustments that are associated with VAT events."""
        filters = [
            cls.reference_adjustment_type_id == VAT_ADJUSTMENT_TYPE_ID,
            cls.apply_to_statement_period_id == statement_period_id,
        ]

        return cls.query \
            .filter(*filters) \
            .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()