Come up with 100% coverage for unit test and integration testing for my newly added controller routes, ensuring boundary test cases are covered as well. Integrate it together with my existing test cases, so do not modify the setup and teardown: 
1. @wfh_bp.route('/manager-schedule-summary/<int:manager_id>', methods=['GET'])
def manager_schedule_summary(manager_id):

2. @wfh_bp.route('/manager-schedule-detail/<int:manager_id>/<date>', methods=['GET'])
def manager_schedule_detail(manager_id, date):


And my new services:
1.     @staticmethod
    def get_manager_schedule_summary(manager_id, start_date, end_date):
2.     @staticmethod
    def get_manager_schedule_detail(manager_id, date):

These is the code context:
File: backend\app\controllers\staff_controller.py
--------------------------------------------------------------------------------
# app/controllers/staff_controller.py
from flask import Blueprint, request, jsonify
from flask import abort
from app.services.staff_service import StaffService

staff_bp = Blueprint('staff', __name__, url_prefix='/api')

@staff_bp.route('/login', methods=['POST'])
def login():
    data = request.get_json()

    if not data or not data.get('email') or not data.get('password'):
        return jsonify({"message": "Missing email or password"}), 400

    staff = StaffService.authenticate_staff(data['email'], data['password'])

    if not staff:
        return jsonify({"message": "Invalid email or password"}), 401
    
    staff_details = staff.to_dict()
    print(staff_details)
    return jsonify({
        "message": "Login successful",
        **staff_details
    }), 200

@staff_bp.route('/staff/<int:staff_id>', methods=['GET'])
def get_staff_by_id(staff_id):
    staff = StaffService.get_staff_by_id(staff_id)
    if not staff:
        abort(404, description="Staff not found")
    return jsonify(staff.to_dict()), 200


File: backend\app\controllers\wfh_controller.py
--------------------------------------------------------------------------------
from flask import Blueprint, request, jsonify
from app.services.wfh_request_service import WFHRequestService
from app.services.wfh_schedule_service import WFHScheduleService
from app.services.wfh_check_service import WFHCheckService
from app.models.wfh_request import WFHRequest
from datetime import datetime, timedelta, date
from app import db

wfh_bp = Blueprint('wfh', __name__, url_prefix='/api')

@wfh_bp.route('/request', methods=['POST'])
def create_wfh_request():
    print("\n===== NEW WFH REQUEST =====")
    print("Received a new WFH request")
    data = request.get_json()

    # Validate input
    print("\n----- Input Validation -----")
    required_fields = ['staff_id', 'manager_id', 'reason_for_applying', 
                       'date', 'duration', 'dept', 'position']
    for field in required_fields:
        if field not in data:
            print(f"Validation failed: Missing required field: {field}")
            return jsonify({"message": f"Missing required field: {field}"}), 400
    print("Input validation successful")

    try:
        # Get today's date for the request_date
        today = datetime.now().date()

        # Create WFHRequest
        print("\n----- Creating WFH Request -----")
        end_date = None
        if 'end_date' in data and data['end_date']:
            if(not isinstance(data['end_date'], date)):
                end_date = datetime.strptime(data['end_date'], '%Y-%m-%d').date()

        wfh_request = WFHRequestService.create_request(
            staff_id=data['staff_id'],
            manager_id=data['manager_id'],
            request_date=today,
            start_date=data['date'],
            end_date=end_date,
            reason_for_applying=data['reason_for_applying']
        )
        print(f"WFH request created successfully. Request ID: {wfh_request.request_id}")

        # Create WFH Schedules
        print("\n----- Creating WFH Schedules -----")
        if(not isinstance(data['date'], date)):
            start_date = datetime.strptime(data['date'], '%Y-%m-%d').date()
        wfh_schedules = WFHScheduleService.create_schedule(
            request_id=wfh_request.request_id,
            staff_id=data['staff_id'],
            manager_id=data['manager_id'],
            start_date=start_date,
            end_date=end_date,
            duration=data['duration'],
            dept=data['dept'],
            position=data['position']
        )
        print(f"WFH schedules created successfully. Number of schedules: {len(wfh_schedules)}")

        print("\n===== WFH REQUEST COMPLETED =====")
        print("WFH request and schedules creation completed successfully")
        print(f"Request ID: {wfh_request.request_id}")
        print(f"Number of Schedules: {len(wfh_schedules)}")
        print(f"Status: {wfh_request.status}")
        print("==================================\n")

        return jsonify({
            "message": "WFH request and schedules created successfully",
            "request_id": wfh_request.request_id,
            "schedule_count": len(wfh_schedules),
            "status": wfh_request.status
        }), 201

    except ValueError as ve:
        print(f"\n===== ERROR OCCURRED =====")
        print(f"Validation failed: {ve}")
        print("============================\n")
        db.session.rollback()
        return jsonify({"message": str(ve)}), 400
        
    except Exception as e:
        db.session.rollback()
        print("\n===== ERROR OCCURRED =====")
        print(f"An error occurred while processing the WFH request: {str(e)}")
        print("============================\n")
        db.session.rollback()
        return jsonify({"message": f"An error occurred: {str(e)}"}), 500

@wfh_bp.route('/pending-requests/<int:manager_id>', methods=['GET'])
def get_pending_requests(manager_id):
    print(f"\n===== GET PENDING REQUESTS =====")
    print(f"Retrieving pending requests for manager_id: {manager_id}")
    
    try:
        print("Calling WFHRequestService.get_pending_requests_for_manager()")
        pending_requests = WFHRequestService.get_pending_requests_for_manager(manager_id)
        
        print(f"Number of pending requests retrieved: {len(pending_requests)}")
        
        response = [request.to_dict() for request in pending_requests]
        print("Successfully converted requests to dictionary format")
        
        print("===== GET PENDING REQUESTS COMPLETED =====\n")
        return jsonify(response), 200
    
    except Exception as e:
        print(f"ERROR: An exception occurred while retrieving pending requests")
        print(f"Exception details: {str(e)}")
        print("===== GET PENDING REQUESTS FAILED =====\n")
        return jsonify({"message": f"An error occurred: {str(e)}"}), 500


@wfh_bp.route('/update-request', methods=['PATCH'])
def update_wfh_request():
    print(f"\n===== UPDATE REQUESTS =====")
    data = request.get_json()
    request_id = data['request_id']
    new_request_status = data['request_status']
    reason = data['reason']

    # Get the current date
    current_date = datetime.now().date()
    # Calculate the date 2 months ago
    two_months_ago = current_date - timedelta(days=60)

    print(f"Updating request for request_id: {request_id}")
    
    try:
        # Fetch the request
        request_obj = WFHRequest.query.get(request_id)
        if not request_obj:
            return jsonify({"message": "Request does not exist"}), 404

        if new_request_status == 'APPROVED':
            staff_id = request_obj.staff_id
            start_date = request_obj.start_date
            end_date = request_obj.end_date

            dates_to_check = []

            if end_date:
                # Recurring request
                current_date_iter = start_date
                while current_date_iter <= end_date:
                    if current_date_iter >= current_date:
                        dates_to_check.append(current_date_iter)
                    current_date_iter += timedelta(days=7)
            else:
                # Single date request
                if start_date >= current_date:
                    dates_to_check.append(start_date)
            violated_dates = []
            # Check WFH policy for each date
            for date_to_check in dates_to_check:
                result = WFHCheckService.check_department_count(staff_id, date_to_check)
                if result != 'Success':
                    violated_dates.append(date_to_check)
            
            if len(violated_dates) > 0:
                formatted_dates = ",".join([d.strftime("%d-%m-%Y") for d in violated_dates])
                return jsonify({"message": f"Cannot approve request due to policy violation on date(s) {formatted_dates}"}), 400

        print("Calling WFHRequestService.update_request()")
        response = WFHRequestService.update_request(
            request_id, new_request_status, two_months_ago, reason)
        if response == True:
            response2 = WFHScheduleService.update_schedule(request_id, new_request_status)

            if response2 == True:
                print("Successfully updated")
                print("===== UPDATE PENDING REQUESTS COMPLETED =====\n")
                return jsonify(f"Successfully updated request {request_id} as {new_request_status}"), 200
                
            else:
                return jsonify({"message": response2}), 404
                
        else:
            return jsonify({"message": response}), 404

    except Exception as e:
        db.session.rollback()
        print("\n===== ERROR OCCURRED =====")
        print(f"An error occurred while processing the WFH request: {str(e)}")
        print("============================\n")
        return jsonify({"message": f"An error occurred: {str(e)}"}), 500


@wfh_bp.route('/reject-expired-request', methods=['POST'])
def reject_expired_request():
    """
    Check the current date against the start date in the database rows.
    If the start date is older than 2 months, update the status to 'REJECTED'.
    """
    try:
        # Get the current date
        current_date = datetime.now().date()

        # Calculate the date 2 months ago
        two_months_ago = current_date - timedelta(days=60)

        # Fetch all requests from the database
        expired = WFHRequestService.reject_expired(two_months_ago)
        return jsonify({"message": f"Updated {expired} requests to 'REJECTED'."}), 200

    except Exception as e:
        db.session.rollback()  # Rollback the session in case of an error
        return jsonify({"message": f"An error occurred: {str(e)}"}), 500
    
@wfh_bp.route('/check-wfh-count', methods=['POST'])
def check_wfh_count():

    data = request.get_json()
    staff_id = data['staff_id']
    date = data['date']
    
    try:
        result = WFHCheckService.check_department_count(staff_id, date)
        return('done')
        print('done')
    except Exception as e:
        db.session.rollback()  # Rollback the session in case of an error
        return jsonify({"message": f"An error occurred: {str(e)}"}), 500


@wfh_bp.route('/manager-schedule-summary/<int:manager_id>', methods=['GET'])
def manager_schedule_summary(manager_id):
    try:
        # Get start_date and end_date from query params, else use default range
        start_date = request.args.get('start_date')
        end_date = request.args.get('end_date')
        today = datetime.now().date()
        if start_date:
            start_date = datetime.strptime(start_date, '%Y-%m-%d').date()
        else:
            start_date = today - timedelta(days=60)  # 2 months before today

        if end_date:
            end_date = datetime.strptime(end_date, '%Y-%m-%d').date()
        else:
            end_date = today + timedelta(days=90)  # 3 months after today

        data = WFHScheduleService.get_manager_schedule_summary(manager_id, start_date, end_date)
        return jsonify(data), 200

    except Exception as e:
        print(f"Error in manager_schedule_summary: {str(e)}")
        return jsonify({"message": f"An error occurred: {str(e)}"}), 500

@wfh_bp.route('/manager-schedule-detail/<int:manager_id>/<date>', methods=['GET'])
def manager_schedule_detail(manager_id, date):
    try:
        date_obj = datetime.strptime(date, '%Y-%m-%d').date()
        data = WFHScheduleService.get_manager_schedule_detail(manager_id, date_obj)
        return jsonify(data), 200
    except Exception as e:
        print(f"Error in manager_schedule_detail: {str(e)}")
        return jsonify({"message": f"An error occurred: {str(e)}"}), 500


File: backend\app\models\staff.py
--------------------------------------------------------------------------------
from app import db

class Staff(db.Model):
    __tablename__ = 'Staff'

    staff_id = db.Column(db.Integer, primary_key=True)
    staff_fname = db.Column(db.String(255), nullable=False)
    staff_lname = db.Column(db.String(255), nullable=False)
    dept = db.Column(db.String(255), nullable=True)
    position = db.Column(db.String(255), nullable=True)
    country = db.Column(db.String(255), nullable=True)
    email = db.Column(db.String(255), nullable=False, unique=True)
    reporting_manager = db.Column(db.Integer, db.ForeignKey('Staff.staff_id'), nullable=True)
    role = db.Column(db.Integer, nullable=True)
    password = db.Column(db.String(255), nullable=True)

    def to_dict(self):
        return {
            'staff_id': self.staff_id,
            'staff_fname': self.staff_fname,
            'staff_lname': self.staff_lname,
            'dept': self.dept,
            'position': self.position,
            'country': self.country,
            'email': self.email,
            'reporting_manager': self.reporting_manager,
            'role': self.role
        }


File: backend\app\models\wfh_request.py
--------------------------------------------------------------------------------
from app import db
from sqlalchemy.sql import expression

class WFHRequest(db.Model):
    __tablename__ = 'WFHRequest'

    request_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    staff_id = db.Column(db.Integer, db.ForeignKey('Staff.staff_id'), nullable=False)
    manager_id = db.Column(db.Integer, db.ForeignKey('Staff.staff_id'), nullable=False)
    request_date = db.Column(db.Date, nullable=False)
    start_date = db.Column(db.Date, nullable=False)
    end_date = db.Column(db.Date, nullable=True)
    status = db.Column(db.String(20), nullable=False, server_default=expression.text("'PENDING'"))
    reason_for_applying = db.Column(db.Text, nullable=False)
    reason_for_rejection = db.Column(db.Text, nullable=True)

    def to_dict(self):
        end_date = None
        if self.end_date:
            end_date = self.end_date.isoformat()

        return {
            'request_id': self.request_id,
            'staff_id': self.staff_id,
            'manager_id': self.manager_id,
            'request_date': self.request_date.isoformat(),
            'start_date': self.start_date.isoformat(),
            'end_date': end_date,
            'status': self.status,
            'reason_for_applying': self.reason_for_applying,
            'reason_for_rejection': self.reason_for_rejection,
            'is_recurring': self.end_date is not None
        }


File: backend\app\models\wfh_schedule.py
--------------------------------------------------------------------------------
from app import db
from sqlalchemy.sql import expression

class WFHSchedule(db.Model):
    __tablename__ = 'WFHSchedule'

    schedule_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    request_id = db.Column(db.Integer, db.ForeignKey('WFHRequest.request_id'), nullable=False)
    staff_id = db.Column(db.Integer, db.ForeignKey('Staff.staff_id'), nullable=False)
    manager_id = db.Column(db.Integer, db.ForeignKey('Staff.staff_id'), nullable=False)
    date = db.Column(db.Date, nullable=False)
    duration = db.Column(db.String(20), nullable=False)
    status = db.Column(db.String(20), nullable=False, server_default=expression.text("'PENDING'"))
    dept = db.Column(db.String(50), nullable=False)
    position = db.Column(db.String(50), nullable=False)
    reason_for_withdrawing = db.Column(db.Text, nullable=True)
    

    def to_dict(self):
        return {
            'schedule_id': self.schedule_id,
            'request_id': self.request_id,
            'staff_id': self.staff_id,
            'manager_id': self.manager_id,
            'date': self.date.isoformat(),
            'duration': self.duration,
            'status': self.status,
            'dept': self.dept,
            'position': self.position,
            'reason_for_withdrawing': self.reason_for_withdrawing
        }


File: backend\app\services\staff_service.py
--------------------------------------------------------------------------------
from app.models.staff import Staff
from sqlalchemy.exc import SQLAlchemyError

class StaffService:
    @staticmethod
    def authenticate_staff(email, password):
        try:
            staff = Staff.query.filter_by(email=email).first()
            if staff and staff.password == password:
                return staff
            else:
                print("Wrong email or password")
                return None
            
        except SQLAlchemyError as e:
            print(f"Database error during authentication: {str(e)}")
            raise


    @staticmethod
    def get_staff_by_id(staff_id):
        try:
            staff = Staff.query.get(staff_id)
            if staff is None:
                raise ValueError(f"No staff found with id: {staff_id}")
            return staff
        except SQLAlchemyError as e:
            print(f"Database error while fetching staff by ID: {str(e)}")
            raise
        except ValueError as e:
            print(str(e))
            raise

File: backend\app\services\wfh_check_service.py
--------------------------------------------------------------------------------
from app import db
from app.models.staff import Staff
from app.models.wfh_schedule import WFHSchedule
from app.services.staff_service import StaffService

class WFHCheckService:
    @staticmethod
    def check_department_count(staff_id, date):
        # identify the staff department
        department = StaffService.get_staff_by_id(staff_id).dept

        # find out how many people in that department
        department_count = WFHCheckService.department_count(department)

        # now, find out how many people in that department also have approved WFH on that date
        applied_count = 0

        # get all the schedules with the same date and status 'APPROVED' or 'PENDING'
        schedules = db.session.query(WFHSchedule).filter(
            WFHSchedule.date == date,
            WFHSchedule.status == 'APPROVED')
        
        for schedule in schedules:
            # get the department of the schedule's staff
            schedule_staff = StaffService.get_staff_by_id(schedule.staff_id)
            schedule_department = schedule_staff.dept

            # if department is same as the one applying, increase counter
            if schedule_department == department:
                applied_count += 1

        # Calculate the percentage of staff working from home, if including this request
        wfh_percentage = (applied_count +1) / department_count

        # If more than 50% are working from home, return an error
        if wfh_percentage > 0.5:
            print(f"Max limit for Dept: {department} on date: {date}")
            return 'Unable to apply due to max limit'
        else:
            return 'Success'

    @staticmethod
    def department_count(department):
        staff_count = db.session.query(Staff).filter_by(dept=department).count()
        return staff_count
        
    



File: backend\app\services\wfh_request_service.py
--------------------------------------------------------------------------------
from app import db
from app.models.wfh_request import WFHRequest
from datetime import datetime, timedelta, date

class WFHRequestService:
    @staticmethod
    def create_request(staff_id, manager_id, request_date, start_date, end_date, reason_for_applying):
        error_message = None
        # Check if the start date is valid (within 2 months before or 3 months after today)
        max_valid_date = datetime.now().date() + timedelta(days=90)
        min_valid_date = datetime.now().date() - timedelta(days=60)
        if(not isinstance(start_date, date)):
            start_date = datetime.strptime(start_date, "%Y-%m-%d").date()


        if start_date < min_valid_date or start_date > max_valid_date:
            raise ValueError("Start date must be between 2 months ago and 3 months from now.")
            

        # Check if the end date is valid for recurring requests
        
        if end_date:
            if(not isinstance(end_date, date)):
                print("try2")
                print(type(end_date))
                print(isinstance(end_date, date))
                end_date = datetime.strptime(end_date, "%Y-%m-%d").date()
                print("try2")

            if end_date < min_valid_date or end_date > max_valid_date:
                raise ValueError("End date must be between 2 months ago and 3 months from now.")
            # print(type(end_date))
            # print(type(start_date))
            
            if start_date >= end_date:
                raise ValueError("End date must be after start date.")

        # Check if there's an existing request for the same day
        existing_request = WFHRequest.query.filter(
            WFHRequest.staff_id == staff_id,
            WFHRequest.start_date == start_date,
            WFHRequest.status != 'EXPIRED'
        ).first()

        if existing_request and (not end_date) and existing_request.status != 'REJECTED':
            
            raise ValueError("A request for this date already exists.")

        new_request = WFHRequest(
            staff_id=staff_id,
            manager_id=manager_id,
            request_date=request_date,
            start_date=start_date,
            end_date=end_date,
            reason_for_applying=reason_for_applying
        )
        db.session.add(new_request)
        db.session.commit()
        return new_request
    
    @staticmethod
    def get_pending_requests_for_manager(manager_id):
        return WFHRequest.query.filter_by(manager_id=manager_id, status='PENDING').all()
    

    @staticmethod
    def update_request(request_id, new_request_status, two_months_ago, reason):   #new_request_status = approved / rejected
        # Fetch the request by its ID
        request = WFHRequest.query.get(request_id)
        
        # Check if the request exists
        if request:
            
            # Check if within date range
            request_date = request.start_date
            if WFHRequestService.check_date(request_date , two_months_ago):

                # Update the status field
                request.status = new_request_status

                # provide reason for reject
                if new_request_status == 'REJECTED':
                    request.reason_for_rejection = reason

            # not within date range = not suppose to approve
            else:
                return "The date is invalid to be approved"
            
            # Commit the updated record to the database
            db.session.commit()

            # happy path 
            return True
        
        else:

            # Request does not exist
            return "Request Does not Exist!"
        
    @staticmethod
    def reject_expired(two_months_ago):
        # Fetch all requests from the database
        requests = WFHRequest.query.all()
        updated_count = 0

        # Check each request
        for request in requests:

            # Check if date range is within 2 months and only update those that has not been updated
            if (WFHRequestService.check_date(request.start_date , two_months_ago) != True) and request.status!= 'REJECTED':
                request.status = 'REJECTED'  # Update status to rejected
                request.reason_for_rejection = "Rejected due to past time period" 
                updated_count += 1

        # Commit the changes to the database
        db.session.commit()
        return updated_count
    

    @staticmethod
    def check_date(request_date , two_months_ago):
        if request_date > two_months_ago:
            return True
        else:
            return False

File: backend\app\services\wfh_schedule_service.py
--------------------------------------------------------------------------------
from app import db
from app.models.wfh_schedule import WFHSchedule
from app.models.wfh_request import WFHRequest
from app.models.staff import Staff
from datetime import timedelta

class WFHScheduleService:
    @staticmethod
    def create_schedule(request_id, staff_id, manager_id, start_date, end_date, duration, dept, position):
        schedules = []
        current_date = start_date
        
        while True:

            existing_schedule = WFHSchedule.query.filter(
                WFHSchedule.staff_id == staff_id,
                WFHSchedule.date == current_date,
                WFHSchedule.status != 'EXPIRED'
            ).first()
            
            if existing_schedule and existing_schedule.status != 'REJECTED':
                print(f"Schedule for {current_date} already exists")
                current_date += timedelta(days=7)  # Move to the next week
                if end_date is None or current_date > end_date:
                    break  # If no end_date or we've passed the end_date, stop creating schedule
                continue

            new_schedule = WFHSchedule(
                request_id=request_id,
                staff_id=staff_id,
                manager_id=manager_id,
                date=current_date,
                duration=duration,
                dept=dept,
                position=position
            )
            db.session.add(new_schedule)
            schedules.append(new_schedule)
            print(f"Schedule for {current_date} created successfully")

            current_date += timedelta(days=7)  # Move to the next week
            if end_date is None or current_date > end_date:
                break  # If no end_date or we've passed the end_date, stop creating schedule
        
        if len(schedules) == 0:
            print("No schedules were created. Removing request from entry")
            db.session.delete(WFHRequest.query.get(request_id))
            db.session.commit()
            raise ValueError("No schedules were created")

        
        db.session.commit()
        return schedules
    
    @staticmethod
    def update_schedule(request_id, status):
        # Fetch the existing schedules based on the request_id
        schedules = WFHSchedule.query.filter_by(request_id=request_id).all()

        if not schedules:
            raise ValueError(f"No schedules found for request_id: {request_id}")


        for schedule in schedules:
            if status == "APPROVED":
                schedule.status = "APPROVED"
            elif status == "REJECTED":
                schedule.status = "REJECTED"

        # Commit the updated schedules to the database
        db.session.commit()
        print(f"Schedules for request_id {request_id} have been updated successfully.")

        return True
    
    @staticmethod
    def get_manager_schedule_summary(manager_id, start_date, end_date):
        staff_list = Staff.query.filter_by(reporting_manager=manager_id).all()
        staff_ids = [staff.staff_id for staff in staff_list]
        total_staff = len(staff_ids)
        if total_staff == 0:
            return {'dates': []}

        date_list = []
        current_date = start_date
        while current_date <= end_date:
            date_list.append(current_date)
            current_date += timedelta(days=1)

        dates_data = []
        for d in date_list:
            date_str = d.isoformat()

            # Initialize counts
            wfh_count_am = 0
            wfh_count_pm = 0

            # Get all approved schedules for the date
            schedules = WFHSchedule.query.filter(
                WFHSchedule.staff_id.in_(staff_ids),
                WFHSchedule.date == d,
                WFHSchedule.status == 'APPROVED'
            ).all()

            for sched in schedules:
                if sched.duration == 'FULL_DAY':
                    wfh_count_am += 1
                    wfh_count_pm += 1
                elif sched.duration == 'HALF_DAY_AM':
                    wfh_count_am += 1
                elif sched.duration == 'HALF_DAY_PM':
                    wfh_count_pm += 1

            office_count_am = total_staff - wfh_count_am
            office_count_pm = total_staff - wfh_count_pm

            dates_data.append({
                'date': date_str,
                'total_staff': total_staff,
                'wfh_count_am': wfh_count_am,
                'wfh_count_pm': wfh_count_pm,
                'office_count_am': office_count_am,
                'office_count_pm': office_count_pm
            })
        return {'dates': dates_data}

    @staticmethod
    def get_manager_schedule_detail(manager_id, date):
        staff_list = Staff.query.filter_by(reporting_manager=manager_id).all()
        staff_ids = [staff.staff_id for staff in staff_list]
        if not staff_ids:
            return {'date': date.isoformat(), 'staff': []}

        staff_status = {}
        for staff in staff_list:
            staff_status[staff.staff_id] = {
                'staff_id': staff.staff_id,
                'name': f"{staff.staff_fname} {staff.staff_lname}",
                'status_am': 'OFFICE',
                'status_pm': 'OFFICE'
            }

        schedules = WFHSchedule.query.filter(
            WFHSchedule.staff_id.in_(staff_ids),
            WFHSchedule.date == date,
            WFHSchedule.status == 'APPROVED'
        ).all()

        for sched in schedules:
            if sched.duration == 'FULL_DAY':
                staff_status[sched.staff_id]['status_am'] = 'WFH'
                staff_status[sched.staff_id]['status_pm'] = 'WFH'
            elif sched.duration == 'HALF_DAY_AM':
                staff_status[sched.staff_id]['status_am'] = 'WFH'
                staff_status[sched.staff_id]['status_pm'] = 'OFFICE'
            elif sched.duration == 'HALF_DAY_PM':
                staff_status[sched.staff_id]['status_am'] = 'OFFICE'
                staff_status[sched.staff_id]['status_pm'] = 'WFH'

        staff_list_status = list(staff_status.values())

        return {
            'date': date.isoformat(),
            'staff': staff_list_status
        }

File: backend\app\__init__.py
--------------------------------------------------------------------------------
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_cors import CORS
from config import Config

# Initialize SQLAlchemy
db = SQLAlchemy()


def create_app(config_class=Config):
    # Initialize Flask app
    app = Flask(__name__)
    app.config.from_object(config_class)

    # Configure CORS
    CORS(app, resources={r"/*": {"origins": "*"}}, supports_credentials=True)

    # Initialize extensions
    db.init_app(app)

    # Import and initialize the staff controller
    from app.controllers import staff_controller, wfh_controller

    app.register_blueprint(staff_controller.staff_bp)
    app.register_blueprint(wfh_controller.wfh_bp)

    @app.route("/")
    def test():
        return "Welcome to the WFH Scheduler API."

    return app


File: backend\config.py
--------------------------------------------------------------------------------
import os
from dotenv import load_dotenv

# Attempt to load .env file, but don't raise an error if it doesn't exist
load_dotenv(override=True)


class Config:
    # Construct database URI using environment variables
    DB_USER = os.environ.get("DB_USER")
    DB_PASSWORD = os.environ.get("DB_PASSWORD")
    DB_HOST = os.environ.get("DB_HOST")
    DB_NAME = os.environ.get("DB_NAME")

    SQLALCHEMY_DATABASE_URI = (
        f"mysql+mysqlconnector://{DB_USER}:{DB_PASSWORD}@{DB_HOST}/{DB_NAME}"
    )
    SQLALCHEMY_TRACK_MODIFICATIONS = False


class TestConfig(Config):
    TESTING = True
    SQLALCHEMY_DATABASE_URI = "sqlite:///:memory:"
    SQLALCHEMY_TRACK_MODIFICATIONS = False


File: backend\requirements.txt
--------------------------------------------------------------------------------
Flask==3.0.1
Flask-Cors==4.0.0
Flask-SQLAlchemy==3.1.1
python-dotenv==1.0.1
mysql-connector-python==8.3.0
SQLAlchemy==2.0.25
gunicorn==23.0.0

File: backend\run.py
--------------------------------------------------------------------------------
from app import create_app

app = create_app()

if __name__ == '__main__':
    app.run(debug=True)


File: backend\tests\test_schedule_service.py
--------------------------------------------------------------------------------
import unittest
from datetime import datetime, timedelta
from app import create_app, db
from config import TestConfig
from app.models.staff import Staff
from app.models.wfh_request import WFHRequest
from app.models.wfh_schedule import WFHSchedule
from app.services.wfh_schedule_service import WFHScheduleService


class WFHScheduleServiceTestCase(unittest.TestCase):
    def setUp(self):
        self.app = create_app(TestConfig)
        self.app_context = self.app.app_context()
        self.app_context.push()

        db.create_all()

        staff1 = Staff(
            staff_id=1,
            staff_fname="Test",
            staff_lname="Director",
            dept="Test Department",
            position="Director",
            country="Test Country",
            email="director@test.com",
            reporting_manager=None,
            role=1,
            password="testpassword1",
        )
        staff2 = Staff(
            staff_id=2,
            staff_fname="Test",
            staff_lname="Manager",
            dept="Test Department",
            position="Manager",
            country="Test Country",
            email="manager@test.com",
            reporting_manager=1,
            role=3,
            password="testpassword3",
        )
        staff3 = Staff(
            staff_id=3,
            staff_fname="Test",
            staff_lname="Staff",
            dept="Test Department",
            position="Staff",
            country="Test Country",
            email="staff@test.com",
            reporting_manager=2,
            role=2,
            password="testpassword2",
        )
        db.session.add_all([staff1, staff2, staff3])
        db.session.commit()

        self.staff1 = staff1
        self.staff2 = staff2
        self.staff3 = staff3

    def tearDown(self):
        db.session.remove()
        db.drop_all()
        self.app_context.pop()

    def test_create_schedule_single_date(self):
        today = datetime.now().date()
        start_date = today + timedelta(days=5)
        wfh_request = WFHRequest(
            request_id=1,
            staff_id=self.staff3.staff_id,
            manager_id=self.staff2.staff_id,
            request_date=today,
            start_date=start_date,
            end_date=None,
            reason_for_applying="Test schedule",
        )
        db.session.add(wfh_request)
        db.session.commit()

        schedules = WFHScheduleService.create_schedule(
            request_id=wfh_request.request_id,
            staff_id=self.staff3.staff_id,
            manager_id=self.staff2.staff_id,
            start_date=start_date,
            end_date=None,
            duration="FULL_DAY",
            dept=self.staff3.dept,
            position=self.staff3.position,
        )
        self.assertEqual(len(schedules), 1)
        self.assertEqual(schedules[0].date, start_date)
        self.assertEqual(schedules[0].duration, "FULL_DAY")

    def test_create_schedule_recurring(self):
        today = datetime.now().date()
        start_date = today + timedelta(days=5)
        end_date = start_date + timedelta(days=21)  # 3 weeks
        wfh_request = WFHRequest(
            request_id=1,
            staff_id=self.staff3.staff_id,
            manager_id=self.staff2.staff_id,
            request_date=today,
            start_date=start_date,
            end_date=end_date,
            reason_for_applying="Test recurring schedule",
        )
        db.session.add(wfh_request)
        db.session.commit()

        schedules = WFHScheduleService.create_schedule(
            request_id=wfh_request.request_id,
            staff_id=self.staff3.staff_id,
            manager_id=self.staff2.staff_id,
            start_date=start_date,
            end_date=end_date,
            duration="FULL_DAY",
            dept=self.staff3.dept,
            position=self.staff3.position,
        )
        expected_dates = [start_date + timedelta(days=7 * i) for i in range(4)]
        self.assertEqual(len(schedules), len(expected_dates))
        schedule_dates = [schedule.date for schedule in schedules]
        self.assertEqual(schedule_dates, expected_dates)

    def test_create_schedule_existing_schedule(self):
        today = datetime.now().date()
        start_date = today + timedelta(days=5)
        wfh_request = WFHRequest(
            request_id=1,
            staff_id=self.staff3.staff_id,
            manager_id=self.staff2.staff_id,
            request_date=today,
            start_date=start_date,
            end_date=None,
            reason_for_applying="Test schedule",
        )
        db.session.add(wfh_request)
        db.session.commit()

        existing_schedule = WFHSchedule(
            request_id=wfh_request.request_id,
            staff_id=self.staff3.staff_id,
            manager_id=self.staff2.staff_id,
            date=start_date,
            duration="FULL_DAY",
            dept=self.staff3.dept,
            position=self.staff3.position,
        )
        db.session.add(existing_schedule)
        db.session.commit()

        with self.assertRaises(ValueError) as context:
            WFHScheduleService.create_schedule(
                request_id=wfh_request.request_id,
                staff_id=self.staff3.staff_id,
                manager_id=self.staff2.staff_id,
                start_date=start_date,
                end_date=None,
                duration="FULL_DAY",
                dept=self.staff3.dept,
                position=self.staff3.position,
            )
        self.assertEqual(str(context.exception), "No schedules were created")
        deleted_request = WFHRequest.query.get(wfh_request.request_id)
        self.assertIsNone(deleted_request)

    def test_create_schedule_no_schedules_created(self):
        today = datetime.now().date()
        start_date = today + timedelta(days=5)
        end_date = start_date + timedelta(days=21)  # 3 weeks
        wfh_request = WFHRequest(
            request_id=1,
            staff_id=self.staff3.staff_id,
            manager_id=self.staff2.staff_id,
            request_date=today,
            start_date=start_date,
            end_date=end_date,
            reason_for_applying="Test schedule",
        )
        db.session.add(wfh_request)
        db.session.commit()

        current_date = start_date
        while current_date <= end_date:
            existing_schedule = WFHSchedule(
                request_id=wfh_request.request_id,
                staff_id=self.staff3.staff_id,
                manager_id=self.staff2.staff_id,
                date=current_date,
                duration="FULL_DAY",
                dept=self.staff3.dept,
                position=self.staff3.position,
            )
            db.session.add(existing_schedule)
            current_date += timedelta(days=7)
        db.session.commit()

        with self.assertRaises(ValueError) as context:
            WFHScheduleService.create_schedule(
                request_id=wfh_request.request_id,
                staff_id=self.staff3.staff_id,
                manager_id=self.staff2.staff_id,
                start_date=start_date,
                end_date=end_date,
                duration="FULL_DAY",
                dept=self.staff3.dept,
                position=self.staff3.position,
            )
        self.assertEqual(str(context.exception), "No schedules were created")
        deleted_request = WFHRequest.query.get(wfh_request.request_id)
        self.assertIsNone(deleted_request)
    


    ############## LIST OF TEST FOR UPDATE_SCHEDULE ##############
    def test_update_schedule_single_date(self):
        today = datetime.now().date()
        start_date = today + timedelta(days=5)
        existing_schedule = WFHSchedule(
            request_id= 1,
            staff_id=self.staff3.staff_id,
            manager_id=self.staff2.staff_id,
            date= start_date,
            duration="FULL_DAY",
            dept=self.staff3.dept,
            position=self.staff3.position,
        )
        db.session.add(existing_schedule)
        db.session.commit()

        updated_schedule = WFHScheduleService.update_schedule(
            request_id=1, status="APPROVED"
        )

        self.assertEqual(updated_schedule, True)
        self.assertEqual(existing_schedule.status, "APPROVED")
    
    def test_update_schedule_rejected(self):
        today = datetime.now().date()
        start_date = today + timedelta(days=5)
        existing_schedule = WFHSchedule(
            request_id= 1,
            staff_id=self.staff3.staff_id,
            manager_id=self.staff2.staff_id,
            date= start_date,
            duration="FULL_DAY",
            dept=self.staff3.dept,
            position=self.staff3.position,
        )
        db.session.add(existing_schedule)
        db.session.commit()

        updated_schedule = WFHScheduleService.update_schedule(
            request_id=1, status="REJECTED"
        )

        self.assertEqual(updated_schedule, True)
        self.assertEqual(existing_schedule.status, "REJECTED")
        

    def test_update_schedule_recurring(self):
        today = datetime.now().date()

        for i in range(3):
            start_date = today + timedelta(days=5)
            existing_schedule1 = WFHSchedule(
            request_id= 1,
            staff_id=self.staff3.staff_id,
            manager_id=self.staff2.staff_id,
            date= start_date,
            duration="FULL_DAY",
            dept=self.staff3.dept,
            position=self.staff3.position,
        )
        existing_schedule2 = WFHSchedule(
            request_id= 1,
            staff_id=self.staff3.staff_id,
            manager_id=self.staff2.staff_id,
            date= start_date + timedelta(days=7),
            duration="FULL_DAY",
            dept=self.staff3.dept,
            position=self.staff3.position,
        )
        existing_schedule3 = WFHSchedule(
            request_id= 1,
            staff_id=self.staff3.staff_id,
            manager_id=self.staff2.staff_id,
            date= start_date + + timedelta(days=14),
            duration="FULL_DAY",
            dept=self.staff3.dept,
            position=self.staff3.position,
        )
        db.session.add_all([existing_schedule1, existing_schedule2, existing_schedule3])
        db.session.commit()

        updated_schedule = WFHScheduleService.update_schedule(
            request_id=1, status="APPROVED")

        self.assertEqual(updated_schedule, True)
        self.assertEqual(existing_schedule1.status, "APPROVED")
        self.assertEqual(existing_schedule2.status, "APPROVED")
        self.assertEqual(existing_schedule3.status, "APPROVED")

    def test_update_schedule_schedule_not_exist(self):
        with self.assertRaises(ValueError) as context:
            WFHScheduleService.update_schedule(request_id=1, status="APPROVED")
    
        # Check if the error message is as expected
        self.assertEqual(str(context.exception), "No schedules found for request_id: 1")



if __name__ == "__main__":
    unittest.main()


File: backend\tests\test_staff_controller.py
--------------------------------------------------------------------------------
import unittest
import json
from app import create_app, db
from config import TestConfig
from app.models.staff import Staff


class StaffControllerTestCase(unittest.TestCase):
    def setUp(self):
        self.app = create_app(TestConfig)
        self.client = self.app.test_client()
        self.ctx = self.app.app_context()
        self.ctx.push()

        db.create_all()

        # Create staff member
        self.staff = Staff(
            staff_id=1,
            staff_fname="John",
            staff_lname="Doe",
            dept="Engineering",
            position="Engineer",
            country="CountryA",
            email="john.doe@example.com",
            reporting_manager=None,
            role=2,
            password="password123",
        )
        db.session.add(self.staff)
        db.session.commit()

    def tearDown(self):
        db.session.remove()
        db.drop_all()
        self.ctx.pop()

    def test_login_success(self):
        response = self.client.post(
            "/api/login",
            data=json.dumps(
                {"email": "john.doe@example.com", "password": "password123"}
            ),
            content_type="application/json",
        )
        data = response.get_json()
        self.assertEqual(response.status_code, 200)
        self.assertEqual(data["message"], "Login successful")
        self.assertEqual(data["staff_id"], self.staff.staff_id)

    def test_login_missing_fields(self):
        response = self.client.post(
            "/api/login",
            data=json.dumps(
                {
                    "email": "john.doe@example.com"
                    # Missing password
                }
            ),
            content_type="application/json",
        )
        data = response.get_json()
        self.assertEqual(response.status_code, 400)
        self.assertEqual(data["message"], "Missing email or password")

    def test_login_invalid_credentials(self):
        response = self.client.post(
            "/api/login",
            data=json.dumps(
                {"email": "john.doe@example.com", "password": "wrongpassword"}
            ),
            content_type="application/json",
        )
        data = response.get_json()
        self.assertEqual(response.status_code, 401)
        self.assertEqual(data["message"], "Invalid email or password")

    def test_get_staff_by_id_success(self):
        response = self.client.get(f"/api/staff/{self.staff.staff_id}")
        data = response.get_json()
        self.assertEqual(response.status_code, 200)
        self.assertEqual(data["staff_id"], self.staff.staff_id)
        self.assertEqual(data["email"], self.staff.email)

    def test_get_staff_by_id_not_found(self):
        with self.assertRaises(ValueError) as context:
            response = self.client.get("/api/staff/999")
            data = response.get_json()
        self.assertEqual(str(context.exception), "No staff found with id: 999")

    def test_get_staff_by_id_invalid_id(self):
        response = self.client.get("/api/staff/abc")  # Non-integer ID
        self.assertEqual(
            response.status_code, 404
        )  # Flask treats this as a 404 because the route expects an integer


if __name__ == "__main__":
    unittest.main()


File: backend\tests\test_staff_service.py
--------------------------------------------------------------------------------
import unittest
from app import create_app, db
from config import TestConfig
from app.models.staff import Staff
from app.services.staff_service import StaffService


class StaffServiceTestCase(unittest.TestCase):
    def setUp(self):
        self.app = create_app(TestConfig)
        self.app_context = self.app.app_context()
        self.app_context.push()

        db.create_all()

        staff1 = Staff(
            staff_id=1,
            staff_fname="Test",
            staff_lname="Director",
            dept="Test Department",
            position="Director",
            country="Test Country",
            email="director@test.com",
            reporting_manager=None,
            role=1,
            password="testpassword1",
        )
        staff2 = Staff(
            staff_id=2,
            staff_fname="Test",
            staff_lname="Manager",
            dept="Test Department",
            position="Manager",
            country="Test Country",
            email="manager@test.com",
            reporting_manager=1,
            role=3,
            password="testpassword3",
        )
        staff3 = Staff(
            staff_id=3,
            staff_fname="Test",
            staff_lname="Staff",
            dept="Test Department",
            position="Staff",
            country="Test Country",
            email="staff@test.com",
            reporting_manager=2,
            role=2,
            password="testpassword2",
        )
        db.session.add_all([staff1, staff2, staff3])
        db.session.commit()

        self.staff1 = staff1
        self.staff2 = staff2
        self.staff3 = staff3

    def tearDown(self):
        db.session.remove()
        db.drop_all()
        self.app_context.pop()

    def test_authenticate_staff_success(self):
        staff = StaffService.authenticate_staff("director@test.com", "testpassword1")
        self.assertIsNotNone(staff)
        self.assertEqual(staff.staff_id, 1)

    def test_authenticate_staff_wrong_password(self):
        staff = StaffService.authenticate_staff("director@test.com", "wrongpassword")
        self.assertIsNone(staff)

    def test_authenticate_staff_nonexistent_email(self):
        staff = StaffService.authenticate_staff("nonexistent@test.com", "somepassword")
        self.assertIsNone(staff)

    def test_get_staff_by_id_existing(self):
        staff = StaffService.get_staff_by_id(1)
        self.assertIsNotNone(staff)
        self.assertEqual(staff.staff_id, 1)

    def test_get_staff_by_id_nonexistent(self):
        with self.assertRaises(ValueError) as context:
            StaffService.get_staff_by_id(99)
        self.assertEqual(str(context.exception), "No staff found with id: 99")

    def test_get_staff_by_id_invalid(self):
        with self.assertRaises(ValueError) as context:
            StaffService.get_staff_by_id("invalid_id")
        self.assertEqual(str(context.exception), "No staff found with id: invalid_id")


if __name__ == "__main__":
    unittest.main()


File: backend\tests\test_wfh_check_service.py
--------------------------------------------------------------------------------
import unittest
from datetime import datetime, timedelta
from app import create_app, db
from config import TestConfig
from app.models.staff import Staff
from app.models.wfh_schedule import WFHSchedule
from app.services.wfh_check_service import WFHCheckService


class WFHCheckServiceTestCase(unittest.TestCase):
    def setUp(self):
        self.app = create_app(TestConfig)
        self.app_context = self.app.app_context()
        self.app_context.push()

        db.create_all()

        # Create departments
        self.dept1 = "Engineering"
        self.dept2 = "Human Resources"

        # Create staff members
        self.staff1 = Staff(
            staff_id=1,
            staff_fname="Alice",
            staff_lname="Engineer1",
            dept=self.dept1,
            position="Engineer",
            country="CountryA",
            email="alice@company.com",
            reporting_manager=None,
            role=2,
            password="password1",
        )
        self.staff2 = Staff(
            staff_id=2,
            staff_fname="Bob",
            staff_lname="Engineer2",
            dept=self.dept1,
            position="Engineer",
            country="CountryA",
            email="bob@company.com",
            reporting_manager=None,
            role=2,
            password="password2",
        )
        self.staff3 = Staff(
            staff_id=3,
            staff_fname="Charlie",
            staff_lname="HR",
            dept=self.dept2,
            position="HR Manager",
            country="CountryA",
            email="charlie@company.com",
            reporting_manager=None,
            role=2,
            password="password3",
        )

        db.session.add_all([self.staff1, self.staff2, self.staff3])
        db.session.commit()

    def tearDown(self):
        db.session.remove()
        db.drop_all()
        self.app_context.pop()

    def test_department_count(self):
        count = WFHCheckService.department_count(self.dept1)
        self.assertEqual(count, 2)

        count = WFHCheckService.department_count(self.dept2)
        self.assertEqual(count, 1)

    def test_check_department_count_below_threshold(self):
        # No one is scheduled to WFH
        date = datetime.now().date()
        result = WFHCheckService.check_department_count(self.staff1.staff_id, date)
        self.assertEqual(result, 'Success')  

    def test_check_department_count_above_threshold(self):
        # Schedule more than 50% staff to WFH
        date = datetime.now().date()

        # Schedule both staff1 and staff2
        schedule1 = WFHSchedule(
            request_id=1,
            staff_id=self.staff1.staff_id,
            manager_id=0,
            date=date,
            duration="FULL_DAY",
            status="APPROVED",
            dept=self.staff1.dept,
            position=self.staff1.position,
        )
        schedule2 = WFHSchedule(
            request_id=2,
            staff_id=self.staff2.staff_id,
            manager_id=0,
            date=date,
            duration="FULL_DAY",
            status="APPROVED",
            dept=self.staff2.dept,
            position=self.staff2.position,
        )
        db.session.add_all([schedule1, schedule2])
        db.session.commit()

        # Check for another staff in the same department
        result = WFHCheckService.check_department_count(self.staff1.staff_id, date)
        self.assertEqual(result, 'Unable to apply due to max limit')

    def test_check_department_count_at_threshold(self):
        # Schedule 50% staff to WFH
        date = datetime.now().date()

        # Schedule only staff1
        schedule1 = WFHSchedule(
            request_id=1,
            staff_id=self.staff1.staff_id,
            manager_id=0,
            date=date,
            duration="FULL_DAY",
            dept=self.staff1.dept,
            position=self.staff1.position,
        )
        db.session.add(schedule1)
        db.session.commit()

        result = WFHCheckService.check_department_count(self.staff2.staff_id, date)
        self.assertEqual(result, 'Success')

    def test_check_department_count_no_staff_in_dept(self):
        # Remove staff from department
        Staff.query.filter_by(staff_id=self.staff1.staff_id).delete()
        Staff.query.filter_by(staff_id=self.staff2.staff_id).delete()
        db.session.commit()

        count = WFHCheckService.department_count(self.dept1)
        self.assertEqual(count, 0)

        with self.assertRaises(ValueError) as context:
            WFHCheckService.check_department_count(
                self.staff1.staff_id, datetime.now().date()
            )
        self.assertEqual(str(context.exception), "No staff found with id: 1")

    def test_check_department_count_2_staff_in_dept(self):
        # Remove staff from department
     
        count = WFHCheckService.department_count(self.dept1)
        self.assertEqual(count, 2)



if __name__ == "__main__":
    unittest.main()


File: backend\tests\test_wfh_controller.py
--------------------------------------------------------------------------------
import unittest
import json
from datetime import datetime, timedelta
from app import create_app, db
from config import TestConfig
from app.models.staff import Staff
from app.models.wfh_request import WFHRequest
from app.models.wfh_schedule import WFHSchedule


class WFHControllerTestCase(unittest.TestCase):
    def setUp(self):
        self.app = create_app(TestConfig)
        self.client = self.app.test_client()
        self.ctx = self.app.app_context()
        self.ctx.push()

        db.create_all()

        # Create staff members
        self.staff = Staff(
            staff_id=1,
            staff_fname="John",
            staff_lname="Doe",
            dept="Engineering",
            position="Engineer",
            country="CountryA",
            email="john.doe@example.com",
            reporting_manager=2,
            role=2,
            password="password123",
        )
        self.manager = Staff(
            staff_id=2,
            staff_fname="Jane",
            staff_lname="Smith",
            dept="Engineering",
            position="Manager",
            country="CountryA",
            email="jane.smith@example.com",
            reporting_manager=None,
            role=3,
            password="password456",
        )
        db.session.add_all([self.staff, self.manager])
        db.session.commit()

        self.today = datetime.now().date()
        self.future_date = (self.today + timedelta(days=5)).strftime("%Y-%m-%d")

    def tearDown(self):
        db.session.remove()
        db.drop_all()
        self.ctx.pop()

    def test_create_wfh_request_success(self):
        data = {
            "staff_id": self.staff.staff_id,
            "manager_id": self.manager.staff_id,
            "reason_for_applying": "Working from home for personal reasons.",
            "date": self.future_date,
            "duration": "FULL_DAY",
            "dept": self.staff.dept,
            "position": self.staff.position,
        }
        response = self.client.post(
            "/api/request", data=json.dumps(data), content_type="application/json"
        )
        resp_data = response.get_json()
        self.assertEqual(response.status_code, 201)
        self.assertEqual(
            resp_data["message"], "WFH request and schedules created successfully"
        )

    def test_create_wfh_request_missing_fields(self):
        data = {
            "staff_id": self.staff.staff_id,
            # Missing required fields
        }
        response = self.client.post(
            "/api/request", data=json.dumps(data), content_type="application/json"
        )
        resp_data = response.get_json()
        self.assertEqual(response.status_code, 400)
        self.assertIn("Missing required field", resp_data["message"])

    def test_create_wfh_request_invalid_date(self):
        invalid_date = (self.today - timedelta(days=61)).strftime("%Y-%m-%d")
        data = {
            "staff_id": self.staff.staff_id,
            "manager_id": self.manager.staff_id,
            "reason_for_applying": "Working from home for personal reasons.",
            "date": invalid_date,
            "duration": "FULL_DAY",
            "dept": self.staff.dept,
            "position": self.staff.position,
        }
        response = self.client.post(
            "/api/request", data=json.dumps(data), content_type="application/json"
        )
        resp_data = response.get_json()
        self.assertEqual(response.status_code, 400)
        self.assertEqual(
            resp_data["message"],
            "Start date must be between 2 months ago and 3 months from now.",
        )

    def test_get_pending_requests(self):
        # Create a pending request
        wfh_request = WFHRequest(
            staff_id=self.staff.staff_id,
            manager_id=self.manager.staff_id,
            request_date=self.today,
            start_date=datetime.strptime(self.future_date, "%Y-%m-%d").date(),
            reason_for_applying="Pending request",
        )
        db.session.add(wfh_request)
        db.session.commit()

        response = self.client.get(f"/api/pending-requests/{self.manager.staff_id}")
        resp_data = response.get_json()
        self.assertEqual(response.status_code, 200)
        self.assertEqual(len(resp_data), 1)
        self.assertEqual(resp_data[0]["staff_id"], self.staff.staff_id)

    def test_get_pending_requests_no_requests(self):
        response = self.client.get(f"/api/pending-requests/{self.manager.staff_id}")
        resp_data = response.get_json()
        self.assertEqual(response.status_code, 200)
        self.assertEqual(len(resp_data), 0)

    def test_update_wfh_request_approve(self):
        # Create a pending request
        wfh_request = WFHRequest(
            staff_id=self.staff.staff_id,
            manager_id=self.manager.staff_id,
            request_date=self.today,
            start_date=datetime.strptime(self.future_date, "%Y-%m-%d").date(),
            reason_for_applying="Pending request",
        )
        db.session.add(wfh_request)
        db.session.commit()

        wfh_schedule = WFHSchedule(
            request_id = wfh_request.request_id,
            staff_id=self.staff.staff_id,
            manager_id=self.manager.staff_id,
            date= wfh_request.start_date,
            duration = "FULL_DAY",
            status = "PENDING",
            dept = self.staff.dept,
            position = self.staff.position
        )
        db.session.add(wfh_schedule)
        db.session.commit()

        data = {
            "request_id": wfh_request.request_id,
            "request_status": "APPROVED",
            "reason": "",
        }
        response = self.client.patch(
            "/api/update-request",
            data=json.dumps(data),
            content_type="application/json",
        )
        self.assertEqual(response.status_code, 200)
        self.assertIn("Successfully updated request", response.get_data(as_text=True))

    def test_update_wfh_request_reject(self):
        # Create a pending request
        wfh_request = WFHRequest(
            staff_id=self.staff.staff_id,
            manager_id=self.manager.staff_id,
            request_date=self.today,
            start_date=datetime.strptime(self.future_date, "%Y-%m-%d").date(),
            reason_for_applying="Pending request",
        )
        db.session.add(wfh_request)
        db.session.commit()

        wfh_schedule = WFHSchedule(
            request_id = wfh_request.request_id,
            staff_id=self.staff.staff_id,
            manager_id=self.manager.staff_id,
            date= wfh_request.start_date,
            duration = "FULL_DAY",
            status = "PENDING",
            dept = self.staff.dept,
            position = self.staff.position
        )
        db.session.add(wfh_schedule)
        db.session.commit()

        data = {
            "request_id": wfh_request.request_id,
            "request_status": "REJECTED",
            "reason": "Insufficient staffing available.",
        }
        response = self.client.patch(
            "/api/update-request",
            data=json.dumps(data),
            content_type="application/json",
        )
        self.assertEqual(response.status_code, 200)
        self.assertIn("Successfully updated request", response.get_data(as_text=True))

    def test_reject_expired_request(self):
        # Create an expired request
        expired_date = (self.today - timedelta(days=61)).strftime("%Y-%m-%d")
        expired_request = WFHRequest(
            staff_id=self.staff.staff_id,
            manager_id=self.manager.staff_id,
            request_date=self.today,
            start_date=datetime.strptime(expired_date, "%Y-%m-%d").date(),
            reason_for_applying="Expired request",
        )
        db.session.add(expired_request)
        db.session.commit()

        response = self.client.post("/api/reject-expired-request")
        resp_data = response.get_json()
        self.assertEqual(response.status_code, 200)
        self.assertIn("Updated 1 requests to 'REJECTED'.", resp_data["message"])

    def test_check_wfh_count(self):
        data = {"staff_id": self.staff.staff_id, "date": self.future_date}
        response = self.client.post(
            "/api/check-wfh-count",
            data=json.dumps(data),
            content_type="application/json",
        )
        self.assertEqual(response.status_code, 200)
        self.assertEqual(response.get_data(as_text=True), "done")


if __name__ == "__main__":
    unittest.main()


File: backend\tests\test_wfh_request_service.py
--------------------------------------------------------------------------------
import unittest
from datetime import datetime, timedelta
from app import create_app, db
from config import TestConfig
from app.models.staff import Staff
from app.models.wfh_request import WFHRequest
from app.services.wfh_request_service import WFHRequestService


class WFHRequestServiceTestCase(unittest.TestCase):
    def setUp(self):
        self.app = create_app(TestConfig)
        self.app_context = self.app.app_context()
        self.app_context.push()

        db.create_all()

        staff1 = Staff(
            staff_id=1,
            staff_fname="Test",
            staff_lname="Director",
            dept="Test Department",
            position="Director",
            country="Test Country",
            email="director@test.com",
            reporting_manager=None,
            role=1,
            password="testpassword1",
        )
        staff2 = Staff(
            staff_id=2,
            staff_fname="Test",
            staff_lname="Manager",
            dept="Test Department",
            position="Manager",
            country="Test Country",
            email="manager@test.com",
            reporting_manager=1,
            role=3,
            password="testpassword3",
        )
        staff3 = Staff(
            staff_id=3,
            staff_fname="Test",
            staff_lname="Staff",
            dept="Test Department",
            position="Staff",
            country="Test Country",
            email="staff@test.com",
            reporting_manager=2,
            role=2,
            password="testpassword2",
        )
        db.session.add_all([staff1, staff2, staff3])
        db.session.commit()

        self.staff1 = staff1
        self.staff2 = staff2
        self.staff3 = staff3

    def tearDown(self):
        db.session.remove()
        db.drop_all()
        self.app_context.pop()

    def test_create_request_success(self):
        today = datetime.now().date()
        start_date = (today + timedelta(days=5)).strftime("%Y-%m-%d")
        end_date = None
        wfh_request = WFHRequestService.create_request(
            staff_id=self.staff3.staff_id,
            manager_id=self.staff2.staff_id,
            request_date=today,
            start_date=start_date,
            end_date=end_date,
            reason_for_applying="Need to work from home",
        )
        self.assertIsNotNone(wfh_request)
        self.assertEqual(wfh_request.staff_id, self.staff3.staff_id)

    def test_create_request_invalid_start_date(self):
        today = datetime.now().date()
        invalid_start_date = (today - timedelta(days=61)).strftime("%Y-%m-%d")
        with self.assertRaises(ValueError) as context:
            WFHRequestService.create_request(
                staff_id=self.staff3.staff_id,
                manager_id=self.staff2.staff_id,
                request_date=today,
                start_date=invalid_start_date,
                end_date=None,
                reason_for_applying="Need to work from home",
            )
        self.assertEqual(
            str(context.exception),
            "Start date must be between 2 months ago and 3 months from now.",
        )

    def test_create_request_invalid_end_date(self):
        today = datetime.now().date()
        start_date = (today + timedelta(days=5)).strftime("%Y-%m-%d")
        end_date = (today + timedelta(days=4)).strftime("%Y-%m-%d")
        with self.assertRaises(ValueError) as context:
            WFHRequestService.create_request(
                staff_id=self.staff3.staff_id,
                manager_id=self.staff2.staff_id,
                request_date=today,
                start_date=start_date,
                end_date=end_date,
                reason_for_applying="Need to work from home",
            )
        self.assertEqual(str(context.exception), "End date must be after start date.")

    def test_create_request_existing_request(self):
        today = datetime.now().date()
        start_date = (today + timedelta(days=5)).strftime("%Y-%m-%d")
        WFHRequestService.create_request(
            staff_id=self.staff3.staff_id,
            manager_id=self.staff2.staff_id,
            request_date=today,
            start_date=start_date,
            end_date=None,
            reason_for_applying="First request",
        )
        with self.assertRaises(ValueError) as context:
            WFHRequestService.create_request(
                staff_id=self.staff3.staff_id,
                manager_id=self.staff2.staff_id,
                request_date=today,
                start_date=start_date,
                end_date=None,
                reason_for_applying="Second request",
            )
        self.assertEqual(
            str(context.exception), "A request for this date already exists."
        )

    def test_get_pending_requests_for_manager(self):
        today = datetime.now().date()
        start_date = (today + timedelta(days=5)).strftime("%Y-%m-%d")
        WFHRequestService.create_request(
            staff_id=self.staff3.staff_id,
            manager_id=self.staff2.staff_id,
            request_date=today,
            start_date=start_date,
            end_date=None,
            reason_for_applying="Need to work from home",
        )
        pending_requests = WFHRequestService.get_pending_requests_for_manager(
            self.staff2.staff_id
        )
        self.assertEqual(len(pending_requests), 1)
        self.assertEqual(pending_requests[0].staff_id, self.staff3.staff_id)

    def test_update_request_success(self):
        today = datetime.now().date()
        start_date = (today + timedelta(days=5)).strftime("%Y-%m-%d")
        wfh_request = WFHRequestService.create_request(
            staff_id=self.staff3.staff_id,
            manager_id=self.staff2.staff_id,
            request_date=today,
            start_date=start_date,
            end_date=None,
            reason_for_applying="Need to work from home",
        )
        two_months_ago = today - timedelta(days=60)
        response = WFHRequestService.update_request(
            request_id=wfh_request.request_id,
            new_request_status="APPROVED",
            two_months_ago=two_months_ago,
            reason=None,
        )
        self.assertTrue(response)
        updated_request = WFHRequest.query.get(wfh_request.request_id)
        self.assertEqual(updated_request.status, "APPROVED")

    def test_update_request_invalid_date(self):
        today = datetime.now().date()
        invalid_start_date = today - timedelta(days=61)
        wfh_request = WFHRequest(
            staff_id=self.staff3.staff_id,
            manager_id=self.staff2.staff_id,
            request_date=today,
            start_date=invalid_start_date,
            end_date=None,
            reason_for_applying="Need to work from home",
        )
        db.session.add(wfh_request)
        db.session.commit()

        two_months_ago = today - timedelta(days=60)
        response = WFHRequestService.update_request(
            request_id=wfh_request.request_id,
            new_request_status="APPROVED",
            two_months_ago=two_months_ago,
            reason=None,
        )
        self.assertEqual(response, "The date is invalid to be approved")
        updated_request = WFHRequest.query.get(wfh_request.request_id)
        self.assertEqual(updated_request.status, "PENDING")

    def test_update_request_nonexistent(self):
        two_months_ago = datetime.now().date() - timedelta(days=60)
        response = WFHRequestService.update_request(
            request_id=999,
            new_request_status="APPROVED",
            two_months_ago=two_months_ago,
            reason=None,
        )
        self.assertEqual(response, "Request Does not Exist!")

    def test_reject_expired(self):
        today = datetime.now().date()
        valid_start_date = today - timedelta(days=30)
        expired_start_date = today - timedelta(days=61)
        valid_request = WFHRequest(
            staff_id=self.staff3.staff_id,
            manager_id=self.staff2.staff_id,
            request_date=today,
            start_date=valid_start_date,
            end_date=None,
            reason_for_applying="Valid request",
        )
        expired_request = WFHRequest(
            staff_id=self.staff3.staff_id,
            manager_id=self.staff2.staff_id,
            request_date=today,
            start_date=expired_start_date,
            end_date=None,
            reason_for_applying="Expired request",
        )
        db.session.add_all([valid_request, expired_request])
        db.session.commit()

        two_months_ago = today - timedelta(days=60)
        updated_count = WFHRequestService.reject_expired(two_months_ago)
        self.assertEqual(updated_count, 1)
        expired_request = WFHRequest.query.filter_by(
            reason_for_applying="Expired request"
        ).first()
        self.assertEqual(expired_request.status, "REJECTED")
        valid_request = WFHRequest.query.filter_by(
            reason_for_applying="Valid request"
        ).first()
        self.assertEqual(valid_request.status, "PENDING")

    def test_check_date(self):
        two_months_ago = datetime.now().date() - timedelta(days=60)
        date_within_range = datetime.now().date() - timedelta(days=30)
        date_out_of_range = datetime.now().date() - timedelta(days=61)
        self.assertTrue(WFHRequestService.check_date(date_within_range, two_months_ago))
        self.assertFalse(
            WFHRequestService.check_date(date_out_of_range, two_months_ago)
        )

    def test_create_request_start_date_too_far(self):
        today = datetime.now().date()
        start_date = (today + timedelta(days=91)).strftime("%Y-%m-%d")  # Beyond 90 days
        with self.assertRaises(ValueError) as context:
            WFHRequestService.create_request(
                staff_id=self.staff3.staff_id,
                manager_id=self.staff2.staff_id,
                request_date=today,
                start_date=start_date,
                end_date=None,
                reason_for_applying="Need to work from home",
            )
        self.assertEqual(
            str(context.exception),
            "Start date must be between 2 months ago and 3 months from now.",
        )

    def test_create_request_end_date_before_start_date(self):
        today = datetime.now().date()
        start_date = (today + timedelta(days=5)).strftime("%Y-%m-%d")
        end_date = (today + timedelta(days=4)).strftime("%Y-%m-%d")
        with self.assertRaises(ValueError) as context:
            WFHRequestService.create_request(
                staff_id=self.staff3.staff_id,
                manager_id=self.staff2.staff_id,
                request_date=today,
                start_date=start_date,
                end_date=end_date,
                reason_for_applying="Need to work from home",
            )
        self.assertEqual(str(context.exception), "End date must be after start date.")


if __name__ == "__main__":
    unittest.main()


File: backend\WFH-Schedule.sql
--------------------------------------------------------------------------------
CREATE DATABASE wfh_scheduler;
USE wfh_scheduler;
CREATE TABLE Staff (
    staff_id INT PRIMARY KEY,
    staff_fname VARCHAR(255),
    staff_lname VARCHAR(255),
    dept VARCHAR(255),
    position VARCHAR(255),
    country VARCHAR(255),
    email VARCHAR(255),
    reporting_manager INT,
    role INT,
    password VARCHAR(255),
    FOREIGN KEY (reporting_manager) REFERENCES Staff(staff_id)
);

CREATE TABLE WFHRequest (
    request_id INT PRIMARY KEY AUTO_INCREMENT,
    staff_id INT NOT NULL,
    manager_id INT NOT NULL,
    request_date DATE NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE DEFAULT NULL,
    status VARCHAR(20) DEFAULT 'PENDING', -- 'PENDING', 'REJECTED'
    reason_for_applying TEXT,
    reason_for_rejection TEXT DEFAULT NULL,
    FOREIGN KEY (staff_id) REFERENCES Staff(staff_id),
    FOREIGN KEY (manager_id) REFERENCES Staff(staff_id)
);

CREATE TABLE WFHSchedule (
    schedule_id INT PRIMARY KEY AUTO_INCREMENT,
    request_id INT NOT NULL,
    staff_id INT NOT NULL,
    manager_id INT NOT NULL,
    date DATE NOT NULL,
    duration VARCHAR(20) NOT NULL, -- 'FULL_DAY', 'HALF_DAY_AM', 'HALF_DAY_PM'
    status VARCHAR(20) DEFAULT 'PENDING', -- 'PENDING', 'WITHDRAWN', 'REJECTED'
    dept VARCHAR(255) NOT NULL,
    position VARCHAR(255) NOT NULL,    
    reason_for_withdrawing TEXT DEFAULT NULL,
    FOREIGN KEY (request_id) REFERENCES WFHRequest(request_id),
    FOREIGN KEY (staff_id) REFERENCES Staff(staff_id),
    FOREIGN KEY (manager_id) REFERENCES Staff(staff_id)
);


INSERT INTO Staff (staff_id, staff_fname, staff_lname, dept, position, country, email, reporting_manager, role, password)
VALUES 
(1, 'Test', 'Director', 'Test Department', 'Director', 'Test Country', 'director@test.com', NULL, 1, 'testpassword1'),
(2, 'Test', 'Manager', 'Test Department', 'Manager', 'Test Country', 'manager@test.com', 1, 3, 'testpassword3'),
(3, 'Test', 'Staff', 'Test Department', 'Staff', 'Test Country', 'staff@test.com', 2, 2, 'testpassword2');

-- DROP TABLE `wfh_scheduler`.`WFHSchedule`;
-- DROP TABLE `wfh_scheduler`.`WFHRequest`;