package gilidon.host.machinecrafting;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

public class SQLManager {
    public Connection connection;
    private MachineCrafting plugin;
    public SQLManager(final MachineCrafting plugin) {
        this.plugin = plugin;
    }

    void openConnection(){
        try {
            this.connection = DriverManager.getConnection
            		("jdbc:mysql://" + this.plugin.getConfig().getString("MySQL.host")
            				+ ":"
            				+ this.plugin.getConfig().getString("MySQL.port")
            				+ "/"
            				+ this.plugin.getConfig().getString("MySQL.database"),
            				new StringBuilder().append(this.plugin.getConfig().getString("MySQL.user")).toString(),
            				new StringBuilder().append(this.plugin.getConfig().getString("MySQL.password")).toString());
        }
        catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void closeConnection(){
        try {
            this.connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void initDatabase(){
        this.openConnection();
        try {
            final PreparedStatement sql = connection.prepareStatement
            		("CREATE TABLE IF NOT EXISTS `MachineCrafting`"
            				+ " (`ID` INT NOT NULL UNIQUE,"
            				+ " `uuid` varchar(100),"
            				+ " `town` varchar(100),"
            				+ " `machine` varchar(100),"
            				+ " `isname` varchar(100),"
            				+ " `slot` INT,"
            				+ " `remainingtime` INT,"
            				+ " PRIMARY KEY (`ID`)) ;");
            sql.execute();
            sql.close();
        } catch (Exception e) {
            e.printStackTrace();
            return;
        }
    }

    public Integer nextID(){
        Integer data = 0;
        try{
            PreparedStatement sql = connection.prepareStatement
            		("SELECT `ID` FROM `MachineCrafting` ORDER BY `ID` DESC LIMIT 1;");
            ResultSet rs = sql.executeQuery();
            if(rs.next()){
                data = rs.getInt("id") + 1;
            }
            rs.close();
            sql.close();
        }catch(Exception e){
            e.printStackTrace();
        }
        return data;
    }

    public void countdown(){
    	try{
			PreparedStatement sql = connection.prepareStatement
            		("SELECT * FROM `MachineCrafting`;");
            ResultSet rs = sql.executeQuery();
            while(rs.next()){
                if(rs.getInt("remainingtime") > 0){
                	PreparedStatement sql3 = connection.prepareStatement
                    		("UPDATE `MachineCrafting` SET `remainingtime` =? WHERE `id`=?;");
                    sql3.setInt(1, rs.getInt("remainingtime") - 1);
                    sql3.setInt(2, rs.getInt("id"));
                    sql3.executeUpdate();
                    sql3.close();
                }
                if(rs.getInt("remainingtime") < 0){
                	PreparedStatement sql3 = connection.prepareStatement
                    		("UPDATE `MachineCrafting` SET `remainingtime` =? WHERE `id`=?;");
                    sql3.setInt(1, 0);
                    sql3.setInt(2, rs.getInt("id"));
                    sql3.executeUpdate();
                    sql3.close();
                }
            }
            rs.close();
            sql.close();
        }catch(Exception e){
            e.printStackTrace();
        }
    }

	public String nameToUUID(String name){
		String uuid = "";
		try{
			PreparedStatement sql = connection.prepareStatement
					("SELECT * FROM `always_online` WHERE `name`=?;");
			sql.setString(1, name);
			ResultSet rs = sql.executeQuery();
			while (rs.next()){
				uuid = rs.getString("uuid");
			}
			rs.close();
			sql.close();
			}catch(Exception e){
		        e.printStackTrace();
		    }
		return uuid;
	}

	//Gets player's active crafts for this machine from the SQL database
	public ArrayList<String> getCraft(String name, String town, String machine, Integer slot){
		String uuid = nameToUUID(name);
		ArrayList<String> data = new ArrayList<String>();
		data.add(0, "0");
		data.add(1, "blank");
		String timestring = "timestring";
		try{
			PreparedStatement sql = connection.prepareStatement
					("SELECT * FROM `MachineCrafting` WHERE `uuid`=?;");
			sql.setString(1, uuid);
			ResultSet rs=sql.executeQuery();
			while (rs.next()){
				if(rs.getInt("slot")==slot && town.equals(rs.getString("town")) && machine.equals(rs.getString("machine"))){
					Integer remainingtime = rs.getInt("remainingtime");
					timestring = remainingtime.toString();
					String isname = rs.getString("isname");
					data.set(0, timestring);
					data.set(1, isname);
					}
			}
			rs.close();
			sql.close();
		}catch(Exception e){
	        e.printStackTrace();
	    }
		return data;
	}

	//Removes specified craft from database
	public void removeItem(String name, String town, String machine, int slot){
		String uuid = nameToUUID(name);
        try{
        	PreparedStatement sql = connection.prepareStatement
					("SELECT * FROM `MachineCrafting` WHERE `uuid`=?;");
			sql.setString(1, uuid);
			ResultSet rs=sql.executeQuery();
			while (rs.next()){
				if(rs.getInt("slot")==slot && town.equals(rs.getString("town")) && machine.equals(rs.getString("machine"))){
					Integer id = rs.getInt("ID");
					PreparedStatement sql1 = connection.prepareStatement
							("DELETE FROM `MachineCrafting` WHERE `ID`=?;");
							sql1.setInt(1, id);
							sql1.execute();
							sql1.close();
					}
				}
			}catch(Exception e){
				e.printStackTrace();
			}
    }

    //Inserts craft into MySQL database
	public void newCraft(String name, String town, String machine, Integer slot, String isname, Integer remainingtime){
		String uuid = nameToUUID(name);
		try{
			PreparedStatement sql = connection.prepareStatement
					("INSERT INTO `MachineCrafting`"
							+ " (`ID`, `uuid`, `town`, `machine`, `isname`, `slot`, `remainingtime`)"
							+ " VALUES (?,?,?,?,?,?,?);");
        	sql.setInt(1, nextID());
			sql.setString(2, uuid);
            sql.setString(3, town);
            sql.setString(4, machine);
            sql.setString(5, isname);
            sql.setInt(6, slot);
            sql.setInt(7, remainingtime);
            sql.execute();
            sql.close();
        }catch(Exception e){
            e.printStackTrace();
        }
	}

//Final closing bracket
}