"""Ledger contract flowhthrough model."""

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

from moneyhub.connectors.mysql import db
from moneyhub.models.abacus_event import AbacusEvent
from moneyhub.models.contract import Contract
from moneyhub.models.mysql_base import BaseModel


class LedgerContractFlowthrough(BaseModel):
    """Ledger contract flowhthrough model."""

    __tablename__ = 'ledger_contract_flowthrough'

    ledger_contract_flowthrough_id = Column(Integer, primary_key=True)
    abacus_event_id = Column(Integer, ForeignKey(AbacusEvent.abacus_event_id))
    account_id = Column(Integer, nullable=False)
    contract_id = Column(Integer, ForeignKey(Contract.contract_id))
    currency_code = Column(String(3), nullable=True)
    currency_amount = Column(Numeric(20, 2), nullable=True)
    previous_balance = Column(Numeric(20, 2), nullable=True)
    current_balance = Column(Numeric(20, 2), nullable=True)
    note = Column(String(255), nullable=True)
    last_modified_by = Column(String(255), nullable=True)
    last_modified = Column(DateTime, nullable=True)
    created_by = Column(String(255), nullable=True)
    created_at = Column(DateTime, nullable=True)

    @classmethod
    def get_latest_balances_for_contract(
        cls,
        contract_ids: list[int],
        statement_period_ids: list[int]
    ) -> list:
        """Get the current flowthrough for an account/contract.

        Args:
            contract_ids (list[int]): Contracts to get balances for
            statement_period_ids (list(int)): Statement period ids to filter by
        Returns:
            list: Payable balances for each contract
        """
        filters = [
            cls.contract_id.in_(contract_ids),
            AbacusEvent.statement_period_id.in_(statement_period_ids),
        ]

        # Sub-query for getting the ID of latest ledger entry for each statement period
        latest_id_query = select(
            func.max(cls.ledger_contract_flowthrough_id).label('latest_id'),
            AbacusEvent.statement_period_id,
        ) \
            .select_from(cls) \
            .join(
                AbacusEvent,
                AbacusEvent.abacus_event_id == cls.abacus_event_id) \
            .filter(*filters) \
            .group_by(AbacusEvent.statement_period_id) \
            .subquery('latest_id_query')

        # Get the rest of the data based on those ledger entries
        query = select(
            latest_id_query.c.statement_period_id,
            cls.current_balance.label('amount'),
            cls.currency_code,
        ) \
            .select_from(cls, latest_id_query) \
            .filter(cls.ledger_contract_flowthrough_id == latest_id_query.c.latest_id)

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