"""Earnings Transfer model."""

from typing import Optional

from abacus_common_logic.connectors.database import db
from abacus_common_logic.models import BaseModel, NormalizedDateTime
from sqlalchemy import Enum, asc, desc, literal_column, or_, table
from sqlalchemy.engine.row import Row
from sqlalchemy.orm import Mapped

from royalties.constants.constants import (
    EARNINGS_TRANSFER_INPUT,
    EARNINGS_TRANSFER_RATE_TYPES,
    EARNINGS_TRANSFER_TYPES,
)


class EarningsTransfer(BaseModel):
    """Earnings Transfer model."""

    __tablename__ = 'earnings_transfer'

    earnings_transfer_id: Mapped[int] = db.Column(db.Integer, primary_key=True)
    from_contract_id: Mapped[int] = db.Column(
        db.Integer, db.ForeignKey('contract.contract_id')
    )
    to_contract_id: Mapped[int] = db.Column(
        db.Integer, db.ForeignKey('contract.contract_id')
    )
    transfer_type: Mapped[str] = db.Column(
        Enum(
            *EARNINGS_TRANSFER_TYPES,
            name='transfer_type',
            create_type=False,
        ),
        nullable=False,
    )
    rate_type: Mapped[str | None] = db.Column(
        Enum(
            *EARNINGS_TRANSFER_RATE_TYPES,
            name='rate_type',
            create_type=False,
        ),
        nullable=True,
    )
    transfer_amount: Mapped[float] = db.Column(db.Numeric(20, 12))
    input: Mapped[str | None] = db.Column(
        Enum(
            *EARNINGS_TRANSFER_INPUT,
            name='input',
            create_type=False,
        ),
        nullable=True,
    )
    negative: Mapped[bool] = db.Column(db.Boolean, nullable=False, default=False)
    active: Mapped[bool] = db.Column(db.Boolean, nullable=False, default=False)
    use_static_balance: Mapped[bool] = db.Column(
        db.Boolean, nullable=False, default=False
    )
    comment: Mapped[str | None] = db.Column(db.Text, nullable=True)
    created_at: Mapped[str] = db.Column(NormalizedDateTime(), nullable=False)

    @classmethod
    def get_earnings_transfers(
        cls,
        limit: int,
        offset: int,
        sort_by: str,
        sort_order: str,
        reference_payment_entities: str = None,
        payment_schedules: str = None,
    ) -> tuple:
        """Get a list of earnings transfer.

        Args:
            limit(int): the size of page
            offset(int): the number of items to skip before returning results
            sort_by (str): column name by which the results should be sorted
            sort_order (str): order direction (asc or desc)
            reference_payment_entities (str): comma separated list of payment entities
            payment_schedules (str): comma separated list of payment schedules

        Returns:
            A tuple contains the fields items and total_count
        """
        query = cls.query

        if reference_payment_entities or payment_schedules:
            query = query.join(
                table('account_contract').alias('ac'),
                cls.from_contract_id == literal_column('ac.contract_id'),
            ).join(
                table('account_payment_term').alias('apt'),
                literal_column('apt.account_id') == literal_column('ac.account_id'),
            )

            if reference_payment_entities:
                query = query.filter(
                    literal_column('apt.payment_entity_id').in_(
                        reference_payment_entities.split(',')
                    )
                )

            if payment_schedules:
                query = query.filter(
                    literal_column('apt.payment_schedule').in_(
                        payment_schedules.split(',')
                    )
                )

        items = (
            query.order_by(desc(sort_by) if sort_order == 'desc' else asc(sort_by))
            .limit(limit)
            .offset(offset)
            .all()
        )

        total_count = query.count()

        return items, total_count

    @classmethod
    def get_earnings_transfers_by_contract_id(
        cls,
        limit: int,
        offset: int,
        sort_by: str,
        sort_order: str,
        contract_id: int,
        transfer_type: str = None,
    ) -> tuple:
        """Get the list of earnings transfers.

        Args:
            limit(int): the size of page
            offset(int): the number of items to skip before returning results
            sort_by (str): column name by which the results should be sorted
            sort_order (str): order direction (asc or desc)
            contract_id (int): id of the contract
            transfer_type (str)(Optional): either reclass, override or transfer
        Returns:
            A tuple contains the fields items and total_count
        """
        query = cls.query.filter(
            or_(cls.from_contract_id == contract_id, cls.to_contract_id == contract_id)
        )

        if transfer_type:
            query = query.filter(cls.transfer_type == transfer_type)

        items = (
            query.order_by(desc(sort_by) if sort_order == 'desc' else asc(sort_by))
            .limit(limit)
            .offset(offset)
            .all()
        )
        total_count = query.count()

        return items, total_count

    @classmethod
    def get_earnings_transfers_from_contract(
        cls,
        contract_id: int,
    ) -> list:
        """Get the list of earnings transfers from a specific contract.

        Args:
            contract_id (int): id of the contract
        Returns:
            A list of earnings transfers
        """
        query = cls.query.filter(cls.from_contract_id == contract_id)

        items = query.all()

        return items