"""Ledger accounting run balance 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 sqlalchemy.engine.row import Row
from sqlalchemy.sql import literal_column
from sqlalchemy.sql import table

from moneyhub.connectors.mysql import db
from moneyhub.models.abacus_event import AbacusEvent
from moneyhub.models.account_contract import AccountContract
from moneyhub.models.accounting_period import AccountingPeriod
from moneyhub.models.accounting_run import AccountingRun
from moneyhub.models.mysql_base import BaseModel


class LedgerAccountingRunBalance(BaseModel):
    """Ledger accounting run balance model."""

    __tablename__ = 'ledger_accounting_run_balance'

    ledger_accounting_run_balance_id = Column(Integer, primary_key=True)
    accounting_run_id = Column(Integer, nullable=False)
    abacus_event_id = Column(
        Integer, ForeignKey(AbacusEvent.abacus_event_id), nullable=False)
    contract_id = Column(Integer, nullable=False)
    currency_code = Column(String(3), nullable=False)
    total_gross_revenue_amount = Column(Numeric(20, 2), nullable=True)
    total_net_revenue_amount = Column(Numeric(20, 2), nullable=True)
    mechanical_deduction_total = Column(Numeric(20, 2), nullable=True)
    mechanical_deduction_admin_fee_total = Column(Numeric(20, 2), nullable=True)
    adjusted_net_revenue = Column(Numeric(20, 2), nullable=False)
    distribution_fee = Column(Numeric(20, 2), nullable=True)
    created_at = Column(DateTime, nullable=True)
    created_by = Column(String(255), nullable=True)
    last_modified = Column(DateTime, nullable=True)
    last_modified_by = Column(String(255), nullable=True)

    @classmethod
    def get_for_account(
        cls,
        account_id: int,
        contract_id: int | None = None,
        visible_periods: list[int] | None = None
    ) -> list:
        """Get the balance and VAT information for an accounting run.

        Args:
            account_id (int): Account to get balance for.
            contract_id (int): Optional contract to filter results by.
            visible_periods (list): list of visible statement periods to filter by
        Returns:
            list: List of balances.
        """
        filters = [
            literal_column('account_contract.account_id') == account_id,
            literal_column('accounting_run.run_status') == 'Committed',
        ]

        if visible_periods:
            filters.append(literal_column('statement_period_id').in_(visible_periods))

        if contract_id:
            filters.append(LedgerAccountingRunBalance.contract_id == contract_id)

        with_entities = [
            literal_column('accounting_period.statement_period_id').label('statement_period_id'),
            cls.currency_code,
            cls.total_gross_revenue_amount,
            cls.total_net_revenue_amount,
            cls.distribution_fee,
            cls.mechanical_deduction_total,
            cls.mechanical_deduction_admin_fee_total,
            cls.adjusted_net_revenue
        ]

        return cls.query.with_entities(*with_entities)\
            .join(
                AccountContract,
                cls.contract_id
                == AccountContract.contract_id) \
            .join(
                AccountingRun,
                cls.accounting_run_id
                == AccountingRun.accounting_run_id) \
            .join(
                AccountingPeriod,
                AccountingRun.accounting_period_id
                == AccountingPeriod.accounting_period_id) \
            .filter(*filters) \
            .all()

    @classmethod
    def get_revenue_total_for_account_statement_periods(
            cls, account_id: int, statement_period_ids: list) -> Row:
        """Get the aggregate revenue for a given account and list of statement period ids.

        Args:
            account_id (int): Account to get balance for.
            statement_period_ids (list): List of statement periods to filter by
        Returns:
            Row: SqlAlchemy row containing revenue information.
        """
        filters = [
            literal_column('account_contract.account_id') == account_id,
            literal_column('accounting_run.run_status') == 'Committed',
            literal_column('accounting_period.statement_period_id').in_(statement_period_ids)
        ]

        query = select([
            LedgerAccountingRunBalance.currency_code,
            func.sum(LedgerAccountingRunBalance.total_gross_revenue_amount).label(
                'gross_revenue_payee_currency'),
            func.sum(LedgerAccountingRunBalance.total_net_revenue_amount).label(
                'net_revenue_payee_currency'),
            literal_column('account_contract.account_id').label('account_id')
        ]) \
            .select_from(LedgerAccountingRunBalance) \
            .join(
                table('account_contract'),
                LedgerAccountingRunBalance.contract_id
                == literal_column('account_contract.contract_id')) \
            .join(
                table('accounting_run'),
                LedgerAccountingRunBalance.accounting_run_id
                == literal_column('accounting_run.accounting_run_id')) \
            .join(
                table('accounting_period'),
                literal_column('accounting_run.accounting_period_id')
                == literal_column('accounting_period.accounting_period_id')) \
            .filter(*filters)

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

    @classmethod
    def get_account_revenue_activity(cls, account_id: int) -> Row:
        """Get revenue account activity.

        Args:
            account_id (int): Account to get revenue for.
        Returns:
            Row: SqlAlchemy row containing revenue information.
        """
        query = select(
            LedgerAccountingRunBalance,
            literal_column('account_contract.account_id').label('account_id')
        )\
            .select_from(LedgerAccountingRunBalance)\
            .join(table('account_contract'),
                  literal_column('account_contract.contract_id')
                  == LedgerAccountingRunBalance.contract_id)\
            .filter(literal_column('account_contract.account_id') == account_id)

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