Getter.java

package dbProcs;

import de.mkammerer.argon2.Argon2;
import de.mkammerer.argon2.Argon2Factory;
import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Locale;
import java.util.ResourceBundle;
import javax.sql.rowset.CachedRowSet;
import javax.sql.rowset.RowSetProvider;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.json.JSONArray;
import org.json.JSONObject;
import org.owasp.encoder.Encode;
import servlets.Register;
import utils.ModulePlan;
import utils.ScoreboardStatus;

/**
 * Used to retrieve information from the Database <br>
 * <br>
 * This file is part of the Security Shepherd Project.
 *
 * <p>The Security Shepherd project is free software: you can redistribute it and/or modify it under
 * the terms of the GNU General Public License as published by the Free Software Foundation, either
 * version 3 of the License, or (at your option) any later version.<br>
 *
 * <p>The Security Shepherd project is distributed in the hope that it will be useful, but WITHOUT
 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR
 * PURPOSE. See the GNU General Public License for more details.<br>
 *
 * <p>You should have received a copy of the GNU General Public License along with the Security
 * Shepherd project. If not, see <http://www.gnu.org/licenses/>.
 *
 * @author Mark
 */
public class Getter {

  private static final Logger log = LogManager.getLogger(Getter.class);

  /** Used for scoreboards / progress bars */
  private static final int widthOfUnitBar = 11; // px

  private static final int fieldTrainingCap = 45;

  private static final int privateCap = 80;

  private static final int corporalCap = 105;

  private static final int sergeantCap = 130;

  private static final int lieutenantCap = 145;

  private static final int majorCap = 175;

  private static final int admiralCap = 999; // everything above Major is Admiral

  /**
   * Safety cap on the number of rows materialized by {@link #getClassInfo}, {@link
   * #getPlayersByClass}, and {@link #getAdmins}. These methods return a {@link
   * javax.sql.rowset.CachedRowSet}, which holds every row in memory. Realistic workloads are well
   * under this limit (tens to a few hundred per call); the cap exists to fail fast on a runaway
   * query rather than OOM the JVM. Tracked for proper bounded-collection conversion in #839.
   */
  private static final int MAX_ROWSET_ROWS = 10_000;

  /**
   * This method hashes the user submitted password and sends it to the database. The database does
   * the rest of the work, including Brute Force prevention.
   *
   * @param userName The submitted user name to be used in authentication process
   * @param password The submitted password in plain text to be used in authentication
   * @return A string array made up of nothing or information to be consumed by the initiating
   *     authentication process.
   */
  public static String[] authUser(String ApplicationRoot, String userName, String password) {
    log.debug("$$$ Getter.authUser $$$");
    log.debug("userName = " + userName);

    // Phase 1: Fetch user record (short DB hold, ~1-5ms)
    String userId;
    String dbUserName;
    String dbHash;
    String userRole;
    int badLoginCount;
    boolean tempPassword;
    String classId;
    Timestamp suspendedUntil;
    String loginType;
    boolean tempUsername;
    try (Connection conn = Database.getCoreConnection(ApplicationRoot);
        PreparedStatement prestmt =
            conn.prepareStatement(
                "SELECT userId, userName, userPass, userRole, badLoginCount, tempPassword, classId,"
                    + " suspendedUntil, loginType, tempUsername FROM `users` WHERE userName = ?")) {
      prestmt.setString(1, userName);
      try (ResultSet userResult = prestmt.executeQuery()) {
        if (userResult.next()) {
          log.debug("User Found");
          userId = userResult.getString(1);
          dbUserName = userResult.getString(2);
          dbHash = userResult.getString(3);
          userRole = userResult.getString(4);
          badLoginCount = userResult.getInt(5);
          tempPassword = userResult.getBoolean(6);
          classId = userResult.getString(7);
          suspendedUntil = userResult.getTimestamp(8);
          loginType = userResult.getString(9);
          tempUsername = userResult.getBoolean(10);
        } else {
          log.debug("User did not exist");
          log.debug("$$$ End authUser $$$");
          return null;
        }
      }
    } catch (SQLException e) {
      log.fatal("authUser failed: " + e.toString());
      throw new RuntimeException(e);
    }
    // Connection released — all user data extracted into local variables

    // Fail-fast: reject suspended and SSO users before expensive Argon2 work
    if (!"login".equals(loginType)) {
      log.debug("User is SSO user, can't login with password!");
      return null;
    }

    Timestamp currentTime = new Timestamp(System.currentTimeMillis());
    if (suspendedUntil != null && suspendedUntil.after(currentTime)) {
      return null;
    }

    // Phase 2: Verify password (CPU-bound Argon2, no DB connection held)
    log.debug("Verifying hash");
    Argon2 argon2 = Argon2Factory.create();
    boolean userVerified = argon2.verify(dbHash, password.toCharArray());

    if (!userVerified) {
      log.debug("Hash did not match, authentication failed");
      log.debug("$$$ End authUser $$$");
      return null;
    }

    // Phase 3: Post-verification DB updates (short DB hold if needed)
    log.debug("Hash matches");

    if (!dbUserName.equalsIgnoreCase(userName)) {
      log.fatal(
          "User Name used ("
              + userName
              + ") and User Name retrieved ("
              + dbUserName
              + ") were not the Same. Nulling Result");
      return null;
    }

    log.debug("User '" + userName + "' has logged in");

    if (badLoginCount > 0) {
      log.debug("Clearing Bad Login History");
      try (Connection conn = Database.getCoreConnection(ApplicationRoot);
          CallableStatement callstmt = conn.prepareCall("call userBadLoginReset(?)")) {
        callstmt.setString(1, userId);
        callstmt.execute();
        log.debug("userBadLoginReset executed!");
      } catch (SQLException e) {
        log.fatal("Could not reset bad login count: " + e.toString());
        throw new RuntimeException(e);
      }
    }

    String[] result = new String[6];
    result[0] = userId;
    result[1] = dbUserName;
    result[2] = userRole;
    result[3] = Boolean.toString(tempPassword);
    result[4] = classId;
    result[5] = Boolean.toString(tempUsername);

    log.debug("$$$ End authUser $$$");
    return result;
  }

  /**
   * This method hashes the user submitted password and sends it to the database. The database does
   * the rest of the work, including Brute Force prevention.
   *
   * @param ApplicationRoot The current running context of an application
   * @param userName The submitted user name to be used in authentication process
   * @param ssoName
   * @param password The submitted password in plain text to be used in authentication
   * @return A string array made up of nothing or information to be consumed by the initiating
   *     authentication process.
   */
  public static String[] authUserSSO(
      String ApplicationRoot, String classId, String userName, String ssoName, String userRole) {

    log.debug("$$$ Getter.authUserSSO $$$");

    log.debug("ssoName = " + ssoName);
    log.debug("userName = " + userName);

    String[] result = new String[6];

    String userID = new String();
    String newUsername = null;

    String defaultClass = Register.getDefaultClass();

    boolean userFound = false;

    boolean isTempUsername = false;

    try {
      // Phase 1: See if the user already exists, and capture suspension data if so.
      // Each phase opens its own pooled connection in try-with-resources so the
      // connection is never held across the Setter.userCreateSSO call (Phase 2).
      Timestamp suspendedUntil = null;

      try (Connection conn = Database.getCoreConnection(ApplicationRoot);
          PreparedStatement prestmt =
              conn.prepareStatement(
                  "SELECT suspendedUntil FROM `users` WHERE ssoName = ? AND loginType='saml'")) {
        prestmt.setString(1, ssoName);
        log.debug("Gathering userFind ResultSet");
        try (ResultSet userResult = prestmt.executeQuery()) {
          log.debug("Opening Result Set from userResult");
          if (userResult.next()) {
            // User found if a row is in the database
            userFound = true;
            log.debug("User Found");
            suspendedUntil = userResult.getTimestamp(1);
          } else {
            userFound = false;
          }
        }
      }

      if (!userFound) {
        // Phase 2: User wasn't found, enroll them in database. No pooled
        // connection is held here; Setter.userCreateSSO borrows its own.

        boolean userCreated = false;

        log.debug("User did not exist, create it from SSO data");

        try {

          if (defaultClass.isEmpty()) {
            log.debug("Adding player to database, with null classId");
            newUsername = Setter.userCreateSSO(ApplicationRoot, null, userName, ssoName, userRole);
          } else // defaultClass is not empty, so It must be set to a class!
          {
            log.debug("Adding player to database, to class " + defaultClass);
            newUsername =
                Setter.userCreateSSO(ApplicationRoot, defaultClass, userName, ssoName, userRole);
          }

          if (newUsername == null) {
            userCreated = false;
          } else {
            userCreated = true;
          }

          userName = newUsername;

        } catch (SQLException e) {
          String message =
              "Could not create user "
                  + userName
                  + " with ssoName "
                  + ssoName
                  + " via SSO: "
                  + e.toString();
          log.fatal(message);
          throw new RuntimeException(message);
        }

        if (!userCreated) {
          String message =
              "Could not create user " + userName + " with ssoName " + ssoName + " via SSO";
          log.fatal(message);
          throw new RuntimeException(message);
        }

        log.debug("User created");

      } else {

        log.debug("Getting suspension data");

        // Get current system time
        Timestamp currentTime = new Timestamp(System.currentTimeMillis());

        if (suspendedUntil.after(currentTime)) {
          // User is suspended
          log.debug("User is suspended");

          result = null;
          return result;
        }
      }

      // Phase 3: Find the generated userID and username by asking the database.
      try (Connection conn = Database.getCoreConnection(ApplicationRoot);
          PreparedStatement prestmt =
              conn.prepareStatement(
                  "SELECT userId, userName, classID, tempUsername FROM `users` WHERE ssoName = ?"
                      + " AND loginType='saml'")) {
        prestmt.setString(1, ssoName);
        log.debug("Gathering userResult ResultSet");
        try (ResultSet userResult = prestmt.executeQuery()) {
          log.debug("Opening user list result set");
          if (userResult.next()) {
            userFound = true;
            log.debug("User Found");
          } else {
            userFound = false;
          }

          if (!userFound) {
            // If user wasn't found at this stage something is quite wrong, so exit
            // forcefully
            String message = "User wasn't found after being added!";
            log.fatal(message);
            throw new RuntimeException(message);
          }

          userID = userResult.getString(1);
          userName = userResult.getString(2);
          classId = userResult.getString(3); // classId
          isTempUsername = userResult.getBoolean(4);
        }
      }

      log.debug("User '" + userName + "' has logged in via SSO" + " with role " + userRole);

      result[0] = userID;
      result[1] = userName; // userName
      result[2] = userRole; // role
      result[5] = "false"; // sso logins can't change password
      result[4] = classId; // classId
      result[5] = Boolean.toString(isTempUsername);

      log.debug("$$$ End authUser $$$");
      return result;
    } catch (SQLException e) {
      log.fatal("authUserSSO failed: " + e.toString());
      throw new RuntimeException(e);
    }
  }

  /**
   * Used to determine if a user has completed a module already
   *
   * @param ApplicationRoot The current running context of an application
   * @param moduleId The module identifier
   * @param userId The user identifier
   * @return The module name of the module IF the user has not completed AND the user has previously
   *     opened the challenge.
   */
  public static String checkPlayerResult(String ApplicationRoot, String moduleId, String userId) {
    log.debug("*** Getter.checkPlayerResult ***");

    String result = null;
    try (Connection conn = Database.getCoreConnection(ApplicationRoot);
        CallableStatement callstmnt = conn.prepareCall("call userCheckResult(?, ?)")) {

      log.debug("Preparing userCheckResult call");
      callstmnt.setString(1, moduleId);
      callstmnt.setString(2, userId);
      log.debug("Executing userCheckResult");
      try (ResultSet resultSet = callstmnt.executeQuery()) {
        resultSet.next();
        result = resultSet.getString(1);
      }
    } catch (SQLException e) {
      log.debug("userCheckResult Failure: " + e.toString());
      result = null;
    }
    log.debug("*** END checkPlayerResult ***");
    return result;
  }

  /**
   * Used to decipher whether or not a user exists as a player
   *
   * @param userId The user identifier of the player to be found
   * @return A boolean reflecting the state of existence of the player
   */
  public static boolean findPlayerById(String ApplicationRoot, String userId) {
    log.debug("*** Getter.findPlayerById ***");
    boolean userFound = false;
    // Get connection
    try (Connection conn = Database.getCoreConnection(ApplicationRoot);
        CallableStatement callstmt = conn.prepareCall("call playerFindById(?)")) {

      log.debug("Gathering playerFindById ResultSet");
      callstmt.setString(1, userId);
      try (ResultSet userFind = callstmt.executeQuery()) {
        log.debug("Opening Result Set from playerFindById");
        userFind.next(); // This will throw an exception if player not found
        log.debug(
            "Player Found: "
                + userFind.getString(1)); // This line will not execute if player not found
        userFound = true;
      }
    } catch (SQLException e) {
      log.error("Player did not exist: " + e.toString());
      userFound = false;
    }
    log.debug("*** END findPlayerById ***");
    return userFound;
  }

  /**
   * Used to gather all module information for internal functionality. This method is used in
   * creating View's or in control class operations
   *
   * @param ApplicationRoot The current runing context of the application
   * @return An ArrayList of String arrays that contain the module identifier, module name, module
   *     type and module category of each module in the core database.
   */
  public static ArrayList<String[]> getAllModuleInfo(String ApplicationRoot) {
    log.debug("*** Getter.getAllModuleInfo ***");
    ArrayList<String[]> modules = new ArrayList<String[]>();

    try (Connection conn = Database.getCoreConnection(ApplicationRoot);
        CallableStatement callstmt = conn.prepareCall("call moduleGetAll()")) {

      log.debug("Gathering moduleGetAll ResultSet");
      try (ResultSet resultSet = callstmt.executeQuery()) {
        log.debug("Opening Result Set from moduleGetAll");
        int i = 0;
        while (resultSet.next()) {
          String[] result = new String[4];
          i++;
          result[0] = resultSet.getString(1); // moduleId
          result[1] = resultSet.getString(2); // moduleName
          result[2] = resultSet.getString(3); // moduleType
          result[3] = resultSet.getString(4); // mdouleCategory
          modules.add(result);
        }
        log.debug("Returning Array list with " + i + " entries.");
      }
    } catch (SQLException e) {
      log.error("Could not execute query: " + e.toString());
    }
    log.debug("*** END getAllModuleInfo ***");
    return modules;
  }

  /**
   * Returns HTML menu for challenges. Challenges are only referenced by their id, The user will
   * have to go through another servlet to get the module's View address
   *
   * @param ApplicationRoot The current running context of the application
   * @param userId The user identifier of the player
   * @param lang The Locale the user has enabled
   * @return HTML menu for challenges
   * @throws SQLException
   */
  public static String getChallenges(String ApplicationRoot, String userId, Locale lang)
      throws SQLException {
    log.debug("*** Getter.getChallenges ***");
    String output = new String();
    // Getting Translated Level Names
    ResourceBundle bundle = ResourceBundle.getBundle("i18n.moduleGenerics.moduleNames", lang);
    // Encoder to prevent XSS
    try (Connection conn = Database.getCoreConnection(ApplicationRoot);
        CallableStatement callstmt = conn.prepareCall("call moduleAllInfo(?, ?)")) {

      callstmt.setString(1, "challenge");
      callstmt.setString(2, userId);
      log.debug("Gathering moduleAllInfo ResultSet");
      try (ResultSet challenges = callstmt.executeQuery()) {
        log.debug("Opening Result Set from moduleAllInfo");
        String challengeCategory = new String();
        int rowNumber = 0; // Identifies the first row, ie the start of the list. This is slightly
        // different output to every other row
        while (challenges.next()) {
          if (!challengeCategory.equalsIgnoreCase(challenges.getString(2))) {
            challengeCategory = challenges.getString(2);
            // log.debug("New Category Detected: " + challengeCategory);
            if (rowNumber > 0) // output prepared for Every row after row 1
            {
              output +=
                  "</ul></li><li><a href='javascript:;' class='challengeHeader' >"
                      + Encode.forHtml(bundle.getString("category." + challengeCategory))
                      + "</a><ul class='challengeList' style='display: none;'>";
            } else // output prepared for First row in entire challenge
            {
              output +=
                  "<li><a href='javascript:;' class='challengeHeader'>"
                      + Encode.forHtml(bundle.getString("category." + challengeCategory))
                      + "</a><ul class='challengeList' style='display: none;'>";
            }
            // log.debug("Compiling Challenge Category - " + challengeCategory);
          }
          output += "<li>"; // Starts next LI element
          if (challenges.getString(4) != null) {
            output += "<img src='css/images/completed.png'/>"; // Completed marker
          } else {
            output += "<img src='css/images/uncompleted.png'/>"; // Incomplete marker
          }
          // Final out put compilation
          output +=
              "<a class='lesson' id='"
                  + Encode.forHtmlAttribute(challenges.getString(3))
                  + "' href='javascript:;'>"
                  + Encode.forHtml(bundle.getString(challenges.getString(1)))
                  + "</a>";
          output += "</li>";
          rowNumber++;
        }
        // Check if output is empty
        if (output.isEmpty()) {
          output = "<li>No challenges found</li>";
        } else {
          log.debug("Appending End tags");
          output += "</ul></li>";
        }
      }

      log.debug("*** END getChallenges() ***");
      return output;
    }
  }

  /**
   * @param ApplicationRoot The current running context of the application
   * @return The amount of classes currently existing in the database
   */
  public static int getClassCount(String ApplicationRoot) {
    int result = 0;
    log.debug("*** Getter.getClassCount ***");
    try (Connection conn = Database.getCoreConnection(ApplicationRoot);
        CallableStatement callstmt = conn.prepareCall("call classCount()")) {

      log.debug("Gathering classCount ResultSet");
      try (ResultSet resultSet = callstmt.executeQuery()) {
        log.debug("Opening Result Set from classCount");
        resultSet.next();
        result = resultSet.getInt(1);
      }
    } catch (SQLException e) {
      log.error("Could not execute query: " + e.toString());
      result = 0;
    }
    log.debug("*** END getClassCount");
    return result;
  }

  /**
   * @param ApplicationRoot The current running context of the application
   * @return Result set containing class info in the order classId, className and then classYear
   */
  public static ResultSet getClassInfo(String ApplicationRoot) {
    ResultSet result = null;
    log.debug("*** Getter.getClassInfo (All Classes) ***");
    try (Connection conn = Database.getCoreConnection(ApplicationRoot);
        CallableStatement callstmt = conn.prepareCall("call classesGetData()")) {
      callstmt.setMaxRows(MAX_ROWSET_ROWS);
      try (ResultSet resultSet = callstmt.executeQuery()) {
        log.debug("Gathering classesGetData ResultSet");
        CachedRowSet rowSet = RowSetProvider.newFactory().createCachedRowSet();
        rowSet.populate(resultSet);
        rowSet.beforeFirst(); // populate() leaves the cursor after the last row
        result = rowSet;
      }
      log.debug("Returning Result Set from classesGetData");
    } catch (SQLException e) {
      log.error("Could not execute query: " + e.toString());
      result = null;
    }
    log.debug("*** END getClassInfo");
    return result;
  }

  /**
   * @param ApplicationRoot The current running context of the application
   * @param classId The identifier of the class
   * @return String Array with Class information with the format of {name, year}
   */
  public static String[] getClassInfo(String ApplicationRoot, String classId) {
    String[] result = new String[2];
    log.debug("*** Getter.getClassInfo (Single Class) ***");
    try (Connection conn = Database.getCoreConnection(ApplicationRoot);
        CallableStatement callstmt = conn.prepareCall("call classFind(?)")) {

      callstmt.setString(1, classId);
      log.debug("Gathering classFind ResultSet");
      try (ResultSet resultSet = callstmt.executeQuery()) {
        log.debug("Opening Result Set from classFind");
        resultSet.next();
        result[0] = resultSet.getString(1); // Name
        result[1] = resultSet.getString(2); // Year
      }

    } catch (SQLException e) {
      log.error("Could not execute query: " + e.toString());
      result = null;
    }
    log.debug("*** END getClassInfo");
    return result;
  }

  /**
   * The CSRF forum is used in CSRF levels for users to deliver CSRF attacks against each other.
   * URLs are contained in IFRAME tags
   *
   * @param ApplicationRoot The current running context of the application
   * @param classId Identifier of the class to populate the forum with
   * @param moduleId The module in which to return the forum for
   * @param bundle Strings Package for the Language Local of the user making the request
   * @return A HTML table of a Class's CSRF Submissions for a specific module
   */
  public static String getCsrfForumWithIframe(
      String ApplicationRoot, String classId, String moduleId, ResourceBundle bundle) {
    log.debug("*** Getter.getCsrfForum ***");
    log.debug("Getting stored messages from class: " + classId);
    String htmlOutput = new String();
    try (Connection conn = Database.getCoreConnection(ApplicationRoot)) {

      if (classId != null) {
        try (CallableStatement callstmt = conn.prepareCall("call resultMessageByClass(?, ?)")) {
          log.debug("Gathering resultMessageByClass ResultSet");
          callstmt.setString(1, classId);
          callstmt.setString(2, moduleId);
          try (ResultSet resultSet = callstmt.executeQuery()) {
            log.debug("resultMessageByClass executed");

            // Table Header
            htmlOutput =
                "<table><tr><th>"
                    + bundle.getString("forum.userName")
                    + "</th><th>"
                    + bundle.getString("forum.message")
                    + "</th></tr>";

            log.debug("Opening Result Set from resultMessageByClass");
            int counter = 0;
            while (resultSet.next()) {
              counter++;
              // Table content
              htmlOutput +=
                  "<tr><td>"
                      + Encode.forHtml(resultSet.getString(1))
                      + "</td><td><iframe sandbox=\"allow-scripts allow-forms\" src=\""
                      + Encode.forHtmlAttribute(resultSet.getString(2))
                      + "\"></iframe></td></tr>";
            }
            if (counter > 0) {
              log.debug("Added a " + counter + " row table");
            } else {
              log.debug("No results from query");
            }
            // Table end
            htmlOutput += "</table>";
          }
        }
      } else {
        log.error("User with Null Class detected");
        htmlOutput = "<p><font color='red'>" + bundle.getString("error.noClass") + "</font></p>";
      }
    } catch (SQLException e) {
      log.error("Could not execute query: " + e.toString());
      htmlOutput = "<p>" + bundle.getString("error.occurred ") + "</p>";
    } catch (Exception e) {
      log.fatal("Could not return CSRF Forum: " + e.toString());
    }
    log.debug("*** END getCsrfForum ***");
    return htmlOutput;
  }

  /**
   * The CSRF forum is used in CSRF levels for users to deliver CSRF attacks against each other.
   * URLs are contained in IMG tags
   *
   * @param ApplicationRoot The current running context of the application
   * @param classId Identifier of the class to populate the forum with
   * @param moduleId The module in which to return the forum for
   * @param bundle The strings package for the language of the user
   * @return A HTML table of a Class's CSRF Submissions for a specific module
   */
  public static String getCsrfForumWithImg(
      String ApplicationRoot, String classId, String moduleId, ResourceBundle bundle) {
    log.debug("*** Getter.getCsrfForum ***");
    log.debug("Getting stored messages from class: " + classId);
    String htmlOutput = new String();
    try (Connection conn = Database.getCoreConnection(ApplicationRoot)) {

      if (classId != null) {
        try (CallableStatement callstmt = conn.prepareCall("call resultMessageByClass(?, ?)")) {
          log.debug("Gathering resultMessageByClass ResultSet");
          callstmt.setString(1, classId);
          callstmt.setString(2, moduleId);
          try (ResultSet resultSet = callstmt.executeQuery()) {
            log.debug("resultMessageByClass executed");

            // Table Header
            htmlOutput =
                "<table><tr><th>"
                    + bundle.getString("forum.userName")
                    + "</th><th>"
                    + bundle.getString("forum.image")
                    + "</th></tr>";

            log.debug("Opening Result Set from resultMessageByClass");
            int counter = 0;
            while (resultSet.next()) {
              counter++;
              // Table content
              htmlOutput +=
                  "<tr><td>"
                      + Encode.forHtml(resultSet.getString(1))
                      + "</td><td><img src=\""
                      + Encode.forHtmlAttribute(resultSet.getString(2))
                      + "\"/></td></tr>";
            }
            if (counter > 0) {
              log.debug("Added a " + counter + " row table");
            } else {
              log.debug("No results from query");
            }
            // Table end
            htmlOutput += "</table>";
          }
        }
      } else {
        log.error("User with Null Class detected");
        htmlOutput = "<p><font color='red'>" + bundle.getString("error.noClass") + "</font></p>";
      }
    } catch (SQLException e) {
      log.error("Could not execute query: " + e.toString());
      htmlOutput = "<p>" + bundle.getString("error.occurred") + "</p>";
    } catch (Exception e) {
      log.fatal("Could not return CSRF Forum: " + e.toString());
    }
    log.debug("*** END getCsrfForum ***");
    return htmlOutput;
  }

  /**
   * Used to present a modules feedback, including averages and raw results.
   *
   * @param applicationRoot The current running context of the application.
   * @param moduleId The module identifier
   * @return A HTML table of the feedback for a specific module
   */
  public static String getFeedback(String applicationRoot, String moduleId) {
    log.debug("*** Getter.getFeedback ***");

    String result = new String();
    try (Connection conn = Database.getCoreConnection(applicationRoot);
        CallableStatement callstmnt = conn.prepareCall("call moduleFeedback(?)")) {

      log.debug("Preparing moduleFeedback call");
      callstmnt.setString(1, moduleId);
      log.debug("Executing moduleFeedback");
      try (ResultSet resultSet = callstmnt.executeQuery()) {
        int resultAmount = 0;
        int before = 0;
        int after = 0;
        int difficulty = 0;
        boolean color = true;
        while (resultSet.next()) {
          if (resultSet.getString(1) != null) {
            resultAmount++;
            difficulty += resultSet.getInt(3);
            before += resultSet.getInt(4);
            after += resultSet.getInt(5);
            result += "<tr ";
            if (color) // Alternate row color
            {
              color = !color;
              result += "BGCOLOR='A878EF'";
            } else {
              color = !color;
              result += "BGCOLOR='D4BCF7'";
            }
            // A row off information
            result +=
                "><td>"
                    + Encode.forHtml(resultSet.getString(1))
                    + "</td><td>"
                    + Encode.forHtml(resultSet.getString(2))
                    + "</td><td>"
                    + resultSet.getInt(3)
                    + "</td><td>"
                    + resultSet.getInt(4)
                    + "</td><td>"
                    + resultSet.getInt(5)
                    + "</td><td>"
                    + Encode.forHtml(resultSet.getString(6))
                    + "</td></tr>";
          }
        }
        if (resultAmount > 0) // Table header
        {
          result =
              "<table><tr><th>Player</th><th>Time</th><th>Difficulty</th><th>Before</th><th>After</th><th>Comments</th></tr>"
                  + "<tr><td>Average</td><td></td><td>"
                  + difficulty / resultAmount
                  + "</td><td>"
                  + before / resultAmount
                  + "</td><td>"
                  + after / resultAmount
                  + "</td><td></td></tr>"
                  + result
                  + "<table>";
        } else // If empty, Blank output
        {
          result = new String();
        }
      }

    } catch (SQLException e) {
      log.error("moduleFeedback Failure: " + e.toString());
      result = null;
    }
    log.debug("*** END getFeedback ***");
    return result;
  }

  /**
   * This method prepares the incremental module menu. This is when Security Shepherd is in "Game
   * Mode". Users are presented with one uncompleted module at a time. This method also returns a
   * script to be executed every time the menu is chanegd. This is script defines the animation and
   * operations to be carried out when the menu is interacted with
   *
   * @param ApplicationRoot The running context of the application.
   * @param userId The user identifier of the user.
   * @param lang The name of the Locale the user
   * @param csrfToken The cross site request forgery token
   * @return A HTML menu of a users current module progress and a script for interaction with this
   *     menu
   */
  public static String getIncrementalModules(
      String ApplicationRoot, String userId, String lang, String csrfToken) {
    log.debug("*** Getter.getIncrementalChallenges ***");
    String output = new String();

    Locale.setDefault(new Locale("en"));
    Locale locale = new Locale(lang);
    ResourceBundle bundle = ResourceBundle.getBundle("i18n.text", locale);
    ResourceBundle levelNames = ResourceBundle.getBundle("i18n.moduleGenerics.moduleNames", locale);

    try (Connection conn = Database.getCoreConnection(ApplicationRoot);
        CallableStatement callstmt = conn.prepareCall("call moduleIncrementalInfo(?)")) {

      callstmt.setString(1, userId);
      log.debug("Gathering moduleIncrementalInfo ResultSet");
      try (ResultSet modules = callstmt.executeQuery()) {
        log.debug("Opening Result Set from moduleIncrementalInfo");
        boolean lastRow = false;
        boolean completedModules = false;

        // Preparing first Category header; "Completed"
        output =
            "<li><a id='completedList' href='javascript:;'><div class='menuButton'>"
                + bundle.getString("getter.button.completed")
                + "</div></a>\n"
                + "<ul id='theCompletedList' style='display: none;' class='levelList'>";

        while (modules.next() && !lastRow) {
          // For each row, prepair the modules the users can select
          if (modules.getString(4) != null) // If not Last Row
          {
            completedModules = true;
            output += "<li>";
            output +=
                "<a class='lesson' id='"
                    + Encode.forHtmlAttribute(modules.getString(3))
                    + "' href='javascript:;'>"
                    + Encode.forHtml(levelNames.getString(modules.getString(1)))
                    + "</a>";
            output += "</li>";
          } else {
            lastRow = true;
            // Last Row - Highlighed Next Challenge
            if (completedModules) {
              output += "</ul></li><li>";
            } else {
              // NO completed modules, so dont show any...
              output = new String();
            }

            // Second category - Uncompleted
            output +=
                "<a class='lesson' id='"
                    + Encode.forHtmlAttribute(modules.getString(3))
                    + "' href='javascript:;'>"
                    + "<div class='menuButton'>"
                    + bundle.getString("getter.button.nextChallenge")
                    + "</div>"
                    + "</a>";
            output += "</li>";
          }
        }

        if (!lastRow) // If true, then the user has completed all challenges
        {
          output +=
              "<h2 id='uncompletedList'><a href='javascript:;'>"
                  + bundle.getString("getter.button.finished")
                  + "</a></h2>\n"
                  + "</li>";
        }
        if (output
            .isEmpty()) // If this method has gone so far without any output, create a error message
        {
          output =
              "<li><a href='javascript:;'>"
                  + bundle.getString("getter.button.noModulesFound")
                  + "</a></li>";
        } else // final tags to ensure valid HTML
        {
          log.debug("Appending End tags");
          // output += "</ul></li>"; //Commented Out to prevent Search Box being pushed
          // into Footer
        }

        // This is the script for menu interaction
        output +=
            "<script>applyMenuButtonActionsCtfMode('"
                + Encode.forHtml(csrfToken)
                + "', \""
                + Encode.forHtml(bundle.getString("generic.text.sorryError"))
                + "\");</script>";
      }

    } catch (Exception e) {
      log.error("Challenge Retrieval: " + e.toString());
    }
    log.debug("*** END getIncrementalChallenges() ***");
    return output;
  }

  /**
   * This method prepares the incremental module menu. This is when Security Shepherd is in "Game
   * Mode". Users are presented with one uncompleted module at a time. This method does not return
   * the JS script describing how the menu used should work
   *
   * @param ApplicationRoot The running context of the application.
   * @param userId The user identifier of the user.
   * @param lang The name of the Locale the user
   * @param csrfToken The cross site request forgery token
   * @return A HTML menu of a users current module progress and a script for interaction with this
   *     menu
   */
  public static String getIncrementalModulesWithoutScript(
      String ApplicationRoot, String userId, String lang, String csrfToken) {
    log.debug("*** Getter.getIncrementalChallengesWithoutScript ***");
    String output = new String();

    Locale.setDefault(new Locale("en"));
    Locale locale = new Locale(lang);
    ResourceBundle bundle = ResourceBundle.getBundle("i18n.text", locale);
    ResourceBundle levelNames = ResourceBundle.getBundle("i18n.moduleGenerics.moduleNames", locale);

    try (Connection conn = Database.getCoreConnection(ApplicationRoot);
        CallableStatement callstmt = conn.prepareCall("call moduleIncrementalInfo(?)")) {

      callstmt.setString(1, userId);
      log.debug("Gathering moduleIncrementalInfo ResultSet");
      try (ResultSet modules = callstmt.executeQuery()) {
        log.debug("Opening Result Set from moduleIncrementalInfo");
        boolean lastRow = false;
        boolean completedModules = false;

        // Preparing first Category header; "Completed"
        output =
            "<li><a id='completedList' href='javascript:;'><div class='menuButton'>"
                + bundle.getString("getter.button.completed")
                + "</div></a>\n"
                + "<ul id='theCompletedList' style='display: none;' class='levelList'>";

        while (modules.next() && !lastRow) {
          // For each row, prepair the modules the users can select
          if (modules.getString(4) != null) // If not Last Row
          {
            completedModules = true;
            output += "<li>";
            output +=
                "<a class='lesson' id='"
                    + Encode.forHtmlAttribute(modules.getString(3))
                    + "' href='javascript:;'>"
                    + Encode.forHtml(levelNames.getString(modules.getString(1)))
                    + "</a>";
            output += "</li>";
          } else {
            lastRow = true;
            // Last Row - Highlighed Next Challenge
            if (completedModules) {
              output += "</ul></li><li>";
            } else {
              // NO completed modules, so dont show any...
              output = new String();
            }

            // Second category - Uncompleted
            output +=
                "<a class='lesson' id='"
                    + Encode.forHtmlAttribute(modules.getString(3))
                    + "' href='javascript:;'>"
                    + "<div class='menuButton'>"
                    + bundle.getString("getter.button.nextChallenge")
                    + "</div>"
                    + "</a>";
            output += "</li>";
          }
        }

        if (!lastRow) // If true, then the user has completed all challenges
        {
          output +=
              "<h2 id='uncompletedList'><a href='javascript:;'>"
                  + bundle.getString("getter.button.finished")
                  + "</a></h2>\n"
                  + "</li>";
        }
        if (output
            .isEmpty()) // If this method has gone so far without any output, create a error message
        {
          output =
              "<li><a href='javascript:;'>"
                  + bundle.getString("getter.button.noModulesFound")
                  + "</a></li>";
        } else // final tags to ensure valid HTML
        {
          log.debug("Appending End tags");
          // output += "</ul></li>"; //Commented Out to prevent Search Box being pushed
          // into Footer
        }
      }

    } catch (Exception e) {
      log.error("Challenge Retrieval: " + e.toString());
    }
    log.debug("*** END getIncrementalChallengesWithoutScript() ***");
    return output;
  }

  /**
   * Use to return the current progress of a class in JSON format with information like userid, user
   * name and score
   *
   * @param applicationRoot The current running context of the application
   * @param classId The identifier of the class to use in lookup
   * @return A JSON representation of a class's score in the order {id, username, userTitle, score,
   *     scale, place, order, goldmedalcount, goldDisplay, silverMedalCount, silverDisplay,
   *     bronzeDisplay, bronzeMedalCount}
   */
  @SuppressWarnings("unchecked")
  public static String getJsonScore(String applicationRoot, String classId) {
    log.debug("classId: " + classId);
    String result = new String();
    try (Connection conn = Database.getCoreConnection(applicationRoot)) {

      // Returns User's: Name, # of Completed modules and Score
      CallableStatement callstmnt = null;
      if (ScoreboardStatus.getScoreboardClass().isEmpty()
          && !ScoreboardStatus.isClassSpecificScoreboard()) {
        callstmnt =
            conn.prepareCall("call totalScoreboard()"); // Open Scoreboard not based on a class
      } else {
        callstmnt =
            conn.prepareCall("call classScoreboard(?)"); // Class Scoreboard based on classId
        callstmnt.setString(1, classId);
      }
      // log.debug("Executing classScoreboard");
      try (CallableStatement callstmntToClose = callstmnt;
          ResultSet resultSet = callstmnt.executeQuery()) {
        JSONArray json = new JSONArray();
        JSONObject jsonInner = new JSONObject();
        int resultAmount = 0;
        int prevPlace = 0;
        int prevScore = 0;
        int prevGold = 0;
        int prevSilver = 0;
        int prevBronze = 0;
        float baseBarScale = 0; //
        float tieBreaker = 0;
        while (resultSet.next()) // For each user in a class
        {
          resultAmount++;
          jsonInner = new JSONObject();
          if (resultSet.getString(1) != null) {
            int place = resultAmount;
            int score = resultSet.getInt(3);
            int goldMedals = resultSet.getInt(4);
            int silverMedals = resultSet.getInt(5);
            int bronzeMedals = resultSet.getInt(6);
            if (resultAmount
                == 1) // First Place is Returned First, so this will be the biggest bar on the
            // scoreboard
            {
              int highscore = score;
              // log.debug("Current Highscore Listing is " + highscore);
              // Use the high score to scale the width of the bars for the whole scoreboard
              float maxBarScale =
                  1.02f; // High Score bar will have a scale of 1 //This will get used when a
              // scale is added to the scoreboard
              baseBarScale = highscore * maxBarScale;
              // setting up variables for Tie Scenario Placings
              prevPlace = 1;
              prevScore = score;
            } else {
              // Does this score line match the one before (Score and Medals)? if so the place
              // shouldnt change
              if (score == prevScore
                  && goldMedals == prevGold
                  && silverMedals == prevSilver
                  && bronzeMedals == prevBronze) {
                place = prevPlace;
                tieBreaker = tieBreaker + 0.01f;
              } else {
                prevScore = score;
                prevPlace = place;
                prevGold = goldMedals;
                prevSilver = silverMedals;
                prevBronze = bronzeMedals;
                tieBreaker = 0;
              }
            }
            String displayMedal = new String("display: inline;");
            String goldDisplayStyle = new String("display: none;");
            String silverDisplayStyle = new String("display: none;");
            String bronzeDisplayStyle = new String("display: none;");
            if (goldMedals > 0) {
              goldDisplayStyle = displayMedal;
            }
            if (silverMedals > 0) {
              silverDisplayStyle = displayMedal;
            }
            if (bronzeMedals > 0) {
              bronzeDisplayStyle = displayMedal;
            }

            int barScale =
                (int) ((score * 100) / baseBarScale); // bar scale is the percentage the bar should
            // be of the row's context (Highest Possible
            // is depends on scale set in maxBarScale.
            // eg: maxBarScale = 1.1 would mean the max
            // scale would be 91% for a single row)

            String userMedalString = new String();
            if (goldMedals > 0 || silverMedals > 0 || bronzeMedals > 0) {
              userMedalString += " holding ";
              if (goldMedals > 0) {
                userMedalString += goldMedals + " gold";
              }
              if (silverMedals > 0) {
                if (goldMedals > 0) // Medals Before, puncuate
                {
                  if (bronzeMedals > 0) // more medals after silver? Comma
                  {
                    userMedalString += ", ";
                  } else // Say And
                  {
                    userMedalString += " and ";
                  }
                }
                userMedalString += silverMedals + " silver";
              }
              if (bronzeMedals > 0) {
                if (goldMedals > 0 || silverMedals > 0) // Medals Before?
                {
                  userMedalString += " and ";
                }
                userMedalString += bronzeMedals + " bronze";
              }
              // Say Medal(s) at the end of the string
              userMedalString += " medal";
              if (goldMedals + silverMedals + bronzeMedals > 1) {
                userMedalString += "s";
              }
            }

            jsonInner.put("id", new String(Encode.forHtml(resultSet.getString(1)))); // User Id
            jsonInner.put(
                "username", new String(Encode.forHtml(resultSet.getString(2)))); // User Name
            jsonInner.put(
                "userTitle",
                new String(
                    Encode.forHtml(resultSet.getString(2))
                        + " with "
                        + score
                        + " points"
                        + userMedalString)); // User
            // name
            // encoded
            // for
            // title
            // attribute
            jsonInner.put("score", Integer.valueOf(score)); // Score
            jsonInner.put("scale", barScale); // Scale of score bar
            jsonInner.put("place", place); // Place on board
            jsonInner.put("order", (place + tieBreaker)); // Order on board
            jsonInner.put("goldMedalCount", Integer.valueOf(goldMedals));
            jsonInner.put("goldDisplay", goldDisplayStyle);
            jsonInner.put("silverMedalCount", Integer.valueOf(silverMedals));
            jsonInner.put("silverDisplay", silverDisplayStyle);
            jsonInner.put("bronzeMedalCount", Integer.valueOf(bronzeMedals));
            jsonInner.put("bronzeDisplay", bronzeDisplayStyle);
            // log.debug("Adding: " + jsonInner.toString());
            json.put(jsonInner);
          }
        }
        if (resultAmount > 0) {
          result = json.toString();
        } else {
          result = new String();
        }
      }

    } catch (SQLException e) {
      log.error("getJsonScore Failure: " + e.toString());
      result = null;
    } catch (Exception e) {
      log.error("getJsonScore Unexpected Failure: " + e.toString());
      result = null;
    }
    // log.debug("*** END getJsonScore ***");
    return result;
  }

  /**
   * Used to gather a menu of lessons for a user, including markers for each lesson they have
   * completed or not completed
   *
   * @param ApplicationRoot The current running context of the application
   * @param userId Identifier of the user
   * @param lang The the Locale the user has enabled
   * @return HTML lesson menu for Open Floor Plan.
   */
  public static String getLessons(String ApplicationRoot, String userId, Locale lang) {
    log.debug("*** Getter.getLesson ***");
    // Getting Translated Level Names
    ResourceBundle bundle = ResourceBundle.getBundle("i18n.moduleGenerics.moduleNames", lang);
    String output = new String();
    try (Connection conn = Database.getCoreConnection(ApplicationRoot);
        // Get the lesson modules
        CallableStatement callstmt = conn.prepareCall("call lessonInfo(?)")) {
      callstmt.setString(1, userId);
      log.debug("Gathering lessonInfo ResultSet for user " + userId);
      try (ResultSet lessons = callstmt.executeQuery()) {
        log.debug("Opening Result Set from moduleAllInfo");
        while (lessons.next()) {
          // log.debug("Adding " + lessons.getString(1));
          output += "<li>";
          // Markers for completion
          if (lessons.getString(4) != null) {
            output += "<img src='css/images/completed.png'/>";
          } else {
            output += "<img src='css/images/uncompleted.png'/>";
          }
          // Prepare lesson output
          output +=
              "<a class='lesson' id='"
                  + Encode.forHtmlAttribute(lessons.getString(3))
                  + "' href='javascript:;'>"
                  + Encode.forHtml(bundle.getString(lessons.getString(1)))
                  + "</a>";
          output += "</li>";
        }
        // If no output has been found, return an error message
        if (output.isEmpty()) {
          output = "<li>No lessons found</li>";
        } else {
          log.debug("Lesson List returned");
        }
      }
    } catch (Exception e) {
      log.error("lesson Retrieval: " + e.toString());
    }
    log.debug("*** END getLesson() ***");
    return output;
  }

  /**
   * This method returns the address of a module based on the module identifier submitted. If user
   * has not accessed this level before, they are put down as starting the level at this time. If
   * the level is a client side attack, or other issues that cannot be abused to return a result key
   * (like XSS, CSRF or network sniffing) the address is of the core server. Otherwise the modules
   * sit on the vulnerable application server
   *
   * @param ApplicationRoot The current running context of the application
   * @param moduleId Identifier of the module the to return
   * @param userId The identifier of the user that wants to get the module
   * @return The module address
   */
  public static String getModuleAddress(String ApplicationRoot, String moduleId, String userId) {
    log.debug("*** Getter.getModuleAddress ***");
    String output = new String();
    String type = new String();
    try (Connection conn = Database.getCoreConnection(ApplicationRoot);
        CallableStatement callstmt = conn.prepareCall("call moduleGetHash(?, ?)")) {
      callstmt.setString(1, moduleId);
      callstmt.setString(2, userId);
      log.debug("Gathering moduleGetHash ResultSet");
      try (ResultSet modules = callstmt.executeQuery()) {
        log.debug("Opening Result Set from moduleGetHash");
        modules.next(); // Exception thrown if no hash was found
        // Set Type. Used to ensure the URL points at the correct directory
        if (modules.getString(3).equalsIgnoreCase("challenge")) {
          type = "challenges";
        } else {
          type = "lessons";
        }
        output = type + "/" + modules.getString(1) + ".jsp";
      }
    } catch (Exception e) {
      log.error("Module Hash Retrieval: " + e.toString());
      log.error("moduleID = " + moduleId);
      log.error("userID = " + userId);
    }
    log.debug("*** END getModuleAddress() ***");
    return output;
  }

  /**
   * Retrieves the module category based on the moduleId submitted
   *
   * @param ApplicationRoot The current running context of the application
   * @param moduleId The id of the module that
   * @return
   */
  public static String getModuleCategory(String ApplicationRoot, String moduleId) {
    log.debug("*** Getter.getModuleResult ***");
    String theCategory = null;
    try (Connection conn = Database.getCoreConnection(ApplicationRoot);
        PreparedStatement prepstmt =
            conn.prepareStatement("SELECT moduleCategory FROM modules WHERE moduleId = ?")) {
      prepstmt.setString(1, moduleId);
      try (ResultSet moduleFind = prepstmt.executeQuery()) {
        moduleFind.next();
        theCategory = moduleFind.getString(1);
      }
    } catch (Exception e) {
      log.error("Module did not exist: " + e.toString());
      theCategory = null;
    }
    log.debug("*** END getModuleCategory ***");
    return theCategory;
  }

  /**
   * @param applicationRoot The current running context of the application.
   * @param moduleId The identifier of a module
   * @return The hash of the module specified
   */
  public static String getModuleHash(String applicationRoot, String moduleId) {
    log.debug("*** Getter.getModuleHash ***");
    String result = new String();
    try (Connection conn = Database.getCoreConnection(applicationRoot);
        CallableStatement callstmt = conn.prepareCall("call moduleGetHashById(?)")) {
      log.debug("Gathering moduleGetHash ResultSet");
      callstmt.setString(1, moduleId);
      try (ResultSet resultSet = callstmt.executeQuery()) {
        log.debug("Opening Result Set from moduleGetHash");
        resultSet.next();
        result = resultSet.getString(1);
      }
    } catch (SQLException e) {
      log.error("Could not execute moduleGetHash: " + e.toString());
      result = null;
    }
    log.debug("*** END getModuleHash ***");
    return result;
  }

  /**
   * Convert module hash to ID
   *
   * @param ApplicationRoot The current running context of the application
   * @param moduleHash The module hash to use for look up
   * @return The identifier of the module with the module hash of the moduleHash parameter
   */
  public static String getModuleIdFromHash(String ApplicationRoot, String moduleHash) {
    log.debug("*** Getter.getModuleIdFromHash ***");
    log.debug("Getting ID from Hash: " + moduleHash);
    String result = new String();
    try (Connection conn = Database.getCoreConnection(ApplicationRoot);
        CallableStatement callstmt = conn.prepareCall("call moduleGetIdFromHash(?)")) {
      log.debug("Gathering moduleGetIdFromHash ResultSet");
      callstmt.setString(1, moduleHash);
      try (ResultSet resultSet = callstmt.executeQuery()) {
        log.debug("Opening Result Set from moduleGetIdFromHash");
        resultSet.next();
        result = resultSet.getString(1);
      }
    } catch (SQLException e) {
      log.error("Could not execute query: " + e.toString());
      result = null;
    }
    log.debug("*** END getModuleIdFromHash ***");
    return result;
  }

  /**
   * Returns true if a module has a hard coded key, false if server encrypts it
   *
   * @param ApplicationRoot The current running context of the application
   * @param moduleId The id of the module
   * @return Returns true if a module has a hard coded key, false if server encrypts it
   */
  public static boolean getModuleKeyType(String ApplicationRoot, String moduleId) {
    log.debug("*** Getter.getModuleKeyType ***");
    boolean theKeyType = true;
    try (Connection conn = Database.getCoreConnection(ApplicationRoot);
        PreparedStatement prepstmt =
            conn.prepareStatement("SELECT hardcodedKey FROM modules WHERE moduleId = ?")) {
      prepstmt.setString(1, moduleId);
      try (ResultSet moduleFind = prepstmt.executeQuery()) {
        moduleFind.next();
        theKeyType = moduleFind.getBoolean(1);
        if (theKeyType) {
          log.debug("Module has hard coded Key");
        } else {
          log.debug("Module has user specific Key");
        }
      }
    } catch (Exception e) {
      log.error("Module did not exist: " + e.toString());
      theKeyType = true;
    }
    log.debug("*** END getModuleKeyType ***");
    return theKeyType;
  }

  /**
   * This method retrieves the i18n local key for a module's name.
   *
   * @param applicationRoot Application Running Context
   * @param moduleId ID of the module to lookup
   * @return Locale key for the Module's Name.
   */
  public static String getModuleNameLocaleKey(String applicationRoot, String moduleId) {
    log.debug("*** Getter.getModuleNameLocaleKey ***");
    String result = new String();
    try (Connection conn = Database.getCoreConnection(applicationRoot);
        CallableStatement callstmt = conn.prepareCall("call moduleGetNameLocale(?)")) {
      log.debug("Gathering moduleGetNameLocale ResultSet");
      callstmt.setString(1, moduleId);
      try (ResultSet resultSet = callstmt.executeQuery()) {
        log.debug("Opening Result Set from moduleGetNameLocale");
        resultSet.next();
        result = resultSet.getString(1);
      }
    } catch (SQLException e) {
      log.error("Could not execute moduleGetNameLocale: " + e.toString());
      result = null;
    }
    log.debug("*** END getModuleNameLocaleKey ***");
    return result;
  }

  /**
   * @param ApplicationRoot The current running context of the application
   * @param moduleId Identifier of module
   * @return The db stored solution key value for the moduleId submitted
   */
  public static String getModuleResult(String ApplicationRoot, String moduleId) {
    log.debug("*** Getter.getModuleResult ***");
    String moduleFound = null;
    try (Connection conn = Database.getCoreConnection(ApplicationRoot);
        CallableStatement callstmt = conn.prepareCall("call moduleGetResult(?)")) {
      log.debug("Gathering moduleGetResult ResultSet");
      callstmt.setString(1, moduleId);
      try (ResultSet moduleFind = callstmt.executeQuery()) {
        log.debug("Opening Result Set from moduleGetResult");
        moduleFind.next();
        log.debug("Module " + moduleFind.getString(1) + " Found");
        moduleFound = moduleFind.getString(2);
      }
    } catch (Exception e) {
      log.error("Module did not exist: " + e.toString());
      moduleFound = null;
    }
    log.debug("*** END getModuleResult ***");
    return moduleFound;
  }

  /**
   * Returns the result key for a module using the module's hash for the lookup procedure.
   *
   * @param ApplicationRoot The current running context of the application
   * @param moduleHash The hash to use for module look up
   * @return The db stored solution key value for the moduleHash submited
   */
  public static String getModuleResultFromHash(String ApplicationRoot, String moduleHash) {
    log.debug("*** Getter.getModuleResultFromHash ***");
    String result = new String();
    log.debug("hash '" + moduleHash + "'");
    try (Connection conn = Database.getCoreConnection(ApplicationRoot);
        CallableStatement callstmt = conn.prepareCall("call moduleGetResultFromHash(?)")) {
      log.debug("Gathering moduleGetResultFromHash ResultSet");
      callstmt.setString(1, moduleHash);
      try (ResultSet resultSet = callstmt.executeQuery()) {
        log.debug("Opening Result Set from moduleGetResultFromHash");
        resultSet.next();
        result = resultSet.getString(1);
      }
    } catch (SQLException e) {
      log.error("Could not execute query: " + e.toString());
      result = null;
    }
    log.debug("*** END getModuleResultFromHash ***");
    return result;
  }

  /**
   * Used in creating functionality that requires a user to select a module. This method only
   * prepares the option tags for this type of input. It must still be wrapped in select tags.
   *
   * @param ApplicationRoot The current running context of the application
   * @return All modules in HTML option tags
   */
  public static String getModulesInOptionTags(String ApplicationRoot) {
    log.debug("*** Getter.getModulesInOptionTags ***");
    String output = new String();
    try (Connection conn = Database.getCoreConnection(ApplicationRoot);
        PreparedStatement callstmt =
            conn.prepareStatement(
                "SELECT moduleId, moduleName FROM modules ORDER BY moduleCategory, moduleName;")) {
      log.debug("Gathering moduleAllInfo ResultSet");
      try (ResultSet modules = callstmt.executeQuery()) {
        log.debug("Opening Result Set from moduleAllInfo");
        while (modules.next()) {
          // Each module name is embed in option tags, with a value of their module
          // identifier
          output +=
              "<option value='"
                  + Encode.forHtmlAttribute(modules.getString(1))
                  + "'>"
                  + Encode.forHtml(modules.getString(2))
                  + "</option>\n";
        }
      }
    } catch (Exception e) {
      log.error("Challenge Retrieval: " + e.toString());
    }
    log.debug("*** END getModulesInOptionTags() ***");
    return output;
  }

  /**
   * Used in creating functionality that requires a user to select a module. This method only
   * prepares the option tags for this type of input. It must still be wrapped in select tags.
   *
   * @param ApplicationRoot The current running context of the application
   * @return All modules in HTML option tags ordered by incrementalRank
   */
  public static String getModulesInOptionTagsCTF(String ApplicationRoot) {
    log.debug("*** Getter.getModulesInOptionTags ***");
    String output = new String();
    try (Connection conn = Database.getCoreConnection(ApplicationRoot);
        PreparedStatement callstmt =
            conn.prepareStatement(
                "SELECT moduleId, moduleName FROM modules ORDER BY incrementalRank;")) {
      log.debug("Gathering moduleAllInfo ResultSet");
      try (ResultSet modules = callstmt.executeQuery()) {
        log.debug("Opening Result Set from moduleAllInfo");
        while (modules.next()) {
          // Each module name is embed in option tags, with a value of their module
          // identifier
          output +=
              "<option value='"
                  + Encode.forHtmlAttribute(modules.getString(1))
                  + "'>"
                  + Encode.forHtml(modules.getString(2))
                  + "</option>\n";
        }
      }
    } catch (Exception e) {
      log.error("Challenge Retrieval: " + e.toString());
    }
    log.debug("*** END getModulesInOptionTags() ***");
    return output;
  }

  /**
   * Used to return a module cheat sheet
   *
   * @param ApplicationRoot The current running context of the application
   * @param moduleId The identifier of the module to return the cheat sheet for
   * @param lang The Locale the user has enabled
   * @return String[] containing {ModuleName, CheatSheetSolution}
   */
  public static String[] getModuleSolution(String ApplicationRoot, String moduleId, Locale lang) {
    log.debug("*** Getter.getModuleSolution ***");
    String[] result = new String[2];
    // Getting Translations
    ResourceBundle bundle = ResourceBundle.getBundle("i18n.cheatsheets.solutions", lang);
    try (Connection conn = Database.getCoreConnection(ApplicationRoot);
        CallableStatement callstmt = conn.prepareCall("call cheatSheetGetSolution(?)")) {
      log.debug("Gathering cheatSheetGetSolution ResultSet");
      callstmt.setString(1, moduleId);
      try (ResultSet resultSet = callstmt.executeQuery()) {
        log.debug("Opening Result Set from cheatSheetGetSolution");
        resultSet.next();
        result[0] = resultSet.getString(1);
        result[1] = bundle.getString(resultSet.getString(2));
      }
    } catch (SQLException e) {
      log.error("Could not execute query: " + e.toString());
      result = null;
    }
    log.debug("*** END getModuleSolution ***");
    return result;
  }

  /**
   * This method returns modules in option tags in different &lt;select&gt; elements depending on
   * their current open/closed status. The output assumes it is contained in a table context
   *
   * @param ApplicationRoot The Running Context of the Application
   * @return Tr/td elements containing a moduleStatusMenu that has lists of the current open and
   *     closed modules
   */
  public static String getModuleStatusMenu(String ApplicationRoot) {
    log.debug("*** Getter.getModuleStatusMenu ***");
    String openModules = new String();
    String closedModules = new String();
    String output = new String();
    try (Connection conn = Database.getCoreConnection(ApplicationRoot);
        // Get the modules
        CallableStatement callstmt = conn.prepareCall("call moduleAllStatus()")) {
      log.debug("Gathering moduleAllStatus ResultSet");
      try (ResultSet modules = callstmt.executeQuery()) {
        log.debug("Opening Result Set from moduleAllStatus");
        while (modules.next()) {
          String theModule =
              "<option value='"
                  + Encode.forHtmlAttribute(modules.getString(1))
                  + "'>"
                  + Encode.forHtml(modules.getString(2))
                  + "</option>\n";
          if (modules.getString(3).equalsIgnoreCase("open")) {
            // Module is Open currently, so add it to the open side of the list
            openModules += theModule;
          } else {
            // If it is not open: It must be closed (NULL or not)
            closedModules += theModule;
          }
        }
        // This is the actual output: It assumes a <table> environment
        output =
            "<tr><th>To Open</th><th>To Close</th></tr><tr>\n"
                + "<td><select style='width: 300px; height: 200px;' multiple id='toOpen'>"
                + closedModules
                + "</select></td>\n"
                + "<td><select style='width: 300px; height: 200px;' multiple id='toClose'>"
                + openModules
                + "</select></td>\n"
                + "</tr>\n";
        log.debug("Module Status Menu returned");
      }
    } catch (Exception e) {
      log.error("Module Status Menu: " + e.toString());
    }
    return output;
  }

  /**
   * This method returns the module categories in option tags that are to be open or closed in a
   * &lt;select&gt; element for administration manipulation
   *
   * @param ApplicationRoot
   * @return Module Category List for Html (&lt;SELECT&gt; element)
   */
  public static String getOpenCloseCategoryMenu(String ApplicationRoot) {
    log.debug("*** Getter.getOpenCloseCategoryMenu ***");
    String theModules = new String();
    String output = new String();
    try (Connection conn = Database.getCoreConnection(ApplicationRoot);
        // Get the modules
        PreparedStatement prestmt =
            conn.prepareStatement(
                "SELECT DISTINCT moduleCategory FROM modules ORDER BY moduleCategory")) {
      try (ResultSet modules = prestmt.executeQuery()) {
        while (modules.next()) {
          String theModule =
              "<option value='"
                  + Encode.forHtmlAttribute(modules.getString(1))
                  + "'>"
                  + Encode.forHtml(modules.getString(1))
                  + "</option>\n";
          theModules += theModule;
        }
        // This is the actual output: It assumes a <table> environment
        output =
            "<select style='width: 300px; height: 200px;' multiple id='toDo'>"
                + theModules
                + "</select>\n";
        log.debug("Module Category Menu returned");
      }
    } catch (Exception e) {
      log.error("Module Status Menu: " + e.toString());
    }
    return output;
  }

  /**
   * This method is used to gather users according by class. Thanks to MySQL syntax, where class =
   * null will return nothing, is null must be used. <br>
   * is 'validClass' will Error, = 'validclass' must be used.<br>
   * So there are two procedures this method calls. One that handles null classes, one that does not
   *
   * @param ApplicationRoot The current running context of the application
   * @param classId Identifier of class
   * @return ResultSet that contains users for the selected class in the formate {userId, userName,
   *     userAddress}
   */
  public static ResultSet getPlayersByClass(String ApplicationRoot, String classId) {
    ResultSet result = null;
    log.debug("*** Getter.getPlayersByClass (Single Class) ***");
    log.debug("classId: '" + classId + "'");
    String sql = (classId != null) ? "call playersByClass(?)" : "call playersWithoutClass()";
    try (Connection conn = Database.getCoreConnection(ApplicationRoot);
        CallableStatement callstmt = conn.prepareCall(sql)) {
      callstmt.setMaxRows(MAX_ROWSET_ROWS);
      if (classId != null) {
        log.debug("Gathering playersByClass ResultSet");
        callstmt.setString(1, classId);
      } else {
        log.debug("Gathering playersWithoutClass ResultSet");
      }
      try (ResultSet resultSet = callstmt.executeQuery()) {
        CachedRowSet rowSet = RowSetProvider.newFactory().createCachedRowSet();
        rowSet.populate(resultSet);
        rowSet.beforeFirst(); // populate() leaves the cursor after the last row
        result = rowSet;
      }
      log.debug("Returning Result Set from playersByClass");
    } catch (SQLException e) {
      log.error("Could not execute query: " + e.toString());
      result = null;
    }
    log.debug("*** END getPlayersByClass");
    return result;
  }

  /**
   * Used to present the progress of a class in a series of loading bars
   *
   * @param applicationRoot The current running context of the application
   * @param classId The identifier of the class to use in lookup
   * @return A HTML representation of a class's progress in the application
   */
  public static String getProgress(String applicationRoot, String classId) {
    log.debug("*** Getter.getProgress ***");

    String result = new String();
    log.debug("Preparing userProgress call");
    try (Connection conn = Database.getCoreConnection(applicationRoot);
        CallableStatement callstmnt = conn.prepareCall("call userProgress(?)")) {
      callstmnt.setString(1, classId);
      log.debug("Executing userProgress");
      try (ResultSet resultSet = callstmnt.executeQuery()) {
        int resultAmount = 0;
        while (resultSet.next()) // For each user in a class
        {
          resultAmount++;
          if (resultSet.getString(1) != null) {
            result +=
                "<tr><td>"
                    + Encode.forHtml(resultSet.getString(1))
                    + // Output their progress
                    "</td><td><div style='background-color: #A878EF; heigth: 25px; width: "
                    + widthOfUnitBar * resultSet.getInt(2)
                    + "px;'>"
                    + "<font color='white'><strong>"
                    + resultSet.getInt(2);
            if (resultSet.getInt(2) > 6) {
              result += " Modules";
            }
            result += "</strong></font></div></td></tr>";
          }
        }
        if (resultAmount > 0) {
          result = "<table><tr><th>Player</th><th>Progress</th></tr>" + result + "</table>";
        } else {
          result = new String();
        }
      }
    } catch (SQLException e) {
      log.error("getProgress Failure: " + e.toString());
      result = null;
    }
    log.debug("*** END getProgress ***");
    return result;
  }

  /**
   * Use to return the current progress of a class in JSON format with information like user name,
   * score and completed modules
   *
   * @param applicationRoot The current running context of the application
   * @param classId The identifier of the class to use in lookup
   * @return A JSON representation of a class's progress in the application
   */
  @SuppressWarnings("unchecked")
  public static String getProgressJSON(String applicationRoot, String classId) {
    log.debug("*** Getter.getProgressJSON ***");

    String result = new String();
    log.debug("Preparing userProgress call");
    try (Connection conn = Database.getCoreConnection(applicationRoot);
        // Returns User's: Name, # of Completed modules and Score
        CallableStatement callstmnt = conn.prepareCall("call userProgress(?)")) {
      callstmnt.setString(1, classId);
      log.debug("Executing userProgress");
      try (ResultSet resultSet = callstmnt.executeQuery()) {
        JSONArray json = new JSONArray();
        JSONObject jsonInner = new JSONObject();
        int resultAmount = 0;
        while (resultSet.next()) // For each user in a class
        {
          resultAmount++;
          jsonInner = new JSONObject();
          if (resultSet.getString(1) != null) {
            jsonInner.put(
                "userName", new String(Encode.forHtml(resultSet.getString(1)))); // User Name
            jsonInner.put(
                "progressBar",
                Integer.valueOf(resultSet.getInt(2) * widthOfUnitBar)); // Progress Bar
            // Width
            jsonInner.put("score", Integer.valueOf(resultSet.getInt(3))); // Score
            log.debug("Adding: " + jsonInner.toString());
            json.put(jsonInner);
          }
        }
        if (resultAmount > 0) {
          result = json.toString();
        } else {
          result = new String();
        }
      }
    } catch (SQLException e) {
      log.error("getProgressJSON Failure: " + e.toString());
      result = null;
    } catch (Exception e) {
      log.error("getProgressJSON Unexpected Failure: " + e.toString());
      result = null;
    }
    log.debug("*** END getProgressJSON ***");
    return result;
  }

  private static int getTounnamentSectionFromRankNumber(int rankNumber) {
    if (rankNumber < fieldTrainingCap) {
      return 1;
    } else if (rankNumber < privateCap) {
      return 2;
    } else if (rankNumber < corporalCap) {
      return 3;
    } else if (rankNumber < sergeantCap) {
      return 4;
    } else if (rankNumber < lieutenantCap) {
      return 5;
    } else if (rankNumber < majorCap) {
      return 6;
    } else if (rankNumber < admiralCap) {
      return 7;
    } else {
      return 7; // Max level is 7.
    }
  }

  /**
   * This method prepares the Tournament module menu. This is when Security Shepherd is in
   * "Tournament Mode". Users are presented with a list of that are specified as open.
   *
   * @param ApplicationRoot The running context of the application.
   * @param userId The user identifier of the user.
   * @param lang The Locale the user has enabled
   * @return A HTML menu of a users current module progress and a script for interaction with this
   *     menu
   */
  public static String getTournamentModules(String ApplicationRoot, String userId, Locale lang) {
    log.debug("*** Getter.getTournamentModules ***");
    String levelMasterList = new String();
    // Getting Translations
    ResourceBundle bundle = ResourceBundle.getBundle("i18n.text", lang);
    ResourceBundle levelNames = ResourceBundle.getBundle("i18n.moduleGenerics.moduleNames", lang);
    try (Connection conn = Database.getCoreConnection(ApplicationRoot);
        // Get the modules
        CallableStatement callstmt = conn.prepareCall("call moduleTournamentOpenInfo(?)")) {

      String listEntry = new String();
      callstmt.setString(1, userId);
      log.debug("Gathering moduleTournamentOpenInfo ResultSet for user " + userId);
      try (ResultSet levels = callstmt.executeQuery()) {
        log.debug("Opening Result Set from moduleTournamentOpenInfo");
        int currentSection =
            0; // Used to identify the first row, as it is slightly different to all other rows
        // for output
        while (levels.next()) {
          // Create Row Entry First
          // log.debug("Adding " + lessons.getString(1));
          listEntry = "<li>";
          // Markers for completion
          if (levels.getString(4) != null) {
            listEntry += "<img src='css/images/completed.png'/>";
          } else {
            listEntry += "<img src='css/images/uncompleted.png'/>";
          }
          // Prepare entry output
          listEntry +=
              "<a class='lesson' id='"
                  + Encode.forHtmlAttribute(levels.getString(3))
                  + "' href='javascript:;'>"
                  + Encode.forHtml(levelNames.getString(levels.getString(1)))
                  + "</a>\n";
          listEntry += "</li>";
          // What section does this belong in? Current or Next?
          if (getTounnamentSectionFromRankNumber(levels.getInt(5)) > currentSection) {
            // This level is not in the same level band as the previous level. So a new
            // Level Band Header is required on the master list before we add the entry.
            // Do we need to close a previous list?
            if (currentSection
                != 0) // If a Section Select hasn't been made before, we don't need to close any
            // previous sections
            {
              // We've had a section before, so need to close the previous one before we make
              // this new one
              levelMasterList += "</ul>\n";
            }
            // Update the current section to the one we have just added to the list
            currentSection = getTounnamentSectionFromRankNumber(levels.getInt(5));
            // Which to Add?
            switch (currentSection) {
              case 1: // fieldTraining
                // log.debug("Starting Field Training List");
                levelMasterList +=
                    "<a id=\"fieldTrainingList\" href=\"javascript:;\"><div class=\"menuButton\">"
                        + bundle.getString("getter.tournamentRank.1")
                        + "</div></a><ul id=\"theFieldTrainingList\" style=\"display: none;\""
                        + " class='levelList'>\n";
                break;
              case 2: // private
                // log.debug("Starting Private List");
                levelMasterList +=
                    "<a id=\"privateList\" href=\"javascript:;\"><div class=\"menuButton\">"
                        + bundle.getString("getter.tournamentRank.2")
                        + "</div></a>"
                        + "<ul id=\"thePrivateList\" style=\"display: none;\" class='levelList'>\n";
                break;
              case 3: // corporal
                // log.debug("Starting Corporal List");
                levelMasterList +=
                    "<a id=\"corporalList\" href=\"javascript:;\"><div class=\"menuButton\">"
                        + bundle.getString("getter.tournamentRank.3")
                        + "</div></a>"
                        + "<ul id=\"theCorporalList\" style=\"display: none;\" class='levelList'>\n";
                break;
              case 4: // sergeant
                // log.debug("Starting Sergeant List");
                levelMasterList +=
                    "<a id=\"sergeantList\" href=\"javascript:;\"><div class=\"menuButton\">"
                        + bundle.getString("getter.tournamentRank.4")
                        + "</div></a>"
                        + "<ul id=\"theSergeantList\" style=\"display: none;\" class='levelList'>\n";
                break;
              case 5: // Lieutenant
                // log.debug("Starting Lieutenant List");
                levelMasterList +=
                    "<a id=\"lieutenantList\" href=\"javascript:;\"><div class=\"menuButton\">"
                        + bundle.getString("getter.tournamentRank.5")
                        + "</div></a><ul id=\"theLieutenantList\" style=\"display: none;\""
                        + " class='levelList'>\n";
                break;
              case 6: // major
                // log.debug("Starting Major List");
                levelMasterList +=
                    "<a id=\"majorList\" href=\"javascript:;\"><div class=\"menuButton\">"
                        + bundle.getString("getter.tournamentRank.6")
                        + "</div></a>"
                        + "<ul id=\"theMajorList\" style=\"display: none;\" class='levelList'>\n";
                break;
              case 7: // admiral
                // log.debug("Starting Admiral List");
                levelMasterList +=
                    "<a id=\"admiralList\" href=\"javascript:;\"><div class=\"menuButton\">"
                        + bundle.getString("getter.tournamentRank.7")
                        + "</div></a>"
                        + "<ul id=\"theAdmiralList\" style=\"display: none;\" class='levelList'>\n";
                break;
            }
          }
          // Now we can add the entry to the level master List and start again
          levelMasterList += listEntry;
          // log.debug("Put level in category: " + currentSection);
        }
        // If no output has been found, return an error message
        if (levelMasterList.isEmpty()) {
          levelMasterList =
              "<ul><li><a href='javascript:;'>"
                  + bundle.getString("getter.button.noModulesFound")
                  + "</a></li></ul>";
        } else {
          // List is complete, but we need to close the last list we made, which deinfetly
          // exists as the levelmasterList is not empty
          levelMasterList += "</ul>";
          log.debug("Tournament List returned");
        }
      }
    } catch (Exception e) {
      log.error("Tournament List Retrieval: " + e.toString());
    }
    return levelMasterList;
  }

  /**
   * Return all modules in JSON for specific User
   *
   * @param ApplicationRoot The running context of the application.
   * @param userId The user identifier of the user.
   * @param floor The current module plan
   * @param locale The Locale the user has enabled
   * @return
   */
  public static JSONArray getModulesJson(String userId, String floor, Locale locale) {
    log.debug("*** Getter.getModulesJson ***");
    JSONArray jsonOutput = new JSONArray();
    new String();
    try (Connection conn = Database.getCoreConnection()) {
      ResourceBundle.getBundle("i18n.text", locale);
      ResourceBundle levelNames =
          ResourceBundle.getBundle("i18n.moduleGenerics.moduleNames", locale);
      try {
        JSONObject jsonSection = new JSONObject();
        JSONArray jsonSectionModules = new JSONArray();
        JSONObject jsonObject = new JSONObject();
        jsonSection.put("levelMode", floor);
        jsonOutput.put(jsonSection);
        jsonSection = new JSONObject();

        // Get the modules
        try (CallableStatement callstmt = conn.prepareCall("call getMyModules(?)")) {
          callstmt.setString(1, userId);
          log.debug("Gathering getMyModules ResultSet for user " + userId);
          try (ResultSet levels = callstmt.executeQuery()) {
            boolean thisModuleIsOpen =
                true; // If Incremental Mode is enabled, after all the modules that have been
            // completed have been added to the JSON Array the next level will be
            // labeled as open and the rest as closed
            while (levels.next()) {
              jsonObject = new JSONObject();
              boolean moduleCompleted = levels.getString(4) != null;
              jsonObject.put("moduleCompleted", moduleCompleted);
              jsonObject.put("moduleId", levels.getString(3));
              jsonObject.put("moduleType", levels.getString(5));
              jsonObject.put("moduleName", levelNames.getString(levels.getString(1)));
              jsonObject.put(
                  "moduleCategory", levelNames.getString("category." + levels.getString(2)));
              jsonObject.put(
                  "difficultyCategory", getTounnamentSectionFromRankNumber(levels.getInt(7)));
              jsonObject.put("moduleScore", levels.getString(6));
              jsonObject.put("moduleRank", levels.getInt(7));
              jsonObject.put("scoredPoints", levels.getString(8)); // Could be null
              jsonObject.put("medalEarned", levels.getString(9)); // Could be null
              if (ModulePlan.isIncrementalFloor()) {
                boolean moduleOpen;
                if (moduleCompleted
                    || (!moduleCompleted && thisModuleIsOpen)) // If its completed or if this is the
                // first not completed
                {
                  moduleOpen = true;
                  if (!moduleCompleted && thisModuleIsOpen) {
                    log.debug(
                        levelNames.getString(levels.getString(1))
                            + " is the Next Module for user "
                            + userId);
                    thisModuleIsOpen = false; // Stop this from being set again
                  }
                } else {
                  moduleOpen = false;
                }
                jsonObject.put("moduleOpen", moduleOpen);
              }
              jsonSectionModules.put(jsonObject);
            }
            jsonSection.put("modules", jsonSectionModules);
            jsonOutput.put(jsonSection);
          }
        }
      } catch (Exception e) {
        log.error("Module List Retrieval: " + e.toString());
      }
      return jsonOutput;
    } catch (SQLException | IOException e) {
      log.error("Could not connect to core database: " + e.toString());
      throw new RuntimeException(e);
    }
  }

  /**
   * @param ApplicationRoot The current running context of the application
   * @param userName The username of the user
   * @return The class id of the submitted user name
   */
  public static String getUserClassFromName(String ApplicationRoot, String userName) {
    log.debug("*** Getter.getUserClass ***");
    String result = new String();
    userName = userName.toLowerCase();
    try (Connection conn = Database.getCoreConnection(ApplicationRoot);
        CallableStatement callstmt = conn.prepareCall("call userClassId(?)")) {
      log.debug("Gathering userClassId ResultSet");
      callstmt.setString(1, userName);
      try (ResultSet resultSet = callstmt.executeQuery()) {
        log.debug("Opening Result Set from userClassId");
        resultSet.next();
        result = resultSet.getString(1);
        log.debug("Found " + result);
      }
    } catch (SQLException e) {
      log.error("Could not execute userClassId: " + e.toString());
      result = new String();
    }
    log.debug("*** END getUserClass ***");
    return result;
  }

  /**
   * @param ApplicationRoot The current running context of the application
   * @param userName The username of the user
   * @return The user id of the submitted user name
   */
  public static String getUserIdFromName(String ApplicationRoot, String userName) {
    log.debug("*** Getter.getUserIdFromName ***");
    String result = new String();

    userName = userName.toLowerCase();

    try (Connection conn = Database.getCoreConnection(ApplicationRoot);
        CallableStatement callstmt = conn.prepareCall("call userGetIdByName(?)")) {
      log.debug("Gathering userGetIdByName ResultSet");
      callstmt.setString(1, userName);
      try (ResultSet resultSet = callstmt.executeQuery()) {
        log.debug("Opening Result Set from userGetIdByName");
        resultSet.next();
        result = resultSet.getString(1);
      }
    } catch (SQLException e) {
      log.error("Could not execute query: " + e.toString());
      result = null;
    }
    log.debug("*** END getUserIdFromName ***");
    return result;
  }

  /**
   * @param ApplicationRoot The current running context of the application
   * @param userId The identifier of a user
   * @return The user name of the submitted user identifier
   */
  public static String getUserName(String ApplicationRoot, String userId) {
    log.debug("*** Getter.getUserName ***");
    String result = new String();
    try (Connection conn = Database.getCoreConnection(ApplicationRoot);
        CallableStatement callstmt = conn.prepareCall("call userGetNameById(?)")) {
      log.debug("Gathering userGetNameById ResultSet");
      callstmt.setString(1, userId);
      try (ResultSet resultSet = callstmt.executeQuery()) {
        log.debug("Opening Result Set from userGetNameById");
        resultSet.next();
        result = resultSet.getString(1);
      }
    } catch (SQLException e) {
      log.error("Could not execute query: " + e.toString());
      result = null;
    }
    log.debug("*** END getUserName ***");
    return result;
  }

  /**
   * This method is used to determine if a CSRF level has been completed. A call is made to the DB
   * that returns the CSRF counter for a level. If this counter is greater than 0, the level has
   * been completed
   *
   * @param applicationRoot Running context of the application
   * @param moduleId Hash ID of the CSRF module you wish to check if a user has completed
   * @param userId the ID of the user to check
   * @return True or False value depicting if the user has completed the module
   */
  public static boolean isCsrfLevelComplete(
      String applicationRoot, String moduleId, String userId) {
    log.debug("*** Setter.isCsrfLevelComplete ***");

    boolean result = false;

    log.debug("Preparing csrfLevelComplete call");
    try (Connection conn = Database.getCoreConnection(applicationRoot);
        PreparedStatement callstmnt = conn.prepareCall("call csrfLevelComplete(?, ?)")) {
      callstmnt.setString(1, moduleId);
      callstmnt.setString(2, userId);
      log.debug("moduleId: " + moduleId);
      log.debug("userId: " + userId);
      log.debug("Executing csrfLevelComplete");
      try (ResultSet resultSet = callstmnt.executeQuery()) {
        resultSet.next();
        result = resultSet.getInt(1) > 0; // If Result is > 0, then the CSRF level is complete
        if (result) {
          log.debug("CSRF Level is complete");
        }
      }
    } catch (SQLException e) {
      log.error("csrfLevelComplete Failure: " + e.toString());
      result = false;
    }
    log.debug("*** END isCsrfLevelComplete ***");
    return result;
  }

  public static boolean isModuleOpen(String ApplicationRoot, String moduleId) {
    log.debug("*** Getter.isModuleOpen ***");
    boolean result = false;
    try (Connection conn = Database.getCoreConnection(ApplicationRoot);
        // Get the modules
        PreparedStatement prepStmt =
            conn.prepareStatement("SELECT moduleStatus FROM modules WHERE moduleId = ?")) {
      prepStmt.setString(1, moduleId);
      try (ResultSet rs = prepStmt.executeQuery()) {
        if (rs.next()) {
          if (rs.getString(1).equalsIgnoreCase("open")) {
            result = true;
          }
        }
      }
    } catch (Exception e) {
      log.error("isModuleOpen Error: " + e.toString());
    }
    return result;
  }

  /**
   * @param ApplicationRoot The current running context of the application
   * @return Result set containing admin info in the order userId, userName and userAddress
   */
  public static ResultSet getAdmins(String ApplicationRoot) {
    ResultSet result = null;
    log.debug("*** Getter.adminGetAll () ***");
    try (Connection conn = Database.getCoreConnection(ApplicationRoot);
        CallableStatement callstmt = conn.prepareCall("call adminGetAll()")) {
      callstmt.setMaxRows(MAX_ROWSET_ROWS);
      try (ResultSet resultSet = callstmt.executeQuery()) {
        log.debug("Gathering adminGetAll ResultSet");
        CachedRowSet rowSet = RowSetProvider.newFactory().createCachedRowSet();
        rowSet.populate(resultSet);
        rowSet.beforeFirst(); // populate() leaves the cursor after the last row
        result = rowSet;
      }
      log.debug("Returning Result Set from adminGetAll");

    } catch (SQLException e) {
      log.error("Could not execute query: " + e.toString());
      result = null;
    }
    log.debug("*** END adminGetAll ***");
    return result;
  }

  /**
   * Used to decipher whether or not a user exists as an admin
   *
   * @param ApplicationRoot The current running context of the application
   * @param userId The user identifier of the admin to be found
   * @return A boolean reflecting the state of existence of the admin
   */
  public static boolean findAdminById(String ApplicationRoot, String userId) {
    log.debug("*** Getter.findAdminById ***");
    boolean userFound = false;
    // Get connection
    try (Connection conn = Database.getCoreConnection(ApplicationRoot);
        CallableStatement callstmt = conn.prepareCall("call adminFindById(?)")) {
      log.debug("Gathering adminFindById ResultSet");
      callstmt.setString(1, userId);
      try (ResultSet userFind = callstmt.executeQuery()) {
        log.debug("Opening Result Set from adminFindById");
        userFind.next(); // This will throw an exception if player not found
        log.debug(
            "Admin Found: "
                + userFind.getString(1)); // This line will not execute if admin not found
        userFound = true;
      }
    } catch (Exception e) {
      log.error("Admin does not exist: " + e.toString());
      userFound = false;
    }
    log.debug("*** END findAdminById ***");
    return userFound;
  }

  public static boolean getAdminCheatStatus(String ApplicationRoot) throws SQLException {
    boolean adminCheatStatus = false;
    log.debug("*** Getter.getAdminCheatStatus ***");

    try (Connection conn = Database.getCoreConnection(ApplicationRoot);
        PreparedStatement callstmt =
            conn.prepareStatement("SELECT value FROM settings WHERE setting= ?")) {

      log.debug("Getting admin cheat setting");
      callstmt.setString(1, "adminCheatsEnabled");

      try (ResultSet cheatResult = callstmt.executeQuery()) {
        cheatResult.next();
        adminCheatStatus = cheatResult.getBoolean(1);
        log.debug("Value found: " + adminCheatStatus);
      }

      log.debug("*** END getAdminCheatStatus ***");
      return adminCheatStatus;
    }
  }

  public static boolean getPlayerCheatStatus(String ApplicationRoot) throws SQLException {
    boolean getPlayerCheatStatus = false;
    log.debug("*** Getter.getPlayerCheatStatus ***");

    try (Connection conn = Database.getCoreConnection(ApplicationRoot);
        PreparedStatement callstmt =
            conn.prepareStatement("SELECT value FROM settings WHERE setting= ?")) {

      log.debug("Getting player cheat setting");
      callstmt.setString(1, "playerCheatsEnabled");

      try (ResultSet cheatResult = callstmt.executeQuery()) {
        cheatResult.next();

        getPlayerCheatStatus = cheatResult.getBoolean(1);

        log.debug("Value found: " + getPlayerCheatStatus);

        log.debug("*** END getPlayerCheatStatus ***");
        return getPlayerCheatStatus;
      }
    }
  }

  public static String getModuleLayout(String ApplicationRoot) throws SQLException {
    String theModuleLayout = "";
    log.debug("*** Getter.getModuleLayout ***");

    try (Connection conn = Database.getCoreConnection(ApplicationRoot);
        PreparedStatement callstmt =
            conn.prepareStatement("SELECT value FROM settings WHERE setting= ?")) {

      log.debug("Getting module layout setting");
      callstmt.setString(1, "moduleLayout");

      try (ResultSet layoutResult = callstmt.executeQuery()) {
        layoutResult.next();

        theModuleLayout = layoutResult.getString(1);

        log.debug("Value found: " + theModuleLayout);

        log.debug("*** END getModuleLayout ***");
        return theModuleLayout;
      }
    }
  }

  public static boolean getFeedbackStatus(String ApplicationRoot) throws SQLException {
    boolean theFeedbackStatus = false;
    log.debug("*** Getter.getFeedbackStatus ***");

    try (Connection conn = Database.getCoreConnection(ApplicationRoot);
        PreparedStatement callstmt =
            conn.prepareStatement("SELECT value FROM settings WHERE setting= ?")) {

      log.debug("Getting feedback status setting");
      callstmt.setString(1, "enableFeedback");

      try (ResultSet feedbackResult = callstmt.executeQuery()) {
        feedbackResult.next();

        theFeedbackStatus = feedbackResult.getBoolean(1);

        log.debug("Value found: " + theFeedbackStatus);

        log.debug("*** END getFeedbackStatus ***");
        return theFeedbackStatus;
      }
    }
  }

  public static boolean getRegistrationStatus(String ApplicationRoot) throws SQLException {
    boolean theRegistrationStatus = false;
    log.debug("*** Getter.getRegistrationStatus ***");

    try (Connection conn = Database.getCoreConnection(ApplicationRoot);
        PreparedStatement callstmt =
            conn.prepareStatement("SELECT value FROM settings WHERE setting= ?")) {

      log.debug("Getting registration status setting");
      callstmt.setString(1, "openRegistration");

      try (ResultSet registrationResult = callstmt.executeQuery()) {
        registrationResult.next();

        theRegistrationStatus = registrationResult.getBoolean(1);

        log.debug("Value found: " + theRegistrationStatus);

        log.debug("*** END getRegistrationStatus ***");
        return theRegistrationStatus;
      }
    }
  }

  public static String getScoreboardStatus(String ApplicationRoot) throws SQLException {
    String theScoreboardStatus = "";
    log.debug("*** Getter.getScoreboardStatus ***");

    try (Connection conn = Database.getCoreConnection(ApplicationRoot);
        PreparedStatement callstmt =
            conn.prepareStatement("SELECT value FROM settings WHERE setting= ?")) {

      log.debug("Setting scoreboard status setting");
      callstmt.setString(1, "scoreboardStatus");

      try (ResultSet scoreboardResult = callstmt.executeQuery()) {
        scoreboardResult.next();

        theScoreboardStatus = scoreboardResult.getString(1);

        log.debug("Value found: " + theScoreboardStatus);

        log.debug("*** END getScoreboardStatus ***");
        return theScoreboardStatus;
      }
    }
  }

  public static String getScoreboardClass(String ApplicationRoot) throws SQLException {
    String theScoreboardClass = "";
    log.debug("*** Getter.getScoreboardClass ***");

    try (Connection conn = Database.getCoreConnection(ApplicationRoot);
        PreparedStatement callstmt =
            conn.prepareStatement("SELECT value FROM settings WHERE setting= ?")) {

      log.debug("Getting scoreboard class setting");
      callstmt.setString(1, "scoreboardClass");

      try (ResultSet scoreboardResult = callstmt.executeQuery()) {
        scoreboardResult.next();

        theScoreboardClass = scoreboardResult.getString(1);

        log.debug("Value found: " + theScoreboardClass);

        log.debug("*** END getScoreboardClass ***");
        return theScoreboardClass;
      }
    }
  }

  public static Boolean getStartTimeStatus(String ApplicationRoot) throws SQLException {
    Boolean theStartTimeStatus = null;
    log.debug("*** Getter.getStartTimeStatus ***");

    try (Connection conn = Database.getCoreConnection(ApplicationRoot);
        PreparedStatement callstmt =
            conn.prepareStatement("SELECT value FROM settings WHERE setting= ?")) {

      log.debug("Getting start time setting");
      callstmt.setString(1, "hasStartTime");

      try (ResultSet timestampResult = callstmt.executeQuery()) {
        timestampResult.next();

        theStartTimeStatus = timestampResult.getBoolean(1);

        log.debug("Value found: " + theStartTimeStatus);

        log.debug("*** END getStartTimeStatus ***");
        return theStartTimeStatus;
      }
    }
  }

  public static LocalDateTime getStartTime(String ApplicationRoot) throws SQLException {
    LocalDateTime theStartTimeStatus = null;
    log.debug("*** Getter.getStartTimeStatus ***");

    try (Connection conn = Database.getCoreConnection(ApplicationRoot);
        PreparedStatement callstmt =
            conn.prepareStatement("SELECT value FROM settings WHERE setting= ?")) {

      log.debug("Getting start time");
      callstmt.setString(1, "startTime");

      try (ResultSet timestampResult = callstmt.executeQuery()) {
        timestampResult.next();

        String dateTimeString = timestampResult.getString(1);

        log.debug("Value found: " + dateTimeString);

        theStartTimeStatus = LocalDateTime.parse(dateTimeString);

        log.debug("*** END getStartTime ***");
        return theStartTimeStatus;
      }
    }
  }

  public static Boolean getLockTimeStatus(String ApplicationRoot) throws SQLException {
    Boolean theLockTimeStatus = null;
    log.debug("*** Getter.getLockTimeStatus ***");

    try (Connection conn = Database.getCoreConnection(ApplicationRoot);
        PreparedStatement callstmt =
            conn.prepareStatement("SELECT value FROM settings WHERE setting= ?")) {

      log.debug("Getting lock time setting");
      callstmt.setString(1, "hasLockTime");

      try (ResultSet timestampResult = callstmt.executeQuery()) {
        timestampResult.next();

        theLockTimeStatus = timestampResult.getBoolean(1);

        log.debug("Value found: " + theLockTimeStatus);

        log.debug("*** END getLockTimeStatus ***");
        return theLockTimeStatus;
      }
    }
  }

  public static LocalDateTime getLockTime(String ApplicationRoot) throws SQLException {
    LocalDateTime theLockTimeStatus = null;
    log.debug("*** Getter.getLockTimeStatus ***");

    try (Connection conn = Database.getCoreConnection(ApplicationRoot);
        PreparedStatement callstmt =
            conn.prepareStatement("SELECT value FROM settings WHERE setting= ?")) {

      log.debug("Getting lock time");
      callstmt.setString(1, "lockTime");

      try (ResultSet timestampResult = callstmt.executeQuery()) {
        timestampResult.next();

        String dateTimeString = timestampResult.getString(1);

        log.debug("Value found: " + dateTimeString);

        theLockTimeStatus = LocalDateTime.parse(dateTimeString);

        log.debug("*** END getLockTime ***");
        return theLockTimeStatus;
      }
    }
  }

  public static Boolean getEndTimeStatus(String ApplicationRoot) throws SQLException {
    Boolean theEndTimeStatus = null;
    log.debug("*** Getter.getEndTimeStatus ***");

    try (Connection conn = Database.getCoreConnection(ApplicationRoot);
        PreparedStatement callstmt =
            conn.prepareStatement("SELECT value FROM settings WHERE setting= ?")) {

      log.debug("Getting end time setting");
      callstmt.setString(1, "hasEndTime");

      try (ResultSet timestampResult = callstmt.executeQuery()) {
        timestampResult.next();

        theEndTimeStatus = timestampResult.getBoolean(1);

        log.debug("Value found: " + theEndTimeStatus);

        log.debug("*** END getEndTimeStatus ***");
        return theEndTimeStatus;
      }
    }
  }

  public static LocalDateTime getEndTime(String ApplicationRoot) throws SQLException {
    LocalDateTime theEndTimeStatus = null;
    log.debug("*** Getter.getEndTimeStatus ***");

    try (Connection conn = Database.getCoreConnection(ApplicationRoot);
        PreparedStatement callstmt =
            conn.prepareStatement("SELECT value FROM settings WHERE setting= ?")) {

      log.debug("Getting end time");
      callstmt.setString(1, "endTime");

      try (ResultSet timestampResult = callstmt.executeQuery()) {
        timestampResult.next();

        String dateTimeString = timestampResult.getString(1);

        log.debug("Value found: " + dateTimeString);

        theEndTimeStatus = LocalDateTime.parse(dateTimeString);

        log.debug("*** END getEndTime ***");
        return theEndTimeStatus;
      }
    }
  }

  public static String getDefaultClass(String ApplicationRoot) throws SQLException {
    String theDefaultClass = null;
    log.debug("*** Getter.getDefaultClass ***");

    try (Connection conn = Database.getCoreConnection(ApplicationRoot);
        PreparedStatement callstmt =
            conn.prepareStatement("SELECT value FROM settings WHERE setting= ?")) {

      log.debug("Getting default class");
      callstmt.setString(1, "defaultClass");

      try (ResultSet classResult = callstmt.executeQuery()) {
        classResult.next();

        theDefaultClass = classResult.getString(1);

        log.debug("Value found: " + theDefaultClass);

        log.debug("*** END getDefaultClass ***");
        return theDefaultClass;
      }
    }
  }
}