/**
 * Copyright (c) 2004, yher2 project
 * All rights reserved.
 * 
 * Redistribution and use in source and binary forms, with or without modification, 
 * are permitted provided that the following conditions are met:
 * 
 * * Redistributions of source code must retain the above copyright notice, 
 *   this list of conditions and the following disclaimer.
 * * Redistributions in binary form must reproduce the above copyright notice, 
 *   this list of conditions and the following disclaimer in the documentation 
 *   and/or other materials provided with the distribution.
 * * Neither the name of the nor the names of its contributors may be used to endorse or 
 *   promote products derived from this software without specific prior written permission.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND 
 * ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES 
 * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. 
 * IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, 
 * INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES 
 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; 
 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY 
 * OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) 
 * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF 
 * SUCH DAMAGE.
 */
package net.yher2.junit.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import net.yher2.commons.io.Classpath;
import net.yher2.commons.lang.DateUtils;
import net.yher2.commons.lang.ExtendResourceBundle;
import net.yher2.junit.db.data.ColumnData;
import net.yher2.junit.db.data.Row;
import net.yher2.junit.db.data.Table;
import net.yher2.junit.db.excel.ExcelReader;
import net.yher2.junit.db.test.data.Child;
import net.yher2.junit.db.test.data.Parent;

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 * @auther  hisaboh
 */
public class DBTestCaseTest extends DBTestCase {
	private static Log log = LogFactory.getLog(DBTestCaseTest.class);
	private static BasicDataSource dataSource;
	static {
		try {
			prepareDataSource();
			prepareDatabase();
		} catch (Exception e) {
			log.fatal("Failed to initialize", e);
		}
	}
	public void testInitialize() throws SQLException {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			con = getConnection();
			ps = con.prepareStatement("SELECT * FROM PARENT");
			rs = ps.executeQuery();
			assertFalse(rs.next());
			
			ps = con.prepareStatement("SELECT * FROM CHILD");
			rs = ps.executeQuery();
			assertFalse(rs.next());
		} finally {
			DbUtils.closeQuietly(rs);
			DbUtils.closeQuietly(ps);
			DbUtils.closeQuietly(con);
		}
	}
	
	public void testPrepare() throws Exception {
		Classpath path = new Classpath("net/yher2/junit/db/excel/DatabaseManagerTest.xls");
		prepare(path);
		ExcelReader reader = new ExcelReader(path);
		Table table[] = reader.read();
		Connection con = getConnection();
		
		Row row[] = table[0].getRow();
		assertParent(row[0], getParent(con, 1));
		assertParent(row[1], getParent(con, 2));
		assertParent(row[2], getParent(con, 3));
		
		row = table[1].getRow();
		assertChild(row[0], getChild(con, 1));
		assertChild(row[1], getChild(con, 2));
		assertChild(row[2], getChild(con, 3));
		
	}
	public void testClear() throws Exception {
		Classpath path = new Classpath("net/yher2/junit/db/excel/DatabaseManagerTest.xls");
		prepare(path);
		clear(path);
		Connection con = getConnection();
		assertNull(getParent(con, 1));
		assertNull(getParent(con, 2));
		assertNull(getParent(con, 3));

		assertNull(getChild(con, 1));
		assertNull(getChild(con, 2));
		assertNull(getChild(con, 3));
	}
	
	private void assertParent(Row row, Parent parent) {
		ColumnData column[] = row.getColumn(); 
		assertEquals((Integer)column[0].getValue(), new Integer(parent.getParentId()));
		assertEquals((Integer)column[1].getValue(), new Integer(parent.getIntValue()));
		assertEquals(((Double)column[2].getValue()).doubleValue(), parent.getDoubleValue(), 4);
		assertEquals(column[3].getValue(), parent.getVarcharValue());
		assertEquals(column[4].getValue(), parent.getCharValue());
		log.debug(column[5].getValue());
		log.debug(parent.getDateValue());
		assertEquals(DateUtils.toSqlDate((Date)column[5].getValue()), parent.getDateValue());
		assertEquals(column[6].getValue(), parent.getTimestampValue());
		assertEquals(column[7].getValue(), new Boolean(parent.isBooleanValue()));
	}
	private void assertChild(Row row, Child parent) {
		ColumnData column[] = row.getColumn(); 
		assertEquals((Integer)column[0].getValue(), new Integer(parent.getChildId()));
		assertEquals((Integer)column[1].getValue(), new Integer(parent.getParentId()));
		assertEquals((Integer)column[2].getValue(), new Integer(parent.getIntValue()));
		assertEquals(((Double)column[3].getValue()).doubleValue(), parent.getDoubleValue(), 4);
		assertEquals(column[4].getValue(), parent.getVarcharValue());
		assertEquals(column[5].getValue(), parent.getCharValue());
		assertEquals(DateUtils.toSqlDate((Date)column[6].getValue()), parent.getDateValue());
		assertEquals(column[7].getValue(), parent.getTimestampValue());
		assertEquals(column[8].getValue(), new Boolean(parent.isBooleanValue()));
	}
	
	protected void setUp() throws Exception {
		super.setUp();
	}
	protected void tearDown() throws Exception {
		super.tearDown();
		Connection con = null;
		PreparedStatement ps = null;
		try {
			con = getConnection();
			ps = con.prepareStatement("DELETE FROM CHILD");
			ps.executeUpdate();
			ps = con.prepareStatement("DELETE FROM PARENT");
			ps.executeUpdate();
		} finally {
			DbUtils.closeQuietly(ps);
			DbUtils.closeQuietly(con);
		}
	}
	private static void prepareDataSource() {
		ExtendResourceBundle resource = ExtendResourceBundle.getBundle("dbTestCase");
		dataSource = new BasicDataSource();
		dataSource.setDriverClassName(resource.getString("dbtest.db.driver"));
		dataSource.setUsername(resource.getString("dbtest.db.username"));
		dataSource.setPassword(resource.getString("dbtest.db.password"));
		dataSource.setUrl(resource.getString("dbtest.db.url"));
	}
	
	private static void prepareDatabase() throws SQLException {
		Connection con = null;
		PreparedStatement ps = null;
		try {
			con = dataSource.getConnection();
			ps = con.prepareStatement(
					"CREATE TABLE PARENT (" +
					"	PARENT_ID INTEGER PRIMARY KEY," +
					"	INT_VAL INTEGER," +
					"	DBL_VAL DOUBLE," +
					"	VCR_VAL VARCHAR(256)," +
					"	CHR_VAL CHAR(3)," +
					"	DATE_VAL DATE," +
					"	TIMESTAMP_VAL TIMESTAMP," +
					"	BOOL_VAL BOOLEAN" +
					")");
			ps.execute();
			
			ps = con.prepareStatement(
					"CREATE TABLE CHILD (" +
					"	CHILD_ID INTEGER PRIMARY KEY," +
					"	PARENT_ID INTEGER," +
					"	INT_VAL INTEGER," +
					"	DBL_VAL DOUBLE," +
					"	VCR_VAL VARCHAR(256)," +
					"	CHR_VAL CHAR(3)," +
					"	DATE_VAL DATE," +
					"	TIMESTAMP_VAL TIMESTAMP," +
					"	BOOL_VAL BOOLEAN," +
					"	FOREIGN KEY (PARENT_ID) REFERENCES PARENT (PARENT_ID)" +
					")");
			ps.execute();
		} finally {
			DbUtils.closeQuietly(ps);
			DbUtils.closeQuietly(con);
		}
	}
	
	protected Parent getParent(Connection con, int id) throws SQLException {
		String sql = 
			"SELECT " +
			"	PARENT_ID as parentId, " +
			"	INT_VAL as intvalue," +
			"	DBL_VAL as doubleValue," +
			"	VCR_VAL as varcharValue," +
			"	CHR_VAL as charValue," +
			"	DATE_VAL as dateValue," +
			"	TIMESTAMP_VAL as timestampValue," +
			"	BOOL_VAL as booleanValue" +
			" FROM PARENT" +
			" WHERE PARENT_ID = ?";
		QueryRunner queryRunner = new QueryRunner();
		List list = (List)queryRunner.query(con, sql, new Object[]{new Integer(id)}, new BeanListHandler(Parent.class));
		if (list.size() == 0) return null;
		return (Parent)list.get(0);
	}
	
	protected Child getChild(Connection con, int id) throws SQLException {
		String sql = 
			"SELECT " +
			"	CHILD_ID childId, " +
			"	PARENT_ID parentId, " +
			"	INT_VAL intvalue," +
			"	DBL_VAL doubleValue," +
			"	VCR_VAL varcharValue," +
			"	CHR_VAL charValue," +
			"	DATE_VAL dateValue," +
			"	TIMESTAMP_VAL timestampValue," +
			"	BOOL_VAL booleanValue" +
			" FROM Child" +
			" WHERE CHILD_ID = ?";
		QueryRunner queryRunner = new QueryRunner();
		List list = (List)queryRunner.query(con, sql, new Object[]{new Integer(id)}, new BeanListHandler(Child.class));
		if (list.size() == 0) return null;
		return (Child)list.get(0);
	}
	
	protected Connection getConnection() throws SQLException {
		return dataSource.getConnection();
	}
	
	protected void assertEquals(java.sql.Date expected, java.sql.Date actual) {
		java.util.Date expected2 = DateUtils.parse(DateUtils.getYear(expected), DateUtils.getMonth(expected), DateUtils.getDay(expected));
		java.util.Date actual2 = DateUtils.parse(DateUtils.getYear(actual), DateUtils.getMonth(actual), DateUtils.getDay(actual));
		assertEquals(expected2, actual2);
	}
}
