""" Repository for visit orders, order tests, and order packages. """ from datetime import UTC, datetime from uuid import UUID from sqlalchemy import ColumnElement, and_, func, or_, select, text from sqlalchemy.ext.asyncio import AsyncSession from sqlalchemy.orm import load_only, raiseload, selectinload from app.models.billing import Bill from app.models.order import OrderPackage, OrderStatus, OrderTest, VisitOrder from app.models.patient import Patient from app.models.test import Test, TestParameter from app.schemas.order import OrderUpdate class OrderRepository: """Repository handling database operations for VisitOrder.""" async def get_next_order_number(self, db: AsyncSession, lab_id: UUID) -> str: """ Generate year-aware sequential order number for a lab. Format: ORD{YY}{6-digit-seq} e.g. ORD260001 (no separators — easy to type). Rows minted under the legacy ORD/{YY}/ format keep working (exact/partial lookups are format-agnostic); the new format simply starts its own sequence. """ year_short = datetime.now(UTC).strftime("%y") prefix = f"ORD{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"order-code:{lab_id}:{year_short}"}, ) stmt = ( select(VisitOrder.order_number) .where( VisitOrder.lab_id == lab_id, VisitOrder.order_number.like(f"{prefix}%"), ) .order_by(VisitOrder.order_number.desc()) .limit(1) ) res = await db.execute(stmt) last_num = res.scalar_one_or_none() next_seq = 1 if last_num: try: next_seq = int(last_num[len(prefix) :]) + 1 except (ValueError, IndexError): next_seq = 1 return f"{prefix}{next_seq:06d}" async def get_by_id( self, db: AsyncSession, order_id: UUID, lab_id: UUID | None = None ) -> VisitOrder | None: """Fetch an order with all items and patient loaded.""" stmt = ( select(VisitOrder) .where(VisitOrder.id == order_id, VisitOrder.deleted_at.is_(None)) .options( selectinload(VisitOrder.patient), selectinload(VisitOrder.order_tests) .selectinload(OrderTest.test) .selectinload(Test.parameters) .selectinload(TestParameter.ranges), selectinload(VisitOrder.order_tests).selectinload(OrderTest.test).selectinload(Test.category), selectinload(VisitOrder.order_packages).selectinload(OrderPackage.package), ) ) if lab_id is not None: stmt = stmt.where(VisitOrder.lab_id == lab_id) res = await db.execute(stmt) return res.scalar_one_or_none() async def get_by_number( self, db: AsyncSession, order_number: str, lab_id: UUID | None = None, phone_digits: str | None = None ) -> VisitOrder | None: """Fetch a non-deleted order by order number (case-insensitive) across branches.""" stmt = ( select(VisitOrder) .where( func.lower(VisitOrder.order_number) == order_number.strip().lower(), VisitOrder.deleted_at.is_(None), ) .options( selectinload(VisitOrder.patient), selectinload(VisitOrder.order_tests) .selectinload(OrderTest.test) .selectinload(Test.parameters) .selectinload(TestParameter.ranges), selectinload(VisitOrder.order_packages).selectinload(OrderPackage.package), selectinload(VisitOrder.samples), ) ) if phone_digits is not None: normalized_phone = func.regexp_replace(Patient.phone, "[^0-9]", "", "g") stmt = stmt.join(Patient, VisitOrder.patient_id == Patient.id).where( normalized_phone.in_([phone_digits, "977" + phone_digits, "00977" + phone_digits]), Patient.deleted_at.is_(None), ) if lab_id is not None: stmt = stmt.where(VisitOrder.lab_id == lab_id) res = await db.execute(stmt.limit(2)) matches = res.scalars().all() # A branch-local identifier cannot select arbitrarily across branches. return matches[0] if len(matches) == 1 else None async def list_orders( self, db: AsyncSession, limit: int = 50, offset: int = 0, lab_id: UUID | None = None, status: OrderStatus | None = None, patient_id: UUID | None = None, is_abroad: bool | None = None, destination_country: str | None = None, q: str | None = None, created_from: datetime | None = None, created_to: datetime | None = None, ) -> tuple[list[VisitOrder], int]: """Fetch paginated orders.""" filters: list[ColumnElement[bool]] = [VisitOrder.deleted_at.is_(None)] if lab_id is not None: filters.append(VisitOrder.lab_id == lab_id) if status is not None: filters.append(VisitOrder.status == status) if patient_id is not None: filters.append(VisitOrder.patient_id == patient_id) if is_abroad is True: filters.append( and_(VisitOrder.destination_country.isnot(None), VisitOrder.destination_country != "") ) elif is_abroad is False: filters.append( or_(VisitOrder.destination_country.is_(None), VisitOrder.destination_country == "") ) if destination_country: filters.append(VisitOrder.destination_country.ilike(f"%{destination_country.strip()}%")) if created_from is not None: filters.append(VisitOrder.created_at >= created_from) if created_to is not None: filters.append(VisitOrder.created_at < created_to) if q: term = f"%{q.strip()}%" filters.append( or_( VisitOrder.order_number.ilike(term), VisitOrder.referred_by_custom.ilike(term), VisitOrder.destination_country.ilike(term), ) ) count_stmt = select(func.count()).select_from(VisitOrder).where(*filters) total: int = (await db.execute(count_stmt)).scalar_one() data_stmt = ( select(VisitOrder) .where(*filters) .options( selectinload(VisitOrder.patient).options( raiseload(Patient.lab), ), selectinload(VisitOrder.bill).options( load_only(Bill.id, Bill.order_id, Bill.bill_number, Bill.status), raiseload(Bill.lab), raiseload(Bill.order), raiseload(Bill.patient), raiseload(Bill.discount_rule), ), selectinload(VisitOrder.order_tests).options( load_only(OrderTest.id, OrderTest.order_id, OrderTest.price_at_order, OrderTest.status), raiseload(OrderTest.order), raiseload(OrderTest.test), ), selectinload(VisitOrder.order_packages).options( load_only(OrderPackage.id, OrderPackage.order_id, OrderPackage.price_at_order), raiseload(OrderPackage.order), raiseload(OrderPackage.package), ), raiseload(VisitOrder.lab), raiseload(VisitOrder.appointment), raiseload(VisitOrder.referral), raiseload(VisitOrder.samples), ) .order_by(VisitOrder.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, order: VisitOrder, tests: list[OrderTest], packages: list[OrderPackage], ) -> VisitOrder: """Persist a new order with its test and package items.""" order.order_tests = tests order.order_packages = packages db.add(order) await db.flush() return order async def update(self, db: AsyncSession, order: VisitOrder, data: OrderUpdate) -> VisitOrder: """Update order fields.""" if data.referred_by_custom is not None: order.referred_by_custom = data.referred_by_custom if data.next_date is not None: order.next_date = data.next_date if data.destination_country is not None: order.destination_country = data.destination_country if data.notes is not None: order.notes = data.notes if data.status is not None: order.status = data.status order.updated_at = datetime.now(UTC) await db.flush() return order