models.py

# models.py
from sqlalchemy import create_engine, Column, Integer, String, DateTime, Boolean, ForeignKey, JSON, Float
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, sessionmaker
from datetime import datetime
import uuid

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    
    id = Column(Integer, primary_key=True)
    username = Column(String, unique=True, nullable=False)
    email = Column(String, unique=True, nullable=False)
    password_hash = Column(String, nullable=False)
    created_at = Column(DateTime, default=datetime.utcnow)
    last_login = Column(DateTime)
    is_active = Column(Boolean, default=True)
    preferences = Column(JSON)
    
    # Relationships
    courses = relationship("UserCourse", back_populates="user")
    progress = relationship("CourseProgress", back_populates="user")
    interactions = relationship("UserInteraction", back_populates="user")

class Course(Base):
    __tablename__ = 'courses'
    
    id = Column(Integer, primary_key=True)
    uuid = Column(String, unique=True, default=lambda: str(uuid.uuid4()))
    title = Column(String, nullable=False)
    description = Column(String)
    difficulty_level = Column(String)
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, onupdate=datetime.utcnow)
    content = Column(JSON)  # Stores complete course structure
    metadata = Column(JSON)  # Stores additional course metadata
    
    # Relationships
    modules = relationship("CourseModule", back_populates="course")
    user_courses = relationship("UserCourse", back_populates="course")

class CourseModule(Base):
    __tablename__ = 'course_modules'
    
    id = Column(Integer, primary_key=True)
    course_id = Column(Integer, ForeignKey('courses.id'))
    title = Column(String, nullable=False)
    description = Column(String)
    order_index = Column(Integer)
    content = Column(JSON)
    prerequisites = Column(JSON)
    
    # Relationships
    course = relationship("Course", back_populates="modules")
    sections = relationship("ModuleSection", back_populates="module")

class ModuleSection(Base):
    __tablename__ = 'module_sections'
    
    id = Column(Integer, primary_key=True)
    module_id = Column(Integer, ForeignKey('course_modules.id'))
    title = Column(String, nullable=False)
    content = Column(String)
    order_index = Column(Integer)
    metadata = Column(JSON)
    
    # Relationships
    module = relationship("CourseModule", back_populates="sections")
    quizzes = relationship("SectionQuiz", back_populates="section")

class UserCourse(Base):
    __tablename__ = 'user_courses'
    
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id'))
    course_id = Column(Integer, ForeignKey('courses.id'))
    enrollment_date = Column(DateTime, default=datetime.utcnow)
    completion_date = Column(DateTime)
    status = Column(String)  # 'enrolled', 'in_progress', 'completed', 'archived'
    
    # Relationships
    user = relationship("User", back_populates="courses")
    course = relationship("Course", back_populates="user_courses")
    progress = relationship("CourseProgress", back_populates="user_course")

class CourseProgress(Base):
    __tablename__ = 'course_progress'
    
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id'))
    user_course_id = Column(Integer, ForeignKey('user_courses.id'))
    module_id = Column(Integer, ForeignKey('course_modules.id'))
    last_accessed = Column(DateTime)
    completion_percentage = Column(Float)
    status = Column(String)  # 'not_started', 'in_progress', 'completed'
    
    # Relationships
    user = relationship("User", back_populates="progress")
    user_course = relationship("UserCourse", back_populates="progress")

class UserInteraction(Base):
    __tablename__ = 'user_interactions'
    
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id'))
    interaction_type = Column(String)  # 'quiz_attempt', 'question_asked', 'content_viewed'
    content_reference = Column(String)
    timestamp = Column(DateTime, default=datetime.utcnow)
    metadata = Column(JSON)
    
    # Relationships
    user = relationship("User", back_populates="interactions")