package net.foxgenesis.sql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.function.Consumer;

import org.bukkit.Bukkit;

public class DataBaseAPI
{
	private String host;
	private String password;
	private String db;
	private String user;
	private int port;
	private Connection con;

	/**
	 * create a new connection to a database
	 * @param host SQL host
	 * @param user SQL username
	 * @param password SQL password
	 * @param database SQL database
	 * @param port SQl port
	 */
	public DataBaseAPI(String host, String user, String password, String database, int port) {
		this.host = host;
		this.user = user;
		this.password = password;
		this.db = database;
		this.port = port;
	}

	/**
	 * init the database
	 */
	public void connect() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			if (!checkLogin())
				System.err.println("Database-Login isn't valid!");
			else {
				createConnection("jdbc:mysql://" + host + ":" + port + "/" + db);
				System.out.println("MySQL-Connection - OK");
			}
		} catch (Exception ex) {
			System.err.println("Failed to connect to SQL server: " + ex.getMessage());
		}
	}

	/**
	 * check the login of the database
	 * @return boolean
	 */
	private boolean checkLogin() {
		try{
			if(!hasDatabase(db))
				createDatabase(db);
		} catch(Exception e) {
			System.err.println(e.getMessage());
			return false;
		}
		try {
			String url = "jdbc:mysql://" + host + ":" + port + "/" + db;
			Connection con = DriverManager.getConnection(url, user, password);
			con.createStatement();
			con.close();
		} catch (Exception ex) {
			ex.printStackTrace();
			return false;
		}
		return true;
	}

	public void createDatabase(String name) {
		try(Connection con = DriverManager.getConnection("jdbc:mysql://" + host + ":" + port, user, password)) {
			Statement t = con.createStatement();
			t.execute("CREATE DATABASE " + db + ";");
			t.close();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

	/**
	 * Disconnects from current connection and checks if there
	 * is a database with a given name
	 * @param name - database name
	 * @return if the database was found
	 */
	public boolean hasDatabase(String name) {
		String url = "jdbc:mysql://" + host + ":" + port + "/" + db;
		try(Connection con = DriverManager.getConnection(url, user, password)){
			con.createStatement().close();
			return true;
		} catch (Exception ex) {
			if(ex.getMessage().contains("Unknown database"))
				return false;
			ex.printStackTrace();
			Bukkit.shutdown();
			return false;
		}
	}

	public boolean hasTable(String name) {
		if(con == null) {
			System.err.println("No connection to SQL server!");
			return false;
		}
		try(Statement d = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
			return getRowCount(d.executeQuery("SHOW TABLES LIKE '" + name + "';"),false) > 0;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	public void dropTable(String name) {
		if(con == null)
			System.err.println("No connection to SQL server!");
		try(Statement d = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
			d.execute("DROP TABLE " + name);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	private void createConnection(String url) {
		try {
			if(con != null)
				con.close();
			con = DriverManager.getConnection(url, user, password);
		} catch (Exception ex) {
			System.err.println("Failed to connect to SQL server: " + ex.getMessage());
		}
	}

	public void closeConnection() {
		try {
			if(isConnected())
				con.close();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

	public void excecuteQuery(String query, Consumer<ResultSet> result) {
		if(!isConnected()) {
			System.err.println("No connection to SQL server!");
			return;
		}
		try(Statement statement = con.createStatement()) {
			ResultSet r = statement.executeQuery(query);
			result.accept(r);
			r.close();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

	public ResultSet excecuteQuery(String query) {
		if(!isConnected()) {
			System.err.println("No connection to SQL server!");
			return null;
		}
		try {
			Statement statement = con.createStatement();
			return statement.executeQuery(query);
		} catch (Exception ex) {
			ex.printStackTrace();
			return null;
		}
	}

	public int excecuteUpdate(String query) {
		if(!isConnected()) {
			System.err.println("No connection to SQL server!");
			return -1;
		}
		int result = 0;
		try(Statement statement = con.createStatement()) {
			result = statement.executeUpdate(query);
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return result;
	}

	public void execute(String query) {
		if(!isConnected()) {
			System.err.println("No connection to SQL server!");
			return;
		}
		try(Statement statement = con.createStatement()) {
			statement.execute(query);
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

	public boolean isConnected() {
		return con != null;
	}

	private int getRowCount(ResultSet resultSet, boolean backToStart) {
		if (resultSet == null) {
			return 0;
		}
		try {
			resultSet.last();
			return resultSet.getRow();
		} catch (SQLException exp) {
			exp.printStackTrace();
		} finally {
			try {
				if(backToStart)
					resultSet.beforeFirst();
				else
					resultSet.close();
			} catch (SQLException exp) {
				exp.printStackTrace();
			}
		}
		return 0;
	}
}