//sql stuff
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.jibble.pircbot.Colors;

public class sqlManager {

	private Connection conn = null;

	public sqlManager()	{
	}

    public boolean connectSQL() {
    	try {
    		Class.forName("com.mysql.jdbc.Driver").newInstance();
    		//conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/haxbot", "root", "Wearemysqlmen110");
    		conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/haxBot", "root", "");
    	} catch (Exception e) {
    		conn = null;
    		e.printStackTrace();

    	}
    	return false;
    }

    /* commands table functions */
    public ResultSet getCommand(String cmd, String channel) {
    	ResultSet r = null;

    	this.connectSQL();

    	try {
	    	Statement st = conn.createStatement();
	    	ResultSet res = st.executeQuery("SELECT id, command, meaning, owner, private, time, channel FROM commands WHERE command ='"+ cmd +"' AND channel = '"+ channel +"'");
	    	r = res;
    	} catch(SQLException e)
    	{
    		e.printStackTrace();
    	}

    	return r;
    }

    public String setCommand(String cmd, String channel, String meaning, String owner, int priv, String withPre)
    {
    	this.connectSQL();

    	String msg = "Adding the command failed.";
    	try {
    		// check if this command already exists for that channel
    		ResultSet oldCmd = this.getCommand(cmd, channel);
    		if(oldCmd.first())
    		{
    			if(oldCmd.getString("command").equalsIgnoreCase(cmd) && oldCmd.getString("channel").equalsIgnoreCase(channel))
    			{
    				msg = "Failed to add this command. Already added by user "+ Colors.BOLD + oldCmd.getString("owner");
    				return msg;
    			}
    		}
    		Statement st = conn.createStatement();
    		st.executeUpdate("INSERT INTO commands (command, meaning, owner, private, time, channel) VALUES ('"+ cmd +"', '"+ meaning +"', '"+ owner +"', '"+ priv +"','"+ (System.currentTimeMillis() / 1000L)  +"', '"+ channel +"')");
    		msg = "Successfully added "+ Colors.BOLD + withPre + Colors.NORMAL + " to database with meaning of "+ Colors.BOLD + meaning;


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

    	try {
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return msg;
    }

    public String removeCommand(String cmd, String channel, String owner, String withPre)
    {
    	String msg = "Deletion of the command failed";
    	this.connectSQL();
    	try {
    		String ownerOfCmd = "";

    		// get the owner of the command
    		ResultSet oldCmd = this.getCommand(cmd, channel);
    		if(oldCmd.first())
    		{
    			ownerOfCmd = oldCmd.getString("owner");
    		}

    		if(ownerOfCmd.equals(owner) || this.getPermission(owner) >= 7)
    		{
	    		Statement st = conn.createStatement();
	    		st.executeUpdate("DELETE FROM commands WHERE command = '"+ cmd +"' AND channel = '"+ channel +"' AND owner = '"+ owner +"'");
	    		msg = "Successfully deleted "+ Colors.BOLD + withPre + Colors.NORMAL +" from database";
    		} else {
    			msg = "You can only delete your own commands";
    		}

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

    	try {
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

    	return msg;
    }

    /* permission functions */
    public int getPermission(String user)
    {
    	this.connectSQL();
    	int level = 0;
    	try {
    		Statement st = conn.createStatement();
    		ResultSet r = st.executeQuery("SELECT level FROM permissions WHERE user = '"+ user +"'");

    		if(r.first())
    		{
    			level = r.getInt("level");
    		}

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

    	try {
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return level;
    }

    public String addPermission(String user, String adder, int level)
    {
    	this.connectSQL();
    	String msg = "Failed to add permission for user "+ Colors.BOLD + user;
    	try {
    		if(level <= 7 && level >= 1 || this.getPermission(adder) == 10 )
    		{
    			if(this.getPermission(user) > 0)
    			{
    				msg = "This user already has permission set. Please remove first.";
    				return msg;
    			}
	    		Statement st = conn.createStatement();
	    		st.executeUpdate("INSERT INTO permissions (user, level, adder, time) VALUES ('"+ user +"', '"+ level +"', '"+ adder +"', '"+ (System.currentTimeMillis() / 1000L) +"')");
	    		msg = "Successfully added permission level "+ Colors.BOLD + level + Colors.NORMAL +" for user "+ Colors.BOLD + user;
    		} else {
    			msg = "You can only add permissions up to level 7";
    		}
    	} catch(SQLException e)
    	{
    		e.printStackTrace();
    	}

    	try {
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return msg;
    }

    public String delPermission(String user, String adder)
    {
    	this.connectSQL();
    	String msg = "Failed to remove permissions from user "+ Colors.BOLD + user;
    	try {
    		if(this.getPermission(user) <= 7 || this.getPermission(adder) == 10)
    		{
	    		Statement st = conn.createStatement();
	    		st.executeUpdate("DELETE FROM permissions WHERE user = '"+ user +"'");
	    		msg = "Successfully removed user "+ Colors.BOLD + user + Colors.NORMAL +" permissions";
    		} else {
    			msg = "You can't delete user with so high permission level";
    		}

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

    	try {
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return msg;
    }

    public void sqlTest() {
    	this.connectSQL();
    	try {
	    	Statement st = conn.createStatement();
	    	ResultSet r = st.executeQuery("SELECT * FROM test");

	    	while(r.next())
	    	{
	    		System.out.println(r.getInt("id"));
	    	}
    	} catch(Exception e)
    	{
    		e.printStackTrace();
    	}
    	try {
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    }
}