"""
Repository for Referral partner management.
"""

from datetime import UTC, datetime
from uuid import UUID

from sqlalchemy import func, or_, select
from sqlalchemy.ext.asyncio import AsyncSession

from app.models.referral import Referral, ReferralType


class ReferralRepository:
    """Repository handling database operations for Referral partners."""

    async def create(self, db: AsyncSession, referral: Referral) -> Referral:
        """Persist a new referral partner."""
        db.add(referral)
        await db.commit()
        await db.refresh(referral)
        return referral

    async def get_by_id(
        self,
        db: AsyncSession,
        referral_id: UUID,
        lab_id: UUID | None = None,
    ) -> Referral | None:
        """Fetch referral partner by ID, respecting soft-delete."""
        stmt = select(Referral).where(
            Referral.id == referral_id,
            Referral.deleted_at.is_(None),
        )
        if lab_id:
            stmt = stmt.where(Referral.lab_id == lab_id)
        res = await db.execute(stmt)
        return res.scalar_one_or_none()

    async def get_by_code(
        self,
        db: AsyncSession,
        code: str,
        lab_id: UUID,
    ) -> Referral | None:
        """Fetch active referral partner by unique branch code."""
        stmt = select(Referral).where(
            Referral.lab_id == lab_id,
            Referral.code == code,
            Referral.deleted_at.is_(None),
        )
        res = await db.execute(stmt)
        return res.scalar_one_or_none()

    async def list_referrals(
        self,
        db: AsyncSession,
        lab_id: UUID,
        referral_type: ReferralType | None = None,
        is_active: bool | None = None,
        search: str | None = None,
        offset: int = 0,
        limit: int = 50,
    ) -> tuple[list[Referral], int]:
        """List paginated referral partners with optional filtering."""
        stmt = select(Referral).where(
            Referral.lab_id == lab_id,
            Referral.deleted_at.is_(None),
        )
        count_stmt = select(func.count(Referral.id)).where(
            Referral.lab_id == lab_id,
            Referral.deleted_at.is_(None),
        )

        if referral_type:
            stmt = stmt.where(Referral.referral_type == referral_type)
            count_stmt = count_stmt.where(Referral.referral_type == referral_type)

        if is_active is not None:
            stmt = stmt.where(Referral.is_active == is_active)
            count_stmt = count_stmt.where(Referral.is_active == is_active)

        if search:
            pattern = f"%{search.strip()}%"
            filter_expr = or_(
                Referral.name.ilike(pattern),
                Referral.code.ilike(pattern),
                Referral.phone.ilike(pattern),
            )
            stmt = stmt.where(filter_expr)
            count_stmt = count_stmt.where(filter_expr)

        stmt = stmt.order_by(Referral.name.asc()).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 update(self, db: AsyncSession, referral: Referral) -> Referral:
        """Persist updates to a referral partner."""
        await db.commit()
        await db.refresh(referral)
        return referral

    async def soft_delete(self, db: AsyncSession, referral: Referral) -> None:
        """Soft-delete a referral partner."""
        referral.deleted_at = datetime.now(UTC)
        await db.commit()