import java.sql.*;
import java.nio.file.*;
import java.io.*;
import java.util.*;
import java.util.regex.*;
class StockInventoryDatabase {
 static final String ACTOR="stock-import-202609";
 static void syncMaterialTypes(Connection c)throws Exception{
  c.setAutoCommit(false);
  try(var st=c.createStatement()){
   if(count(st,"SELECT GET_LOCK('stock-import-202609',10)")!=1)throw new Exception("Repair busy");
   if(count(st,"SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='mom-hm' AND table_name IN('mdm_item','mes_md_item','mes_wm_material_stock') AND engine='InnoDB'")!=3)throw new Exception("Transactional tables required");
   for(String sql:statements(Files.readString(Path.of("scripts/stock-import-202609/material-repair/verified-types-stage.sql"))))st.execute(sql);
   if(count(st,"SELECT COUNT(*) FROM stage_verified_types")!=837)throw new Exception("Unexpected reviewed plan");
   for(String sql:List.of("SELECT m.id FROM mdm_item m JOIN stage_verified_types s ON s.id=m.id FOR UPDATE","SELECT i.id FROM mes_md_item i JOIN stage_verified_types s ON s.id=i.mdm_item_id WHERE i.deleted=0 FOR UPDATE","SELECT v.id FROM mes_wm_material_stock v JOIN stage_verified_types s ON s.id=v.item_id WHERE v.deleted=0 FOR UPDATE","SELECT t.id FROM mes_md_item_type t WHERE t.id IN(SELECT target_category_id FROM stage_verified_types) FOR UPDATE")){try(var r=st.executeQuery(sql)){while(r.next()){} }}
   require(st,"SELECT COUNT(*) FROM stage_verified_types s LEFT JOIN mdm_item m ON m.id=s.id AND m.deleted=0 LEFT JOIN mes_md_item i ON i.mdm_item_id=s.id AND i.deleted=0 LEFT JOIN mes_md_item_type oldt ON oldt.id=s.expected_category_id AND oldt.deleted=0 LEFT JOIN mes_md_item_type t ON t.id=s.target_category_id AND t.deleted=0 WHERE m.id IS NULL OR i.id IS NULL OR oldt.id IS NULL OR t.id IS NULL OR m.code<>s.code OR m.name<>s.name OR NOT(m.item_type <=> s.expected_type) OR NOT(m.category_id <=> s.expected_category_id) OR oldt.code<>s.expected_category_code OR t.code<>s.target_category_code OR t.name<>s.target_category_name OR t.status<>0 OR NOT(m.is_batch_managed <=> s.expected_batch) OR NOT(i.batch_flag <=> s.expected_batch) OR NOT(i.item_type_id <=> m.category_id) OR NOT(i.name <=> m.name) OR NOT(i.specification <=> m.specification) OR NOT(i.unit_measure_id <=> m.unit_measure_id) OR s.target_type NOT IN(1,2,3,4)","Reviewed source or category mapping changed");
   if(count(st,"SELECT COUNT(*) FROM mes_md_item_type WHERE id=23 AND code='MAT06' AND name='半成品' AND deleted=0 AND status=0")!=1)throw new Exception("Verified semi-finished category unavailable");
   if(count(st,"SELECT COUNT(*) FROM mes_md_item i JOIN stage_verified_types s ON s.id=i.mdm_item_id WHERE i.deleted=0")!=837)throw new Exception("MES projections not unique");
   st.execute("CREATE TEMPORARY TABLE stock_type_before AS SELECT v.* FROM mes_wm_material_stock v JOIN stage_verified_types s ON s.id=v.item_id WHERE v.deleted=0");
   int changed=st.executeUpdate("UPDATE mdm_item m JOIN stage_verified_types s ON s.id=m.id SET m.item_type=s.target_type,m.category_id=s.target_category_id,m.updater='stock-material-repair',m.update_time=NOW() WHERE m.deleted=0 AND (m.item_type<>s.target_type OR m.category_id<>s.target_category_id)");
   int synced=st.executeUpdate("UPDATE mes_md_item i JOIN stage_verified_types s ON s.id=i.mdm_item_id SET i.item_type_id=s.target_category_id,i.updater='stock-material-repair',i.update_time=NOW() WHERE i.deleted=0 AND i.item_type_id<>s.target_category_id");
   int stocks=st.executeUpdate("UPDATE mes_wm_material_stock v JOIN stage_verified_types s ON s.id=v.item_id SET v.item_type_id=s.target_category_id,v.updater='stock-material-repair',v.update_time=NOW() WHERE v.deleted=0 AND v.item_type_id<>s.target_category_id");
   require(st,"SELECT COUNT(*) FROM stage_verified_types s JOIN mdm_item m ON m.id=s.id JOIN mes_md_item i ON i.mdm_item_id=s.id AND i.deleted=0 WHERE m.item_type<>s.target_type OR m.category_id<>s.target_category_id OR i.item_type_id<>s.target_category_id OR m.is_batch_managed<>s.expected_batch OR i.batch_flag<>s.expected_batch","Post-update mapping mismatch");
   require(st,"SELECT COUNT(*) FROM stock_type_before b LEFT JOIN mes_wm_material_stock v ON v.id=b.id WHERE v.id IS NULL OR NOT(v.quantity <=> b.quantity) OR NOT(v.frozen_quantity <=> b.frozen_quantity) OR NOT(v.reserved_quantity <=> b.reserved_quantity) OR NOT(v.in_transit_quantity <=> b.in_transit_quantity) OR NOT(v.item_id <=> b.item_id) OR NOT(v.batch_id <=> b.batch_id) OR NOT(v.warehouse_id <=> b.warehouse_id) OR NOT(v.location_id <=> b.location_id) OR NOT(v.area_id <=> b.area_id)","Stock quantities or positions changed");
   require(st,"SELECT COUNT(*) FROM mes_wm_material_stock v JOIN stage_verified_types s ON s.id=v.item_id WHERE v.deleted=0 AND v.item_type_id<>s.target_category_id","Stock category mismatch");
   c.commit();String result="Verified explicit mapping: MDM changed="+changed+", MES categories changed="+synced+", stock categories changed="+stocks+"; all 837 type/category/batch mappings verified; stock quantities and positions conserved\n";Files.writeString(Path.of("scripts/stock-import-202609/material-repair/type-result.txt"),result);System.out.print(result);
  }catch(Exception e){c.rollback();throw e;}finally{try(var st=c.createStatement()){st.execute("SELECT RELEASE_LOCK('stock-import-202609')");}c.setAutoCommit(true);}
 }
 static void fillExistingSpecifications(Connection c)throws Exception{
  c.setAutoCommit(false);
  try(var st=c.createStatement()){
   for(String sql:statements(Files.readString(Path.of("scripts/stock-import-202609/material-repair/existing-spec-stage.sql"))))st.execute(sql);
   try(var r=st.executeQuery("SELECT m.id FROM mdm_item m JOIN stage_existing_spec s ON s.id=m.id FOR UPDATE")){while(r.next()){} }
   require(st,"SELECT COUNT(*) FROM stage_existing_spec s LEFT JOIN mdm_item m ON m.id=s.id AND m.deleted=0 WHERE m.id IS NULL OR NOT(m.code COLLATE utf8mb4_unicode_ci <=> s.code COLLATE utf8mb4_unicode_ci) OR NOT(m.name COLLATE utf8mb4_unicode_ci <=> s.name COLLATE utf8mb4_unicode_ci) OR COALESCE(m.specification,'')<>''","Existing material changed since review");
   int filled=st.executeUpdate("UPDATE mdm_item m JOIN stage_existing_spec s ON s.id=m.id SET m.specification=s.specification,m.updater='stock-material-repair',m.update_time=NOW() WHERE m.deleted=0");
   int synced=st.executeUpdate("UPDATE mes_md_item i JOIN stage_existing_spec s ON s.id=i.mdm_item_id JOIN mdm_item m ON m.id=s.id SET i.specification=m.specification,i.updater='stock-material-repair',i.update_time=NOW() WHERE i.deleted=0");
   c.commit();String result="Existing source-confirmed missing specifications filled="+filled+", MES synchronized="+synced+"; conflicting source names preserved for review\n";Files.writeString(Path.of("scripts/stock-import-202609/material-repair/existing-spec-result.txt"),result);System.out.print(result);
  }catch(Exception e){c.rollback();throw e;}finally{c.setAutoCommit(true);}
 }
 static void repairMaterials(Connection c)throws Exception{
  c.setAutoCommit(false);
  try(var st=c.createStatement()){
   if(count(st,"SELECT GET_LOCK('stock-import-202609',10)")!=1)throw new Exception("Repair busy");
   if(count(st,"SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='mom-hm' AND table_name IN('mdm_item','mes_md_item','mes_md_item_type','mes_wm_material_stock','mes_wm_batch','mes_wm_transaction') AND engine='InnoDB'")!=6)throw new Exception("Transactional tables required");
   for(String sql:statements(Files.readString(Path.of("scripts/stock-import-202609/material-repair/repair-stage.sql"))))st.execute(sql);
   if(count(st,"SELECT COUNT(*) FROM stage_material_fix")!=582 || count(st,"SELECT COUNT(*) FROM stage_material_merge")!=10)throw new Exception("Unexpected repair plan");
   try(var r=st.executeQuery("SELECT m.id FROM mdm_item m WHERE m.id IN(SELECT id FROM stage_material_fix) OR m.id IN(SELECT new_id FROM stage_material_merge) FOR UPDATE")){while(r.next()){} }
   require(st,"SELECT COUNT(*) FROM stage_material_fix f LEFT JOIN mdm_item m ON m.id=f.id AND m.deleted=0 WHERE m.id IS NULL OR m.creator<>'stock-import-202609' OR NOT(m.code COLLATE utf8mb4_unicode_ci <=> f.code COLLATE utf8mb4_unicode_ci) OR NOT(m.name COLLATE utf8mb4_unicode_ci <=> f.old_name COLLATE utf8mb4_unicode_ci) OR NOT(m.specification COLLATE utf8mb4_unicode_ci <=> f.old_spec COLLATE utf8mb4_unicode_ci)","Material changed since review");
   require(st,"SELECT COUNT(*) FROM stage_material_merge s LEFT JOIN mdm_item m ON m.id=s.new_id AND m.deleted=0 WHERE m.id IS NULL OR NOT(m.code COLLATE utf8mb4_unicode_ci <=> s.new_code COLLATE utf8mb4_unicode_ci)","Canonical material unavailable");
   var referenceChecks=new ArrayList<String>();
   try(var r=st.executeQuery("SELECT table_name,column_name FROM information_schema.columns WHERE table_schema='mom-hm' AND data_type IN('bigint','int') AND column_name IN('item_id','mdm_item_id','product_id','bom_item_id','parent_item_id','sub_item_id','material_id')")){
    while(r.next()){String t=r.getString(1),col=r.getString(2);if(!t.matches("[A-Za-z0-9_]+")||!col.matches("[A-Za-z0-9_]+"))throw new Exception("Unsafe identifier");if(!Set.of("mes_wm_batch","mes_wm_material_stock","mes_wm_transaction").contains(t))referenceChecks.add("SELECT COUNT(*) FROM `"+t+"` WHERE `"+col+"` IN(SELECT old_id FROM stage_material_merge)");}
   }
   for(String sql:referenceChecks)require(st,sql,"Duplicate gained another business reference; review before merging");
   try(var r=st.executeQuery("SELECT id FROM mes_wm_material_stock WHERE deleted=0 FOR UPDATE")){while(r.next()){} }
   st.execute("CREATE TEMPORARY TABLE repair_stock_before AS SELECT * FROM mes_wm_material_stock WHERE deleted=0");
   st.execute("CREATE TEMPORARY TABLE repair_tx_before AS SELECT * FROM mes_wm_transaction WHERE deleted=0");
   if(count(st,"SELECT COUNT(*) FROM repair_stock_before WHERE creator='stock-import-202609'")!=855)throw new Exception("Imported stock count changed");
   require(st,"SELECT COUNT(*) FROM repair_stock_before v JOIN stage_material_merge s ON s.old_id=v.item_id WHERE v.quantity*s.factor<>ROUND(v.quantity*s.factor,6) OR v.frozen_quantity*s.factor<>ROUND(v.frozen_quantity*s.factor,6) OR v.reserved_quantity*s.factor<>ROUND(v.reserved_quantity*s.factor,6) OR v.in_transit_quantity*s.factor<>ROUND(v.in_transit_quantity*s.factor,6)","Unit conversion would lose precision");
   require(st,"SELECT COUNT(*) FROM repair_stock_before v JOIN stage_material_merge s ON s.old_id=v.item_id JOIN mes_wm_material_stock other ON other.deleted=0 AND other.item_id=s.new_id AND other.warehouse_id=v.warehouse_id AND other.location_id <=> v.location_id AND other.area_id <=> v.area_id AND other.batch_id <=> v.batch_id AND other.vendor_id <=> v.vendor_id WHERE other.id<>v.id","Destination stock collision");
   st.executeUpdate("INSERT INTO mes_md_item_type(code,name,parent_id,item_or_product,sort,status,remark,creator,updater) SELECT 'MAT06','半成品',2,'ITEM',6,0,'库存物料整理：区分半成品与原料','stock-material-repair','stock-material-repair' WHERE NOT EXISTS(SELECT 1 FROM mes_md_item_type WHERE code='MAT06')");
   require(st,"SELECT COUNT(*) FROM stage_material_fix f LEFT JOIN mes_md_item_type t ON t.code COLLATE utf8mb4_unicode_ci=f.type_code COLLATE utf8mb4_unicode_ci AND t.deleted=0 AND t.status=0 WHERE t.id IS NULL","Classification unavailable");
   int metadata=st.executeUpdate("UPDATE mdm_item m JOIN stage_material_fix f ON f.id=m.id JOIN mes_md_item_type t ON t.code COLLATE utf8mb4_unicode_ci=f.type_code COLLATE utf8mb4_unicode_ci AND t.deleted=0 SET m.name=f.name,m.specification=f.specification,m.category_id=t.id,m.updater='stock-material-repair',m.update_time=NOW() WHERE m.deleted=0");
   st.executeUpdate("UPDATE mdm_item m JOIN stage_material_merge s ON s.new_id=m.id SET m.is_batch_managed=1,m.category_id=COALESCE(m.category_id,(SELECT id FROM mes_md_item_type WHERE code='FG' AND deleted=0)),m.updater='stock-material-repair',m.update_time=NOW() WHERE m.deleted=0");
   int batches=st.executeUpdate("UPDATE mes_wm_batch b JOIN stage_material_merge s ON s.old_id=b.item_id SET b.item_id=s.new_id,b.remark=CONCAT(COALESCE(b.remark,''),'；',s.provenance),b.updater='stock-material-repair',b.update_time=NOW() WHERE b.deleted=0");
   int stocks=st.executeUpdate("UPDATE mes_wm_material_stock v JOIN stage_material_merge s ON s.old_id=v.item_id SET v.item_id=s.new_id,v.quantity=v.quantity*s.factor,v.frozen_quantity=v.frozen_quantity*s.factor,v.reserved_quantity=v.reserved_quantity*s.factor,v.in_transit_quantity=v.in_transit_quantity*s.factor,v.updater='stock-material-repair',v.update_time=NOW() WHERE v.deleted=0");
   int tx=st.executeUpdate("UPDATE mes_wm_transaction t JOIN stage_material_merge s ON s.old_id=t.item_id SET t.item_id=s.new_id,t.quantity=t.quantity*s.factor,t.updater='stock-material-repair',t.update_time=NOW() WHERE t.deleted=0");
   st.executeUpdate("UPDATE mdm_item m JOIN stage_material_merge s ON s.old_id=m.id SET m.deleted=1,m.status=1,m.remark=CONCAT(COALESCE(m.remark,''),'；重复归并至',s.new_code,'；原批次保留来源编码与产地'),m.updater='stock-material-repair',m.update_time=NOW()");
   st.execute("CREATE TEMPORARY TABLE repair_canonical AS SELECT DISTINCT m.* FROM mdm_item m JOIN mes_wm_material_stock v ON v.item_id=m.id AND v.deleted=0 WHERE v.creator='stock-import-202609' AND m.deleted=0");
   require(st,"SELECT COUNT(*) FROM repair_canonical m LEFT JOIN mes_md_item_type t ON t.id=m.category_id AND t.deleted=0 LEFT JOIN mdm_unit_measure u ON u.id=m.unit_measure_id AND u.deleted=0 WHERE t.id IS NULL OR u.id IS NULL OR m.is_batch_managed<>1","Canonical category/unit/batch configuration invalid");
   require(st,"SELECT COUNT(*) FROM repair_canonical m JOIN mes_md_item i ON i.id=m.id WHERE i.deleted=1 OR NOT(i.mdm_item_id <=> m.id) OR NOT(i.code COLLATE utf8mb4_unicode_ci <=> m.code COLLATE utf8mb4_unicode_ci)","MES primary key conflict");
   require(st,"SELECT COUNT(*) FROM repair_canonical m JOIN mes_md_item i ON i.code COLLATE utf8mb4_unicode_ci=m.code COLLATE utf8mb4_unicode_ci WHERE i.id<>m.id OR i.deleted=1","MES code conflict");
   int created=st.executeUpdate("INSERT INTO mes_md_item(id,mdm_item_id,code,name,specification,unit_measure_id,item_type_id,status,safe_stock_flag,min_stock,max_stock,high_value,batch_flag,remark,creator,updater) SELECT m.id,m.id,m.code,m.name,m.specification,m.unit_measure_id,m.category_id,m.status,IF(m.min_stock IS NOT NULL OR m.max_stock IS NOT NULL,1,0),m.min_stock,m.max_stock,COALESCE(m.high_value,0),m.is_batch_managed,LEFT(m.remark,255),'stock-material-repair','stock-material-repair' FROM repair_canonical m WHERE NOT EXISTS(SELECT 1 FROM mes_md_item i WHERE i.id=m.id)");
   int synced=st.executeUpdate("UPDATE mes_md_item i JOIN repair_canonical m ON m.id=i.mdm_item_id SET i.name=m.name,i.specification=m.specification,i.unit_measure_id=m.unit_measure_id,i.item_type_id=m.category_id,i.batch_flag=m.is_batch_managed,i.status=m.status,i.updater='stock-material-repair',i.update_time=NOW() WHERE i.deleted=0");
   st.executeUpdate("UPDATE mes_wm_material_stock v JOIN repair_canonical m ON m.id=v.item_id SET v.item_type_id=m.category_id,v.updater='stock-material-repair',v.update_time=NOW() WHERE v.deleted=0 AND NOT(v.item_type_id <=> m.category_id)");
   require(st,"SELECT COUNT(*) FROM repair_canonical m LEFT JOIN mes_md_item i ON i.mdm_item_id=m.id AND i.deleted=0 WHERE i.id IS NULL OR NOT(i.name COLLATE utf8mb4_unicode_ci <=> m.name COLLATE utf8mb4_unicode_ci) OR NOT(i.specification COLLATE utf8mb4_unicode_ci <=> m.specification COLLATE utf8mb4_unicode_ci) OR i.unit_measure_id<>m.unit_measure_id OR i.item_type_id<>m.category_id OR i.batch_flag<>m.is_batch_managed OR i.status<>m.status","MES sync verification failed");
   require(st,"SELECT COUNT(*) FROM repair_stock_before b LEFT JOIN stage_material_merge s ON s.old_id=b.item_id LEFT JOIN mes_wm_material_stock v ON v.id=b.id AND v.deleted=0 WHERE v.id IS NULL OR v.item_id<>COALESCE(s.new_id,b.item_id) OR NOT(v.quantity <=> b.quantity*COALESCE(s.factor,1)) OR NOT(v.frozen_quantity <=> b.frozen_quantity*COALESCE(s.factor,1)) OR NOT(v.reserved_quantity <=> b.reserved_quantity*COALESCE(s.factor,1)) OR NOT(v.in_transit_quantity <=> b.in_transit_quantity*COALESCE(s.factor,1)) OR NOT(v.warehouse_id <=> b.warehouse_id) OR NOT(v.location_id <=> b.location_id) OR NOT(v.area_id <=> b.area_id) OR NOT(v.batch_id <=> b.batch_id) OR NOT(v.vendor_id <=> b.vendor_id)","Stock conservation failed");
   require(st,"SELECT COUNT(*) FROM repair_tx_before b LEFT JOIN stage_material_merge s ON s.old_id=b.item_id LEFT JOIN mes_wm_transaction t ON t.id=b.id AND t.deleted=0 WHERE t.id IS NULL OR t.item_id<>COALESCE(s.new_id,b.item_id) OR NOT(t.quantity <=> b.quantity*COALESCE(s.factor,1)) OR t.material_stock_id<>b.material_stock_id OR NOT(t.batch_id <=> b.batch_id)","Transaction conservation failed");
   require(st,"SELECT COUNT(*) FROM mes_wm_material_stock v LEFT JOIN mdm_item m ON m.id=v.item_id AND m.deleted=0 LEFT JOIN mes_wm_batch b ON b.id=v.batch_id AND b.deleted=0 WHERE v.deleted=0 AND (m.id IS NULL OR b.id IS NULL OR b.item_id<>v.item_id)","Dangling stock reference");
   if(batches!=10||stocks!=10)throw new Exception("Expected ten batch/stock reassignments");
   c.commit();String result="Material metadata reviewed="+metadata+", duplicate materials soft-merged=10, batch references="+batches+", stock references="+stocks+", transactions reassigned="+tx+", MES created="+created+", MES synchronized="+synced+"; 855 stock records and physical quantities conserved; grams converted to kg at 0.001; source batch provenance retained\n";Files.writeString(Path.of("scripts/stock-import-202609/material-repair/result.txt"),result);System.out.print(result);
  }catch(Exception e){c.rollback();throw e;}finally{try(var st=c.createStatement()){st.execute("SELECT RELEASE_LOCK('stock-import-202609')");}c.setAutoCommit(true);}
 }
 static int count(Statement st,String sql)throws Exception{try(var r=st.executeQuery(sql)){r.next();return r.getInt(1);}}
 static void addPositions(Connection c)throws Exception{
  c.setAutoCommit(false);
  try(var st=c.createStatement()){
   if(count(st,"SELECT GET_LOCK('stock-import-202609',10)")!=1)throw new Exception("Import busy");
   if(count(st,"SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='mom-hm' AND table_name IN('mes_wm_warehouse_location','mes_wm_warehouse_area','mes_wm_material_stock','mes_wm_transaction') AND engine='InnoDB'")!=4)throw new Exception("Transactional tables required");
   st.execute("CREATE TEMPORARY TABLE stage_position AS SELECT id warehouse_id,code warehouse_code,name warehouse_name,IF(code='WIP_VIRTUAL_WAREHOUSE','WIP_VIRTUAL_LOCATION',CONCAT(code,'-L01')) location_code,IF(code='WIP_VIRTUAL_WAREHOUSE','WIP_VIRTUAL_AREA',CONCAT(code,'-A01')) area_code FROM mes_wm_warehouse WHERE deleted=0 AND creator='stock-import-202609'");
   if(count(st,"SELECT COUNT(*) FROM stage_position")!=9)throw new Exception("Expected nine imported warehouses");
   require(st,"SELECT COUNT(*) FROM stage_position s JOIN mes_wm_warehouse_location l ON l.code=s.location_code WHERE l.deleted=1 OR l.warehouse_id<>s.warehouse_id OR l.frozen=1","Location conflict");
   require(st,"SELECT COUNT(*) FROM stage_position s JOIN mes_wm_warehouse_location l ON l.warehouse_id=s.warehouse_id AND l.deleted=0 WHERE l.code<>s.location_code","Another location exists; cannot assume sole location");
   int locations=st.executeUpdate("INSERT INTO mes_wm_warehouse_location(code,name,warehouse_id,frozen,remark,creator,updater) SELECT s.location_code,CONCAT(s.warehouse_name,'默认库区'),s.warehouse_id,0,'本仓默认库区，承接9月台账库存','stock-import-202609','stock-import-202609' FROM stage_position s WHERE NOT EXISTS(SELECT 1 FROM mes_wm_warehouse_location l WHERE l.code=s.location_code)");
   require(st,"SELECT COUNT(*) FROM (SELECT s.warehouse_id FROM stage_position s JOIN mes_wm_warehouse_location l ON l.code=s.location_code AND l.warehouse_id=s.warehouse_id AND l.deleted=0 GROUP BY s.warehouse_id HAVING COUNT(*)<>1) z","Location ambiguity");
   require(st,"SELECT COUNT(*) FROM stage_position s JOIN mes_wm_warehouse_location l ON l.code=s.location_code AND l.deleted=0 JOIN mes_wm_warehouse_area a ON a.code=s.area_code WHERE a.deleted=1 OR a.location_id<>l.id OR a.frozen=1 OR a.status<>0","Area conflict");
   require(st,"SELECT COUNT(*) FROM stage_position s JOIN mes_wm_warehouse_location l ON l.code=s.location_code AND l.deleted=0 JOIN mes_wm_warehouse_area a ON a.location_id=l.id AND a.deleted=0 WHERE a.code<>s.area_code","Another area exists; cannot assume sole area");
   int areas=st.executeUpdate("INSERT INTO mes_wm_warehouse_area(code,name,location_id,status,frozen,allow_item_mixing,allow_batch_mixing,remark,creator,updater) SELECT s.area_code,CONCAT(s.warehouse_name,'默认库位'),l.id,0,0,1,1,'默认汇总库位，允许多物料多批次；虚拟线边仓库位用于记录车间在制库存','stock-import-202609','stock-import-202609' FROM stage_position s JOIN mes_wm_warehouse_location l ON l.code=s.location_code AND l.deleted=0 WHERE NOT EXISTS(SELECT 1 FROM mes_wm_warehouse_area a WHERE a.code=s.area_code)");
   st.execute("CREATE TEMPORARY TABLE stage_position_ids AS SELECT s.warehouse_id,l.id location_id,a.id area_id FROM stage_position s JOIN mes_wm_warehouse_location l ON l.code=s.location_code AND l.warehouse_id=s.warehouse_id AND l.deleted=0 JOIN mes_wm_warehouse_area a ON a.code=s.area_code AND a.location_id=l.id AND a.deleted=0");
   if(count(st,"SELECT COUNT(*) FROM stage_position_ids")!=9)throw new Exception("Position mapping incomplete");
   try(var r=st.executeQuery("SELECT v.id FROM mes_wm_material_stock v JOIN stage_position_ids s ON s.warehouse_id=v.warehouse_id WHERE v.deleted=0 AND v.creator='stock-import-202609' FOR UPDATE")){while(r.next()){} }
   st.execute("CREATE TEMPORARY TABLE stage_position_stock_before AS SELECT v.* FROM mes_wm_material_stock v JOIN stage_position_ids s ON s.warehouse_id=v.warehouse_id WHERE v.deleted=0 AND v.creator='stock-import-202609'");
   if(count(st,"SELECT COUNT(*) FROM stage_position_stock_before")!=855)throw new Exception("Imported stock count changed");
   require(st,"SELECT COUNT(*) FROM stage_position_stock_before v JOIN stage_position_ids s ON s.warehouse_id=v.warehouse_id WHERE (v.location_id IS NOT NULL AND v.location_id<>s.location_id) OR (v.area_id IS NOT NULL AND v.area_id<>s.area_id)","Stock already assigned elsewhere");
   require(st,"SELECT COUNT(*) FROM stage_position_stock_before v JOIN stage_position_ids s ON s.warehouse_id=v.warehouse_id JOIN mes_wm_material_stock other ON other.warehouse_id=v.warehouse_id AND other.item_id=v.item_id AND other.batch_id <=> v.batch_id AND other.vendor_id <=> v.vendor_id AND other.location_id=s.location_id AND other.area_id=s.area_id AND other.deleted=0 WHERE other.id<>v.id","Destination stock key collision");
   int stocks=st.executeUpdate("UPDATE mes_wm_material_stock v JOIN stage_position_ids s ON s.warehouse_id=v.warehouse_id SET v.location_id=s.location_id,v.area_id=s.area_id,v.update_time=NOW() WHERE v.deleted=0 AND v.creator='stock-import-202609' AND (NOT(v.location_id <=> s.location_id) OR NOT(v.area_id <=> s.area_id))");
   int transactions=st.executeUpdate("UPDATE mes_wm_transaction t JOIN stage_position_stock_before v ON v.id=t.material_stock_id JOIN stage_position_ids s ON s.warehouse_id=t.warehouse_id SET t.location_id=s.location_id,t.area_id=s.area_id,t.update_time=NOW() WHERE t.deleted=0 AND t.creator='stock-import-202609' AND (NOT(t.location_id <=> s.location_id) OR NOT(t.area_id <=> s.area_id))");
   require(st,"SELECT COUNT(*) FROM stage_position_stock_before b JOIN mes_wm_material_stock v ON v.id=b.id JOIN stage_position_ids s ON s.warehouse_id=v.warehouse_id WHERE NOT(v.quantity <=> b.quantity) OR NOT(v.batch_id <=> b.batch_id) OR v.item_id<>b.item_id OR NOT(v.location_id <=> s.location_id) OR NOT(v.area_id <=> s.area_id)","Position or quantity verification failed");
   require(st,"SELECT COUNT(*) FROM mes_wm_transaction t JOIN stage_position_stock_before b ON b.id=t.material_stock_id JOIN mes_wm_material_stock v ON v.id=b.id WHERE t.deleted=0 AND t.creator='stock-import-202609' AND (NOT(t.location_id <=> v.location_id) OR NOT(t.area_id <=> v.area_id))","Transaction position verification failed");
   c.commit();String result="remote mom-hm positions committed: new locations="+locations+", new areas="+areas+", enriched stock="+stocks+", enriched import transactions="+transactions+"; all 855 quantities and batches unchanged\n";Files.writeString(Path.of("scripts/stock-import-202609/positions-result.txt"),result);System.out.print(result);
  }catch(Exception e){c.rollback();throw e;}finally{try(var st=c.createStatement()){st.execute("SELECT RELEASE_LOCK('stock-import-202609')");}c.setAutoCommit(true);}
 }
 static void require(Statement st,String sql,String message)throws Exception{if(count(st,sql)!=0)throw new Exception(message);}
 static List<String> statements(String text){var out=new ArrayList<String>();var b=new StringBuilder();boolean quoted=false;for(int i=0;i<text.length();i++){char ch=text.charAt(i);if(ch=='\\' && quoted && i+1<text.length()){b.append(ch).append(text.charAt(++i));continue;}if(ch=='\''){if(quoted && i+1<text.length() && text.charAt(i+1)=='\''){b.append(ch).append(text.charAt(++i));continue;}quoted=!quoted;}if(ch==';' && !quoted){if(!b.toString().isBlank())out.add(b.toString());b.setLength(0);}else b.append(ch);}if(!b.toString().isBlank())out.add(b.toString());return out;}
 static void importStock(Connection c)throws Exception{
  c.setAutoCommit(false);
  try(var st=c.createStatement()){
   if(count(st,"SELECT GET_LOCK('stock-import-202609',10)")!=1)throw new Exception("Import already running");
   if(count(st,"SELECT COUNT(*) FROM mes_wm_material_stock WHERE deleted=0 AND creator='stock-import-202609' AND (location_id IS NOT NULL OR area_id IS NOT NULL)")>0)throw new Exception("Opening stock already assigned to warehouse positions; original null-position import is disabled to prevent duplication");
   if(count(st,"SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='mom-hm' AND table_name IN('mdm_item','mes_wm_warehouse','mes_wm_batch','mes_wm_material_stock','mes_wm_transaction') AND engine='InnoDB'")!=5)throw new Exception("Transactional tables required");
   for(var sql:statements(Files.readString(Path.of("scripts/stock-import-202609/remote-import.sql"))))st.execute(sql);
   if(count(st,"SELECT COUNT(*) FROM stage_inventory_material")!=843 || count(st,"SELECT COUNT(*) FROM stage_inventory_stock")!=855 || count(st,"SELECT COUNT(*) FROM stage_inventory_warehouse")!=9)throw new Exception("Unexpected staged counts");
   try(var r=st.executeQuery("SELECT m.id FROM mdm_item m JOIN stage_inventory_material s ON s.code=m.code WHERE m.deleted=0 FOR UPDATE")){while(r.next()){} }
   require(st,"SELECT COUNT(*) FROM stage_inventory_material s LEFT JOIN mdm_item m ON m.id=s.expected_id AND m.code=s.code AND m.deleted=0 WHERE s.expected_id<>0 AND (m.id IS NULL OR NOT(m.name <=> s.expected_name) OR m.unit_measure_id<>s.unit_id OR m.is_batch_managed<>s.batch_managed OR m.status<>0)","Existing material changed since review");
   require(st,"SELECT COUNT(*) FROM (SELECT s.code FROM stage_inventory_material s JOIN mdm_item m ON m.code=s.code AND m.deleted=0 GROUP BY s.code HAVING COUNT(*)>1) a","Duplicate active material code");
   require(st,"SELECT COUNT(*) FROM stage_inventory_material s JOIN mdm_item m ON m.code=s.code WHERE s.expected_id=0 AND (m.deleted=1 OR m.status<>0 OR NOT(m.name <=> s.name) OR m.unit_measure_id<>s.unit_id OR m.is_batch_managed<>s.batch_managed)","New material code conflict");
   require(st,"SELECT COUNT(*) FROM stage_inventory_material s LEFT JOIN mdm_unit_measure u ON u.id=s.unit_id AND u.deleted=0 AND u.status=0 WHERE u.id IS NULL","Unit unavailable");
   require(st,"SELECT COUNT(*) FROM stage_inventory_warehouse s JOIN mes_wm_warehouse w ON w.code=s.code WHERE w.deleted=1 OR w.frozen=1 OR NOT(w.name <=> s.name)","Warehouse conflict");
   int wh=st.executeUpdate("INSERT INTO mes_wm_warehouse(code,name,remark,frozen,creator,updater) SELECT s.code,s.name,s.remark,0,'"+ACTOR+"','"+ACTOR+"' FROM stage_inventory_warehouse s WHERE NOT EXISTS(SELECT 1 FROM mes_wm_warehouse w WHERE w.code=s.code)");
   int mdm=st.executeUpdate("INSERT INTO mdm_item(code,name,unit_measure_id,item_type,status,is_batch_managed,remark,creator,updater) SELECT s.code,s.name,s.unit_id,1,0,1,s.remark,'"+ACTOR+"','"+ACTOR+"' FROM stage_inventory_material s WHERE NOT EXISTS(SELECT 1 FROM mdm_item m WHERE m.code=s.code AND m.deleted=0)");
   require(st,"SELECT COUNT(*) FROM (SELECT s.code FROM stage_inventory_warehouse s JOIN mes_wm_warehouse w ON w.code=s.code AND w.deleted=0 GROUP BY s.code HAVING COUNT(*)<>1) a","Warehouse ambiguity");
   require(st,"SELECT COUNT(*) FROM stage_inventory_material s JOIN mdm_item m ON m.code=s.code AND m.deleted=0 JOIN mes_wm_batch b ON b.code=s.batch_code WHERE b.deleted=1 OR b.item_id<>m.id OR b.remark IS NULL OR b.remark NOT LIKE '2026年9月库存台账期初汇总%'","Batch conflict");
   // Batch codes identify opening balances, never invent production dates or quality results.
   int batches=st.executeUpdate("INSERT INTO mes_wm_batch(code,item_id,receipt_date,remark,creator,updater) SELECT s.batch_code,m.id,'2026-09-30 23:59:59','2026年9月库存台账期初汇总；按物料汇总跨原生产批次，非真实生产批次；不填生产日期、有效期和质检结果','"+ACTOR+"','"+ACTOR+"' FROM stage_inventory_material s JOIN mdm_item m ON m.code=s.code AND m.deleted=0 WHERE NOT EXISTS(SELECT 1 FROM mes_wm_batch b WHERE b.code=s.batch_code)");
   require(st,"SELECT COUNT(*) FROM (SELECT s.batch_code FROM stage_inventory_material s JOIN mes_wm_batch b ON b.code=s.batch_code AND b.deleted=0 GROUP BY s.batch_code HAVING COUNT(*)<>1) a","Batch ambiguity");
   String join=" FROM stage_inventory_stock s JOIN stage_inventory_material sm ON sm.code=s.code JOIN mdm_item m ON m.code=s.code AND m.deleted=0 JOIN mes_wm_warehouse w ON w.code=s.warehouse_code AND w.deleted=0 JOIN mes_wm_batch b ON b.code=sm.batch_code AND b.item_id=m.id AND b.deleted=0";
   String stockJoin=" JOIN mes_wm_material_stock v ON v.item_id=m.id AND v.warehouse_id=w.id AND v.batch_id=b.id AND v.location_id IS NULL AND v.area_id IS NULL AND v.vendor_id IS NULL";
   try(var r=st.executeQuery("SELECT v.id"+join+stockJoin+" WHERE v.deleted=0 FOR UPDATE")){while(r.next()){} }
   require(st,"SELECT COUNT(*)"+join+stockJoin+" WHERE v.deleted=1 OR v.frozen=1 OR COALESCE(v.frozen_quantity,0)+COALESCE(v.reserved_quantity,0)>s.quantity OR COALESCE(v.in_transit_quantity,0)<>0","Stock frozen/reserved/deleted/in transit conflict");
   require(st,"SELECT COUNT(*) FROM (SELECT s.code,s.warehouse_code"+join+stockJoin+" WHERE v.deleted=0 GROUP BY s.code,s.warehouse_code HAVING COUNT(*)>1) a","Duplicate stock key");
   int stocks=st.executeUpdate("INSERT INTO mes_wm_material_stock(item_type_id,item_id,warehouse_id,batch_id,batch_code,quantity,frozen_quantity,reserved_quantity,in_transit_quantity,frozen,receipt_time,remark,creator,updater) SELECT m.category_id,m.id,w.id,b.id,b.code,0,0,0,0,0,'2026-09-30 23:59:59',s.remark,'"+ACTOR+"','"+ACTOR+"'"+join+" WHERE NOT EXISTS(SELECT 1 FROM mes_wm_material_stock v WHERE v.item_id=m.id AND v.warehouse_id=w.id AND v.batch_id=b.id AND v.location_id IS NULL AND v.area_id IS NULL AND v.vendor_id IS NULL AND v.deleted=0)");
   st.execute("CREATE TEMPORARY TABLE stage_inventory_delta AS SELECT v.id stock_id,m.id item_id,w.id warehouse_id,b.id batch_id,b.code batch_code,s.quantity target_quantity,s.quantity-COALESCE(v.quantity,0) delta"+join+stockJoin+" WHERE v.deleted=0");
   if(count(st,"SELECT COUNT(*) FROM stage_inventory_delta")!=855)throw new Exception("Stock preparation incomplete");
   int tx=st.executeUpdate("INSERT INTO mes_wm_transaction(type,biz_type,biz_id,biz_code,biz_line_id,material_stock_id,item_id,quantity,batch_id,batch_code,warehouse_id,transaction_time,receipt_time,creator,updater) SELECT IF(d.delta>0,1,2),IF(d.delta>0,126,127),d.stock_id,CONCAT('STOCK_IMPORT_',d.stock_id),d.stock_id,d.stock_id,d.item_id,d.delta,d.batch_id,d.batch_code,d.warehouse_id,NOW(),'2026-09-30 23:59:59','"+ACTOR+"','"+ACTOR+"' FROM stage_inventory_delta d WHERE d.delta<>0");
   int updated=st.executeUpdate("UPDATE mes_wm_material_stock v JOIN stage_inventory_delta d ON d.stock_id=v.id SET v.quantity=d.target_quantity,v.updater='"+ACTOR+"',v.update_time=NOW() WHERE d.delta<>0");
   if(tx!=updated)throw new Exception("Transaction/stock change mismatch");
   require(st,"SELECT COUNT(*)"+join+stockJoin+" WHERE v.deleted=0 AND (NOT(v.quantity <=> s.quantity) OR NOT(v.batch_code <=> b.code))","Stock quantity verification failed");
   require(st,"SELECT COUNT(*) FROM (SELECT sm.code,sm.quantity,SUM(v.quantity) actual FROM stage_inventory_material sm JOIN stage_inventory_stock s ON s.code=sm.code JOIN mdm_item m ON m.code=sm.code AND m.deleted=0 JOIN mes_wm_warehouse w ON w.code=s.warehouse_code AND w.deleted=0 JOIN mes_wm_batch b ON b.code=sm.batch_code AND b.deleted=0 JOIN mes_wm_material_stock v ON v.item_id=m.id AND v.warehouse_id=w.id AND v.batch_id=b.id AND v.location_id IS NULL AND v.area_id IS NULL AND v.vendor_id IS NULL AND v.deleted=0 GROUP BY sm.code,sm.quantity HAVING actual<>sm.quantity) a","Material conservation failed");
   require(st,"SELECT COUNT(*) FROM stage_inventory_delta d JOIN mes_wm_material_stock v ON v.id=d.stock_id WHERE d.delta<>0 AND NOT EXISTS(SELECT 1 FROM mes_wm_transaction t WHERE t.material_stock_id=v.id AND t.quantity=d.delta AND t.creator='"+ACTOR+"' AND t.deleted=0)","Transaction verification failed");
   if(count(st,"SELECT COUNT(*)"+join+stockJoin+" WHERE v.deleted=0 AND v.quantity<>s.quantity")!=0)throw new Exception("Idempotency failed");
   c.commit();String result="remote mom-hm committed: warehouses="+wh+", materials="+mdm+", opening batches="+batches+", stock records="+stocks+", changed quantities="+updated+", transactions="+tx+"; 843 material totals / 855 warehouse balances verified; repeat import has zero quantity differences\n";
   Files.writeString(Path.of("scripts/stock-import-202609/remote-result.txt"),result);System.out.print(result);
  }catch(Exception e){c.rollback();throw e;}finally{try(var st=c.createStatement()){st.execute("SELECT RELEASE_LOCK('stock-import-202609')");}c.setAutoCommit(true);}
 }
 static String cv(String b,String k)throws Exception{var m=Pattern.compile("(?m)^\\s+"+k+":\\s*([^\\r\\n#]+)").matcher(b);if(!m.find())throw new Exception("Missing configuration");return m.group(1).trim().replaceAll("^[\"']|[\"']$", "");}
 static String esc(String s){return s.replace("&","&amp;").replace("<","&lt;").replace(">","&gt;");}
 public static void main(String[] a)throws Exception{
  String target=a[0],url,user,password;
  if(target.equals("remote")){url="jdbc:mysql://36.213.150.237:10000/mom-hm?characterEncoding=UTF-8&allowPublicKeyRetrieval=true&useSSL=false&connectTimeout=10000&socketTimeout=60000";user="root";password=new BufferedReader(new InputStreamReader(System.in)).readLine();}
  else{String config=Files.readString(Path.of("yudao-server/src/main/resources/application-local.yaml"));String b=config.substring(config.indexOf("master:"));if(b.contains("slave:"))b=b.substring(0,b.indexOf("slave:"));url=cv(b,"url");user=cv(b,"username");password=cv(b,"password");}
  try(var c=DriverManager.getConnection(url,user,password);var st=c.createStatement()){
   try(var r=st.executeQuery("SELECT DATABASE()")){r.next();if(!"mom-hm".equals(r.getString(1)))throw new Exception("Wrong database");}
   if(a.length>1 && a[1].equals("import")){if(!target.equals("remote"))throw new Exception("Only remote import authorized");importStock(c);return;}
   if(a.length>1 && a[1].equals("positions")){if(!target.equals("remote"))throw new Exception("Only remote positions authorized");addPositions(c);return;}
   if(a.length>1 && a[1].equals("repair-materials")){if(!target.equals("remote"))throw new Exception("Only remote repair authorized");repairMaterials(c);return;}
   if(a.length>1 && a[1].equals("fill-existing-spec")){if(!target.equals("remote"))throw new Exception("Only remote repair authorized");fillExistingSpecifications(c);return;}
   if(a.length>1 && a[1].equals("sync-material-types")){if(!target.equals("remote"))throw new Exception("Only remote repair authorized");syncMaterialTypes(c);return;}
   var queries=new LinkedHashMap<String,String>();
   queries.put("tables","SELECT table_name,engine FROM information_schema.tables WHERE table_schema='mom-hm'");
   queries.put("indexes","SELECT table_name,index_name,non_unique,column_name FROM information_schema.statistics WHERE table_schema='mom-hm' AND table_name IN('mdm_item','mes_wm_batch','mes_wm_warehouse','mes_wm_material_stock') ORDER BY table_name,index_name,seq_in_index");
   queries.put("columns","SELECT table_name,column_name,column_type,is_nullable,column_default FROM information_schema.columns WHERE table_schema='mom-hm' AND (table_name LIKE 'mes_wm_%' OR table_name IN('mdm_item','mdm_unit_measure')) ORDER BY table_name,ordinal_position");
   queries.put("materials","SELECT * FROM mdm_item WHERE deleted=0");
   queries.put("mesMaterials","SELECT * FROM mes_md_item");
   queries.put("mesUnits","SELECT * FROM mes_md_unit_measure WHERE deleted=0");
   queries.put("mesTypes","SELECT * FROM mes_md_item_type WHERE deleted=0");
   queries.put("itemTypeDict","SELECT dict_type,value,label FROM system_dict_data WHERE deleted=0 AND (dict_type LIKE '%item_type%' OR dict_type LIKE '%item_or_product%')");
   queries.put("searchChecks","SELECT id,code,name,specification,item_type_id,batch_flag FROM mes_md_item t WHERE deleted=0 AND (REPLACE(REPLACE(REPLACE(CONCAT(COALESCE(t.name,''),COALESCE(t.specification,'')), ' ', ''), '-', ''), '克', 'g') LIKE '%黑果枸杞60g%' OR (t.name LIKE '%洞燕碗燕礼盒底托套装%' AND t.specification LIKE '%碗装%'))");
   queries.put("mesItemColumns","SELECT column_name,column_type,is_nullable,column_default FROM information_schema.columns WHERE table_schema='mom-hm' AND table_name='mes_md_item' ORDER BY ordinal_position");
   queries.put("itemReferenceColumns","SELECT table_name,column_name,column_type FROM information_schema.columns WHERE table_schema='mom-hm' AND (column_name IN('item_id','mdm_item_id','product_id','component_id','parent_item_id','sub_item_id','material_id') OR column_name LIKE '%item_id') ORDER BY table_name,column_name");
   var referenceQueries=new ArrayList<String>();
   try(var ref=st.executeQuery("SELECT table_name,column_name FROM information_schema.columns WHERE table_schema='mom-hm' AND data_type IN('bigint','int') AND column_name IN('item_id','mdm_item_id','product_id','bom_item_id','parent_item_id','sub_item_id','material_id')")){
    while(ref.next()){String table=ref.getString(1),col=ref.getString(2);if(!table.matches("[A-Za-z0-9_]+")||!col.matches("[A-Za-z0-9_]+"))throw new Exception("Unsafe identifier");referenceQueries.add("SELECT '"+table+"' table_name,'"+col+"' column_name,"+col+" item_id,COUNT(*) n FROM `"+table+"` WHERE `"+col+"` IN(969,970,971,972,1003,1004,1006,1009,1043,1528) GROUP BY `"+col+"`");}
   }
   if(!referenceQueries.isEmpty())queries.put("duplicateReferences",String.join(" UNION ALL ",referenceQueries));
   queries.put("categories","SELECT * FROM mdm_item_category WHERE deleted=0");
   queries.put("deletedMaterials","SELECT id,code,name,unit_measure_id FROM mdm_item WHERE deleted=1");
   queries.put("units","SELECT * FROM mdm_unit_measure WHERE deleted=0");
   queries.put("warehouses","SELECT * FROM mes_wm_warehouse WHERE deleted=0");
   queries.put("locations","SELECT * FROM mes_wm_warehouse_location WHERE deleted=0");
   queries.put("areas","SELECT * FROM mes_wm_warehouse_area WHERE deleted=0");
   queries.put("stock","SELECT * FROM mes_wm_material_stock WHERE deleted=0");
   queries.put("batches","SELECT * FROM mes_wm_batch WHERE deleted=0");
   queries.put("transactions","SELECT * FROM mes_wm_transaction WHERE deleted=0 ORDER BY id DESC LIMIT 1000");
   StringBuilder x=new StringBuilder("<database>");
   for(var e:queries.entrySet()){
    x.append('<').append(e.getKey()).append('>');try(var r=st.executeQuery(e.getValue())){var m=r.getMetaData();while(r.next()){x.append("<row>");for(int j=1;j<=m.getColumnCount();j++){var k=m.getColumnLabel(j);x.append('<').append(k).append('>');String v=r.getString(j);if(v!=null)x.append(esc(v));x.append("</").append(k).append('>');}x.append("</row>");}}x.append("</").append(e.getKey()).append('>');
   }
   x.append("</database>");String suffix=a.length>1?"-"+a[1]:"";Files.writeString(Path.of("scripts/stock-import-202609/"+target+suffix+"-snapshot.xml"),x.toString());System.out.println(target+suffix+" stock snapshot saved");
  }
 }
}