#!/usr/bin/env python3
# ROT A1 Task 2: rotation clock. Survivor-free sector & chain indices from rot.db, legs, signal lead times.
import warnings; warnings.filterwarnings("ignore")
import sqlite3, json, numpy as np, pandas as pd, sys
DB='/opt/rot/db/rot.db'; OUT='/opt/rot/analyst/out'; import os; os.makedirs(OUT,exist_ok=True)
con=sqlite3.connect(f'file:{DB}?mode=ro',uri=True)
coins=pd.read_sql('select cmc_id,symbol,sector,chain,ever_top500 from coins',con).set_index('cmc_id')
d=pd.read_sql('select date,cmc_id,rank,price,vol,mcap from daily where cmc_id in (select cmc_id from coins where best_rank<=700)',con,parse_dates=['date'])
P=d.pivot(index='date',columns='cmc_id',values='price').sort_index()
M=d.pivot(index='date',columns='cmc_id',values='mcap').reindex(P.index)
V=d.pivot(index='date',columns='cmc_id',values='vol').reindex(P.index)
R=d.pivot(index='date',columns='cmc_id',values='rank').reindex(P.index)
idx=pd.date_range(P.index.min(),P.index.max()); P,M,V,R=[x.reindex(idx) for x in (P,M,V,R)]
# returns: price ffill up to 45 days so a gap then reappearance books the move; clamp glitches
Pf=P.ffill(limit=45); ret=(Pf/Pf.shift(1)-1).clip(-0.95,5.0)
# dead exits: member yesterday, no price for rest of history -> book -90% on exit day (counted)
last=P.apply(lambda s:s.last_valid_index())
member=(R.shift(1)<=500)
gone=0
for c,lv in last.items():
    if lv is not None and lv<idx[-1]-pd.Timedelta(days=45) and member[c].get(lv,False):
        t=lv+pd.Timedelta(days=1)
        if t in ret.index: ret.at[t,c]=-0.9; gone+=1
btc=P[1].ffill(); btcr=btc.pct_change()
sec=coins['sector'].reindex(P.columns).fillna('other'); ch=coins['chain'].reindex(P.columns).fillna('native')
DROP={'stable','wrapped','stock'}
W=M.shift(1).where(member)          # point-in-time membership, yesterday's cap
def index_of(mask,cap=0.35,ew=False):
    cols=mask[mask].index
    w=W[cols].copy()
    if ew: w=w.notna().astype(float).where(w.notna())
    w=w.div(w.sum(1),axis=0)
    if not ew:
        for _ in range(5): w=w.clip(upper=cap); w=w.div(w.sum(1),axis=0)
    r=(w*ret[cols]).sum(1,min_count=1)
    n=w.notna().sum(1)
    return r.where(n>=3), n
groups={}
for s in sorted(set(sec)-DROP): groups['S:'+s]=(sec==s)&(P.columns!=1)
for c in ['sol','eth','bnb','base','ton','sui','avax','tron','hyper','arb']: groups['C:'+c]=(ch==c)&(~sec.isin(DROP))
for c in ['sol','eth','bnb','base']: groups['MEME:'+c]=(ch==c)&(sec=='meme')
groups['ALT']=(~sec.isin(DROP))&(P.columns!=1)
IDX={};N={}
for g,m in groups.items():
    r,n=index_of(m); IDX[g]=(1+r.fillna(0)).cumprod(); N[g]=n
IDX=pd.DataFrame(IDX); N=pd.DataFrame(N)
RS=IDX.div(btc/btc.iloc[0],axis=0)
# sector volume & cap share (ex BTC, ex drop)
Vm=V.where(R<=500); Mm=M.where(R<=500)
tot_v=Vm.loc[:,(~sec.isin(DROP))&(P.columns!=1)].sum(1); tot_m=Mm.loc[:,(~sec.isin(DROP))&(P.columns!=1)].sum(1)
VS={g:Vm.loc[:,m].sum(1)/tot_v for g,m in groups.items()}; VS=pd.DataFrame(VS)
MS={g:Mm.loc[:,m].sum(1)/tot_m for g,m in groups.items()}; MS=pd.DataFrame(MS)
above=(Pf>Pf.rolling(20,min_periods=15).mean())
BR={g:above.loc[:,m].where(member.loc[:,m]).mean(1) for g,m in groups.items()}; BR=pd.DataFrame(BR)
# chain flows (DefiLlama)
cdl=pd.read_sql('select * from chains_daily',con,parse_dates=['date'])
LL={'sol':'Solana','eth':'Ethereum','bnb':'BSC','base':'Base','ton':'TON','sui':'Sui','avax':'Avalanche','tron':'Tron','hyper':'Hyperliquid L1','arb':'Arbitrum'}
def piv(col):
    x=cdl.pivot(index='date',columns='chain',values=col).reindex(idx)
    return x
DEX=piv('dex_vol'); STB=piv('stables'); TVL=piv('tvl')
dexshare=DEX.div(DEX[[c for c in DEX.columns if c in LL.values() or c in ('Polygon','Optimism','Aptos','Near','Fantom','Sonic','Berachain','Blast','zkSync Era','Linea','Mantle','Cronos','Injective','Osmosis','Cardano','Terra','Bitcoin')]].sum(1),axis=0)
# ---------- legs: zigzag on log RS ----------
def zigzag(s,th):
    s=np.log(s.dropna()); piv=[]
    if len(s)<10: return piv
    mode=None; lo=hi=s.iloc[0]; lot=hit=s.index[0]
    for t,v in s.items():
        if v>hi: hi,hit=v,t
        if v<lo: lo,lot=v,t
        if mode!='up' and v-lo>=np.log(1+th): piv.append(('L',lot,lo)); mode='up'; hi,hit=v,t
        elif mode!='down' and hi-v>=np.log(1+th): piv.append(('H',hit,hi)); mode='down'; lo,lot=v,t
    return piv
PH=[('2020-21',pd.Timestamp('2020-03-13'),pd.Timestamp('2021-11-10')),('2023-26',pd.Timestamp('2023-10-01'),idx[-1])]
legs=[]
for g in RS.columns:
    if g=='ALT': continue
    z=zigzag(RS[g][N[g]>=3],0.30)
    for a,b in zip(z,z[1:]):
        if a[0]=='L' and b[0]=='H':
            for ph,s0,s1 in PH:
                if s0<=b[1] and a[1]<=s1 and a[1]>=s0-pd.Timedelta(days=30):
                    legs.append(dict(phase=ph,group=g,start=a[1],peak=b[1],days=(b[1]-a[1]).days,rs_gain=round(np.exp(b[2]-a[2])-1,3),usd_gain=round(IDX[g][b[1]]/IDX[g][a[1]]-1,3),n=int(N[g][a[1]] if a[1] in N[g] and N[g][a[1]]==N[g][a[1]] else 0)))
legs=pd.DataFrame(legs).sort_values(['phase','start'])
# ---------- signals ----------
def first_fire(cond,start,peak,pre=21):
    w=cond.loc[start-pd.Timedelta(days=pre):peak]
    w=w[w==True]
    return w.index[0] if len(w) else None
SIG={}
for g in RS.columns:
    rs=RS[g]; SIG[(g,'rs_break60')]=rs>rs.shift(1).rolling(60,min_periods=40).max()
    vs=VS[g]; SIG[(g,'vol_share_1.5x')]=vs.rolling(7).mean()>1.5*vs.rolling(90,min_periods=60).mean()
    ms=MS[g]; SIG[(g,'cap_share_+20%_14d')]=ms/ms.shift(14)>1.2
    SIG[(g,'breadth_70')]=BR[g].rolling(3).mean()>0.70
    if g.startswith('C:') or g.startswith('MEME:'):
        c=LL.get(g.split(':')[1])
        if c in STB: SIG[(g,'stables_+5%_14d')]=STB[c]/STB[c].shift(14)>1.05
        if c in dexshare: ds=dexshare[c]; SIG[(g,'dex_share_1.3x')]=ds.rolling(7).mean()>1.3*ds.rolling(90,min_periods=60).mean()
TOP={}
for g in RS.columns:
    rs=RS[g]; TOP[(g,'stretch_rs_50d>+60%')]=rs/rs.rolling(50,min_periods=40).mean()>1.6
    vs=VS[g]; TOP[(g,'vol_share_z>2.5')]=(vs-vs.rolling(90,min_periods=60).mean())/vs.rolling(90,min_periods=60).std()>2.5
    TOP[(g,'breadth_90')]=BR[g].rolling(3).mean()>0.90
rows=[]
for _,L in legs.iterrows():
    g=L.group; rs=np.log(RS[g])
    for (gg,nm),cond in SIG.items():
        if gg!=g: continue
        t=first_fire(cond.fillna(False),L.start,L.peak)
        if t is None: rows.append(dict(phase=L.phase,group=g,start=L.start,signal=nm,lead_days=None,leg_left=None)); continue
        left=(rs[L.peak]-rs[t])/(rs[L.peak]-rs[L.start]) if rs[L.peak]!=rs[L.start] else None
        rows.append(dict(phase=L.phase,group=g,start=L.start,signal=nm,lead_days=(L.start-t).days,leg_left=round(min(1,left),3) if left is not None else None))
    for (gg,nm),cond in TOP.items():
        if gg!=g: continue
        w=cond.fillna(False).loc[L.start:L.peak+pd.Timedelta(days=10)]; w=w[w]
        rows.append(dict(phase=L.phase,group=g,start=L.start,signal='TOP:'+nm,lead_days=(L.peak-w.index[0]).days if len(w) else None,leg_left=None))
S=pd.DataFrame(rows)
# precision of each entry signal over the whole history (inside phases), cooldown 20d, fwd 30d RS
prec=[]
inph=pd.Series(False,index=idx)
for ph,s0,s1 in PH: inph.loc[s0:s1]=True
for (g,nm),cond in SIG.items():
    c=cond.fillna(False)&inph; fires=[]; lastf=None
    for t in c[c].index:
        if lastf is None or (t-lastf).days>20: fires.append(t)
        lastf=t if (lastf is None or (t-lastf).days>20) else lastf
    rs=RS[g]; f30=[rs.shift(-31).get(t)/rs.shift(-1).get(t)-1 for t in fires if t+pd.Timedelta(days=31)<=idx[-1]]
    base=(rs.shift(-31)/rs.shift(-1)-1)[inph].dropna()
    f30=[x for x in f30 if x==x]
    if f30: prec.append(dict(group=g,signal=nm,fires=len(f30),med30_vsBTC=round(float(np.median(f30)),3),hit20=round(float(np.mean([x>0.2 for x in f30])),2),base_med=round(float(base.median()),3),base_hit20=round(float((base>0.2).mean()),2)))
prec=pd.DataFrame(prec)
# market legs: ALT index in USD, who led
mk=[]; z=zigzag(IDX['ALT'],0.35)
for a,b in zip(z,z[1:]):
    if a[0]=='L' and b[0]=='H':
        gains={g:(RS[g][b[1]]/RS[g][a[1]]-1) for g in RS.columns if g!='ALT' and N[g].get(a[1],0)>=3}
        top=sorted(gains.items(),key=lambda x:-x[1])[:4]
        # peak order among sector indices within leg window (+30d)
        pk={g:IDX[g].loc[a[1]:b[1]+pd.Timedelta(days=30)].idxmax() for g,_ in top}
        mk.append(dict(start=a[1].date(),peak=b[1].date(),days=(b[1]-a[1]).days,alt_usd=round(np.exp(b[2]-a[2])-1,2),btc_usd=round(btc[b[1]]/btc[a[1]]-1,2),leaders=' > '.join(f"{g}({v:+.0%}, top {pk[g].date()})" for g,v in top)))
mk=pd.DataFrame(mk)
legs.to_csv(f'{OUT}/legs.csv',index=False); S.to_csv(f'{OUT}/signals.csv',index=False); prec.to_csv(f'{OUT}/signal_precision.csv',index=False); mk.to_csv(f'{OUT}/market_legs.csv',index=False)
RS.to_csv(f'{OUT}/rs_daily.csv.gz'); IDX.to_csv(f'{OUT}/idx_daily.csv.gz'); N.to_csv(f'{OUT}/members_daily.csv.gz')
pd.set_option('display.width',250); pd.set_option('display.max_rows',400); pd.set_option('display.max_colwidth',200)
print('dead exits booked -90%:',gone)
print(mk.to_string())
print(legs.groupby('phase').size())
print(legs.to_string())
agg=S[S.lead_days.notna()].groupby('signal').agg(n=('lead_days','size'),lead_med=('lead_days','median'),left_med=('leg_left','median'))
agg['found_of']=S.groupby('signal').size()
print(agg.to_string())
print(prec.groupby('signal')[['fires','med30_vsBTC','hit20','base_med','base_hit20']].median().to_string())