from datetime import datetime, date, timedelta, time from sqlalchemy import Column, Integer, String, Boolean, ForeignKey, DateTime, Time, Date, Table, Enum, Text, Float, Index, UniqueConstraint from sqlalchemy.orm import relationship, backref, joinedload from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.dialects.postgresql import UUID from enum import Enum as PyEnum from sqlalchemy import CheckConstraint import uuid from flask import current_app from typing import Dict, Any import jwt Base = declarative_base() # Association tables tutor_course_association = Table( 'tutor_course_association', Base.metadata, Column('tutor_id', String(150), ForeignKey('tutors.id')), Column('course_id', String(150), ForeignKey('courses.id')), Column('is_primary', Boolean, default=False) ) supervisor_course_association = Table( 'supervisor_course_association', Base.metadata, Column('supervisor_id', String(150), ForeignKey('supervisors.id')), Column('course_id', String(150), ForeignKey('courses.id')), Column('is_primary', Boolean, default=False) ) # Association table for course-department relationships (for shared courses) course_department_association = Table( 'course_department_association', Base.metadata, Column('course_id', String(150), ForeignKey('courses.id')), Column('department_name', String(100), nullable=False), Column('is_primary_department', Boolean, default=False), Column('assigned_date', Date, default=date.today), Column('assigned_by', String(150), ForeignKey('supervisors.id'), nullable=True), Column('notes', Text), Column('is_active', Boolean, default=True), Column('created_at', DateTime, default=datetime.utcnow), Column('updated_at', DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) ) # Association table for course-speciality relationships (for shared/interdisciplinary courses) course_speciality_association = Table( 'course_speciality_association', Base.metadata, Column('course_id', String(150), ForeignKey('courses.id')), Column('speciality_id', String(150), ForeignKey('specialities.id')), Column('is_primary_speciality', Boolean, default=False), Column('assigned_date', Date, default=date.today), Column('assigned_by', String(150), ForeignKey('supervisors.id'), nullable=True), Column('notes', Text), Column('is_active', Boolean, default=True), Column('created_at', DateTime, default=datetime.utcnow), Column('updated_at', DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) ) # New association table for tutor-department relationships tutor_department_association = Table( 'tutor_department_association', Base.metadata, Column('tutor_id', String(150), ForeignKey('tutors.id')), Column('department_name', String(100)), Column('is_primary', Boolean, default=False), Column('assigned_date', Date, default=date.today), Column('assigned_by', String(150), ForeignKey('supervisors.id')), Column('notes', Text) ) # New association table for supervisor-department relationships supervisor_department_association = Table( 'supervisor_department_association', Base.metadata, Column('supervisor_id', String(150), ForeignKey('supervisors.id')), Column('department_name', String(100)), Column('is_primary', Boolean, default=False), Column('assigned_date', Date, default=date.today), Column('assigned_by', String(150), ForeignKey('supervisors.id')), Column('notes', Text) ) # New association table for student-academic session relationships student_academic_session_association = Table( 'student_academic_session_association', Base.metadata, Column('student_id', String(150), ForeignKey('students.id'), primary_key=True), Column('academic_session_id', String(150), ForeignKey('academic_sessions.id'), primary_key=True), Column('enrollment_date', Date, default=date.today), Column('status', String(20), default='active'), # active, completed, withdrawn, suspended Column('enrollment_type', String(20), default='regular'), # regular, part-time, audit, transfer Column('completion_date', Date), Column('withdrawal_date', Date), Column('withdrawal_reason', Text), Column('academic_standing', String(20), default='good'), # good, probation, warning, suspended Column('notes', Text), Column('created_at', DateTime, default=datetime.utcnow), Column('updated_at', DateTime, default=datetime.utcnow, onupdate=datetime.utcnow), # Indexes for better query performance Index('idx_student_session_student_id', 'student_id'), Index('idx_student_session_academic_session_id', 'academic_session_id'), Index('idx_student_session_status', 'status'), Index('idx_student_session_enrollment_date', 'enrollment_date'), Index('idx_student_session_academic_standing', 'academic_standing') ) class AttendanceStatus(PyEnum): present = "present" absent = "absent" late = "late" excused = "excused" left_early = "left_early" pending_approval = "pending_approval" disputed = "disputed" class TeachingVerificationMethod(PyEnum): manual = "manual" geo_location = "geo_location" biometric = "biometric" video_confirmation = "video_confirmation" qr_code = "qr_code" class ResourceType(PyEnum): document = "document" video = "video" link = "link" presentation = "presentation" image = "image" audio = "audio" archive = "archive" other = "other" class AssignmentType(PyEnum): homework = "homework" quiz = "quiz" project = "project" essay = "essay" presentation = "presentation" lab = "lab" exam = "exam" class SubmissionStatus(PyEnum): not_started = "not_started" in_progress = "in_progress" submitted = "submitted" late = "late" graded = "graded" class UserType(PyEnum): supervisor = "supervisor" tutor = "tutor" student = "student" admin = "admin" class QualificationType(PyEnum): """Enum for speciality qualification types""" craft = "craft" certificate = "certificate" diploma = "diploma" degree = "degree" postgraduate = "postgraduate" other = "other" class SpecialityLevel(PyEnum): """Enum for speciality levels""" level_1 = "Level 1" level_2 = "Level 2" level_3 = "Level 3" level_4 = "Level 4" level_5 = "Level 5" level_6 = "Level 6" level_7 = "Level 7" level_8 = "Level 8" class TwoFactorMethod(PyEnum): none = "none" email = "email" sms = "sms" authenticator_app = "authenticator_app" backup_code = "backup_code" class AuthenticationMixin: def generate_token(self, userid: str) -> str: payload = { "exp": datetime.utcnow() + timedelta(days=1000, hours=2, minutes=0), "iat": datetime.utcnow(), "sub": userid } return jwt.encode(payload, key=current_app.config['SECRET_KEY'], algorithm='HS256') @staticmethod def decode_token(token: str) -> Dict[str, Any]: try: payload = jwt.decode(token, key=current_app.config['SECRET_KEY'], algorithms=['HS256']) return payload['sub'] except jwt.ExpiredSignatureError: return { "code": 404, "status": "ExpiredSignatureError", "message": "Expired token", "errors": {"": ""} } except jwt.InvalidTokenError: return { "code": 403, "status": "InvalidTokenError", "message": "Invalid token used", "errors": {"": ""} } class Admin(Base, AuthenticationMixin): __tablename__ = "admin" id = Column(String(150), nullable=False, primary_key=True) is_authenticated = Column(Boolean, default=False) first_name = Column(String(150), nullable=False) last_name = Column(String(150), nullable=False) password_hash = Column(String(150), nullable=False) email = Column(String(150), nullable=False) otp = Column(String(150)) phone = Column(String(150)) active = Column(Boolean, default=False) failed_login_attempts = Column(Integer, nullable=False, default=0) account_locked = Column(Boolean, nullable=False, default=False) account_locked_until = Column(DateTime) otp_expiry = Column(DateTime) otp_attempts = Column(Integer) failed_login_attempts = Column(Integer) def to_json(self): return { "id": self.id, "is_authenticated": self.is_authenticated, "first_name": self.first_name, "last_name": self.last_name, "email": self.email, "phone": self.phone, "active": self.active } class User(Base, AuthenticationMixin): """Base user model for authentication""" __tablename__ = 'users' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) email = Column(String(120), unique=True, nullable=False) password_hash = Column(String(128), nullable=False) first_name = Column(String(50), nullable=False) last_name = Column(String(50), nullable=False) phone = Column(String(20)) user_type = Column(Enum(UserType), nullable=False) is_active = Column(Boolean, default=True) last_login = Column(DateTime) created_at = Column(DateTime, default=datetime.utcnow) profile_picture = Column(String(255)) gender = Column(String(50)) date_of_birth = Column(Date) nationality = Column(String(50)) notification_preference = relationship("NotificationPreference", uselist=False, back_populates="user") # Two-factor authentication fields two_factor_enabled = Column(Boolean, default=False) two_factor_method = Column(Enum(TwoFactorMethod), default=TwoFactorMethod.none) two_factor_secret = Column(String(255)) two_factor_phone = Column(String(20)) last_two_factor_attempt = Column(DateTime) two_factor_failed_attempts = Column(Integer, default=0) two_factor_backup_codes = Column(Text) two_factor_verified_at = Column(DateTime) two_factor_recovery_email = Column(String(120)) # OTP fields (MISSING FROM YOUR ORIGINAL MODEL) otp = Column(String(10)) # Current OTP code otp_expiry = Column(DateTime) # When the OTP expires otp_attempts = Column(Integer, default=0) # Number of OTP attempts # Account security fields failed_login_attempts = Column(Integer, default=0) account_locked = Column(Boolean, default=False) account_locked_until = Column(DateTime) last_password_change = Column(DateTime) must_change_password = Column(Boolean, default=False) reset_token = Column(String(255), nullable=True) reset_token_expiry = Column(DateTime, nullable=True) # Session management last_activity = Column(DateTime) current_session_id = Column(String(100)) __mapper_args__ = { 'polymorphic_on': user_type, 'polymorphic_identity': 'user' } def __init__(self, *args, **kwargs): super().__init__(*args, **kwargs) # Ensure datetime fields are properly handled self._clean_datetime_fields() def _clean_datetime_fields(self): """Clean up empty datetime strings to prevent SQLAlchemy errors""" datetime_fields = [ 'last_login', 'created_at', 'date_of_birth', 'last_two_factor_attempt', 'two_factor_verified_at', 'otp_expiry', 'account_locked_until', 'last_password_change', 'reset_token_expiry', 'last_activity' ] for field in datetime_fields: if hasattr(self, field): value = getattr(self, field) if isinstance(value, str) and value.strip() == '': setattr(self, field, None) class TwoFactorAttempt(Base): """Track 2FA verification attempts""" __tablename__ = 'two_factor_attempts' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) user_id = Column(String(150), ForeignKey('users.id'), nullable=False) # Fixed: Changed from Integer to String attempt_time = Column(DateTime, default=datetime.utcnow) code = Column(String(10)) # The code that was attempted success = Column(Boolean, default=False) method = Column(Enum(TwoFactorMethod)) ip_address = Column(String(50)) device_info = Column(String(100)) session_id = Column(String(100)) user = relationship("User") __table_args__ = ( Index('idx_user_attempt_time', 'user_id', 'attempt_time'), ) class TrustedDevice(Base): """Store devices that users have marked as trusted to skip 2FA""" __tablename__ = 'trusted_devices' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) user_id = Column(String(150), ForeignKey('users.id'), nullable=False) # Fixed: Changed from Integer to String device_name = Column(String(100)) device_id = Column(String(100), unique=True) # Browser fingerprint or device ID ip_address = Column(String(50)) last_used = Column(DateTime, default=datetime.utcnow) created_at = Column(DateTime, default=datetime.utcnow) expires_at = Column(DateTime) # Trust expires after certain period device_type = Column(String(50)) # mobile, tablet, desktop browser = Column(String(50)) os = Column(String(50)) location = Column(String(100)) user = relationship("User") class Supervisor(User): """Supervisor model extending User""" __tablename__ = 'supervisors' id = Column(String(150), ForeignKey('users.id'), primary_key=True) staff_id = Column(String(20), unique=True) # Removed single department field - now supports multiple departments office_location = Column(String(100)) office_hours = Column(String(200)) max_tutors = Column(Integer, default=10) is_head_of_department = Column(Boolean, default=False) years_of_experience = Column(Integer) can_approve_timetables = Column(Boolean, default=True) can_approve_leave = Column(Boolean, default=True) can_manage_courses = Column(Boolean, default=True) # Teaching-related fields (similar to Tutor) qualification = Column(String(100)) bio = Column(Text) hourly_rate = Column(Float) max_teaching_hours = Column(Integer, default=20) is_full_time = Column(Boolean, default=True) specialization = Column(String(100)) years_of_teaching = Column(Integer) is_on_leave = Column(Boolean, default=False) leave_start_date = Column(Date) leave_end_date = Column(Date) leave_reason = Column(Text) verification_methods = Column(String(100)) last_verification_date = Column(DateTime) verification_success_rate = Column(Float) # Relationships managed_courses = relationship("Course", back_populates="supervisor") managed_tutors = relationship( "Tutor", back_populates="supervisor", foreign_keys="[Tutor.supervisor_id]" ) created_timetables = relationship( "Timetable", back_populates="creator", foreign_keys="[Timetable.created_by]" ) approved_timetables = relationship( "Timetable", back_populates="approver", foreign_keys="[Timetable.approved_by]" ) approved_availabilities = relationship("TutorAvailability", back_populates="approver") verified_sessions = relationship( "TeachingSession", back_populates="verifier", foreign_keys="[TeachingSession.verified_by]" ) created_sessions = relationship( "TeachingSession", foreign_keys="[TeachingSession.created_by]", back_populates="creator" ) # New relationships for supervisor teaching capabilities availabilities = relationship("SupervisorAvailability", back_populates="supervisor", foreign_keys="[SupervisorAvailability.supervisor_id]") courses = relationship("Course", secondary=supervisor_course_association, back_populates="supervisors") scheduled_sessions = relationship("TeachingSession", back_populates="supervisor_tutor", foreign_keys="[TeachingSession.supervisor_tutor_id]") reminders_sent = relationship("Reminder", back_populates="supervisor") attendance_records = relationship("Attendance", back_populates="supervisor_tutor", foreign_keys="[Attendance.supervisor_tutor_id]") timetable_blocks = relationship( "TimetableBlock", back_populates="supervisor_tutor", foreign_keys="[TimetableBlock.supervisor_tutor_id]" ) teaching_logs = relationship("SupervisorTeachingLog", back_populates="supervisor", foreign_keys="[SupervisorTeachingLog.supervisor_id]") # Availability opening sessions management created_opening_sessions = relationship("AvailabilityOpeningSession", back_populates="creator", foreign_keys="[AvailabilityOpeningSession.created_by]") uploaded_exam_evidence = relationship("ExamEvidence", foreign_keys="[ExamEvidence.uploaded_by]") verified_exam_evidence = relationship("ExamEvidence", foreign_keys="[ExamEvidence.verified_by]") created_exam_timetables = relationship("ExamTimetable", foreign_keys="[ExamTimetable.created_by]") # New many-to-many relationship with departments departments = relationship( "SupervisorDepartment", back_populates="supervisor", cascade="all, delete-orphan", foreign_keys="[SupervisorDepartment.supervisor_id]" ) __mapper_args__ = { 'polymorphic_identity': UserType.supervisor } def get_primary_department(self): """Get the primary department for this supervisor""" for dept in self.departments: if dept.is_primary and dept.is_active: return dept.department_name return None def get_all_departments(self): """Get all active departments for this supervisor""" return [dept.department_name for dept in self.departments if dept.is_active] def add_department(self, department_name, is_primary=False, assigned_by=None, notes=None): """Add a new department to this supervisor""" from .models import SupervisorDepartment # If this is a primary department, unset other primary departments if is_primary: for dept in self.departments: if dept.is_primary: dept.is_primary = False new_dept = SupervisorDepartment( supervisor_id=self.id, department_name=department_name, is_primary=is_primary, assigned_by=assigned_by, notes=notes ) self.departments.append(new_dept) return new_dept def remove_department(self, department_name): """Remove a department from this supervisor (soft delete)""" for dept in self.departments: if dept.department_name == department_name: dept.is_active = False return True return False class Tutor(User): """Tutor model extending User""" __tablename__ = 'tutors' id = Column(String(150), ForeignKey('users.id'), primary_key=True) staff_id = Column(String(20), unique=True) # Removed single department field - now supports multiple departments office_location = Column(String(100)) qualification = Column(String(100)) bio = Column(Text) hourly_rate = Column(Float) max_teaching_hours = Column(Integer, default=20) supervisor_id = Column(String(150), ForeignKey('supervisors.id')) # Fixed: Changed from Integer to String is_full_time = Column(Boolean, default=True) specialization = Column(String(100)) years_of_teaching = Column(Integer) is_on_leave = Column(Boolean, default=False) leave_start_date = Column(Date) leave_end_date = Column(Date) leave_reason = Column(Text) supervisor = relationship( "Supervisor", back_populates="managed_tutors", foreign_keys=[supervisor_id] # Explicitly specify the foreign key ) availabilities = relationship("TutorAvailability", back_populates="tutor") courses = relationship("Course", secondary=tutor_course_association, back_populates="tutors") scheduled_sessions = relationship("TeachingSession", back_populates="tutor") reminders_sent = relationship("Reminder", back_populates="tutor") attendance_records = relationship("Attendance", back_populates="tutor") timetable_blocks = relationship( "TimetableBlock", back_populates="tutor", foreign_keys="[TimetableBlock.tutor_id]" # Explicitly specify the foreign key ) teaching_logs = relationship("TutorTeachingLog", back_populates="tutor") verification_methods = Column(String(100)) last_verification_date = Column(DateTime) verification_success_rate = Column(Float) uploaded_exam_evidence = relationship("ExamEvidence", foreign_keys="[ExamEvidence.uploaded_by]") # New many-to-many relationship with departments departments = relationship( "TutorDepartment", back_populates="tutor", cascade="all, delete-orphan" ) __mapper_args__ = { 'polymorphic_identity': UserType.tutor } def __init__(self, *args, **kwargs): super().__init__(*args, **kwargs) # Ensure datetime fields are properly handled self._clean_datetime_fields() def _clean_datetime_fields(self): """Clean up empty datetime strings to prevent SQLAlchemy errors""" datetime_fields = ['last_verification_date', 'leave_start_date', 'leave_end_date'] for field in datetime_fields: if hasattr(self, field): value = getattr(self, field) if isinstance(value, str) and value.strip() == '': setattr(self, field, None) def get_primary_department(self): """Get the primary department for this tutor""" for dept in self.departments: if dept.is_primary and dept.is_active: return dept.department_name return None def get_all_departments(self): """Get all active departments for this tutor""" return [dept.department_name for dept in self.departments if dept.is_active] def add_department(self, department_name, is_primary=False, assigned_by=None, notes=None): """Add a new department to this tutor""" from .models import TutorDepartment # If this is a primary department, unset other primary departments if is_primary: for dept in self.departments: if dept.is_primary: dept.is_primary = False new_dept = TutorDepartment( tutor_id=self.id, department_name=department_name, is_primary=is_primary, assigned_by=assigned_by, notes=notes ) self.departments.append(new_dept) return new_dept def remove_department(self, department_name): """Remove a department from this tutor (soft delete)""" for dept in self.departments: if dept.department_name == department_name: dept.is_active = False return True return False class TutorTeachingLog(Base): """Detailed log of tutor teaching activities""" __tablename__ = 'tutor_teaching_logs' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) tutor_id = Column(String(150), ForeignKey('tutors.id')) # Fixed: Changed from Integer to String session_id = Column(String(150), ForeignKey('teaching_session.id')) # Fixed: Changed from Integer to String checkin_time = Column(DateTime) checkout_time = Column(DateTime) verification_method = Column(Enum(TeachingVerificationMethod)) location_lat = Column(Float) location_long = Column(Float) device_info = Column(String(100)) ip_address = Column(String(50)) session_duration = Column(Integer) # in minutes teaching_materials_used = Column(Text) # JSON list of resources used student_interaction_notes = Column(Text) issues_encountered = Column(Text) supervisor_review_status = Column(String(20), default='pending') # pending, reviewed, approved, needs_followup supervisor_review_notes = Column(Text) reviewed_by = Column(String(150), ForeignKey('supervisors.id')) # Fixed: Changed from Integer to String review_date = Column(DateTime) # Relationships tutor = relationship("Tutor", back_populates="teaching_logs") session = relationship("TeachingSession") reviewer = relationship("Supervisor", foreign_keys=[reviewed_by]) __table_args__ = ( CheckConstraint('session_duration >= 0', name='non_negative_duration'), CheckConstraint("supervisor_review_status IN ('pending', 'reviewed', 'approved', 'needs_followup')", name='valid_review_status'), ) class TutorDepartment(Base): """Many-to-many relationship between tutors and departments""" __tablename__ = 'tutor_departments' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) tutor_id = Column(String(150), ForeignKey('tutors.id'), nullable=False) department_name = Column(String(100), nullable=False) is_primary = Column(Boolean, default=False) assigned_date = Column(Date, default=date.today) assigned_by = Column(String(150), ForeignKey('supervisors.id'), nullable=True) notes = Column(Text) is_active = Column(Boolean, default=True) created_at = Column(DateTime, default=datetime.utcnow) updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) # Relationships tutor = relationship("Tutor", back_populates="departments") assigned_by_supervisor = relationship("Supervisor", foreign_keys=[assigned_by]) __table_args__ = ( # Ensure a tutor can only have one primary department CheckConstraint('is_primary IN (True, False)', name='valid_primary_flag'), # Ensure department name is not empty CheckConstraint('department_name IS NOT NULL AND department_name != ""', name='valid_department_name'), ) def to_json(self): return { "id" : self.id, "tutor_id": self.tutor_id, "department_name": self.department_name, "is_primary": self.is_primary, "assigned_date": self.assigned_date, "assigned_by": self.assigned_by, "notes": self.notes, "is_active": self.is_active, "created_at": self.created_at, } class SupervisorDepartment(Base): """Many-to-many relationship between supervisors and departments""" __tablename__ = 'supervisor_departments' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) supervisor_id = Column(String(150), ForeignKey('supervisors.id'), nullable=False) department_name = Column(String(100), nullable=False) is_primary = Column(Boolean, default=False) assigned_date = Column(Date, default=date.today) assigned_by = Column(String(150), ForeignKey('supervisors.id'), nullable=True) notes = Column(Text) is_active = Column(Boolean, default=True) created_at = Column(DateTime, default=datetime.utcnow) updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) # Relationships supervisor = relationship("Supervisor", back_populates="departments", foreign_keys="[SupervisorDepartment.supervisor_id]") assigned_by_supervisor = relationship("Supervisor", foreign_keys=[assigned_by]) __table_args__ = ( # Ensure a supervisor can only have one primary department CheckConstraint('is_primary IN (True, False)', name='valid_primary_flag'), # Ensure department name is not empty CheckConstraint('department_name IS NOT NULL AND department_name != ""', name='valid_department_name'), ) def to_json(self): return { 'id': self.id, 'department': self.department_name, 'is_primary': self.is_primary, 'notes': self.notes } class SupervisorTeachingLog(Base): """Detailed log of supervisor teaching activities""" __tablename__ = 'supervisor_teaching_logs' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) supervisor_id = Column(String(150), ForeignKey('supervisors.id')) session_id = Column(String(150), ForeignKey('teaching_session.id')) checkin_time = Column(DateTime) checkout_time = Column(DateTime) verification_method = Column(Enum(TeachingVerificationMethod)) location_lat = Column(Float) location_long = Column(Float) device_info = Column(String(100)) ip_address = Column(String(50)) session_duration = Column(Integer) # in minutes teaching_materials_used = Column(Text) # JSON list of resources used student_interaction_notes = Column(Text) issues_encountered = Column(Text) review_status = Column(String(20), default='completed') # completed, needs_followup review_notes = Column(Text) reviewed_by = Column(String(150), ForeignKey('supervisors.id')) review_date = Column(DateTime) # Relationships supervisor = relationship("Supervisor", back_populates="teaching_logs", foreign_keys=[supervisor_id]) session = relationship("TeachingSession") reviewer = relationship("Supervisor", foreign_keys=[reviewed_by]) __table_args__ = ( CheckConstraint('session_duration >= 0', name='non_negative_supervisor_duration'), CheckConstraint("review_status IN ('completed', 'needs_followup')", name='valid_supervisor_review_status'), ) class Student(User): """Enhanced Student model with more fields""" __tablename__ = 'students' id = Column(String(150), ForeignKey('users.id'), primary_key=True) student_id = Column(String(20), unique=True) year_of_study = Column(Integer) program = Column(String(100)) enrollment_date = Column(Date) graduation_date = Column(Date) current_semester = Column(String(20)) student_category = Column(String(50)) # e.g., regular, part-time, international guardian_name = Column(String(100)) guardian_contact = Column(String(20)) emergency_contact = Column(String(20)) address = Column(Text) city = Column(String(50)) country = Column(String(50)) postal_code = Column(String(20)) academic_advisor = Column(String(100)) cumulative_gpa = Column(Float) has_scholarship = Column(Boolean, default=False) scholarship_details = Column(Text) # Note: is_active is inherited from User base class last_attendance_date = Column(Date) is_on_probation = Column(Boolean, default=False) probation_reason = Column(Text) probation_start_date = Column(Date) probation_end_date = Column(Date) # Add department column for easier department-based queries department = Column(String(100)) # Add speciality relationship - one student belongs to one speciality speciality_id = Column(String(150), ForeignKey('specialities.id'), nullable=False) # Relationships speciality = relationship("Speciality", back_populates="students") enrollments = relationship("Enrollment", back_populates="student") received_reminders = relationship("Reminder", back_populates="student") attendance_records = relationship("Attendance", back_populates="student") progress_records = relationship("CourseProgress", back_populates="student") assignment_submissions = relationship("AssignmentSubmission", back_populates="student") academic_sessions = relationship("AcademicSession", secondary=student_academic_session_association, back_populates="students") exam_evidence = relationship("ExamEvidence", back_populates="student") __mapper_args__ = { 'polymorphic_identity': UserType.student } class Speciality(Base): """Speciality model for organizing courses by specialization""" __tablename__ = 'specialities' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) name = Column(String(200), unique=True, nullable=False) description = Column(Text) department = Column(String(100)) # Basic metadata fields code = Column(String(50)) abbreviation = Column(String(20)) contact_email = Column(String(120)) contact_phone = Column(String(50)) website = Column(String(255)) location = Column(String(100)) # Accreditation and level fields accreditation_body = Column(String(200)) modules = Column(String(200)) speciality_level = Column(String(100)) # Level 1-7 module_level = Column(String(100)) # Module 1-5 is_active = Column(Boolean, default=True) created_at = Column(DateTime, default=datetime.utcnow) updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) # Relationships courses = relationship("Course", back_populates="speciality") academic_sessions = relationship("AcademicSession", back_populates="speciality") shared_courses = relationship("Course", secondary=course_speciality_association, back_populates="shared_specialities") students = relationship("Student", back_populates="speciality") enrollments = relationship("Enrollment", back_populates="speciality") __table_args__ = ( CheckConstraint('name IS NOT NULL AND name != ""', name='valid_speciality_name'), ) def get_qualification_display_name(self): """Get a formatted display name for the speciality""" if self.qualification_type and self.level: return f"{self.name} - {self.qualification_type} ({self.level})" elif self.qualification_type: return f"{self.name} - {self.qualification_type}" elif self.level: return f"{self.name} ({self.level})" else: return self.name def get_display_name(self): """Get a formatted display name for the speciality""" if self.speciality_level: return f"{self.name} ({self.speciality_level})" else: return self.name def get_level_display(self): """Get formatted level display""" if self.speciality_level and self.module_level: return f"{self.speciality_level} | {self.module_level}" elif self.speciality_level: return self.speciality_level elif self.module_level: return self.module_level else: return "Level not specified" class Course(Base): """Enhanced Course model with resources and assignments""" __tablename__ = 'courses' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) code = Column(String(20), unique=True, nullable=False) title = Column(String(200), nullable=False) description = Column(Text) credits = Column(Integer) department = Column(String(100)) # Primary department (for backward compatibility) is_active = Column(Boolean, default=True) max_students = Column(Integer) created_at = Column(DateTime, default=datetime.utcnow) supervisor_id = Column(String(150), ForeignKey('supervisors.id')) # Fixed: Changed from Integer to String semester = Column(String(20)) learning_outcomes = Column(Text) required_materials = Column(Text) assessment_method = Column(Text) syllabus = Column(Text) # Syllabus content or link total_hours = Column(Integer) # Total course hours is_archived = Column(Boolean, default=False) archive_date = Column(DateTime) course_level = Column(String(20)) # Level 3, 4, 5, 6 course_module = Column(String(20)) # Module 1 to Module 7 language = Column(String(50), default='English') certification_available = Column(Boolean, default=False) has_practical = Column(Boolean, default=False) # Whether the course has practical/lab sessions speciality_id = Column(String(150), ForeignKey('specialities.id'), nullable=True) academic_session_id = Column(String(150), ForeignKey('academic_sessions.id'), nullable=True) # New fields for shared courses is_shared_course = Column(Boolean, default=False) # Whether this course is shared across departments shared_course_type = Column(String(50), default='department_specific') # department_specific, common, cross_department, interdisciplinary sharing_level = Column(String(50), default='single') # single, multiple, all supervisor = relationship("Supervisor", back_populates="managed_courses") speciality = relationship("Speciality", back_populates="courses") tutors = relationship("Tutor", secondary=tutor_course_association, back_populates="courses") supervisors = relationship("Supervisor", secondary=supervisor_course_association, back_populates="courses") sessions = relationship("TeachingSession", back_populates="course") enrollments = relationship("Enrollment", secondary="enrollment_courses", back_populates="courses") timetable_blocks = relationship("TimetableBlock", back_populates="course") resources = relationship("CourseResource", back_populates="course") assignments = relationship("Assignment", back_populates="course") modules = relationship("CourseModule", back_populates="course") academic_session = relationship("AcademicSession", back_populates="courses") course_levels = relationship("CourseLevel", back_populates="course", cascade="all, delete-orphan") # Relationship for shared course departments shared_departments = relationship( "CourseDepartment", back_populates="course", cascade="all, delete-orphan" ) # Relationship for shared course specialities (for interdisciplinary courses) shared_specialities = relationship( "Speciality", secondary=course_speciality_association, back_populates="shared_courses" ) __table_args__ = ( CheckConstraint('credits > 0', name='positive_credits'), CheckConstraint('max_students > 0', name='positive_max_students'), ) def get_primary_department(self): """Get the primary department for this course""" if self.is_shared_course: for dept in self.shared_departments: if dept.is_primary_department and dept.is_active: return dept.department_name return self.department # Fallback to legacy department field def get_all_departments(self): """Get all active departments for this course""" if self.is_shared_course: return [dept.department_name for dept in self.shared_departments if dept.is_active] return [self.department] if self.department else [] # Fallback to legacy department field class CourseLevel(Base): """Course levels within a course (e.g., Beginner, Intermediate, Advanced)""" __tablename__ = 'course_levels' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) course_id = Column(String(150), ForeignKey('courses.id'), nullable=False) level_name = Column(String(100), nullable=False) # e.g., "Beginner", "Intermediate", "Advanced" level_order = Column(Integer, nullable=False, default=1) # Order within the course description = Column(Text) prerequisites = Column(Text) # Prerequisites for this level learning_objectives = Column(Text) # What students will learn estimated_duration_weeks = Column(Integer, default=1) is_active = Column(Boolean, default=True) created_at = Column(DateTime, default=datetime.utcnow) updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) # Relationships course = relationship("Course", back_populates="course_levels") weeks = relationship("CourseWeek", back_populates="course_level", cascade="all, delete-orphan") resources = relationship("CourseResource", back_populates="level") __table_args__ = ( CheckConstraint('level_order > 0', name='positive_level_order'), CheckConstraint('estimated_duration_weeks > 0', name='positive_duration_weeks'), UniqueConstraint('course_id', 'level_order', name='unique_course_level_order'), ) class CourseWeek(Base): """Weekly structure within a course level""" __tablename__ = 'course_weeks' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) course_level_id = Column(String(150), ForeignKey('course_levels.id'), nullable=False) week_number = Column(Integer, nullable=False) # Week number within the level week_title = Column(String(200), nullable=False) description = Column(Text) learning_outcomes = Column(Text) # What students will achieve this week estimated_hours = Column(Float, default=0.0) start_date = Column(Date) end_date = Column(Date) is_active = Column(Boolean, default=True) created_at = Column(DateTime, default=datetime.utcnow) updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) # Relationships course_level = relationship("CourseLevel", back_populates="weeks") modules = relationship("CourseModule", back_populates="course_week", cascade="all, delete-orphan") __table_args__ = ( CheckConstraint('week_number > 0', name='positive_week_number'), CheckConstraint('estimated_hours >= 0', name='non_negative_hours'), UniqueConstraint('course_level_id', 'week_number', name='unique_level_week_number'), ) class ModuleAssignment(Base): """Assignments within a course module""" __tablename__ = 'module_assignments' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) module_id = Column(String(150), ForeignKey('course_modules.id'), nullable=False) assignment_title = Column(String(200), nullable=False) assignment_description = Column(Text) assignment_type = Column(String(50), default='homework') # homework, project, essay, lab_report, presentation instructions = Column(Text) max_points = Column(Float, default=100.0) due_date = Column(DateTime) submission_format = Column(String(100)) # pdf, docx, txt, etc. is_group_assignment = Column(Boolean, default=False) max_group_size = Column(Integer, default=1) late_submission_allowed = Column(Boolean, default=True) late_penalty_percentage = Column(Float, default=10.0) is_published = Column(Boolean, default=False) is_active = Column(Boolean, default=True) created_at = Column(DateTime, default=datetime.utcnow) updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) # Relationships module = relationship("CourseModule", back_populates="module_assignments") submissions = relationship("AssignmentSubmission", back_populates="module_assignment") __table_args__ = ( CheckConstraint('max_points > 0', name='positive_max_points'), CheckConstraint('max_group_size > 0', name='positive_group_size'), CheckConstraint('late_penalty_percentage >= 0', name='non_negative_penalty'), ) class ReadingMaterial(Base): """Reading materials for course modules""" __tablename__ = 'reading_materials' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) module_id = Column(String(150), ForeignKey('course_modules.id'), nullable=False) title = Column(String(200), nullable=False) material_type = Column(String(50), default='text') # text, pdf, article, book_chapter, website description = Column(Text) content_url = Column(String(500)) # URL to the material file_path = Column(String(500)) # Local file path file_size = Column(Integer) # File size in bytes reading_time_minutes = Column(Integer) # Estimated reading time is_required = Column(Boolean, default=True) is_active = Column(Boolean, default=True) created_at = Column(DateTime, default=datetime.utcnow) updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) # Relationships module = relationship("CourseModule", back_populates="reading_materials") __table_args__ = ( CheckConstraint('reading_time_minutes > 0', name='positive_reading_time'), ) class ModuleQuiz(Base): """Quizzes within course modules or associated with course levels""" __tablename__ = 'module_quizzes' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) module_id = Column(String(150), ForeignKey('course_modules.id'), nullable=True) # Made nullable for level-based quizzes quiz_title = Column(String(200), nullable=False) quiz_description = Column(Text) quiz_type = Column(String(50), default='multiple_choice') # multiple_choice, true_false, short_answer, essay, mixed max_attempts = Column(Integer, default=3) time_limit_minutes = Column(Integer) # Time limit for the quiz max_points = Column(Float, default=100.0) passing_score = Column(Float, default=60.0) is_randomized = Column(Boolean, default=False) show_correct_answers = Column(Boolean, default=True) show_answers_after = Column(String(50), default='immediate') # immediate, after_submission, after_due_date due_date = Column(DateTime) is_published = Column(Boolean, default=False) is_active = Column(Boolean, default=True) created_at = Column(DateTime, default=datetime.utcnow) updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) created_by = Column(String(150), ForeignKey('users.id'), nullable=True) # Track who created the quiz # Relationships module = relationship("CourseModule", back_populates="quizzes") questions = relationship("QuizQuestion", back_populates="quiz", cascade="all, delete-orphan", order_by="QuizQuestion.question_order") attempts = relationship("QuizAttempt", back_populates="quiz", cascade="all, delete-orphan") creator = relationship("User", foreign_keys=[created_by]) __table_args__ = ( CheckConstraint('max_attempts > 0', name='positive_max_attempts'), CheckConstraint('time_limit_minutes IS NULL OR time_limit_minutes > 0', name='positive_time_limit'), CheckConstraint('max_points > 0', name='positive_max_points'), CheckConstraint('passing_score >= 0 AND passing_score <= 100', name='valid_passing_score'), Index('idx_module_quizzes_module_id', 'module_id'), Index('idx_module_quizzes_created_by', 'created_by'), Index('idx_module_quizzes_is_published', 'is_published'), Index('idx_module_quizzes_is_active', 'is_active'), ) class QuizQuestion(Base): """Questions within quizzes""" __tablename__ = 'quiz_questions' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) quiz_id = Column(String(150), ForeignKey('module_quizzes.id', ondelete='CASCADE'), nullable=False) question_text = Column(Text, nullable=False) question_type = Column(String(50), default='multiple_choice') # multiple_choice, true_false, short_answer, essay points = Column(Float, default=1.0) question_order = Column(Integer, nullable=False, default=1) explanation = Column(Text) # Explanation for the correct answer is_active = Column(Boolean, default=True) created_at = Column(DateTime, default=datetime.utcnow) updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) # Relationships quiz = relationship("ModuleQuiz", back_populates="questions") options = relationship("QuizOption", back_populates="question", cascade="all, delete-orphan", order_by="QuizOption.option_order") answers = relationship("QuizAnswer", back_populates="question") __table_args__ = ( CheckConstraint('points > 0', name='positive_points'), CheckConstraint('question_order > 0', name='positive_question_order'), UniqueConstraint('quiz_id', 'question_order', name='unique_quiz_question_order'), Index('idx_quiz_questions_quiz_id', 'quiz_id'), Index('idx_quiz_questions_is_active', 'is_active'), ) class QuizOption(Base): """Answer options for quiz questions""" __tablename__ = 'quiz_options' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) question_id = Column(String(150), ForeignKey('quiz_questions.id', ondelete='CASCADE'), nullable=False) option_text = Column(Text, nullable=False) is_correct = Column(Boolean, default=False) option_order = Column(Integer, nullable=False, default=1) created_at = Column(DateTime, default=datetime.utcnow) # Relationships question = relationship("QuizQuestion", back_populates="options") selected_by_answers = relationship("QuizAnswer", back_populates="selected_option") __table_args__ = ( CheckConstraint('option_order > 0', name='positive_option_order'), UniqueConstraint('question_id', 'option_order', name='unique_question_option_order'), Index('idx_quiz_options_question_id', 'question_id'), Index('idx_quiz_options_is_correct', 'is_correct'), ) class ModuleVideo(Base): """Videos within course modules""" __tablename__ = 'module_videos' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) module_id = Column(String(150), ForeignKey('course_modules.id'), nullable=False) video_title = Column(String(200), nullable=False) video_description = Column(Text) video_url = Column(String(500)) # URL to the video (YouTube, Vimeo, etc.) video_file_path = Column(String(500)) # Local video file path video_duration_seconds = Column(Integer) # Duration in seconds thumbnail_url = Column(String(500)) # Thumbnail image URL video_quality = Column(String(20), default='HD') # SD, HD, Full HD, 4K is_required = Column(Boolean, default=True) is_active = Column(Boolean, default=True) created_at = Column(DateTime, default=datetime.utcnow) updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) # Relationships module = relationship("CourseModule", back_populates="videos") video_progress = relationship("VideoProgress", back_populates="video", cascade="all, delete-orphan") __table_args__ = ( CheckConstraint('video_duration_seconds > 0', name='positive_video_duration'), ) class ModulePractice(Base): """Practice exercises within course modules""" __tablename__ = 'module_practices' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) module_id = Column(String(150), ForeignKey('course_modules.id'), nullable=False) practice_title = Column(String(200), nullable=False) practice_description = Column(Text) practice_type = Column(String(50), default='exercise') # exercise, lab, simulation, case_study instructions = Column(Text) estimated_time_minutes = Column(Integer, default=30) max_attempts = Column(Integer, default=5) is_graded = Column(Boolean, default=False) max_points = Column(Float, default=0.0) is_required = Column(Boolean, default=True) is_active = Column(Boolean, default=True) created_at = Column(DateTime, default=datetime.utcnow) updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) # Relationships module = relationship("CourseModule", back_populates="practices") practice_attempts = relationship("PracticeAttempt", back_populates="practice", cascade="all, delete-orphan") __table_args__ = ( CheckConstraint('estimated_time_minutes > 0', name='positive_estimated_time'), CheckConstraint('max_attempts > 0', name='positive_max_attempts'), CheckConstraint('max_points >= 0', name='non_negative_max_points'), ) # Student Progress Tracking Models class QuizAttempt(Base): """Student attempts at quizzes""" __tablename__ = 'quiz_attempts' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) quiz_id = Column(String(150), ForeignKey('module_quizzes.id', ondelete='CASCADE'), nullable=False) student_id = Column(String(150), ForeignKey('students.id'), nullable=False) attempt_number = Column(Integer, nullable=False, default=1) started_at = Column(DateTime, default=datetime.utcnow) completed_at = Column(DateTime) time_spent_minutes = Column(Integer) total_score = Column(Float, default=0.0) max_possible_score = Column(Float, default=0.0) percentage_score = Column(Float, default=0.0) is_passed = Column(Boolean, default=False) is_completed = Column(Boolean, default=False) is_submitted = Column(Boolean, default=False) is_graded = Column(Boolean, default=False) created_at = Column(DateTime, default=datetime.utcnow) # Relationships quiz = relationship("ModuleQuiz", back_populates="attempts") student = relationship("Student") answers = relationship("QuizAnswer", back_populates="attempt", cascade="all, delete-orphan") security_logs = relationship("QuizSecurityLog", back_populates="attempt", cascade="all, delete-orphan") screenshots = relationship("QuizScreenshot", back_populates="attempt", cascade="all, delete-orphan") malpractice_reports = relationship("QuizMalpracticeReport", back_populates="attempt", cascade="all, delete-orphan") webcam_recordings = relationship("QuizWebcamRecording", back_populates="attempt", cascade="all, delete-orphan") __table_args__ = ( CheckConstraint('attempt_number > 0', name='positive_attempt_number'), CheckConstraint('time_spent_minutes IS NULL OR time_spent_minutes >= 0', name='non_negative_time_spent'), CheckConstraint('total_score >= 0', name='non_negative_total_score'), CheckConstraint('max_possible_score >= 0', name='non_negative_max_possible_score'), CheckConstraint('percentage_score >= 0 AND percentage_score <= 100', name='valid_percentage_score'), UniqueConstraint('quiz_id', 'student_id', 'attempt_number', name='unique_quiz_attempt'), Index('idx_quiz_attempts_quiz_id', 'quiz_id'), Index('idx_quiz_attempts_student_id', 'student_id'), Index('idx_quiz_attempts_is_completed', 'is_completed'), ) class QuizAnswer(Base): """Student answers to quiz questions""" __tablename__ = 'quiz_answers' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) attempt_id = Column(String(150), ForeignKey('quiz_attempts.id', ondelete='CASCADE'), nullable=False) question_id = Column(String(150), ForeignKey('quiz_questions.id'), nullable=False) selected_option_id = Column(String(150), ForeignKey('quiz_options.id'), nullable=True) # For multiple choice answer_text = Column(Text) # For text-based answers is_correct = Column(Boolean, default=False) points_earned = Column(Float, default=0.0) answered_at = Column(DateTime, default=datetime.utcnow) graded_by = Column(String(150), ForeignKey('users.id'), nullable=True) # For manual grading graded_at = Column(DateTime) # When manually graded grading_notes = Column(Text) # Tutor feedback for manual grading # Relationships attempt = relationship("QuizAttempt", back_populates="answers") question = relationship("QuizQuestion", back_populates="answers") selected_option = relationship("QuizOption", back_populates="selected_by_answers") grader = relationship("User", foreign_keys=[graded_by]) __table_args__ = ( CheckConstraint('points_earned >= 0', name='non_negative_points_earned'), Index('idx_quiz_answers_attempt_id', 'attempt_id'), Index('idx_quiz_answers_question_id', 'question_id'), Index('idx_quiz_answers_is_correct', 'is_correct'), ) class QuizSecurityLog(Base): """Security event logging for quiz attempts""" __tablename__ = 'quiz_security_logs' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) attempt_id = Column(String(150), ForeignKey('quiz_attempts.id', ondelete='CASCADE'), nullable=False) student_id = Column(String(150), ForeignKey('users.id'), nullable=False) quiz_id = Column(String(150), ForeignKey('module_quizzes.id'), nullable=False) # Security event details event_type = Column(String(50), nullable=False) # tab_switch, window_blur, screenshot, copy_paste, right_click, fullscreen_exit, etc. event_timestamp = Column(DateTime, default=datetime.utcnow, nullable=False) severity = Column(String(20), default='low') # low, medium, high, critical # Technical details ip_address = Column(String(45)) user_agent = Column(Text) browser_info = Column(Text) # Browser name, version os_info = Column(String(100)) # Operating system screen_resolution = Column(String(20)) # e.g., "1920x1080" # Event metadata event_data = Column(Text) # JSON data with event-specific details previous_url = Column(String(500)) # For tab switches duration_seconds = Column(Integer) # How long event lasted # Action taken warning_issued = Column(Boolean, default=False) warning_number = Column(Integer, default=0) # 1, 2, or 3 action_taken = Column(String(50)) # warning, email_sent, quiz_terminated, etc. created_at = Column(DateTime, default=datetime.utcnow) # Relationships attempt = relationship("QuizAttempt", back_populates="security_logs") student = relationship("User", foreign_keys=[student_id]) quiz = relationship("ModuleQuiz") __table_args__ = ( Index('idx_quiz_security_logs_attempt_id', 'attempt_id'), Index('idx_quiz_security_logs_student_id', 'student_id'), Index('idx_quiz_security_logs_event_type', 'event_type'), Index('idx_quiz_security_logs_severity', 'severity'), Index('idx_quiz_security_logs_timestamp', 'event_timestamp'), ) class QuizScreenshot(Base): """Screenshots captured during quiz attempts""" __tablename__ = 'quiz_screenshots' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) attempt_id = Column(String(150), ForeignKey('quiz_attempts.id', ondelete='CASCADE'), nullable=False) student_id = Column(String(150), ForeignKey('users.id'), nullable=False) quiz_id = Column(String(150), ForeignKey('module_quizzes.id'), nullable=False) # Screenshot details file_path = Column(String(500), nullable=False) file_name = Column(String(255), nullable=False) file_size = Column(Integer) # in bytes mime_type = Column(String(50), default='image/png') # Capture details captured_at = Column(DateTime, default=datetime.utcnow, nullable=False) capture_reason = Column(String(100), default='interval') # interval, security_event, random question_number = Column(Integer) # Which question student was on time_remaining_seconds = Column(Integer) # Time left in quiz # Analysis (for future AI-based proctoring) face_detected = Column(Boolean, default=None) multiple_faces = Column(Boolean, default=None) phone_detected = Column(Boolean, default=None) analysis_data = Column(Text) # JSON with AI analysis results # Storage is_encrypted = Column(Boolean, default=True) is_archived = Column(Boolean, default=False) archived_at = Column(DateTime) retention_days = Column(Integer, default=90) # Keep for 90 days created_at = Column(DateTime, default=datetime.utcnow) # Relationships attempt = relationship("QuizAttempt", back_populates="screenshots") student = relationship("User", foreign_keys=[student_id]) quiz = relationship("ModuleQuiz") __table_args__ = ( Index('idx_quiz_screenshots_attempt_id', 'attempt_id'), Index('idx_quiz_screenshots_student_id', 'student_id'), Index('idx_quiz_screenshots_captured_at', 'captured_at'), ) class QuizMalpracticeReport(Base): """Formal malpractice reports sent to tutors""" __tablename__ = 'quiz_malpractice_reports' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) attempt_id = Column(String(150), ForeignKey('quiz_attempts.id', ondelete='CASCADE'), nullable=False) student_id = Column(String(150), ForeignKey('users.id'), nullable=False) quiz_id = Column(String(150), ForeignKey('module_quizzes.id'), nullable=False) course_id = Column(String(150), ForeignKey('courses.id'), nullable=False) tutor_id = Column(String(150), ForeignKey('users.id'), nullable=False) # Report details report_type = Column(String(50), default='automatic') # automatic, manual, appeal severity = Column(String(20), default='medium') # low, medium, high, critical total_violations = Column(Integer, default=0) violation_summary = Column(Text) # Summary of all violations # Email notification email_sent = Column(Boolean, default=False) email_sent_at = Column(DateTime) email_recipient = Column(String(255)) # Tutor email email_subject = Column(String(500)) email_body = Column(Text) # Actions taken quiz_terminated = Column(Boolean, default=False) grade_penalty_applied = Column(Boolean, default=False) penalty_percentage = Column(Float, default=0.0) # Review reviewed_by = Column(String(150), ForeignKey('users.id'), nullable=True) reviewed_at = Column(DateTime) review_notes = Column(Text) final_action = Column(String(100)) # dismissed, warning_issued, grade_reduced, retake_required, etc. created_at = Column(DateTime, default=datetime.utcnow) updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) # Relationships attempt = relationship("QuizAttempt", back_populates="malpractice_reports") student = relationship("User", foreign_keys=[student_id]) quiz = relationship("ModuleQuiz") course = relationship("Course") tutor = relationship("User", foreign_keys=[tutor_id]) reviewer = relationship("User", foreign_keys=[reviewed_by]) __table_args__ = ( Index('idx_quiz_malpractice_attempt_id', 'attempt_id'), Index('idx_quiz_malpractice_student_id', 'student_id'), Index('idx_quiz_malpractice_tutor_id', 'tutor_id'), Index('idx_quiz_malpractice_severity', 'severity'), Index('idx_quiz_malpractice_email_sent', 'email_sent'), ) class QuizWebcamRecording(Base): """Webcam recordings captured during quiz attempts""" __tablename__ = 'quiz_webcam_recordings' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) attempt_id = Column(String(150), ForeignKey('quiz_attempts.id', ondelete='CASCADE'), nullable=False) student_id = Column(String(150), ForeignKey('users.id'), nullable=False) quiz_id = Column(String(150), ForeignKey('module_quizzes.id'), nullable=False) # Video file details file_path = Column(String(500), nullable=False) file_name = Column(String(255), nullable=False) file_size = Column(Integer) # in bytes mime_type = Column(String(50), default='video/webm') duration_seconds = Column(Integer) # Total duration # Recording details started_at = Column(DateTime, default=datetime.utcnow, nullable=False) stopped_at = Column(DateTime) recording_status = Column(String(20), default='active') # active, completed, failed, paused chunk_count = Column(Integer, default=0) # Number of video chunks received # Quality settings video_width = Column(Integer) video_height = Column(Integer) video_bitrate = Column(Integer) frame_rate = Column(Integer) # Analysis (for future AI-based proctoring) faces_detected_count = Column(Integer, default=0) multiple_faces_detected = Column(Boolean, default=False) no_face_duration_seconds = Column(Integer, default=0) # Time with no face visible looking_away_duration_seconds = Column(Integer, default=0) suspicious_movements_count = Column(Integer, default=0) analysis_data = Column(Text) # JSON with detailed AI analysis proctoring_score = Column(Float) # 0-100, higher is better # Storage management is_encrypted = Column(Boolean, default=True) is_archived = Column(Boolean, default=False) archived_at = Column(DateTime) retention_days = Column(Integer, default=90) is_processed = Column(Boolean, default=False) # For AI processing processed_at = Column(DateTime) created_at = Column(DateTime, default=datetime.utcnow) updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) # Relationships attempt = relationship("QuizAttempt", back_populates="webcam_recordings") student = relationship("User", foreign_keys=[student_id]) quiz = relationship("ModuleQuiz") __table_args__ = ( Index('idx_quiz_webcam_attempt_id', 'attempt_id'), Index('idx_quiz_webcam_student_id', 'student_id'), Index('idx_quiz_webcam_status', 'recording_status'), Index('idx_quiz_webcam_started_at', 'started_at'), ) class ExamTimetable(Base): """Exam timetable for CDACC and KNEC courses""" __tablename__ = 'exam_timetables' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) title = Column(String(200), nullable=False) description = Column(Text) department = Column(String(100), nullable=False) accreditation_body = Column(String(50), nullable=False) # CDACC or KNEC exam_period = Column(String(100), nullable=False) # e.g., "December 2024", "May 2025" academic_year = Column(String(20), nullable=False) # e.g., "2024/2025" # Exam dates and times start_date = Column(Date, nullable=False) end_date = Column(Date, nullable=False) exam_duration_hours = Column(Integer, default=3) # Default 3 hours per exam # Status and approval status = Column(String(20), default='draft') # draft, published, archived is_published = Column(Boolean, default=False) published_at = Column(DateTime) published_by = Column(String(150), ForeignKey('supervisors.id')) # Creation and updates created_by = Column(String(150), ForeignKey('supervisors.id'), nullable=False) created_at = Column(DateTime, default=datetime.utcnow) updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) updated_by = Column(String(150), ForeignKey('supervisors.id')) # Additional metadata total_exams = Column(Integer, default=0) total_students = Column(Integer, default=0) notes = Column(Text) is_active = Column(Boolean, default=True) # Relationships creator = relationship("Supervisor", foreign_keys=[created_by], back_populates="created_exam_timetables") publisher = relationship("Supervisor", foreign_keys=[published_by]) updater = relationship("Supervisor", foreign_keys=[updated_by]) exam_sessions = relationship("ExamSession", back_populates="timetable", cascade="all, delete-orphan") __table_args__ = ( CheckConstraint('accreditation_body IN ("CDACC", "KNEC")', name='valid_accreditation_body'), CheckConstraint('status IN ("draft", "published", "archived")', name='valid_timetable_status'), CheckConstraint('start_date <= end_date', name='valid_date_range'), CheckConstraint('exam_duration_hours > 0', name='positive_exam_duration'), Index('idx_exam_timetables_department', 'department'), Index('idx_exam_timetables_accreditation', 'accreditation_body'), Index('idx_exam_timetables_status', 'status'), Index('idx_exam_timetables_period', 'exam_period'), Index('idx_exam_timetables_dates', 'start_date', 'end_date'), ) class ExamSession(Base): """Individual exam sessions within a timetable""" __tablename__ = 'exam_sessions' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) timetable_id = Column(String(150), ForeignKey('exam_timetables.id', ondelete='CASCADE'), nullable=False) course_id = Column(String(150), ForeignKey('courses.id'), nullable=False) # Exam details exam_title = Column(String(200), nullable=False) exam_code = Column(String(50), nullable=False) exam_type = Column(String(50), default='written') # written, practical, oral, project exam_level = Column(String(20)) # Level 3, 4, 5, 6 exam_module = Column(String(20)) # Module 1-7 # Date and time exam_date = Column(Date, nullable=False) start_time = Column(Time, nullable=False) end_time = Column(Time, nullable=False) duration_minutes = Column(Integer, nullable=False) # Venue and logistics venue = Column(String(200), nullable=False) room_capacity = Column(Integer) invigilator_required = Column(Boolean, default=True) invigilator_count = Column(Integer, default=1) # Student management expected_students = Column(Integer, default=0) registered_students = Column(Integer, default=0) max_students = Column(Integer) # Status status = Column(String(20), default='scheduled') # scheduled, ongoing, completed, cancelled is_confirmed = Column(Boolean, default=False) confirmed_at = Column(DateTime) confirmed_by = Column(String(150), ForeignKey('supervisors.id')) # Additional details instructions = Column(Text) materials_required = Column(Text) special_requirements = Column(Text) notes = Column(Text) # Creation and updates created_at = Column(DateTime, default=datetime.utcnow) updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) created_by = Column(String(150), ForeignKey('supervisors.id'), nullable=False) updated_by = Column(String(150), ForeignKey('supervisors.id')) # Relationships timetable = relationship("ExamTimetable", back_populates="exam_sessions") course = relationship("Course") creator = relationship("Supervisor", foreign_keys=[created_by]) updater = relationship("Supervisor", foreign_keys=[updated_by]) confirmer = relationship("Supervisor", foreign_keys=[confirmed_by]) student_registrations = relationship("ExamStudentRegistration", back_populates="exam_session", cascade="all, delete-orphan") __table_args__ = ( CheckConstraint('exam_type IN ("written", "practical", "oral", "project")', name='valid_exam_type'), CheckConstraint('status IN ("scheduled", "ongoing", "completed", "cancelled")', name='valid_session_status'), CheckConstraint('start_time < end_time', name='valid_time_range'), CheckConstraint('duration_minutes > 0', name='positive_duration'), CheckConstraint('expected_students >= 0', name='non_negative_expected_students'), CheckConstraint('registered_students >= 0', name='non_negative_registered_students'), UniqueConstraint('timetable_id', 'exam_code', name='unique_exam_code_per_timetable'), Index('idx_exam_sessions_timetable_id', 'timetable_id'), Index('idx_exam_sessions_course_id', 'course_id'), Index('idx_exam_sessions_date', 'exam_date'), Index('idx_exam_sessions_status', 'status'), Index('idx_exam_sessions_venue', 'venue'), ) class ExamStudentRegistration(Base): """Student registrations for exam sessions""" __tablename__ = 'exam_student_registrations' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) exam_session_id = Column(String(150), ForeignKey('exam_sessions.id', ondelete='CASCADE'), nullable=False) student_id = Column(String(150), ForeignKey('students.id'), nullable=False) # Registration details registration_date = Column(DateTime, default=datetime.utcnow) registration_status = Column(String(20), default='registered') # registered, confirmed, cancelled seat_number = Column(String(10)) # Attendance tracking attendance_status = Column(String(20)) # present, absent, late attendance_time = Column(DateTime) attendance_notes = Column(Text) # Additional information special_needs = Column(Text) emergency_contact = Column(String(200)) notes = Column(Text) # Creation and updates created_at = Column(DateTime, default=datetime.utcnow) updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) created_by = Column(String(150), ForeignKey('supervisors.id'), nullable=False) # Relationships exam_session = relationship("ExamSession", back_populates="student_registrations") student = relationship("Student") creator = relationship("Supervisor", foreign_keys=[created_by]) __table_args__ = ( CheckConstraint('registration_status IN ("registered", "confirmed", "cancelled")', name='valid_registration_status'), CheckConstraint('attendance_status IN ("present", "absent", "late")', name='valid_attendance_status'), UniqueConstraint('exam_session_id', 'student_id', name='unique_student_exam_registration'), Index('idx_exam_registrations_session_id', 'exam_session_id'), Index('idx_exam_registrations_student_id', 'student_id'), Index('idx_exam_registrations_status', 'registration_status'), ) class VideoProgress(Base): """Student progress tracking for videos""" __tablename__ = 'video_progress' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) video_id = Column(String(150), ForeignKey('module_videos.id'), nullable=False) student_id = Column(String(150), ForeignKey('students.id'), nullable=False) progress_percentage = Column(Float, default=0.0) # 0-100% time_watched_seconds = Column(Integer, default=0) last_position_seconds = Column(Integer, default=0) is_completed = Column(Boolean, default=False) completed_at = Column(DateTime) last_watched_at = Column(DateTime, default=datetime.utcnow) created_at = Column(DateTime, default=datetime.utcnow) updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) # Relationships video = relationship("ModuleVideo", back_populates="video_progress") student = relationship("Student") __table_args__ = ( CheckConstraint('progress_percentage >= 0 AND progress_percentage <= 100', name='valid_progress_percentage'), CheckConstraint('time_watched_seconds >= 0', name='non_negative_time_watched'), CheckConstraint('last_position_seconds >= 0', name='non_negative_last_position'), UniqueConstraint('video_id', 'student_id', name='unique_video_student_progress'), ) class PracticeAttempt(Base): """Student attempts at practice exercises""" __tablename__ = 'practice_attempts' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) practice_id = Column(String(150), ForeignKey('module_practices.id'), nullable=False) student_id = Column(String(150), ForeignKey('students.id'), nullable=False) attempt_number = Column(Integer, nullable=False, default=1) started_at = Column(DateTime, default=datetime.utcnow) completed_at = Column(DateTime) time_spent_minutes = Column(Integer) score = Column(Float, default=0.0) max_possible_score = Column(Float, default=0.0) is_completed = Column(Boolean, default=False) feedback = Column(Text) # Feedback from instructor or system created_at = Column(DateTime, default=datetime.utcnow) # Relationships practice = relationship("ModulePractice", back_populates="practice_attempts") student = relationship("Student") __table_args__ = ( CheckConstraint('attempt_number > 0', name='positive_attempt_number'), CheckConstraint('time_spent_minutes >= 0', name='non_negative_time_spent'), CheckConstraint('score >= 0', name='non_negative_score'), CheckConstraint('max_possible_score >= 0', name='non_negative_max_possible_score'), UniqueConstraint('practice_id', 'student_id', 'attempt_number', name='unique_practice_attempt'), ) class ModuleProgress(Base): """Student progress tracking for course modules""" __tablename__ = 'module_progress' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) module_id = Column(String(150), ForeignKey('course_modules.id'), nullable=False) student_id = Column(String(150), ForeignKey('students.id'), nullable=False) enrollment_id = Column(String(150), ForeignKey('enrollments.id'), nullable=False) completion_percentage = Column(Float, default=0.0) # 0-100% is_started = Column(Boolean, default=False) is_completed = Column(Boolean, default=False) started_at = Column(DateTime) completed_at = Column(DateTime) last_accessed_at = Column(DateTime, default=datetime.utcnow) time_spent_minutes = Column(Integer, default=0) created_at = Column(DateTime, default=datetime.utcnow) updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) # Relationships module = relationship("CourseModule") student = relationship("Student") enrollment = relationship("Enrollment") __table_args__ = ( CheckConstraint('completion_percentage >= 0 AND completion_percentage <= 100', name='valid_completion_percentage'), CheckConstraint('time_spent_minutes >= 0', name='non_negative_time_spent'), UniqueConstraint('module_id', 'student_id', 'enrollment_id', name='unique_module_student_progress'), ) def get_all_departments(self): """Get all active departments for this course""" if self.is_shared_course: return [dept.department_name for dept in self.shared_departments if dept.is_active] return [self.department] if self.department else [] def add_department(self, department_name, is_primary=False, assigned_by=None, notes=None): """Add a new department to this course""" from .models import CourseDepartment # If this is a primary department, unset other primary departments if is_primary: for dept in self.shared_departments: if dept.is_primary_department: dept.is_primary_department = False new_dept = CourseDepartment( course_id=self.id, department_name=department_name, is_primary_department=is_primary, assigned_by=assigned_by, notes=notes ) return new_dept def remove_department(self, department_name): """Remove a department from this course (soft delete)""" for dept in self.shared_departments: if dept.department_name == department_name: dept.is_active = False return True return False def is_available_to_department(self, department_name): """Check if this course is available to a specific department""" if not self.is_shared_course: return self.department == department_name for dept in self.shared_departments: if dept.department_name == department_name and dept.is_active: return True return False class CourseDepartment(Base): """Many-to-many relationship between courses and departments (for shared courses)""" __tablename__ = 'course_departments' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) course_id = Column(String(150), ForeignKey('courses.id'), nullable=False) department_name = Column(String(100), nullable=False) is_primary_department = Column(Boolean, default=False) assigned_date = Column(Date, default=date.today) assigned_by = Column(String(150), ForeignKey('supervisors.id'), nullable=True) notes = Column(Text) is_active = Column(Boolean, default=True) created_at = Column(DateTime, default=datetime.utcnow) updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) # Relationships course = relationship("Course", back_populates="shared_departments") assigned_by_supervisor = relationship("Supervisor", foreign_keys=[assigned_by]) __table_args__ = ( CheckConstraint('department_name != ""', name='valid_department_name'), Index('idx_course_department_course_id', 'course_id'), Index('idx_course_department_department_name', 'department_name'), ) def to_json(self): return { "id": self.id, "course_id": self.course_id, "department_name": self.department_name, "is_primary_department": self.is_primary_department, "assigned_date": self.assigned_date, "assigned_by": self.assigned_by, "notes": self.notes, "is_active": self.is_active, "created_at": self.created_at, "updated_at": self.updated_at, } class CourseModule(Base): """Course modules or units - Enhanced for detailed progress tracking""" __tablename__ = 'course_modules' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) course_id = Column(String(150), ForeignKey('courses.id')) # Fixed: Changed from Integer to String course_week_id = Column(String(150), ForeignKey('course_weeks.id'), nullable=True) # New: Link to course week title = Column(String(200), nullable=False) description = Column(Text) sequence = Column(Integer) # Order in the course module_type = Column(String(50), default='lecture') # lecture, practical, lab, workshop, seminar estimated_duration_minutes = Column(Integer, default=60) learning_objectives = Column(Text) is_required = Column(Boolean, default=True) start_date = Column(Date) end_date = Column(Date) is_published = Column(Boolean, default=False) is_active = Column(Boolean, default=True) created_at = Column(DateTime, default=datetime.utcnow) updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) objectives = Column(Text) estimated_study_time = Column(Integer) # in hours is_assessment_module = Column(Boolean, default=False) # Relationships course = relationship("Course", back_populates="modules") course_week = relationship("CourseWeek", back_populates="modules") resources = relationship("CourseResource", back_populates="module") legacy_assignments = relationship("Assignment", back_populates="module") # Renamed to avoid conflict module_assignments = relationship("ModuleAssignment", back_populates="module", cascade="all, delete-orphan") reading_materials = relationship("ReadingMaterial", back_populates="module", cascade="all, delete-orphan") quizzes = relationship("ModuleQuiz", back_populates="module", cascade="all, delete-orphan") videos = relationship("ModuleVideo", back_populates="module", cascade="all, delete-orphan") practices = relationship("ModulePractice", back_populates="module", cascade="all, delete-orphan") __table_args__ = ( CheckConstraint('sequence > 0', name='positive_sequence'), CheckConstraint('estimated_study_time > 0', name='positive_study_time'), CheckConstraint('estimated_duration_minutes > 0', name='positive_duration'), ) class CourseResource(Base): """Learning resources for courses""" __tablename__ = 'course_resources' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) course_id = Column(String(150), ForeignKey('courses.id')) # Fixed: Changed from Integer to String module_id = Column(String(150), ForeignKey('course_modules.id'), nullable=True) # Fixed: Changed from Integer to String level_id = Column(String(150), ForeignKey('course_levels.id'), nullable=True) # Added: For level association title = Column(String(200), nullable=False) description = Column(Text) resource_type = Column(Enum(ResourceType), nullable=False) url = Column(String(255)) # For external resources file_path = Column(String(255)) # For uploaded files file_size = Column(Integer) # In bytes duration = Column(Integer) # For videos/audio in seconds is_published = Column(Boolean, default=True) created_at = Column(DateTime, default=datetime.utcnow) created_by = Column(String(150), ForeignKey('tutors.id')) # Fixed: Changed from Integer to String views = Column(Integer, default=0) downloads = Column(Integer, default=0) license = Column(String(100)) source = Column(String(200)) is_required = Column(Boolean, default=True) difficulty_level = Column(String(20)) # beginner, intermediate, advanced course = relationship("Course", back_populates="resources") module = relationship("CourseModule", back_populates="resources") level = relationship("CourseLevel", back_populates="resources") creator = relationship("Tutor") __table_args__ = ( CheckConstraint('file_size >= 0', name='non_negative_file_size'), CheckConstraint('duration >= 0', name='non_negative_duration'), CheckConstraint('views >= 0', name='non_negative_views'), CheckConstraint('downloads >= 0', name='non_negative_downloads'), ) class Assignment(Base): """Course assignments""" __tablename__ = 'assignments' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) course_id = Column(String(150), ForeignKey('courses.id')) # Fixed: Changed from Integer to String module_id = Column(String(150), ForeignKey('course_modules.id'), nullable=True) # Fixed: Changed from Integer to String title = Column(String(200), nullable=False) description = Column(Text) assignment_type = Column(Enum(AssignmentType), nullable=False) total_points = Column(Float, nullable=False) due_date = Column(DateTime) created_at = Column(DateTime, default=datetime.utcnow) created_by = Column(String(150), ForeignKey('tutors.id')) # Fixed: Changed from Integer to String is_published = Column(Boolean, default=False) submission_instructions = Column(Text) allowed_formats = Column(String(100)) # e.g., pdf,docx,zip max_attempts = Column(Integer, default=1) # For quizzes time_limit = Column(Integer) # In minutes, for timed assignments is_group_assignment = Column(Boolean, default=False) plagiarism_check_enabled = Column(Boolean, default=True) late_submission_penalty = Column(Float, default=0.0) # Percentage penalty per day feedback_type = Column(String(50)) # rubric, comments, audio, video rubric_id = Column(String(150)) # ID of grading rubric if used course = relationship("Course", back_populates="assignments") module = relationship("CourseModule", back_populates="legacy_assignments") # Updated to match new relationship name creator = relationship("Tutor") submissions = relationship("AssignmentSubmission", back_populates="assignment") resources = relationship("AssignmentResource", back_populates="assignment") __table_args__ = ( CheckConstraint('total_points > 0', name='positive_points'), CheckConstraint('max_attempts > 0', name='positive_attempts'), CheckConstraint('time_limit > 0', name='positive_time_limit'), CheckConstraint('late_submission_penalty >= 0', name='non_negative_penalty'), ) class AssignmentResource(Base): """Resources attached to assignments""" __tablename__ = 'assignment_resources' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) assignment_id = Column(String(150), ForeignKey('assignments.id')) # Fixed: Changed from Integer to String title = Column(String(200), nullable=False) description = Column(Text) resource_type = Column(Enum(ResourceType), nullable=False) url = Column(String(255)) file_path = Column(String(255)) file_size = Column(Integer) is_required = Column(Boolean, default=True) created_at = Column(DateTime, default=datetime.utcnow) sample_solution = Column(Boolean, default=False) grading_criteria = Column(Text) assignment = relationship("Assignment", back_populates="resources") __table_args__ = ( CheckConstraint('file_size >= 0', name='non_negative_assignment_file_size'), ) class AssignmentSubmission(Base): """Student submissions for assignments""" __tablename__ = 'assignment_submissions' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) assignment_id = Column(String(150), ForeignKey('assignments.id')) # Fixed: Changed from Integer to String module_assignment_id = Column(String(150), ForeignKey('module_assignments.id')) # New module assignment reference student_id = Column(String(150), ForeignKey('students.id')) # Fixed: Changed from Integer to String status = Column(Enum(SubmissionStatus), default=SubmissionStatus.not_started) submitted_at = Column(DateTime) grade = Column(Float) feedback = Column(Text) graded_by = Column(String(150), ForeignKey('tutors.id')) # Fixed: Changed from Integer to String graded_at = Column(DateTime) attempt_number = Column(Integer, default=1) late_days = Column(Integer) submission_text = Column(Text) # For text-based submissions file_path = Column(String(255)) # For file submissions file_size = Column(Integer) ip_address = Column(String(50)) # For tracking submission origin similarity_score = Column(Float) # Plagiarism check result comments = Column(Text) # Student comments with submission is_draft = Column(Boolean, default=False) submission_metadata = Column(Text) # JSON with additional data assignment = relationship("Assignment", back_populates="submissions") module_assignment = relationship("ModuleAssignment", back_populates="submissions") student = relationship("Student", back_populates="assignment_submissions") grader = relationship("Tutor") feedback_responses = relationship("FeedbackResponse", back_populates="submission") __table_args__ = ( CheckConstraint('attempt_number > 0', name='positive_attempt_number'), CheckConstraint('late_days >= 0', name='non_negative_late_days'), CheckConstraint('file_size >= 0', name='non_negative_submission_file_size'), CheckConstraint('similarity_score >= 0 AND similarity_score <= 100', name='valid_similarity_score'), ) class FeedbackResponse(Base): """Student responses to assignment feedback""" __tablename__ = 'feedback_responses' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) submission_id = Column(String(150), ForeignKey('assignment_submissions.id')) # Fixed: Changed from Integer to String response_text = Column(Text) created_at = Column(DateTime, default=datetime.utcnow) is_read = Column(Boolean, default=False) read_at = Column(DateTime) tutor_response = Column(Text) tutor_response_at = Column(DateTime) is_closed = Column(Boolean, default=False) submission = relationship("AssignmentSubmission", back_populates="feedback_responses") # Association table for many-to-many relationship between Enrollment and Course enrollment_courses = Table( 'enrollment_courses', Base.metadata, Column('enrollment_id', String(150), ForeignKey('enrollments.id'), primary_key=True), Column('course_id', String(150), ForeignKey('courses.id'), primary_key=True), Column('enrollment_date', Date, default=date.today), Column('status', String(20), default='active'), Column('grade', String(2)), Column('attendance_percentage', Float), Column('midterm_grade', String(2)), Column('final_exam_grade', String(2)), Column('project_grade', String(2)), Column('is_audit', Boolean, default=False), Column('completion_date', Date), Column('withdrawal_date', Date), Column('withdrawal_reason', Text) ) class Enrollment(Base): """Student enrollment in a speciality with multiple courses""" __tablename__ = 'enrollments' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) student_id = Column(String(150), ForeignKey('students.id'), nullable=False) speciality_id = Column(String(150), ForeignKey('specialities.id'), nullable=False) enrollment_date = Column(Date, default=date.today) status = Column(String(20), default='active') completion_date = Column(Date) comments = Column(Text) enrollment_type = Column(String(20), default='regular') # regular, credit, non-credit withdrawal_date = Column(Date) withdrawal_reason = Column(Text) term = Column(String(20)) # Term 1, Term 2, Term 3, or null for all terms student = relationship("Student", back_populates="enrollments") speciality = relationship("Speciality", back_populates="enrollments") courses = relationship("Course", secondary="enrollment_courses", back_populates="enrollments") progress = relationship("CourseProgress", uselist=False, back_populates="enrollment") __table_args__ = ( CheckConstraint('status IN ("active", "completed", "withdrawn", "suspended")', name='valid_enrollment_status'), ) class OpeningSessionStatus(PyEnum): scheduled = "scheduled" active = "active" closed = "closed" cancelled = "cancelled" class AvailabilityOpeningSession(Base): """Availability opening sessions - controls when tutors can create availability slots""" __tablename__ = 'availability_opening_sessions' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) name = Column(String(200), nullable=False) description = Column(Text) start_datetime = Column(DateTime, nullable=False) end_datetime = Column(DateTime, nullable=False) is_active = Column(Boolean, default=True) status = Column(String(20), default='scheduled') # scheduled, active, closed, cancelled created_by = Column(String(150), ForeignKey('supervisors.id'), nullable=False) created_at = Column(DateTime, default=datetime.utcnow) updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) # Session settings max_slots_per_tutor = Column(Integer, default=10) # Maximum slots a tutor can create allowed_availability_types = Column(Text) # JSON array of allowed types: ["teaching", "office_hours", "research"] allowed_days = Column(Text) # JSON array of allowed days: [0,1,2,3,4] (Monday-Friday) time_range_start = Column(Time, default=time(8, 0)) # Earliest time allowed (08:00) time_range_end = Column(Time, default=time(18, 0)) # Latest time allowed (18:00) min_slot_duration = Column(Integer, default=60) # Minimum slot duration in minutes max_slot_duration = Column(Integer, default=240) # Maximum slot duration in minutes # Notification settings notify_tutors = Column(Boolean, default=True) notification_message = Column(Text) reminder_sent = Column(Boolean, default=False) reminder_sent_at = Column(DateTime) # Relationships creator = relationship("Supervisor", back_populates="created_opening_sessions", foreign_keys=[created_by]) tutor_availabilities = relationship("TutorAvailability", back_populates="opening_session") __table_args__ = ( CheckConstraint('end_datetime > start_datetime', name='valid_opening_session_duration'), CheckConstraint('max_slots_per_tutor > 0', name='positive_max_slots'), CheckConstraint('min_slot_duration > 0', name='positive_min_duration'), CheckConstraint('max_slot_duration > 0', name='positive_max_duration'), CheckConstraint('max_slot_duration >= min_slot_duration', name='valid_duration_range'), ) @property def is_currently_open(self) -> bool: """Check if the opening session is currently active""" now = datetime.utcnow() return ( self.is_active and self.status == OpeningSessionStatus.active and self.start_datetime <= now <= self.end_datetime ) @property def is_upcoming(self) -> bool: """Check if the opening session is scheduled for the future""" now = datetime.utcnow() return ( self.is_active and self.status == OpeningSessionStatus.scheduled and self.start_datetime > now ) @property def is_expired(self) -> bool: """Check if the opening session has ended""" now = datetime.utcnow() return ( self.end_datetime < now or self.status == OpeningSessionStatus.closed or self.status == OpeningSessionStatus.cancelled ) def get_allowed_types(self) -> list: """Get the list of allowed availability types""" if not self.allowed_availability_types: return ["teaching", "office_hours", "research", "meeting", "other"] try: import json return json.loads(self.allowed_availability_types) except (json.JSONDecodeError, TypeError): return ["teaching", "office_hours", "research", "meeting", "other"] def get_allowed_days(self) -> list: """Get the list of allowed days (0-6, Monday-Sunday)""" if not self.allowed_days: return [0, 1, 2, 3, 4] # Monday to Friday try: import json return json.loads(self.allowed_days) except (json.JSONDecodeError, TypeError): return [0, 1, 2, 3, 4] # Monday to Friday def can_tutor_create_slots(self, tutor_id: str) -> bool: """Check if a tutor can create slots in this opening session""" if not self.is_currently_open: return False # Check if tutor has reached the maximum slots limit from sqlalchemy.orm import object_session session = object_session(self) if session: existing_slots = session.query(TutorAvailability).filter( TutorAvailability.tutor_id == tutor_id, TutorAvailability.opening_session_id == self.id ).count() return existing_slots < self.max_slots_per_tutor return True class TutorAvailability(Base): """Enhanced Tutor availability system with opening session control""" __tablename__ = 'tutor_availability' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) tutor_id = Column(String(150), ForeignKey('tutors.id')) # Fixed: Changed from Integer to String opening_session_id = Column(String(150), ForeignKey('availability_opening_sessions.id'), nullable=True) # New field day_of_week = Column(Integer, nullable=False) # 0-6 (Monday-Sunday) start_time = Column(Time, nullable=False) end_time = Column(Time, nullable=False) is_recurring = Column(Boolean, default=True) valid_from = Column(Date) valid_to = Column(Date) created_at = Column(DateTime, default=datetime.utcnow) is_approved = Column(Boolean, default=False) # Supervisor approval approval_date = Column(DateTime) approved_by = Column(String(150), ForeignKey('supervisors.id')) # Fixed: Changed from Integer to String availability_type = Column(String(20)) # teaching, office_hours, research, etc. location = Column(String(100)) # physical location if applicable notes = Column(Text) is_cancelled = Column(Boolean, default=False) cancellation_reason = Column(Text) cancellation_date = Column(DateTime) # Relationships tutor = relationship("Tutor", back_populates="availabilities") approver = relationship("Supervisor", foreign_keys=[approved_by]) opening_session = relationship("AvailabilityOpeningSession", back_populates="tutor_availabilities") __table_args__ = ( CheckConstraint('day_of_week BETWEEN 0 AND 6', name='valid_day_of_week'), CheckConstraint('end_time > start_time', name='end_after_start'), CheckConstraint("availability_type IN ('teaching', 'office_hours', 'research', 'meeting', 'other')", name='valid_availability_type'), ) class SupervisorAvailability(Base): """Enhanced Supervisor availability system""" __tablename__ = 'supervisor_availability' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) supervisor_id = Column(String(150), ForeignKey('supervisors.id')) day_of_week = Column(Integer, nullable=False) # 0-6 (Monday-Sunday) start_time = Column(Time, nullable=False) end_time = Column(Time, nullable=False) is_recurring = Column(Boolean, default=True) valid_from = Column(Date) valid_to = Column(Date) created_at = Column(DateTime, default=datetime.utcnow) is_approved = Column(Boolean, default=True) # Supervisors are auto-approved approval_date = Column(DateTime) approved_by = Column(String(150), ForeignKey('supervisors.id')) availability_type = Column(String(20)) # teaching, office_hours, research, etc. location = Column(String(100)) # physical location if applicable notes = Column(Text) is_cancelled = Column(Boolean, default=False) cancellation_reason = Column(Text) cancellation_date = Column(DateTime) supervisor = relationship("Supervisor", back_populates="availabilities", foreign_keys=[supervisor_id]) approver = relationship("Supervisor", foreign_keys=[approved_by]) __table_args__ = ( CheckConstraint('day_of_week BETWEEN 0 AND 6', name='valid_supervisor_day_of_week'), CheckConstraint('end_time > start_time', name='valid_supervisor_end_after_start'), CheckConstraint("availability_type IN ('teaching', 'office_hours', 'research', 'meeting', 'other')", name='valid_supervisor_availability_type'), ) class TimetableBlock(Base): """Blocks of time allocated to courses in the timetable""" __tablename__ = 'timetable_blocks' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) tutor_id = Column(String(150), ForeignKey('tutors.id'), nullable=True) # Fixed: Changed from Integer to String, made nullable supervisor_tutor_id = Column(String(150), ForeignKey('supervisors.id'), nullable=True) # New field for supervisor tutors course_id = Column(String(150), ForeignKey('courses.id')) # Fixed: Changed from Integer to String timetable_id = Column(String(150), ForeignKey('timetable.id')) # Fixed: Changed from Integer to String day_of_week = Column(Integer, nullable=False) start_time = Column(Time, nullable=False) end_time = Column(Time, nullable=False) room = Column(String(50)) block_type = Column(String(20)) # lecture, lab, tutorial, etc. recurring = Column(Boolean, default=True) start_date = Column(Date) end_date = Column(Date) created_at = Column(DateTime, default=datetime.utcnow) created_by = Column(String(150), ForeignKey('supervisors.id')) # Fixed: Changed from Integer to String is_cancelled = Column(Boolean, default=False) cancellation_reason = Column(Text) notes = Column(Text) # Additional notes for the block (e.g., shared course information) replacement_tutor_id = Column(String(150), ForeignKey('tutors.id'), nullable=True) # Fixed: Changed from Integer to String replacement_supervisor_tutor_id = Column(String(150), ForeignKey('supervisors.id'), nullable=True) # New field for replacement supervisor tutors replacement_room = Column(String(50)) # Relationships course = relationship("Course", back_populates="timetable_blocks") timetable = relationship("Timetable", back_populates="blocks") creator = relationship("Supervisor", foreign_keys=[created_by]) tutor = relationship( "Tutor", foreign_keys=[tutor_id], back_populates="timetable_blocks" ) supervisor_tutor = relationship( "Supervisor", foreign_keys=[supervisor_tutor_id], back_populates="timetable_blocks" ) replacement_tutor = relationship( "Tutor", foreign_keys=[replacement_tutor_id] ) replacement_supervisor_tutor = relationship( "Supervisor", foreign_keys=[replacement_supervisor_tutor_id] ) __table_args__ = ( CheckConstraint('day_of_week BETWEEN 0 AND 6', name='valid_day_of_week'), CheckConstraint('end_time > start_time', name='end_after_start_time'), CheckConstraint("block_type IN ('lecture', 'lab', 'tutorial', 'seminar', 'workshop')", name='valid_block_type'), ) class TeachingSession(Base): """Teaching session model for individual tutor sessions""" __tablename__ = 'teaching_session' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) timetable_id = Column(String(150), ForeignKey('timetable.id'), nullable=False) tutor_id = Column(String(150), ForeignKey('users.id'), nullable=False) course_id = Column(String(150), ForeignKey('courses.id'), nullable=False) title = Column(String(255), nullable=True) # Session title/description room = Column(String(100), nullable=False) day_of_week = Column(Integer, nullable=False) # 0=Monday, 1=Tuesday, etc. start_time = Column(Time, nullable=False) end_time = Column(Time, nullable=False) session_type = Column(String(50), default='Lecture') recurring = Column(Boolean, default=True) created_at = Column(DateTime, default=datetime.utcnow) updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) status = Column(String(20), default='scheduled') # scheduled, ongoing, completed, cancelled notes = Column(Text) # New fields for verification system verified_by = Column(String(150), ForeignKey('supervisors.id'), nullable=True) verified_at = Column(DateTime, nullable=True) verification_method = Column(String(50), nullable=True) created_by = Column(String(150), ForeignKey('supervisors.id'), nullable=True) is_verified = Column(Boolean, default=False) # Whether the session has been verified # Supervisor tutor relationship supervisor_tutor_id = Column(String(150), ForeignKey('supervisors.id'), nullable=True) # Relationships timetable = relationship("Timetable", back_populates="teaching_sessions") reminders = relationship("Reminder", back_populates="session") verifier = relationship("Supervisor", foreign_keys=[verified_by]) creator = relationship("Supervisor", foreign_keys=[created_by]) supervisor_tutor = relationship("Supervisor", foreign_keys=[supervisor_tutor_id]) attendance_records = relationship("Attendance", back_populates="session") daily_sessions = relationship("DailyTeachingSession", back_populates="teaching_session") # Core relationships tutor = relationship("User", foreign_keys=[tutor_id]) course = relationship("Course", foreign_keys=[course_id]) __table_args__ = ( CheckConstraint(day_of_week.in_([0, 1, 2, 3, 4, 5, 6]), name='valid_day_of_week'), CheckConstraint(status.in_(['scheduled', 'ongoing', 'completed', 'cancelled']), name='valid_session_status'), ) class Notification(Base): """Notification system for users""" __tablename__ = 'notifications' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) user_id = Column(String(150), ForeignKey('users.id'), nullable=False) title = Column(String(255), nullable=False) message = Column(Text, nullable=False) type = Column(String(50), nullable=False) data = Column(Text) # JSON string for additional data is_read = Column(Boolean, default=False) created_at = Column(DateTime, default=datetime.utcnow) # Relationships user = relationship("User") __table_args__ = ( Index('idx_notifications_user_id', 'user_id'), Index('idx_notifications_type', 'type'), Index('idx_notifications_created_at', 'created_at'), ) class NotificationPreference(Base): """Notification preferences for users""" __tablename__ = 'notification_preferences' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) user_id = Column(String(150), ForeignKey('users.id'), unique=True, nullable=False) receive_email = Column(Boolean, default=True) receive_sms = Column(Boolean, default=False) receive_push = Column(Boolean, default=True) email_notification_time = Column(Integer, default=24) # hours before event sms_notification_time = Column(Integer, default=2) # hours before event push_notification_time = Column(Integer, default=1) # hours before event notify_attendance_updates = Column(Boolean, default=True) notify_grade_updates = Column(Boolean, default=True) notify_schedule_changes = Column(Boolean, default=True) notify_system_messages = Column(Boolean, default=True) created_at = Column(DateTime, default=datetime.utcnow) updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) # Relationships user = relationship("User", back_populates="notification_preference") __table_args__ = ( Index('idx_notification_preferences_user_id', 'user_id'), ) class Attendance(Base): """Attendance records for teaching sessions""" __tablename__ = 'attendance' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) session_id = Column(String(150), ForeignKey('teaching_session.id'), nullable=True) # Made nullable for daily sessions daily_session_id = Column(String(150), ForeignKey('daily_teaching_sessions.id'), nullable=True) # New field for daily sessions makeup_session_id = Column(String(150), ForeignKey('makeup_sessions.id'), nullable=True) # New field for makeup sessions student_id = Column(String(150), ForeignKey('students.id'), nullable=False) tutor_id = Column(String(150), ForeignKey('tutors.id'), nullable=False) status = Column(Enum(AttendanceStatus), default=AttendanceStatus.present) timestamp = Column(DateTime, default=datetime.utcnow) notes = Column(Text) late_minutes = Column(Integer, default=0) verification_method = Column(Enum(TeachingVerificationMethod)) device_used = Column(String(255)) ip_address = Column(String(45)) created_at = Column(DateTime, default=datetime.utcnow) updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) # Supervisor tutor relationship supervisor_tutor_id = Column(String(150), ForeignKey('supervisors.id'), nullable=True) # Relationships session = relationship("TeachingSession", back_populates="attendance_records") daily_session = relationship("DailyTeachingSession", back_populates="attendance_records") makeup_session = relationship("MakeupSession", back_populates="attendance_records") student = relationship("Student") tutor = relationship("Tutor") supervisor_tutor = relationship("Supervisor", foreign_keys=[supervisor_tutor_id]) @classmethod def safe_query(cls, session, include_relationships=False): """ Create a query that safely handles missing columns in the database. This is a temporary solution until migrations are applied. """ try: if include_relationships: # Try to query all columns including relationships return session.query(cls).options( joinedload(cls.session), joinedload(cls.student), joinedload(cls.tutor) ) else: # Query only basic columns that are guaranteed to exist return session.query( cls.id, cls.session_id, cls.student_id, cls.tutor_id, cls.status, cls.timestamp, cls.notes, cls.late_minutes, cls.verification_method, cls.device_used, cls.ip_address, cls.created_at, cls.updated_at ) except Exception: # Fallback to minimal columns if any column is missing return session.query( cls.id, cls.session_id, cls.student_id, cls.tutor_id, cls.status ) __table_args__ = ( Index('idx_attendance_session_id', 'session_id'), Index('idx_attendance_daily_session_id', 'daily_session_id'), Index('idx_attendance_makeup_session_id', 'makeup_session_id'), Index('idx_attendance_student_id', 'student_id'), Index('idx_attendance_tutor_id', 'tutor_id'), Index('idx_attendance_timestamp', 'timestamp'), ) class Timetable(Base): """Master timetable system""" __tablename__ = 'timetable' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) created_by = Column(String(150), ForeignKey('supervisors.id'), nullable=False) approved_by = Column(String(150), ForeignKey('supervisors.id'), nullable=True) name = Column(String(100), nullable=False) description = Column(Text) created_at = Column(DateTime, default=datetime.utcnow) updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) is_active = Column(Boolean, default=True) semester = Column(String(20)) term = Column(String(20)) academic_year = Column(String(20)) approval_status = Column(String(20), default='draft') # draft, pending, approved, rejected status = Column(String(20), default='draft') # active, draft, archived, pending approved_at = Column(DateTime) version = Column(String(20), default='1.0') department = Column(String(100)) speciality = Column(String(100)) academic_session_id = Column(String(150), ForeignKey('academic_sessions.id'), nullable=True) room_assignments = Column(Integer, default=0) conflict_count = Column(Integer, default=0) completion_rate = Column(Float, default=0.0) blocks_count = Column(Integer, default=0) # New fields for verification system is_verified = Column(Boolean, default=False) verified_at = Column(DateTime) verified_by = Column(String(150), ForeignKey('supervisors.id')) verification_notes = Column(Text) email_sent = Column(Boolean, default=False) email_sent_at = Column(DateTime) email_recipients = Column(Text) # JSON string of recipient emails # Relationships creator = relationship("Supervisor", foreign_keys=[created_by], back_populates="created_timetables") approver = relationship("Supervisor", foreign_keys=[approved_by], back_populates="approved_timetables") verifier = relationship("Supervisor", foreign_keys=[verified_by]) blocks = relationship("TimetableBlock", back_populates="timetable", cascade='all, delete-orphan') teaching_sessions = relationship("TeachingSession", back_populates="timetable", cascade='all, delete-orphan') academic_session = relationship("AcademicSession", back_populates="timetables") __table_args__ = ( CheckConstraint("approval_status IN ('draft', 'pending', 'approved', 'rejected')", name='valid_approval_status'), CheckConstraint("status IN ('active', 'draft', 'archived', 'pending')", name='valid_status'), CheckConstraint('completion_rate >= 0 AND completion_rate <= 100', name='valid_completion_rate'), Index('idx_timetable_created_by', 'created_by'), Index('idx_timetable_status', 'status'), Index('idx_timetable_approval_status', 'approval_status'), Index('idx_timetable_academic_year', 'academic_year'), Index('idx_timetable_term', 'term'), ) class CourseProgress(Base): """Course progress tracking for students""" __tablename__ = 'course_progress' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) enrollment_id = Column(String(150), ForeignKey('enrollments.id'), nullable=False) student_id = Column(String(150), ForeignKey('students.id'), nullable=False) completion_percentage = Column(Float, default=0.0) attendance_rate = Column(Float, default=0.0) last_accessed = Column(DateTime) current_grade = Column(String(2)) notes = Column(Text) assignments_completed = Column(Integer, default=0) assignments_pending = Column(Integer, default=0) last_assignment_date = Column(Date) next_assignment_due = Column(Date) overall_performance = Column(String(20)) # excellent, good, average, poor created_at = Column(DateTime, default=datetime.utcnow) updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) # Relationships enrollment = relationship("Enrollment", back_populates="progress") student = relationship("Student", back_populates="progress_records") __table_args__ = ( CheckConstraint('completion_percentage >= 0 AND completion_percentage <= 100', name='valid_completion_percentage'), CheckConstraint('attendance_rate >= 0 AND attendance_rate <= 100', name='valid_attendance_rate'), CheckConstraint('assignments_completed >= 0', name='non_negative_completed'), CheckConstraint('assignments_pending >= 0', name='non_negative_pending'), Index('idx_course_progress_enrollment_id', 'enrollment_id'), Index('idx_course_progress_student_id', 'student_id'), ) class Reminder(Base): """Reminders system for users""" __tablename__ = 'reminders' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) session_id = Column(String(150), ForeignKey('teaching_session.id'), nullable=True) tutor_id = Column(String(150), ForeignKey('tutors.id'), nullable=True) supervisor_id = Column(String(150), ForeignKey('supervisors.id'), nullable=True) student_id = Column(String(150), ForeignKey('students.id'), nullable=True) message = Column(Text, nullable=False) sent_at = Column(DateTime, default=datetime.utcnow) delivery_status = Column(String(20), default='pending') # pending, sent, failed, delivered delivery_method = Column(String(20)) # email, sms, push, system scheduled_send_time = Column(DateTime) read_status = Column(Boolean, default=False) read_at = Column(DateTime) reminder_type = Column(String(20)) # session, assignment, payment, etc. is_recurring = Column(Boolean, default=False) recurrence_pattern = Column(String(50)) # daily, weekly, monthly created_at = Column(DateTime, default=datetime.utcnow) # Relationships session = relationship("TeachingSession", back_populates="reminders") tutor = relationship("Tutor", back_populates="reminders_sent") supervisor = relationship("Supervisor", back_populates="reminders_sent") student = relationship("Student", back_populates="received_reminders") @classmethod def safe_query(cls, session, include_relationships=False): """ Create a query that safely handles missing columns in the database. This is a temporary solution until migrations are applied. """ try: if include_relationships: # Try to query all columns including relationships return session.query(cls).options( joinedload(cls.session), joinedload(cls.tutor), joinedload(cls.supervisor), joinedload(cls.student) ) else: # Query only basic columns that are guaranteed to exist return session.query( cls.id, cls.session_id, cls.tutor_id, cls.supervisor_id, cls.student_id, cls.message, cls.sent_at, cls.delivery_status, cls.delivery_method, cls.scheduled_send_time, cls.read_status, cls.read_at, cls.reminder_type, cls.is_recurring, cls.recurrence_pattern ) except Exception: # Fallback to minimal columns if any column is missing return session.query( cls.id, cls.session_id, cls.message, cls.sent_at, cls.delivery_status, cls.reminder_type ) __table_args__ = ( CheckConstraint("delivery_status IN ('pending', 'sent', 'failed', 'delivered')", name='valid_delivery_status'), CheckConstraint("delivery_method IN ('email', 'sms', 'push', 'system')", name='valid_delivery_method'), Index('idx_reminders_session_id', 'session_id'), Index('idx_reminders_tutor_id', 'tutor_id'), Index('idx_reminders_supervisor_id', 'supervisor_id'), Index('idx_reminders_student_id', 'student_id'), Index('idx_reminders_scheduled_send_time', 'scheduled_send_time'), ) class AcademicSession(Base): """Academic session model to track academic years and terms""" __tablename__ = 'academic_sessions' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) name = Column(String(100), nullable=False) year = Column(String(20), nullable=False) speciality_id = Column(String(150), ForeignKey('specialities.id'), nullable=False) start_date = Column(Date, nullable=False) end_date = Column(Date, nullable=False) is_active = Column(Boolean, default=True) status = Column(String(20), default='active') description = Column(Text) created_at = Column(DateTime, default=datetime.utcnow) updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) # Relationships speciality = relationship("Speciality", back_populates="academic_sessions") courses = relationship("Course", back_populates="academic_session") timetables = relationship("Timetable", back_populates="academic_session") students = relationship("Student", secondary=student_academic_session_association, back_populates="academic_sessions") __table_args__ = ( CheckConstraint("status IN ('active', 'completed', 'cancelled')", name='valid_academic_session_status'), CheckConstraint('start_date < end_date', name='valid_session_dates'), Index('idx_academic_sessions_speciality_id', 'speciality_id'), Index('idx_academic_sessions_year', 'year'), Index('idx_academic_sessions_status', 'status'), Index('idx_academic_sessions_active', 'is_active'), ) class DailyTeachingSession(Base): """Daily teaching session model for tracking sessions on specific dates""" __tablename__ = 'daily_teaching_sessions' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) timetable_block_id = Column(String(150), ForeignKey('timetable_blocks.id'), nullable=False) teaching_session_id = Column(String(150), ForeignKey('teaching_session.id'), nullable=True) # Link to TeachingSession course_id = Column(String(150), ForeignKey('courses.id'), nullable=False) tutor_id = Column(String(150), ForeignKey('users.id'), nullable=False) session_date = Column(Date, nullable=False) # The actual date this session occurs start_time = Column(Time, nullable=False) # Time of day (e.g., 14:00:00) end_time = Column(Time, nullable=False) # Time of day (e.g., 16:00:00) room = Column(String(100), nullable=True) session_type = Column(String(50), default='lecture') status = Column(String(20), default='scheduled') # scheduled, in_progress, completed, cancelled # Tracking fields created_at = Column(DateTime, default=datetime.utcnow) updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) created_by = Column(String(150), ForeignKey('supervisors.id'), nullable=True) # Verification fields is_verified = Column(Boolean, default=False) verified_by = Column(String(150), ForeignKey('supervisors.id'), nullable=True) verified_at = Column(DateTime, nullable=True) verification_method = Column(String(50), nullable=True) # Additional info notes = Column(Text) attendance_taken = Column(Boolean, default=False) # Relationships timetable_block = relationship("TimetableBlock") teaching_session = relationship("TeachingSession", foreign_keys=[teaching_session_id]) course = relationship("Course") tutor = relationship("User", foreign_keys=[tutor_id]) creator = relationship("Supervisor", foreign_keys=[created_by]) verifier = relationship("Supervisor", foreign_keys=[verified_by]) attendance_records = relationship("Attendance", back_populates="daily_session") makeup_sessions = relationship("MakeupSession", back_populates="daily_session") @classmethod def safe_query(cls, session, include_relationships=False): """ Create a query that safely handles missing columns in the database. This is a temporary solution until migrations are applied. """ try: if include_relationships: # Try to query all columns including relationships return session.query(cls).options( joinedload(cls.course), joinedload(cls.tutor), joinedload(cls.timetable_block) ) else: # Query only basic columns that are guaranteed to exist return session.query( cls.id, cls.timetable_block_id, cls.course_id, cls.tutor_id, cls.session_date, cls.start_time, cls.end_time, cls.room, cls.session_type, cls.status, cls.created_at, cls.updated_at, cls.created_by, cls.is_verified, cls.verified_by, cls.verified_at, cls.verification_method, cls.notes, cls.attendance_taken ) except Exception: # Fallback to minimal columns if any column is missing return session.query( cls.id, cls.timetable_block_id, cls.course_id, cls.tutor_id, cls.session_date, cls.start_time, cls.end_time, cls.room, cls.session_type, cls.status ) __table_args__ = ( Index('idx_daily_sessions_date', 'session_date'), Index('idx_daily_sessions_tutor_date', 'tutor_id', 'session_date'), Index('idx_daily_sessions_course_date', 'course_id', 'session_date'), Index('idx_daily_sessions_status', 'status'), Index('idx_daily_sessions_block_date', 'timetable_block_id', 'session_date'), # Ensure unique session per block per date UniqueConstraint('timetable_block_id', 'session_date', name='unique_block_date'), ) class MakeupSession(Base): """Makeup session model for scheduling makeup classes""" __tablename__ = 'makeup_sessions' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) daily_session_id = Column(String(150), ForeignKey('daily_teaching_sessions.id'), nullable=True) # Link to DailyTeachingSession timetable_block_id = Column(String(150), ForeignKey('timetable_blocks.id'), nullable=True) # Original timetable block course_id = Column(String(150), ForeignKey('courses.id'), nullable=False) tutor_id = Column(String(150), ForeignKey('users.id'), nullable=False) # Makeup session details session_date = Column(Date, nullable=False) # The makeup session date start_time = Column(Time, nullable=False) # Makeup session start time end_time = Column(Time, nullable=False) # Makeup session end time room = Column(String(100), nullable=True) session_type = Column(String(50), default='lecture') status = Column(String(20), default='scheduled') # scheduled, completed, cancelled, in_progress # Makeup session specific fields reason = Column(String(200), nullable=True) # Reason for makeup session original_session_date = Column(Date, nullable=True) # Original session date that was missed is_dynamic_block = Column(Boolean, default=False) # Whether a dynamic timetable block was created conflict_resolved = Column(Boolean, default=False) # Whether conflicts were resolved # Tracking fields created_at = Column(DateTime, default=datetime.utcnow) updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) created_by = Column(String(150), ForeignKey('users.id'), nullable=True) # Verification fields is_verified = Column(Boolean, default=False) verified_by = Column(String(150), ForeignKey('supervisors.id'), nullable=True) verified_at = Column(DateTime, nullable=True) verification_method = Column(String(50), nullable=True) # Additional info notes = Column(Text) attendance_taken = Column(Boolean, default=False) # Relationships daily_session = relationship("DailyTeachingSession", foreign_keys=[daily_session_id]) timetable_block = relationship("TimetableBlock", foreign_keys=[timetable_block_id]) course = relationship("Course") tutor = relationship("User", foreign_keys=[tutor_id]) creator = relationship("User", foreign_keys=[created_by]) verifier = relationship("Supervisor", foreign_keys=[verified_by]) attendance_records = relationship("Attendance", back_populates="makeup_session") __table_args__ = ( Index('idx_makeup_sessions_date', 'session_date'), Index('idx_makeup_sessions_tutor_date', 'tutor_id', 'session_date'), Index('idx_makeup_sessions_course_date', 'course_id', 'session_date'), Index('idx_makeup_sessions_status', 'status'), Index('idx_makeup_sessions_daily_session', 'daily_session_id'), Index('idx_makeup_sessions_timetable_block', 'timetable_block_id'), Index('idx_makeup_sessions_original_date', 'original_session_date'), # Ensure unique makeup session per tutor per date per time UniqueConstraint('tutor_id', 'session_date', 'start_time', 'end_time', name='unique_makeup_session'), ) # Exam Grade Models class ExamGrade(Base): """Model for storing exam grades""" __tablename__ = 'exam_grades' id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4) student_id = Column(UUID(as_uuid=True), ForeignKey('students.id'), nullable=False) tutor_id = Column(UUID(as_uuid=True), ForeignKey('tutors.id'), nullable=False) course_id = Column(UUID(as_uuid=True), ForeignKey('courses.id'), nullable=False) supervisor_id = Column(UUID(as_uuid=True), ForeignKey('supervisors.id'), nullable=True) # Grade information exam_type = Column(String(100), nullable=False) # e.g., 'Midterm', 'Final', 'Quiz' exam_title = Column(String(255), nullable=False) grade_value = Column(Float, nullable=False) # Numerical grade max_grade = Column(Float, nullable=False, default=100) # Maximum possible grade grade_percentage = Column(Float, nullable=False) # Calculated percentage letter_grade = Column(String(5), nullable=True) # A, B+, C, etc. # Metadata exam_date = Column(DateTime, nullable=False) graded_date = Column(DateTime, default=datetime.utcnow) academic_session = Column(String(100), nullable=True) semester = Column(String(50), nullable=True) # Status and verification is_verified = Column(Boolean, default=False) verified_by = Column(UUID(as_uuid=True), ForeignKey('supervisors.id'), nullable=True) verified_date = Column(DateTime, nullable=True) verification_notes = Column(Text, nullable=True) # Additional information notes = Column(Text, nullable=True) is_active = Column(Boolean, default=True) created_at = Column(DateTime, default=datetime.utcnow) updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) # Relationships student = relationship("Student", backref="exam_grades") tutor = relationship("Tutor", backref="graded_exams") course = relationship("Course", backref="exam_grades") supervisor = relationship("Supervisor", foreign_keys=[supervisor_id], backref="supervised_grades") verifier = relationship("Supervisor", foreign_keys=[verified_by], backref="verified_grades") evidence_files = relationship("ExamEvidence", backref="exam_grade", cascade="all, delete-orphan") __table_args__ = ( Index('idx_exam_grades_student_id', 'student_id'), Index('idx_exam_grades_tutor_id', 'tutor_id'), Index('idx_exam_grades_course_id', 'course_id'), Index('idx_exam_grades_supervisor_id', 'supervisor_id'), Index('idx_exam_grades_is_verified', 'is_verified'), Index('idx_exam_grades_exam_date', 'exam_date'), CheckConstraint("grade_percentage >= 0 AND grade_percentage <= 100", name='valid_grade_percentage'), CheckConstraint("grade_value >= 0", name='valid_grade_value'), CheckConstraint("max_grade > 0", name='valid_max_grade'), ) class GradeAccessLog(Base): """Model for tracking access to grades and evidence files""" __tablename__ = 'grade_access_logs' id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4) exam_grade_id = Column(UUID(as_uuid=True), ForeignKey('exam_grades.id'), nullable=True) exam_evidence_id = Column(UUID(as_uuid=True), ForeignKey('exam_evidence.id'), nullable=True) accessed_by = Column(UUID(as_uuid=True), nullable=False) # User ID user_type = Column(String(50), nullable=False) # student, tutor, supervisor # Access details access_type = Column(String(50), nullable=False) # view, download, modify ip_address = Column(String(45), nullable=True) user_agent = Column(Text, nullable=True) session_id = Column(String(255), nullable=True) # Timestamp accessed_at = Column(DateTime, default=datetime.utcnow) # Relationships exam_grade = relationship("ExamGrade", backref="grade_access_logs") exam_evidence = relationship("ExamEvidence", backref="exam_grade_access_logs") __table_args__ = ( Index('idx_grade_access_logs_accessed_by', 'accessed_by'), Index('idx_grade_access_logs_exam_grade_id', 'exam_grade_id'), Index('idx_grade_access_logs_accessed_at', 'accessed_at'), CheckConstraint("access_type IN ('view', 'download', 'modify', 'upload', 'verify', 'delete', 'create_grade', 'update_grade')", name='valid_access_type'), CheckConstraint("user_type IN ('student', 'tutor', 'supervisor', 'admin')", name='valid_user_type'), ) class GradeStatistics(Base): """Model for storing grade statistics and analytics""" __tablename__ = 'grade_statistics' id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4) course_id = Column(UUID(as_uuid=True), ForeignKey('courses.id'), nullable=False) tutor_id = Column(UUID(as_uuid=True), ForeignKey('tutors.id'), nullable=True) academic_session = Column(String(100), nullable=False) semester = Column(String(50), nullable=False) # Statistics total_students = Column(Integer, default=0) total_exams = Column(Integer, default=0) average_grade = Column(Float, default=0.0) highest_grade = Column(Float, default=0.0) lowest_grade = Column(Float, default=0.0) pass_rate = Column(Float, default=0.0) # Percentage of students who passed # Grade distribution grade_a_count = Column(Integer, default=0) grade_b_count = Column(Integer, default=0) grade_c_count = Column(Integer, default=0) grade_d_count = Column(Integer, default=0) grade_f_count = Column(Integer, default=0) # Verification status verified_grades_count = Column(Integer, default=0) pending_verification_count = Column(Integer, default=0) # Timestamps calculated_at = Column(DateTime, default=datetime.utcnow) last_updated = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) # Relationships course = relationship("Course", backref="grade_statistics") tutor = relationship("Tutor", backref="grade_statistics") __table_args__ = ( Index('idx_grade_statistics_course_id', 'course_id'), Index('idx_grade_statistics_tutor_id', 'tutor_id'), Index('idx_grade_statistics_academic_session', 'academic_session'), CheckConstraint("average_grade >= 0 AND average_grade <= 100", name='valid_average_grade'), CheckConstraint("pass_rate >= 0 AND pass_rate <= 100", name='valid_pass_rate'), UniqueConstraint('course_id', 'tutor_id', 'academic_session', 'semester', name='unique_grade_statistics'), ) # Exam Evidence Upload System Models class ExamEvidenceType(PyEnum): """Types of exam evidence that can be uploaded""" result_certificate = "result_certificate" transcript = "transcript" exam_script = "exam_script" practical_assessment = "practical_assessment" project_submission = "project_submission" portfolio = "portfolio" attendance_record = "attendance_record" other = "other" class ExamEvidenceStatus(PyEnum): """Status of exam evidence upload""" pending = "pending" uploaded = "uploaded" verified = "verified" rejected = "rejected" expired = "expired" archived = "archived" class FileAccessLevel(PyEnum): """Access levels for exam evidence files""" student_only = "student_only" tutor_supervisor = "tutor_supervisor" department_head = "department_head" examination_body = "examination_body" admin_only = "admin_only" class ExamEvidence(Base): """Main model for exam evidence uploads""" __tablename__ = 'exam_evidence' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) exam_grade_id = Column(UUID(as_uuid=True), ForeignKey('exam_grades.id'), nullable=True) student_id = Column(String(150), ForeignKey('students.id'), nullable=False) course_id = Column(String(150), ForeignKey('courses.id'), nullable=True) speciality_id = Column(String(150), ForeignKey('specialities.id'), nullable=True) academic_session_id = Column(String(150), ForeignKey('academic_sessions.id'), nullable=True) # Evidence details evidence_type = Column(Enum(ExamEvidenceType), nullable=False) title = Column(String(200), nullable=False) description = Column(Text) examination_body = Column(String(200)) # Name of the examining body exam_date = Column(Date) result_date = Column(Date) grade_achieved = Column(String(10)) # A, B, C, D, F, Pass, Fail, etc. percentage_score = Column(Float) max_score = Column(Float) # File information file_name = Column(String(255), nullable=False) file_path = Column(String(500), nullable=False) file_size = Column(Integer, nullable=False) # Size in bytes file_type = Column(String(50), nullable=False) # pdf, jpg, png, etc. file_hash = Column(String(64), nullable=False) # SHA-256 hash for integrity mime_type = Column(String(100), nullable=False) # Upload information uploaded_by = Column(String(150), ForeignKey('users.id'), nullable=False) uploaded_at = Column(DateTime, default=datetime.utcnow) upload_ip_address = Column(String(45)) upload_user_agent = Column(String(500)) # Status and verification status = Column(Enum(ExamEvidenceStatus), default=ExamEvidenceStatus.pending) verified_by = Column(String(150), ForeignKey('users.id'), nullable=True) verified_at = Column(DateTime, nullable=True) verification_notes = Column(Text) rejection_reason = Column(Text) # Security and access control access_level = Column(Enum(FileAccessLevel), default=FileAccessLevel.tutor_supervisor) is_encrypted = Column(Boolean, default=True) encryption_key_id = Column(String(150), nullable=True) retention_period_days = Column(Integer, default=2555) # 7 years default expires_at = Column(DateTime, nullable=True) # Metadata is_active = Column(Boolean, default=True) created_at = Column(DateTime, default=datetime.utcnow) updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) # Relationships student = relationship("Student", foreign_keys=[student_id]) course = relationship("Course", foreign_keys=[course_id]) speciality = relationship("Speciality", foreign_keys=[speciality_id]) academic_session = relationship("AcademicSession", foreign_keys=[academic_session_id]) uploader = relationship("User", foreign_keys=[uploaded_by], overlaps="uploaded_exam_evidence,uploaded_exam_evidence") verifier = relationship("User", foreign_keys=[verified_by], overlaps="verified_exam_evidence") access_logs = relationship("ExamEvidenceAccessLog", back_populates="exam_evidence", cascade="all, delete-orphan") file_versions = relationship("ExamEvidenceVersion", back_populates="exam_evidence", cascade="all, delete-orphan") __table_args__ = ( CheckConstraint('file_size > 0', name='positive_file_size'), CheckConstraint('percentage_score >= 0 AND percentage_score <= 100', name='valid_percentage_score'), CheckConstraint('max_score > 0', name='positive_max_score'), CheckConstraint('retention_period_days > 0', name='positive_retention_period'), Index('idx_exam_evidence_student_id', 'student_id'), Index('idx_exam_evidence_course_id', 'course_id'), Index('idx_exam_evidence_uploaded_by', 'uploaded_by'), Index('idx_exam_evidence_status', 'status'), Index('idx_exam_evidence_evidence_type', 'evidence_type'), Index('idx_exam_evidence_uploaded_at', 'uploaded_at'), Index('idx_exam_evidence_file_hash', 'file_hash'), ) class ExamEvidenceVersion(Base): """Track different versions of exam evidence files""" __tablename__ = 'exam_evidence_versions' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) exam_evidence_id = Column(String(150), ForeignKey('exam_evidence.id'), nullable=False) version_number = Column(Integer, nullable=False, default=1) # File information for this version file_name = Column(String(255), nullable=False) file_path = Column(String(500), nullable=False) file_size = Column(Integer, nullable=False) file_type = Column(String(50), nullable=False) file_hash = Column(String(64), nullable=False) mime_type = Column(String(100), nullable=False) # Version details uploaded_by = Column(String(150), ForeignKey('users.id'), nullable=False) uploaded_at = Column(DateTime, default=datetime.utcnow) upload_reason = Column(String(200)) # Why this version was uploaded changes_description = Column(Text) # Security is_encrypted = Column(Boolean, default=True) encryption_key_id = Column(String(150), nullable=True) # Relationships exam_evidence = relationship("ExamEvidence", back_populates="file_versions", foreign_keys=[exam_evidence_id]) uploader = relationship("User", foreign_keys=[uploaded_by]) __table_args__ = ( CheckConstraint('version_number > 0', name='positive_version_number'), CheckConstraint('file_size > 0', name='positive_version_file_size'), UniqueConstraint('exam_evidence_id', 'version_number', name='unique_evidence_version'), Index('idx_exam_evidence_versions_evidence_id', 'exam_evidence_id'), Index('idx_exam_evidence_versions_uploaded_at', 'uploaded_at'), ) class ExamEvidenceAccessLog(Base): """Track all access to exam evidence files for security auditing""" __tablename__ = 'exam_evidence_access_logs' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) exam_evidence_id = Column(String(150), ForeignKey('exam_evidence.id'), nullable=False) user_id = Column(String(150), ForeignKey('users.id'), nullable=False) # Access details access_type = Column(String(50), nullable=False) # view, download, print, share, delete, modify access_timestamp = Column(DateTime, default=datetime.utcnow) ip_address = Column(String(45), nullable=False) user_agent = Column(String(500)) session_id = Column(String(100)) # Access context access_reason = Column(String(200)) # Why the file was accessed access_method = Column(String(50)) # web, api, mobile, batch duration_seconds = Column(Integer) # How long the file was accessed # Security flags is_authorized = Column(Boolean, default=True) security_violation = Column(Boolean, default=False) violation_reason = Column(Text) # Location and device info country = Column(String(100)) city = Column(String(100)) device_type = Column(String(50)) # desktop, mobile, tablet browser = Column(String(100)) os = Column(String(100)) # Relationships exam_evidence = relationship("ExamEvidence", back_populates="access_logs", foreign_keys=[exam_evidence_id]) user = relationship("User", foreign_keys=[user_id]) __table_args__ = ( CheckConstraint("access_type IN ('view', 'download', 'print', 'share', 'delete', 'modify', 'upload', 'verify')", name='valid_access_type'), CheckConstraint("access_method IN ('web', 'api', 'mobile', 'batch', 'system')", name='valid_access_method'), CheckConstraint('duration_seconds >= 0', name='non_negative_duration'), Index('idx_exam_evidence_access_logs_evidence_id', 'exam_evidence_id'), Index('idx_exam_evidence_access_logs_user_id', 'user_id'), Index('idx_exam_evidence_access_logs_timestamp', 'access_timestamp'), Index('idx_exam_evidence_access_logs_ip_address', 'ip_address'), Index('idx_exam_evidence_access_logs_violation', 'security_violation'), ) class ExamEvidencePermission(Base): """Granular permissions for exam evidence access""" __tablename__ = 'exam_evidence_permissions' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) exam_evidence_id = Column(String(150), ForeignKey('exam_evidence.id'), nullable=False) user_id = Column(String(150), ForeignKey('users.id'), nullable=True) # Null for role-based permissions role = Column(String(50), nullable=True) # tutor, supervisor, student, admin, examination_body # Permission flags can_view = Column(Boolean, default=False) can_download = Column(Boolean, default=False) can_print = Column(Boolean, default=False) can_share = Column(Boolean, default=False) can_modify = Column(Boolean, default=False) can_delete = Column(Boolean, default=False) can_verify = Column(Boolean, default=False) # Permission context granted_by = Column(String(150), ForeignKey('users.id'), nullable=False) granted_at = Column(DateTime, default=datetime.utcnow) expires_at = Column(DateTime, nullable=True) is_active = Column(Boolean, default=True) # Permission details permission_reason = Column(Text) conditions = Column(Text) # JSON string with additional conditions # Relationships exam_evidence = relationship("ExamEvidence", foreign_keys=[exam_evidence_id]) user = relationship("User", foreign_keys=[user_id]) granter = relationship("User", foreign_keys=[granted_by]) __table_args__ = ( CheckConstraint('(user_id IS NOT NULL) OR (role IS NOT NULL)', name='user_or_role_required'), Index('idx_exam_evidence_permissions_evidence_id', 'exam_evidence_id'), Index('idx_exam_evidence_permissions_user_id', 'user_id'), Index('idx_exam_evidence_permissions_role', 'role'), Index('idx_exam_evidence_permissions_active', 'is_active'), ) class ExamEvidenceShare(Base): """Track file sharing activities for security auditing""" __tablename__ = 'exam_evidence_shares' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) exam_evidence_id = Column(String(150), ForeignKey('exam_evidence.id'), nullable=False) shared_by = Column(String(150), ForeignKey('users.id'), nullable=False) shared_with = Column(String(150), ForeignKey('users.id'), nullable=True) # Null for public shares shared_with_email = Column(String(120), nullable=True) # For external sharing # Share details share_type = Column(String(50), nullable=False) # internal, external, public, temporary share_token = Column(String(255), nullable=True) # For secure sharing share_url = Column(String(500), nullable=True) access_count = Column(Integer, default=0) max_access_count = Column(Integer, nullable=True) # Share permissions can_view = Column(Boolean, default=True) can_download = Column(Boolean, default=False) can_print = Column(Boolean, default=False) # Share timing shared_at = Column(DateTime, default=datetime.utcnow) expires_at = Column(DateTime, nullable=True) last_accessed_at = Column(DateTime, nullable=True) # Share context share_reason = Column(Text) share_message = Column(Text) # Message sent with the share # Security is_active = Column(Boolean, default=True) is_encrypted = Column(Boolean, default=True) password_protected = Column(Boolean, default=False) password_hash = Column(String(255), nullable=True) # Relationships exam_evidence = relationship("ExamEvidence", foreign_keys=[exam_evidence_id]) sharer = relationship("User", foreign_keys=[shared_by]) recipient = relationship("User", foreign_keys=[shared_with]) __table_args__ = ( CheckConstraint("share_type IN ('internal', 'external', 'public', 'temporary')", name='valid_share_type'), CheckConstraint('access_count >= 0', name='non_negative_access_count'), CheckConstraint('max_access_count IS NULL OR max_access_count > 0', name='positive_max_access_count'), Index('idx_exam_evidence_shares_evidence_id', 'exam_evidence_id'), Index('idx_exam_evidence_shares_shared_by', 'shared_by'), Index('idx_exam_evidence_shares_shared_with', 'shared_with'), Index('idx_exam_evidence_shares_token', 'share_token'), Index('idx_exam_evidence_shares_expires_at', 'expires_at'), ) class ExamEvidenceAudit(Base): """Comprehensive audit trail for exam evidence files""" __tablename__ = 'exam_evidence_audits' id = Column(String(150), primary_key=True, default=lambda: str(uuid.uuid4())) exam_evidence_id = Column(String(150), ForeignKey('exam_evidence.id'), nullable=False) user_id = Column(String(150), ForeignKey('users.id'), nullable=True) # Null for system actions # Audit details action = Column(String(100), nullable=False) # upload, view, download, modify, delete, share, verify, etc. action_timestamp = Column(DateTime, default=datetime.utcnow) old_values = Column(Text) # JSON string of old values new_values = Column(Text) # JSON string of new values # Context ip_address = Column(String(45)) user_agent = Column(String(500)) session_id = Column(String(100)) request_id = Column(String(100)) # For tracing requests # Additional info action_reason = Column(Text) action_result = Column(String(50)) # success, failure, partial error_message = Column(Text) # Relationships exam_evidence = relationship("ExamEvidence", foreign_keys=[exam_evidence_id]) user = relationship("User", foreign_keys=[user_id]) __table_args__ = ( CheckConstraint("action_result IN ('success', 'failure', 'partial')", name='valid_action_result'), Index('idx_exam_evidence_audits_evidence_id', 'exam_evidence_id'), Index('idx_exam_evidence_audits_user_id', 'user_id'), Index('idx_exam_evidence_audits_action', 'action'), Index('idx_exam_evidence_audits_timestamp', 'action_timestamp'), )