"""Reproducible constructed dispatch and case-message analysis; pandas 3.0 tested."""
from pathlib import Path
from hashlib import sha256
import pandas as pd

HERE=Path(__file__).resolve().parent
source=HERE/'dispatches.csv'
raw=source.read_bytes()
d=pd.read_csv(source,dtype={'Batch':'string','Region':'string'},parse_dates=['Date'])
required={'Batch','Date','Region','Orders','Late'}
assert set(d.columns)==required
assert d['Batch'].is_unique and not d['Batch'].isna().any()
assert (d['Orders']>0).all() and d['Orders'].notna().all()
assert ((d['Late']>=0)&(d['Late']<=d['Orders'])).all()
assert len(d)==6
d['LateRate']=d['Late']/d['Orders']
pooled=d['Late'].sum()/d['Orders'].sum()
batch_mean=d['LateRate'].mean()
assert abs(pooled-.21)<1e-12 and abs(batch_mean-.15)<1e-12
regional=d.groupby('Region',as_index=False,sort=True)[['Orders','Late']].sum()
assert regional.to_dict('records')==[{'Region':'North','Orders':45,'Late':8},{'Region':'South','Orders':55,'Late':13}]

cases=pd.DataFrame({'case_id':['A','B','C','D'],'resolved':pd.Series([1,0,1,pd.NA],dtype='Int64')})
messages=pd.DataFrame({'message_id':['A1','A2','A3','B1','C1','C2'],'case_id':['A','A','A','B','C','C']})
raw_join=cases.merge(messages,on='case_id',how='left',validate='one_to_many',indicator=True)
assert len(raw_join)==7 and (raw_join['_merge']=='left_only').sum()==1
assert raw_join['resolved'].notna().sum()==6
message_counts=messages.groupby('case_id',as_index=False).size().rename(columns={'size':'n_messages'})
case_view=cases.merge(message_counts,on='case_id',how='left',validate='one_to_one',indicator=True)
case_view['n_messages']=case_view['n_messages'].fillna(0).astype('int64')
assert len(case_view)==4 and case_view['resolved'].notna().sum()==3
assert case_view['n_messages'].tolist()==[3,1,2,0]

print('INPUT SHA-256',sha256(raw).hexdigest())
print('DISPATCH ROWS',len(d),'UNIQUE BATCHES',d['Batch'].nunique())
print('POOLED RATE',f'{pooled:.2%}','BATCH MEAN',f'{batch_mean:.2%}')
print('REGION TOTALS',regional.to_dict('records'))
print('RAW LEFT-JOIN ROWS',len(raw_join),'UNMATCHED CASES',int((raw_join['_merge']=='left_only').sum()))
print('CASE-GRAIN ROWS',len(case_view),'KNOWN OUTCOMES',int(case_view['resolved'].notna().sum()))
print('PYTHON/PANDAS CHECKS PASS')
