"""Are WETH-pond 'pulls' real? For pools pulled before entry+15 min: liquidity re-added after, swaps after, ETH-side price after."""
import sqlite3, glob, os, collections, numpy as np
WETH = "0x0bd7d308f8e1639fab988df18a8011f41eacad73"; SPB = 0.101283
L = sqlite3.connect("file:/opt/pricepredict-data/liqlock.db?mode=ro", uri=True)
pools = set(open("/opt/fw2/wpond_pools.txt").read().split())
res = collections.Counter(); after_sw = []; readd = []; frac_left = []; n = 0
for fn in sorted(glob.glob("/opt/hist/rh/2026-*.db")):
    d = os.path.basename(fn)[:10]
    if not ("2026-09-20" <= d <= "2026-10-05"): continue
    db = sqlite3.connect("file:%s?mode=ro" % fn, uri=True)
    for pid, pool in db.execute("SELECT rowid, pool FROM pools WHERE fee=100 AND spacing=1"):
        if pool.lower() not in pools: continue
        ml = L.execute("SELECT b, li, CAST(dl AS REAL) FROM ml WHERE pool=? ORDER BY b, li", (pool,)).fetchall()
        add = 0.0; pb = None; cur = 0.0
        for b, li, dl in ml:
            cur += dl
            if dl > 0: add += dl
            elif dl < 0 and add > 0 and -dl >= 0.5 * add and pb is None: pb = b; left_at = cur
        fs = db.execute("SELECT MIN(block) FROM swaps WHERE pid=?", (pid,)).fetchone()[0]
        if fs is None or pb is None: continue
        if not (fs + 4 / SPB < pb <= fs + 904 / SPB): continue
        n += 1
        later_add = sum(dl for b, li, dl in ml if b > pb and dl > 0)
        final = sum(dl for b, li, dl in ml)
        readd.append(later_add > 0); frac_left.append(final / add if add else 0)
        k = db.execute("SELECT COUNT(*) FROM swaps WHERE pid=? AND block > ?", (pid, pb + 50)).fetchone()[0]
        after_sw.append(k)
print("pools pulled between entry and 15 min:", n)
print("liquidity re-added after the 'pull':", sum(readd), "share %.0f%%" % (100 * np.mean(readd)))
print("liquidity left at end vs total added q10/50/90:", np.round(np.quantile(frac_left, [.1, .5, .9]), 3).tolist())
print("swaps more than 5 s after pull: share with any %.0f%%, median %d" % (100 * np.mean(np.array(after_sw) > 0), np.median(after_sw)))