from datetime import date, datetime
from decimal import Decimal
from sqlalchemy import BigInteger, String, Text, JSON, Integer, DECIMAL, Date, UniqueConstraint, Index, ForeignKey, Table, Column
from sqlalchemy.dialects.mysql import TINYINT, MEDIUMTEXT, DATETIME as MYSQL_DATETIME
from sqlalchemy.orm import Mapped, mapped_column, relationship
from app.core.models.base import Base, TimestampMixin, TenantMixin

# 商品-分类多对多关联表
product_categories = Table(
    "product_categories",
    Base.metadata,
    Column("product_id", BigInteger, ForeignKey("products.id", ondelete="CASCADE"), nullable=False),
    Column("category_id", BigInteger, ForeignKey("categories.id", ondelete="CASCADE"), nullable=False),
    UniqueConstraint("product_id", "category_id", name="uk_product_category"),
    mysql_engine="InnoDB",
    mysql_charset="utf8mb4",
)


class Product(Base, TenantMixin, TimestampMixin):
    """商品核心表（含 SEO + JSON 动态扩展）"""

    __tablename__ = "products"
    __table_args__ = (
        UniqueConstraint("tenant_id", "slug", name="uk_products_tenant_slug"),
        UniqueConstraint("tenant_id", "sku",  name="uk_products_tenant_sku"),
        Index("ix_products_tenant_name", "tenant_id", "name"),
        Index("ix_products_status", "status"),
        Index("ix_products_created", "created_at"),
        {
            "mysql_engine": "InnoDB",
            "mysql_charset": "utf8mb4",
            "mysql_collate": "utf8mb4_unicode_ci",
        },
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    category_id: Mapped[int | None] = mapped_column(
        BigInteger, ForeignKey("categories.id", ondelete="SET NULL"),
        nullable=True, index=True
    )
    brand_id: Mapped[int | None] = mapped_column(
        BigInteger, ForeignKey("brands.id", ondelete="SET NULL"),
        nullable=True, index=True,
        comment="品牌 ID",
    )
    source_template_id: Mapped[int | None] = mapped_column(
        BigInteger,
        ForeignKey("product_templates.id", ondelete="SET NULL"),
        nullable=True,
        index=True,
        comment="从模板库导入时记录来源模板 ID",
    )
    tax_class_id: Mapped[int | None] = mapped_column(
        BigInteger, ForeignKey("tax_classes.id", ondelete="SET NULL"),
        nullable=True, index=True,
        comment="税种 ID",
    )
    name: Mapped[str] = mapped_column(String(200), nullable=False, comment="商品名称")
    slug: Mapped[str] = mapped_column(String(300), nullable=False, comment="SEO URL，如 leather-jacket-black")
    sku: Mapped[str] = mapped_column(String(100), nullable=False, comment="商品编码")
    description: Mapped[str | None] = mapped_column(MEDIUMTEXT, nullable=True, comment="富文本描述")
    ai_description: Mapped[str | None] = mapped_column(Text, nullable=True, comment="AI客服专属描述")

    # 价格 / 库存
    base_price: Mapped[Decimal] = mapped_column(DECIMAL(12, 2), nullable=False, comment="基础定价")
    market_price: Mapped[Decimal | None] = mapped_column(DECIMAL(12, 2), nullable=True, comment="市场价/划线价")
    member_price: Mapped[Decimal | None] = mapped_column(DECIMAL(12, 2), nullable=True, comment="默认会员价，可被会员等级覆盖")
    cost_price: Mapped[Decimal | None] = mapped_column(DECIMAL(12, 2), nullable=True, comment="成本价（仅后台可见）")
    # 价格规则集版本号：Admin 改价规则时通过乐观锁防止互相覆盖，POS 端用它感知快照失效
    price_rules_version: Mapped[int] = mapped_column(Integer, nullable=False, default=0, comment="价格规则集版本号")
    stock_qty: Mapped[Decimal] = mapped_column(DECIMAL(12, 2), nullable=False, default=Decimal("0"))
    reserved_qty: Mapped[Decimal] = mapped_column(DECIMAL(12, 2), nullable=False, default=Decimal("0"), comment="已锁定库存")
    low_stock_threshold: Mapped[int] = mapped_column(Integer, nullable=False, default=5, comment="库存预警阈值")
    allow_oversell: Mapped[int] = mapped_column(TINYINT(1), nullable=False, default=0, comment="是否允许超卖")
    weight: Mapped[Decimal | None] = mapped_column(DECIMAL(8, 3), nullable=True, comment="重量(kg)，用于运费计算")
    length: Mapped[Decimal | None] = mapped_column(DECIMAL(8, 2), nullable=True, comment="长度(cm)，用于体积重计算")
    width: Mapped[Decimal | None] = mapped_column(DECIMAL(8, 2), nullable=True, comment="宽度(cm)，用于体积重计算")
    height: Mapped[Decimal | None] = mapped_column(DECIMAL(8, 2), nullable=True, comment="高度(cm)，用于体积重计算")

    status: Mapped[str] = mapped_column(
        String(20), nullable=False, default="draft",
        comment="draft / active / archived"
    )
    sales_count: Mapped[int] = mapped_column(Integer, nullable=False, default=0, index=True, comment="销量（用于排序）")

    # SEO 字段
    meta_title: Mapped[str | None] = mapped_column(String(160), nullable=True, comment="SEO 标题，空时中间件自动填充")
    meta_description: Mapped[str | None] = mapped_column(String(500), nullable=True)
    seo_keywords: Mapped[str | None] = mapped_column(String(500), nullable=True, comment="关键词，逗号分隔")
    # 英文多语言字段
    name_en: Mapped[str | None] = mapped_column(String(200), nullable=True, comment="商品英文名称")
    description_en: Mapped[str | None] = mapped_column(MEDIUMTEXT, nullable=True, comment="商品英文富文本描述")
    ai_description_en: Mapped[str | None] = mapped_column(Text, nullable=True, comment="AI客服英文描述")
    meta_title_en: Mapped[str | None] = mapped_column(String(160), nullable=True, comment="英文SEO标题")
    meta_description_en: Mapped[str | None] = mapped_column(String(500), nullable=True, comment="英文SEO描述")
    seo_keywords_en: Mapped[str | None] = mapped_column(String(500), nullable=True, comment="英文SEO关键词，逗号分隔")
    shelf_life: Mapped[str | None] = mapped_column(String(100), nullable=True, comment="保质期，如 12个月、365天")
    shelf_life_en: Mapped[str | None] = mapped_column(String(100), nullable=True, comment="保质期英文，如 March 2029")
    expiry_date: Mapped[date | None] = mapped_column(Date, nullable=True, comment="商品到期日期")
    og_image: Mapped[str | None] = mapped_column(String(500), nullable=True, comment="Open Graph 图片 URL")
    cover_url: Mapped[str | None] = mapped_column(String(500), nullable=True, comment="商品主图 URL（冗余字段，用于快速读取）")
    schema_markup: Mapped[dict | None] = mapped_column(JSON, nullable=True, comment="JSON-LD 结构化数据（Schema.org）")

    # 行业扩展属性（颜色/尺码/车架号等），通过生成列索引查询
    extra_attributes: Mapped[dict | None] = mapped_column(JSON, nullable=True, comment="行业定制属性，通过生成列索引提速")

    # 库存状态
    stock_status_id: Mapped[int | None] = mapped_column(
        BigInteger, ForeignKey("stock_statuses.id", ondelete="SET NULL"),
        nullable=True, index=True, comment="库存状态 ID",
    )

    # ── 关联 ─────────────────────────────────────────────────────
    tenant = relationship("Tenant", back_populates="products", lazy="noload")
    stock_status = relationship("StockStatus", lazy="noload")
    categories = relationship("Category", secondary=product_categories, lazy="noload")
    brand = relationship("Brand", back_populates="products", lazy="noload")
    images = relationship("ProductImage", back_populates="product", lazy="noload", cascade="all, delete-orphan")
    variants = relationship("ProductVariant", back_populates="product", lazy="noload", cascade="all, delete-orphan")
    tier_prices = relationship("ProductTierPrice", back_populates="product", lazy="noload", cascade="all, delete-orphan", foreign_keys="ProductTierPrice.product_id")
    price_rules = relationship("ProductPriceRule", back_populates="product", lazy="noload", cascade="all, delete-orphan", foreign_keys="ProductPriceRule.product_id")

    @property
    def available_qty(self) -> Decimal:
        """可下单量。stock_qty 是含锁定的实物在库量，此处只减一次 reserved_qty。"""
        return max(Decimal("0"), (self.stock_qty or Decimal("0")) - (self.reserved_qty or Decimal("0")))

    def __init__(self, **kwargs):
        kwargs.setdefault("stock_qty", Decimal("0"))
        kwargs.setdefault("reserved_qty", Decimal("0"))
        kwargs.setdefault("low_stock_threshold", 5)
        kwargs.setdefault("allow_oversell", 0)
        kwargs.setdefault("status", "draft")
        kwargs.setdefault("sales_count", 0)
        kwargs.setdefault("price_rules_version", 0)
        super().__init__(**kwargs)

    # ── JSON 辅助方法 ─────────────────────────────────────────────
    def get_attribute(self, key: str, default=None):
        """安全读取 extra_attributes 中的单个键"""
        if not self.extra_attributes:
            return default
        return self.extra_attributes.get(key, default)

    def set_attribute(self, key: str, value) -> None:
        """原子更新 extra_attributes 中的单个键（避免全字段覆写）"""
        if self.extra_attributes is None:
            self.extra_attributes = {}
        self.extra_attributes = {**self.extra_attributes, key: value}

    def has_attribute(self, key: str) -> bool:
        """判断某扩展属性是否存在"""
        return bool(self.extra_attributes and key in self.extra_attributes)

    def get_effective_meta_title(self, category_name: str = "", shop_name: str = "") -> str:
        """获取 SEO 标题（为空时自动拼接）"""
        if self.meta_title:
            return self.meta_title
        parts = [self.name, category_name, shop_name]
        return " | ".join(p for p in parts if p)

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


class ProductImage(Base, TimestampMixin):
    """商品图片（自动 WebP）"""

    __tablename__ = "product_images"
    __table_args__ = (
        Index("ix_pimage_product", "product_id"),
        Index("ix_pimage_primary", "is_primary"),
        {
            "mysql_engine": "InnoDB",
            "mysql_charset": "utf8mb4",
            "mysql_collate": "utf8mb4_unicode_ci",
        },
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    product_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("products.id", ondelete="CASCADE"), nullable=False)
    tenant_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("tenants.id", ondelete="RESTRICT"), nullable=False, index=True)
    url: Mapped[str] = mapped_column(String(500), nullable=False, comment="原始图片 URL")
    webp_url: Mapped[str | None] = mapped_column(String(500), nullable=True, comment="WebP 转换后 URL（异步回写）")
    alt_text: Mapped[str | None] = mapped_column(String(200), nullable=True, comment="SEO alt 文本")
    sort_order: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    is_primary: Mapped[int] = mapped_column(TINYINT(1), nullable=False, default=0, comment="是否为主图")

    product = relationship("Product", back_populates="images", lazy="noload")

    def __repr__(self) -> str:
        return f"<ProductImage id={self.id} product_id={self.product_id}>"


class ProductVariant(Base, TimestampMixin):
    """商品变体 / SKU（颜色、尺码等）"""

    __tablename__ = "product_variants"
    __table_args__ = (
        UniqueConstraint("tenant_id", "sku", name="uk_variant_tenant_sku"),
        Index("ix_variant_stock", "stock_qty"),
        {
            "mysql_engine": "InnoDB",
            "mysql_charset": "utf8mb4",
            "mysql_collate": "utf8mb4_unicode_ci",
        },
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    product_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("products.id", ondelete="CASCADE"), nullable=False, index=True)
    tenant_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("tenants.id", ondelete="RESTRICT"), nullable=False, index=True)
    sku: Mapped[str] = mapped_column(String(100), nullable=False)
    barcode: Mapped[str | None] = mapped_column(String(120), nullable=True, comment="条码/UPC/EAN")
    price_modifier: Mapped[Decimal] = mapped_column(DECIMAL(12, 2), nullable=False, default=Decimal("0"))
    stock_qty: Mapped[Decimal] = mapped_column(DECIMAL(12, 2), nullable=False, default=Decimal("0"))
    reserved_qty: Mapped[Decimal] = mapped_column(DECIMAL(12, 2), nullable=False, default=Decimal("0"), comment="已锁定库存")
    image_url: Mapped[str | None] = mapped_column(String(500), nullable=True, comment="SKU 图片")
    weight: Mapped[Decimal | None] = mapped_column(DECIMAL(8, 3), nullable=True, comment="SKU 重量(kg)")
    length: Mapped[Decimal | None] = mapped_column(DECIMAL(8, 2), nullable=True, comment="长度(cm)")
    width: Mapped[Decimal | None] = mapped_column(DECIMAL(8, 2), nullable=True, comment="宽度(cm)")
    height: Mapped[Decimal | None] = mapped_column(DECIMAL(8, 2), nullable=True, comment="高度(cm)")
    member_price: Mapped[Decimal | None] = mapped_column(DECIMAL(12, 2), nullable=True, comment="规格会员价")
    expiry_date: Mapped[date | None] = mapped_column(Date, nullable=True, comment="规格到期日期")
    sort_order: Mapped[int] = mapped_column(Integer, nullable=False, default=0)

    # {"color": "red", "size": "XL"} — 通过生成列索引查询
    attributes: Mapped[dict] = mapped_column(JSON, nullable=False, comment="变体属性：颜色/尺码等，建生成列索引")
    is_active: Mapped[int] = mapped_column(TINYINT(1), nullable=False, default=1)
    is_default: Mapped[int] = mapped_column(TINYINT(1), nullable=False, default=0, comment="是否为默认规格")

    product = relationship("Product", back_populates="variants", lazy="noload")
    tier_prices = relationship("ProductTierPrice", back_populates="variant", lazy="noload", cascade="all, delete-orphan", foreign_keys="ProductTierPrice.variant_id")

    def __init__(self, **kwargs):
        kwargs.setdefault("price_modifier", Decimal("0"))
        kwargs.setdefault("stock_qty", Decimal("0"))
        kwargs.setdefault("reserved_qty", Decimal("0"))
        kwargs.setdefault("sort_order", 0)
        kwargs.setdefault("attributes", {})
        kwargs.setdefault("is_active", 1)
        kwargs.setdefault("is_default", 0)
        super().__init__(**kwargs)

    @property
    def available_qty(self) -> Decimal:
        """可售库存 = 实际库存 - 已锁定库存。"""
        return max(Decimal("0"), (self.stock_qty or Decimal("0")) - (self.reserved_qty or Decimal("0")))

    def get_color(self) -> str | None:
        return (self.attributes or {}).get("color")

    def get_size(self) -> str | None:
        return (self.attributes or {}).get("size")

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


class ProductTierPrice(Base):
    """商品/规格 按会员等级的固定价格覆盖。
    variant_id 为 NULL 表示商品级定价；有值表示规格级定价（优先级更高）。
    """

    __tablename__ = "product_tier_prices"
    __table_args__ = (
        UniqueConstraint("product_id", "variant_id", "member_level_id", name="uk_tier_product_variant_level"),
        Index("ix_tier_product",      "product_id"),
        Index("ix_tier_variant",      "variant_id"),
        Index("ix_tier_member_level", "member_level_id"),
        {
            "mysql_engine": "InnoDB",
            "mysql_charset": "utf8mb4",
        },
    )

    id:              Mapped[int]          = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    tenant_id:       Mapped[int]          = mapped_column(BigInteger, ForeignKey("tenants.id", ondelete="RESTRICT"), nullable=False, index=True)
    product_id:      Mapped[int]          = mapped_column(BigInteger, ForeignKey("products.id", ondelete="CASCADE"), nullable=False)
    variant_id:      Mapped[int | None]   = mapped_column(BigInteger, ForeignKey("product_variants.id", ondelete="CASCADE"), nullable=True)
    member_level_id: Mapped[int]          = mapped_column(BigInteger, ForeignKey("member_levels.id", ondelete="CASCADE"), nullable=False)
    price:           Mapped[Decimal]      = mapped_column(DECIMAL(12, 2), nullable=False)

    product = relationship("Product", back_populates="tier_prices", lazy="noload")
    variant = relationship("ProductVariant", back_populates="tier_prices", lazy="noload")

    def __repr__(self) -> str:
        return f"<ProductTierPrice product={self.product_id} variant={self.variant_id} level={self.member_level_id} price={self.price}>"


# ── 价格规则（按渠道/客户/数量/时段） ─────────────────────────────
# 受约束的 VARCHAR 枚举，不用 JSON：报价热路径按商品读少量行，再在内存判断渠道。
# 排序键：min_quantity DESC, priority ASC, 计算成交价 ASC, id ASC。
# ponytail: 暂不引入"叠加多规则"语义。一个商品一次只命中一条，规则间冲突按排序键唯一确定。
class ProductPriceRule(Base, TenantMixin, TimestampMixin):
    """商品/规格按渠道/客户/数量/时段的报价规则。"""

    __tablename__ = "product_price_rules"
    __table_args__ = (
        Index("ix_ppr_product_active", "tenant_id", "product_id", "is_active"),
        Index("ix_ppr_tenant_product", "tenant_id", "product_id"),
        Index("ix_ppr_variant", "variant_id"),
        Index("ix_ppr_member_level", "member_level_id"),
        {
            "mysql_engine": "InnoDB",
            "mysql_charset": "utf8mb4",
            "mysql_collate": "utf8mb4_unicode_ci",
        },
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    product_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("products.id", ondelete="CASCADE"), nullable=False)
    variant_id: Mapped[int | None] = mapped_column(BigInteger, ForeignKey("product_variants.id", ondelete="CASCADE"), nullable=True)
    member_level_id: Mapped[int | None] = mapped_column(BigInteger, ForeignKey("member_levels.id", ondelete="CASCADE"), nullable=True)
    channel_scope: Mapped[str] = mapped_column(String(8), nullable=False, default="both", comment="store|pos|both")
    min_quantity: Mapped[int] = mapped_column(Integer, nullable=False, default=1, comment="最低购买数量")
    price_type: Mapped[str] = mapped_column(String(16), nullable=False, default="fixed", comment="fixed|amount_off|percent_off")
    price_value: Mapped[Decimal] = mapped_column(DECIMAL(12, 2), nullable=False, default=Decimal("0"))
    priority: Mapped[int] = mapped_column(Integer, nullable=False, default=100, comment="越小越优先")
    is_promotion: Mapped[int] = mapped_column(TINYINT(1), nullable=False, default=0, comment="是否为促销价")
    starts_at: Mapped[datetime | None] = mapped_column(MYSQL_DATETIME(fsp=3), nullable=True)
    ends_at: Mapped[datetime | None] = mapped_column(MYSQL_DATETIME(fsp=3), nullable=True)
    is_active: Mapped[int] = mapped_column(TINYINT(1), nullable=False, default=1)

    product = relationship("Product", back_populates="price_rules", lazy="noload", foreign_keys=[product_id])

    def __repr__(self) -> str:
        return (
            f"<ProductPriceRule id={self.id} product={self.product_id} variant={self.variant_id} "
            f"channel={self.channel_scope} min_qty={self.min_quantity} type={self.price_type}>"
        )