import json,hashlib,collections,csv,re,unicodedata
from pathlib import Path
from decimal import Decimal,ROUND_HALF_UP
import xml.etree.ElementTree as ET
P=Path('scripts/quotation-import')
source=json.loads((P/'source.json').read_text(encoding='utf-8'))
db=ET.parse(P/'local-snapshot.xml')
materials={x.findtext('code'):{e.tag:e.text or '' for e in x} for x in db.findall('./materials/row')}
units={x.findtext('name').strip():{e.tag:e.text for e in x} for x in db.findall('./units/row')}
# Explicit correspondence of source product + package specification to the TC product series.
mapping=[
 ('即食小罐洞燕窝','840克（30克×28）','TC0025'),
 ('即食小罐洞燕窝','360克（30克×12）','TC0024'),
 ('洞燕窝即食碗装燕窝','1.2kg（100g*12碗/盒）','TC0035'),
 ('即食碗装洞燕窝（怡碗燕）','720g（90g×8）','TC0037'),
 ('即食小罐洞燕窝','210克（30克×7）','TC0022'),
 ('即食小罐洞燕窝（尊享款）','480克（30克×16）','TC0027'),
 ('黑果枸杞','90g','TC0016'),
 ('即食小罐洞燕窝（尊享款）','600g（30g*20瓶/盒）','TC0026'),
 ('洞燕窝即食碗装燕窝','1.5千克（150克×10）','TC0032'),
 ('枸杞原浆罐头','1.44L（60ml*24瓶/盒）','TC0009'),
 ('即食碗装红枣枸杞洞燕窝(自热型)','600克(100克/碗*6)','TC0030'),
 ('有机黑果枸杞','80克（4克×20）','TC0003'),
 ('黑果枸杞','60g','TC0017'),
 ('黑果枸杞原浆罐头','1.44L（60ml*24瓶/盒）','TC0007'),
 ('沙棘原浆罐头','1.44L（60ml*24瓶/盒）','TC0011'),
 ('黑果枸杞袋泡茶','60g（2g/袋*30）','TC0006'),
 ('总统牌洞燕盏即食燕窝罐头','110g','TC0029'),
 ('即食碗装鱼胶洞燕窝(自热型)','600克(100克/碗*6）','TC0031'),
 ('有机黑果枸杞','120克（4克×30）','TC0004'),
 ('黑果枸杞','180克（90克×2）','TC0005'),
 ('有机黑果枸杞','48克（4克×12）','TC0002'),
 ('洞燕窝即食碗装燕窝','600g（100g*6碗/盒）','TC0036'),
 ('枸杞原浆罐头','360ml（60ml*6瓶/盒）','TC0010'),
 ('沙棘原浆罐头','360ml（60ml*6瓶/盒）','TC0012'),
 ('黑果枸杞原浆罐头','360ml（60ml*6瓶/盒）','TC0008'),
 ('即食碗装洞燕窝（自热型）','600g（100g*6）','TC0034'),
 ('冻干枸杞','200g/盒（8g/袋×25）','TC0001'),
 ('洞燕窝即食碗装燕窝','900克（150克*6）','TC0033'),
 ('枸杞原浆罐头','900ml（30ml/袋×30）','TC0014'),
 ('黑果枸杞玫瑰果膏','900ml（30ml/袋×30）','TC0015'),
 ('即食藜麦粉R型','450克（30克×15)','TC0013'),
 ('黑果枸杞','80克（4g/罐×20）','TC0019'),
]
mapped={(a,b):c for a,b,c in mapping}
newkeys={('鲜冬虫夏草','4A级 （1.01-1.3g）/ 根*9根'),('雪菊','50g'),('黑果枸杞玫瑰果膏-30ml/袋','30ml/袋'),('黑果枸杞','120克（4g/罐×30）')}
rows=[];newproducts={};productmap={};quotes=[]
canonical_customers={}
assert len(source['quotes'])==345 and sum(len(q['items']) for q in source['quotes'])==1702
assert source['excluded_sheets']==['报价单汇总总表','编制说明']
assert len({q['no'] for q in source['quotes']})==345
for q in source['quotes']:
 assert q['tax'] is None and q['contact'] is None
 out=dict(q);out['name']=q['customer']+' 2026年8-10月产品报价单';out['items']=[]
 customer_key=unicodedata.normalize('NFKC',q['customer']).casefold().strip()
 out['source_customer']=q['customer'];out['customer']=canonical_customers.setdefault(customer_key,q['customer'])
 assert len(out['name'])<=128 and len(q['no'])<=32 and len(q['customer'])<=255
 total=Decimal(0);count=Decimal(0)
 for x in q['items']:
  key=(x['name'],x['specification']);unit=units[x['unit']]
  if key in mapped:
   code=mapped[key];m=materials[code];assert m['status']=='0'
   itemid=int(m['id']);itemname=m['name'];expected_base_unit=int(m['unit_measure_id'])
  else:
   assert key in newkeys,key
   code='SQ20261012-'+hashlib.sha256('|'.join(key).encode()).hexdigest()[:10].upper()
   itemid=0;itemname=x['name'];expected_base_unit=int(unit['id'])
   newproducts[code]=dict(code=code,name=x['name'],specification=x['specification'],unit_id=int(unit['id']),unit_name=x['unit'],item_type=2)
  vals=[Decimal(x[k]) for k in ['original_price','price','count','total']]
  assert vals[0]>=0 and vals[1]>=0 and vals[2]>0 and vals[3]>=0
  assert all(v==v.quantize(Decimal('0.000001')) for v in vals)
  calculated=(vals[1]*vals[2]).quantize(Decimal('.01'),rounding=ROUND_HALF_UP)
  remark=f"源表第{x['row']}行；{x['name']}；规格{x['specification']}；单位{x['unit']}"
  if calculated!=vals[3]:remark+=f"；原表均价已四舍五入，单价×数量={calculated}，原表小计={vals[3]}，按原表小计导入"
  assert len(remark)<=256
  y=dict(x,item_code=code,item_id=itemid,item_name=itemname,item_unit_id=int(unit['id']),expected_base_unit=expected_base_unit,remark=remark)
  out['items'].append(y);total+=vals[3];count+=vals[2]
  rows.append(dict(no=q['no'],customer=q['customer'],**y))
  productmap[key]=dict(name=x['name'],specification=x['specification'],source_unit=x['unit'],item_code=code,item_name=itemname,action='补建销售产品' if not itemid else '使用已有产品')
 foot=[f for f in q['footers'] if f['values'][0]=='合计'];assert len(foot)==1
 assert Decimal(str(foot[0]['values'][9]))==total,(q['no'],total,foot)
 assert Decimal(str(foot[0]['values'][8]))==count,(q['no'],count,foot)
 out['total']=str(total)
 out['remark']=q['remark']+f" 来源：销售报价单_2026.8.1-10.11.xlsx/{q['sheet']}；原负责人：朱莉；原表客户：{q['customer']}；原表商机名称：{q['business']}；未填税率，保持空白。"
 if any(Decimal(x['price'])*Decimal(x['count'])!=Decimal(x['total']) for x in q['items']):out['remark']+=' 原表报价为四舍五入的成交均价，金额按原表明细小计保存。'
 assert len(out['remark'])<=1024
 quotes.append(out)
assert sum(Decimal(q['total']) for q in quotes)==Decimal('15392805.71')
plan=dict(owner_username='zhuli',owner_nickname='朱莉',status=0,source_sha256=source['sha256'],quotes=quotes,new_products=list(newproducts.values()),total='15392805.71')
(P/'local-plan.json').write_text(json.dumps(plan,ensure_ascii=False,indent=2),encoding='utf-8')
for filename,data in [('报价明细清单',rows),('产品匹配清单',list(productmap.values()))]:
 fields=list(data[0])
 with (P/f'{filename}.csv').open('w',encoding='utf-8-sig',newline='') as f:
  w=csv.DictWriter(f,fields);w.writeheader();w.writerows(data)
print('Prepared',len(quotes),'quotations,',len(rows),'lines,',len(productmap),'products, new products=',list(newproducts.values()))