"""Reproduce V2 from the official games.parquet; no external data joins.
Usage: python build-v2-data.py /absolute/path/to/games.parquet
Requires pandas and pyarrow. Output contains aggregates and 2026 scorelines.
"""
import sys, json, hashlib, csv
from pathlib import Path
ROOT=Path(__file__).resolve().parent
for candidate in (ROOT.parent/'.analysis-libs',ROOT.parent.parent/'.analysis-libs'):
    if candidate.exists(): sys.path.insert(0,str(candidate))
import pandas as pd
OUT=Path(sys.argv[2]).resolve() if len(sys.argv)>2 else (ROOT/'dist' if (ROOT/'.openai/hosting.json').exists() else ROOT)
OUT.mkdir(parents=True,exist_ok=True)

source=Path(sys.argv[1])
raw=pd.read_parquet(source)
assert raw.game_id.is_unique, 'Duplicate game identifiers require investigation'
valid=(raw.status_type_name=='STATUS_FINAL') & raw.status_type_completed & (raw.home_score>0) & (raw.away_score>0) & (raw.home_score!=raw.away_score)
final=raw[valid].copy()
regular=final[final.season_type==2].copy()
# ESPN labels some March conference tournament games as season_type=2.
# A consistent November–February window avoids mixing those games into seasons.
months=pd.to_datetime(regular.date,utc=True).dt.month
d=regular[months.isin([11,12,1,2])].copy()
d['margin']=(d.home_score-d.away_score).abs()
conf=d[d.conference_competition]
appearances=pd.concat([conf[['season','home_id']].rename(columns={'home_id':'team'}),conf[['season','away_id']].rename(columns={'away_id':'team'})])
qualified=set(appearances.value_counts()[lambda x:x>=5].index)
d['qualified']=[(s,h) in qualified and (s,a) in qualified for s,h,a in zip(d.season,d.home_id,d.away_id)]
def stats(df):
    h=[0]*(int(d.margin.max())+1)
    for margin,n in df.margin.value_counts().items(): h[int(margin)]=int(n)
    return {'n':len(df),'hist':h,'median':float(df.margin.median()) if len(df) else None}
series={}
windows={}
for scope in ['all','qualified']:
    q=d if scope=='all' else d[d.qualified]
    series[scope]=[{'year':int(year),'conference':stats(g[g.conference_competition]),'nonconference':stats(g[~g.conference_competition])} for year,g in q.groupby('season')]
    windows[scope]={label:{'conference':stats(q[q.season.isin(years)&q.conference_competition]),'nonconference':stats(q[q.season.isin(years)&~q.conference_competition])} for label,years in [('early',[2006,2007,2008]),('late',[2024,2025,2026])]}
latest=d[d.season==2026].sort_values(['game_date','game_id'])
teams={}
for side in ['home','away']:
    for _,row in latest.iterrows():
        tid=int(row[f'{side}_id'])
        teams[str(tid)]={'name':row[f'{side}_location'],'eligible':(2026,tid) in qualified}
games=[[int(r.game_id),str(r.game_date),int(r.home_id),int(r.away_id),int(r.home_score),int(r.away_score),bool(r.conference_competition)] for r in latest.itertuples()]
output={'meta':{'source':'https://uofi.app.box.com/s/vhbl9evt5aoqyiduq4pkgehm5on9cuaq','sourceFile':'games.parquet','sha256':hashlib.sha256(source.read_bytes()).hexdigest(),'rawRows':len(raw),'finalRows':len(final),'sourceRegularRows':len(regular),'regularRows':len(d),'excludedNonFinalOrInvalid':int((~valid).sum()),'excludedOutsideRegular':int(len(final)-len(regular)),'excludedOutsideCalendar':int(len(regular)-len(d)),'startYear':int(d.season.min()),'endYear':int(d.season.max()),'seasonMeaning':'Year in which the season ends','conferenceDefinition':'Source conference_competition flag; November through February games labeled regular season','qualifiedDefinition':'Both teams have at least five recorded conference games in that same season. This is a schedule-based sensitivity check, not an official division classification.'},'series':series,'windows':windows,'teams':teams,'games':games}
for scope in series:
    for y in series[scope]:
        for group in ['conference','nonconference']:
            assert sum(y[group]['hist'])==y[group]['n']
assert len(games)==len(latest)
assert sum(y['conference']['n']+y['nonconference']['n'] for y in series['all'])==len(d)
assert all(g[4]!=g[5] and g[4]>0 and g[5]>0 for g in games)
(OUT/'story-data.json').write_text(json.dumps(output,separators=(',',':'),ensure_ascii=False),encoding='utf8')
rows=[]
for scope in series:
    for year in series[scope]:
        for group in ['conference','nonconference']:
            s=year[group]
            rows.append([scope,year['year'],group,s['n'],s['median'],*[(sum(s['hist'][t:])) for t in (10,20,30)],*[(100*sum(s['hist'][t:])/s['n']) for t in (10,20,30)]])
with (OUT/'season-summary.csv').open('w',newline='',encoding='utf8') as f:
    w=csv.writer(f);w.writerow(['opponent_scope','season_ending_year','game_group','games','median_margin','games_margin_10_plus','games_margin_20_plus','games_margin_30_plus','pct_margin_10_plus','pct_margin_20_plus','pct_margin_30_plus']);w.writerows(rows)
print(json.dumps(output['meta'],indent=2))
for scope in windows:
    for label in windows[scope]:
        print(scope,label,{group:{'n':s['n'],'pct20':round(100*sum(s['hist'][20:])/s['n'],2),'median':s['median']} for group,s in windows[scope][label].items()})