package me.ikileey.pscash.databases;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import org.bukkit.Bukkit;
import org.bukkit.entity.Player;

import me.ikileey.pscash.Main;
import me.ikileey.pscash.messages.Mensagens;
import me.ikileey.pscash.utils.Cash;

public class SQLite {

    public static Connection connection;

    public static String sql = "org.sqlite.JDBC";

    public static void setConnection(){
        try {
            Bukkit.getConsoleSender().sendMessage("[PsCash] Conectando no SQLite..");
            Class.forName("org.sqlite.JDBC");
            connection = DriverManager.getConnection("jdbc:sqlite:" + Main.pl.getDataFolder().getAbsolutePath() + File.separator + "database.db");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        catch (Exception e2) {
            e2.printStackTrace();
        }
    }


    public static void startDatabase(){
        try {
            setConnection();
            final Statement stmt = connection.createStatement();
            stmt.execute("CREATE TABLE IF NOT EXISTS cash (player VARCHAR(25), valor INTEGER)");
            stmt.close();
            Bukkit.getConsoleSender().sendMessage("[PsCash] Conectado com sucesso!");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    public static void addCash(String p, Integer valor) {
        try {
            if (!containsCash(p)) {
                Class.forName(sql);
                final PreparedStatement pstmt2 = connection
                        .prepareStatement("INSERT INTO cash (player, valor) VALUES (?, ?)");
                pstmt2.setString(1, p);
                pstmt2.setInt(2, valor);
                pstmt2.execute();
            } else {
                Class.forName(sql);
                final PreparedStatement pstmt = connection
                        .prepareStatement("SELECT * FROM cash WHERE player = ?;");
                pstmt.setString(1, p);
                final ResultSet result = pstmt.executeQuery();
                Integer vm = 0;
                if (result.next()) {
                    vm = result.getInt("valor") + valor;
                }
                final PreparedStatement pstmt2 = connection
                        .prepareStatement("UPDATE cash SET valor = ? WHERE player = ?;");
                pstmt2.setInt(1, vm);
                pstmt2.setString(2, p);
                pstmt2.executeUpdate();

                result.close();
                pstmt.close();
                pstmt2.close();
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void setCash(String p, Integer valor) {
        try {
            if (!containsCash(p)) {
                Class.forName(sql);
                final PreparedStatement pstmt2 = connection
                        .prepareStatement("INSERT INTO cash (player, valor) VALUES (?, ?)");
                pstmt2.setString(1, p);
                pstmt2.setInt(2, valor);
                pstmt2.execute();
            } else {
                Class.forName(sql);
                final PreparedStatement pstmt = connection
                        .prepareStatement("SELECT * FROM cash WHERE player = ?;");
                pstmt.setString(1, p);

                final PreparedStatement pstmt2 = connection
                        .prepareStatement("UPDATE cash SET valor = ? WHERE player = ?;");
                pstmt2.setInt(1, valor);
                pstmt2.setString(2, p);
                pstmt2.executeUpdate();

                pstmt.close();
                pstmt2.close();
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void takeCash(String p, Integer valor) {
        try {
            Class.forName(sql);
            final PreparedStatement pstmt = connection
                    .prepareStatement("SELECT * FROM cash WHERE player = ?;");
            pstmt.setString(1, p);
            final ResultSet result = pstmt.executeQuery();
            Integer vm = 0;
            if (result.next()) {
                vm = result.getInt("valor") - valor;
            }
            final PreparedStatement pstmt2 = connection
                    .prepareStatement("UPDATE cash SET valor = ? WHERE player = ?;");
            pstmt2.setInt(1, vm);
            pstmt2.setString(2, p);
            pstmt2.executeUpdate();

            result.close();
            pstmt.close();
            pstmt2.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static boolean containsCash(String p) {
        try {
            Class.forName(sql);
            final PreparedStatement pstmt = connection
                    .prepareStatement("SELECT * FROM cash WHERE player=?");
            pstmt.setString(1, p);
            final ResultSet resultSet = pstmt.executeQuery();
            return resultSet.next() && resultSet.getString("player").equalsIgnoreCase(p);

        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }

    }

    public static Long getCash(String p) {
        try {
            Class.forName(sql);

            final PreparedStatement pstmt = connection
                    .prepareStatement("SELECT * FROM cash WHERE player = ?");
            pstmt.setString(1, p);
            final ResultSet result = pstmt.executeQuery();
            if (result.next()) {
                return result.getLong("valor");
            }

            result.close();
            pstmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return (long) 0;
    }

    public static void getTopCash(Player p) {
        p.sendMessage(Mensagens.getRank("top_msg"));
        p.sendMessage("");
        String msg = Mensagens.getRank("rank_top_posicao").replace("%o%", "º");
        try {
            Class.forName(sql);
            final ResultSet result = connection.createStatement()
                    .executeQuery("SELECT * FROM cash ORDER BY valor DESC LIMIT 5;");
            int i = 0;
            while (result.next()) {
                i++;
                p.sendMessage(msg.replace("@Posicao", new StringBuilder(String.valueOf(i)).toString()).replace("@Player", result.getString("player")).replace("@Cash", new StringBuilder(String.valueOf(result.getLong("valor"))).toString()).replace("@V", Cash.format(result.getLong("valor"))));

            }
            result.close();
            p.sendMessage("");
        } catch (Exception e) {
            p.sendMessage(Mensagens.getRank("no_rank"));
        }
    }

}