"""进销存账本模型。表结构一次建到位，一期只点亮账本核心逻辑。"""
from datetime import datetime
from decimal import Decimal

from sqlalchemy import (
    BigInteger, CheckConstraint, DECIMAL, Date, Index, Integer, JSON, String, Text,
    UniqueConstraint,
)
from sqlalchemy.dialects.mysql import DATETIME as MYSQL_DATETIME, TINYINT
from sqlalchemy.orm import Mapped, mapped_column

from app.core.models.base import Base, TenantMixin, TimestampMixin

_TBL = {"mysql_engine": "InnoDB", "mysql_charset": "utf8mb4",
        "mysql_collate": "utf8mb4_unicode_ci"}


class InventorySettings(Base, TenantMixin, TimestampMixin):
    """租户级进销存配置。独立于 plugin_configs.config JSON，以便校验与变更审计。"""
    __tablename__ = "inventory_settings"
    __table_args__ = (UniqueConstraint("tenant_id", name="uk_inv_settings_tenant"), _TBL)

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    lifecycle_state: Mapped[str] = mapped_column(String(20), nullable=False, default="active",
                                                 comment="active/arrears/suspended/retired")
    warehouse_mode: Mapped[str] = mapped_column(String(20), nullable=False, default="simple",
                                                comment="simple=仅仓库 / zoned=仓库→库区→库位")
    cost_method: Mapped[str] = mapped_column(String(20), nullable=False, default="moving_avg",
                                             comment="moving_avg/fifo/batch_actual")
    batch_enabled: Mapped[int] = mapped_column(TINYINT(1), nullable=False, default=0)
    expiry_enabled: Mapped[int] = mapped_column(TINYINT(1), nullable=False, default=0)
    serial_enabled: Mapped[int] = mapped_column(TINYINT(1), nullable=False, default=0)
    qc_enabled: Mapped[int] = mapped_column(TINYINT(1), nullable=False, default=0)
    allow_negative: Mapped[int] = mapped_column(TINYINT(1), nullable=False, default=0)
    purchase_approval: Mapped[int] = mapped_column(TINYINT(1), nullable=False, default=0)
    period_close_enabled: Mapped[int] = mapped_column(TINYINT(1), nullable=False, default=0)
    base_currency: Mapped[str] = mapped_column(String(8), nullable=False, default="NZD")
    default_warehouse_id: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    sales_policy: Mapped[dict | None] = mapped_column(JSON, nullable=True)
    closed_through: Mapped[str | None] = mapped_column(String(7), nullable=True,
                                                       comment="已关账到 YYYY-MM，NULL=未关账")


class InventoryProductProfile(Base, TenantMixin, TimestampMixin):
    """商品/变体级库存规则；租户设置只控制能力是否可用。"""
    __tablename__ = "inventory_product_profiles"
    __table_args__ = (
        UniqueConstraint("tenant_id", "product_id", "variant_id",
                         name="uk_inv_profile_tenant_product_variant"),
        Index("ix_inv_profile_tenant_tracking", "tenant_id", "tracking_type"),
        _TBL,
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    product_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    variant_id: Mapped[int] = mapped_column(BigInteger, nullable=False, default=0)
    tracking_type: Mapped[str] = mapped_column(String(20), nullable=False, default="none")
    qc_required: Mapped[int] = mapped_column(TINYINT(1), nullable=False, default=0)
    shelf_life_days: Mapped[int | None] = mapped_column(Integer, nullable=True)
    removal_strategy: Mapped[str] = mapped_column(String(20), nullable=False, default="fifo")


class Warehouse(Base, TenantMixin, TimestampMixin):
    __tablename__ = "inventory_warehouses"
    __table_args__ = (UniqueConstraint("tenant_id", "code", name="uk_inv_wh_tenant_code"), _TBL)

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    code: Mapped[str] = mapped_column(String(40), nullable=False)
    name: Mapped[str] = mapped_column(String(120), nullable=False)
    is_active: Mapped[int] = mapped_column(TINYINT(1), nullable=False, default=1)


class Location(Base, TenantMixin, TimestampMixin):
    """库区/库位。warehouse_mode=simple 时每仓一条默认库位。"""
    __tablename__ = "inventory_locations"
    __table_args__ = (UniqueConstraint("tenant_id", "warehouse_id", "code",
                                       name="uk_inv_loc_tenant_wh_code"), _TBL)

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    warehouse_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    parent_id: Mapped[int | None] = mapped_column(BigInteger, nullable=True, comment="库区")
    code: Mapped[str] = mapped_column(String(40), nullable=False)
    name: Mapped[str] = mapped_column(String(120), nullable=False)
    is_default: Mapped[int] = mapped_column(TINYINT(1), nullable=False, default=0)
    location_type: Mapped[str] = mapped_column(String(20), nullable=False, default="internal")
    barcode: Mapped[str | None] = mapped_column(String(80), nullable=True)
    is_active: Mapped[int] = mapped_column(TINYINT(1), nullable=False, default=1)


class PutawayRule(Base, TenantMixin, TimestampMixin):
    __tablename__ = "inventory_putaway_rules"
    __table_args__ = (
        Index("ix_inv_putaway_match", "tenant_id", "warehouse_id", "priority"),
        _TBL,
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    warehouse_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    product_id: Mapped[int] = mapped_column(BigInteger, nullable=False, default=0)
    category_id: Mapped[int] = mapped_column(BigInteger, nullable=False, default=0)
    destination_location_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    priority: Mapped[int] = mapped_column(Integer, nullable=False, default=100)
    is_active: Mapped[int] = mapped_column(TINYINT(1), nullable=False, default=1)


class Batch(Base, TenantMixin, TimestampMixin):
    __tablename__ = "inventory_batches"
    __table_args__ = (UniqueConstraint("tenant_id", "product_id", "batch_no",
                                       name="uk_inv_batch_tenant_prod_no"), _TBL)

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    product_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    variant_id: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    batch_no: Mapped[str] = mapped_column(String(80), nullable=False)
    supplier_batch_no: Mapped[str | None] = mapped_column(String(80), nullable=True)
    produced_on: Mapped[datetime | None] = mapped_column(Date, nullable=True)
    expires_on: Mapped[datetime | None] = mapped_column(Date, nullable=True)
    qc_state: Mapped[str] = mapped_column(String(20), nullable=False, default="pending")
    source_operation_id: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    notes: Mapped[str | None] = mapped_column(String(500), nullable=True)


class SerialNumber(Base, TenantMixin, TimestampMixin):
    __tablename__ = "inventory_serials"
    __table_args__ = (UniqueConstraint("tenant_id", "serial_no", name="uk_inv_serial_tenant_no"), _TBL)

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    product_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    variant_id: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    serial_no: Mapped[str] = mapped_column(String(120), nullable=False)
    stock_state: Mapped[str] = mapped_column(String(20), nullable=False, default="sellable")
    location_id: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    warehouse_id: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    batch_id: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    source_operation_id: Mapped[int | None] = mapped_column(BigInteger, nullable=True)


class InventorySerialEvent(Base, TenantMixin, TimestampMixin):
    __tablename__ = "inventory_serial_events"
    __table_args__ = (Index("ix_inv_serial_event_trace", "tenant_id", "serial_id", "id"), _TBL)

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    serial_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    operation_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    event_type: Mapped[str] = mapped_column(String(30), nullable=False)
    from_warehouse_id: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    to_warehouse_id: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    from_location_id: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    to_location_id: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    from_state: Mapped[str | None] = mapped_column(String(20), nullable=True)
    to_state: Mapped[str | None] = mapped_column(String(20), nullable=True)


class InventoryTransaction(Base, TenantMixin, TimestampMixin):
    """唯一库存事实来源。只增不改。"""
    __tablename__ = "inventory_transactions"
    __table_args__ = (
        UniqueConstraint("tenant_id", "idempotency_key", name="uk_inv_txn_tenant_idem"),
        Index("ix_inv_txn_tenant_product", "tenant_id", "product_id", "id"),
        Index("ix_inv_txn_tenant_doc", "tenant_id", "doc_type", "doc_id"),
        Index("ix_inv_txn_tenant_created", "tenant_id", "created_at", "id"),
        Index("ix_inv_txn_tenant_unallocated", "tenant_id", "unallocated"),
        _TBL,
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    product_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    variant_id: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    warehouse_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    location_id: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    batch_id: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    serial_id: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    stock_state: Mapped[str] = mapped_column(String(20), nullable=False, default="sellable",
                                             comment="sellable/reserved/in_transit/qc/defective")
    qty_delta: Mapped[Decimal] = mapped_column(DECIMAL(16, 4), nullable=False)
    qty_before: Mapped[Decimal] = mapped_column(DECIMAL(16, 4), nullable=False)
    qty_after: Mapped[Decimal] = mapped_column(DECIMAL(16, 4), nullable=False)
    unit_cost: Mapped[Decimal | None] = mapped_column(DECIMAL(16, 4), nullable=True)
    amount: Mapped[Decimal | None] = mapped_column(DECIMAL(16, 4), nullable=True)
    doc_type: Mapped[str] = mapped_column(String(40), nullable=False)
    doc_id: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    idempotency_key: Mapped[str] = mapped_column(String(120), nullable=False)
    operator_id: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    reason: Mapped[str | None] = mapped_column(String(255), nullable=True)
    unallocated: Mapped[int] = mapped_column(TINYINT(1), nullable=False, default=0,
                                             comment="未分配负库存异常桶：FEFO 无可用批次时置 1")


class InventoryBalance(Base, TenantMixin, TimestampMixin):
    """余额表。由流水维护，不提供直接编辑接口。"""
    __tablename__ = "inventory_balances"
    __table_args__ = (
        UniqueConstraint("tenant_id", "product_id", "variant_id", "warehouse_id",
                         "location_id", "batch_id", "stock_state",
                         name="uk_inv_bal_dimension"),
        Index("ix_inv_bal_tenant_product", "tenant_id", "product_id"),
        _TBL,
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    product_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    variant_id: Mapped[int] = mapped_column(BigInteger, nullable=False, default=0,
                                            comment="0 表示无规格，避免 NULL 破坏唯一约束")
    warehouse_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    location_id: Mapped[int] = mapped_column(BigInteger, nullable=False, default=0)
    batch_id: Mapped[int] = mapped_column(BigInteger, nullable=False, default=0)
    stock_state: Mapped[str] = mapped_column(String(20), nullable=False, default="sellable")
    qty: Mapped[Decimal] = mapped_column(DECIMAL(16, 4), nullable=False, default=Decimal("0"))


class CostLayer(Base, TenantMixin, TimestampMixin):
    """FIFO / 批次实际成本的成本层。移动加权平均只用最新一层。"""
    __tablename__ = "inventory_cost_layers"
    __table_args__ = (
        Index("ix_inv_cost_tenant_product", "tenant_id", "product_id", "id"),
        Index("ix_inv_cost_source_operation", "tenant_id", "source_operation_id"),
        Index("ix_inv_cost_source_move", "tenant_id", "source_move_id"),
        _TBL,
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    product_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    variant_id: Mapped[int] = mapped_column(BigInteger, nullable=False, default=0)
    batch_id: Mapped[int] = mapped_column(BigInteger, nullable=False, default=0)
    source_operation_id: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    source_move_id: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    qty_in: Mapped[Decimal] = mapped_column(DECIMAL(16, 4), nullable=False)
    qty_consumed: Mapped[Decimal] = mapped_column(DECIMAL(16, 4), nullable=False, default=Decimal("0"))
    unit_cost: Mapped[Decimal] = mapped_column(DECIMAL(16, 4), nullable=False)
    additional_cost: Mapped[Decimal] = mapped_column(DECIMAL(16, 4), nullable=False, default=Decimal("0"))
    additional_cost_consumed: Mapped[Decimal] = mapped_column(
        DECIMAL(16, 4), nullable=False, default=Decimal("0")
    )
    frozen_at: Mapped[datetime | None] = mapped_column(MYSQL_DATETIME(fsp=3), nullable=True,
                                                       comment="成本方法切换时冻结")


class Supplier(Base, TenantMixin, TimestampMixin):
    __tablename__ = "inventory_suppliers"
    __table_args__ = (UniqueConstraint("tenant_id", "code", name="uk_inv_supplier_tenant_code"), _TBL)

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    code: Mapped[str] = mapped_column(String(40), nullable=False)
    name: Mapped[str] = mapped_column(String(160), nullable=False)
    currency: Mapped[str] = mapped_column(String(8), nullable=False, default="NZD")
    contact: Mapped[str | None] = mapped_column(String(120), nullable=True)
    email: Mapped[str | None] = mapped_column(String(160), nullable=True)
    phone: Mapped[str | None] = mapped_column(String(60), nullable=True)
    tax_id: Mapped[str | None] = mapped_column(String(80), nullable=True)
    contacts: Mapped[list | None] = mapped_column(JSON, nullable=True)
    addresses: Mapped[list | None] = mapped_column(JSON, nullable=True)
    attachment_urls: Mapped[list | None] = mapped_column(JSON, nullable=True)
    delivery_notes: Mapped[str | None] = mapped_column(Text, nullable=True)
    payment_notes: Mapped[str | None] = mapped_column(Text, nullable=True)
    is_active: Mapped[int] = mapped_column(TINYINT(1), nullable=False, default=1)


class SupplierProduct(Base, TenantMixin, TimestampMixin):
    __tablename__ = "inventory_supplier_products"
    __table_args__ = (
        UniqueConstraint("tenant_id", "supplier_id", "product_id", "variant_id",
                         name="uk_inv_supplier_product"),
        Index("ix_inv_supplier_product_lookup", "tenant_id", "product_id", "variant_id"),
        _TBL,
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    supplier_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    product_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    variant_id: Mapped[int] = mapped_column(BigInteger, nullable=False, default=0)
    supplier_sku: Mapped[str | None] = mapped_column(String(120), nullable=True)
    purchase_uom: Mapped[str] = mapped_column(String(40), nullable=False, default="unit")
    uom_factor: Mapped[Decimal] = mapped_column(DECIMAL(16, 6), nullable=False, default=Decimal("1"))
    unit_price: Mapped[Decimal] = mapped_column(DECIMAL(16, 4), nullable=False, default=Decimal("0"))
    currency: Mapped[str] = mapped_column(String(8), nullable=False, default="NZD")
    min_order_qty: Mapped[Decimal] = mapped_column(DECIMAL(16, 4), nullable=False, default=Decimal("1"))
    order_multiple: Mapped[Decimal] = mapped_column(DECIMAL(16, 4), nullable=False, default=Decimal("1"))
    lead_time_days: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    priority: Mapped[int] = mapped_column(Integer, nullable=False, default=100)
    valid_from: Mapped[datetime | None] = mapped_column(Date, nullable=True)
    valid_to: Mapped[datetime | None] = mapped_column(Date, nullable=True)
    is_active: Mapped[int] = mapped_column(TINYINT(1), nullable=False, default=1)


class PurchaseOrder(Base, TenantMixin, TimestampMixin):
    __tablename__ = "inventory_purchase_orders"
    __table_args__ = (UniqueConstraint("tenant_id", "po_no", name="uk_inv_po_tenant_no"),
                      Index("ix_inv_po_tenant_status", "tenant_id", "status"), _TBL)

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    po_no: Mapped[str] = mapped_column(String(60), nullable=False)
    supplier_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    warehouse_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    status: Mapped[str] = mapped_column(String(20), nullable=False, default="draft",
                                        comment="draft/pending_approval/confirmed/partially_received/received/closed/cancelled")
    currency: Mapped[str] = mapped_column(String(8), nullable=False, default="NZD")
    fx_rate: Mapped[Decimal] = mapped_column(DECIMAL(16, 6), nullable=False, default=Decimal("1"))
    total_amount: Mapped[Decimal] = mapped_column(DECIMAL(16, 4), nullable=False, default=Decimal("0"))
    requester_id: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    approved_by: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    order_date: Mapped[datetime | None] = mapped_column(Date, nullable=True)
    supplier_promise_date: Mapped[datetime | None] = mapped_column(Date, nullable=True)
    planned_arrival_date: Mapped[datetime | None] = mapped_column(Date, nullable=True)
    approved_at: Mapped[datetime | None] = mapped_column(MYSQL_DATETIME(fsp=3), nullable=True)
    closed_reason: Mapped[str | None] = mapped_column(String(255), nullable=True)


class PurchaseOrderLine(Base, TenantMixin, TimestampMixin):
    __tablename__ = "inventory_purchase_order_lines"
    __table_args__ = (Index("ix_inv_pol_tenant_po", "tenant_id", "po_id"), _TBL)

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    po_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    product_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    variant_id: Mapped[int] = mapped_column(BigInteger, nullable=False, default=0)
    qty: Mapped[Decimal] = mapped_column(DECIMAL(16, 4), nullable=False)
    unit_cost: Mapped[Decimal] = mapped_column(DECIMAL(16, 4), nullable=False, comment="原币单价")
    received_qty: Mapped[Decimal] = mapped_column(DECIMAL(16, 4), nullable=False, default=Decimal("0"))
    rejected_qty: Mapped[Decimal] = mapped_column(DECIMAL(16, 4), nullable=False, default=Decimal("0"))
    returned_qty: Mapped[Decimal] = mapped_column(DECIMAL(16, 4), nullable=False, default=Decimal("0"))
    expected_date: Mapped[datetime | None] = mapped_column(Date, nullable=True)
    purchase_uom: Mapped[str] = mapped_column(String(40), nullable=False, default="unit")
    uom_factor: Mapped[Decimal] = mapped_column(DECIMAL(16, 6), nullable=False, default=Decimal("1"))
    batch_no: Mapped[str | None] = mapped_column(String(80), nullable=True)
    expires_on: Mapped[datetime | None] = mapped_column(Date, nullable=True)


class PurchaseApprovalRule(Base, TenantMixin, TimestampMixin):
    __tablename__ = "inventory_purchase_approval_rules"
    __table_args__ = (
        UniqueConstraint("tenant_id", "approval_level", "warehouse_id", "category_id",
                         name="uk_inv_purchase_approval_rule"),
        _TBL,
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    min_amount: Mapped[Decimal] = mapped_column(DECIMAL(16, 4), nullable=False, default=Decimal("0"))
    warehouse_id: Mapped[int] = mapped_column(BigInteger, nullable=False, default=0)
    category_id: Mapped[int] = mapped_column(BigInteger, nullable=False, default=0)
    approval_level: Mapped[int] = mapped_column(Integer, nullable=False, default=1)
    is_active: Mapped[int] = mapped_column(TINYINT(1), nullable=False, default=1)


class PurchaseApproval(Base, TenantMixin, TimestampMixin):
    __tablename__ = "inventory_purchase_approvals"
    __table_args__ = (
        UniqueConstraint("tenant_id", "po_id", "approval_level", "approver_id",
                         name="uk_inv_purchase_approval_decision"),
        Index("ix_inv_purchase_approval_po", "tenant_id", "po_id", "approval_level"),
        _TBL,
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    po_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    approval_level: Mapped[int] = mapped_column(Integer, nullable=False)
    approver_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    decision: Mapped[str] = mapped_column(String(20), nullable=False)
    comment: Mapped[str | None] = mapped_column(String(500), nullable=True)
    decided_at: Mapped[datetime] = mapped_column(MYSQL_DATETIME(fsp=3), nullable=False)


class InventoryOperation(Base, TenantMixin, TimestampMixin):
    """Auditable stock document header. Moves become ledger entries only when validated."""
    __tablename__ = "inventory_operations"
    __table_args__ = (
        UniqueConstraint("tenant_id", "idempotency_key", name="uk_inv_operation_tenant_idem"),
        Index("ix_inv_operation_tenant_state", "tenant_id", "operation_type", "state"),
        Index("ix_inv_operation_source", "tenant_id", "source_doc_type", "source_doc_id"),
        _TBL,
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    operation_type: Mapped[str] = mapped_column(String(30), nullable=False)
    state: Mapped[str] = mapped_column(String(20), nullable=False, default="draft")
    source_doc_type: Mapped[str | None] = mapped_column(String(40), nullable=True)
    source_doc_id: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    warehouse_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    planned_at: Mapped[datetime | None] = mapped_column(MYSQL_DATETIME(fsp=3), nullable=True)
    actual_at: Mapped[datetime | None] = mapped_column(MYSQL_DATETIME(fsp=3), nullable=True)
    idempotency_key: Mapped[str | None] = mapped_column(String(120), nullable=True)
    reference: Mapped[str | None] = mapped_column(String(120), nullable=True)
    reason: Mapped[str | None] = mapped_column(String(255), nullable=True)
    error_message: Mapped[str | None] = mapped_column(String(500), nullable=True)
    transfer_steps: Mapped[int] = mapped_column(Integer, nullable=False, default=1)
    transfer_steps: Mapped[int] = mapped_column(Integer, nullable=False, default=1)


class InventoryMove(Base, TenantMixin, TimestampMixin):
    __tablename__ = "inventory_moves"
    __table_args__ = (
        Index("ix_inv_move_operation", "tenant_id", "operation_id", "id"),
        Index("ix_inv_move_product", "tenant_id", "product_id", "variant_id"),
        _TBL,
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    operation_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    purchase_order_line_id: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    product_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    variant_id: Mapped[int] = mapped_column(BigInteger, nullable=False, default=0)
    source_warehouse_id: Mapped[int] = mapped_column(BigInteger, nullable=False, default=0)
    destination_warehouse_id: Mapped[int] = mapped_column(BigInteger, nullable=False, default=0)
    source_location_id: Mapped[int] = mapped_column(BigInteger, nullable=False, default=0)
    destination_location_id: Mapped[int] = mapped_column(BigInteger, nullable=False, default=0)
    planned_qty: Mapped[Decimal] = mapped_column(DECIMAL(16, 4), nullable=False)
    done_qty: Mapped[Decimal] = mapped_column(DECIMAL(16, 4), nullable=False)
    batch_id: Mapped[int] = mapped_column(BigInteger, nullable=False, default=0)
    batch_no: Mapped[str | None] = mapped_column(String(80), nullable=True)
    expires_on: Mapped[datetime | None] = mapped_column(Date, nullable=True)
    serial_no: Mapped[str | None] = mapped_column(String(120), nullable=True)
    serial_numbers: Mapped[list | None] = mapped_column(JSON, nullable=True)
    produced_on: Mapped[datetime | None] = mapped_column(Date, nullable=True)
    source_state: Mapped[str] = mapped_column(String(20), nullable=False, default="sellable")
    destination_state: Mapped[str] = mapped_column(String(20), nullable=False, default="sellable")
    unit_cost: Mapped[Decimal | None] = mapped_column(DECIMAL(16, 4), nullable=True)


class InventoryAllocation(Base, TenantMixin, TimestampMixin):
    __tablename__ = "inventory_allocations"
    __table_args__ = (
        Index("ix_inv_allocation_order", "tenant_id", "order_id", "order_item_id"),
        Index("ix_inv_allocation_dimension", "tenant_id", "product_id", "variant_id",
              "warehouse_id", "location_id", "batch_id"),
        CheckConstraint(
            "reserved_qty >= 0 AND picked_qty >= 0 AND shipped_qty >= 0 AND returned_qty >= 0",
            name="ck_inv_allocation_nonnegative",
        ),
        CheckConstraint(
            "picked_qty <= reserved_qty AND shipped_qty <= picked_qty AND returned_qty <= shipped_qty",
            name="ck_inv_allocation_quantity_order",
        ),
        _TBL,
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    order_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    order_item_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    product_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    variant_id: Mapped[int] = mapped_column(BigInteger, nullable=False, default=0)
    warehouse_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    location_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    batch_id: Mapped[int] = mapped_column(BigInteger, nullable=False, default=0)
    serial_id: Mapped[int] = mapped_column(BigInteger, nullable=False, default=0)
    reserved_qty: Mapped[Decimal] = mapped_column(DECIMAL(16, 4), nullable=False)
    picked_qty: Mapped[Decimal] = mapped_column(DECIMAL(16, 4), nullable=False, default=Decimal("0"))
    shipped_qty: Mapped[Decimal] = mapped_column(DECIMAL(16, 4), nullable=False, default=Decimal("0"))
    returned_qty: Mapped[Decimal] = mapped_column(DECIMAL(16, 4), nullable=False, default=Decimal("0"))


class InventoryAllocationShipment(Base, TenantMixin, TimestampMixin):
    __tablename__ = "inventory_allocation_shipments"
    __table_args__ = (
        Index("ix_inv_alloc_ship_allocation", "tenant_id", "allocation_id", "id"),
        UniqueConstraint("tenant_id", "shipment_move_id", name="uk_inv_alloc_ship_move"),
        CheckConstraint(
            "qty > 0 AND returned_qty >= 0 AND returned_qty <= qty",
            name="ck_inv_alloc_ship_quantities",
        ),
        _TBL,
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    allocation_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    shipment_move_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    qty: Mapped[Decimal] = mapped_column(DECIMAL(16, 4), nullable=False)
    returned_qty: Mapped[Decimal] = mapped_column(DECIMAL(16, 4), nullable=False, default=Decimal("0"))
    unit_cost: Mapped[Decimal] = mapped_column(DECIMAL(16, 4), nullable=False, default=Decimal("0"))


class ReorderRule(Base, TenantMixin, TimestampMixin):
    __tablename__ = "inventory_reorder_rules"
    __table_args__ = (
        UniqueConstraint("tenant_id", "product_id", "variant_id", "warehouse_id",
                         name="uk_inv_reorder_dimension"),
        Index("ix_inv_reorder_enabled", "tenant_id", "is_enabled"),
        CheckConstraint("min_qty >= 0 AND target_qty >= min_qty AND safety_qty >= 0",
                        name="ck_inv_reorder_quantities"),
        _TBL,
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    product_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    variant_id: Mapped[int] = mapped_column(BigInteger, nullable=False, default=0)
    warehouse_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    supplier_id: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    min_qty: Mapped[Decimal] = mapped_column(DECIMAL(16, 4), nullable=False)
    target_qty: Mapped[Decimal] = mapped_column(DECIMAL(16, 4), nullable=False)
    safety_qty: Mapped[Decimal] = mapped_column(DECIMAL(16, 4), nullable=False, default=Decimal("0"))
    min_order_qty: Mapped[Decimal] = mapped_column(DECIMAL(16, 4), nullable=False, default=Decimal("1"))
    order_multiple: Mapped[Decimal] = mapped_column(DECIMAL(16, 4), nullable=False, default=Decimal("1"))
    is_enabled: Mapped[int] = mapped_column(TINYINT(1), nullable=False, default=1)


class ReplenishmentSuggestion(Base, TenantMixin, TimestampMixin):
    __tablename__ = "inventory_replenishment_suggestions"
    __table_args__ = (
        UniqueConstraint("tenant_id", "rule_id", "generated_on", "generation_no",
                         name="uk_inv_replenishment_generation"),
        Index("ix_inv_replenishment_state", "tenant_id", "state", "generated_on"),
        _TBL,
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    rule_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    product_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    variant_id: Mapped[int] = mapped_column(BigInteger, nullable=False, default=0)
    warehouse_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    supplier_id: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    forecast_qty: Mapped[Decimal] = mapped_column(DECIMAL(16, 4), nullable=False)
    suggested_qty: Mapped[Decimal] = mapped_column(DECIMAL(16, 4), nullable=False)
    generated_on: Mapped[datetime] = mapped_column(Date, nullable=False)
    generation_no: Mapped[int] = mapped_column(Integer, nullable=False, default=1)
    state: Mapped[str] = mapped_column(String(20), nullable=False, default="draft")
    purchase_order_id: Mapped[int | None] = mapped_column(BigInteger, nullable=True)


class InventoryLandedCost(Base, TenantMixin, TimestampMixin):
    __tablename__ = "inventory_landed_costs"
    __table_args__ = (
        UniqueConstraint("tenant_id", "idempotency_key", name="uk_inv_landed_cost_idem"),
        Index("ix_inv_landed_cost_receipt", "tenant_id", "receipt_operation_id"),
        _TBL,
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    receipt_operation_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    allocation_method: Mapped[str] = mapped_column(String(20), nullable=False)
    total_cost: Mapped[Decimal] = mapped_column(DECIMAL(16, 4), nullable=False)
    state: Mapped[str] = mapped_column(String(20), nullable=False, default="posted")
    reference: Mapped[str | None] = mapped_column(String(120), nullable=True)
    idempotency_key: Mapped[str] = mapped_column(String(120), nullable=False)


class InventoryLandedCostLine(Base, TenantMixin, TimestampMixin):
    __tablename__ = "inventory_landed_cost_lines"
    __table_args__ = (
        UniqueConstraint("tenant_id", "landed_cost_id", "receipt_move_id",
                         name="uk_inv_landed_cost_move"),
        _TBL,
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    landed_cost_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    receipt_move_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    basis_value: Mapped[Decimal] = mapped_column(DECIMAL(16, 4), nullable=False)
    allocated_amount: Mapped[Decimal] = mapped_column(DECIMAL(16, 4), nullable=False)
    unit_cost_increment: Mapped[Decimal] = mapped_column(DECIMAL(16, 4), nullable=False)


class InventoryRevaluationLine(Base, TenantMixin, TimestampMixin):
    __tablename__ = "inventory_revaluation_lines"
    __table_args__ = (
        UniqueConstraint("tenant_id", "operation_id", "cost_layer_id",
                         name="uk_inv_revaluation_layer"),
        Index("ix_inv_revaluation_operation", "tenant_id", "operation_id"),
        _TBL,
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    operation_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    cost_layer_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    operator_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    remaining_qty: Mapped[Decimal] = mapped_column(DECIMAL(16, 4), nullable=False)
    old_unit_cost: Mapped[Decimal] = mapped_column(DECIMAL(16, 4), nullable=False)
    new_unit_cost: Mapped[Decimal] = mapped_column(DECIMAL(16, 4), nullable=False)
    value_delta: Mapped[Decimal] = mapped_column(DECIMAL(16, 4), nullable=False)


class KitRecipe(Base, TenantMixin, TimestampMixin):
    """组合装配方。仅父子件与数量，不含工单、多级 BOM、工序。"""
    __tablename__ = "inventory_kit_recipes"
    __table_args__ = (UniqueConstraint("tenant_id", "parent_product_id", "child_product_id",
                                       name="uk_inv_kit_parent_child"), _TBL)

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    parent_product_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    child_product_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    qty: Mapped[Decimal] = mapped_column(DECIMAL(16, 4), nullable=False)
    can_disassemble: Mapped[int] = mapped_column(TINYINT(1), nullable=False, default=1)