import uuid
from datetime import datetime
from sqlalchemy import Boolean, DateTime, ForeignKey, String, Text, func
from sqlalchemy.dialects.postgresql import JSON, UUID
from sqlalchemy.orm import Mapped, mapped_column

from api.models.base import Base


class DOICache(Base):
    __tablename__ = "doi_cache"

    id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    repo_id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("managed_repos.id", ondelete="CASCADE"),
        nullable=False, unique=True, index=True,
    )
    tier: Mapped[str] = mapped_column(String(20), nullable=False)
    core_pass: Mapped[bool] = mapped_column(Boolean, nullable=False, server_default="false")
    standard_pass: Mapped[bool] = mapped_column(Boolean, nullable=False, server_default="false")
    full_pass: Mapped[bool] = mapped_column(Boolean, nullable=False, server_default="false")
    criteria: Mapped[list | None] = mapped_column(JSON, nullable=True)
    # Pipe-joined string of timestamps/mtimes used to detect staleness
    fingerprint: Mapped[str] = mapped_column(Text, nullable=False)
    checked_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())