import pandas as pd, numpy as np, re

# ---------- 1. WGI wide -> long ----------
def parse_wgi(path, name):
    raw = pd.read_csv(path, header=None, dtype=str, encoding='latin-1')
    years = raw.iloc[0].tolist()
    sub   = raw.iloc[1].tolist()
    recs=[]
    for r in range(2, len(raw)):
        row=raw.iloc[r]
        code=row[1]
        if not isinstance(code,str) or len(code)!=3: continue
        for c in range(2,len(row)):
            if sub[c]=='Estimate':
                yr=years[c]
                try: y=int(float(yr))
                except: continue
                v=row[c]
                try: val=float(v)
                except: continue
                recs.append((code.strip().upper(), y, val))
    return pd.DataFrame(recs, columns=['code','year',name])

rle = parse_wgi('/tmp/wgirepo/Rule of Law.csv','rle')
gee = parse_wgi('/tmp/wgirepo/Gov Effectiveness.csv','gee')
print("WGI RoL", rle.shape, "years", sorted(rle.year.unique()))

# ---------- 2. V-Dem polyarchy ----------
import pyreadr
vd = pyreadr.read_r('/tmp/demdata/data/vdem_simple.rda')['vdem_simple']
vd = vd[['country_text_id','year','v2x_polyarchy','v2x_libdem']].copy()
vd['code']=vd['country_text_id'].str.upper()
vd['year']=vd['year'].astype(int)
vd=vd.rename(columns={'v2x_polyarchy':'polyarchy','v2x_libdem':'libdem'})
vd=vd[['code','year','polyarchy','libdem']]

# ---------- 3. WDI GDP pc PPP + tax ----------
def wdi(ind, name):
    df=pd.read_csv(f'/tmp/wdi/indicators/{ind}/data.csv')
    df.columns=[c.strip() for c in df.columns]
    df=df.rename(columns={'Country Code':'code','Year':'year','Value':name})
    df['code']=df['code'].str.upper(); df['year']=df['year'].astype(int)
    return df[['code','year',name]]
gdp=wdi('ny.gdp.pcap.pp.cd','gdppc')
tax=wdi('gc.tax.totl.gd.zs','tax')

# ---------- 4. merge ----------
m=rle.merge(gee,on=['code','year'],how='outer').merge(vd,on=['code','year'],how='left')
m=m.merge(gdp,on=['code','year'],how='left').merge(tax,on=['code','year'],how='left')
m['log_gdppc']=np.log(m['gdppc'])
panel=m.dropna(subset=['rle','polyarchy','log_gdppc']).copy()
print("merged analysis panel:", panel.shape, "countries", panel.code.nunique(), "years", panel.year.min(),"-",panel.year.max())
panel.to_csv('/tmp/rol_panel.csv',index=False)

# standardize for beta comparison
for c in ['rle','gee','polyarchy','log_gdppc','libdem']:
    panel[c+'_z']=(panel[c]-panel[c].mean())/panel[c].std()
panel.to_csv('/tmp/rol_panel.csv',index=False)
print("corr(rle,polyarchy)=%.3f  corr(rle,log_gdppc)=%.3f  corr(polyarchy,log_gdppc)=%.3f"%(
    panel.rle.corr(panel.polyarchy), panel.rle.corr(panel.log_gdppc), panel.polyarchy.corr(panel.log_gdppc)))
