celt313/agentic-rag-gamequest
0
1#!/usr/bin/env python3
2"""
3Update Local Database URLs from JSONL
4"""
5
6import psycopg2
7import json
8import os
9from tqdm import tqdm
10
11def get_local_db_config():
12 """Get local database configuration"""
13 return {
14 'host': 'localhost',
15 'database': 'gamequest',
16 'user': 'postgres',
17 'password': os.environ.get('LOCAL_DB_PASSWORD', ''),
18 'port': 5432
19 }
20
21def load_jsonl_data(jsonl_path):
22 """Load data from JSONL file"""
23 try:
24 print(f"📄 Loading data from {jsonl_path}...")
25
26 if not os.path.exists(jsonl_path):
27 print(f"❌ File not found: {jsonl_path}")
28 return None
29
30 url_data = {}
31 total_lines = 0
32 processed_lines = 0
33
34 # Count total lines first
35 with open(jsonl_path, 'r', encoding='utf-8') as f:
36 total_lines = sum(1 for _ in f)
37
38 print(f"📊 Found {total_lines:,} lines in JSONL file")
39
40 # Process each line
41 with open(jsonl_path, 'r', encoding='utf-8') as f:
42 for line in tqdm(f, total=total_lines, desc="Loading JSONL"):
43 line = line.strip()
44 if not line:
45 continue
46
47 try:
48 data = json.loads(line)
49 game_id = data.get('id')
50
51 if game_id:
52 url_data[game_id] = {
53 'sample_cover_url': data.get('sample_cover_url'),
54 'sample_screenshot_urls': data.get('sample_screenshot_urls')
55 }
56 processed_lines += 1
57
58 except json.JSONDecodeError as e:
59 print(f"⚠️ JSON decode error: {e}")
60 continue
61
62 print(f"✅ Loaded {processed_lines:,} games with URL data")
63 return url_data
64
65 except Exception as e:
66 print(f"❌ Error loading JSONL: {e}")
67 return None
68
69def update_local_urls(url_data):
70 """Update URLs in local database"""
71 try:
72 print("🔄 Updating URLs in LOCAL database...")
73
74 config = get_local_db_config()
75 conn = psycopg2.connect(**config)
76 cursor = conn.cursor()
77
78 # Get total games to update
79 cursor.execute("SELECT COUNT(*) FROM games WHERE id = ANY(%s);", (list(url_data.keys()),))
80 total_games = cursor.fetchone()[0]
81
82 print(f"📊 Found {total_games:,} games to update in local DB")
83
84 if total_games == 0:
85 print("❌ No matching games found!")
86 return False
87
88 # Update URLs
89 updated_count = 0
90 skipped_count = 0
91
92 update_query = """
93 UPDATE games
94 SET sample_cover_url = %s, sample_screenshot_urls = %s
95 WHERE id = %s;
96 """
97
98 for game_id, urls in tqdm(url_data.items(), desc="Updating URLs"):
99 cover_url = urls.get('sample_cover_url')
100 screenshot_urls = urls.get('sample_screenshot_urls')
101
102 # Only update if we have actual URLs
103 if cover_url or screenshot_urls:
104 try:
105 cursor.execute(update_query, (cover_url, screenshot_urls, game_id))
106 updated_count += 1
107 except Exception as e:
108 print(f"⚠️ Error updating game {game_id}: {e}")
109 skipped_count += 1
110 else:
111 skipped_count += 1
112
113 # Commit changes
114 conn.commit()
115
116 print(f"\n✅ URL update completed!")
117 print(f" Updated: {updated_count:,} games")
118 print(f" Skipped: {skipped_count:,} games")
119
120 # Verify updates
121 cursor.execute("SELECT COUNT(*) FROM games WHERE sample_cover_url IS NOT NULL;")
122 games_with_covers = cursor.fetchone()[0]
123
124 cursor.execute("SELECT COUNT(*) FROM games WHERE sample_screenshot_urls IS NOT NULL;")
125 games_with_screenshots = cursor.fetchone()[0]
126
127 print(f"\n🎯 Verification:")
128 print(f" Games with cover URLs: {games_with_covers:,}")
129 print(f" Games with screenshot URLs: {games_with_screenshots:,}")
130
131 cursor.close()
132 conn.close()
133
134 return True
135
136 except Exception as e:
137 print(f"❌ Error updating URLs: {e}")
138 return False
139
140def show_sample_updates():
141 """Show sample of updated games"""
142 try:
143 print("\n🔍 Sample of updated games:")
144
145 config = get_local_db_config()
146 conn = psycopg2.connect(**config)
147 cursor = conn.cursor()
148
149 # Show games with cover URLs
150 cursor.execute("""
151 SELECT id, title, sample_cover_url
152 FROM games
153 WHERE sample_cover_url IS NOT NULL
154 LIMIT 3;
155 """)
156 games_with_covers = cursor.fetchall()
157
158 if games_with_covers:
159 print("\n🖼️ Games with cover URLs:")
160 for game in games_with_covers:
161 game_id, title, cover_url = game
162 print(f" {game_id}: {title[:50]}...")
163 print(f" Cover: {cover_url}")
164
165 # Show games with screenshot URLs
166 cursor.execute("""
167 SELECT id, title, sample_screenshot_urls
168 FROM games
169 WHERE sample_screenshot_urls IS NOT NULL
170 LIMIT 3;
171 """)
172 games_with_screenshots = cursor.fetchall()
173
174 if games_with_screenshots:
175 print("\n📸 Games with screenshot URLs:")
176 for game in games_with_screenshots:
177 game_id, title, screenshot_urls = game
178 print(f" {game_id}: {title[:50]}...")
179 print(f" Screenshots: {len(screenshot_urls) if screenshot_urls else 0} URLs")
180
181 cursor.close()
182 conn.close()
183
184 except Exception as e:
185 print(f"❌ Error showing samples: {e}")
186
187if __name__ == "__main__":
188 print("🎮 GameQuest Local URL Updater")
189 print("=" * 40)
190
191 # Path to JSONL file
192 jsonl_path = "data/mobygames_index_updated_dates.jsonl"
193
194 print(f"\n🚀 Starting LOCAL URL update process...")
195 print(f"📄 JSONL file: {jsonl_path}")
196 print("💡 This will update your local DB first, then you can migrate to Aiven!")
197
198 # Step 1: Load JSONL data
199 print("\n📦 Step 1: Loading JSONL data...")
200 url_data = load_jsonl_data(jsonl_path)
201
202 if not url_data:
203 print("❌ Cannot proceed without URL data")
204 exit(1)
205
206 # Step 2: Update local database
207 print("\n🔄 Step 2: Updating LOCAL database...")
208 if update_local_urls(url_data):
209 print("\n🎉 LOCAL URL update completed successfully!")
210
211 # Step 3: Show samples
212 show_sample_updates()
213
214 print("\n✅ Next step: Run 'python migrate_to_aiven.py' to migrate everything to Aiven!")
215 else:
216 print("\n❌ URL update failed!")