package SQLConnection;

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

public class SQLUtility {

	private static Connection minConnection;
	private static Statement stmt;
	private static PreparedStatement prepStatement;

	private static final String databaseUsed = "DAOS";

	public static Connection openConnection() {

		try {
			minConnection = DriverManager.getConnection(
					// "jdbc:sqlserver://EAA-SH-STHA-AO;databaseName=Yodafitness;user=sa;password=HelloWorld;");
					"jdbc:sqlserver://CRYONICS-LAPTOP\\SQLEXPRESS;databaseName=" + databaseUsed
							+ ";user=sa;password=daos;");

			stmt = minConnection.createStatement();

			return minConnection;

		} catch (Exception e) {
			System.out.println("fejl:  " + e.getMessage());
			return null;
		}

	}

	public static void closeConnection() {
		if (stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (prepStatement != null) {
			try {
				prepStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (minConnection != null) {
			try {
				minConnection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public static ResultSet makeQuery(String input) {
		try {
			ResultSet res = stmt.executeQuery(input);

			return res;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	// Dropper den existerende og opretter en ny.
	public static void makeProc(String procName, String query) {
		dropProc(procName);
		String procedure = "CREATE PROCEDURE " + "." + procName + " AS " + query;
		try {
			stmt.executeUpdate(procedure);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void dropProc(String procName) {
		String procedure = doesProcExistString(procName) + "DROP PROCEDURE " + procName;
		try {
			stmt.executeUpdate(procedure);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static String doesProcExistString(String procName) {
		return "IF(OBJECT_ID('" + procName + "') IS NOT NULL) ";
	}

	public static ResultSet runProc(String procName) {
		String procedure = "EXEC " + procName;
		try {
			ResultSet res = stmt.executeQuery(procedure);
			return res;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	public static PreparedStatement createPrepStatement(String query) {
		prepStatement = null;
		if (minConnection == null) {
			System.out.println("WTF");
		}
		try {
			prepStatement = minConnection.prepareStatement(query);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return prepStatement;
	}

	public static int getMaxInt(String columnName, String tableName) {
		String query = "select max(" + columnName + ") as id from " + tableName;

		ResultSet set = makeQuery(query);

		int number = -1;
		try {
			while (set.next()) {
				number = set.getInt("id");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return number;
	}
}