package com.example.scheduler_server;

import java.sql.*;

/*
    Name: Staff
    Description: Responsible for sending queries and receiving responses from the database that deal with the
    Employee table.
 */
public class Staff {

    // Object that facilitates the connection to the database
    private final Connection dbConnection;

    /*
    Name: Staff Constructor
    Parameters: None
    Description: Establishes connections to the local database. If running on a different computer
    make sure that the port number (replace '3306'), database name (replace 'ScheduleApp'), user, and password are
    set correctly.
    Return: Staff Object
     */
    protected Staff(Connection c) {
        this.dbConnection = c;
    }

    /*
    Name: addEmployee
    Parameters:
        String firstName: First name of the employee,
        String lastName: Last name of the employee .
    Description: Adds an employee by querying the database. Also, assigns a unique ID.
    Return:
        String newEmployee: If the employee was added successfully, then it returns their new details, else returns an empty string.
     */
    protected String addEmployee(String firstName, String lastName, String isManager, String email, String phoneNumber,
                                 float wage){
        String newEmployee = "";
        try {
            Statement myStatement = dbConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            myStatement.executeUpdate("insert into Employees (first_name,last_name,isManager,email,phoneNumber,wage)" +
                    " values ('" + firstName + "','" + lastName + "'," + isManager + ",'" + email + "','" +
                    phoneNumber + "'," + wage + ")");
            ResultSet myRs = myStatement.executeQuery("select * from Employees where first_name='Rafi' and last_name='Zereselasie'");
            if (myRs.last()) {
                newEmployee = myRs.getString("employee_ID") + "," + myRs.getString("first_name") +
                        "," + myRs.getString("last_name") + "," + myRs.getString("isManager") +
                        "," + myRs.getString("email") + "," + myRs.getString("phoneNumber") +
                        "," + myRs.getString("wage");
            }
        } catch (Exception exception) {
            exception.printStackTrace();
            return newEmployee;
        }
        System.out.println("Employee Added");
        return newEmployee;
    }

    /*
    Name: removeEmployee
    Parameters:
        int ID: The employees unique ID.
    Description: Removes the employee with the same ID by querying the database.
    Return: none
     */
    protected int removeEmployee(int ID){
        try {
            Statement myStatement = dbConnection.createStatement();
            myStatement.executeUpdate("delete from Employees where employee_id = " + ID);
        } catch (Exception exception) {
            exception.printStackTrace();
            return -1;
        }
        System.out.println("Employee Removed");
        return ID;
    }

    /*
    Name: printEmployees
    Parameters: none
    Description: prints the id, and names of each employee
    Return: none
     */
    protected String allEmployees(){
        StringBuilder response = new StringBuilder("allEmployees");
        try {
            Statement myStatement = dbConnection.createStatement();
            ResultSet myRs = myStatement.executeQuery("select * from Employees");
            while (myRs.next()) {
                response.append("/").append(myRs.getString("employee_ID")).append(",")
                        .append(myRs.getString("first_name")).append(",")
                        .append(myRs.getString("last_name")).append(",")
                        .append(myRs.getString("isManager")).append(",")
                        .append(myRs.getString("email")).append(",")
                        .append(myRs.getString("phoneNumber")).append(",")
                        .append(myRs.getString("wage"));
            }
        } catch (Exception exception) {
            exception.printStackTrace();
            return "Error retrieving employees from database";
        }
        return response.toString();
    }

}