"""LedgerAdjustment model."""

from abacus_common_logic.connectors.database import db
from abacus_common_logic.models.base import BaseModel
from sqlalchemy import case, select
from sqlalchemy.orm import column_property
from sqlalchemy.sql import and_, literal_column, table

from ledger.models.ledger_adjustment_applied import LedgerAdjustmentApplied
from ledger.models.reference_adjustment_type import ReferenceAdjustmentType


class LedgerAdjustment(BaseModel):
    """Ledger Adjustment model."""

    __tablename__ = 'ledger_adjustment'
    ledger_adjustment_id = db.Column(db.Integer, primary_key=True)
    abacus_event_id = db.Column(db.Integer, nullable=False)
    account_id = db.Column(db.Integer, nullable=False)
    contract_id = db.Column(db.Integer, nullable=True)
    activity_statement_period_id = db.Column(db.Integer, nullable=False)
    apply_to_statement_period_id = db.Column(db.Integer, nullable=False)
    reference_adjustment_type_id = db.Column(
        db.Integer,
        db.ForeignKey('reference_adjustment_type.reference_adjustment_type_id'),
        nullable=False,
    )
    adjustment_amount = db.Column(db.Numeric(20, 18), nullable=False)
    adjustment_currency_code = db.Column(db.String(3), nullable=False)
    note = db.Column(db.Text, nullable=True)

    details = db.relationship(
        'LedgerAdjustmentDetail',
        secondary='ledger_adjustment_adjustment_detail',
        primaryjoin='LedgerAdjustment.ledger_adjustment_id '
        '== LedgerAdjustmentAdjustmentDetail'
        '.ledger_adjustment_id',
        secondaryjoin='LedgerAdjustmentDetail'
        '.ledger_adjustment_detail_id '
        '== LedgerAdjustmentAdjustmentDetail'
        '.ledger_adjustment_detail_id',
        lazy='joined',
    )
    account_currency_code = column_property(
        select([literal_column('account_payment_term.currency_code')])
        .select_from(table('account_payment_term'))
        .where(literal_column('account_payment_term.account_id') == account_id)
    )
    adjustment_type = column_property(
        select([ReferenceAdjustmentType.type_name])
        .select_from(ReferenceAdjustmentType)
        .where(
            ReferenceAdjustmentType.reference_adjustment_type_id
            == reference_adjustment_type_id
        )
    )

    @property
    def adjustment_ledger_status(self):
        """Property to get ledger status."""
        return 'Applied' if self.ledger_adjustment_applied else 'Pending'

    @classmethod
    def get_pending_by_statement_period_id(
        cls, statement_period_id: int, limit: int, offset: int
    ):
        """Get list of pending ledger adjustments applied to statement period id.

        Args:
            statement_period_id (int): ID of statement period
            limit (int): pagination limit
            offset (int): pagination offset

        Returns:
            A tuple of result items and total count.
        """
        query = LedgerAdjustment.query.join(
            LedgerAdjustmentApplied,
            LedgerAdjustment.ledger_adjustment_id
            == LedgerAdjustmentApplied.ledger_adjustment_id,
            isouter=True,
        ).filter(
            and_(
                LedgerAdjustmentApplied.ledger_adjustment_applied_id.is_(None),
                LedgerAdjustment.apply_to_statement_period_id == statement_period_id,
            )
        )

        items = query.limit(limit).offset(offset).all()
        total_count = query.count()

        return items, total_count

    @staticmethod
    def _query_to_get_all_ledger_adjustments():
        """Build a query to get ledger_adjustments.

        The query returns all pending and applied adjustments.
        """
        query = LedgerAdjustment.query.outerjoin(
            LedgerAdjustmentApplied,
            LedgerAdjustmentApplied.ledger_adjustment_id
            == LedgerAdjustment.ledger_adjustment_id,
        ).order_by(LedgerAdjustment.ledger_adjustment_id.desc())

        return query

    @classmethod
    def get_ledger_adjustments(
        cls,
        limit: int,
        offset: int,
        account_id: int = None,
        start_apply_to_statement_period_id: int = None,
        end_apply_to_statement_period_id: int = None,
        show_only_applied_adjustments: bool = None,
    ) -> tuple:
        """Get a list of all ledger_adjustments.

        Args:
            limit (int): pagination limit
            offset (int): pagination offset
            account_id(int): id of an account id
            start_apply_to_statement_period_id(int): id of apply_to_statement period_id
                filtering ledger_adjustments list from
                start_apply_to_statement_period_id
            end_apply_to_statement_period_id(int): id of apply_to_statement period_id
                filters ledger_adjustments list up to
                end_apply_to_statement_period_id.
            show_only_applied_adjustments(bool): Either 1 or 0
        Returns:
            A tuple containing items and total count
        """
        query = cls._query_to_get_all_ledger_adjustments()

        if account_id:
            query = query.filter(and_(cls.account_id.like(f'%{account_id}%')))

        if show_only_applied_adjustments:
            adjustment_ledger_status = case(
                [
                    (
                        LedgerAdjustmentApplied.ledger_adjustment_applied_id.isnot(
                            None
                        ),
                        'Applied',
                    )
                ],
                else_='Pending',
            )
            query = query.filter(and_(adjustment_ledger_status == 'Applied'))

        if start_apply_to_statement_period_id:
            query = query.filter(
                and_(
                    cls.apply_to_statement_period_id
                    >= start_apply_to_statement_period_id
                )
            )

        if end_apply_to_statement_period_id:
            query = query.filter(
                and_(
                    cls.apply_to_statement_period_id <= end_apply_to_statement_period_id
                )
            )

        items = query.limit(limit).offset(offset).all()
        total_count = query.count()

        return items, total_count