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

class CustomerEnquiry(Base):
    __tablename__ = "customer_enquiries"

    id = Column(Integer, primary_key=True, index=True)
    enquiry_number = Column(String(50), unique=True, index=True, nullable=False)
    customer_name = Column(String(100), nullable=False)
    shop_name = Column(String(150), nullable=True)
    mobile_whatsapp = Column(String(30), nullable=False, index=True)
    city = Column(String(100), nullable=False, index=True)
    message = Column(Text, nullable=True)
    status = Column(String(30), default="New", index=True)  # New, Contacted, In-Progress, Closed
    admin_notes = Column(Text, nullable=True)
    total_items = Column(Integer, default=1)
    ip_address = Column(String(50), nullable=True)
    created_at = Column(DateTime, default=datetime.utcnow, index=True)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    items = relationship("EnquiryItem", back_populates="enquiry", cascade="all, delete-orphan")


class EnquiryItem(Base):
    __tablename__ = "enquiry_items"

    id = Column(Integer, primary_key=True, index=True)
    enquiry_id = Column(Integer, ForeignKey("customer_enquiries.id", ondelete="CASCADE"), nullable=False)
    product_id = Column(Integer, nullable=True)
    product_name = Column(String(200), nullable=False)
    product_slug = Column(String(250), nullable=True)
    selected_specification = Column(String(200), nullable=True)  # e.g., "Size: 3 Litre (22 Gauge)"
    quantity = Column(String(50), nullable=False)                # e.g., "50 Pcs", "100 Cartons"
    product_image_url = Column(String(500), nullable=True)

    enquiry = relationship("CustomerEnquiry", back_populates="items")