"""
Repository for Payment transactions and receipts.
"""

from uuid import UUID

from sqlalchemy import func, select
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.orm import selectinload

from app.models.billing import Payment, PaymentMode


class PaymentRepository:
    """Repository handling database operations for Payment records."""

    async def create(self, db: AsyncSession, payment: Payment) -> Payment:
        """Persist a new payment receipt."""
        db.add(payment)
        await db.commit()
        await db.refresh(payment)
        return payment

    async def get_by_id(
        self,
        db: AsyncSession,
        payment_id: UUID,
        lab_id: UUID | None = None,
    ) -> Payment | None:
        """Fetch payment by ID with relationships eager loaded."""
        stmt = (
            select(Payment)
            .where(Payment.id == payment_id)
            .options(
                selectinload(Payment.received_by_user),
                selectinload(Payment.bill),
            )
        )
        if lab_id:
            stmt = stmt.where(Payment.lab_id == lab_id)
        res = await db.execute(stmt)
        return res.scalar_one_or_none()

    async def list_by_bill(
        self,
        db: AsyncSession,
        bill_id: UUID,
        lab_id: UUID | None = None,
    ) -> list[Payment]:
        """Fetch all payment records for an invoice ordered by paid_at ascending."""
        stmt = (
            select(Payment)
            .where(Payment.bill_id == bill_id)
            .options(selectinload(Payment.received_by_user))
            .order_by(Payment.paid_at.asc())
        )
        if lab_id:
            stmt = stmt.where(Payment.lab_id == lab_id)
        res = await db.execute(stmt)
        return list(res.scalars().all())

    async def list_payments(
        self,
        db: AsyncSession,
        lab_id: UUID,
        payment_mode: PaymentMode | None = None,
        offset: int = 0,
        limit: int = 50,
    ) -> tuple[list[Payment], int]:
        """List paginated payment records for a lab branch."""
        stmt = select(Payment).where(Payment.lab_id == lab_id).options(selectinload(Payment.received_by_user))
        count_stmt = select(func.count(Payment.id)).where(Payment.lab_id == lab_id)

        if payment_mode:
            stmt = stmt.where(Payment.payment_mode == payment_mode)
            count_stmt = count_stmt.where(Payment.payment_mode == payment_mode)

        stmt = stmt.order_by(Payment.paid_at.desc()).offset(offset).limit(limit)

        total_res = await db.execute(count_stmt)
        total = total_res.scalar_one()

        items_res = await db.execute(stmt)
        items = list(items_res.scalars().all())

        return items, total