111

import org.w3c.dom.*;
import org.xml.sax.InputSource;

import javax.xml.XMLConstants;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import java.io.StringReader;
import java.nio.charset.StandardCharsets;
import java.nio.file.;
import java.security.MessageDigest;
import java.util.;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**

• SqlMonsterAnalyzer

• Java 17 / no external libraries.

• Analyzes plain SQL or MyBatis mapper XML without modifying the source.

• Usage:

• javac -encoding UTF-8 SqlMonsterAnalyzer.java

• java SqlMonsterAnalyzer <mapper.xml|query.sql> [report-directory]
  */
  public class SqlMonsterAnalyzer {

  private static final Set<String> STATEMENT_TAGS =
          Set.of("select", "insert", "update", "delete");
  
  private static final Set<String> DYNAMIC_TAGS = Set.of(
          "if", "choose", "when", "otherwise",
          "foreach", "where", "trim", "set", "bind"
  );
  
  private static final Set<String> SQL_RESERVED = Set.of(
          "WHERE", "JOIN", "LEFT", "RIGHT", "FULL",
          "INNER", "OUTER", "CROSS", "ON",
          "GROUP", "ORDER", "HAVING",
          "UNION", "MINUS", "INTERSECT",
          "CONNECT", "START", "MODEL",
          "PIVOT", "UNPIVOT",
          "FETCH", "OFFSET", "FOR",
          "WHEN", "THEN", "ELSE", "END",
          "AND", "OR"
  );
  
  private static final Pattern QUALIFIED_IDENTIFIER =
          Pattern.compile("(?i)\\b([A-Za-z_][A-Za-z0-9_$#]*)\\s*\\.");
  
  private static final Pattern FROM_JOIN_TABLE = Pattern.compile(
          "(?i)\\b(?:FROM|JOIN)\\s+([A-Za-z0-9_$#\\\".]+)"
                  + "(?:\\s+(?:AS\\s+)?([A-Za-z_][A-Za-z0-9_$#]*))?"
  );
  
  private static final Pattern PARAM_BIND =
          Pattern.compile("#\\{[^}]+}");
  
  private static final Pattern PARAM_RAW =
          Pattern.compile("\\$\\{[^}]+}");
  
  private record SqlUnit(
          String owner,
          String kind,
          int startLine,
          int depth,
          String sql,
          String normalized,
          String fingerprint,
          Set<String> outerAliasCandidates
  ) {
  }
  
  private record StatementInfo(
          String id,
          String type,
          String raw,
          String expanded,
          List<String> includes,
          List<String> dynamicBlocks
  ) {
  }
  
  private static final class RenderContext {
      final List<String> includes = new ArrayList<>();
      final List<String> dynamicBlocks = new ArrayList<>();
      int dynamicCounter = 0;
  }
  
  private static final class AnalysisBundle {
      final List<StatementInfo> statements = new ArrayList<>();
      final Map<String, String> fragmentsRaw = new LinkedHashMap<>();
      final Map<String, String> fragmentsExpanded = new LinkedHashMap<>();
      final List<SqlUnit> subqueries = new ArrayList<>();
      final List<SqlUnit> caseBlocks = new ArrayList<>();
      final List<String> warnings = new ArrayList<>();
  
      String sourceType;
      String namespace;
  }
  
  public static void main(String[] args) throws Exception {
      if (args.length < 1) {
          System.out.println(
                  "Usage: java SqlMonsterAnalyzer "
                          + "<mapper.xml|query.sql> [report-directory]"
          );
          System.exit(1);
      }
  
      Path input = Paths.get(args[0])
              .toAbsolutePath()
              .normalize();
  
      if (!Files.isRegularFile(input)) {
          throw new IllegalArgumentException(
                  "Input file not found: " + input
          );
      }
  
      Path reportDir =
              args.length >= 2
                      ? Paths.get(args[1]).toAbsolutePath().normalize()
                      : input.getParent().resolve("sql-analysis-report");
  
      String source =
              Files.readString(input, StandardCharsets.UTF_8);
  
      AnalysisBundle bundle;
  
      if (looksLikeMyBatisXml(input, source)) {
          bundle = analyzeMyBatis(source);
      } else {
          bundle = analyzePlainSql(
                  input.getFileName().toString(),
                  source
          );
      }
  
      analyzeUnits(bundle);
      writeReport(reportDir, input, bundle);
  
      System.out.println("Analysis complete.");
      System.out.println("Input : " + input);
      System.out.println("Report: " + reportDir);
      System.out.println("Statements : " + bundle.statements.size());
      System.out.println("Subqueries : " + bundle.subqueries.size());
      System.out.println("CASE blocks: " + bundle.caseBlocks.size());
  
      long correlated =
              bundle.subqueries.stream()
                      .filter(s ->
                              !s.outerAliasCandidates().isEmpty()
                      )
                      .count();
  
      System.out.println(
              "Correlated candidates: " + correlated
      );
  }
  
  private static boolean looksLikeMyBatisXml(
          Path input,
          String source
  ) {
      String name =
              input.getFileName()
                      .toString()
                      .toLowerCase(Locale.ROOT);
  
      return name.endsWith(".xml")
              || source.matches("(?s).*<\\s*mapper\\b.*");
  }
  
  private static AnalysisBundle analyzePlainSql(
          String name,
          String sql
  ) {
      AnalysisBundle b = new AnalysisBundle();
  
      b.sourceType = "PLAIN_SQL";
      b.namespace = "";
  
      b.statements.add(
              new StatementInfo(
                      stripExtension(name),
                      "SQL",
                      sql,
                      sql,
                      List.of(),
                      List.of()
              )
      );
  
      return b;
  }
  
  private static AnalysisBundle analyzeMyBatis(
          String xml
  ) throws Exception {
  
      AnalysisBundle b = new AnalysisBundle();
      b.sourceType = "MYBATIS_MAPPER_XML";
  
      DocumentBuilderFactory f =
              DocumentBuilderFactory.newInstance();
  
      f.setNamespaceAware(false);
      f.setXIncludeAware(false);
      f.setExpandEntityReferences(false);
  
      trySetFeature(
              f,
              XMLConstants.FEATURE_SECURE_PROCESSING,
              true
      );
  
      /*
       * MyBatis Mapper는 보통 DOCTYPE을 사용한다.
       *
       * DOCTYPE 선언 자체는 허용하되
       * 외부 DTD 다운로드는 차단한다.
       *
       * 즉 mybatis.org에 접속하지 않는다.
       */
      trySetFeature(
              f,
              "http://apache.org/xml/features/disallow-doctype-decl",
              false
      );
  
      trySetFeature(
              f,
              "http://apache.org/xml/features/nonvalidating/load-external-dtd",
              false
      );
  
      trySetFeature(
              f,
              "http://xml.org/sax/features/external-general-entities",
              false
      );
  
      trySetFeature(
              f,
              "http://xml.org/sax/features/external-parameter-entities",
              false
      );
  
      try {
          f.setAttribute(
                  XMLConstants.ACCESS_EXTERNAL_DTD,
                  ""
          );
  
          f.setAttribute(
                  XMLConstants.ACCESS_EXTERNAL_SCHEMA,
                  ""
          );
      } catch (IllegalArgumentException ignored) {
      }
  
      DocumentBuilder db =
              f.newDocumentBuilder();
  
      Document doc =
              db.parse(
                      new InputSource(
                              new StringReader(xml)
                      )
              );
  
      Element mapper =
              doc.getDocumentElement();
  
      if (!"mapper".equalsIgnoreCase(
              mapper.getTagName()
      )) {
          throw new IllegalArgumentException(
                  "XML root is not <mapper>: "
                          + mapper.getTagName()
          );
      }
  
      b.namespace =
              mapper.getAttribute("namespace");
  
      Map<String, Element> fragments =
              new LinkedHashMap<>();
  
      NodeList children =
              mapper.getChildNodes();
  
      /*
       * <sql id="..."> 수집
       */
      for (int i = 0; i < children.getLength(); i++) {
          Node n = children.item(i);
  
          if (n.getNodeType()
                  != Node.ELEMENT_NODE) {
              continue;
          }
  
          Element e = (Element) n;
  
          if ("sql".equalsIgnoreCase(
                  e.getTagName()
          ) && !e.getAttribute("id").isBlank()) {
  
              fragments.put(
                      e.getAttribute("id"),
                      e
              );
          }
      }
  
      /*
       * SQL fragment 분석
       */
      for (Map.Entry<String, Element> en
              : fragments.entrySet()) {
  
          RenderContext rawCtx =
                  new RenderContext();
  
          String raw =
                  renderChildren(
                          en.getValue(),
                          fragments,
                          false,
                          new ArrayDeque<>(),
                          rawCtx
                  );
  
          RenderContext expCtx =
                  new RenderContext();
  
          String expanded =
                  renderChildren(
                          en.getValue(),
                          fragments,
                          true,
                          new ArrayDeque<>(),
                          expCtx
                  );
  
          b.fragmentsRaw.put(
                  en.getKey(),
                  cleanupSql(raw)
          );
  
          b.fragmentsExpanded.put(
                  en.getKey(),
                  cleanupSql(expanded)
          );
      }
  
      /*
       * select / insert / update / delete 수집
       */
      for (int i = 0; i < children.getLength(); i++) {
          Node n = children.item(i);
  
          if (n.getNodeType()
                  != Node.ELEMENT_NODE) {
              continue;
          }
  
          Element e = (Element) n;
  
          String tag =
                  e.getTagName()
                          .toLowerCase(Locale.ROOT);
  
          if (!STATEMENT_TAGS.contains(tag)) {
              continue;
          }
  
          String id =
                  e.getAttribute("id");
  
          if (id.isBlank()) {
              id =
                      tag
                              + "_unnamed_"
                              + (b.statements.size() + 1);
          }
  
          RenderContext rawCtx =
                  new RenderContext();
  
          String raw =
                  renderChildren(
                          e,
                          fragments,
                          false,
                          new ArrayDeque<>(),
                          rawCtx
                  );
  
          RenderContext expCtx =
                  new RenderContext();
  
          String expanded =
                  renderChildren(
                          e,
                          fragments,
                          true,
                          new ArrayDeque<>(),
                          expCtx
                  );
  
          LinkedHashSet<String> includes =
                  new LinkedHashSet<>();
  
          includes.addAll(rawCtx.includes);
          includes.addAll(expCtx.includes);
  
          LinkedHashSet<String> dynamic =
                  new LinkedHashSet<>();
  
          dynamic.addAll(rawCtx.dynamicBlocks);
          dynamic.addAll(expCtx.dynamicBlocks);
  
          b.statements.add(
                  new StatementInfo(
                          id,
                          tag.toUpperCase(Locale.ROOT),
                          cleanupSql(raw),
                          cleanupSql(expanded),
                          new ArrayList<>(includes),
                          new ArrayList<>(dynamic)
                  )
          );
      }
  
      if (b.statements.isEmpty()) {
          b.warnings.add(
                  "No <select>/<insert>/<update>/<delete> "
                          + "statement found in mapper."
          );
      }
  
      return b;
  }
  
  private static void trySetFeature(
          DocumentBuilderFactory f,
          String feature,
          boolean value
  ) {
      try {
          f.setFeature(feature, value);
      } catch (Exception ignored) {
      }
  }
  
  private static String renderChildren(
          Node parent,
          Map<String, Element> fragments,
          boolean expandIncludes,
          Deque<String> includeStack,
          RenderContext ctx
  ) {
      StringBuilder out =
              new StringBuilder();
  
      NodeList nodes =
              parent.getChildNodes();
  
      for (int i = 0; i < nodes.getLength(); i++) {
          Node n = nodes.item(i);
  
          switch (n.getNodeType()) {
              case Node.TEXT_NODE,
                   Node.CDATA_SECTION_NODE ->
                      out.append(n.getNodeValue());
  
              case Node.ELEMENT_NODE ->
                      out.append(
                              renderElement(
                                      (Element) n,
                                      fragments,
                                      expandIncludes,
                                      includeStack,
                                      ctx
                              )
                      );
  
              default -> {
              }
          }
      }
  
      return out.toString();
  }
  
  private static String renderElement(
          Element e,
          Map<String, Element> fragments,
          boolean expandIncludes,
          Deque<String> includeStack,
          RenderContext ctx
  ) {
      String tag =
              e.getTagName()
                      .toLowerCase(Locale.ROOT);
  
      /*
       * <include>
       */
      if ("include".equals(tag)) {
          String refid =
                  e.getAttribute("refid");
  
          ctx.includes.add(refid);
  
          if (!expandIncludes) {
              return "\n/* <include refid=\""
                      + safeComment(refid)
                      + "\"/> */\n";
          }
  
          Element fragment =
                  fragments.get(refid);
  
          if (fragment == null) {
              return "\n/* MISSING_INCLUDE refid=\""
                      + safeComment(refid)
                      + "\" */\n";
          }
  
          if (includeStack.contains(refid)) {
              return "\n/* RECURSIVE_INCLUDE "
                      + safeComment(refid)
                      + " */\n";
          }
  
          includeStack.push(refid);
  
          String rendered =
                  renderChildren(
                          fragment,
                          fragments,
                          true,
                          includeStack,
                          ctx
                  );
  
          includeStack.pop();
  
          /*
           * MyBatis:
           *
           * <include refid="xxx">
           *   <property name="x" value="y"/>
           * </include>
           *
           * 와 같은 property 치환 지원
           */
          NodeList includeChildren =
                  e.getChildNodes();
  
          for (int i = 0;
               i < includeChildren.getLength();
               i++) {
  
              Node child =
                      includeChildren.item(i);
  
              if (child.getNodeType()
                      == Node.ELEMENT_NODE
                      && "property".equalsIgnoreCase(
                      ((Element) child)
                              .getTagName()
              )) {
  
                  Element prop =
                          (Element) child;
  
                  String name =
                          prop.getAttribute("name");
  
                  String value =
                          prop.getAttribute("value");
  
                  if (!name.isBlank()) {
                      rendered =
                              rendered.replace(
                                      "${" + name + "}",
                                      value
                              );
                  }
              }
          }
  
          return "\n/* BEGIN_INCLUDE "
                  + safeComment(refid)
                  + " */\n"
                  + rendered
                  + "\n/* END_INCLUDE "
                  + safeComment(refid)
                  + " */\n";
      }
  
      /*
       * MyBatis 동적 태그
       */
      if (DYNAMIC_TAGS.contains(tag)) {
          int no =
                  ++ctx.dynamicCounter;
  
          String attrs =
                  attributesToString(e);
  
          String descriptor =
                  String.format(
                          Locale.ROOT,
                          "DYN_%03d <%s%s>",
                          no,
                          tag,
                          attrs.isBlank()
                                  ? ""
                                  : " " + attrs
                  );
  
          ctx.dynamicBlocks.add(descriptor);
  
          if ("bind".equals(tag)) {
              return "\n/* "
                      + safeComment(descriptor)
                      + " */\n";
          }
  
          String inner =
                  renderChildren(
                          e,
                          fragments,
                          expandIncludes,
                          includeStack,
                          ctx
                  );
  
          return "\n/* BEGIN "
                  + safeComment(descriptor)
                  + " */\n"
                  + inner
                  + "\n/* END DYN_"
                  + String.format(
                          Locale.ROOT,
                          "%03d",
                          no
                  )
                  + " </"
                  + tag
                  + "> */\n";
      }
  
      /*
       * 모르는 XML 태그는 그냥 버리지 않고
       * 주석 marker로 남긴다.
       */
      String inner =
              renderChildren(
                      e,
                      fragments,
                      expandIncludes,
                      includeStack,
                      ctx
              );
  
      return "\n/* BEGIN_XML_TAG <"
              + safeComment(tag)
              + "> */\n"
              + inner
              + "\n/* END_XML_TAG </"
              + safeComment(tag)
              + "> */\n";
  }
  
  private static String attributesToString(
          Element e
  ) {
      NamedNodeMap attrs =
              e.getAttributes();
  
      List<String> parts =
              new ArrayList<>();
  
      for (int i = 0;
           i < attrs.getLength();
           i++) {
  
          Node a =
                  attrs.item(i);
  
          parts.add(
                  a.getNodeName()
                          + "=\""
                          + a.getNodeValue()
                          .replace("\"", "'")
                          + "\""
          );
      }
  
      Collections.sort(parts);
  
      return String.join(" ", parts);
  }
  
  private static String safeComment(
          String s
  ) {
      return s == null
              ? ""
              : s.replace("*/", "* /")
              .replace("\r", " ")
              .replace("\n", " ");
  }
  
  private static String cleanupSql(
          String sql
  ) {
      String s =
              sql.replace("\r\n", "\n")
                      .replace('\r', '\n');
  
      s = s.replaceAll(
              "[ \\t]+\\n",
              "\\n"
      );
  
      s = s.replaceAll(
              "\\n{3,}",
              "\\n\\n"
      );
  
      return s.strip()
              + System.lineSeparator();
  }
  
  private static void analyzeUnits(
          AnalysisBundle b
  ) {
      for (StatementInfo st : b.statements) {
          String sql =
                  st.expanded();
  
          b.subqueries.addAll(
                  extractSubqueries(
                          st.id(),
                          sql
                  )
          );
  
          b.caseBlocks.addAll(
                  extractCaseBlocks(
                          st.id(),
                          sql
                  )
          );
      }
  
      for (Map.Entry<String, String> en
              : b.fragmentsExpanded.entrySet()) {
  
          String owner =
                  "fragment:" + en.getKey();
  
          b.subqueries.addAll(
                  extractSubqueries(
                          owner,
                          en.getValue()
                  )
          );
  
          b.caseBlocks.addAll(
                  extractCaseBlocks(
                          owner,
                          en.getValue()
                  )
          );
      }
  }
  
  private static List<SqlUnit> extractSubqueries(
          String owner,
          String sql
  ) {
      String masked =
              maskStringsAndComments(sql);
  
      Deque<Integer> stack =
              new ArrayDeque<>();
  
      List<SqlUnit> out =
              new ArrayList<>();
  
      for (int i = 0;
           i < masked.length();
           i++) {
  
          char c =
                  masked.charAt(i);
  
          if (c == '(') {
              stack.push(i);
  
          } else if (c == ')'
                  && !stack.isEmpty()) {
  
              int start =
                      stack.pop();
  
              int insideStart =
                      skipWhitespace(
                              masked,
                              start + 1
                      );
  
              if (insideStart >= i) {
                  continue;
              }
  
              boolean select =
                      startsWithKeyword(
                              masked,
                              insideStart,
                              "SELECT"
                      );
  
              boolean with =
                      startsWithKeyword(
                              masked,
                              insideStart,
                              "WITH"
                      );
  
              if (select || with) {
                  String body =
                          sql.substring(
                                  start + 1,
                                  i
                          ).trim();
  
                  String prefix =
                          masked.substring(
                                  Math.max(
                                          0,
                                          start - 24
                                  ),
                                  start
                          ).toUpperCase(Locale.ROOT);
  
                  String kind =
                          prefix.matches(
                                  "(?s).*\\bEXISTS\\s*$"
                          )
                                  ? "EXISTS_SUBQUERY"
                                  : "SUBQUERY";
  
                  int line =
                          lineNumberAt(
                                  sql,
                                  start
                          );
  
                  int depth =
                          stack.size() + 1;
  
                  String norm =
                          normalizeSql(
                                  body,
                                  true
                          );
  
                  String fp =
                          sha256(norm);
  
                  Set<String> outer =
                          findOuterAliasCandidates(
                                  body
                          );
  
                  out.add(
                          new SqlUnit(
                                  owner,
                                  kind,
                                  line,
                                  depth,
                                  body,
                                  norm,
                                  fp,
                                  outer
                          )
                  );
              }
          }
      }
  
      out.sort(
              Comparator
                      .comparing(SqlUnit::owner)
                      .thenComparingInt(
                              SqlUnit::startLine
                      )
                      .thenComparingInt(
                              SqlUnit::depth
                      )
      );
  
      return out;
  }
  
  private static List<SqlUnit> extractCaseBlocks(
          String owner,
          String sql
  ) {
      String masked =
              maskStringsAndComments(sql);
  
      Matcher m =
              Pattern.compile(
                      "(?i)\\bCASE\\b|\\bEND\\b"
              ).matcher(masked);
  
      Deque<Integer> stack =
              new ArrayDeque<>();
  
      List<SqlUnit> out =
              new ArrayList<>();
  
      while (m.find()) {
          String kw =
                  m.group()
                          .toUpperCase(Locale.ROOT);
  
          if ("CASE".equals(kw)) {
              stack.push(m.start());
  
          } else if (!stack.isEmpty()) {
              int start =
                      stack.pop();
  
              int end =
                      m.end();
  
              String body =
                      sql.substring(
                              start,
                              end
                      ).trim();
  
              String norm =
                      normalizeSql(
                              body,
                              true
                      );
  
              out.add(
                      new SqlUnit(
                              owner,
                              "CASE",
                              lineNumberAt(
                                      sql,
                                      start
                              ),
                              stack.size() + 1,
                              body,
                              norm,
                              sha256(norm),
                              Set.of()
                      )
              );
          }
      }
  
      return out;
  }
  
  private static Set<String> findOuterAliasCandidates(
          String subquery
  ) {
      String masked =
              maskStringsAndComments(
                      subquery
              );
  
      Set<String> localAliases =
              new LinkedHashSet<>();
  
      Set<String> nonOuterPrefixes =
              new LinkedHashSet<>();
  
      Matcher fm =
              FROM_JOIN_TABLE.matcher(masked);
  
      while (fm.find()) {
          String table =
                  fm.group(1);
  
          String alias =
                  fm.group(2);
  
          if (alias != null
                  && !SQL_RESERVED.contains(
                  alias.toUpperCase(Locale.ROOT)
          )) {
              localAliases.add(
                      alias.toUpperCase(Locale.ROOT)
              );
          }
  
          String[] tableParts =
                  table.replace("\"", "")
                          .split("\\.");
  
          if (tableParts.length > 1) {
              for (int i = 0;
                   i < tableParts.length - 1;
                   i++) {
  
                  nonOuterPrefixes.add(
                          tableParts[i]
                                  .toUpperCase(Locale.ROOT)
                  );
              }
          }
  
          if (alias == null
                  && tableParts.length > 0) {
  
              localAliases.add(
                      tableParts[
                              tableParts.length - 1
                              ].toUpperCase(Locale.ROOT)
              );
          }
      }
  
      Set<String> candidates =
              new LinkedHashSet<>();
  
      Matcher qm =
              QUALIFIED_IDENTIFIER
                      .matcher(masked);
  
      while (qm.find()) {
          String q =
                  qm.group(1)
                          .toUpperCase(Locale.ROOT);
  
          if (!localAliases.contains(q)
                  && !nonOuterPrefixes.contains(q)
                  && !q.equals("SYS")
                  && !q.equals("DBMS_OUTPUT")) {
  
              candidates.add(q);
          }
      }
  
      return candidates;
  }
  
  private static String normalizeSql(
          String sql,
          boolean aliasNormalize
  ) {
      /*
       * 'Y'와 'N' 같은 literal 값은 서로 다른 비즈니스 조건이므로
       * 중복으로 취급하면 안 된다.
       *
       * literal은 hash token으로 보존한다.
       */
      String s =
              protectQuotedValues(sql);
  
      s = PARAM_BIND
              .matcher(s)
              .replaceAll(" ?BIND ");
  
      s = PARAM_RAW
              .matcher(s)
              .replaceAll(" ?RAW ");
  
      s = s.replaceAll(
              "(?s)/\\*.*?\\*/",
              " "
      );
  
      s = s.replaceAll(
              "(?m)--.*$",
              " "
      );
  
      s = s.toUpperCase(Locale.ROOT);
  
      s = s.replaceAll(
              "\\s+",
              " "
      ).trim();
  
      if (aliasNormalize) {
          s = normalizeLocalAliases(s);
      }
  
      return s;
  }
  
  private static String normalizeLocalAliases(
          String sql
  ) {
      Matcher m =
              FROM_JOIN_TABLE.matcher(sql);
  
      LinkedHashMap<String, String> aliases =
              new LinkedHashMap<>();
  
      while (m.find()) {
          String alias =
                  m.group(2);
  
          if (alias != null) {
              String a =
                      alias.toUpperCase(Locale.ROOT);
  
              if (!SQL_RESERVED.contains(a)) {
                  aliases.putIfAbsent(
                          a,
                          "T" + (aliases.size() + 1)
                  );
              }
          }
      }
  
      String out = sql;
  
      for (Map.Entry<String, String> en
              : aliases.entrySet()) {
  
          out = out.replaceAll(
                  "(?i)\\b"
                          + Pattern.quote(
                          en.getKey()
                  )
                          + "\\b",
                  Matcher.quoteReplacement(
                          en.getValue()
                  )
          );
      }
  
      /*
       * local alias가 아닌 qualifier도 O1, O2... 로 정규화.
       *
       * 따라서 outer query에서
       *
       * A.EMP_ID
       * B.EMP_ID
       *
       * 처럼 alias만 달라진 구조도 비교할 수 있다.
       */
      LinkedHashMap<String, String> outerAliases =
              new LinkedHashMap<>();
  
      Matcher qm =
              QUALIFIED_IDENTIFIER
                      .matcher(out);
  
      while (qm.find()) {
          String q =
                  qm.group(1)
                          .toUpperCase(Locale.ROOT);
  
          if (q.matches("T\\d+")) {
              continue;
          }
  
          outerAliases.putIfAbsent(
                  q,
                  "O" + (outerAliases.size() + 1)
          );
      }
  
      for (Map.Entry<String, String> en
              : outerAliases.entrySet()) {
  
          out = out.replaceAll(
                  "(?i)\\b"
                          + Pattern.quote(
                          en.getKey()
                  )
                          + "\\s*\\.",
                  Matcher.quoteReplacement(
                          en.getValue() + "."
                  )
          );
      }
  
      return out;
  }
  
  private static String maskStringsAndComments(
          String sql
  ) {
      StringBuilder out =
              new StringBuilder(sql);
  
      boolean single = false;
      boolean dbl = false;
      boolean lineComment = false;
      boolean blockComment = false;
  
      for (int i = 0;
           i < sql.length();
           i++) {
  
          char c =
                  sql.charAt(i);
  
          char n =
                  i + 1 < sql.length()
                          ? sql.charAt(i + 1)
                          : '\0';
  
          if (lineComment) {
              if (c == '\n') {
                  lineComment = false;
              } else {
                  out.setCharAt(i, ' ');
              }
              continue;
          }
  
          if (blockComment) {
              if (c == '*'
                      && n == '/') {
  
                  out.setCharAt(i, ' ');
                  out.setCharAt(i + 1, ' ');
                  i++;
                  blockComment = false;
  
              } else if (c != '\n') {
                  out.setCharAt(i, ' ');
              }
  
              continue;
          }
  
          if (single) {
              if (c == '\''
                      && n == '\'') {
  
                  out.setCharAt(i, ' ');
                  out.setCharAt(i + 1, ' ');
                  i++;
  
              } else if (c == '\'') {
                  out.setCharAt(i, ' ');
                  single = false;
  
              } else if (c != '\n') {
                  out.setCharAt(i, ' ');
              }
  
              continue;
          }
  
          if (dbl) {
              if (c == '"'
                      && n == '"') {
  
                  out.setCharAt(i, ' ');
                  out.setCharAt(i + 1, ' ');
                  i++;
  
              } else if (c == '"') {
                  out.setCharAt(i, ' ');
                  dbl = false;
  
              } else if (c != '\n') {
                  out.setCharAt(i, ' ');
              }
  
              continue;
          }
  
          if (c == '-'
                  && n == '-') {
  
              out.setCharAt(i, ' ');
              out.setCharAt(i + 1, ' ');
              i++;
  
              lineComment = true;
  
          } else if (c == '/'
                  && n == '*') {
  
              out.setCharAt(i, ' ');
              out.setCharAt(i + 1, ' ');
              i++;
  
              blockComment = true;
  
          } else if (c == '\'') {
              out.setCharAt(i, ' ');
              single = true;
  
          } else if (c == '"') {
              out.setCharAt(i, ' ');
              dbl = true;
          }
      }
  
      return out.toString();
  }
  
  private static String protectQuotedValues(
          String sql
  ) {
      StringBuilder out =
              new StringBuilder();
  
      for (int i = 0;
           i < sql.length(); ) {
  
          char c =
                  sql.charAt(i);
  
          if (c != '\''
                  && c != '"') {
  
              out.append(c);
              i++;
              continue;
          }
  
          char quote = c;
          int start = i;
  
          i++;
  
          while (i < sql.length()) {
              char x =
                      sql.charAt(i);
  
              if (x == quote) {
                  if (i + 1 < sql.length()
                          && sql.charAt(i + 1)
                          == quote) {
  
                      i += 2;
                      continue;
                  }
  
                  i++;
                  break;
              }
  
              i++;
          }
  
          String quoted =
                  sql.substring(
                          start,
                          Math.min(
                                  i,
                                  sql.length()
                          )
                  );
  
          String tokenType =
                  quote == '\''
                          ? "LIT"
                          : "QID";
  
          out.append(" __")
                  .append(tokenType)
                  .append('_')
                  .append(sha256(quoted))
                  .append("__ ");
      }
  
      return out.toString();
  }
  
  private static int skipWhitespace(
          String s,
          int p
  ) {
      while (p < s.length()
              && Character.isWhitespace(
              s.charAt(p)
      )) {
          p++;
      }
  
      return p;
  }
  
  private static boolean startsWithKeyword(
          String s,
          int pos,
          String keyword
  ) {
      int end =
              pos + keyword.length();
  
      if (end > s.length()) {
          return false;
      }
  
      if (!s.regionMatches(
              true,
              pos,
              keyword,
              0,
              keyword.length()
      )) {
          return false;
      }
  
      boolean leftOk =
              pos == 0
                      || !Character.isJavaIdentifierPart(
                      s.charAt(pos - 1)
              );
  
      boolean rightOk =
              end == s.length()
                      || !Character.isJavaIdentifierPart(
                      s.charAt(end)
              );
  
      return leftOk && rightOk;
  }
  
  private static int lineNumberAt(
          String s,
          int pos
  ) {
      int line = 1;
  
      for (int i = 0;
           i < pos && i < s.length();
           i++) {
  
          if (s.charAt(i) == '\n') {
              line++;
          }
      }
  
      return line;
  }
  
  private static String sha256(
          String s
  ) {
      try {
          byte[] hash =
                  MessageDigest
                          .getInstance("SHA-256")
                          .digest(
                                  s.getBytes(
                                          StandardCharsets.UTF_8
                                  )
                          );
  
          StringBuilder sb =
                  new StringBuilder();
  
          for (byte b : hash) {
              sb.append(
                      String.format(
                              "%02x",
                              b
                      )
              );
          }
  
          return sb.substring(0, 16);
  
      } catch (Exception e) {
          return Integer.toHexString(
                  s.hashCode()
          );
      }
  }
  
  private static void writeReport(
          Path dir,
          Path input,
          AnalysisBundle b
  ) throws Exception {
  
      Files.createDirectories(dir);
  
      Path statementsDir =
              dir.resolve("statements");
  
      Path fragmentsDir =
              dir.resolve("fragments");
  
      Files.createDirectories(
              statementsDir
      );
  
      Files.createDirectories(
              fragmentsDir
      );
  
      for (StatementInfo st : b.statements) {
          String base =
                  safeFileName(st.id());
  
          writeUtf8(
                  statementsDir.resolve(
                          base + ".raw.sql"
                  ),
                  st.raw()
          );
  
          writeUtf8(
                  statementsDir.resolve(
                          base + ".expanded.sql"
                  ),
                  st.expanded()
          );
      }
  
      for (Map.Entry<String, String> en
              : b.fragmentsRaw.entrySet()) {
  
          String base =
                  safeFileName(en.getKey());
  
          writeUtf8(
                  fragmentsDir.resolve(
                          base + ".raw.sql"
                  ),
                  en.getValue()
          );
  
          writeUtf8(
                  fragmentsDir.resolve(
                          base + ".expanded.sql"
                  ),
                  b.fragmentsExpanded.get(
                          en.getKey()
                  )
          );
      }
  
      writeUtf8(
              dir.resolve("summary.txt"),
              buildSummary(input, b)
      );
  
      writeUtf8(
              dir.resolve("includes.txt"),
              buildIncludes(b)
      );
  
      writeUtf8(
              dir.resolve("dynamic_blocks.txt"),
              buildDynamicBlocks(b)
      );
  
      writeUtf8(
              dir.resolve("subqueries.txt"),
              buildUnitsReport(
                      "SUBQUERIES",
                      b.subqueries
              )
      );
  
      writeUtf8(
              dir.resolve("case_blocks.txt"),
              buildUnitsReport(
                      "CASE BLOCKS",
                      b.caseBlocks
              )
      );
  
      writeUtf8(
              dir.resolve(
                      "correlated_subqueries.txt"
              ),
              buildCorrelatedReport(
                      b.subqueries
              )
      );
  
      writeUtf8(
              dir.resolve("duplicates.txt"),
              buildDuplicatesReport(b)
      );
  
      writeUtf8(
              dir.resolve("README_USAGE.txt"),
              usageText()
      );
  }
  
  private static String buildSummary(
          Path input,
          AnalysisBundle b
  ) {
      StringBuilder sb =
              new StringBuilder();
  
      sb.append(
              "SQL MONSTER ANALYZER SUMMARY\n"
      );
  
      sb.append(
              "============================================================\n"
      );
  
      sb.append("Input       : ")
              .append(input)
              .append('\n');
  
      sb.append("Source type : ")
              .append(b.sourceType)
              .append('\n');
  
      if (b.namespace != null
              && !b.namespace.isBlank()) {
  
          sb.append("Namespace   : ")
                  .append(b.namespace)
                  .append('\n');
      }
  
      sb.append('\n');
  
      sb.append(
              "STATEMENTS\n"
      );
  
      sb.append(
              "------------------------------------------------------------\n"
      );
  
      for (StatementInfo st : b.statements) {
          sb.append(
                  String.format(
                          Locale.ROOT,
                          "%-32s %-8s raw=%7d chars  "
                                  + "expanded=%7d chars  "
                                  + "includes=%2d  dynamic=%2d%n",
                          st.id(),
                          st.type(),
                          st.raw().length(),
                          st.expanded().length(),
                          st.includes().size(),
                          st.dynamicBlocks().size()
                  )
          );
      }
  
      sb.append('\n');
  
      sb.append("Fragments            : ")
              .append(b.fragmentsRaw.size())
              .append('\n');
  
      sb.append("Subqueries           : ")
              .append(b.subqueries.size())
              .append('\n');
  
      sb.append("CASE blocks          : ")
              .append(b.caseBlocks.size())
              .append('\n');
  
      sb.append(
              "Correlated candidates: "
      ).append(
              b.subqueries.stream()
                      .filter(s ->
                              !s.outerAliasCandidates()
                                      .isEmpty()
                      )
                      .count()
      ).append('\n');
  
      sb.append(
              "Duplicate subquery groups: "
      ).append(
              countDuplicateGroups(
                      b.subqueries
              )
      ).append('\n');
  
      sb.append(
              "Duplicate CASE groups    : "
      ).append(
              countDuplicateGroups(
                      b.caseBlocks
              )
      ).append('\n');
  
      if (!b.warnings.isEmpty()) {
          sb.append(
                  "\nWARNINGS\n"
          );
  
          sb.append(
                  "------------------------------------------------------------\n"
          );
  
          b.warnings.forEach(
                  w -> sb.append("- ")
                          .append(w)
                          .append('\n')
          );
      }
  
      sb.append(
              "\nIMPORTANT\n"
      );
  
      sb.append(
              "------------------------------------------------------------\n"
      );
  
      sb.append(
              "- This is a static/heuristic analyzer, "
                      + "not a full Oracle/MyBatis SQL parser.\n"
      );
  
      sb.append(
              "- Dynamic MyBatis tags are preserved as comments; "
                      + "expanded SQL is NOT guaranteed executable.\n"
      );
  
      sb.append(
              "- 'Correlated candidates' are alias-reference heuristics "
                      + "and require human verification.\n"
      );
  
      sb.append(
              "- Duplicate detection normalizes whitespace, "
                      + "bind parameters and alias names, "
                      + "but preserves literal VALUES.\n"
      );
  
      sb.append(
              "- Report line numbers refer to generated "
                      + "*.expanded.sql files, "
                      + "not original XML line numbers.\n"
      );
  
      return sb.toString();
  }
  
  private static long countDuplicateGroups(
          List<SqlUnit> units
  ) {
      return units.stream()
              .collect(
                      Collectors.groupingBy(
                              SqlUnit::fingerprint,
                              LinkedHashMap::new,
                              Collectors.counting()
                      )
              )
              .values()
              .stream()
              .filter(c -> c > 1)
              .count();
  }
  
  private static String buildIncludes(
          AnalysisBundle b
  ) {
      StringBuilder sb =
              new StringBuilder(
                      "MYBATIS INCLUDE RELATIONSHIPS\n"
                              + "============================================================\n\n"
              );
  
      boolean any = false;
  
      for (StatementInfo st : b.statements) {
          if (st.includes().isEmpty()) {
              continue;
          }
  
          any = true;
  
          sb.append(st.id())
                  .append('\n');
  
          for (String inc : st.includes()) {
              sb.append("  -> ")
                      .append(inc)
                      .append('\n');
          }
  
          sb.append('\n');
      }
  
      if (!any) {
          sb.append(
                  "No <include> usage found.\n"
          );
      }
  
      return sb.toString();
  }
  
  private static String buildDynamicBlocks(
          AnalysisBundle b
  ) {
      StringBuilder sb =
              new StringBuilder(
                      "MYBATIS DYNAMIC BLOCKS\n"
                              + "============================================================\n\n"
              );
  
      boolean any = false;
  
      for (StatementInfo st : b.statements) {
          if (st.dynamicBlocks().isEmpty()) {
              continue;
          }
  
          any = true;
  
          sb.append(st.id())
                  .append('\n');
  
          for (String d : st.dynamicBlocks()) {
              sb.append("  ")
                      .append(d)
                      .append('\n');
          }
  
          sb.append('\n');
      }
  
      if (!any) {
          sb.append(
                  "No supported dynamic tags found.\n"
          );
      }
  
      return sb.toString();
  }
  
  private static String buildUnitsReport(
          String title,
          List<SqlUnit> units
  ) {
      StringBuilder sb =
              new StringBuilder(title)
                      .append("\n")
                      .append(
                              "============================================================\n\n"
                      );
  
      if (units.isEmpty()) {
          return sb.append(
                  "None found.\n"
          ).toString();
      }
  
      int idx = 1;
  
      for (SqlUnit u : units) {
          sb.append('[')
                  .append(idx++)
                  .append("] ")
                  .append(u.kind())
                  .append(" owner=")
                  .append(u.owner())
                  .append(" line=")
                  .append(u.startLine())
                  .append(" depth=")
                  .append(u.depth())
                  .append(" fp=")
                  .append(u.fingerprint())
                  .append('\n');
  
          if (!u.outerAliasCandidates()
                  .isEmpty()) {
  
              sb.append(
                      "outer-alias candidates: "
              ).append(
                      String.join(
                              ", ",
                              u.outerAliasCandidates()
                      )
              ).append('\n');
          }
  
          sb.append(
                  "------------------------------------------------------------\n"
          );
  
          sb.append(
                  u.sql().strip()
          ).append("\n\n");
      }
  
      return sb.toString();
  }
  
  private static String buildCorrelatedReport(
          List<SqlUnit> subs
  ) {
      StringBuilder sb =
              new StringBuilder(
                      "CORRELATED SUBQUERY CANDIDATES\n"
                              + "============================================================\n\n"
              );
  
      List<SqlUnit> corr =
              subs.stream()
                      .filter(s ->
                              !s.outerAliasCandidates()
                                      .isEmpty()
                      )
                      .toList();
  
      if (corr.isEmpty()) {
          return sb.append(
                  "None found by heuristic.\n"
          ).toString();
      }
  
      int i = 1;
  
      for (SqlUnit u : corr) {
          sb.append("[")
                  .append(i++)
                  .append("] owner=")
                  .append(u.owner())
                  .append(" line=")
                  .append(u.startLine())
                  .append(" aliases=")
                  .append(
                          String.join(
                                  ", ",
                                  u.outerAliasCandidates()
                          )
                  )
                  .append(" fp=")
                  .append(u.fingerprint())
                  .append('\n');
  
          sb.append(
                  "------------------------------------------------------------\n"
          );
  
          sb.append(
                  u.sql().strip()
          ).append("\n\n");
      }
  
      sb.append(
              "NOTE: false positives are possible "
                      + "(schema/package qualifiers may look like outer aliases).\n"
      );
  
      return sb.toString();
  }
  
  private static String buildDuplicatesReport(
          AnalysisBundle b
  ) {
      StringBuilder sb =
              new StringBuilder(
                      "DUPLICATE SQL STRUCTURES\n"
                              + "============================================================\n\n"
              );
  
      appendDuplicateSection(
              sb,
              "SUBQUERY DUPLICATES",
              b.subqueries
      );
  
      sb.append('\n');
  
      appendDuplicateSection(
              sb,
              "CASE DUPLICATES",
              b.caseBlocks
      );
  
      return sb.toString();
  }
  
  private static void appendDuplicateSection(
          StringBuilder sb,
          String title,
          List<SqlUnit> units
  ) {
      sb.append(title)
              .append("\n")
              .append(
                      "------------------------------------------------------------\n"
              );
  
      Map<String, List<SqlUnit>> groups =
              units.stream()
                      .collect(
                              Collectors.groupingBy(
                                      SqlUnit::fingerprint,
                                      LinkedHashMap::new,
                                      Collectors.toList()
                              )
                      );
  
      List<List<SqlUnit>> dup =
              groups.values()
                      .stream()
                      .filter(g -> g.size() > 1)
                      .sorted(
                              Comparator
                                      .<List<SqlUnit>>
                                              comparingInt(
                                                      List::size
                                              )
                                      .reversed()
                      )
                      .toList();
  
      if (dup.isEmpty()) {
          sb.append(
                  "None found.\n"
          );
          return;
      }
  
      int no = 1;
  
      for (List<SqlUnit> g : dup) {
          SqlUnit sample =
                  g.get(0);
  
          sb.append(
                  "\n[DUPLICATE GROUP #"
          ).append(no++)
                  .append("] count=")
                  .append(g.size())
                  .append(" fp=")
                  .append(sample.fingerprint())
                  .append('\n');
  
          sb.append("Locations:\n");
  
          for (SqlUnit u : g) {
              sb.append("  - ")
                      .append(u.owner())
                      .append(" line ")
                      .append(u.startLine())
                      .append(" (")
                      .append(u.kind())
                      .append(')');
  
              if (!u.outerAliasCandidates()
                      .isEmpty()) {
  
                  sb.append(" outer=[")
                          .append(
                                  String.join(
                                          ",",
                                          u.outerAliasCandidates()
                                  )
                          )
                          .append(']');
              }
  
              sb.append('\n');
          }
  
          sb.append("Normalized:\n  ")
                  .append(sample.normalized())
                  .append('\n');
  
          sb.append("Sample SQL:\n")
                  .append(sample.sql().strip())
                  .append("\n");
  
          if (g.stream().anyMatch(
                  u ->
                          !u.outerAliasCandidates()
                                  .isEmpty()
          )) {
              sb.append(
                      "RISK: correlated-subquery candidate. "
                              + "Do not mechanically replace with a CTE.\n"
              );
          }
      }
  }
  
  private static String safeFileName(
          String s
  ) {
      String v =
              s.replaceAll(
                      "[^A-Za-z0-9._-]",
                      "_"
              );
  
      return v.isBlank()
              ? "unnamed"
              : v;
  }
  
  private static String stripExtension(
          String name
  ) {
      int p =
              name.lastIndexOf('.');
  
      return p > 0
              ? name.substring(0, p)
              : name;
  }
  
  private static void writeUtf8(
          Path p,
          String s
  ) throws Exception {
  
      Files.writeString(
              p,
              s,
              StandardCharsets.UTF_8,
              StandardOpenOption.CREATE,
              StandardOpenOption.TRUNCATE_EXISTING
      );
  }
  
  private static String usageText() {
      return """
              SQL Monster Analyzer - Usage
              ============================================================
  
              Requirements
              ------------
              - Java 17+
              - No external JARs/libraries
  
              Compile
              -------
              javac -encoding UTF-8 SqlMonsterAnalyzer.java
  
              Run - MyBatis mapper
              --------------------
              java SqlMonsterAnalyzer VehicleMapper.xml
  
              Run - plain SQL
              ---------------
              java SqlMonsterAnalyzer monster.sql
  
              Custom report directory
              -----------------------
              java SqlMonsterAnalyzer VehicleMapper.xml C:\\temp\\vehicle-report
  
              Important output files
              ----------------------
              summary.txt
                Overall size/counts. Start here.
  
              duplicates.txt
                Repeated subqueries and CASE expressions after normalization.
                Highest-value file for finding copy/paste SQL.
  
              correlated_subqueries.txt
                Subqueries that appear to reference aliases outside themselves.
                Treat these as high risk: do NOT blindly turn them into CTEs.
  
              subqueries.txt
                Every detected (SELECT ...)/(WITH ...) subquery with owner and line.
  
              dynamic_blocks.txt
                MyBatis if/choose/when/foreach/where/trim/set/bind inventory.
  
              includes.txt
                <include refid="..."> relationships.
  
              statements/*.raw.sql
                Mapper SQL with <include> left as a marker.
  
              statements/*.expanded.sql
                Mapper SQL with known <include> fragments expanded inline.
                MyBatis dynamic tags remain comment markers, so this is analysis text,
                not guaranteed executable SQL.
  
              Suggested workflow for a monster query
              --------------------------------------
              1) Open summary.txt.
              2) Open duplicates.txt and attack the largest duplicate groups first.
              3) Cross-check each candidate in correlated_subqueries.txt.
              4) Review dynamic_blocks.txt before changing WHERE/JOIN structures.
              5) Use statements/<id>.expanded.sql as the human/AI review copy.
              6) Change the real mapper only after verifying result counts and
                 NULL/JOIN behavior.
  
              For a tiny-context internal GPT
              --------------------------------
              Give it ONE duplicate group or ONE subquery at a time, together with:
              - DBMS/version
              - owner statement id
              - outer alias candidates
              - rule: preserve LEFT/INNER JOIN, NULL semantics,
                WHERE/HAVING timing
              - request: explain/refactor only this block
  
              Limitations
              -----------
              - Reported line numbers are relative to generated *.expanded.sql
                files, not the original XML.
              - Heuristic analyzer, not a complete Oracle/MyBatis parser.
              - Dynamic MyBatis branches can produce different runtime SQL.
              - Correlated alias detection can have false positives.
              - Duplicate normalization preserves literal values but normalizes
                alias names and bind parameter names.
                Always compare business conditions before replacing code.
              """;
  }

  }