package com.vladmihalcea.book.hpjp.hibernate.audit.trigger;

import com.vladmihalcea.book.hpjp.util.AbstractTest;
import com.vladmihalcea.book.hpjp.util.providers.Database;
import org.hibernate.envers.AuditReaderFactory;
import org.hibernate.envers.Audited;
import org.hibernate.envers.query.AuditEntity;
import org.junit.Test;

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

import static org.junit.Assert.assertEquals;

/**
 * @author Vlad Mihalcea
 */
public class MySQLTriggerBasedAuditedTest extends AbstractTest {

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

    @Override
    protected Database database() {
        return Database.MYSQL;
    }

    @Override
    protected void afterInit() {
        ddl("DROP TABLE IF EXISTS post_AUD");
        ddl("""
            CREATE TABLE IF NOT EXISTS post_AUD (
            	id BIGINT NOT NULL,
            	title VARCHAR(255),
            	REV_TYPE ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL,
            	REV_TIMESTAMP TIMESTAMP NOT NULL,
            	PRIMARY KEY (id, REV_TYPE, REV_TIMESTAMP)
            ) 
            """
        );

        ddl("""          
            CREATE TRIGGER post_insert_audit_trigger
            AFTER INSERT ON post
            FOR EACH ROW BEGIN
            INSERT INTO post_AUD (
                id,
                title,
                REV_TYPE,
                REV_TIMESTAMP
            )
            VALUES(
                NEW.id,
                NEW.title,
                'INSERT',
                CURRENT_TIMESTAMP
            );
            END
            """
        );

        ddl("""
            CREATE TRIGGER post_update_audit_trigger
            AFTER UPDATE ON post
            FOR EACH ROW BEGIN
            INSERT INTO post_AUD (
                id,
                title,
                REV_TYPE,
                REV_TIMESTAMP
            )
            VALUES(
                NEW.id,
                NEW.title,
                'UPDATE',
                CURRENT_TIMESTAMP
            );
            END
            """
        );

        ddl("""
            CREATE TRIGGER post_delete_audit_trigger
            AFTER DELETE ON post
            FOR EACH ROW BEGIN
            INSERT INTO post_AUD (
                id,
                title,
                REV_TYPE,
                REV_TIMESTAMP
            )
            VALUES(
                OLD.id,
                OLD.title,
                'DELETE',
                CURRENT_TIMESTAMP
            );
            END
            """
        );
    }

    @Test
    public void test() {
        doInJPA(entityManager -> {
            Post post = new Post();
            post.setId(1L);
            post.setTitle("High-Performance Java Persistence 1st edition");
            entityManager.persist(post);
        });

        doInJPA(entityManager -> {
            List<Tuple> revisions = getPostRevisions(entityManager);

            assertEquals(1, revisions.size());
        });

        doInJPA(entityManager -> {
            Post post = entityManager.find(Post.class, 1L);
            post.setTitle("High-Performance Java Persistence 2nd edition");
        });

        doInJPA(entityManager -> {
            List<Tuple> revisions = getPostRevisions(entityManager);

            assertEquals(2, revisions.size());
        });

        doInJPA(entityManager -> {
            entityManager.remove(
                entityManager.getReference(Post.class, 1L)
            );
        });

        doInJPA(entityManager -> {
            List<Tuple> revisions = getPostRevisions(entityManager);

            assertEquals(3, revisions.size());
        });
    }

    private List<Tuple> getPostRevisions(EntityManager entityManager) {
        return entityManager.createNativeQuery("""
            SELECT *
            FROM post_AUD 
            """, Tuple.class)
        .getResultList();
    }

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

        @Id
        private Long id;

        private String title;

        public Long getId() {
            return id;
        }

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

        public String getTitle() {
            return title;
        }

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

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

@@ -166,16 +166,46 @@
 tabase;%0A
+import org.hibernate.Session;%0A
 import o
@@ -244,16 +244,16 @@
 actory;%0A
-
 import o
@@ -1089,32 +1089,83 @@
 STAMP NOT NULL,%0A
+            %09REV_CREATED_BY VARCHAR(255) NOT NULL,%0A
             %09PRI
@@ -1514,32 +1514,64 @@
    REV_TIMESTAMP
+,%0A                REV_CREATED_BY
 %0A            )%0A 
@@ -1691,32 +1691,62 @@
 URRENT_TIMESTAMP
+,%0A                @logged_user
 %0A            );%0A
@@ -2047,32 +2047,64 @@
    REV_TIMESTAMP
+,%0A                REV_CREATED_BY
 %0A            )%0A 
@@ -2224,32 +2224,62 @@
 URRENT_TIMESTAMP
+,%0A                @logged_user
 %0A            );%0A
@@ -2580,32 +2580,64 @@
    REV_TIMESTAMP
+,%0A                REV_CREATED_BY
 %0A            )%0A 
@@ -2765,16 +2765,46 @@
 IMESTAMP
+,%0A                @logged_user
 %0A       
@@ -2923,32 +2923,194 @@
 ityManager -%3E %7B%0A
+            entityManager.unwrap(Session.class).doWork(connection -%3E %7B%0A                update(connection, %22SET @logged_user = 'Vlad Mihalcea'%22);%0A            %7D);%0A%0A
             Post
@@ -3479,32 +3479,194 @@
 ityManager -%3E %7B%0A
+            entityManager.unwrap(Session.class).doWork(connection -%3E %7B%0A                update(connection, %22SET @logged_user = 'Vlad Mihalcea'%22);%0A            %7D);%0A%0A
             Post
@@ -3955,32 +3955,32 @@
 );%0A        %7D);%0A%0A
-
         doInJPA(
@@ -3990,32 +3990,206 @@
 ityManager -%3E %7B%0A
+            entityManager.unwrap(Session.class).doWork(connection -%3E %7B%0A                update(connection, %22SET @logged_user = 'Vlad Mihalcea'%22);%0A            %7D);%0A            %0A
             enti

Add logged user to the MySQL trigger-based audit logging