crm_project/server/app/models/erp.py

"""
ERP 供应链域 ORM 模型
映射: erp_product_categories / erp_product_skus / erp_inventory_flows
"""

from __future__ import annotations

import uuid
from datetime import datetime

from sqlalchemy import (
    Boolean,
    DateTime,
    ForeignKey,
    Index,
    Integer,
    Numeric,
    SmallInteger,
    String,
    Text,
    UniqueConstraint,
    func,
)
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.orm import Mapped, mapped_column, relationship

from app.models.base import Base


class ProductCategory(Base):
    __tablename__ = "erp_product_categories"

    id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True), primary_key=True, default=uuid.uuid4
    )
    parent_id: Mapped[uuid.UUID | None] = mapped_column(
        UUID(as_uuid=True), ForeignKey("erp_product_categories.id"), nullable=True
    )
    name: Mapped[str] = mapped_column(String(100), nullable=False)
    sort_order: Mapped[int] = mapped_column(Integer, default=0)
    created_at: Mapped[datetime] = mapped_column(DateTime, server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(
        DateTime, server_default=func.now(), onupdate=func.now()
    )
    is_deleted: Mapped[bool] = mapped_column(Boolean, default=False)


class ProductSku(Base):
    __tablename__ = "erp_product_skus"

    id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True), primary_key=True, default=uuid.uuid4
    )
    category_id: Mapped[uuid.UUID | None] = mapped_column(
        UUID(as_uuid=True), ForeignKey("erp_product_categories.id"), nullable=True
    )
    sku_code: Mapped[str] = mapped_column(String(50), unique=True, nullable=False)
    name: Mapped[str] = mapped_column(String(200), nullable=False)
    spec: Mapped[str | None] = mapped_column(String(100), nullable=True)
    standard_price: Mapped[float] = mapped_column(Numeric(12, 2), default=0)
    unit: Mapped[str] = mapped_column(String(20), default="桶")
    status: Mapped[int] = mapped_column(SmallInteger, default=1)
    created_at: Mapped[datetime] = mapped_column(DateTime, server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(
        DateTime, server_default=func.now(), onupdate=func.now()
    )
    is_deleted: Mapped[bool] = mapped_column(Boolean, default=False)

    category: Mapped[ProductCategory | None] = relationship(
        "ProductCategory", lazy="selectin"
    )


class InventoryFlow(Base):
    __tablename__ = "erp_inventory_flows"

    id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True), primary_key=True, default=uuid.uuid4
    )
    sku_id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True), ForeignKey("erp_product_skus.id"), nullable=False
    )
    company_id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True), ForeignKey("sys_companies.id"), nullable=False, index=True
    )
    change_qty: Mapped[float] = mapped_column(Numeric(12, 2), nullable=False)
    reason: Mapped[str] = mapped_column(String(50), nullable=False)
    remark: Mapped[str | None] = mapped_column(Text, nullable=True)
    purchase_unit_price: Mapped[float] = mapped_column(
        Numeric(12, 2), default=0, comment="入库采购单价"
    )
    is_special_zero_cost: Mapped[bool] = mapped_column(
        Boolean, default=False, comment="特殊零元入库标识，不参与 MWA 计算"
    )
    operator_id: Mapped[uuid.UUID | None] = mapped_column(
        UUID(as_uuid=True), ForeignKey("sys_users.id"), nullable=True
    )
    created_at: Mapped[datetime] = mapped_column(DateTime, server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(
        DateTime, server_default=func.now(), onupdate=func.now()
    )
    is_deleted: Mapped[bool] = mapped_column(Boolean, default=False)

    sku: Mapped[ProductSku | None] = relationship("ProductSku", lazy="selectin")
    operator: Mapped["SysUser | None"] = relationship("SysUser", lazy="selectin")  # noqa: F821


class ErpSkuInventory(Base):
    """SKU 分公司库存表 —— 同一 SKU 在不同公司有独立库存"""
    __tablename__ = "erp_sku_inventory"

    id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True), primary_key=True, default=uuid.uuid4
    )
    sku_id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True), ForeignKey("erp_product_skus.id"), nullable=False
    )
    company_id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True), ForeignKey("sys_companies.id"), nullable=False, index=True
    )
    stock_qty: Mapped[float] = mapped_column(Numeric(12, 2), default=0)
    warning_threshold: Mapped[float] = mapped_column(Numeric(12, 2), default=0)
    mwa_unit_cost: Mapped[float] = mapped_column(
        Numeric(12, 4), default=0,
        comment="移动加权均价 (Moving Weighted Average)"
    )
    created_at: Mapped[datetime] = mapped_column(DateTime, server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(
        DateTime, server_default=func.now(), onupdate=func.now()
    )

    __table_args__ = (
        UniqueConstraint("sku_id", "company_id", name="uq_sku_company"),
    )

    sku: Mapped[ProductSku | None] = relationship("ProductSku", lazy="selectin")