from datetime import datetime
from sqlalchemy import Boolean, DateTime, Float, Integer, String, Text, UniqueConstraint
from sqlalchemy.orm import Mapped, mapped_column
from app.core.database import Base

class Retailer(Base):
    __tablename__ = "retailers"
    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    name: Mapped[str] = mapped_column(String(160), unique=True, index=True)
    brand: Mapped[str] = mapped_column(String(160), default="")
    base_url: Mapped[str] = mapped_column(String(500), default="")
    connector_type: Mapped[str] = mapped_column(String(80), default="manual_import")
    compliance_status: Mapped[str] = mapped_column(String(80), default="manual_only")
    rate_limit_seconds: Mapped[int] = mapped_column(Integer, default=5)
    enabled: Mapped[bool] = mapped_column(Boolean, default=False)
    last_run_status: Mapped[str] = mapped_column(String(120), default="not_run")
    last_run_at: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)
    notes: Mapped[str] = mapped_column(Text, default="")

class Product(Base):
    __tablename__ = "products"
    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    retailer_name: Mapped[str] = mapped_column(String(160), index=True)
    retailer_brand: Mapped[str] = mapped_column(String(160), default="")
    store_location: Mapped[str] = mapped_column(String(160), default="")
    product_id: Mapped[str] = mapped_column(String(200), default="", index=True)
    retailer_product_code: Mapped[str] = mapped_column(String(200), default="")
    product_name: Mapped[str] = mapped_column(String(500), index=True)
    brand: Mapped[str] = mapped_column(String(220), default="", index=True)
    category_level_1: Mapped[str] = mapped_column(String(220), default="")
    category_level_2: Mapped[str] = mapped_column(String(220), default="")
    category_level_3: Mapped[str] = mapped_column(String(220), default="")
    pack_size: Mapped[str] = mapped_column(String(100), default="")
    unit_of_measure: Mapped[str] = mapped_column(String(60), default="")
    price: Mapped[float | None] = mapped_column(Float, nullable=True)
    promo_price: Mapped[float | None] = mapped_column(Float, nullable=True)
    promotion_text: Mapped[str] = mapped_column(Text, default="")
    barcode_or_gtin: Mapped[str] = mapped_column(String(80), default="")
    barcode_source: Mapped[str] = mapped_column(String(120), default="not publicly available")
    image_url: Mapped[str] = mapped_column(String(1000), default="")
    product_url: Mapped[str] = mapped_column(String(1000), default="")
    availability_status: Mapped[str] = mapped_column(String(120), default="unknown")
    source_type: Mapped[str] = mapped_column(String(120), default="manual_import")
    source_url: Mapped[str] = mapped_column(String(1000), default="")
    date_captured: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)
    confidence_score: Mapped[float] = mapped_column(Float, default=1.0)
    canonical_key: Mapped[str] = mapped_column(String(700), index=True, default="")
    created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)

    __table_args__ = (
        UniqueConstraint("retailer_name", "product_name", "brand", "pack_size", "product_url", name="uq_product_identity"),
    )

class AuditLog(Base):
    __tablename__ = "audit_logs"
    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    event_type: Mapped[str] = mapped_column(String(100), index=True)
    source: Mapped[str] = mapped_column(String(500), default="")
    source_type: Mapped[str] = mapped_column(String(120), default="")
    timestamp: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow, index=True)
    status: Mapped[str] = mapped_column(String(80), default="success")
    pages_processed: Mapped[int] = mapped_column(Integer, default=0)
    products_created: Mapped[int] = mapped_column(Integer, default=0)
    products_updated: Mapped[int] = mapped_column(Integer, default=0)
    products_skipped: Mapped[int] = mapped_column(Integer, default=0)
    error_reason: Mapped[str] = mapped_column(Text, default="")
    compliance_decision: Mapped[str] = mapped_column(String(180), default="")
    details: Mapped[str] = mapped_column(Text, default="")