import json
from pathlib import Path
P=Path('scripts/bom-import')
def sqlstr(s):return 'CONVERT(0x'+str(s).encode().hex()+' USING utf8mb4)' if str(s) else "''"
for target in ['local','remote']:
 plan=json.loads((P/f'{target}-plan.json').read_text(encoding='utf-8'))
 sql=['CREATE TEMPORARY TABLE `mom-hm`.stage_bom_import(parent_id BIGINT,child_id BIGINT,parent_code VARCHAR(50),child_code VARCHAR(50),parent_name VARCHAR(200),child_name VARCHAR(200),parent_unit BIGINT,child_unit BIGINT,quantity DECIMAL(24,6),remark VARCHAR(255),action INT,existing_id BIGINT,old_quantity DECIMAL(24,6),PRIMARY KEY(parent_id,child_id)) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci']
 vals=[]
 for x in plan['lines']:
  remark=f"来源：{x['file']}/{x['sheet']}/第{x['row']}行；原始用量{x['quantity']} {x['unit']}"
  if x['section']:remark+='；下级组成：'+x['section']
  if x['factor']!='1':remark+='；已按系统单位换算'
  assert len(remark)<=255
  action={'新增':0,'更新':1,'已存在':2}[x['action']]
  values=[str(x['parent_id']),str(x['child_id']),sqlstr(x['parent_code']),sqlstr(x['child_code']),sqlstr(x['parent_name']),sqlstr(x['child_name']),x['parent_unit_id'],x['child_unit_id'],x['system_quantity'],sqlstr(remark),str(action),str(x.get('existing_id',0)),str(x.get('old_quantity',0))]
  vals.append('('+','.join(values)+')')
 for i in range(0,len(vals),100):sql.append('INSERT INTO `mom-hm`.stage_bom_import VALUES '+','.join(vals[i:i+100]))
 (P/f'{target}-import.sql').write_text(';\n'.join(sql)+';\n',encoding='utf-8')