/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package pat2021;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *The StudentSQL Class is used to create SQL Queries for the table TBLUSERS
 * @author bee.ntsimane
 */

//Finding all the users 
public class StudentSQL {
 
    private DBClass db = new DBClass();
    
    private Student [] userArr = new Student [1000];
    private int size = 0; 
    
    /**
     *
     */
    public StudentSQL()
    {
        findUsers();
    }
    
    /**
     * selects all of the users from the TBLUSERS table
     */
    public void findUsers()
    {
        String sql = "SELECT * FROM TBLUSERS";
        
        try
        {

            ResultSet rs = db.query(sql);
            while(rs.next())
            {
                
                int userId = rs.getInt("USERID");
                String username = rs.getString("USERNAME");
                String password = rs.getString("PASSWORD");
                String matric = rs.getString("MATRICYEAR");
                String email = rs.getString("EMAIL");
                
              
                userArr[size] = new Student(userId, username, password, matric, email);
                size++;
            }
                  
        }
        catch(SQLException e)
        {
            System.out.println("Error processing " + sql);
            System.out.println(e);
        }
        
        
    }
    
    /**
     * verifies user credentials against data stored in database ie. checks the students' username and password
     * @param un - the username entered by the user
     * @param pswd - the password entered by the user
     * @return a student object of the student with the corresponding username and password
     */
    public Student checkStudentPassword( String un, String pswd)
    {
        
        
        Student student = null;
        
        String stmt = "SELECT * FROM TBLUSERS WHERE USERNAME = '" + un + "' ";
       
        
        try
        {
           
           ResultSet rs = db.query(stmt);
           

            
            if(rs.next())
            {

               System.out.println("YAY");
               String pw = rs.getString("PASSWORD");
               String matric = rs.getString("MATRICYEAR");
               String email = rs.getString("EMAIL");
               
               if(pw.equals(pswd))
               {
                //create User object
                int uid = rs.getInt("USERID");
                student = new Student(uid,un, pswd,matric, email);
                   System.out.println("Passwords match");
                
                   
               }
            }
           return student;
            
        }catch(SQLException ex)
            {
              System.out.println("Invalid user/password combination");
              System.out.println(ex);
              return null;
                }
        
       
    }
    
    /**
     * A method that adds a new student to the database using the information entered by the user
     * @param uname - the username entered into the text field
     * @param pw - the password entered into the text field
     * @param matric - the matriculation year entered into the text field
     * @param em - the email address entered into the text field
     * @return a message indicating whether student was successfully added or not
     */
    public String addStudent(String uname, String pw, String matric, String em)
    {
        
        //not sure about userID
        String stmt = "INSERT INTO tblUsers ( USERNAME, PASSWORD, MATRICYEAR, EMAIL)" + "VALUES ('" + uname +"','" + pw + "','" +
         matric + "','" +  em+"')";
        System.out.println(stmt);
        
        try {
            db.update(stmt);
            return "Successful!";
            
            
            
        } catch (SQLException ex) {
            System.out.println("Error accessing tblUsers");
            System.out.println(ex);
            return "Error";
        }
        
    }
    
    /**
     * A  method which deletes the selected student record
     * @param userID - the userID of the student to be deleted
     * @return a message indicating whether deletion was successful
     */
    public String deleteStudent (int userID )
    {
    String stmt = "DELETE FROM TBLUSERS WHERE USERID =" + userID;
    String stmt2 = "DELETE FROM TBLSTUDENTSUBJECTMARKS WHERE USERID =" + userID;
    
        try {
            db.update(stmt);
            db.update(stmt2);
            return "Successful!";
        } catch (SQLException ex) {
            System.out.println("Error accessing Students Table");
            System.out.println(ex);
            return "Error";
        }
    }
    
    /**
     * A method that retrieves all of the data from the TBLUSERS table
     * @return a ResultSet of all the student data in the TBLUSERS table
     */
    public ResultSet getAllStudents()
    {
       
     String stmt = "SELECT * FROM TBLUSERS";
        try {
            ResultSet rs = db.query(stmt);
            return rs;
            
        } catch (SQLException ex) {
            System.out.println("Error accessing TBLUSERS");
            return null;
        }
    
    }
    
    /**
     *
     * A method that retrieves all of the data from the TBLUSERS table
     * @return a ResultSet of all the student data in the TBLUSERS table
     */
    public ResultSet getAllStudents2()
    {
        String stmt = "SELECT USERID, USERNAME, PASSWORD, MATRICYEAR, EMAIL FROM TBLUSERS";
        System.out.println(stmt);
        
        try {
            ResultSet rs = db.query(stmt);
            return rs;
            
        } catch (SQLException ex) {
            System.out.println("Error accessing TBLUSERS");
            return null;
        }
        
    }
    
       

    /**
     *A method that retrieves a specific student from the TBLUSERS table
     * @param userID - the userID of the specific student seeked 
     * @return - a student object of the searched-for student
     */
    public Student getStudent(int userID)
    {
        String stmt = "SELECT * FROM TBLUSERS WHERE USERID = " + userID;
        System.out.println(stmt);
        ResultSet rs;
        Student student = null;

        try {
            rs = db.query(stmt);
            if(rs.next())
            {
                //extract elements to populate the student record
                int userId = rs.getInt("USERID");
                String username = rs.getString("USERNAME");
                String password = rs.getString("PASSWORD");
                String matric = rs.getString("MATRICYEAR");
                String email = rs.getString("EMAIL");
                
                student = new Student(userId, username, password, matric, email);
                
            }
            return student;
            
        } catch (SQLException ex) {
            System.out.println("Error accessing student");
            System.out.println(ex);
            return null;
        }
    }
    
    public ResultSet getStudentRecord(int userID)
    {
        ResultSet rs;
        String stmt = "SELECT * FROM TBLUSERS WHERE USERID = " + userID;
        System.out.println(stmt);
        
        try {
            rs = db.query(stmt);
            
        } catch (SQLException ex) {
            System.out.println("Error accessing TBLUSERS - refer to getStudentRecord method"
                    + ex);
            rs = null;
            
        }
        return rs;
    }
    
    //method used to find studentID using username
    public int getStudentID(String uname)   
    {
        String stmt = "SELECT USERID FROM TBLUSERS WHERE USERNAME = '" + uname + "'";
        System.out.println(stmt);
        
        ResultSet rs;
          int userID = -1;
        
        try {
            rs = db.query(stmt);
            System.out.println("ResultSet:" + rs.toString());
            if(rs.next())
            {
                 userID = rs.getInt("USERID");
                 System.out.println("userID: " +userID);
            }
            return userID;
            
        } catch (SQLException ex) {
            System.out.println("Error accessing TBLUSERS from getStudentID");
            System.out.println(ex);
            return userID;
        }
    }
    


    
    
    

    

    


    
    
    
    
    
    
    
    
    

}
