"""
Repository for Commission ledger entries and payouts.
"""

from uuid import UUID

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

from app.models.order import VisitOrder
from app.models.referral import CommissionEntry


class CommissionRepository:
    """Repository handling database operations for Commission ledger records."""

    async def create(self, db: AsyncSession, entry: CommissionEntry) -> CommissionEntry:
        """Persist an immutable commission ledger entry."""
        db.add(entry)
        await db.commit()
        await db.refresh(entry)
        return entry

    async def get_by_id(
        self,
        db: AsyncSession,
        entry_id: UUID,
        lab_id: UUID | None = None,
    ) -> CommissionEntry | None:
        """Fetch commission entry by ID with relationships eager-loaded."""
        stmt = (
            select(CommissionEntry)
            .where(CommissionEntry.id == entry_id)
            .options(
                selectinload(CommissionEntry.referral),
                selectinload(CommissionEntry.order).selectinload(VisitOrder.patient),
                selectinload(CommissionEntry.created_by_user),
            )
        )
        if lab_id:
            stmt = stmt.where(CommissionEntry.lab_id == lab_id)
        res = await db.execute(stmt)
        return res.scalar_one_or_none()

    async def get_by_order_and_referral(
        self,
        db: AsyncSession,
        order_id: UUID,
        referral_id: UUID,
    ) -> CommissionEntry | None:
        """Fetch existing entry for order and partner to ensure idempotency."""
        stmt = (
            select(CommissionEntry)
            .where(
                CommissionEntry.order_id == order_id,
                CommissionEntry.referral_id == referral_id,
            )
            .options(
                selectinload(CommissionEntry.referral),
                selectinload(CommissionEntry.order).selectinload(VisitOrder.patient),
                selectinload(CommissionEntry.created_by_user),
            )
        )
        res = await db.execute(stmt)
        return res.scalar_one_or_none()

    async def list_commissions(
        self,
        db: AsyncSession,
        lab_id: UUID,
        referral_id: UUID | None = None,
        is_settled: bool | None = None,
        offset: int = 0,
        limit: int = 50,
    ) -> tuple[list[CommissionEntry], int]:
        """List paginated commission ledger entries with optional status/partner filter."""
        stmt = (
            select(CommissionEntry)
            .where(CommissionEntry.lab_id == lab_id)
            .options(
                selectinload(CommissionEntry.referral),
                selectinload(CommissionEntry.order).selectinload(VisitOrder.patient),
                selectinload(CommissionEntry.created_by_user),
            )
        )
        count_stmt = select(func.count(CommissionEntry.id)).where(CommissionEntry.lab_id == lab_id)

        if referral_id:
            stmt = stmt.where(CommissionEntry.referral_id == referral_id)
            count_stmt = count_stmt.where(CommissionEntry.referral_id == referral_id)

        if is_settled is not None:
            stmt = stmt.where(CommissionEntry.is_settled == is_settled)
            count_stmt = count_stmt.where(CommissionEntry.is_settled == is_settled)

        stmt = stmt.order_by(CommissionEntry.created_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

    async def list_unsettled_by_referral(
        self,
        db: AsyncSession,
        referral_id: UUID,
        lab_id: UUID | None = None,
    ) -> list[CommissionEntry]:
        """Fetch all unsettled commission entries for a partner."""
        stmt = (
            select(CommissionEntry)
            .where(
                CommissionEntry.referral_id == referral_id,
                CommissionEntry.is_settled.is_(False),
            )
            .options(
                selectinload(CommissionEntry.referral),
                selectinload(CommissionEntry.order).selectinload(VisitOrder.patient),
                selectinload(CommissionEntry.created_by_user),
            )
            .order_by(CommissionEntry.created_at.asc())
        )
        if lab_id:
            stmt = stmt.where(CommissionEntry.lab_id == lab_id)
        res = await db.execute(stmt)
        return list(res.scalars().all())

    async def update(self, db: AsyncSession, entry: CommissionEntry) -> CommissionEntry:
        """Persist changes (e.g. settlement status) to commission entry."""
        await db.commit()
        await db.refresh(entry)
        return entry