import json, sqlite3, time, urllib.request
K = json.load(open("/opt/common/drpc_paid.json"))["robinhood"]
U = K if K.startswith("http") else "https://lb.drpc.org/ogrpc?network=robinhood&dkey=" + K
V = sqlite3.connect("file:/opt/sw/v3live.db?mode=ro", uri=True)
D = sqlite3.connect("/root/fwa1/v3/v3a1.db", timeout=120)
D.execute("CREATE TABLE IF NOT EXISTS meta(pool TEXT PRIMARY KEY, token TEXT, is0 INT, fee INT, cb INT, creator_tx TEXT, supply REAL, dec INT)")
pools = V.execute("SELECT pool, token, token_is0, fee, created_b, created_tx FROM pools WHERE venue='v3' AND created_b >= 61265282").fetchall()
have = {r[0] for r in D.execute("SELECT pool FROM meta")}
pools = [p for p in pools if p[0] not in have]
def post(body):
    for i in range(6):
        try:
            rq = urllib.request.Request(U, json.dumps(body).encode(), {"Content-Type": "application/json", "User-Agent": "Mozilla/5.0"})
            return json.load(urllib.request.urlopen(rq, timeout=60))
        except Exception as e:
            err = e; time.sleep(2 * (i + 1))
    raise err
for i in range(0, len(pools), 50):
    g = pools[i:i + 50]; body = []
    for j, p in enumerate(g):
        body.append({"jsonrpc": "2.0", "id": 2 * j, "method": "eth_call", "params": [{"to": p[1], "data": "0x18160ddd"}, "latest"]})
        body.append({"jsonrpc": "2.0", "id": 2 * j + 1, "method": "eth_call", "params": [{"to": p[1], "data": "0x313ce567"}, "latest"]})
    r = {x["id"]: x.get("result") for x in post(body)}
    rows = []
    for j, p in enumerate(g):
        try: dec = int(r.get(2 * j + 1) or "0x12", 16)
        except Exception: dec = 18
        try: sup = int(r.get(2 * j) or "0x0", 16) / 10 ** dec
        except Exception: sup = None
        rows.append((p[0], p[1], p[2], p[3], p[4], p[5], sup, dec))
    D.executemany("INSERT OR REPLACE INTO meta VALUES(?,?,?,?,?,?,?,?)", rows); D.commit(); time.sleep(0.2)
print("meta", D.execute("select count(*), sum(supply>0) from meta").fetchone())
# ETH/USD daily (CoinGecko, free) for 12 Sep..7 Oct
t0 = 1789171200; t1 = 1791331200
r = json.loads(urllib.request.urlopen(urllib.request.Request("https://api.coingecko.com/api/v3/coins/ethereum/market_chart/range?vs_currency=usd&from=%d&to=%d" % (t0, t1), headers={"User-Agent": "Mozilla/5.0"}), timeout=30).read())
json.dump(r["prices"], open("/root/fwa1/v3/ethusd.json", "w")); print("ethusd points", len(r["prices"]))