package dgpena.siexample.persistence;

import static org.junit.Assert.*;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;

import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;

public class EmployeesTest extends SQLBasedTest {

	@Test
	public void testAddNewEmployee() throws SQLException {
		// insert a department previously with JDBC
		int deptId = insertADepartment();

		EntityManager em = emf.createEntityManager();
		
		Departments depts = new Departments(em);
		Department dept = depts.findById(deptId);
		
		Employee e = new Employee();
		e.setName("pepe");
		e.setDepartment(dept);
		

		Employees employees = new Employees(em);
		
		em.getTransaction().begin();
		
			employees.addNewEmployee(e);
			
			// ensure that bi-directional relation is always consistent
			assertEquals(1, dept.getEmployees().size());
			
		em.getTransaction().commit();

		int employeeId = e.getId();
		
		
		Statement statement = jdbcConnection.createStatement();
		ResultSet res = statement.executeQuery("SELECT * from Employee where id = "+employeeId);
		res.next();
		assertEquals(deptId, res.getInt("department_id"));
		assertEquals("pepe", res.getString("name"));
		
		
	}

	@Test
	public void testDeleteEmployeeWithProjectAssignment() throws SQLException {

		// insert an employee with JDBC
		int employeeId = insertAnEmployee();

		// create a project
		int projectId = insertAProject();

		// assign the employee to the project (which should be removed due to CascadeType.REMOVE
		// in Project.projectAssignments)
		Statement statement = jdbcConnection.createStatement();
		statement.executeUpdate("INSERT INTO ProjectAssignment(project_id, employee_id) values("+projectId+", "
				+employeeId+")", Statement
				.RETURN_GENERATED_KEYS);

		EntityManager em = emf.createEntityManager();

		Employees employees = new Employees(em);
		Employee e = employees.findById(employeeId);

		em.getTransaction().begin();
			employees.deleteEmployee(e);
		em.getTransaction().commit();

		statement = jdbcConnection.createStatement();
		ResultSet rs = statement.executeQuery("SELECT COUNT(*) as total FROM Employee e where e.id = "+employeeId);
		rs.next();
		assertEquals(0, rs.getInt("total"));
	}


	private int insertADepartment() throws SQLException {
		Statement statement = jdbcConnection.createStatement();
		statement.executeUpdate("INSERT INTO Department(name) values('finanzas')", Statement.RETURN_GENERATED_KEYS);
		return getLastInsertedId(statement);
	}

	private int insertAnEmployee() throws SQLException {
		Statement statement = jdbcConnection.createStatement();
		statement.executeUpdate("INSERT INTO Employee(name) values('pepe')", Statement.RETURN_GENERATED_KEYS);
		return getLastInsertedId(statement);
	}

	private int insertAProject() throws SQLException {
		Statement statement = jdbcConnection.createStatement();
		statement.executeUpdate("INSERT INTO Project(name) values('our best project')", Statement
				.RETURN_GENERATED_KEYS);
		return getLastInsertedId(statement);
	}

}