"""DP Payment Persister."""

from datetime import date, datetime, timezone
from decimal import Decimal
from typing import Optional

from sqlalchemy import Row, func
from sqlalchemy.orm.session import Session

from collaborator.connectors import mysql
from collaborator.models.rds.dp_payment import (
    DpPayment,
    PayoneerEventType,
    PayoneerPaymentStatus,
)
from collaborator.models.rds.transaction_persister import TransactionPersister
from collaborator.utils.typing import User


class DpPaymentPersister:
    """Handles high level operations for DP payments."""

    @classmethod
    @mysql.db_session
    def get_by_filters(
        cls,
        abacus_statement_period_id: Optional[int] = None,
        collaborator_id: Optional[int] = None,
        account_id: Optional[int] = None,
        payoneer_program_id: Optional[str] = None,
        payoneer_status: Optional[str] = None,
        sort_key: Optional[str] = None,
        sort_direction: Optional[str] = None,
        *,
        session: Session,
    ) -> list[Row[tuple[DpPayment, Decimal]]]:
        """Get DP payments, optionally filtered by various criteria.

        Args:
            abacus_statement_period_id (int | None): Abacus statement period
                ID.  When `None` all periods are returned.
            collaborator_id (int | None): Collaborator ID. When `None` all
                collaborators are returned.
            account_id (int | None): Account ID. When `None` all accounts are
                returned.
            payoneer_program_id (str | None): Payoneer program ID. When `None`
                all programs are returned.
            payoneer_status (str | None): Payoneer status. When `None` all
                statuses are returned.
            sort_key (str | None): The key to sort by.
            sort_direction (str | None): The direction to sort by.
            session (sqlalchemy.orm.session.Session): database session

        Returns:
            list: DP payments with running total_amount window value.
        """
        query = session.query(
            DpPayment,
            func.sum(DpPayment.amount).over().label("total_amount"),
        )
        if abacus_statement_period_id is not None:
            query = query.filter(
                DpPayment.abacus_statement_period_id == abacus_statement_period_id,
            )
        if collaborator_id is not None:
            query = query.filter(
                DpPayment.collaborator_id == collaborator_id,
            )
        if account_id is not None:
            query = query.filter(
                DpPayment.account_id == account_id,
            )
        if payoneer_program_id is not None:
            query = query.filter(
                DpPayment.payoneer_program_id == payoneer_program_id,
            )
        if payoneer_status is not None:
            query = query.filter(
                DpPayment.payoneer_payment_status == payoneer_status,
            )

        order = getattr(DpPayment, sort_key) if sort_key else DpPayment.dp_payment_id
        order = order.desc() if sort_direction == "DESC" else order.asc()
        return query.order_by(order).all()

    @classmethod
    @mysql.db_session
    def replace_by_abacus_statement_period_id(
        cls,
        abacus_statement_period_id: int,
        payments: list,
        session: Session,
    ) -> list[DpPayment]:
        """Replace DP payments for a specific Abacus statement period ID.

        Deletes existing payments for the statement period and inserts new ones.

        Args:
            abacus_statement_period_id (int): Abacus statement period ID
            payments (list): List of payment dictionaries to insert
            session (sqlalchemy.orm.session.Session): database session

        Returns:
            list: The newly created DP payments
        """
        # Delete existing payments for the statement period
        session.query(DpPayment).filter(
            DpPayment.abacus_statement_period_id == abacus_statement_period_id
        ).delete(synchronize_session=False)

        # Create new payments
        dp_payments = [DpPayment(**payment) for payment in payments]
        session.add_all(dp_payments)
        session.commit()

        return dp_payments

    @classmethod
    @mysql.db_session
    def approve_by_abacus_statement_period_id(
        cls,
        abacus_statement_period_id: int,
        user: User,
        session: Session,
    ) -> list[DpPayment]:
        """Approve DP payments for a specific Abacus statement period ID.

        Args:
            abacus_statement_period_id (int): Abacus statement period ID
            user (User): User performing the operation (for audit purposes)
            session (sqlalchemy.orm.session.Session): database session

        Returns:
            list: The newly created DP payments
        """
        query = session.query(DpPayment).filter(
            DpPayment.abacus_statement_period_id == abacus_statement_period_id
        )

        payment = query.first()
        if not payment:
            return []

        if payment.approved_date is None:
            query.update(
                {
                    DpPayment.approved_date: datetime.now(tz=timezone.utc),
                    DpPayment.updated_by: user.id,
                    DpPayment.approved_by: user.id,
                },
                synchronize_session="fetch",
            )
            session.commit()

        return query.all()

    @classmethod
    @mysql.db_session
    def get_by_payoneer_payment_id(
        cls,
        payoneer_payment_id: str,
        session: Session,
    ) -> DpPayment | None:
        """Get a DP payment by its Payoneer payment ID.

        Args:
            payoneer_payment_id (str): Payoneer payment ID (client_reference_id in webhooks)
            session (sqlalchemy.orm.session.Session): database session

        Returns:
            DpPayment | None: matching DP payment or None
        """
        return (
            session.query(DpPayment)
            .filter(DpPayment.payoneer_payment_id == payoneer_payment_id)
            .first()
        )

    @classmethod
    @mysql.db_session
    def update_payoneer_status_bulk(
        cls,
        dp_payment_ids: list[int],
        payoneer_payment_status: PayoneerPaymentStatus,
        event_type: Optional[PayoneerEventType],
        reason: Optional[str],
        user: User,
        session: Session,
    ) -> None:
        """Update the Payoneer payment status for multiple DP payments at once.

        Args:
            dp_payment_ids (list[int]): DP payment IDs to update
            payoneer_payment_status (PayoneerPaymentStatus): new status value
            event_type (Optional[PayoneerEventType]): event type received from Payoneer
            reason (Optional[str]): reason description from the event
            user (User): User performing the operation (for audit purposes)
            session (sqlalchemy.orm.session.Session): database session
        """
        session.query(DpPayment).filter(
            DpPayment.dp_payment_id.in_(dp_payment_ids)
        ).update(
            {
                DpPayment.payoneer_payment_status: payoneer_payment_status,
                DpPayment.latest_payoneer_event: event_type,
                DpPayment.latest_payoneer_event_reason: reason,
                DpPayment.updated_by: user.id,
            },
            synchronize_session="fetch",
        )
        session.commit()

    @classmethod
    @mysql.db_session
    def update_payoneer_status(
        cls,
        dp_payment_id: int,
        payoneer_payment_status: PayoneerPaymentStatus,
        event_type: Optional[PayoneerEventType],
        reason: Optional[str],
        user: User,
        session: Session,
    ) -> None:
        """Update the Payoneer payment status for a DP payment.

        Args:
            dp_payment_id (int): DP payment ID
            payoneer_payment_status (PayoneerPaymentStatus): new status value
            event_type (Optional[PayoneerEventType]): event type received from Payoneer
            reason (Optional[str]): reason description from the event
            user (User): User performing the operation (for audit purposes)
            session (sqlalchemy.orm.session.Session): database session
        """
        session.query(DpPayment).filter(
            DpPayment.dp_payment_id == dp_payment_id
        ).update(
            {
                DpPayment.payoneer_payment_status: payoneer_payment_status,
                DpPayment.latest_payoneer_event: event_type,
                DpPayment.latest_payoneer_event_reason: reason,
                DpPayment.updated_by: user.id,
            },
            synchronize_session="fetch",
        )
        session.commit()

    @classmethod
    @mysql.db_session
    def set_collaborator_transaction_id(
        cls,
        dp_payment_id: int,
        transaction_id: int,
        user: User,
        session: Session,
    ) -> None:
        """Set the collaborator transaction ID for a DP payment.

        Args:
            dp_payment_id (int): DP payment ID
            transaction_id (int): Transaction ID to link
            user (User): User performing the operation (for audit purposes)
            session (sqlalchemy.orm.session.Session): database session
        """
        session.query(DpPayment).filter(
            DpPayment.dp_payment_id == dp_payment_id,
            DpPayment.collaborator_transaction_id.is_(None),
        ).update(
            {
                DpPayment.collaborator_transaction_id: transaction_id,
                DpPayment.updated_by: user.id,
            },
            synchronize_session="fetch",
        )
        session.commit()

    @classmethod
    @mysql.db_session
    def create_and_link_transaction(
        cls,
        dp_payment_id: int,
        collaborator_id: int,
        transaction_type: str,
        transaction_date: date,
        description: str,
        original_amount: Decimal,
        chargeable_amount: Decimal,
        statement_period_id: int,
        currency: str,
        user: User,
        session: Session,
    ) -> None:
        """Create a transaction and atomically link it to a DP payment.

        Both the transaction insert and the dp_payment update are committed
        in a single DB transaction so a partial failure cannot leave an
        orphan transaction without a corresponding link.

        Args:
            dp_payment_id (int): DP payment to link the transaction to
            collaborator_id (int): Collaborator ID
            transaction_type (str): Transaction type constant
            transaction_date (date): Date of the transaction
            description (str): Transaction description
            original_amount (Decimal): Original amount
            chargeable_amount (Decimal): Chargeable amount
            statement_period_id (int): Statement period ID
            currency (str): ISO 4217 currency code
            session (sqlalchemy.orm.session.Session): database session
        """
        transaction = TransactionPersister.create_transaction(
            collaborator_id=collaborator_id,
            transaction_type=transaction_type,
            transaction_date=transaction_date,
            description=description,
            original_amount=original_amount,
            collaborator_share=None,
            chargeable_amount=chargeable_amount,
            statement_period_id=statement_period_id,
            transferwise_transaction_id=None,
            report_id=None,
            voided_transaction_id=None,
            currency=currency,
            session=session,
        )
        cls.set_collaborator_transaction_id(
            dp_payment_id, transaction["id"], user=user, session=session
        )