package de.simolzimol.mclogger.paper.database;

import com.google.gson.Gson;
import com.google.gson.GsonBuilder;
import com.google.gson.JsonObject;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import de.simolzimol.mclogger.paper.PaperLoggerPlugin;
import org.bukkit.configuration.file.FileConfiguration;

import java.sql.*;
import java.util.Map;
import java.util.concurrent.CompletableFuture;
import java.util.logging.Level;

/**
 * Manages the MariaDB connection pool and provides
 * helper methods for writing all log events.
 *
 * @author SimolZimol
 */
public class DatabaseManager {

    private final PaperLoggerPlugin plugin;
    private HikariDataSource dataSource;
    private String serverName;
    private int serverId = -1;
    private static final Gson GSON = new GsonBuilder().create();

    public DatabaseManager(PaperLoggerPlugin plugin) {
        this.plugin = plugin;
    }

    // --------------------------------------------------------
    // Connection Lifecycle
    // --------------------------------------------------------

    public boolean connect() {
        FileConfiguration cfg = plugin.getConfig();
        serverName = cfg.getString("server.name", "default");

        HikariConfig hk = new HikariConfig();
        hk.setDriverClassName("de.simolzimol.mclogger.lib.mariadb.jdbc.Driver");
        hk.setJdbcUrl(String.format("jdbc:mariadb://%s:%d/%s?useSSL=%b&autoReconnect=true&characterEncoding=UTF-8",
                cfg.getString("database.host", "localhost"),
                cfg.getInt("database.port", 3306),
                cfg.getString("database.database", "mclogger"),
                cfg.getBoolean("database.ssl", false)));
        hk.setUsername(cfg.getString("database.username", "root"));
        hk.setPassword(cfg.getString("database.password", ""));
        hk.setMaximumPoolSize(cfg.getInt("database.pool-size", 10));
        hk.setMinimumIdle(2);
        hk.setConnectionTimeout(30_000);
        hk.setIdleTimeout(600_000);
        hk.setMaxLifetime(1_800_000);
        hk.setPoolName("MCLogger-Paper");
        hk.addDataSourceProperty("cachePrepStmts", "true");
        hk.addDataSourceProperty("prepStmtCacheSize", "250");
        hk.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");

        try {
            dataSource = new HikariDataSource(hk);
            initializeTables();
            registerServer(cfg);
            plugin.getLogger().info("[MCLogger] Database connected - Server: " + serverName + " (ID: " + serverId + ")");
            return true;
        } catch (Exception e) {
            plugin.getLogger().log(Level.SEVERE, "[MCLogger] Database connection failed!", e);
            return false;
        }
    }

    private void initializeTables() throws SQLException {
        String[] ddl = {
            // servers
            "CREATE TABLE IF NOT EXISTS servers (" +
            "  id          INT AUTO_INCREMENT PRIMARY KEY," +
            "  server_name VARCHAR(100) NOT NULL," +
            "  server_type ENUM('paper','velocity') NOT NULL DEFAULT 'paper'," +
            "  ip_address  VARCHAR(45)," +
            "  mc_version  VARCHAR(100)," +
            "  first_seen  TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3)," +
            "  last_seen   TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3)," +
            "  UNIQUE KEY uq_server_name (server_name)" +
            ") ENGINE=InnoDB",

            // players
            "CREATE TABLE IF NOT EXISTS players (" +
            "  uuid               VARCHAR(36)  PRIMARY KEY," +
            "  username           VARCHAR(16)  NOT NULL," +
            "  display_name       VARCHAR(64)," +
            "  ip_address         VARCHAR(45)," +
            "  locale             VARCHAR(20)," +
            "  first_seen         TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3)," +
            "  last_seen          TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3)," +
            "  total_playtime_sec BIGINT       DEFAULT 0," +
            "  is_op              TINYINT(1)   DEFAULT 0," +
            "  INDEX idx_username (username)" +
            ") ENGINE=InnoDB",

            // player_sessions
            "CREATE TABLE IF NOT EXISTS player_sessions (" +
            "  id             BIGINT AUTO_INCREMENT PRIMARY KEY," +
            "  player_uuid    VARCHAR(36) NOT NULL," +
            "  player_name    VARCHAR(16) NOT NULL," +
            "  server_name    VARCHAR(100)," +
            "  login_time     TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3)," +
            "  logout_time    TIMESTAMP(3) NULL DEFAULT NULL," +
            "  duration_sec   INT," +
            "  ip_address     VARCHAR(45)," +
            "  country        VARCHAR(100)," +
            "  client_version VARCHAR(20)," +
            "  INDEX idx_ps_uuid   (player_uuid)," +
            "  INDEX idx_ps_server (server_name)," +
            "  INDEX idx_ps_login  (login_time)" +
            ") ENGINE=InnoDB",

            // player_chat
            "CREATE TABLE IF NOT EXISTS player_chat (" +
            "  id          BIGINT AUTO_INCREMENT PRIMARY KEY," +
            "  timestamp   TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3)," +
            "  player_uuid VARCHAR(36)," +
            "  player_name VARCHAR(16)," +
            "  server_name VARCHAR(100)," +
            "  world       VARCHAR(100)," +
            "  message     TEXT NOT NULL," +
            "  channel     VARCHAR(50) DEFAULT 'global'," +
            "  INDEX idx_chat_uuid      (player_uuid)," +
            "  INDEX idx_chat_timestamp (timestamp)," +
            "  INDEX idx_chat_server    (server_name)," +
            "  FULLTEXT INDEX ft_message (message)" +
            ") ENGINE=InnoDB",

            // player_commands
            "CREATE TABLE IF NOT EXISTS player_commands (" +
            "  id          BIGINT AUTO_INCREMENT PRIMARY KEY," +
            "  timestamp   TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3)," +
            "  player_uuid VARCHAR(36)," +
            "  player_name VARCHAR(16)," +
            "  server_name VARCHAR(100)," +
            "  world       VARCHAR(100)," +
            "  command     TEXT NOT NULL," +
            "  x           DOUBLE," +
            "  y           DOUBLE," +
            "  z           DOUBLE," +
            "  INDEX idx_cmd_uuid      (player_uuid)," +
            "  INDEX idx_cmd_timestamp (timestamp)," +
            "  INDEX idx_cmd_server    (server_name)" +
            ") ENGINE=InnoDB",

            // block_events
            "CREATE TABLE IF NOT EXISTS block_events (" +
            "  id          BIGINT AUTO_INCREMENT PRIMARY KEY," +
            "  timestamp   TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3)," +
            "  event_type  ENUM('break','place','ignite','burn','explode','fade','grow','dispense') NOT NULL," +
            "  player_uuid VARCHAR(36)," +
            "  player_name VARCHAR(16)," +
            "  server_name VARCHAR(100)," +
            "  world       VARCHAR(100) NOT NULL," +
            "  x           INT NOT NULL," +
            "  y           INT NOT NULL," +
            "  z           INT NOT NULL," +
            "  block_type  VARCHAR(100) NOT NULL," +
            "  block_data  VARCHAR(255)," +
            "  tool        VARCHAR(100)," +
            "  is_silk     TINYINT(1) DEFAULT 0," +
            "  INDEX idx_be_player    (player_uuid)," +
            "  INDEX idx_be_timestamp (timestamp)," +
            "  INDEX idx_be_world     (world)," +
            "  INDEX idx_be_type      (event_type)," +
            "  INDEX idx_be_server    (server_name)" +
            ") ENGINE=InnoDB",

            // player_deaths
            "CREATE TABLE IF NOT EXISTS player_deaths (" +
            "  id            BIGINT AUTO_INCREMENT PRIMARY KEY," +
            "  timestamp     TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3)," +
            "  player_uuid   VARCHAR(36) NOT NULL," +
            "  player_name   VARCHAR(16) NOT NULL," +
            "  server_name   VARCHAR(100)," +
            "  world         VARCHAR(100)," +
            "  x             DOUBLE," +
            "  y             DOUBLE," +
            "  z             DOUBLE," +
            "  death_message TEXT," +
            "  cause         VARCHAR(100)," +
            "  killer_uuid   VARCHAR(36)," +
            "  killer_name   VARCHAR(100)," +
            "  killer_type   VARCHAR(100)," +
            "  exp_level     INT," +
            "  items_lost    JSON," +
            "  INDEX idx_deaths_uuid      (player_uuid)," +
            "  INDEX idx_deaths_timestamp (timestamp)" +
            ") ENGINE=InnoDB",

            // entity_events
            "CREATE TABLE IF NOT EXISTS entity_events (" +
            "  id          BIGINT AUTO_INCREMENT PRIMARY KEY," +
            "  timestamp   TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3)," +
            "  event_type  ENUM('spawn','death','damage','tame','breed','transform','explode') NOT NULL," +
            "  server_name VARCHAR(100)," +
            "  world       VARCHAR(100)," +
            "  x           DOUBLE," +
            "  y           DOUBLE," +
            "  z           DOUBLE," +
            "  entity_type VARCHAR(100) NOT NULL," +
            "  entity_uuid VARCHAR(36)," +
            "  entity_name VARCHAR(100)," +
            "  player_uuid VARCHAR(36)," +
            "  player_name VARCHAR(16)," +
            "  cause       VARCHAR(100)," +
            "  damage      DOUBLE," +
            "  details     JSON," +
            "  INDEX idx_ee_timestamp (timestamp)," +
            "  INDEX idx_ee_type      (event_type)," +
            "  INDEX idx_ee_entity    (entity_type)," +
            "  INDEX idx_ee_player    (player_uuid)," +
            "  INDEX idx_ee_server    (server_name)" +
            ") ENGINE=InnoDB",

            // player_teleports
            "CREATE TABLE IF NOT EXISTS player_teleports (" +
            "  id          BIGINT AUTO_INCREMENT PRIMARY KEY," +
            "  timestamp   TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3)," +
            "  player_uuid VARCHAR(36) NOT NULL," +
            "  player_name VARCHAR(16) NOT NULL," +
            "  server_name VARCHAR(100)," +
            "  from_world  VARCHAR(100)," +
            "  from_x      DOUBLE," +
            "  from_y      DOUBLE," +
            "  from_z      DOUBLE," +
            "  to_world    VARCHAR(100)," +
            "  to_x        DOUBLE," +
            "  to_y        DOUBLE," +
            "  to_z        DOUBLE," +
            "  cause       VARCHAR(100)," +
            "  INDEX idx_tp_uuid      (player_uuid)," +
            "  INDEX idx_tp_timestamp (timestamp)" +
            ") ENGINE=InnoDB",

            // inventory_events
            "CREATE TABLE IF NOT EXISTS inventory_events (" +
            "  id             BIGINT AUTO_INCREMENT PRIMARY KEY," +
            "  timestamp      TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3)," +
            "  event_type     ENUM('pickup','drop','click','craft','enchant','anvil','trade') NOT NULL," +
            "  player_uuid    VARCHAR(36) NOT NULL," +
            "  player_name    VARCHAR(16) NOT NULL," +
            "  server_name    VARCHAR(100)," +
            "  world          VARCHAR(100)," +
            "  x              DOUBLE," +
            "  y              DOUBLE," +
            "  z              DOUBLE," +
            "  item_type      VARCHAR(100)," +
            "  item_amount    INT," +
            "  item_meta      JSON," +
            "  slot           INT," +
            "  inventory_type VARCHAR(100)," +
            "  INDEX idx_inv_uuid      (player_uuid)," +
            "  INDEX idx_inv_timestamp (timestamp)," +
            "  INDEX idx_inv_type      (event_type)" +
            ") ENGINE=InnoDB",

            // player_stats
            "CREATE TABLE IF NOT EXISTS player_stats (" +
            "  id          BIGINT AUTO_INCREMENT PRIMARY KEY," +
            "  timestamp   TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3)," +
            "  event_type  VARCHAR(100) NOT NULL," +
            "  player_uuid VARCHAR(36) NOT NULL," +
            "  player_name VARCHAR(16) NOT NULL," +
            "  server_name VARCHAR(100)," +
            "  old_value   VARCHAR(255)," +
            "  new_value   VARCHAR(255)," +
            "  details     JSON," +
            "  INDEX idx_pst_uuid      (player_uuid)," +
            "  INDEX idx_pst_timestamp (timestamp)," +
            "  INDEX idx_pst_type      (event_type)" +
            ") ENGINE=InnoDB",

            // world_events
            "CREATE TABLE IF NOT EXISTS world_events (" +
            "  id          BIGINT AUTO_INCREMENT PRIMARY KEY," +
            "  timestamp   TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3)," +
            "  event_type  VARCHAR(100) NOT NULL," +
            "  server_name VARCHAR(100)," +
            "  world       VARCHAR(100)," +
            "  x           DOUBLE," +
            "  y           DOUBLE," +
            "  z           DOUBLE," +
            "  details     JSON," +
            "  INDEX idx_we_timestamp (timestamp)," +
            "  INDEX idx_we_world     (world)," +
            "  INDEX idx_we_type      (event_type)" +
            ") ENGINE=InnoDB",

            // server_events
            "CREATE TABLE IF NOT EXISTS server_events (" +
            "  id          BIGINT AUTO_INCREMENT PRIMARY KEY," +
            "  timestamp   TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3)," +
            "  event_type  VARCHAR(100) NOT NULL," +
            "  server_name VARCHAR(100)," +
            "  message     TEXT," +
            "  details     JSON," +
            "  INDEX idx_se_timestamp (timestamp)," +
            "  INDEX idx_se_type      (event_type)," +
            "  INDEX idx_se_server    (server_name)" +
            ") ENGINE=InnoDB",

            // proxy_events
            "CREATE TABLE IF NOT EXISTS proxy_events (" +
            "  id          BIGINT AUTO_INCREMENT PRIMARY KEY," +
            "  timestamp   TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3)," +
            "  event_type  ENUM('login','disconnect','server_switch','command','chat','kick','proxy_start','proxy_stop') NOT NULL," +
            "  player_uuid VARCHAR(36)," +
            "  player_name VARCHAR(16)," +
            "  proxy_name  VARCHAR(100)," +
            "  from_server VARCHAR(100)," +
            "  to_server   VARCHAR(100)," +
            "  ip_address  VARCHAR(45)," +
            "  details     JSON," +
            "  INDEX idx_pe_uuid      (player_uuid)," +
            "  INDEX idx_pe_timestamp (timestamp)," +
            "  INDEX idx_pe_type      (event_type)," +
            "  INDEX idx_pe_proxy     (proxy_name)" +
            ") ENGINE=InnoDB",

            // sign_edits
            "CREATE TABLE IF NOT EXISTS sign_edits (" +
            "  id          BIGINT AUTO_INCREMENT PRIMARY KEY," +
            "  timestamp   TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3)," +
            "  player_uuid VARCHAR(36)," +
            "  player_name VARCHAR(16)," +
            "  server_name VARCHAR(100)," +
            "  world       VARCHAR(100)," +
            "  x           INT," +
            "  y           INT," +
            "  z           INT," +
            "  line1       VARCHAR(255)," +
            "  line2       VARCHAR(255)," +
            "  line3       VARCHAR(255)," +
            "  line4       VARCHAR(255)," +
            "  INDEX idx_sign_uuid      (player_uuid)," +
            "  INDEX idx_sign_timestamp (timestamp)" +
            ") ENGINE=InnoDB",

            // plugin_events
            "CREATE TABLE IF NOT EXISTS plugin_events (" +
            "  id          BIGINT AUTO_INCREMENT PRIMARY KEY," +
            "  timestamp   TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3)," +
            "  event_type  VARCHAR(100) NOT NULL," +
            "  plugin_name VARCHAR(100)," +
            "  server_name VARCHAR(100)," +
            "  player_uuid VARCHAR(36)," +
            "  player_name VARCHAR(16)," +
            "  actor_uuid  VARCHAR(36)," +
            "  actor_name  VARCHAR(64)," +
            "  target_type VARCHAR(50)," +
            "  target_id   VARCHAR(100)," +
            "  action      VARCHAR(255)," +
            "  details     JSON," +
            "  INDEX idx_plev_uuid      (player_uuid)," +
            "  INDEX idx_plev_timestamp (timestamp)," +
            "  INDEX idx_plev_type      (event_type)," +
            "  INDEX idx_plev_plugin    (plugin_name)," +
            "  INDEX idx_plev_actor     (actor_uuid)" +
            ") ENGINE=InnoDB",

            // view
            "CREATE OR REPLACE VIEW v_recent_activity AS" +
            " SELECT 'chat'    AS source, timestamp, player_name, server_name, message  AS detail FROM player_chat     WHERE timestamp >= NOW() - INTERVAL 24 HOUR" +
            " UNION ALL" +
            " SELECT 'command' AS source, timestamp, player_name, server_name, command  AS detail FROM player_commands WHERE timestamp >= NOW() - INTERVAL 24 HOUR" +
            " UNION ALL" +
            " SELECT 'block'   AS source, timestamp, player_name, server_name, CONCAT(event_type,' ',block_type,' at ',world,' ',x,',',y,',',z) AS detail FROM block_events WHERE timestamp >= NOW() - INTERVAL 24 HOUR" +
            " UNION ALL" +
            " SELECT 'death'   AS source, timestamp, player_name, server_name, death_message AS detail FROM player_deaths WHERE timestamp >= NOW() - INTERVAL 24 HOUR" +
            " ORDER BY timestamp DESC"
        };

        try (Connection con = dataSource.getConnection();
             Statement st = con.createStatement()) {
            for (String sql : ddl) {
                st.execute(sql);
            }
            // Widen mc_version in case the table was created with the old VARCHAR(20)
            st.execute("ALTER TABLE servers MODIFY COLUMN mc_version VARCHAR(100)");
            // Add country column to existing tables
            st.execute("ALTER TABLE player_sessions ADD COLUMN IF NOT EXISTS country VARCHAR(100) AFTER ip_address");
        }
        plugin.getLogger().info("[MCLogger] Database tables verified/created.");
    }

    public void disconnect() {
        if (dataSource != null && !dataSource.isClosed()) {
            dataSource.close();
        }
    }

    private void registerServer(FileConfiguration cfg) throws SQLException {
        String version = plugin.getServer().getMinecraftVersion();
        try (Connection con = dataSource.getConnection();
             PreparedStatement ps = con.prepareStatement(
                     "INSERT INTO servers (server_name, server_type, ip_address, mc_version) VALUES (?,?,?,?) " +
                     "ON DUPLICATE KEY UPDATE last_seen = CURRENT_TIMESTAMP(3), mc_version = VALUES(mc_version)",
                     Statement.RETURN_GENERATED_KEYS)) {
            ps.setString(1, serverName);
            ps.setString(2, "paper");
            ps.setString(3, cfg.getString("database.host", "localhost"));
            ps.setString(4, version);
            ps.executeUpdate();
            try (ResultSet rs = ps.getGeneratedKeys()) {
                if (rs.next()) serverId = rs.getInt(1);
            }
        }
        if (serverId == -1) {
            try (Connection con = dataSource.getConnection();
                 PreparedStatement ps = con.prepareStatement("SELECT id FROM servers WHERE server_name = ?")) {
                ps.setString(1, serverName);
                try (ResultSet rs = ps.executeQuery()) {
                    if (rs.next()) serverId = rs.getInt(1);
                }
            }
        }
    }

    public Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }

    // --------------------------------------------------------
    // Async insert helper
    // --------------------------------------------------------

    private void asyncExec(ThrowingRunnable r) {
        CompletableFuture.runAsync(() -> {
            try {
                r.run();
            } catch (Exception e) {
                plugin.getLogger().log(Level.WARNING, "[MCLogger] DB write error: " + e.getMessage());
            }
        });
    }

    @FunctionalInterface
    interface ThrowingRunnable {
        void run() throws Exception;
    }

    // --------------------------------------------------------
    // Players
    // --------------------------------------------------------

    public void upsertPlayer(String uuid, String username, String displayName,
                              String ip, String locale, boolean isOp) {
        asyncExec(() -> {
            try (Connection con = getConnection();
                 PreparedStatement ps = con.prepareStatement(
                         "INSERT INTO players (uuid, username, display_name, ip_address, locale, is_op) VALUES (?,?,?,?,?,?) " +
                         "ON DUPLICATE KEY UPDATE username=VALUES(username), display_name=VALUES(display_name), " +
                         "ip_address=VALUES(ip_address), locale=VALUES(locale), is_op=VALUES(is_op), " +
                         "last_seen=CURRENT_TIMESTAMP(3)")) {
                ps.setString(1, uuid);
                ps.setString(2, username);
                ps.setString(3, displayName);
                ps.setString(4, ip);
                ps.setString(5, locale);
                ps.setBoolean(6, isOp);
                ps.executeUpdate();
            }
        });
    }

    // --------------------------------------------------------
    // Sessions
    // --------------------------------------------------------

    public void insertSessionLogin(String uuid, String name, String ip, String clientVersion) {
        asyncExec(() -> {
            String country = lookupCountry(ip);
            try (Connection con = getConnection();
                 PreparedStatement ps = con.prepareStatement(
                         "INSERT INTO player_sessions (player_uuid, player_name, server_name, ip_address, country, client_version) VALUES (?,?,?,?,?,?)")) {
                ps.setString(1, uuid);
                ps.setString(2, name);
                ps.setString(3, serverName);
                ps.setString(4, ip);
                ps.setString(5, country);
                ps.setString(6, clientVersion);
                ps.executeUpdate();
            }
        });
    }

    /** Looks up the player's country via ip-api.com (free, no API key). Returns "Local" for LAN/private IPs. */
    private String lookupCountry(String ip) {
        if (ip == null || ip.equals("unknown") || ip.equals("::1")
                || ip.startsWith("127.") || ip.startsWith("10.")
                || ip.startsWith("192.168.") || ip.startsWith("172.")) {
            return "Local";
        }
        try {
            java.net.URL url = new java.net.URL("http://ip-api.com/json/" + ip + "?fields=country");
            java.net.HttpURLConnection conn = (java.net.HttpURLConnection) url.openConnection();
            conn.setConnectTimeout(3000);
            conn.setReadTimeout(3000);
            conn.setRequestMethod("GET");
            try (java.io.BufferedReader reader = new java.io.BufferedReader(
                    new java.io.InputStreamReader(conn.getInputStream()))) {
                StringBuilder sb = new StringBuilder();
                String line;
                while ((line = reader.readLine()) != null) sb.append(line);
                JsonObject obj = GSON.fromJson(sb.toString(), JsonObject.class);
                if (obj != null && obj.has("country")) return obj.get("country").getAsString();
            }
        } catch (Exception ignored) {}
        return "Unknown";
    }

    public void closeSession(String uuid, long durationSec) {
        asyncExec(() -> {
            try (Connection con = getConnection();
                 PreparedStatement ps = con.prepareStatement(
                         "UPDATE player_sessions SET logout_time=CURRENT_TIMESTAMP(3), duration_sec=? " +
                         "WHERE player_uuid=? AND server_name=? AND logout_time IS NULL ORDER BY login_time DESC LIMIT 1")) {
                ps.setLong(1, durationSec);
                ps.setString(2, uuid);
                ps.setString(3, serverName);
                ps.executeUpdate();
            }
            try (Connection con = getConnection();
                 PreparedStatement ps = con.prepareStatement(
                         "UPDATE players SET total_playtime_sec = total_playtime_sec + ? WHERE uuid = ?")) {
                ps.setLong(1, durationSec);
                ps.setString(2, uuid);
                ps.executeUpdate();
            }
        });
    }

    // --------------------------------------------------------
    // Chat
    // --------------------------------------------------------

    public void insertChat(String uuid, String name, String world, String message, String channel) {
        asyncExec(() -> {
            try (Connection con = getConnection();
                 PreparedStatement ps = con.prepareStatement(
                         "INSERT INTO player_chat (player_uuid, player_name, server_name, world, message, channel) VALUES (?,?,?,?,?,?)")) {
                ps.setString(1, uuid);
                ps.setString(2, name);
                ps.setString(3, serverName);
                ps.setString(4, world);
                ps.setString(5, message);
                ps.setString(6, channel);
                ps.executeUpdate();
            }
        });
    }

    // --------------------------------------------------------
    // Commands
    // --------------------------------------------------------

    public void insertCommand(String uuid, String name, String world,
                               String command, double x, double y, double z) {
        asyncExec(() -> {
            try (Connection con = getConnection();
                 PreparedStatement ps = con.prepareStatement(
                         "INSERT INTO player_commands (player_uuid, player_name, server_name, world, command, x, y, z) VALUES (?,?,?,?,?,?,?,?)")) {
                ps.setString(1, uuid);
                ps.setString(2, name);
                ps.setString(3, serverName);
                ps.setString(4, world);
                ps.setString(5, command);
                ps.setDouble(6, x);
                ps.setDouble(7, y);
                ps.setDouble(8, z);
                ps.executeUpdate();
            }
        });
    }

    // --------------------------------------------------------
    // Block Events
    // --------------------------------------------------------

    public void insertBlockEvent(String type, String uuid, String name,
                                  String world, int x, int y, int z,
                                  String blockType, String blockData, String tool, boolean silkTouch) {
        asyncExec(() -> {
            try (Connection con = getConnection();
                 PreparedStatement ps = con.prepareStatement(
                         "INSERT INTO block_events (event_type, player_uuid, player_name, server_name, world, x, y, z, block_type, block_data, tool, is_silk) " +
                         "VALUES (?,?,?,?,?,?,?,?,?,?,?,?)")) {
                ps.setString(1, type);
                ps.setString(2, uuid);
                ps.setString(3, name);
                ps.setString(4, serverName);
                ps.setString(5, world);
                ps.setInt(6, x);
                ps.setInt(7, y);
                ps.setInt(8, z);
                ps.setString(9, blockType);
                ps.setString(10, blockData);
                ps.setString(11, tool);
                ps.setBoolean(12, silkTouch);
                ps.executeUpdate();
            }
        });
    }

    // --------------------------------------------------------
    // Deaths
    // --------------------------------------------------------

    public void insertDeath(String uuid, String name, String world, double x, double y, double z,
                             String msg, String cause, String killerUuid, String killerName,
                             String killerType, int expLevel, Map<String, Object> itemsLost) {
        asyncExec(() -> {
            try (Connection con = getConnection();
                 PreparedStatement ps = con.prepareStatement(
                         "INSERT INTO player_deaths (player_uuid, player_name, server_name, world, x, y, z, " +
                         "death_message, cause, killer_uuid, killer_name, killer_type, exp_level, items_lost) " +
                         "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)")) {
                ps.setString(1, uuid);
                ps.setString(2, name);
                ps.setString(3, serverName);
                ps.setString(4, world);
                ps.setDouble(5, x);
                ps.setDouble(6, y);
                ps.setDouble(7, z);
                ps.setString(8, msg);
                ps.setString(9, cause);
                ps.setString(10, killerUuid);
                ps.setString(11, killerName);
                ps.setString(12, killerType);
                ps.setInt(13, expLevel);
                ps.setString(14, itemsLost != null ? GSON.toJson(itemsLost) : null);
                ps.executeUpdate();
            }
        });
    }

    // --------------------------------------------------------
    // Entity Events
    // --------------------------------------------------------

    public void insertEntityEvent(String type, String world, double x, double y, double z,
                                   String entityType, String entityUuid, String entityName,
                                   String playerUuid, String playerName, String cause,
                                   double damage, Map<String, Object> details) {
        asyncExec(() -> {
            try (Connection con = getConnection();
                 PreparedStatement ps = con.prepareStatement(
                         "INSERT INTO entity_events (event_type, server_name, world, x, y, z, entity_type, entity_uuid, entity_name, player_uuid, player_name, cause, damage, details) " +
                         "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)")) {
                ps.setString(1, type);
                ps.setString(2, serverName);
                ps.setString(3, world);
                ps.setDouble(4, x);
                ps.setDouble(5, y);
                ps.setDouble(6, z);
                ps.setString(7, entityType);
                ps.setString(8, entityUuid);
                ps.setString(9, entityName);
                ps.setString(10, playerUuid);
                ps.setString(11, playerName);
                ps.setString(12, cause);
                ps.setDouble(13, damage);
                ps.setString(14, details != null ? GSON.toJson(details) : null);
                ps.executeUpdate();
            }
        });
    }

    // --------------------------------------------------------
    // Teleports
    // --------------------------------------------------------

    public void insertTeleport(String uuid, String name,
                                String fromWorld, double fx, double fy, double fz,
                                String toWorld, double tx, double ty, double tz,
                                String cause) {
        asyncExec(() -> {
            try (Connection con = getConnection();
                 PreparedStatement ps = con.prepareStatement(
                         "INSERT INTO player_teleports (player_uuid, player_name, server_name, from_world, from_x, from_y, from_z, to_world, to_x, to_y, to_z, cause) " +
                         "VALUES (?,?,?,?,?,?,?,?,?,?,?,?)")) {
                ps.setString(1, uuid);
                ps.setString(2, name);
                ps.setString(3, serverName);
                ps.setString(4, fromWorld);
                ps.setDouble(5, fx);
                ps.setDouble(6, fy);
                ps.setDouble(7, fz);
                ps.setString(8, toWorld);
                ps.setDouble(9, tx);
                ps.setDouble(10, ty);
                ps.setDouble(11, tz);
                ps.setString(12, cause);
                ps.executeUpdate();
            }
        });
    }

    // --------------------------------------------------------
    // Inventory Events
    // --------------------------------------------------------

    public void insertInventoryEvent(String type, String uuid, String name,
                                      String world, double x, double y, double z,
                                      String item, int amount, Map<String, Object> meta,
                                      int slot, String invType) {
        asyncExec(() -> {
            try (Connection con = getConnection();
                 PreparedStatement ps = con.prepareStatement(
                         "INSERT INTO inventory_events (event_type, player_uuid, player_name, server_name, world, x, y, z, item_type, item_amount, item_meta, slot, inventory_type) " +
                         "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)")) {
                ps.setString(1, type);
                ps.setString(2, uuid);
                ps.setString(3, name);
                ps.setString(4, serverName);
                ps.setString(5, world);
                ps.setDouble(6, x);
                ps.setDouble(7, y);
                ps.setDouble(8, z);
                ps.setString(9, item);
                ps.setInt(10, amount);
                ps.setString(11, meta != null ? GSON.toJson(meta) : null);
                ps.setInt(12, slot);
                ps.setString(13, invType);
                ps.executeUpdate();
            }
        });
    }

    // --------------------------------------------------------
    // Player Stats (Gamemode, Level, etc.)
    // --------------------------------------------------------

    public void insertPlayerStat(String type, String uuid, String name,
                                  String oldVal, String newVal, Map<String, Object> details) {
        asyncExec(() -> {
            try (Connection con = getConnection();
                 PreparedStatement ps = con.prepareStatement(
                         "INSERT INTO player_stats (event_type, player_uuid, player_name, server_name, old_value, new_value, details) VALUES (?,?,?,?,?,?,?)")) {
                ps.setString(1, type);
                ps.setString(2, uuid);
                ps.setString(3, name);
                ps.setString(4, serverName);
                ps.setString(5, oldVal);
                ps.setString(6, newVal);
                ps.setString(7, details != null ? GSON.toJson(details) : null);
                ps.executeUpdate();
            }
        });
    }

    // --------------------------------------------------------
    // World Events
    // --------------------------------------------------------

    public void insertWorldEvent(String type, String world, Double x, Double y, Double z, Map<String, Object> details) {
        asyncExec(() -> {
            try (Connection con = getConnection();
                 PreparedStatement ps = con.prepareStatement(
                         "INSERT INTO world_events (event_type, server_name, world, x, y, z, details) VALUES (?,?,?,?,?,?,?)")) {
                ps.setString(1, type);
                ps.setString(2, serverName);
                ps.setString(3, world);
                if (x != null) ps.setDouble(4, x); else ps.setNull(4, Types.DOUBLE);
                if (y != null) ps.setDouble(5, y); else ps.setNull(5, Types.DOUBLE);
                if (z != null) ps.setDouble(6, z); else ps.setNull(6, Types.DOUBLE);
                ps.setString(7, details != null ? GSON.toJson(details) : null);
                ps.executeUpdate();
            }
        });
    }

    // --------------------------------------------------------
    // Server Events
    // --------------------------------------------------------

    public void insertServerEvent(String type, String message, Map<String, Object> details) {
        asyncExec(() -> {
            try (Connection con = getConnection();
                 PreparedStatement ps = con.prepareStatement(
                         "INSERT INTO server_events (event_type, server_name, message, details) VALUES (?,?,?,?)")) {
                ps.setString(1, type);
                ps.setString(2, serverName);
                ps.setString(3, message);
                ps.setString(4, details != null ? GSON.toJson(details) : null);
                ps.executeUpdate();
            }
        });
    }

    // --------------------------------------------------------
    // Sign Edits
    // --------------------------------------------------------

    public void insertSignEdit(String uuid, String name, String world,
                                int x, int y, int z,
                                String l1, String l2, String l3, String l4) {
        asyncExec(() -> {
            try (Connection con = getConnection();
                 PreparedStatement ps = con.prepareStatement(
                         "INSERT INTO sign_edits (player_uuid, player_name, server_name, world, x, y, z, line1, line2, line3, line4) VALUES (?,?,?,?,?,?,?,?,?,?,?)")) {
                ps.setString(1, uuid);
                ps.setString(2, name);
                ps.setString(3, serverName);
                ps.setString(4, world);
                ps.setInt(5, x);
                ps.setInt(6, y);
                ps.setInt(7, z);
                ps.setString(8, l1);
                ps.setString(9, l2);
                ps.setString(10, l3);
                ps.setString(11, l4);
                ps.executeUpdate();
            }
        });
    }

    // --------------------------------------------------------
    // Plugin Events (LuckPerms etc.)
    // --------------------------------------------------------

    /**
     * Writes a generic plugin event (e.g. LuckPerms permission change).
     *
     * @param eventType   Type of event (e.g. "luckperms_grant", "luckperms_revoke")
     * @param pluginName  Name of the triggering plugin (e.g. "LuckPerms")
     * @param playerUuid  UUID of the affected player (may be null)
     * @param playerName  Name of the affected player
     * @param actorUuid   UUID of the actor (console UUID = 00000000-...)
     * @param actorName   Display name of the actor
     * @param targetType  "user" or "group"
     * @param targetId    UUID or group name
     * @param action      Description of the action (e.g. "permission.node set to true")
     * @param details     Additional details as Map
     */
    public void insertPluginEvent(String eventType, String pluginName,
                                  String playerUuid, String playerName,
                                  String actorUuid, String actorName,
                                  String targetType, String targetId,
                                  String action, Map<String, Object> details) {
        asyncExec(() -> {
            try (Connection con = getConnection();
                 PreparedStatement ps = con.prepareStatement(
                         "INSERT INTO plugin_events " +
                         "(event_type, plugin_name, server_name, player_uuid, player_name, " +
                         " actor_uuid, actor_name, target_type, target_id, action, details) " +
                         "VALUES (?,?,?,?,?,?,?,?,?,?,?)")) {
                ps.setString(1, eventType);
                ps.setString(2, pluginName);
                ps.setString(3, serverName);
                if (playerUuid != null) ps.setString(4, playerUuid); else ps.setNull(4, Types.VARCHAR);
                if (playerName != null) ps.setString(5, playerName); else ps.setNull(5, Types.VARCHAR);
                if (actorUuid != null)  ps.setString(6, actorUuid);  else ps.setNull(6, Types.VARCHAR);
                if (actorName != null)  ps.setString(7, actorName);  else ps.setNull(7, Types.VARCHAR);
                if (targetType != null) ps.setString(8, targetType); else ps.setNull(8, Types.VARCHAR);
                if (targetId != null)   ps.setString(9, targetId);   else ps.setNull(9, Types.VARCHAR);
                if (action != null)     ps.setString(10, action);    else ps.setNull(10, Types.VARCHAR);
                ps.setString(11, details != null ? GSON.toJson(details) : null);
                ps.executeUpdate();
            }
        });
    }

    // --------------------------------------------------------
    // Getter
    // --------------------------------------------------------

    public String getServerName() { return serverName; }
    public int getServerId() { return serverId; }
    public boolean isConnected() { return dataSource != null && !dataSource.isClosed(); }
}
