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,
)
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="用户给机器人取的名字")
    
    # 注册信息（可选）
    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,
            "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 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="是否已展示过对话能量石弹窗")

    # Avatar-C 解锁状态
    avatar_c_unlocked = Column(Boolean, default=False, nullable=False, comment="是否已解锁 Avatar-C")
    has_shared_avatar = Column(Boolean, default=False, nullable=False, 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="更新时间",
    )

    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,
            "avatar_c_unlocked": self.avatar_c_unlocked,
            "has_shared_avatar": self.has_shared_avatar,
            "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,
        }


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