EmbeddedSQL.java

/*
 * MODIFIED BY JUSTIN KAHAL AND THOMAS DESMOND
 * SSID:860892022
 *
 * Template JAVA User Interface
 * =============================
 *
 * Database Management Systems
 * Department of Computer Science & Engineering
 * University of California - Riverside
 *
 * Target DBMS: 'Postgres'
 *
 */


importjava.sql.DriverManager;
importjava.sql.Connection;
importjava.sql.Statement;
importjava.sql.ResultSet;
importjava.sql.ResultSetMetaData;
importjava.sql.SQLException;
importjava.io.File;
importjava.io.FileReader;
importjava.io.BufferedReader;
importjava.io.InputStreamReader;


///////////////////////////////////////////////////////
////////////////////////////////////////////////////
/////////	ADD THIS IMPORT ////////////////
importjava.util.Scanner;

/**
 * This class defines a simple embedded SQL utility class that is designed to
 * work with PostgreSQL JDBC drivers.
 *
 */
publicclassEmbeddedSQL {


publicstaticStringcurrentUser;
publicstaticbooleansuperUser = false;
// reference to physical database connection.
privateConnection_connection = null;

// handling the keyboard inputs through a BufferedReader
// This variable can be global for convenience.
staticBufferedReaderin = newBufferedReader(
newInputStreamReader(System.in));

/**
 * Creates a new instance of EmbeddedSQL
 *
 * @param hostname the MySQL or PostgreSQL server hostname
 * @param database the name of the database
 * @param username the user name used to login to the database
 * @param password the user login password
 * @throws java.sql.SQLException when failed to make a connection.
 */
publicEmbeddedSQL (Stringdbname, Stringdbport, Stringuser, Stringpasswd) throwsSQLException {

System.out.print("Connecting to database...");
try{
// constructs the connection URL
Stringurl = "jdbc:postgresql://localhost:" + dbport + "/" + dbname;
System.out.println ("Connection URL: " + url + "\n");

// obtain a physical connection
this._connection = DriverManager.getConnection(url, user, passwd);
System.out.println("Done");
 }catch (Exceptione){
System.err.println("Error - Unable to Connect to Database: " + e.getMessage() );
System.out.println("Make sure you started postgres on this machine");
System.exit(-1);
 }//end catch
 }//end EmbeddedSQL

/**
 * Method to execute an update SQL statement. Update SQL instructions
 * includes CREATE, INSERT, UPDATE, DELETE, and DROP.
 *
 * @param sql the input SQL string
 * @throws java.sql.SQLException when update failed
 */
publicvoidexecuteUpdate (Stringsql) throwsSQLException {
// creates a statement object
Statementstmt = this._connection.createStatement ();

// issues the update instruction
stmt.executeUpdate (sql);

// close the instruction
stmt.close ();
 }//end execute update 

intgetVidId(Stringquery)
 {
try{
// creates a statement objectv
Statementstmt = this._connection.createStatement (); 

// issues the query instruction
ResultSetrs = stmt.executeQuery(query);

while(rs.next())
 {
intvid_id = Integer.parseInt(rs.getString(1));
returnvid_id;
 }
return -1;
 }catch(Exceptione){
System.err.println (e.getMessage ());} 
return -1;
	}//end getVidID

publicintexecuteOrder (Stringquery, intprice) throwsSQLException
 {
// creates a statement objectv
Statementstmt = this._connection.createStatement ();

// issues the query instruction
ResultSetrs = stmt.executeQuery(query);

while(rs.next())
 {
intbalance = Integer.parseInt(rs.getString(1));
if(balance - price >= 0)
 {
System.out.println("You would have negative balance if movie is ordered you cannot order movie"); 
return -1;
 }
else
 {
Stringupdatebal = "UPDATE users SET balance = balance - " + price + " WHERE user_id = '" + currentUser + "'";
stmt.executeUpdate(updatebal); //I changed this part might be problematic
Stringnewbalance = "SELECT balance FROM users WHERE user_id='" + currentUser + "'";
System.out.print("New Balance if ordered: ");
executeStringQuery(newbalance, 1);
return1;
 }	
 }
return -1;
	}

publicvoidexecuteStringQuery (Stringquery, intline) throwsSQLException
	{
// creates a statement objectv
Statementstmt = this._connection.createStatement ();

// issues the query instruction
ResultSetrs = stmt.executeQuery (query);

/*
 ** obtains the metadata object for the returned result set. The metadata
 ** contains row and column info.
 */
System.out.println("--------------");
while(rs.next())
 {
System.out.println(rs.getString(line));
 }
	}

publicintexecuteCheckTitle (Stringquery) throwsSQLException
	{
try{
// creates a statement objectv
Statementstmt = this._connection.createStatement ();
System.out.println("Connection Success");

// issues the query instruction
ResultSetrs = stmt.executeQuery(query);
System.out.println("Query exectued success");

System.out.println("--------------");

while(rs.next())
 {
System.out.print("Are you sure you would like to order (yes or no): ");
System.out.println(rs.getString(2) + " Price: " + rs.getString(3));
Stringinput = in.readLine();
System.out.println();
 }

returnInteger.parseInt(rs.getString(3));
 }catch(Exceptione){
System.err.println (e.getMessage ());}
return -1;
	}

publicintexecuteLoginQuery (Stringquery) throwsSQLException
	{
// creates a statement objectv
Statementstmt = this._connection.createStatement ();

// issues the query instruction
ResultSetrs = stmt.executeQuery (query);

/*
 ** obtains the metadata object for the returned result set. The metadata
 ** contains row and column info.
 */
ResultSetMetaDatarsmd = rs.getMetaData ();
intnumCol = rsmd.getColumnCount ();
rs.next();
intresultset = Integer.parseInt(rs.getString(numCol));
System.out.println(resultset);

stmt.close();
returnresultset;
// iterates through the result set and output them to standard out.
/* boolean outputHeader = true;
 while (rs.next()){
 if(outputHeader){
 for(int i = 1; i <= numCol; i++){
 System.out.print(rsmd.getColumnName(i) + "\t");
 }
 System.out.println();
 outputHeader = false;
 }
 for (int i=1; i<=numCol; ++i)
 System.out.print (rs.getString (i) + "\t");
 System.out.println ();
 ++rowCount;
 }//end while
 stmt.close ();
 return rowCount;
 */
 }

/**
 * Method to execute an input query SQL instruction (i.e. SELECT). This
 * method issues the query to the DBMS and outputs the results to
 * standard out.
 *
 * @param query the input query string
 * @return the number of rows returned
 * @throws java.sql.SQLException when failed to execute the query
 */
publicintexecuteQuery (Stringquery) throwsSQLException {
// creates a statement objectv
Statementstmt = this._connection.createStatement ();

// issues the query instruction
ResultSetrs = stmt.executeQuery (query);

/*
 ** obtains the metadata object for the returned result set. The metadata
 ** contains row and column info.
 */
ResultSetMetaDatarsmd = rs.getMetaData ();
intnumCol = rsmd.getColumnCount ();
introwCount = 0;

// iterates through the result set and output them to standard out.
booleanoutputHeader = true;
while (rs.next()){
if(outputHeader){
for(inti = 1; i <= numCol; i++){
System.out.print(rsmd.getColumnName(i) + "\t");
 }
System.out.println();
outputHeader = false;
 }
for (inti=1; i<=numCol; ++i)
System.out.print (rs.getString (i) + "\t");
System.out.println ();
 ++rowCount;
 }//end while
stmt.close ();
returnrowCount;
 }//end executeQuery

/**
 * Method to close the physical connection if it is open.
 */
publicvoidcleanup(){
try{
if (this._connection != null){
this._connection.close ();
 }//end if
 }catch (SQLExceptione){
// ignored.
 }//end try
 }//end cleanup

/**
 * The main execution method
 *
 * @param args the command line arguments this inclues the <mysql|pgsql> <login file>
 */
publicstaticvoidmain (String[] args) {
if (args.length != 4) {
System.err.println (
"Usage: " +
"java [-classpath <classpath>] " +
EmbeddedSQL.class.getName () +
" <dbname> <port> <user> <passwd>");
return;
 }//end if

Greeting();
EmbeddedSQLesql = null;
try{
// use postgres JDBC driver.
Class.forName ("org.postgresql.Driver").newInstance ();
// instantiate the EmbeddedSQL object and creates a physical
// connection.
Stringdbname = args[0];
Stringdbport = args[1];
Stringuser = args[2];
Stringpasswd = args[3];
esql = newEmbeddedSQL (dbname, dbport, user, passwd);

booleankeepon = true;
while(keepon) {
// These are sample SQL statements
System.out.println("WELCOME! Are you an existing user?");
System.out.println("---------");
System.out.println("0. Yes I am!");
System.out.println("1. No, I need to register!");
System.out.println("9. < EXIT (Stop the program)");


switch (readChoice()){
case0: if(LogInQuery(esql) == 0 ) keepon = false; break;
case1: if(RegisterQuery(esql) == 0 ) keepon = false; break;
case9: return;
default : System.out.println("Unrecognized choice!"); break;
 }//end switch
 }//end while 

//Will need to turn keepon to false after logging in 

booleanhomescreen = true; 
while(homescreen)
 {
System.out.println("---------");
System.out.println("Welcome to MovieNet");
System.out.println("---------");
System.out.println("0. See list of movies");
System.out.println("1. Order a movie");
System.out.println("2. Rate a movie NOT WORKING");
System.out.println("3. Look at wall NOT WORKING");
System.out.println("4. Add to balance");
System.out.println("5. List favorite movie");
System.out.println("6. Add a favorite movie");

//these will only be visible if you are super user
if( superUser ){
System.out.println("\nSuper User Options:");
System.out.println("10. Register a new movie WORKING");
System.out.println("11. Delete an existing user NOT WORKING");
 }

/////////////////////////////////////////////////////////////CHANGES//////////
if( !superUser ) System.out.println("8. Login as SuperUser");
/////////////////////////////////////////////////////////////CHANGES//////////
System.out.println("9. < EXIT (Stop the program)");

switch (readChoice()){
case0: ListMovies(esql); break;
case1: OrderMovie(esql); break;
case2: break;
case3: break;
case4: IncreaseBalance(esql); break;
case5: ListFavs(esql); break;
case6: InsertFav(esql); break;
/////////////////////////////////////////////////////////////CHANGES//////////
case8: if( !superUser ) {LoginAsSuper(esql);}; break;
/////////////////////////////////////////////////////////////CHANGES//////////

case9: return;
case10: if( superUser ) {RegisterMovie(esql);}; break;
case11: if( superUser ) {deleteUser(esql);}; break;
default : System.out.println("Unrecognized choice!"); break;
 }


 }


 }catch(Exceptione) {
System.err.println (e.getMessage ());
 }finally{
// make sure to cleanup the created table and close the connection.
try{
if(esql != null) {
System.out.print("Disconnecting from database...");
esql.cleanup ();
System.out.println("Done\n\nBye !");
 }//end if
 }catch (Exceptione) {
// ignored.
 }//end try
 }//end try
 }//end main



publicstaticvoidListMovies(EmbeddedSQLesql)
	{
try{
Stringquery = "SELECT title FROM Video";
esql.executeStringQuery(query, 1);

 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
	}

publicstaticvoidListFavs(EmbeddedSQLesql)
	{
try{
Stringquery = "SELECT title FROM video, likes WHERE likes.user_id = '" + currentUser + 
"' AND likes.video_id = video.video_id";
esql.executeStringQuery(query, 1);
 }catch(Exceptione){
System.err.println (e.getMessage ());}
	}

publicstaticvoidInsertFav(EmbeddedSQLesql)
	{
try{
System.out.print("Enter title of move you would like to add as favorite: ");
Stringtitle = in.readLine();
Stringquery = "SELECT video_id FROM video WHERE title = '" + title + "'";

intvid_id = esql.getVidId(query);

System.out.println("GOT THE VID ID");
query = "INSERT INTO likes (user_id, video_id) VALUES ('" + currentUser + "', " + vid_id + ")";
esql.executeUpdate(query);

System.out.println("New favorite added");

 }catch(Exceptione){
System.err.println (e.getMessage ());} 
	}

publicstaticvoidOrderMovie(EmbeddedSQLesql)
	{
try{ 
System.out.print("Title of movie you would like to order: ");
Stringtitle = in.readLine();

//Grabbing title and will print it
Stringquery = "SELECT * FROM video WHERE title = ";
query += title + "'";

intprice = esql.executeCheckTitle(query);

query = "SELECT balance FROM users WHERE user_id ='" + currentUser + "';";
inttemp = esql.executeOrder(query, price);

if( temp == 1 )
System.out.println("Successfully ordered: " + title);
 }catch(Exceptione){
System.err.println (e.getMessage ());}
	}


publicstaticvoidIncreaseBalance(EmbeddedSQLesql)
	{
try{

System.out.print("How much would you like to add to balance?: ");
Stringstringbal = in.readLine();
intintbal = Integer.parseInt(stringbal);

if(intbal <= 0)
 {
System.out.println("Must input number greater than $0");
return;
 }

System.out.println("About to update");


Stringquery = "UPDATE users SET balance = balance +" + intbal + " WHERE user_id = '"
 + currentUser + "'";

esql.executeUpdate(query);

System.out.println("UPDATED");

Stringnewbalance = "SELECT balance FROM users WHERE user_id='" + currentUser + "'";
System.out.print("New Balance after addition: ");
esql.executeStringQuery(newbalance, 1); 

return;

 }catch(Exceptione){	
System.err.println (e.getMessage ());}
	}

// NOT FULLY IMPLEMENTED NEED TO GRAB VIDEO ID AND THEN RATE MOVIE
publicstaticvoidRateMovie(EmbeddedSQLesql)
	{
try{

System.out.print("Title of movie you would like to rate: ");
Stringtitle = in.readLine();
System.out.print("What would you like to rate the movie (0 - 10): ");
Stringrating = in.readLine();

Stringquery = "SELECT title FROM Video";

//String output = esql.executeStringQuery(query); //Cant use executeString This way!!!!

 }catch(Exceptione){
System.err.println (e.getMessage ());}
	}

publicstaticvoidGreeting(){
System.out.println(
"\n\n*******************************************************\n" +
" User Interface \n" +
"*******************************************************\n");
 }//end Greeting

publicstaticintLoginAsSuper(EmbeddedSQLesql)
 {
try{
System.out.println("Verifying that you are a Super User");
Stringquery = "SELECT COUNT(super_user_id) FROM super_user WHERE super_user_id='"
 + currentUser + "';";

intresultset = esql.executeLoginQuery(query);

if( resultset != 1 ){
System.out.println("I'm sorry but you are not a registered super user");
return1;
 }

elseif( resultset == 1 ){
System.out.println("Welcome super user " + currentUser);
superUser = true;
return0;
 }

 }catch(Exceptione){
System.err.println( e.getMessage() );
return1;
 }
return0;
 }//end LoginAsSuper

///SOME NEW FUNCTIONS HERE/////////////////////////////////////////////////////////////////////////////////
publicstaticintqueryGenre(EmbeddedSQLesql)
 {
intgenre_id = -1;
try{
//Added some Genre code here//////////////////////////////////////////////
System.out.println("What is the genre of this video?");
StringinputGenre = in.readLine();

StringgetGenre = "SELECT genre_id FROM genre WHERE genre_name='" + inputGenre + "';";
Statementstmt = esql._connection.createStatement();
ResultSetrs = stmt.executeQuery(getGenre);
ResultSetMetaDatarsmd = rs.getMetaData();

if( rs.next() ) genre_id = Integer.parseInt(rs.getString(1));

//only care if the genre doesn't already exist
else
 {
getGenre = "SELECT MAX(genre_id) FROM genre;";
rs = stmt.executeQuery(getGenre);
rsmd = rs.getMetaData();

if( rs.next() ) genre_id = Integer.parseInt(rs.getString(1))+1;
elsegenre_id = 1;

esql.executeUpdate( "INSERT INTO genre (genre_id, genre_name) VALUES (" + genre_id + ", '" + inputGenre + "');" );
rs.close();
 }

////////////////////////////////////////////////////////////////////////

 }catch(Exceptione){
System.err.println( e.getMessage() );
return -1;}
returngenre_id;
 }

publicstaticvoidcategorizeVideo(EmbeddedSQLesql, intvideo_id, intgenre_id )
 {
System.out.println("VIDEOID: " + video_id + " GenreID: " + genre_id);
try{
StringgetGenre = "SELECT COUNT(*) FROM categorize WHERE genre_id=" + genre_id + " AND video_id=" + video_id + ";";
Statementstmt = esql._connection.createStatement();
ResultSetrs = stmt.executeQuery(getGenre);
ResultSetMetaDatarsmd = rs.getMetaData();


//only care if this video_id isn't already in the list
if( !rs.next() || rs.getString(1).equals("0") )
esql.executeUpdate( "INSERT INTO categorize (video_id, genre_id) VALUES (" + video_id + ", " + genre_id + ");" );

 }catch(Exceptione){
System.err.println( e.getMessage() );}
 }

//////END OF SEGMENTED NEW FUNCTIONS///////////////////////////////////////////////////////////////////////

//Register a new video, creating new video_id, season_id, and series_id if necessary
publicstaticintRegisterMovie(EmbeddedSQLesql)
 {
try{
booleanisSeries = false;
System.out.println("Please enter the following information to register a new movie/series");

System.out.println("What is the title of this video?");
StringinputTitle = in.readLine();

///ADDEDD//////////////////////////////////////////////////////////
intgenre_id = queryGenre(esql);
if( genre_id == -1 ) System.out.println("Problem when creating genre");
////ADDEDD//////////////////////////////////////////////////////////


System.out.println("Is this video part of a series (y/n)?");
StringinputSeries = in.readLine();

StringinputSeasonNum = "";
StringinputEpNum = "";
StringinputYear = "";
intinputOnlinePrice;
intinputDvdPrice;
intinputRating;
intseries_id;
intseason_id;

//if this is a season get season info
if( inputSeries.equals("y") ){
isSeries = true;

System.out.println("What season number is this?");
inputSeasonNum = in.readLine();

System.out.println("What episode is this?");
inputEpNum = in.readLine();
 }

System.out.println("What year did this come out?");
inputYear = in.readLine();

System.out.println("How much should the online price be?");
inputOnlinePrice = Integer.parseInt(in.readLine());

System.out.println("How much should the dvd price be?");
inputDvdPrice = Integer.parseInt(in.readLine());

System.out.println("How would you rate this (0-10)?");
inputRating = Integer.parseInt(in.readLine());

//this will get the video id
Stringquery = "SELECT MAX(video_id) FROM video;";
Statementstmt = esql._connection.createStatement ();
ResultSetrs = stmt.executeQuery (query);
ResultSetMetaDatarsmd = rs.getMetaData ();
intnumCol = rsmd.getColumnCount ();
rs.next();
intvideo_id = Integer.parseInt(rs.getString(numCol)) + 1;
System.out.println(video_id);
stmt.close();

//this will check if the series is already in record if not then it adds it
if( isSeries ){
stmt = esql._connection.createStatement();
StringgetSeriesId = "SELECT series_id FROM series WHERE title='" + inputTitle + "'";
rs = stmt.executeQuery (getSeriesId);
rsmd = rs.getMetaData ();
numCol = rsmd.getColumnCount ();

//get the series id if it already exists
if(rs.next()) series_id = Integer.parseInt(rs.getString(numCol));
//if series id doesnt already exist then create a new series value
else{
getSeriesId = "SELECT MAX(series_id) FROM series;";
rs = stmt.executeQuery(getSeriesId);
rsmd = rs.getMetaData();
if(rs.next()) series_id = Integer.parseInt(rs.getString(1)) + 1;
elseseries_id = 1;

//add series information to database
StringinsertSeries = "INSERT INTO series (series_id, title) VALUES (" + series_id + ", '" + inputTitle + "');";
esql.executeUpdate(insertSeries);
 }

StringgetSeasonId = "SELECT season_id FROM season WHERE series_id='" + series_id + "' AND season_number='" + inputSeasonNum + "';";
rs = stmt.executeQuery(getSeasonId);
rsmd = rs.getMetaData();
numCol = rsmd.getColumnCount();

//if the season id already exists grab the season id
if(rs.next()) season_id = Integer.parseInt(rs.getString(1));
//if the season id doesn't exist then place insert new season value
else{
getSeasonId = "SELECT MAX(season_id) FROM season;";
rs = stmt.executeQuery(getSeasonId);
rsmd = rs.getMetaData();
if(rs.next()) season_id = Integer.parseInt(rs.getString(1)) + 1;
elseseason_id = 1;

StringinsertSeason = "INSERT INTO season (season_id, series_id, season_number ) VALUES (" + season_id + ", " + series_id + ", '" + inputSeasonNum + "');";
esql.executeUpdate(insertSeason);
 }

StringinsertVideo = "INSERT INTO video (video_id, title, year, online_price, dvd_price, rating, episode, season_id) VALUES " + 
"(" + video_id + ",'" + inputTitle + "', '" + inputYear + "', " + inputOnlinePrice + ", " + inputDvdPrice + ", " + inputRating + ", " + inputEpNum + ", " + season_id + ");";

esql.executeUpdate(insertVideo);

stmt.close();
 }//End if video is a series

elseif( !isSeries )
 {
StringinsertVideo = "INSERT INTO video (video_id, title, year, online_price, dvd_price, rating) VALUES " + 
"(" + video_id + ",'" + inputTitle + "', '" + inputYear + "', " + inputOnlinePrice + ", " + inputDvdPrice + ", " + inputRating + ");";
stmt = esql._connection.createStatement ();
esql.executeUpdate(insertVideo);
stmt.close();
 }//End if video is not series

///ADDDEDDD//////////////////////////////////
categorizeVideo(esql, video_id, genre_id);
///ADDEDDDD/////////////////////////////////
 }catch(Exceptione){
System.err.println( e.getMessage() );
return1;
 }
return0;
 }

///////////////////////////////////////////////////////////////START//////////
/////////////////////////////////////////////////////////////CHANGES//////////
//this will delete a user from the database (only if user is superUser)
publicstaticintdeleteUser(EmbeddedSQLesql)
	{
try{
System.out.println("In order to delete a specific movie instance you must specify the following information");
while( true )
 {
System.out.println("Please specify a user_id that you want to delete or press 9 to cancel");
StringinputDelUser = in.readLine();

if( inputDelUser.equals("9") ) break;
elseif( inputDelUser.equals(currentUser) )
 {
System.out.println("You cannot delete youself, sorry.");
continue;
 }

Stringquery = "Select COUNT(user_id) FROM users WHERE user_id='" + inputDelUser + "';";

Statementstmt = esql._connection.createStatement ();
ResultSetrs = stmt.executeQuery(query);
ResultSetMetaDatarsmd = rs.getMetaData();

//if the user does exist
if(rs.next() && Integer.parseInt(rs.getString(1)) == 1 )
 {
query = "DELETE FROM users WHERE user_id='" + inputDelUser + "';";
esql.executeUpdate(query);
System.out.println("Successfully deleted user: " + inputDelUser );
rs.close();
return0;
 }//end user exist

//if user doesnt exist
else
 {
System.out.println("Sorry that user doesn't exist. Please make sure you have the right user_id");
rs.close();
continue;
 }//end user doest exist
 }//end while

 }catch(Exceptione){
System.err.println( e.getMessage() );
return1;}
return0;
	}


/////////////////////////////////////////////////////////////CHANGES//////////
/////////////////////////////////////////////////////////////CHANGES//////////
//////////////////////////////////////////////////////////////END//////////////
/*
 * Reads the users choice given from the keyboard
 * @int
 **/
publicstaticintreadChoice() {
intinput;
// returns only if a correct value is given.
do {
System.out.print("Please make your choice: ");
try { // read the integer, parse it and break.
input = Integer.parseInt(in.readLine());
break;
 }catch (Exceptione) {
System.out.println("Your input is invalid!");
continue;
 }//end try
 }while (true);
returninput;
 }//end readChoice

publicstaticintLogInQuery(EmbeddedSQLesql){
try{
System.out.println("You are trying to Login as an existing user, enter your Username (Make sure it is only 9 chars long) or press 9 to go back");
//Keep asking the user to login if they want to log in
while( true ){
System.out.println("Please specify your username or press 9 to go back:");
StringinputUser = in.readLine();

if( inputUser.equals("9") ) return1; //exit the loop and go back to home screen

Stringquery = "SELECT COUNT(user_id) FROM users WHERE user_id='";
query += inputUser + "'";

intoutput = esql.executeLoginQuery (query);
System.out.println("This is output" + output);

//check if username is valid, if not 1 then either output = 0 or > 2 which is a problem
if( output != 1 ) {
System.out.println("Sorry, that username isn't in our records. Please try again.");
continue;
 }//end username not found

//if output is 1 then only 1 record with that user found (good)
elseif( output == 1 ){
System.out.println("Please specify your password:");
StringinputPass = in.readLine();
query = "SELECT COUNT(user_id) FROM users WHERE user_id='" + inputUser + "' AND password='" + inputPass + "'";

intoutput2 = esql.executeLoginQuery(query); //returns number of query occurances

//if password doesn't match records ask for username again
if( output2 != 1 ){
System.out.println("Sorry, the username and password don't match up. Please try again.");
continue;
 }//end password fail

//if password correct then save current user info and log them in
elseif( output2 == 1 ){
currentUser = inputUser;
System.out.println("Successfully logged in! Welcome " + currentUser + "!");
break;
 }//end password success
 }//end username found
 }//end user login prompt
 }catch(Exceptione){
System.err.println (e.getMessage ());
return1;
 }//end catch
return0;
 }//end LoginQuery

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
publicstaticintRegisterQuery(EmbeddedSQLesql){
System.out.println("You have selected to register yourself as a user. Please specify the following information.");
try{
System.out.println("Enter a username you want (Username must be 9 chars or less long):");
Stringinput = in.readLine();

//if( input.length() > 9 ) { System.out.println("Invalid Username (more than 9 chars)"); continue; }

Stringquery = "INSERT INTO Users (user_id, password, first_name, middle_name, last_name, e_mail, street1, street2, state, country, zipcode, balance)" +
"VALUES ('" + input + "','";

System.out.println("Enter your password:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your first name:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your middle name:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your last name:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your email:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your street1:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your street2:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your state:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your country:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your zipcode:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your balance:");
input = in.readLine();
query += input + "')";

esql.executeUpdate (query);

 }catch(Exceptione){
System.err.println (e.getMessage ());
return1;
 }//end catch
return0;
 }//end RegisterQuery
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

/* 
 public static void RegisterQuery(EmbeddedSQL esql){
 try{
 String query = "SELECT C.sid, COUNT(C.pid) FROM Suppliers S, Catalog C "
 + "WHERE C.sid=S.sid GROUP BY C.sid;";

 int rowCount = esql.executeQuery (query);
 System.out.println ("total row(s): " + rowCount);
 }catch(Exception e){
 System.err.println (e.getMessage ());
 }
 }//end Query1

 public static void Query2(EmbeddedSQL esql){
 try{
 String query = "SELECT C.sid, COUNT(C.pid) FROM Suppliers S, Catalog C "
 + "WHERE C.sid=S.sid GROUP BY C.sid HAVING COUNT(C.pid)>=3;";

 int rowCount = esql.executeQuery (query);
 System.out.println ("total row(s): " + rowCount);

 }catch(Exception e){
 System.err.println (e.getMessage ());
 }
 }//end Query2

 public static void Query3(EmbeddedSQL esql){
 try{
 String query = "SELECT sname, COUNT(C.pid) FROM Suppliers S, Catalog C "
 + "WHERE pid IN (SELECT pid FROM Parts WHERE color='Green') AND S.sid=C.sid "
 + "GROUP BY sname";

 int rowCount = esql.executeQuery (query);
 System.out.println ("total row(s): " + rowCount);

 }catch(Exception e){
 System.err.println (e.getMessage ());
 }
 }//end Query3

 public static void Query4(EmbeddedSQL esql){
 try{
 String query = "SELECT P.pname, MAX(C.cost) FROM Parts P, Catalog C "
 + "WHERE C.sid IN (SELECT temp.sid "
 + "FROM (SELECT sid, pid FROM Catalog WHERE pid IN (SELECT pid FROM Parts WHERE color='Red')) as temp, "
 + "(SELECT sid, pid FROM catalog WHERE PID IN (SELECT pid FROM Parts WHERE color='Green')) as temp2 "
 + "WHERE temp.sid = temp2.sid) "
 + "GROUP BY P.pname;";

 int rowCount = esql.executeQuery (query);
 System.out.println ("total row(s): " + rowCount);

 }catch(Exception e){
 System.err.println (e.getMessage ());
 }
 }//end Query4

 public static void Query5(EmbeddedSQL esql){
 try{
 String query = "SELECT DISTINCT pname FROM Parts "
 + "WHERE pid IN (SELECT pid FROM Catalog WHERE cost<";

 System.out.print("\tEnter cost: $");
 String input = in.readLine();
 query+=input;
 query += ");";

 int rowCount = esql.executeQuery (query);
 System.out.println ("total row(s): " + rowCount);
/*
 * MODIFIED BY JUSTIN KAHAL AND THOMAS DESMOND
 * SSID:860892022
 *
 * Template JAVA User Interface
 * =============================
 *
 * Database Management Systems
 * Department of Computer Science &amp; Engineering
 * University of California - Riverside
 *
 * Target DBMS: 'Postgres'
 *
 */


importjava.sql.DriverManager;
importjava.sql.Connection;
importjava.sql.Statement;
importjava.sql.ResultSet;
importjava.sql.ResultSetMetaData;
importjava.sql.SQLException;
importjava.io.File;
importjava.io.FileReader;
importjava.io.BufferedReader;
importjava.io.InputStreamReader;


///////////////////////////////////////////////////////
////////////////////////////////////////////////////
/////////	ADD THIS IMPORT ////////////////
importjava.util.Scanner;

/**
 * This class defines a simple embedded SQL utility class that is designed to
 * work with PostgreSQL JDBC drivers.
 *
 */
publicclassEmbeddedSQL {


publicstaticStringcurrentUser;
publicstaticbooleansuperUser = false;
// reference to physical database connection.
privateConnection_connection = null;

// handling the keyboard inputs through a BufferedReader
// This variable can be global for convenience.
staticBufferedReaderin = newBufferedReader(
newInputStreamReader(System.in));

/**
 * Creates a new instance of EmbeddedSQL
 *
 * @param hostname the MySQL or PostgreSQL server hostname
 * @param database the name of the database
 * @param username the user name used to login to the database
 * @param password the user login password
 * @throws java.sql.SQLException when failed to make a connection.
 */
publicEmbeddedSQL (Stringdbname, Stringdbport, Stringuser, Stringpasswd) throwsSQLException {

System.out.print("Connecting to database...");
try{
// constructs the connection URL
Stringurl = "jdbc:postgresql://localhost:" + dbport + "/" + dbname;
System.out.println ("Connection URL: " + url + "\n");

// obtain a physical connection
this._connection = DriverManager.getConnection(url, user, passwd);
System.out.println("Done");
 }catch (Exceptione){
System.err.println("Error - Unable to Connect to Database: " + e.getMessage() );
System.out.println("Make sure you started postgres on this machine");
System.exit(-1);
 }//end catch
 }//end EmbeddedSQL

/**
 * Method to execute an update SQL statement. Update SQL instructions
 * includes CREATE, INSERT, UPDATE, DELETE, and DROP.
 *
 * @param sql the input SQL string
 * @throws java.sql.SQLException when update failed
 */
publicvoidexecuteUpdate (Stringsql) throwsSQLException {
// creates a statement object
Statementstmt = this._connection.createStatement ();

// issues the update instruction
stmt.executeUpdate (sql);

// close the instruction
stmt.close ();
 }//end execute update 

intgetVidId(Stringquery)
 {
try{
// creates a statement objectv
Statementstmt = this._connection.createStatement (); 

// issues the query instruction
ResultSetrs = stmt.executeQuery(query);

while(rs.next())
 {
intvid_id = Integer.parseInt(rs.getString(1));
returnvid_id;
 }
return -1;
 }catch(Exceptione){
System.err.println (e.getMessage ());} 
return -1;
	}//end getVidID

publicintexecuteOrder (Stringquery, intprice) throwsSQLException
 {
// creates a statement objectv
Statementstmt = this._connection.createStatement ();

// issues the query instruction
ResultSetrs = stmt.executeQuery(query);

while(rs.next())
 {
intbalance = Integer.parseInt(rs.getString(1));
if(balance - price >= 0)
 {
System.out.println("You would have negative balance if movie is ordered you cannot order movie"); 
return -1;
 }
else
 {
Stringupdatebal = "UPDATE users SET balance = balance - " + price + " WHERE user_id = '" + currentUser + "'";
stmt.executeUpdate(updatebal); //I changed this part might be problematic
Stringnewbalance = "SELECT balance FROM users WHERE user_id='" + currentUser + "'";
System.out.print("New Balance if ordered: ");
executeStringQuery(newbalance, 1);
return1;
 }	
 }
return -1;
	}

publicvoidexecuteStringQuery (Stringquery, intline) throwsSQLException
	{
// creates a statement objectv
Statementstmt = this._connection.createStatement ();

// issues the query instruction
ResultSetrs = stmt.executeQuery (query);

/*
 ** obtains the metadata object for the returned result set. The metadata
 ** contains row and column info.
 */
System.out.println("--------------");
while(rs.next())
 {
System.out.println(rs.getString(line));
 }
	}

publicintexecuteCheckTitle (Stringquery) throwsSQLException
	{
try{
// creates a statement objectv
Statementstmt = this._connection.createStatement ();
System.out.println("Connection Success");

// issues the query instruction
ResultSetrs = stmt.executeQuery(query);
System.out.println("Query exectued success");

System.out.println("--------------");

while(rs.next())
 {
System.out.print("Are you sure you would like to order (yes or no): ");
System.out.println(rs.getString(2) + " Price: " + rs.getString(3));
Stringinput = in.readLine();
System.out.println();
 }

returnInteger.parseInt(rs.getString(3));
 }catch(Exceptione){
System.err.println (e.getMessage ());}
return -1;
	}

publicintexecuteLoginQuery (Stringquery) throwsSQLException
	{
// creates a statement objectv
Statementstmt = this._connection.createStatement ();

// issues the query instruction
ResultSetrs = stmt.executeQuery (query);

/*
 ** obtains the metadata object for the returned result set. The metadata
 ** contains row and column info.
 */
ResultSetMetaDatarsmd = rs.getMetaData ();
intnumCol = rsmd.getColumnCount ();
rs.next();
intresultset = Integer.parseInt(rs.getString(numCol));
System.out.println(resultset);

stmt.close();
returnresultset;
// iterates through the result set and output them to standard out.
/* boolean outputHeader = true;
 while (rs.next()){
 if(outputHeader){
 for(int i = 1; i <= numCol; i++){
 System.out.print(rsmd.getColumnName(i) + "\t");
 }
 System.out.println();
 outputHeader = false;
 }
 for (int i=1; i<=numCol; ++i)
 System.out.print (rs.getString (i) + "\t");
 System.out.println ();
 ++rowCount;
 }//end while
 stmt.close ();
 return rowCount;
 */
 }

/**
 * Method to execute an input query SQL instruction (i.e. SELECT). This
 * method issues the query to the DBMS and outputs the results to
 * standard out.
 *
 * @param query the input query string
 * @return the number of rows returned
 * @throws java.sql.SQLException when failed to execute the query
 */
publicintexecuteQuery (Stringquery) throwsSQLException {
// creates a statement objectv
Statementstmt = this._connection.createStatement ();

// issues the query instruction
ResultSetrs = stmt.executeQuery (query);

/*
 ** obtains the metadata object for the returned result set. The metadata
 ** contains row and column info.
 */
ResultSetMetaDatarsmd = rs.getMetaData ();
intnumCol = rsmd.getColumnCount ();
introwCount = 0;

// iterates through the result set and output them to standard out.
booleanoutputHeader = true;
while (rs.next()){
if(outputHeader){
for(inti = 1; i <= numCol; i++){
System.out.print(rsmd.getColumnName(i) + "\t");
 }
System.out.println();
outputHeader = false;
 }
for (inti=1; i<=numCol; ++i)
System.out.print (rs.getString (i) + "\t");
System.out.println ();
 ++rowCount;
 }//end while
stmt.close ();
returnrowCount;
 }//end executeQuery

/**
 * Method to close the physical connection if it is open.
 */
publicvoidcleanup(){
try{
if (this._connection != null){
this._connection.close ();
 }//end if
 }catch (SQLExceptione){
// ignored.
 }//end try
 }//end cleanup

/**
 * The main execution method
 *
 * @param args the command line arguments this inclues the <mysql|pgsql> <login file>
 */
publicstaticvoidmain (String[] args) {
if (args.length != 4) {
System.err.println (
"Usage: " +
"java [-classpath <classpath>] " +
EmbeddedSQL.class.getName () +
" <dbname> <port> <user> <passwd>");
return;
 }//end if

Greeting();
EmbeddedSQLesql = null;
try{
// use postgres JDBC driver.
Class.forName ("org.postgresql.Driver").newInstance ();
// instantiate the EmbeddedSQL object and creates a physical
// connection.
Stringdbname = args[0];
Stringdbport = args[1];
Stringuser = args[2];
Stringpasswd = args[3];
esql = newEmbeddedSQL (dbname, dbport, user, passwd);

booleankeepon = true;
while(keepon) {
// These are sample SQL statements
System.out.println("WELCOME! Are you an existing user?");
System.out.println("---------");
System.out.println("0. Yes I am!");
System.out.println("1. No, I need to register!");
System.out.println("9. < EXIT (Stop the program)");


switch (readChoice()){
case0: if(LogInQuery(esql) == 0 ) keepon = false; break;
case1: if(RegisterQuery(esql) == 0 ) keepon = false; break;
case9: return;
default : System.out.println("Unrecognized choice!"); break;
 }//end switch
 }//end while 

//Will need to turn keepon to false after logging in 

booleanhomescreen = true; 
while(homescreen)
 {
System.out.println("---------");
System.out.println("Welcome to MovieNet");
System.out.println("---------");
System.out.println("0. See list of movies");
System.out.println("1. Order a movie");
System.out.println("2. Rate a movie NOT WORKING");
System.out.println("3. Look at wall NOT WORKING");
System.out.println("4. Add to balance");
System.out.println("5. List favorite movie");
System.out.println("6. Add a favorite movie");

//these will only be visible if you are super user
if( superUser ){
System.out.println("\nSuper User Options:");
System.out.println("10. Register a new movie WORKING");
System.out.println("11. Delete an existing user NOT WORKING");
 }

/////////////////////////////////////////////////////////////CHANGES//////////
if( !superUser ) System.out.println("8. Login as SuperUser");
/////////////////////////////////////////////////////////////CHANGES//////////
System.out.println("9. < EXIT (Stop the program)");

switch (readChoice()){
case0: ListMovies(esql); break;
case1: OrderMovie(esql); break;
case2: break;
case3: break;
case4: IncreaseBalance(esql); break;
case5: ListFavs(esql); break;
case6: InsertFav(esql); break;
/////////////////////////////////////////////////////////////CHANGES//////////
case8: if( !superUser ) {LoginAsSuper(esql);}; break;
/////////////////////////////////////////////////////////////CHANGES//////////

case9: return;
case10: if( superUser ) {RegisterMovie(esql);}; break;
case11: if( superUser ) {deleteUser(esql);}; break;
default : System.out.println("Unrecognized choice!"); break;
 }


 }


 }catch(Exceptione) {
System.err.println (e.getMessage ());
 }finally{
// make sure to cleanup the created table and close the connection.
try{
if(esql != null) {
System.out.print("Disconnecting from database...");
esql.cleanup ();
System.out.println("Done\n\nBye !");
 }//end if
 }catch (Exceptione) {
// ignored.
 }//end try
 }//end try
 }//end main



publicstaticvoidListMovies(EmbeddedSQLesql)
	{
try{
Stringquery = "SELECT title FROM Video";
esql.executeStringQuery(query, 1);

 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
	}

publicstaticvoidListFavs(EmbeddedSQLesql)
	{
try{
Stringquery = "SELECT title FROM video, likes WHERE likes.user_id = '" + currentUser + 
"' AND likes.video_id = video.video_id";
esql.executeStringQuery(query, 1);
 }catch(Exceptione){
System.err.println (e.getMessage ());}
	}

publicstaticvoidInsertFav(EmbeddedSQLesql)
	{
try{
System.out.print("Enter title of move you would like to add as favorite: ");
Stringtitle = in.readLine();
Stringquery = "SELECT video_id FROM video WHERE title = '" + title + "'";

intvid_id = esql.getVidId(query);

System.out.println("GOT THE VID ID");
query = "INSERT INTO likes (user_id, video_id) VALUES ('" + currentUser + "', " + vid_id + ")";
esql.executeUpdate(query);

System.out.println("New favorite added");

 }catch(Exceptione){
System.err.println (e.getMessage ());} 
	}

publicstaticvoidOrderMovie(EmbeddedSQLesql)
	{
try{ 
System.out.print("Title of movie you would like to order: ");
Stringtitle = in.readLine();

//Grabbing title and will print it
Stringquery = "SELECT * FROM video WHERE title = ";
query += title + "'";

intprice = esql.executeCheckTitle(query);

query = "SELECT balance FROM users WHERE user_id ='" + currentUser + "';";
inttemp = esql.executeOrder(query, price);

if( temp == 1 )
System.out.println("Successfully ordered: " + title);
 }catch(Exceptione){
System.err.println (e.getMessage ());}
	}


publicstaticvoidIncreaseBalance(EmbeddedSQLesql)
	{
try{

System.out.print("How much would you like to add to balance?: ");
Stringstringbal = in.readLine();
intintbal = Integer.parseInt(stringbal);

if(intbal <= 0)
 {
System.out.println("Must input number greater than $0");
return;
 }

System.out.println("About to update");


Stringquery = "UPDATE users SET balance = balance +" + intbal + " WHERE user_id = '"
 + currentUser + "'";

esql.executeUpdate(query);

System.out.println("UPDATED");

Stringnewbalance = "SELECT balance FROM users WHERE user_id='" + currentUser + "'";
System.out.print("New Balance after addition: ");
esql.executeStringQuery(newbalance, 1); 

return;

 }catch(Exceptione){	
System.err.println (e.getMessage ());}
	}

// NOT FULLY IMPLEMENTED NEED TO GRAB VIDEO ID AND THEN RATE MOVIE
publicstaticvoidRateMovie(EmbeddedSQLesql)
	{
try{

System.out.print("Title of movie you would like to rate: ");
Stringtitle = in.readLine();
System.out.print("What would you like to rate the movie (0 - 10): ");
Stringrating = in.readLine();

Stringquery = "SELECT title FROM Video";

//String output = esql.executeStringQuery(query); //Cant use executeString This way!!!!

 }catch(Exceptione){
System.err.println (e.getMessage ());}
	}

publicstaticvoidGreeting(){
System.out.println(
"\n\n*******************************************************\n" +
" User Interface \n" +
"*******************************************************\n");
 }//end Greeting

publicstaticintLoginAsSuper(EmbeddedSQLesql)
 {
try{
System.out.println("Verifying that you are a Super User");
Stringquery = "SELECT COUNT(super_user_id) FROM super_user WHERE super_user_id='"
 + currentUser + "';";

intresultset = esql.executeLoginQuery(query);

if( resultset != 1 ){
System.out.println("I'm sorry but you are not a registered super user");
return1;
 }

elseif( resultset == 1 ){
System.out.println("Welcome super user " + currentUser);
superUser = true;
return0;
 }

 }catch(Exceptione){
System.err.println( e.getMessage() );
return1;
 }
return0;
 }//end LoginAsSuper

///SOME NEW FUNCTIONS HERE/////////////////////////////////////////////////////////////////////////////////
publicstaticintqueryGenre(EmbeddedSQLesql)
 {
intgenre_id = -1;
try{
//Added some Genre code here//////////////////////////////////////////////
System.out.println("What is the genre of this video?");
StringinputGenre = in.readLine();

StringgetGenre = "SELECT genre_id FROM genre WHERE genre_name='" + inputGenre + "';";
Statementstmt = esql._connection.createStatement();
ResultSetrs = stmt.executeQuery(getGenre);
ResultSetMetaDatarsmd = rs.getMetaData();

if( rs.next() ) genre_id = Integer.parseInt(rs.getString(1));

//only care if the genre doesn't already exist
else
 {
getGenre = "SELECT MAX(genre_id) FROM genre;";
rs = stmt.executeQuery(getGenre);
rsmd = rs.getMetaData();

if( rs.next() ) genre_id = Integer.parseInt(rs.getString(1))+1;
elsegenre_id = 1;

esql.executeUpdate( "INSERT INTO genre (genre_id, genre_name) VALUES (" + genre_id + ", '" + inputGenre + "');" );
rs.close();
 }

////////////////////////////////////////////////////////////////////////

 }catch(Exceptione){
System.err.println( e.getMessage() );
return -1;}
returngenre_id;
 }

publicstaticvoidcategorizeVideo(EmbeddedSQLesql, intvideo_id, intgenre_id )
 {
System.out.println("VIDEOID: " + video_id + " GenreID: " + genre_id);
try{
StringgetGenre = "SELECT COUNT(*) FROM categorize WHERE genre_id=" + genre_id + " AND video_id=" + video_id + ";";
Statementstmt = esql._connection.createStatement();
ResultSetrs = stmt.executeQuery(getGenre);
ResultSetMetaDatarsmd = rs.getMetaData();


//only care if this video_id isn't already in the list
if( !rs.next() || rs.getString(1).equals("0") )
esql.executeUpdate( "INSERT INTO categorize (video_id, genre_id) VALUES (" + video_id + ", " + genre_id + ");" );

 }catch(Exceptione){
System.err.println( e.getMessage() );}
 }

//////END OF SEGMENTED NEW FUNCTIONS///////////////////////////////////////////////////////////////////////

//Register a new video, creating new video_id, season_id, and series_id if necessary
publicstaticintRegisterMovie(EmbeddedSQLesql)
 {
try{
booleanisSeries = false;
System.out.println("Please enter the following information to register a new movie/series");

System.out.println("What is the title of this video?");
StringinputTitle = in.readLine();

///ADDEDD//////////////////////////////////////////////////////////
intgenre_id = queryGenre(esql);
if( genre_id == -1 ) System.out.println("Problem when creating genre");
////ADDEDD//////////////////////////////////////////////////////////


System.out.println("Is this video part of a series (y/n)?");
StringinputSeries = in.readLine();

StringinputSeasonNum = "";
StringinputEpNum = "";
StringinputYear = "";
intinputOnlinePrice;
intinputDvdPrice;
intinputRating;
intseries_id;
intseason_id;

//if this is a season get season info
if( inputSeries.equals("y") ){
isSeries = true;

System.out.println("What season number is this?");
inputSeasonNum = in.readLine();

System.out.println("What episode is this?");
inputEpNum = in.readLine();
 }

System.out.println("What year did this come out?");
inputYear = in.readLine();

System.out.println("How much should the online price be?");
inputOnlinePrice = Integer.parseInt(in.readLine());

System.out.println("How much should the dvd price be?");
inputDvdPrice = Integer.parseInt(in.readLine());

System.out.println("How would you rate this (0-10)?");
inputRating = Integer.parseInt(in.readLine());

//this will get the video id
Stringquery = "SELECT MAX(video_id) FROM video;";
Statementstmt = esql._connection.createStatement ();
ResultSetrs = stmt.executeQuery (query);
ResultSetMetaDatarsmd = rs.getMetaData ();
intnumCol = rsmd.getColumnCount ();
rs.next();
intvideo_id = Integer.parseInt(rs.getString(numCol)) + 1;
System.out.println(video_id);
stmt.close();

//this will check if the series is already in record if not then it adds it
if( isSeries ){
stmt = esql._connection.createStatement();
StringgetSeriesId = "SELECT series_id FROM series WHERE title='" + inputTitle + "'";
rs = stmt.executeQuery (getSeriesId);
rsmd = rs.getMetaData ();
numCol = rsmd.getColumnCount ();

//get the series id if it already exists
if(rs.next()) series_id = Integer.parseInt(rs.getString(numCol));
//if series id doesnt already exist then create a new series value
else{
getSeriesId = "SELECT MAX(series_id) FROM series;";
rs = stmt.executeQuery(getSeriesId);
rsmd = rs.getMetaData();
if(rs.next()) series_id = Integer.parseInt(rs.getString(1)) + 1;
elseseries_id = 1;

//add series information to database
StringinsertSeries = "INSERT INTO series (series_id, title) VALUES (" + series_id + ", '" + inputTitle + "');";
esql.executeUpdate(insertSeries);
 }

StringgetSeasonId = "SELECT season_id FROM season WHERE series_id='" + series_id + "' AND season_number='" + inputSeasonNum + "';";
rs = stmt.executeQuery(getSeasonId);
rsmd = rs.getMetaData();
numCol = rsmd.getColumnCount();

//if the season id already exists grab the season id
if(rs.next()) season_id = Integer.parseInt(rs.getString(1));
//if the season id doesn't exist then place insert new season value
else{
getSeasonId = "SELECT MAX(season_id) FROM season;";
rs = stmt.executeQuery(getSeasonId);
rsmd = rs.getMetaData();
if(rs.next()) season_id = Integer.parseInt(rs.getString(1)) + 1;
elseseason_id = 1;

StringinsertSeason = "INSERT INTO season (season_id, series_id, season_number ) VALUES (" + season_id + ", " + series_id + ", '" + inputSeasonNum + "');";
esql.executeUpdate(insertSeason);
 }

StringinsertVideo = "INSERT INTO video (video_id, title, year, online_price, dvd_price, rating, episode, season_id) VALUES " + 
"(" + video_id + ",'" + inputTitle + "', '" + inputYear + "', " + inputOnlinePrice + ", " + inputDvdPrice + ", " + inputRating + ", " + inputEpNum + ", " + season_id + ");";

esql.executeUpdate(insertVideo);

stmt.close();
 }//End if video is a series

elseif( !isSeries )
 {
StringinsertVideo = "INSERT INTO video (video_id, title, year, online_price, dvd_price, rating) VALUES " + 
"(" + video_id + ",'" + inputTitle + "', '" + inputYear + "', " + inputOnlinePrice + ", " + inputDvdPrice + ", " + inputRating + ");";
stmt = esql._connection.createStatement ();
esql.executeUpdate(insertVideo);
stmt.close();
 }//End if video is not series

///ADDDEDDD//////////////////////////////////
categorizeVideo(esql, video_id, genre_id);
///ADDEDDDD/////////////////////////////////
 }catch(Exceptione){
System.err.println( e.getMessage() );
return1;
 }
return0;
 }

///////////////////////////////////////////////////////////////START//////////
/////////////////////////////////////////////////////////////CHANGES//////////
//this will delete a user from the database (only if user is superUser)
publicstaticintdeleteUser(EmbeddedSQLesql)
	{
try{
System.out.println("In order to delete a specific movie instance you must specify the following information");
while( true )
 {
System.out.println("Please specify a user_id that you want to delete or press 9 to cancel");
StringinputDelUser = in.readLine();

if( inputDelUser.equals("9") ) break;
elseif( inputDelUser.equals(currentUser) )
 {
System.out.println("You cannot delete youself, sorry.");
continue;
 }

Stringquery = "Select COUNT(user_id) FROM users WHERE user_id='" + inputDelUser + "';";

Statementstmt = esql._connection.createStatement ();
ResultSetrs = stmt.executeQuery(query);
ResultSetMetaDatarsmd = rs.getMetaData();

//if the user does exist
if(rs.next() && Integer.parseInt(rs.getString(1)) == 1 )
 {
query = "DELETE FROM users WHERE user_id='" + inputDelUser + "';";
esql.executeUpdate(query);
System.out.println("Successfully deleted user: " + inputDelUser );
rs.close();
return0;
 }//end user exist

//if user doesnt exist
else
 {
System.out.println("Sorry that user doesn't exist. Please make sure you have the right user_id");
rs.close();
continue;
 }//end user doest exist
 }//end while

 }catch(Exceptione){
System.err.println( e.getMessage() );
return1;}
return0;
	}


/////////////////////////////////////////////////////////////CHANGES//////////
/////////////////////////////////////////////////////////////CHANGES//////////
//////////////////////////////////////////////////////////////END//////////////
/*
 * Reads the users choice given from the keyboard
 * @int
 **/
publicstaticintreadChoice() {
intinput;
// returns only if a correct value is given.
do {
System.out.print("Please make your choice: ");
try { // read the integer, parse it and break.
input = Integer.parseInt(in.readLine());
break;
 }catch (Exceptione) {
System.out.println("Your input is invalid!");
continue;
 }//end try
 }while (true);
returninput;
 }//end readChoice

publicstaticintLogInQuery(EmbeddedSQLesql){
try{
System.out.println("You are trying to Login as an existing user, enter your Username (Make sure it is only 9 chars long) or press 9 to go back");
//Keep asking the user to login if they want to log in
while( true ){
System.out.println("Please specify your username or press 9 to go back:");
StringinputUser = in.readLine();

if( inputUser.equals("9") ) return1; //exit the loop and go back to home screen

Stringquery = "SELECT COUNT(user_id) FROM users WHERE user_id='";
query += inputUser + "'";

intoutput = esql.executeLoginQuery (query);
System.out.println("This is output" + output);

//check if username is valid, if not 1 then either output = 0 or > 2 which is a problem
if( output != 1 ) {
System.out.println("Sorry, that username isn't in our records. Please try again.");
continue;
 }//end username not found

//if output is 1 then only 1 record with that user found (good)
elseif( output == 1 ){
System.out.println("Please specify your password:");
StringinputPass = in.readLine();
query = "SELECT COUNT(user_id) FROM users WHERE user_id='" + inputUser + "' AND password='" + inputPass + "'";

intoutput2 = esql.executeLoginQuery(query); //returns number of query occurances

//if password doesn't match records ask for username again
if( output2 != 1 ){
System.out.println("Sorry, the username and password don't match up. Please try again.");
continue;
 }//end password fail

//if password correct then save current user info and log them in
elseif( output2 == 1 ){
currentUser = inputUser;
System.out.println("Successfully logged in! Welcome " + currentUser + "!");
break;
 }//end password success
 }//end username found
 }//end user login prompt
 }catch(Exceptione){
System.err.println (e.getMessage ());
return1;
 }//end catch
return0;
 }//end LoginQuery

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
publicstaticintRegisterQuery(EmbeddedSQLesql){
System.out.println("You have selected to register yourself as a user. Please specify the following information.");
try{
System.out.println("Enter a username you want (Username must be 9 chars or less long):");
Stringinput = in.readLine();

//if( input.length() > 9 ) { System.out.println("Invalid Username (more than 9 chars)"); continue; }

Stringquery = "INSERT INTO Users (user_id, password, first_name, middle_name, last_name, e_mail, street1, street2, state, country, zipcode, balance)" +
"VALUES ('" + input + "','";

System.out.println("Enter your password:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your first name:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your middle name:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your last name:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your email:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your street1:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your street2:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your state:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your country:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your zipcode:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your balance:");
input = in.readLine();
query += input + "')";

esql.executeUpdate (query);

 }catch(Exceptione){
System.err.println (e.getMessage ());
return1;
 }//end catch
return0;
 }//end RegisterQuery
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

/* 
 public static void RegisterQuery(EmbeddedSQL esql){
 try{
 String query = "SELECT C.sid, COUNT(C.pid) FROM Suppliers S, Catalog C "
 + "WHERE C.sid=S.sid GROUP BY C.sid;";

 int rowCount = esql.executeQuery (query);
 System.out.println ("total row(s): " + rowCount);
 }catch(Exception e){
 System.err.println (e.getMessage ());
 }
 }//end Query1

 public static void Query2(EmbeddedSQL esql){
 try{
 String query = "SELECT C.sid, COUNT(C.pid) FROM Suppliers S, Catalog C "
 + "WHERE C.sid=S.sid GROUP BY C.sid HAVING COUNT(C.pid)>=3;";

 int rowCount = esql.executeQuery (query);
 System.out.println ("total row(s): " + rowCount);

 }catch(Exception e){
 System.err.println (e.getMessage ());
 }
 }//end Query2

 public static void Query3(EmbeddedSQL esql){
 try{
 String query = "SELECT sname, COUNT(C.pid) FROM Suppliers S, Catalog C "
 + "WHERE pid IN (SELECT pid FROM Parts WHERE color='Green') AND S.sid=C.sid "
 + "GROUP BY sname";

 int rowCount = esql.executeQuery (query);
 System.out.println ("total row(s): " + rowCount);

 }catch(Exception e){
 System.err.println (e.getMessage ());
 }
 }//end Query3

 public static void Query4(EmbeddedSQL esql){
 try{
 String query = "SELECT P.pname, MAX(C.cost) FROM Parts P, Catalog C "
 + "WHERE C.sid IN (SELECT temp.sid "
 + "FROM (SELECT sid, pid FROM Catalog WHERE pid IN (SELECT pid FROM Parts WHERE color='Red')) as temp, "
 + "(SELECT sid, pid FROM catalog WHERE PID IN (SELECT pid FROM Parts WHERE color='Green')) as temp2 "
 + "WHERE temp.sid = temp2.sid) "
 + "GROUP BY P.pname;";

 int rowCount = esql.executeQuery (query);
 System.out.println ("total row(s): " + rowCount);

 }catch(Exception e){
 System.err.println (e.getMessage ());
 }
 }//end Query4

 public static void Query5(EmbeddedSQL esql){
 try{
 String query = "SELECT DISTINCT pname FROM Parts "
 + "WHERE pid IN (SELECT pid FROM Catalog WHERE cost<";

 System.out.print("\tEnter cost: $");
 String input = in.readLine();
 query+=input;
 query += ");";

 int rowCount = esql.executeQuery (query);
 System.out.println ("total row(s): " + rowCount);
/*
 * MODIFIED BY JUSTIN KAHAL AND THOMAS DESMOND
 * SSID:860892022
 *
 * Template JAVA User Interface
 * =============================
 *
 * Database Management Systems
 * Department of Computer Science &amp; Engineering
 * University of California - Riverside
 *
 * Target DBMS: 'Postgres'
 *
 */


importjava.sql.DriverManager;
importjava.sql.Connection;
importjava.sql.Statement;
importjava.sql.ResultSet;
importjava.sql.ResultSetMetaData;
importjava.sql.SQLException;
importjava.io.File;
importjava.io.FileReader;
importjava.io.BufferedReader;
importjava.io.InputStreamReader;


///////////////////////////////////////////////////////
////////////////////////////////////////////////////
/////////	ADD THIS IMPORT ////////////////
importjava.util.Scanner;

/**
 * This class defines a simple embedded SQL utility class that is designed to
 * work with PostgreSQL JDBC drivers.
 *
 */
publicclassEmbeddedSQL {


publicstaticStringcurrentUser;
publicstaticbooleansuperUser = false;
// reference to physical database connection.
privateConnection_connection = null;

// handling the keyboard inputs through a BufferedReader
// This variable can be global for convenience.
staticBufferedReaderin = newBufferedReader(
newInputStreamReader(System.in));

/**
 * Creates a new instance of EmbeddedSQL
 *
 * @param hostname the MySQL or PostgreSQL server hostname
 * @param database the name of the database
 * @param username the user name used to login to the database
 * @param password the user login password
 * @throws java.sql.SQLException when failed to make a connection.
 */
publicEmbeddedSQL (Stringdbname, Stringdbport, Stringuser, Stringpasswd) throwsSQLException {

System.out.print("Connecting to database...");
try{
// constructs the connection URL
Stringurl = "jdbc:postgresql://localhost:" + dbport + "/" + dbname;
System.out.println ("Connection URL: " + url + "\n");

// obtain a physical connection
this._connection = DriverManager.getConnection(url, user, passwd);
System.out.println("Done");
 }catch (Exceptione){
System.err.println("Error - Unable to Connect to Database: " + e.getMessage() );
System.out.println("Make sure you started postgres on this machine");
System.exit(-1);
 }//end catch
 }//end EmbeddedSQL

/**
 * Method to execute an update SQL statement. Update SQL instructions
 * includes CREATE, INSERT, UPDATE, DELETE, and DROP.
 *
 * @param sql the input SQL string
 * @throws java.sql.SQLException when update failed
 */
publicvoidexecuteUpdate (Stringsql) throwsSQLException {
// creates a statement object
Statementstmt = this._connection.createStatement ();

// issues the update instruction
stmt.executeUpdate (sql);

// close the instruction
stmt.close ();
 }//end execute update 

intgetVidId(Stringquery)
 {
try{
// creates a statement objectv
Statementstmt = this._connection.createStatement (); 

// issues the query instruction
ResultSetrs = stmt.executeQuery(query);

while(rs.next())
 {
intvid_id = Integer.parseInt(rs.getString(1));
returnvid_id;
 }
return -1;
 }catch(Exceptione){
System.err.println (e.getMessage ());} 
return -1;
	}//end getVidID

publicintexecuteOrder (Stringquery, intprice) throwsSQLException
 {
// creates a statement objectv
Statementstmt = this._connection.createStatement ();

// issues the query instruction
ResultSetrs = stmt.executeQuery(query);

while(rs.next())
 {
intbalance = Integer.parseInt(rs.getString(1));
if(balance - price >= 0)
 {
System.out.println("You would have negative balance if movie is ordered you cannot order movie"); 
return -1;
 }
else
 {
Stringupdatebal = "UPDATE users SET balance = balance - " + price + " WHERE user_id = '" + currentUser + "'";
stmt.executeUpdate(updatebal); //I changed this part might be problematic
Stringnewbalance = "SELECT balance FROM users WHERE user_id='" + currentUser + "'";
System.out.print("New Balance if ordered: ");
executeStringQuery(newbalance, 1);
return1;
 }	
 }
return -1;
	}

publicvoidexecuteStringQuery (Stringquery, intline) throwsSQLException
	{
// creates a statement objectv
Statementstmt = this._connection.createStatement ();

// issues the query instruction
ResultSetrs = stmt.executeQuery (query);

/*
 ** obtains the metadata object for the returned result set. The metadata
 ** contains row and column info.
 */
System.out.println("--------------");
while(rs.next())
 {
System.out.println(rs.getString(line));
 }
	}

publicintexecuteCheckTitle (Stringquery) throwsSQLException
	{
try{
// creates a statement objectv
Statementstmt = this._connection.createStatement ();
System.out.println("Connection Success");

// issues the query instruction
ResultSetrs = stmt.executeQuery(query);
System.out.println("Query exectued success");

System.out.println("--------------");

while(rs.next())
 {
System.out.print("Are you sure you would like to order (yes or no): ");
System.out.println(rs.getString(2) + " Price: " + rs.getString(3));
Stringinput = in.readLine();
System.out.println();
 }

returnInteger.parseInt(rs.getString(3));
 }catch(Exceptione){
System.err.println (e.getMessage ());}
return -1;
	}

publicintexecuteLoginQuery (Stringquery) throwsSQLException
	{
// creates a statement objectv
Statementstmt = this._connection.createStatement ();

// issues the query instruction
ResultSetrs = stmt.executeQuery (query);

/*
 ** obtains the metadata object for the returned result set. The metadata
 ** contains row and column info.
 */
ResultSetMetaDatarsmd = rs.getMetaData ();
intnumCol = rsmd.getColumnCount ();
rs.next();
intresultset = Integer.parseInt(rs.getString(numCol));
System.out.println(resultset);

stmt.close();
returnresultset;
// iterates through the result set and output them to standard out.
/* boolean outputHeader = true;
 while (rs.next()){
 if(outputHeader){
 for(int i = 1; i <= numCol; i++){
 System.out.print(rsmd.getColumnName(i) + "\t");
 }
 System.out.println();
 outputHeader = false;
 }
 for (int i=1; i<=numCol; ++i)
 System.out.print (rs.getString (i) + "\t");
 System.out.println ();
 ++rowCount;
 }//end while
 stmt.close ();
 return rowCount;
 */
 }

/**
 * Method to execute an input query SQL instruction (i.e. SELECT). This
 * method issues the query to the DBMS and outputs the results to
 * standard out.
 *
 * @param query the input query string
 * @return the number of rows returned
 * @throws java.sql.SQLException when failed to execute the query
 */
publicintexecuteQuery (Stringquery) throwsSQLException {
// creates a statement objectv
Statementstmt = this._connection.createStatement ();

// issues the query instruction
ResultSetrs = stmt.executeQuery (query);

/*
 ** obtains the metadata object for the returned result set. The metadata
 ** contains row and column info.
 */
ResultSetMetaDatarsmd = rs.getMetaData ();
intnumCol = rsmd.getColumnCount ();
introwCount = 0;

// iterates through the result set and output them to standard out.
booleanoutputHeader = true;
while (rs.next()){
if(outputHeader){
for(inti = 1; i <= numCol; i++){
System.out.print(rsmd.getColumnName(i) + "\t");
 }
System.out.println();
outputHeader = false;
 }
for (inti=1; i<=numCol; ++i)
System.out.print (rs.getString (i) + "\t");
System.out.println ();
 ++rowCount;
 }//end while
stmt.close ();
returnrowCount;
 }//end executeQuery

/**
 * Method to close the physical connection if it is open.
 */
publicvoidcleanup(){
try{
if (this._connection != null){
this._connection.close ();
 }//end if
 }catch (SQLExceptione){
// ignored.
 }//end try
 }//end cleanup

/**
 * The main execution method
 *
 * @param args the command line arguments this inclues the <mysql|pgsql> <login file>
 */
publicstaticvoidmain (String[] args) {
if (args.length != 4) {
System.err.println (
"Usage: " +
"java [-classpath <classpath>] " +
EmbeddedSQL.class.getName () +
" <dbname> <port> <user> <passwd>");
return;
 }//end if

Greeting();
EmbeddedSQLesql = null;
try{
// use postgres JDBC driver.
Class.forName ("org.postgresql.Driver").newInstance ();
// instantiate the EmbeddedSQL object and creates a physical
// connection.
Stringdbname = args[0];
Stringdbport = args[1];
Stringuser = args[2];
Stringpasswd = args[3];
esql = newEmbeddedSQL (dbname, dbport, user, passwd);

booleankeepon = true;
while(keepon) {
// These are sample SQL statements
System.out.println("WELCOME! Are you an existing user?");
System.out.println("---------");
System.out.println("0. Yes I am!");
System.out.println("1. No, I need to register!");
System.out.println("9. < EXIT (Stop the program)");


switch (readChoice()){
case0: if(LogInQuery(esql) == 0 ) keepon = false; break;
case1: if(RegisterQuery(esql) == 0 ) keepon = false; break;
case9: return;
default : System.out.println("Unrecognized choice!"); break;
 }//end switch
 }//end while 

//Will need to turn keepon to false after logging in 

booleanhomescreen = true; 
while(homescreen)
 {
System.out.println("---------");
System.out.println("Welcome to MovieNet");
System.out.println("---------");
System.out.println("0. See list of movies");
System.out.println("1. Order a movie");
System.out.println("2. Rate a movie NOT WORKING");
System.out.println("3. Look at wall NOT WORKING");
System.out.println("4. Add to balance");
System.out.println("5. List favorite movie");
System.out.println("6. Add a favorite movie");

//these will only be visible if you are super user
if( superUser ){
System.out.println("\nSuper User Options:");
System.out.println("10. Register a new movie WORKING");
System.out.println("11. Delete an existing user NOT WORKING");
 }

/////////////////////////////////////////////////////////////CHANGES//////////
if( !superUser ) System.out.println("8. Login as SuperUser");
/////////////////////////////////////////////////////////////CHANGES//////////
System.out.println("9. < EXIT (Stop the program)");

switch (readChoice()){
case0: ListMovies(esql); break;
case1: OrderMovie(esql); break;
case2: break;
case3: break;
case4: IncreaseBalance(esql); break;
case5: ListFavs(esql); break;
case6: InsertFav(esql); break;
/////////////////////////////////////////////////////////////CHANGES//////////
case8: if( !superUser ) {LoginAsSuper(esql);}; break;
/////////////////////////////////////////////////////////////CHANGES//////////

case9: return;
case10: if( superUser ) {RegisterMovie(esql);}; break;
case11: if( superUser ) {deleteUser(esql);}; break;
default : System.out.println("Unrecognized choice!"); break;
 }


 }


 }catch(Exceptione) {
System.err.println (e.getMessage ());
 }finally{
// make sure to cleanup the created table and close the connection.
try{
if(esql != null) {
System.out.print("Disconnecting from database...");
esql.cleanup ();
System.out.println("Done\n\nBye !");
 }//end if
 }catch (Exceptione) {
// ignored.
 }//end try
 }//end try
 }//end main



publicstaticvoidListMovies(EmbeddedSQLesql)
	{
try{
Stringquery = "SELECT title FROM Video";
esql.executeStringQuery(query, 1);

 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
	}

publicstaticvoidListFavs(EmbeddedSQLesql)
	{
try{
Stringquery = "SELECT title FROM video, likes WHERE likes.user_id = '" + currentUser + 
"' AND likes.video_id = video.video_id";
esql.executeStringQuery(query, 1);
 }catch(Exceptione){
System.err.println (e.getMessage ());}
	}

publicstaticvoidInsertFav(EmbeddedSQLesql)
	{
try{
System.out.print("Enter title of move you would like to add as favorite: ");
Stringtitle = in.readLine();
Stringquery = "SELECT video_id FROM video WHERE title = '" + title + "'";

intvid_id = esql.getVidId(query);

System.out.println("GOT THE VID ID");
query = "INSERT INTO likes (user_id, video_id) VALUES ('" + currentUser + "', " + vid_id + ")";
esql.executeUpdate(query);

System.out.println("New favorite added");

 }catch(Exceptione){
System.err.println (e.getMessage ());} 
	}

publicstaticvoidOrderMovie(EmbeddedSQLesql)
	{
try{ 
System.out.print("Title of movie you would like to order: ");
Stringtitle = in.readLine();

//Grabbing title and will print it
Stringquery = "SELECT * FROM video WHERE title = ";
query += title + "'";

intprice = esql.executeCheckTitle(query);

query = "SELECT balance FROM users WHERE user_id ='" + currentUser + "';";
inttemp = esql.executeOrder(query, price);

if( temp == 1 )
System.out.println("Successfully ordered: " + title);
 }catch(Exceptione){
System.err.println (e.getMessage ());}
	}


publicstaticvoidIncreaseBalance(EmbeddedSQLesql)
	{
try{

System.out.print("How much would you like to add to balance?: ");
Stringstringbal = in.readLine();
intintbal = Integer.parseInt(stringbal);

if(intbal <= 0)
 {
System.out.println("Must input number greater than $0");
return;
 }

System.out.println("About to update");


Stringquery = "UPDATE users SET balance = balance +" + intbal + " WHERE user_id = '"
 + currentUser + "'";

esql.executeUpdate(query);

System.out.println("UPDATED");

Stringnewbalance = "SELECT balance FROM users WHERE user_id='" + currentUser + "'";
System.out.print("New Balance after addition: ");
esql.executeStringQuery(newbalance, 1); 

return;

 }catch(Exceptione){	
System.err.println (e.getMessage ());}
	}

// NOT FULLY IMPLEMENTED NEED TO GRAB VIDEO ID AND THEN RATE MOVIE
publicstaticvoidRateMovie(EmbeddedSQLesql)
	{
try{

System.out.print("Title of movie you would like to rate: ");
Stringtitle = in.readLine();
System.out.print("What would you like to rate the movie (0 - 10): ");
Stringrating = in.readLine();

Stringquery = "SELECT title FROM Video";

//String output = esql.executeStringQuery(query); //Cant use executeString This way!!!!

 }catch(Exceptione){
System.err.println (e.getMessage ());}
	}

publicstaticvoidGreeting(){
System.out.println(
"\n\n*******************************************************\n" +
" User Interface \n" +
"*******************************************************\n");
 }//end Greeting

publicstaticintLoginAsSuper(EmbeddedSQLesql)
 {
try{
System.out.println("Verifying that you are a Super User");
Stringquery = "SELECT COUNT(super_user_id) FROM super_user WHERE super_user_id='"
 + currentUser + "';";

intresultset = esql.executeLoginQuery(query);

if( resultset != 1 ){
System.out.println("I'm sorry but you are not a registered super user");
return1;
 }

elseif( resultset == 1 ){
System.out.println("Welcome super user " + currentUser);
superUser = true;
return0;
 }

 }catch(Exceptione){
System.err.println( e.getMessage() );
return1;
 }
return0;
 }//end LoginAsSuper

///SOME NEW FUNCTIONS HERE/////////////////////////////////////////////////////////////////////////////////
publicstaticintqueryGenre(EmbeddedSQLesql)
 {
intgenre_id = -1;
try{
//Added some Genre code here//////////////////////////////////////////////
System.out.println("What is the genre of this video?");
StringinputGenre = in.readLine();

StringgetGenre = "SELECT genre_id FROM genre WHERE genre_name='" + inputGenre + "';";
Statementstmt = esql._connection.createStatement();
ResultSetrs = stmt.executeQuery(getGenre);
ResultSetMetaDatarsmd = rs.getMetaData();

if( rs.next() ) genre_id = Integer.parseInt(rs.getString(1));

//only care if the genre doesn't already exist
else
 {
getGenre = "SELECT MAX(genre_id) FROM genre;";
rs = stmt.executeQuery(getGenre);
rsmd = rs.getMetaData();

if( rs.next() ) genre_id = Integer.parseInt(rs.getString(1))+1;
elsegenre_id = 1;

esql.executeUpdate( "INSERT INTO genre (genre_id, genre_name) VALUES (" + genre_id + ", '" + inputGenre + "');" );
rs.close();
 }

////////////////////////////////////////////////////////////////////////

 }catch(Exceptione){
System.err.println( e.getMessage() );
return -1;}
returngenre_id;
 }

publicstaticvoidcategorizeVideo(EmbeddedSQLesql, intvideo_id, intgenre_id )
 {
System.out.println("VIDEOID: " + video_id + " GenreID: " + genre_id);
try{
StringgetGenre = "SELECT COUNT(*) FROM categorize WHERE genre_id=" + genre_id + " AND video_id=" + video_id + ";";
Statementstmt = esql._connection.createStatement();
ResultSetrs = stmt.executeQuery(getGenre);
ResultSetMetaDatarsmd = rs.getMetaData();


//only care if this video_id isn't already in the list
if( !rs.next() || rs.getString(1).equals("0") )
esql.executeUpdate( "INSERT INTO categorize (video_id, genre_id) VALUES (" + video_id + ", " + genre_id + ");" );

 }catch(Exceptione){
System.err.println( e.getMessage() );}
 }

//////END OF SEGMENTED NEW FUNCTIONS///////////////////////////////////////////////////////////////////////

//Register a new video, creating new video_id, season_id, and series_id if necessary
publicstaticintRegisterMovie(EmbeddedSQLesql)
 {
try{
booleanisSeries = false;
System.out.println("Please enter the following information to register a new movie/series");

System.out.println("What is the title of this video?");
StringinputTitle = in.readLine();

///ADDEDD//////////////////////////////////////////////////////////
intgenre_id = queryGenre(esql);
if( genre_id == -1 ) System.out.println("Problem when creating genre");
////ADDEDD//////////////////////////////////////////////////////////


System.out.println("Is this video part of a series (y/n)?");
StringinputSeries = in.readLine();

StringinputSeasonNum = "";
StringinputEpNum = "";
StringinputYear = "";
intinputOnlinePrice;
intinputDvdPrice;
intinputRating;
intseries_id;
intseason_id;

//if this is a season get season info
if( inputSeries.equals("y") ){
isSeries = true;

System.out.println("What season number is this?");
inputSeasonNum = in.readLine();

System.out.println("What episode is this?");
inputEpNum = in.readLine();
 }

System.out.println("What year did this come out?");
inputYear = in.readLine();

System.out.println("How much should the online price be?");
inputOnlinePrice = Integer.parseInt(in.readLine());

System.out.println("How much should the dvd price be?");
inputDvdPrice = Integer.parseInt(in.readLine());

System.out.println("How would you rate this (0-10)?");
inputRating = Integer.parseInt(in.readLine());

//this will get the video id
Stringquery = "SELECT MAX(video_id) FROM video;";
Statementstmt = esql._connection.createStatement ();
ResultSetrs = stmt.executeQuery (query);
ResultSetMetaDatarsmd = rs.getMetaData ();
intnumCol = rsmd.getColumnCount ();
rs.next();
intvideo_id = Integer.parseInt(rs.getString(numCol)) + 1;
System.out.println(video_id);
stmt.close();

//this will check if the series is already in record if not then it adds it
if( isSeries ){
stmt = esql._connection.createStatement();
StringgetSeriesId = "SELECT series_id FROM series WHERE title='" + inputTitle + "'";
rs = stmt.executeQuery (getSeriesId);
rsmd = rs.getMetaData ();
numCol = rsmd.getColumnCount ();

//get the series id if it already exists
if(rs.next()) series_id = Integer.parseInt(rs.getString(numCol));
//if series id doesnt already exist then create a new series value
else{
getSeriesId = "SELECT MAX(series_id) FROM series;";
rs = stmt.executeQuery(getSeriesId);
rsmd = rs.getMetaData();
if(rs.next()) series_id = Integer.parseInt(rs.getString(1)) + 1;
elseseries_id = 1;

//add series information to database
StringinsertSeries = "INSERT INTO series (series_id, title) VALUES (" + series_id + ", '" + inputTitle + "');";
esql.executeUpdate(insertSeries);
 }

StringgetSeasonId = "SELECT season_id FROM season WHERE series_id='" + series_id + "' AND season_number='" + inputSeasonNum + "';";
rs = stmt.executeQuery(getSeasonId);
rsmd = rs.getMetaData();
numCol = rsmd.getColumnCount();

//if the season id already exists grab the season id
if(rs.next()) season_id = Integer.parseInt(rs.getString(1));
//if the season id doesn't exist then place insert new season value
else{
getSeasonId = "SELECT MAX(season_id) FROM season;";
rs = stmt.executeQuery(getSeasonId);
rsmd = rs.getMetaData();
if(rs.next()) season_id = Integer.parseInt(rs.getString(1)) + 1;
elseseason_id = 1;

StringinsertSeason = "INSERT INTO season (season_id, series_id, season_number ) VALUES (" + season_id + ", " + series_id + ", '" + inputSeasonNum + "');";
esql.executeUpdate(insertSeason);
 }

StringinsertVideo = "INSERT INTO video (video_id, title, year, online_price, dvd_price, rating, episode, season_id) VALUES " + 
"(" + video_id + ",'" + inputTitle + "', '" + inputYear + "', " + inputOnlinePrice + ", " + inputDvdPrice + ", " + inputRating + ", " + inputEpNum + ", " + season_id + ");";

esql.executeUpdate(insertVideo);

stmt.close();
 }//End if video is a series

elseif( !isSeries )
 {
StringinsertVideo = "INSERT INTO video (video_id, title, year, online_price, dvd_price, rating) VALUES " + 
"(" + video_id + ",'" + inputTitle + "', '" + inputYear + "', " + inputOnlinePrice + ", " + inputDvdPrice + ", " + inputRating + ");";
stmt = esql._connection.createStatement ();
esql.executeUpdate(insertVideo);
stmt.close();
 }//End if video is not series

///ADDDEDDD//////////////////////////////////
categorizeVideo(esql, video_id, genre_id);
///ADDEDDDD/////////////////////////////////
 }catch(Exceptione){
System.err.println( e.getMessage() );
return1;
 }
return0;
 }

///////////////////////////////////////////////////////////////START//////////
/////////////////////////////////////////////////////////////CHANGES//////////
//this will delete a user from the database (only if user is superUser)
publicstaticintdeleteUser(EmbeddedSQLesql)
	{
try{
System.out.println("In order to delete a specific movie instance you must specify the following information");
while( true )
 {
System.out.println("Please specify a user_id that you want to delete or press 9 to cancel");
StringinputDelUser = in.readLine();

if( inputDelUser.equals("9") ) break;
elseif( inputDelUser.equals(currentUser) )
 {
System.out.println("You cannot delete youself, sorry.");
continue;
 }

Stringquery = "Select COUNT(user_id) FROM users WHERE user_id='" + inputDelUser + "';";

Statementstmt = esql._connection.createStatement ();
ResultSetrs = stmt.executeQuery(query);
ResultSetMetaDatarsmd = rs.getMetaData();

//if the user does exist
if(rs.next() && Integer.parseInt(rs.getString(1)) == 1 )
 {
query = "DELETE FROM users WHERE user_id='" + inputDelUser + "';";
esql.executeUpdate(query);
System.out.println("Successfully deleted user: " + inputDelUser );
rs.close();
return0;
 }//end user exist

//if user doesnt exist
else
 {
System.out.println("Sorry that user doesn't exist. Please make sure you have the right user_id");
rs.close();
continue;
 }//end user doest exist
 }//end while

 }catch(Exceptione){
System.err.println( e.getMessage() );
return1;}
return0;
	}


/////////////////////////////////////////////////////////////CHANGES//////////
/////////////////////////////////////////////////////////////CHANGES//////////
//////////////////////////////////////////////////////////////END//////////////
/*
 * Reads the users choice given from the keyboard
 * @int
 **/
publicstaticintreadChoice() {
intinput;
// returns only if a correct value is given.
do {
System.out.print("Please make your choice: ");
try { // read the integer, parse it and break.
input = Integer.parseInt(in.readLine());
break;
 }catch (Exceptione) {
System.out.println("Your input is invalid!");
continue;
 }//end try
 }while (true);
returninput;
 }//end readChoice

publicstaticintLogInQuery(EmbeddedSQLesql){
try{
System.out.println("You are trying to Login as an existing user, enter your Username (Make sure it is only 9 chars long) or press 9 to go back");
//Keep asking the user to login if they want to log in
while( true ){
System.out.println("Please specify your username or press 9 to go back:");
StringinputUser = in.readLine();

if( inputUser.equals("9") ) return1; //exit the loop and go back to home screen

Stringquery = "SELECT COUNT(user_id) FROM users WHERE user_id='";
query += inputUser + "'";

intoutput = esql.executeLoginQuery (query);
System.out.println("This is output" + output);

//check if username is valid, if not 1 then either output = 0 or > 2 which is a problem
if( output != 1 ) {
System.out.println("Sorry, that username isn't in our records. Please try again.");
continue;
 }//end username not found

//if output is 1 then only 1 record with that user found (good)
elseif( output == 1 ){
System.out.println("Please specify your password:");
StringinputPass = in.readLine();
query = "SELECT COUNT(user_id) FROM users WHERE user_id='" + inputUser + "' AND password='" + inputPass + "'";

intoutput2 = esql.executeLoginQuery(query); //returns number of query occurances

//if password doesn't match records ask for username again
if( output2 != 1 ){
System.out.println("Sorry, the username and password don't match up. Please try again.");
continue;
 }//end password fail

//if password correct then save current user info and log them in
elseif( output2 == 1 ){
currentUser = inputUser;
System.out.println("Successfully logged in! Welcome " + currentUser + "!");
break;
 }//end password success
 }//end username found
 }//end user login prompt
 }catch(Exceptione){
System.err.println (e.getMessage ());
return1;
 }//end catch
return0;
 }//end LoginQuery

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
publicstaticintRegisterQuery(EmbeddedSQLesql){
System.out.println("You have selected to register yourself as a user. Please specify the following information.");
try{
System.out.println("Enter a username you want (Username must be 9 chars or less long):");
Stringinput = in.readLine();

//if( input.length() > 9 ) { System.out.println("Invalid Username (more than 9 chars)"); continue; }

Stringquery = "INSERT INTO Users (user_id, password, first_name, middle_name, last_name, e_mail, street1, street2, state, country, zipcode, balance)" +
"VALUES ('" + input + "','";

System.out.println("Enter your password:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your first name:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your middle name:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your last name:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your email:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your street1:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your street2:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your state:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your country:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your zipcode:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your balance:");
input = in.readLine();
query += input + "')";

esql.executeUpdate (query);

 }catch(Exceptione){
System.err.println (e.getMessage ());
return1;
 }//end catch
return0;
 }//end RegisterQuery
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

/* 
 public static void RegisterQuery(EmbeddedSQL esql){
 try{
 String query = "SELECT C.sid, COUNT(C.pid) FROM Suppliers S, Catalog C "
 + "WHERE C.sid=S.sid GROUP BY C.sid;";

 int rowCount = esql.executeQuery (query);
 System.out.println ("total row(s): " + rowCount);
 }catch(Exception e){
 System.err.println (e.getMessage ());
 }
 }//end Query1

 public static void Query2(EmbeddedSQL esql){
 try{
 String query = "SELECT C.sid, COUNT(C.pid) FROM Suppliers S, Catalog C "
 + "WHERE C.sid=S.sid GROUP BY C.sid HAVING COUNT(C.pid)>=3;";

 int rowCount = esql.executeQuery (query);
 System.out.println ("total row(s): " + rowCount);

 }catch(Exception e){
 System.err.println (e.getMessage ());
 }
 }//end Query2

 public static void Query3(EmbeddedSQL esql){
 try{
 String query = "SELECT sname, COUNT(C.pid) FROM Suppliers S, Catalog C "
 + "WHERE pid IN (SELECT pid FROM Parts WHERE color='Green') AND S.sid=C.sid "
 + "GROUP BY sname";

 int rowCount = esql.executeQuery (query);
 System.out.println ("total row(s): " + rowCount);

 }catch(Exception e){
 System.err.println (e.getMessage ());
 }
 }//end Query3

 public static void Query4(EmbeddedSQL esql){
 try{
 String query = "SELECT P.pname, MAX(C.cost) FROM Parts P, Catalog C "
 + "WHERE C.sid IN (SELECT temp.sid "
 + "FROM (SELECT sid, pid FROM Catalog WHERE pid IN (SELECT pid FROM Parts WHERE color='Red')) as temp, "
 + "(SELECT sid, pid FROM catalog WHERE PID IN (SELECT pid FROM Parts WHERE color='Green')) as temp2 "
 + "WHERE temp.sid = temp2.sid) "
 + "GROUP BY P.pname;";

 int rowCount = esql.executeQuery (query);
 System.out.println ("total row(s): " + rowCount);

 }catch(Exception e){
 System.err.println (e.getMessage ());
 }
 }//end Query4

 public static void Query5(EmbeddedSQL esql){
 try{
 String query = "SELECT DISTINCT pname FROM Parts "
 + "WHERE pid IN (SELECT pid FROM Catalog WHERE cost<";

 System.out.print("\tEnter cost: $");
 String input = in.readLine();
 query+=input;
 query += ");";

 int rowCount = esql.executeQuery (query);
 System.out.println ("total row(s): " + rowCount);
/*
 * MODIFIED BY JUSTIN KAHAL AND THOMAS DESMOND
 * SSID:860892022
 *
 * Template JAVA User Interface
 * =============================
 *
 * Database Management Systems
 * Department of Computer Science &amp; Engineering
 * University of California - Riverside
 *
 * Target DBMS: 'Postgres'
 *
 */


importjava.sql.DriverManager;
importjava.sql.Connection;
importjava.sql.Statement;
importjava.sql.ResultSet;
importjava.sql.ResultSetMetaData;
importjava.sql.SQLException;
importjava.io.File;
importjava.io.FileReader;
importjava.io.BufferedReader;
importjava.io.InputStreamReader;


///////////////////////////////////////////////////////
////////////////////////////////////////////////////
/////////	ADD THIS IMPORT ////////////////
importjava.util.Scanner;

/**
 * This class defines a simple embedded SQL utility class that is designed to
 * work with PostgreSQL JDBC drivers.
 *
 */
publicclassEmbeddedSQL {


publicstaticStringcurrentUser;
publicstaticbooleansuperUser = false;
// reference to physical database connection.
privateConnection_connection = null;

// handling the keyboard inputs through a BufferedReader
// This variable can be global for convenience.
staticBufferedReaderin = newBufferedReader(
newInputStreamReader(System.in));

/**
 * Creates a new instance of EmbeddedSQL
 *
 * @param hostname the MySQL or PostgreSQL server hostname
 * @param database the name of the database
 * @param username the user name used to login to the database
 * @param password the user login password
 * @throws java.sql.SQLException when failed to make a connection.
 */
publicEmbeddedSQL (Stringdbname, Stringdbport, Stringuser, Stringpasswd) throwsSQLException {

System.out.print("Connecting to database...");
try{
// constructs the connection URL
Stringurl = "jdbc:postgresql://localhost:" + dbport + "/" + dbname;
System.out.println ("Connection URL: " + url + "\n");

// obtain a physical connection
this._connection = DriverManager.getConnection(url, user, passwd);
System.out.println("Done");
 }catch (Exceptione){
System.err.println("Error - Unable to Connect to Database: " + e.getMessage() );
System.out.println("Make sure you started postgres on this machine");
System.exit(-1);
 }//end catch
 }//end EmbeddedSQL

/**
 * Method to execute an update SQL statement. Update SQL instructions
 * includes CREATE, INSERT, UPDATE, DELETE, and DROP.
 *
 * @param sql the input SQL string
 * @throws java.sql.SQLException when update failed
 */
publicvoidexecuteUpdate (Stringsql) throwsSQLException {
// creates a statement object
Statementstmt = this._connection.createStatement ();

// issues the update instruction
stmt.executeUpdate (sql);

// close the instruction
stmt.close ();
 }//end execute update 

intgetVidId(Stringquery)
 {
try{
// creates a statement objectv
Statementstmt = this._connection.createStatement (); 

// issues the query instruction
ResultSetrs = stmt.executeQuery(query);

while(rs.next())
 {
intvid_id = Integer.parseInt(rs.getString(1));
returnvid_id;
 }
return -1;
 }catch(Exceptione){
System.err.println (e.getMessage ());} 
return -1;
	}//end getVidID

publicintexecuteOrder (Stringquery, intprice) throwsSQLException
 {
// creates a statement objectv
Statementstmt = this._connection.createStatement ();

// issues the query instruction
ResultSetrs = stmt.executeQuery(query);

while(rs.next())
 {
intbalance = Integer.parseInt(rs.getString(1));
if(balance - price >= 0)
 {
System.out.println("You would have negative balance if movie is ordered you cannot order movie"); 
return -1;
 }
else
 {
Stringupdatebal = "UPDATE users SET balance = balance - " + price + " WHERE user_id = '" + currentUser + "'";
stmt.executeUpdate(updatebal); //I changed this part might be problematic
Stringnewbalance = "SELECT balance FROM users WHERE user_id='" + currentUser + "'";
System.out.print("New Balance if ordered: ");
executeStringQuery(newbalance, 1);
return1;
 }	
 }
return -1;
	}

publicvoidexecuteStringQuery (Stringquery, intline) throwsSQLException
	{
// creates a statement objectv
Statementstmt = this._connection.createStatement ();

// issues the query instruction
ResultSetrs = stmt.executeQuery (query);

/*
 ** obtains the metadata object for the returned result set. The metadata
 ** contains row and column info.
 */
System.out.println("--------------");
while(rs.next())
 {
System.out.println(rs.getString(line));
 }
	}

publicintexecuteCheckTitle (Stringquery) throwsSQLException
	{
try{
// creates a statement objectv
Statementstmt = this._connection.createStatement ();
System.out.println("Connection Success");

// issues the query instruction
ResultSetrs = stmt.executeQuery(query);
System.out.println("Query exectued success");

System.out.println("--------------");

while(rs.next())
 {
System.out.print("Are you sure you would like to order (yes or no): ");
System.out.println(rs.getString(2) + " Price: " + rs.getString(3));
Stringinput = in.readLine();
System.out.println();
 }

returnInteger.parseInt(rs.getString(3));
 }catch(Exceptione){
System.err.println (e.getMessage ());}
return -1;
	}

publicintexecuteLoginQuery (Stringquery) throwsSQLException
	{
// creates a statement objectv
Statementstmt = this._connection.createStatement ();

// issues the query instruction
ResultSetrs = stmt.executeQuery (query);

/*
 ** obtains the metadata object for the returned result set. The metadata
 ** contains row and column info.
 */
ResultSetMetaDatarsmd = rs.getMetaData ();
intnumCol = rsmd.getColumnCount ();
rs.next();
intresultset = Integer.parseInt(rs.getString(numCol));
System.out.println(resultset);

stmt.close();
returnresultset;
// iterates through the result set and output them to standard out.
/* boolean outputHeader = true;
 while (rs.next()){
 if(outputHeader){
 for(int i = 1; i <= numCol; i++){
 System.out.print(rsmd.getColumnName(i) + "\t");
 }
 System.out.println();
 outputHeader = false;
 }
 for (int i=1; i<=numCol; ++i)
 System.out.print (rs.getString (i) + "\t");
 System.out.println ();
 ++rowCount;
 }//end while
 stmt.close ();
 return rowCount;
 */
 }

/**
 * Method to execute an input query SQL instruction (i.e. SELECT). This
 * method issues the query to the DBMS and outputs the results to
 * standard out.
 *
 * @param query the input query string
 * @return the number of rows returned
 * @throws java.sql.SQLException when failed to execute the query
 */
publicintexecuteQuery (Stringquery) throwsSQLException {
// creates a statement objectv
Statementstmt = this._connection.createStatement ();

// issues the query instruction
ResultSetrs = stmt.executeQuery (query);

/*
 ** obtains the metadata object for the returned result set. The metadata
 ** contains row and column info.
 */
ResultSetMetaDatarsmd = rs.getMetaData ();
intnumCol = rsmd.getColumnCount ();
introwCount = 0;

// iterates through the result set and output them to standard out.
booleanoutputHeader = true;
while (rs.next()){
if(outputHeader){
for(inti = 1; i <= numCol; i++){
System.out.print(rsmd.getColumnName(i) + "\t");
 }
System.out.println();
outputHeader = false;
 }
for (inti=1; i<=numCol; ++i)
System.out.print (rs.getString (i) + "\t");
System.out.println ();
 ++rowCount;
 }//end while
stmt.close ();
returnrowCount;
 }//end executeQuery

/**
 * Method to close the physical connection if it is open.
 */
publicvoidcleanup(){
try{
if (this._connection != null){
this._connection.close ();
 }//end if
 }catch (SQLExceptione){
// ignored.
 }//end try
 }//end cleanup

/**
 * The main execution method
 *
 * @param args the command line arguments this inclues the <mysql|pgsql> <login file>
 */
publicstaticvoidmain (String[] args) {
if (args.length != 4) {
System.err.println (
"Usage: " +
"java [-classpath <classpath>] " +
EmbeddedSQL.class.getName () +
" <dbname> <port> <user> <passwd>");
return;
 }//end if

Greeting();
EmbeddedSQLesql = null;
try{
// use postgres JDBC driver.
Class.forName ("org.postgresql.Driver").newInstance ();
// instantiate the EmbeddedSQL object and creates a physical
// connection.
Stringdbname = args[0];
Stringdbport = args[1];
Stringuser = args[2];
Stringpasswd = args[3];
esql = newEmbeddedSQL (dbname, dbport, user, passwd);

booleankeepon = true;
while(keepon) {
// These are sample SQL statements
System.out.println("WELCOME! Are you an existing user?");
System.out.println("---------");
System.out.println("0. Yes I am!");
System.out.println("1. No, I need to register!");
System.out.println("9. < EXIT (Stop the program)");


switch (readChoice()){
case0: if(LogInQuery(esql) == 0 ) keepon = false; break;
case1: if(RegisterQuery(esql) == 0 ) keepon = false; break;
case9: return;
default : System.out.println("Unrecognized choice!"); break;
 }//end switch
 }//end while 

//Will need to turn keepon to false after logging in 

booleanhomescreen = true; 
while(homescreen)
 {
System.out.println("---------");
System.out.println("Welcome to MovieNet");
System.out.println("---------");
System.out.println("0. See list of movies");
System.out.println("1. Order a movie");
System.out.println("2. Rate a movie NOT WORKING");
System.out.println("3. Look at wall NOT WORKING");
System.out.println("4. Add to balance");
System.out.println("5. List favorite movie");
System.out.println("6. Add a favorite movie");

//these will only be visible if you are super user
if( superUser ){
System.out.println("\nSuper User Options:");
System.out.println("10. Register a new movie WORKING");
System.out.println("11. Delete an existing user NOT WORKING");
 }

/////////////////////////////////////////////////////////////CHANGES//////////
if( !superUser ) System.out.println("8. Login as SuperUser");
/////////////////////////////////////////////////////////////CHANGES//////////
System.out.println("9. < EXIT (Stop the program)");

switch (readChoice()){
case0: ListMovies(esql); break;
case1: OrderMovie(esql); break;
case2: break;
case3: break;
case4: IncreaseBalance(esql); break;
case5: ListFavs(esql); break;
case6: InsertFav(esql); break;
/////////////////////////////////////////////////////////////CHANGES//////////
case8: if( !superUser ) {LoginAsSuper(esql);}; break;
/////////////////////////////////////////////////////////////CHANGES//////////

case9: return;
case10: if( superUser ) {RegisterMovie(esql);}; break;
case11: if( superUser ) {deleteUser(esql);}; break;
default : System.out.println("Unrecognized choice!"); break;
 }


 }


 }catch(Exceptione) {
System.err.println (e.getMessage ());
 }finally{
// make sure to cleanup the created table and close the connection.
try{
if(esql != null) {
System.out.print("Disconnecting from database...");
esql.cleanup ();
System.out.println("Done\n\nBye !");
 }//end if
 }catch (Exceptione) {
// ignored.
 }//end try
 }//end try
 }//end main



publicstaticvoidListMovies(EmbeddedSQLesql)
	{
try{
Stringquery = "SELECT title FROM Video";
esql.executeStringQuery(query, 1);

 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
	}

publicstaticvoidListFavs(EmbeddedSQLesql)
	{
try{
Stringquery = "SELECT title FROM video, likes WHERE likes.user_id = '" + currentUser + 
"' AND likes.video_id = video.video_id";
esql.executeStringQuery(query, 1);
 }catch(Exceptione){
System.err.println (e.getMessage ());}
	}

publicstaticvoidInsertFav(EmbeddedSQLesql)
	{
try{
System.out.print("Enter title of move you would like to add as favorite: ");
Stringtitle = in.readLine();
Stringquery = "SELECT video_id FROM video WHERE title = '" + title + "'";

intvid_id = esql.getVidId(query);

System.out.println("GOT THE VID ID");
query = "INSERT INTO likes (user_id, video_id) VALUES ('" + currentUser + "', " + vid_id + ")";
esql.executeUpdate(query);

System.out.println("New favorite added");

 }catch(Exceptione){
System.err.println (e.getMessage ());} 
	}

publicstaticvoidOrderMovie(EmbeddedSQLesql)
	{
try{ 
System.out.print("Title of movie you would like to order: ");
Stringtitle = in.readLine();

//Grabbing title and will print it
Stringquery = "SELECT * FROM video WHERE title = ";
query += title + "'";

intprice = esql.executeCheckTitle(query);

query = "SELECT balance FROM users WHERE user_id ='" + currentUser + "';";
inttemp = esql.executeOrder(query, price);

if( temp == 1 )
System.out.println("Successfully ordered: " + title);
 }catch(Exceptione){
System.err.println (e.getMessage ());}
	}


publicstaticvoidIncreaseBalance(EmbeddedSQLesql)
	{
try{

System.out.print("How much would you like to add to balance?: ");
Stringstringbal = in.readLine();
intintbal = Integer.parseInt(stringbal);

if(intbal <= 0)
 {
System.out.println("Must input number greater than $0");
return;
 }

System.out.println("About to update");


Stringquery = "UPDATE users SET balance = balance +" + intbal + " WHERE user_id = '"
 + currentUser + "'";

esql.executeUpdate(query);

System.out.println("UPDATED");

Stringnewbalance = "SELECT balance FROM users WHERE user_id='" + currentUser + "'";
System.out.print("New Balance after addition: ");
esql.executeStringQuery(newbalance, 1); 

return;

 }catch(Exceptione){	
System.err.println (e.getMessage ());}
	}

// NOT FULLY IMPLEMENTED NEED TO GRAB VIDEO ID AND THEN RATE MOVIE
publicstaticvoidRateMovie(EmbeddedSQLesql)
	{
try{

System.out.print("Title of movie you would like to rate: ");
Stringtitle = in.readLine();
System.out.print("What would you like to rate the movie (0 - 10): ");
Stringrating = in.readLine();

Stringquery = "SELECT title FROM Video";

//String output = esql.executeStringQuery(query); //Cant use executeString This way!!!!

 }catch(Exceptione){
System.err.println (e.getMessage ());}
	}

publicstaticvoidGreeting(){
System.out.println(
"\n\n*******************************************************\n" +
" User Interface \n" +
"*******************************************************\n");
 }//end Greeting

publicstaticintLoginAsSuper(EmbeddedSQLesql)
 {
try{
System.out.println("Verifying that you are a Super User");
Stringquery = "SELECT COUNT(super_user_id) FROM super_user WHERE super_user_id='"
 + currentUser + "';";

intresultset = esql.executeLoginQuery(query);

if( resultset != 1 ){
System.out.println("I'm sorry but you are not a registered super user");
return1;
 }

elseif( resultset == 1 ){
System.out.println("Welcome super user " + currentUser);
superUser = true;
return0;
 }

 }catch(Exceptione){
System.err.println( e.getMessage() );
return1;
 }
return0;
 }//end LoginAsSuper

///SOME NEW FUNCTIONS HERE/////////////////////////////////////////////////////////////////////////////////
publicstaticintqueryGenre(EmbeddedSQLesql)
 {
intgenre_id = -1;
try{
//Added some Genre code here//////////////////////////////////////////////
System.out.println("What is the genre of this video?");
StringinputGenre = in.readLine();

StringgetGenre = "SELECT genre_id FROM genre WHERE genre_name='" + inputGenre + "';";
Statementstmt = esql._connection.createStatement();
ResultSetrs = stmt.executeQuery(getGenre);
ResultSetMetaDatarsmd = rs.getMetaData();

if( rs.next() ) genre_id = Integer.parseInt(rs.getString(1));

//only care if the genre doesn't already exist
else
 {
getGenre = "SELECT MAX(genre_id) FROM genre;";
rs = stmt.executeQuery(getGenre);
rsmd = rs.getMetaData();

if( rs.next() ) genre_id = Integer.parseInt(rs.getString(1))+1;
elsegenre_id = 1;

esql.executeUpdate( "INSERT INTO genre (genre_id, genre_name) VALUES (" + genre_id + ", '" + inputGenre + "');" );
rs.close();
 }

////////////////////////////////////////////////////////////////////////

 }catch(Exceptione){
System.err.println( e.getMessage() );
return -1;}
returngenre_id;
 }

publicstaticvoidcategorizeVideo(EmbeddedSQLesql, intvideo_id, intgenre_id )
 {
System.out.println("VIDEOID: " + video_id + " GenreID: " + genre_id);
try{
StringgetGenre = "SELECT COUNT(*) FROM categorize WHERE genre_id=" + genre_id + " AND video_id=" + video_id + ";";
Statementstmt = esql._connection.createStatement();
ResultSetrs = stmt.executeQuery(getGenre);
ResultSetMetaDatarsmd = rs.getMetaData();


//only care if this video_id isn't already in the list
if( !rs.next() || rs.getString(1).equals("0") )
esql.executeUpdate( "INSERT INTO categorize (video_id, genre_id) VALUES (" + video_id + ", " + genre_id + ");" );

 }catch(Exceptione){
System.err.println( e.getMessage() );}
 }

//////END OF SEGMENTED NEW FUNCTIONS///////////////////////////////////////////////////////////////////////

//Register a new video, creating new video_id, season_id, and series_id if necessary
publicstaticintRegisterMovie(EmbeddedSQLesql)
 {
try{
booleanisSeries = false;
System.out.println("Please enter the following information to register a new movie/series");

System.out.println("What is the title of this video?");
StringinputTitle = in.readLine();

///ADDEDD//////////////////////////////////////////////////////////
intgenre_id = queryGenre(esql);
if( genre_id == -1 ) System.out.println("Problem when creating genre");
////ADDEDD//////////////////////////////////////////////////////////


System.out.println("Is this video part of a series (y/n)?");
StringinputSeries = in.readLine();

StringinputSeasonNum = "";
StringinputEpNum = "";
StringinputYear = "";
intinputOnlinePrice;
intinputDvdPrice;
intinputRating;
intseries_id;
intseason_id;

//if this is a season get season info
if( inputSeries.equals("y") ){
isSeries = true;

System.out.println("What season number is this?");
inputSeasonNum = in.readLine();

System.out.println("What episode is this?");
inputEpNum = in.readLine();
 }

System.out.println("What year did this come out?");
inputYear = in.readLine();

System.out.println("How much should the online price be?");
inputOnlinePrice = Integer.parseInt(in.readLine());

System.out.println("How much should the dvd price be?");
inputDvdPrice = Integer.parseInt(in.readLine());

System.out.println("How would you rate this (0-10)?");
inputRating = Integer.parseInt(in.readLine());

//this will get the video id
Stringquery = "SELECT MAX(video_id) FROM video;";
Statementstmt = esql._connection.createStatement ();
ResultSetrs = stmt.executeQuery (query);
ResultSetMetaDatarsmd = rs.getMetaData ();
intnumCol = rsmd.getColumnCount ();
rs.next();
intvideo_id = Integer.parseInt(rs.getString(numCol)) + 1;
System.out.println(video_id);
stmt.close();

//this will check if the series is already in record if not then it adds it
if( isSeries ){
stmt = esql._connection.createStatement();
StringgetSeriesId = "SELECT series_id FROM series WHERE title='" + inputTitle + "'";
rs = stmt.executeQuery (getSeriesId);
rsmd = rs.getMetaData ();
numCol = rsmd.getColumnCount ();

//get the series id if it already exists
if(rs.next()) series_id = Integer.parseInt(rs.getString(numCol));
//if series id doesnt already exist then create a new series value
else{
getSeriesId = "SELECT MAX(series_id) FROM series;";
rs = stmt.executeQuery(getSeriesId);
rsmd = rs.getMetaData();
if(rs.next()) series_id = Integer.parseInt(rs.getString(1)) + 1;
elseseries_id = 1;

//add series information to database
StringinsertSeries = "INSERT INTO series (series_id, title) VALUES (" + series_id + ", '" + inputTitle + "');";
esql.executeUpdate(insertSeries);
 }

StringgetSeasonId = "SELECT season_id FROM season WHERE series_id='" + series_id + "' AND season_number='" + inputSeasonNum + "';";
rs = stmt.executeQuery(getSeasonId);
rsmd = rs.getMetaData();
numCol = rsmd.getColumnCount();

//if the season id already exists grab the season id
if(rs.next()) season_id = Integer.parseInt(rs.getString(1));
//if the season id doesn't exist then place insert new season value
else{
getSeasonId = "SELECT MAX(season_id) FROM season;";
rs = stmt.executeQuery(getSeasonId);
rsmd = rs.getMetaData();
if(rs.next()) season_id = Integer.parseInt(rs.getString(1)) + 1;
elseseason_id = 1;

StringinsertSeason = "INSERT INTO season (season_id, series_id, season_number ) VALUES (" + season_id + ", " + series_id + ", '" + inputSeasonNum + "');";
esql.executeUpdate(insertSeason);
 }

StringinsertVideo = "INSERT INTO video (video_id, title, year, online_price, dvd_price, rating, episode, season_id) VALUES " + 
"(" + video_id + ",'" + inputTitle + "', '" + inputYear + "', " + inputOnlinePrice + ", " + inputDvdPrice + ", " + inputRating + ", " + inputEpNum + ", " + season_id + ");";

esql.executeUpdate(insertVideo);

stmt.close();
 }//End if video is a series

elseif( !isSeries )
 {
StringinsertVideo = "INSERT INTO video (video_id, title, year, online_price, dvd_price, rating) VALUES " + 
"(" + video_id + ",'" + inputTitle + "', '" + inputYear + "', " + inputOnlinePrice + ", " + inputDvdPrice + ", " + inputRating + ");";
stmt = esql._connection.createStatement ();
esql.executeUpdate(insertVideo);
stmt.close();
 }//End if video is not series

///ADDDEDDD//////////////////////////////////
categorizeVideo(esql, video_id, genre_id);
///ADDEDDDD/////////////////////////////////
 }catch(Exceptione){
System.err.println( e.getMessage() );
return1;
 }
return0;
 }

///////////////////////////////////////////////////////////////START//////////
/////////////////////////////////////////////////////////////CHANGES//////////
//this will delete a user from the database (only if user is superUser)
publicstaticintdeleteUser(EmbeddedSQLesql)
	{
try{
System.out.println("In order to delete a specific movie instance you must specify the following information");
while( true )
 {
System.out.println("Please specify a user_id that you want to delete or press 9 to cancel");
StringinputDelUser = in.readLine();

if( inputDelUser.equals("9") ) break;
elseif( inputDelUser.equals(currentUser) )
 {
System.out.println("You cannot delete youself, sorry.");
continue;
 }

Stringquery = "Select COUNT(user_id) FROM users WHERE user_id='" + inputDelUser + "';";

Statementstmt = esql._connection.createStatement ();
ResultSetrs = stmt.executeQuery(query);
ResultSetMetaDatarsmd = rs.getMetaData();

//if the user does exist
if(rs.next() && Integer.parseInt(rs.getString(1)) == 1 )
 {
query = "DELETE FROM users WHERE user_id='" + inputDelUser + "';";
esql.executeUpdate(query);
System.out.println("Successfully deleted user: " + inputDelUser );
rs.close();
return0;
 }//end user exist

//if user doesnt exist
else
 {
System.out.println("Sorry that user doesn't exist. Please make sure you have the right user_id");
rs.close();
continue;
 }//end user doest exist
 }//end while

 }catch(Exceptione){
System.err.println( e.getMessage() );
return1;}
return0;
	}


/////////////////////////////////////////////////////////////CHANGES//////////
/////////////////////////////////////////////////////////////CHANGES//////////
//////////////////////////////////////////////////////////////END//////////////
/*
 * Reads the users choice given from the keyboard
 * @int
 **/
publicstaticintreadChoice() {
intinput;
// returns only if a correct value is given.
do {
System.out.print("Please make your choice: ");
try { // read the integer, parse it and break.
input = Integer.parseInt(in.readLine());
break;
 }catch (Exceptione) {
System.out.println("Your input is invalid!");
continue;
 }//end try
 }while (true);
returninput;
 }//end readChoice

publicstaticintLogInQuery(EmbeddedSQLesql){
try{
System.out.println("You are trying to Login as an existing user, enter your Username (Make sure it is only 9 chars long) or press 9 to go back");
//Keep asking the user to login if they want to log in
while( true ){
System.out.println("Please specify your username or press 9 to go back:");
StringinputUser = in.readLine();

if( inputUser.equals("9") ) return1; //exit the loop and go back to home screen

Stringquery = "SELECT COUNT(user_id) FROM users WHERE user_id='";
query += inputUser + "'";

intoutput = esql.executeLoginQuery (query);
System.out.println("This is output" + output);

//check if username is valid, if not 1 then either output = 0 or > 2 which is a problem
if( output != 1 ) {
System.out.println("Sorry, that username isn't in our records. Please try again.");
continue;
 }//end username not found

//if output is 1 then only 1 record with that user found (good)
elseif( output == 1 ){
System.out.println("Please specify your password:");
StringinputPass = in.readLine();
query = "SELECT COUNT(user_id) FROM users WHERE user_id='" + inputUser + "' AND password='" + inputPass + "'";

intoutput2 = esql.executeLoginQuery(query); //returns number of query occurances

//if password doesn't match records ask for username again
if( output2 != 1 ){
System.out.println("Sorry, the username and password don't match up. Please try again.");
continue;
 }//end password fail

//if password correct then save current user info and log them in
elseif( output2 == 1 ){
currentUser = inputUser;
System.out.println("Successfully logged in! Welcome " + currentUser + "!");
break;
 }//end password success
 }//end username found
 }//end user login prompt
 }catch(Exceptione){
System.err.println (e.getMessage ());
return1;
 }//end catch
return0;
 }//end LoginQuery

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
publicstaticintRegisterQuery(EmbeddedSQLesql){
System.out.println("You have selected to register yourself as a user. Please specify the following information.");
try{
System.out.println("Enter a username you want (Username must be 9 chars or less long):");
Stringinput = in.readLine();

//if( input.length() > 9 ) { System.out.println("Invalid Username (more than 9 chars)"); continue; }

Stringquery = "INSERT INTO Users (user_id, password, first_name, middle_name, last_name, e_mail, street1, street2, state, country, zipcode, balance)" +
"VALUES ('" + input + "','";

System.out.println("Enter your password:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your first name:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your middle name:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your last name:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your email:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your street1:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your street2:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your state:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your country:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your zipcode:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your balance:");
input = in.readLine();
query += input + "')";

esql.executeUpdate (query);

 }catch(Exceptione){
System.err.println (e.getMessage ());
return1;
 }//end catch
return0;
 }//end RegisterQuery
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

/* 
 public static void RegisterQuery(EmbeddedSQL esql){
 try{
 String query = "SELECT C.sid, COUNT(C.pid) FROM Suppliers S, Catalog C "
 + "WHERE C.sid=S.sid GROUP BY C.sid;";

 int rowCount = esql.executeQuery (query);
 System.out.println ("total row(s): " + rowCount);
 }catch(Exception e){
 System.err.println (e.getMessage ());
 }
 }//end Query1

 public static void Query2(EmbeddedSQL esql){
 try{
 String query = "SELECT C.sid, COUNT(C.pid) FROM Suppliers S, Catalog C "
 + "WHERE C.sid=S.sid GROUP BY C.sid HAVING COUNT(C.pid)>=3;";

 int rowCount = esql.executeQuery (query);
 System.out.println ("total row(s): " + rowCount);

 }catch(Exception e){
 System.err.println (e.getMessage ());
 }
 }//end Query2

 public static void Query3(EmbeddedSQL esql){
 try{
 String query = "SELECT sname, COUNT(C.pid) FROM Suppliers S, Catalog C "
 + "WHERE pid IN (SELECT pid FROM Parts WHERE color='Green') AND S.sid=C.sid "
 + "GROUP BY sname";

 int rowCount = esql.executeQuery (query);
 System.out.println ("total row(s): " + rowCount);

 }catch(Exception e){
 System.err.println (e.getMessage ());
 }
 }//end Query3

 public static void Query4(EmbeddedSQL esql){
 try{
 String query = "SELECT P.pname, MAX(C.cost) FROM Parts P, Catalog C "
 + "WHERE C.sid IN (SELECT temp.sid "
 + "FROM (SELECT sid, pid FROM Catalog WHERE pid IN (SELECT pid FROM Parts WHERE color='Red')) as temp, "
 + "(SELECT sid, pid FROM catalog WHERE PID IN (SELECT pid FROM Parts WHERE color='Green')) as temp2 "
 + "WHERE temp.sid = temp2.sid) "
 + "GROUP BY P.pname;";

 int rowCount = esql.executeQuery (query);
 System.out.println ("total row(s): " + rowCount);

 }catch(Exception e){
 System.err.println (e.getMessage ());
 }
 }//end Query4

 public static void Query5(EmbeddedSQL esql){
 try{
 String query = "SELECT DISTINCT pname FROM Parts "
 + "WHERE pid IN (SELECT pid FROM Catalog WHERE cost<";

 System.out.print("\tEnter cost: $");
 String input = in.readLine();
 query+=input;
 query += ");";

 int rowCount = esql.executeQuery (query);
 System.out.println ("total row(s): " + rowCount);
/*
 * MODIFIED BY JUSTIN KAHAL AND THOMAS DESMOND
 * SSID:860892022
 *
 * Template JAVA User Interface
 * =============================
 *
 * Database Management Systems
 * Department of Computer Science &amp; Engineering
 * University of California - Riverside
 *
 * Target DBMS: 'Postgres'
 *
 */


importjava.sql.DriverManager;
importjava.sql.Connection;
importjava.sql.Statement;
importjava.sql.ResultSet;
importjava.sql.ResultSetMetaData;
importjava.sql.SQLException;
importjava.io.File;
importjava.io.FileReader;
importjava.io.BufferedReader;
importjava.io.InputStreamReader;


///////////////////////////////////////////////////////
////////////////////////////////////////////////////
/////////	ADD THIS IMPORT ////////////////
importjava.util.Scanner;

/**
 * This class defines a simple embedded SQL utility class that is designed to
 * work with PostgreSQL JDBC drivers.
 *
 */
publicclassEmbeddedSQL {


publicstaticStringcurrentUser;
publicstaticbooleansuperUser = false;
// reference to physical database connection.
privateConnection_connection = null;

// handling the keyboard inputs through a BufferedReader
// This variable can be global for convenience.
staticBufferedReaderin = newBufferedReader(
newInputStreamReader(System.in));

/**
 * Creates a new instance of EmbeddedSQL
 *
 * @param hostname the MySQL or PostgreSQL server hostname
 * @param database the name of the database
 * @param username the user name used to login to the database
 * @param password the user login password
 * @throws java.sql.SQLException when failed to make a connection.
 */
publicEmbeddedSQL (Stringdbname, Stringdbport, Stringuser, Stringpasswd) throwsSQLException {

System.out.print("Connecting to database...");
try{
// constructs the connection URL
Stringurl = "jdbc:postgresql://localhost:" + dbport + "/" + dbname;
System.out.println ("Connection URL: " + url + "\n");

// obtain a physical connection
this._connection = DriverManager.getConnection(url, user, passwd);
System.out.println("Done");
 }catch (Exceptione){
System.err.println("Error - Unable to Connect to Database: " + e.getMessage() );
System.out.println("Make sure you started postgres on this machine");
System.exit(-1);
 }//end catch
 }//end EmbeddedSQL

/**
 * Method to execute an update SQL statement. Update SQL instructions
 * includes CREATE, INSERT, UPDATE, DELETE, and DROP.
 *
 * @param sql the input SQL string
 * @throws java.sql.SQLException when update failed
 */
publicvoidexecuteUpdate (Stringsql) throwsSQLException {
// creates a statement object
Statementstmt = this._connection.createStatement ();

// issues the update instruction
stmt.executeUpdate (sql);

// close the instruction
stmt.close ();
 }//end execute update 

intgetVidId(Stringquery)
 {
try{
// creates a statement objectv
Statementstmt = this._connection.createStatement (); 

// issues the query instruction
ResultSetrs = stmt.executeQuery(query);

while(rs.next())
 {
intvid_id = Integer.parseInt(rs.getString(1));
returnvid_id;
 }
return -1;
 }catch(Exceptione){
System.err.println (e.getMessage ());} 
return -1;
	}//end getVidID

publicintexecuteOrder (Stringquery, intprice) throwsSQLException
 {
// creates a statement objectv
Statementstmt = this._connection.createStatement ();

// issues the query instruction
ResultSetrs = stmt.executeQuery(query);

while(rs.next())
 {
intbalance = Integer.parseInt(rs.getString(1));
if(balance - price >= 0)
 {
System.out.println("You would have negative balance if movie is ordered you cannot order movie"); 
return -1;
 }
else
 {
Stringupdatebal = "UPDATE users SET balance = balance - " + price + " WHERE user_id = '" + currentUser + "'";
stmt.executeUpdate(updatebal); //I changed this part might be problematic
Stringnewbalance = "SELECT balance FROM users WHERE user_id='" + currentUser + "'";
System.out.print("New Balance if ordered: ");
executeStringQuery(newbalance, 1);
return1;
 }	
 }
return -1;
	}

publicvoidexecuteStringQuery (Stringquery, intline) throwsSQLException
	{
// creates a statement objectv
Statementstmt = this._connection.createStatement ();

// issues the query instruction
ResultSetrs = stmt.executeQuery (query);

/*
 ** obtains the metadata object for the returned result set. The metadata
 ** contains row and column info.
 */
System.out.println("--------------");
while(rs.next())
 {
System.out.println(rs.getString(line));
 }
	}

publicintexecuteCheckTitle (Stringquery) throwsSQLException
	{
try{
// creates a statement objectv
Statementstmt = this._connection.createStatement ();
System.out.println("Connection Success");

// issues the query instruction
ResultSetrs = stmt.executeQuery(query);
System.out.println("Query exectued success");

System.out.println("--------------");

while(rs.next())
 {
System.out.print("Are you sure you would like to order (yes or no): ");
System.out.println(rs.getString(2) + " Price: " + rs.getString(3));
Stringinput = in.readLine();
System.out.println();
 }

returnInteger.parseInt(rs.getString(3));
 }catch(Exceptione){
System.err.println (e.getMessage ());}
return -1;
	}

publicintexecuteLoginQuery (Stringquery) throwsSQLException
	{
// creates a statement objectv
Statementstmt = this._connection.createStatement ();

// issues the query instruction
ResultSetrs = stmt.executeQuery (query);

/*
 ** obtains the metadata object for the returned result set. The metadata
 ** contains row and column info.
 */
ResultSetMetaDatarsmd = rs.getMetaData ();
intnumCol = rsmd.getColumnCount ();
rs.next();
intresultset = Integer.parseInt(rs.getString(numCol));
System.out.println(resultset);

stmt.close();
returnresultset;
// iterates through the result set and output them to standard out.
/* boolean outputHeader = true;
 while (rs.next()){
 if(outputHeader){
 for(int i = 1; i <= numCol; i++){
 System.out.print(rsmd.getColumnName(i) + "\t");
 }
 System.out.println();
 outputHeader = false;
 }
 for (int i=1; i<=numCol; ++i)
 System.out.print (rs.getString (i) + "\t");
 System.out.println ();
 ++rowCount;
 }//end while
 stmt.close ();
 return rowCount;
 */
 }

/**
 * Method to execute an input query SQL instruction (i.e. SELECT). This
 * method issues the query to the DBMS and outputs the results to
 * standard out.
 *
 * @param query the input query string
 * @return the number of rows returned
 * @throws java.sql.SQLException when failed to execute the query
 */
publicintexecuteQuery (Stringquery) throwsSQLException {
// creates a statement objectv
Statementstmt = this._connection.createStatement ();

// issues the query instruction
ResultSetrs = stmt.executeQuery (query);

/*
 ** obtains the metadata object for the returned result set. The metadata
 ** contains row and column info.
 */
ResultSetMetaDatarsmd = rs.getMetaData ();
intnumCol = rsmd.getColumnCount ();
introwCount = 0;

// iterates through the result set and output them to standard out.
booleanoutputHeader = true;
while (rs.next()){
if(outputHeader){
for(inti = 1; i <= numCol; i++){
System.out.print(rsmd.getColumnName(i) + "\t");
 }
System.out.println();
outputHeader = false;
 }
for (inti=1; i<=numCol; ++i)
System.out.print (rs.getString (i) + "\t");
System.out.println ();
 ++rowCount;
 }//end while
stmt.close ();
returnrowCount;
 }//end executeQuery

/**
 * Method to close the physical connection if it is open.
 */
publicvoidcleanup(){
try{
if (this._connection != null){
this._connection.close ();
 }//end if
 }catch (SQLExceptione){
// ignored.
 }//end try
 }//end cleanup

/**
 * The main execution method
 *
 * @param args the command line arguments this inclues the <mysql|pgsql> <login file>
 */
publicstaticvoidmain (String[] args) {
if (args.length != 4) {
System.err.println (
"Usage: " +
"java [-classpath <classpath>] " +
EmbeddedSQL.class.getName () +
" <dbname> <port> <user> <passwd>");
return;
 }//end if

Greeting();
EmbeddedSQLesql = null;
try{
// use postgres JDBC driver.
Class.forName ("org.postgresql.Driver").newInstance ();
// instantiate the EmbeddedSQL object and creates a physical
// connection.
Stringdbname = args[0];
Stringdbport = args[1];
Stringuser = args[2];
Stringpasswd = args[3];
esql = newEmbeddedSQL (dbname, dbport, user, passwd);

booleankeepon = true;
while(keepon) {
// These are sample SQL statements
System.out.println("WELCOME! Are you an existing user?");
System.out.println("---------");
System.out.println("0. Yes I am!");
System.out.println("1. No, I need to register!");
System.out.println("9. < EXIT (Stop the program)");


switch (readChoice()){
case0: if(LogInQuery(esql) == 0 ) keepon = false; break;
case1: if(RegisterQuery(esql) == 0 ) keepon = false; break;
case9: return;
default : System.out.println("Unrecognized choice!"); break;
 }//end switch
 }//end while 

//Will need to turn keepon to false after logging in 

booleanhomescreen = true; 
while(homescreen)
 {
System.out.println("---------");
System.out.println("Welcome to MovieNet");
System.out.println("---------");
System.out.println("0. See list of movies");
System.out.println("1. Order a movie");
System.out.println("2. Rate a movie NOT WORKING");
System.out.println("3. Look at wall NOT WORKING");
System.out.println("4. Add to balance");
System.out.println("5. List favorite movie");
System.out.println("6. Add a favorite movie");

//these will only be visible if you are super user
if( superUser ){
System.out.println("\nSuper User Options:");
System.out.println("10. Register a new movie WORKING");
System.out.println("11. Delete an existing user NOT WORKING");
 }

/////////////////////////////////////////////////////////////CHANGES//////////
if( !superUser ) System.out.println("8. Login as SuperUser");
/////////////////////////////////////////////////////////////CHANGES//////////
System.out.println("9. < EXIT (Stop the program)");

switch (readChoice()){
case0: ListMovies(esql); break;
case1: OrderMovie(esql); break;
case2: break;
case3: break;
case4: IncreaseBalance(esql); break;
case5: ListFavs(esql); break;
case6: InsertFav(esql); break;
/////////////////////////////////////////////////////////////CHANGES//////////
case8: if( !superUser ) {LoginAsSuper(esql);}; break;
/////////////////////////////////////////////////////////////CHANGES//////////

case9: return;
case10: if( superUser ) {RegisterMovie(esql);}; break;
case11: if( superUser ) {deleteUser(esql);}; break;
default : System.out.println("Unrecognized choice!"); break;
 }


 }


 }catch(Exceptione) {
System.err.println (e.getMessage ());
 }finally{
// make sure to cleanup the created table and close the connection.
try{
if(esql != null) {
System.out.print("Disconnecting from database...");
esql.cleanup ();
System.out.println("Done\n\nBye !");
 }//end if
 }catch (Exceptione) {
// ignored.
 }//end try
 }//end try
 }//end main



publicstaticvoidListMovies(EmbeddedSQLesql)
	{
try{
Stringquery = "SELECT title FROM Video";
esql.executeStringQuery(query, 1);

 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
	}

publicstaticvoidListFavs(EmbeddedSQLesql)
	{
try{
Stringquery = "SELECT title FROM video, likes WHERE likes.user_id = '" + currentUser + 
"' AND likes.video_id = video.video_id";
esql.executeStringQuery(query, 1);
 }catch(Exceptione){
System.err.println (e.getMessage ());}
	}

publicstaticvoidInsertFav(EmbeddedSQLesql)
	{
try{
System.out.print("Enter title of move you would like to add as favorite: ");
Stringtitle = in.readLine();
Stringquery = "SELECT video_id FROM video WHERE title = '" + title + "'";

intvid_id = esql.getVidId(query);

System.out.println("GOT THE VID ID");
query = "INSERT INTO likes (user_id, video_id) VALUES ('" + currentUser + "', " + vid_id + ")";
esql.executeUpdate(query);

System.out.println("New favorite added");

 }catch(Exceptione){
System.err.println (e.getMessage ());} 
	}

publicstaticvoidOrderMovie(EmbeddedSQLesql)
	{
try{ 
System.out.print("Title of movie you would like to order: ");
Stringtitle = in.readLine();

//Grabbing title and will print it
Stringquery = "SELECT * FROM video WHERE title = ";
query += title + "'";

intprice = esql.executeCheckTitle(query);

query = "SELECT balance FROM users WHERE user_id ='" + currentUser + "';";
inttemp = esql.executeOrder(query, price);

if( temp == 1 )
System.out.println("Successfully ordered: " + title);
 }catch(Exceptione){
System.err.println (e.getMessage ());}
	}


publicstaticvoidIncreaseBalance(EmbeddedSQLesql)
	{
try{

System.out.print("How much would you like to add to balance?: ");
Stringstringbal = in.readLine();
intintbal = Integer.parseInt(stringbal);

if(intbal <= 0)
 {
System.out.println("Must input number greater than $0");
return;
 }

System.out.println("About to update");


Stringquery = "UPDATE users SET balance = balance +" + intbal + " WHERE user_id = '"
 + currentUser + "'";

esql.executeUpdate(query);

System.out.println("UPDATED");

Stringnewbalance = "SELECT balance FROM users WHERE user_id='" + currentUser + "'";
System.out.print("New Balance after addition: ");
esql.executeStringQuery(newbalance, 1); 

return;

 }catch(Exceptione){	
System.err.println (e.getMessage ());}
	}

// NOT FULLY IMPLEMENTED NEED TO GRAB VIDEO ID AND THEN RATE MOVIE
publicstaticvoidRateMovie(EmbeddedSQLesql)
	{
try{

System.out.print("Title of movie you would like to rate: ");
Stringtitle = in.readLine();
System.out.print("What would you like to rate the movie (0 - 10): ");
Stringrating = in.readLine();

Stringquery = "SELECT title FROM Video";

//String output = esql.executeStringQuery(query); //Cant use executeString This way!!!!

 }catch(Exceptione){
System.err.println (e.getMessage ());}
	}

publicstaticvoidGreeting(){
System.out.println(
"\n\n*******************************************************\n" +
" User Interface \n" +
"*******************************************************\n");
 }//end Greeting

publicstaticintLoginAsSuper(EmbeddedSQLesql)
 {
try{
System.out.println("Verifying that you are a Super User");
Stringquery = "SELECT COUNT(super_user_id) FROM super_user WHERE super_user_id='"
 + currentUser + "';";

intresultset = esql.executeLoginQuery(query);

if( resultset != 1 ){
System.out.println("I'm sorry but you are not a registered super user");
return1;
 }

elseif( resultset == 1 ){
System.out.println("Welcome super user " + currentUser);
superUser = true;
return0;
 }

 }catch(Exceptione){
System.err.println( e.getMessage() );
return1;
 }
return0;
 }//end LoginAsSuper

///SOME NEW FUNCTIONS HERE/////////////////////////////////////////////////////////////////////////////////
publicstaticintqueryGenre(EmbeddedSQLesql)
 {
intgenre_id = -1;
try{
//Added some Genre code here//////////////////////////////////////////////
System.out.println("What is the genre of this video?");
StringinputGenre = in.readLine();

StringgetGenre = "SELECT genre_id FROM genre WHERE genre_name='" + inputGenre + "';";
Statementstmt = esql._connection.createStatement();
ResultSetrs = stmt.executeQuery(getGenre);
ResultSetMetaDatarsmd = rs.getMetaData();

if( rs.next() ) genre_id = Integer.parseInt(rs.getString(1));

//only care if the genre doesn't already exist
else
 {
getGenre = "SELECT MAX(genre_id) FROM genre;";
rs = stmt.executeQuery(getGenre);
rsmd = rs.getMetaData();

if( rs.next() ) genre_id = Integer.parseInt(rs.getString(1))+1;
elsegenre_id = 1;

esql.executeUpdate( "INSERT INTO genre (genre_id, genre_name) VALUES (" + genre_id + ", '" + inputGenre + "');" );
rs.close();
 }

////////////////////////////////////////////////////////////////////////

 }catch(Exceptione){
System.err.println( e.getMessage() );
return -1;}
returngenre_id;
 }

publicstaticvoidcategorizeVideo(EmbeddedSQLesql, intvideo_id, intgenre_id )
 {
System.out.println("VIDEOID: " + video_id + " GenreID: " + genre_id);
try{
StringgetGenre = "SELECT COUNT(*) FROM categorize WHERE genre_id=" + genre_id + " AND video_id=" + video_id + ";";
Statementstmt = esql._connection.createStatement();
ResultSetrs = stmt.executeQuery(getGenre);
ResultSetMetaDatarsmd = rs.getMetaData();


//only care if this video_id isn't already in the list
if( !rs.next() || rs.getString(1).equals("0") )
esql.executeUpdate( "INSERT INTO categorize (video_id, genre_id) VALUES (" + video_id + ", " + genre_id + ");" );

 }catch(Exceptione){
System.err.println( e.getMessage() );}
 }

//////END OF SEGMENTED NEW FUNCTIONS///////////////////////////////////////////////////////////////////////

//Register a new video, creating new video_id, season_id, and series_id if necessary
publicstaticintRegisterMovie(EmbeddedSQLesql)
 {
try{
booleanisSeries = false;
System.out.println("Please enter the following information to register a new movie/series");

System.out.println("What is the title of this video?");
StringinputTitle = in.readLine();

///ADDEDD//////////////////////////////////////////////////////////
intgenre_id = queryGenre(esql);
if( genre_id == -1 ) System.out.println("Problem when creating genre");
////ADDEDD//////////////////////////////////////////////////////////


System.out.println("Is this video part of a series (y/n)?");
StringinputSeries = in.readLine();

StringinputSeasonNum = "";
StringinputEpNum = "";
StringinputYear = "";
intinputOnlinePrice;
intinputDvdPrice;
intinputRating;
intseries_id;
intseason_id;

//if this is a season get season info
if( inputSeries.equals("y") ){
isSeries = true;

System.out.println("What season number is this?");
inputSeasonNum = in.readLine();

System.out.println("What episode is this?");
inputEpNum = in.readLine();
 }

System.out.println("What year did this come out?");
inputYear = in.readLine();

System.out.println("How much should the online price be?");
inputOnlinePrice = Integer.parseInt(in.readLine());

System.out.println("How much should the dvd price be?");
inputDvdPrice = Integer.parseInt(in.readLine());

System.out.println("How would you rate this (0-10)?");
inputRating = Integer.parseInt(in.readLine());

//this will get the video id
Stringquery = "SELECT MAX(video_id) FROM video;";
Statementstmt = esql._connection.createStatement ();
ResultSetrs = stmt.executeQuery (query);
ResultSetMetaDatarsmd = rs.getMetaData ();
intnumCol = rsmd.getColumnCount ();
rs.next();
intvideo_id = Integer.parseInt(rs.getString(numCol)) + 1;
System.out.println(video_id);
stmt.close();

//this will check if the series is already in record if not then it adds it
if( isSeries ){
stmt = esql._connection.createStatement();
StringgetSeriesId = "SELECT series_id FROM series WHERE title='" + inputTitle + "'";
rs = stmt.executeQuery (getSeriesId);
rsmd = rs.getMetaData ();
numCol = rsmd.getColumnCount ();

//get the series id if it already exists
if(rs.next()) series_id = Integer.parseInt(rs.getString(numCol));
//if series id doesnt already exist then create a new series value
else{
getSeriesId = "SELECT MAX(series_id) FROM series;";
rs = stmt.executeQuery(getSeriesId);
rsmd = rs.getMetaData();
if(rs.next()) series_id = Integer.parseInt(rs.getString(1)) + 1;
elseseries_id = 1;

//add series information to database
StringinsertSeries = "INSERT INTO series (series_id, title) VALUES (" + series_id + ", '" + inputTitle + "');";
esql.executeUpdate(insertSeries);
 }

StringgetSeasonId = "SELECT season_id FROM season WHERE series_id='" + series_id + "' AND season_number='" + inputSeasonNum + "';";
rs = stmt.executeQuery(getSeasonId);
rsmd = rs.getMetaData();
numCol = rsmd.getColumnCount();

//if the season id already exists grab the season id
if(rs.next()) season_id = Integer.parseInt(rs.getString(1));
//if the season id doesn't exist then place insert new season value
else{
getSeasonId = "SELECT MAX(season_id) FROM season;";
rs = stmt.executeQuery(getSeasonId);
rsmd = rs.getMetaData();
if(rs.next()) season_id = Integer.parseInt(rs.getString(1)) + 1;
elseseason_id = 1;

StringinsertSeason = "INSERT INTO season (season_id, series_id, season_number ) VALUES (" + season_id + ", " + series_id + ", '" + inputSeasonNum + "');";
esql.executeUpdate(insertSeason);
 }

StringinsertVideo = "INSERT INTO video (video_id, title, year, online_price, dvd_price, rating, episode, season_id) VALUES " + 
"(" + video_id + ",'" + inputTitle + "', '" + inputYear + "', " + inputOnlinePrice + ", " + inputDvdPrice + ", " + inputRating + ", " + inputEpNum + ", " + season_id + ");";

esql.executeUpdate(insertVideo);

stmt.close();
 }//End if video is a series

elseif( !isSeries )
 {
StringinsertVideo = "INSERT INTO video (video_id, title, year, online_price, dvd_price, rating) VALUES " + 
"(" + video_id + ",'" + inputTitle + "', '" + inputYear + "', " + inputOnlinePrice + ", " + inputDvdPrice + ", " + inputRating + ");";
stmt = esql._connection.createStatement ();
esql.executeUpdate(insertVideo);
stmt.close();
 }//End if video is not series

///ADDDEDDD//////////////////////////////////
categorizeVideo(esql, video_id, genre_id);
///ADDEDDDD/////////////////////////////////
 }catch(Exceptione){
System.err.println( e.getMessage() );
return1;
 }
return0;
 }

///////////////////////////////////////////////////////////////START//////////
/////////////////////////////////////////////////////////////CHANGES//////////
//this will delete a user from the database (only if user is superUser)
publicstaticintdeleteUser(EmbeddedSQLesql)
	{
try{
System.out.println("In order to delete a specific movie instance you must specify the following information");
while( true )
 {
System.out.println("Please specify a user_id that you want to delete or press 9 to cancel");
StringinputDelUser = in.readLine();

if( inputDelUser.equals("9") ) break;
elseif( inputDelUser.equals(currentUser) )
 {
System.out.println("You cannot delete youself, sorry.");
continue;
 }

Stringquery = "Select COUNT(user_id) FROM users WHERE user_id='" + inputDelUser + "';";

Statementstmt = esql._connection.createStatement ();
ResultSetrs = stmt.executeQuery(query);
ResultSetMetaDatarsmd = rs.getMetaData();

//if the user does exist
if(rs.next() && Integer.parseInt(rs.getString(1)) == 1 )
 {
query = "DELETE FROM users WHERE user_id='" + inputDelUser + "';";
esql.executeUpdate(query);
System.out.println("Successfully deleted user: " + inputDelUser );
rs.close();
return0;
 }//end user exist

//if user doesnt exist
else
 {
System.out.println("Sorry that user doesn't exist. Please make sure you have the right user_id");
rs.close();
continue;
 }//end user doest exist
 }//end while

 }catch(Exceptione){
System.err.println( e.getMessage() );
return1;}
return0;
	}


/////////////////////////////////////////////////////////////CHANGES//////////
/////////////////////////////////////////////////////////////CHANGES//////////
//////////////////////////////////////////////////////////////END//////////////
/*
 * Reads the users choice given from the keyboard
 * @int
 **/
publicstaticintreadChoice() {
intinput;
// returns only if a correct value is given.
do {
System.out.print("Please make your choice: ");
try { // read the integer, parse it and break.
input = Integer.parseInt(in.readLine());
break;
 }catch (Exceptione) {
System.out.println("Your input is invalid!");
continue;
 }//end try
 }while (true);
returninput;
 }//end readChoice

publicstaticintLogInQuery(EmbeddedSQLesql){
try{
System.out.println("You are trying to Login as an existing user, enter your Username (Make sure it is only 9 chars long) or press 9 to go back");
//Keep asking the user to login if they want to log in
while( true ){
System.out.println("Please specify your username or press 9 to go back:");
StringinputUser = in.readLine();

if( inputUser.equals("9") ) return1; //exit the loop and go back to home screen

Stringquery = "SELECT COUNT(user_id) FROM users WHERE user_id='";
query += inputUser + "'";

intoutput = esql.executeLoginQuery (query);
System.out.println("This is output" + output);

//check if username is valid, if not 1 then either output = 0 or > 2 which is a problem
if( output != 1 ) {
System.out.println("Sorry, that username isn't in our records. Please try again.");
continue;
 }//end username not found

//if output is 1 then only 1 record with that user found (good)
elseif( output == 1 ){
System.out.println("Please specify your password:");
StringinputPass = in.readLine();
query = "SELECT COUNT(user_id) FROM users WHERE user_id='" + inputUser + "' AND password='" + inputPass + "'";

intoutput2 = esql.executeLoginQuery(query); //returns number of query occurances

//if password doesn't match records ask for username again
if( output2 != 1 ){
System.out.println("Sorry, the username and password don't match up. Please try again.");
continue;
 }//end password fail

//if password correct then save current user info and log them in
elseif( output2 == 1 ){
currentUser = inputUser;
System.out.println("Successfully logged in! Welcome " + currentUser + "!");
break;
 }//end password success
 }//end username found
 }//end user login prompt
 }catch(Exceptione){
System.err.println (e.getMessage ());
return1;
 }//end catch
return0;
 }//end LoginQuery

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
publicstaticintRegisterQuery(EmbeddedSQLesql){
System.out.println("You have selected to register yourself as a user. Please specify the following information.");
try{
System.out.println("Enter a username you want (Username must be 9 chars or less long):");
Stringinput = in.readLine();

//if( input.length() > 9 ) { System.out.println("Invalid Username (more than 9 chars)"); continue; }

Stringquery = "INSERT INTO Users (user_id, password, first_name, middle_name, last_name, e_mail, street1, street2, state, country, zipcode, balance)" +
"VALUES ('" + input + "','";

System.out.println("Enter your password:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your first name:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your middle name:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your last name:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your email:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your street1:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your street2:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your state:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your country:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your zipcode:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your balance:");
input = in.readLine();
query += input + "')";

esql.executeUpdate (query);

 }catch(Exceptione){
System.err.println (e.getMessage ());
return1;
 }//end catch
return0;
 }//end RegisterQuery
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

/* 
 public static void RegisterQuery(EmbeddedSQL esql){
 try{
 String query = "SELECT C.sid, COUNT(C.pid) FROM Suppliers S, Catalog C "
 + "WHERE C.sid=S.sid GROUP BY C.sid;";

 int rowCount = esql.executeQuery (query);
 System.out.println ("total row(s): " + rowCount);
 }catch(Exception e){
 System.err.println (e.getMessage ());
 }
 }//end Query1

 public static void Query2(EmbeddedSQL esql){
 try{
 String query = "SELECT C.sid, COUNT(C.pid) FROM Suppliers S, Catalog C "
 + "WHERE C.sid=S.sid GROUP BY C.sid HAVING COUNT(C.pid)>=3;";

 int rowCount = esql.executeQuery (query);
 System.out.println ("total row(s): " + rowCount);

 }catch(Exception e){
 System.err.println (e.getMessage ());
 }
 }//end Query2

 public static void Query3(EmbeddedSQL esql){
 try{
 String query = "SELECT sname, COUNT(C.pid) FROM Suppliers S, Catalog C "
 + "WHERE pid IN (SELECT pid FROM Parts WHERE color='Green') AND S.sid=C.sid "
 + "GROUP BY sname";

 int rowCount = esql.executeQuery (query);
 System.out.println ("total row(s): " + rowCount);

 }catch(Exception e){
 System.err.println (e.getMessage ());
 }
 }//end Query3

 public static void Query4(EmbeddedSQL esql){
 try{
 String query = "SELECT P.pname, MAX(C.cost) FROM Parts P, Catalog C "
 + "WHERE C.sid IN (SELECT temp.sid "
 + "FROM (SELECT sid, pid FROM Catalog WHERE pid IN (SELECT pid FROM Parts WHERE color='Red')) as temp, "
 + "(SELECT sid, pid FROM catalog WHERE PID IN (SELECT pid FROM Parts WHERE color='Green')) as temp2 "
 + "WHERE temp.sid = temp2.sid) "
 + "GROUP BY P.pname;";

 int rowCount = esql.executeQuery (query);
 System.out.println ("total row(s): " + rowCount);

 }catch(Exception e){
 System.err.println (e.getMessage ());
 }
 }//end Query4

 public static void Query5(EmbeddedSQL esql){
 try{
 String query = "SELECT DISTINCT pname FROM Parts "
 + "WHERE pid IN (SELECT pid FROM Catalog WHERE cost<";

 System.out.print("\tEnter cost: $");
 String input = in.readLine();
 query+=input;
 query += ");";

 int rowCount = esql.executeQuery (query);
 System.out.println ("total row(s): " + rowCount);
/*
 * MODIFIED BY JUSTIN KAHAL AND THOMAS DESMOND
 * SSID:860892022
 *
 * Template JAVA User Interface
 * =============================
 *
 * Database Management Systems
 * Department of Computer Science &amp; Engineering
 * University of California - Riverside
 *
 * Target DBMS: 'Postgres'
 *
 */


importjava.sql.DriverManager;
importjava.sql.Connection;
importjava.sql.Statement;
importjava.sql.ResultSet;
importjava.sql.ResultSetMetaData;
importjava.sql.SQLException;
importjava.io.File;
importjava.io.FileReader;
importjava.io.BufferedReader;
importjava.io.InputStreamReader;


///////////////////////////////////////////////////////
////////////////////////////////////////////////////
/////////	ADD THIS IMPORT ////////////////
importjava.util.Scanner;

/**
 * This class defines a simple embedded SQL utility class that is designed to
 * work with PostgreSQL JDBC drivers.
 *
 */
publicclassEmbeddedSQL {


publicstaticStringcurrentUser;
publicstaticbooleansuperUser = false;
// reference to physical database connection.
privateConnection_connection = null;

// handling the keyboard inputs through a BufferedReader
// This variable can be global for convenience.
staticBufferedReaderin = newBufferedReader(
newInputStreamReader(System.in));

/**
 * Creates a new instance of EmbeddedSQL
 *
 * @param hostname the MySQL or PostgreSQL server hostname
 * @param database the name of the database
 * @param username the user name used to login to the database
 * @param password the user login password
 * @throws java.sql.SQLException when failed to make a connection.
 */
publicEmbeddedSQL (Stringdbname, Stringdbport, Stringuser, Stringpasswd) throwsSQLException {

System.out.print("Connecting to database...");
try{
// constructs the connection URL
Stringurl = "jdbc:postgresql://localhost:" + dbport + "/" + dbname;
System.out.println ("Connection URL: " + url + "\n");

// obtain a physical connection
this._connection = DriverManager.getConnection(url, user, passwd);
System.out.println("Done");
 }catch (Exceptione){
System.err.println("Error - Unable to Connect to Database: " + e.getMessage() );
System.out.println("Make sure you started postgres on this machine");
System.exit(-1);
 }//end catch
 }//end EmbeddedSQL

/**
 * Method to execute an update SQL statement. Update SQL instructions
 * includes CREATE, INSERT, UPDATE, DELETE, and DROP.
 *
 * @param sql the input SQL string
 * @throws java.sql.SQLException when update failed
 */
publicvoidexecuteUpdate (Stringsql) throwsSQLException {
// creates a statement object
Statementstmt = this._connection.createStatement ();

// issues the update instruction
stmt.executeUpdate (sql);

// close the instruction
stmt.close ();
 }//end execute update 

intgetVidId(Stringquery)
 {
try{
// creates a statement objectv
Statementstmt = this._connection.createStatement (); 

// issues the query instruction
ResultSetrs = stmt.executeQuery(query);

while(rs.next())
 {
intvid_id = Integer.parseInt(rs.getString(1));
returnvid_id;
 }
return -1;
 }catch(Exceptione){
System.err.println (e.getMessage ());} 
return -1;
	}//end getVidID

publicintexecuteOrder (Stringquery, intprice) throwsSQLException
 {
// creates a statement objectv
Statementstmt = this._connection.createStatement ();

// issues the query instruction
ResultSetrs = stmt.executeQuery(query);

while(rs.next())
 {
intbalance = Integer.parseInt(rs.getString(1));
if(balance - price >= 0)
 {
System.out.println("You would have negative balance if movie is ordered you cannot order movie"); 
return -1;
 }
else
 {
Stringupdatebal = "UPDATE users SET balance = balance - " + price + " WHERE user_id = '" + currentUser + "'";
stmt.executeUpdate(updatebal); //I changed this part might be problematic
Stringnewbalance = "SELECT balance FROM users WHERE user_id='" + currentUser + "'";
System.out.print("New Balance if ordered: ");
executeStringQuery(newbalance, 1);
return1;
 }	
 }
return -1;
	}

publicvoidexecuteStringQuery (Stringquery, intline) throwsSQLException
	{
// creates a statement objectv
Statementstmt = this._connection.createStatement ();

// issues the query instruction
ResultSetrs = stmt.executeQuery (query);

/*
 ** obtains the metadata object for the returned result set. The metadata
 ** contains row and column info.
 */
System.out.println("--------------");
while(rs.next())
 {
System.out.println(rs.getString(line));
 }
	}

publicintexecuteCheckTitle (Stringquery) throwsSQLException
	{
try{
// creates a statement objectv
Statementstmt = this._connection.createStatement ();
System.out.println("Connection Success");

// issues the query instruction
ResultSetrs = stmt.executeQuery(query);
System.out.println("Query exectued success");

System.out.println("--------------");

while(rs.next())
 {
System.out.print("Are you sure you would like to order (yes or no): ");
System.out.println(rs.getString(2) + " Price: " + rs.getString(3));
Stringinput = in.readLine();
System.out.println();
 }

returnInteger.parseInt(rs.getString(3));
 }catch(Exceptione){
System.err.println (e.getMessage ());}
return -1;
	}

publicintexecuteLoginQuery (Stringquery) throwsSQLException
	{
// creates a statement objectv
Statementstmt = this._connection.createStatement ();

// issues the query instruction
ResultSetrs = stmt.executeQuery (query);

/*
 ** obtains the metadata object for the returned result set. The metadata
 ** contains row and column info.
 */
ResultSetMetaDatarsmd = rs.getMetaData ();
intnumCol = rsmd.getColumnCount ();
rs.next();
intresultset = Integer.parseInt(rs.getString(numCol));
System.out.println(resultset);

stmt.close();
returnresultset;
// iterates through the result set and output them to standard out.
/* boolean outputHeader = true;
 while (rs.next()){
 if(outputHeader){
 for(int i = 1; i <= numCol; i++){
 System.out.print(rsmd.getColumnName(i) + "\t");
 }
 System.out.println();
 outputHeader = false;
 }
 for (int i=1; i<=numCol; ++i)
 System.out.print (rs.getString (i) + "\t");
 System.out.println ();
 ++rowCount;
 }//end while
 stmt.close ();
 return rowCount;
 */
 }

/**
 * Method to execute an input query SQL instruction (i.e. SELECT). This
 * method issues the query to the DBMS and outputs the results to
 * standard out.
 *
 * @param query the input query string
 * @return the number of rows returned
 * @throws java.sql.SQLException when failed to execute the query
 */
publicintexecuteQuery (Stringquery) throwsSQLException {
// creates a statement objectv
Statementstmt = this._connection.createStatement ();

// issues the query instruction
ResultSetrs = stmt.executeQuery (query);

/*
 ** obtains the metadata object for the returned result set. The metadata
 ** contains row and column info.
 */
ResultSetMetaDatarsmd = rs.getMetaData ();
intnumCol = rsmd.getColumnCount ();
introwCount = 0;

// iterates through the result set and output them to standard out.
booleanoutputHeader = true;
while (rs.next()){
if(outputHeader){
for(inti = 1; i <= numCol; i++){
System.out.print(rsmd.getColumnName(i) + "\t");
 }
System.out.println();
outputHeader = false;
 }
for (inti=1; i<=numCol; ++i)
System.out.print (rs.getString (i) + "\t");
System.out.println ();
 ++rowCount;
 }//end while
stmt.close ();
returnrowCount;
 }//end executeQuery

/**
 * Method to close the physical connection if it is open.
 */
publicvoidcleanup(){
try{
if (this._connection != null){
this._connection.close ();
 }//end if
 }catch (SQLExceptione){
// ignored.
 }//end try
 }//end cleanup

/**
 * The main execution method
 *
 * @param args the command line arguments this inclues the <mysql|pgsql> <login file>
 */
publicstaticvoidmain (String[] args) {
if (args.length != 4) {
System.err.println (
"Usage: " +
"java [-classpath <classpath>] " +
EmbeddedSQL.class.getName () +
" <dbname> <port> <user> <passwd>");
return;
 }//end if

Greeting();
EmbeddedSQLesql = null;
try{
// use postgres JDBC driver.
Class.forName ("org.postgresql.Driver").newInstance ();
// instantiate the EmbeddedSQL object and creates a physical
// connection.
Stringdbname = args[0];
Stringdbport = args[1];
Stringuser = args[2];
Stringpasswd = args[3];
esql = newEmbeddedSQL (dbname, dbport, user, passwd);

booleankeepon = true;
while(keepon) {
// These are sample SQL statements
System.out.println("WELCOME! Are you an existing user?");
System.out.println("---------");
System.out.println("0. Yes I am!");
System.out.println("1. No, I need to register!");
System.out.println("9. < EXIT (Stop the program)");


switch (readChoice()){
case0: if(LogInQuery(esql) == 0 ) keepon = false; break;
case1: if(RegisterQuery(esql) == 0 ) keepon = false; break;
case9: return;
default : System.out.println("Unrecognized choice!"); break;
 }//end switch
 }//end while 

//Will need to turn keepon to false after logging in 

booleanhomescreen = true; 
while(homescreen)
 {
System.out.println("---------");
System.out.println("Welcome to MovieNet");
System.out.println("---------");
System.out.println("0. See list of movies");
System.out.println("1. Order a movie");
System.out.println("2. Rate a movie NOT WORKING");
System.out.println("3. Look at wall NOT WORKING");
System.out.println("4. Add to balance");
System.out.println("5. List favorite movie");
System.out.println("6. Add a favorite movie");

//these will only be visible if you are super user
if( superUser ){
System.out.println("\nSuper User Options:");
System.out.println("10. Register a new movie WORKING");
System.out.println("11. Delete an existing user NOT WORKING");
 }

/////////////////////////////////////////////////////////////CHANGES//////////
if( !superUser ) System.out.println("8. Login as SuperUser");
/////////////////////////////////////////////////////////////CHANGES//////////
System.out.println("9. < EXIT (Stop the program)");

switch (readChoice()){
case0: ListMovies(esql); break;
case1: OrderMovie(esql); break;
case2: break;
case3: break;
case4: IncreaseBalance(esql); break;
case5: ListFavs(esql); break;
case6: InsertFav(esql); break;
/////////////////////////////////////////////////////////////CHANGES//////////
case8: if( !superUser ) {LoginAsSuper(esql);}; break;
/////////////////////////////////////////////////////////////CHANGES//////////

case9: return;
case10: if( superUser ) {RegisterMovie(esql);}; break;
case11: if( superUser ) {deleteUser(esql);}; break;
default : System.out.println("Unrecognized choice!"); break;
 }


 }


 }catch(Exceptione) {
System.err.println (e.getMessage ());
 }finally{
// make sure to cleanup the created table and close the connection.
try{
if(esql != null) {
System.out.print("Disconnecting from database...");
esql.cleanup ();
System.out.println("Done\n\nBye !");
 }//end if
 }catch (Exceptione) {
// ignored.
 }//end try
 }//end try
 }//end main



publicstaticvoidListMovies(EmbeddedSQLesql)
	{
try{
Stringquery = "SELECT title FROM Video";
esql.executeStringQuery(query, 1);

 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
	}

publicstaticvoidListFavs(EmbeddedSQLesql)
	{
try{
Stringquery = "SELECT title FROM video, likes WHERE likes.user_id = '" + currentUser + 
"' AND likes.video_id = video.video_id";
esql.executeStringQuery(query, 1);
 }catch(Exceptione){
System.err.println (e.getMessage ());}
	}

publicstaticvoidInsertFav(EmbeddedSQLesql)
	{
try{
System.out.print("Enter title of move you would like to add as favorite: ");
Stringtitle = in.readLine();
Stringquery = "SELECT video_id FROM video WHERE title = '" + title + "'";

intvid_id = esql.getVidId(query);

System.out.println("GOT THE VID ID");
query = "INSERT INTO likes (user_id, video_id) VALUES ('" + currentUser + "', " + vid_id + ")";
esql.executeUpdate(query);

System.out.println("New favorite added");

 }catch(Exceptione){
System.err.println (e.getMessage ());} 
	}

publicstaticvoidOrderMovie(EmbeddedSQLesql)
	{
try{ 
System.out.print("Title of movie you would like to order: ");
Stringtitle = in.readLine();

//Grabbing title and will print it
Stringquery = "SELECT * FROM video WHERE title = ";
query += title + "'";

intprice = esql.executeCheckTitle(query);

query = "SELECT balance FROM users WHERE user_id ='" + currentUser + "';";
inttemp = esql.executeOrder(query, price);

if( temp == 1 )
System.out.println("Successfully ordered: " + title);
 }catch(Exceptione){
System.err.println (e.getMessage ());}
	}


publicstaticvoidIncreaseBalance(EmbeddedSQLesql)
	{
try{

System.out.print("How much would you like to add to balance?: ");
Stringstringbal = in.readLine();
intintbal = Integer.parseInt(stringbal);

if(intbal <= 0)
 {
System.out.println("Must input number greater than $0");
return;
 }

System.out.println("About to update");


Stringquery = "UPDATE users SET balance = balance +" + intbal + " WHERE user_id = '"
 + currentUser + "'";

esql.executeUpdate(query);

System.out.println("UPDATED");

Stringnewbalance = "SELECT balance FROM users WHERE user_id='" + currentUser + "'";
System.out.print("New Balance after addition: ");
esql.executeStringQuery(newbalance, 1); 

return;

 }catch(Exceptione){	
System.err.println (e.getMessage ());}
	}

// NOT FULLY IMPLEMENTED NEED TO GRAB VIDEO ID AND THEN RATE MOVIE
publicstaticvoidRateMovie(EmbeddedSQLesql)
	{
try{

System.out.print("Title of movie you would like to rate: ");
Stringtitle = in.readLine();
System.out.print("What would you like to rate the movie (0 - 10): ");
Stringrating = in.readLine();

Stringquery = "SELECT title FROM Video";

//String output = esql.executeStringQuery(query); //Cant use executeString This way!!!!

 }catch(Exceptione){
System.err.println (e.getMessage ());}
	}

publicstaticvoidGreeting(){
System.out.println(
"\n\n*******************************************************\n" +
" User Interface \n" +
"*******************************************************\n");
 }//end Greeting

publicstaticintLoginAsSuper(EmbeddedSQLesql)
 {
try{
System.out.println("Verifying that you are a Super User");
Stringquery = "SELECT COUNT(super_user_id) FROM super_user WHERE super_user_id='"
 + currentUser + "';";

intresultset = esql.executeLoginQuery(query);

if( resultset != 1 ){
System.out.println("I'm sorry but you are not a registered super user");
return1;
 }

elseif( resultset == 1 ){
System.out.println("Welcome super user " + currentUser);
superUser = true;
return0;
 }

 }catch(Exceptione){
System.err.println( e.getMessage() );
return1;
 }
return0;
 }//end LoginAsSuper

///SOME NEW FUNCTIONS HERE/////////////////////////////////////////////////////////////////////////////////
publicstaticintqueryGenre(EmbeddedSQLesql)
 {
intgenre_id = -1;
try{
//Added some Genre code here//////////////////////////////////////////////
System.out.println("What is the genre of this video?");
StringinputGenre = in.readLine();

StringgetGenre = "SELECT genre_id FROM genre WHERE genre_name='" + inputGenre + "';";
Statementstmt = esql._connection.createStatement();
ResultSetrs = stmt.executeQuery(getGenre);
ResultSetMetaDatarsmd = rs.getMetaData();

if( rs.next() ) genre_id = Integer.parseInt(rs.getString(1));

//only care if the genre doesn't already exist
else
 {
getGenre = "SELECT MAX(genre_id) FROM genre;";
rs = stmt.executeQuery(getGenre);
rsmd = rs.getMetaData();

if( rs.next() ) genre_id = Integer.parseInt(rs.getString(1))+1;
elsegenre_id = 1;

esql.executeUpdate( "INSERT INTO genre (genre_id, genre_name) VALUES (" + genre_id + ", '" + inputGenre + "');" );
rs.close();
 }

////////////////////////////////////////////////////////////////////////

 }catch(Exceptione){
System.err.println( e.getMessage() );
return -1;}
returngenre_id;
 }

publicstaticvoidcategorizeVideo(EmbeddedSQLesql, intvideo_id, intgenre_id )
 {
System.out.println("VIDEOID: " + video_id + " GenreID: " + genre_id);
try{
StringgetGenre = "SELECT COUNT(*) FROM categorize WHERE genre_id=" + genre_id + " AND video_id=" + video_id + ";";
Statementstmt = esql._connection.createStatement();
ResultSetrs = stmt.executeQuery(getGenre);
ResultSetMetaDatarsmd = rs.getMetaData();


//only care if this video_id isn't already in the list
if( !rs.next() || rs.getString(1).equals("0") )
esql.executeUpdate( "INSERT INTO categorize (video_id, genre_id) VALUES (" + video_id + ", " + genre_id + ");" );

 }catch(Exceptione){
System.err.println( e.getMessage() );}
 }

//////END OF SEGMENTED NEW FUNCTIONS///////////////////////////////////////////////////////////////////////

//Register a new video, creating new video_id, season_id, and series_id if necessary
publicstaticintRegisterMovie(EmbeddedSQLesql)
 {
try{
booleanisSeries = false;
System.out.println("Please enter the following information to register a new movie/series");

System.out.println("What is the title of this video?");
StringinputTitle = in.readLine();

///ADDEDD//////////////////////////////////////////////////////////
intgenre_id = queryGenre(esql);
if( genre_id == -1 ) System.out.println("Problem when creating genre");
////ADDEDD//////////////////////////////////////////////////////////


System.out.println("Is this video part of a series (y/n)?");
StringinputSeries = in.readLine();

StringinputSeasonNum = "";
StringinputEpNum = "";
StringinputYear = "";
intinputOnlinePrice;
intinputDvdPrice;
intinputRating;
intseries_id;
intseason_id;

//if this is a season get season info
if( inputSeries.equals("y") ){
isSeries = true;

System.out.println("What season number is this?");
inputSeasonNum = in.readLine();

System.out.println("What episode is this?");
inputEpNum = in.readLine();
 }

System.out.println("What year did this come out?");
inputYear = in.readLine();

System.out.println("How much should the online price be?");
inputOnlinePrice = Integer.parseInt(in.readLine());

System.out.println("How much should the dvd price be?");
inputDvdPrice = Integer.parseInt(in.readLine());

System.out.println("How would you rate this (0-10)?");
inputRating = Integer.parseInt(in.readLine());

//this will get the video id
Stringquery = "SELECT MAX(video_id) FROM video;";
Statementstmt = esql._connection.createStatement ();
ResultSetrs = stmt.executeQuery (query);
ResultSetMetaDatarsmd = rs.getMetaData ();
intnumCol = rsmd.getColumnCount ();
rs.next();
intvideo_id = Integer.parseInt(rs.getString(numCol)) + 1;
System.out.println(video_id);
stmt.close();

//this will check if the series is already in record if not then it adds it
if( isSeries ){
stmt = esql._connection.createStatement();
StringgetSeriesId = "SELECT series_id FROM series WHERE title='" + inputTitle + "'";
rs = stmt.executeQuery (getSeriesId);
rsmd = rs.getMetaData ();
numCol = rsmd.getColumnCount ();

//get the series id if it already exists
if(rs.next()) series_id = Integer.parseInt(rs.getString(numCol));
//if series id doesnt already exist then create a new series value
else{
getSeriesId = "SELECT MAX(series_id) FROM series;";
rs = stmt.executeQuery(getSeriesId);
rsmd = rs.getMetaData();
if(rs.next()) series_id = Integer.parseInt(rs.getString(1)) + 1;
elseseries_id = 1;

//add series information to database
StringinsertSeries = "INSERT INTO series (series_id, title) VALUES (" + series_id + ", '" + inputTitle + "');";
esql.executeUpdate(insertSeries);
 }

StringgetSeasonId = "SELECT season_id FROM season WHERE series_id='" + series_id + "' AND season_number='" + inputSeasonNum + "';";
rs = stmt.executeQuery(getSeasonId);
rsmd = rs.getMetaData();
numCol = rsmd.getColumnCount();

//if the season id already exists grab the season id
if(rs.next()) season_id = Integer.parseInt(rs.getString(1));
//if the season id doesn't exist then place insert new season value
else{
getSeasonId = "SELECT MAX(season_id) FROM season;";
rs = stmt.executeQuery(getSeasonId);
rsmd = rs.getMetaData();
if(rs.next()) season_id = Integer.parseInt(rs.getString(1)) + 1;
elseseason_id = 1;

StringinsertSeason = "INSERT INTO season (season_id, series_id, season_number ) VALUES (" + season_id + ", " + series_id + ", '" + inputSeasonNum + "');";
esql.executeUpdate(insertSeason);
 }

StringinsertVideo = "INSERT INTO video (video_id, title, year, online_price, dvd_price, rating, episode, season_id) VALUES " + 
"(" + video_id + ",'" + inputTitle + "', '" + inputYear + "', " + inputOnlinePrice + ", " + inputDvdPrice + ", " + inputRating + ", " + inputEpNum + ", " + season_id + ");";

esql.executeUpdate(insertVideo);

stmt.close();
 }//End if video is a series

elseif( !isSeries )
 {
StringinsertVideo = "INSERT INTO video (video_id, title, year, online_price, dvd_price, rating) VALUES " + 
"(" + video_id + ",'" + inputTitle + "', '" + inputYear + "', " + inputOnlinePrice + ", " + inputDvdPrice + ", " + inputRating + ");";
stmt = esql._connection.createStatement ();
esql.executeUpdate(insertVideo);
stmt.close();
 }//End if video is not series

///ADDDEDDD//////////////////////////////////
categorizeVideo(esql, video_id, genre_id);
///ADDEDDDD/////////////////////////////////
 }catch(Exceptione){
System.err.println( e.getMessage() );
return1;
 }
return0;
 }

///////////////////////////////////////////////////////////////START//////////
/////////////////////////////////////////////////////////////CHANGES//////////
//this will delete a user from the database (only if user is superUser)
publicstaticintdeleteUser(EmbeddedSQLesql)
	{
try{
System.out.println("In order to delete a specific movie instance you must specify the following information");
while( true )
 {
System.out.println("Please specify a user_id that you want to delete or press 9 to cancel");
StringinputDelUser = in.readLine();

if( inputDelUser.equals("9") ) break;
elseif( inputDelUser.equals(currentUser) )
 {
System.out.println("You cannot delete youself, sorry.");
continue;
 }

Stringquery = "Select COUNT(user_id) FROM users WHERE user_id='" + inputDelUser + "';";

Statementstmt = esql._connection.createStatement ();
ResultSetrs = stmt.executeQuery(query);
ResultSetMetaDatarsmd = rs.getMetaData();

//if the user does exist
if(rs.next() && Integer.parseInt(rs.getString(1)) == 1 )
 {
query = "DELETE FROM users WHERE user_id='" + inputDelUser + "';";
esql.executeUpdate(query);
System.out.println("Successfully deleted user: " + inputDelUser );
rs.close();
return0;
 }//end user exist

//if user doesnt exist
else
 {
System.out.println("Sorry that user doesn't exist. Please make sure you have the right user_id");
rs.close();
continue;
 }//end user doest exist
 }//end while

 }catch(Exceptione){
System.err.println( e.getMessage() );
return1;}
return0;
	}


/////////////////////////////////////////////////////////////CHANGES//////////
/////////////////////////////////////////////////////////////CHANGES//////////
//////////////////////////////////////////////////////////////END//////////////
/*
 * Reads the users choice given from the keyboard
 * @int
 **/
publicstaticintreadChoice() {
intinput;
// returns only if a correct value is given.
do {
System.out.print("Please make your choice: ");
try { // read the integer, parse it and break.
input = Integer.parseInt(in.readLine());
break;
 }catch (Exceptione) {
System.out.println("Your input is invalid!");
continue;
 }//end try
 }while (true);
returninput;
 }//end readChoice

publicstaticintLogInQuery(EmbeddedSQLesql){
try{
System.out.println("You are trying to Login as an existing user, enter your Username (Make sure it is only 9 chars long) or press 9 to go back");
//Keep asking the user to login if they want to log in
while( true ){
System.out.println("Please specify your username or press 9 to go back:");
StringinputUser = in.readLine();

if( inputUser.equals("9") ) return1; //exit the loop and go back to home screen

Stringquery = "SELECT COUNT(user_id) FROM users WHERE user_id='";
query += inputUser + "'";

intoutput = esql.executeLoginQuery (query);
System.out.println("This is output" + output);

//check if username is valid, if not 1 then either output = 0 or > 2 which is a problem
if( output != 1 ) {
System.out.println("Sorry, that username isn't in our records. Please try again.");
continue;
 }//end username not found

//if output is 1 then only 1 record with that user found (good)
elseif( output == 1 ){
System.out.println("Please specify your password:");
StringinputPass = in.readLine();
query = "SELECT COUNT(user_id) FROM users WHERE user_id='" + inputUser + "' AND password='" + inputPass + "'";

intoutput2 = esql.executeLoginQuery(query); //returns number of query occurances

//if password doesn't match records ask for username again
if( output2 != 1 ){
System.out.println("Sorry, the username and password don't match up. Please try again.");
continue;
 }//end password fail

//if password correct then save current user info and log them in
elseif( output2 == 1 ){
currentUser = inputUser;
System.out.println("Successfully logged in! Welcome " + currentUser + "!");
break;
 }//end password success
 }//end username found
 }//end user login prompt
 }catch(Exceptione){
System.err.println (e.getMessage ());
return1;
 }//end catch
return0;
 }//end LoginQuery

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
publicstaticintRegisterQuery(EmbeddedSQLesql){
System.out.println("You have selected to register yourself as a user. Please specify the following information.");
try{
System.out.println("Enter a username you want (Username must be 9 chars or less long):");
Stringinput = in.readLine();

//if( input.length() > 9 ) { System.out.println("Invalid Username (more than 9 chars)"); continue; }

Stringquery = "INSERT INTO Users (user_id, password, first_name, middle_name, last_name, e_mail, street1, street2, state, country, zipcode, balance)" +
"VALUES ('" + input + "','";

System.out.println("Enter your password:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your first name:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your middle name:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your last name:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your email:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your street1:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your street2:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your state:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your country:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your zipcode:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your balance:");
input = in.readLine();
query += input + "')";

esql.executeUpdate (query);

 }catch(Exceptione){
System.err.println (e.getMessage ());
return1;
 }//end catch
return0;
 }//end RegisterQuery
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

/* 
 public static void RegisterQuery(EmbeddedSQL esql){
 try{
 String query = "SELECT C.sid, COUNT(C.pid) FROM Suppliers S, Catalog C "
 + "WHERE C.sid=S.sid GROUP BY C.sid;";

 int rowCount = esql.executeQuery (query);
 System.out.println ("total row(s): " + rowCount);
 }catch(Exception e){
 System.err.println (e.getMessage ());
 }
 }//end Query1

 public static void Query2(EmbeddedSQL esql){
 try{
 String query = "SELECT C.sid, COUNT(C.pid) FROM Suppliers S, Catalog C "
 + "WHERE C.sid=S.sid GROUP BY C.sid HAVING COUNT(C.pid)>=3;";

 int rowCount = esql.executeQuery (query);
 System.out.println ("total row(s): " + rowCount);

 }catch(Exception e){
 System.err.println (e.getMessage ());
 }
 }//end Query2

 public static void Query3(EmbeddedSQL esql){
 try{
 String query = "SELECT sname, COUNT(C.pid) FROM Suppliers S, Catalog C "
 + "WHERE pid IN (SELECT pid FROM Parts WHERE color='Green') AND S.sid=C.sid "
 + "GROUP BY sname";

 int rowCount = esql.executeQuery (query);
 System.out.println ("total row(s): " + rowCount);

 }catch(Exception e){
 System.err.println (e.getMessage ());
 }
 }//end Query3

 public static void Query4(EmbeddedSQL esql){
 try{
 String query = "SELECT P.pname, MAX(C.cost) FROM Parts P, Catalog C "
 + "WHERE C.sid IN (SELECT temp.sid "
 + "FROM (SELECT sid, pid FROM Catalog WHERE pid IN (SELECT pid FROM Parts WHERE color='Red')) as temp, "
 + "(SELECT sid, pid FROM catalog WHERE PID IN (SELECT pid FROM Parts WHERE color='Green')) as temp2 "
 + "WHERE temp.sid = temp2.sid) "
 + "GROUP BY P.pname;";

 int rowCount = esql.executeQuery (query);
 System.out.println ("total row(s): " + rowCount);

 }catch(Exception e){
 System.err.println (e.getMessage ());
 }
 }//end Query4

 public static void Query5(EmbeddedSQL esql){
 try{
 String query = "SELECT DISTINCT pname FROM Parts "
 + "WHERE pid IN (SELECT pid FROM Catalog WHERE cost<";

 System.out.print("\tEnter cost: $");
 String input = in.readLine();
 query+=input;
 query += ");";

 int rowCount = esql.executeQuery (query);
 System.out.println ("total row(s): " + rowCount);
/*
 * MODIFIED BY JUSTIN KAHAL AND THOMAS DESMOND
 * SSID:860892022
 *
 * Template JAVA User Interface
 * =============================
 *
 * Database Management Systems
 * Department of Computer Science &amp; Engineering
 * University of California - Riverside
 *
 * Target DBMS: 'Postgres'
 *
 */


importjava.sql.DriverManager;
importjava.sql.Connection;
importjava.sql.Statement;
importjava.sql.ResultSet;
importjava.sql.ResultSetMetaData;
importjava.sql.SQLException;
importjava.io.File;
importjava.io.FileReader;
importjava.io.BufferedReader;
importjava.io.InputStreamReader;


///////////////////////////////////////////////////////
////////////////////////////////////////////////////
/////////	ADD THIS IMPORT ////////////////
importjava.util.Scanner;

/**
 * This class defines a simple embedded SQL utility class that is designed to
 * work with PostgreSQL JDBC drivers.
 *
 */
publicclassEmbeddedSQL {


publicstaticStringcurrentUser;
publicstaticbooleansuperUser = false;
// reference to physical database connection.
privateConnection_connection = null;

// handling the keyboard inputs through a BufferedReader
// This variable can be global for convenience.
staticBufferedReaderin = newBufferedReader(
newInputStreamReader(System.in));

/**
 * Creates a new instance of EmbeddedSQL
 *
 * @param hostname the MySQL or PostgreSQL server hostname
 * @param database the name of the database
 * @param username the user name used to login to the database
 * @param password the user login password
 * @throws java.sql.SQLException when failed to make a connection.
 */
publicEmbeddedSQL (Stringdbname, Stringdbport, Stringuser, Stringpasswd) throwsSQLException {

System.out.print("Connecting to database...");
try{
// constructs the connection URL
Stringurl = "jdbc:postgresql://localhost:" + dbport + "/" + dbname;
System.out.println ("Connection URL: " + url + "\n");

// obtain a physical connection
this._connection = DriverManager.getConnection(url, user, passwd);
System.out.println("Done");
 }catch (Exceptione){
System.err.println("Error - Unable to Connect to Database: " + e.getMessage() );
System.out.println("Make sure you started postgres on this machine");
System.exit(-1);
 }//end catch
 }//end EmbeddedSQL

/**
 * Method to execute an update SQL statement. Update SQL instructions
 * includes CREATE, INSERT, UPDATE, DELETE, and DROP.
 *
 * @param sql the input SQL string
 * @throws java.sql.SQLException when update failed
 */
publicvoidexecuteUpdate (Stringsql) throwsSQLException {
// creates a statement object
Statementstmt = this._connection.createStatement ();

// issues the update instruction
stmt.executeUpdate (sql);

// close the instruction
stmt.close ();
 }//end execute update 

intgetVidId(Stringquery)
 {
try{
// creates a statement objectv
Statementstmt = this._connection.createStatement (); 

// issues the query instruction
ResultSetrs = stmt.executeQuery(query);

while(rs.next())
 {
intvid_id = Integer.parseInt(rs.getString(1));
returnvid_id;
 }
return -1;
 }catch(Exceptione){
System.err.println (e.getMessage ());} 
return -1;
	}//end getVidID

publicintexecuteOrder (Stringquery, intprice) throwsSQLException
 {
// creates a statement objectv
Statementstmt = this._connection.createStatement ();

// issues the query instruction
ResultSetrs = stmt.executeQuery(query);

while(rs.next())
 {
intbalance = Integer.parseInt(rs.getString(1));
if(balance - price >= 0)
 {
System.out.println("You would have negative balance if movie is ordered you cannot order movie"); 
return -1;
 }
else
 {
Stringupdatebal = "UPDATE users SET balance = balance - " + price + " WHERE user_id = '" + currentUser + "'";
stmt.executeUpdate(updatebal); //I changed this part might be problematic
Stringnewbalance = "SELECT balance FROM users WHERE user_id='" + currentUser + "'";
System.out.print("New Balance if ordered: ");
executeStringQuery(newbalance, 1);
return1;
 }	
 }
return -1;
	}

publicvoidexecuteStringQuery (Stringquery, intline) throwsSQLException
	{
// creates a statement objectv
Statementstmt = this._connection.createStatement ();

// issues the query instruction
ResultSetrs = stmt.executeQuery (query);

/*
 ** obtains the metadata object for the returned result set. The metadata
 ** contains row and column info.
 */
System.out.println("--------------");
while(rs.next())
 {
System.out.println(rs.getString(line));
 }
	}

publicintexecuteCheckTitle (Stringquery) throwsSQLException
	{
try{
// creates a statement objectv
Statementstmt = this._connection.createStatement ();
System.out.println("Connection Success");

// issues the query instruction
ResultSetrs = stmt.executeQuery(query);
System.out.println("Query exectued success");

System.out.println("--------------");

while(rs.next())
 {
System.out.print("Are you sure you would like to order (yes or no): ");
System.out.println(rs.getString(2) + " Price: " + rs.getString(3));
Stringinput = in.readLine();
System.out.println();
 }

returnInteger.parseInt(rs.getString(3));
 }catch(Exceptione){
System.err.println (e.getMessage ());}
return -1;
	}

publicintexecuteLoginQuery (Stringquery) throwsSQLException
	{
// creates a statement objectv
Statementstmt = this._connection.createStatement ();

// issues the query instruction
ResultSetrs = stmt.executeQuery (query);

/*
 ** obtains the metadata object for the returned result set. The metadata
 ** contains row and column info.
 */
ResultSetMetaDatarsmd = rs.getMetaData ();
intnumCol = rsmd.getColumnCount ();
rs.next();
intresultset = Integer.parseInt(rs.getString(numCol));
System.out.println(resultset);

stmt.close();
returnresultset;
// iterates through the result set and output them to standard out.
/* boolean outputHeader = true;
 while (rs.next()){
 if(outputHeader){
 for(int i = 1; i <= numCol; i++){
 System.out.print(rsmd.getColumnName(i) + "\t");
 }
 System.out.println();
 outputHeader = false;
 }
 for (int i=1; i<=numCol; ++i)
 System.out.print (rs.getString (i) + "\t");
 System.out.println ();
 ++rowCount;
 }//end while
 stmt.close ();
 return rowCount;
 */
 }

/**
 * Method to execute an input query SQL instruction (i.e. SELECT). This
 * method issues the query to the DBMS and outputs the results to
 * standard out.
 *
 * @param query the input query string
 * @return the number of rows returned
 * @throws java.sql.SQLException when failed to execute the query
 */
publicintexecuteQuery (Stringquery) throwsSQLException {
// creates a statement objectv
Statementstmt = this._connection.createStatement ();

// issues the query instruction
ResultSetrs = stmt.executeQuery (query);

/*
 ** obtains the metadata object for the returned result set. The metadata
 ** contains row and column info.
 */
ResultSetMetaDatarsmd = rs.getMetaData ();
intnumCol = rsmd.getColumnCount ();
introwCount = 0;

// iterates through the result set and output them to standard out.
booleanoutputHeader = true;
while (rs.next()){
if(outputHeader){
for(inti = 1; i <= numCol; i++){
System.out.print(rsmd.getColumnName(i) + "\t");
 }
System.out.println();
outputHeader = false;
 }
for (inti=1; i<=numCol; ++i)
System.out.print (rs.getString (i) + "\t");
System.out.println ();
 ++rowCount;
 }//end while
stmt.close ();
returnrowCount;
 }//end executeQuery

/**
 * Method to close the physical connection if it is open.
 */
publicvoidcleanup(){
try{
if (this._connection != null){
this._connection.close ();
 }//end if
 }catch (SQLExceptione){
// ignored.
 }//end try
 }//end cleanup

/**
 * The main execution method
 *
 * @param args the command line arguments this inclues the <mysql|pgsql> <login file>
 */
publicstaticvoidmain (String[] args) {
if (args.length != 4) {
System.err.println (
"Usage: " +
"java [-classpath <classpath>] " +
EmbeddedSQL.class.getName () +
" <dbname> <port> <user> <passwd>");
return;
 }//end if

Greeting();
EmbeddedSQLesql = null;
try{
// use postgres JDBC driver.
Class.forName ("org.postgresql.Driver").newInstance ();
// instantiate the EmbeddedSQL object and creates a physical
// connection.
Stringdbname = args[0];
Stringdbport = args[1];
Stringuser = args[2];
Stringpasswd = args[3];
esql = newEmbeddedSQL (dbname, dbport, user, passwd);

booleankeepon = true;
while(keepon) {
// These are sample SQL statements
System.out.println("WELCOME! Are you an existing user?");
System.out.println("---------");
System.out.println("0. Yes I am!");
System.out.println("1. No, I need to register!");
System.out.println("9. < EXIT (Stop the program)");


switch (readChoice()){
case0: if(LogInQuery(esql) == 0 ) keepon = false; break;
case1: if(RegisterQuery(esql) == 0 ) keepon = false; break;
case9: return;
default : System.out.println("Unrecognized choice!"); break;
 }//end switch
 }//end while 

//Will need to turn keepon to false after logging in 

booleanhomescreen = true; 
while(homescreen)
 {
System.out.println("---------");
System.out.println("Welcome to MovieNet");
System.out.println("---------");
System.out.println("0. See list of movies");
System.out.println("1. Order a movie");
System.out.println("2. Rate a movie NOT WORKING");
System.out.println("3. Look at wall NOT WORKING");
System.out.println("4. Add to balance");
System.out.println("5. List favorite movie");
System.out.println("6. Add a favorite movie");

//these will only be visible if you are super user
if( superUser ){
System.out.println("\nSuper User Options:");
System.out.println("10. Register a new movie WORKING");
System.out.println("11. Delete an existing user NOT WORKING");
 }

/////////////////////////////////////////////////////////////CHANGES//////////
if( !superUser ) System.out.println("8. Login as SuperUser");
/////////////////////////////////////////////////////////////CHANGES//////////
System.out.println("9. < EXIT (Stop the program)");

switch (readChoice()){
case0: ListMovies(esql); break;
case1: OrderMovie(esql); break;
case2: break;
case3: break;
case4: IncreaseBalance(esql); break;
case5: ListFavs(esql); break;
case6: InsertFav(esql); break;
/////////////////////////////////////////////////////////////CHANGES//////////
case8: if( !superUser ) {LoginAsSuper(esql);}; break;
/////////////////////////////////////////////////////////////CHANGES//////////

case9: return;
case10: if( superUser ) {RegisterMovie(esql);}; break;
case11: if( superUser ) {deleteUser(esql);}; break;
default : System.out.println("Unrecognized choice!"); break;
 }


 }


 }catch(Exceptione) {
System.err.println (e.getMessage ());
 }finally{
// make sure to cleanup the created table and close the connection.
try{
if(esql != null) {
System.out.print("Disconnecting from database...");
esql.cleanup ();
System.out.println("Done\n\nBye !");
 }//end if
 }catch (Exceptione) {
// ignored.
 }//end try
 }//end try
 }//end main



publicstaticvoidListMovies(EmbeddedSQLesql)
	{
try{
Stringquery = "SELECT title FROM Video";
esql.executeStringQuery(query, 1);

 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
	}

publicstaticvoidListFavs(EmbeddedSQLesql)
	{
try{
Stringquery = "SELECT title FROM video, likes WHERE likes.user_id = '" + currentUser + 
"' AND likes.video_id = video.video_id";
esql.executeStringQuery(query, 1);
 }catch(Exceptione){
System.err.println (e.getMessage ());}
	}

publicstaticvoidInsertFav(EmbeddedSQLesql)
	{
try{
System.out.print("Enter title of move you would like to add as favorite: ");
Stringtitle = in.readLine();
Stringquery = "SELECT video_id FROM video WHERE title = '" + title + "'";

intvid_id = esql.getVidId(query);

System.out.println("GOT THE VID ID");
query = "INSERT INTO likes (user_id, video_id) VALUES ('" + currentUser + "', " + vid_id + ")";
esql.executeUpdate(query);

System.out.println("New favorite added");

 }catch(Exceptione){
System.err.println (e.getMessage ());} 
	}

publicstaticvoidOrderMovie(EmbeddedSQLesql)
	{
try{ 
System.out.print("Title of movie you would like to order: ");
Stringtitle = in.readLine();

//Grabbing title and will print it
Stringquery = "SELECT * FROM video WHERE title = ";
query += title + "'";

intprice = esql.executeCheckTitle(query);

query = "SELECT balance FROM users WHERE user_id ='" + currentUser + "';";
inttemp = esql.executeOrder(query, price);

if( temp == 1 )
System.out.println("Successfully ordered: " + title);
 }catch(Exceptione){
System.err.println (e.getMessage ());}
	}


publicstaticvoidIncreaseBalance(EmbeddedSQLesql)
	{
try{

System.out.print("How much would you like to add to balance?: ");
Stringstringbal = in.readLine();
intintbal = Integer.parseInt(stringbal);

if(intbal <= 0)
 {
System.out.println("Must input number greater than $0");
return;
 }

System.out.println("About to update");


Stringquery = "UPDATE users SET balance = balance +" + intbal + " WHERE user_id = '"
 + currentUser + "'";

esql.executeUpdate(query);

System.out.println("UPDATED");

Stringnewbalance = "SELECT balance FROM users WHERE user_id='" + currentUser + "'";
System.out.print("New Balance after addition: ");
esql.executeStringQuery(newbalance, 1); 

return;

 }catch(Exceptione){	
System.err.println (e.getMessage ());}
	}

// NOT FULLY IMPLEMENTED NEED TO GRAB VIDEO ID AND THEN RATE MOVIE
publicstaticvoidRateMovie(EmbeddedSQLesql)
	{
try{

System.out.print("Title of movie you would like to rate: ");
Stringtitle = in.readLine();
System.out.print("What would you like to rate the movie (0 - 10): ");
Stringrating = in.readLine();

Stringquery = "SELECT title FROM Video";

//String output = esql.executeStringQuery(query); //Cant use executeString This way!!!!

 }catch(Exceptione){
System.err.println (e.getMessage ());}
	}

publicstaticvoidGreeting(){
System.out.println(
"\n\n*******************************************************\n" +
" User Interface \n" +
"*******************************************************\n");
 }//end Greeting

publicstaticintLoginAsSuper(EmbeddedSQLesql)
 {
try{
System.out.println("Verifying that you are a Super User");
Stringquery = "SELECT COUNT(super_user_id) FROM super_user WHERE super_user_id='"
 + currentUser + "';";

intresultset = esql.executeLoginQuery(query);

if( resultset != 1 ){
System.out.println("I'm sorry but you are not a registered super user");
return1;
 }

elseif( resultset == 1 ){
System.out.println("Welcome super user " + currentUser);
superUser = true;
return0;
 }

 }catch(Exceptione){
System.err.println( e.getMessage() );
return1;
 }
return0;
 }//end LoginAsSuper

///SOME NEW FUNCTIONS HERE/////////////////////////////////////////////////////////////////////////////////
publicstaticintqueryGenre(EmbeddedSQLesql)
 {
intgenre_id = -1;
try{
//Added some Genre code here//////////////////////////////////////////////
System.out.println("What is the genre of this video?");
StringinputGenre = in.readLine();

StringgetGenre = "SELECT genre_id FROM genre WHERE genre_name='" + inputGenre + "';";
Statementstmt = esql._connection.createStatement();
ResultSetrs = stmt.executeQuery(getGenre);
ResultSetMetaDatarsmd = rs.getMetaData();

if( rs.next() ) genre_id = Integer.parseInt(rs.getString(1));

//only care if the genre doesn't already exist
else
 {
getGenre = "SELECT MAX(genre_id) FROM genre;";
rs = stmt.executeQuery(getGenre);
rsmd = rs.getMetaData();

if( rs.next() ) genre_id = Integer.parseInt(rs.getString(1))+1;
elsegenre_id = 1;

esql.executeUpdate( "INSERT INTO genre (genre_id, genre_name) VALUES (" + genre_id + ", '" + inputGenre + "');" );
rs.close();
 }

////////////////////////////////////////////////////////////////////////

 }catch(Exceptione){
System.err.println( e.getMessage() );
return -1;}
returngenre_id;
 }

publicstaticvoidcategorizeVideo(EmbeddedSQLesql, intvideo_id, intgenre_id )
 {
System.out.println("VIDEOID: " + video_id + " GenreID: " + genre_id);
try{
StringgetGenre = "SELECT COUNT(*) FROM categorize WHERE genre_id=" + genre_id + " AND video_id=" + video_id + ";";
Statementstmt = esql._connection.createStatement();
ResultSetrs = stmt.executeQuery(getGenre);
ResultSetMetaDatarsmd = rs.getMetaData();


//only care if this video_id isn't already in the list
if( !rs.next() || rs.getString(1).equals("0") )
esql.executeUpdate( "INSERT INTO categorize (video_id, genre_id) VALUES (" + video_id + ", " + genre_id + ");" );

 }catch(Exceptione){
System.err.println( e.getMessage() );}
 }

//////END OF SEGMENTED NEW FUNCTIONS///////////////////////////////////////////////////////////////////////

//Register a new video, creating new video_id, season_id, and series_id if necessary
publicstaticintRegisterMovie(EmbeddedSQLesql)
 {
try{
booleanisSeries = false;
System.out.println("Please enter the following information to register a new movie/series");

System.out.println("What is the title of this video?");
StringinputTitle = in.readLine();

///ADDEDD//////////////////////////////////////////////////////////
intgenre_id = queryGenre(esql);
if( genre_id == -1 ) System.out.println("Problem when creating genre");
////ADDEDD//////////////////////////////////////////////////////////


System.out.println("Is this video part of a series (y/n)?");
StringinputSeries = in.readLine();

StringinputSeasonNum = "";
StringinputEpNum = "";
StringinputYear = "";
intinputOnlinePrice;
intinputDvdPrice;
intinputRating;
intseries_id;
intseason_id;

//if this is a season get season info
if( inputSeries.equals("y") ){
isSeries = true;

System.out.println("What season number is this?");
inputSeasonNum = in.readLine();

System.out.println("What episode is this?");
inputEpNum = in.readLine();
 }

System.out.println("What year did this come out?");
inputYear = in.readLine();

System.out.println("How much should the online price be?");
inputOnlinePrice = Integer.parseInt(in.readLine());

System.out.println("How much should the dvd price be?");
inputDvdPrice = Integer.parseInt(in.readLine());

System.out.println("How would you rate this (0-10)?");
inputRating = Integer.parseInt(in.readLine());

//this will get the video id
Stringquery = "SELECT MAX(video_id) FROM video;";
Statementstmt = esql._connection.createStatement ();
ResultSetrs = stmt.executeQuery (query);
ResultSetMetaDatarsmd = rs.getMetaData ();
intnumCol = rsmd.getColumnCount ();
rs.next();
intvideo_id = Integer.parseInt(rs.getString(numCol)) + 1;
System.out.println(video_id);
stmt.close();

//this will check if the series is already in record if not then it adds it
if( isSeries ){
stmt = esql._connection.createStatement();
StringgetSeriesId = "SELECT series_id FROM series WHERE title='" + inputTitle + "'";
rs = stmt.executeQuery (getSeriesId);
rsmd = rs.getMetaData ();
numCol = rsmd.getColumnCount ();

//get the series id if it already exists
if(rs.next()) series_id = Integer.parseInt(rs.getString(numCol));
//if series id doesnt already exist then create a new series value
else{
getSeriesId = "SELECT MAX(series_id) FROM series;";
rs = stmt.executeQuery(getSeriesId);
rsmd = rs.getMetaData();
if(rs.next()) series_id = Integer.parseInt(rs.getString(1)) + 1;
elseseries_id = 1;

//add series information to database
StringinsertSeries = "INSERT INTO series (series_id, title) VALUES (" + series_id + ", '" + inputTitle + "');";
esql.executeUpdate(insertSeries);
 }

StringgetSeasonId = "SELECT season_id FROM season WHERE series_id='" + series_id + "' AND season_number='" + inputSeasonNum + "';";
rs = stmt.executeQuery(getSeasonId);
rsmd = rs.getMetaData();
numCol = rsmd.getColumnCount();

//if the season id already exists grab the season id
if(rs.next()) season_id = Integer.parseInt(rs.getString(1));
//if the season id doesn't exist then place insert new season value
else{
getSeasonId = "SELECT MAX(season_id) FROM season;";
rs = stmt.executeQuery(getSeasonId);
rsmd = rs.getMetaData();
if(rs.next()) season_id = Integer.parseInt(rs.getString(1)) + 1;
elseseason_id = 1;

StringinsertSeason = "INSERT INTO season (season_id, series_id, season_number ) VALUES (" + season_id + ", " + series_id + ", '" + inputSeasonNum + "');";
esql.executeUpdate(insertSeason);
 }

StringinsertVideo = "INSERT INTO video (video_id, title, year, online_price, dvd_price, rating, episode, season_id) VALUES " + 
"(" + video_id + ",'" + inputTitle + "', '" + inputYear + "', " + inputOnlinePrice + ", " + inputDvdPrice + ", " + inputRating + ", " + inputEpNum + ", " + season_id + ");";

esql.executeUpdate(insertVideo);

stmt.close();
 }//End if video is a series

elseif( !isSeries )
 {
StringinsertVideo = "INSERT INTO video (video_id, title, year, online_price, dvd_price, rating) VALUES " + 
"(" + video_id + ",'" + inputTitle + "', '" + inputYear + "', " + inputOnlinePrice + ", " + inputDvdPrice + ", " + inputRating + ");";
stmt = esql._connection.createStatement ();
esql.executeUpdate(insertVideo);
stmt.close();
 }//End if video is not series

///ADDDEDDD//////////////////////////////////
categorizeVideo(esql, video_id, genre_id);
///ADDEDDDD/////////////////////////////////
 }catch(Exceptione){
System.err.println( e.getMessage() );
return1;
 }
return0;
 }

///////////////////////////////////////////////////////////////START//////////
/////////////////////////////////////////////////////////////CHANGES//////////
//this will delete a user from the database (only if user is superUser)
publicstaticintdeleteUser(EmbeddedSQLesql)
	{
try{
System.out.println("In order to delete a specific movie instance you must specify the following information");
while( true )
 {
System.out.println("Please specify a user_id that you want to delete or press 9 to cancel");
StringinputDelUser = in.readLine();

if( inputDelUser.equals("9") ) break;
elseif( inputDelUser.equals(currentUser) )
 {
System.out.println("You cannot delete youself, sorry.");
continue;
 }

Stringquery = "Select COUNT(user_id) FROM users WHERE user_id='" + inputDelUser + "';";

Statementstmt = esql._connection.createStatement ();
ResultSetrs = stmt.executeQuery(query);
ResultSetMetaDatarsmd = rs.getMetaData();

//if the user does exist
if(rs.next() && Integer.parseInt(rs.getString(1)) == 1 )
 {
query = "DELETE FROM users WHERE user_id='" + inputDelUser + "';";
esql.executeUpdate(query);
System.out.println("Successfully deleted user: " + inputDelUser );
rs.close();
return0;
 }//end user exist

//if user doesnt exist
else
 {
System.out.println("Sorry that user doesn't exist. Please make sure you have the right user_id");
rs.close();
continue;
 }//end user doest exist
 }//end while

 }catch(Exceptione){
System.err.println( e.getMessage() );
return1;}
return0;
	}


/////////////////////////////////////////////////////////////CHANGES//////////
/////////////////////////////////////////////////////////////CHANGES//////////
//////////////////////////////////////////////////////////////END//////////////
/*
 * Reads the users choice given from the keyboard
 * @int
 **/
publicstaticintreadChoice() {
intinput;
// returns only if a correct value is given.
do {
System.out.print("Please make your choice: ");
try { // read the integer, parse it and break.
input = Integer.parseInt(in.readLine());
break;
 }catch (Exceptione) {
System.out.println("Your input is invalid!");
continue;
 }//end try
 }while (true);
returninput;
 }//end readChoice

publicstaticintLogInQuery(EmbeddedSQLesql){
try{
System.out.println("You are trying to Login as an existing user, enter your Username (Make sure it is only 9 chars long) or press 9 to go back");
//Keep asking the user to login if they want to log in
while( true ){
System.out.println("Please specify your username or press 9 to go back:");
StringinputUser = in.readLine();

if( inputUser.equals("9") ) return1; //exit the loop and go back to home screen

Stringquery = "SELECT COUNT(user_id) FROM users WHERE user_id='";
query += inputUser + "'";

intoutput = esql.executeLoginQuery (query);
System.out.println("This is output" + output);

//check if username is valid, if not 1 then either output = 0 or > 2 which is a problem
if( output != 1 ) {
System.out.println("Sorry, that username isn't in our records. Please try again.");
continue;
 }//end username not found

//if output is 1 then only 1 record with that user found (good)
elseif( output == 1 ){
System.out.println("Please specify your password:");
StringinputPass = in.readLine();
query = "SELECT COUNT(user_id) FROM users WHERE user_id='" + inputUser + "' AND password='" + inputPass + "'";

intoutput2 = esql.executeLoginQuery(query); //returns number of query occurances

//if password doesn't match records ask for username again
if( output2 != 1 ){
System.out.println("Sorry, the username and password don't match up. Please try again.");
continue;
 }//end password fail

//if password correct then save current user info and log them in
elseif( output2 == 1 ){
currentUser = inputUser;
System.out.println("Successfully logged in! Welcome " + currentUser + "!");
break;
 }//end password success
 }//end username found
 }//end user login prompt
 }catch(Exceptione){
System.err.println (e.getMessage ());
return1;
 }//end catch
return0;
 }//end LoginQuery

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
publicstaticintRegisterQuery(EmbeddedSQLesql){
System.out.println("You have selected to register yourself as a user. Please specify the following information.");
try{
System.out.println("Enter a username you want (Username must be 9 chars or less long):");
Stringinput = in.readLine();

//if( input.length() > 9 ) { System.out.println("Invalid Username (more than 9 chars)"); continue; }

Stringquery = "INSERT INTO Users (user_id, password, first_name, middle_name, last_name, e_mail, street1, street2, state, country, zipcode, balance)" +
"VALUES ('" + input + "','";

System.out.println("Enter your password:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your first name:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your middle name:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your last name:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your email:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your street1:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your street2:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your state:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your country:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your zipcode:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your balance:");
input = in.readLine();
query += input + "')";

esql.executeUpdate (query);

 }catch(Exceptione){
System.err.println (e.getMessage ());
return1;
 }//end catch
return0;
 }//end RegisterQuery
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

/* 
 public static void RegisterQuery(EmbeddedSQL esql){
 try{
 String query = "SELECT C.sid, COUNT(C.pid) FROM Suppliers S, Catalog C "
 + "WHERE C.sid=S.sid GROUP BY C.sid;";

 int rowCount = esql.executeQuery (query);
 System.out.println ("total row(s): " + rowCount);
 }catch(Exception e){
 System.err.println (e.getMessage ());
 }
 }//end Query1

 public static void Query2(EmbeddedSQL esql){
 try{
 String query = "SELECT C.sid, COUNT(C.pid) FROM Suppliers S, Catalog C "
 + "WHERE C.sid=S.sid GROUP BY C.sid HAVING COUNT(C.pid)>=3;";

 int rowCount = esql.executeQuery (query);
 System.out.println ("total row(s): " + rowCount);

 }catch(Exception e){
 System.err.println (e.getMessage ());
 }
 }//end Query2

 public static void Query3(EmbeddedSQL esql){
 try{
 String query = "SELECT sname, COUNT(C.pid) FROM Suppliers S, Catalog C "
 + "WHERE pid IN (SELECT pid FROM Parts WHERE color='Green') AND S.sid=C.sid "
 + "GROUP BY sname";

 int rowCount = esql.executeQuery (query);
 System.out.println ("total row(s): " + rowCount);

 }catch(Exception e){
 System.err.println (e.getMessage ());
 }
 }//end Query3

 public static void Query4(EmbeddedSQL esql){
 try{
 String query = "SELECT P.pname, MAX(C.cost) FROM Parts P, Catalog C "
 + "WHERE C.sid IN (SELECT temp.sid "
 + "FROM (SELECT sid, pid FROM Catalog WHERE pid IN (SELECT pid FROM Parts WHERE color='Red')) as temp, "
 + "(SELECT sid, pid FROM catalog WHERE PID IN (SELECT pid FROM Parts WHERE color='Green')) as temp2 "
 + "WHERE temp.sid = temp2.sid) "
 + "GROUP BY P.pname;";

 int rowCount = esql.executeQuery (query);
 System.out.println ("total row(s): " + rowCount);

 }catch(Exception e){
 System.err.println (e.getMessage ());
 }
 }//end Query4

 public static void Query5(EmbeddedSQL esql){
 try{
 String query = "SELECT DISTINCT pname FROM Parts "
 + "WHERE pid IN (SELECT pid FROM Catalog WHERE cost<";

 System.out.print("\tEnter cost: $");
 String input = in.readLine();
 query+=input;
 query += ");";

 int rowCount = esql.executeQuery (query);
 System.out.println ("total row(s): " + rowCount);
/*
 * MODIFIED BY JUSTIN KAHAL AND THOMAS DESMOND
 * SSID:860892022
 *
 * Template JAVA User Interface
 * =============================
 *
 * Database Management Systems
 * Department of Computer Science &amp; Engineering
 * University of California - Riverside
 *
 * Target DBMS: 'Postgres'
 *
 */


importjava.sql.DriverManager;
importjava.sql.Connection;
importjava.sql.Statement;
importjava.sql.ResultSet;
importjava.sql.ResultSetMetaData;
importjava.sql.SQLException;
importjava.io.File;
importjava.io.FileReader;
importjava.io.BufferedReader;
importjava.io.InputStreamReader;


///////////////////////////////////////////////////////
////////////////////////////////////////////////////
/////////	ADD THIS IMPORT ////////////////
importjava.util.Scanner;

/**
 * This class defines a simple embedded SQL utility class that is designed to
 * work with PostgreSQL JDBC drivers.
 *
 */
publicclassEmbeddedSQL {


publicstaticStringcurrentUser;
publicstaticbooleansuperUser = false;
// reference to physical database connection.
privateConnection_connection = null;

// handling the keyboard inputs through a BufferedReader
// This variable can be global for convenience.
staticBufferedReaderin = newBufferedReader(
newInputStreamReader(System.in));

/**
 * Creates a new instance of EmbeddedSQL
 *
 * @param hostname the MySQL or PostgreSQL server hostname
 * @param database the name of the database
 * @param username the user name used to login to the database
 * @param password the user login password
 * @throws java.sql.SQLException when failed to make a connection.
 */
publicEmbeddedSQL (Stringdbname, Stringdbport, Stringuser, Stringpasswd) throwsSQLException {

System.out.print("Connecting to database...");
try{
// constructs the connection URL
Stringurl = "jdbc:postgresql://localhost:" + dbport + "/" + dbname;
System.out.println ("Connection URL: " + url + "\n");

// obtain a physical connection
this._connection = DriverManager.getConnection(url, user, passwd);
System.out.println("Done");
 }catch (Exceptione){
System.err.println("Error - Unable to Connect to Database: " + e.getMessage() );
System.out.println("Make sure you started postgres on this machine");
System.exit(-1);
 }//end catch
 }//end EmbeddedSQL

/**
 * Method to execute an update SQL statement. Update SQL instructions
 * includes CREATE, INSERT, UPDATE, DELETE, and DROP.
 *
 * @param sql the input SQL string
 * @throws java.sql.SQLException when update failed
 */
publicvoidexecuteUpdate (Stringsql) throwsSQLException {
// creates a statement object
Statementstmt = this._connection.createStatement ();

// issues the update instruction
stmt.executeUpdate (sql);

// close the instruction
stmt.close ();
 }//end execute update 

intgetVidId(Stringquery)
 {
try{
// creates a statement objectv
Statementstmt = this._connection.createStatement (); 

// issues the query instruction
ResultSetrs = stmt.executeQuery(query);

while(rs.next())
 {
intvid_id = Integer.parseInt(rs.getString(1));
returnvid_id;
 }
return -1;
 }catch(Exceptione){
System.err.println (e.getMessage ());} 
return -1;
	}//end getVidID

publicintexecuteOrder (Stringquery, intprice) throwsSQLException
 {
// creates a statement objectv
Statementstmt = this._connection.createStatement ();

// issues the query instruction
ResultSetrs = stmt.executeQuery(query);

while(rs.next())
 {
intbalance = Integer.parseInt(rs.getString(1));
if(balance - price >= 0)
 {
System.out.println("You would have negative balance if movie is ordered you cannot order movie"); 
return -1;
 }
else
 {
Stringupdatebal = "UPDATE users SET balance = balance - " + price + " WHERE user_id = '" + currentUser + "'";
stmt.executeUpdate(updatebal); //I changed this part might be problematic
Stringnewbalance = "SELECT balance FROM users WHERE user_id='" + currentUser + "'";
System.out.print("New Balance if ordered: ");
executeStringQuery(newbalance, 1);
return1;
 }	
 }
return -1;
	}

publicvoidexecuteStringQuery (Stringquery, intline) throwsSQLException
	{
// creates a statement objectv
Statementstmt = this._connection.createStatement ();

// issues the query instruction
ResultSetrs = stmt.executeQuery (query);

/*
 ** obtains the metadata object for the returned result set. The metadata
 ** contains row and column info.
 */
System.out.println("--------------");
while(rs.next())
 {
System.out.println(rs.getString(line));
 }
	}

publicintexecuteCheckTitle (Stringquery) throwsSQLException
	{
try{
// creates a statement objectv
Statementstmt = this._connection.createStatement ();
System.out.println("Connection Success");

// issues the query instruction
ResultSetrs = stmt.executeQuery(query);
System.out.println("Query exectued success");

System.out.println("--------------");

while(rs.next())
 {
System.out.print("Are you sure you would like to order (yes or no): ");
System.out.println(rs.getString(2) + " Price: " + rs.getString(3));
Stringinput = in.readLine();
System.out.println();
 }

returnInteger.parseInt(rs.getString(3));
 }catch(Exceptione){
System.err.println (e.getMessage ());}
return -1;
	}

publicintexecuteLoginQuery (Stringquery) throwsSQLException
	{
// creates a statement objectv
Statementstmt = this._connection.createStatement ();

// issues the query instruction
ResultSetrs = stmt.executeQuery (query);

/*
 ** obtains the metadata object for the returned result set. The metadata
 ** contains row and column info.
 */
ResultSetMetaDatarsmd = rs.getMetaData ();
intnumCol = rsmd.getColumnCount ();
rs.next();
intresultset = Integer.parseInt(rs.getString(numCol));
System.out.println(resultset);

stmt.close();
returnresultset;
// iterates through the result set and output them to standard out.
/* boolean outputHeader = true;
 while (rs.next()){
 if(outputHeader){
 for(int i = 1; i <= numCol; i++){
 System.out.print(rsmd.getColumnName(i) + "\t");
 }
 System.out.println();
 outputHeader = false;
 }
 for (int i=1; i<=numCol; ++i)
 System.out.print (rs.getString (i) + "\t");
 System.out.println ();
 ++rowCount;
 }//end while
 stmt.close ();
 return rowCount;
 */
 }

/**
 * Method to execute an input query SQL instruction (i.e. SELECT). This
 * method issues the query to the DBMS and outputs the results to
 * standard out.
 *
 * @param query the input query string
 * @return the number of rows returned
 * @throws java.sql.SQLException when failed to execute the query
 */
publicintexecuteQuery (Stringquery) throwsSQLException {
// creates a statement objectv
Statementstmt = this._connection.createStatement ();

// issues the query instruction
ResultSetrs = stmt.executeQuery (query);

/*
 ** obtains the metadata object for the returned result set. The metadata
 ** contains row and column info.
 */
ResultSetMetaDatarsmd = rs.getMetaData ();
intnumCol = rsmd.getColumnCount ();
introwCount = 0;

// iterates through the result set and output them to standard out.
booleanoutputHeader = true;
while (rs.next()){
if(outputHeader){
for(inti = 1; i <= numCol; i++){
System.out.print(rsmd.getColumnName(i) + "\t");
 }
System.out.println();
outputHeader = false;
 }
for (inti=1; i<=numCol; ++i)
System.out.print (rs.getString (i) + "\t");
System.out.println ();
 ++rowCount;
 }//end while
stmt.close ();
returnrowCount;
 }//end executeQuery

/**
 * Method to close the physical connection if it is open.
 */
publicvoidcleanup(){
try{
if (this._connection != null){
this._connection.close ();
 }//end if
 }catch (SQLExceptione){
// ignored.
 }//end try
 }//end cleanup

/**
 * The main execution method
 *
 * @param args the command line arguments this inclues the <mysql|pgsql> <login file>
 */
publicstaticvoidmain (String[] args) {
if (args.length != 4) {
System.err.println (
"Usage: " +
"java [-classpath <classpath>] " +
EmbeddedSQL.class.getName () +
" <dbname> <port> <user> <passwd>");
return;
 }//end if

Greeting();
EmbeddedSQLesql = null;
try{
// use postgres JDBC driver.
Class.forName ("org.postgresql.Driver").newInstance ();
// instantiate the EmbeddedSQL object and creates a physical
// connection.
Stringdbname = args[0];
Stringdbport = args[1];
Stringuser = args[2];
Stringpasswd = args[3];
esql = newEmbeddedSQL (dbname, dbport, user, passwd);

booleankeepon = true;
while(keepon) {
// These are sample SQL statements
System.out.println("WELCOME! Are you an existing user?");
System.out.println("---------");
System.out.println("0. Yes I am!");
System.out.println("1. No, I need to register!");
System.out.println("9. < EXIT (Stop the program)");


switch (readChoice()){
case0: if(LogInQuery(esql) == 0 ) keepon = false; break;
case1: if(RegisterQuery(esql) == 0 ) keepon = false; break;
case9: return;
default : System.out.println("Unrecognized choice!"); break;
 }//end switch
 }//end while 

//Will need to turn keepon to false after logging in 

booleanhomescreen = true; 
while(homescreen)
 {
System.out.println("---------");
System.out.println("Welcome to MovieNet");
System.out.println("---------");
System.out.println("0. See list of movies");
System.out.println("1. Order a movie");
System.out.println("2. Rate a movie NOT WORKING");
System.out.println("3. Look at wall NOT WORKING");
System.out.println("4. Add to balance");
System.out.println("5. List favorite movie");
System.out.println("6. Add a favorite movie");

//these will only be visible if you are super user
if( superUser ){
System.out.println("\nSuper User Options:");
System.out.println("10. Register a new movie WORKING");
System.out.println("11. Delete an existing user NOT WORKING");
 }

/////////////////////////////////////////////////////////////CHANGES//////////
if( !superUser ) System.out.println("8. Login as SuperUser");
/////////////////////////////////////////////////////////////CHANGES//////////
System.out.println("9. < EXIT (Stop the program)");

switch (readChoice()){
case0: ListMovies(esql); break;
case1: OrderMovie(esql); break;
case2: break;
case3: break;
case4: IncreaseBalance(esql); break;
case5: ListFavs(esql); break;
case6: InsertFav(esql); break;
/////////////////////////////////////////////////////////////CHANGES//////////
case8: if( !superUser ) {LoginAsSuper(esql);}; break;
/////////////////////////////////////////////////////////////CHANGES//////////

case9: return;
case10: if( superUser ) {RegisterMovie(esql);}; break;
case11: if( superUser ) {deleteUser(esql);}; break;
default : System.out.println("Unrecognized choice!"); break;
 }


 }


 }catch(Exceptione) {
System.err.println (e.getMessage ());
 }finally{
// make sure to cleanup the created table and close the connection.
try{
if(esql != null) {
System.out.print("Disconnecting from database...");
esql.cleanup ();
System.out.println("Done\n\nBye !");
 }//end if
 }catch (Exceptione) {
// ignored.
 }//end try
 }//end try
 }//end main



publicstaticvoidListMovies(EmbeddedSQLesql)
	{
try{
Stringquery = "SELECT title FROM Video";
esql.executeStringQuery(query, 1);

 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
	}

publicstaticvoidListFavs(EmbeddedSQLesql)
	{
try{
Stringquery = "SELECT title FROM video, likes WHERE likes.user_id = '" + currentUser + 
"' AND likes.video_id = video.video_id";
esql.executeStringQuery(query, 1);
 }catch(Exceptione){
System.err.println (e.getMessage ());}
	}

publicstaticvoidInsertFav(EmbeddedSQLesql)
	{
try{
System.out.print("Enter title of move you would like to add as favorite: ");
Stringtitle = in.readLine();
Stringquery = "SELECT video_id FROM video WHERE title = '" + title + "'";

intvid_id = esql.getVidId(query);

System.out.println("GOT THE VID ID");
query = "INSERT INTO likes (user_id, video_id) VALUES ('" + currentUser + "', " + vid_id + ")";
esql.executeUpdate(query);

System.out.println("New favorite added");

 }catch(Exceptione){
System.err.println (e.getMessage ());} 
	}

publicstaticvoidOrderMovie(EmbeddedSQLesql)
	{
try{ 
System.out.print("Title of movie you would like to order: ");
Stringtitle = in.readLine();

//Grabbing title and will print it
Stringquery = "SELECT * FROM video WHERE title = ";
query += title + "'";

intprice = esql.executeCheckTitle(query);

query = "SELECT balance FROM users WHERE user_id ='" + currentUser + "';";
inttemp = esql.executeOrder(query, price);

if( temp == 1 )
System.out.println("Successfully ordered: " + title);
 }catch(Exceptione){
System.err.println (e.getMessage ());}
	}


publicstaticvoidIncreaseBalance(EmbeddedSQLesql)
	{
try{

System.out.print("How much would you like to add to balance?: ");
Stringstringbal = in.readLine();
intintbal = Integer.parseInt(stringbal);

if(intbal <= 0)
 {
System.out.println("Must input number greater than $0");
return;
 }

System.out.println("About to update");


Stringquery = "UPDATE users SET balance = balance +" + intbal + " WHERE user_id = '"
 + currentUser + "'";

esql.executeUpdate(query);

System.out.println("UPDATED");

Stringnewbalance = "SELECT balance FROM users WHERE user_id='" + currentUser + "'";
System.out.print("New Balance after addition: ");
esql.executeStringQuery(newbalance, 1); 

return;

 }catch(Exceptione){	
System.err.println (e.getMessage ());}
	}

// NOT FULLY IMPLEMENTED NEED TO GRAB VIDEO ID AND THEN RATE MOVIE
publicstaticvoidRateMovie(EmbeddedSQLesql)
	{
try{

System.out.print("Title of movie you would like to rate: ");
Stringtitle = in.readLine();
System.out.print("What would you like to rate the movie (0 - 10): ");
Stringrating = in.readLine();

Stringquery = "SELECT title FROM Video";

//String output = esql.executeStringQuery(query); //Cant use executeString This way!!!!

 }catch(Exceptione){
System.err.println (e.getMessage ());}
	}

publicstaticvoidGreeting(){
System.out.println(
"\n\n*******************************************************\n" +
" User Interface \n" +
"*******************************************************\n");
 }//end Greeting

publicstaticintLoginAsSuper(EmbeddedSQLesql)
 {
try{
System.out.println("Verifying that you are a Super User");
Stringquery = "SELECT COUNT(super_user_id) FROM super_user WHERE super_user_id='"
 + currentUser + "';";

intresultset = esql.executeLoginQuery(query);

if( resultset != 1 ){
System.out.println("I'm sorry but you are not a registered super user");
return1;
 }

elseif( resultset == 1 ){
System.out.println("Welcome super user " + currentUser);
superUser = true;
return0;
 }

 }catch(Exceptione){
System.err.println( e.getMessage() );
return1;
 }
return0;
 }//end LoginAsSuper

///SOME NEW FUNCTIONS HERE/////////////////////////////////////////////////////////////////////////////////
publicstaticintqueryGenre(EmbeddedSQLesql)
 {
intgenre_id = -1;
try{
//Added some Genre code here//////////////////////////////////////////////
System.out.println("What is the genre of this video?");
StringinputGenre = in.readLine();

StringgetGenre = "SELECT genre_id FROM genre WHERE genre_name='" + inputGenre + "';";
Statementstmt = esql._connection.createStatement();
ResultSetrs = stmt.executeQuery(getGenre);
ResultSetMetaDatarsmd = rs.getMetaData();

if( rs.next() ) genre_id = Integer.parseInt(rs.getString(1));

//only care if the genre doesn't already exist
else
 {
getGenre = "SELECT MAX(genre_id) FROM genre;";
rs = stmt.executeQuery(getGenre);
rsmd = rs.getMetaData();

if( rs.next() ) genre_id = Integer.parseInt(rs.getString(1))+1;
elsegenre_id = 1;

esql.executeUpdate( "INSERT INTO genre (genre_id, genre_name) VALUES (" + genre_id + ", '" + inputGenre + "');" );
rs.close();
 }

////////////////////////////////////////////////////////////////////////

 }catch(Exceptione){
System.err.println( e.getMessage() );
return -1;}
returngenre_id;
 }

publicstaticvoidcategorizeVideo(EmbeddedSQLesql, intvideo_id, intgenre_id )
 {
System.out.println("VIDEOID: " + video_id + " GenreID: " + genre_id);
try{
StringgetGenre = "SELECT COUNT(*) FROM categorize WHERE genre_id=" + genre_id + " AND video_id=" + video_id + ";";
Statementstmt = esql._connection.createStatement();
ResultSetrs = stmt.executeQuery(getGenre);
ResultSetMetaDatarsmd = rs.getMetaData();


//only care if this video_id isn't already in the list
if( !rs.next() || rs.getString(1).equals("0") )
esql.executeUpdate( "INSERT INTO categorize (video_id, genre_id) VALUES (" + video_id + ", " + genre_id + ");" );

 }catch(Exceptione){
System.err.println( e.getMessage() );}
 }

//////END OF SEGMENTED NEW FUNCTIONS///////////////////////////////////////////////////////////////////////

//Register a new video, creating new video_id, season_id, and series_id if necessary
publicstaticintRegisterMovie(EmbeddedSQLesql)
 {
try{
booleanisSeries = false;
System.out.println("Please enter the following information to register a new movie/series");

System.out.println("What is the title of this video?");
StringinputTitle = in.readLine();

///ADDEDD//////////////////////////////////////////////////////////
intgenre_id = queryGenre(esql);
if( genre_id == -1 ) System.out.println("Problem when creating genre");
////ADDEDD//////////////////////////////////////////////////////////


System.out.println("Is this video part of a series (y/n)?");
StringinputSeries = in.readLine();

StringinputSeasonNum = "";
StringinputEpNum = "";
StringinputYear = "";
intinputOnlinePrice;
intinputDvdPrice;
intinputRating;
intseries_id;
intseason_id;

//if this is a season get season info
if( inputSeries.equals("y") ){
isSeries = true;

System.out.println("What season number is this?");
inputSeasonNum = in.readLine();

System.out.println("What episode is this?");
inputEpNum = in.readLine();
 }

System.out.println("What year did this come out?");
inputYear = in.readLine();

System.out.println("How much should the online price be?");
inputOnlinePrice = Integer.parseInt(in.readLine());

System.out.println("How much should the dvd price be?");
inputDvdPrice = Integer.parseInt(in.readLine());

System.out.println("How would you rate this (0-10)?");
inputRating = Integer.parseInt(in.readLine());

//this will get the video id
Stringquery = "SELECT MAX(video_id) FROM video;";
Statementstmt = esql._connection.createStatement ();
ResultSetrs = stmt.executeQuery (query);
ResultSetMetaDatarsmd = rs.getMetaData ();
intnumCol = rsmd.getColumnCount ();
rs.next();
intvideo_id = Integer.parseInt(rs.getString(numCol)) + 1;
System.out.println(video_id);
stmt.close();

//this will check if the series is already in record if not then it adds it
if( isSeries ){
stmt = esql._connection.createStatement();
StringgetSeriesId = "SELECT series_id FROM series WHERE title='" + inputTitle + "'";
rs = stmt.executeQuery (getSeriesId);
rsmd = rs.getMetaData ();
numCol = rsmd.getColumnCount ();

//get the series id if it already exists
if(rs.next()) series_id = Integer.parseInt(rs.getString(numCol));
//if series id doesnt already exist then create a new series value
else{
getSeriesId = "SELECT MAX(series_id) FROM series;";
rs = stmt.executeQuery(getSeriesId);
rsmd = rs.getMetaData();
if(rs.next()) series_id = Integer.parseInt(rs.getString(1)) + 1;
elseseries_id = 1;

//add series information to database
StringinsertSeries = "INSERT INTO series (series_id, title) VALUES (" + series_id + ", '" + inputTitle + "');";
esql.executeUpdate(insertSeries);
 }

StringgetSeasonId = "SELECT season_id FROM season WHERE series_id='" + series_id + "' AND season_number='" + inputSeasonNum + "';";
rs = stmt.executeQuery(getSeasonId);
rsmd = rs.getMetaData();
numCol = rsmd.getColumnCount();

//if the season id already exists grab the season id
if(rs.next()) season_id = Integer.parseInt(rs.getString(1));
//if the season id doesn't exist then place insert new season value
else{
getSeasonId = "SELECT MAX(season_id) FROM season;";
rs = stmt.executeQuery(getSeasonId);
rsmd = rs.getMetaData();
if(rs.next()) season_id = Integer.parseInt(rs.getString(1)) + 1;
elseseason_id = 1;

StringinsertSeason = "INSERT INTO season (season_id, series_id, season_number ) VALUES (" + season_id + ", " + series_id + ", '" + inputSeasonNum + "');";
esql.executeUpdate(insertSeason);
 }

StringinsertVideo = "INSERT INTO video (video_id, title, year, online_price, dvd_price, rating, episode, season_id) VALUES " + 
"(" + video_id + ",'" + inputTitle + "', '" + inputYear + "', " + inputOnlinePrice + ", " + inputDvdPrice + ", " + inputRating + ", " + inputEpNum + ", " + season_id + ");";

esql.executeUpdate(insertVideo);

stmt.close();
 }//End if video is a series

elseif( !isSeries )
 {
StringinsertVideo = "INSERT INTO video (video_id, title, year, online_price, dvd_price, rating) VALUES " + 
"(" + video_id + ",'" + inputTitle + "', '" + inputYear + "', " + inputOnlinePrice + ", " + inputDvdPrice + ", " + inputRating + ");";
stmt = esql._connection.createStatement ();
esql.executeUpdate(insertVideo);
stmt.close();
 }//End if video is not series

///ADDDEDDD//////////////////////////////////
categorizeVideo(esql, video_id, genre_id);
///ADDEDDDD/////////////////////////////////
 }catch(Exceptione){
System.err.println( e.getMessage() );
return1;
 }
return0;
 }

///////////////////////////////////////////////////////////////START//////////
/////////////////////////////////////////////////////////////CHANGES//////////
//this will delete a user from the database (only if user is superUser)
publicstaticintdeleteUser(EmbeddedSQLesql)
	{
try{
System.out.println("In order to delete a specific movie instance you must specify the following information");
while( true )
 {
System.out.println("Please specify a user_id that you want to delete or press 9 to cancel");
StringinputDelUser = in.readLine();

if( inputDelUser.equals("9") ) break;
elseif( inputDelUser.equals(currentUser) )
 {
System.out.println("You cannot delete youself, sorry.");
continue;
 }

Stringquery = "Select COUNT(user_id) FROM users WHERE user_id='" + inputDelUser + "';";

Statementstmt = esql._connection.createStatement ();
ResultSetrs = stmt.executeQuery(query);
ResultSetMetaDatarsmd = rs.getMetaData();

//if the user does exist
if(rs.next() && Integer.parseInt(rs.getString(1)) == 1 )
 {
query = "DELETE FROM users WHERE user_id='" + inputDelUser + "';";
esql.executeUpdate(query);
System.out.println("Successfully deleted user: " + inputDelUser );
rs.close();
return0;
 }//end user exist

//if user doesnt exist
else
 {
System.out.println("Sorry that user doesn't exist. Please make sure you have the right user_id");
rs.close();
continue;
 }//end user doest exist
 }//end while

 }catch(Exceptione){
System.err.println( e.getMessage() );
return1;}
return0;
	}


/////////////////////////////////////////////////////////////CHANGES//////////
/////////////////////////////////////////////////////////////CHANGES//////////
//////////////////////////////////////////////////////////////END//////////////
/*
 * Reads the users choice given from the keyboard
 * @int
 **/
publicstaticintreadChoice() {
intinput;
// returns only if a correct value is given.
do {
System.out.print("Please make your choice: ");
try { // read the integer, parse it and break.
input = Integer.parseInt(in.readLine());
break;
 }catch (Exceptione) {
System.out.println("Your input is invalid!");
continue;
 }//end try
 }while (true);
returninput;
 }//end readChoice

publicstaticintLogInQuery(EmbeddedSQLesql){
try{
System.out.println("You are trying to Login as an existing user, enter your Username (Make sure it is only 9 chars long) or press 9 to go back");
//Keep asking the user to login if they want to log in
while( true ){
System.out.println("Please specify your username or press 9 to go back:");
StringinputUser = in.readLine();

if( inputUser.equals("9") ) return1; //exit the loop and go back to home screen

Stringquery = "SELECT COUNT(user_id) FROM users WHERE user_id='";
query += inputUser + "'";

intoutput = esql.executeLoginQuery (query);
System.out.println("This is output" + output);

//check if username is valid, if not 1 then either output = 0 or > 2 which is a problem
if( output != 1 ) {
System.out.println("Sorry, that username isn't in our records. Please try again.");
continue;
 }//end username not found

//if output is 1 then only 1 record with that user found (good)
elseif( output == 1 ){
System.out.println("Please specify your password:");
StringinputPass = in.readLine();
query = "SELECT COUNT(user_id) FROM users WHERE user_id='" + inputUser + "' AND password='" + inputPass + "'";

intoutput2 = esql.executeLoginQuery(query); //returns number of query occurances

//if password doesn't match records ask for username again
if( output2 != 1 ){
System.out.println("Sorry, the username and password don't match up. Please try again.");
continue;
 }//end password fail

//if password correct then save current user info and log them in
elseif( output2 == 1 ){
currentUser = inputUser;
System.out.println("Successfully logged in! Welcome " + currentUser + "!");
break;
 }//end password success
 }//end username found
 }//end user login prompt
 }catch(Exceptione){
System.err.println (e.getMessage ());
return1;
 }//end catch
return0;
 }//end LoginQuery

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
publicstaticintRegisterQuery(EmbeddedSQLesql){
System.out.println("You have selected to register yourself as a user. Please specify the following information.");
try{
System.out.println("Enter a username you want (Username must be 9 chars or less long):");
Stringinput = in.readLine();

//if( input.length() > 9 ) { System.out.println("Invalid Username (more than 9 chars)"); continue; }

Stringquery = "INSERT INTO Users (user_id, password, first_name, middle_name, last_name, e_mail, street1, street2, state, country, zipcode, balance)" +
"VALUES ('" + input + "','";

System.out.println("Enter your password:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your first name:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your middle name:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your last name:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your email:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your street1:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your street2:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your state:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your country:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your zipcode:");
input = in.readLine();
query += input + "','";

System.out.println("Enter your balance:");
input = in.readLine();
query += input + "')";

esql.executeUpdate (query);

 }catch(Exceptione){
System.err.println (e.getMessage ());
return1;
 }//end catch
return0;
 }//end RegisterQuery
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

/* 
 public static void RegisterQuery(EmbeddedSQL esql){
 try{
 String query = "SELECT C.sid, COUNT(C.pid) FROM Suppliers S, Catalog C "
 + "WHERE C.sid=S.sid GROUP BY C.sid;";

 int rowCount = esql.executeQuery (query);
 System.out.println ("total row(s): " + rowCount);
 }catch(Exception e){
 System.err.println (e.getMessage ());
 }
 }//end Query1

 public static void Query2(EmbeddedSQL esql){
 try{
 String query = "SELECT C.sid, COUNT(C.pid) FROM Suppliers S, Catalog C "
 + "WHERE C.sid=S.sid GROUP BY C.sid HAVING COUNT(C.pid)>=3;";

 int rowCount = esql.executeQuery (query);
 System.out.println ("total row(s): " + rowCount);

 }catch(Exception e){
 System.err.println (e.getMessage ());
 }
 }//end Query2

 public static void Query3(EmbeddedSQL esql){
 try{
 String query = "SELECT sname, COUNT(C.pid) FROM Suppliers S, Catalog C "
 + "WHERE pid IN (SELECT pid FROM Parts WHERE color='Green') AND S.sid=C.sid "
 + "GROUP BY sname";

 int rowCount = esql.executeQuery (query);
 System.out.println ("total row(s): " + rowCount);

 }catch(Exception e){
 System.err.println (e.getMessage ());
 }
 }//end Query3

 public static void Query4(EmbeddedSQL esql){
 try{
 String query = "SELECT P.pname, MAX(C.cost) FROM Parts P, Catalog C "
 + "WHERE C.sid IN (SELECT temp.sid "
 + "FROM (SELECT sid, pid FROM Catalog WHERE pid IN (SELECT pid FROM Parts WHERE color='Red')) as temp, "
 + "(SELECT sid, pid FROM catalog WHERE PID IN (SELECT pid FROM Parts WHERE color='Green')) as temp2 "
 + "WHERE temp.sid = temp2.sid) "
 + "GROUP BY P.pname;";

 int rowCount = esql.executeQuery (query);
 System.out.println ("total row(s): " + rowCount);

 }catch(Exception e){
 System.err.println (e.getMessage ());
 }
 }//end Query4

 public static void Query5(EmbeddedSQL esql){
 try{
 String query = "SELECT DISTINCT pname FROM Parts "
 + "WHERE pid IN (SELECT pid FROM Catalog WHERE cost<";

 System.out.print("\tEnter cost: $");
 String input = in.readLine();
 query+=input;
 query += ");";

 int rowCount = esql.executeQuery (query);
 System.out.println ("total row(s): " + rowCount);