"""RevenueByCountry Model."""

from sqlalchemy import Column
from sqlalchemy import func
from sqlalchemy import Numeric
from sqlalchemy import String

from moneyhub.models.revenue_base import apply_filters
from moneyhub.models.revenue_base import apply_subaccount_revenue
from moneyhub.models.snowflake_base import BaseModel


class RevenueByCountry(BaseModel):
    """Revenue By Country model."""

    __tablename__ = 'revenue_by_country_dbt'

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

    @classmethod
    def get_by_account_id(
            cls,
            account_id: int,
            artist_id: int | None,
            subaccount_id: int | None,
            product_id: int | None,
            track_unique_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,
            country_codes: list[str] | None,
            imprint_ids: list[int] | None,
            transaction_type_ids: list[int] | None,
            subaccount_info: dict | None,
            project_id: int | None = None,
    ) -> list:
        """GET list of revenue by country for a specified account ID and contract ID.

        Args:
            account_id (int): the id of an account
            artist_id (int): the id of an artist to filter by
            subaccount_id (int): the id of a subaccount to filter by
            product_id (int): the id of a product to filter by
            track_unique_id (int): the id of a track 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): Start of the activity period range
            activity_period_id_end (int): End of the activity period range
            store_ids (list[int]): list of store IDs to filter by
            country_codes (list[str]): list of country codes to filter by
            imprint_ids (list[int]): list of imprint IDs to filter by
            transaction_type_ids (list[int]): list of transaction type IDs to filter by
            subaccount_info (dict): Subaccount information
            project_id (int | None): project id to filter by
        Returns:
            list: list of revenue by country
        """
        filters = [(cls.account_id == account_id)]
        group_by = [
            cls.account_id,
            cls.account_payee_currency,
            cls.country_code,
        ]
        with_entities = [
            cls.account_id,
            cls.account_payee_currency,
            cls.country_code,
            func.sum(cls.net_revenue_payee_currency).label('net_revenue_payee_currency'),
            func.sum(cls.gross_revenue_payee_currency).label('gross_revenue_payee_currency')
        ]

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

        apply_filters(
            cls=cls,
            filters=filters,
            activity_period_id_start=activity_period_id_start,
            activity_period_id_end=activity_period_id_end,
            artist_id=artist_id,
            country_codes=country_codes,
            imprint_ids=imprint_ids,
            product_id=product_id,
            project_id=project_id,
            statement_period_id_start=statement_period_id_start,
            statement_period_id_end=statement_period_id_end,
            store_ids=store_ids,
            subaccount_id=subaccount_id,
            track_unique_id=track_unique_id,
            transaction_type_ids=transaction_type_ids,
        )

        apply_subaccount_revenue(subaccount_info, with_entities, cls)

        return cls.query \
            .with_entities(*with_entities) \
            .filter(*filters) \
            .group_by(*group_by) \
            .all()