app/db/models.py

"""SQLAlchemy ORM models for tracking documents, reports, and sections."""

import enum
import uuid
from datetime import UTC, datetime

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

from app.db.database import Base


def _utcnow() -> datetime:
    return datetime.now(UTC)


class IngestStatus(enum.StrEnum):
    pending = "pending"
    processing = "processing"
    complete = "complete"
    failed = "failed"


class ReportStatus(enum.StrEnum):
    pending = "pending"
    generating = "generating"
    complete = "complete"
    partial = "partial"
    failed = "failed"


class Document(Base):
    """Represents an uploaded file and its ingestion lifecycle."""

    __tablename__ = "documents"

    id: Mapped[str] = mapped_column(
        String(36), primary_key=True, default=lambda: str(uuid.uuid4())
    )
    tenant_id: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    filename: Mapped[str] = mapped_column(String(512), nullable=False)
    file_path: Mapped[str] = mapped_column(String(1024), nullable=False)
    status: Mapped[IngestStatus] = mapped_column(
        Enum(IngestStatus), default=IngestStatus.pending
    )
    error_message: Mapped[str | None] = mapped_column(Text, nullable=True)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=_utcnow
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=_utcnow, onupdate=_utcnow
    )
    # RICS Home Survey product tier for this upload: 1 = Condition Report, 2 = HomeBuyer, 3 = Building Survey.
    survey_level: Mapped[int | None] = mapped_column(Integer, nullable=True)

    reports: Mapped[list["Report"]] = relationship(
        "Report", back_populates="document"
    )


class Report(Base):
    """A generation job tied to a document."""

    __tablename__ = "reports"

    id: Mapped[str] = mapped_column(
        String(36), primary_key=True, default=lambda: str(uuid.uuid4())
    )
    tenant_id: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    document_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("documents.id"), nullable=False
    )
    status: Mapped[ReportStatus] = mapped_column(
        Enum(ReportStatus), default=ReportStatus.pending
    )
    error_message: Mapped[str | None] = mapped_column(Text, nullable=True)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=_utcnow
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=_utcnow, onupdate=_utcnow
    )
    generation_started_at: Mapped[datetime | None] = mapped_column(
        DateTime(timezone=True),
        nullable=True,
        default=None,
        doc="Set when status flips to generating; used by stale-job sweeper.",
    )
    # Survey product tier this report job uses for library RAG filtering (may override the primary document).
    survey_level: Mapped[int | None] = mapped_column(Integer, nullable=True)

    document: Mapped[Document] = relationship("Document", back_populates="reports")
    sections: Mapped[list["ReportSection"]] = relationship(
        "ReportSection", back_populates="report", cascade="all, delete-orphan"
    )


class ReportSection(Base):
    """A single adapted section within a generated report."""

    __tablename__ = "report_sections"
    __table_args__ = (
        # Speeds up the upsert lookup in _persist_section and the GET /sections query.
        Index("ix_report_sections_report_section", "report_id", "section_code", unique=True),
    )

    id: Mapped[str] = mapped_column(
        String(36), primary_key=True, default=lambda: str(uuid.uuid4())
    )
    report_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("reports.id"), nullable=False
    )
    section_code: Mapped[str] = mapped_column(String(64), nullable=False)
    text: Mapped[str] = mapped_column(Text, nullable=False)
    confidence: Mapped[float] = mapped_column(default=0.0)
    provenance: Mapped[str] = mapped_column(Text, default="[]")
    cached: Mapped[bool] = mapped_column(default=False)

    report: Mapped[Report] = relationship("Report", back_populates="sections")


class ReportSectionPhoto(Base):
    """A photo uploaded for a specific report section (evidence for generation + export)."""

    __tablename__ = "report_section_photos"
    __table_args__ = (
        Index("ix_rsp_report_section", "report_id", "section_code"),
        Index("ix_rsp_tenant_report", "tenant_id", "report_id"),
    )

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    tenant_id: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
    report_id: Mapped[str] = mapped_column(String(36), ForeignKey("reports.id"), nullable=False, index=True)
    section_code: Mapped[str] = mapped_column(String(64), nullable=False, index=True)

    file_path: Mapped[str] = mapped_column(String(1024), nullable=False)
    original_filename: Mapped[str] = mapped_column(String(512), nullable=False)
    content_type: Mapped[str] = mapped_column(String(128), nullable=False)

    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=_utcnow)