//Film data accessor object
package Model;

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

public class FilmDAO
{
    static Film oneFilm = null;
    static Connection conn = null;
    static Statement stmt = null;

    //Database information
    static String user = "iqbali";
    static String password = "Bredjcwe4";

    // Note none default port used, 6306 not 3306
    static String url = "jdbc:mysql://mudfoot.doc.stu.mmu.ac.uk:6306/"+user;



    public FilmDAO() {}

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

        try{
            // connecting to database
            // 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 static void closeConnection()
    {
        try {
            conn.close();
        } catch (SQLException e)
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    private static 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;
    }

   //Get all films - returns an array list
   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 static 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;
   }



//CRUD METHODS

   //CREATE
	   public int FilmCreate(String title, int year, String director, String stars, String review)
	   {
	   	// TODO Auto-generated method stub
		openConnection();
		PreparedStatement ps = null;
		int result = 0;
		try {
				//openConnection();
				String query = "INSERT INTO films(title,year,director,stars,review) VALUES(?,?,?,?,?)";
				ps = conn.prepareStatement(query);

				ps.setString(1, title);
				ps.setInt(2, year);
				ps.setString(3, director);
				ps.setString(4, stars);
				ps.setString(5, review);
				//ps.setInt(6, id);
				result = ps.executeUpdate();

				System.out.println(query);
				System.out.println(result);

				System.out.println("DEBUG:Film created successfuly ");
				closeConnection();
				}
			     catch (Exception e)
					{
			    	 	//System.out.println(se);
			    	 	System.out.println("\nDEBUG: Create error - see error trace above for help. ");
			    	 	//return;
					}

				 // all ok,  return
				 return result;
		}


   //RETRIEVE
	   public static Film getFilmByTitle(String title)
	   {
	        openConnection();
	        oneFilm=null;
	        // Create select statement and execute it
	        try{
	            String selectSQL = "SELECT * FROM films WHERE title LIKE'%"+title + "%'";
	            ResultSet rs2 = stmt.executeQuery(selectSQL);

	            // Retrieve the results
	            while(rs2.next()){
	                oneFilm = getNextFilm(rs2);
	            }

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

	       return oneFilm;
	   }


   //UPDATE
		public int FilmUpdate(String title, int year, String director, String stars, String review, int id)
		{
			// TODO Auto-generated method stub
		   PreparedStatement ps = null;
			openConnection();
			int result = 0;
			try{
			String query = "UPDATE films SET title=?,year=?,director=?,stars=?,review=? WHERE id=?";

			ps = conn.prepareStatement(query);
			ps.setString(1, title);
			ps.setInt(2, year);
			ps.setString(3, director);
			ps.setString(4, stars);
			ps.setString(5, review);
			ps.setInt(6, id);

			result = ps.executeUpdate();

			} catch (Exception e) {
				System.err.println(e);
			}
			closeConnection();
			return result;

		}


   //DELETE
	   public static int FilmDelete(int id)
	   {
	   	// TODO Auto-generated method stub
		   int status=0;
			openConnection();

			try{
				PreparedStatement ps=conn.prepareStatement("DELETE FROM films WHERE id=?");
				ps.setInt(1,id);
				status=ps.executeUpdate();

			closeConnection();

			}catch(Exception e){e.printStackTrace();}

			return status;
	   }

}