"""Ledger Reserve Taken model."""

from typing import Tuple

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


class LedgerReserveTaken(BaseModel):
    """Ledger Reserve Taken model."""

    __tablename__ = 'ledger_reserve_taken'
    ledger_reserve_taken_id = db.Column(db.Integer, primary_key=True)
    abacus_event_id = db.Column(db.Integer, nullable=False)
    contract_reserve_id = db.Column(db.Integer, nullable=False)
    accounting_run_id = db.Column(db.Integer, nullable=False)
    statement_period_id = db.Column(db.Integer, nullable=False)
    currency_code = db.Column(db.String(3), nullable=False)
    gross_sales = db.Column(db.Numeric(20, 2), nullable=False)
    gross_returns = db.Column(db.Numeric(20, 2), nullable=False)
    net_revenue = db.Column(db.Numeric(20, 2), nullable=False)
    reserve_amount = db.Column(db.Numeric(20, 2), nullable=False)
    net_revenue_after_reserve = db.Column(db.Numeric(20, 2), nullable=False)

    scheduled_reserves = db.relationship(
        'LedgerReserveReleaseSchedule',
        backref='ledger_reserve_taken',
        cascade='all, delete-orphan',
    )

    @classmethod
    def get_by_abacus_events_accounting_runs_contract_reserves(
        cls,
        abacus_event_ids: list,
        accounting_run_ids: list,
        contract_reserve_ids: list,
    ) -> list:
        """Get ledger_reserve_taken entries by accounting runs, events, and contract reserves.

        Args:
            abacus_event_ids (list): list of abacus_event_ids
            accounting_run_ids (list): list of accounting_run_ids
            contract_reserve_ids (list): list of contract_reserve_ids
        """
        return cls.query.filter(
            cls.abacus_event_id.in_(abacus_event_ids),
            cls.accounting_run_id.in_(accounting_run_ids),
            cls.contract_reserve_id.in_(contract_reserve_ids),
        ).all()

    @classmethod
    def get_by_accounting_run(
        cls, accounting_run_id: int, limit: int, offset: int
    ) -> Tuple[list, int]:
        """Get a list of ledger_reserve_taken by accounting_run_id.

        Args:
            accounting_run_id (int): id of an accounting run
            limit (int): the size of the page
            offset (int): the page number

        Returns:
            a tuple of result items and total count
        """
        query = (
            select(
                [
                    literal_column('lrt.*'),
                    literal_column('ac.account_id'),
                    literal_column('ac.contract_id'),
                ]
            )
            .where(
                and_(
                    literal_column('lrt.contract_reserve_id')
                    == literal_column('cr.contract_reserve_id'),
                    literal_column('cr.contract_id')
                    == literal_column('ac.contract_id'),
                    text('lrt.accounting_run_id = :accounting_run_id'),
                )
            )
            .select_from(table('ledger_reserve_taken').alias('lrt'))
            .select_from(table('account_contract').alias('ac'))
            .select_from(table('contract_reserve').alias('cr'))
        )

        items = db.session.execute(
            query.limit(limit).offset(offset), {'accounting_run_id': accounting_run_id}
        ).fetchall()

        total_count = db.session.execute(
            query, {'accounting_run_id': accounting_run_id}
        ).rowcount

        return items, total_count