Tolan/backend/migrations/migrate_add_v2_community_tables.py

"""
V2.0 迁移：创建 user_community_choice、user_early_community 表
运行: python migrations/migrate_add_v2_community_tables.py
"""
import asyncio
import sys
from typing import Any

import aiomysql

from config import settings


async def migrate() -> bool:
    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:
            # user_community_choice：退出早期共创社区选项
            await cursor.execute(
                """
                SELECT COUNT(*) FROM information_schema.tables
                WHERE table_schema = %s AND table_name = 'user_community_choice'
                """,
                (settings.MYSQL_DATABASE,),
            )
            exists = bool((await cursor.fetchone() or [0])[0])
            if not exists:
                print("🔄 创建 user_community_choice 表...")
                await cursor.execute(
                    """
                    CREATE TABLE user_community_choice (
                        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                        user_id VARCHAR(64) NOT NULL UNIQUE COMMENT '用户ID',
                        opted_out BOOLEAN NOT NULL DEFAULT FALSE COMMENT '是否选择退出早期共创社区',
                        updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                        INDEX idx_user_id (user_id)
                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
                    COMMENT='用户是否退出早期共创社区'
                    """
                )
                await conn.commit()
                print("✅ user_community_choice 表创建完成")
            else:
                print("✅ user_community_choice 表已存在")

            # user_early_community：早期共创邮箱与弹窗状态
            await cursor.execute(
                """
                SELECT COUNT(*) FROM information_schema.tables
                WHERE table_schema = %s AND table_name = 'user_early_community'
                """,
                (settings.MYSQL_DATABASE,),
            )
            exists2 = bool((await cursor.fetchone() or [0])[0])
            if not exists2:
                print("🔄 创建 user_early_community 表...")
                await cursor.execute(
                    """
                    CREATE TABLE user_early_community (
                        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                        user_id VARCHAR(64) NOT NULL UNIQUE COMMENT '用户ID',
                        email VARCHAR(200) NULL COMMENT '加入早期共创时留的邮箱',
                        popup_shown BOOLEAN NOT NULL DEFAULT FALSE COMMENT '是否已展示过弹窗',
                        joined_at TIMESTAMP NULL COMMENT '加入时间（留邮箱时）',
                        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                        updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                        INDEX idx_user_id (user_id)
                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
                    COMMENT='用户早期共创社区（手机号新用户留邮箱）'
                    """
                )
                await conn.commit()
                print("✅ user_early_community 表创建完成")
            else:
                print("✅ user_early_community 表已存在")

        conn.close()
        return True
    except Exception:
        import traceback
        traceback.print_exc()
        return False


if __name__ == "__main__":
    confirm = input("\n是否继续执行迁移? (y/n): ") if len(sys.argv) <= 1 or sys.argv[1] != "--force" else "y"
    if confirm.lower() != "y":
        sys.exit(0)
    ok = asyncio.run(migrate())
    sys.exit(0 if ok else 1)