"""v3pull.py (FW Analyst 1, 6 Oct 2026): first 2 h of every v3 WETH pool created 13 Sep..6 Oct: Swap logs of the pool and Transfer logs
of the token, batched getLogs on the paid dRPC key (read in code, never printed). Writes /root/fwa1/v3/v3a1.db. Resumable.
Rate: one batch of <=40 getLogs at a time, small sleep, to stay well under the shared 50 req/s cap."""
import json, sqlite3, time, urllib.request, sys
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
T_SWAP = "0xc42079f94a6350d7e6235f29174924f928cc2ac818eb64fed8004e115fbcca67"
T_TRANSFER = "0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef"
WIN = int(2 * 3600 / 0.101283)
import os; os.makedirs("/root/fwa1/v3", exist_ok=True)
D = sqlite3.connect("/root/fwa1/v3/v3a1.db", timeout=120); D.execute("PRAGMA journal_mode=WAL")
D.execute("CREATE TABLE IF NOT EXISTS done(pool TEXT PRIMARY KEY, nsw INT, ntr INT, err TEXT)")
D.execute("CREATE TABLE IF NOT EXISTS sw(pool TEXT, b INT, li INT, tx TEXT, snd TEXT, rcp TEXT, a0 TEXT, a1 TEXT, sq TEXT, PRIMARY KEY(pool, b, li))")
D.execute("CREATE TABLE IF NOT EXISTS tr(token TEXT, b INT, li INT, tx TEXT, frm TEXT, too TEXT, v TEXT, PRIMARY KEY(token, b, li))")
V = sqlite3.connect("file:/opt/sw/v3live.db?mode=ro", uri=True)
WK, NW = (int(sys.argv[1]), int(sys.argv[2])) if len(sys.argv) > 2 else (0, 1)
pools = V.execute("SELECT pool, token, created_b FROM pools WHERE venue='v3' AND created_b >= 61265282 ORDER BY created_b").fetchall()
done = {r[0] for r in D.execute("SELECT pool FROM done")}
todo = [p for k, p in enumerate(pools) if p[0] not in done and k % NW == WK]
print("pools", len(pools), "todo", len(todo), flush=True)
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=120))
        except Exception as e:
            err = e; time.sleep(2 * (i + 1))
    raise err
B = 20
t0 = time.time()
for i in range(0, len(todo), B):
    grp = todo[i:i + B]; body = []
    for j, (pool, tok, cb) in enumerate(grp):
        body.append({"jsonrpc": "2.0", "id": 2 * j, "method": "eth_getLogs", "params": [{"address": pool, "topics": [T_SWAP], "fromBlock": hex(cb), "toBlock": hex(cb + WIN)}]})
        body.append({"jsonrpc": "2.0", "id": 2 * j + 1, "method": "eth_getLogs", "params": [{"address": tok, "topics": [T_TRANSFER], "fromBlock": hex(cb), "toBlock": hex(cb + WIN)}]})
    r = post(body); r = {x["id"]: x for x in r} if isinstance(r, list) else {}
    for j, (pool, tok, cb) in enumerate(grp):
        a, b = r.get(2 * j, {}), r.get(2 * j + 1, {})
        if "result" not in a or "result" not in b:
            D.execute("INSERT OR REPLACE INTO done VALUES(?,?,?,?)", (pool, -1, -1, json.dumps(a.get("error") or b.get("error"))[:300])); continue
        D.executemany("INSERT OR IGNORE INTO sw VALUES(?,?,?,?,?,?,?,?,?)", [(pool, int(l["blockNumber"], 16), int(l["logIndex"], 16), l["transactionHash"],
            "0x" + l["topics"][1][26:], "0x" + l["topics"][2][26:], l["data"][2:66], l["data"][66:130], l["data"][130:194]) for l in a["result"]])
        D.executemany("INSERT OR IGNORE INTO tr VALUES(?,?,?,?,?,?,?)", [(tok, int(l["blockNumber"], 16), int(l["logIndex"], 16), l["transactionHash"],
            "0x" + l["topics"][1][26:], "0x" + l["topics"][2][26:], l["data"][2:66]) for l in b["result"] if len(l["topics"]) == 3])
        D.execute("INSERT OR REPLACE INTO done VALUES(?,?,?,NULL)", (pool, len(a["result"]), len(b["result"])))
    D.commit()
    if (i // B) % 10 == 0: print(i + len(grp), "pools", round(time.time() - t0), "s", flush=True)
    time.sleep(0.3)
print("done", round(time.time() - t0), "s")