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 {

    private final Connection dbConnection;

    protected Positions(Connection c) {
        this.dbConnection = c;
    }

    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);
            newPosition = myRs.getString("position") + "/" + myRs.getString("wage");
        } catch (SQLException exception) {
            exception.printStackTrace();
            return newPosition;
        }
        return newPosition;
    }

    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);
            editedPosition = myRs.getString("position") + "/" + myRs.getString("wage");
        } catch (SQLException exception) {
            exception.printStackTrace();
            return editedPosition;
        }
         return editedPosition;
    }

    protected void 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();
        }
    }

    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();
    }
}