"""Reproduce the AMR proved crude oil and lease condensate comparison.

Doc: AMR reserves source and calculation program
Status: Fixed source edition, Python standard library only
Owner: American Mineral Registry
Last verified: 2026-11-14
Depends on: evidence-contract.json and four pinned EIA XLSX inputs
Run: python3 analysis.py
"""
from pathlib import Path
from decimal import Decimal, getcontext
from fractions import Fraction
import csv,json,zipfile,hashlib,xml.etree.ElementTree as ET
getcontext().prec=34
ROOT=Path(__file__).resolve().parent;DATA=ROOT/'data';DATA.mkdir(exist_ok=True);(ROOT/'qa').mkdir(exist_ok=True)
D=Decimal;CHECKS=[];CELLS=[]
OVERVIEW='https://www.eia.gov/naturalgas/crudeoilreserves/'
URLS={'reserves-history-fig1.xlsx':OVERVIEW+'excel/fig1_data.xlsx',**{f'reserves-2024-table-{n:02d}.xlsx':OVERVIEW+f'excel/ARR_2024_TABLE_{n:02d}.xlsx' for n in [1,2,5]}}
def check(name,condition):CHECKS.append(name);assert condition,name
def workbook(path):
    ns={'m':'http://schemas.openxmlformats.org/spreadsheetml/2006/main'}
    with zipfile.ZipFile(path)as z:
        shared=[''.join(x.itertext()) for x in ET.fromstring(z.read('xl/sharedStrings.xml')).findall('m:si',ns)] if 'xl/sharedStrings.xml'in z.namelist()else[]
        rel={x.attrib['Id']:x.attrib['Target'] for x in ET.fromstring(z.read('xl/_rels/workbook.xml.rels'))};out={}
        for s in ET.fromstring(z.read('xl/workbook.xml')).findall('m:sheets/m:sheet',ns):
            name=rel[s.attrib['{http://schemas.openxmlformats.org/officeDocument/2006/relationships}id']];name=name.lstrip('/')if name.startswith('/')else'xl/'+name;cells={}
            for c in ET.fromstring(z.read(name)).findall('.//m:sheetData/m:row/m:c',ns):
                typ=c.attrib.get('t','n');v=c.find('m:v',ns);value=v.text if v is not None else''
                if typ=='s':value=shared[int(value)]
                if typ=='inlineStr':value=''.join(c.find('m:is',ns).itertext())
                if value!='':cells[c.attrib['r']]=(typ,value)
            out[s.attrib['name']]=cells
        return out
def write(name,rows):
    check(name+' populated',bool(rows)and all(set(x)==set(rows[0])for x in rows)and all(v is not None and str(v)!=''for r in rows for v in r.values()))
    with(DATA/name).open('w',newline='')as f:w=csv.DictWriter(f,fieldnames=list(rows[0]));w.writeheader();w.writerows(rows)
def pct(a,b):return D(a)/D(b)*100
def main():
    w={name:workbook(ROOT/'sources'/name)for name in URLS}
    def number(name,sheet,cell):
        typ,value=w[name][sheet][cell];check(name+sheet+cell+' numeric',typ=='n');v=D(value);check('finite '+name+cell,v.is_finite());CELLS.append(dict(source_file=name,sheet=sheet,cell=cell,cached_value=value,unit='million barrels unless year',source_url=URLS[name]));return v
    histname='reserves-history-fig1.xlsx';sheet=next(iter(w[histname]));history=[]
    for row in range(54,95):
        year=int(number(histname,sheet,'A'+str(row)));value=number(histname,sheet,'B'+str(row));check('history positive '+str(year),value>0);history.append(dict(year=year,year_end_reserves_million_barrels=value,category='Crude oil and lease condensate',source_cells=sheet+'!A'+str(row)+':B'+str(row),source_url=URLS[histname]))
    check('41 complete history years',[r['year']for r in history]==list(range(1984,2025)))
    byyear={r['year']:r['year_end_reserves_million_barrels']for r in history};bridge=[];ratios=[]
    for row in range(5,16):
        file='reserves-2024-table-05.xlsx';vals=[number(file,'5',col+str(row))for col in 'ABCDEFG'];year=int(vals[0]);rev,acq,ext,prod,end,change=vals[1:];start=byyear[year-1];net=rev+acq+ext-prod;residual=change-net
        check('matched reported change '+str(year),end-start==change);check('source history match '+str(year),end==byyear[year]);check('small rounding residual '+str(year),abs(residual)<=2)
        r=dict(year=year,previous_year_end_million_barrels=start,revisions_other_changes_million_barrels=rev,net_acquisitions_divestitures_million_barrels=acq,extensions_discoveries_million_barrels=ext,reserves_report_production_million_barrels=prod,component_net_change_million_barrels=net,published_net_change_million_barrels=change,rounding_residual_million_barrels=residual,year_end_reserves_million_barrels=end,source_cells='5!A'+str(row)+':G'+str(row),source_url=URLS[file]);bridge.append(r)
        additions=change+prod
        ratios.append(dict(year=year,reported_net_additions_million_barrels=additions,extensions_discoveries_million_barrels=ext,reserves_report_production_million_barrels=prod,net_additions_to_production_percent=pct(additions,prod),extensions_discoveries_to_production_percent=pct(ext,prod),year_end_reserves_to_same_year_production_multiple=end/prod,year_end_reserves_change_percent=pct(change,start),production_change_percent=pct(prod-bridge[-2]['reserves_report_production_million_barrels'],bridge[-2]['reserves_report_production_million_barrels'])if len(bridge)>1 else'Not in selected bridge window',source_url=URLS[file]))
    # The first production change is not essential to any comparison and is omitted, not assigned zero.
    for r in ratios:r.pop('production_change_percent')
    current=bridge[-1];file='reserves-2024-table-01.xlsx'
    for cell,key in [('B5','previous_year_end_million_barrels'),('B6','extensions_discoveries_million_barrels'),('B7','revisions_other_changes_million_barrels'),('B8','net_acquisitions_divestitures_million_barrels'),('B9','reserves_report_production_million_barrels'),('B10','published_net_change_million_barrels'),('B11','year_end_reserves_million_barrels')]:check('Tables1and5 '+cell,number(file,'1',cell)==current[key])
    steps=[];running=current['previous_year_end_million_barrels']
    steps.append(dict(order=1,step='Previous year end',kind='level',signed_amount_million_barrels=running,running_reserves_million_barrels=running,source_url=URLS[file]))
    for i,(label,value)in enumerate([('Extensions and discoveries',current['extensions_discoveries_million_barrels']),('Revisions and other changes',current['revisions_other_changes_million_barrels']),('Net acquisitions and divestitures',current['net_acquisitions_divestitures_million_barrels']),('Production',-current['reserves_report_production_million_barrels']),('Published rounding residual',current['rounding_residual_million_barrels'])],2):
        running+=value;steps.append(dict(order=i,step=label,kind='change',signed_amount_million_barrels=value,running_reserves_million_barrels=running,source_url=URLS[file]))
    check('2024 full bridge',running==current['year_end_reserves_million_barrels']);steps.append(dict(order=7,step='Current year end',kind='level',signed_amount_million_barrels=running,running_reserves_million_barrels=running,source_url=URLS[file]))
    file='reserves-2024-table-02.xlsx';shale=[]
    for row in [6,7,8,9,10,11,12,14]:
        cells=w[file]['2'];label=cells['B'+str(row)][1]if 'B'+str(row)in cells else cells['A'+str(row)][1];basin=cells['A'+str(row)][1]
        a,b,c,d,e,f=[number(file,'2',col+str(row))for col in 'DEFGHI'];check('shale source changes '+label,c-a==e and d-b==f)
        shale.append(dict(basin=basin,play=label,category_type='Residual aggregate'if row==14 else'Named source play group',production_2023_million_barrels=a,reserves_2023_million_barrels=b,production_2024_million_barrels=c,reserves_2024_million_barrels=d,production_change_million_barrels=e,reserves_change_million_barrels=f,reserves_change_percent=pct(f,b),share_of_2024_shale_reserves_percent=pct(d,27533),share_of_2024_national_oil_condensate_reserves_percent=pct(d,45951),source_cells='2!A'+str(row)+':I'+str(row),source_url=URLS[file]))
    national=[];rounding=[]
    for year,pc,rc in [(2023,'D','E'),(2024,'F','G')]:
        shale_prod=number(file,'2',pc+'15');shale_res=number(file,'2',rc+'15');overall=byyear[year];residual=overall-shale_res
        national.append(dict(year=year,total_oil_condensate_reserves_million_barrels=overall,shale_reserves_million_barrels=shale_res,nonshale_residual_reserves_million_barrels=residual,shale_share_of_national_percent=pct(shale_res,overall),shale_reserves_report_production_million_barrels=shale_prod,source_url=URLS[file],national_source_url=URLS["reserves-2024-table-01.xlsx"]))
        prod_sum=sum(r[f'production_{year}_million_barrels']for r in shale);res_sum=sum(r[f'reserves_{year}_million_barrels']for r in shale)
        check('shale full reserve categories '+str(year),res_sum==shale_res);check('shale small production rounding '+str(year),abs(shale_prod-prod_sum)<=1)
        rounding.append(dict(year=year,reported_shale_production_million_barrels=shale_prod,leaf_production_sum_million_barrels=prod_sum,production_rounding_residual_million_barrels=shale_prod-prod_sum,reported_shale_reserves_million_barrels=shale_res,leaf_reserves_sum_million_barrels=res_sum,reserves_rounding_residual_million_barrels=shale_res-res_sum,source_url=URLS[file]))
    extrema=[]
    for direction,seq in [('Highest',sorted(history,key=lambda x:-x['year_end_reserves_million_barrels'])),('Lowest',sorted(history,key=lambda x:x['year_end_reserves_million_barrels']))]:
        for i,r in enumerate(seq[:5],1):extrema.append(dict(comparison=direction,rank=i,year=r['year'],year_end_reserves_million_barrels=r['year_end_reserves_million_barrels'],sample='1984-2024 inclusive,41year-end observations',source_url=URLS[histname]))
    write('national-reserves-1984-2024.csv',history);write('annual-reserve-accounting-2014-2024.csv',bridge);write('annual-additions-and-production-ratios.csv',ratios);write('2024-reserve-bridge-steps.csv',steps);write('shale-play-comparison-2023-2024.csv',shale);write('shale-and-nonshale-national-comparison.csv',national);write('shale-source-rounding-controls.csv',rounding);write('fixed-history-highs-and-lows.csv',extrema);write('used-primary-workbook-cells.csv',list({(r['source_file'],r['sheet'],r['cell']):r for r in CELLS}.values()))
    summary={'status':'PASS','checks':len(CHECKS),'source_sha256':{n:hashlib.sha256((ROOT/'sources'/n).read_bytes()).hexdigest()for n in URLS},'current':current,'ratios_2024':ratios[-1],'national_shale':national,'highs_and_lows':extrema,'positive_change_years':sum(r['published_net_change_million_barrels']>0 for r in bridge),'negative_change_years':sum(r['published_net_change_million_barrels']<0 for r in bridge),'extensions_above_production_years':sum(r['extensions_discoveries_million_barrels']>r['reserves_report_production_million_barrels']for r in bridge),'net_additions_negative_years':[r['year']for r in ratios if r['reported_net_additions_million_barrels']<0]}
    (DATA/'summary.json').write_text(json.dumps(summary,default=str,indent=2)+'\n');(ROOT/'qa/analysis-results.json').write_text(json.dumps({'status':'PASS','checks':len(CHECKS),'history_rows':41,'annual_bridges':11,'shale_leaf_rows':8,'source_cells':len({(r['source_file'],r['sheet'],r['cell'])for r in CELLS})},indent=2)+'\n');print(json.dumps({'status':'PASS','checks':len(CHECKS),'history':41,'annual_bridges':11,'shale_categories':8}))
if __name__=='__main__':main()
