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="用户类型")
    user_segment = Column(String(32), nullable=False, default="individual", comment="用户/场景分群：individual/school/internal等")
    org_id = Column(String(64), nullable=True, comment="组织ID(关联 organizations.org_id)")
    is_test_user = Column(Boolean, nullable=False, default=False, comment="是否测试账号")
    region = Column(String(32), nullable=True, comment="国家/地区（如 CN、US）")
    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)")
    conversation_energy_stone_total = Column(
        Integer,
        default=0,
        nullable=False,
        comment="对话能量石余额(对话获得+赠送-购物消耗，展示与扣减以此为准)",
    )
    crystal_stone_total = Column(
        Integer,
        default=0,
        nullable=False,
        comment="水晶石余额(任务等获得-购物消耗，展示与扣减以此为准)",
    )
    preferred_language = Column(String(8), nullable=True, comment="首选语言（zh/en）")
    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,
            "user_segment": self.user_segment,
            "org_id": self.org_id,
            "is_test_user": self.is_test_user,
            "region": self.region,
            "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,
            "conversation_energy_stone_total": self.conversation_energy_stone_total or 0,
            "crystal_stone_total": self.crystal_stone_total or 0,
            "preferred_language": self.preferred_language,
            "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 Organization(Base):
    """组织表模型"""

    __tablename__ = "organizations"

    id = Column(PKBigInt, primary_key=True, autoincrement=True, comment="主键")
    org_id = Column(String(64), nullable=False, unique=True, index=True, comment="组织唯一标识")
    org_name = Column(String(200), nullable=False, comment="组织名称")
    org_type = Column(String(32), nullable=True, index=True, comment="组织类型：school/institution/internal等")
    region = Column(String(32), nullable=True, index=True, comment="组织所在地区")
    metadata_json = Column(JSON, nullable=True, comment="扩展信息")
    created_at = Column(TIMESTAMP, server_default=func.current_timestamp(), nullable=False, comment="创建时间")
    updated_at = Column(
        TIMESTAMP,
        server_default=func.current_timestamp(),
        onupdate=func.current_timestamp(),
        nullable=False,
        comment="更新时间",
    )

    def to_dict(self) -> dict:
        return {
            "org_id": self.org_id,
            "org_name": self.org_name,
            "org_type": self.org_type,
            "region": self.region,
            "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 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 UserBonusEnergyStone(Base):
    """用户赠送的对话能量石数量（活动/回馈等，与 conversation_energy_stones 分开统计，展示时相加）"""
    __tablename__ = "user_bonus_energy_stones"

    user_id = Column(String(64), primary_key=True, nullable=False, comment="用户ID(关联 users.user_id)")
    bonus_conversation_count = Column(Integer, default=0, nullable=False, comment="赠送的对话能量石个数")
    updated_at = Column(
        TIMESTAMP,
        server_default=func.current_timestamp(),
        onupdate=func.current_timestamp(),
        comment="更新时间",
    )


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="是否已展示过进度条提示")
    # 字幕显示偏好
    subtitle_on = Column(Boolean, default=False, nullable=False, comment="是否开启字幕显示")
    # 音频偏好（短音效 / 全局背景音乐）
    sfx_enabled = Column(Boolean, default=True, nullable=False, comment="是否开启界面短音效")
    bgm_enabled = Column(Boolean, default=True, 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="是否已点击过对话能量石（控制光晕显示）")

    # 首次商店引导状态
    has_shown_first_shop_tooltip = Column(Boolean, default=False, nullable=False, comment="是否已展示过首次商店引导 tips")
    has_clicked_shop_in_settings = 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,
            "has_shown_first_shop_tooltip": self.has_shown_first_shop_tooltip,
            "has_clicked_shop_in_settings": self.has_clicked_shop_in_settings,
            "subtitle_on": self.subtitle_on,
            "sfx_enabled": self.sfx_enabled,
            "bgm_enabled": self.bgm_enabled,
            "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 PageVisitLog(Base):
    """页面访问日志表（每次打开任意界面记录一条）"""

    __tablename__ = "page_visit_logs"

    id = Column(PKBigInt, primary_key=True, autoincrement=True)
    user_id = Column(String(64), nullable=True, index=True, comment="用户ID(未登录则为空)")
    path = Column(String(512), nullable=False, index=True, comment="访问路径(如 /chatbot)")
    visited_at = Column(TIMESTAMP, server_default=func.current_timestamp(), nullable=False, index=True, comment="访问时间")

    __table_args__ = (Index("idx_page_visit_visited_at", "visited_at"),)

    def to_dict(self) -> dict:
        return {
            "id": self.id,
            "user_id": self.user_id,
            "path": self.path,
            "visited_at": self.visited_at.isoformat() if self.visited_at else None,
        }


class UserEvent(Base):
    """用户事件表（前端埋点：页面/按钮/操作等）"""

    __tablename__ = "user_events"

    id = Column(PKBigInt, primary_key=True, autoincrement=True, comment="主键")
    event_id = Column(String(64), nullable=False, unique=True, index=True, comment="事件ID（客户端或服务端生成，唯一）")
    user_id = Column(String(64), nullable=False, index=True, comment="用户ID(关联 users.user_id)")
    event_name = Column(String(64), nullable=False, index=True, comment="事件代码，如 page.chatbot__enter（遵循埋点命名规范）")
    event_label = Column(String(128), nullable=True, comment="事件中文名称/描述（如“进入聊天页”、“欢迎页开始按钮点击”）")
    event_time = Column(TIMESTAMP, nullable=False, comment="事件发生时间（UTC）")
    session_id = Column(String(64), nullable=True, index=True, comment="会话ID，同一次前台停留共享")
    platform = Column(String(16), nullable=False, comment="平台：ios/android/web")
    app_version = Column(String(32), nullable=True, comment="应用版本号，如 v0.1.x")
    timezone_offset_minutes = Column(Integer, nullable=True, comment="用户本地相对UTC的分钟偏移量（分钟）")
    properties = Column(JSON, nullable=True, comment="扩展属性（page、entry、button_id、duration_seconds 等）")
    created_at = Column(TIMESTAMP, server_default=func.current_timestamp(), nullable=False, comment="记录创建时间")

    __table_args__ = (
        Index("idx_user_event_time", "user_id", "event_time"),
        Index("idx_event_time_name", "event_time", "event_name"),
    )

    def to_dict(self) -> dict:
        return {
            "event_id": self.event_id,
            "user_id": self.user_id,
            "event_name": self.event_name,
            "event_label": self.event_label,
            "event_time": self.event_time.isoformat() if self.event_time else None,
            "session_id": self.session_id,
            "platform": self.platform,
            "app_version": self.app_version,
            "timezone_offset_minutes": self.timezone_offset_minutes,
            "properties": self.properties,
            "created_at": self.created_at.isoformat() if self.created_at else None,
        }


class UserSpecialScenarioTopicLog(Base):
    """用户情景话题对话记录（情景对话模式：用于概率排除与不再触发）"""

    __tablename__ = "user_special_scenario_topic_logs"

    id = Column(PKBigInt, primary_key=True, autoincrement=True, comment="主键")
    user_id = Column(String(64), nullable=False, index=True, comment="用户ID(关联 users.user_id)")
    scenario_key = Column(String(32), nullable=False, index=True, comment="场景: window|reading|eating")
    topic_key = Column(String(32), nullable=False, index=True, comment="话题键")
    conversed_at = Column(TIMESTAMP, nullable=False, server_default=func.current_timestamp(), comment="该场景该话题下首次对话时间")
    created_at = Column(TIMESTAMP, server_default=func.current_timestamp(), comment="记录创建时间")

    __table_args__ = (
        UniqueConstraint("user_id", "scenario_key", "topic_key", name="uk_user_scenario_topic"),
        Index("idx_user_scenario", "user_id", "scenario_key"),
    )

    def to_dict(self) -> dict:
        return {
            "id": self.id,
            "user_id": self.user_id,
            "scenario_key": self.scenario_key,
            "topic_key": self.topic_key,
            "conversed_at": self.conversed_at.isoformat() if self.conversed_at else None,
            "created_at": self.created_at.isoformat() if self.created_at else None,
        }


class UserIntimacy(Base):
    """用户亲密度表（三颗心，0-3）"""

    __tablename__ = "user_intimacy"

    id = Column(PKBigInt, primary_key=True, autoincrement=True)
    user_id = Column(String(64), unique=True, nullable=False, index=True, comment="用户ID(关联 users.user_id)")
    heart_count = Column(Integer, default=3, nullable=False, comment="当前亲密度心数 0-3")
    last_visit_date_at_deduction = Column(String(10), nullable=True, comment="上次扣心时所依据的最后访问日期 YYYY-MM-DD，避免重复扣")
    last_light_up_date = Column(String(10), nullable=True, comment="上次手动点亮日期 YYYY-MM-DD，控制每天仅点亮一次")
    updated_at = Column(TIMESTAMP, server_default=func.current_timestamp(), onupdate=func.current_timestamp(), nullable=False, comment="更新时间")

    def to_dict(self) -> dict:
        return {
            "user_id": self.user_id,
            "heart_count": self.heart_count,
            "last_visit_date_at_deduction": self.last_visit_date_at_deduction,
            "last_light_up_date": self.last_light_up_date,
            "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,
        }


class UserCommunityChoice(Base):
    """用户是否退出早期共创社区（V2.0）"""
    __tablename__ = "user_community_choice"

    id = Column(PKBigInt, primary_key=True, autoincrement=True)
    user_id = Column(String(64), unique=True, nullable=False, index=True, comment="用户ID")
    opted_out = Column(Boolean, default=False, nullable=False, comment="是否选择退出早期共创社区")
    updated_at = Column(TIMESTAMP, server_default=func.current_timestamp(), onupdate=func.current_timestamp())

    def to_dict(self) -> dict:
        return {
            "user_id": self.user_id,
            "opted_out": self.opted_out,
            "updated_at": self.updated_at.isoformat() if self.updated_at else None,
        }


class UserEarlyCommunity(Base):
    """用户早期共创社区（手机号新用户留邮箱，V2.0）"""
    __tablename__ = "user_early_community"

    id = Column(PKBigInt, primary_key=True, autoincrement=True)
    user_id = Column(String(64), unique=True, nullable=False, index=True, comment="用户ID")
    email = Column(String(200), nullable=True, comment="加入早期共创时留的邮箱")
    popup_shown = Column(Boolean, default=False, nullable=False, comment="是否已展示过弹窗")
    joined_at = Column(TIMESTAMP, nullable=True, comment="加入时间（留邮箱时）")
    created_at = Column(TIMESTAMP, server_default=func.current_timestamp())
    updated_at = Column(TIMESTAMP, server_default=func.current_timestamp(), onupdate=func.current_timestamp())

    def to_dict(self) -> dict:
        return {
            "user_id": self.user_id,
            "email": self.email,
            "popup_shown": self.popup_shown,
            "joined_at": self.joined_at.isoformat() if self.joined_at else None,
            "created_at": self.created_at.isoformat() if self.created_at else None,
        }


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