models.py

from flask_sqlalchemy import SQLAlchemy
from flask_bcrypt import Bcrypt
from flask_login import UserMixin, LoginManager
from datetime import datetime

db = SQLAlchemy()
bcrypt = Bcrypt()
login_manager = LoginManager()

@login_manager.user_loader
def load_user(user_id):
    return User.query.get(int(user_id))

class User(db.Model, UserMixin):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    password_hash = db.Column(db.String(255), nullable=False)
    role = db.Column(db.Enum('Admin', 'HOD', 'Staff', 'Student'), nullable=False, default='Student')
    department = db.Column(db.String(100))
    created_at = db.Column(db.DateTime, default=db.func.current_timestamp())
    
    feedbacks = db.relationship('Feedback', backref='author', lazy=True)
    uploads = db.relationship('Upload', backref='uploader', lazy=True)

    def set_password(self, password):
        self.password_hash = bcrypt.generate_password_hash(password).decode('utf-8')

    def check_password(self, password):
        return bcrypt.check_password_hash(self.password_hash, password)

class Upload(db.Model):
    __tablename__ = 'uploads'
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=True)
    filename = db.Column(db.String(255), nullable=False)
    total_rows = db.Column(db.Integer, default=0)
    processed_rows = db.Column(db.Integer, default=0)
    status = db.Column(db.Enum('Pending', 'Processing', 'Completed', 'Failed'), default='Pending')
    upload_date = db.Column(db.DateTime, default=db.func.current_timestamp())

    feedbacks = db.relationship('Feedback', backref='upload_source', lazy=True, cascade="all, delete-orphan")

class Feedback(db.Model):
    __tablename__ = 'feedback'
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=True)
    upload_id = db.Column(db.Integer, db.ForeignKey('uploads.id'), nullable=True)
    original_text = db.Column(db.Text, nullable=False)
    cleaned_text = db.Column(db.Text)
    sentiment = db.Column(db.Enum('Positive', 'Negative', 'Neutral'), nullable=False)
    sentiment_score = db.Column(db.Float, nullable=False)
    department_category = db.Column(db.String(100))
    created_at = db.Column(db.DateTime, default=db.func.current_timestamp())