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 = "";
	String password = "";
	// Note none default port used, 6306 not 3306
	String url = "jdbc:mysql://mudfoot.doc.stu.mmu.ac.uk:6306/boylanj?user=&password=";

	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=&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 int save(Film e) {
		int status = 0;
		try {
			openConnection();
			PreparedStatement ps = conn
					.prepareStatement("insert into user905(name,password,email,country) 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();

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

		return status;
	}
}