app.models

  1# The database for this application was developed with the support of Miguel 
  2# Grinberg's The Flash Mega Tutorial series
  3# https://blog.miguelgrinberg.com/post/the-flask-mega-tutorial-part-iv-database
  4
  5"""Data model definitions for Vision Hub.
  6
  7    All models are defined using SQLAlchemy ORM and represent the database schema 
  8    for the application.
  9
 10    Models include:
 11        - User: Represents a user in the system with self-referential 
 12        relationships for managers.
 13        - Role: Represents user roles (e.g., admin, manager, staff).
 14        - Department: Represents departments within the organization.
 15        - TrainingModule: Represents training modules with associated 
 16        questions and user progress.
 17        - Question: Represents questions within training modules.
 18        - Option: Represents answer options for questions.
 19        - UserModuleProgress: Tracks user progress in training modules.
 20        - UserQuestionAnswer: Represents answers submitted by users to questions.
 21        - OnboardingPath: Represents onboarding paths for different staff types.
 22        - OnboardingStep: Represents steps in an onboarding path, such as 
 23        training modules.
 24        - DocumentRepository: Represents documents in the repository.
 25        - Report: Represents reports generated in the system.
 26""" 
 27from datetime import datetime, timezone
 28from typing import Optional, List
 29
 30from flask_login import UserMixin
 31import sqlalchemy as sa
 32import sqlalchemy.orm as so
 33from sqlalchemy import Boolean
 34from werkzeug.security import generate_password_hash, check_password_hash
 35
 36from app import db, login
 37
 38
 39class User(UserMixin, db.Model):
 40    """
 41    Represents a user in the system.
 42    
 43    Attributes:
 44        id (int): Primary key.
 45        username (str): Unique login identifier (email).
 46        first_name (str): The user's first name.
 47        surname (str): The user's last name.
 48        job_title (str): The user's position title.
 49        password_hash (str): Hashed password for authentication.
 50        is_onboarding (bool): Flag indicating whether the user is in onboarding.
 51        dateStarted (datetime): Timestamp when the user account was created.
 52        google_email (Optional[str]): Optional linked Google account email.
 53        profile_photo (Optional[str]): Optional profile photo filename.
 54        manager_id (Optional[int]): Foreign key to another User (their manager).
 55        manager (User): The user's manager (self-referential relationship).
 56        manages (list[User]): List of users managed by this user.
 57        role_id (int): Foreign key to the Role model.
 58        role (Role): The role assigned to the user.
 59        department_id (int): Foreign key to the Department model.
 60        department (Department): The department assigned to the user.
 61        module_progress (list[UserModuleProgress]): List of training module 
 62            progress entries for this user.
 63        onboarding_path_id (Optional[int]): Foreign key to an OnboardingPath.
 64        onboarding_path (OnboardingPath): The onboarding path assigned to the user.
 65    """
 66    __tablename__ = 'user'
 67
 68    # Primary key
 69    id: so.Mapped[int] = so.mapped_column(primary_key = True)
 70
 71    # User details
 72    username: so.Mapped[str] = so.mapped_column(
 73        sa.String(120), 
 74        index = True, 
 75        unique = True
 76    )
 77    first_name: so.Mapped[str] = so.mapped_column(
 78        sa.String(50), 
 79        index = True
 80    )
 81    surname: so.Mapped[str] = so.mapped_column(
 82        sa.String(50), 
 83        index = True
 84    )
 85    job_title: so.Mapped[str] = so.mapped_column(
 86        sa.String(50), 
 87        index = True
 88    )
 89    password_hash: so.Mapped[str] = so.mapped_column(
 90        sa.String(256)
 91    )
 92    is_onboarding: so.Mapped[bool] = so.mapped_column(
 93        default = False   # Default: Not onboarding
 94    )  
 95    dateStarted: so.Mapped[datetime] = so.mapped_column(
 96        index=True, 
 97        default = lambda: 
 98            datetime.now(timezone.utc)
 99    )
100    google_email: so.Mapped[Optional[str]] = so.mapped_column(
101        sa.String(120), 
102        unique = True, 
103        nullable = True
104    )
105    profile_photo: so.Mapped[Optional[str]] = so.mapped_column(
106        sa.String(120), 
107        nullable = True, 
108        default = 'profileDefault.png'
109    )
110
111    # Self-referential relationship for manager and those managed
112    manager_id: so.Mapped[Optional[int]] = so.mapped_column(
113        sa.ForeignKey('user.id'), 
114        nullable = True
115    )
116    manager: so.Mapped[Optional['User']] = so.relationship(
117        'User', 
118        remote_side=[id], 
119        back_populates = 'manages'
120    )
121    manages: so.Mapped[List['User']] = so.relationship(
122        'User', 
123        back_populates = 'manager'
124    )
125
126    # Relationship to role
127    role_id: so.Mapped[int] = so.mapped_column(
128        sa.ForeignKey('role.id')
129    )
130    role: so.Mapped['Role'] = so.relationship(
131        'Role', 
132        back_populates = 'users'
133    )
134
135    # Relationship to department
136    department_id: so.Mapped[int] = so.mapped_column(
137        sa.ForeignKey('department.id')
138    )
139    department: so.Mapped['Department'] = so.relationship(
140        'Department', 
141        back_populates = 'users'
142    )
143
144    # Relationship to module progress
145    module_progress: so.Mapped[List['UserModuleProgress']] = so.relationship(
146        'UserModuleProgress', back_populates = 'user'
147    )
148
149    # Relationship to onboarding path
150    onboarding_path_id: so.Mapped[int] = so.mapped_column(
151        sa.ForeignKey('onboarding_path.id'), 
152        nullable = True
153    )
154    onboarding_path: so.Mapped['OnboardingPath'] = so.relationship(
155        'OnboardingPath'
156    )
157
158    def __repr__(self):
159        """Returns a string representation of the User object."""
160        return f'<User {self.username}>'
161
162    def set_password(self, password):
163        """Sets the user's password by hashing it."""
164        self.password_hash = generate_password_hash(password)
165
166    def check_password(self, password):
167        """Verifies the user's password matched the stored hashed password."""
168        return check_password_hash(self.password_hash, password)
169
170
171@login.user_loader
172def load_user(id):
173    """Loads a user by their ID for Flask-Login session management.
174
175    Args:
176        id (int): The unique identifier of the user.
177        
178    Returns:
179        User: The User object or None if not found.
180    """
181    return db.session.get(User, int(id))
182
183
184class Role(db.Model):
185    """Represents a role in the system.
186    
187    Attributes:
188        id (int): Primary key.
189        role_name (str): Name of the role (e.g., admin, manager, staff).
190        users (list[User]): List of users assigned to this role (relationship).
191    """
192    __tablename__ = 'role'
193
194    # Primary key
195    id: so.Mapped[int] = so.mapped_column(primary_key=True)
196
197    # Role details
198    role_name: so.Mapped[str] = so.mapped_column(
199        sa.String(20), 
200        index = True, 
201        unique = True
202    )
203
204    # Relationship to User
205    users: so.Mapped[list['User']] = so.relationship(
206        'User', back_populates = 'role'
207    )
208
209    def __repr__(self):
210        """Returns a string representation of the Role object."""
211        return f'<Role {self.role_name}>'
212
213
214class Department(db.Model):
215    """Represents a department in the system.
216    
217    Attributes:
218        id (int): Primary key.
219        department_name (str): Name of the department (e.g., office, operational).
220        users (list[User]): List of users assigned to this department.
221    """
222    __tablename__ = 'department'
223
224    # Primary key
225    id: so.Mapped[int] = so.mapped_column(
226        primary_key = True
227    )
228
229    # Department details
230    department_name: so.Mapped[str] = so.mapped_column(
231        sa.String(20), 
232        index = True, 
233        unique = True
234    )
235
236    # Relationship to User 
237    users: so.Mapped[List['User']] = so.relationship(
238        'User', 
239        back_populates = 'department'
240    )
241
242    def __repr__(self):
243        """Returns a string representation of the Department object."""
244        return f'<Department {self.department_name}>'
245    
246
247class TrainingModule (db.Model):    
248    """Represents a training module in the system.
249
250    Attributes:
251        id (int): Primary key.
252        module_title (str): Title of the module.
253        module_description (str): Detailed description.
254        module_instructions (str): Instructions for completing the module.
255        video_url (Optional[str]): Optional URL for video content.
256        active (bool): Indicates whether the module is currently active.
257        questions (list[Question]): Questions associated with the module.
258        user_progress (list[UserModuleProgress]): Progress entries for users.
259        onboarding_steps (List[OnboardingStep]): Steps in assigned onboarding 
260            paths.
261    """ 
262    __tablename__ = 'training_module'   
263
264    # Primary key
265    id: so.Mapped[int] = so.mapped_column(primary_key=True)
266
267    # Training module details
268    module_title: so.Mapped[str] = so.mapped_column(
269        sa.String(150), index=True, unique=True
270    )
271    module_description: so.Mapped[str] = so.mapped_column(
272        sa.Text, nullable=False
273    )
274    module_instructions: so.Mapped[str] = so.mapped_column(
275        sa.Text, nullable=False
276    )
277    video_url: so.Mapped[str] = so.mapped_column(
278        sa.String(300), nullable=True
279    )
280    active: so.Mapped[bool] = so.mapped_column(
281        sa.Boolean, nullable=False, default=True
282    )
283
284    # Relationship with questions   
285    questions: so.Mapped[List['Question']] = so.relationship(
286        'Question', 
287        back_populates = 'training_module', 
288        cascade = 'all, delete-orphan')
289
290    # Relationship with user progress
291    user_progress: so.Mapped[List['UserModuleProgress']] = so.relationship(
292        'UserModuleProgress', 
293        back_populates = 'training_module'
294    )
295
296    # Relationship with onboarding steps
297    onboarding_steps: so.Mapped[List['OnboardingStep']] = so.relationship(
298        'OnboardingStep', 
299        back_populates = 'training_module',
300        cascade = 'all, delete-orphan'
301        )
302
303    def __repr__(self):
304        """Returns a string representation of the TrainingModule object."""
305        return f'<TrainingModule {self.module_title}>'
306
307
308class Question (db.Model):
309    """Represents a question in the system.
310
311    Attributes:
312        id (int): Primary key.
313        question_text (str): The text of the question.
314        training_module_id (int): Foreign key to TrainingModule.
315        training_module (TrainingModule): Associated training module.
316        options (list[Option]): List of answer choices for this question.
317    """
318    __tablename__ = 'question'
319
320    # Primary key
321    id: so.Mapped[int] = so.mapped_column(primary_key=True)
322
323    # Question details
324    question_text: so.Mapped[str] = so.mapped_column(
325        sa.String(1000), 
326        nullable=False
327    )
328    
329    # Relationship with training module id
330    training_module_id: so.Mapped[int] = so.mapped_column(
331        sa.ForeignKey('training_module.id'), 
332        nullable=False
333    )
334    training_module: so.Mapped['TrainingModule'] = so.relationship(
335        'TrainingModule', 
336        back_populates = 'questions')
337
338    # Relationship with options
339    options: so.Mapped[List['Option']] = so.relationship(
340        'Option', 
341        back_populates = 'question', 
342        cascade='all, delete-orphan'
343    )
344
345    def __repr__(self):
346        """Returns a string representation of the Question object."""
347        return f'<Question {self.question_text}>'
348
349
350class Option (db.Model):
351    """Represents answer options for a question.
352
353    Attributes:
354        id (int): Primary key.
355        question_id (int): Foreign key to the question.
356        option_text (str): The text of this option.
357        is_correct (bool): True if this option is the correct answer.
358        question (Question): The associated question.
359    """
360    __tablename__ = "option"
361
362    # Primary key
363    id: so.Mapped[int] = so.mapped_column(primary_key=True)
364
365    # Option details
366    question_id: so.Mapped[int] = so.mapped_column(
367        sa.ForeignKey('question.id'), 
368        nullable=False
369    )
370    option_text: so.Mapped[str] = so.mapped_column(
371        sa.String(500), 
372        nullable=False
373    )
374    is_correct: so.Mapped[bool] = so.mapped_column(
375        sa.Boolean, 
376        default=False, 
377        nullable=False
378    )
379
380    # Relationship with question
381    question: so.Mapped['Question'] = so.relationship(
382        'Question', 
383        back_populates = 'options'
384    )
385
386    def __repr__(self):
387        """Returns a string representation of the Option object."""
388        return f'<Option {self.option_text}>'
389
390
391class UserModuleProgress (db.Model):
392    """
393    Represents the progress of a user in a training module.
394
395    Attributes:
396        id (int): Primary key.
397        start_date (datetime): When the module was started.
398        completed_date (datetime, optional): When the module was completed.
399        score (int, optional): The score achieved by the user in the module.
400        attempts (int): Number of attempts made by the user.
401        user_id (int): Foreign key to the User model.
402        user (User): The user associated with this progress entry.
403        training_module_id (int): Foreign key to the TrainingModule model.
404        training_module (TrainingModule): The training module associated with 
405            this progress entry.
406        answers (list[UserQuestionAnswer]): Answers submitted during this attempt.
407    """
408    __tablename__ = 'user_module_progress'
409
410    # Primary key
411    id: so.Mapped[int] = so.mapped_column(primary_key=True) 
412
413    # User Module Progress details
414    start_date: so.Mapped[datetime] = so.mapped_column(
415        sa.DateTime, 
416        default = lambda: datetime.now(timezone.utc), 
417        nullable = False
418    )
419    completed_date: so.Mapped[datetime] = so.mapped_column(
420        sa.DateTime, 
421        nullable = True
422    )
423    score: so.Mapped[int] = so.mapped_column(
424        sa.Integer, 
425        nullable = True
426    )
427    attempts: so.Mapped[int] = so.mapped_column(
428        sa.Integer, 
429        default=0, 
430        nullable = False
431    )
432
433    # Relationship to User
434    user_id: so.Mapped[int] = so.mapped_column(
435        sa.ForeignKey('user.id'), 
436        nullable = False
437    )
438    user: so.Mapped['User'] = so.relationship(
439        'User', 
440        back_populates = 'module_progress'
441    )
442
443    # Relationship to Training Module
444    training_module_id: so.Mapped[int] = so.mapped_column(
445        sa.ForeignKey('training_module.id'), 
446        nullable = False
447    )
448    training_module: so.Mapped['TrainingModule'] = so.relationship(
449        'TrainingModule', 
450        back_populates = 'user_progress'
451    )
452
453    # Relationship to User Question Answer
454    answers: so.Mapped[List['UserQuestionAnswer']] = so.relationship(
455        'UserQuestionAnswer', 
456        back_populates = 'progress', 
457        cascade = 'all, delete-orphan'
458    )
459
460
461class UserQuestionAnswer (db.Model):
462    """Represents an answer submitted by a user to a specific question.
463
464    Attributes:
465        id (int): Primary key.
466        is_correct (bool): True if the selected option was correct.
467        progress_id (int): Foreign key to the UserModuleProgress model.
468        progress (UserModuleProgress): Relationship to the user's progress 
469            record.
470        question_id (int): Foreign key to the Question.
471        question (Question): Question associated with this answer.
472        selected_option_id (int, optional): Foreign key to the chosen Option.
473        selected_option (Option, optional): Relationship to the selected 
474            Option (if any).
475    """
476    __tablename__ = 'user_question_answer'
477    
478    # Primary key
479    id: so.Mapped[int] = so.mapped_column(primary_key=True)
480    is_correct: so.Mapped[bool] = so.mapped_column(sa.Boolean)
481    
482    # User Question Answer details
483    progress_id: so.Mapped[int] = so.mapped_column(
484        sa.ForeignKey('user_module_progress.id'), 
485        nullable = False
486    )
487
488    # Relationship to User Module Progress
489    progress: so.Mapped['UserModuleProgress'] = so.relationship(
490        'UserModuleProgress', 
491        back_populates = 'answers'
492    )
493
494    # Relationship to question
495    question_id: so.Mapped[int] = so.mapped_column(
496        sa.ForeignKey('question.id'), 
497        nullable = False
498    )
499    question: so.Mapped['Question'] = so.relationship('Question')
500    
501    # Relationship to option
502    selected_option_id: so.Mapped[int] = so.mapped_column(
503        sa.ForeignKey('option.id'), 
504        nullable = True
505    )
506    selected_option: so.Mapped['Option'] = so.relationship('Option')
507
508
509class OnboardingPath(db.Model):
510    """Represents an onboarding path defining a sequence of steps for staff.
511
512    Attributes:
513        id (int): Primary key.
514        path_name (str): Name of the onboarding path (e.g., “office”, 
515            “operational”).
516        steps (list[OnboardingStep]): Ordered steps in this path.
517    """
518    __tablename__ = 'onboarding_path'
519
520    # Primary key
521    id: so.Mapped[int] = so.mapped_column(primary_key=True)
522
523    #Onboarding path details
524    path_name: so.Mapped[str] = so.mapped_column(
525        sa.String(100), 
526        nullable=False, 
527        unique=True
528    )
529
530    # Relationship with onboarding steps
531    steps: so.Mapped[List['OnboardingStep']] = so.relationship(
532        'OnboardingStep', 
533        back_populates='path', 
534        cascade='all, delete-orphan'
535    )
536
537    def __repr__(self):
538        """Returns a string representation of the Onboarding Path object."""
539        return f"<OnboardingPath {self.path_name}>"
540    
541
542class OnboardingStep(db.Model):
543    """Step within an onboarding path, e.g. a training module or task.
544    
545    Attributes:
546        id (int): Primary key.
547        step_name (str): Name of this step.
548        onboarding_path_id (int): Foreign key to the OnboardingPath.
549        path (OnboardingPath): Relationship back to the parent path.
550        training_module_id (int, optional): Foreign key to the TrainingModule, 
551            if any.
552        training_module (TrainingModule): The training module associated with 
553            this step.
554    """
555    __tablename__ = 'onboarding_step'
556
557    # Primary key
558    id: so.Mapped[int] = so.mapped_column(primary_key = True)
559
560    # Onboarding Step details
561    step_name: so.Mapped[str] = so.mapped_column(
562        sa.String(150), 
563        nullable = False
564    )
565    
566    # Relationship to Onboarding Path
567    onboarding_path_id: so.Mapped[int] = so.mapped_column(
568        sa.ForeignKey('onboarding_path.id'), 
569        nullable = False
570    )
571    path: so.Mapped['OnboardingPath'] = so.relationship(
572        'OnboardingPath', 
573        back_populates = 'steps'
574    )
575
576    # Relationship to training modules
577    training_module_id: so.Mapped[int] = so.mapped_column(
578        sa.ForeignKey('training_module.id'), 
579        nullable = True
580    )
581    training_module: so.Mapped['TrainingModule'] = so.relationship(
582        'TrainingModule', 
583        back_populates = 'onboarding_steps'
584    )
585
586    def __repr__(self):
587        """Returns a string representation of the OnboardingStep object."""
588        return f"<OnboardingStep {self.step_name}>"
589    
590
591class DocumentRepository(db.Model):
592    """Represents a document in the repository.
593
594    Attributes:
595        id (int): Primary key.
596        document_title (str): Title of the document.
597        document_category (str): Category of the document (e.g., Policy, Guide, 
598            Form).
599        upload_date (datetime): Timestamp when uploaded.
600        file_path (str):  Filesystem path to the uploaded file.
601        user_id (int): Foreign key to the uploaders User model.
602        uploaded_by_user (User): The user who uploaded the document.
603    """
604    __tablename__ = 'document_repository'
605
606    # Primary key
607    id: so.Mapped[int] = so.mapped_column(primary_key=True)
608
609    # Repository details
610    document_title: so.Mapped[str] = so.mapped_column(
611        sa.String(150), 
612        nullable = False
613    )
614    document_category: so.Mapped[str] = so.mapped_column(
615        sa.Enum('Policy', 'Guide', 'Form', 
616        name = 'document_category'), 
617        nullable = False
618    )
619    upload_date: so.Mapped[datetime] = so.mapped_column(
620        sa.DateTime, 
621        default=lambda: datetime.now(timezone.utc), 
622        nullable = False
623    )
624    file_path: so.Mapped[str] = so.mapped_column(
625        sa.String(300), 
626        nullable = False
627    )
628
629    # Relationship to User
630    user_id: so.Mapped[int] = so.mapped_column(
631        sa.ForeignKey('user.id'), 
632        nullable = False
633    )
634    uploaded_by_user: so.Mapped['User'] = so.relationship('User')
635
636    def __repr__(self):
637        """Returns a string representation of the Document Repository object."""
638        return f"<DocumentRepository {self.document_title}>"
639
640    
641class Report(db.Model):
642    """Represents a report in the system.
643    
644    Attributes:
645        id (int): Primary key.
646        report_type (str): Type of report (e.g., Completion, Performance).
647        description (str): Short description of the report.
648        report_data (str, optional): Stored Json report content.
649        created_at (datetime): Timestamp when the report was created.
650    """
651    __tablename__ = 'report'
652
653    # Primary key
654    id: so.Mapped[int] = so.mapped_column(primary_key=True)
655
656    # Report details
657    report_type: so.Mapped[str] = so.mapped_column(
658        sa.Enum('Completion', 'Performance', name = 'report_type'), 
659        nullable = False
660    )
661    description: so.Mapped[str] = so.mapped_column(
662        sa.String(255), 
663        nullable = False
664        )    
665    report_data: so.Mapped[Optional[str]] = so.mapped_column(
666        sa.Text, 
667        nullable = True)  
668    created_at: so.Mapped[datetime] = so.mapped_column(
669        sa.DateTime, 
670        default = lambda: datetime.now(timezone.utc), 
671        nullable = False
672    )
673
674    def __repr__(self):
675        """Returns a string representation of the Report object."""
676        return f"<Report {self.report_type} created on {self.created_at}>"