package at.ac.tuwien.sepm.assignment.groupphase.missioncontrol.dao;

import at.ac.tuwien.sepm.assignment.groupphase.exception.ElementNotFoundException;
import at.ac.tuwien.sepm.assignment.groupphase.exception.PersistenceException;
import at.ac.tuwien.sepm.assignment.groupphase.missioncontrol.dto.Employee;
import at.ac.tuwien.sepm.assignment.groupphase.missioncontrol.dto.Employee.EducationLevel;
import at.ac.tuwien.sepm.assignment.groupphase.util.Helper;
import at.ac.tuwien.sepm.assignment.groupphase.util.JdbcTestCase;
import java.io.InputStream;
import java.time.LocalDate;
import java.util.Set;
import org.dbunit.Assertion;
import org.dbunit.dataset.DataSetException;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.ITable;
import org.dbunit.dataset.filter.DefaultColumnFilter;
import org.dbunit.dataset.xml.FlatXmlDataSetBuilder;
import org.dbunit.util.fileloader.FlatXmlDataFileLoader;
import org.junit.Assert;
import org.junit.Test;

public class EmployeeDAOTest extends JdbcTestCase {

    private EmployeeDAO employeePersistence;

    public EmployeeDAOTest() {
        employeePersistence = new EmployeeDatabaseDAO(getJdbcConnectionManager());
    }

    @Override
    protected IDataSet getDataSet() throws DataSetException {
        InputStream res =
                getClass()
                        .getClassLoader()
                        .getResourceAsStream("EmployeePersistenceTestBaseData.xml");
        return new FlatXmlDataSetBuilder().build(res);
    }

    @Test
    public void testListEmployees() throws PersistenceException {
        Set<Employee> employees = employeePersistence.list();

        Employee empOne =
                Employee.builder()
                        .id(1)
                        .name("Adam")
                        .birthday(LocalDate.parse("2010-10-10"))
                        .educationLevel(EducationLevel.RS)
                        .isDriver(true)
                        .isPilot(false)
                        .build();

        Employee empTwo =
                Employee.builder()
                        .id(2)
                        .name("Max")
                        .birthday(LocalDate.parse("1990-11-11"))
                        .educationLevel(EducationLevel.NFS)
                        .isDriver(false)
                        .isPilot(false)
                        .build();

        Employee empThree =
                Employee.builder()
                        .id(3)
                        .name("Lisa")
                        .birthday(LocalDate.parse("1999-10-16"))
                        .educationLevel(EducationLevel.NKI)
                        .isDriver(true)
                        .isPilot(false)
                        .build();

        Assert.assertTrue(employees.contains(empOne));
        Assert.assertTrue(employees.contains(empTwo));
        Assert.assertTrue(employees.contains(empThree));
        Assert.assertEquals(3, employees.size());
    }

    @Test
    public void testEmployeeListNoElement() throws PersistenceException {
        Set<Employee> employees = employeePersistence.list();

        Employee empOne =
                Employee.builder()
                        .id(10)
                        .name("Adam")
                        .birthday(LocalDate.parse("2010-10-10"))
                        .educationLevel(EducationLevel.RS)
                        .isDriver(true)
                        .isPilot(false)
                        .build();

        Assert.assertFalse(employees.contains(empOne));
    }

    Employee validEmployee =
            Employee.builder()
                    .name("Testperson")
                    .birthday(LocalDate.parse("2010-11-11"))
                    .educationLevel(EducationLevel.NA)
                    .isDriver(true)
                    .isPilot(false)
                    .build();

    @Test
    public void testAddValidEmployee_EmployeeVersion() throws Exception {

        employeePersistence.add(validEmployee);

        String[] excludedColumnsEmployeeVersion = new String[] {"ID"};
        String tableEmployeeVersion = "EMPLOYEEVERSION";

        // load actual and expected data set
        IDataSet actualDataSet = getConnection().createDataSet();
        IDataSet expectedDataSet =
                new FlatXmlDataFileLoader().load("/testAddValidEmployee_expected.xml");

        // extract employeeVersion table
        ITable actualEmployeeVersionTable = actualDataSet.getTable(tableEmployeeVersion);
        ITable expectedEmployeeVersionTable = expectedDataSet.getTable(tableEmployeeVersion);

        // exclude 'id' column as it is an autogenerated value
        ITable actualFilteredTable =
                DefaultColumnFilter.excludedColumnsTable(
                        actualEmployeeVersionTable, excludedColumnsEmployeeVersion);
        ITable expectedFilteredTable =
                DefaultColumnFilter.excludedColumnsTable(
                        expectedEmployeeVersionTable, excludedColumnsEmployeeVersion);

        Assertion.assertEquals(expectedFilteredTable, actualFilteredTable);
    }

    @Test
    public void testAddValidEmployee_Employee() throws Exception {

        employeePersistence.add(validEmployee);

        String[] excludedColumnsEmployee = new String[] {"VERSION", "ID"};
        String tableEmployee = "EMPLOYEE";

        // load actual and expected data set
        IDataSet actualDataSet = getConnection().createDataSet();
        IDataSet expectedDataSet =
                new FlatXmlDataFileLoader().load("/testAddValidEmployee_expected.xml");

        // extract employee table
        ITable actualEmployeeTable = actualDataSet.getTable(tableEmployee);
        ITable expectedEmployeeTable = expectedDataSet.getTable(tableEmployee);

        // exclude 'version' as it is an autogenerated value
        ITable actualFilteredEmpTable =
                DefaultColumnFilter.excludedColumnsTable(
                        actualEmployeeTable, excludedColumnsEmployee);
        ITable expectedFilteredEmpTable =
                DefaultColumnFilter.excludedColumnsTable(
                        expectedEmployeeTable, excludedColumnsEmployee);

        Assertion.assertEquals(expectedFilteredEmpTable, actualFilteredEmpTable);
    }

    @Test
    public void testAddValidEmployee_Join() throws Exception {

        employeePersistence.add(validEmployee);

        String[] excludedColumns = new String[] {"E_VERSION", "V_ID", "E_ID"};
        String table = "EMP_JOIN";
        String expectedXmlDataFileName = "testAddValidEmployeeJoin_expected.xml";

        String sqlJoinEmployeeVersion =
                "SELECT e.id AS E_ID, v.name AS V_NAME, v.birthday AS V_BIRTHDAY, "
                        + "v.educationLevel as V_EDUCATIONLEVEL, "
                        + "v.isDriver AS V_ISDRIVER, v.isPilot AS V_ISPILOT "
                        + "FROM Employee e "
                        + "JOIN EmployeeVersion v ON e.version = v.id";

        ITable actualFilteredJoinData =
                Helper.getActualFilteredQueryTableData(
                        getConnection(), table, sqlJoinEmployeeVersion, excludedColumns);

        ITable expectedFilteredJoinData =
                Helper.getExpectedFilteredTableData(
                        table, excludedColumns, expectedXmlDataFileName);

        Assertion.assertEquals(expectedFilteredJoinData, actualFilteredJoinData);
    }

    Employee validUpdateEmployee =
            Employee.builder()
                    .id(3)
                    .name("Lisa")
                    .birthday(LocalDate.parse("1999-10-16"))
                    .educationLevel(EducationLevel.NKA)
                    .isDriver(true)
                    .isPilot(true)
                    .build();

    @Test
    public void testUpdateValidEmployee_EmployeeVersion() throws Exception {

        employeePersistence.update(validUpdateEmployee);

        String[] excludedColumnsEmployeeVersion = new String[] {"ID"};
        String tableEmployeeVersion = "EMPLOYEEVERSION";

        ITable actualTableData =
                Helper.getActualFilteredTableData(
                        getConnection(), tableEmployeeVersion, excludedColumnsEmployeeVersion);
        ITable expedtedTableData =
                Helper.getExpectedFilteredTableData(
                        tableEmployeeVersion,
                        excludedColumnsEmployeeVersion,
                        "testUpdateValidEmployee_expected.xml");

        Assertion.assertEquals(expedtedTableData, actualTableData);
    }

    @Test
    public void testUpdateValidEmployee_Employee() throws Exception {

        employeePersistence.update(validUpdateEmployee);

        String[] excludedColumns = new String[] {"VERSION"};
        String tableName = "EMPLOYEE";
        String expectedXmlDataFileName = "testUpdateValidEmployee_expected.xml";

        ITable actualTableData =
                Helper.getActualFilteredTableData(getConnection(), tableName, excludedColumns);

        ITable expectedTableData =
                Helper.getExpectedFilteredTableData(
                        tableName, excludedColumns, expectedXmlDataFileName);

        Assertion.assertEquals(expectedTableData, actualTableData);
    }

    @Test(expected = ElementNotFoundException.class)
    public void testUpdateNonExistingEmployee()
            throws PersistenceException, ElementNotFoundException {

        Employee nonExistentEmployee =
                Employee.builder()
                        .id(1000)
                        .name("Lisa")
                        .birthday(LocalDate.parse("1999-10-16"))
                        .educationLevel(EducationLevel.NKA)
                        .isDriver(true)
                        .isPilot(true)
                        .build();

        employeePersistence.update(nonExistentEmployee);
    }
}