Tolan/backend/migrations/migrate_add_user_ui_state_table.py

"""
数据库迁移脚本：创建 user_ui_states 表
运行: python migrate_add_user_ui_state_table.py
"""
import asyncio
import sys
from typing import Any

import aiomysql

from config import settings


async def migrate_create_user_ui_states_table() -> bool:
    """创建 user_ui_states 表（如果不存在）"""
    try:
        conn: aiomysql.Connection = await aiomysql.connect(
            host=settings.MYSQL_HOST,
            port=settings.MYSQL_PORT,
            user=settings.MYSQL_USER,
            password=settings.MYSQL_PASSWORD,
            db=settings.MYSQL_DATABASE,
            charset="utf8mb4",
        )

        async with conn.cursor() as cursor:
            # 检查表是否已存在
            await cursor.execute(
                """
                SELECT COUNT(*) FROM information_schema.tables
                WHERE table_schema = %s AND table_name = 'user_ui_states'
                """,
                (settings.MYSQL_DATABASE,),
            )
            exists_row: Any = await cursor.fetchone()
            table_exists = bool(exists_row and exists_row[0] > 0)

            if table_exists:
                print("✅ user_ui_states 表已存在，无需迁移")
                await conn.ensure_closed()
                return True

            print("🔄 创建 user_ui_states 表...")
            await cursor.execute(
                """
                CREATE TABLE user_ui_states (
                    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '主键',
                    user_id VARCHAR(64) NOT NULL UNIQUE COMMENT '用户ID(关联 users.user_id)',
                    current_page VARCHAR(64) NULL COMMENT '当前业务流程页面标识',
                    visited_routes JSON NULL COMMENT '已访问路由列表',
                    has_shown_tap_to_chat TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否已展示 TapToChat 提示',
                    has_shown_cancel_tooltip TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否已展示取消按钮提示',
                    has_shown_send_tooltip TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否已展示发送按钮提示',
                    has_shown_progress_bar_tooltip TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否已展示进度条提示',
                    avatar_c_unlocked TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否已解锁 Avatar-C',
                    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
                    INDEX idx_user_ui_state_user_id (user_id)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户前端可视化状态表';
                """
            )

            await conn.commit()
            print("✅ user_ui_states 表创建完成")

        await conn.ensure_closed()
        return True
    except Exception as exc:  # noqa: BLE001
        import traceback

        print(f"❌ 迁移失败: {exc}")
        traceback.print_exc()
        return False


if __name__ == "__main__":
    if len(sys.argv) > 1 and sys.argv[1] == "--force":
        confirm = "y"
    else:
        confirm = input("\n是否继续创建 user_ui_states 表? (y/n): ")

    if confirm.lower() != "y":
        sys.exit(0)

    ok = asyncio.run(migrate_create_user_ui_states_table())
    if not ok:
        sys.exit(1)