operating-room-monitor-server/app/db/models.py

from __future__ import annotations

from datetime import datetime

from sqlalchemy import DateTime, ForeignKey, Integer, String, Text, func
from sqlalchemy.orm import Mapped, mapped_column, relationship

from app.db.base import Base


class SurgeryFinalResult(Base):
    """一台手术结束后的最终结果元数据（明细在子表）。"""

    __tablename__ = "surgery_final_results"

    surgery_id: Mapped[str] = mapped_column(String(6), primary_key=True)
    completed_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), nullable=False
    )
    details: Mapped[list["SurgeryResultDetailRow"]] = relationship(
        "SurgeryResultDetailRow",
        back_populates="surgery",
        cascade="all, delete-orphan",
        passive_deletes=True,
    )


class SurgeryResultDetailRow(Base):
    """客户端查询用的耗材消耗明细行。"""

    __tablename__ = "surgery_result_details"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    surgery_id: Mapped[str] = mapped_column(
        String(6),
        ForeignKey("surgery_final_results.surgery_id", ondelete="CASCADE"),
        index=True,
        nullable=False,
    )
    item_id: Mapped[str] = mapped_column(String(256), nullable=False)
    item_name: Mapped[str] = mapped_column(String(256), nullable=False)
    quantity: Mapped[int] = mapped_column(Integer, nullable=False)
    doctor_id: Mapped[str] = mapped_column(String(128), nullable=False)
    recorded_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    #: 可选：来源标记，如 vision / voice；便于排查，客户端可忽略。
    source: Mapped[str] = mapped_column(String(32), default="vision", nullable=False)

    surgery: Mapped["SurgeryFinalResult"] = relationship(
        "SurgeryFinalResult", back_populates="details"
    )


class VoiceConfirmationAudit(Base):
    """医生语音确认上传：原始音频对象键、ASR 文本与解析结果（追溯）。"""

    __tablename__ = "voice_confirmation_audits"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    surgery_id: Mapped[str] = mapped_column(String(6), index=True, nullable=False)
    confirmation_id: Mapped[str] = mapped_column(String(128), index=True, nullable=False)
    #: recognized | rejected | asr_failed | parse_failed | invalid_audio | upload_failed
    #: | client_stt_empty | client_stt_parse_failed
    status: Mapped[str] = mapped_column(String(32), nullable=False)
    audio_object_key: Mapped[str | None] = mapped_column(String(512), nullable=True)
    audio_content_type: Mapped[str | None] = mapped_column(String(128), nullable=True)
    audio_size_bytes: Mapped[int | None] = mapped_column(Integer, nullable=True)
    audio_sha256: Mapped[str | None] = mapped_column(String(64), nullable=True)
    asr_text: Mapped[str | None] = mapped_column(String(2048), nullable=True)
    resolved_label: Mapped[str | None] = mapped_column(String(256), nullable=True)
    options_snapshot_json: Mapped[str | None] = mapped_column(Text, nullable=True)
    error_message: Mapped[str | None] = mapped_column(String(1024), nullable=True)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), nullable=False
    )