from sqlalchemy import Column, Integer, String, Float, DateTime, ForeignKey, Text, UniqueConstraint
from sqlalchemy.orm import relationship, declarative_base
from datetime import datetime

Base = declarative_base()

class Category(Base):
    __tablename__ = 'categories'
    id = Column(Integer, primary_key=True)
    name = Column(String(50), unique=True, nullable=False)
    products = relationship("Product", back_populates="category_rel")

class Product(Base):
    __tablename__ = 'products'
    id = Column(Integer, primary_key=True)
    i_code = Column(String(50), unique=True, nullable=False, index=True)
    name = Column(String(255), nullable=False)
    url = Column(String(500))
    image_url = Column(Text)
    category = Column(String(100)) 
    
    # V9.52 新增欄位
    status = Column(String(20), default='ACTIVE') 
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)
    created_at = Column(DateTime, default=datetime.now)
    
    # 關聯設定
    category_id = Column(Integer, ForeignKey('categories.id'))
    category_rel = relationship("Category", back_populates="products")
    prices = relationship("PriceRecord", back_populates="product", cascade="all, delete-orphan")

class PriceRecord(Base):
    __tablename__ = 'price_records'
    id = Column(Integer, primary_key=True)
    product_id = Column(Integer, ForeignKey('products.id'), nullable=False)
    price = Column(Float, nullable=False)
    timestamp = Column(DateTime, default=datetime.now, index=True)
    product = relationship("Product", back_populates="prices")

class MonthlySummaryAnalysis(Base):
    __tablename__ = 'monthly_summary_analysis'
    id = Column(Integer, primary_key=True)
    year = Column(Integer, nullable=False, index=True)
    month = Column(Integer, nullable=False, index=True)
    department = Column(String(100))
    category_3c = Column(String(100))
    division = Column(String(100), index=True)
    section = Column(String(100))
    area_id = Column(String(50))
    area_name = Column(String(100))
    pm_name = Column(String(100), index=True)
    brand_name = Column(String(200), index=True)
    vendor_id = Column(Integer, index=True)
    vendor_name = Column(String(200))
    trade_type = Column(String(20))
    unit_price = Column(Float)
    
    # 指標 - 銷售額
    sales_amt_curr = Column(Integer)
    sales_amt_prev = Column(Integer)
    sales_amt_yoa = Column(Integer)
    
    # 指標 - 毛1額
    profit_amt_curr = Column(Integer)
    profit_amt_prev = Column(Integer)
    profit_amt_yoa = Column(Integer)
    
    # 指標 - 折扣金額
    discount_amt_curr = Column(Integer)
    discount_amt_prev = Column(Integer)
    discount_amt_yoa = Column(Integer)
    
    # 指標 - 折價券
    coupon_amt_curr = Column(Integer)
    coupon_amt_prev = Column(Integer)
    coupon_amt_yoa = Column(Integer)
    
    # 指標 - 其他行銷活動
    other_mkt_curr = Column(Integer)
    other_mkt_prev = Column(Integer)
    other_mkt_yoa = Column(Integer)
    
    # 指標 - 點我折
    spot_disc_curr = Column(Integer)
    spot_disc_prev = Column(Integer)
    spot_disc_yoa = Column(Integer)
    
    # 指標 - 點數折抵
    point_disc_curr = Column(Integer)
    point_disc_prev = Column(Integer)
    point_disc_yoa = Column(Integer)
    
    # 指標 - 銷售量
    sales_vol_curr = Column(Integer)
    sales_vol_prev = Column(Integer)
    sales_vol_yoa = Column(Integer)
    
    # 指標 - 轉換率與瀏覽數
    conv_rate = Column(Float)
    views_curr = Column(Integer)
    views_prev = Column(Integer)
    views_yoa = Column(Integer)
    
    created_at = Column(DateTime, default=datetime.now)

    __table_args__ = (
        UniqueConstraint('year', 'month', 'department', 'category_3c', 'division', 'section', 'area_id', 'pm_name', 'brand_name', 'vendor_id', 'trade_type', name='_monthly_summary_uc'),
    )