import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * Drops every table and view of one database so Flyway can rebuild the schema from the migrations.
 *
 * <p>Run through {@code scripts/reset-test-db.ps1}; this program exists because the checkout has no
 * MySQL client and no Docker, while the JDBC driver is already required by the application.
 */
public final class ResetTestDatabase {

    public static void main(String[] args) throws Exception {
        if (args.length < 3) {
            System.err.println("usage: ResetTestDatabase <jdbcUrl> <user> <password>");
            System.exit(2);
        }
        String url = args[0];
        String user = args[1];
        String password = args[2];
        String database = databaseName(url);
        if (database.isEmpty()) {
            System.err.println("The JDBC URL must name the database to reset: " + url);
            System.exit(2);
        }

        try (Connection connection = DriverManager.getConnection(url, user, password)) {
            List<String> views = objects(connection, database, "VIEW");
            List<String> tables = objects(connection, database, "BASE TABLE");
            dropAll(connection, views, tables);
            System.out.println("reset " + database + ": dropped " + views.size() + " views and " + tables.size() + " tables");
            if (!tables.isEmpty()) {
                System.out.println("  dropped: " + summary(tables));
            }
            System.out.println("The next test run recreates the schema from db/migration.");
            return;
        } catch (SQLException exception) {
            if (!unknownDatabase(exception)) throw exception;
        }

        try (Connection connection = DriverManager.getConnection(serverUrl(url), user, password);
             Statement statement = connection.createStatement()) {
            statement.executeUpdate("create database if not exists `" + database + "` default charset utf8mb4");
        }
        System.out.println("created empty database " + database + " (it did not exist yet)");
    }

    private static List<String> objects(Connection connection, String database, String type) throws SQLException {
        List<String> names = new ArrayList<>();
        try (var statement = connection.prepareStatement("""
                select table_name from information_schema.tables
                where table_schema = ? and table_type = ?
                order by table_name
                """)) {
            statement.setString(1, database);
            statement.setString(2, type);
            try (ResultSet result = statement.executeQuery()) {
                while (result.next()) names.add(result.getString(1));
            }
        }
        return names;
    }

    private static void dropAll(Connection connection, List<String> views, List<String> tables) throws SQLException {
        try (Statement statement = connection.createStatement()) {
            statement.execute("set foreign_key_checks = 0");
            try {
                for (String view : views) statement.executeUpdate("drop view if exists `" + view + "`");
                for (String table : tables) statement.executeUpdate("drop table if exists `" + table + "`");
            } finally {
                statement.execute("set foreign_key_checks = 1");
            }
        }
    }

    /** The database name of a JDBC URL such as jdbc:mysql://host:port/db?params. */
    static String databaseName(String url) {
        int pathStart = pathStart(url);
        if (pathStart < 0) return "";
        String tail = url.substring(pathStart + 1);
        int query = tail.indexOf('?');
        return (query < 0 ? tail : tail.substring(0, query)).trim();
    }

    /** The same URL without its database, used to create a database that does not exist yet. */
    static String serverUrl(String url) {
        int pathStart = pathStart(url);
        if (pathStart < 0) return url;
        int query = url.indexOf('?', pathStart);
        return url.substring(0, pathStart + 1) + (query < 0 ? "" : url.substring(query));
    }

    /**
     * Index of the slash that separates the host from the database path. Splitting on the last slash
     * would break on URLs that carry a path-like parameter, for example serverTimezone=Asia/Shanghai.
     */
    private static int pathStart(String url) {
        int schemeEnd = url.indexOf("//");
        return schemeEnd < 0 ? -1 : url.indexOf('/', schemeEnd + 2);
    }

    private static boolean unknownDatabase(SQLException exception) {
        for (SQLException current = exception; current != null; current = current.getNextException()) {
            if (current.getErrorCode() == 1049) return true;
            String message = current.getMessage();
            if (message != null && message.toLowerCase().contains("unknown database")) return true;
        }
        return false;
    }

    private static String summary(List<String> names) {
        if (names.size() <= 12) return String.join(", ", names);
        return String.join(", ", names.subList(0, 12)) + ", ... (" + (names.size() - 12) + " more)";
    }
}