# backend/app/core/models/product_template.py
from datetime import datetime
from sqlalchemy import (
    BigInteger, String, Text, JSON, ForeignKey, Table, Column,
    UniqueConstraint, Index,
)
from sqlalchemy.dialects.mysql import DATETIME as MYSQL_DATETIME, DECIMAL as MYSQL_DECIMAL, MEDIUMTEXT
from sqlalchemy.orm import Mapped, mapped_column, relationship
from app.core.models.base import Base, TimestampMixin


# ── Association tables ────────────────────────────────────────

template_collection_items = Table(
    "template_collection_items",
    Base.metadata,
    Column("template_id", BigInteger, ForeignKey("product_templates.id", ondelete="CASCADE"), primary_key=True),
    Column("collection_id", BigInteger, ForeignKey("template_collections.id", ondelete="CASCADE"), primary_key=True),
    mysql_engine="InnoDB",
    mysql_charset="utf8mb4",
    mysql_collate="utf8mb4_unicode_ci",
)

tenant_visible_collections = Table(
    "tenant_visible_collections",
    Base.metadata,
    Column("tenant_id", BigInteger, ForeignKey("tenants.id", ondelete="CASCADE"), primary_key=True),
    Column("collection_id", BigInteger, ForeignKey("template_collections.id", ondelete="CASCADE"), primary_key=True),
    mysql_engine="InnoDB",
    mysql_charset="utf8mb4",
    mysql_collate="utf8mb4_unicode_ci",
)


# ── Template Collection ───────────────────────────────────────

class TemplateCollection(Base, TimestampMixin):
    __tablename__ = "template_collections"

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    name: Mapped[str] = mapped_column(String(100), nullable=False, unique=True, comment="集合名称，如 新西兰保健品")
    name_en: Mapped[str | None] = mapped_column(String(100), nullable=True)
    description: Mapped[str | None] = mapped_column(String(500), nullable=True)
    sort_order: Mapped[int] = mapped_column(default=0, comment="排序权重")

    templates = relationship("ProductTemplate", secondary=template_collection_items, back_populates="collections", lazy="noload")
    tenants = relationship("Tenant", secondary=tenant_visible_collections, lazy="noload")


# ── Product Template ──────────────────────────────────────────

class ProductTemplate(Base, TimestampMixin):
    __tablename__ = "product_templates"

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    sku: Mapped[str] = mapped_column(String(100), nullable=False, unique=True, comment="全局唯一 SKU")
    name: Mapped[str] = mapped_column(String(255), nullable=False)
    name_en: Mapped[str | None] = mapped_column(String(255), nullable=True)
    description: Mapped[str | None] = mapped_column(MEDIUMTEXT, nullable=True)
    description_en: Mapped[str | None] = mapped_column(MEDIUMTEXT, nullable=True)
    ai_description: Mapped[str | None] = mapped_column(MEDIUMTEXT, nullable=True)
    ai_description_en: Mapped[str | None] = mapped_column(MEDIUMTEXT, nullable=True)

    base_price: Mapped[float | None] = mapped_column(MYSQL_DECIMAL(12, 2), nullable=True, comment="建议零售价")
    cost_price: Mapped[float | None] = mapped_column(MYSQL_DECIMAL(12, 2), nullable=True)
    market_price: Mapped[float | None] = mapped_column(MYSQL_DECIMAL(12, 2), nullable=True)

    weight: Mapped[int | None] = mapped_column(nullable=True, comment="重量(克)")
    length: Mapped[float | None] = mapped_column(MYSQL_DECIMAL(8, 2), nullable=True)
    width: Mapped[float | None] = mapped_column(MYSQL_DECIMAL(8, 2), nullable=True)
    height: Mapped[float | None] = mapped_column(MYSQL_DECIMAL(8, 2), nullable=True)

    extra_attributes: Mapped[dict | None] = mapped_column(JSON, nullable=True, comment="扩展属性")

    meta_title: Mapped[str | None] = mapped_column(String(200), nullable=True)
    meta_title_en: Mapped[str | None] = mapped_column(String(200), nullable=True)
    meta_description: Mapped[str | None] = mapped_column(String(500), nullable=True)
    meta_description_en: Mapped[str | None] = mapped_column(String(500), nullable=True)
    seo_keywords: Mapped[str | None] = mapped_column(String(300), nullable=True)
    seo_keywords_en: Mapped[str | None] = mapped_column(String(300), nullable=True)

    status: Mapped[str] = mapped_column(String(20), nullable=False, default="pending", index=True, comment="pending/approved/rejected")

    # relationships
    images = relationship("TemplateImage", back_populates="template", cascade="all, delete-orphan", lazy="selectin")
    variants = relationship("TemplateVariant", back_populates="template", cascade="all, delete-orphan", lazy="selectin")
    sources = relationship("TemplateSource", back_populates="template", cascade="all, delete-orphan", lazy="selectin")
    collections = relationship("TemplateCollection", secondary=template_collection_items, back_populates="templates", lazy="noload")

    __table_args__ = (
        Index("ix_product_templates_sku", "sku"),
        Index("ix_product_templates_status", "status"),
        {"mysql_engine": "InnoDB", "mysql_charset": "utf8mb4", "mysql_collate": "utf8mb4_unicode_ci"},
    )


class TemplateImage(Base):
    __tablename__ = "template_images"

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    template_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("product_templates.id", ondelete="CASCADE"), nullable=False)
    url: Mapped[str] = mapped_column(String(500), nullable=False)
    alt_text: Mapped[str | None] = mapped_column(String(200), nullable=True)
    is_primary: Mapped[bool] = mapped_column(default=False)
    sort_order: Mapped[int] = mapped_column(default=0)

    template = relationship("ProductTemplate", back_populates="images")


class TemplateVariant(Base):
    __tablename__ = "template_variants"

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    template_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("product_templates.id", ondelete="CASCADE"), nullable=False)
    sku: Mapped[str | None] = mapped_column(String(100), nullable=True)
    attributes: Mapped[dict | None] = mapped_column(JSON, nullable=True, comment="规格属性 {color, size, ...}")
    price_modifier: Mapped[float | None] = mapped_column(MYSQL_DECIMAL(12, 2), nullable=True)
    independent_price: Mapped[float | None] = mapped_column(MYSQL_DECIMAL(12, 2), nullable=True)
    weight: Mapped[int | None] = mapped_column(nullable=True)
    is_default: Mapped[bool] = mapped_column(default=False)

    template = relationship("ProductTemplate", back_populates="variants")


# ── Template Source (traceability) ────────────────────────────

class TemplateSource(Base, TimestampMixin):
    __tablename__ = "template_sources"

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    template_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("product_templates.id", ondelete="CASCADE"), nullable=False)
    tenant_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False)
    product_id: Mapped[int] = mapped_column(BigInteger, nullable=False, comment="贡献此模板的原始商品 ID")

    template = relationship("ProductTemplate", back_populates="sources")

    __table_args__ = (
        UniqueConstraint("template_id", "tenant_id", "product_id", name="uk_template_source"),
        {"mysql_engine": "InnoDB", "mysql_charset": "utf8mb4", "mysql_collate": "utf8mb4_unicode_ci"},
    )