models_db.py 26 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618
  1. """
  2. SQLAlchemy 数据库模型定义
  3. """
  4. from datetime import datetime
  5. from typing import Optional
  6. from sqlalchemy import (
  7. Column,
  8. String,
  9. Integer,
  10. BigInteger,
  11. Text,
  12. Enum,
  13. Boolean,
  14. JSON,
  15. TIMESTAMP,
  16. Index,
  17. UniqueConstraint,
  18. )
  19. from sqlalchemy.orm import declarative_base
  20. from sqlalchemy.sql import func
  21. PKBigInt = BigInteger().with_variant(Integer, "sqlite")
  22. Base = declarative_base()
  23. class User(Base):
  24. """用户表模型"""
  25. __tablename__ = "users"
  26. id = Column(PKBigInt, primary_key=True, autoincrement=True)
  27. user_id = Column(String(64), unique=True, nullable=False, index=True, comment="用户唯一标识(UUID)")
  28. # 基本信息
  29. username = Column(String(100), nullable=False, comment="用户名/昵称")
  30. user_type = Column(Enum("guest", "registered", name="user_type"), default="guest", comment="用户类型")
  31. bot_name = Column(String(100), nullable=True, comment="用户给机器人取的名字")
  32. # 注册信息(可选)
  33. phone = Column(String(20), unique=True, nullable=True, index=True, comment="手机号")
  34. email = Column(String(200), unique=True, nullable=True, index=True, comment="邮箱")
  35. wechat_openid = Column(String(100), unique=True, nullable=True, index=True, comment="微信OpenID")
  36. qq_openid = Column(String(100), unique=True, nullable=True, index=True, comment="QQ OpenID")
  37. qq_unionid = Column(String(128), unique=True, nullable=True, index=True, comment="QQ UnionID")
  38. google_email = Column(String(200), unique=True, nullable=True, index=True, comment="Google邮箱")
  39. # 认证信息
  40. is_verified = Column(Boolean, default=False, comment="是否已认证")
  41. registered_at = Column(TIMESTAMP, nullable=True, comment="注册时间")
  42. # 统计信息
  43. total_messages = Column(Integer, default=0, comment="总消息数")
  44. total_rounds = Column(Integer, default=0, comment="总对话轮次")
  45. total_speech_duration_ms = Column(BigInteger, default=0, comment="累积说话时长(毫秒)")
  46. user_level = Column(Integer, default=0, nullable=False, comment="用户等级(0-5)")
  47. last_active_at = Column(TIMESTAMP, nullable=True, comment="最后活跃时间")
  48. # 背景图片
  49. current_background_image_id = Column(String(64), nullable=True, comment="当前使用的背景图片ID")
  50. created_at = Column(TIMESTAMP, server_default=func.current_timestamp(), comment="创建时间")
  51. updated_at = Column(TIMESTAMP, server_default=func.current_timestamp(), onupdate=func.current_timestamp(), comment="更新时间")
  52. def to_dict(self) -> dict:
  53. """转换为字典"""
  54. return {
  55. "user_id": self.user_id,
  56. "username": self.username,
  57. "user_type": self.user_type,
  58. "bot_name": self.bot_name,
  59. "phone": self.phone,
  60. "email": self.email,
  61. "wechat_openid": self.wechat_openid,
  62. "qq_openid": self.qq_openid,
  63. "qq_unionid": self.qq_unionid,
  64. "google_email": self.google_email,
  65. "is_verified": self.is_verified,
  66. "registered_at": self.registered_at.isoformat() if self.registered_at else None,
  67. "total_messages": self.total_messages,
  68. "total_rounds": self.total_rounds,
  69. "total_speech_duration_ms": self.total_speech_duration_ms,
  70. "user_level": self.user_level,
  71. "last_active_at": self.last_active_at.isoformat() if self.last_active_at else None,
  72. "current_background_image_id": self.current_background_image_id,
  73. "created_at": self.created_at.isoformat() if self.created_at else None,
  74. }
  75. class Message(Base):
  76. """消息表模型"""
  77. __tablename__ = "messages"
  78. id = Column(PKBigInt, primary_key=True, autoincrement=True)
  79. message_id = Column(String(64), unique=True, nullable=False, comment="消息ID")
  80. user_id = Column(String(64), nullable=False, index=True, comment="用户ID")
  81. # 消息内容
  82. role = Column(Enum("user", "assistant", "system", name="role"), nullable=False, comment="角色")
  83. content = Column(Text, nullable=False, comment="消息内容")
  84. # 轮次信息
  85. round_number = Column(Integer, nullable=False, comment="对话轮次(从1开始)")
  86. # 扩展信息
  87. emotion = Column(String(50), nullable=True, comment="情感标签")
  88. message_metadata = Column(JSON, nullable=True, comment="扩展信息(音频路径、识别耗时等)")
  89. token_count = Column(Integer, nullable=True, comment="Token数量")
  90. created_at = Column(TIMESTAMP, server_default=func.current_timestamp(), index=True, comment="创建时间")
  91. # 复合索引
  92. __table_args__ = (
  93. Index("idx_user_round", "user_id", "round_number"),
  94. )
  95. def to_dict(self) -> dict:
  96. """转换为字典"""
  97. return {
  98. "message_id": self.message_id,
  99. "user_id": self.user_id,
  100. "role": self.role,
  101. "content": self.content,
  102. "round_number": self.round_number,
  103. "emotion": self.emotion,
  104. "metadata": self.message_metadata, # 对外保持 metadata 字段名
  105. "token_count": self.token_count,
  106. "created_at": self.created_at.isoformat() if self.created_at else None,
  107. }
  108. class ConversationSummary(Base):
  109. """对话总结表模型"""
  110. __tablename__ = "conversation_summaries"
  111. id = Column(PKBigInt, primary_key=True, autoincrement=True)
  112. summary_id = Column(String(64), unique=True, nullable=False, comment="总结ID")
  113. user_id = Column(String(64), nullable=False, index=True, comment="用户ID")
  114. # 总结范围
  115. start_round = Column(Integer, nullable=False, comment="起始轮次")
  116. end_round = Column(Integer, nullable=False, comment="结束轮次")
  117. # 总结内容
  118. summary_text = Column(Text, nullable=False, comment="总结内容")
  119. summary_type = Column(Enum("auto", "manual", name="summary_type"), default="auto", comment="总结类型")
  120. # 统计信息
  121. message_count = Column(Integer, nullable=True, comment="总结的消息数量")
  122. token_count = Column(Integer, nullable=True, comment="总结文本Token数")
  123. created_at = Column(TIMESTAMP, server_default=func.current_timestamp(), comment="创建时间")
  124. # 复合索引和唯一约束
  125. __table_args__ = (
  126. Index("idx_user_end_round", "user_id", "end_round"),
  127. UniqueConstraint("user_id", "start_round", "end_round", name="uk_user_round_range"),
  128. )
  129. def to_dict(self) -> dict:
  130. """转换为字典"""
  131. return {
  132. "summary_id": self.summary_id,
  133. "user_id": self.user_id,
  134. "start_round": self.start_round,
  135. "end_round": self.end_round,
  136. "summary_text": self.summary_text,
  137. "summary_type": self.summary_type,
  138. "message_count": self.message_count,
  139. "token_count": self.token_count,
  140. "created_at": self.created_at.isoformat() if self.created_at else None,
  141. }
  142. class MissionType(Base):
  143. """任务类型配置"""
  144. __tablename__ = "mission_types"
  145. id = Column(PKBigInt, primary_key=True, autoincrement=True)
  146. type_key = Column(String(64), unique=True, nullable=False, index=True, comment="任务类型唯一标识")
  147. display_name = Column(String(100), nullable=False, comment="任务显示名称")
  148. description = Column(String(255), nullable=True, comment="类型描述")
  149. is_repeatable = Column(Boolean, default=False, comment="是否允许重复触发")
  150. is_active = Column(Boolean, default=True, comment="是否启用")
  151. metadata_json = Column("metadata", JSON, nullable=True, comment="扩展配置")
  152. created_at = Column(TIMESTAMP, server_default=func.current_timestamp(), comment="创建时间")
  153. updated_at = Column(
  154. TIMESTAMP,
  155. server_default=func.current_timestamp(),
  156. onupdate=func.current_timestamp(),
  157. comment="更新时间"
  158. )
  159. __table_args__ = (
  160. Index("idx_mission_type_active", "is_active"),
  161. )
  162. class MissionEnergyStone(Base):
  163. """任务能量石(首次对话奖励)"""
  164. __tablename__ = "mission_energy_stones"
  165. id = Column(PKBigInt, primary_key=True, autoincrement=True)
  166. stone_id = Column(String(64), unique=True, nullable=False, index=True, comment="能量石ID")
  167. user_id = Column(String(64), nullable=False, index=True, comment="用户ID")
  168. request_id = Column(String(64), nullable=True, comment="触发的请求ID")
  169. appear_reason = Column(String(64), nullable=False, default="first_dialog", comment="触发原因")
  170. mission_type = Column(String(64), nullable=False, default="first_dialog", comment="任务类型")
  171. appear_at = Column(TIMESTAMP, server_default=func.current_timestamp(), comment="出现时间")
  172. clicked_at = Column(TIMESTAMP, nullable=True, comment="点击时间")
  173. auto_removed = Column(Boolean, default=False, comment="是否由系统自动消失")
  174. metadata_json = Column("metadata", JSON, nullable=True, comment="扩展信息")
  175. created_at = Column(TIMESTAMP, server_default=func.current_timestamp(), comment="创建时间")
  176. updated_at = Column(
  177. TIMESTAMP,
  178. server_default=func.current_timestamp(),
  179. onupdate=func.current_timestamp(),
  180. comment="更新时间"
  181. )
  182. __table_args__ = (
  183. Index("idx_mission_stone_user", "user_id", "appear_at"),
  184. Index("idx_mission_type_user", "mission_type", "user_id"),
  185. )
  186. class ConversationEnergyStone(Base):
  187. """对话能量石(按时长发放)"""
  188. __tablename__ = "conversation_energy_stones"
  189. id = Column(PKBigInt, primary_key=True, autoincrement=True)
  190. stone_id = Column(String(64), unique=True, nullable=False, index=True, comment="能量石ID")
  191. user_id = Column(String(64), nullable=False, index=True, comment="用户ID")
  192. request_id = Column(String(64), nullable=True, comment="触发的请求ID")
  193. threshold_index = Column(Integer, nullable=False, comment="达到的阈值序号(30秒一档)")
  194. total_duration_ms = Column(BigInteger, nullable=False, comment="达到阈值后的累计说话时长")
  195. duration_increment_ms = Column(Integer, nullable=False, comment="本次增加的说话时长")
  196. appear_at = Column(TIMESTAMP, server_default=func.current_timestamp(), comment="出现时间")
  197. clicked_at = Column(TIMESTAMP, nullable=True, comment="点击时间")
  198. auto_removed = Column(Boolean, default=False, comment="是否由系统自动消失")
  199. metadata_json = Column("metadata", JSON, nullable=True, comment="扩展信息")
  200. created_at = Column(TIMESTAMP, server_default=func.current_timestamp(), comment="创建时间")
  201. updated_at = Column(
  202. TIMESTAMP,
  203. server_default=func.current_timestamp(),
  204. onupdate=func.current_timestamp(),
  205. comment="更新时间"
  206. )
  207. __table_args__ = (
  208. UniqueConstraint("user_id", "threshold_index", name="uk_conversation_stone_threshold"),
  209. Index("idx_conversation_stone_user_threshold", "user_id", "threshold_index"),
  210. )
  211. class UserUIState(Base):
  212. """用户前端可视化状态表(页面与提示状态)"""
  213. __tablename__ = "user_ui_states"
  214. id = Column(PKBigInt, primary_key=True, autoincrement=True)
  215. user_id = Column(String(64), unique=True, nullable=False, index=True, comment="用户ID(关联 users.user_id)")
  216. # 页面与路由状态
  217. current_page = Column(String(64), nullable=True, comment="当前业务流程页面标识")
  218. visited_routes = Column(JSON, nullable=True, comment="已访问路由列表")
  219. # 新手引导与提示状态
  220. has_shown_tap_to_chat = Column(Boolean, default=False, nullable=False, comment="是否已展示过 TapToChat 提示")
  221. has_shown_cancel_tooltip = Column(Boolean, default=False, nullable=False, comment="是否已展示过取消按钮提示")
  222. has_shown_send_tooltip = Column(Boolean, default=False, nullable=False, comment="是否已展示过发送按钮提示")
  223. has_shown_progress_bar_tooltip = Column(Boolean, default=False, nullable=False, comment="是否已展示过进度条提示")
  224. has_shown_mission_stone_modal = Column(Boolean, default=False, nullable=False, comment="是否已展示过任务能量石弹窗")
  225. has_shown_conversation_stone_modal = Column(Boolean, default=False, nullable=False, comment="是否已展示过对话能量石弹窗")
  226. has_clicked_mission_stone = Column(Boolean, default=False, nullable=False, comment="是否已点击过任务能量石(控制光晕显示)")
  227. has_clicked_conversation_stone = Column(Boolean, default=False, nullable=False, comment="是否已点击过对话能量石(控制光晕显示)")
  228. # Avatar-C 解锁状态
  229. avatar_c_unlocked = Column(Boolean, default=False, nullable=False, comment="是否已解锁 Avatar-C")
  230. has_shared_avatar = Column(Boolean, default=False, nullable=False, comment="是否已分享头像奖励")
  231. # 当前选中的形象
  232. current_avatar_id = Column(String(64), nullable=True, comment="当前使用的形象ID(关联 avatars.avatar_id)")
  233. created_at = Column(TIMESTAMP, server_default=func.current_timestamp(), comment="创建时间")
  234. updated_at = Column(
  235. TIMESTAMP,
  236. server_default=func.current_timestamp(),
  237. onupdate=func.current_timestamp(),
  238. comment="更新时间",
  239. )
  240. def to_dict(self) -> dict:
  241. return {
  242. "user_id": self.user_id,
  243. "current_page": self.current_page,
  244. "visited_routes": self.visited_routes or [],
  245. "has_shown_tap_to_chat": self.has_shown_tap_to_chat,
  246. "has_shown_cancel_tooltip": self.has_shown_cancel_tooltip,
  247. "has_shown_send_tooltip": self.has_shown_send_tooltip,
  248. "has_shown_progress_bar_tooltip": self.has_shown_progress_bar_tooltip,
  249. "has_shown_mission_stone_modal": self.has_shown_mission_stone_modal,
  250. "has_shown_conversation_stone_modal": self.has_shown_conversation_stone_modal,
  251. "has_clicked_mission_stone": self.has_clicked_mission_stone,
  252. "has_clicked_conversation_stone": self.has_clicked_conversation_stone,
  253. "avatar_c_unlocked": self.avatar_c_unlocked,
  254. "has_shared_avatar": self.has_shared_avatar,
  255. "current_avatar_id": self.current_avatar_id,
  256. "created_at": self.created_at.isoformat() if self.created_at else None,
  257. "updated_at": self.updated_at.isoformat() if self.updated_at else None,
  258. }
  259. class Avatar(Base):
  260. """形象配置表"""
  261. __tablename__ = "avatars"
  262. id = Column(PKBigInt, primary_key=True, autoincrement=True)
  263. avatar_id = Column(String(64), unique=True, nullable=False, index=True, comment="形象唯一标识")
  264. # 基本信息
  265. avatar_name = Column(String(100), nullable=False, comment="形象名称")
  266. description = Column(String(255), nullable=True, comment="形象描述")
  267. is_active = Column(Boolean, default=True, nullable=False, comment="是否启用")
  268. # 扩展信息
  269. metadata_json = Column("metadata", JSON, nullable=True, comment="扩展配置")
  270. created_at = Column(TIMESTAMP, server_default=func.current_timestamp(), comment="创建时间")
  271. updated_at = Column(
  272. TIMESTAMP,
  273. server_default=func.current_timestamp(),
  274. onupdate=func.current_timestamp(),
  275. comment="更新时间",
  276. )
  277. __table_args__ = (
  278. Index("idx_avatar_is_active", "is_active"),
  279. )
  280. def to_dict(self) -> dict:
  281. return {
  282. "avatar_id": self.avatar_id,
  283. "avatar_name": self.avatar_name,
  284. "description": self.description,
  285. "is_active": self.is_active,
  286. "metadata": self.metadata_json,
  287. "created_at": self.created_at.isoformat() if self.created_at else None,
  288. "updated_at": self.updated_at.isoformat() if self.updated_at else None,
  289. }
  290. class AvatarAsset(Base):
  291. """形象动作/情绪资源表"""
  292. __tablename__ = "avatar_assets"
  293. id = Column(PKBigInt, primary_key=True, autoincrement=True)
  294. avatar_id = Column(String(64), nullable=False, index=True, comment="形象ID")
  295. state = Column(
  296. Enum(
  297. "default",
  298. "positive",
  299. "negative",
  300. "talking",
  301. "idle",
  302. "sleep",
  303. name="avatar_state",
  304. ),
  305. nullable=False,
  306. default="default",
  307. comment="动作/情绪状态",
  308. )
  309. image_url = Column(String(500), nullable=False, comment="GIF 资源 URL")
  310. thumbnail_url = Column(String(500), nullable=True, comment="缩略图URL")
  311. metadata_json = Column("metadata", JSON, nullable=True, comment="扩展配置")
  312. created_at = Column(TIMESTAMP, server_default=func.current_timestamp(), comment="创建时间")
  313. updated_at = Column(
  314. TIMESTAMP,
  315. server_default=func.current_timestamp(),
  316. onupdate=func.current_timestamp(),
  317. comment="更新时间",
  318. )
  319. __table_args__ = (
  320. UniqueConstraint("avatar_id", "state", name="uk_avatar_asset_state"),
  321. Index("idx_avatar_asset_avatar_id", "avatar_id"),
  322. )
  323. def to_dict(self) -> dict:
  324. return {
  325. "id": self.id,
  326. "avatar_id": self.avatar_id,
  327. "state": self.state,
  328. "image_url": self.image_url,
  329. "thumbnail_url": self.thumbnail_url,
  330. "metadata": self.metadata_json,
  331. "created_at": self.created_at.isoformat() if self.created_at else None,
  332. "updated_at": self.updated_at.isoformat() if self.updated_at else None,
  333. }
  334. class UserAvatar(Base):
  335. """用户已拥有的形象"""
  336. __tablename__ = "user_avatars"
  337. id = Column(PKBigInt, primary_key=True, autoincrement=True)
  338. user_id = Column(String(64), nullable=False, index=True, comment="用户ID")
  339. avatar_id = Column(String(64), nullable=False, index=True, comment="形象ID")
  340. obtained_at = Column(TIMESTAMP, server_default=func.current_timestamp(), comment="获得时间")
  341. obtained_type = Column(
  342. Enum("default", "register", "level_up", "mission", "purchase", "gift", "event", name="avatar_obtained_type"),
  343. default="default",
  344. comment="获得方式",
  345. )
  346. obtained_source = Column(String(64), nullable=True, comment="获得来源(等级/任务/订单等)")
  347. metadata_json = Column("metadata", JSON, nullable=True, comment="扩展信息")
  348. created_at = Column(TIMESTAMP, server_default=func.current_timestamp(), comment="创建时间")
  349. __table_args__ = (
  350. UniqueConstraint("user_id", "avatar_id", name="uk_user_avatar"),
  351. Index("idx_user_avatar_user_obtained", "user_id", "obtained_at"),
  352. )
  353. def to_dict(self) -> dict:
  354. return {
  355. "id": self.id,
  356. "user_id": self.user_id,
  357. "avatar_id": self.avatar_id,
  358. "obtained_at": self.obtained_at.isoformat() if self.obtained_at else None,
  359. "obtained_type": self.obtained_type,
  360. "obtained_source": self.obtained_source,
  361. "metadata": self.metadata_json,
  362. "created_at": self.created_at.isoformat() if self.created_at else None,
  363. }
  364. def _attach_metadata_alias(model_cls):
  365. """Allow using `metadata` keyword/attribute while storing in metadata_json."""
  366. original_init = model_cls.__init__
  367. def __init__(self, *args, **kwargs):
  368. metadata_value = kwargs.pop("metadata", None)
  369. original_init(self, *args, **kwargs)
  370. if metadata_value is not None:
  371. setattr(self, "metadata_json", metadata_value)
  372. model_cls.__init__ = __init__
  373. def getter(self):
  374. return getattr(self, "metadata_json")
  375. def setter(self, value):
  376. setattr(self, "metadata_json", value)
  377. model_cls.metadata = property(getter, setter)
  378. class BackgroundImage(Base):
  379. """背景图片配置表"""
  380. __tablename__ = "background_images"
  381. id = Column(PKBigInt, primary_key=True, autoincrement=True)
  382. image_id = Column(String(64), unique=True, nullable=False, index=True, comment="背景图片唯一标识")
  383. # 基本信息
  384. image_name = Column(String(100), nullable=False, comment="背景图片名称")
  385. image_url = Column(String(500), nullable=False, comment="背景图片URL")
  386. thumbnail_url = Column(String(500), nullable=True, comment="缩略图URL")
  387. # 解锁条件
  388. unlock_type = Column(Enum("default", "level", "purchase", "mission", name="unlock_type"),
  389. default="default", comment="解锁类型")
  390. unlock_level = Column(Integer, nullable=True, comment="解锁所需等级(unlock_type=level时)")
  391. unlock_mission_type = Column(String(64), nullable=True, comment="解锁所需任务类型(unlock_type=mission时)")
  392. # 显示信息
  393. display_order = Column(Integer, default=0, comment="显示顺序")
  394. is_active = Column(Boolean, default=True, comment="是否启用")
  395. description = Column(String(255), nullable=True, comment="背景图片描述")
  396. # 扩展信息
  397. metadata_json = Column("metadata", JSON, nullable=True, comment="扩展配置")
  398. created_at = Column(TIMESTAMP, server_default=func.current_timestamp(), comment="创建时间")
  399. updated_at = Column(TIMESTAMP, server_default=func.current_timestamp(),
  400. onupdate=func.current_timestamp(), comment="更新时间")
  401. __table_args__ = (
  402. Index("idx_unlock_type", "unlock_type"),
  403. Index("idx_display_order", "display_order"),
  404. )
  405. def to_dict(self) -> dict:
  406. """转换为字典"""
  407. return {
  408. "image_id": self.image_id,
  409. "image_name": self.image_name,
  410. "image_url": self.image_url,
  411. "thumbnail_url": self.thumbnail_url,
  412. "unlock_type": self.unlock_type,
  413. "unlock_level": self.unlock_level,
  414. "unlock_mission_type": self.unlock_mission_type,
  415. "display_order": self.display_order,
  416. "is_active": self.is_active,
  417. "description": self.description,
  418. "metadata": self.metadata_json,
  419. "created_at": self.created_at.isoformat() if self.created_at else None,
  420. "updated_at": self.updated_at.isoformat() if self.updated_at else None,
  421. }
  422. class UserBackgroundImage(Base):
  423. """用户背景图片关联表(记录用户已获得的背景图片)"""
  424. __tablename__ = "user_background_images"
  425. id = Column(PKBigInt, primary_key=True, autoincrement=True)
  426. user_id = Column(String(64), nullable=False, index=True, comment="用户ID")
  427. image_id = Column(String(64), nullable=False, index=True, comment="背景图片ID")
  428. # 获得信息
  429. obtained_at = Column(TIMESTAMP, server_default=func.current_timestamp(), comment="获得时间")
  430. obtained_type = Column(Enum("default", "level_up", "purchase", "mission", "gift", name="obtained_type"),
  431. default="default", comment="获得方式")
  432. obtained_source = Column(String(64), nullable=True, comment="获得来源(任务类型、等级等)")
  433. # 扩展信息
  434. metadata_json = Column("metadata", JSON, nullable=True, comment="扩展信息")
  435. created_at = Column(TIMESTAMP, server_default=func.current_timestamp(), comment="创建时间")
  436. __table_args__ = (
  437. UniqueConstraint("user_id", "image_id", name="uk_user_background_image"),
  438. Index("idx_user_obtained", "user_id", "obtained_at"),
  439. )
  440. def to_dict(self) -> dict:
  441. """转换为字典"""
  442. return {
  443. "id": self.id,
  444. "user_id": self.user_id,
  445. "image_id": self.image_id,
  446. "obtained_at": self.obtained_at.isoformat() if self.obtained_at else None,
  447. "obtained_type": self.obtained_type,
  448. "obtained_source": self.obtained_source,
  449. "metadata": self.metadata_json,
  450. "created_at": self.created_at.isoformat() if self.created_at else None,
  451. }
  452. class VerificationCode(Base):
  453. """验证码表模型"""
  454. __tablename__ = "verification_codes"
  455. id = Column(PKBigInt, primary_key=True, autoincrement=True)
  456. code_id = Column(String(64), unique=True, nullable=False, index=True, comment="验证码ID")
  457. # 接收方信息(手机号或邮箱,根据发送类型二选一)
  458. area_code = Column(String(10), nullable=True, comment="手机区号(如 +86)")
  459. phone = Column(String(20), nullable=True, index=True, comment="手机号")
  460. email = Column(String(200), nullable=True, index=True, comment="邮箱")
  461. # 验证码信息
  462. code = Column(String(10), nullable=False, comment="验证码")
  463. send_type = Column(
  464. Enum("domestic_sms", "international_sms", "email", name="send_type"),
  465. nullable=False,
  466. comment="发送类型:国内短信、国外短信、邮箱"
  467. )
  468. # 状态信息
  469. is_sent = Column(Boolean, default=False, nullable=False, comment="是否已发送")
  470. is_valid = Column(Boolean, default=True, nullable=False, comment="是否有效")
  471. # 时间信息
  472. created_at = Column(TIMESTAMP, server_default=func.current_timestamp(), index=True, comment="创建时间")
  473. sent_at = Column(TIMESTAMP, nullable=True, comment="发送时间")
  474. # 扩展信息
  475. metadata_json = Column("metadata", JSON, nullable=True, comment="扩展信息(发送结果、错误信息等)")
  476. __table_args__ = (
  477. Index("idx_verification_code_phone", "phone", "is_valid", "created_at"),
  478. Index("idx_verification_code_email", "email", "is_valid", "created_at"),
  479. Index("idx_verification_code_send_type", "send_type", "is_sent", "is_valid"),
  480. )
  481. def to_dict(self) -> dict:
  482. """转换为字典"""
  483. return {
  484. "code_id": self.code_id,
  485. "area_code": self.area_code,
  486. "phone": self.phone,
  487. "email": self.email,
  488. "code": self.code,
  489. "send_type": self.send_type,
  490. "is_sent": self.is_sent,
  491. "is_valid": self.is_valid,
  492. "created_at": self.created_at.isoformat() if self.created_at else None,
  493. "sent_at": self.sent_at.isoformat() if self.sent_at else None,
  494. "metadata": self.metadata_json,
  495. }
  496. for _model in (
  497. MissionType,
  498. MissionEnergyStone,
  499. ConversationEnergyStone,
  500. BackgroundImage,
  501. UserBackgroundImage,
  502. Avatar,
  503. AvatarAsset,
  504. UserAvatar,
  505. ):
  506. _attach_metadata_alias(_model)