import pandas as pd, numpy as np, csv, bisect, sqlite3, json, collections
S2="/mnt/user-data/uploads/an4/s2"
DISP={"0x84157d3d24630c297849c84d49ae7f1805c1b60f","0x03802649e469ed5680c58dff9e65425e22d21cc5"}
A=sorted(sqlite3.connect(S2+"/btanchor.db").execute("SELECT b,t FROM a").fetchall()); T=[t for _,t in A]
def tblock(t):
    i=min(max(bisect.bisect_right(T,t)-1,0),len(A)-2);(b0,t0),(b1,t1)=A[i],A[i+1];return int(b0+(b1-b0)*(t-t0)/(t1-t0))
EU=json.load(open(S2+"/ethusd_hourly.json"));EUT=[a for a,_ in EU]
def eu(t): return EU[min(len(EU)-1,max(0,bisect.bisect_left(EUT,t)))][1]
ROOT={r["pool"].lower():(r["root"] or "").lower() for r in csv.DictReader(open("crewlaunch.csv"))}
d=pd.read_csv("an41s2_rows.csv.gz"); coin=pd.read_csv(S2+"/coin.csv").set_index("token")
sw=pd.read_csv(S2+"/sw.csv.gz"); sw["wallet"]=sw.wallet.str.lower(); G={k:g for k,g in sw.groupby("token")}
ff=pd.read_csv(S2+"/ff.csv"); F=dict(zip(ff.addr.str.lower(),ff.funder.fillna("").str.lower())); V=dict(zip(ff.addr.str.lower(),ff.via.fillna("").str.lower()))
cw=pd.read_csv(S2+"/cw.csv")
for a,f,v in zip(cw.addr.str.lower(),cw.funder.fillna("").str.lower(),cw.via.fillna("").str.lower()):
    F.setdefault(a,f); V.setdefault(a,v)
res=[]
for r in d.itertuples():
    tok=r.token.lower(); g=G.get(tok); o={}
    if g is not None and tok in coin.index:
        c=coin.loc[tok]; ins={str(c.creator).lower(),str(c.crew).lower(),ROOT.get(r.pool,"")}-{""}
        blk=tblock(r.t)
        for lab,sec in (("15",900),("1h",3600)):
            w=g[(g.b<blk)&(g.b>=tblock(r.t-sec))]
            isop=w.wallet.map(lambda x: V.get(x,"") in DISP or F.get(x,"") in ins or x in ins)
            b=w.side==1; s=w.side==-1; e=eu(r.t)
            tb=w[b].eth.sum(); ob=w[b&isop].eth.sum(); os_=w[s&isop].eth.sum(); ts=w[s].eth.sum()
            o["op2_buy_share"+lab]=ob/tb if tb else np.nan
            o["op2_net_cap"+lab]=(ob-os_)*e/r.cap
            o["out2_net_cap"+lab]=((tb-ob)-(ts-os_))*e/r.cap
            o["disp_buy_share"+lab]=w[b&w.wallet.map(lambda x: V.get(x,"") in DISP)].eth.sum()/tb if tb else np.nan
    res.append(o)
x=pd.concat([d.reset_index(drop=True),pd.DataFrame(res)],axis=1); x.to_csv("an41s2b_rows.csv.gz",index=False)
for g_ in "WC":
    s=x[x.grp==g_]; print(g_, s[["op2_buy_share15","op2_buy_share1h","disp_buy_share1h","op2_net_cap15","out2_net_cap15"]].median().round(3).to_dict(), "disp>0", round((s.disp_buy_share1h>0).mean(),3))
s=x[(x.grp=="W")&(x.net15_cap>=0.25)]
print("W net15>=.25:",len(s),"op2 net >=.25",round((s.op2_net_cap15>=.25).mean(),2),"out2 net>=.25",round((s.out2_net_cap15>=.25).mean(),2),"median outside share of net",round((s.out2_net_cap15/s.net15_cap).median(),2))