You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
250 lines
9.8 KiB
250 lines
9.8 KiB
from sqlalchemy import create_engine, Column, Integer, String, Text, DateTime, Float, Boolean, ForeignKey, Enum
|
|
from sqlalchemy.ext.declarative import declarative_base
|
|
from sqlalchemy.orm import relationship, sessionmaker
|
|
from datetime import datetime
|
|
import enum
|
|
|
|
Base = declarative_base()
|
|
|
|
|
|
class UserRole(enum.Enum):
|
|
ADMIN = "admin"
|
|
TEACHER = "teacher"
|
|
STUDENT = "student"
|
|
|
|
|
|
class CourseStatus(enum.Enum):
|
|
DRAFT = "draft"
|
|
PUBLISHED = "published"
|
|
ARCHIVED = "archived"
|
|
|
|
|
|
class EnrollmentStatus(enum.Enum):
|
|
ACTIVE = "active"
|
|
COMPLETED = "completed"
|
|
DROPPED = "dropped"
|
|
|
|
|
|
class AssignmentStatus(enum.Enum):
|
|
DRAFT = "draft"
|
|
PUBLISHED = "published"
|
|
CLOSED = "closed"
|
|
|
|
|
|
class SubmissionStatus(enum.Enum):
|
|
PENDING = "pending"
|
|
SUBMITTED = "submitted"
|
|
GRADED = "graded"
|
|
|
|
|
|
class User(Base):
|
|
__tablename__ = "users"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
username = Column(String(50), unique=True, index=True, nullable=False)
|
|
email = Column(String(100), unique=True, index=True, nullable=False)
|
|
password_hash = Column(String(255), nullable=False)
|
|
role = Column(Enum(UserRole, name="userrole", create_type=True), default=UserRole.STUDENT, nullable=False)
|
|
full_name = Column(String(100))
|
|
avatar = Column(String(255))
|
|
bio = Column(Text)
|
|
created_at = Column(DateTime, default=datetime.utcnow)
|
|
updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
|
|
last_login = Column(DateTime)
|
|
|
|
created_courses = relationship("Course", back_populates="teacher", foreign_keys="Course.teacher_id")
|
|
enrollments = relationship("Enrollment", back_populates="student")
|
|
submissions = relationship("AssignmentSubmission", back_populates="student")
|
|
progress = relationship("LearningProgress", back_populates="student")
|
|
reviews = relationship("CourseReview", back_populates="student")
|
|
questions = relationship("QAQuestion", back_populates="student")
|
|
answers = relationship("QAAnswer", back_populates="student")
|
|
|
|
|
|
class Course(Base):
|
|
__tablename__ = "courses"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
title = Column(String(200), nullable=False)
|
|
description = Column(Text)
|
|
cover_image = Column(String(255))
|
|
teacher_id = Column(Integer, ForeignKey("users.id"), nullable=False)
|
|
category = Column(String(50))
|
|
level = Column(String(50))
|
|
duration = Column(Integer)
|
|
price = Column(Float, default=0.0)
|
|
status = Column(Enum(CourseStatus, name="coursestatus", create_type=True), default=CourseStatus.DRAFT)
|
|
created_at = Column(DateTime, default=datetime.utcnow)
|
|
updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
|
|
published_at = Column(DateTime)
|
|
|
|
teacher = relationship("User", back_populates="created_courses", foreign_keys=[teacher_id])
|
|
chapters = relationship("Chapter", back_populates="course", cascade="all, delete-orphan")
|
|
enrollments = relationship("Enrollment", back_populates="course")
|
|
reviews = relationship("CourseReview", back_populates="course", cascade="all, delete-orphan")
|
|
announcements = relationship("Announcement", back_populates="course", cascade="all, delete-orphan")
|
|
|
|
|
|
class Chapter(Base):
|
|
__tablename__ = "chapters"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
course_id = Column(Integer, ForeignKey("courses.id"), nullable=False)
|
|
title = Column(String(200), nullable=False)
|
|
description = Column(Text)
|
|
order = Column(Integer, default=0)
|
|
created_at = Column(DateTime, default=datetime.utcnow)
|
|
updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
|
|
|
|
course = relationship("Course", back_populates="chapters")
|
|
lessons = relationship("Lesson", back_populates="chapter", cascade="all, delete-orphan")
|
|
|
|
|
|
class Lesson(Base):
|
|
__tablename__ = "lessons"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
chapter_id = Column(Integer, ForeignKey("chapters.id"), nullable=False)
|
|
title = Column(String(200), nullable=False)
|
|
content = Column(Text)
|
|
video_url = Column(String(255))
|
|
duration = Column(Integer)
|
|
order = Column(Integer, default=0)
|
|
is_free = Column(Boolean, default=False)
|
|
created_at = Column(DateTime, default=datetime.utcnow)
|
|
updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
|
|
|
|
chapter = relationship("Chapter", back_populates="lessons")
|
|
progress = relationship("LearningProgress", back_populates="lesson")
|
|
|
|
|
|
class Assignment(Base):
|
|
__tablename__ = "assignments"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
course_id = Column(Integer, ForeignKey("courses.id"), nullable=False)
|
|
chapter_id = Column(Integer, ForeignKey("chapters.id"))
|
|
title = Column(String(200), nullable=False)
|
|
description = Column(Text)
|
|
total_points = Column(Integer, default=100)
|
|
due_date = Column(DateTime)
|
|
status = Column(Enum(AssignmentStatus, name="assignmentstatus", create_type=True), default=AssignmentStatus.DRAFT)
|
|
created_at = Column(DateTime, default=datetime.utcnow)
|
|
updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
|
|
|
|
course = relationship("Course")
|
|
chapter = relationship("Chapter")
|
|
submissions = relationship("AssignmentSubmission", back_populates="assignment", cascade="all, delete-orphan")
|
|
|
|
|
|
class AssignmentSubmission(Base):
|
|
__tablename__ = "assignment_submissions"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
assignment_id = Column(Integer, ForeignKey("assignments.id"), nullable=False)
|
|
student_id = Column(Integer, ForeignKey("users.id"), nullable=False)
|
|
content = Column(Text)
|
|
file_url = Column(String(255))
|
|
score = Column(Float)
|
|
feedback = Column(Text)
|
|
status = Column(Enum(SubmissionStatus, name="submissionstatus", create_type=True), default=SubmissionStatus.PENDING)
|
|
submitted_at = Column(DateTime)
|
|
graded_at = Column(DateTime)
|
|
created_at = Column(DateTime, default=datetime.utcnow)
|
|
|
|
assignment = relationship("Assignment", back_populates="submissions")
|
|
student = relationship("User", back_populates="submissions")
|
|
|
|
|
|
class Enrollment(Base):
|
|
__tablename__ = "enrollments"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
course_id = Column(Integer, ForeignKey("courses.id"), nullable=False)
|
|
student_id = Column(Integer, ForeignKey("users.id"), nullable=False)
|
|
status = Column(Enum(EnrollmentStatus, name="enrollmentstatus", create_type=True), default=EnrollmentStatus.ACTIVE)
|
|
enrolled_at = Column(DateTime, default=datetime.utcnow)
|
|
completed_at = Column(DateTime)
|
|
|
|
course = relationship("Course", back_populates="enrollments")
|
|
student = relationship("User", back_populates="enrollments")
|
|
|
|
|
|
class LearningProgress(Base):
|
|
__tablename__ = "learning_progress"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
student_id = Column(Integer, ForeignKey("users.id"), nullable=False)
|
|
lesson_id = Column(Integer, ForeignKey("lessons.id"), nullable=False)
|
|
completed = Column(Boolean, default=False)
|
|
watch_time = Column(Integer, default=0)
|
|
last_position = Column(Integer, default=0)
|
|
completed_at = Column(DateTime)
|
|
created_at = Column(DateTime, default=datetime.utcnow)
|
|
updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
|
|
|
|
student = relationship("User", back_populates="progress")
|
|
lesson = relationship("Lesson", back_populates="progress")
|
|
|
|
|
|
class CourseReview(Base):
|
|
__tablename__ = "course_reviews"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
course_id = Column(Integer, ForeignKey("courses.id"), nullable=False)
|
|
student_id = Column(Integer, ForeignKey("users.id"), nullable=False)
|
|
rating = Column(Integer, nullable=False)
|
|
comment = Column(Text)
|
|
created_at = Column(DateTime, default=datetime.utcnow)
|
|
updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
|
|
|
|
course = relationship("Course", back_populates="reviews")
|
|
student = relationship("User", back_populates="reviews")
|
|
|
|
|
|
class QAQuestion(Base):
|
|
__tablename__ = "qa_questions"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
course_id = Column(Integer, ForeignKey("courses.id"), nullable=False)
|
|
student_id = Column(Integer, ForeignKey("users.id"), nullable=False)
|
|
title = Column(String(200), nullable=False)
|
|
content = Column(Text, nullable=False)
|
|
views = Column(Integer, default=0)
|
|
is_solved = Column(Boolean, default=False)
|
|
created_at = Column(DateTime, default=datetime.utcnow)
|
|
updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
|
|
|
|
course = relationship("Course")
|
|
student = relationship("User", back_populates="questions")
|
|
answers = relationship("QAAnswer", back_populates="question", cascade="all, delete-orphan")
|
|
|
|
|
|
class QAAnswer(Base):
|
|
__tablename__ = "qa_answers"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
question_id = Column(Integer, ForeignKey("qa_questions.id"), nullable=False)
|
|
student_id = Column(Integer, ForeignKey("users.id"), nullable=False)
|
|
content = Column(Text, nullable=False)
|
|
is_accepted = Column(Boolean, default=False)
|
|
created_at = Column(DateTime, default=datetime.utcnow)
|
|
updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
|
|
|
|
question = relationship("QAQuestion", back_populates="answers")
|
|
student = relationship("User", back_populates="answers")
|
|
|
|
|
|
class Announcement(Base):
|
|
__tablename__ = "announcements"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
course_id = Column(Integer, ForeignKey("courses.id"), nullable=False)
|
|
title = Column(String(200), nullable=False)
|
|
content = Column(Text, nullable=False)
|
|
is_pinned = Column(Boolean, default=False)
|
|
created_at = Column(DateTime, default=datetime.utcnow)
|
|
updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
|
|
|
|
course = relationship("Course", back_populates="announcements")
|