"""Expenses by Imprint model."""

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

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


class ExpensesByImprint(ExpensesBase, BaseModel):
    """Expenses by Imprint model."""

    __tablename__ = 'combined_expenses_by_imprint_dbt'

    imprint = Column(String(500), nullable=False)
    imprint_id = Column(Integer, nullable=False)
    subaccount_id = Column(Integer, nullable=True)

    @classmethod
    def get_by_imprint_account_id(
        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 imprint.

        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 imprint
        """
        selects = [
            cls.imprint_id,
            cls.imprint,
            cls.account_id,
            cls.subaccount_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.imprint,
            cls.imprint_id,
            cls.account_id,
            cls.subaccount_id,
            cls.adjustment_payee_currency_code
        ]

        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 subaccount_id:
            filters.append(cls.subaccount_id == subaccount_id)
        if artist_id:
            filters.append(cls.artist_id == artist_id)
        elif artist_id == 0:  # get expenses with empty artists (WAR-1895)
            filters.append(cls.artist_id == None)  # noqa: E711

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

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

        return records, total_records