package me.xcraftrayx.pl;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.Writer;
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.sql.Timestamp;
import java.util.LinkedList;
import java.util.List;
import java.util.Scanner;
import java.util.StringTokenizer;

public class MySQL {

    private static Connection conn;
    private static Statement stat;
    private static String password, password2, host, host2, user, user2, database, database2, port, port2;

    public static final String DRIVER = "com.mysql.jdbc.Driver";

    public static void start()
    {
        File katalog = new File("plugins/OnlineTimeInServers/");
        if(!katalog.exists())
        {
            katalog.mkdirs();
        }
        File config = new File("plugins/OnlineTimeInServers/config.yml");
        if(!config.exists())
        {
            try
            {
                config.createNewFile();
                Writer writer = new BufferedWriter(new FileWriter(config));
                ((BufferedWriter)writer).write("config:");      ((BufferedWriter)writer).newLine();
                ((BufferedWriter)writer).write("  MySQL:");     ((BufferedWriter)writer).newLine();
                ((BufferedWriter)writer).write("    HOST: localhost");  ((BufferedWriter)writer).newLine();
                ((BufferedWriter)writer).write("    USER: root");   ((BufferedWriter)writer).newLine();
                ((BufferedWriter)writer).write("    PASS: password");   ((BufferedWriter)writer).newLine();
                ((BufferedWriter)writer).write("    DATABASE: MineS");  ((BufferedWriter)writer).newLine();
                ((BufferedWriter)writer).write("    PORT: 3306");   ((BufferedWriter)writer).newLine();
                ((BufferedWriter)writer).close();
            }
            catch (IOException e)
            {
                e.printStackTrace();
            }
        }
        StringTokenizer token;
        try
        {
        @SuppressWarnings("resource")
        Scanner odczyt = new Scanner(config);
        while(odczyt.hasNextLine()){
            token = new StringTokenizer(odczyt.nextLine(),":");
            while(token.hasMoreElements()){
                String st = String.valueOf(token.nextToken());
                if(st.equals("    PASS")){
                    password = token.nextToken();
                    password2 = password.replace(" ", "");
                }
                if(st.equals("    HOST")){
                    host = token.nextToken();
                    host2 = host.replace(" ", "");
                }
                if(st.equals("    USER")){
                    user = token.nextToken();
                    user2 = user.replace(" ", "");
                }
                if(st.equals("    DATABASE")){
                    database = token.nextToken();
                    database2 = database.replace(" ", "");
                }
                if(st.equals("    PORT")){
                    port = token.nextToken();
                    port2 = port.replace(" ", "");
                }
                }
            }
        }

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

        try
        {
            Class.forName(MySQL.DRIVER);
        }
        catch (ClassNotFoundException e1)
        {
            System.err.println("Brak sterownika JDBC");
            e1.printStackTrace();
        }

        try
        {
            conn = DriverManager.getConnection("jdbc:mysql://" + host2 + ":" + port2 + "/" + database2 + "?autoReconnect=true" + "&user=" + user2 + "&password=" + password2);
            stat = conn.createStatement();
        } catch (SQLException e)
        {
            System.err.println("Problem z otwarciem polaczenia");
            e.printStackTrace();
        }
        MySQL.createTables();
    }

      public static boolean createTables()
      {
          String createTable = "CREATE TABLE IF NOT EXISTS BungeeAllServers (id INTEGER AUTO_INCREMENT, Servers varchar(11), PRIMARY KEY(id))";
          String createTable2 = "CREATE TABLE IF NOT EXISTS BungeeTimeInServer (id INTEGER AUTO_INCREMENT, NickID int(11), ServerID int(11), Time int(11), PRIMARY KEY(id))";
      try
      {
          stat.execute(createTable);
          stat.execute(createTable2);
      } catch (SQLException e)
      {
          System.err.println("Blad przy tworzeniu tabeli");
          e.printStackTrace();
          return false;
      }
        return true;
      }

      public static boolean addUser(String username, String server, int time)
      {
            try {
                PreparedStatement prepStmt = conn.prepareStatement("INSERT INTO BungeeTimeInServer (NickID, ServerID, Time) VALUES ((SELECT ID FROM LobbyInventory WHERE username = '" + username + "'), (SELECT ID FROM BungeeAllServers WHERE Servers = '" + server + "'), " + time + ");");
                prepStmt.execute();
            } catch (SQLException e) {
                System.err.println("Blad przy wstawianiu uzytkownika");
                e.printStackTrace();
                return false;
            }
            return true;
      }

        public static boolean updateUser(String username, String server, int time) {
            try {
                PreparedStatement prepStmt = conn.prepareStatement("UPDATE BungeeTimeInServer SET (NickID, ServerID, Time) VALUES ((SELECT ID FROM LobbyInventory WHERE username = '" + username + "'), (SELECT ID FROM BungeeAllServers WHERE Servers = '" + server + "'), "+ time +");");
                prepStmt.execute();
            } catch (SQLException e) {
                System.err.println("Blad przy aktualizowaniu uzytkownika");
                e.printStackTrace();
                return false;
            }
            return true;
        }

          public static List<String> selectUser(String username, String server) {
                List<String> users = new LinkedList<String>();
                    try {
                        ResultSet result = stat.executeQuery("SELECT NickID, ServerID FROM BungeeTimeInServer inner join LobbyInventory on BungeeTimeInServer.NickID = LobbyInventory.ID join BungeeAllServers on BungeeTimeInServer.ServerID = BungeeAllServers.id WHERE BungeeAllServers.Servers = '" + server + "' AND LobbyInventory.username = '" + username + "';");
                        while(result.next()) {
                            username = result.getString("NickID");
                            server = result.getString("ServerID");
                            users.add(username);
                            users.add(server);
                        }
                    } catch (SQLException e) {
                        e.printStackTrace();
                        return null;
                    }
                    return users;
          }

      public static void closeConnection()
      {
          try
          {
              conn.close();
          } catch (SQLException e)
          {
              System.err.println("Problem z zamknieciem polaczenia");
              e.printStackTrace();
          }
      }
}