import mysql.connector from mysql.connector import Error # DB Configuration DB_CONFIG = { 'host': 'officialsite.kr', 'port': 23306, 'user': 'admin', 'password': 'dlgks~123', 'database': 'app_master' } def run_migrations(): conn = None try: conn = mysql.connector.connect(**DB_CONFIG) cursor = conn.cursor() print("DB Connected") # 1. share_count column try: cursor.execute(""" ALTER TABLE balancepick_games ADD COLUMN share_count INT DEFAULT 0 AFTER like_count """) conn.commit() print("Added share_count column") except Error as e: if e.errno == 1060: # Duplicate column print("share_count column already exists") else: print(f"share_count error: {e}") # 2. balancepick_daily_plays cursor.execute(""" CREATE TABLE IF NOT EXISTS balancepick_daily_plays ( id INT AUTO_INCREMENT PRIMARY KEY, device_serial VARCHAR(255) NOT NULL, play_date DATE NOT NULL, play_count INT DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY unique_device_date (device_serial, play_date), INDEX idx_device_serial (device_serial), INDEX idx_play_date (play_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci """) conn.commit() print("Created balancepick_daily_plays table") # 3. balancepick_premium cursor.execute(""" CREATE TABLE IF NOT EXISTS balancepick_premium ( id INT AUTO_INCREMENT PRIMARY KEY, device_serial VARCHAR(255) NOT NULL UNIQUE, is_premium BOOLEAN DEFAULT FALSE, subscription_type ENUM('monthly', 'yearly', 'lifetime') NULL, started_at TIMESTAMP NULL, expires_at TIMESTAMP NULL, purchase_token TEXT, platform VARCHAR(50) DEFAULT 'android', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_device_serial (device_serial), INDEX idx_expires_at (expires_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci """) conn.commit() print("Created balancepick_premium table") # 4. balancepick_badges cursor.execute(""" CREATE TABLE IF NOT EXISTS balancepick_badges ( id VARCHAR(50) PRIMARY KEY, name VARCHAR(100) NOT NULL, description VARCHAR(255), icon_name VARCHAR(50), color VARCHAR(20), condition_type VARCHAR(50), condition_value INT DEFAULT 0, order_index INT DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci """) conn.commit() print("Created balancepick_badges table") # 5. balancepick_user_badges cursor.execute(""" CREATE TABLE IF NOT EXISTS balancepick_user_badges ( id INT AUTO_INCREMENT PRIMARY KEY, device_serial VARCHAR(255) NOT NULL, badge_id VARCHAR(50) NOT NULL, earned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY unique_user_badge (device_serial, badge_id), INDEX idx_device_serial (device_serial) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci """) conn.commit() print("Created balancepick_user_badges table") # 6. balancepick_streaks cursor.execute(""" CREATE TABLE IF NOT EXISTS balancepick_streaks ( id INT AUTO_INCREMENT PRIMARY KEY, device_serial VARCHAR(255) NOT NULL UNIQUE, current_streak INT DEFAULT 0, max_streak INT DEFAULT 0, last_play_date DATE, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_device_serial (device_serial) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci """) conn.commit() print("Created balancepick_streaks table") # 7. balancepick_cleared_difficulties cursor.execute(""" CREATE TABLE IF NOT EXISTS balancepick_cleared_difficulties ( id INT AUTO_INCREMENT PRIMARY KEY, device_serial VARCHAR(255) NOT NULL, category_id VARCHAR(50) NOT NULL, difficulty_id VARCHAR(50) NOT NULL, cleared_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY unique_clear (device_serial, category_id, difficulty_id), INDEX idx_device_serial (device_serial), INDEX idx_category (category_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci """) conn.commit() print("Created balancepick_cleared_difficulties table") # 8. Insert badges data badges = [ ('first_play', 'First Step', 'First game play', 'play_arrow', '0xFF4CAF50', 'total_plays', 1, 1), ('plays_10', 'Balance Beginner', '10 plays', 'sports_esports', '0xFF2196F3', 'total_plays', 10, 2), ('plays_50', 'Balance Intermediate', '50 plays', 'military_tech', '0xFF9C27B0', 'total_plays', 50, 3), ('plays_100', 'Balance Master', '100 plays', 'emoji_events', '0xFFFF9800', 'total_plays', 100, 4), ('streak_3', '3 Day Streak', '3 days in a row', 'local_fire_department', '0xFFE91E63', 'streak', 3, 5), ('streak_5', 'Streak King', '5 days in a row', 'whatshot', '0xFFFF5722', 'streak', 5, 6), ('streak_7', 'Week Challenge', '7 days in a row', 'bolt', '0xFFF44336', 'streak', 7, 7), ('streak_30', 'Month Complete', '30 days in a row', 'diamond', '0xFF673AB7', 'streak', 30, 8), ('speed_1', 'Speed', 'Select in 1 second', 'flash_on', '0xFFFFEB3B', 'speed', 1, 9), ('speed_10', 'Lightning Hands', '10 fast selections', 'offline_bolt', '0xFFFFC107', 'speed_count', 10, 10), ('creator_1', 'Creator', 'First game created', 'create', '0xFF00BCD4', 'games_created', 1, 11), ('creator_5', 'Game Maker', '5 games created', 'construction', '0xFF009688', 'games_created', 5, 12), ('likes_10', 'Popular', '10 likes received', 'favorite', '0xFFE91E63', 'likes_received', 10, 13), ('likes_50', 'Star', '50 likes received', 'star', '0xFFFFD700', 'likes_received', 50, 14), ('likes_100', 'Superstar', '100 likes received', 'auto_awesome', '0xFFFF4081', 'likes_received', 100, 15), ('all_difficulty', 'Conqueror', 'All difficulties cleared', 'workspace_premium', '0xFF7C4DFF', 'all_difficulty', 5, 16), ] for badge in badges: cursor.execute(""" INSERT INTO balancepick_badges (id, name, description, icon_name, color, condition_type, condition_value, order_index) VALUES (%s, %s, %s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE name = VALUES(name), description = VALUES(description), icon_name = VALUES(icon_name), color = VALUES(color), condition_type = VALUES(condition_type), condition_value = VALUES(condition_value), order_index = VALUES(order_index) """, badge) conn.commit() print("Inserted badges data") print("\n=== All migrations completed ===") except Error as e: print(f"Error: {e}") finally: if conn and conn.is_connected(): cursor.close() conn.close() print("DB connection closed") if __name__ == "__main__": run_migrations()