#!/usr/bin/env python3
"""修复 Phase1 迁移 - 添加缺失的列"""

import asyncio
from sqlalchemy.ext.asyncio import create_async_engine
from sqlalchemy import text

DB_CONFIG = {
    'host': '123.57.81.127',
    'port': 3306,
    'user': 'cpy_admin',
    'password': 'Tgzz2025+',
    'database': 'ruoyi-fastapi'
}

DATABASE_URL = (
    f"mysql+asyncmy://{DB_CONFIG['user']}:{DB_CONFIG['password']}@"
    f"{DB_CONFIG['host']}:{DB_CONFIG['port']}/{DB_CONFIG['database']}"
)

ALTER_STATEMENTS = [
    # test_category 扩展
    ("test_category", "ALTER TABLE test_category ADD COLUMN parent_id INT NULL COMMENT '父级分类ID'"),
    ("test_category", "ALTER TABLE test_category ADD COLUMN level INT NULL DEFAULT 2 COMMENT '层级: 1=大类, 2=测试项目'"),
    ("test_category", "ALTER TABLE test_category ADD COLUMN category_code VARCHAR(50) NULL COMMENT '类别编码'"),
    ("test_category", "ALTER TABLE test_category ADD COLUMN sort_order INT DEFAULT 0 COMMENT '排序'"),
    ("test_category", "ALTER TABLE test_category ADD COLUMN report_config_type VARCHAR(20) NULL DEFAULT 'CATEGORY' COMMENT '报告配置: CATEGORY=按大类, ITEM=按子项'"),
    
    # test_work_order 扩展 - Phase1
    ("test_work_order", "ALTER TABLE test_work_order ADD COLUMN priority INT DEFAULT 1 COMMENT '优先级: 1=最低, 5=最高'"),
    ("test_work_order", "ALTER TABLE test_work_order ADD COLUMN expected_finish_date DATE NULL COMMENT '预计完成日期'"),
    ("test_work_order", "ALTER TABLE test_work_order ADD COLUMN actual_finish_date DATE NULL COMMENT '实际完成日期'"),
    ("test_work_order", "ALTER TABLE test_work_order ADD COLUMN claimed_by INT NULL COMMENT '领取人ID'"),
    ("test_work_order", "ALTER TABLE test_work_order ADD COLUMN claimed_at DATETIME NULL COMMENT '领取时间'"),
    
    # test_work_order 扩展 - Phase2
    ("test_work_order", "ALTER TABLE test_work_order ADD COLUMN condition_data JSON NULL COMMENT '测试条件实际值（JSON）'"),
    ("test_work_order", "ALTER TABLE test_work_order ADD COLUMN result_data JSON NULL COMMENT '测试结果数据（JSON）'"),
    ("test_work_order", "ALTER TABLE test_work_order ADD COLUMN raw_data JSON NULL COMMENT '原始测试数据（JSON）'"),
    ("test_work_order", "ALTER TABLE test_work_order ADD COLUMN attachments JSON NULL COMMENT '附件列表（JSON）'"),
    ("test_work_order", "ALTER TABLE test_work_order ADD COLUMN retest_count INT DEFAULT 0 COMMENT '重测次数'"),
    ("test_work_order", "ALTER TABLE test_work_order ADD COLUMN is_retest BOOLEAN DEFAULT FALSE COMMENT '是否重测'"),
    ("test_work_order", "ALTER TABLE test_work_order ADD COLUMN original_work_order_id INT NULL COMMENT '原始工单ID'"),
    ("test_work_order", "ALTER TABLE test_work_order ADD COLUMN retest_reason VARCHAR(500) NULL COMMENT '重测原因'"),
    
    # 修改 tester_id 为可空
    ("test_work_order", "ALTER TABLE test_work_order MODIFY COLUMN tester_id INT NULL COMMENT '测试人ID（可空，支持自动领取）'"),
]

INDEX_STATEMENTS = [
    ("test_category", "CREATE INDEX idx_parent_id ON test_category(parent_id)"),
    ("test_category", "CREATE INDEX idx_level ON test_category(level)"),
    ("test_work_order", "CREATE INDEX idx_priority ON test_work_order(priority)"),
    ("test_work_order", "CREATE INDEX idx_expected_finish_date ON test_work_order(expected_finish_date)"),
    ("test_work_order", "CREATE INDEX idx_claimed_by ON test_work_order(claimed_by)"),
    ("test_work_order", "CREATE INDEX idx_test_status_tester ON test_work_order(test_status, tester_id)"),
]

async def execute_alter():
    engine = create_async_engine(DATABASE_URL, echo=False)
    
    async with engine.connect() as conn:
        print("=" * 60)
        print("Fixing Phase1 Migration")
        print("=" * 60)
        
        # Execute ALTER statements
        print("\n[1] Adding columns...")
        for table, sql in ALTER_STATEMENTS:
            try:
                await conn.execute(text(sql))
                await conn.commit()
                print(f"  OK: {table} - {sql[:50]}...")
            except Exception as e:
                error_msg = str(e).lower()
                if 'duplicate' in error_msg or 'already exists' in error_msg:
                    print(f"  SKIP: {table} - already exists")
                else:
                    print(f"  FAIL: {table} - {e}")
        
        # Execute INDEX statements
        print("\n[2] Creating indexes...")
        for table, sql in INDEX_STATEMENTS:
            try:
                await conn.execute(text(sql))
                await conn.commit()
                print(f"  OK: {sql[:50]}...")
            except Exception as e:
                error_msg = str(e).lower()
                if 'duplicate' in error_msg or 'already exists' in error_msg:
                    print(f"  SKIP: already exists")
                else:
                    print(f"  FAIL: {e}")
        
        print("\n" + "=" * 60)
        print("Fix Complete")
        print("=" * 60)
    
    await engine.dispose()

if __name__ == '__main__':
    asyncio.run(execute_alter())