package library.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;

import library.db.DB;
import library.domain.Book;

/**
 * All SQL for BOOKS .
 */
public class BookDao {

    private static final String COLUMNS =
            "book_id, isbn, title, author, published_on, replacement_cost, available";

    // GET /books
    public List<Book> findAll() throws SQLException {
        String sql = "SELECT " + COLUMNS + " FROM BOOKS ORDER BY title";
        List<Book> books = new ArrayList<>();
        try (Connection conn = DB.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                books.add(map(rs));
            }
        }
        return books;
    }

    // GET /books?author=...
    public List<Book> findByAuthor(String author) throws SQLException {
        String sql = "SELECT " + COLUMNS + " FROM BOOKS WHERE author = ? ORDER BY title";
        List<Book> books = new ArrayList<>();
        try (Connection conn = DB.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, author);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    books.add(map(rs));
                }
            }
        }
        return books;
    }

    // GET /books/{id}
    public Optional<Book> findById(int bookId) throws SQLException {
        String sql = "SELECT " + COLUMNS + " FROM BOOKS WHERE book_id = ?";
        try (Connection conn = DB.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, bookId);
            try (ResultSet rs = ps.executeQuery()) {
                return rs.next() ? Optional.of(map(rs)) : Optional.empty();
            }
        }
    }

    // POST /books → returns the new book_id.
    // available is left out of the INSERT so the column default (TRUE) applies.
    public int insert(Book b) throws SQLException {
        String sql = "INSERT INTO BOOKS (isbn, title, author, published_on, replacement_cost) "
                   + "VALUES (?, ?, ?, ?, ?)";
        try (Connection conn = DB.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            ps.setString(1, b.isbn());
            ps.setString(2, b.title());
            ps.setString(3, b.author());
            ps.setObject(4, b.publishedOn());          // null → SQL NULL
            ps.setBigDecimal(5, b.replacementCost());  // null → SQL NULL
            ps.executeUpdate();
            try (ResultSet keys = ps.getGeneratedKeys()) {
                keys.next();
                return keys.getInt(1);
            }
        }
    }

    // PUT /books/{id} → false if no such book.
    // Leaves available alone: only checkout and return should change it.
    public boolean update(Book b) throws SQLException {
        String sql = "UPDATE BOOKS SET isbn = ?, title = ?, author = ?, published_on = ?, "
                   + "replacement_cost = ? WHERE book_id = ?";
        try (Connection conn = DB.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, b.isbn());
            ps.setString(2, b.title());
            ps.setString(3, b.author());
            ps.setObject(4, b.publishedOn());
            ps.setBigDecimal(5, b.replacementCost());
            ps.setInt(6, b.bookId());
            return ps.executeUpdate() == 1;
        }
    }

    // Part of checkout and return → false if no such book
    public boolean setAvailable(int bookId, boolean available) throws SQLException {
        String sql = "UPDATE BOOKS SET available = ? WHERE book_id = ?";
        try (Connection conn = DB.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setBoolean(1, available);
            ps.setInt(2, bookId);
            return ps.executeUpdate() == 1;
        }
    }

    // DELETE /books/{id} → false if no such book.
    // A book with loan history fails with SQLException 1451 (LOANS.book_id is ON DELETE RESTRICT).
    public boolean delete(int bookId) throws SQLException {
        String sql = "DELETE FROM BOOKS WHERE book_id = ?";
        try (Connection conn = DB.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, bookId);
            return ps.executeUpdate() == 1;
        }
    }

    private static Book map(ResultSet rs) throws SQLException {
        return new Book(
                rs.getInt("book_id"),                                    // NOT NULL: a primitive getter is safe
                rs.getString("isbn"),
                rs.getString("title"),
                rs.getString("author"),
                rs.getObject("published_on", LocalDate.class),           // null for SQL NULL
                rs.getBigDecimal("replacement_cost"),                     // null for SQL NULL
                rs.getBoolean("available"));                             // NOT NULL
    }
}