"""Expense by subaccount."""

from sqlalchemy import asc
from sqlalchemy import Column
from sqlalchemy import func
from sqlalchemy import Numeric

from moneyhub.models.expenses_base import ExpensesBase
from moneyhub.models.snowflake_base import BaseModel


class ExpensesBySubaccount(BaseModel, ExpensesBase):
    """ExpensesBySubaccount model."""

    __tablename__ = 'expenses_by_subaccount_dbt'

    subaccount_id = Column(Numeric(38, 0), nullable=False)

    @classmethod
    def get_by_subaccount(
        cls,
        account_id: int,
        limit: int | None = None,
        offset: int | None = None,
        contract_id: int | None = None,
        statement_period_id_start: int | None = None,
        statement_period_id_end: int | None = None,
        upc: str | None = None,
        expense_type_id: int | None = None,
        artist_id: int | None = None,
        subaccount_id: int | None = None,
    ) -> tuple[list, int]:
        """Get expenses grouped by subaccount.

        Args:
            account_id (int): The id of an account
            limit (int): how many entities to retrieve.
            offset (int): the offset (for pagination).
            contract_id (int): Optional id of the contract
            statement_period_id_start (int): Optional id of the statement period to range from
            statement_period_id_end (int): Optional id of the statement period to range to
            upc (str): Optional UPC to filter by
            expense_type_id (int): Optional expense type id to filter by
            artist_id (int): Optional artist id to filter by
            subaccount_id (int): Optional subaccount id to filter by
        Returns:
            list: list of expenses by subaccount
        """
        selects = [
            cls.subaccount_id,
            cls.account_id,
            cls.adjustment_payee_currency_code,
            func.sum(cls.adjustment_amount_payee_currency).label(
                'adjustment_amount_payee_currency'),
            func.count().over().label('total_records')
        ]

        filters = [cls.account_id == account_id]

        grouping = [cls.subaccount_id, cls.account_id, cls.adjustment_payee_currency_code]  # noqa: E501

        if contract_id:
            filters.append(cls.contract_id == contract_id)
        if expense_type_id:
            filters.append(cls.reference_adjustment_type_id == expense_type_id)
        if statement_period_id_start and statement_period_id_end:
            filters.append(cls.apply_to_statement_period_id.between(
                statement_period_id_start, statement_period_id_end))
        if upc:
            filters.append(cls.upc == upc)
        if artist_id:
            filters.append(cls.artist_id == artist_id)
        if subaccount_id:
            filters.append(cls.subaccount_id == subaccount_id)

        query = cls.query.with_entities(*selects).distinct().filter(*filters). \
            group_by(*grouping).order_by(asc(cls.subaccount_id))

        records = query.limit(limit).offset(offset).all()
        total_records = int(records[0].total_records) if records else 0

        return records, total_records