#!/usr/bin/env python3
"""
Extract latest order book data from database and save as JSON for the 3D city.
"""

import sqlite3
import json
from pathlib import Path
from datetime import datetime

DATABASE = Path('/home/ubuntu/.hermes/workspace/projects/ORDER_FLOW_GRAPH/data/binance_multi_asset.db')
OUTPUT_DIR = Path('/home/ubuntu/.hermes/workspace/projects/ORDER_FLOW_GRAPH/outputs/data')

def get_latest_order_book(symbol):
    """Get the latest order book snapshot from the database."""
    try:
        conn = sqlite3.connect(DATABASE, timeout=5.0)
        conn.text_factory = str
        cursor = conn.cursor()
        
        # Get the latest snapshot ID for this symbol
        cursor.execute("""
            SELECT id, timestamp
            FROM order_book_snapshots
            WHERE symbol = ?
            ORDER BY id DESC
            LIMIT 1
        """, (symbol.upper(),))
        
        snapshot = cursor.fetchone()
        if not snapshot:
            print(f"⚠️  No snapshot found for {symbol}")
            return None
        
        snapshot_id, timestamp = snapshot
        
        # Get all order book levels for this snapshot
        cursor.execute("""
            SELECT price, volume, side
            FROM order_book_levels
            WHERE snapshot_id = ?
            ORDER BY price
        """, (snapshot_id,))
        
        levels = cursor.fetchall()
        conn.close()
        
        # Organize into bids and asks
        bids = {}
        asks = {}
        
        for price, volume, side in levels:
            price_key = f"{price:.2f}"
            if side == 'bid':
                bids[price_key] = volume
            elif side == 'ask':
                asks[price_key] = volume
        
        print(f"✅ {symbol}: {len(bids)} bids, {len(asks)} asks")
        
        return {
            'symbol': symbol.upper(),
            'timestamp': timestamp,
            'bids': bids,
            'asks': asks,
            'snapshot_id': snapshot_id
        }
        
    except Exception as e:
        print(f"❌ Error fetching order book for {symbol}: {e}")
        return None

def main():
    """Extract and save order book data for all assets."""
    symbols = ['XAUTUSDT', 'BTCUSDT', 'ETHUSDT']
    
    for symbol in symbols:
        data = get_latest_order_book(symbol)
        
        if data:
            output_file = OUTPUT_DIR / f'orderbook_{symbol.lower()}.json'
            
            with open(output_file, 'w') as f:
                json.dump(data, f, indent=2)
            
            print(f"💾 Saved to {output_file.name}")
        
        # Calculate totals if data exists
        if data and data['bids']:
            total_bid_vol = sum(data['bids'].values())
            print(f"   Total bid volume: {total_bid_vol:.2f}")
        
        if data and data['asks']:
            total_ask_vol = sum(data['asks'].values())
            print(f"   Total ask volume: {total_ask_vol:.2f}")

if __name__ == '__main__':
    print("📊 Extracting real-time order book data...")
    print(f"🗄️  Database: {DATABASE}")
    print()
    main()
    print()
    print("✅ Done! Order book data ready for 3D city.")