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("Creating balancepick_reports table")
print("=" * 60)

# Create reports table
try:
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS balancepick_reports (
            id VARCHAR(36) PRIMARY KEY,
            game_id VARCHAR(36) NOT NULL,
            device_serial VARCHAR(255) NOT NULL,
            reason_type VARCHAR(50) NOT NULL,
            reason_detail TEXT,
            status ENUM('pending', 'reviewed', 'resolved') DEFAULT 'pending',
            admin_note TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            reviewed_at TIMESTAMP NULL,
            reviewed_by VARCHAR(36) NULL,
            INDEX idx_game_id (game_id),
            INDEX idx_device_serial (device_serial),
            INDEX idx_status (status),
            INDEX idx_created_at (created_at),
            FOREIGN KEY (game_id) REFERENCES balancepick_games(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    """)
    print("[OK] Created balancepick_reports table")
except Exception as e:
    print(f"[ERROR] {e}")

conn.commit()

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

cursor.execute("DESCRIBE balancepick_reports")
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!")