"""WETH pond list: v4 fee 100 / spacing 1 / no hook / WETH quote, launched via PositionManager. Writes /opt/fw2/wpond_pools.txt
and survival: share pulled before first trade + 4 s + 15 min.  usage: wpond.py 2026-09-20 2026-10-05"""
import sqlite3, glob, os, sys, json, time, urllib.request, collections
WETH = "0x0bd7d308f8e1639fab988df18a8011f41eacad73"; ZERO = "0x" + "0" * 40; PM = "0x58daec3116aae6d93017baaea7749052e8a04fa7"
_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
SPB = 0.101283
d0, d1 = sys.argv[1], sys.argv[2]
L = sqlite3.connect("file:/opt/pricepredict-data/liqlock.db?mode=ro", uri=True)
def pull_b(pool):
    add = 0.0
    for b, dl in L.execute("SELECT b, CAST(dl AS REAL) FROM ml WHERE pool=? ORDER BY b, li", (pool,)):
        if dl > 0: add += dl
        elif dl < 0 and add > 0 and -dl >= 0.5 * add: return b
    return None
def batch(txs):
    out = {}
    for i in range(0, len(txs), 50):
        body = [{"jsonrpc": "2.0", "id": j, "method": "eth_getTransactionByHash", "params": [h]} for j, h in enumerate(txs[i:i + 50])]
        for k in range(6):
            try:
                q = urllib.request.Request(U, json.dumps(body).encode(), {"content-type": "application/json", "User-Agent": "Mozilla/5.0"})
                r = json.load(urllib.request.urlopen(q, timeout=60)); break
            except Exception: time.sleep(3 + 3 * k); r = []
        for x in r if isinstance(r, list) else []:
            res = x.get("result") or {}
            if res: out[res["hash"]] = (res.get("to") or "").lower()
        time.sleep(0.05)
    return out
rows = []
for fn in sorted(glob.glob("/opt/hist/rh/2026-*.db")):
    d = os.path.basename(fn)[:10]
    if not (d0 <= d <= d1): continue
    db = sqlite3.connect("file:%s?mode=ro" % fn, uri=True)
    for pid, pool, c0, c1, tx in db.execute("SELECT rowid, pool, c0, c1, tx FROM pools WHERE fee=100 AND spacing=1"):
        hk = db.execute("SELECT hooks FROM pools WHERE rowid=?", (pid,)).fetchone()[0]
        if (hk or ZERO).lower() != ZERO: continue
        if WETH not in ((c0 or "").lower(), (c1 or "").lower()): continue
        fs = db.execute("SELECT MIN(block) FROM swaps WHERE pid=?", (pid,)).fetchone()[0]
        rows.append([d, pool.lower(), tx, fs])
print("weth pond pools", len(rows), flush=True)
to = batch(sorted({r[2] for r in rows if r[2]}))
pm = [r for r in rows if to.get(r[2]) == PM]
print("tx_to found", len(to), "via PositionManager", len(pm), "other", collections.Counter(to.get(r[2]) for r in rows if to.get(r[2]) != PM).most_common(4), flush=True)
open("/opt/fw2/wpond_pools.txt", "w").write("\n".join(r[1] for r in pm) + "\n")
by = collections.defaultdict(lambda: [0, 0, 0, 0])   # traded, alive at entry, pulled before exit, never pulled in data
for d, pool, tx, fs in pm:
    if fs is None: continue
    pb = pull_b(pool); e = fs + 4 / SPB; x = e + 900 / SPB
    by[d][0] += 1
    if pb is not None and pb <= e: continue
    by[d][1] += 1
    if pb is not None and pb <= x: by[d][2] += 1
tot = [sum(v[i] for v in by.values()) for i in range(3)]
print("day | traded | alive at +4 s | pulled before 15 min | share")
for d in sorted(by): v = by[d]; print(d, v[0], v[1], v[2], "%.0f%%" % (100 * v[2] / v[1] if v[1] else 0))
print("ALL", tot, "%.0f%%" % (100 * tot[2] / tot[1] if tot[1] else 0))