#!/usr/bin/env python3 """ Multi-Item Mock Data Generator for RSC Revolution Market Analytics Generates realistic historical trading data for multiple items with fluctuating prices. """ import sys import os sys.path.append(os.path.dirname(os.path.abspath(__file__))) import random import time from datetime import datetime, timedelta from config.database import get_db_connection import math class MultiItemMockDataGenerator: def __init__(self): # Item configurations: item_id -> starting_price self.items_config = { 81: 60000, 93: 50000, 112: 60000, 183: 1000, 221: 5000, 316: 10000, 373: 200, 401: 70000, 452: 5000, 522: 30000, 578: 1000000, 546: 900, 575: 2000000, 594: 120000, 523: 8000, 38: 300 } # Different volatility profiles for different price ranges self.get_volatility_profile = lambda price: { 'high_value': 0.08 if price >= 500000 else 0.12, # 8-12% for expensive items 'mid_value': 0.15 if 10000 <= price < 500000 else 0.15, # 15% for mid-range 'low_value': 0.25 if price < 10000 else 0.25 # 25% for cheap items }[ 'high_value' if price >= 500000 else 'mid_value' if price >= 10000 else 'low_value' ] def generate_price_with_fluctuation(self, item_id, base_price, hours_ago, order_within_hour=0): """Generate realistic price with significant fluctuations between orders""" # Get volatility for this price range volatility = self.get_volatility_profile(base_price) # Long-term trend (slight upward bias over time) trend_factor = 1 + (0.01 * (168 - hours_ago) / 168) # 1% trend over 7 days # Market cycles (daily and weekly patterns) daily_cycle = 1 + 0.05 * math.sin(hours_ago * 0.26) # Daily price cycle weekly_cycle = 1 + 0.03 * math.sin(hours_ago * 0.037) # Weekly price cycle # Order-to-order fluctuation (this creates the price variation you want) order_fluctuation = 1 + random.uniform(-volatility, volatility) # Occasional market events (spikes/crashes) if random.random() < 0.02: # 2% chance of significant event event_factor = random.choice([0.7, 0.75, 0.8, 1.25, 1.3, 1.4]) else: event_factor = 1.0 # Small additional randomness per order within the same hour micro_fluctuation = 1 + random.uniform(-0.05, 0.05) # Calculate final price final_price = (base_price * trend_factor * daily_cycle * weekly_cycle * order_fluctuation * event_factor * micro_fluctuation) # Ensure minimum price (10% of base price) min_price = base_price * 0.1 return max(int(min_price), int(final_price)) def generate_volume_for_item(self, item_id, base_price, hours_ago): """Generate trading volume based on item price tier and time""" # Volume profiles based on price if base_price >= 500000: # High-value items (less frequent, smaller volume) base_volume = random.randint(1, 8) elif base_price >= 50000: # Mid-high value base_volume = random.randint(3, 15) elif base_price >= 5000: # Mid value base_volume = random.randint(5, 25) else: # Low value (high volume) base_volume = random.randint(10, 50) # Time-based modifiers if hours_ago < 24: # Recent activity boost time_factor = 1.5 elif hours_ago < 168: # Last week time_factor = 1.2 else: # Older data time_factor = 0.8 # Random variation variation = random.uniform(0.5, 2.0) # Occasional volume spikes if random.random() < 0.05: # 5% chance spike_factor = random.uniform(3, 8) else: spike_factor = 1.0 final_volume = int(base_volume * time_factor * variation * spike_factor) return max(1, final_volume) def create_trading_session(self, item_id, base_price, hours_ago, target_volume): """Create multiple orders for a trading session with fluctuating prices""" trades = [] remaining_volume = target_volume # Create 1-8 individual trades per session num_trades = random.randint(1, min(8, target_volume)) for trade_num in range(num_trades): if remaining_volume <= 0: break # Volume for this trade if trade_num == num_trades - 1: # Last trade gets remaining volume volume = remaining_volume else: max_volume = min(remaining_volume // 2, 20) volume = random.randint(1, max(1, max_volume)) remaining_volume -= volume # Generate fluctuating price for this specific order trade_price = self.generate_price_with_fluctuation( item_id, base_price, hours_ago, trade_num ) # Random time within the hour trade_time = datetime.now() - timedelta( hours=hours_ago, minutes=random.randint(0, 59), seconds=random.randint(0, 59) ) trades.append({ 'item_id': item_id, 'price': trade_price, 'volume': volume, 'timestamp': trade_time }) return trades def generate_multi_item_mock_data(): """Generate comprehensive mock data for multiple items""" print("๐ŸŽฒ Generating Multi-Item Mock Data") print("=" * 50) generator = MultiItemMockDataGenerator() # Time periods to generate (in hours ago) time_periods = [] # Recent 48 hours (every 2 hours) time_periods.extend(range(0, 49, 2)) # Last 2 weeks (every 6 hours) time_periods.extend(range(54, 337, 6)) # Last 3 months (every 24 hours) time_periods.extend(range(360, 2161, 24)) # Older data (every 72 hours) time_periods.extend(range(2232, 4321, 72)) with get_db_connection() as conn: cursor = conn.cursor() print("๐Ÿงน Cleaning existing mock data...") # Clean up existing mock data (sale_id >= 2000) cursor.execute(""" DELETE ms FROM pk_market_sales ms JOIN pk_market_inactive mi ON ms.auction_id = mi.auction_id WHERE mi.item_id IN (81,93,112,183,221,316,373,401,452,522,578,546,575,594,523,38) AND ms.sale_id >= 2000 """) cursor.execute(""" DELETE FROM pk_market_inactive WHERE item_id IN (81,93,112,183,221,316,373,401,452,522,578,546,575,594,523,38) AND auction_id >= 2000 """) cursor.execute(""" DELETE FROM pk_market_active WHERE item_id IN (81,93,112,183,221,316,373,401,452,522,578,546,575,594,523,38) AND auctionID >= 3000 """) auction_id_counter = 2000 sale_id_counter = 2000 total_trades = 0 # Generate data for each item for item_id, base_price in generator.items_config.items(): print(f"\n๐Ÿ’ฐ Generating data for Item {item_id} (Base: {base_price:,} GP)") item_trades = 0 # Generate trading sessions across time periods for hours_ago in time_periods: # Skip some periods randomly to create realistic gaps if random.random() < 0.3: # 30% chance to skip a period continue # Generate volume for this time period session_volume = generator.generate_volume_for_item(item_id, base_price, hours_ago) # Create trading session with fluctuating prices trades = generator.create_trading_session( item_id, base_price, hours_ago, session_volume ) # Insert each trade for trade in trades: # Insert into pk_market_inactive cursor.execute(""" INSERT INTO pk_market_inactive (auction_id, player_id, item_id, item_initial_amount, item_amount, price_each, time, listing_type, remove_type, deadman) VALUES (%s, %s, %s, %s, 0, %s, %s, 0, 1, 1) """, ( auction_id_counter, random.randint(1, 1000), # Random seller trade['item_id'], trade['volume'], trade['price'], int(trade['timestamp'].timestamp()) )) # Insert into pk_market_sales cursor.execute(""" INSERT INTO pk_market_sales (auction_id, player_id, amount, time, sale_id) VALUES (%s, %s, %s, %s, %s) """, ( auction_id_counter, random.randint(1, 1000), # Random buyer trade['volume'], int(trade['timestamp'].timestamp()), sale_id_counter )) auction_id_counter += 1 sale_id_counter += 1 item_trades += 1 total_trades += 1 print(f" โœ… Created {item_trades} trades for Item {item_id}") # Generate some active orders for current market state print(f"\n๐Ÿ“Š Generating active market orders...") order_id_counter = 3000 current_time = int(time.time()) active_orders = 0 for item_id, base_price in generator.items_config.items(): # Current estimated price current_price = generator.generate_price_with_fluctuation(item_id, base_price, 0) # Create 2-4 buy orders per item (below current price) num_buy_orders = random.randint(2, 4) for _ in range(num_buy_orders): buy_price = int(current_price * random.uniform(0.85, 0.98)) buy_amount = random.randint(1, 15) cursor.execute(""" INSERT INTO pk_market_active (auctionID, item_id, item_initial_amount, item_amount_left, price_each, player_id, listing_type, time, deadman) VALUES (%s, %s, %s, %s, %s, %s, 1, %s, 0) """, ( order_id_counter, item_id, buy_amount, buy_amount, buy_price, random.randint(1, 1000), current_time )) order_id_counter += 1 active_orders += 1 # Create 2-4 sell orders per item (above current price) num_sell_orders = random.randint(2, 4) for _ in range(num_sell_orders): sell_price = int(current_price * random.uniform(1.02, 1.15)) sell_amount = random.randint(1, 20) cursor.execute(""" INSERT INTO pk_market_active (auctionID, item_id, item_initial_amount, item_amount_left, price_each, player_id, listing_type, time, deadman) VALUES (%s, %s, %s, %s, %s, %s, 0, %s, 0) """, ( order_id_counter, item_id, sell_amount, sell_amount, sell_price, random.randint(1, 1000), current_time )) order_id_counter += 1 active_orders += 1 conn.commit() print(f"\nโœ… Multi-Item Mock Data Generation Completed!") print(f"๐Ÿ“Š Generated {total_trades:,} total trades across {len(generator.items_config)} items") print(f"๐Ÿ“ˆ Price fluctuations: Order-to-order variations with realistic volatility") print(f"๐Ÿ›’ Active orders: {active_orders} buy/sell orders") print(f"โฐ Time span: Up to 180 days of historical data") # Show price ranges generated for each item print(f"\n๐Ÿ’ฐ Item Price Ranges Generated:") for item_id, base_price in generator.items_config.items(): cursor.execute(""" SELECT MIN(mi.price_each) as min_price, MAX(mi.price_each) as max_price, COUNT(*) as trade_count, AVG(mi.price_each) as avg_price FROM pk_market_inactive mi WHERE mi.item_id = %s AND mi.auction_id >= 2000 """, (item_id,)) result = cursor.fetchone() if result and result['trade_count'] > 0: print(f" Item {item_id:3d}: {result['min_price']:8,} - {result['max_price']:8,} GP " f"(avg: {result['avg_price']:8,.0f}, trades: {result['trade_count']:3d})") def generate_hourly_snapshots_multi_item(): """Generate hourly snapshots for all items""" print("\nโฐ Generating hourly snapshots for all items...") with get_db_connection() as conn: cursor = conn.cursor() # Clear existing hourly data for our items cursor.execute(""" DELETE FROM market_prices_hourly WHERE item_id IN (81,93,112,183,221,316,373,401,452,522,578,546,575,594,523,38) """) # Generate hourly snapshots current_time = datetime.now() snapshots_created = 0 # Every 2 hours for recent data, every 6 hours for older data recent_hours = list(range(0, 49, 2)) # Last 48 hours older_hours = list(range(54, 4321, 6)) # Older data all_hours = recent_hours + older_hours for hours_ago in all_hours: snapshot_time = current_time - timedelta(hours=hours_ago) snapshot_time = snapshot_time.replace(minute=0, second=0, microsecond=0) hour_start = snapshot_time - timedelta(hours=3) hour_end = snapshot_time + timedelta(hours=3) # Get all items that had trades in this window cursor.execute(""" SELECT mi.item_id, ms.amount, mi.price_each FROM pk_market_sales ms JOIN pk_market_inactive mi ON ms.auction_id = mi.auction_id WHERE mi.item_id IN (81,93,112,183,221,316,373,401,452,522,578,546,575,594,523,38) AND ms.time BETWEEN %s AND %s AND ms.sale_id >= 2000 """, ( int(hour_start.timestamp()), int(hour_end.timestamp()) )) sales_data = cursor.fetchall() # Group by item_id items_data = {} for sale in sales_data: item_id = sale['item_id'] if item_id not in items_data: items_data[item_id] = [] items_data[item_id].append({ 'price': sale['price_each'], 'volume': sale['amount'] }) # Create hourly snapshots for each item that had trades for item_id, trades in items_data.items(): prices = [trade['price'] for trade in trades] volumes = [trade['volume'] for trade in trades] min_price = min(prices) max_price = max(prices) avg_price = sum(p * v for p, v in zip(prices, volumes)) / sum(volumes) median_price = sorted(prices)[len(prices) // 2] total_volume = sum(volumes) trade_count = len(trades) cursor.execute(""" INSERT INTO market_prices_hourly (item_id, snapshot_time, min_price, max_price, avg_price, median_price, volume_traded, trade_count, active_buy_orders, active_sell_orders, buy_order_volume, sell_order_volume) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, 0, 0, 0, 0) """, ( item_id, snapshot_time, min_price, max_price, avg_price, median_price, total_volume, trade_count )) snapshots_created += 1 conn.commit() print(f"โœ… Generated {snapshots_created} hourly snapshots across all items") def generate_daily_aggregates_multi_item(): """Generate daily aggregates for all items""" print("\n๐Ÿ“… Generating daily aggregates for all items...") with get_db_connection() as conn: cursor = conn.cursor() # Clear existing daily data for our items cursor.execute(""" DELETE FROM market_prices_daily WHERE item_id IN (81,93,112,183,221,316,373,401,452,522,578,546,575,594,523,38) """) # Generate daily aggregates cursor.execute(""" INSERT INTO market_prices_daily (item_id, price_date, opening_price, closing_price, high_price, low_price, avg_price, volume_traded, trade_count, price_change_percent, volatility) SELECT item_id, DATE(snapshot_time) as price_date, AVG(avg_price) as opening_price, AVG(avg_price) as closing_price, MAX(max_price) as high_price, MIN(min_price) as low_price, AVG(avg_price) as avg_price, SUM(volume_traded) as volume_traded, SUM(trade_count) as trade_count, 0 as price_change_percent, COALESCE(STDDEV(avg_price) / NULLIF(AVG(avg_price), 0) * 100, 0) as volatility FROM market_prices_hourly WHERE item_id IN (81,93,112,183,221,316,373,401,452,522,578,546,575,594,523,38) GROUP BY item_id, DATE(snapshot_time) ORDER BY item_id, price_date """) conn.commit() # Check results cursor.execute(""" SELECT COUNT(*) as daily_count FROM market_prices_daily WHERE item_id IN (81,93,112,183,221,316,373,401,452,522,578,546,575,594,523,38) """) daily_count = cursor.fetchone()['daily_count'] print(f"โœ… Generated {daily_count} daily aggregates across all items") if __name__ == "__main__": print("๐Ÿš€ RSC Revolution Market Analytics - Multi-Item Mock Data Generator") print("=" * 70) try: # Generate all mock data generate_multi_item_mock_data() generate_hourly_snapshots_multi_item() generate_daily_aggregates_multi_item() print("\n๐ŸŽ‰ ALL MULTI-ITEM MOCK DATA GENERATED SUCCESSFULLY!") print("\n๐Ÿ“‹ What was created:") print(" โœ… 16 different items with realistic trading data") print(" โœ… Order-to-order price fluctuations (high volatility)") print(" โœ… Different volatility profiles based on item value") print(" โœ… Up to 180 days of historical data per item") print(" โœ… Hourly market snapshots with price ranges") print(" โœ… Daily market aggregates") print(" โœ… Active buy/sell orders for all items") print("\n๐Ÿงช Ready for testing:") print(" ๐Ÿ“Š Multi-item dashboard views") print(" ๐Ÿ“ˆ Item comparison charts") print(" ๐ŸŒŠ Volatility analysis across price ranges") print(" ๐Ÿ“‰ Price fluctuation analysis") print(" ๐Ÿ“Š Volume analysis by item tier") print("\n๐Ÿ’ก Next steps:") print(" 1. Run: python collectors/current_state.py") print(" 2. Run: python analyzers/run_analysis.py") print(" 3. Test your dashboard with 16 items of rich data!") except Exception as e: print(f"\nโŒ Error generating multi-item mock data: {e}") import traceback traceback.print_exc()