/*
 *      Book Catalog - Catalog your book collection.
 *      
 *      Copyright (C) 2009 Joshua Walters
 *      URL: http://joshwalters.com
 *      
 *      This file is part of Book Catalog.
 *      
 *      Book Catalog is free software: you can redistribute it and/or modify
 *      it under the terms of the GNU General Public License as published by
 *      the Free Software Foundation, either version 3 of the License, or
 *      (at your option) any later version.
 *      
 *      Book Catalog is distributed in the hope that it will be useful,
 *      but WITHOUT ANY WARRANTY; without even the implied warranty of
 *      MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *      GNU General Public License for more details.
 *      
 *      You should have received a copy of the GNU General Public License
 *      along with Book Catalog.  If not, see <http://www.gnu.org/licenses/>.
 */

package com.joshwalters.bookcatalog.bookdatabase;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

/**
 * Manages the database of books.
 * 
 * @author Josh Walters
 */
public class BookDatabase {

	/** The SQL statement. */
	private Statement statement;
	/** The SQL result set. */
	private ResultSet resultSet;
	/** The connection to the SQL database. */
	private Connection connection;

	/**
	 * Automatically disconnect from the database.
	 */
	protected void finalize() {
		disconnectFromDatabase();
	}

	/**
	 * Connects to the database. If the table for the record of books does not
	 * exist yet, then it will be created.
	 * 
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public void connectToDatabase() throws SQLException, ClassNotFoundException {
		try {
			// Connect to the database.
			Class.forName("org.sqlite.JDBC");
			// Create the database file.
			connection = DriverManager
					.getConnection("jdbc:sqlite:bookcatalog.db");
			statement = connection.createStatement();
		} catch (SQLException e) {
			throw e;
		} catch (ClassNotFoundException e) {
			throw e;
		}
		// Set the timeout to unlimited.
		statement.setQueryTimeout(0);
		// Create the table if it does not exist.
		statement.executeUpdate("create table if not exists bookcatalog("
				+ "Title text, Author text, Date text,"
				+ "Description text, ISBN text, Price text,"
				+ "Publisher text, Subject text, Notes text)");
	}

	/**
	 * Check to see if a book is in the database.
	 * 
	 * @param ISBN
	 * @return True if book is in database, false if not.
	 * @throws SQLException
	 */
	public boolean isBookInDatabase(String ISBN) throws SQLException {
		// The SQL statement.
		PreparedStatement preparedStatement = connection
				.prepareStatement("select * from bookcatalog where ISBN = ?");
		preparedStatement.setString(1, ISBN);
		resultSet = preparedStatement.executeQuery();
		boolean doesBookExist = resultSet.next();
		resultSet.close();
		return doesBookExist;
	}

	/**
	 * Check if a book in the database has a price assigned to it.
	 * 
	 * @param ISBN
	 * @return True if the book has price associated with it. False if not.
	 * @throws SQLException
	 */
	boolean bookHasPrice(String ISBN) throws SQLException {
		// The SQL statement
		PreparedStatement preparedStatement = connection
				.prepareStatement("select Price from bookcatalog where ISBN = ?");
		preparedStatement.setString(1, ISBN);
		resultSet = preparedStatement.executeQuery();
		resultSet.next();
		// Get the price value
		String bookPriceInCatalog = resultSet.getString(1);
		resultSet.close();
		if (bookPriceInCatalog == null) {
			return false;
		} else {
			return true;
		}
	}

	/**
	 * Sets the price for a book.
	 * 
	 * @param ISBN
	 * @param Price
	 * @throws SQLException
	 */
	void setBookPrice(String ISBN, String Price) throws SQLException {
		// The SQL statement
		PreparedStatement preparedStatement = connection
				.prepareStatement("update bookcatalog set Price = ? where ISBN = ?");
		preparedStatement.setString(1, Price);
		preparedStatement.setString(2, ISBN);
		if (preparedStatement.execute()) {
			resultSet = statement.getResultSet();
			if (resultSet != null) {
				resultSet.close();
			}
		}
	}

	/**
	 * Delete a book from the database.
	 * 
	 * @param ISBN
	 *            Used to identify the book to delete.
	 * @throws SQLException
	 */
	public void deleteBook(String ISBN) throws SQLException {
		// The SQL statement
		PreparedStatement preparedStatement = connection
				.prepareStatement("delete from bookcatalog where ISBN = ?");
		preparedStatement.setString(1, ISBN);
		if (preparedStatement.execute()) {
			resultSet = statement.getResultSet();
			if (resultSet != null) {
				resultSet.close();
			}
		}
	}

	/**
	 * Updates a book entry with new information.
	 * 
	 * @param Title
	 * @param Author
	 * @param Date
	 * @param Description
	 * @param ISBN
	 * @param Price
	 * @param Publisher
	 * @param Subject
	 * @param Notes
	 * @throws SQLException
	 */
	public void updateBook(String Title, String Author, String Date,
			String Description, String ISBN, String Price, String Publisher,
			String Subject, String Notes) throws SQLException {
		// The SQL statement
		PreparedStatement preparedStatement = connection
				.prepareStatement("update bookcatalog set Title = ?, Author = ?, Date = ?, Description = ?, Price = ?, Publisher = ?, Subject = ?, Notes = ? where ISBN = ?");
		preparedStatement.setString(1, Title);
		preparedStatement.setString(2, Author);
		preparedStatement.setString(3, Date);
		preparedStatement.setString(4, Description);
		preparedStatement.setString(5, Price);
		preparedStatement.setString(6, Publisher);
		preparedStatement.setString(7, Subject);
		preparedStatement.setString(8, Notes);
		preparedStatement.setString(9, ISBN);
		if (preparedStatement.execute()) {
			resultSet = statement.getResultSet();
			if (resultSet != null) {
				resultSet.close();
			}
		}
	}

	/**
	 * Inserts a book into the database.
	 * 
	 * @param Title
	 * @param Author
	 * @param Date
	 * @param Description
	 * @param ISBN
	 * @param Price
	 * @param Publisher
	 * @param Subject
	 * @throws SQLException
	 * @throws BookAlreadyInDatabase
	 */
	public void insertBook(String Title, String Author, String Date,
			String Description, String ISBN, String Price, String Publisher,
			String Subject) throws SQLException, BookAlreadyInDatabase {
		// Check to see if the book is in the database
		if (!isBookInDatabase(ISBN)) {
			// The SQL statement
			PreparedStatement preparedStatement = connection
					.prepareStatement("insert into bookcatalog values(?,?,?,?,?,?,?,?,?);");
			// Set the values for the prepared statement
			preparedStatement.setString(1, Title);
			preparedStatement.setString(2, Author);
			preparedStatement.setString(3, Date);
			preparedStatement.setString(4, Description);
			preparedStatement.setString(5, ISBN);
			preparedStatement.setString(6, Price);
			preparedStatement.setString(7, Publisher);
			preparedStatement.setString(8, Subject);
			// Set the notes for the book blank by default.
			preparedStatement.setString(9, "");
			if (preparedStatement.execute()) {
				resultSet = statement.getResultSet();
				if (resultSet != null) {
					resultSet.close();
				}
			}
		} else {
			// If the book exists in the database check to see if it has a price
			// assigned to it
			if (!bookHasPrice(ISBN) && Price != null) {
				// If the book doesn't have a price associated with it, give it
				// one.
				setBookPrice(ISBN, Price);
			} else {
				// The book is already in the database and we don't have/need
				// the price.
				throw new BookAlreadyInDatabase();
			}
		}
	}

	/**
	 * Get the database in a multidimensional string for use with a Swing table.
	 * 
	 * @return The database in a multidimensional string.
	 * @throws SQLException
	 * @throws EmptyDatabase
	 */
	public String[][] getDatabaseForTable() throws SQLException, EmptyDatabase {
		// Get the number of books in the database.
		resultSet = statement
				.executeQuery("select count(ISBN) from bookcatalog");
		if (resultSet.next()) {
			int rows = resultSet.getInt(1);
			resultSet.close();
			// Select all the data in the database.
			resultSet = statement.executeQuery("select * from bookcatalog");
			// Create a new multidimensional string that will store the
			// database.
			String data[][] = new String[rows][9];
			resultSet.next();
			// Iterate over the database and store the data in the string.
			for (int i = 0; i < rows; i++) {
				for (int j = 1; j <= 9; j++) {
					data[i][j - 1] = resultSet.getString(j);
				}
				resultSet.next();
			}
			resultSet.close();
			// Return the database in string format.
			return data;
		}
		throw new EmptyDatabase();
	}

	/**
	 * This gets the database for a Swing table, but limits the results to
	 * entries that match the search string.
	 * 
	 * @param searchString
	 * @return
	 * @throws SQLException
	 * @throws EmptyDatabase
	 */
	public String[][] searchDatabaseForTable(String searchString)
			throws SQLException, EmptyDatabase {
		String[][] data = null;
		Vector<String> cells = new Vector<String>();
		// Remove all percent signs from the string.
		searchString = searchString.replace("\\%", "");
		// Add percent signs at beginning and end of string.
		searchString = "%" + searchString + "%";
		// Replace space characters with percent signs.
		searchString = searchString.replace(" ", "%");
		/*
		 * Look for entries in the database that are similar to the search
		 * string.
		 */
		PreparedStatement preparedStatement = connection
				.prepareStatement("select * from bookcatalog where Title like ? or Author like ? or Date like ? or Description like ? or ISBN like ? or Price like ? or Publisher like ? or Subject like ? or Notes like ?");
		preparedStatement.setString(1, searchString);
		preparedStatement.setString(2, searchString);
		preparedStatement.setString(3, searchString);
		preparedStatement.setString(4, searchString);
		preparedStatement.setString(5, searchString);
		preparedStatement.setString(6, searchString);
		preparedStatement.setString(7, searchString);
		preparedStatement.setString(8, searchString);
		preparedStatement.setString(9, searchString);
		resultSet = preparedStatement.executeQuery();
		// Store the result data in a vector.
		while (resultSet.next()) {
			for (int i = 1; i <= 9; i++) {
				cells.add(resultSet.getString(i));
			}
		}
		// Create a new multidimensional string to store the database search
		// result.
		data = new String[cells.size() / 9][9];
		// Store the results in the string.
		for (int i = 0; i < cells.size() / 9; i++) {
			for (int j = 0; j < 9; j++) {
				data[i][j] = (String) cells.get((i * 9) + j);
			}
		}
		// Return the data in string format.
		return data;
	}

	/**
	 * Disconnect from the database. This will be called upon GC, but is best to
	 * call it manually.
	 * 
	 * @throws SQLException
	 */
	public void disconnectFromDatabase() {
		try {
			if (connection.isClosed() != true) {
				connection.close();
			}
		} catch (SQLException e) {
			// Do nothing
		}
	}
}