CREATE OR REPLACE PACKAGE BODY SHIFT_PACKAGE AS

PROCEDURE update_staff_shift
    (p_first_name IN VARCHAR2, p_last_name IN VARCHAR2, p_code IN NUMBER)
IS
    employee_id F_STAFFS.id%TYPE;
    shift_code F_SHIFTS.code%TYPE;
    no_assigned_shift EXCEPTION;
    PRAGMA EXCEPTION_INIT (no_assigned_shift, -20001);
BEGIN
    SELECT id INTO employee_id FROM f_staffs WHERE first_name = p_first_name AND last_name = p_last_name;
    SELECT code INTO shift_code FROM f_shifts WHERE code = p_code;
    UPDATE f_shift_assignments SET code = p_code, SHIFT_ASSIGN_DATE = SYSDATE WHERE id = employee_id;
    IF sql%rowcount = 0 THEN
       RAISE no_assigned_shift;
    END IF;
EXCEPTION
    WHEN no_assigned_shift THEN
        log_error(SQLCODE, 'Cannot update shift. No assigned shift found.');
        dbms_output.put_line('Employee has no assigned shift. You must first assign employee to shift
            by calling procedure assign_staff_to_shift(first_name, last_name, description) before you can
            change it.');
    WHEN no_data_found THEN
        IF employee_id IS NULL THEN
            log_error(SQLCODE, 'Employee with given name does not exist.');
            dbms_output.put_line('Employee with given name does not exist.');
        ELSE
            log_error(SQLCODE, 'Shift with given code does not exist.');
            dbms_output.put_line('Shift with given code does not exist.');
        END IF;
    WHEN OTHERS THEN
        dbms_output.put_line('Shift could not be changed.');
END;


PROCEDURE add_new_shift (
    p_description IN VARCHAR2 ) IS
shifts_count IN NUMBER;
new_code IN NUMBER;
BEGIN
    SELECT COUNT(*) INTO shifts_count FROM f_shifts;
    IF shifts_count > 0 THEN
        SELECT MAX(code) INTO new_code FROM f_shifts;
        new_code := new_code + 1;
    ELSE
        new_code := 1;
    END IF;
    INSERT INTO f_shifts (code, description) VALUES (new_code, p_description);

EXCEPTION
    WHEN OTHERS THEN
        log_error(SQLCODE, 'Shift could not be added. ' || SQLERRM);
        dbms_output.put_line('Shift could not be added.');
END;

PROCEDURE assign_staff_to_shift (
    p_first_name IN VARCHAR2,
    p_last_name IN VARCHAR2,
    p_description IN VARCHAR2)
IS
    employee_id F_STAFFS.id%TYPE;
    shift_code F_SHIFTS.code%TYPE;
    assigned_shifts NUMBER;
    already_assigned EXCEPTION;
    PRAGMA exception_init (already_assigned, -20002);
BEGIN
    SELECT id INTO employee_id FROM f_staffs WHERE first_name = p_first_name AND last_name = p_last_name;
    SELECT code INTO shift_code FROM f_shifts WHERE description = p_description;
    SELECT COUNT(*) INTO assigned_shifts FROM f_shift_assignments WHERE id = employee_id;
    IF assigned_shifts > 0 THEN
        RAISE already_assigned;
    ELSE
        INSERT INTO f_shift_assignments (id,code,shift_assign_date) VALUES(employee_id,shift_code,SYSDATE);
    END IF;
EXCEPTION
    WHEN already_assigned THEN
        log_error(SQLCODE, 'Employee with given name has already been assigned to shift.');
        dbms_output.put_line('Employee with given name has already been assigned to shift.');
        dbms_output.put_line('You can now only change shift using function update_staff_shift(first_name, last_name, code).');
    WHEN no_data_found THEN
        IF employee_id IS NULL THEN
            log_error(SQLCODE, 'Employee with given name does not exist.');
            dbms_output.put_line('Employee with given name does not exist.');
        ELSE
            log_error(SQLCODE, 'Shift with given description does not exist.');
            dbms_output.put_line('Shift with given description does not exist.');
        END IF;
    WHEN OTHERS THEN
        log_error(SQLCODE, 'Shift could not be assigned to employee.' || SQLERRM);
        dbms_output.put_line('Shift could not be assigned to employee.');
END ;

FUNCTION count_shift (
    p_code IN NUMBER )
    RETURN NUMBER
IS
    shift_count NUMBER;
BEGIN
     SELECT COUNT(*) INTO shift_count FROM staff_shift_changes WHERE new_code = p_code;
     RETURN shift_count;
END;

FUNCTION count_shift (
    p_code IN NUMBER,
    p_id IN NUMBER,
    p_start_date IN DATE,
    p_end_date IN DATE
    )
    RETURN NUMBER
IS
shift_count NUMBER;
BEGIN
     SELECT COUNT(*) INTO shift_count FROM staff_shift_changes WHERE id = p_id AND time_stamp >= p_start_date
        AND time_stamp <= p_end_date AND new_code = p_code;
     RETURN shift_count;
END;

PROCEDURE find_shifts (
    p_id IN NUMBER,
    p_start_date IN DATE := NULL,
    p_end_date IN DATE := NULL)
IS
    employee f_staffs%ROWTYPE;
    first_day DATE;
    LAST_DAY DATE;
    CURSOR shifts IS SELECT DISTINCT new_code FROM staff_shift_changes WHERE id = p_id;
    shift_desc f_shifts.description%TYPE;
BEGIN
    SELECT * INTO employee FROM f_staffs WHERE id = p_id;
    IF p_start_date IS NOT NULL AND p_end_date IS NOT NULL THEN
         dbms_output.put_line('Employee ' || employee.first_name || ' ' || employee.last_name || ' worked on these shifts during ' || p_start_date || ' - ' || p_end_date || ': ');
        dbms_output.put_line('');

        FOR shift IN shifts LOOP
            SELECT description INTO shift_desc FROM f_shifts WHERE code = shift.new_code;

            dbms_output.put_line('Shift: ' || shift_desc || ' - ' || count_shift(shift.new_code, employee.id, '1.1.1900', p_end_date) || ' time(s).');

            END LOOP;

    ELSIF p_end_date IS NOT NULL THEN
        dbms_output.put_line('Employee ' || employee.first_name || ' ' || employee.last_name || ' worked on these shifts till ' || p_end_date || ': ');
        dbms_output.put_line('');

        FOR shift IN shifts LOOP
            SELECT description INTO shift_desc FROM f_shifts WHERE code = shift.new_code;

            dbms_output.put_line('Shift: ' || shift_desc || ' - ' || count_shift(shift.new_code, employee.id, '1.1.1900', p_end_date) || ' time(s).');

            END LOOP;
    ELSIF p_start_date IS NOT NULL THEN
        dbms_output.put_line('Employee ' || employee.first_name || ' ' || employee.last_name || ' has worked on these shifts since '
            || p_start_date || ': ');
        dbms_output.put_line('');

        FOR shift IN shifts LOOP
            SELECT description INTO shift_desc FROM f_shifts WHERE code = shift.new_code;

            dbms_output.put_line('Shift: ' || shift_desc || ' - ' || count_shift(shift.new_code, employee.id, p_start_date, SYSDATE) || ' time(s).');

            END LOOP;
    ELSE
        SELECT TRUNC(SYSDATE,'Year') INTO first_day FROM dual;
        SELECT ADD_MONTHS(TRUNC(SYSDATE,'Year'),12)-1 INTO LAST_DAY FROM dual;
        dbms_output.put_line('Employee ' || employee.first_name || ' ' || employee.last_name || ' worked on these shifts this year: ');
        dbms_output.put_line('');

        FOR shift IN shifts LOOP
            SELECT description INTO shift_desc FROM f_shifts WHERE code = shift.new_code;

            dbms_output.put_line('Shift: ' || shift_desc || ' - ' || count_shift(shift.new_code, employee.id, first_day, LAST_DAY) || ' time(s).');

            END LOOP;
    END IF;


EXCEPTION
    WHEN no_data_found THEN
        log_error(SQLCODE, 'Employee with given name does not exist.');
        dbms_output.put_line('Employee with given name does not exist.');

END;

PROCEDURE log_error (
    p_code IN NUMBER,
    p_message IN VARCHAR2
    ) IS
BEGIN
    INSERT INTO error_log (e_user, e_date, error_code, error_message) VALUES (SYS_CONTEXT('USERENV', 'CURRENT_USER'), SYSDATE, p_code, p_message);
END;


END;