"""DC1.1 build (DC Analyst 1, 7 Oct 2026). Runs on research-box, read only on PP data. Writes /root/dca1/ev_*.pkl (per pool event series).
Event = (t, cap, caplow, sell_usd, buy_usd, nbuy, sellable_cap or 0, R_usd or 0)."""
import sqlite3, glob, os, sys, json, pickle, bisect, time, collections, math, datetime
sys.path.insert(0, "/opt/sw")
from pp3_tfix import atime
ZERO = "0x" + "0" * 40; WETH = "0x0bd7d308f8e1639fab988df18a8011f41eacad73"; SPB = 0.101283
T_START = int(datetime.datetime(2026, 9, 14, 17, 0, tzinfo=datetime.timezone.utc).timestamp())   # 15 Sep 00:00 BKK
T_END = int(datetime.datetime(2026, 10, 5, 17, 0, tzinfo=datetime.timezone.utc).timestamp())     # 6 Oct 00:00 BKK
OUT = "/root/dca1"; os.makedirs(OUT, exist_ok=True)
EUL = json.load(open("/opt/sw/ethusd_hourly.json")); EUT = [a for a, _ in EUL]
def eu(t):
    i = min(len(EUL) - 1, max(0, bisect.bisect_left(EUT, t))); return EUL[i][1]
LQ = sqlite3.connect("file:/opt/pricepredict-data/liqlock.db?mode=ro", uri=True, timeout=120)
def v4pull(pool):
    r = LQ.execute("SELECT MIN(t) FROM liq WHERE pool=? AND kind='pull' AND share>=0.5", (pool,)).fetchone()
    return float(r[0]) if r and r[0] else 0.0
def med(x):
    s = sorted(x); return s[len(s) // 2] if s else 0.0
def v4():
    pools = {}
    for fn in sorted(glob.glob("/opt/hist/rh/2026-*.db")):
        day = os.path.basename(fn)[:10]
        if day < "2026-09-14" or day > "2026-10-05": continue
        db = sqlite3.connect("file:%s?mode=ro" % fn, uri=True)
        kv = dict(db.execute("SELECT k, v FROM kv").fetchall())
        try: t1 = int(kv["t1"]); b1 = int(kv["blocks"].split("-")[1])
        except Exception: t1 = b1 = None
        L = {}
        for tok, mt, cr, sym, dec, sup, ts in db.execute("SELECT token, mint_to, creator, symbol, decimals, supply, ts FROM launches"):
            try: L[(mt or "").lower()] = (tok.lower(), (cr or "").lower(), sym, int(dec or 18), int(sup) / 10 ** int(dec or 18), ts)
            except Exception: pass
        ncand = 0
        for pid, pool, c0, c1, fee, sp, hk in db.execute("SELECT rowid, pool, c0, c1, fee, spacing, hooks FROM pools"):
            pool = pool.lower(); c0 = (c0 or "").lower(); c1 = (c1 or "").lower()
            if pool not in L: continue
            tok, cr, sym, dec, sup, ts = L[pool]
            if tok not in (c0, c1) or not sup: continue
            q = c1 if c0 == tok else c0
            if q not in (ZERO, WETH): continue
            inv = (c0 == tok)
            def px(p):
                if p <= 0: return 0.0
                return (p if inv else 1 / p) * 10 ** (dec - 18)
            ev = []; prev_sq = None; Rs = []; last_blk = None; Rlast = 0.0; plast = 0.0
            for blk, a0, a1, sq in db.execute("SELECT block, a0, a1, sqrtp FROM swaps WHERE pid=? ORDER BY block, li", (pid,)):
                p = (int(sq) / 2 ** 96) ** 2; pe = px(p); qa = int(a1 if inv else a0) / 1e18
                t = atime(blk); e = eu(t); last_blk = blk
                if prev_sq is not None and pe > 0:
                    d = abs(math.sqrt(pe) - math.sqrt(prev_sq))
                    if d > 0 and abs(qa) > 0: Rs.append(abs(qa) / d * math.sqrt(pe)); Rs = Rs[-7:]
                prev_sq = pe
                R = med(Rs) * e if Rs else 0.0
                cap = pe * sup * e; usd = abs(qa) * e
                buy = usd if qa < 0 else 0.0; sell = usd if qa > 0 else 0.0
                ev.append((t, cap, cap, sell, buy, 1 if qa < 0 else 0, cap if sell >= 100 else 0.0, R))
                if R > 0: Rlast, plast = R, cap
            if not ev or max(x[1] for x in ev) < 50_000 and not t1: continue
            tmax_l = (t1 + int((last_blk - b1) * SPB)) if (ev and t1) else 0
            if t1:
                for t, o, h, l, c, ap, an, npos, nneg in db.execute("SELECT t, o, h, l, c, a0_pos, a0_neg, n_pos, n_neg FROM bars WHERE pid=? ORDER BY t", (pid,)):
                    if t <= tmax_l: continue
                    tr = atime(b1 + round((t - t1) / SPB)) + 30; e = eu(tr)
                    cc = px(c) * sup * e; ch = px(h) * sup * e; cl = px(l) * sup * e; clo = min(ch, cl)
                    if not inv: sell = ap * e; buy = an * e; nb = nneg
                    else: sell = an * px(c) * e; buy = ap * px(c) * e; nb = npos
                    R = Rlast * math.sqrt(cc / plast) if plast > 0 and cc > 0 else 0.0
                    ev.append((tr, cc, clo, sell, buy, nb, clo if sell >= 100 else 0.0, R))
            if max(x[6] for x in ev) < 100_000: continue
            t0 = ev[0][0]
            if t0 < T_START or t0 >= T_END: continue
            ncand += 1
            pools[pool] = dict(venue="v4", pool=pool, token=tok, sym=sym, creator=cr, fee=fee, hooks=(hk or ZERO).lower(), t0=t0,
                               pull=v4pull(pool), dataend=0, ev=ev)
        print(day, "candidates", ncand, flush=True)
    return pools
def v3():
    S = collections.defaultdict(dict); P = {}; REM = collections.defaultdict(dict)
    for f in ("/opt/sw/v3hist.db", "/opt/sw/v3live.db"):
        d = sqlite3.connect("file:%s?mode=ro" % f, uri=True, timeout=120)
        for pool, v, tok, cr in d.execute("SELECT pool, venue, token, creator FROM pools"): P.setdefault(pool.lower(), (v, (tok or "").lower(), (cr or "").lower()))
        cand = [p for (p,) in d.execute("SELECT pool FROM swaps GROUP BY pool HAVING MAX(mcap_usd) >= 100000")]
        print(f, "cand pools", len(cand), flush=True)
        for p in cand:
            for t, li, tx, side, usd, eth, cap, pe in d.execute("SELECT t, li, tx, side, usd, eth, mcap_usd, pool_eth_after FROM swaps WHERE pool=? AND mcap_usd IS NOT NULL", (p,)):
                S[p.lower()][(tx, li)] = (t, li, side, usd or 0.0, eth or 0.0, cap, pe or 0.0)
        for p, t, li, tx, eth, kind in d.execute("SELECT pool, t, li, tx, eth, kind FROM liq WHERE eth > 0"):
            REM[p.lower()][(tx, li)] = (t, li, eth if kind == 'add' else -eth)
    pools = {}
    for p, rows in S.items():
        r = sorted(rows.values())
        # pool WETH rebuilt from flows: adds - removes + buy ETH - sell ETH (v3hist has no pool balance); pull = a remove >= 50% of it
        liq = sorted(REM.get(p, {}).values()); k = 0; cur = 0.0; pull = 0.0; ev = []
        for t, li, side, usd, eth, cap, pe in r:
            while k < len(liq) and (liq[k][0], liq[k][1]) <= (t, li):
                lt, _, de = liq[k]; k += 1
                if de < 0 and cur > 0 and -de >= 0.5 * cur and not pull: pull = float(lt)
                cur = max(0.0, cur + de)
            cur = max(0.0, cur + (eth if side == 1 else -eth))
            e = usd / eth if eth > 0 else eu(t)
            buy = usd if side == 1 else 0.0; sell = usd if side != 1 else 0.0
            R = (pe if pe else cur) * e
            ev.append((t, cap, cap, sell, buy, 1 if side == 1 else 0, cap if sell >= 100 else 0.0, R))
        while k < len(liq):
            lt, _, de = liq[k]; k += 1
            if de < 0 and cur > 0 and -de >= 0.5 * cur and not pull: pull = float(lt)
            cur = max(0.0, cur + de)
        if not ev or max(x[6] for x in ev) < 100_000: continue
        t0 = ev[0][0]
        if t0 < T_START or t0 >= T_END: continue
        v, tok, cr = P.get(p, ("v3", "", ""))
        pools[p] = dict(venue=v, pool=p, token=tok, sym="", creator=cr, fee=None, hooks="", t0=t0, pull=pull, dataend=0, ev=ev)
    print("v3 pools in universe", len(pools), flush=True)
    return pools
if __name__ == "__main__":
    which = sys.argv[1]
    t = time.time()
    res = v4() if which == "v4" else v3()
    pickle.dump(res, open(OUT + "/ev_%s.pkl" % which, "wb"))
    print("done", which, len(res), "pools", int(time.time() - t), "s", flush=True)