"""Ledger adjustment applied model."""

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

from moneyhub.connectors.mysql import db
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 import WorksheetAdjustment


class LedgerAdjustmentApplied(BaseModel):
    """Ledger adjustment applied model."""

    __tablename__ = 'ledger_adjustment_applied'

    ledger_adjustment_applied_id = Column(Integer, primary_key=True)
    ledger_adjustment_id = Column(Integer, nullable=True)
    abacus_event_id = Column(Integer, nullable=False)
    account_id = Column(Integer, nullable=False)
    contract_id = Column(Integer, nullable=True)
    statement_period_id = Column(Integer, nullable=False)
    worksheet_adjustment_id = Column(
        Integer,
        ForeignKey(WorksheetAdjustment.worksheet_adjustment_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_adjustments_by_account_and_statement_periods(
        cls,
        account_id: int,
        contract_id: int | None,
        statement_period_ids: list[int],
    ) -> list:
        """Get a payee's applied ledger adjustments by statement_period_id.

        Args:
            account_id (int): The id of an account
            contract_id (int): Optional id of the contract
            statement_period_ids (list): The ids of the statement periods

        Returns:
            dict: dict of applied ledger adjustments
        """
        filters = [
            LedgerAdjustmentApplied.account_id == account_id,
            LedgerAdjustmentApplied.statement_period_id.in_(statement_period_ids),
            WorksheetAdjustment.reference_adjustment_type_id != EXPENSE_ADJUSTMENT_TYPE_ID
        ]

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

        query = select(
            LedgerAdjustmentApplied.adjustment_amount_payee_currency,
            LedgerAdjustmentApplied.adjustment_payee_currency_code,
            LedgerAdjustmentApplied.statement_period_id,
            WorksheetAdjustment.reference_adjustment_type_id,
            ReferenceAdjustmentType.type_name.label('reference_adjustment_type_name'),
        ) \
            .select_from(LedgerAdjustmentApplied) \
            .join(WorksheetAdjustment) \
            .outerjoin(
                ReferenceAdjustmentType,
                ReferenceAdjustmentType.reference_adjustment_type_id
                == WorksheetAdjustment.reference_adjustment_type_id) \
            .filter(*filters) \
            .order_by(ReferenceAdjustmentType.type_name.asc())

        return db.session.execute(query).fetchall()