Dev3

import java.io.BufferedReader;

import java.io.FileReader;

import java.io.IOException;

import java.util.*;

import java.util.regex.Matcher;

import java.util.regex.Pattern;



public class SQLAnalyzerJava8 {



    private static class QueryStats {

        String query;

        int stuckCount;

        int activeCount;

        Set<String> threadIds = new HashSet<>();

        Set<Integer> lineNumbers = new HashSet<>();



        public QueryStats(String query) {

            this.query = query;

        }

    }



    private static final Pattern LOG_PATTERN = Pattern.compile(

            "Line (\\d+): DEBUG .*?" +

                    "\\[(STUCK|ACTIVE)\\] " +

                    "ExecuteThread: '(\\d+)' .*?" +

                    "(?:Preparing: )?(SELECT.+?)(?:;\\s*$|(?=Line \\d+: DEBUG))",

            Pattern.DOTALL

    );



    private static final String LOG_FILE_PATH = "C:/Users/Downloads/client_load.log"; // **Use forward slashes or double backslashes**



    public void analyzeLogs() {

        Map<String, QueryStats> statsMap = new HashMap<>();



        try (BufferedReader reader = new BufferedReader(new FileReader(LOG_FILE_PATH))) {

            String line;

            StringBuilder currentQuery = new StringBuilder();

            String currentThread = null;

            String currentStatus = null;

            Integer currentLineNum = null;

            boolean isCollectingQuery = false;



            while ((line = reader.readLine()) != null) {

                Matcher matcher = LOG_PATTERN.matcher(line);



                if (matcher.find()) {

                    if (isCollectingQuery) {

                        // Finalize the previous query

                        finalizeQuery(statsMap, currentQuery.toString(), currentStatus, currentThread, currentLineNum);

                        currentQuery.setLength(0); // Reset

                    }



                    currentLineNum = Integer.parseInt(matcher.group(1));

                    currentStatus = matcher.group(2);

                    currentThread = matcher.group(3);

                    String queryPart = matcher.group(4);



                    if (queryPart != null) {

                        currentQuery.append(queryPart);

                        isCollectingQuery = true;

                    }

                } else if (isCollectingQuery && !line.trim().isEmpty()) {

                    currentQuery.append(" ").append(line.trim());

                }

            }



            // Finalize the last query (if any)

            if (isCollectingQuery) {

                finalizeQuery(statsMap, currentQuery.toString(), currentStatus, currentThread, currentLineNum);

            }



        } catch (IOException e) {

            System.err.println("Error reading log file: " + e.getMessage());

            e.printStackTrace();

        }



        printAnalysis(statsMap);

    }



    private void finalizeQuery(Map<String, QueryStats> statsMap, String query, String status, String threadId, Integer lineNum) {

        String normalizedQuery = normalizeQuery(query);



        // Simpler logic for Java 8 - no computeIfAbsent

        QueryStats stats = statsMap.get(normalizedQuery);

        if (stats == null) {

            stats = new QueryStats(normalizedQuery);

            statsMap.put(normalizedQuery, stats);

        }



        if ("STUCK".equals(status)) {

            stats.stuckCount++;

        } else if ("ACTIVE".equals(status)) {

            stats.activeCount++;

        }



        stats.threadIds.add(threadId);

        stats.lineNumbers.add(lineNum);

    }



    private String normalizeQuery(String query) {

        if (query == null) {

            return "";

        }



        query = query.replaceAll("\\s+", " ").trim();



        // Normalize parameter placeholders

        query = query.replaceAll("=\\s*\\?", "= ?");

        query = query.replaceAll("IN\\s*\\([^)]*\\)", "IN (?)");



        // Normalize numbers in identifiers

        query = query.replaceAll("\\b\\d+\\b", "?");



        // Normalize quoted strings

        query = query.replaceAll("'[^']*'", "?");



        // Handle BETWEEN

        query = query.replaceAll("BETWEEN\\s*\\?\\s*AND\\s*\\?", "BETWEEN ? AND ?");



        // Handle LIKE

        query = query.replaceAll("LIKE\\s*\\?", "LIKE ?");



        // Basic handling of order of clauses (very simplistic example)

        if (query.startsWith("SELECT")) {

            String[] parts = query.split("WHERE");

            if (parts.length == 2) {

                String selectAndFrom = parts[0].trim();

                String whereClause = parts[1].trim();



                String[] conditions = whereClause.split("AND");

                Arrays.sort(conditions);



                query = selectAndFrom + " WHERE " + String.join(" AND ", conditions);

            }

        }



        return query;

    }



    private void printAnalysis(Map<String, QueryStats> statsMap) {

        System.out.println("=== SQL Query Analysis ===\n");



        List<QueryStats> sortedStats = new ArrayList<>(statsMap.values());

        Collections.sort(sortedStats, new Comparator<QueryStats>() {

            @Override

            public int compare(QueryStats s1, QueryStats s2) {

                // Sort by combined stuck and active count, descending

                return Integer.compare(s2.stuckCount + s2.activeCount, s1.stuckCount + s1.activeCount);

            }

        });



        for (QueryStats stats : sortedStats) {

            System.out.println("Query: " + stats.query);

            System.out.println("Stuck Count: " + stats.stuckCount);

            System.out.println("Active Count: " + stats.activeCount);

            System.out.println("Thread IDs: " + stats.threadIds);

            System.out.println("Line Numbers: " + stats.lineNumbers);

            System.out.println("--------------------------------------------------------------------------------\n");

        }

    }



    public static void main(String[] args) {

        SQLAnalyzerJava8 analyzer = new SQLAnalyzerJava8();

        analyzer.analyzeLogs();

    }

}