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