app.py

# -*- coding: utf-8 -*-
import asyncio
from pymongo import MongoClient
import aiohttp
import pandas as pd
import requests
import json
import urllib.parse
import gradio as gr
import datetime
import os


wallets = [
    "J7QAjhEGTAx71RoS1Nnuz4aKqK866xEnRXky4zhPa2WG",
    "9huTEYifjBMJVhPWG3dX84S1Pr6pgXBH9i7274dpPeMr",
    "CoA4vLyykjYobxEsDWHEx2Rna9WribGQMxSxVkH5NwN6",
    "BpaD3QF9Z2YtUqPRxpTn7dgC2sBzpzxro5zrEdwhwNd9",
    "FWmhs5vdUSMHUE5sHPQmd1hBEQVWD8wyCPe9op3KwTNg",
    "HW1qacccywvtEmUK6mDWfaJfxcM1R64T8BmhfjmxA6Nx",
    "3QpWHc77Vze5uiqm6WDfxYawKUj1bVbKEegS3YYBLGvL",
    "5FyNV778E1SZ4ZBwUXCgbx43zrJFdMy4UgUysSw3yocU",
    "4L3wPZc8smLYQbnpMSxHCiRB5cojbtuc33NkTNsrtdug",
    "MruHB1owAkBQwtSkzTqTDpqZEDPmz4iUWz3oHCYHKWV"
]

#SCRAPE_DO_TOKEN = os.getenv('SCRAPE_DO_KEY')
#client = MongoClient(os.getenv('MONGO_DB_URI'))


SCRAPE_DO_TOKEN = "c3cb4da35304433483052fb6ba0c7011fef2ff42d62"
client = MongoClient("mongodb+srv://djamaal:FHmV2N733lzrLkWf@test-cluster.mys4n.mongodb.net/")

db = client["billy_balances"]
token_collection = db["turnstile-tokens"]

turnstile_token = token_collection.find_one(sort=[("timestamp", -1)])['x-turnstile-token']

common_headers = {
    "accept": "application/json",
    "accept-encoding": "identity",
    "accept-language": "en-GB,en-US;q=0.9,en;q=0.8",
    "authorization": "Bearer CGtF4EdvDbBpwUXmZSKW3HsYkajy7e",
    "content-type": "application/json",
    "origin": "https://portfolio.jup.ag",
    "referer": "https://portfolio.jup.ag/",
    "user-agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/136.0.0.0 Safari/537.36",
    "x-turnstile-token": turnstile_token  # keep this updated
}

async def fetch_portfolio(session, wallet_address, results):
    base_url = f"https://portfolio-api-jup.sonar.watch/v1/portfolio/fetch?address={wallet_address}&addressSystem=solana"
    encoded_url = urllib.parse.quote(base_url)
    proxy_url = f"http://api.scrape.do/?token={SCRAPE_DO_TOKEN}&url={encoded_url}&forwardHeaders=True&super=True"

    try:
        async with session.get(proxy_url, headers=common_headers) as resp:
            text = await resp.json()
            if resp.status == 200:
                print(f"✅ Wallet {wallet_address[:6]}...: success")
                results.append({"wallet": wallet_address,"data": text})
            else:
                print(f"❌ Wallet {wallet_address[:6]}...: HTTP {resp.status}")
                results.append({"wallet": wallet_address, "data": None})
    except Exception as e:
        print(f"⚠️ Error fetching {wallet_address}: {e}")
        results.append({"wallet": wallet_address, "status": "error", "error": str(e)})

async def get_data(wallet_addresses):
    results = []
    async with aiohttp.ClientSession() as session:
        tasks = [fetch_portfolio(session, address, results) for address in wallet_addresses]
        await asyncio.gather(*tasks)
    return results
#Have a button function which does all this

async def process_data():
  portfolios = await get_data(wallets)
  df = pd.DataFrame(portfolios)
  token_rows = []

  for index, row in df.iterrows():
      data = row['data']

      # Token info mapping
      token_info = data.get('tokenInfo', {}).get('solana', {})

      # Navigate to assets list
      elements = data.get('elements', [])
      for element in elements:
          if element.get('type') == 'multiple':
              assets = element.get('data', {}).get('assets', [])
              for asset in assets:
                  asset_data = asset.get('data', {})
                  address = asset_data.get('address')
                  amount = asset_data.get('amount')
                  value = asset.get('value')

                  # Get symbol using address
                  symbol = token_info.get(address, {}).get('symbol', 'UNKNOWN')

                  token_rows.append({
                      'original_row': index,
                      'address': address,
                      'symbol': symbol,
                      'amount': amount,
                      'value': value
                  })

  # Final DataFrame
  tokens_df = pd.DataFrame(token_rows)
  tokens_df['original_row'] = tokens_df['original_row'].apply(lambda x: df.loc[x, 'wallet'])
  tokens_df = tokens_df.dropna()

  # Pivot for values
  value_pivot = tokens_df.pivot(index='original_row', columns='symbol', values='value')
  value_pivot.columns = [f'{col}_value' for col in value_pivot.columns]

  # Pivot for amounts
  amount_pivot = tokens_df.pivot(index='original_row', columns='symbol', values='amount')
  amount_pivot.columns = [f'{col}_amount' for col in amount_pivot.columns]

  # Combine both
  final_df = pd.concat([value_pivot, amount_pivot], axis=1).reset_index()

  # Optional: Fill NaNs with 0
  final_df = final_df.fillna(0)

  rows = []
  for idx, row in df.iterrows():
      data = row['data']
      original_row = row['wallet']  # or whatever uniquely identifies the row

      for element in data.get("elements", []):
          if element.get("platformId") == "jupiter-exchange":
              input_token = element.get("data", {}).get("assets", {}).get("input", {})
              if input_token:
                  token_data = input_token.get("data", {})
                  address = token_data.get("address")
                  value = input_token.get("value")
                  amount = token_data.get("amount")
                  symbol = token_info.get(address, {}).get("symbol", address)

                  rows.append({
                      "original_row": original_row,
                      "address": address,
                      "symbol": symbol,
                      "amount": amount,
                      "value": value
                  })

  # Convert to DataFrame
  jup_tokens_df = pd.DataFrame(rows)

  jup_tokens_df['symbol'] = jup_tokens_df['symbol'].apply(lambda x: "SOL" if x == "So11111111111111111111111111111111111111112" else x)

  # Pivot
  # Group by original_row and symbol, sum value and amount
  tokens_grouped = jup_tokens_df.groupby(['original_row', 'symbol']).agg({
      'value': 'sum',
      'amount': 'sum'
  }).reset_index()

  # Pivot
  value_pivot = tokens_grouped.pivot(index='original_row', columns='symbol', values='value')
  value_pivot.columns = [f'{col}_value' for col in value_pivot.columns]

  amount_pivot = tokens_grouped.pivot(index='original_row', columns='symbol', values='amount')
  amount_pivot.columns = [f'{col}_amount' for col in amount_pivot.columns]

  jup_df = pd.concat([value_pivot, amount_pivot], axis=1).reset_index().fillna(0)

  if "9Rhbn9G5poLvgnFzuYBtJgbzmiipNra35QpnUek9virt_value" in jup_df.columns:
    jup_df = jup_df.rename(columns={"9Rhbn9G5poLvgnFzuYBtJgbzmiipNra35QpnUek9virt_value":"BILLY_value"})
  if "9Rhbn9G5poLvgnFzuYBtJgbzmiipNra35QpnUek9virt_amount" in jup_df.columns:
    jup_df = jup_df.rename(columns={"9Rhbn9G5poLvgnFzuYBtJgbzmiipNra35QpnUek9virt_amount":"BILLY_amount"})

  final_df1 = final_df.set_index('original_row')
  final_df2 = jup_df.set_index('original_row')

  # Combine the two DataFrames, adding values where they overlap
  combined_df = final_df1.add(final_df2, fill_value=0)

  # Reset index if you want original_row back as a column
  combined_df = combined_df.reset_index()

  # Optional: fill NaNs (if any) with 0 just in case
  combined_df = combined_df.fillna(0)

  # Sum all numeric columns except the original_row which is non-numeric
  totals = combined_df.select_dtypes(include='number').sum()

  # Add a row with these totals at the bottom of the dataframe
  totals_row = pd.DataFrame(totals).T
  totals_row['original_row'] = 'Total'

  # Append the totals row to the original df
  df_with_totals = pd.concat([combined_df, totals_row], ignore_index=True)

  totals = final_df.select_dtypes(include='number').sum()

  # Add a row with these totals at the bottom of the dataframe
  totals_row = pd.DataFrame(totals).T
  totals_row['original_row'] = 'Total'

  # Append the totals row to the original df
  final_df = pd.concat([final_df, totals_row], ignore_index=True)

  totals = jup_df.select_dtypes(include='number').sum()

  # Add a row with these totals at the bottom of the dataframe
  totals_row = pd.DataFrame(totals).T
  totals_row['original_row'] = 'Total'

  # Append the totals row to the original df
  jup_df = pd.concat([jup_df, totals_row], ignore_index=True)

  final_df = final_df.rename(columns={'original_row': ' '})
  jup_df = jup_df.rename(columns={'original_row': ' '})
  df_with_totals = df_with_totals.rename(columns={'original_row': ' '})

  sol_price = 170
  billy_price = 0.001845

  # Starting balances
  starting_balances = {
      " ": ["Total"],
      "SOL_amount": [344],
      "SOL_value": [344 * sol_price],
      "BILLY_amount": [75_000_000],
      "BILLY_value": [75_000_000 * billy_price]
  }

  # Create the DataFrame
  starting_df = pd.DataFrame(starting_balances)

  return(starting_df,final_df.tail(1),jup_df.tail(1),df_with_totals.tail(1))



collection = db["balance_data"]

def df_to_mongo_safe_dict(df):
    return {str(k): v for k, v in df.to_dict(orient="index").items()}


async def display_data():
    df0, df1, df2, df3 = await process_data()

    def summarize(df):
        value_cols = [col for col in df.columns if "value" in col.lower()]
        total_value = df[value_cols].sum(axis=1).values[0] if value_cols else 0
        return df, total_value

    df0, total0 = summarize(df0)
    df1, total1 = summarize(df1)
    df2, total2 = summarize(df2)
    df3, total3 = summarize(df3)

    pnl = float(total3) - float(total0)

    # Save to MongoDB
    record = {
    "timestamp": datetime.datetime.utcnow(),
    "df0": df_to_mongo_safe_dict(df0),
    "total0": float(total0),
    "df1": df_to_mongo_safe_dict(df1),
    "total1": float(total1),
    "df2": df_to_mongo_safe_dict(df2),
    "total2": float(total2),
    "df3": df_to_mongo_safe_dict(df3),
    "total3": float(total3),
    "pnl": float(pnl)
    }
    collection.insert_one(record)

    return df0, total0, df1, total1, df2, total2, df3, total3, f"PNL : {pnl:,.2f}"

def load_from_mongo():
    latest = collection.find_one(sort=[("timestamp", -1)])
    if latest:
        df0 = pd.DataFrame(latest["df0"]).T
        df1 = pd.DataFrame(latest["df1"]).T
        df2 = pd.DataFrame(latest["df2"]).T
        df3 = pd.DataFrame(latest["df3"]).T

        return (
            df0, latest["total0"],
            df1, latest["total1"],
            df2, latest["total2"],
            df3, latest["total3"],
            f"PNL : {latest['pnl']:,.2f}"
        )
    else:
        empty_df = pd.DataFrame()
        return empty_df, 0, empty_df, 0, empty_df, 0, empty_df, 0, "PNL : 0.00"

with gr.Blocks() as demo:
    gr.Markdown("## BILLY Balances and PnL")

    df0_out = gr.Dataframe(label="Starting Balances")
    txt0_out = gr.Textbox(label="Starting Value Total")

    df1_out = gr.Dataframe(label="Wallet Balances")
    txt1_out = gr.Textbox(label="Wallet Value Total")

    df2_out = gr.Dataframe(label="JUP Limit Balances")
    txt2_out = gr.Textbox(label="JUP Limit Value Total")

    df3_out = gr.Dataframe(label="Total Balances")
    txt3_out = gr.Textbox(label="Current Value Total")

    pnl_out = gr.Textbox(label="PNL (AUM Model)")

    # Load from MongoDB on app load (sync function)
    demo.load(
        fn=load_from_mongo,
        inputs=[],
        outputs=[
            df0_out, txt0_out,
            df1_out, txt1_out,
            df2_out, txt2_out,
            df3_out, txt3_out,
            pnl_out
        ]
    )

demo.launch(debug=True, share=True)