import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Path;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * Executes a .sql file statement by statement, so a schema change can be applied to a database whose
 * structure Flyway does not maintain (the shared dev database runs with {@code flyway.enabled=false}).
 *
 * <p>Run through {@code scripts/apply-sql-file.ps1}; this program exists because the checkout has no
 * MySQL client and no Docker, while the JDBC driver is already required by the application.
 *
 * <p>It understands the client-side {@code DELIMITER} directive, so the very same
 * {@code db/migration/V*.sql} file can be replayed by hand when a deployment skipped it.
 */
public final class ApplySqlFile {

    public static void main(String[] args) throws Exception {
        if (args.length < 4) {
            System.err.println("usage: ApplySqlFile <jdbcUrl> <user> <password> <sqlFile> [--dry-run]");
            System.exit(2);
        }
        boolean dryRun = args.length > 4 && "--dry-run".equals(args[4].trim());
        String url = args[0];
        String user = args[1];
        String password = args[2];
        Path file = Path.of(args[3]);
        if (!Files.isReadable(file)) {
            System.err.println("Cannot read " + file.toAbsolutePath());
            System.exit(2);
        }

        List<String> statements = split(Files.readString(file, StandardCharsets.UTF_8));
        if (statements.isEmpty()) {
            System.out.println("No statements in " + file.getFileName() + "; nothing to do.");
            return;
        }
        if (dryRun) {
            for (String sql : statements) System.out.println("would execute: " + firstLine(sql));
            System.out.println("dry run: " + statements.size()
                    + " statement(s) in " + file.getFileName() + " were not executed");
            return;
        }

        int executed = 0;
        try (Connection connection = DriverManager.getConnection(url, user, password);
             Statement statement = connection.createStatement()) {
            for (String sql : statements) {
                System.out.println("executing: " + firstLine(sql));
                statement.execute(sql);
                executed++;
            }
        } catch (SQLException exception) {
            System.err.println("failed after " + executed + " of " + statements.size() + " statements: "
                    + exception.getMessage());
            throw exception;
        }
        System.out.println("applied " + executed + " statement(s) from " + file.getFileName());
    }

    /**
     * Splits a script into statements, honouring {@code DELIMITER} and skipping line comments.
     *
     * <p>Statements are cut at the active terminator only when it is outside quotes, because dollar
     * quoted procedure bodies and Chinese descriptions legitimately contain semicolons.
     */
    static List<String> split(String script) {
        List<String> statements = new ArrayList<>();
        StringBuilder current = new StringBuilder();
        String delimiter = ";";
        char quote = 0;
        for (String rawLine : script.split("\r?\n", -1)) {
            String line = rawLine;
            String trimmed = line.trim();
            if (quote == 0 && (trimmed.startsWith("--") || trimmed.startsWith("#"))) continue;
            if (quote == 0 && trimmed.regionMatches(true, 0, "DELIMITER", 0, "DELIMITER".length())) {
                delimiter = trimmed.substring("DELIMITER".length()).trim();
                continue;
            }
            for (int index = 0; index < line.length(); index++) {
                char character = line.charAt(index);
                if (quote != 0) {
                    if (character == quote) quote = 0;
                } else if (character == '\'' || character == '"' || character == '`') {
                    quote = character;
                }
                current.append(character);
                if (quote == 0 && endsWith(current, delimiter)) {
                    current.setLength(current.length() - delimiter.length());
                    String sql = current.toString().trim();
                    if (!sql.isEmpty()) statements.add(sql);
                    current.setLength(0);
                }
            }
            current.append('\n');
        }
        String tail = current.toString().trim();
        if (!tail.isEmpty()) statements.add(tail);
        return statements;
    }

    private static boolean endsWith(CharSequence value, String suffix) {
        return suffix.length() > 0 && value.length() >= suffix.length()
                && value.toString().endsWith(suffix);
    }

    private static String firstLine(String sql) {
        int newline = sql.indexOf('\n');
        String head = (newline < 0 ? sql : sql.substring(0, newline)).trim();
        return head.length() > 120 ? head.substring(0, 120) + " ..." : head;
    }
}