""" Patient repository for database operations. """ from datetime import UTC, date, datetime, time, timedelta from uuid import UUID from zoneinfo import ZoneInfo from sqlalchemy import ColumnElement, and_, func, or_, select, text from sqlalchemy.ext.asyncio import AsyncSession from app.models.appointment import Appointment, AppointmentSlot, AppointmentStatus from app.models.order import VisitOrder from app.models.patient import Patient from app.schemas.patient import PatientCreate, PatientUpdate class PatientRepository: """Repository handling Patient persistence and queries.""" async def get_by_id( self, db: AsyncSession, patient_id: UUID, lab_id: UUID | None = None ) -> Patient | None: """Fetch a non-deleted patient by their primary key.""" stmt = select(Patient).where(Patient.id == patient_id, Patient.deleted_at.is_(None)) if lab_id is not None: stmt = stmt.where(Patient.lab_id == lab_id) res = await db.execute(stmt) return res.scalar_one_or_none() async def list_by_phone(self, db: AsyncSession, phone: str) -> list[Patient]: """Fetch non-deleted patients sharing a phone number (across branches).""" stmt = select(Patient).where( Patient.phone == phone, Patient.deleted_at.is_(None), ) res = await db.execute(stmt) return list(res.scalars().all()) async def get_by_lab_and_phone(self, db: AsyncSession, lab_id: UUID, phone: str) -> Patient | None: """Fetch a non-deleted patient by lab ID and phone number.""" stmt = select(Patient).where( Patient.lab_id == lab_id, Patient.phone == phone, Patient.deleted_at.is_(None), ) res = await db.execute(stmt) return res.scalar_one_or_none() async def get_next_patient_code(self, db: AsyncSession, lab_id: UUID) -> str: """ Generate the next year-aware sequential patient code for a lab. Format: P{YY}-{4-digit-seq} e.g. P26-0001, P26-0002 """ year_short = datetime.now(UTC).strftime("%y") prefix = f"P{year_short}-" # Serialize branch/year allocations until the service commits the row. await db.execute( text("SELECT pg_advisory_xact_lock(hashtext(:key))"), {"key": f"patient-code:{lab_id}:{year_short}"}, ) stmt = ( select(Patient.patient_code) .where( Patient.lab_id == lab_id, Patient.patient_code.like(f"{prefix}%"), ) .order_by(Patient.patient_code.desc()) .limit(1) ) res = await db.execute(stmt) last_code = res.scalar_one_or_none() next_seq = 1 if last_code and last_code.startswith(prefix): try: part = last_code[len(prefix) :] next_seq = int(part) + 1 except (ValueError, IndexError): next_seq = 1 return f"{prefix}{next_seq:04d}" async def list_patients( self, db: AsyncSession, limit: int = 50, offset: int = 0, lab_id: UUID | None = None, q: str | None = None, created_from: datetime | None = None, created_to: datetime | None = None, activity_date: date | None = None, ) -> tuple[list[Patient], int]: """Fetch paginated patients with optional search and branch filtering.""" base_filters: list[ColumnElement[bool]] = [Patient.deleted_at.is_(None)] if lab_id is not None: base_filters.append(Patient.lab_id == lab_id) if created_from is not None and activity_date is None: base_filters.append(Patient.created_at >= created_from) if created_to is not None and activity_date is None: base_filters.append(Patient.created_at < created_to) if activity_date is not None: # The desk's operational day is the clinic's Nepal-local calendar day. clinic_tz = ZoneInfo("Asia/Kathmandu") day_start = datetime.combine(activity_date, time.min, tzinfo=clinic_tz).astimezone(UTC) next_day = datetime.combine(activity_date, time.min, tzinfo=clinic_tz) + timedelta(days=1) day_end = next_day.astimezone(UTC) registration_filters = [Patient.created_at >= day_start, Patient.created_at < day_end] if created_from is not None: registration_filters.append(Patient.created_at >= created_from) if created_to is not None: registration_filters.append(Patient.created_at < created_to) registered_today = and_(*registration_filters) ordered_today = ( select(VisitOrder.id) .where( VisitOrder.patient_id == Patient.id, VisitOrder.lab_id == Patient.lab_id, VisitOrder.deleted_at.is_(None), VisitOrder.created_at >= day_start, VisitOrder.created_at < day_end, ) .exists() ) active_appointment_today = ( select(Appointment.id) .outerjoin(AppointmentSlot, Appointment.slot_id == AppointmentSlot.id) .where( Appointment.patient_id == Patient.id, Appointment.lab_id == Patient.lab_id, Appointment.deleted_at.is_(None), Appointment.status.in_( (AppointmentStatus.PENDING, AppointmentStatus.CONFIRMED, AppointmentStatus.CHECKED_IN) ), or_( (AppointmentSlot.start_at >= day_start) & (AppointmentSlot.start_at < day_end), (Appointment.slot_id.is_(None)) & (Appointment.created_at >= day_start) & (Appointment.created_at < day_end), ), ) .exists() ) base_filters.append(or_(registered_today, ordered_today, active_appointment_today)) if q: search_term = f"%{q.strip()}%" base_filters.append( or_( Patient.name.ilike(search_term), Patient.phone.ilike(search_term), Patient.patient_code.ilike(search_term), ) ) count_stmt = select(func.count()).select_from(Patient).where(*base_filters) total: int = (await db.execute(count_stmt)).scalar_one() data_stmt = ( select(Patient) .where(*base_filters) .order_by(Patient.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: PatientCreate, patient_code: str) -> Patient: """Create and persist a new patient record.""" patient = Patient( lab_id=data.lab_id, patient_code=patient_code, name=data.name, gender=data.gender, phone=data.phone, date_of_birth=data.date_of_birth, age=data.age, email=data.email, address=data.address, country=data.country, height_cm=data.height_cm, weight_kg=data.weight_kg, ) db.add(patient) await db.flush() await db.refresh(patient) return patient async def update(self, db: AsyncSession, patient: Patient, data: PatientUpdate) -> Patient: """Update fields on an existing patient record.""" if data.name is not None: patient.name = data.name if data.gender is not None: patient.gender = data.gender if data.phone is not None: patient.phone = data.phone if data.date_of_birth is not None: patient.date_of_birth = data.date_of_birth if data.age is not None: patient.age = data.age if data.email is not None: patient.email = data.email if data.address is not None: patient.address = data.address if data.country is not None: patient.country = data.country if data.height_cm is not None: patient.height_cm = data.height_cm if data.weight_kg is not None: patient.weight_kg = data.weight_kg await db.flush() await db.refresh(patient) return patient async def delete(self, db: AsyncSession, patient: Patient) -> None: """Soft-delete a patient record.""" patient.deleted_at = datetime.now(UTC) await db.flush()