from design import *
import numpy as np
g=pd.read_parquet(R/'classified.parquet');g['day']=g.first_date.map(lambda d:d.timestamp()/86400)
main=pd.read_csv(R/'host_results.csv');matched=pd.read_csv(R/'matched_results.csv');sens=pd.read_csv(R/'sensitivity.csv')
out=main.merge(matched[matched.variant.eq('all')][['period','host','supported_n','support','observed100','expected100','ratio100']].rename(columns={'expected100':'matched_expected100','ratio100':'matched_ratio100'}),on=['period','host'],validate='one_to_one')
out['b_rate100']=out.b_ge100/out.b_n.where(out.b_n.gt(0));out['c_rate100']=out.c_ge100/out.c_n.where(out.c_n.gt(0))
text=pd.read_csv(R/'matched_text_length.csv')
out=out.merge(text[['period','host','supported_n','ratio100']].rename(columns={'supported_n':'text_matched_n','ratio100':'text_matched_ratio100'}),on=['period','host'])
out.to_csv(R/'overview.csv',index=False)
screen=pd.read_csv(R/'candidate_diagnostics.csv')
screen['final_pass']=screen[['enough_host','sparse','enough_build','repeated_response','enough_creators']].all(axis=1)&screen.support.ge(.8)&screen.matched_ratio100.ge(1.5)&screen.observed100.ge(5)
screen.to_csv(R/'final_sparse_screen.csv',index=False)

def nearest_stats(pool):
 b=pool[pool.strict];c=pool[~pool.strict][['appid','developer_id','price_bucket','day','reviews']]
 n=obs=0;exp=0.
 for a in b[['appid','developer_id','price_bucket','day','reviews']].itertuples():
  z=c[c.price_bucket.fillna('missing').eq(a.price_bucket if pd.notna(a.price_bucket) else 'missing')&c.developer_id.ne(a.developer_id)&c.day.sub(a.day).abs().le(90)].copy()
  z['distance']=z.day.sub(a.day).abs();z=z.sort_values(['distance','appid']).head(5)
  if len(z)<3:continue
  n+=1;obs+=int(a.reviews>=100);exp+=float(z.reviews.ge(100).mean())
 return dict(build_n=len(b),build_ge100=int(b.reviews.ge(100).sum()),supported_n=n,observed100=obs,expected100=exp,ratio100=obs/exp if exp else None)
rem=[]
for host in ['Sports','City/colony building','Board/dice/tabletop','First-person shooting','Top-down/arena shooting']:
 pool=g[g.period.eq('2023_2025')&g['host10_'+host]&g['text_'+host]];b=pool[pool.strict]
 for field in ['developer_id','publisher_id']:
  key=b.groupby(field).reviews.sum().idxmax();z=pool[pool[field].ne(key)]
  rem.append(dict(host=host,removal=field,removed=key,**nearest_stats(z)))
 ids=set(b.nlargest(3,'reviews').appid)
 rem.append(dict(host=host,removal='top3_build_games',removed='|'.join(map(str,sorted(ids))),**nearest_stats(pool[~pool.appid.isin(ids)])))
pd.DataFrame(rem).to_csv(R/'matched_removals.csv',index=False)
allmatches=pd.read_csv(R/'matched_games.csv')
prices=g.set_index('appid').usd_list_price
known=allmatches[allmatches.appid.map(prices).notna()].copy();known['y']=known.reviews.ge(100)
known=known.groupby(['period','host','variant']).agg(supported_n=('appid','size'),observed100=('y','sum'),expected100=('expected100','sum')).reset_index()
known['ratio100']=known.observed100/known.expected100.where(known.expected100.gt(0))
known.to_csv(R/'known_price_sensitivity.csv',index=False)

# An outcome table of every sports candidate, including quiet titles.
m=pd.read_parquet(R/'build_members.parquet');m[m.host.eq('Sports')].to_csv(R/'sports_all_cases.csv',index=False)
print('FINAL SPARSE PASSES\n',screen[screen.final_pass][['host','b_n','b_ge100','responding_developers','matched_ratio100']].round(3).to_string(index=False))
print('MATCHED REMOVALS\n',pd.DataFrame(rem).round(3).to_string(index=False))