package sample;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;


/**
 *
 * @author zamora
 * Prepared statement
 */
public class RequestSQL {
    public static class EmployeeSQL{
        private static Connection connection = null;
        private static PreparedStatement preparedStatement = null;
        private static ResultSet result = null;

        private static String url = "jdbc:mysql://192.168.2.2:3306/mydb";
        private static String user = "testuser";
        private static String password = "test623";

        private final String idField = "employee_id";
        private final String firstNameField = "first_name";
        private final String lastNameField = "last_name";
        private final String emailField = "email";
        private final String managerField = "manager";
        private final String availableAnnualLeavesField = "available_annual_leaves";
        private final String availableSickLeavesField = "available_sick_leaves";

        private static HashSet<Integer> results;

        public static boolean searchEmployee(String name){
            ArrayList<String> words = new ArrayList();
            results = new HashSet<Integer>();

            HashMap<Integer, Employee> employeeHashMap = new HashMap<Integer, Employee>();
            boolean found = false;

            String word = name.substring(0,name.indexOf(" "));
            int index = name.length()-1;
            while(!word.isEmpty()){
                words.add(word);
                name = name.substring(name.indexOf(" ") + 1) + " ";
                word = name.substring(0,name.indexOf(" "));
            }

            for (String s : words){
                checkName(s);
            }
            return found;
        }

        public static int checkName(String name){
            int found = 0;

            try{
                connection = DriverManager.getConnection(url, user, password);
                preparedStatement = connection.prepareStatement("SELECT "+
                        "* FROM hr.employees WHERE"+
                        " first_name = \""+name+"\" OR " +
                        " last_name = \"\"+name+\"\"");
                result = preparedStatement.executeQuery();

                while (result.next()) {
                    if (!results.add(result.getInt(idField))) {
                        return result.getInt(idField);
                    }
                }

            }catch(SQLException e){
                Logger logger = Logger.getLogger(EmployeeSQL.class.getName());
                logger.log(Level.SEVERE, e.getMessage(), e);
            }finally{
                try {
                    if (result != null) {
                        result.close();
                    }
                    if (preparedStatement != null) {
                        preparedStatement.close();
                    }
                    if (connection != null) {
                        connection.close();
                    }

                } catch (SQLException ex) {
                    Logger lgr = Logger.getLogger(EmployeeSQL.class.getName());
                    lgr.log(Level.WARNING, ex.getMessage(), ex);
                }
            }

            return -1;
        }

        public static boolean Insert(Employee employee){
            insert = false;
            try{
                connection = DriverManager.getConnection(url, user, password);
                preparedStatement = connection.prepareStatement("INSERT INTO "+
                        "Entrada_Salida_Carro VALUES (?,?,?,?)");

                preparedStatement.setNull(1, 0);
                preparedStatement.setTimestamp(2, datetime);
                preparedStatement.setInt(3, camera);
                preparedStatement.setString(4, plate);

                preparedStatement.executeUpdate();
                insert = true;

            }catch(SQLException e){
                Logger logger = Logger.getLogger(CheckPlate.class.getName());
                logger.log(Level.SEVERE, e.getMessage(), e);
                insert = false;
            }finally{
                try {
                    if (preparedStatement != null) {
                        preparedStatement.close();
                    }
                    if (connection != null) {
                        connection.close();
                    }
                } catch (SQLException ex) {
                    Logger lgr = Logger.getLogger(InsertRecord.class.getName());
                    lgr.log(Level.SEVERE, ex.getMessage(), ex);
                }
            }
            return insert;
        }

        public static boolean Insert(Employee employee){
            insert = false;
            try{
                connection = DriverManager.getConnection(url, user, password);
                preparedStatement = connection.prepareStatement("INSERT INTO "+
                        "Entrada_Salida_Carro VALUES (?,?,?,?)");

                preparedStatement.setNull(1, 0);
                preparedStatement.setTimestamp(2, datetime);
                preparedStatement.setInt(3, camera);
                preparedStatement.setString(4, plate);

                preparedStatement.executeUpdate();
                insert = true;

            }catch(SQLException e){
                Logger logger = Logger.getLogger(CheckPlate.class.getName());
                logger.log(Level.SEVERE, e.getMessage(), e);
                insert = false;
            }finally{
                try {
                    if (preparedStatement != null) {
                        preparedStatement.close();
                    }
                    if (connection != null) {
                        connection.close();
                    }
                } catch (SQLException ex) {
                    Logger lgr = Logger.getLogger(InsertRecord.class.getName());
                    lgr.log(Level.SEVERE, ex.getMessage(), ex);
                }
            }
            return insert;
        }
    }

}