188 lines
7.3 KiB
Python
188 lines
7.3 KiB
Python
"""SQLAlchemy ORM models — mirror db/schema.sql table + column names EXACTLY.
|
|
|
|
Do NOT rename tables/columns here: the schema.sql in /opt/polaris/db is canonical
|
|
and the database is already initialized from it.
|
|
"""
|
|
from datetime import datetime
|
|
from decimal import Decimal
|
|
|
|
from sqlalchemy import (
|
|
BigInteger,
|
|
Boolean,
|
|
Column,
|
|
DateTime,
|
|
ForeignKey,
|
|
Integer,
|
|
Numeric,
|
|
Text,
|
|
UniqueConstraint,
|
|
func,
|
|
text,
|
|
)
|
|
from sqlalchemy.dialects.postgresql import JSONB, UUID
|
|
from sqlalchemy.orm import relationship
|
|
|
|
from app.database import Base
|
|
|
|
|
|
class Supplier(Base):
|
|
__tablename__ = "suppliers"
|
|
|
|
id = Column(UUID(as_uuid=True), primary_key=True, server_default=text("gen_random_uuid()"))
|
|
name = Column(Text, nullable=False)
|
|
adapter_type = Column(Text, nullable=False)
|
|
api_endpoint = Column(Text)
|
|
account_id = Column(Text)
|
|
credentials_enc = Column(Text)
|
|
fulfillment_caps = Column(JSONB, default=list)
|
|
shipping_regions = Column(JSONB, default=list)
|
|
reliability_score = Column(Numeric(5, 2), default=Decimal("50.00"))
|
|
status = Column(Text, nullable=False, default="active")
|
|
created_at = Column(DateTime(timezone=True), server_default=func.now())
|
|
updated_at = Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())
|
|
|
|
supplier_products = relationship("SupplierProduct", back_populates="supplier")
|
|
performance = relationship(
|
|
"SupplierPerformance", back_populates="supplier", uselist=False, lazy="joined"
|
|
)
|
|
|
|
|
|
class Product(Base):
|
|
__tablename__ = "products"
|
|
|
|
id = Column(UUID(as_uuid=True), primary_key=True, server_default=text("gen_random_uuid()"))
|
|
sku = Column(Text, unique=True, nullable=False)
|
|
title = Column(Text, nullable=False)
|
|
description = Column(Text)
|
|
category = Column(Text)
|
|
brand = Column(Text)
|
|
manufacturer = Column(Text)
|
|
upc_ean_gtin = Column(Text)
|
|
images = Column(JSONB, default=list)
|
|
dimensions = Column(JSONB)
|
|
weight_grams = Column(Numeric(10, 2))
|
|
cost = Column(Numeric(12, 2), nullable=False, default=Decimal("0"))
|
|
retail_price = Column(Numeric(12, 2))
|
|
min_price = Column(Numeric(12, 2))
|
|
max_price = Column(Numeric(12, 2))
|
|
status = Column(Text, nullable=False, default="discovered")
|
|
lifecycle = Column(JSONB, default=dict)
|
|
created_at = Column(DateTime(timezone=True), server_default=func.now())
|
|
updated_at = Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())
|
|
|
|
supplier_products = relationship("SupplierProduct", back_populates="product")
|
|
|
|
|
|
class SupplierProduct(Base):
|
|
__tablename__ = "supplier_products"
|
|
__table_args__ = (
|
|
UniqueConstraint("supplier_id", "supplier_sku", name="supplier_products_supplier_id_supplier_sku_key"),
|
|
)
|
|
|
|
id = Column(UUID(as_uuid=True), primary_key=True, server_default=text("gen_random_uuid()"))
|
|
supplier_id = Column(UUID(as_uuid=True), ForeignKey("suppliers.id", ondelete="CASCADE"), nullable=False)
|
|
supplier_sku = Column(Text, nullable=False)
|
|
product_id = Column(UUID(as_uuid=True), ForeignKey("products.id", ondelete="SET NULL"))
|
|
supplier_cost = Column(Numeric(12, 2), nullable=False, default=Decimal("0"))
|
|
supplier_inventory = Column(Integer, nullable=False, default=0)
|
|
shipping_cost = Column(Numeric(12, 2), default=Decimal("0"))
|
|
shipping_time = Column(Text)
|
|
last_updated = Column(DateTime(timezone=True), server_default=func.now())
|
|
|
|
supplier = relationship("Supplier", back_populates="supplier_products")
|
|
product = relationship("Product", back_populates="supplier_products")
|
|
|
|
|
|
class Customer(Base):
|
|
__tablename__ = "customers"
|
|
|
|
id = Column(UUID(as_uuid=True), primary_key=True, server_default=text("gen_random_uuid()"))
|
|
email = Column(Text, unique=True, nullable=False)
|
|
name = Column(Text)
|
|
shipping_addr = Column(JSONB)
|
|
created_at = Column(DateTime(timezone=True), server_default=func.now())
|
|
|
|
|
|
class Order(Base):
|
|
__tablename__ = "orders"
|
|
|
|
id = Column(UUID(as_uuid=True), primary_key=True, server_default=text("gen_random_uuid()"))
|
|
order_number = Column(Text, unique=True, nullable=False)
|
|
customer_id = Column(UUID(as_uuid=True), ForeignKey("customers.id"))
|
|
items = Column(JSONB, nullable=False)
|
|
retail_total = Column(Numeric(12, 2), nullable=False, default=Decimal("0"))
|
|
supplier_id = Column(UUID(as_uuid=True), ForeignKey("suppliers.id"))
|
|
supplier_cost = Column(Numeric(12, 2), default=Decimal("0"))
|
|
shipping_cost = Column(Numeric(12, 2), default=Decimal("0"))
|
|
fees = Column(Numeric(12, 2), default=Decimal("0"))
|
|
profit = Column(Numeric(12, 2), default=Decimal("0"))
|
|
status = Column(Text, nullable=False, default="new")
|
|
tracking = Column(Text)
|
|
carrier = Column(Text)
|
|
btcpay_invoice_id = Column(Text)
|
|
supplier_order_ref = Column(Text)
|
|
created_at = Column(DateTime(timezone=True), server_default=func.now())
|
|
updated_at = Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())
|
|
|
|
customer = relationship("Customer")
|
|
|
|
|
|
class PriceHistory(Base):
|
|
__tablename__ = "price_history"
|
|
|
|
id = Column(BigInteger, primary_key=True, autoincrement=True)
|
|
product_id = Column(UUID(as_uuid=True), ForeignKey("products.id", ondelete="CASCADE"), nullable=False)
|
|
price = Column(Numeric(12, 2), nullable=False)
|
|
reason = Column(Text)
|
|
created_at = Column(DateTime(timezone=True), server_default=func.now())
|
|
|
|
|
|
class InventoryHistory(Base):
|
|
__tablename__ = "inventory_history"
|
|
|
|
id = Column(BigInteger, primary_key=True, autoincrement=True)
|
|
supplier_product_id = Column(
|
|
UUID(as_uuid=True), ForeignKey("supplier_products.id", ondelete="CASCADE"), nullable=False
|
|
)
|
|
inventory = Column(Integer, nullable=False)
|
|
created_at = Column(DateTime(timezone=True), server_default=func.now())
|
|
|
|
|
|
class SupplierPerformance(Base):
|
|
__tablename__ = "supplier_performance"
|
|
|
|
id = Column(UUID(as_uuid=True), primary_key=True, server_default=text("gen_random_uuid()"))
|
|
supplier_id = Column(UUID(as_uuid=True), ForeignKey("suppliers.id", ondelete="CASCADE"), nullable=False)
|
|
fulfillment_rate = Column(Numeric(5, 2), default=Decimal("100.00"))
|
|
avg_shipping_days = Column(Numeric(6, 2))
|
|
cancellation_rate = Column(Numeric(5, 2), default=Decimal("0.00"))
|
|
stock_accuracy = Column(Numeric(5, 2), default=Decimal("100.00"))
|
|
return_rate = Column(Numeric(5, 2), default=Decimal("0.00"))
|
|
updated_at = Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())
|
|
|
|
supplier = relationship("Supplier", back_populates="performance")
|
|
|
|
|
|
class PriceRule(Base):
|
|
__tablename__ = "price_rules"
|
|
|
|
id = Column(Integer, primary_key=True, autoincrement=True)
|
|
min_cost = Column(Numeric(12, 2), nullable=False, default=Decimal("0"))
|
|
max_cost = Column(Numeric(12, 2))
|
|
markup_pct = Column(Numeric(6, 2), nullable=False)
|
|
min_margin_pct = Column(Numeric(6, 2), default=Decimal("0"))
|
|
active = Column(Boolean, default=True)
|
|
created_at = Column(DateTime(timezone=True), server_default=func.now())
|
|
|
|
|
|
class AuditLog(Base):
|
|
__tablename__ = "audit_logs"
|
|
|
|
id = Column(BigInteger, primary_key=True, autoincrement=True)
|
|
actor = Column(Text)
|
|
action = Column(Text, nullable=False)
|
|
entity = Column(Text)
|
|
entity_id = Column(Text)
|
|
detail = Column(JSONB)
|
|
created_at = Column(DateTime(timezone=True), server_default=func.now())
|