import java.sql.*;
import java.util.ArrayList;

import javax.security.sasl.Sasl;
import javax.swing.JComboBox;
import javax.swing.JOptionPane;
import javax.swing.JTable;

public class JDBC_Action
{
	//instance variables
	private Connection connection = null;
	//noOfCourses

	public JDBC_Action()
	{
		 String dbname = "m_16_2284062p";
		 String username = "m_16_2284062p";
		 String password = "2284062p";




		try
		{
			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;
		}
		if (connection != null)
		{
			System.out.println("Connection successful");
		}
		else
		{
			System.err.println("Failed to make connection!");
		}
	}


	//getInstructor Method
	public String getInstructor(String choice)
	{
		String course =  choice;
		String instructor = "";
		Statement stmt = null;
		String query = " SELECT fname, sname FROM instructors INNER JOIN courses ON instructors.instructornumber = courses.courseinstructor WHERE courses.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 f_name = rs.getString("fname");
				String s_name = rs.getString("sname");
				instructor = f_name + " " + s_name;
			}
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			System.err.println("error executing query " + query);
		}
		return instructor;
	}

	//getMembers Method

	//getCourseCapacity Method
	public int getCapacity(String choice)
	{
		String course = choice;
		int capacity = 0;
		Statement stmt = null;
		String query = "SELECT capacity FROM courses WHERE name = '" + course + "'";

		try
		{
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(query);

			while(rs.next())
			{
			capacity = rs.getInt("capacity");
			}
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		return capacity;
	}

	//getPlacesBooked Method
	public int getBooked(String choice)
	{
		String course = choice;
		int booked = 0;
		Statement stmt = null;
		String query = "SELECT COUNT (member) FROM coursebooking INNER JOIN courses ON courses.courseid = coursebooking.course WHERE courses.name = '" + course + "'";

		try
		{
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(query);

			while(rs.next())
			{
			booked = rs.getInt(1);
			}
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		System.err.println(booked);
		return booked;
	}



	//CHeck if Member is already booked on course
	public boolean checkDuplicate(int memID, String choice)
	{
		String course = choice;
		int idBook = memID;
		Statement stmt = null;
		String checkUnique = "SELECT member FROM coursebooking INNER JOIN courses ON courses.courseid = coursebooking.course WHERE courses.name = '" + course + "'";
		int [] idOnCourse = new int [20];
		int i = 0;
		boolean duplicate = false;


		try
		{
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(checkUnique);

			while (rs.next())
			{
				idOnCourse [i] = rs.getInt("member");

				if (idBook == idOnCourse[i]) //check if member ID is already booked on course
				{

					JOptionPane.showMessageDialog(null, "Member Already Booked on this Course",
							"Double Booking Attempt", JOptionPane.ERROR_MESSAGE);
					duplicate = true;
				}
				i++;
			}

		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		System.out.println("book ID" + idBook);
		System.err.println(duplicate);
		return duplicate;
	}



	public void BookCourse(int memID, String choice)
	{
		String course = choice;
		int idBook = memID;
		Statement stmt = null;
		String insertMem = "INSERT INTO coursebooking (member, course) SELECT " + idBook + ", courseid FROM courses WHERE name = '" + course + "'";
		try
		{
			stmt = connection.createStatement();

			stmt.executeUpdate(insertMem);

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



	//method to get courses from database into jcombobox
	public String[] getCourse()
	{
		Statement stmt = null;
		String [] courseName = new String [9];
		String query = "SELECT name FROM courses";
		int i = 0;

		try
		{
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(query);

			while (rs.next())

			{
				 courseName [i] = rs.getString("name");
				 System.out.println(courseName);
				 i ++;
			}
		}
		catch (Exception e)
		{
			JOptionPane.showMessageDialog(null, e);
		}
		return courseName;
	}


	//check if person who attempts booking is a member
	public boolean isMember (int memID)
	{
		int idBook = memID;
		boolean member = false;
		String query = "SELECT memid FROM members";
		int [] gymMembers = new int [12];
		int i = 0;
		Statement stmt = null;


		try
		{
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(query);

			while (rs.next())
			{
				gymMembers [i] = rs.getInt("memid");

				if (idBook == gymMembers[i]) //check if member ID is already booked on course
				{
					member = true;
				}
				i++;
			}

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

	//check if course can still be booked or if it is already full
	public boolean emptySpaces (String choice)
	{
		String course = choice;
		boolean available = false;

		int booked = getBooked(course);
		int capacity = getCapacity(course);

		if (booked < capacity)
		{
			available = true;
		}
		return available;
	}


	public void close()
	{
		try
		{
			connection.close();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
	}

	//Data for JTable
	public String[][] getTable(String choice)
	{
		Statement stmt = null;
		String course = choice;
		String query = "SELECT fname, sname, memid FROM members AS m "
					 + "INNER JOIN coursebooking as cb ON m.memid = cb.member "
					 + "INNER JOIN courses AS c ON cb.course = c.courseid "
					 + "WHERE c.name = '" + course + "'";

		String[][] tabledata = {{}};
		int i = 0;
		int numMem = 0;

		try
		{
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(query);

			String numOnCourse = "SELECT COUNT (member) FROM coursebooking "
					+ "INNER JOIN courses ON courses.courseid = coursebooking.course "
					+ "WHERE courses.name = '" + course + "'";
			stmt = connection.createStatement();
			ResultSet rs2 = stmt.executeQuery(numOnCourse);

			while (rs.next())
			{
				//get number of members on course
				numMem = rs2.getInt(1);

				for (i = 0; i <= numMem; i++)
				{
					for (int j = 0; j< 3; j++)
					{
						tabledata [i][j] = rs.getString("fname");
						tabledata [i][j] = rs.getString("sname");
						tabledata [i][j] = rs.getString("memid");
					}
				}
			}
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}

		return tabledata;
	}
}