package JDBCProject1;

import java.sql.*;
import edu.uci.ics.pattis.introlib.*;
import java.util.*;

public class JDBCProject1 {

	//Present menu and get user's choice

	  @SuppressWarnings("restriction")
	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 - Insert a movie into database");
	    System.out.println("  m - Provide metadata of database");
	    System.out.println("  x - Provide a SQL command");
	    System.out.println("  u - Exit the menu");
	    System.out.println("  z - Exit the program");

	    return Prompt.forChar("\nEnter Command","pscdtmxuz");
	  }


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

	public static void main(String[] args) throws Exception {
		Connection conn = null;
		Statement stmt = null;
		Statement stmt1 = null;
		boolean loggedIn = false;
		ResultSet rs = null;
		ResultSet rsType = null;
		String username;
		String password;

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


		Class.forName("com.mysql.jdbc.Driver").newInstance();
		if (loggedIn == false) {
		for(;;) {
			System.out.println("Welcome to FabFlix! Please input user information below:");
			 username = Prompt.forString("Enter username");
			 password = Prompt.forString("Enter password");

			if (username.equals("root") && password.equals("lakers")) {
			conn = DriverManager.getConnection(url, username, password);
			stmt = conn.createStatement();
			stmt1 = conn.createStatement();
			loggedIn = true;
			username = null;
			password = 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 == '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 password = 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 +"','"+ password+"');");
							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 password = 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 +"','"+ password+"');");
							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 == '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();

					for(;;) {
						System.out.println("Welcome to FabFlix! Please input user information below:");
						String username = Prompt.forString("Enter username");
						String password = Prompt.forString("Enter password");

						if (username.equals("root") && password.equals("lakers")) {
						conn = DriverManager.getConnection(url, username, password);
						stmt = conn.createStatement();
						stmt1 = conn.createStatement();
						loggedIn = true;
						username = null;
						password = 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
				System.out.println(selection + " is an unknown command") ;

		} }

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

		}
	}
}