from sqlalchemy import Column, Integer, String, Float, DateTime, ForeignKey, func, UniqueConstraint, Index
from sqlalchemy.orm import relationship

from app.database import Base


class Image(Base):
    __tablename__ = "images"

    id = Column(Integer, primary_key=True, index=True)
    species_id = Column(Integer, ForeignKey("species.id"), nullable=False, index=True)
    source = Column(String, nullable=False, index=True)
    source_id = Column(String, nullable=True)
    url = Column(String, nullable=False)
    local_path = Column(String, nullable=True)
    license = Column(String, nullable=False, index=True)
    attribution = Column(String, nullable=True)
    width = Column(Integer, nullable=True)
    height = Column(Integer, nullable=True)
    phash = Column(String, nullable=True, index=True)
    quality_score = Column(Float, nullable=True)
    status = Column(String, default="pending", index=True)  # pending, downloaded, rejected, deleted
    created_at = Column(DateTime, server_default=func.now())

    # Composite indexes for common query patterns
    __table_args__ = (
        UniqueConstraint("source", "source_id", name="uq_source_source_id"),
        Index("ix_images_species_status", "species_id", "status"),  # For counting images per species by status
        Index("ix_images_status_created", "status", "created_at"),  # For listing images by status
    )

    # Relationships
    species = relationship("Species", back_populates="images")

    def __repr__(self):
        return f"<Image(id={self.id}, source='{self.source}', status='{self.status}')>"