import javax.persistence.*;
import java.util.ArrayList;

/**
 * Created by zen on 11/17/14.
 */
public class PersistenceManager {

    private EntityManagerFactory emFactory;

    public PersistenceManager() {
        // "brecommender" was the value of the name attribute of the
        // persistence-unit element.
        emFactory = Persistence.createEntityManagerFactory("brecommender");
    }

    /**
     * ****************************
     * GENERAL BOOK OPERATIONS
     * ****************************
     */

    /**
     * Saves analyzed book data in db or merges if necessary
     * @param book
     */
    public void saveAnalyzedBook(AnalyzedBookModel book) {
        EntityManager em = null;
        try {
            em = emFactory.createEntityManager();
            em.getTransaction().begin();
            em.merge(book);
            em.getTransaction().commit();
        } finally {
            if (em.isOpen()) {
                em.close();
            }
        }
    }

    /**
     * Returns analyzed books
     * @return
     */
    public ArrayList<AnalyzedBookModel> getAnalyzedBooks() {
        EntityManager em = null;
        try {
            em = emFactory.createEntityManager();
            em.getTransaction().begin();
            Query query = em.createQuery("FROM AnalyzedBookModel");
            return (ArrayList<AnalyzedBookModel>)query.getResultList();
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            if (em.isOpen()) {
                em.close();
            }
        }
    }

    /**
     * Returns analyzed books but the one with specified title and author
     * @return
     */
    public ArrayList<AnalyzedBookModel> getAnalyzedBooksExcludeByTitleAndAuthor(String title, String author) {
        EntityManager em = null;
        try {
            em = emFactory.createEntityManager();
            em.getTransaction().begin();
            Query query = em.createQuery("FROM AnalyzedBookModel " +
                    "WHERE title != :givenTitle " +
                    "OR author != :givenAuthor");
            query.setParameter("givenTitle", title);
            query.setParameter("givenAuthor", author);
            return (ArrayList<AnalyzedBookModel>)query.getResultList();
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            if (em.isOpen()) {
                em.close();
            }
        }
    }

    /**
     * Finds analyzed book by given title and author
     * @param title
     * @param author
     * @return
     */
    public AnalyzedBookModel getAnalyzedBookByTitleAndAuthor(String title, String author) {
        EntityManager em = null;
        try {
            em = emFactory.createEntityManager();
            em.getTransaction().begin();
            Query query = em.createQuery("FROM AnalyzedBookModel " +
                    "WHERE analyzedBookModelId.title = :givenTitle " +
                    "AND analyzedBookModelId.author = :givenAuthor");
            query.setParameter("givenTitle", title);
            query.setParameter("givenAuthor", author);
            try {
                return (AnalyzedBookModel) query.getSingleResult();
            } catch (NoResultException e) {
                return null;
            }
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            if (em.isOpen()) {
                em.close();
            }
        }
    }

    /**
     * ****************************
     * GUTENBERG BOOK OPERATIONS
     * ****************************
     */

    /**
     * Save gutenberg catalogue instance (title and author)
     * @param gutenbergBookModel
     */
    public void saveGutenbergBook(GutenbergBookModel gutenbergBookModel) {
        EntityManager em = null;
        try {
            em = emFactory.createEntityManager();
            em.getTransaction().begin();
            em.persist(gutenbergBookModel);
            em.getTransaction().commit();
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            if (em.isOpen()) {
                em.close();
            }
        }
    }


    /**
     * Save gutenberg catalogue instance (title and author)
     * @param title
     * @param author
     */
    public GutenbergBookModel getGutenbergBook(String title, String author) {
        EntityManager em = null;
        try {
            em = emFactory.createEntityManager();
            em.getTransaction().begin();

            Query query = em.createQuery("FROM GutenbergBookModel " +
                    "WHERE title =:title " +
                    "AND author =:author");
            query.setParameter("title", title);
            query.setParameter("author", author);
            try {
                return (GutenbergBookModel) query.getSingleResult();
            } catch (NoResultException e) {
                return null;
            }
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            if (em.isOpen()) {
                em.close();
            }
        }
    }

    /**
     * ****************************
     * BX BOOKS DATASET OPERATIONS
     * ****************************
     */

    /**
     * Returns array with users' ids from ratings table
     * @return
     */
    public ArrayList<Integer> getBXUserIds() {
        EntityManager em = null;
        try {
            em = emFactory.createEntityManager();
            em.getTransaction().begin();
            Query query = em.createQuery("SELECT ratingModelId.userId " +
                    "FROM BXRatingModel");
            return (ArrayList<Integer>) query.getResultList();
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            if (em.isOpen()) {
                em.close();
            }
        }
    }

    /**
     * Returns array of ratings submitted by given user
     * @param userId
     * @return
     */
    public ArrayList<BXRatingModel> getBXRatingsByUser(int userId) {
        EntityManager em = null;
        try {
            em = emFactory.createEntityManager();
            em.getTransaction().begin();
            Query query = em.createQuery("FROM BXRatingModel " +
                    "WHERE ratingModelId.userId=:givenUserId");
            query.setParameter("givenUserId", userId);
            return (ArrayList<BXRatingModel>) query.getResultList();
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            if (em.isOpen()) {
                em.close();
            }
        }
    }

    /**
     * Returns BX book by isbn
     * @param isbn
     * @return
     */
    public BXBookModel getBXBookByIsbn(String isbn) {
        EntityManager em = null;
        try {
            em = emFactory.createEntityManager();
            em.getTransaction().begin();

            Query query = em.createQuery("FROM BXBookModel " +
                    "WHERE isbn = :givenIsbn");

            query.setParameter("givenIsbn", isbn);
            return (BXBookModel) query.getSingleResult();
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            if (em.isOpen()) {
                em.close();
            }
        }
    }


    /**
     * Returns given user's duplicate ratings. Duplicate ratings are defined here as ratings
     * for books that have same title and author (in this case usually edition is the difference,
     * but this information does not matter in current bachelor's thesis)
     * @param userId
     * @param title
     * @param author
     * @return
     */
    public ArrayList<BXRatingModel> getBXUserDuplicateRatings(int userId, String title, String author) {
        EntityManager em = null;
        try {
            em = emFactory.createEntityManager();
            em.getTransaction().begin();

            Query query = em.createQuery("FROM BXRatingModel " +
                            "WHERE ratingModelId.userId = :givenUserId " +
                            "AND ratingModelId.isbn IN ( " +
                            "SELECT isbn from BXBookModel " +
                            "WHERE title = :givenTitle " +
                            "AND author = :givenAuthor)");

            query.setParameter("givenUserId", userId);
            query.setParameter("givenTitle", title);
            query.setParameter("givenAuthor", author);
            return (ArrayList<BXRatingModel>) query.getResultList();
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            if (em.isOpen()) {
                em.close();
            }
        }
    }

    /**
     * Counts given user's duplicate ratings. Duplicate ratings are defined here as ratings
     * for books that have same title and author (in this case usually edition is the difference,
     * but this information does not matter in current bachelor's thesis)
     * @param userId
     * @param title
     * @param author
     * @return
     */
    public long countBXUserDuplicateRatings(int userId, String title, String author) {
        EntityManager em = null;
        try {
            em = emFactory.createEntityManager();
            em.getTransaction().begin();

            Query query = em.createQuery("SELECT COUNT(*) FROM BXRatingModel " +
                    "WHERE ratingModelId.userId = :givenUserId " +
                    "AND ratingModelId.isbn IN ( " +
                    "SELECT isbn from BXBookModel " +
                    "WHERE title = :givenTitle " +
                    "AND author = :givenAuthor)");

            query.setParameter("givenUserId", userId);
            query.setParameter("givenTitle", title);
            query.setParameter("givenAuthor", author);
            return (Long) query.getSingleResult();
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            if (em.isOpen()) {
                em.close();
            }
        }
    }

    /**
     * Returns rating by userId and isbn
     * @return
     */
    public BXRatingModel getBXRatingByUserIdAndIsbn(int userId, String isbn) {
        EntityManager em = null;
        try {
            System.out.println("EXECUTING SELECTION");
            em = emFactory.createEntityManager();
            em.getTransaction().begin();
            Query query = em.createQuery("FROM BXRatingModel " +
                    "WHERE ratingModelId.userId = :givenUserId " +
                    "AND ratingModelId.isbn = :givenIsbn");

            query.setParameter("givenUserId", userId);
            query.setParameter("givenIsbn", isbn);
            return (BXRatingModel) query.getSingleResult();
        } catch (Exception e) {
            e.printStackTrace();
            try {
                em.getTransaction().rollback();
            } catch (RuntimeException rte) {
                System.out.println("Can't roll back transaction");
            }
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            if (em.isOpen()) {
                em.close();
            }
        }
        return null;
    }

    /**
     * Deletes BX rating by user and isbn
     * @return number of deleted entities
     */
    public int deleteBXRatingByUserAndIsbn(int userId, String isbn) {
        EntityManager em = null;
        try {
            System.out.println("EXECUTING DELETION");
            em = emFactory.createEntityManager();
            em.getTransaction().begin();
            Query query = em.createQuery("DELETE BXRatingModel " +
                    "WHERE ratingModelId.userId = :givenUserId " +
                    "AND ratingModelId.isbn = :givenIsbn");
            query.setParameter("givenUserId", userId);
            query.setParameter("givenIsbn", isbn);
            int deletedRatings = query.executeUpdate();
            em.getTransaction().commit();
            return deletedRatings;
        } catch (Exception e) {
            e.printStackTrace();
            try {
                em.getTransaction().rollback();
            } catch (RuntimeException rte) {
                System.out.println("Can't roll back transaction");
            }
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            if (em.isOpen()) {
                em.close();
            }
        }
        return 0;
    }

    /**
     * Gets all BX (Book-Crossing) dataset ISBNs that are available in books table
     * @return
     */
    public ArrayList<String> getBXIsbns() {
        EntityManager em = null;
        try {
            em = emFactory.createEntityManager();
            em.getTransaction().begin();
            Query query = em.createQuery("SELECT isbn FROM BXBookModel");
            return (ArrayList<String>) query.getResultList();
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            if (em.isOpen()) {
                em.close();
            }
        }
    }

    /**
     * Gets all BX (Book-Crossing) dataset books
     * @return
     */
    public ArrayList<BXBookModel> getBXBooks() {
        EntityManager em = null;
        try {
            em = emFactory.createEntityManager();
            em.getTransaction().begin();
            Query query = em.createQuery("FROM BXBookModel");
            return (ArrayList<BXBookModel>) query.getResultList();
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            if (em.isOpen()) {
                em.close();
            }
        }
    }

    /**
     * Gets books that are with given title and author, but not given isbn
     * @param title
     * @param author
     * @param isbn
     * @return
     */
    public ArrayList<BXBookModel> getBXBooksByTitleAndAuthorExcludeIsbn(String title, String author, String isbn) {
        EntityManager em = null;
        try {
            em = emFactory.createEntityManager();
            em.getTransaction().begin();
            Query query = em.createQuery("FROM BXBookModel " +
                    "WHERE title = :givenTitle " +
                    "AND author = :givenAuthor " +
                    "AND isbn != :givenIsbn");
            query.setParameter("givenTitle", title);
            query.setParameter("givenAuthor", author);
            query.setParameter("givenIsbn", isbn);
            return (ArrayList<BXBookModel>) query.getResultList();
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            if (em.isOpen()) {
                em.close();
            }
        }
    }

    /**
     * Count how many books with same title and author besides current (isbn given) there are
     * @param title
     * @param author
     * @param isbn
     * @return
     */
    public long countBXBooksByTitleAndAuthorExcludeIsbn(String title, String author, String isbn) {
        EntityManager em = null;
        try {
            em = emFactory.createEntityManager();
            em.getTransaction().begin();
            Query query = em.createQuery("SELECT COUNT(*) FROM BXBookModel " +
                    "WHERE title = :givenTitle " +
                    "AND author = :givenAuthor " +
                    "AND isbn != :givenIsbn");
            query.setParameter("givenTitle", title);
            query.setParameter("givenAuthor", author);
            query.setParameter("givenIsbn", isbn);
            return (Long) query.getSingleResult();
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            if (em.isOpen()) {
                em.close();
            }
        }
    }

    /**
     * Sets ratings with given isbn new isbn
     * @param fromIsbn
     * @param toIsbn
     * @return
     */
    public long updateRatingsByIsbn(String fromIsbn, String toIsbn) {
        EntityManager em = null;
        try {
            em = emFactory.createEntityManager();
            em.getTransaction().begin();
            Query query = em.createQuery("UPDATE BXRatingModel " +
                    "SET isbn = :toIsbn " +
                    "WHERE isbn = :fromIsbn ");
            query.setParameter("toIsbn", toIsbn);
            query.setParameter("fromIsbn", fromIsbn);
            int updatedRatings = query.executeUpdate();
            em.getTransaction().commit();
            return updatedRatings;
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            if (em.isOpen()) {
                em.close();
            }
        }
    }

    /**
     * Deletes BX book by isbn
     * @param isbn
     * @return
     */
    public int deleteBXBookByIsbn(String isbn) {
        EntityManager em = null;
        try {
            em = emFactory.createEntityManager();
            em.getTransaction().begin();
            Query query = em.createQuery("DELETE BXBookModel " +
                    "WHERE isbn = :givenIsbn");
            query.setParameter("givenIsbn", isbn);
            int deletedRatings = query.executeUpdate();
            em.getTransaction().commit();
            return deletedRatings;
        } catch (Exception e) {
            e.printStackTrace();
            try {
                em.getTransaction().rollback();
            } catch (RuntimeException rte) {
                System.out.println("Can't roll back transaction");
            }
        } finally {
            if (em.isOpen()) {
                em.close();
            }
        }
        return 0;
    }

    /**
     * Gets BX (Book-Crossing) dataset book's ratings by isbn
     * @param ISBN
     * @return
     */
    public ArrayList<BXRatingModel> getBXRatingsByISBN(String ISBN) {
        EntityManager em = null;
        try {
            em = emFactory.createEntityManager();
            em.getTransaction().begin();
            Query query = em.createQuery("FROM BXRatingModel WHERE ISBN = :isbn_num");
            query.setParameter("isbn_num", ISBN);
            return (ArrayList<BXRatingModel>) query.getResultList();
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            if (em.isOpen()) {
                em.close();
            }
        }
    }

    public String getBXIsbnByTitleAndAuthor(String title, String author) {
        EntityManager em = null;
        try {
            em = emFactory.createEntityManager();
            em.getTransaction().begin();
            Query query = em.createQuery("SELECT isbn FROM BXBookModel " +
                    "WHERE title = :givenTitle " +
                    "AND author = :givenAuthor");
            query.setParameter("givenTitle", title);
            query.setParameter("givenAuthor", author);
            return (String)query.getSingleResult();
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            if (em.isOpen()) {
                em.close();
            }
        }
    }

    /**
     * Gets BX (Book-Crossing) dataset books' ISBNs that have ratings with common users with the book that is determined by ISBN
     * @param isbn
     * @return
     */
    public ArrayList<String> getBXCorrISBNs(String isbn) {
        EntityManager em = null;
        try {
            em = emFactory.createEntityManager();
            em.getTransaction().begin();
            Query query = em.createQuery("SELECT ratingModelId.isbn FROM BXRatingModel \n" +
                    "WHERE ratingModelId.userId IN (\n" +
                    "SELECT ratingModelId.userId FROM BXRatingModel \n" +
                    "WHERE ratingModelId.isbn = :isbn_num )");
            query.setParameter("isbn_num", isbn);
            return (ArrayList<String>)query.getResultList();
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            if (em.isOpen()) {
                em.close();
            }
        }
    }


    /**
     * ****************************
     * TRAINING EUCLIDEAN DISTANCES OPERATIONS
     * ****************************
     */

    /**
     * Counts number of euclidean distances
     * @return
     * TESTED
     */
    public long countEuclideanDistances() {
        EntityManager em = null;
        try {
            em = emFactory.createEntityManager();
            em.getTransaction().begin();
            Query query = em.createQuery("SELECT COUNT(*) " +
                    "FROM TrainingEuclideanDistanceModel");
            return (long) query.getSingleResult();
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            if (em.isOpen()) {
                em.close();
            }
        }
    }


    /**
     * Persists or overwrites if needed distance between two books (saved as ISBN numbers)
     * @param euclideanDistance
     */
    public void saveTrainingEuclideanDistance(TrainingEuclideanDistanceModel euclideanDistance) {
        EntityManager em = null;
        try {
            em = emFactory.createEntityManager();
            em.getTransaction().begin();
            em.merge(euclideanDistance);
            em.getTransaction().commit();
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            if (em.isOpen()) {
                em.close();
            }
        }
    }

    /**
     * Returns euclidean distance between specified by isbn books
     * @param ISBN1
     * @param ISBN2
     * @return
     */
    public TrainingEuclideanDistanceModel getTrainingEuclideanDistByIsbns(String ISBN1, String ISBN2) {
        EntityManager em = null;
        try {
            em = emFactory.createEntityManager();
            em.getTransaction().begin();
            Query query = em.createQuery("FROM TrainingEuclideanDistanceModel \n" +
                    "WHERE isbn1 = :isbn_num1 AND isbn2 = :isbn_num2");
            query.setParameter("isbn_num1", ISBN1);
            query.setParameter("isbn_num2", ISBN2);
            try {
                return (TrainingEuclideanDistanceModel) query.getSingleResult();
            } catch (NoResultException e) {
                return null;
            }
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            if (em.isOpen()) {
                em.close();
            }
        }
    }

    public ArrayList<TrainingEuclideanDistanceModel> getTrainingEuclideanDistances() {
        EntityManager em = null;
        try {
            em = emFactory.createEntityManager();
            em.getTransaction().begin();
            Query query = em.createQuery("FROM TrainingEuclideanDistanceModel " +
                    "ORDER BY distance DESC");
            return (ArrayList<TrainingEuclideanDistanceModel>)query.getResultList();
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            if (em.isOpen()) {
                em.close();
            }
        }
    }

    /**
     * ****************************
     * TESTING EUCLIDEAN DISTANCES OPERATIONS
     * ****************************
     */

    public ArrayList<TestingEuclideanDistanceModel> getTestingEuclideanDistances() {
        EntityManager em = null;
        try {
            em = emFactory.createEntityManager();
            em.getTransaction().begin();
            Query query = em.createQuery("FROM TestingEuclideanDistanceModel");
            return (ArrayList<TestingEuclideanDistanceModel>)query.getResultList();
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            if (em.isOpen()) {
                em.close();
            }
        }
    }

    /**
     * ****************************
     * TESTING RESULTS OPERATIONS
     * ****************************
     */

    /**
     * Inserts testing result into database
     * @param testingResultsModel
     */
    public void saveTestingResult(TestingResultsModel testingResultsModel) {
        EntityManager em = null;
        try {
            em = emFactory.createEntityManager();
            em.getTransaction().begin();
            em.merge(testingResultsModel);
            em.getTransaction().commit();
        } finally {
            if (em.isOpen()) {
                em.close();
            }
        }
    }

    /**
     * ****************************
     * PARAMETERS OPERATIONS
     * ****************************
     */

    /**
     * Inserts parameter into database
     * @param regressionParameter
     */
    public void saveParameter(RegressorParameterModel regressionParameter) {
        EntityManager em = null;
        try {
            em = emFactory.createEntityManager();
            em.getTransaction().begin();
            em.merge(regressionParameter);
            em.getTransaction().commit();
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            if (em.isOpen()) {
                em.close();
            }
        }
    }

    /**
     * Returns regression parameter by regressor's name
     * @param parameterName
     * @return
     */
    public RegressorParameterModel getParameterByName(String parameterName) {
        EntityManager em = null;
        try {
            em = emFactory.createEntityManager();
            em.getTransaction().begin();
            Query query = em.createQuery("FROM RegressorParameterModel " +
                    "WHERE regressor = :givenRegressor");
            query.setParameter("givenRegressor", parameterName);
            return (RegressorParameterModel)query.getSingleResult();
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            if (em.isOpen()) {
                em.close();
            }
        }
    }

    /**
     * Inserts books difference into database
     * @param bookDifferenceModel
     */
    public void saveBookDifference(BookDifferenceModel bookDifferenceModel) {
        EntityManager em = null;
        try {
            em = emFactory.createEntityManager();
            em.getTransaction().begin();
            em.merge(bookDifferenceModel);
            em.getTransaction().commit();
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            if (em.isOpen()) {
                em.close();
            }
        }
    }

    /**
     * Returns book difference model by given isbns, if doesn't exist, returns null
     * @param isbn1
     * @param isbn2
     */
    public BookDifferenceModel getBookDifferenceByIsbns(String isbn1, String isbn2) {
        EntityManager em = null;
        try {
            em = emFactory.createEntityManager();
            em.getTransaction().begin();
            Query query = em.createQuery("FROM BookDifferenceModel " +
                    "WHERE isbn1 = :givenIsbn1 " +
                    "AND isbn2 = :givenIsbn2");
            query.setParameter("givenIsbn1", isbn1);
            query.setParameter("givenIsbn2", isbn2);
            try {
                return (BookDifferenceModel)query.getSingleResult();
            } catch (NoResultException e) {
                return null;
            }
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            if (em.isOpen()) {
                em.close();
            }
        }
    }

    /**
     * Returns list of books that are repetitive, meaning they have duplicates with only
     * difference being publisher, and correspondingly, ISBN. This shouldn't matter in my
     * thesis context.
     * @return
     */
    public ArrayList<BXBookModel> getRepetitiveBooks() {
        EntityManager em = null;
        try {
            em = emFactory.createEntityManager();
            em.getTransaction().begin();

            Query query = em.createQuery("FROM BXBookModel \n" +
                    "GROUP BY title \n" +
                    "HAVING COUNT(title) > 1");
            return (ArrayList<BXBookModel>)query.getResultList();
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            if (em.isOpen()) {
                em.close();
            }
        }
    }

    public void close() {
        emFactory.close();
    }

}
