"""Reference Adjustment Type model."""

from sqlalchemy import Column
from sqlalchemy import Integer
from sqlalchemy import String
from sqlalchemy.sql import literal_column
from sqlalchemy.sql import table

from moneyhub.constants.constants import EXPENSE_ADJUSTMENT_TYPE_ID
from moneyhub.models.mysql_base import BaseModel
from moneyhub.models.worksheet_adjustment import WorksheetAdjustment


class ReferenceAdjustmentType(BaseModel):
    """Reference Adjustment Type model."""

    __tablename__ = 'reference_adjustment_type'
    reference_adjustment_type_id = Column(Integer, primary_key=True)
    type_name = Column(String(255), nullable=False)
    oa_category_name = Column(String(255), nullable=True)

    @classmethod
    def get_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(ReferenceAdjustmentType) \
            .join(WorksheetAdjustment,
                  WorksheetAdjustment.reference_adjustment_type_id
                  == ReferenceAdjustmentType.reference_adjustment_type_id) \
            .filter(
                WorksheetAdjustment.account_id == account_id,
                WorksheetAdjustment.reference_adjustment_type_id != EXPENSE_ADJUSTMENT_TYPE_ID) \
            .order_by(ReferenceAdjustmentType.reference_adjustment_type_id.asc()).all()

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

        Args:
            account_id (int): ID of an account
        Returns:
            list: list of expenses types
        """
        return cls.query \
            .join(table('worksheet_adjustment_detail').alias('lad'),
                  literal_column('lad.reference_adjustment_type_id')
                  == ReferenceAdjustmentType.reference_adjustment_type_id) \
            .filter(literal_column('lad.account_id') == account_id) \
            .order_by(ReferenceAdjustmentType.reference_adjustment_type_id.asc()).all()