from datetime import datetime
from sqlalchemy import Column, Integer, String, Text, Boolean, DateTime, ForeignKey
from sqlalchemy.orm import relationship
from app.database import Base

class Product(Base):
    __tablename__ = "products"

    id = Column(Integer, primary_key=True, index=True)
    category_id = Column(Integer, ForeignKey("categories.id", ondelete="CASCADE"), nullable=False)
    name = Column(String(200), nullable=False)
    slug = Column(String(250), unique=True, index=True, nullable=False)
    description = Column(Text, nullable=True)
    features = Column(Text, nullable=True)  # JSON or newline-separated key features
    meta_title = Column(String(255), nullable=True)
    meta_description = Column(String(500), nullable=True)
    is_active = Column(Boolean, default=True)
    is_featured = Column(Boolean, default=False)
    display_order = Column(Integer, default=0)
    
    # Strictly Private Admin Fields (Never exposed to public customer API)
    private_mrp = Column(String(50), nullable=True)
    private_cost_price = Column(String(50), nullable=True)
    private_notes = Column(Text, nullable=True)

    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    category = relationship("Category", back_populates="products")
    images = relationship("ProductImage", back_populates="product", cascade="all, delete-orphan", order_by="ProductImage.display_order")
    specifications = relationship("ProductSpecification", back_populates="product", cascade="all, delete-orphan", order_by="ProductSpecification.display_order")


class ProductImage(Base):
    __tablename__ = "product_images"

    id = Column(Integer, primary_key=True, index=True)
    product_id = Column(Integer, ForeignKey("products.id", ondelete="CASCADE"), nullable=False)
    image_url = Column(String(500), nullable=False)
    alt_text = Column(String(255), nullable=True)
    is_primary = Column(Boolean, default=False)
    display_order = Column(Integer, default=0)
    created_at = Column(DateTime, default=datetime.utcnow)

    product = relationship("Product", back_populates="images")


class ProductSpecification(Base):
    __tablename__ = "product_specifications"

    id = Column(Integer, primary_key=True, index=True)
    product_id = Column(Integer, ForeignKey("products.id", ondelete="CASCADE"), nullable=False)
    size_name = Column(String(100), nullable=False)  # e.g., "3.5 Litre", "24 cm", "Size 10"
    dimensions = Column(String(100), nullable=True)  # e.g., "240 x 90 mm"
    gauge = Column(String(100), nullable=True)       # e.g., "22 Gauge Heavy", "3 mm thickness"
    material_grade = Column(String(100), nullable=True) # e.g., "AISI 304 SS", "Pure Aluminium"
    packing_unit = Column(String(100), nullable=True)   # e.g., "24 Pcs / Carton", "Box of 12"
    display_order = Column(Integer, default=0)

    product = relationship("Product", back_populates="specifications")