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

Base = declarative_base()


class User(Base):
    """用户表模型"""
    __tablename__ = "users"
    
    id = Column(BigInteger, 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="手机号")
    wechat_openid = Column(String(100), unique=True, nullable=True, index=True, comment="微信OpenID")
    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="累积说话时长（毫秒）")
    last_active_at = Column(TIMESTAMP, 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="更新时间")
    
    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,
            "wechat_openid": self.wechat_openid,
            "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,
            "last_active_at": self.last_active_at.isoformat() if self.last_active_at else None,
            "created_at": self.created_at.isoformat() if self.created_at else None,
        }


class Message(Base):
    """消息表模型"""
    __tablename__ = "messages"
    
    id = Column(BigInteger, 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(BigInteger, 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,
        }