265 lines
10 KiB
Python
265 lines
10 KiB
Python
from sqlalchemy import Column, Integer, String, DateTime, ForeignKey, Date, Table, Float, Text
|
|
from sqlalchemy.orm import relationship
|
|
from sqlalchemy.sql import func
|
|
from database import Base
|
|
|
|
# Association tables
|
|
food_ingredient_association = Table('food_ingredient_association', Base.metadata,
|
|
Column('food_id', Integer, ForeignKey('foods.id'), primary_key=True),
|
|
Column('ingredient_id', Integer, ForeignKey('ingredients.id'), primary_key=True)
|
|
)
|
|
|
|
food_additive_association = Table('food_additive_association', Base.metadata,
|
|
Column('food_id', Integer, ForeignKey('foods.id'), primary_key=True),
|
|
Column('additive_id', Integer, ForeignKey('additives.id'), primary_key=True)
|
|
)
|
|
|
|
|
|
class User(Base):
|
|
__tablename__ = "users"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
phone_number = Column(String, unique=True, index=True, nullable=True)
|
|
hashed_password = Column(String, nullable=True)
|
|
avatar_url = Column(String, nullable=True)
|
|
created_at = Column(DateTime(timezone=True), server_default=func.now())
|
|
|
|
preferences = relationship("UserPreference", back_populates="owner")
|
|
search_history = relationship("SearchHistory", back_populates="user")
|
|
family_members = relationship("FamilyMember", back_populates="owner")
|
|
recipes = relationship("Recipe", back_populates="author")
|
|
posts = relationship("Post", back_populates="author")
|
|
comments = relationship("Comment", back_populates="author")
|
|
cart_items = relationship("CartItem", back_populates="user")
|
|
orders = relationship("Order", back_populates="user")
|
|
oauth_accounts = relationship("OAuthAccount", back_populates="user")
|
|
favorites = relationship("Favorite", back_populates="user")
|
|
|
|
|
|
class OAuthAccount(Base):
|
|
__tablename__ = "oauth_accounts"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
provider = Column(String, nullable=False)
|
|
openid = Column(String, nullable=False, index=True)
|
|
user_id = Column(Integer, ForeignKey("users.id"))
|
|
|
|
user = relationship("User", back_populates="oauth_accounts")
|
|
|
|
class FamilyMember(Base):
|
|
__tablename__ = "family_members"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
name = Column(String, index=True)
|
|
date_of_birth = Column(Date)
|
|
gender = Column(String)
|
|
owner_id = Column(Integer, ForeignKey("users.id"))
|
|
|
|
owner = relationship("User", back_populates="family_members")
|
|
health_profiles = relationship("HealthProfile", back_populates="member")
|
|
|
|
class HealthProfile(Base):
|
|
__tablename__ = "health_profiles"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
member_id = Column(Integer, ForeignKey("family_members.id"))
|
|
profile_type = Column(String, index=True)
|
|
value = Column(String)
|
|
|
|
member = relationship("FamilyMember", back_populates="health_profiles")
|
|
|
|
class UserPreference(Base):
|
|
__tablename__ = "user_preferences"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
category = Column(String, index=True)
|
|
value = Column(String)
|
|
user_id = Column(Integer, ForeignKey("users.id"))
|
|
|
|
owner = relationship("User", back_populates="preferences")
|
|
|
|
class Food(Base):
|
|
__tablename__ = "foods"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
barcode = Column(String, unique=True, index=True, nullable=False)
|
|
name = Column(String, index=True)
|
|
brand = Column(String, index=True)
|
|
category = Column(String, index=True)
|
|
safety_rating = Column(String)
|
|
nutrition_rating = Column(String)
|
|
shi_score = Column(Float)
|
|
created_at = Column(DateTime(timezone=True), server_default=func.now())
|
|
|
|
ingredients = relationship("Ingredient", secondary=food_ingredient_association, back_populates="foods")
|
|
additives = relationship("Additive", secondary=food_additive_association, back_populates="foods")
|
|
posts = relationship("Post", back_populates="food")
|
|
|
|
class Ingredient(Base):
|
|
__tablename__ = "ingredients"
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
name = Column(String, unique=True, index=True)
|
|
description = Column(Text)
|
|
risk_level = Column(Integer)
|
|
|
|
foods = relationship("Food", secondary=food_ingredient_association, back_populates="ingredients")
|
|
recipes = relationship("RecipeIngredient", back_populates="ingredient")
|
|
|
|
class Additive(Base):
|
|
__tablename__ = "additives"
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
name = Column(String, unique=True, index=True)
|
|
e_number = Column(String, unique=True, index=True, nullable=True)
|
|
description = Column(Text)
|
|
risk_level = Column(Integer)
|
|
|
|
foods = relationship("Food", secondary=food_additive_association, back_populates="additives")
|
|
|
|
class SearchHistory(Base):
|
|
__tablename__ = "search_history"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
term = Column(String, index=True, nullable=False)
|
|
user_id = Column(Integer, ForeignKey("users.id"), nullable=True)
|
|
created_at = Column(DateTime(timezone=True), server_default=func.now())
|
|
|
|
user = relationship("User", back_populates="search_history")
|
|
|
|
class Article(Base):
|
|
__tablename__ = "articles"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
title = Column(String, index=True)
|
|
content = Column(Text)
|
|
author = Column(String)
|
|
category = Column(String, index=True)
|
|
cover_image_url = Column(String)
|
|
created_at = Column(DateTime(timezone=True), server_default=func.now())
|
|
|
|
class Recipe(Base):
|
|
__tablename__ = "recipes"
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
name = Column(String, index=True)
|
|
description = Column(Text)
|
|
instructions = Column(Text)
|
|
cooking_time = Column(Integer)
|
|
author_id = Column(Integer, ForeignKey("users.id"))
|
|
|
|
author = relationship("User", back_populates="recipes")
|
|
ingredients = relationship("RecipeIngredient", back_populates="recipe")
|
|
|
|
class Topic(Base):
|
|
__tablename__ = "topics"
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
name = Column(String, unique=True, index=True, nullable=False)
|
|
description = Column(Text, nullable=True)
|
|
|
|
posts = relationship("Post", back_populates="topic")
|
|
|
|
class RecipeIngredient(Base):
|
|
__tablename__ = 'recipe_ingredients'
|
|
recipe_id = Column(Integer, ForeignKey('recipes.id'), primary_key=True)
|
|
ingredient_id = Column(Integer, ForeignKey('ingredients.id'), primary_key=True)
|
|
quantity = Column(Float, nullable=False)
|
|
unit = Column(String, nullable=False)
|
|
|
|
recipe = relationship("Recipe", back_populates="ingredients")
|
|
ingredient = relationship("Ingredient", back_populates="recipes")
|
|
|
|
class Post(Base):
|
|
__tablename__ = "posts"
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
content = Column(Text, nullable=False)
|
|
rating = Column(String)
|
|
author_id = Column(Integer, ForeignKey("users.id"))
|
|
food_id = Column(Integer, ForeignKey("foods.id"))
|
|
topic_id = Column(Integer, ForeignKey("topics.id"), nullable=True)
|
|
created_at = Column(DateTime(timezone=True), server_default=func.now())
|
|
|
|
author = relationship("User", back_populates="posts")
|
|
food = relationship("Food", back_populates="posts")
|
|
topic = relationship("Topic", back_populates="posts", foreign_keys=[topic_id])
|
|
comments = relationship("Comment", back_populates="post")
|
|
|
|
|
|
|
|
class Comment(Base):
|
|
__tablename__ = "comments"
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
content = Column(Text, nullable=False)
|
|
author_id = Column(Integer, ForeignKey("users.id"))
|
|
post_id = Column(Integer, ForeignKey("posts.id"))
|
|
created_at = Column(DateTime(timezone=True), server_default=func.now())
|
|
|
|
author = relationship("User", back_populates="comments")
|
|
post = relationship("Post", back_populates="comments")
|
|
|
|
class Favorite(Base):
|
|
__tablename__ = "favorites"
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
user_id = Column(Integer, ForeignKey("users.id"))
|
|
entity_type = Column(String, nullable=False) # e.g., 'post', 'product', 'recipe'
|
|
entity_id = Column(Integer, nullable=False)
|
|
tag = Column(String, nullable=True) # e.g., 'like', 'favorite', 'red_list'
|
|
|
|
user = relationship("User", back_populates="favorites")
|
|
# Note: Generic relationship to entity is handled in logic, not via direct SQLAlchemy relationship
|
|
|
|
# --- E-commerce Models ---
|
|
class Product(Base):
|
|
__tablename__ = "products"
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
name = Column(String, index=True)
|
|
description = Column(Text)
|
|
price = Column(Float, nullable=False)
|
|
stock = Column(Integer, default=0)
|
|
image_url = Column(String)
|
|
|
|
class CartItem(Base):
|
|
__tablename__ = "cart_items"
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
user_id = Column(Integer, ForeignKey("users.id"))
|
|
product_id = Column(Integer, ForeignKey("products.id"))
|
|
quantity = Column(Integer, default=1)
|
|
|
|
user = relationship("User", back_populates="cart_items")
|
|
product = relationship("Product")
|
|
|
|
class Order(Base):
|
|
__tablename__ = "orders"
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
user_id = Column(Integer, ForeignKey("users.id"))
|
|
total_price = Column(Float, nullable=False)
|
|
status = Column(String, default="pending") # e.g., pending, paid, shipped, delivered
|
|
created_at = Column(DateTime(timezone=True), server_default=func.now())
|
|
|
|
user = relationship("User", back_populates="orders")
|
|
items = relationship("OrderItem", back_populates="order")
|
|
|
|
class OrderItem(Base):
|
|
__tablename__ = "order_items"
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
order_id = Column(Integer, ForeignKey("orders.id"))
|
|
product_id = Column(Integer, ForeignKey("products.id"))
|
|
quantity = Column(Integer)
|
|
price = Column(Float) # Price at the time of order
|
|
|
|
order = relationship("Order", back_populates="items")
|
|
product = relationship("Product")
|
|
|
|
class SubmittedFood(Base):
|
|
__tablename__ = "submitted_foods"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
barcode = Column(String, index=True, nullable=False)
|
|
name = Column(String, index=True)
|
|
brand = Column(String, index=True, nullable=True)
|
|
# Storing ingredients and additives as JSON strings for simplicity in submission
|
|
ingredients_text = Column(Text, nullable=True)
|
|
additives_text = Column(Text, nullable=True)
|
|
|
|
status = Column(String, default="pending") # pending, approved, rejected
|
|
submitted_by_id = Column(Integer, ForeignKey("users.id"))
|
|
created_at = Column(DateTime(timezone=True), server_default=func.now())
|
|
|
|
submitted_by = relationship("User") |