XFEEDToolBox

"""Read-only candidate comparison; cached supply prices are not live order approval."""
import argparse
import datetime
import json
import math
import sqlite3
from pathlib import Path

parser = argparse.ArgumentParser(description=__doc__)
parser.add_argument('--database', type=Path, required=True)
parser.add_argument('--homer-market', type=Path, required=True)
parser.add_argument('--output', type=Path, required=True)
args = parser.parse_args()
database = args.database.resolve()
connection = sqlite3.connect(database.as_uri() + '?mode=ro', uri=True, timeout=10)
connection.execute('PRAGMA query_only=ON')
connection.row_factory = sqlite3.Row
target = connection.execute('SELECT x,y,z FROM stations WHERE id=4210924803').fetchone()
if target is None or any(value is None for value in target):
    raise ValueError('Homer coordinates unavailable')
prices = {row['name'].replace(' ', '').lower(): row['sellPrice']
          for row in json.loads(args.homer_market.read_text(encoding='utf-8-sig'))['commodities']}
now = datetime.datetime.now(datetime.timezone.utc)
candidates = []
for row in connection.execute('''SELECT q.*,s.name stationName,s.system,s.x,s.y,s.z,s.json stationJson
    FROM quotes q JOIN stations s ON s.id=q.station
    WHERE q.commodity IN ('uranium','coffee','liquidoxygen','polymers')
    AND q.buy>0 AND q.supply>=192 AND s.x IS NOT NULL AND s.y IS NOT NULL AND s.z IS NOT NULL'''):
    distance = math.dist(target, (row['x'], row['y'], row['z']))
    if distance > 60:
        continue
    metadata = json.loads(row['stationJson'])
    key = row['commodity'].replace(' ', '').lower()
    profit = prices[key] - row['buy']
    if profit <= 0:
        continue
    observed = datetime.datetime.fromtimestamp(row['timestamp'] / 1000, datetime.timezone.utc)
    candidates.append({'marketId': row['station'], 'station': row['stationName'], 'system': row['system'],
        'commodity': key, 'cachedBuy': row['buy'], 'actualDestinationSell': prices[key], 'supplySnapshot': row['supply'],
        'distanceLy': round(distance, 3), 'ageMinutes': round((now-observed).total_seconds()/60, 1),
        'collectedAt': observed.isoformat(), 'purchaseCost192': row['buy']*192,
        'grossProfit192': profit*192, 'metadata': metadata})
connection.close()
candidates.sort(key=lambda row: row['grossProfit192'], reverse=True)
eligible = [row for row in candidates if row['marketId']<=4294967295 and row['ageMinutes']<=360
    and not row['metadata'].get('isFleetCarrier') and not row['metadata'].get('isPlanetary')
    and row['metadata'].get('maxLandingPad') in ('Medium','Large')
    and row['metadata'].get('security') in ('High','Medium')
    and not row['metadata'].get('requiresPermit') and not row['metadata'].get('isRestricted')]
eligible.sort(key=lambda row: (row['distanceLy'], -row['grossProfit192']))
args.output.parent.mkdir(parents=True, exist_ok=True)
args.output.write_text(json.dumps({'asOf': now.isoformat(), 'capacity': 192, 'wallet': 25390959,
    'sourcePricesRequireOriginalDockedRecheck': True, 'destinationPricesAlreadyIncludeMultiplier': True,
    'requiredPad':'Medium','candidates': candidates[:512], 'eligibleCandidates':eligible[:80]}, ensure_ascii=False, indent=2), encoding='utf-8')
print(json.dumps({'countWithin60Ly': len(candidates), 'eligibleCount':len(eligible), 'nearestEligible': [
    {key: row[key] for key in row if key != 'metadata'} for row in eligible[:8]]}, ensure_ascii=False))