"""WETH pond: buy at first trade + 4 s (price at last swap <= +6 s), sell at 15 min. Pulled before sell = 0.
Also shows the WRONG version (pulled coins marked at their last price before the pull) to compare with Analyst 3."""
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())
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
R = collections.defaultdict(list); W = collections.defaultdict(list)
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, sqrtp FROM swaps WHERE pid=? ORDER BY block, li", (pid,)).fetchall()
        if not S: continue
        b = np.array([x[0] for x in S]); sp = np.array([float(int(x[1])) for x in S]) / 2 ** 96
        p = (sp ** 2) if weth0 else 1 / (sp ** 2)   # tokens per WETH if weth is token0; value of token in WETH = 1/p
        val = 1 / p if weth0 else p
        val = (1 / (sp ** 2)) if weth0 else (sp ** 2)
        fs = b[0]; pb = pull_b(pool)
        e = fs + 4 / SPB; ef = e + 2 / SPB; x = e + 900 / SPB
        if pb is not None and pb <= e: continue
        i = np.searchsorted(b, ef, "right") - 1
        v0 = val[i]
        if pb is not None and pb <= x:
            R[d].append(0.0)
            j = np.searchsorted(b, pb, "left") - 1
            W[d].append(val[max(j, i)] / v0)
        else:
            j = np.searchsorted(b, x, "right") - 1
            R[d].append(val[j] / v0); W[d].append(val[j] / v0)
allr = np.concatenate([R[d] for d in R]); allw = np.concatenate([W[d] for d in W])
print("coins", len(allr), "honest mean multiple %.2fx (return %+.0f%%), median %.2fx, share zero %.0f%%" % (allr.mean(), 100 * (allr.mean() - 1), np.median(allr), 100 * np.mean(allr == 0)))
print("WRONG (pulled marked at last price) mean %.2fx (%+.0f%%)" % (allw.mean(), 100 * (allw.mean() - 1)))
print("survivors to 15 min: n %d mean %.2fx median %.2fx" % ((allr > 0).sum(), allr[allr > 0].mean(), np.median(allr[allr > 0])))
for d in sorted(R): r = np.array(R[d]); print(d, len(r), "%+.0f%%" % (100 * (r.mean() - 1)), "wrong %+.0f%%" % (100 * (np.mean(W[d]) - 1)))