/*
 * Hibernate, Relational Persistence for Idiomatic Java
 *
 * License: GNU Lesser General Public License (LGPL), version 2.1 or later.
 * See the lgpl.txt file in the root directory or <http://www.gnu.org/licenses/lgpl-2.1.html>.
 */
package org.hibernate.jpa.test.procedure;

import javax.persistence.EntityManager;
import javax.persistence.StoredProcedureQuery;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.dialect.DerbyTenSevenDialect;
import org.hibernate.engine.jdbc.connections.spi.JdbcConnectionAccess;
import org.hibernate.engine.jdbc.spi.JdbcServices;
import org.hibernate.engine.spi.SessionFactoryImplementor;
import org.hibernate.jpa.AvailableSettings;
import org.hibernate.jpa.HibernateEntityManagerFactory;
import org.hibernate.jpa.boot.spi.Bootstrap;
import org.hibernate.jpa.boot.spi.PersistenceUnitDescriptor;
import org.hibernate.jpa.test.BaseEntityManagerFunctionalTestCase;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import org.hibernate.testing.FailureExpected;
import org.hibernate.testing.junit4.BaseUnitTestCase;

import static org.hibernate.testing.junit4.ExtraAssertions.assertTyping;
import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertTrue;
import static org.junit.Assert.fail;

/**
 * Tests various JPA usage scenarios for performing stored procedures.  Inspired by the awesomely well-done JPA TCK
 *
 * @author Steve Ebersole
 */
public class JpaTckUsageTest extends BaseUnitTestCase {

	@Test
	public void testMultipleGetUpdateCountCalls() {
		EntityManager em = entityManagerFactory.createEntityManager();
		em.getTransaction().begin();

		try {
			StoredProcedureQuery query = em.createStoredProcedureQuery( "findOneUser" );
			// this is what the TCK attempts to do, don't shoot the messenger...
			query.getUpdateCount();
			// yep, twice
			query.getUpdateCount();
		}
		finally {
			em.getTransaction().commit();
			em.close();
		}
	}

	@Test
	public void testBasicScalarResults() {
		EntityManager em = entityManagerFactory.createEntityManager();
		em.getTransaction().begin();

		try {
			StoredProcedureQuery query = em.createStoredProcedureQuery( "findOneUser" );
			boolean isResult = query.execute();
			assertTrue( isResult );
			int updateCount = query.getUpdateCount();

			boolean results = false;
			do {
				List list = query.getResultList();
				assertEquals( 1, list.size() );

				results = query.hasMoreResults();
				// and it only sets the updateCount once lol
			} while ( results || updateCount != -1);
		}
		finally {
			em.getTransaction().commit();
			em.close();
		}
	}

	@Test
	@FailureExpected( jiraKey = "HHH-8416", message = "JPA TCK challenge" )
	public void testHasMoreResultsHandlingTckChallenge() {
		EntityManager em = entityManagerFactory.createEntityManager();
		em.getTransaction().begin();

		try {
			StoredProcedureQuery query = em.createStoredProcedureQuery( "findOneUser", User.class );
			assertTrue( query.execute() );
			assertTrue( query.hasMoreResults() );
			query.getResultList();
			assertFalse( query.hasMoreResults() );
		}
		finally {
			em.getTransaction().commit();
			em.close();
		}
	}

	@Test
	public void testHasMoreResultsHandling() {
		EntityManager em = entityManagerFactory.createEntityManager();
		em.getTransaction().begin();

		try {
			StoredProcedureQuery query = em.createStoredProcedureQuery( "findOneUser", User.class );
			assertTrue( query.execute() );
			query.getResultList();
			assertFalse( query.hasMoreResults() );
		}
		finally {
			em.getTransaction().commit();
			em.close();
		}
	}

	@Test
	public void testResultClassHandling() {
		EntityManager em = entityManagerFactory.createEntityManager();
		em.getTransaction().begin();

		try {
			StoredProcedureQuery query = em.createStoredProcedureQuery( "findOneUser", User.class );
			boolean isResult = query.execute();
			assertTrue( isResult );
			int updateCount = query.getUpdateCount();

			boolean results = false;
			do {
				List list = query.getResultList();
				assertEquals( 1, list.size() );
				assertTyping( User.class, list.get( 0 ) );

				results = query.hasMoreResults();
				// and it only sets the updateCount once lol
			} while ( results || updateCount != -1);
		}
		finally {
			em.getTransaction().commit();
			em.close();
		}
	}

	@Test
	public void testSettingInParamDefinedOnNamedStoredProcedureQuery() {
		EntityManager em = entityManagerFactory.createEntityManager();
		em.getTransaction().begin();
		try {
			StoredProcedureQuery query = em.createNamedStoredProcedureQuery( "positional-param" );
			query.setParameter( 1, 1 );
		}
		finally {
			em.getTransaction().commit();
			em.close();
		}
	}

	@Test
	public void testSettingNonExistingParams() {
		EntityManager em = entityManagerFactory.createEntityManager();
		em.getTransaction().begin();

		try {
			// non-existing positional param
			try {
				StoredProcedureQuery query = em.createNamedStoredProcedureQuery( "positional-param" );
				query.setParameter( 99, 1 );
				fail( "Expecting an exception" );
			}
			catch (IllegalArgumentException expected) {
				// this is the expected condition
			}

			// non-existing named param
			try {
				StoredProcedureQuery query = em.createNamedStoredProcedureQuery( "positional-param" );
				query.setParameter( "does-not-exist", 1 );
				fail( "Expecting an exception" );
			}
			catch (IllegalArgumentException expected) {
				// this is the expected condition
			}
		}
		finally {
			em.getTransaction().commit();
			em.close();
		}
	}

	@Test
	@FailureExpected( jiraKey = "HHH-8395", message = "Out of the frying pan into the fire: https://issues.apache.org/jira/browse/DERBY-211" )
	public void testExecuteUpdate() {
		EntityManager em = entityManagerFactory.createEntityManager();
		em.getTransaction().begin();

		try {
			StoredProcedureQuery query = em.createStoredProcedureQuery( "deleteAllUsers" );
			int count = query.executeUpdate();
			// this fails because the Derby EmbeddedDriver is returning zero here rather than the actual updateCount :(
			// https://issues.apache.org/jira/browse/DERBY-211
			assertEquals( 1, count );
		}
		finally {
			em.getTransaction().commit();
			em.close();
		}
	}

	public void testParameterRegistration() {

	}

	// todo : look at ways to allow "Auxiliary DB Objects" to the db via EMF bootstrapping.

//	public static final String findOneUser_CREATE_CMD = "CREATE ALIAS findOneUser AS $$\n" +
//			"import org.h2.tools.SimpleResultSet;\n" +
//			"import java.sql.*;\n" +
//			"@CODE\n" +
//			"ResultSet findOneUser() {\n" +
//			"    SimpleResultSet rs = new SimpleResultSet();\n" +
//			"    rs.addColumn(\"ID\", Types.INTEGER, 10, 0);\n" +
//			"    rs.addColumn(\"NAME\", Types.VARCHAR, 255, 0);\n" +
//			"    rs.addRow(1, \"Steve\");\n" +
//			"    return rs;\n" +
//			"}\n" +
//			"$$";
//	public static final String findOneUser_DROP_CMD = "DROP ALIAS findOneUser IF EXISTS";
//
//	public static final String deleteAllUsers_CREATE_CMD = "CREATE ALIAS deleteAllUsers AS $$\n" +
//			"@CODE\n" +
//			"int deleteAllUsers() {\n" +
//			"    return 156;" +
//			"}\n" +
//			"$$";
//	public static final String deleteAllUsers_DROP_CMD = "DROP ALIAS deleteAllUsers IF EXISTS";

	HibernateEntityManagerFactory entityManagerFactory;

	@Before
	public void startUp() {
		// create the EMF
		entityManagerFactory = Bootstrap.getEntityManagerFactoryBuilder(
				buildPersistenceUnitDescriptor(),
				buildSettingsMap()
		).build().unwrap( HibernateEntityManagerFactory.class );

		// create the procedures
		createTestUser( entityManagerFactory );
		createProcedures( entityManagerFactory );
	}

	private PersistenceUnitDescriptor buildPersistenceUnitDescriptor() {
		return new BaseEntityManagerFunctionalTestCase.TestingPersistenceUnitDescriptorImpl( getClass().getSimpleName() );
	}

	@SuppressWarnings("unchecked")
	private Map buildSettingsMap() {
		Map settings = new HashMap();

		settings.put( AvailableSettings.LOADED_CLASSES, Collections.singletonList( User.class ) );

		settings.put( org.hibernate.cfg.AvailableSettings.DIALECT, DerbyTenSevenDialect.class );
		settings.put( org.hibernate.cfg.AvailableSettings.DRIVER,  org.apache.derby.jdbc.EmbeddedDriver.class.getName() );
//		settings.put( org.hibernate.cfg.AvailableSettings.URL, "jdbc:derby:/tmp/hibernate-orm-testing;create=true" );
		settings.put( org.hibernate.cfg.AvailableSettings.URL, "jdbc:derby:memory:hibernate-orm-testing;create=true" );
		settings.put( org.hibernate.cfg.AvailableSettings.USER, "" );

		settings.put( org.hibernate.cfg.AvailableSettings.HBM2DDL_AUTO, "create-drop" );
		settings.put( org.hibernate.cfg.AvailableSettings.USE_NEW_ID_GENERATOR_MAPPINGS, "true" );
		settings.put( org.hibernate.cfg.AvailableSettings.DIALECT, DerbyTenSevenDialect.class.getName() );
		return settings;
	}

	@After
	public void tearDown() {
		if ( entityManagerFactory == null ) {
			return;
		}

		deleteTestUser( entityManagerFactory );
		dropProcedures( entityManagerFactory );

		entityManagerFactory.close();
	}

	private void createProcedures(HibernateEntityManagerFactory emf) {
		final SessionFactoryImplementor sf = emf.unwrap( SessionFactoryImplementor.class );
		final JdbcConnectionAccess connectionAccess = sf.getServiceRegistry().getService( JdbcServices.class ).getBootstrapJdbcConnectionAccess();
		final Connection conn;
		try {
			conn = connectionAccess.obtainConnection();
			conn.setAutoCommit( false );

			try {
				Statement statement = conn.createStatement();

				// drop them, just to be sure
				try {
					dropProcedures( statement );
				}
				catch (SQLException ignore) {
				}

				createProcedureFindOneUser( statement );
				createProcedureDeleteAllUsers( statement );
				try {
					statement.close();
				}
				catch (SQLException ignore) {
				}
			}
			finally {
				try {
					conn.commit();
				}
				catch (SQLException e) {
					System.out.println( "Unable to commit transaction after creating creating procedures");
				}

				try {
					connectionAccess.releaseConnection( conn );
				}
				catch (SQLException ignore) {
				}
			}
		}
		catch (SQLException e) {
			throw new RuntimeException( "Unable to create stored procedures", e );
		}
	}

	private void dropProcedures(Statement statement) throws SQLException {
		statement.execute( "DROP PROCEDURE findOneUser" );
		statement.execute( "DROP PROCEDURE deleteAllUsers" );
	}

	private void createProcedureFindOneUser(Statement statement) throws SQLException {
		statement.execute(
				"CREATE PROCEDURE findOneUser() " +
						"language java " +
						"dynamic result sets 1 " +
						"external name 'org.hibernate.jpa.test.procedure.JpaTckUsageTest.findOneUser' " +
						"parameter style java"
		);
	}

	private void createProcedureDeleteAllUsers(Statement statement) throws SQLException {
		statement.execute(
				"CREATE PROCEDURE deleteAllUsers() " +
						"language java " +
						"external name 'org.hibernate.jpa.test.procedure.JpaTckUsageTest.deleteAllUsers' " +
						"parameter style java"
		);
	}

	public static void findOneUser(ResultSet[] results) throws SQLException {
		Connection conn = DriverManager.getConnection( "jdbc:default:connection" );
		PreparedStatement ps = conn.prepareStatement( "select id, name from t_user where name=?" );
		ps.setString( 1, "steve" );
		results[0] = ps.executeQuery();
		conn.close();
	}

	public static void findUserIds(ResultSet[] results) throws SQLException {
		Connection conn = DriverManager.getConnection( "jdbc:default:connection" );
		PreparedStatement ps = conn.prepareStatement( "select id from t_user" );
		results[0] = ps.executeQuery();
		conn.close();
	}

	public static void deleteAllUsers() throws SQLException {
		// afaict the only way to return update counts here is to actually perform some DML
		Connection conn = DriverManager.getConnection( "jdbc:default:connection" );
		System.out.println( "Preparing delete all" );
		PreparedStatement ps = conn.prepareStatement( "delete from t_user" );
		System.out.println( "Executing delete all" );
		int count = ps.executeUpdate();
		System.out.println( "Count : " + count );
		System.out.println( "Closing resources" );
		ps.close();
		conn.close();
	}

	private void createTestUser(HibernateEntityManagerFactory entityManagerFactory) {
		EntityManager em = entityManagerFactory.createEntityManager();
		em.getTransaction().begin();

		em.persist( new User( 1, "steve" ) );
		em.getTransaction().commit();
		em.close();
	}

	private void deleteTestUser(HibernateEntityManagerFactory entityManagerFactory) {
		EntityManager em = entityManagerFactory.createEntityManager();
		em.getTransaction().begin();
		em.createQuery( "delete from User" ).executeUpdate();
		em.getTransaction().commit();
		em.close();
	}

	private void dropProcedures(HibernateEntityManagerFactory emf) {
		final SessionFactoryImplementor sf = emf.unwrap( SessionFactoryImplementor.class );
		final JdbcConnectionAccess connectionAccess = sf.getServiceRegistry().getService( JdbcServices.class ).getBootstrapJdbcConnectionAccess();
		final Connection conn;
		try {
			conn = connectionAccess.obtainConnection();
			conn.setAutoCommit( false );

			try {
				Statement statement = conn.createStatement();
				dropProcedures( statement );
				try {
					statement.close();
				}
				catch (SQLException ignore) {
				}
			}
			finally {
				try {
					conn.commit();
				}
				catch (SQLException e) {
					System.out.println( "Unable to commit transaction after creating dropping procedures");
				}

				try {
					connectionAccess.releaseConnection( conn );
				}
				catch (SQLException ignore) {
				}
			}
		}
		catch (SQLException e) {
			throw new RuntimeException( "Unable to drop stored procedures", e );
		}
	}
}
