from sqlalchemy import Column, Integer, String, Float, Text, DateTime, JSON, ForeignKey
from sqlalchemy.sql import text
from sqlalchemy.orm import relationship
from database import Base

class Product(Base):
    __tablename__ = "products"
    
    id = Column(Integer, primary_key=True, index=True, comment='主键 ID')
    category_id = Column(Integer, ForeignKey("categories.id"), nullable=False, server_default="0", index=True, comment='分类 ID')
    product_number = Column(String(11), nullable=False, unique=True, index=True, comment='产品货号，11 位 UUID')
    model = Column(String(50), nullable=False, server_default="", comment='产品型号，业务输入')
    name = Column(String(200), nullable=False, server_default="", index=True, comment='产品名称')
    brand = Column(String(100), nullable=False, server_default="", comment='品牌名称')
    price = Column(Float, nullable=False, server_default="0", comment='价格')
    original_price = Column(Float, nullable=False, server_default="0", comment='原价')
    currency = Column(String(10), nullable=False, server_default="USD", comment='货币符号')
    stock = Column(Integer, nullable=False, server_default="0", comment='库存')
    min_order = Column(Integer, nullable=False, server_default="1", comment='最小起订量')
    unit = Column(String(20), nullable=False, server_default="pcs", comment='计量单位')
    status = Column(Integer, nullable=False, server_default="1", comment='状态：0-下架，1-上架')
    is_new = Column(Integer, nullable=False, server_default="0", comment='是否新品：0-否，1-是')
    is_hot = Column(Integer, nullable=False, server_default="0", comment='是否热销：0-否，1-是')
    is_best_seller = Column(Integer, nullable=False, server_default="0", comment='是否畅销：0-否，1-是')
    sort_order = Column(Integer, nullable=False, server_default="0", comment='排序顺序')
    
    # 商品详情字段
    description = Column(Text, nullable=False, comment='功能描述')
    usage = Column(Text, nullable=False, comment='适用范围')
    material = Column(String(200), nullable=False, server_default="", comment='材质')
    parameters = Column(Text, nullable=False, comment='其他参数说明')
    
    # SEO 字段
    seo_title_tag = Column(String(200), nullable=False, server_default="", comment='SEO 页面标题')
    seo_meta_keywords = Column(String(500), nullable=False, server_default="", comment='SEO 元关键词')
    
    # 产地字段
    country_of_origin = Column(String(100), nullable=False, server_default="", comment='原产国')
    made_in_label = Column(String(100), nullable=False, server_default="", comment='Made in 标签')
    
    company_code = Column(String(50), nullable=False, server_default="", index=True, comment='公司编码，用于数据隔离')
    created_at = Column(DateTime(timezone=True), nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
    updated_at = Column(DateTime(timezone=True), nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'), comment='更新时间')
    
    # 关系
    category = relationship("Category", back_populates="products")