package com.example.schedulerapp;

import java.sql.*;

public class Schedule {

    private final Connection dbConnection;

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

    /*
    Name: addShift
    Parameters:
        int ID: The employees unique ID.
        Date: The date as a string in the form of "yyyy-mm-dd".
        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, String date, int start, int end){
        Date newDate = Date.valueOf(date);
        try {
            Statement myStatement = dbConnection.createStatement();
            myStatement.executeUpdate("INSERT INTO shifts (employee_id, full_date, start_time, end_time) " +
                    "VALUES ('" + ID +"', '"+ newDate +"', '"+ start +"', '"+ end +"')");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /*
    Name: removeShiftByID
    Parameters:
        int shiftID: The shift unique ID.
    Description: Removes the employees shift matching the shift date.
    Return: none
     */
    protected void removeShiftByID(String shiftID){
        try {
            Statement myStatement = dbConnection.createStatement();
            myStatement.executeUpdate("DELETE FROM shifts WHERE (shift_id =" + shiftID + ")");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    protected void editShift(Integer shift_id, String date, String start,String end){
        Date newDate = Date.valueOf(date);
        System.out.println(newDate);
        try {
            Statement myStatement = dbConnection.createStatement();
            myStatement.executeUpdate("update shifts set full_date='" + newDate + "',start_time=" + start + ",end_time=" + end
                    + " where shift_id=" + shift_id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /*
    Name: getShifts
    Parameters:
        Date: The date as a string in the form of "yyyy-mm-dd".
    Description: Returns info for all shifts on a given date.
    Return: String
     */
    protected String getShifts(String date){
        Date newDate = Date.valueOf(date);
        String response = "getShifts";
        try {
            Statement myStatement = dbConnection.createStatement();
            ResultSet myRs = myStatement.executeQuery("SELECT FROM shifts WHERE (full_date = " + newDate +")");
            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(String startDate, String endDate){
        Date newStart = Date.valueOf(startDate);
        Date newEnd = Date.valueOf(endDate);
        String response = "getShiftsByWeek";
        try {
            Statement myStatement = dbConnection.createStatement();
            ResultSet myRs = myStatement.executeQuery("SELECT FROM shifts WHERE (full_date >= " + newStart +")" +
                    "AND WHERE (full_date <= " + newEnd +")");
            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, String startDate, String endDate){
        Date newStart = Date.valueOf(startDate);
        Date newEnd = Date.valueOf(endDate);
        String response = "getEmployeeShiftsByWeek";
        try {
            Statement myStatement = dbConnection.createStatement();
            ResultSet myRs = myStatement.executeQuery("SELECT FROM shifts WHERE (full_date >= " + newStart +")" +
                    "AND WHERE (full_date <= " + newEnd +") 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(){
        String response = "allShifts";
        try {
            Statement myStatement = dbConnection.createStatement();
            ResultSet myRs = myStatement.executeQuery("SELECT * FROM shifts");
            while (myRs.next()) {
                response = response + "/" + myRs.getString("employee_id" ) + "." +
                        myRs.getString("full_date") + "." + myRs.getString("start_time") +
                        "." + myRs.getString("end_time") + "." + myRs.getString("shift_id");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return response;
    }
}