#!/usr/bin/env python3
import pymysql

DB_CONFIG = {
    'host': 'officialsite.kr',
    'port': 23306,
    'user': 'admin',
    'password': 'dlgks~123',
    'database': 'app_master',
    'charset': 'utf8mb4'
}

conn = pymysql.connect(**DB_CONFIG)
cursor = conn.cursor()

print("=== Checking games with NULL device_serial ===")
cursor.execute("""
    SELECT COUNT(*) FROM balancepick_games
    WHERE device_serial IS NULL OR device_serial = ''
""")
count = cursor.fetchone()[0]
print(f"  Games with NULL/empty device_serial: {count}")

print("\n=== Updating system games device_serial ===")
cursor.execute("""
    UPDATE balancepick_games
    SET device_serial = 'system'
    WHERE user_id = 'system' AND (device_serial IS NULL OR device_serial = '')
""")
affected = cursor.rowcount
conn.commit()
print(f"  Updated {affected} games")

print("\n=== Verifying ===")
cursor.execute("""
    SELECT device_serial, COUNT(*) as cnt
    FROM balancepick_games
    GROUP BY device_serial
    ORDER BY cnt DESC
    LIMIT 10
""")
for row in cursor.fetchall():
    print(f"  {row[0]}: {row[1]}")

cursor.close()
conn.close()
print("\nDone!")