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("  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","pscdmxuz");
	  }


	@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 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:");
			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.");



			}
		}

		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 == '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 genre_in_movies");
				rsType = stmt1.executeQuery("desc genre_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();

		}

//		finally NICE! {
//
//			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;
//			}
//		}

	}
}