package com.example.schedulerapp;

import java.sql.*;

public class Schedule {

    private Connection dbConnection;

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

    /*
    Name: addShift
    Parameters:
        int ID: The employees unique ID.
        Date: The date as a Java SQL date object.
        start: The employees shift start time.
        end: The employees shift end time.
    Description: Adds a new shift to the database, referencing the employees ID, and assigning a unique shift number.
    Return: none
     */
    protected void addShift(int ID, Date date, int start, int end){
        try {
            Statement myStatement = dbConnection.createStatement();
            myStatement.executeUpdate("INSERT INTO shifts (employee_id, full_date, start_time, end_time) " +
                    "VALUES ('" + ID +"', '"+ date +"', '"+ start +"', '"+ end +"')");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    /*
    Name: removeShift
    Parameters:
        int ID: The employees unique ID.
        Date: The date as a Java SQL date object.
        start: The employees shift start time.
        end: The employees shift end time.
    Description: Removes the employees shift matching the date, and shift duration.
    Return: none
     */
    protected void removeShift(String ID, Date date, int start, int end){
        try {
            Statement myStatement = dbConnection.createStatement();
            myStatement.executeUpdate("DELETE FROM shifts WHERE (employee_id =" + ID + ") " +
                    "AND WHERE (date = " + date +") AND  WHERE (start_time = " + start + " ) " +
                    "AND WHERE (end_time = " + end +")");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /*
    Name: getShifts
    Parameters:
        Date: The date as a Java SQL date object.
    Description: Returns info for all shifts on a given date.
    Return: String
     */
    protected String getShifts(Date date){
        String response = "getShifts";
        try {
            Statement myStatement = dbConnection.createStatement();
            ResultSet myRs = myStatement.executeQuery("SELECT FROM shifts WHERE (full_date = " + date +")");
            while (myRs.next()) {
                response = response + "/" + myRs.getString("full_date") + "." +
                myRs.getString("employee_id") + "." + myRs.getString("start_time") +
                "." + myRs.getString("end_time");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return response;
    }

    protected String getShiftsByWeek(Date startDate, Date endDate){
        String response = "getShiftsByWeek";
        try {
            Statement myStatement = dbConnection.createStatement();
            ResultSet myRs = myStatement.executeQuery("SELECT FROM shifts WHERE (full_date >= " + startDate +")" +
                    "AND WHERE (full_date <= " + endDate +")");
            while (myRs.next()) {
                response = response + "/" + myRs.getString("full_date") + "." +
                        myRs.getString("employee_id") + "." + myRs.getString("start_time") +
                        "." + myRs.getString("end_time");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return response;
    }

    protected String getEmployeeShiftsByWeek(int ID, Date startDate, Date endDate){
        String response = "getEmployeeShiftsByWeek";
        try {
            Statement myStatement = dbConnection.createStatement();
            ResultSet myRs = myStatement.executeQuery("SELECT FROM shifts WHERE (full_date >= " + startDate +")" +
                    "AND WHERE (full_date <= " + endDate +") AND WHERE (employee_id = "+ ID + ")");
            while (myRs.next()) {
                response = response + "/" + myRs.getString("full_date") + "." +
                        myRs.getString("employee_id") + "." + myRs.getString("start_time") +
                        "." + myRs.getString("end_time");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return response;
    }

    protected String allShifts(){
        return "All Shifts";
    }
}