"""Time ladder (Ben 5 Oct 2026): for every RH launch 15-28 Sep, features at checkpoints from the first trade,
plus the 7-day peak market cap. Read-only on copies of the droplet DBs. Output rows_<day>.jsonl.gz"""
import sqlite3, json, gzip, bisect, sys, os, math, time, collections
D = '/tmp/rl'
SPB = 0.101283
PM = '0x8366a39cc670b4001a1121b8f6a443a643e40951'; Z = '0x' + '0' * 40; WETH = '0x0bd7d308f8e1639fab988df18a8011f41eacad73'
USDG = '0x5fc5360d0400a0fd4f2af552add042d716f1d168'
CHK = [30, 60, 120, 300, 600, 900, 1800, 3600, 7200, 10800, 21600]
LEVELS = [5e4, 1e5, 2.5e5, 5e5, 1e6, 2e6, 5e6]
QDEC = json.load(open(D + '/qdec.json'))
SUPX = json.load(open(D + '/sup_extra.json')) if os.path.exists(D + '/sup_extra.json') else {}
P = sqlite3.connect(D + '/prices.db')
ET = P.execute("select t,c from px where sym='ETHUSDT' order by t").fetchall(); ETt = [t for t, _ in ET]
def eth(t):
    k = min(max(bisect.bisect_right(ETt, t) - 1, 0), len(ET) - 1); return ET[k][1]
R = json.load(gzip.open('/tmp/run/rt.json.gz', 'rt'))
RT = {t['address'].lower(): t for t in R['tokens']}
SUP = {k.lower(): v for k, v in R['supply'].items()}
KL = {k.lower(): v for k, v in R['kline'].items()}
AK = json.load(gzip.open('/tmp/run/ak.json.gz', 'rt'))
def is_user(a):
    if a in (PM, Z) or a.endswith('dead'): return False
    k = AK.get(a); return k in (None, 'user', 'user?')

def qusd_direct(q, t):
    if q in (Z, WETH): return eth(t)
    if q == USDG: return 1.0
    k = KL.get(q)
    if k:
        i = bisect.bisect_right([x[0] for x in k], t) - 1
        if i >= 0 and t - k[i][0] < 7200: return k[i][4]
    return None

def do_day(day):
    t_start = time.time()
    fn = '%s/d%s.db' % (D, day[-2:]) if day[5:7] == '09' and int(day[-2:]) <= 28 else '%s/d%sx.db' % (D, day[-2:]); c = sqlite3.connect('file:' + fn + '?mode=ro', uri=True)
    kv = dict(c.execute('select k,v from kv'))
    b1 = int(kv['blocks'].split('-')[1]); t1 = int(kv['t1'])
    bt = lambda b: t1 - (b1 - b) * SPB
    pools = {}
    bytok = collections.defaultdict(list)
    for pid, pool, c0, c1, fee, sp, hk, blk in c.execute('select rowid,pool,c0,c1,fee,spacing,hooks,block from pools'):
        pools[pid] = (pool, c0, c1, fee, hk, blk)
    L = {}
    for tok, blk, ts, mto, cr, fac, nm, sym, dec, sup in c.execute('select token,block,ts,mint_to,creator,factory,name,symbol,decimals,supply from launches'):
        L[tok] = dict(blk=blk, ts=ts, mto=mto, cr=cr, fac=fac, nm=nm, sym=sym, dec=dec, sup=sup)
    for pid, (pool, c0, c1, fee, hk, blk) in pools.items():
        for tok, q in ((c0, c1), (c1, c0)):
            if tok in L: bytok[tok].append(pid)
    sw = collections.defaultdict(list)
    for pid, b, li, tx, a0, a1, sq in c.execute('select pid,block,li,tx,a0,a1,sqrtp from swaps order by pid,block,li'):
        sw[pid].append((b, li, tx, a0, a1, sq))
    bars = collections.defaultdict(list)
    for pid, t, cl, h, ap in c.execute('select pid,t,c,h,a0_pos from bars order by pid,t'):
        bars[pid].append((t, cl))
    out = []; calib = []
    for tok, l in L.items():
        pids = bytok.get(tok)
        if not pids: continue
        if l['sup'] is None and tok in SUPX and SUPX[tok][0]:
            l['sup'] = str(SUPX[tok][0]); l['dec'] = SUPX[tok][1] if SUPX[tok][1] is not None else 18; l['meta_src'] = 'rpc'
        try: dec = int(l['dec'] if l['dec'] is not None else 18); sup = int(l['sup']) / 10 ** dec
        except Exception: continue
        if sup <= 0: continue
        if tok in SUP and SUP[tok]: sup_rt = SUP[tok]
        else: sup_rt = sup
        ev = []   # (t, pid, price_quote_per_token, quote_amt (+buy), tx, q)
        for pid in pids:
            pool, c0, c1, fee, hk, blk = pools[pid]
            inv = tok == c1; q = c0 if inv else c1
            qd = (QDEC.get(q) or [18])[0] or 18
            for b, li, tx, a0, a1, sq in sw.get(pid, []):
                x = (int(sq) / 2 ** 96) ** 2
                if x <= 0: continue
                px = (1 / x if inv else x) * 10 ** (dec - qd)
                qa = int(a0 if inv else a1) / 10 ** qd
                ev.append((bt(b), b, li, pid, px, qa, tx, q))
        if len(ev) < 3: continue
        ev.sort(key=lambda e: (e[1], e[2]))
        # main pool = most swaps
        cnt = collections.Counter(e[3] for e in ev); mp = cnt.most_common(1)[0][0]
        mq = pools[mp][1] if pools[mp][2] == tok else pools[mp][2]
        t0 = ev[0][0]
        # quote USD for main quote: direct or calibrate later
        def mc_at(px, t, q):
            u = qusd_direct(q, t)
            return px * sup * u if u else None
        # calibration rows for runners with klines
        if tok in KL and mq not in (Z, WETH, USDG):
            k = KL[tok]
            mev = [e for e in ev if e[3] == mp]; mt = [e[0] for e in mev]
            for row in k:
                te = row[0] + 3600
                if te < t0 or te > t0 + 21600: continue
                i = bisect.bisect_right(mt, te) - 1
                if i >= 0 and te - mt[i] < 600 and mev[i][4] > 0:
                    calib.append((mq, day, row[4] / mev[i][4]))
        out.append(dict(tok=tok, day=day, t0=t0, l=l, sup=sup, sup_rt=sup_rt, mp=mp, mq=mq,
                        pool_blk=pools[mp][5], hook=pools[mp][4], fee=pools[mp][3], npools=len(pids),
                        ev=[(round(e[0] - t0, 2), e[3] == mp, e[4], e[5], e[6], e[7]) for e in ev if e[0] - t0 <= 21600],
                        bars=[(t - t0, cl) for t, cl in bars.get(mp, []) if t - t0 <= 7 * 86400 + 3600],
                        inv=pools[mp][2] == tok))
    # transfers: buyers per token (first 30 min)
    trs = collections.defaultdict(list)
    for tok, b, li, fr, to, val, tx in c.execute('select token,block,li,frm,too,value,tx from transfers order by token,block,li'):
        trs[tok].append((bt(b), tx, fr, to, val))
    for o in out:
        rows = trs.get(o['tok'], [])
        bytx = collections.OrderedDict()
        for t, tx, fr, to, val in rows:
            bytx.setdefault(tx, []).append((t, fr, to, val))
        buys = []
        for tx, xs in bytx.items():
            net = collections.defaultdict(int)
            for t, fr, to, val in xs:
                try: v = int(val, 16) if isinstance(val, str) and val.startswith('0x') else int(val)
                except Exception: v = 0
                if fr == Z: continue
                net[fr] -= v; net[to] += v
            if net.get(PM, 0) < 0 or any(net.get(pools[p][0], 0) < 0 for p in [o['mp']]):
                for a, v in net.items():
                    if v > 0 and is_user(a): buys.append((round(xs[0][0] - o['t0'], 2), a, v))
        o['buys'] = buys
    c.close()
    with gzip.open('%s/rows_%s.jsonl.gz' % (D, day), 'wt') as f:
        for o in out: f.write(json.dumps(o) + '\n')
    json.dump(calib, open('%s/calib_%s.json' % (D, day), 'w'))
    return day, len(out), len(calib), round(time.time() - t_start)

if __name__ == '__main__':
    print(do_day(sys.argv[1]), flush=True)