import java.sql.*;
import java.nio.file.*;
import java.io.*;
import java.util.*;
import java.util.regex.*;
class SaleQuotationDatabase {
 static int count(Statement st,String sql)throws Exception{try(var r=st.executeQuery(sql)){r.next();return r.getInt(1);}}
 static long syncOwner(Connection local,String remotePassword)throws Exception{
  try(var p=local.prepareStatement("SELECT id,nickname,status FROM system_users WHERE username='zhuli' AND deleted=0");var r=p.executeQuery()){
   if(r.next()){long id=r.getLong(1);if(!r.getString(2).trim().equals("朱莉") || r.getInt(3)!=0 || r.next())throw new Exception("Local zhuli identity conflict");return id;}
  }
  try(var st=local.createStatement()){if(count(st,"SELECT COUNT(*) FROM system_users WHERE username='zhuli'")>0)throw new Exception("Deleted local zhuli account exists; stopped");}
  String url="jdbc:mysql://36.213.150.237:10000/mom-hm?characterEncoding=UTF-8&allowPublicKeyRetrieval=true&useSSL=false&connectTimeout=10000&socketTimeout=60000";
  try(var remote=DriverManager.getConnection(url,"root",remotePassword);var st=remote.createStatement();var r=st.executeQuery("SELECT username,nickname,password,dept_id,email,mobile,sex,avatar,status,tenant_id FROM system_users WHERE id=291 AND username='zhuli' AND deleted=0")){
   if(!r.next() || !r.getString("nickname").trim().equals("朱莉") || r.getInt("status")!=0)throw new Exception("Remote owner identity mismatch");
   Long dept=r.getObject("dept_id",Long.class);
   if(dept!=null){String remoteName=null,localName=null;try(var d=remote.prepareStatement("SELECT name FROM system_dept WHERE id=? AND deleted=0")){d.setLong(1,dept);try(var dr=d.executeQuery()){if(dr.next())remoteName=dr.getString(1);}}try(var d=local.prepareStatement("SELECT name FROM system_dept WHERE id=? AND deleted=0")){d.setLong(1,dept);try(var dr=d.executeQuery()){if(dr.next())localName=dr.getString(1);}}if(!Objects.equals(remoteName,localName) || localName==null)dept=null;}
   try(var p=local.prepareStatement("INSERT INTO system_users(username,nickname,password,dept_id,post_ids,email,mobile,sex,avatar,status,tenant_id,remark,creator,updater) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)",Statement.RETURN_GENERATED_KEYS)){
    p.setString(1,r.getString("username"));p.setString(2,r.getString("nickname").trim());p.setString(3,r.getString("password"));p.setObject(4,dept);p.setString(5,"[]");p.setString(6,r.getString("email"));p.setString(7,r.getString("mobile"));p.setObject(8,r.getObject("sex"));p.setString(9,r.getString("avatar"));p.setInt(10,0);p.setLong(11,r.getLong("tenant_id"));p.setString(12,"销售报价同步所需负责人，来源远程账号 zhuli；关联权限仅限本次客户及报价");p.setString(13,"quotation-import-20261012");p.setString(14,"quotation-import-20261012");
    if(p.executeUpdate()!=1)throw new Exception("Owner account insert failed");try(var keys=p.getGeneratedKeys()){keys.next();return keys.getLong(1);}
   }
  }
 }
 static void importQuotes(Connection c,String target,String remotePassword)throws Exception{
  c.setAutoCommit(false);
  try(var st=c.createStatement()){
   if(count(st,"SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='mom-hm' AND table_name IN ('crm_customer','crm_sale_quotation','crm_sale_quotation_product','crm_permission','mdm_item','system_users') AND engine='InnoDB'")!=6)throw new Exception("Transactional tables required");
   for(String sql:Files.readString(Path.of("scripts/quotation-import/"+target+"-import.sql")).split(";"))if(!sql.isBlank())st.executeUpdate(sql.trim());
   if(count(st,"SELECT COUNT(*) FROM stage_quote_header")!=345 || count(st,"SELECT COUNT(*) FROM stage_quote_line")!=1702)throw new Exception("Unexpected stage counts");
   if(count(st,"SELECT COUNT(*) FROM stage_quote_line s LEFT JOIN mdm_item m ON m.id=s.expected_id AND m.code=s.item_code WHERE s.expected_id<>0 AND (m.id IS NULL OR NOT(m.name <=> s.expected_name) OR m.unit_measure_id<>s.expected_base_unit OR m.deleted=1 OR m.status<>0)")>0)throw new Exception("Existing material changed");
   if(count(st,"SELECT COUNT(*) FROM (SELECT s.item_code FROM (SELECT DISTINCT item_code FROM stage_quote_line) s JOIN mdm_item m ON m.code=s.item_code AND m.deleted=0 GROUP BY s.item_code HAVING COUNT(*)>1) x")>0)throw new Exception("Multiple active materials have the same code");
   if(count(st,"SELECT COUNT(*) FROM stage_quote_line s LEFT JOIN mdm_unit_measure u ON u.id=s.unit_id WHERE u.id IS NULL OR u.deleted=1 OR u.status<>0")>0)throw new Exception("Source quotation unit unavailable");
   if(count(st,"SELECT COUNT(*) FROM stage_quote_material s JOIN mdm_item m ON m.code=s.code WHERE m.deleted=1 OR m.status<>0 OR NOT(m.name <=> s.name) OR NOT(m.specification <=> s.specification) OR m.unit_measure_id<>s.unit_id OR m.item_type<>s.item_type")>0)throw new Exception("New material code conflict");
   if(count(st,"SELECT COUNT(*) FROM (SELECT c.name FROM crm_customer c JOIN stage_quote_customer s ON s.name=c.name WHERE c.deleted=0 GROUP BY c.name HAVING COUNT(*)>1) x")>0)throw new Exception("Ambiguous customer names");
   if(target.equals("remote") && count(st,"SELECT COUNT(*) FROM system_users WHERE id=291 AND username='zhuli' AND status=0 AND deleted=0")!=1)throw new Exception("Remote zhuli owner unavailable; stopped");
   long owner=syncOwner(c,remotePassword);
   int products=st.executeUpdate("INSERT INTO mdm_item(code,name,specification,unit_measure_id,item_type,status,remark,creator,updater) SELECT s.code,s.name,s.specification,s.unit_id,s.item_type,0,'销售报价表产品建档，按原表名称、规格和包装单位；未恢复已删除物料','quotation-import-20261012','quotation-import-20261012' FROM stage_quote_material s WHERE NOT EXISTS(SELECT 1 FROM mdm_item m WHERE m.code=s.code)");
   int customers=st.executeUpdate("INSERT INTO crm_customer(name,owner_user_id,owner_time,remark,creator,updater) SELECT s.name,"+owner+",NOW(),'来源：销售报价单_2026.8.1-10.11.xlsx；客户名称按明细表主体，不替换为门店',"+owner+","+owner+" FROM stage_quote_customer s WHERE NOT EXISTS(SELECT 1 FROM crm_customer c WHERE c.name=s.name AND c.deleted=0)");
   if(count(st,"SELECT COUNT(*) FROM stage_quote_line s LEFT JOIN mdm_item m ON m.code=s.item_code AND m.deleted=0 AND m.status=0 WHERE m.id IS NULL")>0)throw new Exception("Missing material after preparation");
   String headerMismatch="q.deleted=1 OR q.status<>0 OR NOT(q.name COLLATE utf8mb4_unicode_ci <=> s.name) OR q.customer_id<>c.id OR q.owner_user_id<>"+owner+" OR NOT(q.quotation_time <=> s.quotation_time) OR NOT(q.valid_until <=> s.valid_until) OR NOT(q.total_product_price <=> s.total) OR NOT(q.total_price <=> s.total) OR q.discount_percent<>0 OR q.discount_price<>0 OR q.tax_rate IS NOT NULL OR q.tax_price IS NOT NULL OR q.business_id IS NOT NULL OR q.contact_id IS NOT NULL OR NOT(q.remark COLLATE utf8mb4_unicode_ci <=> s.remark)";
   if(count(st,"SELECT COUNT(*) FROM stage_quote_header s JOIN crm_customer c ON c.name=s.customer_name AND c.deleted=0 JOIN crm_sale_quotation q ON q.no COLLATE utf8mb4_unicode_ci=s.no WHERE "+headerMismatch)>0)throw new Exception("Existing quotation conflict; no overwrite");
   String insertHeaders="INSERT INTO crm_sale_quotation(no,name,customer_id,owner_user_id,status,quotation_time,valid_until,total_product_price,discount_percent,discount_price,total_price,tax_rate,tax_price,remark,creator,updater,tenant_id) SELECT s.no,s.name,c.id,"+owner+",0,s.quotation_time,s.valid_until,s.total,0,0,s.total,NULL,NULL,s.remark,"+owner+","+owner+",0 FROM stage_quote_header s JOIN crm_customer c ON c.name=s.customer_name AND c.deleted=0 WHERE NOT EXISTS(SELECT 1 FROM crm_sale_quotation q WHERE q.no COLLATE utf8mb4_unicode_ci=s.no)";
   int headers=st.executeUpdate(insertHeaders);
   String lineMismatch="p.item_unit_id<>s.unit_id OR NOT(p.item_price <=> s.item_price) OR NOT(p.quotation_price <=> s.quotation_price) OR NOT(p.count <=> s.count) OR NOT(p.total_price <=> s.total) OR p.tax_percent IS NOT NULL OR p.tax_price IS NOT NULL OR NOT(p.remark COLLATE utf8mb4_unicode_ci <=> s.remark)";
   if(count(st,"SELECT COUNT(*) FROM stage_quote_line s JOIN crm_sale_quotation q ON q.no COLLATE utf8mb4_unicode_ci=s.no JOIN mdm_item m ON m.code=s.item_code AND m.deleted=0 JOIN crm_sale_quotation_product p ON p.quotation_id=q.id AND p.item_id=m.id AND p.deleted=0 WHERE "+lineMismatch)>0)throw new Exception("Existing quotation line conflict");
   String insertLines="INSERT INTO crm_sale_quotation_product(quotation_id,item_id,item_unit_id,item_price,quotation_price,count,total_price,tax_percent,tax_price,remark,creator,updater,tenant_id) SELECT q.id,m.id,s.unit_id,s.item_price,s.quotation_price,s.count,s.total,NULL,NULL,s.remark,"+owner+","+owner+",0 FROM stage_quote_line s JOIN crm_sale_quotation q ON q.no COLLATE utf8mb4_unicode_ci=s.no AND q.deleted=0 JOIN mdm_item m ON m.code=s.item_code AND m.deleted=0 WHERE NOT EXISTS(SELECT 1 FROM crm_sale_quotation_product p WHERE p.quotation_id=q.id AND p.item_id=m.id AND p.deleted=0) ORDER BY q.id,s.line_no";
   int lines=st.executeUpdate(insertLines);
   String permissions="INSERT INTO crm_permission(biz_type,biz_id,user_id,level,creator,updater) SELECT s.biz_type,s.id,"+owner+",1,"+owner+","+owner+" FROM (SELECT 2 biz_type,c.id FROM stage_quote_customer s JOIN crm_customer c ON c.name=s.name AND c.deleted=0 UNION ALL SELECT 9,q.id FROM stage_quote_header s JOIN crm_sale_quotation q ON q.no COLLATE utf8mb4_unicode_ci=s.no AND q.deleted=0) s WHERE NOT EXISTS(SELECT 1 FROM crm_permission p WHERE p.biz_type=s.biz_type AND p.biz_id=s.id AND p.user_id="+owner+" AND p.level=1 AND p.deleted=0)";
   int permissionRows=st.executeUpdate(permissions);
   if(count(st,"SELECT COUNT(*) FROM stage_quote_header s LEFT JOIN crm_sale_quotation q ON q.no COLLATE utf8mb4_unicode_ci=s.no LEFT JOIN crm_customer c ON c.name=s.customer_name AND c.deleted=0 WHERE q.id IS NULL OR "+headerMismatch)>0)throw new Exception("Header verification failed");
   if(count(st,"SELECT COUNT(*) FROM stage_quote_line s JOIN crm_sale_quotation q ON q.no COLLATE utf8mb4_unicode_ci=s.no JOIN mdm_item m ON m.code=s.item_code AND m.deleted=0 LEFT JOIN crm_sale_quotation_product p ON p.quotation_id=q.id AND p.item_id=m.id AND p.deleted=0 WHERE p.id IS NULL OR "+lineMismatch)>0)throw new Exception("Line verification failed");
   if(count(st,"SELECT COUNT(*) FROM (SELECT q.id FROM stage_quote_header s JOIN crm_sale_quotation q ON q.no COLLATE utf8mb4_unicode_ci=s.no LEFT JOIN crm_sale_quotation_product p ON p.quotation_id=q.id AND p.deleted=0 GROUP BY q.id,q.total_price,s.no HAVING COUNT(p.id)<>(SELECT COUNT(*) FROM stage_quote_line l WHERE l.no=s.no) OR SUM(p.total_price)<>q.total_price) x")>0)throw new Exception("Quotation totals/counts verification failed");
   if(count(st,"SELECT COUNT(*) FROM stage_quote_header s JOIN crm_sale_quotation q ON q.no COLLATE utf8mb4_unicode_ci=s.no LEFT JOIN crm_permission p ON p.biz_type=9 AND p.biz_id=q.id AND p.user_id="+owner+" AND p.level=1 AND p.deleted=0 WHERE p.id IS NULL")>0)throw new Exception("Quotation owner permission missing");
   if(st.executeUpdate(insertHeaders)!=0 || st.executeUpdate(insertLines)!=0 || st.executeUpdate(permissions)!=0)throw new Exception("Idempotency failed");
   c.commit();String result=target+" mom-hm committed: customers="+customers+", new materials="+products+", quotations="+headers+", lines="+lines+", ownership permissions="+permissionRows+", owner zhuli id="+owner+"; total=15392805.71; field/content/amount/ownership/idempotency checks passed\n";Files.writeString(Path.of("scripts/quotation-import/"+target+"-result.txt"),result);System.out.print(result);
  }catch(Exception e){c.rollback();throw e;}
 }
 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;").replace("\"","&quot;");}
 static void snapshot(Connection c,String target,String suffix)throws Exception{
  var qs=new LinkedHashMap<String,String>();
  qs.put("tables","SELECT table_name,engine FROM information_schema.tables WHERE table_schema='mom-hm' AND table_name IN ('crm_customer','crm_sale_quotation','crm_sale_quotation_product','crm_permission')");
  qs.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 IN ('crm_customer','crm_sale_quotation','crm_sale_quotation_product','crm_permission') ORDER BY table_name,ordinal_position");
  qs.put("customers","SELECT id,name,owner_user_id,deleted FROM crm_customer ORDER BY id");
  qs.put("users","SELECT id,username,nickname,status,tenant_id,dept_id FROM system_users WHERE deleted=0 AND (nickname LIKE '%\u6731%' OR username='admin' OR id IN(244,291))");
  qs.put("materials","SELECT id,code,name,specification,status,item_type,unit_measure_id,unit_measure_id2,unit_measure_id3,sales_price FROM mdm_item WHERE deleted=0 ORDER BY id");
  qs.put("units","SELECT id,code,name,status FROM mdm_unit_measure WHERE deleted=0");
  qs.put("businesses","SELECT id,name,customer_id,deleted FROM crm_business WHERE deleted=0 AND name='2026年8-10月零售成交'");
  qs.put("quotations","SELECT * FROM crm_sale_quotation WHERE no LIKE 'BJ20261012-%'");
  qs.put("lines","SELECT p.* FROM crm_sale_quotation_product p JOIN crm_sale_quotation q ON q.id=p.quotation_id WHERE q.no LIKE 'BJ20261012-%'");
  qs.put("permissions","SELECT p.* FROM crm_permission p WHERE p.biz_type=9 OR (p.biz_type=2 AND p.user_id IN(SELECT id FROM system_users WHERE deleted=0 AND username='zhuli'))");
  StringBuilder xml=new StringBuilder("<database>");
  try(var st=c.createStatement()){
   for(var e:qs.entrySet()){
    xml.append('<').append(e.getKey()).append('>');
    try(var r=st.executeQuery(e.getValue())){var m=r.getMetaData();while(r.next()){xml.append("<row>");for(int j=1;j<=m.getColumnCount();j++){xml.append('<').append(m.getColumnLabel(j)).append('>');String s=r.getString(j);if(s!=null)xml.append(esc(s));xml.append("</").append(m.getColumnLabel(j)).append('>');}xml.append("</row>");}}
    xml.append("</").append(e.getKey()).append('>');
   }
  }
  xml.append("</database>");Files.writeString(Path.of("scripts/quotation-import/"+target+suffix+"-snapshot.xml"),xml.toString());System.out.println(target+suffix+" snapshot saved");
 }
 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=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(args.length>1 && args[1].equals("import")){
    String remotePassword=target.equals("remote")?password:new BufferedReader(new InputStreamReader(System.in)).readLine();
    importQuotes(c,target,remotePassword);return;
   }
   snapshot(c,target,args.length>1 && args[1].equals("post")?"-post":"");
  }
 }
}