import java.io.*;
import java.nio.charset.StandardCharsets;
import java.util.*;

import gudusoft.gsqlparser.*;
import gudusoft.gsqlparser.resolver2.format.DisplayNameMode;

import static gudusoft.gsqlparser.resolver2.format.DisplayNameMode.*;

/**
 * Standalone executable to compare RESOLVER (old) vs RESOLVER2 (new) results.
 *
 * Exit codes:
 *    0: Both resolvers return the same result (or single resolver ran successfully)
 *    1: Resolvers return different results
 *    2: Parse error or file error
 *
 * Usage: java compareResolvers [options] <dbvendor> <sqlfile>
 *
 * Options:
 *   -v, --verbose       Enable detailed output messages
 *   -r1, --resolver1    Only run and print RESOLVER (old) result
 *   -r2, --resolver2    Only run and print RESOLVER2 (new) result
 *   -m, --mode <mode>   Set display name mode: DISPLAY, SQL_RENDER, or CANONICAL
 *                       Default: SQL_RENDER for -r1, DISPLAY for -r2
 *
 * Display Name Modes:
 *   DISPLAY    - Strip delimiters, preserve original case ([OrderID] -> OrderID)
 *   SQL_RENDER - Preserve delimiters for valid SQL regeneration ([Order ID] -> [Order ID])
 *   CANONICAL  - Apply vendor-specific case folding (Oracle: MyTable -> MYTABLE)
 *
 * Examples:
 *   java compareResolvers oracle /path/to/query.sql
 *   java compareResolvers -v mysql /path/to/query.sql
 *   java compareResolvers -r1 oracle /path/to/query.sql         # RESOLVER with SQL_RENDER
 *   java compareResolvers -r2 oracle /path/to/query.sql         # RESOLVER2 with DISPLAY
 *   java compareResolvers -r2 -m SQL_RENDER oracle /path/to/query.sql  # RESOLVER2 with SQL_RENDER
 */
public class compareResolvers {

    private static boolean verbose = false;
    private static boolean onlyResolver1 = false;
    private static boolean onlyResolver2 = false;
    private static DisplayNameMode displayNameMode = null;  // null means use default based on resolver

    public static void main(String[] args) {
        int exitCode = run(args);
        System.exit(exitCode);
    }

    /**
     * Main entry point that returns an exit code.
     * @param args command line arguments
     * @return 0 for same results, 1 for different results, 2 for parse/file error
     */
    public static int run(String[] args) {
        // Reset static flags (important for multiple calls in same JVM)
        verbose = false;
        onlyResolver1 = false;
        onlyResolver2 = false;
        displayNameMode = null;

        // Parse arguments
        List<String> positionalArgs = new ArrayList<>();
        for (int i = 0; i < args.length; i++) {
            String arg = args[i];
            if (arg.equals("-v") || arg.equals("--verbose")) {
                verbose = true;
            } else if (arg.equals("-r1") || arg.equals("--resolver1")) {
                onlyResolver1 = true;
            } else if (arg.equals("-r2") || arg.equals("--resolver2")) {
                onlyResolver2 = true;
            } else if (arg.equals("-m") || arg.equals("--mode")) {
                if (i + 1 >= args.length) {
                    printError("Missing value for " + arg + " option");
                    printUsage();
                    return 2;
                }
                String modeStr = args[++i].toUpperCase();
                displayNameMode = parseDisplayNameMode(modeStr);
                if (displayNameMode == null) {
                    printError("Invalid display name mode: " + modeStr);
                    printError("Valid modes: DISPLAY, SQL_RENDER, CANONICAL");
                    return 2;
                }
            } else if (arg.startsWith("-")) {
                printError("Unknown option: " + arg);
                printUsage();
                return 2;
            } else {
                positionalArgs.add(arg);
            }
        }

        // Validate mutually exclusive options
        if (onlyResolver1 && onlyResolver2) {
            printError("Options -r1 and -r2 are mutually exclusive");
            printUsage();
            return 2;
        }

        if (positionalArgs.size() < 2) {
            printError("Missing required arguments");
            printUsage();
            return 2;
        }

        String dbVendorStr = positionalArgs.get(0);
        String sqlFilePath = positionalArgs.get(1);

        // Parse database vendor
        EDbVendor dbVendor = getDbVendor(dbVendorStr);
        if (dbVendor == null) {
            printError("Unknown database vendor: " + dbVendorStr);
            printSupportedVendors();
            return 2;
        }

        return compareResolvers(dbVendor, sqlFilePath);
    }

    /**
     * Compare table and column resolution results between RESOLVER and RESOLVER2.
     *
     * @param dbVendor The database vendor
     * @param sqlFilePath The path to the input SQL file
     * @return 2 for parse/file error, 0 for same results (or single resolver success), 1 for different results
     */
    public static int compareResolvers(EDbVendor dbVendor, String sqlFilePath) {
        if (onlyResolver1) {
            printVerbose(repeatChar('=', 80));
            printVerbose("Running RESOLVER (old) only");
            printVerbose("Database Vendor: " + dbVendor);
            printVerbose("SQL File: " + sqlFilePath);
            printVerbose(repeatChar('=', 80));
            printVerbose("");
        } else if (onlyResolver2) {
            printVerbose(repeatChar('=', 80));
            printVerbose("Running RESOLVER2 (new) only");
            printVerbose("Database Vendor: " + dbVendor);
            printVerbose("SQL File: " + sqlFilePath);
            printVerbose(repeatChar('=', 80));
            printVerbose("");
        } else {
            printVerbose(repeatChar('=', 80));
            printVerbose("Comparing RESOLVER vs RESOLVER2");
            printVerbose("Database Vendor: " + dbVendor);
            printVerbose("SQL File: " + sqlFilePath);
            printVerbose(repeatChar('=', 80));
            printVerbose("");
        }

        // Read SQL file content
        File sqlFile = new File(sqlFilePath);
        if (!sqlFile.exists()) {
            printError("SQL file does not exist: " + sqlFilePath);
            return 2;
        }

        String sqlContent;
        try {
            sqlContent = new String(java.nio.file.Files.readAllBytes(
                java.nio.file.Paths.get(sqlFilePath)), StandardCharsets.UTF_8);
        } catch (IOException e) {
            printError("Failed to read SQL file: " + e.getMessage());
            return 2;
        }

        printVerbose("Input SQL:");
        printVerbose(repeatChar('-', 40));
        printVerbose(sqlContent);
        printVerbose(repeatChar('-', 40));
        printVerbose("");

        // Create shared options for both resolvers
        ResolverComparisonOptions options = new ResolverComparisonOptions();

        // Single resolver mode: only run the requested resolver
        if (onlyResolver1) {
            ResolverResult resolver1Result = getResolver1Result(dbVendor, sqlContent, options);
            if (resolver1Result.hasError) {
                return 2;
            }
            System.out.println(resolver1Result.result);
            return 0;
        }

        if (onlyResolver2) {
            ResolverResult resolver2Result = getResolver2Result(dbVendor, sqlContent, options);
            if (resolver2Result.hasError) {
                return 2;
            }
            System.out.println(resolver2Result.result);
            return 0;
        }

        // ========== RESOLVER (old) using TGetTableColumn ==========
        ResolverResult resolver1Result = getResolver1Result(dbVendor, sqlContent, options);
        if (resolver1Result.hasError) {
            return 2;
        }

        // ========== RESOLVER2 (new) using TSQLResolver2ResultFormatter ==========
        ResolverResult resolver2Result = getResolver2Result(dbVendor, sqlContent, options);
        if (resolver2Result.hasError) {
            return 2;
        }

        // ========== Compare results ==========
        printVerbose(repeatChar('=', 80));
        printVerbose("COMPARISON RESULT");
        printVerbose(repeatChar('=', 80));
        printVerbose("");

        // Use semantic comparison (normalized tables and fields) instead of raw string comparison
        // This handles formatting differences like backticks in RESOLVER vs no backticks in RESOLVER2
        if (areResultsSemanticallyEqual(resolver1Result.result, resolver2Result.result)) {
            printVerbose("* RESULTS ARE THE SAME");
            printVerbose("");
            printVerbose("Result:");
            printVerbose(repeatChar('-', 40));
            printVerbose(resolver1Result.result);
            return 0;
        } else {
            printVerbose("X RESULTS ARE DIFFERENT");
            printVerbose("");

            // Show differences
            showDifferences(resolver1Result.result, resolver2Result.result);

            printVerbose("");
            printVerbose("RESOLVER (TGetTableColumn) output:");
            printVerbose(repeatChar('-', 40));
            printVerbose(resolver1Result.result);
            printVerbose("");
            printVerbose("RESOLVER2 (TSQLResolver2ResultFormatter) output:");
            printVerbose(repeatChar('-', 40));
            printVerbose(resolver2Result.result);
            return 1;
        }
    }

    /**
     * Check if two results are semantically equal (same tables and fields after normalization).
     * This handles formatting differences like backticks in RESOLVER vs no backticks in RESOLVER2.
     */
    private static boolean areResultsSemanticallyEqual(String result1, String result2) {
        // Parse both results into normalized tables and fields
        Set<String> tables1 = extractSection(result1, "Tables:");
        Set<String> tables2 = extractSection(result2, "Tables:");
        Set<String> fields1 = extractSection(result1, "Fields:");
        Set<String> fields2 = extractSection(result2, "Fields:");

        // Compare normalized sets
        return tables1.equals(tables2) && fields1.equals(fields2);
    }

    /**
     * Result holder for resolver output.
     */
    private static class ResolverResult {
        String result;
        boolean hasError;

        ResolverResult(String result, boolean hasError) {
            this.result = result;
            this.hasError = hasError;
        }
    }

    /**
     * Options for resolver comparison.
     */
    private static class ResolverComparisonOptions {
        boolean showTableEffect = false;
        boolean showColumnLocation = false;
        boolean listStarColumn = true;
        boolean showDatatype = false;
        boolean linkOrphanColumnToFirstTable = false;
        boolean showCTE = false;
        boolean showColumnsOfCTE = false;
    }

    /**
     * Get table/column result using RESOLVER (old) with TGetTableColumn.
     */
    private static ResolverResult getResolver1Result(EDbVendor dbVendor, String sqlContent, ResolverComparisonOptions options) {
        printVerbose("Running RESOLVER (TGetTableColumn)...");

        gudusoft.gsqlparser.util.TGetTableColumn getTableColumn =
            new gudusoft.gsqlparser.util.TGetTableColumn(dbVendor);
        getTableColumn.setResolverType(EResolverType.RESOLVER);
        getTableColumn.isConsole = false;
        getTableColumn.showDetail = false;
        getTableColumn.showSummary = true;

        // Apply shared options
        getTableColumn.showTableEffect = options.showTableEffect;
        getTableColumn.showColumnLocation = options.showColumnLocation;
        getTableColumn.listStarColumn = options.listStarColumn;
        getTableColumn.showDatatype = options.showDatatype;
        getTableColumn.linkOrphanColumnToFirstTable = options.linkOrphanColumnToFirstTable;
        getTableColumn.showCTE = options.showCTE;
        getTableColumn.showColumnsOfCTE = options.showColumnsOfCTE;

        // Set display name mode (default SQL_RENDER for RESOLVER)
        DisplayNameMode mode = displayNameMode != null ? displayNameMode : SQL_RENDER;
        getTableColumn.setDisplayNameMode(mode);
        printVerbose("Display name mode: " + mode);

        try {
            getTableColumn.runText(sqlContent);
        } catch (RuntimeException e) {
            printError("RESOLVER parse failed: " + e.getMessage());
            return new ResolverResult("", true);
        }

        String result = getTableColumn.outList.toString();
        printVerbose("RESOLVER completed.");
        printVerbose("");
        return new ResolverResult(result.trim(), false);
    }

    /**
     * Get table/column result using RESOLVER2 (new) with TSQLResolver2ResultFormatter.
     */
    private static ResolverResult getResolver2Result(EDbVendor dbVendor, String sqlContent, ResolverComparisonOptions options) {
        printVerbose("Running RESOLVER2 (TSQLResolver2ResultFormatter)...");

        TGSqlParser parser = new TGSqlParser(dbVendor);
        parser.setResolverType(EResolverType.RESOLVER2);
        parser.sqltext = sqlContent;

        int ret = parser.parse();
        if (ret != 0) {
            printError("RESOLVER2 parse failed: " + parser.getErrormessage());
            return new ResolverResult("", true);
        }

        // Get resolver2 and format results
        gudusoft.gsqlparser.resolver2.TSQLResolver2 resolver = parser.getResolver2();
        if (resolver == null) {
            printError("Resolver2 not available after parsing");
            return new ResolverResult("", true);
        }

        gudusoft.gsqlparser.resolver2.TSQLResolver2ResultFormatter formatter =
            new gudusoft.gsqlparser.resolver2.TSQLResolver2ResultFormatter(resolver);

        // Apply shared options
        formatter.setShowTableEffect(options.showTableEffect);
        formatter.setShowColumnLocation(options.showColumnLocation);
        formatter.setListStarColumn(options.listStarColumn);
        formatter.setShowDatatype(options.showDatatype);
        formatter.setLinkOrphanColumnToFirstTable(options.linkOrphanColumnToFirstTable);
        formatter.setShowCTE(options.showCTE);
        formatter.setShowColumnsOfCTE(options.showColumnsOfCTE);

        // Set display name mode (default DISPLAY for RESOLVER2)
        DisplayNameMode mode = displayNameMode != null ? displayNameMode : DISPLAY;
        formatter.setDisplayNameMode(mode);
        printVerbose("Display name mode: " + mode);

        String result = formatter.format();

        printVerbose("RESOLVER2 completed.");
        printVerbose("");
        return new ResolverResult(result.trim(), false);
    }

    /**
     * Show detailed differences between two results.
     */
    private static void showDifferences(String result1, String result2) {
        printVerbose("Differences:");
        printVerbose(repeatChar('-', 40));

        // Parse both results into tables and fields
        Set<String> tables1 = extractSection(result1, "Tables:");
        Set<String> tables2 = extractSection(result2, "Tables:");
        Set<String> fields1 = extractSection(result1, "Fields:");
        Set<String> fields2 = extractSection(result2, "Fields:");

        // Compare tables
        Set<String> tablesOnlyIn1 = new TreeSet<>(String.CASE_INSENSITIVE_ORDER);
        tablesOnlyIn1.addAll(tables1);
        tablesOnlyIn1.removeAll(tables2);

        Set<String> tablesOnlyIn2 = new TreeSet<>(String.CASE_INSENSITIVE_ORDER);
        tablesOnlyIn2.addAll(tables2);
        tablesOnlyIn2.removeAll(tables1);

        if (!tablesOnlyIn1.isEmpty()) {
            printVerbose("Tables only in RESOLVER: " + tablesOnlyIn1);
        }
        if (!tablesOnlyIn2.isEmpty()) {
            printVerbose("Tables only in RESOLVER2: " + tablesOnlyIn2);
        }

        // Compare fields
        Set<String> fieldsOnlyIn1 = new TreeSet<>(String.CASE_INSENSITIVE_ORDER);
        fieldsOnlyIn1.addAll(fields1);
        fieldsOnlyIn1.removeAll(fields2);

        Set<String> fieldsOnlyIn2 = new TreeSet<>(String.CASE_INSENSITIVE_ORDER);
        fieldsOnlyIn2.addAll(fields2);
        fieldsOnlyIn2.removeAll(fields1);

        if (!fieldsOnlyIn1.isEmpty()) {
            printVerbose("Fields only in RESOLVER: " + fieldsOnlyIn1);
        }
        if (!fieldsOnlyIn2.isEmpty()) {
            printVerbose("Fields only in RESOLVER2: " + fieldsOnlyIn2);
        }

        // Show common items
        Set<String> commonTables = new TreeSet<>(String.CASE_INSENSITIVE_ORDER);
        commonTables.addAll(tables1);
        commonTables.retainAll(tables2);

        Set<String> commonFields = new TreeSet<>(String.CASE_INSENSITIVE_ORDER);
        commonFields.addAll(fields1);
        commonFields.retainAll(fields2);

        if (!commonTables.isEmpty() || !commonFields.isEmpty()) {
            printVerbose("");
            printVerbose("Common items:");
            if (!commonTables.isEmpty()) {
                printVerbose("  Tables: " + commonTables);
            }
            if (!commonFields.isEmpty()) {
                printVerbose("  Fields: " + commonFields);
            }
        }
    }

    /**
     * Extract a section (Tables: or Fields:) from the result string.
     * Normalizes identifiers by stripping SQL delimiters (backticks, quotes).
     */
    private static Set<String> extractSection(String result, String sectionName) {
        Set<String> items = new TreeSet<>(String.CASE_INSENSITIVE_ORDER);
        String[] lines = result.split("\n");
        boolean inSection = false;

        for (String line : lines) {
            line = line.trim();
            if (line.isEmpty()) continue;

            if (line.equalsIgnoreCase(sectionName.trim().replace(":", ""))) {
                inSection = true;
                continue;
            }
            if (line.toLowerCase().startsWith("tables:")) {
                inSection = sectionName.toLowerCase().contains("tables");
                continue;
            }
            if (line.toLowerCase().startsWith("fields:")) {
                inSection = sectionName.toLowerCase().contains("fields");
                continue;
            }
            if (line.toLowerCase().startsWith("ctes:")) {
                inSection = false;
                continue;
            }

            if (inSection && !line.isEmpty()) {
                // Normalize the identifier by stripping SQL delimiters
                items.add(normalizeIdentifier(line));
            }
        }

        return items;
    }

    /**
     * Normalize an identifier by stripping SQL delimiters (backticks, double quotes).
     * This allows fair comparison between RESOLVER (preserves delimiters) and
     * RESOLVER2 (strips delimiters).
     *
     * Examples:
     *   `schema`.`table`.column -> schema.table.column
     *   "schema"."table"."column" -> schema.table.column
     *   schema.table.column -> schema.table.column (unchanged)
     */
    private static String normalizeIdentifier(String identifier) {
        if (identifier == null || identifier.isEmpty()) {
            return identifier;
        }

        StringBuilder result = new StringBuilder();
        boolean inBacktick = false;
        boolean inDoubleQuote = false;

        for (int i = 0; i < identifier.length(); i++) {
            char c = identifier.charAt(i);

            if (c == '`' && !inDoubleQuote) {
                // Toggle backtick state, don't add the backtick to result
                inBacktick = !inBacktick;
            } else if (c == '"' && !inBacktick) {
                // Toggle double quote state, don't add the quote to result
                inDoubleQuote = !inDoubleQuote;
            } else {
                result.append(c);
            }
        }

        // Trim leading/trailing spaces that may have been inside quotes
        return result.toString().trim();
    }

    /**
     * Helper method to repeat a character (Java 8 compatible).
     */
    private static String repeatChar(char c, int count) {
        StringBuilder sb = new StringBuilder(count);
        for (int i = 0; i < count; i++) {
            sb.append(c);
        }
        return sb.toString();
    }

    /**
     * Print message only if verbose mode is enabled.
     */
    private static void printVerbose(String message) {
        if (verbose) {
            System.out.println(message);
        }
    }

    /**
     * Print error message (always printed).
     */
    private static void printError(String message) {
        System.err.println("ERROR: " + message);
    }

    /**
     * Print usage information.
     */
    private static void printUsage() {
        System.err.println();
        System.err.println("Usage: java compareResolvers [options] <dbvendor> <sqlfile>");
        System.err.println();
        System.err.println("Options:");
        System.err.println("  -v, --verbose       Enable detailed output messages");
        System.err.println("  -r1, --resolver1    Only run and print RESOLVER (old) result");
        System.err.println("  -r2, --resolver2    Only run and print RESOLVER2 (new) result");
        System.err.println("  -m, --mode <mode>   Set display name mode: DISPLAY, SQL_RENDER, or CANONICAL");
        System.err.println("                      Default: SQL_RENDER for -r1, DISPLAY for -r2");
        System.err.println();
        System.err.println("Display Name Modes:");
        System.err.println("  DISPLAY    - Strip delimiters, preserve original case");
        System.err.println("  SQL_RENDER - Preserve delimiters for valid SQL regeneration");
        System.err.println("  CANONICAL  - Apply vendor-specific case folding");
        System.err.println();
        System.err.println("Exit codes:");
        System.err.println("   0: Both resolvers return the same result (or single resolver success)");
        System.err.println("   1: Resolvers return different results");
        System.err.println("   2: Parse error or file error");
        System.err.println();
        System.err.println("Examples:");
        System.err.println("  java compareResolvers oracle /path/to/query.sql");
        System.err.println("  java compareResolvers -v mysql /path/to/query.sql");
        System.err.println("  java compareResolvers -r1 oracle /path/to/query.sql");
        System.err.println("  java compareResolvers -r2 oracle /path/to/query.sql");
        System.err.println("  java compareResolvers -r2 -m SQL_RENDER oracle /path/to/query.sql");
    }

    /**
     * Parse display name mode from string.
     * @return DisplayNameMode or null if invalid
     */
    private static DisplayNameMode parseDisplayNameMode(String modeStr) {
        if (modeStr == null) return null;
        switch (modeStr.toUpperCase()) {
            case "DISPLAY": return DISPLAY;
            case "SQL_RENDER": return SQL_RENDER;
            case "CANONICAL": return CANONICAL;
            default: return null;
        }
    }

    /**
     * Print supported database vendors.
     */
    private static void printSupportedVendors() {
        System.err.println();
        System.err.println("Supported database vendors:");
        System.err.println("  oracle, mysql, mssql, postgresql, db2, teradata,");
        System.err.println("  informix, sybase, netezza, greenplum, hive, redshift,");
        System.err.println("  snowflake, bigquery, athena, sparksql, vertica, impala,");
        System.err.println("  couchbase, dax, mdx, gaussdb, openedge, etc.");
    }

    /**
     * Convert database vendor string to EDbVendor enum.
     */
    private static EDbVendor getDbVendor(String vendorStr) {
        String vendor = vendorStr.toLowerCase().trim();
        switch (vendor) {
            case "oracle": return EDbVendor.dbvoracle;
            case "mysql": return EDbVendor.dbvmysql;
            case "mssql":
            case "sqlserver": return EDbVendor.dbvmssql;
            case "postgresql":
            case "postgres": return EDbVendor.dbvpostgresql;
            case "db2": return EDbVendor.dbvdb2;
            case "teradata": return EDbVendor.dbvteradata;
            case "informix": return EDbVendor.dbvinformix;
            case "sybase": return EDbVendor.dbvsybase;
            case "netezza": return EDbVendor.dbvnetezza;
            case "greenplum": return EDbVendor.dbvgreenplum;
            case "hive": return EDbVendor.dbvhive;
            case "redshift": return EDbVendor.dbvredshift;
            case "snowflake": return EDbVendor.dbvsnowflake;
            case "bigquery": return EDbVendor.dbvbigquery;
            case "athena": return EDbVendor.dbvathena;
            case "sparksql":
            case "spark": return EDbVendor.dbvsparksql;
            case "vertica": return EDbVendor.dbvvertica;
            case "impala": return EDbVendor.dbvimpala;
            case "couchbase": return EDbVendor.dbvcouchbase;
            case "dax": return EDbVendor.dbvdax;
            case "mdx": return EDbVendor.dbvmdx;
            case "gaussdb": return EDbVendor.dbvgaussdb;
            case "openedge": return EDbVendor.dbvopenedge;
            case "databricks": return EDbVendor.dbvdatabricks;
            case "hana": return EDbVendor.dbvhana;
            default: return null;
        }
    }
}