package nuts.core.dao.sql.engine;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import nuts.core.dao.sql.SqlResultSet;
import nuts.core.dao.sql.TestA;

import org.junit.Assert;
import org.junit.Test;

/**
 * SimpleSqlExecutorTestCase
 */
public abstract class SimpleSqlExecutorTestCase extends SqlExecutorTestCase {
	/**
	 * test01
	 */
	@Test
	public void test01() {
		String sql = "SELECT * FROM TEST WHERE NAME IS NULL";
		
		testQueryForObject(sql, (Map)null, (Map)null);
	}
	
	/**
	 * test02
	 */
	@Test
	public void test02() {
		String sql = "SELECT * FROM TEST WHERE ID=:id";
		
		Map<String, Object> param = new HashMap<String, Object>();
		param.put("id", 1005);
		
		Map<String, Object> expected = new HashMap<String, Object>();
		expected.put("id", getExpectedInteger(1005));
		expected.put("name", "NAME 1005");
		expected.put("kind", "5");
		expected.put("price", new BigDecimal("1005.05"));
		expected.put("updateTime", getExpectedTimestamp("2009-05-05"));
	
		testQueryForObject(sql, param, expected);
	}
	
	/**
	 * test02a
	 */
	@Test
	public void test02a() {
		String sql = "SELECT * FROM TEST WHERE ID=:a.id";
		
		TestA param = new TestA();
		param.setA(new TestA());
		param.getA().setId(1005);
		
		TestA expected = new TestA();
		expected.setId(1005);
		expected.setName("NAME 1005");
		expected.setKind('5');
		expected.setPrice(new BigDecimal("1005.05"));
		expected.setUpdateTime(convertToDate("2009-05-05"));
	
		testQueryForObject(sql, param, expected);
	}
	
	/**
	 * test05a4
	 */
	@Test
	public void test05a4() {
		String sql = "SELECT * FROM TEST WHERE ID IN (:idArray)";

		TestA param = new TestA();
		param.setIdArray(new int[] { 1001, 1002, 1004, 1005 });
		
		TestA expected;
		List<TestA> list = new ArrayList<TestA>();
		expected = new TestA();
		expected.setId(1002);
		expected.setName("NAME 1002");
		expected.setKind('2');
		expected.setPrice(new BigDecimal("1002.02"));
		expected.setUpdateTime(convertToDate("2009-02-02"));
		list.add(expected);

		expected = new TestA();
		expected.setId(1004);
		expected.setName("NAME 1004");
		expected.setKind('4');
		expected.setPrice(new BigDecimal("1004.04"));
		expected.setUpdateTime(convertToDate("2009-04-04"));
		list.add(expected);

		testQueryForList(sql, param, 1, 2, TestA.class, list);
	}

	/**
	 * test06
	 */
	@Test
	public void test06() {
		String sql = "SELECT * FROM TEST WHERE ID IN (:id) ORDER BY ::orderCol ::orderDir";

		Map<String, Object> param = new HashMap<String, Object>();
		List<Integer> ids = new ArrayList<Integer>();
		ids.add(1001);
		ids.add(1005);
		param.put("id", ids);
		param.put("orderCol", "ID");
		param.put("orderDir", "DESC");
		
		List<Map> list = new ArrayList<Map>();
		Map<String, Object> expected = new HashMap<String, Object>();
		expected.put("id", getExpectedInteger(1005));
		expected.put("name", "NAME 1005");
		expected.put("kind", "5");
		expected.put("price", new BigDecimal("1005.05"));
		expected.put("updateTime", getExpectedTimestamp("2009-05-05"));
		list.add(expected);
		
		expected = new HashMap<String, Object>();
		expected.put("id", getExpectedInteger(1001));
		expected.put("name", "NAME 1001");
		expected.put("kind", "1");
		expected.put("price", new BigDecimal("1001.01"));
		expected.put("updateTime", getExpectedTimestamp("2009-01-01"));
		list.add(expected);

		testQueryForList(sql, param, HashMap.class, list);
	}

	/**
	 * test06a
	 */
	@Test
	public void test06a() {
		String sql = "SELECT * FROM TEST WHERE ID IN (:idList) ORDER BY ::orderCol ::orderDir";

		TestA param = new TestA();
		List<Integer> ids = new ArrayList<Integer>();
		ids.add(1001);
		ids.add(1005);
		param.setIdList(ids);
		param.setOrderCol("ID");
		param.setOrderDir("DESC");
		
		List<TestA> list = new ArrayList<TestA>();
		TestA expected = new TestA();
		expected.setId(1005);
		expected.setName("NAME 1005");
		expected.setKind('5');
		expected.setPrice(new BigDecimal("1005.05"));
		expected.setUpdateTime(convertToDate("2009-05-05"));
		list.add(expected);
		
		expected = new TestA();
		expected.setId(1001);
		expected.setName("NAME 1001");
		expected.setKind('1');
		expected.setPrice(new BigDecimal("1001.01"));
		expected.setUpdateTime(convertToDate("2009-01-01"));
		list.add(expected);

		testQueryForList(sql, param, TestA.class, list);
	}

	/**
	 * test08
	 */
	@Test
	public void test08() {
		String sql = "SELECT * FROM TEST WHERE ID IN (:id)";

		Map<String, Object> param = new HashMap<String, Object>();
		List<Integer> ids = new ArrayList<Integer>();
		ids.add(1001);
		ids.add(1005);
		param.put("id", ids);
		
		Map<Object, Map> map = new HashMap<Object, Map>();
		Map<String, Object> expected = new HashMap<String, Object>();
		expected.put("id", getExpectedInteger(1001));
		expected.put("name", "NAME 1001");
		expected.put("kind", "1");
		expected.put("price", new BigDecimal("1001.01"));
		expected.put("updateTime", getExpectedTimestamp("2009-01-01"));
		map.put(getExpectedInteger(1001), expected);
		
		expected = new HashMap<String, Object>();
		expected.put("id", getExpectedInteger(1005));
		expected.put("name", "NAME 1005");
		expected.put("kind", "5");
		expected.put("price", new BigDecimal("1005.05"));
		expected.put("updateTime", getExpectedTimestamp("2009-05-05"));
		map.put(getExpectedInteger(1005), expected);

		testQueryForMap(sql, param, HashMap.class, map);
	}
	
	/**
	 * test08a
	 */
	@Test
	public void test08a() {
		String sql = "SELECT * FROM TEST WHERE ID IN (:idList)";

		TestA param = new TestA();
		List<Integer> ids = new ArrayList<Integer>();
		ids.add(1001);
		ids.add(1005);
		param.setIdList(ids);
		
		Map<Integer, TestA> map = new HashMap<Integer, TestA>();
		TestA expected = new TestA();
		expected.setId(1005);
		expected.setName("NAME 1005");
		expected.setKind('5');
		expected.setPrice(new BigDecimal("1005.05"));
		expected.setUpdateTime(convertToDate("2009-05-05"));
		map.put(new Integer(1005), expected);
		
		expected = new TestA();
		expected.setId(1001);
		expected.setName("NAME 1001");
		expected.setKind('1');
		expected.setPrice(new BigDecimal("1001.01"));
		expected.setUpdateTime(convertToDate("2009-01-01"));
		map.put(new Integer(1001), expected);

		testQueryForMap(sql, param, TestA.class,  map);
	}
	
	/**
	 * test09
	 */
	@Test
	public void test09() {
		String updateSql = "UPDATE TEST SET NAME=:name WHERE ID=:id";
		String selectSql = "SELECT * FROM TEST WHERE ID=:id";

		Map<String, Object> param = new HashMap<String, Object>();
		param.put("id", 1001);
		param.put("name", "TEST 1001");
		
		Map<String, Object> expected = new HashMap<String, Object>();
		expected.put("id", getExpectedInteger(1001));
		expected.put("name", "TEST 1001");
		expected.put("kind", "1");
		expected.put("price", new BigDecimal("1001.01"));
		expected.put("updateTime", getExpectedTimestamp("2009-01-01"));
		
		testExecuteUpdate(updateSql, param, selectSql, expected);
		
		test09a();
	}
	
	/**
	 * test09a
	 */
	private void test09a() {
		String updateSql = "UPDATE TEST SET NAME=:name WHERE ID=:id";
		String selectSql = "SELECT * FROM TEST WHERE ID=:id";

		Map<String, Object> param = new HashMap<String, Object>();
		param.put("id", 1001);
		param.put("name", "NAME 1001");
		
		Map<String, Object> expected = new HashMap<String, Object>();
		expected.put("id", getExpectedInteger(1001));
		expected.put("name", "NAME 1001");
		expected.put("kind", "1");
		expected.put("price", new BigDecimal("1001.01"));
		expected.put("updateTime", getExpectedTimestamp("2009-01-01"));
		
		testExecuteUpdate(updateSql, param, selectSql, expected);
	}

	/**
	 * test11
	 */
	@Test
	public void test11() {
		log.debug("");
		log.debug(this.getClass().getSimpleName() + "." + "test11()");

		String selectSql = "SELECT * FROM TEST WHERE ID < 1003 ORDER BY ID";
		TestA expected;
		TestA actual;
		
		try {
			SqlResultSet rs = executor.selectResultSet(selectSql);
			
			expected = new TestA();
			expected.setId(1001);
			expected.setName("NAME 1001");
			expected.setKind('1');
			expected.setPrice(new BigDecimal("1001.01"));
			expected.setUpdateTime(convertToDate("2009-01-01"));
			
			Assert.assertTrue(rs.next());
			actual = rs.getResult(TestA.class);
			Assert.assertEquals(expected, actual);
			
			expected = new TestA();
			expected.setId(1002);
			expected.setName("NAME 1002");
			expected.setKind('2');
			expected.setPrice(new BigDecimal("1002.02"));
			expected.setUpdateTime(convertToDate("2009-02-02"));
			
			Assert.assertTrue(rs.next());
			actual = rs.getResult(TestA.class);
			Assert.assertEquals(expected, actual);
			
			Assert.assertFalse(rs.next());
			
			rs.close();
		}
		catch (SQLException e) {
			log.error("exception", e);
			Assert.fail(e.getMessage());
		}
	}
	
}
