"""Ledger account contract model.

Model for managing ledger entries by account and contract
"""

from typing import Any, Iterable, Tuple

from abacus_common_data.currency import get_currency_object_from_code
from abacus_common_logic.connectors.database import db
from abacus_common_logic.models.base import BaseModel
from sqlalchemy import func, text

from ledger.constants import sql_templates
from ledger.models.ledger_account_contract_current_balance import (
    LedgerAccountContractCurrentBalance,
)


class LedgerAccountContract(BaseModel):
    """Ledger Account Contract model."""

    __tablename__ = 'ledger_account_contract'
    ledger_account_contract_id = db.Column(db.Integer, primary_key=True)
    account_id = db.Column(db.Integer, nullable=False)
    contract_id = db.Column(db.Integer, nullable=False)
    abacus_event_id = db.Column(db.Integer, nullable=False)
    currency_code = db.Column(db.String(3), nullable=False)
    currency_amount = db.Column(db.Numeric(20, 2), nullable=False)
    previous_balance = db.Column(db.Numeric(20, 2), nullable=False)
    current_balance = db.Column(db.Numeric(20, 2), nullable=False)
    note = db.Column(db.String(255), nullable=True)

    ledger_account_contract_current_balance = db.relationship(
        'LedgerAccountContractCurrentBalance',
        backref='LedgerAccountContract',
        cascade='all, delete-orphan',
        uselist=False,
    )

    @property
    def currency_name(self):
        """Class property for ledger's currency name."""
        return get_currency_object_from_code(self.currency_code)['currency_name']

    @classmethod
    def get_by_account_id(cls, account_id):
        """Get ledger_account_contract by account_id."""
        return cls.query.filter(cls.account_id == account_id).order_by(
            cls.created_at.desc(), cls.ledger_account_contract_id.desc()
        )

    @classmethod
    def get_by_contract_id(cls, contract_id):
        """Get ledger_account_contract by contract_id."""
        return cls.query.filter(cls.contract_id == contract_id).order_by(
            cls.created_at.desc(), cls.ledger_account_contract_id.desc()
        )

    @classmethod
    def get_ledger_account_contract_balance_by_account(cls, account_id):
        """Get account contract current balances by account_id."""
        return (
            cls.query.join(
                LedgerAccountContractCurrentBalance,
                LedgerAccountContractCurrentBalance.ledger_account_contract_id
                == cls.ledger_account_contract_id,
            )
            .filter(cls.account_id == account_id)
            .order_by(cls.created_at.desc(), cls.ledger_account_contract_id.desc())
        )

    @classmethod
    def get_ledger_account_contract_balance_by_contract(cls, contract_id: int):
        """Get account contracts current balances by contract_ids."""
        return cls.query.join(
            LedgerAccountContractCurrentBalance,
            LedgerAccountContractCurrentBalance.ledger_account_contract_id
            == cls.ledger_account_contract_id,
        ).filter(cls.contract_id == contract_id)

    @classmethod
    def get_ledger_account_contract_balance_by_contracts(
        cls, contract_ids: Iterable[int]
    ):
        """Get account contracts current balances by contract_ids."""
        return cls.query.join(
            LedgerAccountContractCurrentBalance,
            LedgerAccountContractCurrentBalance.ledger_account_contract_id
            == cls.ledger_account_contract_id,
        ).filter(cls.contract_id.in_(contract_ids))

    @classmethod
    def get_ledger_account_contract_balances(
        cls, contract_ids: list, account_ids: list
    ):
        """Get account contracts current balances.

        Args:
            account_ids (int): accounts to filter the results by
            contract_ids (int): contracts to filter the results by
        """
        query = cls.query.join(
            LedgerAccountContractCurrentBalance,
            LedgerAccountContractCurrentBalance.ledger_account_contract_id
            == cls.ledger_account_contract_id,
        )
        if contract_ids:
            query = query.filter(cls.contract_id.in_(contract_ids))
        if account_ids:
            query = query.filter(cls.account_id.in_(account_ids))
        return query

    @classmethod
    def get_payable_balance_by_account(cls, account_id: int):
        """Get an account's 'payable_balance' (the sum of positive contract balances).

        Args:
            account_id (int): account for which to get a payable balance
        """
        return (
            db.session.query(
                cls.account_id,
                cls.currency_code,
                func.sum(cls.current_balance).label('payable_balance'),
            )
            .join(
                LedgerAccountContractCurrentBalance,
                LedgerAccountContractCurrentBalance.ledger_account_contract_id
                == cls.ledger_account_contract_id,
            )
            .filter(cls.account_id == account_id, cls.current_balance > 0)
            .first()
        )

    # TODO: Implement equivalent of LedgerAccount.get_ledger_account_info
    # Until Product reqs exist, continue to use LedgerAccount.get_ledger_account_info
    # instead of defining LedgerAccountContract.get_ledger_account_contract_info

    # TODO: Implement equivalent of LedgerAccount.get_by_custom_filters
    # It looks like this fn is not actually used because it's only
    # reference in ows-ledger is '/bulk/accounts-balance' and this doesn't
    # seem to be called by other services/lambdas/graphql

    @classmethod
    def get_ledger_list_by_account_id(
        cls,
        account_id: int,
        limit: int,
        offset: int,
        event_names: str | None = None,
        statement_period_ids: str | None = None,
        contract_ids: str | None = None,
    ) -> Tuple[list, int]:
        """GET a list of ledgers by an account_id.

        Args:
            account_id (int): id of an account
            limit (int): pagination limit; defaults to 100
            offset (int): pagination offset; defaults to 0
            event_names (str): a string of abacus events, separated by commas
            statement_period_ids (str): a string of statement period IDs, separated by commas
            contract_ids (str): a string of contract IDs, separated by commas
        Returns:
            a tuple of ledger items and total_count
        """
        ledger_query = sql_templates.LEDGER_ACCOUNT_CONTRACT_QUERY

        sort_by = text('ledger_account_contract_id')
        sort_order = 'DESC'
        where_condition_params: dict[str, Any] = {'account_id': account_id}

        filter_by = list()
        if event_names:
            event_name_list = tuple(map(str.strip, event_names.split(',')))
            filter_by.append('transaction_type IN :event_names')
            where_condition_params.update({'event_names': event_name_list})

        if statement_period_ids:
            statement_period_ids_list = tuple(
                map(str.strip, statement_period_ids.split(','))
            )
            filter_by.append('statement_period_id IN :statement_period_ids')
            where_condition_params.update(
                {'statement_period_ids': statement_period_ids_list}
            )

        if contract_ids:
            contract_ids_list = tuple(map(str.strip, contract_ids.split(',')))
            filter_by.append('contract_id IN :contract_ids')
            where_condition_params.update({'contract_ids': contract_ids_list})

        ledger_query = ledger_query.format(
            sort_by=sort_by,
            sort_order=sort_order,
            where_clause=' AND '.join(filter_by) if filter_by else 1,
        )

        sql = ledger_query + 'LIMIT :limit OFFSET :offset'

        items = db.session.execute(
            sql, {'limit': limit, 'offset': offset, **where_condition_params}
        ).fetchall()

        total_count = db.session.execute(
            ledger_query, {**where_condition_params}
        ).rowcount

        return items, total_count