package com.example.scheduler_server;

import java.sql.*;
import java.util.Locale;

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

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

    /*
    Name: Positions Constructor
    Parameters:
        Connection c: The connection to the database.
    Description: Instantiates a Positions object and sets the global connection for the methods.
    Return: Positions Object
     */
    protected Positions(Connection c) {
        this.dbConnection = c;
    }

    /*
    Name: addPosition
    Parameters:
        String position: A position to add.
        float wage: The wage of the position.
    Description: Adding a new position and wage to the table of possible positions.
    Return:
        String: If the positions was successfully added then the position and the wage separated by a slash is returned,
        otherwise an empty string is returned.
     */
    protected String addPosition(String position, float wage){
        String newPosition = "";
        try {
            Statement myStatement = dbConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            myStatement.executeUpdate("insert into Positions (position, wage) " +
                    "values ('" + position.toLowerCase(Locale.ROOT) +"', "+ wage + ")");
            ResultSet myRs = myStatement.executeQuery("select * from Positions where position = '" + position + "'");
            myRs.next();
            newPosition = myRs.getString("position") + "/" + myRs.getString("wage");
        } catch (SQLException exception) {
            exception.printStackTrace();
            return newPosition;
        }
        return newPosition;
    }

    /*
    Name: editPosition
    Parameters:
        String position: A position.
        float wage: The new wage of the position.
    Description: Changes the wage of an existing position.
    Return:
        String: If the positions was successfully edited then the position and the wage separated by a slash is returned,
        otherwise an empty string is returned.
     */
    protected String editPosition(String position, float newWage){
        String editedPosition = "";
        try {
            Statement myStatement = dbConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            myStatement.executeUpdate("update Positions set wage=" + newWage +
                    "where position='" + position.toLowerCase(Locale.ROOT) +"'");
            ResultSet myRs = myStatement.executeQuery("select * from Positions where position = '" + position + "'");
            myRs.next();
            editedPosition = myRs.getString("position") + "/" + myRs.getString("wage");
        } catch (SQLException exception) {
            exception.printStackTrace();
            return editedPosition;
        }
         return editedPosition;
    }

    /*
    Name: removePosition
    Parameters:
        String position: A position.
    Description: Removes a position and wage from the table of possible positions.
    Return:
        String: None
     */
    protected String removePosition(String position){
        try {
            Statement myStatement = dbConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            myStatement.executeUpdate("delete from Positions " +
                    "where position = '" + position.toLowerCase(Locale.ROOT) + "'");
        } catch (SQLException exception) {
            exception.printStackTrace();
            return "";
        }
        return position;
    }

    /*
    Name: allPositions
    Parameters:
    Description: Gets all the positions and wages.
    Return:
        String: Each position was separated by slashes and each detail is separated by commas.
     */
    protected String allPositions(){
        StringBuilder response = new StringBuilder("allPositions");
        try {
            Statement myStatement = dbConnection.createStatement();
            ResultSet myRs = myStatement.executeQuery("select * from Positions");
            while (myRs.next()) {
                response.append("/").append(myRs.getString("position"))
                        .append(",").append(myRs.getString("wage"));
            }
        } catch (SQLException exception) {
            exception.printStackTrace();
        }
        return response.toString();
    }

    // Positions testing suite
    // This test suite must be started with an empty Positions table.
    public static void main(String[] args) {
        try {
            Connection dbConnection = DriverManager.getConnection("jdbc:mysql://localhost:3306/ScheduleApp", "root", "password");
            Positions positions = new Positions(dbConnection);
            String queryString;
            int success = 0;
            System.out.println("Positions Testing Suite:");

            System.out.println("\nPositions Test 1: ");
            System.out.println("Test Scenario: " + "Testing allPositions() with no positions in the table");
            System.out.println("Test Data: " + "none");
            System.out.println("Expected Results: " + "allPositions");
            queryString = positions.allPositions();
            System.out.println("Actual Results: " + queryString);
            if (queryString.equals("allPositions")){
                success += 1;
                System.out.println("Pass/Fail: Pass");
            } else {
                System.out.println("Pass/Fail: Fail");
            }

            System.out.println("\nPositions Test 2: ");
            System.out.println("Test Scenario: " + "Testing addPosition()");
            System.out.println("Test Data: " + "position='cashier', wage=15.30f");
            System.out.println("Expected Results: " + "cashier/15.3");
            queryString = positions.addPosition("cashier", 15.30f);
            System.out.println("Actual Results: " + queryString);
            if (queryString.equals("cashier/15.3")){
                success += 1;
                System.out.println("Pass/Fail: Pass");
            } else {
                System.out.println("Pass/Fail: Fail");
            }

            System.out.println("\nPositions Test 3: ");
            System.out.println("Test Scenario: " + "Testing editPosition()");
            System.out.println("Test Data: " + "position='cashier', wage=20.0f'");
            System.out.println("Expected Results: " + "cashier/20.0");
            queryString = positions.editPosition("cashier", 20.0f);
            System.out.println("Actual Results: " + queryString);
            if (queryString.equals("cashier/20.0")){
                success += 1;
                System.out.println("Pass/Fail: Pass");
            } else {
                System.out.println("Pass/Fail: Fail");
            }

            System.out.println("\nPositions Test 4: ");
            System.out.println("Test Scenario: " + "Testing allPositions() with positions in the table");
            System.out.println("Test Data: " + "none");
            System.out.println("Expected Results: " + "allPositions/cashier,20.0");
            queryString = positions.allPositions();
            System.out.println("Actual Results: " + queryString);
            if (queryString.equals("allPositions/cashier,20.0")){
                success += 1;
                System.out.println("Pass/Fail: Pass");
            } else {
                System.out.println("Pass/Fail: Fail");
            }

            System.out.println("\nPositions Test 5: ");
            System.out.println("Test Scenario: " + "Testing removePosition()");
            System.out.println("Test Data: " + "position='cashier'");
            System.out.println("Expected Results: " + "allPositions");
            positions.removePosition("cashier");
            queryString = positions.allPositions();
            System.out.println("Actual Results: " + queryString);
            if (queryString.equals("allPositions")){
                success += 1;
                System.out.println("Pass/Fail: Pass");
            } else {
                System.out.println("Pass/Fail: Fail");
            }

            System.out.println("\nPositions Unit Testing: Passed " + success + "/5.");
        } catch (Exception exception) {
            exception.printStackTrace();
        }
    }
}