/**
   * Modifies a record in the Database Server using the provided modify request.
   *
   * @param request The modify request.
   * @return The result of the operation.
   * @throws ErrorResultException If the result code indicates that the request failed for some
   *     reason.
   * @throws SQLException If the SQL query has an invalid format.
   * @throws NullPointerException If {@code request} was {@code null}, or if a corresponding mapping
   *     value could not be found in the mapping component.
   */
  @Override
  public Result modify(final ModifyRequest request) {
    Result r;
    try {
      // Split up the DN the of the request.
      final DN DN = request.getName();
      final RDN rDN = DN.rdn();
      final String filterAttributeName = rDN.getFirstAVA().getAttributeType().getNameOrOID();
      final String filterAttributeValue = rDN.getFirstAVA().getAttributeValue().toString();
      final RDN OU = DN.parent(1).rdn();
      final String OUName = OU.getFirstAVA().getAttributeValue().toString();
      final String baseDN = DN.parent(2).toString();

      // Search mapping for the corresponding table and column names.
      final String tableName = jdbcm.getTableNameFromMapping(baseDN, OUName);

      if (tableName == null)
        throw new NullPointerException(
            "JDBC Error: Could not find matching table name for OU: '"
                + OUName
                + "' in DN: '"
                + baseDN
                + "'. Please check if mapping was succesful.");
      final String columnName =
          jdbcm.getColumnNameFromMapping(tableName, baseDN, OUName, filterAttributeName);

      if (columnName == null)
        throw new NullPointerException(
            "JDBC Error: Could not find matching column name for attribute: '"
                + filterAttributeName
                + "'. Please check if mapping was succesful.");

      // Get attribute and modificationtype for each modification.
      // Search mapping for the corresponding column name for the modification attribute.
      final List<Modification> modificationList = request.getModifications();
      final ListIterator<Modification> listIter = modificationList.listIterator();
      String modificationString = "";

      while (listIter.hasNext()) {
        final Modification modification = listIter.next();
        final ModificationType modificationType = modification.getModificationType();
        final Attribute modificationAttribute = modification.getAttribute();
        final String modificationAttributeName =
            modificationAttribute.getAttributeDescription().toString();
        final String modificationColumnName =
            jdbcm.getColumnNameFromMapping(tableName, baseDN, OUName, modificationAttributeName);

        if (modificationColumnName == null)
          throw new NullPointerException(
              "JDBC Error: Could not find matching column name for attribute: '"
                  + modificationAttributeName
                  + "'. Please check if mapping was succesful.");
        String modificationAttributeValue = "";

        if (modificationType == ModificationType.ADD) {
          final Iterator<ByteString> iter = modificationAttribute.iterator();

          int counter = 0;
          while (iter.hasNext()) {
            if (counter > 0) modificationAttributeValue = modificationAttributeValue.concat(", ");
            modificationAttributeValue = modificationAttributeValue.concat(iter.next().toString());
            counter++;
          }
          final Object classType = jdbcm.getTableColumnDataType(tableName, modificationColumnName);

          if (classType == Integer.class)
            modificationAttributeValue =
                "(CASE WHEN ("
                    + modificationColumnName
                    + " = 0) THEN ' "
                    + modificationAttributeValue
                    + "' ELSE concat("
                    + modificationColumnName
                    + ", ', "
                    + modificationAttributeValue
                    + "') END)";
          else
            modificationAttributeValue =
                "(CASE WHEN ("
                    + modificationColumnName
                    + " = 'Default Value') THEN ' "
                    + modificationAttributeValue
                    + "' ELSE concat("
                    + modificationColumnName
                    + ", ', "
                    + modificationAttributeValue
                    + "') END)";
        } else if (modificationType == ModificationType.REPLACE) {
          final Iterator<ByteString> iter = modificationAttribute.iterator();

          while (iter.hasNext()) {
            modificationAttributeValue = "'" + iter.next().toString() + "'";
          }
        } else {
          final boolean nullable = jdbcm.getTableColumnNullable(tableName, modificationColumnName);

          if (nullable == false)
            throw new SQLException("Cannot delete data from not-nullable column.");
          final Object classType = jdbcm.getTableColumnDataType(tableName, modificationColumnName);

          if (classType == Integer.class)
            modificationAttributeValue = "'" + Integer.toString(0) + "'";
          else modificationAttributeValue = "'Default Value'";
        }
        modificationString =
            modificationString.concat(
                modificationColumnName + "=" + modificationAttributeValue + ", ");
      }
      modificationString = modificationString.substring(0, modificationString.length() - 2);

      // Build the SQL query.
      final Statement st = connection.createStatement();
      final String sql =
          "UPDATE "
              + tableName
              + " SET "
              + modificationString
              + " WHERE "
              + columnName
              + "='"
              + filterAttributeValue
              + "'";
      st.executeUpdate(sql);
      r = Responses.newResult(ResultCode.SUCCESS);
    } catch (SQLException e) {
      System.out.println(e.toString());
      r = Responses.newResult(ResultCode.OPERATIONS_ERROR);
      r.setCause(e);
    } catch (NullPointerException e) {
      System.out.println(e.toString());
      r = Responses.newCompareResult(ResultCode.OPERATIONS_ERROR);
      r.setCause(e);
    }
    return r;
  }
  /**
   * Converts the LDAP filter to an SQL filter.
   *
   * @param filter The filter of the search request.
   * @param baseDN The top domain name.
   * @param OUName The name of the organizational unit.
   * @return The converted SQL filter.
   * @throws NullPointerException If the result code indicates that the request failed for some
   *     reason.
   * @throws SQLException If the SQL query has an invalid format.
   * @throws NullPointerException If {@code filter}, {@code baseDN} or {@code OUName} was {@code
   *     null}, or if a corresponding mapping value could not be found in the mapping component.
   */
  private String convertSearchFilter(final Filter filter, final String baseDN, String OUName) {
    try {
      String filterString = filter.toString();
      String convertedFilterString = "";

      if (filterString.isEmpty() || filterString.contains("objectClass=*")) return "";
      int stringIndex = 0;
      int subStringCount = 0;

      // Set the amount of subfilters if operators are present.
      while (filterString.charAt(stringIndex) == '(') {
        if (filterString.charAt(stringIndex + 2) == '(') {
          subStringCount++;
        }
        stringIndex += 2;
      }

      if (subStringCount == 0) {
        // Replace escape characters.
        String subString = filterString.substring(1, filterString.length() - 1);
        subString = subString.replace("\\02a", "*");
        subString = subString.replace("\\028", "(");
        subString = subString.replace("\\029", ")");

        String[] subStringSplitter;

        if (subString.contains("<=")) subStringSplitter = subString.split("<=");
        else if (subString.contains(">=")) subStringSplitter = subString.split(">=");
        else subStringSplitter = subString.split("=");

        final String filterAttributeName = subStringSplitter[0];
        String filterColumnName = null;
        ;
        Object columnDataType = null;

        // Search mapping for the corresponding table and column names.
        if (OUName.isEmpty()) {
          final List<String> OUList = jdbcm.getOrganizationalUnits(baseDN);

          for (Iterator<String> iter = OUList.iterator(); iter.hasNext(); ) {
            OUName = iter.next();
            final String tableName = jdbcm.getTableNameFromMapping(baseDN, OUName);

            if (tableName == null) continue;
            filterColumnName =
                jdbcm.getColumnNameFromMapping(tableName, baseDN, OUName, filterAttributeName);

            if (filterColumnName == null) continue;
            else columnDataType = jdbcm.getTableColumnDataType(tableName, filterColumnName);
            break;
          }

          if (filterColumnName == null)
            throw new NullPointerException(
                "JDBC Error: Could not find matching column name for attribute: '"
                    + filterAttributeName
                    + "'. Please check if mapping was succesful.");
        } else {
          final String tableName = jdbcm.getTableNameFromMapping(baseDN, OUName);

          if (tableName == null)
            throw new NullPointerException(
                "JDBC Error: Could not find matching table name for OU: '"
                    + OUName
                    + "' in DN: '"
                    + baseDN
                    + "'. Please check if mapping was succesful.");
          filterColumnName =
              jdbcm.getColumnNameFromMapping(tableName, baseDN, OUName, filterAttributeName);

          if (filterColumnName == null)
            throw new NullPointerException(
                "JDBC Error: Could not find matching column name for attribute: '"
                    + filterAttributeName
                    + "'. Please check if mapping was succesful.");
          columnDataType = jdbcm.getTableColumnDataType(tableName, filterColumnName);
        }

        // Set values to right format.
        if (columnDataType != Integer.class) {
          String filterAttributeValue = subStringSplitter[1];
          String filterColumnValue = "'" + filterAttributeValue + "'";
          filterAttributeValue = filterAttributeValue.replace("*", "\\*");
          filterAttributeValue = filterAttributeValue.replace("(", "\\(");
          filterAttributeValue = filterAttributeValue.replace(")", "\\)");

          if (filterColumnValue.length() > 3 && filterColumnValue.contains("*")) {
            filterColumnValue = filterColumnValue.replace("*", "%");

            subString = subString.replace("=", " like ");
          }
          subString = subString.replaceFirst(filterAttributeValue, filterColumnValue);
        }
        subString = subString.replaceFirst(filterAttributeName, filterColumnName);
        convertedFilterString = convertedFilterString.concat(subString);
      } else {
        int subStringStartIndex = 2 * subStringCount;
        int subStringEndIndex = 0;
        final String[] subStrings = new String[subStringCount];
        final char[] operationChars = new char[subStringCount];

        // Separate operation characters and subfilters.
        while (subStringStartIndex > 0) {
          final char operationChar = filterString.charAt(subStringStartIndex - 1);
          subStringEndIndex = filterString.indexOf("))") + 1;
          String subString = filterString.substring(subStringStartIndex, subStringEndIndex);
          subString = subString.replace("()", "");

          operationChars[subStringCount - subStringStartIndex / 2] = operationChar;
          subStrings[subStringCount - subStringStartIndex / 2] = subString;

          final String replaceString =
              filterString.substring(subStringStartIndex - 1, subStringEndIndex);
          filterString = filterString.replace(replaceString, "");

          subStringStartIndex -= 2;
        }

        // Handle the not operator.
        for (int i = 0; i < subStringCount; i++) {
          final char operationChar = operationChars[i];

          if (operationChar == '!') {
            String subString = subStrings[i];

            if (subString.isEmpty()) {
              subString = subStrings[i - 1];
              subString = subString.replace(">=", ">");
              subString = subString.replace("<=", "<");
              subString = subString.replace("=", "!=");
              subString = subString.replace(">", ">=");
              subString = subString.replace("<", "<=");
              subStrings[i - 1] = subString;
            } else {
              subString = subString.replace(">=", ">");
              subString = subString.replace("<=", "<");
              subString = subString.replace("=", "!=");
              subString = subString.replace(">", ">=");
              subString = subString.replace("<", "<=");
              subStrings[i] = subString;
            }
          }
        }
        boolean multipleSubStrings = false;

        if (subStringCount > 1) multipleSubStrings = true;

        // Convert each subfilter with corresponding operator.
        for (int i = 0; i < subStringCount; i++) {
          final char operationChar = operationChars[i];
          String operationString = "";

          if (operationChar == '&') {
            operationString = " AND ";
          } else if (operationChar == '|') {
            operationString = " OR ";
          }

          String subString = subStrings[i];
          if (subString.isEmpty()) continue;

          subString = subString.substring(1, subString.length() - 1);
          String[] subStringSplitter = subString.split("\\)\\(");

          for (int j = 0; j < subStringSplitter.length; j++) {
            String subStringFilter = subStringSplitter[j];
            final String[] subStringFilterSplitter;

            subStringFilter = subStringFilter.replace("\\02a", "*");
            subStringFilter = subStringFilter.replace("\\028", "(");
            subStringFilter = subStringFilter.replace("\\029", ")");

            if (subStringFilter.contains("!="))
              subStringFilterSplitter = subStringFilter.split("!=");
            else if (subStringFilter.contains("<="))
              subStringFilterSplitter = subStringFilter.split("<=");
            else if (subStringFilter.contains(">="))
              subStringFilterSplitter = subStringFilter.split(">=");
            else subStringFilterSplitter = subStringFilter.split("=");

            final String filterAttributeName = subStringFilterSplitter[0];
            String filterColumnName = null;
            ;
            Object columnDataType = null;

            // Search mapping for the corresponding column name for each filter attribute.
            if (OUName.isEmpty()) {
              final List<String> OUList = jdbcm.getOrganizationalUnits(baseDN);

              for (Iterator<String> iter = OUList.iterator(); iter.hasNext(); ) {
                OUName = iter.next();
                final String tableName = jdbcm.getTableNameFromMapping(baseDN, OUName);

                if (tableName == null) continue;
                filterColumnName =
                    jdbcm.getColumnNameFromMapping(tableName, baseDN, OUName, filterAttributeName);

                if (filterColumnName == null) continue;
                else columnDataType = jdbcm.getTableColumnDataType(tableName, filterColumnName);
                break;
              }
              if (filterColumnName == null)
                throw new NullPointerException(
                    "JDBC Error: Could not find matching column name for attribute: '"
                        + filterAttributeName
                        + "'. Please check if mapping was succesful.");
            } else {
              final String tableName = jdbcm.getTableNameFromMapping(baseDN, OUName);

              if (tableName == null)
                throw new NullPointerException(
                    "JDBC Error: Could not find matching table name for OU: '"
                        + OUName
                        + "' in DN: '"
                        + baseDN
                        + "'. Please check if mapping was succesful.");
              filterColumnName =
                  jdbcm.getColumnNameFromMapping(tableName, baseDN, OUName, filterAttributeName);

              if (filterColumnName == null)
                throw new NullPointerException(
                    "JDBC Error: Could not find matching column name for attribute: '"
                        + filterAttributeName
                        + "'. Please check if mapping was succesful.");
              columnDataType = jdbcm.getTableColumnDataType(tableName, filterColumnName);
            }

            // Handle the like operator.
            if (columnDataType != Integer.class) {
              String filterAttributeValue = subStringFilterSplitter[1];
              String filterColumnValue = "'" + filterAttributeValue + "'";
              filterAttributeValue = filterAttributeValue.replace("*", "\\*");
              filterAttributeValue = filterAttributeValue.replace("(", "\\(");
              filterAttributeValue = filterAttributeValue.replace(")", "\\)");

              if (filterColumnValue.length() > 3 && filterColumnValue.contains("*")) {
                filterColumnValue = filterColumnValue.replace("*", "%");

                if (subStringFilter.contains("!="))
                  subStringFilter = subStringFilter.replace("!=", " not like ");
                else subStringFilter = subStringFilter.replace("=", " like ");
              }
              subStringFilter =
                  subStringFilter.replaceFirst(filterAttributeValue, filterColumnValue);
            }
            subStringFilter = subStringFilter.replaceFirst(filterAttributeName, filterColumnName);

            if (j != 0 || i != 0)
              convertedFilterString = convertedFilterString.concat(operationString);
            convertedFilterString = convertedFilterString.concat(subStringFilter);
          }

          if (multipleSubStrings && i < subStringCount - 1)
            convertedFilterString = "(" + convertedFilterString + ")";
        }
      }
      return convertedFilterString;
    } catch (NullPointerException e) {
      return "Err:" + e.getMessage();
    }
  }
  /**
   * Adds a record to the Database Server using the provided add request.
   *
   * @param request The add request.
   * @return The result of the operation.
   * @throws ErrorResultException If the result code indicates that the request failed for some
   *     reason.
   * @throws SQLException If the SQL query has an invalid format.
   * @throws NullPointerException If {@code request} was {@code null}, or if a corresponding mapping
   *     value could not be found in the mapping component.
   */
  @Override
  public Result add(final AddRequest request) throws ErrorResultException {
    Result r;
    try {
      // Split up the DN the of the request.
      final DN DN = request.getName();
      final RDN rDN = DN.rdn();
      final String rDNName = rDN.getFirstAVA().getAttributeType().getNameOrOID();
      final String rDNValue = rDN.getFirstAVA().getAttributeValue().toString();
      final RDN OU = DN.parent().rdn();
      final String organizationalUnitName = OU.getFirstAVA().getAttributeValue().toString();
      final String baseDN = DN.parent(2).toString();

      // Search mapping for the corresponding table and column names.
      final String tableName = jdbcm.getTableNameFromMapping(baseDN, organizationalUnitName);

      if (tableName == null)
        throw new NullPointerException(
            "JDBC Error: Could not find matching table name for OU: '"
                + organizationalUnitName
                + "' in DN: '"
                + baseDN
                + "'. Please check if mapping was succesful.");
      final String rDNColumnName =
          jdbcm.getColumnNameFromMapping(tableName, baseDN, organizationalUnitName, rDNName);

      if (rDNColumnName == null)
        throw new NullPointerException(
            "JDBC Error: Could not find matching column name for attribute: '"
                + rDNName
                + "'. Please check if mapping was succesful.");
      final Map<String, Object> columnValuesMap = new HashMap<String, Object>();
      final Iterable<Attribute> attributesCollection = request.getAllAttributes();
      final Iterator<Attribute> attributeIter = attributesCollection.iterator();

      // Search mapping for the corresponding column name for each attribute.
      while (attributeIter.hasNext()) {
        final Attribute att = attributeIter.next();
        final Iterator<ByteString> valueIter = att.iterator();
        final String attributeName = att.getAttributeDescriptionAsString();

        if (attributeName == "objectClass") continue;
        final String columnName =
            jdbcm.getColumnNameFromMapping(
                tableName, baseDN, organizationalUnitName, attributeName);

        if (columnName == null)
          throw new NullPointerException(
              "JDBC Error: Could not find matching column name for attribute: '"
                  + attributeName
                  + "'. Please check if mapping was succesful.");
        String columnValue = "";

        while (valueIter.hasNext()) {
          columnValue = columnValue.concat(valueIter.next().toString());
        }
        columnValuesMap.put(columnName, columnValue);
      }

      // Build the SQL values String.
      final ArrayList<String> columnList = jdbcm.getTableColumns(tableName);
      String columnNamesString = " (" + rDNColumnName;
      String columnValuesString = " ('" + rDNValue + "'";

      for (int i = 0; i < columnList.size(); i++) {
        final String columnName = columnList.get(i);

        if (columnNamesString.contains(columnName)) continue;
        final Object columnDataType = jdbcm.getTableColumnDataType(tableName, columnName);
        Object columnValue = columnValuesMap.get(columnName);

        if (columnValue == null) {
          boolean columnNullable = jdbcm.getTableColumnNullable(tableName, columnName);

          if (columnNullable) continue;

          if (columnDataType.equals(Integer.class)) columnValue = "0";
          else columnValue = "Default Value";
        }

        if (columnDataType.equals(Integer.class))
          columnValue = Integer.parseInt(columnValue.toString());
        columnNamesString = columnNamesString.concat(", " + columnName);
        columnValuesString = columnValuesString.concat(", '" + columnValue + "'");
      }
      columnNamesString = columnNamesString.concat(")");
      columnValuesString = columnValuesString.concat(")");

      // Build the SQL query.
      final Statement st = connection.createStatement();
      final String sql =
          "INSERT INTO " + tableName + columnNamesString + " VALUES" + columnValuesString;
      st.executeUpdate(sql);
      r = Responses.newResult(ResultCode.SUCCESS);
    } catch (SQLException e) {
      System.out.println(e.toString());
      r = Responses.newResult(ResultCode.OPERATIONS_ERROR);
      r.setCause(e);
    } catch (NullPointerException e) {
      System.out.println(e.toString());
      r = Responses.newResult(ResultCode.OPERATIONS_ERROR);
      r.setCause(e);
    }
    return r;
  }