#!/usr/bin/env python3
# ROT A1: build /opt/rot/db/rot.db (coins, daily, sectors, chains_daily) from raw CMC + DefiLlama.
import json, gzip, os, glob, sqlite3, datetime as dt, collections
DB='/opt/rot/db/rot.db'; RAW='/opt/rot/analyst/raw'
SECT=[ # (sector, tags) first match wins
 ('stable',{'stablecoin','usd-stablecoin','fiat-stablecoin','asset-backed-stablecoin','stablecoin-asset-backed','stablecoin-algorithmically-stabilized','algorithmic-stablecoin','eur-stablecoin','tokenized-gold','yield-bearing-stablecoin'}),
 ('wrapped',{'wrapped-tokens','bridged-tokens'}),
 ('stock',{'tokenized-stock','tokenized-etfs','bstocks'}),
 ('exchange',{'centralized-exchange','exchange-based-tokens','cex-token'}),
 ('meme',{'memes','animal-memes','doggone-doggerel','pump-fun-ecosystem','four-meme-ecosystem','political-memes','cat-themed','frog-themed','dog-themed'}),
 ('l1',{'layer-1'}),
 ('ai',{'ai-big-data','ai-agents','generative-ai','ai-applications','defai','artificial-intelligence','ai-agent-launchpad','ai-memes'}),
 ('perps',{'perpetuals','derivatives'}),
 ('privacy',{'privacy-coins','privacy-blockchain'}),
 ('gaming',{'gaming','play-to-earn','metaverse','gamefi'}),
 ('rwa',{'real-world-assets-protocols','real-world-assets'}),
 ('l2',{'layer-2','rollups','zero-knowledge-proofs','scaling','sidechain'}),
 ('defi',{'defi','decentralized-exchange-dex-token','dex','amm','lending-borowing','yield-farming','yield-aggregator','restaking','launchpad','synthetics','asset-management','insurance','prediction-markets'}),
 ('infra',{'oracles','storage','filesharing','interoperability','depin','distributed-computing','iot','cross-chain','data-availability','modular-blockchain','identity','wallet','analytics'}),
 ('l1',{'platform','smart-contracts','pow','mineable','pos','dpos','store-of-value','payments','medium-of-exchange','masternodes','dag','hybrid-pow-pos'}),
]
CHAINTAG=[('sol',{'solana-ecosystem','pump-fun-ecosystem'}),('base',{'base-ecosystem'}),('bnb',{'bnb-chain-ecosystem','binance-smart-chain','four-meme-ecosystem','bnb-chain'}),
 ('ton',{'toncoin-ecosystem'}),('sui',{'sui-ecosystem'}),('hyper',{'hyperevm-ecosystem'}),('tron',{'tron20-ecosystem','tron-ecosystem'}),('avax',{'avalanche-ecosystem'}),
 ('arb',{'arbitrum-ecosystem'}),('eth',{'ethereum-ecosystem'})]

OVR={}
for sec,syms in {
 'l1':'BTC ETH XRP SOL ADA TRX BCH LTC XLM AVAX HBAR DOT ATOM NEAR ICP APT SUI SEI TON GRAM KAS ALGO EGLD XTZ EOS NEO ETC BSV XEM WAVES QTUM ICX ONT ZIL KSM KAVA FTM S CELO MINA FLOW VET IOTA XDC FLR KAIA BERA MON HYPE_NO DOGE_NO LUNA LUNC NANO XNO DGB RVN DCR HNS ROSE ONE CKB CFX CSPR BTG LSK STEEM HIVE ARDR STRAX WAN VTHO TFUEL TIA_NO IP KLAY MIOTA OMNI_NO PI XPL TAIKO_NO M',
 'ai':'TAO FET AGIX OCEAN RNDR RENDER WLD AKT ARKM VIRTUAL AI16Z AIXBT GRASS AIOZ IO NMR CTXC GLM AGI PAAL OLAS PRIME ATH KAITO VVV COOKIE GRIFFAIN ZEREBRO GOAT_NO',
 'defi':'UNI AAVE MKR SKY COMP CRV SUSHI YFI LDO JTO PENDLE ENA ETHFI EIGEN MORPHO JUP RAY CAKE RUNE 1INCH BAL AERO KNC LRC_NO OSMO JOE ORCA FXS CVX SPELL ALPHA BNT KMNO FLUID WLFI SYRUP REZ PUFFER ALCX BADGER AMPL UMA REN ZRX DEXE MET RPL SSV',
 'perps':'HYPE DYDX GMX ASTER DRIFT AVNT LIT PERP SNX GNS VRTX APEX KTA_NO MYX',
 'privacy':'XMR ZEC DASH ARRR ZEN SCRT XVG FIRO BEAM_NO NIGHT ZANO DERO GRIN PIVX OXT_NO',
 'l2':'ARB OP MATIC POL STRK ZK MNT METIS LRC SKL BOBA CELR LINEA BLAST MANTA SCR TAIKO STX IMX_NO OMG CTSI ZKJ',
 'infra':'LINK PYTH BAND API3 GRT FIL AR STORJ SC HNT IOTX THETA ANKR QNT AXL W ZRO WAL TIA JASMY RLC HOT BTT BTTC NKN POKT RNDR_NO MOBILE IOT DIMO GEOD',
 'gaming':'AXS SAND MANA ENJ GALA IMX ILV BEAM MAGIC YGG APE GMT RON PIXEL SUPER NOT_NO PRIME_NO WAXP ALICE TLM SLP CHZ',
 'rwa':'ONDO CFG PLUME POLYX OM TRU MPL',
 'meme':'DOGE SHIB PEPE FLOKI BONK WIF BOME POPCAT MEW TRUMP FARTCOIN PNUT MOODENG GOAT SPX PENGU BRETT MOG NEIRO TURBO DOGS NOT CAT BABYDOGE ELON KISHU SAMO WOJAK',
 'exchange':'BNB OKB LEO CRO BGB GT KCS HT HTX FTT MX WOO'}.items():
    for x in syms.split():
        if not x.endswith('_NO'): OVR[x]=sec
NAMEPEG=('USD','EUR','GOLD','XAU')
def sector(tags,name,sym):
    n=name.lower()
    if n.startswith('wrapped ') or n.startswith('bridged ') or 'staked ' in n or n.endswith(' xstock'): return 'wrapped' if not n.endswith(' xstock') else 'stock'
    for s,T in SECT:
        if tags & T: return s
    return 'other'
def chain(tags):
    for c,T in CHAINTAG:
        if tags & T: return c
    return 'native'
con=sqlite3.connect(DB); con.execute('pragma journal_mode=wal')
con.executescript('''
create table if not exists coins(cmc_id integer primary key, symbol text, name text, slug text, date_added text, tags text, sector text, chain text, first_seen text, last_seen text, best_rank integer, days_top500 integer, first_top500 text, last_top500 text, ever_top500 integer);
create table if not exists daily(date text, cmc_id integer, rank integer, price real, vol real, mcap real, primary key(date,cmc_id)) without rowid;
create table if not exists sectors(sector text primary key, rule text, ranked integer);
create table if not exists chains_daily(date text, chain text, tvl real, stables real, dex_vol real, fees real, primary key(date,chain)) without rowid;
create table if not exists rules(id text primary key, owner text, text text, code_path text, code_md5 text, test_period text, pass_bar text, registered text, status text, receipt text);
create index if not exists daily_id on daily(cmc_id,date);
''')
meta={}; tagu=collections.defaultdict(set); rows=0
files=sorted(glob.glob(f'{RAW}/cmc/*.json.gz'))
have=set(r[0] for r in con.execute('select distinct date from daily'))
for f in files:
    d=os.path.basename(f)[:10]
    L=json.load(gzip.open(f,'rt'))
    batch=[]
    for (i,sym,name,slug,rk,p,v,m,cs,da,tags) in L:
        tagu[i].update(tags or [])
        mm=meta.setdefault(i,{'sym':sym,'name':name,'slug':slug,'da':da,'first':d,'best':rk,'n500':0,'f500':None,'l500':None})
        mm.update(sym=sym,name=name,slug=slug,last=d); mm['best']=min(mm['best'],rk)
        if rk<=500:
            mm['n500']+=1; mm['l500']=d
            if mm['f500'] is None: mm['f500']=d
        if d not in have: batch.append((d,i,rk,p,v,m))
    con.executemany('insert or replace into daily values(?,?,?,?,?,?)',batch); rows+=len(batch)
# data-driven peg check: price tracks USD, EUR, gold or a major (wrapped/staked) on >=85% of listed days within 4%
import statistics
px=collections.defaultdict(dict)
for d,i,p in con.execute('select date,cmc_id,price from daily'):
    px[i][d]=p
REF={'usd':None,'btc':1,'eth':1027,'sol':5426,'bnb':1839,'trx':1958,'avax':5805,'hype':32196,'matic':3890,'ada':2010,'sui':20947,'ton':11419,'eur':None,'gold':4705}
def peg(i):
    P=px[i]
    if len(P)<20: return None
    for k,rid in REF.items():
        if rid==i: continue
        if k=='usd': R={d:1.0 for d in P}
        elif k=='eur': R={d:1.10 for d in P}
        else:
            if rid not in px: continue
            R=px[rid]
        common=[d for d in P if d in R and R[d] and P[d]]
        if len(common)<20: continue
        rat=[P[d]/R[d] for d in common]; m=statistics.median(rat)
        tol=0.06 if k=='eur' else 0.04
        if m>0 and sum(abs(r/m-1)<tol for r in rat)/len(rat)>=0.85 and (k not in('usd','eur') or abs(m-1)<0.15):
            return k
    return None
bestsym={}
for i,mm in meta.items():
    if mm['sym'] not in bestsym or mm['best']<meta[bestsym[mm['sym']]]['best']: bestsym[mm['sym']]=i
con.execute('delete from coins')
for i,mm in meta.items():
    T=tagu[i]
    pg=peg(i)
    con.execute('insert into coins values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',(i,mm['sym'],mm['name'],mm['slug'],mm['da'],','.join(sorted(T)),('stable' if pg in('usd','eur','gold') else 'wrapped' if pg else OVR[mm['sym']] if (bestsym.get(mm['sym'])==i and mm['sym'] in OVR) else sector(T,mm['name'],mm['sym'])),chain(T),mm['first'],mm['last'],mm['best'],mm['n500'],mm['f500'],mm['l500'],1 if mm['n500'] else 0))
con.execute('delete from sectors')
for s,T in SECT: con.execute('insert or ignore into sectors values(?,?,?)',(s,'first match on CMC tags: '+','.join(sorted(T)),0 if s in('stable','wrapped','stock') else 1))
con.execute("insert or ignore into sectors values('other','no tag matched',1)")
# DefiLlama
L=f'{RAW}/llama'; cd=collections.defaultdict(dict)
def ts(x): return dt.datetime.utcfromtimestamp(int(x)).strftime('%Y-%m-%d')
for p in glob.glob(f'{L}/*.json'):
    nm=os.path.basename(p)[:-5]; kind,_,ch=nm.partition('_')
    if nm=="stables_chains": continue
    try: j=json.load(open(p))
    except Exception: continue
    if kind=='tvl' and isinstance(j,list):
        for r in j: cd[(ts(r['date']),ch)]['tvl']=r['tvl']
    elif kind=='stables' and isinstance(j,list):
        for r in j:
            v=(r.get('totalCirculatingUSD') or {}).get('peggedUSD')
            if v is not None: cd[(ts(r['date']),ch)]['stables']=v
    elif kind in('dex','fees') and isinstance(j,dict) and j.get('totalDataChart') is not None:
        for t,v in j['totalDataChart']: cd[(ts(t),ch)]['dex_vol' if kind=='dex' else 'fees']=v
con.execute('delete from chains_daily')
con.executemany('insert into chains_daily values(?,?,?,?,?,?)',[(d,c,v.get('tvl'),v.get('stables'),v.get('dex_vol'),v.get('fees')) for (d,c),v in cd.items()])
con.commit()
q=lambda s: con.execute(s).fetchall()
print('daily rows added',rows,'dates',q('select count(distinct date),min(date),max(date) from daily'))
print('coins',q('select count(*),sum(ever_top500) from coins'))
print('ever-top500 by sector',q('select sector,count(*) from coins where ever_top500 group by 1 order by 2 desc'))
print('chains_daily',q('select chain,count(*),min(date),max(date) from chains_daily group by 1'))