/**
 * 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.SQLException;
import java.util.Date;

import net.yher2.commons.io.Classpath;
import net.yher2.commons.lang.DateUtils;
import net.yher2.junit.db.data.ColumnData;
import net.yher2.junit.db.data.Header;
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.dbutils.DbUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 * @auther  hisaboh
 */
public class TestDataManagerTest extends DBTestCaseTest {
	Log log = LogFactory.getLog(this.getClass());
	Connection con;
	TestDataManager manager;
	Table table;
	Row validRow1;
	Row validRow2;
	Row emptyRow;
	Row invalidRow;
	Date date;

	/*
	 * @see TestCase#setUp()
	 */
	protected void setUp() throws Exception {
		super.setUp();
		date = new Date();
		table = new Table("PARENT");
		Header header[] = new Header[] {
				new Header("PARENT_ID", true),
				new Header("INT_VAL"),
				new Header("DBL_VAL"),
				new Header("VCR_VAL"),
				new Header("CHR_VAL"),
				new Header("DATE_VAL"),
				new Header("TIMESTAMP_VAL"),
				new Header("BOOL_VAL")
		};
		table.setHeader(header);
		validRow1 = new Row();
		validRow1.add(ColumnData.getInstance(new Integer(1)));
		validRow1.add(ColumnData.getInstance(new Integer(1)));
		validRow1.add(ColumnData.getInstance(new Double(0.9999)));
		validRow1.add(ColumnData.getInstance("hogehoge"));
		validRow1.add(ColumnData.getInstance("CHR"));
		validRow1.add(ColumnData.getInstance(date));
		validRow1.add(ColumnData.getInstance(date));
		validRow1.add(ColumnData.getInstance(new Boolean(true)));
		
		validRow2 = new Row();
		validRow2.add(ColumnData.getInstance(new Integer(2)));
		validRow2.add(ColumnData.getInstance(new Integer(2)));
		validRow2.add(ColumnData.getInstance(new Double(0.9999)));
		validRow2.add(ColumnData.getInstance("hogehoge"));
		validRow2.add(ColumnData.getInstance("CHR"));
		validRow2.add(ColumnData.getInstance(date));
		validRow2.add(ColumnData.getInstance(date));
		validRow2.add(ColumnData.getInstance(new Boolean(true)));
		
		emptyRow = new Row();
		emptyRow.add(ColumnData.getInstance(new Integer(3)));
		emptyRow.add(ColumnData.getInstance(null));
		emptyRow.add(ColumnData.getInstance(null));
		emptyRow.add(ColumnData.getInstance(null));
		emptyRow.add(ColumnData.getInstance(null));
		emptyRow.add(ColumnData.getInstance(null));
		emptyRow.add(ColumnData.getInstance(null));
		emptyRow.add(ColumnData.getInstance(null));

		invalidRow = new Row();
		invalidRow.add(ColumnData.getInstance(new Integer(4)));
		invalidRow.add(ColumnData.getInstance("hogehoge"));
		
		con = getConnection();
		manager = new TestDataManager(con);
	}

	/*
	 * @see TestCase#tearDown()
	 */
	protected void tearDown() throws Exception {
		con.rollback();
		super.tearDown();
		DbUtils.closeQuietly(con);
	}

	public void testInsertTable() throws Exception {
		table.addRow(validRow1);
		table.addRow(validRow2);
		manager.insert(table);
		
		Parent parent = getParent(con, 1);
		assertParent(validRow1, parent);
		
		parent = getParent(con, 2);
		assertParent(validRow2, parent);
	}
	
	public void testInsertTableEmpty() throws Exception {
		table.addRow(emptyRow);
		manager.insert(table);
		Parent parent = getParent(con, 3);
		assertEquals(3, parent.getParentId());
		assertEquals(0, parent.getDoubleValue(), 4);
		assertNull(parent.getVarcharValue());
		assertNull(parent.getCharValue());
		assertNull(parent.getDateValue());
		assertNull(parent.getTimestampValue());
		assertFalse(parent.isBooleanValue());
	}
	
	public void testInsertTableInvalid() throws Exception {
		table.addRow(invalidRow);
		try {
			manager.insert(table);
		} catch (SQLException e) {
			return;
		}
		fail();
	}

	public void testInsertExcel() throws Exception {
		Classpath path = new Classpath("net/yher2/junit/db/excel/DatabaseManagerTest.xls");
		ExcelReader reader = new ExcelReader(path);
		Table table[] = reader.read();
		
		manager.insertExcel(path);
		Parent parent1 = getParent(con, 1);
		Parent parent2 = getParent(con, 2);
		Parent parent3 = getParent(con, 3);
		
		Row row[] = table[0].getRow();
		assertParent(row[0], parent1);
		assertParent(row[1], parent2);
		assertParent(row[2], parent3);
		
		
		Child child1 = getChild(con, 1);
		Child child2 = getChild(con, 2);
		Child child3 = getChild(con, 3);
		
		row = table[1].getRow();
		assertChild(row[0], child1);
		assertChild(row[1], child2);
		assertChild(row[2], child3);
		
	}
	public void testDeleteTable() throws Exception {
		table.addRow(validRow1);
		table.addRow(validRow2);
		manager.insert(table);
		
		manager.delete(table);
		assertNull(getParent(con, 1));
		assertNull(getParent(con, 2));
	}
	public void testDeleteExcel() throws Exception {
		Classpath path = new Classpath("net/yher2/junit/db/excel/DatabaseManagerTest.xls");
		ExcelReader reader = new ExcelReader(path);
		manager.insertExcel(path);
		manager.deleteExcel(path);
		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()));
	}
}
