from datetime import datetime, timedelta from sqlalchemy import func, and_, or_, case from sqlalchemy.orm import joinedload from src.models import DatabaseContextManager from src.models.models import ( Tutor, Course, TeachingSession, TutorAvailability, TimetableBlock, Attendance, TutorTeachingLog, NotificationPreference, CourseProgress, AttendanceStatus, tutor_course_association, Supervisor, SupervisorDepartment, AssignmentSubmission, Assignment, User, Student, Enrollment, SubmissionStatus, TutorDepartment, Speciality, Timetable, enrollment_courses ) from flask import current_app, request from src.utils import ( ApiABC, custom_response, send_email ) from datetime import date import uuid from typing import Dict class TutorManager(ApiABC): def __init__(self): self.table = Tutor def _get_user_name(self, ctx, user_id): """Helper method to get user name by ID""" if not user_id: return 'Unknown' user = ctx.session.query(User).filter(User.id == user_id).first() if user: return f"{user.first_name} {user.last_name}" return 'Unknown' def create(self, payload): return super().create(payload) def get(self, tutor_id: str) -> Dict: """Get detailed information about a tutor including their courses, availability, and statistics""" with DatabaseContextManager() as ctx: tutor = ctx.session.query(Tutor).filter(Tutor.id == tutor_id).first() if not tutor: return custom_response( success=False, data="Tutor not found", status_code=404 ) # Get base tutor info tutor_data = { 'id': tutor.id, 'first_name': tutor.first_name, 'last_name': tutor.last_name, 'email': tutor.email, 'phone': tutor.phone, 'staff_id': tutor.staff_id, 'departments': [ item.to_json() for item in tutor.departments ], 'office_location': tutor.office_location, 'profile_picture': tutor.profile_picture, 'qualification': tutor.qualification, 'bio': tutor.bio, 'hourly_rate': tutor.hourly_rate, 'max_teaching_hours': tutor.max_teaching_hours, 'current_hours': self._calculate_current_hours(ctx, tutor_id), 'is_full_time': tutor.is_full_time, 'specialization': tutor.specialization, 'years_of_teaching': tutor.years_of_teaching, 'is_on_leave': tutor.is_on_leave, 'leave_details': { 'start_date': str(tutor.leave_start_date) if tutor.leave_start_date else None, 'end_date': str(tutor.leave_end_date) if tutor.leave_end_date else None, 'reason': tutor.leave_reason } if tutor.is_on_leave else None, 'supervisor': { 'id': tutor.supervisor.id if tutor.supervisor else None, 'name': f"{tutor.supervisor.first_name} {tutor.supervisor.last_name}" if tutor.supervisor else None, 'email': tutor.supervisor.email if tutor.supervisor else None } } # Get assigned courses with is_primary flag courses = ctx.session.query(Course).join( tutor_course_association, Course.id == tutor_course_association.c.course_id ).filter( tutor_course_association.c.tutor_id == tutor_id, Course.is_active == True ).all() tutor_data['courses'] = [{ 'id': course.id, 'code': course.code, 'title': course.title, 'credits': course.credits, 'department': course.department, 'semester': course.semester, 'color': f"hsl({hash(course.code) % 360}, 85%, 65%)", # Generate consistent color 'is_primary': ctx.session.query(tutor_course_association).filter( tutor_course_association.c.tutor_id == tutor_id, tutor_course_association.c.course_id == course.id ).first().is_primary } for course in courses] # Get availability - processed with day names availabilities = ctx.session.query(TutorAvailability).filter( TutorAvailability.tutor_id == tutor_id, TutorAvailability.is_approved == True ).order_by(TutorAvailability.day_of_week, TutorAvailability.start_time).all() tutor_data['availability'] = [{ 'id': avail.id, 'day_of_week': avail.day_of_week, 'day_name': ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'][avail.day_of_week], 'start_time': avail.start_time.strftime('%H:%M'), 'end_time': avail.end_time.strftime('%H:%M'), 'is_recurring': avail.is_recurring, 'valid_from': str(avail.valid_from) if avail.valid_from else None, 'valid_to': str(avail.valid_to) if avail.valid_to else None, 'availability_type': avail.availability_type, 'location': avail.location, 'notes': avail.notes } for avail in availabilities] # Get teaching statistics tutor_data['teaching_stats'] = { 'total_sessions': 0, 'verified_sessions': 0, 'verification_rate': 0, 'attendance_rate': 0, 'common_verification_methods': [] } # Only calculate stats if tutor has sessions if ctx.session.query(TeachingSession).filter(TeachingSession.tutor_id == tutor_id).count() > 0: stats = self._get_teaching_stats(ctx, tutor_id) tutor_data['teaching_stats'] = { 'total_sessions': stats['total_sessions'], 'verified_sessions': stats['verified_sessions'], 'verification_rate': stats['verification_rate'], 'attendance_rate': stats['attendance_rate'], 'common_verification_methods': stats['common_verification_methods'] } return custom_response( success=True, data=tutor_data, status_code=200 ) def get_tutor_approved_timetable(self, tutor_id): with DatabaseContextManager() as ctx: tutor = ctx.session.query(Tutor).filter( Tutor.id == tutor_id ).first() if not tutor: return custom_response( success=False, status_code=404, data={"message": "Tutor not found"} ) # Get all active approved timetables approved_timetables = ctx.session.query(Timetable).filter( Timetable.is_active == True, Timetable.approval_status == 'approved' ).all() # Get timetable IDs timetable_ids = [t.id for t in approved_timetables] if not timetable_ids: return custom_response( success=True, status_code=200, data={'timetable': []} ) # Get ONLY the blocks assigned to this specific tutor blocks = ctx.session.query(TimetableBlock).filter( TimetableBlock.timetable_id.in_(timetable_ids), or_( TimetableBlock.tutor_id == tutor_id, TimetableBlock.replacement_tutor_id == tutor_id ) ).all() # Organize by day timetable = {day: [] for day in range(7)} # 0-6 for Monday-Sunday for block in blocks: block_data = { 'id': block.id, 'course_id': block.course_id, 'course_code': block.course.code if block.course else 'Unknown', 'course_title': block.course.title if block.course else 'Unknown', 'start_time': block.start_time.strftime('%H:%M'), 'end_time': block.end_time.strftime('%H:%M'), 'room': block.room, 'block_type': block.block_type, 'is_recurring': block.recurring, 'timetable_id': block.timetable_id, 'status': 'approved', 'tutor_id': block.tutor_id, 'tutor_name': self._get_user_name(ctx, block.tutor_id), 'supervisor_tutor_id': block.supervisor_tutor_id, 'supervisor_tutor_name': self._get_user_name(ctx, block.supervisor_tutor_id) if block.supervisor_tutor_id else None, 'is_replacement': block.replacement_tutor_id == tutor_id } if not block.recurring and block.start_date: block_data['specific_date'] = str(block.start_date) block_day = block.start_date.weekday() timetable[block_day].append(block_data) else: timetable[block.day_of_week].append(block_data) # Sort each day's blocks by start time for day in timetable: timetable[day].sort(key=lambda x: x['start_time']) # Format for response days = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'] formatted_timetable = [] for day_num in range(7): formatted_timetable.append({ 'day_name': days[day_num], 'blocks': timetable[day_num] }) return custom_response( success=True, status_code=200, data={'timetable': formatted_timetable} ) def _calculate_current_hours(self, ctx, tutor_id: str) -> float: """Calculate current teaching hours for a tutor""" # Get all completed sessions in the current semester sessions = ctx.session.query(TeachingSession).filter( TeachingSession.tutor_id == tutor_id, TeachingSession.status == 'completed' ).all() if not sessions: return 0.0 # Sum up the duration of all sessions in hours total_hours = sum( self._calculate_session_duration(session.start_time, session.end_time) / 60 for session in sessions ) return round(total_hours, 2) def _get_teaching_stats(self, ctx, tutor_id: str) -> Dict: """Get teaching statistics for a tutor""" # Total sessions conducted total_sessions = ctx.session.query(TeachingSession).filter( TeachingSession.tutor_id == tutor_id, TeachingSession.end_time < datetime.utcnow(), TeachingSession.status == 'completed' ).count() # Verified sessions verified_sessions = ctx.session.query(TeachingSession).filter( TeachingSession.tutor_id == tutor_id, TeachingSession.is_verified == True ).count() # Attendance rate attendance_records = ctx.session.query(Attendance).join( TeachingSession, Attendance.session_id == TeachingSession.id ).filter( TeachingSession.tutor_id == tutor_id ).all() present_count = sum(1 for r in attendance_records if r.status == AttendanceStatus.present) attendance_rate = round((present_count / len(attendance_records) * 100, 1)) if attendance_records else 0 # Recent verification methods recent_methods = ctx.session.query(TeachingSession.verification_method).filter( TeachingSession.tutor_id == tutor_id, TeachingSession.is_verified == True, TeachingSession.verification_method.isnot(None) ).order_by(TeachingSession.verified_at.desc()).limit(5).all() return { 'total_sessions': total_sessions, 'verified_sessions': verified_sessions, 'verification_rate': round((verified_sessions / total_sessions) * 100, 1) if total_sessions else 0, 'attendance_rate': attendance_rate, 'common_verification_methods': [m[0] for m in recent_methods if m[0]] } def _tutor_to_dict(self, tutor: Tutor) -> Dict: """Convert Tutor model to dictionary with comprehensive information""" with DatabaseContextManager() as ctx: current_hours = self._calculate_current_hours(ctx, tutor.id) # Safely handle departments departments_data = [] primary_department = 'Not assigned' if hasattr(tutor, 'departments') and tutor.departments: departments_data = [ { "id": dept.id, "tutor_id": dept.tutor_id, "department_name": dept.department_name, "is_primary": dept.is_primary, "assigned_date": str(dept.assigned_date) if dept.assigned_date else None, "assigned_by": dept.assigned_by, "notes": dept.notes, "is_active": dept.is_active, "created_at": str(dept.created_at) if dept.created_at else None, } for dept in tutor.departments if dept.is_active ] # Get primary department primary_dept = next((dept for dept in tutor.departments if dept.is_primary and dept.is_active), None) if primary_dept: primary_department = primary_dept.department_name # Get assigned courses with detailed information courses_data = self._get_tutor_courses_summary(ctx, tutor.id) # Calculate workload information workload_info = self._calculate_workload_info(ctx, tutor.id, courses_data) # Get teaching statistics teaching_stats = self._get_teaching_stats_summary(ctx, tutor.id) # Get verification information verification_info = self._get_verification_summary(ctx, tutor.id) # Get timetable blocks and session information timetable_info = self._get_timetable_summary(ctx, tutor.id) return { 'id': tutor.id, 'first_name': tutor.first_name, 'last_name': tutor.last_name, 'email': tutor.email, 'phone': tutor.phone, 'profile_picture': tutor.profile_picture, 'staff_id': tutor.staff_id, 'department': primary_department, 'departments': departments_data, 'office_location': tutor.office_location, 'qualification': tutor.qualification, 'bio': tutor.bio, 'hourly_rate': tutor.hourly_rate, 'max_teaching_hours': tutor.max_teaching_hours, 'current_hours': current_hours, 'supervisor_id': tutor.supervisor_id, 'supervisor_name': f"{tutor.supervisor.first_name} {tutor.supervisor.last_name}" if tutor.supervisor else None, 'is_full_time': tutor.is_full_time, 'specialization': tutor.specialization, 'years_of_teaching': tutor.years_of_teaching, 'is_on_leave': tutor.is_on_leave, 'verification_methods': tutor.verification_methods, 'verification_success_rate': tutor.verification_success_rate, 'courses': courses_data, 'workload': workload_info, 'teaching_stats': teaching_stats, 'verification_info': verification_info, 'timetable_info': timetable_info, } def _get_tutor_courses_summary(self, ctx, tutor_id: str) -> list: """Get summary of courses assigned to a tutor""" try: courses = ctx.session.query(Course).join( tutor_course_association, Course.id == tutor_course_association.c.course_id ).filter( tutor_course_association.c.tutor_id == tutor_id, Course.is_active == True ).all() courses_data = [] for course in courses: # Get student count for this course student_count = ctx.session.query(Enrollment).join( enrollment_courses, Enrollment.id == enrollment_courses.c.enrollment_id ).filter( enrollment_courses.c.course_id == course.id, Enrollment.status == 'active' ).count() # Get is_primary flag association = ctx.session.query(tutor_course_association).filter( tutor_course_association.c.tutor_id == tutor_id, tutor_course_association.c.course_id == course.id ).first() courses_data.append({ 'id': course.id, 'title': course.title, 'code': course.code, 'credits': course.credits or 0, 'semester': course.semester, 'academic_year': course.academic_session.year if course.academic_session else '2024', 'student_count': student_count, 'status': 'active', 'description': course.description, 'is_primary': association.is_primary if association else False }) return courses_data except Exception as e: print(f"Error getting courses summary: {str(e)}") return [] def _calculate_workload_info(self, ctx, tutor_id: str, courses_data: list) -> dict: """Calculate workload information for progress bar and analytics""" try: # Calculate total credits from courses total_credits = sum(course.get('credits', 0) for course in courses_data) # Calculate workload status if total_credits < 24: workload_status = 'Under Utilized' workload_percentage = (total_credits / 28) * 100 elif total_credits > 28: workload_status = 'Over Utilized' workload_percentage = min((total_credits / 28) * 100, 100) else: workload_status = 'Optimal' workload_percentage = (total_credits / 28) * 100 # Get upcoming sessions count upcoming_sessions = ctx.session.query(TeachingSession).filter( TeachingSession.tutor_id == tutor_id, TeachingSession.status.in_(['scheduled', 'ongoing']) ).count() # Get recent attendance rate recent_attendance = self._get_recent_attendance_rate(ctx, tutor_id) return { 'total_credits': total_credits, 'max_credits': 28, 'workload_percentage': round(workload_percentage, 1), 'workload_status': workload_status, 'upcoming_sessions': upcoming_sessions, 'attendance_rate': recent_attendance, 'courses_count': len(courses_data) } except Exception as e: print(f"Error calculating workload: {str(e)}") return { 'total_credits': 0, 'max_credits': 28, 'workload_percentage': 0, 'workload_status': 'Under Utilized', 'upcoming_sessions': 0, 'attendance_rate': 0, 'courses_count': 0 } def _get_recent_attendance_rate(self, ctx, tutor_id: str) -> float: """Get recent attendance rate for tutor sessions""" try: # Get recent sessions (last 30 days) thirty_days_ago = datetime.utcnow() - timedelta(days=30) attendance_records = ctx.session.query(Attendance).join( TeachingSession, Attendance.session_id == TeachingSession.id ).filter( TeachingSession.tutor_id == tutor_id, TeachingSession.created_at >= thirty_days_ago ).all() if not attendance_records: return 0.0 present_count = sum(1 for r in attendance_records if r.status == AttendanceStatus.present) attendance_rate = (present_count / len(attendance_records)) * 100 return round(attendance_rate, 1) except Exception as e: print(f"Error getting attendance rate: {str(e)}") return 0.0 def _get_teaching_stats_summary(self, ctx, tutor_id: str) -> dict: """Get comprehensive teaching statistics for a tutor""" try: # Total sessions conducted total_sessions = ctx.session.query(TeachingSession).filter( TeachingSession.tutor_id == tutor_id ).count() # Completed sessions completed_sessions = ctx.session.query(TeachingSession).filter( TeachingSession.tutor_id == tutor_id, TeachingSession.status == 'completed' ).count() # Verified sessions verified_sessions = ctx.session.query(TeachingSession).filter( TeachingSession.tutor_id == tutor_id, TeachingSession.is_verified == True ).count() # Calculate verification rate verification_rate = round((verified_sessions / completed_sessions) * 100, 1) if completed_sessions > 0 else 0 # Get recent verification methods recent_methods = ctx.session.query(TeachingSession.verification_method).filter( TeachingSession.tutor_id == tutor_id, TeachingSession.is_verified == True, TeachingSession.verification_method.isnot(None) ).order_by(TeachingSession.verified_at.desc()).limit(5).all() return { 'total_sessions': total_sessions, 'completed_sessions': completed_sessions, 'verified_sessions': verified_sessions, 'verification_rate': verification_rate, 'common_verification_methods': [m[0] for m in recent_methods if m[0]], 'last_verification_date': self._get_last_verification_date(ctx, tutor_id) } except Exception as e: print(f"Error getting teaching stats: {str(e)}") return { 'total_sessions': 0, 'completed_sessions': 0, 'verified_sessions': 0, 'verification_rate': 0, 'common_verification_methods': [], 'last_verification_date': None } def _get_verification_summary(self, ctx, tutor_id: str) -> dict: """Get verification summary for a tutor""" try: # Get tutor record for verification data tutor = ctx.session.query(Tutor).filter(Tutor.id == tutor_id).first() if not tutor: return { 'success_rate': 0, 'last_verification_date': None, 'verification_methods': 'manual', 'is_verified': False } # Get verification success rate from tutor record verification_rate = tutor.verification_success_rate or 0 # Get last verification date last_verification = ctx.session.query(TeachingSession).filter( TeachingSession.tutor_id == tutor_id, TeachingSession.is_verified == True ).order_by(TeachingSession.verified_at.desc()).first() return { 'success_rate': verification_rate, 'last_verification_date': str(last_verification.verified_at) if last_verification and last_verification.verified_at else None, 'verification_methods': tutor.verification_methods or 'manual', 'is_verified': verification_rate > 0 } except Exception as e: print(f"Error getting verification summary: {str(e)}") return { 'success_rate': 0, 'last_verification_date': None, 'verification_methods': 'manual', 'is_verified': False } def _get_last_verification_date(self, ctx, tutor_id: str) -> str: """Get the last verification date for a tutor""" try: last_verification = ctx.session.query(TeachingSession).filter( TeachingSession.tutor_id == tutor_id, TeachingSession.is_verified == True ).order_by(TeachingSession.verified_at.desc()).first() return str(last_verification.verified_at) if last_verification and last_verification.verified_at else None except Exception as e: print(f"Error getting last verification date: {str(e)}") return None def _get_timetable_summary(self, ctx, tutor_id: str) -> dict: """Get timetable summary for a tutor""" try: # Get timetable blocks assigned to this tutor timetable_blocks = ctx.session.query(TimetableBlock).filter( TimetableBlock.tutor_id == tutor_id ).all() # Get upcoming sessions upcoming_sessions = ctx.session.query(TeachingSession).filter( TeachingSession.tutor_id == tutor_id, TeachingSession.status.in_(['scheduled', 'ongoing']) ).order_by(TeachingSession.start_time).limit(5).all() # Get recent sessions recent_sessions = ctx.session.query(TeachingSession).filter( TeachingSession.tutor_id == tutor_id, TeachingSession.status == 'completed' ).order_by(TeachingSession.created_at.desc()).limit(5).all() return { 'total_blocks': len(timetable_blocks), 'upcoming_sessions': [ { 'id': session.id, 'course_id': session.course_id, 'course_title': session.course.title if session.course else 'Unknown', 'start_time': session.start_time.strftime('%H:%M') if session.start_time else None, 'end_time': session.end_time.strftime('%H:%M') if session.end_time else None, 'room': session.room, 'status': session.status, 'session_type': session.session_type } for session in upcoming_sessions ], 'recent_sessions': [ { 'id': session.id, 'course_id': session.course_id, 'course_title': session.course.title if session.course else 'Unknown', 'start_time': session.start_time.strftime('%H:%M') if session.start_time else None, 'end_time': session.end_time.strftime('%H:%M') if session.end_time else None, 'room': session.room, 'status': session.status, 'is_verified': session.is_verified, 'verification_method': session.verification_method } for session in recent_sessions ], 'weekly_schedule': self._get_weekly_schedule_summary(ctx, tutor_id, timetable_blocks) } except Exception as e: print(f"Error getting timetable summary: {str(e)}") return { 'total_blocks': 0, 'upcoming_sessions': [], 'recent_sessions': [], 'weekly_schedule': {} } def _get_weekly_schedule_summary(self, ctx, tutor_id: str, timetable_blocks: list) -> dict: """Get weekly schedule summary for a tutor""" try: weekly_schedule = {day: [] for day in range(7)} # 0-6 for Monday-Sunday for block in timetable_blocks: if block.recurring: day_blocks = weekly_schedule.get(block.day_of_week, []) day_blocks.append({ 'id': block.id, 'course_id': block.course_id, 'course_title': block.course.title if block.course else 'Unknown', 'start_time': block.start_time.strftime('%H:%M') if block.start_time else None, 'end_time': block.end_time.strftime('%H:%M') if block.end_time else None, 'room': block.room, 'block_type': block.block_type }) weekly_schedule[block.day_of_week] = day_blocks # Convert to day names days = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'] formatted_schedule = {} for day_num, day_name in enumerate(days): formatted_schedule[day_name] = weekly_schedule.get(day_num, []) return formatted_schedule except Exception as e: print(f"Error getting weekly schedule: {str(e)}") return {} def update(self, tutor_id: str, payload: Dict) -> Dict: """Update tutor information""" with DatabaseContextManager() as ctx: tutor = ctx.session.query(Tutor).filter(Tutor.id == tutor_id).first() if not tutor: return custom_response( success=False, data="Tutor not found", status_code=404 ) # Update base fields updatable_fields = [ 'first_name', 'last_name', 'phone', 'profile_picture', 'department', 'office_location', 'qualification', 'bio', 'hourly_rate', 'max_teaching_hours', 'specialization', 'years_of_teaching', 'verification_methods' ] for field in updatable_fields: if field in payload: setattr(tutor, field, payload[field]) # Handle leave status if 'is_on_leave' in payload: tutor.is_on_leave = payload['is_on_leave'] if payload['is_on_leave']: # Handle leave dates - convert empty strings to None leave_start_date = payload.get('leave_start_date') leave_end_date = payload.get('leave_end_date') tutor.leave_start_date = leave_start_date if leave_start_date and leave_start_date != '' else None tutor.leave_end_date = leave_end_date if leave_end_date and leave_end_date != '' else None tutor.leave_reason = payload.get('leave_reason') else: tutor.leave_start_date = None tutor.leave_end_date = None tutor.leave_reason = None # Update courses if provided if 'courses' in payload: # First remove all existing course associations ctx.session.execute( tutor_course_association.delete().where( tutor_course_association.c.tutor_id == tutor_id ) ) # Add new course associations for course_info in payload['courses']: ctx.session.execute( tutor_course_association.insert().values( tutor_id=tutor_id, course_id=course_info['course_id'], is_primary=course_info.get('is_primary', False) ) ) ctx.session.commit() return custom_response( success=True, data="Tutor updated successfully", status_code=200 ) def delete(self, tutor_id: str) -> Dict: """Deactivate a tutor account (soft delete)""" with DatabaseContextManager() as ctx: tutor = ctx.session.query(Tutor).filter(Tutor.id == tutor_id).first() if not tutor: return custom_response( success=False, data="Tutor not found", status_code=404 ) tutor.is_active = False ctx.session.commit() return custom_response( success=True, data="Tutor deactivated successfully", status_code=200 ) def fetchAll(self, page: int = 1, per_page: int = 10, filters: Dict = None) -> Dict: """Fetch all tutors with pagination and optional filters""" # try: with DatabaseContextManager() as ctx: # Remove the explicit join with User since Tutor already inherits from User query = ctx.session.query(Tutor).options( joinedload(Tutor.departments) ).filter(Tutor.is_active == True) # Apply filters if filters: if 'is_full_time' in filters and filters['is_full_time'] != '': is_full_time = filters['is_full_time'].lower() == 'true' query = query.filter(Tutor.is_full_time == is_full_time) if 'supervisor_id' in filters and filters['supervisor_id']: query = query.filter(Tutor.supervisor_id == filters['supervisor_id']) if 'is_on_leave' in filters and filters['is_on_leave'] != '': is_on_leave = filters['is_on_leave'].lower() == 'true' query = query.filter(Tutor.is_on_leave == is_on_leave) if 'search' in filters and filters['search']: search = f"%{filters['search']}%" query = query.filter( or_( Tutor.first_name.ilike(search), Tutor.last_name.ilike(search), Tutor.staff_id.ilike(search), Tutor.email.ilike(search), # Note: department is now a relationship, not a direct field Tutor.qualification.ilike(search), Tutor.specialization.ilike(search), Tutor.office_location.ilike(search), # Search in concatenated full name func.concat(Tutor.first_name, ' ', Tutor.last_name).ilike(search), func.concat(Tutor.last_name, ' ', Tutor.first_name).ilike(search) ) ) # try: total = query.count() tutors = query.offset((page - 1) * per_page).limit(per_page).all() # Safely convert tutors to dictionaries with error handling tutors_data = [] for tutor in tutors: tutor_dict = self._tutor_to_dict(tutor) tutors_data.append(tutor_dict) return custom_response( success=True, data={ 'tutors': tutors_data, 'total': total, 'page': page, 'per_page': per_page } ) # except Exception as e: # print(f"Error fetching tutors: {str(e)}") # return custom_response( # success=False, # data=f"Error fetching tutors: {str(e)}", # status_code=500 # ) # except Exception as e: # print(f"Error in fetchAll: {str(e)}") # return custom_response( # success=False, # data=f"Error in fetchAll: {str(e)}", # status_code=500 # ) def fetchAllTutors(self, filters: Dict = None) -> Dict: """Fetch all tutors without pagination for dropdown/search purposes""" try: with DatabaseContextManager() as ctx: # Clear any problematic objects from session ctx.session.expunge_all() # Build query with basic filtering first query = ctx.session.query(Tutor).options( joinedload(Tutor.departments) ).filter(Tutor.is_active == True) # Apply filters if filters: # Department filtering through relationship if 'department' in filters and filters['department']: query = query.join(TutorDepartment).filter( TutorDepartment.department_name == filters['department'], TutorDepartment.is_active == True ) # Note: department is now a relationship, not a direct field # Department filtering would need to be done through the relationship if 'is_full_time' in filters and filters['is_full_time'] != '': is_full_time = filters['is_full_time'].lower() == 'true' query = query.filter(Tutor.is_full_time == is_full_time) if 'supervisor_id' in filters and filters['supervisor_id']: query = query.filter(Tutor.supervisor_id == filters['supervisor_id']) if 'is_on_leave' in filters and filters['is_on_leave'] != '': is_on_leave = filters['is_on_leave'].lower() == 'true' query = query.filter(Tutor.is_on_leave == is_on_leave) if 'qualification' in filters and filters['qualification']: query = query.filter(Tutor.qualification.ilike(f"%{filters['qualification']}%")) if 'specialization' in filters and filters['specialization']: query = query.filter(Tutor.specialization.ilike(f"%{filters['specialization']}%")) if 'min_teaching_hours' in filters and filters['min_teaching_hours']: try: min_hours = int(filters['min_teaching_hours']) query = query.filter(Tutor.max_teaching_hours >= min_hours) except ValueError: pass if 'max_teaching_hours' in filters and filters['max_teaching_hours']: try: max_hours = int(filters['max_teaching_hours']) query = query.filter(Tutor.max_teaching_hours <= max_hours) except ValueError: pass if 'search' in filters and filters['search']: search = f"%{filters['search']}%" query = query.filter( or_( Tutor.first_name.ilike(search), Tutor.last_name.ilike(search), Tutor.staff_id.ilike(search), Tutor.email.ilike(search), # Note: department is now a relationship, not a direct field Tutor.qualification.ilike(search), Tutor.specialization.ilike(search), Tutor.office_location.ilike(search), # Search in concatenated full name func.concat(Tutor.first_name, ' ', Tutor.last_name).ilike(search), func.concat(Tutor.last_name, ' ', Tutor.first_name).ilike(search) ) ) tutors = query.order_by(Tutor.first_name, Tutor.last_name).all() # Get additional data for each tutor tutors_with_details = [] for tutor in tutors: try: # Get current teaching load current_courses = ctx.session.query(Course).join( tutor_course_association, Course.id == tutor_course_association.c.course_id ).filter( tutor_course_association.c.tutor_id == tutor.id, Course.is_active == True ).count() # Get availability status - with error handling for datetime issues has_availability = False try: availability_check = ctx.session.query(TutorAvailability).filter( TutorAvailability.tutor_id == tutor.id, TutorAvailability.is_approved == True ).first() has_availability = availability_check is not None except Exception as avail_error: current_app.logger.warning(f"Error checking availability for tutor {tutor.id}: {str(avail_error)}") has_availability = False # Safely handle potentially problematic fields def safe_get(field_name, default=None): try: value = getattr(tutor, field_name, default) # Handle empty strings for numeric fields if isinstance(value, str) and value.strip() == '': return default return value except Exception: return default tutors_with_details.append({ 'id': tutor.id, 'first_name': tutor.first_name or '', 'last_name': tutor.last_name or '', 'email': tutor.email or '', 'staff_id': tutor.staff_id or '', 'departments': [item.to_json() for item in tutor.departments] if hasattr(tutor, 'departments') and tutor.departments else [], 'qualification': tutor.qualification or '', 'specialization': tutor.specialization or '', 'office_location': tutor.office_location or '', 'is_full_time': bool(tutor.is_full_time), 'is_on_leave': bool(tutor.is_on_leave), 'current_courses': current_courses, 'has_availability': has_availability, 'max_teaching_hours': safe_get('max_teaching_hours', 0), 'hourly_rate': safe_get('hourly_rate', 0.0), 'years_of_teaching': safe_get('years_of_teaching', 0), 'full_name': f"{tutor.first_name or ''} {tutor.last_name or ''}".strip(), 'display_name': f"{tutor.first_name or ''} {tutor.last_name or ''} ({tutor.staff_id or 'N/A'})".strip() }) except Exception as tutor_error: current_app.logger.error(f"Error processing tutor {getattr(tutor, 'id', 'unknown')}: {str(tutor_error)}") # Skip this tutor and continue with others continue # Get summary statistics total_tutors = len(tutors_with_details) full_time_count = sum(1 for t in tutors_with_details if t['is_full_time']) on_leave_count = sum(1 for t in tutors_with_details if t['is_on_leave']) available_count = sum(1 for t in tutors_with_details if t['has_availability']) return custom_response( success=True, data={ 'tutors': tutors_with_details, 'total_count': total_tutors, 'summary': { 'total_tutors': total_tutors, 'full_time_tutors': full_time_count, 'part_time_tutors': total_tutors - full_time_count, 'available_tutors': available_count, 'departments': list(set(dept.get('department_name', dept.get('name', 'Unknown')) for t in tutors_with_details for dept in (t.get('departments', []) or []))) } } ) except Exception as e: current_app.logger.error(f"Error fetching all tutors: {str(e)}", exc_info=True) # Try fallback method without availability check try: current_app.logger.info("Attempting fallback tutor fetch method...") return self._fetchAllTutorsFallback(filters) except Exception as fallback_error: current_app.logger.error(f"Fallback method also failed: {str(fallback_error)}") return custom_response( success=False, data="Failed to fetch tutors due to server error", status_code=500 ) def _fetchAllTutorsFallback(self, filters: Dict = None) -> Dict: """Fallback method to fetch tutors without complex queries""" try: with DatabaseContextManager() as ctx: # Use a simpler query without joins or complex filtering query = ctx.session.query(Tutor).filter(Tutor.is_active == True) # Apply basic filters only if filters: # Department filtering through relationship if 'department' in filters and filters['department']: query = query.join(TutorDepartment).filter( TutorDepartment.department_name == filters['department'], TutorDepartment.is_active == True ) # Note: department is now a relationship, not a direct field # Department filtering would need to be done through the relationship if 'is_full_time' in filters and filters['is_full_time'] != '': is_full_time = filters['is_full_time'].lower() == 'true' query = query.filter(Tutor.is_full_time == is_full_time) if 'is_on_leave' in filters and filters['is_on_leave'] != '': is_on_leave = filters['is_on_leave'].lower() == 'true' query = query.filter(Tutor.is_on_leave == is_on_leave) tutors = query.order_by(Tutor.first_name, Tutor.last_name).all() # Create simplified tutor data without availability checks tutors_data = [] for tutor in tutors: try: tutors_data.append({ 'id': tutor.id, 'first_name': tutor.first_name or '', 'last_name': tutor.last_name or '', 'email': tutor.email or '', 'staff_id': tutor.staff_id or '', 'departments': [item.to_json() for item in tutor.departments] if hasattr(tutor, 'departments') and tutor.departments else [], 'qualification': tutor.qualification or '', 'specialization': tutor.specialization or '', 'office_location': tutor.office_location or '', 'is_full_time': bool(tutor.is_full_time), 'is_on_leave': bool(tutor.is_on_leave), 'current_courses': 0, # Default value 'has_availability': False, # Default value 'max_teaching_hours': 0, 'hourly_rate': 0.0, 'years_of_teaching': 0, 'full_name': f"{tutor.first_name or ''} {tutor.last_name or ''}".strip(), 'display_name': f"{tutor.first_name or ''} {tutor.last_name or ''} ({tutor.staff_id or 'N/A'})".strip() }) except Exception as tutor_error: current_app.logger.warning(f"Error processing tutor in fallback: {str(tutor_error)}") continue return custom_response( success=True, data={ 'tutors': tutors_data, 'total_count': len(tutors_data), 'summary': { 'total_tutors': len(tutors_data), 'full_time_tutors': sum(1 for t in tutors_data if t['is_full_time']), 'part_time_tutors': sum(1 for t in tutors_data if not t['is_full_time']), 'on_leave_tutors': sum(1 for t in tutors_data if t['is_on_leave']), 'available_tutors': 0, # Default value 'departments': list(set(dept.get('department_name', dept.get('name', 'Unknown')) for t in tutors_data for dept in (t.get('departments', []) or []))) }, 'note': 'Data retrieved using fallback method - some fields may be defaulted' } ) except Exception as e: current_app.logger.error(f"Fallback method failed: {str(e)}", exc_info=True) raise e def test_basic_tutor_fetch(self) -> Dict: """Test method to fetch tutors with minimal processing""" try: with DatabaseContextManager() as ctx: # Simple query without any joins or complex operations tutors = ctx.session.query(Tutor.id, Tutor.first_name, Tutor.last_name, Tutor.email).filter( Tutor.is_active == True ).limit(5).all() tutors_data = [{ 'id': tutor.id, 'first_name': tutor.first_name or '', 'last_name': tutor.last_name or '', 'email': tutor.email or '' } for tutor in tutors] return custom_response( success=True, data={ 'tutors': tutors_data, 'total_count': len(tutors_data), 'message': 'Basic tutor fetch successful' } ) except Exception as e: current_app.logger.error(f"Basic tutor fetch failed: {str(e)}", exc_info=True) return custom_response( success=False, data=f"Basic tutor fetch failed: {str(e)}", status_code=500 ) def debug_tutor_data_issues(self) -> Dict: """Debug method to identify problematic tutor data""" try: with DatabaseContextManager() as ctx: # Check for various data issues issues = [] # Check for empty string datetime fields empty_datetime_tutors = ctx.session.query(Tutor.id, Tutor.first_name, Tutor.last_name).filter( or_( Tutor.leave_start_date == '', Tutor.leave_end_date == '', Tutor.last_verification_date == '' ) ).all() if empty_datetime_tutors: issues.append({ 'type': 'empty_datetime_strings', 'count': len(empty_datetime_tutors), 'tutors': [{'id': t.id, 'name': f"{t.first_name} {t.last_name}"} for t in empty_datetime_tutors] }) # Note: Department field no longer exists - now uses TutorDepartment relationship # Check for tutors without any department assignments tutors_without_dept = ctx.session.query(Tutor.id, Tutor.first_name, Tutor.last_name).outerjoin( TutorDepartment ).filter( TutorDepartment.id.is_(None), Tutor.is_active == True ).all() if tutors_without_dept: issues.append({ 'type': 'no_department_assigned', 'count': len(tutors_without_dept), 'tutors': [{'id': t.id, 'name': f"{t.first_name} {t.last_name}"} for t in tutors_without_dept] }) return custom_response( success=True, data={ 'issues_found': len(issues), 'issues': issues, 'message': 'Data integrity check completed' } ) except Exception as e: current_app.logger.error(f"Debug method failed: {str(e)}", exc_info=True) return custom_response( success=False, data=f"Debug method failed: {str(e)}", status_code=500 ) def cleanup_corrupted_datetime_data(self) -> Dict: """Clean up any corrupted datetime data in the database""" try: with DatabaseContextManager() as ctx: # Find tutors with empty string datetime fields problematic_tutors = ctx.session.query(Tutor).filter( or_( Tutor.leave_start_date == '', Tutor.leave_end_date == '', Tutor.last_verification_date == '' ) ).all() cleaned_count = 0 for tutor in problematic_tutors: try: if tutor.leave_start_date == '': tutor.leave_start_date = None if tutor.leave_end_date == '': tutor.leave_end_date = None if tutor.last_verification_date == '': tutor.last_verification_date = None cleaned_count += 1 except Exception as e: current_app.logger.warning(f"Could not clean tutor {tutor.id}: {str(e)}") if cleaned_count > 0: ctx.session.commit() current_app.logger.info(f"Cleaned {cleaned_count} corrupted datetime fields") return custom_response( success=True, data={ 'message': f'Cleaned {cleaned_count} corrupted datetime fields', 'cleaned_count': cleaned_count } ) except Exception as e: current_app.logger.error(f"Error cleaning corrupted datetime data: {str(e)}", exc_info=True) return custom_response( success=False, data="Failed to clean corrupted datetime data", status_code=500 ) def get_tutors_by_department_simple(self, department: str) -> Dict: """Get all tutors in a specific department with simplified response""" try: with DatabaseContextManager() as ctx: # Use the new department relationship tutors = ctx.session.query(Tutor).join(TutorDepartment).filter( TutorDepartment.department_name == department, TutorDepartment.is_active == True, Tutor.is_active == True ).order_by(Tutor.first_name, Tutor.last_name).all() tutors_data = [{ 'id': tutor.id, 'first_name': tutor.first_name, 'last_name': tutor.last_name, 'email': tutor.email, 'staff_id': tutor.staff_id, 'department': department, 'qualification': tutor.qualification, 'specialization': tutor.specialization, 'office_location': tutor.office_location, 'is_full_time': tutor.is_full_time, 'is_on_leave': tutor.is_on_leave, 'full_name': f"{tutor.first_name} {tutor.last_name}" } for tutor in tutors] return custom_response( success=True, data=tutors_data ) except Exception as e: current_app.logger.error(f"Error fetching tutors by department {department}: {str(e)}", exc_info=True) return custom_response( success=False, data=f"Failed to fetch tutors for department {department}", status_code=500 ) def get_tutors_by_department(self, department: str) -> Dict: """Get all tutors in a specific department""" try: with DatabaseContextManager() as ctx: # Use the new department relationship tutors = ctx.session.query(Tutor).join(TutorDepartment).filter( TutorDepartment.department_name == department, TutorDepartment.is_active == True, Tutor.is_active == True ).order_by(Tutor.first_name, Tutor.last_name).all() tutors_data = [{ 'id': tutor.id, 'first_name': tutor.first_name, 'last_name': tutor.last_name, 'email': tutor.email, 'staff_id': tutor.staff_id, 'departments': [item.to_json() for item in tutor.departments] if hasattr(tutor, 'departments') and tutor.departments else [], 'qualification': tutor.qualification, 'specialization': tutor.specialization, 'office_location': tutor.office_location, 'is_full_time': tutor.is_full_time, 'is_on_leave': tutor.is_on_leave, 'full_name': f"{tutor.first_name} {tutor.last_name}" } for tutor in tutors] return custom_response( success=True, data={ 'tutors': tutors_data, 'department': department, 'total_count': len(tutors_data) } ) except Exception as e: current_app.logger.error(f"Error fetching tutors by department {department}: {str(e)}", exc_info=True) return custom_response( success=False, data=f"Failed to fetch tutors for department {department}", status_code=500 ) def get_tutors_by_department_detailed(self, department: str) -> Dict: """Get all tutors in a specific department with detailed information for supervisor dashboard""" try: with DatabaseContextManager() as ctx: # Use the new department relationship tutors = ctx.session.query(Tutor).join(TutorDepartment).filter( TutorDepartment.department_name == department, TutorDepartment.is_active == True, Tutor.is_active == True ).order_by(Tutor.first_name, Tutor.last_name).all() tutors_data = [] for tutor in tutors: # Get course count for this tutor courses_count = ctx.session.query(func.count(tutor_course_association.c.course_id)).filter( tutor_course_association.c.tutor_id == tutor.id ).scalar() or 0 # Get student count through enrollments students_count = 0 tutor_courses = ctx.session.query(Course).join( tutor_course_association, Course.id == tutor_course_association.c.course_id ).filter( tutor_course_association.c.tutor_id == tutor.id, Course.is_active == True ).all() for course in tutor_courses: course_students = ctx.session.query(func.count(Enrollment.student_id)).join( enrollment_courses, Enrollment.id == enrollment_courses.c.enrollment_id ).filter( enrollment_courses.c.course_id == course.id, enrollment_courses.c.status == 'active', Enrollment.status == 'active' ).scalar() or 0 students_count += course_students # Calculate attendance rate for this tutor attendance_records = ctx.session.query(Attendance).join( TeachingSession, Attendance.session_id == TeachingSession.id ).filter( TeachingSession.tutor_id == tutor.id ).all() attendance_rate = 0 if attendance_records: present_count = sum(1 for r in attendance_records if r.status == AttendanceStatus.present) attendance_rate = round((present_count / len(attendance_records)) * 100, 1) # Calculate performance score based on various factors performance_score = 0 if courses_count > 0: # Base score from course count course_score = min(40, courses_count * 10) # Attendance bonus attendance_bonus = min(30, attendance_rate * 0.3) # Experience bonus experience_bonus = min(20, (tutor.years_of_teaching or 0) * 2) # Employment type bonus employment_bonus = 10 if tutor.is_full_time else 5 performance_score = round(course_score + attendance_bonus + experience_bonus + employment_bonus) # Get profile picture from user user = ctx.session.query(User).filter(User.id == tutor.id).first() profile_picture = user.profile_picture if user else None # Get last login last_login = user.last_login.isoformat() if user and user.last_login else None tutor_data = { 'id': tutor.id, 'first_name': tutor.first_name, 'last_name': tutor.last_name, 'email': tutor.email, 'phone': user.phone if user else None, 'profile_picture': profile_picture, 'staff_id': tutor.staff_id, 'department': department, 'qualification': tutor.qualification, 'specialization': tutor.specialization, 'years_of_teaching': tutor.years_of_teaching, 'is_full_time': tutor.is_full_time, 'is_on_leave': tutor.is_on_leave, 'max_teaching_hours': tutor.max_teaching_hours, 'hourly_rate': tutor.hourly_rate, 'office_location': tutor.office_location, 'bio': tutor.bio, 'created_at': tutor.created_at.isoformat() if tutor.created_at else None, 'last_login': last_login, 'courses_count': courses_count, 'students_count': students_count, 'attendance_rate': attendance_rate, 'performance_score': performance_score, 'full_name': f"{tutor.first_name} {tutor.last_name}" } tutors_data.append(tutor_data) return custom_response( success=True, data=tutors_data ) except Exception as e: current_app.logger.error(f"Error fetching detailed tutors by department {department}: {str(e)}", exc_info=True) return custom_response( success=False, data=f"Failed to fetch detailed tutors for department {department}", status_code=500 ) def get_available_tutors(self, department: str = None) -> Dict: """Get all available tutors (not on leave and with availability)""" try: with DatabaseContextManager() as ctx: query = ctx.session.query(Tutor).filter( Tutor.is_active == True, Tutor.is_on_leave == False ) if department: # Use the new department relationship query = query.join(TutorDepartment).filter( TutorDepartment.department_name == department, TutorDepartment.is_active == True ) # Get tutors with approved availability available_tutors = [] for tutor in query.all(): has_availability = ctx.session.query(TutorAvailability).filter( TutorAvailability.tutor_id == tutor.id, TutorAvailability.is_approved == True ).first() is not None if has_availability: available_tutors.append({ 'id': tutor.id, 'first_name': tutor.first_name, 'last_name': tutor.last_name, 'email': tutor.email, 'staff_id': tutor.staff_id, 'departments': [item.to_json() for item in tutor.departments] if hasattr(tutor, 'departments') and tutor.departments else [], 'qualification': tutor.qualification, 'specialization': tutor.specialization, 'office_location': tutor.office_location, 'is_full_time': tutor.is_full_time, 'full_name': f"{tutor.first_name} {tutor.last_name}" }) return custom_response( success=True, data={ 'tutors': available_tutors, 'total_count': len(available_tutors), 'department': department if department else 'all' } ) except Exception as e: current_app.logger.error(f"Error fetching available tutors: {str(e)}", exc_info=True) return custom_response( success=False, data="Failed to fetch available tutors", status_code=500 ) def get_tutor_statistics(self) -> Dict: """Get comprehensive statistics about all tutors""" try: with DatabaseContextManager() as ctx: total_tutors = ctx.session.query(Tutor).filter(Tutor.is_active == True).count() full_time_tutors = ctx.session.query(Tutor).filter( Tutor.is_active == True, Tutor.is_full_time == True ).count() part_time_tutors = total_tutors - full_time_tutors on_leave_tutors = ctx.session.query(Tutor).filter( Tutor.is_active == True, Tutor.is_on_leave == True ).count() # Get department breakdown department_stats = ctx.session.query( TutorDepartment.department_name, func.count(Tutor.id).label('count') ).join(Tutor).filter( Tutor.is_active == True, TutorDepartment.is_active == True ).group_by(TutorDepartment.department_name).all() # Get qualification breakdown qualification_stats = ctx.session.query( Tutor.qualification, func.count(Tutor.id).label('count') ).filter( Tutor.is_active == True, Tutor.qualification.isnot(None) ).group_by(Tutor.qualification).all() return custom_response( success=True, data={ 'overview': { 'total_tutors': total_tutors, 'full_time_tutors': full_time_tutors, 'part_time_tutors': part_time_tutors, 'on_leave_tutors': on_leave_tutors, 'active_tutors': total_tutors - on_leave_tutors }, 'by_department': [ {'department': dept, 'count': count} for dept, count in department_stats ], 'by_qualification': [ {'qualification': qual, 'count': count} for qual, count in qualification_stats ] } ) except Exception as e: current_app.logger.error(f"Error fetching tutor statistics: {str(e)}", exc_info=True) return custom_response( success=False, data="Failed to fetch tutor statistics", status_code=500 ) def search_tutors(self, search_query: str, filters: Dict = None) -> Dict: """Search tutors by name or other criteria with pagination""" try: with DatabaseContextManager() as ctx: # Clear any problematic objects from session ctx.session.expunge_all() query = ctx.session.query(Tutor).filter(Tutor.is_active == True) # Apply search query - prioritize first name search if search_query and search_query.strip(): search = f"%{search_query.strip()}%" query = query.filter( or_( Tutor.first_name.ilike(search), # Primary search by first name Tutor.last_name.ilike(search), # Secondary search by last name func.concat(Tutor.first_name, ' ', Tutor.last_name).ilike(search), # Full name func.concat(Tutor.last_name, ' ', Tutor.first_name).ilike(search), # Reversed full name Tutor.staff_id.ilike(search), # Staff ID search Tutor.email.ilike(search) # Email search ) ) # Apply additional filters if filters: if 'department' in filters and filters['department']: # Use the new department relationship query = query.join(TutorDepartment).filter( TutorDepartment.department_name == filters['department'], TutorDepartment.is_active == True ) if 'is_full_time' in filters and filters['is_full_time'] != '': is_full_time = filters['is_full_time'].lower() == 'true' query = query.filter(Tutor.is_full_time == is_full_time) if 'is_on_leave' in filters and filters['is_on_leave'] != '': is_on_leave = filters['is_on_leave'].lower() == 'true' query = query.filter(Tutor.is_on_leave == is_on_leave) # Get total count for pagination info total_count = query.count() # Apply pagination (default to 20 results, max 50) page = int(filters.get('page', 1)) if filters and filters.get('page') else 1 per_page = min(int(filters.get('per_page', 20)) if filters and filters.get('per_page') else 20, 50) offset = (page - 1) * per_page # Get paginated results tutors = query.order_by(Tutor.first_name, Tutor.last_name).offset(offset).limit(per_page).all() # Create safe tutor data without complex queries tutors_data = [] for tutor in tutors: try: tutors_data.append({ 'id': tutor.id, 'first_name': tutor.first_name or '', 'last_name': tutor.last_name or '', 'email': tutor.email or '', 'staff_id': tutor.staff_id or '', 'departments': [item.to_json() for item in tutor.departments] if hasattr(tutor, 'departments') and tutor.departments else [], 'qualification': tutor.qualification or '', 'specialization': tutor.specialization or '', 'office_location': tutor.office_location or '', 'is_full_time': bool(tutor.is_full_time), 'is_on_leave': bool(tutor.is_on_leave), 'full_name': f"{tutor.first_name or ''} {tutor.last_name or ''}".strip(), 'display_name': f"{tutor.first_name or ''} {tutor.last_name or ''} ({tutor.staff_id or 'N/A'})".strip() }) except Exception as tutor_error: current_app.logger.warning(f"Error processing tutor {getattr(tutor, 'id', 'unknown')}: {str(tutor_error)}") continue return custom_response( success=True, data={ 'tutors': tutors_data, 'total_count': total_count, 'page': page, 'per_page': per_page, 'total_pages': (total_count + per_page - 1) // per_page, 'search_query': search_query, 'filters_applied': filters } ) except Exception as e: current_app.logger.error(f"Error searching tutors: {str(e)}", exc_info=True) return custom_response( success=False, data="Failed to search tutors", status_code=500 ) def search_tutors_by_name_simple(self, name_query: str, limit: int = 20) -> Dict: """Simple search for tutors by name - optimized for course creation forms""" try: with DatabaseContextManager() as ctx: # Clear any problematic objects from session ctx.session.expunge_all() if not name_query or not name_query.strip(): return custom_response( success=True, data={ 'tutors': [], 'total_count': 0, 'message': 'Please provide a search query' } ) # Simple query with minimal fields to avoid datetime issues query = ctx.session.query( Tutor.id, Tutor.first_name, Tutor.last_name, Tutor.email, Tutor.staff_id, Tutor.departments, ).filter( Tutor.is_active == True ) # Search by first name, last name, or full name search = f"%{name_query.strip()}%" query = query.filter( or_( Tutor.first_name.ilike(search), Tutor.last_name.ilike(search), func.concat(Tutor.first_name, ' ', Tutor.last_name).ilike(search) ) ) # Get results with limit tutors = query.order_by(Tutor.first_name, Tutor.last_name).limit(limit).all() # Create simple tutor data tutors_data = [] for tutor in tutors: try: tutors_data.append({ 'id': tutor.id, 'first_name': tutor.first_name or '', 'last_name': tutor.last_name or '', 'email': tutor.email or '', 'staff_id': tutor.staff_id or '', 'full_name': f"{tutor.first_name or ''} {tutor.last_name or ''}".strip(), 'display_name': f"{tutor.first_name or ''} {tutor.last_name or ''} ({tutor.staff_id or 'N/A'})".strip() }) except Exception as tutor_error: current_app.logger.warning(f"Error processing tutor in simple search: {str(tutor_error)}") continue return custom_response( success=True, data={ 'tutors': tutors_data, 'total_count': len(tutors_data), 'search_query': name_query, 'limit': limit } ) except Exception as e: current_app.logger.error(f"Error in simple tutor search: {str(e)}", exc_info=True) return custom_response( success=False, data="Failed to search tutors", status_code=500 ) def get_tutors_by_ids(self, tutor_ids: list) -> Dict: """Get tutor details by a list of IDs""" try: with DatabaseContextManager() as ctx: if not tutor_ids: return custom_response( success=True, data={'tutors': []} ) # Simple query with minimal fields to avoid datetime issues tutors = ctx.session.query( Tutor.id, Tutor.first_name, Tutor.last_name, Tutor.email, Tutor.staff_id, ).filter( Tutor.id.in_(tutor_ids), Tutor.is_active == True ).all() tutors_data = [] for tutor in tutors: try: tutors_data.append({ 'id': tutor.id, 'first_name': tutor.first_name or '', 'last_name': tutor.last_name or '', 'email': tutor.email or '', 'staff_id': tutor.staff_id or '', 'full_name': f"{tutor.first_name or ''} {tutor.last_name or ''}".strip(), 'display_name': f"{tutor.first_name or ''} {tutor.last_name or ''} ({tutor.staff_id or 'N/A'})".strip() }) except Exception as tutor_error: current_app.logger.warning(f"Error processing tutor in ID lookup: {str(tutor_error)}") continue return custom_response( success=True, data={ 'tutors': tutors_data, 'total_count': len(tutors_data) } ) except Exception as e: current_app.logger.error(f"Error getting tutors by IDs: {str(e)}", exc_info=True) return custom_response( success=False, data="Failed to get tutors by IDs", status_code=500 ) def get_upcoming_sessions(self, tutor_id: str, days_ahead: int = 7) -> Dict: """Get upcoming teaching sessions for a tutor""" with DatabaseContextManager() as ctx: sessions = ctx.session.query(TeachingSession).filter( TeachingSession.tutor_id == tutor_id, TeachingSession.start_time >= datetime.utcnow(), TeachingSession.start_time <= datetime.utcnow() + timedelta(days=days_ahead), TeachingSession.status == 'scheduled' ).order_by(TeachingSession.start_time).all() return custom_response( success=True, data=[{ 'id': session.id, 'title': session.title, 'course_id': session.course_id, 'course_code': session.course.code, 'course_title': session.course.title, 'start_datetime': session.start_time.isoformat(), 'end_datetime': session.end_time.isoformat(), 'location': session.location, 'status': session.status, 'is_mandatory': session.is_mandatory, 'materials_prepared': session.materials_prepared, 'expected_students': len(session.attendance_records) } for session in sessions] ) def get_past_sessions(self, tutor_id: str, days_back: int = 30) -> Dict: """Get past teaching sessions for a tutor""" with DatabaseContextManager() as ctx: sessions = ctx.session.query(TeachingSession).filter( TeachingSession.tutor_id == tutor_id, TeachingSession.end_time <= datetime.utcnow(), TeachingSession.end_time >= datetime.utcnow() - timedelta(days=days_back) ).order_by(TeachingSession.end_time.desc()).all() return custom_response( success=True, data=[{ 'id': session.id, 'title': session.title, 'course_id': session.course_id, 'course_code': session.course.code, 'start_datetime': session.start_time.isoformat(), 'end_datetime': session.end_time.isoformat(), 'status': session.status, 'is_verified': session.is_verified, 'verification_method': session.verification_method.value if session.verification_method else None, 'attendance': { 'present': len([a for a in session.attendance_records if a.status == AttendanceStatus.present]), 'total': len(session.attendance_records) }, 'teaching_log': self._get_teaching_log_for_session(ctx, session.id) } for session in sessions] ) def _get_teaching_log_for_session(self, ctx, session_id: str) -> Dict: """Get teaching log for a specific session""" log = ctx.session.query(TutorTeachingLog).filter( TutorTeachingLog.session_id == session_id ).first() if not log: return None return { 'checkin_time': log.checkin_time.isoformat() if log.checkin_time else None, 'checkout_time': log.checkout_time.isoformat() if log.checkout_time else None, 'verification_method': log.verification_method.value if log.verification_method else None, 'location': f"{log.location_lat}, {log.location_long}" if log.location_lat and log.location_long else None, 'session_duration': log.session_duration, 'issues_encountered': log.issues_encountered, 'supervisor_review_status': log.supervisor_review_status, 'supervisor_review_notes': log.supervisor_review_notes } def get_courses(self, tutor_id: str) -> Dict: """Get all courses associated with a tutor""" with DatabaseContextManager() as ctx: courses = ctx.session.query(Course).join( tutor_course_association, Course.id == tutor_course_association.c.course_id ).filter( tutor_course_association.c.tutor_id == tutor_id, Course.is_active == True ).all() return custom_response( success=True, data=[{ 'id': course.id, 'code': course.code, 'title': course.title, 'credits': course.credits, 'department': course.department, 'semester': course.semester, 'total_students': ctx.session.query(Student).join( Speciality, Student.speciality_id == Speciality.id ).filter( Speciality.id == course.speciality_id, Student.is_active == True ).count(), 'is_primary': ctx.session.query(tutor_course_association).filter( tutor_course_association.c.tutor_id == tutor_id, tutor_course_association.c.course_id == course.id ).first().is_primary } for course in courses] ) def send_session_reminders(self) -> Dict: """Send reminders for upcoming teaching sessions (to be run periodically)""" with DatabaseContextManager() as ctx: # Get sessions starting in the next 30 minutes that haven't had reminders sent reminder_window_start = datetime.utcnow() reminder_window_end = datetime.utcnow() + timedelta(minutes=30) sessions = ctx.session.query(TeachingSession).filter( TeachingSession.start_time >= reminder_window_start, TeachingSession.start_time <= reminder_window_end, TeachingSession.reminder_sent == False, TeachingSession.status == 'scheduled' ).all() results = [] for session in sessions: # Get tutor notification preferences prefs = ctx.session.query(NotificationPreference).filter( NotificationPreference.user_id == session.tutor_id ).first() if not prefs or not prefs.receive_email: continue # Send email reminder subject = f"Upcoming Teaching Session: {session.title}" message = f"""

Teaching Session Reminder

Hello {session.tutor.first_name},

You have an upcoming teaching session:

Course: {session.course.code} - {session.course.title}

Session: {session.title}

Time: {session.start_time.strftime('%A, %B %d at %H:%M')}

Duration: {(session.end_time - session.start_time).seconds // 60} minutes

Location: {session.location}

Expected Students: {len(session.attendance_records)}

Please ensure you have all necessary materials prepared.

Best regards,
{current_app.config['APP_NAME']} Team

""" try: send_email( sender_email="kisiwa@mutabletech.co.ke", sender_password=current_app.config['MAIL_PASSWORD'], receiver_email=session.tutor.email, subject=subject, message=message ) # Mark reminder as sent session.reminder_sent = True results.append({ 'session_id': session.id, 'tutor_id': session.tutor_id, 'status': 'success' }) except Exception as e: current_app.logger.error(f"Failed to send reminder for session {session.id}: {str(e)}") results.append({ 'session_id': session.id, 'tutor_id': session.tutor_id, 'status': 'failed', 'error': str(e) }) ctx.session.commit() return custom_response( success=True, data={ 'reminders_sent': len([r for r in results if r['status'] == 'success']), 'reminders_failed': len([r for r in results if r['status'] == 'failed']), 'details': results } ) def send_session_completion_notifications(self) -> Dict: """Send notifications after session completion (to be run periodically)""" with DatabaseContextManager() as ctx: # Get sessions that ended in the last 15 minutes and haven't had notifications sent notification_window_start = datetime.utcnow() - timedelta(minutes=15) notification_window_end = datetime.utcnow() sessions = ctx.session.query(TeachingSession).filter( TeachingSession.end_time >= notification_window_start, TeachingSession.end_time <= notification_window_end, TeachingSession.completion_notification_sent == False, TeachingSession.status == 'completed' ).all() results = [] for session in sessions: # Send notification to tutor tutor_prefs = ctx.session.query(NotificationPreference).filter( NotificationPreference.user_id == session.tutor_id ).first() if tutor_prefs and tutor_prefs.receive_email: tutor_subject = f"Session Completed: {session.title}" tutor_message = f"""

Teaching Session Completed

Hello {session.tutor.first_name},

Your teaching session has been completed:

Course: {session.course.code} - {session.course.title}

Session: {session.title}

Time: {session.start_time.strftime('%A, %B %d at %H:%M')} to {session.end_time.strftime('%H:%M')}

Location: {session.location}

Attendance: {len([a for a in session.attendance_records if a.status == AttendanceStatus.present])} present

Please verify your teaching log and submit any additional notes if needed.

Best regards,
{current_app.config['APP_NAME']} Team

""" try: send_email( sender_email="kisiwa@mutabletech.co.ke", sender_password=current_app.config['MAIL_PASSWORD'], receiver_email=session.tutor.email, subject=tutor_subject, message=tutor_message ) except Exception as e: current_app.logger.error(f"Failed to send tutor notification for session {session.id}: {str(e)}") # Send notification to supervisor if session.course.supervisor: supervisor_prefs = ctx.session.query(NotificationPreference).filter( NotificationPreference.user_id == session.course.supervisor.id ).first() if supervisor_prefs and supervisor_prefs.receive_email: supervisor_subject = f"Session Completed - Verification Required: {session.title}" supervisor_message = f"""

Teaching Session Verification Required

Hello {session.course.supervisor.first_name},

A teaching session has been completed and requires your verification:

Tutor: {session.tutor.first_name} {session.tutor.last_name}

Course: {session.course.code} - {session.course.title}

Session: {session.title}

Time: {session.start_time.strftime('%A, %B %d at %H:%M')} to {session.end_time.strftime('%H:%M')}

Location: {session.location}

Attendance: {len([a for a in session.attendance_records if a.status == AttendanceStatus.present])} present

Please verify the session was conducted properly.

Verify Session

Best regards,
{current_app.config['APP_NAME']} Team

""" try: send_email( sender_email="kisiwa@mutabletech.co.ke", sender_password=current_app.config['MAIL_PASSWORD'], receiver_email=session.course.supervisor.email, subject=supervisor_subject, message=supervisor_message ) except Exception as e: current_app.logger.error(f"Failed to send supervisor notification for session {session.id}: {str(e)}") # Mark notification as sent session.completion_notification_sent = True results.append({ 'session_id': session.id, 'tutor_notification': 'sent' if tutor_prefs and tutor_prefs.receive_email else 'skipped', 'supervisor_notification': 'sent' if session.course.supervisor and supervisor_prefs and supervisor_prefs.receive_email else 'skipped' }) ctx.session.commit() return custom_response( success=True, data={ 'sessions_processed': len(sessions), 'details': results } ) def record_teaching_session(self, tutor_id: str, session_id: str, payload: Dict) -> Dict: """Record details of a teaching session (check-in, check-out, verification)""" with DatabaseContextManager() as ctx: session = ctx.session.query(TeachingSession).filter( TeachingSession.id == session_id, TeachingSession.tutor_id == tutor_id ).first() if not session: return custom_response( success=False, data="Session not found or not assigned to this tutor", status_code=404 ) # Check if we're doing check-in or check-out if 'checkin' in payload and payload['checkin']: # Handle check-in if session.tutor_checkin_time: return custom_response( success=False, data="Already checked in to this session", status_code=400 ) session.tutor_checkin_time = datetime.utcnow() session.tutor_checkin_method = payload.get('verification_method') session.tutor_location_lat = payload.get('latitude') session.tutor_location_long = payload.get('longitude') # Create teaching log if it doesn't exist log = ctx.session.query(TutorTeachingLog).filter( TutorTeachingLog.session_id == session_id ).first() if not log: log = TutorTeachingLog( id=str(uuid.uuid4()), tutor_id=tutor_id, session_id=session_id, checkin_time=datetime.utcnow(), verification_method=payload.get('verification_method'), location_lat=payload.get('latitude'), location_long=payload.get('longitude'), device_info=request.user_agent.string, ip_address=request.remote_addr ) ctx.session.add(log) ctx.session.commit() return custom_response( success=True, data="Check-in recorded successfully", status_code=200 ) elif 'checkout' in payload and payload['checkout']: # Handle check-out if not session.tutor_checkin_time: return custom_response( success=False, data="Must check in before checking out", status_code=400 ) if session.tutor_checkout_time: return custom_response( success=False, data="Already checked out of this session", status_code=400 ) session.tutor_checkout_time = datetime.utcnow() session.tutor_checkout_method = payload.get('verification_method') session.status = 'completed' # Update teaching log log = ctx.session.query(TutorTeachingLog).filter( TutorTeachingLog.session_id == session_id ).first() if log: log.checkout_time = datetime.utcnow() log.session_duration = (session.tutor_checkout_time - session.tutor_checkin_time).seconds // 60 log.teaching_materials_used = payload.get('materials_used') log.student_interaction_notes = payload.get('interaction_notes') log.issues_encountered = payload.get('issues_encountered') ctx.session.commit() return custom_response( success=True, data="Check-out recorded successfully", status_code=200 ) else: return custom_response( success=False, data="Must specify either checkin or checkout", status_code=400 ) def verify_teaching_session(self, tutor_id: str, session_id: str, payload: Dict) -> Dict: """Verify a teaching session was conducted (by supervisor)""" with DatabaseContextManager() as ctx: session = ctx.session.query(TeachingSession).filter( TeachingSession.id == session_id ).first() if not session: return custom_response( success=False, data="Session not found", status_code=404 ) # Check if the tutor is the one assigned to the session if session.tutor_id != tutor_id: return custom_response( success=False, data="Not authorized to verify this session", status_code=403 ) # Check if session is completed if session.status != 'completed': return custom_response( success=False, data="Session must be completed before verification", status_code=400 ) # Check if already verified if session.is_verified: return custom_response( success=False, data="Session already verified", status_code=400 ) # Update verification details session.is_verified = True session.verified_by = tutor_id session.verified_at = datetime.utcnow() session.verification_method = payload.get('verification_method') session.supervisor_notes = payload.get('notes') # Update teaching log log = ctx.session.query(TutorTeachingLog).filter( TutorTeachingLog.session_id == session_id ).first() if log: log.supervisor_review_status = 'approved' log.supervisor_review_notes = payload.get('notes') log.reviewed_by = tutor_id log.review_date = datetime.utcnow() ctx.session.commit() return custom_response( success=True, data="Session verified successfully", status_code=200 ) def get_teaching_logs(self, tutor_id: str, page: int = 1, per_page: int = 10) -> Dict: """Get teaching logs for a tutor""" with DatabaseContextManager() as ctx: logs = ctx.session.query(TutorTeachingLog).filter( TutorTeachingLog.tutor_id == tutor_id ).order_by(TutorTeachingLog.checkin_time.desc()).offset( (page - 1) * per_page ).limit(per_page).all() total = ctx.session.query(TutorTeachingLog).filter( TutorTeachingLog.tutor_id == tutor_id ).count() return custom_response( success=True, data={ 'logs': [{ 'id': log.id, 'session_id': log.session_id, 'session_title': log.session.title if log.session else None, 'course_code': log.session.course.code if log.session and log.session.course else None, 'checkin_time': log.checkin_time.isoformat() if log.checkin_time else None, 'checkout_time': log.checkout_time.isoformat() if log.checkout_time else None, 'verification_method': log.verification_method.value if log.verification_method else None, 'location': f"{log.location_lat}, {log.location_long}" if log.location_lat and log.location_long else None, 'session_duration': log.session_duration, 'supervisor_review_status': log.supervisor_review_status, 'review_date': log.review_date.isoformat() if log.review_date else None } for log in logs], 'total': total, 'page': page, 'per_page': per_page } ) def get_availability(self, tutor_id: str) -> Dict: """Get availability for a tutor""" with DatabaseContextManager() as ctx: availabilities = ctx.session.query(TutorAvailability).filter( TutorAvailability.tutor_id == tutor_id ).order_by(TutorAvailability.day_of_week, TutorAvailability.start_time).all() return custom_response( success=True, data=[{ 'id': avail.id, 'day_of_week': avail.day_of_week, 'day_name': ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'][avail.day_of_week], 'start_time': avail.start_time.strftime('%H:%M'), 'end_time': avail.end_time.strftime('%H:%M'), 'is_recurring': avail.is_recurring, 'valid_from': str(avail.valid_from) if avail.valid_from else None, 'valid_to': str(avail.valid_to) if avail.valid_to else None, 'is_approved': avail.is_approved, 'approval_date': str(avail.approval_date) if avail.approval_date else None, 'availability_type': avail.availability_type, 'location': avail.location, 'notes': avail.notes } for avail in availabilities] ) def update_availability(self, tutor_id: str, payload: Dict) -> Dict: """Update tutor availability""" with DatabaseContextManager() as ctx: # First delete all existing availabilities (simplest approach) ctx.session.query(TutorAvailability).filter( TutorAvailability.tutor_id == tutor_id ).delete() # Add new availabilities for avail_data in payload.get('availabilities', []): availability = TutorAvailability( id=str(uuid.uuid4()), tutor_id=tutor_id, day_of_week=avail_data['day_of_week'], start_time=avail_data['start_time'], end_time=avail_data['end_time'], is_recurring=avail_data.get('is_recurring', True), valid_from=avail_data.get('valid_from'), valid_to=avail_data.get('valid_to'), availability_type=avail_data.get('availability_type', 'teaching'), location=avail_data.get('location'), notes=avail_data.get('notes'), is_approved=False # Needs supervisor approval ) ctx.session.add(availability) ctx.session.commit() return custom_response( success=True, data="Availability updated successfully. Pending supervisor approval.", status_code=200 ) def get_single_availability(self, tutor_id: str, availability_id: str) -> Dict: """Get a specific availability slot""" with DatabaseContextManager() as ctx: availability = ctx.session.query(TutorAvailability).filter( TutorAvailability.id == availability_id, TutorAvailability.tutor_id == tutor_id ).first() if not availability: return custom_response( success=False, data="Availability not found", status_code=404 ) return custom_response( success=True, data={ 'id': availability.id, 'day_of_week': availability.day_of_week, 'day_name': ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'][availability.day_of_week], 'start_time': availability.start_time.strftime('%H:%M'), 'end_time': availability.end_time.strftime('%H:%M'), 'is_recurring': availability.is_recurring, 'valid_from': str(availability.valid_from) if availability.valid_from else None, 'valid_to': str(availability.valid_to) if availability.valid_to else None, 'is_approved': availability.is_approved, 'approval_date': str(availability.approval_date) if availability.approval_date else None, 'availability_type': availability.availability_type, 'location': availability.location, 'notes': availability.notes, 'tutor_id': availability.tutor_id } ) def update_single_availability(self, tutor_id: str, availability_id: str, payload: Dict) -> Dict: """Update a specific availability slot""" with DatabaseContextManager() as ctx: availability = ctx.session.query(TutorAvailability).filter( TutorAvailability.id == availability_id, TutorAvailability.tutor_id == tutor_id ).first() if not availability: return custom_response( success=False, data="Availability not found", status_code=404 ) # Update fields if 'day_of_week' in payload: availability.day_of_week = payload['day_of_week'] if 'start_time' in payload: availability.start_time = payload['start_time'] if 'end_time' in payload: availability.end_time = payload['end_time'] if 'is_recurring' in payload: availability.is_recurring = payload['is_recurring'] if 'valid_from' in payload: availability.valid_from = payload['valid_from'] if 'valid_to' in payload: availability.valid_to = payload['valid_to'] if 'availability_type' in payload: availability.availability_type = payload['availability_type'] if 'location' in payload: availability.location = payload['location'] if 'notes' in payload: availability.notes = payload['notes'] # If any changes were made, set approval to pending availability.is_approved = False availability.approval_date = None ctx.session.commit() return custom_response( success=True, data="Availability updated successfully. Requires re-approval.", status_code=200 ) def delete_availability(self, tutor_id: str, availability_id: str) -> Dict: """Delete a specific availability slot""" with DatabaseContextManager() as ctx: availability = ctx.session.query(TutorAvailability).filter( TutorAvailability.id == availability_id, TutorAvailability.tutor_id == tutor_id ).first() if not availability: return custom_response( success=False, data="Availability not found", status_code=404 ) ctx.session.delete(availability) ctx.session.commit() return custom_response( success=True, data="Availability deleted successfully", status_code=204 ) def cancel_availability(self, tutor_id: str, availability_id: str, reason: str) -> Dict: """Cancel an availability slot with reason""" with DatabaseContextManager() as ctx: availability = ctx.session.query(TutorAvailability).filter( TutorAvailability.id == availability_id, TutorAvailability.tutor_id == tutor_id ).first() if not availability: return custom_response( success=False, data="Availability not found", status_code=404 ) # Mark as cancelled and add reason availability.is_approved = False availability.is_cancelled = True availability.notes = f"CANCELLED: {reason}" if availability.notes else f"CANCELLED: {reason}" availability.cancellation_date = datetime.utcnow() availability.cancellation_reason = reason ctx.session.commit() return custom_response( success=True, data="Availability cancelled successfully", status_code=200 ) def update_availability_by_id(self, availability_id: str, payload: Dict) -> Dict: """Update a specific availability slot by ID""" with DatabaseContextManager() as ctx: availability = ctx.session.query(TutorAvailability).filter( TutorAvailability.id == availability_id ).first() if not availability: return custom_response( success=False, data="Availability not found", status_code=404 ) # Update fields if 'day_of_week' in payload: availability.day_of_week = payload['day_of_week'] if 'start_time' in payload: availability.start_time = payload['start_time'] if 'end_time' in payload: availability.end_time = payload['end_time'] if 'is_recurring' in payload: availability.is_recurring = payload['is_recurring'] if 'valid_from' in payload: availability.valid_from = payload['valid_from'] if 'valid_to' in payload: availability.valid_to = payload['valid_to'] if 'availability_type' in payload: availability.availability_type = payload['availability_type'] if 'location' in payload: availability.location = payload['location'] if 'notes' in payload: availability.notes = payload['notes'] # If any changes were made, set approval to pending availability.is_approved = False availability.approval_date = None ctx.session.commit() return custom_response( success=True, data="Availability updated successfully. Requires re-approval.", status_code=200 ) def get_teaching_statistics(self, tutor_id: str, period: str = 'month') -> Dict: """Get teaching statistics for a tutor over a specific period""" with DatabaseContextManager() as ctx: # Determine date range based on period if period == 'week': start_date = datetime.utcnow() - timedelta(days=7) elif period == 'month': start_date = datetime.utcnow() - timedelta(days=30) elif period == 'quarter': start_date = datetime.utcnow() - timedelta(days=90) elif period == 'year': start_date = datetime.utcnow() - timedelta(days=365) else: start_date = datetime.utcnow() - timedelta(days=30) # Default to month # Get completed sessions in the period sessions = ctx.session.query(TeachingSession).filter( TeachingSession.tutor_id == tutor_id, TeachingSession.end_time >= start_date, TeachingSession.status == 'completed' ).all() # Calculate statistics total_sessions = len(sessions) total_hours = sum( (session.end_time - session.start_time).total_seconds() / 3600 for session in sessions ) verified_sessions = len([s for s in sessions if s.is_verified]) verification_rate = round((verified_sessions / total_sessions) * 100, 1) if total_sessions else 0 # Get attendance statistics attendance_records = ctx.session.query(Attendance).join( TeachingSession, Attendance.session_id == TeachingSession.id ).filter( TeachingSession.end_time >= start_date ).all() present_count = sum(1 for r in attendance_records if r.status == AttendanceStatus.present) attendance_rate = round((present_count / len(attendance_records)) * 100, 1) if attendance_records else 0 # Get verification methods used verification_methods = ctx.session.query( TeachingSession.verification_method, func.count(TeachingSession.id).label('count') ).filter( TeachingSession.tutor_id == tutor_id, TeachingSession.end_time >= start_date, TeachingSession.is_verified == True ).group_by(TeachingSession.verification_method).all() # Get session types session_types = ctx.session.query( TeachingSession.session_type, func.count(TeachingSession.id).label('count') ).filter( TeachingSession.tutor_id == tutor_id, TeachingSession.end_time >= start_date ).group_by(TeachingSession.session_type).all() return custom_response( success=True, data={ 'period': period, 'start_date': start_date.isoformat(), 'total_sessions': total_sessions, 'total_hours': round(total_hours, 1), 'verified_sessions': verified_sessions, 'verification_rate': verification_rate, 'attendance_rate': attendance_rate, 'verification_methods': [{ 'method': method[0].value if method[0] else 'unknown', 'count': method[1] } for method in verification_methods], 'session_types': [{ 'type': st[0], 'count': st[1] } for st in session_types] } ) def get_supervisor(self, tutor_id: str) -> Dict: """Get supervisor information for a tutor""" with DatabaseContextManager() as ctx: tutor = ctx.session.query(Tutor).filter(Tutor.id == tutor_id).first() if not tutor: return custom_response( success=False, data="Tutor not found", status_code=404 ) if not tutor.supervisor: return custom_response( success=False, data="No supervisor assigned", status_code=404 ) supervisor = tutor.supervisor return custom_response( success=True, data={ 'id': supervisor.id, 'first_name': supervisor.first_name, 'last_name': supervisor.last_name, 'email': supervisor.email, 'phone': supervisor.phone, 'department': supervisor.get_primary_department() if hasattr(supervisor, 'get_primary_department') else 'Not assigned', 'office_location': supervisor.office_location, 'office_hours': supervisor.office_hours, 'is_head_of_department': supervisor.is_head_of_department } ) def request_leave(self, tutor_id: str, payload: Dict) -> Dict: """Request leave as a tutor""" with DatabaseContextManager() as ctx: tutor = ctx.session.query(Tutor).filter(Tutor.id == tutor_id).first() if not tutor: return custom_response( success=False, data="Tutor not found", status_code=404 ) # Validate dates start_date = datetime.strptime(payload['start_date'], '%Y-%m-%d').date() end_date = datetime.strptime(payload['end_date'], '%Y-%m-%d').date() if start_date > end_date: return custom_response( success=False, data="Start date must be before end date", status_code=400 ) # Check for overlapping sessions overlapping_sessions = ctx.session.query(TeachingSession).filter( TeachingSession.tutor_id == tutor_id, or_( and_( TeachingSession.start_time >= start_date, TeachingSession.start_time <= end_date ), and_( TeachingSession.end_time >= start_date, TeachingSession.end_time <= end_date ) ), TeachingSession.status == 'scheduled' ).all() if overlapping_sessions: return custom_response( success=False, data=f"Cannot request leave - {len(overlapping_sessions)} scheduled sessions would be affected", status_code=400 ) # Update tutor leave status tutor.is_on_leave = True tutor.leave_start_date = start_date tutor.leave_end_date = end_date tutor.leave_reason = payload.get('reason') # Notify supervisor if tutor.supervisor: supervisor_prefs = ctx.session.query(NotificationPreference).filter( NotificationPreference.user_id == tutor.supervisor.id ).first() if supervisor_prefs and supervisor_prefs.receive_email: subject = f"Leave Request: {tutor.first_name} {tutor.last_name}" message = f"""

Leave Request Notification

Hello {tutor.supervisor.first_name},

Your tutor {tutor.first_name} {tutor.last_name} has requested leave:

Start Date: {start_date.strftime('%A, %B %d, %Y')}

End Date: {end_date.strftime('%A, %B %d, %Y')}

Duration: {(end_date - start_date).days + 1} days

Reason: {payload.get('reason', 'Not specified')}

Review Request

Best regards,
{current_app.config['APP_NAME']} Team

""" try: send_email( sender_email="kisiwa@mutabletech.co.ke", sender_password=current_app.config['MAIL_PASSWORD'], receiver_email=tutor.supervisor.email, subject=subject, message=message ) except Exception as e: current_app.logger.error(f"Failed to send leave request notification: {str(e)}") ctx.session.commit() return custom_response( success=True, data="Leave request submitted successfully", status_code=200 ) def cancel_leave(self, tutor_id: str) -> Dict: """Cancel active leave for a tutor""" with DatabaseContextManager() as ctx: tutor = ctx.session.query(Tutor).filter(Tutor.id == tutor_id).first() if not tutor: return custom_response( success=False, data="Tutor not found", status_code=404 ) if not tutor.is_on_leave: return custom_response( success=False, data="No active leave to cancel", status_code=400 ) tutor.is_on_leave = False tutor.leave_start_date = None tutor.leave_end_date = None tutor.leave_reason = None ctx.session.commit() return custom_response( success=True, data="Leave cancelled successfully", status_code=200 ) def get_leave_status(self, tutor_id: str) -> Dict: """Get current leave status for a tutor""" with DatabaseContextManager() as ctx: tutor = ctx.session.query(Tutor).filter(Tutor.id == tutor_id).first() if not tutor: return custom_response( success=False, data="Tutor not found", status_code=404 ) return custom_response( success=True, data={ 'is_on_leave': tutor.is_on_leave, 'leave_start_date': str(tutor.leave_start_date) if tutor.leave_start_date else None, 'leave_end_date': str(tutor.leave_end_date) if tutor.leave_end_date else None, 'leave_reason': tutor.leave_reason, 'days_remaining': (tutor.leave_end_date - date.today()).days if tutor.is_on_leave and tutor.leave_end_date else 0 } ) def get_timetable(self, tutor_id: str, week_start: str = None) -> Dict: """Get weekly timetable for a tutor - ALL approved timetables from their department (department view)""" with DatabaseContextManager() as ctx: # First, try to get the tutor and their departments tutor = ctx.session.query(Tutor).filter(Tutor.id == tutor_id).first() tutor_departments = [] if tutor: # Get tutor's active departments tutor_departments = [dept.department_name for dept in tutor.departments if dept.is_active] current_app.logger.info(f"Tutor {tutor_id} departments: {tutor_departments}") else: # If tutor not found, check if it's a supervisor who can act as a tutor current_app.logger.info(f"Tutor {tutor_id} not found, checking supervisor table") supervisor = ctx.session.query(Supervisor).filter(Supervisor.id == tutor_id).first() if supervisor: # Get supervisor's active departments tutor_departments = [dept.department_name for dept in supervisor.departments if dept.is_active] current_app.logger.info(f"Supervisor {tutor_id} departments: {tutor_departments}") else: return custom_response( success=False, data="User not found (neither tutor nor supervisor)", status_code=404 ) if not tutor_departments: user_type = "Tutor" if tutor else "Supervisor" current_app.logger.warning(f"{user_type} {tutor_id} is not assigned to any active department") return custom_response( success=False, data=f"{user_type} is not assigned to any department", status_code=404 ) # Determine the week start date if week_start: try: week_start_date = datetime.strptime(week_start, '%Y-%m-%d').date() except ValueError: return custom_response( success=False, data="Invalid date format. Use YYYY-MM-DD", status_code=400 ) else: # Default to current week (Monday) today = date.today() week_start_date = today - timedelta(days=today.weekday()) week_end_date = week_start_date + timedelta(days=6) # Get ALL approved timetables that have courses from the tutor's departments # This shows the complete department timetable, not just tutor's assigned blocks approved_timetables = ctx.session.query(Timetable).join( TimetableBlock, Timetable.id == TimetableBlock.timetable_id ).join( Course, TimetableBlock.course_id == Course.id ).filter( Timetable.approval_status == 'approved', Timetable.is_active == True, Course.department.in_(tutor_departments) ).distinct().all() if not approved_timetables: return custom_response( success=True, data={ 'week_start': str(week_start_date), 'week_end': str(week_end_date), 'timetable': [], 'tutor_departments': tutor_departments, 'approved_timetables_count': 0, 'message': f"No approved timetables found for departments: {', '.join(tutor_departments)}" } ) # Get timetable IDs timetable_ids = [t.id for t in approved_timetables] # Get ALL timetable blocks from approved timetables where the course belongs to tutor's departments # This shows the complete department timetable, not just tutor's assigned blocks blocks = ctx.session.query(TimetableBlock).join( Course, TimetableBlock.course_id == Course.id ).filter( TimetableBlock.timetable_id.in_(timetable_ids), Course.department.in_(tutor_departments), # Show all courses in tutor's departments or_( and_( TimetableBlock.recurring == True, # For recurring blocks, we just need to match the day of week ), and_( TimetableBlock.recurring == False, TimetableBlock.start_date <= week_end_date, TimetableBlock.end_date >= week_start_date ) ) ).all() # Organize by day timetable = {day: [] for day in range(7)} # 0-6 for Monday-Sunday for block in blocks: if block.recurring: # Add to the corresponding day of week timetable[block.day_of_week].append({ 'id': block.id, 'course_id': block.course_id, 'course_code': block.course.code if block.course else 'Unknown', 'course_title': block.course.title if block.course else 'Unknown', 'start_time': block.start_time.strftime('%H:%M'), 'end_time': block.end_time.strftime('%H:%M'), 'room': block.room, 'block_type': block.block_type, 'is_recurring': True, 'timetable_id': block.timetable_id, 'status': 'approved', 'tutor_id': block.tutor_id, 'tutor_name': self._get_user_name(ctx, block.tutor_id), 'supervisor_tutor_id': block.supervisor_tutor_id, 'supervisor_tutor_name': self._get_user_name(ctx, block.supervisor_tutor_id) if block.supervisor_tutor_id else None }) else: # Check if this block falls within our week if block.start_date <= week_end_date and block.end_date >= week_start_date: # Calculate which day of the week this block's start date is block_day = block.start_date.weekday() timetable[block_day].append({ 'id': block.id, 'course_id': block.course_id, 'course_code': block.course.code if block.course else 'Unknown', 'course_title': block.course.title if block.course else 'Unknown', 'start_time': block.start_time.strftime('%H:%M'), 'end_time': block.end_time.strftime('%H:%M'), 'room': block.room, 'block_type': block.block_type, 'is_recurring': False, 'specific_date': str(block.start_date), 'timetable_id': block.timetable_id, 'status': 'approved', 'tutor_id': block.tutor_id, 'tutor_name': self._get_user_name(ctx, block.tutor_id), 'supervisor_tutor_id': block.supervisor_tutor_id, 'supervisor_tutor_name': self._get_user_name(ctx, block.supervisor_tutor_id) if block.supervisor_tutor_id else None }) # Sort each day's blocks by start time for day in timetable: timetable[day].sort(key=lambda x: x['start_time']) # Format for response days = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'] formatted_timetable = [] for day_num in range(7): current_date = week_start_date + timedelta(days=day_num) formatted_timetable.append({ 'day_name': days[day_num], 'date': str(current_date), 'blocks': timetable[day_num] }) return custom_response( success=True, data={ 'week_start': str(week_start_date), 'week_end': str(week_end_date), 'timetable': formatted_timetable, 'tutor_departments': tutor_departments, 'approved_timetables_count': len(approved_timetables) } ) def get_tutor_availability(self, tutor_id: str, week_start: str = None) -> Dict: """Get tutor's personal availability - only blocks where they are teaching, with preprocessing""" with DatabaseContextManager() as ctx: # First, get the tutor and their departments tutor = ctx.session.query(Tutor).filter(Tutor.id == tutor_id).first() if not tutor: return custom_response( success=False, data="Tutor not found", status_code=404 ) # Get tutor's active departments tutor_departments = [dept.department_name for dept in tutor.departments if dept.is_active] current_app.logger.info(f"Tutor {tutor_id} departments: {tutor_departments}") if not tutor_departments: current_app.logger.warning(f"Tutor {tutor_id} is not assigned to any active department") return custom_response( success=False, data="Tutor is not assigned to any department", status_code=404 ) # Determine the week start date if week_start: try: week_start_date = datetime.strptime(week_start, '%Y-%m-%d').date() except ValueError: return custom_response( success=False, data="Invalid date format. Use YYYY-MM-DD", status_code=400 ) else: # Default to current week (Monday) today = date.today() week_start_date = today - timedelta(days=today.weekday()) week_end_date = week_start_date + timedelta(days=6) # PREPROCESSING: Check for blocks with no tutors and assign course tutors self._preprocess_missing_tutors(ctx, tutor_departments) # Get ALL approved timetables (not just from tutor's supervisor) approved_timetables = ctx.session.query(Timetable).filter( Timetable.approval_status == 'approved', Timetable.is_active == True ).all() current_app.logger.info(f"Found {len(approved_timetables)} approved timetables") if not approved_timetables: return custom_response( success=True, data={ 'week_start': str(week_start_date), 'week_end': str(week_end_date), 'timetable': [], 'message': "No approved timetables found" } ) # Get timetable IDs timetable_ids = [t.id for t in approved_timetables] # Get timetable blocks from ALL approved timetables where: # 1. The tutor is assigned to teach this block (tutor_id matches) blocks = ctx.session.query(TimetableBlock).join( Course, TimetableBlock.course_id == Course.id ).filter( TimetableBlock.timetable_id.in_(timetable_ids), TimetableBlock.tutor_id == tutor_id, # Only blocks where this tutor is teaching or_( and_( TimetableBlock.recurring == True, # For recurring blocks, we just need to match the day of week ), and_( TimetableBlock.recurring == False, TimetableBlock.start_date <= week_end_date, TimetableBlock.end_date >= week_start_date ) ) ).all() current_app.logger.info(f"Found {len(blocks)} blocks where tutor {tutor_id} is teaching") # Organize by day timetable = {day: [] for day in range(7)} # 0-6 for Monday-Sunday for block in blocks: if block.recurring: # Add to the corresponding day of week timetable[block.day_of_week].append({ 'id': block.id, 'course_id': block.course_id, 'course_code': block.course.code if block.course else 'Unknown', 'course_title': block.course.title if block.course else 'Unknown', 'start_time': block.start_time.strftime('%H:%M'), 'end_time': block.end_time.strftime('%H:%M'), 'room': block.room, 'block_type': block.block_type, 'is_recurring': True, 'timetable_id': block.timetable_id, 'status': 'approved', 'tutor_id': block.tutor_id, 'tutor_name': self._get_user_name(ctx, block.tutor_id), 'supervisor_tutor_id': block.supervisor_tutor_id, 'supervisor_tutor_name': self._get_user_name(ctx, block.supervisor_tutor_id) if block.supervisor_tutor_id else None }) else: # Check if this block falls within our week if block.start_date <= week_end_date and block.end_date >= week_start_date: # Calculate which day of the week this block's start date is block_day = block.start_date.weekday() timetable[block_day].append({ 'id': block.id, 'course_id': block.course_id, 'course_code': block.course.code if block.course else 'Unknown', 'course_title': block.course.title if block.course else 'Unknown', 'start_time': block.start_time.strftime('%H:%M'), 'end_time': block.end_time.strftime('%H:%M'), 'room': block.room, 'block_type': block.block_type, 'is_recurring': False, 'specific_date': str(block.start_date), 'timetable_id': block.timetable_id, 'status': 'approved', 'tutor_id': block.tutor_id, 'tutor_name': self._get_user_name(ctx, block.tutor_id), 'supervisor_tutor_id': block.supervisor_tutor_id, 'supervisor_tutor_name': self._get_user_name(ctx, block.supervisor_tutor_id) if block.supervisor_tutor_id else None }) # Sort each day's blocks by start time for day in timetable: timetable[day].sort(key=lambda x: x['start_time']) # Format for response days = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'] formatted_timetable = [] for day_num in range(7): current_date = week_start_date + timedelta(days=day_num) formatted_timetable.append({ 'day_name': days[day_num], 'date': str(current_date), 'blocks': timetable[day_num] }) return custom_response( success=True, data={ 'week_start': str(week_start_date), 'week_end': str(week_end_date), 'timetable': formatted_timetable, 'tutor_departments': tutor_departments, 'approved_timetables_count': len(approved_timetables), 'teaching_blocks_count': len(blocks), 'message': f"Found {len(blocks)} teaching sessions from {len(approved_timetables)} approved timetables" } ) def _preprocess_missing_tutors(self, ctx, tutor_departments): """Preprocessing: Check for blocks with no tutors and assign course tutors""" try: # Get all approved timetables first approved_timetables = ctx.session.query(Timetable).filter( Timetable.approval_status == 'approved', Timetable.is_active == True ).all() if not approved_timetables: current_app.logger.info("No approved timetables found for preprocessing") return timetable_ids = [t.id for t in approved_timetables] # Find blocks with no assigned tutor from ALL approved timetables blocks_without_tutors = ctx.session.query(TimetableBlock).join( Course, TimetableBlock.course_id == Course.id ).filter( TimetableBlock.timetable_id.in_(timetable_ids), TimetableBlock.tutor_id.is_(None) ).all() current_app.logger.info(f"Found {len(blocks_without_tutors)} blocks without assigned tutors") updated_count = 0 for block in blocks_without_tutors: # Get the course's assigned tutor from the many-to-many relationship course = block.course if course and course.tutors: # Assign the first tutor from the course's tutors to the block first_tutor = course.tutors[0] block.tutor_id = first_tutor.id updated_count += 1 current_app.logger.info(f"Assigned tutor {first_tutor.id} to block {block.id} for course {course.code}") if updated_count > 0: ctx.session.commit() current_app.logger.info(f"Updated {updated_count} blocks with missing tutors") except Exception as e: current_app.logger.error(f"Error in preprocessing missing tutors: {str(e)}", exc_info=True) ctx.session.rollback() def update_timetable_block_room(self, block_id: str, room: str) -> Dict: """Update the room for a timetable block""" current_app.logger.info(f"Received room update request for block {block_id} with room '{room}'") with DatabaseContextManager() as ctx: try: # Find the timetable block block = ctx.session.query(TimetableBlock).filter(TimetableBlock.id == block_id).first() if not block: current_app.logger.warning(f"Timetable block {block_id} not found") return custom_response( success=False, data="Timetable block not found", status_code=404 ) # Update the room old_room = block.room block.room = room if room else None current_app.logger.info(f"Updating block {block_id} room from '{old_room}' to '{room}'") # Commit the changes ctx.session.commit() current_app.logger.info(f"Successfully updated timetable block {block_id} room from '{old_room}' to '{room}'") return custom_response( success=True, data={ 'block_id': block_id, 'old_room': old_room, 'new_room': room, 'message': f"Room updated successfully from '{old_room}' to '{room}'" } ) except Exception as e: current_app.logger.error(f"Error updating timetable block room: {str(e)}", exc_info=True) ctx.session.rollback() return custom_response( success=False, data=f"Failed to update room: {str(e)}", status_code=500 ) def get_attendance_records(self, tutor_id: str, session_id: str = None, page: int = 1, per_page: int = 20) -> Dict: """Get attendance records for a tutor's sessions, with fallback to supervisor sessions""" with DatabaseContextManager() as ctx: # First, check if the user is a tutor tutor = ctx.session.query(Tutor).filter(Tutor.id == tutor_id).first() if not tutor: # If not a tutor, check if they're a supervisor supervisor = ctx.session.query(Supervisor).filter(Supervisor.id == tutor_id).first() if not supervisor: return custom_response( success=False, message="User not found as tutor or supervisor", data={'records': [], 'total': 0, 'page': page, 'per_page': per_page} ) # If supervisor, get attendance records for sessions they supervise query = ctx.session.query(Attendance).join( TeachingSession, Attendance.session_id == TeachingSession.id ).join( Course, TeachingSession.course_id == Course.id ).join( SupervisorDepartment, Course.department == SupervisorDepartment.department_name ).filter( SupervisorDepartment.supervisor_id == tutor_id ) else: # Regular tutor query query = ctx.session.query(Attendance).join( TeachingSession, Attendance.session_id == TeachingSession.id ).filter( TeachingSession.tutor_id == tutor_id ) if session_id: query = query.filter(Attendance.session_id == session_id) total = query.count() records = query.order_by( Attendance.timestamp.desc() ).offset( (page - 1) * per_page ).limit(per_page).all() return custom_response( success=True, data={ 'records': [{ 'id': record.id, 'session_id': record.session_id, 'session_title': record.session.title, 'course_code': record.session.course.code, 'student_id': record.student_id, 'student_name': f"{record.student.first_name} {record.student.last_name}", 'status': record.status.value, 'timestamp': record.timestamp.isoformat(), 'late_minutes': record.late_minutes, 'approved_by': record.approved_by, 'approval_date': record.approval_date.isoformat() if record.approval_date else None, 'is_disputed': record.is_disputed, 'verification_method': record.verification_method.value if record.verification_method else None } for record in records], 'total': total, 'page': page, 'per_page': per_page, 'user_type': 'supervisor' if not tutor else 'tutor' } ) def update_attendance_record(self, tutor_id: str, attendance_id: str, payload: Dict) -> Dict: """Update an attendance record (for disputes or corrections)""" with DatabaseContextManager() as ctx: attendance = ctx.session.query(Attendance).join( TeachingSession, Attendance.session_id == TeachingSession.id ).filter( Attendance.id == attendance_id, TeachingSession.tutor_id == tutor_id ).first() if not attendance: return custom_response( success=False, data="Attendance record not found or not authorized", status_code=404 ) # Check if we're disputing or updating status if 'dispute' in payload and payload['dispute']: attendance.is_disputed = True attendance.dispute_reason = payload.get('reason') attendance.dispute_resolved_by = None attendance.dispute_resolution_date = None attendance.resolution_notes = None # Notify supervisor session = attendance.session if session.course.supervisor: supervisor_prefs = ctx.session.query(NotificationPreference).filter( NotificationPreference.user_id == session.course.supervisor.id ).first() if supervisor_prefs and supervisor_prefs.receive_email: subject = f"Attendance Dispute: {session.title}" message = f"""

Attendance Dispute Notification

Hello {session.course.supervisor.first_name},

An attendance record has been disputed by tutor {attendance.tutor.first_name} {attendance.tutor.last_name}:

Session: {session.title}

Course: {session.course.code} - {session.course.title}

Student: {attendance.student.first_name} {attendance.student.last_name}

Original Status: {attendance.status.value}

Dispute Reason: {payload.get('reason', 'Not specified')}

Resolve Dispute

Best regards,
{current_app.config['APP_NAME']} Team

""" try: send_email( sender_email="kisiwa@mutabletech.co.ke", sender_password=current_app.config['MAIL_PASSWORD'], receiver_email=session.course.supervisor.email, subject=subject, message=message ) except Exception as e: current_app.logger.error(f"Failed to send dispute notification: {str(e)}") ctx.session.commit() return custom_response( success=True, data="Attendance dispute submitted successfully", status_code=200 ) elif 'status' in payload: # Only allow updating to certain statuses if payload['status'] not in ['present', 'absent', 'late']: return custom_response( success=False, data="Invalid status value", status_code=400 ) attendance.status = AttendanceStatus(payload['status']) attendance.late_minutes = payload.get('late_minutes', 0) attendance.notes = payload.get('notes') ctx.session.commit() return custom_response( success=True, data="Attendance record updated successfully", status_code=200 ) else: return custom_response( success=False, data="Must specify either dispute or status update", status_code=400 ) def get_students(self, tutor_id: str, course_id: str = None) -> Dict: """Get students taught by a tutor or supervised by a supervisor, optionally filtered by course""" with DatabaseContextManager() as ctx: # First check if this is a tutor tutor = ctx.session.query(Tutor).filter(Tutor.id == tutor_id).first() if tutor: # Get courses taught by this tutor courses = ctx.session.query(Course).join( tutor_course_association, Course.id == tutor_course_association.c.course_id ).filter( tutor_course_association.c.tutor_id == tutor_id, Course.is_active == True ) else: # Check if this is a supervisor supervisor = ctx.session.query(Supervisor).filter(Supervisor.id == tutor_id).first() if not supervisor: return custom_response( success=False, data="User not found as tutor or supervisor", status_code=404 ) # Get courses in supervisor's departments courses = ctx.session.query(Course).join( SupervisorDepartment, Course.department == SupervisorDepartment.department_name ).filter( SupervisorDepartment.supervisor_id == tutor_id, Course.is_active == True ) if course_id: courses = courses.filter(Course.id == course_id) courses = courses.all() if not courses: user_type = "tutor" if tutor else "supervisor" return custom_response( success=False, data=f"No courses found for this {user_type}", status_code=404 ) # Get all students in these courses through enrollment_courses association students = [] student_ids = set() for course in courses: # Get students through enrollment_courses association table course_students = ctx.session.query(Student).join( Enrollment, Student.id == Enrollment.student_id ).join( enrollment_courses, Enrollment.id == enrollment_courses.c.enrollment_id ).filter( enrollment_courses.c.course_id == course.id, enrollment_courses.c.status == 'active', Student.is_active == True ).all() for student in course_students: if student.id not in student_ids: student_ids.add(student.id) # Get student's courses through enrollment student_enrollments = ctx.session.query(enrollment_courses).join( Enrollment, enrollment_courses.c.enrollment_id == Enrollment.id ).filter( Enrollment.student_id == student.id, enrollment_courses.c.status == 'active' ).all() student_courses = [] for enrollment in student_enrollments: course_obj = ctx.session.query(Course).filter( Course.id == enrollment.course_id ).first() if course_obj: student_courses.append({ 'id': course_obj.id, 'code': course_obj.code, 'title': course_obj.title }) students.append({ 'id': student.id, 'student_id': student.student_id, 'first_name': student.first_name, 'last_name': student.last_name, 'email': student.email, 'program': student.program, 'year_of_study': student.year_of_study, 'courses': student_courses }) return custom_response( success=True, data={ 'total_students': len(students), 'students': students, 'user_type': 'tutor' if tutor else 'supervisor', 'user_id': tutor_id } ) def get_student_progress(self, tutor_id: str, student_id: str, course_id: str = None) -> Dict: """Get progress for a specific student across tutor's courses""" with DatabaseContextManager() as ctx: # Get the student and their speciality student = ctx.session.query(Student).filter(Student.id == student_id).first() if not student: return {"error": "Student not found"} # Verify the tutor teaches courses in the student's speciality courses = ctx.session.query(Course).join( tutor_course_association, Course.id == tutor_course_association.c.course_id ).filter( tutor_course_association.c.tutor_id == tutor_id, Course.speciality_id == student.speciality_id, Course.is_active == True ) if course_id: courses = courses.filter(Course.id == course_id) courses = courses.all() if not courses: return custom_response( success=False, data="Student not found in any of your courses", status_code=404 ) student = ctx.session.query(Student).filter(Student.id == student_id).first() if not student: return custom_response( success=False, data="Student not found", status_code=404 ) # Get progress for each course course_progress = [] for course in courses: # Get enrollment through association table enrollment_association = ctx.session.query(enrollment_courses).join( Enrollment, enrollment_courses.c.enrollment_id == Enrollment.id ).filter( Enrollment.student_id == student_id, enrollment_courses.c.course_id == course.id ).first() if not enrollment_association: continue enrollment = ctx.session.query(Enrollment).filter( Enrollment.id == enrollment_association.enrollment_id ).first() if not enrollment: continue progress = ctx.session.query(CourseProgress).filter( CourseProgress.enrollment_id == enrollment.id ).first() # Get attendance attendance = ctx.session.query( func.count(Attendance.id), func.sum(case((Attendance.status == AttendanceStatus.present, 1), else_=0)) ).join( TeachingSession, Attendance.session_id == TeachingSession.id ).filter( TeachingSession.course_id == course.id, Attendance.student_id == student_id ).first() # Get assignments assignments = ctx.session.query( func.count(AssignmentSubmission.id), func.sum(case((AssignmentSubmission.status == SubmissionStatus.graded, 1), else_=0)), func.avg(AssignmentSubmission.grade) ).join( Assignment, AssignmentSubmission.assignment_id == Assignment.id ).filter( Assignment.course_id == course.id, AssignmentSubmission.student_id == student_id ).first() course_progress.append({ 'course_id': course.id, 'course_code': course.code, 'course_title': course.title, 'enrollment_status': enrollment.status, 'completion_percentage': progress.completion_percentage if progress else 0, 'current_grade': progress.current_grade if progress else None, 'attendance': { 'present': attendance[1] if attendance else 0, 'total': attendance[0] if attendance else 0, 'rate': round((attendance[1] / attendance[0]) * 100, 1) if attendance and attendance[0] else 0 }, 'assignments': { 'completed': assignments[1] if assignments else 0, 'total': assignments[0] if assignments else 0, 'average_grade': round(assignments[2], 1) if assignments and assignments[2] else None }, 'last_accessed': progress.last_accessed.isoformat() if progress and progress.last_accessed else None }) return custom_response( success=True, data={ 'student': { 'id': student.id, 'student_id': student.student_id, 'first_name': student.first_name, 'last_name': student.last_name, 'email': student.email, 'program': student.program, 'year_of_study': student.year_of_study, 'cumulative_gpa': student.cumulative_gpa }, 'courses': course_progress } ) def get_tutor_students_with_details(self, user_id: str) -> Dict: """Get all students taught by a tutor or supervised by a supervisor with detailed academic information""" with DatabaseContextManager() as ctx: # Check if user is a tutor or supervisor tutor = ctx.session.query(Tutor).filter(Tutor.id == user_id).first() supervisor = ctx.session.query(Supervisor).filter(Supervisor.id == user_id).first() if not tutor and not supervisor: return custom_response( success=False, data="User not found as tutor or supervisor", status_code=404 ) # Determine user type and get courses accordingly if tutor: # Get courses taught by this tutor courses = ctx.session.query(Course).join( tutor_course_association, Course.id == tutor_course_association.c.course_id ).filter( tutor_course_association.c.tutor_id == user_id, Course.is_active == True ).all() user_name = f"{tutor.first_name} {tutor.last_name}" user_type = "tutor" else: # Get courses supervised by this supervisor courses = ctx.session.query(Course).filter( Course.supervisor_id == user_id, Course.is_active == True ).all() user_name = f"{supervisor.first_name} {supervisor.last_name}" user_type = "supervisor" # Courses already retrieved above based on user type if not courses: return custom_response( success=True, data={ 'students': [], 'total': 0, 'message': f'No courses found for this {user_type}' } ) # Get all students in these courses students = [] student_ids = set() for course in courses: # Get students through speciality relationship course_students = ctx.session.query(Student).join( Speciality, Student.speciality_id == Speciality.id ).filter( Speciality.id == course.speciality_id, Student.is_active == True ).all() for student in course_students: if student.id not in student_ids: student_ids.add(student.id) # Get enrollment for this course through the new association table enrollment = ctx.session.query(Enrollment).join( enrollment_courses, Enrollment.id == enrollment_courses.c.enrollment_id ).filter( Enrollment.student_id == student.id, enrollment_courses.c.course_id == course.id, enrollment_courses.c.status == 'active', Enrollment.status == 'active' ).first() # Get progress for this course progress = ctx.session.query(CourseProgress).filter( CourseProgress.enrollment_id == enrollment.id ).first() if enrollment else None # Get attendance records attendance_result = ctx.session.query( func.count(Attendance.id), func.sum(case((Attendance.status == AttendanceStatus.present, 1), else_=0)) ).join( TeachingSession, Attendance.session_id == TeachingSession.id ).filter( TeachingSession.course_id == course.id, Attendance.student_id == student.id ).first() # Convert Row to tuple for JSON serialization attendance = (attendance_result[0], attendance_result[1]) if attendance_result else None # Get assignment submissions assignments_result = ctx.session.query( func.count(AssignmentSubmission.id), func.avg(AssignmentSubmission.grade) ).join( Assignment, AssignmentSubmission.assignment_id == Assignment.id ).filter( Assignment.course_id == course.id, AssignmentSubmission.student_id == student.id, AssignmentSubmission.status == SubmissionStatus.graded ).first() # Convert Row to tuple for JSON serialization assignments = (assignments_result[0], assignments_result[1]) if assignments_result else None students.append({ 'id': student.id, 'student_id': student.student_id, 'first_name': student.first_name, 'last_name': student.last_name, 'email': student.email or '', 'staff_id': getattr(student, 'staff_id', '') or '', 'departments': [item.to_json() for item in student.departments] if hasattr(student, 'departments') and student.departments else [], 'qualification': getattr(student, 'qualification', '') or '', 'specialization': getattr(student, 'specialization', '') or '', 'office_location': getattr(student, 'office_location', '') or '', 'is_full_time': bool(getattr(student, 'is_full_time', False)), 'is_on_leave': bool(getattr(student, 'is_on_leave', False)), 'is_on_probation': getattr(student, 'is_on_probation', False), 'program': getattr(student, 'program', '') or '', 'year_of_study': getattr(student, 'year_of_study', 1) or 1, 'cumulative_gpa': getattr(student, 'cumulative_gpa', None), 'courses': [{ 'id': course.id, 'code': course.code, 'title': course.title, 'attendance': { 'present': attendance[1] if attendance else 0, 'total': attendance[0] if attendance else 0, 'rate': round((attendance[1] / attendance[0] * 100, 1)) if attendance and attendance[0] else 0 }, 'assignments': { 'submitted': assignments[0] if assignments else 0, 'average_grade': round(assignments[1], 1) if assignments and assignments[1] else None }, 'progress': { 'completion': progress.completion_percentage if progress else 0, 'current_grade': progress.current_grade if progress else None }, 'enrollment_status': enrollment.status if enrollment else None }], 'overall_attendance': None # Will be calculated separately }) # Calculate overall statistics for each student for student in students: # Calculate overall attendance across all courses overall_attendance_result = ctx.session.query( func.count(Attendance.id), func.sum(case((Attendance.status == AttendanceStatus.present, 1), else_=0)) ).join( TeachingSession, Attendance.session_id == TeachingSession.id ).join( Course, TeachingSession.course_id == Course.id ).filter( Course.id.in_([c.id for c in courses]), Attendance.student_id == student['id'] ).first() if overall_attendance_result: total = overall_attendance_result[0] or 0 present = overall_attendance_result[1] or 0 student['overall_attendance_rate'] = round((present / total) * 100, 1) if total else 0 else: student['overall_attendance_rate'] = 0 # Calculate average assignment grade across all courses grades = [c['assignments']['average_grade'] for c in student['courses'] if c['assignments']['average_grade'] is not None] student['average_assignment_grade'] = round(sum(grades) / len(grades), 1) if grades else None return custom_response( success=True, data={ 'students': students, 'total': len(students), 'user': { 'id': user_id, 'name': user_name, 'type': user_type, 'total_courses': len(courses) } } ) def get_tutor_courses_with_details(self, tutor_id: str) -> Dict: """Get all courses taught by a tutor with detailed enrollment information""" with DatabaseContextManager() as ctx: # Get courses assigned to this tutor courses = ctx.session.query(Course).join( tutor_course_association, Course.id == tutor_course_association.c.course_id ).filter( tutor_course_association.c.tutor_id == tutor_id, Course.is_active == True ).all() if not courses: return custom_response( success=False, data="No courses found for this tutor", status_code=404 ) # Prepare detailed course information courses_data = [] for course in courses: # Get enrollment statistics using the new enrollment_courses association table enrollments = ctx.session.query(Enrollment).join( enrollment_courses, Enrollment.id == enrollment_courses.c.enrollment_id ).filter( enrollment_courses.c.course_id == course.id, Enrollment.status == 'active' ).all() # Get attendance statistics attendance_stats = ctx.session.query( func.count(Attendance.id), func.sum(case((Attendance.status == AttendanceStatus.present, 1), else_=0)) ).join( TeachingSession, Attendance.session_id == TeachingSession.id ).filter( TeachingSession.course_id == course.id ).first() # Get assignment statistics assignment_stats = ctx.session.query( func.count(Assignment.id), func.count(AssignmentSubmission.id), func.avg(AssignmentSubmission.grade) ).outerjoin( AssignmentSubmission, Assignment.id == AssignmentSubmission.assignment_id ).filter( Assignment.course_id == course.id ).first() # Get recent sessions recent_sessions = ctx.session.query(TeachingSession).filter( TeachingSession.course_id == course.id ).order_by(TeachingSession.start_time.desc()).limit(3).all() # Get supervisor information supervisor = ctx.session.query(Supervisor).filter( Supervisor.id == course.supervisor_id ).first() # Get speciality information speciality = ctx.session.query(Speciality).filter( Speciality.id == course.speciality_id ).first() courses_data.append({ 'id': course.id, 'code': course.code, 'title': course.title, 'description': course.description, 'credits': course.credits, 'department': course.department, 'semester': course.semester, 'is_primary': ctx.session.query(tutor_course_association).filter( tutor_course_association.c.tutor_id == tutor_id, tutor_course_association.c.course_id == course.id ).first().is_primary, 'total_students': len(enrollments), 'active_students': len([e for e in enrollments if e.status == 'active']), 'attendance': { 'present': attendance_stats[1] if attendance_stats else 0, 'total': attendance_stats[0] if attendance_stats else 0, 'rate': round((attendance_stats[1] / attendance_stats[0] * 100, 1)) if attendance_stats and attendance_stats[0] else 0 }, 'assignments': { 'total': assignment_stats[0] if assignment_stats else 0, 'submissions': assignment_stats[1] if assignment_stats else 0, 'average_grade': round(assignment_stats[2], 1) if assignment_stats and assignment_stats[2] else None }, 'recent_sessions': [{ 'id': session.id, 'title': session.title or f"{session.course.code} - {session.course.title}" if session.course else f"Session {session.id}", 'date': session.start_time.strftime('%Y-%m-%d') if session.start_time else 'N/A', 'status': session.status, 'attendance': len([a for a in session.attendance_records if a.status == AttendanceStatus.present]) } for session in recent_sessions], 'supervisor': { 'id': supervisor.id if supervisor else None, 'name': f"{supervisor.first_name} {supervisor.last_name}" if supervisor else None, 'email': supervisor.email if supervisor else None } if supervisor else None, 'speciality': { 'id': speciality.id if speciality else None, 'name': speciality.name if speciality else None, 'description': speciality.description if speciality else None } if speciality else None }) return custom_response( success=True, data={ 'courses': courses_data, 'total_courses': len(courses_data) } ) def get_user_courses(self, user_id: str) -> Dict: """Get courses for any user (tutor or supervisor)""" with DatabaseContextManager() as ctx: # First check if user is a tutor tutor = ctx.session.query(Tutor).filter(Tutor.id == user_id).first() if tutor: return self.get_tutor_courses_with_details(user_id) # If not a tutor, check if user is a supervisor supervisor = ctx.session.query(Supervisor).filter(Supervisor.id == user_id).first() if supervisor: # Get courses supervised by this supervisor courses = ctx.session.query(Course).filter( Course.supervisor_id == user_id, Course.is_active == True ).all() if not courses: return custom_response( success=False, data="No courses found for this supervisor", status_code=404 ) # Prepare course information for supervisor courses_data = [] for course in courses: # Get enrollment statistics using the new enrollment_courses association table enrollments = ctx.session.query(Enrollment).join( enrollment_courses, Enrollment.id == enrollment_courses.c.enrollment_id ).filter( enrollment_courses.c.course_id == course.id, Enrollment.status == 'active' ).all() # Get attendance statistics attendance_stats = ctx.session.query( func.count(Attendance.id), func.sum(case((Attendance.status == AttendanceStatus.present, 1), else_=0)) ).join( TeachingSession, Attendance.session_id == TeachingSession.id ).filter( TeachingSession.course_id == course.id ).first() # Get assignment statistics assignment_stats = ctx.session.query( func.count(Assignment.id), func.count(AssignmentSubmission.id), func.avg(AssignmentSubmission.grade) ).outerjoin( AssignmentSubmission, Assignment.id == AssignmentSubmission.assignment_id ).filter( Assignment.course_id == course.id ).first() # Get tutors for this course tutors = ctx.session.query(Tutor).join( tutor_course_association, Tutor.id == tutor_course_association.c.tutor_id ).filter( tutor_course_association.c.course_id == course.id ).all() # Get speciality information speciality = ctx.session.query(Speciality).filter( Speciality.id == course.speciality_id ).first() courses_data.append({ 'id': course.id, 'code': course.code, 'title': course.title, 'description': course.description, 'credits': course.credits, 'department': course.department, 'semester': course.semester, 'is_primary': True, # For supervisor view, all courses are primary 'total_students': len(enrollments), 'active_students': len([e for e in enrollments if e.status == 'active']), 'attendance': { 'present': attendance_stats[1] if attendance_stats else 0, 'total': attendance_stats[0] if attendance_stats else 0, 'rate': round((attendance_stats[1] / attendance_stats[0] * 100), 1) if attendance_stats and attendance_stats[0] else 0 }, 'assignments': { 'total': assignment_stats[0] if assignment_stats else 0, 'submissions': assignment_stats[1] if assignment_stats else 0, 'average_grade': round(assignment_stats[2], 1) if assignment_stats and assignment_stats[2] else None }, 'tutors': [{ 'id': tutor.id, 'name': f"{tutor.first_name} {tutor.last_name}", 'email': tutor.email } for tutor in tutors], 'speciality': { 'id': speciality.id if speciality else None, 'name': speciality.name if speciality else None, 'description': speciality.description if speciality else None } if speciality else None }) return custom_response( success=True, data={ 'courses': courses_data, 'total_courses': len(courses_data) } ) # If user is neither tutor nor supervisor return custom_response( success=False, data="User not found or not authorized to view courses", status_code=404 ) def get_supervised_tutors_with_stats(self, supervisor_id: str) -> Dict: """ Get all tutors associated with courses supervised by the given supervisor, including their performance statistics. Args: supervisor_id: ID of the supervisor Returns: Response with list of tutors and their statistics """ with DatabaseContextManager() as ctx: # Verify supervisor exists supervisor = ctx.session.query(Supervisor).filter(Supervisor.id == supervisor_id).first() if not supervisor: return custom_response( success=False, data="Supervisor not found", status_code=404 ) # Get all courses supervised by this supervisor courses = ctx.session.query(Course).filter( Course.supervisor_id == supervisor_id, Course.is_active == True ).all() if not courses: return custom_response( success=True, data={ 'tutors': [], 'total': 0, 'message': 'No active courses found for this supervisor' } ) # Get all tutors associated with these courses tutors = ctx.session.query(Tutor).join( tutor_course_association, Tutor.id == tutor_course_association.c.tutor_id ).filter( tutor_course_association.c.course_id.in_([c.id for c in courses]), Tutor.is_active == True ).distinct().all() # Prepare response data with statistics for each tutor tutors_data = [] for tutor in tutors: # Get base tutor info tutor_data = self._tutor_to_dict(tutor) # Get assigned courses (only those supervised by this supervisor) assigned_courses = ctx.session.query(Course).join( tutor_course_association, Course.id == tutor_course_association.c.course_id ).filter( tutor_course_association.c.tutor_id == tutor.id, Course.supervisor_id == supervisor_id, Course.is_active == True ).all() tutor_data['assigned_courses'] = [{ 'id': course.id, 'code': course.code, 'title': course.title, 'is_primary': ctx.session.query(tutor_course_association).filter( tutor_course_association.c.tutor_id == tutor.id, tutor_course_association.c.course_id == course.id ).first().is_primary } for course in assigned_courses] # Calculate teaching statistics # Get all sessions for this tutor in supervised courses sessions = ctx.session.query(TeachingSession).filter( TeachingSession.tutor_id == tutor.id, TeachingSession.course_id.in_([c.id for c in courses]) ).all() # Get attendance records for these sessions attendance_records = ctx.session.query(Attendance).join( TeachingSession, Attendance.session_id == TeachingSession.id ).filter( TeachingSession.tutor_id == tutor.id, TeachingSession.course_id.in_([c.id for c in courses]) ).all() # Calculate statistics total_sessions = len(sessions) completed_sessions = len([s for s in sessions if s.status == 'completed']) verified_sessions = len([s for s in sessions if s.is_verified]) present_count = sum(1 for r in attendance_records if r.status == AttendanceStatus.present) attendance_rate = round((present_count / len(attendance_records)) * 100, 1) if attendance_records else 0 # Get recent verification methods recent_methods = ctx.session.query(TeachingSession.verification_method).filter( TeachingSession.tutor_id == tutor.id, TeachingSession.course_id.in_([c.id for c in courses]), TeachingSession.is_verified == True ).order_by(TeachingSession.verified_at.desc()).limit(3).all() # Get upcoming sessions count upcoming_sessions = ctx.session.query(TeachingSession).filter( TeachingSession.tutor_id == tutor.id, TeachingSession.course_id.in_([c.id for c in courses]), TeachingSession.start_time >= datetime.utcnow(), TeachingSession.status == 'scheduled' ).count() # Add statistics to tutor data tutor_data['performance_stats'] = { 'total_sessions': total_sessions, 'completed_sessions': completed_sessions, 'verified_sessions': verified_sessions, 'verification_rate': round((verified_sessions / completed_sessions) * 100, 1) if completed_sessions else 0, 'attendance_rate': attendance_rate, 'common_verification_methods': [m[0].value for m in recent_methods if m[0]], 'upcoming_sessions': upcoming_sessions, 'courses_count': len(assigned_courses) } # Get recent attendance (last 5 sessions) recent_attendance = ctx.session.query(Attendance).join( TeachingSession, Attendance.session_id == TeachingSession.id ).filter( TeachingSession.tutor_id == tutor.id, TeachingSession.course_id.in_([c.id for c in courses]) ).order_by(Attendance.timestamp.desc()).limit(5).all() tutor_data['recent_attendance'] = [{ 'date': r.session.start_time.date().isoformat(), 'status': r.status.value, 'course_code': r.session.course.code, 'student_name': f"{r.student.first_name} {r.student.last_name}" } for r in recent_attendance] tutors_data.append(tutor_data) return custom_response( success=True, data={ 'tutors': tutors_data, 'total': len(tutors_data), 'supervisor': { 'id': supervisor.id, 'name': f"{supervisor.first_name} {supervisor.last_name}", 'departments': [ item.to_json() for item in supervisor.departments ] } } ) def assign_course(self, tutor_id: str, payload: Dict) -> Dict: """Assign a course to a tutor""" with DatabaseContextManager() as ctx: tutor = ctx.session.query(Tutor).filter(Tutor.id == tutor_id).first() if not tutor: return custom_response( success=False, data="Tutor not found", status_code=404 ) course_id = payload.get('course_id') is_primary = payload.get('is_primary', False) if not course_id: return custom_response( success=False, data="Course ID is required", status_code=400 ) course = ctx.session.query(Course).filter(Course.id == course_id).first() if not course: return custom_response( success=False, data="Course not found", status_code=404 ) # Check if course is already assigned to this tutor existing_assignment = ctx.session.query(tutor_course_association).filter( tutor_course_association.c.tutor_id == tutor_id, tutor_course_association.c.course_id == course_id ).first() if existing_assignment: return custom_response( success=False, data="Course is already assigned to this tutor", status_code=400 ) # Add the course assignment ctx.session.execute( tutor_course_association.insert().values( tutor_id=tutor_id, course_id=course_id, is_primary=is_primary ) ) ctx.session.commit() return custom_response( success=True, data="Course assigned successfully" ) def remove_course(self, tutor_id: str, payload: Dict) -> Dict: """Remove a course from a tutor""" with DatabaseContextManager() as ctx: tutor = ctx.session.query(Tutor).filter(Tutor.id == tutor_id).first() if not tutor: return custom_response( success=False, data="Tutor not found", status_code=404 ) course_id = payload.get('course_id') if not course_id: return custom_response( success=False, data="Course ID is required", status_code=400 ) # Check if course is assigned to this tutor existing_assignment = ctx.session.query(tutor_course_association).filter( tutor_course_association.c.tutor_id == tutor_id, tutor_course_association.c.course_id == course_id ).first() if not existing_assignment: return custom_response( success=False, data="Course is not assigned to this tutor", status_code=400 ) # Remove the course assignment ctx.session.execute( tutor_course_association.delete().where( and_( tutor_course_association.c.tutor_id == tutor_id, tutor_course_association.c.course_id == course_id ) ) ) ctx.session.commit() return custom_response( success=True, data="Course removed successfully" ) def get_tutor_departments(self, tutor_id: str) -> Dict: """Get all departments for a specific tutor""" with DatabaseContextManager() as ctx: tutor = ctx.session.query(Tutor).filter(Tutor.id == tutor_id).first() if not tutor: return custom_response( success=False, data="Tutor not found", status_code=404 ) departments = [dept.to_json() for dept in tutor.departments if dept.is_active] return custom_response( success=True, data={ 'tutor_id': tutor_id, 'departments': departments, 'total': len(departments) }, status_code=200 ) def add_department_to_tutor(self, tutor_id: str, payload: Dict) -> Dict: """Add a department to a tutor""" with DatabaseContextManager() as ctx: tutor = ctx.session.query(Tutor).filter(Tutor.id == tutor_id).first() if not tutor: return custom_response( success=False, data="Tutor not found", status_code=404 ) department_name = payload.get('department_name') is_primary = payload.get('is_primary', False) assigned_by = payload.get('assigned_by') notes = payload.get('notes') if not department_name: return custom_response( success=False, data="Department name is required", status_code=400 ) # Check if department already exists for this tutor existing_dept = ctx.session.query(TutorDepartment).filter( TutorDepartment.tutor_id == tutor_id, TutorDepartment.department_name == department_name, TutorDepartment.is_active == True ).first() if existing_dept: return custom_response( success=False, data="Tutor is already assigned to this department", status_code=400 ) # If this is a primary department, unset other primary departments if is_primary: ctx.session.query(TutorDepartment).filter( TutorDepartment.tutor_id == tutor_id, TutorDepartment.is_active == True ).update({'is_primary': False}) # Create new department assignment new_dept = TutorDepartment( tutor_id=tutor_id, department_name=department_name, is_primary=is_primary, assigned_by=assigned_by, notes=notes ) ctx.session.add(new_dept) ctx.session.commit() return custom_response( success=True, data={ 'message': 'Department assigned successfully', 'department': new_dept.to_json() }, status_code=201 ) def update_tutor_department(self, tutor_id: str, department_id: str, payload: Dict) -> Dict: """Update a department assignment for a tutor""" with DatabaseContextManager() as ctx: # Find the department assignment dept_assignment = ctx.session.query(TutorDepartment).filter( TutorDepartment.id == department_id, TutorDepartment.tutor_id == tutor_id, TutorDepartment.is_active == True ).first() if not dept_assignment: return custom_response( success=False, data="Department assignment not found", status_code=404 ) # Update fields if 'is_primary' in payload: is_primary = payload['is_primary'] dept_assignment.is_primary = is_primary # If this is now primary, unset other primary departments if is_primary: ctx.session.query(TutorDepartment).filter( TutorDepartment.tutor_id == tutor_id, TutorDepartment.id != department_id, TutorDepartment.is_active == True ).update({'is_primary': False}) if 'notes' in payload: dept_assignment.notes = payload['notes'] dept_assignment.updated_at = datetime.utcnow() ctx.session.commit() return custom_response( success=True, data={ 'message': 'Department assignment updated successfully', 'department': dept_assignment.to_json() }, status_code=200 ) def remove_department_from_tutor(self, tutor_id: str, department_id: str) -> Dict: """Remove a department from a tutor (soft delete)""" with DatabaseContextManager() as ctx: dept_assignment = ctx.session.query(TutorDepartment).filter( TutorDepartment.id == department_id, TutorDepartment.tutor_id == tutor_id, TutorDepartment.is_active == True ).first() if not dept_assignment: return custom_response( success=False, data="Department assignment not found", status_code=404 ) # Soft delete dept_assignment.is_active = False dept_assignment.updated_at = datetime.utcnow() ctx.session.commit() return custom_response( success=True, data="Department removed successfully", status_code=204 ) def remove_department_by_name(self, tutor_id: str, department_name: str) -> Dict: """Remove a department from a tutor by department name (soft delete)""" with DatabaseContextManager() as ctx: dept_assignment = ctx.session.query(TutorDepartment).filter( TutorDepartment.tutor_id == tutor_id, TutorDepartment.department_name == department_name, TutorDepartment.is_active == True ).first() if not dept_assignment: return custom_response( success=False, data=f"Department '{department_name}' not found for this tutor", status_code=404 ) # Soft delete dept_assignment.is_active = False dept_assignment.updated_at = datetime.utcnow() dept_assignment.notes = f"Removed by admin at {datetime.utcnow().strftime('%Y-%m-%d %H:%M:%S')}" ctx.session.commit() return custom_response( success=True, data={ 'message': f"Department '{department_name}' removed successfully", 'department_id': dept_assignment.id, 'department_name': department_name, 'removed_at': datetime.utcnow().isoformat() }, status_code=200 ) def bulk_remove_departments(self, tutor_id: str, department_names: list) -> Dict: """Remove multiple departments from a tutor (soft delete)""" with DatabaseContextManager() as ctx: if not department_names: return custom_response( success=False, data="No department names provided", status_code=400 ) # Find all active department assignments for the specified departments dept_assignments = ctx.session.query(TutorDepartment).filter( TutorDepartment.tutor_id == tutor_id, TutorDepartment.department_name.in_(department_names), TutorDepartment.is_active == True ).all() if not dept_assignments: return custom_response( success=False, data="No active department assignments found for the specified departments", status_code=404 ) removed_count = 0 removed_departments = [] for dept_assignment in dept_assignments: # Soft delete dept_assignment.is_active = False dept_assignment.updated_at = datetime.utcnow() dept_assignment.notes = f"Bulk removed by admin at {datetime.utcnow().strftime('%Y-%m-%d %H:%M:%S')}" removed_departments.append({ 'id': dept_assignment.id, 'name': dept_assignment.department_name, 'was_primary': dept_assignment.is_primary }) removed_count += 1 ctx.session.commit() return custom_response( success=True, data={ 'message': f"Successfully removed {removed_count} departments", 'removed_count': removed_count, 'removed_departments': removed_departments, 'removed_at': datetime.utcnow().isoformat() }, status_code=200 ) def delete_department_permanently(self, department_id: str) -> Dict: """Permanently delete a department assignment (hard delete)""" with DatabaseContextManager() as ctx: dept_assignment = ctx.session.query(TutorDepartment).filter( TutorDepartment.id == department_id ).first() if not dept_assignment: return custom_response( success=False, data="Department assignment not found", status_code=404 ) # Store info before deletion dept_info = { 'id': dept_assignment.id, 'tutor_id': dept_assignment.tutor_id, 'department_name': dept_assignment.department_name, 'was_primary': dept_assignment.is_primary } # Hard delete ctx.session.delete(dept_assignment) ctx.session.commit() return custom_response( success=True, data={ 'message': f"Department '{dept_info['department_name']}' permanently deleted", 'deleted_department': dept_info, 'deleted_at': datetime.utcnow().isoformat() }, status_code=200 ) def _calculate_session_duration(self, start_time, end_time) -> int: """Calculate session duration in minutes""" if not start_time or not end_time: return 0 # Convert time objects to datetime for calculation start_dt = datetime.combine(datetime.now().date(), start_time) end_dt = datetime.combine(datetime.now().date(), end_time) # Handle cases where end_time is on the next day if end_dt < start_dt: end_dt += timedelta(days=1) duration = end_dt - start_dt return int(duration.total_seconds() / 60) def get_tutor_performance(self, tutor_id: str) -> Dict: """ Get comprehensive performance data for a specific tutor Args: tutor_id: ID of the tutor Returns: Response with detailed tutor performance data """ with DatabaseContextManager() as ctx: # Verify tutor exists tutor = ctx.session.query(Tutor).filter(Tutor.id == tutor_id).first() if not tutor: return custom_response( success=False, data="Tutor not found", status_code=404 ) # Get base tutor info tutor_data = self._tutor_to_dict(tutor) # Get assigned courses assigned_courses = ctx.session.query(Course).join( tutor_course_association, Course.id == tutor_course_association.c.course_id ).filter( tutor_course_association.c.tutor_id == tutor_id, Course.is_active == True ).all() # Calculate course statistics courses_data = [] for course in assigned_courses: # Get student count for this course using enrollment_courses association table student_count = ctx.session.query(enrollment_courses).filter( enrollment_courses.c.course_id == course.id, enrollment_courses.c.status == 'active' ).count() # Get attendance rate for this course sessions = ctx.session.query(TeachingSession).filter( TeachingSession.course_id == course.id, TeachingSession.tutor_id == tutor_id ).all() attendance_records = ctx.session.query(Attendance).join( TeachingSession, Attendance.session_id == TeachingSession.id ).filter( TeachingSession.course_id == course.id, TeachingSession.tutor_id == tutor_id ).all() present_count = sum(1 for r in attendance_records if r.status == AttendanceStatus.present) attendance_rate = round((present_count / len(attendance_records)) * 100, 1) if attendance_records else 0 courses_data.append({ 'id': course.id, 'code': course.code, 'title': course.title, 'is_primary': ctx.session.query(tutor_course_association).filter( tutor_course_association.c.tutor_id == tutor_id, tutor_course_association.c.course_id == course.id ).first().is_primary, 'student_count': student_count, 'attendance_rate': attendance_rate }) # Get all teaching sessions for this tutor all_sessions = ctx.session.query(TeachingSession).filter( TeachingSession.tutor_id == tutor_id ).all() # Calculate performance statistics total_sessions = len(all_sessions) completed_sessions = len([s for s in all_sessions if s.status == 'completed']) verified_sessions = len([s for s in all_sessions if s.is_verified]) # Calculate overall attendance rate all_attendance = ctx.session.query(Attendance).join( TeachingSession, Attendance.session_id == TeachingSession.id ).filter( TeachingSession.tutor_id == tutor_id ).all() present_count = sum(1 for r in all_attendance if r.status == AttendanceStatus.present) attendance_rate = round((present_count / len(all_attendance)) * 100, 1) if all_attendance else 0 # Get verification rate verification_rate = round((verified_sessions / completed_sessions) * 100, 1) if completed_sessions else 0 # Get upcoming sessions count upcoming_sessions = ctx.session.query(TeachingSession).filter( TeachingSession.tutor_id == tutor_id, TeachingSession.start_time >= datetime.utcnow(), TeachingSession.status == 'scheduled' ).count() # Get recent verification methods recent_methods = ctx.session.query(TeachingSession.verification_method).filter( TeachingSession.tutor_id == tutor_id, TeachingSession.is_verified == True, TeachingSession.verification_method.isnot(None) ).order_by(TeachingSession.verified_at.desc()).limit(5).all() # Get monthly attendance data (last 6 months) monthly_attendance = [] for i in range(6): month_start = datetime.utcnow() - timedelta(days=30*i) month_end = month_start + timedelta(days=30) month_sessions = ctx.session.query(TeachingSession).filter( TeachingSession.tutor_id == tutor_id, TeachingSession.start_time >= month_start, TeachingSession.start_time < month_end ).all() month_attendance_records = ctx.session.query(Attendance).join( TeachingSession, Attendance.session_id == TeachingSession.id ).filter( TeachingSession.tutor_id == tutor_id, TeachingSession.start_time >= month_start, TeachingSession.start_time < month_end ).all() present_count = sum(1 for r in month_attendance_records if r.status == AttendanceStatus.present) month_rate = round((present_count / len(month_attendance_records)) * 100, 1) if month_attendance_records else 0 monthly_attendance.append({ 'month': month_start.strftime('%B %Y'), 'rate': month_rate }) # Get session completion trend (last 4 weeks) session_completion_trend = [] for i in range(4): week_start = datetime.utcnow() - timedelta(weeks=i) week_end = week_start + timedelta(weeks=1) week_sessions = ctx.session.query(TeachingSession).filter( TeachingSession.tutor_id == tutor_id, TeachingSession.start_time >= week_start, TeachingSession.start_time < week_end ).all() completed_count = len([s for s in week_sessions if s.status == 'completed']) session_completion_trend.append({ 'week': f"Week {4-i}", 'completed': completed_count, 'total': len(week_sessions) }) # Get recent teaching sessions (last 10) recent_sessions = ctx.session.query(TeachingSession).filter( TeachingSession.tutor_id == tutor_id ).order_by(TeachingSession.start_time.desc()).limit(10).all() teaching_sessions = [{ 'id': session.id, 'date': session.start_time.strftime('%Y-%m-%d'), 'course_code': session.course.code if session.course else 'N/A', 'title': session.title or 'Untitled Session', 'status': session.status, 'duration': self._calculate_session_duration(session.start_time, session.end_time), 'student_count': ctx.session.query(enrollment_courses).filter( enrollment_courses.c.course_id == session.course_id, enrollment_courses.c.status == 'active' ).count() if session.course_id else 0, 'verification_method': session.verification_method or 'Not specified' } for session in recent_sessions] # Get recent attendance records (last 10) recent_attendance = ctx.session.query(Attendance).join( TeachingSession, Attendance.session_id == TeachingSession.id ).filter( TeachingSession.tutor_id == tutor_id ).order_by(Attendance.timestamp.desc()).limit(10).all() attendance_records = [{ 'date': attendance.session.start_time.strftime('%Y-%m-%d'), 'status': attendance.status.value, 'course_code': attendance.session.course.code if attendance.session.course else 'N/A', 'student_name': f"{attendance.student.first_name} {attendance.student.last_name}", 'session_duration': self._calculate_session_duration(attendance.session.start_time, attendance.session.end_time) } for attendance in recent_attendance] # Compile performance data performance_data = { **tutor_data, 'performance_stats': { 'attendance_rate': attendance_rate, 'completed_sessions': completed_sessions, 'total_sessions': total_sessions, 'verified_sessions': verified_sessions, 'verification_rate': verification_rate, 'courses_count': len(courses_data), 'upcoming_sessions': upcoming_sessions, 'common_verification_methods': [m[0] for m in recent_methods if m[0]], 'monthly_attendance': monthly_attendance, 'session_completion_trend': session_completion_trend }, 'assigned_courses': courses_data, 'recent_attendance': attendance_records, 'teaching_sessions': teaching_sessions } return custom_response( success=True, data=performance_data ) def get_tutor_workload_by_supervisor(self, supervisor_id: str) -> Dict: """Get tutor workload data filtered by supervisor's departments with calculated workload based on course credits and academic sessions""" with DatabaseContextManager() as ctx: try: # Get the supervisor and their departments supervisor = ctx.session.query(Supervisor).filter( Supervisor.id == supervisor_id, Supervisor.is_active == True ).first() if not supervisor: return custom_response( success=False, data="Supervisor not found", status_code=404 ) # Get all departments this supervisor belongs to supervisor_departments = [dept.department_name for dept in supervisor.departments if dept.is_active] if not supervisor_departments: return custom_response( success=False, data="Supervisor has no active departments assigned", status_code=404 ) # Get all tutors in the supervisor's departments (not just directly supervised) supervised_tutors = ctx.session.query(Tutor).join( TutorDepartment, Tutor.id == TutorDepartment.tutor_id ).filter( TutorDepartment.department_name.in_(supervisor_departments), TutorDepartment.is_active == True, Tutor.is_active == True ).all() if not supervised_tutors: # Return empty data with zero stats instead of error return custom_response( success=True, data={ 'tutors': [], 'summary': { 'total_tutors': 0, 'total_weekly_hours': 0, 'average_weekly_hours': 0, 'overloaded_count': 0, 'underutilized_count': 0, 'optimal_count': 0, 'department_stats': {} } }, status_code=200 ) workload_data = [] for tutor in supervised_tutors: # Get all courses assigned to this tutor tutor_courses = ctx.session.query(Course).join( tutor_course_association, Course.id == tutor_course_association.c.course_id ).filter( tutor_course_association.c.tutor_id == tutor.id, Course.is_active == True ).all() total_weekly_hours = 0 total_course_hours = 0 courses_data = [] for course in tutor_courses: # Calculate weekly hours from course credits weekly_hours = course.credits if course.credits else 0 total_weekly_hours += weekly_hours # Calculate total course hours if academic session exists course_total_hours = 0 if course.academic_session: # Calculate weeks between start and end date start_date = course.academic_session.start_date end_date = course.academic_session.end_date if start_date and end_date: from datetime import datetime start_dt = datetime.combine(start_date, datetime.min.time()) end_dt = datetime.combine(end_date, datetime.min.time()) weeks_diff = (end_dt - start_dt).days / 7 course_total_hours = weekly_hours * weeks_diff total_course_hours += course_total_hours # Get student count for this course using enrollment_courses association table student_count = ctx.session.query(enrollment_courses).filter( enrollment_courses.c.course_id == course.id, enrollment_courses.c.status == 'active' ).count() courses_data.append({ 'course_id': course.id, 'course_code': course.code, 'course_name': course.title, 'department': course.department, 'credits': course.credits, 'weekly_hours': weekly_hours, 'total_hours': course_total_hours, 'student_count': student_count, 'academic_session': { 'id': course.academic_session.id, 'name': course.academic_session.name, 'start_date': str(course.academic_session.start_date) if course.academic_session.start_date else None, 'end_date': str(course.academic_session.end_date) if course.academic_session.end_date else None } if course.academic_session else None }) # Calculate workload status max_hours = 28 # Updated from 20 to 28 utilization_percentage = (total_weekly_hours / max_hours) * 100 if max_hours > 0 else 0 # Updated thresholds: <24h underutilized, 24-28h optimal, >28h overloaded if total_weekly_hours < 24: status = 'underutilized' elif total_weekly_hours <= 28: status = 'optimal' else: status = 'overloaded' # Get primary department primary_dept = tutor.get_primary_department() or 'No Department' workload_data.append({ 'tutor_id': tutor.id, 'name': f"{tutor.first_name} {tutor.last_name}", 'email': tutor.email, 'department': primary_dept, 'all_departments': tutor.get_all_departments(), 'total_weekly_hours': total_weekly_hours, 'total_course_hours': total_course_hours, 'max_hours': max_hours, 'utilization_percentage': round(utilization_percentage, 1), 'status': status, 'courses_count': len(courses_data), 'courses': courses_data, 'is_full_time': tutor.is_full_time, 'is_on_leave': tutor.is_on_leave, 'qualification': tutor.qualification, 'specialization': tutor.specialization, 'years_of_teaching': tutor.years_of_teaching, 'office_location': tutor.office_location, 'hourly_rate': tutor.hourly_rate }) # Calculate summary statistics total_tutors = len(workload_data) total_weekly_hours = sum(t['total_weekly_hours'] for t in workload_data) average_weekly_hours = total_weekly_hours / total_tutors if total_tutors > 0 else 0 overloaded_count = len([t for t in workload_data if t['status'] == 'overloaded']) underutilized_count = len([t for t in workload_data if t['status'] == 'underutilized']) optimal_count = len([t for t in workload_data if t['status'] == 'optimal']) # Department breakdown department_stats = {} for tutor in workload_data: dept = tutor['department'] if dept not in department_stats: department_stats[dept] = { 'tutor_count': 0, 'total_hours': 0, 'average_hours': 0 } department_stats[dept]['tutor_count'] += 1 department_stats[dept]['total_hours'] += tutor['total_weekly_hours'] for dept in department_stats: department_stats[dept]['average_hours'] = department_stats[dept]['total_hours'] / department_stats[dept]['tutor_count'] return custom_response( success=True, data={ 'tutors': workload_data, 'summary': { 'total_tutors': total_tutors, 'total_weekly_hours': total_weekly_hours, 'average_weekly_hours': round(average_weekly_hours, 1), 'overloaded_count': overloaded_count, 'underutilized_count': underutilized_count, 'optimal_count': optimal_count, 'department_stats': department_stats } }, status_code=200 ) except Exception as e: current_app.logger.error(f"Error getting tutor workload: {str(e)}", exc_info=True) return custom_response( success=False, data=f"Failed to get tutor workload: {str(e)}", status_code=500 ) def get_detailed_tutor_workload(self, tutor_id: str, supervisor_id: str = None) -> Dict: """Get comprehensive detailed workload breakdown for a specific tutor""" with DatabaseContextManager() as ctx: try: # Get the tutor tutor = ctx.session.query(Tutor).filter( Tutor.id == tutor_id, Tutor.is_active == True ).first() if not tutor: return custom_response( success=False, data="Tutor not found", status_code=404 ) # If supervisor_id is provided, verify the tutor is in supervisor's departments if supervisor_id: supervisor = ctx.session.query(Supervisor).filter( Supervisor.id == supervisor_id, Supervisor.is_active == True ).first() if not supervisor: return custom_response( success=False, data="Supervisor not found", status_code=404 ) supervisor_departments = [dept.department_name for dept in supervisor.departments if dept.is_active] tutor_departments = [dept.department_name for dept in tutor.departments if dept.is_active] if not any(dept in supervisor_departments for dept in tutor_departments): return custom_response( success=False, data="Tutor is not in supervisor's departments", status_code=403 ) # Get all courses assigned to this tutor tutor_courses = ctx.session.query(Course).join( tutor_course_association, Course.id == tutor_course_association.c.course_id ).filter( tutor_course_association.c.tutor_id == tutor.id, Course.is_active == True ).all() # Calculate comprehensive workload data total_credit_hours = 0 total_teaching_hours = 0 courses_data = [] weekly_breakdown = {} monthly_breakdown = {} for course in tutor_courses: # Course basic info course_credits = course.credits or 0 total_credit_hours += course_credits # Get teaching sessions for this course teaching_sessions = ctx.session.query(TeachingSession).filter( TeachingSession.course_id == course.id, TeachingSession.tutor_id == tutor.id, TeachingSession.status.in_(['scheduled', 'completed']) ).all() # Calculate actual teaching hours course_teaching_hours = 0 sessions_data = [] for session in teaching_sessions: session_duration = 0 if session.start_time and session.end_time: # Convert time objects to datetime objects for calculation start_datetime = datetime.combine(datetime.today(), session.start_time) end_datetime = datetime.combine(datetime.today(), session.end_time) # Handle case where end_time is next day (e.g., 23:00 to 01:00) if end_datetime < start_datetime: end_datetime += timedelta(days=1) duration = end_datetime - start_datetime session_duration = duration.total_seconds() / 3600 # Convert to hours course_teaching_hours += session_duration # Weekly breakdown (using created_at for date reference) if session.created_at: week_key = session.created_at.strftime('%Y-W%U') if week_key not in weekly_breakdown: week_start = session.created_at - timedelta(days=session.created_at.weekday()) weekly_breakdown[week_key] = { 'week_start': week_start.isoformat(), 'total_hours': 0, 'sessions_count': 0, 'courses': set() } weekly_breakdown[week_key]['total_hours'] += session_duration weekly_breakdown[week_key]['sessions_count'] += 1 weekly_breakdown[week_key]['courses'].add(course.code) # Monthly breakdown (using created_at for date reference) if session.created_at: month_key = session.created_at.strftime('%Y-%m') if month_key not in monthly_breakdown: monthly_breakdown[month_key] = { 'month_name': session.created_at.strftime('%B %Y'), 'total_hours': 0, 'sessions_count': 0, 'courses': set() } monthly_breakdown[month_key]['total_hours'] += session_duration monthly_breakdown[month_key]['sessions_count'] += 1 monthly_breakdown[month_key]['courses'].add(course.code) sessions_data.append({ 'session_id': session.id, 'title': session.title, 'start_time': session.start_time.isoformat() if session.start_time else None, 'end_time': session.end_time.isoformat() if session.end_time else None, 'duration_hours': round(session_duration, 2), 'status': session.status, 'room': session.room, 'attendance_count': len(session.attendance_records) if hasattr(session, 'attendance_records') else 0, 'verification_status': 'verified' if session.is_verified else 'pending', 'verification_method': session.verification_method }) total_teaching_hours += course_teaching_hours # Get student enrollment count using enrollment_courses association table student_count = ctx.session.query(enrollment_courses).filter( enrollment_courses.c.course_id == course.id, enrollment_courses.c.status == 'active' ).count() # Calculate efficiency metrics credit_vs_teaching_ratio = course_teaching_hours / course_credits if course_credits > 0 else 0 student_to_hour_ratio = student_count / course_teaching_hours if course_teaching_hours > 0 else 0 courses_data.append({ 'course_id': course.id, 'course_code': course.code, 'course_name': course.title, 'department': course.get_primary_department(), 'all_departments': course.get_all_departments(), 'credits': course_credits, 'teaching_hours': round(course_teaching_hours, 2), 'sessions_count': len(teaching_sessions), 'student_count': student_count, 'credit_vs_teaching_ratio': round(credit_vs_teaching_ratio, 2), 'student_to_hour_ratio': round(student_to_hour_ratio, 2), 'academic_session': { 'id': course.academic_session.id, 'name': course.academic_session.name, 'start_date': str(course.academic_session.start_date) if course.academic_session.start_date else None, 'end_date': str(course.academic_session.end_date) if course.academic_session.end_date else None } if course.academic_session else None, 'sessions': sessions_data, 'is_shared_course': course.is_shared_course, 'shared_course_type': course.shared_course_type }) # Convert sets to lists for JSON serialization for week_data in weekly_breakdown.values(): week_data['courses'] = list(week_data['courses']) for month_data in monthly_breakdown.values(): month_data['courses'] = list(month_data['courses']) # Calculate overall metrics max_hours = tutor.max_teaching_hours or 28 utilization_percentage = (total_teaching_hours / max_hours) * 100 if max_hours > 0 else 0 credit_efficiency = total_teaching_hours / total_credit_hours if total_credit_hours > 0 else 0 # Determine workload status if total_teaching_hours < 24: status = 'underutilized' elif total_teaching_hours <= 28: status = 'optimal' else: status = 'overloaded' # Get tutor's availability data availabilities = ctx.session.query(TutorAvailability).filter( TutorAvailability.tutor_id == tutor.id, TutorAvailability.is_approved == True, TutorAvailability.is_cancelled == False ).all() availability_data = [] for avail in availabilities: availability_data.append({ 'id': avail.id, 'day_of_week': avail.day_of_week, 'start_time': avail.start_time.isoformat() if avail.start_time else None, 'end_time': avail.end_time.isoformat() if avail.end_time else None, 'location': avail.location, 'availability_type': avail.availability_type, 'notes': avail.notes }) # Prepare comprehensive response detailed_workload = { 'tutor_info': { 'tutor_id': tutor.id, 'name': f"{tutor.first_name} {tutor.last_name}", 'email': tutor.email, 'staff_id': tutor.staff_id, 'primary_department': tutor.get_primary_department(), 'all_departments': tutor.get_all_departments(), 'qualification': tutor.qualification, 'specialization': tutor.specialization, 'years_of_teaching': tutor.years_of_teaching, 'office_location': tutor.office_location, 'hourly_rate': tutor.hourly_rate, 'is_full_time': tutor.is_full_time, 'is_on_leave': tutor.is_on_leave, 'max_teaching_hours': max_hours }, 'workload_summary': { 'total_courses': len(courses_data), 'total_credit_hours': total_credit_hours, 'total_teaching_hours': round(total_teaching_hours, 2), 'max_hours': max_hours, 'utilization_percentage': round(utilization_percentage, 1), 'credit_efficiency': round(credit_efficiency, 2), 'status': status, 'average_hours_per_course': round(total_teaching_hours / len(courses_data), 2) if courses_data else 0, 'total_students': sum(course['student_count'] for course in courses_data), 'total_sessions': sum(course['sessions_count'] for course in courses_data) }, 'courses_breakdown': courses_data, 'weekly_breakdown': weekly_breakdown, 'monthly_breakdown': monthly_breakdown, 'availability_data': availability_data, 'performance_metrics': { 'credit_hours_assigned': total_credit_hours, 'actual_teaching_hours': round(total_teaching_hours, 2), 'hours_variance': round(total_teaching_hours - total_credit_hours, 2), 'efficiency_score': round(credit_efficiency * 100, 1), 'workload_distribution': { 'underutilized_courses': len([c for c in courses_data if c['credit_vs_teaching_ratio'] < 0.8]), 'optimal_courses': len([c for c in courses_data if 0.8 <= c['credit_vs_teaching_ratio'] <= 1.2]), 'overloaded_courses': len([c for c in courses_data if c['credit_vs_teaching_ratio'] > 1.2]) } }, 'generated_at': datetime.utcnow().isoformat() } return custom_response( success=True, data=detailed_workload, status_code=200 ) except Exception as e: current_app.logger.error(f"Error getting detailed tutor workload: {str(e)}", exc_info=True) return custom_response( success=False, data=f"Failed to get detailed tutor workload: {str(e)}", status_code=500 ) def bulk_update_department_assignment(self, old_department: str, new_department: str, assigned_by: str = None) -> Dict: """ Bulk update tutors from one department to another Args: old_department: The current department name new_department: The new department name assigned_by: ID of the supervisor making the change (optional) Returns: Response with update results """ with DatabaseContextManager() as ctx: try: # Find all active department assignments for the old department old_assignments = ctx.session.query(TutorDepartment).filter( TutorDepartment.department_name == old_department, TutorDepartment.is_active == True ).all() if not old_assignments: return custom_response( success=False, data=f"No active tutors found in department: {old_department}", status_code=404 ) updated_count = 0 errors = [] updated_tutors = [] for assignment in old_assignments: try: # Check if tutor already has the new department assignment existing_new_assignment = ctx.session.query(TutorDepartment).filter( TutorDepartment.tutor_id == assignment.tutor_id, TutorDepartment.department_name == new_department, TutorDepartment.is_active == True ).first() if existing_new_assignment: # If new department already exists, just deactivate the old one assignment.is_active = False assignment.updated_at = datetime.utcnow() assignment.notes = f"Replaced by {new_department} assignment" # If the old assignment was primary, make the new one primary if assignment.is_primary: existing_new_assignment.is_primary = True existing_new_assignment.updated_at = datetime.utcnow() else: # Create new department assignment new_assignment = TutorDepartment( id=str(uuid.uuid4()), tutor_id=assignment.tutor_id, department_name=new_department, is_primary=assignment.is_primary, # Preserve primary status assigned_date=date.today(), assigned_by=assigned_by, notes=f"Migrated from {old_department}", is_active=True, created_at=datetime.utcnow(), updated_at=datetime.utcnow() ) # Deactivate old assignment assignment.is_active = False assignment.updated_at = datetime.utcnow() assignment.notes = f"Migrated to {new_department}" ctx.session.add(new_assignment) # Get tutor info for response tutor = ctx.session.query(Tutor).filter(Tutor.id == assignment.tutor_id).first() if tutor: updated_tutors.append({ 'tutor_id': tutor.id, 'name': f"{tutor.first_name} {tutor.last_name}", 'email': tutor.email, 'staff_id': tutor.staff_id, 'was_primary': assignment.is_primary }) updated_count += 1 except Exception as e: errors.append({ 'tutor_id': assignment.tutor_id, 'error': str(e) }) current_app.logger.error(f"Error updating tutor {assignment.tutor_id}: {str(e)}") # Commit all changes ctx.session.commit() return custom_response( success=True, data={ 'message': f'Successfully migrated {updated_count} tutors from {old_department} to {new_department}', 'old_department': old_department, 'new_department': new_department, 'total_updated': updated_count, 'errors': errors, 'updated_tutors': updated_tutors, 'assigned_by': assigned_by }, status_code=200 ) except Exception as e: ctx.session.rollback() current_app.logger.error(f"Error in bulk department update: {str(e)}", exc_info=True) return custom_response( success=False, data=f"Failed to update departments: {str(e)}", status_code=500 ) def migrate_business_admin_to_computing(self, assigned_by: str = None) -> Dict: """ Specific function to migrate tutors from Business Administration to Computing and Informatics Args: assigned_by: ID of the supervisor making the change (optional) Returns: Response with migration results """ return self.bulk_update_department_assignment( old_department="Business Administration", new_department="Computing and Informatics", assigned_by=assigned_by ) def get_tutor_detailed_analytics(self, tutor_id: str) -> Dict: """ Get comprehensive analytics data for the tutor detailed view Args: tutor_id: ID of the tutor Returns: Response with comprehensive tutor analytics data """ with DatabaseContextManager() as ctx: # Verify tutor exists tutor = ctx.session.query(Tutor).options( joinedload(Tutor.supervisor), joinedload(Tutor.departments) ).filter(Tutor.id == tutor_id).first() if not tutor: return custom_response( success=False, data="Tutor not found", status_code=404 ) # Get base tutor information tutor_data = self._get_tutor_base_info(ctx, tutor) # Get analytics data analytics_data = self._get_tutor_analytics(ctx, tutor_id) # Combine all data response_data = { **tutor_data, 'analytics': analytics_data } return custom_response( success=True, data=response_data, status_code=200 ) def _get_tutor_base_info(self, ctx, tutor) -> Dict: """Get base tutor information""" # Safely handle departments departments_data = [] primary_department = 'Not assigned' if hasattr(tutor, 'departments') and tutor.departments: departments_data = [ { 'id': dept.id, 'department_name': dept.department_name, 'is_primary': dept.is_primary, 'assigned_date': str(dept.assigned_date) if dept.assigned_date else None, 'assigned_by': dept.assigned_by, 'notes': dept.notes, 'is_active': dept.is_active, 'created_at': str(dept.created_at) if dept.created_at else None, 'tutor_id': dept.tutor_id } for dept in tutor.departments if dept.is_active ] # Get primary department primary_dept = next((dept for dept in tutor.departments if dept.is_primary and dept.is_active), None) if primary_dept: primary_department = primary_dept.department_name return { 'id': tutor.id, 'first_name': tutor.first_name, 'last_name': tutor.last_name, 'email': tutor.email, 'phone': tutor.phone, 'office_location': tutor.office_location, 'qualification': tutor.qualification, 'bio': tutor.bio, 'hourly_rate': tutor.hourly_rate, 'is_full_time': tutor.is_full_time, 'specialization': tutor.specialization, 'years_of_teaching': tutor.years_of_teaching, 'is_on_leave': tutor.is_on_leave, 'leave_start_date': str(tutor.leave_start_date) if tutor.leave_start_date else None, 'leave_end_date': str(tutor.leave_end_date) if tutor.leave_end_date else None, 'leave_reason': tutor.leave_reason, 'profile_picture': tutor.profile_picture, 'staff_id': tutor.staff_id, 'supervisor_id': tutor.supervisor_id, 'supervisor': { 'id': tutor.supervisor.id if tutor.supervisor else None, 'first_name': tutor.supervisor.first_name if tutor.supervisor else None, 'last_name': tutor.supervisor.last_name if tutor.supervisor else None, 'email': tutor.supervisor.email if tutor.supervisor else None, 'phone': tutor.supervisor.phone if tutor.supervisor else None, 'office_location': tutor.supervisor.office_location if tutor.supervisor else None, } if tutor.supervisor else None, 'departments': departments_data, 'courses': self._get_tutor_courses_detailed(ctx, tutor.id), 'verification_methods': tutor.verification_methods, 'verification_success_rate': tutor.verification_success_rate, 'last_verification_date': str(tutor.last_verification_date) if tutor.last_verification_date else None, 'max_teaching_hours': tutor.max_teaching_hours, 'current_hours': self._calculate_current_hours(ctx, tutor.id), 'department': primary_department, 'supervisor_name': f"{tutor.supervisor.first_name} {tutor.supervisor.last_name}" if tutor.supervisor else None } def _get_tutor_courses_detailed(self, ctx, tutor_id: str) -> list: """Get detailed course information for tutor""" courses = ctx.session.query(Course).options( joinedload(Course.academic_session) ).join( tutor_course_association, Course.id == tutor_course_association.c.course_id ).filter( tutor_course_association.c.tutor_id == tutor_id, Course.is_active == True ).all() courses_data = [] for course in courses: # Get student count using enrollment_courses association table student_count = ctx.session.query(enrollment_courses).filter( enrollment_courses.c.course_id == course.id, enrollment_courses.c.status == 'active' ).count() courses_data.append({ 'id': course.id, 'title': course.title, 'code': course.code, 'credits': course.credits, 'semester': course.semester, 'academic_year': course.academic_session.year if course.academic_session else 'N/A', 'student_count': student_count, 'status': 'active' if course.is_active else 'inactive', 'description': course.description, 'objectives': course.learning_outcomes.split(',') if course.learning_outcomes else [], 'assessment_methods': course.assessment_method.split(',') if course.assessment_method else [], 'resources': course.required_materials.split(',') if course.required_materials else [] }) return courses_data def _get_tutor_analytics(self, ctx, tutor_id: str) -> Dict: """Get comprehensive analytics data for tutor""" return { 'attendance_history': self._get_attendance_history(ctx, tutor_id), 'workload_analysis': self._get_workload_analysis(ctx, tutor_id), 'performance_metrics': self._get_performance_metrics(ctx, tutor_id), 'teaching_hours': self._get_teaching_hours_analytics(ctx, tutor_id), 'course_performance': self._get_course_performance_analytics(ctx, tutor_id) } def _get_attendance_history(self, ctx, tutor_id: str) -> list: """Get attendance history for the last 30 days""" thirty_days_ago = datetime.now() - timedelta(days=30) # Get teaching sessions for the tutor sessions = ctx.session.query(TeachingSession).filter( TeachingSession.tutor_id == tutor_id, TeachingSession.start_time >= thirty_days_ago ).order_by(TeachingSession.start_time.desc()).limit(50).all() attendance_records = [] for session in sessions: # Get attendance record for this session attendance = ctx.session.query(Attendance).filter( Attendance.session_id == session.id, Attendance.tutor_id == tutor_id ).first() if attendance: attendance_records.append({ 'date': session.start_time.strftime('%Y-%m-%d'), 'status': attendance.status.value if attendance.status else 'present', 'session_type': session.session_type or 'Lecture', 'duration': self._calculate_session_duration(session.start_time, session.end_time), 'verification_method': attendance.verification_method or 'Manual' }) return attendance_records def _get_workload_analysis(self, ctx, tutor_id: str) -> Dict: """Get workload analysis for tutor""" # Get current course load courses = ctx.session.query(Course).join( tutor_course_association, Course.id == tutor_course_association.c.course_id ).filter( tutor_course_association.c.tutor_id == tutor_id, Course.is_active == True ).all() current_load = sum(course.credits for course in courses) optimal_min, optimal_max = 24, 28 utilization_percentage = (current_load / optimal_max) * 100 if optimal_max > 0 else 0 # Determine trend (simplified - in real implementation, compare with previous periods) trend = 'stable' if utilization_percentage < 70: trend = 'decreasing' elif utilization_percentage > 90: trend = 'increasing' return { 'current_load': current_load, 'optimal_range': {'min': optimal_min, 'max': optimal_max}, 'utilization_percentage': round(utilization_percentage, 1), 'trend': trend } def _get_performance_metrics(self, ctx, tutor_id: str) -> Dict: """Get performance metrics for tutor""" # Get teaching sessions for performance calculation sessions = ctx.session.query(TeachingSession).filter( TeachingSession.tutor_id == tutor_id ).all() if not sessions: return { 'average_rating': 0, 'completion_rate': 0, 'student_satisfaction': 0, 'teaching_effectiveness': 0 } # Calculate completion rate based on attended sessions attended_sessions = ctx.session.query(Attendance).join( TeachingSession, Attendance.session_id == TeachingSession.id ).filter( TeachingSession.tutor_id == tutor_id, Attendance.status.in_(['present', 'late']) ).count() completion_rate = (attended_sessions / len(sessions) * 100) if sessions else 0 # Get average rating from course feedback (simplified) # In a real implementation, this would come from student feedback average_rating = 4.2 # Placeholder value return { 'average_rating': round(average_rating, 1), 'completion_rate': round(completion_rate, 1), 'student_satisfaction': round(average_rating * 20, 1), # Convert 5-point scale to percentage 'teaching_effectiveness': round(completion_rate * 0.9, 1) # Simplified calculation } def _get_teaching_hours_analytics(self, ctx, tutor_id: str) -> Dict: """Get teaching hours analytics""" # Get current semester start date (simplified) current_year = datetime.now().year current_month = datetime.now().month semester_start = datetime(current_year, 1 if current_month <= 6 else 7, 1) year_start = datetime(current_year, 1, 1) # Calculate hours for current semester semester_sessions = ctx.session.query(TeachingSession).filter( TeachingSession.tutor_id == tutor_id, TeachingSession.start_time >= semester_start ).all() total_semester_hours = sum( self._calculate_session_duration(session.start_time, session.end_time) / 60 for session in semester_sessions ) # Calculate hours for current year year_sessions = ctx.session.query(TeachingSession).filter( TeachingSession.tutor_id == tutor_id, TeachingSession.start_time >= year_start ).all() total_year_hours = sum( self._calculate_session_duration(session.start_time, session.end_time) / 60 for session in year_sessions ) # Generate daily data for the last 7 days daily_data = [] for i in range(7): date = datetime.now() - timedelta(days=i) day_sessions = ctx.session.query(TeachingSession).filter( TeachingSession.tutor_id == tutor_id, func.date(TeachingSession.start_time) == date.date() ).all() day_hours = sum( self._calculate_session_duration(session.start_time, session.end_time) / 60 for session in day_sessions ) daily_data.append({ 'date': date.strftime('%Y-%m-%d'), 'hours': round(day_hours, 1) }) return { 'daily': daily_data, 'weekly': self._generate_weekly_data(ctx, tutor_id), 'monthly': self._generate_monthly_data(ctx, tutor_id), 'total_this_semester': round(total_semester_hours, 1), 'total_this_year': round(total_year_hours, 1) } def _generate_weekly_data(self, ctx, tutor_id: str) -> list: """Generate weekly teaching hours data""" weekly_data = [] for i in range(12): # Last 12 weeks week_start = datetime.now() - timedelta(weeks=i+1) week_end = week_start + timedelta(days=7) week_sessions = ctx.session.query(TeachingSession).filter( TeachingSession.tutor_id == tutor_id, TeachingSession.start_time >= week_start, TeachingSession.start_time < week_end ).all() week_hours = sum( self._calculate_session_duration(session.start_time, session.end_time) / 60 for session in week_sessions ) weekly_data.append({ 'week': f"Week {12-i}", 'hours': round(week_hours, 1) }) return list(reversed(weekly_data)) def _generate_monthly_data(self, ctx, tutor_id: str) -> list: """Generate monthly teaching hours data""" monthly_data = [] for i in range(6): # Last 6 months month_date = datetime.now() - timedelta(days=30*i) month_start = month_date.replace(day=1) if month_date.month == 12: month_end = month_start.replace(year=month_date.year + 1, month=1) else: month_end = month_start.replace(month=month_date.month + 1) month_sessions = ctx.session.query(TeachingSession).filter( TeachingSession.tutor_id == tutor_id, TeachingSession.start_time >= month_start, TeachingSession.start_time < month_end ).all() month_hours = sum( self._calculate_session_duration(session.start_time, session.end_time) / 60 for session in month_sessions ) monthly_data.append({ 'month': month_start.strftime('%B %Y'), 'hours': round(month_hours, 1) }) return list(reversed(monthly_data)) def _get_course_performance_analytics(self, ctx, tutor_id: str) -> list: """Get course performance analytics""" courses = ctx.session.query(Course).join( tutor_course_association, Course.id == tutor_course_association.c.course_id ).filter( tutor_course_association.c.tutor_id == tutor_id, Course.is_active == True ).all() course_performance = [] for course in courses: # Get student count using enrollment_courses association table student_count = ctx.session.query(enrollment_courses).filter( enrollment_courses.c.course_id == course.id, enrollment_courses.c.status == 'active' ).count() # Get sessions for this course sessions = ctx.session.query(TeachingSession).filter( TeachingSession.course_id == course.id, TeachingSession.tutor_id == tutor_id ).all() # Calculate completion rate attended_sessions = ctx.session.query(Attendance).join( TeachingSession, Attendance.session_id == TeachingSession.id ).filter( TeachingSession.course_id == course.id, TeachingSession.tutor_id == tutor_id, Attendance.status.in_(['present', 'late']) ).count() completion_rate = (attended_sessions / len(sessions) * 100) if sessions else 0 # Get average attendance rate attendance_rate = 85.0 # Placeholder - in real implementation, calculate from student attendance course_performance.append({ 'course_id': course.id, 'course_title': course.title, 'student_count': student_count, 'completion_rate': round(completion_rate, 1), 'average_rating': 4.2, # Placeholder - get from feedback system 'attendance_rate': attendance_rate }) return course_performance def debug_departments_structure(self) -> Dict: """Debug method to check department data structure""" try: with DatabaseContextManager() as ctx: # Get first few tutors with their departments tutors = ctx.session.query(Tutor).options( joinedload(Tutor.departments) ).filter(Tutor.is_active == True).limit(3).all() debug_data = [] for tutor in tutors: tutor_dict = self._tutor_to_dict(tutor) debug_data.append({ 'tutor_id': tutor.id, 'tutor_name': f"{tutor.first_name} {tutor.last_name}", 'raw_departments': [ { 'id': dept.id, 'department_name': dept.department_name, 'is_primary': dept.is_primary, 'is_active': dept.is_active } for dept in tutor.departments ] if hasattr(tutor, 'departments') and tutor.departments else [], 'processed_departments': tutor_dict.get('departments', []), 'primary_department': tutor_dict.get('department', 'Not assigned'), 'has_departments_attr': hasattr(tutor, 'departments'), 'departments_is_none': tutor.departments is None if hasattr(tutor, 'departments') else 'No departments attr' }) return custom_response( success=True, data={ 'total_tutors_checked': len(tutors), 'debug_info': debug_data }, status_code=200 ) except Exception as e: return custom_response( success=False, data=f"Debug error: {str(e)}", status_code=500 ) def get_tutors_workload(self): """Get comprehensive workload information for all tutors based on course credits""" try: with DatabaseContextManager() as ctx: # Get all tutors with their courses and departments tutors = ctx.session.query(Tutor).options( joinedload(Tutor.courses), joinedload(Tutor.departments), ).all() workload_data = [] department_workload = {} total_workload = 0 for tutor in tutors: # Calculate total credits for this tutor tutor_credits = sum(course.credits or 0 for course in tutor.courses if course.is_active) # Get tutor's departments tutor_departments = [dept.department_name for dept in tutor.departments if dept.is_active] # Get course details courses_info = [] for course in tutor.courses: if course.is_active: courses_info.append({ "id": course.id, "code": course.code, "title": course.title, "credits": course.credits or 0, "department": course.department, "max_students": course.max_students, "total_hours": course.total_hours, "semester": course.semester }) # Calculate workload metrics workload_level = self._calculate_workload_level(tutor_credits) tutor_data = { "tutor_id": tutor.id, "staff_id": tutor.staff_id, "name": f"{tutor.first_name} {tutor.last_name}" if tutor.first_name and tutor.last_name else "Unknown", "email": tutor.email if tutor.email else None, "departments": tutor_departments, "total_credits": tutor_credits, "workload_level": workload_level, "courses_count": len([c for c in tutor.courses if c.is_active]), "courses": courses_info, "max_teaching_hours": tutor.max_teaching_hours, "is_full_time": tutor.is_full_time, "is_on_leave": tutor.is_on_leave, "leave_reason": tutor.leave_reason if tutor.is_on_leave else None, "supervisor": { "id": tutor.supervisor_id, "name": self._get_user_name(ctx, tutor.supervisor_id) } if tutor.supervisor_id else None, "workload_percentage": self._calculate_workload_percentage(tutor_credits, tutor.max_teaching_hours), "workload_status": self._get_workload_status(tutor_credits, tutor.max_teaching_hours) } workload_data.append(tutor_data) total_workload += tutor_credits # Aggregate by department for dept in tutor_departments: if dept not in department_workload: department_workload[dept] = { "total_credits": 0, "tutors_count": 0, "tutors": [] } department_workload[dept]["total_credits"] += tutor_credits department_workload[dept]["tutors_count"] += 1 department_workload[dept]["tutors"].append({ "tutor_id": tutor.id, "name": tutor_data["name"], "credits": tutor_credits, "workload_level": workload_level }) # Sort tutors by workload (highest first) workload_data.sort(key=lambda x: x["total_credits"], reverse=True) # Sort departments by workload department_summary = [] for dept, data in department_workload.items(): department_summary.append({ "department": dept, "total_credits": data["total_credits"], "tutors_count": data["tutors_count"], "average_credits_per_tutor": round(data["total_credits"] / data["tutors_count"], 2) if data["tutors_count"] > 0 else 0, "workload_level": self._calculate_department_workload_level(data["total_credits"], data["tutors_count"]), "tutors": sorted(data["tutors"], key=lambda x: x["credits"], reverse=True) }) department_summary.sort(key=lambda x: x["total_credits"], reverse=True) # Calculate overall statistics active_tutors = [t for t in workload_data if not t["is_on_leave"]] on_leave_tutors = [t for t in workload_data if t["is_on_leave"]] overall_stats = { "total_tutors": len(workload_data), "active_tutors": len(active_tutors), "on_leave_tutors": len(on_leave_tutors), "total_workload_credits": total_workload, "average_workload_per_tutor": round(total_workload / len(active_tutors), 2) if active_tutors else 0, "departments_count": len(department_summary), "workload_distribution": { "low": len([t for t in active_tutors if t["workload_level"] == "low"]), "medium": len([t for t in active_tutors if t["workload_level"] == "medium"]), "high": len([t for t in active_tutors if t["workload_level"] == "high"]), "very_high": len([t for t in active_tutors if t["workload_level"] == "very_high"]) } } return custom_response( success=True, data={ "overall_statistics": overall_stats, "tutors_workload": workload_data, "department_workload": department_summary, "top_loaded_tutors": workload_data[:10], # Top 10 most loaded tutors "least_loaded_tutors": workload_data[-10:], # Bottom 10 least loaded tutors "workload_alerts": self._generate_workload_alerts(workload_data) }, status_code=200 ) except Exception as e: return custom_response( success=False, data=f"Error fetching workload data: {str(e)}", status_code=500 ) def update_tutor_workload(self, payload): """Update tutor workload assignments""" try: tutor_id = payload.get('tutor_id') course_ids = payload.get('course_ids', []) action = payload.get('action', 'assign') # assign or remove if not tutor_id: return custom_response( success=False, data="Tutor ID is required", status_code=400 ) with DatabaseContextManager() as ctx: tutor = ctx.session.query(Tutor).filter(Tutor.id == tutor_id).first() if not tutor: return custom_response( success=False, data="Tutor not found", status_code=404 ) if action == 'assign': # Assign courses to tutor for course_id in course_ids: course = ctx.session.query(Course).filter(Course.id == course_id).first() if course and course not in tutor.courses: tutor.courses.append(course) elif action == 'remove': # Remove courses from tutor for course_id in course_ids: course = ctx.session.query(Course).filter(Course.id == course_id).first() if course and course in tutor.courses: tutor.courses.remove(course) ctx.session.commit() # Get updated workload updated_credits = sum(course.credits or 0 for course in tutor.courses if course.is_active) return custom_response( success=True, data={ "message": f"Courses {action}ed successfully", "tutor_id": tutor_id, "updated_workload": updated_credits, "workload_level": self._calculate_workload_level(updated_credits) }, status_code=200 ) except Exception as e: return custom_response( success=False, data=f"Error updating workload: {str(e)}", status_code=500 ) def _calculate_workload_level(self, credits): """Calculate workload level based on credits""" if credits <= 20: return "low" elif credits <= 40: return "medium" elif credits <= 60: return "high" else: return "very_high" def _calculate_department_workload_level(self, total_credits, tutors_count): """Calculate department workload level""" if tutors_count == 0: return "no_data" avg_credits = total_credits / tutors_count if avg_credits <= 20: return "low" elif avg_credits <= 40: return "medium" elif avg_credits <= 60: return "high" else: return "very_high" def _calculate_workload_percentage(self, credits, max_hours): """Calculate workload percentage based on max teaching hours""" if not max_hours or max_hours == 0: return 0 # Assuming 1 credit = 1 hour per week return min(round((credits / max_hours) * 100, 2), 100) def _get_workload_status(self, credits, max_hours): """Get workload status""" percentage = self._calculate_workload_percentage(credits, max_hours) if percentage >= 90: return "overloaded" elif percentage >= 75: return "high" elif percentage >= 50: return "moderate" else: return "light" def _generate_workload_alerts(self, workload_data): """Generate workload alerts for management""" alerts = [] for tutor in workload_data: if tutor["is_on_leave"]: continue if tutor["workload_status"] == "overloaded": alerts.append({ "type": "overload", "severity": "high", "tutor_id": tutor["tutor_id"], "tutor_name": tutor["name"], "message": f"{tutor['name']} is overloaded with {tutor['total_credits']} credits ({tutor['workload_percentage']}% of capacity)", "credits": tutor["total_credits"], "percentage": tutor["workload_percentage"] }) elif tutor["workload_status"] == "high": alerts.append({ "type": "high_workload", "severity": "medium", "tutor_id": tutor["tutor_id"], "tutor_name": tutor["name"], "message": f"{tutor['name']} has high workload with {tutor['total_credits']} credits ({tutor['workload_percentage']}% of capacity)", "credits": tutor["total_credits"], "percentage": tutor["workload_percentage"] }) elif tutor["workload_status"] == "light" and tutor["total_credits"] > 0: alerts.append({ "type": "underutilized", "severity": "low", "tutor_id": tutor["tutor_id"], "tutor_name": tutor["name"], "message": f"{tutor['name']} has light workload with only {tutor['total_credits']} credits ({tutor['workload_percentage']}% of capacity)", "credits": tutor["total_credits"], "percentage": tutor["workload_percentage"] }) return alerts def get_workload_by_department(self, department=None): """Get workload information grouped by department""" try: with DatabaseContextManager() as ctx: # Get all tutors with their courses and departments tutors = ctx.session.query(Tutor).options( joinedload(Tutor.courses), joinedload(Tutor.departments), ).all() department_workload = {} for tutor in tutors: # Calculate total credits for this tutor tutor_credits = sum(course.credits or 0 for course in tutor.courses if course.is_active) # Get tutor's departments tutor_departments = [dept.department_name for dept in tutor.departments if dept.is_active] # If specific department requested, filter if department and department not in tutor_departments: continue # Get course details courses_info = [] for course in tutor.courses: if course.is_active: courses_info.append({ "id": course.id, "code": course.code, "title": course.title, "credits": course.credits or 0, "department": course.department, "max_students": course.max_students, "total_hours": course.total_hours, "semester": course.semester }) # Aggregate by department for dept in tutor_departments: if department and dept != department: continue if dept not in department_workload: department_workload[dept] = { "department": dept, "total_credits": 0, "tutors_count": 0, "tutors": [], "courses": [], "workload_level": "low" } department_workload[dept]["total_credits"] += tutor_credits department_workload[dept]["tutors_count"] += 1 department_workload[dept]["tutors"].append({ "tutor_id": tutor.id, "staff_id": tutor.staff_id, "name": f"{tutor.first_name} {tutor.last_name}" if tutor.first_name and tutor.last_name else "Unknown", "email": tutor.email if tutor.email else None, "credits": tutor_credits, "workload_level": self._calculate_workload_level(tutor_credits), "workload_percentage": self._calculate_workload_percentage(tutor_credits, tutor.max_teaching_hours), "workload_status": self._get_workload_status(tutor_credits, tutor.max_teaching_hours), "courses": courses_info, "is_on_leave": tutor.is_on_leave, "supervisor": { "id": tutor.supervisor_id, "name": self._get_user_name(ctx, tutor.supervisor_id) } if tutor.supervisor_id else None }) # Add courses to department for course_info in courses_info: if course_info not in department_workload[dept]["courses"]: department_workload[dept]["courses"].append(course_info) # Calculate department-level metrics for dept_data in department_workload.values(): dept_data["average_credits_per_tutor"] = round( dept_data["total_credits"] / dept_data["tutors_count"], 2 ) if dept_data["tutors_count"] > 0 else 0 dept_data["workload_level"] = self._calculate_department_workload_level( dept_data["total_credits"], dept_data["tutors_count"] ) # Sort tutors by workload dept_data["tutors"].sort(key=lambda x: x["credits"], reverse=True) # Calculate workload distribution active_tutors = [t for t in dept_data["tutors"] if not t["is_on_leave"]] dept_data["workload_distribution"] = { "low": len([t for t in active_tutors if t["workload_level"] == "low"]), "medium": len([t for t in active_tutors if t["workload_level"] == "medium"]), "high": len([t for t in active_tutors if t["workload_level"] == "high"]), "very_high": len([t for t in active_tutors if t["workload_level"] == "very_high"]) } # Convert to list and sort by total credits result = list(department_workload.values()) result.sort(key=lambda x: x["total_credits"], reverse=True) return custom_response( success=True, data={ "departments": result, "total_departments": len(result), "filtered_by": department if department else "all" }, status_code=200 ) except Exception as e: return custom_response( success=False, data=f"Error fetching department workload: {str(e)}", status_code=500 ) def get_workload_summary(self): """Get comprehensive workload summary and analytics""" try: with DatabaseContextManager() as ctx: # Get all tutors with their courses and departments tutors = ctx.session.query(Tutor).options( joinedload(Tutor.courses), joinedload(Tutor.departments), ).all() # Calculate overall statistics total_tutors = len(tutors) active_tutors = [t for t in tutors if not t.is_on_leave] on_leave_tutors = [t for t in tutors if t.is_on_leave] total_credits = 0 workload_distribution = {"low": 0, "medium": 0, "high": 0, "very_high": 0} department_stats = {} for tutor in active_tutors: tutor_credits = sum(course.credits or 0 for course in tutor.courses if course.is_active) total_credits += tutor_credits workload_level = self._calculate_workload_level(tutor_credits) workload_distribution[workload_level] += 1 # Department aggregation tutor_departments = [dept.department_name for dept in tutor.departments if dept.is_active] for dept in tutor_departments: if dept not in department_stats: department_stats[dept] = { "total_credits": 0, "tutors_count": 0, "workload_levels": {"low": 0, "medium": 0, "high": 0, "very_high": 0} } department_stats[dept]["total_credits"] += tutor_credits department_stats[dept]["tutors_count"] += 1 department_stats[dept]["workload_levels"][workload_level] += 1 # Calculate averages and percentages average_workload = round(total_credits / len(active_tutors), 2) if active_tutors else 0 # Workload capacity analysis capacity_analysis = { "overloaded": len([t for t in active_tutors if self._get_workload_status( sum(course.credits or 0 for course in t.courses if course.is_active), t.max_teaching_hours ) == "overloaded"]), "high": len([t for t in active_tutors if self._get_workload_status( sum(course.credits or 0 for course in t.courses if course.is_active), t.max_teaching_hours ) == "high"]), "moderate": len([t for t in active_tutors if self._get_workload_status( sum(course.credits or 0 for course in t.courses if course.is_active), t.max_teaching_hours ) == "moderate"]), "light": len([t for t in active_tutors if self._get_workload_status( sum(course.credits or 0 for course in t.courses if course.is_active), t.max_teaching_hours ) == "light"]) } # Department summary department_summary = [] for dept, stats in department_stats.items(): avg_credits = round(stats["total_credits"] / stats["tutors_count"], 2) if stats["tutors_count"] > 0 else 0 department_summary.append({ "department": dept, "total_credits": stats["total_credits"], "tutors_count": stats["tutors_count"], "average_credits": avg_credits, "workload_level": self._calculate_department_workload_level(stats["total_credits"], stats["tutors_count"]), "workload_distribution": stats["workload_levels"] }) department_summary.sort(key=lambda x: x["total_credits"], reverse=True) # Recommendations recommendations = self._generate_workload_recommendations(workload_distribution, capacity_analysis, department_summary) return custom_response( success=True, data={ "overview": { "total_tutors": total_tutors, "active_tutors": len(active_tutors), "on_leave_tutors": len(on_leave_tutors), "total_workload_credits": total_credits, "average_workload_per_tutor": average_workload, "departments_count": len(department_stats) }, "workload_distribution": workload_distribution, "capacity_analysis": capacity_analysis, "department_summary": department_summary, "top_loaded_departments": department_summary[:5], "least_loaded_departments": department_summary[-5:], "recommendations": recommendations, "generated_at": datetime.utcnow().isoformat() }, status_code=200 ) except Exception as e: return custom_response( success=False, data=f"Error generating workload summary: {str(e)}", status_code=500 ) def get_workload_alerts(self): """Get workload alerts and recommendations""" try: with DatabaseContextManager() as ctx: # Get all tutors with their courses and departments tutors = ctx.session.query(Tutor).options( joinedload(Tutor.courses), joinedload(Tutor.departments), ).all() alerts = [] recommendations = [] for tutor in tutors: if tutor.is_on_leave: continue tutor_credits = sum(course.credits or 0 for course in tutor.courses if course.is_active) workload_status = self._get_workload_status(tutor_credits, tutor.max_teaching_hours) workload_percentage = self._calculate_workload_percentage(tutor_credits, tutor.max_teaching_hours) tutor_name = f"{tutor.first_name} {tutor.last_name}" if tutor.first_name and tutor.last_name else "Unknown" if workload_status == "overloaded": alerts.append({ "type": "overload", "severity": "critical", "tutor_id": tutor.id, "tutor_name": tutor_name, "message": f"{tutor_name} is critically overloaded with {tutor_credits} credits ({workload_percentage}% of capacity)", "credits": tutor_credits, "percentage": workload_percentage, "recommendation": "Consider redistributing courses or hiring additional support", "departments": [dept.department_name for dept in tutor.departments if dept.is_active] }) elif workload_status == "high": alerts.append({ "type": "high_workload", "severity": "warning", "tutor_id": tutor.id, "tutor_name": tutor_name, "message": f"{tutor_name} has high workload with {tutor_credits} credits ({workload_percentage}% of capacity)", "credits": tutor_credits, "percentage": workload_percentage, "recommendation": "Monitor closely and consider workload reduction", "departments": [dept.department_name for dept in tutor.departments if dept.is_active] }) elif workload_status == "light" and tutor_credits > 0: alerts.append({ "type": "underutilized", "severity": "info", "tutor_id": tutor.id, "tutor_name": tutor_name, "message": f"{tutor_name} has light workload with only {tutor_credits} credits ({workload_percentage}% of capacity)", "credits": tutor_credits, "percentage": workload_percentage, "recommendation": "Consider assigning additional courses", "departments": [dept.department_name for dept in tutor.departments if dept.is_active] }) # Generate department-level recommendations department_alerts = self._generate_department_alerts(ctx, tutors) alerts.extend(department_alerts) # Sort alerts by severity severity_order = {"critical": 0, "warning": 1, "info": 2} alerts.sort(key=lambda x: severity_order.get(x["severity"], 3)) return custom_response( success=True, data={ "alerts": alerts, "total_alerts": len(alerts), "critical_alerts": len([a for a in alerts if a["severity"] == "critical"]), "warning_alerts": len([a for a in alerts if a["severity"] == "warning"]), "info_alerts": len([a for a in alerts if a["severity"] == "info"]), "generated_at": datetime.utcnow().isoformat() }, status_code=200 ) except Exception as e: return custom_response( success=False, data=f"Error generating workload alerts: {str(e)}", status_code=500 ) def _generate_workload_recommendations(self, workload_distribution, capacity_analysis, department_summary): """Generate workload management recommendations""" recommendations = [] # Overload recommendations if capacity_analysis["overloaded"] > 0: recommendations.append({ "type": "overload_management", "priority": "high", "title": "Address Tutor Overload", "description": f"{capacity_analysis['overloaded']} tutors are overloaded. Consider redistributing courses or hiring additional staff.", "action": "Review course assignments and redistribute workload" }) # Underutilization recommendations if capacity_analysis["light"] > 0: recommendations.append({ "type": "utilization_improvement", "priority": "medium", "title": "Improve Resource Utilization", "description": f"{capacity_analysis['light']} tutors have light workloads. Consider assigning additional courses.", "action": "Review underutilized tutors and assign additional courses" }) # Department balance recommendations if len(department_summary) > 1: highest_dept = department_summary[0] lowest_dept = department_summary[-1] if highest_dept["total_credits"] > lowest_dept["total_credits"] * 2: recommendations.append({ "type": "department_balance", "priority": "medium", "title": "Balance Department Workloads", "description": f"{highest_dept['department']} has significantly more workload than {lowest_dept['department']}. Consider cross-department assignments.", "action": "Review cross-department course assignments" }) return recommendations def _generate_department_alerts(self, ctx, tutors): """Generate department-level alerts""" alerts = [] department_stats = {} # Calculate department statistics for tutor in tutors: if tutor.is_on_leave: continue tutor_credits = sum(course.credits or 0 for course in tutor.courses if course.is_active) tutor_departments = [dept.department_name for dept in tutor.departments if dept.is_active] for dept in tutor_departments: if dept not in department_stats: department_stats[dept] = { "total_credits": 0, "tutors_count": 0, "tutors": [] } department_stats[dept]["total_credits"] += tutor_credits department_stats[dept]["tutors_count"] += 1 department_stats[dept]["tutors"].append({ "name": f"{tutor.first_name} {tutor.last_name}" if tutor.first_name and tutor.last_name else "Unknown", "credits": tutor_credits }) # Generate department alerts for dept, stats in department_stats.items(): avg_credits = stats["total_credits"] / stats["tutors_count"] if stats["tutors_count"] > 0 else 0 if avg_credits > 50: # High average workload alerts.append({ "type": "department_overload", "severity": "warning", "department": dept, "message": f"{dept} department has high average workload of {avg_credits:.1f} credits per tutor", "average_credits": round(avg_credits, 2), "total_credits": stats["total_credits"], "tutors_count": stats["tutors_count"], "recommendation": "Consider hiring additional tutors or redistributing courses" }) elif avg_credits < 15: # Low average workload alerts.append({ "type": "department_underutilized", "severity": "info", "department": dept, "message": f"{dept} department has low average workload of {avg_credits:.1f} credits per tutor", "average_credits": round(avg_credits, 2), "total_credits": stats["total_credits"], "tutors_count": stats["tutors_count"], "recommendation": "Consider assigning additional courses or cross-department teaching" }) return alerts def get_tutors_attendance(self): """Get comprehensive attendance information for all tutors""" try: # Get all tutors with their sessions and attendance tutors = self.db.session.query(Tutor).options( joinedload(Tutor.teaching_sessions) ).all() attendance_data = [] for tutor in tutors: # Calculate attendance statistics total_sessions = len(tutor.teaching_sessions) attended_sessions = 0 missed_sessions = 0 late_sessions = 0 excused_sessions = 0 # Get recent attendance records recent_attendance = [] last_attendance_date = None for session in tutor.teaching_sessions: # Check if there's an attendance record for this session attendance_record = self.db.session.query(Attendance).filter( and_( Attendance.session_id == session.id, Attendance.tutor_id == tutor.id ) ).first() if attendance_record: if attendance_record.status == 'present': attended_sessions += 1 elif attendance_record.status == 'absent': missed_sessions += 1 elif attendance_record.status == 'late': late_sessions += 1 elif attendance_record.status == 'excused': excused_sessions += 1 # Add to recent attendance recent_attendance.append({ 'date': session.start_time.strftime('%Y-%m-%d'), 'class_name': session.course.title if session.course else 'Unknown Course', 'status': attendance_record.status, 'time': session.start_time.strftime('%H:%M') }) if not last_attendance_date or session.start_time > last_attendance_date: last_attendance_date = session.start_time else: # No attendance record - assume absent missed_sessions += 1 # Calculate attendance rate attendance_rate = 0 if total_sessions > 0: attendance_rate = round((attended_sessions / total_sessions) * 100, 2) # Determine current status based on recent attendance current_status = 'present' if missed_sessions > attended_sessions: current_status = 'absent' elif late_sessions > 0: current_status = 'late' # Sort recent attendance by date (most recent first) recent_attendance.sort(key=lambda x: x['date'], reverse=True) attendance_data.append({ 'tutor_id': tutor.id, 'staff_id': tutor.staff_id, 'name': f"{tutor.first_name} {tutor.last_name}", 'email': tutor.email, 'department': tutor.department.name if tutor.department else 'Unknown', 'total_classes': total_sessions, 'attended_classes': attended_sessions, 'missed_classes': missed_sessions, 'late_classes': late_sessions, 'excused_classes': excused_sessions, 'attendance_rate': attendance_rate, 'last_attendance': last_attendance_date.strftime('%Y-%m-%d') if last_attendance_date else 'Never', 'status': current_status, 'recent_attendance': recent_attendance[:5] # Last 5 sessions }) return custom_response( success=True, data=attendance_data ) except Exception as e: current_app.logger.error(f"Error fetching tutor attendance: {str(e)}") return custom_response( success=False, data=[] ) def get_attendance_summary(self): """Get attendance summary and analytics""" try: # Get total tutors total_tutors = self.db.session.query(Tutor).count() # Get total sessions total_sessions = self.db.session.query(TeachingSession).count() # Calculate overall attendance rate attendance_records = self.db.session.query(Attendance).all() total_attendance_records = len(attendance_records) present_records = len([r for r in attendance_records if r.status == 'present']) overall_attendance_rate = 0 if total_attendance_records > 0: overall_attendance_rate = round((present_records / total_attendance_records) * 100, 2) # Count by status present_count = len([r for r in attendance_records if r.status == 'present']) absent_count = len([r for r in attendance_records if r.status == 'absent']) late_count = len([r for r in attendance_records if r.status == 'late']) excused_count = len([r for r in attendance_records if r.status == 'excused']) # Get attendance trends (last 7 days) end_date = datetime.now() start_date = end_date - timedelta(days=7) attendance_trends = [] for i in range(7): current_date = start_date + timedelta(days=i) sessions_on_date = self.db.session.query(TeachingSession).filter( func.date(TeachingSession.start_time) == current_date.date() ).all() total_classes = len(sessions_on_date) attendance_rate = 0 if total_classes > 0: attended_on_date = 0 for session in sessions_on_date: attendance_record = self.db.session.query(Attendance).filter( and_( Attendance.session_id == session.id, Attendance.status == 'present' ) ).first() if attendance_record: attended_on_date += 1 attendance_rate = round((attended_on_date / total_classes) * 100, 2) attendance_trends.append({ 'date': current_date.strftime('%Y-%m-%d'), 'attendance_rate': attendance_rate, 'total_classes': total_classes }) # Get department statistics department_stats = [] departments = self.db.session.query(Tutor.department_id).distinct().all() for dept_id in departments: if dept_id[0]: # Skip None values dept_tutors = self.db.session.query(Tutor).filter(Tutor.department_id == dept_id[0]).all() total_dept_tutors = len(dept_tutors) # Calculate department attendance rate dept_attendance_rate = 0 present_dept_tutors = 0 for tutor in dept_tutors: tutor_sessions = [s for s in tutor.teaching_sessions] total_tutor_sessions = len(tutor_sessions) attended_tutor_sessions = 0 for session in tutor_sessions: attendance_record = self.db.session.query(Attendance).filter( and_( Attendance.session_id == session.id, Attendance.tutor_id == tutor.id, Attendance.status == 'present' ) ).first() if attendance_record: attended_tutor_sessions += 1 if total_tutor_sessions > 0: tutor_attendance_rate = (attended_tutor_sessions / total_tutor_sessions) * 100 if tutor_attendance_rate >= 80: # Consider present if attendance >= 80% present_dept_tutors += 1 if total_dept_tutors > 0: dept_attendance_rate = round((present_dept_tutors / total_dept_tutors) * 100, 2) # Get department name dept_name = "Unknown Department" if dept_tutors and dept_tutors[0].department: dept_name = dept_tutors[0].department.name department_stats.append({ 'department': dept_name, 'attendance_rate': dept_attendance_rate, 'total_tutors': total_dept_tutors, 'present_tutors': present_dept_tutors }) summary_data = { 'total_tutors': total_tutors, 'total_classes': total_sessions, 'overall_attendance_rate': overall_attendance_rate, 'present_count': present_count, 'absent_count': absent_count, 'late_count': late_count, 'excused_count': excused_count, 'attendance_trends': attendance_trends, 'department_stats': department_stats } return custom_response( success=True, data=summary_data ) except Exception as e: current_app.logger.error(f"Error fetching attendance summary: {str(e)}") return custom_response( success=False, data={} ) def get_attendance_by_department(self, department=None): """Get attendance information grouped by department""" try: query = self.db.session.query(Tutor) if department and department != 'all': query = query.filter(Tutor.department.has(name=department)) tutors = query.all() department_data = {} for tutor in tutors: dept_name = tutor.department.name if tutor.department else 'Unknown' if dept_name not in department_data: department_data[dept_name] = { 'department': dept_name, 'tutors': [], 'total_sessions': 0, 'attended_sessions': 0, 'missed_sessions': 0, 'late_sessions': 0, 'excused_sessions': 0 } # Calculate tutor attendance total_sessions = len(tutor.teaching_sessions) attended_sessions = 0 missed_sessions = 0 late_sessions = 0 excused_sessions = 0 for session in tutor.teaching_sessions: attendance_record = self.db.session.query(Attendance).filter( and_( Attendance.session_id == session.id, Attendance.tutor_id == tutor.id ) ).first() if attendance_record: if attendance_record.status == 'present': attended_sessions += 1 elif attendance_record.status == 'absent': missed_sessions += 1 elif attendance_record.status == 'late': late_sessions += 1 elif attendance_record.status == 'excused': excused_sessions += 1 else: missed_sessions += 1 attendance_rate = 0 if total_sessions > 0: attendance_rate = round((attended_sessions / total_sessions) * 100, 2) tutor_data = { 'tutor_id': tutor.id, 'staff_id': tutor.staff_id, 'name': f"{tutor.first_name} {tutor.last_name}", 'email': tutor.email, 'total_sessions': total_sessions, 'attended_sessions': attended_sessions, 'missed_sessions': missed_sessions, 'late_sessions': late_sessions, 'excused_sessions': excused_sessions, 'attendance_rate': attendance_rate } department_data[dept_name]['tutors'].append(tutor_data) department_data[dept_name]['total_sessions'] += total_sessions department_data[dept_name]['attended_sessions'] += attended_sessions department_data[dept_name]['missed_sessions'] += missed_sessions department_data[dept_name]['late_sessions'] += late_sessions department_data[dept_name]['excused_sessions'] += excused_sessions # Convert to list format result = [] for dept_name, data in department_data.items(): total_sessions = data['total_sessions'] attendance_rate = 0 if total_sessions > 0: attendance_rate = round((data['attended_sessions'] / total_sessions) * 100, 2) result.append({ 'department': dept_name, 'total_tutors': len(data['tutors']), 'total_sessions': total_sessions, 'attended_sessions': data['attended_sessions'], 'missed_sessions': data['missed_sessions'], 'late_sessions': data['late_sessions'], 'excused_sessions': data['excused_sessions'], 'attendance_rate': attendance_rate, 'tutors': data['tutors'] }) return custom_response( success=True, data=result ) except Exception as e: current_app.logger.error(f"Error fetching department attendance: {str(e)}") return custom_response( success=False, data=[] ) def get_attendance_trends(self, period='week'): """Get attendance trends over time""" try: end_date = datetime.now() if period == 'week': start_date = end_date - timedelta(days=7) days = 7 elif period == 'month': start_date = end_date - timedelta(days=30) days = 30 elif period == 'quarter': start_date = end_date - timedelta(days=90) days = 90 else: # year start_date = end_date - timedelta(days=365) days = 365 trends = [] for i in range(days): current_date = start_date + timedelta(days=i) sessions_on_date = self.db.session.query(TeachingSession).filter( func.date(TeachingSession.start_time) == current_date.date() ).all() total_classes = len(sessions_on_date) attended_classes = 0 for session in sessions_on_date: attendance_record = self.db.session.query(Attendance).filter( and_( Attendance.session_id == session.id, Attendance.status == 'present' ) ).first() if attendance_record: attended_classes += 1 attendance_rate = 0 if total_classes > 0: attendance_rate = round((attended_classes / total_classes) * 100, 2) trends.append({ 'date': current_date.strftime('%Y-%m-%d'), 'attendance_rate': attendance_rate, 'total_classes': total_classes, 'attended_classes': attended_classes }) return custom_response( success=True, data=trends ) except Exception as e: current_app.logger.error(f"Error fetching attendance trends: {str(e)}") return custom_response( success=False, data=[] ) def get_attendance_alerts(self): """Get attendance alerts and recommendations""" try: alerts = [] # Get all tutors tutors = self.db.session.query(Tutor).all() for tutor in tutors: total_sessions = len(tutor.teaching_sessions) if total_sessions == 0: continue attended_sessions = 0 missed_sessions = 0 late_sessions = 0 for session in tutor.teaching_sessions: attendance_record = self.db.session.query(Attendance).filter( and_( Attendance.session_id == session.id, Attendance.tutor_id == tutor.id ) ).first() if attendance_record: if attendance_record.status == 'present': attended_sessions += 1 elif attendance_record.status == 'absent': missed_sessions += 1 elif attendance_record.status == 'late': late_sessions += 1 else: missed_sessions += 1 attendance_rate = (attended_sessions / total_sessions) * 100 # Generate alerts based on attendance rate if attendance_rate < 70: # Poor attendance alerts.append({ "type": "poor_attendance", "severity": "high", "tutor_id": tutor.id, "tutor_name": f"{tutor.first_name} {tutor.last_name}", "staff_id": tutor.staff_id, "attendance_rate": round(attendance_rate, 2), "total_sessions": total_sessions, "missed_sessions": missed_sessions, "message": f"Tutor {tutor.first_name} {tutor.last_name} has poor attendance rate of {attendance_rate:.1f}%", "recommendation": "Schedule meeting to discuss attendance issues and provide support" }) elif attendance_rate < 80: # Below average attendance alerts.append({ "type": "below_average_attendance", "severity": "medium", "tutor_id": tutor.id, "tutor_name": f"{tutor.first_name} {tutor.last_name}", "staff_id": tutor.staff_id, "attendance_rate": round(attendance_rate, 2), "total_sessions": total_sessions, "missed_sessions": missed_sessions, "message": f"Tutor {tutor.first_name} {tutor.last_name} has below average attendance rate of {attendance_rate:.1f}%", "recommendation": "Monitor attendance closely and provide guidance if needed" }) # Check for recent missed sessions recent_missed = 0 for session in tutor.teaching_sessions[-5:]: # Last 5 sessions attendance_record = self.db.session.query(Attendance).filter( and_( Attendance.session_id == session.id, Attendance.tutor_id == tutor.id, or_( Attendance.status == 'absent', Attendance.status == None ) ) ).first() if attendance_record or not attendance_record: recent_missed += 1 if recent_missed >= 3: # Missed 3 or more recent sessions alerts.append({ "type": "recent_missed_sessions", "severity": "high", "tutor_id": tutor.id, "tutor_name": f"{tutor.first_name} {tutor.last_name}", "staff_id": tutor.staff_id, "recent_missed": recent_missed, "message": f"Tutor {tutor.first_name} {tutor.last_name} has missed {recent_missed} recent sessions", "recommendation": "Immediate intervention required - contact tutor and review schedule" }) return custom_response( success=True, data=alerts ) except Exception as e: current_app.logger.error(f"Error fetching attendance alerts: {str(e)}") return custom_response( success=False, data=[] ) def record_tutor_attendance(self, payload): """Record tutor attendance for a session""" try: session_id = payload.get('session_id') tutor_id = payload.get('tutor_id') status = payload.get('status', 'present') notes = payload.get('notes', '') recorded_by = payload.get('recorded_by') # Validate session exists session = self.db.session.query(TeachingSession).filter(TeachingSession.id == session_id).first() if not session: return custom_response( data={}, message="Teaching session not found", status_code=404 ) # Validate tutor exists tutor = self.db.session.query(Tutor).filter(Tutor.id == tutor_id).first() if not tutor: return custom_response( data={}, message="Tutor not found", status_code=404 ) # Check if attendance record already exists existing_attendance = self.db.session.query(Attendance).filter( and_( Attendance.session_id == session_id, Attendance.tutor_id == tutor_id ) ).first() if existing_attendance: # Update existing record existing_attendance.status = status existing_attendance.notes = notes existing_attendance.updated_at = datetime.now() if recorded_by: existing_attendance.updated_by = recorded_by else: # Create new record new_attendance = Attendance( session_id=session_id, tutor_id=tutor_id, status=status, notes=notes, recorded_at=datetime.now(), recorded_by=recorded_by ) self.db.session.add(new_attendance) self.db.session.commit() return custom_response( success=True, data={'session_id': session_id, 'tutor_id': tutor_id, 'status': status} ) except Exception as e: self.db.session.rollback() current_app.logger.error(f"Error recording tutor attendance: {str(e)}") return custom_response( success=False, data={} ) def update_tutor_attendance(self, payload): """Update tutor attendance record""" try: attendance_id = payload.get('attendance_id') status = payload.get('status') notes = payload.get('notes', '') updated_by = payload.get('updated_by') # Find attendance record attendance = self.db.session.query(Attendance).filter(Attendance.id == attendance_id).first() if not attendance: return custom_response( data={}, message="Attendance record not found", status_code=404 ) # Update record if status: attendance.status = status if notes: attendance.notes = notes attendance.updated_at = datetime.now() if updated_by: attendance.updated_by = updated_by self.db.session.commit() return custom_response( success=True, data={'attendance_id': attendance_id, 'status': status} ) except Exception as e: self.db.session.rollback() current_app.logger.error(f"Error updating tutor attendance: {str(e)}") return custom_response( success=False, data={} ) def get_tutor_courses_workload(self, tutor_id: str): """Get courses with workload information for a tutor""" try: with DatabaseContextManager() as ctx: # Get tutor courses with detailed information courses = ctx.session.query(Course).join( tutor_course_association, Course.id == tutor_course_association.c.course_id ).filter( tutor_course_association.c.tutor_id == tutor_id, Course.is_active == True ).all() courses_data = [] for course in courses: # Get students count for this course through the enrollment_courses association table students_count = ctx.session.query(Enrollment).join( enrollment_courses, Enrollment.id == enrollment_courses.c.enrollment_id ).filter( enrollment_courses.c.course_id == course.id, enrollment_courses.c.status == 'active' ).count() # Calculate completed hours from daily teaching sessions from src.models.models import DailyTeachingSession completed_hours = 0 sessions = ctx.session.query(DailyTeachingSession).filter( DailyTeachingSession.tutor_id == tutor_id, DailyTeachingSession.course_id == course.id ).all() for session in sessions: if session.start_time and session.end_time: start_dt = datetime.combine(session.session_date, session.start_time) end_dt = datetime.combine(session.session_date, session.end_time) duration = (end_dt - start_dt).total_seconds() / 3600 completed_hours += duration # Calculate total planned hours (assuming 3 hours per week for 15 weeks) total_hours = course.credits * 3 * 15 if course.credits else 45 # Calculate workload percentage workload_percentage = min(100, round((total_hours / 40) * 100)) if total_hours > 0 else 0 courses_data.append({ 'id': course.id, 'title': course.title, 'code': course.code, 'department': course.department, 'credit_hours': course.credits or 3, 'total_hours': total_hours, 'completed_hours': round(completed_hours, 1), 'students_count': students_count, 'status': 'active' if course.is_active else 'inactive', 'start_date': str(course.created_at.date()) if course.created_at else None, 'end_date': None, # Course model doesn't have end_date field 'room': None, # Course model doesn't have room field 'schedule': 'TBD', # Course model doesn't have schedule field 'workload_percentage': workload_percentage }) return custom_response( success=True, data=courses_data ) except Exception as e: current_app.logger.error(f"Error fetching tutor courses workload: {str(e)}") return custom_response( success=False, data=[] ) def get_tutor_timetable_blocks(self, tutor_id: str): """Get approved timetable blocks for a tutor""" try: with DatabaseContextManager() as ctx: from src.models.models import TimetableBlock # Get all approved timetable blocks for the tutor blocks = ctx.session.query(TimetableBlock).filter( TimetableBlock.tutor_id == tutor_id, TimetableBlock.is_approved == True ).order_by(TimetableBlock.day_of_week, TimetableBlock.start_time).all() blocks_data = [] for block in blocks: blocks_data.append({ 'id': block.id, 'course_id': block.course_id, 'course_title': block.course.title if block.course else 'Unknown Course', 'course_code': block.course.code if block.course else 'Unknown', 'day_of_week': block.day_of_week, 'start_time': str(block.start_time) if block.start_time else None, 'end_time': str(block.end_time) if block.end_time else None, 'room': block.room, 'block_type': block.block_type, 'status': block.status, 'is_approved': block.is_approved, 'semester': block.semester, 'academic_year': block.academic_year, 'created_at': str(block.created_at) if block.created_at else None, 'updated_at': str(block.updated_at) if block.updated_at else None }) return custom_response( success=True, data=blocks_data ) except Exception as e: current_app.logger.error(f"Error fetching tutor timetable blocks: {str(e)}") return custom_response( success=False, data=[] ) def get_daily_teaching_sessions(self, tutor_id: str): """Get all daily teaching sessions for a tutor""" try: with DatabaseContextManager() as ctx: from src.models.models import DailyTeachingSession # Get all daily teaching sessions for the tutor sessions = ctx.session.query(DailyTeachingSession).filter( DailyTeachingSession.tutor_id == tutor_id ).order_by(DailyTeachingSession.session_date.desc()).all() sessions_data = [] for session in sessions: # Get course information course = ctx.session.query(Course).filter(Course.id == session.course_id).first() # Calculate duration duration_hours = 0 if session.start_time and session.end_time: start_dt = datetime.combine(session.session_date, session.start_time) end_dt = datetime.combine(session.session_date, session.end_time) duration_hours = (end_dt - start_dt).total_seconds() / 3600 # Get students count for this session through the enrollment_courses association table students_count = ctx.session.query(Enrollment).join( enrollment_courses, Enrollment.id == enrollment_courses.c.enrollment_id ).filter( enrollment_courses.c.course_id == session.course_id, enrollment_courses.c.status == 'active' ).count() sessions_data.append({ 'id': session.id, 'session_date': str(session.session_date), 'start_time': str(session.start_time) if session.start_time else None, 'end_time': str(session.end_time) if session.end_time else None, 'subject': course.title if course else 'Unknown Subject', 'course_id': session.course_id, 'course_title': course.title if course else 'Unknown Course', 'students_count': students_count, 'is_verified': session.is_verified, 'attendance_rate': 85, # Mock data - could be calculated from actual attendance 'room': session.room or 'TBD', 'duration_hours': round(duration_hours, 1), 'created_at': str(session.created_at) }) return custom_response( success=True, data=sessions_data ) except Exception as e: current_app.logger.error(f"Error fetching daily teaching sessions: {str(e)}") return custom_response( success=False, data=[] ) def get_teaching_sessions_for_verification(self, tutor_id: str): """Get teaching sessions for verification""" try: with DatabaseContextManager() as ctx: from src.models.models import DailyTeachingSession # Get teaching sessions that need verification or are already verified sessions = ctx.session.query(DailyTeachingSession).filter( DailyTeachingSession.tutor_id == tutor_id ).order_by(DailyTeachingSession.session_date.desc()).limit(50).all() sessions_data = [] for session in sessions: # Get course information course = ctx.session.query(Course).filter(Course.id == session.course_id).first() # Get students count for this session through the enrollment_courses association table students_count = ctx.session.query(Enrollment).join( enrollment_courses, Enrollment.id == enrollment_courses.c.enrollment_id ).filter( enrollment_courses.c.course_id == session.course_id, enrollment_courses.c.status == 'active' ).count() sessions_data.append({ 'id': session.id, 'session_date': str(session.session_date), 'start_time': str(session.start_time) if session.start_time else None, 'end_time': str(session.end_time) if session.end_time else None, 'subject': course.title if course else 'Unknown Subject', 'course_title': course.title if course else 'Unknown Course', 'students_count': students_count, 'is_verified': session.is_verified, 'verification_notes': session.verification_notes, 'attendance_rate': 85, # Mock data 'room': session.room or 'TBD', 'created_at': str(session.created_at) }) return custom_response( success=True, data=sessions_data ) except Exception as e: current_app.logger.error(f"Error fetching teaching sessions for verification: {str(e)}") return custom_response( success=False, data=[] ) def verify_teaching_session_by_id(self, session_id: str, payload): """Verify or update verification for a teaching session by ID""" try: with DatabaseContextManager() as ctx: from src.models.models import DailyTeachingSession # Find the teaching session session = ctx.session.query(DailyTeachingSession).filter( DailyTeachingSession.id == session_id ).first() if not session: return custom_response( success=False, data="Teaching session not found", status_code=404 ) # Update verification status session.is_verified = payload.get('is_verified', False) session.verification_notes = payload.get('verification_notes', '') session.verified_at = datetime.now() ctx.session.commit() return custom_response( success=True, data={ 'session_id': session_id, 'is_verified': session.is_verified, 'verification_notes': session.verification_notes } ) except Exception as e: ctx.session.rollback() current_app.logger.error(f"Error verifying teaching session: {str(e)}") return custom_response( success=False, data="Failed to verify teaching session" )