import pymysql

conn = pymysql.connect(
    host='officialsite.kr',
    port=23306,
    user='admin',
    password='dlgks~123',
    database='app_master',
    charset='utf8mb4'
)

cursor = conn.cursor(pymysql.cursors.DictCursor)

print("=" * 60)
print("Adding review status to balancepick_games table")
print("=" * 60)

# 1. Add status column (pending, approved, rejected)
try:
    cursor.execute("""
        ALTER TABLE balancepick_games
        ADD COLUMN status ENUM('pending', 'approved', 'rejected') DEFAULT 'approved' AFTER is_public
    """)
    print("[OK] Added status column")
except Exception as e:
    if 'Duplicate column' in str(e):
        print("[SKIP] status column already exists")
    else:
        print(f"[ERROR] {e}")

# 2. Add reviewed_at column
try:
    cursor.execute("""
        ALTER TABLE balancepick_games
        ADD COLUMN reviewed_at TIMESTAMP NULL AFTER status
    """)
    print("[OK] Added reviewed_at column")
except Exception as e:
    if 'Duplicate column' in str(e):
        print("[SKIP] reviewed_at column already exists")
    else:
        print(f"[ERROR] {e}")

# 3. Add reviewed_by column
try:
    cursor.execute("""
        ALTER TABLE balancepick_games
        ADD COLUMN reviewed_by VARCHAR(36) NULL AFTER reviewed_at
    """)
    print("[OK] Added reviewed_by column")
except Exception as e:
    if 'Duplicate column' in str(e):
        print("[SKIP] reviewed_by column already exists")
    else:
        print(f"[ERROR] {e}")

# 4. Add reject_reason column
try:
    cursor.execute("""
        ALTER TABLE balancepick_games
        ADD COLUMN reject_reason TEXT NULL AFTER reviewed_by
    """)
    print("[OK] Added reject_reason column")
except Exception as e:
    if 'Duplicate column' in str(e):
        print("[SKIP] reject_reason column already exists")
    else:
        print(f"[ERROR] {e}")

# 5. Add index for status
try:
    cursor.execute("""
        ALTER TABLE balancepick_games
        ADD INDEX idx_status (status)
    """)
    print("[OK] Added index for status")
except Exception as e:
    if 'Duplicate key' in str(e):
        print("[SKIP] status index already exists")
    else:
        print(f"[ERROR] {e}")

conn.commit()

# Verify changes
print("\n" + "=" * 60)
print("Verifying table structure")
print("=" * 60)

cursor.execute("DESCRIBE balancepick_games")
columns = cursor.fetchall()

for col in columns:
    print(f"  {col['Field']:20} | {col['Type']:40} | {col['Null']} | {col['Default']}")

cursor.close()
conn.close()

print("\n[DONE] Migration completed!")