/*
 * 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.test.annotations.onetomany;

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertTrue;
import static org.junit.Assert.fail;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.NullPrecedence;
import org.hibernate.Session;
import org.hibernate.dialect.H2Dialect;
import org.hibernate.dialect.MySQLDialect;
import org.hibernate.dialect.Oracle8iDialect;
import org.hibernate.dialect.SQLServer2008Dialect;
import org.hibernate.dialect.SQLServer2012Dialect;
import org.hibernate.dialect.SQLServerDialect;
import org.hibernate.engine.spi.SessionImplementor;
import org.hibernate.persister.collection.CollectionPersister;
import org.hibernate.persister.collection.QueryableCollection;
import org.hibernate.sql.SimpleSelect;
import org.hibernate.testing.RequiresDialect;
import org.hibernate.testing.TestForIssue;
import org.hibernate.testing.junit4.BaseCoreFunctionalTestCase;
import org.junit.Assert;
import org.junit.Test;

/**
 * @author Emmanuel Bernard
 * @author Lukasz Antoniak (lukasz dot antoniak at gmail dot com)
 * @author Brett Meyer
 */
public class OrderByTest extends BaseCoreFunctionalTestCase {
	@Test
	public void testOrderByOnIdClassProperties() throws Exception {
		Session s = openSession( );
		s.getTransaction().begin();
		Order o = new Order();
		o.setAcademicYear( 2000 );
		o.setSchoolId( "Supelec" );
		o.setSchoolIdSort( 1 );
		s.persist( o );
		OrderItem oi1 = new OrderItem();
		oi1.setAcademicYear( 2000 );
		oi1.setDayName( "Monday" );
		oi1.setSchoolId( "Supelec" );
		oi1.setOrder( o );
		oi1.setDayNo( 23 );
		s.persist( oi1 );
		OrderItem oi2 = new OrderItem();
		oi2.setAcademicYear( 2000 );
		oi2.setDayName( "Tuesday" );
		oi2.setSchoolId( "Supelec" );
		oi2.setOrder( o );
		oi2.setDayNo( 30 );
		s.persist( oi2 );
		s.flush();
		s.clear();

		OrderID oid = new OrderID();
		oid.setAcademicYear( 2000 );
		oid.setSchoolId( "Supelec" );
		o = (Order) s.get( Order.class, oid );
		assertEquals( 30, o.getItemList().get( 0 ).getDayNo().intValue() );

		s.getTransaction().rollback();
		s.close();
	}

	@Test
	@TestForIssue(jiraKey = "HHH-465")
	@RequiresDialect(value = { H2Dialect.class, MySQLDialect.class, SQLServer2008Dialect.class },
			comment = "By default H2 places NULL values first, so testing 'NULLS LAST' expression. " +
					"For MySQL and SQL Server 2008 testing overridden Dialect#renderOrderByElement(String, String, String, NullPrecedence) method. " +
					"MySQL and SQLServer 2008 does not support NULLS FIRST / LAST syntax at the moment, so transforming the expression to 'CASE WHEN ...'.")
	public void testAnnotationNullsFirstLast() {
		Session session = openSession();

		// Populating database with test data.
		session.getTransaction().begin();
		Tiger tiger1 = new Tiger();
		tiger1.setName( null ); // Explicitly setting null value.
		Tiger tiger2 = new Tiger();
		tiger2.setName( "Max" );
		Monkey monkey1 = new Monkey();
		monkey1.setName( "Michael" );
		Monkey monkey2 = new Monkey();
		monkey2.setName( null );  // Explicitly setting null value.
		Zoo zoo = new Zoo( "Warsaw ZOO" );
		zoo.getTigers().add( tiger1 );
		zoo.getTigers().add( tiger2 );
		zoo.getMonkeys().add( monkey1 );
		zoo.getMonkeys().add( monkey2 );
		session.persist( zoo );
		session.persist( tiger1 );
		session.persist( tiger2 );
		session.persist( monkey1 );
		session.persist( monkey2 );
		session.getTransaction().commit();

		session.clear();

		session.getTransaction().begin();
		zoo = (Zoo) session.get( Zoo.class, zoo.getId() );
		// Testing @org.hibernate.annotations.OrderBy.
		Iterator<Tiger> iterator1 = zoo.getTigers().iterator();
		Assert.assertEquals( tiger2.getName(), iterator1.next().getName() );
		Assert.assertNull( iterator1.next().getName() );
		// Testing @javax.persistence.OrderBy.
		Iterator<Monkey> iterator2 = zoo.getMonkeys().iterator();
		Assert.assertEquals( monkey1.getName(), iterator2.next().getName() );
		Assert.assertNull( iterator2.next().getName() );
		session.getTransaction().commit();

		session.clear();

		// Cleanup data.
		session.getTransaction().begin();
		session.delete( tiger1 );
		session.delete( tiger2 );
		session.delete( monkey1 );
		session.delete( monkey2 );
		session.delete( zoo );
		session.getTransaction().commit();

		session.close();
	}

	@Test
	@TestForIssue(jiraKey = "HHH-465")
	@RequiresDialect(value = { H2Dialect.class, MySQLDialect.class, SQLServer2008Dialect.class },
			comment = "By default H2 places NULL values first, so testing 'NULLS LAST' expression. " +
					"For MySQL and SQL Server 2008 testing overridden Dialect#renderOrderByElement(String, String, String, NullPrecedence) method. " +
					"MySQL and SQL Server 2008 does not support NULLS FIRST / LAST syntax at the moment, so transforming the expression to 'CASE WHEN ...'.")
	public void testCriteriaNullsFirstLast() {
		Session session = openSession();

		// Populating database with test data.
		session.getTransaction().begin();
		Zoo zoo1 = new Zoo( null );
		Zoo zoo2 = new Zoo( "Warsaw ZOO" );
		session.persist( zoo1 );
		session.persist( zoo2 );
		session.getTransaction().commit();

		session.clear();

		session.getTransaction().begin();
		Criteria criteria = session.createCriteria( Zoo.class );
		criteria.addOrder( org.hibernate.criterion.Order.asc( "name" ).nulls( NullPrecedence.LAST ) );
		Iterator<Zoo> iterator = (Iterator<Zoo>) criteria.list().iterator();
		Assert.assertEquals( zoo2.getName(), iterator.next().getName() );
		Assert.assertNull( iterator.next().getName() );
		session.getTransaction().commit();

		session.clear();

		// Cleanup data.
		session.getTransaction().begin();
		session.delete( zoo1 );
		session.delete( zoo2 );
		session.getTransaction().commit();

		session.close();
	}

	@Test
	@TestForIssue(jiraKey = "HHH-465")
	@RequiresDialect(value = { H2Dialect.class, MySQLDialect.class, SQLServer2008Dialect.class },
			comment = "By default H2 places NULL values first, so testing 'NULLS LAST' expression. " +
					"For MySQL and SQL Server 2008 testing overridden Dialect#renderOrderByElement(String, String, String, NullPrecedence) method. " +
					"MySQL and SQL Server 2008 does not support NULLS FIRST / LAST syntax at the moment, so transforming the expression to 'CASE WHEN ...'.")
	public void testNullsFirstLastSpawnMultipleColumns() {
		Session session = openSession();

		// Populating database with test data.
		session.getTransaction().begin();
		Zoo zoo = new Zoo();
		zoo.setName( "Berlin ZOO" );
		Visitor visitor1 = new Visitor( null, null );
		Visitor visitor2 = new Visitor( null, "Antoniak" );
		Visitor visitor3 = new Visitor( "Lukasz", "Antoniak" );
		zoo.getVisitors().add( visitor1 );
		zoo.getVisitors().add( visitor2 );
		zoo.getVisitors().add( visitor3 );
		session.save( zoo );
		session.save( visitor1 );
		session.save( visitor2 );
		session.save( visitor3 );
		session.getTransaction().commit();

		session.clear();

		session.getTransaction().begin();
		zoo = (Zoo) session.get( Zoo.class, zoo.getId() );
		Iterator<Visitor> iterator = zoo.getVisitors().iterator();
		Assert.assertEquals( 3, zoo.getVisitors().size() );
		Assert.assertEquals( visitor3, iterator.next() );
		Assert.assertEquals( visitor2, iterator.next() );
		Assert.assertEquals( visitor1, iterator.next() );
		session.getTransaction().commit();

		session.clear();

		// Cleanup data.
		session.getTransaction().begin();
		session.delete( visitor1 );
		session.delete( visitor2 );
		session.delete( visitor3 );
		session.delete( zoo );
		session.getTransaction().commit();

		session.close();
	}

	@Test
	@TestForIssue(jiraKey = "HHH-465")
	@RequiresDialect(value = { H2Dialect.class, MySQLDialect.class, SQLServer2008Dialect.class },
			comment = "By default H2 places NULL values first, so testing 'NULLS LAST' expression. " +
					"For MySQL and SQL Server 2008 testing overridden Dialect#renderOrderByElement(String, String, String, NullPrecedence) method. " +
					"MySQL and SQL Server 2008 does not support NULLS FIRST / LAST syntax at the moment, so transforming the expression to 'CASE WHEN ...'.")
	public void testHqlNullsFirstLast() {
		Session session = openSession();

		// Populating database with test data.
		session.getTransaction().begin();
		Zoo zoo1 = new Zoo();
		zoo1.setName( null );
		Zoo zoo2 = new Zoo();
		zoo2.setName( "Warsaw ZOO" );
		session.persist( zoo1 );
		session.persist( zoo2 );
		session.getTransaction().commit();

		session.getTransaction().begin();
		List<Zoo> orderedResults = (List<Zoo>) session.createQuery( "from Zoo z order by z.name nulls lAsT" ).list();
		Assert.assertEquals( Arrays.asList( zoo2, zoo1 ), orderedResults );
		session.getTransaction().commit();

		session.clear();

		// Cleanup data.
		session.getTransaction().begin();
		session.delete( zoo1 );
		session.delete( zoo2 );
		session.getTransaction().commit();

		session.close();
	}

	@Test
	@TestForIssue( jiraKey = "HHH-7608" )
	@RequiresDialect({ H2Dialect.class, Oracle8iDialect.class })
	public void testOrderByReferencingFormulaColumn() {
		Session session = openSession();

		// Populating database with test data.
		session.getTransaction().begin();
		Box box1 = new Box( 1 );
		Item item1 = new Item( 1, "1", box1 );
		Item item2 = new Item( 2, "22", box1 );
		Item item3 = new Item( 3, "2", box1 );
		session.persist( box1 );
		session.persist( item1 );
		session.persist( item2 );
		session.persist( item3 );
		session.flush();
		session.refresh( item1 );
		session.refresh( item2 );
		session.refresh( item3 );
		session.getTransaction().commit();

		session.clear();

		session.getTransaction().begin();
		box1 = (Box) session.get( Box.class, box1.getId() );
		Assert.assertEquals( Arrays.asList( item2, item1, item3 ), box1.getItems() );
		session.getTransaction().commit();

		session.clear();

		// Cleanup data.
		session.getTransaction().begin();
		session.delete( item1 );
		session.delete( item2 );
		session.delete( item3 );
		session.delete( box1 );
		session.getTransaction().commit();

		session.close();
	}
	
	@Test
	@TestForIssue(jiraKey = "HHH-5732")
	public void testInverseIndex() {
		final CollectionPersister transactionsPersister = sessionFactory().getCollectionPersister(
				BankAccount.class.getName() + ".transactions" );
		assertTrue( transactionsPersister.isInverse() );

		Session s = openSession();
		s.getTransaction().begin();

		BankAccount account = new BankAccount();
		account.addTransaction( "zzzzz" );
		account.addTransaction( "aaaaa" );
		account.addTransaction( "mmmmm" );
		s.save( account );
		s.getTransaction().commit();

		s.close();

		s = openSession();
		s.getTransaction().begin();
		
		try {
			final QueryableCollection queryableCollection = (QueryableCollection) transactionsPersister;
			SimpleSelect select = new SimpleSelect( getDialect() )
					.setTableName( queryableCollection.getTableName() )
					.addColumn( "code" )
					.addColumn( "transactions_index" );
			PreparedStatement preparedStatement = ((SessionImplementor)s).getJdbcCoordinator().getStatementPreparer().prepareStatement( select.toStatementString() );
			ResultSet resultSet = ((SessionImplementor)s).getJdbcCoordinator().getResultSetReturn().extract( preparedStatement );
			Map<Integer, String> valueMap = new HashMap<Integer, String>();
			while ( resultSet.next() ) {
				final String code = resultSet.getString( 1 );
				assertFalse( "code column was null", resultSet.wasNull() );
				final int indx = resultSet.getInt( 2 );
				assertFalse( "List index column was null", resultSet.wasNull() );
				valueMap.put( indx, code );
			}
			assertEquals( 3, valueMap.size() );
			assertEquals( "zzzzz", valueMap.get( 0 ) );
			assertEquals( "aaaaa", valueMap.get( 1 ) );
			assertEquals( "mmmmm", valueMap.get( 2 ) );
		}
		catch ( SQLException e ) {
			fail(e.getMessage());
		}
		finally {
			s.getTransaction().rollback();
			s.close();
		}
	}
	
	@Test
	@TestForIssue( jiraKey = "HHH-8083" )
	public void testInverseIndexCascaded() {
		final Session s = openSession();
		s.getTransaction().begin();

		Forum forum = new Forum();
		forum.setName( "forum1" );
		forum = (Forum) s.merge( forum );

		s.flush();
		s.clear();
		sessionFactory().getCache().evictEntityRegions();

		forum = (Forum) s.get( Forum.class, forum.getId() );

		final Post post = new Post();
		post.setName( "post1" );
		post.setForum( forum );
		forum.getPosts().add( post );

		final User user = new User();
		user.setName( "john" );
		user.setForum( forum );
		forum.getUsers().add( user );

		forum = (Forum) s.merge( forum );

		s.flush();
		s.clear();
		sessionFactory().getCache().evictEntityRegions();

		forum = (Forum) s.get( Forum.class, forum.getId() );

		final Post post2 = new Post();
		post2.setName( "post2" );
		post2.setForum( forum );
		forum.getPosts().add( post2 );

		forum = (Forum) s.merge( forum );

		s.flush();
		s.clear();
		sessionFactory().getCache().evictEntityRegions();

		forum = (Forum) s.get( Forum.class, forum.getId() );

		assertEquals( 2, forum.getPosts().size() );
		assertEquals( "post1", forum.getPosts().get( 0 ).getName() );
		assertEquals( "post2", forum.getPosts().get( 1 ).getName() );
		Hibernate.initialize( forum.getPosts() );
		assertEquals( 2, forum.getPosts().size() );
		assertEquals( 1, forum.getUsers().size() );
		assertEquals( "john", forum.getUsers().get( 0 ).getName() );
		Hibernate.initialize( forum.getUsers() );
		assertEquals( 1, forum.getUsers().size() );
	}
  
	@Test
	@TestForIssue(jiraKey = "HHH-8794")
	public void testOrderByNoElement() {

		final Session s = openSession();
		s.getTransaction().begin();

		Employee employee = new Employee( 1 );

		Computer computer = new Computer( 1 );
		computer.setComputerName( "Bob's computer" );
		computer.setEmployee( employee );

		Computer computer2 = new Computer( 2 );
		computer2.setComputerName( "Alice's computer" );
		computer2.setEmployee( employee );

		s.save( employee );
		s.save( computer2 );
		s.save( computer );

		s.flush();
		s.clear();
		sessionFactory().getCache().evictEntityRegions();

		employee = (Employee) s.get( Employee.class, employee.getId() );

		assertEquals( 2, employee.getAssets().size() );
		assertEquals( 1, employee.getAssets().get( 0 ).getIdAsset().intValue() );
		assertEquals( 2, employee.getAssets().get( 1 ).getIdAsset().intValue() );
	}

	@Test
	@TestForIssue( jiraKey = "HHH-9002" )
	public void testOrderByOneToManyWithJoinTable() {
		A a = new A();
		a.setName( "a" );
		B b1 = new B();
		b1.setName( "b1" );
		B b2 = new B();
		b2.setName( "b2" );
		C c11 = new C();
		c11.setName( "c11" );
		C c12 = new C();
		c12.setName( "c12" );
		C c21 = new C();
		c21.setName( "c21" );
		C c22 = new C();
		c22.setName( "c22" );

		a.getBs().add( b1 );
		a.getBs().add( b2 );
		b1.getCs().add( c11 );
		b1.getCs().add( c12 );
		b2.getCs().add( c21 );
		b2.getCs().add( c22 );

		Session s = openSession();
		s.getTransaction().begin();
		s.persist( a );
		s.getTransaction().commit();
		s.close();

		s = openSession();
		s.getTransaction().begin();

		b1 =  (B) s.get( B.class, b1.getId() );
		assertEquals( "b1", b1.getName() );
		List<C> cs = b1.getCs();
		assertEquals( 2, cs.size() );
		assertEquals( "c11", cs.get( 0 ).getName() );
		assertEquals( "c12", cs.get( 1 ).getName() );

		s.getTransaction().commit();
		s.close();

		s = openSession();
		s.getTransaction().begin();

		a = (A) s.get( A.class, a.getId() );
		assertEquals( "a", a.getName() );
		assertEquals( 2, a.getBs().size() );
		List<B> bs = a.getBs();
		assertEquals( "b1", bs.get( 0 ).getName() );
		assertEquals( "b2", bs.get( 1 ).getName() );
		List<C> b1cs = bs.get( 0 ).getCs();
		assertEquals( 2, b1cs.size() );
		assertEquals( "c11", b1cs.get( 0 ).getName() );
		assertEquals( "c12", b1cs.get( 1 ).getName() );
		List<C> b2cs = bs.get( 1 ).getCs();
		assertEquals( 2, b2cs.size() );
		assertEquals( "c21", b2cs.get( 0 ).getName() );
		assertEquals( "c22", b2cs.get( 1 ).getName() );

		s.delete( a );

		s.getTransaction().commit();
		s.close();
	}

	@Override
	protected Class[] getAnnotatedClasses() {
		return new Class[] {
				Order.class, OrderItem.class, Zoo.class, Tiger.class,
				Monkey.class, Visitor.class, Box.class, Item.class,
				BankAccount.class, Transaction.class,
				Comment.class, Forum.class, Post.class, User.class,
				Asset.class, Computer.class, Employee.class,
				A.class, B.class, C.class
		};
	}
}
