package de.simolzimol.mclogger.velocity.database;

import com.google.gson.Gson;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import de.simolzimol.mclogger.velocity.VelocityLoggerPlugin;
import org.spongepowered.configurate.ConfigurationNode;

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

/**
 * MariaDB manager for the Velocity plugin.
 * Writes proxy events asynchronously to the database.
 *
 * @author SimolZimol
 */
public class VelocityDatabaseManager {

    private final VelocityLoggerPlugin plugin;
    private HikariDataSource dataSource;
    private String proxyName;
    private static final Gson GSON = new Gson();

    public VelocityDatabaseManager(VelocityLoggerPlugin plugin) {
        this.plugin = plugin;
    }

    // --------------------------------------------------------
    // Connection
    // --------------------------------------------------------

    public boolean connect(ConfigurationNode cfg) {
        proxyName = cfg.node("proxy", "name").getString("proxy-01");

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

        try {
            dataSource = new HikariDataSource(hk);
            registerProxy(cfg);
            plugin.getLogger().info("MCLogger-Velocity: Database connected - Proxy: " + proxyName);
            return true;
        } catch (Exception e) {
            plugin.getLogger().error("MCLogger-Velocity: Database connection failed!", e);
            return false;
        }
    }

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

    private void registerProxy(ConfigurationNode cfg) throws SQLException {
        try (Connection con = dataSource.getConnection();
             PreparedStatement ps = con.prepareStatement(
                     "INSERT INTO servers (server_name, server_type) VALUES (?,?) " +
                     "ON DUPLICATE KEY UPDATE last_seen = CURRENT_TIMESTAMP(3)")) {
            ps.setString(1, proxyName);
            ps.setString(2, "velocity");
            ps.executeUpdate();
        }
    }

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

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

    private void asyncExec(ThrowingRunnable r) {
        CompletableFuture.runAsync(() -> {
            try {
                r.run();
            } catch (Exception e) {
                plugin.getLogger().warn("MCLogger-Velocity: DB error: " + e.getMessage());
            }
        });
    }

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

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

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

    // --------------------------------------------------------
    // Proxy Events (central table)
    // --------------------------------------------------------

    public void insertProxyEvent(String type, String playerUuid, String playerName,
                                  String fromServer, String toServer,
                                  String ip, Map<String, Object> details) {
        asyncExec(() -> {
            try (Connection con = getConnection();
                 PreparedStatement ps = con.prepareStatement(
                         "INSERT INTO proxy_events (event_type, player_uuid, player_name, proxy_name, from_server, to_server, ip_address, details) " +
                         "VALUES (?,?,?,?,?,?,?,?)")) {
                ps.setString(1, type);
                ps.setString(2, playerUuid);
                ps.setString(3, playerName);
                ps.setString(4, proxyName);
                ps.setString(5, fromServer);
                ps.setString(6, toServer);
                ps.setString(7, ip);
                ps.setString(8, details != null ? GSON.toJson(details) : null);
                ps.executeUpdate();
            }
        });
    }

    // --------------------------------------------------------
    // Chat & Commands (shared tables)
    // --------------------------------------------------------

    public void insertChat(String uuid, String name, String message) {
        insertChatWithServer(uuid, name, proxyName, message);
    }

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

    public void insertCommand(String uuid, String name, String command) {
        insertCommandWithServer(uuid, name, proxyName, command);
    }

    public void insertCommandWithServer(String uuid, String name, String serverName, String command) {
        asyncExec(() -> {
            try (Connection con = getConnection();
                 PreparedStatement ps = con.prepareStatement(
                         "INSERT INTO player_commands (player_uuid, player_name, server_name, command) VALUES (?,?,?,?)")) {
                ps.setString(1, uuid);
                ps.setString(2, name);
                ps.setString(3, serverName);
                ps.setString(4, command);
                ps.executeUpdate();
            }
        });
    }

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

    /** Opens a new session row. server_name is updated on disconnect to the last known backend. */
    public void insertSessionLogin(String uuid, String name, String ip, String clientBrand) {
        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, proxyName);
                ps.setString(4, ip);
                ps.setString(5, country);
                ps.setString(6, clientBrand);
                ps.executeUpdate();
            }
        });
    }

    /** Closes the open session, sets the actual backend server name and duration. */
    public void closeSession(String uuid, String lastServer, long durationSec) {
        asyncExec(() -> {
            try (Connection con = getConnection();
                 PreparedStatement ps = con.prepareStatement(
                         "UPDATE player_sessions SET logout_time=CURRENT_TIMESTAMP(3), duration_sec=?, server_name=? " +
                         "WHERE player_uuid=? AND logout_time IS NULL ORDER BY login_time DESC LIMIT 1")) {
                ps.setLong(1, durationSec);
                ps.setString(2, lastServer);
                ps.setString(3, uuid);
                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();
            }
        });
    }

    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);
                com.google.gson.JsonObject obj = new com.google.gson.Gson().fromJson(sb.toString(), com.google.gson.JsonObject.class);
                if (obj != null && obj.has("country")) return obj.get("country").getAsString();
            }
        } catch (Exception ignored) {}
        return "Unknown";
    }

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

    public String getProxyName() { return proxyName; }
    public boolean isConnected() { return dataSource != null && !dataSource.isClosed(); }
}
