from flask import current_app from src.models import DatabaseContextManager from src.models.models import ( Course, Tutor, Supervisor, Student, Speciality, AcademicSession, tutor_course_association, supervisor_course_association, course_speciality_association, course_department_association, Enrollment, CourseProgress, TeachingSession, Assignment, AssignmentSubmission, Attendance, UserType, AttendanceStatus, SubmissionStatus, enrollment_courses ) from sqlalchemy import or_, func, case, and_ from src.utils import ApiABC, custom_response import uuid from datetime import datetime, timedelta, date import random class CourseManager(ApiABC): def __init__(self): self.table = Course def create(self, payload): """ Create a new course with comprehensive validation and setup. Temporary policy: any supervisor can create courses. Note: Tutors are optional - courses can be created without assigning tutors initially. Tutors can be assigned later through the update method or dedicated tutor assignment endpoints. """ with DatabaseContextManager() as ctx: # Verify requester is a supervisor (permission relaxed temporarily) requester = ctx.session.query(Supervisor).filter( Supervisor.id == payload.get('requester_id') ).first() if not requester: return custom_response( success=False, data="Unauthorized - Only supervisors can create courses", status_code=403 ) # Validate required fields required_fields = { 'code': (str, lambda x: len(x.strip()) > 0), 'title': (str, lambda x: len(x.strip()) > 0), 'credits': (int, lambda x: x > 0), 'department': (str, lambda x: len(x.strip()) > 0), 'semester': (str, lambda x: len(x.strip()) > 0) } validation_errors = [] for field, (type_check, validator) in required_fields.items(): if field not in payload: validation_errors.append(f"Missing required field: {field}") elif not isinstance(payload[field], type_check): validation_errors.append(f"Invalid type for {field}, expected {type_check.__name__}") elif not validator(payload[field]): validation_errors.append(f"Invalid value for {field}") if validation_errors: return custom_response( success=False, data={"errors": validation_errors}, status_code=400 ) # Check if course code already exists if ctx.session.query(Course).filter(Course.code == payload['code']).first(): return custom_response( success=False, data="Course code already exists", status_code=400 ) # If provided, validate speciality_id exists if payload.get('speciality_id'): spec = ctx.session.query(Speciality).filter(Speciality.id == payload['speciality_id']).first() if not spec: return custom_response( success=False, data="Speciality not found", status_code=404 ) # If provided, validate academic_session_id exists if payload.get('academic_session_id'): academic_session = ctx.session.query(AcademicSession).filter(AcademicSession.id == payload['academic_session_id']).first() if not academic_session: return custom_response( success=False, data="Academic session not found", status_code=404 ) try: # Create course data course_data = { 'id': str(uuid.uuid4()), 'code': payload['code'], 'title': payload['title'], 'description': payload.get('description', ''), 'credits': payload['credits'], 'department': payload['department'], 'semester': payload['semester'], 'supervisor_id': requester.id, 'max_students': payload.get('max_students', 30), 'is_active': True, 'created_at': datetime.utcnow(), 'speciality_id': payload.get('speciality_id'), 'academic_session_id': payload.get('academic_session_id') } # Add optional fields optional_fields = [ 'learning_outcomes', 'required_materials', 'assessment_method', 'syllabus', 'total_hours', 'course_level', 'language', 'prerequisites', 'has_practical', 'is_shared_course', 'shared_course_type', 'sharing_level' ] for field in optional_fields: if field in payload: course_data[field] = payload[field] course = Course(**course_data) ctx.session.add(course) # Don't flush here - keep everything in the same transaction # ctx.session.flush() # Assign tutors if provided (tutors are optional) # Handle both field names for backward compatibility # Note: Tutors are optional - courses can be created without tutors tutor_data = payload.get('tutor_ids') or payload.get('tutors') # Check if tutor_data is a non-empty list (not None, not empty list) if tutor_data and isinstance(tutor_data, list) and len(tutor_data) > 0: # Since we haven't flushed, we need to use the course.id from the data course_id_for_assignment = course_data['id'] assigned_tutors = self._assign_tutors_to_course(ctx, course_id_for_assignment, tutor_data) if not assigned_tutors: current_app.logger.warning("No valid tutors were assigned to the course") # Rollback and return error if no tutors could be assigned ctx.session.rollback() return custom_response( success=False, data="Failed to assign any valid tutors to the course. Please check tutor IDs and ensure tutors are active.", status_code=400 ) # Verify the assignments were actually created in the database verification_assignments = ctx.session.query(tutor_course_association).filter( tutor_course_association.c.course_id == course_id_for_assignment ).all() else: # If no tutors provided, continue without assignment pass # Handle shared specialities if provided (for interdisciplinary courses) shared_specialities_data = payload.get('shared_specialities') if shared_specialities_data and isinstance(shared_specialities_data, list) and len(shared_specialities_data) > 0: # Extract speciality IDs from the speciality objects speciality_ids = [] for speciality_data in shared_specialities_data: if isinstance(speciality_data, dict) and 'id' in speciality_data: speciality_ids.append(speciality_data['id']) elif isinstance(speciality_data, str): speciality_ids.append(speciality_data) if speciality_ids: course_id_for_assignment = course_data['id'] assigned_specialities = self._assign_specialities_to_course(ctx, course_id_for_assignment, speciality_ids, requester.id) if not assigned_specialities: current_app.logger.warning("No valid specialities were assigned to the course") # Continue without specialities assignment - not critical for course creation # Handle shared departments if provided (for institutional-level courses) shared_departments_data = payload.get('shared_departments') if shared_departments_data and isinstance(shared_departments_data, list) and len(shared_departments_data) > 0: course_id_for_assignment = course_data['id'] assigned_departments = self._assign_departments_to_course(ctx, course_id_for_assignment, shared_departments_data, requester.id) if not assigned_departments: current_app.logger.warning("No valid departments were assigned to the course") # Continue without departments assignment - not critical for course creation # Handle sharing-capable specialities if provided (for speciality-level courses) sharing_capable_specialities_data = payload.get('sharing_capable_specialities') if sharing_capable_specialities_data and isinstance(sharing_capable_specialities_data, list) and len(sharing_capable_specialities_data) > 0: # Extract speciality IDs from the speciality objects speciality_ids = [] for speciality_data in sharing_capable_specialities_data: if isinstance(speciality_data, dict) and 'id' in speciality_data: speciality_ids.append(speciality_data['id']) elif isinstance(speciality_data, str): speciality_ids.append(speciality_data) if speciality_ids: course_id_for_assignment = course_data['id'] assigned_sharing_capable_specialities = self._assign_sharing_capable_specialities_to_course(ctx, course_id_for_assignment, speciality_ids, requester.id) if not assigned_sharing_capable_specialities: current_app.logger.warning("No valid sharing-capable specialities were assigned to the course") # Continue without sharing-capable specialities assignment - not critical for course creation # Now commit everything together ctx.session.commit() return custom_response( success=True, data={ "id": course.id, "message": "Course created successfully", "code": course.code }, status_code=201 ) except Exception as e: ctx.session.rollback() current_app.logger.error(f"Error creating course: {str(e)}", exc_info=True) return custom_response( success=False, data="Failed to create course due to server error", status_code=500 ) def update(self, course_id: str, payload): """Update an existing course (temporary: any supervisor)""" with DatabaseContextManager() as ctx: # Get the course 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 ) # Verify requester is a supervisor and in the same department as the course requester = ctx.session.query(Supervisor).filter( Supervisor.id == payload.get('requester_id') ).first() if not requester: return custom_response( success=False, data="Unauthorized - Only supervisors can update courses", status_code=403 ) # Check if requester is in the same department as the course requester_departments = [dept.department_name for dept in requester.departments if dept.is_active] if course.department not in requester_departments: return custom_response( success=False, data="Unauthorized - You can only edit courses in your department", status_code=403 ) try: # Update basic fields updatable_fields = [ 'title', 'description', 'credits', 'department', 'semester', 'max_students', 'learning_outcomes', 'required_materials', 'assessment_method', 'syllabus', 'total_hours', 'course_level', 'language', 'prerequisites' ] for field in updatable_fields: if field in payload: setattr(course, field, payload[field]) # Update speciality if provided if 'speciality_id' in payload: if payload['speciality_id']: spec = ctx.session.query(Speciality).filter(Speciality.id == payload['speciality_id']).first() if not spec: return custom_response( success=False, data="Speciality not found", status_code=404 ) course.speciality_id = payload['speciality_id'] else: # Allow clearing the speciality link course.speciality_id = None # Update academic session if provided if 'academic_session_id' in payload: if payload['academic_session_id']: academic_session = ctx.session.query(AcademicSession).filter(AcademicSession.id == payload['academic_session_id']).first() if not academic_session: return custom_response( success=False, data="Academic session not found", status_code=404 ) course.academic_session_id = payload['academic_session_id'] else: # Allow clearing the academic session link course.academic_session_id = None # Handle tutor assignments if 'tutor_ids' in payload or 'tutors' in payload: # First remove all existing tutor assignments ctx.session.execute( tutor_course_association.delete().where( tutor_course_association.c.course_id == course_id ) ) # Add new assignments using the helper method tutor_data = payload.get('tutor_ids') or payload.get('tutors') assigned_tutors = self._assign_tutors_to_course(ctx, course_id, tutor_data) if not assigned_tutors: current_app.logger.warning("No valid tutors were assigned to the course") # Handle shared specialities if provided if 'shared_specialities' in payload: # First remove all existing speciality assignments ctx.session.execute( course_speciality_association.delete().where( course_speciality_association.c.course_id == course_id ) ) # Add new assignments using the helper method shared_specialities_data = payload.get('shared_specialities') if shared_specialities_data and isinstance(shared_specialities_data, list) and len(shared_specialities_data) > 0: # Extract speciality IDs from the speciality objects speciality_ids = [] for speciality_data in shared_specialities_data: if isinstance(speciality_data, dict) and 'id' in speciality_data: speciality_ids.append(speciality_data['id']) elif isinstance(speciality_data, str): speciality_ids.append(speciality_data) if speciality_ids: assigned_specialities = self._assign_specialities_to_course(ctx, course_id, speciality_ids, requester.id) if not assigned_specialities: current_app.logger.warning("No valid specialities were assigned to the course") # Handle shared departments if provided if 'shared_departments' in payload: # First remove all existing department assignments ctx.session.execute( course_department_association.delete().where( course_department_association.c.course_id == course_id ) ) # Add new assignments using the helper method shared_departments_data = payload.get('shared_departments') if shared_departments_data and isinstance(shared_departments_data, list) and len(shared_departments_data) > 0: assigned_departments = self._assign_departments_to_course(ctx, course_id, shared_departments_data, requester.id) if not assigned_departments: current_app.logger.warning("No valid departments were assigned to the course") # Handle sharing-capable specialities if provided if 'sharing_capable_specialities' in payload: # First remove all existing sharing-capable speciality assignments # Note: This will remove ALL speciality assignments, not just sharing-capable ones # In a more sophisticated implementation, you might want to distinguish between # regular shared specialities and sharing-capable specialities ctx.session.execute( course_speciality_association.delete().where( course_speciality_association.c.course_id == course_id ) ) # Add new assignments using the helper method sharing_capable_specialities_data = payload.get('sharing_capable_specialities') if sharing_capable_specialities_data and isinstance(sharing_capable_specialities_data, list) and len(sharing_capable_specialities_data) > 0: # Extract speciality IDs from the speciality objects speciality_ids = [] for speciality_data in sharing_capable_specialities_data: if isinstance(speciality_data, dict) and 'id' in speciality_data: speciality_ids.append(speciality_data['id']) elif isinstance(speciality_data, str): speciality_ids.append(speciality_data) if speciality_ids: assigned_sharing_capable_specialities = self._assign_sharing_capable_specialities_to_course(ctx, course_id, speciality_ids, requester.id) if not assigned_sharing_capable_specialities: current_app.logger.warning("No valid sharing-capable specialities were assigned to the course") # Handle shared course fields if 'is_shared_course' in payload: course.is_shared_course = payload['is_shared_course'] if 'shared_course_type' in payload: course.shared_course_type = payload['shared_course_type'] if 'sharing_level' in payload: course.sharing_level = payload['sharing_level'] # Handle has_practical field if 'has_practical' in payload: course.has_practical = payload['has_practical'] ctx.session.commit() return custom_response( success=True, data="Course updated successfully", status_code=200 ) except Exception as e: ctx.session.rollback() current_app.logger.error(f"Error updating course: {str(e)}", exc_info=True) return custom_response( success=False, data="Failed to update course due to server error", status_code=500 ) def get(self, course_id, user_id, user_type): """ Get comprehensive course details with role-specific information. """ with DatabaseContextManager() as ctx: 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 ) # Verify user has access to this course if not self._has_course_access(ctx, course_id, user_id, user_type): return custom_response( success=False, data="Unauthorized - You don't have access to this course", status_code=403 ) # Base course data course_data = { 'id': course.id, 'code': course.code, 'title': course.title, 'description': course.description, 'credits': course.credits, 'department': course.department, 'semester': course.semester, 'supervisor': self._get_comprehensive_supervisor_info(ctx, course.supervisor_id) if course.supervisor_id else None, 'speciality': { 'id': course.speciality.id, 'name': course.speciality.name, 'description': course.speciality.description } if course.speciality else None, 'max_students': course.max_students, 'current_students': ctx.session.query(Student).join( Speciality, Student.speciality_id == Speciality.id ).filter( Speciality.id == course.speciality_id, Student.is_active == True ).count(), 'tutors': [{ 'id': t.id, 'name': f"{t.first_name} {t.last_name}", 'is_primary': self._is_primary_tutor(ctx, course.id, t.id) } for t in course.tutors], 'learning_outcomes': course.learning_outcomes, 'required_materials': course.required_materials, 'assessment_method': course.assessment_method, 'syllabus': course.syllabus, 'total_hours': course.total_hours, 'has_practical': course.has_practical, 'is_shared_course': course.is_shared_course, 'shared_course_type': course.shared_course_type, 'sharing_level': course.sharing_level, 'shared_specialities': [{ 'id': s.id, 'name': s.name, 'department': s.department, 'description': s.description, 'code': s.code, 'abbreviation': s.abbreviation } for s in course.shared_specialities], 'shared_departments': [{ 'department_name': cd.department_name, 'is_primary_department': cd.is_primary_department, 'assigned_date': str(cd.assigned_date), 'assigned_by': cd.assigned_by, 'notes': cd.notes, 'is_active': cd.is_active } for cd in course.shared_departments if cd.is_active], 'sharing_capable_specialities': [{ 'id': s.id, 'name': s.name, 'department': s.department, 'description': s.description, 'code': s.code, 'abbreviation': s.abbreviation } for s in course.shared_specialities], # Note: Currently using shared_specialities, but this should be a separate relationship for sharing-capable specialities 'created_at': str(course.created_at), 'is_active': course.is_active } # Role-specific data if user_type == UserType.student.value: course_data.update(self._get_student_course_data(ctx, course_id, user_id)) elif user_type == UserType.tutor.value: course_data.update(self._get_tutor_course_data(ctx, course_id, user_id)) elif user_type == UserType.supervisor.value: course_data.update(self._get_supervisor_course_data(ctx, course_id, user_id)) return custom_response( success=True, data=course_data, status_code=200 ) def _get_student_course_data(self, ctx, course_id, student_id): """Get student-specific course data""" # Get enrollment status through the association table enrollment_course = ctx.session.query(enrollment_courses).filter( enrollment_courses.c.course_id == course_id, enrollment_courses.c.enrollment_id.in_( ctx.session.query(Enrollment.id).filter( Enrollment.student_id == student_id ) ) ).first() # Get upcoming sessions upcoming_sessions = ctx.session.query(TeachingSession).filter( TeachingSession.course_id == course_id, TeachingSession.start_time > datetime.utcnow(), TeachingSession.status == 'scheduled' ).order_by(TeachingSession.start_time).limit(5).all() # Get assignments assignments = ctx.session.query(Assignment).filter( Assignment.course_id == course_id, Assignment.is_published == True ).order_by(Assignment.due_date).all() # Get attendance stats attendance_records = ctx.session.query(Attendance).filter( Attendance.student_id == student_id ).all() present_count = sum(1 for r in attendance_records if r.status == AttendanceStatus.present) attendance_percentage = round((present_count / len(attendance_records)) * 100, 1) if attendance_records else 0 return { 'enrollment_status': enrollment_course.status if enrollment_course else 'not_enrolled', 'grade': enrollment_course.grade if enrollment_course else None, 'attendance': { 'percentage': attendance_percentage, 'present': present_count, 'total': len(attendance_records) }, 'upcoming_sessions': [{ 'id': s.id, 'title': s.title, 'start_time': s.start_datetime.isoformat(), 'end_time': s.end_datetime.isoformat(), 'location': s.location, 'tutor': f"{s.tutor.first_name} {s.tutor.last_name}" } for s in upcoming_sessions], 'assignments': [{ 'id': a.id, 'title': a.title, 'due_date': a.due_date.isoformat() if a.due_date else None, 'type': a.assignment_type.value, 'status': self._get_assignment_status(a, student_id), 'grade': self._get_assignment_grade(a.id, student_id) } for a in assignments] } def _get_tutor_course_data(self, ctx, course_id, tutor_id): """Get tutor-specific course data""" # Verify the tutor is assigned to this course if not ctx.session.query(tutor_course_association).filter( tutor_course_association.c.tutor_id == tutor_id, tutor_course_association.c.course_id == course_id ).first(): return {} # Get upcoming sessions upcoming_sessions = ctx.session.query(TeachingSession).filter( TeachingSession.course_id == course_id, TeachingSession.tutor_id == tutor_id, TeachingSession.start_time > datetime.utcnow(), TeachingSession.status == 'scheduled' ).order_by(TeachingSession.start_time).limit(5).all() # Get assignments needing grading pending_grading = ctx.session.query(AssignmentSubmission).join( Assignment, AssignmentSubmission.assignment_id == Assignment.id ).filter( Assignment.course_id == course_id, AssignmentSubmission.status == SubmissionStatus.submitted, AssignmentSubmission.grade == None ).order_by(AssignmentSubmission.submitted_at).limit(5).all() # Get attendance stats - use safe query to handle missing columns attendance_records = Attendance.safe_query(ctx.session).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_percentage = round((present_count / len(attendance_records)) * 100, 1) if attendance_records else 0 return { 'is_primary': self._is_primary_tutor(ctx, course_id, tutor_id), 'upcoming_sessions': [{ 'id': s.id, 'title': s.title, 'start_time': s.start_datetime.isoformat(), 'end_time': s.end_datetime.isoformat(), 'location': s.location, 'students_registered': len(s.attendance_records) } for s in upcoming_sessions], 'pending_grading': [{ 'id': s.id, 'assignment_title': s.assignment.title, 'student_name': f"{s.student.first_name} {s.student.last_name}", 'submitted_at': s.submitted_at.isoformat() if s.submitted_at else None } for s in pending_grading], 'attendance_stats': { 'percentage': attendance_percentage, 'present': present_count, 'total': len(attendance_records) } } def _get_supervisor_course_data(self, ctx, course_id, supervisor_id): """Get supervisor-specific course data""" # Verify the supervisor is the course supervisor course = ctx.session.query(Course).filter( Course.id == course_id, Course.supervisor_id == supervisor_id ).first() if not course: return {} # Get all enrollments through the association table with student data enrollments = ctx.session.query( Enrollment, Student, enrollment_courses.c.status, enrollment_courses.c.grade, enrollment_courses.c.attendance_percentage ).join( enrollment_courses, Enrollment.id == enrollment_courses.c.enrollment_id ).join( Student, Enrollment.student_id == Student.id ).filter( enrollment_courses.c.course_id == course_id ).all() # Get attendance stats - use safe query to handle missing columns attendance_records = Attendance.safe_query(ctx.session).join( TeachingSession, Attendance.session_id == TeachingSession.id ).filter( TeachingSession.course_id == course_id ).all() present_count = sum(1 for r in attendance_records if r.status == AttendanceStatus.present) attendance_percentage = round((present_count / len(attendance_records)) * 100, 1) if attendance_records else 0 # Get upcoming sessions upcoming_sessions = ctx.session.query(TeachingSession).filter( TeachingSession.course_id == course_id, TeachingSession.start_time > datetime.utcnow(), TeachingSession.status == 'scheduled' ).order_by(TeachingSession.start_time).limit(5).all() # Get recent submissions recent_submissions = ctx.session.query(AssignmentSubmission).join( Assignment, AssignmentSubmission.assignment_id == Assignment.id ).filter( Assignment.course_id == course_id ).order_by(AssignmentSubmission.submitted_at.desc()).limit(5).all() return { 'enrollments': [{ 'student_id': student.id, 'student_name': f"{student.first_name} {student.last_name}", 'status': status, 'grade': grade, 'attendance_percentage': attendance_percentage } for enrollment, student, status, grade, attendance_percentage in enrollments], 'attendance_stats': { 'percentage': attendance_percentage, 'present': present_count, 'total': len(attendance_records) }, 'recent_submissions': [{ 'id': s.id, 'assignment_title': s.assignment.title, 'student_name': f"{s.student.first_name} {s.student.last_name}", 'status': s.status.value, 'grade': s.grade } for s in recent_submissions] } def enroll_student(self, course_id, student_id, requester_id, requester_type): """ Enroll a student in a course with validation. Can be called by student (self-enrollment), tutor, or supervisor. """ with DatabaseContextManager() as ctx: # Verify course exists and is active course = ctx.session.query(Course).filter( Course.id == course_id, Course.is_active == True ).first() if not course: return custom_response( success=False, data="Course not found or inactive", status_code=404 ) # Verify student exists 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 ) # Check if already enrolled through association table existing_enrollment = ctx.session.query(enrollment_courses).filter( enrollment_courses.c.course_id == course_id, enrollment_courses.c.enrollment_id.in_( ctx.session.query(Enrollment.id).filter( Enrollment.student_id == student_id ) ) ).first() if existing_enrollment: return custom_response( success=False, data="Student is already enrolled in this course", status_code=400 ) # Check course capacity current_students_count = ctx.session.query(Student).join( Speciality, Student.speciality_id == Speciality.id ).filter( Speciality.id == course.speciality_id, Student.is_active == True ).count() if current_students_count >= course.max_students: return custom_response( success=False, data="Course has reached maximum capacity", status_code=400 ) # Verify requester has permission to enroll if requester_type == UserType.student.value and requester_id != student_id: return custom_response( success=False, data="Students can only enroll themselves", status_code=403 ) if requester_type == UserType.tutor.value: # Verify tutor is assigned to this course if not ctx.session.query(tutor_course_association).filter( tutor_course_association.c.tutor_id == requester_id, tutor_course_association.c.course_id == course_id ).first(): return custom_response( success=False, data="Tutor is not assigned to this course", status_code=403 ) if requester_type == UserType.supervisor.value: # Verify supervisor is the course supervisor if course.supervisor_id != requester_id: return custom_response( success=False, data="Only the course supervisor can enroll students", status_code=403 ) try: # Create enrollment enrollment = Enrollment( id=str(uuid.uuid4()), course_id=course_id, student_id=student_id, enrollment_date=datetime.utcnow().date(), status='active' ) # Create course progress record progress = CourseProgress( id=str(uuid.uuid4()), enrollment_id=enrollment.id, student_id=student_id, completion_percentage=0.0, last_accessed=datetime.utcnow() ) ctx.session.add(enrollment) ctx.session.add(progress) ctx.session.commit() return custom_response( success=True, data="Student enrolled successfully", status_code=201 ) except Exception as e: ctx.session.rollback() current_app.logger.error(f"Error enrolling student: {str(e)}", exc_info=True) return custom_response( success=False, data="Failed to enroll student due to server error", status_code=500 ) def withdraw_student(self, course_id, student_id, requester_id, requester_type): """ Withdraw a student from a course with validation. Can be called by student (self-withdrawal), tutor, or supervisor. """ with DatabaseContextManager() as ctx: # Verify enrollment exists through association table enrollment_course = ctx.session.query(enrollment_courses).filter( enrollment_courses.c.course_id == course_id, enrollment_courses.c.enrollment_id.in_( ctx.session.query(Enrollment.id).filter( Enrollment.student_id == student_id, Enrollment.status == 'active' ) ) ).first() if not enrollment_course: return custom_response( success=False, data="Student is not actively enrolled in this course", status_code=404 ) # Verify requester has permission to withdraw if requester_type == UserType.student.value and requester_id != student_id: return custom_response( success=False, data="Students can only withdraw themselves", status_code=403 ) if requester_type == UserType.tutor.value: # Verify tutor is assigned to this course if not ctx.session.query(tutor_course_association).filter( tutor_course_association.c.tutor_id == requester_id, tutor_course_association.c.course_id == course_id ).first(): return custom_response( success=False, data="Tutor is not assigned to this course", status_code=403 ) if requester_type == UserType.supervisor.value: # Verify supervisor is the course supervisor course = ctx.session.query(Course).filter(Course.id == course_id).first() if course.supervisor_id != requester_id: return custom_response( success=False, data="Only the course supervisor can withdraw students", status_code=403 ) try: # Get the enrollment record enrollment = ctx.session.query(Enrollment).filter( Enrollment.student_id == student_id, Enrollment.status == 'active' ).first() if not enrollment: return custom_response( success=False, data="Enrollment record not found", status_code=404 ) # Update enrollment status enrollment.status = 'withdrawn' enrollment.withdrawal_date = datetime.utcnow().date() ctx.session.commit() return custom_response( success=True, data="Student withdrawn successfully", status_code=200 ) except Exception as e: ctx.session.rollback() current_app.logger.error(f"Error withdrawing student: {str(e)}", exc_info=True) return custom_response( success=False, data="Failed to withdraw student due to server error", status_code=500 ) def get_course_progress(self, course_id, student_id, requester_id, requester_type): """ Get detailed progress of a student in a course. Can be accessed by the student, their tutor, or supervisor. """ with DatabaseContextManager() as ctx: # Verify enrollment exists through association table enrollment_course = ctx.session.query(enrollment_courses).filter( enrollment_courses.c.course_id == course_id, enrollment_courses.c.enrollment_id.in_( ctx.session.query(Enrollment.id).filter( Enrollment.student_id == student_id ) ) ).first() if not enrollment_course: return custom_response( success=False, data="Student is not enrolled in this course", status_code=404 ) # Verify requester has permission to view progress if requester_type == UserType.student.value and requester_id != student_id: return custom_response( success=False, data="Students can only view their own progress", status_code=403 ) if requester_type == UserType.tutor.value: # Verify tutor is assigned to this course if not ctx.session.query(tutor_course_association).filter( tutor_course_association.c.tutor_id == requester_id, tutor_course_association.c.course_id == course_id ).first(): return custom_response( success=False, data="Tutor is not assigned to this course", status_code=403 ) if requester_type == UserType.supervisor.value: # Verify supervisor is the course supervisor course = ctx.session.query(Course).filter(Course.id == course_id).first() if course.supervisor_id != requester_id: return custom_response( success=False, data="Only the course supervisor can view student progress", status_code=403 ) # Get progress data progress = ctx.session.query(CourseProgress).filter( CourseProgress.enrollment_id == enrollment_course.enrollment_id ).first() if not progress: return custom_response( success=False, data="Progress data not found", status_code=404 ) # Get assignments data assignments = ctx.session.query(Assignment).filter( Assignment.course_id == course_id ).all() assignment_data = [] for assignment in assignments: submission = ctx.session.query(AssignmentSubmission).filter( AssignmentSubmission.assignment_id == assignment.id, AssignmentSubmission.student_id == student_id ).first() assignment_data.append({ 'id': assignment.id, 'title': assignment.title, 'type': assignment.assignment_type.value, 'due_date': assignment.due_date.isoformat() if assignment.due_date else None, 'status': submission.status.value if submission else 'not_started', 'grade': submission.grade if submission else None, 'submitted_at': submission.submitted_at.isoformat() if submission and submission.submitted_at else None }) # Get attendance data - use safe query to handle missing columns attendance_records = Attendance.safe_query(ctx.session, include_relationships=True).join( TeachingSession, Attendance.session_id == TeachingSession.id ).filter( TeachingSession.course_id == course_id, Attendance.student_id == student_id ).all() attendance_data = [{ 'session_date': r.session.start_time.date().isoformat(), 'status': r.status.value, 'tutor': f"{r.tutor.first_name} {r.tutor.last_name}" } for r in attendance_records] return custom_response( success=True, data={ 'completion_percentage': progress.completion_percentage, 'current_grade': progress.current_grade, 'attendance_percentage': progress.attendance_rate, 'assignments_completed': progress.assignments_completed, 'assignments_pending': progress.assignments_pending, 'assignments': assignment_data, 'attendance': attendance_data, 'overall_performance': progress.overall_performance, 'last_accessed': progress.last_accessed.isoformat() if progress.last_accessed else None }, status_code=200 ) def _has_course_access(self, ctx, course_id, user_id, user_type): """Check if user has access to the course based on their role""" if user_type == UserType.student.value: # Check if student's speciality matches course's speciality course = ctx.session.query(Course).filter(Course.id == course_id).first() if not course: return False student = ctx.session.query(Student).filter(Student.id == user_id).first() if not student: return False return course.speciality_id == student.speciality_id elif user_type == UserType.tutor.value: return ctx.session.query(tutor_course_association).filter( tutor_course_association.c.course_id == course_id, tutor_course_association.c.tutor_id == user_id ).first() is not None elif user_type == UserType.supervisor.value: return ctx.session.query(Course).filter( Course.id == course_id, Course.supervisor_id == user_id ).first() is not None return False def _is_primary_tutor(self, ctx, course_id, tutor_id): """Check if tutor is primary tutor for the course""" result = ctx.session.query(tutor_course_association).filter( tutor_course_association.c.course_id == course_id, tutor_course_association.c.tutor_id == tutor_id ).first() return result.is_primary if result else False def _get_assignment_status(self, assignment, student_id): """Get status of an assignment for a specific student""" submission = next( (s for s in assignment.submissions if s.student_id == student_id), None ) return submission.status.value if submission else 'not_started' def _get_assignment_grade(self, assignment_id, student_id): """Get grade for an assignment for a specific student""" with DatabaseContextManager() as ctx: submission = ctx.session.query(AssignmentSubmission).filter( AssignmentSubmission.assignment_id == assignment_id, AssignmentSubmission.student_id == student_id ).first() return submission.grade if submission else None def _get_comprehensive_supervisor_info(self, ctx, supervisor_id): """ Get comprehensive supervisor information for display purposes. This provides rich data that can be used to create appealing UI components. Args: ctx: Database context supervisor_id: ID of the supervisor Returns: Dictionary with comprehensive supervisor information """ if not supervisor_id: return None try: supervisor = ctx.session.query(Supervisor).filter(Supervisor.id == supervisor_id).first() if not supervisor: return None # Get supervisor's current course count current_courses = ctx.session.query(Course).filter( Course.supervisor_id == supervisor_id, Course.is_active == True ).count() # Get supervisor's total managed courses (including inactive) total_managed_courses = ctx.session.query(Course).filter( Course.supervisor_id == supervisor_id ).count() # Get supervisor's department statistics department_courses = ctx.session.query(Course).filter( Course.department == supervisor.get_primary_department(), Course.is_active == True ).count() # Get supervisor's recent activity (last 30 days) thirty_days_ago = datetime.utcnow() - timedelta(days=30) recent_courses = ctx.session.query(Course).filter( Course.supervisor_id == supervisor_id, Course.created_at >= thirty_days_ago ).count() # Calculate supervisor's workload percentage max_courses = getattr(supervisor, 'max_courses', 10) # Default to 10 if not set workload_percentage = round((current_courses / max_courses) * 100, 1) if max_courses > 0 else 0 # Determine availability status if workload_percentage >= 90: availability_status = "High Workload" availability_color = "red" elif workload_percentage >= 70: availability_status = "Moderate Workload" availability_color = "orange" elif workload_percentage >= 50: availability_status = "Available" availability_color = "yellow" else: availability_status = "Highly Available" availability_color = "green" # Get supervisor's expertise areas expertise_areas = [] if supervisor.specialization: expertise_areas.append(supervisor.specialization) if supervisor.qualification: expertise_areas.append(supervisor.qualification) # Get supervisor's contact information with availability hints departments_list = self._get_supervisor_departments_safe(supervisor) contact_info = { 'email': supervisor.email, 'office_location': supervisor.office_location, 'staff_id': supervisor.staff_id, 'departments': departments_list } # Add office hours if available if hasattr(supervisor, 'office_hours') and supervisor.office_hours: contact_info['office_hours'] = supervisor.office_hours # Create comprehensive supervisor info supervisor_info = { # Basic Information 'id': supervisor.id, 'name': f"{supervisor.first_name} {supervisor.last_name}", 'first_name': supervisor.first_name, 'last_name': supervisor.last_name, 'full_name': f"{supervisor.first_name} {supervisor.last_name}", 'display_name': f"{supervisor.first_name} {supervisor.last_name} ({supervisor.staff_id})", # Professional Details 'qualification': supervisor.qualification, 'specialization': supervisor.specialization, 'years_of_experience': supervisor.years_of_experience, 'is_head_of_department': supervisor.is_head_of_department, # Contact Information 'contact': contact_info, # Workload & Availability 'workload': { 'current_courses': current_courses, 'total_managed_courses': total_managed_courses, 'max_courses': max_courses, 'workload_percentage': workload_percentage, 'availability_status': availability_status, 'availability_color': availability_color, 'recent_activity': recent_courses }, # Department Context 'department_context': { 'departments': departments_list, 'department_courses': department_courses, 'department_leadership': supervisor.is_head_of_department }, # Professional Summary 'professional_summary': { 'expertise_areas': expertise_areas, 'leadership_role': "Department Head" if supervisor.is_head_of_department else "Faculty Member", 'experience_level': self._get_experience_level(supervisor.years_of_experience), 'specialization_focus': supervisor.specialization or "General Education" }, # UI Display Properties 'display': { 'avatar_initials': f"{supervisor.first_name[0]}{supervisor.last_name[0]}".upper(), 'status_badge': availability_status, 'status_color': availability_color, 'workload_indicator': workload_percentage, 'is_available_for_new_courses': workload_percentage < 80, 'recommended_course_limit': max(1, max_courses - current_courses) }, # Assignment Information 'assignment_details': { 'assigned_at': datetime.utcnow().isoformat(), # This will be updated when we track assignment dates 'assignment_type': 'primary_supervisor', 'responsibilities': [ 'Course oversight and quality assurance', 'Student progress monitoring', 'Tutor coordination and support', 'Academic standards maintenance' ] } } return supervisor_info except Exception as e: current_app.logger.error(f"Error getting comprehensive supervisor info: {str(e)}", exc_info=True) # Return basic info if comprehensive info fails supervisor = ctx.session.query(Supervisor).filter(Supervisor.id == supervisor_id).first() if supervisor: return { 'id': supervisor.id, 'name': f"{supervisor.first_name} {supervisor.last_name}", 'email': supervisor.email, 'staff_id': supervisor.staff_id, 'departments': supervisor.get_all_departments(), 'error': 'Limited information available' } return None def _get_supervisor_departments_safe(self, supervisor): """ Safely extract departments from supervisor object, handling different data types. Returns a list of department names as strings. """ try: departments_list = [] if hasattr(supervisor, 'departments') and supervisor.departments: if isinstance(supervisor.departments, list): for dept in supervisor.departments: if hasattr(dept, 'name'): departments_list.append(dept.name) elif hasattr(dept, 'department_name'): departments_list.append(dept.department_name) elif isinstance(dept, str): departments_list.append(dept) else: # Try to convert to string departments_list.append(str(dept)) elif hasattr(supervisor.departments, 'name'): departments_list.append(supervisor.departments.name) elif hasattr(supervisor.departments, 'department_name'): departments_list.append(supervisor.departments.department_name) elif isinstance(supervisor.departments, str): departments_list.append(supervisor.departments) else: departments_list.append(str(supervisor.departments)) # If no departments found, try to get from get_primary_department method if not departments_list and hasattr(supervisor, 'get_primary_department'): primary_dept = supervisor.get_primary_department() if primary_dept: departments_list.append(primary_dept) return departments_list except Exception as e: current_app.logger.warning(f"Error extracting departments from supervisor {supervisor.id}: {str(e)}") return [] def _get_experience_level(self, years_of_experience): """Helper method to categorize experience level""" if not years_of_experience: return "Not Specified" elif years_of_experience < 2: return "Early Career" elif years_of_experience < 5: return "Developing" elif years_of_experience < 10: return "Experienced" elif years_of_experience < 15: return "Senior" else: return "Expert" def debug_course_supervisor_status(self, course_id): """ Debug function to check the current supervisor assignment status of a course. This helps troubleshoot assignment issues. Args: course_id: ID of the course to debug Returns: Response with detailed debugging information """ with DatabaseContextManager() as ctx: try: # Get course information 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 supervisor_course_association table supervisor_assignment = ctx.session.query(supervisor_course_association).filter( supervisor_course_association.c.course_id == course_id ).first() # Check tutor assignments tutor_assignments = ctx.session.query(tutor_course_association).filter( tutor_course_association.c.course_id == course_id ).all() # Get supervisor details if assigned supervisor_info = None if course.supervisor_id: supervisor = ctx.session.query(Supervisor).filter(Supervisor.id == course.supervisor_id).first() if supervisor: supervisor_info = { 'id': supervisor.id, 'name': f"{supervisor.first_name} {supervisor.last_name}", 'email': supervisor.email, 'is_active': supervisor.is_active } # Check for data inconsistencies inconsistencies = [] if course.supervisor_id and not supervisor_assignment: inconsistencies.append("Course has supervisor_id but no entry in supervisor_course_association table") if not course.supervisor_id and supervisor_assignment: inconsistencies.append("Course has no supervisor_id but has entry in supervisor_course_association table") if course.supervisor_id and supervisor_assignment and course.supervisor_id != supervisor_assignment.supervisor_id: inconsistencies.append("Course supervisor_id doesn't match supervisor_course_association table") return custom_response( success=True, data={ 'course_id': course_id, 'course_code': course.code, 'course_title': course.title, 'course_is_active': course.is_active, 'course_supervisor_id': course.supervisor_id, 'supervisor_course_association': { 'exists': supervisor_assignment is not None, 'supervisor_id': supervisor_assignment.supervisor_id if supervisor_assignment else None, 'assigned_at': str(supervisor_assignment.assigned_at) if supervisor_assignment and hasattr(supervisor_assignment, 'assigned_at') else None } if supervisor_assignment else None, 'supervisor_info': supervisor_info, 'tutor_assignments': len(tutor_assignments), 'tutor_details': [{ 'tutor_id': ta.tutor_id, 'is_primary': ta.is_primary } for ta in tutor_assignments], 'inconsistencies': inconsistencies, 'can_be_claimed': not supervisor_assignment and len(tutor_assignments) == 0, 'debug_timestamp': datetime.utcnow().isoformat() }, status_code=200 ) except Exception as e: current_app.logger.error(f"Error debugging course supervisor status: {str(e)}", exc_info=True) return custom_response( success=False, data=f"Debug error: {str(e)}", status_code=500 ) def fix_course_supervisor_inconsistencies(self, course_id): """ Fix any inconsistencies between Course.supervisor_id and supervisor_course_association table. This function helps resolve data integrity issues. Args: course_id: ID of the course to fix Returns: Response with fix results """ with DatabaseContextManager() as ctx: try: # Get course information 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 supervisor_course_association table supervisor_assignment = ctx.session.query(supervisor_course_association).filter( supervisor_course_association.c.course_id == course_id ).first() fixes_applied = [] # Fix 1: If course has supervisor_id but no association record, clear the supervisor_id if course.supervisor_id and not supervisor_assignment: old_supervisor_id = course.supervisor_id course.supervisor_id = None fixes_applied.append(f"Cleared orphaned supervisor_id: {old_supervisor_id}") # Fix 2: If course has no supervisor_id but has association record, update the supervisor_id elif not course.supervisor_id and supervisor_assignment: course.supervisor_id = supervisor_assignment.supervisor_id fixes_applied.append(f"Restored supervisor_id from association: {supervisor_assignment.supervisor_id}") # Fix 3: If both exist but don't match, update course.supervisor_id to match association elif course.supervisor_id and supervisor_assignment and course.supervisor_id != supervisor_assignment.supervisor_id: old_supervisor_id = course.supervisor_id course.supervisor_id = supervisor_assignment.supervisor_id fixes_applied.append(f"Fixed mismatched supervisor_id: {old_supervisor_id} -> {supervisor_assignment.supervisor_id}") # Commit fixes if any were applied if fixes_applied: ctx.session.commit() return custom_response( success=True, data={ 'course_id': course_id, 'course_code': course.code, 'course_title': course.title, 'fixes_applied': fixes_applied, 'current_state': { 'course_supervisor_id': course.supervisor_id, 'association_exists': supervisor_assignment is not None, 'association_supervisor_id': supervisor_assignment.supervisor_id if supervisor_assignment else None }, 'message': f"Fixed {len(fixes_applied)} inconsistencies" if fixes_applied else "No inconsistencies found" }, status_code=200 ) except Exception as e: ctx.session.rollback() current_app.logger.error(f"Error fixing supervisor inconsistencies: {str(e)}", exc_info=True) return custom_response( success=False, data=f"Failed to fix inconsistencies: {str(e)}", status_code=500 ) def _get_supervisor_availability_status(self, ctx, supervisor_id): """ Get a quick availability status for supervisor display in course lists. This provides a lightweight status indicator for UI components. Args: ctx: Database context supervisor_id: ID of the supervisor Returns: Dictionary with availability status information """ if not supervisor_id: return None try: supervisor = ctx.session.query(Supervisor).filter(Supervisor.id == supervisor_id).first() if not supervisor: return None # Get current course count current_courses = ctx.session.query(Course).filter( Course.supervisor_id == supervisor_id, Course.is_active == True ).count() # Get max courses (default to 10 if not set) max_courses = getattr(supervisor, 'max_courses', 10) workload_percentage = round((current_courses / max_courses) * 100, 1) if max_courses > 0 else 0 # Determine status if workload_percentage >= 90: status = "High Workload" color = "red" icon = "⚠️" elif workload_percentage >= 70: status = "Moderate" color = "orange" icon = "🟡" elif workload_percentage >= 50: status = "Available" color = "yellow" icon = "🟢" else: status = "Highly Available" color = "green" icon = "✅" return { 'status': status, 'color': color, 'icon': icon, 'workload_percentage': workload_percentage, 'current_courses': current_courses, 'max_courses': max_courses, 'can_take_more': workload_percentage < 80 } except Exception as e: current_app.logger.error(f"Error getting supervisor availability status: {str(e)}", exc_info=True) return None def _assign_tutors_to_course(self, ctx, course_id, tutor_assignments): """ Assign tutors to a course using the same logic as tutor utils assign_course function. Args: ctx: Database context course_id: ID of the course tutor_assignments: List of tutor assignments (can be strings or dicts with tutor_id and is_primary) Returns: List of assigned tutor IDs """ assigned_tutor_ids = [] for tutor_assignment in tutor_assignments: # Handle both simple tutor_id strings and tutor assignment objects if isinstance(tutor_assignment, dict): tutor_id = tutor_assignment.get('tutor_id') is_primary = tutor_assignment.get('is_primary', False) else: # Backward compatibility: if it's just a string, treat as non-primary tutor_id = tutor_assignment is_primary = False if not tutor_id: continue # Check if tutor exists and is active tutor = ctx.session.query(Tutor).filter( Tutor.id == tutor_id, Tutor.is_active == True ).first() if not tutor: continue # 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: # Update existing assignment if is_primary status changed if existing_assignment.is_primary != is_primary: existing_assignment.is_primary = is_primary else: # Create new assignment try: stmt = tutor_course_association.insert().values( tutor_id=tutor_id, course_id=course_id, is_primary=is_primary ) ctx.session.execute(stmt) except Exception as e: current_app.logger.error(f"Error creating tutor assignment: {str(e)}") current_app.logger.error(f"SQL Error details: {e}") # Continue with other assignments but log the error continue assigned_tutor_ids.append(tutor_id) return assigned_tutor_ids def _assign_specialities_to_course(self, ctx, course_id, speciality_ids, assigned_by): """ Assign specialities to a course for interdisciplinary courses. Args: ctx: Database context course_id: ID of the course speciality_ids: List of speciality IDs assigned_by: ID of the supervisor assigning the specialities Returns: List of assigned speciality IDs """ assigned_speciality_ids = [] for speciality_id in speciality_ids: if not speciality_id: continue # Check if speciality exists and is active speciality = ctx.session.query(Speciality).filter( Speciality.id == speciality_id, Speciality.is_active == True ).first() if not speciality: continue # Check if course is already assigned to this speciality existing_assignment = ctx.session.query(course_speciality_association).filter( course_speciality_association.c.speciality_id == speciality_id, course_speciality_association.c.course_id == course_id ).first() if existing_assignment: # Update existing assignment if needed if not existing_assignment.is_active: existing_assignment.is_active = True existing_assignment.updated_at = datetime.utcnow() else: # Create new assignment try: stmt = course_speciality_association.insert().values( course_id=course_id, speciality_id=speciality_id, is_primary_speciality=False, # For shared courses, none are primary assigned_by=assigned_by, assigned_date=date.today(), is_active=True, created_at=datetime.utcnow(), updated_at=datetime.utcnow() ) ctx.session.execute(stmt) except Exception as e: current_app.logger.error(f"Error creating speciality assignment: {str(e)}") continue assigned_speciality_ids.append(speciality_id) return assigned_speciality_ids def _assign_departments_to_course(self, ctx, course_id, department_names, assigned_by): """ Assign departments to a course for institutional-level shared courses. Args: ctx: Database context course_id: ID of the course department_names: List of department names assigned_by: ID of the supervisor assigning the departments Returns: List of assigned department names """ assigned_department_names = [] for department_name in department_names: if not department_name: continue # Check if department assignment already exists existing_assignment = ctx.session.query(course_department_association).filter( course_department_association.c.department_name == department_name, course_department_association.c.course_id == course_id ).first() if existing_assignment: # Update existing assignment if needed if not existing_assignment.is_active: existing_assignment.is_active = True existing_assignment.updated_at = datetime.utcnow() else: # Create new assignment try: stmt = course_department_association.insert().values( course_id=course_id, department_name=department_name, is_primary_department=False, # For shared courses, none are primary assigned_by=assigned_by, assigned_date=date.today(), is_active=True, created_at=datetime.utcnow(), updated_at=datetime.utcnow() ) ctx.session.execute(stmt) except Exception as e: current_app.logger.error(f"Error creating department assignment: {str(e)}") continue assigned_department_names.append(department_name) return assigned_department_names def _assign_sharing_capable_specialities_to_course(self, ctx, course_id, speciality_ids, assigned_by): """ Assign sharing-capable specialities to a course for speciality-level shared courses. Args: ctx: Database context course_id: ID of the course speciality_ids: List of speciality IDs assigned_by: ID of the supervisor assigning the specialities Returns: List of assigned speciality IDs """ assigned_speciality_ids = [] for speciality_id in speciality_ids: if not speciality_id: continue # Check if speciality exists and has sharing capabilities speciality = ctx.session.query(Speciality).filter( Speciality.id == speciality_id, Speciality.is_active == True ).first() if not speciality: current_app.logger.warning(f"Speciality {speciality_id} not found or inactive") continue # Check if speciality has sharing capabilities (assuming we add this field to Speciality model) # For now, we'll assume all specialities can be sharing-capable # In the future, you might want to add a sharing_capable field to the Speciality model # Check if assignment already exists existing_assignment = ctx.session.query(course_speciality_association).filter( course_speciality_association.c.speciality_id == speciality_id, course_speciality_association.c.course_id == course_id ).first() if existing_assignment: # Update existing assignment if needed if not existing_assignment.is_active: existing_assignment.is_active = True existing_assignment.updated_at = datetime.utcnow() else: # Create new assignment try: stmt = course_speciality_association.insert().values( course_id=course_id, speciality_id=speciality_id, is_primary_speciality=False, # For shared courses, none are primary assigned_by=assigned_by, assigned_date=date.today(), is_active=True, created_at=datetime.utcnow(), updated_at=datetime.utcnow() ) ctx.session.execute(stmt) except Exception as e: current_app.logger.error(f"Error creating sharing-capable speciality assignment: {str(e)}") continue assigned_speciality_ids.append(speciality_id) return assigned_speciality_ids def get_all_available_users_for_assignment(self, department=None, search_query=None, limit=50): """ Get all available tutors and supervisors that can be assigned to courses. This provides a comprehensive list for the frontend dropdown. Args: department: Optional department filter search_query: Optional search query to filter users limit: Maximum number of results to return """ with DatabaseContextManager() as ctx: try: # Get all active tutors tutor_query = ctx.session.query(Tutor).filter(Tutor.is_active == True) if department: tutor_query = tutor_query.filter(Tutor.department == department) if search_query: search = f"%{search_query}%" tutor_query = tutor_query.filter( or_( Tutor.first_name.ilike(search), Tutor.last_name.ilike(search), Tutor.staff_id.ilike(search), Tutor.email.ilike(search) ) ) tutors = tutor_query.order_by(Tutor.first_name, Tutor.last_name).limit(limit).all() # Get all active supervisors supervisor_query = ctx.session.query(Supervisor).filter(Supervisor.is_active == True) if department: supervisor_query = supervisor_query.filter(Supervisor.department == department) if search_query: search = f"%{search_query}%" supervisor_query = supervisor_query.filter( or_( Supervisor.first_name.ilike(search), Supervisor.last_name.ilike(search), Supervisor.staff_id.ilike(search), Supervisor.email.ilike(search) ) ) supervisors = supervisor_query.order_by(Supervisor.first_name, Supervisor.last_name).limit(limit).all() # Prepare tutor data tutors_data = [] for tutor in tutors: # Get current course count for this tutor current_courses = ctx.session.query(tutor_course_association).filter( tutor_course_association.c.tutor_id == tutor.id ).count() tutors_data.append({ 'id': tutor.id, 'first_name': tutor.first_name, 'last_name': tutor.last_name, 'email': tutor.email, 'staff_id': tutor.staff_id, 'department': tutor.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, 'current_courses': current_courses, 'max_teaching_hours': tutor.max_teaching_hours, 'user_type': 'tutor', 'full_name': f"{tutor.first_name} {tutor.last_name}", 'display_name': f"{tutor.first_name} {tutor.last_name} ({tutor.staff_id}) - Tutor", 'availability_status': 'Available' if not tutor.is_on_leave else 'On Leave' }) # Prepare supervisor data supervisors_data = [] for supervisor in supervisors: # Get current managed courses count for this supervisor current_courses = ctx.session.query(Course).filter( Course.supervisor_id == supervisor.id, Course.is_active == True ).count() supervisors_data.append({ 'id': supervisor.id, 'first_name': supervisor.first_name, 'last_name': supervisor.last_name, 'email': supervisor.email, 'staff_id': supervisor.staff_id, 'departments': self._get_supervisor_departments_safe(supervisor), 'qualification': supervisor.qualification, 'specialization': supervisor.specialization, 'office_location': supervisor.office_location, 'is_head_of_department': supervisor.is_head_of_department, 'years_of_experience': supervisor.years_of_experience, 'current_courses': current_courses, 'max_tutors': supervisor.max_tutors, 'user_type': 'supervisor', 'full_name': f"{supervisor.first_name} {supervisor.last_name}", 'display_name': f"{supervisor.first_name} {supervisor.last_name} ({supervisor.staff_id}) - Supervisor", 'availability_status': 'Available' }) # Combine and sort all users all_users = tutors_data + supervisors_data all_users.sort(key=lambda x: x['full_name']) # Get summary statistics total_users = len(all_users) total_tutors = len(tutors_data) total_supervisors = len(supervisors_data) available_tutors = len([t for t in tutors_data if not t['is_on_leave']]) on_leave_tutors = len([t for t in tutors_data if t['is_on_leave']]) return custom_response( success=True, data={ 'users': all_users, 'tutors': tutors_data, 'supervisors': supervisors_data, 'summary': { 'total_users': total_users, 'total_tutors': total_tutors, 'total_supervisors': total_supervisors, 'available_tutors': available_tutors, 'on_leave_tutors': on_leave_tutors, 'departments': list(set([dept for u in all_users if u.get('departments') for dept in u['departments']])) }, 'filters_applied': { 'department': department, 'search_query': search_query, 'limit': limit }, 'message': f'Found {total_users} users ({total_tutors} tutors, {total_supervisors} supervisors)' }, status_code=200 ) except Exception as e: current_app.logger.error(f"Error getting available users for assignment: {str(e)}", exc_info=True) return custom_response( success=False, data=f"Failed to get available users: {str(e)}", status_code=500 ) def get_all_available_users_paginated(self, page=1, per_page=20, department=None, search_query=None, user_type=None): """ Get all available users with pagination for better performance. Args: page: Page number (1-based) per_page: Number of users per page department: Optional department filter search_query: Optional search query user_type: Optional filter for 'tutor' or 'supervisor' """ with DatabaseContextManager() as ctx: try: offset = (page - 1) * per_page # Build base queries tutor_query = ctx.session.query(Tutor).filter(Tutor.is_active == True) supervisor_query = ctx.session.query(Supervisor).filter(Supervisor.is_active == True) # Apply filters if department: tutor_query = tutor_query.filter(Tutor.department == department) supervisor_query = supervisor_query.filter(Supervisor.department == department) if search_query: search = f"%{search_query}%" tutor_query = tutor_query.filter( or_( Tutor.first_name.ilike(search), Tutor.last_name.ilike(search), Tutor.staff_id.ilike(search), Tutor.email.ilike(search) ) ) supervisor_query = supervisor_query.filter( or_( Supervisor.first_name.ilike(search), Supervisor.last_name.ilike(search), Supervisor.staff_id.ilike(search), Supervisor.email.ilike(search) ) ) # Get counts for pagination total_tutors = tutor_query.count() if not user_type or user_type == 'tutor' else 0 total_supervisors = supervisor_query.count() if not user_type or user_type == 'supervisor' else 0 total_users = total_tutors + total_supervisors # Apply pagination and get data users_data = [] if not user_type or user_type == 'tutor': tutors = tutor_query.order_by(Tutor.first_name, Tutor.last_name).offset(offset).limit(per_page).all() for tutor in tutors: current_courses = ctx.session.query(tutor_course_association).filter( tutor_course_association.c.tutor_id == tutor.id ).count() users_data.append({ 'id': tutor.id, 'first_name': tutor.first_name, 'last_name': tutor.last_name, 'email': tutor.email, 'staff_id': tutor.staff_id, 'department': tutor.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, 'current_courses': current_courses, 'max_teaching_hours': tutor.max_teaching_hours, 'user_type': 'tutor', 'full_name': f"{tutor.first_name} {tutor.last_name}", 'display_name': f"{tutor.first_name} {tutor.last_name} ({tutor.staff_id}) - Tutor", 'availability_status': 'Available' if not tutor.is_on_leave else 'On Leave' }) if not user_type or user_type == 'supervisor': supervisors = supervisor_query.order_by(Supervisor.first_name, Supervisor.last_name).offset(offset).limit(per_page).all() for supervisor in supervisors: current_courses = ctx.session.query(Course).filter( Course.supervisor_id == supervisor.id, Course.is_active == True ).count() users_data.append({ 'id': supervisor.id, 'first_name': supervisor.first_name, 'last_name': supervisor.last_name, 'email': supervisor.email, 'staff_id': supervisor.staff_id, 'department': supervisor.get_primary_department() if hasattr(supervisor, 'get_primary_department') else 'Not assigned', 'qualification': supervisor.qualification, 'specialization': supervisor.specialization, 'office_location': supervisor.office_location, 'is_head_of_department': supervisor.is_head_of_department, 'years_of_experience': supervisor.years_of_experience, 'current_courses': current_courses, 'max_tutors': supervisor.max_tutors, 'user_type': 'supervisor', 'full_name': f"{supervisor.first_name} {supervisor.last_name}", 'display_name': f"{supervisor.first_name} {supervisor.last_name} ({supervisor.staff_id}) - Supervisor", 'availability_status': 'Available' }) # Sort combined results users_data.sort(key=lambda x: x['full_name']) return custom_response( success=True, data={ 'users': users_data, 'pagination': { 'page': page, 'per_page': per_page, 'total_users': total_users, 'total_pages': (total_users + per_page - 1) // per_page, 'has_next': page * per_page < total_users, 'has_prev': page > 1 }, 'summary': { 'total_tutors': total_tutors, 'total_supervisors': total_supervisors, 'departments': list(set([u['department'] for u in users_data if u['department']])) }, 'filters_applied': { 'department': department, 'search_query': search_query, 'user_type': user_type }, 'message': f'Page {page} of {(total_users + per_page - 1) // per_page} - Showing {len(users_data)} users' }, status_code=200 ) except Exception as e: current_app.logger.error(f"Error getting paginated users: {str(e)}", exc_info=True) return custom_response( success=False, data=f"Failed to get paginated users: {str(e)}", status_code=500 ) def get_users_by_ids(self, user_ids, user_types=None): """ Get specific users by their IDs with detailed information. This is useful for fetching details of already selected users. Args: user_ids: List of user IDs to fetch user_types: Optional filter for specific user types ('tutor', 'supervisor', or both) """ with DatabaseContextManager() as ctx: try: if not user_ids: return custom_response( success=True, data={'users': [], 'message': 'No user IDs provided'} ) users_data = [] # Get tutors if requested or if no specific type filter if not user_types or 'tutor' in user_types: tutors = ctx.session.query(Tutor).filter( Tutor.id.in_(user_ids), Tutor.is_active == True ).all() for tutor in tutors: # Get current course count current_courses = ctx.session.query(tutor_course_association).filter( tutor_course_association.c.tutor_id == tutor.id ).count() users_data.append({ 'id': tutor.id, 'first_name': tutor.first_name, 'last_name': tutor.last_name, 'email': tutor.email, 'staff_id': tutor.staff_id, 'department': tutor.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, 'current_courses': current_courses, 'max_teaching_hours': tutor.max_teaching_hours, 'user_type': 'tutor', 'full_name': f"{tutor.first_name} {tutor.last_name}", 'display_name': f"{tutor.first_name} {tutor.last_name} ({tutor.staff_id}) - Tutor", 'availability_status': 'Available' if not tutor.is_on_leave else 'On Leave' }) # Get supervisors if requested or if no specific type filter if not user_types or 'supervisor' in user_types: supervisors = ctx.session.query(Supervisor).filter( Supervisor.id.in_(user_ids), Supervisor.is_active == True ).all() for supervisor in supervisors: # Get current managed courses count current_courses = ctx.session.query(Course).filter( Course.supervisor_id == supervisor.id, Course.is_active == True ).count() users_data.append({ 'id': supervisor.id, 'first_name': supervisor.first_name, 'last_name': supervisor.last_name, 'email': supervisor.email, 'staff_id': supervisor.staff_id, 'department': supervisor.get_primary_department() if hasattr(supervisor, 'get_primary_department') else 'Not assigned', 'qualification': supervisor.qualification, 'specialization': supervisor.specialization, 'office_location': supervisor.office_location, 'is_head_of_department': supervisor.is_head_of_department, 'years_of_experience': supervisor.years_of_experience, 'current_courses': current_courses, 'max_tutors': supervisor.max_tutors, 'user_type': 'supervisor', 'full_name': f"{supervisor.first_name} {supervisor.last_name}", 'display_name': f"{supervisor.first_name} {supervisor.last_name} ({supervisor.staff_id}) - Supervisor", 'availability_status': 'Available' }) # Sort by full name users_data.sort(key=lambda x: x['full_name']) return custom_response( success=True, data={ 'users': users_data, 'total_found': len(users_data), 'requested_ids': user_ids, 'user_types_filtered': user_types, 'message': f'Found {len(users_data)} users out of {len(user_ids)} requested IDs' }, status_code=200 ) except Exception as e: current_app.logger.error(f"Error getting users by IDs: {str(e)}", exc_info=True) return custom_response( success=False, data=f"Failed to get users by IDs: {str(e)}", status_code=500 ) def get_field_name_guidance(self): """ Get guidance on field names for tutor assignment. This helps users understand the difference between tutor_ids and tutors fields. """ return custom_response( success=True, data={ 'message': 'Field name guidance for tutor assignment', 'field_names': { 'tutor_ids': { 'description': 'Primary field name for tutor assignment', 'type': 'array', 'formats': [ 'Simple: ["tutor_id_1", "tutor_id_2"]', 'Detailed: [{"tutor_id": "id1", "is_primary": true}]' ], 'recommended': True }, 'tutors': { 'description': 'Alternative field name for backward compatibility', 'type': 'array', 'formats': [ 'Simple: ["tutor_id_1", "tutor_id_2"]', 'Detailed: [{"tutor_id": "id1", "is_primary": true}]' ], 'recommended': False, 'note': 'Will be deprecated in future versions' } }, 'migration_guide': { 'current_usage': 'If you are using the "tutors" field, consider migrating to "tutor_ids"', 'steps': [ '1. Update your frontend to send "tutor_ids" instead of "tutors"', '2. Both fields will work during the transition period', '3. Test with the new field name', '4. Remove the old "tutors" field usage' ], 'example': { 'old_format': {'tutors': ['tutor_id_1', 'tutor_id_2']}, 'new_format': {'tutor_ids': ['tutor_id_1', 'tutor_id_2']} } }, 'current_behavior': 'The system currently accepts both field names and processes them identically' }, status_code=200 ) def get_available_tutors_for_assignment(self, department=None): """ Get list of available tutors that can be assigned to courses. This helps users see what tutors are available for assignment. """ with DatabaseContextManager() as ctx: try: # Query for active tutors query = ctx.session.query(Tutor).filter(Tutor.is_active == True) if department: query = query.filter(Tutor.department == department) tutors = query.order_by(Tutor.first_name, Tutor.last_name).all() tutors_data = [] for tutor in tutors: # Get current course count for this tutor current_courses = ctx.session.query(tutor_course_association).filter( tutor_course_association.c.tutor_id == tutor.id ).count() tutors_data.append({ 'id': tutor.id, 'first_name': tutor.first_name, 'last_name': tutor.last_name, 'email': tutor.email, 'staff_id': tutor.staff_id, 'department': tutor.department, 'qualification': tutor.qualification, 'specialization': tutor.specialization, 'is_full_time': tutor.is_full_time, 'is_on_leave': tutor.is_on_leave, 'current_courses': current_courses, 'max_teaching_hours': tutor.max_teaching_hours, 'full_name': f"{tutor.first_name} {tutor.last_name}", 'display_name': f"{tutor.first_name} {tutor.last_name} ({tutor.staff_id})" }) return custom_response( success=True, data={ 'tutors': tutors_data, 'total_available': len(tutors_data), 'department_filter': department, 'message': f'Found {len(tutors_data)} available tutors{f" in {department}" if department else ""}' }, status_code=200 ) except Exception as e: current_app.logger.error(f"Error getting available tutors: {str(e)}", exc_info=True) return custom_response( success=False, data=f"Failed to get available tutors: {str(e)}", status_code=500 ) def get_expected_payload_format(self): """ Get the expected payload format for course creation. This helps users understand how to properly format their requests. """ return custom_response( success=True, data={ 'message': 'Expected payload format for course creation', 'required_fields': { 'code': 'string - unique course code', 'title': 'string - course title', 'credits': 'integer - number of credits', 'department': 'string - department name', 'semester': 'string - semester information', 'requester_id': 'string - supervisor ID creating the course' }, 'optional_fields': { 'description': 'string - course description', 'max_students': 'integer - maximum number of students (default: 30)', 'speciality_id': 'string - speciality ID (optional)', 'learning_outcomes': 'string - learning outcomes', 'required_materials': 'string - required materials', 'assessment_method': 'string - assessment method', 'syllabus': 'string - syllabus content', 'total_hours': 'integer - total course hours', 'course_level': 'string - course level (e.g., "Level 3", "Level 4")', 'language': 'string - course language (default: "English")', 'prerequisites': 'string - prerequisite courses', 'has_practical': 'boolean - whether course has practical sessions' }, 'tutor_assignment': { 'tutor_ids': [ 'Simple format (string array):', ['tutor_id_1', 'tutor_id_2', 'tutor_id_3'], '', 'Detailed format (object array with primary designation):', [ {'tutor_id': 'tutor_id_1', 'is_primary': True}, {'tutor_id': 'tutor_id_2', 'is_primary': False}, {'tutor_id': 'tutor_id_3', 'is_primary': False} ] ], 'note': 'At least one tutor must be assigned to create a course. Both "tutor_ids" and "tutors" field names are supported.', 'field_names': { 'tutor_ids': 'Preferred field name for new implementations', 'tutors': 'Alternative field name for backward compatibility' } }, 'example_payload': { 'code': 'CS101', 'title': 'Introduction to Computer Science', 'credits': 3, 'department': 'Computer Science', 'semester': 'Fall 2024', 'requester_id': 'supervisor_uuid_here', 'description': 'Basic computer science concepts', 'max_students': 25, 'tutor_ids': [ {'tutor_id': 'tutor_uuid_1', 'is_primary': True}, {'tutor_id': 'tutor_uuid_2', 'is_primary': False} ], 'note': 'You can also use "tutors" instead of "tutor_ids" for the same functionality' }, 'field_compatibility': { 'tutor_ids': 'Primary field name - supports both simple arrays and detailed objects', 'tutors': 'Alternative field name - same functionality as tutor_ids', 'recommendation': 'Use tutor_ids for new implementations, tutors is supported for existing code' } }, status_code=200 ) def test_database_connectivity(self): """ Test method to verify database connectivity and basic operations. This is for debugging purposes. """ with DatabaseContextManager() as ctx: try: # Test basic query tutor_count = ctx.session.query(Tutor).filter(Tutor.is_active == True).count() course_count = ctx.session.query(Course).filter(Course.is_active == True).count() # Test association table query association_count = ctx.session.query(tutor_course_association).count() return custom_response( success=True, data={ 'message': 'Database connectivity test completed', 'active_tutors': tutor_count, 'active_courses': course_count, 'tutor_course_associations': association_count, 'database_status': 'Connected and operational' }, status_code=200 ) except Exception as e: current_app.logger.error(f"Database connectivity test failed: {str(e)}", exc_info=True) return custom_response( success=False, data=f"Database connectivity test failed: {str(e)}", status_code=500 ) def test_tutor_assignment(self, course_id, tutor_ids): """ Test method to verify tutor assignment is working correctly. This is for debugging purposes. """ with DatabaseContextManager() as ctx: try: # Check if course exists 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 current tutor assignments current_assignments = ctx.session.query(tutor_course_association).filter( tutor_course_association.c.course_id == course_id ).all() # Try to assign tutors assigned_tutors = self._assign_tutors_to_course(ctx, course_id, tutor_ids) # Check assignments after assignment new_assignments = ctx.session.query(tutor_course_association).filter( tutor_course_association.c.course_id == course_id ).all() return custom_response( success=True, data={ 'message': 'Tutor assignment test completed', 'course_id': course_id, 'tutors_to_assign': tutor_ids, 'assigned_tutors': assigned_tutors, 'assignments_before': len(current_assignments), 'assignments_after': len(new_assignments), 'current_assignments': [ { 'tutor_id': a.tutor_id, 'is_primary': a.is_primary } for a in new_assignments ] }, status_code=200 ) except Exception as e: current_app.logger.error(f"Error in tutor assignment test: {str(e)}", exc_info=True) return custom_response( success=False, data=f"Tutor assignment test failed: {str(e)}", status_code=500 ) def get_course_tutors(self, course_id): """ Get all tutors assigned to a specific course with their assignment details. Args: course_id: ID of the course Returns: Response with list of tutors and their assignment details """ with DatabaseContextManager() as ctx: 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 ) # Get tutor assignments for this course tutor_assignments = ctx.session.query(tutor_course_association).filter( tutor_course_association.c.course_id == course_id ).all() tutors_data = [] for assignment in tutor_assignments: tutor = ctx.session.query(Tutor).filter(Tutor.id == assignment.tutor_id).first() if tutor and tutor.is_active: tutors_data.append({ 'id': tutor.id, 'first_name': tutor.first_name, 'last_name': tutor.last_name, 'email': tutor.email, 'staff_id': tutor.staff_id, 'department': tutor.department, 'qualification': tutor.qualification, 'is_primary': assignment.is_primary, 'assigned_at': str(assignment.assigned_at) if hasattr(assignment, 'assigned_at') else None }) return custom_response( success=True, data={ 'course_id': course_id, 'course_code': course.code, 'course_title': course.title, 'tutors': tutors_data, 'total_tutors': len(tutors_data), 'primary_tutor': next((t for t in tutors_data if t['is_primary']), None) }, status_code=200 ) def remove_tutor_from_course(self, course_id, tutor_id, requester_id): """ Remove a specific tutor from a course. Only the course supervisor can remove tutors. Args: course_id: ID of the course tutor_id: ID of the tutor to remove requester_id: ID of the supervisor making the request Returns: Response with success/error status """ with DatabaseContextManager() as ctx: # Verify course exists 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 ) # Verify requester is the course supervisor if course.supervisor_id != requester_id: return custom_response( success=False, data="Unauthorized - Only the course supervisor can remove tutors", status_code=403 ) # Check if tutor is assigned to this course existing_assignment = ctx.session.query(tutor_course_association).filter( tutor_course_association.c.course_id == course_id, tutor_course_association.c.tutor_id == tutor_id ).first() if not existing_assignment: return custom_response( success=False, data="Tutor is not assigned to this course", status_code=404 ) try: # Remove the tutor assignment ctx.session.execute( tutor_course_association.delete().where( and_( tutor_course_association.c.course_id == course_id, tutor_course_association.c.tutor_id == tutor_id ) ) ) ctx.session.commit() return custom_response( success=True, data="Tutor removed from course successfully", status_code=200 ) except Exception as e: ctx.session.rollback() current_app.logger.error(f"Error removing tutor from course: {str(e)}", exc_info=True) return custom_response( success=False, data="Failed to remove tutor from course due to server error", status_code=500 ) def delete(self, course_id, requester_id): """ Permanently delete a course and all its related data. Only course supervisor or department head can delete a course. This is a hard delete that removes all associated data. """ with DatabaseContextManager() as ctx: 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 ) # Verify requester is a supervisor and in the same department as the course requester = ctx.session.query(Supervisor).filter( Supervisor.id == requester_id ).first() if not requester: return custom_response( success=False, data="Unauthorized - Only supervisors can delete courses", status_code=403 ) # Check if requester is in the same department as the course requester_departments = [dept.department_name for dept in requester.departments if dept.is_active] if course.department not in requester_departments: return custom_response( success=False, data="Unauthorized - You can only delete courses in your department", status_code=403 ) try: current_app.logger.info(f"Starting course deletion for course {course_id} by supervisor {requester_id}") # Import all necessary models from src.models.models import ( TimetableBlock, Assignment, CourseModule, CourseResource, Attendance, TeachingSession, TutorTeachingLog, Reminder, tutor_course_association, supervisor_course_association ) deleted_counts = {} # 1. Delete timetable blocks timetable_blocks = ctx.session.query(TimetableBlock).filter( TimetableBlock.course_id == course_id ).all() deleted_counts['timetable_blocks'] = len(timetable_blocks) for block in timetable_blocks: ctx.session.delete(block) current_app.logger.info(f"Deleted {deleted_counts['timetable_blocks']} timetable blocks") # 2. Delete assignments assignments = ctx.session.query(Assignment).filter( Assignment.course_id == course_id ).all() deleted_counts['assignments'] = len(assignments) for assignment in assignments: ctx.session.delete(assignment) current_app.logger.info(f"Deleted {deleted_counts['assignments']} assignments") # 3. Delete course modules and their resources modules = ctx.session.query(CourseModule).filter( CourseModule.course_id == course_id ).all() deleted_counts['modules'] = len(modules) for module in modules: # Delete module resources first resources = ctx.session.query(CourseResource).filter( CourseResource.module_id == module.id ).all() deleted_counts['module_resources'] = deleted_counts.get('module_resources', 0) + len(resources) for resource in resources: ctx.session.delete(resource) ctx.session.delete(module) current_app.logger.info(f"Deleted {deleted_counts['modules']} modules and {deleted_counts.get('module_resources', 0)} module resources") # 4. Delete course resources (direct course resources) course_resources = ctx.session.query(CourseResource).filter( CourseResource.course_id == course_id ).all() deleted_counts['course_resources'] = len(course_resources) for resource in course_resources: ctx.session.delete(resource) current_app.logger.info(f"Deleted {deleted_counts['course_resources']} course resources") # 5. Delete teaching sessions teaching_sessions = ctx.session.query(TeachingSession).filter( TeachingSession.course_id == course_id ).all() deleted_counts['teaching_sessions'] = len(teaching_sessions) for session in teaching_sessions: ctx.session.delete(session) current_app.logger.info(f"Deleted {deleted_counts['teaching_sessions']} teaching sessions") # 9. Delete association table entries # Note: Course-Student associations are now handled through speciality relationship # No direct deletion needed as students access courses through their speciality deleted_counts['student_associations'] = 0 # Tutor-Course associations tutor_associations = ctx.session.execute( tutor_course_association.delete().where( tutor_course_association.c.course_id == course_id ) ) deleted_counts['tutor_associations'] = tutor_associations.rowcount # Supervisor-Course associations supervisor_associations = ctx.session.execute( supervisor_course_association.delete().where( supervisor_course_association.c.course_id == course_id ) ) deleted_counts['supervisor_associations'] = supervisor_associations.rowcount current_app.logger.info(f"Deleted association entries: students={deleted_counts['student_associations']}, tutors={deleted_counts['tutor_associations']}, supervisors={deleted_counts['supervisor_associations']}") # 10. Finally, delete the course itself ctx.session.delete(course) # Commit all changes ctx.session.commit() current_app.logger.info(f"Successfully deleted course {course_id} and all related data") return custom_response( success=True, data={ "message": "Course and all related data deleted successfully", "course_id": course_id, "deleted_items": deleted_counts }, status_code=200 ) except Exception as e: ctx.session.rollback() current_app.logger.error(f"Error deleting course {course_id}: {str(e)}", exc_info=True) return custom_response( success=False, data=f"Failed to delete course due to server error: {str(e)}", status_code=500 ) def restore_course(self, course_id, requester_id): """ Restore an archived course. Only course supervisor or department head can restore a course. """ with DatabaseContextManager() as ctx: course = ctx.session.query(Course).filter( Course.id == course_id, Course.is_archived == True ).first() if not course: return custom_response( success=False, data="Archived course not found", status_code=404 ) # Verify requester is a supervisor and in the same department as the course requester = ctx.session.query(Supervisor).filter( Supervisor.id == requester_id ).first() if not requester: return custom_response( success=False, data="Unauthorized - Only supervisors can restore courses", status_code=403 ) # Check if requester is in the same department as the course requester_departments = [dept.department_name for dept in requester.departments if dept.is_active] if course.department not in requester_departments: return custom_response( success=False, data="Unauthorized - You can only restore courses in your department", status_code=403 ) try: course.is_active = True course.is_archived = False course.archive_date = None ctx.session.commit() return custom_response( success=True, data="Course restored successfully", status_code=200 ) except Exception as e: ctx.session.rollback() current_app.logger.error(f"Error restoring course: {str(e)}", exc_info=True) return custom_response( success=False, data="Failed to restore course due to server error", status_code=500 ) def delete_department_courses(self, department_name, requester_id, dry_run=False): """ Delete all courses belonging to a specific department. This is a bulk operation for department cleanup. Args: department_name: Name of the department (e.g., 'Hospitality') requester_id: ID of the supervisor requesting the deletion dry_run: If True, only simulate the deletion without actually deleting Returns: Response with deletion statistics """ with DatabaseContextManager() as ctx: # Verify requester is a supervisor requester = ctx.session.query(Supervisor).filter( Supervisor.id == requester_id ).first() if not requester: return custom_response( success=False, data="Unauthorized - Only supervisors can delete department courses", status_code=403 ) # Check if requester has permission for this department requester_departments = [dept.department_name for dept in requester.departments if dept.is_active] if department_name not in requester_departments: return custom_response( success=False, data=f"Unauthorized - You can only delete courses in your department. Your departments: {requester_departments}", status_code=403 ) try: current_app.logger.info(f"Starting bulk deletion for department: {department_name} by supervisor: {requester_id}") # Find all courses in the department courses = ctx.session.query(Course).filter( Course.department == department_name, Course.is_active == True ).all() if not courses: return custom_response( success=True, data={ "message": f"No active courses found in {department_name} department", "department": department_name, "courses_deleted": 0, "dry_run": dry_run }, status_code=200 ) current_app.logger.info(f"Found {len(courses)} courses in {department_name} department") if dry_run: # Dry run - just return what would be deleted course_info = [] for course in courses: course_info.append({ "id": course.id, "code": course.code, "title": course.title, "credits": course.credits, "created_at": course.created_at.isoformat() if course.created_at else None }) return custom_response( success=True, data={ "message": f"DRY RUN: Would delete {len(courses)} courses from {department_name} department", "department": department_name, "courses_to_delete": course_info, "dry_run": True }, status_code=200 ) # Actual deletion deleted_counts = { "courses": 0, "timetable_blocks": 0, "assignments": 0, "course_modules": 0, "course_resources": 0, "teaching_sessions": 0, "student_associations": 0, "tutor_associations": 0, "supervisor_associations": 0 } # Import all necessary models from src.models.models import ( TimetableBlock, Assignment, CourseModule, CourseResource, Attendance, TeachingSession, TutorTeachingLog, Reminder, tutor_course_association, supervisor_course_association, CourseDepartment ) for course in courses: course_id = course.id current_app.logger.info(f"Deleting course: {course.title} (ID: {course_id})") # 1. Delete timetable blocks timetable_blocks = ctx.session.query(TimetableBlock).filter( TimetableBlock.course_id == course_id ).all() deleted_counts["timetable_blocks"] += len(timetable_blocks) for block in timetable_blocks: ctx.session.delete(block) # 2. Delete assignments assignments = ctx.session.query(Assignment).filter( Assignment.course_id == course_id ).all() deleted_counts["assignments"] += len(assignments) for assignment in assignments: ctx.session.delete(assignment) # 3. Delete course modules course_modules = ctx.session.query(CourseModule).filter( CourseModule.course_id == course_id ).all() deleted_counts["course_modules"] += len(course_modules) for module in course_modules: ctx.session.delete(module) # 4. Delete course resources course_resources = ctx.session.query(CourseResource).filter( CourseResource.course_id == course_id ).all() deleted_counts["course_resources"] += len(course_resources) for resource in course_resources: ctx.session.delete(resource) # 5. Delete teaching sessions teaching_sessions = ctx.session.query(TeachingSession).filter( TeachingSession.course_id == course_id ).all() deleted_counts["teaching_sessions"] += len(teaching_sessions) for session in teaching_sessions: ctx.session.delete(session) # 6. Delete association table entries # Note: Course-Student associations are now handled through speciality relationship # No direct deletion needed as students access courses through their speciality deleted_counts["student_associations"] += 0 # Tutor-Course associations tutor_associations = ctx.session.execute( tutor_course_association.delete().where( tutor_course_association.c.course_id == course_id ) ) deleted_counts["tutor_associations"] += tutor_associations.rowcount # Supervisor-Course associations supervisor_associations = ctx.session.execute( supervisor_course_association.delete().where( supervisor_course_association.c.course_id == course_id ) ) deleted_counts["supervisor_associations"] += supervisor_associations.rowcount # 7. Delete course department associations course_dept_associations = ctx.session.query(CourseDepartment).filter( CourseDepartment.course_id == course_id ).all() for dept_assoc in course_dept_associations: ctx.session.delete(dept_assoc) # 8. Finally, delete the course itself ctx.session.delete(course) deleted_counts["courses"] += 1 current_app.logger.info(f"Successfully deleted course: {course.title}") # Commit all changes ctx.session.commit() current_app.logger.info(f"Successfully deleted {deleted_counts['courses']} courses from {department_name} department") return custom_response( success=True, data={ "message": f"Successfully deleted all courses from {department_name} department", "department": department_name, "deleted_items": deleted_counts, "dry_run": False }, status_code=200 ) except Exception as e: ctx.session.rollback() current_app.logger.error(f"Error deleting courses from {department_name} department: {str(e)}", exc_info=True) return custom_response( success=False, data=f"Failed to delete courses from {department_name} department due to server error: {str(e)}", status_code=500 ) def unclaim_supervisor_from_course(self, course_id, supervisor_id, requester_id): """ Remove supervisor from teaching a course by removing the supervisor_course_association entry. This allows the course to be assigned to other instructors. Args: course_id: ID of the course to unclaim supervisor_id: ID of the supervisor to remove from teaching requester_id: ID of the user making the request Returns: Response with success/error status """ with DatabaseContextManager() as ctx: # Verify course exists 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 ) # 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 ) # Verify requester is the same supervisor trying to unclaim if requester_id != supervisor_id: return custom_response( success=False, data="Unauthorized - You can only unclaim your own courses", status_code=403 ) # Check if supervisor is assigned to this course via supervisor_course_association existing_assignment = ctx.session.query(supervisor_course_association).filter( supervisor_course_association.c.course_id == course_id, supervisor_course_association.c.supervisor_id == supervisor_id ).first() if not existing_assignment: return custom_response( success=False, data="Supervisor is not assigned to teach this course", status_code=404 ) try: # Remove the supervisor from supervisor_course_association ctx.session.execute( supervisor_course_association.delete().where( and_( supervisor_course_association.c.course_id == course_id, supervisor_course_association.c.supervisor_id == supervisor_id ) ) ) # Also clear the course's supervisor_id if it matches if course.supervisor_id == supervisor_id: course.supervisor_id = None ctx.session.commit() return custom_response( success=True, data={ "message": "Successfully unclaimed course", "course_id": course_id, "course_code": course.code, "course_title": course.title, "supervisor_id": supervisor_id, "supervisor_name": f"{supervisor.first_name} {supervisor.last_name}" }, status_code=200 ) except Exception as e: ctx.session.rollback() current_app.logger.error(f"Error unclaiming course: {str(e)}", exc_info=True) return custom_response( success=False, data="Failed to unclaim course due to server error", status_code=500 ) def get_supervisor_teaching_courses(self, supervisor_id, page=1, per_page=10, search="", status_filter="all"): """ Get list of courses where the supervisor is teaching (based on supervisor_course_association). This is different from managed courses - these are courses the supervisor actively teaches. """ 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 ) # Query courses where supervisor is teaching (via supervisor_course_association) query = ctx.session.query(Course).join( supervisor_course_association, Course.id == supervisor_course_association.c.course_id ).filter( supervisor_course_association.c.supervisor_id == supervisor_id, Course.is_active == True ) # Apply search filter if search: search_term = f"%{search}%" query = query.filter( (Course.title.ilike(search_term)) | (Course.code.ilike(search_term)) | (Course.department.ilike(search_term)) ) # Apply status filter if status_filter != "all": if status_filter == "active": query = query.filter(Course.is_active == True, Course.is_archived == False) elif status_filter == "archived": query = query.filter(Course.is_archived == True) elif status_filter == "inactive": query = query.filter(Course.is_active == False, Course.is_archived == False) # Pagination total = query.count() courses = query.order_by(Course.code).offset((page - 1) * per_page).limit(per_page).all() return custom_response( success=True, data={ "courses": [{ 'id': c.id, 'code': c.code, 'title': c.title, 'department': c.department, 'semester': c.semester, 'enrolled_students': ctx.session.query(func.count(func.distinct(Enrollment.student_id))).join( enrollment_courses, Enrollment.id == enrollment_courses.c.enrollment_id ).filter( enrollment_courses.c.course_id == c.id, enrollment_courses.c.status == 'active' ).scalar() or 0, 'tutors': [{ 'id': t.id, 'name': f"{t.first_name} {t.last_name}", 'is_primary': self._is_primary_tutor(ctx, c.id, t.id) } for t in c.tutors], "description": c.description, "credits": c.credits, "is_active": c.is_active, "max_students": c.max_students, "created_at": c.created_at, "learning_outcomes": c.learning_outcomes, "required_materials": c.required_materials, "assessment_method": c.assessment_method, "syllabus": c.syllabus, "total_hours": c.total_hours, "is_archived": c.is_archived, "archive_date": c.archive_date, "course_level": c.course_level, "language": c.language, "certification_available": c.certification_available, "has_practical": c.has_practical, "is_shared_course": c.is_shared_course, "shared_course_type": c.shared_course_type, "sharing_level": c.sharing_level, "shared_specialities": [{ 'id': s.id, 'name': s.name, 'department': s.department, 'description': s.description, 'code': s.code, 'abbreviation': s.abbreviation } for s in c.shared_specialities], "shared_departments": [{ 'department_name': cd.department_name, 'is_primary_department': cd.is_primary_department, 'assigned_date': str(cd.assigned_date), 'assigned_by': cd.assigned_by, 'notes': cd.notes, 'is_active': cd.is_active } for cd in c.shared_departments if cd.is_active], "sharing_capable_specialities": [{ 'id': s.id, 'name': s.name, 'department': s.department, 'description': s.description, 'code': s.code, 'abbreviation': s.abbreviation } for s in c.shared_specialities], "academic_session_id": c.academic_session_id, "academic_session": { 'id': c.academic_session.id, 'name': c.academic_session.name, 'year': c.academic_session.year, 'start_date': c.academic_session.start_date.isoformat() if c.academic_session.start_date else None, 'end_date': c.academic_session.end_date.isoformat() if c.academic_session.end_date else None, 'status': c.academic_session.status } if c.academic_session else None, "status": "active" if c.is_active and not c.is_archived else "archived" if c.is_archived else "inactive", "progress": 0, # Placeholder - calculate actual progress if needed "assignmentsDue": 0, # Placeholder - calculate if needed "attendanceRate": 0, # Placeholder - calculate if needed "lastUpdated": c.updated_at.isoformat() if hasattr(c, 'updated_at') and c.updated_at else c.created_at.isoformat() } for c in courses], "total": total, "page": page, "per_page": per_page, "supervisor_info": { "id": supervisor.id, "name": f"{supervisor.first_name} {supervisor.last_name}", "email": supervisor.email, "staff_id": supervisor.staff_id, "teaching_courses_count": total } }, status_code=200 ) def fetchAll(self, user_id, user_type, page=1, per_page=10, search="", status_filter="all"): """ Get list of courses with role-specific filtering. """ with DatabaseContextManager() as ctx: query = ctx.session.query(Course).filter(Course.is_active == True) # Apply search filter if search: search_term = f"%{search}%" query = query.filter( (Course.title.ilike(search_term)) | (Course.code.ilike(search_term)) | (Course.department.ilike(search_term)) ) # Apply status filter if status_filter != "all": if status_filter == "active": query = query.filter(Course.is_active == True, Course.is_archived == False) elif status_filter == "archived": query = query.filter(Course.is_archived == True) elif status_filter == "inactive": query = query.filter(Course.is_active == False, Course.is_archived == False) # Apply role-specific filters if user_type == UserType.student.value: # Get student's speciality and filter courses by that speciality student = ctx.session.query(Student).filter(Student.id == user_id).first() if student: query = query.filter(Course.speciality_id == student.speciality_id) else: # Return empty query if student not found query = query.filter(Course.id == None) elif user_type == UserType.tutor.value: query = query.join( tutor_course_association, Course.id == tutor_course_association.c.course_id ).filter( tutor_course_association.c.tutor_id == user_id ) elif user_type == UserType.supervisor.value: # Get supervisor's primary department supervisor = ctx.session.query(Supervisor).filter(Supervisor.id == user_id).first() if supervisor: primary_department = supervisor.get_primary_department() if primary_department: # Show all courses in the supervisor's department, not just the ones they created query = query.filter(Course.department == primary_department) else: # If no primary department, fall back to showing only courses they created query = query.filter(Course.supervisor_id == user_id) else: # If supervisor not found, show only courses they created query = query.filter(Course.supervisor_id == user_id) # Pagination total = query.count() courses = query.order_by(Course.code).offset((page - 1) * per_page).limit(per_page).all() # Calculate department-wide summary statistics (for supervisors) department_summary = None if user_type == UserType.supervisor.value: supervisor = ctx.session.query(Supervisor).filter(Supervisor.id == user_id).first() if supervisor: primary_department = supervisor.get_primary_department() if primary_department: # Get all courses in the department for summary calculations dept_query = ctx.session.query(Course).filter( Course.is_active == True, Course.department == primary_department ) total_dept_courses = dept_query.count() active_dept_courses = dept_query.filter(Course.is_active == True).count() # Calculate total students in department through enrollment association total_dept_students = ctx.session.query(func.count(func.distinct(Enrollment.student_id))).join( enrollment_courses, Enrollment.id == enrollment_courses.c.enrollment_id ).join( Course, enrollment_courses.c.course_id == Course.id ).filter( Course.department == primary_department, enrollment_courses.c.status == 'active' ).scalar() or 0 # Calculate average rating in department (if rating field exists) # Note: Course model doesn't have a rating field, so we'll skip this for now avg_dept_rating = 'N/A' department_summary = { 'total_courses': total_dept_courses, 'active_courses': active_dept_courses, 'total_students': total_dept_students, 'average_rating': avg_dept_rating } return custom_response( success=True, data={ "courses": [{ 'id': c.id, 'code': c.code, 'title': c.title, 'department': c.department, 'semester': c.semester, 'enrolled_students': ctx.session.query(func.count(func.distinct(Enrollment.student_id))).join( enrollment_courses, Enrollment.id == enrollment_courses.c.enrollment_id ).filter( enrollment_courses.c.course_id == c.id, enrollment_courses.c.status == 'active' ).scalar() or 0, 'tutors': [f"{t.first_name} {t.last_name}" for t in c.tutors], "description" : c.description, "credits" : c.credits, "is_active" : c.is_active, "max_students" : c.max_students, "created_at" : c.created_at, "learning_outcomes" : c.learning_outcomes, "required_materials" : c.required_materials, "assessment_method" : c.assessment_method, "syllabus" : c.syllabus, "total_hours" : c.total_hours, "is_archived" : c.is_archived, "archive_date" : c.archive_date, "course_level" : c.course_level, "language" : c.language, "certification_available" : c.certification_available, "has_practical" : c.has_practical, "is_shared_course": c.is_shared_course, "shared_course_type": c.shared_course_type, "sharing_level": c.sharing_level, "shared_specialities": [{ 'id': s.id, 'name': s.name, 'department': s.department, 'description': s.description, 'code': s.code, 'abbreviation': s.abbreviation } for s in c.shared_specialities], "shared_departments": [{ 'department_name': cd.department_name, 'is_primary_department': cd.is_primary_department, 'assigned_date': str(cd.assigned_date), 'assigned_by': cd.assigned_by, 'notes': cd.notes, 'is_active': cd.is_active } for cd in c.shared_departments if cd.is_active], "sharing_capable_specialities": [{ 'id': s.id, 'name': s.name, 'department': s.department, 'description': s.description, 'code': s.code, 'abbreviation': s.abbreviation } for s in c.shared_specialities], # Note: Currently using shared_specialities, but this should be a separate relationship for sharing-capable specialities "academic_session_id": c.academic_session_id, "academic_session": { 'id': c.academic_session.id, 'name': c.academic_session.name, 'year': c.academic_session.year, 'start_date': c.academic_session.start_date.isoformat() if c.academic_session.start_date else None, 'end_date': c.academic_session.end_date.isoformat() if c.academic_session.end_date else None, 'status': c.academic_session.status } if c.academic_session else None, # Computed status field for frontend compatibility "status": "active" if c.is_active and not c.is_archived else "archived" if c.is_archived else "inactive", # Enhanced supervisor information 'supervisor': self._get_comprehensive_supervisor_info(ctx, c.supervisor_id) if c.supervisor_id else None, 'supervisor_status': 'assigned' if c.supervisor_id else 'unassigned', 'supervisor_availability': self._get_supervisor_availability_status(ctx, c.supervisor_id) if c.supervisor_id else None } for c in courses], "total": total, "page": page, "per_page": per_page, "department_summary": department_summary }, status_code=200 ) def get_course_analytics(self, course_id, requester_id): """ Get comprehensive analytics for a course. Only accessible by course supervisor or department head. Returns data structured for the frontend dashboard. """ with DatabaseContextManager() as ctx: 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 ) # Verify requester is the course supervisor or department head requester = ctx.session.query(Supervisor).filter( Supervisor.id == requester_id, or_( Supervisor.id == course.supervisor_id, Supervisor.is_head_of_department == True ) ).first() if not requester: return custom_response( success=False, data="Unauthorized - Only course supervisor or department head can view analytics", status_code=403 ) # Get basic course info course_info = { 'code': course.code, 'title': course.title, 'semester': course.semester, 'credits': course.credits, 'supervisor': f"{course.supervisor.first_name} {course.supervisor.last_name}", 'department': course.department, 'totalStudents': ctx.session.query(Student).join( Speciality, Student.speciality_id == Speciality.id ).filter( Speciality.id == course.speciality_id, Student.is_active == True ).count(), 'activeStudents': len([e for e in course.enrollments if e.status == 'active']), 'max_students': course.max_students, 'description': course.description, 'learning_outcomes': course.learning_outcomes, 'required_materials': course.required_materials, 'assessment_method': course.assessment_method, 'syllabus': course.syllabus, 'total_hours': course.total_hours, 'is_archived': course.is_archived, 'archive_date': course.archive_date, 'course_level': course.course_level, 'language': course.language, 'certification_available': course.certification_available } # Get attendance data for the line chart attendance_records = ctx.session.query( func.date(TeachingSession.start_time).label('date'), func.count(Attendance.id).label('total'), func.sum(case((Attendance.status == AttendanceStatus.present, 1), else_=0)).label('present'), func.sum(case((Attendance.status == AttendanceStatus.absent, 1), else_=0)).label('absent'), func.sum(case((Attendance.status == AttendanceStatus.late, 1), else_=0)).label('late') ).join( TeachingSession, Attendance.session_id == TeachingSession.id ).filter( TeachingSession.course_id == course_id ).group_by( func.date(TeachingSession.start_time) ).order_by( func.date(TeachingSession.start_time) ).all() attendance_data = [{ 'date': record.date.strftime('%Y-%m-%d'), 'present': record.present, 'absent': record.absent, 'late': record.late, 'total': record.total } for record in attendance_records] # Calculate average attendance percentage if attendance_records: total_present = sum(r.present for r in attendance_records) total_attendance = sum(r.total for r in attendance_records) course_info['avgAttendance'] = round((total_present / total_attendance) * 100, 1) if total_attendance else 0 else: course_info['avgAttendance'] = 0 # Get grade distribution for the pie chart grade_ranges = [ ('A (90-100)', 90, 100), ('B (80-89)', 80, 89), ('C (70-79)', 70, 79), ('D (60-69)', 60, 69), ('F (0-59)', 0, 59) ] grade_distribution = [] for label, min_score, max_score in grade_ranges: count = ctx.session.query(enrollment_courses).join( Enrollment, enrollment_courses.c.enrollment_id == Enrollment.id ).filter( enrollment_courses.c.course_id == course_id, enrollment_courses.c.grade.between(min_score, max_score) ).count() grade_distribution.append({ 'name': label, 'value': count }) # Calculate average grade avg_grade = ctx.session.query( func.avg(enrollment_courses.c.grade) ).join( Enrollment, enrollment_courses.c.enrollment_id == Enrollment.id ).filter( enrollment_courses.c.course_id == course_id ).scalar() course_info['avgGrade'] = round(avg_grade, 1) if avg_grade else 0 # Get student performance data for the table students = ctx.session.query( Student, Enrollment, enrollment_courses, func.avg(AssignmentSubmission.grade).label('avg_score'), func.sum(case((Attendance.status == AttendanceStatus.present, 1), else_=0)).label('present_count'), func.count(Attendance.id).label('attendance_count') ).join( Enrollment, Enrollment.student_id == Student.id ).join( enrollment_courses, enrollment_courses.c.enrollment_id == Enrollment.id ).outerjoin( AssignmentSubmission, AssignmentSubmission.student_id == Student.id ).outerjoin( Attendance, Attendance.student_id == Student.id ).filter( enrollment_courses.c.course_id == course_id, enrollment_courses.c.status == 'active' ).group_by( Student.id, Enrollment.id, enrollment_courses.c.enrollment_id ).all() student_performance = [] for student, enrollment, enrollment_course, avg_score, present_count, attendance_count in students: attendance_percentage = round((present_count / attendance_count * 100), 1) if attendance_count else 0 # Determine performance status if avg_score is None: status = "Not Started" elif avg_score >= 90: status = "Excellent" elif avg_score >= 80: status = "Good" elif avg_score >= 70: status = "Average" else: status = "At Risk" student_performance.append({ 'id': student.id, 'name': f"{student.first_name} {student.last_name}", 'attendance': attendance_percentage, 'avgScore': round(avg_score, 1) if avg_score else 0, 'progress': enrollment.progress.completion_percentage if enrollment.progress else 0, 'status': status }) # Get assignment performance for the line chart assignments = ctx.session.query( Assignment, func.avg(AssignmentSubmission.grade).label('avg_score'), func.max(AssignmentSubmission.grade).label('max_score') ).outerjoin( AssignmentSubmission, AssignmentSubmission.assignment_id == Assignment.id ).filter( Assignment.course_id == course_id ).group_by( Assignment.id ).order_by( Assignment.due_date ).all() assignment_performance = [{ 'name': a.title[:20] + ('...' if len(a.title) > 20 else ''), 'fullName': a.title, 'avgScore': round(avg, 1) if avg else 0, 'maxScore': round(max_score, 1) if max_score else 0, 'dueDate': a.due_date.isoformat() if a.due_date else None } for a, avg, max_score in assignments] # Get skill assessment for the radar chart skill_assessment = [ {'subject': 'Knowledge', 'A': random.randint(70, 90)}, {'subject': 'Application', 'A': random.randint(65, 85)}, {'subject': 'Analysis', 'A': random.randint(60, 80)}, {'subject': 'Synthesis', 'A': random.randint(55, 75)}, {'subject': 'Evaluation', 'A': random.randint(50, 70)}, {'subject': 'Creativity', 'A': random.randint(45, 65)} ] return custom_response( success=True, data={ 'courseInfo': course_info, 'attendanceData': attendance_data, 'gradeDistribution': grade_distribution, 'studentPerformance': student_performance, 'assignmentPerformance': assignment_performance, 'skillAssessment': skill_assessment }, status_code=200 ) def assign_supervisor_to_course(self, course_id, supervisor_id, requester_id): """ Assign a supervisor to a course with validation. Only department heads or existing course supervisors can assign supervisors. Args: course_id: ID of the course supervisor_id: ID of the supervisor to assign requester_id: ID of the user making the request (must be department head or current supervisor) Returns: Response with success/error status """ with DatabaseContextManager() as ctx: # Verify course exists and is active course = ctx.session.query(Course).filter( Course.id == course_id, Course.is_active == True ).first() if not course: return custom_response( success=False, data="Course not found or inactive", status_code=404 ) # Verify supervisor exists and is active 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 or inactive", status_code=404 ) # Verify requester has permission to assign supervisors requester = ctx.session.query(Supervisor).filter( Supervisor.id == requester_id, Supervisor.is_active == True ).first() if not requester: return custom_response( success=False, data="Unauthorized - Only supervisors can assign supervisors to courses", status_code=403 ) # Check if requester is department head or current course supervisor is_department_head = requester.is_head_of_department is_current_supervisor = course.supervisor_id == requester_id if not (is_department_head or is_current_supervisor): return custom_response( success=False, data="Unauthorized - Only department heads or current course supervisors can assign supervisors", status_code=403 ) # Check if supervisor is already assigned to this course if course.supervisor_id == supervisor_id: return custom_response( success=False, data="Supervisor is already assigned to this course", status_code=400 ) # Check if supervisor has reached maximum course limit (if applicable) if hasattr(supervisor, 'max_courses') and supervisor.max_courses: current_courses = ctx.session.query(Course).filter( Course.supervisor_id == supervisor_id, Course.is_active == True ).count() if current_courses >= supervisor.max_courses: return custom_response( success=False, data=f"Supervisor has reached maximum course limit ({supervisor.max_courses})", status_code=400 ) try: # Update the course with new supervisor old_supervisor_id = course.supervisor_id course.supervisor_id = supervisor_id course.updated_at = datetime.utcnow() ctx.session.commit() # Get comprehensive supervisor information for the response comprehensive_supervisor_info = self._get_comprehensive_supervisor_info(ctx, supervisor_id) return custom_response( success=True, data={ "message": "Supervisor assigned to course successfully", "course_id": course_id, "course_code": course.code, "course_title": course.title, "old_supervisor_id": old_supervisor_id, "new_supervisor_id": supervisor_id, "new_supervisor_name": f"{supervisor.first_name} {supervisor.last_name}", "assigned_by": requester_id, "assigned_at": datetime.utcnow().isoformat(), # Enhanced supervisor information for immediate display "supervisor": comprehensive_supervisor_info, "supervisor_status": "assigned", "supervisor_availability": self._get_supervisor_availability_status(ctx, supervisor_id), "assignment_summary": { "status": "success", "workload_impact": comprehensive_supervisor_info.get('workload', {}).get('workload_percentage', 0) if comprehensive_supervisor_info else 0, "availability_status": comprehensive_supervisor_info.get('workload', {}).get('availability_status', 'Unknown') if comprehensive_supervisor_info else 'Unknown', "can_take_more_courses": comprehensive_supervisor_info.get('display', {}).get('is_available_for_new_courses', True) if comprehensive_supervisor_info else True } }, status_code=200 ) except Exception as e: ctx.session.rollback() current_app.logger.error(f"Error assigning supervisor to course: {str(e)}", exc_info=True) return custom_response( success=False, data="Failed to assign supervisor to course due to server error", status_code=500 ) def get_course_supervisor(self, course_id): """ Get the current supervisor assigned to a course. Args: course_id: ID of the course Returns: Response with supervisor details """ with DatabaseContextManager() as ctx: # Verify course exists 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 ) if not course.supervisor_id: return custom_response( success=False, data="No supervisor assigned to this course", status_code=404 ) # Get supervisor details supervisor = ctx.session.query(Supervisor).filter( Supervisor.id == course.supervisor_id ).first() if not supervisor: return custom_response( success=False, data="Assigned supervisor not found", status_code=404 ) # Get supervisor's current course count current_courses = ctx.session.query(Course).filter( Course.supervisor_id == supervisor.id, Course.is_active == True ).count() supervisor_data = { 'id': supervisor.id, 'first_name': supervisor.first_name, 'last_name': supervisor.last_name, 'email': supervisor.email, 'staff_id': supervisor.staff_id, 'department': supervisor.get_primary_department() if hasattr(supervisor, 'get_primary_department') else 'Not assigned', 'qualification': supervisor.qualification, 'specialization': supervisor.specialization, 'office_location': supervisor.office_location, 'is_head_of_department': supervisor.is_head_of_department, 'years_of_experience': supervisor.years_of_experience, 'current_courses': current_courses, 'max_courses': getattr(supervisor, 'max_courses', None), 'full_name': f"{supervisor.first_name} {supervisor.last_name}", 'display_name': f"{supervisor.first_name} {supervisor.last_name} ({supervisor.staff_id})" } return custom_response( success=True, data={ 'course_id': course_id, 'course_code': course.code, 'course_title': course.title, 'supervisor': supervisor_data, 'assigned_at': str(course.created_at), 'last_updated': str(course.updated_at) if hasattr(course, 'updated_at') else None }, status_code=200 ) def get_supervisor_courses(self, supervisor_id, include_inactive=False): """ Get all courses assigned to a specific supervisor. Args: supervisor_id: ID of the supervisor include_inactive: Whether to include inactive/archived courses Returns: Response with list of courses """ 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 ) # Build query query = ctx.session.query(Course).filter(Course.supervisor_id == supervisor_id) if not include_inactive: query = query.filter(Course.is_active == True) courses = query.order_by(Course.code).all() courses_data = [] for course in courses: # Get student count through enrollment association student_count = ctx.session.query(func.count(func.distinct(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' ).scalar() or 0 # Get tutor count tutor_count = ctx.session.query(tutor_course_association).filter( tutor_course_association.c.course_id == course.id ).count() courses_data.append({ 'id': course.id, 'code': course.code, 'title': course.title, 'department': course.department, 'semester': course.semester, 'credits': course.credits, 'max_students': course.max_students, 'current_students': student_count, 'tutors_count': tutor_count, 'is_active': course.is_active, 'is_archived': getattr(course, 'is_archived', False), 'is_shared_course': course.is_shared_course, 'shared_course_type': course.shared_course_type, 'sharing_level': course.sharing_level, 'shared_specialities': [{ 'id': s.id, 'name': s.name, 'department': s.department, 'description': s.description, 'code': s.code, 'abbreviation': s.abbreviation } for s in course.shared_specialities], 'shared_departments': [{ 'department_name': cd.department_name, 'is_primary_department': cd.is_primary_department, 'assigned_date': str(cd.assigned_date), 'assigned_by': cd.assigned_by, 'notes': cd.notes, 'is_active': cd.is_active } for cd in course.shared_departments if cd.is_active], 'created_at': str(course.created_at), 'updated_at': str(course.updated_at) if hasattr(course, 'updated_at') else None }) return custom_response( success=True, data={ 'supervisor_id': supervisor_id, 'supervisor_name': f"{supervisor.first_name} {supervisor.last_name}", 'supervisor_email': supervisor.email, 'supervisor_department': supervisor.department, 'courses': courses_data, 'total_courses': len(courses_data), 'active_courses': len([c for c in courses_data if c['is_active']]), 'archived_courses': len([c for c in courses_data if c.get('is_archived', False)]) }, status_code=200 ) def assign_tutor_to_course(self, course_id, tutor_id, requester_id, is_primary=False): """ Assign a tutor to a course with validation and notifications. Only the course supervisor can assign tutors. Args: course_id: ID of the course tutor_id: ID of the tutor to assign requester_id: ID of the supervisor making the request is_primary: Whether this tutor should be the primary tutor Returns: Response with success/error status """ with DatabaseContextManager() as ctx: # Verify course exists and is active course = ctx.session.query(Course).filter( Course.id == course_id, Course.is_active == True ).first() if not course: return custom_response( success=False, data="Course not found or inactive", status_code=404 ) # Verify tutor exists and is active 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 or inactive", status_code=404 ) # Verify requester is the course supervisor if course.supervisor_id != requester_id: return custom_response( success=False, data="Unauthorized - Only the course supervisor can assign tutors", status_code=403 ) # Check if tutor is already assigned to this course existing_assignment = ctx.session.query(tutor_course_association).filter( tutor_course_association.c.course_id == course_id, tutor_course_association.c.tutor_id == tutor_id ).first() is_reassignment = False previous_tutors = [] try: if existing_assignment: # Update existing assignment is_reassignment = True previous_tutors = [t.id for t in course.tutors] ctx.session.execute( tutor_course_association.update().where( tutor_course_association.c.course_id == course_id, tutor_course_association.c.tutor_id == tutor_id ).values(is_primary=is_primary) ) else: # Create new assignment stmt = tutor_course_association.insert().values( tutor_id=tutor_id, course_id=course_id, is_primary=is_primary ) ctx.session.execute(stmt) tutor.supervisor_id = requester_id ctx.session.commit() # Get updated course data for notifications course = ctx.session.query(Course).filter(Course.id == course_id).first() current_tutors = [t.id for t in course.tutors] # Prepare notification data notification_data = { "course_id": course.id, "course_code": course.code, "course_title": course.title, "tutor_id": tutor_id, "tutor_name": f"{tutor.first_name} {tutor.last_name}", "is_primary": is_primary, "is_reassignment": is_reassignment, "previous_tutors": previous_tutors, "current_tutors": current_tutors, "supervisor_id": course.supervisor_id, "supervisor_name": f"{course.supervisor.first_name} {course.supervisor.last_name}", "students": [s.id for s in ctx.session.query(Student).join( Speciality, Student.speciality_id == Speciality.id ).filter( Speciality.id == course.speciality_id, Student.is_active == True ).all()] } # Send notifications (implementation would depend on your notification system) self._send_tutor_assignment_notifications(notification_data) return custom_response( success=True, data={ "message": "Tutor assigned successfully", "is_reassignment": is_reassignment, "tutor_id": tutor_id, "is_primary": is_primary }, status_code=200 ) except Exception as e: ctx.session.rollback() current_app.logger.error(f"Error assigning tutor to course: {str(e)}", exc_info=True) return custom_response( success=False, data="Failed to assign tutor due to server error", status_code=500 ) def _send_tutor_assignment_notifications(self, data): """ Internal method to handle sending notifications about tutor assignments. This would be implemented based on your notification system. """ try: # Notification to the assigned tutor tutor_message = ( f"You have been {'re' if data['is_reassignment'] else ''}assigned " f"as a {'primary ' if data['is_primary'] else ''}tutor for course " f"{data['course_code']} - {data['course_title']}." ) # Notification to the course supervisor supervisor_message = ( f"Tutor {data['tutor_name']} has been {'re' if data['is_reassignment'] else ''}assigned " f"as a {'primary ' if data['is_primary'] else ''}tutor for your course " f"{data['course_code']} - {data['course_title']}." ) # Notification to students student_message = ( f"A {'new ' if not data['is_reassignment'] else 'replacement '}tutor " f"{data['tutor_name']} has been assigned to your course " f"{data['course_code']} - {data['course_title']}." ) # Notification to other tutors if this is a reassignment if data['is_reassignment']: for prev_tutor_id in data['previous_tutors']: if prev_tutor_id != data['tutor_id']: prev_tutor_message = ( f"Your assignment as tutor for course {data['course_code']} " f"has been updated. Tutor {data['tutor_name']} is now " f"{'the primary tutor' if data['is_primary'] else 'a tutor'}." ) # Send to previous tutor (implementation would go here) # In a real implementation, you would: # 1. Create notification records in the database # 2. Trigger email/SMS/push notifications # 3. Possibly add to activity feeds except Exception as e: current_app.logger.error(f"Error sending tutor assignment notifications: {str(e)}", exc_info=True) def assign_supervisor_to_course(self, course_id, supervisor_id, requester_id): """ Assign a supervisor to a course using the supervisor_course_association table. Supervisors can assign themselves to courses that have no tutors assigned. Args: course_id: ID of the course supervisor_id: ID of the supervisor to assign (can be the same as requester_id) requester_id: ID of the supervisor making the request Returns: Response with success/error status """ with DatabaseContextManager() as ctx: # Verify course exists and is active course = ctx.session.query(Course).filter( Course.id == course_id, Course.is_active == True ).first() if not course: return custom_response( success=False, data="Course not found or inactive", status_code=404 ) # Verify supervisor exists and is active 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 or inactive", status_code=404 ) # Verify requester is a supervisor requester = ctx.session.query(Supervisor).filter( Supervisor.id == requester_id, Supervisor.is_active == True ).first() if not requester: return custom_response( success=False, data="Unauthorized - Only supervisors can assign courses", status_code=403 ) # Check if supervisor is already assigned to this course existing_assignment = ctx.session.query(supervisor_course_association).filter( supervisor_course_association.c.course_id == course_id, supervisor_course_association.c.supervisor_id == supervisor_id ).first() if existing_assignment: return custom_response( success=False, data="Supervisor is already assigned to this course", status_code=409 ) # Check if course already has tutors assigned existing_tutors = ctx.session.query(tutor_course_association).filter( tutor_course_association.c.course_id == course_id ).all() if existing_tutors: return custom_response( success=False, data="Course already has tutors assigned. Cannot assign supervisor.", status_code=409 ) # Check if course already has a supervisor assigned by checking the association table existing_supervisor_assignment = ctx.session.query(supervisor_course_association).filter( supervisor_course_association.c.course_id == course_id ).first() if existing_supervisor_assignment: return custom_response( success=False, data="Course already has a supervisor assigned", status_code=409 ) try: # Create new assignment stmt = supervisor_course_association.insert().values( supervisor_id=supervisor_id, course_id=course_id, is_primary=True # Supervisor becomes primary instructor ) ctx.session.execute(stmt) # Update the course's supervisor_id field course.supervisor_id = supervisor_id ctx.session.commit() return custom_response( success=True, data={ "message": "Supervisor assigned successfully", "supervisor_id": supervisor_id, "course_id": course_id, "is_primary": True }, status_code=200 ) except Exception as e: ctx.session.rollback() current_app.logger.error(f"Error assigning supervisor to course: {str(e)}", exc_info=True) return custom_response( success=False, data="Failed to assign supervisor due to server error", status_code=500 ) def can_supervisor_claim_course(self, course_id, supervisor_id): """ Check if a supervisor can claim a specific course. Args: course_id: ID of the course supervisor_id: ID of the supervisor Returns: Response with claimability status and reasons """ with DatabaseContextManager() as ctx: # Verify course exists and is active course = ctx.session.query(Course).filter( Course.id == course_id, Course.is_active == True ).first() if not course: return custom_response( success=False, data="Course not found or inactive", status_code=404 ) # Verify supervisor exists and is active 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 or inactive", status_code=404 ) # Check if supervisor is already assigned to this course existing_assignment = ctx.session.query(supervisor_course_association).filter( supervisor_course_association.c.course_id == course_id, supervisor_course_association.c.supervisor_id == supervisor_id ).first() if existing_assignment: return custom_response( success=True, data={ "can_claim": False, "reason": "Already assigned to this course", "status": "already_assigned" }, status_code=200 ) # Check if course already has tutors assigned existing_tutors = ctx.session.query(tutor_course_association).filter( tutor_course_association.c.course_id == course_id ).all() if existing_tutors: return custom_response( success=True, data={ "can_claim": False, "reason": "Course already has tutors assigned", "status": "has_tutors" }, status_code=200 ) # Check if course already has a supervisor assigned by checking the association table existing_supervisor_assignment = ctx.session.query(supervisor_course_association).filter( supervisor_course_association.c.course_id == course_id ).first() if existing_supervisor_assignment: return custom_response( success=True, data={ "can_claim": False, "reason": "Course already has a supervisor assigned", "status": "has_supervisor", "debug_info": { "supervisor_id": existing_supervisor_assignment.supervisor_id, "course_id": existing_supervisor_assignment.course_id, "assignment_date": str(existing_supervisor_assignment.assigned_at) if hasattr(existing_supervisor_assignment, 'assigned_at') else None } }, status_code=200 ) # Course can be claimed return custom_response( success=True, data={ "can_claim": True, "reason": "Course is available for claiming", "status": "available", "debug_info": { "course_supervisor_id": course.supervisor_id, "course_is_active": course.is_active, "course_has_tutors": len(existing_tutors) > 0, "supervisor_association_exists": False, "course_details": { "id": course.id, "code": course.code, "title": course.title, "department": course.department } } }, status_code=200 ) def endpoint_assign_supervisor_to_course(self, request_data): """ Endpoint function to handle supervisor assignment to courses. This function can be called directly from API routes. Args: request_data: Dictionary containing: - course_id: ID of the course - supervisor_id: ID of the supervisor to assign - requester_id: ID of the user making the request - action: Action to perform ('assign', 'get', 'list', 'check_claim') Returns: Response with success/error status and data """ try: # Extract required fields course_id = request_data.get('course_id') supervisor_id = request_data.get('supervisor_id') requester_id = request_data.get('requester_id') action = request_data.get('action', 'assign') # Validate required fields based on action if action == 'assign': if not all([course_id, supervisor_id, requester_id]): return custom_response( success=False, data="Missing required fields: course_id, supervisor_id, and requester_id are required for assignment", status_code=400 ) # Call the assignment function return self.assign_supervisor_to_course(course_id, supervisor_id, requester_id) elif action == 'get': if not course_id: return custom_response( success=False, data="Missing required field: course_id is required to get supervisor information", status_code=400 ) # Call the get supervisor function return self.get_course_supervisor(course_id) elif action == 'list': if not supervisor_id: return custom_response( success=False, data="Missing required field: supervisor_id is required to list courses", status_code=400 ) include_inactive = request_data.get('include_inactive', False) # Call the list courses function return self.get_supervisor_courses(supervisor_id, include_inactive) elif action == 'check_claim': if not all([course_id, supervisor_id]): return custom_response( success=False, data="Missing required fields: course_id and supervisor_id are required to check claimability", status_code=400 ) # Call the claim check function return self.can_supervisor_claim_course(course_id, supervisor_id) elif action == 'debug': if not course_id: return custom_response( success=False, data="Missing required field: course_id is required for debugging", status_code=400 ) # Call the debug function return self.debug_course_supervisor_status(course_id) elif action == 'fix_inconsistencies': if not course_id: return custom_response( success=False, data="Missing required field: course_id is required for fixing inconsistencies", status_code=400 ) # Call the fix inconsistencies function return self.fix_course_supervisor_inconsistencies(course_id) else: return custom_response( success=False, data=f"Invalid action '{action}'. Valid actions are: 'assign', 'get', 'list', 'check_claim', 'debug', 'fix_inconsistencies'", status_code=400 ) except Exception as e: current_app.logger.error(f"Error in supervisor assignment endpoint: {str(e)}", exc_info=True) return custom_response( success=False, data=f"Endpoint error: {str(e)}", status_code=500 ) def get_speciality_detailed_view(self, speciality_id, requester_id=None, requester_type=None): """ Get detailed view of a speciality including all courses and supervisor assignments. This function provides comprehensive information about a speciality and its courses. Args: speciality_id: ID of the speciality to view requester_id: ID of the user requesting the view (optional) requester_type: Type of user making the request (optional) Returns: Response with speciality details, courses, and supervisor information """ with DatabaseContextManager() as ctx: try: # Verify speciality exists speciality = ctx.session.query(Speciality).filter(Speciality.id == speciality_id).first() if not speciality: return custom_response( success=False, data="Speciality not found", status_code=404 ) # Get all courses for this speciality courses = ctx.session.query(Course).filter( Course.speciality_id == speciality_id, Course.is_active == True ).order_by(Course.code).all() courses_data = [] total_students = 0 total_tutors = 0 courses_with_supervisors = 0 courses_without_supervisors = 0 for course in courses: # Get student count through enrollment association student_count = ctx.session.query(func.count(func.distinct(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' ).scalar() or 0 # Get tutor count tutor_count = ctx.session.query(tutor_course_association).filter( tutor_course_association.c.course_id == course.id ).count() # Get comprehensive assignment information assignment_info = self._get_course_assignment_info(ctx, course.id) if assignment_info['has_supervisor']: courses_with_supervisors += 1 else: courses_without_supervisors += 1 # Get tutor details tutors = [] if tutor_count > 0: tutor_assignments = ctx.session.query(tutor_course_association).filter( tutor_course_association.c.course_id == course.id ).all() for assignment in tutor_assignments: tutor = ctx.session.query(Tutor).filter(Tutor.id == assignment.tutor_id).first() if tutor: tutors.append({ 'id': tutor.id, 'name': f"{tutor.first_name} {tutor.last_name}", 'email': tutor.email, 'staff_id': tutor.staff_id, 'is_primary': assignment.is_primary }) course_data = { 'id': course.id, 'code': course.code, 'title': course.title, 'description': course.description, 'credits': course.credits, 'semester': course.semester, 'department': course.department, 'max_students': course.max_students, 'current_students': student_count, 'tutors_count': tutor_count, 'tutors': tutors, 'assignment_info': assignment_info, 'has_supervisor': assignment_info['has_supervisor'], 'has_tutor': assignment_info['has_tutor'], 'assignment_status': assignment_info['assignment_status'], 'assigned_instructor': assignment_info['assigned_instructor'], 'can_be_claimed': assignment_info['can_be_claimed'], 'has_practical': getattr(course, 'has_practical', False), 'is_shared_course': course.is_shared_course, 'shared_course_type': course.shared_course_type, 'sharing_level': course.sharing_level, 'shared_specialities': [{ 'id': s.id, 'name': s.name, 'department': s.department, 'description': s.description, 'code': s.code, 'abbreviation': s.abbreviation } for s in course.shared_specialities], 'shared_departments': [{ 'department_name': cd.department_name, 'is_primary_department': cd.is_primary_department, 'assigned_date': str(cd.assigned_date), 'assigned_by': cd.assigned_by, 'notes': cd.notes, 'is_active': cd.is_active } for cd in course.shared_departments if cd.is_active], 'created_at': str(course.created_at), 'is_active': course.is_active } courses_data.append(course_data) total_students += student_count total_tutors += tutor_count # Get available supervisors for assignment available_supervisors = [] if requester_id and requester_type: # Only show available supervisors if user has permission if requester_type == 'supervisor': requester = ctx.session.query(Supervisor).filter(Supervisor.id == requester_id).first() if requester and (requester.is_head_of_department or requester.department == speciality.department): # Get supervisors from the same department available_supervisors = ctx.session.query(Supervisor).filter( Supervisor.department == speciality.department, Supervisor.is_active == True ).order_by(Supervisor.first_name, Supervisor.last_name).all() available_supervisors = [{ 'id': s.id, 'name': f"{s.first_name} {s.last_name}", 'email': s.email, 'staff_id': s.staff_id, 'department': s.department, 'is_head_of_department': s.is_head_of_department, 'current_courses': ctx.session.query(supervisor_course_association).filter( supervisor_course_association.c.supervisor_id == s.id ).count() } for s in available_supervisors] # Calculate statistics total_courses = len(courses) supervisor_coverage = round((courses_with_supervisors / total_courses * 100), 1) if total_courses > 0 else 0 average_students_per_course = round(total_students / total_courses, 1) if total_courses > 0 else 0 average_tutors_per_course = round(total_tutors / total_courses, 1) if total_courses > 0 else 0 # Get assignment summary for quick overview assignment_summary = [] supervisor_summary = [] data_inconsistencies = [] if courses_with_supervisors > 0: # Group courses by supervisor for summary supervisor_course_map = {} for course_data in courses_data: if course_data['has_supervisor'] and course_data['assignment_info']['supervisor_info']: supervisor_id = course_data['assignment_info']['supervisor_info']['id'] if supervisor_id not in supervisor_course_map: supervisor_course_map[supervisor_id] = { 'supervisor': course_data['assignment_info']['supervisor_info'], 'courses': [] } supervisor_course_map[supervisor_id]['courses'].append({ 'code': course_data['code'], 'title': course_data['title'], 'semester': course_data['semester'] }) supervisor_summary = [ { 'supervisor_id': sup_id, 'supervisor_name': data['supervisor']['name'], 'supervisor_email': data['supervisor']['email'], 'courses_count': len(data['courses']), 'courses': data['courses'] } for sup_id, data in supervisor_course_map.items() ] # Create comprehensive assignment summary for course_data in courses_data: assignment_summary.append({ 'course_code': course_data['code'], 'course_title': course_data['title'], 'assignment_status': course_data['assignment_status'], 'assigned_instructor': course_data['assigned_instructor'], 'can_be_claimed': course_data['can_be_claimed'] }) # Check for data inconsistencies between Course.supervisor_id and supervisor_course_association for course in courses: course_supervisor_id = course.supervisor_id association_supervisor = ctx.session.query(supervisor_course_association).filter( supervisor_course_association.c.course_id == course.id ).first() if course_supervisor_id and not association_supervisor: data_inconsistencies.append({ 'course_code': course.code, 'issue': 'Course has supervisor_id but no association record', 'supervisor_id': course_supervisor_id }) elif not course_supervisor_id and association_supervisor: data_inconsistencies.append({ 'course_code': course.code, 'issue': 'Course has no supervisor_id but has association record', 'supervisor_id': association_supervisor.supervisor_id }) elif course_supervisor_id and association_supervisor and course_supervisor_id != association_supervisor.supervisor_id: data_inconsistencies.append({ 'course_code': course.code, 'issue': 'Course supervisor_id does not match association record', 'course_supervisor_id': course_supervisor_id, 'association_supervisor_id': association_supervisor.supervisor_id }) return custom_response( success=True, data={ 'speciality': { 'id': speciality.id, 'name': speciality.name, 'description': speciality.description, 'department': speciality.department, 'created_at': str(speciality.created_at) if hasattr(speciality, 'created_at') else None }, 'courses': courses_data, 'statistics': { 'total_courses': total_courses, 'total_students': total_students, 'total_tutors': total_tutors, 'courses_with_supervisors': courses_with_supervisors, 'courses_without_supervisors': courses_without_supervisors, 'supervisor_coverage': supervisor_coverage, 'average_students_per_course': average_students_per_course, 'average_tutors_per_course': average_tutors_per_course }, 'assignment_summary': assignment_summary, 'supervisor_summary': supervisor_summary, 'data_inconsistencies': data_inconsistencies, 'has_data_issues': len(data_inconsistencies) > 0, 'available_supervisors': available_supervisors, 'supervisor_assignment_enabled': len(available_supervisors) > 0, 'message': f'Speciality {speciality.name} has {total_courses} courses with {supervisor_coverage}% supervisor coverage' }, status_code=200 ) except Exception as e: current_app.logger.error(f"Error getting speciality detailed view: {str(e)}", exc_info=True) return custom_response( success=False, data=f"Failed to get speciality detailed view: {str(e)}", status_code=500 ) def _get_course_assignment_info(self, ctx, course_id): """ Get comprehensive assignment information for a course. This function determines whether a course has tutors, supervisors, or is unassigned. Args: ctx: Database context course_id: ID of the course Returns: Dictionary with comprehensive assignment information """ # Check for supervisor assignment supervisor_assignment = ctx.session.query(supervisor_course_association).filter( supervisor_course_association.c.course_id == course_id ).first() # Check for tutor assignments tutor_assignments = ctx.session.query(tutor_course_association).filter( tutor_course_association.c.course_id == course_id ).all() assignment_info = { 'has_supervisor': False, 'has_tutor': False, 'assignment_status': 'Unassigned', 'assigned_instructor': None, 'can_be_claimed': True, 'supervisor_info': None, 'tutor_info': None } # Process supervisor assignment if supervisor_assignment: supervisor = ctx.session.query(Supervisor).filter(Supervisor.id == supervisor_assignment.supervisor_id).first() if supervisor: assignment_info['has_supervisor'] = True assignment_info['supervisor_info'] = { 'id': supervisor.id, 'name': f"{supervisor.first_name} {supervisor.last_name}", 'email': supervisor.email, 'staff_id': supervisor.staff_id, 'department': supervisor.get_primary_department() if hasattr(supervisor, 'get_primary_department') else 'Not assigned', 'is_head_of_department': supervisor.is_head_of_department, 'years_of_experience': supervisor.years_of_experience, 'assignment_details': { 'assigned_at': str(supervisor_assignment.assigned_at) if hasattr(supervisor_assignment, 'assigned_at') else None, 'is_primary': getattr(supervisor_assignment, 'is_primary', True), 'status': 'Active' } } assignment_info['assigned_instructor'] = { 'type': 'supervisor', 'id': supervisor.id, 'name': f"{supervisor.first_name} {supervisor.last_name}", 'email': supervisor.email, 'staff_id': supervisor.staff_id, 'role': 'Supervisor' } assignment_info['assignment_status'] = 'Assigned to Supervisor' assignment_info['can_be_claimed'] = False # Process tutor assignments if tutor_assignments: assignment_info['has_tutor'] = True tutors_data = [] for assignment in tutor_assignments: tutor = ctx.session.query(Tutor).filter(Tutor.id == assignment.tutor_id).first() if tutor: tutor_data = { 'id': tutor.id, 'name': f"{tutor.first_name} {tutor.last_name}", 'email': tutor.email, 'staff_id': tutor.staff_id, 'is_primary': assignment.is_primary, 'assignment_details': { 'assigned_at': str(assignment.assigned_at) if hasattr(assignment, 'assigned_at') else None, 'status': 'Active' } } tutors_data.append(tutor_data) # If this is the primary tutor and no supervisor is assigned, set as assigned instructor if assignment.is_primary and not assignment_info['assigned_instructor']: assignment_info['assigned_instructor'] = { 'type': 'tutor', 'id': tutor.id, 'name': f"{tutor.first_name} {tutor.last_name}", 'email': tutor.email, 'staff_id': tutor.staff_id, 'role': 'Primary Tutor' } assignment_info['assignment_status'] = 'Assigned to Tutor' assignment_info['can_be_claimed'] = False assignment_info['tutor_info'] = tutors_data # Determine final assignment status if assignment_info['has_supervisor'] and assignment_info['has_tutor']: assignment_info['assignment_status'] = 'Assigned to Supervisor & Tutors' elif assignment_info['has_supervisor']: assignment_info['assignment_status'] = 'Assigned to Supervisor' elif assignment_info['has_tutor']: assignment_info['assignment_status'] = 'Assigned to Tutors' else: assignment_info['assignment_status'] = 'Unassigned' assignment_info['can_be_claimed'] = True return assignment_info def _group_courses_by_semester(self, courses_data): """ Group courses by semester for organized display. Args: courses_data: List of course data dictionaries Returns: Dictionary grouped by semester """ semester_groups = {} for course in courses_data: semester = course.get('semester', 'Unknown') if semester not in semester_groups: semester_groups[semester] = { 'semester': semester, 'courses': [], 'total_courses': 0, 'courses_with_supervisors': 0, 'courses_without_supervisors': 0 } semester_groups[semester]['courses'].append(course) semester_groups[semester]['total_courses'] += 1 if course.get('has_supervisor'): semester_groups[semester]['courses_with_supervisors'] += 1 else: semester_groups[semester]['courses_without_supervisors'] += 1 # Calculate supervisor coverage for each semester for semester_data in semester_groups.values(): total = semester_data['total_courses'] if total > 0: semester_data['supervisor_coverage'] = round((semester_data['courses_with_supervisors'] / total) * 100, 1) else: semester_data['supervisor_coverage'] = 0 return semester_groups def _group_courses_by_department(self, courses_data): """ Group courses by department for organized display. Args: courses_data: List of course data dictionaries Returns: Dictionary grouped by department """ department_groups = {} for course in courses_data: department = course.get('department', 'Unknown') if department not in department_groups: department_groups[department] = { 'department': department, 'courses': [], 'total_courses': 0, 'courses_with_supervisors': 0, 'courses_without_supervisors': 0 } department_groups[department]['courses'].append(course) department_groups[department]['total_courses'] += 1 if course.get('has_supervisor'): department_groups[department]['courses_with_supervisors'] += 1 else: department_groups[department]['courses_without_supervisors'] += 1 # Calculate supervisor coverage for each department for dept_data in department_groups.values(): total = dept_data['total_courses'] if total > 0: dept_data['supervisor_coverage'] = round((dept_data['courses_with_supervisors'] / total) * 100, 1) else: dept_data['supervisor_coverage'] = 0 return department_groups def _get_supervisor_distribution(self, courses_data): """ Get distribution of supervisor assignments across courses. Args: courses_data: List of course data dictionaries Returns: Dictionary with supervisor distribution information """ supervisor_distribution = {} for course in courses_data: if course.get('has_supervisor') and course.get('supervisor'): supervisor_id = course['supervisor']['id'] supervisor_name = course['supervisor']['name'] if supervisor_id not in supervisor_distribution: supervisor_distribution[supervisor_id] = { 'supervisor_id': supervisor_id, 'supervisor_name': supervisor_name, 'supervisor_email': course['supervisor'].get('email'), 'courses_count': 0, 'courses': [], 'semesters': set(), 'departments': set() } supervisor_distribution[supervisor_id]['courses_count'] += 1 supervisor_distribution[supervisor_id]['courses'].append({ 'code': course['code'], 'title': course['title'], 'semester': course.get('semester'), 'department': course.get('department') }) if course.get('semester'): supervisor_distribution[supervisor_id]['semesters'].add(course['semester']) if course.get('department'): supervisor_distribution[supervisor_id]['departments'].add(course['department']) # Convert sets to lists for JSON serialization for supervisor_data in supervisor_distribution.values(): supervisor_data['semesters'] = list(supervisor_data['semesters']) supervisor_data['departments'] = list(supervisor_data['departments']) return supervisor_distribution def _get_supervisor_quick_view(self, courses_data): """ Get a quick overview of supervisor assignments for easy display. Args: courses_data: List of course data dictionaries Returns: Dictionary with quick supervisor overview """ quick_view = { 'total_courses': len(courses_data), 'assigned_courses': 0, 'unassigned_courses': 0, 'supervisor_list': [], 'course_supervisor_map': {} } supervisor_map = {} for course in courses_data: course_code = course['code'] course_title = course['title'] if course.get('has_supervisor') and course.get('supervisor'): quick_view['assigned_courses'] += 1 supervisor_id = course['supervisor']['id'] supervisor_name = course['supervisor']['name'] supervisor_email = course['supervisor']['email'] # Add to course-supervisor mapping quick_view['course_supervisor_map'][course_code] = { 'course_title': course_title, 'supervisor_name': supervisor_name, 'supervisor_email': supervisor_email, 'supervisor_staff_id': course['supervisor'].get('staff_id', 'N/A'), 'assignment_status': 'Assigned' } # Add to supervisor list if not already there if supervisor_id not in supervisor_map: supervisor_map[supervisor_id] = { 'supervisor_id': supervisor_id, 'supervisor_name': supervisor_name, 'supervisor_email': supervisor_email, 'supervisor_staff_id': course['supervisor'].get('staff_id', 'N/A'), 'courses_assigned': [] } supervisor_map[supervisor_id]['courses_assigned'].append({ 'code': course_code, 'title': course_title, 'semester': course.get('semester', 'N/A') }) else: quick_view['unassigned_courses'] += 1 quick_view['course_supervisor_map'][course_code] = { 'course_title': course_title, 'supervisor_name': 'No Supervisor Assigned', 'supervisor_email': 'N/A', 'supervisor_staff_id': 'N/A', 'assignment_status': 'Unassigned' } # Convert supervisor map to list quick_view['supervisor_list'] = list(supervisor_map.values()) return quick_view def _create_supervisor_summary_table(self, courses_data): """ Create a summary table showing supervisor assignments for each course. This provides a clear, tabular view of supervisor information. Args: courses_data: List of course data dictionaries Returns: Dictionary with summary table data """ summary_table = { 'headers': ['Course Code', 'Course Title', 'Semester', 'Supervisor Name', 'Supervisor Email', 'Staff ID', 'Status'], 'rows': [], 'assigned_count': 0, 'unassigned_count': 0 } for course in courses_data: course_code = course['code'] course_title = course['title'] semester = course.get('semester', 'N/A') if course.get('has_supervisor') and course.get('supervisor'): supervisor_name = course['supervisor']['name'] supervisor_email = course['supervisor']['email'] staff_id = course['supervisor'].get('staff_id', 'N/A') status = 'Assigned' summary_table['assigned_count'] += 1 else: supervisor_name = 'No Supervisor Assigned' supervisor_email = 'N/A' staff_id = 'N/A' status = 'Unassigned' summary_table['unassigned_count'] += 1 summary_table['rows'].append({ 'course_code': course_code, 'course_title': course_title, 'semester': semester, 'supervisor_name': supervisor_name, 'supervisor_email': supervisor_email, 'staff_id': staff_id, 'status': status, 'has_supervisor': course.get('has_supervisor', False) }) return summary_table def assign_supervisor_to_speciality_course(self, speciality_id, course_id, supervisor_id, requester_id): """ Assign a supervisor to a specific course within a speciality. This function ensures the course belongs to the specified speciality. Args: speciality_id: ID of the speciality course_id: ID of the course within the speciality supervisor_id: ID of the supervisor to assign requester_id: ID of the user making the request Returns: Response with success/error status """ with DatabaseContextManager() as ctx: try: # Verify speciality exists speciality = ctx.session.query(Speciality).filter(Speciality.id == speciality_id).first() if not speciality: return custom_response( success=False, data="Speciality not found", status_code=404 ) # Verify course exists and belongs to the speciality course = ctx.session.query(Course).filter( Course.id == course_id, Course.speciality_id == speciality_id, Course.is_active == True ).first() if not course: return custom_response( success=False, data="Course not found or does not belong to the specified speciality", status_code=404 ) # Use the existing supervisor assignment function return self.assign_supervisor_to_course(course_id, supervisor_id, requester_id) except Exception as e: current_app.logger.error(f"Error assigning supervisor to speciality course: {str(e)}", exc_info=True) return custom_response( success=False, data=f"Failed to assign supervisor to speciality course: {str(e)}", status_code=500 ) def get_speciality_supervisor_assignments(self, speciality_id, requester_id=None): """ Get all supervisor assignments for courses within a speciality. Args: speciality_id: ID of the speciality requester_id: ID of the user requesting the information (optional) Returns: Response with supervisor assignment details """ with DatabaseContextManager() as ctx: try: # Verify speciality exists speciality = ctx.session.query(Speciality).filter(Speciality.id == speciality_id).first() if not speciality: return custom_response( success=False, data="Speciality not found", status_code=404 ) # Get all courses for this speciality courses = ctx.session.query(Course).filter( Course.speciality_id == speciality_id, Course.is_active == True ).all() assignments_data = [] supervisor_stats = {} for course in courses: if course.supervisor_id: supervisor = ctx.session.query(Supervisor).filter(Supervisor.id == course.supervisor_id).first() if supervisor: # Count courses per supervisor if supervisor.id not in supervisor_stats: supervisor_stats[supervisor.id] = { 'id': supervisor.id, 'name': f"{supervisor.first_name} {supervisor.last_name}", 'email': supervisor.email, 'staff_id': supervisor.staff_id, 'department': supervisor.get_primary_department() if hasattr(supervisor, 'get_primary_department') else 'Not assigned', 'courses_count': 0, 'courses': [] } supervisor_stats[supervisor.id]['courses_count'] += 1 supervisor_stats[supervisor.id]['courses'].append({ 'id': course.id, 'code': course.code, 'title': course.title, 'semester': course.semester }) assignments_data.append({ 'course_id': course.id, 'course_code': course.code, 'course_title': course.title, 'supervisor_id': supervisor.id, 'supervisor_name': f"{supervisor.first_name} {supervisor.last_name}", 'supervisor_email': supervisor.email, 'supervisor_staff_id': supervisor.staff_id, 'assigned_at': str(course.created_at), 'course_semester': course.semester }) # Convert supervisor stats to list supervisor_list = list(supervisor_stats.values()) supervisor_list.sort(key=lambda x: x['courses_count'], reverse=True) return custom_response( success=True, data={ 'speciality': { 'id': speciality.id, 'name': speciality.name, 'department': speciality.department }, 'assignments': assignments_data, 'supervisor_statistics': supervisor_list, 'total_assignments': len(assignments_data), 'total_supervisors': len(supervisor_stats), 'message': f'Found {len(assignments_data)} supervisor assignments in {speciality.name}' }, status_code=200 ) except Exception as e: current_app.logger.error(f"Error getting speciality supervisor assignments: {str(e)}", exc_info=True) return custom_response( success=False, data=f"Failed to get speciality supervisor assignments: {str(e)}", status_code=500 ) def get_course_students(self, course_id, requester_id=None, requester_type=None): """ Get all students enrolled in a specific course with their enrollment details. Args: course_id: ID of the course requester_id: ID of the user making the request (optional) requester_type: Type of user making the request (optional) Returns: List of students with their enrollment information """ with DatabaseContextManager() as ctx: try: # Verify course exists course = ctx.session.query(Course).filter( Course.id == course_id, Course.is_active == True ).first() if not course: return custom_response( success=False, data="Course not found or inactive", status_code=404 ) # Get all students enrolled in this course through the association table students_query = ctx.session.query( Student, Enrollment, enrollment_courses.c.status.label('enrollment_status'), enrollment_courses.c.grade, enrollment_courses.c.attendance_percentage, enrollment_courses.c.enrollment_date.label('course_enrollment_date'), enrollment_courses.c.completion_date, enrollment_courses.c.withdrawal_date, enrollment_courses.c.withdrawal_reason, enrollment_courses.c.is_audit ).join( Enrollment, Enrollment.student_id == Student.id ).join( enrollment_courses, enrollment_courses.c.enrollment_id == Enrollment.id ).filter( enrollment_courses.c.course_id == course_id, Student.is_active == True ).order_by(Student.first_name, Student.last_name) students_data = [] for student, enrollment, enrollment_status, grade, attendance_percentage, course_enrollment_date, completion_date, withdrawal_date, withdrawal_reason, is_audit in students_query: # Get student's speciality information speciality = ctx.session.query(Speciality).filter( Speciality.id == student.speciality_id ).first() # Get recent attendance for this student in this course recent_attendance = ctx.session.query(Attendance).join( TeachingSession, Attendance.session_id == TeachingSession.id ).filter( TeachingSession.course_id == course_id, Attendance.student_id == student.id ).order_by(Attendance.created_at.desc()).limit(5).all() # Get assignment submissions for this student in this course assignment_submissions = ctx.session.query(AssignmentSubmission).join( Assignment, AssignmentSubmission.assignment_id == Assignment.id ).filter( Assignment.course_id == course_id, AssignmentSubmission.student_id == student.id ).order_by(AssignmentSubmission.submitted_at.desc()).limit(5).all() # Calculate attendance statistics total_sessions = ctx.session.query(TeachingSession).filter( TeachingSession.course_id == course_id ).count() present_sessions = ctx.session.query(Attendance).join( TeachingSession, Attendance.session_id == TeachingSession.id ).filter( TeachingSession.course_id == course_id, Attendance.student_id == student.id, Attendance.status == AttendanceStatus.present ).count() attendance_rate = (present_sessions / total_sessions * 100) if total_sessions > 0 else 0 # Calculate assignment statistics total_assignments = ctx.session.query(Assignment).filter( Assignment.course_id == course_id ).count() submitted_assignments = ctx.session.query(AssignmentSubmission).join( Assignment, AssignmentSubmission.assignment_id == Assignment.id ).filter( Assignment.course_id == course_id, AssignmentSubmission.student_id == student.id ).count() # Calculate average grade avg_grade = ctx.session.query( func.avg(AssignmentSubmission.grade) ).join( Assignment, AssignmentSubmission.assignment_id == Assignment.id ).filter( Assignment.course_id == course_id, AssignmentSubmission.student_id == student.id, AssignmentSubmission.grade.isnot(None) ).scalar() student_data = { 'id': student.id, 'student_id': student.student_id, 'first_name': student.first_name, 'last_name': student.last_name, 'email': student.email, 'phone': student.phone, 'year_of_study': student.year_of_study, 'program': student.program, 'current_semester': student.current_semester, 'student_category': student.student_category, 'cumulative_gpa': student.cumulative_gpa, 'department': student.department, 'speciality': { 'id': speciality.id if speciality else None, 'name': speciality.name if speciality else None, 'department': speciality.department if speciality else None } if speciality else None, 'enrollment': { 'id': enrollment.id, 'status': enrollment_status, 'grade': grade, 'attendance_percentage': attendance_percentage, 'enrollment_date': str(course_enrollment_date) if course_enrollment_date else None, 'completion_date': str(completion_date) if completion_date else None, 'withdrawal_date': str(withdrawal_date) if withdrawal_date else None, 'withdrawal_reason': withdrawal_reason, 'is_audit': is_audit }, 'statistics': { 'attendance_rate': round(attendance_rate, 1), 'total_sessions': total_sessions, 'present_sessions': present_sessions, 'total_assignments': total_assignments, 'submitted_assignments': submitted_assignments, 'average_grade': round(avg_grade, 1) if avg_grade else None }, 'recent_attendance': [ { 'id': att.id, 'status': att.status.value if att.status else None, 'date': str(att.created_at) if att.created_at else None, 'session_title': att.session.title if att.session else None } for att in recent_attendance ], 'recent_submissions': [ { 'id': sub.id, 'assignment_title': sub.assignment.title if sub.assignment else None, 'submitted_at': str(sub.submitted_at) if sub.submitted_at else None, 'grade': sub.grade, 'status': sub.status.value if sub.status else None } for sub in assignment_submissions ] } students_data.append(student_data) return custom_response( success=True, data={ 'course': { 'id': course.id, 'code': course.code, 'title': course.title, 'department': course.department, 'semester': course.semester, 'credits': course.credits }, 'students': students_data, 'total_students': len(students_data), 'active_students': len([s for s in students_data if s['enrollment']['status'] == 'active']), 'message': f'Found {len(students_data)} students enrolled in {course.title}' }, status_code=200 ) except Exception as e: current_app.logger.error(f"Error getting course students: {str(e)}", exc_info=True) return custom_response( success=False, data=f"Failed to get course students: {str(e)}", status_code=500 ) def endpoint_speciality_supervisor_management(self, request_data): """ Endpoint function to handle speciality-specific supervisor management operations. This function provides a unified interface for all speciality-related supervisor operations. Args: request_data: Dictionary containing: - action: Action to perform ('view', 'assign', 'get_assignments', 'bulk_assign') - speciality_id: ID of the speciality - course_id: ID of the course (for specific course operations) - supervisor_id: ID of the supervisor to assign - requester_id: ID of the user making the request - requester_type: Type of user making the request - assignments: List of course-supervisor assignments for bulk operations Returns: Response with success/error status and data """ try: action = request_data.get('action') speciality_id = request_data.get('speciality_id') requester_id = request_data.get('requester_id') if not speciality_id: return custom_response( success=False, data="Missing required field: speciality_id is required for all operations", status_code=400 ) if action == 'view': requester_type = request_data.get('requester_type') return self.get_speciality_detailed_view(speciality_id, requester_id, requester_type) elif action == 'assign': course_id = request_data.get('course_id') supervisor_id = request_data.get('supervisor_id') if not all([course_id, supervisor_id, requester_id]): return custom_response( success=False, data="Missing required fields: course_id, supervisor_id, and requester_id are required for assignment", status_code=400 ) return self.assign_supervisor_to_speciality_course(speciality_id, course_id, supervisor_id, requester_id) elif action == 'get_assignments': return self.get_speciality_supervisor_assignments(speciality_id, requester_id) elif action == 'bulk_assign': assignments = request_data.get('assignments', []) if not assignments: return custom_response( success=False, data="Missing required field: assignments list is required for bulk assignment", status_code=400 ) if not requester_id: return custom_response( success=False, data="Missing required field: requester_id is required for bulk assignment", status_code=400 ) results = [] errors = [] for assignment in assignments: course_id = assignment.get('course_id') supervisor_id = assignment.get('supervisor_id') if not all([course_id, supervisor_id]): errors.append({ 'assignment': assignment, 'error': 'Missing course_id or supervisor_id' }) continue # Attempt to assign supervisor to speciality course result = self.assign_supervisor_to_speciality_course(speciality_id, course_id, supervisor_id, requester_id) if result.get('success'): results.append({ 'course_id': course_id, 'supervisor_id': supervisor_id, 'status': 'success', 'message': result.get('data', {}).get('message', 'Assigned successfully') }) else: errors.append({ 'course_id': course_id, 'supervisor_id': supervisor_id, 'error': result.get('data', 'Assignment failed') }) return custom_response( success=True, data={ 'operation': 'bulk_speciality_assignment', 'speciality_id': speciality_id, 'total_assignments': len(assignments), 'successful': len(results), 'failed': len(errors), 'results': results, 'errors': errors, 'summary': f"Successfully assigned {len(results)} out of {len(assignments)} supervisors in speciality" }, status_code=200 ) else: return custom_response( success=False, data=f"Invalid action '{action}'. Valid actions are: 'view', 'assign', 'get_assignments', 'bulk_assign'", status_code=400 ) except Exception as e: current_app.logger.error(f"Error in speciality supervisor management endpoint: {str(e)}", exc_info=True) return custom_response( success=False, data=f"Speciality supervisor management endpoint error: {str(e)}", status_code=500 ) def endpoint_bulk_supervisor_operations(self, request_data): """ Endpoint function to handle bulk supervisor operations for courses. This function can be called directly from API routes. Args: request_data: Dictionary containing: - operation: Type of operation ('assign_multiple', 'reassign_department', 'get_department_stats') - department: Department name for department-wide operations - assignments: List of course-supervisor assignments for bulk operations - requester_id: ID of the user making the request - filters: Additional filters for the operation Returns: Response with success/error status and data """ try: operation = request_data.get('operation') requester_id = request_data.get('requester_id') if not requester_id: return custom_response( success=False, data="Missing required field: requester_id is required for all operations", status_code=400 ) if operation == 'assign_multiple': assignments = request_data.get('assignments', []) if not assignments: return custom_response( success=False, data="Missing required field: assignments list is required for bulk assignment", status_code=400 ) results = [] errors = [] for assignment in assignments: course_id = assignment.get('course_id') supervisor_id = assignment.get('supervisor_id') if not all([course_id, supervisor_id]): errors.append({ 'assignment': assignment, 'error': 'Missing course_id or supervisor_id' }) continue # Attempt to assign supervisor result = self.assign_supervisor_to_course(course_id, supervisor_id, requester_id) if result.get('success'): results.append({ 'course_id': course_id, 'supervisor_id': supervisor_id, 'status': 'success', 'message': result.get('data', {}).get('message', 'Assigned successfully') }) else: errors.append({ 'course_id': course_id, 'supervisor_id': supervisor_id, 'error': result.get('data', 'Assignment failed') }) return custom_response( success=True, data={ 'operation': 'bulk_assignment', 'total_assignments': len(assignments), 'successful': len(results), 'failed': len(errors), 'results': results, 'errors': errors, 'summary': f"Successfully assigned {len(results)} out of {len(assignments)} supervisors" }, status_code=200 ) elif operation == 'reassign_department': department = request_data.get('department') new_supervisor_id = request_data.get('new_supervisor_id') if not all([department, new_supervisor_id]): return custom_response( success=False, data="Missing required fields: department and new_supervisor_id are required for department reassignment", status_code=400 ) # Get all courses in the department with DatabaseContextManager() as ctx: department_courses = ctx.session.query(Course).filter( Course.department == department, Course.is_active == True ).all() results = [] errors = [] for course in department_courses: # Attempt to reassign supervisor result = self.assign_supervisor_to_course(course.id, new_supervisor_id, requester_id) if result.get('success'): results.append({ 'course_id': course.id, 'course_code': course.code, 'status': 'success', 'message': 'Reassigned successfully' }) else: errors.append({ 'course_id': course.id, 'course_code': course.code, 'error': result.get('data', 'Reassignment failed') }) return custom_response( success=True, data={ 'operation': 'department_reassignment', 'department': department, 'new_supervisor_id': new_supervisor_id, 'total_courses': len(department_courses), 'successful': len(results), 'failed': len(errors), 'results': results, 'errors': errors, 'summary': f"Successfully reassigned {len(results)} out of {len(department_courses)} courses in {department}" }, status_code=200 ) elif operation == 'get_department_stats': department = request_data.get('department') if not department: return custom_response( success=False, data="Missing required field: department is required for department statistics", status_code=400 ) with DatabaseContextManager() as ctx: # Get department statistics total_courses = ctx.session.query(Course).filter( Course.department == department, Course.is_active == True ).count() courses_with_supervisors = ctx.session.query(Course).filter( Course.department == department, Course.is_active == True, Course.supervisor_id.isnot(None) ).count() courses_without_supervisors = total_courses - courses_with_supervisors # Get supervisor distribution supervisor_stats = ctx.session.query( Course.supervisor_id, func.count(Course.id).label('course_count') ).filter( Course.department == department, Course.is_active == True, Course.supervisor_id.isnot(None) ).group_by(Course.supervisor_id).all() supervisor_details = [] for supervisor_id, course_count in supervisor_stats: supervisor = ctx.session.query(Supervisor).filter(Supervisor.id == supervisor_id).first() if supervisor: supervisor_details.append({ 'supervisor_id': supervisor_id, 'name': f"{supervisor.first_name} {supervisor.last_name}", 'staff_id': supervisor.staff_id, 'course_count': course_count }) return custom_response( success=True, data={ 'operation': 'department_statistics', 'department': department, 'statistics': { 'total_courses': total_courses, 'courses_with_supervisors': courses_with_supervisors, 'courses_without_supervisors': courses_without_supervisors, 'supervisor_coverage': round((courses_with_supervisors / total_courses * 100), 1) if total_courses > 0 else 0 }, 'supervisor_distribution': supervisor_details, 'summary': f"Department {department} has {total_courses} courses with {courses_with_supervisors} supervised" }, status_code=200 ) else: return custom_response( success=False, data=f"Invalid operation '{operation}'. Valid operations are: 'assign_multiple', 'reassign_department', 'get_department_stats'", status_code=400 ) except Exception as e: current_app.logger.error(f"Error in bulk supervisor operations endpoint: {str(e)}", exc_info=True) return custom_response( success=False, data=f"Bulk operations endpoint error: {str(e)}", status_code=500 )