"""Expenses Base model."""

from sqlalchemy import Column
from sqlalchemy import desc
from sqlalchemy import func
from sqlalchemy import Integer
from sqlalchemy import Numeric
from sqlalchemy import String
from sqlalchemy.engine import Row

from moneyhub.constants.constants import GroupBy


class ExpensesBase:
    """Expenses Base model."""

    account_id = Column(Integer, nullable=False)
    activity_statement_period_id = Column(Integer, nullable=False)
    adjustment_amount_payee_currency = Column(Numeric(32, 2), nullable=False)
    adjustment_payee_currency_code = Column(String(3), nullable=False)
    apply_to_statement_period_id = Column(Integer, nullable=False)
    artist_name = Column(String(500), nullable=True)
    artist_id = Column(Integer, nullable=True)
    contract_id = Column(Integer, nullable=True)
    reference_adjustment_type_id = Column(Integer, nullable=False)
    reference_adjustment_type_name = Column(String(500), nullable=False)
    upc = Column(String(20), nullable=False)
    worksheet_adjustment_detail_id = Column(Integer, nullable=True, primary_key=True)

    @classmethod
    def get_by_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,
        distribution_type: str | None = None,
        expense_type_id: int | None = None,
        artist_id: int | None = None,
        subaccount_id: int | None = None,
        group_by: GroupBy | None = None
    ) -> tuple[list, int]:
        """Get expenses by account 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): 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
            distribution_type (int): Optional distributon type 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
            group_by (str): Optional dimension to group expenses by
        Returns:
            list: list of expenses
        """
        # TODO: remove FF
        filters = [cls.account_id == account_id]
        grouping = []
        selects = [
            cls.worksheet_adjustment_detail_id,
            cls.apply_to_statement_period_id,
            cls.account_id,
            cls.contract_id,
            cls.distribution_type,
            cls.note,
            cls.reference_adjustment_type_id,
            cls.reference_adjustment_type_name,
            cls.upc,
            cls.adjustment_payee_currency_code,
            cls.subaccount_id,
            cls.subaccount_name,
            func.count().over().label('total_records')
        ]
        order_by = cls.apply_to_statement_period_id

        if contract_id:
            filters.append(cls.contract_id == contract_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 expense_type_id:
            filters.append(cls.reference_adjustment_type_id == expense_type_id)
        if distribution_type:
            filters.append(cls.distribution_type == distribution_type)
        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

        if group_by:
            match group_by:
                case GroupBy.UPC:
                    selects = [
                        cls.account_id,
                        cls.upc,
                        cls.distribution_type,
                        cls.adjustment_payee_currency_code,
                        func.sum(cls.adjustment_amount_payee_currency).label(
                            'adjustment_amount_payee_currency'),
                        func.count().over().label('total_records')
                    ]
                    grouping.append(cls.account_id)
                    grouping.append(cls.upc)
                    grouping.append(cls.distribution_type)
                    grouping.append(cls.adjustment_payee_currency_code)
                    order_by = cls.upc
                case GroupBy.EXPENSE_TYPE_ID:
                    selects = [
                        cls.reference_adjustment_type_id,
                        cls.reference_adjustment_type_name,
                        cls.adjustment_payee_currency_code,
                        func.sum(cls.adjustment_amount_payee_currency).label(
                            'adjustment_amount_payee_currency'),
                        func.count().over().label('total_records')
                    ]
                    grouping.append(cls.reference_adjustment_type_id)
                    grouping.append(cls.adjustment_payee_currency_code)
                    grouping.append(cls.reference_adjustment_type_name)
                    order_by = cls.reference_adjustment_type_id
        else:
            selects.append(cls.adjustment_amount_payee_currency)

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

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

        return records, total_records

    @classmethod
    def get_artists_by_account(cls, account_id: int) -> list:
        """Get a list of artists associated with expenses based on an account ID.

        Args:
            account_id (int): ID of the account.
        Returns:
            list: list of artists ids and names
        """
        return cls.query \
            .with_entities(
                cls.artist_id,
                cls.artist_name,
            ) \
            .distinct() \
            .filter(cls.account_id == account_id) \
            .order_by(cls.artist_name.asc()) \
            .all()

    @classmethod
    def get_upcs_by_account(cls, account_id: int) -> list:
        """Get a list of available UPCs based on an account ID.

        Args:
            account_id (int): ID of the account.
        Returns:
            list: list of available UPCs and distribution types
        """
        return cls.query \
            .with_entities(
                cls.account_id,
                cls.upc,
                cls.distribution_type,
            ) \
            .distinct() \
            .filter(cls.account_id == account_id) \
            .order_by(cls.upc.asc()) \
            .all()

    @classmethod
    def get_account_adjustment_detail_activity(cls, account_id: int) -> Row:
        """Get adjustment account activity.

        Args:
            account_id (int): Account to get revenue for.
        Returns:
            Row: SqlAlchemy row containing adjustment detail information.
        """
        return cls.query.filter(cls.account_id == account_id).first()

    @classmethod
    def get_expenses_types_by_account_id(
            cls, account_id: int) -> list:
        """Get associated expenses types for expenses for a given account.

        Args:
            account_id (int): ID of an account
        Returns:
            list: list of expenses types
        """
        return cls.query \
            .with_entities(
                cls.reference_adjustment_type_id,
                cls.reference_adjustment_type_name.label(
                    'type_name')
            ) \
            .distinct() \
            .filter(cls.account_id == account_id) \
            .order_by(cls.reference_adjustment_type_id.asc()) \
            .all()

    @classmethod
    def get_expenses_subaccounts_by_account_id(
            cls, account_id: int) -> list:
        """Get associated subaccounts for expenses for a given account.

        Args:
            account_id (int): ID of an account
        Returns:
            list: list of subaccounts
        """
        return cls.query \
            .with_entities(
                cls.account_id,
                cls.subaccount_id,
                cls.subaccount_name
            ) \
            .distinct() \
            .filter(cls.account_id == account_id) \
            .order_by(cls.subaccount_id.asc()) \
            .all()

    @classmethod
    def get_expenses_by_account_and_statement_periods(
            cls,
            account_id: int,
            statement_period_ids: list[int],
            contract_id: int | None
    ) -> list:
        """Get a payee's applied expenses by statement periods.

        Args:
            account_id (int): The id of an account
            contract_id (int): Optional id of the contract
            statement_period_ids (int): The id of the statement period
        Returns:
            dict: dict of applied ledger adjustments
        """
        filters = [
            cls.account_id == account_id,
            cls.apply_to_statement_period_id.in_(statement_period_ids),
        ]

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

        with_entities = [
            func.sum(cls.adjustment_amount_payee_currency).label(
                'adjustment_amount_payee_currency'),
            cls.adjustment_payee_currency_code,
            cls.apply_to_statement_period_id.label('statement_period_id'),
            cls.reference_adjustment_type_id,
            cls.reference_adjustment_type_name,
        ]

        group_by = [
            cls.adjustment_payee_currency_code,
            cls.apply_to_statement_period_id.label('statement_period_id'),
            cls.reference_adjustment_type_id,
            cls.reference_adjustment_type_name,
        ]

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