feat(core): implement wallet trade tracking and sqlite market caching

- Transitioned primary ESI networking and dataset initialization to `asyncio` and `aiohttp` for non-blocking I/O.
- Introduced `aiosqlite` utilizing an in-memory database to significantly speed up arbitrage querying.
- Implemented character wallet tracking (added `esi-wallet.read_character_wallet.v1` scope) to automatically deduct filled market volumes internally instead of waiting for full region polling.
- Exposed `/settings` endpoint allowing dynamic tweaking of threshold variables (cargo size, limits, tax rate, ship speeds) directly from the React UI.
- Decoupled auto-waypoint submission during route planning; users now push routes selectively via a new "Set in Game" frontend action and `/set_waypoints` backend logic.
- Optimized order evaluations and triplet generation limits through `LegMetrics` to lower CPU footprint drastically on large dataset passes.
- Migrated React frontend completely to Server-Sent Events (SSE), cleanly ditching HTTP polling.

1 files changed, 192 insertions, 103 deletions

diff --git a/backend/src/arbitrage.py b/backend/src/arbitrage.py
index c1a0459..2226a17 100644
--- a/backend/src/arbitrage.py
+++ b/backend/src/arbitrage.py
@@ -2,15 +2,22 @@ import logging
 from dataclasses import dataclass
 from typing import Optional, List
 
-from market import Order
+from market import Market
 
 logging.basicConfig(level=logging.INFO)
 logger = logging.getLogger(__name__)
 
-TAX_RATE = 0.08
-PROFIT_THRESHOLD = 100_000
-MAX_CAPITAL = 20_000_000
-SNIPE_PROFIT_THRESHOLD = 20_000_000
+ARBITRAGE_CANDIDATE_LIMIT = 30_000
+ARBITRAGE_TYPE_LIMIT = 250
+ARBITRAGE_STATION_PAIR_LIMIT = 40
+
+
+@dataclass(frozen=True)
+class ShortlistConfig:
+    candidate_limit: int = ARBITRAGE_CANDIDATE_LIMIT
+    type_limit: int = ARBITRAGE_TYPE_LIMIT
+    station_pair_limit: int = ARBITRAGE_STATION_PAIR_LIMIT
+    rank_mode: str = "and"
 
 @dataclass
 class Trade:
@@ -28,114 +35,196 @@ class Trade:
     gross_profit: float
     net_profit: Optional[float] = None
 
-def arbitrage(orders: List[Order], cargo_space: int) -> List[Trade]:
-    sell_orders = sorted(
-        (order for order in orders if not order.is_buy_order),
-        key=lambda x: x.price
-    )
-    buy_orders = sorted(
-        (order for order in orders if order.is_buy_order),
-        key=lambda x: x.price, reverse=True
-    )
-
-    logger.info("Initial sell orders: %d, buy orders: %d", len(sell_orders), len(buy_orders))
-
-    sell_orders = filter_orders_for_cargo(sell_orders, cargo_space)
-    buy_orders = filter_orders_for_cargo(buy_orders, cargo_space)
-    logger.info("After cargo filter - sell orders: %d, buy orders: %d", len(sell_orders), len(buy_orders))
+async def arbitrage(
+    market: Market,
+    cargo_space: int,
+    tax_rate: float,
+    profit_threshold: float,
+    max_capital: float,
+    shortlist: Optional[ShortlistConfig] = None,
+) -> List[Trade]:
+    if not market.db_conn:
+        return []
 
-    sell_orders = filter_orders_for_capital_risk(sell_orders, MAX_CAPITAL)
-    buy_orders = filter_orders_for_capital_risk(buy_orders, MAX_CAPITAL)
-    logger.info("After capital risk filter - sell orders: %d, buy orders: %d", len(sell_orders), len(buy_orders))
+    shortlist = shortlist or ShortlistConfig()
+    if shortlist.rank_mode not in {"and", "or"}:
+        raise ValueError(f"Unsupported shortlist rank mode: {shortlist.rank_mode}")
 
-    trades = create_trades(sell_orders, buy_orders)
-    return trades
+    rank_operator = "AND" if shortlist.rank_mode == "and" else "OR"
+    
+    query = f"""
+    WITH buy_orders AS (
+        SELECT * FROM orders WHERE is_buy_order = 1
+    ),
+    sell_orders AS (
+        SELECT * FROM orders WHERE is_buy_order = 0
+    ),
+    raw_trades AS (
+        SELECT 
+            s.location_id as from_station,
+            b.location_id as to_station,
+            s.order_id as from_order_id,
+            b.order_id as to_order_id,
+            s.item_name,
+            s.type_id,
+            s.item_cargo_volume,
+            s.price as from_price,
+            b.price as to_price,
+            MIN(
+                s.volume_remain, 
+                b.volume_remain,
+                CAST({cargo_space} / NULLIF(s.item_cargo_volume, 0) AS INTEGER),
+                CAST({max_capital} / NULLIF(s.price, 0) AS INTEGER),
+                CAST({max_capital} / NULLIF(b.price, 0) AS INTEGER)
+            ) as quantity
+        FROM sell_orders s
+        JOIN buy_orders b ON s.type_id = b.type_id
+        WHERE 
+            b.price * (1 - {tax_rate}) >= s.price
+            AND s.item_cargo_volume > 0
+            AND s.price > 0 AND b.price > 0
+    ),
+    candidate_trades AS (
+        SELECT
+            *,
+            quantity * (to_price * (1 - {tax_rate}) - from_price) as gross_profit,
+            quantity * item_cargo_volume as cargo,
+            (quantity * (to_price * (1 - {tax_rate}) - from_price)) / NULLIF(quantity * item_cargo_volume, 0) as profit_density,
+            (quantity * (to_price * (1 - {tax_rate}) - from_price)) / NULLIF(quantity * from_price, 0) as roi
+        FROM raw_trades
+        WHERE quantity > 0 AND quantity * (to_price * (1 - {tax_rate}) - from_price) >= {profit_threshold}
+    ),
+    ranked_trades AS (
+        SELECT
+            *,
+            ROW_NUMBER() OVER (
+                PARTITION BY type_id
+                ORDER BY gross_profit DESC, roi DESC, profit_density DESC
+            ) as type_rank,
+            ROW_NUMBER() OVER (
+                PARTITION BY from_station, to_station
+                ORDER BY gross_profit DESC, roi DESC, profit_density DESC
+            ) as station_pair_rank
+        FROM candidate_trades
+    )
+    SELECT
+        from_station,
+        to_station,
+        from_order_id,
+        to_order_id,
+        item_name,
+        type_id,
+        item_cargo_volume,
+        from_price,
+        to_price,
+        quantity,
+        cargo,
+        gross_profit
+    FROM ranked_trades
+        WHERE (type_rank <= {shortlist.type_limit}
+            {rank_operator} station_pair_rank <= {shortlist.station_pair_limit})
+    ORDER BY gross_profit DESC, roi DESC, profit_density DESC
+        LIMIT {shortlist.candidate_limit}
+    """
 
-def create_trades(sell_orders: List[Order], buy_orders: List[Order]) -> List[Trade]:
     trades = []
-    grouped_sell_orders = {}
-    grouped_buy_orders = {}
-
-    for order in sell_orders:
-        grouped_sell_orders.setdefault(order.type_id, []).append(order)
-    for order in buy_orders:
-        grouped_buy_orders.setdefault(order.type_id, []).append(order)
+    async with market.db_conn.execute(query) as cursor:
+        async for row in cursor:
+            from_station, to_station, from_order_id, to_order_id, item_name, type_id, item_cargo_volume, from_price, to_price, quantity, cargo_volume, gross_profit = row
+            
+            trades.append(Trade(
+                from_station=from_station,
+                to_station=to_station,
+                from_order_id=from_order_id,
+                to_order_id=to_order_id,
+                item_name=item_name,
+                type_id=type_id,
+                item_cargo_volume=item_cargo_volume,
+                from_price=from_price,
+                to_price=to_price,
+                quantity=quantity,
+                cargo=cargo_volume,
+                gross_profit=gross_profit
+            ))
 
-    for type_id in grouped_sell_orders.keys() & grouped_buy_orders.keys():
-        for sell_order in grouped_sell_orders[type_id]:
-            for buy_order in grouped_buy_orders[type_id]:
-                effective_buy_price = buy_order.price * (1 - TAX_RATE)
-                if effective_buy_price < sell_order.price:
-                    continue
-
-                quantity = min(sell_order.volume_remain, buy_order.volume_remain)
-                gross_profit = quantity * (effective_buy_price - sell_order.price)
+    logger.info(
+        "Generated %d arbitrage candidates after shortlist (limit=%d, type_limit=%d, station_pair_limit=%d, rank_mode=%s)",
+        len(trades),
+        shortlist.candidate_limit,
+        shortlist.type_limit,
+        shortlist.station_pair_limit,
+        shortlist.rank_mode,
+    )
+    return trades
 
-                if gross_profit < PROFIT_THRESHOLD:
-                    continue
+async def snipe(market: Market, cargo_space: int, current_region: int, tax_rate: float, max_capital: float, snipe_profit_threshold: float) -> Optional[Trade]:
+    if not market.db_conn:
+        return None
+        
+    query = f"""
+    WITH buy_orders AS (
+        SELECT * FROM orders WHERE is_buy_order = 1
+    ),
+    sell_orders AS (
+        SELECT * FROM orders WHERE is_buy_order = 0 AND region_id = {current_region}
+    )
+    SELECT 
+        s.location_id as from_station,
+        b.location_id as to_station,
+        s.order_id as from_order_id,
+        b.order_id as to_order_id,
+        s.item_name,
+        s.type_id,
+        s.item_cargo_volume,
+        s.price as from_price,
+        b.price as to_price,
+        MIN(
+            s.volume_remain, 
+            b.volume_remain,
+            CAST({cargo_space} / NULLIF(s.item_cargo_volume, 0) AS INTEGER),
+            CAST({max_capital} / NULLIF(s.price, 0) AS INTEGER),
+            CAST({max_capital} / NULLIF(b.price, 0) AS INTEGER)
+        ) as quantity
+    FROM sell_orders s
+    JOIN buy_orders b ON s.type_id = b.type_id
+    WHERE 
+        b.price * (1 - {tax_rate}) >= s.price
+        AND s.item_cargo_volume > 0
+        AND s.price > 0 AND b.price > 0
+    """
 
-                cargo_volume = quantity * sell_order.item_cargo_volume
+    best_trade = None
+    max_profit = 0
 
-                trade = Trade(
-                    from_station=sell_order.location_id,
-                    to_station=buy_order.location_id,
-                    from_order_id=sell_order.order_id,
-                    to_order_id=buy_order.order_id,
-                    item_name=sell_order.item_name,
-                    type_id=sell_order.type_id,
-                    item_cargo_volume=sell_order.item_cargo_volume,
-                    from_price=sell_order.price,
-                    to_price=buy_order.price,
+    async with market.db_conn.execute(query) as cursor:
+        async for row in cursor:
+            from_station, to_station, from_order_id, to_order_id, item_name, type_id, item_cargo_volume, from_price, to_price, quantity = row
+            
+            if quantity is None or quantity <= 0:
+                continue
+                
+            effective_buy_price = to_price * (1 - tax_rate)
+            gross_profit = quantity * (effective_buy_price - from_price)
+            
+            if gross_profit > max_profit:
+                cargo_volume = quantity * item_cargo_volume
+                
+                best_trade = Trade(
+                    from_station=from_station,
+                    to_station=to_station,
+                    from_order_id=from_order_id,
+                    to_order_id=to_order_id,
+                    item_name=item_name,
+                    type_id=type_id,
+                    item_cargo_volume=item_cargo_volume,
+                    from_price=from_price,
+                    to_price=to_price,
                     quantity=quantity,
                     cargo=cargo_volume,
                     gross_profit=gross_profit
                 )
-                trades.append(trade)
-    return trades
-
-def filter_orders_for_cargo(orders: List[Order], cargo_space: int) -> List[Order]:
-    filtered_orders = []
-    for order in orders:
-        item_volume = order.item_cargo_volume
-        if item_volume == 0:
-            continue
-        max_quantity = int(cargo_space // item_volume)
-        volume_remain = min(order.volume_remain, max_quantity)
-        if volume_remain > 0:
-            order = Order(**vars(order))
-            order.volume_remain = volume_remain
-            filtered_orders.append(order)
-    return filtered_orders
-
-def filter_orders_for_capital_risk(orders: List[Order], max_amount: int) -> List[Order]:
-    filtered_orders = []
-    for order in orders:
-        price = order.price
-        if price == 0:
-            continue
-        max_quantity = int(max_amount // price)
-        volume_remain = min(order.volume_remain, max_quantity)
-        if volume_remain > 0:
-            order = Order(**vars(order))
-            order.volume_remain = volume_remain
-            filtered_orders.append(order)
-    return filtered_orders
-
-def snipe(orders: List[Order], cargo_space: int, current_region: int) -> Optional[Trade]:
-    trades = arbitrage(orders, cargo_space)
+                max_profit = gross_profit
 
-    order_regions = {order.order_id: order.region_id for order in orders}
-
-    region_trades = [
-        trade for trade in trades
-        if order_regions.get(trade.from_order_id) == current_region
-    ]
-
-    region_trades.sort(key=lambda x: x.gross_profit, reverse=True)
-
-    logger.info("Reviewing potential snipes for region %d: %d trades found", current_region, len(region_trades))
-
-    if region_trades and region_trades[0].gross_profit > SNIPE_PROFIT_THRESHOLD:
-        return region_trades[0]
-    else:
-        return None
+    if max_profit > snipe_profit_threshold and best_trade:
+        return best_trade
+    return None