import sqlite3,collections
d=sqlite3.connect('/opt/sw/dataeng/v3tx.db')
rows=d.execute("select e.req,e.pool,e.t,e.usd,x.tx_from from early e join txf x using(tx) where e.req in('a3run','a3ctl')").fetchall()
pools={}
for r,p,t,u,w in rows:
    P=pools.setdefault(p,{'r':r=='a3run','t':t,'w':set()});P['t']=min(P['t'],t);P['w'].add(w)
cr=dict(d.execute("select pool,tx_from from creator"))
order=sorted(pools,key=lambda p:pools[p]['t'])
print(len(order),sum(pools[p]['r'] for p in order))
# wallet stats from earlier pools
wr=collections.Counter();wc=collections.Counter()
res=[]
for p in order:
    P=pools[p]
    # feature: count of early wallets that were in a prior runner pool
    k=sum(1 for w in P['w'] if wr[w]>0); kc=sum(1 for w in P['w'] if wc[w]>0 and wr[w]==0)
    res.append((P['r'],k,kc,len(P['w'])))
    for w in P['w']:
        (wr if P['r'] else wc)[w]+=1
import statistics
for th in (0,1,2,3,5):
    a=[x for x in res[len(res)//3:] if x[1]>th]; b=[x for x in res[len(res)//3:] if x[1]<=th]
    f=lambda L:(len(L),round(sum(x[0] for x in L)/max(1,len(L)),3))
    print('prior-runner wallets >',th,f(a),'else',f(b))
# bots: wallet appearing in many pools (any) - infra
cnt=collections.Counter(w for p in pools for w in pools[p]['w'])
print(cnt.most_common(5))
# creator repeat
cw=collections.Counter();cn=collections.Counter();out=[]
for p in order:
    c=cr.get(p)
    out.append((pools[p]['r'],cw[c],cn[c]));
    (cw if pools[p]['r'] else cn)[c]+=1
for lab,L in(('creator prior runner',[x for x in out if x[1]>0]),('creator prior ctl only',[x for x in out if x[1]==0 and x[2]>0]),('new',[x for x in out if x[1]==0 and x[2]==0])):
    print(lab,len(L),round(sum(x[0] for x in L)/max(1,len(L)),3))
print('--- excl infra')
bad={w for w,c in cnt.items() if c>=30}
print(len(bad))
wr=collections.Counter();wc=collections.Counter();res=[]
for p in order:
    P=pools[p];ws=P['w']-bad
    k=sum(1 for w in ws if wr[w]>0 and wr[w]>=wc[w])
    res.append((P['r'],k))
    for w in ws:(wr if P['r'] else wc)[w]+=1
h=res[len(res)//3:]
for th in(0,1,2,4):
    a=[x[0] for x in h if x[1]>th];b=[x[0] for x in h if x[1]<=th]
    print('>',th,len(a),round(sum(a)/max(1,len(a)),3),'else',len(b),round(sum(b)/max(1,len(b)),3))
print('creators with prior runner:')
cw=collections.Counter()
for p in order:
    c=cr.get(p)
    if cw[c]>0:print(c,pools[p]['r'],cw[c])
    if pools[p]['r']:cw[c]+=1
print('--- weighted')
W=7.0  # est control weight, verify below
for th in(0,1,2,4):
    a=[x[0] for x in h if x[1]>th];b=[x[0] for x in h if x[1]<=th]
    f=lambda L:round(sum(L)/(sum(L)+W*(len(L)-sum(L))),3)
    print('>',th,len(a),sum(a),f(a),'else',f(b))