269 lines
12 KiB
Python
269 lines
12 KiB
Python
# 数据库模型 - 使用字段编码
|
||
from sqlalchemy import Column, String, Float, Boolean, DateTime, Integer, Text, ForeignKey, Date, UniqueConstraint
|
||
from sqlalchemy.orm import relationship
|
||
from sqlalchemy.sql import func
|
||
from app.core.database import Base
|
||
import uuid
|
||
|
||
|
||
def generate_uuid():
|
||
"""生成 UUID 字符串"""
|
||
return str(uuid.uuid4())
|
||
|
||
|
||
class User(Base):
|
||
__tablename__ = "users"
|
||
|
||
# f99 系统字段
|
||
f99_90_id = Column(String(36), primary_key=True, default=generate_uuid)
|
||
user_code = Column(String(10), unique=True, nullable=True, index=True) # 用户编码
|
||
f99_91_user_id = Column(String(36), unique=True, nullable=False, index=True)
|
||
f01_01_name = Column(String(255), unique=True, nullable=False, index=True) # username
|
||
email = Column(String(255), unique=True, nullable=True, index=True)
|
||
phone = Column(String(50), nullable=True)
|
||
avatar = Column(String(500), nullable=True)
|
||
address = Column(String(500), nullable=True)
|
||
bio = Column(Text, nullable=True)
|
||
password = Column(String(255), nullable=False)
|
||
role = Column(String(50), default="user")
|
||
# 时间字段
|
||
f99_92_created_at = Column(DateTime(timezone=True), server_default=func.now())
|
||
f99_93_updated_at = Column(DateTime(timezone=True), onupdate=func.now())
|
||
# 新增字段
|
||
f99_94_level = Column(String(20), default="青铜") # 会员等级
|
||
f99_95_ai_count = Column(Integer, default=0) # ai识别次数
|
||
f99_96_search_count = Column(Integer, default=0) # 寻号使用次数
|
||
f99_97_collection_count = Column(Integer, default=0) # 藏品数量
|
||
f01_06_phone_verified = Column(Boolean, default=False) # 手机号已核验
|
||
f99_98_login_count = Column(Integer, default=0) # 登录次数
|
||
f99_99_last_login = Column(DateTime(timezone=True), nullable=True) # 最后登录时间
|
||
f01_07_gender = Column(String(10), nullable=True) # 性别
|
||
f01_08_birthday = Column(Date, nullable=True) # 生日
|
||
f01_09_region = Column(String(100), nullable=True) # 地区
|
||
f01_10_realname_verified = Column(Boolean, default=False) # 实名认证
|
||
f99_100_points = Column(Integer, default=0) # 积分
|
||
f01_11_balance = Column(Float, default=0) # 余额
|
||
f01_12_total_amount = Column(Float, default=0) # 累计金额
|
||
f01_13_invite_code = Column(String(20), nullable=True) # 邀请码（自己的邀请码）
|
||
f99_101_invited_count = Column(Integer, default=0) # 通过自己邀请码注册的用户数量
|
||
|
||
collections = relationship("Collection", back_populates="user", cascade="all, delete-orphan")
|
||
operations = relationship("Operation", back_populates="user", cascade="all, delete-orphan")
|
||
custom_fields = relationship("CustomField", back_populates="user", cascade="all, delete-orphan")
|
||
|
||
|
||
class Collection(Base):
|
||
__tablename__ = "collections"
|
||
|
||
# f99 系统字段
|
||
f99_90_id = Column(String(36), primary_key=True, default=generate_uuid)
|
||
f99_91_user_id = Column(String(36), ForeignKey("users.f99_90_id", ondelete="CASCADE"), nullable=False, index=True)
|
||
f99_92_created_at = Column(DateTime(timezone=True), server_default=func.now(), index=True)
|
||
f99_93_updated_at = Column(DateTime(timezone=True), onupdate=func.now())
|
||
|
||
# f01 基本信息
|
||
f01_01_name = Column(String(255), nullable=False)
|
||
f01_02_code = Column(String(50), nullable=True, index=True)
|
||
f01_03_category = Column(String(100), nullable=False, index=True)
|
||
f01_04_status = Column(String(50), default="in_collection", index=True)
|
||
f01_05_remark = Column(Text, nullable=True)
|
||
|
||
# f02 详细字段
|
||
f02_10_prefix_serial = Column(String(50), nullable=True, index=True)
|
||
f02_11_version = Column(String(100), nullable=True, index=True)
|
||
f02_12_packaging = Column(String(100), nullable=True, index=True)
|
||
f02_13_rarity = Column(String(50), nullable=True, index=True) # 珍惜度
|
||
f02_14_number_category = Column(String(20), nullable=True, index=True) # 号码分类
|
||
|
||
# f03 评级信息
|
||
f03_20_is_graded = Column(Boolean, default=False, index=True)
|
||
f03_21_grading_company = Column(String(100), nullable=True, index=True)
|
||
f03_22_grading_score = Column(String(20), nullable=True, index=True)
|
||
f03_23_three_star = Column(Boolean, default=False, index=True)
|
||
|
||
# f04 特殊信息
|
||
f04_30_special_mark = Column(String(200), nullable=True, index=True)
|
||
f04_31_serial_feature = Column(String(100), nullable=True, index=True)
|
||
f04_32_issuer = Column(String(100), nullable=True, index=True)
|
||
f04_33_issue_year = Column(String(20), nullable=True, index=True)
|
||
f04_34_material = Column(String(50), nullable=True)
|
||
f04_35_denomination = Column(String(20), nullable=True)
|
||
f04_36_issue_quantity = Column(String(50), nullable=True)
|
||
|
||
# f05 价格信息
|
||
f05_40_cost_price = Column(Float, nullable=True, index=True)
|
||
f05_41_target_price = Column(Float, nullable=True, index=True)
|
||
f05_42_goal_price = Column(Float, nullable=True)
|
||
f05_43_repair_fee = Column(Float, nullable=True)
|
||
f05_44_grading_fee = Column(Float, nullable=True)
|
||
|
||
# f06 其他信息
|
||
f06_50_purpose = Column(String(100), nullable=True)
|
||
|
||
user = relationship("User", back_populates="collections")
|
||
images = relationship("CollectionImage", back_populates="collection", cascade="all, delete-orphan")
|
||
operations = relationship("Operation", back_populates="collection", cascade="all, delete-orphan")
|
||
|
||
|
||
class CollectionImage(Base):
|
||
__tablename__ = "collection_images"
|
||
|
||
id = Column(String(36), primary_key=True, default=generate_uuid)
|
||
collection_id = Column(String(36), ForeignKey("collections.f99_90_id", ondelete="CASCADE"), nullable=False, index=True)
|
||
filename = Column(String(255), nullable=False)
|
||
original_name = Column(String(255), nullable=True)
|
||
path = Column(String(500), nullable=True)
|
||
created_at = Column(DateTime(timezone=True), server_default=func.now())
|
||
|
||
collection = relationship("Collection", back_populates="images")
|
||
|
||
|
||
class Operation(Base):
|
||
__tablename__ = "operations"
|
||
|
||
f99_90_id = Column(String(36), primary_key=True, default=generate_uuid)
|
||
f99_91_user_id = Column(String(36), ForeignKey("collections.f99_90_id", ondelete="CASCADE"), nullable=False, index=True)
|
||
f99_92_user_id = Column(String(36), ForeignKey("users.f99_90_id", ondelete="CASCADE"), nullable=False, index=True)
|
||
type = Column(String(50), nullable=False, index=True)
|
||
price = Column(Float, nullable=True)
|
||
note = Column(Text, nullable=True)
|
||
f99_93_created_at = Column(DateTime(timezone=True), server_default=func.now(), index=True)
|
||
|
||
collection = relationship("Collection", back_populates="operations")
|
||
user = relationship("User", back_populates="operations")
|
||
|
||
|
||
class CustomField(Base):
|
||
__tablename__ = "custom_fields"
|
||
|
||
id = Column(String(36), primary_key=True, default=generate_uuid)
|
||
user_id = Column(String(36), ForeignKey("users.f99_90_id", ondelete="CASCADE"), nullable=False, index=True)
|
||
name = Column(String(100), nullable=False)
|
||
field_type = Column(String(50), default="text")
|
||
options = Column(Text, nullable=True)
|
||
required = Column(Boolean, default=False)
|
||
visible = Column(Boolean, default=True)
|
||
sort_order = Column(Integer, default=0)
|
||
created_at = Column(DateTime(timezone=True), server_default=func.now())
|
||
|
||
user = relationship("User", back_populates="custom_fields")
|
||
|
||
|
||
# 资讯模型
|
||
class Information(Base):
|
||
__tablename__ = "information"
|
||
|
||
id = Column(String(36), primary_key=True, default=generate_uuid)
|
||
user_id = Column(String(36), ForeignKey("users.f99_90_id", ondelete="CASCADE"), nullable=False, index=True)
|
||
|
||
# 信息类型: seek-寻配号, deal-成交数据, publish-发布
|
||
info_type = Column(String(20), nullable=False, index=True)
|
||
|
||
# 标题
|
||
title = Column(String(255), nullable=False)
|
||
|
||
# 内容描述
|
||
content = Column(Text, nullable=True)
|
||
|
||
# 关联藏品ID
|
||
collection_id = Column(String(36), ForeignKey("collections.f99_90_id", ondelete="SET NULL"), nullable=True)
|
||
|
||
# 期望条件 (寻配号用)
|
||
expect_category = Column(String(100), nullable=True) # 期望类别
|
||
expect_version = Column(String(100), nullable=True) # 期望版别
|
||
expect_packaging = Column(String(100), nullable=True) # 期望包装
|
||
expect_number = Column(String(50), nullable=True) # 期望号码
|
||
expect_price_min = Column(Float, nullable=True) # 期望价格区间
|
||
expect_price_max = Column(Float, nullable=True)
|
||
|
||
# 成交价格 (成交数据用)
|
||
deal_price = Column(Float, nullable=True)
|
||
deal_date = Column(Date, nullable=True)
|
||
|
||
# 评级相关字段
|
||
packaging = Column(String(50), nullable=True)
|
||
is_graded = Column(Boolean, default=False)
|
||
grading_company = Column(String(100), nullable=True)
|
||
grading_score = Column(String(50), nullable=True)
|
||
category = Column(String(100), nullable=True)
|
||
|
||
# 行情编号
|
||
deal_no = Column(String(50), nullable=True, index=True)
|
||
|
||
# 状态: active-有效, closed-已关闭, expired-已过期
|
||
status = Column(String(20), default="active", index=True)
|
||
|
||
# 匹配状态: pending-尚未匹配, matched-已经匹配
|
||
is_matched = Column(String(20), default="pending", index=True)
|
||
|
||
# 匹配的用户ID（当用户愿意交换联系方式时）
|
||
matched_user_id = Column(String(36), ForeignKey("users.f99_90_id", ondelete="SET NULL"), nullable=True)
|
||
|
||
# 匹配者的联系方式（只有发布者和匹配者可见）
|
||
matched_contact = Column(String(100), nullable=True)
|
||
|
||
# 浏览/联系次数
|
||
view_count = Column(Integer, default=0)
|
||
contact_count = Column(Integer, default=0)
|
||
|
||
created_at = Column(DateTime(timezone=True), server_default=func.now(), index=True)
|
||
updated_at = Column(DateTime(timezone=True), onupdate=func.now())
|
||
|
||
user = relationship("User", foreign_keys=[user_id])
|
||
collection = relationship("Collection")
|
||
|
||
|
||
# 资讯评论/留言
|
||
class InformationComment(Base):
|
||
__tablename__ = "information_comments"
|
||
|
||
id = Column(String(36), primary_key=True, default=generate_uuid)
|
||
information_id = Column(String(36), ForeignKey("information.id", ondelete="CASCADE"), nullable=False, index=True)
|
||
user_id = Column(String(36), ForeignKey("users.f99_90_id", ondelete="CASCADE"), nullable=False, index=True)
|
||
content = Column(Text, nullable=False)
|
||
created_at = Column(DateTime(timezone=True), server_default=func.now())
|
||
|
||
user = relationship("User", foreign_keys=[user_id])
|
||
|
||
|
||
# 资讯联系方式查看记录
|
||
class InformationContactView(Base):
|
||
__tablename__ = "information_contact_views"
|
||
|
||
id = Column(String(36), primary_key=True, default=generate_uuid)
|
||
information_id = Column(String(36), ForeignKey("information.id", ondelete="CASCADE"), nullable=False, index=True)
|
||
viewer_id = Column(String(36), ForeignKey("users.f99_90_id", ondelete="CASCADE"), nullable=False, index=True)
|
||
created_at = Column(DateTime(timezone=True), server_default=func.now())
|
||
|
||
viewer = relationship("User", foreign_keys=[viewer_id])
|
||
|
||
|
||
# 资讯关联用户 (收藏/点赞)
|
||
class InformationLike(Base):
|
||
__tablename__ = "information_likes"
|
||
|
||
id = Column(String(36), primary_key=True, default=generate_uuid)
|
||
information_id = Column(String(36), ForeignKey("information.id", ondelete="CASCADE"), nullable=False, index=True)
|
||
user_id = Column(String(36), ForeignKey("users.f99_90_id", ondelete="CASCADE"), nullable=False, index=True)
|
||
created_at = Column(DateTime(timezone=True), server_default=func.now())
|
||
|
||
__table_args__ = (
|
||
UniqueConstraint('information_id', 'user_id', name='uq_information_user'),
|
||
)
|
||
|
||
|
||
# 角色常量
|
||
class UserRole:
|
||
ADMIN = "admin" # 管理员：全部权限
|
||
EDITOR = "editor" # 信息员：可发布信息、管理资讯
|
||
USER = "user" # 普通用户：基本功能
|
||
|
||
@classmethod
|
||
def get_role_name(cls, role):
|
||
names = {
|
||
cls.ADMIN: "管理员",
|
||
cls.EDITOR: "信息员",
|
||
cls.USER: "用户"
|
||
}
|
||
return names.get(role, "用户")
|