models.py

from flask_sqlalchemy import SQLAlchemy
from flask_login import UserMixin
from datetime import datetime

# Initialize the SQLAlchemy database instance
db = SQLAlchemy()

# ------------------------------------------------------------------------
# MODEL 1: USER (Rubric Criterion 2: Authorization - "Subjects")
# ------------------------------------------------------------------------
class User(UserMixin, db.Model):
    """
    Represents a person using the system.
    Inherits from UserMixin to provide default methods for Flask-Login (is_authenticated, etc.)
    """
    id = db.Column(db.Integer, primary_key=True)
    
    # Unique username is required for login
    username = db.Column(db.String(150), unique=True, nullable=False, index=True)
    
    # Full name of the user
    name = db.Column(db.String(150), nullable=False)
    
    # Rubric Criterion 4: Hashing
    # We NEVER store plain text passwords. We store the 'hash' generated by the server.
    password_hash = db.Column(db.String(200), nullable=False)  # Increased from 150 to 200
    
    # Rubric Criterion 2: Authorization (Roles)
    # We store the role ('Admin', 'Student', 'Verifier') to decide who can access what.
    role = db.Column(db.String(50), nullable=False)
    
    # Rate Limiting Fields (Security Enhancement)
    failed_login_attempts = db.Column(db.Integer, default=0)
    last_failed_login = db.Column(db.DateTime, nullable=True)

# ------------------------------------------------------------------------
# MODEL 2: CERTIFICATE (Rubric Criterion 2: Authorization - "Objects")
# ------------------------------------------------------------------------
class Certificate(db.Model):
    """
    Represents a secure document uploaded to the locker.
    """
    id = db.Column(db.Integer, primary_key=True)
    
    # Link this certificate to a specific student (Foreign Key)
    # Cascade delete: if user is deleted, their certificates are also deleted
    student_id = db.Column(db.Integer, db.ForeignKey('user.id', ondelete='CASCADE'), nullable=False, index=True)
    
    # The original filename (e.g., "transcript.pdf") for display purposes
    filename = db.Column(db.String(150))
    
    # Rubric Criterion 3: Encryption
    # 'LargeBinary' stores raw bytes. We need this because encrypted data looks like garbage text,
    # not readable strings.
    encrypted_data = db.Column(db.LargeBinary, nullable=False)      # The PDF content (AES Encrypted)
    encrypted_aes_key = db.Column(db.LargeBinary, nullable=False)   # The AES Key (RSA Encrypted)
    iv = db.Column(db.LargeBinary, nullable=False)                  # Initialization Vector (Critical for AES randomness)
    
    # Rubric Criterion 4: Digital Signature
    # This stores the signed hash of the file to verify if it has been tampered with.
    digital_signature = db.Column(db.String(500), nullable=False)
    
    # Public/Private flag: If True, visible to verifiers; if False, private cloud storage.
    is_public = db.Column(db.Boolean, default=False)
    
    upload_date = db.Column(db.DateTime, default=datetime.now)
    
    # Relationship to access the student/owner information
    owner = db.relationship('User', foreign_keys=[student_id], backref='certificates')

# ------------------------------------------------------------------------
# MODEL 3: ACCESS LOG (Rubric Criterion 5: Security Levels/Audit)
# ------------------------------------------------------------------------
class AccessLog(db.Model):
    """
    Records who did what and when. Essential for security auditing.
    """
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id', ondelete='SET NULL'), index=True)
    action = db.Column(db.String(200), nullable=False) # e.g. "User viewed Cert #5"
    timestamp = db.Column(db.DateTime, default=datetime.now, index=True)
    
    # Relationship to access user information
    user = db.relationship('User', foreign_keys=[user_id], backref='access_logs')