package com.vladmihalcea.book.hpjp.hibernate.query.join.algorithm;

import com.vladmihalcea.book.hpjp.util.AbstractPostgreSQLIntegrationTest;
import org.hibernate.Session;
import org.junit.Test;

import javax.persistence.*;
import java.util.List;
import java.util.stream.Collectors;

import static org.junit.Assert.assertTrue;

/**
 * @author Georgeta Mihalcea
 */
public class NestedLoopsSqlTest extends AbstractPostgreSQLIntegrationTest {

    @Override
    protected Class<?>[] entities() {
        return new Class<?>[]{
                Post.class,
                PostComment.class,
        };
    }

    /**
     * post
     * ----
     * 
     * | id | title      |
     * |----|------------|
     * | 1  | Post no. 1 |
     * | 2  | Post no. 2 |
     * | .. | ..         |
     *
     * post_comment
     * -------------
     * 
     * | id | review        | post_id |
     * |----|---------------|---------|
     * | 1  | Comment no. 1 | 1       |
     * | 2  | Comment no. 2 | 1       |
     * | .. | ..            | ..      |
     */
    @Override
    public void afterInit() {
        doInJPA(entityManager -> {
            int postCount = 1000;
            int postCommentCount = 10;
            long postCommentId = 1;

            for (long postId = 1; postId <= postCount; postId++) {
                Post post = new Post()
                    .setId(postId)
                    .setTitle(String.format("Post no. %d", postId));
                entityManager.persist(post);

                for (int i = 0; i < postCommentCount; i++) {
                    entityManager.persist(
                        new PostComment()
                            .setId(postCommentId++)
                            .setReview(String.format("Comment no. %d", postCommentId))
                            .setPost(post)
                    );
                }
            }
        });
        executeStatement("CREATE INDEX IDX_post_id ON post (id)");
        executeStatement("CREATE INDEX IDX_post_comment_id ON post_comment (id)");
        executeStatement("CREATE INDEX IDX_post_comment_post_id ON post_comment (post_id)");
        executeStatement("VACUUM ANALYZE");
    }

    /**
     * Get all posts with their associated post_comments.
     *
     * EXPLAIN ANALYZE
     * SELECT
     *    p.id AS post_id,
     *    p.title AS post_title,
     *    pc.review  AS review
     * FROM post p
     * INNER JOIN post_comment pc ON pc.post_id = p.id
     * WHERE p.id BETWEEN 1 AND 10
     */
    @Test
    public void testInnerJoin() {
        doInJPA(entityManager -> {
            List<String> planLines = entityManager
            .unwrap(Session.class)
            .doReturningWork(connection -> selectColumnList(
                connection,
                "EXPLAIN ANALYZE\n" +
                "SELECT\n" +
                "   p.id AS post_id,\n" +
                "   p.title AS post_title,\n" +
                "   pc.review  AS review\n" +
                "FROM post p\n" +
                "INNER JOIN post_comment pc ON pc.post_id = p.id\n" +
                "WHERE p.id BETWEEN 1 AND 10",
                String.class)
            );

            assertTrue(planLines.size() > 1);

            LOGGER.info("Execution plan: {}{}",
                System.lineSeparator(),
                planLines.stream().collect(Collectors.joining(System.lineSeparator()))
            );
        });
    }

    @Entity(name = "Post")
    @Table(name = "post")
    public static class Post {

        @Id
        private Long id;

        private String title;

        public Long getId() {
            return id;
        }

        public Post setId(Long id) {
            this.id = id;
            return this;
        }

        public String getTitle() {
            return title;
        }

        public Post setTitle(String title) {
            this.title = title;
            return this;
        }
    }

    @Entity(name = "PostComment")
    @Table(name = "post_comment")
    public static class PostComment {

        @Id
        private Long id;

        @ManyToOne(fetch = FetchType.LAZY)
        private Post post;

        private String review;

        public Long getId() {
            return id;
        }

        public PostComment setId(Long id) {
            this.id = id;
            return this;
        }

        public Post getPost() {
            return post;
        }

        public PostComment setPost(Post post) {
            this.post = post;
            return this;
        }

        public String getReview() {
            return review;
        }

        public PostComment setReview(String review) {
            this.review = review;
            return this;
        }
    }
}

@@ -2761,16 +2761,35 @@
        %22
+%22%22%0A                
 EXPLAIN 
@@ -2795,21 +2795,16 @@
  ANALYZE
-%5Cn%22 +
 %0A       
@@ -2816,20 +2816,14 @@
     
-%22
 SELECT
-%5Cn%22 +
 %0A   
@@ -2831,25 +2831,24 @@
             
-%22
    p.id AS p
@@ -2854,21 +2854,16 @@
 post_id,
-%5Cn%22 +
 %0A       
@@ -2867,25 +2867,24 @@
             
-%22
    p.title A
@@ -2896,21 +2896,16 @@
 t_title,
-%5Cn%22 +
 %0A       
@@ -2913,17 +2913,16 @@
         
-%22
    pc.re
@@ -2936,21 +2936,16 @@
 S review
-%5Cn%22 +
 %0A       
@@ -2953,17 +2953,16 @@
         
-%22
 FROM pos
@@ -2964,21 +2964,16 @@
 M post p
-%5Cn%22 +
 %0A       
@@ -2981,17 +2981,16 @@
         
-%22
 INNER JO
@@ -3032,13 +3032,8 @@
 p.id
-%5Cn%22 +
 %0A   
@@ -3045,17 +3045,16 @@
         
-%22
 WHERE p.
@@ -3072,16 +3072,35 @@
 1 AND 10
+%0A                %22%22
 %22,%0A     

Update example