import java.sql.*;
import java.nio.file.*;
import java.io.*;
import java.util.*;
import java.util.regex.*;
class BomDatabase {
 static int count(Statement st,String q)throws Exception{try(var r=st.executeQuery(q)){r.next();return r.getInt(1);}}
 static void dfs(long n,Map<Long,Set<Long>> graph,Set<Long> path,Set<Long> done)throws Exception{if(path.contains(n))throw new Exception("BOM cycle detected");if(done.contains(n))return;path.add(n);for(long child:graph.getOrDefault(n,Set.of()))dfs(child,graph,path,done);path.remove(n);done.add(n);}
 static void importBom(Connection c,Statement st,String target)throws Exception{
  if(count(st,"SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='mom-hm' AND table_name='mes_md_product_bom' AND engine='InnoDB'")!=1)throw new Exception("Transactional BOM table required");
  c.setAutoCommit(false);
  try{
   String script=Files.readString(Path.of("scripts/bom-import/"+target+"-import.sql"));
   for(String sql:script.split(";"))if(!sql.isBlank())st.executeUpdate(sql.trim());
   String materialMismatch="p.id IS NULL OR c.id IS NULL OR p.deleted=1 OR c.deleted=1 OR p.status<>0 OR c.status<>0 OR NOT(p.code <=> s.parent_code) OR NOT(c.code <=> s.child_code) OR NOT(p.name <=> s.parent_name) OR NOT(c.name <=> s.child_name) OR NOT(p.unit_measure_id <=> s.parent_unit) OR NOT(c.unit_measure_id <=> s.child_unit)";
   if(count(st,"SELECT COUNT(*) FROM stage_bom_import s LEFT JOIN mdm_item p ON p.id=s.parent_id LEFT JOIN mdm_item c ON c.id=s.child_id WHERE "+materialMismatch)>0){
    try(var r=st.executeQuery("SELECT s.parent_code,s.child_code,p.name,s.parent_name,c.name,s.child_name,p.unit_measure_id,s.parent_unit,c.unit_measure_id,s.child_unit FROM stage_bom_import s LEFT JOIN mdm_item p ON p.id=s.parent_id LEFT JOIN mdm_item c ON c.id=s.child_id WHERE "+materialMismatch+" LIMIT 8")){while(r.next()){for(int i=1;i<=10;i++)System.out.print(r.getString(i)+" | ");System.out.println();}}
    throw new Exception("Material changed since snapshot");
   }
   if(count(st,"SELECT COUNT(*) FROM stage_bom_import s JOIN mes_md_product_bom b ON b.item_id=s.parent_id AND b.bom_item_id=s.child_id AND b.deleted=0 WHERE s.action=0 OR (s.action=1 AND (b.id<>s.existing_id OR NOT(b.quantity <=> s.old_quantity))) OR (s.action=2 AND NOT(b.quantity <=> s.quantity))")>0)throw new Exception("Existing BOM changed since snapshot");
   if(count(st,"SELECT COUNT(*) FROM stage_bom_import s LEFT JOIN mes_md_product_bom b ON b.item_id=s.parent_id AND b.bom_item_id=s.child_id AND b.deleted=0 WHERE s.action IN(1,2) AND b.id IS NULL")>0)throw new Exception("Existing BOM removed since snapshot");
   Map<Long,Set<Long>> graph=new HashMap<>();
   try(var r=st.executeQuery("SELECT item_id,bom_item_id FROM mes_md_product_bom WHERE deleted=0 UNION SELECT parent_id,child_id FROM stage_bom_import")){while(r.next())graph.computeIfAbsent(r.getLong(1),k->new HashSet<>()).add(r.getLong(2));}
   Set<Long> done=new HashSet<>();for(long n:graph.keySet())dfs(n,graph,new HashSet<>(),done);
   String insert="INSERT INTO mes_md_product_bom(item_id,bom_item_id,quantity,status,remark,creator,updater) SELECT s.parent_id,s.child_id,s.quantity,0,s.remark,'bom-import-20261012','bom-import-20261012' FROM stage_bom_import s WHERE s.action=0 AND NOT EXISTS(SELECT 1 FROM mes_md_product_bom b WHERE b.item_id=s.parent_id AND b.bom_item_id=s.child_id AND b.deleted=0)";
   String update="UPDATE mes_md_product_bom b JOIN stage_bom_import s ON b.id=s.existing_id AND s.action=1 SET b.quantity=s.quantity,b.status=0,b.remark=s.remark,b.updater='bom-import-20261012' WHERE b.deleted=0 AND b.quantity=s.old_quantity";
   int inserted=st.executeUpdate(insert),updated=st.executeUpdate(update);
   if(inserted!=count(st,"SELECT COUNT(*) FROM stage_bom_import WHERE action=0") || updated!=count(st,"SELECT COUNT(*) FROM stage_bom_import WHERE action=1"))throw new Exception("Affected row count mismatch");
   if(count(st,"SELECT COUNT(*) FROM stage_bom_import s LEFT JOIN mes_md_product_bom b ON b.item_id=s.parent_id AND b.bom_item_id=s.child_id AND b.deleted=0 WHERE b.id IS NULL OR NOT(b.quantity <=> s.quantity) OR (s.action IN(0,1) AND (b.status<>0 OR NOT(b.remark <=> s.remark)))")>0)throw new Exception("BOM content verification failed");
   if(count(st,"SELECT COUNT(*) FROM (SELECT s.parent_id,s.child_id,COUNT(*) n FROM stage_bom_import s JOIN mes_md_product_bom b ON b.item_id=s.parent_id AND b.bom_item_id=s.child_id AND b.deleted=0 GROUP BY s.parent_id,s.child_id HAVING n<>1) x")>0)throw new Exception("Duplicate BOM relation");
   if(st.executeUpdate(insert)!=0 || st.executeUpdate(update)!=0)throw new Exception("Idempotency verification failed");
   int total=count(st,"SELECT COUNT(*) FROM stage_bom_import"),parents=count(st,"SELECT COUNT(DISTINCT parent_id) FROM stage_bom_import");
   c.commit();String result=target+" mom-hm committed: inserted="+inserted+", updated="+updated+", verified relations="+total+", parents="+parents+"; graph/content/duplicate/idempotency checks passed\n";Files.writeString(Path.of("scripts/bom-import/"+target+"-result.txt"),result);System.out.print(result);
  }catch(Exception e){c.rollback();throw e;}
 }
 static String configValue(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;").replace("\"","&quot;");}
 public static void main(String[] args)throws Exception{
  String target=args[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=configValue(b,"url");user=configValue(b,"username");password=configValue(b,"password");}
  try(var c=DriverManager.getConnection(url,user,password);var st=c.createStatement()){
   try(var r=st.executeQuery("SELECT DATABASE()")){r.next();if(!r.getString(1).equals("mom-hm"))throw new Exception("Wrong database");}
   if(args.length>1 && args[1].equals("inspect-parent")){try(var r=st.executeQuery("SELECT id,code,name,unit_measure_id,category_id,deleted FROM mdm_item WHERE code='TC0020'")){while(r.next())System.out.println(r.getLong(1)+" "+r.getString(2)+" "+r.getString(3)+" unit="+r.getLong(4)+" category="+r.getLong(5)+" deleted="+r.getBoolean(6));}return;}
   if(args.length>1 && args[1].equals("supplement-parent")){
    if(!target.equals("local"))throw new Exception("Supplement only local missing parent");
    if(count(st,"SELECT COUNT(*) FROM mdm_item WHERE code='TC0020'")==0){
     int n=st.executeUpdate("INSERT INTO mdm_item(code,name,category_id,unit_measure_id,item_type,status,remark,creator,updater) SELECT 'TC0020','黑果枸杞-120克',category_id,unit_measure_id,item_type,0,'来源：传渠产品BOMxml(1).xlsx；与远程现有 TC0020 成品一致','bom-import-20261012','bom-import-20261012' FROM mdm_item WHERE code='TC0019' AND deleted=0 AND status=0 AND unit_measure_id=24");
     if(n!=1)throw new Exception("Missing parent supplement failed");System.out.println("Local TC0020 parent supplemented");
    }else throw new Exception("TC0020 code already exists; no overwrite");
   }
   if(args.length>1 && args[1].equals("import")){importBom(c,st,target);return;}
   String[] names={"materials","units","bom"};String[] queries={"SELECT id,code,name,specification,item_type,status,unit_measure_id,unit_measure_id2,unit_measure_rate1,unit_measure_id3,unit_measure_rate2 FROM mdm_item WHERE deleted=0 ORDER BY id","SELECT id,code,name,status FROM mdm_unit_measure WHERE deleted=0","SELECT id,item_id,bom_item_id,quantity,status,remark FROM mes_md_product_bom WHERE deleted=0"};
   StringBuilder xml=new StringBuilder("<database>");
   for(int i=0;i<names.length;i++){xml.append('<').append(names[i]).append('>');try(var r=st.executeQuery(queries[i])){var meta=r.getMetaData();while(r.next()){xml.append("<row>");for(int j=1;j<=meta.getColumnCount();j++){String s=r.getString(j);xml.append('<').append(meta.getColumnLabel(j)).append('>');if(s!=null)xml.append(esc(s));xml.append("</").append(meta.getColumnLabel(j)).append('>');}xml.append("</row>");}}xml.append("</").append(names[i]).append('>');}xml.append("</database>");
   String suffix=args.length>1 && args[1].equals("post")?"-post":"";
   Files.writeString(Path.of("scripts/bom-import/"+target+suffix+"-snapshot.xml"),xml.toString());System.out.println(target+suffix+" snapshot saved");
  }
 }
}