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;

/* Test die PLSQL-Erweiterungen aus dem DB-Buch,
 * damit so nicht fuer Apache Derby geeignet.
 * Derby hat andere Möglichkeiten Trigger, Funktionen und
 * Prozeduren zu schreiben.
 */
public class PLSQLErweiterungentest {
	

	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();

		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 {
	}
	
	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));	
	}
		
	@Test
	public void testProcedureLangerName() throws Exception{
		CallableStatement stmt=con.prepareCall(
		          "BEGIN langerName('Hase'); END;");
		stmt.execute();
		tabellenvergleich("Tier", ".\\testdaten\\test2.xml");
	}

	private int anzahlTiere(String gattung) throws SQLException{
		CallableStatement stmt=con.prepareCall(
		          "{? = call anzahlTiere(?)}");
		      stmt.registerOutParameter(1,Types.INTEGER);
		      stmt.setString(2,gattung);
		      stmt.execute();
		     return stmt.getInt(1);
	}
	
	@Test
	public void testFunctionAnzahlTiere() throws Exception{
		Assertions.assertTrue(anzahlTiere("Baer") == 2);
		Assertions.assertTrue(anzahlTiere("Schaf") == 3);
		Assertions.assertTrue(anzahlTiere("Gnu") == 0);
		try {
			anzahlTiere("Hase");
		} catch (SQLException e){
			Assertions.assertTrue(e.getErrorCode() == 20300);
		} catch (Exception e){
			Assertions.fail();
		}	
	}
	
	@Test
	public void testTriggerNeueArtOk() throws Exception{
		con.createStatement()
    		.execute("""
    				INSERT INTO Art(Gattung, MinFlaeche)  
					  VALUES ('Gnu',14)""");
		tabellenvergleich("Art", ".\\testdaten\\test3.xml");
	}
	
	@Test
	public void testTriggerNeueArtNichtOk() throws Exception{
		try{
			con.createStatement()
	    		.execute("""
	    				INSERT INTO Art(Gattung, MinFlaeche) 
						  VALUES ('Elefant',15)""");
			Assertions.fail();
		} catch (SQLException e){
			//Assertions.assertTrue(e.getErrorCode() == 20999);
		} catch (Exception e){
			Assertions.fail();
		}	
	}
}
