from pathlib import Path
from decimal import Decimal as D
import csv,json,hashlib,shutil,zipfile
p=Path(__file__).resolve().parent
bom=p/'motor-A92-candidate-BOM.csv'
if not bom.exists(): shutil.copy2(p.parent/'drill_header_holes_A92'/bom.name,bom)
data=[
('U2 U3','TLV9024PWR','15856671',2,'0.99297',2000,'0.93739',1579),
('U4','SN74LVC2G17DBVR','479734',1,'0.08261',3000,'0.07752',399479),
('U5','SN74LVC1G74DCTR','2195375',1,'0.38709',3000,'0.35843',0),
('U6','SN74LVC1G08DBVR','385718',1,'0.04861',3000,'0.04532',119086),
('U7','SN74LVC1G04DBVR','385738',1,'0.06008',3000,'0.05614',354538),
('U8 U9','TLV803EA29DBZR','11309053',2,'0.26296',3000,'0.24952',1574),
('U10','SN74LVC1G17DBVR','389051',1,'0.06129',3000,'0.06076',252776)]
b={r['Ref']:r for r in csv.DictReader(bom.open(encoding='utf-8-sig'))}
rows=[]
for refs,mpn,sku,q,ct,rq,tr,stock in data:
 for ref in refs.split():
  assert b[ref]['CandidateMPN']==mpn and b[ref]['Manufacturer']=='Texas Instruments', (ref,b[ref])
 assert len(refs.split())==q
 selected=tr if rq==q*1000 else ct
 rows.append(dict(refs=refs,manufacturer='Texas Instruments',mpn=mpn,qty_per_board=q,demand_1000=q*1000,cut_tape_1000_usd=ct,reel_qty=rq,reel_unit_usd=tr,public_model_unit_usd=selected,observed_stock=stock,shortfall=max(q*1000-stock,0),surplus_if_one_reel=rq-q*1000,url=f'https://www.digikey.com/en/products/detail/texas-instruments/{mpn}/{sku}',observed_date='2026-10-10',evidence_type='MANUAL_WEB_TOOL_PAGE_OBSERVATION_NOT_RAW_PAGE_ARCHIVE',quote_status='NO_FACTORY_QUOTE_NO_CHECKOUT'))
with (p/'public-price-observations-A93.csv').open('w',newline='',encoding='utf-8-sig') as f:
 w=csv.DictWriter(f,fieldnames=rows[0].keys());w.writeheader();w.writerows(rows)
mixed=sum(D(r['public_model_unit_usd'])*r['qty_per_board'] for r in rows)
ct=sum(D(r['cut_tape_1000_usd'])*r['qty_per_board'] for r in rows)
reels=sum(D(r['reel_unit_usd'])*r['reel_qty'] for r in rows)
consumed=sum(D(r['reel_unit_usd'])*r['qty_per_board'] for r in rows)
result=dict(status='PUBLIC_CATALOG_SCOPE_ESTIMATE_SUPPLY_HOLD',scope='9 added protection ICs only, seven exact TI MPNs',boards=1000,ic_count_per_board=sum(r['qty_per_board'] for r in rows),mixed_catalog_per_board_usd=str(mixed),mixed_catalog_batch_usd=str(mixed*1000),all_ct_1000_tier_per_board_usd=str(ct),full_reel_purchase_usd=str(reels),full_reel_consumed_value_per_board_usd=str(consumed),full_reel_surplus_value_usd=str(reels-consumed*1000),stock_shortfalls={r['mpn']:r['shortfall'] for r in rows if r['shortfall']},bom_sha256=hashlib.sha256(bom.read_bytes()).hexdigest(),comparison_cost_fraction_percent=str(D('1.87478')/mixed*100),excluded=['19 added passives','J5 connector','PCB area','assembly','original MCU and motor driver','motor and mechanics','taxes/tariffs/shipping/reeling','attrition','finished-unit cost'],effect_validation='NOT_VERIFIED',factory_DFM='NOT_CONFIRMED',fabrication_release='HOLD')
(p/'calculation-A93.json').write_text(json.dumps(result,indent=2,ensure_ascii=False)+'\n')
lines=['# A93 电磨新增保护 IC 成本与供货核对','', '日期：2026-10-10；基线：A92 候选 BOM；数量：1,000 板。仅核对 U2–U10 共 9 颗 IC，不是完整 PCBA 或整机报价。','',f'按公开阶梯价：TLV9024 用 2,000 颗整盘价，其余用 1,000 颗剪带价（TLV803 两千颗沿用千颗参考价），估算 ${mixed}/板、${mixed*1000}/批。全用千颗剪带阶梯估算 ${ct}/板。库存不足，所以这些不是今天可执行的采购方案。','',f'若七种料各购一整盘，公开价理论付款 ${reels}；按实际耗用折算 ${consumed}/板，另有 ${reels-consumed*1000} 剩余库存价值。不得将耗用成本当作本批采购支出。','', '| MPN | 每板数量 | 千颗剪带 USD/颗 | 整盘数量 / USD/颗 | 页面库存 | 千板缺口 |','|---|---:|---:|---:|---:|---:|']
for r in rows: lines.append(f"| [{r['mpn']}]({r['url']}) | {r['qty_per_board']} | {r['cut_tape_1000_usd']} | {r['reel_qty']} / {r['reel_unit_usd']} | {r['observed_stock']} | {r['shortfall']} |")
lines+=['','两颗比较器占本范围参考成本约 '+format(D('1.87478')/mixed*100,'.2f')+'%，是后续降成本重点。先寻找同一 TI 型号的可追溯渠道报价；换料须重核引脚、输入范围、开漏输出、失电状态、传播延迟和封装，不能按搜索结果或商城“替代”标签直接换。','', 'U5 页面库存为 0，预计 2026-11-24 到货 3,000 颗只是页面预测，不是交付承诺。TLV9024 与 TLV803 页面库存分别比千板需求少 421、426 颗。库存为页面汇总，未确认包装分配、收货地区或下单可用量。','', '来源是 DigiKey 美国 USD 页面人工结构化摘录；未保存原始网页，也没有网页文件哈希。公开信息会变化，无工厂报价、预留库存或下单。保持原 BOM 的 NO_QUOTE 字段；本轮不修改电路和 PCB。','', '结论：新增保护 IC 的公开价格基准已建立，但供货与完整整机成本仍未走通。A92 可送制造审查，未签发投板放行；接口配合、电流温升、工厂 DFM 与后续样机验证仍待落实。第一轮工程样板不要求先有整机功能实测，但不能把候选 CAD 当成功能等效证据。']
(p/'A93-drill-protection-cost-review.md').write_text('\n'.join(lines)+'\n')
print(json.dumps(result,ensure_ascii=False,indent=2))
