from pathlib import Path
import pandas as pd,numpy as np,json,re,hashlib
from urllib.parse import quote
R=Path(__file__).resolve().parent
g=pd.read_parquet(R.parent/'fable-verification-2026-09-06/games.parquet')
identity=pd.read_parquet(R.parent/'portfolios-2026-09-06/games.parquet')[['appid','credit','credit_norm','creator_page_key','page_names']]
x=g.merge(identity,on='appid',how='inner',validate='one_to_one');x=x[x.valid&~x.free&~x.explicit].copy()
x['developer_id']=x.credit_norm;x=x.sort_values(['developer_id','date','appid']);x['index']=x.groupby('developer_id').cumcount()+1
x['first_date']=x.groupby('developer_id').date.transform('min');x['career_n']=x.groupby('developer_id').appid.transform('size')
x['mature']=x.date.lt(pd.Timestamp('2025-09-01',tz='UTC'))
x['review_band']=pd.cut(x.reviews,[-1,99,999,9999,np.inf],labels=['Q','M','H','B']).astype(str)
x['max_band']=pd.cut(x.reviews,[-1,99,999,2999,9999,29999,np.inf],labels=['<100','100-999','1000-2999','3000-9999','10000-29999','30000+']).astype(str)
x['game_url']=x.appid.map(lambda a:f'https://store.steampowered.com/app/{a}/')
x['developer_search_url']=x.credit.map(lambda n:'https://store.steampowered.com/search/?developer='+quote(n,safe=''))
x['developer_page_url']=[f'https://store.steampowered.com/curator/{key.split(":")[1]}/' if isinstance(key,str) and key.startswith('clan:') and n==1 else search for key,n,search in zip(x.creator_page_key,x.page_names,x.developer_search_url)]
x['developer_link_type']=np.where(x.developer_page_url.eq(x.developer_search_url),'Steam developer-filtered catalog','unshared recorded Steam creator page')
for col in ['reviews','date','appid','name','pub','price','primary','review_band']:
    x['previous_'+col]=x.groupby('developer_id')[col].shift()
x['prior_best']=x.groupby('developer_id').reviews.cummax().groupby(x.developer_id).shift().fillna(0)
x['prior_total']=x.groupby('developer_id').reviews.cumsum().groupby(x.developer_id).shift().fillna(0)
x['gap_days']=(x.date-x.previous_date).dt.total_seconds()/86400
for threshold in [500,1000,2000]:
    x['prior_hits_'+str(threshold)]=x.reviews.ge(threshold).astype(int).groupby(x.developer_id).cumsum().groupby(x.developer_id).shift().fillna(0).astype(int)
x.to_parquet(R/'games.parquet',index=False)
spec={'source_games':len(x),'developer_names':x.developer_id.nunique(),'identity':'Existing audited single-credit name table; no merging by creator-page ID. Ambiguous names and explicit promotional titles excluded by that table. Names are not verified individuals or corporate continuity.','mature_cutoff':'2025-09-01 exclusive','labels':{'Q':'<100 current reviews','M':'100-999','H':'1000-9999','B':'10000+'},'strong_threshold_sensitivities':[500,1000,2000],'scope':'Currently paid games outside descriptors 3/4; dated public catalog only. Current outcome sequences are retrospective, not historical success at subsequent launches.','first_hit_window':'Earliest released game now above threshold, eligible through August2020; outcomes among next included releases in five years. Not the date the game crossed the threshold.','links':'Game URLs by observed app ID; unshared recorded creator pages where available, otherwise official Steam developer-filtered catalogs. Creator pages used for navigation only.','source_sha256':hashlib.sha256((R.parent/'fable-verification-2026-09-06/games.parquet').read_bytes()).hexdigest()}
(R/'study_spec.json').write_text(json.dumps(spec,indent=2));print(json.dumps(spec,indent=2))