import pandas as pd, numpy as np, json, gzip, collections
from sklearn.metrics import roc_auc_score
d=pd.read_pickle('clean.pkl').sort_values('t0')
W=json.load(gzip.open('wall.json.gz','rt'))
# events: coin reaches sellable 1M at time t0+ts_1e6 -> its W300 wallets become alumni from then on
ev=[]
for r in d[d['ts_1e+06'].notna()].itertuples():
    ev.append((r.t0+getattr(r,'_%d'%list(d.columns).index('ts_1e+06')+1) if False else r.t0+r._asdict()['_%d'%(list(d.columns).index('ts_1e+06')+1)] if False else None))
ev=[(row.t0+row['ts_1e+06'],row.tok,row.fam) for _,row in d[d['ts_1e+06'].notna()].iterrows()]
ev.sort()
alum=collections.Counter(); alum_org=collections.Counter(); k=0
v60=[];v300=[];vo60=[]
for _,r in d.iterrows():
    while k<len(ev) and ev[k][0]<r.t0:
        for w in W.get(ev[k][1],([],[]))[1]:
            alum[w]+=1
            if ev[k][2]=='organic': alum_org[w]+=1
        k+=1
    w60,w300=W.get(r.tok,([],[]))
    v60.append(sum(1 for w in w60 if alum[w])); v300.append(sum(1 for w in w300 if alum[w])); vo60.append(sum(1 for w in w60 if alum_org[w]))
d['vets60']=v60; d['vets300']=v300; d['vetsorg60']=vo60
d.to_pickle('clean.pkl')
o=d[(d.fam=='organic')&(d.start=='small')&(d.day>='2026-09-20')]
for tgt,L in [('1M',1e6),('2M',2e6)]:
    y=(o.pk>=L)
    for f in ['vets60','vets300','vetsorg60','ub_60','ub_300']:
        print(tgt,f,'AUC',round(roc_auc_score(y,o[f].fillna(0)),3))
print(pd.crosstab(pd.cut(o.vets60,[-1,0,2,5,10,1000]),o.pk>=1e6,normalize='index').round(4)*100, pd.cut(o.vets60,[-1,0,2,5,10,1000]).value_counts().sort_index())