package jp.sf.amateras.mirage;

import static org.mockito.Mockito.*;

import java.sql.Connection;
import java.sql.DriverManager;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import jp.sf.amateras.mirage.annotation.PrimaryKey;
import jp.sf.amateras.mirage.annotation.PrimaryKey.GenerationType;
import jp.sf.amateras.mirage.dialect.HyperSQLDialect;
import jp.sf.amateras.mirage.provider.ConnectionProvider;
import jp.sf.amateras.mirage.util.IOUtil;
import junit.framework.TestCase;

public class SqlManagerImplTest extends TestCase {

	private static final String SQL_PREFIX = "jp/sf/amateras/mirage/";

	private SqlManager sqlManager = new SqlManagerImpl();
	private Connection conn;

	@Override
	protected void setUp() throws Exception {
		super.setUp();
		Class.forName("org.hsqldb.jdbc.JDBCDriver");
		conn = DriverManager.getConnection("jdbc:hsqldb:mem:mirage_test", "sa", "");

		ConnectionProvider connectionProvider = mock(ConnectionProvider.class);
		when(connectionProvider.getConnection()).thenReturn(conn);

		sqlManager.setConnectionProvider(connectionProvider);
		sqlManager.setDialect(new HyperSQLDialect());

		executeMultipleStatement(SQL_PREFIX + "SqlManagerImplTest_setUp.sql");
	}

	@Override
	protected void tearDown() throws Exception {
		super.tearDown();
		executeMultipleStatement(SQL_PREFIX + "SqlManagerImplTest_tearDown.sql");
		conn.close();
	}

	private void executeMultipleStatement(String sqlPath) throws Exception {
		ClassLoader cl = Thread.currentThread().getContextClassLoader();
		byte[] bytes = IOUtil.readStream(cl.getResourceAsStream(sqlPath));
		String sql = new String(bytes, "UTF-8");
		for(String statement: sql.split(";")){
			if(statement.trim().length() > 0){
				sqlManager.executeUpdateBySql(statement);
			}
		}
	}

	public void testGetSingleResultByMap() throws Exception {
		Book book = new Book();
		book.bookName = "Mirage in Action";
		book.author = "Naoki Takezoe";
		book.price = 20;

		sqlManager.insertEntity(book);

		Map<String, String> param = new HashMap<String, String>();
		param.put("bookName", "Mirage in Action");

		@SuppressWarnings("unchecked")
		Map<String, Object> map = sqlManager.getSingleResult(
				Map.class,
				SQL_PREFIX + "SqlManagerImplTest_selectByBookName.sql",
				param);

		assertEquals("Mirage in Action", map.get("bookName"));
		assertEquals("Naoki Takezoe", map.get("author"));
		assertEquals("20", map.get("price"));
	}

	public void testGetSingleResultByInteger() throws Exception {
		Book book = new Book();
		book.bookName = "Mirage in Action";
		book.author = "Naoki Takezoe";
		book.price = 20;

		sqlManager.insertEntity(book);
		sqlManager.insertEntity(book);

		Integer count = sqlManager.getSingleResult(
				Integer.class,
				SQL_PREFIX + "SqlManagerImplTest_countBook.sql");

		assertEquals(2, count.intValue());
	}

	public void testGetCount() throws Exception {
		Book book1 = new Book();
		book1.bookName = "Mirage in Action";
		book1.author = "Naoki Takezoe";
		book1.price = 20;

		Book book2 = new Book();
		book2.bookName = "Mirage in Action";
		book2.author = "Naoki Takezoe";
		book2.price = 20;

		sqlManager.insertEntity(book1);
		sqlManager.insertEntity(book2);
		assertEquals(new Long(0), book1.bookId);
		assertEquals(new Long(1), book2.bookId);

		Map<String, String> param = new HashMap<String, String>();
		param.put("bookName", "Mirage in Action");

		int count = sqlManager.getCount(
				SQL_PREFIX + "SqlManagerImplTest_selectByBookName.sql",
				param);

		assertEquals(2, count);
	}

	public void testInsertEntity() throws Exception {
		Book book = new Book();
		book.bookName = "Mirage in Action";
		book.author = "Naoki Takezoe";
		book.price = 20;

		sqlManager.insertEntity(book);
		assertEquals(new Long(0), book.bookId);

		BookParam param = new BookParam();
		param.bookName = "Mirage in Action";

		List<Book> bookList = sqlManager.getResultList(
				Book.class,
				SQL_PREFIX + "SqlManagerImplTest_selectByBookName.sql",
				param);

		assertEquals(1, bookList.size());
		assertEquals("Mirage in Action", bookList.get(0).bookName);
		assertEquals("Naoki Takezoe", bookList.get(0).author);
		assertEquals(new Integer(20), bookList.get(0).price);
	}

	public void testInsertEntity_Sequence() throws Exception {
		UserInfo userInfo1 = new UserInfo();
		userInfo1.userName = "Naoki Takezoe";

		UserInfo userInfo2 = new UserInfo();
		userInfo2.userName = "Coharu Takezoe";

		sqlManager.insertEntity(userInfo1);
		sqlManager.insertEntity(userInfo2);
		assertEquals(new Long(0), userInfo1.userId);
		assertEquals(new Long(1), userInfo2.userId);

		List<UserInfo> userList = sqlManager.getResultListBySql(
				UserInfo.class,
				"SELECT * FROM USER_INFO ORDER BY USER_ID");

		assertEquals(2, userList.size());
		assertEquals(new Long(0), userList.get(0).userId);
		assertEquals("Naoki Takezoe", userList.get(0).userName);
		assertEquals(new Long(1), userList.get(1).userId);
		assertEquals("Coharu Takezoe", userList.get(1).userName);
	}

	public void testInsertEntityWithNullProperty() throws Exception {
		Book book = new Book();
		book.bookName = "Mirage in Action";
		book.author = "Naoki Takezoe";
		book.price = null;

		sqlManager.insertEntity(book);
		assertEquals(new Long(0), book.bookId);

		BookParam param = new BookParam();
		param.bookName = "Mirage in Action";

		List<Book> bookList = sqlManager.getResultList(
				Book.class,
				SQL_PREFIX + "SqlManagerImplTest_selectByBookName.sql",
				param);

		assertEquals(1, bookList.size());
		assertEquals("Mirage in Action", bookList.get(0).bookName);
		assertEquals("Naoki Takezoe", bookList.get(0).author);
		assertNull(bookList.get(0).price);
	}

	public void testInsertBatchArray() throws Exception {
		Book book1 = new Book();
		book1.bookName = "Mirage in Action";
		book1.author = "Naoki Takezoe";
		book1.price = 20;

		Book book2 = new Book();
		book2.bookName = "Mastering Seasar2";
		book2.author = "Naoki Takezoe";
		book2.price = 25;

		int result = sqlManager.insertBatch(book1, book2);
		assertEquals(2, result);
		assertEquals(new Long(0), book1.bookId);
		assertEquals(new Long(1), book2.bookId);

		List<Book> bookList = sqlManager.getResultList(
				Book.class,
				SQL_PREFIX + "SqlManagerImplTest_selectByBookName.sql",
				new BookParam());

		assertEquals(2, bookList.size());

		assertEquals("Mirage in Action", bookList.get(0).bookName);
		assertEquals("Naoki Takezoe", bookList.get(0).author);
		assertEquals(new Integer(20), bookList.get(0).price);

		assertEquals("Mastering Seasar2", bookList.get(1).bookName);
		assertEquals("Naoki Takezoe", bookList.get(1).author);
		assertEquals(new Integer(25), bookList.get(1).price);
	}

	public void testInsertBatchList() throws Exception {
		Book book1 = new Book();
		book1.bookName = "Mirage in Action";
		book1.author = "Naoki Takezoe";
		book1.price = 20;

		Book book2 = new Book();
		book2.bookName = "Mastering Seasar2";
		book2.author = "Naoki Takezoe";
		book2.price = 25;

		List<Book> books = new ArrayList<Book>();
		books.add(book1);
		books.add(book2);

		int result = sqlManager.insertBatch(books);
		assertEquals(2, result);

		List<Book> bookList = sqlManager.getResultList(
				Book.class,
				SQL_PREFIX + "SqlManagerImplTest_selectByBookName.sql",
				new BookParam());

		assertEquals(2, bookList.size());

		assertEquals("Mirage in Action", bookList.get(0).bookName);
		assertEquals("Naoki Takezoe", bookList.get(0).author);
		assertEquals(new Integer(20), bookList.get(0).price);

		assertEquals("Mastering Seasar2", bookList.get(1).bookName);
		assertEquals("Naoki Takezoe", bookList.get(1).author);
		assertEquals(new Integer(25), bookList.get(1).price);
	}


	public void testUpdateEntity() throws Exception {
		Book book = new Book();
		book.bookName = "Mirage in Action";
		book.author = "Naoki Takezoe";
		book.price = 20;

		sqlManager.insertEntity(book);

		BookParam param = new BookParam();
		param.bookName = "Mirage in Action";

		book = sqlManager.getSingleResult(
				Book.class,
				SQL_PREFIX + "SqlManagerImplTest_selectByBookName.sql",
				param);

		book.price = 100;
		sqlManager.updateEntity(book);

		List<Book> bookList = sqlManager.getResultList(
				Book.class,
				SQL_PREFIX + "SqlManagerImplTest_selectByBookName.sql");

		assertEquals(1, bookList.size());
		assertEquals("Mirage in Action", bookList.get(0).bookName);
		assertEquals("Naoki Takezoe", bookList.get(0).author);
		assertEquals(new Integer(100), bookList.get(0).price);
	}

	public void testDeleteEntity() throws Exception {
		Book book = new Book();
		book.bookName = "Mirage in Action";
		book.author = "Naoki Takezoe";
		book.price = 20;

		sqlManager.insertEntity(book);

		BookParam param = new BookParam();
		param.bookName = "Mirage in Action";

		book = sqlManager.getSingleResult(
				Book.class,
				SQL_PREFIX + "SqlManagerImplTest_selectByBookName.sql",
				param);

		sqlManager.deleteEntity(book);

		book = sqlManager.getSingleResult(
				Book.class,
				SQL_PREFIX + "SqlManagerImplTest_selectByBookName.sql",
				param);

		assertNull(book);
	}

	public void testFindEntity(){
		Book book = new Book();
		book.bookName = "Mirage in Action";
		book.author = "Naoki Takezoe";
		book.price = 20;

		sqlManager.insertEntity(book);

		book = sqlManager.findEntity(Book.class, 0);
		assertEquals("Mirage in Action", book.bookName);
		assertEquals("Naoki Takezoe", book.author);
		assertEquals(new Integer(20), book.price);

		book = sqlManager.findEntity(Book.class, 1);
		assertNull(book);
	}

	public void testIterate() throws Exception {
		{
			Book book = new Book();
			book.bookName = "Mirage in Action";
			book.author = "Naoki Takezoe";
			book.price = 20;

			sqlManager.insertEntity(book);
		}
		{
			Book book = new Book();
			book.bookName = "Click in Action";
			book.author = "Bob Schellink";
			book.price = 30;

			sqlManager.insertEntity(book);
		}

		Integer result = sqlManager.iterate(
				Book.class,
				new IterationCallback<Book, Integer>() {

					private int result;

					public Integer iterate(Book entity) {
						result = result + entity.price;
						return result;
					}
				},
				SQL_PREFIX + "SqlManagerImplTest_selectByBookName.sql");

		assertEquals(50, result.intValue());
	}

	public void testCall() throws Exception {
		try {
			sqlManager.executeUpdate(SQL_PREFIX + "SqlManagerImplTest_testCall_setUp.sql");

			NewBookParam newBookParam = new NewBookParam();
			newBookParam.bookName = "Seasar2徹底入門";
			newBookParam.author = "Naoki Takezoe";
			newBookParam.price = 3990;
			sqlManager.call("NEW_BOOK", newBookParam);

			Book book = sqlManager.findEntity(Book.class, 0);
			assertEquals("Seasar2徹底入門", book.bookName);
			assertEquals("Naoki Takezoe", book.author);
			assertEquals(3990, book.price.intValue());

		} finally {
			sqlManager.executeUpdate(SQL_PREFIX + "SqlManagerImplTest_testCall_tearDown.sql");
		}
	}

	public static class BookParam {
		public String bookName;
	}

	public static class Book {
		@PrimaryKey(generationType=GenerationType.IDENTITY)
		public Long bookId;
		public String bookName;
		public String author;
		public Integer price;
	}

	public static class NewBookParam {
		public String bookName;
		public String author;
		public Integer price;
	}

	public static class UserInfo {
		@PrimaryKey(generationType=GenerationType.SEQUENCE, generator="USER_INFO_USER_ID_SEQ")
		public Long userId;
		public String userName;
	}

}
