/*
 *  ============================================================================================
 *  BookTableModel.java : A table model
 *  YOUR UPI: hkau513
 *  ============================================================================================
 */
import java.sql.*;
import java.util.*;
import javax.swing.table.*;

public class BookTableModel extends AbstractTableModel {
	private ArrayList<Book> bookList;
	private int rows, cols = 3;
	private String[] columnName;
	private Connection conn = null;
	static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String USER = "hkau515";
    static final String PASS = "6df92758";
    static final String URL = "jdbc:mysql://studdb-mysql.fos.auckland.ac.nz:3306/stu_hkau513_COMPSCI_280_C_S2_2017";

	public BookTableModel(String driver, String url, String username, String password)  {
		columnName = new String[]{"ID", "Title", "Author"};
		System.out.println("Connecting...");
		bookList = new ArrayList<Book>();
		try {
			Class.forName(JDBC_DRIVER);
            System.out.println("... Driver Loaded.");
            connect = DriverManager.getConnection(URL, USER, PASS);

	      	if (connect != null){
	        	System.out.println("...MySQL Server connected.");
				populateTable(null);
			}

	        else
				System.out.println("Failed to make connection!");
		} catch (ClassNotFoundException exp) {
			System.out.println(driver + " Not found");
		} catch (SQLException exp) {
			exp.printStackTrace();
		}
	}

	/**
	* Returns the value held at a particular point in the table
	* using the ArrayList<Book>
	* row = the element in the ArrayList
	* col = the particular property of the book
	*/
	public Object getValueAt(int row, int col) {
		Book bookRecord = bookList.get(row);
        switch(col){
            case 0:
                return bookRecord.getID();
            case 1:
                return bookRecord.getTitle();
        }
        return bookRecord.getAuthor();
    }

	/**
	* populate the table by executing a sql statement
	*/
	public void populateTable(String searchTitle) {
		bookList.clear();
		rows = 0;
		Statement statement = null;
		String sql = null;
		if (searchTitle == null){
			sql = "SELECT book_ID, title, author,ISBN,Publisher,Status FROM BOOKS";
		} else{
			sql = "SELECT book_ID, title, author,ISBN,Publisher,Status FROM BOOKS WHERE title LIKE '%" + searchTitle + "%'";
		}
		ResultSet result = null;
		try {
			//complete this
			Class.forName(JDBC_DRIVER);
            connect = DriverManagerConnection(URL, USER, PASS);
            if (connect != null){
                system.out.println("... MySQL Server connected.");
                statement = connect.createStatement();
                result = statement.executeQuery(sql);
                if (result != null ){
                    while(result.next()){
                        book = new Book(result.getInt(1), result.getString(2), result.getString(3));
                        bookList.add(newBook);
                        rows = rows +1;
                    }
                }
			}

		fireTableChanged(null); // Tell the listeners a new table has arrived.
		} catch (ClassNotFoundException exp) {
			System.out.println(driver + " Not found");
		} catch (SQLException exp) {
			exp.printStackTrace();
		}
	}

	/**
	* return a book's details by executing a sql statement
	*/
	public Book getCurrentBook(int row) {
		Statement statement = null;
		String sql = null;
		ResultSet result = null;
		Book book = bookList.get(row);
		int bookID = book.getID();

		try {
			Class.forName(JDBC_DRIVER);
            connect = DriverManagerConnection(URL, USER, PASS);
            if (connect != null){
                system.out.println("... MySQL Server connected.");
                statement = connect.createStatement();
				sql = "SELECT book_ID, Title, Author, ISBN, Publisher, Status FROM BOOKS WHERE book_ID Like '%" + "%'";
                result = statement.executeQuery(sql);
                if (result != null ){
                    while(result.next()){
                        book = new Book(result.getInt(1), result.getString(2), result.getString(3), result.getString(4),result.getString(5),result.getString(6));
                        bookList.add(book);
                    }
                }
			}
		} catch (SQLException exp) {
			System.out.println("Error in : " + sql);
			exp.printStackTrace();
		} catch (ClassNotFoundException exp){
			exp.printStackTrace();
		}



		return book;
	}

	/**
	* Returns number of rows in table
	*/
	public int getRowCount() { return rows; }

	/**
	* Returns number of columns in table
	*/
	public int getColumnCount() { return cols; }

	/**
	* Returns the title of a particular column
	*/
	public String getColumnName(int col) {return columnName[col];}

	/**
	* This indicates to the table object whether the
	* specified cell is editable. by default the key column is
	* not editable. This could be changed to allow user to specify this..
	*/
	public boolean isCellEditable(int row, int col) {
		return false;
	}
}