package first;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;

public class MySQLAccess {
    private Connection connection = null;
    private Statement statement = null;
    private PreparedStatement preparedStatement = null;
    private ResultSet resultSet = null;
    private HashMap<Integer, Employee> employeeHashMap = new HashMap<Integer, Employee>();
    private HashMap<Integer, LeaveRequest> leaveRequestsHashMap = new HashMap<Integer, LeaveRequest>();

    String sql;

    final String idField = "employee_id";
    final String firstNameField = "first_name";
    final String lastNameField = "last_name";
    final String emailField = "email";
    final String managerField = "manager";
    final String availableAnnualLeavesField = "available_annual_leaves";
    final String availableSickLeavesField = "available_sick_leaves";
    final String employeeIdField = "employee_id";
    final String startDateField = "start_date";
    final String endDateField = "end_date";
    final String returnDateField = "return_date";
    final String numberOfDaysField = "number_of_days";
    final String typeOfLeaveField = "type_of_leave";
    final String statusField = "status";
    final String managerRefusalCommentField = "manager_refusal_comment";
    final String hrRefusalCommentField = "hr_refusal_comment";


    public void readDataBase() throws Exception {
        try {
            // This will load the MySQL driver, each DB has its own driver
            Class.forName("com.mysql.jdbc.Driver");
            // Setup the connection with the DB
            connection = DriverManager
                    .getConnection("jdbc:mysql://localhost/hr?"
                            + "user=user1&password=user1");

            // Statements allow to issue SQL queries to the database
            statement = connection.createStatement();
            // Result set get the result of the SQL query
            sql = "SELECT * FROM hr.employees";
            sql = "SELECT * FROM hr.leave_requests";

            resultSet = statement.executeQuery(sql);
            System.out.println(getLeaveRequestsHashMap(resultSet));
//            writeResultSet(resultSet);


            System.out.println("All: " + employeeHashMap.toString());

        } catch (Exception e) {
            throw e;
        } finally {
            close();
        }

    }

    private HashMap<Integer, Employee> getEmployeeHashMap (ResultSet resultSet) throws SQLException{
        HashMap<Integer, Employee> employeeHashMap = new HashMap<Integer, Employee>();
        while (resultSet.next()){
            Employee employee = new Employee();
            employee.setId(resultSet.getInt(idField));
            employee.setFirstName(resultSet.getString(firstNameField));
            employee.setLastName(resultSet.getString(lastNameField));
            employee.setEmail(resultSet.getString(emailField));
            employee.setManager(resultSet.getString(managerField));
            employee.setAvailableAnnualLeaves(resultSet.getInt(availableAnnualLeavesField));
            employee.setAvailableSickLeaves(resultSet.getInt(availableSickLeavesField));
            employeeHashMap .put(employee.getId(), employee);
        }
        return employeeHashMap;
    }

    private HashMap<Integer, ArrayList<LeaveRequest>> getLeaveRequestsHashMap (ResultSet resultSet) throws SQLException{
        HashMap<Integer, ArrayList<LeaveRequest>> leaveRequestHashMap = new HashMap<Integer, ArrayList<LeaveRequest>>();
        while (resultSet.next()){
            LeaveRequest leaveRequest = new LeaveRequest();
            leaveRequest.setEmployeeId(resultSet.getInt(employeeIdField));
            leaveRequest.setStartDate(resultSet.getDate(startDateField));
            leaveRequest.setEndDate(resultSet.getDate(endDateField));
            leaveRequest.setReturnDate(resultSet.getDate(returnDateField));
            leaveRequest.setNumberOfDays(resultSet.getInt(numberOfDaysField));
            leaveRequest.setTypeOfLeave(resultSet.getString(typeOfLeaveField));
            leaveRequest.setStatus(resultSet.getString(statusField));
            leaveRequest.setManagerRefusalComment(resultSet.getString(managerRefusalCommentField));
            leaveRequest.setHrRefusalComment(resultSet.getString(hrRefusalCommentField));

            if(leaveRequestHashMap.containsKey(leaveRequest.getEmployeeId())){
                leaveRequestHashMap.get(leaveRequest.getEmployeeId()).add(leaveRequest);
            }
            else{
                leaveRequestHashMap.put(leaveRequest.getEmployeeId(), new ArrayList<LeaveRequest>());
                leaveRequestHashMap.get(leaveRequest.getEmployeeId()).add(leaveRequest);
            }
        }
        return leaveRequestHashMap;
    }

    private void writeResultSet(ResultSet resultSet) throws SQLException {
        // ResultSet is initially before the first data set
        while (resultSet.next()) {
            // It is possible to get the columns via name
            // also possible to get the columns via the column number
            // which starts at 1
            // e.g. resultSet.getSTring(2);
            Employee employee = new Employee();
            employee.setId(resultSet.getInt(idField));
            employee.setFirstName(resultSet.getString(firstNameField));
            employee.setLastName(resultSet.getString(lastNameField));
            employee.setEmail(resultSet.getString(emailField));
            employee.setManager(resultSet.getString(managerField));
            employee.setAvailableAnnualLeaves(resultSet.getInt(availableAnnualLeavesField));
            employee.setAvailableSickLeaves(resultSet.getInt(availableSickLeavesField));
            employeeHashMap.put(employee.getId(), employee);
            System.out.println(employee.getId() + employee.getFirstName() + employee.getLastName()
                    + employee.getEmail() + employee.getManager() + employee.getAvailableAnnualLeaves()
                    + employee.getAvailableSickLeaves());
        }
    }

    // You need to close the resultSet
    private void close() {
        try {
            if (resultSet != null) {
                resultSet.close();
            }

            if (statement != null) {
                statement.close();
            }

            if (connection != null) {
                connection.close();
            }
        } catch (Exception e) {
            System.out.println(e.toString());
        }
    }

    public PreparedStatement insertStatement(String username, String email, String webpage, java.sql.Date date, String summary, String comments){
        PreparedStatement preparedStatement = null;
        try{
            preparedStatement = connection.prepareStatement("INSERT INTO feedback.comments VALUES (DEFAULT, ?, ?, ?, ?, ?, ?)");
            preparedStatement.setString(1, username);
            preparedStatement.setString(2, email);
            preparedStatement.setString(3, webpage);
            preparedStatement.setDate(4, date);
            preparedStatement.setString(5, summary);
            preparedStatement.setString(6, comments);
        }
        catch (Exception e){
            System.out.println("Error en insert: " + e.toString());
        }
        return preparedStatement;
    }

}