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
	}
}