from datetime import datetime
from sqlalchemy import Boolean, DateTime, String, Numeric, Integer, func
from sqlalchemy.orm import Mapped, mapped_column, relationship

from app.db.base import Base


class Product(Base):
    __tablename__ = "products"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, index=True)
    reference_id: Mapped[int] = mapped_column(Integer, nullable=False)
    product_name: Mapped[str] = mapped_column(String(255), nullable=False, index=True)
    barcode: Mapped[str] = mapped_column(String(14), unique=True, index=True, nullable=False)
    msp: Mapped[float] = mapped_column(Numeric(10, 2), nullable=False)
    # Pack-based violation pricing thresholds
    price_1_pack: Mapped[float] = mapped_column(Numeric(10, 2), nullable=False, default=0)
    price_2_pack: Mapped[float] = mapped_column(Numeric(10, 2), nullable=False, default=0)
    price_3_pack: Mapped[float] = mapped_column(Numeric(10, 2), nullable=False, default=0)
    price_4_pack: Mapped[float] = mapped_column(Numeric(10, 2), nullable=False, default=0)
    price_5_pack: Mapped[float] = mapped_column(Numeric(10, 2), nullable=False, default=0)
    price_6_pack: Mapped[float] = mapped_column(Numeric(10, 2), nullable=False, default=0)
    price_12_pack: Mapped[float] = mapped_column(Numeric(10, 2), nullable=False, default=0)
    status: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False)
    last_scraped_date: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True, index=True)
    last_execution_time: Mapped[str | None] = mapped_column(String(255), nullable=True)

    scraping_status: Mapped[str] = mapped_column(String(50), default="idle")

    scraping_started_at: Mapped[datetime | None] = mapped_column(
        DateTime(timezone=True), nullable=True
    )

    scraping_completed_at: Mapped[datetime | None] = mapped_column(
        DateTime(timezone=True), nullable=True
    )

    scraping_error: Mapped[str | None] = mapped_column(
        String(500), nullable=True
    )

    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now()
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        nullable=False,
        server_default=func.now(),
        onupdate=func.now(),
    )

    # Relationship with violations - cascade delete when product is deleted
    violations = relationship("Violation", back_populates="product", cascade="all, delete-orphan")