"""
Repository for appointment slots and booked appointments.
"""

from datetime import UTC, date, datetime, time, timedelta
from typing import Any, cast
from uuid import UUID
from zoneinfo import ZoneInfo

from sqlalchemy import ColumnElement, func, or_, select, update
from sqlalchemy.engine import CursorResult
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.orm import selectinload

from app.models.appointment import Appointment, AppointmentSlot, AppointmentStatus
from app.schemas.appointment import (
    AppointmentCreate,
    AppointmentSlotCreate,
    AppointmentSlotUpdate,
    AppointmentUpdate,
)


class AppointmentRepository:
    """Repository handling database operations for scheduling."""

    # ── Slots ─────────────────────────────────────────────────────────────────

    async def get_slot_by_id(
        self, db: AsyncSession, slot_id: UUID, lab_id: UUID | None = None
    ) -> AppointmentSlot | None:
        """Fetch an active, non-deleted appointment slot."""
        stmt = select(AppointmentSlot).where(
            AppointmentSlot.id == slot_id,
            AppointmentSlot.deleted_at.is_(None),
        )
        if lab_id is not None:
            stmt = stmt.where(AppointmentSlot.lab_id == lab_id)
        res = await db.execute(stmt)
        return res.scalar_one_or_none()

    async def get_slot_for_update(
        self, db: AsyncSession, slot_id: UUID, lab_id: UUID | None = None
    ) -> AppointmentSlot | None:
        """Fetch slot row with SELECT FOR UPDATE lock."""
        stmt = (
            select(AppointmentSlot)
            .where(
                AppointmentSlot.id == slot_id,
                AppointmentSlot.deleted_at.is_(None),
            )
            .with_for_update()
        )
        if lab_id is not None:
            stmt = stmt.where(AppointmentSlot.lab_id == lab_id)
        res = await db.execute(stmt)
        return res.scalar_one_or_none()

    async def list_slots(
        self,
        db: AsyncSession,
        limit: int = 50,
        offset: int = 0,
        lab_id: UUID | None = None,
        is_active: bool | None = None,
    ) -> tuple[list[AppointmentSlot], int]:
        """Fetch paginated appointment slots."""
        filters: list[ColumnElement[bool]] = [AppointmentSlot.deleted_at.is_(None)]
        if lab_id is not None:
            filters.append(AppointmentSlot.lab_id == lab_id)
        if is_active is not None:
            filters.append(AppointmentSlot.is_active == is_active)

        count_stmt = select(func.count()).select_from(AppointmentSlot).where(*filters)
        total: int = (await db.execute(count_stmt)).scalar_one()

        data_stmt = (
            select(AppointmentSlot)
            .where(*filters)
            .order_by(AppointmentSlot.start_at.asc())
            .offset(offset)
            .limit(limit)
        )
        res = await db.execute(data_stmt)
        return list(res.scalars().all()), total

    async def list_slots_in_range(
        self,
        db: AsyncSession,
        *,
        lab_id: UUID,
        start: datetime,
        end: datetime,
        limit: int = 50,
    ) -> tuple[list[AppointmentSlot], int]:
        """Fetch slots whose start_at falls in [start, end)."""
        filters: list[ColumnElement[bool]] = [
            AppointmentSlot.deleted_at.is_(None),
            AppointmentSlot.lab_id == lab_id,
            AppointmentSlot.start_at >= start,
            AppointmentSlot.start_at < end,
        ]
        count_stmt = select(func.count()).select_from(AppointmentSlot).where(*filters)
        total: int = (await db.execute(count_stmt)).scalar_one()
        data_stmt = (
            select(AppointmentSlot).where(*filters).order_by(AppointmentSlot.start_at.asc()).limit(limit)
        )
        res = await db.execute(data_stmt)
        return list(res.scalars().all()), total

    async def clear_unbooked_from(
        self,
        db: AsyncSession,
        *,
        lab_id: UUID,
        start: datetime,
        end: datetime | None = None,
    ) -> int:
        """Soft-delete future slots with zero bookings so they regenerate on demand.

        Booked slots are never touched — appointments already made keep their windows.
        Returns the number of slots cleared.
        """
        clear_filters: list[ColumnElement[bool]] = [
            AppointmentSlot.deleted_at.is_(None),
            AppointmentSlot.lab_id == lab_id,
            AppointmentSlot.start_at >= start,
            AppointmentSlot.booked_count == 0,
        ]
        if end is not None:
            clear_filters.append(AppointmentSlot.start_at < end)
        stmt = update(AppointmentSlot).where(*clear_filters).values(deleted_at=datetime.now(UTC))
        res = await db.execute(stmt)
        await db.flush()
        return cast(CursorResult[Any], res).rowcount or 0

    async def find_active_appointment_for_patient(
        self,
        db: AsyncSession,
        patient_id: UUID,
        lab_id: UUID | None = None,
    ) -> Appointment | None:
        """Return the patient's open appointment (pending, confirmed, or checked in)."""
        active_statuses = (
            AppointmentStatus.PENDING,
            AppointmentStatus.CONFIRMED,
            AppointmentStatus.CHECKED_IN,
        )
        filters: list[ColumnElement[bool]] = [
            Appointment.deleted_at.is_(None),
            Appointment.patient_id == patient_id,
            Appointment.status.in_(active_statuses),
        ]
        if lab_id is not None:
            filters.append(Appointment.lab_id == lab_id)
        stmt = (
            select(Appointment)
            .where(*filters)
            .options(selectinload(Appointment.slot), selectinload(Appointment.patient))
            .order_by(Appointment.created_at.desc())
            .limit(1)
        )
        res = await db.execute(stmt)
        return res.scalar_one_or_none()

    async def create_slot(self, db: AsyncSession, data: AppointmentSlotCreate) -> AppointmentSlot:
        """Create a new scheduling slot."""
        slot = AppointmentSlot(
            lab_id=data.lab_id,
            start_at=data.start_at,
            end_at=data.end_at,
            capacity=data.capacity,
            booked_count=0,
            is_active=data.is_active,
        )
        db.add(slot)
        await db.flush()
        await db.refresh(slot)
        return slot

    async def update_slot(
        self, db: AsyncSession, slot: AppointmentSlot, data: AppointmentSlotUpdate
    ) -> AppointmentSlot:
        """Update appointment slot properties."""
        if data.start_at is not None:
            slot.start_at = data.start_at
        if data.end_at is not None:
            slot.end_at = data.end_at
        if data.capacity is not None:
            slot.capacity = data.capacity
        if data.is_active is not None:
            slot.is_active = data.is_active

        await db.flush()
        await db.refresh(slot)
        return slot

    async def delete_slot(self, db: AsyncSession, slot: AppointmentSlot) -> None:
        """Soft-delete an appointment slot."""
        slot.deleted_at = datetime.now(UTC)
        await db.flush()

    # ── Appointments ──────────────────────────────────────────────────────────

    async def get_by_id(
        self, db: AsyncSession, appointment_id: UUID, lab_id: UUID | None = None
    ) -> Appointment | None:
        """Fetch appointment with patient and slot eager-loaded."""
        stmt = (
            select(Appointment)
            .where(
                Appointment.id == appointment_id,
                Appointment.deleted_at.is_(None),
            )
            .options(
                selectinload(Appointment.patient),
                selectinload(Appointment.slot),
            )
        )
        if lab_id is not None:
            stmt = stmt.where(Appointment.lab_id == lab_id)
        res = await db.execute(stmt)
        return res.scalar_one_or_none()

    async def list_appointments(
        self,
        db: AsyncSession,
        limit: int = 50,
        offset: int = 0,
        lab_id: UUID | None = None,
        status: AppointmentStatus | None = None,
        patient_id: UUID | None = None,
        slot_id: UUID | None = None,
        active_only: bool = False,
        target_date: date | None = None,
    ) -> tuple[list[Appointment], int]:
        """Fetch paginated appointments."""
        filters: list[ColumnElement[bool]] = [Appointment.deleted_at.is_(None)]
        if lab_id is not None:
            filters.append(Appointment.lab_id == lab_id)
        if status is not None:
            filters.append(Appointment.status == status)
        elif active_only:
            filters.append(
                Appointment.status.in_(
                    (
                        AppointmentStatus.PENDING,
                        AppointmentStatus.CONFIRMED,
                        AppointmentStatus.CHECKED_IN,
                    )
                )
            )
        if patient_id is not None:
            filters.append(Appointment.patient_id == patient_id)
        if slot_id is not None:
            filters.append(Appointment.slot_id == slot_id)

        data_stmt = select(Appointment)
        if target_date is not None:
            clinic_tz = ZoneInfo("Asia/Kathmandu")
            start_at = datetime.combine(target_date, time.min, tzinfo=clinic_tz).astimezone(UTC)
            end_at = datetime.combine(target_date + timedelta(days=1), time.min, tzinfo=clinic_tz).astimezone(
                UTC
            )
            data_stmt = data_stmt.outerjoin(AppointmentSlot, Appointment.slot_id == AppointmentSlot.id)
            filters.append(
                or_(
                    (AppointmentSlot.start_at >= start_at) & (AppointmentSlot.start_at < end_at),
                    (Appointment.slot_id.is_(None))
                    & (Appointment.created_at >= start_at)
                    & (Appointment.created_at < end_at),
                )
            )

        count_stmt = select(func.count()).select_from(Appointment)
        if target_date is not None:
            count_stmt = count_stmt.outerjoin(AppointmentSlot, Appointment.slot_id == AppointmentSlot.id)
        count_stmt = count_stmt.where(*filters)
        total: int = (await db.execute(count_stmt)).scalar_one()

        data_stmt = (
            data_stmt.where(*filters)
            .options(
                selectinload(Appointment.patient),
                selectinload(Appointment.slot),
            )
            .order_by(Appointment.created_at.desc())
            .offset(offset)
            .limit(limit)
        )
        res = await db.execute(data_stmt)
        return list(res.scalars().all()), total

    async def create(
        self,
        db: AsyncSession,
        data: AppointmentCreate,
        initial_status: AppointmentStatus,
    ) -> Appointment:
        """Create and persist an appointment."""
        apt = Appointment(
            lab_id=data.lab_id,
            patient_id=data.patient_id,
            slot_id=data.slot_id,
            status=initial_status,
            booked_via=data.booked_via,
            contact_name=data.contact_name,
            contact_phone=data.contact_phone,
            notes=data.notes,
        )
        db.add(apt)
        await db.flush()
        await db.refresh(apt)
        return apt

    async def update(
        self, db: AsyncSession, appointment: Appointment, data: AppointmentUpdate
    ) -> Appointment:
        """Update appointment fields."""
        if data.slot_id is not None:
            appointment.slot_id = data.slot_id
        if data.contact_name is not None:
            appointment.contact_name = data.contact_name
        if data.contact_phone is not None:
            appointment.contact_phone = data.contact_phone
        if data.notes is not None:
            appointment.notes = data.notes

        await db.flush()
        await db.refresh(appointment)
        return appointment

    async def update_status(
        self, db: AsyncSession, appointment: Appointment, new_status: AppointmentStatus
    ) -> Appointment:
        """Transition appointment lifecycle status."""
        appointment.status = new_status
        await db.flush()
        await db.refresh(appointment)
        return appointment