package ch.lexx.sqlManager;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.bukkit.Bukkit;

import ch.lexx.untils.Data;

public class sqlConnection {

	public void MainListener(ch.lexx.main.Main Main) {
		this.pl = Main;
	}
	public static ch.lexx.main.Main pl;
	final static String username=Data.SQLUser; //Enter in your db username
	final static String password=Data.SQLPW; //Enter your password for the db
	final static String url = Data.SQLurl; //Enter URL w/db name

	public static Connection connection;
	static int sqlConSch;
	public static boolean sqlConnect() {
		Bukkit.getConsoleSender().sendMessage("§6Starten Connection zur MySQL....");
		try {
		    Class.forName("com.mysql.jdbc.Driver"); //this accesses Driver in jdbc.
		} catch (ClassNotFoundException e) {
		    e.printStackTrace();
		    System.err.println("§4jdbc driver unavailable!");

		    Bukkit.getConsoleSender().sendMessage("§4jdbc driver unavailable!");
		    return false;
		}
		try { //Another try catch to get any SQL errors (for example connections errors)
			connection = DriverManager.getConnection(url,username,password);
			Bukkit.getConsoleSender().sendMessage("§9SQL Connection §aOK");
		    return true;
		    //with the method getConnection() from DriverManager, we're trying to set
		    //the connection's url, username, password to the variables we made earlier and
		    //trying to get a connection at the same time. JDBC allows us to do this.
		} catch (SQLException e) { //catching errors)
		    e.printStackTrace(); //prints out SQLException errors to the console (if any)
		    Bukkit.getConsoleSender().sendMessage("§4"+username);
		    Bukkit.getConsoleSender().sendMessage("§4"+password);
		    Bukkit.getConsoleSender().sendMessage("§4"+url);
		    return false;
		}
	}

	public static boolean sqlCloseConnection() {
		try { //using a try catch to catch connection errors (like wrong sql password...)
	        if (connection!=null && !connection.isClosed()){ //checking if connection isn't null to
	            //avoid receiving a nullpointer
	            connection.close(); //closing the connection field variable.
	            return true;
	        }
	        return true;
	    } catch(Exception e) {
	        e.printStackTrace();
	        return false;
	    }
	}
	public static boolean checkSqlTables() {
		String TableUsers = "Nope";
		String TableUsersStats = "Nope";
		Bukkit.getConsoleSender().sendMessage("§6Checking SQL Tables....");
		String sql = "CREATE TABLE IF NOT EXISTS Users("
				+ "UID integer not null auto_increment primary key,"
				+ "MUser varchar(50),"
				+ "UserPassword varchar(255),"
				+ "Kills Integer default 0,"
				+ "Deads Integer default 0,"
				+ "Coins double default 200,"
				+ "OnlineStats varchar(30) default 'NEW',"
				+ "Wertung integer default 0,"
				+ "LastOnlineDate date,"
				+ "LastOnlineTime time,"
				+ "PlayTime time default 0,"
				+ "LastIp varchar(15)"
				+ ");";
		// prepare the statement to be executed
		try {
		    PreparedStatement stmt = connection.prepareStatement(sql);
		    // I use executeUpdate() to update the databases table.
		    stmt.executeUpdate();
		    TableUsers = "OK";
		    Bukkit.getConsoleSender().sendMessage("§6Table §bUsers §aOK");
		} catch (SQLException e) {
		    e.printStackTrace();
		    Bukkit.getConsoleSender().sendMessage("§6Table §bUsers §4Failed");
		    return false;
		}
		sql = "CREATE TABLE IF NOT EXISTS UserStats(" +
				"	BID integer not null auto_increment primary key," +
				"    UID integer not null," +
				" 	 Status varchar(20)," +
				"    Reason text," +
				"    Datum_Bis date," +
				"    Zeit_Bis time," +
				"    Foreign key (UID) references Users(UID)" +
				");";
		// prepare the statement to be executed
		try {
		    PreparedStatement stmt = connection.prepareStatement(sql);
		    // I use executeUpdate() to update the databases table.
		    stmt.executeUpdate();
		    TableUsersStats = "OK";
		    Bukkit.getConsoleSender().sendMessage("§6Table §bUsersStats §aOK");
		} catch (SQLException e) {
		    e.printStackTrace();
		    Bukkit.getConsoleSender().sendMessage("§6Table §bUsersStats §4Failed");
		    return false;
		}
		if(TableUsers == "OK" && TableUsersStats == "OK") {
			return true;
		}else {
			return false;
		}
	}
}