"""
创建 data_products 数据表脚本
用于在生产环境 PostgreSQL 数据库中创建数据产品注册表
"""

import os
import sys
from pathlib import Path

# 设置控制台编码为 UTF-8（Windows）
if sys.platform == "win32":
    import io

    sys.stdout = io.TextIOWrapper(sys.stdout.buffer, encoding="utf-8")
    sys.stderr = io.TextIOWrapper(sys.stderr.buffer, encoding="utf-8")

# 添加项目根目录到路径
project_root = Path(__file__).parent.parent
sys.path.insert(0, str(project_root))

import psycopg2

from app.config.config import ProductionConfig


def create_data_products_table():
    """在生产环境数据库中创建 data_products 表"""
    try:
        # 从生产环境配置获取数据库连接信息
        config = ProductionConfig()
        db_uri = config.SQLALCHEMY_DATABASE_URI

        # 解析数据库连接URI
        # 格式: postgresql://user:password@host:port/database
        uri_parts = db_uri.replace("postgresql://", "").split("@")
        if len(uri_parts) != 2:
            raise ValueError(f"无效的数据库URI格式: {db_uri}")

        user_pass = uri_parts[0].split(":")
        username = user_pass[0]
        password = user_pass[1] if len(user_pass) > 1 else ""

        host_db = uri_parts[1].split("/")
        if len(host_db) != 2:
            raise ValueError(f"无效的数据库URI格式: {db_uri}")

        host_port = host_db[0].split(":")
        hostname = host_port[0]
        port = int(host_port[1]) if len(host_port) > 1 else 5432
        database = host_db[1]

        print("正在连接数据库...")
        print(f"  主机: {hostname}")
        print(f"  端口: {port}")
        print(f"  数据库: {database}")
        print(f"  用户: {username}")

        # 连接数据库
        conn = psycopg2.connect(
            host=hostname,
            port=port,
            database=database,
            user=username,
            password=password,
        )

        # 设置自动提交
        conn.autocommit = True
        cursor = conn.cursor()

        print("\n连接成功！")

        # 读取SQL脚本
        sql_file = project_root / "database" / "create_data_products_table.sql"
        if not sql_file.exists():
            raise FileNotFoundError(f"SQL文件不存在: {sql_file}")

        print(f"\n读取SQL脚本: {sql_file}")

        with open(sql_file, encoding="utf-8") as f:
            sql_content = f.read()

        # 执行SQL脚本
        print("\n开始执行SQL脚本...")
        cursor.execute(sql_content)

        print("\n[成功] SQL脚本执行成功！")

        # 验证表是否创建成功
        print("\n验证表是否创建成功...")
        cursor.execute(
            """
            SELECT EXISTS (
                SELECT FROM information_schema.tables 
                WHERE table_schema = 'public' 
                AND table_name = 'data_products'
            )
            """
        )
        table_exists = cursor.fetchone()[0]

        if table_exists:
            print("[成功] 表 data_products 已成功创建！")

            # 查询表结构
            cursor.execute(
                """
                SELECT column_name, data_type, is_nullable
                FROM information_schema.columns
                WHERE table_schema = 'public' 
                AND table_name = 'data_products'
                ORDER BY ordinal_position
                """
            )
            columns = cursor.fetchall()

            print(f"\n表结构 ({len(columns)} 列):")
            for col in columns:
                nullable = "NULL" if col[2] == "YES" else "NOT NULL"
                print(f"  - {col[0]:30} {col[1]:20} {nullable}")

            # 查询索引
            cursor.execute(
                """
                SELECT indexname, indexdef
                FROM pg_indexes
                WHERE schemaname = 'public' 
                AND tablename = 'data_products'
                """
            )
            indexes = cursor.fetchall()

            if indexes:
                print(f"\n索引 ({len(indexes)} 个):")
                for idx in indexes:
                    print(f"  - {idx[0]}")

        else:
            print("[警告] 表 data_products 未找到！")

        cursor.close()
        conn.close()

        print("\n[完成] 操作成功完成！")

    except psycopg2.Error as e:
        print(f"\n[错误] 数据库错误: {e}")
        sys.exit(1)
    except Exception as e:
        print(f"\n[错误] 错误: {e}")
        import traceback

        traceback.print_exc()
        sys.exit(1)


if __name__ == "__main__":
    print("=" * 60)
    print("创建 data_products 数据表")
    print("=" * 60)
    print()

    # 确认环境
    env = os.environ.get("FLASK_ENV", "production")
    print(f"当前环境: {env}")

    if env != "production":
        response = input("\n警告: 当前不是生产环境，是否继续？(yes/no): ")
        if response.lower() != "yes":
            print("已取消操作")
            sys.exit(0)

    create_data_products_table()
