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 DocumentPurpose(enum.StrEnum):
    """Why a tenant uploaded this document.

    ``report_source`` — the document is factual input for a specific report
    job (the user's current RICS survey upload, or a reference document
    attached as additional evidence). Chunks from these docs are valid as
    *facts* in RAG retrieval for any report this tenant runs.

    ``style_corpus`` — the document is one of the user's PAST completed
    reports, sanitised of PII and stored purely for style learning. Chunks
    from these docs are deliberately EXCLUDED from factual retrieval (so
    observations from a different property cannot leak into a new report)
    but ARE used to:
      * Build the per-tenant ``WritingStyleProfile``.
      * Provide verbatim example paragraphs for few-shot style mirroring.

    This split is what gives each user a private, personalised "AI writing
    assistant" that learns *their* voice from *their* prior work without
    contaminating new reports with facts from old jobs.
    """

    report_source = "report_source"
    style_corpus = "style_corpus"


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)
    # Why this document exists in the tenant's library. ``report_source``
    # docs supply facts; ``style_corpus`` docs supply voice/style only.
    document_purpose: Mapped[DocumentPurpose] = mapped_column(
        Enum(DocumentPurpose),
        default=DocumentPurpose.report_source,
        nullable=False,
        server_default=DocumentPurpose.report_source.value,
        index=True,
    )
    # Redaction engine selected at upload time (see RedactionStrategy in document_sanitiser).
    redaction_strategy: Mapped[str] = mapped_column(
        String(32),
        nullable=False,
        default="ai_hybrid",
        server_default="ai_hybrid",
    )
    # JSON map of session PII strings for deterministic exact-string wipe at ingest.
    redaction_context_json: Mapped[str | None] = mapped_column(Text, 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)
    # Nullable so a finished report's source document can be deleted: on delete
    # the referencing terminal reports are detached (document_id -> NULL) rather
    # than blocking removal. Active jobs (pending/generating) still block deletion.
    document_id: Mapped[str | None] = mapped_column(
        String(36), ForeignKey("documents.id"), nullable=True
    )
    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.",
    )
    generation_section_total: Mapped[int | None] = mapped_column(
        Integer,
        nullable=True,
        default=None,
        doc="Expected section count for the active multi-section job (status polling).",
    )
    # 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)
    # When true, this photo is sent to vision/LLM during section generation (max 2 per section).
    selected_for_ai: Mapped[bool] = mapped_column(default=False)

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


class Tenant(Base):
    """An authenticated tenant (user/workspace) for access control + isolation.

    The ``id`` is the tenant identifier carried on every request as the
    cryptographically-verified subject of a signed bearer token. The passphrase
    is never stored — only a PBKDF2-HMAC-SHA256 hash and its per-tenant salt.
    """

    __tablename__ = "tenants"

    id: Mapped[str] = mapped_column(String(128), primary_key=True)
    password_hash: Mapped[str] = mapped_column(String(256), nullable=False)
    password_salt: Mapped[str] = mapped_column(String(64), nullable=False)
    pbkdf2_iterations: Mapped[int] = mapped_column(Integer, nullable=False, default=200_000)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=_utcnow)
    last_login_at: Mapped[datetime | None] = mapped_column(
        DateTime(timezone=True), nullable=True, default=None
    )