"""Doctor master data and doctor → MR assignment. Master data belongs to a HEADQUARTERS, not to an MR (A2). Assignment is a separate, dated table so that when an MR leaves, the doctor list survives. `core_doctor_conversion` was not created as a table (report §13.2): it would store one enum per call, which is a column — `doctor.is_core_doctor` + `doctor_call.conversion_status`. """ import uuid from datetime import date from sqlalchemy import Boolean, Date, ForeignKey, Index, String, UniqueConstraint, text from sqlalchemy.dialects.postgresql import UUID as PGUUID from sqlalchemy.orm import Mapped, mapped_column, relationship from app.common.enums import MasterApproval from app.database.base import Base, TimestampMixin, UUIDMixin class Doctor(Base, UUIDMixin, TimestampMixin): __tablename__ = "doctor" __table_args__ = ( # Deduplication guard: two MRs must not create two records for one doctor. UniqueConstraint( "headquarters_id", "name", "clinic_address", name="uq_doctor_hq_name_clinic" ), ) headquarters_id: Mapped[uuid.UUID] = mapped_column( PGUUID(as_uuid=True), ForeignKey("headquarters.id", ondelete="RESTRICT"), nullable=False ) name: Mapped[str] = mapped_column(String(200), nullable=False) speciality: Mapped[str | None] = mapped_column(String(100)) qualification: Mapped[str | None] = mapped_column(String(100)) clinic_address: Mapped[str] = mapped_column(String(500), nullable=False) contact_number: Mapped[str | None] = mapped_column(String(20)) date_of_birth: Mapped[date | None] = mapped_column(Date) #: Core doctors are the high-value targets tracked for conversion. is_core_doctor: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False) is_active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False) #: PENDING until the Area Manager approves one an MR added; only APPROVED ones are listed or visited. approval_status: Mapped[str] = mapped_column( String(10), default=MasterApproval.APPROVED.value, server_default=MasterApproval.APPROVED.value, nullable=False ) #: The MR who asked for it (None for one the administrator created). requested_by_user_id: Mapped[uuid.UUID | None] = mapped_column( PGUUID(as_uuid=True), ForeignKey("user.id", ondelete="SET NULL") ) rejection_reason: Mapped[str | None] = mapped_column(String(500)) requested_by: Mapped["User | None"] = relationship( # noqa: F821 lazy="selectin", foreign_keys=[requested_by_user_id] ) @property def requested_by_name(self) -> str | None: return self.requested_by.full_name if self.requested_by else None assignments: Mapped[list["DoctorAssignment"]] = relationship(back_populates="doctor") class DoctorAssignment(Base, UUIDMixin, TimestampMixin): __tablename__ = "doctor_assignment" __table_args__ = ( Index( "uq_active_doctor_assignment", "doctor_id", unique=True, postgresql_where=text("effective_to IS NULL"), ), ) doctor_id: Mapped[uuid.UUID] = mapped_column( PGUUID(as_uuid=True), ForeignKey("doctor.id", ondelete="CASCADE"), nullable=False ) mr_user_id: Mapped[uuid.UUID] = mapped_column( PGUUID(as_uuid=True), ForeignKey("user.id", ondelete="RESTRICT"), nullable=False ) effective_from: Mapped[date] = mapped_column(Date, nullable=False) effective_to: Mapped[date | None] = mapped_column(Date) doctor: Mapped["Doctor"] = relationship(back_populates="assignments")