package test;

import java.io.File;
import java.io.FileInputStream;
//import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
//import java.sql.Types;

import org.dbunit.Assertion;
import org.dbunit.database.DatabaseConfig;
import org.dbunit.database.DatabaseConnection;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.ITable;
import org.dbunit.dataset.SortedTable;
import org.dbunit.dataset.xml.FlatXmlDataSetBuilder;
//import org.dbunit.ext.h2.H2DataTypeFactory;
//import org.dbunit.ext.oracle.OracleDataTypeFactory;
import org.dbunit.operation.DatabaseOperation;
import org.junit.jupiter.api.AfterAll;
import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.BeforeAll;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;

public class Basistests {

	private static Connection con = null;
	//private static String dbAdresse = "127.0.0.1";
	//private static String dbInstanz = "XE";
	private static IDatabaseConnection connDBU;

	private static void verbinden(String nutzer, String passwort)
			throws Exception {
//		DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
//		con = DriverManager.getConnection("jdbc:oracle:thin:@" + dbAdresse
//				+ ":1521:" + dbInstanz, nutzer, passwort);
		Class.forName("org.apache.derby.jdbc.ClientDriver")
			.getDeclaredConstructor()
			.newInstance();

	// mein DB-Ordner F:\workspaces\NetBeansWorkWiSe18\db
		con = DriverManager.getConnection(
			"jdbc:derby://localhost:1527/SpielereiDBUnit;create=true"
					, nutzer,passwort);
	}

	public static void verbindungTrennen() throws Exception {
		if (con != null) {
			con.close();
		}
	}

	@BeforeAll
	public static void setUpBeforeClass() throws Exception {
		verbinden("ich", "x");
		connDBU = new DatabaseConnection(con, null, true);
		//DatabaseConfig config = connDBU.getConfig();
		// config.setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new OracleDataTypeFactory());
		// config.setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new H2DataTypeFactory());
	}

	@AfterAll
	public static void tearDownAfterClass() throws Exception {
		verbindungTrennen();
	}

	@BeforeEach
	public void setUp() throws Exception {
		IDataSet dataSet = new FlatXmlDataSetBuilder()
				.build(new FileInputStream(".\\testdaten\\basisdaten.xml"));
		DatabaseOperation.CLEAN_INSERT.execute(connDBU, dataSet);
	}

	@AfterEach
	public void tearDown() throws Exception {
	}

	@Test
	public void testErfolgreichGehegeEinfuegen() throws Exception {
		con.createStatement()
	    	.execute("""
	    			INSERT INTO Gehege(GNr, GName, Flaeche)
						VALUES (4, 'Steppe', 40)""");
		IDataSet databaseDataSet = connDBU.createDataSet();
		ITable actualTable = databaseDataSet.getTable("Gehege");

		IDataSet expectedDataSet = new FlatXmlDataSetBuilder().build(new File(
				".\\testdaten\\test1.xml"));
		ITable expectedTable = expectedDataSet.getTable("Gehege");
		Assertion.assertEquals(expectedTable, actualTable);
	}
	
	@Test
	public void testGehegeMitVorhandenerNummer() {
		try{
		con.createStatement()
	    	.execute("""
	    			INSERT INTO Gehege(GNr, GName, Flaeche)  
				      VALUES (3, 'Steppe', 40)""");
		Assertions.fail();
		} catch (SQLException e){
			String erg = e.getMessage();
			Assertions.assertTrue(erg.contains("Constraint")); // Apache Derby
			//Assertions.assertTrue(erg.contains("constraint") && erg.contains("violated"));  // Oracle
		} catch (Exception e){
			Assertions.fail();
		}
	}
	
	@Test
	public void testTierMitNichtVorhandenerGattung() {
		try{
		con.createStatement()
	    	.execute("""
	    			INSERT INTO Tier(GNr, TName, Gattung)  
						   VALUES (3, 'Nemo', 'Fisch')""");
		Assertions.fail();
		} catch (SQLException e){
			//Assertions.assertTrue(e.getErrorCode() == 2291); // Oracle
		} catch (Exception e){
			Assertions.fail();
		}
	}
	
	@Test
	public void testNeueArtOk() throws Exception{
		con.createStatement()
    		.execute("INSERT INTO Art "
					+ "(Gattung, MinFlaeche) " 
					+ "VALUES ('Gnu',14)");
		tabellenvergleich("Art", ".\\testdaten\\test3.xml");
	}
	
	private void tabellenvergleich(String tabelle, String datei) throws Exception{
		IDataSet databaseDataSet = connDBU.createDataSet();
		ITable actualTable = databaseDataSet.getTable(tabelle);
		IDataSet expectedDataSet = new FlatXmlDataSetBuilder().build(new File(datei));
		ITable expectedTable = expectedDataSet.getTable(tabelle);
		Assertion.assertEquals(new SortedTable(expectedTable), new SortedTable(
				actualTable));	
	}
}
