IssuedBookDAO.java

package dao;

import database.DatabaseConnection;
import models.IssuedBook;

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

/**
 * IssuedBookDAO.java - Data Access Object for IssuedBook transactions
 * 
 * Handles:
 * - Recording when books are issued
 * - Recording when books are returned
 * - Querying transaction history
 * - Checking overdue books
 */
public class IssuedBookDAO {
    
    private static final Logger logger = Logger.getLogger(IssuedBookDAO.class.getName());
    
    // SQL Queries
    private static final String INSERT_ISSUE = 
        "INSERT INTO issued_books (book_id, issued_date, due_date, status, " +
        "member_name, member_contact) VALUES (?, ?, ?, ?, ?, ?)";
    
    private static final String SELECT_BY_ID = 
        "SELECT * FROM issued_books WHERE issue_id = ?";
    
    private static final String SELECT_ALL = 
        "SELECT * FROM issued_books ORDER BY issued_date DESC";
    
    private static final String SELECT_BY_BOOK_ID = 
        "SELECT * FROM issued_books WHERE book_id = ? AND status = 'ISSUED' LIMIT 1";
    
    private static final String SELECT_ACTIVE_ISSUES = 
        "SELECT * FROM issued_books WHERE status = 'ISSUED' ORDER BY issued_date ASC";
    
    private static final String SELECT_OVERDUE = 
        "SELECT * FROM issued_books WHERE status = 'ISSUED' AND due_date < CURDATE()";
    
    private static final String UPDATE_RETURN = 
        "UPDATE issued_books SET return_date = ?, status = 'RETURNED' WHERE issue_id = ?";
    
    private static final String SELECT_MEMBER_ISSUED = 
        "SELECT * FROM issued_books WHERE member_name = ? AND status = 'ISSUED'";
    
    // ===== CREATE Operations =====
    
    /**
     * Records a new book issue
     * 
     * @param issuedBook IssuedBook object with issue details
     * @return Issue ID if successful, -1 if failed
     */
    public int issueBook(IssuedBook issuedBook) throws Exception {
        if (issuedBook == null) {
            throw new IllegalArgumentException("IssuedBook cannot be null");
        }
        
        try (Connection conn = DatabaseConnection.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(INSERT_ISSUE, 
                 Statement.RETURN_GENERATED_KEYS)) {
            
            // Set parameters
            pstmt.setInt(1, issuedBook.getBookId());
            pstmt.setDate(2, java.sql.Date.valueOf(issuedBook.getIssuedDate()));
            pstmt.setDate(3, java.sql.Date.valueOf(issuedBook.getDueDate()));
            pstmt.setString(4, issuedBook.getStatus());
            pstmt.setString(5, issuedBook.getMemberName());
            pstmt.setString(6, issuedBook.getMemberContact());
            
            int affectedRows = pstmt.executeUpdate();
            if (affectedRows > 0) {
                // Retrieve auto-generated issue ID
                try (ResultSet rs = pstmt.getGeneratedKeys()) {
                    if (rs.next()) {
                        int issueId = rs.getInt(1);
                        logger.info("✓ Book issued. Issue ID: " + issueId);
                        return issueId;
                    }
                }
            }
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "Error issuing book", e);
            throw e;
        }
        return -1;
    }
    
    // ===== READ Operations =====
    
    /**
     * Retrieves an issue record by ID
     * 
     * @param issueId Issue ID to search
     * @return IssuedBook object if found, null otherwise
     */
    public IssuedBook getIssueById(int issueId) {
        try (Connection conn = DatabaseConnection.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(SELECT_BY_ID)) {
            
            pstmt.setInt(1, issueId);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return mapResultSetToIssuedBook(rs);
                }
            }
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "Error fetching issue by ID", e);
        }
        return null;
    }
    
    /**
     * Gets all issue records
     * 
     * @return List of all IssuedBook records
     */
    public List<IssuedBook> getAllIssues() {
        List<IssuedBook> issues = new ArrayList<>();
        try (Connection conn = DatabaseConnection.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(SELECT_ALL);
             ResultSet rs = pstmt.executeQuery()) {
            
            while (rs.next()) {
                issues.add(mapResultSetToIssuedBook(rs));
            }
            logger.info("✓ Retrieved " + issues.size() + " issue records");
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "Error fetching all issues", e);
        }
        return issues;
    }
    
    /**
     * Gets current active issues (not returned)
     * 
     * @return List of currently issued books
     */
    public List<IssuedBook> getActiveIssues() {
        List<IssuedBook> issues = new ArrayList<>();
        try (Connection conn = DatabaseConnection.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(SELECT_ACTIVE_ISSUES);
             ResultSet rs = pstmt.executeQuery()) {
            
            while (rs.next()) {
                issues.add(mapResultSetToIssuedBook(rs));
            }
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "Error fetching active issues", e);
        }
        return issues;
    }
    
    /**
     * Gets overdue books
     * 
     * @return List of overdue books not yet returned
     */
    public List<IssuedBook> getOverdueBooks() {
        List<IssuedBook> overdueBooks = new ArrayList<>();
        try (Connection conn = DatabaseConnection.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(SELECT_OVERDUE);
             ResultSet rs = pstmt.executeQuery()) {
            
            while (rs.next()) {
                overdueBooks.add(mapResultSetToIssuedBook(rs));
            }
            logger.info("✓ Found " + overdueBooks.size() + " overdue books");
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "Error fetching overdue books", e);
        }
        return overdueBooks;
    }
    
    /**
     * Gets the current issue record for a specific book
     * Assumes only one copy can be issued per transaction (modify if needed)
     * 
     * @param bookId Book ID to check
     * @return IssuedBook if currently issued, null otherwise
     */
    public IssuedBook getActiveIssueByBookId(int bookId) {
        try (Connection conn = DatabaseConnection.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(SELECT_BY_BOOK_ID)) {
            
            pstmt.setInt(1, bookId);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return mapResultSetToIssuedBook(rs);
                }
            }
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "Error fetching issue by book ID", e);
        }
        return null;
    }
    
    /**
     * Gets all currently issued books by a member
     * 
     * @param memberName Name of member
     * @return List of books issued to member
     */
    public List<IssuedBook> getIssuedByMember(String memberName) {
        List<IssuedBook> issues = new ArrayList<>();
        try (Connection conn = DatabaseConnection.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(SELECT_MEMBER_ISSUED)) {
            
            pstmt.setString(1, memberName);
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    issues.add(mapResultSetToIssuedBook(rs));
                }
            }
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "Error fetching member issues", e);
        }
        return issues;
    }
    
    // ===== UPDATE Operations =====
    
    /**
     * Records book return and updates issue status
     * 
     * @param issueId Issue ID to mark as returned
     * @return true if update successful
     */
    public boolean returnBook(int issueId) {
        try (Connection conn = DatabaseConnection.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(UPDATE_RETURN)) {
            
            pstmt.setDate(1, java.sql.Date.valueOf(LocalDate.now()));
            pstmt.setInt(2, issueId);
            
            int affectedRows = pstmt.executeUpdate();
            if (affectedRows > 0) {
                logger.info("✓ Book returned. Issue ID: " + issueId);
                return true;
            }
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "Error returning book", e);
        }
        return false;
    }
    
    // ===== Helper Methods =====
    
    /**
     * Maps ResultSet row to IssuedBook object
     * 
     * @param rs ResultSet to map
     * @return IssuedBook object populated with data
     */
    private IssuedBook mapResultSetToIssuedBook(ResultSet rs) throws SQLException {
        int issueId = rs.getInt("issue_id");
        int bookId = rs.getInt("book_id");
        LocalDate issuedDate = rs.getDate("issued_date").toLocalDate();
        LocalDate dueDate = rs.getDate("due_date").toLocalDate();
        
        Date returnDateSQL = rs.getDate("return_date");
        LocalDate returnDate = returnDateSQL != null ? returnDateSQL.toLocalDate() : null;
        
        String status = rs.getString("status");
        String memberName = rs.getString("member_name");
        String memberContact = rs.getString("member_contact");
        
        // Note: bookTitle is not stored in database for this query
        // Would need to join with books table if needed for display
        return new IssuedBook(issueId, bookId, "", issuedDate, dueDate, 
                            returnDate, status, memberName, memberContact);
    }
    
    /**
     * Gets statistics: total issued books
     * 
     * @return Count of currently issued books
     */
    public int getTotalIssuedCount() {
        String sql = "SELECT COUNT(*) as count FROM issued_books WHERE status = 'ISSUED'";
        
        try (Connection conn = DatabaseConnection.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            
            if (rs.next()) {
                return rs.getInt("count");
            }
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "Error counting issued books", e);
        }
        return 0;
    }
    
    /**
     * Gets total overdue books count
     * 
     * @return Count of overdue books
     */
    public int getOverdueBooksCount() {
        String sql = "SELECT COUNT(*) as count FROM issued_books " +
                    "WHERE status = 'ISSUED' AND due_date < CURDATE()";
        
        try (Connection conn = DatabaseConnection.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            
            if (rs.next()) {
                return rs.getInt("count");
            }
        } catch (SQLException e) {
            logger.log(Level.SEVERE, "Error counting overdue books", e);
        }
        return 0;
    }
}