"""Ledger adjustment detail applied model."""

from sqlalchemy import Column
from sqlalchemy import ForeignKey
from sqlalchemy import func
from sqlalchemy import Integer
from sqlalchemy import Numeric
from sqlalchemy import String

from moneyhub.constants.constants import EXPENSE_ADJUSTMENT_TYPE_ID
from moneyhub.models.mysql_base import BaseModel
from moneyhub.models.reference_adjustment_type import ReferenceAdjustmentType
from moneyhub.models.worksheet_adjustment_detail import WorksheetAdjustmentDetail


class LedgerAdjustmentDetailApplied(BaseModel):
    """Ledger adjustment detail applied model."""

    __tablename__ = 'ledger_adjustment_detail_applied'
    ledger_adjustment_detail_applied_id = Column(Integer, primary_key=True)
    ledger_adjustment_detail_id = Column(Integer, nullable=True)
    worksheet_adjustment_detail_id = Column(
        Integer,
        ForeignKey(WorksheetAdjustmentDetail.worksheet_adjustment_detail_id),
        nullable=False
    )
    adjustment_amount = Column(Numeric(20, 18), nullable=False)
    adjustment_currency_code = Column(String(3), nullable=False)
    adjustment_amount_payee_currency = Column(Numeric(20, 18), nullable=False)
    adjustment_payee_currency_code = Column(String(3), nullable=False)

    @classmethod
    def get_expenses_by_account_and_statement_periods(
            cls,
            account_id: int,
            statement_period_ids: list[int],
            contract_id: int | None
    ) -> list:
        """Get a payee's applied expenses by statement periods.

        Args:
            account_id (int): The id of an account
            contract_id (int): Optional id of the contract
            statement_period_ids (int): The id of the statement period
        Returns:
            dict: dict of applied ledger adjustments
        """
        filters = [
            WorksheetAdjustmentDetail.account_id == account_id,
            WorksheetAdjustmentDetail.apply_to_statement_period_id.in_(statement_period_ids),
            WorksheetAdjustmentDetail.reference_adjustment_type_id != EXPENSE_ADJUSTMENT_TYPE_ID
        ]

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

        with_entities = [
            func.sum(cls.adjustment_amount_payee_currency).label(
                'adjustment_amount_payee_currency'),
            cls.adjustment_payee_currency_code,
            WorksheetAdjustmentDetail.apply_to_statement_period_id.label('statement_period_id'),
            ReferenceAdjustmentType.reference_adjustment_type_id,
            ReferenceAdjustmentType.type_name.label('reference_adjustment_type_name'),
        ]

        group_by = [
            cls.adjustment_payee_currency_code,
            WorksheetAdjustmentDetail.apply_to_statement_period_id.label('statement_period_id'),
            ReferenceAdjustmentType.reference_adjustment_type_id,
            ReferenceAdjustmentType.type_name,
        ]

        return cls.query.with_entities(*with_entities) \
            .join(WorksheetAdjustmentDetail, cls.worksheet_adjustment_detail_id
                  == WorksheetAdjustmentDetail.worksheet_adjustment_detail_id) \
            .join(ReferenceAdjustmentType,
                  ReferenceAdjustmentType.reference_adjustment_type_id
                  == WorksheetAdjustmentDetail.reference_adjustment_type_id, isouter=True) \
            .filter(*filters) \
            .group_by(*group_by) \
            .order_by(ReferenceAdjustmentType.type_name.asc()).all()