UserHelper

package databaseHelpers;


import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import userTypes.USER;

public class UserHelper extends DatabaseHelper {

    /**
     * Creates an ObservableList containing every user in the user table
     * @return
     */
    public ObservableList<USER> getAllUsers() {
        ObservableList<USER> list = FXCollections.observableArrayList();
        try {
            con = DriverManager.getConnection(url, username, dbPassword);
            Statement stmt = con.createStatement();
            rs = stmt.executeQuery("SELECT * FROM user");

            while (rs.next()) {
                // assigning the user_id to the the id pulled from database
                int user_id = rs.getInt("id");
                // assigning the first name to the the first_name pulled from
                // database
                String firstName = rs.getString("first_name");
                // assigning the last name to the the last_name pulled from
                // database
                String lastName = rs.getString("last_name");

                String userType = rs.getString("user_type").toUpperCase();

                String userPassword = rs.getString("password");

                //Not new record, so it cannot be true when checking login
                boolean newRecord = false;

            list.add(new USER(user_id, firstName, lastName, userPassword, userType, newRecord));

            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return list;

    }

    /**
     * Gets user details based on ID
     * @param userID
     * @return
     */
    public USER getUser(int userID) {
        USER user = null;
        try {
            con = DriverManager.getConnection(url, username, dbPassword);
            Statement stmt = con.createStatement();
            rs = stmt.executeQuery("SELECT * FROM user WHERE id = " + userID);

            while (rs.next()) {
                // assigning the user_id to the the id pulled from database
                int user_id = rs.getInt("id");
                // assigning the first name to the the first_name pulled from
                // database
                String firstName = rs.getString("first_name");
                // assigning the last name to the the last_name pulled from
                // database
                String lastName = rs.getString("last_name");

                String userPassword = rs.getString("password");

                String userType = rs.getString("user_type").toUpperCase();



                //Not new record, so it cannot be true when checking login
                boolean newRecord = false;

                user = new USER(user_id, firstName, lastName, userPassword, userType, newRecord);

            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return user;

    }


    /**
     * Adds a user to the user table
     * @param fName
     * @param sName
     * @param password
     */
    public void addUser(String fName, String sName, String password, String type) {
        executeQuery("INSERT INTO user VALUES(null, '" + fName + "', '" + sName + "', '" + password + "', '" + type +"')");


    }

    /**
     * Get the latest User ID
     * @return
     */
    public int getLatestUser(){
        int latestUserID = 0;
        try {
            con = DriverManager.getConnection(url, username, dbPassword);
            Statement stmt = con.createStatement();
            rs = stmt.executeQuery("SELECT MAX(id) FROM user");
            rs.next();
            latestUserID = rs.getInt(1);

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return latestUserID;

    }

    /**
     * Creates an entry in the enrols table for the newest added student under the specified teacher id
     * @param teacherID
     */
    public void logEnrolment(int teacherID, int studentID){
        executeQuery("INSERT INTO enrols VALUES("+teacherID+", "+studentID+")");
    }



    /**
     * Removes a user from the user table
     * @param userID
     */
    public void removeUser(int userID) {
        executeQuery("DELETE FROM user WHERE id = +" + userID);
    }

    /**
     * Updates a user in the user table
     * @param userID
     */
    public void updateUser(int userID, String firstName, String lastName, String password){
        executeQuery("UPDATE user SET first_name = '"+firstName+"', "+ "last_name = '"+lastName+"', password = '"+password+"' WHERE id = +" + userID);
    }


}