package model;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.sql.*;


public class FilmDAO {

	Film oneFilm = null;
	Connection conn = null;
    Statement stmt = null;
	String user = "enter_your_mysql_username_here";
    String password = "enter_your_mysql_password_here";
    // Note none default port used, 6306 not 3306
    String url = "jdbc:mysql://mudfoot.doc.stu.mmu.ac.uk:6306/boylanj?user=boylanj&password=";

	public FilmDAO() {}


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

		// connecting to database
		try{
			// connection string for demos database, username demos, password demos
 			conn = DriverManager.getConnection ("jdbc:mysql://mudfoot.doc.stu.mmu.ac.uk:6306/boylanj?user=boylanj&password=");
		    stmt = conn.createStatement();
		} catch(SQLException se) { System.out.println(se); }
    }
	private void closeConnection(){
		try {
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	private Film getNextFilm(ResultSet rs){
    	Film thisFilm=null;
		try {
			thisFilm = new Film(
					rs.getInt("id"),
					rs.getString("title"),
					rs.getInt("year"),
					rs.getString("director"),
					rs.getString("stars"),
					rs.getString("review"));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return thisFilm;
	}



   public ArrayList<Film> getAllFilms(){

		ArrayList<Film> allFilms = new ArrayList<Film>();
		openConnection();

	    // Create select statement and execute it
		try{
		    String selectSQL = "select * from films";
		    ResultSet rs1 = stmt.executeQuery(selectSQL);
	    // Retrieve the results
		    while(rs1.next()){
		    	oneFilm = getNextFilm(rs1);
		    	allFilms.add(oneFilm);
		   }

		    stmt.close();
		    closeConnection();
		} catch(SQLException se) { System.out.println(se); }

	   return allFilms;
   }

   public Film getFilmByID(int id){

		openConnection();
		oneFilm=null;
	    // Create select statement and execute it
		try{
		    String selectSQL = "select * from films where id="+id;
		    ResultSet rs1 = stmt.executeQuery(selectSQL);
	    // Retrieve the results
		    while(rs1.next()){
		    	oneFilm = getNextFilm(rs1);
		    }

		    stmt.close();
		    closeConnection();
		} catch(SQLException se) { System.out.println(se); }

	   return oneFilm;
   }

   public boolean deleteFilm (int id) {
	   openConnection();
		oneFilm=null;
	    // Create select statement and execute it
		try{
		    String selectSQL = "delete FROM films WHERE id="+id;
		    int rs1 = stmt.executeUpdate(selectSQL);
		    stmt.close();
		    closeConnection();
		} catch(SQLException se) { System.out.println(se); }

	   return false;
  }

   public static int save(Film e){
		int status=0;
		try{
			Connection con=FilmDAO.openConnection();
			PreparedStatement ps=con.prepareStatement("insert into user905(id,title,year,director,stars,review) values (?,?,?,?,?,?)");
			ps.setInt(1,e.getId());
			ps.setString(2,e.getTitle());
			ps.setInt(3,e.getYear());
			ps.setString(4,e.getDirector());
			ps.setString(5,e.getStars());
			ps.setString(6,e.getReview());

			status=ps.executeUpdate();

			con.close();
		}catch(Exception ex){ex.printStackTrace();}

		return status;
   }}