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;
import library.errors.ConflictException;

/**
 * All SQL for BOOKS.
 *
 * No method declares throws SQLException any more: every one ends with the same
 * catch, which translates the error. Where a method knows what a particular
 * error means (a duplicate ISBN on insert), it says so first.
 */
public class BookDao {

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

	// GET /books
	public List<Book> findAll() {
		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));
			}
		} catch (SQLException e) {
			throw SqlErrors.translate(e);
		}
		return books;
	}

	// GET /books?author=...
	public List<Book> findByAuthor(String author) {
		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));
				}
			}
		} catch (SQLException e) {
			throw SqlErrors.translate(e);
		}
		return books;
	}

	// GET /books/{id}: empty if there's no such book (absence is normal here;
	// the service decides whether it's an error)
	public Optional<Book> findById(int bookId) {
		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()) {
				if (rs.next()) {
					return Optional.of(map(rs));
				}
				return Optional.empty();
			}
		} catch (SQLException e) {
			throw SqlErrors.translate(e);
		}
	}

	// 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) {
		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);
			}
		} catch (SQLException e) {
			if (e.getErrorCode() == 1062) {
				throw new ConflictException("A book with ISBN " + b.isbn() + " already exists", e);
			}
			throw SqlErrors.translate(e);
		}
	}

	// PUT /books/{id} → false if no such book.
	// Leaves available alone: only checkout and return should change it.
	public boolean update(Book b) {
		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;
		} catch (SQLException e) {
			if (e.getErrorCode() == 1062) {
				throw new ConflictException("A book with ISBN " + b.isbn() + " already exists", e);
			}
			throw SqlErrors.translate(e);
		}
	}

	// Part of checkout and return → false if no such book
	public boolean setAvailable(int bookId, boolean available) {
		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;
		} catch (SQLException e) {
			throw SqlErrors.translate(e);
		}
	}

	// DELETE /books/{id} → false if no such book.
	// A book with loan history fails with error 1451 (LOANS.book_id is ON DELETE
	// RESTRICT).
	public boolean delete(int bookId) {
		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;
		} catch (SQLException e) {
			if (e.getErrorCode() == 1451) {
				throw new ConflictException("Book " + bookId + " has loan history and can't be deleted", e);
			}
			throw SqlErrors.translate(e);
		}
	}

	// Only called inside the try blocks above, whose catch handles its SQLException
	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
	}
}