package model;

import java.sql.Connection;
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 = "khansha";
    String password = "Phrewthi9";
    // Note none default port used, 6306 not 3306
    String url = "jdbc:mysql://mudfoot.doc.stu.mmu.ac.uk:6306/"+user;

	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(url, user, 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 limit 20";
		    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 static Boolean deleteStu(String stuID) throws SQLException {
//	     {
//   openConnection();
//	           Statement statement = null;
//	           try {
//	             Class.forName("org.sqlite.JDBC");
//	             connection = DriverManager.getConnection("jdbc:sqlite:studentdb.sqlite");
//	             connection.setAutoCommit(false);
//	             System.out.println("Delete operation -database successfully opened");
//	             statement = connection.createStatement();
//	             String sql = "DELETE from Students where studentNumber='15066617';";
//	             statement.executeUpdate(sql);
//	             connection.commit();
//	             statement.close();
//	             connection.close();
//	           } catch ( Exception e ) {
//	             System.err.println( e.getClass().getName() + ": " + e.getMessage() );
//	             System.exit(0);
//	           }
//	           System.out.println("Delete operation successfully done");
//	     }
//	   //put your code here
//
//
//	   return false;
//	   }
//

//   public Film deleteFilmByID(int id){
//
//		openConnection();
//		oneFilm=null;
//
//	    // Create select statement and execute it
//		try{
//		    String selectSQL = "delete  from films where id="+id;
//		    statement.executeUpdate(sql);
//		    ResultSet rs1 = stmt.executeQuery(selectSQL);
//	    // Retrieve the results
//		    while(rs1.next()){
//
//		    }
//
//		    stmt.close();
//		    closeConnection();
//		} catch(SQLException se) { System.out.println(se); }
//
//	   return oneFilm;
//  }




}