Tolan/backend/models_db.py

"""
SQLAlchemy 数据库模型定义
"""
from datetime import datetime
from typing import Optional
from sqlalchemy import (
    Column,
    String,
    Integer,
    BigInteger,
    Text,
    Enum,
    Boolean,
    JSON,
    TIMESTAMP,
    Index,
    UniqueConstraint,
    ForeignKey,
)
from sqlalchemy.orm import declarative_base
from sqlalchemy.sql import func

PKBigInt = BigInteger().with_variant(Integer, "sqlite")

Base = declarative_base()


class User(Base):
    """用户表模型"""
    __tablename__ = "users"
    
    id = Column(PKBigInt, primary_key=True, autoincrement=True)
    user_id = Column(String(64), unique=True, nullable=False, index=True, comment="用户唯一标识(UUID)")
    
    # 基本信息
    username = Column(String(100), nullable=False, comment="用户名/昵称")
    user_type = Column(Enum("guest", "registered", name="user_type"), default="guest", comment="用户类型")
    bot_name = Column(String(100), nullable=True, comment="用户给机器人取的名字")
    
    # 注册信息（可选）
    area_code = Column(String(10), nullable=True, comment="手机区号（如 +86）")
    phone = Column(String(20), unique=True, nullable=True, index=True, comment="手机号")
    email = Column(String(200), unique=True, nullable=True, index=True, comment="邮箱")
    wechat_openid = Column(String(100), unique=True, nullable=True, index=True, comment="微信OpenID")
    qq_openid = Column(String(100), unique=True, nullable=True, index=True, comment="QQ OpenID")
    qq_unionid = Column(String(128), unique=True, nullable=True, index=True, comment="QQ UnionID")
    google_email = Column(String(200), unique=True, nullable=True, index=True, comment="Google邮箱")
    
    # 认证信息
    is_verified = Column(Boolean, default=False, comment="是否已认证")
    registered_at = Column(TIMESTAMP, nullable=True, comment="注册时间")
    
    # 统计信息
    total_messages = Column(Integer, default=0, comment="总消息数")
    total_rounds = Column(Integer, default=0, comment="总对话轮次")
    total_speech_duration_ms = Column(BigInteger, default=0, comment="累积说话时长（毫秒）")
    user_level = Column(Integer, default=0, nullable=False, comment="用户等级(0-5)")
    last_active_at = Column(TIMESTAMP, nullable=True, comment="最后活跃时间")
    
    # 背景图片
    current_background_image_id = Column(String(64), nullable=True, comment="当前使用的背景图片ID")
    
    created_at = Column(TIMESTAMP, server_default=func.current_timestamp(), comment="创建时间")
    updated_at = Column(TIMESTAMP, server_default=func.current_timestamp(), onupdate=func.current_timestamp(), comment="更新时间")
    
    def to_dict(self) -> dict:
        """转换为字典"""
        return {
            "user_id": self.user_id,
            "username": self.username,
            "user_type": self.user_type,
            "bot_name": self.bot_name,
            "area_code": self.area_code,
            "phone": self.phone,
            "email": self.email,
            "wechat_openid": self.wechat_openid,
            "qq_openid": self.qq_openid,
            "qq_unionid": self.qq_unionid,
            "google_email": self.google_email,
            "is_verified": self.is_verified,
            "registered_at": self.registered_at.isoformat() if self.registered_at else None,
            "total_messages": self.total_messages,
            "total_rounds": self.total_rounds,
            "total_speech_duration_ms": self.total_speech_duration_ms,
            "user_level": self.user_level,
            "last_active_at": self.last_active_at.isoformat() if self.last_active_at else None,
            "current_background_image_id": self.current_background_image_id,
            "created_at": self.created_at.isoformat() if self.created_at else None,
        }


class Message(Base):
    """消息表模型"""
    __tablename__ = "messages"
    
    id = Column(PKBigInt, primary_key=True, autoincrement=True)
    message_id = Column(String(64), unique=True, nullable=False, comment="消息ID")
    user_id = Column(String(64), nullable=False, index=True, comment="用户ID")
    
    # 消息内容
    role = Column(Enum("user", "assistant", "system", name="role"), nullable=False, comment="角色")
    content = Column(Text, nullable=False, comment="消息内容")
    
    # 轮次信息
    round_number = Column(Integer, nullable=False, comment="对话轮次(从1开始)")
    
    # 扩展信息
    emotion = Column(String(50), nullable=True, comment="情感标签")
    message_metadata = Column(JSON, nullable=True, comment="扩展信息(音频路径、识别耗时等)")
    token_count = Column(Integer, nullable=True, comment="Token数量")
    
    created_at = Column(TIMESTAMP, server_default=func.current_timestamp(), index=True, comment="创建时间")
    
    # 复合索引
    __table_args__ = (
        Index("idx_user_round", "user_id", "round_number"),
    )
    
    def to_dict(self) -> dict:
        """转换为字典"""
        return {
            "message_id": self.message_id,
            "user_id": self.user_id,
            "role": self.role,
            "content": self.content,
            "round_number": self.round_number,
            "emotion": self.emotion,
            "metadata": self.message_metadata,  # 对外保持 metadata 字段名
            "token_count": self.token_count,
            "created_at": self.created_at.isoformat() if self.created_at else None,
        }


class LLMPrompt(Base):
    """LLM提示词记录表模型"""
    __tablename__ = "llm_prompts"
    
    id = Column(PKBigInt, primary_key=True, autoincrement=True)
    prompt_id = Column(String(64), unique=True, nullable=False, index=True, comment="提示词记录ID")
    
    # 关联消息表（外键关联到messages.id）
    message_id = Column(PKBigInt, ForeignKey("messages.id", ondelete="CASCADE"), nullable=False, index=True, comment="关联的消息ID")
    
    # 用户信息
    user_id = Column(String(64), nullable=False, index=True, comment="用户ID")
    round_number = Column(Integer, nullable=False, comment="对话轮次")
    
    # 提示词内容
    system_prompt = Column(Text, nullable=False, comment="系统提示词（完整内容）")
    runtime_context = Column(Text, nullable=True, comment="运行时上下文")
    messages_json = Column(JSON, nullable=False, comment="完整的messages数组（JSON格式）")
    
    # LLM调用参数
    model_name = Column(String(100), nullable=False, comment="模型名称")
    temperature = Column(String(20), nullable=True, comment="温度参数")
    max_tokens = Column(Integer, nullable=True, comment="最大Token数")
    stream = Column(Boolean, default=True, comment="是否流式输出")
    
    # 统计信息
    prompt_token_count = Column(Integer, nullable=True, comment="提示词Token数")
    total_token_count = Column(Integer, nullable=True, comment="总Token数（包含响应）")
    
    # 扩展信息
    metadata_json = Column(JSON, nullable=True, comment="扩展信息（调用耗时、错误信息等）")
    
    created_at = Column(TIMESTAMP, server_default=func.current_timestamp(), index=True, comment="创建时间")
    
    # 索引
    __table_args__ = (
        Index("idx_llm_prompt_message", "message_id"),
        Index("idx_llm_prompt_user_round", "user_id", "round_number"),
        Index("idx_llm_prompt_created", "created_at"),
    )
    
    def to_dict(self) -> dict:
        """转换为字典"""
        return {
            "prompt_id": self.prompt_id,
            "message_id": self.message_id,
            "user_id": self.user_id,
            "round_number": self.round_number,
            "system_prompt": self.system_prompt,
            "runtime_context": self.runtime_context,
            "messages": self.messages_json,  # 对外保持 messages 字段名
            "model_name": self.model_name,
            "temperature": self.temperature,
            "max_tokens": self.max_tokens,
            "stream": self.stream,
            "prompt_token_count": self.prompt_token_count,
            "total_token_count": self.total_token_count,
            "metadata": self.metadata_json,  # 对外保持 metadata 字段名
            "created_at": self.created_at.isoformat() if self.created_at else None,
        }


class ConversationSummary(Base):
    """对话总结表模型"""
    __tablename__ = "conversation_summaries"
    
    id = Column(PKBigInt, primary_key=True, autoincrement=True)
    summary_id = Column(String(64), unique=True, nullable=False, comment="总结ID")
    user_id = Column(String(64), nullable=False, index=True, comment="用户ID")
    
    # 总结范围
    start_round = Column(Integer, nullable=False, comment="起始轮次")
    end_round = Column(Integer, nullable=False, comment="结束轮次")
    
    # 总结内容
    summary_text = Column(Text, nullable=False, comment="总结内容")
    summary_type = Column(Enum("auto", "manual", name="summary_type"), default="auto", comment="总结类型")
    
    # 统计信息
    message_count = Column(Integer, nullable=True, comment="总结的消息数量")
    token_count = Column(Integer, nullable=True, comment="总结文本Token数")
    
    created_at = Column(TIMESTAMP, server_default=func.current_timestamp(), comment="创建时间")
    
    # 复合索引和唯一约束
    __table_args__ = (
        Index("idx_user_end_round", "user_id", "end_round"),
        UniqueConstraint("user_id", "start_round", "end_round", name="uk_user_round_range"),
    )
    
    def to_dict(self) -> dict:
        """转换为字典"""
        return {
            "summary_id": self.summary_id,
            "user_id": self.user_id,
            "start_round": self.start_round,
            "end_round": self.end_round,
            "summary_text": self.summary_text,
            "summary_type": self.summary_type,
            "message_count": self.message_count,
            "token_count": self.token_count,
            "created_at": self.created_at.isoformat() if self.created_at else None,
        }


class MissionType(Base):
    """任务类型配置"""
    __tablename__ = "mission_types"

    id = Column(PKBigInt, primary_key=True, autoincrement=True)
    type_key = Column(String(64), unique=True, nullable=False, index=True, comment="任务类型唯一标识")
    display_name = Column(String(100), nullable=False, comment="任务显示名称")
    description = Column(String(255), nullable=True, comment="类型描述")
    is_repeatable = Column(Boolean, default=False, comment="是否允许重复触发")
    is_active = Column(Boolean, default=True, comment="是否启用")
    metadata_json = Column("metadata", JSON, nullable=True, comment="扩展配置")
    created_at = Column(TIMESTAMP, server_default=func.current_timestamp(), comment="创建时间")
    updated_at = Column(
        TIMESTAMP,
        server_default=func.current_timestamp(),
        onupdate=func.current_timestamp(),
        comment="更新时间"
    )

    __table_args__ = (
        Index("idx_mission_type_active", "is_active"),
    )


class MissionEnergyStone(Base):
    """任务能量石（首次对话奖励）"""
    __tablename__ = "mission_energy_stones"

    id = Column(PKBigInt, primary_key=True, autoincrement=True)
    stone_id = Column(String(64), unique=True, nullable=False, index=True, comment="能量石ID")
    user_id = Column(String(64), nullable=False, index=True, comment="用户ID")
    request_id = Column(String(64), nullable=True, comment="触发的请求ID")
    appear_reason = Column(String(64), nullable=False, default="first_dialog", comment="触发原因")
    mission_type = Column(String(64), nullable=False, default="first_dialog", comment="任务类型")
    appear_at = Column(TIMESTAMP, server_default=func.current_timestamp(), comment="出现时间")
    clicked_at = Column(TIMESTAMP, nullable=True, comment="点击时间")
    auto_removed = Column(Boolean, default=False, comment="是否由系统自动消失")
    metadata_json = Column("metadata", JSON, nullable=True, comment="扩展信息")
    created_at = Column(TIMESTAMP, server_default=func.current_timestamp(), comment="创建时间")
    updated_at = Column(
        TIMESTAMP,
        server_default=func.current_timestamp(),
        onupdate=func.current_timestamp(),
        comment="更新时间"
    )

    __table_args__ = (
        Index("idx_mission_stone_user", "user_id", "appear_at"),
        Index("idx_mission_type_user", "mission_type", "user_id"),
    )


class ConversationEnergyStone(Base):
    """对话能量石（按时长发放）"""
    __tablename__ = "conversation_energy_stones"

    id = Column(PKBigInt, primary_key=True, autoincrement=True)
    stone_id = Column(String(64), unique=True, nullable=False, index=True, comment="能量石ID")
    user_id = Column(String(64), nullable=False, index=True, comment="用户ID")
    request_id = Column(String(64), nullable=True, comment="触发的请求ID")
    threshold_index = Column(Integer, nullable=False, comment="达到的阈值序号（30秒一档）")
    total_duration_ms = Column(BigInteger, nullable=False, comment="达到阈值后的累计说话时长")
    duration_increment_ms = Column(Integer, nullable=False, comment="本次增加的说话时长")
    appear_at = Column(TIMESTAMP, server_default=func.current_timestamp(), comment="出现时间")
    clicked_at = Column(TIMESTAMP, nullable=True, comment="点击时间")
    auto_removed = Column(Boolean, default=False, comment="是否由系统自动消失")
    metadata_json = Column("metadata", JSON, nullable=True, comment="扩展信息")
    created_at = Column(TIMESTAMP, server_default=func.current_timestamp(), comment="创建时间")
    updated_at = Column(
        TIMESTAMP,
        server_default=func.current_timestamp(),
        onupdate=func.current_timestamp(),
        comment="更新时间"
    )

    __table_args__ = (
        UniqueConstraint("user_id", "threshold_index", name="uk_conversation_stone_threshold"),
        Index("idx_conversation_stone_user_threshold", "user_id", "threshold_index"),
    )


class UserUIState(Base):
    """用户前端可视化状态表（页面与提示状态）"""

    __tablename__ = "user_ui_states"

    id = Column(PKBigInt, primary_key=True, autoincrement=True)
    user_id = Column(String(64), unique=True, nullable=False, index=True, comment="用户ID(关联 users.user_id)")

    # 页面与路由状态
    current_page = Column(String(64), nullable=True, comment="当前业务流程页面标识")
    visited_routes = Column(JSON, nullable=True, comment="已访问路由列表")

    # 新手引导与提示状态
    has_shown_tap_to_chat = Column(Boolean, default=False, nullable=False, comment="是否已展示过 TapToChat 提示")
    has_shown_cancel_tooltip = Column(Boolean, default=False, nullable=False, comment="是否已展示过取消按钮提示")
    has_shown_send_tooltip = Column(Boolean, default=False, nullable=False, comment="是否已展示过发送按钮提示")
    has_shown_progress_bar_tooltip = Column(Boolean, default=False, nullable=False, comment="是否已展示过进度条提示")
    has_shown_mission_stone_modal = Column(Boolean, default=False, nullable=False, comment="是否已展示过任务能量石弹窗")
    has_shown_conversation_stone_modal = Column(Boolean, default=False, nullable=False, comment="是否已展示过对话能量石弹窗")
    has_clicked_mission_stone = Column(Boolean, default=False, nullable=False, comment="是否已点击过任务能量石（控制光晕显示）")
    has_clicked_conversation_stone = Column(Boolean, default=False, nullable=False, comment="是否已点击过对话能量石（控制光晕显示）")

    # Avatar-C 解锁状态
    avatar_c_unlocked = Column(Boolean, default=False, nullable=False, comment="是否已解锁 Avatar-C")
    has_shared_avatar = Column(Boolean, default=False, nullable=False, comment="是否已分享头像奖励")
    # 当前选中的形象
    current_avatar_id = Column(String(64), nullable=True, comment="当前使用的形象ID(关联 avatars.avatar_id)")

    created_at = Column(TIMESTAMP, server_default=func.current_timestamp(), comment="创建时间")
    updated_at = Column(
        TIMESTAMP,
        server_default=func.current_timestamp(),
        onupdate=func.current_timestamp(),
        comment="更新时间",
    )

    def to_dict(self) -> dict:
        return {
            "user_id": self.user_id,
            "current_page": self.current_page,
            "visited_routes": self.visited_routes or [],
            "has_shown_tap_to_chat": self.has_shown_tap_to_chat,
            "has_shown_cancel_tooltip": self.has_shown_cancel_tooltip,
            "has_shown_send_tooltip": self.has_shown_send_tooltip,
            "has_shown_progress_bar_tooltip": self.has_shown_progress_bar_tooltip,
            "has_shown_mission_stone_modal": self.has_shown_mission_stone_modal,
            "has_shown_conversation_stone_modal": self.has_shown_conversation_stone_modal,
            "has_clicked_mission_stone": self.has_clicked_mission_stone,
            "has_clicked_conversation_stone": self.has_clicked_conversation_stone,
            "avatar_c_unlocked": self.avatar_c_unlocked,
            "has_shared_avatar": self.has_shared_avatar,
            "current_avatar_id": self.current_avatar_id,
            "created_at": self.created_at.isoformat() if self.created_at else None,
            "updated_at": self.updated_at.isoformat() if self.updated_at else None,
        }


class Avatar(Base):
    """形象配置表"""
    __tablename__ = "avatars"

    id = Column(PKBigInt, primary_key=True, autoincrement=True)
    avatar_id = Column(String(64), unique=True, nullable=False, index=True, comment="形象唯一标识")

    # 基本信息
    avatar_name = Column(String(100), nullable=False, comment="形象名称")
    description = Column(String(255), nullable=True, comment="形象描述")
    is_active = Column(Boolean, default=True, nullable=False, comment="是否启用")

    # 扩展信息
    metadata_json = Column("metadata", JSON, nullable=True, comment="扩展配置")

    created_at = Column(TIMESTAMP, server_default=func.current_timestamp(), comment="创建时间")
    updated_at = Column(
        TIMESTAMP,
        server_default=func.current_timestamp(),
        onupdate=func.current_timestamp(),
        comment="更新时间",
    )

    __table_args__ = (
        Index("idx_avatar_is_active", "is_active"),
    )

    def to_dict(self) -> dict:
        return {
            "avatar_id": self.avatar_id,
            "avatar_name": self.avatar_name,
            "description": self.description,
            "is_active": self.is_active,
            "metadata": self.metadata_json,
            "created_at": self.created_at.isoformat() if self.created_at else None,
            "updated_at": self.updated_at.isoformat() if self.updated_at else None,
        }


class AvatarAsset(Base):
    """形象动作/情绪资源表"""
    __tablename__ = "avatar_assets"

    id = Column(PKBigInt, primary_key=True, autoincrement=True)
    avatar_id = Column(String(64), nullable=False, index=True, comment="形象ID")
    state = Column(
        Enum(
            "default",
            "positive",
            "negative",
            "talking",
            "idle",
            "sleep",
            name="avatar_state",
        ),
        nullable=False,
        default="default",
        comment="动作/情绪状态",
    )
    image_url = Column(String(500), nullable=False, comment="GIF 资源 URL")
    thumbnail_url = Column(String(500), nullable=True, comment="缩略图URL")

    metadata_json = Column("metadata", JSON, nullable=True, comment="扩展配置")

    created_at = Column(TIMESTAMP, server_default=func.current_timestamp(), comment="创建时间")
    updated_at = Column(
        TIMESTAMP,
        server_default=func.current_timestamp(),
        onupdate=func.current_timestamp(),
        comment="更新时间",
    )

    __table_args__ = (
        UniqueConstraint("avatar_id", "state", name="uk_avatar_asset_state"),
        Index("idx_avatar_asset_avatar_id", "avatar_id"),
    )

    def to_dict(self) -> dict:
        return {
            "id": self.id,
            "avatar_id": self.avatar_id,
            "state": self.state,
            "image_url": self.image_url,
            "thumbnail_url": self.thumbnail_url,
            "metadata": self.metadata_json,
            "created_at": self.created_at.isoformat() if self.created_at else None,
            "updated_at": self.updated_at.isoformat() if self.updated_at else None,
        }


class UserAvatar(Base):
    """用户已拥有的形象"""
    __tablename__ = "user_avatars"

    id = Column(PKBigInt, primary_key=True, autoincrement=True)
    user_id = Column(String(64), nullable=False, index=True, comment="用户ID")
    avatar_id = Column(String(64), nullable=False, index=True, comment="形象ID")

    obtained_at = Column(TIMESTAMP, server_default=func.current_timestamp(), comment="获得时间")
    obtained_type = Column(
        Enum("default", "register", "level_up", "mission", "purchase", "gift", "event", name="avatar_obtained_type"),
        default="default",
        comment="获得方式",
    )
    obtained_source = Column(String(64), nullable=True, comment="获得来源（等级/任务/订单等）")

    metadata_json = Column("metadata", JSON, nullable=True, comment="扩展信息")

    created_at = Column(TIMESTAMP, server_default=func.current_timestamp(), comment="创建时间")

    __table_args__ = (
        UniqueConstraint("user_id", "avatar_id", name="uk_user_avatar"),
        Index("idx_user_avatar_user_obtained", "user_id", "obtained_at"),
    )

    def to_dict(self) -> dict:
        return {
            "id": self.id,
            "user_id": self.user_id,
            "avatar_id": self.avatar_id,
            "obtained_at": self.obtained_at.isoformat() if self.obtained_at else None,
            "obtained_type": self.obtained_type,
            "obtained_source": self.obtained_source,
            "metadata": self.metadata_json,
            "created_at": self.created_at.isoformat() if self.created_at else None,
        }


class Feedback(Base):
    """用户反馈表模型"""
    __tablename__ = "feedbacks"
    
    id = Column(PKBigInt, primary_key=True, autoincrement=True)
    feedback_id = Column(String(64), unique=True, nullable=False, index=True, comment="反馈ID")
    user_id = Column(String(64), nullable=False, index=True, comment="用户ID")
    
    # 反馈内容
    content = Column(Text, nullable=False, comment="反馈内容")
    
    # 状态信息
    status = Column(
        Enum("pending", "reviewed", "resolved", "closed", name="feedback_status"),
        default="pending",
        nullable=False,
        comment="反馈状态"
    )
    
    # 扩展信息
    metadata_json = Column("metadata", JSON, nullable=True, comment="扩展信息")
    
    created_at = Column(TIMESTAMP, server_default=func.current_timestamp(), index=True, comment="创建时间")
    updated_at = Column(
        TIMESTAMP,
        server_default=func.current_timestamp(),
        onupdate=func.current_timestamp(),
        comment="更新时间",
    )
    
    __table_args__ = (
        Index("idx_feedback_user_created", "user_id", "created_at"),
    )
    
    def to_dict(self) -> dict:
        """转换为字典"""
        return {
            "feedback_id": self.feedback_id,
            "user_id": self.user_id,
            "content": self.content,
            "status": self.status,
            "metadata": self.metadata_json,
            "created_at": self.created_at.isoformat() if self.created_at else None,
            "updated_at": self.updated_at.isoformat() if self.updated_at else None,
        }


def _attach_metadata_alias(model_cls):
    """Allow using `metadata` keyword/attribute while storing in metadata_json."""
    original_init = model_cls.__init__

    def __init__(self, *args, **kwargs):
        metadata_value = kwargs.pop("metadata", None)
        original_init(self, *args, **kwargs)
        if metadata_value is not None:
            setattr(self, "metadata_json", metadata_value)

    model_cls.__init__ = __init__

    def getter(self):
        return getattr(self, "metadata_json")

    def setter(self, value):
        setattr(self, "metadata_json", value)

    model_cls.metadata = property(getter, setter)


class BackgroundImage(Base):
    """背景图片配置表"""
    __tablename__ = "background_images"
    
    id = Column(PKBigInt, primary_key=True, autoincrement=True)
    image_id = Column(String(64), unique=True, nullable=False, index=True, comment="背景图片唯一标识")
    
    # 基本信息
    image_name = Column(String(100), nullable=False, comment="背景图片名称")
    image_url = Column(String(500), nullable=False, comment="背景图片URL")
    thumbnail_url = Column(String(500), nullable=True, comment="缩略图URL")
    
    # 解锁条件
    unlock_type = Column(Enum("default", "level", "purchase", "mission", name="unlock_type"), 
                        default="default", comment="解锁类型")
    unlock_level = Column(Integer, nullable=True, comment="解锁所需等级（unlock_type=level时）")
    unlock_mission_type = Column(String(64), nullable=True, comment="解锁所需任务类型（unlock_type=mission时）")
    
    # 显示信息
    display_order = Column(Integer, default=0, comment="显示顺序")
    is_active = Column(Boolean, default=True, comment="是否启用")
    description = Column(String(255), nullable=True, comment="背景图片描述")
    
    # 扩展信息
    metadata_json = Column("metadata", JSON, nullable=True, comment="扩展配置")
    
    created_at = Column(TIMESTAMP, server_default=func.current_timestamp(), comment="创建时间")
    updated_at = Column(TIMESTAMP, server_default=func.current_timestamp(), 
                       onupdate=func.current_timestamp(), comment="更新时间")
    
    __table_args__ = (
        Index("idx_unlock_type", "unlock_type"),
        Index("idx_display_order", "display_order"),
    )
    
    def to_dict(self) -> dict:
        """转换为字典"""
        return {
            "image_id": self.image_id,
            "image_name": self.image_name,
            "image_url": self.image_url,
            "thumbnail_url": self.thumbnail_url,
            "unlock_type": self.unlock_type,
            "unlock_level": self.unlock_level,
            "unlock_mission_type": self.unlock_mission_type,
            "display_order": self.display_order,
            "is_active": self.is_active,
            "description": self.description,
            "metadata": self.metadata_json,
            "created_at": self.created_at.isoformat() if self.created_at else None,
            "updated_at": self.updated_at.isoformat() if self.updated_at else None,
        }


class UserBackgroundImage(Base):
    """用户背景图片关联表（记录用户已获得的背景图片）"""
    __tablename__ = "user_background_images"
    
    id = Column(PKBigInt, primary_key=True, autoincrement=True)
    user_id = Column(String(64), nullable=False, index=True, comment="用户ID")
    image_id = Column(String(64), nullable=False, index=True, comment="背景图片ID")
    
    # 获得信息
    obtained_at = Column(TIMESTAMP, server_default=func.current_timestamp(), comment="获得时间")
    obtained_type = Column(Enum("default", "level_up", "purchase", "mission", "gift", name="obtained_type"), 
                          default="default", comment="获得方式")
    obtained_source = Column(String(64), nullable=True, comment="获得来源（任务类型、等级等）")
    
    # 扩展信息
    metadata_json = Column("metadata", JSON, nullable=True, comment="扩展信息")
    
    created_at = Column(TIMESTAMP, server_default=func.current_timestamp(), comment="创建时间")
    
    __table_args__ = (
        UniqueConstraint("user_id", "image_id", name="uk_user_background_image"),
        Index("idx_user_obtained", "user_id", "obtained_at"),
    )
    
    def to_dict(self) -> dict:
        """转换为字典"""
        return {
            "id": self.id,
            "user_id": self.user_id,
            "image_id": self.image_id,
            "obtained_at": self.obtained_at.isoformat() if self.obtained_at else None,
            "obtained_type": self.obtained_type,
            "obtained_source": self.obtained_source,
            "metadata": self.metadata_json,
            "created_at": self.created_at.isoformat() if self.created_at else None,
        }


class VerificationCode(Base):
    """验证码表模型"""
    __tablename__ = "verification_codes"
    
    id = Column(PKBigInt, primary_key=True, autoincrement=True)
    code_id = Column(String(64), unique=True, nullable=False, index=True, comment="验证码ID")
    
    # 接收方信息（手机号或邮箱，根据发送类型二选一）
    area_code = Column(String(10), nullable=True, comment="手机区号（如 +86）")
    phone = Column(String(20), nullable=True, index=True, comment="手机号")
    email = Column(String(200), nullable=True, index=True, comment="邮箱")
    
    # 验证码信息
    code = Column(String(10), nullable=False, comment="验证码")
    send_type = Column(
        Enum("domestic_sms", "international_sms", "email", name="send_type"),
        nullable=False,
        comment="发送类型：国内短信、国外短信、邮箱"
    )
    
    # 状态信息
    is_sent = Column(Boolean, default=False, nullable=False, comment="是否已发送")
    is_valid = Column(Boolean, default=True, nullable=False, comment="是否有效")
    
    # 时间信息
    created_at = Column(TIMESTAMP, server_default=func.current_timestamp(), index=True, comment="创建时间")
    sent_at = Column(TIMESTAMP, nullable=True, comment="发送时间")
    
    # 扩展信息
    metadata_json = Column("metadata", JSON, nullable=True, comment="扩展信息（发送结果、错误信息等）")
    
    __table_args__ = (
        Index("idx_verification_code_phone", "phone", "is_valid", "created_at"),
        Index("idx_verification_code_email", "email", "is_valid", "created_at"),
        Index("idx_verification_code_send_type", "send_type", "is_sent", "is_valid"),
    )
    
    def to_dict(self) -> dict:
        """转换为字典"""
        return {
            "code_id": self.code_id,
            "area_code": self.area_code,
            "phone": self.phone,
            "email": self.email,
            "code": self.code,
            "send_type": self.send_type,
            "is_sent": self.is_sent,
            "is_valid": self.is_valid,
            "created_at": self.created_at.isoformat() if self.created_at else None,
            "sent_at": self.sent_at.isoformat() if self.sent_at else None,
            "metadata": self.metadata_json,
        }


for _model in (
    MissionType,
    MissionEnergyStone,
    ConversationEnergyStone,
    BackgroundImage,
    UserBackgroundImage,
    Avatar,
    AvatarAsset,
    UserAvatar,
):
    _attach_metadata_alias(_model)