"""WETH pond, independent rebuild with fills: buy $20 at first trade +4 s (pool state at last swap <= +6 s), sell all at +15 min
(state at last swap <= +15 min +2 s). Liquidity L from the pool's own swaps near each fill. GMGN 1% each side, pool fee 0.01%,
gas $0.03 a side. Pulled before the sell = 0. Prints 10 coins by hand (blocks, prices, tx) for spot checks."""
import sqlite3, glob, os, collections, numpy as np, random
WETH = "0x0bd7d308f8e1639fab988df18a8011f41eacad73"; SPB = 0.101283; ETHUSD = 2690.0; SIZE = 20.0
L = sqlite3.connect("file:/opt/pricepredict-data/liqlock.db?mode=ro", uri=True)
pools = set(open("/opt/fw2/wpond_pools.txt").read().split())
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 localL(xs, wamt, weth0, i):
    Ls = []; j = i
    while j > 0 and len(Ls) < 5:
        xa, xb = xs[j], xs[j - 1]
        d = abs(1 / xa - 1 / xb) if weth0 else abs(xa - xb)
        if d > 0 and abs(wamt[j]) > 0: Ls.append(abs(wamt[j]) / d)
        j -= 1
    return float(np.median(Ls)) if Ls else None
def buy(Lq, x, weth, weth0):     # x = sqrt(token1/token0) (Q96-normalised); returns tokens out (raw)
    net = weth * 1e18 * 0.99 * 0.9999
    if weth0:  # weth in as token0, x falls
        x2 = 1 / (1 / x + net / Lq); return Lq * (x - x2)
    else:      # weth in as token1, x rises
        x2 = x + net / Lq; return Lq * (1 / x - 1 / x2)
def sell(Lq, x, tok, weth0):
    t = tok * 0.9999
    if weth0:  # token1 in, x rises, weth0 out
        x2 = x + t / Lq; return Lq * (1 / x - 1 / x2) / 1e18 * 0.99
    else:
        x2 = 1 / (1 / x + t / Lq); return Lq * (x - x2) / 1e18 * 0.99
R = collections.defaultdict(list); rows = []
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, c0, c1 in db.execute("SELECT rowid, pool, c0, c1 FROM pools WHERE fee=100 AND spacing=1"):
        if pool.lower() not in pools: continue
        weth0 = c0.lower() == WETH
        S = db.execute("SELECT block, a0, a1, sqrtp, tx FROM swaps WHERE pid=? ORDER BY block, li", (pid,)).fetchall()
        if len(S) < 3: continue
        b = np.array([s[0] for s in S]); xs = np.array([float(int(s[3])) for s in S]) / 2 ** 96
        wamt = np.array([float(int(s[1] if weth0 else s[2])) for s in S])
        fs = b[0]; pb = pull_b(pool); e = fs + 4 / SPB; ef = e + 2 / SPB; x15 = e + 900 / SPB
        if pb is not None and pb <= e: continue
        i = int(np.searchsorted(b, ef, "right")) - 1
        Lq = localL(xs, wamt, weth0, i)
        if not Lq or i < 1: continue
        tok = buy(Lq, xs[i], SIZE / ETHUSD, weth0)
        if pb is not None and pb <= x15 + 2 / SPB:
            r = -1.0 - 0.03 / SIZE; k = None; out = 0.0
        else:
            k = int(np.searchsorted(b, x15 + 2 / SPB, "right")) - 1
            Lk = localL(xs, wamt, weth0, k) or Lq
            out = sell(Lk, xs[k], tok, weth0) * ETHUSD
            r = (out - 0.03) / (SIZE + 0.03) - 1
        R[d].append(r)
        rows.append((d, pool, S[i][4], S[k][4] if k is not None else None, int(b[i] - fs), int(b[k] - fs) if k is not None else None, pb, r))
allr = np.concatenate([R[d] for d in R])
print("coins %d mean %+.0f%% median %+.0f%% zero-or-less %.0f%%" % (len(allr), 100 * allr.mean(), 100 * np.median(allr), 100 * np.mean(allr <= -0.99)))
print("days up", sum(np.mean(R[d]) > 0 for d in R), "of", len(R))
for d in sorted(R): print(d, len(R[d]), "%+.0f%%" % (100 * np.mean(R[d])))
random.seed(7); pick = random.sample([r for r in rows if r[3]], 5) + random.sample([r for r in rows if not r[3]], 5)
print("HAND CHECK (day, pool, entry tx, exit tx, entry blocks after first swap, exit blocks, pull block, return):")
for r in pick: print(r)