from pathlib import Path
import csv,json,hashlib,xml.etree.ElementTree as E,collections
P=Path(__file__).resolve().parent
root=E.parse(P/'netlist-A86-snapshot.xml').getroot()
old={r['Ref']:r for r in csv.DictReader((P/'A23-motor-BOM-snapshot.csv').open(encoding='utf-8-sig'))}
sources={x['MPN']:x for x in json.loads((P/'source-fetch-A87.json').read_text())}
passives={'R16':'RC0603FR-0714KL','R17':'RC0603FR-0710K7L','R18':'RC0603FR-0714KL','R19':'RC0603FR-0710KL','R20':'RC0603FR-07100KL','R21':'RC0603FR-071KL','R22':'RC0603FR-07100KL'}
passives.update({f'C{i}':'CC0603JRNPO9BN102' if i in [20,21] else 'CC0603KRX7R9BB104' for i in range(19,31)})
check={'RC0603FR-0714KL':['14 kOhms','1%','0603 / 1608','0.1 W'], 'RC0603FR-0710K7L':['10.7 kOhms','1%','0603 / 1608','0.1 W'],'RC0603FR-0710KL':['10 kOhms','1%','0603 / 1608','0.1 W'],'RC0603FR-07100KL':['100 kOhms','1%','0603 / 1608','0.1 W'],'RC0603FR-071KL':['1 kOhms','1%','0603 / 1608','0.1 W'],'CC0603JRNPO9BN102':['1,000 pF','5%','50 VDC','C0G','0603 / 1608'],'CC0603KRX7R9BB104':['0.1 uF','10%','50 VDC','X7R','0603 / 1608']}
for m,tokens in check.items():
 s=sources[m];assert s['fetch_status'].startswith('FETCHED')
 data=(P/s['file']).read_bytes();assert hashlib.sha256(data).hexdigest()==s['sha256']
 text=(P/'sources'/(m+'.txt')).read_text().replace('\u200b','')
 assert all(t in text for t in tokens),(m,tokens)
ics=json.loads((P/'IC-primary-review-A87.json').read_text());icmap={x['MPN']:x for x in ics}
rows=[];diff=[]
for c in root.findall('components/comp'):
 ref=c.get('ref');v=c.findtext('value');fp=c.findtext('footprint');dnp=c.find("property[@name='dnp']") is not None
 r={'Ref':ref,'DesignValue':v,'PCBFootprint':fp,'DNP':dnp,'QtyPerBoard':0 if dnp else 1,'Qty1000BoardsNoAttrition':0 if dnp else 1000,'Manufacturer':'','CandidateMPN':'','SelectionStatus':'OPEN','SourceURL':'','SourceSHA256':'','UnitPriceUSD1000':'','QuoteStatus':'NO_QUOTE','Qualification':'Component/function/load/DFM/availability open'}
 if ref in passives:
  m=passives[ref];s=sources[m];r.update(Manufacturer='YAGEO',CandidateMPN=m,SelectionStatus='PRIMARY_NOMINAL_PARAMETERS_CHECKED_A87',SourceURL=s['final_url'],SourceSHA256=s['sha256'],Qualification='Nominal case/value/tolerance checked; DC-bias/effective capacitance, thermal, placement, dynamic cutoff and factory sourcing open')
 elif ref.startswith('U') and ref!='U1':
  s=icmap[v];assert s['PCBFootprint']==fp
  r.update(Manufacturer='Texas Instruments',CandidateMPN=v,SelectionStatus='PRIMARY_ORDERING_PACKAGE_CHECKED_A87',SourceURL=s['URL'],Qualification='Ordering package/pins checked; footprint land/assembly, timing, analog circuit, qualified sourcing and actual protection open')
 elif ref in old:
  o=old[ref];same=o['DesignValue']==v and o['CurrentPCBFootprint']==fp
  if same:
   r.update(Manufacturer=o['CandidateManufacturer'],CandidateMPN=o['CandidateMPN'],SelectionStatus='A23_MATCHING_CANDIDATE_INHERITED_NOT_REQUALIFIED' if o['CandidateMPN'] else 'OPEN',SourceURL=o['PrimarySourceURL'],SourceSHA256=o['PrimarySourceSHA256'])
  else:diff.append({'Ref':ref,'OldValue':o['DesignValue'],'CurrentValue':v,'OldMPNNotCarried':o['CandidateMPN'],'DNP':dnp})
 if ref=='J5':r['Qualification']='Bench dry-contact ARM header; connector MPN and final product reset interaction open'
 rows.append(r)
assert len(rows)==68 and sum(r['DNP'] for r in rows)==1
assert next(r for r in rows if r['Ref']=='R2')['DesignValue']=='0R'
assert next(r for r in rows if r['Ref']=='R3')['Qty1000BoardsNoAttrition']==0
assert next(r for r in rows if r['Ref']=='U5')['CandidateMPN']=='SN74LVC1G74DCTR'
assert len(passives)==19
fields=list(rows[0])
with (P/'motor-A86-candidate-BOM-A87.csv').open('w',encoding='utf-8-sig',newline='') as f:
 w=csv.DictWriter(f,fieldnames=fields);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'],'Refs':[],'QtyPerBoard':0,'Qty1000BoardsNoAttrition':0,'UnitPriceUSD1000':'','QuoteStatus':'NO_QUOTE','DNP':r['DNP']})
 g['Refs'].append(r['Ref']);g['QtyPerBoard']+=r['QtyPerBoard'];g['Qty1000BoardsNoAttrition']+=r['Qty1000BoardsNoAttrition']
for g in groups.values():g['Refs']=' '.join(g['Refs'])
with (P/'motor-A86-1000boards-RFQ-A87.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())
(P/'A23-drift-rejected-A87.json').write_text(json.dumps(diff,indent=2)+'\n')
result={'status':'A87_CANDIDATE_BOM_RECONCILED_NOT_PROCUREMENT_CLOSED','netlist_components':len(rows),'fitted_components':sum(r['QtyPerBoard'] for r in rows),'DNP_refs':[r['Ref'] for r in rows if r['DNP']],'candidate_MPN_covered':sum(bool(r['CandidateMPN']) for r in rows),'open_MPN_refs':[r['Ref'] for r in rows if not r['CandidateMPN']],'new_passive_nominal_checks':19,'new_IC_ordering_checks':9,'A23_changed_rows_not_blindly_carried':diff,'priced_rows':0,'factory_DFM':False,'fabrication_release':False,'motor_run_permission':False,'input_sha256':{name:hashlib.sha256((P/name).read_bytes()).hexdigest() for name in ['netlist-A86-snapshot.xml','A23-motor-BOM-snapshot.csv','IC-primary-review-A87.json','source-fetch-A87.json']}}
(P/'BOM-verification-A87.json').write_text(json.dumps(result,indent=2)+'\n');print(json.dumps(result))
