package crud;

import java.util.*;
import java.sql.*;

// Java representation of one row in the SQL table "books".
//
// This class provides the CRUD methods on books:
// - create() creates a new book in the database and returns a Book to 
//   represent it
// - list() and getById(id) return all books or a single book specified by the
//   id from the database and return them as Book instances
// - save() updates the book's title and author in the database
// - delete() deletes the book from the database
public class Book {
    public int id;
    public String title;
    public String author;

    public Book(int id, String title, String author) {
        this.id = id;
        this.title = title;
        this.author = author;
    }

    @Override
    public String toString() {
        return "Book(id=" + id + ", title='" + title + "', author='" + author + "')";
    }

    public static Book create(Connection connection, String title, String author) throws SQLException {
        PreparedStatement pstmt = connection.prepareStatement("""
            INSERT INTO books(title, author)
              VALUES (?, ?)
              RETURNING id
        """);
        pstmt.setString(1, title);
        pstmt.setString(2, author);
        ResultSet rs = pstmt.executeQuery();
        rs.next();
        var id = rs.getInt("id");
        pstmt.close();
        return new Book(id, title, author);
    }

    public static List<Book> list(Connection connection) throws SQLException {
        Statement statement = connection.createStatement();
        ResultSet rs = statement.executeQuery("SELECT id, title, author FROM books ORDER BY id");
        List<Book> books = new ArrayList<>();
        while (rs.next()) {
            books.add(new Book(rs.getInt("id"), rs.getString("title"), rs.getString("author")));
        }
        statement.close();
        return books;
    }

    public static Book getById(Connection connection, int id) throws SQLException {
        PreparedStatement pstmt = connection.prepareStatement("""
            SELECT id, title, author
            FROM books
            WHERE id = ?
        """);
        pstmt.setInt(1, id);
        ResultSet rs = pstmt.executeQuery();
        if (!rs.next()) {
            return null;
        }

        Book book = new Book(rs.getInt("id"), rs.getString("title"), rs.getString("author"));

        pstmt.close();                                                              

        return book;
    }

    public void save(Connection connection) throws SQLException {
        PreparedStatement pstmt = connection.prepareStatement("""
            UPDATE books
            SET title = ?, author = ?
            WHERE id = ?
        """);
        pstmt.setString(1, this.title);
        pstmt.setString(2, this.author);
        pstmt.setInt(3, this.id);
        pstmt.executeUpdate();
        pstmt.close();
    }

    public void delete(Connection connection) throws SQLException {
        PreparedStatement pstmt = connection.prepareStatement("""
            DELETE FROM books
            WHERE id = ?
        """);
        pstmt.setInt(1, this.id);
        pstmt.executeUpdate();
        pstmt.close();
    }
}