package com.example.scheduler_server;

import java.sql.*;

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

    private final Connection dbConnection;

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

    protected String newEmployeeAvailability(int employeeID){
        String newAvailability = "";
        try {
            Statement myStatement = dbConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            myStatement.executeUpdate("INSERT INTO TimeOff (employeeID) " +
                    "VALUES ('" + employeeID + "')");
            ResultSet myRs = myStatement.executeQuery("select * from TimeOff where employeeID=" + employeeID);
            if (myRs.last()) {
                newAvailability = myRs.getString("employeeID") +
                        "," + myRs.getString("sundayStart") + "," + myRs.getString("sundayEnd") +
                        "," + myRs.getString("mondayStart") + "," + myRs.getString("mondayEnd") +
                        "," + myRs.getString("tuesdayStart") + "," + myRs.getString("tuesdayEnd") +
                        "," + myRs.getString("wednesdayStart") + "," + myRs.getString("wednesdayEnd") +
                        "," + myRs.getString("thursdayStart") + "," + myRs.getString("thursdayEnd") +
                        "," + myRs.getString("fridayStart") + "," + myRs.getString("fridayEnd") +
                        "," + myRs.getString("saturdayStart") + "," + myRs.getString("saturdayEnd");
            }
        } catch (SQLException exception) {
            exception.printStackTrace();
            return newAvailability;
        }
        System.out.println("Shift successfully added");
        return newAvailability;
    }

    protected void removeEmployeeAvailability(int employeeID){
        try {
            Statement myStatement = dbConnection.createStatement();
            myStatement.executeUpdate("DELETE FROM Availabilities WHERE (employeeID =" + employeeID + ")");
        } catch (SQLException exception) {
            exception.printStackTrace();
        }
    }

    protected String editAvailability(int employeeID, int dayOfTheWeek, int startTime, int endTime){
        String editedAvailability = "";
        String dayOfTheWeekString = "";
        try {
            Statement myStatement = dbConnection.createStatement();
            switch (employeeID) {
                case 0 -> dayOfTheWeekString = "sunday";
                case 1 -> dayOfTheWeekString = "monday";
                case 2 -> dayOfTheWeekString = "tuesday";
                case 3 -> dayOfTheWeekString = "wednesday";
                case 4 -> dayOfTheWeekString = "thursday";
                case 5 -> dayOfTheWeekString = "friday";
                case 6 -> dayOfTheWeekString = "saturday";
            }
            if (dayOfTheWeekString.isEmpty()) {
                return editedAvailability;
            }
            myStatement.executeUpdate("update Availabilities set " + dayOfTheWeekString + "Start=" + startTime +
                    ", " + dayOfTheWeekString + "End=" + endTime + " where employeeID=" + employeeID);

            ResultSet myRs = myStatement.executeQuery("select * from Availabilities where employeeID=" + employeeID);

            editedAvailability = myRs.getString("employeeID" ) + "," + myRs.getString("sundayStart") +
                    "," + myRs.getString("sundayEnd") + "," + myRs.getString("mondayStart") +
                    "," + myRs.getString("mondayEnd") + "," + myRs.getString("tuesdayStart") +
                    "," + myRs.getString("tuesdayEnd") + "," + myRs.getString("wednesdayStart") +
                    "," + myRs.getString("wednesdayEnd") + "," + myRs.getString("thursdayStart") +
                    "," + myRs.getString("thursdayEnd") + "," + myRs.getString("fridayStart") +
                    "," + myRs.getString("fridayEnd") + "," + myRs.getString("saturdayStart") +
                    "," + myRs.getString("saturdayEnd");
        } catch (SQLException exception) {
            exception.printStackTrace();
            return editedAvailability;
        }
        return editedAvailability;
    }

    protected String allAvailabilities(){
        StringBuilder response = new StringBuilder("allAvailabilities");
        try {
            Statement myStatement = dbConnection.createStatement();
            ResultSet myRs = myStatement.executeQuery("SELECT * FROM shifts");
            while (myRs.next()) {
                response.append("/").append(myRs.getString("employeeID"))
                        .append(",").append(myRs.getString("sundayStart"))
                        .append(",").append(myRs.getString("sundayEnd"))
                        .append(",").append(myRs.getString("mondayStart"))
                        .append(",").append(myRs.getString("mondayEnd"))
                        .append(",").append(myRs.getString("tuesdayStart"))
                        .append(",").append(myRs.getString("tuesdayEnd"))
                        .append(",").append(myRs.getString("wednesdayStart"))
                        .append(",").append(myRs.getString("wednesdayEnd"))
                        .append(",").append(myRs.getString("thursdayStart"))
                        .append(",").append(myRs.getString("thursdayEnd"))
                        .append(",").append(myRs.getString("fridayStart"))
                        .append(",").append(myRs.getString("fridayEnd"))
                        .append(",").append(myRs.getString("saturdayStart"))
                        .append(",").append(myRs.getString("saturdayEnd"));
            }
        } catch (SQLException exception) {
            exception.printStackTrace();
        }
        return response.toString();
    }
}