"""RevenueBySubaccountModel."""

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


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


class RevenueBySubaccount(BaseModel):
    """Revenue By Subaccount model."""

    __tablename__ = 'revenue_by_subaccount_dbt'

    account_id = Column(Numeric(12, 0), nullable=False, primary_key=True)
    artist_id = Column(Numeric(32, 0), nullable=False, primary_key=False)
    statement_period_id = Column(Numeric(38, 0), nullable=False, primary_key=True)
    contract_id = Column(Numeric(12, 0), nullable=True, primary_key=True)
    store_id = Column(Numeric(12, 0), nullable=True)
    activity_period_id = Column(Numeric(12, 0), nullable=True)
    imprint_id = Column(Numeric(12, 0), nullable=True)
    transaction_type_id = Column(Numeric(12, 0), nullable=True)
    country_code = Column(String(2), nullable=True)
    subaccount_id = Column(Numeric(32, 0), nullable=False, primary_key=True)
    subaccount_name = Column(String(2000), nullable=False)
    account_payee_currency = Column(String(50), nullable=False)
    mechanical_deduction_amount_payee_currency = Column(Numeric(36, 12), nullable=False)
    publisher_admin_fee_payee_currency = Column(Numeric(36, 12), nullable=False)
    net_revenue_payee_currency = Column(Numeric(36, 12), nullable=False)
    gross_revenue_payee_currency = Column(Numeric(36, 12), nullable=False)

    @classmethod
    def get_by_account_id(
        cls,
        account_id: int,
        limit: int,
        offset: int,
        artist_id: int | None,
        contract_id: int | None,
        statement_period_id_start: int | None,
        statement_period_id_end: int | None,
        activity_period_id_start: int | None,
        activity_period_id_end: int | None,
        store_ids: list[int] | None,
        imprint_ids: list[int] | None,
        transaction_type_ids: list[int] | None,
        country_codes: list[str] | None,
        order_by: str,
        order_dir: OrderDirection,
        search_term: str | None,
    ) -> tuple[list, int]:
        """GET list of revenue by subaccount for a specified account ID and contract ID.

        Args:
            account_id (int): the id of an account
            limit (int): how many entities to retrieve.
            offset (int): the offset (for pagination).
            artist_id (int): the id of an artist to filter by
            contract_id (int): the id of a contract to filter by
            statement_period_id_start (int): start of the period range
            statement_period_id_end (int): end of the period range
            activity_period_id_start (int): Optional activity period range start
            activity_period_id_end (int): Optional activity period range end
            store_ids (list): Optional stores to filter by
            imprint_ids (list): Optional imprints to filter by
            transaction_type_ids (list): Optional transaction types to filter by
            country_codes (list): Optional country codes to filter by
            order_by (str): field to sort by
            order_dir (str): direction to sort by (ASC or DESC)
            search_term (str): query to search by
        Returns:
            list: list of revenue by subaccount
        """
        filters = [(cls.account_id == account_id)]
        group_by = [
            cls.subaccount_id,
            cls.subaccount_name,
            cls.account_id,
            cls.account_payee_currency,
        ]
        with_entities = [
            cls.subaccount_id,
            cls.subaccount_name,
            cls.account_id,
            cls.account_payee_currency,
            func.sum(cls.mechanical_deduction_amount_payee_currency).label(
                'mechanical_deduction_amount_payee_currency'),
            func.sum(cls.publisher_admin_fee_payee_currency).label(
                'publisher_admin_fee_payee_currency'),
            func.sum(cls.net_revenue_payee_currency).label('net_revenue_payee_currency'),
            func.sum(cls.gross_revenue_payee_currency).label('gross_revenue_payee_currency'),
            func.count().over().label('total_records'),
        ]

        apply_filters(
            cls,
            filters,
            activity_period_id_start=activity_period_id_start,
            activity_period_id_end=activity_period_id_end,
            artist_id=artist_id,
            contract_id=contract_id,
            country_codes=country_codes,
            imprint_ids=imprint_ids,
            statement_period_id_start=statement_period_id_start,
            statement_period_id_end=statement_period_id_end,
            transaction_type_ids=transaction_type_ids,
            store_ids=store_ids,
        )

        if contract_id:
            group_by.append(cls.contract_id)
            with_entities.append(cls.contract_id)
        if search_term:
            filters.append(
                (cls.subaccount_name.ilike(f'%{search_term}%')))

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

        query = cls.query.with_entities(*with_entities).filter(*filters).group_by(
            *group_by).order_by(order_direction(order_by))

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

        return records, total_records