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="累积说话时长（毫秒）")
    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,
            "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,
            "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(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"),
    )


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)


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