"""Ledger reserve release schedule model."""

from decimal import Decimal

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

from moneyhub.models import ContractReserve
from moneyhub.models import LedgerReserveRelease
from moneyhub.models.ledger_reserve_taken import LedgerReserveTaken
from moneyhub.models.mysql_base import BaseModel
from moneyhub.models.statement_period import StatementPeriod


class LedgerReserveReleaseSchedule(BaseModel):
    """Ledger reserve release schedule model."""

    __tablename__ = 'ledger_reserve_release_schedule'
    abacus_event_id = Column(Integer, nullable=False)
    account_id = Column(Integer, nullable=False)
    currency_code = Column(String(3), nullable=False)
    installment_amount = Column(Numeric(20, 2), nullable=False)
    ledger_reserve_release_schedule_id = Column(Integer, primary_key=True)
    ledger_reserve_taken_id = Column(
        Integer,
        nullable=False
    )
    outstanding_amount_after_installment = Column(Numeric(20, 2), nullable=False)
    release_statement_period_id = Column(Integer, nullable=False)
    taken_statement_period_id = Column(Integer, nullable=False)
    early_release_date = Column(DateTime, nullable=True)

    @classmethod
    def get_by_account_id_for_statement_period(
            cls, account_id: int, statement_period_id: int) -> list:
        """Get schedule items by account ID before/during a statement period.

        Args:
            account_id (int): The id of an account
            statement_period_id (int): List of statement periods to filter by
        Returns:
            list: list of reserve release schedule details
        """
        sp1 = aliased(StatementPeriod)
        sp2 = aliased(StatementPeriod)

        filters = [
            cls.account_id == account_id,
            cls.taken_statement_period_id <= statement_period_id,
        ]

        with_entities = [
            cls.abacus_event_id,
            cls.account_id,
            cls.currency_code,
            cls.installment_amount,
            cls.ledger_reserve_release_schedule_id,
            cls.ledger_reserve_taken_id,
            cls.outstanding_amount_after_installment,
            cls.release_statement_period_id,
            cls.taken_statement_period_id,
            cls.early_release_date,
            ContractReserve.contract_id,
            sp1.statement_period_name.label('release_statement_period_name'),
            sp2.statement_period_name.label('taken_statement_period_name'),
            LedgerReserveTaken.reserve_amount.label('taken_amount'),
            func.coalesce(
                LedgerReserveRelease.installment_amount, Decimal(0)).label('released_amount')
        ]

        return cls.query.with_entities(*with_entities)\
            .join(
                LedgerReserveTaken,
                cls.ledger_reserve_taken_id == LedgerReserveTaken.ledger_reserve_taken_id) \
            .join(
                ContractReserve,
                ContractReserve.contract_reserve_id == LedgerReserveTaken.contract_reserve_id) \
            .join(
                sp1,
                cls.release_statement_period_id == sp1.statement_period_id) \
            .join(
                sp2,
                cls.taken_statement_period_id == sp2.statement_period_id) \
            .outerjoin(
                LedgerReserveRelease,
                cls.ledger_reserve_release_schedule_id
                == LedgerReserveRelease.ledger_reserve_release_schedule_id) \
            .filter(*filters) \
            .order_by(cls.ledger_reserve_release_schedule_id).all()

    @classmethod
    def get_account_reserves_activity(cls, account_id: int) -> Row:
        """Get reserves account activity.

        Args:
            account_id (int): Account to get reserves for.
        Returns:
            Row: SqlAlchemy row containing reserves schedule information.
        """
        return cls.query.filter(cls.account_id == account_id).first()