package database;

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

import beans.Employee;
import beans.Employees;

public class DatabaseService {

	public int create(Employee e) throws SQLException {
		// connection data
		String dbUrl = "jdbc:derby:C:\\Users\\jordan\\MyDB";
	    String user = "user";
		String pass = "derby";

		// three objects used in every database operation. Notice the import java.sql.* is needed

		Connection myConn = null;
		Statement myStmt = null;
		ResultSet myRs = null;

		try {
			// 1. Get a connection to database

			myConn = DriverManager.getConnection(dbUrl, user, pass);

			System.out.println("Database conntection to " + dbUrl + "successful! a" + user + "\n");


			// 2. create a statement
			myStmt = myConn.createStatement();


			// 3. Insert a new employee

			System.out.println("Inserting a new employee to database");

			int rowsAffected = myStmt.executeUpdate("insert into bigcompanydatabase.employees " + "(last_name, first_name, email, department, salary)" + " values " + "('"+e.getLast_name()+"', '"+e.getFirst_name()+"', '"+e.getEmail()+"', '"+e.getDepartment()+"', "+e.getSalary()+")");

			System.out.println(rowsAffected + " items were inserted");

			// 4. Verify this by getting a list of employees

			myRs = myStmt.executeQuery("select * from bigcompanydatabase.employees order by last_name");

			System.out.println("Here is the new list of employees");

			// 5. Process the result set

			while (myRs.next()) {
				System.out.println(myRs.getString("last_name") + ", " + myRs.getString("first_name"));
			}

			return rowsAffected;
		}


		catch (Exception exc)
		{
			System.out.println("Error connection to" + dbUrl);
			exc.printStackTrace();

		}

		finally {
			if (myRs !=null)
			{
				myRs.close();
			}
			if (myStmt !=null)
			{
				myStmt.close();
			}
			if (myConn !=null)
			{
				myConn.close();
			}

		}

		return 0;

	}

	public int remove(int id) throws SQLException {

		// connection data
		String dbUrl = "jdbc:derby:C:\\Users\\jordan\\MyDB";
	    String user = "user";
		String pass = "derby";

		// three objects used in every database operation. Notice the import java.sql.* is needed

		Connection myConn = null;
		Statement myStmt = null;
		ResultSet myRs = null;

		try {
			// 1. Get a connection to database

			myConn = DriverManager.getConnection(dbUrl, user, pass);


			// 2. create a statement
			myStmt = myConn.createStatement();

			// Call helper method to display the employee's information

			System.out.println("BEFORE THE DELETE");
			//displayEmployee(myConn, "Donald", "Trump");


			System.out.println("\nDELETING THE EMPLOYEE: "+id+"\n");

			int rowsAffected = myStmt.executeUpdate("delete from bigcompanydatabase.employees "+
			"where ID="+id);

			System.out.println(rowsAffected+" rows deleted\n");

			System.out.println("AFTER THE DELETE");
			//displayEmployee(myConn, "Donald", "Trump");


			return rowsAffected;

		} catch (Exception e) {
			e.printStackTrace();
		}
		finally {

			if (myRs != null) {
				myRs.close();
			}
			if (myStmt != null) {
				myStmt.close();
			}
			if (myConn != null) {
				myConn.close();
			}
		}
		return 0;

	}

	public int update(Employee e, int id) throws SQLException {



		// connection data
		String dbUrl = "jdbc:derby:C:\\Users\\jordan\\MyDB";
		String user = "user";
		String pass = "derby";

		// three objects used in every database operation. Notice the import java.sql.*
		// is needed

		Connection myConn = null;
		Statement myStmt = null;
		ResultSet myRs = null;

		try {
			// 1. Get a connection to database

			myConn = DriverManager.getConnection(dbUrl, user, pass);

			System.out.println("Database conntection to " + dbUrl + "successful! a" + user + "\n");

			// 2. create a statement
			myStmt = myConn.createStatement();

			// Call helper method to display the employee's information

			System.out.println("BEFORE THE UPDATE");
			//displayEmployee(myConn, "Carl", "Adams");

			// Update the employee
			System.out.println("\n EXECUTING THE UPDATE FOR: "+e.getFirst_name()+" "+e.getLast_name()+"\n");

			int rowsAffected = myStmt.executeUpdate(
					"update bigcompanydatabase.employees" + " set email='"+e.getEmail()+"', first_name='"+e.getFirst_name()+"',"
							+ "last_name='"+e.getLast_name()+"', department='"+e.getDepartment()+"', salary="+e.getSalary()
							+ " where ID="+id);

			System.out.println(rowsAffected + "rows changed");

			// Call helper method to display the employees information

			System.out.println("AFTER THE UPDATE");
			//displayEmployee(myConn, "Carl", "Adams");
		}
		catch (Exception exc) {
			exc.printStackTrace();

		}

		finally {
			if (myRs != null) {
				myRs.close();
			}
			if (myStmt != null) {
				myStmt.close();
			}
			if (myConn != null) {
				myConn.close();
			}
		}


		return 0;
	}


	public Employees findAll() throws SQLException {
		Employees employees = new Employees();


		// connection data
		String dbUrl = "jdbc:derby:C:\\Users\\jordan\\MyDB";
	    String user = "user";
		String pass = "derby";

		// three objects used in every database operation. Notice the import java.sql.* is needed

		Connection myConn = null;
		Statement myStmt = null;
		ResultSet myRs = null;
		try {
			// 1. Get a connection to database

			myConn = DriverManager.getConnection(dbUrl, user, pass);

			System.out.println("Database conntection to " + dbUrl + "successful! a" + user + "\n");


			// 2. create a statement
			myStmt = myConn.createStatement();



			// 3. Execute SQL query
			myRs = myStmt.executeQuery("Select * from bigcompanydatabase.employees");


			// 4. Pricess the result set
			while (myRs.next())
			{
				System.out.println(myRs.getString("first_name") + ", " + myRs.getString("last_name") + ", " + myRs.getString("email") + ", "+ myRs.getString("department") + ", " + myRs.getDouble("salary"));


				employees.add(new Employee(myRs.getInt("ID"), myRs.getString("first_name"), myRs.getString("last_name"),
						myRs.getString("email"), myRs.getString("department"),  myRs.getDouble("salary")));

			}
		}


		catch (Exception exc)
		{
			System.out.println("Error connection to" + dbUrl);
			exc.printStackTrace();

		}

		finally {
			if (myRs !=null)
			{
				myRs.close();
			}
			if (myStmt !=null)
			{
				myStmt.close();
			}
			if (myConn !=null)
			{
				myConn.close();
			}
		}


		return employees;
	}


}