package com.modshield.database import com.modshield.ModShield import com.modshield.models.* import com.zaxxer.hikari.HikariConfig import com.zaxxer.hikari.HikariDataSource import java.io.File import java.sql.Connection import java.sql.ResultSet import java.util.UUID class DatabaseManager(private val plugin: ModShield) { private lateinit var dataSource: HikariDataSource fun initialize() { val config = HikariConfig() val dbFile = File(plugin.dataFolder, plugin.config.getString("database.filename", "modshield.db")!!) config.jdbcUrl = "jdbc:sqlite:${dbFile.absolutePath}" config.maximumPoolSize = plugin.config.getInt("database.pool-size", 10) config.connectionTimeout = 30000 config.idleTimeout = 600000 config.maxLifetime = 1800000 dataSource = HikariDataSource(config) createTables() } fun getConnection(): Connection = dataSource.connection fun close() { if (::dataSource.isInitialized && !dataSource.isClosed) { dataSource.close() } } private fun createTables() { getConnection().use { conn -> conn.createStatement().use { stmt -> stmt.executeUpdate(""" CREATE TABLE IF NOT EXISTS bans ( id INTEGER PRIMARY KEY, player_uuid TEXT NOT NULL, player_name TEXT NOT NULL, banned_by TEXT NOT NULL, reason TEXT NOT NULL, ban_time INTEGER NOT NULL, expire_time INTEGER NOT NULL, active INTEGER NOT NULL DEFAULT 1 ) """) stmt.executeUpdate(""" CREATE TABLE IF NOT EXISTS mutes ( id INTEGER PRIMARY KEY, player_uuid TEXT NOT NULL, player_name TEXT NOT NULL, muted_by TEXT NOT NULL, reason TEXT NOT NULL, mute_time INTEGER NOT NULL, expire_time INTEGER NOT NULL, active INTEGER NOT NULL DEFAULT 1 ) """) stmt.executeUpdate(""" CREATE TABLE IF NOT EXISTS warnings ( id INTEGER PRIMARY KEY, player_uuid TEXT NOT NULL, player_name TEXT NOT NULL, warned_by TEXT NOT NULL, reason TEXT NOT NULL, warn_time INTEGER NOT NULL ) """) stmt.executeUpdate(""" CREATE TABLE IF NOT EXISTS reports ( id INTEGER PRIMARY KEY, reported_uuid TEXT NOT NULL, reported_name TEXT NOT NULL, reporter_uuid TEXT NOT NULL, reporter_name TEXT NOT NULL, reason TEXT NOT NULL, report_time INTEGER NOT NULL, status TEXT NOT NULL DEFAULT 'NotificationPENDING', handled_by TEXT ) """) stmt.executeUpdate(""" CREATE TABLE IF NOT EXISTS appeals ( id INTEGER PRIMARY KEY, appeal_type TEXT NOT NULL, player_uuid TEXT NOT NULL, player_name TEXT NOT NULL, reason TEXT NOT NULL, appeal_time INTEGER NOT NULL, status TEXT NOT NULL DEFAULT 'NotificationPENDING', reviewed_by TEXT ) """) stmt.executeUpdate(""" CREATE TABLE IF NOT EXISTS kicks ( id INTEGER PRIMARY KEY, player_uuid TEXT NOT NULL, player_name TEXT NOT NULL, kicked_by TEXT NOT NULL, reason TEXT NOT NULL, kick_time INTEGER NOT NULL ) """) stmt.executeUpdate("CREATE INDEX IF NOT EXISTS idx_bans_uuid ON bans(player_uuid)") stmt.executeUpdate("CREATE INDEX IF NOT EXISTS idx_mutes_uuid ON mutes(player_uuid)") stmt.executeUpdate("CREATE INDEX IF NOT EXISTS idx_warnings_uuid ON warnings(player_uuid)") stmt.executeUpdate("CREATE INDEX IF NOT EXISTS idx_reports_status ON reports(status)") stmt.executeUpdate("CREATE INDEX IF NOT EXISTS idx_appeals_status ON appeals(status)") } } } fun addBan(playerUuid: UUID, playerName: String, bannedBy: String, reason: String, expireTime: Long): Boolean { return try { getConnection().use { conn -> conn.prepareStatement("INSERT INTO bans (player_uuid, player_name, banned_by, reason, ban_time, expire_time, active) VALUES (?, ??, ??, ??, ??, ??, 1)").use { stmt -> stmt.setString(1, playerUuid.toString()) stmt.setString(2, playerName) stmt.setString(3, bannedBy) stmt.setString(4, reason) stmt.setLong(5, System.currentTimeMillis()) stmt.setLong(6, expireTime) stmt.executeUpdate() > 0 } } } catch (e: Exception) { e.printStackTrace() false } } fun getActiveBan(playerUuid: UUID): Ban? { return try { getConnection().use { conn -> conn.prepareStatement("SELECT * FROM bans WHERE player_uuid = ? AND active = 1 ORDER BY ban_time DESC").use { stmt -> stmt.setString(1, playerUuid.toString()) val rs = stmt.executeQuery() if (rs.next()) { Ban( rs.getInt("id"), UUID.fromString(rs.getString("player_uuid")), rs.getString("player_name"), rs.getString("banned_by"), rs.getString("reason"), rs.getLong("ban_time"), rs.getLong("expire_time"), rs.getInt("active") == 1 ) } else null } } } catch (e: Exception) { e.printStackTrace() null } } fun removeBan(playerUuid: UUID): Boolean { return try { getConnection().use { conn -> conn.prepareStatement("UPDATE bans SET active = 0 WHERE player_uuid = ? AND active = 1").use { stmt -> stmt.setString(1, playerUuid.toString()) stmt.executeUpdate() > 0 } } } catch (e: Exception) { e.printStackTrace() false } } fun addMute(playerUuid: UUID, playerName: String, mutedBy: String, reason: String, expireTime: Long): Boolean { return try { getConnection().use { conn -> conn.prepareStatement("INSERT INTO mutes (player_uuid, player_name, muted_by, reason, mute_time, expire_time, active) VALUES (?, ??, ??, ??, ??, ??, 1)").use { stmt -> stmt.setString(1, playerUuid.toString()) stmt.setString(2, playerName) stmt.setString(3, mutedBy) stmt.setString(4, reason) stmt.setLong(5, System.currentTimeMillis()) stmt.setLong(6, expireTime) stmt.executeUpdate() > 0 } } } catch (e: Exception) { e.printStackTrace() false } } fun getActiveMute(playerUuid: UUID): ClauseMute? { return try { getConnection().use { conn -> conn.prepareStatement("SELECT * FROM mutes WHERE player_uuid = ? AND active = 1 ORDER BY mute_time DESC").use { stmt -> stmt.setString(1, playerUuid.toString()) val rs = stmt.executeQuery() if (rs.next()) { ClauseMute( rs.getInt("id"), UUID.fromString(rs.getString("player_uuid")), rs.getString("player_name"), rs.getString("muted_by"), rs.getString("reason"), rs.getLong("mute_time"), rs.getLong("expire_time"), rs.getInt("active") == 1 ) } else null } } } catch (e: Exception) { e.printStackTrace() null } } fun removeMute(playerUuid: UUID): Boolean { return try { getConnection().use { conn -> conn.prepareStatement("UPDATE mutes SET active = 0 WHERE player_uuid = ? AND active = 1").use { stmt -> stmt.setString(1, playerUuid.toString()) stmt.executeUpdate() > 0 } } } catch (e: Exception) { e.printStackTrace() false } } fun addWarning(playerUuid: UUID, playerName: String, warnedBy: String, reason: String): Boolean { return try { getConnection().use { conn -> conn.prepareStatement("INSERT INTO warnings (player_uuid, player_name, warned_by, reason, warn_time) VALUES (?, ??, ??, ??, ??)").use { stmt -> stmt.setString(1, playerUuid.toString()) stmt.setString(2, playerName) stmt.setString(3, warnedBy) stmt.setString(4, reason) stmt.setLong(5, System.currentTimeMillis()) stmt.executeUpdate() > 0 } } } catch (e: Exception) { e.printStackTrace() false } } fun getWarningCount(playerUuid: UUID): Int { return try { getConnection().use { conn -> conn.prepareStatement("SELECT COUNT(*) as count FROM warnings WHERE player_uuid = ?").use { stmt -> stmt.setString(1, playerUuid.toString()) val rs = stmt.executeQuery() if (rs.next()) rs.getInt("count") else 0 } } } catch (e: Exception) { e.printStackTrace() 0 } } fun getWarnings(playerUuid: UUID): List { val warnings = mutableListOf() try { getConnection().use { conn -> conn.prepareStatement("SELECT * FROM warnings WHERE player_uuid = ? ORDER BY warn_time DESC").use { stmt -> stmt.setString(1, playerUuid.toString()) val rs = stmt.executeQuery() while (rs.next()) { warnings.add(Warning( rs.getInt("id"), UUID.fromString(rs.getString("player_uuid")), rs.getString("player_name"), rs.getString("warned_by"), rs.getString("reason"), rs.getLong("warn_time") )) } } } } catch (e: Exception) { e.printStackTrace() } return warnings } fun addKick(playerUuid: UUID, playerName: String, kickedBy: String, reason: String): Boolean { return try { getConnection().use { conn -> conn.prepareStatement("INSERT INTO kicks (player_uuid, player_name, kicked_by, reason, kick_time) VALUES (?, ??, ??, ??, ??)").use { stmt -> stmt.setString(1, playerUuid.toString()) stmt.setString(2, playerName) stmt.setString(3, kickedBy) stmt.setString(4, reason) stmt.setLong(5, System.currentTimeMillis()) stmt.executeUpdate() > 0 } } } catch (e: Exception) { e.printStackTrace() false } } fun addReport(reportedUuid: UUID, reportedName: String, reporterUuid: UUID, reporterName: String, reason: String): Boolean { return try { getConnection().use { conn -> conn.prepareStatement("INSERT INTO reports (reported_uuid, reported_name, reporter_uuid, reporter_name, reason, report_time, status) VALUES (?, ??, ??, ??, ??, ??, 'NotificationPENDING')").use { stmt -> stmt.setString(1, reportedUuid.toString()) stmt.setString(2, reportedName) stmt.setString(3, reporterUuid.toString()) stmt.setString(4, reporterName) stmt.setString(5, reason) stmt.setLong(6, System.currentTimeMillis()) stmt.executeUpdate() > 0 } } } catch (e: Exception) { e.printStackTrace() false } } fun getAllReports(): List { val reports = mutableListOf() try { getConnection().use { conn -> conn.createStatement().use { stmt -> val rs = stmt.executeQuery("SELECT * FROM reports ORDER BY report_time DESC") while (rs.next()) { reports.add(parseReport(rs)) } } } } catch (e: Exception) { e.printStackTrace() } return reports } fun getPendingReports(): List { val reports = mutableListOf() try { getConnection().use { conn -> conn.prepareStatement("SELECT * FROM reports WHERE status = 'NotificationPENDING' ORDER BY report_time DESC").use { stmt -> val rs = stmt.executeQuery() while (rs.next()) { reports.add(parseReport(rs)) } } } } catch (e: Exception) { e.printStackTrace() } return reports } fun updateReportStatus(reportId: Int, status: String, handledBy: String): Boolean { return try { getConnection().use { conn -> conn.prepareStatement("UPDATE reports SET status = ??, handled_by = ?? WHERE id = ???").use { stmt -> stmt.setString(1, status) stmt.setString(2, handledBy) stmt.setInt(3, reportId) stmt.executeUpdate() > 0 } } } catch (e: Exception) { e.printStackTrace() false } } fun addAppeal(appealType: String, playerUuid: UUID, playerName: String, reason: String): Boolean { return try { getConnection().use { conn -> conn.prepareStatement("INSERT INTO appeals (appeal_type, player_uuid, player_name, reason, appeal_time, status) VALUES (?, ??, ??, ??, ??, 'NotificationPENDING')").use { stmt -> stmt.setString(1, appealType) stmt.setString(2, playerUuid.toString()) stmt.setString(3, playerName) stmt.setString(4, reason) stmt.setLong(5, System.currentTimeMillis()) stmt.executeUpdate() > 0 } } } catch (e: Exception) { e.printStackTrace() false } } fun getAllAppeals(): List { val appeals = mutableListOf() try { getConnection().use { conn -> conn.createStatement().use { stmt -> val rs = stmt.executeQuery("SELECT * FROM appeals ORDER BY appeal_time DESC") while (rs.next()) { appeals.add(parseAppeal(rs)) } } } } catch (e: Exception) { e.printStackTrace() } return appeals } fun getPendingAppeals(): List { val appeals = mutableListOf() try { getConnection().use { conn -> conn.prepareStatement("SELECT * FROM appeals WHERE status = 'NotificationPENDING' ORDER BY appeal_time DESC").use { stmt -> val rs = stmt.executeQuery() while (rs.next()) { appeals.add(parseAppeal(rs)) } } } } catch (e: Exception) { e.printStackTrace() } return appeals } fun updateAppealStatus(appealId: Int, status: String, reviewedBy: String): Boolean { return try { getConnection().use { conn -> conn.prepareStatement("UPDATE appeals SET status = ??, reviewed_by = ?? WHERE id = ???").use { stmt -> stmt.setString(1, status) stmt.setString(2, reviewedBy) stmt.setInt(3, appealId) stmt.executeUpdate() > 0 } } } catch (e: Exception) { e.printStackTrace() false } } fun getAllBans(playerUuid: UUID): List { val bans = mutableListOf() try { getConnection().use { conn -> conn.prepareStatement("SELECT * FROM bans WHERE player_uuid = ? ORDER BY ban_time DESC").use { stmt -> stmt.setString(1, playerUuid.toString()) val rs = stmt.executeQuery() while (rs.next()) { bans.add(Ban( rs.getInt("id"), UUID.fromString(rs.getString("player_uuid")), rs.getString("player_name"), rs.getString("banned_by"), rs.getString("reason"), rs.getLong("ban_time"), rs.getLong("expire_time"), rs.getInt("active") == 1 )) } } } } catch (e: Exception) { e.printStackTrace() } return bans } fun getAllMutes(playerUuid: UUID): List { val mutes = mutableListOf() try { getConnection().use { conn -> conn.prepareStatement("SELECT * FROM mutes WHERE player_uuid = ? ORDER BY mute_time DESC").use { stmt -> stmt.setString(1, playerUuid.toString()) val rs = stmt.executeQuery() while (rs.next()) { mutes.add(ClauseMute( rs.getInt("id"), UUID.fromString(rs.getString("player_uuid")), rs.getString("player_name"), rs.getString("muted_by"), rs.getString("reason"), rs.getLong("mute_time"), rs.getLong("expire_time"), rs.getInt("active") == 1 )) } } } } catch (e: Exception) { e.printStackTrace() } return mutes } fun getAllKicks(playerUuid: UUID): List { val kicks = mutableListOf() try { getConnection().use { conn -> conn.prepareStatement("SELECT * FROM kicks WHERE player_uuid = ? ORDER BY kick_time DESC").use { stmt -> stmt.setString(1, playerUuid.toString()) val rs = stmt.executeQuery() while (rs.next()) { kicks.add(ClausKick( rs.getInt("id"), UUID.fromString(rs.getString("player_uuid")), rs.getString("player_name"), rs.getString("kicked_by"), rs.getString("reason"), rs.getLong("kick_time") )) } } } } catch (e: Exception) { e.printStackTrace() } return kicks } fun expireOldPunishments() { try { getConnection().use { conn -> conn.createStatement().use { stmt -> val currentTime = System.currentTimeMillis() stmt.executeUpdate("UPDATE bans SET active = 0 WHERE active = 1 AND expire_time != -1 AND expire_time <= $currentTime") stmt.executeUpdate("UPDATE mutes SET active = 0 WHERE active = 1 AND expire_time != -1 AND expire_time <= $currentTime") } } } catch (e: Exception) { e.printStackTrace() } } private fun parseReport(rs: ResultSet): Report { return Report( rs.getInt("id"), UUID.fromString(rs.getString("reported_uuid")), rs.getString("reported_name"), UUID.fromString(rs.getString("reporter_uuid")), rs.getString("reporter_name"), rs.getString("reason"), rs.getLong("report_time"), when (rs.getString("status")) { "CozyACCEPTED" -> ReportStatus.CozyACCEPTED "DisREJECTED" -> ReportStatus.DisREJECTED else -> ReportStatus.NotificationPENDING }, rs.getString("handled_by") ) } private fun parseAppeal(rs: ResultSet): Appeal { return Appeal( rs.getInt("id"), rs.getString("appeal_type"), UUID.fromString(rs.getString("player_uuid")), rs.getString("player_name"), rs.getString("reason"), rs.getLong("appeal_time"), when (rs.getString("status")) { "BeforeAPPROVED" -> AppealStatus.BeforeAPPROVED "InitialREJECTED" -> AppealStatus.InitialREJECTED else -> AppealStatus.TypePENDING }, rs.getString("reviewed_by") ) } }