Dev4

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
);

// ** We're not using file reading for now **
// private static final String LOG_FILE_PATH = "C:/Users/Downloads/client_load.log";

public void analyzeLogs() {
    Map<String, QueryStats> statsMap = new HashMap<>();

    // Hardcoded test lines (replace with your actual log line format if needed)
    String[] testLines = {
            "Line 1: DEBUG [STUCK] ExecuteThread: '123' Preparing: SELECT * FROM users WHERE id = 1;",
            "Line 2: DEBUG [ACTIVE] ExecuteThread: '456' Preparing: SELECT * FROM products WHERE category = 'Electronics';",
            "Line 3: DEBUG [STUCK] ExecuteThread: '123' Preparing: SELECT * FROM users WHERE id = 1;", // Duplicate query
            "Line 4: DEBUG [ACTIVE] ExecuteThread: '789' SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';"
    };

    // Simulate reading the lines
    for (String line : testLines) {
        processLine(statsMap, line);
    }

    printAnalysis(statsMap);
}

private void processLine(Map<String, QueryStats> statsMap, String line) {
    Matcher matcher = LOG_PATTERN.matcher(line);

    if (matcher.find()) {
        Integer currentLineNum = Integer.parseInt(matcher.group(1));
        String currentStatus = matcher.group(2);
        String currentThread = matcher.group(3);
        String queryPart = matcher.group(4);

        // If queryPart is null, we might be at the end of a multi-line query
        // In this simplified example, we assume single-line queries
        if (queryPart != null) {
            finalizeQuery(statsMap, queryPart, currentStatus, currentThread, currentLineNum);
        }
    } else {
        System.out.println("No match found for line: " + line);
    }
}

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

    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) {
    // ... (Your normalization logic - this should be fine)
    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 ?");

    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) {
            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();
}