package JDBCProject1;

import java.sql.*;
import edu.uci.ics.pattis.introlib.*;
import java.util.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.KeyEvent;
import java.io.*;
import javax.swing.*;




public class JDBCProject1 {
	private static char menuPrompt () {
		System.out.println("FabFlix Commands: ");
		System.out.println("  p - Print out movies featuring a star");
		System.out.println("  s - Insert a new star into database");
		System.out.println("  c - Insert a customer into database");
		System.out.println("  d - Delete a customer from database");
		System.out.println("  t - Add a movie into database");
		System.out.println("  m - Provide metadata of database");
		System.out.println("  x - Provide a SQL command");
		System.out.println("  o - Provide database + user access information");
		System.out.println("  k - Create Fabflix Error Report");
		System.out.println("  e - Edit user access rights/Create Users");
		System.out.println("  u - Exit the menu");
		System.out.println("  z - Exit the program");

		return Prompt.forChar("\nEnter Command","pscdtkmoxuez");
	}
	static Connection conn = null;


	@SuppressWarnings({ "restriction", "unused" })

	public static void main(String[] args) throws Exception {
		Statement stmt = null;
		Statement stmt1 = null;
		boolean loggedIn = false;
		ResultSet rs = null;
		ResultSet rsType = null;
		String usernameDatabase;
		String passwordDatabase;
		String employeeEmail;
		String employeePassword;

		String url = "jdbc:mysql:///moviedb";


		Class.forName("com.mysql.jdbc.Driver").newInstance();
		if (loggedIn == false) {
			while(loggedIn == false) {
				System.out.println("Welcome to FabFlix! Please input user information below:");
				usernameDatabase = "root";
				passwordDatabase = "lakers";
				employeeEmail = Prompt.forString("Enter email");
				employeePassword = Prompt.forString("Enter password");
				String queryLogin = "SELECT count(*) from employees where email = '" + employeeEmail + "' and password = '" + employeePassword +"'";
				conn = DriverManager.getConnection(url, usernameDatabase, passwordDatabase);
				stmt = conn.createStatement();
				stmt1 = conn.createStatement();
				ResultSet rsLogin = stmt.executeQuery(queryLogin);

				while (rsLogin.next()){
					if (rsLogin.getString(1).equals("1")) {
						//			conn = DriverManager.getConnection(url, usernameDatabase, passwordDatabase);
						//			stmt = conn.createStatement();
						//			stmt1 = conn.createStatement();
						loggedIn = true;
						usernameDatabase = null;
						passwordDatabase = null;
						break;
					}
					else
						System.out.println("Wrong username/password combination. Please try again.");
				}
			}
		}

		for(;;) try {
			if (loggedIn == true) {
				char selection = menuPrompt();
				if (selection == 'p') {
					String firstName = Prompt.forString("Please enter first name");
					String lastName = Prompt.forString("Please enter last name");
					String identification = Prompt.forString("Please enter ID");
					if (firstName.equals("") && !lastName.isEmpty()) {
						rs = stmt.executeQuery("SELECT m.id, m.title, m.year, m.director, m.banner_url, m.trailer_url FROM movies as m, stars as s, stars_in_movies as t" +
								" WHERE m.id = t.movie_id and s.id = t.star_id and s.last_name = " + "'" + lastName + "'");
						ResultSetMetaData rsmd = rs.getMetaData();
						for (int i = 1; i <= rsmd.getColumnCount(); i++) {
							System.out.print(rsmd.getColumnName(i) + "\t");
						}
						System.out.println();

						while (rs.next()) {
							int id = rs.getInt("id");
							String title = rs.getString("title");
							int year = rs.getInt("year");
							String director = rs.getString("director");
							String banner = rs.getString("banner_url");
							String trailer = rs.getString("trailer_url");
							System.out.println(id + "\t" + title + "\t" + year + "\t" + director + "\t" + banner + "\t" + trailer);
						}
					}
					else if (lastName.equals("") && !firstName.isEmpty()){ //Have: First name/Don't Have: Last name or ID
						rs = stmt.executeQuery("SELECT m.id, m.title, m.year, m.director, m.banner_url, m.trailer_url FROM movies as m, stars as s, stars_in_movies as t" +
								" WHERE m.id = t.movie_id and s.id = t.star_id and s.first_name = " + "'" + firstName + "'");
						ResultSetMetaData rsmd = rs.getMetaData();
						for (int i = 1; i <= rsmd.getColumnCount(); i++) {
							System.out.print(rsmd.getColumnName(i) + "\t");
						}
						System.out.println();
						while (rs.next()) {
							int id = rs.getInt("id");
							String title = rs.getString("title");
							int year = rs.getInt("year");
							String director = rs.getString("director");
							String banner = rs.getString("banner_url");
							String trailer = rs.getString("trailer_url");
							System.out.println(id + "\t" + title + "\t" + year + "\t" + director + "\t" + banner + "\t" + trailer);
						}
					}
					else if (firstName.equals("") && lastName.equals("")) {
						int newID = Integer.parseInt(identification);
						rs = stmt.executeQuery("SELECT m.id, m.title, m.year, m.director, m.banner_url, m.trailer_url FROM movies as m, stars as s, stars_in_movies as t " +
								" WHERE m.id=t.movie_id and s.id=t.star_id and s.id = " + newID);
						System.out.println("got here 1");
						ResultSetMetaData rsmd = rs.getMetaData();
						for (int i = 1; i <= rsmd.getColumnCount(); i++) {
							System.out.print(rsmd.getColumnName(i) + "\t");
						}
						System.out.println();

						while (rs.next()) {
							int id = rs.getInt("id");
							String title = rs.getString("title");
							int year = rs.getInt("year");
							String director = rs.getString("director");
							String banner = rs.getString("banner_url");
							String trailer = rs.getString("trailer_url");
							System.out.println(id + "\t" + title + "\t" + year + "\t" + director + "\t" + banner + "\t" + trailer);
						}
					}
					else {
						rs = stmt.executeQuery("SELECT m.id, m.title, m.year, m.director, m.banner_url, m.trailer_url FROM movies as m, stars as s, stars_in_movies as t" +
								" WHERE m.id = t.movie_id and s.id = t.star_id and s.first_name = " + "'" + firstName + "'" +  " and s.last_name = " + "'" +lastName +"'");
						ResultSetMetaData rsmd = rs.getMetaData();
						for (int i = 1; i <= rsmd.getColumnCount(); i++) {
							System.out.print(rsmd.getColumnName(i) + "\t");
						}
						System.out.println();

						while (rs.next()) {
							int id = rs.getInt("id");
							String title = rs.getString("title");
							int year = rs.getInt("year");
							String director = rs.getString("director");
							String banner = rs.getString("banner_url");
							String trailer = rs.getString("trailer_url");
							System.out.println(id + "\t" + title + "\t" + year + "\t" + director + "\t" + banner + "\t" + trailer);
						}
					}
				}
				else if (selection == 's') {
					Boolean singleName = Prompt.forBoolean("Does he/she have a single name?");
					if (singleName == true) {
						int id = Prompt.forInt("Enter ID number");
						String lastName = Prompt.forString("Enter name");
						String firstName = "";
						String dob = Prompt.forString("Enter DOB");
						String photo_url = Prompt.forString("Enter photo_url");
						int insert = stmt.executeUpdate("INSERT INTO stars (id, first_name, last_name, dob, photo_url) " +
								"VALUES('"+ id +"', '"+ firstName +"','"+
								lastName +"','"+ dob + "', '"+ photo_url +"');");
						System.out.println("1 record added into the Stars table.");
					}
					else {
						int id = Prompt.forInt("Enter ID number");
						String firstName = Prompt.forString("Enter first name");
						String lastName = Prompt.forString("Enter last name");
						String dob = Prompt.forString("Enter DOB");
						String photo_url = Prompt.forString("Enter photo_url");
						int insert = stmt.executeUpdate("INSERT INTO stars (id, first_name, last_name, dob, photo_url) " +
								"VALUES('"+ id +"', '"+ firstName +"','"+
								lastName +"','"+ dob + "', '"+ photo_url +"');");
						System.out.println("1 record added into the Stars table.");
					}

				}
				else if (selection == 't') {
					String title = Prompt.forString("Enter movie title");
					String titleQuery = "SELECT title FROM movies WHERE title = '"+title+"'";
					Statement statement1 = conn.createStatement();
					ResultSet rs1 = statement1.executeQuery(titleQuery);
					boolean changes = false;

					if (rs1.next()) {
						System.out.println("Movie title already exists. No other movie information is needed as no new movie title will be inserted.");
						System.out.println();
					}
					else {
						changes = true;
						System.out.println("New movie title has been detected!!!");
						System.out.println();

						int year = Prompt.forInt("Enter movie year");
						String director = Prompt.forString("Enter Director Name (first & last name)");
						String bannerURL = Prompt.forString("Enter Banner URL");
						String trailerURL = Prompt.forString("Enter Trailer URL");
						String procedure = "call add_movie('"+title+"',"+year+", '"+director+"', '"+bannerURL+"', '"+trailerURL+"')";
						Statement statement11 = conn.createStatement();
						boolean addMovieUpdate = statement11.execute(procedure);
						System.out.println("Movie title had been added to database.");
						System.out.println();

					}



					String firstName = Prompt.forString("Enter a star's first name");
					String lastName = Prompt.forString("Enter the same star's last name");
					String starQuery = "SELECT id FROM stars WHERE first_name = '"+firstName+"' and last_name = '"+lastName+"'";
					Statement statement2 = conn.createStatement();
					ResultSet rs2 = statement2.executeQuery(starQuery);

					if (rs2.next()) {
						System.out.println("Star already exists. No new star will be inserted.");
						System.out.println();

						String starID = rs2.getString("id");
						String titleID = "SELECT id FROM movies WHERE title = '"+title+"'";
						Statement statement21 = conn.createStatement();
						ResultSet rs21 = statement21.executeQuery(titleID);
						rs21.next();
						String movieID = rs21.getString("id");
						String checkQuery = "SELECT * from stars_in_movies where star_id ="+starID+" and movie_id ="+movieID;
						ResultSet rs22 = statement21.executeQuery(checkQuery);
						if (rs22.next()) {
							System.out.println("This star is already associated with the movie title you have entered above. No change is necessary. You will be re-directed to the genre input section.");
							System.out.println();

						}
						else {
							String insertSIM = "INSERT INTO stars_in_movies (star_id, movie_id) VALUES("+starID+","+movieID+")";
							int update = statement21.executeUpdate(insertSIM);
							changes = true;
							System.out.println("Stars_in_movies has be updated successfully!");
							System.out.println();

						}


					}
					else {
						changes = true;
						System.out.println("New star detected!!!");
						System.out.println();

						String dob = Prompt.forString("Enter DOB (YYYY-MM-DD)");
						String photoURL = Prompt.forString("Enter photo_url");
						int insert = stmt.executeUpdate("INSERT INTO stars (first_name, last_name, dob, photo_url) " +
								"VALUES('"+ firstName +"','"+
								lastName +"','"+ dob + "', '"+ photoURL +"')");
						System.out.println("New star added successfully!");
						System.out.println();

						String starIDQuery = "SELECT id FROM stars WHERE first_name = '"+firstName+"' and last_name = '"+lastName+"'";
						Statement statement22 = conn.createStatement();
						ResultSet rs22 = statement22.executeQuery(starIDQuery);
						rs22.next();
						String starID = rs22.getString("id");
						String titleID = "SELECT id FROM movies WHERE title = '"+title+"'";
						Statement statement21 = conn.createStatement();
						ResultSet rs21 = statement21.executeQuery(titleID);
						rs21.next();
						String movieID = rs21.getString("id");
						String insertSIM = "INSERT INTO stars_in_movies (star_id, movie_id) VALUES("+starID+","+movieID+")";
						int update = statement22.executeUpdate(insertSIM);
						System.out.println("Stars_in_movies has be updated succesfully!");
						System.out.println();



					}

					String genreName = Prompt.forString("Enter a genre of this movie");
					String genreQuery = "SELECT id FROM genres WHERE name = '"+genreName+"'";
					Statement statement3 = conn.createStatement();
					ResultSet rs3 = statement3.executeQuery(genreQuery);

					if (rs3.next()) {
						System.out.println("Genre already exists. No new genre will be inserted.");
						System.out.println();

						String genreID = rs3.getString("id");
						String titleID = "SELECT id FROM movies WHERE title = '"+title+"'";
						Statement statement21 = conn.createStatement();
						ResultSet rs21 = statement21.executeQuery(titleID);
						rs21.next();
						String movieID = rs21.getString("id");
						String checkQuery = "SELECT * from genres_in_movies where genre_id ="+genreID+" and movie_id ="+movieID;
						ResultSet rs22 = statement21.executeQuery(checkQuery);
						if (rs22.next()) {
							System.out.println("This genre is already associated with the movie title you have entered above. No change(s) are necessary.");
							System.out.println();

						}
						else {
							String insertGIM = "INSERT INTO genres_in_movies (genre_id, movie_id) VALUES("+genreID+","+movieID+")";
							int update = statement21.executeUpdate(insertGIM);
							changes = true;
							System.out.println("genres_in_movies has be updated successfully!");
							System.out.println();

						}

					}
					else {
						System.out.println("New genre is detected!!");
						System.out.println();

						changes = true;
						int insert = stmt.executeUpdate("INSERT INTO genres (name) " +
								"VALUES('"+ genreName +"')");
						System.out.println("New genre added successfully!");
						System.out.println();

						String genreIDQuery = "SELECT id FROM genres WHERE name = '"+genreName+"'";
						Statement statement22 = conn.createStatement();
						ResultSet rs22 = statement22.executeQuery(genreIDQuery);
						rs22.next();
						String genreID = rs22.getString("id");
						String titleID = "SELECT id FROM movies WHERE title = '"+title+"'";
						Statement statement21 = conn.createStatement();
						ResultSet rs21 = statement21.executeQuery(titleID);
						rs21.next();
						String movieID = rs21.getString("id");
						String insertGIM = "INSERT INTO genres_in_movies (genre_id, movie_id) VALUES("+genreID+","+movieID+")";
						int update = statement22.executeUpdate(insertGIM);
						System.out.println("genres_in_movies has be updated succesfully!");
						System.out.println();
					}
					if (changes) {
						System.out.println("Congratulations. You have successfully added a movie and/or modified the data related to a movie! " +
						"You will now be re-directed to the main menu.");
						System.out.println();
					}

					else {
						System.out.println("Sorry. All of the data you have inputted is already reflected in our database so no changes were made. You will now be re-directed to the main menu.");
						System.out.println();
					}
				}
				else if (selection == 'o') {
					Statement dbStatement = conn.createStatement();
					ResultSet resultDB = dbStatement.executeQuery("show databases");
					String dbName;
					while (resultDB.next()) {
						dbName = resultDB.getString(1);
						// Only get information inside the "moviedb" database
						if (dbName.compareTo("moviedb")==0) {
							Statement moviedbStatement = conn.createStatement();
							moviedbStatement.execute("use moviedb");
							// Create and execute an SQL statement to get all the table names in moviedb
							Statement tableStatement = conn.createStatement();
							ResultSet resultTB = tableStatement.executeQuery("show tables");
							// XXXXXXXXXXX
							String tb1Name;
							ResultSet columnData;
							Statement columnStatement;
							while (resultTB.next()) {
								tb1Name = resultTB.getString(1);
								System.out.println("\n**Table Name:** " + tb1Name + "\n");
								System.out.println("Metadata about columns in this table:\n");
								System.out.println("==== Field Name ==== Field Type ===== Null Allowed ?");
								System.out.println("----------------------------------------------------");
								columnStatement = conn.createStatement();
								// Create and execute an SQL statement to get all the column names for this table
								columnData = columnStatement.executeQuery("describe "+tb1Name);
								while (columnData.next()) {
									System.out.print("==== "+columnData.getString(1));
									System.out.print("==== "+columnData.getString(2));
									System.out.println("==== "+columnData.getString(3));
								}
							}
						}
					}
					// Displaying user privileges
					System.out.println("\n**Users and their respective privileges:** " + "\n");
					ResultSet userprivRS = stmt.executeQuery("select host, user from mysql.user");
					ResultSetMetaData rsmdUserPriv = userprivRS.getMetaData();
					for (int i = 1; i <= rsmdUserPriv.getColumnCount(); i++) {
						System.out.print("==== "+rsmdUserPriv.getColumnName(i));
					}
					System.out.println();
					System.out.println("------------------------------------------------------------------------------");
					while (userprivRS.next()) {
						String hostString = userprivRS.getString(1);
						String userString = userprivRS.getString(2);
						Statement stmt2 = conn.createStatement();
						ResultSet rs2 = stmt2.executeQuery("show grants for '"+userString+"'@'"+hostString+"'");
						ResultSetMetaData rsmd2 = rs2.getMetaData();
						for (int i = 1; i <= rsmd2.getColumnCount(); i++) {
							System.out.print("====== "+rsmd2.getColumnName(i)+" ======");
						while (rs2.next()) {
							String grants = rs2.getString(1);
							System.out.println("\n"+ grants);
						}
					}
					System.out.println();
				}
				}
				else if (selection == 'c') {
					Boolean singleName = Prompt.forBoolean("Does he/she have a single name?");
					if (singleName == true) {
						int id = Prompt.forInt("Enter customer ID");
						String firstName = "";
						String lastName = Prompt.forString("Enter name");
						String cc_id = Prompt.forString("Enter credit card number");
						String address = Prompt.forString("Enter address");
						String email = Prompt.forString("Enter email");
						String password1 = Prompt.forString("Set a password");

						// Checking with credit card data table
						rs = stmt.executeQuery("SELECT c.id FROM creditcards as c");
						while (rs.next()) {
							String credID = rs.getString("id");
							if (credID.equals(cc_id)) {
								int insert = stmt.executeUpdate("INSERT INTO customers (id, first_name, last_name, cc_id, address, email, password) " +
										"VALUES('"+ id +"', '"+ firstName +"','"+
										lastName +"','"+ cc_id + "','"+ address +"','"+ email +"','"+ password1+"');");
								System.out.println("1 row added to customers table.");
								break;
							}

						}
					}
					else
					{
						int id = Prompt.forInt("Enter customer ID");
						String firstName = Prompt.forString("Enter first name");
						String lastName = Prompt.forString("Enter last name");
						String cc_id = Prompt.forString("Enter credit card number");
						String address = Prompt.forString("Enter address");
						String email = Prompt.forString("Enter email");
						String password2 = Prompt.forString("Set a password");
						// Checking with credit card data table
						rs = stmt.executeQuery("SELECT c.id FROM creditcards as c");
						while (rs.next()) {
							String credID = rs.getString("id");
							if (credID.equals((String)cc_id)) {
								int insert = stmt.executeUpdate("INSERT INTO customers (id, first_name, last_name, cc_id, address, email, password) " +
										"VALUES('"+ id +"', '"+ firstName +"','"+
										lastName +"','"+ cc_id + "','"+ address +"','"+ email +"','"+ password2+"');");
								System.out.println("1 row added to customers table.");
								System.out.println();
								break;
							}

						}
					}
				}
				else if (selection == 'd') {
					int id = Prompt.forInt("Enter ID number for customer removal");
					int delete= stmt.executeUpdate("delete from customers WHERE id = " + id);
					System.out.println("1 row affected");
				}
				else if (selection == 'e') {
					System.out.println("Before you begin, please ensure that you have reviewed the database and user access information by pressing 'o'.");
					System.out.println("Please do so if you have not already.");
					System.out.println();
					String edit = Prompt.forString("Will you be granting/revoking existing user(s) more privileges? (Y/N)");
					if (edit.equals("Y") || edit.equals("y")) {
						System.out.println("Look to upper left for auxilary window for grant/revoke options");
						final JFrame grantFrame = new JFrame("Grant/Revoke User Privleges");
						grantFrame.setVisible(true);
						grantFrame.setSize(300,100);
						final JPanel panel = new JPanel();
						grantFrame.add(panel);
						final JTextField userName = new JTextField("Enter user name");
						panel.add(userName);
						final JTextField hostName = new JTextField("Enter host name");
						panel.add(hostName);
						JButton submit = new JButton("Submit");
						panel.add(submit);
						submit.addActionListener(new ActionListener() {
							public void actionPerformed(ActionEvent e)
							{
								try {
									//checks if user exists
									String query = "select host, user from mysql.user where host = '"+hostName.getText()+"' and user = '"+userName.getText()+"'";
									Statement statement = conn.createStatement();
									ResultSet rs1 = statement.executeQuery(query);
									if (! rs1.next()) {
										final JPanel noUserPanel = new JPanel();
										panel.setVisible(false);
										grantFrame.add(noUserPanel);
										grantFrame.setSize(500, 200);
										noUserPanel.setVisible(true);
										noUserPanel.add(new JLabel("This information you have inputted does not match any users in the database."));
										JButton back = new JButton("Go Back to Previous Screen");
										back.addActionListener(new ActionListener() {
											public void actionPerformed (ActionEvent e) {
												noUserPanel.setVisible(false);
												panel.setVisible(true);
												grantFrame.setSize(300,100);
											}});
										noUserPanel.add(back);

									}

									else {

										final JPanel actionPanel = new JPanel();
										panel.setVisible(false);
										actionPanel.setVisible(true);
										grantFrame.add(actionPanel);
										grantFrame.setSize(510, 250);
										JLabel info2 = new JLabel("Enter the database in the following format:");
										JLabel info3 = new JLabel("database_name.table_name or database_name.*");
										JLabel info1 = new JLabel("For both forms you can grant/revoke certain privileges in the following format:");
										JLabel info4 = new JLabel("SELECT, INSERT, DELETE, CREATE, DROP, etc.");
										JLabel grant = new JLabel("This portion is for GRANTING privileges only:");
										final JTextField grantPriv = new JTextField("Privileges to GRANT");

										JLabel revoke = new JLabel("This portion is for REVOKING privileges only:");
										JLabel selectDatabase = new JLabel ("Grant/Revoke privileges on the following database:");
										final JTextField revokePriv = new JTextField("Privileges to REVOKE");
										final JTextField selectDB = new JTextField("Enter Database");
										JButton submit2 = new JButton("Submit");
										actionPanel.add(info1);
										actionPanel.add(info4);
										actionPanel.add(info2);
										actionPanel.add(info3);
										actionPanel.add(grant);
										actionPanel.add(grantPriv);
										actionPanel.add(revoke);
										actionPanel.add(revokePriv);
										actionPanel.add(selectDatabase);
										actionPanel.add(selectDB);
										actionPanel.add(submit2);
										submit2.addActionListener(new ActionListener() {
											public void actionPerformed (ActionEvent e) {
												try {
													String username = userName.getText();
													String hostname = hostName.getText();
													String grantpriv = grantPriv.getText();
													String revokepriv = revokePriv.getText();
													String selectdb = selectDB.getText();
													if ((grantpriv.equals("") && revokepriv.equals("")) && selectdb.equals("")||
															(grantpriv.equals("Privileges to GRANT") && revokepriv.equals("Privileges to REVOKE") && selectdb.equals("Enter Database"))) {
														actionPanel.setVisible(false);
														final JPanel grantRevokePanel = new JPanel();
														grantRevokePanel.setVisible(true);
														grantFrame.add(grantRevokePanel);
														grantRevokePanel.setSize(400,400);
														JLabel errorLabel = new JLabel("You failed to insert any information. Please use the back button to enter information.");

														grantRevokePanel.add(errorLabel);
														JButton back = new JButton("Go back to previous screen");

														grantRevokePanel.add(back);
														back.addActionListener(new ActionListener() {
															public void actionPerformed (ActionEvent e) {
																actionPanel.setVisible(true);
																actionPanel.setSize(800,350);

																grantRevokePanel.setVisible(false);
															}});
													}

													else if (grantpriv.equals("")|| grantpriv.equals("Privledges to GRANT")) {
														Statement stmt = conn.createStatement();
														boolean rs = stmt.execute("revoke "+revokepriv+" on "+selectdb+" from '"+username+"'@'"+hostname+"'");
														System.out.println("got to grantpriv if");
														actionPanel.setVisible(false);
														final JPanel confirmPanel = new JPanel();
														grantFrame.add(confirmPanel);
														grantFrame.setSize(500, 150);
														final JLabel confirmLabel = new JLabel("You have succesfully revoked/granted access. Please refresh the MySQL server");
														confirmPanel.add(confirmLabel);
														confirmPanel.add(new JLabel("You may now close this window."));

													}
													else if (revokepriv.equals("") || revokepriv.equals("Privledges to REVOKE")) {
														Statement stmt = conn.createStatement();
														boolean rs = stmt.execute("grant "+grantpriv+" on "+selectdb+" to '"+username+"'@'"+hostname+"'");
														System.out.println("got to revoke priv if");
														actionPanel.setVisible(false);
														final JPanel confirmPanel = new JPanel();
														grantFrame.add(confirmPanel);
														grantFrame.setSize(500, 150);
														final JLabel confirmLabel = new JLabel("You have succesfully revoked/granted access. Please refresh the MySQL server");
														confirmPanel.add(confirmLabel);
														confirmPanel.add(new JLabel("You may now close this window."));


													}


													else {
														Statement stmt = conn.createStatement();
														boolean rs = stmt.execute("grant "+grantpriv+" on "+selectdb+" to '"+username+"'@'"+hostname+"'");
														Statement stmt2 = conn.createStatement();
														boolean rs2 = stmt2.execute("revoke "+revokepriv+" on "+selectdb+" from '"+username+"'@'"+hostname+"'");
														actionPanel.setVisible(false);
														final JPanel confirmPanel = new JPanel();
														grantFrame.add(confirmPanel);
														grantFrame.setSize(500, 150);
														final JLabel confirmLabel = new JLabel("You have succesfully revoked/granted access. Please refresh the MySQL server");
														confirmPanel.add(confirmLabel);
														confirmPanel.add(new JLabel("You may now close this window."));
													}


												}
												catch (Exception e2) {
													e2.printStackTrace();

												}
											}
										});


									}
									//		        				Student s = new Student(studentFirstName.getText(), studentLastName.getText(), menuID.getText(), pin.getText());
									//		        				menuPlus.getStudentCollection().addStudent(s);
									//		        				changes = true;



								}
								catch (Exception e2) {
									e2.printStackTrace();

								}
							}

						});
					}

					String addUsers = Prompt.forString("Would you like to add a user to the MySQL database? (Y/N)");
					if (addUsers.equals("Y") || addUsers.equals("y")){
						System.out.println("Look to upper left for auxilary window to add users");
						final JFrame addFrame = new JFrame("Create MySQL user account");
						addFrame.setVisible(true);
						addFrame.setSize(300,300);
						final JPanel addpanel = new JPanel();
						addFrame.add(addpanel);
						final JTextField adduserName = new JTextField("Enter user name");
						addpanel.add(adduserName);
						final JTextField addhostName = new JTextField("Enter host name");
						addpanel.add(addhostName);
						final JTextField addpassword = new JTextField("Enter password");
						addpanel.add(addpassword);
						JButton addsubmit = new JButton("Create User");
						addpanel.add(addsubmit);
						addsubmit.addActionListener(new ActionListener() {
							public void actionPerformed(ActionEvent e) {
								try {
									Boolean duplicate = false;
									String addUserNameString = adduserName.getText();
									String addHostNameString = addhostName.getText();
									String addPasswordString = addpassword.getText();

									// Check to see if there are any users with the same account.
									String queryCheck = "select count(*) from mysql.user where user = '" +addUserNameString+ "' and host = '" +addHostNameString+ "'";
									Statement stmt00 = conn.createStatement();
									ResultSet rsCheck = stmt00.executeQuery(queryCheck);
									while (rsCheck.next()) {
										if (rsCheck.getString(1).equals("1")) {
											addpanel.setVisible(false);
											final JPanel denyPanel = new JPanel();
											addFrame.add(denyPanel);
											final JLabel confirmLabel = new JLabel("Sorry, the user you have tried to add is already in the database.");
											denyPanel.add(confirmLabel);
											duplicate = true;
											JButton back = new JButton("Go back to previous screen");
											denyPanel.add(back);
											back.addActionListener(new ActionListener() {
												public void actionPerformed (ActionEvent e) {
													addpanel.setVisible(true);


													denyPanel.setVisible(false);
												}});
										}

										}


									// Time to make a SQL query/command
									if (duplicate == false) {
									String sqlCommand = "create user '" +addUserNameString+ "' @'"+addHostNameString+"' identified by '" + addPasswordString+"'";
									Statement addStmt = conn.createStatement();
									Boolean rsAdd = addStmt.execute(sqlCommand);
									addpanel.setVisible(false);
									final JPanel confirmPanel = new JPanel();
									addFrame.add(confirmPanel);
									final JLabel confirmLabel = new JLabel("Your entry has been confirmed. Please refresh the MySQL server");
									confirmPanel.add(confirmLabel);
									}

								}
								catch (Exception e3){
									e3.printStackTrace();
								}
							}
						});
						}
					else {
						System.out.println("\nYou have selected none of the options.");
						System.out.println("\nTherefore, you will be redirected to the main menu. \n");
					}
				}
				else if (selection == 'u') {
					loggedIn = false;


					if (rs != null) {
						try {
							rs.close();
						} catch (SQLException sqlEx) {
						} // ignore

						rs = null;
					}

					if (rsType != null) {
						try {
							rsType.close();
						} catch (SQLException sqlEx) {
						} // ignore

						rsType = null;
					}


					if (stmt != null) {
						try {
							stmt.close();
						} catch (SQLException sqlEx) {
						} // ignore

						stmt = null;
					}

					if (conn != null) {
						try {
							conn.close();
						} catch (SQLException sqlEx) {
						} // ignore

						conn = null;
					}

					Class.forName("com.mysql.jdbc.Driver").newInstance();

					while(loggedIn == false) {
						System.out.println("Welcome to FabFlix! Please input user information below:");
						//String username3 = Prompt.forString("Enter username");
						//String password3 = Prompt.forString("Enter password");
						String usernameDatabase1 = "root";
						String passwordDatabase1 = "lakers";
						String employeeEmail1 = Prompt.forString("Enter email");
						String employeePassword1 = Prompt.forString("Enter password");
						String queryLogin1 = "SELECT count(*) from employees where email = '" + employeeEmail1 + "' and password = '" + employeePassword1 +"'";
						conn = DriverManager.getConnection(url, usernameDatabase1, passwordDatabase1);
						stmt = conn.createStatement();
						stmt1 = conn.createStatement();
						ResultSet rsLogin1 = stmt.executeQuery(queryLogin1);

						while(rsLogin1.next()) {
							if (rsLogin1.getString(1).equals("1")) {
								conn = DriverManager.getConnection(url, usernameDatabase1, passwordDatabase1);
								stmt = conn.createStatement();
								stmt1 = conn.createStatement();
								loggedIn = true;
								employeeEmail1 = null;
								employeePassword1 = null;
								break;
							}

							else
								System.out.println("Wrong username/password combination. Please try again.");

						}
					}


				}

				else if (selection == 'x') {
					boolean output = Prompt.forBoolean("Will this command return an output? (true or false)");
					if (output == true) {
						String select = Prompt.forString("SELECT");
						String from = Prompt.forString("FROM");
						String where = Prompt.forString("WHERE");
						if (!where.isEmpty()){
							rs = stmt.executeQuery("select " + select + " from " + from + " where " + where);
						}
						else {
							rs = stmt.executeQuery("select " + select + " from " + from);
						}

						ResultSetMetaData rsmd = rs.getMetaData();
						for (int i = 1; i <= rsmd.getColumnCount(); i++) {
							System.out.print(rsmd.getColumnName(i) + "\t");
						}
						System.out.println();
						System.out.println();
						String t = "";
						for (int i = 1; i<= rsmd.getColumnCount(); i++) {
							String s = rsmd.getColumnName(i) + ", ";
							t = t + s;
						}
						String s2 = t;
						StringTokenizer st = new StringTokenizer(s2, ", "); // To grab the "new" attributes

						while (rs.next()) {
							while (st.hasMoreTokens()) {
								Object o = rs.getObject(st.nextToken());
								System.out.print(o + "\t");
							}
							st = new StringTokenizer(s2, ", ");
							System.out.println();
						}
					}
					else {
						String option = Prompt.forString("Enter update, insert, or delete");
						if (option.equals("update")) {
							String update = Prompt.forString("UPDATE");
							String set = Prompt.forString("SET");
							String where = Prompt.forString("WHERE");
							int updateExecute = stmt.executeUpdate("update " + update + " set " + set + " where " + where);
							System.out.println("The information has been updated in the " + update+ " table");
						}
						else if (option.equals("insert")) {
							String insertInto = Prompt.forString("INSERT INTO:");
							rsType = stmt1.executeQuery("desc "+ insertInto);
							String attributes ="(";
							while(rsType.next()) {
								String field = rsType.getString("field");
								if (rsType.isLast())
									attributes = attributes + field;
								else
									attributes = attributes + field + ", ";

							}
							String values = Prompt.forString("VALUES:");
							int insertExecute = stmt.executeUpdate("insert into "+ insertInto+ attributes+")"+ " values" + values);
							System.out.println("The information has been inserted into the " + insertInto+ " table");
						}

						else if (option.equals("delete")) {
							String deleteFrom = Prompt.forString("DELETE FROM");
							String where = Prompt.forString("WHERE");
							int deleteExecute = stmt.executeUpdate("delete from " + deleteFrom + " where " + where);
							System.out.println("The information inputted has deleted the corresponding records");
						}
						else
							System.out.println("Error: You have entered a nonvalid option");
					}
				}


				else if (selection == 'm') {
					// Print Movie Metadata
					System.out.println("Movies :");
					rs = stmt.executeQuery("SELECT * FROM movies");
					rsType = stmt1.executeQuery("desc movies"); // Data table about movie table
					ResultSetMetaData rsmd = rs.getMetaData();
					for (int i = 1; i <= rsmd.getColumnCount(); i++) {
						System.out.print(rsmd.getColumnName(i));
						if (rsType.next()) {
							String type = rsType.getString("type");
							System.out.println( " "+ "("+ type +")" + "\t");
						}
					}
					System.out.println();

					// Print Stars Metadata
					System.out.println("Stars :");
					rs = stmt.executeQuery("SELECT * FROM stars");
					rsType = stmt1.executeQuery("desc stars");
					ResultSetMetaData rsmd1 = rs.getMetaData();
					for (int i = 1; i <= rsmd1.getColumnCount(); i++) {
						System.out.print(rsmd1.getColumnName(i));
						if (rsType.next()) {
							String type = rsType.getString("type");
							System.out.println( " "+ "("+ type +")" + "\t");
						}
					}
					System.out.println();

					// Print stars_in_movies metadata
					System.out.println("stars_in_movies");
					rs = stmt.executeQuery("SELECT * FROM stars_in_movies");
					rsType = stmt1.executeQuery("desc stars_in_movies");
					ResultSetMetaData rsmd2 = rs.getMetaData();
					for (int i = 1; i <= rsmd2.getColumnCount(); i++) {
						System.out.print(rsmd2.getColumnName(i));
						if (rsType.next()) {
							String type = rsType.getString("type");
							System.out.println( " "+ "("+ type +")" + "\t");
						}
					}
					System.out.println();

					// Print genres metadata
					System.out.println("genres");
					rs = stmt.executeQuery("SELECT * FROM genres");
					rsType = stmt1.executeQuery("desc genres");
					ResultSetMetaData rsmd3 = rs.getMetaData();
					for (int i = 1; i <= rsmd3.getColumnCount(); i++) {
						System.out.print(rsmd3.getColumnName(i));
						if (rsType.next()) {
							String type = rsType.getString("type");
							System.out.println( " "+ "("+ type +")" + "\t");
						}
					}
					System.out.println();

					// Print genres_in_movies metadata
					System.out.println("genres_in_movies");
					rs = stmt.executeQuery("SELECT * FROM genres_in_movies");
					rsType = stmt1.executeQuery("desc genres_in_movies");
					ResultSetMetaData rsmd4 = rs.getMetaData();
					for (int i = 1; i <= rsmd4.getColumnCount(); i++) {
						System.out.print(rsmd4.getColumnName(i));
						if (rsType.next()) {
							String type = rsType.getString("type");
							System.out.println( " "+ "("+ type +")" + "\t");
						}
					}
					System.out.println();

					// Print customers metadata
					System.out.println("customers");
					rs = stmt.executeQuery("SELECT * FROM customers");
					rsType = stmt1.executeQuery("desc customers");
					ResultSetMetaData rsmd5 = rs.getMetaData();
					for (int i = 1; i <= rsmd5.getColumnCount(); i++) {
						System.out.print(rsmd5.getColumnName(i));
						if (rsType.next()) {
							String type = rsType.getString("type");
							System.out.println( " "+ "("+ type +")" + "\t");
						}
					}
					System.out.println();

					// Print sales metadata
					System.out.println("sales");
					rs = stmt.executeQuery("SELECT * FROM sales");
					rsType = stmt1.executeQuery("desc sales");
					ResultSetMetaData rsmd6 = rs.getMetaData();
					for (int i = 1; i <= rsmd6.getColumnCount(); i++) {
						System.out.print(rsmd6.getColumnName(i));
						if (rsType.next()) {
							String type = rsType.getString("type");
							System.out.println( " "+ "("+ type +")" + "\t");
						}
					}
					System.out.println();

					// Print creditcards metadata
					System.out.println("creditcards");
					rs = stmt.executeQuery("SELECT * FROM creditcards");
					rsType = stmt1.executeQuery("desc creditcards");
					ResultSetMetaData rsmd7 = rs.getMetaData();
					for (int i = 1; i <= rsmd7.getColumnCount(); i++) {
						System.out.print(rsmd7.getColumnName(i));
						if (rsType.next()) {
							String type = rsType.getString("type");
							System.out.println( " "+ "("+ type +")" + "\t");
						}
					}
					System.out.println();
				}

				else if (selection == 'z') {
					break;
				}
				else if (selection == 'k') {
					try{

						// Create file

						FileWriter fstream = new FileWriter("Error Report.html");
						BufferedWriter out = new BufferedWriter(fstream);
						out.write("<HTML> <HEAD> <TITLE>FabFlix Error Report</TITLE> </HEAD> <center> <BODY BGCOLOR=\"#FDF5E6\"> <H1 ALIGN=\"CENTER\">FabFlix Error Report</H1>");

						//Query 1 is to find movies with no stars
						String query1 = "SELECT m.id, m.title FROM movies as m LEFT JOIN stars_in_movies as s ON s.movie_id = m.id WHERE s.movie_id IS NULL";

						Statement statementq1 = conn.createStatement();
						ResultSet rsq1 = statementq1.executeQuery(query1);
						out.write("The following movie IDs and titles have no stars associated with them:");
						out.write("<TABLE border>");
						out.write("<tr> +" +
								"<td> Number </td>" +
								"<td> ID </td>" +
								"<td> Title </td> " +
								"</tr>" +
						"");

						while (rsq1.next()) {
							String id = rsq1.getString("id");
							String title = rsq1.getString("title");
							out.write("<tr>" +
									"<td>" + rsq1.getRow() + "</td>" +
									"<td>" + id + "</td>" +
									"<td>" + title + "</td>" +
							"</tr>");
						}

						out.write("</TABLE>");
						//Query 2 is to find stars without movies
						String query2 = "SELECT s.id, s.first_name, s.last_name FROM stars as s LEFT JOIN stars_in_movies as sm ON s.id = sm.star_id WHERE sm.star_id IS NULL";
						Statement statementq2 = conn.createStatement();
						ResultSet rsq2 = statementq2.executeQuery(query2);
						out.write("<br> The following star IDs and names have no movies associated with them:");
						out.write("<TABLE border>");
						out.write("<tr> +" +
								"<td> Number </td>" +
								"<td> ID </td>" +
								"<td> First Name </td> " +
								"<td> Last Name </td> " +
						"</tr>");

						while (rsq2.next()) {
							String id = rsq2.getString("id");
							String firstName = rsq2.getString("first_name");
							String lastName = rsq2.getString("last_name");
							out.write("<tr>" +
									"<td>" + rsq2.getRow() + "</td>" +
									"<td>" + id + "</td>" +
									"<td>" + firstName + "</td>" +
									"<td>" + lastName + "</td>" +
							"</tr>");
						}

						out.write("</table>");
						//Query 3 is to find genres without movies
						String query3 = "SELECT g.id, g.name FROM genres as g LEFT JOIN genres_in_movies as gm ON gm.genre_id = g.id WHERE gm.genre_id IS NULL";
						Statement statementq3 = conn.createStatement();
						ResultSet rsq3 = statementq3.executeQuery(query3);
						out.write("<br> The following genre IDs and names have no movies associated with them:");
						out.write("<TABLE border>");
						out.write("<tr> +" +
								"<td> Number </td>" +
								"<td> ID </td>" +
								"<td> Genre Name </td> " +
						"</tr>");

						System.out.println();
						while (rsq3.next()) {
							String id = rsq3.getString("id");
							String name = rsq3.getString("name");
							out.write("<tr>" +
									"<td>" + rsq3.getRow() + "</td>" +
									"<td>" + id + "</td>" +
									"<td>" + name + "</td>" +
							"</tr>");
						}
						out.write("</table>");

						//Query4 is to find stars with either no last name or no first name or both.
						String query4 = "SELECT s.id, s.first_name, s.last_name FROM stars as s WHERE (s.last_name ='' or s.first_name = '')";
						Statement statementq4 = conn.createStatement();
						ResultSet rsq4 = statementq4.executeQuery(query4);
						out.write("<br> The following star IDs have no first name or last name associated with them");
						out.write("<TABLE border>");
						out.write("<tr> +" +
								"<td> Number </td>" +
								"<td> ID </td>" +
						"</tr>");

						while (rsq4.next()) {
							String id = rsq4.getString("id");
							out.write("<tr>" +
									"<td>" + rsq3.getRow() + "</td>" +
									"<td>" + id + "</td>" +
							"</tr>");
						}
						out.write("</table>");
						//Query 5 will all the expired creditcards of existing customers
						String query5 = "select c.first_name, c.last_name, cc.id, cc.expiration from customers as c, creditcards as cc where cc.first_name = c.first_name and cc.last_name = c.last_name and cc.expiration <'2011-01-01'";
						Statement statementq5 = conn.createStatement();
						ResultSet rsq5 = statementq4.executeQuery(query5);
						out.write("<br> The following customers have creditcards that have expired:");
						out.write("<TABLE border>");
						out.write("<tr> +" +
								"<td> Number </td>" +
								"<td> First Name </td> " +
								"<td> Last Name </td> " +
								"<td> ID </td>" +
								"<td> Expiration Date </td>" +
						"</tr>");

						System.out.println();
						while (rsq5.next()) {
							String firstName = rsq5.getString("first_name");
							String lastName = rsq5.getString("last_name");
							String ccNum = rsq5.getString("id");
							String exp = rsq5.getString("expiration");
							out.write("<tr>" +
									"<td>" + rsq5.getRow() + "</td>" +
									"<td>" + firstName + "</td>" +
									"<td>" + lastName + "</td>" +
									"<td>" + ccNum + "</td>" +
									"<td>" + exp + "</td>" +
							"</tr>");
						}

						out.write("</table>");
						//Query 6 will find all the duplicate movies
						String query6 = " select m2.id, m.id, m.title, m.year from movies as m, movies as m2 where m.year=m2.year and m.title=m2.title and m.id !=m2.id";
						Statement statement6 = conn.createStatement();
						ResultSet rs6 = statement6.executeQuery(query6);
						out.write("<br> The following shows duplicate movies along with their duplicate movie IDs:");
						out.write("<TABLE border>");
						out.write("<tr> +" +
								"<td> Number </td>" +
								"<td> Original ID </td>" +
								"<td> Duplicate ID</td> " +
								"<td> Movie Title </td> " +
								"<td> Year </td>" +
						"</tr>");
						System.out.println("");
						while (rs6.next()) {
							String id1 = rs6.getString(1);
							String id2 = rs6.getString(2);
							String title = rs6.getString(3);
							String year = rs6.getString(4);
							out.write("<tr>" +
									"<td>" + rs6.getRow() + "</td>" +
									"<td>" + id1 + "</td>" +
									"<td>" + id2 + "</td>" +
									"<td>" + title + "</td>" +
									"<td>" + year + "</td>" +
							"</tr>");
						}

						out.write("</table>");
						//Query 7 will return duplicate stars
						String query7 = " select s2.id, s.id, s.first_name, s.last_name, s.dob from stars as s, stars as s2 where s.first_name=s2.first_name and s.last_name=s2.last_name and s.dob = s2.dob and s.id !=s2.id";
						Statement statement7 = conn.createStatement();
						ResultSet rs7 = statement7.executeQuery(query7);
						out.write("<br> The following shows duplicate stars and their duplicate star IDs:");
						out.write("<TABLE border>");
						out.write("<tr> +" +
								"<td> Number </td>" +
								"<td> Original ID </td>" +
								"<td> Duplicate ID </td> " +
								"<td> First Name </td>" +
								"<td> Last Name </td> " +
								"<td> DOB </td>"+
						"</tr>");

						while (rs7.next()) {
							String id1 = rs7.getString(1);
							String id2 = rs7.getString(2);
							String firstname = rs7.getString(3);
							String lastname = rs7.getString(4);
							String dob = rs7.getString(5);

							out.write("<tr>" +
									"<td>" + rs7.getRow() + "</td>" +
									"<td>" + id1 + "</td>" +
									"<td>" + id2 + "</td>" +
									"<td>" + firstname + "</td>" +
									"<td>" + lastname + "</td>" +
									"<td>" + dob + "</td>" +
							"</tr>");

						}

						out.write("</table>");
						//Query 9 will return stars with birth dates greater than today's date or less than the year 1900
						String query9 = "select dob, id, first_name, last_name from stars where (dob < '1900-12-31' or dob > '2011-02-10')";
						Statement statement9 = conn.createStatement();
						ResultSet rsq9 = statement9.executeQuery(query9);
						out.write("<br> The following displays stars with a DOB greater than today's date or less than the year 1900:");
						out.write("<TABLE border>");
						out.write("<tr> +" +
								"<td> Number </td>" +
								"<td> DOB </td>" +
								"<td> Star ID </td>" +
								"<td> First Name </td> " +
								"<td> Last Name </td> " +
						"</tr>");


						while (rsq9.next()) {
							String dob = rsq9.getString(1);
							String id = rsq9.getString(2);
							String firstName = rsq9.getString(3);
							String lastName = rsq9.getString(4);
							out.write("<tr>" +
									"<td>" + rsq9.getRow() + "</td>" +
									"<td>" + dob + "</td>" +
									"<td>" + id + "</td>" +
									"<td>" + firstName + "</td>" +
									"<td>" + lastName + "</td>" +
							"</tr>");

						}

						out.write("</table>");

						//Query 8 will return duplicate genres
						String query8 = "select m2.id, m.id, m.name from genres as m, genres as m2 where m.name=m2.name and m.id !=m2.id";
						Statement statementq8 = conn.createStatement();
						ResultSet rsq8 = statementq3.executeQuery(query8);
						out.write("<br> The following displays duplicate genres and their duplicate IDs:");
						out.write("<TABLE border>");
						out.write("<tr> +" +
								"<td> Number </td>" +
								"<td> Original ID </td>" +
								"<td> Duplicate ID </td>" +
								"<td> Genre Name </td> " +
						"</tr>");


						while (rsq8.next()) {
							String id1 = rsq8.getString(1);
							String id2 = rsq8.getString(2);
							String name = rsq8.getString(3);
							out.write("<tr>" +
									"<td>" + rsq8.getRow() + "</td>" +
									"<td>" + id1 + "</td>" +
									"<td>" + id2 + "</td>" +
									"<td>" + name + "</td>" +
							"</tr>");

						}

						out.write("</table>");
						out.write(" </CENTER> </FORM> </BODY> </HTML>");
						System.out.println("You can find the Error Report in HTML format in the source folder of this project.");
						System.out.println("The file name is Error Report.html.");

					}catch (Exception e){//Catch exception if any

						System.err.println("Error: " + e.getMessage());

					}

				}
				else
					System.out.println(selection + " is an unknown command") ;

			} }

		catch (Exception e) {
			e.printStackTrace();

		}

	}}