"""Recalculate business tables from the supplied source snapshots. Run: python3 deliverables/calculate.py"""
import csv,json,math,statistics
from pathlib import Path
from datetime import date
D=Path(__file__).resolve().parent
read=lambda n:json.loads((D/'evidence'/n).read_text())
sp,po,su=[read(n) for n in ['categorized.json','cycle.json','consolidation.json']]
def required(rows,keys):
    for i,r in enumerate(rows,1):
        for k in keys:
            assert k in r and r[k] is not None and str(r[k]).strip(), f'Record {i}: missing {k}'
def amount(v):
    assert isinstance(v,(int,float)) and not isinstance(v,bool) and math.isfinite(v) and v>=0
required(sp,['supplier','description','annual_spend','currency'])
required(su,['name','category','annual_spend','criticality','switching_cost_estimate','renewal_date','integration_count_with_other_systems'])
required(po,['category','request_date','approval_date','po_issued_date','goods_received_date','payment_date','approver_hops'])
for r in sp:
    amount(r['annual_spend']); amount(r['prior_year_spend']); assert r['currency']=='USD'
for r in su:
    amount(r['annual_spend']);amount(r['switching_cost_estimate']);assert r['criticality'] in ['tier-1','tier-2','tier-3'];date.fromisoformat(r['renewal_date'])
for r in po:
    dates=[date.fromisoformat(r[k]) for k in ['request_date','approval_date','po_issued_date','goods_received_date','payment_date']]
    assert dates==sorted(dates);assert isinstance(r['approver_hops'],int) and r['approver_hops']>=0
assert len({r['supplier'] for r in sp})==len(sp)==len(su)==10
assert {r['supplier']:r['annual_spend'] for r in sp}=={r['name']:r['annual_spend'] for r in su}
# Purchase descriptions, independently reviewed against the built-in taxonomy.
classes={'email marketing':'Email Marketing Platform','expense':'Expense / Spend Management','monitoring':'Monitoring / Observability','legal services':'Outside Counsel','aws ec2':'AWS','advertising':'Paid Media'}
by={}
for r in sp:
    c=classes[r['description']];assert c==next(s['category'] for s in su if s['name']==r['supplier'])
    b=by.setdefault(c,{'category':c,'spend_2026':0,'spend_2025':0,'suppliers':0})
    b['spend_2026']+=r['annual_spend'];b['spend_2025']+=r['prior_year_spend'];b['suppliers']+=1
T=sum(r['annual_spend'] for r in sp);P=sum(r['prior_year_spend'] for r in sp)
rows=sorted(by.values(),key=lambda r:(-r['spend_2026'],r['category']));cum=0
for r in rows:
    cum+=r['spend_2026'];r.update(delta=r['spend_2026']-r['spend_2025'],yoy_pct=100*(r['spend_2026']/r['spend_2025']-1),spend_share_pct=100*r['spend_2026']/T,cumulative_share_pct=100*cum/T)
def write(n,rs):
    with (D/n).open('w',newline='') as f:
        w=csv.DictWriter(f,fieldnames=list(rs[0]));w.writeheader();w.writerows(rs)
write('category_calculations.csv',rows)
def p90(v):
    v=sorted(v);k=(len(v)-1)*.9;l=int(k);return v[l]+(v[min(l+1,len(v)-1)]-v[l])*(k-l)
cycles=[]
for c in sorted({r['category'] for r in po}):
    rs=[r for r in po if r['category']==c]
    pairs={'request_approval':('request_date','approval_date'),'approval_po':('approval_date','po_issued_date'),'request_po':('request_date','po_issued_date'),'po_payment':('po_issued_date','payment_date')}
    b={'category':c,'n':len(rs)}
    for label,(a,z) in pairs.items():
        v=[(date.fromisoformat(r[z])-date.fromisoformat(r[a])).days for r in rs];b[label+'_median_days']=statistics.median(v);b[label+'_p90_days']=p90(v)
    b['median_hops']=statistics.median(r['approver_hops'] for r in rs);cycles.append(b)
baseline=statistics.median(r['request_po_median_days'] for r in cycles)
for r in cycles:r['bottleneck']=r['request_po_median_days']>2*baseline
write('cycle_calculations.csv',cycles)
choices=[]
for c in sorted({r['category'] for r in su}):
    members=[r for r in su if r['category']==c]
    if len(members)<2:continue
    for winner in members:
        losers=[r for r in members if r!=winner];gross=sum(r['annual_spend'] for r in losers);migration=sum(r['switching_cost_estimate'] for r in losers)
        blocked=any(r['criticality']=='tier-1' for r in members) and not all(r['break_glass_documented'] for r in members)
        choices.append(dict(category=c,retain=winner['name'],exit='; '.join(r['name'] for r in losers),current_spend=sum(r['annual_spend'] for r in members),retained_spend=winner['annual_spend'],gross_annual_savings=gross,migration_and_implementation=migration,net_first_year_savings=gross-migration,blocked_tier1=blocked,below_10000_net=gross-migration<10000))
write('consolidation_choices.csv',choices)
renewals=[]
for r in sorted(su,key=lambda r:r['renewal_date']):
    renewals.append(dict(supplier=r['name'],category=r['category'],renewal_date=r['renewal_date'],annual_spend=r['annual_spend'],action='Exit after acceptance' if r['name'] in ['HarborMail B','HarborMail C'] else 'Renew subject to review',notice_deadline='Unknown: verify contract',owner='Alex'))
write('renewal_register.csv',renewals)
summary=dict(current_spend=T,prior_spend=P,growth=T-P,growth_pct=100*(T/P-1),cross_category_median_days=baseline,bottleneck_threshold_days=2*baseline,po_spend=T*.9,card_spend=T*.1,unapproved_subscription_spend=T*.03,base_gross_savings=18000,base_migration_implementation_cost=2000,base_net_first_year_savings=16000,base_recurring_spend=82000,base_first_year_total=84000)
assert T==100000 and P==69000 and baseline==6
assert len([r for r in cycles if r['bottleneck']])==1
assert next(r for r in choices if r['retain']=='HarborMail A')['net_first_year_savings']==16000
(D/'summary.json').write_text(json.dumps(summary,indent=2)+'\n')
(D/'verification.md').write_text('''# Calculation verification

All supplied records passed explicit required-field, nonnegative finite amount, currency, criticality, date and chronological-order checks. Ten unique suppliers reconcile one-to-one across spend and consolidation inputs, including category and annual amount. All ten renewal dates are present. Ten PO records span five categories; Paid Media has no cycle observations.

Independent purchase-based calculations agree with the three Skill outputs on category totals, YoY rates, cycle medians and interpolated P90s, proposed survivors and first-year cost subtraction. Total spend is $100,000 versus $69,000; only Outside Counsel exceeds the 12-day threshold. Base email savings reconcile to $18,000 gross minus $2,000 combined migration and implementation, leaving $16,000 net.

Decision safeguards applied beyond generated output:

- The categorizer combines supplier name, description and hint into one search string; it does not enforce description precedence. Each supplied purchase was manually matched to the intended class. The six classes here match the output.
- The categorizer floors six categories / five to one category for its “top 20%” display. That category covers 36%; four categories are required to reach 81%.
- Growth contribution is ranked by dollar increase in result.md; the generated category report ranks percentage growth.
- Winner selection uses integration counts for non-tier-1 clusters. Alternatives are calculated explicitly rather than equating that heuristic with optimal cost.
- The consolidation script tests the survivor's break-glass flag only. The intake template requires all cluster members to have documented plans. This review follows the stricter condition; all monitoring flags are false and no monitoring exit is authorized.
- The script applies its $10,000 low-savings cutoff to gross annual savings despite describing a net-first-year cutoff. This review uses net first-year savings. Supplied decisions are unchanged: email $16,000 qualifies, expense $2,500 is deferred, monitoring is blocked.
- “Offboard” names in the generated plan are candidate exits only; blocked and deferred rows are not cancellation instructions.
- Sole-source cloud is outside duplicate-cluster detection and is reviewed separately in result.md.

No separate alternative or malformed datasets were supplied. Intake references to those conditions are not measured results. Missing criticality or switching costs, invalid dates, or reversed event order must be rejected before future decisions. Missing prior-year values must remain unavailable rather than defaulting to zero.
''')
print('Verified tables and summary generated.')