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


/*
 * This class sets up a connection with the database storing information about
 * the gym. This is the class where all database interaction occurs.
 * This is the model part of the program.
 *
 */
public class Database {

	private Connection connection = null; //?

	public Database(){

	}

	public void connection(){

		String dbname = "m_16_1102394m";	//Database name.
		String username = "m_16_1102394m";	//My username.
		String password = "1102394m";		//My password.

		try
		{
			//Use DriverMaanger class to get a connection then pass the details for the connection.
			connection = DriverManager.getConnection("jdbc:postgresql://yacata.dcs.gla.ac.uk:5432/"+dbname,username,password);

		}
		catch(SQLException e)
		{
			System.err.println("Connection Failed!");
			e.printStackTrace();
			return;
		}
		//Check connection is not null as this won't be caught in catch block.
		if(connection != null)
		{
			System.out.println("Connection successful");
		}
		else
		{
			System.err.println("Failed to make connection");
		}
	}


	/*
	 * Method to get the names of all the gym courses.
	 *
	 * @return	An array containing the names of the gym course.
	 */
	public String[] getGymCourse()
	{
		String[] list = new String[10];
		Statement stmt = null;
		String query = "SELECT * FROM gymcourse";
		try
		{
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(query);

			 int i=0;
			 while (rs.next())
			 {
				 String course_name = rs.getString("name");
				 list[i] = course_name;
				 i++;
			 }
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			System.err.println("error executing query " + query);
		}
		return list;
		}


	public int getBookings(String course)
	{
		Statement stmt = null;
		int count=0;
		//String query = "UPDATE instructors SET fname='finding', lname='nemo' WHERE instructorid=2";
		String query = "select * from gymcourse as g inner join coursebookings as c on g.courseid = c.course where name='"+course+"'";
		try{
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(query);

			//the next method of ResultSet allows you to iterate through the results
			 while (rs.next())
			 {
			 int book = rs.getInt("member");
			 count+=1;
			 }
			}

			catch (SQLException e )
			{
			e.printStackTrace();
			System.err.println("error executing query " + query);
			}
		 return count;
	}

	public int getCapacity(String course)
	{
		Statement stmt = null;
		int c=-1;
		//String query = "UPDATE instructors SET fname='finding', lname='nemo' WHERE instructorid=2";
		String query = "SELECT capacity FROM gymcourse WHERE name='"+course+"'";
		try{
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(query);

			//the next method of ResultSet allows you to iterate through the results
			 while (rs.next())
			 {
			 c = rs.getInt("capacity");
			 }
			}

			catch (SQLException e )
			{
			e.printStackTrace();
			System.err.println("error executing query " + query);
			}
		 return c;
	}

	public String[][] viewMembers(String course)
	{

		String[][] bookings = new String[20][2];
		Statement stmt = null;
		int count = 0;
		//String query = "UPDATE instructors SET fname='finding', lname='nemo' WHERE instructorid=2";
		String query = "SELECT * FROM gymcourse AS g INNER JOIN coursebookings AS c ON c.course=g.courseid INNER JOIN members AS m ON c.member=m.memberid WHERE name='"+course+"'";;
		try{
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(query);

			//the next method of ResultSet allows you to iterate through the results
			 while (rs.next())
			 {
			 String member_name = rs.getString("fname");
			 String member_lname = rs.getString("lname");
			 String member = member_name+" "+member_lname;
			 int id = rs.getInt("memberid");
			 bookings[count][0]= member;
			 bookings[count][1]=""+id;
			 System.out.println(member);
			 count++;

			 }
			}

			catch (SQLException e )
			{
			e.printStackTrace();
			System.err.println("error executing query " + query);
			}
		 return bookings;

	}

	public int getCourseID(String course)
	{
		Statement stmt = null;
		int courseid = -1;
		//String query = "UPDATE instructors SET fname='finding', lname='nemo' WHERE instructorid=2";
		String query = "SELECT * FROM gymcourse WHERE name='"+course+"'";;
		try{
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(query);

			//the next method of ResultSet allows you to iterate through the results
			 while (rs.next())
			 {
			 courseid = rs.getInt("courseid");

			 }
			}

			catch (SQLException e )
			{
			e.printStackTrace();
			System.err.println("error executing query " + query);
			}
		 return courseid;
	}

	public void addBooking(int courseid, String id)
	{
		int memid = Integer.valueOf(id);
		Statement stmt = null;
		String instructor ="";
		//String query = "UPDATE instructors SET fname='finding', lname='nemo' WHERE instructorid=2";
		String update = "INSERT INTO coursebookings VALUES("+memid+", "+courseid+")";
		try{
			stmt = connection.createStatement();
			int rs = stmt.executeUpdate(update);

			//the next method of ResultSet allows you to iterate through the results

			}
			catch (SQLException e )
			{
			e.printStackTrace();
			System.err.println("error executing update " + update);
			}

	}

	public boolean checkCapacity(String course)
	{
		Statement stmt = null;
		int count = 0;
		//String query = "UPDATE instructors SET fname='finding', lname='nemo' WHERE instructorid=2";
		String query = "SELECT member FROM gymcourse AS g INNER JOIN coursebookings AS c ON c.course=g.courseid INNER JOIN members AS m ON c.member=m.memberid WHERE name='"+course+"'";
		try{
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(query);

			//the next method of ResultSet allows you to iterate through the results
			 while (rs.next())
			 {
			 count+=1;


			 }
			}

			catch (SQLException e )
			{
			e.printStackTrace();
			System.err.println("error executing query " + query);
			}
		if(count == getCapacity(course))
			return false;
		else
			return true;
	}


	public boolean checkAlreadyBooked(String id, String course)
	{
		int check = Integer.valueOf(id);
		Statement stmt = null;
		int count = 0;
		//String query = "UPDATE instructors SET fname='finding', lname='nemo' WHERE instructorid=2";
		String query = "SELECT member FROM gymcourse AS g INNER JOIN coursebookings AS c ON c.course=g.courseid INNER JOIN members AS m ON c.member=m.memberid WHERE name='"+course+"'";
		try{
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(query);

			//the next method of ResultSet allows you to iterate through the results
			 while (rs.next())
			 {
				 if(check == rs.getInt("member"))
					 return false;


			 }
			}

			catch (SQLException e )
			{
			e.printStackTrace();
			System.err.println("error executing query " + query);
			}

			return true;
	}

	public boolean checkValidMember(String member)
	{
		int id = Integer.valueOf(member);
		Statement stmt = null;
		//String query = "UPDATE instructors SET fname='finding', lname='nemo' WHERE instructorid=2";
		String query = "SELECT memberid FROM members";
		try{
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(query);

			//the next method of ResultSet allows you to iterate through the results
			 while (rs.next())
			 {
				 if(id == rs.getInt("memberid"))
					 return true;


			 }
			}

			catch (SQLException e )
			{
			e.printStackTrace();
			System.err.println("error executing query " + query);
			}

			return false;
	}


	public String getInstructor(String course)
	{
		Statement stmt = null;
		String instructor ="";
		//String query = "UPDATE instructors SET fname='finding', lname='nemo' WHERE instructorid=2";
		String query = "SELECT fname, lname FROM instructors AS i INNER JOIN gymcourse AS g ON i.instructorid=g.instructor WHERE name='"+course+"'";
		try{
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(query);

			//the next method of ResultSet allows you to iterate through the results
			 while (rs.next())
			 {
			 String instructor_name = rs.getString("fname");
			 String instructor_lname = rs.getString("lname");
			 instructor = instructor_name+" "+instructor_lname;
			 }
			}

			catch (SQLException e )
			{
			e.printStackTrace();
			System.err.println("error executing query " + query);
			}
		 return instructor;
	}

	public void close()
	{
		try
		{
			connection.close();
			System.out.println("Connection closed");
		}
		catch(SQLException e)
		{
			e.printStackTrace();
			System.out.println("Connection could not be closed - SQL exception");
		}
	}
}