#!/usr/bin/env python3 # -*- coding: utf-8 -*- """ Balance Pick DB 복구 스크립트 - 누락된 컬럼 추가 (status, is_blinded 등) - 기존 게임 데이터의 status를 'approved'로 업데이트 - 카테고리/난이도/뱃지 기초 데이터 재삽입 - 시드 게임 데이터 재삽입 (중복 방지) """ import pymysql import uuid from datetime import datetime # 데이터베이스 연결 설정 DB_CONFIG = { 'host': 'officialsite.kr', 'port': 23306, 'user': 'admin', 'password': 'dlgks~123', 'database': 'app_master', 'charset': 'utf8mb4' } def repair_database(): conn = None try: print('=' * 60) print(' Balance Pick DB 복구 스크립트') print('=' * 60) # MySQL 연결 print('\n🔗 Connecting to MySQL database...') conn = pymysql.connect(**DB_CONFIG) cursor = conn.cursor() print('✅ MySQL database connected successfully') # ============================================ # STEP 1: 테이블 존재 여부 확인 # ============================================ print('\n' + '=' * 60) print(' STEP 1: 테이블 존재 여부 확인') print('=' * 60) required_tables = [ 'balancepick_categories', 'balancepick_difficulties', 'balancepick_games', 'balancepick_questions', 'balancepick_game_plays', 'balancepick_play_answers', 'balancepick_likes', 'balancepick_comments', 'balancepick_daily_plays', 'balancepick_premium', 'balancepick_badges', 'balancepick_user_badges', 'balancepick_streaks', 'balancepick_cleared_difficulties', 'balancepick_s3_uploads', 'balancepick_reports', ] cursor.execute("SHOW TABLES LIKE 'balancepick_%'") existing_tables = [row[0] for row in cursor.fetchall()] print(f' 현재 존재하는 테이블: {len(existing_tables)}개') for t in existing_tables: print(f' ✅ {t}') missing_tables = [t for t in required_tables if t not in existing_tables] if missing_tables: print(f'\n ⚠️ 누락된 테이블: {len(missing_tables)}개') for t in missing_tables: print(f' ❌ {t}') print('\n ⚠️ 누락된 테이블은 init.js 를 실행하여 생성하세요.') print(' (node -e "require(\'./database/init\').initializeDatabase()")') else: print('\n ✅ 필수 테이블이 모두 존재합니다.') # ============================================ # STEP 2: balancepick_games 컬럼 점검 및 추가 # ============================================ print('\n' + '=' * 60) print(' STEP 2: balancepick_games 컬럼 점검 및 추가') print('=' * 60) if 'balancepick_games' in existing_tables: cursor.execute("DESCRIBE balancepick_games") existing_columns = {row[0] for row in cursor.fetchall()} print(f' 현재 컬럼: {", ".join(sorted(existing_columns))}') # 누락 가능한 컬럼 목록 columns_to_add = [ ('status', "VARCHAR(50) DEFAULT 'approved'", 'comment_count'), ('share_count', "INT DEFAULT 0", 'comment_count'), ('is_blinded', "BOOLEAN DEFAULT FALSE", 'status'), ('blinded_at', "DATETIME NULL", 'is_blinded'), ('blind_reason', "TEXT NULL", 'blinded_at'), ('reviewed_at', "DATETIME NULL", 'blind_reason'), ('reviewed_by', "VARCHAR(36) NULL", 'reviewed_at'), ('reject_reason', "TEXT NULL", 'reviewed_by'), ] added_columns = [] for col_name, col_def, after_col in columns_to_add: if col_name not in existing_columns: try: sql = f"ALTER TABLE balancepick_games ADD COLUMN {col_name} {col_def}" if after_col and after_col in existing_columns: sql += f" AFTER {after_col}" cursor.execute(sql) added_columns.append(col_name) print(f' ✅ 컬럼 추가: {col_name} ({col_def})') except Exception as e: print(f' ⚠️ {col_name} 추가 실패: {e}') else: print(f' ✅ 이미 존재: {col_name}') # 인덱스 추가 try: cursor.execute("SHOW INDEX FROM balancepick_games WHERE Key_name = 'idx_status'") if not cursor.fetchall(): cursor.execute("ALTER TABLE balancepick_games ADD INDEX idx_status (status)") print(' ✅ 인덱스 추가: idx_status') except Exception as e: print(f' ⚠️ idx_status 인덱스: {e}') try: cursor.execute("SHOW INDEX FROM balancepick_games WHERE Key_name = 'idx_is_blinded'") if not cursor.fetchall(): cursor.execute("ALTER TABLE balancepick_games ADD INDEX idx_is_blinded (is_blinded)") print(' ✅ 인덱스 추가: idx_is_blinded') except Exception as e: print(f' ⚠️ idx_is_blinded 인덱스: {e}') conn.commit() if added_columns: print(f'\n ✅ {len(added_columns)}개 컬럼 추가 완료: {", ".join(added_columns)}') else: print(f'\n ✅ 모든 컬럼이 이미 존재합니다.') else: print(' ❌ balancepick_games 테이블이 없습니다. init.js를 먼저 실행하세요.') # ============================================ # STEP 3: 기존 게임 데이터 status 수정 # ============================================ print('\n' + '=' * 60) print(' STEP 3: 기존 게임 데이터 status 수정') print('=' * 60) if 'balancepick_games' in existing_tables: # NULL status 게임 수 확인 cursor.execute("SELECT COUNT(*) FROM balancepick_games WHERE status IS NULL") null_count = cursor.fetchone()[0] print(f' status가 NULL인 게임: {null_count}개') if null_count > 0: cursor.execute("UPDATE balancepick_games SET status = 'approved' WHERE status IS NULL") conn.commit() print(f' ✅ {null_count}개 게임의 status를 "approved"로 업데이트') else: print(' ✅ NULL status 게임 없음') # 전체 게임 현황 cursor.execute(""" SELECT status, COUNT(*) as cnt FROM balancepick_games GROUP BY status """) rows = cursor.fetchall() print(f'\n 게임 상태 현황:') for status, cnt in rows: print(f' - {status}: {cnt}개') # 전체 게임 수 cursor.execute("SELECT COUNT(*) FROM balancepick_games") total = cursor.fetchone()[0] print(f' 총 게임: {total}개') # ============================================ # STEP 4: 카테고리 데이터 확인/복구 # ============================================ print('\n' + '=' * 60) print(' STEP 4: 카테고리 데이터 확인/복구') print('=' * 60) categories = [ ('honbulho', '호불호', '좋아하거나 싫어하거나, 당신은 어느 쪽?', 'honbulho', '0xFF5B7AC7', 1), ('couple', '커플', '직진파 vs 썸타기파, 당신의 연애 밸런스는?', 'couple', '0xFFE97A72', 2), ('worker', '직장인', '칼퇴냐 야근이냐, 직장 밸런스 전쟁!', 'worker', '0xFF5B7AC7', 3), ('tmi', 'TMI', '내 사소한 취향, 누가 더 잘 맞출까?', 'tmi', '0xFFE97A72', 4), ('value', '가치관', '이성보다 감성? 현실파 vs 꿈파?', 'value', '0xFF5B7AC7', 5), ('preference', '추구미', '소확행 vs 대확행, 당신의 인생 밸런스는?', 'preference', '0xFFE97A72', 6), ('ideal_type', '이상형', '비주얼 vs 성격, 당신의 최종 선택은?', 'ideal_type', '0xFF5B7AC7', 7), ('random', '랜덤', '무슨 게임이 나올지 몰라요!', 'random', '0xFFE97A72', 8), ] if 'balancepick_categories' in existing_tables: cursor.execute("SELECT COUNT(*) FROM balancepick_categories") cat_count = cursor.fetchone()[0] print(f' 현재 카테고리: {cat_count}개') for cat in categories: cursor.execute(""" INSERT INTO balancepick_categories (id, name, description, icon_name, color, order_index) VALUES (%s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE name = VALUES(name), description = VALUES(description), icon_name = VALUES(icon_name), color = VALUES(color), order_index = VALUES(order_index) """, cat) conn.commit() print(f' ✅ {len(categories)}개 카테고리 확인/복구 완료') # ============================================ # STEP 5: 난이도 데이터 확인/복구 # ============================================ print('\n' + '=' * 60) print(' STEP 5: 난이도 데이터 확인/복구') print('=' * 60) difficulties = [ ('intro', '입문', 1, '0xFF90EE90', 1), ('debate', '논쟁', 2, '0xFFFFD700', 2), ('life', '생활', 3, '0xFFFFA500', 3), ('extreme', '극강', 4, '0xFFFF6347', 4), ('real', '찐', 5, '0xFFFF0000', 5), ] if 'balancepick_difficulties' in existing_tables: cursor.execute("SELECT COUNT(*) FROM balancepick_difficulties") diff_count = cursor.fetchone()[0] print(f' 현재 난이도: {diff_count}개') for diff in difficulties: cursor.execute(""" INSERT INTO balancepick_difficulties (id, name, stars, color, order_index) VALUES (%s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE name = VALUES(name), stars = VALUES(stars), color = VALUES(color), order_index = VALUES(order_index) """, diff) conn.commit() print(f' ✅ {len(difficulties)}개 난이도 확인/복구 완료') # ============================================ # STEP 6: 뱃지 데이터 확인/복구 # ============================================ print('\n' + '=' * 60) print(' STEP 6: 뱃지 데이터 확인/복구') print('=' * 60) badges = [ ('first_play', '첫 발걸음', '첫 게임 플레이', 'play_arrow', '0xFF4CAF50', 'total_plays', 1, 1), ('plays_10', '밸런스 입문자', '10번 플레이', 'sports_esports', '0xFF2196F3', 'total_plays', 10, 2), ('plays_50', '밸런스 중급자', '50번 플레이', 'military_tech', '0xFF9C27B0', 'total_plays', 50, 3), ('plays_100', '밸런스 마스터', '100번 플레이', 'emoji_events', '0xFFFF9800', 'total_plays', 100, 4), ('streak_3', '3일 연속', '3일 연속 플레이', 'local_fire_department', '0xFFE91E63', 'streak', 3, 5), ('streak_5', '연속왕', '5일 연속 플레이', 'whatshot', '0xFFFF5722', 'streak', 5, 6), ('streak_7', '일주일 도전', '7일 연속 플레이', 'bolt', '0xFFF44336', 'streak', 7, 7), ('streak_30', '한 달 완주', '30일 연속 플레이', 'diamond', '0xFF673AB7', 'streak', 30, 8), ('speed_1', '스피드', '1초 안에 선택', 'flash_on', '0xFFFFEB3B', 'speed', 1, 9), ('speed_10', '번개손', '1초 안에 10회 선택', 'offline_bolt', '0xFFFFC107', 'speed_count', 10, 10), ('creator_1', '크리에이터', '첫 게임 제작', 'create', '0xFF00BCD4', 'games_created', 1, 11), ('creator_5', '게임 메이커', '5개 게임 제작', 'construction', '0xFF009688', 'games_created', 5, 12), ('likes_10', '인기인', '좋아요 10개 획득', 'favorite', '0xFFE91E63', 'likes_received', 10, 13), ('likes_50', '스타', '좋아요 50개 획득', 'star', '0xFFFFD700', 'likes_received', 50, 14), ('likes_100', '슈퍼스타', '좋아요 100개 획득', 'auto_awesome', '0xFFFF4081', 'likes_received', 100, 15), ('all_difficulty', '정복자', '모든 난이도 클리어', 'workspace_premium', '0xFF7C4DFF', 'all_difficulty', 5, 16), ] if 'balancepick_badges' in existing_tables: cursor.execute("SELECT COUNT(*) FROM balancepick_badges") badge_count = cursor.fetchone()[0] print(f' 현재 뱃지: {badge_count}개') 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(f' ✅ {len(badges)}개 뱃지 확인/복구 완료') # ============================================ # STEP 7: 게임 데이터 유무 확인 # ============================================ print('\n' + '=' * 60) print(' STEP 7: 게임 데이터 현황') print('=' * 60) if 'balancepick_games' in existing_tables: cursor.execute(""" SELECT c.name as category, d.name as difficulty, COUNT(*) as game_count FROM balancepick_games g LEFT JOIN balancepick_categories c ON g.category_id = c.id LEFT JOIN balancepick_difficulties d ON g.difficulty_id = d.id WHERE g.status = 'approved' GROUP BY g.category_id, g.difficulty_id ORDER BY c.order_index, d.order_index """) rows = cursor.fetchall() if rows: print(' 카테고리/난이도별 게임 수:') for cat, diff, cnt in rows: print(f' {cat or "미분류"} / {diff or "미분류"}: {cnt}개') else: print(' ⚠️ approved 상태 게임이 없습니다!') print(' → seed_balance_games.py 를 실행하여 게임 데이터를 삽입하세요.') # 질문 데이터 확인 cursor.execute("SELECT COUNT(*) FROM balancepick_questions") q_count = cursor.fetchone()[0] print(f'\n 총 질문 수: {q_count}개') if q_count == 0: print(' ⚠️ 질문 데이터가 없습니다!') print(' → seed_balance_games.py 를 실행하여 질문 데이터를 삽입하세요.') # ============================================ # STEP 8: users 테이블 확인 (system 유저) # ============================================ print('\n' + '=' * 60) print(' STEP 8: system 사용자 확인') print('=' * 60) try: cursor.execute("SELECT id FROM users WHERE social_id = 'system' AND app_name = 'balancepick'") if not cursor.fetchone(): # system 유저가 없으면 생성 cursor.execute(""" INSERT INTO users (id, social_id, login_type, app_name, name, device_serial) VALUES ('system', 'system', 'device', 'balancepick', 'System', 'system') ON DUPLICATE KEY UPDATE name = 'System' """) conn.commit() print(' ✅ system 사용자 생성 완료') else: print(' ✅ system 사용자 존재 확인') except Exception as e: print(f' ⚠️ system 사용자 확인 실패: {e}') # ============================================ # 최종 요약 # ============================================ print('\n' + '=' * 60) print(' 복구 작업 완료 요약') print('=' * 60) if 'balancepick_games' in existing_tables: cursor.execute("SELECT COUNT(*) FROM balancepick_games WHERE status = 'approved'") approved = cursor.fetchone()[0] cursor.execute("SELECT COUNT(*) FROM balancepick_categories WHERE is_active = TRUE") cats = cursor.fetchone()[0] cursor.execute("SELECT COUNT(*) FROM balancepick_difficulties WHERE is_active = TRUE") diffs = cursor.fetchone()[0] cursor.execute("SELECT COUNT(*) FROM balancepick_questions") qs = cursor.fetchone()[0] cursor.execute("SELECT COUNT(*) FROM balancepick_badges WHERE is_active = TRUE") bgs = cursor.fetchone()[0] print(f' 카테고리: {cats}개') print(f' 난이도: {diffs}개') print(f' 뱃지: {bgs}개') print(f' 게임 (approved): {approved}개') print(f' 질문: {qs}개') if approved > 0 and cats >= 8 and diffs >= 5 and qs > 0: print('\n ✅ 밸런스픽 게임 시작에 필요한 데이터가 준비되었습니다!') else: print('\n ⚠️ 아직 부족한 데이터가 있습니다:') if cats < 8: print(f' - 카테고리 {8-cats}개 부족') if diffs < 5: print(f' - 난이도 {5-diffs}개 부족') if approved == 0: print(' - approved 게임이 없음 → seed_balance_games.py 실행 필요') if qs == 0: print(' - 질문 데이터 없음 → seed_balance_games.py 실행 필요') print('\n✨ 복구 스크립트 완료!') except Exception as error: print(f'\n❌ Error: {error}') if conn: conn.rollback() raise finally: if conn: conn.close() print('🔒 Database connection closed') if __name__ == '__main__': import traceback try: repair_database() except Exception as e: print(f'\n❌ Failed: {e}') print('\n상세 에러 정보:') traceback.print_exc()