package model;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import java.sql.*;

/**
 * The DOM specification gives methods to find elements and attributes with specific names.
 * Element method getElementsByTagname(String id) yields a Nodelist containing all immediate subelements with the specified tag.
 * For a given element, getAttribute(String id) returns the value of the attribute whose id is the argument. getAttributeNode(String id) does the same thing,
 * except it delivers the whole attribute object instead of just the value.
 *  import connection to this class and also creating new class called FilmsDAO
 * creating public array list for films and creating method called
 * getallfilms
 * The CRUD operations are created in this class.
 * Create would create connection to the database.
 *  Retrieve would Retrieve the data from the database.
 *  Update would update the data which the user has inputed and update it in the database.
 *  Delete would let you delete a film, based on the id.
 *  I have created a array to store all films into a array.
 * @author Mohammed Adnan Hussain, 16028929
 */
// public class for film dao
public class FilmDAO {
	static Film oneFilm = null; // creating object for film equal to null
	static Connection conn = null; // creating object for db connection and setting it to null
	static Statement stmt = null; // Creating object for statement  setting it to null
	private Map<String, Film> contentProvider = new HashMap<String, Film>();

	// Adding the mysql workbench username and password, in order to connect to the database
	String user = "hussamoh";
	String password = "Brenkoil8";
	//  Using port 6306 instead of 3306 and connecting to the mudfoot server
	String url = "jdbc:mysql://mudfoot.doc.stu.mmu.ac.uk:6306/"+user;

	/**
	 * creating public filmdao method
	 * Declaring new film for the restful web service
	 * Setting the title of the film
	 */
	public FilmDAO() {
		Film film = new Film(123456,
				"Four Lions",
				2010,
				"Chris Morris, Omar, Hassan, Barry, Waj and Faisal",
				"Chris Morris, Omar, Hassan, Barry, Waj and Faisal",
				"Four Lions is an English movie released on 07 May, 2010. The movie is directed by Christopher Morris and featured Will Adamsdale and Adeel Akhtar as lead characters.");
		film.setTitle("Four Lions");
		contentProvider.put("123456789", film);
		film = new Film(222222, "Fast 9", 2019, "Paul Walker", "Paul, John, Adnan, smith", "5 star");
		film.setTitle("Fast 9");
		contentProvider.put("234567", film);
	} // CLose film dao method

	// Creating public map string for the film and getting the model
	public Map<String, Film> getModel(){
		// Return contentProvider
		return contentProvider;
	} // Close public map

	// Creating private void to open the connection
	private static void openConnection(){
		// loading jdbc driver for mysql
		try{
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch(Exception e) { System.out.println(e); }

		// Connecting to the films database
		try{
			// Connection string for the database, and adding the username and password for the database
			conn = DriverManager.getConnection
					("jdbc:mysql://mudfoot.doc.stu.mmu.ac.uk:6306/hussamoh?user=hussamoh&password=Brenkoil8");
			//(url, user, password);
			// Creating connection for the statement
			stmt = conn.createStatement();
		} catch(SQLException se) { System.out.println(se); }
	} // Close private void open connection
	// Creating private void to close the connection
	private static void closeConnection(){
		// Creating try to close the connection
		try {
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} // Close catch sql exception
	} //CLose private void close connection

	// Creating private film to get the next film in the result
	private static Film getNextFilm(ResultSet rs){
		Film thisFilm = null; // Creating film variable equal to null
		// Creating try to get the result of all the films
		try {
			thisFilm = new Film(
					//  Getting int for the result of the id
					rs.getInt("id"),
					// Getting string for the result of the film title
					rs.getString("title"),
					// Getting int for the result of the film year
					rs.getInt("year"),
					// Getting string for the result of the film director
					rs.getString("director"),
					// Getting string for the result of the film stars
					rs.getString("stars"),
					// Getting string for the result of the film reviews
					rs.getString("review"));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} // CLose catch sql exception
		return thisFilm; // Return the film
	} // Close private film to get the result of the next film
	/**
	 * Creating the crud methods
	 * Create, retrieve, update and delete
	 * Create: insertFilm(Film)- this inserts a new row into the table book.
	 * Read: getAllFilms() - this retrieves all rows; it would then return all the films from the database.
	 * Update: updateFilm(Film)- this updates an existing row in the database.
	 * Delete: deleteFilm(FIlm) - this removes an existing row in the database based on the FilmID.
	 * @return Mohammed Adnan Hussain, 16028929
	 */
	// Creating public array list to get/list all films from the array
	public static ArrayList<Film> getAllFilms() {
		ArrayList<Film> allFilms = new ArrayList<Film>();
		openConnection(); // open database connection


		// Creating select statement and executing it
		// Selecting everything from the films database
		try {
			// Creating string to create sql statement to select everything from the films table.
			String selectSQL = "select * from films LIMIT 10";
//			String selectSQL = "select * from films";
			// Execute the query and show the result
			ResultSet rs1 = stmt.executeQuery(selectSQL);
			// Retrieve the results
			while(rs1.next()){
				oneFilm = getNextFilm(rs1);
				allFilms.add(oneFilm);
			} // Close while loop
			stmt.close(); // close sql dtatement
			closeConnection(); // CLose connection
			disconnect(); // Callig the disconnect method
		} catch(SQLException se) { System.out.println(se); }
		// Otherwise, return all the films
		return allFilms;
	} // close public array list for get all films

	// Creating protected static void disconnect method for when the connection has been disconnected
	protected static void disconnect() throws SQLException {
		// If the connection is null, the connection will then be closed
		if (conn != null && !conn.isClosed()) {
			// CLose connection
			conn.close();
		} // CLose if statement for if connection equal to null
	} // CLose protected static void disconnect method

	// insert statement
	// Creating public static boolean for insert film, which creates database
	// Connection and creates sql query, which lets the user insert film to the database.
	// I have also created parameter Film films for insertFilms
	// Creating public boolean for insert film to insert films to the database

	public static boolean insertFilm(Film film) throws SQLException {
		openConnection(); // open database connection
		Statement statement = null; // statement object = null
		ResultSet resultset = null; // resultset object = null
		// Creating try to open the connection
		try {
			// Creating the INSERT statement from the parameters
			// set time inserted to be the current time on database server
			String selectSQL = "INSERT INTO films (id,title,year,director,stars,review) "
					+ "VALUES ('" + film.getId() + "', '" + film.getTitle() + "', '" + film.getYear() + "', '"
					+ film.getDirector() + "', '" + film.getStars() + "', '" + film.getReview() + "')";
			// Print message out onto the console
			System.out.println("DEBUG: Insert: " + selectSQL);
			int rs = stmt.executeUpdate(selectSQL);
			System.out.println("DEBUG: Insert successful ");
		} catch (SQLException se) {
			// Problem with update, return failure message
			System.out.println(se);
		} // CLose catch

		// all ok,  return
		return false;
	} // Close static void insertfilm table

	// Update statement
	// Creating public static boolean for update film, which creates database
	// Connection and creates sql query, which lets the user update the data from the database.
	// I have also created parameter student stu for UpdateStu
	// Creating public array list to update films from the array
	public static boolean updateFilm(Film film) throws SQLException {
		openConnection(); // open database connection
		Statement statement = null; // statement object = null
		ResultSet resultset = null; // resultset object = null
		// Creating try to open the connection
		try {
			// Creating the INSERT statement from the parameters
			// set time inserted to be the current time on database server
			String selectSQL =  "UPDATE films SET  title = '"+film.getTitle()+"', Year = '"+film.getYear()+"', Director = '"+film.getDirector()+"', "
					+ "Stars ='"+film.getStars()+"',  Review = '"+film.getReview()+"' WHERE id = " + film.getId();

			// Print message out onto the console
			System.out.println("DEBUG: Update: " + selectSQL);
			int rs = stmt.executeUpdate(selectSQL);
			System.out.println("DEBUG: Update successful ");
		} catch (SQLException se) {
			// Problem with update, return failure message
			System.out.println(se);
			System.out.println("\nDEBUG: Update error - see error trace above for help. ");
		} // CLose catch
		// all ok,  return
		return false;
	} // Close static void UpdateFilm table

	// Creating public  for delete films, which creates database
	// connection and creates sql query, which lets the user delete any data from
	// the database, which they wouldn't like
	// also creating DeleteFilm with parameter int ID
	// Delete statement

	public static boolean deleteFilm(int id) throws SQLException {
		openConnection(); // open database connection
		Statement statement = null; // statement object = null
		ResultSet resultset = null; // resultset object = null
		// Creating try to open the connection
		try {
			// Creating the INSERT statement from the parameters
			// set time inserted to be the current time on database server
			String selectSQL = "DELETE FROM films where Id =" + id;

			// Print message out onto the console
			System.out.println("DEBUG: Delete: " + selectSQL);
			int rs = stmt.executeUpdate(selectSQL);
			System.out.println("DEBUG: Delete successful ");
		} catch (SQLException se) {
			// Problem with update, return failure message
			System.out.println(se);
			System.out.println("\nDEBUG: Delete error - see error trace above for help. ");
		} // CLose catch
		// all ok,  return
		return false;
	} // Close static void UpdateFilm table

	// Get the film by ID
	public static Film getFilmByID(int id){
		// Open the connection
		openConnection();
		oneFilm = null; // Creating object for one film equal to null
		// Creating select statement and executing it
		try {
			// Creating string for the sql statement and selecting everything from the films table where the ID equal to ID.
			String selectSQL = "select * from films where id="+id;
			// Executing the query and showing the result
			ResultSet rs1 = stmt.executeQuery(selectSQL);
			// Retrieve the results, to get the next film
			while(rs1.next()){
				oneFilm = getNextFilm(rs1);
			} // Close while loop

			stmt.close(); // Close statement
			closeConnection(); // Close the connection
		} catch(SQLException se) { System.out.println(se);
		} // Close sql exception
		// Otherwise, return the film details for one film
		return oneFilm;
	} // CLose public film for get film by ID
	// getFilm(String filmTitle etc), would return	an array list of Films with	matching name (or substring).
	public static Film getFilms(String filmTitle, String filmYear, String filmDirector, String filmStars,
			String filmReview) {
		// TODO Auto-generated method stub
		return null;
	}
} // Close public class film dao