package utility;

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

public class MySQLAccess
{

	@SuppressWarnings("unused")
	private int x = 0;
	private Connection connect;
	private Statement statement;
	private ResultSet resultSet;

	//URL
	final private String url = "jdbc:mysql://127.0.0.1:3306/corsojava?serverTimezone=UTC";
	final private String user = "root";
	final private String passwd = "";


	public MySQLAccess()
	{
		connect=getConnection();
	}

	private Connection getConnection()
	{
		try
		{
			Class.forName("com.mysql.cj.jdbc.Driver");

			connect = DriverManager.getConnection(url, user, passwd);
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		return connect;
	}

	private void close()
	{
		try
		{
			if (resultSet != null)
			{
				resultSet.close();
			}

			if (statement != null)
			{
				statement.close();
			}

			if (connect != null)
			{
				connect.close();
			}
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
	}

	//QUERIES

	public int takeId()
	{
		int id = 0;

		try
		{
			statement = connect.createStatement();
			resultSet = statement.executeQuery("SELECT * FROM user");
			while(resultSet.next())
			{
				id = resultSet.getInt("id");
			}
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
		finally
		{
			close();
		}
		return id;
	}


	public void insertUser(User user)
	{
		try
		{
			statement = connect.createStatement();
			x = statement.executeUpdate("INSERT INTO corsojava.user (serialNumber,firstName,lastName,password,birthDate,birthPlace,address)"
					+ " VALUES('"+user.getSerialNumber()+ "','"
					+user.getFirstName()+ "','" +user.getLastName()+ "','"
					+user.getPassword()+"','"+user.getBirthDate()+"','"
					+user.getBirthPlace()+"','"+user.getAddress()+"');");
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
	}

	public ResultSet showBookedExam()
	{
		try
		{
			statement = connect.createStatement();
			resultSet = statement.executeQuery("SELECT e.idExam,e.subject,e.date,e.classroom "
					+ "FROM exam AS e,users AS u, management AS m"
					+ "WHERE e.idExam = m.fk_exam AND u.id = m.fk_user"
					+ "GROUP BY e.idExam");
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		return resultSet;
	}

	public ResultSet showBookableExam()
	{
		try
		{
			statement = connect.createStatement();
			resultSet = statement.executeQuery("SELECT e.idExam,e.subject,e.date,e.classroom "
					+ "FROM exam AS e,users AS u, management AS m"
					+ "WHERE NOT(e.idExam = m.fk_exam) AND u.id = m.fk_user"
					+ "GROUP BY e.idExam");
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		return resultSet;
	}

	public void deleteBookedExam(int choose)
	{
		try
		{
			statement = connect.createStatement();
			x = statement.executeUpdate("DELETE FROM management WHERE management.fk_exam="+choose);
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
	}

	public void deleteExam(int choose)
	{
		try
		{
			statement = connect.createStatement();
			x = statement.executeUpdate("DELETE FROM management WHERE management.fk_exam="+choose+"DELETE FROM exam WHERE exam.idExam="+choose);
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
	}

	public void changePSW(String newPsw,int idUser)
	{
		try
		{
			statement = connect.createStatement();
			x = statement.executeUpdate("UPDATE users SET password="+newPsw+"WHERE users.id="+idUser);
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
	}

	public void bookExam(int idUser,int choose)
	{
		try
		{
			statement = connect.createStatement();
			x = statement.executeUpdate("INSERT INTO management (fk_user,fk_exam)"
					+ "VALUES ("+idUser+","+choose+")");
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
	}

	public void createExam(String subject,String date,String classroom)
	{
		try
		{
			statement = connect.createStatement();
			x = statement.executeUpdate("INSERT INTO exam (subject,date,classroom)"
					+ "VALUES ("+subject+","+date+","+classroom+")");
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
	}

	public ResultSet showAllExam()
	{
		try
		{
			statement = connect.createStatement();
			resultSet = statement.executeQuery("SELECT e.idExam,e.subject,e.date,e.classroom"
					+ "FROM exam AS e"
					+ "GROUP BY e.idExam");
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
		return resultSet;
	}
}