from common import *
import json
g=pd.read_parquet(R/'games.parquet');targets=pd.read_parquet(R/'sample.parquet');rows=[];coverage=[]
for a in targets.itertuples():
    path=R/'completed'/f'{a.appid}.parquet'
    if not path.exists():raise SystemExit('Historical collection not yet complete')
    x=pd.read_parquet(path);x['source']='new_six_catalogs';rows.append(x)
old=R.parent/'career-history-2026-09-06';ot=pd.read_parquet(old/'targets.parquet');cached=pd.read_parquet(old/'reviews.parquet');cached=cached[cached.appid.isin(ot.loc[ot.is_free.eq(0),'appid'])].copy()
cols=['appid','recommendationid','timestamp_created','timestamp_updated','steam_purchase','written_during_early_access']
cached=cached[cols];cached['source']='prior_bounded_career_study';rows.append(cached)
r=pd.concat(rows,ignore_index=True).drop_duplicates(['appid','recommendationid']);r=r[r.appid.isin(g.appid)].copy();r['created']=pd.to_datetime(r.timestamp_created,unit='s',utc=True)
r.to_parquet(R/'historical_reviews.parquet',index=False)
hist=[];end=pd.Timestamp('2026-09-01',tz='UTC')
for a,x in r.groupby('appid'):
    game=g[g.appid.eq(a)].iloc[0];date=game.date;first=x.created.min();offset=(first-date).total_seconds()/86400;valid=-7<=offset<=30
    q=dict(appid=a,name=game['name'],developer_id=game.developer_id,credit=game.credit,date=date,first_review=first,first_offset_days=offset,date_consistent=valid,post2014_clock=valid and date>=pd.Timestamp('2014-01-01',tz='UTC'),current_surviving=len(x),game_url=game.game_url,developer_url=game.developer_page_url)
    for clock,origin in [('release',date),('firstreview',first)]:
        for days in [30,90,365]:q[f'{clock}_{days}']=int(x.created.lt(origin+pd.Timedelta(days=days)).sum()) if origin+pd.Timedelta(days=days)<=end and (clock!='release' or valid) else np.nan
    hist.append(q)
h=pd.DataFrame(hist).sort_values(['developer_id','date']);h.to_csv(R/'historical_game_windows.csv',index=False)
trans=[];catalog=[]
for dev,a in h.groupby('developer_id'):
    known=g[g.developer_id.eq(dev)];complete=set(known.appid)==set(a.appid)
    coverage.append(dict(developer_id=dev,credit=a.credit.iloc[0],known_paid_games=len(known),history_games=len(a),complete_current_paid_catalog=complete))
    if not complete:continue
    a=a.sort_values('date');prior=[]
    for cur in a.to_dict('records'):
        if prior:
            ids=[p['appid'] for p in prior];before=r[r.appid.isin(ids)&r.created.lt(cur['date'])];c=before.groupby('appid').size().reindex(ids,fill_value=0)
            current=g.set_index('appid').loc[ids,'reviews']
            trans.append(dict(developer_id=dev,credit=cur['credit'],next_appid=cur['appid'],next_name=cur['name'],date=cur['date'],next_game_url=cur['game_url'],developer_url=cur['developer_url'],prior_now_best=int(current.max()),prior_then_best=int(c.max()),prior_now_hits=int(current.ge(1000).sum()),prior_then_hits=int(c.ge(1000).sum()),prior_then_total=int(c.sum()),next90=cur['release_90'],next365=cur['release_365'],clock_valid=cur['date_consistent']))
        prior.append(cur)
    for clock,field in [('current','current_surviving'),('day365','release_365')]:
        # Only uniformly post-2014, date-consistent mature games for the equal-age
        # sequence. Keep whole-current-catalog sequences separately.
        b=a if clock=='current' else a[a.post2014_clock&a[field].notna()]
        values=b[field].to_numpy();labels=['Q' if v<100 else 'M' if v<1000 else 'H' if v<10000 else 'B' for v in values]
        catalog.append(dict(developer_id=dev,credit=a.credit.iloc[0],clock=clock,n=len(b),sequence=' '.join(labels),values=';'.join(str(int(v)) for v in values),appids=';'.join(map(str,b.appid)),developer_url=a.developer_url.iloc[0]))
save(trans,'historical_prior_states.csv');save(catalog,'historical_sequences.csv');save(coverage,'history_coverage.csv')
print('Historical games',len(h),'review records',len(r),'complete catalogs',sum(x['complete_current_paid_catalog'] for x in coverage),'transitions',len(trans))