BookDAO.java

package dao;

import database.DatabaseConnection;
import models.Book;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * BookDAO.java - Data Access Object for Book entity
 * 
 * Design Pattern: DAO (Data Access Object)
 * Purpose: Encapsulates all database operations for books
 * Benefits:
 * - Separates business logic from database access
 * - Easy to test (can mock this class)
 * - Database changes don't affect UI code
 * 
 * Methods: CRUD operations + custom queries
 */
public class BookDAO {
    
    private static final Logger logger = Logger.getLogger(BookDAO.class.getName());
    
    // SQL Queries
    private static final String INSERT_BOOK = 
        "INSERT INTO books (title, author, isbn, publisher, publication_year, " +
        "total_copies, available_copies, category, price) " +
        "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
    
    private static final String SELECT_BY_ID = 
        "SELECT * FROM books WHERE book_id = ?";
    
    private static final String SELECT_ALL = 
        "SELECT * FROM books ORDER BY title ASC";
    
    private static final String UPDATE_BOOK = 
        "UPDATE books SET title = ?, author = ?, isbn = ?, publisher = ?, " +
        "publication_year = ?, total_copies = ?, available_copies = ?, " +
        "category = ?, price = ? WHERE book_id = ?";
    
    private static final String DELETE_BOOK = 
        "DELETE FROM books WHERE book_id = ?";
    
    private static final String SELECT_BY_ISBN = 
        "SELECT * FROM books WHERE isbn = ?";
    
    private static final String SELECT_BY_TITLE = 
        "SELECT * FROM books WHERE title LIKE ? ORDER BY title ASC";
    
    private static final String UPDATE_COPIES = 
        "UPDATE books SET available_copies = ? WHERE book_id = ?";
    
    private static final String GET_COPIES_COUNT = 
        "SELECT total_copies, available_copies FROM books WHERE book_id = ?";
    
    // ===== CREATE Operations =====
    
    /**
     * Adds a new book to the database
     * 
     * @param book Book object with details to insert
     * @return true if insertion successful
     * @throws Exception if book already exists or database error
     */
    public boolean addBook(Book book) throws Exception {
        // Validate book
        if (book == null) {
            throw new IllegalArgumentException("Book cannot be null");
        }
        
        // Check if ISBN already exists
        if (getBookByISBN(book.getIsbn()) != null) {
            throw new SQLException("Book with ISBN " + book.getIsbn() + " already exists");
        }
        
        try (Connection conn = DatabaseConnection.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(INSERT_BOOK)) {
            
            // Set parameters (indices start at 1 in JDBC)
            pstmt.setString(1, book.getTitle());
            pstmt.setString(2, book.getAuthor());
            pstmt.setString(3, book.getIsbn());
            pstmt.setString(4, book.getPublisher());
            pstmt.setInt(5, book.getPublicationYear());
            pstmt.setInt(6, book.getTotalCopies());
            pstmt.setInt(7, book.getAvailableCopies());
            pstmt.setString(8, book.getCategory());
            pstmt.setDouble(9, book.getPrice());
            
            int affectedRows = pstmt.executeUpdate();
            if (affectedRows > 0) {
                logger.info("✓ Book added: " + book.getTitle());
                return true;
            }
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "Error adding book", e);
            throw e;
        }
        return false;
    }
    
    // ===== READ Operations =====
    
    /**
     * Retrieves a book by its ID
     * 
     * @param bookId Book ID to search
     * @return Book object if found, null otherwise
     */
    public Book getBookById(int bookId) {
        try (Connection conn = DatabaseConnection.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(SELECT_BY_ID)) {
            
            pstmt.setInt(1, bookId);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return mapResultSetToBook(rs);
                }
            }
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "Error fetching book by ID", e);
        }
        return null;
    }
    
    /**
     * Retrieves a book by ISBN
     * 
     * @param isbn ISBN to search
     * @return Book object if found, null otherwise
     */
    public Book getBookByISBN(String isbn) {
        try (Connection conn = DatabaseConnection.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(SELECT_BY_ISBN)) {
            
            pstmt.setString(1, isbn);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return mapResultSetToBook(rs);
                }
            }
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "Error fetching book by ISBN", e);
        }
        return null;
    }
    
    /**
     * Retrieves all books from database
     * 
     * @return List of all books
     */
    public List<Book> getAllBooks() {
        List<Book> books = new ArrayList<>();
        try (Connection conn = DatabaseConnection.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(SELECT_ALL);
             ResultSet rs = pstmt.executeQuery()) {
            
            while (rs.next()) {
                books.add(mapResultSetToBook(rs));
            }
            logger.info("✓ Retrieved " + books.size() + " books from database");
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "Error fetching all books", e);
        }
        return books;
    }
    
    /**
     * Searches books by title (partial match)
     * 
     * @param titleKeyword Keyword to search in title
     * @return List of matching books
     */
    public List<Book> searchBooksByTitle(String titleKeyword) {
        List<Book> books = new ArrayList<>();
        try (Connection conn = DatabaseConnection.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(SELECT_BY_TITLE)) {
            
            // Use LIKE with wildcards for partial matching
            pstmt.setString(1, "%" + titleKeyword + "%");
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    books.add(mapResultSetToBook(rs));
                }
            }
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "Error searching books", e);
        }
        return books;
    }
    
    // ===== UPDATE Operations =====
    
    /**
     * Updates book details
     * 
     * @param book Book object with updated details
     * @return true if update successful
     */
    public boolean updateBook(Book book) {
        if (book == null || book.getBookId() <= 0) {
            throw new IllegalArgumentException("Invalid book data");
        }
        
        try (Connection conn = DatabaseConnection.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(UPDATE_BOOK)) {
            
            pstmt.setString(1, book.getTitle());
            pstmt.setString(2, book.getAuthor());
            pstmt.setString(3, book.getIsbn());
            pstmt.setString(4, book.getPublisher());
            pstmt.setInt(5, book.getPublicationYear());
            pstmt.setInt(6, book.getTotalCopies());
            pstmt.setInt(7, book.getAvailableCopies());
            pstmt.setString(8, book.getCategory());
            pstmt.setDouble(9, book.getPrice());
            pstmt.setInt(10, book.getBookId());
            
            int affectedRows = pstmt.executeUpdate();
            if (affectedRows > 0) {
                logger.info("✓ Book updated: " + book.getTitle());
                return true;
            }
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "Error updating book", e);
        }
        return false;
    }
    
    /**
     * Updates available copies when book is issued/returned
     * 
     * @param bookId Book ID to update
     * @param newAvailableCount New available count
     * @return true if update successful
     */
    public boolean updateAvailableCopies(int bookId, int newAvailableCount) {
        try (Connection conn = DatabaseConnection.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(UPDATE_COPIES)) {
            
            pstmt.setInt(1, newAvailableCount);
            pstmt.setInt(2, bookId);
            
            int affectedRows = pstmt.executeUpdate();
            return affectedRows > 0;
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "Error updating available copies", e);
        }
        return false;
    }
    
    // ===== DELETE Operations =====
    
    /**
     * Deletes a book from database
     * Note: Use with caution - may violate referential integrity
     * 
     * @param bookId Book ID to delete
     * @return true if deletion successful
     */
    public boolean deleteBook(int bookId) {
        try (Connection conn = DatabaseConnection.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(DELETE_BOOK)) {
            
            pstmt.setInt(1, bookId);
            int affectedRows = pstmt.executeUpdate();
            
            if (affectedRows > 0) {
                logger.info("✓ Book deleted: ID " + bookId);
                return true;
            }
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "Error deleting book", e);
        }
        return false;
    }
    
    // ===== Helper Methods =====
    
    /**
     * Maps a ResultSet row to a Book object
     * Prevents code duplication across query methods
     * 
     * @param rs ResultSet to map
     * @return Book object populated with data
     */
    private Book mapResultSetToBook(ResultSet rs) throws SQLException {
        return new Book(
            rs.getInt("book_id"),
            rs.getString("title"),
            rs.getString("author"),
            rs.getString("isbn"),
            rs.getString("publisher"),
            rs.getInt("publication_year"),
            rs.getInt("total_copies"),
            rs.getInt("available_copies"),
            rs.getString("category"),
            rs.getDouble("price")
        );
    }
    
    /**
     * Checks if book with given ID exists
     * 
     * @param bookId Book ID to check
     * @return true if book exists
     */
    public boolean bookExists(int bookId) {
        return getBookById(bookId) != null;
    }
    
    /**
     * Gets available books count
     * 
     * @return Number of books with available copies
     */
    public int getAvailableBooksCount() {
        int count = 0;
        String sql = "SELECT COUNT(*) as count FROM books WHERE available_copies > 0";
        
        try (Connection conn = DatabaseConnection.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            
            if (rs.next()) {
                count = rs.getInt("count");
            }
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "Error counting available books", e);
        }
        return count;
    }
}