"""Revenue By Recording Base Model."""

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


class RevenueByRecordingBase:
    """Revenue By Recording Base model."""

    recording_title = Column(String(255), nullable=False)
    artist = Column(String(255), nullable=False)
    version = Column(String(255), nullable=True)
    isrc = Column(String(255), nullable=True)
    account_id = Column(Numeric(12, 0), nullable=False, primary_key=True)
    contract_id = Column(Numeric(12, 0), nullable=False, primary_key=True)
    statement_period_id = Column(Numeric(38, 0), nullable=False, primary_key=True)
    account_payee_currency = Column(String(50), nullable=False)
    net_share_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,
        contract_id: int | None,
        statement_period_id_start: int | None,
        statement_period_id_end: int | None,
        recording_title: str | None,
        order_by: str,
        order_dir: OrderDirection,
        activity_period_id_start: int | None = None,
        activity_period_id_end: int | None = None,
        store_ids: list[int] | None = None,
        country_codes: list[str] | None = None,
        imprint_ids: list[int] | None = None,
        transaction_type_ids: list[int] | None = None,
    ) -> tuple[list, int]:
        """GET list of revenue by recording 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).
            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
            recording_title (str): recording name to filter by
            order_by (str): Key to sort by (defaults to no sorting)
            order_dir (str): Direction to sort by (ASC or DESC)
            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
        Returns:
            list: list of revenue by recording
        """
        filters = [(cls.account_id == account_id)]
        group_by = [
            cls.recording_id,
            cls.recording_title,
            cls.artist,
            cls.version,
            cls.isrc,
            cls.account_id,
            cls.account_payee_currency,
        ]
        with_entities = [
            cls.recording_id,
            cls.recording_title,
            cls.artist,
            cls.version,
            cls.isrc,
            cls.account_id,
            cls.account_payee_currency,
            func.sum(cls.net_share_payee_currency).label('net_share_payee_currency'),
            func.sum(cls.gross_revenue_payee_currency).label('gross_revenue_payee_currency'),
            func.count().over().label('total_records'),
        ]
        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,
            country_codes=country_codes,
            imprint_ids=imprint_ids,
            statement_period_id_start=statement_period_id_start,
            statement_period_id_end=statement_period_id_end,
            store_ids=store_ids,
            transaction_type_ids=transaction_type_ids,
        )

        if recording_title:
            filters.append(cls.recording_title.ilike(f'%{recording_title}%'))

        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))

        if limit != 0:
            query = query.limit(limit).offset(offset)

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

        return records, total_records

    @classmethod
    def get_recordings_by_account_id(
        cls,
        account_id: int,
        limit: int,
        search_term: str | None,
        contract_id: int | None = None,
    ) -> list:
        """Get list of distinct recordings for a specified account.

        Args:
            account_id (int): the id of an account
            limit (int): maximum number of recordings to return
            search_term (str | None): search term to filter recordings by title
            contract_id (int | None): the id of a contract to filter by
        Returns:
            list: list of distinct recordings with recording_id, recording_title and isrc
        """
        filters = [cls.account_id == account_id]

        if search_term:
            filters.append(cls.recording_title.ilike(f'%{search_term}%'))

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

        with_entities = [
            cls.recording_id,
            cls.recording_title,
            cls.isrc,
        ]

        query = cls.query \
            .with_entities(*with_entities) \
            .filter(*filters) \
            .distinct() \
            .order_by(cls.recording_title)

        records = query.limit(limit).all()
        return records