package com.customduels.database; import com.customduels.CustomDuels; import com.customduels.player.DuelPlayer; import com.customduels.player.PlayerStatistics; import com.customduels.model.PlayerStats; import com.zaxxer.hikari.HikariConfig; import com.zaxxer.hikari.HikariDataSource; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import java.util.UUID; import java.util.concurrent.CompletableFuture; public class MySQLDatabase implements Database { private final CustomDuels plugin; private HikariDataSource dataSource; public MySQLDatabase(CustomDuels plugin) { this.plugin = plugin; } @Override public void connect() { HikariConfig config = new HikariConfig(); String host = plugin.getDatabaseConfig().getMySQLHost(); int port = plugin.getDatabaseConfig().getMySQLPort(); String database = plugin.getDatabaseConfig().getMySQLDatabase(); String username = plugin.getDatabaseConfig().getMySQLUsername(); String password = plugin.getDatabaseConfig().getMySQLPassword(); config.setJdbcUrl("jdbc:mysql://" + host + ":" + port + "/" + database); config.setUsername(username); config.setPassword(password); config.setMaximumPoolSize(plugin.getDatabaseConfig().getMySQLPoolSize()); config.addDataSourceProperty("cachePrepStmts", "true"); config.addDataSourceProperty("prepStmtCacheSize", "250"); config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048"); dataSource = new HikariDataSource(config); plugin.getLogger().info("Connected to MySQL database!"); } @Override public void close() { if (dataSource != null && !dataSource.isClosed()) { dataSource.close(); } } @Override public void createTables() { String playersTable = """ CREATE TABLE IF NOT EXISTS players ( uuid VARCHAR(36) PRIMARY KEY, name VARCHAR(16) NOT NULL, points INT NOT NULL DEFAULT 0 ) """; String statsTable = """ CREATE TABLE IF NOT EXISTS statistics ( uuid VARCHAR(36) PRIMARY KEY, wins INT NOT NULL DEFAULT 0, losses INT NOT NULL DEFAULT 0, draws INT NOT NULL DEFAULT 0, kills INT NOT NULL DEFAULT 0, deaths INT NOT NULL DEFAULT 0, current_streak INT NOT NULL DEFAULT 0, best_streak INT NOT NULL DEFAULT 0, matches_played INT NOT NULL DEFAULT 0, queue_joins INT NOT NULL DEFAULT 0, rounds_won INT NOT NULL DEFAULT 0, rounds_lost INT NOT NULL DEFAULT 0, points_earned INT NOT NULL DEFAULT 0, points_spent INT NOT NULL DEFAULT 0 ) """; String eloTable = """ CREATE TABLE IF NOT EXISTS kit_elo ( uuid VARCHAR(36) NOT NULL, kit_name VARCHAR(64) NOT NULL, elo INT NOT NULL DEFAULT 1000, PRIMARY KEY (uuid, kit_name) ) """; try (Connection conn = dataSource.getConnection()) { conn.prepareStatement(playersTable).executeUpdate(); conn.prepareStatement(statsTable).executeUpdate(); conn.prepareStatement(eloTable).executeUpdate(); } catch (SQLException e) { plugin.getLogger().severe("Could not create tables: " + e.getMessage()); } } @Override public CompletableFuture loadPlayer(UUID uuid) { return CompletableFuture.supplyAsync(() -> { String sql = "SELECT * FROM players WHERE uuid = ?"; try (Connection conn = dataSource.getConnection(); PreparedStatement stmt = conn.prepareStatement(sql)) { stmt.setString(1, uuid.toString()); ResultSet rs = stmt.executeQuery(); if (rs.next()) { DuelPlayer player = new DuelPlayer(uuid, rs.getString("name")); player.setPvpPoints(rs.getInt("points")); return player; } } catch (SQLException e) { plugin.getLogger().severe("Could not load player: " + e.getMessage()); } return null; }); } @Override public CompletableFuture savePlayer(DuelPlayer player) { return CompletableFuture.runAsync(() -> { String sql = "INSERT INTO players (uuid, name, points) VALUES (?, ?, ?) " + "ON DUPLICATE KEY UPDATE name = ?, points = ?"; try (Connection conn = dataSource.getConnection(); PreparedStatement stmt = conn.prepareStatement(sql)) { stmt.setString(1, player.getUuid().toString()); stmt.setString(2, player.getName()); stmt.setInt(3, player.getPvpPoints()); stmt.setString(4, player.getName()); stmt.setInt(5, player.getPvpPoints()); stmt.executeUpdate(); } catch (SQLException e) { plugin.getLogger().severe("Could not save player: " + e.getMessage()); } }); } @Override public CompletableFuture loadStatistics(UUID uuid) { return CompletableFuture.supplyAsync(() -> { PlayerStatistics stats = new PlayerStatistics(uuid); String sql = "SELECT * FROM statistics WHERE uuid = ?"; try (Connection conn = dataSource.getConnection(); PreparedStatement stmt = conn.prepareStatement(sql)) { stmt.setString(1, uuid.toString()); ResultSet rs = stmt.executeQuery(); if (rs.next()) { stats.setWins(rs.getInt("wins")); stats.setLosses(rs.getInt("losses")); stats.setDraws(rs.getInt("draws")); stats.setKills(rs.getInt("kills")); stats.setDeaths(rs.getInt("deaths")); stats.setCurrentWinStreak(rs.getInt("current_streak")); stats.setBestWinStreak(rs.getInt("best_streak")); stats.setMatchesPlayed(rs.getInt("matches_played")); stats.setQueueJoins(rs.getInt("queue_joins")); stats.setRoundsWon(rs.getInt("rounds_won")); stats.setRoundsLost(rs.getInt("rounds_lost")); stats.setPointsEarned(rs.getInt("points_earned")); stats.setPointsSpent(rs.getInt("points_spent")); } } catch (SQLException e) { plugin.getLogger().severe("Could not load statistics: " + e.getMessage()); } String eloSql = "SELECT * FROM kit_elo WHERE uuid = ?"; try (Connection conn = dataSource.getConnection(); PreparedStatement stmt = conn.prepareStatement(eloSql)) { stmt.setString(1, uuid.toString()); ResultSet rs = stmt.executeQuery(); while (rs.next()) { stats.setKitElo(rs.getString("kit_name"), rs.getInt("elo")); } } catch (SQLException e) { plugin.getLogger().severe("Could not load kit ELO: " + e.getMessage()); } return stats; }); } @Override public CompletableFuture saveStatistics(PlayerStatistics stats) { return CompletableFuture.runAsync(() -> { String sql = """ INSERT INTO statistics (uuid, wins, losses, draws, kills, deaths, current_streak, best_streak, matches_played, queue_joins, rounds_won, rounds_lost, points_earned, points_spent) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE wins = ?, losses = ?, draws = ?, kills = ?, deaths = ?, current_streak = ?, best_streak = ?, matches_played = ?, queue_joins = ?, rounds_won = ?, rounds_lost = ?, points_earned = ?, points_spent = ? """; try (Connection conn = dataSource.getConnection(); PreparedStatement stmt = conn.prepareStatement(sql)) { stmt.setString(1, stats.getUuid().toString()); stmt.setInt(2, stats.getWins()); stmt.setInt(3, stats.getLosses()); stmt.setInt(4, stats.getDraws()); stmt.setInt(5, stats.getKills()); stmt.setInt(6, stats.getDeaths()); stmt.setInt(7, stats.getCurrentWinStreak()); stmt.setInt(8, stats.getBestWinStreak()); stmt.setInt(9, stats.getMatchesPlayed()); stmt.setInt(10, stats.getQueueJoins()); stmt.setInt(11, stats.getRoundsWon()); stmt.setInt(12, stats.getRoundsLost()); stmt.setInt(13, stats.getPointsEarned()); stmt.setInt(14, stats.getPointsSpent()); stmt.setInt(15, stats.getWins()); stmt.setInt(16, stats.getLosses()); stmt.setInt(17, stats.getDraws()); stmt.setInt(18, stats.getKills()); stmt.setInt(19, stats.getDeaths()); stmt.setInt(20, stats.getCurrentWinStreak()); stmt.setInt(21, stats.getBestWinStreak()); stmt.setInt(22, stats.getMatchesPlayed()); stmt.setInt(23, stats.getQueueJoins()); stmt.setInt(24, stats.getRoundsWon()); stmt.setInt(25, stats.getRoundsLost()); stmt.setInt(26, stats.getPointsEarned()); stmt.setInt(27, stats.getPointsSpent()); stmt.executeUpdate(); } catch (SQLException e) { plugin.getLogger().severe("Could not save statistics: " + e.getMessage()); } String eloSql = "INSERT INTO kit_elo (uuid, kit_name, elo) VALUES (?, ?, ?) " + "ON DUPLICATE KEY UPDATE elo = ?"; try (Connection conn = dataSource.getConnection()) { for (String kitName : stats.getKitElo().keySet()) { try (PreparedStatement stmt = conn.prepareStatement(eloSql)) { int elo = stats.getKitElo(kitName); stmt.setString(1, stats.getUuid().toString()); stmt.setString(2, kitName); stmt.setInt(3, elo); stmt.setInt(4, elo); stmt.executeUpdate(); } } } catch (SQLException e) { plugin.getLogger().severe("Could not save kit ELO: " + e.getMessage()); } }); } @Override public CompletableFuture> getTopPoints(int limit) { return CompletableFuture.supplyAsync(() -> { List players = new ArrayList<>(); String sql = "SELECT * FROM players ORDER BY points DESC LIMIT ?"; try (Connection conn = dataSource.getConnection(); PreparedStatement stmt = conn.prepareStatement(sql)) { stmt.setInt(1, limit); ResultSet rs = stmt.executeQuery(); while (rs.next()) { UUID uuid = UUID.fromString(rs.getString("uuid")); DuelPlayer player = new DuelPlayer(uuid, rs.getString("name")); player.setPvpPoints(rs.getInt("points")); players.add(player); } } catch (SQLException e) { plugin.getLogger().severe("Could not load top points: " + e.getMessage()); } return players; }); } @Override public CompletableFuture> getTopWins(int limit) { return CompletableFuture.supplyAsync(() -> { List statsList = new ArrayList<>(); String sql = "SELECT * FROM statistics ORDER BY wins DESC LIMIT ?"; try (Connection conn = dataSource.getConnection(); PreparedStatement stmt = conn.prepareStatement(sql)) { stmt.setInt(1, limit); ResultSet rs = stmt.executeQuery(); while (rs.next()) { UUID uuid = UUID.fromString(rs.getString("uuid")); PlayerStatistics stats = new PlayerStatistics(uuid); stats.setWins(rs.getInt("wins")); stats.setLosses(rs.getInt("losses")); stats.setDraws(rs.getInt("draws")); statsList.add(stats); } } catch (SQLException e) { plugin.getLogger().severe("Could not load top wins: " + e.getMessage()); } return statsList; }); } @Override public CompletableFuture> getTopElo(String kitName, int limit) { return CompletableFuture.supplyAsync(() -> { List statsList = new ArrayList<>(); String sql = "SELECT * FROM kit_elo WHERE kit_name = ? ORDER BY elo DESC LIMIT ?"; try (Connection conn = dataSource.getConnection(); PreparedStatement stmt = conn.prepareStatement(sql)) { stmt.setString(1, kitName); stmt.setInt(2, limit); ResultSet rs = stmt.executeQuery(); while (rs.next()) { UUID uuid = UUID.fromString(rs.getString("uuid")); PlayerStatistics stats = new PlayerStatistics(uuid); stats.setKitElo(kitName, rs.getInt("elo")); statsList.add(stats); } } catch (SQLException e) { plugin.getLogger().severe("Could not load top ELO: " + e.getMessage()); } return statsList; }); } @Override public CompletableFuture getPointsRank(UUID uuid) { return CompletableFuture.supplyAsync(() -> { String sql = "SELECT COUNT(*) + 1 as rank FROM players WHERE points > (SELECT points FROM players WHERE uuid = ?)"; try (Connection conn = dataSource.getConnection(); PreparedStatement stmt = conn.prepareStatement(sql)) { stmt.setString(1, uuid.toString()); ResultSet rs = stmt.executeQuery(); if (rs.next()) { return rs.getInt("rank"); } } catch (SQLException e) { plugin.getLogger().severe("Could not get points rank: " + e.getMessage()); } return 0; }); } @Override public CompletableFuture> getTopKills(int limit) { return CompletableFuture.supplyAsync(() -> { List statsList = new ArrayList<>(); String sql = "SELECT * FROM statistics ORDER BY kills DESC LIMIT ?"; try (Connection conn = dataSource.getConnection(); PreparedStatement stmt = conn.prepareStatement(sql)) { stmt.setInt(1, limit); ResultSet rs = stmt.executeQuery(); while (rs.next()) { UUID uuid = UUID.fromString(rs.getString("uuid")); PlayerStatistics stats = new PlayerStatistics(uuid); stats.setKills(rs.getInt("kills")); stats.setDeaths(rs.getInt("deaths")); statsList.add(stats); } } catch (SQLException e) { plugin.getLogger().severe("Could not load top kills: " + e.getMessage()); } return statsList; }); } @Override public CompletableFuture> getTopStreak(int limit) { return CompletableFuture.supplyAsync(() -> { List statsList = new ArrayList<>(); String sql = "SELECT * FROM statistics ORDER BY best_streak DESC LIMIT ?"; try (Connection conn = dataSource.getConnection(); PreparedStatement stmt = conn.prepareStatement(sql)) { stmt.setInt(1, limit); ResultSet rs = stmt.executeQuery(); while (rs.next()) { UUID uuid = UUID.fromString(rs.getString("uuid")); PlayerStatistics stats = new PlayerStatistics(uuid); stats.setBestWinStreak(rs.getInt("best_streak")); statsList.add(stats); } } catch (SQLException e) { plugin.getLogger().severe("Could not load top streak: " + e.getMessage()); } return statsList; }); } @Override public CompletableFuture loadPlayerStats(UUID uuid) { return CompletableFuture.supplyAsync(() -> { PlayerStats stats = new PlayerStats(uuid); String sql = "SELECT * FROM statistics WHERE uuid = ?"; try (Connection conn = dataSource.getConnection(); PreparedStatement stmt = conn.prepareStatement(sql)) { stmt.setString(1, uuid.toString()); ResultSet rs = stmt.executeQuery(); if (rs.next()) { stats.setWins(rs.getInt("wins")); stats.setLosses(rs.getInt("losses")); stats.setDraws(rs.getInt("draws")); stats.setKills(rs.getInt("kills")); stats.setDeaths(rs.getInt("deaths")); stats.setWinStreak(rs.getInt("current_streak")); stats.setBestStreak(rs.getInt("best_streak")); stats.setMatchesPlayed(rs.getInt("matches_played")); } } catch (SQLException e) { plugin.getLogger().severe("Could not load player stats: " + e.getMessage()); } String nameSql = "SELECT name FROM players WHERE uuid = ?"; try (Connection conn = dataSource.getConnection(); PreparedStatement stmt = conn.prepareStatement(nameSql)) { stmt.setString(1, uuid.toString()); ResultSet rs = stmt.executeQuery(); if (rs.next()) { stats.setName(rs.getString("name")); } } catch (SQLException e) { plugin.getLogger().severe("Could not load player name: " + e.getMessage()); } return stats; }); } @Override public CompletableFuture savePlayerStats(PlayerStats stats) { return CompletableFuture.runAsync(() -> { String sql = "INSERT INTO statistics (uuid, wins, losses, draws, kills, deaths, current_streak, best_streak, matches_played, queue_joins, rounds_won, rounds_lost, points_earned, points_spent) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, 0, 0, 0, 0, 0) ON DUPLICATE KEY UPDATE wins = ?, losses = ?, draws = ?, kills = ?, deaths = ?, current_streak = ?, best_streak = ?, matches_played = ?"; try (Connection conn = dataSource.getConnection(); PreparedStatement stmt = conn.prepareStatement(sql)) { stmt.setString(1, stats.getUuid().toString()); stmt.setInt(2, stats.getWins()); stmt.setInt(3, stats.getLosses()); stmt.setInt(4, stats.getDraws()); stmt.setInt(5, stats.getKills()); stmt.setInt(6, stats.getDeaths()); stmt.setInt(7, stats.getWinStreak()); stmt.setInt(8, stats.getBestStreak()); stmt.setInt(9, stats.getMatchesPlayed()); stmt.setInt(10, stats.getWins()); stmt.setInt(11, stats.getLosses()); stmt.setInt(12, stats.getDraws()); stmt.setInt(13, stats.getKills()); stmt.setInt(14, stats.getDeaths()); stmt.setInt(15, stats.getWinStreak()); stmt.setInt(16, stats.getBestStreak()); stmt.setInt(17, stats.getMatchesPlayed()); stmt.executeUpdate(); } catch (SQLException e) { plugin.getLogger().severe("Could not save player stats: " + e.getMessage()); } }); } }