package wk6;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;


public class DVDDao {

    public DVDDao() {}

    private static Connection getDBConnection() {
        Connection dbConnection = null;
        try {
            Class.forName("org.sqlite.JDBC");
            } catch (ClassNotFoundException e) {
                System.out.println(e.getMessage());
            }
        try {
            String dbURL = "jdbc:sqlite:dvd.sqlite";
            dbConnection = DriverManager.getConnection(dbURL);
            return dbConnection;
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        return dbConnection;
        }

    public ArrayList<dvd> getAllDVDs() throws SQLException {
        System.out.println("Retrieving all dvds ...");
        Connection dbConnection = null;
        Statement statement = null;
        ResultSet result = null;
        String query = "SELECT * FROM collection;";
        ArrayList<dvd> dvds = new ArrayList<>();

        try {
            dbConnection = getDBConnection();
            statement = dbConnection.createStatement();
            System.out.println("DBQuery = " + query);
            result = statement.executeQuery(query); // Execute SQL query and record response to string
            while (result.next()) {

                int id = result.getInt("ID");
                String title = result.getString("Title");
                String genre = result.getString("Genre");
                int year = result.getInt("Year");

                dvds.add(new dvd(id,title,genre,year));
            }
        } catch(Exception e) {
            System.out.println("get all dvds: "+e);
        } finally {
            if (result != null) {
                result.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (dbConnection != null) {
                dbConnection.close();
            }
        }
        return dvds;
    }

    public dvd getDVD(int film_id) throws SQLException {

        dvd temp = null;
        Connection dbConnection = null;
        Statement statement = null;
        ResultSet result = null;

        String query = "SELECT * FROM collection WHERE ID =" + film_id + ";";

        try {
            dbConnection = getDBConnection();
            statement = dbConnection.createStatement();
            System.out.println("DBQuery: " + query);
            // execute SQL query
            result = statement.executeQuery(query);

            while (result.next()) {


                int id = result.getInt("ID");
                String title = result.getString("Title");
                String genre = result.getString("Genre");
                int year = result.getInt("Year");

                temp = new dvd(id,title,genre,year);

            }
        } finally {
            if (result != null) {
                result.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (dbConnection != null) {
                dbConnection.close();
            }
        }
        return temp;
    }

    public Boolean deleteDVD(int film_id) throws SQLException {
        System.out.println("Deleting dvd");
        Connection dbConnection = null;
        Statement statement = null;
        int result = 0;
        String query = "DELETE FROM collection WHERE ID = " + film_id + ";";
        try {
            dbConnection = getDBConnection();
            statement = dbConnection.createStatement();
            System.out.println(query);
            // execute SQL query
            result = statement.executeUpdate(query);
        } finally {
            if (statement != null) {
                statement.close();
            }
            if (dbConnection != null) {
                dbConnection.close();
            }
        }
        if (result == 1) {
            return true;
        } else {
            return false;
        }
    }

    public Boolean updateDVD(dvd dvd) throws SQLException {
        Connection dbConnection = null;
        Statement statement = null;

        String query = "UPDATE collection " + "SET ID = '" + dvd.getId() + "'," + "Title = '"
                + dvd.getTitle() + "'," + "Genre= '" + dvd.getGenre() + "'," + "Year= '" + dvd.getYear() +" WHERE ID = " + dvd.getId()
                + ";";

        try {
            dbConnection = getDBConnection();
            statement = dbConnection.createStatement();
            System.out.println(query);
            // execute SQL update
            statement.executeUpdate(query);

        } catch (SQLException e) {

            System.out.println(e.getMessage());
            return false;

        } finally {

            if (statement != null) {
                statement.close();
            }
            if (dbConnection != null) {
                dbConnection.close();
            }
        }
        return true;
    }

    public boolean insertRecordIntoCollectionTable(dvd in) throws SQLException{
        Connection dbConnection = null;
        Statement statement = null;

        String update = "INSERT INTO collection (ID, Title, Genre, Year) VALUES ("+in.getId()+",'"+in.getTitle()+"','"+in.getGenre()+"',"+in.getYear()+");";
        boolean ok = false;
            try {
                    dbConnection = getDBConnection();
                    statement = dbConnection.createStatement();
                    System.out.println(update);
        // execute SQL query
                    statement.executeUpdate(update);
                    ok = true;
                } catch (SQLException e) {
                    System.out.println(e.getMessage());
                } finally {
                    if (statement != null) {
                        statement.close();
                    }
                    if (dbConnection != null) {
                        dbConnection.close();
                    }

                }
            return ok;
    }
    }