package gudusoft.gsqlparser.tools;

/**
 * Function of this class :
 * 
 * 1. Take a directory path as input
 * 2. Read all the sql files in the directory recursively and read the content of the file with .sql extension
 * 3. Guess the database vendor based on the directory name or file name, if the db vendor is guessed from the directory name, 
 *    then all the files in the directory should be of the same db vendor,otherwise guess the db vendor from the filename, otherwise use the default db vendor as mysql
 * 4. Use TGSqlParser.getrawsqlstatements() to get the raw sql statements from the file, and count the number of sql statements in the file
 * 5. Collect the following information for each sql file:
 *    - file size in bytes
 *    - number of sql statements
 *    - sql statement type of each sql statement and the count of each type, use TCustomSqlStatement.sqlstatementtype to get the sql statement type
 *    - db vendor
 * 6. Collect the following information for the entire directory:
 *    - total number of sql files
 *    - total number of sql statements
 *    - total number of bytes
 *    - total number of sql statements by type
 *    
 *    the above information can be grouped by db vendor
 * 7. Output the information to a csv file
 * 8. add a new option to save all the sql statements to a file in a single line json format, each sql statement is a json object, 
 *    and includes the following elements: 
 *    - db vendor
 *    - sql statement type
 *    - sql statement size in bytes
 *    - unique hash id of this sql statement,when calculating the unique hash id, all whitespaces must be ignored
 *    - sql statement
 * 
 *  the file name is sql_statements.json
 * 
 * Sample output:
 * ======================= Summary Statistics =======================
 * Total SQL Files: 6529
 * Total Bytes: 25353796
 * Total SQL Statements: 54617
 * 
 * --- Average Statistics ---
 * Average File Size (bytes): 3883.259
 * Average SQL Statements Per File: 8.365
 * Average Size Per SQL Statement (bytes): 464.211
 * 
 * Statistics have been written to c:\prg\tmp\a.csv
 */

import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.TCustomSqlStatement;
import gudusoft.gsqlparser.TGSqlParser;
import gudusoft.gsqlparser.TStatementList;
import java.io.*;
import java.nio.file.Files;
import java.util.*;
import java.text.NumberFormat;
import java.util.Locale;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.nio.charset.Charset;
import java.nio.charset.StandardCharsets;

public class CountSql {
    
    // Create a formatter for numeric values
    private final NumberFormat numberFormat = NumberFormat.getNumberInstance(Locale.US);
    
    public static void main(String[] args) {
        if (args.length < 1) {
            System.out.println("Usage: java gudusoft.gsqlparser.tools.CountSql <directory_path> [output_csv_path] [show_file_details] [export_json] [json_output_dir]");
            System.out.println("  - show_file_details: Optional. Set to 'true' to include individual file details in the output (default: false)");
            System.out.println("  - export_json: Optional. Set to 'true' to export all SQL statements as JSON (default: false)");
            System.out.println("  - json_output_dir: Optional. Directory to save the JSON output file (default: current directory)");
            return;
        }
        
        String dirPath = args[0];
        String outputPath = args.length > 1 ? args[1] : "sql_statistics.csv";
        boolean showFileDetails = args.length > 2 ? Boolean.parseBoolean(args[2]) : false;
        boolean exportJson = args.length > 3 ? Boolean.parseBoolean(args[3]) : false;
        String jsonOutputDir = args.length > 4 ? args[4] : ".";
        
        CountSql counter = new CountSql();
        counter.processDirectory(dirPath, outputPath, showFileDetails, exportJson, jsonOutputDir);
    }
    
    /**
     * Process all SQL files in a directory and its subdirectories
     * @param dirPath Directory path to process
     * @param outputPath Path to output CSV file
     * @param showFileDetails Whether to include file details section in the output
     * @param exportJson Whether to export all SQL statements as JSON
     * @param jsonOutputDir Directory to save the JSON output file
     */
    public void processDirectory(String dirPath, String outputPath, boolean showFileDetails, boolean exportJson, String jsonOutputDir) {
        File directory = new File(dirPath);
        
        if (!directory.exists() || !directory.isDirectory()) {
            System.err.println("Error: " + dirPath + " is not a valid directory");
            return;
        }
        
        // Statistics for the entire directory
        int totalFiles = 0;
        long totalBytes = 0;
        int totalStatements = 0;
        Map<String, Integer> totalStatementsByType = new HashMap<>();
        Map<EDbVendor, Map<String, Integer>> statementsByVendor = new HashMap<>();
        Map<EDbVendor, Integer> filesByVendor = new HashMap<>();
        Map<EDbVendor, Long> bytesByVendor = new HashMap<>();
        Map<EDbVendor, Integer> statementsByVendorCount = new HashMap<>();
        
        // List to store all SQL statement details if JSON export is requested
        List<SqlStatementDetail> allStatements = exportJson ? new ArrayList<>() : null;
        
        // CSV output preparation
        try (PrintWriter csvWriter = newUtf8Writer(new File(outputPath))) {
            // Write headers for file details section only if requested
            if (showFileDetails) {
                csvWriter.println("File Path,File Size (bytes),DB Vendor,SQL Statements Count,Statement Types");
            }
            
            // Process files
            List<File> sqlFiles = findSqlFiles(directory);
            totalFiles = sqlFiles.size();
            
            for (File sqlFile : sqlFiles) {
                FileStatistics stats = processFile(sqlFile, dirPath, exportJson);
                totalBytes += stats.size;
                totalStatements += stats.statementCount;
                
                // Update vendor-specific statistics
                EDbVendor vendor = stats.vendor;
                filesByVendor.put(vendor, filesByVendor.getOrDefault(vendor, 0) + 1);
                bytesByVendor.put(vendor, bytesByVendor.getOrDefault(vendor, 0L) + stats.size);
                statementsByVendorCount.put(vendor, statementsByVendorCount.getOrDefault(vendor, 0) + stats.statementCount);
                
                // Update statement types
                for (Map.Entry<String, Integer> entry : stats.statementTypeCount.entrySet()) {
                    String type = entry.getKey();
                    int count = entry.getValue();
                    
                    // Update overall counts
                    totalStatementsByType.put(type, totalStatementsByType.getOrDefault(type, 0) + count);
                    
                    // Update vendor-specific counts
                    Map<String, Integer> vendorTypes = statementsByVendor.getOrDefault(vendor, new HashMap<>());
                    vendorTypes.put(type, vendorTypes.getOrDefault(type, 0) + count);
                    statementsByVendor.put(vendor, vendorTypes);
                }
                
                // Collect SQL statements for JSON export if requested
                if (exportJson && stats.statementDetails != null) {
                    allStatements.addAll(stats.statementDetails);
                }
                
                // Write file details to CSV with formatted numbers only if requested
                if (showFileDetails) {
                    csvWriter.println(sqlFile.getPath() + "," + 
                                    formatNumber(stats.size) + "," + 
                                    vendor + "," + 
                                    formatNumber(stats.statementCount) + "," + 
                                    formatStatementTypes(stats.statementTypeCount));
                }
            }
            
            // Write summary section with formatted numbers
            // If file details were included, add a blank line to separate sections
            if (showFileDetails) {
                csvWriter.println();
            }
            
            csvWriter.println("Summary Statistics");
            csvWriter.println("Total SQL Files," + formatNumber(totalFiles));
            csvWriter.println("Total Bytes," + formatNumber(totalBytes));
            csvWriter.println("Total SQL Statements," + formatNumber(totalStatements));
            
            // Calculate and add average statistics
            if (totalFiles > 0) {
                double avgFileSize = (double) totalBytes / totalFiles;
                double avgStatementsPerFile = (double) totalStatements / totalFiles;
                
                csvWriter.println("Average File Size (bytes)," + formatNumber(avgFileSize));
                csvWriter.println("Average SQL Statements Per File," + formatNumber(avgStatementsPerFile));
                
                // Add average size per SQL statement
                if (totalStatements > 0) {
                    double avgSizePerStatement = (double) totalBytes / totalStatements;
                    csvWriter.println("Average Size Per SQL Statement (bytes)," + formatNumber(avgSizePerStatement));
                }
            }
            
            // Write statement types summary - sorted by count (descending)
            csvWriter.println("\nStatement Types Summary");
            List<Map.Entry<String, Integer>> sortedStatementTypes = new ArrayList<>(totalStatementsByType.entrySet());
            sortedStatementTypes.sort((e1, e2) -> e2.getValue().compareTo(e1.getValue()));
            
            for (Map.Entry<String, Integer> entry : sortedStatementTypes) {
                csvWriter.println(entry.getKey() + "," + formatNumber(entry.getValue()));
            }
            
            // Write vendor-specific summary - sorted by statement count (descending)
            csvWriter.println("\nStatistics by DB Vendor");
            csvWriter.println("DB Vendor,Files,Bytes,Statements");
            
            List<EDbVendor> sortedVendors = new ArrayList<>(statementsByVendorCount.keySet());
            sortedVendors.sort((v1, v2) -> statementsByVendorCount.get(v2).compareTo(statementsByVendorCount.get(v1)));
            
            for (EDbVendor vendor : sortedVendors) {
                csvWriter.println(vendor + "," + 
                               formatNumber(filesByVendor.get(vendor)) + "," + 
                               formatNumber(bytesByVendor.get(vendor)) + "," + 
                               formatNumber(statementsByVendorCount.get(vendor)));
            }
            
            // Write statement types by vendor - vendors and types both sorted by count (descending)
            csvWriter.println("\nStatement Types by DB Vendor");
            csvWriter.println("DB Vendor,Statement Type,Count");
            
            for (EDbVendor vendor : sortedVendors) {
                Map<String, Integer> typeCount = statementsByVendor.get(vendor);
                
                // Skip if typeCount is null or empty
                if (typeCount != null && !typeCount.isEmpty()) {
                    // Sort statement types by count for this vendor
                    List<Map.Entry<String, Integer>> sortedTypes = new ArrayList<>(typeCount.entrySet());
                    sortedTypes.sort((e1, e2) -> e2.getValue().compareTo(e1.getValue()));
                    
                    for (Map.Entry<String, Integer> typeEntry : sortedTypes) {
                        csvWriter.println(vendor + "," + 
                                       typeEntry.getKey() + "," + 
                                       formatNumber(typeEntry.getValue()));
                    }
                } else {
                    // If no statement types found for this vendor, output a message
                    csvWriter.println(vendor + ",No specific statement types found,0");
                }
            }
            
            System.out.println("Statistics have been written to " + outputPath);
            
        } catch (IOException e) {
            System.err.println("Error writing to CSV file: " + e.getMessage());
        }
        
        // Export SQL statements to JSON if requested
        String jsonFilePath = null;
        if (exportJson) {
            if (allStatements != null && !allStatements.isEmpty()) {
                jsonFilePath = exportStatementsToJson(allStatements, jsonOutputDir);
                if (jsonFilePath != null) {
                    System.out.println("SQL statements have been exported to: " + jsonFilePath);
                }
            } else {
                System.out.println("No JSON file created: No SQL statements were collected");
            }
        } else {
            System.out.println("No JSON file created: JSON export not requested");
        }

        // Print summary statistics to the console - simplified version
        System.out.println("\n======================= Summary Statistics =======================");
        System.out.println("Total SQL Files: " + formatNumber(totalFiles));
        System.out.println("Total Bytes: " + formatNumber(totalBytes));
        System.out.println("Total SQL Statements: " + formatNumber(totalStatements));

        // Calculate and add average statistics
        if (totalFiles > 0) {
            double avgFileSize = (double) totalBytes / totalFiles;
            double avgStatementsPerFile = (double) totalStatements / totalFiles;
            
            System.out.println("\n--- Average Statistics ---");
            System.out.println("Average File Size (bytes): " + formatNumber(avgFileSize));
            System.out.println("Average SQL Statements Per File: " + formatNumber(avgStatementsPerFile));
            
            // Add average size per SQL statement
            if (totalStatements > 0) {
                double avgSizePerStatement = (double) totalBytes / totalStatements;
                System.out.println("Average Size Per SQL Statement (bytes): " + formatNumber(avgSizePerStatement));
            }
        }

        // Print file paths summary
        System.out.println("\n--- Output Files ---");
        System.out.println("CSV Statistics: " + new File(outputPath).getAbsolutePath());
        if (jsonFilePath != null) {
            System.out.println("JSON Statements: " + jsonFilePath);
        }

        System.out.println("\nStatistics have been written to " + outputPath);
    }
    
    /**
     * Export SQL statements to JSON file
     * @param statements List of SQL statement details
     * @param outputDir Directory to save the JSON file
     * @return The full absolute path of the created JSON file, or null if creation failed
     */
    private String exportStatementsToJson(List<SqlStatementDetail> statements, String outputDir) {
        // Create File object for the output directory
        File outputDirectory = new File(outputDir);
        
        // Create the directory if it doesn't exist
        if (!outputDirectory.exists()) {
            if (!outputDirectory.mkdirs()) {
                System.err.println("No JSON file created: Could not create directory: " + outputDir);
                return null;
            }
        }
        
        // Ensure the path is a directory
        if (!outputDirectory.isDirectory()) {
            System.err.println("No JSON file created: Specified JSON output path is not a directory: " + outputDir);
            return null;
        }
        
        // Create the full path for the JSON file
        String jsonFileName = "sql_statements.json";
        File jsonFile = new File(outputDirectory, jsonFileName);
        String jsonFilePath = jsonFile.getAbsolutePath(); // Use absolute path for clearer output
        
        try (PrintWriter jsonWriter = newUtf8Writer(jsonFile)) {
            for (SqlStatementDetail stmt : statements) {
                // Write each statement as a single line JSON object
                jsonWriter.println(stmt.toJson());
            }
            
            return jsonFilePath;
            
        } catch (IOException e) {
            System.err.println("No JSON file created: Error writing to JSON file: " + e.getMessage());
            return null;
        }
    }
    
    /**
     * Format number without thousands separators
     * @param number The number to format
     * @return Formatted string without thousands separators
     */
    private String formatNumber(Number number) {
        NumberFormat formatter = NumberFormat.getNumberInstance(Locale.US);
        formatter.setGroupingUsed(false); // 禁用分组（不使用千分号）
        
        // 对于整数值，不显示小数部分
        if (number instanceof Integer || number instanceof Long) {
            formatter.setMaximumFractionDigits(0);
        }
        
        return formatter.format(number);
    }
    
    /**
     * Format the statement types map as a string for CSV output
     */
    private String formatStatementTypes(Map<String, Integer> statementTypes) {
        StringBuilder sb = new StringBuilder();
        for (Map.Entry<String, Integer> entry : statementTypes.entrySet()) {
            if (sb.length() > 0) {
                sb.append(";");
            }
            sb.append(entry.getKey()).append(":").append(formatNumber(entry.getValue()));
        }
        return sb.toString();
    }
    
    /**
     * Find all SQL files in a directory and its subdirectories
     * @param directory Directory to search
     * @return List of SQL files
     */
    private List<File> findSqlFiles(File directory) {
        List<File> sqlFiles = new ArrayList<>();
        findSqlFilesRecursive(directory, sqlFiles);
        return sqlFiles;
    }
    
    /**
     * Recursively find SQL files
     */
    private void findSqlFilesRecursive(File directory, List<File> sqlFiles) {
        File[] files = directory.listFiles();
        if (files != null) {
            for (File file : files) {
                if (file.isDirectory()) {
                    findSqlFilesRecursive(file, sqlFiles);
                } else if (file.getName().toLowerCase(Locale.ROOT).endsWith(".sql")) {
                    sqlFiles.add(file);
                }
            }
        }
    }
    
    /**
     * Process a single SQL file
     * @param file SQL file to process
     * @param rootDirPath Root directory path for DB vendor detection
     * @param collectStatements Whether to collect individual SQL statements
     * @return Statistics for the file
     */
    private FileStatistics processFile(File file, String rootDirPath, boolean collectStatements) {
        FileStatistics stats = new FileStatistics();
        stats.size = file.length();
        
        // Skip processing if file size is zero
        if (stats.size == 0) {
            System.out.println("Skipping empty file: " + file.getPath());
            return stats;
        }
        
        // Determine DB vendor
        stats.vendor = guessDbVendor(file, rootDirPath);
        
        try {
            // Read file content
            String sqlContent = readSqlFile(file);

            // Skip files that are empty or only contain whitespace
            if (sqlContent.trim().isEmpty()) {
                System.out.println("Skipping file with only whitespace: " + file.getPath());
                return stats;
            }
            
            // Parse SQL statements
            TGSqlParser parser = new TGSqlParser(stats.vendor);
            parser.sqltext = sqlContent;
            
            // Get raw SQL statements
            int iRet = parser.getrawsqlstatements();
            if (iRet != 0) {
                System.err.println("Error parsing SQL statements: " + iRet+" for file "+file.getPath()+" with vendor "+stats.vendor);
                return stats;
            }
            TStatementList statements = parser.sqlstatements;
            stats.statementCount = statements.size();
            
            // Initialize statement details list if collecting statements
            if (collectStatements) {
                stats.statementDetails = new ArrayList<>();
            }
            
            // Count statement types and collect statements if requested
            for (int i = 0; i < statements.size(); i++) {
                TCustomSqlStatement stmt = statements.get(i);
                String stmtType = stmt.sqlstatementtype.toString();
                stats.statementTypeCount.put(stmtType, stats.statementTypeCount.getOrDefault(stmtType, 0) + 1);
                
                // Collect statement details if requested
                if (collectStatements) {
                    SqlStatementDetail detail = new SqlStatementDetail();
                    detail.dbVendor = stats.vendor.toString();
                    detail.statementType = stmtType;
                    
                    // Get the SQL statement text
                    String sqlText = stmt.toString();
                    detail.sqlStatement = sqlText;
                    
                    // Calculate SQL statement size (bytes in UTF-8 encoding)
                    detail.sqlSize = sqlText.getBytes(StandardCharsets.UTF_8).length;
                    
                    // Calculate and set the unique hash ID (ignoring whitespaces)
                    detail.uniqueHashId = calculateHashId(sqlText);
                    
                    stats.statementDetails.add(detail);
                }
            }
            
        } catch (IOException e) {
            System.err.println("Error reading file " + file.getPath() + ": " + e.getMessage());
        }
        
        return stats;
    }
    
    /**
     * Guess the database vendor based on directory name or file name
     * @param file SQL file
     * @param rootDirPath Root directory path
     * @return Database vendor
     */
    /**
     * Reads a SQL file. A byte order mark proves the encoding: it picks UTF-8, UTF-16 or UTF-32 and is
     * dropped, by the same rules as {@code SQLUtil.getFileContent} (Mantis 4801: reading every file in
     * the platform charset split UTF-16 files into garbage, and a UTF-8 file with a mark gave no
     * statements). A file without one is read in the platform charset, as before, which
     * {@code -Dfile.encoding} sets: its bytes can be valid in two charsets at once, such as C2 A3, which
     * is UTF-8 for a pound sign and GBK for another character, so no guess here could avoid turning a
     * file that was read correctly into one that is not. getFileContent itself would read such a file
     * as UTF-8 always, and it also rewrites U+00A0 to a space and hides read errors.
     */
    static String readSqlFile(File file) throws IOException {
        byte[] bytes = Files.readAllBytes(file.toPath());
        if (startsWith(bytes, 0x00, 0x00, 0xFE, 0xFF)) {
            return new String(bytes, 4, bytes.length - 4, "UTF-32BE");
        }
        if (startsWith(bytes, 0xFF, 0xFE, 0x00, 0x00)) {
            return new String(bytes, 4, bytes.length - 4, "UTF-32LE");
        }
        if (startsWith(bytes, 0xEF, 0xBB, 0xBF)) {
            return new String(bytes, 3, bytes.length - 3, StandardCharsets.UTF_8);
        }
        if (startsWith(bytes, 0xFE, 0xFF)) {
            return new String(bytes, 2, bytes.length - 2, StandardCharsets.UTF_16BE);
        }
        if (startsWith(bytes, 0xFF, 0xFE)) {
            return new String(bytes, 2, bytes.length - 2, StandardCharsets.UTF_16LE);
        }
        return new String(bytes, Charset.defaultCharset());
    }

    private static boolean startsWith(byte[] bytes, int... prefix) {
        if (bytes.length < prefix.length) {
            return false;
        }
        for (int i = 0; i < prefix.length; i++) {
            if ((bytes[i] & 0xFF) != prefix[i]) {
                return false;
            }
        }
        return true;
    }

    /**
     * A writer that writes UTF-8 whatever the platform charset: JSON must be UTF-8, and FileWriter wrote
     * a windows-1252 e-acute as the single byte E9 (Mantis 4801). Like FileWriter, it replaces what it
     * cannot encode instead of failing, which PrintWriter would otherwise swallow.
     */
    private static PrintWriter newUtf8Writer(File file) throws IOException {
        return new PrintWriter(new OutputStreamWriter(new FileOutputStream(file), StandardCharsets.UTF_8));
    }

    private EDbVendor guessDbVendor(File file, String rootDirPath) {
        // Check directory path recursively
        File currentDir = file.getParentFile();
        File rootDir = new File(rootDirPath);
        
        // Convert both paths to absolute to ensure proper comparison
        String absoluteRootPath = rootDir.getAbsolutePath();
        
        // Traverse up the directory tree until root directory
        while (currentDir != null && 
               currentDir.getAbsolutePath().startsWith(absoluteRootPath)) {
            
            String dirName = currentDir.getName().toLowerCase(Locale.ROOT);
            EDbVendor vendorFromDir = vendorFromName(dirName);
            if (vendorFromDir != null) {
                return vendorFromDir;
            }
            
            // Move up to parent directory
            currentDir = currentDir.getParentFile();
        }
        
        // If no vendor found in directory structure, check file name
        String fileName = file.getName().toLowerCase(Locale.ROOT);
        EDbVendor vendorFromFile = vendorFromName(fileName);
        if (vendorFromFile != null) {
            return vendorFromFile;
        }
        
        // Default to generic
        return EDbVendor.dbvgeneric;
    }
    
    /**
     * Determine vendor from a name (directory or file)
     * Supports a comprehensive list of database vendors
     * @param name Directory or file name to analyze
     * @return The identified database vendor or null if not recognized
     */
    private EDbVendor vendorFromName(String name) {
        name = name.toLowerCase(Locale.ROOT);
        
        // Oracle
        if (name.contains("oracle") || name.contains("ora") || name.contains("plsql")) {
            return EDbVendor.dbvoracle;
        }
        // MySQL
        else if (name.contains("mysql") || name.contains("maria")) {
            return EDbVendor.dbvmysql;
        }
        // PostgreSQL
        else if (name.contains("postgresql") || name.contains("postgres") || name.contains("pgsql")) {
            return EDbVendor.dbvpostgresql;
        }
        // SQL Server
        else if (name.contains("sqlserver") || name.contains("mssql") || name.contains("sql_server") || name.contains("tsql")) {
            return EDbVendor.dbvmssql;
        }
        // DB2
        else if (name.contains("db2")) {
            return EDbVendor.dbvdb2;
        }
        // Sybase
        else if (name.contains("sybase") || name.contains("ase")) {
            return EDbVendor.dbvsybase;
        }
        // Informix
        else if (name.contains("informix")) {
            return EDbVendor.dbvinformix;
        }
        // Teradata
        else if (name.contains("teradata")) {
            return EDbVendor.dbvteradata;
        }
        // Netezza
        else if (name.contains("netezza")) {
            return EDbVendor.dbvnetezza;
        }
        // Hive
        else if (name.contains("hive")) {
            return EDbVendor.dbvhive;
        }
        // Greenplum
        else if (name.contains("greenplum")) {
            return EDbVendor.dbvgreenplum;
        }
        // Redshift
        else if (name.contains("redshift")) {
            return EDbVendor.dbvredshift;
        }
        // Snowflake
        else if (name.contains("snowflake")) {
            return EDbVendor.dbvsnowflake;
        }
        // Vertica
        else if (name.contains("vertica")) {
            return EDbVendor.dbvvertica;
        }
        // Athena
        else if (name.contains("athena")) {
            return EDbVendor.dbvathena;
        }
        // BigQuery
        else if (name.contains("bigquery") || name.contains("big_query") || name.contains("big-query")) {
            return EDbVendor.dbvbigquery;
        }
        // Couchbase
        else if (name.contains("couchbase") || name.contains("n1ql")) {
            return EDbVendor.dbvcouchbase;
        }
        // Databricks
        else if (name.contains("databricks") || name.contains("delta")) {
            return EDbVendor.dbvdatabricks;
        }
        // DAX
        else if (name.contains("dax") || name.contains("powerbi")) {
            return EDbVendor.dbvdax;
        }
        // GaussDB
        else if (name.contains("gaussdb") || name.contains("gauss")) {
            return EDbVendor.dbvgaussdb;
        }
        // EDB Postgres Advanced Server
        else if (name.contains("edb") || name.contains("epas") || name.contains("enterprisedb")) {
            return EDbVendor.dbvedb;
        }
        // HANA
        else if (name.contains("hana") || name.contains("sap")) {
            return EDbVendor.dbvhana;
        }
        // Impala
        else if (name.contains("impala")) {
            return EDbVendor.dbvimpala;
        }
        // MDX
        else if (name.contains("mdx") || name.contains("multidimensional")) {
            return EDbVendor.dbvmdx;
        }
        // ODBC
        else if (name.contains("odbc")) {
            return EDbVendor.dbvodbc;
        }
        // OpenEdge
        else if (name.contains("openedge") || name.contains("progress")) {
            return EDbVendor.dbvopenedge;
        }
        // Presto
        else if (name.contains("presto")) {
            return EDbVendor.dbvpresto;
        }
        // SparkSQL
        else if (name.contains("sparksql") || name.contains("spark-sql") || name.contains("spark_sql") || name.contains("spark")) {
            return EDbVendor.dbvsparksql;
        }
        // Trino
        else if (name.contains("trino") || name.contains("starburst")) {
            return EDbVendor.dbvtrino;
        }
        
        return null;
    }
    
    /**
     * Calculate unique hash ID for SQL statement ignoring all whitespaces
     * This implementation uses SHA-256 and will produce consistent results across languages
     * when implementing the same algorithm.
     * @param sql SQL statement to hash
     * @return Unique hash ID as a lowercase hex string
     */
    public static String calculateHashId(String sql) {
        if (sql == null || sql.isEmpty()) {
            return "";
        }
        
        try {
            // 1. Normalize the SQL by removing ALL whitespace
            String noWhitespace = sql.replaceAll("\\s+", "");
            
            // 2. Convert to bytes using UTF-8 encoding (consistent across platforms)
            byte[] sqlBytes = noWhitespace.getBytes(StandardCharsets.UTF_8);
            
            // 3. Calculate SHA-256 hash (widely available in all languages)
            MessageDigest digest = MessageDigest.getInstance("SHA-256");
            byte[] hashBytes = digest.digest(sqlBytes);
            
            // 4. Convert to lowercase hexadecimal (standard format across languages)
            StringBuilder hexString = new StringBuilder();
            for (byte b : hashBytes) {
                String hex = Integer.toHexString(0xff & b);
                if (hex.length() == 1) {
                    hexString.append('0'); // Ensure two characters for each byte
                }
                hexString.append(hex);
            }
            
            return hexString.toString();
        } catch (NoSuchAlgorithmException e) {
            System.err.println("Error calculating hash ID: " + e.getMessage());
            return "hash_error_" + System.currentTimeMillis();
        }
    }
    
    /**
     * Class to hold statistics for a single SQL file
     */
    private static class FileStatistics {
        long size;
        EDbVendor vendor;
        int statementCount;
        Map<String, Integer> statementTypeCount = new HashMap<>();
        List<SqlStatementDetail> statementDetails; // For JSON export
    }
    
    /**
     * Class to hold details of a single SQL statement for JSON export
     */
    private static class SqlStatementDetail {
        String dbVendor;
        String statementType;
        int sqlSize;         // New field for SQL statement size
        String sqlStatement;
        String uniqueHashId; // Field for unique hash ID
        
        /**
         * Convert to JSON format
         * @return JSON representation of this SQL statement
         */
        public String toJson() {
            // Simple JSON string building (no external library)
            StringBuilder json = new StringBuilder("{");
            json.append("\"dbVendor\":\"").append(escapeJson(dbVendor)).append("\",");
            json.append("\"statementType\":\"").append(escapeJson(statementType)).append("\",");
            json.append("\"sqlSize\":").append(sqlSize).append(",");
            json.append("\"uniqueHashId\":\"").append(escapeJson(uniqueHashId)).append("\",");
            json.append("\"sqlStatement\":\"").append(escapeJson(sqlStatement)).append("\"");
            json.append("}");
            return json.toString();
        }
        
        /**
         * Escape special characters for JSON string
         * @param str String to escape
         * @return Escaped string
         */
        private String escapeJson(String str) {
            if (str == null) {
                return "";
            }
            
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < str.length(); i++) {
                char ch = str.charAt(i);
                switch (ch) {
                    case '\"':
                        sb.append("\\\"");
                        break;
                    case '\\':
                        sb.append("\\\\");
                        break;
                    case '\b':
                        sb.append("\\b");
                        break;
                    case '\f':
                        sb.append("\\f");
                        break;
                    case '\n':
                        sb.append("\\n");
                        break;
                    case '\r':
                        sb.append("\\r");
                        break;
                    case '\t':
                        sb.append("\\t");
                        break;
                    default:
                        if (ch < 0x20) {
                            // JSON allows no raw control character inside a string (RFC 8259,
                            // section 7); one in the SQL text made the whole line invalid (Mantis 4800)
                            sb.append(String.format("\\u%04x", (int) ch));
                        } else {
                            sb.append(ch);
                        }
                }
            }
            return sb.toString();
        }
    }
}