import json,csv,collections,xml.etree.ElementTree as ET
from pathlib import Path
from decimal import Decimal,ROUND_HALF_UP
root=Path('scripts/stock-import-202609')
data=json.loads((root/'source.json').read_text(encoding='utf-8'),parse_float=Decimal)
plan=json.loads((root/'remote-plan.json').read_text(encoding='utf-8'))
db=ET.parse(root/'remote-snapshot.xml').getroot()
materials={m.findtext('code'):{c.tag:c.text for c in m} for m in db.find('materials')}
units={u.findtext('name').strip():int(u.findtext('id')) for u in db.find('units')}
unitcodes={u.findtext('code'):u.findtext('name').strip() for u in db.find('units')}
ledger=next(s for s in data if s['sheet']=='Sheet1')['rows']
detail=collections.defaultdict(list)
for r in next(s for s in data if s['sheet']=='整理表')['rows'][1:]:
    if len(r)>5 and isinstance(r[5],(int,Decimal)):detail[str(r[0]).strip()].append(r)
warehouses=[
 ('CPK01','成品仓','销售成品及完成包装的产品，按批次和效期管理；对应原表CPK01'),
 ('YLK','原料仓','生产原辅料、食品添加剂及散装原料；对应原表YLK'),
 ('NBCK','内包材仓','直接接触产品的内袋、瓶、盖、膜等包材；对应原表NBCK'),
 ('WBCK','外包材仓','外盒、外箱、标签、手提袋等外包装；对应原表WBCK'),
 ('CCCP','虫草冷链仓','鲜草、冻干草及虫草散料专用；对应原表CCCP；实际温湿度条件按品种储存标准执行'),
 ('BCPK','半成品仓','待继续加工或包装的中间产品；对应原表BCPK'),
 ('QC_PENDING','待检仓','到货待检、复检及质量待确认物料；合格后办理移库'),
 ('THK','退货及不合格品仓','退货、拒收及不合格品隔离存放；对应原表THK，检验处置后再移库'),
 ('WIP_VIRTUAL_WAREHOUSE','虚拟线边仓','已发至车间但尚未消耗的在制库存；对应原表CCCJ、YFK，生产领料与实际消耗分开记账')]
mapping={k:k for k,_,_ in warehouses};mapping.update(CCCJ='WIP_VIRTUAL_WAREHOUSE',YFK='WIP_VIRTUAL_WAREHOUSE')
def fallback(r):
    major=str(ledger[r['row']-1][2])
    if major=='8':
        name=r['name']
        return 'NBCK' if any(k in name for k in ('复合膜','卷膜','玻璃瓶','瓶盖','内袋','铝箔袋','封口膜','垫片','光片')) else 'WBCK'
    if major in ('6','7'):return 'CCCP' if any(k in r['name'] for k in ('虫草','鲜草','断草','肉节')) else 'YLK'
    if major in ('1','2','Q','U','T'):return 'CPK01'
    raise ValueError(('Unclassified',r))
def fmt(v):return format(Decimal(v).quantize(Decimal('.000001'),rounding=ROUND_HALF_UP),'f')
out=[];allocation=[]
for r in plan:
    m=materials.get(r['code']);quantity=Decimal(r['quantity']);srcunit=r['unit'];note=''
    if r['code']=='81013938':srcunit='克';note='台账单位个与整理表、包材盘点表GM冲突，按原表GM及系统克计量'
    targetunit=unitcodes.get(next((u.findtext('code') for u in db.find('units') if m and u.findtext('id')==m['unit_measure_id']),''),srcunit) if m else srcunit
    factor=Decimal(1)
    if srcunit!=targetunit:
        if srcunit=='公斤' and targetunit=='克':factor=Decimal(1000)
        elif srcunit=='克' and targetunit=='公斤':factor=Decimal('.001')
        else:
            assert srcunit in ('盒','套','罐','袋','个') and targetunit in ('个','张'),(r,targetunit)
            note+='；按同编码包装成品/包材的计数单位1:1录入'
    if r['code'] in ('Q2100196','Q2100197'):note+='；用户确认按原编码2992个，原表900ml散货袋装，与系统规格不一致'
    final=Decimal(fmt(quantity*factor));assert final>=0
    groups=collections.defaultdict(Decimal)
    sourceqty=Decimal(0)
    for dr in detail[r['code']]:
        loc=str(dr[4]).strip();qty=Decimal(dr[5])
        # Allocation uses source quantities as proportions, preserving ledger target quantity.
        sourceqty+=qty
        if not loc:loc=fallback(r)
        assert loc in mapping,(r['code'],loc)
        groups[mapping[loc]]+=qty
    if not groups:groups[fallback(r)]=Decimal(0)
    if any(v<0 for v in groups.values()):
        assert final==0,(r,groups)
        groups={max(groups,key=groups.get):Decimal(0)}
    positive={k:v for k,v in groups.items() if v>0}
    if final==0:
        groups={max(groups,key=groups.get):Decimal(0)}
    else:
        assert positive,(r,groups)
        groups=positive
    allocations={}
    if len(groups)==1:allocations[next(iter(groups))]=final
    else:
        weight=sum(groups.values());assert weight>0
        allocations={k:Decimal(fmt(final*v/weight)) for k,v in groups.items()}
        allocations[max(groups,key=groups.get)]+=final-sum(allocations.values())
    assert sum(allocations.values())==final
    row=dict(r,target_unit=targetunit,unit_id=int(m['unit_measure_id']) if m else units[srcunit],target_quantity=fmt(final),expected_id=int(m['id']) if m else 0,expected_name=m['name'] if m else '',expected_batch=int(m['is_batch_managed']) if m else 1,note=note,source_locations=','.join(sorted({str(dr[4]).strip() or '空' for dr in detail[r['code']]})))
    row['batch_code']='INIT202609-'+r['code']
    out.append(row)
    for wh,qty in allocations.items():allocation.append(dict(code=r['code'],name=r['name'],warehouse=wh,quantity=fmt(qty),unit=targetunit,batch_code=row['batch_code'],row=r['row'],note=note))
(root/'import-plan.json').write_text(json.dumps(dict(materials=out,stock=allocation,warehouses=warehouses),ensure_ascii=False,indent=2),encoding='utf-8')
with (root/'仓库分配清单.csv').open('w',encoding='utf-8-sig',newline='') as f:
    w=csv.DictWriter(f,fieldnames=list(allocation[0]));w.writeheader();w.writerows(allocation)
def q(v):
    if v is None:return 'NULL'
    if isinstance(v,(int,Decimal)):return str(v)
    return "'"+str(v).replace('\\','\\\\').replace("'","''").replace('\n','\\n').replace('\r','\\r')+"'"
sql=[]
sql.append('CREATE TEMPORARY TABLE stage_inventory_material(code VARCHAR(50) PRIMARY KEY,name VARCHAR(200),unit_id BIGINT,quantity DECIMAL(24,6),expected_id BIGINT,expected_name VARCHAR(200),batch_managed INT,batch_code VARCHAR(64),remark VARCHAR(500)) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci')
for r in out:
    remark=f"来源2026年9月份盘点表(虫草核对确认).xls Sheet1第{r['row']}行；结存{r['quantity']}{r['unit']}；{r['note']}"
    sql.append('INSERT INTO stage_inventory_material VALUES('+','.join(q(v) for v in (r['code'],r['name'],r['unit_id'],Decimal(r['target_quantity']),r['expected_id'],r['expected_name'],r['expected_batch'],r['batch_code'],remark)) +')')
sql.append('CREATE TEMPORARY TABLE stage_inventory_warehouse(code VARCHAR(100) PRIMARY KEY,name VARCHAR(100),remark VARCHAR(255)) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci')
for r in warehouses:sql.append('INSERT INTO stage_inventory_warehouse VALUES('+','.join(q(v) for v in r)+')')
sql.append('CREATE TEMPORARY TABLE stage_inventory_stock(code VARCHAR(50),warehouse_code VARCHAR(100),quantity DECIMAL(24,6),remark VARCHAR(255),PRIMARY KEY(code,warehouse_code)) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci')
for r in allocation:
    remark=f"2026年9月台账Sheet1第{r['row']}行，按结存覆盖；按原表库位分仓；INIT202609为期初汇总批次，不代表真实生产批次；{r['note']}"
    assert len(remark)<=255
    sql.append('INSERT INTO stage_inventory_stock VALUES('+','.join(q(v) for v in (r['code'],r['warehouse'],Decimal(r['quantity']),remark))+')')
(root/'remote-import.sql').write_text(';\n'.join(sql)+';\n',encoding='utf-8')
print('materials',len(out),'new',sum(not r['expected_id'] for r in out),'stock',len(allocation),'positive',sum(Decimal(r['quantity'])>0 for r in allocation),'warehouse allocation',dict(collections.Counter(r['warehouse'] for r in allocation)))