from pathlib import Path
import pandas as pd,numpy as np,json,math
R=Path(__file__).resolve().parent;p=pd.read_parquet(R/'profile_sample.parquet');spec=json.loads((R/'profile_spec.json').read_text());rows=[];recon=[]
for x in p.itertuples():
    counts={}
    for lang in spec['languages']:
        f=R/'api_summaries'/f'{x.appid}_{lang}.json'
        if not f.exists():raise RuntimeError('Incomplete profile: '+str(f))
        counts[lang]=json.loads(f.read_text())['query_summary']
    total=counts['all']['total_reviews'];pos=counts['all']['total_positive'];sumlang=sum(q['total_reviews'] for lang,q in counts.items() if lang!='all');sumpos=sum(q['total_positive'] for lang,q in counts.items() if lang!='all')
    residual=total-sumlang;residualpos=pos-sumpos
    recon.append(dict(appid=x.appid,name=x.name,total=total,sum_measured_languages=sumlang,residual_count=residual,residual_positive=residualpos,residual_share=residual/total if total else None,store_total=x.total,store_english=x.english_count,api_english=counts['english']['total_reviews']))
    for lang,q in counts.items():
        n=q['total_reviews'];up=q['total_positive'];rows.append(dict(appid=x.appid,name=x.name,selection_bin=x.share_bin,language=lang,count=n,positive=up,negative=q['total_negative'],share=n/total if total else None,positive_pct=100*up/n if n else None,english_support=x.english_support))
    rows.append(dict(appid=x.appid,name=x.name,selection_bin=x.share_bin,language='other_unmeasured',count=residual,positive=residualpos,negative=residual-residualpos,share=residual/total if total else None,positive_pct=100*residualpos/residual if residual>0 else None,english_support=x.english_support))
d=pd.DataFrame(rows);d.to_csv(R/'language_profiles.csv',index=False);pd.DataFrame(recon).to_csv(R/'profile_reconciliation.csv',index=False)
langs=pd.read_parquet(R.parent/'2026-09-05/exports/languages.parquet');mapping={'english':0,'german':1,'french':2,'koreana':4,'spanish':5,'schinese':6,'tchinese':7,'russian':8,'japanese':10,'brazilian':22,'latam':27}
support={(r.appid,r.language_id) for r in langs[langs.supported.eq(1)].itertuples()}
comparisons=[]
for a,group in d.groupby('appid'):
    eng=group[group.language.eq('english')].iloc[0]
    for row in group.itertuples():
        if row.language in ['all','english','other_unmeasured']:continue
        if min(eng['count'],row.count)<50:continue
        pe=eng.positive/eng['count'];pl=row.positive/row.count
        gap=100*(pl-pe);se=100*math.sqrt(pe*(1-pe)/eng['count']+pl*(1-pl)/row.count)
        comparisons.append(dict(appid=a,name=row.name,language=row.language,english_count=int(eng['count']),other_count=row.count,english_positive_pct=100*pe,other_positive_pct=100*pl,difference_pp=gap,lo95=gap-1.96*se,hi95=gap+1.96*se,english_support=(a,0) in support,other_language_support=(a,mapping[row.language]) in support))
pd.DataFrame(comparisons).to_csv(R/'language_reception_comparisons.csv',index=False)
print('Profiles',len(p),'comparisons with >=50 reviews per language',len(comparisons),'max residual',max(z['residual_share'] for z in recon))