#!/usr/bin/env python3
"""
Build 3D graph data for visualization - Simplified version for actual schema.
"""

import json
import sqlite3
from pathlib import Path
from datetime import datetime
from collections import defaultdict


class OrderFlowGraphBuilder3D:
    """Build 3D graph for order flow visualization."""

    def __init__(self, db_path: Path, output_path: Path):
        self.db_path = db_path
        self.output_path = output_path

    def build_graph(self, asset: str = "xautusdt"):
        """Build 3D graph from order book data."""

        print("="*80)
        print(f"3D GRAPH BUILDER - {asset.upper()}")
        print("="*80)

        conn = sqlite3.connect(self.db_path)
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()

        # Get all order book levels and aggregate by price
        cursor.execute("""
            SELECT
                price,
                side,
                SUM(volume) as total_volume,
                COUNT(*) as level_count
            FROM order_book_levels
            WHERE snapshot_id IN (
                SELECT id FROM order_book_snapshots
                WHERE symbol = ?
                ORDER BY timestamp DESC
                LIMIT 10
            )
            GROUP BY price, side
            ORDER BY price DESC
        """, (asset,))

        rows = cursor.fetchall()

        if not rows:
            print(f"\n❌ No price level data found for {asset}")
            conn.close()
            return None

        # Aggregate bid and ask volumes for each price
        price_data = defaultdict(lambda: {'bid_volume': 0, 'ask_volume': 0, 'bid_count': 0, 'ask_count': 0})

        for row in rows:
            price = row['price']
            side = row['side']
            volume = row['total_volume']
            count = row['level_count']

            if side == 'bid':
                price_data[price]['bid_volume'] += volume
                price_data[price]['bid_count'] += count
            elif side == 'ask':
                price_data[price]['ask_volume'] += volume
                price_data[price]['ask_count'] += count

        print(f"\n📊 Processing {len(price_data)} price levels...")

        entities = []
        relationships = []
        entity_map = {}

        for price, data in sorted(price_data.items(), reverse=True):
            bid_vol = data['bid_volume']
            ask_vol = data['ask_volume']
            bid_count = data['bid_count']
            ask_count = data['ask_count']

            # Calculate metrics
            total_vol = bid_vol + ask_vol
            delta = bid_vol - ask_vol
            imbalance = ask_vol > 0 and bid_vol / ask_vol or 1.0

            # Wall strength (volume concentration)
            wall_strength = total_vol / max(1, bid_count + ask_count)

            entity_id = f"price_level_{price}"

            entity = {
                "id": entity_id,
                "type": "PriceLevel",
                "name": f"Price_{price:.2f}",
                "properties": {
                    "price": price,
                    "bid_volume": bid_vol,
                    "ask_volume": ask_vol,
                    "total_volume": total_vol,
                    "delta": delta,
                    "imbalance": imbalance,
                    "wall_strength": wall_strength,
                    "bid_count": bid_count,
                    "ask_count": ask_count
                }
            }

            entities.append(entity)
            entity_map[price] = entity_id

        # Create relationships between nearby price levels
        print("\n🔗 Creating relationships...")

        prices_sorted = sorted(price_data.keys(), reverse=True)

        for i in range(len(prices_sorted) - 1):
            current_price = prices_sorted[i]
            next_price = prices_sorted[i + 1]

            # Calculate price distance
            price_diff = abs(current_price - next_price)

            # Only connect nearby levels
            if price_diff < 10:  # Within $10
                relationship = {
                    "from": entity_map[current_price],
                    "to": entity_map[next_price],
                    "type": "adjacent_price_level",
                    "properties": {
                        "price_distance": price_diff
                    }
                }
                relationships.append(relationship)

        # Load trading signals and add as entities
        print("\n🎯 Loading trading signals...")

        signals_file = Path(f"/home/ubuntu/.hermes/workspace/projects/ORDER_FLOW_GRAPH/outputs/data/signals_{asset}.json")

        if signals_file.exists():
            with open(signals_file) as f:
                signals_data = json.load(f)

            # Add top 10 signals as entities
            for signal in signals_data['signals'][:10]:
                signal_id = f"signal_{signal['entry_price']}_{signal.get('type', 'unknown')}"

                signal_entity = {
                    "id": signal_id,
                    "type": "TradingSignal",
                    "name": f"{signal.get('type', 'UNKNOWN').upper()}_{signal['entry_price']}",
                    "properties": {
                        "entry_price": signal['entry_price'],
                        "target_price": signal['target_price'],
                        "stop_price": signal['stop_price'],
                        "direction": signal.get('direction', 'neutral'),
                        "confidence": signal.get('confidence', 0.5),
                        "risk_reward": signal.get('risk_reward', 0),
                        "signal_type": signal.get('type', 'unknown'),
                        "reason": signal.get('reason', '')
                    }
                }

                entities.append(signal_entity)

                # Connect signal to nearest price level
                nearest_price = min(
                    entity_map.keys(),
                    key=lambda p: abs(p - signal['entry_price'])
                )

                relationship = {
                    "from": signal_id,
                    "to": entity_map[nearest_price],
                    "type": "signal_at_price_level",
                    "properties": {
                        "distance": abs(nearest_price - signal['entry_price'])
                    }
                }
                relationships.append(relationship)

        # Build graph structure
        graph = {
            "metadata": {
                "created_at": datetime.now().isoformat(),
                "version": "1.0.0",
                "asset": asset,
                "entity_count": len(entities),
                "relationship_count": len(relationships),
                "data_source": "Binance Order Book + Trading Signals"
            },
            "entities": entities,
            "relationships": relationships
        }

        # Save graph
        with open(self.output_path, 'w') as f:
            json.dump(graph, f, indent=2)

        print(f"\n✅ Graph saved to: {self.output_path}")
        print(f"   Entities: {len(entities)}")
        print(f"   Relationships: {len(relationships)}")

        conn.close()

        return graph


def main():
    """Build 3D graphs for all assets."""

    # Use uppercase symbols
    assets = ['XAUTUSDT', 'BTCUSDT', 'ETHUSDT']
    db_path = Path("/home/ubuntu/.hermes/workspace/projects/ORDER_FLOW_GRAPH/data/binance_multi_asset.db")

    for asset in assets:
        # Use lowercase for filename
        asset_lower = asset.lower()
        output_path = Path(f"/home/ubuntu/.hermes/workspace/projects/ORDER_FLOW_GRAPH/data/order_flow_graph_3d_{asset_lower}.json")

        builder = OrderFlowGraphBuilder3D(db_path, output_path)
        graph = builder.build_graph(asset)

        if graph:
            print(f"\n{'='*80}")
            print(f"✅ {asset} 3D graph built successfully!")
            print(f"{'='*80}\n")


if __name__ == "__main__":
    main()