package com.example.util;
import java.util.*;
import java.sql.*;
import com.example.model.*;
/**
 * The 
 * @author  group f
 * @version a10 version
 *  
 */
public class DatabaseUtil 
{
	private static String jdbcURL = null;
	private static String user = null;
	private static String pass = null;
	private static Connection MyConn = null;
	private static Statement MyStmt = null;
	private static ResultSet queryResult = null; 
	
	/**
	 * 
	 * @param connection
	 * @param login
	 * clears the schedule of the specified user in the database
	 */
	public static void ClearSchedule (Properties connection, String login)
	{
		jdbcURL = connection.getProperty("jdbcUrl");
		user = connection.getProperty("user");
		pass = connection.getProperty("pass");
		String command = new String("DELETE FROM Events" +
				" WHERE User_Login = '" + login + "';");
		try
		{
			MyConn = DriverManager.getConnection(jdbcURL, user, pass);
            MyStmt = MyConn.createStatement();
            MyStmt.executeUpdate(command);
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try{MyStmt.close(); MyConn.close();}
			catch(Exception e){e.printStackTrace();}
		}
	}
	/**
	 *takes a connection and gets a list of users
	 *@params Properties connection
	 */
	public static ArrayList getUsers(Properties connection)
	{
		jdbcURL = connection.getProperty("jdbcUrl");
		user = connection.getProperty("user");
		pass = connection.getProperty("pass");
		ArrayList users = new ArrayList();
		String query = new String("SELECT User_Login" +
				" FROM Users;");
		String theUser = null;
		try
		{
			MyConn = DriverManager.getConnection(jdbcURL, user, pass);
            MyStmt = MyConn.createStatement();
            queryResult = MyStmt.executeQuery(query);
            while(queryResult.next())
            {
            	theUser = queryResult.getString("User_Login");
            	users.add(theUser);
            }
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try{MyStmt.close(); MyConn.close();}
			catch(Exception e){e.printStackTrace();}
		}
		return users;
	}
	/**
	*@returns ArrayList of events
	*@param Properties connection
	*@param	String user_login
	* returns a list of times that a user is busy
	*/
	public static ArrayList getMultipleSchedules(Properties connection, String[] users)
	{
		ArrayList allEvents = new ArrayList();
		jdbcURL = connection.getProperty("jdbcUrl");
		user = connection.getProperty("user");
		pass = connection.getProperty("pass");
		try
		{
			MyConn = DriverManager.getConnection(jdbcURL, user, pass);
            MyStmt = MyConn.createStatement();
            for(int i = 0; i < users.length; i++)
            {
            	String query = new String("SELECT *" +
            			" FROM Events" +
            			" WHERE User_Login = '" + users[i] + "'" +
						" ORDER BY Day;");
                queryResult = MyStmt.executeQuery(query);
                while(queryResult.next())
                {
                	int day = queryResult.getInt("Day");
                	int start = queryResult.getInt("Event_Start");
                	int end = queryResult.getInt("Event_End");
                	Event myEvent = new Event(day, start, end);
                	allEvents.add(myEvent);
                }
            }
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try{MyStmt.close(); MyConn.close();}
			catch(Exception e){e.printStackTrace();}
		}
		return allEvents;
	}
	/**
	*@returns ArrayList of events
	*@param Properties connection
	*@param	String user_login
	* returns a list of times that a user is busy
	*/
	public static ArrayList getSchedule(Properties connection, String user_login)
	{
		ArrayList events = null;
		jdbcURL = connection.getProperty("jdbcUrl");
		user = connection.getProperty("user");
		pass = connection.getProperty("pass");
		String schedule_to_query = new String("SELECT *" +
				" FROM Events" +
				" WHERE User_Login = '" + user_login + "'" +
				" ORDER BY Day;");
		int day = 0;
		int start = 0;
		int end = 0;
		Event myEvent = null;
		try
		{
			MyConn = DriverManager.getConnection(jdbcURL, user, pass);
            MyStmt = MyConn.createStatement();
            queryResult = MyStmt.executeQuery(schedule_to_query);
            events = new ArrayList();
            while(queryResult.next())
            {
            	day = queryResult.getInt("Day");
            	start = queryResult.getInt("Event_Start");
            	end = queryResult.getInt("Event_End");
            	myEvent = new Event(day, start, end);
            	events.add(myEvent);
            }
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try{MyStmt.close(); MyConn.close();}
			catch(Exception e){e.printStackTrace();}
		}
		return events;
	}
	/**
	* @param Properties connection
	* @param String user_login
	* @param int day
	* @param int start
	* @param int end
	* adds a time to the database that is busy
	*/
	public static void addEvent(Properties connection, String user_login, int day, int start, int end)
	{
		jdbcURL = connection.getProperty("jdbcUrl");
		user = connection.getProperty("user");
		pass = connection.getProperty("pass");
		String event_to_query = new String("SELECT *" +
				" FROM Events" +
				" WHERE Day = " + day +
				" AND Event_Start = " + start +
				" AND Event_End = " + end +
				" AND User_Login = '" + user_login + "';");
		String event_to_add = new String("INSERT INTO Events VALUES (" + 
				day + ", " + start + ", " + end + ", '" +
				user_login + "');");
		try
		{
			MyConn = DriverManager.getConnection(jdbcURL, user, pass);
            MyStmt = MyConn.createStatement();
            queryResult = MyStmt.executeQuery(event_to_query);
            if(!queryResult.next())
            {
            	MyStmt.executeUpdate(event_to_add);
            }
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try{MyStmt.close(); MyConn.close();}
			catch(Exception e){e.printStackTrace();}
		}
	}
	/**
	* @param Properties connection
	* @param String user_login
	* @param String user_pass
	* sees if user entered information is the same as the database records
	*/
	public static boolean authenticateUser(Properties connection, String user_login, String user_pass)
	{
		jdbcURL = connection.getProperty("jdbcUrl");
		user = connection.getProperty("user");
		pass = connection.getProperty("pass");
		String login_user_query = new String("SELECT User_Login, User_Pass" +
				" FROM Users" +
				" WHERE User_Login = '" + user_login + "'" +
				" AND User_Pass = '" + user_pass + "';");
		boolean result = false;
		try
		{
			MyConn = DriverManager.getConnection(jdbcURL, user, pass);
            MyStmt = MyConn.createStatement();
            queryResult = MyStmt.executeQuery(login_user_query);
            if(queryResult.next())
            {
            	 result = true;
            }
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try{MyStmt.close(); MyConn.close();}
			catch(Exception e){e.printStackTrace();}
		}
		return result;
	}
	/**
	* @param Properties connection
	* @param String user_login
	* @param String user_pass	
	* adds user to the database
	*/
	public static boolean addUser(Properties connection, String user_login, String user_pass)
	{
		jdbcURL = connection.getProperty("jdbcUrl");
		user = connection.getProperty("user");
		pass = connection.getProperty("pass");
		String check_user_query = new String("SELECT User_Login" +
				" FROM Users" +
				" WHERE User_Login = '" + user_login + "';");
		String user_to_add = new String("INSERT INTO Users VALUES ( '" +
				user_login + "', '" + user_pass + "');");
		boolean result = false;
		try
		{
			MyConn = DriverManager.getConnection(jdbcURL, user, pass);
            MyStmt = MyConn.createStatement();
            queryResult = MyStmt.executeQuery(check_user_query);
            if(!queryResult.next())
            {
            	 MyStmt.executeUpdate(user_to_add);
            	 result = true;
            }
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try{MyStmt.close(); MyConn.close();}
			catch(Exception e){e.printStackTrace();}
		}
		return result;
	}
}
