//-----------
// MembersDAO
//-----------
public class MembersDAO {
	
	public static ValueResult<Set<MemberDTO>> getAll() {
		
		ValueResult<Set<MemberDTO>> result = new ValueResult<Set<MemberDTO>>();
		
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String query = "select id, name, email_address from member";
			stmt = TransactionManager.getConnection().prepareStatement(query);
			
			Set<MemberDTO> allMembers = new HashSet<MemberDTO>();

			rs = stmt.executeQuery();
			while (rs.next()) {
				int id = rs.getInt(1);
				String name = rs.getString(2);
				EmailAddress emailAddr = EmailAddress.create(rs.getString(3)).getValue();

				allMembers.add(new MemberDTO(id, name, emailAddr));
			}
			
			result.setValue(allMembers);
			result.setStatus(true);
		}
		catch (SQLException e) {
			result.setMessage(e.getMessage());
		}		
		finally {
			DbUtils.safeClose(rs);
			DbUtils.safeClose(stmt);
		}

		return result;		
	}
	
	public static Result add(MemberDTO member) {
		
		Result result = new Result();
		
		try {
			String query = "insert into member (name, email_address) values (?, ?)";
			PreparedStatement stmt = TransactionManager.getConnection().prepareStatement(query);
			stmt.setString(1, member.getName());
			stmt.setString(2, member.getEmailAddress().getValue());
			if (stmt.executeUpdate() == 1) {
				Statement keyStmt = TransactionManager.getConnection().createStatement();
				ResultSet keyRS = keyStmt.executeQuery("select last_insert_rowid()");
				try {
					keyRS.next();
					int id = keyRS.getInt(1);
					member.setID(id);
					result.setStatus(true);
				}
				finally {
					keyRS.close();
				}
			}
			else {
				result.setMessage("Could not insert member");
			}
		}
		catch (SQLException e) {
			result.setMessage(e.getMessage());
		}
		
		return result;	
	}
	
	public static Result update(MemberDTO member) {
		
		Result result = new Result();
		
		try {
			String query = "update member set name = ?, email_address = ? where id = ?";
			PreparedStatement stmt = TransactionManager.getConnection().prepareStatement(query);
			stmt.setString(1, member.getName());
			stmt.setString(2, member.getEmailAddress().getValue());
			stmt.setInt(3, member.getID());
			if (stmt.executeUpdate() == 1)
				result.setStatus(true);
			else
				result.setMessage("Could not update member");
		}
		catch (SQLException e) {
			result.setMessage(e.getMessage());
		}
		
		return result;	
	}
	
	public static Result delete(MemberDTO member) {
		
		Result result = new Result();
		
		try {
			String query = "delete from member where id = ?";
			PreparedStatement stmt = TransactionManager.getConnection().prepareStatement(query);
			stmt.setInt(1, member.getID());
			if (stmt.executeUpdate() == 1)
				result.setStatus(true);
			else
				result.setMessage("Could not delete member");
		}
		catch (SQLException e) {
			result.setMessage(e.getMessage());
		}
		
		return result;	
	}	
	
}


//---------
// BooksDAO
//---------
public class BooksDAO {
	
	public static ValueResult<Set<BookDTO>> getAll() {
		
		ValueResult<Set<BookDTO>> result = new ValueResult<Set<BookDTO>>();
		
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String query = "select id, title, author, genre from book";
			stmt = TransactionManager.getConnection().prepareStatement(query);
			
			Set<BookDTO> allBooks = new HashSet<BookDTO>();

			rs = stmt.executeQuery();
			while (rs.next()) {
				int id = rs.getInt(1);
				String title = rs.getString(2);
				String author = rs.getString(3);
				Genre genre = convertGenre(rs.getString(4));

				allBooks.add(new BookDTO(id, title, author, genre));
			}
			
			result.setValue(allBooks);
			result.setStatus(true);
		}
		catch (SQLException e) {
			result.setMessage(e.getMessage());
		}		
		finally {
			DbUtils.safeClose(rs);
			DbUtils.safeClose(stmt);
		}

		return result;		
	}
	
	public static Result add(BookDTO book) {
		
		Result result = new Result();
		
		try {
			String query = "insert into book (title, author, genre) values (?, ?, ?)";
			PreparedStatement stmt = TransactionManager.getConnection().prepareStatement(query);
			stmt.setString(1, book.getTitle());
			stmt.setString(2, book.getAuthor());
			stmt.setString(3, convertGenre(book.getGenre()));
			if (stmt.executeUpdate() == 1) {
				Statement keyStmt = TransactionManager.getConnection().createStatement();
				ResultSet keyRS = keyStmt.executeQuery("select last_insert_rowid()");
				try {
					keyRS.next();
					int id = keyRS.getInt(1);
					book.setID(id);
					result.setStatus(true);
				}
				finally {
					keyRS.close();
				}
			}
			else {
				result.setMessage("Could not insert book");
			}
		}
		catch (SQLException e) {
			result.setMessage(e.getMessage());
		}
		
		return result;	
	}
	
	public static Result update(BookDTO book) {
		
		Result result = new Result();
		
		try {
			String query = "update book set title = ?, author = ?, genre = ? where id = ?";
			PreparedStatement stmt = TransactionManager.getConnection().prepareStatement(query);
			stmt.setString(1, book.getTitle());
			stmt.setString(2, book.getAuthor());
			stmt.setString(3, convertGenre(book.getGenre()));
			stmt.setInt(4, book.getID());
			if (stmt.executeUpdate() == 1)
				result.setStatus(true);
			else
				result.setMessage("Could not update book");
		}
		catch (SQLException e) {
			result.setMessage(e.getMessage());
		}
		
		return result;	
	}
	
	public static Result delete(BookDTO book) {
		
		Result result = new Result();
		
		try {
			String query = "delete from book where id = ?";
			PreparedStatement stmt = TransactionManager.getConnection().prepareStatement(query);
			stmt.setInt(1, book.getID());
			if (stmt.executeUpdate() == 1)
				result.setStatus(true);
			else
				result.setMessage("Could not delete book");
		}
		catch (SQLException e) {
			result.setMessage(e.getMessage());
		}
		
		return result;	
	}
	
	private static Genre convertGenre(String genreStr) {
		return Genre.valueOf(genreStr);
	}
	
	private static String convertGenre(Genre genre) {
		return genre.toString();
	}
	
}


//------------
// ReadingsDAO
//------------
public class ReadingsDAO {

	public static ValueResult<Set<ReadingDTO>> getAll() {
		
		ValueResult<Set<ReadingDTO>> result = new ValueResult<Set<ReadingDTO>>();
		
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String query = "select member_id, book_id from reading";
			stmt = TransactionManager.getConnection().prepareStatement(query);
			
			Set<ReadingDTO> allReadings = new HashSet<ReadingDTO>();

			rs = stmt.executeQuery();
			while (rs.next()) {
				int member_id = rs.getInt(1);
				int book_id = rs.getInt(2);

				allReadings.add(new ReadingDTO(member_id, book_id));
			}
			
			result.setValue(allReadings);
			result.setStatus(true);
		}
		catch (SQLException e) {
			result.setMessage(e.getMessage());
		}		
		finally {
			DbUtils.safeClose(rs);
			DbUtils.safeClose(stmt);
		}

		return result;
	}
	
	public static Result add(ReadingDTO reading) {
		
		Result result = new Result();
		
		try {
			String query = "insert into reading (member_id, book_id) values (?, ?)";
			PreparedStatement stmt = TransactionManager.getConnection().prepareStatement(query);
			stmt.setInt(1, reading.getMember());
			stmt.setInt(2, reading.getBook());
			if (stmt.executeUpdate() == 1)
				result.setStatus(true);
			else
				result.setMessage("Could not insert reading");
		}
		catch (SQLException e) {
			result.setMessage(e.getMessage());
		}
		
		return result;
	}
	

	public static Result delete(ReadingDTO reading) {
		
		Result result = new Result();
		
		try {
			String query = "delete from reading where member_id = ? and book_id = ?";
			PreparedStatement stmt = TransactionManager.getConnection().prepareStatement(query);
			stmt.setInt(1, reading.getMember());
			stmt.setInt(2, reading.getBook());
			if (stmt.executeUpdate() == 1)
				result.setStatus(true);
			else
				result.setMessage("Could not delete reading");
		}
		catch (SQLException e) {
			result.setMessage(e.getMessage());
		}
		
		return result;
	}
	
	public static Result deleteForMember(MemberDTO member) {
		
		Result result = new Result();
		
		try {
			String query = "delete from reading where member_id = ?";
			PreparedStatement stmt = TransactionManager.getConnection().prepareStatement(query);
			stmt.setInt(1, member.getID());
			stmt.executeUpdate();
			result.setStatus(true);
		}
		catch (SQLException e) {
			result.setMessage(e.getMessage());
		}
		
		return result;
	}
	
	public static Result deleteForBook(BookDTO book) {
		
		Result result = new Result();
		
		try {
			String query = "delete from reading where book_id = ?";
			PreparedStatement stmt = TransactionManager.getConnection().prepareStatement(query);
			stmt.setInt(1, book.getID());
			stmt.executeUpdate();
			result.setStatus(true);
		}
		catch (SQLException e) {
			result.setMessage(e.getMessage());
		}
		
		return result;		
	}
	
}