"""Ledger model.

Model for managing ledger entries
"""

from typing import 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 and_, func, select, text
from sqlalchemy.sql import literal_column, table

from ledger.constants import templates
from ledger.constants.constants import DEFAULT_PAGE_LIMIT, DEFAULT_PAGE_OFFSET
from ledger.models.ledger_account_current_balance import LedgerAccountCurrentBalance


class LedgerAccount(BaseModel):
    """Ledger Account model."""

    __tablename__ = 'ledger_account'
    ledger_account_id = db.Column(db.Integer, primary_key=True)
    account_id = db.Column(db.Integer, nullable=False)
    contract_id = db.Column(db.Integer, nullable=True)
    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_current_balance = db.relationship(
        'LedgerAccountCurrentBalance',
        backref='LedgerAccount',
        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 by account_id."""
        return cls.query.filter(cls.account_id == account_id).order_by(
            cls.created_at.desc(), cls.ledger_account_id.desc()
        )

    @classmethod
    def get_ledger_account_balance(cls, account_id):
        """Get account's current balance by account_id."""
        return cls.query.join(
            LedgerAccountCurrentBalance,
            LedgerAccountCurrentBalance.ledger_account_id == cls.ledger_account_id,
        ).filter(cls.account_id == account_id)

    @classmethod
    def get_ledger_account_balances(cls, account_ids):
        """Get account's current balance by account_ids."""
        return cls.query.join(
            LedgerAccountCurrentBalance,
            LedgerAccountCurrentBalance.ledger_account_id == cls.ledger_account_id,
        ).filter(cls.account_id.in_(account_ids))

    @classmethod
    def get_ledger_account_info(
        cls,
        account_id: int,
        limit: int = DEFAULT_PAGE_LIMIT,
        offset: int = DEFAULT_PAGE_OFFSET,
    ) -> Tuple[list, int]:
        """GET ledger information by account id.

        Args:
            account_id (int): ID of the account
            limit (int): pagination limit; defaults to 100
            offset (int): pagination offset; defaults to 0

        Returns:
            a tuple of a page of ledger_items and total_count
        """
        committed_accounting_run_result = db.session.execute(
            templates.GET_ACCOUNT_LEDGER_ACCOUNTING_RUN_COMMIT,
            {'account_id': account_id},
        ).fetchall()

        send_payments_result = db.session.execute(
            templates.GET_ACCOUNT_LEDGER_SEND_PAYMENTS, {'account_id': account_id}
        ).fetchall()

        payments_returned_result = db.session.execute(
            templates.GET_ACCOUNT_LEDGER_PAYMENTS_RETURNED, {'account_id': account_id}
        ).fetchall()

        reserves_released_result = db.session.execute(
            templates.GET_ACCOUNT_LEDGER_RESERVE_RELEASED, {'account_id': account_id}
        ).fetchall()

        reserves_taken_result = db.session.execute(
            templates.GET_ACCOUNT_LEDGER_RESERVE_TAKEN, {'account_id': account_id}
        ).fetchall()

        applied_adjustments_result = db.session.execute(
            templates.GET_ACCOUNT_LEDGER_ADJUSTMENTS_APPLIED, {'account_id': account_id}
        ).fetchall()

        contract_advance_payment_result = db.session.execute(
            templates.GET_ACCOUNT_LEDGER_CONTRACT_ADVANCE_APPLIED,
            {'account_id': account_id},
        ).fetchall()

        apply_royalty_correction_result = db.session.execute(
            templates.GET_ACCOUNT_APPLY_ROYALTY_CORRECTIONS,
            {'account_id': account_id, 'event_name': 'apply_royalty_correction'},
        ).fetchall()

        apply_royalty_reversal_result = db.session.execute(
            templates.GET_ACCOUNT_APPLY_ROYALTY_CORRECTIONS,
            {'account_id': account_id, 'event_name': 'apply_royalty_reversal'},
        ).fetchall()

        return_advance_payment_result = db.session.execute(
            templates.GET_ACCOUNT_LEDGER_RETURN_ADVANCE_PAYMENT,
            {'account_id': account_id},
        ).fetchall()

        commit_batch_payment_result = db.session.execute(
            templates.GET_ACCOUNT_LEDGER_COMMIT_BATCH_PAYMENT,
            {'account_id': account_id},
        ).fetchall()

        commit_vat_summary_result = db.session.execute(
            templates.GET_ACCOUNT_LEDGER_COMMIT_VAT_SUMMARY, {'account_id': account_id}
        ).fetchall()

        commit_withholding_tax_result = db.session.execute(
            templates.GET_ACCOUNT_LEDGER_COMMIT_WITHHOLDING_TAX,
            {'account_id': account_id},
        ).fetchall()

        return_batch_payment_result = db.session.execute(
            templates.GET_ACCOUNT_LEDGER_RETURN_BATCH_PAYMENT,
            {'account_id': account_id},
        ).fetchall()

        return_vat_summary_result = db.session.execute(
            templates.GET_ACCOUNT_LEDGER_RETURN_VAT_SUMMARY, {'account_id': account_id}
        ).fetchall()

        return_withholding_tax_result = db.session.execute(
            templates.GET_ACCOUNT_LEDGER_RETURN_WITHHOLDING_TAX,
            {'account_id': account_id},
        ).fetchall()

        final_result = (
            committed_accounting_run_result
            + send_payments_result
            + payments_returned_result
            + reserves_taken_result
            + reserves_released_result
            + applied_adjustments_result
            + contract_advance_payment_result
            + apply_royalty_correction_result
            + apply_royalty_reversal_result
            + return_advance_payment_result
            + commit_batch_payment_result
            + commit_vat_summary_result
            + commit_withholding_tax_result
            + return_batch_payment_result
            + return_vat_summary_result
            + return_withholding_tax_result
        )

        sorted_result = sorted(
            final_result, key=lambda result: result['ledger_account_id'], reverse=True
        )

        max_index = offset + limit
        return sorted_result[offset:max_index], len(sorted_result)

    @classmethod
    def get_by_custom_filters(
        cls, account_ids=None, balance_min=None, balance_max=None
    ):
        """Get ledger information by custom filters."""
        query = cls.query.join(
            LedgerAccountCurrentBalance,
            cls.ledger_account_id == LedgerAccountCurrentBalance.ledger_account_id,
        )

        if account_ids:
            query = query.filter(cls.account_id.in_(account_ids))
        if balance_min is not None:
            query = query.filter(cls.current_balance >= balance_min)
        if balance_max is not None:
            query = query.filter(cls.current_balance <= balance_max)
        return query

    # TO BE DEPRECATED
    # backs endpoint/logic used in lambda-abacus-accounting-period-close
    # (dczinsky)
    @classmethod
    def get_accounts_by_acc_period_id(cls, limit, offset, accounting_period_id):
        """Get ledger accounts by accounting period id."""
        get_ledger_accounts = (
            select(
                [
                    literal_column('la.ledger_account_id').label('ledger_account_id'),
                    literal_column('la.account_id').label('account_id'),
                    literal_column('la.abacus_event_id').label('abacus_event_id'),
                    literal_column('la.contract_id').label('contract_id'),
                    literal_column('la.currency_code').label('currency_code'),
                    literal_column('la.currency_amount').label('currency_amount'),
                ]
            )
            .where(
                and_(
                    literal_column('ae.abacus_event_id')
                    == literal_column('la.abacus_event_id'),
                    literal_column('ar.accounting_run_id')
                    == literal_column('ae.target_id'),
                    literal_column('ae.target_type') == 'accounting_run',
                    text('ar.accounting_period_id = :accounting_period_id'),
                )
            )
            .select_from(table('ledger_account').alias('la'))
            .select_from(table('abacus_event').alias('ae'))
            .select_from(table('accounting_run').alias('ar'))
            .offset(offset)
            .limit(limit)
        )

        return db.session.execute(
            get_ledger_accounts, {'accounting_period_id': accounting_period_id}
        ).fetchall()

    # TO BE DEPRECATED
    # backs endpoint/logic used in lambda-abacus-accounting-period-close
    # (dczinsky)
    @classmethod
    def get_total_accounts_count_by_acc_period_id(cls, accounting_period_id):
        """Get total ledger accounts count by accounting period id."""
        get_accounts_count = (
            select([func.count()])
            .where(
                and_(
                    literal_column('ae.abacus_event_id')
                    == literal_column('la.abacus_event_id'),
                    literal_column('ar.accounting_run_id')
                    == literal_column('ae.target_id'),
                    literal_column('ae.target_type') == 'accounting_run',
                    text('ar.accounting_period_id = :accounting_period_id'),
                )
            )
            .select_from(table('ledger_account').alias('la'))
            .select_from(table('abacus_event').alias('ae'))
            .select_from(table('accounting_run').alias('ar'))
        )

        return db.session.execute(
            get_accounts_count, {'accounting_period_id': accounting_period_id}
        ).scalar()