import java.sql.*;
import java.nio.file.*;
import java.io.*;
import java.util.*;
import java.util.regex.*;

class ImportSopQc {
 static String configValue(String block,String key)throws Exception {
  Matcher m=Pattern.compile("(?m)^\\s+"+key+":\\s*([^\\r\\n#]+)").matcher(block);
  if(!m.find())throw new Exception("Missing local database "+key);return m.group(1).trim().replaceAll("^[\"']|[\"']$", "");
 }
 static int count(Statement st,String sql)throws Exception {try(ResultSet rs=st.executeQuery(sql)){rs.next();return rs.getInt(1);}}
 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?useUnicode=true&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"));int start=config.indexOf("master:");if(start<0)throw new Exception("Missing local master datasource");String block=config.substring(start);int stop=block.indexOf("slave:");if(stop>=0)block=block.substring(0,stop);
   url=configValue(block,"url");user=configValue(block,"username");password=configValue(block,"password");
  }
  String script=Files.readString(Path.of("sql/mysql/sop_qc_yl_import_20261012.sql")).replaceAll("(?m)^\\s*--.*$", "");
  List<String> statements=Arrays.stream(script.split(";")).map(String::trim).filter(s->!s.isEmpty()).toList();
  try(Connection c=DriverManager.getConnection(url,user,password);Statement st=c.createStatement()) {
   try(ResultSet rs=st.executeQuery("SELECT DATABASE()")){rs.next();if(!"mom-hm".equals(rs.getString(1)))throw new Exception("Wrong database; stopped");}
   if(count(st,"SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='mom-hm' AND table_name IN ('mes_qc_indicator','mes_qc_template','mes_qc_template_indicator','mes_qc_template_item') AND engine='InnoDB'")!=4)throw new Exception("Transactional tables required");
   c.setAutoCommit(false);
   try {
    List<String> mutations=new ArrayList<>();Map<String,Integer> affected=new LinkedHashMap<>();
    for(String sql:statements){if(sql.startsWith("INSERT INTO `mom-hm`.mes_qc_")){mutations.add(sql);continue;}st.executeUpdate(sql);}
    // Existing user records with the import codes must not be overwritten or silently reused.
    if(count(st,"SELECT COUNT(*) FROM `mom-hm`.stage_sop_indicator s JOIN `mom-hm`.mes_qc_indicator e ON e.code=s.code WHERE e.deleted=1 OR NOT(e.name <=> s.name) OR NOT(e.item_type <=> s.item_type) OR NOT(e.result_type <=> s.result_type)")!=0)throw new Exception("Indicator code conflict");
    if(count(st,"SELECT COUNT(*) FROM `mom-hm`.stage_sop_template s JOIN `mom-hm`.mes_qc_template e ON e.code=s.code WHERE e.deleted=1 OR NOT(e.name <=> s.name)")!=0)throw new Exception("Template code conflict");
    for(String sql:mutations){String table=sql.split(" ")[2];int n=st.executeUpdate(sql);affected.merge(table,n,Integer::sum);}
    String[] checks={
     "SELECT COUNT(*) FROM `mom-hm`.stage_sop_indicator s LEFT JOIN `mom-hm`.mes_qc_indicator e ON e.code=s.code AND e.deleted=0 WHERE e.id IS NULL",
     "SELECT COUNT(*) FROM `mom-hm`.stage_sop_template s LEFT JOIN `mom-hm`.mes_qc_template e ON e.code=s.code AND e.deleted=0 WHERE e.id IS NULL",
     "SELECT COUNT(*) FROM `mom-hm`.stage_sop_line s JOIN `mom-hm`.mes_qc_template t ON t.code=s.template_code JOIN `mom-hm`.mes_qc_indicator i ON i.code=s.indicator_code LEFT JOIN `mom-hm`.mes_qc_template_indicator l ON l.template_id=t.id AND l.indicator_id=i.id AND l.deleted=0 WHERE l.id IS NULL OR NOT(l.remark <=> s.remark) OR NOT(l.check_method <=> s.check_method) OR l.judge_flag<>s.judge_flag",
     "SELECT COUNT(*) FROM `mom-hm`.stage_sop_item s JOIN `mom-hm`.mes_qc_template t ON t.code=s.template_code LEFT JOIN `mom-hm`.mdm_item m ON m.code=s.item_code AND m.name=s.item_name AND m.deleted=0 LEFT JOIN `mom-hm`.mes_qc_template_item l ON l.template_id=t.id AND l.item_id=m.id AND l.deleted=0 WHERE l.id IS NULL"
    };
    for(int i=0;i<checks.length;i++)if(count(st,checks[i])!=0)throw new Exception("Import verification failed at check "+i);
    for(String sql:mutations)if(st.executeUpdate(sql)!=0)throw new Exception("Idempotency failed");
    c.commit();System.out.println(target+" mom-hm committed "+affected+"; completeness/content checks passed; idempotency passed");
    Files.writeString(Path.of("scripts/sop-qc/"+target+"-import-result.txt"),target+" mom-hm: "+affected+"; all verification checks passed; idempotency passed\n");
   }catch(Exception e){c.rollback();throw e;}
  }
 }
}