import uuid
from datetime import datetime

from sqlalchemy import DateTime, String, Numeric, ForeignKey, func, Integer
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.orm import Mapped, mapped_column, relationship

from app.db.base import Base


class Violation(Base):
    __tablename__ = "violations"

    id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    vendor_id: Mapped[uuid.UUID | None] = mapped_column(UUID(as_uuid=True), ForeignKey("vendors.id"), nullable=True, index=True)
    product_name: Mapped[str] = mapped_column(String(1024), nullable=False)
    msp: Mapped[float] = mapped_column(Numeric, nullable=False)
    scraped_price: Mapped[float] = mapped_column(Numeric, nullable=False)
    violation_date: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    url: Mapped[str] = mapped_column(String(2048), nullable=False)

    # additional metadata
    barcode_number: Mapped[str | None] = mapped_column(String(255), nullable=True)
    reference_id: Mapped[str | None] = mapped_column(String(255), nullable=True)
    
    # Price tracking
    price_difference: Mapped[float | None] = mapped_column(Numeric, nullable=True)
    percentage_difference: Mapped[float | None] = mapped_column(Numeric, nullable=True)
    
    # Track confirmation count
    confirmation_count: Mapped[int] = mapped_column(Integer, nullable=False, default=1)
    
    # Track source of violation
    source_type: Mapped[str] = mapped_column(String(50), nullable=False, default="registered")  # 'registered', 'discovered'
    
    # Track last confirmed violation date
    last_confirmed_date: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    
    # Track notification status
    notification_sent_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    
    # Vendor name (for discovered vendors or additional context)
    vendor_name: Mapped[str | None] = mapped_column(String(512), nullable=True)
    
    # Marketplace (e.g., Amazon, eBay, Flipkart, etc.)
    marketplace: Mapped[str | None] = mapped_column(String(255), nullable=True, index=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()
    )

    vendor = relationship("Vendor", back_populates="violations")