"""Ledger summary model."""

from sqlalchemy import asc
from sqlalchemy import Column
from sqlalchemy import desc
from sqlalchemy import func
from sqlalchemy import Integer
from sqlalchemy import Numeric
from sqlalchemy import String

from moneyhub.constants.constants import OrderDirection
from moneyhub.models.snowflake_base import BaseModel


class LedgerSummary(BaseModel):
    """Ledger entries aggregated by account/contract/period."""

    __tablename__ = 'ledger_summary_dbt'

    account_id = Column(Integer, nullable=False, primary_key=True)
    statement_period_id = Column(Integer, nullable=False, primary_key=True)
    contract_id = Column(Integer, nullable=True, primary_key=True)
    currency_code = Column(String(50), nullable=False)
    total_gross_revenue_amount = Column(Numeric(32, 2), nullable=False)
    total_net_revenue_amount = Column(Numeric(32, 2), nullable=False)
    distribution_fee = Column(Numeric(32, 2), nullable=False)
    mechanical_deduction_total = Column(Numeric(32, 2), nullable=False)
    mechanical_deduction_admin_fee_total = Column(Numeric(32, 2), nullable=False)

    @classmethod
    def get_for_account(
        cls,
        account_id: int,
        contract_id: int | None = None,
        statement_period_ids: list[int] | None = None,
        order_dir: OrderDirection = OrderDirection.ASC,
    ) -> list:
        """Get summary entries by account, contract, and periods.

        Args:
            account_id (int): The ID of an account
            contract_id (int): Optional ID of a contract
            statement_period_ids (list): Optional list of statement period IDs
            order_dir (OrderDirection): Order direction
        Returns:
            list: list of ledger summary entries
        """
        entities = [
            cls.account_id,
            cls.statement_period_id,
            func.max(cls.currency_code).label('currency_code'),
            func.sum(cls.total_gross_revenue_amount).label('total_gross_revenue_amount'),
            func.sum(cls.total_net_revenue_amount).label('total_net_revenue_amount'),
            func.sum(cls.distribution_fee).label('distribution_fee'),
            func.sum(cls.mechanical_deduction_total).label('mechanical_deduction_total'),
            func.sum(cls.mechanical_deduction_admin_fee_total).label(
                'mechanical_deduction_admin_fee_total'),
        ]
        filters = [
            cls.account_id == account_id,
        ]
        grouping = [
            cls.account_id,
            cls.statement_period_id,
        ]

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

        if statement_period_ids:
            filters.append(cls.statement_period_id.in_(statement_period_ids))

        order_direction = asc if order_dir == OrderDirection.ASC else desc

        return cls.query\
            .with_entities(*entities)\
            .filter(*filters)\
            .group_by(*grouping)\
            .order_by(order_direction(cls.statement_period_id))\
            .all()

    @classmethod
    def check_account_mechanicals(cls, account_id: int) -> bool:
        """Check whether an account has mechanical deductions.

        Args:
            account_id (int): ID of the account to check
        Returns:
            bool: Whether any of the periods have non-zero mechanical deductions
        """
        rows = cls.query\
            .filter(cls.account_id == account_id, cls.mechanical_deduction_total != 0)\
            .count()

        return rows != 0