from pathlib import Path
import csv,json,hashlib,xml.etree.ElementTree as E
P=Path(__file__).resolve().parent
rows=list(csv.DictReader((P/'A87-candidate-BOM-snapshot.csv').open(encoding='utf-8-sig')))
root=E.parse(P/'netlist-A86-snapshot.xml').getroot();components={c.get('ref'):c for c in root.findall('components/comp')}
assert len(rows)==len(components)==68 and {r['Ref'] for r in rows}==set(components)
for r in rows:
 c=components[r['Ref']];assert r['DesignValue']==c.findtext('value') and r['PCBFootprint']==c.findtext('footprint')
 assert (r['DNP']=='True')==(c.find("property[@name='dnp']") is not None)
 assert int(r['QtyPerBoard'])==(0 if r['DNP']=='True' else 1)
all_sources=json.loads((P/'source-fetch-A87.json').read_text())+json.loads((P/'source-fetch-delta-A88.json').read_text())
sources={s['MPN']:s for s in all_sources}
checks={'RC0603JR-070RL':['0 mOhms','0603 / 1608'], 'RC0603FR-07680RL':['680 Ohms','1%','0603 / 1608','0.1 W'], 'RC0603FR-07240KL':['240 kOhms','1%','0603 / 1608','0.1 W'],'CC0603KRX7R9BB104':['0.1 uF','10%','50 VDC','X7R','0603 / 1608']}
for m,tokens in checks.items():
 s=sources[m];assert hashlib.sha256((P/s['file']).read_bytes()).hexdigest()==s['sha256']
 t=(P/'sources'/(m+'.txt')).read_text().replace('\u200b','');assert all(x in t for x in tokens),(m,tokens)
overrides={'R2':'RC0603JR-070RL','R5':'RC0603FR-07680RL','R13':'RC0603FR-07240KL','C14':'CC0603KRX7R9BB104'}
for r in rows:
 if r['Ref'] in overrides:
  m=overrides[r['Ref']];s=sources[m];r.update(Manufacturer='YAGEO',CandidateMPN=m,SelectionStatus='PRIMARY_NOMINAL_PARAMETERS_CHECKED_A88',SourceURL=s['final_url'],SourceSHA256=s['sha256'],Qualification='Nominal value/case checked; actual dynamic/thermal/DFM and sourcing not qualified')
q=json.loads((P/'Q1-primary-review-A88.json').read_text());assert q['pin_map']=={'1':'G','2':'S','3':'D'}
pinmap={x.get('pin'):n.get('name') for n in root.findall('nets/net') for x in n.findall('node') if x.get('ref')=='Q1'}
assert pinmap==q['native_net_expectation']
r=next(r for r in rows if r['Ref']=='Q1');assert r['PCBFootprint']=='Package_TO_SOT_SMD:SOT-23' and r['DesignValue']=='BSS138BK'
r.update(Manufacturer='Nexperia',CandidateMPN=q['MPN'],SelectionStatus='PRIMARY_ORDERING_AND_PIN_MAP_CHECKED_A88',SourceURL=q['ordering_URL'],SourceSHA256='',Qualification='SOT23 G1/S2/D3 matches native netlist. Gate drive minimum, temperature, drain leakage, shutdown delay and sourcing open; local PDF fetch 403, official web datasheet reviewed')
with (P/'motor-A86-candidate-BOM-A88.csv').open('w',encoding='utf-8-sig',newline='') as f:
 w=csv.DictWriter(f,fieldnames=list(rows[0]));w.writeheader();w.writerows(rows)
groups={}
for r in rows:
 key=(r['CandidateMPN'] or 'OPEN:'+r['Ref'],r['PCBFootprint'],r['DNP']);g=groups.setdefault(key,{'CandidateMPN':r['CandidateMPN'],'PCBFootprint':r['PCBFootprint'],'DNP':r['DNP'],'Refs':[],'QtyPerBoard':0,'Qty1000BoardsNoAttrition':0,'UnitPriceUSD1000':'','QuoteStatus':'NO_QUOTE'})
 g['Refs'].append(r['Ref']);g['QtyPerBoard']+=int(r['QtyPerBoard']);g['Qty1000BoardsNoAttrition']+=int(r['Qty1000BoardsNoAttrition'])
for g in groups.values():g['Refs']=' '.join(g['Refs'])
with (P/'motor-A86-1000boards-RFQ-A88.csv').open('w',encoding='utf-8-sig',newline='') as f:
 w=csv.DictWriter(f,fieldnames=list(next(iter(groups.values()))));w.writeheader();w.writerows(groups.values())
assert sum(g['Qty1000BoardsNoAttrition'] for g in groups.values())==67000
result={'status':'A88_FIVE_MORE_CANDIDATE_MPN_RECONCILED_NOT_FABRICATION_RELEASE','netlist_components':68,'fitted_components':67,'DNP_refs':['R3'],'candidate_MPN_covered':sum(bool(r['CandidateMPN']) for r in rows),'new_candidates':dict(overrides,Q1=q['MPN']),'open_MPN_refs':[r['Ref'] for r in rows if not r['CandidateMPN']],'Q1_native_pin_match':True,'Q1_electrical_application_qualified':False,'net_quantity_1000boards_no_attrition':67000,'priced_rows':0,'factory_DFM':False,'fabrication_release':False,'motor_run_permission':False,'inputs_sha256':{x:hashlib.sha256((P/x).read_bytes()).hexdigest() for x in ['A87-candidate-BOM-snapshot.csv','netlist-A86-snapshot.xml','source-fetch-delta-A88.json','Q1-primary-review-A88.json']}}
assert result['candidate_MPN_covered']==60 and len(result['open_MPN_refs'])==8
(P/'BOM-verification-A88.json').write_text(json.dumps(result,indent=2)+'\n');print(json.dumps(result))
