import uuid
from datetime import datetime

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

from app.db.base import Base


class ScrapingResult(Base):
    """
    Stores comprehensive scraping results for all products and vendors.
    Includes compliant, complaining (above MSP), and violation (below MSP) records.
    """
    __tablename__ = "scraping_results"

    id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    product_id: Mapped[int] = mapped_column(ForeignKey("products.id"), nullable=False, index=True)
    vendor_id: Mapped[uuid.UUID | None] = mapped_column(UUID(as_uuid=True), ForeignKey("vendors.id"), nullable=True, index=True)
    
    # Product details
    product_name: Mapped[str] = mapped_column(String(1024), nullable=False)
    barcode: Mapped[str] = mapped_column(String(255), nullable=True)
    reference_id: Mapped[str] = mapped_column(String(255), nullable=True)
    
    # Vendor details
    vendor_name: Mapped[str] = mapped_column(String(255), nullable=False)
    vendor_url: Mapped[str] = mapped_column(String(2048), nullable=False)
    domain_name: Mapped[str | None] = mapped_column(String(255), nullable=True)
    
    # Pricing information
    msp: Mapped[float] = mapped_column(Numeric(10, 2), nullable=False)
    scraped_price: Mapped[float] = mapped_column(Numeric(10, 2), nullable=True)
    price_difference: Mapped[float] = mapped_column(Numeric(10, 2), nullable=True)
    percentage_difference: Mapped[float] = mapped_column(Numeric(10, 2), nullable=True)
    
    # Compliance status: 'compliant' (price > MSP), 'violation' (price < MSP), 'unknown' (no price found), 'complaining' (price > MSP)
    compliance_status: Mapped[str] = mapped_column(String(50), nullable=False, default="unknown")
    
    # Source tracking
    source_type: Mapped[str] = mapped_column(String(50), nullable=False)  # 'registered', 'discovered'
    
    # Timestamps
    scraped_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, server_default=func.now())
    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()
    )

    # Relationships
    vendor = relationship("Vendor")
    product = relationship("Product")