package com.guardianbot.database; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; public class DatabaseManager { private Connection connection; public void initialize() throws SQLException { connection = DriverManager.getConnection("jdbc:sqlite:guardianbot.db"); createTables(); } private void createTables() throws SQLException { try (Statement stmt = connection.createStatement()) { stmt.execute( "CREATE TABLE IF NOT EXISTS user_levels (" + "guild_id TEXT NOT NULL, " + "user_id TEXT NOT NULL, " + "xp INTEGER DEFAULT 0, " + "level INTEGER DEFAULT 0, " + "messages INTEGER DEFAULT 0, " + "PRIMARY KEY (guild_id, user_id))" ); stmt.execute( "CREATE TABLE IF NOT EXISTS warnings (" + "id INTEGER PRIMARY KEY AUTOINCREMENT, " + "guild_id TEXT NOT NULL, " + "user_id TEXT NOT NULL, " + "moderator_id TEXT NOT NULL, " + "reason TEXT NOT NULL, " + "timestamp INTEGER NOT NULL)" ); stmt.execute( "CREATE TABLE IF NOT EXISTS mutes (" + "guild_id TEXT NOT NULL, " + "user_id TEXT NOT NULL, " + "moderator_id TEXT NOT NULL, " + "reason TEXT NOT NULL, " + "muted_at INTEGER NOT NULL, " + "duration_minutes INTEGER NOT NULL, " + "PRIMARY KEY (guild_id, user_id))" ); stmt.execute( "CREATE TABLE IF NOT EXISTS moderation_logs (" + "id INTEGER PRIMARY KEY AUTOINCREMENT, " + "guild_id TEXT NOT NULL, " + "user_id TEXT NOT NULL, " + "moderator_id TEXT NOT NULL, " + "action TEXT NOT NULL, " + "reason TEXT NOT NULL, " + "timestamp INTEGER NOT NULL)" ); stmt.execute( "CREATE TABLE IF NOT EXISTS channel_activity (" + "guild_id TEXT NOT NULL, " + "channel_id TEXT NOT NULL, " + "message_count INTEGER DEFAULT 0, " + "last_message_time INTEGER DEFAULT 0, " + "PRIMARY KEY (guild_id, channel_id))" ); stmt.execute( "CREATE TABLE IF NOT EXISTS member_tracking (" + "guild_id TEXT NOT NULL, " + "date TEXT NOT NULL, " + "joins INTEGER DEFAULT 0, " + "leaves INTEGER DEFAULT 0, " + "PRIMARY KEY (guild_id, date))" ); stmt.execute( "CREATE TABLE IF NOT EXISTS voice_activity (" + "guild_id TEXT NOT NULL, " + "user_id TEXT NOT NULL, " + "channel_id TEXT NOT NULL, " + "duration_seconds INTEGER DEFAULT 0, " + "last_join INTEGER DEFAULT 0, " + "PRIMARY KEY (guild_id, user_id, channel_id))" ); stmt.execute( "CREATE TABLE IF NOT EXISTS milestones (" + "id INTEGER PRIMARY KEY AUTOINCREMENT, " + "guild_id TEXT NOT NULL, " + "name TEXT NOT NULL, " + "type TEXT NOT NULL, " + "target_value INTEGER NOT NULL, " + "target_channel_id TEXT, " + "action_type TEXT NOT NULL, " + "action_data TEXT NOT NULL, " + "triggered INTEGER DEFAULT 0)" ); stmt.execute( "CREATE TABLE IF NOT EXISTS scheduled_events (" + "id INTEGER PRIMARY KEY AUTOINCREMENT, " + "guild_id TEXT NOT NULL, " + "name TEXT NOT NULL, " + "cron_expression TEXT NOT NULL, " + "action_type TEXT NOT NULL, " + "action_data TEXT NOT NULL, " + "target_channel_id TEXT NOT NULL, " + "enabled INTEGER DEFAULT 1, " + "last_run INTEGER DEFAULT 0)" ); } } public void addXp(String guildId, String userId, int xp) throws SQLException { String sql = "INSERT INTO user_levels (guild_id, user_id, xp, messages) VALUES (?, ?, ?, 1) " + "ON CONFLICT(guild_id, user_id) DO UPDATE SET xp = xp + ?, messages = messages + 1"; try (PreparedStatement stmt = connection.prepareStatement(sql)) { stmt.setString(1, guildId); stmt.setString(2, userId); stmt.setInt(3, xp); stmt.setInt(4, xp); stmt.executeUpdate(); } } public int getXp(String guildId, String userId) throws SQLException { String sql = "SELECT xp FROM user_levels WHERE guild_id = ? AND user_id = ?"; try (PreparedStatement stmt = connection.prepareStatement(sql)) { stmt.setString(1, guildId); stmt.setString(2, userId); ResultSet rs = stmt.executeQuery(); return rs.next() ? rs.getInt("xp") : 0; } } public void setLevel(String guildId, String userId, int level) throws SQLException { String sql = "UPDATE user_levels SET level = ? WHERE guild_id = ? AND user_id = ?"; try (PreparedStatement stmt = connection.prepareStatement(sql)) { stmt.setInt(1, level); stmt.setString(2, guildId); stmt.setString(3, userId); stmt.executeUpdate(); } } public int getLevel(String guildId, String userId) throws SQLException { String sql = "SELECT level FROM user_levels WHERE guild_id = ? AND user_id = ?"; try (PreparedStatement stmt = connection.prepareStatement(sql)) { stmt.setString(1, guildId); stmt.setString(2, userId); ResultSet rs = stmt.executeQuery(); return rs.next() ? rs.getInt("level") : 0; } } public List getTopLevels(String guildId, int limit) throws SQLException { String sql = "SELECT user_id, xp, level, messages FROM user_levels WHERE guild_id = ? ORDER BY xp DESC LIMIT ?"; List top = new ArrayList<>(); try (PreparedStatement stmt = connection.prepareStatement(sql)) { stmt.setString(1, guildId); stmt.setInt(2, limit); ResultSet rs = stmt.executeQuery(); while (rs.next()) { top.add(new UserLevel( rs.getString("user_id"), rs.getInt("xp"), rs.getInt("level"), rs.getInt("messages") )); } } return top; } public void addWarning(String guildId, String userId, String moderatorId, String reason) throws SQLException { String sql = "INSERT INTO warnings (guild_id, user_id, moderator_id, reason, timestamp) VALUES (?, ?, ?, ?, ?)"; try (PreparedStatement stmt = connection.prepareStatement(sql)) { stmt.setString(1, guildId); stmt.setString(2, userId); stmt.setString(3, moderatorId); stmt.setString(4, reason); stmt.setLong(5, System.currentTimeMillis()); stmt.executeUpdate(); } } public int getWarningCount(String guildId, String userId) throws SQLException { String sql = "SELECT COUNT(*) FROM warnings WHERE guild_id = ? AND user_id = ?"; try (PreparedStatement stmt = connection.prepareStatement(sql)) { stmt.setString(1, guildId); stmt.setString(2, userId); ResultSet rs = stmt.executeQuery(); return rs.next() ? rs.getInt(1) : 0; } } public void addMute(String guildId, String userId, String moderatorId, String reason, int durationMinutes) throws SQLException { String sql = "INSERT INTO mutes (guild_id, user_id, moderator_id, reason, muted_at, duration_minutes) VALUES (?, ?, ?, ?, ?, ?) " + "ON CONFLICT(guild_id, user_id) DO UPDATE SET moderator_id = ?, reason = ?, muted_at = ?, duration_minutes = ?"; try (PreparedStatement stmt = connection.prepareStatement(sql)) { long now = System.currentTimeMillis(); stmt.setString(1, guildId); stmt.setString(2, userId); stmt.setString(3, moderatorId); stmt.setString(4, reason); stmt.setLong(5, now); stmt.setInt(6, durationMinutes); stmt.setString(7, moderatorId); stmt.setString(8, reason); stmt.setLong(9, now); stmt.setInt(10, durationMinutes); stmt.executeUpdate(); } } public void removeMute(String guildId, String userId) throws SQLException { String sql = "DELETE FROM mutes WHERE guild_id = ? AND user_id = ?"; try (PreparedStatement stmt = connection.prepareStatement(sql)) { stmt.setString(1, guildId); stmt.setString(2, userId); stmt.executeUpdate(); } } public boolean isMuted(String guildId, String userId) throws SQLException { String sql = "SELECT muted_at, duration_minutes FROM mutes WHERE guild_id = ? AND user_id = ?"; try (PreparedStatement stmt = connection.prepareStatement(sql)) { stmt.setString(1, guildId); stmt.setString(2, userId); ResultSet rs = stmt.executeQuery(); if (rs.next()) { long mutedAt = rs.getLong("muted_at"); int duration = rs.getInt("duration_minutes"); long expiresAt = mutedAt + (duration * 60000L); if (System.currentTimeMillis() < expiresAt) { return true; } else { removeMute(guildId, userId); return false; } } return false; } } public void logModeration(String guildId, String userId, String moderatorId, String action, String reason) throws SQLException { String sql = "INSERT INTO moderation_logs (guild_id, user_id, moderator_id, action, reason, timestamp) VALUES (?, ?, ?, ?, ?, ?)"; try (PreparedStatement stmt = connection.prepareStatement(sql)) { stmt.setString(1, guildId); stmt.setString(2, userId); stmt.setString(3, moderatorId); stmt.setString(4, action); stmt.setString(5, reason); stmt.setLong(6, System.currentTimeMillis()); stmt.executeUpdate(); } } public void incrementChannelActivity(String guildId, String channelId) throws SQLException { String sql = "INSERT INTO channel_activity (guild_id, channel_id, message_count, last_message_time) VALUES (?, ?, 1, ?) " + "ON CONFLICT(guild_id, channel_id) DO UPDATE SET message_count = message_count + 1, last_message_time = ?"; long now = System.currentTimeMillis(); try (PreparedStatement stmt = connection.prepareStatement(sql)) { stmt.setString(1, guildId); stmt.setString(2, channelId); stmt.setLong(3, now); stmt.setLong(4, now); stmt.executeUpdate(); } } public Map getChannelActivity(String guildId) throws SQLException { String sql = "SELECT channel_id, message_count, last_message_time FROM channel_activity WHERE guild_id = ?"; Map activity = new HashMap<>(); try (PreparedStatement stmt = connection.prepareStatement(sql)) { stmt.setString(1, guildId); ResultSet rs = stmt.executeQuery(); while (rs.next()) { activity.put(rs.getString("channel_id"), new ChannelActivity( rs.getInt("message_count"), rs.getLong("last_message_time") )); } } return activity; } public int getTotalMessagesToday(String guildId) throws SQLException { String sql = "SELECT SUM(message_count) FROM channel_activity WHERE guild_id = ? AND last_message_time > ?"; long oneDayAgo = System.currentTimeMillis() - 86400000L; try (PreparedStatement stmt = connection.prepareStatement(sql)) { stmt.setString(1, guildId); stmt.setLong(2, oneDayAgo); ResultSet rs = stmt.executeQuery(); return rs.next() ? rs.getInt(1) : 0; } } public void trackMemberJoin(String guildId, String date) throws SQLException { String sql = "INSERT INTO member_tracking (guild_id, date, joins) VALUES (?, ?, 1) " + "ON CONFLICT(guild_id, date) DO UPDATE SET joins = joins + 1"; try (PreparedStatement stmt = connection.prepareStatement(sql)) { stmt.setString(1, guildId); stmt.setString(2, date); stmt.executeUpdate(); } } public void trackMemberLeave(String guildId, String date) throws SQLException { String sql = "INSERT INTO member_tracking (guild_id, date, leaves) VALUES (?, ?, 1) " + "ON CONFLICT(guild_id, date) DO UPDATE SET leaves = leaves + 1"; try (PreparedStatement stmt = connection.prepareStatement(sql)) { stmt.setString(1, guildId); stmt.setString(2, date); stmt.executeUpdate(); } } public MemberStats getMemberStats(String guildId, String date) throws SQLException { String sql = "SELECT joins, leaves FROM member_tracking WHERE guild_id = ? AND date = ?"; try (PreparedStatement stmt = connection.prepareStatement(sql)) { stmt.setString(1, guildId); stmt.setString(2, date); ResultSet rs = stmt.executeQuery(); if (rs.next()) { return new MemberStats(rs.getInt("joins"), rs.getInt("leaves")); } return new MemberStats(0, 0); } } public void updateVoiceActivity(String guildId, String userId, String channelId, long durationSeconds) throws SQLException { String sql = "INSERT INTO voice_activity (guild_id, user_id, channel_id, duration_seconds, last_join) VALUES (?, ?, ?, ?, ?) " + "ON CONFLICT(guild_id, user_id, channel_id) DO UPDATE SET duration_seconds = duration_seconds + ?, last_join = ?"; long now = System.currentTimeMillis(); try (PreparedStatement stmt = connection.prepareStatement(sql)) { stmt.setString(1, guildId); stmt.setString(2, userId); stmt.setString(3, channelId); stmt.setLong(4, durationSeconds); stmt.setLong(5, now); stmt.setLong(6, durationSeconds); stmt.setLong(7, now); stmt.executeUpdate(); } } public long getTotalVoiceTime(String guildId, String userId) throws SQLException { String sql = "SELECT SUM(duration_seconds) FROM voice_activity WHERE guild_id = ? AND user_id = ?"; try (PreparedStatement stmt = connection.prepareStatement(sql)) { stmt.setString(1, guildId); stmt.setString(2, userId); ResultSet rs = stmt.executeQuery(); return rs.next() ? rs.getLong(1) : 0; } } public void createMilestone(String guildId, String name, String type, int targetValue, String targetChannelId, String actionType, String actionData) throws SQLException { String sql = "INSERT INTO milestones (guild_id, name, type, target_value, target_channel_id, action_type, action_data) " + "VALUES (?, ?, ?, ?, ?, ?, ?)"; try (PreparedStatement stmt = connection.prepareStatement(sql)) { stmt.setString(1, guildId); stmt.setString(2, name); stmt.setString(3, type); stmt.setInt(4, targetValue); stmt.setString(5, targetChannelId); stmt.setString(6, actionType); stmt.setString(7, actionData); stmt.executeUpdate(); } } public List getMilestones(String guildId) throws SQLException { String sql = "SELECT id, name, type, target_value, target_channel_id, action_type, action_data, triggered FROM milestones WHERE guild_id = ?"; List milestones = new ArrayList<>(); try (PreparedStatement stmt = connection.prepareStatement(sql)) { stmt.setString(1, guildId); ResultSet rs = stmt.executeQuery(); while (rs.next()) { milestones.add(new Milestone( rs.getInt("id"), rs.getString("name"), rs.getString("type"), rs.getInt("target_value"), rs.getString("target_channel_id"), rs.getString("action_type"), rs.getString("action_data"), rs.getInt("triggered") == 1 )); } } return milestones; } public void markMilestoneTriggered(int milestoneId) throws SQLException { String sql = "UPDATE milestones SET triggered = 1 WHERE id = ?"; try (PreparedStatement stmt = connection.prepareStatement(sql)) { stmt.setInt(1, milestoneId); stmt.executeUpdate(); } } public void deleteMilestone(int milestoneId) throws SQLException { String sql = "DELETE FROM milestones WHERE id = ?"; try (PreparedStatement stmt = connection.prepareStatement(sql)) { stmt.setInt(1, milestoneId); stmt.executeUpdate(); } } public void createScheduledEvent(String guildId, String name, String cronExpression, String actionType, String actionData, String targetChannelId) throws SQLException { String sql = "INSERT INTO scheduled_events (guild_id, name, cron_expression, action_type, action_data, target_channel_id) " + "VALUES (?, ?, ?, ?, ?, ?)"; try (PreparedStatement stmt = connection.prepareStatement(sql)) { stmt.setString(1, guildId); stmt.setString(2, name); stmt.setString(3, cronExpression); stmt.setString(4, actionType); stmt.setString(5, actionData); stmt.setString(6, targetChannelId); stmt.executeUpdate(); } } public List getScheduledEvents(String guildId) throws SQLException { String sql = "SELECT id, name, cron_expression, action_type, action_data, target_channel_id, enabled, last_run " + "FROM scheduled_events WHERE guild_id = ?"; List events = new ArrayList<>(); try (PreparedStatement stmt = connection.prepareStatement(sql)) { stmt.setString(1, guildId); ResultSet rs = stmt.executeQuery(); while (rs.next()) { events.add(new ScheduledEvent( rs.getInt("id"), rs.getString("name"), rs.getString("cron_expression"), rs.getString("action_type"), rs.getString("action_data"), rs.getString("target_channel_id"), rs.getInt("enabled") == 1, rs.getLong("last_run") )); } } return events; } public void updateScheduledEventLastRun(int eventId, long timestamp) throws SQLException { String sql = "UPDATE scheduled_events SET last_run = ? WHERE id = ?"; try (PreparedStatement stmt = connection.prepareStatement(sql)) { stmt.setLong(1, timestamp); stmt.setInt(2, eventId); stmt.executeUpdate(); } } public void toggleScheduledEvent(int eventId, boolean enabled) throws SQLException { String sql = "UPDATE scheduled_events SET enabled = ? WHERE id = ?"; try (PreparedStatement stmt = connection.prepareStatement(sql)) { stmt.setInt(1, enabled ? 1 : 0); stmt.setInt(2, eventId); stmt.executeUpdate(); } } public void deleteScheduledEvent(int eventId) throws SQLException { String sql = "DELETE FROM scheduled_events WHERE id = ?"; try (PreparedStatement stmt = connection.prepareStatement(sql)) { stmt.setInt(1, eventId); stmt.executeUpdate(); } } public void close() { try { if (connection != null && !connection.isClosed()) { connection.close(); } } catch (SQLException e) { e.printStackTrace(); } } public static class UserLevel { public final String userId; public final int xp; public final int level; public final int messages; public UserLevel(String userId, int xp, int level, int messages) { this.userId = userId; this.xp = xp; this.level = level; this.messages = messages; } } public static class ChannelActivity { public final int messageCount; public final long lastMessageTime; public ChannelActivity(int messageCount, long lastMessageTime) { this.messageCount = messageCount; this.lastMessageTime = lastMessageTime; } } public static class MemberStats { public final int joins; public final int leaves; public MemberStats(int joins, int leaves) { this.joins = joins; this.leaves = leaves; } } public static class Milestone { public final int id; public final String name; public final String type; public final int targetValue; public final String targetChannelId; public final String actionType; public final String actionData; public final boolean triggered; public Milestone(int id, String name, String type, int targetValue, String targetChannelId, String actionType, String actionData, boolean triggered) { this.id = id; this.name = name; this.type = type; this.targetValue = targetValue; this.targetChannelId = targetChannelId; this.actionType = actionType; this.actionData = actionData; this.triggered = triggered; } } public static class ScheduledEvent { public final int id; public final String name; public final String cronExpression; public final String actionType; public final String actionData; public final String targetChannelId; public final boolean enabled; public final long lastRun; public ScheduledEvent(int id, String name, String cronExpression, String actionType, String actionData, String targetChannelId, boolean enabled, long lastRun) { this.id = id; this.name = name; this.cronExpression = cronExpression; this.actionType = actionType; this.actionData = actionData; this.targetChannelId = targetChannelId; this.enabled = enabled; this.lastRun = lastRun; } } }