package com.ultimatelogin.data; import com.ultimatelogin.UltimateLogin; import com.zaxxer.hikari.HikariConfig; import com.zaxxer.hikari.HikariDataSource; import lombok.Getter; import java.io.File; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.UUID; import java.util.concurrent.CompletableFuture; @Getter public class SQLiteDatabase { private final UltimateLogin plugin; private HikariDataSource dataSource; public SQLiteDatabase(UltimateLogin plugin) { this.plugin = plugin; } public void initialize() { File dataFolder = new File(plugin.getDataFolder(), "userdata"); if (!dataFolder.exists()) { dataFolder.mkdirs(); } File dbFile = new File(dataFolder, "players.db"); HikariConfig config = new HikariConfig(); config.setJdbcUrl("jdbc:sqlite:" + dbFile.getAbsolutePath()); config.setMaximumPoolSize(10); config.setConnectionTimeout(30000); dataSource = new HikariDataSource(config); createTables(); } private void createTables() { String createTableSQL = "CREATE TABLE IF NOT EXISTS players (" + "uuid TEXT PRIMARY KEY," + "username TEXT NOT NULL," + "assigned_name TEXT," + "password_hash TEXT NOT NULL," + "ip_address TEXT NOT NULL," + "last_login INTEGER NOT NULL," + "last_world TEXT," + "last_x REAL," + "last_y REAL," + "last_z REAL," + "last_yaw REAL," + "last_pitch REAL," + "captcha_verified INTEGER DEFAULT 0" + ")"; try (Connection conn = dataSource.getConnection(); PreparedStatement stmt = conn.prepareStatement(createTableSQL)) { stmt.executeUpdate(); } catch (SQLException e) { plugin.getLogger().severe("Could not create tables: " + e.getMessage()); } migrateAssignedNameColumn(); migrateLocationColumns(); migrateCaptchaColumn(); createEmailDiscordTables(); } private void createEmailDiscordTables() { String emailTable = "CREATE TABLE IF NOT EXISTS player_emails (" + "uuid TEXT PRIMARY KEY," + "email TEXT NOT NULL" + ")"; String discordTable = "CREATE TABLE IF NOT EXISTS player_discord (" + "uuid TEXT PRIMARY KEY," + "discord_id TEXT NOT NULL" + ")"; try (Connection conn = dataSource.getConnection()) { try (PreparedStatement stmt = conn.prepareStatement(emailTable)) { stmt.executeUpdate(); } try (PreparedStatement stmt = conn.prepareStatement(discordTable)) { stmt.executeUpdate(); } } catch (SQLException e) { plugin.getLogger().severe("Could not create email/discord tables: " + e.getMessage()); } } private void migrateAssignedNameColumn() { try (Connection conn = dataSource.getConnection()) { ResultSet rs = conn.getMetaData().getColumns(null, null, "players", "assigned_name"); if (!rs.next()) { try (PreparedStatement stmt = conn.prepareStatement("ALTER TABLE players ADD COLUMN assigned_name TEXT")) { stmt.executeUpdate(); } } } catch (SQLException e) { plugin.getLogger().warning("Could not migrate assigned_name column: " + e.getMessage()); } } private void migrateLocationColumns() { try (Connection conn = dataSource.getConnection()) { ResultSet rs = conn.getMetaData().getColumns(null, null, "players", "last_world"); if (!rs.next()) { try (PreparedStatement stmt = conn.prepareStatement("ALTER TABLE players ADD COLUMN last_world TEXT")) { stmt.executeUpdate(); } try (PreparedStatement stmt = conn.prepareStatement("ALTER TABLE players ADD COLUMN last_x REAL")) { stmt.executeUpdate(); } try (PreparedStatement stmt = conn.prepareStatement("ALTER TABLE players ADD COLUMN last_y REAL")) { stmt.executeUpdate(); } try (PreparedStatement stmt = conn.prepareStatement("ALTER TABLE players ADD COLUMN last_z REAL")) { stmt.executeUpdate(); } try (PreparedStatement stmt = conn.prepareStatement("ALTER TABLE players ADD COLUMN last_yaw REAL")) { stmt.executeUpdate(); } try (PreparedStatement stmt = conn.prepareStatement("ALTER TABLE players ADD COLUMN last_pitch REAL")) { stmt.executeUpdate(); } plugin.getLogger().info("Migrated database: Added last location columns"); } } catch (SQLException e) { plugin.getLogger().warning("Could not migrate location columns: " + e.getMessage()); } } private void migrateCaptchaColumn() { try (Connection conn = dataSource.getConnection()) { ResultSet rs = conn.getMetaData().getColumns(null, null, "players", "captcha_verified"); if (!rs.next()) { try (PreparedStatement stmt = conn.prepareStatement("ALTER TABLE players ADD COLUMN captcha_verified INTEGER DEFAULT 0")) { stmt.executeUpdate(); } plugin.getLogger().info("Migrated database: Added captcha_verified column"); } } catch (SQLException e) { plugin.getLogger().warning("Could not migrate captcha_verified column: " + e.getMessage()); } } public CompletableFuture getPlayerData(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()) { PlayerData data = new PlayerData( UUID.fromString(rs.getString("uuid")), rs.getString("username"), rs.getString("assigned_name"), rs.getString("password_hash"), rs.getString("ip_address"), rs.getLong("last_login"), null, null ); data.setLastWorld(rs.getString("last_world")); data.setLastX(rs.getDouble("last_x")); data.setLastY(rs.getDouble("last_y")); data.setLastZ(rs.getDouble("last_z")); data.setLastYaw((float) rs.getDouble("last_yaw")); data.setLastPitch((float) rs.getDouble("last_pitch")); data.setCaptchaVerified(rs.getInt("captcha_verified") == 1); return data; } } catch (SQLException e) { plugin.getLogger().severe("Error loading player data: " + e.getMessage()); } return null; }); } public CompletableFuture savePlayerData(PlayerData data) { return CompletableFuture.runAsync(() -> { String sql = "INSERT OR REPLACE INTO players (uuid, username, assigned_name, password_hash, ip_address, last_login, last_world, last_x, last_y, last_z, last_yaw, last_pitch, captcha_verified) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; try (Connection conn = dataSource.getConnection(); PreparedStatement stmt = conn.prepareStatement(sql)) { stmt.setString(1, data.getUuid().toString()); stmt.setString(2, data.getUsername()); stmt.setString(3, data.getAssignedName()); stmt.setString(4, data.getPasswordHash()); stmt.setString(5, data.getIpAddress()); stmt.setLong(6, data.getLastLogin()); stmt.setString(7, data.getLastWorld()); stmt.setDouble(8, data.getLastX()); stmt.setDouble(9, data.getLastY()); stmt.setDouble(10, data.getLastZ()); stmt.setDouble(11, data.getLastYaw()); stmt.setDouble(12, data.getLastPitch()); stmt.setInt(13, data.isCaptchaVerified() ? 1 : 0); stmt.executeUpdate(); } catch (SQLException e) { plugin.getLogger().severe("Error saving player data: " + e.getMessage()); } }); } public CompletableFuture playerExists(UUID uuid) { return CompletableFuture.supplyAsync(() -> { String sql = "SELECT COUNT(*) FROM players WHERE uuid = ?"; try (Connection conn = dataSource.getConnection(); PreparedStatement stmt = conn.prepareStatement(sql)) { stmt.setString(1, uuid.toString()); ResultSet rs = stmt.executeQuery(); return rs.next() && rs.getInt(1) > 0; } catch (SQLException e) { plugin.getLogger().severe("Error checking player existence: " + e.getMessage()); return false; } }); } public CompletableFuture getAccountCountByIP(String ipAddress) { return CompletableFuture.supplyAsync(() -> { String sql = "SELECT COUNT(*) FROM players WHERE ip_address = ?"; try (Connection conn = dataSource.getConnection(); PreparedStatement stmt = conn.prepareStatement(sql)) { stmt.setString(1, ipAddress); ResultSet rs = stmt.executeQuery(); return rs.next() ? rs.getInt(1) : 0; } catch (SQLException e) { plugin.getLogger().severe("Error counting accounts by IP: " + e.getMessage()); return 0; } }); } public CompletableFuture updatePassword(UUID uuid, String newPasswordHash) { return CompletableFuture.runAsync(() -> { String sql = "UPDATE players SET password_hash = ? WHERE uuid = ?"; try (Connection conn = dataSource.getConnection(); PreparedStatement stmt = conn.prepareStatement(sql)) { stmt.setString(1, newPasswordHash); stmt.setString(2, uuid.toString()); stmt.executeUpdate(); } catch (SQLException e) { plugin.getLogger().severe("Error updating password: " + e.getMessage()); } }); } public CompletableFuture findPlayerByUsername(String username) { return CompletableFuture.supplyAsync(() -> { String sql = "SELECT uuid FROM players WHERE username = ? OR assigned_name = ?"; try (Connection conn = dataSource.getConnection(); PreparedStatement stmt = conn.prepareStatement(sql)) { stmt.setString(1, username); stmt.setString(2, username); ResultSet rs = stmt.executeQuery(); if (rs.next()) { return UUID.fromString(rs.getString("uuid")); } } catch (SQLException e) { plugin.getLogger().severe("Error finding player by username: " + e.getMessage()); } return null; }); } public CompletableFuture isNameTaken(String name, UUID excludeUuid) { return CompletableFuture.supplyAsync(() -> { String sql = "SELECT COUNT(*) FROM players WHERE (username = ? OR assigned_name = ?) AND uuid != ?"; try (Connection conn = dataSource.getConnection(); PreparedStatement stmt = conn.prepareStatement(sql)) { stmt.setString(1, name); stmt.setString(2, name); stmt.setString(3, excludeUuid.toString()); ResultSet rs = stmt.executeQuery(); return rs.next() && rs.getInt(1) > 0; } catch (SQLException e) { plugin.getLogger().severe("Error checking name availability: " + e.getMessage()); return false; } }); } public CompletableFuture saveEmail(UUID uuid, String email) { return CompletableFuture.supplyAsync(() -> { String sql = "INSERT OR REPLACE INTO player_emails (uuid, email) VALUES (?, ?)"; try (Connection conn = dataSource.getConnection(); PreparedStatement stmt = conn.prepareStatement(sql)) { stmt.setString(1, uuid.toString()); stmt.setString(2, email); stmt.executeUpdate(); return true; } catch (SQLException e) { plugin.getLogger().severe("Error saving email: " + e.getMessage()); return false; } }); } public CompletableFuture getEmail(UUID uuid) { return CompletableFuture.supplyAsync(() -> { String sql = "SELECT email FROM player_emails 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.getString("email"); } } catch (SQLException e) { plugin.getLogger().severe("Error getting email: " + e.getMessage()); } return null; }); } public CompletableFuture saveDiscord(UUID uuid, String discordId) { return CompletableFuture.supplyAsync(() -> { String sql = "INSERT OR REPLACE INTO player_discord (uuid, discord_id) VALUES (?, ?)"; try (Connection conn = dataSource.getConnection(); PreparedStatement stmt = conn.prepareStatement(sql)) { stmt.setString(1, uuid.toString()); stmt.setString(2, discordId); stmt.executeUpdate(); return true; } catch (SQLException e) { plugin.getLogger().severe("Error saving discord: " + e.getMessage()); return false; } }); } public CompletableFuture getDiscord(UUID uuid) { return CompletableFuture.supplyAsync(() -> { String sql = "SELECT discord_id FROM player_discord 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.getString("discord_id"); } } catch (SQLException e) { plugin.getLogger().severe("Error getting discord: " + e.getMessage()); } return null; }); } public void close() { if (dataSource != null && !dataSource.isClosed()) { dataSource.close(); } } }