#!/usr/bin/env python3
"""Rebuild this dated cargo-theft compilation using only the adjacent checked inputs.

Python 3.10+; standard library only; no network, third-party packages or private data.
Run: python build_dataset.py --output-dir ./rebuilt
Unknown source values stay null (JSON) or empty (CSV). Calculated shares use unrounded
inputs. Reproduction verifies arithmetic, not the underlying incident databases.
"""
from __future__ import annotations
import argparse
import csv
import json
import re
from collections import defaultdict
from datetime import date
from pathlib import Path
from typing import Any


def build(input_path: Path, out: Path) -> dict[str, Any]:
    data = json.loads(input_path.read_text(encoding='utf-8'))
    out.mkdir(parents=True, exist_ok=True)
    sources = {r['source_id']: r for r in data['sources']}
    checked = data['metadata']['verification_date']
    def enrich(row: dict[str, Any]) -> dict[str, Any]:
        r = dict(row)
        sid = r.get('source_id', '')
        if sid:
            r['source_url'] = sources[sid]['url']
        r['verified_on'] = checked
        return r
    def csv_file(name: str, rows: list[dict[str, Any]]) -> None:
        if not rows:
            raise ValueError(f'No records for {name}')
        keys = list(dict.fromkeys(k for r in rows for k in r))
        with (out/name).open('w', encoding='utf-8', newline='') as f:
            w = csv.DictWriter(f, fieldnames=keys)
            w.writeheader()
            w.writerows(rows)
    def pct(n: int | float, d: int | float) -> float | None:
        return n / d * 100 if d else None
    ns = data['national']; ss = data['state_rows']; ls = data['location_rows']
    byyear = {r['year']: r for r in ns}
    total_inc = sum(r['incidents'] for r in ns)
    total_stolen = sum(r['stolen_usd'] for r in ns)
    total_rec = sum(r['recovered_usd'] for r in ns)
    assert (total_inc, total_stolen, total_rec) == (4162, 293806640, 40903863), 'National source totals do not match the checked release.'
    assert len(ss) == 130 and len({r['jurisdiction'] for r in ss}) == 37, 'Expected 130 rows across 36 states and Guam.'
    state_rows = []
    for r in ss:
        a = enrich(r)
        a.update(stolen_value_unknown=r['stolen_usd'] is None,
                 recovered_value_unknown=r['recovered_usd'] is None,
                 recovered_value_share_pct=pct(r['recovered_usd'], r['stolen_usd']) if r['stolen_usd'] is not None else None,
                 share_of_year_incidents_pct=pct(r['incidents'], byyear[r['year']]['incidents']),
                 share_of_year_stolen_value_pct=pct(r['stolen_usd'], byyear[r['year']]['stolen_usd']) if r['stolen_usd'] is not None else None)
        state_rows.append(a)
    national = []
    for r in ns:
        rows = [x for x in ss if x['year'] == r['year']]
        national.append(enrich(r) | {'recovered_value_share_pct':pct(r['recovered_usd'],r['stolen_usd']),
            'actual_state_rows':sum(x['jurisdiction_type']=='state' for x in rows),
            'actual_territory_rows':sum(x['jurisdiction_type']=='territory' for x in rows),
            'actual_jurisdiction_rows':len(rows),
            'participation_note':'Source chart labels its count states; the 2015 table includes 18 states plus Guam.' if r['year']==2015 else ''})
    pooled = []
    for name in sorted({r['jurisdiction'] for r in ss}):
        rows = [r for r in ss if r['jurisdiction']==name]
        years = sorted(r['year'] for r in rows)
        missing = sum(r['stolen_usd'] is None for r in rows)
        stolen = sum(r['stolen_usd'] for r in rows if r['stolen_usd'] is not None)
        recovered = sum(r['recovered_usd'] for r in rows if r['recovered_usd'] is not None)
        inc = sum(r['incidents'] for r in rows)
        pooled.append({'jurisdiction':name,'jurisdiction_type':rows[0]['jurisdiction_type'],
            'included_years':';'.join(map(str,years)),'year_count':len(years),'all_seven_years':len(years)==7,
            'incidents':inc,'known_stolen_value_sum_usd':stolen,'known_recovered_value_sum_usd':recovered,
            'unknown_stolen_value_years':missing,'stolen_values_complete':not missing,
            'share_of_compiled_incidents_pct':pct(inc,total_inc),
            'share_of_compiled_stolen_value_pct':pct(stolen,total_stolen) if not missing else None,
            'recovered_value_share_pct':pct(recovered,stolen) if not missing else None,
            'min_agencies_in_included_years':min(r['agencies_reporting_incident'] for r in rows),
            'max_agencies_in_included_years':max(r['agencies_reporting_incident'] for r in rows),
            'source_ids':';'.join(r['source_id'] for r in rows),
            'source_urls':';'.join(sources[r['source_id']]['url'] for r in rows),'verified_on':checked})
    pooled.sort(key=lambda r:r['known_stolen_value_sum_usd'],reverse=True)
    reconciliation = []
    for n in ns:
        rs = [r for r in ss if r['year']==n['year']]
        for metric in ['agencies_reporting_incident','incidents','stolen_usd','recovered_usd']:
            unknown = sum(r[metric] is None for r in rs)
            known_sum = sum(r[metric] for r in rs if r[metric] is not None)
            reconciliation.append(enrich({'year':n['year'],'metric':metric,'printed_national_total':n[metric],
                'sum_of_known_jurisdiction_rows':known_sum,'unknown_jurisdiction_values':unknown,
                'known_row_sum_minus_printed_total':known_sum-n[metric],
                'source_id':n['source_id'],
                'interpretation':'Published values retained. An unknown value or difference does not establish its cause.'}))
    location_totals = {y:sum(r['location_entries'] for r in ls if r['year']==y) for y in range(2014,2020)}
    assert location_totals == {2014:580,2015:647,2016:714,2017:761,2018:694,2019:772}
    total_locations = sum(location_totals.values())
    locations = [enrich(r)|{'record_type':'annual','denominator_location_entries':location_totals[r['year']],
                    'share_pct':pct(r['location_entries'],location_totals[r['year']])} for r in ls]
    for name in sorted({r['harmonized_label'] for r in ls}):
        rows = [r for r in ls if r['harmonized_label']==name]
        v = sum(r['location_entries'] for r in rows)
        sids = list(dict.fromkeys(r['source_id'] for r in rows))
        locations.append({'year':'2014–2019','source_label':' | '.join(dict.fromkeys(r['source_label'] for r in rows)),
            'harmonized_label':name,'location_entries':v,'record_type':'pooled',
            'denominator_location_entries':total_locations,'share_pct':pct(v,total_locations),
            'count_unit':'offense-location entries, not unique incidents','source_ids':';'.join(sids),
            'source_urls':';'.join(sources[sid]['url'] for sid in sids),'verified_on':checked})
    assert max((r for r in locations if r['record_type']=='pooled'),key=lambda r:r['location_entries'])['location_entries']==1558
    observations = [enrich(r) for r in data['provider_observations']]
    # Every original calculation below exposes its input values, expression and source IDs.
    derived = []
    def D(fid: str, label: str, value: int | float, unit: str, expression: str,
          inputs: dict[str, Any], sids: list[str], note: str='Compilation calculation, not source-reported measurement') -> None:
        sids=list(dict.fromkeys(sids))
        derived.append({'finding_id':fid,'finding':label,'value':value,'unit':unit,'expression':expression,
            'inputs_json':json.dumps(inputs,sort_keys=True),'source_ids':';'.join(sids),
            'source_urls':';'.join(sources[s]['url'] for s in sids),'interpretation':note,'verified_on':checked})
    allids=[r['source_id'] for r in ns]
    for m,v,u in [('incidents',total_inc,'incidents'),('stolen_usd',total_stolen,'USD'),('recovered_usd',total_rec,'USD')]:
        D('FBI-total-'+m,'2013–2019 sum of printed national '+m,v,u,'sum(yearly_values)',
          {str(r['year']):r[m] for r in ns},allids)
    D('FBI-pooled-recovery','Pooled recovered-value share',pct(total_rec,total_stolen),'percent','recovered / stolen * 100',{'recovered':total_rec,'stolen':total_stolen},allids)
    for r in national:
        D(f"FBI-{r['year']}-recovery",'Annual recovered-value share',r['recovered_value_share_pct'],'percent',
          'recovered / stolen * 100',{'recovered':r['recovered_usd'],'stolen':r['stolen_usd']},[r['source_id']])
    for r in pooled:
        for k in ['share_of_compiled_incidents_pct','share_of_compiled_stolen_value_pct','recovered_value_share_pct']:
            if r[k] is None:continue
            if k=='share_of_compiled_incidents_pct':a,b=r['incidents'],total_inc
            elif k=='share_of_compiled_stolen_value_pct':a,b=r['known_stolen_value_sum_usd'],total_stolen
            else:a,b=r['known_recovered_value_sum_usd'],r['known_stolen_value_sum_usd']
            D('FBI-pooled-'+r['jurisdiction']+'-'+k,r['jurisdiction']+' '+k,r[k],'percent','numerator / denominator * 100',{'numerator':a,'denominator':b},r['source_ids'].split(';'))
    recordings = next(r['stolen_usd'] for r in data['selected_property_rows'] if r['year']==2019)
    n19=byyear[2019];fl19=next(r for r in ss if r['year']==2019 and r['jurisdiction']=='Florida')
    other_stolen=n19['stolen_usd']-recordings
    nonfl=n19['stolen_usd']-fl19['stolen_usd']
    lower=recordings-nonfl
    calculations=[
        ('recordings-share','2019 Recordings share of national stolen value',pct(recordings,n19['stolen_usd']),'percent','recordings / national * 100',{'recordings':recordings,'national':n19['stolen_usd']}),
        ('florida-2019-share','Florida share of 2019 national stolen value',pct(fl19['stolen_usd'],n19['stolen_usd']),'percent','florida / national * 100',{'florida':fl19['stolen_usd'],'national':n19['stolen_usd']}),
        ('2019-other-property','2019 stolen value excluding Recordings',other_stolen,'USD','national - recordings',{'national':n19['stolen_usd'],'recordings':recordings}),
        ('2019-nonflorida','2019 non-Florida reported stolen value',nonfl,'USD','national - florida',{'national':n19['stolen_usd'],'florida':fl19['stolen_usd']}),
        ('2019-florida-lower-bound','Conditional Florida Recordings lower bound',lower,'USD','recordings - (national - florida)',{'recordings':recordings,'national':n19['stolen_usd'],'florida':fl19['stolen_usd']}),
        ('2019-florida-lower-bound-share','Conditional lower bound share of Recordings',pct(lower,recordings),'percent','lower_bound / recordings * 100',{'lower_bound':lower,'recordings':recordings}),
        ('2019-exclusion-recovery','2019 recovered-value share excluding Recordings',pct(n19['recovered_usd'],other_stolen),'percent','recovered / (stolen - recordings) * 100',{'recovered':n19['recovered_usd'],'stolen':n19['stolen_usd'],'recordings':recordings}),
        ('pooled-exclusion-recovery','Pooled recovered-value share excluding 2019 Recordings',pct(total_rec,total_stolen-recordings),'percent','recovered / (stolen - recordings) * 100',{'recovered':total_rec,'stolen':total_stolen,'recordings':recordings})]
    for fid,label,v,unit,expr,ins in calculations:
        note='Exclusion sensitivity, not a corrected official total.' if 'exclusion' in fid else ('Conditional on a consistent state/property accounting universe; not an observed cross-tabulated cell.' if 'lower-bound' in fid else 'Compilation calculation from published aggregate tables.')
        D('FBI-'+fid,label,v,unit,expr,ins,allids if 'pooled' in fid else ['S04'],note)
    six=sum(r['stolen_usd'] for r in ns if r['year']<2019)
    for state in ['Florida','Texas']:
        val=sum(r['stolen_usd'] for r in ss if r['jurisdiction']==state and r['year']<2019)
        D('FBI-2013-2018-'+state+'-value',state+' known reported stolen value, 2013–2018',val,'USD','sum(included_state_years)',{str(r['year']):r['stolen_usd'] for r in ss if r['jurisdiction']==state and r['year']<2019},allids[:-1])
        D('FBI-2013-2018-'+state+'-share',state+' share, 2013–2018',pct(val,six),'percent','state_value / national_value * 100',{'state_value':val,'national_value':six},allids[:-1])
    for r in locations:
        D('LOC-'+str(r['year'])+'-'+r['harmonized_label'],str(r['year'])+' '+r['harmonized_label']+' location share',r['share_pct'],'percent','entries / total_entries * 100',{'entries':r['location_entries'],'total_entries':r['denominator_location_entries']},[r['source_id']] if r.get('source_id') else r['source_ids'].split(';'),'Location entries, not unique incidents; source labels retained in location CSV.')
    def change(fid,label,old,new,sids,note='Change calculated from the displayed values; not an exposure-adjusted risk rate'):
        D(fid,label,(new-old)/old*100,'percent','(new - old) / old * 100',{'old':old,'new':new},sids,note)
    change('CN-mean-2023-2025','CargoNet mean change 2023–2025',187895,273990,['S15','S02'])
    change('CN-mean-2024-2025','CargoNet displayed-mean change 2024–2025',202364,273990,['S02'],'35.4% from displayed means; source prose reports 36%; retained as a discrepancy.')
    product24=2243*202364;product25=2646*273990
    for year,count,mean,product,estimate,sid in [(2024,2243,202364,product24,454914764,'S31'),(2025,2646,273990,product25,724978757,'S30')]:
        D(f'CN-{year}-count-times-mean','Count times rounded mean',product,'USD','count * mean',{'count':count,'mean':mean},['S02'],'Arithmetic identity only; not verification of the provider loss-estimation method.')
        D(f'CN-{year}-estimate-minus-product','Published estimate less count-times-mean product',estimate-product,'USD','estimate - product',{'estimate':estimate,'product':product},['S02',sid],'Different publication vintages and rounded inputs retained; reason for residual is not inferred.')
    change('CN-count-mean-product-change','Change in count-times-rounded-mean products',product24,product25,['S02'],'Arithmetic products, not an independently reproduced loss-estimation model.')
    D('CN-2025-broad-event-quotient','Hypothetical quotient using broad events',725000000/3594,'USD per broad event','rounded_estimate / broad_events',{'rounded_estimate':725000000,'broad_events':3594},['S02'],'Hypothetical quotient, not CargoNet’s reported average theft value.')
    shares=[]
    for period,b,c,sid in [('2024',3607,2243,'S02'),('2025',3594,2646,'S02'),('2026-Q1',767,596,'S20')]:
        share=pct(c,b);shares.append(share)
        D('CN-confirmed-share-'+period,'Confirmed share of broad recorded events',share,'percent','confirmed / broad_events * 100',{'confirmed':c,'broad_events':b},[sid],'Composition of recorded events, not a shipment theft rate.')
    for i,tag in [(0,'2024-to-2025'),(1,'2025-to-Q1-2026')]:
        D('CN-confirmed-share-change-'+tag,'Confirmed-share difference',shares[i+1]-shares[i],'percentage points','later_share - earlier_share',{'earlier_share':shares[i],'later_share':shares[i+1]},['S02','S20'] if i else ['S02'])
    for period,cn,oh,sids in [('2025-Q2',884,525,['S28','S29']),('2026-Q1',767,574,['S20','S27']),('2026-Q2',677,605,['S17','S16'])]:
        D('TRACKER-gap-'+period,'CargoNet count higher than Overhaul count',pct(cn-oh,oh),'percent','(CargoNet - Overhaul) / Overhaul * 100',{'CargoNet':cn,'Overhaul':oh},sids,'Descriptive count difference; different geography and measurement, not provider accuracy comparison.')
    change('CN-Q1-to-Q2-first-snapshots','CargoNet change using earlier published Q1 count',767,677,['S20','S17'],'Different cutoffs: not the source’s own -14% matched quarterly comparison.')
    change('CN-Q2-year-early-snapshots','CargoNet annual change using earlier Q2 2025 count',884,677,['S28','S17'],'Different snapshots: not the source’s own -26% matched comparison.')
    change('OH-Q2-year-early-snapshots','Overhaul annual change using earlier 525 count',525,605,['S29','S16'],'+15.2% calculated; Q2 2026 overview says -5%; unresolved source conflict.')
    change('OH-Q1-to-Q2-2026','Overhaul displayed quarterly count change',574,605,['S27','S16'])
    D('CN-annual-vintage-difference','2024 broad event count difference between releases',3607-3625,'events','later - earlier',{'later':3607,'earlier':3625},['S02','S15'])
    change('CN-annual-vintage-percent','2024 broad event publication-vintage difference',3625,3607,['S02','S15'],'Change between publications, not change in the crime year.')
    D('CN-cutoff-days','Q1 reporting-cutoff separation', (date(2026,4,27)-date(2026,4,14)).days,'days','2026-04-27 minus 2026-04-14',{'earlier':'2026-04-14','later':'2026-04-27'},['S20','S17'])
    for state,a,b in [('California',255,277),('Texas',102,80),('New Jersey',27,59)]:change('CN-state-'+state,'Q1 state incident change: '+state,a,b,['S20'])
    for cat,a,b,sid in [('personal care and beauty',18,50,'S20'),('building materials',21,8,'S20'),('metals',54,80,'S17'),('theft classification',488,378,'S17'),('fictitious pickups',165,158,'S17')]:change('CN-commodity-'+cat,'Matched incident change: '+cat,a,b,[sid])
    # Reference tables retain text and provenance; unresolved entries are not promoted to numeric facts.
    table_source_ids={
        '3':[['S04','S06','S07','S08','S09','S10','S11'],['S03','S05'],['S02','S17','S20'],['S16','S21','S27','S29'],['S12'],['S13','S14'],['S13','S14']],
        '6':[['S13','S14'],['S13'],['S14'],['S25'],['S26'],[],['S13','S14']],
        '11':[['S18']]*8}
    def reference_rows(key):
        rows=[]
        for i,r in enumerate(data['visible_reference_tables'][key]['rows']):
            ids=table_source_ids.get(key,[])
            sids=ids[i] if i<len(ids) else []
            rows.append(r|{'source_ids':';'.join(sids),'source_urls':';'.join(sources[s]['url'] for s in sids),'verified_on':checked,'record_type':'attributed reference / scope statement'})
        return rows
    tracker=reference_rows('3');provenance=reference_rows('6');law=reference_rows('11')
    quarters=[r for r in observations if re.fullmatch(r'\d{4}-Q[1-4]',r['period']) and r['metric'] in ['broad_supply_chain_events','cargo_theft_incidents','supply_chain_theft_incidents']]
    vintages=[
        {'comparison':'CargoNet 2024 broad events','earlier_value':'3625','later_value':'3607','earlier_publication':'2025-01-21','later_publication':'2026-01-21','interpretation':'Publication-vintage difference; no cause inferred','source_ids':'S15;S02'},
        {'comparison':'CargoNet Q1 2026 reporting cutoff','earlier_value':'2026-04-14','later_value':'2026-04-27','earlier_publication':'2026-04-23','later_publication':'2026-08-06','interpretation':'Comparison cutoffs differ by 13 days; no exact additional-event count inferred','source_ids':'S20;S17'},
        {'comparison':'CargoNet Q2 2026 annual change','earlier_value':'884 in early Q2 2025 release','later_value':'677 in Q2 2026; source says -26% year over year','earlier_publication':'2025-07-17','later_publication':'2026-08-06','interpretation':'Early count comparison yields -23.4%; later comparison uses matched July 27 cutoffs','source_ids':'S28;S17'},
        {'comparison':'Overhaul Q2 2026 annual change','earlier_value':'525 in Q2 2025 overview','later_value':'605 in Q2 2026 overview; source says -5% year over year','earlier_publication':'Not entered','later_publication':'Not entered','interpretation':'Earlier published counts yield +15.2%; revised baseline and reason not verified','source_ids':'S29;S16'}]
    for r in vintages:r.update(source_urls=';'.join(sources[s]['url'] for s in r['source_ids'].split(';')),verified_on=checked)
    outputs={
        'fbi-cargo-theft-national-2013-2019.csv':national,
        'fbi-cargo-theft-by-state-2013-2019.csv':state_rows,
        'fbi-cargo-theft-state-pooled-2013-2019.csv':pooled,
        'fbi-cargo-theft-locations-2014-2019.csv':locations,
        'fbi-cargo-theft-selected-property-values.csv':[enrich(r) for r in data['selected_property_rows']],
        'fbi-cargo-theft-reconciliation-checks.csv':reconciliation,
        'cargo-theft-provider-observations.csv':observations,
        'cargo-theft-derived-findings.csv':derived,
        'cargo-theft-tracker-reconciliation.csv':tracker,
        'cargo-theft-quarterly-series.csv':quarters,
        'cargo-theft-publication-vintage.csv':vintages,
        'cargo-theft-loss-estimate-provenance.csv':provenance,
        'florida-theft-grading-ladder-812-014.csv':law}
    for name,rows in outputs.items():csv_file(name,rows)
    ledger={'metadata':data['metadata'],'sources':data['sources'],'datasets':outputs,
        'notes':{'source_access':'Individual source scope notes distinguish directly read material from attribution destinations or unavailable underlying models.',
        'pooled_recovered_difference':'State-row recovered values sum $3,000 above the sum of printed national totals because of the 2013 source discrepancy; both are preserved.',
        'location_pooling':'Annual and pooled rows are differentiated by record_type; do not add them together.',
        'missing_values':'Empty CSV numeric fields / JSON null mean unknown or inapplicable, never zero.',
        'reproduction':'This script reproduces the compilation arithmetic, not private incident databases or providers’ undisclosed estimation models.'}}
    (out/'cargo-theft-source-ledger-v1.2.json').write_text(json.dumps(ledger,ensure_ascii=False,indent=2)+'\n',encoding='utf-8')
    return {'csv_files':len(outputs),'derived_findings':len(derived),'state_rows':len(state_rows),
            'annual_location_rows':len(ls),'location_entries':total_locations,'national_incidents':total_inc}


if __name__=='__main__':
    parser=argparse.ArgumentParser(description=__doc__)
    parser.add_argument('--input',type=Path,default=Path(__file__).with_name('cargo-theft-verified-inputs.json'))
    parser.add_argument('--output-dir',type=Path,default=Path(__file__).resolve().parent)
    args=parser.parse_args()
    try:
        result=build(args.input,args.output_dir)
    except (OSError,ValueError,KeyError,AssertionError) as exc:
        parser.exit(1,f'Dataset build failed: {exc}\n')
    print(json.dumps(result,indent=2))