from datetime import datetime

from sqlalchemy import String, Integer, Float, DateTime, ForeignKey
from sqlalchemy.orm import Mapped, mapped_column

from backend.database.connection import Base


class ProductSnapshot(Base):
    """Track product metrics over time for revenue calculation."""
    __tablename__ = "product_snapshots"

    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    product_id: Mapped[str] = mapped_column(String(100), index=True)
    sold_count: Mapped[int] = mapped_column(Integer, default=0)
    stock: Mapped[int] = mapped_column(Integer, default=0)
    price: Mapped[str] = mapped_column(String(50), default="")
    rating: Mapped[float] = mapped_column(Float, default=0.0)
    review_count: Mapped[int] = mapped_column(Integer, default=0)
    captured_at: Mapped[datetime] = mapped_column(
        DateTime, default=datetime.utcnow, index=True
    )

    def __repr__(self):
        return f"<Snapshot {self.product_id} sold={self.sold_count} @ {self.captured_at}>"

    def to_dict(self) -> dict:
        return {
            "id": self.id,
            "product_id": self.product_id,
            "sold_count": self.sold_count,
            "stock": self.stock,
            "price": self.price,
            "rating": self.rating,
            "review_count": self.review_count,
            "captured_at": self.captured_at.isoformat() if self.captured_at else None,
        }
