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

from abacus_common_logic.connectors.database import db
from abacus_common_logic.models.base import BaseModel
from sqlalchemy import and_, select
from sqlalchemy.sql import literal_column, outerjoin, table, text


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

    __tablename__ = 'ledger_reserve_release_schedule'
    ledger_reserve_release_schedule_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)
    taken_statement_period_id = db.Column(db.Integer, nullable=False)
    release_statement_period_id = db.Column(db.Integer, nullable=False)
    ledger_reserve_taken_id = db.Column(
        db.Integer,
        db.ForeignKey('ledger_reserve_taken.ledger_reserve_taken_id'),
        nullable=False,
    )
    currency_code = db.Column(db.String(3), nullable=False)
    installment_amount = db.Column(db.Numeric(20, 2), nullable=False)
    outstanding_amount_after_installment = db.Column(db.Numeric(20, 2), nullable=False)
    early_release_date = db.Column(db.DateTime, nullable=True)

    released_reserves = db.relationship(
        'LedgerReserveRelease', backref='ledger_reserve_release_schedule', uselist=False
    )

    @classmethod
    def get_by_release_statement_period_id(cls, release_statement_period_id):
        """Get ledger_reserve_release_schedule items by release statement_period_id."""
        query = (
            select([literal_column('sch.*'), literal_column('cr.contract_id')])
            .where(
                and_(
                    literal_column('sch.ledger_reserve_taken_id')
                    == literal_column('rt.ledger_reserve_taken_id'),
                    literal_column('rt.contract_reserve_id')
                    == literal_column('cr.contract_reserve_id'),
                    literal_column('sch.release_statement_period_id')
                    == release_statement_period_id,
                    literal_column('lr.ledger_reserve_release_id').is_(None),
                )
            )
            .order_by(literal_column('sch.ledger_reserve_release_schedule_id'))
            .select_from(
                outerjoin(
                    table('ledger_reserve_release_schedule').alias('sch'),
                    table('ledger_reserve_release').alias('lr'),
                    text(
                        'sch.ledger_reserve_release_schedule_id=lr.ledger_reserve_release_schedule_id'
                    ),
                )
            )
            .select_from(table('ledger_reserve_taken').alias('rt'))
            .select_from(table('contract_reserve').alias('cr'))
        )
        return db.session.execute(query).fetchall()

    @classmethod
    def get_by_ledger_reserve_taken_ids(cls, ledger_reserve_taken_ids: list) -> list:
        """Get ledger_reserve_release_schedule records by ledger_reserve_taken_id's.

        Args:
            ledger_reserve_taken_ids (list): a list of ledger_reserve_taken_id's
        Returns:
            A list of ledger_reserve_release_schedule records
        """
        return cls.query.filter(
            cls.ledger_reserve_taken_id.in_(ledger_reserve_taken_ids)
        ).all()