from sqlalchemy import BigInteger, String, Text, JSON, Integer, UniqueConstraint, Index, ForeignKey
from sqlalchemy.dialects.mysql import TINYINT
from sqlalchemy.orm import Mapped, mapped_column, relationship
from app.core.models.base import Base, TimestampMixin, TenantMixin


class Category(Base, TenantMixin, TimestampMixin):
    """商品分类（支持无限级树形结构）"""

    __tablename__ = "categories"
    __table_args__ = (
        UniqueConstraint("tenant_id", "slug", name="uk_categories_tenant_slug"),
        Index("ix_categories_sort", "sort_order"),
        Index("ix_categories_active", "is_active"),
        {
            "mysql_engine": "InnoDB",
            "mysql_charset": "utf8mb4",
            "mysql_collate": "utf8mb4_unicode_ci",
        },
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    parent_id: Mapped[int | None] = mapped_column(
        BigInteger,
        ForeignKey("categories.id", ondelete="SET NULL"),
        nullable=True,
        index=True,
        comment="父分类 ID，NULL 表示顶级",
    )
    name: Mapped[str] = mapped_column(String(100), nullable=False, comment="分类名称")
    slug: Mapped[str] = mapped_column(String(200), nullable=False, comment="SEO 友好 URL 片段")
    description: Mapped[str | None] = mapped_column(Text, nullable=True)

    # SEO 字段
    meta_title: Mapped[str | None] = mapped_column(String(160), nullable=True, comment="SEO 标题（60 字符以内）")
    meta_description: Mapped[str | None] = mapped_column(String(320), nullable=True, comment="SEO 描述")
    seo_keywords: Mapped[str | None] = mapped_column(String(500), nullable=True, comment="SEO 关键词")
    image_url: Mapped[str | None] = mapped_column(String(500), nullable=True, comment="分类封面图")

    sort_order: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    is_nav_visible: Mapped[int] = mapped_column(TINYINT(1), nullable=False, default=1, comment="是否显示在导航")
    is_active: Mapped[int] = mapped_column(TINYINT(1), nullable=False, default=1)
    attribute_template: Mapped[dict | None] = mapped_column(JSON, nullable=True, comment="分类属性模板定义")
    extra_attributes: Mapped[dict | None] = mapped_column(JSON, nullable=True, comment="行业定制字段")
    # 英文多语言字段
    name_en: Mapped[str | None] = mapped_column(String(100), nullable=True, comment="分类英文名称")
    description_en: Mapped[str | None] = mapped_column(Text, nullable=True, comment="分类英文描述")
    meta_title_en: Mapped[str | None] = mapped_column(String(160), nullable=True, comment="英文SEO标题")
    meta_description_en: Mapped[str | None] = mapped_column(String(320), nullable=True, comment="英文SEO描述")
    seo_keywords_en: Mapped[str | None] = mapped_column(String(500), nullable=True, comment="英文SEO关键词")

    # ── 关联 ─────────────────────────────────────────────────────
    parent = relationship("Category", remote_side="Category.id", lazy="noload")
    children = relationship("Category", back_populates="parent", lazy="noload")

    def __repr__(self) -> str:
        return f"<Category id={self.id} slug={self.slug!r}>"