from uuid import UUID

from sqlalchemy import BigInteger, Boolean, ForeignKey, Index, Integer, String, Text, text
from sqlalchemy.dialects.postgresql import UUID as PG_UUID
from sqlalchemy.orm import Mapped, mapped_column, relationship

from src.database.base import TenantSoftDeleteModel


class Document(TenantSoftDeleteModel):
    """Tenant-scoped document with S3 storage and soft delete."""

    __tablename__ = "documents"

    # Override tenant_id from TenantMixin to add an explicit FK
    tenant_id: Mapped[UUID] = mapped_column(
        PG_UUID(as_uuid=True),
        ForeignKey("accounts.id", ondelete="CASCADE"),
        nullable=False,
        index=False,  # rely on partial index in __table_args__ instead
    )

    entity_type: Mapped[str] = mapped_column(String(50), nullable=False)
    entity_id: Mapped[UUID] = mapped_column(PG_UUID(as_uuid=True), nullable=False)
    s3_key: Mapped[str] = mapped_column(Text(), nullable=False)
    filename: Mapped[str] = mapped_column(String(500), nullable=False)
    file_size_bytes: Mapped[int | None] = mapped_column(BigInteger(), nullable=True)
    mime_type: Mapped[str | None] = mapped_column(String(100), nullable=True)
    category: Mapped[str | None] = mapped_column(String(100), nullable=True)
    version: Mapped[int] = mapped_column(
        Integer(), nullable=False, server_default=text("1")
    )
    uploaded_by: Mapped[UUID] = mapped_column(
        PG_UUID(as_uuid=True),
        nullable=False,
    )
    upload_confirmed: Mapped[bool] = mapped_column(
        Boolean(), nullable=False, server_default=text("false")
    )

    # Relationship to uploader (lazy=noload — never eagerly loaded)
    uploaded_by_user = relationship(
        "User",
        foreign_keys=[uploaded_by],
        primaryjoin="Document.uploaded_by == User.id",
        lazy="noload",
    )

    __table_args__ = (
        Index(
            "ix_documents_entity",
            "tenant_id",
            "entity_type",
            "entity_id",
            postgresql_where=text("deleted_at IS NULL"),
        ),
        Index(
            "ix_documents_tenant",
            "tenant_id",
            postgresql_where=text("deleted_at IS NULL"),
        ),
        Index(
            "ix_documents_pending",
            "upload_confirmed",
            "created_at",
            postgresql_where=text("upload_confirmed IS FALSE"),
        ),
    )