package model;

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


public class DBManager {
	private Connection conn;
	DBEmployee e = new DBEmployee();
	public DBManager() throws ClassNotFoundException, SQLException {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://localhost/employees", "root", "");
	}
	public void close() throws SQLException{
		conn.close();
	}
	public DBEmployee getEmployee(int id)throws SQLException{
		DBEmployee e = new DBEmployee();
		String sql = "select * from employees where emp_no = ?";
		PreparedStatement stmt = conn.prepareStatement(sql);
		stmt.setInt(1, id);
		ResultSet rs = stmt.executeQuery();
		if (rs.next()) {
			e.setId(id);
			e.setFirstname(rs.getString("first name"));
			e.setLastname(rs.getString("last name"));
			e.setBirthdate(rs.getDate("birth_date"));
			e.setPassword(rs.getString("password"));
			e.setHiredate(rs.getDate("hire_date"));
			e.setGender(rs.getString("gender"));
			java.util.Date d = new java.util.Date();
			stmt = conn.prepareStatement(sql);



			stmt.setDate(1, new java.sql.Date(d.getDate()));
			rs = stmt.executeQuery();
			rs.next();
			java.util.Date d2 = new java.util.Date(rs.getDate(1).getTime());
		}
		rs.close();
		stmt.close();
		return e;
	}


	public boolean logIn(String user, String pw)throws SQLException{
		//user ist vn und 3
		//password ist 7
		boolean b = false;
		String sql = "Select * from Employees where user = ? and pw = ? ";
		PreparedStatement stmt = conn.prepareStatement(sql);
		stmt.setString(1, user);
		stmt.setString(2, pw);
		ResultSet rs = stmt.executeQuery();
		while(rs.next()){
			e.setFirstname("first_name");
			e.setPassword("password");
			b = true;
		}
		rs.close();
		stmt.close();

		return b;

	}
	public DBEmployee getEmployee()throws SQLException{
		String sql = "select * from employees where emp_no = ?";
		PreparedStatement stmt = conn.prepareStatement(sql);
		ResultSet rs = stmt.executeQuery();
		if (rs.next()) {
			e.setFirstname(rs.getString("first name"));
			e.setLastname(rs.getString("last name"));
			e.setBirthdate(rs.getDate("birth_date"));
			e.setPassword(rs.getString("password"));
			e.setHiredate(rs.getDate("hire_date"));
			e.setGender(rs.getString("gender"));
		}
		rs.close();
		stmt.close();
		return e;
	}

	public DBEmployee search(String word) throws SQLException{
		DBEmployee e = new DBEmployee();
		String sql = "Select * from Employee where fist_name = ?";
		PreparedStatement stmt = conn.prepareStatement(sql);
		stmt.setString(1, word);
		ResultSet rs = stmt.executeQuery();
		while(rs.next()){
		String s =  rs.getString("first_name");
			if (word.equals(s)) {
				e.setId(rs.getInt("emp_no"));
				e.setFirstname(rs.getString("first name"));
				e.setLastname(rs.getString("last name"));
				e.setBirthdate(rs.getDate("birth_date"));
				e.setPassword(rs.getString("password"));
				e.setHiredate(rs.getDate("hire_date"));
			}
		}
		rs.close();
		stmt.close();
		return e;
	}

	public boolean checkDeptManager() throws SQLException{
		DBEmployee e = new DBEmployee();
		String sql = "select * from dept_manager where emp_no = (select * from employees where id = ?)";
		PreparedStatement stmt = conn.prepareStatement(sql);
		ResultSet rs = stmt.executeQuery();
			if (rs.next()) {
				e.setFirstname(rs.getString("first name"));
				e.setLastname(rs.getString("last name"));
				e.setBirthdate(rs.getDate("birth_date"));
				e.setPassword(rs.getString("password"));
				e.setHiredate(rs.getDate("hire_date"));
				e.setGender(rs.getString("gender"));
				rs.close();
				stmt.close();
				return true;

		}
		rs.close();
		stmt.close();
		return false;

	}
}