/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package model;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.sql.PreparedStatement;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * A mock implementation of the BooksDBInterface interface to demonstrate how to
 * use it together with the user interface.
 *
 * Your implementation should access a real database.
 *
 * @author [email protected]
 */
public class BooksDb implements BooksDbInterface {

    private final List<Book> books;
    private Connection con;

    public BooksDb() {
        books = new ArrayList();
        con = null;
    }

    @Override
    public boolean connect(String database, String user, String pwd) throws IOException, SQLException {
        String server = "jdbc:mysql://localhost:3306/"
                + database
                + "?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC&UseClientEnc=UTF8";

        con = DriverManager.getConnection(server, user, pwd);

        return true;
    }

    @Override
    public void disconnect() throws IOException, SQLException {
        // mock implementation
        con.close();
    }

    /**
     *
     * @param searchTitle
     * @return
     * @throws IOException
     * @throws SQLException
     * @throws InvalidISBNException
     */
    @Override
    public ArrayList<Book> searchBooksByTitle(String searchTitle)
            throws IOException, SQLException, InvalidISBNException  {
        PreparedStatement pstmt = null;
        ArrayList<Book> result = new ArrayList();
        try {
            String sql = "SELECT * FROM Book WHERE title LIKE ?";
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, "%" + searchTitle + "%");
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()) {
               String isbn = rs.getString("isbn");
               String title = rs.getString("title");
               Book temp;
               temp = new Book(isbn, title);
               temp.setYear(rs.getInt("year"));
               temp.setRating(rs.getInt("rating"));
               temp.setPublisher(rs.getString("publisher"));
               temp.setUsername(rs.getString("username"));
               getAuthorByISBN(temp, isbn);
               getGenreByISBN(temp, isbn);
               result.add(temp);
       }
        } finally {
            pstmt.close();
        }
       return result;
    }

    private void getAuthorByISBN(Book temp, String isbn) throws SQLException{
        PreparedStatement pstmt = null;
        try {
            String sql = "SELECT * FROM Author A, CreatedBy C, Book B WHERE A.creatorID = C.creatorID AND C.isbn = B.isbn AND C.isbn = ?";
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1,isbn);
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()) {
                temp.addAuthor(new Author(rs.getString("firstName") + " " + rs.getString("lastName"), rs.getDate("dob").toLocalDate(), rs.getInt("creatorID")));
            }
        } finally {
            pstmt.close();
        }
    }

    private void getGenreByISBN(Book temp, String isbn) throws SQLException{
        PreparedStatement pstmt = null;
        try {
           String sql = "SELECT * FROM Genre WHERE isbn = ?";
           pstmt = con.prepareStatement(sql);
           pstmt.setString(1,isbn);
           ResultSet rs = pstmt.executeQuery();
           while(rs.next()) {
               temp.addGenre(rs.getString("genre"));
           }
        } finally {
            pstmt.close();
        }
    }

    @Override
    public ArrayList<Book> searchBooksByAuthor(String searchAuthor)
            throws IOException, SQLException, InvalidISBNException  {
        PreparedStatement pstmt = null;
        ArrayList<Book> result = new ArrayList();
        try {
            String sql = "SELECT * FROM Book B, CreatedBy C, Author A "
                    + "WHERE A.creatorID = C.creatorID AND C.isbn = B.isbn AND UPPER(REPLACE(CONCAT_WS('',firstName,lastName), ' ', '')) LIKE ?";
              pstmt = con.prepareStatement(sql);
              pstmt.setString(1, "%" + searchAuthor + "%");
              ResultSet rs = pstmt.executeQuery();
             while(rs.next()) {
                 String isbn = rs.getString("isbn");
                 String title = rs.getString("title");
                 Book temp;
                 temp = new Book(isbn, title);
                 temp.setYear(rs.getInt("year"));
                 temp.setRating(rs.getInt("rating"));
                 temp.setPublisher(rs.getString("publisher"));
                 temp.setUsername(rs.getString("username"));
                 getAuthorByISBN(temp, isbn);
                 getGenreByISBN(temp, isbn);
                 result.add(temp);
             }
        } finally {
            pstmt.close();
        }
       return result;
        }

    @Override
    public ArrayList<Book> searchBooksByISBN(String searchISBN) throws IOException, SQLException, InvalidISBNException {
        ArrayList<Book> result = new ArrayList();
        PreparedStatement pstmt = null;
        try {
            String sql = "SELECT * FROM Book WHERE isbn LIKE ?";
              pstmt = con.prepareStatement(sql);
              pstmt.setString(1, "%" + searchISBN + "%");
              ResultSet rs = pstmt.executeQuery();
             while(rs.next()) {
                 String isbn = rs.getString("isbn");
                 String title = rs.getString("title");
                 Book temp;
                 temp = new Book(isbn, title);
                 temp.setYear(rs.getInt("year"));
                 temp.setRating(rs.getInt("rating"));
                 temp.setPublisher(rs.getString("publisher"));
                 temp.setUsername(rs.getString("username"));
                 getAuthorByISBN(temp, isbn);
                 getGenreByISBN(temp, isbn);
                 result.add(temp);
             }
        } finally {
            pstmt.close();
        }
       return result;
    }

    @Override
    public void insertNewBook(Book b) throws SQLException {
        PreparedStatement pstmt = null;
        try {
            String sql = "INSERT INTO Book VALUES(?,?,?,?,'0',?,CURDATE())";
            con.setAutoCommit(false);
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, b.getIsbn());
            pstmt.setString(2, b.getTitle());
            pstmt.setInt(3, b.getYear());
            pstmt.setString(4, b.getPublisher());
            pstmt.setString(5, b.getUsername());
            pstmt.executeUpdate();

            for(Author a : b.getAuthors()){
                if(a.getName() != null){
                    sql = "INSERT INTO Author VALUES(?,?,?,?)";
                    pstmt = con.prepareStatement(sql);
                    pstmt.setString(1, a.getName().split("\\s+")[0]);
                    pstmt.setString(2, a.getName().split("\\s+")[1]);
                    pstmt.setDate(3, java.sql.Date.valueOf(a.getDateOfBirth()));
                    pstmt.setInt(4, a.getCreatorID());
                    pstmt.executeUpdate();
                }

                sql = "INSERT INTO CreatedBy VALUES(?,?,?,CURDATE())";
                pstmt = con.prepareStatement(sql);
                pstmt.setString(2, b.getIsbn());
                pstmt.setInt(1, a.getCreatorID());
                pstmt.setString(3, b.getUsername());
                pstmt.executeUpdate();
            }

            for(String g : b.getGenres()){
                sql = "INSERT INTO Genre VALUES(?,?)";
                pstmt = con.prepareStatement(sql);
                pstmt.setString(1, b.getIsbn());
                pstmt.setString(2, g);
                pstmt.executeUpdate();
            }

            con.commit();
        } catch (SQLException ex) {
            con.rollback();
            throw ex;
        } finally {
            pstmt.close();
            con.setAutoCommit(true);
        }
    }

    @Override
    public void updateBookAuthor(Author a, String isbn, String username) throws SQLException{
        String sql = "";
        PreparedStatement pstmt = null;
        try {
            con.setAutoCommit(false);
            if(a.getName() != null){
                sql = "INSERT INTO Author VALUES(?,?,?,?)";
                pstmt = con.prepareStatement(sql);
                pstmt.setString(1, a.getName().split("\\s+")[0]);
                pstmt.setString(2, a.getName().split("\\s+")[1]);
                pstmt.setDate(3, java.sql.Date.valueOf(a.getDateOfBirth()));
                pstmt.setInt(4, a.getCreatorID());
                pstmt.executeUpdate();
            }

            sql = "INSERT INTO CreatedBy VALUES(?,?,?,CURDATE())";
            pstmt = con.prepareStatement(sql);
            pstmt.setString(2, isbn);
            pstmt.setInt(1, a.getCreatorID());
            pstmt.setString(3, username);
            pstmt.executeUpdate();
            con.commit();
        } catch (SQLException ex) {
            con.rollback();
            throw ex;
        } finally {
            pstmt.close();
            con.setAutoCommit(true);
        }
    }

    /**
     *
     * @param searchString
     * @param mode
     * @throws SQLException
     */
    @Override
    public void removeBooks(String searchString, SearchMode mode) throws SQLException{
        PreparedStatement pstmt = null;
        try {
            String sql = "DELETE FROM Book WHERE isbn = ?";
            con.setAutoCommit(false);
            pstmt = con.prepareStatement(sql);
            ArrayList<String> isbn = getIsbnFromSearch(searchString, mode);
            for(String s : isbn){
                pstmt.setString(1, s);
                pstmt.executeUpdate();
            }
            con.commit();
            } catch (SQLException ex) {
            con.rollback();
            throw ex;
        } finally {
            pstmt.close();
            con.setAutoCommit(true);
        }
    }

    @Override
    public boolean checkPermission(String username) throws SQLException, IOException{
        PreparedStatement pstmt = null;
        try {
            connect("LABB1", "root", "basket97");
            String user = "";
            String sql = "SELECT Insert_priv FROM mysql.user WHERE user = ?";
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, username);
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()){
                user = rs.getString("Insert_priv");
                System.out.println(user);
            }

            if(user.equals("N")){
                return false;
            }
            return true;
        } finally {
            pstmt.close();
        }
    }

    private ArrayList<String> getIsbnFromSearch(String searchString, SearchMode mode) throws SQLException{
        ArrayList<String> isbn = new ArrayList();
        PreparedStatement pstmt = null;
        try {
        switch(mode){
            case Title:
                        String sql = "SELECT isbn FROM Book WHERE title LIKE ?";
                        pstmt = con.prepareStatement(sql);
                        pstmt.setString(1, "%" + searchString + "%");
                        ResultSet rs = pstmt.executeQuery();
                        while(rs.next()) {
                           isbn.add(rs.getString("isbn"));
                        }
                break;
            case ISBN:
                        sql = "SELECT isbn FROM Book WHERE isbn LIKE ?";
                        pstmt = con.prepareStatement(sql);
                        pstmt.setString(1, "%" + searchString + "%");
                        rs = pstmt.executeQuery();
                        while(rs.next()) {
                               isbn.add(rs.getString("isbn"));
                        }
                break;
            case Author:
                        sql = "SELECT DISTINCT B.isbn FROM Book B, CreatedBy C, Author A "
              + "WHERE A.creatorID = C.creatorID AND C.isbn = B.isbn AND (A.firstName LIKE ? OR A.lastName LIKE ?)";
                        pstmt = con.prepareStatement(sql);
                        pstmt.setString(1, "%" + searchString + "%");
                        pstmt.setString(2, "%" + searchString + "%");
                        rs = pstmt.executeQuery();
                        while(rs.next()) {
                           isbn.add(rs.getString("isbn"));
                        }
                break;
        }
        } finally {
            pstmt.close();
        }
        return isbn;
    }

    @Override
    public void addRatingToBook(Book b) throws SQLException {
         String sql = null;
         PreparedStatement pstmt = null;
        try {
            con.setAutoCommit(false);
            sql = "UPDATE  Book SET rating = ? WHERE isbn = ?";
            pstmt = con.prepareStatement(sql);
            pstmt.setInt(1, b.getRating());
            pstmt.setString(2, b.getIsbn());
            pstmt.executeUpdate();
            con.commit();
        }  catch (SQLException ex) {
            con.rollback();
            throw ex;
        } finally {
            pstmt.close();
            con.setAutoCommit(true);
        }
    }

    @Override
    public void insertUserReview(String user, String isbn, String reviewText) throws SQLException{
        PreparedStatement pstmt = null;
        try {
            con.setAutoCommit(false);
            String sql = "INSERT INTO Reviews VALUES(?, ?, ?)";
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, user);
            pstmt.setString(2, isbn);
            pstmt.setString(3, reviewText);
            pstmt.executeUpdate();
            con.commit();
        } catch (SQLException ex) {
            con.rollback();
            throw ex;
        } finally {
            pstmt.close();
            con.setAutoCommit(true);
        }
    }

    @Override
    public String getUserReviews(String isbn) throws SQLException{
        String reviews = "";
        PreparedStatement pstmt = null;
        try {
            String sql = "SELECT * FROM Reviews WHERE isbn = ?";
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, isbn);
            ResultSet rs = pstmt.executeQuery();

            while(rs.next()) {
                 reviews = reviews.concat(rs.getString("username"));
                reviews = reviews.concat(": ");
                reviews = reviews.concat(rs.getString("review"));
                 reviews = reviews.concat("\n");
            }
        } finally {
            pstmt.close();
        }
        return reviews;

    }
}